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.BudgetConstructionDocumentAccountObjectDetailReportDao;
023 import org.kuali.rice.kns.service.PersistenceService;
024
025
026 public class BudgetConstructionDocumentAccountObjectDetailReportDaoJdbc extends BudgetConstructionDaoJdbcBase implements BudgetConstructionDocumentAccountObjectDetailReportDao {
027
028 private PersistenceService persistenceService;
029
030 private SQLForStep initialInsert;
031 private SQLForStep setNonLeaveCSFFTE;
032 private SQLForStep setLeaveCSFFTE;
033
034 private ArrayList<Integer> insertionPoints = new ArrayList<Integer>(4);
035
036 public BudgetConstructionDocumentAccountObjectDetailReportDaoJdbc()
037 {
038 StringBuilder sqlText = new StringBuilder(750);
039 sqlText.append("INSERT INTO LD_BCN_BAL_BY_ACCT_T\n");
040 sqlText.append("(PERSON_UNVL_ID, UNIV_FISCAL_YR, FIN_COA_CD, ACCOUNT_NBR, SUB_ACCT_NBR, FIN_OBJECT_CD,\n");
041 sqlText.append(" FIN_SUB_OBJ_CD, FIN_OBJ_TYP_CD, FIN_OBJ_LEVEL_CD, TYP_FIN_REPORT_SORT_CD, FIN_CONS_SORT_CD,\n");
042 sqlText.append(" LEV_FIN_REPORT_SORT_CD, APPT_RQST_FTE_QTY, APPT_RQCSF_FTE_QTY, POS_CSF_FTE_QTY,\n");
043 sqlText.append(" ACLN_ANNL_BAL_AMT, FIN_BEG_BAL_LN_AMT, POS_CSF_LV_FTE_QTY)\n");
044 sqlText.append("SELECT ?,\n");
045 sqlText.append(" a.univ_fiscal_yr,\n");
046 sqlText.append(" a.fin_coa_cd,\n");
047 sqlText.append(" a.account_nbr,\n");
048 sqlText.append(" a.sub_acct_nbr,\n");
049 sqlText.append(" a.fin_object_cd,\n");
050 sqlText.append(" a.fin_sub_obj_cd,\n");
051 sqlText.append(" a.fin_obj_typ_cd,\n");
052 sqlText.append(" l.fin_obj_level_cd,\n");
053 // DB-specific substring format (first character of t.fin_report_sort_cd)
054 insertionPoints.add(sqlText.length());
055 sqlText.append(",\n");
056 sqlText.append(" c.fin_report_sort_cd,\n");
057 sqlText.append(" l.fin_report_sort_cd,\n");
058 sqlText.append(" COALESCE(SUM(p.appt_rqst_fte_qty),0),\n");
059 sqlText.append(" COALESCE(SUM(p.appt_rqcsf_fte_qty),0),\n");
060 sqlText.append(" 0, a.acln_annl_bal_amt, a.fin_beg_bal_ln_amt, 0\n");
061 sqlText.append("FROM (LD_PND_BCNSTR_GL_T a LEFT OUTER JOIN LD_PNDBC_APPTFND_T p\n");
062 sqlText.append(" ON ((a.univ_fiscal_yr = p.univ_fiscal_yr) AND\n");
063 sqlText.append(" (a.fin_coa_cd = p.fin_coa_cd) AND\n");
064 sqlText.append(" (a.account_nbr = p.account_nbr) AND\n");
065 sqlText.append(" (a.sub_acct_nbr = p.sub_acct_nbr) AND\n");
066 sqlText.append(" (a.fin_object_cd = p.fin_object_cd) AND\n");
067 sqlText.append(" (a.fin_sub_obj_cd = p.fin_sub_obj_cd))),\n");
068 sqlText.append(" CA_OBJECT_CODE_T o,\n");
069 sqlText.append(" CA_OBJ_TYPE_T t,\n");
070 sqlText.append(" CA_OBJ_LEVEL_T l,\n");
071 sqlText.append(" CA_OBJ_CONSOLDTN_T c\n");
072 sqlText.append("WHERE a.fdoc_nbr = ?\n");
073 sqlText.append(" AND a.univ_fiscal_yr = ?\n");
074 sqlText.append(" AND a.fin_coa_cd = ?\n");
075 sqlText.append(" AND a.account_nbr = ?\n");
076 sqlText.append(" AND a.sub_acct_nbr = ?\n");
077 sqlText.append(" AND a.univ_fiscal_yr = o.univ_fiscal_yr\n");
078 sqlText.append(" AND a.fin_coa_cd = o.fin_coa_cd\n");
079 sqlText.append(" AND a.fin_object_cd = o.fin_object_cd\n");
080 sqlText.append(" AND o.fin_obj_typ_cd = t.fin_obj_typ_cd\n");
081 sqlText.append(" AND o.fin_coa_cd = l.fin_coa_cd\n");
082 sqlText.append(" AND o.fin_obj_level_cd = l.fin_obj_level_cd\n");
083 sqlText.append(" AND c.fin_coa_cd = l.fin_coa_cd\n");
084 sqlText.append(" AND c.fin_cons_obj_cd = l.fin_cons_obj_cd\n");
085 sqlText.append("GROUP BY a.univ_fiscal_yr,\n");
086 sqlText.append(" a.fin_coa_cd,\n");
087 sqlText.append(" a.account_nbr,\n");
088 sqlText.append(" a.sub_acct_nbr,\n");
089 sqlText.append(" a.fin_object_cd,\n");
090 sqlText.append(" a.fin_sub_obj_cd,\n");
091 sqlText.append(" a.fin_obj_typ_cd,\n");
092 sqlText.append(" l.fin_obj_level_cd,\n");
093 sqlText.append(" t.fin_report_sort_cd,\n");
094 sqlText.append(" c.fin_report_sort_cd,\n");
095 sqlText.append(" l.fin_report_sort_cd,\n");
096 sqlText.append(" a.acln_annl_bal_amt,\n");
097 sqlText.append(" a.fin_beg_bal_ln_amt");
098 // initial insertion into the table
099 initialInsert = new SQLForStep(sqlText,insertionPoints);
100 sqlText.delete(0,sqlText.length());
101 insertionPoints.clear();
102 //
103 // update non-leave CSF FTE
104 sqlText.append("UPDATE LD_BCN_BAL_BY_ACCT_T\n");
105 sqlText.append("SET pos_csf_fte_qty =\n");
106 sqlText.append(" (SELECT sum(pos_csf_fte_qty)\n");
107 sqlText.append(" FROM LD_BCN_CSF_TRCKR_T c\n");
108 sqlText.append(" WHERE person_unvl_id = ?\n");
109 sqlText.append(" and LD_BCN_BAL_BY_ACCT_T.univ_fiscal_yr = c.univ_fiscal_yr\n");
110 sqlText.append(" and LD_BCN_BAL_BY_ACCT_T.fin_coa_cd = c.fin_coa_cd\n");
111 sqlText.append(" and LD_BCN_BAL_BY_ACCT_T.account_nbr = c.account_nbr\n");
112 sqlText.append(" and LD_BCN_BAL_BY_ACCT_T.sub_acct_nbr = c.sub_acct_nbr\n");
113 sqlText.append(" and LD_BCN_BAL_BY_ACCT_T.fin_object_cd = c.fin_object_cd\n");
114 sqlText.append(" and LD_BCN_BAL_BY_ACCT_T.fin_sub_obj_cd = c.fin_sub_obj_cd\n");
115 sqlText.append(" and c.pos_csf_fndstat_cd <> '");
116 // CSF code for a leave
117 insertionPoints.add(sqlText.length());
118 sqlText.append("')\n");
119 sqlText.append("WHERE person_unvl_id = ?\n");
120 sqlText.append(" AND EXISTS (SELECT 1\n");
121 sqlText.append(" FROM LD_BCN_CSF_TRCKR_T c1\n");
122 sqlText.append(" WHERE LD_BCN_BAL_BY_ACCT_T.univ_fiscal_yr = c1.univ_fiscal_yr\n");
123 sqlText.append(" and LD_BCN_BAL_BY_ACCT_T.fin_coa_cd = c1.fin_coa_cd\n");
124 sqlText.append(" and LD_BCN_BAL_BY_ACCT_T.account_nbr = c1.account_nbr\n");
125 sqlText.append(" and LD_BCN_BAL_BY_ACCT_T.sub_acct_nbr = c1.sub_acct_nbr\n");
126 sqlText.append(" and LD_BCN_BAL_BY_ACCT_T.fin_object_cd = c1.fin_object_cd\n");
127 sqlText.append(" and LD_BCN_BAL_BY_ACCT_T.fin_sub_obj_cd = c1.fin_sub_obj_cd\n");
128 sqlText.append(" and c1.pos_csf_fndstat_cd <> '");
129 // CSF code for a leave
130 insertionPoints.add(sqlText.length());
131 sqlText.append("')");
132 // update non-leave CSF FTE
133 setNonLeaveCSFFTE = new SQLForStep(sqlText,insertionPoints);
134 sqlText.delete(0,sqlText.length());
135 insertionPoints.clear();
136 //
137 // update leave CSF FTE
138 sqlText.append("UPDATE LD_BCN_BAL_BY_ACCT_T\n");
139 sqlText.append("SET pos_csf_lv_fte_qty = \n");
140 sqlText.append(" (SELECT sum(pos_csf_fte_qty)\n");
141 sqlText.append(" FROM LD_BCN_CSF_TRCKR_T c\n");
142 sqlText.append(" WHERE person_unvl_id = ?\n");
143 sqlText.append(" and LD_BCN_BAL_BY_ACCT_T.univ_fiscal_yr = c.univ_fiscal_yr\n");
144 sqlText.append(" and LD_BCN_BAL_BY_ACCT_T.fin_coa_cd = c.fin_coa_cd\n");
145 sqlText.append(" and LD_BCN_BAL_BY_ACCT_T.account_nbr = c.account_nbr\n");
146 sqlText.append(" and LD_BCN_BAL_BY_ACCT_T.sub_acct_nbr = c.sub_acct_nbr\n");
147 sqlText.append(" and LD_BCN_BAL_BY_ACCT_T.fin_object_cd = c.fin_object_cd\n");
148 sqlText.append(" and LD_BCN_BAL_BY_ACCT_T.fin_sub_obj_cd = c.fin_sub_obj_cd\n");
149 sqlText.append(" and c.pos_csf_fndstat_cd = '");
150 // CSF code for a leave
151 insertionPoints.add(sqlText.length());
152 sqlText.append("')\n");
153 sqlText.append("WHERE person_unvl_id = ?\n");
154 sqlText.append(" AND EXISTS (SELECT 1\n");
155 sqlText.append(" FROM LD_BCN_CSF_TRCKR_T c1\n");
156 sqlText.append(" WHERE LD_BCN_BAL_BY_ACCT_T.univ_fiscal_yr = c1.univ_fiscal_yr\n");
157 sqlText.append(" and LD_BCN_BAL_BY_ACCT_T.fin_coa_cd = c1.fin_coa_cd\n");
158 sqlText.append(" and LD_BCN_BAL_BY_ACCT_T.account_nbr = c1.account_nbr\n");
159 sqlText.append(" and LD_BCN_BAL_BY_ACCT_T.sub_acct_nbr = c1.sub_acct_nbr\n");
160 sqlText.append(" and LD_BCN_BAL_BY_ACCT_T.fin_object_cd = c1.fin_object_cd\n");
161 sqlText.append(" and LD_BCN_BAL_BY_ACCT_T.fin_sub_obj_cd = c1.fin_sub_obj_cd\n");
162 sqlText.append(" and c1.pos_csf_fndstat_cd = '");
163 // CSF code for a leave
164 insertionPoints.add(sqlText.length());
165 sqlText.append("')");
166 // update leave CSF FTE
167 setLeaveCSFFTE = new SQLForStep(sqlText,insertionPoints);
168 sqlText.delete(0,sqlText.length());
169 insertionPoints.clear();
170 }
171
172 /**
173 *
174 * @see org.kuali.kfs.module.bc.document.dataaccess.BudgetConstructionDocumentAccountObjectDetailReportDao#updateDocumentAccountObjectDetailReportTable(java.lang.String, java.lang.String, java.lang.Integer, java.lang.String, java.lang.String, java.lang.String)
175 */
176 public void updateDocumentAccountObjectDetailReportTable(String principalName, String documentNumber, Integer universityFiscalYear, String chartOfAccountsCode, String accountNumber, String subAccountNumber) {
177 // eliminate any rows already extant in the table for this user
178 this.clearTempTableByUnvlId("LD_BCN_BAL_BY_ACCT_T", "PERSON_UNVL_ID", principalName);
179 // insert the substring function into the SQL string
180 StringBuilder sqlText = this.getSqlSubStringFunction("t.fin_report_sort_cd",1,1);
181 ArrayList<String> stringsToInsert = new ArrayList<String>(1);
182 stringsToInsert.add(sqlText.toString());
183 getSimpleJdbcTemplate().update(initialInsert.getSQL(stringsToInsert),principalName, documentNumber, universityFiscalYear, chartOfAccountsCode, accountNumber, subAccountNumber);
184 // set the non-leave CSF FTE
185 stringsToInsert.clear();
186 stringsToInsert.add(new String(BCConstants.csfFundingStatusFlag.LEAVE.getFlagValue()));
187 stringsToInsert.add(stringsToInsert.get(0));
188 getSimpleJdbcTemplate().update(setNonLeaveCSFFTE.getSQL(stringsToInsert), principalName, principalName);
189 // set the CSF FTE for people on leave
190 // (we are inserting the same set of leave flags as in the previous step)
191 getSimpleJdbcTemplate().update(setLeaveCSFFTE.getSQL(stringsToInsert), principalName, principalName);
192 // clear the cache (OJB data from the last report for this user might still be cached)
193 persistenceService.clearCache();
194 }
195
196 public void setPersistenceService(PersistenceService persistenceService)
197 {
198 this.persistenceService = persistenceService;
199 }
200
201
202 }
203