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


function write_monthly_project_costs_header($mpc_no, $reference, 
                                        $project_id, $month_id, $date, 
                                        $base_total_type, $distribution_type, 
                                        $header_comment, $scheduled_total, $row_state)                          
{
    global $Refs;
    
    if($mpc_no > 0){        
        update_monthly_project_costs_header($mpc_no, $reference, $month_id, $date, 
                                        $base_total_type, $distribution_type, 
                                        $header_comment, $scheduled_total, $row_state);
    }else{
        
        $mpc_no = add_monthly_project_costs_header($reference, $project_id, $month_id, $date, 
                                                $base_total_type, $distribution_type, 
                                                $header_comment, $scheduled_total);
        
        $Refs->save(ST_MONTHLY_PROJECT_COSTS, $mpc_no, $reference);
    }

    if(!($mpc_no > 0)){
        return;
    }    
    
    return $mpc_no;
}


function write_monthly_project_costs_detail($mpc_no, $mpc_line_id,
                                        $description, $unit_id, $quantity,
                                        $rate, $task_boq_group_id,
                                        $task_id, $mpc_type_id, $debit_account, $credit_account, $comment)
{            
    if($mpc_line_id > 0){         
        $n = update_monthly_project_costs_detail($mpc_no, $mpc_line_id,
                                        $description, $unit_id, $quantity,
                                        $rate, $task_boq_group_id,
                                        $task_id, $mpc_type_id, $debit_account, $credit_account, $comment);
        if($n != 1){
           return array("success" => false, "message" => "Another user has updated monthly project cost detail $mpc_line_id.");  
        }
    }
    else{
        add_monthly_project_costs_detail($mpc_no, $description,
                                        $unit_id, $quantity,
                                        $rate, $task_boq_group_id,
                                        $task_id, $mpc_type_id, $debit_account, $credit_account, $comment);
    }
    
    update_monthly_project_costs_header_total($mpc_no);
    
    return array("success" => true, "message" =>"Miscellaneous project cost $mpc_no has been updated.");
    
}


function update_monthly_project_costs_header($mpc_no, $reference, $month_id, $date, 
                                        $base_total_type, $distribution_type, 
                                        $header_comment, $scheduled_total, $row_state)
{
    $updated_by = $_SESSION['wa_current_user']->user;
    $updated_on = date('Y-m-d H:i:s');
    
    $sql = "UPDATE ".TB_PREF."monthly_project_costs
            SET 
                reference=".db_escape($reference).",
                month_id=".db_escape($month_id).",
                date=".db_escape($date).", 
                base_total_type=".db_escape($base_total_type).",
                distribution_type=".db_escape($distribution_type).", 
                comment=".db_escape($header_comment).", 
                scheduled_total=".db_escape($scheduled_total).", 
                updated_by=".db_escape($updated_by).",
                updated_on=".db_escape($updated_on).",
                row_state=". db_new_row_state($row_state)."        
                
            WHERE id=".db_escape($mpc_no)." 
                AND row_state = ".db_escape($row_state);
    
    db_query($sql);
    return db_num_affected_rows();  
    
}


function add_monthly_project_costs_header($reference, $project_id, $month_id, $date, 
                                                $base_total_type, $distribution_type, 
                                                $header_comment, $scheduled_total)
{
    $created_by = $_SESSION['wa_current_user']->user;
    $created_on = date('Y-m-d H:i:s');
    
    $sql = "INSERT INTO ".TB_PREF."monthly_project_costs( 
                    reference, project_id, month_id,
                    date, base_total_type, 
                    distribution_type, comment,     
                    scheduled_total,
                    created_by, created_on )         
                VALUES (
                    ".db_escape($reference).", ".db_escape($project_id).", ".db_escape($month_id).", 
                    ".db_escape($date).", ".db_escape($base_total_type).",
                    ".db_escape($distribution_type).", ".db_escape($header_comment).",
                    ".db_escape($scheduled_total).", 
                    ".db_escape($created_by).", ".db_escape($created_on).")";
     
    db_query($sql);
    return db_insert_id();        
}


