accounting_dev_v2/application/controllers/Inventoryprofessional.php

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

class Inventoryprofessional extends MY_Controller
{
    public function __construct()
    {
        parent::__construct();
        $this->load->library(array('ProfessionalInventoryService','CompanyContext'));
        $this->load->model('InventoryModel','inventory');
    }

    private function uid(){return(int)$this->session->userdata('user_id');}
    private function company(){return(int)$this->companycontext->id();}
    private function roleId(){$role=(int)$this->session->userdata('role_id');if($role)return$role;$user=$this->db->select('role_id')->get_where('users',array('id'=>$this->uid()))->row();return$user?(int)$user->role_id:0;}
    private function can($action){return$this->session->userdata('role')==='Admin'||check_permission('items',$action);}
    private function page($view,$active,array$data=array()){$data['active_menu']=$active;$data['inventory_permissions']=$this->permissionMap();$this->load->view('partials/header',$data);$this->load->view($view,$data);$this->load->view('partials/footer');}
    private function permissionMap(){return array('create'=>$this->can('can_create'),'update'=>$this->can('can_update'),'approve'=>$this->can('can_approve'),'post'=>$this->can('can_post'),'export'=>$this->can('can_export')) ;}

    public function index()
    {
        $company=$this->company();$this->professionalinventoryservice->expireReservations();
        $this->page('inventory_professional/dashboard','inventory_overview',array('summary'=>$this->inventory->dashboard($company),'action_items'=>$this->inventory->actionItems($company),'documents'=>$this->inventory->recentDocuments($company),'last_reconciliation'=>$this->db->where('company_id',$company)->order_by('id','DESC')->get('inventory_reconciliations')->row()));
    }

    public function operations()
    {
        $company=$this->company();$warehouses=$this->db->where(array('company_id'=>$company,'is_active'=>1))->order_by('nama')->get('warehouses')->result();
        $this->page('inventory_professional/operations','inventory_operations',array('warehouses'=>$warehouses));
    }

    public function locations()
    {
        $this->page('inventory_professional/locations','inventory_locations');
    }

    public function reports()
    {
        $warehouses=$this->db->where(array('company_id'=>$this->company(),'is_active'=>1))->order_by('nama')->get('warehouses')->result();
        $this->page('inventory_professional/reports','inventory_reports',array('warehouses'=>$warehouses));
    }

    public function document_data()
    {
        $company=$this->company();$draw=(int)$this->input->get('draw');$start=max(0,(int)$this->input->get('start'));$length=min(100,max(10,(int)$this->input->get('length')));$search=trim((string)($this->input->get('search')['value']??''));$type=trim((string)$this->input->get('type'));$status=trim((string)$this->input->get('status'));
        $base=$this->db->from('stock_documents d')->where('d.company_id',$company);if($type==='manual')$base->where_in('d.document_type',array('opening','in','out'));elseif($type==='damage')$base->where_in('d.document_type',array('damaged','lost'));elseif($type!=='')$base->where('d.document_type',$type);if($status!=='')$base->where('d.status',$status);if($search!=='')$base->group_start()->like('d.document_no',$search)->or_like('d.reason',$search)->or_like('d.source_type',$search)->group_end();$filtered=$base->count_all_results('',false);
        $rows=$base->select('d.*,wf.nama from_warehouse,wt.nama to_warehouse,u.nama creator_name,(SELECT COUNT(*) FROM stock_document_lines l WHERE l.stock_document_id=d.id) line_count')->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')->order_by('d.id','DESC')->limit($length,$start)->get()->result();
        $total=$this->db->where('company_id',$company)->count_all_results('stock_documents');return$this->output->set_content_type('application/json')->set_output(json_encode(array('draw'=>$draw,'recordsTotal'=>$total,'recordsFiltered'=>$filtered,'data'=>$rows)));
    }

    public function document_detail($id)
    {
        $doc=$this->inventory->documentDetail((int)$id,$this->company());if(!$doc)return json_response(false,'Dokumen stok tidak ditemukan.',array(),404);
        foreach($doc->attachments as$a){$a->preview_url=base_url('accountingattachments/preview/'.$a->id);$a->download_url=base_url('accountingattachments/download/'.$a->id);}
        return json_response(true,'Detail dokumen stok.',array('document'=>$doc,'permissions'=>$this->permissionMap()));
    }

    public function create()
    {
        if(strtoupper($this->input->method())!=='POST')show_404();
        $this->requirePermission('items','can_create');$lines=json_decode((string)$this->input->post('lines'),true);if(!is_array($lines))return json_response(false,'Detail barang tidak valid.',array(),422);
        try{$id=$this->professionalinventoryservice->createDocument(array('type'=>$this->input->post('type',true),'date'=>$this->input->post('date',true),'from_warehouse_id'=>$this->input->post('from_warehouse_id'),'to_warehouse_id'=>$this->input->post('to_warehouse_id'),'reason'=>$this->input->post('reason',true),'source_type'=>$this->input->post('source_type',true),'source_id'=>$this->input->post('source_id'),'idempotency_key'=>$this->input->post('idempotency_key',true),'lines'=>$lines),$this->uid());return json_response(true,'Dokumen stok berhasil disimpan sebagai draft.',array('document_id'=>$id));}catch(Throwable$e){return business_exception_response($e);}
    }

