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.apache.log4j.Logger;
021 import org.kuali.kfs.module.bc.batch.dataaccess.impl.SQLForStep;
022 import org.kuali.kfs.module.bc.document.dataaccess.BudgetConstructionMonthlyBudgetsCreateDeleteDao;
023 import org.kuali.rice.kns.service.PersistenceService;
024
025
026 public class BudgetConstructionMonthlyBudgetsCreateDeleteDaoJdbc extends BudgetConstructionDaoJdbcBase implements BudgetConstructionMonthlyBudgetsCreateDeleteDao {
027
028
029
030 private static Logger LOG = org.apache.log4j.Logger.getLogger(BudgetConstructionMonthlyBudgetsCreateDeleteDaoJdbc.class);
031
032
033 private static ArrayList<SQLForStep> deleteAllSql = new ArrayList<SQLForStep>(2);
034 private static ArrayList<SQLForStep> spreadRevenueSql = new ArrayList<SQLForStep>(2);
035 private static ArrayList<SQLForStep> spreadExpenditureSql = new ArrayList<SQLForStep>(3);
036
037 private PersistenceService persistenceService;
038
039 public BudgetConstructionMonthlyBudgetsCreateDeleteDaoJdbc()
040 {
041
042 StringBuilder sqlBuilder = new StringBuilder(5000);
043 ArrayList<Integer> insertionPoints = new ArrayList<Integer>();
044 // delete all rows for a given key from the budget construction monthly table
045 sqlBuilder.append("DELETE FROM LD_BCNSTR_MONTH_T\n");
046 sqlBuilder.append("WHERE (fdoc_nbr = ?)\n");
047 sqlBuilder.append(" AND (univ_fiscal_yr = ?)\n");
048 sqlBuilder.append(" AND (fin_coa_cd = ?)\n");
049 sqlBuilder.append(" AND (account_nbr = ?)\n");
050 sqlBuilder.append(" AND (sub_acct_nbr = ?)\n");
051 sqlBuilder.append(" AND (fin_obj_typ_cd IN ");
052 insertionPoints.add(sqlBuilder.length());
053 sqlBuilder.append(")");
054 // revenue
055 deleteAllSql.add(new SQLForStep(sqlBuilder,insertionPoints));
056 // expenditure (exact same thing at present)
057 deleteAllSql.add(new SQLForStep(sqlBuilder,insertionPoints));
058 sqlBuilder.delete(0,sqlBuilder.length());
059 insertionPoints.clear();
060
061 // SQL needed to spread revenue
062 // delete existing revenue for this key, so it can be spread again
063 sqlBuilder.append("DELETE FROM LD_BCNSTR_MONTH_T\n");
064 sqlBuilder.append("WHERE (fdoc_nbr = ?)\n");
065 sqlBuilder.append(" AND (univ_fiscal_yr = ?)\n");
066 sqlBuilder.append(" AND (fin_coa_cd = ?)\n");
067 sqlBuilder.append(" AND (account_nbr = ?)\n");
068 sqlBuilder.append(" AND (sub_acct_nbr = ?)\n");
069 sqlBuilder.append(" AND (fin_obj_typ_cd IN ");
070 insertionPoints.add(sqlBuilder.length());
071 sqlBuilder.append(")");
072 spreadRevenueSql.add(new SQLForStep(sqlBuilder,insertionPoints));
073 insertionPoints.clear();
074
075 sqlBuilder.delete(0,sqlBuilder.length());
076 // insert ALL revenue (since we do not re-calculate benefits on revenue, any revenue benefits object classes should be spread along with the other object classes
077 sqlBuilder.append("INSERT INTO LD_BCNSTR_MONTH_T\n");
078 sqlBuilder.append("(FDOC_NBR, UNIV_FISCAL_YR, FIN_COA_CD, ACCOUNT_NBR, SUB_ACCT_NBR, FIN_OBJECT_CD, FIN_SUB_OBJ_CD, FIN_BALANCE_TYP_CD, FIN_OBJ_TYP_CD,\n");
079 sqlBuilder.append(" FDOC_LN_MO1_AMT, FDOC_LN_MO2_AMT, FDOC_LN_MO3_AMT, FDOC_LN_MO4_AMT, FDOC_LN_MO5_AMT, FDOC_LN_MO6_AMT,\n");
080 sqlBuilder.append(" FDOC_LN_MO7_AMT, FDOC_LN_MO8_AMT, FDOC_LN_MO9_AMT, FDOC_LN_MO10_AMT, FDOC_LN_MO11_AMT, FDOC_LN_MO12_AMT)\n");
081 sqlBuilder.append("(SELECT ?, ?, ?, ?, ?, FIN_OBJECT_CD, FIN_SUB_OBJ_CD, FIN_BALANCE_TYP_CD, FIN_OBJ_TYP_CD,\n");
082 sqlBuilder.append(" ROUND((acln_annl_bal_amt / 12), 0) + \n");
083 sqlBuilder.append(" (acln_annl_bal_amt - (ROUND((acln_annl_bal_amt / 12), 0) * 12)),\n");
084 sqlBuilder.append(" ROUND((LD_PND_BCNSTR_GL_T.acln_annl_bal_amt / 12), 0),\n");
085 sqlBuilder.append(" ROUND((LD_PND_BCNSTR_GL_T.acln_annl_bal_amt / 12), 0),\n");
086 sqlBuilder.append(" ROUND((LD_PND_BCNSTR_GL_T.acln_annl_bal_amt / 12), 0),\n");
087 sqlBuilder.append(" ROUND((LD_PND_BCNSTR_GL_T.acln_annl_bal_amt / 12), 0),\n");
088 sqlBuilder.append(" ROUND((LD_PND_BCNSTR_GL_T.acln_annl_bal_amt / 12), 0),\n");
089 sqlBuilder.append(" ROUND((LD_PND_BCNSTR_GL_T.acln_annl_bal_amt / 12), 0),\n");
090 sqlBuilder.append(" ROUND((LD_PND_BCNSTR_GL_T.acln_annl_bal_amt / 12), 0),\n");
091 sqlBuilder.append(" ROUND((LD_PND_BCNSTR_GL_T.acln_annl_bal_amt / 12), 0),\n");
092 sqlBuilder.append(" ROUND((LD_PND_BCNSTR_GL_T.acln_annl_bal_amt / 12), 0),\n");
093 sqlBuilder.append(" ROUND((LD_PND_BCNSTR_GL_T.acln_annl_bal_amt / 12), 0),\n");
094 sqlBuilder.append(" ROUND((LD_PND_BCNSTR_GL_T.acln_annl_bal_amt / 12), 0)\n");
095 sqlBuilder.append(" FROM LD_PND_BCNSTR_GL_T\n");
096 sqlBuilder.append(" WHERE (fdoc_nbr = ?)\n");
097 sqlBuilder.append(" AND (univ_fiscal_yr = ?)\n");
098 sqlBuilder.append(" AND (fin_coa_cd = ?)\n");
099 sqlBuilder.append(" AND (account_nbr = ?)\n");
100 sqlBuilder.append(" AND (sub_acct_nbr = ?)\n");
101 sqlBuilder.append(" AND (fin_obj_typ_cd IN ");
102 insertionPoints.add(sqlBuilder.length());
103 sqlBuilder.append("))");
104 spreadRevenueSql.add(new SQLForStep(sqlBuilder,insertionPoints));
105 insertionPoints.clear();
106
107 sqlBuilder.delete(0,sqlBuilder.length());
108
109 // SQL to spread expenditure
110 // delete existing monthly expenditure (except for actual benefits objects--the benefits will be recalulated and spread later from the GL)
111 sqlBuilder.append("DELETE FROM LD_BCNSTR_MONTH_T\n");
112 sqlBuilder.append("WHERE (fdoc_nbr = ?)\n");
113 sqlBuilder.append(" AND (univ_fiscal_yr = ?)\n");
114 sqlBuilder.append(" AND (fin_coa_cd = ?)\n");
115 sqlBuilder.append(" AND (account_nbr = ?)\n");
116 sqlBuilder.append(" AND (sub_acct_nbr = ?)\n");
117 sqlBuilder.append(" AND (fin_obj_typ_cd IN ");
118 insertionPoints.add(sqlBuilder.length());
119 sqlBuilder.append(")\n");
120 sqlBuilder.append("AND (NOT EXISTS (SELECT 1\n");
121 sqlBuilder.append(" FROM LD_BENEFITS_CALC_T\n");
122 sqlBuilder.append(" WHERE (LD_BENEFITS_CALC_T.UNIV_FISCAL_YR = ?)\n");
123 sqlBuilder.append(" AND (LD_BENEFITS_CALC_T.FIN_COA_CD = ?)\n");
124 sqlBuilder.append(" AND (LD_BENEFITS_CALC_T.POS_FRNGBEN_OBJ_CD = LD_BCNSTR_MONTH_T.FIN_OBJECT_CD)))\n");
125 spreadExpenditureSql.add(new SQLForStep(sqlBuilder,insertionPoints));
126 insertionPoints.clear();
127
128 sqlBuilder.delete(0,sqlBuilder.length());
129 // spread the general ledger expenditure anew over the 12 months
130 sqlBuilder.append("INSERT INTO LD_BCNSTR_MONTH_T\n");
131 sqlBuilder.append("(FDOC_NBR, UNIV_FISCAL_YR, FIN_COA_CD, ACCOUNT_NBR, SUB_ACCT_NBR, FIN_OBJECT_CD, FIN_SUB_OBJ_CD, FIN_BALANCE_TYP_CD, FIN_OBJ_TYP_CD,\n");
132 sqlBuilder.append(" FDOC_LN_MO1_AMT, FDOC_LN_MO2_AMT, FDOC_LN_MO3_AMT, FDOC_LN_MO4_AMT, FDOC_LN_MO5_AMT, FDOC_LN_MO6_AMT,\n");
133 sqlBuilder.append(" FDOC_LN_MO7_AMT, FDOC_LN_MO8_AMT, FDOC_LN_MO9_AMT, FDOC_LN_MO10_AMT, FDOC_LN_MO11_AMT, FDOC_LN_MO12_AMT)\n");
134 sqlBuilder.append("(SELECT ?, ?, ?, ?, ?, FIN_OBJECT_CD, FIN_SUB_OBJ_CD, FIN_BALANCE_TYP_CD, FIN_OBJ_TYP_CD,\n");
135 sqlBuilder.append(" ROUND((acln_annl_bal_amt / 12), 0) + \n");
136 sqlBuilder.append(" (acln_annl_bal_amt - (ROUND((acln_annl_bal_amt / 12), 0) * 12)),\n");
137 sqlBuilder.append(" ROUND((LD_PND_BCNSTR_GL_T.acln_annl_bal_amt / 12), 0),\n");
138 sqlBuilder.append(" ROUND((LD_PND_BCNSTR_GL_T.acln_annl_bal_amt / 12), 0),\n");
139 sqlBuilder.append(" ROUND((LD_PND_BCNSTR_GL_T.acln_annl_bal_amt / 12), 0),\n");
140 sqlBuilder.append(" ROUND((LD_PND_BCNSTR_GL_T.acln_annl_bal_amt / 12), 0),\n");
141 sqlBuilder.append(" ROUND((LD_PND_BCNSTR_GL_T.acln_annl_bal_amt / 12), 0),\n");
142 sqlBuilder.append(" ROUND((LD_PND_BCNSTR_GL_T.acln_annl_bal_amt / 12), 0),\n");
143 sqlBuilder.append(" ROUND((LD_PND_BCNSTR_GL_T.acln_annl_bal_amt / 12), 0),\n");
144 sqlBuilder.append(" ROUND((LD_PND_BCNSTR_GL_T.acln_annl_bal_amt / 12), 0),\n");
145 sqlBuilder.append(" ROUND((LD_PND_BCNSTR_GL_T.acln_annl_bal_amt / 12), 0),\n");
146 sqlBuilder.append(" ROUND((LD_PND_BCNSTR_GL_T.acln_annl_bal_amt / 12), 0),\n");
147 sqlBuilder.append(" ROUND((LD_PND_BCNSTR_GL_T.acln_annl_bal_amt / 12), 0)\n");
148 sqlBuilder.append(" FROM LD_PND_BCNSTR_GL_T\n");
149 sqlBuilder.append(" WHERE (fdoc_nbr = ?)\n");
150 sqlBuilder.append(" AND (univ_fiscal_yr = ?)\n");
151 sqlBuilder.append(" AND (fin_coa_cd = ?)\n");
152 sqlBuilder.append(" AND (account_nbr = ?)\n");
153 sqlBuilder.append(" AND (sub_acct_nbr = ?)\n");
154 sqlBuilder.append("AND (NOT EXISTS (SELECT 1\n");
155 sqlBuilder.append(" FROM LD_BENEFITS_CALC_T\n");
156 sqlBuilder.append(" WHERE (LD_BENEFITS_CALC_T.UNIV_FISCAL_YR = ?)\n");
157 sqlBuilder.append(" AND (LD_BENEFITS_CALC_T.FIN_COA_CD = ?)\n");
158 sqlBuilder.append(" AND (LD_BENEFITS_CALC_T.POS_FRNGBEN_OBJ_CD = LD_PND_BCNSTR_GL_T.FIN_OBJECT_CD)))\n");
159 sqlBuilder.append(" AND (fin_obj_typ_cd IN ");
160 insertionPoints.add(sqlBuilder.length());
161 sqlBuilder.append("))");
162 spreadExpenditureSql.add(new SQLForStep(sqlBuilder,insertionPoints));
163 insertionPoints.clear();
164
165 sqlBuilder.delete(0,sqlBuilder.length());
166 // count the number of object classes eligible for fringe benefits, to signal the caller that benefits need to be recalculated and spread.
167 sqlBuilder.append("SELECT COUNT(*)\n");
168 sqlBuilder.append("FROM (LD_BCNSTR_MONTH_T INNER JOIN LD_LBR_OBJ_BENE_T\n");
169 sqlBuilder.append(" ON ((LD_BCNSTR_MONTH_T.UNIV_FISCAL_YR = LD_LBR_OBJ_BENE_T.UNIV_FISCAL_YR) AND\n");
170 sqlBuilder.append(" (LD_BCNSTR_MONTH_T.FIN_COA_CD = LD_LBR_OBJ_BENE_T.FIN_COA_CD) AND\n");
171 sqlBuilder.append(" (LD_BCNSTR_MONTH_T.FIN_OBJECT_CD = LD_LBR_OBJ_BENE_T.FIN_OBJECT_CD)))\n");
172 sqlBuilder.append("WHERE (LD_BCNSTR_MONTH_T.FDOC_NBR = ?)\n");
173 sqlBuilder.append(" AND (LD_BCNSTR_MONTH_T.UNIV_FISCAL_YR = ?)\n");
174 sqlBuilder.append(" AND (LD_BCNSTR_MONTH_T.FIN_COA_CD = ?)\n");
175 sqlBuilder.append(" AND (LD_BCNSTR_MONTH_T.ACCOUNT_NBR = ?)\n");
176 sqlBuilder.append(" AND (LD_BCNSTR_MONTH_T.SUB_ACCT_NBR = ?)\n");
177 sqlBuilder.append(" AND (LD_BCNSTR_MONTH_T.FIN_OBJ_TYP_CD IN ");
178 insertionPoints.add(sqlBuilder.length());
179 sqlBuilder.append(")\n");
180 spreadExpenditureSql.add(new SQLForStep(sqlBuilder,insertionPoints));
181 insertionPoints.clear();
182
183 }
184
185 /**
186 *
187 * @see org.kuali.kfs.module.bc.document.dataaccess.BudgetConstructionMonthlyBudgetsCreateDeleteDao#BudgetConstructionMonthlyBudgetsDeleteRevenue(java.lang.String, java.lang.String, java.lang.String, java.lang.String, java.lang.String, java.lang.String)
188 */
189 public void deleteBudgetConstructionMonthlyBudgetsRevenue(String documentNumber, Integer fiscalYear, String chartCode, String accountNumber, String subAccountNumber) {
190
191 // get the revenue object types as an SQL IN list
192 ArrayList<String> inSqlString = new ArrayList<String>();
193 inSqlString.add(getRevenueINList());
194
195 //run the delete-all SQL with the revenue object classes
196 int returnCount = getSimpleJdbcTemplate().update(deleteAllSql.get(0).getSQL(inSqlString), documentNumber, fiscalYear, chartCode, accountNumber, subAccountNumber);
197 LOG.warn(String.format("\n%s\n Expenditure (all) rows deleted for (%s,%d,%s,%s,%s) = %d",getDbPlatform().toString(),documentNumber, fiscalYear,chartCode,accountNumber, subAccountNumber, returnCount));
198 /**
199 * 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.
200 */
201 persistenceService.clearCache();
202 }
203
204 /**
205 *
206 * @see org.kuali.kfs.module.bc.document.dataaccess.BudgetConstructionMonthlyBudgetsCreateDeleteDao#BudgetConstructionMonthlyBudgetsDeleteExpenditure(java.lang.String, java.lang.String, java.lang.String, java.lang.String, java.lang.String, java.lang.String)
207 */
208 public void deleteBudgetConstructionMonthlyBudgetsExpenditure(String documentNumber, Integer fiscalYear, String chartCode, String accountNumber, String subAccountNumber) {
209
210 // get the expenditure object types as an SQL IN list
211 ArrayList<String> inSqlString = new ArrayList<String>();
212 inSqlString.add(getExpenditureINList());
213
214 // run the delete-all SQL with the expenditure object classes
215 int returnCount = getSimpleJdbcTemplate().update(deleteAllSql.get(1).getSQL(inSqlString), documentNumber, fiscalYear, chartCode, accountNumber, subAccountNumber);
216 LOG.warn(String.format("\n%s\n Expenditure (all) rows deleted for (%s,%d,%s,%s,%s) = %d",getDbPlatform().toString(),documentNumber, fiscalYear,chartCode,accountNumber, subAccountNumber, returnCount));
217 /**
218 * 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.
219 */
220 persistenceService.clearCache();
221 }
222
223 /**
224 *
225 * @see org.kuali.kfs.module.bc.document.dataaccess.BudgetConstructionMonthlyBudgetsCreateDeleteDao#BudgetConstructionMonthlyBudgetsSpreadRevenue(java.lang.String, java.lang.String, java.lang.String, java.lang.String, java.lang.String)
226 */
227 public void spreadBudgetConstructionMonthlyBudgetsRevenue(String documentNumber, Integer fiscalYear, String chartCode, String accountNumber, String subAccountNumber) {
228
229 // for revenue, we delete all existing rows, and spread all the corresponding rows in the general ledger
230 // if there is any revenue for benefits, it will be spread, not calculated based on non-benefits rows as expenditure benefits will be
231 // get the revenue IN list
232 ArrayList<String> inSqlString = new ArrayList<String>();
233 inSqlString.add(getRevenueINList());
234
235
236 // delete what is there now for this document for the revenue object classes
237 int returnCount = getSimpleJdbcTemplate().update(spreadRevenueSql.get(0).getSQL(inSqlString), documentNumber, fiscalYear, chartCode, accountNumber, subAccountNumber);
238 LOG.warn(String.format("\n%s\n RevenueSpread rows deleted for (%s,%d,%s,%s,%s) = %d",getDbPlatform().toString(),documentNumber, fiscalYear,chartCode,accountNumber, subAccountNumber, returnCount));
239
240 // run the create-monthly-budgets-from-GL SQL with the revenue object classes
241 returnCount = getSimpleJdbcTemplate().update(spreadRevenueSql.get(1).getSQL(inSqlString),documentNumber, fiscalYear, chartCode, accountNumber, subAccountNumber, documentNumber, fiscalYear, chartCode, accountNumber, subAccountNumber );
242 LOG.warn(String.format("\n%s\n RevenueSpread rows inserted for (%s,%d,%s,%s,%s) = %d",getDbPlatform().toString(),documentNumber, fiscalYear,chartCode,accountNumber, subAccountNumber, returnCount));
243 /**
244 * 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.
245 */
246 persistenceService.clearCache();
247 }
248
249
250 /**
251 *
252 * @see org.kuali.kfs.module.bc.document.dataaccess.BudgetConstructionMonthlyBudgetsCreateDeleteDao#BudgetConstructionMonthlyBudgetsSpreadExpenditure(java.lang.String, java.lang.String, java.lang.String, java.lang.String, java.lang.String)
253 */
254 public boolean spreadBudgetConstructionMonthlyBudgetsExpenditure(String documentNumber, Integer fiscalYear, String chartCode, String accountNumber, String subAccountNumber) {
255
256 // spread general ledger expenditures across 12 months, excluding benefits object types. benefits object expenditure will be recalculated and spread later, because several object codes eligible for benefits can target the same fringe benefit object
257 // get the expenditure object types as an SQL IN list
258 ArrayList<String> inSqlString = new ArrayList<String>();
259 inSqlString.add(getExpenditureINList());
260
261 // run the delete-all-except-benefits SQL with the expenditure object classes
262 int returnCount = getSimpleJdbcTemplate().update(spreadExpenditureSql.get(0).getSQL(inSqlString), documentNumber, fiscalYear, chartCode, accountNumber, subAccountNumber, fiscalYear, chartCode);
263 LOG.warn(String.format("\n%s\n ExpenditureSpread rows deleted for (%s,%d,%s,%s,%s) = %d",getDbPlatform().toString(),documentNumber, fiscalYear,chartCode,accountNumber, subAccountNumber, returnCount));
264
265 // run the create-monthly-budgets-from-GL SQL with the expenditure object classes
266 returnCount = getSimpleJdbcTemplate().update(spreadExpenditureSql.get(1).getSQL(inSqlString), documentNumber, fiscalYear, chartCode, accountNumber, subAccountNumber, documentNumber, fiscalYear, chartCode, accountNumber, subAccountNumber, fiscalYear, chartCode);
267 LOG.warn(String.format("\n%s\n ExpenditureSpread rows inserted for (%s,%d,%s,%s,%s) = %d",getDbPlatform().toString(),documentNumber, fiscalYear,chartCode,accountNumber, subAccountNumber, returnCount));
268 /**
269 * 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.
270 */
271 persistenceService.clearCache();
272
273 // tell the caller whether there were any benefits-eligible object classes with non-zero amounts
274 return(budgetConstructionMonthlyBudgetContainsBenefitsExpenditure(spreadExpenditureSql.get(2).getSQL(inSqlString), documentNumber, fiscalYear, chartCode, accountNumber, subAccountNumber));
275 }
276
277 /**
278 *
279 * return true if there are benefits object codes in the general ledger for the document, false otherwise
280 */
281 protected boolean budgetConstructionMonthlyBudgetContainsBenefitsExpenditure(String BenefitsObjectsCheckSQL, String documentNumber, Integer fiscalYear, String chartCode, String accountNumber, String subAccountNumber) {
282
283 Long numberOfBenefitsEligibleRows = getSimpleJdbcTemplate().queryForLong(BenefitsObjectsCheckSQL,documentNumber, fiscalYear, chartCode, accountNumber, subAccountNumber);
284 return (numberOfBenefitsEligibleRows != 0);
285 }
286
287 public void setPersistenceService(PersistenceService persistenceService)
288 {
289 this.persistenceService = persistenceService;
290 }
291
292
293 }