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.coa.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.kuali.kfs.coa.batch.dataaccess.LedgerReferenceValuePreparedStatementCachingDao;
025 import org.kuali.kfs.coa.businessobject.A21SubAccount;
026 import org.kuali.kfs.coa.businessobject.Account;
027 import org.kuali.kfs.coa.businessobject.AccountingPeriod;
028 import org.kuali.kfs.coa.businessobject.BalanceType;
029 import org.kuali.kfs.coa.businessobject.Chart;
030 import org.kuali.kfs.coa.businessobject.IndirectCostRecoveryType;
031 import org.kuali.kfs.coa.businessobject.ObjectCode;
032 import org.kuali.kfs.coa.businessobject.ObjectLevel;
033 import org.kuali.kfs.coa.businessobject.ObjectType;
034 import org.kuali.kfs.coa.businessobject.OffsetDefinition;
035 import org.kuali.kfs.coa.businessobject.Organization;
036 import org.kuali.kfs.coa.businessobject.ProjectCode;
037 import org.kuali.kfs.coa.businessobject.SubAccount;
038 import org.kuali.kfs.coa.businessobject.SubFundGroup;
039 import org.kuali.kfs.coa.businessobject.SubObjectCode;
040 import org.kuali.kfs.sys.KFSConstants;
041 import org.kuali.kfs.sys.batch.dataaccess.impl.AbstractPreparedStatementCachingDaoJdbc;
042
043 public class LedgerReferenceValuePreparedStatementCachingDaoJdbc extends AbstractPreparedStatementCachingDaoJdbc implements LedgerReferenceValuePreparedStatementCachingDao {
044 static final Map<String,String> sql = new HashMap<String,String>();
045 static {
046 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 = ?");
047 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 = ?");
048 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 = ?");
049 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 = ?");
050 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 = ?");
051 sql.put(RETRIEVE_PREFIX + ProjectCode.class, "select proj_active_cd from CA_PROJECT_T where project_cd = ?");
052 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 = ?");
053 sql.put(RETRIEVE_PREFIX + SubFundGroup.class, "select fund_grp_cd from CA_SUB_FUND_GRP_T where sub_fund_grp_cd = ?");
054 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 = ?");
055 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 = ?");
056 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 = ?");
057 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 = ?");
058 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 = ?");
059 sql.put(RETRIEVE_PREFIX + AccountingPeriod.class, "select row_actv_ind from SH_ACCT_PERIOD_T where univ_fiscal_yr = ? and univ_fiscal_prd_cd = ?");
060 sql.put(RETRIEVE_PREFIX + IndirectCostRecoveryType.class, "select ACCT_ICR_TYP_ACTV_IND from CA_ICR_TYPE_T where acct_icr_typ_cd = ?");
061 }
062
063 @Override
064 protected Map<String, String> getSql() {
065 return sql;
066 }
067
068 public A21SubAccount getA21SubAccount(final String chartOfAccountsCode, final String accountNumber, final String subAccountNumber) {
069 return new RetrievingJdbcWrapper<A21SubAccount>() {
070 @Override
071 protected void populateStatement(PreparedStatement preparedStatement) throws SQLException {
072 preparedStatement.setString(1, chartOfAccountsCode);
073 preparedStatement.setString(2, accountNumber);
074 preparedStatement.setString(3, subAccountNumber);
075 }
076 @Override
077 protected A21SubAccount extractResult(ResultSet resultSet) throws SQLException {
078 A21SubAccount a21SubAccount = new A21SubAccount();
079 a21SubAccount.setChartOfAccountsCode(chartOfAccountsCode);
080 a21SubAccount.setAccountNumber(accountNumber);
081 a21SubAccount.setSubAccountNumber(subAccountNumber);
082 a21SubAccount.setSubAccountTypeCode(resultSet.getString(1));
083 a21SubAccount.setCostShareChartOfAccountCode(resultSet.getString(2));
084 a21SubAccount.setCostShareSourceAccountNumber(resultSet.getString(3));
085 a21SubAccount.setCostShareSourceSubAccountNumber(resultSet.getString(4));
086 a21SubAccount.setIndirectCostRecoveryTypeCode(resultSet.getString(5));
087 a21SubAccount.setFinancialIcrSeriesIdentifier(resultSet.getString(6));
088 a21SubAccount.setIndirectCostRcvyFinCoaCode(resultSet.getString(7));
089 a21SubAccount.setIndirectCostRecoveryAcctNbr(resultSet.getString(8));
090 return a21SubAccount;
091 }
092 }.get(A21SubAccount.class);
093 }
094
095 public Account getAccount(final String chartCode, final String accountNumber) {
096 return new RetrievingJdbcWrapper<Account>() {
097 @Override
098 protected void populateStatement(PreparedStatement preparedStatement) throws SQLException {
099 preparedStatement.setString(1, chartCode);
100 preparedStatement.setString(2, accountNumber);
101 }
102 @Override
103 protected Account extractResult(ResultSet resultSet) throws SQLException {
104 Account account = new Account();
105 account.setChartOfAccountsCode(chartCode);
106 account.setAccountNumber(accountNumber);
107 account.setAccountExpirationDate(resultSet.getDate(1));
108 account.setActive(KFSConstants.ParameterValues.YES.equals(resultSet.getString(2)) ? false : true);
109 account.setSubFundGroupCode(resultSet.getString(3));
110 account.setOrganizationCode(resultSet.getString(4));
111 account.setContinuationFinChrtOfAcctCd(resultSet.getString(5));
112 account.setContinuationAccountNumber(resultSet.getString(6));
113 account.setFinancialIcrSeriesIdentifier(resultSet.getString(7));
114 account.setAcctIndirectCostRcvyTypeCd(resultSet.getString(8));
115 account.setAccountSufficientFundsCode(resultSet.getString(9));
116 return account;
117 }
118 }.get(Account.class);
119 }
120
121 public AccountingPeriod getAccountingPeriod(final Integer fiscalYear, final String fiscalPeriodCode) {
122 return new RetrievingJdbcWrapper<AccountingPeriod>() {
123 @Override
124 protected void populateStatement(PreparedStatement preparedStatement) throws SQLException {
125 preparedStatement.setInt(1, fiscalYear);
126 preparedStatement.setString(2, fiscalPeriodCode);
127 }
128 @Override
129 protected AccountingPeriod extractResult(ResultSet resultSet) throws SQLException {
130 AccountingPeriod accountingPeriod = new AccountingPeriod();
131 accountingPeriod.setUniversityFiscalYear(fiscalYear);
132 accountingPeriod.setUniversityFiscalPeriodCode(fiscalPeriodCode);
133 accountingPeriod.setActive(KFSConstants.ParameterValues.YES.equals(resultSet.getString(1)) ? true : false);
134 return accountingPeriod;
135 }
136 }.get(AccountingPeriod.class);
137 }
138
139 public BalanceType getBalanceType(final String financialBalanceTypeCode) {
140 return new RetrievingJdbcWrapper<BalanceType>() {
141 @Override
142 protected void populateStatement(PreparedStatement preparedStatement) throws SQLException {
143 preparedStatement.setString(1, financialBalanceTypeCode);
144 }
145 @Override
146 protected BalanceType extractResult(ResultSet resultSet) throws SQLException {
147 BalanceType balanceType = new BalanceType();
148 balanceType.setFinancialBalanceTypeCode(financialBalanceTypeCode);
149 balanceType.setFinancialOffsetGenerationIndicator(KFSConstants.ParameterValues.YES.equals(resultSet.getString(1)) ? true : false);
150 balanceType.setFinBalanceTypeEncumIndicator(KFSConstants.ParameterValues.YES.equals(resultSet.getString(2)) ? true : false);
151 balanceType.setActive(KFSConstants.ParameterValues.YES.equals(resultSet.getString(3)) ? true : false);
152 return balanceType;
153 }
154 }.get(BalanceType.class);
155 }
156
157 public Chart getChart(final String chartOfAccountsCode) {
158 return new RetrievingJdbcWrapper<Chart>() {
159 @Override
160 protected void populateStatement(PreparedStatement preparedStatement) throws SQLException {
161 preparedStatement.setString(1, chartOfAccountsCode);
162 }
163 @Override
164 protected Chart extractResult(ResultSet resultSet) throws SQLException {
165 Chart chart = new Chart();
166 chart.setChartOfAccountsCode(chartOfAccountsCode);
167 chart.setActive(KFSConstants.ParameterValues.YES.equals(resultSet.getString(1)) ? true : false);
168 chart.setFundBalanceObjectCode(resultSet.getString(4));
169 chart.setFinancialCashObjectCode(resultSet.getString(2));
170 chart.setFinAccountsPayableObjectCode(resultSet.getString(3));
171 return chart;
172 }
173 }.get(Chart.class);
174 }
175
176 public IndirectCostRecoveryType getIndirectCostRecoveryType(final String accountIcrTypeCode) {
177 return new RetrievingJdbcWrapper<IndirectCostRecoveryType>() {
178 @Override
179 protected void populateStatement(PreparedStatement preparedStatement) throws SQLException {
180 preparedStatement.setString(1, accountIcrTypeCode);
181 }
182 @Override
183 protected IndirectCostRecoveryType extractResult(ResultSet resultSet) throws SQLException {
184 IndirectCostRecoveryType indirectCostRecoveryType = new IndirectCostRecoveryType();
185 indirectCostRecoveryType.setActive(KFSConstants.ParameterValues.YES.equals(resultSet.getString(1)) ? true : false);
186 return indirectCostRecoveryType;
187 }
188 }.get(IndirectCostRecoveryType.class);
189 }
190
191 public ObjectCode getObjectCode(final Integer universityFiscalYear, final String chartOfAccountsCode, final String financialObjectCode) {
192 return new RetrievingJdbcWrapper<ObjectCode>() {
193 @Override
194 protected void populateStatement(PreparedStatement preparedStatement) throws SQLException {
195 preparedStatement.setInt(1, universityFiscalYear);
196 preparedStatement.setString(2, chartOfAccountsCode);
197 preparedStatement.setString(3, financialObjectCode);
198 }
199 @Override
200 protected ObjectCode extractResult(ResultSet resultSet) throws SQLException {
201 ObjectCode objectCode = new ObjectCode();
202 objectCode.setUniversityFiscalYear(universityFiscalYear);
203 objectCode.setChartOfAccountsCode(chartOfAccountsCode);
204 objectCode.setFinancialObjectCode(financialObjectCode);
205 objectCode.setFinancialObjectTypeCode(resultSet.getString(1));
206 objectCode.setFinancialObjectSubTypeCode(resultSet.getString(2));
207 objectCode.setFinancialObjectLevelCode(resultSet.getString(3));
208 objectCode.setActive(KFSConstants.ParameterValues.YES.equals(resultSet.getString(4)) ? true : false);
209 objectCode.setReportsToChartOfAccountsCode(resultSet.getString(5));
210 objectCode.setReportsToFinancialObjectCode(resultSet.getString(6));
211 return objectCode;
212 }
213 }.get(ObjectCode.class);
214 }
215
216 public ObjectLevel getObjectLevel(final String chartOfAccountsCode, final String financialObjectLevelCode) {
217 return new RetrievingJdbcWrapper<ObjectLevel>() {
218 @Override
219 protected void populateStatement(PreparedStatement preparedStatement) throws SQLException {
220 preparedStatement.setString(1, chartOfAccountsCode);
221 preparedStatement.setString(2, financialObjectLevelCode);
222 }
223 @Override
224 protected ObjectLevel extractResult(ResultSet resultSet) throws SQLException {
225 ObjectLevel objectLevel = new ObjectLevel();
226 objectLevel.setChartOfAccountsCode(chartOfAccountsCode);
227 objectLevel.setFinancialObjectLevelCode(financialObjectLevelCode);
228 objectLevel.setFinancialConsolidationObjectCode(resultSet.getString(1));
229 return objectLevel;
230 }
231 }.get(ObjectLevel.class);
232 }
233
234 public ObjectType getObjectType(final String financialObjectTypeCode) {
235 return new RetrievingJdbcWrapper<ObjectType>() {
236 @Override
237 protected void populateStatement(PreparedStatement preparedStatement) throws SQLException {
238 preparedStatement.setString(1, financialObjectTypeCode);
239 }
240 @Override
241 protected ObjectType extractResult(ResultSet resultSet) throws SQLException {
242 ObjectType objectType = new ObjectType();
243 objectType.setCode(financialObjectTypeCode);
244 objectType.setFundBalanceIndicator(KFSConstants.ParameterValues.YES.equals(resultSet.getString(1)) ? true : false);
245 objectType.setFinObjectTypeDebitcreditCd(resultSet.getString(2));
246 objectType.setFinObjectTypeIcrSelectionIndicator(KFSConstants.ParameterValues.YES.equals(resultSet.getString(3)) ? true : false);
247 objectType.setActive(KFSConstants.ParameterValues.YES.equals(resultSet.getString(4)) ? true : false);
248 return objectType;
249 }
250 }.get(ObjectType.class);
251 }
252
253 public OffsetDefinition getOffsetDefinition(final Integer universityFiscalYear, final String chartOfAccountsCode, final String financialDocumentTypeCode, final String financialBalanceTypeCode) {
254 return new RetrievingJdbcWrapper<OffsetDefinition>() {
255 @Override
256 protected void populateStatement(PreparedStatement preparedStatement) throws SQLException {
257 preparedStatement.setInt(1, universityFiscalYear);
258 preparedStatement.setString(2, chartOfAccountsCode);
259 preparedStatement.setString(3, financialDocumentTypeCode);
260 preparedStatement.setString(4, financialBalanceTypeCode);
261 }
262 @Override
263 protected OffsetDefinition extractResult(ResultSet resultSet) throws SQLException {
264 OffsetDefinition offsetDefinition = new OffsetDefinition();
265 offsetDefinition.setUniversityFiscalYear(universityFiscalYear);
266 offsetDefinition.setChartOfAccountsCode(chartOfAccountsCode);
267 offsetDefinition.setFinancialDocumentTypeCode(financialDocumentTypeCode);
268 offsetDefinition.setFinancialBalanceTypeCode(financialBalanceTypeCode);
269 offsetDefinition.setFinancialObjectCode(resultSet.getString(1));
270 return offsetDefinition;
271 }
272 }.get(OffsetDefinition.class);
273 }
274
275 public Organization getOrganization(final String chartOfAccountsCode, final String organizationCode) {
276 return new RetrievingJdbcWrapper<Organization>() {
277 @Override
278 protected void populateStatement(PreparedStatement preparedStatement) throws SQLException {
279 preparedStatement.setString(1, chartOfAccountsCode);
280 preparedStatement.setString(2, organizationCode);
281 }
282 @Override
283 protected Organization extractResult(ResultSet resultSet) throws SQLException {
284 Organization organization = new Organization();
285 organization.setChartOfAccountsCode(chartOfAccountsCode);
286 organization.setOrganizationCode(organizationCode);
287 organization.setOrganizationPlantChartCode(resultSet.getString(1));
288 organization.setOrganizationPlantAccountNumber(resultSet.getString(2));
289 organization.setCampusPlantChartCode(resultSet.getString(3));
290 organization.setCampusPlantAccountNumber(resultSet.getString(4));
291 return organization;
292 }
293 }.get(Organization.class);
294 }
295
296 public ProjectCode getProjectCode(final String financialSystemProjectCode) {
297 return new RetrievingJdbcWrapper<ProjectCode>() {
298 @Override
299 protected void populateStatement(PreparedStatement preparedStatement) throws SQLException {
300 preparedStatement.setString(1, financialSystemProjectCode);
301 }
302 @Override
303 protected ProjectCode extractResult(ResultSet resultSet) throws SQLException {
304 ProjectCode projectCode = new ProjectCode();
305 projectCode.setCode(financialSystemProjectCode);
306 projectCode.setActive(KFSConstants.ParameterValues.YES.equals(resultSet.getString(1)) ? true : false);
307 return projectCode;
308 }
309 }.get(ProjectCode.class);
310 }
311
312 public SubAccount getSubAccount(final String chartOfAccountsCode, final String accountNumber, final String subAccountNumber) {
313 return new RetrievingJdbcWrapper<SubAccount>() {
314 @Override
315 protected void populateStatement(PreparedStatement preparedStatement) throws SQLException {
316 preparedStatement.setString(1, chartOfAccountsCode);
317 preparedStatement.setString(2, accountNumber);
318 preparedStatement.setString(3, subAccountNumber);
319 }
320 @Override
321 protected SubAccount extractResult(ResultSet resultSet) throws SQLException {
322 SubAccount subAccount = new SubAccount();
323 subAccount.setChartOfAccountsCode(chartOfAccountsCode);
324 subAccount.setAccountNumber(accountNumber);
325 subAccount.setSubAccountNumber(subAccountNumber);
326 subAccount.setActive(KFSConstants.ParameterValues.YES.equals(resultSet.getString(1)) ? true : false);
327 return subAccount;
328 }
329 }.get(SubAccount.class);
330 }
331
332 public SubFundGroup getSubFundGroup(final String subFundGroupCode) {
333 return new RetrievingJdbcWrapper<SubFundGroup>() {
334 @Override
335 protected void populateStatement(PreparedStatement preparedStatement) throws SQLException {
336 preparedStatement.setString(1, subFundGroupCode);
337 }
338 @Override
339 protected SubFundGroup extractResult(ResultSet resultSet) throws SQLException {
340 SubFundGroup subFundGroup = new SubFundGroup();
341 subFundGroup.setSubFundGroupCode(subFundGroupCode);
342 subFundGroup.setFundGroupCode(resultSet.getString(1));
343 return subFundGroup;
344 }
345 }.get(SubFundGroup.class);
346 }
347
348 public SubObjectCode getSubObjectCode(final Integer universityFiscalYear, final String chartOfAccountsCode, final String accountNumber, final String financialObjectCode, final String financialSubObjectCode) {
349 return new RetrievingJdbcWrapper<SubObjectCode>() {
350 @Override
351 protected void populateStatement(PreparedStatement preparedStatement) throws SQLException {
352 preparedStatement.setInt(1, universityFiscalYear);
353 preparedStatement.setString(2, chartOfAccountsCode);
354 preparedStatement.setString(3, accountNumber);
355 preparedStatement.setString(4, financialObjectCode);
356 preparedStatement.setString(5, financialSubObjectCode);
357 }
358 @Override
359 protected SubObjectCode extractResult(ResultSet resultSet) throws SQLException {
360 SubObjectCode subObjectCode = new SubObjectCode();
361 subObjectCode.setUniversityFiscalYear(universityFiscalYear);
362 subObjectCode.setChartOfAccountsCode(chartOfAccountsCode);
363 subObjectCode.setAccountNumber(accountNumber);
364 subObjectCode.setFinancialObjectCode(financialObjectCode);
365 subObjectCode.setFinancialSubObjectCode(financialSubObjectCode);
366 subObjectCode.setActive(KFSConstants.ParameterValues.YES.equals(resultSet.getString(1)) ? true : false);
367 return subObjectCode;
368 }
369 }.get(SubObjectCode.class);
370 }
371 }