<?php
class M_bank_reconcile extends CI_Model {
    public $table               = "at_bankreconcille";
    public $detail              = "at_brendetail";

    public $company_gl          = 'at_compaccountgl';
    public $account_gl          = 'am_accountgl';
    
    public $bank                = 'am_bank';
    public $branch              = "am_branch";
    public $currency            = 'am_currency';

    public $trans_bank          = 'av_all_transaction';
    //public $trans_bank          = 'av_reconcile';
    
    public $purchase_payment    = 'at_purchasepayment';
    public $sales_receipt       = 'at_salesreceipt';
    public $cash_deposit        = 'at_cashdeposit';
    
    function get_all(){
        $param              = $this->input->post('param');
        
        $bank_reconcile     = isset($param['cmb_Bank']) ? $param['cmb_Bank'] : '';
        $bren_date_from     = isset($param['txt_DateFrom']) ? $param['txt_DateFrom'] : '';
        $bren_date_to       = isset($param['txt_DateTo']) ? $param['txt_DateTo'] : '';
        $branch_code        = $this->session->userdata('branch');
        
        $this->db->distinct();
        $this->db->select("bren.*, br.BranchName, cy.CurrencyName, ag.AccountName, emp.EmployeeName as CreatedBy,
                          (case 
                            when bren.TransStatus = 1 then 'On Progress'
                            when bren.TransStatus = 2 then 'Finished'
                            when bren.TransStatus = 3 then 'Suspend'
                            when bren.TransStatus = 4 then 'Need App. Mgr.'
                            when bren.TransStatus = 5 then 'Need App. Dir.'
                            when bren.TransStatus = 6 then 'Need App. Dir. & Mgr.'
                            when bren.TransStatus = 9 then 'Canceled by Dir.'
                            else 'Unknown' end) as TransStatus");
        $this->db->from($this->table . ' bren');
        //$this->db->join($this->detail . ' br_det', 'br_det.BRenNumber = bren.BRenNumber',  'INNER');
        $this->db->join($this->account_gl . ' ag', 'ag.AccountNumber = bren.BankAccount', 'INNER');
        $this->db->join($this->currency . ' cy', 'cy.CurrencyCode = ag.CurrencyCode', 'INNER');
        $this->db->join($this->branch . ' br','br.BranchCode = bren.BranchCode','INNER');
        $this->db->join('am_employee as  emp','emp.EmployeeCode = bren.ReconCreatedBy','INNER');
        
        if(!empty($bank_reconcile)){ 
            $this->db->where('bren.BankAccount', $bank_reconcile);            
        }
        
        if($bren_date_from != '' && $bren_date_to != '' ){
            $this->db->where('bren.ReconcileDate >= ', date("Y-m-d", strtotime($bren_date_from)));
            $this->db->where('bren.ReconcileDate <= ', date("Y-m-d", strtotime($bren_date_to)));
        }
	
        $this->db->where('bren.BranchCode', $branch_code);
        $this->db->order_by('bren.BranchCode', 'ASC');
        $this->db->order_by('bren.ReconcileID', 'ASC');
        $this->db->order_by('bren.ReconcileDate', 'DESC');
        $query = $this->db->get();
        
        return $query->result();
    }

    function get_all_to_print($data_filter){
        $branch_code        = $this->session->userdata('branch');
        $bank_reconcile     = $data_filter['cmb_Bank'];
        $bren_date_from     = $data_filter['txt_DateFrom'];
        $bren_date_to       = $data_filter['txt_DateTo'];
        
        $this->db->distinct();
        $this->db->select("bren.*, br.BranchName, cy.CurrencyName, ag.AccountName, emp.EmployeeName as CreatedBy,
                          (case 
                            when bren.TransStatus = 1 then 'On Progress'
                            when bren.TransStatus = 2 then 'Finished'
                            when bren.TransStatus = 3 then 'Suspend'
                            when bren.TransStatus = 4 then 'Need App. Mgr.'
                            when bren.TransStatus = 5 then 'Need App. Dir.'
                            when bren.TransStatus = 6 then 'Need App. Dir. & Mgr.'
                            when bren.TransStatus = 9 then 'Canceled by Dir.'
                            else 'Unknown' end) as TransStatus");
        $this->db->from($this->table . ' bren');
        //$this->db->join($this->detail . ' br_det', 'br_det.BRenNumber = bren.BRenNumber',  'INNER');
        $this->db->join($this->account_gl . ' ag', 'ag.AccountNumber = bren.BankAccount', 'INNER');
        $this->db->join($this->currency . ' cy', 'cy.CurrencyCode = ag.CurrencyCode', 'INNER');
        $this->db->join($this->branch . ' br','br.BranchCode = bren.BranchCode','INNER');
        $this->db->join('am_employee as  emp','emp.EmployeeCode = bren.ReconCreatedBy','INNER');
        
        if(!empty($bank_reconcile)){ 
            $this->db->where('bren.BankAccount', $bank_reconcile);            
        }
        
        if($bren_date_from != '' && $bren_date_to != '' ){
            $this->db->where('bren.ReconcileDate >= ', date("Y-m-d", strtotime($bren_date_from)));
            $this->db->where('bren.ReconcileDate <= ', date("Y-m-d", strtotime($bren_date_to)));
        }
	
        $this->db->where('bren.BranchCode', $branch_code);
        $this->db->order_by('bren.BranchCode', 'ASC');
        $this->db->order_by('bren.ReconcileID', 'ASC');
        $this->db->order_by('bren.ReconcileDate', 'DESC');
        $query = $this->db->get();
        
        return $query->result();
    }
    function get_bank_account(){
        // $this->db->select('cg.AccountNumber, ag.AccountName, bk.BankOutCode, bk.BankInCode, bk.BankName, cy.CurrencyCode, 
        //                    cy.CurrencyName, cy.CurrencyRate, cg.GLSaldoBalance' . CURRENT_PERIOD . ' as BankBalance');
        $this->db->select('cg.AccountNumber, ag.AccountName,cg.GLSaldoBalance' . CURRENT_PERIOD . ' as BankBalance, ag.CurrencyCode');
        $this->db->from($this->company_gl . ' cg');
        $this->db->join($this->account_gl . ' ag', 'ag.AccountNumber = cg.AccountNumber', 'INNER');
        // $this->db->join($this->bank . ' bk', 'bk.BankID = ag.BankID', 'INNER');
        // $this->db->join($this->currency . ' cy', 'cy.CurrencyCode = ag.CurrencyCode', 'INNER');
        $this->db->where('cg.CompanyCode',$this->session->userdata('company'));
        $this->db->where('cg.BranchCode', $this->session->userdata('branch'));
        $this->db->where('cg.GLSaldoYears', date('Y'));
        $this->db->where_in('ag.AccountSubTypeID', array(5,7,8));
        $this->db->where('ag.AccountChild', 1);
        $this->db->where('cg.TransStatus', 2);
        $query  = $this->db->get();
                
        return $query->result();
    }

    function get_last_reconcile_date($account_number=null){
        $this->db->select('Journal_Date');
        $this->db->from($this->trans_bank);

        if($account_number!=null){
            $this->db->where('Account_Number',$account_number);
        }

        $this->db->order_by('Journal_Date', 'Asc');
        $this->db->limit(1);
        $query = $this->db->get();
        
        if($query->num_rows() > 0){
            $bren_date = $query->row()->Journal_Date;
            if($bren_date == '0000-00-00'){
                return false;
            }else{
            // return date('d-m-Y', strtotime($bren_date));
                return $bren_date;
            }
        }
        else{
            return false;
        }
    }

    function update_reverse_reconcile($TransNumber,$Bank_Account,$data_reverse){
        $this->db->where('TransNumber',$TransNumber);
        $this->db->where('BankAccount',$Bank_Account);
        $this->db->update($this->table,$data_reverse);
    }

    function cek_in_reconcile($account_number){
        $this->db->where('BankAccount',$account_number);

        $query = $this->db->get($this->table)->result_array();

        if(count($query)>0){
            return 1;
        }else{
            return 0;
        }
    }
    
    function get_detail_item_from_av($account_number,$ReconcileDate,$end_date,$Reconcile_Status,$CurrencyCode){
        
        $CompanyCode    = $this->session->userdata('company');
        $Branch_Code    = $this->session->userdata('branch');

        $where          = '(('.$this->table.'.ReconcileStatus not like 1) or ('.$this->table.'.ReconcileStatus is null))';

        if($CurrencyCode == 'IDR'){
            $this->db->_protect_identifiers = FALSE;
            $this->db->select($this->trans_bank.'.Account_Number,DATE_FORMAT('.$this->trans_bank.'.Journal_Date,"%Y-%m-%d") as Trans_Date,'.$this->trans_bank.'.Journal_Number as Trans_Number,'.$this->trans_bank.'.Journal_Desc as Trans_Desc,'.$this->trans_bank.'.Trans_Type,'
                .$this->trans_bank.'.Saldo_Debet as Deposit,'.$this->trans_bank.'.Saldo_Credit as WithDrawal,
                (CASE WHEN ('.$this->table.'.ReconcileBook=0) and ('.$this->table.'.ReconcileStatus=1) THEN "Yes" 
                      WHEN ('.$this->table.'.ReconcileBook=1) and ('.$this->table.'.ReconcileStatus=0) THEN "Yes"
                      ELSE "No" END) as Status_Rebook,
                (CASE WHEN ('.$this->table.'.ReconcileStatus=1) THEN "Yes" ELSE "No" END) as Status_Reconcile');
        }else{
            $this->db->_protect_identifiers = FALSE;
             $this->db->select($this->trans_bank.'.Account_Number,DATE_FORMAT('.$this->trans_bank.'.Journal_Date,"%Y-%m-%d") as Trans_Date,'.$this->trans_bank.'.Journal_Number as Trans_Number,'.$this->trans_bank.'.Journal_Desc as Trans_Desc,'.$this->trans_bank.'.Trans_Type,
                (CASE WHEN '.$this->trans_bank.'.Saldo_Debet=0 THEN 0 ELSE '.$this->trans_bank.'.Amount_Currency END) as Deposit,
                (CASE WHEN '.$this->trans_bank.'.Saldo_Credit=0 THEN 0 ELSE '.$this->trans_bank.'.Amount_Currency END) as WithDrawal,
                (CASE WHEN ('.$this->table.'.ReconcileBook=0) and ('.$this->table.'.ReconcileStatus=1) THEN "Yes" 
                      WHEN ('.$this->table.'.ReconcileBook=1) and ('.$this->table.'.ReconcileStatus=0) THEN "Yes"
                      ELSE "No" END) as Status_Rebook,
                (CASE WHEN '.$this->table.'.ReconcileStatus=1 THEN "Yes" ELSE "No" END) as Status_Reconcile');

        }

        $this->db->from($this->trans_bank);
        $this->db->join($this->table,$this->trans_bank.'.Journal_Number='.$this->table.'.TransNumber AND '.$this->trans_bank.'.Account_Number='.$this->table.'.BankAccount','LEFT');

        $this->db->where($this->trans_bank.'.Branch_Code',$Branch_Code);

        $this->db->where($this->trans_bank.'.Account_Number',$account_number);

        if($ReconcileDate!=null){
            $this->db->where($this->trans_bank.'.Journal_Date >=',$ReconcileDate);
            $this->db->where($this->trans_bank.'.Journal_Date <=',$end_date);
        }

        if($Reconcile_Status == 0){
            $this->db->where($where);
        }

        //$this->db->group_by($this->trans_bank.'.Journal_Number');

        $this->db->order_by('Journal_Date','ASC');

        $query = $this->db->get();
        return $query->result_array();
    }

    function get_detail_item_from_reconcile($account_number,$ReconcileDate,$Reconcile_Status){
        
        $CompanyCode    = $this->session->userdata('company');
        $Branch_Code    = $this->session->userdata('branch');

        
        $this->db->select($this->table.'.BankAccount as Account_Number,'.$this->table.'.TransDate as Trans_Date,'.$this->table.'.TransNumber as Trans_Number,'
            .$this->table.'.SaldoDebet as Deposit,'.$this->table.'.SaldoCredit as WithDrawal,
            (CASE WHEN ('.$this->table.'.ReconcileBook=0) and ('.$this->table.'.ReconcileStatus=1) THEN "Yes" 
                  WHEN ('.$this->table.'.ReconcileBook=1) and ('.$this->table.'.ReconcileStatus=0) THEN "Yes"
                  ELSE "No" END) as Status_Rebook,
            (CASE WHEN ('.$this->table.'.ReconcileStatus=1) THEN "Yes" ELSE "No" END) as Status_Reconcile');
        $this->db->from($this->table);
        //$this->db->join($this->table,$this->trans_bank.'.Journal_Number='.$this->table.'.TransNumber','LEFT');
        // $this->db->join($this->table.' as br',$this->trans_bank.'.Account_Number = br.BankAccount','LEFT');

        $this->db->where($this->table.'.BranchCode',$Branch_Code);

        $this->db->where($this->table.'.BankAccount',$account_number);

        if($ReconcileDate!=null){
            $this->db->where($this->table.'.TransDate >=',$ReconcileDate);
            $this->db->where($this->table.'.TransDate <=',date('Y-m-d'));
        }

        if($Reconcile_Status == 0){
            //$this->db->where($this->table.'.ReconcileStatus',0);
        }

        $this->db->group_by($this->table.'.TransNumber');

        $this->db->order_by('Trans_Date','ASC');

        $query = $this->db->get();
        return $query->result_array();
    }

    function get_detail_item_for_edit($bren_number, $account_number){
        $select_query = "SELECT Branch_Code, Trans_Date, Trans_Number, Cheque_Number, Bank_Account, Trans_Desc, Currency_Rate,
                         Trans_Type, 0 as BRenDetailID, 'No' as Status, 0 as Modus_Status, 0 as Modus_Type,
                         (CASE WHEN Saldo_Account = 'Debet' THEN Trans_TotalAmount ELSE 0 END) as Deposit,
                         (CASE WHEN Saldo_Account = 'Credit' THEN Trans_TotalAmount ELSE 0 END) as WithDrawal
                         FROM av_trans_bank as trans_bank
                         WHERE (NOT EXISTS (SELECT NULL FROM at_brendetail as bren_det WHERE bren_det.TransNumber = trans_bank.Trans_Number)) 
                         AND trans_bank.Bank_Account = '" . $account_number . "' 
                         AND trans_bank.Reconcile_Status = 0 
                         UNION
                         SELECT bank_ren.BranchCode as Branch_Code, bren_det.TransDate as Trans_Date, bren_det.TransNumber as Trans_Number, 
                         bren_det.TransCqNumber as Cheque_Number, bank_ren.BankReconcile as Bank_Account, bren_det.TransDesc as Trans_Desc, 
                         bren_det.TransCurrRate as Currency_Rate, bren_det.TransType as Trans_Type, bren_det.BRenDetailID, 'Yes' as Status, 
                         1 as Modus_Status, 0 as Modus_Type, bren_det.TransDeposit as Deposit, bren_det.TransWDrawal as WithDrawal
                         FROM at_bankreconcile as bank_ren
                         INNER JOIN at_brendetail as bren_det ON bren_det.BRenNumber = bank_ren.BRenNumber
                         WHERE bank_ren.BRenNumber = '" . $bren_number . "' 
                         AND bank_ren.BankReconcile = '" . $account_number . "' 
                         AND bank_ren.TransStatus Not In (2,3,9) 
                         ORDER BY Branch_Code Asc, Trans_Number Asc";
        
        $query = $this->db->query($select_query);
        return $query->result();
    }

    function get_detail_item_for_edit_status($bren_number, $account_number){
        $this->db->_protect_identifiers = false;
        $this->db->select("bank_ren.BranchCode as Branch_Code, bren_det.TransDate as Trans_Date, bren_det.TransNumber as Trans_Number, 
                           bren_det.TransCqNumber as Cheque_Number, bank_ren.BankReconcile as Bank_Account, bren_det.TransDesc as Trans_Desc, 
                           bren_det.BRenDetailID, 'Yes' as Status, 1 as Modus_Status, 0 as Modus_Type,
                           bren_det.TransDeposit as Deposit, bren_det.TransWDrawal as WithDrawal");
        $this->db->from($this->table . ' bank_ren');
        $this->db->join($this->detail . ' bren_det', 'bren_det.BRenNumber = bank_ren.BRenNumber', 'INNER');
        $this->db->where('bank_ren.BRenNumber', $bren_number);
        $this->db->where('bank_ren.BankReconcile', $account_number);
        $this->db->where_in('bank_ren.TransStatus', array(2,3,9));
        $this->db->order_by('bank_ren.BranchCode', 'ASC');
        $this->db->order_by('bren_det.TransNumber', 'ASC');        
        $query = $this->db->get();
        $this->db->_protect_identifiers = true;
        
        return $query->result();        
    }

    function get_bank_reconcile($bren_number){
        $this->db->select('br.BRenNumber, br.BRenDate, br.BankReconcile, br.TotalClear, br.TotalOutStanding, br.TransStatus, br.AppLevel1Status');
        $this->db->from($this->table . ' br' );
        $this->db->join($this->detail . ' br_det', 'br_det.BRenNumber = br.BRenNumber', 'INNER');
        $this->db->where('br.BRenNumber', $bren_number);
        $query = $this->db->get();
        
        return $query->row();
    }
    
    function get_bren_last_date(){
        $this->db->limit(1);
        $this->db->select('BRenDate');
        $this->db->from($this->table);
        $this->db->order_by('BRenDate', 'Desc');        
        $query = $this->db->get();
        
        return $query->row();
    }
    
    function insert($data){
        $this->db->insert($this->table, $data);

        // if ($this->db->trans_status() === FALSE) {
        //     $this->db->trans_rollback();
        //     return 0;
        // }
        // else {
        //     return 1;
        // }
    }

    function get_currency_rate($Trans_Number,$Trans_Type){

    }
	
    function insert_detail($data) {
        $this->db->insert($this->detail, $data);

        if ($this->db->trans_status() === FALSE) {
            $this->db->trans_rollback();
            return 0;
        }
        else {
            return 1;
        }
    }

    function update($data, $bren_number){
        $this->db->update($this->table, $data, array('BRenNumber'=>$bren_number));
        
        if ($this->db->trans_status() === FALSE) {
            $this->db->trans_rollback();
            return 0;
        }
        else {
            return 1;
        }
    }
    
    function update_detail($data, $bren_number, $bren_detail_id){
        $this->db->update($this->detail, $data, array('BRenNumber'=>$bren_number, 'BRenDetailID'=>$bren_detail_id));
        
        if ($this->db->trans_status() === FALSE) {
            $this->db->trans_rollback();
            return 0;
        }
        else {
            return 1;
        }
    }
    
    function update_reconcile_status($table_name, $field_name, $trans_number){        
        $update_query = "UPDATE " . $table_name . "
                         SET ReconcileStatus = 1, 
                             LastUser = '" . $this->session->userdata("username") . "' 
                         WHERE " . $field_name . " = '" . $trans_number . "'" ;

        $this->db->query($update_query);

        if ($this->db->trans_status() === FALSE) {
            $this->db->trans_rollback();
            return 0;
        }
        else {
            return 1;
        }
    }
    
    function delete_detail($bren_detail_id){
        $this->db->delete($this->detail, array('BRenDetailID' => $bren_detail_id));

        if ($this->db->trans_status() === FALSE) {
            return 0;
        }
        else {
            return 1;
        }                
    }

    function check_code_exist($bren_number) {
        $query = $this->db->get_where($this->table, array('BRenNumber' => $bren_number), 1, 0);
		
        if ($query->num_rows() > 0) {
            return TRUE;
        }
        else {
            return FALSE;
        }	
    }    
    
    function create_br_number($date = ''){
        $branch     = $this->session->userdata('branch');
        
        if($date != ''){
            $month	= date('m', strtotime($date));
            $year	= date('y', strtotime($date));
        }
        else{
            $month	= date('m');
            $year	= date('y');
        }
		
        $br_number  = $branch."/".$month.$year."BR/";
	
        $this->db->like('BRenNumber', $br_number);
        $num    = $this->db->get($this->table)->num_rows()+1;
        $long   = strlen($num);
	
        if($long == 1){
            $number ='00'.$num;
        }
        elseif($long == 2){
            $number = '0'.$num;
        }
        else{
            $number = $num;
        }
	
        $new_br_number = $br_number.$number;
        
        while($this->check_code_exist($new_br_number)){
            $num++;
            $long = strlen($num);
    
            if($long == 1){
               $number = '00'.$num;
            }
            elseif($long == 2){
               $number = '0'.$num;
            }
            else{
                $number = $num;
            }
            
            $new_br_number = $br_number.$number;		
        }
        return $new_br_number;
    }

    function cek_status_reconcilebook($TransNumber,$Bank_Account){

        $this->db->select('ReconcileBook');
        $this->db->where('TransNumber',$TransNumber);
        $this->db->where('BankAccount',$Bank_Account);

        $query = $this->db->get($this->table)->result_array();

        if(count($query) > 0){

           return true;

        }else{

           return false;
        }

    }

    function update_rebook($TransNumber,$Bank_Account,$SaldoDebet,$data_rebook){
        $this->db->update($this->table,$data_rebook,array('TransNumber' => $TransNumber,'BankAccount'=>$Bank_Account,'SaldoDebet' => $SaldoDebet));
    }

    function update_approval_reconcile($TransNumber,$Bank_Account,$data_approval){
        $this->db->update($this->table,$data_approval,array('TransNumber' => $TransNumber,'BankAccount'=>$Bank_Account));
    }
}