<?php
class M_period_end extends CI_Model {
    public $table               = 'at_periodend';
    public $detail              = 'at_perioddetail';
    public $currency            = 'am_currency';

    public $comp_gl             = 'at_compaccountgl';
    public $account_gl          = 'am_accountgl';
    public $branch              = 'am_branch';
    public $company             = 'am_company';
    public $sub_type            = 'am_accountsubtype';
    public $acc_type            = 'am_accounttype';
    public $history_currency    = 'ah_currency_history';
    
    public $pinv_recur          = 'at_pinvdetailrecur';
    public $purchase_invoice    = 'at_purchaseinvoice';

    public $periodadvance       = 'at_periodadvance';
    public $periodfixedasset    = 'at_periodfixedasset';
    public $periodunrealized    = 'at_periodunrealized';    

    public $invent_detail       = 'at_inventdetail';
    public $item_category       = 'am_itemcategory';
    public $fixed_asset_type    = 'am_fixedassettype';
    public $depreciatemethod    = 'am_depreciatemethod';
    
    public $gnl_ledger          = 'at_journalvoucher';
    public $ledger_detail       = 'at_jvchdetail';
    public $ledger_journal      = "at_jvchjournal";
    
    public $transaction         = 'av_transaction';
    public $trans_bank          = 'av_trans_bank';
    public $av_transstatus      = 'av_all_transstatus';

    public $reconcile           = 'at_bankreconcille';
    public $av_all_transaction  = 'av_all_transaction';

    public $sys_advanceaccount  = 'sys_advanceaccount';
    public $sys_advancecategory = 'sys_advancecategory';
    public $sys_advancedetail   = 'sys_advancedetail';
    
    function check_period_end_status($period_month, $period_year){
        $company_code = $this->session->userdata('company'); 

        $this->db->_protect_identifiers = false;
        $this->db->select('count(*) as num_records');
        $this->db->from($this->table);
        $this->db->where('CompanyCode', $company_code);
        $this->db->where('substring(PeriodEnd, -2) = ', $period_month);
        $this->db->where('substring(PeriodEnd, 1, 4) = ', $period_year);
        $query = $this->db->get();
        $this->db->_protect_identifiers = true;
        
        if($query->row()->num_records > 0){
            return FALSE;
        }
        else{
            return TRUE;
        }        
    }
    
    function check_not_approved_yet($period_month, $period_year){
        $company_code = $this->session->userdata('company'); 
        
        $this->db->select('count(*) as num_records');
        $this->db->from($this->transaction . ' tr');
        $this->db->join($this->branch . ' br', 'br.BranchCode = tr.Branch_Code', 'INNER');
        $this->db->where('br.CompanyCode', $company_code);
        $this->db->where_in('tr.Trans_Status', array('4,5,6'));
        $this->db->where('month(tr.Trans_Date)', $period_month);
        $this->db->where('year(tr.Trans_Date)', $period_year);
        $query = $this->db->get();
        
        if($query->row()->num_records > 0){
            return FALSE;
        }
        else{
            return TRUE;
        }
    }

    function check_not_reconcile_yet($period_month, $period_year){
        $company_code = $this->session->userdata('company'); 

        $this->db->select('count(*) as num_records');
        $this->db->from($this->trans_bank . ' tb');
        $this->db->join($this->branch . ' br', 'br.BranchCode = tb.Branch_Code', 'INNER');
        $this->db->where('br.CompanyCode', $company_code);
        $this->db->where('tb.Reconcile_Status', 0);
        $this->db->where('month(tb.Trans_Date)', $period_month);
        $this->db->where('year(tb.Trans_Date)', $period_year);
        $query = $this->db->get();
        
        if($query->row()->num_records > 0){
            return FALSE;
        }
        else{
            return TRUE;
        }
    }
    
