1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16 package org.kuali.ole.gl.batch.dataaccess.impl;
17
18 import java.sql.ResultSet;
19 import java.sql.SQLException;
20 import java.util.Comparator;
21 import java.util.LinkedHashMap;
22 import java.util.List;
23 import java.util.Map;
24 import java.util.Set;
25 import java.util.TreeSet;
26
27 import org.kuali.ole.gl.batch.dataaccess.YearEndDao;
28 import org.kuali.rice.core.framework.persistence.jdbc.dao.PlatformAwareDaoBaseJdbc;
29 import org.springframework.jdbc.core.RowMapper;
30
31
32
33
34 public class YearEndDaoJdbc extends PlatformAwareDaoBaseJdbc implements YearEndDao {
35
36
37
38 protected Comparator<Map<String, String>> subFundGroupPrimaryKeyComparator = new Comparator<Map<String, String>>() {
39 public int compare(Map<String, String> firstSubFundGroupPK, Map<String, String> secondSubFundGroupPK) {
40 return firstSubFundGroupPK.get("subFundGroupCode").compareTo(secondSubFundGroupPK.get("subFundGroupCode"));
41 }
42 };
43
44 protected Comparator<Map<String, String>> priorYearAccountPrimaryKeyComparator = new Comparator<Map<String, String>>() {
45 public int compare(Map<String, String> firstPriorYearPK, Map<String, String> secondPriorYearPK) {
46 if (firstPriorYearPK.get("chartOfAccountsCode").equals(secondPriorYearPK.get("chartOfAccountsCode"))) {
47 return firstPriorYearPK.get("accountNumber").compareTo(secondPriorYearPK.get("accountNumber"));
48 }
49 else {
50 return firstPriorYearPK.get("chartOfAccountsCode").compareTo(secondPriorYearPK.get("chartOfAccountsCode"));
51 }
52 }
53 };
54
55 protected RowMapper subFundGroupRowMapper = new RowMapper() {
56 public Object mapRow(ResultSet rs, int rowNum) throws SQLException {
57 Map<String, String> subFundGroupKey = new LinkedHashMap<String, String>();
58 subFundGroupKey.put("subFundGroupCode", rs.getString("sub_fund_grp_cd"));
59 return subFundGroupKey;
60 }
61 };
62
63 protected RowMapper priorYearAccountRowMapper = new RowMapper() {
64 public Object mapRow(ResultSet rs, int rowNum) throws SQLException {
65 Map<String, String> keyMap = new LinkedHashMap<String, String>();
66 keyMap.put("chartOfAccountsCode", rs.getString("fin_coa_cd"));
67 keyMap.put("accountNumber", rs.getString("account_nbr"));
68 return keyMap;
69 }
70 };
71
72
73
74
75
76
77
78
79 public Set<Map<String, String>> findKeysOfMissingPriorYearAccountsForBalances(Integer balanceFiscalYear) {
80
81 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);
82
83
84 return selectMissingPriorYearAccounts(priorYearKeys);
85 }
86
87
88
89
90
91
92
93
94 protected Set<Map<String, String>> selectMissingPriorYearAccounts(List priorYearKeys) {
95 Set<Map<String, String>> missingPriorYears = new TreeSet<Map<String, String>>(priorYearAccountPrimaryKeyComparator);
96 for (Object priorYearKeyAsObject : priorYearKeys) {
97 Map<String, String> priorYearKey = (Map<String, String>) priorYearKeyAsObject;
98 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") });
99 if (count == 0) {
100 missingPriorYears.add(priorYearKey);
101 }
102 }
103 return missingPriorYears;
104 }
105
106
107
108
109
110
111
112
113 public Set<Map<String, String>> findKeysOfMissingSubFundGroupsForBalances(Integer balanceFiscalYear) {
114
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
121
122
123
124
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
140
141
142
143
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
152
153
154
155
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 }