<?php
class Analaysis_report_model extends CI_Model{
	
	public function get_booking_type() {
	   $this->db->from('booking_type');
	   $this->db->where('status',1);
	   $this->db->order_by('booking_type_name', 'asc');
	   return $this->db->get()->result();
	}
	
	public function get_trip_type() {
	   $this->db->from('trip_type');
	   $this->db->where('status', 1);
	   $this->db->order_by('trip_type_name', 'asc');
	   return $this->db->get()->result();
	}
	
	public function get_vehicle_type() {
		$this->db->select('veh_type_id,veh_type,veh_category.veh_category');
	   $this->db->from('veh_type');
	   $this->db->join('veh_category', 'veh_category.veh_cat_id = veh_type.veh_category','left');
	   $this->db->where('veh_type.status', 1);
	   $this->db->order_by('veh_type', 'asc');
	   return $this->db->get()->result();
	}
	
	public function get_referral() {
		$this->db->from('employees');
		$this->db->join('people', 'people.person_id = employees.person_id');
		$this->db->join('role', 'employees.role = role.role_id');
	   return $this->db->get()->result();
	}
	public function get_vehicle_no($vehicle_type) {
		$this->db->select("vehicle_id,vehicle_no");
		$this->db->from('vehicle');
		$this->db->where_in('vehicle.vehicle_type', $vehicle_type);
	    $this->db->order_by('vehicle_id', 'asc');
	   return $this->db->get()->result();
	}
	public function get_source($search){
		$this->db->from('city');	
		$this->db->where('status', 1);
		$this->db->group_start();
			$this->db->like('city_name', $search);
		$this->db->group_end();
		$this->db->order_by('city_name', 'asc');
		$suggestions = array();
		foreach($this->db->get()->result() as $row){
			$suggestions[] = array('label' => "$row->city_name", 'city_id' => $row->city_id);
		}
		return $suggestions;
	}
	
