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


function get_project_credit_notes_pending_summary($project_id, $from_date, $to_date)
{    
    $sql = "SELECT SUM(total) total_amount 
            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);
    
    $result = db_query($sql,"get_project_credit_notes_pending_summary");
    
    $row = db_fetch($result);
    if(isset($row['total_amount']) && $row['total_amount'] != 0){
        return $row['total_amount'];
    }
    else{
        return 0;
    }
    
}


function get_project_invoices_pending_summary($project_id, $from_date, $to_date)
{    
    $sql = "SELECT SUM(total) total_amount
            FROM ".TB_PREF."project_invoices pi 
            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);
    
    $result = db_query($sql,"get_project_invoices_pending_summary");
    
    $row = db_fetch($result);
    if(isset($row['total_amount']) && $row['total_amount'] != 0){
        return $row['total_amount'];
    }
    else{
        return 0;
    }
    
}


function get_project_credit_notes_certified_summary($type, $project_id, $from_date, $to_date)
{    
    $sql = "SELECT SUM(ov_amount - ov_discount) total_amount  
            FROM ".TB_PREF."debtor_trans dt 
                JOIN ".TB_PREF."project_credit_notes pcn ON 
                    dt.trans_no = pcn.order_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);
    
    $result = db_query($sql,"get_project_credit_notes_certified_summary");
    
    $row = db_fetch($result);
    if(isset($row['total_amount']) && $row['total_amount'] != 0){
        return $row['total_amount'];
    }
    else{
        return 0;
    }
}


function get_project_credit_notes_certified_summary_with_taxes($type, $project_id, $from_date, $to_date)
{    
    $sql = "SELECT SUM(ov_amount + ov_gst + ov_freight + ov_freight_tax - ov_discount) total_amount  
            FROM ".TB_PREF."debtor_trans dt 
                JOIN ".TB_PREF."project_credit_notes pcn ON 
                    dt.trans_no = pcn.order_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);
    
    $result = db_query($sql,"get_project_credit_notes_certified_summary_with_taxes");
    
    $row = db_fetch($result);
    if(isset($row['total_amount']) && $row['total_amount'] != 0){
        return $row['total_amount'];
    }
    else{
        return 0;
    }
}


function get_project_invoices_certified_summary($type, $project_id, $from_date, $to_date)
{    
    $sql = "SELECT SUM(ov_amount - ov_discount) total_amount
            FROM ".TB_PREF."debtor_trans dt 
                JOIN ".TB_PREF."project_invoices pi ON 
                    dt.trans_no = pi.order_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);
    
    $result = db_query($sql,"get_project_invoices_certified_summary");
    
    $row = db_fetch($result);
    if(isset($row['total_amount']) && $row['total_amount'] != 0){
        return $row['total_amount'];
    }
    else{
        return 0;
    }
    
}

function get_project_invoices_certified_summary_with_taxes($type, $project_id, $from_date, $to_date)
{    
    $sql = "SELECT SUM(ov_amount + ov_gst + ov_freight + ov_freight_tax - ov_discount) total_amount
            FROM ".TB_PREF."debtor_trans dt 
                JOIN ".TB_PREF."project_invoices pi ON 
                    dt.trans_no = pi.order_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);
    
    $result = db_query($sql,"get_project_invoices_certified_summary_with_taxes");
    
    $row = db_fetch($result);
    if(isset($row['total_amount']) && $row['total_amount'] != 0){
        return $row['total_amount'];
    }
    else{
        return 0;
    }
    
}

function get_project_invoices_retentions_summary($type, $project_id, $from_date, $to_date, $line_category_in)
{    
    $sql = "SELECT pi.order_no,  pid.amount
            FROM ".TB_PREF."project_invoices pi 
                JOIN ".TB_PREF."project_invoice_details pid ON 
                    pi.order_no = pid.order_no   
                JOIN ".TB_PREF."list_values lcn ON 
                    lcn.id = pid.category_id AND 
                    lcn.value_code IN (".$line_category_in.") 
            WHERE pi.project_id = ".db_escape($project_id)
                ." AND pi.ord_date >=".db_escape($from_date)
                ." AND pi.ord_date <=".db_escape($to_date);
    
//    $sql .= " AND pi.approval_status = ".db_escape(AS_APPROVED);
    
    $sql .= " ORDER BY pi.ord_date DESC LIMIT 1 ";
    

    
    $result = db_query($sql,"get_project_invoices_retentions_summary");
    
    return db_fetch($result);
    
}

