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.gl.batch.dataaccess.impl;
017
018 import java.sql.ResultSet;
019 import java.sql.SQLException;
020 import java.util.Comparator;
021 import java.util.LinkedHashMap;
022 import java.util.List;
023 import java.util.Map;
024 import java.util.Set;
025 import java.util.TreeSet;
026
027 import org.kuali.kfs.gl.batch.dataaccess.YearEndDao;
028 import org.kuali.rice.kns.dao.jdbc.PlatformAwareDaoBaseJdbc;
029 import org.springframework.jdbc.core.RowMapper;
030
031 /**
032 * A JDBC implementation of the YearEndDao, built mainly because OJB is darn slow at some queries
033 */
034 public class YearEndDaoJdbc extends PlatformAwareDaoBaseJdbc implements YearEndDao {
035
036 // All of the Comparators and RowMappers are stateless, so I can simply create them as variables and avoid unnecessary object
037 // creation
038 protected Comparator<Map<String, String>> subFundGroupPrimaryKeyComparator = new Comparator<Map<String, String>>() {
039 public int compare(Map<String, String> firstSubFundGroupPK, Map<String, String> secondSubFundGroupPK) {
040 return firstSubFundGroupPK.get("subFundGroupCode").compareTo(secondSubFundGroupPK.get("subFundGroupCode"));
041 }
042 };
043
044 protected Comparator<Map<String, String>> priorYearAccountPrimaryKeyComparator = new Comparator<Map<String, String>>() {
045 public int compare(Map<String, String> firstPriorYearPK, Map<String, String> secondPriorYearPK) {
046 if (firstPriorYearPK.get("chartOfAccountsCode").equals(secondPriorYearPK.get("chartOfAccountsCode"))) {
047 return firstPriorYearPK.get("accountNumber").compareTo(secondPriorYearPK.get("accountNumber"));
048 }
049 else {
050 return firstPriorYearPK.get("chartOfAccountsCode").compareTo(secondPriorYearPK.get("chartOfAccountsCode"));
051 }
052 }
053 };
054
055 protected RowMapper subFundGroupRowMapper = new RowMapper() {
056 public Object mapRow(ResultSet rs, int rowNum) throws SQLException {
057 Map<String, String> subFundGroupKey = new LinkedHashMap<String, String>();
058 subFundGroupKey.put("subFundGroupCode", rs.getString("sub_fund_grp_cd"));
059 return subFundGroupKey;
060 }
061 };
062
063 protected RowMapper priorYearAccountRowMapper = new RowMapper() {
064 public Object mapRow(ResultSet rs, int rowNum) throws SQLException {
065 Map<String, String> keyMap = new LinkedHashMap<String, String>();
066 keyMap.put("chartOfAccountsCode", rs.getString("fin_coa_cd"));
067 keyMap.put("accountNumber", rs.getString("account_nbr"));
068 return keyMap;
069 }
070 };
071
072 /**
073 * Queries the databse to find missing prior year accounts
074 *
075 * @param balanceFiscalyear the fiscal year of balances to check for missing prior year accounts for
076 * @return a Set of Maps holding the primary keys of missing prior year accounts
077 * @see org.kuali.kfs.gl.batch.dataaccess.YearEndDao#findKeysOfMissingPriorYearAccountsForBalances(java.lang.Integer)
078 */
079 public Set<Map<String, String>> findKeysOfMissingPriorYearAccountsForBalances(Integer balanceFiscalYear) {
080 // 1. get a sorted list of the prior year account keys that are used by balances for the given fiscal year
081 List priorYearKeys = getJdbcTemplate().query("select distinct fin_coa_cd, account_nbr from GL_BALANCE_T where univ_fiscal_yr = ? order by fin_coa_cd, account_nbr", new Object[] { balanceFiscalYear }, priorYearAccountRowMapper);
082
083 // 2. go through that list, finding which prior year accounts don't show up in the database
084 return selectMissingPriorYearAccounts(priorYearKeys);
085 }
086
087 /**
088 * This method puts all of the prior year accounts that aren't in the database, based on the list of keys sent in, into the
089 * given set
090 *
091 * @param priorYearKeys the prior year keys to search for
092 * @return the set of those prior year accounts that are missing
093 */
094 protected Set<Map<String, String>> selectMissingPriorYearAccounts(List priorYearKeys) {
095 Set<Map<String, String>> missingPriorYears = new TreeSet<Map<String, String>>(priorYearAccountPrimaryKeyComparator);
096 for (Object priorYearKeyAsObject : priorYearKeys) {
097 Map<String, String> priorYearKey = (Map<String, String>) priorYearKeyAsObject;
098 int count = getJdbcTemplate().queryForInt("select count(*) from CA_PRIOR_YR_ACCT_T where fin_coa_cd = ? and account_nbr = ?", new Object[] { priorYearKey.get("chartOfAccountsCode"), priorYearKey.get("accountNumber") });
099 if (count == 0) {
100 missingPriorYears.add(priorYearKey);
101 }
102 }
103 return missingPriorYears;
104 }
105
106 /**
107 * Queries the database to find missing sub fund groups
108 *
109 * @param balanceFiscalYear the fiscal year of the balance to find missing sub fund groups for
110 * @return a Set of Maps holding the primary keys of missing sub fund groups
111 * @see org.kuali.kfs.gl.batch.dataaccess.YearEndDao#findKeysOfMissingSubFundGroupsForBalances(java.lang.Integer)
112 */
113 public Set<Map<String, String>> findKeysOfMissingSubFundGroupsForBalances(Integer balanceFiscalYear) {
114 // see algorithm for findKeysOfMissingPriorYearAccountsForBalances
115 List subFundGroupKeys = getJdbcTemplate().query("select distinct CA_PRIOR_YR_ACCT_T.sub_fund_grp_cd from CA_PRIOR_YR_ACCT_T, GL_BALANCE_T where CA_PRIOR_YR_ACCT_T.fin_coa_cd = GL_BALANCE_T.fin_coa_cd and CA_PRIOR_YR_ACCT_T.account_nbr = GL_BALANCE_T.account_nbr and GL_BALANCE_T.univ_fiscal_yr = ? and CA_PRIOR_YR_ACCT_T.sub_fund_grp_cd is not null order by CA_PRIOR_YR_ACCT_T.sub_fund_grp_cd", new Object[] { balanceFiscalYear }, subFundGroupRowMapper);
116 return selectMissingSubFundGroups(subFundGroupKeys);
117 }
118
119 /**
120 * This method puts all of the sub fund groups that are in the given list of subFundGroupKeys but aren't in the database into
121 * the given set
122 *
123 * @param subFundGroupKeys the list of sub fund group keys to search through
124 * @return a set of those sub fund group keys that are missing
125 */
126 protected Set<Map<String, String>> selectMissingSubFundGroups(List subFundGroupKeys) {
127 Set<Map<String, String>> missingSubFundGroups = new TreeSet<Map<String, String>>(subFundGroupPrimaryKeyComparator);
128 for (Object subFundGroupKeyAsObject : subFundGroupKeys) {
129 Map<String, String> subFundGroupKey = (Map<String, String>) subFundGroupKeyAsObject;
130 int count = getJdbcTemplate().queryForInt("select count(*) from CA_SUB_FUND_GRP_T where sub_fund_grp_cd = ?", new Object[] { subFundGroupKey.get("subFundGroupCode") });
131 if (count == 0) {
132 missingSubFundGroups.add(subFundGroupKey);
133 }
134 }
135 return missingSubFundGroups;
136 }
137
138 /**
139 * Queries the databsae to find missing prior year account records referred to by encumbrance records
140 *
141 * @param encumbranceFiscalYear the fiscal year of balances to find missing encumbrance records for
142 * @return a Set of Maps holding the primary keys of missing prior year accounts
143 * @see org.kuali.kfs.gl.batch.dataaccess.YearEndDao#findKeysOfMissingPriorYearAccountsForOpenEncumbrances(java.lang.Integer)
144 */
145 public Set<Map<String, String>> findKeysOfMissingPriorYearAccountsForOpenEncumbrances(Integer encumbranceFiscalYear) {
146 List priorYearKeys = getJdbcTemplate().query("select distinct fin_coa_cd, account_nbr from GL_ENCUMBRANCE_T where univ_fiscal_yr = ? and acln_encum_amt <> acln_encum_cls_amt order by fin_coa_cd, account_nbr", new Object[] { encumbranceFiscalYear }, priorYearAccountRowMapper);
147 return selectMissingPriorYearAccounts(priorYearKeys);
148 }
149
150 /**
151 * Queries the database to find missing sub fund group records referred to by encumbrances
152 *
153 * @param encumbranceFiscalYear the fiscal year of encumbrances to find missing sub fund group records for
154 * @return a Set of Maps holding the primary keys of missing sub fund group records
155 * @see org.kuali.kfs.gl.batch.dataaccess.YearEndDao#findKeysOfMissingSubFundGroupsForOpenEncumbrances(java.lang.Integer)
156 */
157 public Set<Map<String, String>> findKeysOfMissingSubFundGroupsForOpenEncumbrances(Integer encumbranceFiscalYear) {
158 List subFundGroupKeys = getJdbcTemplate().query("select distinct CA_PRIOR_YR_ACCT_T.sub_fund_grp_cd from CA_PRIOR_YR_ACCT_T, GL_ENCUMBRANCE_T where CA_PRIOR_YR_ACCT_T.fin_coa_cd = GL_ENCUMBRANCE_T.fin_coa_cd and CA_PRIOR_YR_ACCT_T.account_nbr = GL_ENCUMBRANCE_T.account_nbr and GL_ENCUMBRANCE_T.univ_fiscal_yr = ? and GL_ENCUMBRANCE_T.acln_encum_amt <> GL_ENCUMBRANCE_T.acln_encum_cls_amt and CA_PRIOR_YR_ACCT_T.sub_fund_grp_cd is not null order by CA_PRIOR_YR_ACCT_T.sub_fund_grp_cd", new Object[] { encumbranceFiscalYear }, subFundGroupRowMapper);
159 return selectMissingSubFundGroups(subFundGroupKeys);
160 }
161
162 }