Source code

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