1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16 package org.kuali.ole.gl.batch.dataaccess.impl;
17
18 import java.sql.PreparedStatement;
19 import java.sql.ResultSet;
20 import java.sql.SQLException;
21 import java.sql.Timestamp;
22 import java.util.HashMap;
23 import java.util.Map;
24
25 import org.apache.commons.lang.StringUtils;
26 import org.kuali.ole.gl.GeneralLedgerConstants;
27 import org.kuali.ole.gl.batch.dataaccess.LedgerPreparedStatementCachingDao;
28 import org.kuali.ole.gl.businessobject.AccountBalance;
29 import org.kuali.ole.gl.businessobject.Balance;
30 import org.kuali.ole.gl.businessobject.Encumbrance;
31 import org.kuali.ole.gl.businessobject.Entry;
32 import org.kuali.ole.gl.businessobject.ExpenditureTransaction;
33 import org.kuali.ole.gl.businessobject.Reversal;
34 import org.kuali.ole.gl.businessobject.SufficientFundBalances;
35 import org.kuali.ole.gl.businessobject.Transaction;
36 import org.kuali.ole.sys.batch.dataaccess.impl.AbstractPreparedStatementCachingDaoJdbc;
37 import org.kuali.rice.core.api.util.type.KualiDecimal;
38
39 public class LedgerPreparedStatementCachingDaoJdbc extends AbstractPreparedStatementCachingDaoJdbc implements LedgerPreparedStatementCachingDao {
40 static final Map<String, String> sql = new HashMap<String, String>();
41 static {
42 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 = ?");
43 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 = ?");
44 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 (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)");
45 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 = ?");
46 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 = ?");
47 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 (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)");
48 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 = ?");
49 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 = ?");
50 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 (?,?,?,?,?,?,?,?,?,?,?,?)");
51 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 = ?");
52 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 = ?");
53 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 (?,?,?,?,?,?,?,?,?)");
54 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 = ?");
55 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 = ?");
56 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 (?,?,?,?,?,?,?,?,?,?)");
57 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 = ?");
58 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 (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)");
59 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 (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)");
60 }
61
62 @Override
63 protected Map<String, String> getSql() {
64 return sql;
65 }
66
67 public int getMaxSequenceNumber(final Transaction t) {
68 return new RetrievingJdbcWrapper<Integer>() {
69 @Override
70 protected void populateStatement(PreparedStatement preparedStatement) throws SQLException {
71 preparedStatement.setInt(1, t.getUniversityFiscalYear());
72 preparedStatement.setString(2, t.getChartOfAccountsCode());
73 preparedStatement.setString(3, t.getAccountNumber());
74 preparedStatement.setString(4, t.getSubAccountNumber());
75 preparedStatement.setString(5, t.getFinancialObjectCode());
76 preparedStatement.setString(6, t.getFinancialSubObjectCode());
77 preparedStatement.setString(7, t.getFinancialBalanceTypeCode());
78 preparedStatement.setString(8, t.getFinancialObjectTypeCode());
79 preparedStatement.setString(9, t.getUniversityFiscalPeriodCode());
80 preparedStatement.setString(10, t.getFinancialDocumentTypeCode());
81 preparedStatement.setString(11, t.getFinancialSystemOriginationCode());
82 preparedStatement.setString(12, t.getDocumentNumber());
83 }
84
85 @Override
86 protected Integer extractResult(ResultSet resultSet) throws SQLException {
87 return resultSet.getInt(1);
88 }
89 }.get(Integer.class);
90 }
91
92 public AccountBalance getAccountBalance(final Transaction t) {
93 return new RetrievingJdbcWrapper<AccountBalance>() {
94 @Override
95 protected void populateStatement(PreparedStatement preparedStatement) throws SQLException {
96 preparedStatement.setInt(1, t.getUniversityFiscalYear());
97 preparedStatement.setString(2, t.getChartOfAccountsCode());
98 preparedStatement.setString(3, t.getAccountNumber());
99 preparedStatement.setString(4, t.getSubAccountNumber());
100 preparedStatement.setString(5, t.getFinancialObjectCode());
101 preparedStatement.setString(6, t.getFinancialSubObjectCode());
102 }
103
104 @Override
105 protected AccountBalance extractResult(ResultSet resultSet) throws SQLException {
106 AccountBalance accountBalance = new AccountBalance();
107 accountBalance.setUniversityFiscalYear(t.getUniversityFiscalYear());
108 accountBalance.setChartOfAccountsCode(t.getChartOfAccountsCode());
109 accountBalance.setAccountNumber(t.getAccountNumber());
110 accountBalance.setSubAccountNumber(t.getSubAccountNumber());
111 accountBalance.setObjectCode(t.getFinancialObjectCode());
112 accountBalance.setSubObjectCode(t.getFinancialSubObjectCode());
113 accountBalance.setCurrentBudgetLineBalanceAmount(new KualiDecimal(resultSet.getBigDecimal(1)));
114 accountBalance.setAccountLineActualsBalanceAmount(new KualiDecimal(resultSet.getBigDecimal(2)));
115 accountBalance.setAccountLineEncumbranceBalanceAmount(new KualiDecimal(resultSet.getBigDecimal(3)));
116 return accountBalance;
117 }
118 }.get(AccountBalance.class);
119 }
120
121 public void insertAccountBalance(final AccountBalance accountBalance, final Timestamp currentTimestamp) {
122 new InsertingJdbcWrapper<AccountBalance>() {
123 @Override
124 protected void populateStatement(PreparedStatement preparedStatement) throws SQLException {
125 preparedStatement.setInt(1, accountBalance.getUniversityFiscalYear());
126 preparedStatement.setString(2, accountBalance.getChartOfAccountsCode());
127 preparedStatement.setString(3, accountBalance.getAccountNumber());
128 preparedStatement.setString(4, accountBalance.getSubAccountNumber());
129 preparedStatement.setString(5, accountBalance.getObjectCode());
130 preparedStatement.setString(6, accountBalance.getSubObjectCode());
131 preparedStatement.setBigDecimal(7, accountBalance.getCurrentBudgetLineBalanceAmount().bigDecimalValue());
132 preparedStatement.setBigDecimal(8, accountBalance.getAccountLineActualsBalanceAmount().bigDecimalValue());
133 preparedStatement.setBigDecimal(9, accountBalance.getAccountLineEncumbranceBalanceAmount().bigDecimalValue());
134 preparedStatement.setTimestamp(10, currentTimestamp);
135 }
136 }.execute(AccountBalance.class);
137 }
138
139 public void updateAccountBalance(final AccountBalance accountBalance, final Timestamp currentTimestamp) {
140 new UpdatingJdbcWrapper<AccountBalance>() {
141 @Override
142 protected void populateStatement(PreparedStatement preparedStatement) throws SQLException {
143 preparedStatement.setBigDecimal(1, accountBalance.getCurrentBudgetLineBalanceAmount().bigDecimalValue());
144 preparedStatement.setBigDecimal(2, accountBalance.getAccountLineActualsBalanceAmount().bigDecimalValue());
145 preparedStatement.setBigDecimal(3, accountBalance.getAccountLineEncumbranceBalanceAmount().bigDecimalValue());
146 preparedStatement.setTimestamp(4, currentTimestamp);
147 preparedStatement.setInt(5, accountBalance.getUniversityFiscalYear());
148 preparedStatement.setString(6, accountBalance.getChartOfAccountsCode());
149 preparedStatement.setString(7, accountBalance.getAccountNumber());
150 preparedStatement.setString(8, accountBalance.getSubAccountNumber());
151 preparedStatement.setString(9, accountBalance.getObjectCode());
152 preparedStatement.setString(10, accountBalance.getSubObjectCode());
153 }
154 }.execute(AccountBalance.class);
155 }
156
157 public Balance getBalance(final Transaction t) {
158 return new RetrievingJdbcWrapper<Balance>() {
159 @Override
160 protected void populateStatement(PreparedStatement preparedStatement) throws SQLException {
161 preparedStatement.setInt(1, t.getUniversityFiscalYear());
162 preparedStatement.setString(2, t.getChartOfAccountsCode());
163 preparedStatement.setString(3, t.getAccountNumber());
164 preparedStatement.setString(4, t.getSubAccountNumber());
165 preparedStatement.setString(5, t.getFinancialObjectCode());
166 preparedStatement.setString(6, t.getFinancialSubObjectCode());
167 preparedStatement.setString(7, t.getFinancialBalanceTypeCode());
168 preparedStatement.setString(8, t.getFinancialObjectTypeCode());
169 }
170
171 @Override
172 protected Balance extractResult(ResultSet resultSet) throws SQLException {
173 Balance balance = new Balance();
174 balance.setUniversityFiscalYear(t.getUniversityFiscalYear());
175 balance.setChartOfAccountsCode(t.getChartOfAccountsCode());
176 balance.setAccountNumber(t.getAccountNumber());
177 balance.setSubAccountNumber(t.getSubAccountNumber());
178 balance.setObjectCode(t.getFinancialObjectCode());
179 balance.setSubObjectCode(t.getFinancialSubObjectCode());
180 balance.setBalanceTypeCode(t.getFinancialBalanceTypeCode());
181 balance.setObjectTypeCode(t.getFinancialObjectTypeCode());
182 balance.setAccountLineAnnualBalanceAmount(new KualiDecimal(resultSet.getBigDecimal(1)));
183 balance.setBeginningBalanceLineAmount(new KualiDecimal(resultSet.getBigDecimal(2)));
184 balance.setContractsGrantsBeginningBalanceAmount(new KualiDecimal(resultSet.getBigDecimal(3)));
185 balance.setMonth1Amount(new KualiDecimal(resultSet.getBigDecimal(4)));
186 balance.setMonth2Amount(new KualiDecimal(resultSet.getBigDecimal(5)));
187 balance.setMonth3Amount(new KualiDecimal(resultSet.getBigDecimal(6)));
188 balance.setMonth4Amount(new KualiDecimal(resultSet.getBigDecimal(7)));
189 balance.setMonth5Amount(new KualiDecimal(resultSet.getBigDecimal(8)));
190 balance.setMonth6Amount(new KualiDecimal(resultSet.getBigDecimal(9)));
191 balance.setMonth7Amount(new KualiDecimal(resultSet.getBigDecimal(10)));
192 balance.setMonth8Amount(new KualiDecimal(resultSet.getBigDecimal(11)));
193 balance.setMonth9Amount(new KualiDecimal(resultSet.getBigDecimal(12)));
194 balance.setMonth10Amount(new KualiDecimal(resultSet.getBigDecimal(13)));
195 balance.setMonth11Amount(new KualiDecimal(resultSet.getBigDecimal(14)));
196 balance.setMonth12Amount(new KualiDecimal(resultSet.getBigDecimal(15)));
197 balance.setMonth13Amount(new KualiDecimal(resultSet.getBigDecimal(16)));
198 return balance;
199 }
200 }.get(Balance.class);
201 }
202
203 public void insertBalance(final Balance balance, final Timestamp currentTimestamp) {
204 new InsertingJdbcWrapper<Balance>() {
205 @Override
206 protected void populateStatement(PreparedStatement preparedStatement) throws SQLException {
207 preparedStatement.setInt(1, balance.getUniversityFiscalYear());
208 preparedStatement.setString(2, balance.getChartOfAccountsCode());
209 preparedStatement.setString(3, balance.getAccountNumber());
210 preparedStatement.setString(4, balance.getSubAccountNumber());
211 preparedStatement.setString(5, balance.getObjectCode());
212 preparedStatement.setString(6, balance.getSubObjectCode());
213 preparedStatement.setString(7, balance.getBalanceTypeCode());
214 preparedStatement.setString(8, balance.getObjectTypeCode());
215 preparedStatement.setBigDecimal(9, balance.getAccountLineAnnualBalanceAmount().bigDecimalValue());
216 preparedStatement.setBigDecimal(10, balance.getBeginningBalanceLineAmount().bigDecimalValue());
217 preparedStatement.setBigDecimal(11, balance.getContractsGrantsBeginningBalanceAmount().bigDecimalValue());
218 preparedStatement.setBigDecimal(12, balance.getMonth1Amount().bigDecimalValue());
219 preparedStatement.setBigDecimal(13, balance.getMonth2Amount().bigDecimalValue());
220 preparedStatement.setBigDecimal(14, balance.getMonth3Amount().bigDecimalValue());
221 preparedStatement.setBigDecimal(15, balance.getMonth4Amount().bigDecimalValue());
222 preparedStatement.setBigDecimal(16, balance.getMonth5Amount().bigDecimalValue());
223 preparedStatement.setBigDecimal(17, balance.getMonth6Amount().bigDecimalValue());
224 preparedStatement.setBigDecimal(18, balance.getMonth7Amount().bigDecimalValue());
225 preparedStatement.setBigDecimal(19, balance.getMonth8Amount().bigDecimalValue());
226 preparedStatement.setBigDecimal(20, balance.getMonth9Amount().bigDecimalValue());
227 preparedStatement.setBigDecimal(21, balance.getMonth10Amount().bigDecimalValue());
228 preparedStatement.setBigDecimal(22, balance.getMonth11Amount().bigDecimalValue());
229 preparedStatement.setBigDecimal(23, balance.getMonth12Amount().bigDecimalValue());
230 preparedStatement.setBigDecimal(24, balance.getMonth13Amount().bigDecimalValue());
231 preparedStatement.setTimestamp(25, currentTimestamp);
232 }
233 }.execute(Balance.class);
234 }
235
236 public void updateBalance(final Balance balance, final Timestamp currentTimestamp) {
237 new UpdatingJdbcWrapper<Balance>() {
238 @Override
239 protected void populateStatement(PreparedStatement preparedStatement) throws SQLException {
240 preparedStatement.setBigDecimal(1, balance.getAccountLineAnnualBalanceAmount().bigDecimalValue());
241 preparedStatement.setBigDecimal(2, balance.getBeginningBalanceLineAmount().bigDecimalValue());
242 preparedStatement.setBigDecimal(3, balance.getContractsGrantsBeginningBalanceAmount().bigDecimalValue());
243 preparedStatement.setBigDecimal(4, balance.getMonth1Amount().bigDecimalValue());
244 preparedStatement.setBigDecimal(5, balance.getMonth2Amount().bigDecimalValue());
245 preparedStatement.setBigDecimal(6, balance.getMonth3Amount().bigDecimalValue());
246 preparedStatement.setBigDecimal(7, balance.getMonth4Amount().bigDecimalValue());
247 preparedStatement.setBigDecimal(8, balance.getMonth5Amount().bigDecimalValue());
248 preparedStatement.setBigDecimal(9, balance.getMonth6Amount().bigDecimalValue());
249 preparedStatement.setBigDecimal(10, balance.getMonth7Amount().bigDecimalValue());
250 preparedStatement.setBigDecimal(11, balance.getMonth8Amount().bigDecimalValue());
251 preparedStatement.setBigDecimal(12, balance.getMonth9Amount().bigDecimalValue());
252 preparedStatement.setBigDecimal(13, balance.getMonth10Amount().bigDecimalValue());
253 preparedStatement.setBigDecimal(14, balance.getMonth11Amount().bigDecimalValue());
254 preparedStatement.setBigDecimal(15, balance.getMonth12Amount().bigDecimalValue());
255 preparedStatement.setBigDecimal(16, balance.getMonth13Amount().bigDecimalValue());
256 preparedStatement.setTimestamp(17, currentTimestamp);
257 preparedStatement.setInt(18, balance.getUniversityFiscalYear());
258 preparedStatement.setString(19, balance.getChartOfAccountsCode());
259 preparedStatement.setString(20, balance.getAccountNumber());
260 preparedStatement.setString(21, balance.getSubAccountNumber());
261 preparedStatement.setString(22, balance.getObjectCode());
262 preparedStatement.setString(23, balance.getSubObjectCode());
263 preparedStatement.setString(24, balance.getBalanceTypeCode());
264 preparedStatement.setString(25, balance.getObjectTypeCode());
265 }
266 }.execute(Balance.class);
267 }
268
269 public Encumbrance getEncumbrance(final Entry entry) {
270 return new RetrievingJdbcWrapper<Encumbrance>() {
271 @Override
272 protected void populateStatement(PreparedStatement preparedStatement) throws SQLException {
273 preparedStatement.setInt(1, entry.getUniversityFiscalYear());
274 preparedStatement.setString(2, entry.getChartOfAccountsCode());
275 preparedStatement.setString(3, entry.getAccountNumber());
276 preparedStatement.setString(4, entry.getSubAccountNumber());
277 preparedStatement.setString(5, entry.getFinancialObjectCode());
278 preparedStatement.setString(6, entry.getFinancialSubObjectCode());
279 preparedStatement.setString(7, entry.getFinancialBalanceTypeCode());
280 preparedStatement.setString(8, entry.getFinancialDocumentTypeCode());
281 preparedStatement.setString(9, entry.getFinancialSystemOriginationCode());
282 preparedStatement.setString(10, entry.getDocumentNumber());
283 }
284
285 @Override
286 protected Encumbrance extractResult(ResultSet resultSet) throws SQLException {
287 Encumbrance encumbrance = new Encumbrance();
288 encumbrance.setUniversityFiscalYear(entry.getUniversityFiscalYear());
289 encumbrance.setChartOfAccountsCode(entry.getChartOfAccountsCode());
290 encumbrance.setAccountNumber(entry.getAccountNumber());
291 encumbrance.setSubAccountNumber(entry.getSubAccountNumber());
292 encumbrance.setObjectCode(entry.getFinancialObjectCode());
293 encumbrance.setSubObjectCode(entry.getFinancialSubObjectCode());
294 encumbrance.setBalanceTypeCode(entry.getFinancialBalanceTypeCode());
295 encumbrance.setDocumentTypeCode(entry.getFinancialDocumentTypeCode());
296 encumbrance.setOriginCode(entry.getFinancialSystemOriginationCode());
297 encumbrance.setDocumentNumber(entry.getDocumentNumber());
298 encumbrance.setTransactionEncumbranceDescription(resultSet.getString(1));
299 encumbrance.setTransactionEncumbranceDate(resultSet.getDate(2));
300 encumbrance.setAccountLineEncumbranceAmount(new KualiDecimal(resultSet.getBigDecimal(3)));
301 encumbrance.setAccountLineEncumbranceClosedAmount(new KualiDecimal(resultSet.getBigDecimal(4)));
302 encumbrance.setAccountLineEncumbrancePurgeCode(resultSet.getString(5));
303 return encumbrance;
304 }
305 }.get(Encumbrance.class);
306 }
307
308 public void insertEncumbrance(final Encumbrance encumbrance, final Timestamp currentTimestamp) {
309 new InsertingJdbcWrapper<Encumbrance>() {
310 @Override
311 protected void populateStatement(PreparedStatement preparedStatement) throws SQLException {
312 preparedStatement.setInt(1, encumbrance.getUniversityFiscalYear());
313 preparedStatement.setString(2, encumbrance.getChartOfAccountsCode());
314 preparedStatement.setString(3, encumbrance.getAccountNumber());
315 preparedStatement.setString(4, encumbrance.getSubAccountNumber());
316 preparedStatement.setString(5, encumbrance.getObjectCode());
317 preparedStatement.setString(6, encumbrance.getSubObjectCode());
318 preparedStatement.setString(7, encumbrance.getBalanceTypeCode());
319 preparedStatement.setString(8, encumbrance.getDocumentTypeCode());
320 preparedStatement.setString(9, encumbrance.getOriginCode());
321 preparedStatement.setString(10, encumbrance.getDocumentNumber());
322 preparedStatement.setString(11, encumbrance.getTransactionEncumbranceDescription());
323 preparedStatement.setDate(12, encumbrance.getTransactionEncumbranceDate());
324 preparedStatement.setBigDecimal(13, encumbrance.getAccountLineEncumbranceAmount().bigDecimalValue());
325 preparedStatement.setBigDecimal(14, encumbrance.getAccountLineEncumbranceClosedAmount().bigDecimalValue());
326 preparedStatement.setString(15, encumbrance.getAccountLineEncumbrancePurgeCode());
327 preparedStatement.setTimestamp(16, currentTimestamp);
328 }
329 }.execute(Encumbrance.class);
330 }
331
332 public void updateEncumbrance(final Encumbrance encumbrance, final Timestamp currentTimestamp) {
333 new UpdatingJdbcWrapper<Encumbrance>() {
334 @Override
335 protected void populateStatement(PreparedStatement preparedStatement) throws SQLException {
336 preparedStatement.setString(1, encumbrance.getTransactionEncumbranceDescription());
337 preparedStatement.setDate(2, encumbrance.getTransactionEncumbranceDate());
338 preparedStatement.setBigDecimal(3, encumbrance.getAccountLineEncumbranceAmount().bigDecimalValue());
339 preparedStatement.setBigDecimal(4, encumbrance.getAccountLineEncumbranceClosedAmount().bigDecimalValue());
340 preparedStatement.setString(5, encumbrance.getAccountLineEncumbrancePurgeCode());
341 preparedStatement.setTimestamp(6, currentTimestamp);
342 preparedStatement.setInt(7, encumbrance.getUniversityFiscalYear());
343 preparedStatement.setString(8, encumbrance.getChartOfAccountsCode());
344 preparedStatement.setString(9, encumbrance.getAccountNumber());
345 preparedStatement.setString(10, encumbrance.getSubAccountNumber());
346 preparedStatement.setString(11, encumbrance.getObjectCode());
347 preparedStatement.setString(12, encumbrance.getSubObjectCode());
348 preparedStatement.setString(13, encumbrance.getBalanceTypeCode());
349 preparedStatement.setString(14, encumbrance.getDocumentTypeCode());
350 preparedStatement.setString(15, encumbrance.getOriginCode());
351 preparedStatement.setString(16, encumbrance.getDocumentNumber());
352 }
353 }.execute(Encumbrance.class);
354 }
355
356 public ExpenditureTransaction getExpenditureTransaction(final Transaction t) {
357 return new RetrievingJdbcWrapper<ExpenditureTransaction>() {
358 @Override
359 protected void populateStatement(PreparedStatement preparedStatement) throws SQLException {
360 preparedStatement.setInt(1, t.getUniversityFiscalYear());
361 preparedStatement.setString(2, t.getChartOfAccountsCode());
362 preparedStatement.setString(3, t.getAccountNumber());
363 preparedStatement.setString(4, t.getSubAccountNumber());
364 preparedStatement.setString(5, t.getFinancialObjectCode());
365 preparedStatement.setString(6, t.getFinancialSubObjectCode());
366 preparedStatement.setString(7, t.getFinancialBalanceTypeCode());
367 preparedStatement.setString(8, t.getFinancialObjectTypeCode());
368 preparedStatement.setString(9, t.getUniversityFiscalPeriodCode());
369 preparedStatement.setString(10, t.getProjectCode());
370 preparedStatement.setString(11, StringUtils.isBlank(t.getOrganizationReferenceId()) ? GeneralLedgerConstants.getDashOrganizationReferenceId() : t.getOrganizationReferenceId());
371 }
372
373 @Override
374 protected ExpenditureTransaction extractResult(ResultSet resultSet) throws SQLException {
375 ExpenditureTransaction expenditureTransaction = new ExpenditureTransaction();
376 expenditureTransaction.setUniversityFiscalYear(t.getUniversityFiscalYear());
377 expenditureTransaction.setChartOfAccountsCode(t.getChartOfAccountsCode());
378 expenditureTransaction.setAccountNumber(t.getAccountNumber());
379 expenditureTransaction.setSubAccountNumber(t.getSubAccountNumber());
380 expenditureTransaction.setObjectCode(t.getFinancialObjectCode());
381 expenditureTransaction.setSubObjectCode(t.getFinancialSubObjectCode());
382 expenditureTransaction.setBalanceTypeCode(t.getFinancialBalanceTypeCode());
383 expenditureTransaction.setObjectTypeCode(t.getFinancialObjectTypeCode());
384 expenditureTransaction.setUniversityFiscalAccountingPeriod(t.getUniversityFiscalPeriodCode());
385 expenditureTransaction.setProjectCode(t.getProjectCode());
386 expenditureTransaction.setOrganizationReferenceId(StringUtils.isBlank(t.getOrganizationReferenceId()) ? GeneralLedgerConstants.getDashOrganizationReferenceId() : t.getOrganizationReferenceId());
387 expenditureTransaction.setAccountObjectDirectCostAmount(new KualiDecimal(resultSet.getBigDecimal(1)));
388 return expenditureTransaction;
389 }
390 }.get(ExpenditureTransaction.class);
391 }
392
393 public void insertExpenditureTransaction(final ExpenditureTransaction expenditureTransaction) {
394 new InsertingJdbcWrapper<ExpenditureTransaction>() {
395 @Override
396 protected void populateStatement(PreparedStatement preparedStatement) throws SQLException {
397 preparedStatement.setInt(1, expenditureTransaction.getUniversityFiscalYear());
398 preparedStatement.setString(2, expenditureTransaction.getChartOfAccountsCode());
399 preparedStatement.setString(3, expenditureTransaction.getAccountNumber());
400 preparedStatement.setString(4, expenditureTransaction.getSubAccountNumber());
401 preparedStatement.setString(5, expenditureTransaction.getObjectCode());
402 preparedStatement.setString(6, expenditureTransaction.getSubObjectCode());
403 preparedStatement.setString(7, expenditureTransaction.getBalanceTypeCode());
404 preparedStatement.setString(8, expenditureTransaction.getObjectTypeCode());
405 preparedStatement.setString(9, expenditureTransaction.getUniversityFiscalAccountingPeriod());
406 preparedStatement.setString(10, expenditureTransaction.getProjectCode());
407 preparedStatement.setString(11, expenditureTransaction.getOrganizationReferenceId());
408 preparedStatement.setBigDecimal(12, expenditureTransaction.getAccountObjectDirectCostAmount().bigDecimalValue());
409 }
410 }.execute(ExpenditureTransaction.class);
411 }
412
413 public void updateExpenditureTransaction(final ExpenditureTransaction expenditureTransaction) {
414 new UpdatingJdbcWrapper<ExpenditureTransaction>() {
415 @Override
416 protected void populateStatement(PreparedStatement preparedStatement) throws SQLException {
417 preparedStatement.setBigDecimal(1, expenditureTransaction.getAccountObjectDirectCostAmount().bigDecimalValue());
418 preparedStatement.setInt(2, expenditureTransaction.getUniversityFiscalYear());
419 preparedStatement.setString(3, expenditureTransaction.getChartOfAccountsCode());
420 preparedStatement.setString(4, expenditureTransaction.getAccountNumber());
421 preparedStatement.setString(5, expenditureTransaction.getSubAccountNumber());
422 preparedStatement.setString(6, expenditureTransaction.getObjectCode());
423 preparedStatement.setString(7, expenditureTransaction.getSubObjectCode());
424 preparedStatement.setString(8, expenditureTransaction.getBalanceTypeCode());
425 preparedStatement.setString(9, expenditureTransaction.getObjectTypeCode());
426 preparedStatement.setString(10, expenditureTransaction.getUniversityFiscalAccountingPeriod());
427 preparedStatement.setString(11, expenditureTransaction.getProjectCode());
428 preparedStatement.setString(12, expenditureTransaction.getOrganizationReferenceId());
429 }
430 }.execute(ExpenditureTransaction.class);
431 }
432
433 public SufficientFundBalances getSufficientFundBalances(final Integer universityFiscalYear, final String chartOfAccountsCode, final String accountNumber, final String financialObjectCode) {
434 return new RetrievingJdbcWrapper<SufficientFundBalances>() {
435 @Override
436 protected void populateStatement(PreparedStatement preparedStatement) throws SQLException {
437 preparedStatement.setInt(1, universityFiscalYear);
438 preparedStatement.setString(2, chartOfAccountsCode);
439 preparedStatement.setString(3, accountNumber);
440 preparedStatement.setString(4, financialObjectCode);
441 }
442
443 @Override
444 protected SufficientFundBalances extractResult(ResultSet resultSet) throws SQLException {
445 SufficientFundBalances sufficientFundBalances = new SufficientFundBalances();
446 sufficientFundBalances.setUniversityFiscalYear(universityFiscalYear);
447 sufficientFundBalances.setChartOfAccountsCode(chartOfAccountsCode);
448 sufficientFundBalances.setAccountNumber(accountNumber);
449 sufficientFundBalances.setFinancialObjectCode(financialObjectCode);
450 sufficientFundBalances.setAccountSufficientFundsCode(resultSet.getString(1));
451 sufficientFundBalances.setCurrentBudgetBalanceAmount(new KualiDecimal(resultSet.getBigDecimal(2)));
452 sufficientFundBalances.setAccountActualExpenditureAmt(new KualiDecimal(resultSet.getBigDecimal(3)));
453 sufficientFundBalances.setAccountEncumbranceAmount(new KualiDecimal(resultSet.getBigDecimal(4)));
454 return sufficientFundBalances;
455 }
456 }.get(SufficientFundBalances.class);
457 }
458
459 public void insertSufficientFundBalances(final SufficientFundBalances sufficientFundBalances, final Timestamp currentTimestamp) {
460 new InsertingJdbcWrapper<SufficientFundBalances>() {
461 @Override
462 protected void populateStatement(PreparedStatement preparedStatement) throws SQLException {
463 preparedStatement.setInt(1, sufficientFundBalances.getUniversityFiscalYear());
464 preparedStatement.setString(2, sufficientFundBalances.getChartOfAccountsCode());
465 preparedStatement.setString(3, sufficientFundBalances.getAccountNumber());
466 preparedStatement.setString(4, sufficientFundBalances.getFinancialObjectCode());
467 preparedStatement.setString(5, sufficientFundBalances.getAccountSufficientFundsCode());
468 preparedStatement.setBigDecimal(6, sufficientFundBalances.getCurrentBudgetBalanceAmount().bigDecimalValue());
469 preparedStatement.setBigDecimal(7, sufficientFundBalances.getAccountActualExpenditureAmt().bigDecimalValue());
470 preparedStatement.setBigDecimal(8, sufficientFundBalances.getAccountEncumbranceAmount().bigDecimalValue());
471 preparedStatement.setTimestamp(9, currentTimestamp);
472 }
473 }.execute(SufficientFundBalances.class);
474 }
475
476 public void updateSufficientFundBalances(final SufficientFundBalances sufficientFundBalances, final Timestamp currentTimestamp) {
477 new UpdatingJdbcWrapper<SufficientFundBalances>() {
478 @Override
479 protected void populateStatement(PreparedStatement preparedStatement) throws SQLException {
480 preparedStatement.setString(1, sufficientFundBalances.getAccountSufficientFundsCode());
481 preparedStatement.setBigDecimal(2, sufficientFundBalances.getCurrentBudgetBalanceAmount().bigDecimalValue());
482 preparedStatement.setBigDecimal(3, sufficientFundBalances.getAccountActualExpenditureAmt().bigDecimalValue());
483 preparedStatement.setBigDecimal(4, sufficientFundBalances.getAccountEncumbranceAmount().bigDecimalValue());
484 preparedStatement.setTimestamp(5, currentTimestamp);
485 preparedStatement.setInt(6, sufficientFundBalances.getUniversityFiscalYear());
486 preparedStatement.setString(7, sufficientFundBalances.getChartOfAccountsCode());
487 preparedStatement.setString(8, sufficientFundBalances.getAccountNumber());
488 preparedStatement.setString(9, sufficientFundBalances.getFinancialObjectCode());
489 }
490 }.execute(SufficientFundBalances.class);
491 }
492
493 public void insertEntry(final Entry entry, final Timestamp currentTimestamp) {
494 new InsertingJdbcWrapper<Entry>() {
495 @Override
496 protected void populateStatement(PreparedStatement preparedStatement) throws SQLException {
497 preparedStatement.setInt(1, entry.getUniversityFiscalYear());
498 preparedStatement.setString(2, entry.getChartOfAccountsCode());
499 preparedStatement.setString(3, entry.getAccountNumber());
500 preparedStatement.setString(4, entry.getSubAccountNumber());
501 preparedStatement.setString(5, entry.getFinancialObjectCode());
502 preparedStatement.setString(6, entry.getFinancialSubObjectCode());
503 preparedStatement.setString(7, entry.getFinancialBalanceTypeCode());
504 preparedStatement.setString(8, entry.getFinancialObjectTypeCode());
505 preparedStatement.setString(9, entry.getUniversityFiscalPeriodCode());
506 preparedStatement.setString(10, entry.getFinancialDocumentTypeCode());
507 preparedStatement.setString(11, entry.getFinancialSystemOriginationCode());
508 preparedStatement.setString(12, entry.getDocumentNumber());
509 preparedStatement.setInt(13, entry.getTransactionLedgerEntrySequenceNumber());
510 preparedStatement.setString(14, entry.getTransactionLedgerEntryDescription());
511 preparedStatement.setBigDecimal(15, entry.getTransactionLedgerEntryAmount().bigDecimalValue());
512 preparedStatement.setString(16, entry.getTransactionDebitCreditCode());
513 preparedStatement.setDate(17, entry.getTransactionDate());
514 preparedStatement.setString(18, entry.getOrganizationDocumentNumber());
515 preparedStatement.setString(19, entry.getProjectCode());
516 preparedStatement.setString(20, entry.getOrganizationReferenceId());
517 preparedStatement.setString(21, entry.getReferenceFinancialDocumentTypeCode());
518 preparedStatement.setString(22, entry.getReferenceFinancialSystemOriginationCode());
519 preparedStatement.setString(23, entry.getReferenceFinancialDocumentNumber());
520 preparedStatement.setDate(24, entry.getFinancialDocumentReversalDate());
521 preparedStatement.setString(25, entry.getTransactionEncumbranceUpdateCode());
522 preparedStatement.setDate(26, entry.getTransactionPostingDate());
523 preparedStatement.setTimestamp(27, currentTimestamp);
524 }
525 }.execute(Entry.class);
526 }
527
528 public void insertReversal(final Reversal reversal) {
529 new InsertingJdbcWrapper<Reversal>() {
530 @Override
531 protected void populateStatement(PreparedStatement preparedStatement) throws SQLException {
532 preparedStatement.setDate(1, reversal.getFinancialDocumentReversalDate());
533 preparedStatement.setInt(2, reversal.getUniversityFiscalYear());
534 preparedStatement.setString(3, reversal.getChartOfAccountsCode());
535 preparedStatement.setString(4, reversal.getAccountNumber());
536 preparedStatement.setString(5, reversal.getSubAccountNumber());
537 preparedStatement.setString(6, reversal.getFinancialObjectCode());
538 preparedStatement.setString(7, reversal.getFinancialSubObjectCode());
539 preparedStatement.setString(8, reversal.getFinancialBalanceTypeCode());
540 preparedStatement.setString(9, reversal.getFinancialObjectTypeCode());
541 preparedStatement.setString(10, reversal.getUniversityFiscalPeriodCode());
542 preparedStatement.setString(11, reversal.getFinancialDocumentTypeCode());
543 preparedStatement.setString(12, reversal.getFinancialSystemOriginationCode());
544 preparedStatement.setString(13, reversal.getDocumentNumber());
545 preparedStatement.setInt(14, reversal.getTransactionLedgerEntrySequenceNumber());
546 preparedStatement.setString(15, reversal.getTransactionLedgerEntryDescription());
547 preparedStatement.setBigDecimal(16, reversal.getTransactionLedgerEntryAmount().bigDecimalValue());
548 preparedStatement.setString(17, reversal.getTransactionDebitCreditCode());
549 preparedStatement.setDate(18, reversal.getTransactionDate());
550 preparedStatement.setString(19, reversal.getOrganizationDocumentNumber());
551 preparedStatement.setString(20, reversal.getProjectCode());
552 preparedStatement.setString(21, reversal.getOrganizationReferenceId());
553 preparedStatement.setString(22, reversal.getReferenceFinancialDocumentTypeCode());
554 preparedStatement.setString(23, reversal.getReferenceFinancialSystemOriginationCode());
555 preparedStatement.setString(24, reversal.getReferenceFinancialDocumentNumber());
556 preparedStatement.setString(25, reversal.getTransactionEncumbranceUpdateCode());
557 preparedStatement.setDate(26, reversal.getTransactionPostingDate());
558 }
559 }.execute(Reversal.class);
560 }
561 }