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.module.bc.document.dataaccess.impl;
017
018 import java.math.BigDecimal;
019 import java.util.ArrayList;
020
021 import org.kuali.kfs.module.bc.BCConstants;
022 import org.kuali.kfs.module.bc.batch.dataaccess.impl.SQLForStep;
023 import org.kuali.kfs.module.bc.document.dataaccess.BudgetConstructionReasonStatisticsReportDao;
024 import org.kuali.rice.kns.service.PersistenceService;
025 import org.kuali.rice.kns.util.Guid;
026 import org.kuali.rice.kns.util.KualiDecimal;
027
028 /**
029 * build the set of rows for the salary reason statistics report
030 */
031
032 public class BudgetConstructionReasonStatisticsReportDaoJdbc extends BudgetConstructionDaoJdbcBase implements BudgetConstructionReasonStatisticsReportDao {
033 private static org.apache.log4j.Logger LOG = org.apache.log4j.Logger.getLogger(BudgetConstructionReasonStatisticsReportDaoJdbc.class);
034
035 private static ArrayList<SQLForStep> updateReportsReasonStatisticsTable = new ArrayList<SQLForStep>(10);
036 private static ArrayList<SQLForStep> reportReasonStatisticsWithThreshold = new ArrayList<SQLForStep>(3);
037 private static ArrayList<SQLForStep> reportReasonStatisticsWithNoThreshold = new ArrayList<SQLForStep>(2);
038
039 private PersistenceService persistenceService;
040
041 public BudgetConstructionReasonStatisticsReportDaoJdbc() {
042
043 ArrayList<Integer> insertionPoints = new ArrayList<Integer>(10);
044
045 // builds and updates ReasonStatisticsReports
046
047 /* get all emplids for the selections if we are doing the report using a threshold*/
048 StringBuilder sqlText = new StringBuilder(2500);
049 sqlText.append("INSERT INTO LD_BCN_BUILD_EXSALTOT01_MT (SESID, EMPLID) \n");
050 sqlText.append("SELECT DISTINCT ?, bcaf.emplid \n");
051 sqlText.append("FROM LD_BCN_CTRL_LIST_T ctrl, LD_PNDBC_APPTFND_T bcaf, LD_BCN_OBJ_PICK_T pick \n");
052 sqlText.append("WHERE ctrl.person_unvl_id = ? \n");
053 sqlText.append(" AND bcaf.univ_fiscal_yr = ctrl.univ_fiscal_yr \n");
054 sqlText.append(" AND bcaf.fin_coa_cd = ctrl.fin_coa_cd \n");
055 sqlText.append(" AND bcaf.account_nbr = ctrl.account_nbr \n");
056 sqlText.append(" AND bcaf.sub_acct_nbr = ctrl.sub_acct_nbr \n");
057 sqlText.append(" AND bcaf.emplid <> '");
058 // employee ID for a vacant line in budget construction
059 insertionPoints.add(sqlText.length());
060 sqlText.append("' \n");
061 sqlText.append(" AND bcaf.fin_object_cd = pick.fin_object_cd \n");
062 sqlText.append(" AND pick.person_unvl_id = ctrl.person_unvl_id \n");
063 sqlText.append(" AND pick.select_flag > 0 \n");
064
065 reportReasonStatisticsWithThreshold.add(new SQLForStep(sqlText,insertionPoints));
066 sqlText.delete(0, sqlText.length());
067 insertionPoints.clear();
068
069 /* get emplids with at least one reason rec for the selections if we are doing the report without a threshold*/
070 sqlText.append("INSERT INTO LD_BCN_BUILD_EXSALTOT01_MT (SESID, EMPLID) \n");
071 sqlText.append("SELECT DISTINCT ?, bcaf.emplid \n");
072 sqlText.append("FROM LD_BCN_CTRL_LIST_T ctrl, LD_PNDBC_APPTFND_T bcaf, LD_BCN_OBJ_PICK_T pick, LD_BCN_AF_REASON_T reas, LD_BCN_RSN_CD_PK_T rpk \n");
073 sqlText.append("WHERE ctrl.person_unvl_id = ? \n");
074 sqlText.append(" AND bcaf.univ_fiscal_yr = ctrl.univ_fiscal_yr \n");
075 sqlText.append(" AND bcaf.fin_coa_cd = ctrl.fin_coa_cd \n");
076 sqlText.append(" AND bcaf.account_nbr = ctrl.account_nbr \n");
077 sqlText.append(" AND bcaf.sub_acct_nbr = ctrl.sub_acct_nbr \n");
078 sqlText.append(" AND bcaf.emplid <> '");
079 // employee ID for a vacant line in budget construction
080 insertionPoints.add(sqlText.length());
081 sqlText.append("' \n");
082 sqlText.append(" AND bcaf.fin_object_cd = pick.fin_object_cd \n");
083 sqlText.append(" AND pick.person_unvl_id = ctrl.person_unvl_id \n");
084 sqlText.append(" AND pick.select_flag > 0 \n");
085 sqlText.append(" AND bcaf.univ_fiscal_yr = reas.univ_fiscal_yr \n");
086 sqlText.append(" AND bcaf.fin_coa_cd = reas.fin_coa_cd \n");
087 sqlText.append(" AND bcaf.account_nbr = reas.account_nbr \n");
088 sqlText.append(" AND bcaf.sub_acct_nbr = reas.sub_acct_nbr \n");
089 sqlText.append(" AND bcaf.fin_object_cd = reas.fin_object_cd \n");
090 sqlText.append(" AND bcaf.fin_sub_obj_cd = reas.fin_sub_obj_cd \n");
091 sqlText.append(" AND bcaf.position_nbr = reas.position_nbr \n");
092 sqlText.append(" AND bcaf.emplid = reas.emplid \n");
093 sqlText.append(" AND reas.appt_fnd_reason_cd = rpk.appt_fnd_reason_cd \n");
094 sqlText.append(" AND rpk.person_unvl_id = ctrl.person_unvl_id \n");
095 sqlText.append(" AND rpk.select_flag <> 0 \n");
096
097 reportReasonStatisticsWithNoThreshold.add(new SQLForStep(sqlText,insertionPoints));
098 sqlText.delete(0, sqlText.length());
099 insertionPoints.clear();
100
101 /* get the salary and months data for people not on leave from request, base (CSF), and the position table */
102 sqlText.append("INSERT INTO LD_BCN_BUILD_EXSALTOT02_MT \n");
103 sqlText.append("(SESID, EMPLID, POSITION_NBR, SAL_AMT, SAL_PCT, SAL_MTHS, \n");
104 sqlText.append(" POS_CSF_AMT, POS_CSF_TM_PCT, SAL_PMTHS) \n");
105 sqlText.append("SELECT ?, bcaf.emplid, bcaf.position_nbr, bcaf.appt_rqst_amt, bcaf.appt_rqst_tm_pct, bcaf.appt_fnd_mo, \n");
106 sqlText.append(" COALESCE(bcsf.pos_csf_amt,0), COALESCE(bcsf.pos_csf_tm_pct,0.0), posn.iu_pay_months \n");
107 sqlText.append("FROM (LD_PNDBC_APPTFND_T bcaf LEFT OUTER JOIN LD_BCN_CSF_TRCKR_T bcsf \n");
108 sqlText.append(" ON ((bcaf.univ_fiscal_yr = bcsf.univ_fiscal_yr) \n");
109 sqlText.append(" AND (bcaf.fin_coa_cd = bcsf.fin_coa_cd) \n");
110 sqlText.append(" AND (bcaf.account_nbr = bcsf.account_nbr) \n");
111 sqlText.append(" AND (bcaf.sub_acct_nbr = bcsf.sub_acct_nbr) \n");
112 sqlText.append(" AND (bcaf.fin_object_cd = bcsf.fin_object_cd) \n");
113 sqlText.append(" AND (bcaf.fin_sub_obj_cd = bcsf.fin_sub_obj_cd) \n");
114 sqlText.append(" AND (bcaf.position_nbr = bcsf.position_nbr) \n");
115 sqlText.append(" AND (bcaf.emplid = bcsf.emplid))),\n");
116 sqlText.append(" LD_BCN_POS_T posn, LD_BCN_CTRL_LIST_T ctrl, LD_BCN_BUILD_EXSALTOT01_MT tssn, LD_BCN_OBJ_PICK_T pick \n");
117 sqlText.append("WHERE ctrl.person_unvl_id = ? \n");
118 sqlText.append(" AND tssn.sesid = ? \n");
119 sqlText.append(" AND bcaf.emplid = tssn.emplid \n");
120 sqlText.append(" AND bcaf.univ_fiscal_yr = ctrl.univ_fiscal_yr \n");
121 sqlText.append(" AND bcaf.fin_coa_cd = ctrl.fin_coa_cd \n");
122 sqlText.append(" AND bcaf.account_nbr = ctrl.account_nbr \n");
123 sqlText.append(" AND bcaf.sub_acct_nbr = ctrl.sub_acct_nbr \n");
124 sqlText.append(" AND bcaf.emplid <> '");
125 // employee ID for a vacant line in budget construction
126 insertionPoints.add(sqlText.length());
127 sqlText.append("' \n");
128 sqlText.append(" AND bcaf.appt_fnd_dur_cd = '");
129 // default funding duration code
130 insertionPoints.add(sqlText.length());
131 sqlText.append("'\n");
132 sqlText.append(" AND bcaf.fin_object_cd = pick.fin_object_cd \n");
133 sqlText.append(" AND pick.person_unvl_id = ctrl.person_unvl_id \n");
134 sqlText.append(" AND pick.select_flag > 0 \n");
135 sqlText.append(" AND bcaf.univ_fiscal_yr = posn.univ_fiscal_yr \n");
136 sqlText.append(" AND bcaf.position_nbr = posn.position_nbr \n");
137
138 updateReportsReasonStatisticsTable.add(new SQLForStep(sqlText,insertionPoints));
139 sqlText.delete(0, sqlText.length());
140 insertionPoints.clear();
141
142 /* get leave flagged bcaf, bcsf and posn info first */
143 /* uses leave related info from bcaf, etc */
144 sqlText.append("INSERT INTO LD_BCN_BUILD_EXSALTOT02_MT \n");
145 sqlText.append("(SESID, EMPLID, POSITION_NBR, SAL_AMT, SAL_PCT, SAL_MTHS, \n");
146 sqlText.append(" POS_CSF_AMT, POS_CSF_TM_PCT, SAL_PMTHS) \n");
147 sqlText.append(" SELECT ?, bcaf.emplid, bcaf.position_nbr, bcaf.appt_rqst_csf_amt, bcaf.appt_rqcsf_tm_pct, posn.iu_norm_work_months, \n");
148 sqlText.append(" bcsf.pos_csf_amt, bcsf.pos_csf_tm_pct, posn.iu_pay_months \n");
149 sqlText.append("FROM (LD_PNDBC_APPTFND_T bcaf LEFT OUTER JOIN LD_BCN_CSF_TRCKR_T bcsf \n");
150 sqlText.append("ON ((bcaf.univ_fiscal_yr = bcsf.univ_fiscal_yr) \n");
151 sqlText.append(" AND (bcaf.fin_coa_cd = bcsf.fin_coa_cd)\n");
152 sqlText.append(" AND (bcaf.account_nbr = bcsf.account_nbr) \n");
153 sqlText.append(" AND (bcaf.sub_acct_nbr = bcsf.sub_acct_nbr) \n");
154 sqlText.append(" AND (bcaf.fin_object_cd = bcsf.fin_object_cd) \n");
155 sqlText.append(" AND (bcaf.fin_sub_obj_cd = bcsf.fin_sub_obj_cd) \n");
156 sqlText.append(" AND (bcaf.position_nbr = bcsf.position_nbr) \n");
157 sqlText.append(" AND (bcaf.emplid = bcsf.emplid))),\n");
158 sqlText.append(" LD_BCN_POS_T posn, LD_BCN_CTRL_LIST_T ctrl, LD_BCN_BUILD_EXSALTOT01_MT tssn, LD_BCN_OBJ_PICK_T pick \n");
159 sqlText.append("WHERE ctrl.person_unvl_id = ? \n");
160 sqlText.append("AND tssn.sesid = ? \n");
161 sqlText.append("AND bcaf.emplid = tssn.emplid \n");
162 sqlText.append("AND bcaf.univ_fiscal_yr = ctrl.univ_fiscal_yr \n");
163 sqlText.append("AND bcaf.fin_coa_cd = ctrl.fin_coa_cd \n");
164 sqlText.append("AND bcaf.account_nbr = ctrl.account_nbr \n");
165 sqlText.append("AND bcaf.sub_acct_nbr = ctrl.sub_acct_nbr \n");
166 sqlText.append("AND bcaf.emplid <> '");
167 // employee ID for a vacant line in budget construction
168 insertionPoints.add(sqlText.length());
169 sqlText.append("' \n");
170 sqlText.append("AND bcaf.appt_fnd_dur_cd <> '");
171 // default funding duration code
172 insertionPoints.add(sqlText.length());
173 sqlText.append("' \n");
174 sqlText.append("AND bcaf.fin_object_cd = pick.fin_object_cd \n");
175 sqlText.append("AND pick.person_unvl_id = ctrl.person_unvl_id \n");
176 sqlText.append("AND pick.select_flag > 0 \n");
177 sqlText.append("AND bcaf.univ_fiscal_yr = posn.univ_fiscal_yr \n");
178 sqlText.append("AND bcaf.position_nbr = posn.position_nbr \n");
179
180 updateReportsReasonStatisticsTable.add(new SQLForStep(sqlText,insertionPoints));
181 sqlText.delete(0, sqlText.length());
182 insertionPoints.clear();
183
184 /* take the request appointment attributes (months and position months) from the row for each person with the largest request amount */
185 sqlText.append("INSERT INTO LD_BCN_BUILD_EXSALTOT03_MT (SESID, EMPLID, SAL_MTHS, SAL_PMTHS) \n");
186 sqlText.append("SELECT DISTINCT ?, sd.emplid, sd.sal_mths, sd.sal_pmths \n");
187 sqlText.append("FROM LD_BCN_BUILD_EXSALTOT02_MT sd \n");
188 sqlText.append("WHERE sesid = ? \n");
189 sqlText.append(" AND sd.sal_amt <> 0 \n");
190 sqlText.append(" AND sd.sal_amt = \n");
191 sqlText.append(" (SELECT max(sd2.sal_amt) \n");
192 sqlText.append(" FROM LD_BCN_BUILD_EXSALTOT02_MT sd2 \n");
193 sqlText.append(" WHERE sd2.sesid = sd.sesid AND sd2.emplid = sd.emplid) \n");
194 sqlText.append(" AND sd.position_nbr = \n");
195 sqlText.append(" (SELECT min(sd3.position_nbr) \n");
196 sqlText.append(" FROM LD_BCN_BUILD_EXSALTOT02_MT sd3 \n");
197 sqlText.append(" WHERE sd3.sesid = sd.sesid AND sd3.emplid = sd.emplid AND sd3.sal_amt = sd.sal_amt) \n");
198
199 updateReportsReasonStatisticsTable.add(new SQLForStep(sqlText));
200 sqlText.delete(0, sqlText.length());
201
202 /* get the previous year's (base) appointment attributes for each person from the base row with the largest amount */
203 sqlText.append("INSERT INTO LD_BCN_BUILD_EXSALTOT04_MT (SESID, EMPLID, CSF_MTHS, CSF_PMTHS) \n");
204 sqlText.append("SELECT DISTINCT ?, sd.emplid, p.iu_norm_work_months, p.iu_pay_months \n");
205 sqlText.append("FROM LD_BCN_BUILD_EXSALTOT02_MT sd, LD_BCN_POS_T p \n");
206 sqlText.append("WHERE sesid = ? AND sd.pos_csf_amt <> 0 AND sd.pos_csf_amt = \n");
207 sqlText.append(" (SELECT max(sd2.pos_csf_amt) FROM LD_BCN_BUILD_EXSALTOT02_MT sd2 \n");
208 sqlText.append(" WHERE sd2.sesid = sd.sesid AND sd2.emplid = sd.emplid) \n");
209 sqlText.append(" AND sd.position_nbr = (SELECT min(sd3.position_nbr) \n");
210 sqlText.append(" FROM LD_BCN_BUILD_EXSALTOT02_MT sd3 \n");
211 sqlText.append(" WHERE sd3.sesid = sd.sesid AND sd3.emplid = sd.emplid AND sd3.pos_csf_amt = sd.pos_csf_amt) \n");
212 sqlText.append("AND p.univ_fiscal_yr = ? AND p.position_nbr = sd.position_nbr \n");
213
214 updateReportsReasonStatisticsTable.add(new SQLForStep(sqlText));
215 sqlText.delete(0, sqlText.length());
216
217 /* merge the base and request appointment attributes and amount sums into a single table, and initialize the use_flag */
218 sqlText.append("INSERT INTO LD_BCN_BUILD_EXSALTOT05_MT \n");
219 sqlText.append("(SESID, EMPLID, POS_CSF_AMT, RES_CSF_AMT, POS_CSF_TM_PCT, SAL_AMT, \n");
220 sqlText.append(" SAL_PCT, SAL_FTE, SAL_MTHS, SAL_PMTHS, CSF_MTHS, CSF_PMTHS, USE_FLAG) \n");
221 sqlText.append("SELECT ?, sm.emplid, SUM(COALESCE(sd.pos_csf_amt,0)), 0, SUM(COALESCE(sd.pos_csf_tm_pct,0)), SUM(COALESCE(sd.sal_amt,0)), \n");
222 sqlText.append(" SUM(COALESCE(sd.sal_pct,0)), 0, sm.sal_mths, sm.sal_pmths, COALESCE(cm.csf_mths,0), COALESCE(cm.csf_pmths,0), 'Y' \n");
223 sqlText.append("FROM (LD_BCN_BUILD_EXSALTOT03_MT sm LEFT OUTER JOIN LD_BCN_BUILD_EXSALTOT04_MT cm \n");
224 sqlText.append(" ON ((sm.sesid = cm.sesid) AND (sm.emplid = cm.emplid))), LD_BCN_BUILD_EXSALTOT02_MT sd \n");
225 sqlText.append("WHERE sm.sesid = ? AND sd.sesid = sm.sesid AND sd.emplid = sm.emplid \n");
226 sqlText.append("GROUP BY sm.emplid, sm.sal_mths, sm.sal_pmths, cm.csf_mths, cm.csf_pmths \n");
227
228 updateReportsReasonStatisticsTable.add(new SQLForStep(sqlText));
229 sqlText.delete(0, sqlText.length());
230
231 /* restate the base (CSF) amount to account for changes in the request appointment attributes */
232 sqlText.append("UPDATE LD_BCN_BUILD_EXSALTOT05_MT \n");
233 sqlText.append("SET res_csf_amt = ROUND(COALESCE(((pos_csf_amt * sal_pct * sal_mths * csf_pmths) / (pos_csf_tm_pct * csf_mths * sal_pmths)), 0.00),0) \n");
234 sqlText.append("WHERE sesid = ? AND pos_csf_tm_pct <> 0 AND csf_mths <> 0 AND sal_pmths <> 0 \n");
235
236 updateReportsReasonStatisticsTable.add(new SQLForStep(sqlText));
237 sqlText.delete(0, sqlText.length());
238
239 /* restate the base (CSF) amount to account for changes in the request FTE */
240 sqlText.append("UPDATE LD_BCN_BUILD_EXSALTOT05_MT \n");
241 sqlText.append("SET res_csf_amt = ROUND(COALESCE(((res_csf_amt * sal_pmths) / csf_pmths), 0.00),0) \n");
242 sqlText.append("WHERE sesid = ? AND sal_pmths <> csf_pmths AND csf_pmths <> 0 \n");
243
244 updateReportsReasonStatisticsTable.add(new SQLForStep(sqlText));
245 sqlText.delete(0, sqlText.length());
246
247 /* calculate the fte for each person */
248 sqlText.append("UPDATE LD_BCN_BUILD_EXSALTOT05_MT \n");
249 sqlText.append("SET sal_fte = COALESCE((((sal_pct * sal_mths) / sal_pmths) / 100.0), 0.0) \n");
250 sqlText.append("WHERE sesid = ? AND sal_pmths <> 0 \n");
251
252 updateReportsReasonStatisticsTable.add(new SQLForStep(sqlText));
253 sqlText.delete(0, sqlText.length());
254
255 /* for a run with a threshold, we need to set the use_flag to exclude rows with percent changes below the threshold */
256 sqlText.append("UPDATE LD_BCN_BUILD_EXSALTOT05_MT \n");
257 sqlText.append("SET USE_FLAG = 'N' \n");
258 sqlText.append("WHERE sesid = ? \n");
259 sqlText.append(" AND ROUND((((sal_amt - res_csf_amt) / res_csf_amt) * 100),1) < ? \n");
260 sqlText.append(" AND res_csf_amt <> 0 \n");
261 sqlText.append(" AND sal_amt <> 0 \n");
262
263 reportReasonStatisticsWithThreshold.add(new SQLForStep(sqlText));
264 sqlText.delete(0, sqlText.length());
265
266 /* reset recs greater than percent change - keep lte */
267 sqlText.append("UPDATE LD_BCN_BUILD_EXSALTOT05_MT \n");
268 sqlText.append("SET USE_FLAG = 'N' \n");
269 sqlText.append("WHERE sesid = ? \n");
270 sqlText.append(" AND ROUND((((sal_amt - res_csf_amt) / res_csf_amt) * 100),1) > ? \n");
271 sqlText.append(" AND res_csf_amt <> 0 \n");
272 sqlText.append(" AND sal_amt <> 0 \n");
273
274 reportReasonStatisticsWithThreshold.add(new SQLForStep(sqlText));
275 sqlText.delete(0, sqlText.length());
276
277 /* make a copy of the detailed rows by organization for continuing people */
278 sqlText.append("INSERT INTO LD_BCN_BUILD_EXSALTOT06_MT \n");
279 sqlText.append("(SESID, ORG_FIN_COA_CD, ORG_CD, EMPLID, POS_CSF_AMT, \n");
280 sqlText.append(" APPT_RQST_AMT, APPT_RQST_FTE_QTY, INIT_RQST_AMT, INIT_RQST_FTE) \n");
281 sqlText.append("SELECT DISTINCT ?, ctrl.sel_org_fin_coa, ctrl.sel_org_cd, ssni.emplid, ssni.res_csf_amt, \n");
282 sqlText.append(" ssni.sal_amt, ssni.sal_fte, 0, 0 \n");
283 sqlText.append("FROM LD_BCN_CTRL_LIST_T ctrl, LD_PNDBC_APPTFND_T bcaf, LD_BCN_OBJ_PICK_T pick, LD_BCN_BUILD_EXSALTOT05_MT ssni \n");
284 sqlText.append("WHERE ssni.res_csf_amt <> 0 \n");
285 sqlText.append(" AND ssni.use_flag = 'Y' \n");
286 sqlText.append(" AND ctrl.person_unvl_id = ? \n");
287 sqlText.append(" AND bcaf.univ_fiscal_yr = ctrl.univ_fiscal_yr \n");
288 sqlText.append(" AND bcaf.fin_coa_cd = ctrl.fin_coa_cd \n");
289 sqlText.append(" AND bcaf.account_nbr = ctrl.account_nbr \n");
290 sqlText.append(" AND bcaf.sub_acct_nbr = ctrl.sub_acct_nbr \n");
291 sqlText.append(" AND bcaf.emplid = ssni.emplid \n");
292 sqlText.append(" AND ssni.sesid = ? \n");
293 sqlText.append(" AND bcaf.fin_object_cd = pick.fin_object_cd \n");
294 sqlText.append(" AND pick.person_unvl_id = ctrl.person_unvl_id \n");
295 sqlText.append(" AND pick.select_flag > 0 \n");
296
297 updateReportsReasonStatisticsTable.add(new SQLForStep(sqlText));
298 sqlText.delete(0, sqlText.length());
299
300 /* create copy of detail rows by organization for new people (who do not get a raise and therefore satisfy any threshold tests) */
301 sqlText.append("INSERT INTO LD_BCN_BUILD_EXSALTOT06_MT \n");
302 sqlText.append("(SESID, ORG_FIN_COA_CD, ORG_CD, EMPLID, POS_CSF_AMT, \n");
303 sqlText.append(" APPT_RQST_AMT, APPT_RQST_FTE_QTY, INIT_RQST_AMT, INIT_RQST_FTE) \n");
304 sqlText.append("SELECT DISTINCT ?, ctrl.sel_org_fin_coa, ctrl.sel_org_cd, ssni.emplid, ssni.res_csf_amt, \n");
305 sqlText.append(" 0, 0, ssni.sal_amt, ssni.sal_fte \n");
306 sqlText.append("FROM LD_BCN_CTRL_LIST_T ctrl, LD_PNDBC_APPTFND_T bcaf, LD_BCN_OBJ_PICK_T pick, LD_BCN_BUILD_EXSALTOT05_MT ssni \n");
307 sqlText.append("WHERE ssni.res_csf_amt = 0 \n");
308 sqlText.append(" AND ssni.use_flag = 'Y' \n");
309 sqlText.append(" AND ctrl.person_unvl_id = ? \n");
310 sqlText.append(" AND bcaf.univ_fiscal_yr = ctrl.univ_fiscal_yr \n");
311 sqlText.append(" AND bcaf.fin_coa_cd = ctrl.fin_coa_cd \n");
312 sqlText.append(" AND bcaf.account_nbr = ctrl.account_nbr \n");
313 sqlText.append(" AND bcaf.sub_acct_nbr = ctrl.sub_acct_nbr \n");
314 sqlText.append(" AND bcaf.emplid = ssni.emplid \n");
315 sqlText.append(" AND ssni.sesid = ? \n");
316 sqlText.append(" AND bcaf.fin_object_cd = pick.fin_object_cd \n");
317 sqlText.append(" AND pick.person_unvl_id = ctrl.person_unvl_id \n");
318 sqlText.append(" AND pick.select_flag > 0 \n");
319
320 reportReasonStatisticsWithNoThreshold.add(new SQLForStep(sqlText));
321 sqlText.delete(0, sqlText.length());
322
323 /* sum all the detailed rows and insert into the report table */
324 sqlText.append("INSERT INTO LD_BCN_SLRY_TOT_T \n");
325 sqlText.append("(PERSON_UNVL_ID, ORG_FIN_COA_CD, ORG_CD, POS_CSF_AMT, \n");
326 sqlText.append("APPT_RQST_AMT, APPT_RQST_FTE_QTY, INIT_RQST_AMT, INIT_RQST_FTE_QTY) \n");
327 sqlText.append("SELECT ?, org_fin_coa_cd, org_cd, ROUND(SUM(pos_csf_amt),0),\n");
328 sqlText.append(" SUM(appt_rqst_amt), SUM(appt_rqst_fte_qty), SUM(init_rqst_amt), SUM(init_rqst_fte) \n");
329 sqlText.append("FROM LD_BCN_BUILD_EXSALTOT06_MT \n");
330 sqlText.append("WHERE sesid = ? \n");
331 sqlText.append("GROUP BY org_fin_coa_cd, org_cd \n");
332
333 updateReportsReasonStatisticsTable.add(new SQLForStep(sqlText));
334 sqlText.delete(0, sqlText.length());
335 }
336
337 /**
338 *
339 * @see org.kuali.kfs.module.bc.document.dataaccess.BudgetConstructionReasonStatisticsReportDao#cleanReportsReasonStatisticsTable(java.lang.String)
340 */
341 public void cleanReportsReasonStatisticsTable(String principalName) {
342 clearTempTableByUnvlId("LD_BCN_SLRY_TOT_T", "PERSON_UNVL_ID", principalName);
343 }
344
345 /**
346 *
347 * clears the rows for this session out of the work tables
348 * @param idForSession--a unique identifier for the session
349 */
350 public void cleanWorkTablesFromThisSession(String idForSession)
351 {
352 clearTempTableBySesId("LD_BCN_BUILD_EXSALTOT01_MT", "SESID", idForSession);
353 clearTempTableBySesId("LD_BCN_BUILD_EXSALTOT02_MT", "SESID", idForSession);
354 clearTempTableBySesId("LD_BCN_BUILD_EXSALTOT03_MT", "SESID", idForSession);
355 clearTempTableBySesId("LD_BCN_BUILD_EXSALTOT04_MT", "SESID", idForSession);
356 clearTempTableBySesId("LD_BCN_BUILD_EXSALTOT05_MT", "SESID", idForSession);
357 clearTempTableBySesId("LD_BCN_BUILD_EXSALTOT06_MT", "SESID", idForSession);
358 /**
359 * this is necessary to clear any rows for the tables we have just updated from the OJB cache. otherwise, subsequent calls to OJB will fetch the old, unupdated cached rows.
360 */
361 persistenceService.clearCache();
362 }
363
364 /**
365 *
366 * works in both threshold and non-threshold mode to get the summary salary statistics and appointment attributes for each person
367 * @param principalName--the user running the report
368 * @param idForSession--a unique ID for the session of the user running the report
369 * @param previousFiscalYear--the fiscal year preceding the one for which we are preparing a budget
370 */
371 protected void adjustLastYearSalaryForAppointmentChanges(String principalName, String idForSession, Integer previousFiscalYear)
372 {
373 // strings to be inserted into SQL
374 ArrayList<String >stringsToInsert = new ArrayList<String>(2);
375 stringsToInsert.add(BCConstants.VACANT_EMPLID);
376 stringsToInsert.add(BCConstants.AppointmentFundingDurationCodes.NONE.durationCode);
377 // get base (CSF) and request appointment attributes for people with no leave indicated
378 getSimpleJdbcTemplate().update(updateReportsReasonStatisticsTable.get(0).getSQL(stringsToInsert), idForSession, principalName, idForSession);
379 // get base (CSF) and request appointment attributes for people who are marked as going on leave next year
380 getSimpleJdbcTemplate().update(updateReportsReasonStatisticsTable.get(1).getSQL(stringsToInsert), idForSession, principalName, idForSession);
381 // for each person, take the request appointment attributes from the record with the higest salary amount
382 getSimpleJdbcTemplate().update(updateReportsReasonStatisticsTable.get(2).getSQL(), idForSession, idForSession);
383 // for each continuing person, take the base (CSF) appointment attributes from the record with the highest base salary amount
384 getSimpleJdbcTemplate().update(updateReportsReasonStatisticsTable.get(3).getSQL(), idForSession, idForSession, previousFiscalYear);
385 // merge the appointment attributes and the sums of base and request salary and percent time into a single table
386 getSimpleJdbcTemplate().update(updateReportsReasonStatisticsTable.get(4).getSQL(), idForSession, idForSession);
387 // restate the base (CSF) salary to account for changes in last year's appointment attributes
388 getSimpleJdbcTemplate().update(updateReportsReasonStatisticsTable.get(5).getSQL(), idForSession);
389 // restate the base (CSF) salary to account for changes in attributes that affect FTE
390 getSimpleJdbcTemplate().update(updateReportsReasonStatisticsTable.get(6).getSQL(), idForSession);
391 // calculate an request FTE for each person
392 getSimpleJdbcTemplate().update(updateReportsReasonStatisticsTable.get(7).getSQL(), idForSession);
393 }
394
395 /**
396 *
397 * get detailed salary/FTE rows by person and organization for the continuing people to be reported
398 * @param principalName
399 * @param idForSession
400 */
401 protected void fetchIndividualDetailForContinuingPeople(String principalName, String idForSession)
402 {
403 // salaries and FTE by EMPLID and organization for people in the payroll in the base budget year
404 getSimpleJdbcTemplate().update(updateReportsReasonStatisticsTable.get(8).getSQL(), idForSession, principalName, idForSession);
405 }
406
407
408 /**
409 *
410 * @see org.kuali.kfs.module.bc.document.dataaccess.BudgetConstructionReasonStatisticsReportDao#reportReasonStatisticsWithAThreshold(java.lang.String, java.lang.Integer, boolean, org.kuali.rice.kns.util.KualiDecimal)
411 */
412 public void updateReasonStatisticsReportsWithAThreshold(String principalName, Integer previousFiscalYear, boolean reportIncreasesAtOrAboveTheThreshold, KualiDecimal thresholdPercent) {
413
414 // get a unique session ID
415 String idForSession = (new Guid()).toString();
416 cleanReportsReasonStatisticsTable(principalName);
417 // build the list of constant strings to insert into the SQL
418 ArrayList<String> stringsToInsert = new ArrayList<String>(1);
419 stringsToInsert.add(BCConstants.VACANT_EMPLID);
420
421 // for a report by threshold, we want everyone--exclude only vacant lines
422 getSimpleJdbcTemplate().update(reportReasonStatisticsWithThreshold.get(0).getSQL(stringsToInsert), idForSession, principalName);
423 // get all the salary and appointment information for those people
424 adjustLastYearSalaryForAppointmentChanges(principalName, idForSession, previousFiscalYear);
425
426 // mark the rows to be excluded when we are screening with a threshold percent
427 // (KualiDecimal is not recognized as a type by java.sql--we have to convert it to its superclass BigDecimal)
428 BigDecimal thresholdValue = thresholdPercent.bigDecimalValue();
429 if (reportIncreasesAtOrAboveTheThreshold)
430 {
431 // exclude everyone with increases less than the threshold
432 getSimpleJdbcTemplate().update(reportReasonStatisticsWithThreshold.get(1).getSQL(), idForSession, thresholdValue);
433 }
434 else
435 {
436 // exclude everyone with increases over the threshold
437 getSimpleJdbcTemplate().update(reportReasonStatisticsWithThreshold.get(2).getSQL(), idForSession, thresholdValue);
438 }
439
440 fetchIndividualDetailForContinuingPeople(principalName, idForSession);
441 sumTheDetailRowsToProduceTheReportData (principalName, idForSession);
442
443 cleanWorkTablesFromThisSession(idForSession);
444 /**
445 * this is necessary to clear any rows for the tables we have just updated from the OJB cache. otherwise, subsequent calls to OJB will fetch the old, unupdated cached rows.
446 */
447 persistenceService.clearCache();
448 }
449
450 /**
451 *
452 * @see org.kuali.kfs.module.bc.document.dataaccess.BudgetConstructionReasonStatisticsReportDao#reportReasonStatisticsWithoutAThreshold(java.lang.String, java.lang.Integer)
453 */
454 public void updateReasonStatisticsReportsWithoutAThreshold(String principalName, Integer previousFiscalYear) {
455 // get a unique session ID
456 String idForSession = (new Guid()).toString();
457 cleanReportsReasonStatisticsTable(principalName);
458
459 // build the list of constant strings to insert into the SQL
460 ArrayList<String> stringsToInsert = new ArrayList<String>(1);
461 stringsToInsert.add(BCConstants.VACANT_EMPLID);
462
463 // we want only people who have an attached reason code
464 getSimpleJdbcTemplate().update(reportReasonStatisticsWithNoThreshold.get(0).getSQL(stringsToInsert), idForSession, principalName);
465 // get all the salary and appointment information for those people
466 adjustLastYearSalaryForAppointmentChanges(principalName, idForSession, previousFiscalYear);
467
468 fetchIndividualDetailForContinuingPeople(principalName, idForSession);
469 // when we are using a reason code and not a threshold, we want everyone with a reason code, not just continuing people
470 // new people have no percent increase, and so would not match any threshold, but should be included under this report option
471 getSimpleJdbcTemplate().update(reportReasonStatisticsWithNoThreshold.get(1).getSQL(), idForSession, principalName, idForSession);
472 sumTheDetailRowsToProduceTheReportData (principalName, idForSession);
473
474 cleanWorkTablesFromThisSession(idForSession);
475 /**
476 * this is necessary to clear any rows for the tables we have just updated from the OJB cache. otherwise, subsequent calls to OJB will fetch the old, unupdated cached rows.
477 */
478 persistenceService.clearCache();
479 }
480
481 /**
482 *
483 * sum base and request amounts and FTE by organization to produce the data used by the report
484 * @param idForSession--the session of the user doing the report
485 */
486 protected void sumTheDetailRowsToProduceTheReportData (String principalName, String idForSession) {
487 getSimpleJdbcTemplate().update(updateReportsReasonStatisticsTable.get(9).getSQL(), principalName, idForSession);
488 }
489
490 public void setPersistenceService(PersistenceService persistenceService)
491 {
492 this.persistenceService = persistenceService;
493 }
494
495 }
496