<?php
class Statue_books_stock_report_model extends CI_Model {
    
    function __construct() {
        parent::__construct();
        $this->category = TBL_INV_CATEGORY;
        $this->item = TBL_INV_ITEM;
        $this->templeshop = TBL_BILL_TEMPLESHOP_SALES;
        $this->templeshop_item = TBL_BILL_TEMPLESHOP_ITEM;
        $this->services = TBL_SERVICE;
        $this->statue_book = TBL_STATUE_BOOK;
        $this->sb_item = TBL_SB_ITEM;
    }
    
    public function list_category(){
        $this->db->select('id,code as cat_code,name as cat_name');
        $this->db->where('is_statue_cat',1);
        $this->db->where('status',1);
        return $this->db->get($this->category)->result();
    }
    
    public function list_cat_item($category_id){
        $this->db->select('id,code as item_code,item_name');
        if($category_id != '') {
        $this->db->where('category_id',$category_id);
        }
        $this->db->where('is_statue_item',1);
        $this->db->where('status',1);
        return $this->db->get($this->item)->result();
    }
   
    public function get_data($cat_id='',$item_id='') {
        $sale_qty = ', (SELECT CASE WHEN ISNULL(ti.quantity) THEN "0" ELSE SUM(ti.quantity) END as ti_quantity FROM ' . $this->templeshop_item . ' as ti WHERE ti.inventory_item_id = si.item_id AND ti.status != 2 GROUP BY ti.inventory_item_id) as sales_qty';
        
        $purchase_qty = ', (SELECT CASE WHEN ISNULL(sb.quantity) THEN "0" ELSE SUM(sb.quantity) END as sb_quantity FROM ' . $this->statue_book . ' as sb WHERE sb.item_id = si.item_id AND sb.status != 2 GROUP BY sb.item_id) as purchased_qty';
        
        $this->db->select('si.*, c.name as cat_name, i.item_name, i.selling_price' . $sale_qty . $purchase_qty , FALSE);
        $this->db->join($this->item . ' as i','i.id = si.item_id AND is_statue_item = 1');
        $this->db->join($this->category . ' as c','c.id = i.category_id AND is_statue_cat = 1');
        if(!empty($cat_id)) {
            $this->db->where('c.id',$cat_id);
        }
        if(!empty($item_id)) {
            $this->db->where('si.item_id',$item_id);
        }
        $this->db->group_by('si.item_id');
        $this->db->order_by('id','desc');
        return $this->db->get($this->sb_item . ' as si')->result();
    }
}
?>