<?php //
/**********************************************************************
***********************************************************************/


function add_task_group_details($project_id,$code,$description,$type_id, $long_description, $start_date, $end_date, $boq_group_id)
{                
        $sql = "INSERT INTO ".TB_PREF."project_task_groups (
                        code, project_id, 
                        type_id, description, 
                        long_description, 
                        start_date, end_date, 
                        boq_group_id) 
                VALUES (".db_escape($code).", ". db_escape($project_id).", "
                        .db_escape($type_id).", ".db_escape($description).", "
                        .db_escape($long_description).", "
                        .db_escape(date2sql($start_date)).", ".db_escape(date2sql($end_date)).", "
                        .db_escape($boq_group_id).")";

        db_query($sql,"Could not add_task_group_details");	
        
        return db_insert_id();
}



function update_task_group_details($id,$code,$description, $type_id, $long_description, $start_date, $end_date, $boq_group_id)
{

        $sql = "UPDATE ".TB_PREF."project_task_groups 
                SET  
                        code=".db_escape($code).",
                        type_id = ".db_escape($type_id).",    
                        description = ".db_escape($description).",    
                        long_description=".db_escape($long_description).",       
                        start_date=".db_escape(date2sql($start_date)).",
                        end_date=".db_escape(date2sql($end_date)).",
                        boq_group_id=".db_escape($boq_group_id)."
                WHERE id=".db_escape($id);

        db_query($sql,"Task could not be updated");		
}


function add_task($project_id,$task_code,$description,$type_id, $budget, $task_group_id, $detail_type_code, $add_budgets = 0)
{
        $id_user = $_SESSION['wa_current_user']->user;
        $datetime = date('Y-m-d H:i:s');
                
        $sql = "INSERT INTO ".TB_PREF."project_tasks (
                        type_id, task_code, description, ";
                        if($add_budgets){
                            $sql .= " budget, ";
                        }
                    $sql .= " new_budget, project_id, task_group_id,
                        detail_type_code,
                        created_by, created_on ) 
                VALUES (".db_escape($type_id).", ".db_escape($task_code).", ".db_escape($description).", ";
                        if($add_budgets){
                            $sql .=db_escape($budget)." ,";
                        }
                    $sql .= db_escape($budget).", ".db_escape($project_id).",".db_escape($task_group_id).","
                        .db_escape($detail_type_code)." , "
                        .db_escape($id_user).",".db_escape($datetime).")";

        db_query($sql,"Task could not be added");		
}

function update_task_details($task_id,$task_code,$description, $type_id, $budget, $task_group_id, $add_budgets = 1)
{
        $id_user = $_SESSION['wa_current_user']->user;
        $datetime = date('Y-m-d H:i:s');
                
        $sql = "UPDATE ".TB_PREF."project_tasks 
                SET  
                        task_code=".db_escape($task_code).",
                        description = ".db_escape($description).",    
                        type_id = ".db_escape($type_id).",      
                        updated_by=".db_escape($id_user).", ";
                        
                        if($add_budgets){
                            $sql .= " budget=".db_escape($budget).", ";
                        }
                    $sql .= " new_budget=".db_escape($budget).",  
                        task_group_id=".db_escape($task_group_id).",
                        updated_on=".db_escape($datetime)."
                        WHERE task_id=".db_escape($task_id);

        db_query($sql,"Task could not be updated");		
}

function delete_task($task_id)
{
	$sql="DELETE FROM ".TB_PREF."project_tasks WHERE task_id= ".db_escape($task_id);
	db_query($sql,"task could not be deleted");			
}


function get_tasks($project_id)
{
        
        $sql = "SELECT pt.*, lv.value_name task_name 
                FROM ".TB_PREF."project_tasks pt 
                        LEFT JOIN ".TB_PREF."list_values lv ON 
                                pt.type_id = lv.id 
                WHERE pt.project_id=".  db_escape($project_id);
                    
        
        
	return db_query($sql,"tasks could not be retreived");
    
}


