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

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

}

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

        $sql = "UPDATE ".TB_PREF."vehicle_allocations 
                SET                     
                    vehicle_request_id=".db_escape($vehicle_request_id).",
                    project_id=".db_escape($project_id).",
                    task_id=".db_escape($task_id).",
                    package_id=".db_escape($package_id).",     
                    vehicle_id=".db_escape($vehicle_id).",     
                    driver_id=".db_escape($driver_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).",   
                    supplier_id = ".db_escape($supplier_id).",   
                    route = ".db_escape($route).",   
                    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 allocation could not be updated");
        return db_num_affected_rows();

}

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

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


function get_open_vehicle_allocations($vehicle_id = 0, $from_date = null, $to_date = null, $owned_hired = null)
{
    if(!($vehicle_id > 0)){
        $vehicle_id = 0;
    }
    $sql = "SELECT va.*, 
                    pm.description project_description,
                    pt.description task_description,
                    vp.description package_description,
                    vh.plate_number,
                    vh.hired,
                    vp.cost 
            FROM  ".TB_PREF."vehicle_allocations AS va 
                    LEFT JOIN ".TB_PREF."projects_master AS pm ON
                            va.project_id = pm.project_id 
                    LEFT JOIN ".TB_PREF."project_tasks AS pt ON 
                            va.task_id = pt.task_id
                    LEFT JOIN ".TB_PREF."vehicle_packages AS vp ON 
                            va.package_id = vp.id
                    LEFT JOIN ".TB_PREF."vehicles AS vh ON 
                           va.vehicle_id = vh.id 
            WHERE va.closed = 0";
    
    if($vehicle_id > 0){
        $sql .= " AND va.vehicle_id = $vehicle_id";
    }
    if($from_date != null){
        $sql .= " AND va.to_date_time >= ".db_escape(date2sql($from_date));
    }
    if($to_date != null){
//        $to_date = add_days($to_date, 1);
        $sql .= " AND va.from_date_time < ".db_escape(date2sql($to_date));
    }
    if($owned_hired != null){
        switch ($owned_hired) {
            case 'COMPANY':
                $sql .= " AND vh.hired = 0 ";
                break;
            case 'HIRED':
                $sql .= " AND vh.hired = 1 ";
                break;
            case 'ALL':
            default:
                break;
        }
        
    }
               
    return db_query($sql, "Could not get vehicle allocation");
}

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

function close_vehicle_allocation($selected_id)
        
{
//         $updated_by = $_SESSION['wa_current_user']->user;
//         $updated_on = date('Y-m-d H:i:s');

        $sql = "UPDATE ".TB_PREF."vehicle_allocations 
                SET                     
                    closed = 1 
                WHERE id=".db_escape($selected_id);

        db_query($sql,"Could not close_vehicle_allocation");

}

