<?php
class Vehicle_trip_model extends CI_Model{
	
	
	public function get_vehicle_type() {
	   $this->db->select('veh_type_id,veh_type');
	   $this->db->from('veh_type');
	   $this->db->where('veh_type.status', 1);
	   $this->db->order_by('veh_type', 'asc');
	   return $this->db->get()->result();
	   
	   
	}
	public function get_vehicle_no($vehicle_type) {
		$this->db->select("vehicle_id,vehicle_no");
		$this->db->from('vehicle');
		$this->db->where_in('vehicle.vehicle_type', $vehicle_type);
	    $this->db->order_by('vehicle_id', 'asc');
	   return $this->db->get()->result();
	}
	
	public function search($start_date,$end_date,$vehicle_type,$vehicle_no){
		$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 op_vehicle_id,vehicle_no, veh_type,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 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')   $type_qry $vch_qry GROUP BY op_vehicle_id";
			
		$query_info = $this->db->query($query);
		return $query_info->result();
	}
}
?>