<?php
defined('BASEPATH') OR exit('No direct script access allowed');
class Vehicle_Model extends CI_Model{
	function __construct()  
	{  
		// Call the Model constructor  
		parent::__construct();  
	}
	
	/*  function vehiclelist($vehicletype)
	{
		$query = $this->db->query("select v.vehicle_id, d.route_details,v.tag_no, v.tag_id, v.vehicle_number, v.insurance_expiry_date, v.pollution_expiry_date, v.charges,r.route_no from  tbl_vehicle_master as v join tbl_route_master as r on r.route_bus_id = v.vehicle_id left join route_details as d on d.id = r.route_no   where r.date_of_entry = (select max(date_of_entry) from tbl_route_master)");
		return $query->result();				
	}  */
	
	
	
	 function vehiclelist($vehicletype)
	{
		$query = $this->db->query("select v.status, v.vehicle_id,v.tag_no, v.tag_id,d.route_details,v.vehicle_number, v.insurance_expiry_date, v.pollution_expiry_date, v.charges, r.route_no from tbl_route_master as r left join tbl_vehicle_master as v on v.vehicle_id=r.route_bus_id left join route_details as d on d.route_id = r.route_no where date_of_entry in (SELECT MAX(date_of_entry) FROM tbl_route_master GROUP BY route_bus_id)");
		return $query->result();				
	} 
	
	
	function vehiclelistcab()
	{
		$query = $this->db->query("select v.status,v.fitness,v.permit,v.Vehicle_model,v.vender, s.shift, v.vehicle_id, t.tag_id, v.vehicle_number, v.insurance_expiry_date, v.pollution_expiry_date, v.charges from tbl_tag_master_cab as t left join tbl_vehicle_master as v on v.vehicle_id=t.tag_cab_id left join tbl_cab_shift as s on v.vehicle_id = s.tag_cab_id where t.date_of_entry in (SELECT MAX(date_of_entry) FROM tbl_tag_master_cab GROUP BY tag_cab_id) &&  s.date_of_entry in (SELECT MAX(date_of_entry) FROM tbl_cab_shift GROUP BY tag_cab_id)");
		return $query->result();				
	}
	
	
	
	/* function vehiclelistcab()
	{
		$query = $this->db->query("select v.status,v.fitness,v.permit,v.Vehicle_model,v.vender, v.vehicle_id, t.tag_id, v.vehicle_number, v.insurance_expiry_date, v.pollution_expiry_date, v.charges from tbl_tag_master_cab as t left join tbl_vehicle_master as v on v.vehicle_id=t.tag_cab_id where t.date_of_entry in (SELECT MAX(date_of_entry) FROM tbl_tag_master_cab GROUP BY tag_cab_id)");
		return $query->result();				
	}  */
	
	
	
	/* function vehiclelistcab()
	{
		$query = $this->db->query("select * from tbl_vehicle_master where vehicle_type='Cab'");
		return $query->result();				
	}  */
	
	
	
	
	function vehiclelistcab1()
	{
		$query = $this->db->query("select vehicle_number,tag_no from tbl_vehicle_master where vehicle_type='Cab'");
		return $query->result();				
	} 
	
	
	function monthlyrepord($vehicletype)
	{ 
	 	
		$query = $this->db->query("select distinct(v.vehicle_id), (v.vehicle_number) from  tbl_vehicle_master as v join tbl_route_master as r on r.route_bus_id = v.vehicle_id where vehicle_type = '".$vehicletype."'");
		return $query->result();				
	}
	
	function monthlyresult()
	{
		$query = $this->db->query("select t.route_tag_id,r.route_no,v.vehicle_number,d.route_details, v.vehicle_id from tbl_vehicle_master as v left join tbl_route_master as r on r.route_bus_id = v.vehicle_id left join tbl_tag_master as t on r.route_id = t.route_id left join route_details as d on d.id = r.route_id");
		return $query->result();				
	}
	
	
	function monthlyresult1($vehicleid,$from,$to)
	{
		$query = $this->db->query("select log.time_in,log.time_out,t.route_tag_id,r.route_no,v.vehicle_number,d.route_details, v.vehicle_id from tbl_vehicle_master as v left join tbl_route_master as r on r.route_bus_id = v.vehicle_id left join tbl_tag_master as t on r.route_id = t.route_id left join route_details as d on d.id = r.route_id left join log as log on log.tag_id = r.route_tag_id where v.vehicle_id = '".$vehicleid."' AND (t.date_of_entry >= '".$from."' AND t.date_of_entry <= '".$to."')");
		//echo $this->db->last_query(); die;
		return $query->result();				
	}
	
    // for the 	bus route report
	/* function busroute1()
	{
		$query = $this->db->query("select log.time_in,log.time_out, t.route_tag_id,r.route_no,v.vehicle_number,d.route_details, v.vehicle_id,t.date_of_entry from tbl_vehicle_master as v left join tbl_route_master as r on r.route_bus_id = v.vehicle_id left join tbl_tag_master as t on r.route_id = t.route_id left join route_details as d on d.id = r.route_id right join log as log on log.tag_id = r.route_tag_id");
		return $query->result();				
	} */
	
