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.util.ArrayList;
019
020 import org.kuali.kfs.module.bc.batch.dataaccess.impl.SQLForStep;
021 import org.kuali.kfs.module.bc.document.dataaccess.BudgetConstructionPositionFundingDetailReportDao;
022 import org.kuali.rice.kns.service.PersistenceService;
023 import org.kuali.rice.kns.util.Guid;
024 import org.kuali.rice.kns.util.KualiDecimal;
025
026
027 /**
028 * populates the report table for positon funding detail for a given user
029 */
030
031 public class BudgetConstructionPositionFundingDetailReportDaoJdbc extends BudgetConstructionDaoJdbcBase implements BudgetConstructionPositionFundingDetailReportDao {
032 private static org.apache.log4j.Logger LOG = org.apache.log4j.Logger.getLogger(BudgetConstructionPositionFundingDetailReportDaoJdbc.class);
033
034 private static ArrayList<SQLForStep> updateReportsPositionFundingDetailTable = new ArrayList<SQLForStep>(5);
035
036 private PersistenceService persistenceService;
037
038 public BudgetConstructionPositionFundingDetailReportDaoJdbc() {
039
040 /* populate the table based on threshold settings */
041 /* sum the request and base by ssn */
042 StringBuilder sqlText = new StringBuilder(1500);
043 sqlText.append("INSERT INTO LD_BCN_BUILD_POSLIST01_MT \n");
044 sqlText.append("(SESID, EMPLID, POS_CSF_AMT, POS_CSF_FTE_QTY, APPT_RQST_AMT, APPT_RQST_FTE_QTY) \n");
045 sqlText.append("SELECT ?, bcaf.emplid, SUM(COALESCE(bcsf.pos_csf_amt,0)), SUM(COALESCE(bcsf.pos_csf_fte_qty,0)), SUM(bcaf.appt_rqst_amt), SUM(bcaf.appt_rqst_fte_qty) \n");
046 sqlText.append("FROM (LD_PNDBC_APPTFND_T bcaf LEFT OUTER JOIN LD_BCN_CSF_TRCKR_T bcsf ON \n");
047 sqlText.append(" ((bcaf.univ_fiscal_yr = bcsf.univ_fiscal_yr) AND \n");
048 sqlText.append(" (bcaf.fin_coa_cd = bcsf.fin_coa_cd) AND \n");
049 sqlText.append(" (bcaf.account_nbr = bcsf.account_nbr) AND \n");
050 sqlText.append(" (bcaf.sub_acct_nbr = bcsf.sub_acct_nbr) AND \n");
051 sqlText.append(" (bcaf.fin_object_cd = bcsf.fin_object_cd) AND \n");
052 sqlText.append(" (bcaf.fin_sub_obj_cd = bcsf.fin_sub_obj_cd) AND \n");
053 sqlText.append(" (bcaf.position_nbr = bcsf.position_nbr) AND \n");
054 sqlText.append(" (bcaf.emplid = bcsf.emplid))), LD_BCN_CTRL_LIST_T ctrl, LD_BCN_OBJ_PICK_T pick \n");
055 sqlText.append("WHERE ctrl.person_unvl_id = ? \n");
056 sqlText.append(" AND bcaf.univ_fiscal_yr = ctrl.univ_fiscal_yr \n");
057 sqlText.append(" AND bcaf.fin_coa_cd = ctrl.fin_coa_cd \n");
058 sqlText.append(" AND bcaf.account_nbr = ctrl.account_nbr \n");
059 sqlText.append(" AND bcaf.sub_acct_nbr = ctrl.sub_acct_nbr \n");
060 sqlText.append(" AND bcaf.appt_rqst_amt <> 0 \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 sqlText.append("GROUP BY bcaf.emplid \n");
065
066 updateReportsPositionFundingDetailTable.add(new SQLForStep(sqlText));
067 sqlText.delete(0, sqlText.length());
068
069 /* get the set where percent change (fraction * 100) in the salary exceeds the threshold */
070 sqlText.append("INSERT INTO LD_BCN_POS_FND_T \n");
071 sqlText.append(" (PERSON_UNVL_ID, SEL_ORG_FIN_COA, SEL_ORG_CD, PERSON_NM, EMPLID, POSITION_NBR, UNIV_FISCAL_YR, \n");
072 sqlText.append(" FIN_COA_CD, ACCOUNT_NBR, SUB_ACCT_NBR, FIN_OBJECT_CD, FIN_SUB_OBJ_CD) \n");
073 sqlText.append("SELECT ?, ctrl.sel_org_fin_coa, ctrl.sel_org_cd, COALESCE(iinc.person_nm,'VACANT'), bcaf.emplid, bcaf.position_nbr, bcaf.univ_fiscal_yr,\n");
074 sqlText.append(" bcaf.fin_coa_cd, bcaf.account_nbr, bcaf.sub_acct_nbr, bcaf.fin_object_cd, bcaf.fin_sub_obj_cd \n");
075 sqlText.append("FROM (LD_PNDBC_APPTFND_T bcaf LEFT OUTER JOIN LD_BCN_INTINCBNT_T iinc \n");
076 sqlText.append(" ON (bcaf.emplid = iinc.emplid)), LD_BCN_CTRL_LIST_T ctrl, LD_BCN_OBJ_PICK_T pick, LD_BCN_BUILD_POSLIST01_MT tssn \n");
077 sqlText.append("WHERE ctrl.person_unvl_id = ? \n");
078 sqlText.append(" AND bcaf.univ_fiscal_yr = ctrl.univ_fiscal_yr \n");
079 sqlText.append(" AND bcaf.fin_coa_cd = ctrl.fin_coa_cd \n");
080 sqlText.append(" AND bcaf.account_nbr = ctrl.account_nbr \n");
081 sqlText.append(" AND bcaf.sub_acct_nbr = ctrl.sub_acct_nbr \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.emplid = tssn.emplid \n");
086 sqlText.append(" AND tssn.appt_rqst_fte_qty = tssn.pos_csf_fte_qty \n");
087 sqlText.append(" AND ROUND((((tssn.appt_rqst_amt - tssn.pos_csf_amt) / tssn.pos_csf_amt) * 100),1) >= ? \n");
088 sqlText.append(" AND tssn.pos_csf_amt <> 0 \n");
089 sqlText.append(" AND tssn.sesid = ? \n");
090
091 updateReportsPositionFundingDetailTable.add(new SQLForStep(sqlText));
092 sqlText.delete(0, sqlText.length());
093
094 /* get the set where the percent change (fraction * 100) in the salary is less than or equal to the threshold */
095 sqlText.append("INSERT INTO LD_BCN_POS_FND_T \n");
096 sqlText.append(" (PERSON_UNVL_ID, SEL_ORG_FIN_COA, SEL_ORG_CD, PERSON_NM, EMPLID, POSITION_NBR, UNIV_FISCAL_YR, \n");
097 sqlText.append(" FIN_COA_CD, ACCOUNT_NBR, SUB_ACCT_NBR, FIN_OBJECT_CD, FIN_SUB_OBJ_CD) \n");
098 sqlText.append(" SELECT ?, ctrl.sel_org_fin_coa, ctrl.sel_org_cd, COALESCE(iinc.person_nm,'VACANT'), bcaf.emplid, bcaf.position_nbr, bcaf.univ_fiscal_yr, \n");
099 sqlText.append(" bcaf.fin_coa_cd, bcaf.account_nbr, bcaf.sub_acct_nbr, bcaf.fin_object_cd, bcaf.fin_sub_obj_cd \n");
100 sqlText.append("FROM (LD_PNDBC_APPTFND_T bcaf LEFT OUTER JOIN LD_BCN_INTINCBNT_T iinc ON (bcaf.emplid = iinc.emplid)), \n");
101 sqlText.append(" LD_BCN_CTRL_LIST_T ctrl, LD_BCN_OBJ_PICK_T pick, LD_BCN_BUILD_POSLIST01_MT tssn \n");
102 sqlText.append("WHERE ctrl.person_unvl_id = ? \n");
103 sqlText.append(" AND bcaf.univ_fiscal_yr = ctrl.univ_fiscal_yr \n");
104 sqlText.append(" AND bcaf.fin_coa_cd = ctrl.fin_coa_cd \n");
105 sqlText.append(" AND bcaf.account_nbr = ctrl.account_nbr \n");
106 sqlText.append(" AND bcaf.sub_acct_nbr = ctrl.sub_acct_nbr \n");
107 sqlText.append(" AND bcaf.fin_object_cd = pick.fin_object_cd \n");
108 sqlText.append(" AND pick.person_unvl_id = ctrl.person_unvl_id \n");
109 sqlText.append(" AND pick.select_flag > 0 \n");
110 sqlText.append(" AND bcaf.emplid = tssn.emplid \n");
111 sqlText.append(" AND tssn.appt_rqst_fte_qty = tssn.pos_csf_fte_qty \n");
112 sqlText.append(" AND ROUND((((tssn.appt_rqst_amt - tssn.pos_csf_amt) / tssn.pos_csf_amt) * 100),1) <= ? \n");
113 sqlText.append(" AND tssn.pos_csf_amt <> 0 \n");
114 sqlText.append(" AND tssn.sesid = ? \n");
115
116 updateReportsPositionFundingDetailTable.add(new SQLForStep(sqlText));
117 sqlText.delete(0, sqlText.length());
118
119 /* populate the table using the full set--no check on the percent change in the salary */
120 sqlText.append("INSERT INTO LD_BCN_POS_FND_T \n");
121 sqlText.append(" (PERSON_UNVL_ID, SEL_ORG_FIN_COA, SEL_ORG_CD, PERSON_NM, EMPLID, POSITION_NBR, UNIV_FISCAL_YR, \n");
122 sqlText.append(" FIN_COA_CD, ACCOUNT_NBR, SUB_ACCT_NBR, FIN_OBJECT_CD, FIN_SUB_OBJ_CD) \n");
123 sqlText.append("SELECT ?, ctrl.sel_org_fin_coa, ctrl.sel_org_cd, COALESCE(iinc.person_nm,'VACANT'), bcaf.emplid, bcaf.position_nbr, bcaf.univ_fiscal_yr, \n");
124 sqlText.append(" bcaf.fin_coa_cd, bcaf.account_nbr, bcaf.sub_acct_nbr, bcaf.fin_object_cd, bcaf.fin_sub_obj_cd \n");
125 sqlText.append("FROM (LD_PNDBC_APPTFND_T bcaf LEFT OUTER JOIN LD_BCN_INTINCBNT_T iinc \n");
126 sqlText.append(" ON (bcaf.emplid = iinc.emplid)), LD_BCN_CTRL_LIST_T ctrl, LD_BCN_OBJ_PICK_T pick \n");
127 sqlText.append("WHERE ctrl.person_unvl_id = ? \n");
128 sqlText.append(" AND bcaf.univ_fiscal_yr = ctrl.univ_fiscal_yr \n");
129 sqlText.append(" AND bcaf.fin_coa_cd = ctrl.fin_coa_cd \n");
130 sqlText.append(" AND bcaf.account_nbr = ctrl.account_nbr \n");
131 sqlText.append(" AND bcaf.sub_acct_nbr = ctrl.sub_acct_nbr \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
136 updateReportsPositionFundingDetailTable.add(new SQLForStep(sqlText));
137 sqlText.delete(0, sqlText.length());
138 }
139
140 public void cleanReportsPositionFundingDetailTable(String principalName) {
141 clearTempTableByUnvlId("LD_BCN_POS_FND_T", "PERSON_UNVL_ID", principalName);
142 /**
143 * 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.
144 */
145 persistenceService.clearCache();
146 }
147 /**
148 *
149 * build a list of people with salaries at or above the threshold
150 * @param principalName--the user requesting the list
151 * @param thresholdPercent--the percent marking the threshold
152 */
153 protected void updateReportsPositionFundingDetailTableAboveThreshold(String principalName, KualiDecimal thresholdPercent)
154 {
155 Guid guid = new Guid();
156 String idForSession = guid.toString();
157 // get rid of any previous reporting data from this user
158 cleanReportsPositionFundingDetailTable(principalName);
159 // sum the FTE and amounts into a temporary table
160 getSimpleJdbcTemplate().update(updateReportsPositionFundingDetailTable.get(0).getSQL(),idForSession,principalName);
161 // fill the reporting table with only those people who are at or above the threshold
162 // (jdbcTemplate will apparenlty not accept a parameter of type KualiDecimal, and a cast when we pass the parameter doesn't help: 04/09/2008)
163 // (apparently, creating a new value from a cast doesn't help either)
164 Number thresholdValue = thresholdPercent.floatValue();
165 getSimpleJdbcTemplate().update(updateReportsPositionFundingDetailTable.get(1).getSQL(),principalName, principalName, thresholdValue, idForSession);
166 // remove the data for this user's session from the temporary table for total amounts and FTE
167 this.clearTempTableBySesId("LD_BCN_BUILD_POSLIST01_MT","SESID",idForSession);
168 }
169
170 /**
171 *
172 * build a list of people with salaries at or below the threshhold
173 * @param principalName--the user requesting the list
174 * @param thresholdPercent--the percent marking the threshold
175 */
176 protected void updateReportsPositionFundingDetailTableBelowThreshold(String principalName, KualiDecimal thresholdPercent)
177 {
178 Guid guid = new Guid();
179 String idForSession = guid.toString();
180 // get rid of any previous reporting data from this user
181 cleanReportsPositionFundingDetailTable(principalName);
182 // sum the FTE and amounts into a temporary table
183 getSimpleJdbcTemplate().update(updateReportsPositionFundingDetailTable.get(0).getSQL(),idForSession,principalName);
184 // fill the reporting table with only those people who are at or below the threshold
185 // (jdbcTemplate will apparenlty not accept a parameter of type KualiDecimal, and a cast when we pass the parameter doesn't help: 04/09/2008)
186 // (apparently, creating a new value from a cast doesn't help either)
187 Number thresholdValue = thresholdPercent.floatValue();
188 getSimpleJdbcTemplate().update(updateReportsPositionFundingDetailTable.get(2).getSQL(),principalName, principalName, thresholdValue, idForSession);
189 // remove the data for this user's session from the temporary table for total amounts and FTE
190 this.clearTempTableBySesId("LD_BCN_BUILD_POSLIST01_MT","SESID",idForSession);
191 }
192
193 /**
194 *
195 * build a list of all salaries which this user can see
196 * @param principalName--the user requesting the list
197 */
198 protected void updateReportsPositionFundingDetailTableWithAllData(String principalName)
199 {
200 // get rid of any previous reporting data from this user
201 cleanReportsPositionFundingDetailTable(principalName);
202 // dump all the data this user is authorized to report on into the reporting table
203 getSimpleJdbcTemplate().update(updateReportsPositionFundingDetailTable.get(3).getSQL(), principalName, principalName);
204 }
205
206 /**
207 *
208 * @see org.kuali.kfs.module.bc.document.dataaccess.BudgetConstructionPositionFundingDetailReportDao#updateReportsPositionFundingDetailTable(java.lang.String, boolean, boolean, java.lang.Number)
209 */
210 public void updateReportsPositionFundingDetailTable(String principalName, boolean applyAThreshold, boolean selectOnlyGreaterThanOrEqualToThreshold, KualiDecimal thresholdPercent) {
211 // if there is no threshold, just dump everything in and return
212 if (! applyAThreshold)
213 {
214 updateReportsPositionFundingDetailTableWithAllData(principalName);
215 return;
216 }
217 // the user wants a threshold--list above or below?
218 if (selectOnlyGreaterThanOrEqualToThreshold)
219 {
220 updateReportsPositionFundingDetailTableAboveThreshold(principalName, thresholdPercent);
221 }
222 else
223 {
224 updateReportsPositionFundingDetailTableBelowThreshold(principalName, thresholdPercent);
225 }
226 /**
227 * 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.
228 */
229 persistenceService.clearCache();
230 }
231
232 public void setPersistenceService(PersistenceService persistenceService)
233 {
234 this.persistenceService = persistenceService;
235 }
236
237 }
238