    public function submit($id){$this->requirePermission('items','can_create');return$this->action(fn()=>$this->professionalinventoryservice->submit($id,$this->uid()),'Dokumen stok berhasil diajukan.');}
    public function approve($id){$this->requirePermission('items','can_approve');return$this->action(fn()=>$this->professionalinventoryservice->approve($id,$this->uid(),$this->roleId()),'Dokumen stok berhasil disetujui.');}
    public function reject($id){$this->requirePermission('items','can_approve');return$this->action(fn()=>$this->professionalinventoryservice->reject($id,$this->uid(),$this->roleId(),$this->input->post('reason',true)),'Dokumen stok ditolak.');}
    public function post($id){$this->requirePermission('items','can_post');return$this->action(fn()=>$this->professionalinventoryservice->post($id,$this->uid()),'Mutasi stok berhasil diposting.');}
    public function reverse($id){$this->requirePermission('items','can_post');return$this->action(fn()=>$this->professionalinventoryservice->reverse($id,$this->input->post('date',true),$this->input->post('reason',true),$this->uid()),'Reversal stok berhasil diposting.');}

    public function lookup_items()
    {
        $q=trim((string)$this->input->get('q'));$warehouse=(int)$this->input->get('warehouse_id');$this->db->select("i.id,CONCAT(i.kode_detail,' - ',i.nama_barang) text,i.nama_barang,i.kode_detail,i.harga_beli,i.allow_decimal,k.tracking_type,k.need_serial_number,COALESCE(s.qty,0) on_hand,COALESCE(r.qty,0) reserved",false)->from('items i')->join('kode_barang k','k.id=i.kode_id','left')->join("(SELECT item_id,warehouse_id,SUM(IF(direction='in',qty,-qty)) qty FROM inventory_ledger GROUP BY item_id,warehouse_id)s",'s.item_id=i.id'.($warehouse?' AND s.warehouse_id='.(int)$warehouse:''),'left')->join("(SELECT item_id,warehouse_id,SUM(qty-released_qty) qty FROM stock_reservations WHERE status IN('active','partially_released') GROUP BY item_id,warehouse_id)r",'r.item_id=i.id'.($warehouse?' AND r.warehouse_id='.(int)$warehouse:''),'left')->where(array('i.company_id'=>$this->company(),'i.status'=>'active'));if($q!=='')$this->db->group_start()->like('i.kode_detail',$q)->or_like('i.nama_barang',$q)->group_end();$rows=$this->db->group_by('i.id')->order_by('i.nama_barang')->limit(25)->get()->result();return json_response(true,'Daftar barang.',array('results'=>$rows));
    }

    public function lookup_barcodes()
    {
        $item=(int)$this->input->get('item_id');$warehouse=(int)$this->input->get('warehouse_id');$q=trim((string)$this->input->get('q'));$this->assertItem($item);
        $this->db->select("b.id,b.barcode,b.serial_number,b.qty_sisa,b.reserved_qty,b.return_reserved_qty,b.status,CONCAT(b.barcode,IF(b.serial_number IS NULL OR b.serial_number='','',CONCAT(' / SN ',b.serial_number)),' - tersedia ',FORMAT(GREATEST(b.qty_sisa-b.reserved_qty-b.return_reserved_qty,0),2,'id_ID')) text",false)->from('item_barcodes b')->join('items i','i.id=b.item_id')->where(array('b.item_id'=>$item,'i.company_id'=>$this->company()))->where_in('b.status',array('available','reserved'))->where('b.qty_sisa >',0)->where('(b.qty_sisa-COALESCE(b.reserved_qty,0)-COALESCE(b.return_reserved_qty,0)) > 0',null,false);
        if($warehouse)$this->db->where('b.warehouse_id',$warehouse);if($q!=='')$this->db->group_start()->like('b.barcode',$q)->or_like('b.serial_number',$q)->group_end();$rows=$this->db->order_by('b.id')->limit(30)->get()->result();return json_response(true,'Daftar barcode.',array('results'=>$rows));
    }

    public function lookup_batches()
    {
        $item=(int)$this->input->get('item_id');$q=trim((string)$this->input->get('q'));$this->db->select("id,CONCAT(batch_no,IF(expiry_date IS NULL,'',CONCAT(' · exp ',DATE_FORMAT(expiry_date,'%d/%m/%Y')))) text,batch_no,expiry_date",false)->where(array('company_id'=>$this->company(),'item_id'=>$item,'is_active'=>1));if($q!=='')$this->db->like('batch_no',$q);$rows=$this->db->order_by('id','DESC')->limit(25)->get('inventory_batches')->result();return json_response(true,'Daftar batch.',array('results'=>$rows));
    }