function get_task_records($project_id)		
{		
       $sql = "SELECT pt.* 		
            FROM ".TB_PREF."project_tasks pt 		
            WHERE pt.project_id=".  db_escape($project_id);		
            		
        		
       		
	return db_query($sql,"get_task_records");		
}


function get_boq_group_tasks($project_id, $boq_group_id)
{
    if(!($boq_group_id > 0)){
        $boq_group_id = 0;
    }
        
        $sql = "SELECT pt.*, lv.value_name task_name 
                FROM ".TB_PREF."project_tasks pt 
                        LEFT JOIN ".TB_PREF."list_values lv ON 
                                pt.type_id = lv.id 
                WHERE pt.project_id=".  db_escape($project_id)." AND 
                        pt.boq_group_id=".  db_escape($boq_group_id);
                    
        
        
	return db_query($sql,"tasks could not be retreived");
    
}


function get_boq_group_task_groups($project_id, $boq_group_id)
{
    if(!($boq_group_id > 0)){
        $boq_group_id = 0;
    }
        
        $sql = "SELECT ptg.*, tn.value_name type_name, 
                        vtgs.budget, vtgs.budget_used, vtgs.budget_allocated 
                FROM ".TB_PREF."project_task_groups ptg,
                        ".TB_PREF."list_values tn,
                        ".TB_PREF."vw_task_group_summaries vtgs 
                WHERE ptg.type_id = tn.id AND 
                        ptg.id=vtgs.task_group_id AND 
                        ptg.project_id =". db_escape($project_id)." AND 
                        ptg.boq_group_id=".  db_escape($boq_group_id);          
        
	return db_query($sql,"Could not get_boq_group_task_groups");
    
}

function get_task_details($task_id)
{
	$sql = "SELECT pt.*, tn.value_name task_name  
            FROM ".TB_PREF."project_tasks pt,
                    ".TB_PREF."list_values tn 
            WHERE pt.type_id = tn.id AND pt.task_id =".db_escape($task_id);
	
	$result = db_query($sql,"task details could not be retreived");
	
	return db_fetch($result);
}

function get_task_approval_path($task_id)
{
    $sql = " SELECT pt.approval_path_id, ap.approval_path_name FROM "
            .TB_PREF."projects_master pt ,"
            .TB_PREF."project_tasks p ,"
            .TB_PREF."approval_path ap    
WHERE pt.approval_path_id = ap.approval_path_id AND p.project_id=pt.project_id AND p.task_id =". db_escape($task_id);
                
   
    $result = db_query($sql,"approval path could not be retreived");
    return db_fetch($result);
}


function get_task_record($task_id)
{
	$sql = "SELECT pt.* 
            FROM ".TB_PREF."project_tasks pt  
            WHERE pt.task_id =".db_escape($task_id);
	
	$result = db_query($sql,"task details could not be retreived");
	
	return db_fetch($result);
}

function get_est_task_record_by_detail_type($task_group_id, $detail_type_code)
{
    $sql = "SELECT pt.* 
            FROM ".TB_PREF."est_project_tasks pt  
            WHERE pt.task_group_id =".db_escape($task_group_id)." 
                AND pt.detail_type_code =".db_escape($detail_type_code);

    $result = db_query($sql,"Could not get_est_task_record_by_detail_type");

    return db_fetch($result);
}


function get_task_projects($tasks_in)
{
    $sql = "SELECT task_id, project_id, description task_name FROM ".TB_PREF."project_tasks WHERE id IN (".$tasks_in.")";
	
	return db_query($sql,"Could not get_task_projects");
	
}

function get_boq_group_tasks_summary($boq_group_id, $project_id)
{
    $sql = "SELECT SUM(budget) budget, SUM(budget_used) budget_used, SUM(budget_allocated) budget_allocated
            FROM ".TB_PREF."project_tasks pt, 
                    ".TB_PREF."project_task_groups ptg 
            WHERE pt.task_group_id = ptg.id "; 
    
    if($boq_group_id > 0){
        $sql .= " AND ptg.boq_group_id=".  db_escape($boq_group_id);
    }
    else{
        $sql .= " AND ptg.project_id=".  db_escape($project_id)." AND ptg.boq_group_id=".  db_escape($boq_group_id);
    }

	$result = db_query($sql,"tasks could not be retreived");
    return db_fetch($result);
    
}

