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

function add_vehicle_running_chart_entry($vehicle_allocation_id, $project_id, 
                                        $task_id, $package_id, $vehicle_id, $plate_no,
                                        $start_date_time,$meter_start, $end_date_time,
                                         $meter_end, $bill_name, $passengers,
                                         $route, $driver_id, $cost, 
                                        $supplier_id, $comment)
{
        $created_by = $_SESSION['wa_current_user']->user;
        $created_on = date('Y-m-d H:i:s');        
        
	$sql = "INSERT INTO ".TB_PREF."vehicle_running_chart 
                                  (vehicle_allocation_id, project_id, 
                                   task_id, package_id, vehicle_id, plate_no, start_date_time,
                                   meter_start, end_date_time, meter_end,
                                   bill_name, passengers, route, 
                                   driver_id, cost, 
                                   supplier_id, comment,                   
                                   created_by, created_on)                     
                VALUES ("
                    .db_escape($vehicle_allocation_id).", "
                    .db_escape($project_id).", "
                    .db_escape($task_id).", "
                    .db_escape($package_id).", "
                    .db_escape($vehicle_id).", "
                    .db_escape($plate_no).", "
                    .db_escape($start_date_time).", "                  
                    .db_escape($meter_start). ", "
                    .db_escape($end_date_time).", "                   
                    .db_escape($meter_end).", " 
                    .db_escape($bill_name).", "  
                    .db_escape($passengers).", " 
                    .db_escape($route).", "
                    .db_escape($driver_id).", " 
                    .db_escape($cost).", " 
                    .db_escape($supplier_id).", " 
                    .db_escape($comment).", " 
                    .db_escape($created_by).", " 
                    .db_escape($created_on).")";
        
        db_query($sql,"Vehicle running chart entry could not be added");        
        return db_insert_id();
}

function update_vehicle_running_chart_entry($rec_id, $vehicle_allocation_id, 
                                            $project_id, $task_id, 
                                            $package_id, $vehicle_id, $plate_no,
                                            $start_date_time, $meter_start, 
                                            $end_date_time, $meter_end, $bill_name, $passengers,
                                            $route, $driver_id, $cost, 
                                            $supplier_id, $comment )
{

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

        $sql = "UPDATE ".TB_PREF."vehicle_running_chart 
                SET 
                    vehicle_allocation_id=".db_escape($vehicle_allocation_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).",
                    plate_no =".db_escape($plate_no).",
                    start_date_time=".db_escape($start_date_time).",                    
                    meter_start =".db_escape($meter_start).",
                    end_date_time =".db_escape($end_date_time).",
                    meter_end = ".db_escape($meter_end).", 
                    bill_name = ".db_escape($bill_name).", 
                    passengers = ".db_escape($passengers).", 
                    route = ".db_escape($route).", 
                    driver_id = ".db_escape($driver_id).", 
                    cost = ".db_escape($cost).", 
                    supplier_id = ".db_escape($supplier_id).", 
                    comment = ".db_escape($comment).", 
                    updated_by=".db_escape($updated_by).",
                    updated_on=".db_escape($updated_on)." 
                WHERE id=".db_escape($rec_id);

        db_query($sql,"Vehicle running chart entry could not be updated");        
        return db_num_affected_rows();
}

function delete_vehicle_running_chart_entry($rec_id, $vehicle_id)
{
        $sql = "DELETE FROM ".TB_PREF."vehicle_running_chart  
                WHERE id=".db_escape($rec_id)." AND 
                        vehicle_id=".db_escape($vehicle_id)." AND 
                        batch_no = 0 AND 
                        inv_no = 0 AND 
                        payment_source_type = 0 AND 
                        payment_source_no = 0";

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

function get_vehicle_running_chart_entry($id)
{
    $sql = "SELECT vrc.*,
                    va.from_date_time, va.to_date_time,  
                    pm.description project_description,
                    pt.description task_description,
                    vp.description package_description 
            FROM ".TB_PREF."vehicle_running_chart vrc 
                    LEFT JOIN ".TB_PREF."vehicle_allocations va ON
                            vrc.vehicle_allocation_id = va.id 
                    LEFT JOIN ".TB_PREF."projects_master AS pm ON
                            vrc.project_id = pm.project_id 
                    LEFT JOIN ".TB_PREF."project_tasks AS pt ON
                            vrc.task_id = pt.task_id 
                    LEFT JOIN ".TB_PREF."vehicle_packages AS vp ON 
                            vrc.package_id = vp.id 
            WHERE vrc.id = ".db_escape($id);  

    $result = db_query($sql, "Could not get_vehicle_running_chart_entry");    
    return db_fetch($result);
}

function get_vehicle_running_chart_record($id)
{
    $sql = "SELECT vrc.* 
            FROM ".TB_PREF."vehicle_running_chart vrc                      
            WHERE vrc.id = ".db_escape($id);  

    $result = db_query($sql, "Could not get_vehicle_running_chart_record");    
    return db_fetch($result);
}

function get_vehicle_running_chart_entries($asset_id)
{
    $sql = "SELECT vrc.*, 
                    pm.description project_description,
                    pt.description task_description,
                    vp.description package_description,
                    vh.description vehicle_description,
                    vh.hired,
                    vp.cost package_cost,
                    dv.name driver_name,
                    sup.supp_name 
            FROM ".TB_PREF."vehicle_running_chart vrc 
                    LEFT JOIN ".TB_PREF."projects_master AS pm ON
                            vrc.project_id = pm.project_id 
                    LEFT JOIN ".TB_PREF."project_tasks AS pt ON
                            vrc.task_id = pt.task_id 
                    LEFT JOIN ".TB_PREF."vehicle_packages AS vp ON 
                            vrc.package_id = vp.id
                    LEFT JOIN ".TB_PREF."vehicles AS vh ON 
                           vrc.vehicle_id = vh.id 
                    LEFT JOIN ".TB_PREF."drivers AS dv ON 
                           vrc.driver_id = dv.id 
                    LEFT JOIN ".TB_PREF."suppliers AS sup ON 
                           vrc.supplier_id = sup.supplier_id  
            WHERE vrc.vehicle_id = ".db_escape($asset_id)." AND vrc.batch_no=0";           

    return db_query($sql, "Vehicle running chart entry could not be retrieved");      
}

function get_unposted_vehicle_running_chart_entries($from_date, $to_date, $vehicle_id = 0,  $supplier_id = 0, $for_posting = false)
{
    $sql = "SELECT vrc.*, 
                    pm.description project_description,
                    pt.description task_description,
                    vh.description vehicle_description,
                    dr.name driver_name,
                    sup.supp_name 
            FROM ".TB_PREF."vehicle_running_chart vrc 
                    LEFT JOIN ".TB_PREF."projects_master pm ON 
                            vrc.project_id = pm.project_id 
                    LEFT JOIN ".TB_PREF."project_tasks pt ON 
                            vrc.task_id = pt.task_id 
                    LEFT JOIN ".TB_PREF."vehicles AS vh ON 
                           vrc.vehicle_id = vh.id 
                    LEFT JOIN ".TB_PREF."drivers dr ON 
                            vrc.driver_id = dr.id  
                    LEFT JOIN ".TB_PREF."suppliers AS sup ON 
                           vrc.supplier_id = sup.supplier_id  
            WHERE vrc.batch_no = 0 
                    AND start_date_time >= ".db_escape(date2sql($from_date))." AND end_date_time <= ".db_escape(date2sql($to_date));
    if($vehicle_id > 0){
        $sql .= " AND vrc.vehicle_id = ".db_escape($vehicle_id); 
    }
    
    if($supplier_id > 0){
        $sql .= " AND vrc.supplier_id = ".db_escape($supplier_id); 
    }
    
    if($for_posting){
        $sql .= " ORDER BY pm.project_id , pt.task_id, vrc.vehicle_id";           
    }
    else{
         $sql .= " ORDER BY vrc.id ";
    }

    return db_query($sql, "Vehicle running chart details could not be retrieved");      
}

function update_vehicle_running_chart_next_batch_no($next_batch_no,$id)
{

         $sql = "UPDATE ".TB_PREF."vehicle_running_chart 
                SET batch_no=".db_escape($next_batch_no)." 
                WHERE id = ".db_escape($id)." AND batch_no = 0";       
        
         db_query($sql, "Could not update_vehicle_running_chart_next_batch_no");
         
         return db_num_affected_rows();      
    
}


function get_vehicle_running_chart_next_batch_no()
{
    $sql = "SELECT MAX(batch_no) max_batch_no
            FROM ".TB_PREF."vehicle_running_chart ";
    
    $result=db_query($sql, "Batch Number could not retrieved"); 
    
    $max = db_fetch($result);
    
    $next_batch_no = 1;
    if(isset($max['max_batch_no'])){
        $next_batch_no = $max['max_batch_no'] + 1;
    }
    
    return $next_batch_no;
}



function update_vehicle_running_chart_payment_source($vrc_id, $payment_source_type, $payment_source_no, $supplier_charge)
{
    $sql = "UPDATE ".TB_PREF."vehicle_running_chart 
            SET payment_source_type = ".db_escape($payment_source_type).", 
                payment_source_no = ".db_escape($payment_source_no).", 
                supplier_charge = ".db_escape($supplier_charge)."  
            WHERE id=".db_escape($vrc_id)." AND 
                    batch_no > 0 AND inv_no = 0 AND payment_source_no = 0";
         
    db_query($sql);
    
    return db_num_affected_rows();
    
}


function get_vehicle_running_chart_entry_by_payment_source($payment_source_type, $payment_source_no)
{
    $sql = "SELECT *     
            FROM ".TB_PREF."vehicle_running_chart 
            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);

}

?>