<?php //
/**********************************************************************
***********************************************************************/


function add_petty_cash_stock_item($type, $trans_no, 
                    $stype, $strn_no, $sd_id,
                    $location, $tran_date,
                    $stock_id, $item_description, 
                    $tran_unit, $tran_qty, 
                    $tran_unit_cost, $comment,
                    $qty, $conversion_factor,
                    $project_id, $task_id) 
{
    $created_by = $_SESSION['wa_current_user']->user;
    $created_on = date('Y-m-d H:i:s');
    
	$sql = "INSERT INTO ".TB_PREF."petty_cash_stock_item_receipt_details 
                    (type, trans_no, 
                    source_type, source_trans_no, source_detail_id, 
                    location, tran_date, 
                    stock_id, item_description,
                    tran_unit, tran_qty, 
                    tran_unit_cost, comment,
                    qty, conversion_factor,
                    project_id, task_id,
                    created_by, created_on)            
		VALUES 
                (".db_escape($type).", ".db_escape($trans_no).",
                    ".db_escape($stype).",".db_escape($strn_no). ",".db_escape($sd_id). ",
                    ".db_escape($location). ",".db_escape($tran_date). ",
                    ".db_escape($stock_id). ",".db_escape($item_description). ",
                    ".db_escape($tran_unit).",  ".db_escape($tran_qty).", 
                    ".db_escape($tran_unit_cost).", ".db_escape($comment).",    
                    ".db_escape($qty).", ".db_escape($conversion_factor).",    
                    ".db_escape($project_id).", ".db_escape($task_id).",    
                    ".db_escape($created_by).", ".db_escape($created_on).")"; 

    db_query($sql, "add_petty_cash_stock_item"); 
    

}


function update_petty_cash_stock_item($id, $tran_date, $item_description, $tran_qty, 
                    $tran_unit_cost, $comment, $tran_unit, $qty, $conversion_factor)
{
    $updated_by = $_SESSION['wa_current_user']->user;
    $updated_on = date('Y-m-d H:i:s');
    
    $sql = "UPDATE ".TB_PREF."petty_cash_stock_item_receipt_details
            SET tran_date=".db_escape($tran_date).", 
                item_description=".db_escape($item_description).", 
                tran_qty=".db_escape($tran_qty).", 
                tran_unit_cost=".db_escape($tran_unit_cost).", 
                comment=".db_escape($comment).",            
                tran_unit=".db_escape($tran_unit).",            
                qty=".db_escape($qty).",            
                conversion_factor=".db_escape($conversion_factor).",            
                updated_by=".db_escape($updated_by).", 
                updated_on=".db_escape($updated_on)."                     
            WHERE id=".db_escape($id)." 
                AND approved_by = 0 ";
        
    db_query($sql, "update_petty_cash_stock_item");
    
    $n = db_num_affected_rows();
    
    if($n == 1){
        return array("success"=>true, "message"=>"");
    }else{
        return array("success"=>false, "message"=>"Could not update the item receipt record.");
    }

}


function get_petty_cash_item_receive_location_by_source_type($source_type_id, $source_detail_id)
{
	$sql = "SELECT DISTINCT sir.location,                 
                        l.location_name 
                FROM ".TB_PREF."petty_cash_stock_item_receipt_details sir                                          
                        JOIN ".TB_PREF."locations l   
                            ON l.loc_code = sir.location  
                            
                WHERE  sir.source_type=".db_escape($source_type_id)." AND         
                        sir.source_detail_id=".db_escape($source_detail_id);        
  
	$result = db_query($sql, "get_petty_cash_item_receive_location_by_source_type");
        
        return db_fetch($result);
}


function get_petty_cash_item_receive_items_by_source_type($source_type_id, $source_detail_id)
{
	$sql = "SELECT sir.*,                 
                        iu.name tran_unit_name,
                        l.location_name 
                FROM ".TB_PREF."petty_cash_stock_item_receipt_details sir                                          
                        JOIN ".TB_PREF."item_units iu  
                            ON sir.tran_unit = iu.abbr 
                        JOIN ".TB_PREF."locations l   
                            ON l.loc_code = sir.location  
                            
                WHERE  sir.source_type=".db_escape($source_type_id)." AND         
                        sir.source_detail_id=".db_escape($source_detail_id);        
  
	return db_query($sql, "petty_cash_stock_item_receipt_details");
}


