<?php

function cs_get_projects_without_expenses_schedule(){
    $sql = "SELECT *  
            FROM ".TB_PREF."projects_master 
            WHERE project_id NOT IN (SELECT DISTINCT project_id FROM ".TB_PREF."projects_expenses_schedule) 
            LIMIT 10";
    $sql = "SELECT *  
            FROM ".TB_PREF."projects_master 
            WHERE project_id = 107 
            LIMIT 10";
    
    return db_query($sql, "Could not cs_get_projects_without_expenses_schedule");
}


function cs_delete_project_expenses_schedule($project_id){

    $sql = "DELETE FROM ".TB_PREF."projects_expenses_schedule 
            WHERE project_id = ".db_escape($project_id);
    
    db_query($sql, "delete_project_expenses_schedule");	

}

function cs_generate_project_expenses_schedule(){
    $message = "==================Script Sarted==========================";
    write_custom_script_log("generate_project_expenses_schedule", $message);
    
    $projects_result = cs_get_projects_without_expenses_schedule();
    $recs = db_num_rows($projects_result);
    write_custom_script_log("generate_project_expenses_schedule", $recs." Records selected");
    
    while($project = db_fetch($projects_result)){
        cs_delete_project_expenses_schedule($project['project_id']);
        
        $project_name = $project['project_id']." - ".$project['description'];
        
        $project_duration = date_diff2(sql2date($project['end_date']), sql2date($project['start_date']), 'd');
        $new_project_months = $project_duration * 1.5 / 30;
        $new_project_months = ceil($new_project_months);
        
        $project_cost = ($project['approved_cost_budgets_total'] + $project['approved_overhead_budgets_total']);   
        if($project_cost == 0){
                continue;
        }
        
        $month_project_cost = $project_cost / $new_project_months;
        $i = $schedulled_cost = 0;
        $temp_date = $project['start_date'];
        $month = get_month_by_date_within($temp_date);
        
        while ($project_cost - $schedulled_cost > 0) {
            if(isset($month['id']) && $month['id'] > 0){
                $exp_result = get_project_expenses_schedule_month_range($project['project_id'], $month['id'], $month['id']);
                if(db_num_rows($exp_result) > 0){
                    $pxs_id = db_result_value($exp_result, "value");
                }
                else{
                    $pxs_id = 0;
                }

                $comment = "System generated";
                add_or_update_projects_expenses_schedule_detail($project['project_id'], $month['id'], $month_project_cost, $comment, $pxs_id);
                
                $month = get_next_month_by_display_order($month['display_order']);
            }
            else{
                $temp_date = add_days(sql2date($temp_date), 30);
                $month = get_month_by_date_within(date2sql($temp_date));
            }
            $schedulled_cost += $month_project_cost;
            
            $i++;
            if($i > 100){
                break;
            }
            
        }
        
        if($i > 0){
            $message = "Expenses Schedule (".$i." records) created for project ".$project_name;
        }else{
            $message = "Could not create Expenses Schedule for project ".$project_name;
        }
        write_custom_script_log("generate_project_expenses_schedule", $message);
    }
    
    $message = "------------------Script Completed----------------------------";
    write_custom_script_log("generate_project_expenses_schedule", $message);
}

?>