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

function add_or_update_projects_expenses_schedule_detail($project_id, $month_id, $value, $comment, $pxs_id){
    if($pxs_id > 0){
        return update_projects_expenses_schedule_detail($project_id, $month_id, $value, $comment, $pxs_id);
    }else{
        return add_projects_expenses_schedule_detail($project_id, $month_id, $value, $comment);
    }
}

function add_projects_expenses_schedule_detail($project_id, $month_id, $value, $comment){
    $id_user = $_SESSION['wa_current_user']->user;
    $datetime = date('Y-m-d H:i:s');
                
    $sql = "INSERT INTO ".TB_PREF."projects_expenses_schedule (
                    project_id, month_id, 
                    value, comment,
                    created_by,created_on) 
            VALUES (".db_escape($project_id).", ".db_escape($month_id).", "
                    .db_escape($value).",".db_escape($comment).","
                    .db_escape($id_user).", ".db_escape($datetime).")";

    db_query($sql, "add_projects_expenses_schedule_detail");
    if(!db_num_affected_rows() > 0){
        cancel_transaction();
        return array("success" => false, "Could not add new project expenses schedule line and month id $month_id.");
    }else{
        return array("success" => true, "message" => "");
    }
}

function update_projects_expenses_schedule_detail($project_id, $month_id, $value, $comment, $pxs_id){
    $id_user = $_SESSION['wa_current_user']->user;
    $datetime = date('Y-m-d H:i:s');
    
    $sql = "UPDATE ".TB_PREF."projects_expenses_schedule 
            SET project_id = ".db_escape($project_id).",       
                month_id = ".db_escape($month_id).", 
                value = ".db_escape($value).",       
                comment = ".db_escape($comment).",       
                updated_by = ".db_escape($id_user).",
                updated_on = ".db_escape($datetime)."
            WHERE id = ".db_escape($pxs_id);

    db_query($sql, "update_projects_expenses_schedule_detail");	
    if(!db_num_affected_rows() > 0){
        cancel_transaction();
        return array("success" => false, "Could not update project expenses schedule line id #$pxs_id and month id $month_id.");
    }else{
        return array("success" => true, "message" => "");
    }
}

function delete_project_expenses_schedule($project_id, $in_string){
    $count = get_to_be_deleted_project_expenses_schedule_records_count($project_id, $in_string);
    if(!($count > 0)){
        return array("success" => true, "message" => "");
    }
    $sql = "DELETE FROM ".TB_PREF."projects_expenses_schedule 
            WHERE project_id = ".db_escape($project_id)." 
                AND month_id NOT IN ($in_string)";
    
    db_query($sql, "could not delete_project_expenses_schedule");	
    
    if(!(db_num_affected_rows() > 0)){
        cancel_transaction();
        return array("success" => false, "message" => "Could not delete project expenses schedule records.");
    }else{
        return array("success" => true, "message" => "");
    }
}

function get_project_expenses_schedule_month_range($project_id, $from_month_id, $to_month_id){
    $sql = "SELECT m.id month_id,
                   m.description month_name,
                   px.id pxs_id, 
                   px.value,
                   px.comment 
            FROM ".TB_PREF."months m  
                LEFT JOIN ".TB_PREF."projects_expenses_schedule px 
                    ON m.id = px.month_id 
                        AND px.project_id = ".db_escape($project_id)." 
            WHERE m.id >= ".db_escape($from_month_id)." 
                AND m.id <= ".db_escape($to_month_id)." 
            ORDER BY m.display_order";
    return db_query($sql, "get_project_expenses_schedule");
}

function get_project_expenses_schedule_min_and_max_maonths($project_id){
    $sql = "SELECT MIN(month_id) min_month_id, MAX(month_id) max_month_id 
            FROM ".TB_PREF."projects_expenses_schedule 
            WHERE project_id = ".db_escape($project_id);
    $result = db_query($sql, "could not get_project_expenses_schedules");
    return db_fetch($result);
}

function get_to_be_deleted_project_expenses_schedule_records_count($project_id, $in_string){
    $sql = "SELECT COUNT(id) count  
            FROM ".TB_PREF."projects_expenses_schedule 
            WHERE project_id = ".db_escape($project_id)." 
                AND month_id NOT IN ($in_string)";
    $result = db_query($sql, "could not get_project_expenses_schedules");
    $count = db_fetch($result);
    return $count['count'];
}


function get_project_expenses_schedule($project_id){
    $sql = "SELECT m.id month_id,
                   m.description month_name,
                   px.id pxs_id, 
                   px.value,
                   px.comment  
            FROM ".TB_PREF."months m  
                LEFT JOIN ".TB_PREF."projects_expenses_schedule px 
                    ON m.id = px.month_id                        
            WHERE  px.project_id = ".db_escape($project_id)." 
            ORDER BY m.display_order";
    return db_query($sql, "could not get_project_expenses_schedules");
}

function get_project_expenses_schedule_month_value($project_id, $month_id){
    
    $sql = "SELECT px.value 
            FROM ".TB_PREF."projects_expenses_schedule px  
            WHERE  px.project_id = ".db_escape($project_id)." 
                    AND px.month_id = ".db_escape($month_id);
    $result = db_query($sql, "get_project_expenses_schedule_month_value");
    
    return db_result_value($result, "value");
}

function get_project_expenses_schedule_summary($project_id, $from_month_display_order, $to_month_display_order){
    
    $sql = "SELECT SUM(px.value) total_expenses 
            FROM ".TB_PREF."months m  
                JOIN ".TB_PREF."projects_expenses_schedule px 
                    ON m.id = px.month_id                        
            WHERE  px.project_id = ".db_escape($project_id)." 
                AND m.display_order >= ".db_escape($from_month_display_order)." 
                AND m.display_order <= ".db_escape($to_month_display_order)."                     
            ORDER BY m.display_order";
    
    $result = db_query($sql, "get_project_expenses_schedule_summary");
    
    return db_result_value($result, "total_expenses");
}

?>