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

class Purchase_Return_Model_V2 extends CI_Model {
  
    private $tableName = 'purchase_return';
  
	function __construct() 
	{
		/* Call the Model constructor */
		parent::__construct();
	}
  
  
  function get_product_by_serial_no($term='',$cat_id='',$srh_warehouse_id='') {
		//
		
	//	echo "$manual_search";
		$this->db->select('p.*,i.*');
		$this->db->from('purchase_items_serial i');
		$this->db->join('product p', 'p.product_id = i.product_id', 'left');
		
		$this->db->order_by("p.product_name", "asc");
		$cat_query='';
		if($cat_id){
			$cat_query="AND p.cat_id='$cat_id'";
		}
		if($srh_warehouse_id){
			$cat_query.="AND i.avalable_warehouse_id='$srh_warehouse_id'";
		}
		
		//$this->db->where("(i.pis_number = '$term' OR p.product_name LIKE '%$term%' OR p.product_code LIKE '%$term%' ) AND i.product_avalable='1' AND p.product_sn_exissts='1' AND p.product_status='1' $cat_query");
		
		$this->db->where("i.pis_number = '$term' AND i.product_avalable='1' AND p.product_sn_exissts='1' AND p.product_status='1' $cat_query");

		$this->db->where("p.is_saleable","yes");
		
		 $this->db->limit(20, 0);
		$query = $this->db->get();
		echo $this->db->last_query();
		return $query->result_array();
	}
  
    //Sales get toatal paid 
  function get_total_return_by_sale_id($sale_id){
	$this->db->select_sum('sp.sale_pymnt_amount');
	$this->db->from('sale_payments sp');
	$this->db->join('purchase_return sr', 'sr.sl_rtn_id = sp.sale_id', 'left');
	$this->db->where("sp.sale_payment_type",'purchase_return');
	$this->db->where("sr.sale_id",$sale_id);
	$query=$this->db->get();
	//echo $this->db->last_query();
	if($query->row()->sale_pymnt_amount){
		return $query->row()->sale_pymnt_amount;
	}else {
		return 0;
	}
  }
    //Sales return get info by id
	public function get_purchase_return_info($id)
	 {
		$this->db->select('s.*,pr.*, SUM(sp.sale_pymnt_amount) AS pur_rtn_total_paid');
		$this->db->from('purchase_return pr');
		$this->db->join("sale_payments sp", "pr.pur_rtn_id = sp.sale_id AND sp.sale_payment_type ='purchase_return'", "left");
		$this->db->join('supplier s', 's.supp_id = pr.supp_id', 'left');
		$this->db->where("pr.pur_rtn_id", $id);
		$this->db->order_by("pr.pur_rtn_id", "desc");
		//$this->db->group_by("sr.sl_rtn_id");
		$query = $this->db->get();
		return $query->row_array(); 
	 } 
	 
	public function get_return_sale_info_sale_id($id)
	 {
		$this->db->select('*');
		$this->db->from('purchase_return');
		$this->db->where("sale_id", $id);
		$this->db->order_by("sale_id", "desc");
		$query = $this->db->get();
		return $query->result(); 
	 }
	 
  	//getSalesReturnQtyByWarehouseId
	public function getSalesReturnQtyByWarehouseId($warehouse_id,$product_id)
	{
		$this->db->select_sum('sri.quantity');
		$this->db->from('purchase_return_items sri');
		$this->db->join('purchase_return sr', 'sr.sl_rtn_id = sri.sl_rtn_id', 'left');
		$this->db->where('sr.warehouse_id',$warehouse_id);
		$this->db->where('sri.product_id',$product_id);
		$query=$this->db->get();
		return $data['quantity']=$query->row()->quantity;
	}