function get_subcontractor_invoice_advance_paid($project_id, $subcontractor_id, $line_category_in, $from_date, $to_date)
{    
    $sql = "SELECT d.amount
            FROM ".TB_PREF."subcontractor_invoices i 
                JOIN ".TB_PREF."subcontractor_invoice_details d 
                    ON d.order_no = i.order_no 
                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);
    
//    $sql .= " AND pi.approval_status = ".db_escape(AS_APPROVED);
    
//    $sql .= " ORDER BY i.ord_date DESC LIMIT 1 ";
    $sql .= " ORDER BY i.ord_date ";
    

    
//    $result = db_query($sql,"get_subcontractor_invoice_advance_paid");
//    
//    return db_result_value($result, "amount");
    return db_query($sql,"get_subcontractor_invoice_advance_paid");
    
}



function get_material_issues_summary($project_id, $from_date, $to_date){
    
    $sql = "SELECT 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);
    
    $result = db_query($sql,"Could not get_material_issues");
    
    $row = db_fetch($result);
    if(isset($row['total_cost']) && $row['total_cost'] != 0){
        return $row['total_cost'];
    }
    else{
        return 0;
    }
            
}

function get_material_returns_summary($project_id, $from_date, $to_date)
{
$sql = "SELECT 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);

    $result = db_query($sql,"get_material_returns_summary");
    
    $row = db_fetch($result);
    if(isset($row['total_cost']) && $row['total_cost'] != 0){
        return $row['total_cost'];
    }
    else{
        return 0;
    }

}


function get_daily_item_issues_summary($project_id, $from_date, $to_date, $type){
    
    $sql = "SELECT 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 
            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);
    
    $result = db_query($sql,"get_daily_item_issues_summary");
    
    $row = db_fetch($result);
    if(isset($row['total_cost']) && $row['total_cost'] != 0){
        return $row['total_cost'];
    }
    else{
        return 0;
    }
            
}


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 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 
            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);
            
    $result = db_query($sql,"get_material_purchased_invoices");
    return db_result_value($result, "amount");
    
}

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 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 
            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);
            
    $result = db_query($sql,"get_material_purchased_invoices");
    return db_result_value($result, "amount");
    
}


function get_subcontractor_invoices_summary($type, $project_id, $from_date, $to_date)
{
    $sql = "SELECT SUM(ov_amount - ov_discount) total_cost 
            FROM ".TB_PREF."supp_trans st 
                JOIN ".TB_PREF."subcontractor_invoices subi ON 
                    st.trans_no = subi.order_no  
            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);
    
    $result = db_query($sql,"get_subcontractor_invoices_summary");
    
    $row = db_fetch($result);
    if(isset($row['total_cost']) && $row['total_cost'] != 0){
        return $row['total_cost'];
    }
    else{
        return 0;
    }
    
}


function get_subcontractor_credit_notes_summary($type, $project_id, $from_date, $to_date)
{
    $sql = "SELECT SUM(ov_amount - ov_discount) total_cost 
            FROM ".TB_PREF."supp_trans st 
                JOIN ".TB_PREF."subcontractor_credit_notes subi ON 
                    st.trans_no = subi.order_no 
            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);
    
    $result = db_query($sql,"get_subcontractor_credit_notes_summary");
    
    $row = db_fetch($result);
    if(isset($row['total_cost']) && $row['total_cost'] != 0){
        return $row['total_cost'];
    }
    else{
        return 0;
    }
    
}


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_summary($project_id, $from_date, $to_date)
{

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

    $sql = "SELECT SUM(ioua.amount) total_cost 
            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);
            

    $result = db_query($sql,"get_iou_advances_summary");
    
    $row = db_fetch($result);
    if(isset($row['total_cost']) && $row['total_cost'] != 0){
        return $row['total_cost'];
    }
    else{
        return 0;
    }
    
}