	public function search($start_date,$end_date,$table_name,$lead_sts,$booking_type,$trip_type,$vehicle_type,$vehicle_no,$referral_by,$referral_type,$orgin,$destination){
		$start_date = $this->db->escape($start_date);
		$end_date   = $this->db->escape($end_date);
		$dbprefix   = $this->db->dbprefix($table_name);
		$sts_info   = array('enquiry'=>'enquiry_sts','booking'=>'booking_sts','operation'=>'operation_sts','accounts'=>'accounts_sts');
		$sts 		= $sts_info[$table_name];
		if($dbprefix === "sblt_operation"){
			$dbprefix = "sblt_booking";
		}
		if($dbprefix === "sblt_accounts"){
			$dbprefix = "sblt_booking";
		}
		$select = "*,frm.city_name as frm_city_name, to.city_name as to_city_name";
		if($table_name === "enquiry"){
			$select = "customers.customer_name,customers.phone_number,customers.referral_type,$table_name.from_date,$table_name.to_date,frm.city_name as frm_city_name, to.city_name as to_city_name,$table_name.bus_count,$table_name.pax,$table_name.booking_amount,$table_name.trip_days,trip_type.trip_type_name,booking_type.booking_type_name,veh_type.veh_type,$table_name.$sts,$table_name.created_date,$table_name.referral,people.first_name as referred_by,$table_name.trip_details,$table_name.remark,$table_name.cancel_reson,$table_name.avg_km,$table_name.prospect,$table_name.billing_name,$table_name.report_address,$table_name.landmark,ncd";
		}else
		if($table_name === "booking"){
			$select = "$table_name.booking_id,$table_name.enquiry_id,customers.customer_name,customers.phone_number,customers.referral_type,$table_name.tour_no,$table_name.from_date,$table_name.to_date,frm.city_name as frm_city_name, to.city_name as to_city_name,$table_name.bus_count,$table_name.pax,$table_name.trip_days,trip_type.trip_type_name,booking_type.booking_type_name,veh_type.veh_type,$table_name.$sts,$table_name.avg_km,$table_name.trip_details,$table_name.remark,$table_name.billing_name,$table_name.report_address,$table_name.landmark,$table_name.preferred_vehicle,$table_name.reporting_time,$table_name.shed_out_time,IFNULL($dbprefix.booking_amount,0) as book_amount,IFNULL($dbprefix.catering_amount,0) as cat_amt,IFNULL($dbprefix.stay_amount,0) as stay_amt,IFNULL($dbprefix.advance_amount,0) as adv_amt,IFNULL($dbprefix.discount_amount,0) as dis_amt,IFNULL(sum(sblt_balance_log.amount),0) as paid_tot,IFNULL($dbprefix.balance_amount,0) as bal_amt,$table_name.tax_precentage,$table_name.payment_type,$table_name.toll,$table_name.parking,$table_name.driver_batta,$table_name.state_tax,$table_name.catering,$table_name.cancel_reson,$table_name.operation_type,$table_name.tp_info,referral,people.first_name as referred_by,$table_name.created_date";
		}else
		if($table_name === "operation"){
			$select = "$table_name.booking_id,$table_name.enquiry_id,customers.customer_name,customers.phone_number,customers.referral_type,$table_name.tour_no,$table_name.from_date,$table_name.to_date,frm.city_name as frm_city_name, to.city_name as to_city_name,$table_name.bus_count,$table_name.pax,$table_name.trip_days,GROUP_CONCAT(DISTINCT vehicle_no SEPARATOR ',') as vehicle_no,GROUP_CONCAT(DISTINCT op_driver_name SEPARATOR ',') as op_driver_name,GROUP_CONCAT(DISTINCT op_extra_km SEPARATOR ',') as op_extra_km,GROUP_CONCAT(DISTINCT op_driver_number SEPARATOR ',') as op_driver_number,vehicle.vehicle_no,trip_type.trip_type_name,booking_type.booking_type_name,veh_type.veh_type,$table_name.$sts,$table_name.avg_km,$table_name.trip_details,$table_name.remark,$table_name.billing_name,$table_name.report_address,$table_name.landmark,$table_name.preferred_vehicle,$table_name.reporting_time,$table_name.shed_out_time,IFNULL($dbprefix.booking_amount,0) as book_amount,IFNULL($dbprefix.catering_amount,0) as cat_amt,IFNULL($dbprefix.stay_amount,0) as stay_amt,IFNULL($dbprefix.advance_amount,0) as adv_amt,IFNULL($dbprefix.discount_amount,0) as dis_amt,IFNULL(sum(sblt_balance_log.amount),0) as paid_tot,IFNULL(sum(sblt_suspence.suspence_amt),0) as suspence_amt,IFNULL(sum(sblt_driver_suspence.driver_suspence_amount),0) as driver_suspence_amount,IFNULL(sum(sblt_operation_line.op_suspence_amt),0) as op_suspence_amt,IFNULL($dbprefix.balance_amount,0) as bal_amt,$table_name.tax_precentage,$table_name.payment_type,$table_name.toll,$table_name.parking,$table_name.driver_batta,$table_name.state_tax,$table_name.catering,$table_name.operation_type,$table_name.tp_info,$table_name.referral,people.first_name as referred_by,GROUP_CONCAT(DISTINCT trip_start_km SEPARATOR ',') as trip_start_km,GROUP_CONCAT(DISTINCT trip_end_km SEPARATOR ',') as trip_end_km,op_extra_pre_km,op_extra_amt,diesel_amt,vendor_name,$table_name.created_date";
		}else
		if($table_name === "accounts"){
			$select = "$table_name.booking_id,$table_name.enquiry_id,customers.customer_name,customers.phone_number,customers.referral_type,$table_name.tour_no,$table_name.from_date,$table_name.to_date,frm.city_name as frm_city_name, to.city_name as to_city_name,$table_name.bus_count,$table_name.pax,$table_name.trip_days,GROUP_CONCAT(sblt_vehicle.vehicle_no SEPARATOR ',') as vehicle_no,GROUP_CONCAT(DISTINCT op_driver_name SEPARATOR ',') as op_driver_name,(trip_end_km - trip_start_km) as tot_km,GROUP_CONCAT(DISTINCT op_driver_number SEPARATOR ',') as op_driver_number,vehicle.vehicle_no,trip_type.trip_type_name,booking_type.booking_type_name,GROUP_CONCAT(DISTINCT sblt_veh_type.veh_type SEPARATOR ',') as veh_type,$table_name.$sts,$table_name.avg_km,$table_name.trip_details,$table_name.remark,$table_name.billing_name,$table_name.report_address,$table_name.landmark,$table_name.preferred_vehicle,$table_name.reporting_time,$table_name.shed_out_time,IFNULL($dbprefix.booking_amount,0) as book_amount,IFNULL($dbprefix.catering_amount,0) as cat_amt,IFNULL($dbprefix.stay_amount,0) as stay_amt,IFNULL($dbprefix.advance_amount,0) as adv_amt,IFNULL($dbprefix.discount_amount,0) as dis_amt,IFNULL(sum(sblt_balance_log.amount),0) as paid_tot,IFNULL(sum(sblt_suspence.suspence_amt),0) as suspence_amt,IFNULL(sum(sblt_driver_suspence.driver_suspence_amount),0) as driver_suspence_amount,IFNULL(sum(sblt_operation_line.op_suspence_amt),0) as op_suspence_amt,IFNULL($dbprefix.balance_amount,0) as bal_amt,$table_name.tax_precentage,$table_name.payment_type,$table_name.toll,$table_name.parking,$table_name.driver_batta,$table_name.state_tax,$table_name.catering,$table_name.operation_type,$table_name.tp_info,$table_name.referral,people.first_name as referred_by,GROUP_CONCAT(DISTINCT trip_start_km SEPARATOR ',') as trip_start_km,GROUP_CONCAT(DISTINCT trip_end_km SEPARATOR ',') as trip_end_km,op_extra_pre_km,op_extra_amt,diesel_amt,vendor_name,$table_name.created_date";
		}
		
		$this->db->select($select);
		$this->db->from($table_name);
		if($table_name === "booking"){
			$this->db->join("veh_type", "veh_type.veh_type_id = $table_name.vehicle_type","left");
		}
		if($table_name === "operation"){
			$this->db->join("operation_line", "operation_line.operation_id = $table_name.operation_id","left");
			$this->db->join("booking", "booking.booking_id = $table_name.booking_id","left");
			$this->db->join("vehicle", "vehicle.vehicle_id = operation_line.op_vehicle_id","left");	
			$this->db->join("veh_type", "veh_type.veh_type_id = operation_line.op_vehicle_type","left");	
			$this->db->join("diesel", "diesel.diesel_op_id = $table_name.operation_id","left");
			$this->db->join("vendor", "vendor.vendor_id = vehicle.owned_by","left");
		}
		if($table_name === "accounts"){
			$this->db->join("operation", "operation.operation_id = $table_name.operation_id","left");
			$this->db->join("operation_line", "operation_line.operation_id = $table_name.operation_id","left");
			$this->db->join("booking", "booking.booking_id = $table_name.booking_id","left");
			$this->db->join("vehicle", "vehicle.vehicle_id = operation_line.op_vehicle_id","left");
			$this->db->join("veh_type", "veh_type.veh_type_id = operation_line.op_vehicle_type","left");
			$this->db->join("diesel", "diesel.diesel_op_id = $table_name.operation_id","left");
			$this->db->join("vendor", "vendor.vendor_id = vehicle.owned_by","left");
		}
		$this->db->join("customers", "customers.cust_id = $table_name.cust_id","left");
		$this->db->join("trip_type", "trip_type.trip_type_id = $table_name.trip_type","left");		
		$this->db->join("booking_type", "booking_type.booking_type_id = $table_name.cust_type","left");	
		$this->db->join("city frm", "frm.city_id = $table_name.orgin","left");
		$this->db->join("city to", "to.city_id = $table_name.destination","left");
		$this->db->join("people", "people.person_id = $table_name.referral","left");
		
		if($table_name === "booking"){
			$this->db->join('balance_log', "balance_log.booking_id = $table_name.booking_id",'left');
		}
		if($table_name === "operation"){
			$this->db->join('balance_log', "balance_log.booking_id = $table_name.booking_id",'left');
			$this->db->join('suspence', "suspence.suspence_op_id = $table_name.operation_id",'left');
			$this->db->join('driver_suspence', "driver_suspence.driver_suspence_op_id = $table_name.operation_id",'left');
		}
		if($table_name === "accounts"){
			$this->db->join('balance_log', "balance_log.booking_id = $table_name.booking_id",'left');
			$this->db->join('suspence', "suspence.suspence_op_id = $table_name.operation_id",'left');
			$this->db->join('driver_suspence', "driver_suspence.driver_suspence_op_id = $table_name.operation_id",'left');
		}

		if($table_name === "enquiry"){
			$this->db->join("veh_type", "veh_type.veh_type_id = $table_name.vehicle_type","left");
			//$this->db->where("DATE_FORMAT($dbprefix.created_date, '%Y-%m-%d') BETWEEN $start_date AND $end_date");	
			//Viji told to change on 21stMAR2019
			if($lead_sts[0] === "3"){
				$this->db->where("DATE_FORMAT($dbprefix.from_date, '%Y-%m-%d') BETWEEN $start_date AND $end_date");
			}else{
				$this->db->where("DATE_FORMAT($dbprefix.created_date, '%Y-%m-%d') BETWEEN $start_date AND $end_date");
			}
		}else{
			$this->db->where("DATE_FORMAT($dbprefix.from_date, '%Y-%m-%d') BETWEEN $start_date AND $end_date");
		}
		
		if($lead_sts){
			$this->db->where_in("$table_name.$sts", $lead_sts);
		}		
		if($booking_type){
			$this->db->where_in("$table_name.cust_type", $booking_type);
		}		
		if($trip_type){
			$this->db->where_in("$table_name.trip_type", $trip_type);
		}		
		if($vehicle_type){
			$this->db->where_in("$table_name.vehicle_type", $vehicle_type);
		}		
		if($vehicle_no){
			$this->db->where_in("operation_line.op_vehicle_id", $vehicle_no);
 		}        		
		if($referral){
			$this->db->where_in("$table_name.referral", $referral);
		}		
		if($referral_type){
			$this->db->where_in("customers.referral_type", $referral_type);
		}
		if($orgin){
			$this->db->where_in("$table_name.orgin", $orgin);
		}
		if($destination){
			$this->db->where_in("$table_name.destination", $destination);
		}
		
		$this->db->where("$table_name.status",1);
		if($table_name === "booking"){
			$this->db->group_by("$table_name.booking_id");
		}
		if($table_name === "operation"){
			$this->db->group_by("$table_name.operation_id");
		}
		
		if($table_name === "accounts"){
			$this->db->group_by("$table_name.operation_id");
		}
		if((($table_name === "operation") && ($vehicle_no)) || (($table_name === "accounts") && ($vehicle_no))){
			$this->db->group_by("$table_name.operation_id,operation_line.op_line_id");
		}
		return $this->db->get()->result();
	}
}
?>