<?php
if (!defined('BASEPATH')) exit('No direct script access allowed');
class inv_general_model extends CI_Model {
    public function __construct() {
        parent::__construct();
        $this->category = TBL_INV_CATEGORY;
        $this->item = TBL_INV_ITEM;
        $this->master_item = TBL_ITEM;
        $this->unit = TBL_UNIT;
        $this->inv_log = TBL_INV_LOG;
    }
    
    function get_gen_inv($inv_item_id) {
        
        $this->db->select('lg.*, ic.name AS inv_cat_name, ii.item_name AS inv_item_name, ii.uom AS uom_id, un.unit_name');
        
        $this->db->join( TBL_INV_CATEGORY . ' AS ic', 'lg.inv_cat_id = ic.id' );
        $this->db->join( TBL_INV_ITEM . ' AS ii', 'lg.inv_item_id = ii.id' );
        $this->db->join( TBL_UNIT . ' AS un', 'ii.uom = un.id', 'left' );
        
        $this->db->order_by('id DESC');
        $this->db->limit(1);
        $q = $this->db->get_where( TBL_INV_LOG .' AS lg', array( 'lg.inv_item_id' => $inv_item_id, 'lg.status' => 1 ) );
        return $q->row_array();
    }
    
    function manipulate($inv_item_id){
        $post = $this->input->post();
        $now = date('Y-m-d H:i:s');
        $inv_item_id = $post['item_id'];
        
        $q      = $this->db->get_where( TBL_INV_LOG, array( 'inv_item_id' => $inv_item_id, 'status' => 1 ) );
        $log    = $q->row_array();
        $prev_now_avail_qty = !empty( $log['now_avail_qty'] ) ? $log['now_avail_qty'] : 0;
        
        if( !empty( $inv_item_id ) ) :
            // Drop Prev. Row (For EDIT page only)
            $this->db->delete( TBL_INV_LOG, array( 'id' => $log['id']) );
        
        elseif( !empty(  $log['id'] ) ) :
            // Inactive Prev. Record
            $this->db->update( TBL_INV_LOG, array('status' => 0, 'updated_on' => $now, 'updated_by' => $this->user_data['user_id']), array( 'id' => $log['id'] ));
        endif;
        
        $adj_qty = $post['adj_type'] ? $prev_now_avail_qty + $post['adj_qty'] : $prev_now_avail_qty - $post['adj_qty'];
        
        $args = array(
            'type'          => 2,
            'inv_cat_id'    => $post['category_id'],
            'inv_item_id'   => $inv_item_id,
            'uom'           => $post['hidden_uom'],
            'inv_qty'       => $post['adj_qty'],
            'desc'          => !empty( $post['desc'] ) ? $post['desc'] : 'Adjusted Quantity: '. $post['adj_qty'],
            
            'prev_avail_qty'=> $prev_now_avail_qty,
            'now_avail_qty' => $adj_qty > 0 ? $adj_qty : 0,
            
            'created_on'    => $now,
            'created_by'    => $this->user_data['user_id'],
        );
        
        /*echo '<pre>POST:'; print_r( $post ); 
         echo '<pre>'; print_r( $args ); die;*/
        
        $this->db->insert( TBL_INV_LOG, $args );
        return $this->db->insert_id();
    }
    
    function delete( $inv_item_id ) {
        $now = date('Y-m-d H:i:s');
        
        $this->db->order_by('id DESC');
        $this->db->limit(1);
        $q = $this->db->get_where( TBL_INV_LOG, array( 'inv_item_id' => $inv_item_id, 'status' => 1 ) );
        $log = $q->row_array();
        
        // Delete
        $this->db->update( TBL_INV_LOG, array( 'status' => 2, 'updated_on' => $now, 'updated_by' => $this->user_data['user_id'] ), array( 'id' => $log['id'] ) );
        
        // Change Prev. Record
        $this->db->order_by('id DESC');
        $q = $this->db->get_where( TBL_INV_LOG, array( 'inv_item_id' => $log['inv_item_id'], 'status != ' => 2 ) );
        $prev_log = $q->row_array();

        if( !empty( $prev_log['id'] ) ) :
            
            $this->db->update( TBL_INV_LOG, array( 'status' => 1 ), array( 'id' => $prev_log['id'] ) );
        endif;
        
    }
    
