<?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();
    }
    //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;
        }
    }
    function get_purchase_return_list_by_purchase_id($val)
    {
        return array();
    }
    //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.pr_id", $pur_rtn_id); //("id !=",$id);
        $query = $this->db->get();
        return $query->result_array();
    }
    public function get_grn_return_serial_by_id($pur_rtn_id = '', $pur_rtn_itm_id = '')
    {
        $this->db->select('pris.*');
        $this->db->from('purchase_return_items_serial pris');
        $this->db->where('pris.pur_rtn_id', $pur_rtn_id);
        $this->db->where('pris.pur_rtn_itm_id', $pur_rtn_itm_id);
        $query = $this->db->get();
        if ($query->num_rows() > 0) {
            return $query->result();
        } else {
            return false;
        }
    }
    //Sales return get all sales
    function get_all_purchase_return($start = '', $length = '', $search_key_val = '', $order = '')
    {
        $query = "SELECT `pr`.*, `s`.`supp_company_name`, `p`.`reference_no`, SUM(sp.sale_pymnt_amount) AS pur_rtn_total_paid
FROM (`purchase_return` pr)
LEFT JOIN `supplier` s ON `s`.`supp_id` = `pr`.`supp_id`
LEFT JOIN `purchases` p ON `p`.`id` = `pr`.`pur_id`
LEFT JOIN `sale_payments` sp ON `pr`.`pur_rtn_id` = `sp`.`sale_id` AND sp.sale_payment_type ='grn_return'";
        if ($search_key_val) {
            $query .= "WHERE `p`.`reference_no` LIKE '%$search_key_val%' OR pr.pur_rtn_reference_no LIKE '%$search_key_val%' OR s.supp_company_name
LIKE '%$search_key_val%'";
        }
        $query .= "GROUP BY `pr`.`pur_rtn_id`";
        if ($order) {
            $query .= "ORDER BY " . $order['column'] . " " . $order['dir'] . " ";
        }
        if ($start != '' && $length != '') {
            $query .= "LIMIT $start , $length ";
        }
        $result = $this->db->query($query);
        return $result->result_array();
        //print_r($result->result_array());
        /*$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 ($search_key_val) {
        $this->db->where("p.reference_no LIKE '%$search_key_val%' OR pr.pur_rtn_reference_no LIKE '%$search_key_val%' OR s.supp_company_name LIKE '%$search_key_val%'");
        }
        $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();
        return $query->result_array();*/
    }
    function get_all_purchase_return_count($search_key_val = '')
    {
        $this->db->select('COUNT(pur_rtn_id) AS prc');
        $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 ($search_key_val) {
            $this->db->where("p.reference_no LIKE '%$search_key_val%' OR pr.pur_rtn_reference_no LIKE '%$search_key_val%' OR s.supp_company_name LIKE '%$search_key_val%'");
        }
        $this->db->group_by("pr.pur_rtn_id");
        $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.pr_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 getPurchasedRtnQtyByWarehouseId($warehouse_id, $product_id, $srh_from_date = '', $srh_to_date = '', $search_key_val = '', $cat_srh = '')
    {
        $this->db->select_sum('pi.pur_rtn_itm_quantity');
        $this->db->from('purchase_return_items pi');
        //$this->db->join('purchases p', 'p.id = pi.purchase_id', 'left');
        $this->db->join("product pr", "pr.product_id = pi.product_id", "left");
        //$this->db->join("product_category pc", "pr.cat_id = pc.cat_id", "left");
        /*if ($cat_srh) {
            $this->db->where("pc.cat_name", $cat_srh);
        }
        if ($search_key_val) {
            $this->db->where("pi.product_code LIKE '%$search_key_val%'", "left");
        }*/
        if ($product_id)
            $this->db->where('pi.product_id', $product_id);
        /*if ($srh_to_date) {
            $srh_to_date = date('Y-m-d 23:59:59', strtotime($srh_to_date));
            $this->db->where("p.date <=", $srh_to_date);
        }
        if ($srh_from_date) {
            $srh_from_date = date('Y-m-d 00:00:00', strtotime($srh_from_date));
            $this->db->where("p.date >=", $srh_from_date);
        }*/
        $query = $this->db->get();
        if ($query->num_rows() > 0) {
            return $data['quantity'] = $query->row()->pur_rtn_itm_quantity;
        } else {
            return 0;
        }
    }
}