<?php

function department_cash_flow_statement_project_wise_display($department_id, $from_month_id, $to_month_id)
{
    display_heading2("Project Cash Flow Statement");
    start_table(TABLESTYLE, "width=85%");
    $th = array("Project", "Openning Balance", "Cash In (Collections)", "Cash Out (Payments)", "H/O Overhead", "Closing Balance");

    table_header($th);
    $projects = get_department_projects($department_id);
    $month_b = get_month($from_month_id);
    $month_e = get_month($to_month_id);
    
    while($project = db_fetch($projects)){
        alt_table_row_color($k);

        label_cell($project['description']);
        
        $openning_balance = get_department_cash_flow_statement_openning_balance($department_id, $project['project_id'], $month_b['month_begin_date'], $month_e['month_end_date']); 
        amount_cell($openning_balance);
        
        $cash_in = get_department_cash_flow_statement_cash_in_collections($department_id, $project['project_id'], $month_b['month_begin_date'], $month_e['month_end_date']); 
        amount_cell($cash_in);
        
        $cash_out = get_department_cash_flow_statement_cash_out_payments($department_id, $project['project_id'], $month_b['month_begin_date'], $month_e['month_end_date']); 
        amount_cell($cash_out);
        
        $overhead = get_department_cash_flow_statement_overhead($department_id, $project['project_id'], $month_b['month_begin_date'], $month_e['month_end_date']); 
        amount_cell($overhead);
        
        $closing_balance = ($openning_balance + $cash_in) - ($cash_out - $overhead);
        amount_cell($closing_balance);
        
        end_row();
    }
    end_table();
}


function get_department_cash_flow_statement_openning_balance($department_id, $project_id, $month_begin_date, $month_end_date)
{
    return 1000;
}

function get_department_cash_flow_statement_cash_in_collections($department_id, $project_id, $month_begin_date, $month_end_date)
{
    $sql = "SELECT SUM(supp.alloc) total,
                    pm.department_id 
            FROM ".TB_PREF."debtor_trans dt 
                    JOIN ".TB_PREF."supp_trans supp  
                        ON supp.trans_no = dt.trans_no 
                        AND dt.type = ".db_escape(ST_CUSTPAYMENT)." 
                    JOIN ".TB_PREF."projects_master pm 
                        ON dt.project_id = pm.project_id 
            WHERE pm.department_id = ".db_escape($department_id)." 
                AND dt.tran_date >= ".db_escape($month_begin_date)." 
                AND dt.tran_date <= ".db_escape($month_end_date)."
                AND dt.project_id = ".db_escape($project_id)."
            GROUP BY pm.department_id ";
    
    $result = db_query($sql);
    return db_result_value($result, "total");
}

function get_department_cash_flow_statement_cash_out_payments($department_id, $project_id, $month_begin_date, $month_end_date)
{
    $sql = "SELECT SUM(supp.alloc) total,
                    pm.department_id 
            FROM ".TB_PREF."debtor_trans dt 
                    JOIN ".TB_PREF."supp_trans supp  
                        ON supp.trans_no = dt.trans_no 
                        AND dt.type = ".db_escape(ST_SUPPAYMENT)." 
                    JOIN ".TB_PREF."projects_master pm 
                        ON dt.project_id = pm.project_id 
            WHERE pm.department_id = ".db_escape($department_id)." 
                AND dt.tran_date >= ".db_escape($month_begin_date)." 
                AND dt.tran_date <= ".db_escape($month_end_date)."
                AND dt.project_id = ".db_escape($project_id)."
            GROUP BY pm.department_id ";
    
    $result = db_query($sql);
    return db_result_value($result, "total");
}

function get_department_cash_flow_statement_overhead($department_id, $project_id, $month_begin_date, $month_end_date)
{
    return 1000;
}

?>