<?php


function project_cf_table_month_range_project_wise_display($department_id, $from_month_display_order, $to_month_display_order)
{
//    display_heading2("Project Cashflow");
    display_title("Project Wise Cashflow Summary");
    
    
    start_table(TABLESTYLE, "width=95%");
    $th = array("Project", "Openning Balance", "Collections",
                "Subcontractor Payments", "IOU/Petty Cash Payments and Advances", "Utility Payments", 
                        "Transport Payments", "Direct Purchase Payments", "Material Issue Costs", 
                "Total Payments", "H/O Overhead", "Closing Balance");
    $th = array("Project", 
                nl2br("Openning ".PHP_EOL." Balance"),
                nl2br("Project ".PHP_EOL." Invoice ".PHP_EOL." Collections"), 
                nl2br("Sales ".PHP_EOL." Invoice ".PHP_EOL." Collections"), 
                nl2br("Month ".PHP_EOL." Collections"), 
                nl2br("Subcontractor ".PHP_EOL." Invoice ".PHP_EOL." Payments"), 
                nl2br("IOU and ".PHP_EOL." Petty Cash ".PHP_EOL." Payments"), 
                nl2br("Utility Bill ".PHP_EOL." Payments"),  
                nl2br("Transport ".PHP_EOL." Payments"), 
                nl2br("Direct ".PHP_EOL." Material ".PHP_EOL." Purchase ".PHP_EOL." Payments"), 
                nl2br("Stock ".PHP_EOL." Material".PHP_EOL." Issues"), 
                nl2br("Sales ".PHP_EOL." Material ".PHP_EOL." Purchase ".PHP_EOL." Payments"), 
                nl2br("Month ".PHP_EOL." Payments"), 
                nl2br("Overhead"), 
                nl2br("Closing ".PHP_EOL." Balance"));

    table_header($th);
    
    
    $k = $dep_openning_balance = 
            $dep_project_invoice_receipts_and_credit_note_payments = 
            $dep_direct_sales_invoice_receipts_and_credit_note_payments = 
            $dep_project_collections =
            $dep_sub_invoice_payments_credit_notes = 
            $dep_iou_petty_cash_payments_advances = 
            $dep_utility_bill_payemnts_and_returns = 
            $dep_transport = 
            $dep_direct_purchase_payments_and_returns = 
            $dep_material_issues_and_returns = 
            $dep_sales_material_purchase_payments_and_credit_note_returns =
            $dep_project_payments = 
            $dep_overhead = 
            $dep_profit_lost = 
            $dep_closing_balance = 0;
    
    $result = get_department_projects($department_id);
    while($project = db_fetch($result)){
        $project_id = $project['project_id'];
        
        alt_table_row_color($k);

        label_cell($project['description']);
        
        $openning_balance = get_project_cf_openning_balance($project_id, $from_month_display_order, $to_month_display_order);   
        amount_cell($openning_balance);
        $dep_openning_balance += $openning_balance;
        
        $project_invoice_receipts = get_project_cf_project_invoice_receipts($project_id, $from_month_display_order, $to_month_display_order); 
        $project_credit_note_payments = get_project_cf_project_credit_note_payments($project_id, $from_month_display_order, $to_month_display_order); 
        $project_invoice_receipts_and_credit_note_payments = $project_invoice_receipts + $project_credit_note_payments;
        amount_cell($project_invoice_receipts_and_credit_note_payments);
        $dep_project_invoice_receipts_and_credit_note_payments += $project_invoice_receipts_and_credit_note_payments;
        
        $direct_sales_invoice_receipts = get_project_cf_direct_sales_invoice_receipts($project_id, $from_month_display_order, $to_month_display_order); 
        $direct_sales_credit_note_payments = get_project_cf_direct_sales_credit_note_payments($project_id, $from_month_display_order, $to_month_display_order); 
        $direct_sales_invoice_receipts_and_credit_note_payments = $direct_sales_invoice_receipts + $direct_sales_credit_note_payments;
        amount_cell($direct_sales_invoice_receipts_and_credit_note_payments);
        $dep_direct_sales_invoice_receipts_and_credit_note_payments += $direct_sales_invoice_receipts_and_credit_note_payments;
        
        $project_collections = $project_invoice_receipts_and_credit_note_payments + $direct_sales_invoice_receipts_and_credit_note_payments;
        amount_cell($project_collections);
        $dep_project_collections += $project_collections;
        
        $sub_invoice_payments = get_project_cf_subcontractor_invoice_payments($project_id, $from_month_display_order, $to_month_display_order);
        $sub_credit_note_receipts = get_project_cf_subcontractor_credit_note_receipts($project_id, $from_month_display_order, $to_month_display_order);
        $sub_invoice_payments_credit_notes = $sub_invoice_payments + $sub_credit_note_receipts;
        amount_cell($sub_invoice_payments_credit_notes);
        $dep_sub_invoice_payments_credit_notes += $sub_invoice_payments_credit_notes;
        
        $iou_petty_cash_payments = get_project_cf_iou_payments($project_id, $from_month_display_order, $to_month_display_order);
        $iou_advance_payments = get_project_cf_iou_advance_payments($project_id, $from_month_display_order, $to_month_display_order);
        $iou_petty_cash_payments_advance = $iou_petty_cash_payments + $iou_advance_payments;
        amount_cell($iou_petty_cash_payments_advance);        
        $dep_iou_petty_cash_payments_advances += $iou_petty_cash_payments_advance;
        
        $utility_bill_payments = get_project_cf_utility_bill_payments($project_id, $from_month_display_order, $to_month_display_order);
        $utility_bill_returns = get_project_cf_utility_bill_returns($project_id, $from_month_display_order, $to_month_display_order);
        $utility_bill_payemnts_and_returns = $utility_bill_payments + $utility_bill_returns;
        amount_cell($utility_bill_payemnts_and_returns);
        $dep_utility_bill_payemnts_and_returns += $utility_bill_payemnts_and_returns;
        
        $transport = get_project_cf_transport_payments($project_id, $from_month_display_order, $to_month_display_order);
        amount_cell($transport);
        $dep_transport += $transport;
        
        $direct_purchase_payments = get_project_cf_direct_purchase_payments($project_id, $from_month_display_order, $to_month_display_order);
        $direct_purchase_returns = get_project_cf_direct_purchase_credit_notes($project_id, $from_month_display_order, $to_month_display_order);
        $direct_purchase_payments_and_returns = $direct_purchase_payments + $direct_purchase_returns;
        amount_cell($direct_purchase_payments_and_returns);
        $dep_direct_purchase_payments_and_returns += $direct_purchase_payments_and_returns;
        
        $material_issue = get_project_cf_material_issues($project_id, $from_month_display_order, $to_month_display_order);
        $material_returns = get_project_cf_material_returns($project_id, $from_month_display_order, $to_month_display_order);
        $material_issues_and_returns = $material_issue - $material_returns;
        amount_cell($material_issues_and_returns);
        $dep_material_issues_and_returns += $material_issues_and_returns;
        
        $sales_material_purchase_payments = get_project_cf_sales_material_purchase_payments($project_id, $from_month_display_order, $to_month_display_order);
        $sales_material_purchase_credit_note_returns = get_project_cf_sales_material_purchase_credit_note_returns($project_id, $from_month_display_order, $to_month_display_order);
        $sales_material_purchase_payments_and_credit_note_returns = $sales_material_purchase_payments + $sales_material_purchase_credit_note_returns;
        amount_cell($sales_material_purchase_payments_and_credit_note_returns);
        $dep_sales_material_purchase_payments_and_credit_note_returns += $sales_material_purchase_payments_and_credit_note_returns;
        
        $project_payments = $sub_invoice_payments_credit_notes + 
                            $iou_petty_cash_payments_advance + 
                            $utility_bill_payemnts_and_returns + 
                            $transport + 
                            $direct_purchase_payments_and_returns + 
                            $material_issues_and_returns;
        
        amount_cell($project_payments);
//        label_cell('$project_payments');
        $dep_project_payments += $project_payments;

        $overhead = get_project_cf_overhead($project_id, $from_month_display_order, $to_month_display_order);        
        amount_cell($overhead);
        $dep_overhead += $overhead;
        
        $closing_balance = $openning_balance + $project_collections - ($project_payments + $overhead);
        if($closing_balance >= 0){
            amount_cell($closing_balance);
        }
        else{
            label_cell('<font color="red">'.text_bold("(".price_format(-$closing_balance).")").'</font>', "align=right");
        }
        $dep_closing_balance += $closing_balance;

        end_row();
    }
    start_row();
        label_cell(text_bold("Totals"), "align=right");
        amount_cell($dep_openning_balance, true);
        amount_cell($dep_project_invoice_receipts_and_credit_note_payments, true);
        amount_cell($dep_direct_sales_invoice_receipts_and_credit_note_payments, true);
        amount_cell($dep_project_collections, true);
        amount_cell($dep_sub_invoice_payments_credit_notes, true);
        amount_cell($dep_iou_petty_cash_payments_advances, true);
        amount_cell($dep_utility_bill_payemnts_and_returns, true);
        amount_cell($dep_transport, true);
        amount_cell($dep_direct_purchase_payments_and_returns, true);
        amount_cell($dep_material_issues_and_returns, true);
        amount_cell($dep_sales_material_purchase_payments_and_credit_note_returns, true);
        amount_cell($dep_project_payments, true);
        amount_cell($dep_overhead, true);
        
        if($dep_closing_balance >= 0){
            amount_cell($dep_closing_balance);
        }
        else{
            label_cell('<font color="red">'.text_bold("(".price_format(-$dep_closing_balance).")").'</font>', "align=right");
        }

        end_row();
    end_table();
}


