Java tutorial
/* * The Kuali Financial System, a comprehensive financial management system for higher education. * * Copyright 2005-2014 The Kuali Foundation * * This program is free software: you can redistribute it and/or modify * it under the terms of the GNU Affero General Public License as * published by the Free Software Foundation, either version 3 of the * License, or (at your option) any later version. * * This program is distributed in the hope that it will be useful, * but WITHOUT ANY WARRANTY; without even the implied warranty of * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the * GNU Affero General Public License for more details. * * You should have received a copy of the GNU Affero General Public License * along with this program. If not, see <http://www.gnu.org/licenses/>. */ package org.kuali.kfs.gl.dataaccess.impl; import java.io.IOException; import java.io.Writer; import java.sql.ResultSet; import java.sql.SQLException; import java.text.SimpleDateFormat; import java.util.ArrayList; import java.util.Collection; import java.util.Date; import java.util.List; import org.apache.commons.lang.StringUtils; import org.kuali.kfs.gl.dataaccess.IcrEncumbranceDao; import org.kuali.rice.core.api.util.type.KualiDecimal; import org.kuali.rice.core.framework.persistence.jdbc.dao.PlatformAwareDaoBaseJdbc; import org.springframework.dao.DataAccessException; import org.springframework.jdbc.core.ResultSetExtractor; public class IcrEncumbranceDaoJdbc extends PlatformAwareDaoBaseJdbc implements IcrEncumbranceDao { /** * @see org.kuali.kfs.gl.dataaccess.IcrEncumbranceDao#buildIcrEncumbranceFeed() */ @Override public void buildIcrEncumbranceFeed(Integer fiscalYear, final String fiscalPeriod, final String icrEncumbOriginCode, final Collection<String> icrEncumbBalanceTypes, final Collection<String> icrCostTypes, final String[] expenseObjectTypes, final String costShareSubAccountType, final Writer fw) throws IOException { final String rateSql = "select distinct t1.univ_fiscal_yr, t1.fin_coa_cd, t1.account_nbr, t1.sub_acct_nbr, " + getDbPlatform().getIsNullFunction("t3.fin_series_id", "t2.fin_series_id") + " fin_series_id, " + getDbPlatform().getIsNullFunction("t3.icr_typ_cd", "t2.acct_icr_typ_cd") + " acct_icr_typ_cd " + "from gl_encumbrance_t t1 join ca_account_t t2 on (t1.fin_coa_cd = t2.fin_coa_cd and t1.account_nbr = t2.account_nbr) " + "left join ca_a21_sub_acct_t t3 on (t1.fin_coa_cd = t3.fin_coa_cd and t1.account_nbr = t3.account_nbr and t1.sub_acct_nbr = t3.sub_acct_nbr) " + "where t1.fin_balance_typ_cd in (" + inString(icrEncumbBalanceTypes.size()) + ") and t1.fs_origin_cd <> ? " + "and t1.univ_fiscal_yr >= ? " + "and (t3.sub_acct_typ_cd is null or t3.sub_acct_typ_cd <> ?) " + "and acct_icr_typ_cd not in (" + inString(icrCostTypes.size()) + ")"; List<Object> queryArguments = new ArrayList<Object>(); for (String balanceType : icrEncumbBalanceTypes) { queryArguments.add(balanceType); } queryArguments.add(icrEncumbOriginCode); queryArguments.add(fiscalYear); queryArguments.add(costShareSubAccountType); for (String icrCostType : icrCostTypes) { queryArguments.add(icrCostType); } //prevent SQL errors in the event that the INDIRECT_COST_TYPES parameter contains no value if (icrCostTypes.size() < 1) { queryArguments.add("1"); } getJdbcTemplate().query(rateSql, queryArguments.toArray(), new ResultSetExtractor() { @Override public Object extractData(ResultSet rs) throws SQLException, DataAccessException { try { String newLine = System.getProperty("line.separator"); while (rs.next()) { String fin_series_id = rs.getString("fin_series_id"); String acct_icr_typ_cd = rs.getString("acct_icr_typ_cd"); String fiscalYear = rs.getString("univ_fiscal_yr"); String chartCode = rs.getString("fin_coa_cd"); String accountNbr = rs.getString("account_nbr"); String subAccountNbr = rs.getString("sub_acct_nbr"); List<Object> encArgs = new ArrayList<Object>(); encArgs.add(fin_series_id); encArgs.add(acct_icr_typ_cd); encArgs.add(fiscalYear); encArgs.add(chartCode); encArgs.add(accountNbr); encArgs.add(subAccountNbr); for (String balanceType : icrEncumbBalanceTypes) { encArgs.add(balanceType); } encArgs.add(icrEncumbOriginCode); for (String expenseObjectType : expenseObjectTypes) { encArgs.add(expenseObjectType); } executeEncumbranceSql(fiscalPeriod, icrEncumbOriginCode, icrEncumbBalanceTypes, expenseObjectTypes, encArgs.toArray(), fw); } } catch (SQLException e) { throw new RuntimeException(e); } catch (DataAccessException ed) { throw new RuntimeException(ed); } return null; } }); } /** * Retrieves and formats ICR Encumbrance information and writes output records to the file writer * * @param fiscalPeriod the current fiscal period * @param icrEncumbOriginCode the ICR origin code - system parameter INDIRECT_COST_RECOVERY_ENCUMBRANCE_ORIGINATION * @param icrEncumbBalanceTypes a list of balance types - system parameter INDIRECT_COST_RECOVERY_ENCUMBRANCE_BALANCE_TYPES * @param expenseObjectTypes a list of expense object types * @param encArgs a list of query arguments * @param fw the file writer */ protected void executeEncumbranceSql(final String fiscalPeriod, final String icrEncumbOriginCode, final Collection<String> icrEncumbBalanceTypes, final String[] expenseObjectTypes, Object[] encArgs, final Writer fw) { final String encumbSql = "select t1.univ_fiscal_yr, t1.fin_coa_cd, t1.account_nbr, t1.sub_acct_nbr, t5.fin_object_cd, t1.fin_balance_typ_cd, " + "t1.fdoc_typ_cd, t1.fdoc_nbr, " + "sum(" + getDbPlatform().getIsNullFunction("t1.acln_encum_amt - t1.acln_encum_cls_amt", "0") + " * " + getDbPlatform().getIsNullFunction("t5.awrd_icr_rate_pct", "0") + " * .01) encumb_amt " + "from gl_encumbrance_t t1 " + "join ca_icr_auto_entr_t t5 on t5.fin_series_id = ? and t5.univ_fiscal_yr = t1.univ_fiscal_yr " + "and t5.trn_debit_crdt_cd = 'D' " + "join ca_object_code_t t4 on t4.univ_fiscal_yr = t1.univ_fiscal_yr and t4.fin_coa_cd = t1.fin_coa_cd and t4.fin_object_cd = t1.fin_object_cd " + "where not exists (select 1 from ca_icr_excl_type_t where acct_icr_typ_cd = ? " + "and acct_icr_excl_typ_actv_ind = 'Y' and fin_object_cd = t1.fin_object_cd) " + "and t1.univ_fiscal_yr = ? and t1.fin_coa_cd = ? and t1.account_nbr = ? and t1.sub_acct_nbr = ? " + "and t1.fin_balance_typ_cd in (" + inString(icrEncumbBalanceTypes.size()) + ") and t1.fs_origin_cd <> ? " + "and t4.fin_obj_typ_cd in (" + inString(expenseObjectTypes.length) + ") group by t1.univ_fiscal_yr, t1.fin_coa_cd, t1.account_nbr, t1.sub_acct_nbr, t5.fin_object_cd, t1.fin_balance_typ_cd, " + "t1.fdoc_typ_cd, t1.fdoc_nbr"; getJdbcTemplate().query(encumbSql, encArgs, new ResultSetExtractor() { @Override public Object extractData(ResultSet rs) throws SQLException, DataAccessException { try { String newLine = System.getProperty("line.separator"); while (rs.next()) { String fiscalYear = rs.getString("univ_fiscal_yr"); String chartCode = rs.getString("fin_coa_cd"); String accountNbr = rs.getString("account_nbr"); String subAccountNbr = rs.getString("sub_acct_nbr"); String objectCode = rs.getString("fin_object_cd"); String balanceType = rs.getString("fin_balance_typ_cd"); String docType = rs.getString("fdoc_typ_cd"); String docNbr = rs.getString("fdoc_nbr"); KualiDecimal encumb_amt = new KualiDecimal(rs.getDouble("encumb_amt")); KualiDecimal current_amt = KualiDecimal.ZERO; Object[] icrArgs = new String[9]; icrArgs[0] = fiscalYear; icrArgs[1] = chartCode; icrArgs[2] = accountNbr; icrArgs[3] = subAccountNbr; icrArgs[4] = objectCode; icrArgs[5] = balanceType; icrArgs[6] = docType; icrArgs[7] = docNbr; icrArgs[8] = icrEncumbOriginCode; Double icrAmount = getCurrentEncumbranceAmount(icrArgs); if (icrAmount != null) { current_amt = new KualiDecimal(icrAmount); } KualiDecimal new_encumb_amt = encumb_amt.subtract(current_amt); if (new_encumb_amt.isZero()) { // ignore zero dollar amounts continue; } icrArgs = new String[3]; icrArgs[0] = fiscalYear; icrArgs[1] = chartCode; icrArgs[2] = objectCode; String objectTypeCode = getICRObjectTypeCode(icrArgs); String desc = "ICR Encumbrance " + docType + " " + docNbr; String debitCreditInd = "D"; if (new_encumb_amt.isNegative()) { debitCreditInd = "C"; } fw.write("" + fiscalYear // Fiscal year 1-4 + chartCode // Chart code 5-6 + accountNbr // Account Number 7-13 + StringUtils.rightPad(subAccountNbr, 5)// Sub Account 14-18 + objectCode // Object Code 19-22 + "---" // Sub Object 23-25 + balanceType // balance type code + objectTypeCode // Object Type 28-29 + fiscalPeriod // Fiscal Period 30-31 + StringUtils.rightPad(docType, 4) // Document Type 32-35 + icrEncumbOriginCode // Origin Code 36-37 + StringUtils.rightPad(docNbr, 14) // Doc Number 38-51 + StringUtils.rightPad("", 5, '0') // Entry Seq Nbr 52-56 + StringUtils.rightPad(StringUtils.substring(desc, 0, 40), 40) // Description 57-96 + StringUtils.leftPad(new_encumb_amt.abs().toString(), 21, '0') // Amount 97-116 + debitCreditInd // Debit/Credit 117-117 + new SimpleDateFormat("yyyy-MM-dd").format(new Date()) // Trans Date 118-127 + " " // Org Doc Nbr 128-137 + " " // Project Code 138-147 + " " // orig ref id 148-155 + " " // ref doc type 156-159 + " " // ref origin code 160-161 + " " // ref doc number 162-175 + " " // reversal date 176-185 + "D" // Enc update code 186-186 ); fw.write(newLine); fw.flush(); } } catch (SQLException e) { throw new RuntimeException(e); } catch (DataAccessException ed) { throw new RuntimeException(ed); } catch (IOException ex) { throw new RuntimeException(ex); } return null; } }); } /** * Returns the current encumbrance amount * * @param icrEncumbOriginCode the ICR origin code - system parameter INDIRECT_COST_RECOVERY_ENCUMBRANCE_ORIGINATION * @param icrArgs a list of query arguments * @return the current encumbrance amount if found, null otherwise */ protected Double getCurrentEncumbranceAmount(Object[] icrArgs) { final String icrSql = "select sum(" + getDbPlatform().getIsNullFunction("acln_encum_amt - acln_encum_cls_amt", "0") + ") current_amt " + "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_balance_typ_cd = ? and fdoc_typ_cd = ? and fdoc_nbr = ? and fs_origin_cd = ?"; Double icrAmount = (Double) getJdbcTemplate().query(icrSql, icrArgs, new ResultSetExtractor() { @Override public Object extractData(ResultSet rs) throws SQLException, DataAccessException { try { if (rs.next()) { return rs.getDouble("current_amt"); } return null; } catch (SQLException e) { throw new RuntimeException(e); } catch (DataAccessException ed) { throw new RuntimeException(ed); } } }); return icrAmount; } /** * Returns the object type code for the object code associated with the ICR Rate * * @param icrArgs a list of query arguments * @return the object type code if found, null otherwise */ protected String getICRObjectTypeCode(Object[] icrArgs) { final String icrSql = "select fin_obj_typ_cd " + "from ca_object_code_t where univ_fiscal_yr = ? and fin_coa_cd = ? and fin_object_cd = ?"; String objectTypeCode = (String) getJdbcTemplate().query(icrSql, icrArgs, new ResultSetExtractor() { @Override public Object extractData(ResultSet rs) throws SQLException, DataAccessException { try { if (rs.next()) { return rs.getString("fin_obj_typ_cd"); } return null; } catch (SQLException e) { throw new RuntimeException(e); } catch (DataAccessException ed) { throw new RuntimeException(ed); } } }); return objectTypeCode; } /** * Creates a String of SQL parameters markers specified by the size of an array or collection * * @param int the number of parameter markers to include in an SQL in clause * @return the resulting String */ protected String inString(int arraySize) { final String inClause; //prevent SQL errors in the event of an empty array if (arraySize < 1) { inClause = "?"; } else { inClause = StringUtils.repeat("?", ",", arraySize); } return inClause; } }