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

class Marketing_Order_Model extends CI_Model {
  
  private $tableName = 'sales_rep_issues';
  
  function __construct() 
  {
    /* Call the Model constructor */
    parent::__construct();
  }
  
  function get_rep_first_and_second_name_by_id($sales_rep_id)
	{
	$this->db->select('user_first_name');	
	$this->db->select('user_last_name');
	$this->db->from("user");
	$this->db->where("user_id",$sales_rep_id);
	$query=$this->db->get();
	return $query->row_array();
	
	}
  
  public function get_all_sales_user_reps()
	{
		$this->db->select('user.user_id');
		$this->db->select('user.user_first_name');
		$this->db->order_by("user_id", "asc");
		$this->db->where("user_status",1);
		$this->db->where("group_id", 4);
		$query = $this->db->get('user');
		return $query->result_array();
	
	}
	
	public function get_all_routes() {
		$this->db->select('*');
		$this->db->order_by("route_name", "desc");
		$this->db->where("route_id IS NOT NULL");
		$this->db->where("route_status",1);
		$query = $this->db->get('routes');
		return $query->result_array();
  }
  
  public function get_next_ref_no()
	{
	$this->db->select_max('sales_rep_issue_id');
	return $this->db->get('sales_rep_issues');	
	}
	
	 public function get_next_return_ref_no()
	{
	$this->db->select_max('sales_rep_issue_return_id');
	return $this->db->get('sales_rep_issues_return');	
	}
	
	
	
	 public function get_products_suggestions_get_by_rep_issue_id($term,$rep_issue_id)
  {
	   $this->db->distinct();
	$this->db->select('product.*');
	$this->db->from('product');
	
	$this->db->join('sales_rep_issue_items','product.product_id = sales_rep_issue_items.product_id ','left');
	$this->db->join('sales_rep_issues','sales_rep_issue_items.sales_rep_issue_id = sales_rep_issues.sales_rep_issue_id ','left');
	
	
	
	$this->db->where("sales_rep_issues.status","APPROVED" );
	$this->db->where("sales_rep_issues.sales_rep_issue_id",$rep_issue_id );
	$this->db->where("product.product_name LIKE '%$term%'");
	$this->db->where("sales_rep_issues.status","APPROVED" );
	$this->db->or_where("sales_rep_issues.sales_rep_issue_id",$rep_issue_id );
	
	$this->db->where("sales_rep_issues.status","APPROVED" );
	$this->db->where("product.product_code LIKE '%$term%'");
	$this->db->or_where("sales_rep_issues.sales_rep_issue_id",$rep_issue_id );

	$this->db->where("sales_rep_issues.status","APPROVED" );
	$this->db->where("product.product_oem_part_number LIKE '%$term%'");
	$this->db->or_where("sales_rep_issues.sales_rep_issue_id",$rep_issue_id );
	$this->db->where("product.product_part_no LIKE '%$term%'");

	$this->db->where("sales_rep_issues.status","APPROVED" );
	$this->db->where("sales_rep_issue_items.delivery_status",1 );
	$this->db->distinct();
		
	$this->db->limit(10, 0);
		$query = $this->db->get();
	//echo $this->db->last_query();
	return $query->result_array();
	  
  }
	
	
	public function get_product_suggestion($term)
	{
		$this->db->select('product'.'.*');
		$this->db->order_by("product_name", "asc");
		
		$this->db->where("product_name LIKE '%$term%' OR product_code LIKE '%$term%' OR product_oem_part_number LIKE '%$term%' OR product_part_no LIKE '%$term%'");
		 $this->db->limit(10, 0);
		$query = $this->db->get('product');
		return $query->result_array();
	
	
	}
	
	//cus_issue details
	public function get_all_details_of_cus_issue_id($cus_issue_id )
	{
	
	  $this->db->select('marketing_issue_customer.*');		
	  $this->db->select('customer.*');
	  $this->db->select('product.*');
	  $this->db->select('routes.route_name');
	  $this->db->from('marketing_issue_customer');
	 
	  $this->db->join('customer','customer.cus_id=marketing_issue_customer.cus_id','left');
	  $this->db->join('routes','routes.route_id=marketing_issue_customer.route_id','left');
	  
	   $this->db->join('product','product.product_id=marketing_issue_customer.product_id','left');
	  
	 
	  $this->db->where('marketing_issue_customer.cus_issue_id',$cus_issue_id);
	  $this->db->order_by('marketing_issue_customer.cus_issue_id','desc');
	  $query=$this->db->get();
	 	return $query->row_array();	    
 	
		
	}
	
	function get_sale_rep_invoice_item_qty($product_id,$sales_rep_id,$wharehouse_id,$sale_rep_issue_id)
	{
		
		$this->db->select_sum('quantity');
		$this->db->from('sale_items');
		$this->db->where("sale_items.product_id",$product_id);
		$this->db->where("sale_items.sales_rep_issue_id ",$sale_rep_issue_id);
		$query =$this->db->get();	
		return $query->row_array();	
		
	}
	
