<?php if ( ! defined('BASEPATH')) exit('No direct script access allowed');



class Customer_Order_Process_Model extends CI_Model {  

  private $master_tableName = 'customer_order_master';
  private $master_primary_key = 'cus_order_master_id';  
  private $item_tableName = 'customer_order_items';
  private $item_primary_key = 'cus_order_item_id';  

  function __construct() 

  {
	 /* Call the Model constructor */
    parent::__construct();
  }	

	public function get_product_suggestion($term)
	{
		// $query = $this->db->query("SELECT * FROM `product` WHERE `product_status` = 1 AND `product_code` LIKE '%$term%' OR `product_name` LIKE '%$term%' LIMIT 15");
		//return $query->result_array();
		
		 //	 $query = $this->db->query("SELECT * FROM `product` WHERE `product_status` = 1 AND `product_code` LIKE '%$term%' OR `product_name` LIKE '%$term%' LIMIT 15");
		
		$this->db->select('p.*,bp.*')
	
		         ->from('batch_product bp')
		         ->join('product p','p.product_id=bp.product_id','inner')
		         ->where("p.product_status = 1 AND p.product_name LIKE '%$term%'")
		         ->or_where("p.product_status = 1 AND p.product_code LIKE '%$term%'");
		         $this->db->limit(15, 0);
		$query=$this->db->get();
		return $query->result_array();
		
	}
	
 function insert_update_master_table($data,$id)
	{
		if (!$id)
		{
		$this->db->insert($this->master_tableName,$data);	
		}
		else 
		{
		$this->db->where($this->master_primary_key,$id);
		return $this->db->update($this->master_tableName,$data);
		}
	}
	
	 function insert_update_item_table($data,$id)
	{
		if (!$id)
		{
		return $this->db->insert($this->item_tableName,$data);	
		}else{
		$this->db->where($this->item_primary_key,$id);
		return $this->db->update($this->item_tableName,$data);
		}
	}	
	
	public function get_cus_order_master_details($order_id)
	{
	  $this->db->select($this->master_tableName.'.*,u.invoice_name,u.user_first_name,u.user_last_name,w.name,w.address,w.phone,w.email,mt.invoice_type_name');
	  $this->db->from($this->master_tableName);
	  $this->db->join('user u','u.user_id='.$this->master_tableName.'.order_rep_id','left');
	  $this->db->join('warehouses w','w.id='.$this->master_tableName.'.wharehouse_id','left');
	  $this->db->join('routes r','r.route_id='.$this->master_tableName.'.route_id','left');
	  $this->db->join('master_invoice_type mt','mt.invoice_type_id='.$this->master_tableName.'.invoice_type','left');
	  $this->db->where($this->master_tableName.'.cus_order_master_id',$order_id);
	  $query=$this->db->get();
	  return $query->row_array();	
	}
  
  function get_cus_order_item_details($order_id)
	{
		$this->db->select('so.*');
		$this->db->select('p.product_name, p.product_code, p.product_part_no,p.product_oem_part_number');
		$this->db->from('customer_order_items so');
		$this->db->join('product p','so.product_id = p.product_id','left');
		$this->db->where('so.cus_order_master_id',$order_id);
		
		$query = $this->db->get();
		return $query->result_array();
	}
	
	function get_cus_order_delivery_details($order_id)
	{
		$this->db->select('l.*');
		$this->db->select('u.user_first_name,u.user_last_name');
		$this->db->select('v.*');
		$this->db->from('customer_order_items so');
		$this->db->join('marketing_loading_unloading l','so.delivery_id = l.id','left');
		$this->db->join('user u','u.user_id= l.rep_id','left');
		$this->db->join('vehicles v','v.vehicle_id= l.lorry_id','left');
		$this->db->where('so.cus_order_master_id',$order_id);
		$this->db->where('so.delivery_status',1);
		$query = $this->db->get();
		return $query->row_array();	
		
	}
	
	 public function get_cus_order_master_list()
  {
	  $this->db->select($this->master_tableName.'.*,u.user_first_name,w.name,w.address,w.phone,w.email,r.route_name');
	  $this->db->from($this->master_tableName);
	  $this->db->join('user u','u.user_id='.$this->master_tableName.'.order_rep_id','left');
	  $this->db->join('customer c','c.cus_id='.$this->master_tableName.'.customer_id','left');
	  $this->db->join('warehouses w','w.id='.$this->master_tableName.'.wharehouse_id','left');
	  $this->db->join('routes r','r.route_id=c.route_id','left');
	  $this->db->where($this->master_tableName.'.status',0);
	  $query=$this->db->get();
	  return $query->result_array();	
  }
	
	
	 public function get_cus_order_master_loaded_list()
  {
	  $this->db->select($this->master_tableName.'.*,u.user_first_name,w.name,w.address,w.phone,w.email,r.route_name,c.cus_name');
	  $this->db->from($this->master_tableName);
	  $this->db->join('user u','u.user_id='.$this->master_tableName.'.order_rep_id','left');
	  $this->db->join('customer c','c.cus_id='.$this->master_tableName.'.customer_id','left');
	  $this->db->join('warehouses w','w.id='.$this->master_tableName.'.wharehouse_id','left');
	  $this->db->join('routes r','r.route_id=c.route_id','left'); 
	  $this->db->where($this->master_tableName.'.status',1);
	  $query=$this->db->get();
	  return $query->result_array();	
  }

