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

function update_project($selected_id, $project_code, $description,
                        $long_description, $project_manager_id,$customer_id,$project_manager_name,$contact_person, $inactive, 
                        $start_date, $end_date, $editing_project_value, $estimated_cost, $rate_include_taxes, 
                        $type_id, $department_id, $purchase_to_stock, $data_access_group_id, 
                        $revenue_account, $cost_account, $frozen_date, $project_status_id, $overhead_percentage, $employee_advances_account)
{			
        $id_user = $_SESSION['wa_current_user']->user;
        $datetime = date('Y-m-d H:i:s');
        
//        $approved_by = $_SESSION['wa_current_user']->user;
//        $approved_on = date('Y-m-d H:i:s');
        
        $sql = "UPDATE ".TB_PREF."projects_master SET 
                        long_description=".db_escape($long_description).",
                        project_manager_id=".db_escape($project_manager_id).",
                        updated_on=".db_escape($datetime).",
                        updated_by=".db_escape($id_user).",
                        inactive=".db_escape($inactive).",
                        editing_project_value=".db_escape($editing_project_value).",
                        estimated_cost=".db_escape($estimated_cost).",
                        rate_include_taxes=".db_escape($rate_include_taxes).",
                        project_code=".db_escape($project_code).",
                        description=".db_escape($description).",
                        start_date=".db_escape(date2sql($start_date)).",
                        end_date=".db_escape(date2sql($end_date)).", 
                        project_manager_name=".db_escape($project_manager_name).",
                        contact_person=".db_escape($contact_person).", 
                        customer_id=".db_escape($customer_id).", 
                        type_id=".db_escape($type_id).", 
                        department_id=".db_escape($department_id).", 
                        purchase_to_stock=".db_escape($purchase_to_stock).",
                        dag_id=".db_escape($data_access_group_id).",
                        revenue_account=".db_escape($revenue_account).",
                        cost_account=".db_escape($cost_account).",
                        budget_freeze_date=".db_escape(date2sql($frozen_date)).",
                        project_status_id=".db_escape($project_status_id).",    
                        overhead_percentage=".db_escape($overhead_percentage).", 
                        employee_advances_account=".db_escape($employee_advances_account);      

        $sql .= " WHERE project_id=".db_escape($selected_id);

        db_query($sql, "The project could not be updated");

}


function add_project($description, $long_description, $inactive, 
                    $project_manager_id, $company_category_code, $group_serial_no, 
                    $company_serial_no, $year, $customer_category_code, 
                    $project_category_code, $category_serial_no, $start_date, 
                    $end_date,$customer_id,$project_manager_name,$contact_person, 
                    $editing_project_value,$estimated_cost, $rate_include_taxes, $project_code, 
                    $project_name,$type_id, $department_id, $purchase_to_stock, $data_access_group_id,
                    $revenue_account, $cost_account, $approval_status, $add_budgets,
                    $frozen_date, $project_status_id, $overhead_percentage, $employee_advances_account)
{
        $id_user = $_SESSION['wa_current_user']->user;
        $datetime = date('Y-m-d H:i:s');
        
        $sql = "INSERT INTO ".TB_PREF."projects_master (
                            description, long_description,
                            project_manager_id, company_category_code, 
                            group_serial_no, company_serial_no, 
                            year, customer_category_code, 
                            inactive, project_category_code, 
                            category_serial_no, start_date, 
                            end_date,project_manager_name,
                            contact_person,customer_id, 
                            editing_project_value,estimated_cost, rate_include_taxes,
                            project_code, project_name,
                            created_on, created_by,
                            type_id, department_id, 
                            purchase_to_stock, dag_id,
                            revenue_account, cost_account,
                            approval_status, add_budgets, budget_freeze_date,
                            project_status_id, overhead_percentage,
                            employee_advances_account)
                         
                
                VALUES (".db_escape($description).", ".db_escape($long_description).","
                        .db_escape($project_manager_id).", ".db_escape($company_category_code).","
                        .db_escape($group_serial_no).", ".db_escape($company_serial_no).","
                        .db_escape($year).", ".db_escape($customer_category_code).", "
                        .db_escape($inactive).", ".db_escape($project_category_code).","
                        .db_escape($category_serial_no).", ".db_escape(date2sql($start_date)).", "
                        .db_escape(date2sql($end_date)).",".db_escape($project_manager_name).", ".db_escape($contact_person).", "
                        .db_escape($customer_id).",".db_escape($editing_project_value).",".db_escape($estimated_cost).",".db_escape($rate_include_taxes).","
                        .db_escape($project_code).",".db_escape($project_name).","
                        .db_escape($datetime).", ".db_escape($id_user).", "
                        .db_escape($type_id).", ".db_escape($department_id).", "
                        .db_escape($purchase_to_stock).", ".db_escape($data_access_group_id).", "
                        .db_escape($revenue_account).", ".db_escape($cost_account).", "
                        .db_escape($approval_status).", ".db_escape($add_budgets).","
                        .db_escape(date2sql($frozen_date)).", ".db_escape($project_status_id).","
                        .db_escape($overhead_percentage).",".db_escape($employee_advances_account).")"; 
                                               

        db_query($sql, "The project could not be added");

}