function get_no_boq_group_tasks_summary($project_id)
{
    $sql = "SELECT SUM(budget) budget, SUM(budget_used) budget_used, SUM(budget_allocated) budget_allocated
            FROM ".TB_PREF."project_tasks pt, 
                    ".TB_PREF."project_task_groups ptg 
            WHERE pt.task_group_id = ptg.id AND 
                    ptg.boq_group_id = 0 AND 
                    ptg.project_id=".  db_escape($project_id);

	$result = db_query($sql,"Could not get_no_boq_group_tasks_summary");
    return db_fetch($result);
    
}

function get_tasks_advance_search_sql($project_id)
{
    $sql = "SELECT pt.task_id, pt.task_code, pt.type_id, pt. description , lv.value_name task_name 
            FROM ".TB_PREF."project_tasks pt 
                    LEFT JOIN ".TB_PREF."list_values lv ON 
                            pt.type_id = lv.id 
            WHERE pt.project_id=".  db_escape($project_id);
    
    return $sql;
}

function get_task_group_details($task_group_id)
{
	$sql = "SELECT ptg.*, tn.value_name task_name  
            FROM ".TB_PREF."project_task_groups ptg,
                    ".TB_PREF."list_values tn 
            WHERE ptg.type_id = tn.id AND ptg.id =".db_escape($task_group_id);
	
	$result = db_query($sql,"Could not get_task_group_details");
	
	return db_fetch($result);
}

function get_task_group_tasks($project_id, $task_group_id)
{
    if($task_group_id > 0){ 
        $sql = "SELECT pt.*,
                        tdt.id detail_type_id,
                        tdt.value_code detail_type_code,
                        tdt.value_name detail_type_name 
                FROM ".TB_PREF."list_values tdt  
                        INNER JOIN ".TB_PREF."project_task_groups ptg ON 
                                ptg.id=".db_escape($task_group_id)."                             
                        LEFT JOIN ".TB_PREF."project_tasks pt ON 
                                tdt.value_code = pt.detail_type_code  AND 
                                pt.task_group_id = ptg.id                             
                WHERE tdt.list_code = 'PROJECT_TASK_DETAIL_TYPES' AND  
                        ptg.project_id=".  db_escape($project_id); 
    }
    else{
        $sql = "SELECT pt.*,
                        tdt.id detail_type_id,
                        tdt.value_code detail_type_code,
                        tdt.value_name detail_type_name 
                FROM ".TB_PREF."list_values tdt                              
                        LEFT JOIN ".TB_PREF."project_tasks pt ON 
                                tdt.value_code = pt.detail_type_code  AND 
                                pt.task_group_id = -1                             
                WHERE tdt.list_code = 'PROJECT_TASK_DETAIL_TYPES'"; 
    }

        
	return db_query($sql,"tasks could not be retreived");
    
}

function get_other_task_groups_budget_total($project_id, $task_group_id)
{
    $sql = "SELECT SUM(budget) budget_total 
            FROM ".TB_PREF."project_tasks 
            WHERE project_id = ".db_escape($project_id)." AND 
                    task_group_id <> ".db_escape($task_group_id);
    
    $result = db_query($sql,"Could not get_other_task_groups_budget_total");	
	$budget_sum = db_fetch($result);
    $budget_total = 0;
    if(isset($budget_sum['budget_total']) && $budget_sum['budget_total'] > 0){
        $budget_total = $budget_sum['budget_total'];
    }
    return $budget_total;
}

function get_existing_code_from_project_task_groups($project_id, $code, $selected_id)
{
    $sql = "SELECT id, code
            FROM ".TB_PREF."project_task_groups
            WHERE project_id = ".db_escape($project_id)."AND 
                    code = ".db_escape($code)." AND    
                    id <> ".db_escape($selected_id);
    
    $result = db_query($sql, "Could not get_existing_code_from_project_task_groups");
    return db_num_rows($result);    
}


