001: /*
002: * Copyright 2007 The Kuali Foundation.
003: *
004: * Licensed under the Educational Community License, Version 1.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/ecl1.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.module.gl.dao.jdbc;
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.core.dao.jdbc.PlatformAwareDaoBaseJdbc;
028: import org.kuali.core.dbplatform.RawSQL;
029: import org.kuali.module.gl.dao.YearEndDao;
030: import org.springframework.jdbc.core.RowMapper;
031:
032: /**
033: * A JDBC implementation of the YearEndDao, built mainly because OJB is darn slow at some queries
034: */
035: @RawSQL
036: public class YearEndDaoJdbc extends PlatformAwareDaoBaseJdbc implements
037: YearEndDao {
038:
039: // All of the Comparators and RowMappers are stateless, so I can simply create them as variables and avoid unnecessary object
040: // creation
041: private final Comparator<Map<String, String>> subFundGroupPrimaryKeyComparator = new Comparator<Map<String, String>>() {
042: public int compare(Map<String, String> firstSubFundGroupPK,
043: Map<String, String> secondSubFundGroupPK) {
044: return firstSubFundGroupPK.get("subFundGroupCode")
045: .compareTo(
046: secondSubFundGroupPK
047: .get("subFundGroupCode"));
048: }
049: };
050:
051: private final Comparator<Map<String, String>> priorYearAccountPrimaryKeyComparator = new Comparator<Map<String, String>>() {
052: public int compare(Map<String, String> firstPriorYearPK,
053: Map<String, String> secondPriorYearPK) {
054: if (firstPriorYearPK.get("chartOfAccountsCode").equals(
055: secondPriorYearPK.get("chartOfAccountsCode"))) {
056: return firstPriorYearPK.get("accountNumber").compareTo(
057: secondPriorYearPK.get("accountNumber"));
058: } else {
059: return firstPriorYearPK.get("chartOfAccountsCode")
060: .compareTo(
061: secondPriorYearPK
062: .get("chartOfAccountsCode"));
063: }
064: }
065: };
066:
067: private final RowMapper subFundGroupRowMapper = new RowMapper() {
068: public Object mapRow(ResultSet rs, int rowNum)
069: throws SQLException {
070: Map<String, String> subFundGroupKey = new LinkedHashMap<String, String>();
071: subFundGroupKey.put("subFundGroupCode", rs
072: .getString("sub_fund_grp_cd"));
073: return subFundGroupKey;
074: }
075: };
076:
077: private final RowMapper priorYearAccountRowMapper = new RowMapper() {
078: public Object mapRow(ResultSet rs, int rowNum)
079: throws SQLException {
080: Map<String, String> keyMap = new LinkedHashMap<String, String>();
081: keyMap.put("chartOfAccountsCode", rs
082: .getString("fin_coa_cd"));
083: keyMap.put("accountNumber", rs.getString("account_nbr"));
084: return keyMap;
085: }
086: };
087:
088: /**
089: * Queries the databse to find missing prior year accounts
090: *
091: * @param balanceFiscalyear the fiscal year of balances to check for missing prior year accounts for
092: * @return a Set of Maps holding the primary keys of missing prior year accounts
093: * @see org.kuali.module.gl.dao.YearEndDao#findKeysOfMissingPriorYearAccountsForBalances(java.lang.Integer)
094: */
095: @RawSQL
096: public Set<Map<String, String>> findKeysOfMissingPriorYearAccountsForBalances(
097: Integer balanceFiscalYear) {
098: // 1. get a sorted list of the prior year account keys that are used by balances for the given fiscal year
099: List priorYearKeys = getJdbcTemplate()
100: .query(
101: "select distinct fin_coa_cd, account_nbr from gl_balance_t where univ_fiscal_yr = ? order by fin_coa_cd, account_nbr",
102: new Object[] { balanceFiscalYear },
103: priorYearAccountRowMapper);
104:
105: // 2. go through that list, finding which prior year accounts don't show up in the database
106: return selectMissingPriorYearAccounts(priorYearKeys);
107: }
108:
109: /**
110: * 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
111: * given set
112: *
113: * @param priorYearKeys the prior year keys to search for
114: * @return the set of those prior year accounts that are missing
115: */
116: @RawSQL
117: private Set<Map<String, String>> selectMissingPriorYearAccounts(
118: List priorYearKeys) {
119: Set<Map<String, String>> missingPriorYears = new TreeSet<Map<String, String>>(
120: priorYearAccountPrimaryKeyComparator);
121: for (Object priorYearKeyAsObject : priorYearKeys) {
122: Map<String, String> priorYearKey = (Map<String, String>) priorYearKeyAsObject;
123: int count = getJdbcTemplate()
124: .queryForInt(
125: "select count(*) from ca_prior_yr_acct_t where fin_coa_cd = ? and account_nbr = ? order by sub_fund_grp_cd",
126: new Object[] {
127: priorYearKey
128: .get("chartOfAccountsCode"),
129: priorYearKey.get("accountNumber") });
130: if (count == 0) {
131: missingPriorYears.add(priorYearKey);
132: }
133: }
134: return missingPriorYears;
135: }
136:
137: /**
138: * Queries the database to find missing sub fund groups
139: *
140: * @param balanceFiscalYear the fiscal year of the balance to find missing sub fund groups for
141: * @return a Set of Maps holding the primary keys of missing sub fund groups
142: * @see org.kuali.module.gl.dao.YearEndDao#findKeysOfMissingSubFundGroupsForBalances(java.lang.Integer)
143: */
144: @RawSQL
145: public Set<Map<String, String>> findKeysOfMissingSubFundGroupsForBalances(
146: Integer balanceFiscalYear) {
147: // see algorithm for findKeysOfMissingPriorYearAccountsForBalances
148: List subFundGroupKeys = getJdbcTemplate()
149: .query(
150: "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",
151: new Object[] { balanceFiscalYear },
152: subFundGroupRowMapper);
153: return selectMissingSubFundGroups(subFundGroupKeys);
154: }
155:
156: /**
157: * This method puts all of the sub fund groups that are in the given list of subFundGroupKeys but aren't in the database into
158: * the given set
159: *
160: * @param subFundGroupKeys the list of sub fund group keys to search through
161: * @return a set of those sub fund group keys that are missing
162: */
163: @RawSQL
164: private Set<Map<String, String>> selectMissingSubFundGroups(
165: List subFundGroupKeys) {
166: Set<Map<String, String>> missingSubFundGroups = new TreeSet<Map<String, String>>(
167: subFundGroupPrimaryKeyComparator);
168: for (Object subFundGroupKeyAsObject : subFundGroupKeys) {
169: Map<String, String> subFundGroupKey = (Map<String, String>) subFundGroupKeyAsObject;
170: int count = getJdbcTemplate()
171: .queryForInt(
172: "select count(*) from ca_sub_fund_grp_t where sub_fund_grp_cd = ?",
173: new Object[] { subFundGroupKey
174: .get("subFundGroupCode") });
175: if (count == 0) {
176: missingSubFundGroups.add(subFundGroupKey);
177: }
178: }
179: return missingSubFundGroups;
180: }
181:
182: /**
183: * Queries the databsae to find missing prior year account records referred to by encumbrance records
184: *
185: * @param encumbranceFiscalYear the fiscal year of balances to find missing encumbrance records for
186: * @return a Set of Maps holding the primary keys of missing prior year accounts
187: * @see org.kuali.module.gl.dao.YearEndDao#findKeysOfMissingPriorYearAccountsForOpenEncumbrances(java.lang.Integer)
188: */
189: @RawSQL
190: public Set<Map<String, String>> findKeysOfMissingPriorYearAccountsForOpenEncumbrances(
191: Integer encumbranceFiscalYear) {
192: List priorYearKeys = getJdbcTemplate()
193: .query(
194: "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",
195: new Object[] { encumbranceFiscalYear },
196: priorYearAccountRowMapper);
197: return selectMissingPriorYearAccounts(priorYearKeys);
198: }
199:
200: /**
201: * Queries the database to find missing sub fund group records referred to by encumbrances
202: *
203: * @param encumbranceFiscalYear the fiscal year of encumbrances to find missing sub fund group records for
204: * @return a Set of Maps holding the primary keys of missing sub fund group records
205: * @see org.kuali.module.gl.dao.YearEndDao#findKeysOfMissingSubFundGroupsForOpenEncumbrances(java.lang.Integer)
206: */
207: @RawSQL
208: public Set<Map<String, String>> findKeysOfMissingSubFundGroupsForOpenEncumbrances(
209: Integer encumbranceFiscalYear) {
210: List subFundGroupKeys = getJdbcTemplate()
211: .query(
212: "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",
213: new Object[] { encumbranceFiscalYear },
214: subFundGroupRowMapper);
215: return selectMissingSubFundGroups(subFundGroupKeys);
216: }
217:
218: }
|