function get_vehicle_running_chart_entries_summary($project_id, $from_date, $to_date)
{
    $to_date = $to_date." 23:59:59";
    
    $sql = "SELECT SUM(vrc.cost) total_cost 
            FROM ".TB_PREF."vehicle_running_chart vrc  
            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);
    
    $result = db_query($sql,"get_vehicle_running_chart_entries_summary");
    
    $row = db_fetch($result);
    if(isset($row['total_cost']) && $row['total_cost'] != 0){
        return $row['total_cost'];
    }
    else{
        return 0;
    }
    
}

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

    $sql = "SELECT SUM(amount) total_cost 
            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);
    
    $result = db_query($sql,"get_vehicle_running_chart_entries_summary");
    
    return db_result_value($result, 'total_cost');
    
}


function get_monthly_project_costs_summary($project_id, $from_date, $to_date)
{
    $to_date = $to_date." 23:59:59";
    
    $sql = "SELECT SUM(mpcd.value) total_cost 
            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);
    
   
    $result = db_query($sql,"get_monthly_project_costs_summary");
    
    $row = db_fetch($result);
    if(isset($row['total_cost']) && $row['total_cost'] != 0){
        return $row['total_cost'];
    }
    else{
        return 0;
    }
    
}


function get_miscellaneous_project_costs_summary($project_id, $from_date, $to_date)
{
    $to_date = $to_date." 23:59:59";
    
    $sql = "SELECT SUM(mpc.total) total_cost 
            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);
    
   
    $result = db_query($sql,"get_miscellaneous_project_costs_summary");
    
    $row = db_fetch($result);
    if(isset($row['total_cost']) && $row['total_cost'] != 0){
        return $row['total_cost'];
    }
    else{
        return 0;
    }
    
}

function get_wip_costs_summary($project_id)
{
//    $to_date = $to_date." 23:59:59";
    
    $sql = "SELECT SUM(wip.value) total_cost  
            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 ";

    $result = db_query($sql,"get_wip_costs_summary");
    
    $row = db_fetch($result);
    if(isset($row['total_cost']) && $row['total_cost'] != 0){
        return $row['total_cost'];
    }
    else{
        return 0;
    }
    
}

function get_utility_bills_summary($type, $project_id, $from_date, $to_date)
{
    $sql = "SELECT SUM(ov_amount - ov_discount) total_cost 
            FROM ".TB_PREF."supp_trans st 
                JOIN ".TB_PREF."utility_bills ub ON 
                    st.trans_no = ub.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);
    
    $result = db_query($sql,"get_utility_bills_summary");
    
    $row = db_fetch($result);
    if(isset($row['total_cost']) && $row['total_cost'] != 0){
        return $row['total_cost'];
    }
    else{
        return 0;
    }
    
}

function get_utility_bill_credit_notes_summary($type, $project_id, $from_date, $to_date)
{
    $sql = "SELECT SUM(ov_amount - ov_discount) total_cost 
            FROM ".TB_PREF."supp_trans st 
                JOIN ".TB_PREF."utility_bill_credit_notes ubc ON 
                    st.trans_no = ubc.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);
    
    $result = db_query($sql,"get_utility_bill_credit_notes_summary");
    
    $row = db_fetch($result);
    if(isset($row['total_cost']) && $row['total_cost'] != 0){
        return $row['total_cost'];
    }
    else{
        return 0;
    }
    
}


function get_tool_usage_charges_summary($project_id, $from_date, $to_date)
{
    $sql = "SELECT SUM(tucd.no_of_units * tucd.rate) total 
            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);
    
    $result = db_query($sql,"get_tool_usage_charges_summary");
    
    return db_result_value($result, "total");
    
}


function get_material_requisitions_summary($project_id, $from_date, $to_date){
    
    $sql = "SELECT SUM(tca.amount) total_allocation 
            FROM ".TB_PREF."material_requisitions mr  
                    JOIN ".TB_PREF."task_cost_allocations tca ON 
                            tca.tran_no = mr.mr_no 
            WHERE mr.project_id =".db_escape($project_id)
                ." AND mr.mr_date >=".db_escape($from_date)
                ." AND mr.mr_date <=".db_escape($to_date)
                ." AND tca.tran_type = ".db_escape(ST_MATERIAL_REQ);
                 
    
    $result = db_query($sql,"get_material_requisitions_summary");
    
    return db_result_value($result, 'total_allocation');
            
}


