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.math.BigDecimal;
019 import java.util.Date;
020 import java.util.Iterator;
021
022 import org.apache.ojb.broker.query.Criteria;
023 import org.apache.ojb.broker.query.QueryByCriteria;
024 import org.apache.ojb.broker.query.QueryFactory;
025 import org.apache.ojb.broker.query.ReportQueryByCriteria;
026 import org.kuali.kfs.gl.businessobject.Entry;
027 import org.kuali.kfs.gl.businessobject.Transaction;
028 import org.kuali.kfs.gl.dataaccess.EntryDao;
029 import org.kuali.kfs.gl.dataaccess.LedgerEntryBalancingDao;
030 import org.kuali.kfs.sys.KFSConstants;
031 import org.kuali.kfs.sys.KFSPropertyConstants;
032 import org.kuali.rice.kns.dao.impl.PlatformAwareDaoBaseOjb;
033 import org.kuali.rice.kns.util.ObjectUtils;
034 import org.kuali.rice.kns.util.TransactionalServiceUtils;
035
036 /**
037 * An OJB implementation of EntryDao
038 */
039 public class EntryDaoOjb extends PlatformAwareDaoBaseOjb implements EntryDao, LedgerEntryBalancingDao {
040 private static org.apache.log4j.Logger LOG = org.apache.log4j.Logger.getLogger(EntryDaoOjb.class);
041
042 private final static String UNIVERISITY_FISCAL_YEAR = "universityFiscalYear";
043 private final static String CHART_OF_ACCOUNTS_CODE = "chartOfAccountsCode";
044 private final static String ACCOUNT_NUMBER = "accountNumber";
045 private final static String SUB_ACCOUNT_NUMBER = "subAccountNumber";
046 private final static String FINANCIAL_OBJECT_CODE = "financialObjectCode";
047 private final static String FINANCIAL_SUB_OBJECT_CODE = "financialSubObjectCode";
048 private final static String FINANCIAL_BALANCE_TYPE_CODE = "financialBalanceTypeCode";
049 private final static String FINANCIAL_OBJECT_TYPE_CODE = "financialObjectTypeCode";
050 private final static String UNIVERISTY_FISCAL_PERIOD_CODE = "universityFiscalPeriodCode";
051 private final static String FINANCIAL_DOCUMENT_TYPE_CODE = "financialDocumentTypeCode";
052 private final static String FINANCIAL_SYSTEM_ORIGINATION_CODE = "financialSystemOriginationCode";
053 private final static String MAX_CONSTANT = "max(documentNumber)";
054
055
056 /**
057 * Constructs a EntryDaoOjb instance
058 */
059 public EntryDaoOjb() {
060 super();
061 }
062
063 /**
064 * Turns the given transaction into an entry and then saves that entry in the database
065 *
066 * @param t the transaction to save
067 * @param postDate the officially reported posting date
068 * @see org.kuali.kfs.gl.dataaccess.EntryDao#addEntry(org.kuali.kfs.gl.businessobject.Transaction, java.util.Date)
069 */
070 public void addEntry(Transaction t, Date postDate) {
071 LOG.debug("addEntry() started");
072
073 Entry e = new Entry(t, postDate);
074
075 getPersistenceBrokerTemplate().store(e);
076 }
077
078 /**
079 * Find the maximum transactionLedgerEntrySequenceNumber in the entry table for a specific transaction. This is used to make
080 * sure that rows added have a unique primary key.
081 *
082 * @param t the transaction to check
083 * @return the max sequence number
084 */
085 public int getMaxSequenceNumber(Transaction t) {
086 LOG.debug("getSequenceNumber() ");
087
088 Criteria crit = new Criteria();
089 crit.addEqualTo(UNIVERISITY_FISCAL_YEAR, t.getUniversityFiscalYear());
090 crit.addEqualTo(CHART_OF_ACCOUNTS_CODE, t.getChartOfAccountsCode());
091 crit.addEqualTo(ACCOUNT_NUMBER, t.getAccountNumber());
092 crit.addEqualTo(SUB_ACCOUNT_NUMBER, t.getSubAccountNumber());
093 crit.addEqualTo(FINANCIAL_OBJECT_CODE, t.getFinancialObjectCode());
094 crit.addEqualTo(FINANCIAL_SUB_OBJECT_CODE, t.getFinancialSubObjectCode());
095 crit.addEqualTo(FINANCIAL_BALANCE_TYPE_CODE, t.getFinancialBalanceTypeCode());
096 crit.addEqualTo(FINANCIAL_OBJECT_TYPE_CODE, t.getFinancialObjectTypeCode());
097 crit.addEqualTo(UNIVERISTY_FISCAL_PERIOD_CODE, t.getUniversityFiscalPeriodCode());
098 crit.addEqualTo(FINANCIAL_DOCUMENT_TYPE_CODE, t.getFinancialDocumentTypeCode());
099 crit.addEqualTo(FINANCIAL_SYSTEM_ORIGINATION_CODE, t.getFinancialSystemOriginationCode());
100 crit.addEqualTo(KFSPropertyConstants.DOCUMENT_NUMBER, t.getDocumentNumber());
101
102 ReportQueryByCriteria q = QueryFactory.newReportQuery(Entry.class, crit);
103 q.setAttributes(new String[] { "max(transactionLedgerEntrySequenceNumber)" });
104
105 Iterator iter = getPersistenceBrokerTemplate().getReportQueryIteratorByQuery(q);
106 // would this work better? max = (BigDecimal) getPersistenceBrokerTemplate().getObjectByQuery(q);
107 BigDecimal max = null;
108 while (iter.hasNext()) {
109 Object[] data = (Object[]) iter.next();
110 max = (BigDecimal) data[0]; // Don't know why OJB returns a BigDecimal, but it does
111 }
112 if (max == null) {
113 return 0;
114 }
115 else {
116 return max.intValue();
117 }
118 }
119
120 /**
121 * Purge the entry table by chart/year
122 *
123 * @param chart the chart of accounts code of entries to purge
124 * @param year the university fiscal year of entries to purge
125 */
126 public void purgeYearByChart(String chartOfAccountsCode, int year) {
127 LOG.debug("purgeYearByChart() started");
128
129 Criteria criteria = new Criteria();
130 criteria.addEqualTo(CHART_OF_ACCOUNTS_CODE, chartOfAccountsCode);
131 criteria.addLessThan(UNIVERISITY_FISCAL_YEAR, new Integer(year));
132
133 getPersistenceBrokerTemplate().deleteByQuery(new QueryByCriteria(Entry.class, criteria));
134
135 // This is required because if any deleted rows are in the cache, deleteByQuery doesn't
136 // remove them from the cache so a future select will retrieve these deleted account balances from
137 // the cache and return them. Clearing the cache forces OJB to go to the database again.
138 getPersistenceBrokerTemplate().clearCache();
139 }
140
141 /**
142 * @see org.kuali.kfs.gl.dataaccess.LedgerEntryBalancingDao#findEntryByGroup(java.lang.Integer, java.lang.String, java.lang.String, java.lang.String, java.lang.String, java.lang.String)
143 */
144 public Object[] findEntryByGroup(Integer universityFiscalYear, String chartOfAccountsCode, String financialObjectCode, String financialBalanceTypeCode, String universityFiscalPeriodCode, String transactionDebitCreditCode) {
145 Criteria criteria = new Criteria();
146 criteria.addEqualTo(KFSConstants.UNIVERSITY_FISCAL_YEAR_PROPERTY_NAME, universityFiscalYear);
147 criteria.addEqualTo(KFSConstants.CHART_OF_ACCOUNTS_CODE_PROPERTY_NAME, chartOfAccountsCode);
148 criteria.addEqualTo(KFSConstants.FINANCIAL_OBJECT_CODE_PROPERTY_NAME, financialObjectCode);
149 criteria.addEqualTo(KFSConstants.FINANCIAL_BALANCE_TYPE_CODE_PROPERTY_NAME, financialBalanceTypeCode);
150 criteria.addEqualTo(KFSConstants.UNIVERSITY_FISCAL_PERIOD_CODE_PROPERTY_NAME, universityFiscalPeriodCode);
151 criteria.addEqualTo(KFSConstants.TRANSACTION_DEBIT_CREDIT_CODE, transactionDebitCreditCode);
152
153 ReportQueryByCriteria reportQuery = QueryFactory.newReportQuery(Entry.class, criteria);
154 reportQuery.setAttributes(new String[] { "count(*)", "sum(" + KFSConstants.TRANSACTION_LEDGER_ENTRY_AMOUNT + ")"});
155 reportQuery.addGroupBy(new String[] { KFSConstants.UNIVERSITY_FISCAL_YEAR_PROPERTY_NAME, KFSConstants.CHART_OF_ACCOUNTS_CODE_PROPERTY_NAME, KFSConstants.FINANCIAL_OBJECT_CODE_PROPERTY_NAME, KFSConstants.FINANCIAL_BALANCE_TYPE_CODE_PROPERTY_NAME, KFSConstants.UNIVERSITY_FISCAL_PERIOD_CODE_PROPERTY_NAME, KFSConstants.TRANSACTION_DEBIT_CREDIT_CODE});
156
157 Iterator<Object[]> iterator = getPersistenceBrokerTemplate().getReportQueryIteratorByQuery(reportQuery);
158 Object[] returnResult = TransactionalServiceUtils.retrieveFirstAndExhaustIterator(iterator);
159
160 if (ObjectUtils.isNull(returnResult)) {
161 // Do nothing, we'll return null. Data wasn't found.
162 } else if (returnResult[0] instanceof BigDecimal) {
163 returnResult[0] = ((BigDecimal) returnResult[0]).intValue();
164 } else {
165 returnResult[0] = ((Long) returnResult[0]).intValue();
166 }
167
168 return returnResult;
169 }
170
171 /**
172 * @see org.kuali.kfs.gl.dataaccess.LedgerEntryBalancingDao#findCountGreaterOrEqualThan(java.lang.Integer)
173 */
174 public Integer findCountGreaterOrEqualThan(Integer year) {
175 Criteria criteria = new Criteria();
176 criteria.addGreaterOrEqualThan(KFSPropertyConstants.UNIVERSITY_FISCAL_YEAR, year);
177
178 ReportQueryByCriteria query = QueryFactory.newReportQuery(Entry.class, criteria);
179
180 return getPersistenceBrokerTemplate().getCount(query);
181 }
182 }