    public function list_all() {
        
        $this->db->order_by('i.item_name, il.id DESC');
        $this->db->select('il.*, cat.name AS category_name, i.item_name, ii.id AS archanai_item_id',FALSE);
        $this->db->join($this->category . ' as cat','cat.id = il.inv_cat_id','left');
        $this->db->join($this->item . ' as i','i.id = il.inv_item_id','left');
        $this->db->join( TBL_ITEM . ' AS ii', 'ii.inv_item_id = i.id AND ii.status = 1', 'left' );
        
        $q = $this->db->get_where( TBL_INV_LOG . ' AS il', array( 'il.status' => 1 ));
        return $q->result_array();
        
    }
    
    public function category_list() {
        $this->db->where('status', 1);
        $this->db->where('is_statue_cat', 0);
        return $this->db->get($this->category)->result();
    }
    
    public function get_items_by_cat_id($category_id) {
        $this->db->select('i.id, i.item_name');
        $this->db->where('i.category_id',$category_id);
        $this->db->where('i.status',1);
        return $this->db->get($this->item . ' as i')->result();
    }
    
    public function get_unit_by_item_id($id) {
        /*$this->db->select('u.id, u.unit_name, i.quantity, i.issued_qty');
        $this->db->join($this->unit . ' as u','u.id = i.uom');
        $this->db->where('i.id',$id);
        $this->db->where('i.status',1);
        return $this->db->get($this->item . ' as i')->row();*/
        
        $this->db->select('u.id, u.unit_name, ig.available_qty');
        $this->db->join(TBL_INV_ITEM . ' as ii','ii.id = ig.item_id');
        $this->db->join($this->unit . ' as u','u.id = ii.uom');
        $res = $this->db->get_where( TBL_INV_GENERAL . ' AS ig', array('ig.item_id' => $id, 'ig.status' => 1) );
        return $res->row();
    }
   
    public function update_item_qty($data, $id){
        $this->db->where('id', $id);
        $this->db->update($this->item, $data);
        return $this->db->affected_rows();
    }
    
    
    /*public function get_by_id($id) {
        $this->db->select('g.*, g.category_id, cat.name as category_name, i.item_name, u.unit_name, CASE g.status WHEN 1 THEN "Active" ELSE "Inactive" END as status_str',FALSE);
        $this->db->join($this->category . ' as cat','cat.id = g.category_id','left');
        $this->db->join($this->item . ' as i','i.id = g.item_id','left');
        $this->db->join($this->unit . ' as u','u.id = i.uom');
        $this->db->where('g.id',$id);
        return $this->db->get($this->general . ' as g')->row();
    }*/
    
