<?php
class Op_trip_report_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"; 
		}
		
		$this->db->select('*,frm.city_name as frm_city_name, to.city_name as to_city_name,IFNULL(sum(diesel_amt), 0) as diesel_amt,IFNULL(sum(suspence_amt), 0) as suspence_amt,IFNULL(sum(driver_suspence_amount), 0) as driver_susp_amt');
		$this->db->from('operation_line');
		$this->db->join('operation', 'operation.operation_id = operation_line.operation_id','left');
		$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('vehicle', 'vehicle.vehicle_id = operation_line.op_vehicle_id','left');
		$this->db->join('booking_type', 'booking_type.booking_type_id = operation.cust_type','left');
		$this->db->join('diesel', 'diesel.diesel_op_id = operation_line.operation_id and diesel.diesel_vch_id = operation_line.op_vehicle_id','left');
		$this->db->join('suspence', "suspence.suspence_op_id = operation_line.operation_id and suspence.suspence_vch_id = operation_line.op_vehicle_id and suspence.suspence_info != 'Balance_Received'",'left');
		$this->db->join('driver_suspence', 'driver_suspence.driver_suspence_op_id = operation_line.operation_id and driver_suspence.driver_suspence_vch_id = operation_line.op_vehicle_id','left');
		$this->db->join('city frm', 'frm.city_id = operation.orgin','left');
		$this->db->join('city to', 'to.city_id = operation.destination','left');
		
		
		
		if($search){
      $this->db->group_start();
        $this->db->like('operation.tour_no',$search);
        //$this->db->or_like('accounts.tour_no',$search);
        $this->db->or_like('customer_name',$search);
        $this->db->or_like('customers.phone_number',$search);
        $this->db->or_like('booking_type_name',$search);
        $this->db->or_like('trip_type_name',$search);
        $this->db->or_like('frm.city_name',$search);
        $this->db->or_like('to.city_name',$search);
        $this->db->or_like('veh_type',$search);
        //$this->db->or_like('accounts.billing_name',$search);
        $this->db->or_like('operation.from_date',$search);
        $this->db->or_like('operation.to_date',$search);
        $this->db->or_like('vehicle_no',$search);
        $this->db->or_like('op_driver_name',$search);
        //$this->db->or_like('accounts_sts',$search);
		
        if(strpos($search, '-') !== false){
          $this->db->or_like('from_date', date('Y-m-d',strtotime($search))); 
          $this->db->or_like('to_date', date('Y-m-d',strtotime($search)));
        }
        $this->db->group_end(); 
    }
		
		
		$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));
		$this->db->group_end();
		$this->db->where('operation.status',1);
		$this->db->group_by('operation_line.operation_id,operation.operation_id');
		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 excel_operation_trip_report($start_date, $end_date){
	
	$this->db->select('*,frm.city_name as frm_city_name, to.city_name as to_city_name,IFNULL(diesel_amt, 0) as diesel_amt,IFNULL(suspence_amt, 0) as suspence_amt,IFNULL(driver_suspence_amount, 0) as driver_susp_amt,vendor_name');
		$this->db->from('operation_line');
		$this->db->join('operation', 'operation.operation_id = operation_line.operation_id','left');
		$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('vehicle', 'vehicle.vehicle_id = operation_line.op_vehicle_id','left');
		$this->db->join('booking_type', 'booking_type.booking_type_id = operation.cust_type','left');
		$this->db->join('diesel', 'diesel.diesel_op_id = operation_line.operation_id and diesel.diesel_vch_id = operation_line.op_vehicle_id','left');
		$this->db->join('suspence', "suspence.suspence_op_id = operation_line.operation_id and suspence.suspence_vch_id = operation_line.op_vehicle_id and suspence.suspence_info != 'Balance_Received'",'left');
		$this->db->join('driver_suspence', 'driver_suspence.driver_suspence_op_id = operation_line.operation_id and driver_suspence.driver_suspence_vch_id = operation_line.op_vehicle_id','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('vendor', 'vendor.vendor_id = vehicle.owned_by','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));
		$this->db->group_end();
		$this->db->where('operation.status',1);
		$this->db->group_by('operation_line.operation_id,operation.operation_id');
		$excel = $this->db->get();
		return $excel->result_array();
	}
	
}

?>