    public function scan()
    {
        $code=trim((string)$this->input->get('code'));
        if($code==='')return json_response(false,'Barcode atau serial wajib diisi.',array(),422);
        try{
            $select='b.*,i.kode_detail,i.nama_barang,k.unit,w.nama warehouse_name';
            $this->db->from('item_barcodes b')->join('items i','i.id=b.item_id')->join('kode_barang k','k.id=i.kode_id','left')->join('warehouses w','w.id=b.warehouse_id','left');
            if($this->db->table_exists('warehouse_bins')&&$this->db->field_exists('bin_id','item_barcodes')){$select.=',wb.code bin_code';$this->db->join('warehouse_bins wb','wb.id=b.bin_id','left');}else$select.=',NULL bin_code';
            if($this->db->table_exists('inventory_batches')&&$this->db->field_exists('batch_id','item_barcodes')){$select.=',bt.batch_no';$this->db->join('inventory_batches bt','bt.id=b.batch_id','left');}else$select.=',NULL batch_no';
            $row=$this->db->select($select,false)->where('i.company_id',$this->company())->group_start()->where('b.barcode',$code)->or_where('b.serial_number',$code)->group_end()->limit(1)->get()->row();
            if(!$row)return json_response(false,'Barcode atau serial tidak ditemukan pada perusahaan aktif.',array(),404);

            $row->reserved_qty=(float)($row->reserved_qty??0);$row->return_reserved_qty=(float)($row->return_reserved_qty??0);$row->qty_sisa=(float)($row->qty_sisa??0);$row->history=array();
            if($this->db->table_exists('item_movements')&&$this->db->field_exists('barcode_id','item_movements')){
                $type=$this->db->field_exists('movement_type','item_movements')?'m.movement_type':"'Perpindahan'";
                $notes=$this->db->field_exists('notes','item_movements')?'m.notes':"''";
                $created=$this->db->field_exists('created_at','item_movements')?'m.created_at':'NULL';
                $row->history=$this->db->select($type.' movement_type,'.$notes.' notes,'.$created.' created_at',false)->from('item_movements m')->join('items mi','mi.id=m.item_id')->where(array('m.barcode_id'=>$row->id,'mi.company_id'=>$this->company()))->order_by('m.id','DESC')->limit(20)->get()->result();
            }
            if(!$row->history&&$this->db->table_exists('stock_logs')&&$this->db->field_exists('barcode_id','stock_logs')){
                $type=$this->db->field_exists('tipe','stock_logs')?'s.tipe':"'Mutasi Stok'";
                $notes=$this->db->field_exists('keterangan','stock_logs')?'s.keterangan':"''";
                $created=$this->db->field_exists('created_at','stock_logs')?'s.created_at':($this->db->field_exists('tanggal','stock_logs')?'s.tanggal':'NULL');
                $row->history=$this->db->select($type.' movement_type,'.$notes.' notes,'.$created.' created_at',false)->from('stock_logs s')->join('items si','si.id=s.item_id')->where(array('s.barcode_id'=>$row->id,'si.company_id'=>$this->company()))->order_by('s.id','DESC')->limit(20)->get()->result();
            }
            return json_response(true,'Barcode ditemukan.',array('item'=>$row));
        }catch(Throwable$e){return business_exception_response($e);}
    }

    public function reservation_data()
    {
        $rows=$this->db->select('r.*,i.kode_detail,i.nama_barang,w.nama warehouse_name,b.barcode,b.serial_number')->from('stock_reservations r')->join('items i','i.id=r.item_id')->join('warehouses w','w.id=r.warehouse_id')->join('item_barcodes b','b.id=r.barcode_id','left')->where('r.company_id',$this->company())->order_by('r.id','DESC')->limit(300)->get()->result();return json_response(true,'Daftar reservasi.',array('data'=>$rows));
    }

    public function reserve(){if(strtoupper($this->input->method())!=='POST')show_404();$this->requirePermission('items','can_create');try{$id=$this->professionalinventoryservice->reserve($this->input->post('item_id'),$this->input->post('warehouse_id'),$this->input->post('barcode_id'),$this->input->post('qty'),$this->input->post('source_type',true),$this->input->post('source_id'),$this->input->post('expires_at'),$this->uid(),$this->input->post('notes',true));return json_response(true,'Stok berhasil direservasi.',array('reservation_id'=>$id));}catch(Throwable$e){return business_exception_response($e);}}
    public function update_reservation($id){$this->requirePermission('items','can_update');return$this->action(fn()=>$this->professionalinventoryservice->reservationAction($id,$this->input->post('action',true),$this->input->post('qty'),$this->input->post('notes',true),$this->uid()),'Reservasi berhasil diperbarui.');}