function get_purchase_requisitions_summary($project_id, $from_date, $to_date){
    
    $sql = "SELECT SUM(tca.amount) total_allocation 
            FROM ".TB_PREF."purchase_requisitions pr  
                    JOIN ".TB_PREF."task_cost_allocations tca ON 
                            tca.tran_no = pr.pr_no 
            WHERE pr.project_id =".db_escape($project_id)
                ." AND pr.pr_date >=".db_escape($from_date)
                ." AND pr.pr_date <=".db_escape($to_date)
                ." AND tca.tran_type = ".db_escape(ST_PURCH_REQ);
                 
    
    $result = db_query($sql,"get_purchase_requisitions_summary");
    
    return db_result_value($result, 'total_allocation');
            
}


function get_subcontractor_purchase_orders_summary($project_id, $from_date, $to_date){
    
    $sql = "SELECT SUM(tca.amount) total_allocation 
            FROM ".TB_PREF."subcontractor_purchase_orders po  
                    JOIN ".TB_PREF."task_cost_allocations tca ON 
                            tca.tran_no = po.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(ST_SUBCONTRACTOR_PURCH_ORDER);
                 
    
    $result = db_query($sql,"get_subcontractor_purchase_orders_summary");
    
    return db_result_value($result, 'total_allocation');
            
}


function get_i_owe_yous_summary($project_id, $from_date, $to_date){
    
    $sql = "SELECT SUM(tca.amount) total_allocation 
            FROM ".TB_PREF."i_owe_you iou  
                JOIN ".TB_PREF."task_cost_allocations tca ON 
                            tca.tran_no = iou.id 
            WHERE iou.project_id =".db_escape($project_id)
                ." AND iou.tran_date >=".db_escape($from_date)
                ." AND iou.tran_date <=".db_escape($to_date) 
                ." AND tca.tran_type = ".db_escape(ST_I_OWE_YOU);
                 
    
    $result = db_query($sql,"get_i_owe_yous_summary");
    
    return db_result_value($result, 'total_allocation');
            
}

function get_vehicle_requests_summary($project_id, $from_date, $to_date){
    
    $to_date = $to_date." 23:59:59";
    
    $sql = "SELECT SUM(tca.amount) total_allocation 
            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(ST_VEHICLE_REQUEST);                 
    
    $result = db_query($sql,"get_vehicle_requests_summary");
    
    return db_result_value($result, 'total_allocation');
            
}



//function get_project_collections_summary($project_id, $from_date, $to_date)
//{    
//    $sql ="SELECT SUM(pymt.ov_amount) total_amount  
//            FROM 0_debtor_trans pymt 
//                JOIN 0_cust_allocations ca 
//                    ON pymt.type = ca.trans_type_from 
//                    AND pymt.trans_no = ca.trans_no_from 
//                JOIN 0_debtor_trans inv 
//                    ON inv.type = ca.trans_type_to 
//                    AND inv.trans_no = ca.trans_no_to 
//            WHERE pymt.type IN(".db_escape(ST_CUSTPAYMENT).") 
//                AND inv.type IN(".db_escape(ST_PROJECT_INVOICE).")  
//                AND inv.project_id = ".db_escape($project_id)." 
//                AND inv.tran_date >=".db_escape($from_date)." 
//                AND inv.tran_date <=".db_escape($to_date)." 
//            GROUP BY inv.project_id ";
    
    
function get_project_invoices_collection_summary($project_id, $from_date, $to_date)
{ 
    
    $sql = "SELECT SUM(dt.alloc) collection_total 
            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);
    
    $result = db_query($sql,"get_project_invoices_collection_summary");
    
    return db_result_value($result, "collection_total");
    
}

function get_project_credit_notes_payments_summary($project_id, $from_date, $to_date)
{ 
    $sql = "SELECT SUM(dt2.alloc) collection_total 
            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";
    
    
    $result = db_query($sql,"get_project_credit_notes_payments_summary");
    
    return db_result_value($result, "collection_total");
    
}


