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


function get_vehicle_requests($type, $project_id, $from_date, $to_date)
{
    $to_date = $to_date." 23:59:59";
    
    $sql = "SELECT vr.id trans_no, '' reference, vr.to_date_time tran_date, vr.project_id, vr.route,  
                    SUM(tca.amount) total_cost 
            FROM ".TB_PREF."vehicle_requests vr   
                JOIN ".TB_PREF."task_cost_allocations tca ON 
                            tca.tran_no = vr.id 
            WHERE vr.project_id =".db_escape($project_id)
                ." AND vr.to_date_time >=".db_escape($from_date)
                ." AND vr.to_date_time <=".db_escape($to_date) 
                ." AND tca.tran_type = ".db_escape($type)."  
            GROUP by vr.id  
            ORDER by vr.to_date_time";
    
    return db_query($sql,"get_vehicle_requests");
    
}


function get_i_owe_yous($type, $project_id, $from_date, $to_date)
{
    $sql = "SELECT iou.id trans_no, reference, tran_date, project_id, company_code, employee_code,  
                    SUM(tca.amount) total_cost 
            FROM ".TB_PREF."i_owe_you iou  
                JOIN ".TB_PREF."task_cost_allocations tca ON 
                            tca.tran_no = iou.id 
            WHERE project_id =".db_escape($project_id)
                ." AND tran_date >=".db_escape($from_date)
                ." AND tran_date <=".db_escape($to_date) 
                ." AND tca.tran_type = ".db_escape($type)."  
            GROUP by iou.id  
            ORDER by tran_date";
    
    return db_query($sql,"get_purchase_requisitions");
    
}


function get_subcontractor_purchase_orders($type, $project_id, $from_date, $to_date)
{
    $sql = "SELECT po.id trans_no, po.reference, po.date tran_date, po.project_id, supp.supp_name,   
                    SUM(tca.amount) total_cost 
            FROM ".TB_PREF."subcontractor_purchase_orders po  
                    JOIN ".TB_PREF."task_cost_allocations tca ON 
                            tca.tran_no = po.id 
                    JOIN ".TB_PREF."suppliers supp ON 
                            supp.supplier_id = po.supplier_id
                            
            WHERE po.project_id =".db_escape($project_id)
                ." AND po.date >=".db_escape($from_date)
                ." AND po.date <=".db_escape($to_date)
                ." AND tca.tran_type = ".db_escape($type)." 
            GROUP by po.id  
            ORDER by po.date";
    
    return db_query($sql,"get_purchase_requisitions");
    
}


function get_purchase_requisitions($type, $project_id, $from_date, $to_date)
{
    $sql = "SELECT im.pr_no trans_no, im.reference, im.pr_date tran_date, im.project_id, '' employee_code,   
                    SUM(tca.amount) total_cost 
            FROM ".TB_PREF."purchase_requisitions im 
                    JOIN ".TB_PREF."task_cost_allocations tca ON 
                            tca.tran_no = im.pr_no 
            WHERE im.project_id =".db_escape($project_id)
                ." AND im.pr_date >=".db_escape($from_date)
                ." AND im.pr_date <=".db_escape($to_date)
                ." AND tca.tran_type = ".db_escape($type)." 
            GROUP by im.pr_no 
            ORDER by im.pr_date";
    
    return db_query($sql,"get_purchase_requisitions");
    
}


function get_material_requisitions($type, $project_id, $from_date, $to_date)
{
    $sql = "SELECT im.mr_no trans_no, im.reference, im.mr_date tran_date, im.project_id, '' employee_code,   
                    SUM(tca.amount) total_cost 
            FROM ".TB_PREF."material_requisitions im 
                    JOIN ".TB_PREF."task_cost_allocations tca ON 
                            tca.tran_no = im.mr_no 
            WHERE im.project_id =".db_escape($project_id)
                ." AND im.mr_date >=".db_escape($from_date)
                ." AND im.mr_date <=".db_escape($to_date)
                ." AND tca.tran_type = ".db_escape($type)." 
            GROUP by im.mr_no 
            ORDER by im.mr_date";
    
    return db_query($sql,"get_material_requisitions");
    
}

function get_material_issues($type, $project_id, $from_date, $to_date)
{
    $sql = "SELECT im.im_no, im.reference, im.im_date, im.project_id, im.company_code, im.employee_code, 
                    SUM(imd.qty_issued * imd.unit_cost) total_cost 
            FROM ".TB_PREF."issue_materials im 
                    JOIN ".TB_PREF."issue_material_details imd ON 
                            imd.im_no = im.im_no 
            WHERE im.project_id =".db_escape($project_id)
                ." AND im.im_date >=".db_escape($from_date)
                ." AND im.im_date <=".db_escape($to_date)." 
            GROUP by im.im_no 
            ORDER by im.im_date";
    
    return db_query($sql,"Could not get_material_issues");
    
}

