<?php
class Archanai_sale_report_model extends CI_Model {
    
    function __construct() {
        parent::__construct();
        $this->inv_item = TBL_INV_ITEM;
        $this->inv_category = TBL_INV_CATEGORY;
        $this->archanai_item = TBL_BARI;
        $this->items = TBL_ITEM;
        $this->inv_log = TBL_INV_LOG;
        $this->inv_issued = TBL_INV_ISSUED;
    }
    
    public function list_item(){        
        $this->db->select('i.*');
        $this->db->join($this->items . ' as i','i.id = ai.bari_item_id');
        $this->db->where('i.status !=',2);
        $this->db->where('i.is_inventory',1);
        $this->db->group_by('ai.bari_item_id');
        return $this->db->get($this->archanai_item . ' as ai')->result();
    
    }
    
    public function get_data($start_date='',$end_date='',$item_id=''){
        /* $total_avail_qty = ', (SELECT SUM(iss.total_avail_qty) as totalqty FROM ' . $this->inv_issued . ' as iss WHERE iss.inv_item_id = ii.id AND iss.usage_type = 1 AND iss.status != 2) as total_avail_quantity';    
            
            
        $total_issued_qty = ', (SELECT SUM(inv.total_sold_qty) as issued_qty FROM ' . $this->inv_issued . ' as inv WHERE inv.inv_item_id = ii.id AND inv.usage_type = 1 AND inv.status != 2) as total_sold_quantity'; */
        
        if(!empty($start_date) && !empty($end_date)) {
            $where = ' AND ( bari.created_on >= "'.date('Y-m-d 00:00:00',strtotime($start_date)).'" AND bari.created_on <= "'.date('Y-m-d 23:59:59',strtotime($end_date)).'" )';
        } else {
            $where = ' AND ( bari.created_on >= "'.date('Y-m-d 00:00:00').'" AND bari.created_on <= "'.date('Y-m-d 23:59:59').'" )';
        }
        
        $issued_qty = ', (SELECT SUM(bari.bari_qty) as qty FROM ' . $this->archanai_item .' as bari WHERE bari.bari_item_id = ai.bari_item_id AND bari.bari_is_inventory = 1' . $where . ') as issued_qty';
        
        if(!empty($start_date) && !empty($end_date)) {
            $o_where = ' AND ( obari.created_on < "'.date('Y-m-d 00:00:00',strtotime($start_date)).'" )';
        } else {
            $o_where = ' AND ( obari.created_on < "'.date('Y-m-d 00:00:00').'" )';
        }
        
        $old_issued_qty = ', (SELECT CASE WHEN ISNULL(SUM(obari.bari_qty)) THEN "0" ELSE (SUM(obari.bari_qty)) END as qty FROM ' . $this->archanai_item .' as obari WHERE obari.bari_item_id = ai.bari_item_id AND obari.bari_is_inventory = 1' . $o_where . ') as old_issued_qty';
        
        $total_issued_qty = ', (SELECT CASE WHEN ISNULL(SUM(issq.total_issued_qty)) THEN "" ELSE (SUM(issq.total_issued_qty)) END as iss_qty FROM ' . $this->inv_issued .' as issq WHERE issq.inv_item_id = ii.id AND issq.usage_type = 1) as total_issued_qty';
        
        $this->db->select('ii.id as iv_item, i.name as item, i.price, ai.created_on, iq.total_issued_qty as total_issued, iq.total_sold_qty, iq.total_avail_qty' . $issued_qty . $old_issued_qty . $total_issued_qty, FALSE);
        $this->db->join($this->items . ' as i','i.id = ai.bari_item_id');
        $this->db->join($this->inv_item . ' as ii','ii.id = i.inv_item_id');
        $this->db->join($this->inv_issued . ' as iq','iq.inv_item_id = ii.id AND iq.usage_type = 1');
        if(!empty($start_date) && !empty($end_date)) {
            $this->db->where('ai.created_on >=',date('Y-m-d 00:00:00',strtotime($start_date)));
            $this->db->where('ai.created_on <=',date('Y-m-d 23:59:59',strtotime($end_date)));
        } else {
            $this->db->where('ai.created_on >=',date('Y-m-d 00:00:00'));
            $this->db->where('ai.created_on <=',date('Y-m-d 23:59:59'));
        }
        if(!empty($item_id)) {
            $this->db->where('i.id',$item_id);
        }
        $this->db->where('i.status !=',2);
        $this->db->where('i.is_inventory',1);
        $this->db->where('ai.bari_is_inventory',1);
        $this->db->group_by('ai.bari_item_id');
        return $this->db->get($this->archanai_item . ' as ai')->result();
    }
}
?>