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

class M_account_journal extends CI_Model {
    public $comp_gl             = 'at_compaccountgl';
    public $branch              = 'am_branch';

    public $account_gl          = 'am_accountgl';
    public $ppay_journal        = 'at_ppayjournal';
    public $pret_journal        = 'at_pretjournal';

    public $sys_item_cat        = 'sys_itemcategory';
    public $sys_purchase_cat    = 'sys_purchasecategory';
    public $sys_sales_cat       = 'sys_salescategory';
    public $sys_expensecategory  = 'sys_expensecategory';
	
    public $sys_item_acc        = 'sys_itemaccount';
    public $sys_purchase_acc    = 'sys_purchaseaccount';
    public $sys_sales_acc       = 'sys_salesaccount';
    public $sys_service_acc     = 'sys_serviceaccount';
    public $sys_advance_acc     = 'sys_advanceaccount';
	public $sys_expenseaccount  = 'sys_expenseaccount';
	
    public $sub_type            = "am_accountsubtype";
    public $acc_type            = "am_accounttype";

    function get_sys_item_account($account_code){
        $this->db->select('sys_ia.AccountNumber, ag.AccountName');
        $this->db->from($this->sys_item_acc . ' as sys_ia');
        $this->db->join($this->comp_gl . ' as cg', 'cg.AccountNumber = sys_ia.AccountNumber', 'INNER');
        $this->db->join($this->account_gl . ' as ag', 'ag.AccountNumber = cg.AccountNumber', 'INNER');
        $this->db->join($this->branch . ' as br', 'br.BranchCode = cg.BranchCode', 'INNER');
        $this->db->where('cg.CompanyCode', $this->session->userdata('company'));
        $this->db->where('cg.BranchCode', $this->session->userdata('branch'));            
        $this->db->where('(case when br.BranchStatus = 0 then cg.PLStatus in (0, 1) else cg.PLStatus = 1 end)');            
        $this->db->where('cg.GLSaldoYears', date('Y'));                
        $this->db->where('ag.CurrencyCode', 'IDR');
        $this->db->where('sys_ia.AccountCode', $account_code);
        $query_account = $this->db->get();        

        if ($query_account->num_rows() > 0) {
            $data_account = $query_account->row();            
            return $data_account;
        }
        else {
            return false;
        }        
    }
    