//SELECT type trans_type, trans_no,  tran_date, (ov_amount-ov_discount) amount, ov_gst taxes FROM 0_supp_trans WHERE type = 22 OR type = 21 OR type = 2
//UNION 
//SELECT 175 trans_type, order_no trans_no, ord_date tran_date, total amount, taxes taxes FROM 0_subcontractor_credit_notes WHERE approval_status = 30 
//UNION 
//SELECT 178 trans_type, id trans_no, date tran_date, total amount, taxes taxes FROM 0_utility_bill_credit_notes WHERE approval_status = 30 


//------------------------------------------------------------------------------------------------------------------

function get_project_cf_project_invoice_receipts($project_id, $from_month_display_order, $to_month_display_order)
{
    $receipts_types_in = SCVT_PROJECT_INVOICE_RECEIPTS;
    
    return get_summary_projects_month_range_project_value($from_month_display_order, $to_month_display_order, $project_id, $receipts_types_in);
}


function get_project_cf_project_credit_note_payments($project_id, $from_month_display_order, $to_month_display_order)
{
    $receipts_types_in = SCVT_PROJECT_CREDIT_NOTE_PAYMENTS;
    
    return get_summary_projects_month_range_project_value($from_month_display_order, $to_month_display_order, $project_id, $receipts_types_in);
}