function get_material_returns($type, $project_id, $from_date, $to_date)
{
    $sql = "SELECT rm.rm_no, rm.reference, rm.rm_date, rm.project_id, rm.company_code, rm.employee_code, 
                    SUM(rmd.qty_returned * rmd.unit_cost) total_cost 
            FROM ".TB_PREF."return_materials rm 
                JOIN ".TB_PREF."return_material_details rmd ON 
                    rmd.rm_no = rm.rm_no 
            WHERE rm.project_id =".db_escape($project_id)
                ." AND rm.rm_date >=".db_escape($from_date)
                ." AND rm.rm_date <=".db_escape($to_date)." 
            GROUP by rm.rm_no 
            ORDER by rm.rm_date";
    
    return db_query($sql,"Could not get_material_returns");
    
}


function get_daily_item_issues($type, $project_id, $from_date, $to_date)
{
    $sql = "SELECT im.trans_no, im.reference, im.tran_date, im.project_id, im.group_company_code, im.movement_type, 
                    mt.name movement_type_name, 
                    SUM(imd.qty * imd.standard_cost) total_cost 
            FROM ".TB_PREF."daily_item_issues im 
                    JOIN ".TB_PREF."daily_item_issue_details imd 
                        ON imd.type = im.type 
                        AND imd.trans_no = im.trans_no 
                    JOIN ".TB_PREF."movement_types mt  
                        ON mt.id = im.movement_type  
            WHERE im.project_id =".db_escape($project_id)
                ." AND im.tran_date >=".db_escape($from_date)
                ." AND im.tran_date <=".db_escape($to_date) 
                ." AND im.type =".db_escape($type)." 
            GROUP by im.trans_no 
            ORDER by im.tran_date";
    
    return db_query($sql,"get_daily_item_issues");
    
}


function get_material_purchased_invoices($type, $project_id, $from_date, $to_date)
{
//    $sql = "SELECT DISTINCT st.trans_no inv_no,
//                            st.*, 
//                            supp.supp_name 
//            FROM ".TB_PREF."supp_trans st 
//                JOIN ".TB_PREF."supp_invoice_items sii ON 
//                    st.trans_no = sii.supp_trans_no 
//                JOIN ".TB_PREF."grn_items gi ON 
//                    sii.grn_item_id = gi.id 
//                JOIN ".TB_PREF."purch_order_details pod ON 
//                    gi.po_detail_item = pod.po_detail_item 
//                JOIN ".TB_PREF."purch_orders po ON 
//                    pod.order_no = po.order_no 
//                JOIN ".TB_PREF."suppliers supp ON 
//                    po.supplier_id = supp.supplier_id 
//
//            WHERE 
//                st.type = ".db_escape($type)
//                ." AND po.project_id =".db_escape($project_id)
//                ." AND st.tran_date >=".db_escape($from_date)
//                ." AND st.tran_date <=".db_escape($to_date)." 
//            ORDER BY st.trans_no";
    
//    $sql = "SELECT st.trans_no, st.tran_date, st.reference, st.alloc, 
//                    supp.supp_name, 
//                    SUM(sii.quantity * sii.unit_price) amount 
//            FROM 0_supp_trans st 
//                JOIN 0_supp_invoice_items sii 
//                    ON st.trans_no = sii.supp_trans_no 
//                JOIN 0_purch_order_details pod 
//                    ON sii.po_detail_item_id = pod.po_detail_item 
//                JOIN 0_purch_orders po 
//                    ON po.order_no = pod.order_no 
//                JOIN ".TB_PREF."suppliers supp ON 
//                    po.supplier_id = supp.supplier_id 
//            WHERE st.type = ".db_escape($type)." 
//                AND sii.supp_trans_type = ".db_escape($type)." 
//                AND po.project_id =".db_escape($project_id)." 
//                AND st.tran_date >=".db_escape($from_date)." 
//                AND st.tran_date <=".db_escape($to_date)." 
//            GROUP BY sii.supp_trans_no";
//    
//    return db_query($sql,"Could not get project material details");
    
}



function get_material_purchased_credit_notes($type, $project_id, $from_date, $to_date)
{
    $sql = "SELECT DISTINCT st.trans_no inv_no,
                            st.*, 
                            supp.supp_name 
            FROM ".TB_PREF."supp_trans st 
                JOIN ".TB_PREF."supp_invoice_items sii ON 
                    st.trans_no = sii.supp_trans_no 
                JOIN ".TB_PREF."grn_items gi ON 
                    sii.grn_item_id = gi.id 
                JOIN ".TB_PREF."purch_order_details pod ON 
                    gi.po_detail_item = pod.po_detail_item 
                JOIN ".TB_PREF."purch_orders po ON 
                    pod.order_no = po.order_no 
                JOIN ".TB_PREF."suppliers supp ON 
                    po.supplier_id = supp.supplier_id 

            WHERE 
                st.type = ".db_escape($type)
                ." AND po.project_id =".db_escape($project_id)
                ." AND st.tran_date >=".db_escape($from_date)
                ." AND st.tran_date <=".db_escape($to_date)." 
            ORDER BY st.trans_no";
    
    $sql = "SELECT st.trans_no, st.tran_date, st.reference, st.alloc, 
                    supp.supp_name, 
                    SUM(sii.quantity * sii.unit_price) amount 
            FROM 0_supp_trans st 
                JOIN 0_supp_invoice_items sii 
                    ON st.trans_no = sii.supp_trans_no 
                JOIN 0_purch_order_details pod 
                    ON sii.po_detail_item_id = pod.po_detail_item 
                JOIN 0_purch_orders po 
                    ON po.order_no = pod.order_no 
                JOIN ".TB_PREF."suppliers supp ON 
                    po.supplier_id = supp.supplier_id 
            WHERE st.type = ".db_escape($type)." 
                AND sii.supp_trans_type = ".db_escape($type)." 
                AND po.project_id =".db_escape($project_id)." 
                AND st.tran_date >=".db_escape($from_date)." 
                AND st.tran_date <=".db_escape($to_date)." 
            GROUP BY sii.supp_trans_no";
    
    return db_query($sql,"Could not get project material details");
    
}