function get_project_invoice_receipts_summary($project_id, $month_begin_date, $month_end_date)
{
    $sql = "SELECT SUM(dt.alloc) total 
            FROM ".TB_PREF."project_invoices pi 
                    JOIN ".TB_PREF."debtor_trans dt 
                        ON pi.order_no = dt.trans_no 
                        AND dt.type = ".db_escape(ST_PROJECT_INVOICE)."  
            WHERE pi.ord_date >= ".db_escape($month_begin_date)." 
                AND pi.ord_date <= ".db_escape($month_end_date)."
                AND pi.approval_status = ".db_escape(AS_APPROVED)." 
                AND pi.project_id = ".db_escape($project_id);
    
    $result = db_query($sql);
    return db_result_value($result, "total");
}



function get_project_credit_note_payments_summary($project_id, $month_begin_date, $month_end_date)
{
    $sql = "SELECT SUM(dt.alloc) total 
            FROM ".TB_PREF."project_credit_notes pi 
                    JOIN ".TB_PREF."debtor_trans dt 
                        ON pi.order_no = dt.trans_no 
                        AND dt.type = ".db_escape(ST_PROJECT_CREDIT_NOTE)."  
            WHERE pi.ord_date >= ".db_escape($month_begin_date)." 
                AND pi.ord_date <= ".db_escape($month_end_date)."
                AND pi.approval_status = ".db_escape(AS_APPROVED)." 
                AND pi.project_id = ".db_escape($project_id);
    
    $result = db_query($sql);
    return db_result_value($result, "total");
}



function get_project_subcontractor_invoice_payments_summary($project_id, $month_begin_date, $month_end_date)
{
    
    $sql = "SELECT SUM(amt) payments   
            FROM ".TB_PREF."supp_allocations alloc 
                JOIN ".TB_PREF."vw_all_supp_cash_outs vw 
                    ON alloc.trans_type_from = vw.trans_type 
                    AND alloc.trans_no_from = vw.trans_no 
                JOIN ".TB_PREF."subcontractor_invoices si 
                    ON alloc.trans_type_to = ".db_escape(ST_SUBCONTRACTOR_INVOICE)." 
                    AND alloc.trans_no_to = si.order_no 
            WHERE vw.tran_date >= ".db_escape($month_begin_date)." 
                AND vw.tran_date <= ".db_escape($month_end_date)."
                AND si.approval_status = ".db_escape(AS_APPROVED)." 
                AND si.project_id = ".db_escape($project_id);
    
    
    $result = db_query($sql);
    return db_result_value($result, "payments");
}


function get_project_subcontractor_credit_note_returns_summary($project_id, $month_begin_date, $month_end_date)
{
    $sql = "SELECT SUM(alloc.amt) credit   
    
            FROM ".TB_PREF."subcontractor_credit_notes subc
                JOIN ".TB_PREF."supp_allocations alloc   
                    ON alloc.trans_type_from = ".db_escape(ST_SUBCONTRACTOR_CREDIT_NOTE)." 
                    AND alloc.trans_no_from = subc.order_no 
            WHERE subc.ord_date >= ".db_escape($month_begin_date)." 
                AND subc.ord_date <= ".db_escape($month_end_date)."
                AND subc.approval_status = ".db_escape(AS_APPROVED)."
                AND subc.project_id = ".db_escape($project_id);

    $result = db_query($sql, "get_project_cf_subcontractor_credit_note_receipts");
    return db_result_value($result, "credit");
}

function get_project_utility_bill_payments_summary($project_id, $month_begin_date, $month_end_date)
{    
    $sql = "SELECT SUM(amt) payments   
            FROM ".TB_PREF."supp_allocations alloc 
                JOIN ".TB_PREF."vw_all_supp_cash_outs vw 
                    ON alloc.trans_type_from = vw.trans_type 
                    AND alloc.trans_no_from = vw.trans_no 
                JOIN ".TB_PREF."utility_bills ub  
                    ON alloc.trans_type_to = ".db_escape(ST_UTILITY_BILLS)." 
                    AND alloc.trans_no_to = ub.id  
            WHERE vw.tran_date >= ".db_escape($month_begin_date)." 
                AND vw.tran_date <= ".db_escape($month_end_date)."
                AND ub.approval_status = ".db_escape(AS_APPROVED)." 
                AND ub.project_id = ".db_escape($project_id);

    $result = db_query($sql);
    return db_result_value($result, "payments");
    
}

