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


    
function get_sql_for_petty_cash_payments_inquiry($from_date, $to_date, $project_id, $float_id, $company_code, $employee_code, $payment_id)
{  
	$sql= "SELECT 
                vp.id,
                vp.batch_no,
                vp.company_code,
                pm.description project_description,                
                pf.name float_name,
                vp.payment_amount,
                vp.tran_date,  
                vp.employee_code,
                vp.expense_source,
                vp.expense_id 
            FROM ".TB_PREF."vw_petty_cash_payments vp 
                    INNER JOIN ".TB_PREF."projects_master pm ON 
                            vp.project_id = pm.project_id 
                    INNER JOIN ".TB_PREF."petty_cash_floats pf ON    
                            vp.float_id = pf.id 
                    
                    LEFT JOIN ".TB_PREF."i_owe_you_batches b ON 
                            vp.batch_no = b.batch_no 
                    LEFT JOIN group_companies gc ON 
                            vp.company_code = gc.company_code 
            WHERE 1 = 1 ";
     
    if ($payment_id > 0)
	{
		$sql .= " AND vp.id = ".  db_escape($payment_id);
	}
    else{
	if ($employee_code != "")
        {
            $sql .= " AND vp.employee_code = ".db_escape($employee_code);
        }
        
        if ($project_id > 0)
	{
		$sql .= " AND vp.project_id = ".  db_escape($project_id);
	}
    
        if ($float_id > 0)
	{
		$sql .= " AND vp.float_id = ".  db_escape($float_id);
	}
   
        if ($company_code != '')
	{
		$sql .= " AND vp.company_code = ".  db_escape($company_code);
	}
        $sql .= " AND vp.tran_date >=".db_escape(date2sql($from_date))."AND vp.tran_date <=".db_escape(date2sql($to_date));
    }
	
    $sql .=" ORDER BY vp.id, vp.tran_date";
        
    return $sql;
}

function get_petty_cash_payments($project_id, $from_date, $to_date)
{
//    ST_I_OWE_YOU', 107);
//define ('ST_SUPPLIER_TRANSPORT_INVOICES', 155);
//define ('ST_VEHICLE_ALLOCATION', 126);
//define ('ST_VEHICLE_RUNNING_CHART', 127);
//define ('ST_PETTY_CASH', 168);
    
    $to_date = $to_date." 23:59:59";
    
    
    "SELECT p.id, p.iou_id, p.payment_date tran_date, p.amount payment_amount, p.expense_source,
                        iou.id expense_id, iou.amount expense_amount, iou.project_id   project_id   
                FROM ".TB_PREF."i_owe_you_payments p 
                    JOIN ".TB_PREF."i_owe_you iou ON 
                         iou.id = p.iou_id                
                WHERE p.paid = 1 AND p.expense_source = 107 
                    
    UNION 
    
    
    SELECT p.id, p.iou_id, p.payment_date tran_date, p.amount payment_amount, p.expense_source,
                        t.id expense_id, IF(t.tax_included, t.total, t.total + t.taxes) expense_amount, t.project_id   project_id   
                FROM ".TB_PREF."i_owe_you_payments p 
                    JOIN ".TB_PREF."supplier_transport_invoices t ON 
                        t.payment_source_no = p.id                                 
                WHERE p.paid = 1 AND p.expense_source = 155  
    
    UNION 
    
    SELECT p.id, p.iou_id, p.payment_date tran_date, p.amount payment_amount, p.expense_source,
                     va.id expense_id, va.amount expense_amount, va.project_id project_id                       
                FROM ".TB_PREF."i_owe_you_payments p 
                    JOIN ".TB_PREF."vehicle_allocations va ON 
                         va.payment_source_no = p.id                 
                WHERE p.paid = 1 AND p.expense_source = 126  
    
    UNION 
    
    SELECT p.id, p.iou_id, p.payment_date tran_date, p.amount payment_amount, p.expense_source,
                    pch.id expense_id, pch.amount expense_amount, pch.project_id  project_id                        
                FROM ".TB_PREF."i_owe_you_payments p 
                    JOIN ".TB_PREF."petty_cash_header pch ON 
                        pch.id = p.iou_id                         
                WHERE p.paid = 1 AND p.expense_source = 168  
                    
    UNION 
    
    SELECT p.id, p.iou_id, p.payment_date tran_date, p.amount payment_amount, p.expense_source,
                    vrc.id expense_id, vrc.cost expense_amount, vrc.project_id project_id                     
                FROM ".TB_PREF."i_owe_you_payments p 
                    JOIN ".TB_PREF."vehicle_running_chart vrc ON 
                    vrc.payment_source_no = p.id                                         
                WHERE p.paid = 1 AND p.expense_source = 127 

    ORDER BY tran_date";
    
    return db_query($sql,"Could not get_petty_cash_expenses");
    
}

?>