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.dataaccess.impl;
017
018 import java.util.ArrayList;
019 import java.util.Arrays;
020 import java.util.Collection;
021 import java.util.HashMap;
022 import java.util.Iterator;
023 import java.util.List;
024 import java.util.Map;
025 import java.util.TreeMap;
026
027 import org.apache.commons.lang.StringUtils;
028 import org.apache.ojb.broker.query.Criteria;
029 import org.apache.ojb.broker.query.Query;
030 import org.apache.ojb.broker.query.QueryByCriteria;
031 import org.apache.ojb.broker.query.QueryFactory;
032 import org.apache.ojb.broker.query.ReportQueryByCriteria;
033 import org.kuali.kfs.coa.businessobject.Account;
034 import org.kuali.kfs.coa.businessobject.OrganizationReversion;
035 import org.kuali.kfs.coa.service.BalanceTypeService;
036 import org.kuali.kfs.coa.service.ObjectTypeService;
037 import org.kuali.kfs.coa.service.SubFundGroupService;
038 import org.kuali.kfs.gl.GeneralLedgerConstants;
039 import org.kuali.kfs.gl.OJBUtility;
040 import org.kuali.kfs.gl.batch.BalanceForwardStep;
041 import org.kuali.kfs.gl.batch.service.FilteringBalanceIterator;
042 import org.kuali.kfs.gl.businessobject.Balance;
043 import org.kuali.kfs.gl.businessobject.CashBalance;
044 import org.kuali.kfs.gl.businessobject.SufficientFundBalances;
045 import org.kuali.kfs.gl.businessobject.Transaction;
046 import org.kuali.kfs.gl.dataaccess.BalanceDao;
047 import org.kuali.kfs.gl.dataaccess.LedgerBalanceBalancingDao;
048 import org.kuali.kfs.sys.KFSConstants;
049 import org.kuali.kfs.sys.KFSPropertyConstants;
050 import org.kuali.kfs.sys.businessobject.SystemOptions;
051 import org.kuali.kfs.sys.context.SpringContext;
052 import org.kuali.kfs.sys.service.OptionsService;
053 import org.kuali.rice.kns.dao.impl.PlatformAwareDaoBaseOjb;
054 import org.kuali.rice.kns.service.ParameterEvaluator;
055 import org.kuali.rice.kns.service.ParameterService;
056 import org.kuali.rice.kns.util.KualiDecimal;
057
058 /**
059 * An OJB implementation of BalanceDao
060 */
061 public class BalanceDaoOjb extends PlatformAwareDaoBaseOjb implements BalanceDao, LedgerBalanceBalancingDao {
062 private static org.apache.log4j.Logger LOG = org.apache.log4j.Logger.getLogger(BalanceDaoOjb.class);
063 private ParameterService parameterService;
064 private OptionsService optionsService;
065 private BalanceTypeService balanceTypService;
066
067 /**
068 * Does a ReportQuery to summarize GL balance data
069 *
070 * @param universityFiscalYear the fiscal year of balances to search for
071 * @param balanceTypeCodes a list of balance type codes of balances to search for
072 * @return iterator of reported on java.lang.Object arrays with the report data
073 * @see org.kuali.kfs.gl.dataaccess.BalanceDao#getGlSummary(int, java.util.List)
074 */
075 public Iterator<Object[]> getGlSummary(int universityFiscalYear, List<String> balanceTypeCodes) {
076 LOG.debug("getGlSummary() started");
077
078 Criteria c = new Criteria();
079 c.addEqualTo(KFSPropertyConstants.UNIVERSITY_FISCAL_YEAR, universityFiscalYear);
080 c.addIn(KFSPropertyConstants.BALANCE_TYPE_CODE, balanceTypeCodes);
081
082 String[] attributes = new String[] { "account.subFundGroup.fundGroupCode", "sum(accountLineAnnualBalanceAmount)", "sum(beginningBalanceLineAmount)", "sum(contractsGrantsBeginningBalanceAmount)", "sum(month1Amount)", "sum(month2Amount)", "sum(month3Amount)", "sum(month4Amount)", "sum(month5Amount)", "sum(month6Amount)", "sum(month7Amount)", "sum(month8Amount)", "sum(month9Amount)", "sum(month10Amount)", "sum(month11Amount)", "sum(month12Amount)", "sum(month13Amount)" };
083
084 String[] groupby = new String[] { "account.subFundGroup.fundGroupCode" };
085
086 ReportQueryByCriteria query = new ReportQueryByCriteria(Balance.class, c);
087
088 query.setAttributes(attributes);
089 query.addGroupBy(groupby);
090 query.addOrderByAscending("account.subFundGroup.fundGroupCode");
091
092 return getPersistenceBrokerTemplate().getReportQueryIteratorByQuery(query);
093 }
094
095 /**
096 * Queries the database for all the balances for a given fiscal year
097 *
098 * @param year the university fiscal year of balances to return
099 * @return an iterator over all balances for a given fiscal year
100 * @see org.kuali.kfs.gl.dataaccess.BalanceDao#findBalancesForFiscalYear(java.lang.Integer)
101 */
102 public Iterator<Balance> findBalancesForFiscalYear(Integer year) {
103 LOG.debug("findBalancesForFiscalYear() started");
104
105 Criteria c = new Criteria();
106 c.addEqualTo(KFSPropertyConstants.UNIVERSITY_FISCAL_YEAR, year);
107
108 QueryByCriteria query = QueryFactory.newQuery(Balance.class, c);
109 query.addOrderByAscending(KFSPropertyConstants.CHART_OF_ACCOUNTS_CODE);
110 query.addOrderByAscending(KFSPropertyConstants.ACCOUNT_NUMBER);
111 query.addOrderByAscending(KFSPropertyConstants.SUB_ACCOUNT_NUMBER);
112 query.addOrderByAscending(KFSPropertyConstants.OBJECT_CODE);
113 query.addOrderByAscending(KFSPropertyConstants.SUB_OBJECT_CODE);
114 query.addOrderByAscending(KFSPropertyConstants.BALANCE_TYPE_CODE);
115 query.addOrderByAscending(KFSPropertyConstants.OBJECT_TYPE_CODE);
116
117 return getPersistenceBrokerTemplate().getIteratorByQuery(query);
118 }
119
120 /**
121 * Saves a balance
122 * @param b a balance to save
123 * @see org.kuali.kfs.gl.dataaccess.BalanceDao#save(org.kuali.kfs.gl.businessobject.Balance)
124 */
125 public void save(Balance b) {
126 LOG.debug("save() started");
127
128 getPersistenceBrokerTemplate().store(b);
129 }
130
131 /**
132 * Using values from the transaction as keys, lookup the balance the transaction would affect were it posted
133 *
134 * @t a transaction to look up the related balance for
135 * @return a Balance that the given transaction would affect
136 * @see org.kuali.kfs.gl.dataaccess.BalanceDao#getBalanceByTransaction(org.kuali.kfs.gl.businessobject.Transaction)
137 */
138 public Balance getBalanceByTransaction(Transaction t) {
139 LOG.debug("getBalanceByTransaction() started");
140
141 Criteria crit = new Criteria();
142 crit.addEqualTo(KFSPropertyConstants.UNIVERSITY_FISCAL_YEAR, t.getUniversityFiscalYear());
143 crit.addEqualTo(KFSPropertyConstants.CHART_OF_ACCOUNTS_CODE, t.getChartOfAccountsCode());
144 crit.addEqualTo(KFSPropertyConstants.ACCOUNT_NUMBER, t.getAccountNumber());
145 crit.addEqualTo(KFSPropertyConstants.SUB_ACCOUNT_NUMBER, t.getSubAccountNumber());
146 crit.addEqualTo(KFSPropertyConstants.OBJECT_CODE, t.getFinancialObjectCode());
147 crit.addEqualTo(KFSPropertyConstants.SUB_OBJECT_CODE, t.getFinancialSubObjectCode());
148 crit.addEqualTo(KFSPropertyConstants.BALANCE_TYPE_CODE, t.getFinancialBalanceTypeCode());
149 crit.addEqualTo(KFSPropertyConstants.OBJECT_TYPE_CODE, t.getFinancialObjectTypeCode());
150
151 QueryByCriteria qbc = QueryFactory.newQuery(Balance.class, crit);
152 return (Balance) getPersistenceBrokerTemplate().getObjectByQuery(qbc);
153 }
154
155 /**
156 * This method adds to the given criteria if the given collection is non-empty. It uses an EQUALS if there is exactly one
157 * element in the collection; otherwise, its uses an IN
158 *
159 * @param criteria - the criteria that might have a criterion appended
160 * @param name - name of the attribute
161 * @param collection - the collection to inspect
162 */
163 protected void criteriaBuilder(Criteria criteria, String name, Collection collection) {
164 criteriaBuilderHelper(criteria, name, collection, false);
165 }
166
167 /**
168 * Similar to criteriaBuilder, this adds a negative criterion (NOT EQUALS, NOT IN)
169 *
170 * @param criteria - the criteria that might have a criterion appended
171 * @param name - name of the attribute
172 * @param collection - the collection to inspect
173 */
174 protected void negatedCriteriaBuilder(Criteria criteria, String name, Collection collection) {
175 criteriaBuilderHelper(criteria, name, collection, true);
176 }
177
178
179 /**
180 * This method provides the implementation for the conveniences methods criteriaBuilder & negatedCriteriaBuilder
181 *
182 * @param criteria - the criteria that might have a criterion appended
183 * @param name - name of the attribute
184 * @param collection - the collection to inspect
185 * @param negate - the criterion will be negated (NOT EQUALS, NOT IN) when this is true
186 */
187 protected void criteriaBuilderHelper(Criteria criteria, String name, Collection collection, boolean negate) {
188 if (collection != null) {
189 int size = collection.size();
190 if (size == 1) {
191 if (negate) {
192 criteria.addNotEqualTo(name, collection.iterator().next());
193 }
194 else {
195 criteria.addEqualTo(name, collection.iterator().next());
196 }
197 }
198 if (size > 1) {
199 if (negate) {
200 criteria.addNotIn(name, collection);
201 }
202 else {
203 criteria.addIn(name, collection);
204
205 }
206 }
207 }
208
209 }
210
211 /**
212 * Build a query based on all the parameters, and return an Iterator of all Balances from the database that qualify
213 *
214 * @param account the account of balances to find
215 * @param fiscalYear the fiscal year of balances to find
216 * @param includedObjectCodes a Collection of object codes found balances should have one of
217 * @param excludedObjectCodes a Collection of object codes found balances should not have one of
218 * @param objectTypeCodes a Collection of object type codes found balances should have one of
219 * @param balanceTypeCodes a Collection of balance type codes found balances should have one of
220 * @return an Iterator of Balances
221 * @see org.kuali.kfs.gl.dataaccess.BalanceDao#findBalances(org.kuali.kfs.coa.businessobject.Account, java.lang.Integer, java.util.Collection, java.util.Collection, java.util.Collection, java.util.Collection)
222 */
223 public Iterator<Balance> findBalances(Account account, Integer fiscalYear, Collection includedObjectCodes, Collection excludedObjectCodes, Collection objectTypeCodes, Collection balanceTypeCodes) {
224 LOG.debug("findBalances() started");
225
226 Criteria criteria = new Criteria();
227
228 criteria.addEqualTo(KFSPropertyConstants.ACCOUNT_NUMBER, account.getAccountNumber());
229 criteria.addEqualTo(KFSPropertyConstants.CHART_OF_ACCOUNTS_CODE, account.getChartOfAccountsCode());
230
231 criteria.addEqualTo(KFSPropertyConstants.UNIVERSITY_FISCAL_YEAR, fiscalYear);
232
233 criteriaBuilder(criteria, GeneralLedgerConstants.ColumnNames.OBJECT_TYPE_CODE, objectTypeCodes);
234 criteriaBuilder(criteria, GeneralLedgerConstants.ColumnNames.BALANCE_TYPE_CODE, balanceTypeCodes);
235 criteriaBuilder(criteria, GeneralLedgerConstants.ColumnNames.OBJECT_CODE, includedObjectCodes);
236 negatedCriteriaBuilder(criteria, GeneralLedgerConstants.ColumnNames.OBJECT_CODE, excludedObjectCodes);
237
238 ReportQueryByCriteria query = new ReportQueryByCriteria(Balance.class, criteria);
239
240 // returns an iterator of all matching balances
241 Iterator balances = getPersistenceBrokerTemplate().getIteratorByQuery(query);
242 return balances;
243 }
244
245 /**
246 * Using the given fieldValues as keys, return all cash balance records
247 *
248 * @param fieldValues the input fields and values
249 * @param isConsolidated consolidation option is applied or not
250 * @return the records of cash balance entries
251 * @see org.kuali.kfs.gl.dataaccess.BalanceDao#findCashBalance(java.util.Map, boolean)
252 */
253 public Iterator<Balance> findCashBalance(Map fieldValues, boolean isConsolidated) {
254 LOG.debug("findCashBalance() started");
255
256 Query query = this.getCashBalanceQuery(fieldValues, isConsolidated);
257 OJBUtility.limitResultSize(query);
258 return getPersistenceBrokerTemplate().getReportQueryIteratorByQuery(query);
259 }
260
261 /**
262 * Get the number of detailed cash balance records that would be returned, were we to do a query based on the given fieldValues
263 *
264 * @param fieldValues the input fields and values
265 * @param isConsolidated consolidation option is applied or not
266 * @return the size collection of cash balance entry groups
267 * @see org.kuali.kfs.gl.dataaccess.BalanceDao#getCashBalanceRecordCount(java.util.Map, boolean)
268 */
269 public Integer getDetailedCashBalanceRecordCount(Map fieldValues) {
270 LOG.debug("getDetailedCashBalanceRecordCount() started");
271
272 Query query = this.getCashBalanceQuery(fieldValues, false);
273 return getPersistenceBrokerTemplate().getCount(query);
274 }
275
276 /**
277 * Given a map of keys, return all of the report data about qualifying cash balances
278 *
279 * @param fieldValues the input fields and values
280 * @return the size collection of cash balance entry groups
281 * @see org.kuali.kfs.gl.dataaccess.BalanceDao#getCashBalanceRecordSize(java.util.Map, boolean)
282 */
283 public Iterator getConsolidatedCashBalanceRecordCount(Map fieldValues) {
284 LOG.debug("getCashBalanceRecordCount() started");
285
286 ReportQueryByCriteria query = this.getCashBalanceCountQuery(fieldValues);
287 return getPersistenceBrokerTemplate().getReportQueryIteratorByQuery(query);
288 }
289
290 /**
291 * Given a map of values, build a query out of those and find all the balances that qualify
292 *
293 * @param fieldValues a Map of fieldValues to use as keys in the query
294 * @param isConsolidated should the results be consolidated?
295 * @return an Iterator of Balances
296 * @see org.kuali.kfs.gl.dataaccess.BalanceDao#findBalance(java.util.Map, boolean)
297 */
298 public Iterator<Balance> findBalance(Map fieldValues, boolean isConsolidated) {
299 LOG.debug("findBalance() started");
300
301 Query query = this.getBalanceQuery(fieldValues, isConsolidated);
302 OJBUtility.limitResultSize(query);
303
304 if (isConsolidated) {
305 return getPersistenceBrokerTemplate().getReportQueryIteratorByQuery(query);
306 }
307 return getPersistenceBrokerTemplate().getIteratorByQuery(query);
308 }
309
310 /**
311 * Given a Map of keys to use as a query, if we performed that query as a consolidated query...
312 * how many records would we get back?
313 *
314 * @param fieldValues a Map of values to use as keys to build the query
315 * @return an Iterator of counts...
316 * @see org.kuali.kfs.gl.dataaccess.BalanceDao#getConsolidatedBalanceRecordCount(java.util.Map)
317 */
318 public Iterator getConsolidatedBalanceRecordCount(Map fieldValues) {
319 LOG.debug("getBalanceRecordCount() started");
320
321 ReportQueryByCriteria query = this.getBalanceCountQuery(fieldValues);
322 return getPersistenceBrokerTemplate().getReportQueryIteratorByQuery(query);
323 }
324
325 /**
326 * Builds a query for cash balances, based on the given field values
327 *
328 * @param fieldValues a map of keys to use when building the query
329 * @return an OJB ReportQuery to use as the query
330 */
331 protected ReportQueryByCriteria getCashBalanceCountQuery(Map fieldValues) {
332 Criteria criteria = buildCriteriaFromMap(fieldValues, new CashBalance());
333 criteria.addEqualTo(KFSPropertyConstants.BALANCE_TYPE_CODE, KFSConstants.BALANCE_TYPE_ACTUAL);
334 criteria.addEqualToField("chart.financialCashObjectCode", KFSPropertyConstants.OBJECT_CODE);
335
336 ReportQueryByCriteria query = QueryFactory.newReportQuery(CashBalance.class, criteria);
337
338 List groupByList = buildGroupByList();
339 groupByList.remove(KFSPropertyConstants.SUB_ACCOUNT_NUMBER);
340 groupByList.remove(KFSPropertyConstants.SUB_OBJECT_CODE);
341 groupByList.remove(KFSPropertyConstants.OBJECT_TYPE_CODE);
342
343 // add the group criteria into the selection statement
344 String[] groupBy = (String[]) groupByList.toArray(new String[groupByList.size()]);
345 query.addGroupBy(groupBy);
346
347 // set the selection attributes
348 query.setAttributes(new String[] { "count(*)" });
349
350 return query;
351 }
352
353 /**
354 * build the query for cash balance search
355 *
356 * @param fieldValues Map of keys to use for the query
357 * @param isConsolidated should the results be consolidated?
358 * @return the OJB query to perform
359 */
360 protected Query getCashBalanceQuery(Map fieldValues, boolean isConsolidated) {
361 Criteria criteria = buildCriteriaFromMap(fieldValues, new CashBalance());
362 criteria.addEqualTo(KFSPropertyConstants.BALANCE_TYPE_CODE, KFSConstants.BALANCE_TYPE_ACTUAL);
363 criteria.addEqualToField("chart.financialCashObjectCode", KFSPropertyConstants.OBJECT_CODE);
364
365 ReportQueryByCriteria query = QueryFactory.newReportQuery(CashBalance.class, criteria);
366 List attributeList = buildAttributeList(false);
367 List groupByList = buildGroupByList();
368
369 // if consolidated, then ignore the following fields
370 if (isConsolidated) {
371 attributeList.remove(KFSPropertyConstants.SUB_ACCOUNT_NUMBER);
372 groupByList.remove(KFSPropertyConstants.SUB_ACCOUNT_NUMBER);
373 attributeList.remove(KFSPropertyConstants.SUB_OBJECT_CODE);
374 groupByList.remove(KFSPropertyConstants.SUB_OBJECT_CODE);
375 attributeList.remove(KFSPropertyConstants.OBJECT_TYPE_CODE);
376 groupByList.remove(KFSPropertyConstants.OBJECT_TYPE_CODE);
377 }
378
379 // add the group criteria into the selection statement
380 String[] groupBy = (String[]) groupByList.toArray(new String[groupByList.size()]);
381 query.addGroupBy(groupBy);
382
383 // set the selection attributes
384 String[] attributes = (String[]) attributeList.toArray(new String[attributeList.size()]);
385 query.setAttributes(attributes);
386
387 return query;
388 }
389
390 /**
391 * build the query for balance search
392 *
393 * @param fieldValues Map of keys to use for the query
394 * @param isConsolidated should the results be consolidated?
395 * @return an OJB query to perform
396 */
397 protected Query getBalanceQuery(Map fieldValues, boolean isConsolidated) {
398 LOG.debug("getBalanceQuery(Map, boolean) started");
399
400 Criteria criteria = buildCriteriaFromMap(fieldValues, new Balance());
401 ReportQueryByCriteria query = QueryFactory.newReportQuery(Balance.class, criteria);
402
403 // if consolidated, then ignore subaccount number and balance type code
404 if (isConsolidated) {
405 List attributeList = buildAttributeList(true);
406 List groupByList = buildGroupByList();
407
408 // ignore subaccount number, sub object code and object type code
409 attributeList.remove(KFSPropertyConstants.SUB_ACCOUNT_NUMBER);
410 groupByList.remove(KFSPropertyConstants.SUB_ACCOUNT_NUMBER);
411 attributeList.remove(KFSPropertyConstants.SUB_OBJECT_CODE);
412 groupByList.remove(KFSPropertyConstants.SUB_OBJECT_CODE);
413 attributeList.remove(KFSPropertyConstants.OBJECT_TYPE_CODE);
414 groupByList.remove(KFSPropertyConstants.OBJECT_TYPE_CODE);
415
416 // set the selection attributes
417 String[] attributes = (String[]) attributeList.toArray(new String[attributeList.size()]);
418 query.setAttributes(attributes);
419
420 // add the group criteria into the selection statement
421 String[] groupBy = (String[]) groupByList.toArray(new String[groupByList.size()]);
422 query.addGroupBy(groupBy);
423 }
424
425 return query;
426 }
427
428 /**
429 * build the query for balance search
430 *
431 * @param fieldValues Map of keys to use for the query
432 * @return an OJB ReportQuery to perform
433 */
434 protected ReportQueryByCriteria getBalanceCountQuery(Map fieldValues) {
435 Criteria criteria = buildCriteriaFromMap(fieldValues, new Balance());
436 ReportQueryByCriteria query = QueryFactory.newReportQuery(Balance.class, criteria);
437
438 // set the selection attributes
439 query.setAttributes(new String[] { "count(*)" });
440
441 List groupByList = buildGroupByList();
442 groupByList.remove(KFSPropertyConstants.SUB_ACCOUNT_NUMBER);
443 groupByList.remove(KFSPropertyConstants.SUB_OBJECT_CODE);
444 groupByList.remove(KFSPropertyConstants.OBJECT_TYPE_CODE);
445
446 // add the group criteria into the selection statement
447 String[] groupBy = (String[]) groupByList.toArray(new String[groupByList.size()]);
448 query.addGroupBy(groupBy);
449 return query;
450 }
451
452 /**
453 * This method builds the query criteria based on the input field map
454 *
455 * @param fieldValues Map of keys to use for the query
456 * @param balance this really usen't used in the method
457 * @return a query criteria
458 */
459 protected Criteria buildCriteriaFromMap(Map fieldValues, Balance balance) {
460 Map localFieldValues = new HashMap();
461 localFieldValues.putAll(fieldValues);
462
463 Criteria criteria = new Criteria();
464
465 // handle encumbrance balance type
466 String propertyName = KFSPropertyConstants.BALANCE_TYPE_CODE;
467 if (localFieldValues.containsKey(propertyName)) {
468 String propertyValue = (String) localFieldValues.get(propertyName);
469 if (KFSConstants.AGGREGATE_ENCUMBRANCE_BALANCE_TYPE_CODE.equals(propertyValue)) {
470 localFieldValues.remove(KFSPropertyConstants.BALANCE_TYPE_CODE);
471
472 // the year should be part of the results for both the cash balance and regular balance lookupables
473 String universityFiscalYearStr = (String) localFieldValues.get(KFSPropertyConstants.UNIVERSITY_FISCAL_YEAR);
474 Integer universityFiscalYear = new Integer(universityFiscalYearStr);
475
476 criteria.addIn(KFSPropertyConstants.BALANCE_TYPE_CODE, balanceTypService.getEncumbranceBalanceTypes(universityFiscalYear));
477 }
478 }
479
480 criteria.addAndCriteria(OJBUtility.buildCriteriaFromMap(localFieldValues, balance));
481 return criteria;
482 }
483
484 /**
485 * This method builds the atrribute list used by balance searching
486 *
487 * @param isExtended should we add the attributes to sum each of the monthly totals?
488 * @return List an attribute list
489 */
490 protected List<String> buildAttributeList(boolean isExtended) {
491 List attributeList = this.buildGroupByList();
492
493 attributeList.add("sum(accountLineAnnualBalanceAmount)");
494 attributeList.add("sum(beginningBalanceLineAmount)");
495 attributeList.add("sum(contractsGrantsBeginningBalanceAmount)");
496
497 // add the entended elements into the list
498 if (isExtended) {
499 attributeList.add("sum(month1Amount)");
500 attributeList.add("sum(month2Amount)");
501 attributeList.add("sum(month3Amount)");
502 attributeList.add("sum(month4Amount)");
503 attributeList.add("sum(month5Amount)");
504 attributeList.add("sum(month6Amount)");
505 attributeList.add("sum(month7Amount)");
506 attributeList.add("sum(month8Amount)");
507 attributeList.add("sum(month9Amount)");
508 attributeList.add("sum(month10Amount)");
509 attributeList.add("sum(month11Amount)");
510 attributeList.add("sum(month12Amount)");
511 attributeList.add("sum(month13Amount)");
512 }
513 return attributeList;
514 }
515
516 /**
517 * This method builds group by attribute list used by balance searching
518 *
519 * @return List an group by attribute list
520 */
521 protected List<String> buildGroupByList() {
522 List attributeList = new ArrayList();
523
524 attributeList.add(KFSPropertyConstants.UNIVERSITY_FISCAL_YEAR);
525 attributeList.add(KFSPropertyConstants.CHART_OF_ACCOUNTS_CODE);
526 attributeList.add(KFSPropertyConstants.ACCOUNT_NUMBER);
527 attributeList.add(KFSPropertyConstants.SUB_ACCOUNT_NUMBER);
528 attributeList.add(KFSPropertyConstants.BALANCE_TYPE_CODE);
529 attributeList.add(KFSPropertyConstants.OBJECT_CODE);
530 attributeList.add(KFSPropertyConstants.SUB_OBJECT_CODE);
531 attributeList.add(KFSPropertyConstants.OBJECT_TYPE_CODE);
532
533 return attributeList;
534 }
535
536 /**
537 * Whoa! This method is seemingly not called in the code base right now, and you know what? You shouldn't call it
538 * First of all, we're not even sending in all the primary keys for Balance, and second of all, we're
539 * returning a SufficientFundsBalance, which we cast to a Balance, which is *always* going to throw a
540 * ClassCastException. Don't call this method. Just...just step away.
541 *
542 * @see org.kuali.kfs.gl.dataaccess.BalanceDao#getBalanceByPrimaryId(java.lang.Integer, java.lang.String, java.lang.String)
543 */
544 public Balance getBalanceByPrimaryId(Integer universityFiscalYear, String chartOfAccountsCode, String accountNumber) {
545 // TODO just kill this
546 LOG.debug("getBalanceByPrimaryId() started");
547
548 Criteria crit = new Criteria();
549 crit.addEqualTo(KFSPropertyConstants.UNIVERSITY_FISCAL_YEAR, universityFiscalYear);
550 crit.addEqualTo(KFSPropertyConstants.CHART_OF_ACCOUNTS_CODE, chartOfAccountsCode);
551 crit.addEqualTo(KFSPropertyConstants.ACCOUNT_NUMBER, accountNumber);
552
553 QueryByCriteria qbc = QueryFactory.newQuery(SufficientFundBalances.class, crit);
554 return (Balance) getPersistenceBrokerTemplate().getObjectByQuery(qbc);
555 }
556
557
558 /**
559 * Since SubAccountNumber, SubObjectCode, and ObjectType are all part of the primary key of Balance, you're guaranteed to get one of those
560 * records when you call this method. Let's hope the right one.
561 *
562 * @param universityFiscalYear the fiscal year of the CB balance to return
563 * @param chartOfAccountsCode the chart of the accounts code of the CB balanes to return
564 * @param accountNumber the account number of the CB balance to return
565 * @param objectCode the object code of the CB balance to return
566 * @return the CB Balance record
567 * @see org.kuali.kfs.gl.dataaccess.BalanceDao#getCurrentBudgetForObjectCode(java.lang.Integer, java.lang.String, java.lang.String,
568 * java.lang.String)
569 */
570 public Balance getCurrentBudgetForObjectCode(Integer universityFiscalYear, String chartOfAccountsCode, String accountNumber, String objectCode) {
571 LOG.debug("getCurrentBudgetForObjectCode() started");
572
573 Criteria crit = new Criteria();
574 crit.addEqualTo(KFSPropertyConstants.UNIVERSITY_FISCAL_YEAR, universityFiscalYear);
575 crit.addEqualTo(KFSPropertyConstants.CHART_OF_ACCOUNTS_CODE, chartOfAccountsCode);
576 crit.addEqualTo(KFSPropertyConstants.ACCOUNT_NUMBER, accountNumber);
577 crit.addEqualTo(KFSPropertyConstants.OBJECT_CODE, objectCode);
578 crit.addEqualTo(KFSPropertyConstants.BALANCE_TYPE_CODE, KFSConstants.BALANCE_TYPE_CURRENT_BUDGET);
579
580 QueryByCriteria qbc = QueryFactory.newQuery(Balance.class, crit);
581 return (Balance) getPersistenceBrokerTemplate().getObjectByQuery(qbc);
582 }
583
584 /**
585 * Find all matching account balances.
586 *
587 * @param universityFiscalYear the university fiscal year of balances to return
588 * @param chartOfAccountsCode the chart of accounts code of balances to return
589 * @param accountNumber the account number of balances to return
590 * @return balances sorted by object code
591 */
592 public Iterator<Balance> findAccountBalances(Integer universityFiscalYear, String chartOfAccountsCode, String accountNumber) {
593 LOG.debug("findAccountBalances() started");
594 return this.findAccountBalances(universityFiscalYear, chartOfAccountsCode, accountNumber, KFSConstants.SF_TYPE_OBJECT);
595 }
596
597 /**
598 * Find all matching account balances. The Sufficient funds code is used to determine the sort of the results.
599 *
600 * @param universityFiscalYear the university fiscal year of balances to return
601 * @param chartOfAccountsCode the chart of accounts code of balances to return
602 * @param accountNumber the account number of balances to return
603 * @param sfCode the sufficient funds code, used to sort on
604 * @return an Iterator of balances
605 */
606 public Iterator<Balance> findAccountBalances(Integer universityFiscalYear, String chartOfAccountsCode, String accountNumber, String sfCode) {
607 LOG.debug("findAccountBalances() started");
608
609 Criteria crit = new Criteria();
610 crit.addEqualTo(KFSPropertyConstants.UNIVERSITY_FISCAL_YEAR, universityFiscalYear);
611 crit.addEqualTo(KFSPropertyConstants.CHART_OF_ACCOUNTS_CODE, chartOfAccountsCode);
612 crit.addEqualTo(KFSPropertyConstants.ACCOUNT_NUMBER, accountNumber);
613
614 QueryByCriteria qbc = QueryFactory.newQuery(Balance.class, crit);
615 if (KFSConstants.SF_TYPE_OBJECT.equals(sfCode)) {
616 qbc.addOrderByAscending(KFSPropertyConstants.OBJECT_CODE);
617 }
618 else if (KFSConstants.SF_TYPE_LEVEL.equals(sfCode)) {
619 qbc.addOrderByAscending(GeneralLedgerConstants.BalanceInquiryDrillDowns.OBJECT_LEVEL_CODE);
620 }
621 else if (KFSConstants.SF_TYPE_CONSOLIDATION.equals(sfCode)) {
622 qbc.addOrderByAscending(GeneralLedgerConstants.BalanceInquiryDrillDowns.CONSOLIDATION_OBJECT_CODE);
623 }
624 return getPersistenceBrokerTemplate().getIteratorByQuery(qbc);
625 }
626
627 /**
628 * Purge the sufficient funds balance table by year/chart
629 *
630 * @param chart the chart of balances to purge
631 * @param year the university fiscal year of balances to purge
632 */
633 public void purgeYearByChart(String chartOfAccountsCode, int year) {
634 LOG.debug("purgeYearByChart() started");
635
636 Criteria criteria = new Criteria();
637 criteria.addEqualTo(KFSPropertyConstants.CHART_OF_ACCOUNTS_CODE, chartOfAccountsCode);
638 criteria.addLessThan(KFSPropertyConstants.UNIVERSITY_FISCAL_YEAR, new Integer(year));
639
640 getPersistenceBrokerTemplate().deleteByQuery(new QueryByCriteria(Balance.class, criteria));
641
642 // This is required because if any deleted account balances are in the cache, deleteByQuery doesn't
643 // remove them from the cache so a future select will retrieve these deleted account balances from
644 // the cache and return them. Clearing the cache forces OJB to go to the database again.
645 getPersistenceBrokerTemplate().clearCache();
646 }
647
648 /**
649 * Returns the count of balances for a given fiscal year; this method is used for year end job reporting
650 * @param year the university fiscal year to count balances for
651 * @return an int with the count of balances for that fiscal year
652 * @see org.kuali.kfs.gl.dataaccess.BalanceDao#countBalancesForFiscalYear(java.lang.Integer)
653 */
654 public int countBalancesForFiscalYear(Integer year) {
655 LOG.debug("countBalancesForFiscalYear() started");
656
657 Criteria c = new Criteria();
658 c.addEqualTo(KFSPropertyConstants.UNIVERSITY_FISCAL_YEAR, year);
659 QueryByCriteria query = QueryFactory.newQuery(Balance.class, c);
660
661 return getPersistenceBrokerTemplate().getCount(query);
662 }
663
664 /**
665 * Finds all of the balances for the fiscal year that should be processed by nominal activity closing
666 *
667 * @param year the university fiscal year of balances to find
668 * @return an Iterator of Balances to process
669 * @see org.kuali.kfs.gl.dataaccess.BalanceDao#findNominalActivityBalancesForFiscalYear(java.lang.Integer)
670 */
671 public Iterator<Balance> findNominalActivityBalancesForFiscalYear(Integer year) {
672 LOG.debug("findNominalActivityBalancesForFiscalYear() started");
673
674 SystemOptions currentYearOptions = optionsService.getCurrentYearOptions();
675
676 // generate List of nominal activity object type codes
677 ObjectTypeService objectTypeService = SpringContext.getBean(ObjectTypeService.class);
678
679 Criteria c = new Criteria();
680 c.addEqualTo(KFSPropertyConstants.UNIVERSITY_FISCAL_YEAR, year);
681 c.addEqualTo(KFSPropertyConstants.BALANCE_TYPE_CODE, currentYearOptions.getActualFinancialBalanceTypeCd());
682 c.addIn(KFSPropertyConstants.OBJECT_TYPE_CODE, objectTypeService.getNominalActivityClosingAllowedObjectTypes(year));
683 c.addNotEqualTo("accountLineAnnualBalanceAmount", KualiDecimal.ZERO);
684
685 QueryByCriteria query = QueryFactory.newQuery(Balance.class, c);
686 query.addOrderByAscending(KFSPropertyConstants.CHART_OF_ACCOUNTS_CODE);
687 query.addOrderByAscending(KFSPropertyConstants.ACCOUNT_NUMBER);
688 query.addOrderByAscending(KFSPropertyConstants.SUB_ACCOUNT_NUMBER);
689 query.addOrderByAscending(KFSPropertyConstants.OBJECT_CODE);
690 query.addOrderByAscending(KFSPropertyConstants.SUB_OBJECT_CODE);
691 query.addOrderByAscending(KFSPropertyConstants.BALANCE_TYPE_CODE);
692 query.addOrderByAscending(KFSPropertyConstants.OBJECT_TYPE_CODE);
693
694 return getPersistenceBrokerTemplate().getIteratorByQuery(query);
695 }
696
697 /**
698 * Returns all of the balances that should be procesed by the BalanceForward year end job under the general rule
699 *
700 * @param the university fiscal year to find balances for
701 * @return an Iterator of Balances to process
702 * @see org.kuali.kfs.gl.dataaccess.BalanceDao#findCumulativeBalancesToForwardForFiscalYear(java.lang.Integer)
703 */
704 public Iterator<Balance> findGeneralBalancesToForwardForFiscalYear(Integer year) {
705 ObjectTypeService objectTypeService = SpringContext.getBean(ObjectTypeService.class);
706
707 String[] generalBalanceForwardBalanceTypesArray = parameterService.getParameterValues(BalanceForwardStep.class, GeneralLedgerConstants.BalanceForwardRule.BALANCE_TYPES_TO_ROLL_FORWARD_FOR_BALANCE_SHEET).toArray(new String[] {});
708 List<String> generalBalanceForwardBalanceTypes = new ArrayList<String>();
709 for (String bt : generalBalanceForwardBalanceTypesArray) {
710 generalBalanceForwardBalanceTypes.add(bt);
711 }
712
713 Criteria c = new Criteria();
714 c.addEqualTo(KFSPropertyConstants.UNIVERSITY_FISCAL_YEAR, year);
715 c.addIn(KFSPropertyConstants.BALANCE_TYPE_CODE, generalBalanceForwardBalanceTypes);
716 c.addIn(KFSPropertyConstants.OBJECT_TYPE_CODE, objectTypeService.getGeneralForwardBalanceObjectTypes(year));
717
718 QueryByCriteria query = QueryFactory.newQuery(Balance.class, c);
719 query.addOrderByAscending(KFSPropertyConstants.CHART_OF_ACCOUNTS_CODE);
720 query.addOrderByAscending(KFSPropertyConstants.ACCOUNT_NUMBER);
721 query.addOrderByAscending(KFSPropertyConstants.SUB_ACCOUNT_NUMBER);
722 query.addOrderByAscending(KFSPropertyConstants.OBJECT_CODE);
723 query.addOrderByAscending(KFSPropertyConstants.SUB_OBJECT_CODE);
724 query.addOrderByAscending(KFSPropertyConstants.BALANCE_TYPE_CODE);
725 query.addOrderByAscending(KFSPropertyConstants.OBJECT_TYPE_CODE);
726
727 Iterator<Balance> balances = getPersistenceBrokerTemplate().getIteratorByQuery(query);
728
729 Map<String, FilteringBalanceIterator> balanceIterators = SpringContext.getBeansOfType(FilteringBalanceIterator.class);
730 FilteringBalanceIterator filteredBalances = balanceIterators.get("glBalanceTotalNotZeroIterator");
731 filteredBalances.setBalancesSource(balances);
732
733 return filteredBalances;
734 }
735
736 /**
737 * Returns all of the balances that should be procesed by the BalanceForward year end job under the active rule
738 *
739 * @param the university fiscal year to find balances for
740 * @return an Iterator of Balances to process
741 * @see org.kuali.kfs.gl.dataaccess.BalanceDao#findGeneralBalancesToForwardForFiscalYear(java.lang.Integer)
742 */
743 public Iterator<Balance> findCumulativeBalancesToForwardForFiscalYear(Integer year) {
744 ObjectTypeService objectTypeService = SpringContext.getBean(ObjectTypeService.class);
745 SubFundGroupService subFundGroupService = SpringContext.getBean(SubFundGroupService.class);
746
747 final String[] subFundGroupsForCumulativeBalanceForwardingArray = parameterService.getParameterValues(BalanceForwardStep.class, GeneralLedgerConstants.BalanceForwardRule.SUB_FUND_GROUPS_FOR_INCEPTION_TO_DATE_REPORTING).toArray(new String[] {});
748 List<String> subFundGroupsForCumulativeBalanceForwarding = new ArrayList<String>();
749 for (String subFundGroup : subFundGroupsForCumulativeBalanceForwardingArray) {
750 subFundGroupsForCumulativeBalanceForwarding.add(subFundGroup);
751 }
752
753 String[] cumulativeBalanceForwardBalanceTypesArray = parameterService.getParameterValues(BalanceForwardStep.class, GeneralLedgerConstants.BalanceForwardRule.BALANCE_TYPES_TO_ROLL_FORWARD_FOR_INCOME_EXPENSE).toArray(new String[] {});
754 List<String> cumulativeBalanceForwardBalanceTypes = new ArrayList<String>();
755 for (String bt : cumulativeBalanceForwardBalanceTypesArray) {
756 cumulativeBalanceForwardBalanceTypes.add(bt);
757 }
758
759 Criteria c = new Criteria();
760 c.addEqualTo(KFSPropertyConstants.UNIVERSITY_FISCAL_YEAR, year);
761 c.addIn(KFSPropertyConstants.BALANCE_TYPE_CODE, cumulativeBalanceForwardBalanceTypes);
762 c.addIn(KFSPropertyConstants.OBJECT_TYPE_CODE, objectTypeService.getCumulativeForwardBalanceObjectTypes(year));
763
764 Criteria forCGCrit = new Criteria();
765 if (parameterService.getIndicatorParameter(Account.class, KFSConstants.ChartApcParms.ACCOUNT_FUND_GROUP_DENOTES_CG)) {
766 for (String value : subFundGroupService.getContractsAndGrantsDenotingValues()) {
767 forCGCrit.addEqualTo("priorYearAccount.subFundGroup.fundGroupCode", value);
768 }
769 } else {
770 for (String value : subFundGroupService.getContractsAndGrantsDenotingValues()) {
771 forCGCrit.addEqualTo("priorYearAccount.subFundGroupCode", value);
772 }
773 }
774
775 Criteria subFundGroupCrit = new Criteria();
776 subFundGroupCrit.addIn("priorYearAccount.subFundGroupCode", subFundGroupsForCumulativeBalanceForwarding);
777 forCGCrit.addOrCriteria(subFundGroupCrit);
778 c.addAndCriteria(forCGCrit);
779
780 QueryByCriteria query = QueryFactory.newQuery(Balance.class, c);
781 query.addOrderByAscending(KFSPropertyConstants.CHART_OF_ACCOUNTS_CODE);
782 query.addOrderByAscending(KFSPropertyConstants.ACCOUNT_NUMBER);
783 query.addOrderByAscending(KFSPropertyConstants.SUB_ACCOUNT_NUMBER);
784 query.addOrderByAscending(KFSPropertyConstants.OBJECT_CODE);
785 query.addOrderByAscending(KFSPropertyConstants.SUB_OBJECT_CODE);
786 query.addOrderByAscending(KFSPropertyConstants.BALANCE_TYPE_CODE);
787 query.addOrderByAscending(KFSPropertyConstants.OBJECT_TYPE_CODE);
788
789 Iterator<Balance> balances = getPersistenceBrokerTemplate().getIteratorByQuery(query);
790
791 FilteringBalanceIterator filteredBalances = SpringContext.getBean(FilteringBalanceIterator.class,"glBalanceAnnualAndCGTotalNotZeroIterator");
792 filteredBalances.setBalancesSource(balances);
793
794 return filteredBalances;
795 }
796
797 protected static final String PARAMETER_PREFIX = "SELECTION_";
798
799 /**
800 * Returns a list of balances to return for the Organization Reversion year end job to process
801 *
802 * @param the university fiscal year to find balances for
803 * @param endOfYear if true, use currrent year accounts, otherwise use prior year accounts
804 * @return an Iterator of Balances to process
805 * @see org.kuali.kfs.gl.dataaccess.BalanceDao#findOrganizationReversionBalancesForFiscalYear(java.lang.Integer, boolean)
806 */
807 public Iterator<Balance> findOrganizationReversionBalancesForFiscalYear(Integer year, boolean endOfYear) {
808 LOG.debug("findOrganizationReversionBalancesForFiscalYear() started");
809 Criteria c = new Criteria();
810 c.addEqualTo(KFSPropertyConstants.UNIVERSITY_FISCAL_YEAR, year);
811 ParameterService parameterService = SpringContext.getBean(ParameterService.class);
812 Map<Integer, String> parsedRules = new TreeMap<Integer, String>();
813 int i = 1;
814 boolean moreParams = true;
815 while (moreParams) {
816 if (parameterService.parameterExists(OrganizationReversion.class, PARAMETER_PREFIX + i)) {
817 ParameterEvaluator parameterEvaluator = parameterService.getParameterEvaluator(OrganizationReversion.class, PARAMETER_PREFIX + i);
818 String currentRule = parameterEvaluator.getValue();
819 if (endOfYear) {
820 currentRule = currentRule.replaceAll("account\\.", "priorYearAccount.");
821 }
822 if (StringUtils.isNotBlank(currentRule)) {
823 String propertyName = StringUtils.substringBefore(currentRule, "=");
824 List<String> ruleValues = Arrays.asList(StringUtils.substringAfter(currentRule, "=").split(";"));
825 if (propertyName != null && propertyName.length() > 0 && ruleValues.size() > 0 && !StringUtils.isBlank(ruleValues.get(0))) {
826 if (parameterEvaluator.constraintIsAllow()) {
827 c.addIn(propertyName, ruleValues);
828 }
829 else {
830 c.addNotIn(propertyName, ruleValues);
831 }
832 }
833 }
834 }
835 else {
836 moreParams = false;
837 }
838 i++;
839 }
840 // we only ever calculate on CB, AC, and encumbrance types, so let's only select those
841 SystemOptions options = SpringContext.getBean(OptionsService.class).getOptions(year);
842 List organizationReversionBalancesToSelect = new ArrayList();
843 organizationReversionBalancesToSelect.add(options.getActualFinancialBalanceTypeCd());
844 organizationReversionBalancesToSelect.add(options.getFinObjTypeExpenditureexpCd());
845 organizationReversionBalancesToSelect.add(options.getCostShareEncumbranceBalanceTypeCd());
846 organizationReversionBalancesToSelect.add(options.getIntrnlEncumFinBalanceTypCd());
847 organizationReversionBalancesToSelect.add(KFSConstants.BALANCE_TYPE_CURRENT_BUDGET);
848 c.addIn(KFSPropertyConstants.BALANCE_TYPE_CODE, organizationReversionBalancesToSelect);
849 QueryByCriteria query = QueryFactory.newQuery(Balance.class, c);
850 query.addOrderByAscending(KFSPropertyConstants.CHART_OF_ACCOUNTS_CODE);
851 query.addOrderByAscending(KFSPropertyConstants.ACCOUNT_NUMBER);
852 query.addOrderByAscending(KFSPropertyConstants.SUB_ACCOUNT_NUMBER);
853 query.addOrderByAscending(KFSPropertyConstants.OBJECT_CODE);
854 query.addOrderByAscending(KFSPropertyConstants.SUB_OBJECT_CODE);
855 query.addOrderByAscending(KFSPropertyConstants.BALANCE_TYPE_CODE);
856 query.addOrderByAscending(KFSPropertyConstants.OBJECT_TYPE_CODE);
857
858 return getPersistenceBrokerTemplate().getIteratorByQuery(query);
859 }
860
861 /**
862 * @see org.kuali.kfs.gl.dataaccess.BalancingDao#findCountGreaterOrEqualThan(java.lang.Integer)
863 */
864 public Integer findCountGreaterOrEqualThan(Integer year) {
865 Criteria criteria = new Criteria();
866 criteria.addGreaterOrEqualThan(KFSPropertyConstants.UNIVERSITY_FISCAL_YEAR, year);
867
868 ReportQueryByCriteria query = QueryFactory.newReportQuery(Balance.class, criteria);
869
870 return getPersistenceBrokerTemplate().getCount(query);
871 }
872
873 public void setOptionsService(OptionsService optionsService) {
874 this.optionsService = optionsService;
875 }
876
877 public void setParameterService(ParameterService parameterService) {
878 this.parameterService = parameterService;
879 }
880
881 public void setBalanceTypService(BalanceTypeService balanceTypService) {
882 this.balanceTypService = balanceTypService;
883 }
884 }