function get_subcontractor_invoices($type, $project_id, $from_date, $to_date)
{
    $sql = "SELECT DISTINCT st.trans_no inv_no,
                            st.*, 
                            supp.supp_name,
                            subi.comments 
            FROM ".TB_PREF."supp_trans st 
                JOIN ".TB_PREF."subcontractor_invoices subi ON 
                    st.trans_no = subi.order_no  
                JOIN ".TB_PREF."suppliers supp ON 
                    st.supplier_id = supp.supplier_id 
            WHERE 
                st.type = ".db_escape($type)
                ." AND subi.project_id =".db_escape($project_id)
                ." AND st.tran_date >=".db_escape($from_date)
                ." AND st.tran_date <=".db_escape($to_date)." 
            ORDER BY st.trans_no";
    
    return db_query($sql,"get_subcontractor_invoices");
    
}


function get_subcontractor_credit_notes($type, $project_id, $from_date, $to_date)
{
    $sql = "SELECT DISTINCT st.trans_no inv_no,
                            st.*, 
                            supp.supp_name,
                            subi.comments 
            FROM ".TB_PREF."supp_trans st 
                JOIN ".TB_PREF."subcontractor_credit_notes subi ON 
                    st.trans_no = subi.order_no  
                JOIN ".TB_PREF."suppliers supp ON 
                    st.supplier_id = supp.supplier_id 
            WHERE 
                st.type = ".db_escape($type)
                ." AND subi.project_id =".db_escape($project_id)
                ." AND st.tran_date >=".db_escape($from_date)
                ." AND st.tran_date <=".db_escape($to_date)." 
            ORDER BY st.trans_no";
    
    return db_query($sql,"get_subcontractor_credit_notes");
    
}


function get_iou_petty_cash_expenses($project_id, $from_date, $to_date)
{
    $to_date = $to_date." 23:59:59";
    
    $sql = "SELECT b.batch_no, b.amount, 
                iou.advance_amount iou_advance_amount, SUM(ioup.amount) iou_paid_amount, iou.company_code iou_company_code, iou.employee_code iou_employee_code      
            FROM ".TB_PREF."i_owe_you_batches b 
                JOIN ".TB_PREF."i_owe_you iou ON 
                    iou.batch_no = b.batch_no 
                        LEFT JOIN ".TB_PREF."i_owe_you_payments ioup ON 
                            iou.id = ioup.iou_id 
                            AND ioup.expense_source = ".db_escape(ST_I_OWE_YOU)."   
            WHERE b.approval_status = ".db_escape(AS_APPROVED)
                ." AND b.project_id =".db_escape($project_id)
                ." AND b.created_on >=".db_escape($from_date)
                ." AND b.created_on <=".db_escape($to_date)." 
            GROUP BY b.batch_no ";
    
    return db_query($sql,"Could not get_iou_petty_cash_expenses");
    
}