	//Sales return get item list
	public function get_grn_return_item_list($pur_rtn_id)
	 {
		$this->db->select('pri.*, p.product_name, p.product_code ,p.product_part_no,p.product_oem_part_number');
		$this->db->from('purchase_return_items pri');
		$this->db->join('product p', 'pri.product_id = p.product_id', 'left');
		$this->db->order_by("pri.pur_rtn_itm_id", "desc");
		$this->db->where("pri.pur_rtn_id", $pur_rtn_id);//("id !=",$id);
		$query = $this->db->get();
		return $query->result_array();
	 }
	 

	

  
    //Sales return get all sales
	function get_all_purchase_return() {
		$this->db->select('pr.*,s.supp_company_name, p.reference_no, SUM(sp.sale_pymnt_amount) AS pur_rtn_total_paid');
		$this->db->from('purchase_return pr');
		$this->db->join('supplier s', 's.supp_id = pr.supp_id', 'left');
		$this->db->join('purchases p', 'p.id = pr.pur_id', 'left');
		$this->db->join("sale_payments sp", "pr.pur_rtn_id = sp.sale_id AND sp.sale_payment_type ='grn_return'", "left");
		$this->db->group_by("pr.pur_rtn_id");		
		$this->db->order_by("pr.pur_rtn_id", "desc");
		$query = $this->db->get();
		//echo $this->db->last_query();
		return $query->result_array();
	}

	//Sales return save sales return
	function save_purchase_return(&$sl_trn_data,$sl_trn_id=false)
	{
		if (!$sl_trn_id)
		{
			$this->db->insert($this->tableName,$sl_trn_data);
		}
	}	
	
	//Sales return save items
	function save_purchase_return_item(&$data_item)
	{
			$this->db->insert('purchase_return_items',$data_item);
	}	
	
	//save sale serial numbers
	function save_grn_return_items_serial_numbers($data_item)
	{
		$this->db->insert('purchase_return_items_serial',$data_item);
	}
	
	//GRN return get get product qty
	function get_purchase_return_product_qty($product_id,$warehouse_id,$sale_id){
		$this->db->select_sum('i.pur_rtn_itm_quantity');
		$this->db->from('purchase_return r');
		$this->db->join('purchase_return_items i', "i.pur_rtn_id = r.pur_rtn_id AND i.product_id='$product_id'", 'left');
		$this->db->where("r.warehouse_id", $warehouse_id);
		$this->db->where("r.pur_id", $sale_id);
		$query = $this->db->get();
		//echo $this->db->last_query();
		return $query->row()->pur_rtn_itm_quantity;
	}
	
	//Sales return get avalable qty
	function get_avalable_product_qty_for_return($product_id,$warehouse_id,$purchase_id){
		$this->db->select_sum('pi.quantity');
		//$this->db->where("fi.fi_type_id", 'sale');
		//$this->db->where("fi.fi_item_id", $product_id);
		$this->db->where("pi.purchase_id", $purchase_id);
		$query = $this->db->get('purchase_items pi');
		//echo $this->db->last_query();
		return $query->row()->quantity;
	}
	
/*		//Sales return get get product qty
	function get_purchase_return_product_qty($product_id,$warehouse_id,$sale_id){
		$this->db->select_sum('i.quantity');
		$this->db->from('purchase_return r');
		$this->db->join('purchase_return_items i', "i.sl_rtn_id = r.sl_rtn_id AND i.product_id='$product_id'", 'left');
		$this->db->where("r.warehouse_id", $warehouse_id);
		$this->db->where("r.sale_id", $sale_id);
		$query = $this->db->get();
		//echo $this->db->last_query();
		return $query->row()->quantity;
	}*/
	
	public function get_next_ref_no($warehouse_id){
	$this->db->select_max('pur_rtn_warehouse_id');
	$this->db->where("warehouse_id", $warehouse_id);
	$query = $this->db->get('purchase_return');
     if($query->num_rows() >0)
     {
       $g = $query->result();
       $u = $g[0]->pur_rtn_warehouse_id;
       return $u;
     }
     else
     {
       return false;
     }
}
	
}