function get_project_cf_direct_sales_invoice_receipts($project_id, $from_month_display_order, $to_month_display_order)
{    
    $value_types_in = SCVT_SALES_INVOICE_RECEIPTS;
    $row = get_summary_sales_month_range_project_values($project_id, $value_types_in, $from_month_display_order, $to_month_display_order);
    $total = $row['order_total'] - $row['discounts_total'] + $row['taxes_total'];
    return $total;
}

function get_project_cf_direct_sales_credit_note_payments($project_id, $from_month_display_order, $to_month_display_order)
{
    
    $value_types_in = SCVT_SALES_CREDIT_NOTE_PAYMENTS;
    $row = get_summary_sales_month_range_project_values($project_id, $value_types_in, $from_month_display_order, $to_month_display_order);
    $total = ($row['order_total'] - $row['discounts_total'] + $row['taxes_total']);    
    return $total;
    
}
function get_project_cf_subcontractor_invoice_payments($project_id, $from_month_display_order, $to_month_display_order)
{
    $receipts_types_in = SCVT_SUBCONTRACTOR_INVOICE_PAYMENTS;
    
    return get_summary_projects_month_range_project_value($from_month_display_order, $to_month_display_order, $project_id, $receipts_types_in);
}

function get_project_cf_subcontractor_credit_note_receipts($project_id, $from_month_display_order, $to_month_display_order)
{
    $receipts_types_in = SCVT_SUBCONTRACTOR_CREDIT_NOTE_RETURNS;
    
    return get_summary_projects_month_range_project_value($from_month_display_order, $to_month_display_order, $project_id, $receipts_types_in);
}