function add_monthly_project_costs_detail($mpc_no, $project_id,
                                        $value, $task_id, $comment)
{
    $created_by = $_SESSION['wa_current_user']->user;
    $created_on = date('Y-m-d H:i:s');
    
    $sql = "INSERT INTO ".TB_PREF."monthly_project_costs_details(
                        mpc_id, project_id, 
                        value, task_id, comment,
                        created_by, created_on)                
            VALUES (".db_escape($mpc_no).", ".db_escape($project_id).","
                        .db_escape($value).", ".db_escape($task_id).", ".db_escape($comment).", "                   
                        .db_escape($created_by).", ".db_escape($created_on).")";
             
    db_query($sql);
       
}

function update_monthly_project_costs_department($id, $base_total_type, $distribution_type, 
                                        $calculated_value, $applied_value, $total)
{
    $updated_by = $_SESSION['wa_current_user']->user;
    $updated_on = date('Y-m-d H:i:s');
    
    $sql = "UPDATE ".TB_PREF."monthly_project_costs_departments 
                SET                    
                    base_total_type=".db_escape($base_total_type).",                  
                    distribution_type=".db_escape($distribution_type).",
                    calculated_value=".db_escape($calculated_value).",
                    applied_value=".db_escape($applied_value).",       
                    total=".db_escape($total).",
                    updated_by=".db_escape($updated_by).",
                    updated_on=".db_escape($updated_on)."                
                WHERE id=".db_escape($id);
     
    db_query($sql); 
    
}


function add_monthly_project_costs_department($mpc_no, $department_id, $base_total_type, $distribution_type, 
                                        $calculated_value, $applied_value, $mpcd_total)
{
    $created_by = $_SESSION['wa_current_user']->user;
    $created_on = date('Y-m-d H:i:s');
    
    $sql = "INSERT INTO ".TB_PREF."monthly_project_costs_departments(
                        mpc_id, department_id, 
                        base_total_type, distribution_type, 
                        calculated_value, applied_value, total,
                        created_by, created_on)                
            VALUES (".db_escape($mpc_no).", ".db_escape($department_id).","
                        .db_escape($base_total_type).", ".db_escape($distribution_type).", " 
                        .db_escape($calculated_value).", ".db_escape($applied_value).", ".db_escape($mpcd_total).", "                   
                        .db_escape($created_by).", ".db_escape($created_on).")";
             
    db_query($sql);
}

function get_monthly_project_costs_header($mpc_no)
{
    $sql = "SELECT mpc.*,
                    pm.description project_description                                 
        
            FROM ".TB_PREF."monthly_project_costs mpc
                JOIN ".TB_PREF."projects_master pm ON
                    mpc.project_id = pm.project_id       
                            
            WHERE mpc.id=".db_escape($mpc_no);
    
    $result = db_query($sql);
    return db_fetch($result);   
    
}

function get_monthly_project_costs_header_record($mpc_no)
{
    $sql = "SELECT mpc.*,
                    pm.description project_description                                 
        
            FROM ".TB_PREF."monthly_project_costs mpc
                JOIN ".TB_PREF."projects_master pm ON
                    mpc.project_id = pm.project_id       
                            
            WHERE mpc.id=".db_escape($mpc_no);
    
    $result = db_query($sql);
    return db_fetch($result);   
    
}


function get_monthly_project_costs_departments($order_no)
{
    $sql = "SELECT dep.id lv_department_id,
                    dep.value_name department_name,
                    mdep.* 
                    
                         
            FROM ".TB_PREF."list_values dep   
                LEFT JOIN ".TB_PREF."monthly_project_costs_departments mdep ON
                    mdep.department_id = dep.id  
                    AND mdep.mpc_id=".db_escape($order_no)." 
            WHERE dep.list_code = 'DEPARTMENTS' 
                    
            ORDER BY dep.value_name";
    
    return db_query($sql, "get_monthly_project_costs_departments.");
    
}


function get_monthly_project_costs_department($mpc_no, $department_id)
{
    $sql = "SELECT *                          
            FROM ".TB_PREF."monthly_project_costs_departments 
            WHERE mpc_id=".db_escape($mpc_no)." 
                AND department_id=".db_escape($department_id);
    
    $result = db_query($sql, "get_monthly_project_costs_department.");
    return db_fetch($result);
}