function get_project_details($project_id)
{
	$sql = "SELECT ps.description company_category_name,
                        psn.description project_category_name,
                        pc.description customer_category_name,
                        yn.value_name year_value,
                        u.real_name,
                        p.* 
		FROM ".TB_PREF."projects_master p, "
                        .TB_PREF."project_serial_numbers ps, "
                        .TB_PREF."users u, "
                        .TB_PREF."list_values yn, "
                        .TB_PREF."project_serial_numbers pc, "
                        .TB_PREF."project_serial_numbers psn
		WHERE p.project_id = $project_id AND 
                    p.company_category_code = ps.category_code AND
                    p.year = yn.value_code AND
                    p.project_manager_id = u.id AND
                    p.customer_category_code = pc.category_code AND
                    p.project_category_code = psn.category_code ";
	
        $sql = "SELECT u.real_name,
                        p.* 
		FROM ".TB_PREF."projects_master p, " 
                        .TB_PREF."users u, "
                        .TB_PREF."list_values yn 
		WHERE p.project_id = $project_id AND 
                    p.project_manager_id = u.id ";
	$result = db_query($sql,"Project could not be retreived");

	return db_fetch($result);
}


function get_project($project_id)
{
	$sql = "SELECT p.*,
                    dn.value_name department_name,
                    dm.name customer_name,
                    u.real_name project_manger_name 
        	FROM ".TB_PREF."projects_master p 
                INNER JOIN ".TB_PREF."list_values dn ON 
                        p.department_id = dn.id 
                LEFT JOIN ".TB_PREF."debtors_master dm ON 
                        p.customer_id = dm.debtor_no 
                LEFT JOIN ".TB_PREF."users u ON 
                        p.project_manager_id = u.id
            WHERE p.project_id =".db_escape($project_id);
    
	$result = db_query($sql,"Could not get_project");

	return db_fetch($result);
}

function get_project_record($project_id)
{
	$sql = "SELECT p.* 
            FROM ".TB_PREF."projects_master p 
            WHERE p.project_id =".db_escape($project_id);
    
	$result = db_query($sql,"Project record could not be retreived");

	return db_fetch($result);
}

function delete_project($id)
{
	$sql="DELETE FROM ".TB_PREF."projects_master WHERE project_id=".db_escape($id);

	db_query($sql,"Selected project could not be deleted");
}

function get_project_task($project_id, $task_id)
{
    $sql = "SELECT p.description as project_description,
                    apn.value_name task_description
                    
            FROM ".TB_PREF."projects_master p, "
                        .TB_PREF."project_tasks pt, "
                            .TB_PREF."list_values apn
                            
             WHERE  p.project_id = ".db_escape($project_id)." AND 
                    pt.task_id = " .db_escape($task_id)." AND
                    pt.type_id = apn.id ";
    
    $result = db_query($sql,"Project and task could not be retreived");
    
    return db_fetch($result);
}

