<?php
class M_compaccountgl extends CI_Model {    
    public $table           = "at_compaccountgl";
    public $account_gl      = "am_accountgl";
    public $sub_type        = "am_accountsubtype";
    public $acc_type        = "am_accounttype";
    public $company         = "am_company";
    public $currency        = "am_currency";
    public $Branch          = "am_branch";
            
    function get_all_lama() {
		/*=========================lemparan parah dari custom.js untuk searching_data_transaksi=====================*/
		$param = $this->input->post('param');
		/*====================================end===================================================================*/
		
		/*=================================post dari find di kiri====================================================*/
		$Number			= isset($param['txt_IDNumber']) ? $param['txt_IDNumber'] : '';
		$Desc			= isset($param['txt_Description']) ? $param['txt_Description'] : '';
		
		$TransStatus	= isset($param['chk_TransStatus']) ? $param['chk_TransStatus'] : '';
		$BranchCode		= isset($param['cmb_branch']) ? $param['cmb_branch'] : '';
		$CompanyCode	= isset($param['cmb_company']) ? $param['cmb_company'] : '';
		$AccountType	= isset($param['account_type']) ? $param['account_type'] : '';
		/*============================================end============================================================*/
		
		/*======================================scrol view 1-150=======================================*/
		$page		= ($this->input->post('page')) ? intval($this->input->post('page')) : 1;
		$rows		= ($this->input->post('rows')) ? intval($this->input->post('rows')) : 150;
		
		$beginning 	= ($page - 1) * $rows;
		$end 		= $page * $rows;
		/*==============================================================================================*/
		
		/*==========================================by session=======================================================*/
		$sess_company		= $this->session->userdata('company');
        $sess_branch		= $this->session->userdata('branch');
        $sess_branch_status	= $this->session->userdata('branch_status');
		/*===========================================================================================================*/
		
		 
        $this->db->_protect_identifiers = FALSE;
        $this->db->select("
                           (case
                                when coa.CurrencyCode != 'IDR' AND (am_accountsubtype.AccountTypeName = 'Bank' OR am_accountsubtype.AccountTypeName = 'Petty Cash' OR am_accountsubtype.AccountTypeName = 'Deposito')
                                Then IF(GLSaldoBalance" .CURRENT_PERIOD. " IS NULL, 0, GLSaldoBalance" .CURRENT_PERIOD. " + GLSaldoOpening * (Select CurrencyRate From am_currency where CurrencyCode = coa.CurrencyCode and CompanyCode = at_compaccountgl.CompanyCode))
                                else IF(GLSaldoBalance" .CURRENT_PERIOD. " IS NULL, 0, GLSaldoBalance" .CURRENT_PERIOD. " + GLSaldoOpening) end) as CurrentBalance,
							(case
                                when coa.CurrencyCode != 'IDR' AND (am_accountsubtype.AccountTypeName = 'Bank' OR am_accountsubtype.AccountTypeName = 'Petty Cash' OR am_accountsubtype.AccountTypeName = 'Deposito')
                                Then IF(GLSaldoBSheet IS NULL, 0, GLSaldoBSheet * (Select CurrencyRate From am_currency where CurrencyCode = coa.CurrencyCode and CompanyCode = at_compaccountgl.CompanyCode))
                                else IF(GLSaldoBSheet IS NULL, 0, GLSaldoBSheet) end) as CurrentBSheet,
                            coa.AccountNumber, AccountName, AccountLevel, AccountParent, coa.AccountSubTypeID, AccountTypeName, GLSaldoOpening, coa.CurrencyCode, 
                            at_compaccountgl.BranchCode, am_company.CompBranchStatus, AppLevel1Status, AppLevel2Status, am_branch.BranchStatus, am_branch.BranchName,am_accountsubtype.AccountBSStatus, at_compaccountgl.TransStatus,am_accountsubtype.AccountAutoStatus,
                            (case when am_accountsubtype.AccountAutoStatus=0 then 'No' else 'Yes' end) as Account_AutoStatus,
                            (case 
                             when AccountChild = 1 
                                then concat(AccountName, ' - ', am_branch.branchcode)
                                else AccountName end) as AccountNameShow");
        $this->db->from($this->table);
        $this->db->join('am_accountgl as coa','coa.AccountNumber = at_compaccountgl.AccountNumber','INNER');
        $this->db->join('am_accountsubtype','coa.AccountSubTypeID = am_accountsubtype.AccountSubTypeID','LEFT');
        $this->db->join('am_company','at_compaccountgl.CompanyCode = am_company.CompanyCode','INNER');
        $this->db->join('am_branch','at_compaccountgl.BranchCode = am_branch.BranchCode','INNER');
        
		$this->db->where('at_compaccountgl.GLSaldoYears', date('Y'));
		
		if($Number != ""){$this->db->like('coa.AccountNumber', $Number);}
		if($Desc != ""){$this->db->like('coa.AccountName', $Desc);}
		if($AccountType != ""){
			if($AccountType == "All"){
				
			}
			else{
				$this->db->like('coa.AccountSubTypeID', $AccountType);
			}
		} 
		/*=====================================================find by status==================================================*/
		if($TransStatus!=''){ 
			$this->db->where_in('at_compaccountgl.TransStatus',$TransStatus); 
		}
		else{
			/*
				trans status 0,1 adalah aktif
			*/
			$this->db->where_in('at_compaccountgl.TransStatus',array(2,6,1)); 
		}
		/*=======================================================end========================================================*/
			
        //$this->db->like('coa.AccountSubTypeID', $Type);			
        
		/*===================================find by branch code=========================================================*/
		/*
			get data dari  combobox find di view
		*/
		if ($sess_branch_status == 1) {
			$this->db->where('at_compaccountgl.PLStatus', 1);	
		}
		
        if($BranchCode != ""){
			$this->db->where('at_compaccountgl.CompanyCode', $CompanyCode);
			$this->db->where('at_compaccountgl.BranchCode', $BranchCode);
		}
		else{
			/*
				inisialisasi untuk loading pertama page branch info 
			*/
			//----------------kondisi by company-------------------------------------//
			/*
				branch status 0 = HO
				branch status 1 = depo/branch
				jangan lupa sesion data di atas untuk setiap kondisi
				jangan lupa tambahin case when trans status
			*/
			if($sess_branch_status == 0){
				$this->db->where('at_compaccountgl.CompanyCode', $sess_company);
			}
			else{
				$this->db->where('at_compaccountgl.CompanyCode', $sess_company);
				$this->db->where('at_compaccountgl.BranchCode', $sess_branch);
			}
			//-----------------------end-------------------------------------------------//
		}
		/*=======================================================end========================================================*/
		
       
        $this->db->order_by('at_compaccountgl.AccountNumber', 'ASC');
        $this->db->order_by('at_compaccountgl.CompAccountGLID', 'ASC');		
        $this->db->order_by('am_branch.BranchStatus', 'ASC');
        $query  = $this->db->limit($end, $beginning);
		
        $query=$this->db->get();	
        $this->db->_protect_identifiers = FALSE;
        return $query->result();
    }

    // Function Get g/L Account based on Company
    function get_comp_gl_account($detail_status) {
        $company_code 	= $this->session->userdata('company');
        $branch_code 	= $this->session->userdata('branch');        
        
        $this->db->_protect_identifiers = FALSE;
        $this->db->select("DISTINCT cg.AccountNumber, cg.PLStatus, ag.AccountName, ag.AccountLevel, ag.AccountChild,
                           st.AccountSubTypeID, st.AccountTypeName, AppLevel1Status, AppLevel2Status, '0' as PLCode, '0' as ModusCode,
                          (case 
                            when ag.TransStatus = 0 then 'Waiting' 
                            when ag.TransStatus = 1 then 'On Progress' 
                            when ag.TransStatus = 2 then 'Active' 
                            when ag.TransStatus = 3 then 'Suspended' end) as Status,
                          (case when PLStatus = 0 then 'No' else 'Yes' end) as PLStatus");
        $this->db->from('at_compaccountgl cg');
        $this->db->join('am_accountgl ag','ag.AccountNumber = cg.AccountNumber', 'INNER');
        $this->db->join('am_company cp','cp.CompanyCode = cg.CompanyCode','INNER');
        $this->db->join('am_branch bc','bc.BranchCode = cg.BranchCode','INNER');
        $this->db->join('am_accountsubtype st','st.AccountSubTypeID = ag.AccountSubTypeID', 'LEFT');
        $this->db->where('cg.CompanyCode', $company_code);
        $this->db->where('cg.BranchCode', $branch_code);
        $this->db->where('cg.GLSaldoYears', date('Y'));
        $this->db->where('st.AccountSubTypeID !=', 7);
        $this->db->where('cg.TransStatus', 2);
        
        if ($detail_status == 1) {
            $this->db->where('ag.AccountChild', 1);            
            $this->db->where('ag.CurrencyCode', 'IDR');            
        }
        
        $this->db->order_by('cg.AccountNumber','ASC');		
        
        $query = $this->db->get();	
        $this->db->_protect_identifiers = TRUE;		
        return $query->result();						
    }    

    function get_comp_pl_account($account_number) {
        $company_code 	= $this->session->userdata('company');
        
        $this->db->_protect_identifiers = FALSE;
        $this->db->select("cg.AccountNumber, cg.PLStatus, ag.AccountName, ag.AccountLevel, ag.AccountChild, st.AccountSubTypeID, st.AccountTypeName, AppLevel1Status, AppLevel2Status, bc.BranchCode,
                          (case when ag.TransStatus = 3 then 'Suspended' when ag.TransStatus = 0 then 'Waiting' else 'Aktif' end) as Status");
        $this->db->from('at_compaccountgl cg');
        $this->db->join('am_accountgl ag','ag.AccountNumber = cg.AccountNumber', 'INNER');
        $this->db->join('am_company cp','cp.CompanyCode = cg.CompanyCode','INNER');
        $this->db->join('am_branch bc','bc.BranchCode = cg.BranchCode','INNER');
        $this->db->join('am_accountsubtype st','st.AccountSubTypeID = ag.AccountSubTypeID', 'LEFT');
        $this->db->where('cg.CompanyCode', $company_code);
        $this->db->where('cg.GLSaldoYears', date('Y'));
        $this->db->where('cg.PLStatus', 1);
        $this->db->where('ag.AccountChild', 1);
        $this->db->where('cg.TransStatus', 2);
        
        if($account_number != ''){
            $this->db->where('cg.AccountNumber', $account_number);
        }
		
        $this->db->order_by('cg.AccountNumber', 'ASC');
        $query = $this->db->get();	
        $this->db->_protect_identifiers = TRUE;

        return $query->result();						
    }
	
	/*==============================loping by company untuk akun coa waktu approve==============================================*/
	function get_branch_coa_comp_account($account_number) {
        $company_code 	= $this->session->userdata('company');
        
        $this->db->_protect_identifiers = FALSE;
        $this->db->select("cg.AccountNumber, cg.PLStatus, ag.AccountName, ag.AccountLevel, ag.AccountChild, st.AccountSubTypeID, st.AccountTypeName, AppLevel1Status, AppLevel2Status, bc.BranchCode,
                          (case when ag.TransStatus = 3 then 'Suspended' when ag.TransStatus = 0 then 'Waiting' else 'Aktif' end) as Status");
        $this->db->from('at_compaccountgl cg');
        $this->db->join('am_accountgl ag','ag.AccountNumber = cg.AccountNumber', 'INNER');
        $this->db->join('am_company cp','cp.CompanyCode = cg.CompanyCode','INNER');
        $this->db->join('am_branch bc','bc.BranchCode = cg.BranchCode','INNER');
        $this->db->join('am_accountsubtype st','st.AccountSubTypeID = ag.AccountSubTypeID', 'LEFT');
        $this->db->where('cg.CompanyCode', $company_code);
        $this->db->where('cg.GLSaldoYears', date('Y'));
        $this->db->where('cg.PLStatus', 0);
        $this->db->where('ag.AccountChild', 1);
        $this->db->where('ag.TransStatus', 5);
        
        if($account_number != ''){
            $this->db->where('cg.AccountNumber', $account_number);
        }
		
        $this->db->order_by('cg.AccountNumber', 'ASC');
        $query = $this->db->get();	
        $this->db->_protect_identifiers = TRUE;

        return $query->result();						
    }    
	/*===================================end=============================================================*/

    function get_by_account($account_number, $branch_code){
        $this->db->select('cg.*, ag.AccountName, ag.AccountParent, ag.AccountSubTypeID, ag.AppLevel1Status, ag.AppLevel2Status, ag.CurrencyCode, cg.GLSaldoBalance'.CURRENT_PERIOD.' as GLSaldoBalance, st.AccountBSStatus, cg.GLSaldoBSheet, st.AccountTypeName, at.AccountTypeName as TypeName');
        $this->db->join('am_accountgl as ag', 'ag.AccountNumber = cg.AccountNumber', 'INNER');
        $this->db->join('am_accountsubtype st','st.AccountSubTypeID = ag.AccountSubTypeID', 'INNER');
		$this->db->join('am_accounttype at','at.AccountTypeID = st.AccountTypeID', 'INNER');

        //$this->db->join('am_accountgl as agp', 'agp.AccountNumber = ag.AccountParent', 'INNER');
		
        if($this->session->userdata('jobtitle_status') != 0){
            $this->db->where('cg.CompanyCode', $this->session->userdata('company'));
        }
        
        $this->db->where('cg.BranchCode', $branch_code);
        $this->db->where('cg.AccountNumber', $account_number);
        $this->db->where('cg.GLSaldoYears', date('Y'));
        $query = $this->db->get($this->table . ' as cg');
        
        if($query->num_rows()>0){
            return $query->row();
        }
        else{
            return false;
        }        
    }

    function get_by_type($account_subt_ype_id){
        $company_code = $this->session->userdata('company');
        
        $this->db->select('cg.CompanyCode, cg.BranchCode,  cg.AccountNumber, coa.AccountName, coa.AccountParent, 
                           coa.AccountChild, coa.AccountSubTypeID, coa.AccountLevel, CurrencyCode, BankID');
        $this->db->from($this->table . ' cg');
        $this->db->join($this->account_gl . ' coa', 'coa.AccountNumber = cg.AccountNumber','INNER');
        $this->db->join($this->sub_type . ' st', 'st.AccountSubTypeID = coa.AccountSubTypeID','LEFT');
        $this->db->where('CompanyCode', $company_code);
        $this->db->where('cg.GLSaldoYears', date('Y'));
        $this->db->where('coa.AccountSubTypeID', $account_subt_ype_id);
        $this->db->where('coa.AccountChild', 0);
        $query = $this->db->get();
        
        return $query->result();
    }
    
    function get_by_id($AccountNumber){
        return $this->db->get_where($this->table, array(
                                                    'AccountNumber' => $AccountNumber, 
                                                    'CompanyCode'   => $this->session->userdata('company'),
                                                    'GLSaldoYears'  => date('Y')))->row();
    }

    function get_by_account_number($branch_code, $account_number){
//        $this->db->_protect_identifiers = FALSE;
        $this->db->select('cg.*, ag.AccountName, ag.BankID, ag.AppLevel1Status, ag.AppLevel2Status, ag.AccountParent, ag.AccountChild, 
                           ag.CurrencyCode, ag.TransStatus, st.AccountSubTypeID, st.AccountTypeName');
        $this->db->from($this->table . ' cg');
        $this->db->join('am_company cp', 'cp.CompanyCode = cg.CompanyCode', 'INNER');
        $this->db->join('am_branch bc', 'bc.BranchCode = cg.BranchCode', 'INNER');
        $this->db->join('am_accountgl ag', 'ag.AccountNumber = cg.AccountNumber', 'INNER');
        $this->db->join('am_currency ac', 'ac.CurrencyCode = ag.CurrencyCode', 'LEFT');
        $this->db->join('am_accountsubtype st', 'st.AccountSubTypeID = ag.AccountSubTypeID', 'LEFT');
        $this->db->join('am_bank ab', 'ab.BankID = ag.BankID', 'INNER');
        $this->db->where('cg.companycode', $this->session->userdata('company'));
        $this->db->where('cg.BranchCode', $branch_code);
        $this->db->where('cg.AccountNumber', $account_number);
        $this->db->where('GLSaldoYears', date('Y'));

        $query = $this->db->get();	
//        $this->db->_protect_identifiers = TRUE;
        
        if($query->num_rows()>0){
            return $query->row();
        }
        else{
            return false;
        }        
    }

//    function get_gl_by_company(){ // No need to use
//        $company    = $this->session->userdata('company');
//        $branch     = $this->session->userdata('branch');
//        
//        $this->db->select('*');
//        $this->db->from($this->table);
//        $this->db->join('am_accountgl', 'am_accountgl.AccountNumber = at_compaccountgl.AccountNumber', 'INNER');
//        $this->db->where('CompanyCode', $company);
//        $this->db->where('BranchCode', $branch);
//        $this->db->where('GLSaldoYears', date('Y'));
//        $query = $this->db->get();
//        
//        return $query->result();
//    }    
    
    function get_company_by_code($CompanyCode){
        return $this->db->get_where('am_company',array('CompanyCode'=>$CompanyCode))->row();
    }
    
    function insert($data){
        $this->db->insert($this->table, $data);
    }
	
    function delete($AccountNumber,$CompanyCode){
        $this->db->where('AccountNumber', $AccountNumber);
        $this->db->where_not_in('CompanyCode', $CompanyCode);	
        $this->db->where('GLSaldoYears', date('Y'));
        $this->db->delete($this->table);
    }
	
    function delete_all($AccountNumber) {
        $this->db->delete($this->table, array('AccountNumber' => $AccountNumber, 'GLSaldoYears'=>date('Y')));
    }

    function delete_branch_gl_account($company_code, $branchcode) {
        $this->db->delete($this->table, array(
                                            'CompanyCode'   => $company_code, 
                                            'BranchCode'    => $branchcode,
                                            'GLSaldoYears'  => date('Y')));
    }
    
    function update($data, $account_number, $company_code, $branch_code = '') {
        $company_code = $this->session->userdata('company');
        
        if($branch_code == ''){
            $branch_code = $this->session->userdata('branch');
        }
        
        $this->db->update($this->table, $data, array(
                                                'CompanyCode'       => $company_code,
                                                'BranchCode'        => $branch_code,
                                                'AccountNumber'     => $account_number,
                                                'GLSaldoYears'      => date('Y')));
    }

    function update_branch($data, $account_number, $branch_code ) {
        $this->db->update($this->table, $data, array(
                                                'AccountNumber'     => $account_number,
                                                'CompanyCode'       => $this->session->userdata('company'),
                                                'BranchCode'        => $branch_code,
                                                'GLSaldoYears'      => date('Y')));  
    }
    
    function update_opbalance($data, $accountnumber, $branchcode) {
        $this->db->update($this->table, $data, array(
                                                'AccountNumber'     => $accountnumber,
                                                'CompanyCode'       => $this->session->userdata('company'),
                                                'BranchCode'        => $branchcode,
                                                'GLSaldoYears'      => date('Y')));
    }
	
	function update_company_coa($accountnumber, $branch_code) {
        $this->db->update($this->table, array('TransStatus'=>2, 'PLStatus' => 1), array(
                                                'AccountNumber'     => $accountnumber,
                                                'BranchCode'       => $branch_code));
		//$this->db->update($this->account_gl, $data, array(
                                                //'AccountNumber'     => $accountnumber));
    }
    
//    function update_comp_pl_status($company_code) {
//        $this->db->update($this->table, array('PLStatus' => 2), array(
//                                                                    'CompanyCode'   => $company_code,
//                                                                    'GLSaldoYears'  => date('Y'),
//                                                                    'PLStatus'      => 1));
//        if ($this->db->trans_status() == FALSE) {
//            return 0;
//        }
//        else {
//            return 1;
//        }
//    }

    function update_pl_status($account_number, $company_code, $branch_code) {
        if ($account_number == '') { 
            $this->db->update($this->table, array('PLStatus'=>0, 'LastUserPL'=>$this->session->userdata("username")),        
                                array(
                                    'CompanyCode'   => $company_code,
                                    'BranchCode'    => $branch_code,
                                    'GLSaldoYears'  => date('Y'),
                                    'PLStatus'      => 1
                                    )
                              );          
        }
        else {            
            $this->db->update($this->table, array('PLStatus'=>1, 'LastUserPL'=>$this->session->userdata("username")),
                                array(
                                    'AccountNumber' => $account_number,
                                    'CompanyCode'   => $company_code,
                                    'BranchCode'    => $branch_code,
                                    'GLSaldoYears'  => date('Y')
                                    )
                             );
        }
    }
        
    function update_pl_active($account_number, $company_code, $branch_code, $pl_status) {
        $this->db->update($this->table, 
                            array(
                                'PLStatus'      => $pl_status, 
                                'LastUserPL'    => $this->session->userdata("username")),
                            array(
                                'AccountNumber' => $account_number,
                                'CompanyCode'   => $company_code,
                                'BranchCode'    => $branch_code,
                                'GLSaldoYears'  => date('Y'))
                        );
    }
    
    function check_if_exist($account_number, $company_code='', $branch_code, $glsaldoyears) {
       
        
        $query = $this->db->get_where($this->table, array(
                                                        'CompanyCode'       => $company_code, 
                                                        'BranchCode'        => $branch_code, 
                                                        'AccountNumber'     => $account_number, 
                                                        'GLSaldoYears'      => $glsaldoyears), 1, 0);
	
        if ($query->num_rows() > 0) {
            return TRUE;
        }
        else {
            return 0;
        }
    }
	
	//by fahri
	function check_if_exist_new($account_number, $company_code, $glsaldoyears) {
        $query = $this->db->get_where($this->table, array(
                                                        'CompanyCode'       => $company_code,
                                                        'AccountNumber'     => $account_number, 
                                                        'GLSaldoYears'      => $glsaldoyears), 1, 0);
	
        if ($query->num_rows() > 0) {
            return TRUE;
        }
        else {
            return 0;
        }
    }
	
	function check_if_exist_suspend($account_number, $branch_code) {
        $query = $this->db->get_where($this->table, array(
														'BranchCode'        => $branch_code, 
                                                        'AccountNumber'     => $account_number,
														'TransStatus'     	=> 3));
	
        if ($query->num_rows() > 0) {
            return TRUE;
        }
        else {
            return 0;
        }
    }
	//end

    function check_branch_pl_account($account_number, $company_code, $branch_code) {
        $this->db->where('AccountNumber', $account_number); 
        $this->db->where('CompanyCode', $company_code); 
        $this->db->where('BranchCode', $branch_code); 
        $this->db->where('GLSaldoYears', date('Y'));
        $query = $this->db->get($this->table);

        if ($query->num_rows() > 0) {
            return TRUE;
        }
        else {
            return FALSE;
        }
    }
	
	function check_branch_pl_account_new($account_number, $company_code, $branch_code) {
		$this->db->_protect_identifiers = FALSE;
        $this->db->select('at_compaccountgl.GLSaldoOpening, at_compaccountgl.AccountNumber, at_compaccountgl.GLSaldoBalance'.CURRENT_PERIOD.' as GLSaldoBalance, am_branch.BranchStatus');
        $this->db->from($this->table);
		$this->db->join('am_branch', 'am_branch.BranchCode = at_compaccountgl.BranchCode', 'INNER');
		
		$this->db->where('at_compaccountgl.AccountNumber', $account_number);
		$this->db->where('at_compaccountgl.BranchCode', $branch_code);
		$this->db->where('at_compaccountgl.CompanyCode', $company_code);
		$this->db->where('at_compaccountgl.GLSaldoYears', date('Y'));
		$this->db->where('am_branch.BranchStatus', 1);
		
		$this->db->_protect_identifiers = TRUE;
		
        $query = $this->db->get();
        if($query->num_rows() > 0){
            return $query->row();
        }
        else{
            return false;
        }
    }

    function check_pl_status($branch_check = ''){
        $company = $this->session->userdata('company');
        
        $this->db->select('*');
        $this->db->from($this->table);
        $this->db->where('CompanyCode', $company);
        
        if($branch_check != ''){
            $this->db->where('BranchCode', $this->session->userdata('branch'));
        }
        
        $this->db->where('GLSaldoYears', date('Y'));
        $this->db->where('PLStatus', 1);
        $query = $this->db->get();

        if ($query->num_rows() > 0) {
            return TRUE;
        }
        else {
            return FALSE;
        }
    }   

    function get_all_accounttye(){
        $this->db->where('ac.AccountTypeID,ac.AccountTypeName');
        $this->db->from($this->account_type.' as ac');
        $this->db->order_by('ac.AccountTypeID','ASC');

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

    function get_all_subtype(){
        $this->db->select('sub.AccountSubTypeID,sub.AccountTypeID,sub.AccountTypeName as SubTypeName,ac.AccountTypeName as AccTypeName,sub.SubTypeParentID,sub.AccountBSStatus,sub.AccountAutoStatus');
        $this->db->from($this->sub_type.' as sub');
        $this->db->join($this->acc_type.' as ac','sub.AccountTypeID=ac.AccountTypeID','inner');
        $this->db->order_by('sub.AccountTypeID','ASC');

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

    function get_all_currency($CompanyCode){

        if($CompanyCode ==''){
            $CompanyCode        = $this->session->userdata('company');
        }else{
            $CompanyCode        = $CompanyCode;
        }

        $this->db->select('cr.CurrencyCode,cr.CurrencyID,cr.CurrencyRate,cr.CompanyCode');
        $this->db->from($this->currency.' as cr');
        $this->db->where('cr.CompanyCode',$CompanyCode);
        $this->db->where('cr.TransStatus',1);

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

    function get_all(){

        $param          = $this->input->post('param');
        $Number         = isset($param['txt_IDNumber']) ? $param['txt_IDNumber'] : '';
        $Desc           = isset($param['txt_Description']) ? $param['txt_Description'] : '';
        
        $TransStatus    = isset($param['chk_TransStatus']) ? $param['chk_TransStatus'] : '';
        $BranchCode     = isset($param['cmb_branch']) ? $param['cmb_branch'] : '';
        $CompanyCode    = isset($param['cmb_company']) ? $param['cmb_company'] : '';
        $AccountType    = isset($param['account_type']) ? $param['account_type'] : '';
        /*============================================end============================================================*/
        
        /*======================================scrol view 1-150=======================================*/
        $page       = ($this->input->post('page')) ? intval($this->input->post('page')) : 1;
        $rows       = ($this->input->post('rows')) ? intval($this->input->post('rows')) : 150;
        
        $beginning  = ($page - 1) * $rows;
        $end        = $page * $rows;

         /*==========================================by session=======================================================*/
        $sess_company       = $this->session->userdata('company');
        $sess_branch        = $this->session->userdata('branch');
        $sess_branch_status = $this->session->userdata('branch_status');
        /*===========================================================================================================*/
        $this->db->_protect_identifiers = FALSE;
        $this->db->select("coa.AccountNumber,coa.AccountName, coa.AccountLevel, coa.AccountParent, coa.AccountSubTypeID, sub.AccountTypeName,acc.AccountTypeName as AccTypeName, coa.CurrencyCode, 
                            compgl.CompanyCode,compgl.BranchCode, cp.CompBranchStatus, coa.AppLevel1Status, coa.AppLevel2Status, br.BranchStatus, br.BranchName,sub.AccountBSStatus, 
                            compgl.TransStatus,sub.AccountAutoStatus, 
                            (CASE WHEN acc.AccountTypeName = 'Cash / Bank' THEN ((compgl.GLSaldoOpening + compgl.GLSaldoBSheet) * cr.CurrencyRate) ELSE (compgl.GLSaldoOpening + compgl.GLSaldoBSheet) END) as GLSaldoBSheet,compgl.GLSaldoOpening,compgl.GLSaldoBalance".CURRENT_PERIOD." as GLSaldoBalance,
                            (case when sub.AccountAutoStatus=0 then 'No' else 'Yes' end) as Account_AutoStatus,
                            (case when AccountChild = 1 then concat(coa.AccountName, ' - ', br.branchcode)else AccountName end) as AccountNameShow");
        $this->db->from($this->table.' as compgl');
        $this->db->join($this->account_gl.' as coa','coa.AccountNumber = compgl.AccountNumber','INNER');
        $this->db->join($this->sub_type.' as sub','coa.AccountSubTypeID = sub.AccountSubTypeID','LEFT');
        $this->db->join($this->acc_type.' as acc','sub.AccountTypeID = acc.AccountTypeID','LEFT');
        $this->db->join($this->company.' as cp','compgl.CompanyCode = cp.CompanyCode','INNER');
        $this->db->join($this->Branch.' as br','compgl.BranchCode = br.BranchCode','INNER');
        $this->db->join($this->currency.' as cr','cr.CurrencyCode=coa.CurrencyCode and cr.CompanyCode=compgl.CompanyCode','LEFT');

        $this->db->where('compgl.GLSaldoYears', date('Y'));
        if($Number != ""){$this->db->like('coa.AccountNumber', $Number);}
        if($Desc != ""){$this->db->like('coa.AccountName', $Desc);}
        if($AccountType != ""){
            if($AccountType == "All"){
                
            }
            else{
                $this->db->like('coa.AccountSubTypeID', $AccountType);
            }
        } 

        if($TransStatus!=''){ 
            $this->db->where_in('compgl.TransStatus',$TransStatus); 
        }else{
            $this->db->where_in('compgl.TransStatus',array(2,6,1)); 
        }

        if($BranchCode == ''){
            if($sess_branch_status == 0){
                $this->db->where('compgl.CompanyCode', $sess_company);
            }else{
                $this->db->where('compgl.CompanyCode', $sess_company);
                $this->db->where('compgl.BranchCode', $sess_branch);
            }
        }else{
            $this->db->where('compgl.CompanyCode', $CompanyCode);
            $this->db->where('compgl.BranchCode', $BranchCode);
        }

        $this->db->order_by('compgl.AccountNumber', 'ASC');
        $this->db->order_by('compgl.CompAccountGLID', 'ASC');     
        $this->db->order_by('br.BranchStatus', 'ASC');
        $query  = $this->db->limit($end, $beginning);
        
        $query=$this->db->get();    
        return $query->result();
    }
}