accounting_dev_v2/application/models/InventoryModel.php

<?php
defined('BASEPATH') OR exit('No direct script access allowed');

class InventoryModel extends CI_Model
{
    public function calculatedStock($itemId)
    {
        $row=$this->db->query("SELECT COALESCE(SUM(IF(direction='in',qty,-qty)),0) stok FROM inventory_ledger WHERE item_id=?",array((int)$itemId))->row();
        return $row?(float)$row->stok:0.0;
    }

    public function synchronizeStock($itemId)
    {
        $stock=$this->calculatedStock($itemId);
        if(!$this->db->where('id',(int)$itemId)->update('items',array('stok'=>$stock)))throw new RuntimeException('Gagal menyinkronkan cache stok barang.');
        return $stock;
    }

    public function synchronizeCompany($companyId)
    {
        $items=$this->db->select('id')->where('company_id',(int)$companyId)->get('items')->result();
        foreach($items as$item)$this->synchronizeStock($item->id);
        return count($items);
    }

    public function dashboard($companyId)
    {
        $companyId=(int)$companyId;
        $row=$this->db->query("SELECT COUNT(CASE WHEN i.status='active' THEN 1 END) active_items,COUNT(CASE WHEN i.status='draft' OR EXISTS(SELECT 1 FROM item_barcodes ib WHERE ib.item_id=i.id AND ib.status='pending') THEN 1 END) pending_items,COALESCE(SUM(CASE WHEN x.qty>0 THEN x.value ELSE 0 END),0) inventory_value,COUNT(CASE WHEN x.qty<=i.reorder_point THEN 1 END) reorder_items FROM items i LEFT JOIN(SELECT item_id,SUM(IF(direction='in',qty,-qty)) qty,SUM(IF(direction='in',value,-value)) value FROM inventory_ledger GROUP BY item_id)x ON x.item_id=i.id WHERE i.company_id=?",array($companyId))->row();
        $row->open_documents=(int)$this->db->where('company_id',$companyId)->where_in('status',array('draft','submitted','approved'))->count_all_results('stock_documents');
        $row->open_reservations=(int)$this->db->where('company_id',$companyId)->where_in('status',array('active','partially_released'))->count_all_results('stock_reservations');
        $row->expiring_batches=(int)$this->db->where('company_id',$companyId)->where('is_active',1)->where('expiry_date IS NOT NULL',null,false)->where('expiry_date <=',date('Y-m-d',strtotime('+30 days')))->count_all_results('inventory_batches');
        return$row;
    }

    public function recentDocuments($companyId,$limit=8)
    {
        return$this->db->select('d.*,wf.nama from_warehouse,wt.nama to_warehouse,u.nama creator_name')->from('stock_documents d')->join('warehouses wf','wf.id=d.from_warehouse_id','left')->join('warehouses wt','wt.id=d.to_warehouse_id','left')->join('users u','u.id=d.created_by','left')->where('d.company_id',(int)$companyId)->order_by('d.id','DESC')->limit((int)$limit)->get()->result();
    }

    public function actionItems($companyId,$limit=8)
    {
        return$this->db->query("SELECT i.id,i.kode_detail,i.nama_barang,i.harga_beli,i.harga_jual,COUNT(DISTINCT CASE WHEN ib.status='pending' THEN ib.id END) pending_barcodes,COUNT(DISTINCT CASE WHEN ib.status='pending' AND (kb.need_serial_number<>'true' OR NULLIF(ib.serial_number,'') IS NOT NULL) THEN ib.id END) ready_barcodes FROM items i LEFT JOIN kode_barang kb ON kb.id=i.kode_id LEFT JOIN item_barcodes ib ON ib.item_id=i.id WHERE i.company_id=? AND EXISTS(SELECT 1 FROM goods_receipt_lines grl WHERE grl.item_id=i.id) AND(i.status='draft' OR ib.status='pending') GROUP BY i.id,i.kode_detail,i.nama_barang,i.harga_beli,i.harga_jual ORDER BY i.id DESC LIMIT ".(int)$limit,array((int)$companyId))->result();
    }

