<?php
class Vehicle_report_model extends CI_Model{
	
	public function get_vehicle() {
		
		$this->db->from('vehicle');
		$this->db->order_by('vehicle_id', 'asc');
		return $this->db->get();
	}
	
	public function vehicle_report($filters,$search_by,$vehicle_id,$expiry){

		$start_date = $filters['start_date'];
		$end_date   = $filters['end_date']; 
		$start = date('d-M-Y',strtotime($start_date));
		$end = date('d-M-Y',strtotime($end_date));
		if($search_by === "1"){
			$vehicle = "and sblt_vehicle.vehicle_id = '$vehicle_id'";
			if($vehicle_id === "All" || $vehicle_id === ""){
				$vehicle = '';
			}
		/* UDY Change for date issues - 07-07-2018
		$qry = $this->db->query("SELECT vendor_name,doc_to,doc_type,fc_date,vehicle_no,permit_date,veh_type,insur_date from (SELECT vehicle_id , MAX(doc_to) AS doc_to,doc_type FROM sblt_doc GROUP BY vehicle_id,doc_type ) AS new_doc left join sblt_vehicle on new_doc.vehicle_id = sblt_vehicle.vehicle_id inner join sblt_veh_type on sblt_veh_type.veh_type_id = sblt_vehicle.vehicle_type left join sblt_vendor on sblt_vendor.vendor_id = sblt_vehicle.owned_by where sblt_vehicle.status = '1' $vehicle GROUP BY sblt_vehicle.vehicle_id");
		*/
		$qry = $this->db->query("SELECT vendor_name,fc_date,vehicle_no,permit_date,veh_type,insur_date,(SELECT MAX(doc_to) FROM sblt_doc where sblt_doc.vehicle_id = sblt_vehicle.vehicle_id and sblt_doc.doc_type = 'Tax') as tax ,(SELECT MAX(doc_to) FROM sblt_doc where sblt_doc.vehicle_id = sblt_vehicle.vehicle_id and sblt_doc.doc_type = 'TP') as tp from sblt_vehicle INNER join sblt_veh_type on sblt_veh_type.veh_type_id = sblt_vehicle.vehicle_type left join sblt_vendor on sblt_vendor.vendor_id = sblt_vehicle.owned_by where sblt_vehicle.status = '1' $vehicle");
		
		$row = $qry->result_array();
		}else
		if($search_by === "2"){
			if($expiry === "1"){
			 //Permit Exp
			  $qry = $this->db->query("SELECT *,vendor_name from sblt_vehicle inner join sblt_veh_type on sblt_veh_type.veh_type_id = sblt_vehicle.vehicle_type left join sblt_vendor on sblt_vendor.vendor_id = sblt_vehicle.owned_by where sblt_vehicle.status = '1' and permit_date between '$start_date' and '$end_date' ");
			}else
			if($expiry === "2"){
			 //Fc Exp
			  $qry = $this->db->query("SELECT *,vendor_name from sblt_vehicle inner join sblt_veh_type on sblt_veh_type.veh_type_id = sblt_vehicle.vehicle_type left join sblt_vendor on sblt_vendor.vendor_id = sblt_vehicle.owned_by where sblt_vehicle.status = '1' and fc_date between '$start_date' and '$end_date' ");
			}else
			if($expiry === "3"){
			 //Ins Exp
			  $qry = $this->db->query("SELECT *,vendor_name from sblt_vehicle inner join sblt_veh_type on sblt_veh_type.veh_type_id = sblt_vehicle.vehicle_type left join sblt_vendor on sblt_vendor.vendor_id = sblt_vehicle.owned_by where sblt_vehicle.status = '1' and insur_date between '$start_date' and '$end_date' ");
			}else
			if($expiry === "4"){
			 //Tax Exp
			  $qry = $this->db->query("SELECT *,vendor_name from sblt_doc inner join sblt_vehicle on sblt_vehicle.vehicle_id = sblt_doc.vehicle_id inner join sblt_veh_type on sblt_veh_type.veh_type_id = sblt_vehicle.vehicle_type left join sblt_vendor on sblt_vendor.vendor_id = sblt_vehicle.owned_by where sblt_doc.status = '1' and sblt_doc.doc_type = 'Tax'  and sblt_doc.doc_to between '$start_date' and '$end_date' order by sblt_doc.doc_to desc limit 0,1 ");
			}else
			if($expiry === "5"){
			 //TP Exp
			  $qry = $this->db->query("SELECT *,vendor_name from sblt_doc inner join sblt_vehicle on sblt_vehicle.vehicle_id = sblt_doc.vehicle_id inner join sblt_veh_type on sblt_veh_type.veh_type_id = sblt_vehicle.vehicle_type left join sblt_vendor on sblt_vendor.vendor_id = sblt_vehicle.owned_by where sblt_doc.status = '1' and sblt_doc.doc_type = 'TP' and sblt_doc.doc_to between '$start_date' and '$end_date' order by sblt_doc.doc_to desc limit 0,1 ");
			}
		
        $row = $qry->result_array();
		}
		return $row;
	}
}
?>