001/*
002 * Copyright 2009 The Kuali Foundation
003 * 
004 * Licensed under the Educational Community License, Version 2.0 (the "License");
005 * you may not use this file except in compliance with the License.
006 * You may obtain a copy of the License at
007 * 
008 * http://www.opensource.org/licenses/ecl2.php
009 * 
010 * Unless required by applicable law or agreed to in writing, software
011 * distributed under the License is distributed on an "AS IS" BASIS,
012 * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
013 * See the License for the specific language governing permissions and
014 * limitations under the License.
015 */
016package org.kuali.ole.gl.batch.dataaccess.impl;
017
018import java.util.List;
019import java.util.Map;
020
021import org.kuali.ole.gl.batch.dataaccess.LedgerEntryBalanceCachingDao;
022import org.kuali.ole.sys.service.UniversityDateService;
023import org.kuali.rice.core.framework.persistence.jdbc.dao.PlatformAwareDaoBaseJdbc;
024import org.kuali.rice.krad.service.PersistenceStructureService;
025
026/**
027 * This class...
028 */
029public class LedgerEntryBalanceCachingDaoJdbc extends PlatformAwareDaoBaseJdbc implements LedgerEntryBalanceCachingDao {
030
031    public List compareEntryHistory(String entryTable, String historyTable, int fiscalYear) {
032        List<Map<String, Object>> data = null;
033        
034        StringBuilder queryBuilder = new StringBuilder();
035
036        queryBuilder.append("select eh.* ");
037        queryBuilder.append("from " + historyTable + " eh ");
038        queryBuilder.append("left join ");
039        queryBuilder.append("( ");
040        queryBuilder.append("SELECT UNIV_FISCAL_YR, FIN_COA_CD, FIN_OBJECT_CD, FIN_BALANCE_TYP_CD, UNIV_FISCAL_PRD_CD, TRN_DEBIT_CRDT_CD, ");
041        queryBuilder.append("count(*) as entry_row_cnt, sum(TRN_LDGR_ENTR_AMT) as entry_amt ");
042        queryBuilder.append("FROM " + entryTable + " ");
043        queryBuilder.append("GROUP BY UNIV_FISCAL_YR, FIN_COA_CD, FIN_OBJECT_CD, FIN_BALANCE_TYP_CD, UNIV_FISCAL_PRD_CD, TRN_DEBIT_CRDT_CD ");
044        queryBuilder.append(") e ");
045        queryBuilder.append("on eh.univ_fiscal_yr = e.univ_fiscal_yr and eh.fin_coa_cd = e.fin_coa_cd and eh.fin_object_cd = e.fin_object_cd and ");
046        queryBuilder.append("eh.fin_balance_typ_cd = e.fin_balance_typ_cd and eh.univ_fiscal_prd_cd = e.univ_fiscal_prd_cd and eh.trn_debit_crdt_cd = e.trn_debit_crdt_cd ");
047        queryBuilder.append("where e.univ_fiscal_yr >= " + fiscalYear + " and (eh.row_cnt <> e.entry_row_cnt or eh.trn_ldgr_entr_amt <> e.entry_amt or e.entry_row_cnt is null) ");
048
049        data = getSimpleJdbcTemplate().queryForList(queryBuilder.toString());
050
051        return data;
052
053    }
054
055    public List compareBalanceHistory(String balanceTable, String historyTable, int fiscalYear) {
056        List<Map<String, Object>> data = null;
057        StringBuilder queryBuilder = new StringBuilder();
058
059        queryBuilder.append("select bh.* ");
060        queryBuilder.append("from " + historyTable + " bh  ");
061        queryBuilder.append("left join ( select ");
062        queryBuilder.append("UNIV_FISCAL_YR, FIN_COA_CD, ACCOUNT_NBR, SUB_ACCT_NBR, FIN_OBJECT_CD, FIN_SUB_OBJ_CD,  FIN_BALANCE_TYP_CD, FIN_OBJ_TYP_CD, ");
063        queryBuilder.append("ACLN_ANNL_BAL_AMT, FIN_BEG_BAL_LN_AMT, CONTR_GR_BB_AC_AMT, MO1_ACCT_LN_AMT, MO2_ACCT_LN_AMT, MO3_ACCT_LN_AMT, MO4_ACCT_LN_AMT, MO5_ACCT_LN_AMT, MO6_ACCT_LN_AMT, MO7_ACCT_LN_AMT, MO8_ACCT_LN_AMT, MO9_ACCT_LN_AMT, MO10_ACCT_LN_AMT, MO11_ACCT_LN_AMT, MO12_ACCT_LN_AMT, MO13_ACCT_LN_AMT ");
064        queryBuilder.append("from " + balanceTable + " ) e on ");
065        queryBuilder.append("bh.UNIV_FISCAL_YR = e.UNIV_FISCAL_YR and bh.FIN_COA_CD = e.FIN_COA_CD and bh.FIN_OBJECT_CD = e.FIN_OBJECT_CD and bh.FIN_BALANCE_TYP_CD = e.FIN_BALANCE_TYP_CD and bh.SUB_ACCT_NBR = e.SUB_ACCT_NBR and bh.ACCOUNT_NBR = e.ACCOUNT_NBR and bh.FIN_SUB_OBJ_CD = e.FIN_SUB_OBJ_CD and bh.FIN_OBJ_TYP_CD = e.FIN_OBJ_TYP_CD ");
066        queryBuilder.append(" where e.UNIV_FISCAL_YR >= " + fiscalYear + " ");
067        queryBuilder.append("and (bh.ACLN_ANNL_BAL_AMT <> e.ACLN_ANNL_BAL_AMT or bh.FIN_BEG_BAL_LN_AMT <> e.FIN_BEG_BAL_LN_AMT or bh.CONTR_GR_BB_AC_AMT <> e.CONTR_GR_BB_AC_AMT or  ");
068        queryBuilder.append("bh.MO1_ACCT_LN_AMT <> e.MO1_ACCT_LN_AMT or bh.MO2_ACCT_LN_AMT <> e.MO2_ACCT_LN_AMT or bh.MO3_ACCT_LN_AMT <> e.MO3_ACCT_LN_AMT or bh.MO4_ACCT_LN_AMT <> e.MO4_ACCT_LN_AMT or bh.MO5_ACCT_LN_AMT <> e.MO5_ACCT_LN_AMT or bh.MO6_ACCT_LN_AMT <> e.MO6_ACCT_LN_AMT or  ");
069        queryBuilder.append("bh.MO7_ACCT_LN_AMT <> e.MO7_ACCT_LN_AMT or bh.MO8_ACCT_LN_AMT <> e.MO8_ACCT_LN_AMT or bh.MO9_ACCT_LN_AMT <> e.MO9_ACCT_LN_AMT or bh.MO10_ACCT_LN_AMT <> e.MO10_ACCT_LN_AMT or bh.MO11_ACCT_LN_AMT <> e.MO11_ACCT_LN_AMT or bh.MO12_ACCT_LN_AMT <> e.MO12_ACCT_LN_AMT or  ");
070        queryBuilder.append("bh.MO13_ACCT_LN_AMT <> e.MO13_ACCT_LN_AMT) ");
071
072        data = getSimpleJdbcTemplate().queryForList(queryBuilder.toString());
073
074        return data;
075
076    }
077
078    public List accountBalanceCompareHistory(String accountBalanceTable, String historyTable, int fiscalYear) {
079        List<Map<String, Object>> data = null;
080        StringBuilder queryBuilder = new StringBuilder();
081
082        queryBuilder.append("select abh.* ");
083        queryBuilder.append("from " + historyTable + " abh ");
084        queryBuilder.append("left join  ");
085        queryBuilder.append("(select UNIV_FISCAL_YR, FIN_COA_CD, ACCOUNT_NBR, SUB_ACCT_NBR, FIN_OBJECT_CD, FIN_SUB_OBJ_CD, CURR_BDLN_BAL_AMT, ACLN_ACTLS_BAL_AMT, ACLN_ENCUM_BAL_AMT ");
086        queryBuilder.append("from " + accountBalanceTable + " ) ab on ");
087        queryBuilder.append("abh.UNIV_FISCAL_YR = ab.UNIV_FISCAL_YR and abh.FIN_COA_CD = ab.FIN_COA_CD and abh.ACCOUNT_NBR = ab.ACCOUNT_NBR and abh.SUB_ACCT_NBR = ab.SUB_ACCT_NBR and abh.FIN_OBJECT_CD = ab.FIN_OBJECT_CD and abh.FIN_SUB_OBJ_CD = ab.FIN_SUB_OBJ_CD ");
088        queryBuilder.append("where ab.UNIV_FISCAL_YR >= " + fiscalYear + " ");
089        queryBuilder.append("and (abh.CURR_BDLN_BAL_AMT <> ab.CURR_BDLN_BAL_AMT or abh.ACLN_ACTLS_BAL_AMT <> ab.ACLN_ACTLS_BAL_AMT or abh.ACLN_ENCUM_BAL_AMT <> ab.ACLN_ENCUM_BAL_AMT) ");
090
091        data = getSimpleJdbcTemplate().queryForList(queryBuilder.toString());
092
093        return data;
094
095    }
096
097    public List encumbranceCompareHistory(String encumbranceTable, String historyTable, int fiscalYear) {
098        List<Map<String, Object>> data = null;
099        StringBuilder queryBuilder = new StringBuilder();
100
101        queryBuilder.append("select eh.*  ");
102        queryBuilder.append("from " + historyTable + " eh ");
103        queryBuilder.append("left join  ( ");
104        queryBuilder.append("select UNIV_FISCAL_YR, FIN_COA_CD, ACCOUNT_NBR, SUB_ACCT_NBR, FIN_OBJECT_CD, FIN_SUB_OBJ_CD, FIN_BALANCE_TYP_CD, FDOC_TYP_CD, FS_ORIGIN_CD, FDOC_NBR, ACLN_ENCUM_AMT, ACLN_ENCUM_CLS_AMT from " + encumbranceTable + " ) e on ");
105        queryBuilder.append("eh.UNIV_FISCAL_YR = e.UNIV_FISCAL_YR and eh.FIN_COA_CD = e.FIN_COA_CD and eh.ACCOUNT_NBR = e.ACCOUNT_NBR and eh.SUB_ACCT_NBR = e.SUB_ACCT_NBR and eh.FIN_OBJECT_CD = e.FIN_OBJECT_CD and eh.FIN_SUB_OBJ_CD = e.FIN_SUB_OBJ_CD and eh.FIN_BALANCE_TYP_CD = e.FIN_BALANCE_TYP_CD and eh.FDOC_TYP_CD = e.FDOC_TYP_CD and eh.FS_ORIGIN_CD = e.FS_ORIGIN_CD and eh.FDOC_NBR = e.FDOC_NBR ");
106        queryBuilder.append("where e.UNIV_FISCAL_YR >= " + fiscalYear + " and (eh.ACLN_ENCUM_AMT <> e.ACLN_ENCUM_AMT or eh.ACLN_ENCUM_CLS_AMT <> e.ACLN_ENCUM_CLS_AMT) ");
107
108        data = getSimpleJdbcTemplate().queryForList(queryBuilder.toString());
109
110        return data;
111
112    }
113
114}