function get_est_task_group_details_by_code($est_project_id, $code)
{
    $sql = "SELECT ptg.*, tn.value_name task_name  
            FROM ".TB_PREF."project_task_groups ptg,
                    ".TB_PREF."list_values tn 
            WHERE ptg.type_id = tn.id 
                AND ptg.project_id =".db_escape($est_project_id)." 
                AND ptg.code =".db_escape($code);
	
    $result = db_query($sql,"Could not get_est_task_group_details_by_code");
	
    return db_fetch($result);
}

function get_task_group_record_by_code($project_id, $code)
{
	$sql = "SELECT ptg.* 
            FROM ".TB_PREF."project_task_groups ptg 
            WHERE ptg.project_id =".db_escape($project_id)." AND 
                    ptg.code =".db_escape($code);
	
	$result = db_query($sql,"Could not get_task_group_record_by_code");
	
	return db_fetch($result);
}

function get_other_task_budgets_total($project_id, $task_id)
{
    $sql = "SELECT SUM(budget) budget_total 
            FROM ".TB_PREF."project_tasks 
            WHERE project_id = ".db_escape($project_id)." AND 
                    task_id <> ".db_escape($task_id);
    
    $result = db_query($sql,"Could not get_other_task_budgets_total");	
	$budget_sum = db_fetch($result);
    $budget_total = 0;
    if(isset($budget_sum['budget_total']) && $budget_sum['budget_total'] > 0){
        $budget_total = $budget_sum['budget_total'];
    }
    return $budget_total;
}


function get_project_tasks_current_budgets_total($project_id)
{
    $sql = "SELECT SUM(budget) current_budgets_total,
                    SUM(new_budget - budget) unapproved_budgets_total
        
            FROM ".TB_PREF."project_tasks
                
            WHERE project_id=".db_escape($project_id);
    
    $result = db_query($sql);
    return db_fetch($result); 
    
    
}

function get_project_tasks_cost_budgets_total($project_id)
{
    $sql = "SELECT SUM(budget) current_budgets_total,
                    SUM(new_budget - budget) unapproved_budgets_total
        
            FROM ".TB_PREF."project_tasks pt 
                JOIN ".TB_PREF."project_task_detail_types dt ON 
                    pt.detail_type_code = dt.detail_type_code 
                JOIN ".TB_PREF."list_values cat ON 
                    dt.category_id = cat.id AND 
                    cat.value_code <> 'OVERHEAD'                 
            WHERE project_id=".db_escape($project_id);
    
    $result = db_query($sql);
    return db_fetch($result); 
}

function get_project_tasks_overhead_budgets_total($project_id)
{
    $sql = "SELECT SUM(budget) current_budgets_total,
                    SUM(new_budget - budget) unapproved_budgets_total
        
            FROM ".TB_PREF."project_tasks pt 
                JOIN ".TB_PREF."project_task_detail_types dt ON 
                    pt.detail_type_code = dt.detail_type_code 
                JOIN ".TB_PREF."list_values cat ON 
                    dt.category_id = cat.id AND 
                    cat.value_code = 'OVERHEAD'                 
            WHERE project_id=".db_escape($project_id);
    
    $result = db_query($sql);
    return db_fetch($result); 
}


function update_project_task_approved_budget($task_id, $new_budget_value, $row_state)
{
    $sql = "UPDATE ".TB_PREF."project_tasks
            SET budget=".db_escape($new_budget_value).",
                row_state=". db_new_row_state($row_state)." 
            WHERE task_id=".db_escape($task_id)." AND 
                row_state =".db_escape($row_state);
    
    db_query($sql);
    return db_num_affected_rows();

}
 
 
//function get_task_record_by_code($task_group_id, $detail_type_code)
//{
//	$sql = "SELECT pt.* 
//            FROM ".TB_PREF."project_tasks pt  
//            WHERE pt.task_group_id =".db_escape($task_group_id)." AND 
//                    pt.detail_type_code =".db_escape($detail_type_code);
//	
//	$result = db_query($sql,"Could not get_task_record_by_code");
//	
//	return db_fetch($result);
//}



?>