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.module.endow.batch.dataaccess.impl;
017
018 import java.math.BigDecimal;
019 import java.util.ArrayList;
020 import java.util.Collection;
021
022 import org.kuali.kfs.module.endow.EndowConstants;
023 import org.kuali.kfs.module.endow.EndowPropertyConstants;
024 import org.kuali.kfs.module.endow.batch.dataaccess.GLInterfaceBatchProcessDao;
025 import org.kuali.kfs.module.endow.businessobject.GLCombinedTransactionArchive;
026 import org.kuali.kfs.module.endow.businessobject.GlInterfaceBatchProcessKemLine;
027 import org.kuali.kfs.module.endow.businessobject.TransactionArchiveSecurity;
028 import org.kuali.kfs.module.endow.dataaccess.GLLinkDao;
029 import org.kuali.kfs.module.endow.dataaccess.KemidGeneralLedgerAccountDao;
030 import org.kuali.kfs.module.endow.dataaccess.TransactionArchiveSecurityDao;
031 import org.kuali.rice.kns.dao.jdbc.PlatformAwareDaoBaseJdbc;
032 import org.kuali.rice.kns.util.ObjectUtils;
033 import org.springframework.jdbc.support.rowset.SqlRowSet;
034
035 /**
036 * A class to do the database queries needed to calculate Balance By Consolidation Balance Inquiry Screen
037 */
038 public class GLInterfaceBatchProcessDaoJdbc extends PlatformAwareDaoBaseJdbc implements GLInterfaceBatchProcessDao {
039 private static org.apache.log4j.Logger LOG = org.apache.log4j.Logger.getLogger(GLInterfaceBatchProcessDaoJdbc.class);
040
041 protected KemidGeneralLedgerAccountDao kemidGeneralLedgerAccountDao;
042 protected GLLinkDao gLLinkDao;
043 protected TransactionArchiveSecurityDao transactionArchiveSecurityDao;
044
045 /**
046 * @see org.kuali.kfs.module.endow.batch.dataaccess.GLInterfaceBatchProcessDao#findDocumentTypes()
047 */
048 public Collection<String> findDocumentTypes() {
049 LOG.info("findDocumentTypes() started");
050
051 Collection<String> documentTypes = new ArrayList<String>();
052
053 SqlRowSet documentTypesRowSet = getJdbcTemplate().queryForRowSet("SELECT DISTINCT(DOC_TYP_NM) DOC_TYP_NM FROM END_TRAN_ARCHV_T ORDER BY DOC_TYP_NM");
054
055 while (documentTypesRowSet.next()) {
056 documentTypes.add(documentTypesRowSet.getString(EndowPropertyConstants.ColumnNames.GlInterfaceBatchProcessLine.TRANSACTION_ARCHIVE_DOC_TYP_NM));
057 }
058
059 LOG.info("findDocumentTypes() exited");
060
061 return documentTypes;
062 }
063
064 /**
065 * @see org.kuali.kfs.module.endow.batch.dataaccess.GLInterfaceBatchProcessDao#getAllKemTransactions(java.util.Date)
066 */
067 public Collection<GlInterfaceBatchProcessKemLine> getAllKemTransactions(java.util.Date postedDate) {
068 LOG.info("getAllKemTransactions() started");
069
070 Collection<GlInterfaceBatchProcessKemLine> kemArchiveTransactions = new ArrayList<GlInterfaceBatchProcessKemLine>();
071
072 //get all the available document types names sorted
073 Collection<String> documentTypes = findDocumentTypes();
074
075 for (String documentType : documentTypes) {
076 //get the cash activity records...
077 SqlRowSet cashTransactionActivities = getAllKemTransactions(documentType, postedDate, EndowConstants.TransactionSubTypeCode.CASH);
078 buildTransactionActivities(kemArchiveTransactions, cashTransactionActivities, true);
079
080 //get non-cash activity records....
081 SqlRowSet nonCashTransactionActivities = getAllKemTransactions(documentType, postedDate, EndowConstants.TransactionSubTypeCode.NON_CASH);
082 buildTransactionActivities(kemArchiveTransactions, nonCashTransactionActivities, false);
083 }
084
085 LOG.info("getAllKemTransactions() exited");
086
087 return kemArchiveTransactions;
088 }
089
090 /**
091 * @see org.kuali.kfs.module.endow.batch.dataaccess.GLInterfaceBatchProcessDao#getAllCombinedKemTransactions(java.util.Date)
092 */
093 public Collection<GlInterfaceBatchProcessKemLine> getAllCombinedKemTransactions(java.util.Date postedDate) {
094 LOG.info("getAllCombinedKemTransactions() started");
095
096 Collection<GlInterfaceBatchProcessKemLine> kemCombinedArchiveTransactions = new ArrayList<GlInterfaceBatchProcessKemLine>();
097 Collection<GlInterfaceBatchProcessKemLine> kemArchiveTransactions = new ArrayList<GlInterfaceBatchProcessKemLine>();
098
099 //get all the available document types names sorted
100 Collection<String> documentTypes = findDocumentTypes();
101
102 for (String documentType : documentTypes) {
103 //get the cash activity records...
104 SqlRowSet cashTransactionActivities = getAllKemTransactions(documentType, postedDate, EndowConstants.TransactionSubTypeCode.CASH);
105 buildTransactionActivities(kemArchiveTransactions, cashTransactionActivities, true);
106 buildCombinedTransactionActivities(kemCombinedArchiveTransactions, kemArchiveTransactions, true);
107
108 //get non-cash activity records....
109 SqlRowSet nonCashTransactionActivities = getAllKemTransactions(documentType, postedDate, EndowConstants.TransactionSubTypeCode.NON_CASH);
110 buildTransactionActivities(kemArchiveTransactions, nonCashTransactionActivities, false);
111 buildCombinedTransactionActivities(kemCombinedArchiveTransactions, kemArchiveTransactions, false);
112 }
113
114 LOG.info("getAllCombinedKemTransactions() exited.");
115
116 return kemCombinedArchiveTransactions;
117 }
118
119 /**
120 * @see org.kuali.kfs.module.endow.batch.dataaccess.GLInterfaceBatchProcessDao#getAllKemTransactionsByDocumentType(Stringjava.util.Date)
121 */
122 public Collection<GlInterfaceBatchProcessKemLine> getAllKemTransactionsByDocumentType(String documentType, java.util.Date postedDate) {
123 LOG.info("getAllKemTransactionsByDocumentType() started");
124
125 Collection<GlInterfaceBatchProcessKemLine> kemArchiveTransactions = new ArrayList<GlInterfaceBatchProcessKemLine>();
126
127 //get the cash activity records...
128 SqlRowSet cashTransactionActivities = getAllKemTransactions(documentType, postedDate, EndowConstants.TransactionSubTypeCode.CASH);
129 buildTransactionActivities(kemArchiveTransactions, cashTransactionActivities, true);
130
131 //get non-cash activity records....
132 SqlRowSet nonCashTransactionActivities = getAllKemTransactions(documentType, postedDate, EndowConstants.TransactionSubTypeCode.NON_CASH);
133 buildTransactionActivities(kemArchiveTransactions, nonCashTransactionActivities, false);
134
135 LOG.info("getAllKemTransactionsByDocumentType() exited.");
136
137 return kemArchiveTransactions;
138 }
139
140 /**
141 * @see org.kuali.kfs.module.endow.batch.dataaccess.GLInterfaceBatchProcessDao#getAllKemCombinedTransactionsByDocumentType(Stringjava.util.Date)
142 */
143 public Collection<GlInterfaceBatchProcessKemLine> getAllKemCombinedTransactionsByDocumentType(String documentType, java.util.Date postedDate) {
144 LOG.info("getAllKemCombinedTransactionsByDocumentType() started");
145
146 Collection<GlInterfaceBatchProcessKemLine> kemCombinedArchiveTransactions = new ArrayList<GlInterfaceBatchProcessKemLine>();
147 Collection<GlInterfaceBatchProcessKemLine> kemCashArchiveTransactions = new ArrayList<GlInterfaceBatchProcessKemLine>();
148 Collection<GlInterfaceBatchProcessKemLine> kemNonCashArchiveTransactions = new ArrayList<GlInterfaceBatchProcessKemLine>();
149
150 //get the cash activity records...
151 SqlRowSet cashTransactionActivities = getAllKemTransactions(documentType, postedDate, EndowConstants.TransactionSubTypeCode.CASH);
152 buildTransactionActivities(kemCashArchiveTransactions, cashTransactionActivities, true);
153 if (kemCashArchiveTransactions.size() > 0) {
154 buildCombinedTransactionActivities(kemCombinedArchiveTransactions, kemCashArchiveTransactions, true);
155 }
156
157 //get non-cash activity records....
158 SqlRowSet nonCashTransactionActivities = getAllKemTransactions(documentType, postedDate, EndowConstants.TransactionSubTypeCode.NON_CASH);
159 buildTransactionActivities(kemNonCashArchiveTransactions, nonCashTransactionActivities, false);
160 if (kemNonCashArchiveTransactions.size() > 0) {
161 buildCombinedTransactionActivities(kemCombinedArchiveTransactions, kemNonCashArchiveTransactions, false);
162 }
163
164 LOG.info("getAllKemCombinedTransactionsByDocumentType() exited.");
165
166 return kemCombinedArchiveTransactions;
167 }
168
169 /**
170 * Method to get the cash activity transactions for a given document type.
171 * @param documenType, postedDate, sortOrder
172 * joins records from END_TRAN_ARCHV_T, END_KEMID_GL_LNK_T, and END_ETRAN_GL_LNK_T tables in the given sort order
173 */
174 protected SqlRowSet getAllKemTransactions(String documentType, java.util.Date postedDate, String TransactionSubTypeCode) {
175 // String transactionArchiveSql = ("SELECT a.FDOC_NBR, a.FDOC_LN_NBR, a.FDOC_LN_TYP_CD, a.DOC_TYP_NM, a.TRAN_SUB_TYP_CD, "
176 // + "a.TRAN_KEMID, a.TRAN_ETRAN_CD, a.TRAN_IP_IND_CD, a.TRAN_INC_CSH_AMT, a.TRAN_PRIN_CSH_AMT, "
177 // + "c.CHRT_CD, c.ACCT_NBR, "
178 // + "d.TRAN_SEC_COST, d.TRAN_SEC_LT_GAIN_LOSS, d.TRAN_SEC_ST_GAIN_LOSS, d.TRAN_SEC_ETRAN_CD "
179 // + "FROM END_TRAN_ARCHV_T a, END_KEMID_GL_LNK_T c, END_TRAN_ARCHV_SEC_T d "
180 // + "WHERE a.TRAN_PSTD_DT = ? AND a.DOC_TYP_NM = ? AND a.TRAN_SUB_TYP_CD = ? AND "
181 // + "a.TRAN_KEMID = c.KEMID AND a.TRAN_IP_IND_CD = c.IP_IND_CD AND c.ROW_ACTV_IND = 'Y' AND "
182 // + "a.FDOC_NBR = d.FDOC_NBR AND a.FDOC_LN_NBR = d.FDOC_LN_NBR AND a.FDOC_LN_TYP_CD = d.FDOC_LN_TYP_CD "
183 // + "ORDER BY a.DOC_TYP_NM, c.CHRT_CD, c.ACCT_NBR, d.TRAN_SEC_ETRAN_CD, a.TRAN_ETRAN_CD, a.TRAN_KEMID");
184
185 String transactionArchiveSql = ("SELECT a.FDOC_NBR, a.FDOC_LN_NBR, a.FDOC_LN_TYP_CD, a.DOC_TYP_NM, a.TRAN_SUB_TYP_CD, "
186 + "a.TRAN_KEMID, a.TRAN_ETRAN_CD, a.TRAN_IP_IND_CD, a.TRAN_INC_CSH_AMT, a.TRAN_PRIN_CSH_AMT, "
187 + "c.CHRT_CD, c.ACCT_NBR "
188 + "FROM END_TRAN_ARCHV_T a, END_KEMID_GL_LNK_T c "
189 + "WHERE a.TRAN_PSTD_DT = ? AND a.DOC_TYP_NM = ? AND a.TRAN_SUB_TYP_CD = ? AND "
190 + "a.TRAN_KEMID = c.KEMID AND a.TRAN_IP_IND_CD = c.IP_IND_CD AND c.ROW_ACTV_IND = 'Y' "
191 + "ORDER BY a.DOC_TYP_NM, c.CHRT_CD, c.ACCT_NBR, a.TRAN_ETRAN_CD, a.TRAN_KEMID");
192
193 return (getJdbcTemplate().queryForRowSet(transactionArchiveSql, new Object[] { postedDate, documentType, TransactionSubTypeCode }));
194 }
195
196 /**
197 * method to go through the rowset and put into transient bo and add to the collection.
198 */
199 protected void buildTransactionActivities(Collection<GlInterfaceBatchProcessKemLine> kemArchiveTransactions, SqlRowSet archiveTransactions, boolean cashType) {
200 LOG.info("buildTransactionActivities() started");
201
202 while (archiveTransactions.next()) {
203 GlInterfaceBatchProcessKemLine glKemLine = new GlInterfaceBatchProcessKemLine();
204
205 glKemLine.setDocumentNumber(archiveTransactions.getString(EndowPropertyConstants.ColumnNames.GlInterfaceBatchProcessLine.TRANSACTION_ARCHIVE_FDOC_NBR));
206 glKemLine.setLineNumber(archiveTransactions.getInt(EndowPropertyConstants.ColumnNames.GlInterfaceBatchProcessLine.TRANSACTION_ARCHIVE_FDOC_LN_NBR));
207 glKemLine.setLineTypeCode(archiveTransactions.getString(EndowPropertyConstants.ColumnNames.GlInterfaceBatchProcessLine.TRANSACTION_ARCHIVE_FDOC_LN_TYP_CD));
208 glKemLine.setSubTypeCode(archiveTransactions.getString(EndowPropertyConstants.ColumnNames.GlInterfaceBatchProcessLine.TRANSACTION_ARCHIVE_TRAN_SUB_TYP_CD));
209 glKemLine.setTypeCode(archiveTransactions.getString(EndowPropertyConstants.ColumnNames.GlInterfaceBatchProcessLine.TRANSACTION_ARCHIVE_DOC_TYP_NM));
210 glKemLine.setKemid(archiveTransactions.getString(EndowPropertyConstants.ColumnNames.GlInterfaceBatchProcessLine.TRANSACTION_ARCHIVE_KEM_ID));
211 glKemLine.setIncomePrincipalIndicatorCode(archiveTransactions.getString(EndowPropertyConstants.ColumnNames.GlInterfaceBatchProcessLine.TRANSACTION_ARCHIVE_TRAN_IP_IND_CD));
212
213 glKemLine.setChartCode(archiveTransactions.getString(EndowPropertyConstants.ColumnNames.GlInterfaceBatchProcessLine.TRANSACTION_ARCHIVE_CHRT_CD));
214 glKemLine.setAccountNumber(archiveTransactions.getString(EndowPropertyConstants.ColumnNames.GlInterfaceBatchProcessLine.TRANSACTION_ARCHIVE_ACCT_NBR));
215
216 //get the security record now....
217 BigDecimal holdingCost = BigDecimal.ZERO;
218 BigDecimal shortTermGainLoss = BigDecimal.ZERO;
219 BigDecimal longTermGainLoss = BigDecimal.ZERO;
220 String eTransactionCode = "";
221
222 TransactionArchiveSecurity transactionArchiveSecurity = transactionArchiveSecurityDao.getByPrimaryKey(glKemLine.getDocumentNumber(), glKemLine.getLineNumber(), glKemLine.getLineTypeCode());
223 if (ObjectUtils.isNotNull(transactionArchiveSecurity)) {
224 eTransactionCode = transactionArchiveSecurity.getEtranCode();
225 holdingCost = transactionArchiveSecurity.getHoldingCost();
226 shortTermGainLoss = transactionArchiveSecurity.getShortTermGainLoss();
227 longTermGainLoss = transactionArchiveSecurity.getLongTermGainLoss();
228 }
229
230 //get transaction amount....
231 if (cashType) {
232 glKemLine.setTransactionArchiveIncomeAmount(archiveTransactions.getBigDecimal(EndowPropertyConstants.ColumnNames.GlInterfaceBatchProcessLine.TRANSACTION_ARCHIVE_TRAN_INC_CSH_AMT));
233 glKemLine.setTransactionArchivePrincipalAmount(archiveTransactions.getBigDecimal(EndowPropertyConstants.ColumnNames.GlInterfaceBatchProcessLine.TRANSACTION_ARCHIVE_TRAN_PRIN_CSH_AMT));
234 glKemLine.setHoldingCost(BigDecimal.ZERO);
235 glKemLine.setLongTermGainLoss(BigDecimal.ZERO);
236 glKemLine.setShortTermGainLoss(BigDecimal.ZERO);
237 if (glKemLine.getTypeCode().equalsIgnoreCase(EndowConstants.DocumentTypeNames.ENDOWMENT_ASSET_DECREASE)) {
238 glKemLine.setHoldingCost(holdingCost);
239 glKemLine.setShortTermGainLoss(shortTermGainLoss);
240 glKemLine.setLongTermGainLoss(longTermGainLoss);
241 }
242 }
243 else {
244 glKemLine.setTransactionArchiveIncomeAmount(BigDecimal.ZERO);
245 glKemLine.setTransactionArchivePrincipalAmount(BigDecimal.ZERO);
246 glKemLine.setHoldingCost(holdingCost);
247 glKemLine.setShortTermGainLoss(shortTermGainLoss);
248 glKemLine.setLongTermGainLoss(longTermGainLoss);
249 }
250
251 //get the object code..
252 if (EndowConstants.TransactionSubTypeCode.CASH.equalsIgnoreCase(glKemLine.getSubTypeCode()) &&
253 (glKemLine.getTypeCode().equalsIgnoreCase(EndowConstants.DocumentTypeNames.ENDOWMENT_CASH_INCREASE) ||
254 glKemLine.getTypeCode().equalsIgnoreCase(EndowConstants.DocumentTypeNames.ENDOWMENT_CASH_DECREASE) ||
255 glKemLine.getTypeCode().equalsIgnoreCase(EndowConstants.DocumentTypeNames.ENDOWMENT_CASH_TRANSFER) ||
256 glKemLine.getTypeCode().equalsIgnoreCase(EndowConstants.DocumentTypeNames.GENERAL_LEDGER_TO_ENDOWMENT_TRANSFER) ||
257 glKemLine.getTypeCode().equalsIgnoreCase(EndowConstants.DocumentTypeNames.ENDOWMENT_TO_GENERAL_LEDGER_TRANSFER)) ) {
258 glKemLine.setObjectCode(gLLinkDao.getObjectCode(glKemLine.getChartCode(), archiveTransactions.getString(EndowPropertyConstants.ColumnNames.GlInterfaceBatchProcessLine.TRANSACTION_ARCHIVE_TRAN_ETRAN_CD)));
259 }
260 else {
261 //user transaction archive security etran code..
262 glKemLine.setObjectCode(gLLinkDao.getObjectCode(glKemLine.getChartCode(), eTransactionCode));
263 }
264
265 if (EndowConstants.TransactionSubTypeCode.NON_CASH.equalsIgnoreCase(glKemLine.getSubTypeCode())) {
266 glKemLine.setNonCashOffsetObjectCode(gLLinkDao.getObjectCode(glKemLine.getChartCode(), archiveTransactions.getString(EndowPropertyConstants.ColumnNames.GlInterfaceBatchProcessLine.TRANSACTION_ARCHIVE_TRAN_ETRAN_CD)));
267 }
268 else {
269 glKemLine.setNonCashOffsetObjectCode(null);
270 }
271
272 kemArchiveTransactions.add(glKemLine);
273 }
274
275 LOG.info("buildTransactionActivities() exited.");
276 }
277
278 /**
279 * method to combine the kem transactions based on chart, account and object code
280 * into single data records
281 */
282 protected void buildCombinedTransactionActivities(Collection<GlInterfaceBatchProcessKemLine> kemCombinedArchiveTransactions, Collection<GlInterfaceBatchProcessKemLine> kemArchiveTransactions, boolean cashType) {
283 LOG.info("buildCombinedTransactionActivities() started");
284
285 GLCombinedTransactionArchive gLCombinedTransactionArchive = new GLCombinedTransactionArchive();
286
287 for (GlInterfaceBatchProcessKemLine kemArchiveTransaction : kemArchiveTransactions) {
288
289 if (gLCombinedTransactionArchive.getChartCode() == null && gLCombinedTransactionArchive.getAccountNumber() == null && gLCombinedTransactionArchive.getObjectCode() == null) {
290 gLCombinedTransactionArchive.copyChartAndAccountNumberAndObjectCodeValues(kemArchiveTransaction);
291 }
292 if (gLCombinedTransactionArchive.getChartCode().compareToIgnoreCase(kemArchiveTransaction.getChartCode()) == 0
293 && gLCombinedTransactionArchive.getAccountNumber().compareToIgnoreCase(kemArchiveTransaction.getAccountNumber()) == 0
294 && gLCombinedTransactionArchive.getObjectCode().compareToIgnoreCase(kemArchiveTransaction.getObjectCode()) == 0) {
295 gLCombinedTransactionArchive.incrementCombinedEntryCount();
296 gLCombinedTransactionArchive.copyKemArchiveTransactionValues(kemArchiveTransaction, cashType);
297 }
298 else {
299 GlInterfaceBatchProcessKemLine glKemLine = gLCombinedTransactionArchive.copyValuesToCombinedTransactionArchive(cashType);
300
301 kemCombinedArchiveTransactions.add(glKemLine);
302 gLCombinedTransactionArchive.initializeAmounts();
303 gLCombinedTransactionArchive.copyChartAndAccountNumberAndObjectCodeValues(kemArchiveTransaction);
304 gLCombinedTransactionArchive.copyKemArchiveTransactionValues(kemArchiveTransaction, cashType);
305 }
306 }
307
308 //write the last record for the document type....
309 if (kemArchiveTransactions.size()> 0) {
310 GlInterfaceBatchProcessKemLine glKemLine = gLCombinedTransactionArchive.copyValuesToCombinedTransactionArchive(cashType);
311 kemCombinedArchiveTransactions.add(glKemLine);
312 }
313
314 LOG.info("buildCombinedTransactionActivities() exited.");
315 }
316
317 /**
318 * gets attribute kemidGeneralLedgerAccountDao
319 * @return kemidGeneralLedgerAccountDao
320 */
321 protected KemidGeneralLedgerAccountDao getKemidGeneralLedgerAccountDao() {
322 return kemidGeneralLedgerAccountDao;
323 }
324
325 /**
326 * sets attribute kemidGeneralLedgerAccountDao
327 */
328 public void setKemidGeneralLedgerAccountDao(KemidGeneralLedgerAccountDao kemidGeneralLedgerAccountDao) {
329 this.kemidGeneralLedgerAccountDao = kemidGeneralLedgerAccountDao;
330 }
331
332 /**
333 * gets attribute gLLinkDao
334 * @return gLLinkDao
335 */
336 protected GLLinkDao getgLLinkDao() {
337 return gLLinkDao;
338 }
339
340 /**
341 * sets attribute gLLinkDao
342 */
343 public void setgLLinkDao(GLLinkDao gLLinkDao) {
344 this.gLLinkDao = gLLinkDao;
345 }
346
347 /**
348 * gets attribute transactionArchiveSecurityDao
349 * @return transactionArchiveSecurityDao
350 */
351 public TransactionArchiveSecurityDao getTransactionArchiveSecurityDao() {
352 return transactionArchiveSecurityDao;
353 }
354
355 /**
356 * sets attribute transactionArchiveSecurityDao
357 */
358 public void setTransactionArchiveSecurityDao(TransactionArchiveSecurityDao transactionArchiveSecurityDao) {
359 this.transactionArchiveSecurityDao = transactionArchiveSecurityDao;
360 }
361 }