<?php

class M_index_to_report extends CI_Model{
    public $trans_journal   = 'av_trans_journal';
    public $comp_gl         = 'at_compaccountgl';
    public $account_gl      = 'am_accountgl';
    
    public $branch          = 'am_branch';
    public $company         = 'am_company';
    public $currency        = 'am_currency';
    
    function get_pl_data($begin_date, $end_date, $sub_type_id){
        $company_code   = $this->session->userdata('company');
        $branch_code    = $this->session->userdata('branch');
        $branch_status  = $this->session->userdata('branch_status');
        
        $this->db->_protect_identifiers = FALSE;
        $this->db->select("*, sum(Saldo_Debet) as Total_Debet, sum(Saldo_Credit) as Total_Credit,
                         (case when br.BranchStatus = 0 then Account_Name else concat(Account_Name, ' - ', BranchCode) end) as Branch_Account");
        $this->db->from($this->trans_journal . ' tj');
        $this->db->join($this->branch . ' br', 'br.BranchCode = tj.Branch_Code', 'INNER');
        $this->db->where('CompanyCode', $company_code);
        $this->db->where('Sub_Type_ID', $sub_type_id);
        $this->db->where('Journal_Date >= ', date('Y-m-d', strtotime($begin_date)));
        $this->db->where('Journal_Date <= ', date('Y-m-d', strtotime($end_date)));
        
        if($branch_status == 1){
            $this->db->where('Branch_Code', $branch_code);            
        }
        
        $this->db->group_by('Branch_Code');
        $this->db->group_by('Account_Number');
        $this->db->order_by('Journal_Date', 'Asc');
        $query = $this->db->get();
        $this->db->_protect_identifiers = TRUE;
        
        return $query->result();
    }
    
    function get_journal_index_report($branch_code, $account_number, $begin_date, $end_date){
        $this->db->_protect_identifiers = FALSE;
        $this->db->select('tj.*, br.BranchName, cp.CompanyName');
        $this->db->from($this->trans_journal . ' tj');
        $this->db->join($this->branch . ' br', 'br.BranchCode = tj.Branch_Code', 'INNER');
        $this->db->join($this->company . ' cp', 'cp.CompanyCode = br.CompanyCode', 'INNER');
        $this->db->where('tj.Branch_Code', $branch_code);
        $this->db->where('tj.Account_Number', $account_number);
        $this->db->where('tj.Journal_Date >= ', date('Y-m-d', strtotime($begin_date)));
        $this->db->where('tj.Journal_Date <= ', date('Y-m-d', strtotime($end_date)));
        $this->db->order_by('tj.Journal_ID', 'Asc');
        $query = $this->db->get();
        $this->db->_protect_identifiers = TRUE;
        
        return $query->result();
    }
    
    function get_coa_balance_sheet($period_month, $period_year, $order_type,$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("cg.CompanyCode, cg.BranchCode, cg.AccountNumber, ag.AccountName, ag.AccountLevel, ag.AccountParent, ag.AccountChild, ag.CurrencyCode, 
                          (case 
                            when br.BranchStatus = 0 then ag.AccountName else concat(ag.AccountName, ' - ', br.BranchCode) end) as Branch_Account,
                          (case
                            when ag.CurrencyCode != 'IDR' and (Left(ag.AccountNumber, 6) = '1-3-01' Or Left(ag.AccountNumber, 6) = '1-3-02' Or Left(ag.AccountNumber, 6) = '1-3-03')
                            Then GLSaldoBalance" . intval($period_month) . " * (Select CurrencyRate From am_currency where CurrencyCode = ag.CurrencyCode)
                            else GLSaldoBalance" . intval($period_month) . " end) as Saldo_Balance");
        $this->db->from($this->comp_gl . ' cg');
        $this->db->join($this->branch . ' br', 'br.BranchCode = cg.BranchCode', 'INNER');
        $this->db->join($this->account_gl . ' ag', 'ag.AccountNumber = cg.AccountNumber', 'INNER');
        $this->db->join($this->currency . ' cy', 'cy.CurrencyCode = ag.CurrencyCode', 'INNER');
        $this->db->where('cg.CompanyCode', $company_code);

        if($branch_status == 1){
            $this->db->where('Branch_Code', $branch_code);            
        }

        $this->db->where('cg.GLSaldoYears', $period_year);
        $this->db->where('ag.AccountSubTypeID Not IN (Select AccountSubTypeID from am_accountgl where AccountSubTypeID in (65, 68, 71, 75, 76) and ag.AccountChild = 1)');

        if($order_type == 0){
            $this->db->order_by('cg.AccountNumber', 'ASC');
            $this->db->order_by('br.BranchStatus', 'ASC');
            $this->db->order_by('br.BranchName', 'ASC');
        }
        else{
            $this->db->order_by('cg.AccountNumber', 'Desc');            
        }
        
        $query = $this->db->get();
        $this->db->_protect_identifiers = TRUE;
        
        return $query->result();            
    }
    
    function get_coa_saldo_balance($period_month, $period_year){
        $company_code   = $this->session->userdata('company');
        $branch_code    = $this->session->userdata('branch');
        $branch_status  = $this->session->userdata('branch_status');
        
        $this->db->_protect_identifiers = FALSE;
        $this->db->select("ag.AccountChild, 
                         (case when ag.AccountParent = '' then ag.AccountNumber else ag.AccountParent end) as Account_Parent, cg.AccountNumber,
                          sum(case 
                            when ag.CurrencyCode != 'IDR' and (Left(ag.AccountNumber, 6) = '1-3-01' Or Left(ag.AccountNumber, 6) = '1-3-02' Or Left(ag.AccountNumber, 6) = '1-3-03')
                            then IF(GLSaldoBalance" . intval($period_month) . " IS NULL, 0, (GLSaldoBalance" . intval($period_month) . " * (Select CurrencyRate From am_currency where CurrencyCode = ag.CurrencyCode))) 
                            else IF(GLSaldoBalance" . intval($period_month) . " IS NULL, 0, (GLSaldoBalance" . intval($period_month)  . ")) end) as Saldo_Balance");
        $this->db->from($this->comp_gl . ' cg'); 
        $this->db->join($this->account_gl . ' ag', 'ag.AccountNumber = cg.AccountNumber', 'INNER');
        $this->db->where('cg.CompanyCode', $company_code);
        if($branch_status == 1){$this->db->where('Branch_Code', $branch_code);}       
        $this->db->where('cg.GLSaldoYears', $period_year);
        $this->db->where('ag.AccountSubTypeID Not IN (Select AccountSubTypeID from am_accountgl where AccountSubTypeID in (65, 68, 71, 75, 76) and ag.AccountChild = 1)');
        $this->db->group_by('cg.CompanyCode');
        if($branch_status == 1){$this->db->group_by('cg.BranchCode');}        
        $this->db->group_by('ag.AccountParent');
        $this->db->order_by('ag.AccountParent', 'ASC');
        $query = $this->db->get();
        $this->db->_protect_identifiers = TRUE;
        
        return $query->result();
    }
}