function get_project_utility_bill_credit_note_returns_summary($project_id, $month_begin_date, $month_end_date)
{
    $sql = "SELECT SUM(alloc.amt) credit       
            FROM ".TB_PREF."utility_bill_credit_notes utibc 
                JOIN ".TB_PREF."supp_allocations alloc 
                    ON alloc.trans_type_from = ".db_escape(ST_UTILITY_BILL_CREDIT_NOTE)." 
                    AND alloc.trans_no_from = utibc.id 
            WHERE utibc.date >= ".db_escape($month_begin_date)." 
                AND utibc.date <= ".db_escape($month_end_date)."
                AND utibc.approval_status = ".db_escape(AS_APPROVED)." 
                AND utibc.project_id = ".db_escape($project_id);
        
    $result = db_query($sql);
    return db_result_value($result, "credit");
    
}


function get_project_transport_payments_summary($project_id, $month_begin_date, $month_end_date)
{
    $month_end_date .= " 23:59:59";
    $vrc_total_project_payment = 0;
    
    if($project_id == 18){
        $ss=23;
    }
    $sql = "SELECT sti.id, sti.total, sti.taxes, alloc.amt  allocation  
            FROM ".TB_PREF."supp_allocations alloc 
                JOIN ".TB_PREF."vw_all_supp_cash_outs vw 
                    ON alloc.trans_type_from = vw.trans_type 
                    AND alloc.trans_no_from = vw.trans_no 
                JOIN ".TB_PREF."supplier_transport_invoices sti   
                    ON alloc.trans_type_to = ".db_escape(ST_SUPPLIER_TRANSPORT_INVOICES)." 
                    AND alloc.trans_no_to = sti.id  
            WHERE vw.tran_date >= ".db_escape($month_begin_date)." 
                AND vw.tran_date <= ".db_escape($month_end_date);

    $result = db_query($sql);
    while($sti = db_fetch($result)){
        
        $vrc_total_project_payment += get_project_transport_invoice_vrc_payments_summary($sti, $project_id);
    }
    
    return $vrc_total_project_payment;
}


function get_project_transport_invoice_vrc_payments_summary($sti, $project_id)
{
    $sql = "SELECT SUM(IF(vrc.supplier_charge > 0, vrc.supplier_charge, vrc.cost)) vrc_project_charge 
            FROM ".TB_PREF."vehicle_running_chart vrc 
            WHERE vrc.project_id = ".db_escape($project_id)." 
                AND vrc.inv_no = ".db_escape($sti['id']);
        
        $result = db_query($sql);
        $vrc_project_charge = db_result_value($result, "vrc_project_charge");
        $vrc_project_payment = $vrc_project_charge * $sti['allocation'] / ($sti['total'] + $sti['taxes']);
        
        return $vrc_project_payment;
}



function get_project_direct_purchase_payments_summary($project_id, $month_begin_date, $month_end_date)
{
    
//    $result = db_query($sql);
//    return db_result_value($result, "cost");
    
    $month_end_date .= " 23:59:59";
    $invoice_total_non_tax_payment = 0;
    
    $sql = "SELECT st.trans_no, st.ov_amount, st.ov_gst, alloc.amt  allocation  
            FROM ".TB_PREF."supp_allocations alloc 
                JOIN ".TB_PREF."vw_all_supp_cash_outs vw 
                    ON alloc.trans_type_from = vw.trans_type 
                    AND alloc.trans_no_from = vw.trans_no 
                JOIN ".TB_PREF."supp_trans st   
                    ON alloc.trans_type_to = st.type  
                    AND alloc.trans_no_to = st.trans_no   
            WHERE st.type = ".db_escape(ST_SUPPINVOICE)." 
                AND vw.tran_date >= ".db_escape($month_begin_date)." 
                AND vw.tran_date <= ".db_escape($month_end_date);

    $result = db_query($sql);
    while($si = db_fetch($result)){
        
        $invoice_total_non_tax_payment += get_project_direct_purchase_grn_payments_summary($si, $project_id);
    }
    
    return $invoice_total_non_tax_payment;
    
    
}

