<?php
class Driver_model extends CI_Model{
	
	public function search($search,$filters,$rows = 0,$limit_from= 0,$sort='',$order='asc'){		
		$start_date = $filters['start_date'];
		$end_date   = $filters['end_date'];
		if(!$sort){
			$sort = "from_date"; 
		}
		$emp_id   = $this->session->userdata('emp_id');
		$this->db->select('*,frm.city_name as frm_city_name, to.city_name as to_city_name');
		$this->db->from('operation_line');
		$this->db->join('operation', 'operation.operation_id = operation_line.operation_id');
		$this->db->join('city frm', 'frm.city_id = operation.orgin','left');
		$this->db->join('city to', 'to.city_id = operation.destination','left');
		$this->db->join('vehicle', 'vehicle.vehicle_id = operation_line.op_vehicle_id','left');
		
		$this->db->group_start();
			$this->db->where('DATE_FORMAT(from_date, "%Y-%m-%d") BETWEEN '. $this->db->escape($start_date).' AND '.$this->db->escape($end_date))->or_where('DATE_FORMAT(to_date, "%Y-%m-%d") BETWEEN '. $this->db->escape($start_date).' AND '.$this->db->escape($end_date));
		$this->db->group_end();
		$this->db->where("op_driver_id",$emp_id)->or_where("second_driver_id",$emp_id);
		$this->db->where("operation_sts !=",3);
		$this->db->order_by($sort,$order);
		if($rows>0){
			$this->db->limit($rows, $limit_from);
		}
		return $this->db->get();
	}
	public function get_found_rows($search,$filters){
		return $this->search($search, $filters)->num_rows();
	}
	public function view_data($operation_id){
		$this->db->select('*,frm.city_name as frm_city_name, to.city_name as to_city_name');
		$this->db->from('operation_line');
		$this->db->join('operation', 'operation.operation_id = operation_line.operation_id');
		$this->db->join('customers', 'customers.cust_id = operation.cust_id','left');
		$this->db->join('trip_type', 'trip_type.trip_type_id = operation.trip_type','left');
		$this->db->join('veh_type', 'veh_type.veh_type_id = operation.vehicle_type','left');
		$this->db->join('booking_type', 'booking_type.booking_type_id = operation.cust_type','left');
		$this->db->join('city frm', 'frm.city_id = operation.orgin','left');
		$this->db->join('city to', 'to.city_id = operation.destination','left');
		$this->db->join('state', 'state_code = customers.state','left');
		$this->db->where('operation_line.operation_id',$operation_id);
		return $this->db->get()->row();
	}
	
	public function update_trip($trip_data, $op_line_id,$start_op_id) {
		$booking_id = $this->get_booking_id($start_op_id);
		$this->db->where('booking_id', $booking_id);
		$this->db->update('booking', array('booking_sts'=>'5'));
		$this->db->where('booking_id', $booking_id);
		$this->db->update('operation', array('operation_sts'=>'5'));
		$this->db->where('booking_id', $booking_id);
		$this->db->update('accounts', array('operation_sts'=>'5','accounts_sts'=>'5'));
			
		$this->db->where('op_line_id', $op_line_id);		
		return $this->db->update('operation_line', $trip_data);
	}
	public function trip_end_info($op_line_id){
		$this->db->select('customers.customer_name,customers.phone_number,operation.tour_no,operation_line.trip_start_km,operation_line.trip_end_km');
		$this->db->from('operation_line');
		$this->db->join('operation', 'operation.operation_id = operation_line.operation_id');
		$this->db->join('customers', 'customers.cust_id = operation.cust_id','left');
		$this->db->where('operation_line.op_line_id',$op_line_id);
		return $this->db->get()->row();
	}
	public function get_booking_id($start_op_id){
		$this->db->from('operation');
		$this->db->where('operation_id',$start_op_id);
		$this->db->where('operation.status',1);
		$booking_info =  $this->db->get()->row();
		$booking_id = $booking_info->booking_id;
		return $booking_id;
	}
	public function get_suspence($suspence_id){
		$this->db->from('suspence');
		$this->db->where('suspence_id',$suspence_id);
		$this->db->where('suspence.status',1);
		return $this->db->get()->result();
	}
	public function save_suspence($suspence_data, $suspence_id){
		if($suspence_id === ""){
			$this->db->insert('suspence', $suspence_data);
			return $operation_data['suspence_id'] = $this->db->insert_id();
		}else{
			$this->db->where('suspence_id', $suspence_id);
			return $this->db->update('suspence', $suspence_data);
		}
	}	
	public function suspence_list($suspence_op_id,$suspence_vch_id){
		$this->db->from('suspence');
		$this->db->join('vehicle', 'vehicle.vehicle_id = suspence.suspence_vch_id','left');
		$this->db->where('suspence_op_id',$suspence_op_id);
		$this->db->where('suspence_vch_id',$suspence_vch_id);
		$this->db->where('suspence.status',1);
		return $this->db->get()->result();
	}
	public function get_suspence_amount($suspence_op_id,$suspence_vch_id) {
		$this->db->select('IFNULL(sum(suspence_amt), 0) as suspence_amount');
		$this->db->from('suspence');
		$this->db->where('suspence_op_id', $suspence_op_id);
		$this->db->where('suspence_vch_id',$suspence_vch_id);
		return $this->db->get()->row();
	}
	