function get_project_cf_iou_payments($project_id, $from_month_display_order, $to_month_display_order)
{
    $receipts_types_in = SCVT_I_OWE_YOU_PAYMENTS;
    
    return get_summary_projects_month_range_project_value($from_month_display_order, $to_month_display_order, $project_id, $receipts_types_in);
}


function get_project_cf_iou_advance_payments($project_id, $from_month_display_order, $to_month_display_order)
{
    $receipts_types_in = SCVT_I_OWE_YOU_ADVANCES;
    
    return get_summary_projects_month_range_project_value($from_month_display_order, $to_month_display_order, $project_id, $receipts_types_in);
}


function get_project_cf_utility_bill_payments($project_id, $from_month_display_order, $to_month_display_order)
{
    $receipts_types_in = SCVT_UTILITY_BILL_PAYMENTS;
    
    return get_summary_projects_month_range_project_value($from_month_display_order, $to_month_display_order, $project_id, $receipts_types_in);
    
}

function get_project_cf_utility_bill_returns($project_id, $from_month_display_order, $to_month_display_order)
{
    $receipts_types_in = SCVT_UTILITY_BILL_CREDIT_NOTE_RETURNS;
    
    return get_summary_projects_month_range_project_value($from_month_display_order, $to_month_display_order, $project_id, $receipts_types_in);
    
}

function get_project_cf_transport_payments($project_id, $from_month_display_order, $to_month_display_order)
{
    $receipts_types_in = SCVT_TRANSPORT_PAYMENTS;
    
    return get_summary_projects_month_range_project_value($from_month_display_order, $to_month_display_order, $project_id, $receipts_types_in);
    
}


function get_project_cf_material_issues($project_id, $from_month_display_order, $to_month_display_order)
{
    $receipts_types_in = SCVT_MATERIAL_ISSUES;
    
    return get_summary_projects_month_range_project_value($from_month_display_order, $to_month_display_order, $project_id, $receipts_types_in);
    
}

function get_project_cf_material_returns($project_id, $from_month_display_order, $to_month_display_order)
{
    $receipts_types_in = SCVT_MATERIAL_RETURNS;
    
    return get_summary_projects_month_range_project_value($from_month_display_order, $to_month_display_order, $project_id, $receipts_types_in);
    
}


function get_project_cf_direct_purchase_payments($project_id, $from_month_display_order, $to_month_display_order)
{
    $receipts_types_in = SCVT_SUPPLIER_INVOICE_PAYMENTS;
    
    return get_summary_projects_month_range_project_value($from_month_display_order, $to_month_display_order, $project_id, $receipts_types_in);
    
}


function get_project_cf_direct_purchase_credit_notes($project_id, $from_month_display_order, $to_month_display_order)
{
    $receipts_types_in = SCVT_SUPPLIER_CREDIT_NOTE_RETURNS;
    
    return get_summary_projects_month_range_project_value($from_month_display_order, $to_month_display_order, $project_id, $receipts_types_in);
    
}