function get_petty_cash_expenses($project_id, $from_date, $to_date)
{
    $to_date = $to_date." 23:59:59";
    
    $sql = "SELECT b.batch_no, b.amount, b.tran_date,
                iou.advance_amount iou_advance_amount, SUM(ioup.amount) iou_paid_amount, iou.company_code iou_company_code, iou.employee_code iou_employee_code, 
                SUM(pcp.amount) petty_cash_paid_amount, ph.company_code ph_company_code, ph.employee_code ph_employee_code, ph.id ph_id      
            FROM ".TB_PREF."i_owe_you_batches b 
                LEFT JOIN ".TB_PREF."i_owe_you iou ON 
                    iou.batch_no = b.batch_no 
                        LEFT JOIN ".TB_PREF."i_owe_you_payments ioup ON 
                            iou.id = ioup.iou_id 
                            AND ioup.expense_source = ".db_escape(ST_I_OWE_YOU)."   
                LEFT JOIN ".TB_PREF."petty_cash_header ph ON 
                    ph.batch_no = b.batch_no 
                        LEFT JOIN ".TB_PREF."i_owe_you_payments pcp ON 
                            ph.id = pcp.iou_id 
                            AND pcp.expense_source = ".db_escape(ST_PETTY_CASH)."   
            WHERE b.approval_status = ".db_escape(AS_APPROVED)
                ." AND b.project_id =".db_escape($project_id)
                ." AND b.tran_date >=".db_escape($from_date)
                ." AND b.tran_date <=".db_escape($to_date)." 
            GROUP BY b.batch_no ";
    
    return db_query($sql,"Could not get_petty_cash_expenses");
    
}


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";
    
    
    $sql_iou = "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 
                FROM ".TB_PREF."i_owe_you_payments p 
                    JOIN ".TB_PREF."i_owe_you iou ON 
                         iou.id = p.iou_id AND iou.project_id =".db_escape($project_id)."                 
                WHERE p.paid = 1 AND p.expense_source = ".db_escape(ST_I_OWE_YOU)." 
                        AND p.payment_date >=".db_escape($from_date)." AND p.payment_date <=".db_escape($to_date);
    
    
    $sql_ti = "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 
                FROM ".TB_PREF."i_owe_you_payments p 
                    JOIN ".TB_PREF."supplier_transport_invoices t ON 
                        t.payment_source_no = p.id AND t.project_id =".db_escape($project_id)."                                
                WHERE p.paid = 1 AND p.expense_source = ".db_escape(ST_SUPPLIER_TRANSPORT_INVOICES)." 
                        AND p.payment_date >=".db_escape($from_date)." AND p.payment_date <=".db_escape($to_date); 
    
    $sql_va = "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                    
                FROM ".TB_PREF."i_owe_you_payments p 
                    JOIN ".TB_PREF."vehicle_allocations va ON 
                         va.payment_source_no = p.id AND va.project_id =".db_escape($project_id)."                
                WHERE p.paid = 1 AND p.expense_source = ".db_escape(ST_VEHICLE_ALLOCATION)." 
                        AND p.payment_date >=".db_escape($from_date)." AND p.payment_date <=".db_escape($to_date);
    
    $sql_pc = "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                      
                FROM ".TB_PREF."i_owe_you_payments p 
                    JOIN ".TB_PREF."petty_cash_header pch ON 
                        pch.id = p.iou_id AND pch.project_id =".db_escape($project_id)."                         
                WHERE p.paid = 1 AND p.expense_source = ".db_escape(ST_PETTY_CASH)." 
                        AND p.payment_date >=".db_escape($from_date)." AND p.payment_date <=".db_escape($to_date);
    
    $sql_vrc = "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                      
                FROM ".TB_PREF."i_owe_you_payments p 
                    JOIN ".TB_PREF."vehicle_running_chart vrc ON 
                    vrc.payment_source_no = p.id AND vrc.project_id =".db_escape($project_id)."                                        
                WHERE p.paid = 1 AND p.expense_source = ".db_escape(ST_VEHICLE_RUNNING_CHART)." 
                        AND p.payment_date >=".db_escape($from_date)." AND p.payment_date <=".db_escape($to_date);
    
    
    $sql = $sql_iou." UNION ".$sql_ti." UNION ".$sql_va." UNION ".$sql_pc." UNION ".$sql_vrc;
    $sql .= " ORDER BY tran_date";
    
    return db_query($sql,"Could not get_petty_cash_expenses");
    
}

