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

function add_vehicle_requests($project_id, $task_id,$vehicle_type_id, $package_id, 
                              $amount, $from_date_time, $to_date_time,                        
                              $passengers, $route, $vehicle_id, $comment)
{
        $created_by = $_SESSION['wa_current_user']->user;
        $created_on = date('Y-m-d H:i:s');        
        
	$sql = "INSERT INTO ".TB_PREF."vehicle_requests (
                           project_id, task_id,vehicle_type_id, package_id, 
                           amount, from_date_time, to_date_time, 
                           passengers, route, vehicle_id, comment,  
                           created_by, created_on) 
                VALUES ("
                    .db_escape($project_id).", "
                    .db_escape($task_id).", ".db_escape($vehicle_type_id).", " .db_escape($package_id).", " 
                    .db_escape($amount). ", ".db_escape($from_date_time).", "
                    .db_escape($to_date_time).", ".db_escape($passengers).", "
                    .db_escape($route).", ".db_escape($vehicle_id).", "
                    .db_escape($comment).","
                    .db_escape($created_by).", ".db_escape($created_on).")";
        
        db_query($sql,"Vehicle Request could not be added");        
        return db_insert_id();

}


function update_vehicle_requests($selected_id, $project_id, $task_id,$vehicle_type, $package_id, 
                                 $amount, $from_date_time, $to_date_time,                        
                                 $passengers, $route, $vehicle_id, $comment)
        
{
         $updated_by = $_SESSION['wa_current_user']->user;
         $updated_on = date('Y-m-d H:i:s');

        $sql = "UPDATE ".TB_PREF."vehicle_requests 
                SET                     
                    project_id=".db_escape($project_id).",
                    task_id=".db_escape($task_id).",
                    vehicle_type_id=".db_escape($vehicle_type).",
                    package_id=".db_escape($package_id).",                   
                    amount=".db_escape($amount).",
                    from_date_time =".db_escape($from_date_time).",
                    to_date_time =".db_escape($to_date_time).",
                    passengers = ".db_escape($passengers).", 
                    route = ".db_escape($route).",      
                    vehicle_id = ".db_escape($vehicle_id).",      
                    comment = ".db_escape($comment).",      
                    updated_by=".db_escape($updated_by).",
                    updated_on=".db_escape($updated_on)." 
                WHERE id=".db_escape($selected_id);

        db_query($sql,"Selected vehicle request could not be updated");
        return db_num_affected_rows();

}


function delete_vehicle_requests($selected_id)
{
        $sql = "DELETE FROM ".TB_PREF."vehicle_requests  
                WHERE id=".db_escape($selected_id)."  ";                    

        db_query($sql," Vehicle requests could not be deleted");        
        return db_num_affected_rows();
}

function get_unapproved_vehicle_requests()
{
    $sql = "SELECT vr.*, 
                   pm.description project_description,
                   pt.description task_description,
                   vp.description package_description                    
            FROM  ".TB_PREF."vehicle_requests AS vr 
                    LEFT JOIN ".TB_PREF."projects_master AS pm ON
                            vr.project_id = pm.project_id 
                    LEFT JOIN ".TB_PREF."project_tasks AS pt ON 
                            vr.task_id = pt.task_id
                    LEFT JOIN ".TB_PREF."vehicle_packages AS vp ON 
                            vr.package_id = vp.id 
            WHERE vr.closed = 0 AND vr.approval_status IN (".AS_NEW.",".AS_AWAITING_APPROVAL.",".AS_WITH_THE_APPROVER.")";
               
    return db_query($sql, "Could not get vehicle requests");
}

function get_open_vehicle_requests($from_date = null, $to_date = null, $project_id = 0, $originator_id = 0)
{
    $sql = "SELECT vr.*, 
                   pm.description project_description,
                   pt.description task_description,
                   vp.description package_description,
                   vp.cost,
                   vtn.value_name vehicle_type_description 
            FROM  ".TB_PREF."vehicle_requests AS vr 
                    LEFT JOIN ".TB_PREF."projects_master AS pm ON
                            vr.project_id = pm.project_id 
                    LEFT JOIN ".TB_PREF."project_tasks AS pt ON
                            vr.task_id = pt.task_id 
                    LEFT JOIN ".TB_PREF."vehicle_packages AS vp ON 
                            vr.package_id = vp.id 
                    LEFT JOIN ".TB_PREF."list_values AS vtn ON 
                            vr.vehicle_type_id = vtn.id 
            WHERE vr.closed = 0 AND vr.approval_status IN (".AS_APPROVED.")";
    
            if($from_date != null){
                $sql .= " AND vr.to_date_time >= ".db_escape(date2sql($from_date));
            }
            
            if($to_date != null){
        //        $to_date = add_days($to_date, 1);
                $sql .= " AND vr.from_date_time < ".db_escape(date2sql($to_date));
            }
            
            if($project_id > 0){
                $sql .= " AND vr.project_id = ".db_escape($project_id);
            }
            
            if($originator_id > 0){
                $sql .= " AND vr.created_by >= ".db_escape($originator_id);
            }
            
               
    return db_query($sql, "Could not get vehicle requests");
}

