<?php

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

class Itemissue_model extends CI_Model {

    public function __construct() {
        parent::__construct();
        $this->current_session = $this->setting_model->getCurrentSession();
    }

    public function get($id = null) {
        $sql = "SELECT item_issue.*,item.name as `item_name`,item.item_category_id,item_category.item_category FROM `item_issue` 
		INNER JOIN item on item.id=item_issue.item_id INNER JOIN item_category on item_category.id=item.item_category_id ";

        $query = $this->db->query($sql);
        return $query->result_array();
    }
    public function get_filtered($from_date = null, $to_date = null, $start_item = null, $end_item = null) {
        $this->db->select('item_issue.*, item.name as item_name, item.id as item_id, item.item_category_id, item_category.item_category');
        $this->db->from('item_issue');
        $this->db->join('item', 'item.id = item_issue.item_id');
        $this->db->join('item_category', 'item_category.id = item.item_category_id');
    
        // Apply date filters
        if ($from_date) {
            $this->db->where('item_issue.issue_date >=', $from_date);
        }
        if ($to_date) {
            $this->db->where('item_issue.issue_date <=', $to_date);
        }
    
        // Filter by item id range
        if ($start_item && $end_item) {
            $this->db->where('item.id >=', $start_item);
            $this->db->where('item.id <=', $end_item);
        } elseif ($start_item) {
            $this->db->where('item.id >=', $start_item);
        } elseif ($end_item) {
            $this->db->where('item.id <=', $end_item);
        }
    
        $this->db->order_by('item_issue.issue_date', 'asc');
        $query = $this->db->get();
        return $query->result_array();
    }
    


    public function get_filteredmvmnt($from_date = null, $to_date = null, $start_item = null, $end_item = null) {
        // Get Quantity Out (from item_issue)
        $this->db->select('
            item.id as item_id,
            item.name as item_name,
            item.item_category_id,
            item_category.item_category,
            MAX(item_issue.issue_date) as issue_date,  -- get latest date
            SUM(item_issue.quantity) as quantity_out
        ');
        $this->db->from('item_issue');
        $this->db->join('item', 'item.id = item_issue.item_id');
        $this->db->join('item_category', 'item_category.id = item.item_category_id');
        
        if ($from_date) {
            $this->db->where('item_issue.issue_date >=', $from_date);
        }
        if ($to_date) {
            $this->db->where('item_issue.issue_date <=', $to_date);
        }
    
        if ($start_item && $end_item) {
            $this->db->where('item.id >=', $start_item);
            $this->db->where('item.id <=', $end_item);
        } elseif ($start_item) {
            $this->db->where('item.id >=', $start_item);
        } elseif ($end_item) {
            $this->db->where('item.id <=', $end_item);
        }
        
    
        $this->db->group_by('item_issue.item_id');
        $item_issues = $this->db->get()->result_array();
    
        // Now get Quantity In (from item_stock)
        foreach ($item_issues as &$item) {
            $this->db->select_sum('quantity');
            $this->db->from('item_stock');
            $this->db->where('item_id', $item['item_id']);
    
            // Optional: filter by stock date if available
            if ($from_date) {
                $this->db->where('date >=', $from_date); // Only if you have a 'date' field
            }
            if ($to_date) {
                $this->db->where('date <=', $to_date);
            }
    
            $in_result = $this->db->get()->row();
            $quantity_in = $in_result ? $in_result->quantity : 0;
    
            $item['quantity_in'] = $quantity_in;
            $item['available_qnty'] = $quantity_in - $item['quantity_out'];
        }
    
        return $item_issues;
    }
    
    public function get_filteredlvl($start_item = null, $end_item = null, $from_date = null, $to_date = null) {
        // Get Quantity Out (from item_issue)
        $this->db->select('
            item.id as item_id,
            item.name as item_name,
            item.item_category_id,
            item_category.item_category,
            MAX(item_issue.issue_date) as issue_date,  -- get latest date
            SUM(item_issue.quantity) as quantity_out
        ');
        $this->db->from('item_issue');
        $this->db->join('item', 'item.id = item_issue.item_id');
        $this->db->join('item_category', 'item_category.id = item.item_category_id');
    
        // ——— DATE FILTERS ———
        if ($from_date) {
            $this->db->where('item_issue.issue_date >=', $from_date);
        }
        if ($to_date) {
            $this->db->where('item_issue.issue_date <=', $to_date);
        }
    
        // ——— ITEM‐RANGE FILTERS ———
        if ($start_item && $end_item) {
            $this->db->where('item.id >=', $start_item);
            $this->db->where('item.id <=', $end_item);
        } elseif ($start_item) {
            $this->db->where('item.id >=', $start_item);
        } elseif ($end_item) {
            $this->db->where('item.id <=', $end_item);
        }
    
        // ——— GROUP & FETCH ———
        $this->db->group_by('item_issue.item_id');
        $item_issues = $this->db->get()->result_array();
    
        // Now get Quantity In (from item_stock), within same date window if dates provided
        foreach ($item_issues as &$item) {
            $this->db->select_sum('quantity');
            $this->db->from('item_stock');
            $this->db->where('item_id', $item['item_id']);
    
            // if your item_stock table has a date field (e.g. `stock_date` or `date`), filter it too:
            if ($from_date) {
                $this->db->where('date >=', $from_date);
            }
            if ($to_date) {
                $this->db->where('date <=', $to_date);
            }
    
            $in_result = $this->db->get()->row();
            $quantity_in = $in_result ? $in_result->quantity : 0;
    
            $item['quantity_in']     = $quantity_in;
            $item['available_qnty']  = $quantity_in - $item['quantity_out'];
        }
    
        return $item_issues;
    }
    

    
    /**
     * This function will delete the record based on the id
     * @param $id
     */
    public function remove($id) {
        $this->db->where('id', $id);
        $this->db->delete('item_issue');
    }

    /**
     * This function will take the post data passed from the controller
     * If id is present, then it will do an update
     * else an insert. One function doing both add and edit.
     * @param $data
     */
    public function add($data) {
        if (isset($data['id'])) {
            $this->db->where('id', $data['id']);
            $this->db->update('item_issue', $data);
        } else {
            $this->db->insert('item_issue', $data);
            return $this->db->insert_id();
        }
    }

}
