001/*
002 * Copyright 2007-2009 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 org.apache.ojb.broker.metadata.MetadataManager;
019import org.kuali.ole.gl.GeneralLedgerConstants;
020import org.kuali.ole.gl.businessobject.AccountBalance;
021import org.kuali.ole.gl.businessobject.AccountBalanceHistory;
022import org.kuali.ole.gl.businessobject.Balance;
023import org.kuali.ole.gl.businessobject.BalanceHistory;
024import org.kuali.ole.gl.businessobject.Encumbrance;
025import org.kuali.ole.gl.businessobject.EncumbranceHistory;
026import org.kuali.ole.gl.businessobject.Entry;
027import org.kuali.ole.gl.businessobject.EntryHistory;
028import org.kuali.ole.gl.dataaccess.BalancingDao;
029import org.kuali.ole.gl.dataaccess.LedgerBalancingDao;
030import org.kuali.rice.core.framework.persistence.jdbc.dao.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 */
037public 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.ole.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.ole.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.ole.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.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}