	public function save_diesel($diesel_data, $diesel_id){
		if($diesel_id === ""){
			$this->db->insert('diesel', $diesel_data);
			return $operation_data['diesel_id'] = $this->db->insert_id();
		}else{
			$this->db->where('diesel_id', $diesel_id);
			return $this->db->update('diesel', $diesel_data);
		}
	}
	public function maintenance_exists($maintenance_op_id){
		$this->db->from('maintenance');
		$this->db->where('op_id', $maintenance_op_id);
		return $this->db->get()->num_rows();
	}
	public function save_maintenance($maintenance_data,$maintenance_op_id){
		$count = $this->maintenance_exists($maintenance_op_id);
		if($count === 0){
			$this->db->insert('maintenance', $maintenance_data);
			return $operation_data['maintenance_data'] = $this->db->insert_id();
		}else{
			$this->db->where('op_id', $maintenance_op_id);
			return $this->db->update('maintenance', $maintenance_data);
		}
	}
	public function get_maintenance($maintenance_id){
		$this->db->from('maintenance');
		$this->db->where('maintenance_id',$maintenance_id);
		$this->db->where('maintenance.status',1);
		return $this->db->get()->result();
	}
	public function maintenance_list($maintenance_op_id,$maintenance_vch_id){
		$this->db->from('maintenance');
		$this->db->join('vehicle', 'vehicle.vehicle_id = maintenance.vehicle_id','left');
		$this->db->where('op_id',$maintenance_op_id);
		$this->db->where('maintenance.vehicle_id',$maintenance_vch_id);
		$this->db->where('maintenance.status',1);
		return $this->db->get()->result();
	}
	public function get_diesel($diesel_id){
		$this->db->from('diesel');
		$this->db->where('diesel_id',$diesel_id);
		$this->db->where('diesel.status',1);
		return $this->db->get()->result();
	}
	public function diesel_list($diesel_op_id,$diesel_vch_id){
		$this->db->from('diesel');
		$this->db->join('vehicle', 'vehicle.vehicle_id = diesel.diesel_vch_id','left');
		$this->db->where('diesel_op_id',$diesel_op_id);
		$this->db->where('diesel_vch_id',$diesel_vch_id);
		$this->db->where('diesel.status',1);
		return $this->db->get()->result();
	}	
	public function get_diesel_amount($diesel_op_id,$diesel_vch_id) {
		$this->db->select('IFNULL(sum(diesel_amt), 0) as diesel_amount');
		$this->db->from('diesel');
		$this->db->where('diesel_op_id', $diesel_op_id);
		$this->db->where('diesel_vch_id', $diesel_vch_id);
		return $this->db->get()->row();
	}	
	public function get_start_km($operation_id,$vch_id) {
		$this->db->select('IFNULL(end_km, 0) as end_km');
		$this->db->from('diesel');
		$this->db->where('diesel_op_id', $operation_id);
		$this->db->where('diesel_vch_id', $vch_id);
		$this->db->order_by('end_km','desc');
		$start_info = $this->db->get()->row();
		//echo $this->db->last_query();
		$start_km = $start_info->end_km;
		if(!$start_km){
			$this->db->from('operation_line');
			$this->db->where('operation_line.operation_id',$operation_id);
			$this->db->where('operation_line.op_vehicle_id',$vch_id);
			$start_info = $this->db->get()->row();
			$start_km = $start_info->trip_start_km;
		}
		return $start_km;
	}	
	public function save_feed_back($feed_data) {
		$feed_op_id      = $feed_data['feed_op_id'];
		$feed_op_line_id = $feed_data['feed_op_line_id'];
		$count = $this->isfeed_exist($feed_op_id,$feed_op_line_id);
		if((int)$count > 0){
			$this->db->where('feed_op_id', $feed_op_id);
			$this->db->where('feed_op_line_id', $feed_op_line_id);
			return $this->db->update('feed_back', $feed_data);
		}else{
			return $this->db->insert('feed_back', $feed_data);
		}
	}
	public function isfeed_exist($feed_op_id,$feed_op_line_id){
		$this->db->from('feed_back');
		$this->db->where('feed_op_id',$feed_op_id);
		$this->db->where('feed_op_line_id',$feed_op_line_id);
		return $this->db->get()->num_rows();
	}
	
}
?>