function get_project_cf_sales_material_purchase_payments($project_id, $from_month_display_order, $to_month_display_order)
{
    $value_types_in = SCVT_SUPPLIER_INVOICE_PAYMENTS;
    $project_types_in = PROJ_TYPE_SALES_COST;
    return get_summary_project_types_month_range_project_value($from_month_display_order, $to_month_display_order, $project_id, $value_types_in, $project_types_in);
}

function get_project_cf_sales_material_purchase_credit_note_returns($project_id, $from_month_display_order, $to_month_display_order)
{
    $value_types_in = SCVT_SUPPLIER_CREDIT_NOTE_RETURNS;
    $project_types_in = PROJ_TYPE_SALES_COST;
    return get_summary_project_types_month_range_project_value($from_month_display_order, $to_month_display_order, $project_id, $value_types_in, $project_types_in);
}


function get_project_cf_overhead($project_id, $from_month_display_order, $to_month_display_order)
{
//        $project_id = $project['project_id'];
    
        $sub_invoices = get_project_pl_subcontractor_invoices($project_id, $from_month_display_order, $to_month_display_order); 
        $sub_credit_notes = get_project_pl_subcontractor_credit_notes($project_id, $from_month_display_order, $to_month_display_order); 
        $sub_invoices_credit_notes = $sub_invoices + $sub_credit_notes;
        
        $iou_petty_cash_expenses = get_project_pl_iou_petty_cash_expenses($project_id, $from_month_display_order, $to_month_display_order); 
        
        $utility_bill = get_project_pl_utility_bills($project_id, $from_month_display_order, $to_month_display_order); 
        $utility_credit = get_project_pl_utility_bill_credits($project_id, $from_month_display_order, $to_month_display_order); 
        $utility_bills_and_credits = $utility_bill - $utility_credit;
        
        $transport = get_project_pl_transport($project_id, $from_month_display_order, $to_month_display_order); 
        
        $direct_purchases = get_project_pl_direct_material_purchases($project_id, $from_month_display_order, $to_month_display_order); 
        $direct_purchase_returns = get_project_pl_direct_material_purchase_credit_notes($project_id, $from_month_display_order, $to_month_display_order); 
        $direct_purchases_and_returns = $direct_purchases - $direct_purchase_returns;
        
        $material_issue = get_project_pl_material_issues($project_id, $from_month_display_order, $to_month_display_order); 
        $material_returns = get_project_pl_material_returns($project_id, $from_month_display_order, $to_month_display_order); 
        $material_issues_and_returns = $material_issue - $material_returns;
        
        $direct_sales_despatches = get_project_pl_direct_sales_despatches($project_id, $from_month_display_order, $to_month_display_order); 
        $direct_sales_despatch_returns = get_project_pl_direct_sales_despatch_returns($project_id, $from_month_display_order, $to_month_display_order); 
        $direct_sales_despatches_and_returns = $direct_sales_despatches - $direct_sales_despatch_returns;
        
        $tool_usage_costs = $dep_tool_usage_costs = 0;
        
        $project_expenses = $sub_invoices_credit_notes 
                            + $iou_petty_cash_expenses 
                            + $utility_bills_and_credits 
                            + $transport 
                            + $direct_purchases_and_returns 
                            + $material_issues_and_returns 
                            + $direct_sales_despatches_and_returns 
                            + $tool_usage_costs;


        $project = get_project_record($project_id);
        if($project['approved_cost_budgets_total']>0){
            $oh_factor = $project['approved_overhead_budgets_total'] / $project['approved_cost_budgets_total'];
        }
        else{
            $oh_factor = 0;
        }
        $overhead = $project_expenses * $oh_factor;
        
        return $overhead;
    
}