function delete_monthly_project_costs_detail($mpc_no, $project_id)
{  
    $sql = "DELETE FROM ".TB_PREF."monthly_project_costs_details 
            WHERE mpc_id=".db_escape($mpc_no)." 
                AND project_id=".db_escape($project_id);
    
    db_query($sql);
    
}

function delete_monthly_project_costs_department($mpc_no, $department_id)
{
    $sql = "DELETE FROM ".TB_PREF."monthly_project_costs_departments 
            WHERE mpc_id=".db_escape($mpc_no)." 
                AND department_id=".db_escape($department_id);
    
    db_query($sql, "delete_monthly_project_costs_department.");

    return db_num_affected_rows();
}

function get_monthly_project_costs_line_count($mpc_no)
{
    $sql = "SELECT COUNT(id) line_count 
            FROM ".TB_PREF."monthly_project_costs_details 
            WHERE mpc_id = ".db_escape($mpc_no);
    
    $result = db_query($sql);
    $count =  db_fetch($result);   
    if(isset($count['line_count']) && $count['line_count'] > 0){
        return $count['line_count'];
    }
    
    return 0;
}


function update_monthly_project_costs_header_total($mpc_no, $total, $old_row_state)
{

    $sql = "UPDATE ".TB_PREF."monthly_project_costs
            SET total=".db_escape($total).",
                row_state=". db_new_row_state($old_row_state)."
            WHERE id=".db_escape($mpc_no)." 
                AND row_state = ".db_escape($old_row_state);
    
    db_query($sql); 
    
    return db_num_affected_rows();
    
}


function get_monthly_project_costs_details_total($mpc_no, $department_id)
{
    $sql = "SELECT SUM(value) total                          
            FROM ".TB_PREF."monthly_project_costs_details mpcd 
                JOIN ".TB_PREF."projects_master pm 
                    ON pm.project_id = mpcd.project_id                  
            WHERE mpcd.mpc_id=".db_escape($mpc_no)." 
                AND pm.department_id=".db_escape($department_id);
    
    $result = db_query($sql, "get_monthly_project_costs_details_total");
    
    return db_result_value($result, 'total');
    
}

function get_monthly_project_costs_departments_total($mpc_no)
{
    $sql = "SELECT SUM(total) total                          
            FROM ".TB_PREF."monthly_project_costs_departments             
            WHERE mpc_id=".db_escape($mpc_no);
    
    $result = db_query($sql, "get_monthly_project_costs_departments_total");
    
    return db_result_value($result, 'total');
    
}

function get_monthly_project_costs_all_departments_details_total($mpc_no)
{
    $sql = "SELECT SUM(value) total                          
            FROM ".TB_PREF."monthly_project_costs_details              
            WHERE mpc_id=".db_escape($mpc_no);
    
    $result = db_query($sql, "get_monthly_project_costs_details_total");
    
    return db_result_value($result, 'total');
    
}

function get_monthly_project_cost_detail($mpc_no, $project_id)
{
    $sql = "SELECT pm.description project_description,
		mpcd.*   
            FROM ".TB_PREF."projects_master pm 
                INNER JOIN ".TB_PREF."project_tasks pt ON 
                    pt.project_id = pm.project_id 
                INNER JOIN ".TB_PREF."monthly_project_costs_details mpcd ON 
                    mpcd.task_id = pt.task_id 
                    
            WHERE mpcd.mpc_id = ".db_escape($mpc_no)." 
                AND pm.project_id = ".db_escape($project_id);
    
    $result = db_query($sql, "get_monthly_project_cost_detail");   
    return db_fetch($result);
}


function get_monthly_project_costs_all_departments_details($mpc_no)
{
    $sql = "SELECT mpcd.*   
            FROM ".TB_PREF."monthly_project_costs_details mpcd 
            WHERE mpcd.mpc_id = ".db_escape($mpc_no);
    
    return db_query($sql, "get_monthly_project_costs_all_departments_details");   

}
?>