Wian Drs
39 lines
7.4 KiB
PHP
39 lines
7.4 KiB
PHP
<?php
|
|
defined('BASEPATH') OR exit('No direct script access allowed');
|
|
class Inventorycheck extends CI_Controller
|
|
{
|
|
public function __construct(){parent::__construct();if(!is_cli())show_404();}
|
|
public function integrity(){
|
|
$checks=array(
|
|
'duplicate_barcodes'=>"SELECT COUNT(*) total FROM(SELECT barcode FROM item_barcodes GROUP BY barcode HAVING COUNT(*)>1)x",
|
|
'duplicate_ledger_idempotency'=>"SELECT COUNT(*) total FROM(SELECT idempotency_key FROM inventory_ledger GROUP BY idempotency_key HAVING COUNT(*)>1)x",
|
|
'negative_ledger_stock'=>"SELECT COUNT(*) total FROM(SELECT item_id,warehouse_id,SUM(IF(direction='in',qty,-qty)) qty FROM inventory_ledger GROUP BY item_id,warehouse_id HAVING qty<-.0001)x",
|
|
'ledger_vs_legacy_qty'=>"SELECT COUNT(*) total FROM(SELECT l.item_id,l.warehouse_id,l.qty-COALESCE(s.qty,0) diff FROM(SELECT item_id,warehouse_id,SUM(IF(direction='in',qty,-qty)) qty FROM inventory_ledger GROUP BY item_id,warehouse_id)l LEFT JOIN(SELECT item_id,COALESCE(warehouse_id,0) warehouse_id,SUM(IF(tipe='masuk',qty,-qty)) qty FROM stock_logs GROUP BY item_id,COALESCE(warehouse_id,0))s ON s.item_id=l.item_id AND s.warehouse_id=l.warehouse_id HAVING ABS(diff)>.0001)x",
|
|
'item_cache_vs_ledger'=>"SELECT COUNT(*) total FROM(SELECT i.id,i.stok-COALESCE(l.qty,0) diff FROM items i LEFT JOIN(SELECT item_id,SUM(IF(direction='in',qty,-qty)) qty FROM inventory_ledger GROUP BY item_id)l ON l.item_id=i.id HAVING ABS(diff)>.0001)x",
|
|
'barcode_reserved_over_qty'=>"SELECT COUNT(*) total FROM item_barcodes WHERE reserved_qty>qty_sisa+.0001",
|
|
'over_released_reservation'=>"SELECT COUNT(*) total FROM stock_reservations WHERE released_qty>qty+.0001",
|
|
'duplicate_active_technician_barcode'=>"SELECT COUNT(*) total FROM(SELECT barcode FROM item_technician WHERE status='active' GROUP BY barcode HAVING COUNT(*)>1)x",
|
|
'technician_barcode_not_installed'=>"SELECT COUNT(*) total FROM item_technician it JOIN item_barcodes ib ON ib.barcode=it.barcode WHERE it.status='active' AND ib.status<>'installed'",
|
|
'cross_company_technician_item'=>"SELECT COUNT(*) total FROM item_technician it JOIN items i ON i.id=it.item_id JOIN k_employees e ON e.id=it.user_id WHERE it.status='active' AND i.company_id<>e.company_id",
|
|
'posted_doc_without_ledger'=>"SELECT COUNT(*) total FROM stock_documents d LEFT JOIN inventory_ledger l ON l.document_id=d.id AND l.document_type=d.document_type WHERE d.status='posted' AND l.id IS NULL AND NOT(d.document_type='opname' AND NOT EXISTS(SELECT 1 FROM stock_document_lines x WHERE x.stock_document_id=d.id AND ABS(x.counted_qty-x.system_qty)>.0001))"
|
|
);$fail=0;foreach($checks as$n=>$q){$v=(int)$this->db->query($q)->row()->total;echo($v?'[FAIL] ':'[OK] ').$n.'='.$v."\n";if($v)$fail++;}if($fail)exit(1);
|
|
}
|
|
public function reconciliation(){$ledger=(float)$this->db->query("SELECT COALESCE(SUM(IF(direction='in',value,-value)),0) total FROM inventory_ledger")->row()->total;$m=$this->db->get_where('system_account_mappings',array('mapping_key'=>'inventory','is_active'=>1))->row();$gl=(float)$this->db->query("SELECT COALESCE(SUM(d.debit-d.kredit),0) total FROM journal_details d JOIN journals j ON j.id=d.journal_id WHERE d.account_id=? AND j.status IN('posted','reversed')",array($m->account_id))->row()->total;echo'ledger_value='.number_format($ledger,2,'.','')."\n".'gl_value='.number_format($gl,2,'.','')."\n".'difference='.number_format($ledger-$gl,2,'.','')."\n";if(abs($ledger-$gl)>.01)exit(1);}
|
|
public function trigger_test(){$row=$this->db->query("SELECT item_id,warehouse_id,SUM(IF(direction='in',qty,-qty)) qty FROM inventory_ledger GROUP BY item_id,warehouse_id HAVING qty>=0 LIMIT 1")->row();if(!$row){echo"[SKIP] no stock row\n";return;}$this->db->trans_begin();$old=$this->db->db_debug;$this->db->db_debug=false;$ok=$this->db->insert('stock_logs',array('item_id'=>$row->item_id,'warehouse_id'=>$row->warehouse_id,'qty'=>$row->qty+999999,'tipe'=>'keluar','keterangan'=>'trigger test','ref_type'=>'test','idempotency_key'=>'TEST-NEGATIVE-'.uniqid()));$this->db->db_debug=$old;$this->db->trans_rollback();echo(!$ok?'[OK] ':'[FAIL] ')."negative_stock_trigger\n";if($ok)exit(1);}
|
|
public function professional(){
|
|
$company=$this->db->select_min('id')->get('companies')->row();if(!$company){fwrite(STDERR,"Company development tidak tersedia.\n");exit(1);}$company=(int)$company->id;$this->load->model('InventoryModel','inventory');
|
|
$required=array('stock_documents'=>array('company_id','source_type','submitted_by','reversal_of_id'),'stock_document_lines'=>array('line_no','movement_direction'),'stock_reservations'=>array('company_id','completed_at'),'stock_logs'=>array('company_id','unit_cost','idempotency_key'),'warehouses'=>array('code','is_active'),'kode_barang'=>array('company_id','is_active'));
|
|
$missing=array();foreach($required as$table=>$columns)foreach($columns as$column)if(!$this->db->field_exists($column,$table))$missing[]=$table.'.'.$column;
|
|
$orphans=(int)$this->db->query("SELECT COUNT(*) total FROM stock_document_lines l LEFT JOIN stock_documents d ON d.id=l.stock_document_id LEFT JOIN items i ON i.id=l.item_id WHERE d.id IS NULL OR i.id IS NULL")->row()->total;
|
|
$wrongCompany=(int)$this->db->query("SELECT COUNT(DISTINCT d.id) total FROM stock_documents d JOIN stock_document_lines l ON l.stock_document_id=d.id JOIN items i ON i.id=l.item_id WHERE d.company_id<>i.company_id")->row()->total;
|
|
$duplicateNumbers=(int)$this->db->query("SELECT COUNT(*) total FROM(SELECT company_id,document_no FROM stock_documents GROUP BY company_id,document_no HAVING COUNT(*)>1)x")->row()->total;
|
|
$dashboard=$this->inventory->dashboard($company);$valuation=$this->inventory->valuation($company);$reconciliation=$this->inventory->reconciliation($company,date('Y-m-d'));
|
|
$ok=!$missing&&$orphans===0&&$wrongCompany===0&&$duplicateNumbers===0;$payload=array('status'=>$ok?'ok':'failed','checks'=>array('missing_columns'=>$missing,'orphan_lines'=>$orphans,'cross_company_documents'=>$wrongCompany,'duplicate_document_numbers'=>$duplicateNumbers),'dashboard'=>$dashboard,'valuation_rows'=>count($valuation),'reconciliation'=>$reconciliation);echo json_encode($payload,JSON_PRETTY_PRINT|JSON_UNESCAPED_UNICODE)."\n";if(!$ok)exit(1);
|
|
}
|
|
public function anomalies(){
|
|
$barcode=$this->db->query("SELECT l.item_id,i.kode_detail,i.nama_barang,l.warehouse_id,l.qty,COALESCE(b.qty,0) barcode_qty,l.qty-COALESCE(b.qty,0) difference FROM(SELECT item_id,warehouse_id,SUM(IF(direction='in',qty,-qty)) qty FROM inventory_ledger GROUP BY item_id,warehouse_id)l JOIN items i ON i.id=l.item_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 HAVING ABS(difference)>.0001 ORDER BY ABS(difference) DESC")->result();
|
|
$technician=$this->db->query("SELECT it.barcode,it.item_id,i.kode_detail,i.nama_barang,it.user_id,e.full_name technician_name,ib.status barcode_status,ib.qty_sisa FROM item_technician it JOIN item_barcodes ib ON ib.barcode=it.barcode JOIN items i ON i.id=it.item_id LEFT JOIN k_employees e ON e.id=it.user_id WHERE it.status='active' AND ib.status<>'installed'")->result();
|
|
$payload=array('items_without_company'=>(int)$this->db->where('company_id IS NULL',null,false)->count_all_results('items'),'stock_logs_without_cost'=>(int)$this->db->group_start()->where('unit_cost IS NULL',null,false)->or_where('unit_cost',0)->group_end()->count_all_results('stock_logs'),'barcode_variances'=>$barcode,'technician_status_variances'=>$technician);echo json_encode($payload,JSON_PRETTY_PRINT|JSON_UNESCAPED_UNICODE)."\n";
|
|
}
|
|
}
|