    public function documentDetail($id,$companyId)
    {
        $d=$this->db->select('d.*,c.name company_name,c.company_code,wf.nama from_warehouse,wt.nama to_warehouse,uc.nama creator_name,us.nama submitter_name,ua.nama approver_name,up.nama poster_name,ur.nama rejecter_name')->from('stock_documents d')->join('companies c','c.id=d.company_id','left')->join('warehouses wf','wf.id=d.from_warehouse_id','left')->join('warehouses wt','wt.id=d.to_warehouse_id','left')->join('users uc','uc.id=d.created_by','left')->join('users us','us.id=d.submitted_by','left')->join('users ua','ua.id=d.approved_by','left')->join('users up','up.id=d.posted_by','left')->join('users ur','ur.id=d.rejected_by','left')->where(array('d.id'=>(int)$id,'d.company_id'=>(int)$companyId))->get()->row();
        if(!$d)return null;
        $d->lines=$this->db->select('l.*,i.kode_detail,i.nama_barang,k.unit,k.tracking_type,k.need_serial_number,b.barcode,b.serial_number,bt.batch_no,fb.code from_bin,tb.code to_bin')->from('stock_document_lines l')->join('items i','i.id=l.item_id')->join('kode_barang k','k.id=i.kode_id','left')->join('item_barcodes b','b.id=l.barcode_id','left')->join('inventory_batches bt','bt.id=l.batch_id','left')->join('warehouse_bins fb','fb.id=l.from_bin_id','left')->join('warehouse_bins tb','tb.id=l.to_bin_id','left')->where('l.stock_document_id',$d->id)->order_by('l.line_no')->order_by('l.id')->get()->result();
        $d->history=$this->db->select('h.*,u.nama user_name')->from('stock_document_status_history h')->join('users u','u.id=h.user_id','left')->where('h.stock_document_id',$d->id)->order_by('h.id')->get()->result();
        $d->attachments=$this->db->where(array('entity_type'=>'stock_document','entity_id'=>$d->id))->order_by('id')->get('transaction_attachments')->result();
        return$d;
    }

    public function valuation($companyId,array$filters=array())
    {
        $params=array((int)$companyId);$where='';
        if(!empty($filters['warehouse_id'])){$where.=' AND l.warehouse_id=?';$params[]=(int)$filters['warehouse_id'];}
        if(!empty($filters['item_id'])){$where.=' AND l.item_id=?';$params[]=(int)$filters['item_id'];}
        return$this->db->query("SELECT i.id,i.kode_detail,i.nama_barang,l.warehouse_id,w.nama warehouse_name,i.costing_method,i.minimum_stock,i.reorder_point,i.last_movement_at,SUM(IF(l.direction='in',l.qty,-l.qty)) qty,SUM(IF(l.direction='in',l.value,-l.value)) inventory_value FROM inventory_ledger l JOIN items i ON i.id=l.item_id JOIN warehouses w ON w.id=l.warehouse_id WHERE i.company_id=? {$where} GROUP BY i.id,i.kode_detail,i.nama_barang,l.warehouse_id,w.nama,i.costing_method,i.minimum_stock,i.reorder_point,i.last_movement_at ORDER BY i.nama_barang,w.nama",$params)->result();
    }

