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.BCConstants;
021 import org.kuali.kfs.module.bc.batch.dataaccess.impl.SQLForStep;
022 import org.kuali.kfs.module.bc.document.dataaccess.BudgetConstructionSynchronizationProblemsReportDao;
023 import org.kuali.kfs.sys.KFSConstants.BudgetConstructionPositionConstants;
024 import org.kuali.rice.kns.service.PersistenceService;
025
026 /**
027 * builds a report table of people whose salaries are budgeted in the wrong object class or have had a position change that merits an object code validity check
028 */
029
030 public class BudgetConstructionSynchronizationProblemsReportDaoJdbc extends BudgetConstructionDaoJdbcBase implements BudgetConstructionSynchronizationProblemsReportDao {
031 private static org.apache.log4j.Logger LOG = org.apache.log4j.Logger.getLogger(BudgetConstructionSynchronizationProblemsReportDaoJdbc.class);
032
033 private static ArrayList<SQLForStep> updateReportsSynchronizationProblemsTable = new ArrayList<SQLForStep>(2);
034
035 private PersistenceService persistenceService;
036
037 public BudgetConstructionSynchronizationProblemsReportDaoJdbc() {
038
039 ArrayList<Integer> insertionPoints = new ArrayList<Integer>(2);
040
041 //builds and updates SynchronizationProblemsReports
042 //builds the salary and default object check MT table
043 StringBuilder sqlText = new StringBuilder(1500);
044 sqlText.append("INSERT INTO LD_BCN_POS_FND_T \n");
045 sqlText.append("(PERSON_UNVL_ID, SEL_ORG_FIN_COA, SEL_ORG_CD, PERSON_NM, EMPLID, POSITION_NBR, \n");
046 sqlText.append(" UNIV_FISCAL_YR, FIN_COA_CD, ACCOUNT_NBR, SUB_ACCT_NBR, FIN_OBJECT_CD, FIN_SUB_OBJ_CD) \n");
047 sqlText.append("SELECT ?, ctrl.sel_org_fin_coa, ctrl.sel_org_cd, COALESCE(iinc.PERSON_NM,'Name not Found'), bcaf.emplid, bcaf.position_nbr, \n");
048 sqlText.append(" bcaf.univ_fiscal_yr, bcaf.fin_coa_cd, bcaf.account_nbr, bcaf.sub_acct_nbr, bcaf.fin_object_cd, bcaf.fin_sub_obj_cd \n");
049 sqlText.append("FROM (LD_PNDBC_APPTFND_T bcaf LEFT OUTER JOIN LD_BCN_INTINCBNT_T iinc ON (bcaf.emplid = iinc.emplid)), LD_BCN_CTRL_LIST_T ctrl \n");
050 sqlText.append("WHERE ctrl.person_unvl_id = ? \n");
051 sqlText.append(" AND bcaf.univ_fiscal_yr = ctrl.univ_fiscal_yr \n");
052 sqlText.append(" AND bcaf.fin_coa_cd = ctrl.fin_coa_cd \n");
053 sqlText.append(" AND bcaf.account_nbr = ctrl.account_nbr \n");
054 sqlText.append(" AND bcaf.sub_acct_nbr = ctrl.sub_acct_nbr \n");
055 sqlText.append(" AND bcaf.appt_fnd_dlt_cd = 'N' \n");
056 sqlText.append(" AND (bcaf.pos_obj_chg_ind = 'Y' OR bcaf.pos_sal_chg_ind = 'Y') \n");
057 sqlText.append(" UNION ALL\n");
058 sqlText.append("SELECT ?, ctrl.sel_org_fin_coa, ctrl.sel_org_cd, COALESCE(iinc.PERSON_NM,'Name not Found'), bcaf.emplid, bcaf.position_nbr, \n");
059 sqlText.append(" bcaf.univ_fiscal_yr, bcaf.fin_coa_cd, bcaf.account_nbr, bcaf.sub_acct_nbr, bcaf.fin_object_cd, bcaf.fin_sub_obj_cd \n");
060 sqlText.append("FROM (LD_PNDBC_APPTFND_T bcaf LEFT OUTER JOIN LD_BCN_INTINCBNT_T iinc ON (bcaf.emplid = iinc.emplid)), LD_BCN_POS_T bp, LD_BCN_CTRL_LIST_T ctrl \n");
061 sqlText.append("WHERE ctrl.person_unvl_id = ? \n");
062 sqlText.append(" AND bcaf.univ_fiscal_yr = ctrl.univ_fiscal_yr \n");
063 sqlText.append(" AND bcaf.fin_coa_cd = ctrl.fin_coa_cd \n");
064 sqlText.append(" AND bcaf.account_nbr = ctrl.account_nbr \n");
065 sqlText.append(" AND bcaf.sub_acct_nbr = ctrl.sub_acct_nbr \n");
066 sqlText.append(" AND bcaf.appt_fnd_dlt_cd = 'N' \n");
067 sqlText.append(" AND bcaf.pos_obj_chg_ind <> 'Y' \n");
068 sqlText.append(" AND bcaf.pos_sal_chg_ind <> 'Y' \n");
069 sqlText.append(" AND bcaf.univ_fiscal_yr = bp.univ_fiscal_yr \n");
070 sqlText.append(" AND bcaf.position_nbr = bp.position_nbr \n");
071 sqlText.append(" AND (bp.pos_eff_status <> '");
072 // active effective status
073 insertionPoints.add(sqlText.length());
074 sqlText.append("' OR bp.budgeted_posn <> 'Y') \n");
075
076 updateReportsSynchronizationProblemsTable.add(new SQLForStep(sqlText,insertionPoints));
077 sqlText.delete(0, sqlText.length());
078 insertionPoints.clear();
079
080 sqlText.append("UPDATE LD_BCN_POS_FND_T \n");
081 sqlText.append("SET PERSON_NM = '");
082 // the string indicating a vacant EMPLID
083 insertionPoints.add(sqlText.length());
084 sqlText.append("' \n");
085 sqlText.append("WHERE (PERSON_UNVL_ID = ?) \n");
086 sqlText.append("AND (EMPLID = '");
087 // the string indicating a vacant EMPLID
088 insertionPoints.add(sqlText.length());
089 sqlText.append("')");
090
091 updateReportsSynchronizationProblemsTable.add(new SQLForStep(sqlText,insertionPoints));
092 sqlText.delete(0, sqlText.length());
093 insertionPoints.clear();
094
095 }
096
097 /**
098 *
099 * removes any rows from a previous report for this uear
100 * @param principalName--the user requesting the report
101 */
102 protected void cleanReportsSynchronizationProblemsTable(String principalName) {
103 clearTempTableByUnvlId("LD_BCN_POS_FND_T", "PERSON_UNVL_ID", principalName);
104 }
105
106 /**
107 *
108 * @see org.kuali.kfs.module.bc.document.dataaccess.BudgetConstructionSynchronizationProblemsReportDao#updateReportsSynchronizationProblemsTable(java.lang.String)
109 */
110 public void updateReportsSynchronizationProblemsTable(String principalName) {
111 ArrayList<String> stringsToInsert = new ArrayList<String>(2);
112 // get rid of any old reports sitting around for this user
113 cleanReportsSynchronizationProblemsTable(principalName);
114 // insert the code for an active position
115 stringsToInsert.add(BudgetConstructionPositionConstants.POSITION_EFFECTIVE_STATUS_ACTIVE);
116 // insert into the report table filled or vacant lines with an object code change, a position change, or an inactive position
117 getSimpleJdbcTemplate().update(updateReportsSynchronizationProblemsTable.get(0).getSQL(stringsToInsert), principalName, principalName, principalName, principalName);
118 // change the name field for any line with a vacant position
119 stringsToInsert.clear();
120 stringsToInsert.add(BCConstants.VACANT_EMPLID);
121 stringsToInsert.add(BCConstants.VACANT_EMPLID);
122 getSimpleJdbcTemplate().update(updateReportsSynchronizationProblemsTable.get(1).getSQL(stringsToInsert), principalName);
123 /**
124 * 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.
125 */
126 persistenceService.clearCache();
127 }
128
129 public void setPersistenceService(PersistenceService persistenceService)
130 {
131 this.persistenceService = persistenceService;
132 }
133
134 }
135