function get_project_direct_purchase_grn_payments_summary($si, $project_id)
{
    $sql = "SELECT SUM(sii.quantity * sii.unit_price) charge      
    
            FROM ".TB_PREF."supp_invoice_items sii   
                JOIN ".TB_PREF."purch_order_details pod    
                    ON sii.po_detail_item_id = pod.po_detail_item    
                JOIN ".TB_PREF."purch_orders poh     
                    ON poh.order_no = pod.order_no   
            WHERE sii.supp_trans_type = ".db_escape(ST_SUPPINVOICE)." 
                AND sii.supp_trans_no = ".db_escape($si['trans_no'])." 
                AND poh.project_id = ".db_escape($project_id);                 
        
        $result = db_query($sql);
        $sii_charge = db_result_value($result, "charge");
        $allocation_factor  = $si['allocation'] / ($si['ov_gst'] + $si['ov_amount']);
        
        $invoice_non_tax_payment = $sii_charge * $allocation_factor;
        
        return $invoice_non_tax_payment;
}


function get_project_direct_purchase_credit_note_returns_summary($project_id, $month_begin_date, $month_end_date)
{
    $month_end_date .= " 23:59:59";
    $credit_note_total_non_tax_payment = 0;
    
    $sql = "SELECT st.trans_no, st.ov_amount, st.ov_gst, alloc.amt  allocation  
            FROM ".TB_PREF."supp_allocations alloc  
                JOIN ".TB_PREF."supp_trans st   
                    ON alloc.trans_type_from = st.type  
                    AND alloc.trans_no_from = st.trans_no   
            WHERE st.type = ".db_escape(ST_SUPPCREDIT)." 
                AND st.tran_date >= ".db_escape($month_begin_date)." 
                AND st.tran_date <= ".db_escape($month_end_date);

    $result = db_query($sql);
    while($si = db_fetch($result)){
        
        $credit_note_total_non_tax_payment += get_project_cf_direct_purchase_grn_returns($si, $project_id);
    }
    
    return $credit_note_total_non_tax_payment;
    
}


function get_project_cf_direct_purchase_grn_returns($si, $project_id)
{
    $sql = "SELECT SUM(sii.quantity * sii.unit_price) charge      
    
            FROM ".TB_PREF."supp_invoice_items sii   
                JOIN ".TB_PREF."purch_order_details pod    
                    ON sii.po_detail_item_id = pod.po_detail_item    
                JOIN ".TB_PREF."purch_orders poh     
                    ON poh.order_no = pod.order_no   
            WHERE sii.supp_trans_type = ".db_escape(ST_SUPPCREDIT)." 
                AND sii.supp_trans_no = ".db_escape($si['trans_no'])." 
                AND poh.project_id = ".db_escape($project_id);                 
        
        $result = db_query($sql);
        $sii_charge = db_result_value($result, "charge");
        $allocation_factor  = $si['allocation'] / ($si['ov_gst'] + $si['ov_amount']);
        
        $credit_note_non_tax_return = $sii_charge * $allocation_factor;
        
        return $credit_note_non_tax_return;
}

function get_site_material_return_summary($project_id, $from_date, $to_date){    
    $sql = "SELECT SUM(total) amount 
            FROM ".TB_PREF."site_material_returns smr  
            WHERE smr.from_project_id = ".db_escape($project_id)." 
                AND smr.tran_date >= ".db_escape($from_date)." 
                AND smr.tran_date <= ".db_escape($to_date)." 
                AND smr.status = ".db_escape(AS_PROCESSED);
            
    $result = db_query($sql, "could not get_site_material_return_total");
    return db_result_value($result, "amount"); 
}

function get_purchase_requisition_issues_summary($project_id, $from_date, $to_date){    
    $sql = "SELECT SUM(total) amount 
            FROM ".TB_PREF."purchase_requisition_issues   
            WHERE project_id = ".db_escape($project_id)." 
                AND created_on >= ".db_escape($from_date)." 
                AND created_on <= ".db_escape($to_date);
            
    $result = db_query($sql, "could not get_purchase_requisition_issues_total");
    return db_result_value($result, "amount"); 
}

