<?php
defined('BASEPATH') OR exit('No direct script access allowed');
class Report_Model extends CI_Model{
	function __construct()  
	{  
		// Call the Model constructor  
		parent::__construct();  
	}
	
	
	function WeeklyReport($vehicletype)
	{	
		$from = date('Y-m-d');
		$to=date("Y-m-d", strtotime("-7 day"));
		$sql = "SELECT log.id, log.tag_id, log.time_in, log.time_out, tbl_vehicle_master.vehicle_id, tbl_vehicle_master.tag_no, tbl_vehicle_master.tag_id, tbl_vehicle_master.alias_tag, tbl_vehicle_master.vehicle_number, tbl_vehicle_master.insurance_expiry_date, tbl_vehicle_master.pollution_expiry_date, tbl_vehicle_master.charges,  tbl_vehicle_master.route_no, tbl_vehicle_master.route_name, tbl_vehicle_master.zone, tbl_vehicle_master.fuel_type, tbl_vehicle_master.km_to_fro, tbl_vehicle_master.seating_capacity, tbl_vehicle_master.acutal_seating_capacity, tbl_vehicle_master.cab_type, tbl_vehicle_master.vender, tbl_vehicle_master.Vehicle_model, tbl_vehicle_master.registration_date, tbl_vehicle_master.fitness, tbl_vehicle_master.permit, tbl_vehicle_master.driver_name, tbl_vehicle_master.contact_number, tbl_vehicle_master.license_number, tbl_vehicle_master.status
		FROM log, tbl_vehicle_master
		WHERE log.tag_id = tbl_vehicle_master.tag_id
		AND tbl_vehicle_master.vehicle_type = '".$vehicletype."' 
		AND DATE_FORMAT( log.time_in, '%Y-%m-%d' ) >= '".$to."' ORDER BY log.time_in desc";
		$query = $this->db->query($sql);
		return $query->result();				
	}
	
