<?php

function cs_get_projects_without_billing_schedule(){
    $sql = "SELECT *  
            FROM ".TB_PREF."projects_master 
            WHERE project_id NOT IN (SELECT DISTINCT project_id FROM ".TB_PREF."projects_billing_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_billing_schedule");
}

function cs_delete_project_billing_schedule($project_id)
{
	$sql="DELETE FROM ".TB_PREF."projects_billing_schedule WHERE project_id= ".db_escape($project_id);
	db_query($sql,"cs_delete_project_billing_schedule");			
}


function cs_generate_project_billing_schedule(){
    $message = "==================Script Sarted==========================";
    write_custom_script_log("generate_project_billing_schedule", $message);
    
    $projects_result = cs_get_projects_without_billing_schedule();
    $recs = db_num_rows($projects_result);
    write_custom_script_log("generate_project_billing_schedule", $recs." Records selected");
    
    while($project = db_fetch($projects_result)){
//        get_project_record($project['project_id']);
        cs_delete_project_billing_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 / 2;
        $new_project_months = ceil($new_project_months);
        
        $project_value = ($project['project_value']);   
        if($project_value == 0){
                continue;
        }
        
        $month_billing_value = $project_value / $new_project_months;
        $i = $schedulled_billing_value = 0;
        $temp_date = $project['start_date'];
        $month = get_month_by_date_within($temp_date);
        
        while ($project_value - $schedulled_billing_value > 0) {
            if(isset($month['id']) && $month['id'] > 0){
                $billing_type = get_listvalue_by_code('PROJECT_BILLING_TYPES', 'PROJECT_WORK');
                $comment = "System generated";
                $percentage = $month_billing_value / $project_value * 100;
                add_project_billing_schedule_detail($project['project_id'], $billing_type['id'], sql2date($month['month_end_date']), $month_billing_value, 
                                                    $percentage, $comment, 0, 0);
                
                $month = get_next_month_by_display_order($month['display_order'] + 1);
            }
            else{
                $temp_date = add_days(sql2date($temp_date), 60);
                $month = get_month_by_date_within(date2sql($temp_date));
            }
            $schedulled_billing_value += $month_billing_value;
            
            $i++;
            if($i > 100){
                break;
            }
            
        }
        
        if($i > 0){
            $message = "Billing Schedule (".$i." records) created for project ".$project_name;
        }else{
            $message = "Could not create billing Schedule for project ".$project_name;
        }
        write_custom_script_log("generate_project_billing_schedule", $message);
    }
    
    $message = "------------------Script Completed----------------------------";
    write_custom_script_log("generate_project_billing_schedule", $message);
}

?>