    public function reconciliation($companyId,$asOf)
    {
        $companyId=(int)$companyId;
        $ledger=(float)$this->db->query("SELECT COALESCE(SUM(IF(l.direction='in',l.value,-l.value)),0) total FROM inventory_ledger l JOIN items i ON i.id=l.item_id WHERE i.company_id=? AND l.movement_date<=?",array($companyId,$asOf))->row()->total;
        $stockLog=(float)$this->db->query("SELECT COALESCE(SUM(IF(s.tipe='masuk',s.qty*COALESCE(s.unit_cost,0),-s.qty*COALESCE(s.unit_cost,0))),0) total FROM stock_logs s JOIN items i ON i.id=s.item_id WHERE i.company_id=? AND DATE(s.created_at)<=?",array($companyId,$asOf))->row()->total;
        $mapping=$this->db->where(array('mapping_key'=>'inventory','is_active'=>1))->get('system_account_mappings')->row();$gl=0;
        if($mapping)$gl=(float)$this->db->query("SELECT COALESCE(SUM(d.debit-d.kredit),0) total FROM journals j JOIN journal_details d ON d.journal_id=j.id WHERE d.account_id=? AND j.company_id=? AND j.tanggal<=? AND j.status IN('posted','reversed')",array($mapping->account_id,$companyId,$asOf))->row()->total;
        $cache=(int)$this->db->query("SELECT COUNT(*) total FROM items i LEFT JOIN(SELECT item_id,SUM(IF(direction='in',qty,-qty)) qty FROM inventory_ledger WHERE movement_date<=? GROUP BY item_id)l ON l.item_id=i.id WHERE i.company_id=? AND ABS(i.stok-COALESCE(l.qty,0))>.0001",array($asOf,$companyId))->row()->total;
        $barcode=(int)$this->db->query("SELECT COUNT(*) total FROM(SELECT l.item_id,l.warehouse_id,l.qty-COALESCE(b.qty,0) variance FROM(SELECT il.item_id,il.warehouse_id,SUM(IF(il.direction='in',il.qty,-il.qty)) qty FROM inventory_ledger il JOIN items i ON i.id=il.item_id WHERE i.company_id=? AND il.movement_date<=? GROUP BY il.item_id,il.warehouse_id)l LEFT JOIN(SELECT item_id,warehouse_id,SUM(qty_sisa) qty FROM item_barcodes GROUP BY item_id,warehouse_id)b ON b.item_id=l.item_id AND b.warehouse_id=l.warehouse_id HAVING ABS(variance)>.0001)x",array($companyId,$asOf))->row()->total;
        $details=array();
        $details['ledger_vs_stock_log']=$this->db->query("SELECT i.id item_id,i.kode_detail,i.nama_barang,w.nama warehouse_name,COALESCE(l.qty,0) ledger_qty,COALESCE(s.qty,0) stock_log_qty,COALESCE(l.inventory_value,0) ledger_value,COALESCE(s.stock_log_value,0) stock_log_value,COALESCE(l.qty,0)-COALESCE(s.qty,0) qty_difference,COALESCE(l.inventory_value,0)-COALESCE(s.stock_log_value,0) value_difference FROM(SELECT item_id,warehouse_id FROM inventory_ledger WHERE movement_date<=? UNION SELECT item_id,COALESCE(warehouse_id,0) FROM stock_logs WHERE DATE(created_at)<=?)p JOIN items i ON i.id=p.item_id LEFT JOIN warehouses w ON w.id=p.warehouse_id LEFT JOIN(SELECT item_id,warehouse_id,SUM(IF(direction='in',qty,-qty)) qty,SUM(IF(direction='in',value,-value)) inventory_value FROM inventory_ledger WHERE movement_date<=? GROUP BY item_id,warehouse_id)l ON l.item_id=p.item_id AND l.warehouse_id=p.warehouse_id LEFT JOIN(SELECT item_id,COALESCE(warehouse_id,0) warehouse_id,SUM(IF(tipe='masuk',qty,-qty)) qty,SUM(IF(tipe='masuk',qty*COALESCE(unit_cost,0),-qty*COALESCE(unit_cost,0))) stock_log_value FROM stock_logs WHERE DATE(created_at)<=? GROUP BY item_id,COALESCE(warehouse_id,0))s ON s.item_id=p.item_id AND s.warehouse_id=p.warehouse_id WHERE i.company_id=? HAVING ABS(qty_difference)>.0001 OR ABS(value_difference)>.01 ORDER BY ABS(value_difference) DESC,ABS(qty_difference) DESC LIMIT 50",array($asOf,$asOf,$asOf,$asOf,$companyId))->result();
        $details['barcode']=$this->db->query("SELECT i.id item_id,i.kode_detail,i.nama_barang,w.nama warehouse_name,l.qty ledger_qty,COALESCE(b.qty,0) barcode_qty,l.qty-COALESCE(b.qty,0) difference FROM(SELECT il.item_id,il.warehouse_id,SUM(IF(il.direction='in',il.qty,-il.qty)) qty FROM inventory_ledger il WHERE il.movement_date<=? GROUP BY il.item_id,il.warehouse_id)l JOIN items i ON i.id=l.item_id LEFT JOIN warehouses w ON w.id=l.warehouse_id LEFT JOIN(SELECT item_id,warehouse_id,SUM(qty_sisa) qty FROM item_barcodes GROUP BY item_id,warehouse_id)b ON b.item_id=l.item_id AND b.warehouse_id=l.warehouse_id WHERE i.company_id=? HAVING ABS(difference)>.0001 ORDER BY ABS(difference) DESC LIMIT 50",array($asOf,$companyId))->result();
        $details['cache']=$this->db->query("SELECT i.id item_id,i.kode_detail,i.nama_barang,i.stok cache_qty,COALESCE(l.qty,0) ledger_qty,i.stok-COALESCE(l.qty,0) difference FROM items i LEFT JOIN(SELECT item_id,SUM(IF(direction='in',qty,-qty)) qty FROM inventory_ledger WHERE movement_date<=? GROUP BY item_id)l ON l.item_id=i.id WHERE i.company_id=? HAVING ABS(difference)>.0001 ORDER BY ABS(difference) DESC LIMIT 50",array($asOf,$companyId))->result();
        return array('ledger_value'=>$ledger,'stock_log_value'=>$stockLog,'gl_value'=>$gl,'difference'=>$ledger-$gl,'cache_variance_count'=>$cache,'barcode_variance_count'=>$barcode,'status'=>abs($ledger-$gl)<.01&&abs($ledger-$stockLog)<.01&&$cache===0&&$barcode===0?'matched':'variance','details'=>$details);
    }
}