function get_job_ref($project_id)
{
    $sql="SELECT description FROM ".TB_PREF."projects_master WHERE project_id=".db_escape($project_id);
    
    $result = db_query($sql,"Job referece not be retreived");
    
    return db_fetch($result);
}


function get_project_approval_path($project_id)
{
    $sql = " SELECT pm.approval_path_id, ap.approval_path_name "
            . "FROM ".TB_PREF."projects_master pm ,"
                    .TB_PREF."approval_path ap    
                WHERE pm.approval_path_id = ap.approval_path_id AND pm.project_id= ". db_escape($project_id);
                
   
    $result = db_query($sql,"Project approval path could not be retreived");
    return db_fetch($result);
}


function can_access_project($project_id, $user_id)
{
    $sql = "SELECT project_id  
            FROM ".TB_PREF."projects_master pm 
                    INNER JOIN ".TB_PREF."user_data_access_groups udag ON
                            pm.dag_id = udag.dag_id  
            WHERE pm.project_id = ".db_escape($project_id)." AND 
                    udag.user_id =".db_escape($user_id);
    
    $result = db_query($sql,"Could not check can_access_project");
    if(db_num_rows($result) > 0){
        return array("success" => true, "message" => "");
    }
    else{
        $pro_description = '';
        $project = get_project_record($project_id);
        if (isset($project['description']) && strlen($project['description']) > 0) {
            $pro_description = $project['description'];
        }
        return array("success" => false, "message" => "You do not have access to the project $pro_description");
    }
}


function update_project_approved_values($project_id, $add_budgets, $cost_budgets_total, $overhead_budgets_total, $project_approved_value)
{			
        
    $sql = "UPDATE ".TB_PREF."projects_master SET 
                add_budgets=".db_escape($add_budgets).", 
                approved_cost_budgets_total=".db_escape($cost_budgets_total).", 
                approved_overhead_budgets_total=".db_escape($overhead_budgets_total).",
                project_value=".db_escape($project_approved_value).",
                row_state = row_state + 1 
            WHERE approval_status = 30 AND project_id=".db_escape($project_id);

        db_query($sql, "update_project_approved_values");
        
        return db_num_affected_rows();

}

function update_project_unapproved($project_id)
{			

    $sql = "UPDATE ".TB_PREF."projects_master SET 
                       
                approved = 0,   
                approved_by = 0,  
                approved_on = '', 
                add_budgets = 0 

            WHERE project_id=".db_escape($project_id);

        db_query($sql, "The project could not be updated");
        
        return db_num_affected_rows();

}

function get_department_projects($dep_id)
{
    $sql = "SELECT * FROM ".TB_PREF."projects_master
            WHERE department_id =".db_escape($dep_id);
    
    return db_query($sql, "get_department_projects");        
}

function get_department_projects_accessible($dep_id, $user_id)
{
    $sql = "SELECT * FROM ".TB_PREF."projects_master pm 
                JOIN ".TB_PREF."user_data_access_groups dag 
                    ON pm.dag_id = dag.dag_id 
                    
            WHERE pm.department_id = ".db_escape($dep_id)." 
                AND dag.user_id = ".db_escape($user_id); 
    
    return db_query($sql, "get_department_projects_accessible");        
}

function get_department_projects_count($dep_id)
{
    $sql = "SELECT COUNT(project_id) projects_count 
            FROM ".TB_PREF."projects_master
            WHERE department_id =".db_escape($dep_id);
    
    $result = db_query($sql, "get_department_projects");     
    return db_result_value($result, 'projects_count');
}

function get_project_by_project_code($project_code) {
    $sql = "SELECT * 
            FROM ".TB_PREF."projects_master 
            WHERE project_code = ".db_escape($project_code);
    
	$result = db_query($sql, "get_project_by_project_code");
	return db_fetch($result);
}

?>