Source code

001/*
002 * Copyright 2009 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 */
016package org.kuali.ole.coa.batch.dataaccess.impl;
017
018import java.sql.PreparedStatement;
019import java.sql.ResultSet;
020import java.sql.SQLException;
021import java.util.HashMap;
022import java.util.List;
023import java.util.Map;
024
025import org.kuali.ole.coa.batch.dataaccess.LedgerReferenceValuePreparedStatementCachingDao;
026import org.kuali.ole.coa.businessobject.A21IndirectCostRecoveryAccount;
027import org.kuali.ole.coa.businessobject.A21SubAccount;
028import org.kuali.ole.coa.businessobject.Account;
029import org.kuali.ole.coa.businessobject.AccountingPeriod;
030import org.kuali.ole.coa.businessobject.BalanceType;
031import org.kuali.ole.coa.businessobject.Chart;
032import org.kuali.ole.coa.businessobject.IndirectCostRecoveryType;
033import org.kuali.ole.coa.businessobject.ObjectCode;
034import org.kuali.ole.coa.businessobject.ObjectLevel;
035import org.kuali.ole.coa.businessobject.ObjectType;
036import org.kuali.ole.coa.businessobject.OffsetDefinition;
037import org.kuali.ole.coa.businessobject.Organization;
038import org.kuali.ole.coa.businessobject.ProjectCode;
039import org.kuali.ole.coa.businessobject.SubAccount;
040import org.kuali.ole.coa.businessobject.SubFundGroup;
041import org.kuali.ole.coa.businessobject.SubObjectCode;
042import org.kuali.ole.sys.OLEConstants;
043import org.kuali.ole.sys.batch.dataaccess.impl.AbstractPreparedStatementCachingDaoJdbc;
044
045public class LedgerReferenceValuePreparedStatementCachingDaoJdbc extends AbstractPreparedStatementCachingDaoJdbc implements LedgerReferenceValuePreparedStatementCachingDao {
046    static final Map<String,String> sql = new HashMap<String,String>();
047    static {
048        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 = ?");
049        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 = ?");
050        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 = ?");
051        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 = ?");
052        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 = ?");
053        sql.put(RETRIEVE_PREFIX + ProjectCode.class, "select proj_active_cd from CA_PROJECT_T where project_cd = ?");
054        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 = ?");
055        sql.put(RETRIEVE_PREFIX + SubFundGroup.class, "select fund_grp_cd from CA_SUB_FUND_GRP_T where sub_fund_grp_cd = ?");
056        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 = ?");
057        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 = ?");
058        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 = ? ");
059        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 = ?");
060        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 = ?");
061        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 = ?");
062        sql.put(RETRIEVE_PREFIX + AccountingPeriod.class, "select row_actv_ind from SH_ACCT_PERIOD_T where univ_fiscal_yr = ? and univ_fiscal_prd_cd = ?");
063        sql.put(RETRIEVE_PREFIX + IndirectCostRecoveryType.class, "select ACCT_ICR_TYP_ACTV_IND from CA_ICR_TYPE_T where acct_icr_typ_cd = ?");
064    }
065
066    @Override
067    protected Map<String, String> getSql() {
068        return sql;
069    }
070
071    public A21SubAccount getA21SubAccount(final String chartOfAccountsCode, final String accountNumber, final String subAccountNumber) {
072        return new RetrievingJdbcWrapper<A21SubAccount>() {
073            @Override
074            protected void populateStatement(PreparedStatement preparedStatement) throws SQLException {
075                preparedStatement.setString(1, chartOfAccountsCode);
076                preparedStatement.setString(2, accountNumber);
077                preparedStatement.setString(3, subAccountNumber);
078            }
079            @Override
080            protected A21SubAccount extractResult(ResultSet resultSet) throws SQLException {
081                A21SubAccount a21SubAccount = new A21SubAccount();
082                a21SubAccount.setChartOfAccountsCode(chartOfAccountsCode);
083                a21SubAccount.setAccountNumber(accountNumber);
084                a21SubAccount.setSubAccountNumber(subAccountNumber);
085                a21SubAccount.setSubAccountTypeCode(resultSet.getString(1));
086                a21SubAccount.setCostShareChartOfAccountCode(resultSet.getString(2));
087                a21SubAccount.setCostShareSourceAccountNumber(resultSet.getString(3));
088                a21SubAccount.setCostShareSourceSubAccountNumber(resultSet.getString(4));
089                a21SubAccount.setIndirectCostRecoveryTypeCode(resultSet.getString(5));
090                a21SubAccount.setFinancialIcrSeriesIdentifier(resultSet.getString(6));
091                return a21SubAccount;
092            }
093        }.get(A21SubAccount.class);
094    }
095
096    @Override
097    public List<A21IndirectCostRecoveryAccount> getA21IndirectCostRecoveryAccounts(final String chartOfAccountsCode, final String accountNumber, final String subAccountNumber) {
098        return new RetrievingListJdbcWrapper<A21IndirectCostRecoveryAccount>() {
099            @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}