001/*
002 * Copyright 2007 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.gl.batch.dataaccess.impl;
017
018import java.sql.ResultSet;
019import java.sql.SQLException;
020import java.util.Comparator;
021import java.util.LinkedHashMap;
022import java.util.List;
023import java.util.Map;
024import java.util.Set;
025import java.util.TreeSet;
026
027import org.kuali.ole.gl.batch.dataaccess.YearEndDao;
028import org.kuali.rice.core.framework.persistence.jdbc.dao.PlatformAwareDaoBaseJdbc;
029import 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 */
034public 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.ole.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.ole.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.ole.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.ole.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}