001 /*
002 * Copyright 2011 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 */
016 package org.kuali.kfs.gl.batch.dataaccess.impl;
017
018 import java.util.Iterator;
019 import java.util.List;
020 import java.util.Map;
021
022 import org.kuali.kfs.gl.batch.dataaccess.LedgerEntryBalanceCachingDao;
023 import org.kuali.kfs.sys.service.UniversityDateService;
024 import org.kuali.rice.kns.dao.jdbc.PlatformAwareDaoBaseJdbc;
025 import org.kuali.rice.kns.service.PersistenceStructureService;
026
027 /**
028 * This class...
029 */
030 public class LedgerEntryBalanceCachingDaoJdbc extends PlatformAwareDaoBaseJdbc implements LedgerEntryBalanceCachingDao {
031
032 PersistenceStructureService persistenceStructureService;
033 UniversityDateService universityDateService;
034
035 public List compareEntryHistory(Class entryClass, Class historyClass, int pastYears) {
036 List<Map<String, Object>> data = null;
037
038 String entryTable = persistenceStructureService.getTableName(entryClass);
039 String historyTable = persistenceStructureService.getTableName(historyClass);
040
041 //String[] pks = persistenceService.getPrimaryKeys(arg0)
042 int fiscalYear = universityDateService.getCurrentFiscalYear()-pastYears;
043
044 StringBuilder queryBuilder = new StringBuilder();
045
046 queryBuilder.append("select eh.* ");
047 queryBuilder.append("from "+historyTable+" eh ");
048 queryBuilder.append("left join ");
049 queryBuilder.append("( ");
050 queryBuilder.append("SELECT UNIV_FISCAL_YR, FIN_COA_CD, FIN_OBJECT_CD, FIN_BALANCE_TYP_CD, UNIV_FISCAL_PRD_CD, TRN_DEBIT_CRDT_CD, ");
051 queryBuilder.append("count(*) as entry_row_cnt, sum(TRN_LDGR_ENTR_AMT) as entry_amt ");
052 queryBuilder.append("FROM "+entryTable+" ");
053 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 ");
054 queryBuilder.append(") e ");
055 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 ");
056 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 ");
057 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) ");
058
059 data = getSimpleJdbcTemplate().queryForList(queryBuilder.toString());
060
061 return data;
062
063 }
064
065 public List compareBalanceHistory(Class balanceClass, Class historyClass, int pastYears) {
066 List<Map<String, Object>> data = null;
067
068 String balanceTable = persistenceStructureService.getTableName(balanceClass);
069 String historyTable = persistenceStructureService.getTableName(historyClass);
070
071
072 int fiscalYear = universityDateService.getCurrentFiscalYear()-pastYears;
073
074 StringBuilder queryBuilder = new StringBuilder();
075
076
077 queryBuilder.append("select bh.* ");
078 queryBuilder.append("from "+historyTable+" bh ");
079 queryBuilder.append("left join ( select ");
080 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, ");
081 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 ");
082 queryBuilder.append("from "+balanceTable+" ) e on ");
083 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 ");
084 queryBuilder.append(" where e.UNIV_FISCAL_YR >= "+fiscalYear+" ");
085 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 ");
086 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 ");
087 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 ");
088 queryBuilder.append("bh.MO13_ACCT_LN_AMT <> e.MO13_ACCT_LN_AMT) ");
089
090
091 data = getSimpleJdbcTemplate().queryForList(queryBuilder.toString());
092
093 return data;
094
095 }
096
097 public List accountBalanceCompareHistory(Class accountBalanceClass, Class historyClass, int pastYears) {
098 List<Map<String, Object>> data = null;
099
100 String accountBalanceTable = persistenceStructureService.getTableName(accountBalanceClass);
101 String historyTable = persistenceStructureService.getTableName(historyClass);
102
103 //String[] pks = persistenceService.getPrimaryKeys(arg0)
104 int fiscalYear = universityDateService.getCurrentFiscalYear()-pastYears;
105
106 StringBuilder queryBuilder = new StringBuilder();
107
108 queryBuilder.append("select abh.* ");
109 queryBuilder.append("from "+historyTable+" abh ");
110 queryBuilder.append("left join ");
111 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 ");
112 queryBuilder.append("from "+accountBalanceTable+" ) ab on ");
113 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 ");
114 queryBuilder.append("where ab.UNIV_FISCAL_YR >= "+fiscalYear+" ");
115 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) ");
116
117 data = getSimpleJdbcTemplate().queryForList(queryBuilder.toString());
118
119 return data;
120
121 }
122
123 public List encumbranceCompareHistory(Class encumbranceClass, Class historyClass, int pastYears) {
124 List<Map<String, Object>> data = null;
125
126 String encumbranceTable = persistenceStructureService.getTableName(encumbranceClass);
127 String historyTable = persistenceStructureService.getTableName(historyClass);
128
129 //String[] pks = persistenceService.getPrimaryKeys(arg0)
130 int fiscalYear = universityDateService.getCurrentFiscalYear()-pastYears;
131
132 StringBuilder queryBuilder = new StringBuilder();
133
134 queryBuilder.append("select eh.* ");
135 queryBuilder.append("from "+historyTable+" eh ");
136 queryBuilder.append("left join ( ");
137 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 ");
138 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 ");
139 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) ");
140
141 data = getSimpleJdbcTemplate().queryForList(queryBuilder.toString());
142
143 return data;
144
145 }
146
147 /**
148 * Sets the persistenceStructureService attribute value.
149 * @param persistenceStructureService The persistenceStructureService to set.
150 */
151 public void setPersistenceStructureService(PersistenceStructureService persistenceStructureService) {
152 this.persistenceStructureService = persistenceStructureService;
153 }
154
155 /**
156 * Sets the universityDateService attribute value.
157 * @param universityDateService The universityDateService to set.
158 */
159 public void setUniversityDateService(UniversityDateService universityDateService) {
160 this.universityDateService = universityDateService;
161 }
162
163 /**
164 * Gets the persistenceStructureService attribute.
165 * @return Returns the persistenceStructureService.
166 */
167 public PersistenceStructureService getPersistenceStructureService() {
168 return persistenceStructureService;
169 }
170
171 /**
172 * Gets the universityDateService attribute.
173 * @return Returns the universityDateService.
174 */
175 public UniversityDateService getUniversityDateService() {
176 return universityDateService;
177 }
178
179
180 }