function get_project_cf_openning_balance($project_id, $to_month_display_order)
{
        $from_month_display_order = 0;

        $before_month = get_before_month_by_display_order($to_month_display_order);
        $before_month_display_order = $before_month['display_order'];
        
//        $project_id = $project['project_id'];
    
        $project_invoice_receipts = get_project_cf_project_invoice_receipts($project_id, 0, $before_month_display_order); 
        $project_credit_note_payments = get_project_cf_project_credit_note_payments($project_id, 0, $before_month_display_order); 
        $project_invoice_receipts_and_credit_note_payments = $project_invoice_receipts + $project_credit_note_payments;
        
        $direct_sales_invoice_receipts = get_project_cf_direct_sales_invoice_receipts($project_id, 0, $before_month_display_order); 
        $direct_sales_credit_note_payments = get_project_cf_direct_sales_credit_note_payments($project_id, 0, $before_month_display_order); 
        $direct_sales_invoice_receipts_and_credit_note_payments = $direct_sales_invoice_receipts + $direct_sales_credit_note_payments;
        
        $collections = $project_invoice_receipts_and_credit_note_payments + $direct_sales_invoice_receipts_and_credit_note_payments;

        
        //Payments
        $sub_invoice_payments = get_project_cf_subcontractor_invoice_payments($project_id, 0, $before_month_display_order); 
        $sub_credit_note_receipts = get_project_cf_subcontractor_credit_note_receipts($project_id, 0, $before_month_display_order); 
        $sub_invoice_payments_credit_notes = $sub_invoice_payments + $sub_credit_note_receipts;
        
        $iou_petty_cash_payments = get_project_cf_iou_payments($project_id, 0, $before_month_display_order); 
        $iou_advance_payments = get_project_cf_iou_advance_payments($project_id, 0, $before_month_display_order); 
        $iou_petty_cash_payments_advance = $iou_petty_cash_payments + $iou_advance_payments;
        
        $utility_bill_payments = get_project_cf_utility_bill_payments($project_id, 0, $before_month_display_order); 
        $utility_bill_returns = get_project_cf_utility_bill_returns($project_id, 0, $before_month_display_order); 
        $utility_bill_payemnts_and_returns = $utility_bill_payments + $utility_bill_returns;
        
        $transport = get_project_cf_transport_payments($project_id, 0, $before_month_display_order); 
        
        $direct_purchase_payments = get_project_cf_direct_purchase_payments($project_id, 0, $before_month_display_order); 
        $direct_purchase_returns = get_project_cf_direct_purchase_credit_notes($project_id, 0, $before_month_display_order); 
        $direct_purchase_payments_and_returns = $direct_purchase_payments + $direct_purchase_returns;
        
        $material_issue = get_project_cf_material_issues($project_id, 0, $before_month_display_order); 
        $material_returns = get_project_cf_material_returns($project_id, 0, $before_month_display_order); 
        $material_issues_and_returns = $material_issue - $material_returns;
        
        $sales_material_purchase_payments = get_project_cf_sales_material_purchase_payments($project_id, 0, $before_month_display_order); 
        $sales_material_purchase_credit_note_returns = get_project_cf_sales_material_purchase_credit_note_returns($project_id, 0, $before_month_display_order); 
        $sales_material_purchase_payments_and_credit_note_returns = $sales_material_purchase_payments + $sales_material_purchase_credit_note_returns;
        
        $payments = $sub_invoice_payments_credit_notes 
                                        + $iou_petty_cash_payments_advance 
                                        + $utility_bill_payemnts_and_returns 
                                        + $transport 
                                        + $direct_purchase_payments_and_returns 
                                        + $material_issues_and_returns 
                                        + $sales_material_purchase_payments_and_credit_note_returns;
        

        $overhead = get_project_cf_overhead($project_id, $from_month_display_order, $before_month_display_order);        
        
        $opening_balance = $collections - ($payments + $overhead);
        return $opening_balance;
        
        
}
?>