/*
* The Kuali Financial System, a comprehensive financial management system for higher education.
*
* Copyright 2005-2014 The Kuali Foundation
*
* This program is free software: you can redistribute it and/or modify
* it under the terms of the GNU Affero General Public License as
* published by the Free Software Foundation, either version 3 of the
* License, or (at your option) any later version.
*
* This program is distributed in the hope that it will be useful,
* but WITHOUT ANY WARRANTY; without even the implied warranty of
* MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
* GNU Affero General Public License for more details.
*
* You should have received a copy of the GNU Affero General Public License
* along with this program. If not, see <http://www.gnu.org/licenses/>.
*/
package org.kuali.kfs.gl.batch.dataaccess.impl;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.util.HashMap;
import java.util.Map;
import org.apache.commons.lang.StringUtils;
import org.kuali.kfs.gl.GeneralLedgerConstants;
import org.kuali.kfs.gl.batch.dataaccess.LedgerPreparedStatementCachingDao;
import org.kuali.kfs.gl.businessobject.AccountBalance;
import org.kuali.kfs.gl.businessobject.Balance;
import org.kuali.kfs.gl.businessobject.Encumbrance;
import org.kuali.kfs.gl.businessobject.Entry;
import org.kuali.kfs.gl.businessobject.ExpenditureTransaction;
import org.kuali.kfs.gl.businessobject.Reversal;
import org.kuali.kfs.gl.businessobject.SufficientFundBalances;
import org.kuali.kfs.gl.businessobject.Transaction;
import org.kuali.kfs.sys.batch.dataaccess.impl.AbstractPreparedStatementCachingDaoJdbc;
import org.kuali.rice.core.api.util.type.KualiDecimal;
public class LedgerPreparedStatementCachingDaoJdbc extends AbstractPreparedStatementCachingDaoJdbc implements LedgerPreparedStatementCachingDao {
static final Map<String, String> sql = new HashMap<String, String>();
static {
sql.put(RETRIEVE_PREFIX + Integer.class, "select max(trn_entr_seq_nbr) from GL_ENTRY_T where univ_fiscal_yr = ? and fin_coa_cd = ? and account_nbr = ? and sub_acct_nbr = ? and fin_object_cd = ? and fin_sub_obj_cd = ? and fin_balance_typ_cd = ? and fin_obj_typ_cd = ? and univ_fiscal_prd_cd = ? and fdoc_typ_cd = ? and fs_origin_cd = ? and fdoc_nbr = ?");
sql.put(RETRIEVE_PREFIX + Balance.class, "select ACLN_ANNL_BAL_AMT, FIN_BEG_BAL_LN_AMT, CONTR_GR_BB_AC_AMT, 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 from GL_BALANCE_T where UNIV_FISCAL_YR = ? and FIN_COA_CD = ? and ACCOUNT_NBR = ? and SUB_ACCT_NBR = ? and FIN_OBJECT_CD = ? and FIN_SUB_OBJ_CD = ? and FIN_BALANCE_TYP_CD = ? and FIN_OBJ_TYP_CD = ?");
sql.put(INSERT_PREFIX + Balance.class, "insert into GL_BALANCE_T (UNIV_FISCAL_YR,FIN_COA_CD,ACCOUNT_NBR,SUB_ACCT_NBR,FIN_OBJECT_CD,FIN_SUB_OBJ_CD,FIN_BALANCE_TYP_CD,FIN_OBJ_TYP_CD,ACLN_ANNL_BAL_AMT,FIN_BEG_BAL_LN_AMT,CONTR_GR_BB_AC_AMT,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,TIMESTAMP)values (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)");
sql.put(UPDATE_PREFIX + Balance.class, "update GL_BALANCE_T set ACLN_ANNL_BAL_AMT = ?, FIN_BEG_BAL_LN_AMT = ?, CONTR_GR_BB_AC_AMT = ?, 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 = ?, TIMESTAMP = ? where UNIV_FISCAL_YR = ? and FIN_COA_CD = ? and ACCOUNT_NBR = ? and SUB_ACCT_NBR = ? and FIN_OBJECT_CD = ? and FIN_SUB_OBJ_CD = ? and FIN_BALANCE_TYP_CD = ? and FIN_OBJ_TYP_CD = ?");
sql.put(RETRIEVE_PREFIX + Encumbrance.class, "select TRN_ENCUM_DESC, TRN_ENCUM_DT, ACLN_ENCUM_AMT, ACLN_ENCUM_CLS_AMT, ACLN_ENCUM_PRG_CD from GL_ENCUMBRANCE_T where univ_fiscal_yr = ? and fin_coa_cd = ? and account_nbr = ? and sub_acct_nbr = ? and fin_object_cd = ? and fin_sub_obj_cd = ? and fin_balance_typ_cd = ? and fdoc_typ_cd = ? and fs_origin_cd = ? and fdoc_nbr = ?");
sql.put(INSERT_PREFIX + Encumbrance.class, "insert into GL_ENCUMBRANCE_T (UNIV_FISCAL_YR, FIN_COA_CD, ACCOUNT_NBR, SUB_ACCT_NBR, FIN_OBJECT_CD, FIN_SUB_OBJ_CD, FIN_BALANCE_TYP_CD, FDOC_TYP_CD, FS_ORIGIN_CD, FDOC_NBR, TRN_ENCUM_DESC, TRN_ENCUM_DT, ACLN_ENCUM_AMT, ACLN_ENCUM_CLS_AMT, ACLN_ENCUM_PRG_CD, TIMESTAMP) values (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)");
sql.put(UPDATE_PREFIX + Encumbrance.class, "update GL_ENCUMBRANCE_T set TRN_ENCUM_DESC = ?, TRN_ENCUM_DT = ?, ACLN_ENCUM_AMT = ?, ACLN_ENCUM_CLS_AMT = ?, ACLN_ENCUM_PRG_CD = ?, TIMESTAMP = ? where univ_fiscal_yr = ? and fin_coa_cd = ? and account_nbr = ? and sub_acct_nbr = ? and fin_object_cd = ? and fin_sub_obj_cd = ? and fin_balance_typ_cd = ? and fdoc_typ_cd = ? and fs_origin_cd = ? and fdoc_nbr = ?");
sql.put(RETRIEVE_PREFIX + ExpenditureTransaction.class, "select ACCT_OBJ_DCST_AMT from GL_EXPEND_TRN_MT where UNIV_FISCAL_YR = ? and FIN_COA_CD = ? and ACCOUNT_NBR = ? and SUB_ACCT_NBR = ? and FIN_OBJECT_CD = ? and FIN_SUB_OBJ_CD = ? and FIN_BALANCE_TYP_CD = ? and FIN_OBJ_TYP_CD = ? and UNIV_FISCAL_PRD_CD = ? and PROJECT_CD = ? and ORG_REFERENCE_ID = ?");
sql.put(INSERT_PREFIX + ExpenditureTransaction.class, "insert into GL_EXPEND_TRN_MT (UNIV_FISCAL_YR, FIN_COA_CD, ACCOUNT_NBR, SUB_ACCT_NBR, FIN_OBJECT_CD, FIN_SUB_OBJ_CD, FIN_BALANCE_TYP_CD, FIN_OBJ_TYP_CD, UNIV_FISCAL_PRD_CD, PROJECT_CD, ORG_REFERENCE_ID, ACCT_OBJ_DCST_AMT) values (?,?,?,?,?,?,?,?,?,?,?,?)");
sql.put(UPDATE_PREFIX + ExpenditureTransaction.class, "update GL_EXPEND_TRN_MT set ACCT_OBJ_DCST_AMT = ? where UNIV_FISCAL_YR = ? and FIN_COA_CD = ? and ACCOUNT_NBR = ? and SUB_ACCT_NBR = ? and FIN_OBJECT_CD = ? and FIN_SUB_OBJ_CD = ? and FIN_BALANCE_TYP_CD = ? and FIN_OBJ_TYP_CD = ? and UNIV_FISCAL_PRD_CD = ? and PROJECT_CD = ? and ORG_REFERENCE_ID = ?");
sql.put(RETRIEVE_PREFIX + SufficientFundBalances.class, "select ACCT_SF_CD, CURR_BDGT_BAL_AMT, ACCT_ACTL_XPND_AMT, ACCT_ENCUM_AMT from GL_SF_BALANCES_T where UNIV_FISCAL_YR = ? and FIN_COA_CD = ? and ACCOUNT_NBR = ? and FIN_OBJECT_CD = ?");
sql.put(INSERT_PREFIX + SufficientFundBalances.class, "insert into GL_SF_BALANCES_T (UNIV_FISCAL_YR, FIN_COA_CD, ACCOUNT_NBR, FIN_OBJECT_CD, ACCT_SF_CD, CURR_BDGT_BAL_AMT, ACCT_ACTL_XPND_AMT, ACCT_ENCUM_AMT, TIMESTAMP) values (?,?,?,?,?,?,?,?,?)");
sql.put(UPDATE_PREFIX + SufficientFundBalances.class, "update GL_SF_BALANCES_T set ACCT_SF_CD = ?, CURR_BDGT_BAL_AMT = ?, ACCT_ACTL_XPND_AMT = ?, ACCT_ENCUM_AMT = ?, TIMESTAMP = ? where UNIV_FISCAL_YR = ? and FIN_COA_CD = ? and ACCOUNT_NBR = ? and FIN_OBJECT_CD = ?");
sql.put(RETRIEVE_PREFIX + AccountBalance.class, "select CURR_BDLN_BAL_AMT, ACLN_ACTLS_BAL_AMT, ACLN_ENCUM_BAL_AMT from GL_ACCT_BALANCES_T where UNIV_FISCAL_YR = ? and FIN_COA_CD = ? and ACCOUNT_NBR = ? and SUB_ACCT_NBR = ? and FIN_OBJECT_CD = ? and FIN_SUB_OBJ_CD = ?");
sql.put(INSERT_PREFIX + AccountBalance.class, "insert into GL_ACCT_BALANCES_T (UNIV_FISCAL_YR, FIN_COA_CD, ACCOUNT_NBR, SUB_ACCT_NBR, FIN_OBJECT_CD, FIN_SUB_OBJ_CD, CURR_BDLN_BAL_AMT, ACLN_ACTLS_BAL_AMT, ACLN_ENCUM_BAL_AMT, TIMESTAMP) values (?,?,?,?,?,?,?,?,?,?)");
sql.put(UPDATE_PREFIX + AccountBalance.class, "update GL_ACCT_BALANCES_T set CURR_BDLN_BAL_AMT = ?, ACLN_ACTLS_BAL_AMT = ?, ACLN_ENCUM_BAL_AMT = ?, TIMESTAMP = ? where UNIV_FISCAL_YR = ? and FIN_COA_CD = ? and ACCOUNT_NBR = ? and SUB_ACCT_NBR = ? and FIN_OBJECT_CD = ? and FIN_SUB_OBJ_CD = ?");
sql.put(INSERT_PREFIX + Entry.class, "INSERT INTO GL_ENTRY_T (UNIV_FISCAL_YR, FIN_COA_CD, ACCOUNT_NBR, SUB_ACCT_NBR, FIN_OBJECT_CD, FIN_SUB_OBJ_CD, FIN_BALANCE_TYP_CD, FIN_OBJ_TYP_CD, UNIV_FISCAL_PRD_CD, FDOC_TYP_CD, FS_ORIGIN_CD, FDOC_NBR, TRN_ENTR_SEQ_NBR, TRN_LDGR_ENTR_DESC, TRN_LDGR_ENTR_AMT, TRN_DEBIT_CRDT_CD, TRANSACTION_DT, ORG_DOC_NBR, PROJECT_CD, ORG_REFERENCE_ID, FDOC_REF_TYP_CD, FS_REF_ORIGIN_CD, FDOC_REF_NBR, FDOC_REVERSAL_DT, TRN_ENCUM_UPDT_CD, TRN_POST_DT, TIMESTAMP) VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)");
sql.put(INSERT_PREFIX + Reversal.class, "INSERT INTO GL_REVERSAL_T (FDOC_REVERSAL_DT, UNIV_FISCAL_YR, FIN_COA_CD, ACCOUNT_NBR, SUB_ACCT_NBR, FIN_OBJECT_CD, FIN_SUB_OBJ_CD, FIN_BALANCE_TYP_CD, FIN_OBJ_TYP_CD, UNIV_FISCAL_PRD_CD, FDOC_TYP_CD, FS_ORIGIN_CD, FDOC_NBR, TRN_ENTR_SEQ_NBR, TRN_LDGR_ENTR_DESC, TRN_LDGR_ENTR_AMT, TRN_DEBIT_CRDT_CD, TRANSACTION_DT, ORG_DOC_NBR, PROJECT_CD, ORG_REFERENCE_ID, FDOC_REF_TYP_CD, FS_REF_ORIGIN_CD, FDOC_REF_NBR, TRN_ENCUM_UPDT_CD, TRN_POST_DT) VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)");
}
@Override
protected Map<String, String> getSql() {
return sql;
}
public int getMaxSequenceNumber(final Transaction t) {
return new RetrievingJdbcWrapper<Integer>() {
@Override
protected void populateStatement(PreparedStatement preparedStatement) throws SQLException {
preparedStatement.setInt(1, t.getUniversityFiscalYear());
preparedStatement.setString(2, t.getChartOfAccountsCode());
preparedStatement.setString(3, t.getAccountNumber());
preparedStatement.setString(4, t.getSubAccountNumber());
preparedStatement.setString(5, t.getFinancialObjectCode());
preparedStatement.setString(6, t.getFinancialSubObjectCode());
preparedStatement.setString(7, t.getFinancialBalanceTypeCode());
preparedStatement.setString(8, t.getFinancialObjectTypeCode());
preparedStatement.setString(9, t.getUniversityFiscalPeriodCode());
preparedStatement.setString(10, t.getFinancialDocumentTypeCode());
preparedStatement.setString(11, t.getFinancialSystemOriginationCode());
preparedStatement.setString(12, t.getDocumentNumber());
}
@Override
protected Integer extractResult(ResultSet resultSet) throws SQLException {
return resultSet.getInt(1);
}
}.get(Integer.class);
}
public AccountBalance getAccountBalance(final Transaction t) {
return new RetrievingJdbcWrapper<AccountBalance>() {
@Override
protected void populateStatement(PreparedStatement preparedStatement) throws SQLException {
preparedStatement.setInt(1, t.getUniversityFiscalYear());
preparedStatement.setString(2, t.getChartOfAccountsCode());
preparedStatement.setString(3, t.getAccountNumber());
preparedStatement.setString(4, t.getSubAccountNumber());
preparedStatement.setString(5, t.getFinancialObjectCode());
preparedStatement.setString(6, t.getFinancialSubObjectCode());
}
@Override
protected AccountBalance extractResult(ResultSet resultSet) throws SQLException {
AccountBalance accountBalance = new AccountBalance();
accountBalance.setUniversityFiscalYear(t.getUniversityFiscalYear());
accountBalance.setChartOfAccountsCode(t.getChartOfAccountsCode());
accountBalance.setAccountNumber(t.getAccountNumber());
accountBalance.setSubAccountNumber(t.getSubAccountNumber());
accountBalance.setObjectCode(t.getFinancialObjectCode());
accountBalance.setSubObjectCode(t.getFinancialSubObjectCode());
accountBalance.setCurrentBudgetLineBalanceAmount(new KualiDecimal(resultSet.getBigDecimal(1)));
accountBalance.setAccountLineActualsBalanceAmount(new KualiDecimal(resultSet.getBigDecimal(2)));
accountBalance.setAccountLineEncumbranceBalanceAmount(new KualiDecimal(resultSet.getBigDecimal(3)));
return accountBalance;
}
}.get(AccountBalance.class);
}
public void insertAccountBalance(final AccountBalance accountBalance, final Timestamp currentTimestamp) {
new InsertingJdbcWrapper<AccountBalance>() {
@Override
protected void populateStatement(PreparedStatement preparedStatement) throws SQLException {
preparedStatement.setInt(1, accountBalance.getUniversityFiscalYear());
preparedStatement.setString(2, accountBalance.getChartOfAccountsCode());
preparedStatement.setString(3, accountBalance.getAccountNumber());
preparedStatement.setString(4, accountBalance.getSubAccountNumber());
preparedStatement.setString(5, accountBalance.getObjectCode());
preparedStatement.setString(6, accountBalance.getSubObjectCode());
preparedStatement.setBigDecimal(7, accountBalance.getCurrentBudgetLineBalanceAmount().bigDecimalValue());
preparedStatement.setBigDecimal(8, accountBalance.getAccountLineActualsBalanceAmount().bigDecimalValue());
preparedStatement.setBigDecimal(9, accountBalance.getAccountLineEncumbranceBalanceAmount().bigDecimalValue());
preparedStatement.setTimestamp(10, currentTimestamp);
}
}.execute(AccountBalance.class);
}
public void updateAccountBalance(final AccountBalance accountBalance, final Timestamp currentTimestamp) {
new UpdatingJdbcWrapper<AccountBalance>() {
@Override
protected void populateStatement(PreparedStatement preparedStatement) throws SQLException {
preparedStatement.setBigDecimal(1, accountBalance.getCurrentBudgetLineBalanceAmount().bigDecimalValue());
preparedStatement.setBigDecimal(2, accountBalance.getAccountLineActualsBalanceAmount().bigDecimalValue());
preparedStatement.setBigDecimal(3, accountBalance.getAccountLineEncumbranceBalanceAmount().bigDecimalValue());
preparedStatement.setTimestamp(4, currentTimestamp);
preparedStatement.setInt(5, accountBalance.getUniversityFiscalYear());
preparedStatement.setString(6, accountBalance.getChartOfAccountsCode());
preparedStatement.setString(7, accountBalance.getAccountNumber());
preparedStatement.setString(8, accountBalance.getSubAccountNumber());
preparedStatement.setString(9, accountBalance.getObjectCode());
preparedStatement.setString(10, accountBalance.getSubObjectCode());
}
}.execute(AccountBalance.class);
}
public Balance getBalance(final Transaction t) {
return new RetrievingJdbcWrapper<Balance>() {
@Override
protected void populateStatement(PreparedStatement preparedStatement) throws SQLException {
preparedStatement.setInt(1, t.getUniversityFiscalYear());
preparedStatement.setString(2, t.getChartOfAccountsCode());
preparedStatement.setString(3, t.getAccountNumber());
preparedStatement.setString(4, t.getSubAccountNumber());
preparedStatement.setString(5, t.getFinancialObjectCode());
preparedStatement.setString(6, t.getFinancialSubObjectCode());
preparedStatement.setString(7, t.getFinancialBalanceTypeCode());
preparedStatement.setString(8, t.getFinancialObjectTypeCode());
}
@Override
protected Balance extractResult(ResultSet resultSet) throws SQLException {
Balance balance = new Balance();
balance.setUniversityFiscalYear(t.getUniversityFiscalYear());
balance.setChartOfAccountsCode(t.getChartOfAccountsCode());
balance.setAccountNumber(t.getAccountNumber());
balance.setSubAccountNumber(t.getSubAccountNumber());
balance.setObjectCode(t.getFinancialObjectCode());
balance.setSubObjectCode(t.getFinancialSubObjectCode());
balance.setBalanceTypeCode(t.getFinancialBalanceTypeCode());
balance.setObjectTypeCode(t.getFinancialObjectTypeCode());
balance.setAccountLineAnnualBalanceAmount(new KualiDecimal(resultSet.getBigDecimal(1)));
balance.setBeginningBalanceLineAmount(new KualiDecimal(resultSet.getBigDecimal(2)));
balance.setContractsGrantsBeginningBalanceAmount(new KualiDecimal(resultSet.getBigDecimal(3)));
balance.setMonth1Amount(new KualiDecimal(resultSet.getBigDecimal(4)));
balance.setMonth2Amount(new KualiDecimal(resultSet.getBigDecimal(5)));
balance.setMonth3Amount(new KualiDecimal(resultSet.getBigDecimal(6)));
balance.setMonth4Amount(new KualiDecimal(resultSet.getBigDecimal(7)));
balance.setMonth5Amount(new KualiDecimal(resultSet.getBigDecimal(8)));
balance.setMonth6Amount(new KualiDecimal(resultSet.getBigDecimal(9)));
balance.setMonth7Amount(new KualiDecimal(resultSet.getBigDecimal(10)));
balance.setMonth8Amount(new KualiDecimal(resultSet.getBigDecimal(11)));
balance.setMonth9Amount(new KualiDecimal(resultSet.getBigDecimal(12)));
balance.setMonth10Amount(new KualiDecimal(resultSet.getBigDecimal(13)));
balance.setMonth11Amount(new KualiDecimal(resultSet.getBigDecimal(14)));
balance.setMonth12Amount(new KualiDecimal(resultSet.getBigDecimal(15)));
balance.setMonth13Amount(new KualiDecimal(resultSet.getBigDecimal(16)));
return balance;
}
}.get(Balance.class);
}
public void insertBalance(final Balance balance, final Timestamp currentTimestamp) {
new InsertingJdbcWrapper<Balance>() {
@Override
protected void populateStatement(PreparedStatement preparedStatement) throws SQLException {
preparedStatement.setInt(1, balance.getUniversityFiscalYear());
preparedStatement.setString(2, balance.getChartOfAccountsCode());
preparedStatement.setString(3, balance.getAccountNumber());
preparedStatement.setString(4, balance.getSubAccountNumber());
preparedStatement.setString(5, balance.getObjectCode());
preparedStatement.setString(6, balance.getSubObjectCode());
preparedStatement.setString(7, balance.getBalanceTypeCode());
preparedStatement.setString(8, balance.getObjectTypeCode());
preparedStatement.setBigDecimal(9, balance.getAccountLineAnnualBalanceAmount().bigDecimalValue());
preparedStatement.setBigDecimal(10, balance.getBeginningBalanceLineAmount().bigDecimalValue());
preparedStatement.setBigDecimal(11, balance.getContractsGrantsBeginningBalanceAmount().bigDecimalValue());
preparedStatement.setBigDecimal(12, balance.getMonth1Amount().bigDecimalValue());
preparedStatement.setBigDecimal(13, balance.getMonth2Amount().bigDecimalValue());
preparedStatement.setBigDecimal(14, balance.getMonth3Amount().bigDecimalValue());
preparedStatement.setBigDecimal(15, balance.getMonth4Amount().bigDecimalValue());
preparedStatement.setBigDecimal(16, balance.getMonth5Amount().bigDecimalValue());
preparedStatement.setBigDecimal(17, balance.getMonth6Amount().bigDecimalValue());
preparedStatement.setBigDecimal(18, balance.getMonth7Amount().bigDecimalValue());
preparedStatement.setBigDecimal(19, balance.getMonth8Amount().bigDecimalValue());
preparedStatement.setBigDecimal(20, balance.getMonth9Amount().bigDecimalValue());
preparedStatement.setBigDecimal(21, balance.getMonth10Amount().bigDecimalValue());
preparedStatement.setBigDecimal(22, balance.getMonth11Amount().bigDecimalValue());
preparedStatement.setBigDecimal(23, balance.getMonth12Amount().bigDecimalValue());
preparedStatement.setBigDecimal(24, balance.getMonth13Amount().bigDecimalValue());
preparedStatement.setTimestamp(25, currentTimestamp);
}
}.execute(Balance.class);
}
public void updateBalance(final Balance balance, final Timestamp currentTimestamp) {
new UpdatingJdbcWrapper<Balance>() {
@Override
protected void populateStatement(PreparedStatement preparedStatement) throws SQLException {
preparedStatement.setBigDecimal(1, balance.getAccountLineAnnualBalanceAmount().bigDecimalValue());
preparedStatement.setBigDecimal(2, balance.getBeginningBalanceLineAmount().bigDecimalValue());
preparedStatement.setBigDecimal(3, balance.getContractsGrantsBeginningBalanceAmount().bigDecimalValue());
preparedStatement.setBigDecimal(4, balance.getMonth1Amount().bigDecimalValue());
preparedStatement.setBigDecimal(5, balance.getMonth2Amount().bigDecimalValue());
preparedStatement.setBigDecimal(6, balance.getMonth3Amount().bigDecimalValue());
preparedStatement.setBigDecimal(7, balance.getMonth4Amount().bigDecimalValue());
preparedStatement.setBigDecimal(8, balance.getMonth5Amount().bigDecimalValue());
preparedStatement.setBigDecimal(9, balance.getMonth6Amount().bigDecimalValue());
preparedStatement.setBigDecimal(10, balance.getMonth7Amount().bigDecimalValue());
preparedStatement.setBigDecimal(11, balance.getMonth8Amount().bigDecimalValue());
preparedStatement.setBigDecimal(12, balance.getMonth9Amount().bigDecimalValue());
preparedStatement.setBigDecimal(13, balance.getMonth10Amount().bigDecimalValue());
preparedStatement.setBigDecimal(14, balance.getMonth11Amount().bigDecimalValue());
preparedStatement.setBigDecimal(15, balance.getMonth12Amount().bigDecimalValue());
preparedStatement.setBigDecimal(16, balance.getMonth13Amount().bigDecimalValue());
preparedStatement.setTimestamp(17, currentTimestamp);
preparedStatement.setInt(18, balance.getUniversityFiscalYear());
preparedStatement.setString(19, balance.getChartOfAccountsCode());
preparedStatement.setString(20, balance.getAccountNumber());
preparedStatement.setString(21, balance.getSubAccountNumber());
preparedStatement.setString(22, balance.getObjectCode());
preparedStatement.setString(23, balance.getSubObjectCode());
preparedStatement.setString(24, balance.getBalanceTypeCode());
preparedStatement.setString(25, balance.getObjectTypeCode());
}
}.execute(Balance.class);
}
public Encumbrance getEncumbrance(final Entry entry) {
return new RetrievingJdbcWrapper<Encumbrance>() {
@Override
protected void populateStatement(PreparedStatement preparedStatement) throws SQLException {
preparedStatement.setInt(1, entry.getUniversityFiscalYear());
preparedStatement.setString(2, entry.getChartOfAccountsCode());
preparedStatement.setString(3, entry.getAccountNumber());
preparedStatement.setString(4, entry.getSubAccountNumber());
preparedStatement.setString(5, entry.getFinancialObjectCode());
preparedStatement.setString(6, entry.getFinancialSubObjectCode());
preparedStatement.setString(7, entry.getFinancialBalanceTypeCode());
preparedStatement.setString(8, entry.getFinancialDocumentTypeCode());
preparedStatement.setString(9, entry.getFinancialSystemOriginationCode());
preparedStatement.setString(10, entry.getDocumentNumber());
}
@Override
protected Encumbrance extractResult(ResultSet resultSet) throws SQLException {
Encumbrance encumbrance = new Encumbrance();
encumbrance.setUniversityFiscalYear(entry.getUniversityFiscalYear());
encumbrance.setChartOfAccountsCode(entry.getChartOfAccountsCode());
encumbrance.setAccountNumber(entry.getAccountNumber());
encumbrance.setSubAccountNumber(entry.getSubAccountNumber());
encumbrance.setObjectCode(entry.getFinancialObjectCode());
encumbrance.setSubObjectCode(entry.getFinancialSubObjectCode());
encumbrance.setBalanceTypeCode(entry.getFinancialBalanceTypeCode());
encumbrance.setDocumentTypeCode(entry.getFinancialDocumentTypeCode());
encumbrance.setOriginCode(entry.getFinancialSystemOriginationCode());
encumbrance.setDocumentNumber(entry.getDocumentNumber());
encumbrance.setTransactionEncumbranceDescription(resultSet.getString(1));
encumbrance.setTransactionEncumbranceDate(resultSet.getDate(2));
encumbrance.setAccountLineEncumbranceAmount(new KualiDecimal(resultSet.getBigDecimal(3)));
encumbrance.setAccountLineEncumbranceClosedAmount(new KualiDecimal(resultSet.getBigDecimal(4)));
encumbrance.setAccountLineEncumbrancePurgeCode(resultSet.getString(5));
return encumbrance;
}
}.get(Encumbrance.class);
}
public void insertEncumbrance(final Encumbrance encumbrance, final Timestamp currentTimestamp) {
new InsertingJdbcWrapper<Encumbrance>() {
@Override
protected void populateStatement(PreparedStatement preparedStatement) throws SQLException {
preparedStatement.setInt(1, encumbrance.getUniversityFiscalYear());
preparedStatement.setString(2, encumbrance.getChartOfAccountsCode());
preparedStatement.setString(3, encumbrance.getAccountNumber());
preparedStatement.setString(4, encumbrance.getSubAccountNumber());
preparedStatement.setString(5, encumbrance.getObjectCode());
preparedStatement.setString(6, encumbrance.getSubObjectCode());
preparedStatement.setString(7, encumbrance.getBalanceTypeCode());
preparedStatement.setString(8, encumbrance.getDocumentTypeCode());
preparedStatement.setString(9, encumbrance.getOriginCode());
preparedStatement.setString(10, encumbrance.getDocumentNumber());
preparedStatement.setString(11, encumbrance.getTransactionEncumbranceDescription());
preparedStatement.setDate(12, encumbrance.getTransactionEncumbranceDate());
preparedStatement.setBigDecimal(13, encumbrance.getAccountLineEncumbranceAmount().bigDecimalValue());
preparedStatement.setBigDecimal(14, encumbrance.getAccountLineEncumbranceClosedAmount().bigDecimalValue());
preparedStatement.setString(15, encumbrance.getAccountLineEncumbrancePurgeCode());
preparedStatement.setTimestamp(16, currentTimestamp);
}
}.execute(Encumbrance.class);
}
public void updateEncumbrance(final Encumbrance encumbrance, final Timestamp currentTimestamp) {
new UpdatingJdbcWrapper<Encumbrance>() {
@Override
protected void populateStatement(PreparedStatement preparedStatement) throws SQLException {
preparedStatement.setString(1, encumbrance.getTransactionEncumbranceDescription());
preparedStatement.setDate(2, encumbrance.getTransactionEncumbranceDate());
preparedStatement.setBigDecimal(3, encumbrance.getAccountLineEncumbranceAmount().bigDecimalValue());
preparedStatement.setBigDecimal(4, encumbrance.getAccountLineEncumbranceClosedAmount().bigDecimalValue());
preparedStatement.setString(5, encumbrance.getAccountLineEncumbrancePurgeCode());
preparedStatement.setTimestamp(6, currentTimestamp);
preparedStatement.setInt(7, encumbrance.getUniversityFiscalYear());
preparedStatement.setString(8, encumbrance.getChartOfAccountsCode());
preparedStatement.setString(9, encumbrance.getAccountNumber());
preparedStatement.setString(10, encumbrance.getSubAccountNumber());
preparedStatement.setString(11, encumbrance.getObjectCode());
preparedStatement.setString(12, encumbrance.getSubObjectCode());
preparedStatement.setString(13, encumbrance.getBalanceTypeCode());
preparedStatement.setString(14, encumbrance.getDocumentTypeCode());
preparedStatement.setString(15, encumbrance.getOriginCode());
preparedStatement.setString(16, encumbrance.getDocumentNumber());
}
}.execute(Encumbrance.class);
}
public ExpenditureTransaction getExpenditureTransaction(final Transaction t) {
return new RetrievingJdbcWrapper<ExpenditureTransaction>() {
@Override
protected void populateStatement(PreparedStatement preparedStatement) throws SQLException {
preparedStatement.setInt(1, t.getUniversityFiscalYear());
preparedStatement.setString(2, t.getChartOfAccountsCode());
preparedStatement.setString(3, t.getAccountNumber());
preparedStatement.setString(4, t.getSubAccountNumber());
preparedStatement.setString(5, t.getFinancialObjectCode());
preparedStatement.setString(6, t.getFinancialSubObjectCode());
preparedStatement.setString(7, t.getFinancialBalanceTypeCode());
preparedStatement.setString(8, t.getFinancialObjectTypeCode());
preparedStatement.setString(9, t.getUniversityFiscalPeriodCode());
preparedStatement.setString(10, t.getProjectCode());
preparedStatement.setString(11, StringUtils.isBlank(t.getOrganizationReferenceId()) ? GeneralLedgerConstants.getDashOrganizationReferenceId() : t.getOrganizationReferenceId());
}
@Override
protected ExpenditureTransaction extractResult(ResultSet resultSet) throws SQLException {
ExpenditureTransaction expenditureTransaction = new ExpenditureTransaction();
expenditureTransaction.setUniversityFiscalYear(t.getUniversityFiscalYear());
expenditureTransaction.setChartOfAccountsCode(t.getChartOfAccountsCode());
expenditureTransaction.setAccountNumber(t.getAccountNumber());
expenditureTransaction.setSubAccountNumber(t.getSubAccountNumber());
expenditureTransaction.setObjectCode(t.getFinancialObjectCode());
expenditureTransaction.setSubObjectCode(t.getFinancialSubObjectCode());
expenditureTransaction.setBalanceTypeCode(t.getFinancialBalanceTypeCode());
expenditureTransaction.setObjectTypeCode(t.getFinancialObjectTypeCode());
expenditureTransaction.setUniversityFiscalAccountingPeriod(t.getUniversityFiscalPeriodCode());
expenditureTransaction.setProjectCode(t.getProjectCode());
expenditureTransaction.setOrganizationReferenceId(StringUtils.isBlank(t.getOrganizationReferenceId()) ? GeneralLedgerConstants.getDashOrganizationReferenceId() : t.getOrganizationReferenceId());
expenditureTransaction.setAccountObjectDirectCostAmount(new KualiDecimal(resultSet.getBigDecimal(1)));
return expenditureTransaction;
}
}.get(ExpenditureTransaction.class);
}
public void insertExpenditureTransaction(final ExpenditureTransaction expenditureTransaction) {
new InsertingJdbcWrapper<ExpenditureTransaction>() {
@Override
protected void populateStatement(PreparedStatement preparedStatement) throws SQLException {
preparedStatement.setInt(1, expenditureTransaction.getUniversityFiscalYear());
preparedStatement.setString(2, expenditureTransaction.getChartOfAccountsCode());
preparedStatement.setString(3, expenditureTransaction.getAccountNumber());
preparedStatement.setString(4, expenditureTransaction.getSubAccountNumber());
preparedStatement.setString(5, expenditureTransaction.getObjectCode());
preparedStatement.setString(6, expenditureTransaction.getSubObjectCode());
preparedStatement.setString(7, expenditureTransaction.getBalanceTypeCode());
preparedStatement.setString(8, expenditureTransaction.getObjectTypeCode());
preparedStatement.setString(9, expenditureTransaction.getUniversityFiscalAccountingPeriod());
preparedStatement.setString(10, expenditureTransaction.getProjectCode());
preparedStatement.setString(11, expenditureTransaction.getOrganizationReferenceId());
preparedStatement.setBigDecimal(12, expenditureTransaction.getAccountObjectDirectCostAmount().bigDecimalValue());
}
}.execute(ExpenditureTransaction.class);
}
public void updateExpenditureTransaction(final ExpenditureTransaction expenditureTransaction) {
new UpdatingJdbcWrapper<ExpenditureTransaction>() {
@Override
protected void populateStatement(PreparedStatement preparedStatement) throws SQLException {
preparedStatement.setBigDecimal(1, expenditureTransaction.getAccountObjectDirectCostAmount().bigDecimalValue());
preparedStatement.setInt(2, expenditureTransaction.getUniversityFiscalYear());
preparedStatement.setString(3, expenditureTransaction.getChartOfAccountsCode());
preparedStatement.setString(4, expenditureTransaction.getAccountNumber());
preparedStatement.setString(5, expenditureTransaction.getSubAccountNumber());
preparedStatement.setString(6, expenditureTransaction.getObjectCode());
preparedStatement.setString(7, expenditureTransaction.getSubObjectCode());
preparedStatement.setString(8, expenditureTransaction.getBalanceTypeCode());
preparedStatement.setString(9, expenditureTransaction.getObjectTypeCode());
preparedStatement.setString(10, expenditureTransaction.getUniversityFiscalAccountingPeriod());
preparedStatement.setString(11, expenditureTransaction.getProjectCode());
preparedStatement.setString(12, expenditureTransaction.getOrganizationReferenceId());
}
}.execute(ExpenditureTransaction.class);
}
public SufficientFundBalances getSufficientFundBalances(final Integer universityFiscalYear, final String chartOfAccountsCode, final String accountNumber, final String financialObjectCode) {
return new RetrievingJdbcWrapper<SufficientFundBalances>() {
@Override
protected void populateStatement(PreparedStatement preparedStatement) throws SQLException {
preparedStatement.setInt(1, universityFiscalYear);
preparedStatement.setString(2, chartOfAccountsCode);
preparedStatement.setString(3, accountNumber);
preparedStatement.setString(4, financialObjectCode);
}
@Override
protected SufficientFundBalances extractResult(ResultSet resultSet) throws SQLException {
SufficientFundBalances sufficientFundBalances = new SufficientFundBalances();
sufficientFundBalances.setUniversityFiscalYear(universityFiscalYear);
sufficientFundBalances.setChartOfAccountsCode(chartOfAccountsCode);
sufficientFundBalances.setAccountNumber(accountNumber);
sufficientFundBalances.setFinancialObjectCode(financialObjectCode);
sufficientFundBalances.setAccountSufficientFundsCode(resultSet.getString(1));
sufficientFundBalances.setCurrentBudgetBalanceAmount(new KualiDecimal(resultSet.getBigDecimal(2)));
sufficientFundBalances.setAccountActualExpenditureAmt(new KualiDecimal(resultSet.getBigDecimal(3)));
sufficientFundBalances.setAccountEncumbranceAmount(new KualiDecimal(resultSet.getBigDecimal(4)));
return sufficientFundBalances;
}
}.get(SufficientFundBalances.class);
}
public void insertSufficientFundBalances(final SufficientFundBalances sufficientFundBalances, final Timestamp currentTimestamp) {
new InsertingJdbcWrapper<SufficientFundBalances>() {
@Override
protected void populateStatement(PreparedStatement preparedStatement) throws SQLException {
preparedStatement.setInt(1, sufficientFundBalances.getUniversityFiscalYear());
preparedStatement.setString(2, sufficientFundBalances.getChartOfAccountsCode());
preparedStatement.setString(3, sufficientFundBalances.getAccountNumber());
preparedStatement.setString(4, sufficientFundBalances.getFinancialObjectCode());
preparedStatement.setString(5, sufficientFundBalances.getAccountSufficientFundsCode());
preparedStatement.setBigDecimal(6, sufficientFundBalances.getCurrentBudgetBalanceAmount().bigDecimalValue());
preparedStatement.setBigDecimal(7, sufficientFundBalances.getAccountActualExpenditureAmt().bigDecimalValue());
preparedStatement.setBigDecimal(8, sufficientFundBalances.getAccountEncumbranceAmount().bigDecimalValue());
preparedStatement.setTimestamp(9, currentTimestamp);
}
}.execute(SufficientFundBalances.class);
}
public void updateSufficientFundBalances(final SufficientFundBalances sufficientFundBalances, final Timestamp currentTimestamp) {
new UpdatingJdbcWrapper<SufficientFundBalances>() {
@Override
protected void populateStatement(PreparedStatement preparedStatement) throws SQLException {
preparedStatement.setString(1, sufficientFundBalances.getAccountSufficientFundsCode());
preparedStatement.setBigDecimal(2, sufficientFundBalances.getCurrentBudgetBalanceAmount().bigDecimalValue());
preparedStatement.setBigDecimal(3, sufficientFundBalances.getAccountActualExpenditureAmt().bigDecimalValue());
preparedStatement.setBigDecimal(4, sufficientFundBalances.getAccountEncumbranceAmount().bigDecimalValue());
preparedStatement.setTimestamp(5, currentTimestamp);
preparedStatement.setInt(6, sufficientFundBalances.getUniversityFiscalYear());
preparedStatement.setString(7, sufficientFundBalances.getChartOfAccountsCode());
preparedStatement.setString(8, sufficientFundBalances.getAccountNumber());
preparedStatement.setString(9, sufficientFundBalances.getFinancialObjectCode());
}
}.execute(SufficientFundBalances.class);
}
public void insertEntry(final Entry entry, final Timestamp currentTimestamp) {
new InsertingJdbcWrapper<Entry>() {
@Override
protected void populateStatement(PreparedStatement preparedStatement) throws SQLException {
preparedStatement.setInt(1, entry.getUniversityFiscalYear());
preparedStatement.setString(2, entry.getChartOfAccountsCode());
preparedStatement.setString(3, entry.getAccountNumber());
preparedStatement.setString(4, entry.getSubAccountNumber());
preparedStatement.setString(5, entry.getFinancialObjectCode());
preparedStatement.setString(6, entry.getFinancialSubObjectCode());
preparedStatement.setString(7, entry.getFinancialBalanceTypeCode());
preparedStatement.setString(8, entry.getFinancialObjectTypeCode());
preparedStatement.setString(9, entry.getUniversityFiscalPeriodCode());
preparedStatement.setString(10, entry.getFinancialDocumentTypeCode());
preparedStatement.setString(11, entry.getFinancialSystemOriginationCode());
preparedStatement.setString(12, entry.getDocumentNumber());
preparedStatement.setInt(13, entry.getTransactionLedgerEntrySequenceNumber());
preparedStatement.setString(14, entry.getTransactionLedgerEntryDescription());
preparedStatement.setBigDecimal(15, entry.getTransactionLedgerEntryAmount().bigDecimalValue());
preparedStatement.setString(16, entry.getTransactionDebitCreditCode());
preparedStatement.setDate(17, entry.getTransactionDate());
preparedStatement.setString(18, entry.getOrganizationDocumentNumber());
preparedStatement.setString(19, entry.getProjectCode());
preparedStatement.setString(20, entry.getOrganizationReferenceId());
preparedStatement.setString(21, entry.getReferenceFinancialDocumentTypeCode());
preparedStatement.setString(22, entry.getReferenceFinancialSystemOriginationCode());
preparedStatement.setString(23, entry.getReferenceFinancialDocumentNumber());
preparedStatement.setDate(24, entry.getFinancialDocumentReversalDate());
preparedStatement.setString(25, entry.getTransactionEncumbranceUpdateCode());
preparedStatement.setDate(26, entry.getTransactionPostingDate());
preparedStatement.setTimestamp(27, currentTimestamp);
}
}.execute(Entry.class);
}
public void insertReversal(final Reversal reversal) {
new InsertingJdbcWrapper<Reversal>() {
@Override
protected void populateStatement(PreparedStatement preparedStatement) throws SQLException {
preparedStatement.setDate(1, reversal.getFinancialDocumentReversalDate());
preparedStatement.setInt(2, reversal.getUniversityFiscalYear());
preparedStatement.setString(3, reversal.getChartOfAccountsCode());
preparedStatement.setString(4, reversal.getAccountNumber());
preparedStatement.setString(5, reversal.getSubAccountNumber());
preparedStatement.setString(6, reversal.getFinancialObjectCode());
preparedStatement.setString(7, reversal.getFinancialSubObjectCode());
preparedStatement.setString(8, reversal.getFinancialBalanceTypeCode());
preparedStatement.setString(9, reversal.getFinancialObjectTypeCode());
preparedStatement.setString(10, reversal.getUniversityFiscalPeriodCode());
preparedStatement.setString(11, reversal.getFinancialDocumentTypeCode());
preparedStatement.setString(12, reversal.getFinancialSystemOriginationCode());
preparedStatement.setString(13, reversal.getDocumentNumber());
preparedStatement.setInt(14, reversal.getTransactionLedgerEntrySequenceNumber());
preparedStatement.setString(15, reversal.getTransactionLedgerEntryDescription());
preparedStatement.setBigDecimal(16, reversal.getTransactionLedgerEntryAmount().bigDecimalValue());
preparedStatement.setString(17, reversal.getTransactionDebitCreditCode());
preparedStatement.setDate(18, reversal.getTransactionDate());
preparedStatement.setString(19, reversal.getOrganizationDocumentNumber());
preparedStatement.setString(20, reversal.getProjectCode());
preparedStatement.setString(21, reversal.getOrganizationReferenceId());
preparedStatement.setString(22, reversal.getReferenceFinancialDocumentTypeCode());
preparedStatement.setString(23, reversal.getReferenceFinancialSystemOriginationCode());
preparedStatement.setString(24, reversal.getReferenceFinancialDocumentNumber());
preparedStatement.setString(25, reversal.getTransactionEncumbranceUpdateCode());
preparedStatement.setDate(26, reversal.getTransactionPostingDate());
}
}.execute(Reversal.class);
}
}