	function get_sales_rep_id($sale_rep_issue_id)
	{
		$this->db->select('sales_rep_id');
		$this->db->from('sales_rep_issues');
		$this->db->where("sales_rep_issue_id",$sale_rep_issue_id);
		$query =$this->db->get();	
		return $query->row_array();	
	}
	
	function get_warehouse_id_and_date($sale_rep_issue_id)
	{
		$this->db->select('wharehouse_id');
		$this->db->select('sales_rep_issue_date');
		$this->db->from('sales_rep_issues');
		$this->db->where("sales_rep_issue_id",$sale_rep_issue_id);
		$query =$this->db->get();	
		return $query->row_array();	
		
	}
	
	
	public function get_customer_order($id)
	{
		$this->db->select('sales_rep_issues.*,user.user_first_name');
	  $this->db->select('routes.route_name');
	  $this->db->from('sales_rep_issues');
	  $this->db->join('user','user.user_id=sales_rep_issues.sales_rep_id','left');
	  $this->db->join('routes','routes.route_id=sales_rep_issues.route_id','left');
	  
	  $this->db->where('sales_rep_issues.issue_type !=',"");
	   $this->db->where('sales_rep_issues.chit_id',$id);
	  $this->db->order_by('sales_rep_issues.sales_rep_issue_id','desc');
	  $query=$this->db->get();
	 	return $query->result_array();	  
		
		
	}
  
  
  public function get_all_sales_rep_issue()
  {
	  $this->db->select('sales_rep_issues.*,user.user_first_name');
	  $this->db->select('routes.route_name');
	  $this->db->from('sales_rep_issues');
	  $this->db->join('user','user.user_id=sales_rep_issues.sales_rep_id','left');
	  $this->db->join('routes','routes.route_id=sales_rep_issues.route_id','left');
	  
	  $this->db->where('sales_rep_issues.issue_type',"");
	  $this->db->where('sales_rep_issues.status !=',"PROCESS_DONE");
	  $this->db->order_by('sales_rep_issues.sales_rep_issue_id','desc');
	  $query=$this->db->get();
	 	return $query->result_array();	    
  
  }
  
  
  
  public function get_all_customer_issue()
  {
	  $this->db->select('marketing_issue_customer.*');		
	  $this->db->select('customer.cus_name,customer.cus_code');
	  $this->db->select('routes.route_name');
	  $this->db->select('product.product_name, product.product_code,product.grp_id,product.book_year');
	  $this->db->from('marketing_issue_customer');
	 
	  $this->db->join('customer','customer.cus_id=marketing_issue_customer.cus_id','left');
	  $this->db->join('routes','routes.route_id=marketing_issue_customer.route_id','left');
	  $this->db->join('product','product.product_id=marketing_issue_customer.product_id','left');
	 
	  $this->db->where('marketing_issue_customer.status',"ISSUED");
	  $this->db->order_by('marketing_issue_customer.cus_issue_id','desc');
	  $query=$this->db->get();
	 	return $query->result_array();	    
  
  } 
  
  
    public function get_all_customer_issue_return_auth()
  {
	  $this->db->select('marketing_issue_customer.*');		
	  $this->db->select('customer.cus_name,customer.cus_code');
	  $this->db->select('routes.route_name');
	  $this->db->select('product.product_name, product.product_code,product.grp_id,product.book_year');
	  $this->db->from('marketing_issue_customer');	 
	  $this->db->join('customer','customer.cus_id=marketing_issue_customer.cus_id','left');
	  $this->db->join('routes','routes.route_id=marketing_issue_customer.route_id','left');
	  $this->db->join('product','product.product_id=marketing_issue_customer.product_id','left');	 
	  $this->db->where('marketing_issue_customer.status',"RETURN_REQUEST");
	  $this->db->order_by('marketing_issue_customer.cus_issue_id','desc');
	  $query=$this->db->get();
	  return $query->result_array();	    
  
  }
  
  
  
  
  
    public function get_all_sales_rep_issue_for_management()
  {
	  $status="RADYTOAPPROVAL";
	  $status2="REJECTED";
	  $status3="HOLD";
	  $this->db->select('sales_rep_issues.*,user.user_first_name');
	  $this->db->select('routes.route_name');
	  $this->db->from('sales_rep_issues');
	  $this->db->join('user','user.user_id=sales_rep_issues.sales_rep_id','left');
	  $this->db->join('routes','routes.route_id=sales_rep_issues.route_id','left');
	  $this->db->where('status',$status);
	  $this->db->or_where('status',$status2);
	  $this->db->order_by('sales_rep_issues.sales_rep_issue_id','desc');
	  $query=$this->db->get();
	 	return $query->result_array();	    
  
  }
  
