<?php
class Search_customer_model extends CI_Model{
	public function get_customer_suggestions($search){		
		$this->db->from('customers');	
		$this->db->where('deleted', 0);
		$this->db->group_start();
			$this->db->like('customer_name', $search);
			$this->db->or_like('phone_number',$search);
			$this->db->or_like('cust_email',$search);
		$this->db->group_end();
		$this->db->order_by('customer_name', 'asc');
		$suggestions = array();
		foreach($this->db->get()->result() as $row){
			$suggestions[] = array('label' => "$row->customer_name / $row->phone_number", 'cust_id' => $row->cust_id,'customer_name' => $row->customer_name,'phone_number' => $row->phone_number,'cust_email' => $row->cust_email,'cust_address' => $row->cust_address);
		}
		return $suggestions;
	}
	
	public function customer_info($cust_id){	
		$this->db->from('customers');
		$this->db->join('state', 'state_code = customers.state','left');
		$this->db->where('cust_id', $cust_id);
		return $this->db->get()->result();
	}
	
	public function enquiry_info($cust_id){
		$this->db->select('*,frm.city_name as frm_city_name, to.city_name as to_city_name');
		$this->db->from('enquiry');
		$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('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->where('enquiry.cust_id',$cust_id);
		return $this->db->get()->result();
	}
	
	public function enquiry_summary($cust_id){
		$this->db->select('count(*) as count,enquiry_sts');
		$this->db->from('enquiry');
		$this->db->where('cust_id',$cust_id);
		$this->db->group_by('enquiry_sts');
		return $this->db->get()->result();
	}
	
	public function booking_summary($cust_id){
		$this->db->select('count(*) as count,booking_sts,IFNULL(sum(booking_amount),0) as booking_amount,IFNULL(sum(catering_amount),0) as catering_amount,IFNULL(sum(stay_amount),0) as stay_amount,IFNULL(sum(balance_amount),0) as balance_amount');
		$this->db->from('booking');
		$this->db->where('cust_id',$cust_id);
		$this->db->group_by('booking_sts');
		return $this->db->get()->result();
	}
	
	public function package_summary($cust_id){
		$this->db->select('count(*) as count,enquiry_sts');
		$this->db->from('temple_enquiry');
		$this->db->where('cust_id',$cust_id);
		$this->db->group_by('enquiry_sts');
		return $this->db->get()->result();
	}
	
	public function line_summary($cust_id){
		$this->db->select('count(*) as count,enquiry_sts,IFNULL(sum(amount),0) as amount');
		$this->db->from('package_line');
		$this->db->where('cust_id',$cust_id);
		$this->db->group_by('enquiry_sts');
		return $this->db->get()->result();
	}
	
	public function vehicle_summary($cust_id){
		$this->db->select('count(*) as count,veh_type.veh_type');
		$this->db->from('operation');
		$this->db->join('veh_type', 'veh_type.veh_type_id = operation.vehicle_type','left');
		$this->db->where('cust_id',$cust_id);
		$this->db->group_by('operation.vehicle_type');
		return $this->db->get()->result();
	}
	
	public function booking_info($cust_id){
		$this->db->select('*,frm.city_name as frm_city_name, to.city_name as to_city_name');
		$this->db->from('booking');
		$this->db->join('trip_type', 'trip_type.trip_type_id = booking.trip_type','left');
		$this->db->join('veh_type', 'veh_type.veh_type_id = booking.vehicle_type','left');
		$this->db->join('booking_type', 'booking_type.booking_type_id = booking.cust_type','left');
		$this->db->join('city frm', 'frm.city_id = booking.orgin','left');
		$this->db->join('city to', 'to.city_id = booking.destination','left');
		$this->db->where('booking.cust_id',$cust_id);
		return $this->db->get()->result();
	}
	
	public function operation_info($cust_id){
		$this->db->select('*,frm.city_name as frm_city_name, to.city_name as to_city_name');
		$this->db->from('operation');
		$this->db->join('operation_line', 'operation_line.operation_id = operation.operation_id','left');
		$this->db->join('trip_type', 'trip_type.trip_type_id = operation.trip_type','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.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('feed_back', 'feed_back.feed_vch_id = operation_line.op_vehicle_id','left');
		$this->db->join('maintenance', 'maintenance.op_id = operation.operation_id','left');
		$this->db->where('operation.cust_id',$cust_id);
		return $this->db->get()->result();
	}
	
	public function package_info($cust_id){
	   $this->db->from('package_line');
	   $this->db->join('package_management', 'package_management.package_id = package_line.package_id','left');
	   $this->db->where('package_line.cust_id',$cust_id);
	   return $this->db->get()->result();
	}
	
	public function temple_info($cust_id){
	   $this->db->from('temple_enquiry');
	   $this->db->join('package_management', 'package_management.package_id = temple_enquiry.package_id','left');
	   $this->db->where('temple_enquiry.cust_id',$cust_id);
	   return $this->db->get()->result();
	}
}
?>