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 org.apache.ojb.broker.metadata.MetadataManager;
019 import org.kuali.kfs.gl.GeneralLedgerConstants;
020 import org.kuali.kfs.gl.businessobject.AccountBalance;
021 import org.kuali.kfs.gl.businessobject.AccountBalanceHistory;
022 import org.kuali.kfs.gl.businessobject.Balance;
023 import org.kuali.kfs.gl.businessobject.BalanceHistory;
024 import org.kuali.kfs.gl.businessobject.Encumbrance;
025 import org.kuali.kfs.gl.businessobject.EncumbranceHistory;
026 import org.kuali.kfs.gl.businessobject.Entry;
027 import org.kuali.kfs.gl.businessobject.EntryHistory;
028 import org.kuali.kfs.gl.dataaccess.BalancingDao;
029 import org.kuali.kfs.gl.dataaccess.LedgerBalancingDao;
030 import org.kuali.rice.kns.dao.jdbc.PlatformAwareDaoBaseJdbc;
031
032 /**
033 * JDBC implementation of BalancingDao and LedgerBalancingDao. This essentially is a copy of one table to another with
034 * group by in some cases. Hence the idea is that JDBC is much faster in this case then creating
035 * BO objects that are essentially not necessary.
036 */
037 public class BalancingDaoJdbc extends PlatformAwareDaoBaseJdbc implements BalancingDao, LedgerBalancingDao {
038 private static org.apache.log4j.Logger LOG = org.apache.log4j.Logger.getLogger(BalancingDaoJdbc.class);
039
040 protected static final String VER_NBR = "VER_NBR";
041 protected static final String ROW_COUNT = "ROW_CNT";
042
043 protected static final String ENTRY_KEY_FIELDS = GeneralLedgerConstants.ColumnNames.UNIVERSITY_FISCAL_YEAR + ", " + GeneralLedgerConstants.ColumnNames.CHART_OF_ACCOUNTS_CODE + ", " + GeneralLedgerConstants.ColumnNames.OBJECT_CODE + ", " + GeneralLedgerConstants.ColumnNames.BALANCE_TYPE_CODE + ", " + GeneralLedgerConstants.ColumnNames.FISCAL_PERIOD_CODE+ ", " + GeneralLedgerConstants.ColumnNames.DEBIT_CREDIT_CODE;
044
045 protected static final String BALANCE_KEY_FIELDS = GeneralLedgerConstants.ColumnNames.UNIVERSITY_FISCAL_YEAR + ", " + GeneralLedgerConstants.ColumnNames.CHART_OF_ACCOUNTS_CODE + ", " + GeneralLedgerConstants.ColumnNames.ACCOUNT_NUMBER + ", " + GeneralLedgerConstants.ColumnNames.SUB_ACCOUNT_NUMBER + ", " + GeneralLedgerConstants.ColumnNames.OBJECT_CODE + ", " + GeneralLedgerConstants.ColumnNames.SUB_OBJECT_CODE + ", " + GeneralLedgerConstants.ColumnNames.BALANCE_TYPE_CODE + ", " + GeneralLedgerConstants.ColumnNames.OBJECT_TYPE_CODE;
046 protected static final String BALANCE_AMOUNT_FIELDS = GeneralLedgerConstants.ColumnNames.ANNUAL_BALANCE + ", " + GeneralLedgerConstants.ColumnNames.BEGINNING_BALANCE + ", " + GeneralLedgerConstants.ColumnNames.CONTRACT_AND_GRANTS_BEGINNING_BALANCE;
047 protected static final String BALANCE_MONTH_AMOUNT_FIELDS = "MO1_ACCT_LN_AMT, MO2_ACCT_LN_AMT, MO3_ACCT_LN_AMT, MO4_ACCT_LN_AMT, MO5_ACCT_LN_AMT, MO6_ACCT_LN_AMT, MO7_ACCT_LN_AMT, MO8_ACCT_LN_AMT, MO9_ACCT_LN_AMT, MO10_ACCT_LN_AMT, MO11_ACCT_LN_AMT, MO12_ACCT_LN_AMT, MO13_ACCT_LN_AMT";
048
049 protected static final String ACCOUNT_BALANCE_KEY_FIELDS = GeneralLedgerConstants.ColumnNames.UNIVERSITY_FISCAL_YEAR + ", " + GeneralLedgerConstants.ColumnNames.CHART_OF_ACCOUNTS_CODE + ", " + GeneralLedgerConstants.ColumnNames.ACCOUNT_NUMBER + ", " + GeneralLedgerConstants.ColumnNames.SUB_ACCOUNT_NUMBER + ", " + GeneralLedgerConstants.ColumnNames.OBJECT_CODE + ", " + GeneralLedgerConstants.ColumnNames.SUB_OBJECT_CODE;
050 protected static final String ACCOUNT_BALANCE_AMOUNT_FIELDS = GeneralLedgerConstants.ColumnNames.CURRENT_BUDGET_LINE_BALANCE_AMOUNT + ", " + GeneralLedgerConstants.ColumnNames.ACCOUNT_LINE_ACTUALS_BALANCE_AMOUNT + ", " + GeneralLedgerConstants.ColumnNames.ACCOUNT_LINE_ENCUMBRANCE_BALANCE_AMOUNT;
051
052 protected static final String ENCUMBRANCE_KEY_FIELDS = GeneralLedgerConstants.ColumnNames.UNIVERSITY_FISCAL_YEAR + ", " + GeneralLedgerConstants.ColumnNames.CHART_OF_ACCOUNTS_CODE + ", " + GeneralLedgerConstants.ColumnNames.ACCOUNT_NUMBER + ", " + GeneralLedgerConstants.ColumnNames.SUB_ACCOUNT_NUMBER + ", " + GeneralLedgerConstants.ColumnNames.OBJECT_CODE + ", " + GeneralLedgerConstants.ColumnNames.SUB_OBJECT_CODE + ", " + GeneralLedgerConstants.ColumnNames.BALANCE_TYPE_CODE + ", " + GeneralLedgerConstants.ColumnNames.FINANCIAL_DOCUMENT_TYPE_CODE + ", " + GeneralLedgerConstants.ColumnNames.ORIGINATION_CODE + ", " + GeneralLedgerConstants.ColumnNames.DOCUMENT_NUMBER;
053 protected static final String ENCUMBRANCE_AMOUNT_FIELDS = GeneralLedgerConstants.ColumnNames.ACCOUNT_LINE_ENCUMBRANCE_AMOUNT + ", " + GeneralLedgerConstants.ColumnNames.ACCOUNT_LINE_ENCUMBRANCE_CLOSED_AMOUNT;
054
055 /**
056 * @see org.kuali.kfs.gl.dataaccess.LedgerBalancingDao#populateLedgerEntryHistory(java.lang.Integer)
057 */
058 public int populateLedgerEntryHistory(Integer universityFiscalYear) {
059 String entryTableName = MetadataManager.getInstance().getGlobalRepository().getDescriptorFor(Entry.class).getFullTableName();
060 String entryHistoryTableName = MetadataManager.getInstance().getGlobalRepository().getDescriptorFor(EntryHistory.class).getFullTableName();
061
062 String sql = "INSERT INTO " + entryHistoryTableName + " (" + ENTRY_KEY_FIELDS + ", " + VER_NBR + ", " + GeneralLedgerConstants.ColumnNames.TRANSACTION_LEDGER_ENTRY_AMOUNT + ", " + ROW_COUNT + ")"
063 + " SELECT " + ENTRY_KEY_FIELDS + ", 1, sum(" + GeneralLedgerConstants.ColumnNames.TRANSACTION_LEDGER_ENTRY_AMOUNT + "), count(*)"
064 + " FROM " + entryTableName
065 + " WHERE " + GeneralLedgerConstants.ColumnNames.UNIVERSITY_FISCAL_YEAR + " >= " + universityFiscalYear
066 + " GROUP BY " + ENTRY_KEY_FIELDS;
067
068 LOG.debug(sql);
069
070 return getSimpleJdbcTemplate().update(sql);
071 }
072
073 /**
074 * @see org.kuali.kfs.gl.dataaccess.LedgerBalancingDao#populateLedgerBalanceHistory(java.lang.Integer)
075 */
076 public int populateLedgerBalanceHistory(Integer universityFiscalYear) {
077 String balanceTableName = MetadataManager.getInstance().getGlobalRepository().getDescriptorFor(Balance.class).getFullTableName();
078 String balanceHistoryTableName = MetadataManager.getInstance().getGlobalRepository().getDescriptorFor(BalanceHistory.class).getFullTableName();
079
080 String sql = "INSERT INTO " + balanceHistoryTableName + " (" + BALANCE_KEY_FIELDS + ", " + BALANCE_AMOUNT_FIELDS + ", " + BALANCE_MONTH_AMOUNT_FIELDS + ")"
081 + " SELECT " + BALANCE_KEY_FIELDS + ", " + BALANCE_AMOUNT_FIELDS + ", " + BALANCE_MONTH_AMOUNT_FIELDS
082 + " FROM " + balanceTableName
083 + " WHERE " + GeneralLedgerConstants.ColumnNames.UNIVERSITY_FISCAL_YEAR + " >= " + universityFiscalYear;
084
085 LOG.debug(sql);
086
087 return getSimpleJdbcTemplate().update(sql);
088 }
089
090 /**
091 * @see org.kuali.kfs.gl.dataaccess.BalancingDao#populateAccountBalancesHistory(java.lang.Integer)
092 */
093 public int populateAccountBalancesHistory(Integer universityFiscalYear) {
094 String accountBalanceTableName = MetadataManager.getInstance().getGlobalRepository().getDescriptorFor(AccountBalance.class).getFullTableName();
095 String accountBalanceHistoryTableName = MetadataManager.getInstance().getGlobalRepository().getDescriptorFor(AccountBalanceHistory.class).getFullTableName();
096
097 String sql = "INSERT INTO " + accountBalanceHistoryTableName + " (" + ACCOUNT_BALANCE_KEY_FIELDS + ", " + ACCOUNT_BALANCE_AMOUNT_FIELDS + ")"
098 + " SELECT " + ACCOUNT_BALANCE_KEY_FIELDS + ", " + ACCOUNT_BALANCE_AMOUNT_FIELDS
099 + " FROM " + accountBalanceTableName
100 + " WHERE " + GeneralLedgerConstants.ColumnNames.UNIVERSITY_FISCAL_YEAR + " >= " + universityFiscalYear;
101
102 LOG.debug(sql);
103
104 return getSimpleJdbcTemplate().update(sql);
105 }
106
107 /**
108 * @see org.kuali.kfs.gl.dataaccess.BalancingDao#populateEncumbranceHistory(java.lang.Integer)
109 */
110 public int populateEncumbranceHistory(Integer universityFiscalYear) {
111 String encumbranceTableName = MetadataManager.getInstance().getGlobalRepository().getDescriptorFor(Encumbrance.class).getFullTableName();
112 String encumbranceHistoryTableName = MetadataManager.getInstance().getGlobalRepository().getDescriptorFor(EncumbranceHistory.class).getFullTableName();
113
114 String sql = "INSERT INTO " + encumbranceHistoryTableName + " (" + ENCUMBRANCE_KEY_FIELDS + ", " + ENCUMBRANCE_AMOUNT_FIELDS + ")"
115 + " SELECT " + ENCUMBRANCE_KEY_FIELDS + ", " + ENCUMBRANCE_AMOUNT_FIELDS
116 + " FROM " + encumbranceTableName
117 + " WHERE " + GeneralLedgerConstants.ColumnNames.UNIVERSITY_FISCAL_YEAR + " >= " + universityFiscalYear;
118
119 LOG.debug(sql);
120
121 return getSimpleJdbcTemplate().update(sql);
122 }
123 }