	function busroute($from,$to)
	{
		$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 = 'Bus'";
		if(($from != "") && ($to != ""))
		{
			//check date from nd to for change of route in route table
			$sql .= " AND DATE_FORMAT( log.time_in, '%Y-%m-%d' ) BETWEEN '".$from."' and '".$to."'"; 
		}
		else
		{
			$sql .= " AND DATE_FORMAT( log.time_in, '%m-%Y' ) = '".$curdate."'";
		}
		$sql .= " 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 busroute1()
	{
		$date =  date('Y-m-21', strtotime('-1 months'));
	    $date1 =  date('Y-m-20'); 
		$query = $this->db->query("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 = 'Bus'
        AND DATE_FORMAT( log.time_in, '%Y-%m-%d' ) BETWEEN '".$date."' and '".$date1."'
		group by(log.time_out)");
		//echo $this->db->last_query(); die;
		return $query->result();				
	}
	
	
	
	/* function busroute($from,$to)
	{
		$query = $this->db->query("select log.time_in,log.time_out, t.route_tag_id,r.route_no,v.vehicle_number,d.route_details, v.vehicle_id,t.date_of_entry from tbl_vehicle_master as v left join tbl_route_master as r on r.route_bus_id = v.vehicle_id left join tbl_tag_master as t on r.route_id = t.route_id left join route_details as d on d.id = r.route_id right join log as log on log.tag_id = r.route_tag_id 
		
		where t.date_of_entry >= '".$from."' AND t.date_of_entry <= '".$to."'");
		return $query->result();				
	} */
	
	
	
	
	
	function routelist()
	{
		$query = $this->db->query("select * from route_details ");
		return $query->result();				
	}
	
	
	
	
	function AllVehicleList()
	{
		$query = $this->db->query("select vehicle_id, tag_no, tag_id, shift, vehicle_type, vehicle_number, insurance_expiry_date, pollution_expiry_date, charges, route_no, route_name, zone, fuel_type, km_to_fro, seating_capacity, acutal_seating_capacity, cab_type, vender, Vehicle_model, registration_date, fitness, permit, driver_name, contact_number, license_number, status from  tbl_vehicle_master");
		return $query->result();				
	}
	
	function LogEntryList()
	{
		$query = $this->db->query("select  log.id, log.tag_id, log.time_in, log.time_out, tbl_master.tag_no from log, tbl_master where log.tag_id = tbl_master.tag_id");
		return $query->result();				
	}
	function EditLogDetails($logid)
	{
		$query = $this->db->query("select  log.id, log.tag_id, log.time_in, log.time_out, tbl_master.tag_no from log, tbl_master where log.tag_id = tbl_master.tag_id and log.id='".$logid."'");
		
		
		return $query->row_array();				
	}
	function Logeedit($logid)
	{
		$logid = trim($this->input->post('logid'));	
		$TimeIn = trim($this->input->post('TimeIn'));
		$TimeOut = trim($this->input->post('TimeOut'));
		
		$tag_no = trim($this->input->post('tag_no'));
		$query = $this->db->query("select tag_no, tag_id from tbl_master where tag_no='".$tag_no."'");
		$row = $query->row_array();
		$tag_id =  $row['tag_id'];
		
		$query = $this->db->query("update log set tag_id='".$tag_id."', time_in='".$TimeIn."', time_out='".$TimeOut."' where id='".$logid."'");
		return $query;	
	}
	function LogAdd()
	{	
		$TimeIn = trim($this->input->post('TimeIn'));
		$TimeOut = trim($this->input->post('TimeOut'));
		
		$tag_no = trim($this->input->post('tag_no'));
		$query = $this->db->query("select tag_no, tag_id from tbl_master where tag_no='".$tag_no."'");
		$row = $query->row_array();
		$tag_id =  $row['tag_id'];
		
		$query = $this->db->query("insert into log (tag_id, time_in, time_out) values ('".$tag_id."','".$TimeIn."', '".$TimeOut."')");
		return $query;	
	}
	function DeleteLog($logid)
	{
		$sql = "delete from log where id='".$logid."'";
		$query = $this->db->query($sql);
		return $query;
	}
	
	function Billing()
	{
		$curdate = date('m-Y');
		//$curdate = date('11-2015');
		$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.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 = 'Bus' 
		AND DATE_FORMAT( log.time_in, '%m-%Y' ) = '".$curdate."' GROUP BY tbl_vehicle_master.vehicle_id";
		$query = $this->db->query($sql);
		return $query->result();								
	}
	
	function BillingReport()
	{
		$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.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";
		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'))."'"; 
		}
		$sql .= " AND tbl_vehicle_master.vehicle_type = 'Bus' GROUP BY tbl_vehicle_master.vehicle_id";
		$query = $this->db->query($sql);
		return $query->result();				
	}
	
	function vehicledetails($vehicleid)
	{  //echo $vehicleid; die;
		$query = $this->db->query("select v.*,r.*,r.route_id as 'route_idid',d.*,rd.*,v.status as 'vstatus'  from  tbl_vehicle_master as v left join tbl_route_master as r on v.vehicle_id = r.route_bus_id left join tbl_driver as d on v.vehicle_id = d.bus_id left join route_details as rd on rd.id = r.route_id where r.route_bus_id='".$vehicleid."' && r.date_of_entry = 
	    (select max(date_of_entry) from tbl_route_master where route_bus_id='".$vehicleid."')");
		return $query->row_array();				
	}

	function vehicledetailscab11($vehicleid)
	{  //echo $vehicleid; die;
		$query = $this->db->query("select *  from  tbl_vehicle_master  where vehicle_id='".$vehicleid."'");
		return $query->row_array();				
	}
	
	function vehicledetailscab($vehicleid)
	{
		$query = $this->db->query("select v.*,t.*,s.shift as 'shift1' from  tbl_vehicle_master as v left join tbl_tag_master_cab as t on v.vehicle_id = t.tag_cab_id left join tbl_cab_shift as s on v.vehicle_id = s.tag_cab_id  where t.tag_cab_id='".$vehicleid."' && s.tag_cab_id = '".$vehicleid."' && s.date_of_entry = (select max(date_of_entry) from tbl_cab_shift where tag_cab_id='".$vehicleid."') && t.date_of_entry = 
	    (select max(date_of_entry) from tbl_tag_master_cab where tag_cab_id='".$vehicleid."')");
		return $query->row_array();				
	}
	
	
	
	
	/* function vehicledetailscab($vehicleid)
	{
		$query = $this->db->query("select v.*,t.tag_id as 'tagidedit' from tbl_vehicle_master as v left join tbl_tag_master_cab as t on v.vehicle_id = t.tag_cab_id where t.date_of_entry = (select max(date_of_entry) from tbl_tag_master_cab where tag_cab_id = '".$vehicleid."'");                       
		return $query->row_array();				
	} */
	
	
	function routedetails()
	{
		$query = $this->db->query("select * from route_details where route_status = 'D'");
		return $query->result();					
	}
	
	
	function routedetails1()
	{
		$query = $this->db->query("select * from route_details where route_status = 'D'");
		return $query->result();					
	}
	
	function details($routid)
	{
		$query = $this->db->query("select * from tbl_route_master where date_of_entry = (select max(date_of_entry) from tbl_route_master where route_no = $routid)");
		return $query->row_array();				
	}
	
	function editroutedetails($routid)
	{
		$query = $this->db->query("select * from route_details where route_id = '".$routid."'");
		return $query->row_array();				
	}
	
	
	
	function vehicleedit($vehicleid)
	{   
		$sqlquery = $this->db->query("select tag_no, tag_id from tbl_vehicle_master where vehicle_id='".$vehicleid."'");
		$fatchrow = $sqlquery->row_array();
		$org_tag_id =  $fatchrow['tag_id'];
		$org_tag_no =  trim($fatchrow['tag_no']);
	
		//get the posted values
		$tag_no = trim($this->input->post('tag_no'));
		$query = $this->db->query("select tag_no, tag_id from tbl_master where tag_no='".$tag_no."'");
		$row = $query->row_array();
		$tag_id =  $row['tag_id'];
		
		if($org_tag_no != $tag_no)
		{
			date_default_timezone_set('Asia/Calcutta');
			$time = time();
			$time_stamp = date("Y-m-d H:i:s",$time);
			$sql = "insert into tbl_relationship (tag, tag_id, ntag, ntag_id, time_stamp) values('".$org_tag_no."', '".$org_tag_id."', '".$tag_no."', '".$tag_id."', '".$time_stamp."')";
			$insertquery = $this->db->query($sql);
		}
		
		$cab_type = $this->input->post('cab_type');
		$alias_tag = $this->input->post('alias_tag');
		$shift = $this->input->post('shift');
		$route_no = $this->input->post('route_no');
		$route_name = $this->input->post('route_name');
		$zone = $this->input->post('zone');
		$fuel_type = $this->input->post('fuel_type');
		$km_to_fro = $this->input->post('km_to_fro');
		$seating_capacity = $this->input->post('seating_capacity');
		$acutal_seating_capacity = $this->input->post('acutal_seating_capacity');
		$vender = $this->input->post('vender');
		$Vehicle_model = $this->input->post('Vehicle_model');
		
		$create_registration_date=date_create($this->input->post('registration_date'));
		$registration_date = date_format($create_registration_date,'Y-m-d');
		
		$fitness = $this->input->post('fitness');
		$permit = $this->input->post('permit');
		
		$vehicle_number = $this->input->post('vehicle_number');
		$create_insurance_expiry_date=date_create($this->input->post('insurance_expiry_date'));
		$insurance_expiry_date = date_format($create_insurance_expiry_date,'Y-m-d');
		$create_pollution_expiry_date=date_create($this->input->post('pollution_expiry_date'));
		$pollution_expiry_date = date_format($create_pollution_expiry_date,'Y-m-d');
		//echo $pollution_expiry_date; die;
		$charges = $this->input->post('charges');
		$remark = $this->input->post('remark');
		$driver_name = $this->input->post('driver_name');
		$contact_number = $this->input->post('contact_number');
		$emergency_contact_person = $this->input->post('emergency_contact_person');
		$emergency_number = $this->input->post('emergency_number');
		$relationship = $this->input->post('relationship');
		$license_number = $this->input->post('license_number');
		$address = $this->input->post('address');
		$city = $this->input->post('city');
		$state = $this->input->post('state');
		$pincode = $this->input->post('pincode');
		$country = 'India';
		$status = $this->input->post('status'); 
		
		$select_query = $this->db->query("select alias_tag from  tbl_vehicle_master where vehicle_id = '".$vehicleid."'");
		$resultrow = $select_query->row_array();
		$old_alias_tag = $resultrow['alias_tag'];
		$query1 = $this->db->query("select * from  tbl_master where tag_id = '".$old_alias_tag."'");
		if($query1->num_rows() == 1)
		{
			$fatchresult = $query1->row_array();
			$idss = $fatchresult['id'];
			$query11 = $this->db->query("update tbl_master set tag_id='".$alias_tag."' where id='".$idss."'");
		}
		else if($query1->num_rows() == 0){
			$sql1 = "insert into tbl_master (tag_no, tag_id) values('9999','".$alias_tag."')";
			$insertquery1 = $this->db->query($sql1);
		}
		
		/* $query = $this->db->query("update tbl_vehicle_master set tag_no='".$tag_no."', tag_id='".$tag_id."', alias_tag='".$alias_tag."', vehicle_number='".$vehicle_number."', insurance_expiry_date='".$insurance_expiry_date."', pollution_expiry_date='".$pollution_expiry_date."', charges='".$charges."', route_no='".$route_no."', route_name='".$route_name."', zone='".$zone."', fuel_type='".$fuel_type."', km_to_fro='".$km_to_fro."', seating_capacity='".$seating_capacity."', acutal_seating_capacity='".$acutal_seating_capacity."', cab_type='".$cab_type."', vender='".$vender."',  Vehicle_model='".$Vehicle_model."', registration_date='".$registration_date."', fitness='".$fitness."', permit='".$permit."', remark='".$remark."',driver_name='".$driver_name."', contact_number='".$contact_number."', emergency_contact_person='".$emergency_contact_person."', emergency_number='".$emergency_number."', relationship='".$relationship."', license_number='".$license_number."', address='".$address."', city='".$city."', state='".$state."', pincode='".$pincode."', country='".$country."', status='".$status."' where vehicle_id='".$vehicleid."'"); */
		
		$query1 = $this->db->query("update tbl_tag_master set v_status='".$status."' where route_bus_id='".$vehicleid."'");
		
		$query12 = $this->db->query("update tbl_route_master set status='".$status."' where route_bus_id='".$vehicleid."'");  
      	
		
       

			 
		$query = $this->db->query("update tbl_vehicle_master set  insurance_expiry_date='".$insurance_expiry_date."', pollution_expiry_date='".$pollution_expiry_date."', charges='".$charges."', seating_capacity='".$seating_capacity."', acutal_seating_capacity='".$acutal_seating_capacity."', cab_type='".$cab_type."', vender='".$vender."',  Vehicle_model='".$Vehicle_model."', registration_date='".$registration_date."', fitness='".$fitness."', permit='".$permit."', remark='".$remark."', status = '".$status."' where vehicle_id='".$vehicleid."'");
		//echo $sql = $this->db->last_query();	die;	
		
		if($status=='A')
	     {		
		    $query123 = $this->db->query("update route_details set route_status='A' where route_id='".$route_no ."'");  
	     }
        else
         {		
		    $query123 = $this->db->query("update route_details set route_status='D' where route_id='".$route_no ."'");  
	     }
	
		return $query;
	} 
	
	
	function vehicleeditcab($vehicleid)
	{  
		$sqlquery = $this->db->query("select tag_no, tag_id from tbl_vehicle_master where vehicle_id='".$vehicleid."'");
		$fatchrow = $sqlquery->row_array();
		$org_tag_id =  $fatchrow['tag_id'];
		$org_tag_no =  trim($fatchrow['tag_no']);
	
		//get the posted values
		$tag_no = trim($this->input->post('tag_no'));
		$query = $this->db->query("select tag_no, tag_id from tbl_master where tag_no='".$tag_no."'");
		$row = $query->row_array();
		$tag_id =  $row['tag_id'];
		
		if($org_tag_no != $tag_no)
		{
			date_default_timezone_set('Asia/Calcutta');
			$time = time();
			$time_stamp = date("Y-m-d H:i:s",$time);
			$sql = "insert into tbl_relationship (tag, tag_id, ntag, ntag_id, time_stamp) values('".$org_tag_no."', '".$org_tag_id."', '".$tag_no."', '".$tag_id."', '".$time_stamp."')";
			$insertquery = $this->db->query($sql);
		}
		
		$cab_type = $this->input->post('cab_type');
		$alias_tag = $this->input->post('alias_tag');
		$shift = $this->input->post('shift'); 
		$route_no = $this->input->post('route_no');
		$route_name = $this->input->post('route_name');
		$zone = $this->input->post('zone');
		$fuel_type = $this->input->post('fuel_type');
		$km_to_fro = $this->input->post('km_to_fro');
		$seating_capacity = $this->input->post('seating_capacity');
		$acutal_seating_capacity = $this->input->post('acutal_seating_capacity');
		$vender = $this->input->post('vender');
		$Vehicle_model = $this->input->post('Vehicle_model');
		
		$create_registration_date=date_create($this->input->post('registration_date'));
		$registration_date = date_format($create_registration_date,'Y-m-d');
		
		$fitness = $this->input->post('fitness');
		$permit = $this->input->post('permit');
		
		$vehicle_number = $this->input->post('vehicle_number');
		$create_insurance_expiry_date=date_create($this->input->post('insurance_expiry_date'));
		$insurance_expiry_date = date_format($create_insurance_expiry_date,'Y-m-d');
		$create_pollution_expiry_date=date_create($this->input->post('pollution_expiry_date'));
		$pollution_expiry_date = date_format($create_pollution_expiry_date,'Y-m-d');
		//echo $pollution_expiry_date; die;
		$charges = $this->input->post('charges');
		$remark = $this->input->post('remark');
		$driver_name = $this->input->post('driver_name');
		$contact_number = $this->input->post('contact_number');
		$emergency_contact_person = $this->input->post('emergency_contact_person');
		$emergency_number = $this->input->post('emergency_number');
		$relationship = $this->input->post('relationship');
		$license_number = $this->input->post('license_number');
		$address = $this->input->post('address');
		$city = $this->input->post('city');
		$state = $this->input->post('state');
		$pincode = $this->input->post('pincode');
		$country = 'India';
		$status = $this->input->post('status'); 
		
		$select_query = $this->db->query("select alias_tag from  tbl_vehicle_master where vehicle_id = '".$vehicleid."'");
		$resultrow = $select_query->row_array();
		$old_alias_tag = $resultrow['alias_tag'];
		$query1 = $this->db->query("select * from  tbl_master where tag_id = '".$old_alias_tag."'");
		if($query1->num_rows() == 1)
		{
			$fatchresult = $query1->row_array();
			$idss = $fatchresult['id'];
			$query11 = $this->db->query("update tbl_master set tag_id='".$alias_tag."' where id='".$idss."'");
		}
		else if($query1->num_rows() == 0){
			$sql1 = "insert into tbl_master (tag_no, tag_id) values('9999','".$alias_tag."')";
			$insertquery1 = $this->db->query($sql1);
		}
		
		 $query = $this->db->query("update tbl_vehicle_master set tag_no='".$tag_no."',  alias_tag='".$alias_tag."', vehicle_number='".$vehicle_number."', insurance_expiry_date='".$insurance_expiry_date."', pollution_expiry_date='".$pollution_expiry_date."', charges='".$charges."',  cab_type='".$cab_type."', vender='".$vender."', Vehicle_model='".$Vehicle_model."', registration_date='".$registration_date."', fitness='".$fitness."', permit='".$permit."', remark='".$remark."',driver_name='".$driver_name."', contact_number='".$contact_number."', emergency_contact_person='".$emergency_contact_person."', emergency_number='".$emergency_number."', relationship='".$relationship."', license_number='".$license_number."', address='".$address."', city='".$city."', state='".$state."', pincode='".$pincode."', country='".$country."', status='".$status."' where vehicle_id='".$vehicleid."' AND vehicle_type = 'Cab'"); 
		 //echo $this->db->last_query(); die;
		 return $query;
		 
	} 
	
	
	
	
	
// start of vehicle models entry	
	function AddVehicle($vehicletype)
	{
		
		$tag_no = $this->input->post('tag_no'); 
		$query = $this->db->query("select tag_no, tag_id from tbl_master where tag_no='".$tag_no."'");
		$row = $query->row_array();
		
		if ($query->num_rows() > 0)
		   {    
			$sqlquery = $this->db->query("select tag_no from tbl_vehicle_master where tag_no='".$tag_no."'");
			$sqlquery->row_array();
			//echo "hello"; die;
			if ($sqlquery->num_rows() > 0)
			 {
				$this->session->set_flashdata('flsh_msg1', 'Tag Id already in used');
				if($vehicletype == 'Cab')
				{
					redirect("Administrator/AddCab/");
				}
				else
				{
					redirect("Administrator/AddBus/");
				}
			 }
			else
			 {
				
				$tag_id =  $row['tag_id'];
				$vehicle_number = $this->input->post('vehicle_number');
				
				$create_insurance_expiry_date=date_create($this->input->post('insurance_expiry_date'));
				$insurance_expiry_date = date_format($create_insurance_expiry_date,'Y-m-d');
				
				$create_pollution_expiry_date=date_create($this->input->post('pollution_expiry_date'));
				$pollution_expiry_date = date_format($create_pollution_expiry_date,'Y-m-d');
				
				$charges = $this->input->post('charges');
				$remark = $this->input->post('remark');
				$driver_name = $this->input->post('driver_name');
				$contact_number = $this->input->post('contact_number');
				$emergency_contact_person = $this->input->post('emergency_contact_person');
				$emergency_number = $this->input->post('emergency_number');
				$relationship = $this->input->post('relationship');
				$license_number = $this->input->post('license_number');
				$address = $this->input->post('address');
				$city = $this->input->post('city');
				$state = $this->input->post('state');
				$pincode = $this->input->post('pincode');
				$country = 'India';
				$status = $this->input->post('status');
				$date=date("Y-m-d");
				
				if($vehicletype == 'Bus')
				{
					$route_no = $this->input->post('route_no');
					$route_name = $this->input->post('route_name');
					$zone = $this->input->post('zone');
					$fuel_type = $this->input->post('fuel_type');
					$km_to_fro = $this->input->post('km_to_fro');
					$seating_capacity = $this->input->post('seating_capacity');
					$acutal_seating_capacity = $this->input->post('acutal_seating_capacity');
					$vender = $this->input->post('vender');
					$Vehicle_model = $this->input->post('Vehicle_model');
					
					$create_registration_date=date_create($this->input->post('registration_date'));
					$registration_date = date_format($create_registration_date,'Y-m-d');
					
					$fitness = $this->input->post('fitness');
					$permit = $this->input->post('permit');
					
					//update route detail table
					if($status == 'A')
					{
					$sql_tag12 = "update route_details set route_status = 'A' where route_id = '".$route_no."'";
					$updatetag = $this->db->query($sql_tag12);
					}
					else
					{
					$sql_tag12 = "update route_details set route_status = 'D' where route_id = '".$route_no."'";
					$updatetag = $this->db->query($sql_tag12);	
					}
										
					// for bus details
				    $sql = "insert into tbl_vehicle_master (tag_no, tag_id, vehicle_type, vehicle_number, insurance_expiry_date, pollution_expiry_date, charges, seating_capacity, acutal_seating_capacity, vender, Vehicle_model, registration_date, fitness, permit, remark, date) values('".$tag_no."','".$tag_id."', '".$vehicletype."', '".$vehicle_number."', '".$insurance_expiry_date."', '".$pollution_expiry_date."','".$charges."','".$seating_capacity."','".$acutal_seating_capacity."','".$vender."','".$Vehicle_model."','".$registration_date."','".$fitness."','".$permit."','".$remark."','".$date."')";
					$insertquery = $this->db->query($sql);
					//return $insertquery ;
					$bus_id=$this->db->insert_id(); 
					// for route details
					$sql_route = "insert into tbl_route_master (route_bus_id,route_tag_id,route_no,route_zone,route_fuel_type,route_kms_to_fro,date_of_entry) values('".$bus_id."','".$tag_id."','".$route_no."','".$zone."', '".$fuel_type."', '".$km_to_fro."','".$date."')";
					$insertroute = $this->db->query($sql_route);
					$route11_id = $this->db->insert_id();
					
					$sql_tag = "insert into tbl_tag_master (route_bus_id,route_tag_id,route_id,date_of_entry) values('".$bus_id."','".$tag_id."','".$route_no."','".$date."')";
					$inserttag = $this->db->query($sql_tag);
				    //echo $sql = $this->db->last_query(); die;				
				     						
					// for driver details
					$sql_driver = "insert into tbl_driver (bus_id, route_id, driver_name, contact_number, emergency_contact_person, emergency_number, relationship, license_number, address, city, state, pincode, country, status, date) values('".$bus_id."','".$tag_no."','".$driver_name."','".$contact_number."','".$emergency_contact_person."','".$emergency_number."','".$relationship."','".$license_number."','".$address."','".$city."','".$state."','".$pincode."','".$country."','".$status."','".$date."')";
					$insertdriver = $this->db->query($sql_driver);
					return $insertdriver ;
					
				}
				
				
				
				
				else if($vehicletype == 'Cab'){
					$alias_tag = $this->input->post('alias_tag');
					$vehicle_id = $this->input->post('vehicle_id');
					$shift  = $this->input->post('shift');
					$cab_type = $this->input->post('cab_type');
					$vender = $this->input->post('vender');
					$Vehicle_model = $this->input->post('Vehicle_model');
					$create_registration_date=date_create($this->input->post('registration_date'));
					$registration_date = date_format($create_registration_date,'Y-m-d');
					$fitness = $this->input->post('fitness');
					$permit = $this->input->post('permit');
					if($alias_tag != ''){
						$sql1 = "insert into tbl_master (tag_no, tag_id) values('9999','".$alias_tag."')";
						$insertquery = $this->db->query($sql1);
					}
					
					
					
					
					$sql = "insert into tbl_vehicle_master (tag_no, tag_id, alias_tag, vehicle_type, shift, vehicle_number, insurance_expiry_date, pollution_expiry_date, charges, cab_type, vender, Vehicle_model, registration_date, fitness, permit,remark, driver_name, contact_number, emergency_contact_person, emergency_number, relationship, license_number, address, city, state, pincode, country, status, date) values('".$tag_no."','".$tag_id."','".$alias_tag."', '".$vehicletype."', '".$shift."', '".$vehicle_number."', '".$insurance_expiry_date."', '".$pollution_expiry_date."','".$charges."','".$cab_type."','".$vender."','".$Vehicle_model."','".$registration_date."','".$fitness."','".$permit."','".$remark."','".$driver_name."','".$contact_number."','".$emergency_contact_person."','".$emergency_number."','".$relationship."','".$license_number."','".$address."','".$city."','".$state."','".$pincode."','".$country."','".$status."','".$date."')";
					$insertquery = $this->db->query($sql);
					$insert_id=$this->db->insert_id();
					
					$sqlshift = "insert into tbl_cab_shift (tag_cab_id, shift, date_of_entry) values('".$insert_id."','".$shift."','".$date."')";
					$insertshift = $this->db->query($sqlshift);
					
					$sqltag = "insert into tbl_tag_master_cab (tag_cab_id, tag_id, date_of_entry) values('".$insert_id."','".$tag_no."','".$date."')";
					$inserttag = $this->db->query($sqltag);
		
		            $sqltag1 = "insert into tbl_tag_daynight (tag_cab_id, tag_no, shift, date_of_entry) values('".$insert_id."','".$tag_no."', '".$shift."', '".$date."')";
					$inserttag1 = $this->db->query($sqltag1);
					
					return $insertquery ;
				}	
			}		
		}
		else
		{
			$this->session->set_flashdata('flsh_msg1', 'Tag Id not in record');
			if($vehicletype == 'Cab'){
				redirect("Administrator/AddCab/");
			}
			else{
				redirect("Administrator/AddBus/");
			}
		}
	}
//end of the vehicle entry models	
	
	function CreateLog($update_by,$update_type,$update_tab,$update_id)
	{
		date_default_timezone_set('Asia/Calcutta');
		$update_time = date('Y-m-d H:i:s');
		$sql = "insert into tbl_updates_log (update_by, update_type, update_tab,  update_id, update_time) values('".$update_by."','".$update_type."', '".$update_tab."', '".$update_id."', '".$update_time."')";
		$query = $this->db->query($sql);
		return $query ;
	}
	function GetMaxId($idType, $table)
	{
		$sql = "SELECT max($idType) as maxid FROM $table";
		$query = $this->db->query($sql);
		$row = $query->row_array();
		$maxid = $row['maxid'];
		return $maxid;
	}
	function LogList()
	{
		$query = $this->db->query("SELECT * FROM tbl_updates_log order by update_time desc");
		return $query->result();
	}
	
	function BusRoundList()
	{
		$query = $this->db->query("SELECT * FROM tbl_monthly_round");
		return $query->result();
	}
	function BusRoundDetails($Id)
	{
		$query = $this->db->query("select * from  tbl_monthly_round where id='".$Id."'");
		return $query->row_array();				
	}
	function AddBusRound()
	{
		$year = $this->input->post('year');
		$month = $this->input->post('month');
		$monthly_round = $this->input->post('monthly_round');
		
		$sql = "insert into tbl_monthly_round (year, month, round) values('".$year."','".$month."', '".$monthly_round."')";
		$insertquery = $this->db->query($sql);
		return $insertquery;	
	}
	function EditBusRound($Id)
	{
		//$Id = $this->input->post('Id');
		$year = $this->input->post('year');
		$month = $this->input->post('month');
		$monthly_round = $this->input->post('monthly_round');
		$sql = "update tbl_monthly_round set year='".$year."', month='".$month."', round='".$monthly_round."' where id='".$Id."'";
		$query = $this->db->query($sql);
		return $query ;		
	}
	
	
	function PenaltyList()
	{
		$query = $this->db->query("SELECT * FROM tbl_penalty order by pdate desc");
		return $query->result();
	}
	function AddPenalty()
	{
		$vehicle_number = $this->input->post('vehicle_number');
		$query = $this->db->query("select vehicle_id, tag_id from  tbl_vehicle_master where vehicle_number='".$vehicle_number."'");
		$row = $query->row_array();
		$vehicle_id =  $row['vehicle_id'];
		$tag_id =  $row['tag_id'];
		
		
		$amount = $this->input->post('amount');
		$create_pdate=date_create($this->input->post('pdate'));
		$pdate = date_format($create_pdate,'Y-m-d');
		$reason = $this->input->post('reason');
		
		$sql = "insert into tbl_penalty (vehicle_id, tag_id, vehicle_number, reason, amount, pdate) values('".$vehicle_id."','".$tag_id."', '".$vehicle_number."', '".$reason."', '".$amount."', '".$pdate."')";
		$insertquery = $this->db->query($sql);
		return $insertquery;	
	}
	function PenaltyDetails($PenaltyId)
	{
		$query = $this->db->query("select * from  tbl_penalty where id='".$PenaltyId."'");
		return $query->row_array();				
	}
	function VehiclePenaltyDetails($VehicleId)
	{
		$query = $this->db->query("select * from  tbl_penalty where vehicle_id='".$VehicleId."'");
		return $query->result();				
	}
	function EditPenalty($PenaltyId)
	{
		$vehicle_number = $this->input->post('vehicle_number');
		
		$query = $this->db->query("select vehicle_id, tag_id from  tbl_vehicle_master where vehicle_number='".$vehicle_number."'");
		$row = $query->row_array();
		
		$vehicle_id =  $row['vehicle_id'];
		$tag_id =  $row['tag_id'];
		
		$amount = $this->input->post('amount');
		$create_pdate=date_create($this->input->post('pdate'));
		$pdate = date_format($create_pdate,'Y-m-d');
		$reason = $this->input->post('reason');
		$sql = "update tbl_penalty set vehicle_id='".$vehicle_id."', tag_id='".$tag_id."', vehicle_number='".$vehicle_number."', reason='".$reason."', amount='".$amount."', pdate='".$pdate."' where id='".$PenaltyId."'";
		$query = $this->db->query($sql);
		return $query ;		
	}
	function NotificationList()
	{
		$query = $this->db->query("select id, notification_for, day, notification_email from tbl_notification");
		return $query->result();				
	}
	function NotificationDetails($NotificationId)
	{
		$query = $this->db->query("select * from  tbl_notification where id='".$NotificationId."'");
		return $query->row_array();				
	}
	function AddNotification()
	{
		$notification_for = $this->input->post('notification_for');
		$day = $this->input->post('day');
		$notification_email = $this->input->post('notification_email');
		
		$sql = "insert into tbl_notification (notification_for, day, notification_email) values('".$notification_for."','".$day."', '".$notification_email."')";
		$insertquery = $this->db->query($sql);
		return $insertquery;	
	}
	function EditNotification($NotificationId)
	{
		$notification_for = $this->input->post('notification_for');
		$day = $this->input->post('day');
		$notification_email = $this->input->post('notification_email');
		
		$sql = "update tbl_notification set notification_for='".$notification_for."', day='".$day."', notification_email='".$notification_email."' where id='".$NotificationId."'";
		$query = $this->db->query($sql);
		return $query ;	
	}
	function AddRoute()
	{
        $vehicle_id = $this->input->post('vehicle_id');
		$route_no = $this->input->post('route_no');
   		$change_date = $this->input->post('change_date');
        $new_route_no = $this->input->post('new_route_no');
	    $tag_id1 = $this->input->post('tag_id1'); 
		$rout_name = $this->input->post('rout_name'); 
		$route_zone = $this->input->post('route_zone'); 
		$route_fuel_type = $this->input->post('route_fuel_type'); 
		$route_kms_to_fro = $this->input->post('route_kms_to_fro'); 
		
		
		$sql11 = "insert into tbl_tag_master (route_bus_id,route_tag_id, route_id, date_of_entry) values
		('".$vehicle_id."','".$tag_id1."','".$new_route_no."', '".$change_date."')";
		$this->db->query($sql11);
		//echo $this->db->last_query(); die;
		$sql = "insert into tbl_route_master (route_bus_id,route_tag_id,route_zone,route_fuel_type,route_kms_to_fro, route_no, date_of_entry) values('".$vehicle_id."','".$tag_id1."','".$route_zone."','".$route_fuel_type."','".$route_kms_to_fro."','".$new_route_no."', '".$change_date."')";
		$insertquery = $this->db->query($sql);
		
		$sqlupdate = "update route_details set route_status='D' where route_id='".$route_no."'";
		$query = $this->db->query($sqlupdate);
		$sqlupdate12 = "update route_details set route_status='A' where route_id='".$new_route_no."'";
		$query = $this->db->query($sqlupdate12);
		return $insertquery;	
	}
	
	function addroutedetails()
	{
		$route_no = $this->input->post('route_no');
		$rout_name = $this->input->post('route_details'); 
		$sql = "insert into route_details (route_id,route_details)
		values('".$route_no."','".$rout_name."')";
		$insertquery = $this->db->query($sql);
		return $insertquery;	
	}
	
	
	function editroutedetails1()
	{
		$route_no = $this->input->post('route_no');
		$rout_name = $this->input->post('route_name'); 
		$sql = "update route_details set route_details ='".$rout_name."' where route_id = '".$route_no."'";
		$insertquery = $this->db->query($sql);
		return $insertquery;	
	}
	
	
	function AddTag()
	{   
	   $routidd = $this->input->post('route1_id');
        $vehicle_id = $this->input->post('vehicle_id');
   		$change_date = $this->input->post('change_date');
        $new_tag_no = $this->input->post('new_tag_no');
	   
		
		$sql1 = "update tbl_route_master set route_tag_id='".$new_tag_no."' where route_id='".$routidd."'";
		$this->db->query($sql1);
		$sql = "insert into tbl_tag_master (route_bus_id,route_tag_id,route_id,date_of_entry) values('".$vehicle_id."','".$new_tag_no."','".$routidd."','".$change_date."')";
		$insertquery = $this->db->query($sql);
		
		return $insertquery;	
	}
	
	
	function AddTagcab()
	{   
	   //$routidd = $this->input->post('route1_id');
        $vehicle_id = $this->input->post('vehicle_id');
   		$change_date = $this->input->post('change_date');
        $new_tag_no = $this->input->post('new_tag_no');
		
		
		$sql = "insert into tbl_tag_master_cab (tag_cab_id,tag_id,date_of_entry) values('".$vehicle_id."','".$new_tag_no."','".$change_date."')";
		$insertquery = $this->db->query($sql);
		
	    $sqltag1 = "update tbl_tag_daynight set tag_no='".$new_tag_no."' where tag_cab_id = '".$vehicle_id."'";
		$insertquery12 = $this->db->query($sqltag1);
		
		
		return $insertquery;	
	}
	
	function AddTagshift()
	{   
	   //$routidd = $this->input->post('route1_id');
        $vehicle_id = $this->input->post('vehicle_id');
   		$change_date = $this->input->post('change_date');
        $new_tag_no = $this->input->post('new_tag_no');
		//$sql1 = "update tbl_route_master set route_tag_id='".$new_tag_no."' where route_id='".$routidd."'";
		//$this->db->query($sql1);
		$sql = "insert into tbl_cab_shift (tag_cab_id,shift,date_of_entry) values('".$vehicle_id."','".$new_tag_no."','".$change_date."')";
		$insertquery = $this->db->query($sql);
		
		$sqltag1 = "update tbl_tag_daynight set shift='".$new_tag_no."' where tag_cab_id = '".$vehicle_id."'";
		$insertquery12 = $this->db->query($sqltag1);
		
		return $insertquery;	
	}

}