<?php

/*---------------- Created By Daswan ---------------------------------------*/
/*---------------- 20/11/2015 ----------------------------------------------*/

class M_index_financial_statement extends CI_Model{
    public $trans_journal   = 'av_trans_journal';
    public $comp_gl         = 'at_compaccountgl';
    public $acc_gl          = 'am_accountgl';
    
    public $branch          = 'am_branch';
    public $company         = 'am_company';
    public $currency        = 'am_currency';
    public $all_transaction = 'av_all_transaction';
    public $sub_type        = 'am_accountsubtype';
    public $acc_type        = 'am_accounttype';
    public $period          = 'at_periodend';
    public $history_currency        = 'ah_currency_history';
    public $vhistory_currency       = 'v_history_currency';

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

        $this->db->select('sum(cg.GLSaldoBalance'.$period_month.') as total_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.BranchCode',$branch);
        //$this->db->where('ag.CurrencyCode',$currency_code);
        $this->db->where('cg.GLSaldoYears', $period_year);
        $this->db->where('acc.AccountTypeName', $account_type);
        $this->db->where('ag.AccountChild', 1);
        $this->db->where('cg.TransStatus', 2);
        $query  = $this->db->get()->result_array();
                
        return $query[0]['total_balance'];
    }

    // function get_total_account_type_standard($branch,$begin_date,$end_date,$account_type,$pm_debet,$pm_kredit){

    //     $this->db->select("sum((CASE WHEN trans.Saldo_Debet=0 THEN (trans.Saldo_Credit * $pm_kredit) ELSE (trans.Saldo_Debet * $pm_debet) END )) as total_balance");
    //     $this->db->from($this->all_transaction.' as trans');
    //     $this->db->where('trans.Sub_Type_Name',$account_type);
    //     $this->db->where('trans.Journal_Date >=',$begin_date);
    //     $this->db->where('trans.Journal_Date <=',$end_date);

    //     $query  = $this->db->get()->result_array();
                
    //     return $query[0]['total_balance'];
    // }

    function get_all_transaction_by_date($branch,$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->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.Branch_Code',$branch);

        $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_all_transaction_by_month_year($branch,$begin_date,$end_date,$account_type){
        $this->db->_protect_identifiers = FALSE;
        $this->db->select("SUBSTRING(trans.Journal_Date,1,4) as year, SUBSTRING(trans.Journal_Date,6,2) as month,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->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.Branch_Code',$branch);

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

     function get_all_transaction_by_company_date($company,$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->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);

        $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_transaction_by_account($branch,$Account_Number,$begin_date,$end_date,$is_consolidation){
        $this->db->select('trans.Journal_Date,trans.Journal_Number,trans.Journal_Desc,trans.Saldo_Debet,trans.Saldo_Credit,trans.Trans_Type');
        $this->db->from($this->all_transaction.' as trans');
        if($is_consolidation){
            
            $CompanyCode    = $this->session->userdata('company');

            $this->db->where('trans.CompanyCode',$CompanyCode);
        }else{
            $this->db->where('trans.Branch_Code',$branch);
        }
        
        $this->db->where('trans.Account_Number',$Account_Number);
        $this->db->where('trans.Journal_Date >=',$begin_date);
        $this->db->where('trans.Journal_Date <=',$end_date);
        $this->db->order_by('trans.Journal_Date');

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

        return $query;
    }

    function get_transaction_by_account_company($CompanyCode,$Account_Number){
        $this->db->select('br.BranchName,trans.Journal_Date,trans.Journal_Number,trans.Journal_Desc,trans.Saldo_Debet,trans.Saldo_Credit,trans.Trans_Type');
        $this->db->from($this->all_transaction.' as trans');
        $this->db->join($this->branch.' as br','br.BranchCode=trans.Branch_Code');
        $this->db->where('trans.CompanyCode',$CompanyCode);
        $this->db->where('trans.Account_Number',$Account_Number);
        $this->db->order_by('trans.Journal_Date');

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

        return $query;
    }

    function get_account_name($Account_Number){
        $this->db->select('ag.AccountName');
        $this->db->from($this->acc_gl.' as ag');
        $this->db->where('ag.AccountNumber',$Account_Number);

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

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

    function get_coa_balance_sheet1($period_month, $period_year,$branch_code=''){
        $company_code   = $this->session->userdata('company');
        $branch_code    = $branch_code == '' ? $this->session->userdata('branch'):$branch_code;
        $branch_status  = $this->session->userdata('branch_status');
        
        $this->db->_protect_identifiers = FALSE;
        
        $this->db->select("br.BranchName,cg.AccountNumber,ag.AccountName,ag.AccountLevel,ag.AccountParent,ag.AccountChild,
            ag.AccountChild,sub.AccountTypeName as Sub_Type_Name,
            ac.AccountTypeName as Acc_Type_Name,
            (CASE WHEN (ac.AccountTypeName = 'Cash / Bank') AND (ag.CurrencyCode != 'IDR') THEN 
                CONCAT(cr.CurrencyName,'-','Rate(',cr.CurrencyRate,')','-','Saldo(',cg.GLSaldoBalance$period_month,')')
            ELSE
                '' END) AS Description,
            (CASE WHEN sub.AccountBSStatus = 1 THEN 
                (CASE WHEN ac.AccountTypeName = 'Cash / Bank' THEN (cg.GLSaldoBalance$period_month * cr.CurrencyRate) ELSE cg.GLSaldoBalance$period_month END)
            ELSE
                GLSaldoBalance$period_month END) as GLSaldoBalance");
        $this->db->from($this->comp_gl.' as cg');
        $this->db->join($this->acc_gl.' as ag','ag.AccountNumber=cg.AccountNumber','inner');
        $this->db->join($this->company.' as com','com.CompanyCode=cg.CompanyCode','inner');
        $this->db->join($this->branch.' as br','br.BranchCode=cg.BranchCode','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->join($this->currency.' as cr','cr.CurrencyCode=ag.CurrencyCode and cr.CompanyCode=cg.CompanyCode','inner');
        //$this->db->where_not_in('cg.AccountNumber',
            //array('5-0-00-000-00','3-0-00-000-00','6-0-00-000-00','8-0-00-000-00','7-0-00-000-00','4-0-00-000-00','1-3-00-000-00'));
        $this->db->where('cg.CompanyCode',$company_code);
        $this->db->where('cg.BranchCode',$branch_code);
        $this->db->where('cg.GLSaldoYears',$period_year);
        $this->db->where_not_in('ac.AccountTypeName',array('Revenue','COGS / COGM (Cost Of Goods Made)','Expense','Other Revenue','Other Expense'));
        $this->db->where('cg.TransStatus',2);

        $this->db->order_by('cg.AccountNumber','ASC');

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

        return $query;
    }

    function get_coa_balance_sheet($period_month, $period_year,$branch_code='',$PeriodEnd,$is_periodend,$is_lastendperiod){
        $company_code   = $this->session->userdata('company');
        $branch_code    = $branch_code == '' ? $this->session->userdata('branch'):$branch_code;
        $branch_status  = $this->session->userdata('branch_status');
        if(abs($period_month) < 12){
            $PeriodEnd      = $period_year.(abs($period_month) + 1);
        }else{
            $PeriodEnd      = (abs($period_year) + 1).(abs(1));
        }
        
        $this->db->_protect_identifiers = FALSE;
        
        $this->db->select("br.BranchName,cg.AccountNumber,ag.AccountName,ag.AccountLevel,ag.AccountParent,ag.AccountChild,
            ag.AccountChild,sub.AccountTypeName as Sub_Type_Name,
            ac.AccountTypeName as Acc_Type_Name,
            (CASE WHEN (ac.AccountTypeName = 'Cash / Bank') AND (ag.CurrencyCode != 'IDR') THEN 
                CONCAT(cr.CurrencyName,'-','Rate(',cr.CurrencyRate,')','-','Saldo(',cg.GLSaldoBalance$period_month,')')
            ELSE
                '' END) AS Description,
            (CASE WHEN sub.AccountBSStatus = 1 THEN 
                (CASE WHEN ac.AccountTypeName = 'Cash / Bank' THEN (cg.GLSaldoBalance$period_month * cr.CurrencyRate) ELSE cg.GLSaldoBalance$period_month END)
            ELSE
                GLSaldoBalance$period_month END) as GLSaldoBalance");
        $this->db->from($this->comp_gl.' as cg');
        $this->db->join($this->acc_gl.' as ag','ag.AccountNumber=cg.AccountNumber','inner');
        $this->db->join($this->company.' as com','com.CompanyCode=cg.CompanyCode','inner');
        $this->db->join($this->branch.' as br','br.BranchCode=cg.BranchCode','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');
        if($is_periodend){
            if($is_lastendperiod){
                $this->db->join($this->currency.' as cr','cr.CurrencyCode=ag.CurrencyCode and cr.CompanyCode=cg.CompanyCode','inner');
            }else{
                $this->db->join($this->vhistory_currency.' as cr','cr.CurrencyCode=ag.CurrencyCode and cr.CompanyCode=cg.CompanyCode and cr.PeriodEnd='.$PeriodEnd,'inner');
            }
        }else{
            $this->db->join($this->currency.' as cr','cr.CurrencyCode=ag.CurrencyCode and cr.CompanyCode=cg.CompanyCode','inner');
        }

        //$this->db->where_not_in('cg.AccountNumber',
            //array('5-0-00-000-00','3-0-00-000-00','6-0-00-000-00','8-0-00-000-00','7-0-00-000-00','4-0-00-000-00','1-3-00-000-00'));
        $this->db->where('cg.CompanyCode',$company_code);
        $this->db->where('cg.BranchCode',$branch_code);
        $this->db->where('cg.GLSaldoYears',$period_year);
        $this->db->where_not_in('ac.AccountTypeName',array('Revenue','COGS / COGM (Cost Of Goods Made)','Expense','Other Revenue','Other Expense'));
        $this->db->where('cg.TransStatus',2);

        $this->db->order_by('cg.AccountNumber','ASC');

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

        return $query;
    }

    function get_coa_balance_sheet_consolidation($period_month,$period_year,$PeriodEnd,$is_periodend,$is_lastendperiod){
        $company_code   = $this->session->userdata('company');
        $branch_code    = $this->session->userdata('branch');
        $branch_status  = $this->session->userdata('branch_status');

        if(abs($period_month) < 12){
            $PeriodEnd      = $period_year.(abs($period_month) + 1);
        }else{
            $PeriodEnd      = (abs($period_year) + 1).(abs(1));
        }
        
        $this->db->_protect_identifiers = FALSE;
        
        $this->db->select("br.BranchName,cg.AccountNumber,ag.AccountName,ag.AccountLevel,ag.AccountParent,ag.AccountChild,
            ag.AccountChild,sub.AccountTypeName as Sub_Type_Name,
            ac.AccountTypeName as Acc_Type_Name,
            (CASE WHEN (ac.AccountTypeName = 'Cash / Bank') AND (ag.CurrencyCode != 'IDR') THEN 
                CONCAT(cr.CurrencyName,'-','Rate(',cr.CurrencyRate,')','-','Saldo(',cg.GLSaldoBalance$period_month,')')
            ELSE
                '' END) AS Description,
            (CASE WHEN sub.AccountBSStatus = 1 THEN 
                (CASE WHEN ac.AccountTypeName = 'Cash / Bank' THEN (cg.GLSaldoBalance$period_month * cr.CurrencyRate) ELSE cg.GLSaldoBalance$period_month END)
            ELSE
                GLSaldoBalance$period_month END) as GLSaldoBalance");
        $this->db->from($this->comp_gl.' as cg');
        $this->db->join($this->acc_gl.' as ag','ag.AccountNumber=cg.AccountNumber','inner');
        $this->db->join($this->company.' as com','com.CompanyCode=cg.CompanyCode','inner');
        $this->db->join($this->branch.' as br','br.BranchCode=cg.BranchCode','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');
       if($is_periodend){
            if($is_lastendperiod){
                $this->db->join($this->currency.' as cr','cr.CurrencyCode=ag.CurrencyCode and cr.CompanyCode=cg.CompanyCode','inner');
            }else{
                $this->db->join($this->vhistory_currency.' as cr','cr.CurrencyCode=ag.CurrencyCode and cr.CompanyCode=cg.CompanyCode and cr.PeriodEnd='.$PeriodEnd,'inner');
            }
        }else{
            $this->db->join($this->currency.' as cr','cr.CurrencyCode=ag.CurrencyCode and cr.CompanyCode=cg.CompanyCode','inner');
        }

        //$this->db->where_not_in('cg.AccountNumber',
            //array('5-0-00-000-00','3-0-00-000-00','6-0-00-000-00','8-0-00-000-00','7-0-00-000-00','4-0-00-000-00','1-3-00-000-00'));
        $this->db->where('cg.CompanyCode',$company_code);
        $this->db->where('cg.GLSaldoYears',$period_year);
        $this->db->where_not_in('ac.AccountTypeName',array('Revenue','COGS / COGM (Cost Of Goods Made)','Expense','Other Revenue','Other Expense'));
        $this->db->where('cg.TransStatus',2);

        $this->db->order_by('cg.AccountNumber','ASC');

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

        return $query;
    }

    function get_total_accounttype(){
        $this->db->select('COUNT(AccountTypeID) as total');

        $query = $this->db->get($this->acc_type);
        $row = $query->row();
        
        return $row->total;
    }

    function get_total_accountsubtype(){
        $this->db->select('COUNT(AccountSubTypeID) as total');

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

        return $row->total;
    }

    function get_journal_balancesheet($branch,$AccountNumber,$from_date,$end_date){

        $this->db->select('br.BranchName,trans.Journal_Date,trans.Journal_Number,trans.Journal_Desc,trans.CurrencyRate,
            (CASE WHEN trans.Acc_Type_Name="Cash / Bank" THEN 
                (CASE WHEN trans.Saldo_Debet=0 THEN 0 ELSE (Amount_Currency) END) ELSE trans.Saldo_Debet END) as Saldo_Debet,
            (CASE WHEN trans.Acc_Type_Name="Cash / Bank" THEN 
                (CASE WHEN trans.Saldo_Credit=0 THEN 0 ELSE (Amount_Currency) END) ELSE trans.Saldo_Credit END) as Saldo_Credit,
            trans.Trans_Type');
        $this->db->from($this->all_transaction.' as trans');
        $this->db->join($this->branch.' as br','br.BranchCode=trans.Branch_Code','inner');
        // $this->db->join($this->acc_gl.' as ag','ag.AccountNumber=trans.Account_Number','inner');
        $this->db->where('trans.Branch_Code',$branch);
        $this->db->where('trans.Account_Number',$AccountNumber);
        $this->db->where('trans.Journal_Date >=',$from_date);
        $this->db->where('trans.Journal_Date <=',$end_date);
        $this->db->order_by('trans.Journal_Date');

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

        return $query;
    }

    function get_journal_balancesheet_consolidation($company,$AccountNumber,$from_date,$end_date){

        $this->db->select('br.BranchCode,br.BranchName,trans.Journal_Date,trans.Journal_Number,trans.Journal_Desc,trans.CurrencyRate,
            (CASE WHEN trans.Acc_Type_Name="Cash / Bank" THEN 
                (CASE WHEN trans.Saldo_Debet=0 THEN 0 ELSE (Amount_Currency) END) ELSE trans.Saldo_Debet END) as Saldo_Debet,
            (CASE WHEN trans.Acc_Type_Name="Cash / Bank" THEN 
                (CASE WHEN trans.Saldo_Credit=0 THEN 0 ELSE (Amount_Currency) END) ELSE trans.Saldo_Credit END) as Saldo_Credit,
            trans.Trans_Type');
        $this->db->from($this->all_transaction.' as trans');
        $this->db->join($this->branch.' as br','br.BranchCode=trans.Branch_Code','inner');
        // $this->db->join($this->acc_gl.' as ag','ag.AccountNumber=trans.Account_Number','inner');
        $this->db->where('trans.CompanyCode',$company);
        $this->db->where('trans.Account_Number',$AccountNumber);
        $this->db->where('trans.Journal_Date >=',$from_date);
        $this->db->where('trans.Journal_Date <=',$end_date);
        $this->db->order_by('trans.Journal_Date');

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

        return $query;
    }

    function get_journal_balancesheet_compare($branch,$AccountNumber,$begin_date_0,$last_date_0,$begin_date_1,$last_date_1){

        $where = "(((trans.Journal_Date >= '$begin_date_0') and (trans.Journal_Date <= '$last_date_0')) or ((trans.Journal_Date >= '$begin_date_1') and (trans.Journal_Date <= '$last_date_1')))";
        
        $this->db->select('br.BranchName,trans.Journal_Date,trans.Journal_Number,trans.Journal_Desc,trans.CurrencyRate,
            (CASE WHEN trans.Acc_Type_Name="Cash / Bank" THEN 
                (CASE WHEN trans.Saldo_Debet=0 THEN 0 ELSE (Amount_Currency) END) ELSE trans.Saldo_Debet END) as Saldo_Debet,
            (CASE WHEN trans.Acc_Type_Name="Cash / Bank" THEN 
                (CASE WHEN trans.Saldo_Credit=0 THEN 0 ELSE (Amount_Currency) END) ELSE trans.Saldo_Credit END) as Saldo_Credit,
            trans.Trans_Type');

        $this->db->from($this->all_transaction.' as trans');
        $this->db->join($this->branch.' as br','br.BranchCode=trans.Branch_Code','inner');

        $this->db->where('trans.Branch_Code',$branch);
        $this->db->where('trans.Account_Number',$AccountNumber);
        $this->db->where($where);
        $this->db->order_by('trans.Journal_Date','ASC');

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

        return $query;
    }

    function get_journal_balancesheet_compare_consolidation($company,$AccountNumber,$begin_date_0,$last_date_0,$begin_date_1,$last_date_1){

        $where = "(((trans.Journal_Date >= '$begin_date_0') and (trans.Journal_Date <= '$last_date_0')) or ((trans.Journal_Date >= '$begin_date_1') and (trans.Journal_Date <= '$last_date_1')))";
        
        $this->db->select('br.BranchName,trans.Journal_Date,trans.Journal_Number,trans.Journal_Desc,trans.CurrencyRate,
            (CASE WHEN trans.Acc_Type_Name="Cash / Bank" THEN 
                (CASE WHEN trans.Saldo_Debet=0 THEN 0 ELSE (Amount_Currency) END) ELSE trans.Saldo_Debet END) as Saldo_Debet,
            (CASE WHEN trans.Acc_Type_Name="Cash / Bank" THEN 
                (CASE WHEN trans.Saldo_Credit=0 THEN 0 ELSE (Amount_Currency) END) ELSE trans.Saldo_Credit END) as Saldo_Credit,
            trans.Trans_Type');

        $this->db->from($this->all_transaction.' as trans');
        $this->db->join($this->branch.' as br','br.BranchCode=trans.Branch_Code','inner');

        $this->db->where('trans.CompanyCode',$company);
        $this->db->where('trans.Account_Number',$AccountNumber);
        $this->db->where($where);
        $this->db->order_by('trans.Journal_Date','ASC');

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

        return $query;
    }

    function get_journal_balancesheet_consolidation_1($company_code,$AccountNumber,$from_date,$end_date){
        $this->db->select('br.BranchName,trans.Journal_Date,trans.Journal_Number,trans.Journal_Desc,trans.Saldo_Debet,trans.Saldo_Credit,trans.Trans_Type');
        $this->db->from($this->all_transaction.' as trans');
        $this->db->join($this->branch.' as br','br.BranchCode=trans.Branch_Code');
        $this->db->where('trans.CompanyCode',$company_code);
        $this->db->where('trans.Account_Number',$AccountNumber);
        $this->db->where('trans.Journal_Date >=',$from_date);
        $this->db->where('trans.Journal_Date <=',$end_date);
        $this->db->order_by('trans.Journal_Date');

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

        return $query;   
    }

    function get_last_endperiod($period){
        $this->db->select('PeriodEnd');
        $this->db->from($this->period);
        $this->db->order_by('PeriodEnd','DESC');
        $this->db->limit(1);

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

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

    function get_saldo_opening_balance($branch,$AccountNumber,$month,$year,$is_endperiod,$is_lastendperiod){

        if(abs($month) < 12){
            $PeriodEnd      = $year.(abs($month) + 1);
        }else{
            $PeriodEnd      = (abs($year) + 1).(abs(1));
        }
        
        $this->db->_protect_identifiers = FALSE;
        
        $this->db->select("
            (CASE WHEN sub.AccountBSStatus = 1 THEN 
                (CASE WHEN ac.AccountTypeName = 'Cash / Bank' THEN (cg.GLSaldoBalance$month) 
                    ELSE 
                cg.GLSaldoBalance$month END)
            ELSE
                cg.GLSaldoBalance$month END) as saldo_opening_balance");
        $this->db->from($this->comp_gl.' as cg');
        $this->db->join($this->acc_gl.' as ag','ag.AccountNumber=cg.AccountNumber','inner');
        $this->db->join($this->branch.' as br','br.BranchCode=cg.BranchCode','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');

        if($is_endperiod){
            if($is_lastendperiod){
                $this->db->join($this->currency.' as cr','cr.CurrencyCode=ag.CurrencyCode and cr.CompanyCode=cg.CompanyCode','inner');
            }else{
                $this->db->join($this->vhistory_currency.' as cr','cr.CurrencyCode=ag.CurrencyCode and cr.CompanyCode=cg.CompanyCode and cr.PeriodEnd='.$PeriodEnd,'inner');
            }
        }else{
            $this->db->join($this->currency.' as cr','cr.CurrencyCode=ag.CurrencyCode and cr.CompanyCode=cg.CompanyCode','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]['saldo_opening_balance'];
        }else{
            return 0;
        }
    }

    function get_saldo_opening_balance_consolidation($company,$AccountNumber,$month,$year,$is_endperiod,$is_lastendperiod){

        if(abs($month) < 12){
            $PeriodEnd      = $year.(abs($month) + 1);
        }else{
            $PeriodEnd      = (abs($year) + 1).(abs(1));
        }

        $this->db->select("
            (CASE WHEN sub.AccountBSStatus = 1 THEN 
                (CASE WHEN ac.AccountTypeName = 'Cash / Bank' THEN sum(cg.GLSaldoBalance$month) 
                    ELSE 
                sum(cg.GLSaldoBalance$month) END)
            ELSE
                sum(cg.GLSaldoBalance$month) END) as saldo_opening_balance");
        
        $this->db->from($this->comp_gl.' as cg');
        $this->db->join($this->acc_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');

        if($is_endperiod){
            if($is_lastendperiod){
                $this->db->join($this->currency.' as cr','cr.CurrencyCode=ag.CurrencyCode and cr.CompanyCode=cg.CompanyCode','inner');
            }else{
                $this->db->join($this->vhistory_currency.' as cr','cr.CurrencyCode=ag.CurrencyCode and cr.CompanyCode=cg.CompanyCode and cr.PeriodEnd='.$PeriodEnd,'inner');
            }
        }else{
            $this->db->join($this->currency.' as cr','cr.CurrencyCode=ag.CurrencyCode and cr.CompanyCode=cg.CompanyCode','inner');
        }

        $this->db->where('cg.AccountNumber',$AccountNumber);
        $this->db->where('cg.GLSaldoYears',$year);
        $this->db->where('cg.TransStatus',2);
        $this->db->where('cg.CompanyCode',$company);

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

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

    function get_coa_type($AccountNumber){
        $this->db->select('ac.AccountTypeName as Acc_Type_Name, sub.AccountTypeName as Sub_Type_Name');
        $this->db->from($this->acc_gl.' as ag');
        $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('ag.AccountNumber',$AccountNumber);

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

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

    function get_list_branch($company_code,$branch_status){
        if($branch_status == 0){
            $this->db->select('BranchCode,BranchName');
            $this->db->from($this->branch);
            $this->db->where('CompanyCode',$company_code);
            $this->db->where('TransStatus',1);

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

            $branch_code = $this->session->userdata('branch');

            $this->db->select('BranchCode,BranchName');
            $this->db->from($this->branch);
            $this->db->where('CompanyCode',$company_code);
            $this->db->where('BranchCode',$branch_code);
            $this->db->where('TransStatus',1);

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

    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->period)->result_array();

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

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

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

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

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