<?php


function project_pl_table_month_range_department_wise_display($from_month_id, $to_month_id)
{
    display_heading2("Project PL Table - Department Wise");
    start_table(TABLESTYLE, "width=85%");
    $th = array("Department", "IPC + Last IPA Value", "Subpayments", "Petty Cash Payments", "Utility Payments", 
                "Transport", "Tool Purchase", "Material Issue", "Project Expences", "H/O Overhead", "Profit/loss");

    table_header($th);
    $departments = get_departments();
    $month_b = get_month($from_month_id);
    $month_e = get_month($to_month_id);
    
    while($department = db_fetch($departments)){
        alt_table_row_color($k);

        label_cell($department['value_name']);
        
        $ipc_plus_last_ipa_value = get_project_pl_ipc_plus_last_ipa_value($department['id'], $month_b['month_begin_date'], $month_e['month_end_date']); 
        amount_cell($ipc_plus_last_ipa_value);
        
        $subpayments_invoice = get_project_pl_subpayments_invoices($department['id'], $month_b['month_begin_date'], $month_e['month_end_date']);
        $subpayments_credit = get_project_pl_subpayments_credits($department['id'], $month_b['month_begin_date'], $month_e['month_end_date']);
        $subpayments = $subpayments_invoice - $subpayments_credit;
        amount_cell($subpayments);

        label_cell("Petty Cash Payments");
        
        $utility_bill = get_project_pl_utility_bills($department['id'], $month_b['month_begin_date'], $month_e['month_end_date']);
        $utility_credit = get_project_pl_utility_bill_credits($department['id'], $month_b['month_begin_date'], $month_e['month_end_date']);
        $utility_payments = $utility_bill - $utility_credit;
        amount_cell($utility_payments);
        
        $transport = get_project_pl_transport($department['id'], $month_b['month_begin_date'], $month_e['month_end_date']);
        amount_cell($transport);
        
        label_cell("Tool Purchase");
        
        $material_issue = get_project_pl_material_issue($department['id'], $month_b['month_begin_date'], $month_e['month_end_date']);
        amount_cell($material_issue);
        
        $project_expences = $subpayments + $utility_payments + $transport + $material_issue;
        amount_cell($project_expences);
        
        $overhead = get_project_pl_overhead($department['id'], $month_b['month_begin_date'], $month_e['month_end_date']);
        amount_cell($overhead);
        
        $profit_lost = $ipc_plus_last_ipa_value - ($project_expences + $overhead);
        amount_cell($profit_lost);

        end_row();
    }
    end_table();
}


function get_project_pl_ipc_plus_last_ipa_value($department_id, $month_begin_date, $month_end_date)
{
    $sql = "SELECT SUM(pi.total) total,
                    pm.department_id 
            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)." 
                    JOIN ".TB_PREF."projects_master pm 
                        ON pi.project_id = pm.project_id 
            WHERE pm.department_id = ".db_escape($department_id)." 
                AND 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)." 
            GROUP BY pm.department_id ";
    
    $result = db_query($sql);
    return db_result_value($result, "total");
}

function get_project_pl_subpayments_invoices($department_id, $month_begin_date, $month_end_date)
{
    $sql = "SELECT SUM(supp.alloc) invoice  
    
            FROM ".TB_PREF."subcontractor_invoices subi 
                JOIN ".TB_PREF."supp_trans supp   
                    ON subi.order_no = supp.trans_no   
                    AND supp.type = ".db_escape(ST_SUBCONTRACTOR_INVOICE)." 
                JOIN ".TB_PREF."projects_master pm 
                    ON pm.project_id = subi.project_id 
     
            WHERE pm.department_id = ".db_escape($department_id)." 
                AND subi.ord_date >= ".db_escape($month_begin_date)." 
                AND subi.ord_date <= ".db_escape($month_end_date)."
                AND subi.approval_status = ".db_escape(AS_APPROVED)." 
                
            GROUP BY supp.type";
    
    $result = db_query($sql);
    return db_result_value($result, "invoice");
}