    public function master_data()
    {
        $company=$this->company();$warehouses=$this->db->where('company_id',$company)->order_by('nama')->get('warehouses')->result();$bins=$this->db->select('b.*,w.nama warehouse_name')->from('warehouse_bins b')->join('warehouses w','w.id=b.warehouse_id')->where('b.company_id',$company)->order_by('w.nama')->order_by('b.code')->get()->result();$batches=$this->db->select('b.*,i.kode_detail,i.nama_barang,s.name supplier_name')->from('inventory_batches b')->join('items i','i.id=b.item_id')->join('suppliers s','s.id=b.supplier_id','left')->where('b.company_id',$company)->order_by('b.id','DESC')->limit(300)->get()->result();return json_response(true,'Master lokasi persediaan.',array('warehouses'=>$warehouses,'bins'=>$bins,'batches'=>$batches));
    }

    public function save_warehouse(){return$this->masterAction(function(){$id=(int)$this->input->post('id');$data=array('company_id'=>$this->company(),'code'=>strtoupper(trim((string)$this->input->post('code',true))),'nama'=>trim((string)$this->input->post('name',true)),'address'=>trim((string)$this->input->post('address',true)),'is_active'=>$this->input->post('is_active')?1:0);if($data['code']===''||$data['nama']==='')throw new BusinessException('Kode dan nama gudang wajib diisi.');$q=$this->db->where(array('company_id'=>$data['company_id'],'code'=>$data['code']))->where('id !=',$id)->get('warehouses')->row();if($q)throw new BusinessException('Kode gudang sudah digunakan.');if($id){$this->db->where(array('id'=>$id,'company_id'=>$data['company_id']))->update('warehouses',$data);return$id;}$this->db->insert('warehouses',$data);return$this->db->insert_id();},'Gudang berhasil disimpan.');}
    public function save_bin(){return$this->masterAction(function(){$id=(int)$this->input->post('id');$warehouse=(int)$this->input->post('warehouse_id');$this->assertWarehouse($warehouse);$data=array('company_id'=>$this->company(),'warehouse_id'=>$warehouse,'code'=>strtoupper(trim((string)$this->input->post('code',true))),'name'=>trim((string)$this->input->post('name',true)),'is_active'=>$this->input->post('is_active')?1:0,'updated_at'=>date('Y-m-d H:i:s'));if($data['code']==='')throw new BusinessException('Kode rak/bin wajib diisi.');$q=$this->db->where(array('warehouse_id'=>$warehouse,'code'=>$data['code']))->where('id !=',$id)->get('warehouse_bins')->row();if($q)throw new BusinessException('Kode rak sudah digunakan di gudang ini.');if($id){$this->db->where(array('id'=>$id,'company_id'=>$data['company_id']))->update('warehouse_bins',$data);return$id;}$this->db->insert('warehouse_bins',$data);return$this->db->insert_id();},'Lokasi rak berhasil disimpan.');}
    public function save_batch(){return$this->masterAction(function(){$id=(int)$this->input->post('id');$item=(int)$this->input->post('item_id');$this->assertItem($item);$data=array('company_id'=>$this->company(),'item_id'=>$item,'batch_no'=>strtoupper(trim((string)$this->input->post('batch_no',true))),'manufactured_date'=>$this->input->post('manufactured_date')?:null,'expiry_date'=>$this->input->post('expiry_date')?:null,'supplier_id'=>(int)$this->input->post('supplier_id')?:null,'is_active'=>$this->input->post('is_active')?1:0,'updated_at'=>date('Y-m-d H:i:s'));if($data['batch_no']==='')throw new BusinessException('Nomor batch wajib diisi.');if($data['manufactured_date']&&$data['expiry_date']&&$data['expiry_date']<$data['manufactured_date'])throw new BusinessException('Tanggal kedaluwarsa tidak boleh sebelum tanggal produksi.');$q=$this->db->where(array('item_id'=>$item,'batch_no'=>$data['batch_no']))->where('id !=',$id)->get('inventory_batches')->row();if($q)throw new BusinessException('Nomor batch sudah digunakan untuk barang ini.');if($id){$this->db->where(array('id'=>$id,'company_id'=>$data['company_id']))->update('inventory_batches',$data);return$id;}$data['created_at']=date('Y-m-d H:i:s');$this->db->insert('inventory_batches',$data);return$this->db->insert_id();},'Batch berhasil disimpan.');}

    public function report_data()
    {
        try{$type=$this->input->get('type')?:'valuation';$filters=array('warehouse_id'=>(int)$this->input->get('warehouse_id'),'item_id'=>(int)$this->input->get('item_id'),'date_from'=>$this->input->get('date_from')?:date('Y-m-01'),'date_to'=>$this->input->get('date_to')?:date('Y-m-d'),'days'=>max(30,(int)$this->input->get('days')));$rows=$this->buildReport($type,$filters);return json_response(true,'Laporan persediaan.',array('data'=>$rows));}catch(Throwable$e){return business_exception_response($e);}
    }

