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

class Sales_Return_Model extends CI_Model {
  
    private $tableName = 'sales_return';
  
	function __construct() 
	{
		/* Call the Model constructor */
		parent::__construct();
	}
  
  
    //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('sales_return sr', 'sr.sl_rtn_id = sp.sale_id', 'left');
	$this->db->where("sp.sale_payment_type",'sales_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_sale_return_info($id)
	 {
		$this->db->select('sr.*, SUM(sp.sale_pymnt_amount) AS sls_rtn_total_paid,sp.sale_pymnt_paying_by');
		$this->db->from('sales_return sr');
		$this->db->join("sale_payments sp", "sr.sl_rtn_id = sp.sale_id AND sp.sale_payment_type ='sales_return'", "left");
		$this->db->where("sr.sl_rtn_id", $id);
		$this->db->order_by("sr.sl_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('sales_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,$srh_from_date='',$srh_to_date='')
	{
		$this->db->select_sum('sri.quantity');
		$this->db->from('sales_return_items sri');
		$this->db->join('sales_return sr', 'sr.sl_rtn_id = sri.sl_rtn_id', 'left');
		if($warehouse_id){
		$this->db->where('sr.warehouse_id',$warehouse_id);
		}
		$this->db->where('sri.product_id',$product_id);
		
		if($srh_from_date){
			$srh_from_date=date('Y-m-d',strtotime($srh_from_date));
			$this->db->where("date(sr.sl_rtn_datetime) >=",$srh_from_date);
		}
		if($srh_to_date){
			$srh_to_date=date('Y-m-d',strtotime($srh_to_date));
			$this->db->where("date(sr.sl_rtn_datetime) <=",$srh_to_date);
		}
		
		
		$query=$this->db->get();
		return $data['quantity']=$query->row()->quantity;
	}

	//Sales return get item list
	public function get_sale_return_item_list($sl_rtn_id)
	 {
		$this->db->select('s.*, p.product_name, p.product_code, s.quantity, ,p.product_part_no,p.product_oem_part_number');
		$this->db->from('sales_return_items s');
		$this->db->join('product p', 's.product_id = p.product_id', 'left');
		$this->db->order_by("s.id", "desc");
		$this->db->where("s.sl_rtn_id", $sl_rtn_id);//("id !=",$id);
		$query = $this->db->get();
		return $query->result_array();
	 }
	 
	//Sales return get avalable qty
	function get_avalable_product_qty_for_return($product_id,$warehouse_id,$sale_id){
		$this->db->select_sum('fi.fi_qty');
		$this->db->where("fi.fi_type_id", 'sale');
		$this->db->where("fi.fi_item_id", $product_id);
		$this->db->where("fi.fi_ref_id", $sale_id);
		$query = $this->db->get('fi_table fi');
		//echo $this->db->last_query();
		return $query->row()->fi_qty;
	}
	
	//Sales return get get product qty
	function get_sales_return_product_qty($product_id,$warehouse_id,$sale_id){
		$this->db->select_sum('i.quantity');
		$this->db->from('sales_return r');
		$this->db->join('sales_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;
	}
  
    //Sales return get all sales
	function get_all_sales_return($start='',$length='',$search_key_val='',$srh_from_date='',$srh_to_date='',$srh_warehouse_id='') {
	//	$this->db->select('sr.*, c.cus_name, s.sale_reference_no, SUM(sp.sale_pymnt_amount) AS sls_rtn_total_paid');
		$this->db->select('sr.*, c.cus_name, s.sale_reference_no');
		$this->db->from('sales_return sr');
		$this->db->join('customer c', 'sr.customer_id = c.cus_id', 'left');
		$this->db->join('sales s', 's.sale_id = sr.sale_id', 'left');
	//	$this->db->join("sale_payments sp", "sr.sl_rtn_id = sp.sale_id AND sp.sale_payment_type ='sales_return'", "left");
		$this->db->group_by("sr.sl_rtn_id");		
		$this->db->order_by("sr.sl_rtn_id", "desc");
		if($srh_from_date){
			$srh_from_date=date('Y-m-d',strtotime($srh_from_date));
			$this->db->where("date(sr.sl_rtn_datetime) >=",$srh_from_date);
		}
		if($srh_to_date){
			$srh_to_date=date('Y-m-d',strtotime($srh_to_date));
			$this->db->where("date(sr.sl_rtn_datetime) <=",$srh_to_date);
		}
		if($srh_warehouse_id){
			$this->db->where("sr.warehouse_id",$srh_warehouse_id);
		}
		
		if($start!='' && $length!=''){
			if($length>0) $this->db->limit($length,$start);
		}
		$query = $this->db->get();
		return $query->result_array();
	}

	//Sales return save sales return
	function save_sales_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_sales_return_item(&$data_item)
	{
			$this->db->insert('sales_return_items',$data_item);
	}	
	
	//save sale serial numbers
	function save_sale_return_items_serial_numbers($data_item)
	{
		$this->db->insert('sale_return_items_serial',$data_item);
	}
	
}