<?php

if (!defined('BASEPATH')) exit('No direct script access allowed');

class inventory_stock_adjustment_model extends CI_Model {

    public function __construct() {

        parent::__construct();

        $this->category 		= TBL_INVENTORY_CATEGORY;

        $this->item 			= TBL_INVENTORY_ITEM;

        //$this->master_item 	= TBL_ITEM;

        $this->unit 			= TBL_UNIT;

        $this->inv_log 			= TBL_INV_LOG;

		$this->inv_stock_adjust	= TBL_INVENTORY_STOCK_ADJUST;

    }

    

    function get_gen_inv($inv_item_id) {        

        $this->db->select('lg.*, ic.name AS inv_cat_name, ii.item_name AS inv_item_name');        

        $this->db->join( TBL_INVENTORY_CATEGORY . ' AS ic', 'lg.category_id = ic.id' );

        $this->db->join( TBL_INVENTORY_ITEM . ' AS ii', 'lg.item_id = ii.id' );

        $this->db->order_by('id DESC');

        $this->db->limit(1);

        $q = $this->db->get_where( TBL_INVENTORY_STOCK_ADJUST .' AS lg', array( 'lg.item_id' => $inv_item_id, 'lg.status' => 1 ) );

        //echo $this->db->last_query();

		return $q->row_array();

    }

    

    function manipulate($inv_id){

        $post 				= $this->input->post();

        $now 				= date('Y-m-d H:i:s');

		$inv_category_id 	= $post['category_id'];

        $item_id 			= $post['item_id'];

        

        $q     	 			= $this->db->get_where( TBL_INV_LOG, array( 'inv_cat_id' => $inv_category_id, 'inv_item_id' => $item_id, 'type' => 3, 'status' => 1 ) );

       //echo $this->db->last_query(); echo "<br>";

	    $log    			= $q->row_array();

		//echo "<pre>"; print_r($post); echo "</pre>";

		//echo "<pre>"; print_r($log); echo "</pre>";

        $prev_now_avail_qty = !empty( $log['now_avail_qty'] ) ? $log['now_avail_qty'] : 0;

		$uom				= $log['uom'];

		$jo     	 		= $this->db->get_where( TBL_INVENTORY_STOCK_ADJUST, array( 'id' => $inv_id, 'status' => 1 ) );

        $jo_log    			= $jo->row_array();

		//echo "<pre>"; print_r($jo_log); echo "</pre>";

		//$prev_issue_qty 	= !empty( $jo_log['issue_qty'] ) ? $jo_log['issue_qty'] : 0;

		//$prev_balance_qty 	= !empty( $jo_log['balance_qty'] ) ? $jo_log['balance_qty'] : 0;

        if( !empty( $inv_id ) ) :

            // Drop Prev. Row (For EDIT page only)

            $this->db->delete( TBL_INV_LOG, array( 'id' => $log['id']) );

        

        elseif( !empty(  $log['id'] ) ) :

		

			$args = array(

				'category_id'   => $post['category_id'],

				'item_id'  		=> $item_id,

				//'stock_issue_to'=> $post['stock_issue'],

				'available_qty' => $post['balance_qty'],

				'adjust_qty'     => $post['adj_qty'],
				
				'adjust_type'	=> $post['adj_type'],

				'adjustment_date'	=> date("Y-m-d", strtotime($post['adj_date'])),

				'balance_qty'	=> $post['balance_qty'],

				'adjustment_reason'          => !empty( $post['desc'] ) ? $post['desc'] : 'Adjusted Quantity: '. $post['adj_qty'],

				'status'		=> 1,

				'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( $this->inv_stock_adjust, $args );

			

			

            // 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;

		

		if($post['adj_qty']){

			$args_issue = array(

				'category_id'   => $post['category_id'],

				'item_id'  		=> $item_id,

				//'stock_issue_to'=> $post['stock_issue'],

				'available_qty' => $post['balance_qty'],

				'adjust_qty'       => $post['adj_qty'],
				
				'adjust_type'       => $post['adj_type'],

				'adjustment_date'	=> date("Y-m-d", strtotime($post['adj_date'])),

				'balance_qty'	=> $post['balance_qty'],

				'adjustment_reason'          => !empty( $post['desc'] ) ? $post['desc'] : 'Adjusted Quantity: '. $post['adj_qty'],

				'status'		=> 1,

				'updated_on'    => $now,

				'updated_by'    => $this->user_data['user_id'],

			);

			$this->db->where('category_id', $post['category_id']);

			$this->db->where('item_id', $item_id);

			$this->db->update( $this->inv_stock_adjust, $args_issue );

			/*echo '<pre>POST:'; print_r( $post ); 

			 echo '<pre>'; print_r( $args_issue ); die;*/

		}

        

        $adj_qty 	= $post['adj_type'] ? $prev_now_avail_qty + $post['adj_qty'] : $prev_now_avail_qty - $post['adj_qty'];

        $total_qty 	= $post['adj_type'] ? $total_qty : $total_qty + $post['adj_qty'];

		

        $args = array(

            'type'          => 3,

            'inv_cat_id'    => $post['category_id'],

            'inv_item_id'   => $item_id,

          //  'uom'           => $uom,

			'cost_price'    => $log['cost_price'],

			'sell_price'    => $log['sell_price'],			

            'inv_qty'       => $post['adj_qty'],

            'desc'          => !empty( $post['desc'] ) ? $post['desc'] : 'Adjust Quantity: '. $post['adj_qty'],            

            'prev_avail_qty'=> $prev_now_avail_qty,

            'now_avail_qty' => $post['balance_qty'], 

			'bal_issued_qty'=> $post['balance_qty'],

			'total_issued_qty'=> $total_qty,           

            '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_INVENTORY_STOCK_ADJUST, array( 'item_id' => $inv_item_id, 'status' => 1 ) );

        $log = $q->row_array();

        

        // Delete

        $this->db->update( TBL_INVENTORY_STOCK_ADJUST, 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_INVENTORY_STOCK_ADJUST, array( 'item_id' => $log['item_id'], 'status != ' => 2 ) );

        $prev_log = $q->row_array();



        if( !empty( $prev_log['id'] ) ) :

            

            $this->db->update( TBL_INVENTORY_STOCK_ADJUST, array( 'status' => 1 ), array( 'id' => $prev_log['id'] ) );

        endif;

        

    }

    

    public function list_all() { 
		
		$this->db->select('lg.*, ic.name AS category_name, ii.item_name AS item_name');        

        $this->db->join( TBL_INVENTORY_CATEGORY . ' AS ic', 'lg.category_id = ic.id' );

        $this->db->join( TBL_INVENTORY_ITEM . ' AS ii', 'lg.item_id = ii.id' );
		
		$this->db->group_by('lg.item_id', 'DESC');
		
        $this->db->order_by('lg.id', 'DESC');
       
	    $q = $this->db->get(TBL_INVENTORY_STOCK_ADJUST .' as lg');
        
		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($cat_id, $item_id) {

        $this->db->select('now_avail_qty');   

		$this->db->order_by('id', 'desc');   

        $res = $this->db->get_where(TBL_INV_LOG, array('inv_cat_id' => $cat_id, 'inv_item_id' => $item_id, 'type' => 3, '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();

    }

    

    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_INVENTORY_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 

                 */

                                 

                $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

                         * ------------------------

                        */

                            // 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;

    }

}

?>