	function get_delivary_customers($order_id)
	{
		$this->db->select('customer_id,cus_name,cus_phone,cus_address,cm.invoice_type,cm.cus_order_master_id,cm.total_amount,cm.customer_order_date,cm.cus_order_ref_no,route_name,cm.invoiced_status');
		$this->db->join('customer','customer.cus_id= cm.customer_id','left');
		$this->db->join('routes','customer.route_id= routes.route_id','left');
		$this->db->from('customer_order_master cm');		
		$this->db->order_by("customer.cus_name", "asc");
		$this->db->order_by("cm.cus_order_ref_no", "asc");
		$this->db->where('delivery_id',$order_id);
		$query = $this->db->get();
		return $query->result_array();
	}
	function get_customer_details_by_cus_id($order_id)
	{
		$this->db->select('cus_id,cus_name,cus_phone,cus_address');
		$this->db->from('customer');
		$this->db->where('cus_id',$order_id);
		$query = $this->db->get();
		return $query->result_array();
	}
	
	function get_all_cus_isued_item($warehouse_id,$product_id)
	{
		$this->db->select_sum('ci.issue_qty');		
		$this->db->from('customer_order_items ci');	
		$this->db->join('customer_order_master cm','cm.cus_order_master_id=ci.cus_order_master_id','left');
		$this->db->where("ci.product_id",$product_id);
		if($warehouse_id){
		$this->db->where("cm.wharehouse_id",$warehouse_id);	
		}
		$this->db->where("ci.delivery_status",1);
		$this->db->where("ci.item_status",1);
		$query=$this->db->get();
		$qty=$query->row_array();
		
		if(isset($qty['issue_qty']))
		{
			return $qty['issue_qty'];
		}
		else
		{
		return 0;	
		}		
	}
	function get_all_cus_return_item($warehouse_id,$product_id)
	{
		$this->db->select_sum('cr.return_qty');		
		$this->db->from('customer_order_return_items cr');	
		$this->db->where("cr.product_id",$product_id);
		$this->db->where("cr.delivery_status",1);
		$this->db->where("cr.item_status",1);
		if($warehouse_id){
		$this->db->where("cr.warehouse_id",$warehouse_id);	
		}
		$query=$this->db->get();
		$qty=$query->row_array();
		
		if(isset($qty['return_qty']))
		{
			return $qty['return_qty'];
		}
		else
		{
		return 0;	
		}		
	}
	
	function get_customer_sale_total($customer_id)
	{
		$this->db->select_sum('s.sale_total');		
		$this->db->from('sales s');	
		$this->db->where("s.customer_id",$customer_id);
		$query=$this->db->get();
		$qty=$query->row_array();
		
		if(isset($qty['sale_total']))
		{
			return $qty['sale_total'];
		}
		else
		{
		return 0;	
		}	
	}
	
	
	function get_customer_payment_total($customer_id)
	{
		$this->db->select_sum('sp.sale_pymnt_amount');		
		$this->db->from('sale_payments sp');
		$this->db->join('sales s','s.sale_id=sp.sale_id','inner');
		$this->db->where("s.customer_id",$customer_id);
		$this->db->where("sp.sale_payment_status",1);
		$query=$this->db->get();
		$qty=$query->row_array();
		
		if(isset($qty['sale_pymnt_amount']))
		{
			return $qty['sale_pymnt_amount'];
		}
		else
		{
		return 0;	
		}	
	}
	
	function get_customer_credit_limit($customer_id)
	{
		$this->db->select('c.cus_credit_limit');		
		$this->db->from('customer c');
		$this->db->where("c.cus_id",$customer_id);
		$this->db->where("c.cus_credit_status",1);
		$query=$this->db->get();
		$qty=$query->row_array();		
		if(isset($qty['cus_credit_limit']))
		{
			return $qty['cus_credit_limit'];
		}
		else
		{
		return 0;	
		}	
	}
	
	function get_customer_pending_order_total($customer_id)
	{
		$this->db->select_sum('cm.total_amount');		
		$this->db->from('customer_order_master cm');
		$this->db->where("cm.customer_id",$customer_id);
		$this->db->where("cm.status",0);
		$this->db->where("cm.order_approval_status",1);
		$query=$this->db->get();
		$qty=$query->row_array();		
		if(isset($qty['total_amount']))
		{
			return $qty['total_amount'];
		}
		else
		{
		return 0;	
		}	
	}
	
	function get_product_cash_price($product_id)
	{
		$this->db->select('product_price');
		$this->db->from('product');
		$this->db->where('product_id',$product_id);
		$query = $this->db->get();
		$price = $query->row_array();
		return $price['product_price']; 
	}
	
	
	
	
}