Source code

001/*
002 * Copyright 2006 The Kuali Foundation
003 * 
004 * Licensed under the Educational Community License, Version 2.0 (the "License");
005 * you may not use this file except in compliance with the License.
006 * You may obtain a copy of the License at
007 * 
008 * http://www.opensource.org/licenses/ecl2.php
009 * 
010 * Unless required by applicable law or agreed to in writing, software
011 * distributed under the License is distributed on an "AS IS" BASIS,
012 * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
013 * See the License for the specific language governing permissions and
014 * limitations under the License.
015 */
016package org.kuali.ole.gl.batch.dataaccess.impl;
017
018import java.util.Iterator;
019import java.util.List;
020
021import org.apache.ojb.broker.query.Criteria;
022import org.apache.ojb.broker.query.QueryByCriteria;
023import org.apache.ojb.broker.query.QueryFactory;
024import org.apache.ojb.broker.query.ReportQueryByCriteria;
025import org.kuali.ole.gl.batch.dataaccess.SufficientFundsDao;
026import org.kuali.ole.gl.businessobject.SufficientFundBalances;
027import org.kuali.ole.sys.OLEConstants;
028import org.kuali.ole.sys.OLEPropertyConstants;
029import org.kuali.ole.sys.businessobject.GeneralLedgerPendingEntry;
030import org.kuali.ole.sys.util.TransactionalServiceUtils;
031import org.kuali.rice.core.api.util.type.KualiDecimal;
032import org.kuali.rice.core.framework.persistence.ojb.dao.PlatformAwareDaoBaseOjb;
033
034/**
035 * An OJB implementation of SufficientFundsDao
036 */
037public class SufficientFundsDaoOjb extends PlatformAwareDaoBaseOjb implements SufficientFundsDao {
038    private static org.apache.log4j.Logger LOG = org.apache.log4j.Logger.getLogger(SufficientFundsDaoOjb.class);
039
040    private static final String YEAR_END_DOC_PREFIX = "YE%";
041
042    /**
043     * Constructs a SufficientFundsDaoOjb instance
044     */
045    public SufficientFundsDaoOjb() {
046    }
047
048    /**
049     * Calculate the Prior Fiscal Year Budget total
050     * 
051     * @param universityFiscalYear the university fiscal year of sufficient funds balances that will be summarized
052     * @param chartOfAccountCode the chart of accounts code of sufficient fund balance records that will be summarized
053     * @param accountNumber the account number of sufficient fund balances that will be summarized
054     * @return the sum of the prior fiscal year budget
055     * @see org.kuali.ole.gl.batch.dataaccess.SufficientFundsDao#calculateM113PfyrBudget(java.lang.Integer, java.lang.String,
056     *      java.lang.String)
057     */
058    public KualiDecimal calculateM113PfyrBudget(Integer universityFiscalYear, String chartOfAccountsCode, String accountNumber) {
059        Criteria criteria = new Criteria();
060        criteria.addEqualTo(OLEConstants.UNIVERSITY_FISCAL_YEAR_PROPERTY_NAME, new Integer(universityFiscalYear.intValue() - 1));
061        criteria.addEqualTo(OLEConstants.CHART_OF_ACCOUNTS_CODE_PROPERTY_NAME, chartOfAccountsCode);
062        criteria.addEqualTo(OLEConstants.ACCOUNT_NUMBER_PROPERTY_NAME, accountNumber);
063        criteria.addEqualTo(OLEConstants.ACCOUNT_SUFFICIENT_FUNDS_CODE_PROPERTY_NAME, OLEConstants.SF_TYPE_CASH_AT_ACCOUNT);
064
065        ReportQueryByCriteria reportQuery = QueryFactory.newReportQuery(SufficientFundBalances.class, criteria);
066        reportQuery.setAttributes(new String[] { OLEConstants.CURRENT_BUDGET_BALANCE_AMOUNT_PROPERTY_NAME });
067
068
069        return executeReportQuery(reportQuery);
070    }
071
072    /**
073     * Calculate the prior fiscal year encumbrance total
074     * 
075     * @param universityFiscalYear the university fiscal year of sufficient funds balances to summarize
076     * @param chartOfAccountsCode the chart of accounts code of sufficient funds balances to summarize
077     * @param accountNumber the account number of sufficient fund balances to summarize
078     * @return the prior fiscal year encumbrnace total
079     * @see org.kuali.ole.gl.batch.dataaccess.SufficientFundsDao#calculateM113PfyrEncum(java.lang.Integer, java.lang.String, java.lang.String)
080     */
081    public KualiDecimal calculateM113PfyrEncum(Integer universityFiscalYear, String chartOfAccountsCode, String accountNumber) {
082        Criteria criteria = new Criteria();
083        criteria.addEqualTo(OLEConstants.UNIVERSITY_FISCAL_YEAR_PROPERTY_NAME, new Integer(universityFiscalYear.intValue() - 1));
084        criteria.addEqualTo(OLEConstants.CHART_OF_ACCOUNTS_CODE_PROPERTY_NAME, chartOfAccountsCode);
085        criteria.addEqualTo(OLEConstants.ACCOUNT_NUMBER_PROPERTY_NAME, accountNumber);
086        criteria.addEqualTo(OLEConstants.ACCOUNT_SUFFICIENT_FUNDS_CODE_PROPERTY_NAME, OLEConstants.SF_TYPE_CASH_AT_ACCOUNT);
087
088        ReportQueryByCriteria reportQuery = QueryFactory.newReportQuery(SufficientFundBalances.class, criteria);
089        reportQuery.setAttributes(new String[] { OLEConstants.ACCOUNT_ENCUMBRANCE_AMOUNT_PROPERTY_NAME });
090
091        return executeReportQuery(reportQuery);
092    }
093
094    /**
095     * Calculate the prior fiscal year pending actual amount
096     * 
097     * @param universityFiscalYear the university fiscal year of sufficient funds balances to summarize
098     * @param chartOfAccountsCode the chart of accounts code of sufficient funds balances to summarize
099     * @param accountNumber the account number of sufficient fund balances to summarize
100     * @param specialFinancialObjectCodes this actually doesn't seem to be used
101     * @param financialObjectCodeForCashInBank the object code for cash in the bank
102     * @return the prior fiscal year pending actual amount
103     * @see org.kuali.ole.gl.batch.dataaccess.SufficientFundsDao#calculateM113PendActual(boolean, java.lang.Integer, java.lang.String,
104     *      java.lang.String, List, String)
105     */
106    public KualiDecimal calculateM113PendActual(boolean financialBeginBalanceLoadInd, Integer universityFiscalYear, String chartOfAccountsCode, String accountNumber, List specialFinancialObjectCodes, String financialObjectCodeForCashInBank) {
107        // fp_sasfc:61-2...78-3 m113 calculate pendActual
108        KualiDecimal pendActual = calculateM113PendActual1(financialBeginBalanceLoadInd, universityFiscalYear, chartOfAccountsCode, accountNumber, true, financialObjectCodeForCashInBank);
109        pendActual = pendActual.subtract(calculateM113PendActual1(financialBeginBalanceLoadInd, universityFiscalYear, chartOfAccountsCode, accountNumber, false, financialObjectCodeForCashInBank));
110        pendActual = pendActual.add(calculateM113PendActual2(financialBeginBalanceLoadInd, universityFiscalYear, chartOfAccountsCode, accountNumber, false, specialFinancialObjectCodes));
111        pendActual = pendActual.subtract(calculateM113PendActual2(financialBeginBalanceLoadInd, universityFiscalYear, chartOfAccountsCode, accountNumber, true, specialFinancialObjectCodes));
112
113        return pendActual;
114
115    }
116
117    /**
118     * Calculates the current pending actual
119     * 
120     * @param isYearEndDocument should year end documents be included?
121     * @param actualFinancialBalanceTypeCd the actual balance type code
122     * @param universityFiscalYear the university fiscal year of sufficient funds balances to summarize
123     * @param chartOfAccountsCode the chart of accounts code of sufficient funds balances to summarize
124     * @param accountNumber the account number of sufficient fund balances to summarize
125     * @param acctSufficientFundsFinObjCd the object code for sufficient funds
126     * @param expenditureCodes object codes that represent expenditures
127     * @return the current pending actual total
128     * @see org.kuali.ole.gl.batch.dataaccess.SufficientFundsDao#calculatePendActual(boolean, java.lang.String, java.lang.Integer,
129     *      java.lang.String, java.lang.String, java.lang.String, List)
130     */
131    public KualiDecimal calculatePendActual(boolean isYearEndDocument, String actualFinancialBalanceTypeCd, Integer universityFiscalYear, String chartOfAccountsCode, String accountNumber, String acctSufficientFundsFinObjCd, List expenditureCodes) {
132        KualiDecimal pendActual = calculatePendActual1(isYearEndDocument, actualFinancialBalanceTypeCd, universityFiscalYear, chartOfAccountsCode, accountNumber, acctSufficientFundsFinObjCd, true, expenditureCodes);
133        pendActual = pendActual.subtract(calculatePendActual1(isYearEndDocument, actualFinancialBalanceTypeCd, universityFiscalYear, chartOfAccountsCode, accountNumber, acctSufficientFundsFinObjCd, false, expenditureCodes));
134        return pendActual;
135    }
136
137    /**
138     * calculates the current year pending budget total
139     * 
140     * @param isYearEndDocument should year end documents be included?
141     * @param budgetCheckingBalanceTypeCd the budget balance type code
142     * @param universityFiscalYear the university fiscal year of sufficient funds balances to summarize
143     * @param chartOfAccountsCode the chart of accounts code of sufficient funds balances to summarize
144     * @param accountNumber the account number of sufficient fund balances to summarize
145     * @param acctSufficientFundsFinObjCd the object code for sufficient funds
146     * @param expenditureCodes object codes that represent expenditures
147     * @return calculates the current year pending budget total
148     * @see org.kuali.ole.gl.batch.dataaccess.SufficientFundsDao#calculatePendBudget(boolean, java.lang.String, java.lang.Integer,
149     *      java.lang.String, java.lang.String, java.lang.String, List)
150     */
151    public KualiDecimal calculatePendBudget(boolean isYearEndDocument, String budgetCheckingBalanceTypeCd, Integer universityFiscalYear, String chartOfAccountsCode, String accountNumber, String acctSufficientFundsFinObjCd, List expenditureCodes) {
152        Criteria criteria = new Criteria();
153        criteria.addEqualTo(OLEConstants.FINANCIAL_BALANCE_TYPE_CODE_PROPERTY_NAME, budgetCheckingBalanceTypeCd);
154        criteria.addEqualTo(OLEConstants.UNIVERSITY_FISCAL_YEAR_PROPERTY_NAME, universityFiscalYear);
155        criteria.addEqualTo(OLEConstants.ACCOUNT_NUMBER_PROPERTY_NAME, accountNumber);
156        criteria.addIn(OLEConstants.FINANCIAL_OBJECT_TYPE_CODE, expenditureCodes);
157        criteria.addEqualTo(OLEConstants.ACCOUNT_SUFFICIENT_FUNDS_FINANCIAL_OBJECT_CODE_PROPERTY_NAME, acctSufficientFundsFinObjCd);
158        criteria.addNotEqualTo(OLEConstants.DOCUMENT_HEADER_PROPERTY_NAME + "." + OLEConstants.DOCUMENT_HEADER_DOCUMENT_STATUS_CODE_PROPERTY_NAME, OLEConstants.DocumentStatusCodes.CANCELLED);
159
160        if (isYearEndDocument) {
161            criteria.addLike(OLEConstants.FINANCIAL_DOCUMENT_TYPE_CODE, YEAR_END_DOC_PREFIX);
162        }
163        else {
164            criteria.addNotLike(OLEConstants.FINANCIAL_DOCUMENT_TYPE_CODE, YEAR_END_DOC_PREFIX);
165        }
166
167        ReportQueryByCriteria reportQuery = QueryFactory.newReportQuery(GeneralLedgerPendingEntry.class, criteria);
168        reportQuery.setAttributes(new String[] { "sum(" + OLEConstants.TRANSACTION_LEDGER_ENTRY_AMOUNT + ")" });
169
170        return executeReportQuery(reportQuery);
171
172    }
173
174    /**
175     * Calculates the current year pending encumbrance total
176     * 
177     * @param isYearEndDocument should year end documents be included?
178     * @param extrnlEncumFinBalanceTypCd the external encumbrance balance type
179     * @param intrnlEncumFinBalanceTypCd the internal encumbrance balance type
180     * @param preencumbranceFinBalTypeCd the pre-encumbrance balance type
181     * @param universityFiscalYear the university fiscal year of sufficient funds balances to summarize
182     * @param chartOfAccountsCode the chart of accounts code of sufficient funds balances to summarize
183     * @param accountNumber the account number of sufficient fund balances to summarize
184     * @param acctSufficientFundsFinObjCd the object code for sufficient funds
185     * @param expenditureCodes object codes that represent expenditures
186     * @return the current year pending encumbrance total
187     * @see org.kuali.ole.gl.batch.dataaccess.SufficientFundsDao#calculatePendEncum(boolean, java.lang.String, java.lang.String,
188     *      java.lang.String, java.lang.Integer, java.lang.String, java.lang.String, java.lang.String, List)
189     */
190    public KualiDecimal calculatePendEncum(boolean isYearEndDocument, String extrnlEncumFinBalanceTypCd, String intrnlEncumFinBalanceTypCd, String preencumbranceFinBalTypeCd, Integer universityFiscalYear, String chartOfAccountsCode, String accountNumber, String acctSufficientFundsFinObjCd, List expenditureCodes) {
191        KualiDecimal pendEncum = calculatePendEncum1(isYearEndDocument, extrnlEncumFinBalanceTypCd, intrnlEncumFinBalanceTypCd, preencumbranceFinBalTypeCd, universityFiscalYear, chartOfAccountsCode, accountNumber, acctSufficientFundsFinObjCd, true, expenditureCodes);
192        pendEncum = pendEncum.subtract(calculatePendEncum1(isYearEndDocument, extrnlEncumFinBalanceTypCd, intrnlEncumFinBalanceTypCd, preencumbranceFinBalTypeCd, universityFiscalYear, chartOfAccountsCode, accountNumber, acctSufficientFundsFinObjCd, false, expenditureCodes));
193        return pendEncum;
194    }
195
196    /**
197     * Calcluate this part of the encumbrance total
198     * 
199     * @param isYearEndDocument should year end documents be included?
200     * @param extrnlEncumFinBalanceTypCd the external encumbrance balance type
201     * @param intrnlEncumFinBalanceTypCd the internal encumbrance balance type
202     * @param preencumbranceFinBalTypeCd the pre-encumbrance balance type
203     * @param universityFiscalYear the university fiscal year of sufficient funds balances to summarize
204     * @param chartOfAccountsCode the chart of accounts code of sufficient funds balances to summarize
205     * @param accountNumber the account number of sufficient fund balances to summarize
206     * @param acctSufficientFundsFinObjCd the object code for sufficient funds
207     * @param isEqualDebitCode should debits be included in the calculation or not
208     * @return this part of the encumbrance total
209     */
210    protected KualiDecimal calculatePendEncum1(boolean isYearEndDocument, String extrnlEncumFinBalanceTypCd, String intrnlEncumFinBalanceTypCd, String preencumbranceFinBalTypeCd, Integer universityFiscalYear, String chartOfAccountsCode, String accountNumber, String acctSufficientFundsFinObjCd, boolean isEqualDebitCode, List expenditureCodes) {
211        Criteria criteria = new Criteria();
212
213        Criteria sub1 = new Criteria();
214        sub1.addEqualTo(OLEConstants.FINANCIAL_BALANCE_TYPE_CODE_PROPERTY_NAME, extrnlEncumFinBalanceTypCd);
215        Criteria sub1_1 = new Criteria();
216        sub1_1.addEqualTo(OLEConstants.FINANCIAL_BALANCE_TYPE_CODE_PROPERTY_NAME, intrnlEncumFinBalanceTypCd);
217        Criteria sub1_2 = new Criteria();
218        sub1_2.addEqualTo(OLEConstants.FINANCIAL_BALANCE_TYPE_CODE_PROPERTY_NAME, preencumbranceFinBalTypeCd);
219        sub1_1.addOrCriteria(sub1_2);
220        sub1.addOrCriteria(sub1_1);
221        criteria.addOrCriteria(sub1);
222
223
224        criteria.addEqualTo(OLEConstants.UNIVERSITY_FISCAL_YEAR_PROPERTY_NAME, universityFiscalYear);
225        criteria.addEqualTo(OLEConstants.CHART_OF_ACCOUNTS_CODE_PROPERTY_NAME, chartOfAccountsCode);
226        criteria.addEqualTo(OLEConstants.ACCOUNT_NUMBER_PROPERTY_NAME, accountNumber);
227        criteria.addEqualTo(OLEConstants.ACCOUNT_SUFFICIENT_FUNDS_FINANCIAL_OBJECT_CODE_PROPERTY_NAME, acctSufficientFundsFinObjCd);
228        criteria.addIn(OLEConstants.FINANCIAL_OBJECT_TYPE_CODE, expenditureCodes);
229
230        if (isEqualDebitCode) {
231            criteria.addEqualTo(OLEConstants.TRANSACTION_DEBIT_CREDIT_CODE, OLEConstants.GL_DEBIT_CODE);
232        }
233        else {
234            criteria.addNotEqualTo(OLEConstants.TRANSACTION_DEBIT_CREDIT_CODE, OLEConstants.GL_DEBIT_CODE);
235        }
236
237        criteria.addNotEqualTo(OLEConstants.DOCUMENT_HEADER_PROPERTY_NAME + "." + OLEConstants.DOCUMENT_HEADER_DOCUMENT_STATUS_CODE_PROPERTY_NAME, OLEConstants.DocumentStatusCodes.CANCELLED);
238
239        if (isYearEndDocument) {
240            criteria.addLike(OLEConstants.FINANCIAL_DOCUMENT_TYPE_CODE, YEAR_END_DOC_PREFIX);
241        }
242        else {
243            criteria.addNotLike(OLEConstants.FINANCIAL_DOCUMENT_TYPE_CODE, YEAR_END_DOC_PREFIX);
244        }
245
246        ReportQueryByCriteria reportQuery = QueryFactory.newReportQuery(GeneralLedgerPendingEntry.class, criteria);
247        reportQuery.setAttributes(new String[] { "sum(" + OLEConstants.TRANSACTION_LEDGER_ENTRY_AMOUNT + ")" });
248
249        return executeReportQuery(reportQuery);
250
251
252    }
253
254    /**
255     * Calculate this part of the actual total
256     * 
257     * @param isYearEndDocument should year end documents be included?
258     * @param actualFinancialBalanceTypeCd the actual balance type code
259     * @param universityFiscalYear the university fiscal year of sufficient funds balances to summarize
260     * @param chartOfAccountsCode the chart of accounts code of sufficient funds balances to summarize
261     * @param accountNumber the account number of sufficient fund balances to summarize
262     * @param acctSufficientFundsFinObjCd the object code for sufficient funds
263     * @return this part of the actual total
264     */
265    protected KualiDecimal calculatePendActual1(boolean isYearEndDocument, String actualFinancialBalanceTypeCd, Integer universityFiscalYear, String chartOfAccountsCode, String accountNumber, String acctSufficientFundsFinObjCd, boolean isEqualDebitCode, List expenditureCodes) {
266        Criteria criteria = new Criteria();
267        criteria.addEqualTo(OLEConstants.FINANCIAL_BALANCE_TYPE_CODE_PROPERTY_NAME, actualFinancialBalanceTypeCd);
268        criteria.addEqualTo(OLEConstants.UNIVERSITY_FISCAL_YEAR_PROPERTY_NAME, universityFiscalYear);
269        criteria.addEqualTo(OLEConstants.ACCOUNT_NUMBER_PROPERTY_NAME, accountNumber);
270        criteria.addEqualTo(OLEConstants.ACCOUNT_SUFFICIENT_FUNDS_FINANCIAL_OBJECT_CODE_PROPERTY_NAME, acctSufficientFundsFinObjCd);
271        criteria.addIn(OLEConstants.FINANCIAL_OBJECT_TYPE_CODE, expenditureCodes);
272
273        if (isEqualDebitCode) {
274            criteria.addEqualTo(OLEConstants.TRANSACTION_DEBIT_CREDIT_CODE, OLEConstants.GL_DEBIT_CODE);
275        }
276        else {
277            criteria.addNotEqualTo(OLEConstants.TRANSACTION_DEBIT_CREDIT_CODE, OLEConstants.GL_DEBIT_CODE);
278        }
279
280        criteria.addNotEqualTo(OLEConstants.DOCUMENT_HEADER_PROPERTY_NAME + "." + OLEConstants.DOCUMENT_HEADER_DOCUMENT_STATUS_CODE_PROPERTY_NAME, OLEConstants.DocumentStatusCodes.CANCELLED);
281
282        if (isYearEndDocument) {
283            criteria.addLike(OLEConstants.FINANCIAL_DOCUMENT_TYPE_CODE, YEAR_END_DOC_PREFIX);
284        }
285        else {
286            criteria.addNotLike(OLEConstants.FINANCIAL_DOCUMENT_TYPE_CODE, YEAR_END_DOC_PREFIX);
287        }
288
289        ReportQueryByCriteria reportQuery = QueryFactory.newReportQuery(GeneralLedgerPendingEntry.class, criteria);
290        reportQuery.setAttributes(new String[] { "sum(" + OLEConstants.TRANSACTION_LEDGER_ENTRY_AMOUNT + ")" });
291        return executeReportQuery(reportQuery);
292    }
293
294    /**
295     * calculate part of the actual total
296     * 
297     * @param universityFiscalYear the university fiscal year of sufficient funds balances to summarize
298     * @param chartOfAccountsCode the chart of accounts code of sufficient funds balances to summarize
299     * @param accountNumber the account number of sufficient fund balances to summarize
300     * @return thsi part of the actual total
301     */
302    protected KualiDecimal calculateM113PendActual1(boolean financialBeginBalanceLoadInd, Integer universityFiscalYear, String chartOfAccountsCode, String accountNumber, boolean isEqualDebitCode, String financialObjectCodeForCashInBank) {
303        Criteria criteria = new Criteria();
304        criteria.addEqualTo(OLEConstants.FINANCIAL_BALANCE_TYPE_CODE_PROPERTY_NAME, OLEConstants.BALANCE_TYPE_ACTUAL);
305
306        if (financialBeginBalanceLoadInd) {
307            criteria.addEqualTo(OLEConstants.UNIVERSITY_FISCAL_YEAR_PROPERTY_NAME, universityFiscalYear);
308        }
309        else {
310            Criteria sub1 = new Criteria();
311            sub1.addEqualTo(OLEConstants.UNIVERSITY_FISCAL_YEAR_PROPERTY_NAME, universityFiscalYear);
312            Criteria sub1_1 = new Criteria();
313            sub1_1.addEqualTo(OLEConstants.UNIVERSITY_FISCAL_YEAR_PROPERTY_NAME, new Integer(universityFiscalYear.intValue() - 1));
314            sub1.addOrCriteria(sub1_1);
315            criteria.addAndCriteria(sub1);
316        }
317
318        criteria.addEqualTo(OLEConstants.CHART_OF_ACCOUNTS_CODE_PROPERTY_NAME, chartOfAccountsCode);
319        criteria.addEqualTo(OLEConstants.ACCOUNT_NUMBER_PROPERTY_NAME, accountNumber);
320        criteria.addEqualTo(OLEConstants.FINANCIAL_OBJECT_CODE_PROPERTY_NAME, financialObjectCodeForCashInBank);
321
322        if (isEqualDebitCode) {
323            criteria.addEqualTo(OLEConstants.TRANSACTION_DEBIT_CREDIT_CODE, OLEConstants.GL_DEBIT_CODE);
324        }
325        else {
326            criteria.addNotEqualTo(OLEConstants.TRANSACTION_DEBIT_CREDIT_CODE, OLEConstants.GL_DEBIT_CODE);
327        }
328
329        criteria.addNotEqualTo(OLEConstants.DOCUMENT_HEADER_PROPERTY_NAME + "." + OLEConstants.DOCUMENT_HEADER_DOCUMENT_STATUS_CODE_PROPERTY_NAME, OLEConstants.DocumentStatusCodes.CANCELLED);
330
331        ReportQueryByCriteria reportQuery = QueryFactory.newReportQuery(GeneralLedgerPendingEntry.class, criteria);
332        reportQuery.setAttributes(new String[] { "sum(" + OLEConstants.TRANSACTION_LEDGER_ENTRY_AMOUNT + ")" });
333
334        return executeReportQuery(reportQuery);
335    }
336
337    /**
338     * Calculate part of the actual total
339     * 
340     * @param universityFiscalYear the university fiscal year of sufficient funds balances to summarize
341     * @param chartOfAccountsCode the chart of accounts code of sufficient funds balances to summarize
342     * @param accountNumber the account number of sufficient fund balances to summarize
343     * @param isEqualDebitCode should this query be returning debits or not?
344     * @param specialFinancialObjectCodes include only these financial object codes
345     * @return this part of the actual total
346     */
347    protected KualiDecimal calculateM113PendActual2(boolean financialBeginBalanceLoadInd, Integer universityFiscalYear, String chartOfAccountsCode, String accountNumber, boolean isEqualDebitCode, List specialFinancialObjectCodes) {
348        Criteria criteria = new Criteria();
349        criteria.addEqualTo(OLEConstants.FINANCIAL_BALANCE_TYPE_CODE_PROPERTY_NAME, OLEConstants.BALANCE_TYPE_ACTUAL);
350
351        if (financialBeginBalanceLoadInd) {
352            criteria.addEqualTo(OLEConstants.UNIVERSITY_FISCAL_YEAR_PROPERTY_NAME, universityFiscalYear);
353        }
354        else {
355            Criteria sub1 = new Criteria();
356            sub1.addEqualTo(OLEConstants.UNIVERSITY_FISCAL_YEAR_PROPERTY_NAME, universityFiscalYear);
357            Criteria sub1_1 = new Criteria();
358            sub1_1.addEqualTo(OLEConstants.UNIVERSITY_FISCAL_YEAR_PROPERTY_NAME, new Integer(universityFiscalYear.intValue() - 1));
359            sub1.addOrCriteria(sub1_1);
360            criteria.addAndCriteria(sub1);
361        }
362
363        criteria.addEqualTo(OLEConstants.CHART_OF_ACCOUNTS_CODE_PROPERTY_NAME, chartOfAccountsCode);
364        criteria.addEqualTo(OLEConstants.ACCOUNT_NUMBER_PROPERTY_NAME, accountNumber);
365        criteria.addIn(OLEConstants.FINANCIAL_OBJECT_CODE_PROPERTY_NAME, specialFinancialObjectCodes);
366
367        if (isEqualDebitCode) {
368            criteria.addEqualTo(OLEConstants.TRANSACTION_DEBIT_CREDIT_CODE, OLEConstants.GL_DEBIT_CODE);
369        }
370        else {
371            criteria.addNotEqualTo(OLEConstants.TRANSACTION_DEBIT_CREDIT_CODE, OLEConstants.GL_DEBIT_CODE);
372        }
373
374        criteria.addNotEqualTo(OLEConstants.DOCUMENT_HEADER_PROPERTY_NAME + "." + OLEConstants.DOCUMENT_HEADER_DOCUMENT_STATUS_CODE_PROPERTY_NAME, OLEConstants.DocumentStatusCodes.CANCELLED);
375
376        ReportQueryByCriteria reportQuery = QueryFactory.newReportQuery(GeneralLedgerPendingEntry.class, criteria);
377        reportQuery.setAttributes(new String[] { "sum(" + OLEConstants.TRANSACTION_LEDGER_ENTRY_AMOUNT + ")" });
378
379        return executeReportQuery(reportQuery);
380    }
381
382    /**
383     * Purge table by year/chart
384     * 
385     * @param chart the chart of sufficient fund balances to purge
386     * @param year the year of sufficient fund balances to purge
387     */
388    public void purgeYearByChart(String chartOfAccountsCode, int year) {
389        LOG.debug("purgeYearByChart() started");
390
391        Criteria criteria = new Criteria();
392        criteria.addEqualTo(OLEPropertyConstants.CHART_OF_ACCOUNTS_CODE, chartOfAccountsCode);
393        criteria.addLessThan(OLEPropertyConstants.UNIVERSITY_FISCAL_YEAR, new Integer(year));
394
395        getPersistenceBrokerTemplate().deleteByQuery(new QueryByCriteria(SufficientFundBalances.class, criteria));
396
397        // This is required because if any deleted account balances are in the cache, deleteByQuery doesn't
398        // remove them from the cache so a future select will retrieve these deleted account balances from
399        // the cache and return them. Clearing the cache forces OJB to go to the database again.
400        getPersistenceBrokerTemplate().clearCache();
401    }
402
403    /**
404     * This returns the very first value returned by a report query and then makes certain that OJB closes the 
405     * connection that retrieved the query data
406     * 
407     * @param reportQuery the ReportQuery to find the first value for
408     * @return the first value generated from the given query
409     */
410    protected KualiDecimal executeReportQuery(ReportQueryByCriteria reportQuery) {
411        Iterator iterator = getPersistenceBrokerTemplate().getReportQueryIteratorByQuery(reportQuery);
412        if (iterator.hasNext()) {
413            KualiDecimal returnResult = (KualiDecimal) ((Object[]) TransactionalServiceUtils.retrieveFirstAndExhaustIterator(iterator))[0];
414            return returnResult;
415        }
416        else {
417            return KualiDecimal.ZERO;
418        }
419    }
420}