<?php
class Inventory_sale_report_model extends CI_Model {
    
    function __construct() {
        parent::__construct();
        $this->inv_item = TBL_INV_ITEM;
        $this->inv_general = TBL_INV_GENERAL;
        $this->inv_log = TBL_INV_LOG;
    }
    
    public function get_all(){
    
        $return = $inv_items = array();
        $post = $this->input->post();
        $whr = array(
            'il.status' => 1
        );
        
        $date_type = !empty( $post['date_type'] ) ? $post['date_type'] : 1;
        $date_clm  = $date_type == 2 ? 'iu.created_on'  : 'il.created_on';
        
        if( !empty( $post['submit'] ) ) :
            
            if( !empty( $post['item_id'] ) ) :
                $whr['il.inv_item_id'] = $post['item_id'];
            
                // Get All Items
                $this->db->order_by('ii.item_name');
                $this->db->group_by('ii.id');
                $this->db->select('ii.id, ii.item_name');
                if( $date_type == 2 ) :
                    
                    // From Issued
                    $this->db->join( TBL_INV_ITEM . ' AS ii', 'ii.id = iu.inv_item_id' );
                    $q = $this->db->get_where( TBL_INV_ISSUED . ' AS iu', array( 'iu.status' => 1 ) );
                else :
                    
                    // From PO Items
                    $this->db->join( TBL_INV_ITEM . ' AS ii', 'ii.id = po.item_id','left' );
                    $this->db->join( TBL_INV_LOG . ' AS il', 'il.inv_item_id = ii.id','left' );
                    $q = $this->db->get_where( TBL_PO_ITEMS . ' AS po', array( 'po.status !=' => 2, 'po.status !=' => 0 ) );
                endif;
                
                $inv_items = $q->result_array();    
            endif;
            
            if( !empty( $post['start_date'] ) ) :
                $e = explode('-', $post['start_date']);
                $whr['DATE('.$date_clm.') >='] = $e[2].'-'.$e[1].'-'.$e[0];
            endif;
            
            if( !empty( $post['end_date'] ) ) :
                $e = explode('-', $post['end_date']);
                $whr['DATE('.$date_clm.') <='] = $e[2].'-'.$e[1].'-'.$e[0];
            endif;
            
        endif;
        
        $this->db->order_by('id DESC');
        //$this->db->group_by('po.item_id');
        $this->db->select('iu.*, ii.item_name, ii.id AS inv_item_id, ii.selling_price, il.cost_price, il.now_avail_qty AS total_avail_qty, DATE(po.created_on) AS purchased_date, DATE( iu.created_on ) AS issued_date, '.$date_clm.' AS date');
        $this->db->join( TBL_INV_ITEM . ' AS ii', 'ii.id = il.inv_item_id' );
        $this->db->join( TBL_INV_ISSUED . ' AS iu', 'iu.inv_item_id = ii.id AND iu.status != 2', 'left' );
        
        $this->db->join( TBL_PO_ITEMS . ' AS po', 'il.inv_item_id = po.item_id AND po.status = 1', 'left');
        $q = $this->db->get_where( TBL_INV_LOG . ' AS il', $whr );
        $results =  $q->result_array();
        //echo '<pre>'; print_r( $results ); die;
        
        /*
         * Get Where as  from "TBL_PO_ITEMS" 
         * ----------------------------------
         * 
         * $whr = array(
            'po.status !=' => 2
        );
        
        $date_type = !empty( $post['date_type'] ) ? $post['date_type'] : 1;
        $date_clm  = $date_type == 2 ? 'iu.created_on'  : 'po.created_on';
        
        if( !empty( $post['submit'] ) ) :
            
            if( !empty( $post['item_id'] ) ) :
                $whr['po.item_id'] = $post['item_id'];
            
                // Get All Items
                $this->db->order_by('ii.item_name');
                $this->db->group_by('ii.id');
                $this->db->select('ii.id, ii.item_name');
                if( $date_type == 2 ) :
                    
                    // From Issued
                    $this->db->join( TBL_INV_ITEM . ' AS ii', 'ii.id = iu.inv_item_id' );
                    $q = $this->db->get_where( TBL_INV_ISSUED . ' AS iu', array( 'iu.status' => 1 ) );
                else :
                    
                    // From PO Items
                    $this->db->join( TBL_INV_ITEM . ' AS ii', 'ii.id = po.item_id' );
                    $q = $this->db->get_where( TBL_PO_ITEMS . ' AS po', array( 'po.status !=' => 2, 'po.status !=' => 0 ) );
                endif;
                
                $inv_items = $q->result_array();    
            endif;
            
            if( !empty( $post['start_date'] ) ) :
                $e = explode('-', $post['start_date']);
                $whr['DATE('.$date_clm.') >='] = $e[2].'-'.$e[1].'-'.$e[0];
            endif;
            
            if( !empty( $post['end_date'] ) ) :
                $e = explode('-', $post['end_date']);
                $whr['DATE('.$date_clm.') <='] = $e[2].'-'.$e[1].'-'.$e[0];
            endif;
            
        endif;
        
        $this->db->order_by('id DESC');
        //$this->db->group_by('po.item_id');
        $this->db->select('iu.*, ii.item_name, ii.id AS inv_item_id, ii.selling_price, il.cost_price, il.now_avail_qty AS total_avail_qty, DATE(po.created_on) AS purchased_date, DATE( iu.created_on ) AS issued_date, '.$date_clm.' AS date');
        $this->db->join( TBL_INV_ITEM . ' AS ii', 'ii.id = po.item_id' );
        $this->db->join( TBL_INV_ISSUED . ' AS iu', 'iu.inv_item_id = ii.id AND iu.status != 2', 'left' );
        $this->db->join( TBL_INV_LOG . ' AS il', 'il.inv_item_id = ii.id AND il.status = 1', 'left');
        
        $q = $this->db->get_where( TBL_PO_ITEMS . ' AS po', $whr );
        $results =  $q->result_array();
         */
        
        
        foreach( $results as $k => $iu ) :
            $i = $iu['issued_date'].'_'.$iu['inv_item_id'];
            
            if( empty( $return[$i] ) ) :
                $iu['tot_issued_qty'] = $iu['tot_sold_qty'] = 0;
                //$iu['usage_1'] = $iu['usage_2'] = $iu['usage_3'] = 0;
                $return[$iu['issued_date'].'_'.$iu['inv_item_id']] = $iu;
                
                
                if( empty( $inv_items[$iu['inv_item_id']] ) && empty( $post['item_id'] ) ) :
                    $inv_items[$iu['inv_item_id']] = array('id' => $iu['inv_item_id'], 'item_name' => $iu['item_name']);
                endif;
            endif;
            
            if( empty($return[$i]['usage_'.$iu['usage_type']]) ) :
                // Usage
                $return[$i]['usage_'.$iu['usage_type']] += $iu['total_issued_qty'];
                $return[$i]['tot_issued_qty'] += $iu['total_issued_qty'];
                $return[$i]['tot_sold_qty'] += $iu['total_sold_qty'];
            endif;
            
        endforeach;
        
        //echo $this->db->last_query(); die;
        
        return array(
            'inv_items' => $inv_items,
            'results'   => $return,
        );
    }
   
}
?>