    function get_cat_item_account($account_code, $item_category_id, $currency_code){   
        $this->db->select('SysItemCategoryID');
        $this->db->from($this->sys_item_cat . ' as sys_ic');
        $this->db->where('ItemCategoryID', $item_category_id);
        $this->db->where('CompanyCode', $this->session->userdata('company'));
        $query_category = $this->db->get();
		
        if ($query_category->num_rows() > 0) {
            $data_category = $query_category->row();
            
            $this->db->distinct();
            $this->db->select('sys_ia.AccountNumber, ag.AccountName');
            $this->db->from($this->sys_item_acc . ' as sys_ia');
            $this->db->join($this->comp_gl . ' as cg', 'cg.AccountNumber = sys_ia.AccountNumber', 'INNER');
            $this->db->join($this->account_gl . ' as ag', 'ag.AccountNumber = cg.AccountNumber', 'INNER');
            $this->db->join($this->branch . ' as br', 'br.BranchCode = cg.BranchCode', 'INNER');
            $this->db->where('cg.CompanyCode', $this->session->userdata('company'));
            $this->db->where('cg.BranchCode', $this->session->userdata('branch'));            
            $this->db->where('(case when br.BranchStatus = 0 then cg.PLStatus in (0, 1) else cg.PLStatus = 1 end)');            
            $this->db->where('cg.GLSaldoYears', date('Y'));                
            $this->db->where('ag.CurrencyCode', 'IDR');
            $this->db->where('sys_ia.AccountCode', $account_code);
            $this->db->where('sys_ia.SysItemCategoryID', $data_category->SysItemCategoryID);
            $query_number = $this->db->get();
            
            if ($query_number->num_rows() > 0) {
                $data_number = $query_number->row();
                
                if($currency_code != 'IDR'){
                    $account_name = implode(' ', explode(' ', $data_number->AccountName, -1));                

                    $this->db->distinct();
                    $this->db->select('cg.AccountNumber, ag.AccountName');
                    $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->branch . ' as br', 'br.BranchCode = cg.BranchCode', 'INNER');
                    $this->db->where('cg.CompanyCode', $this->session->userdata('company'));
                    $this->db->where('cg.BranchCode', $this->session->userdata('branch'));            
                    $this->db->where('(case when br.BranchStatus = 0 then cg.PLStatus in (0, 1) else cg.PLStatus = 1 end)');            
                    $this->db->where('cg.GLSaldoYears', date('Y'));                
                    
                    $this->db->like('ag.AccountName', $account_name);
                    $query_name = $this->db->get();

                    if($query_name->num_rows() > 0) {
                        $data_name = $query_name->row();
                        return $data_name;
                    }
                    else{
                        return false;
                    }
                }
                else{
                    return $data_number;	
                }
            }
            else {
                return false;
            }
        }
        else {
            return false;
        }			
    }    
    //--------------------------------------------------Bata Item Account----------------------------------------------------------//
   
    function get_sys_purchase_account($account_code, $currency_code){
        $this->db->select('sys_pa.AccountNumber, ag.AccountName');
        $this->db->from($this->sys_purchase_acc . ' as sys_pa');
        $this->db->join($this->comp_gl . ' as cg', 'cg.AccountNumber = sys_pa.AccountNumber', 'INNER');
        $this->db->join($this->account_gl . ' as ag', 'ag.AccountNumber = cg.AccountNumber', 'INNER');
        $this->db->join($this->branch . ' as br', 'br.BranchCode = cg.BranchCode', 'INNER');
        $this->db->where('cg.CompanyCode', $this->session->userdata('company'));
        $this->db->where('cg.BranchCode', $this->session->userdata('branch'));            
        $this->db->where('(case when br.BranchStatus = 0 then cg.PLStatus in (0, 1) else cg.PLStatus = 1 end)');            
        $this->db->where('cg.GLSaldoYears', date('Y'));                
        $this->db->where('ag.CurrencyCode', 'IDR');
        $this->db->where('sys_pa.AccountCode', $account_code);
        $query_account = $this->db->get();        

        if ($query_account->num_rows() > 0) {
            $data_account = $query_account->row();
            
            if($currency_code != 'IDR'){            
                $account_name   = implode(' ', explode(' ', $data_account->AccountName, -1));                

                $this->db->distinct();
                $this->db->select('cg.AccountNumber, ag.AccountName');
                $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->branch . ' as br', 'br.BranchCode = cg.BranchCode', 'INNER');
                $this->db->where('cg.CompanyCode', $this->session->userdata('company'));
                $this->db->where('cg.BranchCode', $this->session->userdata('branch'));            
                $this->db->where('(case when br.BranchStatus = 0 then cg.PLStatus in (0, 1) else cg.PLStatus = 1 end)');            
                $this->db->where('cg.GLSaldoYears', date('Y'));                
                $this->db->where('ag.CurrencyCode', $currency_code);
                $this->db->like('ag.AccountName', $account_name);
                $query_name = $this->db->get();

                if($query_name->num_rows() > 0) {
                    $data_name = $query_name->row();
                    return $data_name;
                }
                else{
                    return false;	
                }                
            }
            else{                
                return $data_account;
            }
        }
        else {
            return false;
        }        
    }
    
    function get_cat_purchase_account($account_code, $item_category_id, $currency_code){   
        $this->db->select('SysPurchaseCategoryID');
        $this->db->from($this->sys_purchase_cat . ' as sys_pc');
        $this->db->where('ItemCategoryID', $item_category_id);
        $this->db->where('CompanyCode', $this->session->userdata('company'));
        $query_category = $this->db->get();
		
        if ($query_category->num_rows() > 0) {
            $data_category = $query_category->row();
            
            $this->db->distinct();
            $this->db->select('sys_pa.AccountNumber, ag.AccountName');
            $this->db->from($this->sys_purchase_acc . ' as sys_pa');
            $this->db->join($this->comp_gl . ' as cg', 'cg.AccountNumber = sys_pa.AccountNumber', 'INNER');
            $this->db->join($this->account_gl . ' as ag', 'ag.AccountNumber = cg.AccountNumber', 'INNER');
            $this->db->join($this->branch . ' as br', 'br.BranchCode = cg.BranchCode', 'INNER');
            $this->db->where('cg.CompanyCode', $this->session->userdata('company'));
            $this->db->where('cg.BranchCode', $this->session->userdata('branch'));            
            $this->db->where('(case when br.BranchStatus = 0 then cg.PLStatus in (0, 1) else cg.PLStatus = 1 end)');            
            $this->db->where('cg.GLSaldoYears', date('Y'));                
            $this->db->where('ag.CurrencyCode', 'IDR');
            $this->db->where('sys_pa.AccountCode', $account_code);
            $this->db->where('sys_pa.SysPurchaseCategoryID', $data_category->SysPurchaseCategoryID);
            $query_number = $this->db->get();
			
            if ($query_number->num_rows() > 0) {
                $data_number = $query_number->row();
                
                if($currency_code != 'IDR'){
                    $account_name = implode(' ', explode(' ', $data_number->AccountName, -1));                
					
                    $this->db->distinct();
                    $this->db->select('cg.AccountNumber, ag.AccountName');
                    $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->branch . ' as br', 'br.BranchCode = cg.BranchCode', 'INNER');
                    $this->db->where('cg.CompanyCode', $this->session->userdata('company'));
                    $this->db->where('cg.BranchCode', $this->session->userdata('branch'));            
                    $this->db->where('(case when br.BranchStatus = 0 then cg.PLStatus in (0, 1) else cg.PLStatus = 1 end)');            
                    $this->db->where('cg.GLSaldoYears', date('Y'));                
                    $this->db->where('ag.CurrencyCode', $currency_code);
                    $this->db->like('ag.AccountName', $account_name);
                    $query_name = $this->db->get();

                    if($query_name->num_rows() > 0) {
                        $data_name = $query_name->row();
                        return $data_name;
                    }
                    else{
                        return false;
                    }
                }
                else{
                    return $data_number;	
                }
            }
            else {
                return false;
            }
        }
        else {
            return false;
        }			
    }    
    //------------------------------------------------------Batas Journal Purchase--------------------------------------------------//

    function get_sys_sales_account($account_code, $currency_code){
        $this->db->select('sys_sa.AccountNumber, ag.AccountName');
        $this->db->from($this->sys_sales_acc . ' as sys_sa');
        $this->db->join($this->comp_gl . ' as cg', 'cg.AccountNumber = sys_sa.AccountNumber', 'INNER');
        $this->db->join($this->account_gl . ' as ag', 'ag.AccountNumber = cg.AccountNumber', 'INNER');
        $this->db->join($this->branch . ' as br', 'br.BranchCode = cg.BranchCode', 'INNER');
        $this->db->where('cg.CompanyCode', $this->session->userdata('company'));
        $this->db->where('cg.BranchCode', $this->session->userdata('branch'));            
        $this->db->where('(case when br.BranchStatus = 0 then cg.PLStatus in (0, 1) else cg.PLStatus = 1 end)');            
        $this->db->where('cg.GLSaldoYears', date('Y'));                
        $this->db->where('ag.CurrencyCode', 'IDR');
		$this->db->where('cg.TransStatus', 2); 
        $this->db->where('sys_sa.AccountCode', $account_code);
        $query_account = $this->db->get();        

        if ($query_account->num_rows() > 0) {
            $data_account = $query_account->row();
            
            if($currency_code != 'IDR'){            
                $account_name   = implode(' ', explode(' ', $data_account->AccountName, -1));                
				
                $this->db->distinct();
                $this->db->select('cg.AccountNumber, ag.AccountName');
                $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->branch . ' as br', 'br.BranchCode = cg.BranchCode', 'INNER');
                $this->db->where('cg.CompanyCode', $this->session->userdata('company'));
                $this->db->where('cg.BranchCode', $this->session->userdata('branch'));            
                $this->db->where('(case when br.BranchStatus = 0 then cg.PLStatus in (0, 1) else cg.PLStatus = 1 end)');            
                $this->db->where('cg.GLSaldoYears', date('Y'));                
                $this->db->where('ag.CurrencyCode', $currency_code);
				$this->db->where('cg.TransStatus', 2); 
                $this->db->like('ag.AccountName', $account_name);
                $query_name = $this->db->get();

                if($query_name->num_rows() > 0) {
                    $data_name = $query_name->row();
                    return $data_name;
                }
                else{
                    return false;	
                }                
            }
            else{                
                return $data_account;
            }
        }
        else {
            return false;
        }        
    }
    
    function get_cat_sales_account($account_code, $item_category_id, $currency_code){   
        $this->db->select('SysSalesCategoryID');
        $this->db->from($this->sys_sales_cat . ' as sys_sc');
        $this->db->where('CompanyCode', $this->session->userdata('company'));
        $this->db->where('ItemCategoryID', $item_category_id);
        $query_category = $this->db->get();
		
        if ($query_category->num_rows() > 0) {
            $data_category = $query_category->row();
            
            $this->db->distinct();
            $this->db->select('sys_sa.AccountNumber, ag.AccountName');
            $this->db->from($this->sys_sales_acc . ' as sys_sa');
            $this->db->join($this->comp_gl . ' as cg', 'cg.AccountNumber = sys_sa.AccountNumber', 'INNER');
            $this->db->join($this->account_gl . ' as ag', 'ag.AccountNumber = cg.AccountNumber', 'INNER');
            $this->db->join($this->branch . ' as br', 'br.BranchCode = cg.BranchCode', 'INNER');
            $this->db->where('cg.CompanyCode', $this->session->userdata('company'));
            $this->db->where('cg.BranchCode', $this->session->userdata('branch'));            
            $this->db->where('(case when br.BranchStatus = 0 then cg.PLStatus in (0, 1) else cg.PLStatus = 1 end)');            
            $this->db->where('cg.GLSaldoYears', date('Y'));                
            $this->db->where('ag.CurrencyCode', 'IDR');
            $this->db->where('sys_sa.AccountCode', $account_code);
			$this->db->where('cg.TransStatus', 2); 
            $this->db->where('sys_sa.SysSalesCategoryID', $data_category->SysSalesCategoryID);
            $query_number = $this->db->get();
			
            if ($query_number->num_rows() > 0) {
                $data_number = $query_number->row();
                
                if($currency_code != 'IDR'){
                    $account_name = implode(' ', explode(' ', $data_number->AccountName, -1));                

                    $this->db->distinct();
                    $this->db->select('cg.AccountNumber, ag.AccountName');
                    $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->branch . ' as br', 'br.BranchCode = cg.BranchCode', 'INNER');
                    $this->db->where('cg.CompanyCode', $this->session->userdata('company'));
                    $this->db->where('cg.BranchCode', $this->session->userdata('branch'));            
                    $this->db->where('(case when br.BranchStatus = 0 then cg.PLStatus in (0, 1) else cg.PLStatus = 1 end)');            
                    $this->db->where('cg.GLSaldoYears', date('Y'));                
                    $this->db->where('ag.CurrencyCode', $currency_code);
					$this->db->where('cg.TransStatus', 2); 
                    $this->db->like('ag.AccountName', $account_name);
                    $query_name = $this->db->get();

                    if($query_name->num_rows() > 0) {
                        $data_name = $query_name->row();
                        return $data_name;
                    }
                    else{
                        return false;
                    }
                }
                else{
                    return $data_number;	
                }
            }
            else {
                return false;
            }
        }
        else {
            return false;
        }			
    }    
    //------------------------------------------------------Batas Journal Sales----------------------------------------------------//
    
    function get_sys_service_account($account_code, $currency_code, $item_category_id=''){
		
        $this->db->distinct();
        $this->db->select('sys_sa.AccountNumber, ag.AccountName');
        $this->db->from($this->sys_service_acc . ' as sys_sa');
		if($item_category_id != ''){
			if($currency_code == 'IDR'){
				$this->db->join('sys_servicecategory as sys_sc', 'sys_sa.SvcCategoryID = sys_sc.SvcCategoryID', 'INNER');
			}
		}
		$this->db->join($this->comp_gl . ' as cg', 'cg.AccountNumber = sys_sa.AccountNumber', 'INNER');
        $this->db->join($this->account_gl . ' as ag', 'ag.AccountNumber = cg.AccountNumber', 'INNER');
        $this->db->join($this->branch . ' as br', 'br.BranchCode = cg.BranchCode', 'INNER');
        $this->db->where('cg.CompanyCode', $this->session->userdata('company'));
        $this->db->where('cg.BranchCode', $this->session->userdata('branch'));            
        $this->db->where('(case when br.BranchStatus = 0 then cg.PLStatus in (0, 1) else cg.PLStatus = 1 end)');            
        $this->db->where('cg.GLSaldoYears', date('Y'));                
        $this->db->where('ag.CurrencyCode', 'IDR');
		$this->db->where('cg.TransStatus', 2); 
        $this->db->where('sys_sa.AccountCode', $account_code);
		if($item_category_id != ''){
			if($currency_code == 'IDR'){
				$this->db->where('sys_sc.SvcCategoryID', $item_category_id);
			}
		}
        $qeury_number = $this->db->get();
        
        if($qeury_number->num_rows() > 0) {
            $data_number = $qeury_number->row();
           
            if($currency_code != 'IDR'){
                $account_name   = implode(' ', explode(' ', $data_number->AccountName, -1));

                $this->db->select('cg.AccountNumber, ag.AccountName');
                $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->branch . ' as br', 'br.BranchCode = cg.BranchCode', 'INNER');
                $this->db->where('cg.CompanyCode', $this->session->userdata('company'));
                $this->db->where('cg.BranchCode', $this->session->userdata('branch'));            
                $this->db->where('(case when br.BranchStatus = 0 then cg.PLStatus in (0, 1) else cg.PLStatus = 1 end)');            
                $this->db->where('cg.GLSaldoYears', date('Y'));
                $this->db->where('ag.CurrencyCode', $currency_code);
				$this->db->where('cg.TransStatus', 2); 
                $this->db->like('ag.AccountName', $account_name);
                $query_name = $this->db->get();

                if($query_name->num_rows() > 0) {
                    $data_name = $query_name->row();
                    return $data_name;
                }
                else{
                    return false;	
                }
            }
            else{
                return $data_number;
            }
        }
        else {
            return false;
        }        
    }
    //--------------------------------------------------Batas Journal Service / Expense---------------------------------------------//
    
    function get_sys_advance_account($account_code, $currency_code, $item_category_id=''){
        $this->db->distinct();
        $this->db->select('sys_aa.AccountNumber, ag.AccountName');
        $this->db->from($this->sys_advance_acc . ' as sys_aa');
		if($item_category_id != ''){
			if($currency_code == 'IDR'){
				$this->db->join('sys_advancecategory as sys_ac', 'sys_aa.AdvCategoryID = sys_ac.AdvCategoryID', 'INNER');
			}
		}
        $this->db->join($this->comp_gl . ' as cg', 'cg.AccountNumber = sys_aa.AccountNumber', 'INNER');
        $this->db->join($this->account_gl . ' as ag', 'ag.AccountNumber = cg.AccountNumber', 'INNER');
        $this->db->join($this->branch . ' as br', 'br.BranchCode = cg.BranchCode', 'INNER');
        $this->db->where('cg.CompanyCode', $this->session->userdata('company'));
        $this->db->where('cg.BranchCode', $this->session->userdata('branch'));            
        $this->db->where('(case when br.BranchStatus = 0 then cg.PLStatus in (0, 1) else cg.PLStatus = 1 end)');            
        $this->db->where('cg.GLSaldoYears', date('Y'));                
        $this->db->where('ag.CurrencyCode', 'IDR');
        $this->db->where('sys_aa.AccountCode', $account_code);
		if($item_category_id != ''){
			if($currency_code == 'IDR'){
				$this->db->where('sys_ac.AdvCategoryID', $item_category_id);
			}
		}
        $query_number = $this->db->get();
        
        if($query_number->num_rows() > 0){
            $data_number = $query_number->row();
            
            if($currency_code != 'IDR'){
                $account_name = implode(' ', explode(' ', $data_number->AccountName, -1));

                $this->db->select('cg.AccountNumber, ag.AccountName');
                $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->branch . ' as br', 'br.BranchCode = cg.BranchCode', 'INNER');
                $this->db->where('cg.CompanyCode', $this->session->userdata('company'));
                $this->db->where('cg.BranchCode', $this->session->userdata('branch'));            
                $this->db->where('(case when br.BranchStatus = 0 then cg.PLStatus in (0, 1) else cg.PLStatus = 1 end)');            
                $this->db->where('cg.GLSaldoYears', date('Y'));
                $this->db->where('ag.CurrencyCode', $currency_code);            
                $this->db->like('ag.AccountName', $account_name);
                $query_name = $this->db->get();

                if($query_name->num_rows() > 0) {
                    $data_name = $query_name->row();
                    return $data_name;
                }
                else{
                    return false;	
                }
            }
            else{
                return $data_number;
            }
        }
        else {
            return false;
        }        
    }
    //------------------------------------------------------Batas Journal Advance--------------------------------------------------//

    function get_account_number($account_number){
        $company_code   = $this->session->userdata('company');
        $branch_code    = $this->session->userdata('branch');
        
        $this->db->select('cg.AccountNumber, ag.AccountName');
        $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->branch . ' as br', 'br.BranchCode = cg.BranchCode', 'INNER');
        $this->db->where('cg.CompanyCode', $company_code);
        $this->db->where('cg.BranchCode', $branch_code);            
        $this->db->where('cg.AccountNumber', $account_number);
        $this->db->where('(case when br.BranchStatus = 0 then cg.PLStatus in (0, 1) else cg.PLStatus = 1 end)');            
        $this->db->where('cg.GLSaldoYears', date('Y'));                
        $this->db->where('ag.CurrencyCode', 'IDR');
		$this->db->where_in('cg.TransStatus', array(1,2));
        $query_account = $this->db->get();
        
        if ($query_account->num_rows() > 0) {
            $data = $query_account->row();
            return $data;
        }
        else {
            return false;
        }			
    }
    //-------------------------------------------Batas Journal Get Account by Account Number---------------------------------------//
    
    function get_account_name($account_name, $currency_code = '', $param_status = ''){
        $company_code   = $this->session->userdata('company');
        $branch_code    = $this->session->userdata('branch');
        
        $this->db->select('cg.AccountNumber, ag.AccountName,sbt.AccountTypeName');
        $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 sbt', 'sbt.AccountSubTypeID = ag.AccountSubTypeID', 'INNER');
		$this->db->join($this->acc_type . ' as at', 'at.AccountTypeID = sbt.AccountTypeID', 'INNER');
		
        $this->db->join($this->branch . ' as br', 'br.BranchCode = cg.BranchCode', 'INNER');
        $this->db->where('cg.CompanyCode', $company_code);
        $this->db->where('cg.BranchCode', $branch_code);
        $this->db->where('(case when br.BranchStatus = 0 then cg.PLStatus in (0, 1) else cg.PLStatus = 1 end)');            
        $this->db->where('cg.GLSaldoYears', date('Y'));  
		if($param_status == 1){
			$this->db->where('ag.AccountName', $account_name);
		}
		else{
			$this->db->like('ag.AccountName', $account_name);
		}
		$this->db->where_in('cg.TransStatus', array(1,2)); 
        
        if($currency_code == ''){
            $this->db->where('ag.CurrencyCode', 'IDR');
        }
        else{
            $this->db->where('ag.CurrencyCode', $currency_code);            
        }
        
        $query_account = $this->db->get();
        
        if ($query_account->num_rows() > 0) {
            $data = $query_account->row();
			if((in_array($data->AccountTypeName,array('Uang Muka Pembelian','Uang Muka Pembelian Barang Dagang','Uang Muka Pembelian Expense','Uang Muka Penjualan')))){
				$this->db->select('cg.AccountNumber, ag.AccountName');
                $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->branch . ' as br', 'br.BranchCode = cg.BranchCode', 'INNER');
                $this->db->where('cg.CompanyCode', $this->session->userdata('company'));
                $this->db->where('cg.BranchCode', $this->session->userdata('branch'));            
                $this->db->where('(case when br.BranchStatus = 0 then cg.PLStatus in (0, 1) else cg.PLStatus = 1 end)');            
                $this->db->where('cg.GLSaldoYears', date('Y'));
				$this->db->where('ag.AccountChild', 1);
                $this->db->where('ag.CurrencyCode', $currency_code);            
                $this->db->like('ag.AccountName', $account_name);
                $query_name = $this->db->get();

                if($query_name->num_rows() > 0) {
                    $data_name = $query_name->row();
                    return $data_name;
                }
                else{
                    return false;	
                }
			}
			else{
				return $data;
			}
        }
        else {
            return false;
        }			
    }
    //-------------------------------------------Batas Journal Get Account by Account Name---------------------------------------// 
	
	
	/*
		Get Journal sys_expenseaccount
	*/
	function get_sys_cashbon_account($account_code, $item_category_id, $currency_code){   
        $this->db->select('ExpCategoryID');
        $this->db->from($this->sys_expensecategory . ' as sys_expc');
        $this->db->where('ExpCategoryID', $item_category_id);
        $this->db->where('CompanyCode', $this->session->userdata('company'));
        $query_category = $this->db->get();
		
        if ($query_category->num_rows() > 0) {
            $data_category = $query_category->row();
            
            $this->db->distinct();
            $this->db->select('sys_accash.AccountNumber, ag.AccountName');
            $this->db->from($this->sys_expenseaccount . ' as sys_accash');
            $this->db->join($this->comp_gl . ' as cg', 'cg.AccountNumber = sys_accash.AccountNumber', 'INNER');
            $this->db->join($this->account_gl . ' as ag', 'ag.AccountNumber = cg.AccountNumber', 'INNER');
            $this->db->join($this->branch . ' as br', 'br.BranchCode = cg.BranchCode', 'INNER');
            $this->db->where('cg.CompanyCode', $this->session->userdata('company'));
            $this->db->where('cg.BranchCode', $this->session->userdata('branch'));            
            $this->db->where('(case when br.BranchStatus = 0 then cg.PLStatus in (0, 1) else cg.PLStatus = 1 end)');            
            $this->db->where('cg.GLSaldoYears', date('Y'));                
            $this->db->where('ag.CurrencyCode', 'IDR');
            $this->db->where('sys_accash.AccountCode', $account_code);
            $this->db->where('sys_accash.ExpCategoryID', $data_category->ExpCategoryID);
            $query_number = $this->db->get();
            
            if ($query_number->num_rows() > 0) {
                $data_number = $query_number->row();
                
                if($currency_code != 'IDR'){
                    $account_name = implode(' ', explode(' ', $data_number->AccountName, -1));                

                    $this->db->distinct();
                    $this->db->select('cg.AccountNumber, ag.AccountName');
                    $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->branch . ' as br', 'br.BranchCode = cg.BranchCode', 'INNER');
                    $this->db->where('cg.CompanyCode', $this->session->userdata('company'));
                    $this->db->where('cg.BranchCode', $this->session->userdata('branch'));            
                    $this->db->where('(case when br.BranchStatus = 0 then cg.PLStatus in (0, 1) else cg.PLStatus = 1 end)');            
                    $this->db->where('cg.GLSaldoYears', date('Y'));                
                    $this->db->where('ag.CurrencyCode', $currency_code);  
                    $this->db->like('ag.AccountName', $account_name);
                    $query_name = $this->db->get();

                    if($query_name->num_rows() > 0) {
                        $data_name = $query_name->row();
                        return $data_name;
                    }
                    else{
                        return false;
                    }
                }
                else{
                    return $data_number;	
                }
            }
            else {
                return false;
            }
        }
        else {
            return false;
        }			
    }
	/*====================End======================*/
	
	/*
		Function untuk menentukan nature kredit atau debet
		Kirim variabel type name nya dan sub type nya
	*/
	
	function debet_kredit($type_name, $sub_type){
		switch($type_name) {
            case "Account Payable" : 
				$debet_kredit = "Credit";
            break;
        	case "Account Receivable" : 
				$debet_kredit = "Debet";
            break;
			case "Accumulated Depreciation Fixed Asset" : 
                $debet_kredit = "Credit";
            break;/*
			case "Aktiva Lancar" : 
                $sign_status = ;
            break;*/
			case "Assets" : 
                $debet_kredit = "Debet";
            break;
			case "Cash / Bank" : 
                $debet_kredit = "Debet";
            break;
			case "COGS / COGM (Cost Of Goods Made)" : 
                $debet_kredit = "Debet";
            break;
			case "Equity" : 
               // if(($sub_type == "Laba (Rugi) Ditahan") || ($sub_type == "Laba (Rugi) Berjalan") || ($sub_type == "Laba (Rugi) Bulan Berjalan")){
				//	$result['message']  = "Maaf transaksi dibatalkan karena akun Laba Rugi tidak diperbolehkan";     
				//	echo json_encode($result);
				//	die();
				//}
				/*else*/ if($sub_type == "Dividen"){
					$debet_kredit = "Debet";
				}
				else{
					$debet_kredit = "Credit";
				}
            break;
			case "Expense" : 
                $debet_kredit = "Debet";
            break;
			case "Fixed Asset" : 
                $debet_kredit = "Debet";
            break;
			case "Inventory" : 
                $debet_kredit = "Debet";
            break;
			/*case "Liability" : 
                $debet_kredit == "Debet";
            break;*/
			case "Long Term Liability" : 
                $debet_kredit = "Credit";
            break;
			case "Other Current Asset" : 
                $debet_kredit = "Debet";
            break;
			case "Other Current Liability" : 
                $debet_kredit = "Credit";
            break;
			case "Other Expense" : 
                $debet_kredit = "Debet";
            break;
			case "Other Revenue" : 
                $debet_kredit = "Credit";
            break;
			case "Revenue" : 
               if($sub_type == "Retur Penjualan"){
			   		$debet_kredit = "Debet";
			   }
			   else{
			   		 $debet_kredit = "Credit";
			   }
            break;
        }
        return $debet_kredit;
	}

    function debet_kredit_profit_loss($type_name, $sub_type){

        $debet_kredit = array('plus'=>'','position'=>'');
        switch($type_name) {
            case "Account Payable" : 
                $debet_kredit['plus']       = 'Credit';
                $debet_kredit['position']   = 'Credit';
            break;
            case "Account Receivable" : 
                $debet_kredit['plus']       = 'Debet';
                $debet_kredit['position']   = 'Debet';
            break;
            case "Accumulated Depreciation Fixed Asset" : 
                $debet_kredit['plus']       = 'Credit';
                $debet_kredit['position']   = 'Debet';
            break;/*
            case "Aktiva Lancar" : 
                $sign_status = ;
            break;*/
            case "Assets" : 
                $debet_kredit['plus']       = 'Debet';
                $debet_kredit['position']   = 'Debet';
            break;
            case "Cash / Bank" : 
                
                $debet_kredit['plus']       = 'Debet';
                $debet_kredit['position']   = 'Debet';
            break;
            case "COGS / COGM (Cost Of Goods Made)" : 
                
                $debet_kredit['plus']       = 'Debet';
                $debet_kredit['position']   = 'Credit';
            break;
            case "Equity" :
              	if($sub_type == "Dividen"){
					$debet_kredit['plus']       = 'Debet';
                	$debet_kredit['position']   = 'Debet';
				}
				else{
					$debet_kredit['plus']       = 'Credit';
                	$debet_kredit['position']   = 'Credit';
				}
            break;
            case "Expense" : 
                
                $debet_kredit['plus']       = 'Debet';
                $debet_kredit['position']   = 'Credit';
            break;
            case "Fixed Asset" : 
                
                $debet_kredit['plus']       = 'Debet';
                $debet_kredit['position']   = 'Debet';
            break;
            case "Inventory" : 
               
                $debet_kredit['plus']       = 'Debet';
                $debet_kredit['position']   = 'Debet';
            break;
            /*case "Liability" : 
                $debet_kredit == "Debet";
            break;*/
            case "Long Term Liability" : 
                
                $debet_kredit['plus']       = 'Credit';
                $debet_kredit['position']   = 'Credit';
            break;
            case "Other Current Asset" : 
                
                $debet_kredit['plus']       = 'Debet';
                $debet_kredit['position']   = 'Debet';
            break;
            case "Other Current Liability" : 
              
               $debet_kredit['plus']       = 'Credit';
                $debet_kredit['position']   = 'Credit';
            break;
            case "Other Expense" : 
               
                $debet_kredit['plus']       = 'Debet';
                $debet_kredit['position']   = 'Credit';
            break;
            case "Other Revenue" : 
                
                $debet_kredit['plus']       = 'Credit';
                $debet_kredit['position']   = 'Credit';
            break;
            case "Revenue" : 
               if($sub_type == "Retur Penjualan"){
                   
                    $debet_kredit['plus']       = 'Debet';
                    $debet_kredit['position']   = 'Credit';
               }
               else{
                    
                    $debet_kredit['plus']       = 'Credit';
                    $debet_kredit['position']   = 'Credit';
               }
            break;
        }

        return $debet_kredit;
    }


    function m_get_bank_balance($branch,$AccountNumber){

        $month      = abs(date('m'));

        $this->db->select("(CASE WHEN 
                (acc.AccountTypeName='Revenue') or (acc.AccountTypeName='COGS / COGM (Cost Of Goods Made)') 
                or (acc.AccountTypeName='Expense') or (acc.AccountTypeName='Other Revenue')
                or (acc.AccountTypeName='Other Expense') THEN GLSaldoBalance$month ELSE GLSaldoBSheet END) as SaldoBSheet");
        $this->db->from($this->comp_gl.' as ac');
        $this->db->join($this->account_gl.' as ag','ag.AccountNumber=ac.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('ac.AccountNumber',$AccountNumber);
        $this->db->where('ac.BranchCode',$branch);
        $this->db->where('ac.GLSaldoYears',date('Y'));
        $this->db->where('ac.TransStatus',2);

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

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

    function m_get_account_type($account_number,$branch=''){
        
        if($branch == ''){
            $branch         = $this->session->userdata('branch');
        }

        $this->db->select('acc.AccountTypeName as Type_Name,sub.AccountTypeName as Sub_Type_Name');
        $this->db->from($this->comp_gl.' as ac');
        $this->db->join($this->account_gl.' as ag','ag.AccountNumber=ac.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('ac.AccountNumber',$account_number);
        $this->db->where('ac.BranchCode',$branch);
        $this->db->where('ac.GLSaldoYears',date('Y'));
        $this->db->where('ac.TransStatus',2);

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

    function get_currency_code($account_number='',$account_name='',$branch=''){

        if($branch == ''){
            $branch         = $this->session->userdata('branch');
        }

        $this->db->select('ag.CurrencyCode');
        $this->db->from($this->comp_gl.' as ac');
        $this->db->join($this->account_gl.' as ag','ag.AccountNumber=ac.AccountNumber','inner');

        if($account_number!=''){
            $this->db->where('ac.AccountNumber',$account_number);
        }

        if($account_name!=''){
            $this->db->where('ag.AccountName',$account_name);
        }
        
        $this->db->where('ac.BranchCode',$branch);
        $this->db->where('ac.GLSaldoYears',date('Y'));
        $this->db->where('ac.TransStatus',2);

        $query  = $this->db->get();
        return $query->row();
    }
	
	/*====================End=======================================================*/
}