    function get_saldo_balance($period_month, $period_year){
        $company_code = $this->session->userdata('company'); 

        $this->db->select("cg.BranchCode, cg.AccountNumber, ag.AccountName, ag.AccountSubTypeID, ag.CurrencyCode,
                           GLSaldoBalance" . $period_month . " as SaldoBalance");
        $this->db->from($this->comp_gl . ' cg');
        $this->db->join($this->account_gl . ' ag', 'ag.AccountNumber = cg.AccountNumber', 'INNER');
        $this->db->where('cg.CompanyCode', $company_code);
        $this->db->where('cg.GLSaldoYears', $period_year);
        $this->db->where('ag.AccountChild', 1);
        $this->db->where_in('ag.AccountSubTypeID', array(5,6,7));
        $this->db->order_by('cg.AccountNumber', 'ASC');
        $this->db->order_by('cg.CompanyCode', 'ASC');
        $this->db->order_by('cg.BranchCode', 'ASC');
        $query = $this->db->get();
        
        return $query->result();
    }

    
    function get_adv_payment($company_code,$PeriodDate){
        
        $this->db->select('p_inv.BranchCode, p_rec.PInvDate, p_rec.PInvNumber, p_rec.ItemCode, p_rec.ItemDesc, 
                           p_rec.PInvMemo, p_rec.ItemTotalAmount, p_rec.PaymentAmount, p_rec.ItemAdvNetDueIn,p_rec.JVoucherBalance, 
                           p_rec.PaymentPeriod, p_inv.CurrencyCode, p_inv.CurrencyRate');
        $this->db->from($this->pinv_recur . ' p_rec');
        $this->db->join($this->purchase_invoice . ' p_inv', 'p_inv.PInvNumber = p_rec.PInvNumber', 'INNER');
        $this->db->join($this->branch . ' br', 'br.BranchCode = p_inv.BranchCode', 'INNER');
        // $this->db->where('(ItemAdvNetDueIn - PaymentPeriod) > 0');
        // $this->db->where('ItemTotalAmount > PaymentAmount');
        $this->db->where('br.CompanyCode', $company_code);
        $this->db->where('p_rec.SoldStatus', 1);
        $this->db->where('p_rec.PInvDate <=',$PeriodDate);
        $this->db->order_by('br.BranchCode', 'ASC');
        $this->db->order_by('p_rec.PInvNumber', 'ASC');
        
        $query = $this->db->get();
        
        return $query->result();        
    }
    
    function get_fixed_asset($company_code,$PeriodDate){

        $this->db->select('id.BranchCode,id.AssetCode,id.ReceiptDate, id.ReceiptNumber, id.ItemAverage, fat.DepriciateRate, fat.FixedAssetType, id.PaymentAmount,
                          (id.ItemAverage * ((fat.DepriciateRate / 12) / 100)) as Payment_per_periode,id.ItemCategoryID,id.PaymentPeriod');
        $this->db->from($this->invent_detail . ' id');
        $this->db->join($this->item_category . ' ic', 'ic.ItemCategoryID = id.ItemCategoryID', 'INNER');
        $this->db->join($this->fixed_asset_type . ' fat', 'fat.FixedAssetType = ic.ItemCategoryName', 'INNER');
        $this->db->join($this->branch . ' br', 'br.BranchCode = id.BranchCode', 'INNER');
        $this->db->join($this->depreciatemethod.' as dm','dm.DepMethodID=fat.DepMethodID','inner');
        $this->db->where('br.CompanyCode', $company_code);
        // $this->db->where('id.ItemAverage  < id.PaymentAmount');
        $this->db->where('dm.DepMethodeName !=','Non Depreciable Method');
        //$this->db->where('fat.TransStatus',1);
        $this->db->where('id.SoldStatus',0);
        $this->db->where('id.ReceiptDate <=',$PeriodDate);
        $this->db->order_by('id.BranchCode', 'ASC');
        $this->db->order_by('id.ReceiptNumber', 'ASC');
        $query = $this->db->get();
        
        return $query->result();
    }
    
    function get_fixed_asset_account($fixed_asset_type){
        $this->db->select('AccumAccountNumber, ExpenseAccountNumber');
        $this->db->from($this->fixed_asset_type);
        $this->db->where('FixedAssetType', $fixed_asset_type);
        $query = $this->db->get();

        if($query->num_rows > 0){
            return $query->row();
        }
        else{
            return FALSE;
        }
    }
    
    function insert($data){
        $this->db->insert($this->table, $data);
                
        if ($this->db->trans_status() == FALSE) {
            return 0;
        }
        else {
            return 1;
        }
    }

    function insert_detail($data){
        $this->db->insert($this->detail, $data);
                
        if ($this->db->trans_status() == FALSE) {
            return 0;
        }
        else {
            return 1;
        }
    }

    function insert_unrealized($data){
        $this->db->insert($this->periodunrealized, $data);
                
        if ($this->db->trans_status() == FALSE) {
            return 0;
        }
        else {
            return 1;
        }
    }
    
    function insert_advance($data){
        $this->db->insert($this->periodadvance, $data);
                
        if ($this->db->trans_status() == FALSE) {
            return 0;
        }
        else {
            return 1;
        }
    }        

    function insert_fixed_asset($data){
        $this->db->insert($this->periodfixedasset, $data);
                
        if ($this->db->trans_status() == FALSE) {
            return 0;
        }
        else {
            return 1;
        }
    }        
    
    function insert_gnl_ledger($data){
        $this->db->insert($this->gnl_ledger, $data);
                
        if ($this->db->trans_status() == FALSE) {
            return 0;
        }
        else {
            return 1;
        }
    }        
    
    function insert_ledger_detail($data){
        $this->db->insert($this->ledger_detail, $data);
                
        if ($this->db->trans_status() == FALSE) {
            return 0;
        }
        else {
            return 1;
        }
    }        

    function insert_ledger_journal($data){
        $this->db->insert($this->ledger_journal, $data);
                
        if ($this->db->trans_status() == FALSE) {
            return 0;
        }
        else {
            return 1;
        }
    }        
    
    function update_comp_gl($period_month, $period_year){
        $this->db->_protect_identifiers = false;
        $sql_update = "UPDATE at_compaccountgl 
                       SET GLSaldoBalance" . ($period_month + 1) . " = GLSaldoBalance" . ($period_month + 1) . " + GLSaldoBalance" . $period_month . " 
                       WHERE AccountNumber IN (SELECT AccountNumber FROM am_accountgl WHERE NOT AccountSubTypeID IN (65, 68, 71, 75, 76))
                       AND GLSaldoYears = " . $period_year;
        $this->db->query($sql_update);
        $this->db->_protect_identifiers = true;
        
        if ($this->db->trans_status() == FALSE) {
            return 0;
        }
        else {
            return 1;
        }
    }

    function update_appfinished($jvch_number) {
        $this->db->update($this->ledger_journal, array('AppFinished'=>1), array('JVchNumber' => $jvch_number));			

        if ($this->db->trans_status() == FALSE) {
            return 0;
        }
        else {
            return 1;
        }		
    }
    
    
//    function update_appfinished($jvch_number){
//        $sql_update = "UPDATE at_jvchjournal 
//                       SET AppFinished = 1 
//                       WHERE JVchNumber in (" . $jvch_number . ")";
//        $this->db->query($sql_update);
//
//        if ($this->db->trans_status() == FALSE) {
//            return 0;
//        }
//        else {
//            return 1;
//        }
//    }
    
    function coa_income_goes($period_month, $period_year){
        $company_code = $this->session->userdata('company');
        $branch_code = $this->session->userdata('branch');
        
        $this->db->_protect_identifiers = false;
        $this->db->select('GLSaldoBalance' . $period_month . ' as Saldo_Goes');
        $this->db->from($this->comp_gl . ' cg');
        $this->db->join($this->account_gl . ' ag', 'ag.AccountNumber = cg.AccountNumber', 'INNER');
        $this->db->where('cg.CompanyCode', $company_code);
        $this->db->where('cg.BranchCode', $branch_code);
        $this->db->where('cg.GLSaldoYears', $period_year);
        $this->db->like('ag.AccountName', 'Laba (Rugi) Berjalan');
        $this->db->where('ag.CurrencyCode', 'IDR');
        $query = $this->db->get();
        $this->db->_protect_identifiers = true;

        return $query->row()->Saldo_Goes;
    }

    function coa_income_current($period_month, $period_year){
        $company_code = $this->session->userdata('company');
        $branch_code = $this->session->userdata('branch');
        
        $this->db->_protect_identifiers = false;
        $this->db->select('GLSaldoBalance' . $period_month . ' as Saldo_Current');
        $this->db->from($this->comp_gl . ' cg');
        $this->db->join($this->account_gl . ' ag', 'ag.AccountNumber = cg.AccountNumber', 'INNER');
        $this->db->where('cg.CompanyCode', $company_code);
        $this->db->where('cg.BranchCode', $branch_code);
        $this->db->where('cg.GLSaldoYears', $period_year);
        $this->db->like('ag.AccountName', 'Laba (Rugi) Bulan Berjalan');
        $this->db->where('ag.CurrencyCode', 'IDR');
        $query = $this->db->get();
        $this->db->_protect_identifiers = true;

        return $query->row()->Saldo_Current;
    }
    
    function check_code_exist($jv_number) {
        $query = $this->db->get_where($this->gnl_ledger, array('JVchNumber' => $jv_number), 1, 0);
		
        if ($query->num_rows() > 0) {
            return TRUE;
        }
        else {
            return FALSE;
        }	
    }

    function create_jv_number($date = '',$branch){
        
        if($date != ''){
            $month	= date('m',strtotime($date));
            $year	= date('y',strtotime($date));
        }
        else{
            $month	= date('m');
            $year	= date('y');
        }
		
        $jv_number  = $branch."/".$month.$year."JV/";

        $this->db->_protect_identifiers = false;
        $this->db->select('(CASE 
                              WHEN MAX(CONVERT(SUBSTR(JVchNumber, -3),signed)) IS NULL then 1
                              ELSE MAX(CONVERT(SUBSTR(JVchNumber, -3),signed)) + 1 END) as New_Number');
        $this->db->like('JVchNumber', $jv_number);
        $query = $this->db->get($this->gnl_ledger);
        $this->db->_protect_identifiers = true;
        
        $num    = $query->row()->New_Number;
        $long   = strlen($num);
	
        if($long == 1){
            $number ='00' . $num;
        }
        elseif($long == 2){
            $number = '0' . $num;
        }
        else{
            $number = $num;
        }
	
        $new_jv_number = $jv_number . $number;
        
        while($this->check_code_exist($new_jv_number)){
            $num++;
            $long = strlen($num);
    
            if($long == 1){
                $number = '00' . $num;
            }
            elseif($long == 2){
                $number = '0' . $num;
            }
            else{
                $number = $num;
            }
            
            $new_jv_number = $jv_number . $number;		
        }
        
        return $new_jv_number;
    }   

    function get_last_endperiod(){
        $company_code = $this->session->userdata('company');

        $this->db->select('PeriodEnd');
        $this->db->where('CompanyCode',$company_code);
        $this->db->limit(1);
        $this->db->order_by('LastEdited','Desc');
        $query = $this->db->get($this->table)->result_array();

        if(count($query)> 0){
            return $query[0]['PeriodEnd'];
        }else{
            return false;
        }

    }  

    function check_endperiod($PeriodEnd){
        $company_code = $this->session->userdata('company');

        $this->db->select('PeriodEnd');
        $this->db->where('PeriodEnd',$PeriodEnd);
        $this->db->where('CompanyCode',$company_code);
        $this->db->order_by('PeriodDate','Desc');
        $query = $this->db->get($this->table)->result_array();

        if(count($query)> 0){
            return true;
        }else{
            return false;
        }
    }

    function check_transstatus_not_approved($begin_date,$end_date,$last_emperiode){
        $company_code   = $this->session->userdata('company');

        if($last_emperiode != false){

            $last_year      = substr($last_emperiode,0,4);
            $last_month     = substr($last_emperiode,4,2);

            $from_date      = '01-' . $last_month . '-' . $last_year;
            $from_date      = date("Y-m-d", strtotime($from_date));  
        }

        $this->db->select('Journal_Number,Trans_Type');
        $this->db->where('CompanyCode',$company_code);

        if($last_emperiode == false){
            $this->db->where($this->av_transstatus.'.Journal_Date >=',$begin_date);
            $this->db->where($this->av_transstatus.'.Journal_Date <=',$end_date);
        }else{
            $this->db->where($this->av_transstatus.'.Journal_Date >=',$from_date);
            $this->db->where($this->av_transstatus.'.Journal_Date <=',$end_date);
        }


        $this->db->where_not_in($this->av_transstatus.'.TransStatus',array(1,2,3,9));

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

        if(count($query)>0){
            return $query[0];
        }else{
            return false;
        }
    }  

    function check_transaction_not_reconcile($begin_date,$end_date,$last_emperiode){
        $company_code   = $this->session->userdata('company');
        $where          = '(('.$this->reconcile.'.ReconcileStatus not like 1) or ('.$this->reconcile.'.ReconcileStatus is null))';

       if($last_emperiode != false){
            
            $last_year      = substr($last_emperiode,0,4);
            $last_month     = substr($last_emperiode,4,2);

            $from_date      = '01-' . $last_month . '-' . $last_year;
            $from_date      = date("Y-m-d", strtotime($from_date));  
        }

        $this->db->select($this->av_transstatus.'.Journal_Number,'.$this->av_transstatus.'.Trans_Type');
        $this->db->from($this->av_transstatus);
        $this->db->join($this->reconcile,$this->av_transstatus.'.Journal_Number='.$this->reconcile.'.TransNumber AND '.$this->av_transstatus.'.Account_Number='.$this->reconcile.'.BankAccount','LEFT');
        $this->db->join($this->sub_type.' as sub','sub.AccountSubTypeID='.$this->av_transstatus.'.Sub_Type_ID','inner');
        $this->db->join($this->acc_type.' as acc','acc.AccountTypeID=sub.AccountTypeID','inner');
        $this->db->where($this->av_transstatus.'.CompanyCode',$company_code);
        $this->db->where_in($this->av_transstatus.'.TransStatus',array(1,2));
        $this->db->where_in('acc.AccountTypeName','Cash / Bank');
        $this->db->where($where);

       if($last_emperiode == false){
            $this->db->where($this->av_transstatus.'.Journal_Date >=',$begin_date);
            $this->db->where($this->av_transstatus.'.Journal_Date <=',$end_date);
        }else{
            $this->db->where($this->av_transstatus.'.Journal_Date >=',$from_date);
            $this->db->where($this->av_transstatus.'.Journal_Date <=',$end_date);
        }

        $this->db->group_by($this->av_transstatus.'.Journal_Number');
        $this->db->order_by('Journal_Date','ASC');

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

        if(count($query) > 0){
            return $query[0];
        }else{
            return false;
        }
    } 

    function get_bank_account($currency_code,$company_code,$period_year){
        $this->db->select('cg.AccountNumber, ag.AccountName,cg.GLSaldoBSheet as BankBalance, ag.CurrencyCode,cg.BranchCode');
        $this->db->from($this->comp_gl.' as cg');
        $this->db->join($this->account_gl.' as ag', 'ag.AccountNumber = cg.AccountNumber', 'INNER');
        $this->db->join($this->sub_type . ' as sub', 'sub.AccountSubTypeID = ag.AccountSubTypeID', 'INNER');
        $this->db->join($this->acc_type . ' as acc', 'acc.AccountTypeID = sub.AccountTypeID', 'INNER');
        $this->db->where('cg.CompanyCode',$company_code);
        $this->db->where('ag.CurrencyCode',$currency_code);
        $this->db->where('cg.GLSaldoYears', $period_year);
        $this->db->where('acc.AccountTypeName', 'Cash / Bank');
        $this->db->where('ag.AccountChild', 1);
        $this->db->where('cg.TransStatus', 2);
        $this->db->order_by('cg.BranchCode');
        $query  = $this->db->get()->result_array();
                
        if(count($query)>0){
            return $query;
        }else{
            $query = array();
            return $query;
        }
    }

    function get_account_by_bsstatus_reverse($company_code,$month,$year,$bsstatus){
         if($month == 1){
            $month = 12;
            $year  = $year - 1;
        }else{
            $month = $month - 1;
            $year  = $year;
        }
        
        $this->db->select('cg.AccountNumber, ag.AccountName,cg.GLSaldoBSheet as BankBalance, ag.CurrencyCode,cg.BranchCode,cg.GLSaldoOpening,cg.GLSaldoBalance'.$month.' as GLSaldoBalance,GLSaldoYears');
        $this->db->from($this->comp_gl.' as cg');
        $this->db->join($this->account_gl.' as ag', 'ag.AccountNumber = cg.AccountNumber', 'INNER');
        $this->db->join($this->sub_type . ' as sub', 'sub.AccountSubTypeID = ag.AccountSubTypeID', 'INNER');
        $this->db->where('cg.CompanyCode',$company_code);
        $this->db->where('cg.GLSaldoYears',$year);
        $this->db->where('sub.AccountBSStatus', $bsstatus);
        $this->db->where('ag.AccountChild', 1);
        $this->db->where('cg.TransStatus', 2);
        $this->db->order_by('cg.BranchCode,cg.AccountNumber','ASC');
        $query  = $this->db->get()->result_array();
                
        return $query;
    }

    function get_account_by_bsstatus($company_code,$month,$year,$bsstatus,$last_endperiod){

        if($month == 1){
            if($last_endperiod == false){
                $month = $month - 1;
                $year  = $year;
                $year_now = date('Y');
            }else{
                $month = 12;
                $year  = $year - 1;
                $year_now = date('Y'); 
            }
        }else{
            $month = $month - 1;
            $year  = $year;
            $year_now = date('Y');
        }
        
        $this->db->select('cg.AccountNumber, ag.AccountName,cg.GLSaldoBSheet as BankBalance, ag.CurrencyCode,cg.BranchCode,cg.GLSaldoOpening,cg.GLSaldoBalance'.$month.' as GLSaldoBalance,GLSaldoYears,sub.AccountTypeName as Sub_Type_Name');
        $this->db->from($this->comp_gl.' as cg');
        $this->db->join($this->account_gl.' as ag', 'ag.AccountNumber = cg.AccountNumber', 'INNER');
        $this->db->join($this->sub_type . ' as sub', 'sub.AccountSubTypeID = ag.AccountSubTypeID', 'INNER');
        // $this->db->join($this->acc_type . ' as acc', 'acc.AccountTypeID = sub.AccountTypeID', 'INNER');
        $this->db->where('cg.CompanyCode',$company_code);

        if($year < $year_now){
            $this->db->where_in('cg.GLSaldoYears', array($year,$year_now));
        }else{
            $this->db->where('cg.GLSaldoYears',$year);
        }
        
        $this->db->where('sub.AccountBSStatus', $bsstatus);
        $this->db->where('ag.AccountChild', 1);
        $this->db->where('cg.TransStatus', 2);
        $this->db->order_by('cg.BranchCode,cg.AccountNumber','ASC');
        $query  = $this->db->get()->result_array();
                
        return $query;
    }

    function get_laba_rugi_ditahan($company_code,$year,$type_name){
        $this->db->select('cg.AccountNumber, sum(cg.GLSaldoBSheet) as balance');
        $this->db->from($this->comp_gl.' as cg');
        $this->db->join($this->account_gl.' as ag', 'ag.AccountNumber = cg.AccountNumber', 'INNER');
        $this->db->join($this->sub_type . ' as sub', 'sub.AccountSubTypeID = ag.AccountSubTypeID', 'INNER');
        // $this->db->join($this->acc_type . ' as acc', 'acc.AccountTypeID = sub.AccountTypeID', 'INNER');
        $this->db->where('cg.CompanyCode',$company_code);
        $this->db->where('cg.GLSaldoYears', $year);
        $this->db->where('sub.AccountTypeName', $type_name);
        $this->db->where('ag.AccountChild', 1);
        $this->db->where('cg.TransStatus', 2);
        $query  = $this->db->get();
        $row    = $query->row();
        return $row;
    }

    function get_laba_rugi_berjalan($company_code,$month,$year,$type_name){
        $this->db->select('cg.AccountNumber, sum(cg.GLSaldoBalance'.$month.') as balance');
        $this->db->from($this->comp_gl.' as cg');
        $this->db->join($this->account_gl.' as ag', 'ag.AccountNumber = cg.AccountNumber', 'INNER');
        $this->db->join($this->sub_type . ' as sub', 'sub.AccountSubTypeID = ag.AccountSubTypeID', 'INNER');
        // $this->db->join($this->acc_type . ' as acc', 'acc.AccountTypeID = sub.AccountTypeID', 'INNER');
        $this->db->where('cg.CompanyCode',$company_code);
        $this->db->where('cg.GLSaldoYears', $year);
        $this->db->where('sub.AccountTypeName', $type_name);
        $this->db->where('ag.AccountChild', 1);
        $this->db->where('cg.TransStatus', 2);
        $query  = $this->db->get();
        $row    = $query->row();
        return $row;
    }

    function get_laba_rugi_bulan_berjalan($company_code,$month,$year,$type_name){
        $this->db->select('cg.AccountNumber, sum(cg.GLSaldoBalance'.$month.') as balance');
        $this->db->from($this->comp_gl.' as cg');
        $this->db->join($this->account_gl.' as ag', 'ag.AccountNumber = cg.AccountNumber', 'INNER');
        $this->db->join($this->sub_type . ' as sub', 'sub.AccountSubTypeID = ag.AccountSubTypeID', 'INNER');
        // $this->db->join($this->acc_type . ' as acc', 'acc.AccountTypeID = sub.AccountTypeID', 'INNER');
        $this->db->where('cg.CompanyCode',$company_code);
        $this->db->where('cg.GLSaldoYears', $year);
        $this->db->where('sub.AccountTypeName', $type_name);
        $this->db->where('ag.AccountChild', 1);
        $this->db->where('cg.TransStatus', 2);
        $query  = $this->db->get();
        $row    = $query->row();
        return $row;
    }

    function get_transaction_by_account($bank,$branch_code){
        $this->db->select('Branch_Code,Account_Number,Amount_Currency');
        $this->db->from($this->av_transstatus);
        $this->db->where_in('TransStatus',array(1,2));
        $this->db->where_in('Trans_Type',array('CPay','OInc','OPay','EBon','EPReal'));
        $this->db->where('Branch_Code',$branch_code);

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

        return $query;
    }

    function get_branch($company_code){
        $this->db->select('BranchCode');
        $this->db->from($this->branch);
        $this->db->where('CompanyCode',$company_code);

        $query = $this->db->get()->result();

        $result = [];
        foreach($query as $row) {

            array_push($result,$row->BranchCode);
        }

        return $result;
    }

    function get_arr_jvnumber($arr_branch,$begin_date){
        $result             = array();
        $month              = date('m',strtotime($begin_date));
        $year               = date('Y',strtotime($begin_date));

        $first_date         = date('Y-m-d H:i:s',strtotime($year.'-'.$month.'-01 00:00:00'));
        $day_month          = date('t', strtotime($begin_date));
        $last_date          = date('Y-m-d H:i:s',strtotime($year.'-'.$month.'-'.$day_month.' 23:59:59'));

        for($x=0;$x<count($arr_branch);$x++){
            $this->db->select('JVchNumber');
            $this->db->from($this->gnl_ledger);
            $this->db->where('BranchCode',$arr_branch[$x]);
            $this->db->where('CreatedAt >=',$first_date);
            $this->db->where('CreatedAt <=',$last_date);
            $this->db->order_by('JVchNumber','DESC');

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

            if(count($query)>0){
                $jv_number_before   = $query[0]['JVchNumber'];
                $arr_no             = explode("/", $jv_number_before);
                $no_explode         = $arr_no[2];
                $ordinal_number     = sprintf("%03s",(int)$no_explode+1);
                $jv_number          = $arr_no[0].'/'.$arr_no[1].'/'.$ordinal_number;
            }else{
                $jv_number = $this->create_jv_number($begin_date,$arr_branch[$x]);
            }

            $result['BranchCode'][]  =  $arr_branch[$x];
            $result['JVchNumber'][]  =  $jv_number;
        }

        return $result;
    }

    function get_master_currency($company_code,$CurrencyCode){
        $this->db->select('CurrencyRate');
        $this->db->from($this->currency);
        $this->db->where('CompanyCode',$company_code);
        $this->db->where('CurrencyCode',$CurrencyCode);
        $this->db->where_in('TransStatus',array(1,2));

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

        return $query[0]['CurrencyRate'];
    }

    function get_saldo_balance_by_branch($bank,$branch,$period_year){
        $this->db->select('GLSaldoBSheet');
        $this->db->from($this->comp_gl);
        $this->db->where('BranchCode',$branch);
        $this->db->where('AccountNumber',$bank);
        $this->db->where('GLSaldoYears',$period_year);
        $this->db->where('TransStatus',2);

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

        return $query[0]['GLSaldoBSheet'];
    }

    function get_diff_kurs2($bank,$branch,$last_endperiod,$first_balance,$last_balance,$last_saldo,$currency_rate,$master_rate,$from_date,$end_date){
        
        $this->db->select('sum((CASE WHEN Saldo_Debet=0 THEN (Amount_Currency * -1) ELSE Amount_Currency END)) as Amount_Currency,sum((CASE wHEN Saldo_Debet=0 THEN (Saldo_Credit * -1) ELSE Saldo_Debet END)) as total_saldo');
        $this->db->from($this->av_all_transaction);
        $this->db->where('Branch_Code',$branch);
        $this->db->where('Account_Number',$bank);
        $this->db->where('Journal_Date >=',$from_date);
        $this->db->where('Journal_Date <=',$end_date);

        $Query              = $this->db->get()->result_array();

        if($last_endperiod == false){
            $saldo_awal         = $last_balance - $Query[0]['Amount_Currency'];
        }else{
            $saldo_awal         = $first_balance;
        }

        $saldo_awal_rupiah  = $saldo_awal * $master_rate;
        $saldo_akhir_rupiah = $last_saldo[0]['total_saldo'];
        $total_saldo_rupiah = $saldo_awal_rupiah + $saldo_akhir_rupiah;
        $diff_kurs          = ($last_saldo[0]['Amount_Currency'] * intval($currency_rate)) - $total_saldo_rupiah;

        return $diff_kurs;
    }

    function get_diff_kurs($bank,$branch,$first_balance,$last_balance,$last_saldo,$currency_rate,$master_rate){
        // $saldo_awal         = $first_balance;
        // $saldo_awal_rupiah  = $saldo_awal * $master_rate;
        // $saldo_akhir_rupiah = $last_saldo[0]['total_saldo'];
        // $total_saldo_rupiah = $saldo_awal_rupiah + $saldo_akhir_rupiah;
        // $diff_kurs          = (($last_saldo[0]['Amount_Currency'] + $saldo_awal) * intval($currency_rate)) - $total_saldo_rupiah;

        $saldo_awal         = $first_balance;
        $saldo_awal_rupiah  = bcmul($saldo_awal,$master_rate,20);
        $saldo_akhir_rupiah = $last_saldo[0]['total_saldo'];
        $total_saldo_rupiah = bcadd($saldo_awal_rupiah,$saldo_akhir_rupiah,20);
        $diff_kurs          = bcsub(bcmul(bcadd($last_saldo[0]['Amount_Currency'],$saldo_awal,20),$currency_rate,20),$total_saldo_rupiah,20);
        
        // if($branch == 'IST-HO-JKT'){
        //     if($diff_kurs !=0){
        //         echo '(('.$last_saldo[0]['Amount_Currency'].'+'.$saldo_awal.') * '.$currency_rate.')) - '.$total_saldo_rupiah.' ';
        //         echo $diff_kurs.' ';
        //     }
        // }

        return $diff_kurs;
    }

    function get_last_saldo_bank($bank,$branch,$from_date,$end_date){
        $this->db->select('sum((CASE WHEN Saldo_Debet=0 THEN (Amount_Currency * -1) ELSE Amount_Currency END)) as Amount_Currency,sum((CASE wHEN Saldo_Debet=0 THEN (Saldo_Credit * -1) ELSE Saldo_Debet END)) as total_saldo');
        $this->db->from($this->av_all_transaction);
        $this->db->where('Branch_Code',$branch);
        $this->db->where('Account_Number',$bank);
        $this->db->where('Journal_Date >=',$from_date);
        $this->db->where('Journal_Date <=',$end_date);

        $Query              = $this->db->get()->result_array();

        return $Query;

       
    }

    function get_diff_kurs1($bank,$branch,$last_balance,$currency_rate,$master_rate,$from_date,$end_date){
        $this->db->select('sum((CASE WHEN Saldo_Debet=0 THEN (Amount_Currency * -1) ELSE Amount_Currency END)) as Amount_Currency,sum((CASE wHEN Saldo_Debet=0 THEN (Saldo_Credit * -1) ELSE Saldo_Debet END)) as total_saldo');
        $this->db->from($this->av_all_transaction);
        $this->db->where('Branch_Code',$branch);
        $this->db->where('Account_Number',$bank);
        $this->db->where('Journal_Date >=',$from_date);
        $this->db->where('Journal_Date <=',$end_date);

        $Query              = $this->db->get()->result_array();

        $return['saldo_awal'] = $last_balance - $Query[0]['Amount_Currency'];
        $return['amount_transaksi']   = $Query[0]['Amount_Currency'];
        $return['saldo_awal_rupiah']  = $return['saldo_awal'] * $master_rate;
        $return['saldo_akhir_rupiah'] = $Query[0]['total_saldo'];
        $return['total_saldo_rupiah'] = $return['saldo_awal_rupiah'] + $return['saldo_akhir_rupiah'];
        $return['diff_kurs']          = ($last_balance * intval($currency_rate)) - $return['total_saldo_rupiah'];
        $return['Bank']               = $bank;
        $return['Branch']             = $branch;

        return $return;
    }

    function get_all_transaction($branch,$bank,$from_date,$end_date){
        $this->db->select('Branch_Code,Journal_Number,Saldo_Debet,Saldo_Credit,Amount_Currency,Sign_Status');
        $this->db->from($this->av_all_transaction);
        $this->db->where('Branch_Code',$branch);
        $this->db->where('Account_Number',$bank);
        $this->db->where('Journal_Date >=',$from_date);
        $this->db->where('Journal_Date <=',$end_date);

        $Query = $this->db->get()->result_array();

        return $Query;
    }

    function insert_group_jvb($data_group){
        $this->db->insert($this->gnl_ledger,$data_group);
    }

    function insert_detail_jvb($data_detail){
        $this->db->insert($this->ledger_detail,$data_detail);
    }

    function insert_journal_jvb($data_journal){
        $this->db->insert($this->ledger_journal,$data_journal);
    } 

    function update_group_jvb($JVchNumber,$data_update){
        $this->db->where('JVchNumber',$JVchNumber);
        $this->db->update($this->gnl_ledger,$data_update);
    }

    function get_sys_advance_account($account_code, $currency_code,$item_code,$branch,$period_year){
        
        $this->db->select('sys_det.AdvCategoryID,sys_det.AdvNetDueIn,sys_acc.AccountCode,sys_acc.AccountNumber,acc_gl.AccountName,sub.AccountTypeName as Sub_Type_Name, ac.AccountTypeName as Acc_Type_Name');
        $this->db->from($this->sys_advancedetail.' as sys_det');
        $this->db->join($this->sys_advanceaccount.' as sys_acc','sys_det.AdvCategoryID=sys_acc.AdvCategoryID','inner');
        $this->db->join($this->account_gl.' as acc_gl','sys_acc.AccountNumber=acc_gl.AccountNumber','inner');
        $this->db->join($this->comp_gl.' as c_gl','acc_gl.AccountNumber=.c_gl.AccountNumber','inner');
        $this->db->join($this->sub_type.' as sub','sub.AccountSubTypeID=.acc_gl.AccountSubTypeID','inner');
        $this->db->join($this->acc_type.' as ac','ac.AccountTypeID=.sub.AccountTypeID','inner');
        $this->db->where('sys_det.AdvDetailName',$item_code);
        $this->db->where('sys_acc.AccountCode',$account_code);
        $this->db->where('acc_gl.CurrencyCode',$currency_code);
        $this->db->where('c_gl.BranchCode',$branch);
        $this->db->where('c_gl.GLSaldoYears',$period_year);
        $this->db->where('c_gl.TransStatus',2);

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

        if($query->num_rows()>0){
            return $query->row();
        }else{
            return false;
        }
    }

    function update_invdetailrec($inv_number,$data_update){
        $this->db->where('PInvNumber',$inv_number);
        $this->db->update($this->pinv_recur,$data_update);
    }

    function update_inventdetail($prec_number,$AssetCode,$data_update_dep){
        $this->db->where('AssetCode',$AssetCode);
        $this->db->where('ReceiptNumber',$prec_number);
        $this->db->update($this->invent_detail,$data_update_dep);
    }

    function get_total_account_type($CompanyCode,$period_month,$period_year,$account_type){

        $this->db->select('cg.BranchCode,cg.GLSaldoBalance'.$period_month.' as balance,sub.AccountTypeName as sub_type_name,ac.AccountTypeName as ac_type_name');
        $this->db->from($this->comp_gl.' as cg');
        $this->db->join($this->account_gl.' as ag', 'ag.AccountNumber = cg.AccountNumber', 'INNER');
        $this->db->join($this->sub_type . ' as sub', 'sub.AccountSubTypeID = ag.AccountSubTypeID', 'INNER');
        $this->db->join($this->acc_type . ' as ac', 'ac.AccountTypeID = sub.AccountTypeID', 'INNER');
        $this->db->where('cg.CompanyCode',$CompanyCode);
        //$this->db->where('ag.CurrencyCode',$currency_code);
        $this->db->where('cg.GLSaldoYears', $period_year);
        $this->db->where_in('ac.AccountTypeName', $account_type);
        // $this->db->where('ag.AccountChild', 1);
        $this->db->where('cg.TransStatus', 2);

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

    function get_account_type($CompanyCode,$Account_Number,$period_year){
        $this->db->select('sub.AccountTypeName as sub_type_name,ac.AccountTypeName as ac_type_name');
        $this->db->from($this->comp_gl.' as cg');
        $this->db->join($this->account_gl.' as ag', 'ag.AccountNumber = cg.AccountNumber', 'INNER');
        $this->db->join($this->sub_type . ' as sub', 'sub.AccountSubTypeID = ag.AccountSubTypeID', 'INNER');
        $this->db->join($this->acc_type . ' as ac', 'ac.AccountTypeID = sub.AccountTypeID', 'INNER');
        $this->db->where('cg.AccountNumber',$Account_Number);
        $this->db->where('cg.CompanyCode',$CompanyCode);
        $this->db->where('cg.GLSaldoYears',$period_year);
        $this->db->where('cg.TransStatus', 2);

        $query  = $this->db->get()->result_array();
        if(count($query)>0){
            return $query[0]['ac_type_name'];
        }else{
            return false;
        }
        
    }

    function cek_is_ho_branch($branch_code){
        $this->db->select('BranchStatus');
        $this->db->from($this->branch);
        $this->db->where('BranchCode',$branch_code);

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

        $row    = $query->row();

        return $row->BranchStatus;
          
    }

    function m_get_total_transaction($bank,$branch,$from_date,$end_date){
        $this->db->select('sum((CASE WHEN Saldo_Debet=0 THEN (Amount_Currency * -1) ELSE Amount_Currency END)) as Amount_Currency');
        $this->db->from($this->av_all_transaction);
        $this->db->where('Branch_Code',$branch);
        $this->db->where('Account_Number',$bank);
        $this->db->where('Journal_Date >=',$from_date);
        $this->db->where('Journal_Date <=',$end_date);

        $Query              = $this->db->get()->result_array();

        if(count($Query)>0){
            return $Query[0]['Amount_Currency'];
        }else{
            return 0;
        }
    }

    function get_all_transaction_by_bsstatus($company_code,$from_date,$end_date,$bsstatus){
        $this->db->select('trans.Branch_Code,trans.Account_Number,trans.Trans_Type,trans.CurrencyRate,trans.Sub_Type_Name,trans.Acc_Type_Name,
            (CASE WHEN trans.Acc_Type_Name="Cash / Bank" THEN (CASE WHEN Saldo_Debet=0 THEN (Amount_Currency) ELSE Amount_Currency END) ELSE 
                (CASE WHEN Saldo_Debet=0 THEN (Saldo_Credit) ELSE Saldo_Debet END) END) as Amount_Currency,
        (CASE WHEN trans.Saldo_Debet=0 THEN "Credit" ELSE "Debet" END) as Debet_Credit');
        $this->db->from($this->av_all_transaction.' as trans');
        $this->db->join($this->account_gl.' as cg','trans.Account_Number=cg.AccountNumber','left');
        $this->db->join($this->sub_type.' as sub','trans.Sub_Type_ID=sub.AccountSubTypeID','left');
        $this->db->where('sub.AccountBSStatus',$bsstatus);
        $this->db->where('trans.CompanyCode',$company_code);
        $this->db->where('trans.Journal_Date >=',$from_date);
        $this->db->where('trans.Journal_Date <=',$end_date);
        $this->db->order_by('trans.Branch_Code,trans.Account_Number');
        //$this->db->where('cg.GLSaldoYears =',$period_year);
        //$this->db->where('cg.TransStatus =',2);

        $Query              = $this->db->get()->result();

        return $Query;
    }

    function get_balancesheet($company_code,$period_year){

        $this->db->select('cg.BranchCode,cg.AccountNumber,cg.GLSaldoBSheet');
        $this->db->from($this->comp_gl.' as cg');
        $this->db->join($this->account_gl.' as ag','cg.AccountNumber=ag.AccountNumber','inner');
        $this->db->join($this->sub_type.' as sub','ag.AccountSubTypeID=sub.AccountSubTypeID','inner');
        $this->db->where('sub.AccountBSStatus',1);
        $this->db->where('cg.CompanyCode',$company_code);
        $this->db->where('cg.GLSaldoYears',$period_year);
        $this->db->where('cg.TransStatus',2);

        $query = $this->db->get()->result();

        return $query;
    }

    function update_gl_saldo_balance($branch,$account,$balance,$month,$period_year){
        //$this->db->_protect_identifiers = FALSE;
        $this->db->set('GLSaldoBalance'.$month,$balance);
        $this->db->where('AccountNumber',$account);
        $this->db->where('BranchCode',$branch);
        $this->db->where('GLSaldoYears',$period_year);
        $this->db->where('TransStatus',2);
        $this->db->update($this->comp_gl);
    }

    function insert_group_period($data_period){
        $this->db->insert($this->table,$data_period);
    }

    function update_master_currency_rate($CompanyCode,$CurrencyID,$data_currency){
        $this->db->update($this->currency,$data_currency,array('CurrencyID'=>$CurrencyID,'CompanyCode'=>$CompanyCode));
    }

    function get_account_diff($bank_currency_code,$branch,$period_year){
        $this->db->select('cg.AccountNumber');
        $this->db->from($this->comp_gl.' as cg');
        $this->db->join($this->account_gl.' as ag','ag.AccountNumber=cg.AccountNumber','inner');
        $this->db->where('ag.AccountName','Selisih Kurs Akhir Periode '.$bank_currency_code);
        $this->db->where('cg.BranchCode',$branch);
        $this->db->where('cg.TransStatus',2);
        $this->db->where('cg.GLSaldoYears',$period_year);

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

        if($query->num_rows() > 0){
            $row    = $query->row();
            return $row->AccountNumber;
        }else{
            return false;
        }
    }

    function get_all_transaction_by_date($company_code,$begin_date,$end_date,$account_type){
        $this->db->select("trans.Account_Number,trans.Account_Name,trans.Sub_Type_Name,trans.Acc_Type_Name,(CASE WHEN trans.Saldo_Debet=0 THEN trans.Saldo_Credit ELSE trans.Saldo_Debet END ) as balance,(CASE WHEN trans.Saldo_Debet=0 THEN 'Credit' ELSE 'Debet' END) AS type_trans");
        $this->db->from($this->av_all_transaction.' as trans');
        $this->db->where_in('trans.Acc_Type_Name',$account_type);
        $this->db->where('trans.Journal_Date >=',$begin_date);
        $this->db->where('trans.Journal_Date <=',$end_date);
        $this->db->where('trans.CompanyCode',$company_code);

        $this->db->order_by('trans.Acc_Type_Name,trans.Sub_Type_Name,trans.Account_Name','ASC');
        $query  = $this->db->get()->result();
                
        return $query;
    }

    function get_saldo_opening_balance($branch,$AccountNumber,$month,$year,$last_period){

        if($last_period == false){

            $this->db->select('GLSaldoOpening as Amount_Currency');

        }else{

            $this->db->select('GLSaldoBalance'.$month.' as Amount_Currency');
        } 
        
        $this->db->from($this->comp_gl.' as cg');
        $this->db->join($this->account_gl.' as ag','ag.AccountNumber=cg.AccountNumber','inner');
        //$this->db->join($this->currency.' as cr','cr.CurrencyCode=ag.CurrencyCode','inner');
        $this->db->join($this->sub_type.' as sub','sub.AccountSubTypeID=ag.AccountSubTypeID','inner');
        $this->db->join($this->acc_type.' as ac','ac.AccountTypeID=sub.AccountTypeID','inner');
        $this->db->where('cg.AccountNumber',$AccountNumber);
        $this->db->where('cg.GLSaldoYears',$year);
        $this->db->where('cg.TransStatus',2);
        $this->db->where('cg.BranchCode',$branch);

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

        if(count($query) > 0){
            return $query[0]['Amount_Currency'];
        }else{
            return 0;
        }
    }

    function get_total_amount_period($branch,$AssetCode,$accum_account){
        $this->db->select('sum(dt.JVchCreditSaldo) as amount_period');
        $this->db->from($this->ledger_detail.' as dt');   
        $this->db->join($this->gnl_ledger.' as gr','gr.JVchNumber=dt.JVchNumber','inner');
        $this->db->where('dt.AssetCode',$AssetCode);
        // $this->db->where('dt.JVchAccount',$accum_account);
        $this->db->where('gr.TransStatus',2);
        $this->db->where('gr.JVchType',0);
        $this->db->where('gr.BranchCode',$branch);

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

        if($query->num_rows()>0){
            $row = $query->row();
            return $row->amount_period;
        }else{
            return 0;
        }  
    }

    function count_period_by_itemcode($branch,$item_code,$AdvanceAccount){
        $this->db->select('count(dt.AssetCode) as total_period')  ;
        $this->db->from($this->ledger_detail.' as dt');   
        $this->db->join($this->gnl_ledger.' as gr','gr.JVchNumber=dt.JVchNumber','inner');
        $this->db->where('dt.AssetCode',$item_code);
        //$this->db->where('dt.JVchAccount',$AdvanceAccount);
        $this->db->where('gr.TransStatus',2);
        $this->db->where('gr.JVchType',0);
        $this->db->where('gr.BranchCode',$branch);

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

        if($query->num_rows()>0){
            $row = $query->row();

            return $row->total_period;
        }else{
            return 0;
        }  
    }

    function m_process_period_end($trans_data){

        $this->db->trans_start();

        $this->selisih_kurs($trans_data);
        $this->advance_payment($trans_data);
        $this->fixed_asset($trans_data);
        $this->profit_lost($trans_data);
        $this->new_balancesheet($trans_data);
        $this->insert_group_period($trans_data['insert_group_period']['data']);
        $this->master_currency($trans_data);
        $this->history_currency($trans_data);

        $this->db->trans_complete();

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

    }

    function get_history_currency($PeriodEnd,$CompanyCode){
        $this->db->select('hc.*,cr.CurrencyCode');
        $this->db->from($this->history_currency.' as hc');
        $this->db->join($this->currency.' as cr','cr.CurrencyID=hc.CurrencyID','inner');
        $this->db->where('hc.CompanyCode',$CompanyCode);
        $this->db->where('hc.PeriodEnd',$PeriodEnd);

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

    function history_currency($trans_data){
       for($x=0;$x<count($trans_data['history_currency']['data']);$x++){
             $this->db->insert($this->history_currency,$trans_data['history_currency']['data'][$x]);
       } 
    }

    function selisih_kurs($trans_data){
        for($x=0;$x<$trans_data['selisih_kurs']['total_group'];$x++){
            $this->insert_group_jvb($trans_data['selisih_kurs']['insert_group_jvb']['data'][$x]);
        }

        for($x=0;$x<count($trans_data['selisih_kurs']['insert_detail_jvb']['data']);$x++){
            $this->insert_detail_jvb($trans_data['selisih_kurs']['insert_detail_jvb']['data'][$x]);
            $this->insert_journal_jvb($trans_data['selisih_kurs']['insert_journal_jvb']['data'][$x]);
        }

        for($x=0;$x<$trans_data['selisih_kurs']['total_group'];$x++){
            $this->update_group_jvb($trans_data['selisih_kurs']['update_group_jvb']['TransNumber'][$x],$trans_data['selisih_kurs']['update_group_jvb']['data'][$x]);
        }
    }

    function advance_payment($trans_data){

        if($trans_data['advance_payment']['total_group'] > 0){

            /*====================== insert group ================================*/
            for($x=0;$x<$trans_data['advance_payment']['total_group'];$x++){
                $this->insert_group_jvb($trans_data['advance_payment']['insert_group_jvb']['data'][$x]);
            }

            /*====================== insert detail dan journal ======================*/
            for($x=0;$x<$trans_data['advance_payment']['total_detail'];$x++){
                $this->insert_detail_jvb($trans_data['advance_payment']['insert_detail_jvb']['data'][$x]);
                $this->insert_journal_jvb($trans_data['advance_payment']['insert_journal_jvb']['data'][$x]);
            }

            /*====================== update group ================================*/
            for($x=0;$x<$trans_data['advance_payment']['total_group'];$x++){
                $this->update_group_jvb($trans_data['advance_payment']['update_group_jvb']['TransNumber'][$x],$trans_data['advance_payment']['update_group_jvb']['data'][$x]);
            }
        }
    }

    function fixed_asset($trans_data){
        
        if($trans_data['fixed_asset']['total_group'] > 0){

            /*====================== insert group ================================*/
            for($x=0;$x<$trans_data['fixed_asset']['total_group'];$x++){
                $this->insert_group_jvb($trans_data['fixed_asset']['insert_group_jvb']['data'][$x]);
            }

            /*====================== insert detail dan journal ======================*/
            for($x=0;$x<$trans_data['fixed_asset']['total_detail'];$x++){
                $this->insert_detail_jvb($trans_data['fixed_asset']['insert_detail_jvb']['data'][$x]);
                $this->insert_journal_jvb($trans_data['fixed_asset']['insert_journal_jvb']['data'][$x]);
            }

            /*====================== update group ================================*/
            for($x=0;$x<$trans_data['fixed_asset']['total_group'];$x++){
                $this->update_group_jvb($trans_data['fixed_asset']['update_group_jvb']['TransNumber'][$x],$trans_data['fixed_asset']['update_group_jvb']['data'][$x]);
            }
        }
    }

    function profit_lost($trans_data){
        
        /*=========================== insert group =======================================*/
        $this->insert_group_jvb($trans_data['profit_lost']['insert_group_jvb']['data']);

        /*=========================== insert detail ==========================*/
        for($x=0;$x<count($trans_data['profit_lost']['insert_detail_jvb']['data']);$x++){
            $this->insert_detail_jvb($trans_data['profit_lost']['insert_detail_jvb']['data'][$x]);
            $this->insert_journal_jvb($trans_data['profit_lost']['insert_journal_jvb']['data'][$x]);
        }

        /*=========================== update group ============================*/
        $this->update_group_jvb($trans_data['profit_lost']['update_group_jvb']['TransNumber'],$trans_data['profit_lost']['update_group_jvb']['data']);
    
    }

    function master_currency($trans_data){
        for($x=0;$x<count($trans_data['master_currency']['data']);$x++){
            $this->update_master_currency_rate($trans_data['master_currency']['companycode'][$x],$trans_data['master_currency']['CurrencyID'][$x],$trans_data['master_currency']['data'][$x]);
        }
    }

    function new_balancesheet($trans_data){
        for($x=0;$x<count($trans_data['new_balancesheet']['branch']);$x++){
            $this->update_gl_saldo_balance($trans_data['new_balancesheet']['branch'][$x],$trans_data['new_balancesheet']['account'][$x], $trans_data['new_balancesheet']['balance'][$x],$trans_data['new_balancesheet']['period_month'][$x],$trans_data['new_balancesheet']['period_year'][$x]);
        }
    }

    function get_all_jv_transaction($company_code,$begin_date,$end_date,$type,$PeriodEnd){
        $this->db->select('gr.JVchNumber');
        $this->db->from($this->gnl_ledger.' as gr');
        $this->db->join($this->ledger_detail.' as dt','dt.JVchNumber=gr.JVchNumber','inner');
        $this->db->join($this->branch.' as br','br.BranchCode=gr.BranchCode','inner');
        $this->db->join($this->company.' as cp','cp.CompanyCode=br.CompanyCode','inner');
        $this->db->where('gr.TransStatus',2);
        $this->db->where('dt.PeriodEnd',$PeriodEnd);
        $this->db->where('gr.JVchType',$type);// type end period
        $this->db->where('gr.CreatedAt >=',$begin_date);
        $this->db->where('gr.CreatedAt <=',$end_date);
        $this->db->where('cp.CompanyCode',$company_code);
        $this->db->group_by('gr.JVchNumber');

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

        $result = [];

        for($x=0;$x<count($query);$x++){
            $result[] = $query[$x]['JVchNumber'];
        }

        return $result;
    }

    function m_reverse_period_end($trans_data){
        $this->db->trans_start();
        
        /*======================= suspend journal voucher ========================================*/
        for($x=0;$x<$trans_data['suspend_jv']['total'];$x++){
            $this->suspend_journal($trans_data['suspend_jv']['data'][$x],$trans_data['suspend_jv']['TransNumber'][$x]);
        }

        /*========================= reverse balancesheet ================================*/
        $this->reverse_balancesheet($trans_data['reverse_balancesheet']['arr_branch'],$trans_data['reverse_balancesheet']['month'],$trans_data['reverse_balancesheet']['year'],1);
        
        /*========================= update master currency rate from history ===============================*/
        $this->master_currency($trans_data);

        $this->delete_history_currency($trans_data['delete_history_currency']['PeriodEnd']);

        /*=============================== delete group period end ===========================*/
        $this->delete_group($trans_data['delete_group']['TransNumber']);

        $this->db->trans_complete();

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

    function suspend_journal($data_suspend,$JVchNumber){
        $this->db->update($this->gnl_ledger,$data_suspend,array('JVchNumber'=>$JVchNumber));
    }

    function reverse_balancesheet($arr_branch,$month,$year,$bsstatus){
        $this->db->set('cg.GLSaldoBalance'.$month,0);
        $this->db->where('cg.GLSaldoYears',$year);
        $this->db->where('cg.TransStatus',2);
        $this->db->where('sub.AccountBSStatus',$bsstatus);
        $this->db->where_in('cg.BranchCode',$arr_branch);
        $this->db->update($this->comp_gl.' as cg INNER JOIN '.$this->account_gl.' as ag ON cg.AccountNumber=ag.AccountNumber INNER JOIN '.$this->sub_type.' as sub ON sub.AccountSubTypeID=ag.AccountSubTypeID');
    }

    function delete_group($TransNumber){
        $this->db->where('PeriodEnd',$TransNumber);
        $this->db->delete($this->table);
    }

    function delete_history_currency($TransNumber){
        $this->db->where('PeriodEnd',$TransNumber);
        $this->db->delete($this->history_currency);
    }

    function check_company_create($company_code,$period_month,$period_year){
        $this->db->select('cp.CompanyDate');
        $this->db->from($this->company.' as cp');
        $this->db->where('cp.CompanyCode',$company_code);
        $this->db->where('MONTH(cp.CompanyDate)',$period_month);
        $this->db->where('YEAR(cp.CompanyDate)',$period_year);

        $CountResult =  $this->db->count_all_results();     
        if($CountResult > 0){
            return TRUE;
        }else{
            return FALSE;
        }  
    }
}