function get_iou_advances($project_id, $from_date, $to_date)
{

    $to_date = $to_date." 23:59:59";
    

    $sql = "SELECT iou.id, iou.reference, iou.tran_date, iou.company_code, iou.employee_code,
                    SUM(ioua.amount) total_advances  
            FROM ".TB_PREF."i_owe_you iou 
                JOIN ".TB_PREF."i_owe_you_advances_details ioua 
            WHERE iou.id = ioua.iou_id 
                AND iou.project_id =".db_escape($project_id)
                ." AND iou.tran_date >=".db_escape($from_date)
                ." AND iou.tran_date <=".db_escape($to_date)." 
            GROUP BY iou.id 
            ORDER BY iou.tran_date";
            

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

function get_iou_advances_details($project_id, $from_date, $to_date)
{

    $to_date = $to_date." 23:59:59";
    

    $sql = "SELECT iou.id, iou.reference, iou.tran_date, iou.company_code, iou.employee_code,
                    ioua.amount advance_amount,
                    ioua.batch_no 
            FROM ".TB_PREF."i_owe_you iou 
                JOIN ".TB_PREF."i_owe_you_advances_details ioua 
            WHERE iou.id = ioua.iou_id 
                AND iou.project_id =".db_escape($project_id)
                ." AND iou.tran_date >=".db_escape($from_date)
                ." AND iou.tran_date <=".db_escape($to_date)." 

            ORDER BY iou.tran_date";
            

    return db_query($sql,"Could not get_iou_advances_details");
    
}


function get_vehicle_running_chart_entries($project_id, $from_date, $to_date)
{
    $to_date = $to_date." 23:59:59";
    
    $sql = "SELECT vrc.*, vp.description package_name  
            FROM ".TB_PREF."vehicle_running_chart vrc 
                LEFT JOIN ".TB_PREF."vehicle_packages vp ON 
                    vrc.package_id = vp.id 
            WHERE vrc.batch_no > 0 
                AND vrc.project_id =".db_escape($project_id)
                ." AND vrc.end_date_time >=".db_escape($from_date)
                ." AND vrc.end_date_time <=".db_escape($to_date)." 
            ORDER By vrc.end_date_time";
    
//    SELECT p.amount, vrc.* 
//            FROM 0_vehicle_running_chart vrc 
//            JOIN 0_i_owe_you_payments p ON vrc.payment_source_no = p.id AND p.expense_source = '127' WHERE vrc.payment_source_type = '154' AND vrc.project_id ='107' AND vrc.end_date_time >='2017-12-01' AND vrc.end_date_time <='2018-12-31' ORDER By vrc.id
//    
    return db_query($sql,"Could not get_vehicle_running_chart_entries");
    
}


function get_project_cost_adjustments($project_id, $from_date, $to_date)
{
    $to_date = $to_date." 23:59:59";
    
    $sql = "SELECT *  
            FROM ".TB_PREF."task_cost_adjustments   
            WHERE  project_id =".db_escape($project_id)
                ." AND tran_date >=".db_escape($from_date)
                ." AND tran_date <=".db_escape($to_date)." 
            ORDER By tran_date";
    
//    SELECT p.amount, vrc.* 
//            FROM 0_vehicle_running_chart vrc 
//            JOIN 0_i_owe_you_payments p ON vrc.payment_source_no = p.id AND p.expense_source = '127' WHERE vrc.payment_source_type = '154' AND vrc.project_id ='107' AND vrc.end_date_time >='2017-12-01' AND vrc.end_date_time <='2018-12-31' ORDER By vrc.id
//    
    return db_query($sql,"Could not get_vehicle_running_chart_entries");
    
}



function get_monthly_project_costs($project_id, $from_date, $to_date)
{
    $to_date = $to_date." 23:59:59";
    
    $sql = "SELECT mpc.reference, mpc.date,
                    mpcd.* 
            FROM ".TB_PREF."monthly_project_costs mpc  
                JOIN ".TB_PREF."monthly_project_costs_details mpcd 
                    ON mpc.id = mpcd.mpc_id 
            WHERE mpc.approval_status =  ".db_escape(AS_APPROVED)." 
                AND mpcd.project_id =".db_escape($project_id)
                ." AND mpc.date >=".db_escape($from_date)
                ." AND mpc.date <=".db_escape($to_date)." 
            ORDER By mpc.id";
    
//    SELECT p.amount, vrc.* 
//            FROM 0_vehicle_running_chart vrc 
//            JOIN 0_i_owe_you_payments p ON vrc.payment_source_no = p.id AND p.expense_source = '127' WHERE vrc.payment_source_type = '154' AND vrc.project_id ='107' AND vrc.end_date_time >='2017-12-01' AND vrc.end_date_time <='2018-12-31' ORDER By vrc.id
//    
    return db_query($sql,"get_monthly_project_costs");
    
}

function get_miscellaneous_project_costs($project_id, $from_date, $to_date)
{
    $to_date = $to_date." 23:59:59";
    
    $sql = "SELECT mpc.* 
            FROM ".TB_PREF."miscellaneous_project_costs mpc  
            WHERE mpc.approval_status =  ".db_escape(AS_APPROVED)." 
                AND mpc.project_id =".db_escape($project_id)
                ." AND mpc.date >=".db_escape($from_date)
                ." AND mpc.date <=".db_escape($to_date)." 
            ORDER By mpc.id";
    
//    SELECT p.amount, vrc.* 
//            FROM 0_vehicle_running_chart vrc 
//            JOIN 0_i_owe_you_payments p ON vrc.payment_source_no = p.id AND p.expense_source = '127' WHERE vrc.payment_source_type = '154' AND vrc.project_id ='107' AND vrc.end_date_time >='2017-12-01' AND vrc.end_date_time <='2018-12-31' ORDER By vrc.id
//    
    return db_query($sql,"Could not get_miscellaneous_project_costs");
    
}

function get_wip_costs($project_id)
{
//    $to_date = $to_date." 23:59:59";
    
    $sql = "SELECT wip.*,
                tn.value_name wip_type_name 
            FROM ".TB_PREF."project_wip_costs wip 
                JOIN ".TB_PREF."list_values tn ON 
                    wip.wip_type_id = tn.id 
            WHERE wip.project_id =".db_escape($project_id)." AND 
                wip.value > 0 
            ORDER By tn.value_name";

    return db_query($sql,"Could not get_wip_costs");
    
}


function get_project_invoices_certified($type, $project_id, $from_date, $to_date)
{    
    $sql = "SELECT DISTINCT dt.trans_no inv_no,
                            dt.*, 
                            dm.name cust_name,
                            pi.comments  
            FROM ".TB_PREF."debtor_trans dt 
                JOIN ".TB_PREF."project_invoices pi ON 
                    dt.trans_no = pi.order_no  
                JOIN ".TB_PREF."debtors_master dm ON 
                    dm.debtor_no = pi.debtor_no  
            WHERE 
                dt.type = ".db_escape($type)
                    ." AND pi.project_id = ".db_escape($project_id)
                    ." AND dt.tran_date >=".db_escape($from_date)
                    ." AND dt.tran_date <=".db_escape($to_date)." 
            ORDER BY dt.trans_no ";
    
    return db_query($sql,"get_project_invoices_certified");
    
}


function get_project_credit_notes_certified($type, $project_id, $from_date, $to_date)
{    
    $sql = "SELECT DISTINCT dt.trans_no inv_no,
                            dt.*, 
                            dm.name cust_name,
                            pcn.comment  
            FROM ".TB_PREF."debtor_trans dt 
                JOIN ".TB_PREF."project_credit_notes pcn ON 
                    dt.trans_no = pcn.order_no  
                JOIN ".TB_PREF."debtors_master dm ON 
                    dm.debtor_no = pcn.debtor_no  
            WHERE 
                dt.type = ".db_escape($type)
                    ." AND pcn.project_id = ".db_escape($project_id)
                    ." AND dt.tran_date >=".db_escape($from_date)
                    ." AND dt.tran_date <=".db_escape($to_date)." 
            ORDER BY dt.trans_no ";
    
    return db_query($sql,"get_project_credit_notes_certified");
    
}

function get_project_invoices_and_credit_notes($project_id, $from_date, $to_date)
{    
    $sql1 = "SELECT dt.trans_no trans_no,
                    dt.tran_date tran_date, 
                    dt.reference,
                    dt.type,
                    dt.ov_amount,
                    dt.ov_gst,
                    dt.ov_freight,
                    dt.ov_freight_tax,
                    dt.ov_discount,                    
                    dt.alloc,
                    dm.name cust_name,
                    pi.comments  
            FROM ".TB_PREF."debtor_trans dt 
                JOIN ".TB_PREF."project_invoices pi ON 
                    dt.trans_no = pi.order_no  
                JOIN ".TB_PREF."debtors_master dm ON 
                    dm.debtor_no = pi.debtor_no  
            WHERE 
                dt.type = ".db_escape(ST_PROJECT_INVOICE)
                    ." AND pi.project_id = ".db_escape($project_id)
                    ." AND dt.tran_date >=".db_escape($from_date)
                    ." AND dt.tran_date <=".db_escape($to_date);
    
    
    $sql2 = "SELECT dt2.trans_no trans_no,
                    dt2.tran_date tran_date, 
                    dt2.reference,
                    dt2.type,
                    dt2.ov_amount,
                    dt2.ov_gst,
                    dt2.ov_freight,
                    dt2.ov_freight_tax,
                    dt2.ov_discount,
                    dt2.alloc,
                    dm.name cust_name,
                    pcn.comment  
            FROM ".TB_PREF."debtor_trans dt2 
                JOIN ".TB_PREF."project_credit_notes pcn ON 
                    dt2.trans_no = pcn.order_no  
                JOIN ".TB_PREF."debtors_master dm ON 
                    dm.debtor_no = pcn.debtor_no  
            WHERE 
                dt2.type = ".db_escape(ST_PROJECT_CREDIT_NOTE)
                    ." AND pcn.project_id = ".db_escape($project_id)
                    ." AND dt2.tran_date >=".db_escape($from_date)
                    ." AND dt2.tran_date <=".db_escape($to_date)." 
            ORDER BY tran_date, trans_no";
    
    $sql = $sql1." UNION ".$sql2;
    
    return db_query($sql,"get_project_invoices_and_credit_notes");
    
}


function get_project_invoices_pending($project_id, $from_date, $to_date)
{    
    $sql = "SELECT pi.order_no inv_no,
                            pi.*, 
                            dm.name cust_name,
                            pi.comments  
            FROM ".TB_PREF."project_invoices pi 
                JOIN ".TB_PREF."debtors_master dm ON 
                    dm.debtor_no = pi.debtor_no  
            WHERE 
                pi.approval_status IN (".AS_NEW.",".AS_AWAITING_APPROVAL.",".AS_BACK_TO_ORIGINATOR.",".AS_WITH_THE_APPROVER.") "
                    ." AND pi.project_id = ".db_escape($project_id)
                    ." AND pi.ord_date >=".db_escape($from_date)
                    ." AND pi.ord_date <=".db_escape($to_date)." 
            ORDER BY pi.order_no ";
    
    return db_query($sql,"get_project_invoices_certified");
    
}

function get_project_credit_notes_pending($project_id, $from_date, $to_date)
{    
    $sql = "SELECT pcn.order_no inv_no,
                            pcn.*, 
                            dm.name cust_name,
                            pcn.comment  
            FROM ".TB_PREF."project_credit_notes pcn 
                JOIN ".TB_PREF."debtors_master dm ON 
                    dm.debtor_no = pcn.debtor_no  
            WHERE 
                pcn.approval_status IN (".AS_NEW.",".AS_AWAITING_APPROVAL.",".AS_BACK_TO_ORIGINATOR.",".AS_WITH_THE_APPROVER.") "
                    ." AND pcn.project_id = ".db_escape($project_id)
                    ." AND pcn.ord_date >=".db_escape($from_date)
                    ." AND pcn.ord_date <=".db_escape($to_date)." 
            ORDER BY pcn.order_no ";
    
    return db_query($sql,"get_project_credit_notes_pending");
    
}

function get_utility_bills($type, $project_id, $from_date, $to_date)
{
    $sql = "SELECT DISTINCT st.trans_no inv_no,
                            st.*, 
                            supp.supp_name 
            FROM ".TB_PREF."supp_trans st 
                JOIN ".TB_PREF."utility_bills ub ON 
                    st.trans_no = ub.id   
                JOIN ".TB_PREF."suppliers supp ON 
                    st.supplier_id = supp.supplier_id 
            WHERE 
                st.type = ".db_escape($type)
                ." AND ub.project_id =".db_escape($project_id)
                ." AND st.tran_date >=".db_escape($from_date)
                ." AND st.tran_date <=".db_escape($to_date)." 
            ORDER BY st.trans_no";
    
    return db_query($sql,"get_utility_bills");
    
}


function get_utility_bill_credit_notes($type, $project_id, $from_date, $to_date)
{
    $sql = "SELECT DISTINCT st.trans_no inv_no,
                            st.*, 
                            supp.supp_name 
            FROM ".TB_PREF."supp_trans st 
                JOIN ".TB_PREF."utility_bill_credit_notes ubc ON 
                    st.trans_no = ubc.id   
                JOIN ".TB_PREF."suppliers supp ON 
                    st.supplier_id = supp.supplier_id 
            WHERE 
                st.type = ".db_escape($type)
                ." AND ubc.project_id =".db_escape($project_id)
                ." AND st.tran_date >=".db_escape($from_date)
                ." AND st.tran_date <=".db_escape($to_date)." 
            ORDER BY st.trans_no";
    
    return db_query($sql,"get_utility_bill_credit_notes");
    
}


function get_tool_usage_charges($type, $project_id, $from_date, $to_date)
{
    $sql = "SELECT ti.id trans_no, ti.tran_date, ti.reference, SUM(tucd.no_of_units * tucd.rate) total,
                ti.project_id, ti.company_code, ti.employee_code  
            FROM ".TB_PREF."tool_usage_charge_details tucd 
                JOIN ".TB_PREF."tool_issue_details tid   
                    ON tid.id = tucd.issue_detail_id
                JOIN ".TB_PREF."tool_issues ti    
                    ON ti.id = tid.ai_id 
                JOIN ".TB_PREF."tool_usage_charges tuc 
                    ON tuc.id = tucd.charge_no  
            WHERE ti.project_id = ".db_escape($project_id)
                ." AND tuc.cutoff_date >=".db_escape($from_date)
                ." AND tuc.cutoff_date <=".db_escape($to_date)."   
            GROUP BY ti.id 
            ORDER BY ti.id";
    
    return db_query($sql,"get_utility_bill_credit_notes");
    
}

function get_site_material_returns($project_id, $from_date, $to_date){
    $sql = "SELECT * 
            FROM ".TB_PREF."site_material_returns 
            WHERE status = ".db_escape(AS_PROCESSED)." 
                AND from_project_id = ".db_escape($project_id)." 
                AND tran_date >= ".db_escape($from_date)." 
                AND tran_date <= ".db_escape($to_date)." 
            ORDER BY id";
    
    return db_query($sql," could not get_site_material_returns");
}

function get_pr_issues($project_id, $from_date, $to_date){
    $sql = "SELECT pri.*, 
                    sup.supp_name 
            FROM ".TB_PREF."purchase_requisition_issues pri  
                LEFT JOIN ".TB_PREF."purch_orders po  
                    ON po.order_no = pri.po_no  
                LEFT JOIN ".TB_PREF."suppliers sup  
                    ON sup.supplier_id = po.supplier_id  
            WHERE pri.project_id = ".db_escape($project_id)." 
                AND pri.created_on >= ".db_escape($from_date)." 
                AND pri.created_on <= ".db_escape($to_date)." 
            ORDER BY pri.pri_no";
    
    return db_query($sql," could not get_pr_issues_returns");
}

function get_pr_returns($project_id, $from_date, $to_date){
    $sql = "SELECT prr.*, 
                    sup.supp_name 
            FROM ".TB_PREF."purchase_requisition_returns prr  
                LEFT JOIN ".TB_PREF."supp_trans st   
                    ON st.type = ".ST_SUPPCREDIT."  
                    AND st.trans_no = prr.cn_no   
                LEFT JOIN ".TB_PREF."suppliers sup  
                    ON sup.supplier_id = st.supplier_id  
            WHERE prr.project_id = ".db_escape($project_id)." 
                AND prr.prr_date >= ".db_escape($from_date)." 
                AND prr.prr_date <= ".db_escape($to_date)." 
            ORDER BY prr.prr_no";
    
    return db_query($sql," could not get_pr_returns");
}



function get_subcontractor_invoice_advance_paid_details($project_id, $subcontractor_id, $line_category_in, $from_date, $to_date)
{    
    $sql = "SELECT i.*, SUM(d.amount) amount,
                    supp.supp_name  
            FROM ".TB_PREF."subcontractor_invoices i 
                JOIN ".TB_PREF."subcontractor_invoice_details d 
                    ON d.order_no = i.order_no 
                JOIN ".TB_PREF."suppliers supp 
                    ON i.supplier_id = supp.supplier_id 
                JOIN ".TB_PREF."list_values lc  
                    ON lc.id = d.category_id 
            WHERE i.project_id = ".db_escape($project_id)." 
                AND i.supplier_id =".db_escape($subcontractor_id)." 
                AND lc.value_code IN (".$line_category_in.") 
                AND i.due_date >= ".db_escape($from_date)."  
                AND i.due_date <= ".db_escape($to_date);
    
    $status_array = array(AS_NEW, AS_AWAITING_APPROVAL, AS_WITH_THE_APPROVER, AS_BACK_TO_ORIGINATOR, AS_APPROVED);
    $sql .= " AND i.approval_status IN(". implode(",", $status_array).")";
    
//    $sql .= " ORDER BY i.ord_date DESC LIMIT 1 ";
    $sql .= " GROUP BY i.order_no  ORDER BY i.ord_date ";
    
//    return db_query($sql,"get_subcontractor_invoice_advance_paid_details");
    
    return db_query($sql,"get_subcontractor_invoice_advance_paid_details");
    
//    return db_fetch($result);
    
}


function get_subcontractor_credit_note_advance_returned_details($project_id, $subcontractor_id, $line_category_in)
{    
    $sql = "SELECT i.*, SUM(d.amount) amount,
                    supp.supp_name  
            FROM ".TB_PREF."subcontractor_credit_notes i 
                JOIN ".TB_PREF."subcontractor_credit_note_details d 
                    ON d.order_no = i.order_no 
                JOIN ".TB_PREF."suppliers supp 
                    ON i.supplier_id = supp.supplier_id 
                JOIN ".TB_PREF."list_values lc  
                    ON lc.id = d.category_id 
            WHERE i.project_id = ".db_escape($project_id)." 
                AND i.supplier_id =".db_escape($subcontractor_id)." 
                AND lc.value_code IN (".$line_category_in.")";
    
    $status_array = array(AS_NEW, AS_AWAITING_APPROVAL, AS_WITH_THE_APPROVER, AS_BACK_TO_ORIGINATOR, AS_APPROVED);
    $sql .= " AND i.approval_status IN(". implode(",", $status_array).")";
    
//    $sql .= " ORDER BY i.ord_date DESC LIMIT 1 ";
    $sql .= " GROUP BY i.order_no  ORDER BY i.ord_date ";
    
//    return db_query($sql,"get_subcontractor_invoice_advance_paid_details");
    
    return db_query($sql,"get_subcontractor_invoice_advance_paid_details");
    
//    return db_fetch($result);
    
}


function get_project_invoice_subcontractors($project_id, $line_category_in)
{
    $sql = "SELECT DISTINCT supplier_id  
            FROM ".TB_PREF."subcontractor_invoices subi  
                JOIN ".TB_PREF."subcontractor_invoice_details subid ON 
                    subid.order_no = subi.order_no      
                JOIN ".TB_PREF."list_values lc  
                    ON lc.id = subid.category_id 
            WHERE subi.project_id =".db_escape($project_id)."                 
                AND lc.value_code IN (".$line_category_in.")"; 
    
    return db_query($sql,"get_project_invoice_subcontractors");
    
}

function get_project_credit_note_subcontractors($project_id, $line_category_in)
{
    $sql = "SELECT DISTINCT supplier_id  
            FROM ".TB_PREF."subcontractor_credit_notes subi  
                JOIN ".TB_PREF."subcontractor_credit_note_details subid ON 
                    subid.order_no = subi.order_no      
                JOIN ".TB_PREF."list_values lc  
                    ON lc.id = subid.category_id 
            WHERE subi.project_id =".db_escape($project_id)."                 
                AND lc.value_code IN (".$line_category_in.")"; 
    
    return db_query($sql,"get_project_credit_note_subcontractors");
    
}


	
function get_iou_bank_payments($project_id, $from_date, $to_date, $tran_type)
{
    $sql= "SELECT bt.*  
           FROM ".TB_PREF."bank_trans bt   
                JOIN ".TB_PREF."i_owe_you iou   
                    ON bt.person_id = iou.id 
           WHERE (bt.type =".db_escape($tran_type).")  
                AND bt.person_type_id = ".db_escape(PT_IOU)."  
                AND bt.trans_date >= ".db_escape($from_date)." 
                AND bt.trans_date <= ".db_escape($to_date)." 
                AND iou.project_id = ".db_escape($project_id);
    
        
    return db_query($sql,"get_iou_bank_payments");	
    
}



function get_project_petty_cash_expenses($project_id, $from_date, $to_date, $approval_status_array) {
    $sql = "SELECT iou_b.batch_no, iou_b.amount, iou_b.tran_date, iou_b.approval_status, iou_b.complete, pcf.name float_name 
            FROM ".TB_PREF."i_owe_you_batches iou_b  
                JOIN ".TB_PREF."petty_cash_floats pcf  
                    ON iou_b.float_id = pcf.id       
            WHERE iou_b.project_id = ".db_escape($project_id)."                 
                AND iou_b.tran_date >= ".db_escape($from_date)."                 
                AND iou_b.tran_date <= ".db_escape($to_date)."                 
                AND iou_b.approval_status IN (". implode(',', $approval_status_array).")
            ORDER BY iou_b.tran_date, iou_b.batch_no";
    
    return db_query($sql, "get_project_petty_cash_expenses");
}