	function WeeklyReport1($vehicletype)
	{
		$from = date('Y-m-d');
		$to=date("Y-m-d", strtotime("-7 day"));
		$sql = "SELECT log.id, log.tag_id, r.route_id, log.time_in, d.id,d.route_details, log.time_out, tbl_vehicle_master.vehicle_id, tbl_vehicle_master.tag_no, tbl_vehicle_master.tag_no, tbl_vehicle_master.tag_id,  tbl_vehicle_master.vehicle_number, tbl_vehicle_master.insurance_expiry_date, tbl_vehicle_master.pollution_expiry_date, tbl_vehicle_master.charges, r.route_no, tbl_vehicle_master.zone, tbl_vehicle_master.fuel_type, tbl_vehicle_master.km_to_fro, tbl_vehicle_master.seating_capacity, tbl_vehicle_master.acutal_seating_capacity, tbl_vehicle_master.cab_type, tbl_vehicle_master.vender, tbl_vehicle_master.Vehicle_model, tbl_vehicle_master.registration_date, tbl_vehicle_master.fitness, tbl_vehicle_master.permit, tbl_vehicle_master.driver_name, tbl_vehicle_master.contact_number, tbl_vehicle_master.license_number, tbl_vehicle_master.status
		FROM log, tbl_vehicle_master left join tbl_route_master as r on r.route_bus_id = tbl_vehicle_master.vehicle_id 
        left join tbl_tag_master as t on tbl_vehicle_master.vehicle_id=t.route_bus_id left join route_details as d on d.id = t.route_id
		WHERE t.date_of_entry in (SELECT MAX(date_of_entry) FROM tbl_tag_master)
		AND log.tag_id = tbl_vehicle_master.tag_id
		AND DATE_FORMAT( log.time_in, '%Y-%m-%d' ) >= '".$to."'group by(log.time_in) ORDER BY log.time_in desc";
		$query = $this->db->query($sql);
		return $query->result();				
	}
	
	
	
	
	
	
	 function CabDayReport($vehicletype)
	{
		$from = date("Y-m-d 02:00:00");
		$to = date("Y-m-d 02:00:00",strtotime(" +1 day"));
		$query = $this->db->query("SELECT log.id,t.tag_id as 'tagg',v.vehicle_id, log.tag_id, log.time_in, log.time_out,m.tag_id,v.vehicle_number, v.insurance_expiry_date, v.pollution_expiry_date, v.charges, v.zone, v.fuel_type, v.km_to_fro, v.seating_capacity, v.acutal_seating_capacity,v.cab_type, v.vender,v.Vehicle_model, v.registration_date,v.fitness, v.permit, v.driver_name, v.contact_number, v.license_number, v.status FROM tbl_tag_master_cab as t left join tbl_master as m on m.tag_no = t.tag_id left join log as log on log.tag_id = m.tag_id left join tbl_vehicle_master as v on v.vehicle_id = t.tag_cab_id 
		where DATE_FORMAT( log.time_in, '%Y-%m-%d %H:%i:%s' ) BETWEEN '".trim($from)."' and '".trim($to)."' group by (m.tag_id)");
		//echo $this->db->last_query(); die;
		return $query->result();				
	} 
	
	
	
	
	/* function BusDayReport($vehicletype)
	{
		$query = $this->db->query("SELECT log.id, log.tag_id, log.time_in, log.time_out, tbl_vehicle_master.vehicle_id, tbl_vehicle_master.tag_no, tbl_vehicle_master.tag_id, tbl_vehicle_master.alias_tag, tbl_vehicle_master.vehicle_number, tbl_vehicle_master.insurance_expiry_date, tbl_vehicle_master.pollution_expiry_date, tbl_vehicle_master.charges, tbl_vehicle_master.route_no, tbl_vehicle_master.route_name, tbl_vehicle_master.zone, tbl_vehicle_master.fuel_type, tbl_vehicle_master.km_to_fro, tbl_vehicle_master.seating_capacity, tbl_vehicle_master.acutal_seating_capacity, tbl_vehicle_master.cab_type, tbl_vehicle_master.vender, tbl_vehicle_master.Vehicle_model, tbl_vehicle_master.registration_date, tbl_vehicle_master.fitness, tbl_vehicle_master.permit, tbl_vehicle_master.driver_name, tbl_vehicle_master.contact_number, tbl_vehicle_master.license_number, tbl_vehicle_master.status
		FROM log, tbl_vehicle_master
		WHERE (log.tag_id = tbl_vehicle_master.tag_id or log.tag_id = tbl_vehicle_master.alias_tag)
		AND tbl_vehicle_master.vehicle_type = '".$vehicletype."'
		AND DATE(log.time_in) = CURDATE()");
		return $query->result();				
	} */
	/* function BusDayReport1($vehicletype)
	{
		$curdate = date('Y-m-d');
		$sql = "SELECT log.id, log.tag_id, r.route_id, log.time_in, d.id,d.route_details, log.time_out, tbl_vehicle_master.vehicle_id, tbl_vehicle_master.tag_no, tbl_vehicle_master.tag_no, tbl_vehicle_master.tag_id,  tbl_vehicle_master.vehicle_number, tbl_vehicle_master.insurance_expiry_date, tbl_vehicle_master.pollution_expiry_date, tbl_vehicle_master.charges, r.route_no, tbl_vehicle_master.zone, tbl_vehicle_master.fuel_type, tbl_vehicle_master.km_to_fro, tbl_vehicle_master.seating_capacity, tbl_vehicle_master.acutal_seating_capacity, tbl_vehicle_master.cab_type, tbl_vehicle_master.vender, tbl_vehicle_master.Vehicle_model, tbl_vehicle_master.registration_date, tbl_vehicle_master.fitness, tbl_vehicle_master.permit, tbl_vehicle_master.driver_name, tbl_vehicle_master.contact_number, tbl_vehicle_master.license_number, tbl_vehicle_master.status
		FROM log, tbl_vehicle_master left join tbl_route_master as r on r.route_bus_id = tbl_vehicle_master.vehicle_id 
        left join tbl_tag_master as t on tbl_vehicle_master.vehicle_id=t.route_bus_id left join route_details as d on d.id = r.route_no
		WHERE t.date_of_entry in (SELECT MAX(date_of_entry) FROM tbl_tag_master) AND tbl_vehicle_master.vehicle_type = '".$vehicletype."' AND log.time_in ='".$curdate."' GROUP BY r.route_bus_id";
		//echo $this->db->last_query(); die;
		$query = $this->db->query($sql);
		return $query->result();				
	} */
	
	function BusDayReport1($vehicletype)
	{
		$curdate = date('Y-m-d');
		$sql = "select m.tag_no,log.time_in,log.time_out,m.tag_id,rm.route_no,rm.route_bus_id,v.vehicle_number,rd.id, rd.route_details from log as log left join tbl_master as m on m.tag_id = log.tag_id left join tbl_tag_master t on t.route_tag_id = m.tag_id left join tbl_route_master as rm on rm.route_no = t.route_id left join tbl_vehicle_master as v on v.vehicle_id = rm.route_bus_id left join route_details as rd on rd.id = rm.route_no 
		where  DATE_FORMAT(log.time_in, '%Y-%m-%d') = '".$curdate."'AND v.vehicle_type='Bus' group by(log.time_out)" ;
		//echo $this->db->last_query(); die;
		$query = $this->db->query($sql);
		return $query->result();				
	}
	
	
	function CabReport($vehicletype)
	{    $date =  date('Y-m-21', strtotime('-1 months'));
	     $date1 =  date('Y-m-20');  
		$sql = "SELECT log.id,t.tag_id as 'tagg', log.tag_id,v.vehicle_id, log.time_in, log.time_out,m.tag_id,v.vehicle_number, v.insurance_expiry_date, v.pollution_expiry_date, v.charges, v.zone, v.fuel_type, v.km_to_fro, v.seating_capacity, v.acutal_seating_capacity,v.cab_type, v.vender,v.Vehicle_model, v.registration_date,v.fitness, v.permit, v.driver_name, v.contact_number, v.license_number, v.status FROM tbl_tag_master_cab as t left join tbl_master as m on m.tag_no = t.tag_id left join log as log on log.tag_id = m.tag_id left join tbl_vehicle_master as v on v.vehicle_id = t.tag_cab_id 
		Where log.time_in >= '".$date."' AND log.time_out <= '".$date1."' group by(m.tag_id)";
		$query = $this->db->query($sql);
		return $query->result();				
	}
	
	
	/* 
	function CabReport($vehicleid,$shift,$from,$to)
	{
		//echo $vehicleid;
		//echo $shift;
		//echo $from;
		//echo $to; 
		$sql = "SELECT * from tbl_vehicle_master where tag_no = '".$vehicleid."' AND vehicle_type = 'Cab' AND date >= '".$from."' AND date <= '".$to."' AND shift = '".$shift."'";
		$query = $this->db->query($sql);
		return $query->result();				
	} */
	
	function CabReport1()
	{
		 
		$sql = "SELECT * from tbl_vehicle_master where  vehicle_type = 'Cab' ";
		$query = $this->db->query($sql);
		return $query->result();				
	}
	
	
	
	
	
	function BusReport($vehicletype)
	{
		
		$date =  date('Y-m-21', strtotime('-1 months'));
	    $date1 =  date('Y-m-20'); 
		$sql = "select m.tag_no,log.time_in,v.vehicle_id,log.time_out,m.tag_id,rm.route_no,v.vender,rm.route_bus_id,v.vehicle_number,rd.id, rd.route_details from log as log left join tbl_master as m on m.tag_id = log.tag_id left join tbl_tag_master t on t.route_tag_id = m.tag_id left join tbl_route_master as rm on rm.route_no = t.route_id left join tbl_vehicle_master as v on v.vehicle_id = rm.route_bus_id left join route_details as rd on rd.id = rm.route_no 
		where v.vehicle_type = '".$vehicletype."'
		AND log.time_in >= '".$date."' AND log.time_in <= '".$date1."' GROUP BY t.route_bus_id";
		$query = $this->db->query($sql);
		return $query->result();				
	}
	
	function BusReportRoute($vehicleid)
	{
		$curdate = date('m-Y');
	
		$sql = "SELECT log.id, log.tag_id, log.time_in, log.time_out, tbl_vehicle_master.vehicle_id, tbl_vehicle_master.tag_no, tbl_vehicle_master.tag_id, tbl_vehicle_master.alias_tag, tbl_vehicle_master.vehicle_number, tbl_vehicle_master.insurance_expiry_date, tbl_vehicle_master.pollution_expiry_date, tbl_vehicle_master.charges, tbl_vehicle_master.route_no, tbl_vehicle_master.route_name, tbl_vehicle_master.zone, tbl_vehicle_master.fuel_type, tbl_vehicle_master.km_to_fro, tbl_vehicle_master.seating_capacity, tbl_vehicle_master.acutal_seating_capacity, tbl_vehicle_master.cab_type, tbl_vehicle_master.vender, tbl_vehicle_master.Vehicle_model, tbl_vehicle_master.registration_date, tbl_vehicle_master.fitness, tbl_vehicle_master.permit, tbl_vehicle_master.driver_name, tbl_vehicle_master.contact_number, tbl_vehicle_master.license_number, tbl_vehicle_master.status
		FROM log, tbl_vehicle_master
		WHERE (log.tag_id = tbl_vehicle_master.tag_id or log.tag_id = tbl_vehicle_master.alias_tag)
		AND tbl_vehicle_master.vehicle_type = 'Bus'
		AND DATE_FORMAT( log.time_in, '%m-%Y' ) = '".$curdate."'
		AND tbl_vehicle_master.vehicle_id = '".$vehicleid."' GROUP BY  log.tag_id";
		echo $sql;
		$query = $this->db->query($sql);
		return $query->result();				
	}
	
	
	function FilterBusReport($vehicletype)
	{
		$curdate = date('m-Y');
		$sql = "SELECT log.id, log.tag_id, log.time_in, log.time_out, tbl_vehicle_master.vehicle_id, tbl_vehicle_master.tag_no, tbl_vehicle_master.tag_no, tbl_vehicle_master.tag_id, tbl_vehicle_master.alias_tag, tbl_vehicle_master.vehicle_number, tbl_vehicle_master.insurance_expiry_date, tbl_vehicle_master.pollution_expiry_date, tbl_vehicle_master.charges, tbl_vehicle_master.route_no, tbl_vehicle_master.route_name, tbl_vehicle_master.zone, tbl_vehicle_master.fuel_type, tbl_vehicle_master.km_to_fro, tbl_vehicle_master.seating_capacity, tbl_vehicle_master.acutal_seating_capacity, tbl_vehicle_master.cab_type, tbl_vehicle_master.vender, tbl_vehicle_master.Vehicle_model, tbl_vehicle_master.registration_date, tbl_vehicle_master.fitness, tbl_vehicle_master.permit, tbl_vehicle_master.driver_name, tbl_vehicle_master.contact_number, tbl_vehicle_master.license_number, tbl_vehicle_master.status
		FROM log, tbl_vehicle_master WHERE (log.tag_id = tbl_vehicle_master.tag_id or log.tag_id = tbl_vehicle_master.alias_tag)";
		
		if(trim($this->input->post('vehicleid')) != "")
		{
			$sql .= " AND tbl_vehicle_master.tag_id= '".trim($this->input->post('vehicleid'))."'"; 
		}
		if((trim($this->input->post('from')) != "") && (trim($this->input->post('to')) != ""))
		{
			$sql .= " AND DATE_FORMAT( log.time_in, '%Y-%m-%d' ) BETWEEN '".trim($this->input->post('from'))."' and '".trim($this->input->post('to'))."'"; 
		}
		else
		{
			$sql .= " AND DATE_FORMAT( log.time_in, '%m-%Y' ) = '".$curdate."'";
		}
		$sql .= " AND tbl_vehicle_master.vehicle_type = '".$vehicletype."'";
		$query = $this->db->query($sql);
		return $query->result();				
	}
	
	
	
	
	
	function FilterCabReport($vehicletype)
	{
		$curdate = date('m-Y');
		 //echo $this->input->post('vehicleid');
		//echo $this->input->post('shift');
		$from =  $this->input->post('from');
		$to =  $this->input->post('to'); 
		$sql = "SELECT log.id,t.tag_id as 'tagg', s.shift,log.tag_id,v.vehicle_id, log.time_in, log.time_out,m.tag_id,v.vehicle_number, v.insurance_expiry_date, v.pollution_expiry_date, v.charges, v.zone, v.fuel_type, v.km_to_fro, v.seating_capacity, v.acutal_seating_capacity,v.cab_type, v.vender,v.Vehicle_model, v.registration_date,v.fitness, v.permit, v.driver_name, v.contact_number, v.license_number, v.status FROM tbl_tag_master_cab as t left join tbl_master as m on m.tag_no = t.tag_id left join log as log on log.tag_id = m.tag_id left join tbl_vehicle_master as v on v.vehicle_id = t.tag_cab_id left join tbl_cab_shift as s on s.tag_cab_id = v.vehicle_id where v.vehicle_type = '".$vehicletype."'";
		
		 if(trim($this->input->post('vehicleid')) != "")
		{
			$sql .= " AND t.tag_id = '".trim($this->input->post('vehicleid'))."'"; 
		}
		if(($from != "") && ($to != ""))
		{
			$sql .= " AND log.time_in >= '".$from."' AND log.time_in <= '".$to."'";  
			
		}
		if(trim($this->input->post('shift')) != "")
		{
			$sql .= " AND s.shift= '".trim($this->input->post('shift'))."'"; 
		}
		else
		{
			$sql .= " AND DATE_FORMAT(log.time_in, '%m-%Y') = '".$curdate."'";
		}
		$sql .= " AND v.vehicle_type = '".$vehicletype."' group by(t.tag_id)"; 
		
		$query = $this->db->query($sql);
		//echo $this->db->last_query(); die;
		return $query->result();				  
	}
	
	function ConsolidatedCabReport($vehicletype)
	{
		$current_day_this_month_from = date("Y-m-d 02:00:00");
		$current_day_this_month_to = date("Y-m-d 2:00:00",strtotime(" +1 day"));
		$sql = "SELECT log.id,t.tag_id as 'tagg',s.shift, MIN(log.time_in) as firstin, MAX(log.time_out) as lastout, MAX(log.time_in) as lastin, log.tag_id, log.time_in, log.time_out,m.tag_id,v.vehicle_number, v.insurance_expiry_date, v.pollution_expiry_date, v.charges, v.zone, v.fuel_type, v.km_to_fro, v.seating_capacity, v.acutal_seating_capacity,v.cab_type, v.vender,v.Vehicle_model, v.registration_date,v.fitness, v.permit, v.driver_name, v.contact_number, v.license_number, v.status FROM tbl_tag_master_cab as t left join tbl_master as m on m.tag_no = t.tag_id left join log as log on log.tag_id = m.tag_id left join tbl_vehicle_master as v on v.vehicle_id = t.tag_cab_id left join tbl_cab_shift  as s on s.tag_cab_id = v.vehicle_id";
		
		if(trim($this->input->post('vehicleid')) != "")
		{
			$sql .= " AND v.tagg= '".trim($this->input->post('vehicleid'))."'"; 
		}
		if((trim($this->input->post('from')) != "") && (trim($this->input->post('to')) != ""))
		{
			$f = $this->input->post('from');
			$t = $this->input->post('to');
			$tt = date("Y-m-d" ,strtotime($t." +1 day"));
			$from = date("$f 02:00:00");
			$to = date("$tt 02:00:00");
			$sql .= " AND DATE_FORMAT( log.time_in, '%Y-%m-%d %H:%i:%s' ) BETWEEN '".trim($from)."' and '".trim($to)."'"; 
		}
		if(trim($this->input->post('shift')) != "")
		{
			$sql .= " AND s.shift= '".trim($this->input->post('shift'))."'"; 
		}
		else
		{
			$sql .= " AND DATE_FORMAT( log.time_in, '%Y-%m-%d %H:%i:%s' ) BETWEEN '".$current_day_this_month_from."' and '".$current_day_this_month_to."'";
		} 
		$sql .= "group by (m.tag_id)";
		//echo $sql; die;
		$query = $this->db->query($sql);
		return $query->result();				
	}
	
	
	
	
	function FilterReport($vehicletype)
	{
		$curdate = date('m-Y');
		$sql = "select m.tag_no,log.time_in,log.time_out,m.tag_id,rm.route_no,v.vender,rm.route_bus_id,v.vehicle_number,rd.id, rd.route_details from log as log left join tbl_master as m on m.tag_id = log.tag_id left join tbl_tag_master t on t.route_tag_id = m.tag_id left join tbl_route_master as rm on rm.route_no = t.route_id left join tbl_vehicle_master as v on v.vehicle_id = rm.route_bus_id left join route_details as rd on rd.id = rm.route_no 
		where v.vehicle_type = '".$vehicletype."' ";
		
		if(trim($this->input->post('vehicleid')) != "")
		{
			$sql .= " AND m.tag_id= '".trim($this->input->post('vehicleid'))."'"; 
		}
		if((trim($this->input->post('from')) != "") && (trim($this->input->post('to')) != ""))
		{
			//check date from nd to for change of route in route table
			$sql .= " AND DATE_FORMAT( log.time_in, '%Y-%m-%d' ) BETWEEN '".trim($this->input->post('from'))."' and '".trim($this->input->post('to'))."'"; 
		}
		else
		{
			$sql .= " AND DATE_FORMAT( log.time_in, '%m-%Y' ) = '".$curdate."'";
		}
		$sql .= " AND v.vehicle_type = '".$vehicletype."' group by(log.time_out)";
		$query = $this->db->query($sql);
		return $query->result();				
	}
	
	/* function FilterReport($vehicletype)
	{
		$curdate = date('m-Y');
		$sql = "SELECT log.id, log.tag_id, r.route_id, log.time_in, d.id,d.route_details, log.time_out, tbl_vehicle_master.vehicle_id, tbl_vehicle_master.tag_no, tbl_vehicle_master.tag_no, tbl_vehicle_master.tag_id,  tbl_vehicle_master.vehicle_number, tbl_vehicle_master.insurance_expiry_date, tbl_vehicle_master.pollution_expiry_date, tbl_vehicle_master.charges, r.route_no, tbl_vehicle_master.zone, tbl_vehicle_master.fuel_type, tbl_vehicle_master.km_to_fro, tbl_vehicle_master.seating_capacity, tbl_vehicle_master.acutal_seating_capacity, tbl_vehicle_master.cab_type, tbl_vehicle_master.vender, tbl_vehicle_master.Vehicle_model, tbl_vehicle_master.registration_date, tbl_vehicle_master.fitness, tbl_vehicle_master.permit, tbl_vehicle_master.driver_name, tbl_vehicle_master.contact_number, tbl_vehicle_master.license_number, tbl_vehicle_master.status
		FROM log, tbl_vehicle_master left join tbl_route_master as r on r.route_bus_id = tbl_vehicle_master.vehicle_id 
        left join tbl_tag_master as t on tbl_vehicle_master.vehicle_id=t.route_bus_id left join route_details as d on d.id = r.route_no
		WHERE t.date_of_entry in (SELECT MAX(date_of_entry) FROM tbl_tag_master GROUP BY route_bus_id)

		AND log.tag_id = tbl_vehicle_master.tag_id";
		
		if(trim($this->input->post('vehicleid')) != "")
		{
			$sql .= " AND tbl_vehicle_master.tag_id= '".trim($this->input->post('vehicleid'))."'"; 
		}
		if((trim($this->input->post('from')) != "") && (trim($this->input->post('to')) != ""))
		{
			//check date from nd to for change of route in route table
			$sql .= " AND DATE_FORMAT( log.time_in, '%Y-%m-%d' ) BETWEEN '".trim($this->input->post('from'))."' and '".trim($this->input->post('to'))."'"; 
		}
		else
		{
			$sql .= " AND DATE_FORMAT( log.time_in, '%m-%Y' ) = '".$curdate."'";
		}
		$sql .= " AND tbl_vehicle_master.vehicle_type = '".$vehicletype."' GROUP BY log.tag_id";
		
		$query = $this->db->query($sql);
		return $query->result();				
	} */
	
	
	
	/* function FilterCabReportDay($vehicletype)
	{
		$vehicleid = $this->input->post('vehicleid');
		$shift = $this->input->post('shift');
		$from = $this->input->post('from');
		$to = $this->input->post('to');
		$sql = "SELECT * from tbl_vehicle_master where tag_no = '".$vehicleid."' AND vehicle_type = 'Cab' AND date >= '".$from."' AND date <= '".$to."' AND shift = '".$shift."'";
		$query = $this->db->query($sql);
		return $query->result();	
	} */
	
	function FilterCabReportDay($vehicletype)
	{   
	  /*  echo $this->input->post('vehicleid');
	   echo $this->input->post('shift');
	   echo $this->input->post('from');
	   echo $this->input->post('to');  */
		$curdate = date('m-Y');
		$sql = "SELECT log.id,t.tag_id as 'tagg',s.shift,v.vehicle_id, MIN(log.time_in) as firstin, MAX(log.time_out) as lastout, MAX(log.time_in) as lastin, log.tag_id, log.time_in, log.time_out,m.tag_id,v.vehicle_number, v.insurance_expiry_date, v.pollution_expiry_date, v.charges, v.zone, v.fuel_type, v.km_to_fro, v.seating_capacity, v.acutal_seating_capacity,v.cab_type, v.vender,v.Vehicle_model, v.registration_date,v.fitness, v.permit, v.driver_name, v.contact_number, v.license_number, v.status FROM tbl_tag_master_cab as t left join tbl_master as m on m.tag_no = t.tag_id left join log as log on log.tag_id = m.tag_id left join tbl_vehicle_master as v on v.vehicle_id = t.tag_cab_id  left join tbl_cab_shift as s on s.tag_cab_id = v.vehicle_id   where v.vehicle_type = '".$vehicletype."'";
		
		if(trim($this->input->post('vehicleid')) != "")
		{
			$sql .= " AND t.tag_id= '".trim($this->input->post('vehicleid'))."'"; 
		}
		if((trim($this->input->post('from')) != "") && (trim($this->input->post('to')) != ""))
		{
			if(trim($this->input->post('shift')) == "N"){
				$from = date("Y-m-d H:i:s",strtotime($this->input->post('from')." +18 hour"));
				$to = date("Y-m-d H:i:s",strtotime($this->input->post('to')." +34 hour"));
			} else {
				$from = date("Y-m-d H:i:s",strtotime($this->input->post('from')." +2 hour"));
				$to = date("Y-m-d H:i:s",strtotime($this->input->post('to')." +26 hour"));
			}
			$sql .= " AND DATE_FORMAT( log.time_in, '%Y-%m-%d %H:%i:%s' ) BETWEEN '".trim($from)."' and '".trim($to)."'"; 
		}
		if(trim($this->input->post('shift')) != "")
		{
			$sql .= " AND s.shift= '".trim($this->input->post('shift'))."'"; 
		}
		else
		{
			$sql .= " AND DATE_FORMAT( log.time_in, '%m-%Y' ) = '".$curdate."'";
		}
		$sql .= " AND v.vehicle_type = '".$vehicletype."' group by (m.tag_id)";
		
		//echo $this->db->last_query(); die;
		$query = $this->db->query($sql);
		return $query->result();	
	}
	
	
	
	
	
	function FilterCabReportDay1()
	{
		$sql = "SELECT * from tbl_vehicle_master where vehicle_type = 'Cab'";
		$query = $this->db->query($sql);
		return $query->result();	
	}
	
	
	
	/* function FilterCabReportDay($vehicletype)
	{
		$curdate = date('m-Y');
		$sql = "SELECT log.id, log.tag_id, log.time_in, log.time_out, tbl_vehicle_master.vehicle_id, tbl_vehicle_master.tag_no, tbl_vehicle_master.tag_no, tbl_vehicle_master.tag_id, tbl_vehicle_master.shift, tbl_vehicle_master.alias_tag, tbl_vehicle_master.vehicle_number, tbl_vehicle_master.insurance_expiry_date, tbl_vehicle_master.pollution_expiry_date, tbl_vehicle_master.charges, tbl_vehicle_master.route_no, tbl_vehicle_master.route_name, tbl_vehicle_master.zone, tbl_vehicle_master.fuel_type, tbl_vehicle_master.km_to_fro, tbl_vehicle_master.seating_capacity, tbl_vehicle_master.acutal_seating_capacity, tbl_vehicle_master.cab_type, tbl_vehicle_master.vender, tbl_vehicle_master.Vehicle_model, tbl_vehicle_master.registration_date, tbl_vehicle_master.fitness, tbl_vehicle_master.permit, tbl_vehicle_master.driver_name, tbl_vehicle_master.contact_number, tbl_vehicle_master.license_number, tbl_vehicle_master.status
		FROM log, tbl_vehicle_master WHERE (log.tag_id = tbl_vehicle_master.tag_id or log.tag_id = tbl_vehicle_master.alias_tag)";
		
		if(trim($this->input->post('vehicleid')) != "")
		{
			$sql .= " AND tbl_vehicle_master.tag_id= '".trim($this->input->post('vehicleid'))."'"; 
		}
		if((trim($this->input->post('from')) != "") && (trim($this->input->post('to')) != ""))
		{
			if(trim($this->input->post('shift')) == "N"){
				$from = date("Y-m-d H:i:s",strtotime($this->input->post('from')." +18 hour"));
				$to = date("Y-m-d H:i:s",strtotime($this->input->post('to')." +34 hour"));
			} else {
				$from = date("Y-m-d H:i:s",strtotime($this->input->post('from')." +2 hour"));
				$to = date("Y-m-d H:i:s",strtotime($this->input->post('to')." +26 hour"));
			}
			$sql .= " AND DATE_FORMAT( log.time_in, '%Y-%m-%d %H:%i:%s' ) BETWEEN '".trim($from)."' and '".trim($to)."'"; 
		}
		
		if(trim($this->input->post('shift')) != "")
		{
			$sql .= " AND tbl_vehicle_master.shift= '".trim($this->input->post('shift'))."'"; 
		}
		else
		{
			$sql .= " AND DATE_FORMAT( log.time_in, '%m-%Y' ) = '".$curdate."'";
		}
		$sql .= " AND tbl_vehicle_master.vehicle_type = '".$vehicletype."'";
		$query = $this->db->query($sql);
		return $query->result();	
	} */
	
	
	
	/* function VehicleBusReport($tagid)
	{
		$curdate = date('Y-m');
		$sql = "SELECT log.id, log.tag_id, log.time_in, log.time_out, tbl_vehicle_master.vehicle_id,t.route_id, tbl_vehicle_master.tag_no, tbl_vehicle_master.tag_id, tbl_vehicle_master.alias_tag, tbl_vehicle_master.vehicle_number, tbl_vehicle_master.insurance_expiry_date, tbl_vehicle_master.pollution_expiry_date, tbl_vehicle_master.charges, tbl_vehicle_master.route_no, tbl_vehicle_master.route_name, tbl_vehicle_master.zone, tbl_vehicle_master.fuel_type, tbl_vehicle_master.km_to_fro, tbl_vehicle_master.seating_capacity, tbl_vehicle_master.acutal_seating_capacity, tbl_vehicle_master.cab_type, tbl_vehicle_master.vender, tbl_vehicle_master.Vehicle_model, tbl_vehicle_master.registration_date, tbl_vehicle_master.fitness, tbl_vehicle_master.permit, tbl_vehicle_master.driver_name, tbl_vehicle_master.contact_number, tbl_vehicle_master.license_number, tbl_vehicle_master.status
		FROM log, tbl_vehicle_master left join tbl_tag_master as t on t.route_bus_id = tbl_vehicle_master.vehicle_id  WHERE log.tag_id = tbl_vehicle_master.tag_id
		AND tbl_vehicle_master.vehicle_type = 'Bus'
		AND (tbl_vehicle_master.tag_id = '".$tagid."' )
		AND DATE_FORMAT( log.time_in, '%Y-%m' ) = '".$curdate."'";
		$query = $this->db->query($sql);
		return $query->result();				
	} */
	
	
	function VehicleBusReport($tagid)
	{
		$count = $this->uri->segment(4); 
		//$count = $count-1;
		$sql = "select log.id, log.tag_id, log.time_in, log.time_out, t.route_id,r.id,r.route_details,v.vehicle_number,v.tag_no from tbl_tag_master as t left join tbl_route_master as rm on rm.route_bus_id = t.route_bus_id left join route_details as r on r.id = rm.route_no left join log as log on log.tag_id = t.route_tag_id left join tbl_vehicle_master as v on v.vehicle_id = t.route_bus_id   where rm.route_no = '".$tagid."' group by(log.time_in) order by (log.time_in) desc limit 0,$count";
		$query = $this->db->query($sql);
		return $query->result();				
	}
	
	
	
	
	
	
	
	
	function VehicleBusReportByDate()
	{
		$tagid = trim($this->input->post('tag_id'));
		$routee = $this->input->post('routee_id');
		$sql = "select log.id, log.tag_id, log.time_in, log.time_out, t.route_id,r.id,r.route_details,v.vehicle_number,v.tag_no from tbl_tag_master as t left join tbl_route_master as rm on rm.route_bus_id = t.route_bus_id left join route_details as r on r.id = rm.route_no left join log as log on log.tag_id = t.route_tag_id left join tbl_vehicle_master as v on v.vehicle_id = t.route_bus_id where t.route_id = '".$tagid."' AND DATE_FORMAT( log.time_in, '%Y-%m-%d' ) BETWEEN '".trim($this->input->post('from'))."' and '".trim($this->input->post('to'))."' group by(log.time_in)";
		$query = $this->db->query($sql);
		return $query->result();				
	}
	
	
	function VehicleCabReport($tagid)
	{
		$curdate = date('Y-m');
		$sql = "SELECT log.id, log.tag_id, log.time_in, log.time_out, tbl_vehicle_master.vehicle_id, tbl_vehicle_master.tag_no, tbl_vehicle_master.tag_id, tbl_vehicle_master.alias_tag, tbl_vehicle_master.vehicle_number, tbl_vehicle_master.insurance_expiry_date, tbl_vehicle_master.pollution_expiry_date, tbl_vehicle_master.charges, tbl_vehicle_master.route_no, tbl_vehicle_master.route_name, tbl_vehicle_master.zone, tbl_vehicle_master.fuel_type, tbl_vehicle_master.km_to_fro, tbl_vehicle_master.seating_capacity, tbl_vehicle_master.acutal_seating_capacity, tbl_vehicle_master.cab_type, tbl_vehicle_master.vender, tbl_vehicle_master.Vehicle_model, tbl_vehicle_master.registration_date, tbl_vehicle_master.fitness, tbl_vehicle_master.permit, tbl_vehicle_master.driver_name, tbl_vehicle_master.contact_number, tbl_vehicle_master.license_number, tbl_vehicle_master.status
		FROM log, tbl_vehicle_master WHERE log.tag_id = tbl_vehicle_master.tag_id
		AND tbl_vehicle_master.vehicle_type = 'Cab'
		AND (tbl_vehicle_master.tag_id = '".$tagid."' or tbl_vehicle_master.alias_tag = '".$tagid."')
		AND DATE_FORMAT( log.time_in, '%Y-%m' ) = '".$curdate."'";
		$query = $this->db->query($sql);
		return $query->result();				
	}
	function VehicleCabReportByDate()
	{
		$tagid = trim($this->input->post('tag_id'));
		$from = date("Y-m-d H:i:s",strtotime($this->input->post('from')." +2 hour"));
		$to = date("Y-m-d H:i:s",strtotime($this->input->post('to')." +26 hour"));
		
		$sql = "SELECT log.id, log.tag_id, log.time_in, log.time_out, tbl_vehicle_master.vehicle_id, tbl_vehicle_master.tag_no, tbl_vehicle_master.tag_id, tbl_vehicle_master.alias_tag, tbl_vehicle_master.vehicle_number, tbl_vehicle_master.insurance_expiry_date, tbl_vehicle_master.pollution_expiry_date, tbl_vehicle_master.charges, tbl_vehicle_master.route_no, tbl_vehicle_master.route_name, tbl_vehicle_master.zone, tbl_vehicle_master.fuel_type, tbl_vehicle_master.km_to_fro, tbl_vehicle_master.seating_capacity, tbl_vehicle_master.acutal_seating_capacity, tbl_vehicle_master.cab_type, tbl_vehicle_master.vender, tbl_vehicle_master.Vehicle_model, tbl_vehicle_master.registration_date, tbl_vehicle_master.fitness, tbl_vehicle_master.permit, tbl_vehicle_master.driver_name, tbl_vehicle_master.contact_number, tbl_vehicle_master.license_number, tbl_vehicle_master.status
		FROM log, tbl_vehicle_master WHERE tbl_vehicle_master.vehicle_type = 'Cab'
		AND (log.tag_id = tbl_vehicle_master.tag_id or log.tag_id = tbl_vehicle_master.alias_tag)
		AND DATE_FORMAT( log.time_in, '%Y-%m-%d %H:%i:%s' ) BETWEEN '".trim($from)."' and '".trim($to)."'
		AND tbl_vehicle_master.tag_id = '".$tagid."' or tbl_vehicle_master.alias_tag = '".$tagid."'";
		$query = $this->db->query($sql);
		return $query->result();				
	}
}