function get_petty_cash_item_receive_item_details($id)
{
	$sql = "SELECT sir.*,                 
                        iu.name tran_unit_name                         
                FROM ".TB_PREF."petty_cash_stock_item_receipt_details sir                                          
                        JOIN ".TB_PREF."item_units iu  
                            ON sir.tran_unit = iu.abbr 
                            
                WHERE sir.id=".db_escape($id);               
  
	$result = db_query($sql, "get_petty_cash_item_receive_item_details");
        return db_fetch($result);
}

function get_petty_cash_stock_item_record_by_id($id)
{
    $sql = "SELECT * 
                    

            FROM ".TB_PREF."petty_cash_stock_item_receipt_details bim 

            WHERE bim.id=".db_escape($id);        

    $result = db_query($sql, "Could not get_boq_item_material_record");
    return db_fetch($result);
}

function delete_petty_cash_stock_item($id)
{
	$sql = "DELETE FROM ".TB_PREF."petty_cash_stock_item_receipt_details 
                WHERE id=".db_escape($id)." 
                    AND approved_by = 0 ";
        
	db_query($sql,"delete_petty_cash_stock_item");
    
}



function get_petty_cash_not_processed_stock_items_by_source($stype, $sd_id)
{
    $sql = "SELECT boqm.* 
            FROM ".TB_PREF."petty_cash_stock_item_receipt_details boqm 
            WHERE  source_type=".db_escape($stype)." AND       
                   source_detail_id=".db_escape($sd_id)." AND    
                   boqm.approved_by = 0 ";

        return db_query($sql, "get_petty_cash_not_processed_stock_items_by_source");
  
}



function get_petty_cash_stock_items_total_cost_by_source($stype, $sd_id, $selected_id=0)
{
	$sql = "SELECT SUM(tran_unit_cost * tran_qty) total_cost 
                FROM ".TB_PREF."petty_cash_stock_item_receipt_details 
                WHERE  source_type=".db_escape($stype)." AND            
                        source_detail_id=".db_escape($sd_id);    
        
        if($selected_id > 0){
            $sql .= " AND id <> ".db_escape($selected_id);
        }
        
	$result = db_query($sql,"get_petty_cash_stock_items_total_cost");
        
        return db_result_value($result, "total_cost");
    
}


function update_petty_cash_stock_item_processed($id, $batch_id)
{
    $updated_by = $_SESSION['wa_current_user']->user;
    $updated_on = date('Y-m-d H:i:s');
    
    $sql = "UPDATE ".TB_PREF."petty_cash_stock_item_receipt_details
            SET batch_id=".db_escape($batch_id).", 
                approved_by=".db_escape($updated_by).", 
                approved_on=".db_escape($updated_on)." 
                    
            WHERE id=".db_escape($id)." 
                AND approved_by = 0 ";
        
    db_query($sql, "update_petty_cash_stock_item_processed");
    
    $n = db_num_affected_rows();
    
    if($n == 1){
        return array("success"=>true, "message"=>"");
    }else{
        return array("success"=>false, "message"=>"Another user has updated item receipt record $id.");
    }

}

function get_petty_cash_item_receive_record($type_id, $trans_no)
{
	$sql = "SELECT sir.*,                 
                        iu.name tran_unit_name                         
                FROM ".TB_PREF."petty_cash_stock_item_receipt_details sir                                          
                        JOIN ".TB_PREF."item_units iu  
                            ON sir.tran_unit = iu.abbr 
                            
                WHERE  sir.type=".db_escape($type_id)." AND       
                        sir.trans_no=".db_escape($trans_no);        
  
	$result = db_query($sql, "get_petty_cash_item_receive_record");
        
        return db_fetch($result);
}


function get_petty_cash_stock_items_total_cost($type_id, $trans_no, $detail_id, $selected_id=0)
{
	$sql = "SELECT SUM(tran_unit_cost * tran_qty) total_cost 
                FROM ".TB_PREF."petty_cash_stock_item_receipt_details 
                WHERE  type=".db_escape($type_id)." AND       
                        trans_no=".db_escape($trans_no)." AND       
                        source_detail_id=".db_escape($detail_id);    
        
        if($selected_id > 0){
            $sql .= " AND id <> ".db_escape($selected_id);
        }
        
	$result = db_query($sql,"get_petty_cash_stock_items_total_cost");
        
        return db_result_value($result, "total_cost");
    
}
