001 /*
002 * Copyright 2011 The Kuali Foundation.
003 *
004 * Licensed under the Educational Community License, Version 2.0 (the "License");
005 * you may not use this file except in compliance with the License.
006 * You may obtain a copy of the License at
007 *
008 * http://www.opensource.org/licenses/ecl2.php
009 *
010 * Unless required by applicable law or agreed to in writing, software
011 * distributed under the License is distributed on an "AS IS" BASIS,
012 * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
013 * See the License for the specific language governing permissions and
014 * limitations under the License.
015 */
016 package org.kuali.kfs.gl.batch.dataaccess.impl;
017
018 import java.sql.PreparedStatement;
019 import java.sql.ResultSet;
020 import java.sql.SQLException;
021 import java.util.HashMap;
022 import java.util.Map;
023
024 import org.apache.commons.lang.StringUtils;
025 import org.kuali.kfs.gl.GeneralLedgerConstants;
026 import org.kuali.kfs.gl.batch.dataaccess.LedgerPreparedStatementCachingDao;
027 import org.kuali.kfs.gl.businessobject.AccountBalance;
028 import org.kuali.kfs.gl.businessobject.Balance;
029 import org.kuali.kfs.gl.businessobject.Encumbrance;
030 import org.kuali.kfs.gl.businessobject.Entry;
031 import org.kuali.kfs.gl.businessobject.ExpenditureTransaction;
032 import org.kuali.kfs.gl.businessobject.Reversal;
033 import org.kuali.kfs.gl.businessobject.SufficientFundBalances;
034 import org.kuali.kfs.gl.businessobject.Transaction;
035 import org.kuali.kfs.sys.batch.dataaccess.impl.AbstractPreparedStatementCachingDaoJdbc;
036 import org.kuali.rice.kns.util.KualiDecimal;
037
038 public class LedgerPreparedStatementCachingDaoJdbc extends AbstractPreparedStatementCachingDaoJdbc implements LedgerPreparedStatementCachingDao {
039 static final Map<String,String> sql = new HashMap<String,String>();
040 static {
041 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 = ?");
042 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 = ?");
043 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 (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)");
044 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 = ?");
045 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 = ?");
046 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 (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)");
047 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 = ?");
048 sql.put(RETRIEVE_PREFIX + ExpenditureTransaction.class, "select ACCT_OBJ_DCST_AMT from GL_EXPEND_TRN_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 PROJECT_CD = ? and ORG_REFERENCE_ID = ?");
049 sql.put(INSERT_PREFIX + ExpenditureTransaction.class, "insert into GL_EXPEND_TRN_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, PROJECT_CD, ORG_REFERENCE_ID, ACCT_OBJ_DCST_AMT) values (?,?,?,?,?,?,?,?,?,?,?,?)");
050 sql.put(UPDATE_PREFIX + ExpenditureTransaction.class, "update GL_EXPEND_TRN_T 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 = ?");
051 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 = ?");
052 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 (?,?,?,?,?,?,?,?,?)");
053 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 = ?");
054 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 = ?");
055 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 (?,?,?,?,?,?,?,?,?,?)");
056 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 = ?");
057 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 (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)");
058 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 (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)");
059 }
060
061 @Override
062 protected Map<String, String> getSql() {
063 return sql;
064 }
065
066 public int getMaxSequenceNumber(final Transaction t) {
067 return new RetrievingJdbcWrapper<Integer>() {
068 @Override
069 protected void populateStatement(PreparedStatement preparedStatement) throws SQLException {
070 preparedStatement.setInt(1, t.getUniversityFiscalYear());
071 preparedStatement.setString(2, t.getChartOfAccountsCode());
072 preparedStatement.setString(3, t.getAccountNumber());
073 preparedStatement.setString(4, t.getSubAccountNumber());
074 preparedStatement.setString(5, t.getFinancialObjectCode());
075 preparedStatement.setString(6, t.getFinancialSubObjectCode());
076 preparedStatement.setString(7, t.getFinancialBalanceTypeCode());
077 preparedStatement.setString(8, t.getFinancialObjectTypeCode());
078 preparedStatement.setString(9, t.getUniversityFiscalPeriodCode());
079 preparedStatement.setString(10, t.getFinancialDocumentTypeCode());
080 preparedStatement.setString(11, t.getFinancialSystemOriginationCode());
081 preparedStatement.setString(12, t.getDocumentNumber());
082 }
083 @Override
084 protected Integer extractResult(ResultSet resultSet) throws SQLException {
085 return resultSet.getInt(1);
086 }
087 }.get(Integer.class);
088 }
089
090 public AccountBalance getAccountBalance(final Transaction t) {
091 return new RetrievingJdbcWrapper<AccountBalance>() {
092 @Override
093 protected void populateStatement(PreparedStatement preparedStatement) throws SQLException {
094 preparedStatement.setInt(1, t.getUniversityFiscalYear());
095 preparedStatement.setString(2, t.getChartOfAccountsCode());
096 preparedStatement.setString(3, t.getAccountNumber());
097 preparedStatement.setString(4, t.getSubAccountNumber());
098 preparedStatement.setString(5, t.getFinancialObjectCode());
099 preparedStatement.setString(6, t.getFinancialSubObjectCode());
100 }
101 @Override
102 protected AccountBalance extractResult(ResultSet resultSet) throws SQLException {
103 AccountBalance accountBalance = new AccountBalance();
104 accountBalance.setUniversityFiscalYear(t.getUniversityFiscalYear());
105 accountBalance.setChartOfAccountsCode(t.getChartOfAccountsCode());
106 accountBalance.setAccountNumber(t.getAccountNumber());
107 accountBalance.setSubAccountNumber(t.getSubAccountNumber());
108 accountBalance.setObjectCode(t.getFinancialObjectCode());
109 accountBalance.setSubObjectCode(t.getFinancialSubObjectCode());
110 accountBalance.setCurrentBudgetLineBalanceAmount(new KualiDecimal(resultSet.getBigDecimal(1)));
111 accountBalance.setAccountLineActualsBalanceAmount(new KualiDecimal(resultSet.getBigDecimal(2)));
112 accountBalance.setAccountLineEncumbranceBalanceAmount(new KualiDecimal(resultSet.getBigDecimal(3)));
113 return accountBalance;
114 }
115 }.get(AccountBalance.class);
116 }
117
118 public void insertAccountBalance(final AccountBalance accountBalance) {
119 new InsertingJdbcWrapper<AccountBalance>() {
120 @Override
121 protected void populateStatement(PreparedStatement preparedStatement) throws SQLException {
122 preparedStatement.setInt(1, accountBalance.getUniversityFiscalYear());
123 preparedStatement.setString(2, accountBalance.getChartOfAccountsCode());
124 preparedStatement.setString(3, accountBalance.getAccountNumber());
125 preparedStatement.setString(4, accountBalance.getSubAccountNumber());
126 preparedStatement.setString(5, accountBalance.getObjectCode());
127 preparedStatement.setString(6, accountBalance.getSubObjectCode());
128 preparedStatement.setBigDecimal(7, accountBalance.getCurrentBudgetLineBalanceAmount().bigDecimalValue());
129 preparedStatement.setBigDecimal(8, accountBalance.getAccountLineActualsBalanceAmount().bigDecimalValue());
130 preparedStatement.setBigDecimal(9, accountBalance.getAccountLineEncumbranceBalanceAmount().bigDecimalValue());
131 preparedStatement.setTimestamp(10, dateTimeService.getCurrentTimestamp());
132 }
133 }.execute(AccountBalance.class);
134 }
135
136 public void updateAccountBalance(final AccountBalance accountBalance) {
137 new UpdatingJdbcWrapper<AccountBalance>() {
138 @Override
139 protected void populateStatement(PreparedStatement preparedStatement) throws SQLException {
140 preparedStatement.setBigDecimal(1, accountBalance.getCurrentBudgetLineBalanceAmount().bigDecimalValue());
141 preparedStatement.setBigDecimal(2, accountBalance.getAccountLineActualsBalanceAmount().bigDecimalValue());
142 preparedStatement.setBigDecimal(3, accountBalance.getAccountLineEncumbranceBalanceAmount().bigDecimalValue());
143 preparedStatement.setTimestamp(4, dateTimeService.getCurrentTimestamp());
144 preparedStatement.setInt(5, accountBalance.getUniversityFiscalYear());
145 preparedStatement.setString(6, accountBalance.getChartOfAccountsCode());
146 preparedStatement.setString(7, accountBalance.getAccountNumber());
147 preparedStatement.setString(8, accountBalance.getSubAccountNumber());
148 preparedStatement.setString(9, accountBalance.getObjectCode());
149 preparedStatement.setString(10, accountBalance.getSubObjectCode());
150 }
151 }.execute(AccountBalance.class);
152 }
153
154 public Balance getBalance(final Transaction t) {
155 return new RetrievingJdbcWrapper<Balance>() {
156 @Override
157 protected void populateStatement(PreparedStatement preparedStatement) throws SQLException {
158 preparedStatement.setInt(1, t.getUniversityFiscalYear());
159 preparedStatement.setString(2, t.getChartOfAccountsCode());
160 preparedStatement.setString(3, t.getAccountNumber());
161 preparedStatement.setString(4, t.getSubAccountNumber());
162 preparedStatement.setString(5, t.getFinancialObjectCode());
163 preparedStatement.setString(6, t.getFinancialSubObjectCode());
164 preparedStatement.setString(7, t.getFinancialBalanceTypeCode());
165 preparedStatement.setString(8, t.getFinancialObjectTypeCode());
166 }
167 @Override
168 protected Balance extractResult(ResultSet resultSet) throws SQLException {
169 Balance balance = new Balance();
170 balance.setUniversityFiscalYear(t.getUniversityFiscalYear());
171 balance.setChartOfAccountsCode(t.getChartOfAccountsCode());
172 balance.setAccountNumber(t.getAccountNumber());
173 balance.setSubAccountNumber(t.getSubAccountNumber());
174 balance.setObjectCode(t.getFinancialObjectCode());
175 balance.setSubObjectCode(t.getFinancialSubObjectCode());
176 balance.setBalanceTypeCode(t.getFinancialBalanceTypeCode());
177 balance.setObjectTypeCode(t.getFinancialObjectTypeCode());
178 balance.setAccountLineAnnualBalanceAmount(new KualiDecimal(resultSet.getBigDecimal(1)));
179 balance.setBeginningBalanceLineAmount(new KualiDecimal(resultSet.getBigDecimal(2)));
180 balance.setContractsGrantsBeginningBalanceAmount(new KualiDecimal(resultSet.getBigDecimal(3)));
181 balance.setMonth1Amount(new KualiDecimal(resultSet.getBigDecimal(4)));
182 balance.setMonth2Amount(new KualiDecimal(resultSet.getBigDecimal(5)));
183 balance.setMonth3Amount(new KualiDecimal(resultSet.getBigDecimal(6)));
184 balance.setMonth4Amount(new KualiDecimal(resultSet.getBigDecimal(7)));
185 balance.setMonth5Amount(new KualiDecimal(resultSet.getBigDecimal(8)));
186 balance.setMonth6Amount(new KualiDecimal(resultSet.getBigDecimal(9)));
187 balance.setMonth7Amount(new KualiDecimal(resultSet.getBigDecimal(10)));
188 balance.setMonth8Amount(new KualiDecimal(resultSet.getBigDecimal(11)));
189 balance.setMonth9Amount(new KualiDecimal(resultSet.getBigDecimal(12)));
190 balance.setMonth10Amount(new KualiDecimal(resultSet.getBigDecimal(13)));
191 balance.setMonth11Amount(new KualiDecimal(resultSet.getBigDecimal(14)));
192 balance.setMonth12Amount(new KualiDecimal(resultSet.getBigDecimal(15)));
193 balance.setMonth13Amount(new KualiDecimal(resultSet.getBigDecimal(16)));
194 return balance;
195 }
196 }.get(Balance.class);
197 }
198
199 public void insertBalance(final Balance balance) {
200 new InsertingJdbcWrapper<Balance>() {
201 @Override
202 protected void populateStatement(PreparedStatement preparedStatement) throws SQLException {
203 preparedStatement.setInt(1, balance.getUniversityFiscalYear());
204 preparedStatement.setString(2, balance.getChartOfAccountsCode());
205 preparedStatement.setString(3, balance.getAccountNumber());
206 preparedStatement.setString(4, balance.getSubAccountNumber());
207 preparedStatement.setString(5, balance.getObjectCode());
208 preparedStatement.setString(6, balance.getSubObjectCode());
209 preparedStatement.setString(7, balance.getBalanceTypeCode());
210 preparedStatement.setString(8, balance.getObjectTypeCode());
211 preparedStatement.setBigDecimal(9, balance.getAccountLineAnnualBalanceAmount().bigDecimalValue());
212 preparedStatement.setBigDecimal(10, balance.getBeginningBalanceLineAmount().bigDecimalValue());
213 preparedStatement.setBigDecimal(11, balance.getContractsGrantsBeginningBalanceAmount().bigDecimalValue());
214 preparedStatement.setBigDecimal(12, balance.getMonth1Amount().bigDecimalValue());
215 preparedStatement.setBigDecimal(13, balance.getMonth2Amount().bigDecimalValue());
216 preparedStatement.setBigDecimal(14, balance.getMonth3Amount().bigDecimalValue());
217 preparedStatement.setBigDecimal(15, balance.getMonth4Amount().bigDecimalValue());
218 preparedStatement.setBigDecimal(16, balance.getMonth5Amount().bigDecimalValue());
219 preparedStatement.setBigDecimal(17, balance.getMonth6Amount().bigDecimalValue());
220 preparedStatement.setBigDecimal(18, balance.getMonth7Amount().bigDecimalValue());
221 preparedStatement.setBigDecimal(19, balance.getMonth8Amount().bigDecimalValue());
222 preparedStatement.setBigDecimal(20, balance.getMonth9Amount().bigDecimalValue());
223 preparedStatement.setBigDecimal(21, balance.getMonth10Amount().bigDecimalValue());
224 preparedStatement.setBigDecimal(22, balance.getMonth11Amount().bigDecimalValue());
225 preparedStatement.setBigDecimal(23, balance.getMonth12Amount().bigDecimalValue());
226 preparedStatement.setBigDecimal(24, balance.getMonth13Amount().bigDecimalValue());
227 preparedStatement.setTimestamp(25, dateTimeService.getCurrentTimestamp());
228 }
229 }.execute(Balance.class);
230 }
231
232 public void updateBalance(final Balance balance) {
233 new UpdatingJdbcWrapper<Balance>() {
234 @Override
235 protected void populateStatement(PreparedStatement preparedStatement) throws SQLException {
236 preparedStatement.setBigDecimal(1, balance.getAccountLineAnnualBalanceAmount().bigDecimalValue());
237 preparedStatement.setBigDecimal(2, balance.getBeginningBalanceLineAmount().bigDecimalValue());
238 preparedStatement.setBigDecimal(3, balance.getContractsGrantsBeginningBalanceAmount().bigDecimalValue());
239 preparedStatement.setBigDecimal(4, balance.getMonth1Amount().bigDecimalValue());
240 preparedStatement.setBigDecimal(5, balance.getMonth2Amount().bigDecimalValue());
241 preparedStatement.setBigDecimal(6, balance.getMonth3Amount().bigDecimalValue());
242 preparedStatement.setBigDecimal(7, balance.getMonth4Amount().bigDecimalValue());
243 preparedStatement.setBigDecimal(8, balance.getMonth5Amount().bigDecimalValue());
244 preparedStatement.setBigDecimal(9, balance.getMonth6Amount().bigDecimalValue());
245 preparedStatement.setBigDecimal(10, balance.getMonth7Amount().bigDecimalValue());
246 preparedStatement.setBigDecimal(11, balance.getMonth8Amount().bigDecimalValue());
247 preparedStatement.setBigDecimal(12, balance.getMonth9Amount().bigDecimalValue());
248 preparedStatement.setBigDecimal(13, balance.getMonth10Amount().bigDecimalValue());
249 preparedStatement.setBigDecimal(14, balance.getMonth11Amount().bigDecimalValue());
250 preparedStatement.setBigDecimal(15, balance.getMonth12Amount().bigDecimalValue());
251 preparedStatement.setBigDecimal(16, balance.getMonth13Amount().bigDecimalValue());
252 preparedStatement.setTimestamp(17, dateTimeService.getCurrentTimestamp());
253 preparedStatement.setInt(18, balance.getUniversityFiscalYear());
254 preparedStatement.setString(19, balance.getChartOfAccountsCode());
255 preparedStatement.setString(20, balance.getAccountNumber());
256 preparedStatement.setString(21, balance.getSubAccountNumber());
257 preparedStatement.setString(22, balance.getObjectCode());
258 preparedStatement.setString(23, balance.getSubObjectCode());
259 preparedStatement.setString(24, balance.getBalanceTypeCode());
260 preparedStatement.setString(25, balance.getObjectTypeCode());
261 }
262 }.execute(Balance.class);
263 }
264
265 public Encumbrance getEncumbrance(final Entry entry) {
266 return new RetrievingJdbcWrapper<Encumbrance>() {
267 @Override
268 protected void populateStatement(PreparedStatement preparedStatement) throws SQLException {
269 preparedStatement.setInt(1, entry.getUniversityFiscalYear());
270 preparedStatement.setString(2, entry.getChartOfAccountsCode());
271 preparedStatement.setString(3, entry.getAccountNumber());
272 preparedStatement.setString(4, entry.getSubAccountNumber());
273 preparedStatement.setString(5, entry.getFinancialObjectCode());
274 preparedStatement.setString(6, entry.getFinancialSubObjectCode());
275 preparedStatement.setString(7, entry.getFinancialBalanceTypeCode());
276 preparedStatement.setString(8, entry.getFinancialDocumentTypeCode());
277 preparedStatement.setString(9, entry.getFinancialSystemOriginationCode());
278 preparedStatement.setString(10, entry.getDocumentNumber());
279 }
280 @Override
281 protected Encumbrance extractResult(ResultSet resultSet) throws SQLException {
282 Encumbrance encumbrance = new Encumbrance();
283 encumbrance.setUniversityFiscalYear(entry.getUniversityFiscalYear());
284 encumbrance.setChartOfAccountsCode(entry.getChartOfAccountsCode());
285 encumbrance.setAccountNumber(entry.getAccountNumber());
286 encumbrance.setSubAccountNumber(entry.getSubAccountNumber());
287 encumbrance.setObjectCode(entry.getFinancialObjectCode());
288 encumbrance.setSubObjectCode(entry.getFinancialSubObjectCode());
289 encumbrance.setBalanceTypeCode(entry.getFinancialBalanceTypeCode());
290 encumbrance.setDocumentTypeCode(entry.getFinancialDocumentTypeCode());
291 encumbrance.setOriginCode(entry.getFinancialSystemOriginationCode());
292 encumbrance.setDocumentNumber(entry.getDocumentNumber());
293 encumbrance.setTransactionEncumbranceDescription(resultSet.getString(1));
294 encumbrance.setTransactionEncumbranceDate(resultSet.getDate(2));
295 encumbrance.setAccountLineEncumbranceAmount(new KualiDecimal(resultSet.getBigDecimal(3)));
296 encumbrance.setAccountLineEncumbranceClosedAmount(new KualiDecimal(resultSet.getBigDecimal(4)));
297 encumbrance.setAccountLineEncumbrancePurgeCode(resultSet.getString(5));
298 return encumbrance;
299 }
300 }.get(Encumbrance.class);
301 }
302
303 public void insertEncumbrance(final Encumbrance encumbrance) {
304 new InsertingJdbcWrapper<Encumbrance>() {
305 @Override
306 protected void populateStatement(PreparedStatement preparedStatement) throws SQLException {
307 preparedStatement.setInt(1, encumbrance.getUniversityFiscalYear());
308 preparedStatement.setString(2, encumbrance.getChartOfAccountsCode());
309 preparedStatement.setString(3, encumbrance.getAccountNumber());
310 preparedStatement.setString(4, encumbrance.getSubAccountNumber());
311 preparedStatement.setString(5, encumbrance.getObjectCode());
312 preparedStatement.setString(6, encumbrance.getSubObjectCode());
313 preparedStatement.setString(7, encumbrance.getBalanceTypeCode());
314 preparedStatement.setString(8, encumbrance.getDocumentTypeCode());
315 preparedStatement.setString(9, encumbrance.getOriginCode());
316 preparedStatement.setString(10, encumbrance.getDocumentNumber());
317 preparedStatement.setString(11, encumbrance.getTransactionEncumbranceDescription());
318 preparedStatement.setDate(12, encumbrance.getTransactionEncumbranceDate());
319 preparedStatement.setBigDecimal(13, encumbrance.getAccountLineEncumbranceAmount().bigDecimalValue());
320 preparedStatement.setBigDecimal(14, encumbrance.getAccountLineEncumbranceClosedAmount().bigDecimalValue());
321 preparedStatement.setString(15, encumbrance.getAccountLineEncumbrancePurgeCode());
322 preparedStatement.setTimestamp(16, dateTimeService.getCurrentTimestamp());
323 }
324 }.execute(Encumbrance.class);
325 }
326
327 public void updateEncumbrance(final Encumbrance encumbrance) {
328 new UpdatingJdbcWrapper<Encumbrance>() {
329 @Override
330 protected void populateStatement(PreparedStatement preparedStatement) throws SQLException {
331 preparedStatement.setString(1, encumbrance.getTransactionEncumbranceDescription());
332 preparedStatement.setDate(2, encumbrance.getTransactionEncumbranceDate());
333 preparedStatement.setBigDecimal(3, encumbrance.getAccountLineEncumbranceAmount().bigDecimalValue());
334 preparedStatement.setBigDecimal(4, encumbrance.getAccountLineEncumbranceClosedAmount().bigDecimalValue());
335 preparedStatement.setString(5, encumbrance.getAccountLineEncumbrancePurgeCode());
336 preparedStatement.setTimestamp(6, dateTimeService.getCurrentTimestamp());
337 preparedStatement.setInt(7, encumbrance.getUniversityFiscalYear());
338 preparedStatement.setString(8, encumbrance.getChartOfAccountsCode());
339 preparedStatement.setString(9, encumbrance.getAccountNumber());
340 preparedStatement.setString(10, encumbrance.getSubAccountNumber());
341 preparedStatement.setString(11, encumbrance.getObjectCode());
342 preparedStatement.setString(12, encumbrance.getSubObjectCode());
343 preparedStatement.setString(13, encumbrance.getBalanceTypeCode());
344 preparedStatement.setString(14, encumbrance.getDocumentTypeCode());
345 preparedStatement.setString(15, encumbrance.getOriginCode());
346 preparedStatement.setString(16, encumbrance.getDocumentNumber());
347 }
348 }.execute(Encumbrance.class);
349 }
350
351 public ExpenditureTransaction getExpenditureTransaction(final Transaction t) {
352 return new RetrievingJdbcWrapper<ExpenditureTransaction>() {
353 @Override
354 protected void populateStatement(PreparedStatement preparedStatement) throws SQLException {
355 preparedStatement.setInt(1, t.getUniversityFiscalYear());
356 preparedStatement.setString(2, t.getChartOfAccountsCode());
357 preparedStatement.setString(3, t.getAccountNumber());
358 preparedStatement.setString(4, t.getSubAccountNumber());
359 preparedStatement.setString(5, t.getFinancialObjectCode());
360 preparedStatement.setString(6, t.getFinancialSubObjectCode());
361 preparedStatement.setString(7, t.getFinancialBalanceTypeCode());
362 preparedStatement.setString(8, t.getFinancialObjectTypeCode());
363 preparedStatement.setString(9, t.getUniversityFiscalPeriodCode());
364 preparedStatement.setString(10, t.getProjectCode());
365 preparedStatement.setString(11, StringUtils.isBlank(t.getOrganizationReferenceId()) ? GeneralLedgerConstants.getDashOrganizationReferenceId() : t.getOrganizationReferenceId());
366 }
367 @Override
368 protected ExpenditureTransaction extractResult(ResultSet resultSet) throws SQLException {
369 ExpenditureTransaction expenditureTransaction = new ExpenditureTransaction();
370 expenditureTransaction.setUniversityFiscalYear(t.getUniversityFiscalYear());
371 expenditureTransaction.setChartOfAccountsCode(t.getChartOfAccountsCode());
372 expenditureTransaction.setAccountNumber(t.getAccountNumber());
373 expenditureTransaction.setSubAccountNumber(t.getSubAccountNumber());
374 expenditureTransaction.setObjectCode(t.getFinancialObjectCode());
375 expenditureTransaction.setSubObjectCode(t.getFinancialSubObjectCode());
376 expenditureTransaction.setBalanceTypeCode(t.getFinancialBalanceTypeCode());
377 expenditureTransaction.setObjectTypeCode(t.getFinancialObjectTypeCode());
378 expenditureTransaction.setUniversityFiscalAccountingPeriod(t.getUniversityFiscalPeriodCode());
379 expenditureTransaction.setProjectCode(t.getProjectCode());
380 expenditureTransaction.setOrganizationReferenceId(StringUtils.isBlank(t.getOrganizationReferenceId()) ? GeneralLedgerConstants.getDashOrganizationReferenceId() : t.getOrganizationReferenceId());
381 expenditureTransaction.setAccountObjectDirectCostAmount(new KualiDecimal(resultSet.getBigDecimal(1)));
382 return expenditureTransaction;
383 }
384 }.get(ExpenditureTransaction.class);
385 }
386
387 public void insertExpenditureTransaction(final ExpenditureTransaction expenditureTransaction) {
388 new InsertingJdbcWrapper<ExpenditureTransaction>() {
389 @Override
390 protected void populateStatement(PreparedStatement preparedStatement) throws SQLException {
391 preparedStatement.setInt(1, expenditureTransaction.getUniversityFiscalYear());
392 preparedStatement.setString(2, expenditureTransaction.getChartOfAccountsCode());
393 preparedStatement.setString(3, expenditureTransaction.getAccountNumber());
394 preparedStatement.setString(4, expenditureTransaction.getSubAccountNumber());
395 preparedStatement.setString(5, expenditureTransaction.getObjectCode());
396 preparedStatement.setString(6, expenditureTransaction.getSubObjectCode());
397 preparedStatement.setString(7, expenditureTransaction.getBalanceTypeCode());
398 preparedStatement.setString(8, expenditureTransaction.getObjectTypeCode());
399 preparedStatement.setString(9, expenditureTransaction.getUniversityFiscalAccountingPeriod());
400 preparedStatement.setString(10, expenditureTransaction.getProjectCode());
401 preparedStatement.setString(11, expenditureTransaction.getOrganizationReferenceId());
402 preparedStatement.setBigDecimal(12, expenditureTransaction.getAccountObjectDirectCostAmount().bigDecimalValue());
403 }
404 }.execute(ExpenditureTransaction.class);
405 }
406
407 public void updateExpenditureTransaction(final ExpenditureTransaction expenditureTransaction) {
408 new UpdatingJdbcWrapper<ExpenditureTransaction>() {
409 @Override
410 protected void populateStatement(PreparedStatement preparedStatement) throws SQLException {
411 preparedStatement.setBigDecimal(1, expenditureTransaction.getAccountObjectDirectCostAmount().bigDecimalValue());
412 preparedStatement.setInt(2, expenditureTransaction.getUniversityFiscalYear());
413 preparedStatement.setString(3, expenditureTransaction.getChartOfAccountsCode());
414 preparedStatement.setString(4, expenditureTransaction.getAccountNumber());
415 preparedStatement.setString(5, expenditureTransaction.getSubAccountNumber());
416 preparedStatement.setString(6, expenditureTransaction.getObjectCode());
417 preparedStatement.setString(7, expenditureTransaction.getSubObjectCode());
418 preparedStatement.setString(8, expenditureTransaction.getBalanceTypeCode());
419 preparedStatement.setString(9, expenditureTransaction.getObjectTypeCode());
420 preparedStatement.setString(10, expenditureTransaction.getUniversityFiscalAccountingPeriod());
421 preparedStatement.setString(11, expenditureTransaction.getProjectCode());
422 preparedStatement.setString(12, expenditureTransaction.getOrganizationReferenceId());
423 }
424 }.execute(ExpenditureTransaction.class);
425 }
426
427 public SufficientFundBalances getSufficientFundBalances(final Integer universityFiscalYear, final String chartOfAccountsCode, final String accountNumber, final String financialObjectCode) {
428 return new RetrievingJdbcWrapper<SufficientFundBalances>() {
429 @Override
430 protected void populateStatement(PreparedStatement preparedStatement) throws SQLException {
431 preparedStatement.setInt(1, universityFiscalYear);
432 preparedStatement.setString(2, chartOfAccountsCode);
433 preparedStatement.setString(3, accountNumber);
434 preparedStatement.setString(4, financialObjectCode);
435 }
436 @Override
437 protected SufficientFundBalances extractResult(ResultSet resultSet) throws SQLException {
438 SufficientFundBalances sufficientFundBalances = new SufficientFundBalances();
439 sufficientFundBalances.setUniversityFiscalYear(universityFiscalYear);
440 sufficientFundBalances.setChartOfAccountsCode(chartOfAccountsCode);
441 sufficientFundBalances.setAccountNumber(accountNumber);
442 sufficientFundBalances.setFinancialObjectCode(financialObjectCode);
443 sufficientFundBalances.setAccountSufficientFundsCode(resultSet.getString(1));
444 sufficientFundBalances.setCurrentBudgetBalanceAmount(new KualiDecimal(resultSet.getBigDecimal(2)));
445 sufficientFundBalances.setAccountActualExpenditureAmt(new KualiDecimal(resultSet.getBigDecimal(3)));
446 sufficientFundBalances.setAccountEncumbranceAmount(new KualiDecimal(resultSet.getBigDecimal(4)));
447 return sufficientFundBalances;
448 }
449 }.get(SufficientFundBalances.class);
450 }
451
452 public void insertSufficientFundBalances(final SufficientFundBalances sufficientFundBalances) {
453 new InsertingJdbcWrapper<SufficientFundBalances>() {
454 @Override
455 protected void populateStatement(PreparedStatement preparedStatement) throws SQLException {
456 preparedStatement.setInt(1, sufficientFundBalances.getUniversityFiscalYear());
457 preparedStatement.setString(2, sufficientFundBalances.getChartOfAccountsCode());
458 preparedStatement.setString(3, sufficientFundBalances.getAccountNumber());
459 preparedStatement.setString(4, sufficientFundBalances.getFinancialObjectCode());
460 preparedStatement.setString(5, sufficientFundBalances.getAccountSufficientFundsCode());
461 preparedStatement.setBigDecimal(6, sufficientFundBalances.getCurrentBudgetBalanceAmount().bigDecimalValue());
462 preparedStatement.setBigDecimal(7, sufficientFundBalances.getAccountActualExpenditureAmt().bigDecimalValue());
463 preparedStatement.setBigDecimal(8, sufficientFundBalances.getAccountEncumbranceAmount().bigDecimalValue());
464 preparedStatement.setTimestamp(9, dateTimeService.getCurrentTimestamp());
465 }
466 }.execute(SufficientFundBalances.class);
467 }
468
469 public void updateSufficientFundBalances(final SufficientFundBalances sufficientFundBalances) {
470 new UpdatingJdbcWrapper<SufficientFundBalances>() {
471 @Override
472 protected void populateStatement(PreparedStatement preparedStatement) throws SQLException {
473 preparedStatement.setString(1, sufficientFundBalances.getAccountSufficientFundsCode());
474 preparedStatement.setBigDecimal(2, sufficientFundBalances.getCurrentBudgetBalanceAmount().bigDecimalValue());
475 preparedStatement.setBigDecimal(3, sufficientFundBalances.getAccountActualExpenditureAmt().bigDecimalValue());
476 preparedStatement.setBigDecimal(4, sufficientFundBalances.getAccountEncumbranceAmount().bigDecimalValue());
477 preparedStatement.setTimestamp(5, dateTimeService.getCurrentTimestamp());
478 preparedStatement.setInt(6, sufficientFundBalances.getUniversityFiscalYear());
479 preparedStatement.setString(7, sufficientFundBalances.getChartOfAccountsCode());
480 preparedStatement.setString(8, sufficientFundBalances.getAccountNumber());
481 preparedStatement.setString(9, sufficientFundBalances.getFinancialObjectCode());
482 }
483 }.execute(SufficientFundBalances.class);
484 }
485
486 public void insertEntry(final Entry entry) {
487 new InsertingJdbcWrapper<Entry>() {
488 @Override
489 protected void populateStatement(PreparedStatement preparedStatement) throws SQLException {
490 preparedStatement.setInt(1, entry.getUniversityFiscalYear());
491 preparedStatement.setString(2, entry.getChartOfAccountsCode());
492 preparedStatement.setString(3, entry.getAccountNumber());
493 preparedStatement.setString(4, entry.getSubAccountNumber());
494 preparedStatement.setString(5, entry.getFinancialObjectCode());
495 preparedStatement.setString(6, entry.getFinancialSubObjectCode());
496 preparedStatement.setString(7, entry.getFinancialBalanceTypeCode());
497 preparedStatement.setString(8, entry.getFinancialObjectTypeCode());
498 preparedStatement.setString(9, entry.getUniversityFiscalPeriodCode());
499 preparedStatement.setString(10, entry.getFinancialDocumentTypeCode());
500 preparedStatement.setString(11, entry.getFinancialSystemOriginationCode());
501 preparedStatement.setString(12, entry.getDocumentNumber());
502 preparedStatement.setInt(13, entry.getTransactionLedgerEntrySequenceNumber());
503 preparedStatement.setString(14, entry.getTransactionLedgerEntryDescription());
504 preparedStatement.setBigDecimal(15, entry.getTransactionLedgerEntryAmount().bigDecimalValue());
505 preparedStatement.setString(16, entry.getTransactionDebitCreditCode());
506 preparedStatement.setDate(17, entry.getTransactionDate());
507 preparedStatement.setString(18, entry.getOrganizationDocumentNumber());
508 preparedStatement.setString(19, entry.getProjectCode());
509 preparedStatement.setString(20, entry.getOrganizationReferenceId());
510 preparedStatement.setString(21, entry.getReferenceFinancialDocumentTypeCode());
511 preparedStatement.setString(22, entry.getReferenceFinancialSystemOriginationCode());
512 preparedStatement.setString(23, entry.getReferenceFinancialDocumentNumber());
513 preparedStatement.setDate(24, entry.getFinancialDocumentReversalDate());
514 preparedStatement.setString(25, entry.getTransactionEncumbranceUpdateCode());
515 preparedStatement.setDate(26, entry.getTransactionPostingDate());
516 preparedStatement.setTimestamp(27, dateTimeService.getCurrentTimestamp());
517 }
518 }.execute(Entry.class);
519 }
520 public void insertReversal(final Reversal reversal) {
521 new InsertingJdbcWrapper<Reversal>() {
522 @Override
523 protected void populateStatement(PreparedStatement preparedStatement) throws SQLException {
524 preparedStatement.setDate(1, reversal.getFinancialDocumentReversalDate());
525 preparedStatement.setInt(2, reversal.getUniversityFiscalYear());
526 preparedStatement.setString(3, reversal.getChartOfAccountsCode());
527 preparedStatement.setString(4, reversal.getAccountNumber());
528 preparedStatement.setString(5, reversal.getSubAccountNumber());
529 preparedStatement.setString(6, reversal.getFinancialObjectCode());
530 preparedStatement.setString(7, reversal.getFinancialSubObjectCode());
531 preparedStatement.setString(8, reversal.getFinancialBalanceTypeCode());
532 preparedStatement.setString(9, reversal.getFinancialObjectTypeCode());
533 preparedStatement.setString(10, reversal.getUniversityFiscalPeriodCode());
534 preparedStatement.setString(11, reversal.getFinancialDocumentTypeCode());
535 preparedStatement.setString(12, reversal.getFinancialSystemOriginationCode());
536 preparedStatement.setString(13, reversal.getDocumentNumber());
537 preparedStatement.setInt(14, reversal.getTransactionLedgerEntrySequenceNumber());
538 preparedStatement.setString(15, reversal.getTransactionLedgerEntryDescription());
539 preparedStatement.setBigDecimal(16, reversal.getTransactionLedgerEntryAmount().bigDecimalValue());
540 preparedStatement.setString(17, reversal.getTransactionDebitCreditCode());
541 preparedStatement.setDate(18, reversal.getTransactionDate());
542 preparedStatement.setString(19, reversal.getOrganizationDocumentNumber());
543 preparedStatement.setString(20, reversal.getProjectCode());
544 preparedStatement.setString(21, reversal.getOrganizationReferenceId());
545 preparedStatement.setString(22, reversal.getReferenceFinancialDocumentTypeCode());
546 preparedStatement.setString(23, reversal.getReferenceFinancialSystemOriginationCode());
547 preparedStatement.setString(24, reversal.getReferenceFinancialDocumentNumber());
548 preparedStatement.setString(25, reversal.getTransactionEncumbranceUpdateCode());
549 preparedStatement.setDate(26, reversal.getTransactionPostingDate());
550 }
551 }.execute(Reversal.class);
552 }
553 }