function get_project_pl_subpayments_credits($department_id, $month_begin_date, $month_end_date)
{
    $sql = "SELECT SUM(supp.alloc) credit   
    
            FROM ".TB_PREF."subcontractor_credit_notes subc
                JOIN ".TB_PREF."supp_trans supp   
                    ON subc.order_no = supp.trans_no   
                    AND supp.type = ".db_escape(ST_SUBCONTRACTOR_CREDIT_NOTE)." 
                JOIN ".TB_PREF."projects_master pm 
                    ON pm.project_id = subc.project_id 
     
            WHERE pm.department_id = ".db_escape($department_id)." 
                AND 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)." 
                
            GROUP BY supp.type";

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

function get_project_pl_utility_bills($department_id, $month_begin_date, $month_end_date)
{
    $sql = "SELECT SUM(supp.alloc) bill    
    
            FROM ".TB_PREF."utility_bills utib 
                JOIN ".TB_PREF."supp_trans supp   
                    ON utib.id = supp.trans_no   
                    AND supp.type = ".db_escape(ST_UTILITY_BILLS)." 
                JOIN ".TB_PREF."projects_master pm 
                    ON pm.project_id = utib.project_id 
     
            WHERE pm.department_id = ".db_escape($department_id)." 
                AND utib.date >= ".db_escape($month_begin_date)." 
                AND utib.date <= ".db_escape($month_end_date)."
                AND utib.approval_status = ".db_escape(AS_APPROVED)." 
                
            GROUP BY supp.type";

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

function get_project_pl_utility_bill_credits($department_id, $month_begin_date, $month_end_date)
{
    $sql = "SELECT SUM(supp.alloc) credit   
    
            FROM ".TB_PREF."utility_bill_credit_notes utibc 
                JOIN ".TB_PREF."supp_trans supp   
                    ON utibc.id = supp.trans_no   
                    AND supp.type = ".db_escape(ST_UTILITY_BILL_CREDIT_NOTE)." 
                JOIN ".TB_PREF."projects_master pm 
                    ON pm.project_id = utibc.project_id 
     
            WHERE pm.department_id = ".db_escape($department_id)." 
                AND utibc.date >= ".db_escape($month_begin_date)." 
                AND utibc.date <= ".db_escape($month_end_date)."
                AND utibc.approval_status = ".db_escape(AS_APPROVED)." 
                
            GROUP BY supp.type";
    
        
    $result = db_query($sql);
    return db_result_value($result, "credit");
    
}

function get_project_pl_transport($department_id, $month_begin_date, $month_end_date)
{
    $sql = "SELECT SUM(vrc.cost + vrc.supplier_charge) total 
    
            FROM ".TB_PREF."vehicle_running_chart vrc  
                JOIN ".TB_PREF."projects_master pm 
                    ON pm.project_id = vrc.project_id 
     
            WHERE pm.department_id = ".db_escape($department_id)." 
                AND vrc.start_date_time >= ".db_escape($month_begin_date)." 
                AND vrc.start_date_time <= ".db_escape($month_end_date);

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

function get_project_pl_material_issue($department_id, $month_begin_date, $month_end_date)
{
    $sql = "SELECT SUM(imd.qty_issued * imd.unit_cost) total 
    
            FROM ".TB_PREF."issue_materials im   
                JOIN ".TB_PREF."issue_material_details imd  
                    ON im.im_no = imd.im_no  
                JOIN ".TB_PREF."projects_master pm 
                    ON pm.project_id = im.project_id 
     
            WHERE pm.department_id = ".db_escape($department_id)." 
                AND im.im_date >= ".db_escape($month_begin_date)." 
                AND im.im_date <= ".db_escape($month_end_date);
    
    $result = db_query($sql);
    return db_result_value($result, "total");
}

function get_project_pl_overhead($department_id, $month_begin_date, $month_end_date)
{
    $sql = "SELECT SUM(tc.amount) total 
    
            FROM ".TB_PREF."task_costs tc    
                JOIN ".TB_PREF."project_tasks pt   
                    ON tc.task_id = pt.task_id  
                JOIN ".TB_PREF."list_values lv  
                    ON pt.type_id = lv.id 
                    AND lv.value_code = 'OVERHEAD'
                JOIN ".TB_PREF."projects_master pm 
                    ON pm.project_id = pt.project_id 
     
            WHERE pm.department_id = ".db_escape($department_id)." 
                AND tc.added_on >= ".db_escape($month_begin_date)." 
                AND tc.added_on <= ".db_escape($month_end_date);
    
    $result = db_query($sql);
    return db_result_value($result, "total");
}

?>