    function ajax(){ 
        $post       = $this->input->post();
        $date_now   = date("Y-m-d H:i:s");
        
        $return = array();
        switch($post['act']) :
            
            case 'rm_usage' :
                
                $q = $thi->db->get_where( TBL_INV_ISSUED, array( 'id' => $post['id'] ) );
                $iu = $q->row_array();
                
                $inv_item_id = $iu['inv_item_id'];
                $total_issued_qty = $iu['total_issued_qty'];
                
                if( $iu['usage_type'] == "1" ) :
                    // Reduce Counter
                    $this->db->set('avail_counter_qty', '`avail_counter_qty`-'. $total_issued_qty, FALSE);
                    $this->db->update( TBL_ITEM, array() , array('inv_item_id' => $inv_item_id ));
                endif;
                
                // Inv. Log
                $this->db->order_by('id DESC');
                $this->db->limit(1);
                $q = $thi->db->get_where( TBL_INV_LOG, array( 'inv_item_id' => $inv_item_id, 'status' => 1 ) );
                $log = $q->row_array();
                
                $bal_qty = $log['now_avail_qty'] - $total_issued_qty;
                $args = array(
                    'inv_cat_id'    => $log['inv_cat_id'],
                    'inv_item_id'   => $log['inv_item_id'],
                    'uom'           => $log['uom'],
                    'cost_price'    => $log['cot_price'],
                    'sell_price'    => $log['sell_price'],
                    'inv_qty'       => $total_issued_qty,
                    'prev_avail_qty'=> $log['now_avail_qty'],
                    'now_avail_qty' => $log['now_avail_qty'] - $total_issued_qty,
                    'total_issued_qty'=> $log['total_issued_qty'] - $total_issued_qty,
                    'bal_issued_qty' => $log['bal_issued_qty'] - $total_issued_qty,
                    'desc'          => 'Deleted Issued quanity',
                );
                
                $this->db->insert( TBL_INV_LOG, $args );
                
                
                $this->db->update( TBL_INV_ISSUED, array('status' => 2), array( 'id' => $iu['id'] ) );
                //echo 'Q: '. $this->db->last_query();
            break;    
            
            case 'get_usage':
                
                $inv_item_id = $post['invItemId'];
                    
                $this->db->order_by('id DESC');
                $this->db->select('*, DATE_FORMAT(`created_on`, "%d-%m-%Y") AS date', FALSE);
                $q = $this->db->get_where( TBL_INV_ISSUED, array('inv_item_id' => $inv_item_id,  'status !=' => 2) );
                //echo "Q: ". $this->db->last_query(); die;

                $return = $q->result_array();
                
            break;
        
            case 'usage' :
                
                $ids = explode('`', $post['ids']);
                $inv_log_id = $ids[0];
                $inv_item_id  = $ids[2];
                
                $this->db->order_by('id desc');
                $q = $this->db->get_where(TBL_INV_LOG, array('inv_item_id' => $inv_item_id, 'status' => 1));
                $log = $q->row_array();
                $prev_avail_qty = !empty( $log['now_avail_qty'] ) ? $log['now_avail_qty'] : 0;
                $now_avail_qty  = !empty( $log['now_avail_qty'] ) ? $log['now_avail_qty'] : 0; 
                $issued_qty     = !empty( $log['total_issued_qty'] ) ? $log['total_issued_qty'] : 0; 
                $bal_issued_qty = !empty( $log['bal_issued_qty'] ) ? $log['bal_issued_qty'] : 0; 
                
                /*
                 * Get Inventory Items
                 */
                $this->db->order_by('id desc');
                $q = $this->db->get_where(TBL_INV_ITEM, array('id' => $inv_item_id));
                $inv_item = $q->row_array();
                
                
                /*
                 * Get Last Purchase Entry Item
                 */

                $this->db->order_by('id desc');
                $q = $this->db->get_where(TBL_PO_ITEMS, array('item_id' => $inv_item_id, 'status' => 1));
                $poi = $q->row_array();
                
                /*
                 * Get Inventory Issued based on Item 
                 */
                 $this->db->select('SUM(total_issued_qty) as issued_qty');
                 $this->db->where('inv_item_id',$inv_item_id);
                 $this->db->group_by('inv_item_id');
                 $inv_total_qty = $this->db->get(TBL_INV_ISSUED)->row()->issued_qty;
                
                /*
                 * Get Inventory Log based on Item 
                 */
                 /* $this->db->select('prev_avail_qty,inv_qty');
                 $this->db->where('inv_item_id',$inv_item_id);
                 $this->db->where('status','1');
                 $this->db->where('issue_status','1');
                 $this->db->order_by('id','desc');
                 $inv_qtys = $this->db->get(TBL_INV_LOG)->row(); */
                
                $args = array(
                    'inv_cat_id'    => $ids[1],
                    'inv_item_id'   => $inv_item_id,
                    'uom'           => $log['uom'],
                    'cost_price'    => $poi['unit_price'],
                    'sell_price'    => $inv_item['selling_price'],
                    #'issue_status'  => 1,
                    'created_on'    => date("Y-m-d H:i:s"),
                    'created_by'    => $this->user_data['user_id'],
                );   
                
                $this->db->update( TBL_INV_LOG, array('status' => 0), array( 'id' => $log['id'], 'status' => 1 ) );
                $this->db->insert( TBL_INV_LOG, $args );
                $log_id = $this->db->insert_id();
                
                
                $total_qty = 0;
                for( $i = 1; $i <= 3; $i++ ) :
                    if( !empty( $post['usage'.$i] ) ) :
                        $qty = $post['usage'.$i];
                        /*
                         *  UPDATE THE SAME ROW
                         * ------------------------
                         $q = $this->db->get_where( TBL_INV_ISSUED, array('inv_item_id' => $inv_item['id'], 'usage_type' => $i, 'status' => 1));
                        $issue_row = $q->row_array();
                        
                        if( !empty( $issue_row['id'] ) ) :
                            // Update
                            $this->db->set('`total_issued_qty`', '`total_issued_qty`+'.$qty, FALSE);
                            $this->db->set('`total_avail_qty`', '`total_avail_qty`+'.$qty, FALSE);
                            $this->db->update( TBL_INV_ISSUED, array('updated_on' => $date_now, 'updated_by' => $session_data['user_id']), array('id' => $issue_row['id']));
                            
                        else :*/
                            // Insert
                            
                            $args = array(
                                'inv_item_id'       => $inv_item['id'],
                                'usage_type'        => $i,
                                'total_issued_qty'  => $qty,
                                'total_avail_qty'   => $qty,
                                'status'            => 1,
                                'created_on'        => $date_now,
                                'created_by'        => $this->user_data['user_id'],
                            );

                            $this->db->insert(TBL_INV_ISSUED, $args);
                        
                        //endif;
                    
                       $total_qty += $qty;
                       
                       if( $i == '1' ) :
                           // Counter
                           $this->db->set('`avail_counter_qty`', '`avail_counter_qty`+'.$qty, FALSE);
                           $this->db->update( TBL_ITEM, array(), array('inv_item_id' => $inv_item_id) );
                       endif;
                                                
                    endif;
                endfor;
                
                
                $args = array(
                    'inv_qty'           => $total_qty,
                    'prev_avail_qty'    => $prev_avail_qty,
                    'now_avail_qty'     => $now_avail_qty > $total_qty ? $now_avail_qty - $total_qty : 0,
                    'total_issued_qty'  => $issued_qty + $total_qty,
                );  

                $args['bal_issued_qty'] = $bal_issued_qty + $total_qty;
                $this->db->update( TBL_INV_LOG, $args, array( 'id' => $log_id ) );
                
                $this->db->select('SUM(not_issued_qty) as old_qty');
                $this->db->where('item_id',$inv_item_id);
                $this->db->where('not_issued_qty > ','0');
                $this->db->group_by('item_id');
                $old_po_qty = $this->db->get(TBL_PO_ITEMS)->row()->old_qty;
                 
                /*
                 * Get Last Purchase Entry Item
                 */
                $temp_total_qty = $total_qty;
                
                $this->db->where('item_id',$inv_item_id);
                $this->db->where('status','1');
                $this->db->order_by('id','asc');
                $po_items = $this->db->get(TBL_PO_ITEMS)->result();
                
                $temp_qty = (!empty($inv_total_qty) || $inv_total_qty > 0) ? (($inv_total_qty - $old_po_qty) + $total_qty) : $total_qty;
                
                $used_po_ids = array();
                foreach($po_items as $key=>$value) :
                    
                    if($temp_qty > 0) :     
                        $update = array();
                        if($value->not_issued_qty <= $temp_qty) :
                            // Fully Issued
                            $update['not_issued_qty'] = 0;
                            $update['status'] = 9;
                        else :
                            $update['not_issued_qty'] = $value->not_issued_qty - $temp_qty;
                        endif;
                        
                        $temp_qty = $total_qty - $value->not_issued_qty;
                        
                        $this->db->where('id',$value->id);
                        $this->db->update(TBL_PO_ITEMS,$update);
                        
                        if( !in_array($value->po_id, $used_po_ids) ) :
                            $used_po_ids[] = $value->po_id;
                            
                            // Set Purchase Order is not able to delete
                            $this->db->update( TBL_PURCHASE, array('status' => 9), array( 'id' => $value->po_id ) );
                        endif;
                        
                    else :
                        break;
                    
                    endif;
                    
                endforeach;
                
                $return['total'] = $temp_total_qty;
            break;
        
        endswitch;
        
        return $return;
    }
}
?>