function view_vehicle_allocation($vehicle_allo_no)
{
        
        $sql = "SELECT 
                        vr.*,
                        dp.value_name department,
                        p.description project,
                        pt.description task,
                        vp.description vahicle_package,
                        CONCAT(am.asset_code,'',am.description)vehicle,
                        u.real_name requestor_name
                       
                    FROM 
                        ".TB_PREF."vehicle_allocations 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."asset_master am ON
                        am.asset_id=vr.vehicle_id
                    LEFT JOIN ".TB_PREF."vehicle_requests vs ON
                        vr.vehicle_request_id=vs.id
                    LEFT JOIN ".TB_PREF."users u ON
                        u.id=vs.created_by
                    LEFT JOIN ".TB_PREF."vehicle_packages vp ON
                        vp.id=vr.package_id
                    WHERE vr.id = ".$vehicle_allo_no;
       
        
         $result =db_query($sql,"could not vehicle allocation details");
         return db_fetch($result);
}
function get_sql_for_vehicle_allocation_view($required_by_from = null, $required_by_to = null, 
            $project_id= null, $vehicle_allo_no =null, $vehicle = null,$vehicle_req_no = null, $requester_id = null)
{
        
        $sql = "SELECT 
                        vr.id,
                        vr.vehicle_request_id,
                        p.description project,
                        vp.description vahicle_package,
                        CONCAT(vh.plate_number,' ',vh.description)vehicle,
                        vr.from_date_time,
                        vr.to_date_time,
                        u.real_name requestor_name,
                        vr.amount 
                       
                    FROM 
                        ".TB_PREF."vehicle_allocations vr
                    LEFT JOIN ".TB_PREF."projects_master p ON
                        p.project_id=vr.project_id
                    LEFT JOIN ".TB_PREF."vehicles vh ON
                        vh.id=vr.vehicle_id
                    LEFT JOIN ".TB_PREF."vehicle_requests vs ON
                        vr.vehicle_request_id=vs.id
                    LEFT JOIN ".TB_PREF."users u ON
                        u.id=vs.created_by
                    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($project_id !='')
        {
                $sql .= " AND vr.project_id = ".db_escape($project_id);            
        }
        if ($vehicle_allo_no != 0)
        {
                $sql .= " AND vr.id = ".$vehicle_allo_no;
        }
        if ($vehicle != '-1')
        {
                $sql .= " AND vr.vehicle_id = ".$vehicle;
        }
        if ($vehicle_req_no != 0)
        {
                $sql .= " AND vr.vehicle_request_id = ".$vehicle_req_no;
        }
        if ($requester_id > 0)
        {
                $sql .= " AND vs.created_by = ".$requester_id;
        }
       
        

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


function update_vehicle_allocation_payment_source($id, $payment_source_type, $payment_source_no, $payment_amount)
{
    $sql = "UPDATE ".TB_PREF."vehicle_allocations 
            SET payment_source_type = ".db_escape($payment_source_type).", 
                payment_source_no = ".db_escape($payment_source_no).", 
                paid_amount = ".db_escape($payment_amount)." 
            WHERE id=".db_escape($id)." AND 
                    payment_source_no=0";
    
    db_query($sql);
    
    return db_num_affected_rows();
    
}



function get_vehicle_allocation_record_by_payment_source($payment_source_type, $payment_source_no)
{
    $sql = "SELECT *     
            FROM ".TB_PREF."vehicle_allocations 
            WHERE payment_source_type = ".db_escape($payment_source_type)." AND 
                payment_source_no = ".db_escape($payment_source_no);
    
    $result = db_query($sql);    
    return db_fetch($result);

}



function get_vehicle_allocation_record_by_payment_vrc_entry($vehicle_running_chart_entry)
{
    $sql = "SELECT *     
            FROM ".TB_PREF."vehicle_allocations 
            WHERE settled_vrc_entry_no = ".db_escape($vehicle_running_chart_entry);
    
    $result = db_query($sql);    
    return db_fetch($result);

}


function get_supplier_transport_invoice_vehicle_allocations($sti_no)
{
    if(!($sti_no > 0)){
        $sti_no = 0;
    }

    $sql = "SELECT va.*, 
                    pm.description project_description,
                    vp.description package_description,
                    vh.plate_number,
                    vh.hired,
                    vp.cost,
                    s.supp_name 
            FROM  ".TB_PREF."vehicle_allocations AS va 
                    LEFT JOIN ".TB_PREF."projects_master AS pm ON
                            va.project_id = pm.project_id 
                    LEFT JOIN ".TB_PREF."vehicle_packages AS vp ON 
                            va.package_id = vp.id
                    LEFT JOIN ".TB_PREF."vehicles AS vh ON 
                           va.vehicle_id = vh.id 
                    LEFT JOIN ".TB_PREF."suppliers s ON 
                           va.supplier_id = s.supplier_id 
            WHERE va.payment_source_no > 0 AND 
                    va.settled_vrc_entry_no = 0 AND 
                    va.transport_inv_no = ".db_escape($sti_no);
    

               
    return db_query($sql, "Could not get vehicle allocation");
}


function add_vehicle_allocation_to_supp_transport_invoice($id, $inv_no)
{
        $sql = "UPDATE ".TB_PREF."vehicle_allocations 
                SET transport_inv_no = ".db_escape($inv_no)."  
                WHERE id=".db_escape($id)." AND 
                        payment_source_no > 0 AND 
                        settled_vrc_entry_no = 0 AND 
                        transport_inv_no = 0 ";

    db_query($sql);
    
    return db_num_affected_rows();
}


function update_vehicle_allocation_settled_vrc_entry_no($vehicle_allocation_id, $settled_vrc_id)
{
    $sql = "UPDATE ".TB_PREF."vehicle_allocations 
            SET settled_vrc_entry_no = ".db_escape($settled_vrc_id)."  
            WHERE id=".db_escape($vehicle_allocation_id)." AND 
                    payment_source_no > 0 AND transport_inv_no = 0 AND settled_vrc_entry_no = 0";
         
    db_query($sql);
    
    return db_num_affected_rows();
    
}

?>