    public function stock_card($item)
    {
        $this->assertItem((int)$item);$rows=$this->db->select('l.*,w.nama warehouse_name,d.document_no,d.reason')->from('inventory_ledger l')->join('items i','i.id=l.item_id')->join('warehouses w','w.id=l.warehouse_id','left')->join('stock_documents d','d.id=l.document_id AND d.document_type=l.document_type','left')->where(array('l.item_id'=>(int)$item,'i.company_id'=>$this->company()))->order_by('l.movement_date')->order_by('l.id')->get()->result();return json_response(true,'Kartu stok.',array('rows'=>$rows));
    }

    public function reconciliation()
    {
        $this->requirePermission('items','can_post');$date=$this->input->post('date',true)?:date('Y-m-d');try{$result=$this->inventory->reconciliation($this->company(),$date);$this->db->insert('inventory_reconciliations',array('company_id'=>$this->company(),'as_of_date'=>$date,'ledger_value'=>$result['ledger_value'],'stock_log_value'=>$result['stock_log_value'],'gl_value'=>$result['gl_value'],'difference'=>$result['difference'],'barcode_variance_count'=>$result['barcode_variance_count'],'cache_variance_count'=>$result['cache_variance_count'],'status'=>$result['status'],'notes'=>$result['status']==='matched'?'Semua kontrol cocok':'Perlu tindak lanjut; tidak ada jurnal koreksi otomatis.','created_by'=>$this->uid(),'created_at'=>date('Y-m-d H:i:s')));return json_response(true,'Rekonsiliasi selesai tanpa membuat jurnal koreksi.',array('result'=>$result));}catch(Throwable$e){return business_exception_response($e);}
    }

    public function document_pdf($id)
    {
        $this->requirePermission('items','can_export');$doc=$this->inventory->documentDetail((int)$id,$this->company());if(!$doc)show_404();$this->inlinePdf($this->load->view('inventory_professional/document_pdf',array('document'=>$doc),true),'Dokumen-Stok-'.$doc->document_no.'.pdf');
    }

    public function report_pdf()
    {
        $this->requirePermission('items','can_export');$type=$this->input->get('type')?:'valuation';$filters=array('warehouse_id'=>(int)$this->input->get('warehouse_id'),'item_id'=>(int)$this->input->get('item_id'),'date_from'=>$this->input->get('date_from')?:date('Y-m-01'),'date_to'=>$this->input->get('date_to')?:date('Y-m-d'),'days'=>max(30,(int)$this->input->get('days')));$definition=$this->reportDefinition($type);$rows=$this->buildReport($type,$filters);$company=$this->db->get_where('companies',array('id'=>$this->company()))->row();$html=$this->load->view('inventory_professional/report_pdf',array('rows'=>$rows,'filters'=>$filters,'definition'=>$definition,'company'=>$company),true);$this->inlinePdf($html,'Laporan-Persediaan-'.$type.'-'.date('Ymd').'.pdf');
    }

    public function report_excel()
    {
        $this->requirePermission('items','can_export');$type=$this->input->get('type')?:'valuation';$filters=array('warehouse_id'=>(int)$this->input->get('warehouse_id'),'item_id'=>(int)$this->input->get('item_id'),'date_from'=>$this->input->get('date_from')?:date('Y-m-01'),'date_to'=>$this->input->get('date_to')?:date('Y-m-d'),'days'=>max(30,(int)$this->input->get('days')));$definition=$this->reportDefinition($type);$rows=$this->buildReport($type,$filters);$company=$this->db->get_where('companies',array('id'=>$this->company()))->row();
        require_once FCPATH.'vendor/autoload.php';$book=new \PhpOffice\PhpSpreadsheet\Spreadsheet();$sheet=$book->getActiveSheet();$sheet->setTitle('Persediaan');$last=\PhpOffice\PhpSpreadsheet\Cell\Coordinate::stringFromColumnIndex(max(1,count($definition['columns'])));$companyName=$company?($company->name??$company->company_name??'Perusahaan'):'Perusahaan';$sheet->setCellValue('A1',$companyName);$sheet->mergeCells('A1:'.$last.'1');$sheet->setCellValue('A2',$definition['title']);$sheet->mergeCells('A2:'.$last.'2');$sheet->setCellValue('A3','Periode: '.$filters['date_from'].' s.d. '.$filters['date_to']);$sheet->mergeCells('A3:'.$last.'3');$sheet->getStyle('A1:'.$last.'2')->getFont()->setBold(true);
        $column=1;foreach($definition['columns']as$label){$sheet->setCellValueByColumnAndRow($column++,5,$label);} $sheet->getStyle('A5:'.$last.'5')->getFont()->setBold(true);$rowNumber=6;foreach($rows as$row){$column=1;foreach($definition['columns']as$key=>$label){$value=is_array($row)?($row[$key]??null):($row->$key??null);$sheet->setCellValueByColumnAndRow($column++,$rowNumber,$value);} $rowNumber++;}for($column=1;$column<=count($definition['columns']);$column++)$sheet->getColumnDimension(\PhpOffice\PhpSpreadsheet\Cell\Coordinate::stringFromColumnIndex($column))->setAutoSize(true);$sheet->freezePane('A6');$sheet->setAutoFilter('A5:'.$last.max(5,$rowNumber-1));
        while(ob_get_level()>0)ob_end_clean();$filename='Laporan-Persediaan-'.$type.'-'.date('Ymd-His').'.xlsx';header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');header('Content-Disposition: attachment; filename="'.$filename.'"');header('Cache-Control: max-age=0');\PhpOffice\PhpSpreadsheet\IOFactory::createWriter($book,'Xlsx')->save('php://output');$book->disconnectWorksheets();exit;
    }

