<?php

function dashboard_get_cf_sql_for_project_invoice_receipts_details($type, $project_id, $from_date, $to_date)
{
    $sql = "SELECT pay.*,
                a.amt alllocated_amount,
                pi.order_no invoice_no 
            FROM ".TB_PREF."project_invoices pi   
                JOIN ".TB_PREF."cust_allocations a 
                    ON a.trans_type_to = ".db_escape($type)." 
                    AND a.trans_no_to = pi.order_no 
                JOIN ".TB_PREF."debtor_trans pay  
                    ON pay.type = a.trans_type_from  
                    AND pay.trans_no = a.trans_no_from 
            WHERE pi.project_id =".db_escape($project_id)
                ." AND pay.tran_date >=".db_escape($from_date)
                ." AND pay.tran_date <=".db_escape($to_date)." 
            ORDER BY pay.tran_date ";
    
    return $sql;
    
}

function dashboard_get_cf_project_invoice_receipts_summary($type, $project_id, $from_date, $to_date)
{
    $sql_pay_allocs = dashboard_get_cf_sql_for_project_invoice_receipts_details($type, $project_id, $from_date, $to_date);
    
    $sql = "SELECT SUM(pay.alllocated_amount) total_cost 
            FROM (".$sql_pay_allocs.") pay ";
    
    $result = db_query($sql,"dashboard_get_cf_project_invoice_receipts_summary");
    
    $row = db_fetch($result);
    if(isset($row['total_cost']) && $row['total_cost'] != 0){
        return $row['total_cost'];
    }
    else{
        return 0;
    }
    
}

function dashboard_get_cf_sql_for_project_credit_note_payments_summary($type, $project_id, $from_date, $to_date)
{
    $sql = "SELECT pay.*,
                a.amt alllocated_amount,
                pcn.order_no  
            FROM ".TB_PREF."project_credit_notes pcn   
                JOIN ".TB_PREF."cust_allocations a 
                    ON a.trans_type_from = ".db_escape($type)." 
                    AND a.trans_no_from = pcn.order_no 
                JOIN ".TB_PREF."debtor_trans pay  
                    ON pay.type = a.trans_type_to  
                    AND pay.trans_no = a.trans_no_to 
            WHERE pcn.project_id =".db_escape($project_id)
                ." AND pay.tran_date >=".db_escape($from_date)
                ." AND pay.tran_date <=".db_escape($to_date);
    
    return $sql;
}
    
function dashboard_get_cf_project_credit_note_payments_summary($type, $project_id, $from_date, $to_date)
{
    
    $sql = dashboard_get_cf_sql_for_project_credit_note_payments_summary($type, $project_id, $from_date, $to_date);
    
    $sql = "SELECT SUM(pay.alllocated_amount) total_cost 
            FROM (".$sql.") pay ";
    
    
    $result = db_query($sql,"dashboard_get_cf_project_credit_note_payments_summary");
    
    $row = db_fetch($result);
    if(isset($row['total_cost']) && $row['total_cost'] != 0){
        return $row['total_cost'];
    }
    else{
        return 0;
    }
    
}

function dashboard_get_cf_sql_for_subcontractor_invoice_payments_details($type, $project_id, $from_date, $to_date)
{
    $sql = "SELECT pay.*,
                a.amt alllocated_amount,
                subi.order_no   
            FROM ".TB_PREF."subcontractor_invoices subi  
                JOIN ".TB_PREF."supp_allocations a 
                    ON a.trans_type_to = ".db_escape($type)." 
                    AND a.trans_no_to = subi.order_no 
                JOIN ".TB_PREF."supp_trans pay  
                    ON pay.type = a.trans_type_from  
                    AND pay.trans_no = a.trans_no_from 
            WHERE subi.project_id =".db_escape($project_id)
                ." AND pay.tran_date >=".db_escape($from_date)
                ." AND pay.tran_date <=".db_escape($to_date);
    
    return $sql;
}

function dashboard_get_cf_subcontractor_invoice_payments_summary($type, $project_id, $from_date, $to_date){
    
    $sql = dashboard_get_cf_sql_for_subcontractor_invoice_payments_details($type, $project_id, $from_date, $to_date);
    
    $sql = "SELECT SUM(pay.alllocated_amount) total_cost 
            FROM (".$sql.") pay ";
    
    $result = db_query($sql,"dashboard_get_cf_subcontractor_invoice_payments_summary");
    
    $row = db_fetch($result);
    if(isset($row['total_cost']) && $row['total_cost'] != 0){
        return $row['total_cost'];
    }
    else{
        return 0;
    }
    
}


