<?php
class Er_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 = "ncd"; 
    }

   $this->db->select('enquiry_id,customer_name,customers.phone_number,cust_sts,trip_days,bus_count,enquiry.report_address,billing_name,first_name,avg_km,booking_type_name,frm.city_name as frm_city_name, to.city_name as to_city_name,from_date,to_date,trip_type_name,veh_type,enquiry_sts,prospect,trip_details,ncd,booking_amount');
    $this->db->from('enquiry');
    $this->db->join('customers', 'customers.cust_id = enquiry.cust_id','left');
    $this->db->join('trip_type', 'trip_type.trip_type_id = enquiry.trip_type','left');
    $this->db->join('veh_type', 'veh_type.veh_type_id = enquiry.vehicle_type','left');
    $this->db->join('employees', 'employees.id = enquiry.referral','left');
    $this->db->join('people', 'people.person_id = employees.person_id');
    $this->db->join('booking_type', 'booking_type.booking_type_id = enquiry.cust_type','left');
    $this->db->join('city frm', 'frm.city_id = enquiry.orgin','left');
    $this->db->join('city to', 'to.city_id = enquiry.destination','left');
    
    if($search){
      $this->db->group_start();
        $this->db->like('enquiry_id',$search);
        $this->db->or_like('customer_name',$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);
        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->or_like('ncd', 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))->or_where('DATE_FORMAT(ncd, "%Y-%m-%d") BETWEEN '. $this->db->escape($start_date).' AND '.$this->db->escape($end_date));
    $this->db->group_end(); 
  $this->db->where('enquiry_sts', '1');
  $this->db->where('enquiry.status',1);
    $this->db->order_by($sort,$order);
    if($rows>0){
      $this->db->limit($rows, $limit_from);
    }
    return $this->db->get();
   //echo $this->db->last_query();
  }

  public function get_found_rows($search, $filters){
    return $this->search($search, $filters)->num_rows();
  }
  
  public function get_excel_data($start_date, $end_date){

   $this->db->select('enquiry_id,customer_name,customers.phone_number,alt_number,cust_email,cust_sts,trip_days,bus_count,enquiry.report_address,billing_name,first_name,avg_km,booking_type_name,frm.city_name as frm_city_name, to.city_name as to_city_name,from_date,to_date,trip_type_name,veh_type,enquiry_sts,prospect,trip_details,ncd,booking_amount,enquiry.remark');
    $this->db->from('enquiry');
    $this->db->join('customers', 'customers.cust_id = enquiry.cust_id','left');
    $this->db->join('trip_type', 'trip_type.trip_type_id = enquiry.trip_type','left');
    $this->db->join('veh_type', 'veh_type.veh_type_id = enquiry.vehicle_type','left');
    $this->db->join('employees', 'employees.id = enquiry.referral','left');
    $this->db->join('people', 'people.person_id = employees.person_id');
    $this->db->join('booking_type', 'booking_type.booking_type_id = enquiry.cust_type','left');
    $this->db->join('city frm', 'frm.city_id = enquiry.orgin','left');
    $this->db->join('city to', 'to.city_id = enquiry.destination','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('enquiry_sts', '1');
  $this->db->where('enquiry.status',1);
    $this->db->order_by($sort,$order);
    if($rows>0){
      $this->db->limit($rows, $limit_from);
    }
    $enquiry = $this->db->get();
	return $enquiry->result_array();
  }
}
?>