  function complete_sample_order($sales_rep_issue_id)
	{
	$data = array('status' => "PROCESS_DONE");
	$this->db->where('sales_rep_issue_id', $sales_rep_issue_id);
	return $this->db->update('sales_rep_issues', $data);	
		
	}
	
	
	
	
	 function save_cus_issue_return($data,$cus_issue_id)
	{
	
	$this->db->where('cus_issue_id', $cus_issue_id);
	return $this->db->update('marketing_issue_customer', $data);	
		
	}
	
	
	
	 function sample_order_finish($sales_rep_issue_id)
	{
	$data = array('status' => "APPROVED");
	$this->db->where('sales_rep_issue_id', $sales_rep_issue_id);
	return $this->db->update('sales_rep_issues', $data);	
		
	}

  
  
  
  function save_sales_rep_issue($data,$sales_rep_issue_id)
	{
		
		
		
		
		if ($sales_rep_issue_id==0)
		{
			$this->db->insert($this->tableName,$data);	
		}else 
		{
			$this->db->where('sales_rep_issue_id',$sales_rep_issue_id);
			return $this->db->update($this->tableName,$data);
				
		
		}
	}	
	
	function save_sales_rep_issue_retrurn($data,$sales_rep_issue_id)
	{
		
		
		
		
		if ($sales_rep_issue_id)
		{
			$this->db->insert('sales_rep_issues_return',$data);
			/*$data2=array(			
			'status'=>"COMPLETED",				
			);
			$this->db->where('sales_rep_issue_id',$sales_rep_issue_id);
			$this->db->update($this->tableName,$data2);*/
				
		}else 
		{
			/*$this->db->where('sales_rep_issue_return_id',$sales_rep_issue_id);
			return $this->db->update('sales_rep_issues_return',$data);*/
				
		
		}
	}	
	
	
	function save_sales_rep_issue_retrurn_complte($sales_rep_issue_id)
	{	
			$data2=array(			
			'status'=>"COMPLETED",				
			);
			$this->db->where('sales_rep_issue_id',$sales_rep_issue_id);
			$this->db->update($this->tableName,$data2);
		
	}	
	
	
	
	function save_sales_rep_issue_retrurn_complete($sales_rep_issue_id)
	{
		
	}
	
	
	
	 function save_sales_rep_issue_return($data,$sales_rep_issue_id)
	{
				
		if ($sales_rep_issue_id==0)
		{
			$this->db->insert($this->tableName,$data);	
		}else 
		{
			$this->db->where('sales_rep_issue_return_id',$sales_rep_issue_id);
			return $this->db->update($this->tableName,$data);
				
		
		}
	}	
		
	
	
	public function delete_old_sales_rep_issue_items($sales_rep_issue_id)
	{
		$this->db->where('sales_rep_issue_id', $sales_rep_issue_id);
		$this->db->delete('sales_rep_issue_items');
	}
	
	public function delete_old_sales_rep_issue_items_return($sales_rep_issue_id)
	{
		$this->db->where('sales_rep_issue_return_id', $sales_rep_issue_id);
		$this->db->delete('sales_rep_issues_return_item');
	}
	
	
	function save_customer_issue_item($data_item)
	{
			$result=$this->db->insert('marketing_issue_customer',$data_item);
			return $result;
	}	
	
	
	function save_sales_rep_issue_item($data_item)
	{
			$this->db->insert('sales_rep_issue_items',$data_item);
	}	

	
	function save_sales_rep_issue_item_return($data_item)
	{
			$this->db->insert('sales_rep_issues_return_item',$data_item);
	}	

	
	
	function get_sales_detail_by_sales_rep_issue_id($sale_rep_issue_id)
	{	
		
		$this->db->select('*');
		$this->db->from('sales_rep_issues');
		$this->db->where('sales_rep_issue_id', $sale_rep_issue_id);
		$query=$this->db->get();
		return $query->row_array();
				
	}
	
	
	
	public function get_item_return_qty_in_sales_rep_issue_id($SRI_id,$product_id)
	{
		$this->db->select_sum('sales_rep_retuned_qty');
		$this->db->from('sales_rep_issues_return_item');
 		$this->db->where('sales_rep_id',$SRI_id);
		$this->db->where('product_id',$product_id);
		$query =$this->db->get();
		$result=$query->row_array();
		
		return $result;
		
	}
	
	
	
	
	
	
	
	
	
	
	function get_sale_rep_issue_item_list_by_sale_id($sale_rep_issue_id)
	{
		$this->db->select('sales_rep_issue_items.product_id,sales_rep_issue_items.add_sales_excess_price,sales_rep_issue_items.add_sales_discount_price, sales_rep_issue_items.product_price,sales_rep_issue_items.product_cost, sales_rep_issue_items.salse_rep_issue_qty, product.product_name, product.product_code, product.product_part_no,product.product_oem_part_number');
		$this->db->from('sales_rep_issue_items');
		$this->db->join('product','sales_rep_issue_items.product_id = product.product_id','left');
		$this->db->where('sales_rep_issue_items.sales_rep_issue_id',$sale_rep_issue_id);
		$query = $this->db->get();
		return $query->result_array();                                                                  
	}
  

}