function dashboard_get_cf_sql_for_subcontractor_credit_note_receipts_details($type, $project_id, $from_date, $to_date)
{
    $sql = "SELECT pay.*,
                a.amt alllocated_amount,
                scn.order_no invoice_no 
            FROM ".TB_PREF."subcontractor_credit_notes scn   
                JOIN ".TB_PREF."supp_allocations a 
                    ON a.trans_type_from = ".db_escape($type)." 
                    AND a.trans_no_from = scn.order_no 
                JOIN ".TB_PREF."supp_trans pay  
                    ON pay.type = a.trans_type_to  
                    AND pay.trans_no = a.trans_no_to 
            WHERE scn.project_id =".db_escape($project_id)
                ." AND pay.tran_date >=".db_escape($from_date)
                ." AND pay.tran_date <=".db_escape($to_date);
    
    return $sql;
    
    
}

function dashboard_get_cf_subcontractor_credit_note_receipts_summary($type, $project_id, $from_date, $to_date)
{
    
    $sql = dashboard_get_cf_sql_for_subcontractor_credit_note_receipts_details($type, $project_id, $from_date, $to_date);
    
    $sql = "SELECT SUM(pay.alllocated_amount) total_cost 
            FROM (".$sql.") pay ";
    
    $result = db_query($sql,"dashboard_get_cf_subcontractor_credit_note_receipts_summary");
    
    $row = db_fetch($result);
    if(isset($row['total_cost']) && $row['total_cost'] != 0){
        return $row['total_cost'];
    }
    else{
        return 0;
    }
    
}


function dashboard_get_cf_sql_for_utility_bills_payments_details($type, $project_id, $from_date, $to_date)
{
    
    $sql = "SELECT pay.*,
                a.amt alllocated_amount,
                ub.id ub_no 
            FROM ".TB_PREF."utility_bills ub   
                JOIN ".TB_PREF."supp_allocations a 
                    ON a.trans_type_to = ".db_escape($type)." 
                    AND a.trans_no_to = ub.id 
                JOIN ".TB_PREF."supp_trans pay  
                    ON pay.type = a.trans_type_from  
                    AND pay.trans_no = a.trans_no_from 
            WHERE ub.project_id =".db_escape($project_id)
                ." AND pay.tran_date >=".db_escape($from_date)
                ." AND pay.tran_date <=".db_escape($to_date);
    
    return $sql;
    
}
function dashboard_get_cf_utility_bills_payments_summary($type, $project_id, $from_date, $to_date)
{
    $sql = dashboard_get_cf_sql_for_utility_bills_payments_details($type, $project_id, $from_date, $to_date);
    
    $sql = "SELECT SUM(pay.alllocated_amount) total_cost 
            FROM (".$sql.") pay ";
    
    
    $result = db_query($sql,"dashboard_get_cf_utility_bills_payments_summary");
    
    $row = db_fetch($result);
    if(isset($row['total_cost']) && $row['total_cost'] != 0){
        return $row['total_cost'];
    }
    else{
        return 0;
    }
    
}

function dashboard_get_cf_sql_for_utility_bill_credit_note_receipts_details($type, $project_id, $from_date, $to_date)
{
    
    $sql = "SELECT pay.*,
                a.amt alllocated_amount,
                ubcn.id ub_cn_no 
            FROM ".TB_PREF."utility_bill_credit_notes ubcn   
                JOIN ".TB_PREF."supp_allocations a 
                    ON a.trans_type_from = ".db_escape($type)." 
                    AND a.trans_no_from = ubcn.id 
                JOIN ".TB_PREF."supp_trans pay  
                    ON pay.type = a.trans_type_to  
                    AND pay.trans_no = a.trans_no_to 
            WHERE ubcn.project_id =".db_escape($project_id)
                ." AND pay.tran_date >=".db_escape($from_date)
                ." AND pay.tran_date <=".db_escape($to_date);
    
    return $sql;
    
}
function dashboard_get_cf_utility_bill_credit_note_receipts_summary($type, $project_id, $from_date, $to_date)
{
    

    $sql = dashboard_get_cf_sql_for_utility_bill_credit_note_receipts_details($type, $project_id, $from_date, $to_date);
    
    $sql = "SELECT SUM(pay.alllocated_amount) total_cost 
            FROM (".$sql.") pay ";
    
    $result = db_query($sql,"dashboard_get_cf_utility_bill_credit_note_receipts_summary");
    
    $row = db_fetch($result);
    if(isset($row['total_cost']) && $row['total_cost'] != 0){
        return $row['total_cost'];
    }
    else{
        return 0;
    }
    
}