<?php
class Utilization_model extends CI_Model{
		
	public function search($start_date,$end_date){
		// $type_qry = "";
		// if($vehicle_type){
		// 	$vehicle_type = implode(",",$vehicle_type);
		// 	$type_qry = "and sblt_operation.vehicle_type in ($vehicle_type)";
		// }
		// $vch_qry = "";
		// if($vehicle_no){
		// 	$vehicle_no = implode(",",$vehicle_no);
		// 	$vch_qry = "and sblt_operation_line.op_vehicle_id in ($vehicle_no)";
		// }
		
		$query = "SELECT first_name as d_name,op_driver_id,SUM(CASE WHEN sblt_booking.cust_type !='1' and sblt_booking.trip_type != '2' THEN sblt_booking.trip_days ELSE 0 END) tour_days, SUM(CASE WHEN sblt_booking.trip_type NOT IN ('1','6') and sblt_booking.cust_type !='1' THEN sblt_booking.trip_days ELSE 0 END) local_days, SUM(CASE WHEN sblt_booking.cust_type ='1' THEN sblt_booking.trip_days ELSE 0 END) trip_days, SUM(CASE WHEN sblt_booking.trip_type ='6' THEN sblt_booking.trip_days ELSE 0 END) company_days  FROM `sblt_operation_line` INNER JOIN sblt_operation on sblt_operation.operation_id =sblt_operation_line.operation_id INNER JOIN sblt_booking on sblt_booking.booking_id =sblt_operation.booking_id inner join sblt_vehicle on sblt_vehicle.vehicle_id = sblt_operation_line.op_vehicle_id inner join sblt_veh_type on sblt_veh_type.veh_type_id = sblt_vehicle.vehicle_type inner join sblt_employees on sblt_employees.id = op_driver_id inner join sblt_people on sblt_people.person_id = sblt_employees.person_id where (DATE_FORMAT(sblt_operation.from_date, '%Y-%m-%d') BETWEEN '$start_date' and '$end_date' or DATE_FORMAT(sblt_operation.to_date, '%Y-%m-%d') BETWEEN '$start_date' and '$end_date') and sblt_operation_line.status = '1' and operation_sts in ('4','5') GROUP BY d_name";
			
		$query_info = $this->db->query($query);
		return $query_info->result();
	}
}
?>