View Javadoc
1   /*
2    * Copyright 2007-2009 The Kuali Foundation
3    * 
4    * Licensed under the Educational Community License, Version 2.0 (the "License");
5    * you may not use this file except in compliance with the License.
6    * You may obtain a copy of the License at
7    * 
8    * http://www.opensource.org/licenses/ecl2.php
9    * 
10   * Unless required by applicable law or agreed to in writing, software
11   * distributed under the License is distributed on an "AS IS" BASIS,
12   * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
13   * See the License for the specific language governing permissions and
14   * limitations under the License.
15   */
16  package org.kuali.ole.gl.dataaccess.impl;
17  
18  import org.apache.ojb.broker.metadata.MetadataManager;
19  import org.kuali.ole.gl.GeneralLedgerConstants;
20  import org.kuali.ole.gl.businessobject.AccountBalance;
21  import org.kuali.ole.gl.businessobject.AccountBalanceHistory;
22  import org.kuali.ole.gl.businessobject.Balance;
23  import org.kuali.ole.gl.businessobject.BalanceHistory;
24  import org.kuali.ole.gl.businessobject.Encumbrance;
25  import org.kuali.ole.gl.businessobject.EncumbranceHistory;
26  import org.kuali.ole.gl.businessobject.Entry;
27  import org.kuali.ole.gl.businessobject.EntryHistory;
28  import org.kuali.ole.gl.dataaccess.BalancingDao;
29  import org.kuali.ole.gl.dataaccess.LedgerBalancingDao;
30  import org.kuali.rice.core.framework.persistence.jdbc.dao.PlatformAwareDaoBaseJdbc;
31  
32  /**
33   * JDBC implementation of BalancingDao and LedgerBalancingDao. This essentially is a copy of one table to another with
34   * group by in some cases. Hence the idea is that JDBC is much faster in this case then creating
35   * BO objects that are essentially not necessary.
36   */
37  public class BalancingDaoJdbc extends PlatformAwareDaoBaseJdbc implements BalancingDao, LedgerBalancingDao {
38      private static org.apache.log4j.Logger LOG = org.apache.log4j.Logger.getLogger(BalancingDaoJdbc.class);
39      
40      protected static final String VER_NBR = "VER_NBR";
41      protected static final String ROW_COUNT = "ROW_CNT";
42  
43      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;
44  
45      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;
46      protected static final String BALANCE_AMOUNT_FIELDS = GeneralLedgerConstants.ColumnNames.ANNUAL_BALANCE + ", " + GeneralLedgerConstants.ColumnNames.BEGINNING_BALANCE + ", " + GeneralLedgerConstants.ColumnNames.CONTRACT_AND_GRANTS_BEGINNING_BALANCE;
47      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";
48      
49      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;
50      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;
51      
52      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;
53      protected static final String ENCUMBRANCE_AMOUNT_FIELDS = GeneralLedgerConstants.ColumnNames.ACCOUNT_LINE_ENCUMBRANCE_AMOUNT + ", " + GeneralLedgerConstants.ColumnNames.ACCOUNT_LINE_ENCUMBRANCE_CLOSED_AMOUNT;
54      
55      /**
56       * @see org.kuali.ole.gl.dataaccess.LedgerBalancingDao#populateLedgerEntryHistory(java.lang.Integer)
57       */
58      public int populateLedgerEntryHistory(Integer universityFiscalYear) {
59          String entryTableName = MetadataManager.getInstance().getGlobalRepository().getDescriptorFor(Entry.class).getFullTableName();
60          String entryHistoryTableName = MetadataManager.getInstance().getGlobalRepository().getDescriptorFor(EntryHistory.class).getFullTableName();
61          
62          String sql = "INSERT INTO " + entryHistoryTableName + " (" + ENTRY_KEY_FIELDS + ", " + VER_NBR + ", " + GeneralLedgerConstants.ColumnNames.TRANSACTION_LEDGER_ENTRY_AMOUNT + ", " + ROW_COUNT + ")"
63          + " SELECT " + ENTRY_KEY_FIELDS + ", 1, sum(" +  GeneralLedgerConstants.ColumnNames.TRANSACTION_LEDGER_ENTRY_AMOUNT + "), count(*)"
64          + " FROM " + entryTableName
65          + " WHERE " + GeneralLedgerConstants.ColumnNames.UNIVERSITY_FISCAL_YEAR + " >= " + universityFiscalYear
66          + " GROUP BY " + ENTRY_KEY_FIELDS;
67          
68          LOG.debug(sql);
69          
70          return getSimpleJdbcTemplate().update(sql);
71      }
72      
73      /**
74       * @see org.kuali.ole.gl.dataaccess.LedgerBalancingDao#populateLedgerBalanceHistory(java.lang.Integer)
75       */
76      public int populateLedgerBalanceHistory(Integer universityFiscalYear) {
77          String balanceTableName = MetadataManager.getInstance().getGlobalRepository().getDescriptorFor(Balance.class).getFullTableName();
78          String balanceHistoryTableName = MetadataManager.getInstance().getGlobalRepository().getDescriptorFor(BalanceHistory.class).getFullTableName();
79          
80          String sql = "INSERT INTO " + balanceHistoryTableName + " (" + BALANCE_KEY_FIELDS + ", " + BALANCE_AMOUNT_FIELDS + ", " + BALANCE_MONTH_AMOUNT_FIELDS + ")"
81          + " SELECT " + BALANCE_KEY_FIELDS + ", " + BALANCE_AMOUNT_FIELDS + ", " + BALANCE_MONTH_AMOUNT_FIELDS
82          + " FROM " + balanceTableName
83          + " WHERE " + GeneralLedgerConstants.ColumnNames.UNIVERSITY_FISCAL_YEAR + " >= " + universityFiscalYear;
84          
85          LOG.debug(sql);
86          
87          return getSimpleJdbcTemplate().update(sql);
88      }
89      
90      /**
91       * @see org.kuali.ole.gl.dataaccess.BalancingDao#populateAccountBalancesHistory(java.lang.Integer)
92       */
93      public int populateAccountBalancesHistory(Integer universityFiscalYear) {
94          String accountBalanceTableName = MetadataManager.getInstance().getGlobalRepository().getDescriptorFor(AccountBalance.class).getFullTableName();
95          String accountBalanceHistoryTableName = MetadataManager.getInstance().getGlobalRepository().getDescriptorFor(AccountBalanceHistory.class).getFullTableName();
96          
97          String sql = "INSERT INTO " + accountBalanceHistoryTableName + " (" + ACCOUNT_BALANCE_KEY_FIELDS + ", " + ACCOUNT_BALANCE_AMOUNT_FIELDS + ")"
98          + " SELECT " + ACCOUNT_BALANCE_KEY_FIELDS + ", " + ACCOUNT_BALANCE_AMOUNT_FIELDS
99          + " 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.ole.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 }