function get_vehicle_request($selected_id)
{
    $sql = "SELECT *
            FROM ".TB_PREF."vehicle_requests
            WHERE id=".db_escape($selected_id);
            
    $result = db_query($sql, "Could not get vehicle requests for edit");
    return db_fetch($result);
}


function close_vehicle_request($vr_line, $current_user_id)
        
{
    begin_transaction();
    
    $msg = remove_vehicle_request_task_allocations($vr_line, $current_user_id);
    if($msg['success'] !== true){
        cancel_transaction();
        return $msg;
    }

    $sql = "UPDATE ".TB_PREF."vehicle_requests 
            SET                     
                closed = 1 
            WHERE id=".db_escape($vr_line['vehicle_request_id'])." AND closed = 0";

    db_query($sql,"could not close_vehicle_requests");

    if(db_num_affected_rows() == 1){
        commit_transaction();
        return array("success" => true, "trans_no" => $vr_line['vehicle_request_id'], "message" => "Vehicle Request ".$vr_line['vehicle_request_id']." has been closed.");
    }
    else{
        return array("success" => false, "trans_no" => $vr_line['vehicle_request_id'], "message" => "Another user has updated Vehicle Request ".$vr_line['vehicle_request_id'].".");
    }

}

function remove_vehicle_request_task_allocations($vr_line, $current_user_id)
{
    $task_costs_summary = array();
    
    $cost = get_vehicle_package_cost($vr_line['package_id']);
    add_to_task_cost($task_costs_summary, $vr_line['task_id'], $cost, $cost, $cost);
 
    get_task_cost_details($task_costs_summary);

    $rta_msg = remove_task_allocations(ST_VEHICLE_REQUEST, $vr_line['vehicle_request_id'], $task_costs_summary, $current_user_id);

    return $rta_msg;
}


function view_vehicle_request_details($vehicle_req_no)
{
        
        $sql = "SELECT 
                        vr.id,
                        dp.value_name department,
                        p.description project,
                        pt.description task,
                        vp.description vahicle_package,
                        vr.from_date_time,
                        vr.to_date_time,
                        u.real_name requestor_name,
                        vr.created_on,
                        vr.approval_status,
                        vr.passengers,
                        vr.route,
                        vr.amount,
                        vr.comment,
                        CONCAT(vh.plate_number, ' - ', vh.description) vehicle_description,
                        vtn.value_name vehicle_type_name 
                       
                    FROM ".TB_PREF."vehicle_requests vr
                        LEFT JOIN ".TB_PREF."projects_master p ON
                            p.project_id=vr.project_id
                        LEFT JOIN ".TB_PREF."project_tasks pt ON
                            pt.task_id=vr.task_id
                        LEFT JOIN ".TB_PREF."list_values dp ON
                            dp.id=p.department_id    
                        LEFT JOIN ".TB_PREF."users u ON
                            u.id=vr.created_by
                        LEFT JOIN ".TB_PREF."list_values vtn ON
                            vtn.id=vr.vehicle_type_id
                        LEFT JOIN ".TB_PREF."vehicles vh ON
                            vh.id=vr.vehicle_id
                        LEFT JOIN ".TB_PREF."vehicle_packages vp ON
                            vp.id=vr.package_id
                    WHERE  vr.id = ".$vehicle_req_no;
       
	  $result = db_query($sql,"could not close_vehicle_requests");
          return db_fetch($result);
}
           
function get_sql_for_vehicle_request_view($required_by_from = null, $required_by_to = null, 
            $project_id= null, $vehicle_req_no =null,$package_id = null, $requester_id = null)
{
        
        $sql = "SELECT 
                        vr.id,
                        p.description project,
                        lv.value_name vehicle_type,
                        vp.description vahicle_package,
                        vh.plate_number,
                        vr.from_date_time,
                        vr.to_date_time,
                        u.real_name requestor_name,
                        vr.created_on,
                        vr.approval_status,
                        vr.closed 
                       
                    FROM 
                        ".TB_PREF."vehicle_requests vr
                    LEFT JOIN ".TB_PREF."projects_master p ON
                        p.project_id=vr.project_id  
                    LEFT JOIN ".TB_PREF."users u ON 
                        u.id = vr.created_by
                    LEFT JOIN ".TB_PREF."vehicles vh ON
                        vr.vehicle_id=vh.id 
                     LEFT JOIN ".TB_PREF."list_values lv ON
                        lv.id=vr.vehicle_type_id
                    LEFT JOIN ".TB_PREF."vehicle_packages vp ON
                        vp.id=vr.package_id
                    WHERE  1 ";
      
         if($required_by_from != null)
        {
            $sql .= " AND vr.from_date_time > ".db_escape(date2sql($required_by_from));
        }
        
        if($required_by_to != null)
        {
                $sql .= " AND vr.to_date_time < ".db_escape(date2sql($required_by_to));
        }
        
        if($package_id != '-1')
        {
                $sql .= " AND vr.package_id = ".db_escape($package_id);            
        }
         if($project_id !='')
        {
                $sql .= " AND vr.project_id = ".db_escape($project_id);            
        }
        if ($vehicle_req_no != 0)
        {
                $sql .= " AND vr.id = ".$vehicle_req_no;
        }
        if ($requester_id > 0)
        {
                $sql .= " AND vr.created_by = ".$requester_id;
        }
       
        

        $sql .= " ORDER BY vr.id ";   
	return $sql;
}

?>