<?php
class Online_fb_model extends CI_Model{
	
	//find vehicle no based on date filter
	public function get_vehicle_no($start_date,$end_date){
		$this->db->select('op_vehicle_id,vehicle_id,vehicle_no');
		$this->db->from('operation_line');
		$this->db->join('operation', 'operation.operation_id = operation_line.operation_id');
		$this->db->join('vehicle', 'vehicle.vehicle_id = operation_line.op_vehicle_id');
		$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('operation.status', 1);//active operation status
	    $this->db->where('operation_line.status', 1);//active operation line status
	    $this->db->order_by('vehicle.vehicle_no', 'asc');
	    $this->db->group_by('operation_line.op_vehicle_id');
		$this->db->where('operation.operation_sts', 4);//trip completed
	    return $this->db->get()->result_array();
	   //echo $this->db->last_query();die();
	}
	
	//find tour no based on vehicle no filter
	public function get_tour_no($start_date,$end_date,$vehicle_no) {
	   $this->db->select('tour_no');
	   $this->db->from('operation_line');
	   $this->db->join('operation', 'operation.operation_id = operation_line.operation_id');
	   $this->db->where_in('operation_line.op_vehicle_id', $vehicle_no);
	   $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('operation.operation_sts', 4);//trip completed
	   $this->db->where('operation.status', 1);//active operation status
	   $this->db->where('operation_line.status', 1);//active operation line status
	   $this->db->order_by('tour_no', 'asc');
	   return $this->db->get()->result();
	}
	//online tour feedback report generation
	public function search($start_date,$end_date,$vehicle_no,$tour_no){
		$start_date = $this->db->escape($start_date);
		$end_date   = $this->db->escape($end_date);
		
	    $this->db->select('trip_feedback.tour_no,vehicle_no,name, mobile,from_date,to_date, vch_quality,vch_media,vch_seat,driving_skill,driver_behaviour,sightseeing,remarks');
		$this->db->from('trip_feedback');
		$this->db->join('vehicle', 'vehicle.vehicle_id = trip_feedback.vehicle_id');
		$this->db->join('operation', 'operation.tour_no = trip_feedback.tour_no');
		if($vehicle_no){
			$this->db->where_in('vehicle.vehicle_id', $vehicle_no);
		}
		if($tour_no){
			$this->db->where_in('operation.operation_id', $tour_no);
		}
		$this->db->where("DATE_FORMAT(sblt_trip_feedback.created_date, '%Y-%m-%d') BETWEEN $start_date AND $end_date");	
		return $this->db->get()->result_array();
	}
	
}
?>