    private function buildReport($type,array$filters)
    {
        $allowed=array_keys($this->reportDefinition());if(!in_array($type,$allowed,true))throw new BusinessException('Jenis laporan persediaan tidak valid.');$company=$this->company();$warehouse=(int)$filters['warehouse_id'];$item=(int)$filters['item_id'];$from=$filters['date_from'];$to=$filters['date_to'];if($from>$to)throw new BusinessException('Tanggal awal tidak boleh melewati tanggal akhir.');if($item)$this->assertItem($item);
        if($type==='valuation'){$params=array($to,$company);$where='';if($warehouse){$where.=' AND l.warehouse_id=?';$params[]=$warehouse;}if($item){$where.=' AND l.item_id=?';$params[]=$item;}return$this->db->query("SELECT i.id,i.kode_detail,i.nama_barang,w.nama warehouse_name,i.costing_method,i.reorder_point,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 l.movement_date<=? AND i.company_id=? {$where} GROUP BY i.id,i.kode_detail,i.nama_barang,w.id,w.nama,i.costing_method,i.reorder_point ORDER BY i.nama_barang,w.nama",$params)->result();}
        if($type==='stock_card'){if(!$item)throw new BusinessException('Pilih barang untuk menampilkan kartu stok.');$this->db->select("l.*,w.nama warehouse_name,COALESCE(d.document_no,CONCAT(UPPER(REPLACE(l.document_type,'_',' ')),' #',l.document_id)) document_no,d.reason",false)->from('inventory_ledger l')->join('warehouses w','w.id=l.warehouse_id','left')->join('stock_documents d','d.id=l.document_id AND d.document_type=l.document_type','left')->where('l.item_id',$item)->where('l.movement_date >=',$from)->where('l.movement_date <=',$to);if($warehouse)$this->db->where('l.warehouse_id',$warehouse);return$this->db->order_by('l.movement_date')->order_by('l.id')->get()->result();}
        if($type==='location'){$params=array($to,$company);$where='';if($warehouse){$where.=' AND l.warehouse_id=?';$params[]=$warehouse;}if($item){$where.=' AND l.item_id=?';$params[]=$item;}return$this->db->query("SELECT i.kode_detail,i.nama_barang,w.nama warehouse_name,COALESCE(b.code,'Tanpa Rak') bin_code,SUM(IF(l.direction='in',l.qty,-l.qty)) qty FROM inventory_ledger l JOIN items i ON i.id=l.item_id JOIN warehouses w ON w.id=l.warehouse_id LEFT JOIN warehouse_bins b ON b.id=l.bin_id WHERE l.movement_date<=? AND i.company_id=? {$where} GROUP BY i.id,i.kode_detail,i.nama_barang,w.id,w.nama,b.id,b.code HAVING ABS(qty)>.0001 ORDER BY w.nama,bin_code,i.nama_barang",$params)->result();}
        if($type==='barcode'){$this->db->select('b.barcode,b.serial_number,b.status,b.qty_awal,b.qty_sisa,b.reserved_qty,b.return_reserved_qty,i.kode_detail,i.nama_barang,w.nama warehouse_name,wb.code bin_code,bt.batch_no')->from('item_barcodes b')->join('items i','i.id=b.item_id')->join('warehouses w','w.id=b.warehouse_id','left')->join('warehouse_bins wb','wb.id=b.bin_id','left')->join('inventory_batches bt','bt.id=b.batch_id','left')->where('i.company_id',$company);if($warehouse)$this->db->where('b.warehouse_id',$warehouse);if($item)$this->db->where('b.item_id',$item);return$this->db->order_by('b.id','DESC')->limit(5000)->get()->result();}
        if($type==='batch'){$this->db->select('b.batch_no,b.manufactured_date,b.expiry_date,b.is_active,i.kode_detail,i.nama_barang,w.nama warehouse_name,COALESCE(SUM(ib.qty_sisa),0) qty',false)->from('inventory_batches b')->join('items i','i.id=b.item_id')->join('item_barcodes ib','ib.batch_id=b.id','left')->join('warehouses w','w.id=ib.warehouse_id','left')->where('b.company_id',$company);if($warehouse)$this->db->where('ib.warehouse_id',$warehouse);if($item)$this->db->where('b.item_id',$item);return$this->db->group_by(array('b.id','b.batch_no','b.manufactured_date','b.expiry_date','b.is_active','i.kode_detail','i.nama_barang','w.id','w.nama'))->order_by('b.expiry_date')->get()->result();}
        if($type==='reservation'){$this->db->select('r.reservation_no,r.source_type,r.source_id,r.qty,r.released_qty,r.expires_at,r.status,i.kode_detail,i.nama_barang,w.nama warehouse_name,b.barcode,b.serial_number')->from('stock_reservations r')->join('items i','i.id=r.item_id')->join('warehouses w','w.id=r.warehouse_id')->join('item_barcodes b','b.id=r.barcode_id','left')->where('r.company_id',$company);if($warehouse)$this->db->where('r.warehouse_id',$warehouse);if($item)$this->db->where('r.item_id',$item);return$this->db->order_by('r.id','DESC')->get()->result();}
        if($type==='reorder'){$params=array($to,$company);$where='';if($warehouse){$where.=' AND l.warehouse_id=?';$params[]=$warehouse;}if($item){$where.=' AND l.item_id=?';$params[]=$item;}return$this->db->query("SELECT i.kode_detail,i.nama_barang,w.nama warehouse_name,SUM(IF(l.direction='in',l.qty,-l.qty)) qty,i.reorder_point,i.minimum_stock FROM items i JOIN inventory_ledger l ON l.item_id=i.id JOIN warehouses w ON w.id=l.warehouse_id WHERE l.movement_date<=? AND i.company_id=? {$where} GROUP BY i.id,i.kode_detail,i.nama_barang,w.id,w.nama,i.reorder_point,i.minimum_stock HAVING qty<=i.reorder_point ORDER BY qty",$params)->result();}
        if($type==='slow'){$params=array($company,(int)$filters['days']);$where='';if($item){$where.=' AND i.id=?';$params[]=$item;}return$this->db->query("SELECT i.kode_detail,i.nama_barang,i.last_movement_at,DATEDIFF(CURDATE(),COALESCE(DATE(i.last_movement_at),DATE(i.created_at))) idle_days,i.stok FROM items i WHERE i.company_id=? AND DATEDIFF(CURDATE(),COALESCE(DATE(i.last_movement_at),DATE(i.created_at)))>=? {$where} ORDER BY idle_days DESC",$params)->result();}
        if($type==='damage'){$this->db->select('d.id document_id,d.document_no,d.document_date,d.document_type,d.status,d.reason,i.kode_detail,i.nama_barang,w.nama warehouse_name,l.qty,l.unit_cost,(l.qty*l.unit_cost) value',false)->from('stock_documents d')->join('stock_document_lines l','l.stock_document_id=d.id')->join('items i','i.id=l.item_id')->join('warehouses w','w.id=d.from_warehouse_id','left')->where('d.company_id',$company)->where_in('d.document_type',array('damaged','lost'))->where('d.document_date >=',$from)->where('d.document_date <=',$to);if($warehouse)$this->db->where('d.from_warehouse_id',$warehouse);if($item)$this->db->where('l.item_id',$item);return$this->db->order_by('d.id','DESC')->get()->result();}
        if($type==='opname'){$this->db->select('d.id document_id,d.document_no,d.document_date,d.document_type,d.status,d.reason,i.kode_detail,i.nama_barang,w.nama warehouse_name,l.system_qty,l.counted_qty,(l.counted_qty-l.system_qty) difference,l.unit_cost',false)->from('stock_documents d')->join('stock_document_lines l','l.stock_document_id=d.id')->join('items i','i.id=l.item_id')->join('warehouses w','w.id=d.from_warehouse_id','left')->where(array('d.company_id'=>$company,'d.document_type'=>'opname'))->where('d.document_date >=',$from)->where('d.document_date <=',$to);if($warehouse)$this->db->where('d.from_warehouse_id',$warehouse);if($item)$this->db->where('l.item_id',$item);return$this->db->order_by('d.id','DESC')->get()->result();}
        return$this->db->where('company_id',$company)->where('as_of_date >=',$from)->where('as_of_date <=',$to)->order_by('id','DESC')->limit(200)->get('inventory_reconciliations')->result();
    }