function get_purchase_requisition_returns_summary($project_id, $from_date, $to_date){    
    $sql = "SELECT SUM(total) amount 
            FROM ".TB_PREF."purchase_requisition_returns    
            WHERE project_id = ".db_escape($project_id)." 
                AND prr_date >= ".db_escape($from_date)." 
                AND prr_date <= ".db_escape($to_date);
            
    $result = db_query($sql, "could not get_purchase_requisition_returns_summary");
    return db_result_value($result, "amount"); 
}


function get_sales_despatches_summary($project_id, $month_begin_date, $month_end_date)
{

    $sql = "SELECT (despd.tran_qty * despd.conversion_factor * despd.standard_cost) desp_total  
            FROM ".TB_PREF."debtor_trans_details despd 
                JOIN ".TB_PREF."debtor_trans desp 
                    ON despd.debtor_trans_no = desp.trans_no 
                        AND despd.debtor_trans_type = desp.type 
                JOIN ".TB_PREF."debtor_trans inv 
                    ON desp.order_ = inv.trans_no 
                        AND inv.type = ".db_escape(ST_SALESINVOICE)." 
                JOIN ".TB_PREF."sales_orders so 
                    ON inv.order_ = so.order_no 
                        AND so.trans_type = ".db_escape(ST_SALESORDER)."  
                JOIN ".TB_PREF."projects_master pm 
                    ON so.project_id = pm.project_id 
            WHERE desp.type = ".db_escape(ST_CUSTDELIVERY)." 
                AND desp.tran_date >= ".db_escape($month_begin_date)." 
                AND desp.tran_date <= ".db_escape($month_end_date)." 
                AND pm.project_id = ".db_escape($project_id);
    
    $result = db_query($sql,"get_sales_despatches_summary");
    return db_result_value($result, "desp_total"); 

}


function get_sales_despatch_returns_summary($project_id, $month_begin_date, $month_end_date)
{

    $sql = "SELECT (cnd.tran_qty * cnd.conversion_factor * cnd.standard_cost) cn_total 
            FROM ".TB_PREF."debtor_trans_details cnd 
                JOIN ".TB_PREF."debtor_trans cn 
                    ON cnd.debtor_trans_no = cn.trans_no 
                        AND cnd.debtor_trans_type = cn.type 
                JOIN ".TB_PREF."debtor_trans desp 
                    ON cn.order_ = desp.trans_no 
                        AND desp.type = ".db_escape(ST_CUSTDELIVERY)."    
                JOIN ".TB_PREF."debtor_trans inv 
                    ON desp.order_ = inv.trans_no 
                        AND inv.type = ".db_escape(ST_SALESINVOICE)."  
                JOIN ".TB_PREF."sales_orders so 
                    ON inv.order_ = so.order_no 
                        AND so.trans_type = ".db_escape(ST_SALESORDER)."    
                JOIN ".TB_PREF."projects_master pm 
                    ON so.project_id = pm.project_id 
            WHERE cn.type = ".db_escape(ST_CUSTCREDIT)."    
                AND cn.tran_date >= ".db_escape($month_begin_date)." 
                AND cn.tran_date <= ".db_escape($month_end_date)." 
                AND pm.project_id = ".db_escape($project_id);
                    
    $result = db_query($sql,"get_sales_despatch_returns_summary");
    return db_result_value($result, "cn_total"); 

}

function get_project_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_subcontractors");
    
}



function get_iou_bank_payments_summary($project_id, $from_date, $to_date, $tran_type)
{
    $sql= "SELECT SUM(bt.amount) payment_total  
           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);
    
        
    $result=db_query($sql,"get_iou_bank_payments_summary");	
    
    return db_result_value($result, "payment_total");
}



function get_project_petty_cash_expense_summary($project_id, $from_date, $to_date, $approval_status_array) {    
    $sql = "SELECT SUM(amount) total_amount
            FROM ".TB_PREF."i_owe_you_batches 
            WHERE project_id = ".db_escape($project_id)." 
                AND tran_date >= ".db_escape($from_date)." 
                AND tran_date <= ".db_escape($to_date)." 
                AND approval_status IN (". implode(',', $approval_status_array).")";
    
    $result = db_query($sql, "get_project_petty_cash_expense_summary");
    
        $row = db_fetch($result);
            if (is_array($row)) {
                return $row['total_amount'];
            } else {
                return 0;
            }

}