<?php
if (!defined('BASEPATH'))
    exit('No direct script access allowed');
class Product_Models extends CI_Model
{
    public function __construct()
    {
        parent::__construct();
        $this->db->query("SET sql_mode=(SELECT REPLACE(@@sql_mode, 'ONLY_FULL_GROUP_BY', ''));");
    }
    function getUnit()
    {
        $this->db->select('*');
        $this->db->from('mstr_unit');
        $this->db->where('unit_status', 1);
        $query = $this->db->get();
        if ($query->num_rows() > 0) {
            return $query->result();
        } else {
            return false;
        }
    }
    function getProductsForQTYReport($wherehouse_id = '', $cat_srh = '')
    {
        $query = $this->db->query("SELECT p.*,pc.cat_name,psc.sub_cat_name FROM product p LEFT JOIN product_category pc ON pc.cat_id = p.cat_id
    LEFT JOIN product_sub_category psc ON psc.sub_cat_id = p.sub_cat_id GROUP BY p.product_id  ORDER BY p.product_name , p.product_status asc ");
        if ($query->num_rows() > 0) {
            return $query->result();
        } else {
            return false;
        }
    }
    function getTax()
    {
        $this->db->select('*');
        $this->db->from('tax_rates');
        $query = $this->db->get();
        if ($query->num_rows() > 0) {
            return $query->result();
        } else {
            return false;
        }
    }
    function save_product($pd)
    {
        if ($this->db->insert('product', $pd)) {
            $lst = $this->db->insert_id();
            return $lst;
        } else {
            return false;
        }
    }
    function save_product_material_table($pd)
    {
        if ($this->db->insert('product_material', $pd)) {
            $lst = $this->db->insert_id();
            return $lst;
        } else {
            return false;
        }
    }
    
    
    public function update_product_code($product_id)
    {
        if($product_id){
		$data = array(
            'product_code' => "PD" . sprintf("%06d", $product_id)
        );
        $this->db->where('product_id', $product_id);
        $this->db->update('product', $data);
        return $product_id;
        }
        else return false;
    }
    function getRowMaterials($start, $length, $search_key_val)
    {
        $this->db->select('p.* , c.cat_name , s.sub_cat_name, u.unit_name');
        $this->db->from('product p');
        $this->db->join('product_category c', 'c.cat_id = p.cat_id', 'left');
        $this->db->join('product_sub_category s', 's.sub_cat_id = p.sub_cat_id', 'left');
        $this->db->join('mstr_unit u', 'u.unit_id = p.product_unit', 'left');
        //$this->db->join('master_product_type mpt', 'mpt.mpt_id = p.product_type_id', 'left');
        if ($search_key_val) {
            //$this->db->where("p.product_name LIKE '%$search_key_val%' OR p.product_code LIKE '$search_key_val%'");
            $this->db->where("p.product_type_id = 1 AND  p.product_code LIKE '$search_key_val%'");
		    $this->db->or_where("p.product_type_id = 1 AND p.product_name LIKE '%$search_key_val%'");
        }else{
            $this->db->where("p.product_type_id = 1");
        }
		$this->db->order_by("p.product_id", "desc");
        if ($start != '' && $length != '') {
            $this->db->limit($length, $start);
            $query = $this->db->get();
            if ($query->num_rows() > 0) {
                return $query->result();
            } else {
                return false;
            }
        } else {
            $query = $this->db->get();
            if ($query->num_rows() > 0) {
                return $query->num_rows();
            } else {
                return false;
            }
        }
    }
     function getProducts($start, $length, $search_key_val)
    {
        $this->db->select('p.* , c.cat_name , s.sub_cat_name, u.unit_name,mpt.mpt_name,pbm.pbm_id');
        $this->db->from('product p');
        $this->db->join('product_category c', 'c.cat_id = p.cat_id', 'left');
        $this->db->join('product_sub_category s', 's.sub_cat_id = p.sub_cat_id', 'left');
        $this->db->join('mstr_unit u', 'u.unit_id = p.product_unit', 'inner');
        $this->db->join('master_product_type mpt', 'mpt.mpt_id = p.product_type_id', 'inner');
        $this->db->join('production_block_master pbm', 'pbm.product_id = p.product_id', 'left');
        if ($search_key_val) {
            //$this->db->where("p.product_name LIKE '%$search_key_val%' OR p.product_code LIKE '$search_key_val%'");
              $this->db->where("p.product_code LIKE '$search_key_val%'");
		    $this->db->or_where("p.product_name LIKE '%$search_key_val%'");
        }
        //$this->db->where("p.product_type_id = 3");
		$this->db->order_by("p.product_id", "desc");
		$this->db->group_by("p.product_id");
        if ($start != '' && $length != '') {
            $this->db->limit($length, $start);
            $query = $this->db->get();
            if ($query->num_rows() > 0) {
                return $query->result();
            } else {
                return false;
            }
        } else {
            $query = $this->db->get();
            if ($query->num_rows() > 0) {
                return $query->num_rows();
            } else {
                return false;
            }
        }
    }
    function getProductsProduCodePrint($cat_id = 0, $sub_cat_id = 0)
    {
        $this->db->select('p.* , c.cat_name , s.sub_cat_name, u.unit_name');
        $this->db->from('product p');
        $this->db->join('product_category c', 'c.cat_id = p.cat_id', 'left');
        $this->db->join('product_sub_category s', 's.sub_cat_id = p.sub_cat_id', 'left');
        $this->db->join('mstr_unit u', 'u.unit_id = p.product_unit', 'left');
        if ($cat_id) {
            $this->db->where("p.cat_id", "$cat_id");
        }
        if ($sub_cat_id) {
            $this->db->where("p.sub_cat_id", "$sub_cat_id");
        }
        $this->db->order_by("p.product_id", "asc");
        $query = $this->db->get();
        if ($query->num_rows() > 0) {
            return $query->result();
        } else {
            return false;
        }
    }
    function getProductsForReport_del($wherehouse_id = '', $cat_srh = '')
    {
        $query = $this->db->query("SELECT p.*,pc.cat_name,psc.sub_cat_name, SUM(IF(ft.fi_type_id ='sale', ft.fi_qty, 0)) AS sold_qty, SUM(IF(ft.fi_type_id ='grn', ft.fi_qty, 0)) AS purchased_qty FROM product p LEFT JOIN fi_table ft ON ft.fi_item_id = p.product_id AND (ft.fi_type_id ='sale' OR ft.fi_type_id ='grn') LEFT JOIN product_category pc ON pc.cat_id = p.cat_id
    LEFT JOIN product_sub_category psc ON psc.sub_cat_id = p.sub_cat_id
    WHERE pc.cat_id!='' GROUP BY p.product_id  ORDER BY p.added_time desc");
        if ($query->num_rows() > 0) {
            return $query->result();
        } else {
            return false;
        }
    }
    function getProductsForReport($wherehouse_id = '', $cat_srh = '')
    {
        $this->db->select("p.*,pc.cat_name,psc.sub_cat_name,msu.unit_code");
        $this->db->from('product p');
        $this->db->join("product_category pc", "pc.cat_id = p.cat_id", "left");
        $this->db->join("product_sub_category psc", "psc.sub_cat_id = p.sub_cat_id", "left");
        $this->db->join("mstr_unit msu", "msu.unit_id = p.product_unit", "left");
        $this->db->where("p.sub_product_of ", 0);
        if($cat_srh)
            $this->db->where("p.cat_id ", $cat_srh);
        $this->db->group_by("p.product_id");
        $this->db->order_by("p.added_time", "desc");
        $query = $this->db->get();
        if ($query->num_rows() > 0) {
            return $query->result();
        } else {
            return false;
        }
    }
    function getSupplierProductsForReport($srh_warehouse_id, $supplier_srh)
    {
        $q = '';
        $q = "SELECT p.*,s.supp_company_name,s.supp_code,SUM(IF(ft.fi_type_id ='sale', ft.fi_qty, 0)) AS sold_qty,         SUM(IF(ft.fi_type_id ='grn', ft.fi_qty, 0)) AS purchased_qty
        FROM product p 
        LEFT JOIN purchase_items pi ON pi.product_id = p.product_id 
        LEFT JOIN purchases pu ON pu.id = pi.purchase_id
        LEFT JOIN supplier s ON s.supp_id = pu.supplier_id
        LEFT JOIN fi_table ft ON ft.fi_item_id = p.product_id AND (ft.fi_type_id ='sale' OR ft.fi_type_id ='grn')";
        if ($supplier_srh) {
            $q .= "WHERE pu.supplier_id=$supplier_srh";
        } else {
            $q .= "WHERE p.product_id!=0";
        }
        $q .= " GROUP BY p.product_id  
        ORDER BY p.added_time desc";
        $query = $this->db->query($q);
        if ($query->num_rows() > 0) {
            return $query->result();
        } else {
            return false;
        }
    }
    function get_product_by_name($name = '', $product_id = '')
    {
        $this->db->select('p.*');
        $this->db->from('product p');
        $this->db->where('p.product_name', $name);
        if ($product_id) {
            $this->db->where_not_in("p.product_id", $product_id);
        }
        $this->db->order_by("p.product_name", "asc");
        $query = $this->db->get();
        if ($query->num_rows() > 0) {
            return $query->row();
        } else {
            return false;
        }
    }
    function get_product_by_id($product_id = '')
    {
        $this->db->select('p.*, c.cat_name, u.unit_name, u.unit_code , t.*,b.brand_name,pm.product_mat_code,pm.product_mat_name');
        $this->db->from('product p');
        $this->db->join('product_category c', 'p.cat_id = c.cat_id', 'left');
        $this->db->join('mstr_unit u', 'p.product_unit = u.unit_id', 'left');
        $this->db->join('tax_rates t', 'p.tax = t.id', 'left');
        $this->db->join('master_brand b', 'p.brand_id = b.brand_id', 'left');
        $this->db->join('product_material pm', 'p.sub_product_of = pm.product_mat_id', 'left');
        $this->db->where('p.product_id', $product_id);
        $query = $this->db->get();
        if ($query->num_rows() > 0) {
            return $query->row();
        } else {
            return false;
        }
    }
    function get_sub_cat_name_by_id($id)
    {
        $this->db->select('s.sub_cat_name');
        $this->db->from('product_sub_category s');
        $this->db->where("s.sub_cat_id", $id);
        $query = $this->db->get();
        if ($query->num_rows() > 0) {
            return $data['sub_cat_name'] = $query->row()->sub_cat_name;
        } else {
            return false;
        }
    }
    function get_warehouse_product($product_id = '')
    {
        $this->db->select("w.name ,w.code , wp.quantity");
        $this->db->from("warehouses_products wp");
        $this->db->join("warehouses w", "wp.warehouse_id = w.id", "left");
        $this->db->where("wp.product_id", $product_id);
        $query = $this->db->get();
        if ($query->num_rows() > 0) {
            return $query->result();
        } else {
            return false;
        }
    }
    function get_subcategory()
    {
        $this->db->select('*');
        $this->db->from('product_sub_category');
        $query = $this->db->get();
        if ($query->num_rows() > 0) {
            return $query->result();
        } else {
            return false;
        }
    }
    function update_product($prduct_id, $pd)
    {
        if($prduct_id){
            $this->db->where('product_id', $prduct_id);
            if ($this->db->update('product', $pd)) {
                return true;
            } else {
                return false;
            }
        }else return false;
    }
    function update_product_material($sub_product_id, $pd)
    {
        if($sub_product_id){
            $this->db->where('product_mat_id', $sub_product_id);
            if ($this->db->update('product_material', $pd)) {
                return true;
            } else {
                return false;
            }
        }else return false;
    }
    public function delete_product($product_id = '')
    {
        if ($this->check_del($product_id)) {
            return false;
        } else {
            $this->db->delete('product', array(
                'product_id' => $product_id
            ));
            return true;
        }
    }
    function check_del($product_id = '')
    {
        $this->db->select('product_id');
        $this->db->from('purchase_items');
        $this->db->where('product_id', $product_id);
        $query = $this->db->get();
        if ($query->num_rows() > 0) {
            return true;
        } else {
            return false;
        }
    }
    function get_item_qty($product_id = '')
    {
        $this->db->select('SUM(p.quantity) AS qty');
        $this->db->from('purchase_items p');
        $this->db->where('p.product_id', $product_id);
        $query = $this->db->get();
        if ($query->result()) {
            return $query->result();
        } else {
            return false;
        }
    }
    function getProductsForAlertQtyReport($wherehouse_id = '', $cat_srh = '')
    {
        $this->db->select("p.*,pc.cat_name,psc.sub_cat_name");
        $this->db->from('product p');
        $this->db->join("product_category pc", "pc.cat_id = p.cat_id", "left");
        $this->db->join("product_sub_category psc", "psc.sub_cat_id = p.sub_cat_id", "left");
        $this->db->where('p.product_alert_qty > 0');
        $this->db->where('p.cat_id',$cat_srh);
        $this->db->group_by("p.product_id");
        $this->db->order_by("p.added_time", "desc");
        $query = $this->db->get();
        if ($query->num_rows() > 0) {
            return $query->result();
        } else {
            return false;
        }
    }
    function disable($product_id){
		if($product_id)
		$this->db->query('UPDATE `product` SET `product_status`= 0 WHERE `product_id` = '.$product_id.'');
		}
	function enable($product_id){
		if($product_id)
		$this->db->query('UPDATE `product` SET `product_status`= 1 WHERE `product_id` = '.$product_id.'');
		}
    public function get_search_products($str) {
        $this->db->select('c.*');
        $this->db->from('product c');
        $this->db->order_by("c.product_name", "asc");
        $this->db->or_like('c.product_name',$str);
		$this->db->or_like('c.product_code',$str);
	    $this->db->LIMIT(100);
        $query = $this->db->get();

        if ($query->num_rows() > 0) {
            return $query->result();
        } else {
            return false;
        }
    }
     public function get_search_products_material($str) {
        $this->db->select('c.*');
        $this->db->from('product_material c');
        $this->db->where("c.product_mat_status ='1'AND c.product_mat_type_id='2' AND product_mat_name LIKE '%$str%'");
        $this->db->or_where("c.product_mat_status ='1'AND c.product_mat_type_id='2' AND product_mat_code LIKE '%$str%'");
		$this->db->order_by("c.product_mat_name", "asc");
	    $this->db->LIMIT(100);
        $query = $this->db->get();

        if ($query->num_rows() > 0) {
            return $query->result();
        } else {
            return false;
        }
    }
    function get_list_sp($product_id)
	{
		$this->db->select('product_id,product_name,pr_quantity');
		$this->db->from('product');
		$this->db->where('sub_product_of',$product_id);
		$query=$this->db->get();
		return $query->result();
	}
	 function getScaleProducts()
    {
        $this->db->select('p.* , c.cat_name , s.sub_cat_name, u.unit_name');
        $this->db->from('product p');
        $this->db->join('product_category c', 'c.cat_id = p.cat_id', 'left');
        $this->db->join('product_sub_category s', 's.sub_cat_id = p.sub_cat_id', 'left');
        $this->db->join('mstr_unit u', 'u.unit_id = p.product_unit', 'left');
        $this->db->where("p.product_status", 1);
        $this->db->where("p.is_export_scale", 1);
        $this->db->order_by("p.product_code", "asc");
        $query=$this->db->get();
        return $query->result();
    }
    function getProductType()
    {
        $this->db->select('*');
        $this->db->from('master_product_type');
        $this->db->where('mpt_status', 1);
        $query = $this->db->get();
        if ($query->num_rows() > 0) {
            return $query->result();
        } else {
            return false;
        }
    }
    function save_product_block($pd)
    {
        if ($this->db->insert('production_block_master', $pd)) {
            $lst = $this->db->insert_id();
            return $lst;
        } else {
            return false;
        }
    }
    
    //added by namal
	function get_product_id($code)
    {
        $this->db->select('p.product_mat_id');
        $this->db->from('product_material p');
        $this->db->where("p.product_mat_code", $code);
        $query=$this->db->get();
        $result= $query->row_array();
        if(isset($result['product_mat_id'])){
            return $result['product_mat_id'];
        }else{
            return 0;
        }
    }
    
    
    function check_product_id($code)
    {
        $this->db->select('p.product_code');
        $this->db->from('product p');
        $this->db->where("p.product_code", $code);
        $query=$this->db->get();
        $result= $query->row_array();
        if(isset($result['product_code'])){
            return $result['product_code'];
        }else{
            return 0;
        }
    }
    
    
    
    //Added by namal
    function get_product_name($code)
    {
        $this->db->select('p.product_name');
        $this->db->from('product p');
        $this->db->where("p.product_code", $code);
        ///echo $this->db->last_query(); 
        $query=$this->db->get();
        $result= $query->row_array();
        if(isset($result['product_name'])){
            return $result['product_name'];
        }else{
            return 0;
        }
    }
}