    private function reportDefinition($type=null)
    {
        $all=array(
            'valuation'=>array('title'=>'Nilai Persediaan','columns'=>array('kode_detail'=>'Kode','nama_barang'=>'Barang','warehouse_name'=>'Gudang','costing_method'=>'Metode','qty'=>'Qty','inventory_value'=>'Nilai')),
            'stock_card'=>array('title'=>'Kartu Stok','columns'=>array('movement_date'=>'Tanggal','document_no'=>'Dokumen','warehouse_name'=>'Gudang','direction'=>'Arah','qty'=>'Qty','unit_cost'=>'Biaya Satuan','value'=>'Nilai','reason'=>'Keterangan')),
            'location'=>array('title'=>'Stok per Gudang dan Rak','columns'=>array('kode_detail'=>'Kode','nama_barang'=>'Barang','warehouse_name'=>'Gudang','bin_code'=>'Rak/Bin','qty'=>'Qty')),
            'barcode'=>array('title'=>'Riwayat Barcode dan Serial','columns'=>array('kode_detail'=>'Kode','nama_barang'=>'Barang','barcode'=>'Barcode','serial_number'=>'Serial','warehouse_name'=>'Gudang','bin_code'=>'Rak','batch_no'=>'Batch','qty_sisa'=>'Qty','reserved_qty'=>'Reservasi','status'=>'Status')),
            'batch'=>array('title'=>'Batch dan Kedaluwarsa','columns'=>array('kode_detail'=>'Kode','nama_barang'=>'Barang','batch_no'=>'Batch','warehouse_name'=>'Gudang','manufactured_date'=>'Produksi','expiry_date'=>'Kedaluwarsa','qty'=>'Qty')),
            'reservation'=>array('title'=>'Reservasi Stok','columns'=>array('reservation_no'=>'Reservasi','kode_detail'=>'Kode','nama_barang'=>'Barang','warehouse_name'=>'Gudang','source_type'=>'Sumber','source_id'=>'ID Sumber','qty'=>'Qty','released_qty'=>'Diselesaikan','expires_at'=>'Berlaku Sampai','status'=>'Status')),
            'reorder'=>array('title'=>'Stok Minimum dan Reorder','columns'=>array('kode_detail'=>'Kode','nama_barang'=>'Barang','warehouse_name'=>'Gudang','qty'=>'Qty','reorder_point'=>'Reorder Point','minimum_stock'=>'Minimum')),
            'slow'=>array('title'=>'Slow-moving dan Dead Stock','columns'=>array('kode_detail'=>'Kode','nama_barang'=>'Barang','stok'=>'Stok','last_movement_at'=>'Terakhir Bergerak','idle_days'=>'Tidak Bergerak (hari)')),
            'damage'=>array('title'=>'Barang Rusak dan Hilang','columns'=>array('document_no'=>'Dokumen','document_date'=>'Tanggal','document_type'=>'Jenis','kode_detail'=>'Kode','nama_barang'=>'Barang','warehouse_name'=>'Gudang','qty'=>'Qty','value'=>'Nilai','status'=>'Status','reason'=>'Alasan')),
            'opname'=>array('title'=>'Stock Opname','columns'=>array('document_no'=>'Dokumen','document_date'=>'Tanggal','kode_detail'=>'Kode','nama_barang'=>'Barang','warehouse_name'=>'Gudang','system_qty'=>'Stok Sistem','counted_qty'=>'Fisik','difference'=>'Selisih','status'=>'Status')),
            'reconciliation'=>array('title'=>'Rekonsiliasi Persediaan','columns'=>array('as_of_date'=>'Tanggal','ledger_value'=>'Ledger','stock_log_value'=>'Stock Log','gl_value'=>'General Ledger','difference'=>'Selisih','barcode_variance_count'=>'Selisih Barcode','cache_variance_count'=>'Selisih Cache','status'=>'Status'))
        );if($type===null)return$all;if(!isset($all[$type]))throw new BusinessException('Jenis laporan persediaan tidak valid.');return$all[$type];
    }

    private function inlinePdf($html,$filename){require_once FCPATH.'vendor/autoload.php';$pdf=new Dompdf\Dompdf();$pdf->loadHtml($html);$pdf->setPaper('A4','landscape');$pdf->render();$pdf->stream($filename,array('Attachment'=>false));}
    private function action($callable,$message){if(strtoupper($this->input->method())!=='POST')show_404();try{$result=$callable();return json_response(true,$message,array('result'=>$result));}catch(Throwable$e){return business_exception_response($e);}}
    private function masterAction($callable,$message){$this->requirePermission('items','can_update');return$this->action($callable,$message);}
    private function assertWarehouse($id){$row=$this->db->get_where('warehouses',array('id'=>(int)$id,'company_id'=>$this->company()))->row();if(!$row)throw new BusinessException('Gudang tidak ditemukan.');return$row;}
    private function assertItem($id){$row=$this->db->get_where('items',array('id'=>(int)$id,'company_id'=>$this->company()))->row();if(!$row)throw new BusinessException('Barang tidak ditemukan.');return$row;}
}