load->database();$this->load->library('CashBankService');} private function uid(){return(int)$this->session->userdata('user_id');}private function go($ok,$m){$this->session->set_flashdata($ok?'success':'error',$m);redirect('cashbank');} public function index(){$d=array('active_menu'=>'cash_bank');$d['cash_accounts']=$this->db->select('c.*,a.kode_akun,a.nama_akun')->from('cash_accounts c')->join('accounts a','a.id=c.gl_account_id')->where('c.is_active',1)->get()->result();$d['accounts']=$this->db->order_by('kode_akun')->get('accounts')->result();$d['transactions']=$this->db->select('t.*,c.name account_name')->from('cash_transactions t')->join('cash_accounts c','c.id=t.cash_account_id')->order_by('t.transaction_date','DESC')->order_by('t.id','DESC')->limit(100)->get()->result();$d['statements']=$this->db->select('l.*,c.name account_name')->from('bank_statement_lines l')->join('bank_statement_imports i','i.id=l.import_id')->join('cash_accounts c','c.id=i.cash_account_id')->order_by('l.transaction_date','DESC')->limit(100)->get()->result();$d['positions']=$this->db->query("SELECT c.id,c.name,c.account_type,c.opening_balance+COALESCE(SUM(IF(t.status='posted',IF(t.direction='in',t.amount,-t.amount),0)),0) balance FROM cash_accounts c LEFT JOIN cash_transactions t ON t.cash_account_id=c.id GROUP BY c.id")->result();$this->load->view('partials/header',$d);$this->load->view('cash_bank/index',$d);$this->load->view('partials/footer');} public function transaction(){try{$r=$this->cashbankservice->post(array('transaction_date'=>$this->input->post('date'),'cash_account_id'=>(int)$this->input->post('cash_account_id'),'transaction_type'=>$this->input->post('type'),'direction'=>$this->input->post('direction'),'amount'=>(float)$this->input->post('amount'),'counter_account_id'=>(int)$this->input->post('counter_account_id'),'reference_no'=>$this->input->post('reference_no',true),'description'=>$this->input->post('description',true),'idempotency_key'=>$this->input->post('idempotency_key',true)),$this->uid());$this->go(true,'Transaksi '.$r['transaction_no'].' tersimpan dengan status '.$r['status'].'.');}catch(Throwable$e){$this->go(false,$e->getMessage());}} public function approve($id){$this->requireAdmin();try{$this->cashbankservice->approve($id,$this->uid());$this->go(true,'Pembayaran disetujui dan diposting.');}catch(Throwable$e){$this->go(false,$e->getMessage());}} public function transfer(){try{$this->cashbankservice->transfer((int)$this->input->post('from_id'),(int)$this->input->post('to_id'),$this->input->post('date'),(float)$this->input->post('amount'),(float)$this->input->post('charge'),(int)$this->input->post('charge_account_id'),$this->input->post('reference_no',true),$this->uid(),$this->input->post('idempotency_key',true));$this->go(true,'Transfer berhasil diposting.');}catch(Throwable$e){$this->go(false,$e->getMessage());}} public function import(){try{if(empty($_FILES['statement']['tmp_name']))throw new BusinessException('File CSV wajib dipilih.');$hash=hash_file('sha256',$_FILES['statement']['tmp_name']);$cid=(int)$this->input->post('cash_account_id');if($this->db->get_where('bank_statement_imports',array('cash_account_id'=>$cid,'file_hash'=>$hash))->row())throw new BusinessException('File ini sudah pernah diimpor.');$this->db->insert('bank_statement_imports',array('cash_account_id'=>$cid,'file_name'=>$_FILES['statement']['name'],'file_hash'=>$hash,'created_by'=>$this->uid(),'created_at'=>date('Y-m-d H:i:s')));$iid=$this->db->insert_id();$h=fopen($_FILES['statement']['tmp_name'],'r');$n=0;while(($r=fgetcsv($h,0,','))!==false){if(!$n++&&strtotime($r[0])===false)continue;if(count($r)<4)continue;$this->db->insert('bank_statement_lines',array('import_id'=>$iid,'line_no'=>$n,'transaction_date'=>date('Y-m-d',strtotime($r[0])),'description'=>$r[1],'reference_no'=>isset($r[2])?$r[2]:null,'debit'=>(float)$r[3],'credit'=>isset($r[4])?(float)$r[4]:0,'balance'=>isset($r[5])?(float)$r[5]:null));}fclose($h);$this->autoMatch($iid);$this->go(true,($n-1).' baris diimpor.');}catch(Throwable$e){$this->go(false,$e->getMessage());}} private function autoMatch($iid){$imp=$this->db->get_where('bank_statement_imports',array('id'=>$iid))->row();foreach($this->db->get_where('bank_statement_lines',array('import_id'=>$iid))->result()as$l){$amount=$l->credit>0?$l->credit:$l->debit;$dir=$l->credit>0?'in':'out';$tx=$this->db->query("SELECT * FROM cash_transactions WHERE cash_account_id=? AND direction=? AND status='posted' AND ABS(amount-?)<0.01 AND transaction_date BETWEEN DATE_SUB(?,INTERVAL 3 DAY) AND DATE_ADD(?,INTERVAL 3 DAY) AND id NOT IN(SELECT matched_cash_transaction_id FROM bank_statement_lines WHERE matched_cash_transaction_id IS NOT NULL) ORDER BY IF(reference_no=?,0,1),ABS(DATEDIFF(transaction_date,?)) LIMIT 1",array($imp->cash_account_id,$dir,$amount,$l->transaction_date,$l->transaction_date,$l->reference_no,$l->transaction_date))->row();if($tx)$this->db->where('id',$l->id)->update('bank_statement_lines',array('match_status'=>'suggested','matched_cash_transaction_id'=>$tx->id,'match_score'=>$tx->reference_no===$l->reference_no?100:85));}} public function match($line){$l=$this->db->get_where('bank_statement_lines',array('id'=>$line))->row();if(!$l||!$l->matched_cash_transaction_id)return$this->go(false,'Pasangan belum tersedia.');$this->db->where('id',$line)->update('bank_statement_lines',array('match_status'=>'matched'));$this->go(true,'Mutasi berhasil dicocokkan.');} public function reconcile(){$id=(int)$this->input->post('cash_account_id');$date=$this->input->post('period_end');$row=$this->db->query("SELECT c.opening_balance+COALESCE(SUM(IF(t.direction='in',t.amount,-t.amount)),0)t FROM cash_accounts c LEFT JOIN cash_transactions t ON t.cash_account_id=c.id AND t.status='posted' AND t.transaction_date<=? WHERE c.id=? GROUP BY c.id",array($date,$id))->row();$book=$row?(float)$row->t:0;$statement=(float)$this->input->post('statement_balance');$or=(float)$this->input->post('outstanding_receipts');$op=(float)$this->input->post('outstanding_payments');$diff=round(($statement+$or-$op)-$book,2);$this->db->insert('bank_reconciliations',array('reconciliation_no'=>'BRC-'.date('YmdHis'),'cash_account_id'=>$id,'period_end'=>$date,'book_balance'=>$book,'statement_balance'=>$statement,'outstanding_receipts'=>$or,'outstanding_payments'=>$op,'difference'=>$diff,'status'=>abs($diff)<.01?'balanced':'variance','notes'=>$this->input->post('notes',true),'created_by'=>$this->uid()));$this->go(true,'Rekonsiliasi disimpan. Selisih Rp '.number_format($diff,2,',','.'));} }