1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16 package org.kuali.ole.coa.batch.dataaccess.impl;
17
18 import java.sql.PreparedStatement;
19 import java.sql.ResultSet;
20 import java.sql.SQLException;
21 import java.util.HashMap;
22 import java.util.List;
23 import java.util.Map;
24
25 import org.kuali.ole.coa.batch.dataaccess.LedgerReferenceValuePreparedStatementCachingDao;
26 import org.kuali.ole.coa.businessobject.A21IndirectCostRecoveryAccount;
27 import org.kuali.ole.coa.businessobject.A21SubAccount;
28 import org.kuali.ole.coa.businessobject.Account;
29 import org.kuali.ole.coa.businessobject.AccountingPeriod;
30 import org.kuali.ole.coa.businessobject.BalanceType;
31 import org.kuali.ole.coa.businessobject.Chart;
32 import org.kuali.ole.coa.businessobject.IndirectCostRecoveryType;
33 import org.kuali.ole.coa.businessobject.ObjectCode;
34 import org.kuali.ole.coa.businessobject.ObjectLevel;
35 import org.kuali.ole.coa.businessobject.ObjectType;
36 import org.kuali.ole.coa.businessobject.OffsetDefinition;
37 import org.kuali.ole.coa.businessobject.Organization;
38 import org.kuali.ole.coa.businessobject.ProjectCode;
39 import org.kuali.ole.coa.businessobject.SubAccount;
40 import org.kuali.ole.coa.businessobject.SubFundGroup;
41 import org.kuali.ole.coa.businessobject.SubObjectCode;
42 import org.kuali.ole.sys.OLEConstants;
43 import org.kuali.ole.sys.batch.dataaccess.impl.AbstractPreparedStatementCachingDaoJdbc;
44
45 public class LedgerReferenceValuePreparedStatementCachingDaoJdbc extends AbstractPreparedStatementCachingDaoJdbc implements LedgerReferenceValuePreparedStatementCachingDao {
46 static final Map<String,String> sql = new HashMap<String,String>();
47 static {
48 sql.put(RETRIEVE_PREFIX + Chart.class, "select fin_coa_active_cd, fin_cash_obj_cd, fin_ap_obj_cd, FND_BAL_OBJ_CD from CA_CHART_T where fin_coa_cd = ?");
49 sql.put(RETRIEVE_PREFIX + Account.class, "select acct_expiration_dt, acct_closed_ind, sub_fund_grp_cd, org_cd, cont_fin_coa_cd, cont_account_nbr, fin_series_id, acct_icr_typ_cd, acct_sf_cd from CA_ACCOUNT_T where fin_coa_cd = ? and account_nbr = ?");
50 sql.put(RETRIEVE_PREFIX + SubAccount.class, "select sub_acct_actv_cd from CA_SUB_ACCT_T where fin_coa_cd = ? and account_nbr = ? and sub_acct_nbr = ?");
51 sql.put(RETRIEVE_PREFIX + ObjectCode.class, "select fin_obj_typ_cd, fin_obj_sub_typ_cd, fin_obj_level_cd, fin_obj_active_cd, rpts_to_fin_coa_cd, rpts_to_fin_obj_cd from CA_OBJECT_CODE_T where univ_fiscal_yr = ? and fin_coa_cd = ? and fin_object_cd = ?");
52 sql.put(RETRIEVE_PREFIX + SubObjectCode.class, "select fin_subobj_actv_cd from CA_SUB_OBJECT_CD_T where univ_fiscal_yr = ? and fin_coa_cd = ? and account_nbr = ? and fin_object_cd = ? and fin_sub_obj_cd = ?");
53 sql.put(RETRIEVE_PREFIX + ProjectCode.class, "select proj_active_cd from CA_PROJECT_T where project_cd = ?");
54 sql.put(RETRIEVE_PREFIX + Organization.class, "select org_plnt_coa_cd, org_plnt_acct_nbr, cmp_plnt_coa_cd, cmp_plnt_acct_nbr from CA_ORG_T where fin_coa_cd = ? and org_cd = ?");
55 sql.put(RETRIEVE_PREFIX + SubFundGroup.class, "select fund_grp_cd from CA_SUB_FUND_GRP_T where sub_fund_grp_cd = ?");
56 sql.put(RETRIEVE_PREFIX + OffsetDefinition.class, "select fin_object_cd from GL_OFFSET_DEFN_T where univ_fiscal_yr = ? and fin_coa_cd = ? and fdoc_typ_cd = ? and fin_balance_typ_cd = ?");
57 sql.put(RETRIEVE_PREFIX + A21SubAccount.class, "select sub_acct_typ_cd, cst_shr_coa_cd, cst_shrsrcacct_nbr, cst_srcsubacct_nbr, icr_typ_cd, fin_series_id, icr_fin_coa_cd, icr_account_nbr from CA_A21_SUB_ACCT_T where fin_coa_cd = ? and account_nbr = ? and sub_acct_nbr = ?");
58 sql.put(RETRIEVE_PREFIX + A21IndirectCostRecoveryAccount.class, "select ICR_FIN_COA_CD, ICR_FIN_ACCT_NBR, ACLN_PCT, DOBJ_MAINT_CD_ACTV_IND from CA_A21_ICR_ACCT_T where fin_coa_cd = ? and account_nbr = ? and sub_acct_nbr = ? ");
59 sql.put(RETRIEVE_PREFIX + ObjectType.class, "select fund_balance_cd, fin_objtyp_dbcr_cd, fin_obj_typ_icr_cd, ROW_ACTV_IND from CA_OBJ_TYPE_T where fin_obj_typ_cd = ?");
60 sql.put(RETRIEVE_PREFIX + ObjectLevel.class, "select fin_cons_obj_cd from CA_OBJ_LEVEL_T where fin_coa_cd = ? and fin_obj_level_cd = ?");
61 sql.put(RETRIEVE_PREFIX + BalanceType.class, "select fin_offst_gnrtn_cd, fin_baltyp_enc_cd, ROW_ACTV_IND from CA_BALANCE_TYPE_T where fin_balance_typ_cd = ?");
62 sql.put(RETRIEVE_PREFIX + AccountingPeriod.class, "select row_actv_ind from SH_ACCT_PERIOD_T where univ_fiscal_yr = ? and univ_fiscal_prd_cd = ?");
63 sql.put(RETRIEVE_PREFIX + IndirectCostRecoveryType.class, "select ACCT_ICR_TYP_ACTV_IND from CA_ICR_TYPE_T where acct_icr_typ_cd = ?");
64 }
65
66 @Override
67 protected Map<String, String> getSql() {
68 return sql;
69 }
70
71 public A21SubAccount getA21SubAccount(final String chartOfAccountsCode, final String accountNumber, final String subAccountNumber) {
72 return new RetrievingJdbcWrapper<A21SubAccount>() {
73 @Override
74 protected void populateStatement(PreparedStatement preparedStatement) throws SQLException {
75 preparedStatement.setString(1, chartOfAccountsCode);
76 preparedStatement.setString(2, accountNumber);
77 preparedStatement.setString(3, subAccountNumber);
78 }
79 @Override
80 protected A21SubAccount extractResult(ResultSet resultSet) throws SQLException {
81 A21SubAccount a21SubAccount = new A21SubAccount();
82 a21SubAccount.setChartOfAccountsCode(chartOfAccountsCode);
83 a21SubAccount.setAccountNumber(accountNumber);
84 a21SubAccount.setSubAccountNumber(subAccountNumber);
85 a21SubAccount.setSubAccountTypeCode(resultSet.getString(1));
86 a21SubAccount.setCostShareChartOfAccountCode(resultSet.getString(2));
87 a21SubAccount.setCostShareSourceAccountNumber(resultSet.getString(3));
88 a21SubAccount.setCostShareSourceSubAccountNumber(resultSet.getString(4));
89 a21SubAccount.setIndirectCostRecoveryTypeCode(resultSet.getString(5));
90 a21SubAccount.setFinancialIcrSeriesIdentifier(resultSet.getString(6));
91 return a21SubAccount;
92 }
93 }.get(A21SubAccount.class);
94 }
95
96 @Override
97 public List<A21IndirectCostRecoveryAccount> getA21IndirectCostRecoveryAccounts(final String chartOfAccountsCode, final String accountNumber, final String subAccountNumber) {
98 return new RetrievingListJdbcWrapper<A21IndirectCostRecoveryAccount>() {
99 @Override
100 protected void populateStatement(PreparedStatement preparedStatement) throws SQLException {
101 preparedStatement.setString(1, chartOfAccountsCode);
102 preparedStatement.setString(2, accountNumber);
103 preparedStatement.setString(3, subAccountNumber);
104 }
105 @Override
106 protected A21IndirectCostRecoveryAccount extractResult(ResultSet resultSet) throws SQLException {
107 A21IndirectCostRecoveryAccount a21 = new A21IndirectCostRecoveryAccount();
108 a21.setChartOfAccountsCode(chartOfAccountsCode);
109 a21.setAccountNumber(accountNumber);
110 a21.setSubAccountNumber(subAccountNumber);
111 a21.setIndirectCostRecoveryFinCoaCode(resultSet.getString(1));
112 a21.setIndirectCostRecoveryAccountNumber(resultSet.getString(2));
113 a21.setAccountLinePercent(resultSet.getBigDecimal(3));
114 a21.setActive(OLEConstants.ParameterValues.YES.equals(resultSet.getString(4)) ? false : true);
115 return a21;
116 }
117 }.get(A21IndirectCostRecoveryAccount.class);
118
119 }
120
121 public Account getAccount(final String chartCode, final String accountNumber) {
122 return new RetrievingJdbcWrapper<Account>() {
123 @Override
124 protected void populateStatement(PreparedStatement preparedStatement) throws SQLException {
125 preparedStatement.setString(1, chartCode);
126 preparedStatement.setString(2, accountNumber);
127 }
128 @Override
129 protected Account extractResult(ResultSet resultSet) throws SQLException {
130 Account account = new Account();
131 account.setChartOfAccountsCode(chartCode);
132 account.setAccountNumber(accountNumber);
133 account.setAccountExpirationDate(resultSet.getDate(1));
134 account.setActive(OLEConstants.ParameterValues.YES.equals(resultSet.getString(2)) ? false : true);
135 account.setSubFundGroupCode(resultSet.getString(3));
136 account.setOrganizationCode(resultSet.getString(4));
137 account.setContinuationFinChrtOfAcctCd(resultSet.getString(5));
138 account.setContinuationAccountNumber(resultSet.getString(6));
139 account.setFinancialIcrSeriesIdentifier(resultSet.getString(7));
140 account.setAcctIndirectCostRcvyTypeCd(resultSet.getString(8));
141 account.setAccountSufficientFundsCode(resultSet.getString(9));
142 return account;
143 }
144 }.get(Account.class);
145 }
146
147 public AccountingPeriod getAccountingPeriod(final Integer fiscalYear, final String fiscalPeriodCode) {
148 return new RetrievingJdbcWrapper<AccountingPeriod>() {
149 @Override
150 protected void populateStatement(PreparedStatement preparedStatement) throws SQLException {
151 preparedStatement.setInt(1, fiscalYear);
152 preparedStatement.setString(2, fiscalPeriodCode);
153 }
154 @Override
155 protected AccountingPeriod extractResult(ResultSet resultSet) throws SQLException {
156 AccountingPeriod accountingPeriod = new AccountingPeriod();
157 accountingPeriod.setUniversityFiscalYear(fiscalYear);
158 accountingPeriod.setUniversityFiscalPeriodCode(fiscalPeriodCode);
159 accountingPeriod.setActive(OLEConstants.ParameterValues.YES.equals(resultSet.getString(1)) ? true : false);
160 return accountingPeriod;
161 }
162 }.get(AccountingPeriod.class);
163 }
164
165 public BalanceType getBalanceType(final String financialBalanceTypeCode) {
166 return new RetrievingJdbcWrapper<BalanceType>() {
167 @Override
168 protected void populateStatement(PreparedStatement preparedStatement) throws SQLException {
169 preparedStatement.setString(1, financialBalanceTypeCode);
170 }
171 @Override
172 protected BalanceType extractResult(ResultSet resultSet) throws SQLException {
173 BalanceType balanceType = new BalanceType();
174 balanceType.setFinancialBalanceTypeCode(financialBalanceTypeCode);
175 balanceType.setFinancialOffsetGenerationIndicator(OLEConstants.ParameterValues.YES.equals(resultSet.getString(1)) ? true : false);
176 balanceType.setFinBalanceTypeEncumIndicator(OLEConstants.ParameterValues.YES.equals(resultSet.getString(2)) ? true : false);
177 balanceType.setActive(OLEConstants.ParameterValues.YES.equals(resultSet.getString(3)) ? true : false);
178 return balanceType;
179 }
180 }.get(BalanceType.class);
181 }
182
183 public Chart getChart(final String chartOfAccountsCode) {
184 return new RetrievingJdbcWrapper<Chart>() {
185 @Override
186 protected void populateStatement(PreparedStatement preparedStatement) throws SQLException {
187 preparedStatement.setString(1, chartOfAccountsCode);
188 }
189 @Override
190 protected Chart extractResult(ResultSet resultSet) throws SQLException {
191 Chart chart = new Chart();
192 chart.setChartOfAccountsCode(chartOfAccountsCode);
193 chart.setActive(OLEConstants.ParameterValues.YES.equals(resultSet.getString(1)) ? true : false);
194 chart.setFundBalanceObjectCode(resultSet.getString(4));
195 chart.setFinancialCashObjectCode(resultSet.getString(2));
196 chart.setFinAccountsPayableObjectCode(resultSet.getString(3));
197 return chart;
198 }
199 }.get(Chart.class);
200 }
201
202 public IndirectCostRecoveryType getIndirectCostRecoveryType(final String accountIcrTypeCode) {
203 return new RetrievingJdbcWrapper<IndirectCostRecoveryType>() {
204 @Override
205 protected void populateStatement(PreparedStatement preparedStatement) throws SQLException {
206 preparedStatement.setString(1, accountIcrTypeCode);
207 }
208 @Override
209 protected IndirectCostRecoveryType extractResult(ResultSet resultSet) throws SQLException {
210 IndirectCostRecoveryType indirectCostRecoveryType = new IndirectCostRecoveryType();
211 indirectCostRecoveryType.setActive(OLEConstants.ParameterValues.YES.equals(resultSet.getString(1)) ? true : false);
212 return indirectCostRecoveryType;
213 }
214 }.get(IndirectCostRecoveryType.class);
215 }
216
217 public ObjectCode getObjectCode(final Integer universityFiscalYear, final String chartOfAccountsCode, final String financialObjectCode) {
218 return new RetrievingJdbcWrapper<ObjectCode>() {
219 @Override
220 protected void populateStatement(PreparedStatement preparedStatement) throws SQLException {
221 preparedStatement.setInt(1, universityFiscalYear);
222 preparedStatement.setString(2, chartOfAccountsCode);
223 preparedStatement.setString(3, financialObjectCode);
224 }
225 @Override
226 protected ObjectCode extractResult(ResultSet resultSet) throws SQLException {
227 ObjectCode objectCode = new ObjectCode();
228 objectCode.setUniversityFiscalYear(universityFiscalYear);
229 objectCode.setChartOfAccountsCode(chartOfAccountsCode);
230 objectCode.setFinancialObjectCode(financialObjectCode);
231 objectCode.setFinancialObjectTypeCode(resultSet.getString(1));
232 objectCode.setFinancialObjectSubTypeCode(resultSet.getString(2));
233 objectCode.setFinancialObjectLevelCode(resultSet.getString(3));
234 objectCode.setActive(OLEConstants.ParameterValues.YES.equals(resultSet.getString(4)) ? true : false);
235 objectCode.setReportsToChartOfAccountsCode(resultSet.getString(5));
236 objectCode.setReportsToFinancialObjectCode(resultSet.getString(6));
237 return objectCode;
238 }
239 }.get(ObjectCode.class);
240 }
241
242 public ObjectLevel getObjectLevel(final String chartOfAccountsCode, final String financialObjectLevelCode) {
243 return new RetrievingJdbcWrapper<ObjectLevel>() {
244 @Override
245 protected void populateStatement(PreparedStatement preparedStatement) throws SQLException {
246 preparedStatement.setString(1, chartOfAccountsCode);
247 preparedStatement.setString(2, financialObjectLevelCode);
248 }
249 @Override
250 protected ObjectLevel extractResult(ResultSet resultSet) throws SQLException {
251 ObjectLevel objectLevel = new ObjectLevel();
252 objectLevel.setChartOfAccountsCode(chartOfAccountsCode);
253 objectLevel.setFinancialObjectLevelCode(financialObjectLevelCode);
254 objectLevel.setFinancialConsolidationObjectCode(resultSet.getString(1));
255 return objectLevel;
256 }
257 }.get(ObjectLevel.class);
258 }
259
260 public ObjectType getObjectType(final String financialObjectTypeCode) {
261 return new RetrievingJdbcWrapper<ObjectType>() {
262 @Override
263 protected void populateStatement(PreparedStatement preparedStatement) throws SQLException {
264 preparedStatement.setString(1, financialObjectTypeCode);
265 }
266 @Override
267 protected ObjectType extractResult(ResultSet resultSet) throws SQLException {
268 ObjectType objectType = new ObjectType();
269 objectType.setCode(financialObjectTypeCode);
270 objectType.setFundBalanceIndicator(OLEConstants.ParameterValues.YES.equals(resultSet.getString(1)) ? true : false);
271 objectType.setFinObjectTypeDebitcreditCd(resultSet.getString(2));
272 objectType.setFinObjectTypeIcrSelectionIndicator(OLEConstants.ParameterValues.YES.equals(resultSet.getString(3)) ? true : false);
273 objectType.setActive(OLEConstants.ParameterValues.YES.equals(resultSet.getString(4)) ? true : false);
274 return objectType;
275 }
276 }.get(ObjectType.class);
277 }
278
279 public OffsetDefinition getOffsetDefinition(final Integer universityFiscalYear, final String chartOfAccountsCode, final String financialDocumentTypeCode, final String financialBalanceTypeCode) {
280 return new RetrievingJdbcWrapper<OffsetDefinition>() {
281 @Override
282 protected void populateStatement(PreparedStatement preparedStatement) throws SQLException {
283 preparedStatement.setInt(1, universityFiscalYear);
284 preparedStatement.setString(2, chartOfAccountsCode);
285 preparedStatement.setString(3, financialDocumentTypeCode);
286 preparedStatement.setString(4, financialBalanceTypeCode);
287 }
288 @Override
289 protected OffsetDefinition extractResult(ResultSet resultSet) throws SQLException {
290 OffsetDefinition offsetDefinition = new OffsetDefinition();
291 offsetDefinition.setUniversityFiscalYear(universityFiscalYear);
292 offsetDefinition.setChartOfAccountsCode(chartOfAccountsCode);
293 offsetDefinition.setFinancialDocumentTypeCode(financialDocumentTypeCode);
294 offsetDefinition.setFinancialBalanceTypeCode(financialBalanceTypeCode);
295 offsetDefinition.setFinancialObjectCode(resultSet.getString(1));
296 return offsetDefinition;
297 }
298 }.get(OffsetDefinition.class);
299 }
300
301 public Organization getOrganization(final String chartOfAccountsCode, final String organizationCode) {
302 return new RetrievingJdbcWrapper<Organization>() {
303 @Override
304 protected void populateStatement(PreparedStatement preparedStatement) throws SQLException {
305 preparedStatement.setString(1, chartOfAccountsCode);
306 preparedStatement.setString(2, organizationCode);
307 }
308 @Override
309 protected Organization extractResult(ResultSet resultSet) throws SQLException {
310 Organization organization = new Organization();
311 organization.setChartOfAccountsCode(chartOfAccountsCode);
312 organization.setOrganizationCode(organizationCode);
313 organization.setOrganizationPlantChartCode(resultSet.getString(1));
314 organization.setOrganizationPlantAccountNumber(resultSet.getString(2));
315 organization.setCampusPlantChartCode(resultSet.getString(3));
316 organization.setCampusPlantAccountNumber(resultSet.getString(4));
317 return organization;
318 }
319 }.get(Organization.class);
320 }
321
322 public ProjectCode getProjectCode(final String financialSystemProjectCode) {
323 return new RetrievingJdbcWrapper<ProjectCode>() {
324 @Override
325 protected void populateStatement(PreparedStatement preparedStatement) throws SQLException {
326 preparedStatement.setString(1, financialSystemProjectCode);
327 }
328 @Override
329 protected ProjectCode extractResult(ResultSet resultSet) throws SQLException {
330 ProjectCode projectCode = new ProjectCode();
331 projectCode.setCode(financialSystemProjectCode);
332 projectCode.setActive(OLEConstants.ParameterValues.YES.equals(resultSet.getString(1)) ? true : false);
333 return projectCode;
334 }
335 }.get(ProjectCode.class);
336 }
337
338 public SubAccount getSubAccount(final String chartOfAccountsCode, final String accountNumber, final String subAccountNumber) {
339 return new RetrievingJdbcWrapper<SubAccount>() {
340 @Override
341 protected void populateStatement(PreparedStatement preparedStatement) throws SQLException {
342 preparedStatement.setString(1, chartOfAccountsCode);
343 preparedStatement.setString(2, accountNumber);
344 preparedStatement.setString(3, subAccountNumber);
345 }
346 @Override
347 protected SubAccount extractResult(ResultSet resultSet) throws SQLException {
348 SubAccount subAccount = new SubAccount();
349 subAccount.setChartOfAccountsCode(chartOfAccountsCode);
350 subAccount.setAccountNumber(accountNumber);
351 subAccount.setSubAccountNumber(subAccountNumber);
352 subAccount.setActive(OLEConstants.ParameterValues.YES.equals(resultSet.getString(1)) ? true : false);
353 return subAccount;
354 }
355 }.get(SubAccount.class);
356 }
357
358 public SubFundGroup getSubFundGroup(final String subFundGroupCode) {
359 return new RetrievingJdbcWrapper<SubFundGroup>() {
360 @Override
361 protected void populateStatement(PreparedStatement preparedStatement) throws SQLException {
362 preparedStatement.setString(1, subFundGroupCode);
363 }
364 @Override
365 protected SubFundGroup extractResult(ResultSet resultSet) throws SQLException {
366 SubFundGroup subFundGroup = new SubFundGroup();
367 subFundGroup.setSubFundGroupCode(subFundGroupCode);
368 subFundGroup.setFundGroupCode(resultSet.getString(1));
369 return subFundGroup;
370 }
371 }.get(SubFundGroup.class);
372 }
373
374 public SubObjectCode getSubObjectCode(final Integer universityFiscalYear, final String chartOfAccountsCode, final String accountNumber, final String financialObjectCode, final String financialSubObjectCode) {
375 return new RetrievingJdbcWrapper<SubObjectCode>() {
376 @Override
377 protected void populateStatement(PreparedStatement preparedStatement) throws SQLException {
378 preparedStatement.setInt(1, universityFiscalYear);
379 preparedStatement.setString(2, chartOfAccountsCode);
380 preparedStatement.setString(3, accountNumber);
381 preparedStatement.setString(4, financialObjectCode);
382 preparedStatement.setString(5, financialSubObjectCode);
383 }
384 @Override
385 protected SubObjectCode extractResult(ResultSet resultSet) throws SQLException {
386 SubObjectCode subObjectCode = new SubObjectCode();
387 subObjectCode.setUniversityFiscalYear(universityFiscalYear);
388 subObjectCode.setChartOfAccountsCode(chartOfAccountsCode);
389 subObjectCode.setAccountNumber(accountNumber);
390 subObjectCode.setFinancialObjectCode(financialObjectCode);
391 subObjectCode.setFinancialSubObjectCode(financialSubObjectCode);
392 subObjectCode.setActive(OLEConstants.ParameterValues.YES.equals(resultSet.getString(1)) ? true : false);
393 return subObjectCode;
394 }
395 }.get(SubObjectCode.class);
396 }
397
398 }