/*
 * The Kuali Financial System, a comprehensive financial management system for higher education.
 *
 * Copyright 2005-2016 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 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;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.util.HashMap;
import java.util.Map;

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);
    }
}
