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

class Purchase_Return_Model extends CI_Model {
  
    private $tableName = 'purchase_return';
  
	function __construct() 
	{
		/* Call the Model constructor */
		parent::__construct();
	}
  
  
      public function get_pur_rtn_payment_by_id($pur_rtn_id='')

  {

     $this->db->select('sp.*');

     $this->db->from('sale_payments sp');

     $this->db->where('sp.sale_id',$pur_rtn_id);

     $this->db->where('sp.sale_payment_type','grn_return');

     $query = $this->db->get();

     return $query->result();



  }
  
  
    //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($warehouse_id='',$start='',$length='',$search_key_val='',$get_count='',$search_key1='') {
	    
	    //echo "search_key1:$search_key1";
		$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");
		
		if($warehouse_id){
		$this->db->where("pr.warehouse_id", $warehouse_id);
		}
		
			if($search_key1){
            $this->db->where("s.supp_company_name LIKE '%$search_key1%' OR pr.pur_rtn_reference_no LIKE '%$search_key1%'");;
       	}
       	
		$this->db->group_by("pr.pur_rtn_id");		
		$this->db->order_by("pr.pur_rtn_id", "desc");
			if($start!='' && $length!=''){
            $this->db->limit($length,$start);
        }
		$query = $this->db->get();
		//echo $this->db->last_query();
		
	
        
        	if($get_count){
			return $query->num_rows();
		}else{
			return $query->result_array();
		}
        
		//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;
     }
}

 function get_all_purchase_return_for_report_sup($srh_warehouse_id, $srh_to_date, $srh_from_date, $from = '', $to = '', $srh_supplier_id)
    {
        $this->db->select('pr.*, pr.pur_rtn_inv_total as grand_total,pr.pur_rtn_datetime as date, pr.pur_rtn_reference_no');
        $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");
        if ($srh_warehouse_id) {
            $this->db->where("pr.warehouse_id", $srh_warehouse_id); //("id !=",$id);
        }
        if ($srh_supplier_id) {
            $this->db->where("pr.supp_id", $srh_supplier_id); //("id !=",$id);
        }
        if ($srh_to_date) {
            $this->db->where("date(pr.pur_rtn_datetime) <=", $srh_to_date); //("id !=",$id);
        }
        if ($srh_from_date) {
            $this->db->where("date(pr.pur_rtn_datetime) >=", $srh_from_date); //("id !=",$id);
        }
        if ($to) {
            $this->db->limit($to, $from);
        }
        
        $query = $this->db->get();
        
        if ($query->num_rows() > 0) {
            return $query->result_array();
        } else {
            return false;
        }

        

    }
	
}