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.cam.document.dataaccess.impl;
017
018 import java.math.BigDecimal;
019 import java.sql.Date;
020 import java.sql.PreparedStatement;
021 import java.sql.ResultSet;
022 import java.sql.SQLException;
023 import java.util.ArrayList;
024 import java.util.Calendar;
025 import java.util.Collection;
026 import java.util.HashMap;
027 import java.util.HashSet;
028 import java.util.List;
029 import java.util.Map;
030 import java.util.Set;
031
032 import org.kuali.kfs.module.cam.CamsConstants;
033 import org.kuali.kfs.module.cam.batch.AssetPaymentInfo;
034 import org.kuali.kfs.module.cam.businessobject.Asset;
035 import org.kuali.kfs.module.cam.document.dataaccess.DepreciationBatchDao;
036 import org.kuali.kfs.sys.KFSConstants;
037 import org.kuali.kfs.sys.KFSKeyConstants;
038 import org.kuali.kfs.sys.businessobject.GeneralLedgerPendingEntry;
039 import org.kuali.kfs.sys.businessobject.UniversityDate;
040 import org.kuali.kfs.sys.dataaccess.UniversityDateDao;
041 import org.kuali.kfs.sys.service.OptionsService;
042 import org.kuali.kfs.sys.service.impl.KfsParameterConstants;
043 import org.kuali.rice.kns.dao.jdbc.PlatformAwareDaoBaseJdbc;
044 import org.kuali.rice.kns.service.BusinessObjectService;
045 import org.kuali.rice.kns.service.DateTimeService;
046 import org.kuali.rice.kns.service.KualiConfigurationService;
047 import org.kuali.rice.kns.service.ParameterService;
048 import org.kuali.rice.kns.util.Guid;
049 import org.kuali.rice.kns.util.KualiDecimal;
050 import org.springframework.dao.DataAccessException;
051 import org.springframework.jdbc.core.BatchPreparedStatementSetter;
052 import org.springframework.jdbc.core.PreparedStatementSetter;
053 import org.springframework.jdbc.core.ResultSetExtractor;
054
055 /**
056 * JDBC implementation of {@link DepreciationBatchDao}
057 */
058 public class DepreciationBatchDaoJdbc extends PlatformAwareDaoBaseJdbc implements DepreciationBatchDao {
059 private static org.apache.log4j.Logger LOG = org.apache.log4j.Logger.getLogger(DepreciationBatchDaoJdbc.class);
060 private KualiConfigurationService kualiConfigurationService;
061 private ParameterService parameterService;
062 private DateTimeService dateTimeService;
063 private OptionsService optionsService;
064 private BusinessObjectService businessObjectService;
065 private UniversityDateDao universityDateDao;
066
067 /**
068 * @see org.kuali.kfs.module.cam.document.dataaccess.DepreciationBatchDao#resetPeriodValuesWhenFirstFiscalPeriod(java.lang.Integer)
069 */
070 public void resetPeriodValuesWhenFirstFiscalPeriod(Integer fiscalMonth) throws Exception {
071 if (fiscalMonth == 1) {
072 LOG.info(CamsConstants.Depreciation.DEPRECIATION_BATCH + "Starting resetPeriodValuesWhenFirstFiscalPeriod");
073 // update previous year depreciation amount with sum of all periodic values for all asset payments
074 getJdbcTemplate().update("UPDATE CM_AST_PAYMENT_T SET AST_PRVYRDEPR1_AMT = (COALESCE(AST_PRD1_DEPR1_AMT, 0) + COALESCE(AST_PRD2_DEPR1_AMT, 0) + COALESCE(AST_PRD3_DEPR1_AMT, 0) + COALESCE(AST_PRD4_DEPR1_AMT, 0) + COALESCE(AST_PRD5_DEPR1_AMT, 0) + COALESCE(AST_PRD6_DEPR1_AMT, 0) + COALESCE(AST_PRD7_DEPR1_AMT, 0) + COALESCE(AST_PRD8_DEPR1_AMT, 0) + COALESCE(AST_PRD9_DEPR1_AMT, 0) + COALESCE(AST_PRD10DEPR1_AMT, 0) + COALESCE(AST_PRD11DEPR1_AMT, 0) + COALESCE(AST_PRD12DEPR1_AMT, 0))");
075 // reset periodic columns with zero dollar
076 LOG.info(CamsConstants.Depreciation.DEPRECIATION_BATCH + "Fiscal month = 1. Therefore, initializing each month with zeros.");
077 getJdbcTemplate().update("UPDATE CM_AST_PAYMENT_T SET AST_PRD1_DEPR1_AMT =0.0, AST_PRD2_DEPR1_AMT =0.0, AST_PRD3_DEPR1_AMT =0.0, AST_PRD4_DEPR1_AMT =0.0, AST_PRD5_DEPR1_AMT =0.0, AST_PRD6_DEPR1_AMT =0.0, AST_PRD7_DEPR1_AMT =0.0, AST_PRD8_DEPR1_AMT =0.0, AST_PRD9_DEPR1_AMT =0.0, AST_PRD10DEPR1_AMT =0.0, AST_PRD11DEPR1_AMT =0.0, AST_PRD12DEPR1_AMT=0.0");
078 LOG.info(CamsConstants.Depreciation.DEPRECIATION_BATCH + "Finished resetPeriodValuesWhenFirstFiscalPeriod");
079 }
080 }
081
082 /**
083 * @see org.kuali.kfs.module.cam.document.dataaccess.DepreciationBatchDao#updateAssetPayments(java.util.List, java.lang.Integer)
084 */
085 public void updateAssetPayments(final List<AssetPaymentInfo> assetPayments, final Integer fiscalMonth) {
086 LOG.info(CamsConstants.Depreciation.DEPRECIATION_BATCH + "Batch updating [" + assetPayments.size() + "] payments");
087 getJdbcTemplate().batchUpdate("UPDATE CM_AST_PAYMENT_T SET AST_ACUM_DEPR1_AMT=? , AST_PRD" + fiscalMonth + (fiscalMonth < 10 ? "_" : "") + "DEPR1_AMT = ? WHERE CPTLAST_NBR = ? AND AST_PMT_SEQ_NBR = ? ", new BatchPreparedStatementSetter() {
088
089 public int getBatchSize() {
090 return assetPayments.size();
091 }
092
093 public void setValues(PreparedStatement pstmt, int index) throws SQLException {
094 pstmt.setBigDecimal(1, assetPayments.get(index).getAccumulatedPrimaryDepreciationAmount().bigDecimalValue());
095 pstmt.setBigDecimal(2, assetPayments.get(index).getTransactionAmount().bigDecimalValue());
096 pstmt.setLong(3, assetPayments.get(index).getCapitalAssetNumber());
097 pstmt.setInt(4, assetPayments.get(index).getPaymentSequenceNumber());
098 }
099 });
100 }
101
102
103 /**
104 * @see org.kuali.kfs.module.cam.document.dataaccess.DepreciationBatchDao#updateAssetsCreatedInLastFiscalPeriod(java.lang.Integer,
105 * java.lang.Integer)
106 */
107 public void updateAssetsCreatedInLastFiscalPeriod(final Integer fiscalMonth, final Integer fiscalYear) {
108 // If we are in the last month of the fiscal year
109 if (fiscalMonth == 12) {
110 LOG.info(CamsConstants.Depreciation.DEPRECIATION_BATCH + "Starting updateAssetsCreatedInLastFiscalPeriod()");
111 // Getting last date of fiscal year
112 final UniversityDate lastFiscalYearDate = universityDateDao.getLastFiscalYearDate(fiscalYear);
113 if (lastFiscalYearDate == null) {
114 throw new IllegalStateException(kualiConfigurationService.getPropertyString(KFSKeyConstants.ERROR_UNIV_DATE_NOT_FOUND));
115 }
116
117 List<String> movableEquipmentObjectSubTypes = new ArrayList<String>();
118 if (parameterService.parameterExists(Asset.class, CamsConstants.Parameters.MOVABLE_EQUIPMENT_OBJECT_SUB_TYPES)) {
119 movableEquipmentObjectSubTypes = parameterService.getParameterValues(Asset.class, CamsConstants.Parameters.MOVABLE_EQUIPMENT_OBJECT_SUB_TYPES);
120 }
121
122 // Only update assets with a object sub type code equals to any MOVABLE_EQUIPMENT_OBJECT_SUB_TYPES.
123 if (!movableEquipmentObjectSubTypes.isEmpty()) {
124 getJdbcTemplate().update("UPDATE CM_CPTLAST_T SET CPTL_AST_IN_SRVC_DT=?, CPTL_AST_DEPR_DT=?, FDOC_POST_PRD_CD=? , FDOC_POST_YR=? WHERE CPTLAST_CRT_DT > ? AND FIN_OBJ_SUB_TYP_CD IN (" + buildINValues(movableEquipmentObjectSubTypes) + ")", new PreparedStatementSetter() {
125 public void setValues(PreparedStatement ps) throws SQLException {
126 ps.setDate(1, lastFiscalYearDate.getUniversityDate());
127 ps.setDate(2, lastFiscalYearDate.getUniversityDate());
128 ps.setString(3, fiscalMonth.toString());
129 ps.setInt(4, fiscalYear);
130 ps.setDate(5, lastFiscalYearDate.getUniversityDate());
131 }
132 });
133 }
134 LOG.info(CamsConstants.Depreciation.DEPRECIATION_BATCH + "Finished updateAssetsCreatedInLastFiscalPeriod()");
135 }
136 }
137
138
139 /**
140 * @see org.kuali.kfs.module.cam.document.dataaccess.DepreciationBatchDao#savePendingGLEntries(java.util.List)
141 */
142 public void savePendingGLEntries(final List<GeneralLedgerPendingEntry> glPendingEntries) {
143 LOG.info(CamsConstants.Depreciation.DEPRECIATION_BATCH + "Batch update of [" + glPendingEntries.size() + "] glpes");
144 // we need batch insert for gl pending entry
145 getJdbcTemplate().batchUpdate("INSERT INTO GL_PENDING_ENTRY_T " + " (FS_ORIGIN_CD,FDOC_NBR,TRN_ENTR_SEQ_NBR,OBJ_ID,VER_NBR,FIN_COA_CD,ACCOUNT_NBR,SUB_ACCT_NBR,FIN_OBJECT_CD,FIN_SUB_OBJ_CD,FIN_BALANCE_TYP_CD,FIN_OBJ_TYP_CD,UNIV_FISCAL_YR,UNIV_FISCAL_PRD_CD,TRN_LDGR_ENTR_DESC,TRN_LDGR_ENTR_AMT,TRN_DEBIT_CRDT_CD,TRANSACTION_DT,FDOC_TYP_CD,ORG_DOC_NBR,PROJECT_CD,ORG_REFERENCE_ID,FDOC_REF_TYP_CD,FS_REF_ORIGIN_CD,FDOC_REF_NBR,FDOC_REVERSAL_DT,TRN_ENCUM_UPDT_CD,FDOC_APPROVED_CD,ACCT_SF_FINOBJ_CD,TRNENTR_PROCESS_TM) VALUES " + "(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)", new BatchPreparedStatementSetter() {
146
147 public int getBatchSize() {
148 return glPendingEntries.size();
149 }
150
151 public void setValues(PreparedStatement pstmt, int index) throws SQLException {
152 GeneralLedgerPendingEntry generalLedgerPendingEntry = glPendingEntries.get(index);
153 pstmt.setObject(1, generalLedgerPendingEntry.getFinancialSystemOriginationCode());
154 pstmt.setObject(2, generalLedgerPendingEntry.getDocumentNumber());
155 pstmt.setObject(3, generalLedgerPendingEntry.getTransactionLedgerEntrySequenceNumber());
156 pstmt.setObject(4, new Guid().toString());
157 pstmt.setObject(5, generalLedgerPendingEntry.getVersionNumber());
158 pstmt.setObject(6, generalLedgerPendingEntry.getChartOfAccountsCode());
159 pstmt.setObject(7, generalLedgerPendingEntry.getAccountNumber());
160 pstmt.setObject(8, generalLedgerPendingEntry.getSubAccountNumber());
161 pstmt.setObject(9, generalLedgerPendingEntry.getFinancialObjectCode());
162 pstmt.setObject(10, generalLedgerPendingEntry.getFinancialSubObjectCode());
163 pstmt.setObject(11, generalLedgerPendingEntry.getFinancialBalanceTypeCode());
164 pstmt.setObject(12, generalLedgerPendingEntry.getFinancialObjectTypeCode());
165 pstmt.setObject(13, generalLedgerPendingEntry.getUniversityFiscalYear());
166 pstmt.setObject(14, generalLedgerPendingEntry.getUniversityFiscalPeriodCode());
167 pstmt.setObject(15, generalLedgerPendingEntry.getTransactionLedgerEntryDescription());
168 pstmt.setObject(16, generalLedgerPendingEntry.getTransactionLedgerEntryAmount().bigDecimalValue());
169 pstmt.setObject(17, generalLedgerPendingEntry.getTransactionDebitCreditCode());
170 pstmt.setObject(18, generalLedgerPendingEntry.getTransactionDate());
171 pstmt.setObject(19, generalLedgerPendingEntry.getFinancialDocumentTypeCode());
172 pstmt.setObject(20, generalLedgerPendingEntry.getOrganizationDocumentNumber());
173 pstmt.setObject(21, generalLedgerPendingEntry.getProjectCode());
174 pstmt.setObject(22, generalLedgerPendingEntry.getOrganizationReferenceId());
175 pstmt.setObject(23, generalLedgerPendingEntry.getReferenceFinancialDocumentTypeCode());
176 pstmt.setObject(24, generalLedgerPendingEntry.getReferenceFinancialSystemOriginationCode());
177 pstmt.setObject(25, generalLedgerPendingEntry.getReferenceFinancialDocumentNumber());
178 pstmt.setObject(26, generalLedgerPendingEntry.getFinancialDocumentReversalDate());
179 pstmt.setObject(27, generalLedgerPendingEntry.getTransactionEncumbranceUpdateCode());
180 pstmt.setObject(28, generalLedgerPendingEntry.getFinancialDocumentApprovedCode());
181 pstmt.setObject(29, generalLedgerPendingEntry.getAcctSufficientFundsFinObjCd());
182 pstmt.setObject(30, generalLedgerPendingEntry.getTransactionEntryProcessedTs());
183 }
184
185 });
186 }
187
188 /**
189 * @see org.kuali.kfs.module.cam.document.dataaccess.DepreciationBatchDao#getListOfDepreciableAssetPaymentInfo(java.lang.Integer,
190 * java.lang.Integer, java.util.Calendar)
191 */
192 public Collection<AssetPaymentInfo> getListOfDepreciableAssetPaymentInfo(Integer fiscalYear, Integer fiscalMonth, final Calendar depreciationDate) {
193 LOG.info(CamsConstants.Depreciation.DEPRECIATION_BATCH + "Starting to get the list of depreciable asset payment list.");
194 final List<AssetPaymentInfo> assetPaymentDetails = new ArrayList<AssetPaymentInfo>();
195 List<String> depreciationMethodList = new ArrayList<String>();
196 List<String> notAcceptedAssetStatus = new ArrayList<String>();
197 depreciationMethodList.add(CamsConstants.Asset.DEPRECIATION_METHOD_SALVAGE_VALUE_CODE);
198 depreciationMethodList.add(CamsConstants.Asset.DEPRECIATION_METHOD_STRAIGHT_LINE_CODE);
199 List<String> federallyOwnedObjectSubTypes = getFederallyOwnedObjectSubTypes();
200 if (parameterService.parameterExists(KfsParameterConstants.CAPITAL_ASSETS_BATCH.class, CamsConstants.Parameters.NON_DEPRECIABLE_NON_CAPITAL_ASSETS_STATUS_CODES)) {
201 notAcceptedAssetStatus = parameterService.getParameterValues(KfsParameterConstants.CAPITAL_ASSETS_BATCH.class, CamsConstants.Parameters.NON_DEPRECIABLE_NON_CAPITAL_ASSETS_STATUS_CODES);
202 }
203 String sql = "SELECT A0.CPTLAST_NBR,A0.AST_PMT_SEQ_NBR,A1.CPTL_AST_DEPR_DT,A1.AST_DEPR_MTHD1_CD,A1.CPTLAST_SALVAG_AMT,";
204 sql = sql + "A2.CPTLAST_DEPRLF_LMT,A5.ORG_PLNT_COA_CD,A5.ORG_PLNT_ACCT_NBR,A5.CMP_PLNT_COA_CD,A5.CMP_PLNT_ACCT_NBR,A3.FIN_OBJ_TYP_CD, ";
205 sql = sql + "A3.FIN_OBJ_SUB_TYP_CD, A0.AST_DEPR1_BASE_AMT,A0.FIN_OBJECT_CD, A0.AST_ACUM_DEPR1_AMT,A0.SUB_ACCT_NBR,A0.FIN_SUB_OBJ_CD,A0.PROJECT_CD, A0.FIN_COA_CD";
206 sql = sql + buildCriteria(fiscalYear, fiscalMonth, depreciationMethodList, notAcceptedAssetStatus, federallyOwnedObjectSubTypes, false, false);
207 sql = sql + "ORDER BY A0.CPTLAST_NBR, A0.FS_ORIGIN_CD, A0.ACCOUNT_NBR, A0.SUB_ACCT_NBR, A0.FIN_OBJECT_CD, A0.FIN_SUB_OBJ_CD, A3.FIN_OBJ_TYP_CD, A0.PROJECT_CD";
208 getJdbcTemplate().query(sql, preparedStatementSetter(depreciationDate), new ResultSetExtractor() {
209 public Object extractData(ResultSet rs) throws SQLException, DataAccessException {
210 int counter = 0;
211 while (rs != null && rs.next()) {
212 counter++;
213 if (counter % 10000 == 0) {
214 LOG.info("Reading result row at " + new java.util.Date() + " - " + counter);
215 }
216 AssetPaymentInfo assetPaymentInfo = new AssetPaymentInfo();
217 assetPaymentInfo.setCapitalAssetNumber(rs.getLong(1));
218 assetPaymentInfo.setPaymentSequenceNumber(rs.getInt(2));
219 assetPaymentInfo.setDepreciationDate(rs.getDate(3));
220 String deprMethod = rs.getString(4);
221 assetPaymentInfo.setPrimaryDepreciationMethodCode(deprMethod == null ? CamsConstants.Asset.DEPRECIATION_METHOD_STRAIGHT_LINE_CODE : deprMethod);
222 BigDecimal salvage = rs.getBigDecimal(5);
223 assetPaymentInfo.setSalvageAmount(salvage == null ? KualiDecimal.ZERO : new KualiDecimal(salvage));
224 assetPaymentInfo.setDepreciableLifeLimit(rs.getInt(6));
225 assetPaymentInfo.setOrganizationPlantChartCode(rs.getString(7));
226 assetPaymentInfo.setOrganizationPlantAccountNumber(rs.getString(8));
227 assetPaymentInfo.setCampusPlantChartCode(rs.getString(9));
228 assetPaymentInfo.setCampusPlantAccountNumber(rs.getString(10));
229 assetPaymentInfo.setFinancialObjectTypeCode(rs.getString(11));
230 assetPaymentInfo.setFinancialObjectSubTypeCode(rs.getString(12));
231
232 BigDecimal primaryDeprAmt = rs.getBigDecimal(13);
233 assetPaymentInfo.setPrimaryDepreciationBaseAmount(primaryDeprAmt == null ? KualiDecimal.ZERO : new KualiDecimal(primaryDeprAmt));
234 assetPaymentInfo.setFinancialObjectCode(rs.getString(14));
235 BigDecimal accumDeprAmt = rs.getBigDecimal(15);
236 assetPaymentInfo.setAccumulatedPrimaryDepreciationAmount(accumDeprAmt == null ? KualiDecimal.ZERO : new KualiDecimal(accumDeprAmt));
237 assetPaymentInfo.setSubAccountNumber(rs.getString(16));
238 assetPaymentInfo.setFinancialSubObjectCode(rs.getString(17));
239 assetPaymentInfo.setProjectCode(rs.getString(18));
240 assetPaymentInfo.setChartOfAccountsCode(rs.getString(19));
241
242 assetPaymentDetails.add(assetPaymentInfo);
243 }
244 return assetPaymentDetails;
245 }
246
247 });
248 LOG.info(CamsConstants.Depreciation.DEPRECIATION_BATCH + "Finished getting list of [" + assetPaymentDetails.size() + "] depreciable asset payment list.");
249 return assetPaymentDetails;
250 }
251
252 protected PreparedStatementSetter preparedStatementSetter(final Calendar depreciationDate) {
253 return new PreparedStatementSetter() {
254 public void setValues(PreparedStatement pstmt) throws SQLException {
255 Calendar DateOf1900 = Calendar.getInstance();
256 DateOf1900.set(1900, 0, 1);
257 pstmt.setDate(1, new Date(depreciationDate.getTimeInMillis()));
258 pstmt.setDate(2, new Date(DateOf1900.getTimeInMillis()));
259 }
260 };
261 }
262
263 protected String buildCriteria(Integer fiscalYear, Integer fiscalMonth, List<String> depreciationMethodList, List<String> notAcceptedAssetStatus, List<String> federallyOwnedObjectSubTypes, boolean includeFederal, boolean includePending) {
264 String sql = " FROM CM_AST_PAYMENT_T A0 INNER JOIN CM_CPTLAST_T A1 ON A0.CPTLAST_NBR=A1.CPTLAST_NBR INNER JOIN ";
265 sql = sql + "CM_ASSET_TYPE_T A2 ON A1.CPTLAST_TYP_CD=A2.CPTLAST_TYP_CD INNER JOIN CA_OBJECT_CODE_T A3 ON " + fiscalYear + "=A3.UNIV_FISCAL_YR ";
266 sql = sql + "AND A0.FIN_COA_CD=A3.FIN_COA_CD AND A0.FIN_OBJECT_CD=A3.FIN_OBJECT_CD INNER JOIN CA_ACCOUNT_T A4 ON A0.FIN_COA_CD=A4.FIN_COA_CD ";
267 sql = sql + "AND A0.ACCOUNT_NBR=A4.ACCOUNT_NBR INNER JOIN CA_ORG_T A5 ON A4.FIN_COA_CD=A5.FIN_COA_CD AND A4.ORG_CD=A5.ORG_CD ";
268 sql = sql + "WHERE (A0.AST_DEPR1_BASE_AMT IS NOT NULL AND A0.AST_DEPR1_BASE_AMT <> 0) AND (A0.AST_TRNFR_PMT_CD ";
269 sql = sql + "IN ('N','') OR A0.AST_TRNFR_PMT_CD IS NULL ) AND ( A1.AST_DEPR_MTHD1_CD IS NULL OR A1.AST_DEPR_MTHD1_CD IN (" + buildINValues(depreciationMethodList) + ") ) ";
270 sql = sql + "AND (A1.CPTL_AST_DEPR_DT IS NOT NULL AND A1.CPTL_AST_DEPR_DT <= ? AND A1.CPTL_AST_DEPR_DT <> ?) AND ";
271 sql = sql + "(A1.AST_RETIR_FSCL_YR IS NULL OR A1.AST_RETIR_PRD_CD IS NULL OR A1.AST_RETIR_FSCL_YR > " + fiscalYear + " OR (A1.AST_RETIR_FSCL_YR = " + fiscalYear + " AND A1.AST_RETIR_PRD_CD > " + fiscalMonth + ")) ";
272 sql = sql + "AND A1.AST_INVN_STAT_CD NOT IN (" + buildINValues(notAcceptedAssetStatus) + ")AND A2.CPTLAST_DEPRLF_LMT > 0 ";
273 if (includeFederal) {
274 sql = sql + "AND A3.FIN_OBJ_SUB_TYP_CD IN (" + buildINValues(federallyOwnedObjectSubTypes) + ")";
275 }
276 else {
277 sql = sql + "AND A3.FIN_OBJ_SUB_TYP_CD NOT IN (" + buildINValues(federallyOwnedObjectSubTypes) + ")";
278 }
279 if (!includePending) {
280 sql = sql + " AND NOT EXISTS (SELECT 1 FROM CM_AST_TRNFR_DOC_T TRFR, FS_DOC_HEADER_T HDR WHERE HDR.FDOC_NBR = TRFR.FDOC_NBR AND ";
281 sql = sql + " HDR.FDOC_STATUS_CD = '" + KFSConstants.DocumentStatusCodes.ENROUTE + "' AND TRFR.CPTLAST_NBR = A0.CPTLAST_NBR) ";
282 sql = sql + " AND NOT EXISTS (SELECT 1 FROM CM_AST_RETIRE_DTL_T DTL, FS_DOC_HEADER_T HDR WHERE HDR.FDOC_NBR = DTL.FDOC_NBR ";
283 sql = sql + " AND HDR.FDOC_STATUS_CD = '" + KFSConstants.DocumentStatusCodes.ENROUTE + "' AND DTL.CPTLAST_NBR = A0.CPTLAST_NBR) ";
284 }
285 return sql;
286 }
287
288 /**
289 * @see org.kuali.kfs.module.cam.document.dataaccess.DepreciationBatchDao#getFullyDepreciatedAssetCount()
290 */
291 public Integer getFullyDepreciatedAssetCount() {
292 int count = getJdbcTemplate().queryForInt("SELECT COUNT(1) FROM CM_CPTLAST_T AST, (SELECT CPTLAST_NBR, (SUM(AST_DEPR1_BASE_AMT - AST_ACUM_DEPR1_AMT) - (SELECT 0.0+CPTLAST_SALVAG_AMT FROM CM_CPTLAST_T X WHERE X.CPTLAST_NBR = Y.CPTLAST_NBR)) BAL FROM CM_AST_PAYMENT_T Y WHERE AST_DEPR1_BASE_AMT IS NOT NULL AND AST_DEPR1_BASE_AMT <> 0.0 AND AST_ACUM_DEPR1_AMT IS NOT NULL AND AST_ACUM_DEPR1_AMT <> 0.0 AND (AST_TRNFR_PMT_CD = 'N' OR AST_TRNFR_PMT_CD = '' OR AST_TRNFR_PMT_CD IS NULL) GROUP BY CPTLAST_NBR) PMT WHERE PMT.BAL = 0.0 AND AST.CPTLAST_NBR = PMT.CPTLAST_NBR");
293 return count;
294 }
295
296 public Object[] getAssetAndPaymentCount(Integer fiscalYear, Integer fiscalMonth, final Calendar depreciationDate, boolean includePending) {
297 final Object[] data = new Object[2];
298 List<String> depreciationMethodList = new ArrayList<String>();
299 List<String> notAcceptedAssetStatus = new ArrayList<String>();
300 depreciationMethodList.add(CamsConstants.Asset.DEPRECIATION_METHOD_SALVAGE_VALUE_CODE);
301 depreciationMethodList.add(CamsConstants.Asset.DEPRECIATION_METHOD_STRAIGHT_LINE_CODE);
302 List<String> federallyOwnedObjectSubTypes = getFederallyOwnedObjectSubTypes();
303 if (parameterService.parameterExists(KfsParameterConstants.CAPITAL_ASSETS_BATCH.class, CamsConstants.Parameters.NON_DEPRECIABLE_NON_CAPITAL_ASSETS_STATUS_CODES)) {
304 notAcceptedAssetStatus = parameterService.getParameterValues(KfsParameterConstants.CAPITAL_ASSETS_BATCH.class, CamsConstants.Parameters.NON_DEPRECIABLE_NON_CAPITAL_ASSETS_STATUS_CODES);
305 }
306 String sql = "SELECT COUNT(DISTINCT A0.CPTLAST_NBR), COUNT(1) " + buildCriteria(fiscalYear, fiscalMonth, depreciationMethodList, notAcceptedAssetStatus, federallyOwnedObjectSubTypes, false, includePending);
307 getJdbcTemplate().query(sql, preparedStatementSetter(depreciationDate), new ResultSetExtractor() {
308
309 public Object extractData(ResultSet rs) throws SQLException, DataAccessException {
310 if (rs != null && rs.next()) {
311 data[0] = rs.getInt(1);
312 data[1] = rs.getInt(2);
313 }
314 return data;
315 }
316
317 });
318 return data;
319 }
320
321 public Object[] getFederallyOwnedAssetAndPaymentCount(Integer fiscalYear, Integer fiscalMonth, final Calendar depreciationDate) {
322 final Object[] data = new Object[2];
323 List<String> depreciationMethodList = new ArrayList<String>();
324 List<String> notAcceptedAssetStatus = new ArrayList<String>();
325 depreciationMethodList.add(CamsConstants.Asset.DEPRECIATION_METHOD_SALVAGE_VALUE_CODE);
326 depreciationMethodList.add(CamsConstants.Asset.DEPRECIATION_METHOD_STRAIGHT_LINE_CODE);
327 List<String> federallyOwnedObjectSubTypes = getFederallyOwnedObjectSubTypes();
328 if (parameterService.parameterExists(KfsParameterConstants.CAPITAL_ASSETS_BATCH.class, CamsConstants.Parameters.NON_DEPRECIABLE_NON_CAPITAL_ASSETS_STATUS_CODES)) {
329 notAcceptedAssetStatus = parameterService.getParameterValues(KfsParameterConstants.CAPITAL_ASSETS_BATCH.class, CamsConstants.Parameters.NON_DEPRECIABLE_NON_CAPITAL_ASSETS_STATUS_CODES);
330 }
331 String sql = "SELECT COUNT(DISTINCT A0.CPTLAST_NBR), COUNT(1) " + buildCriteria(fiscalYear, fiscalMonth, depreciationMethodList, notAcceptedAssetStatus, federallyOwnedObjectSubTypes, true, true);
332 getJdbcTemplate().query(sql, preparedStatementSetter(depreciationDate), new ResultSetExtractor() {
333
334 public Object extractData(ResultSet rs) throws SQLException, DataAccessException {
335 if (rs != null && rs.next()) {
336 data[0] = rs.getInt(1);
337 data[1] = rs.getInt(2);
338 }
339 return data;
340 }
341
342 });
343 return data;
344 }
345
346 /**
347 * @see org.kuali.kfs.module.cam.document.dataaccess.DepreciationBatchDao#getPrimaryDepreciationBaseAmountForSV()
348 */
349 public Map<Long, KualiDecimal> getPrimaryDepreciationBaseAmountForSV() {
350 final Map<Long, KualiDecimal> amountMap = new HashMap<Long, KualiDecimal>();
351 getJdbcTemplate().query("SELECT PMT.CPTLAST_NBR, SUM(PMT.AST_DEPR1_BASE_AMT) FROM CM_CPTLAST_T AST, CM_AST_PAYMENT_T PMT WHERE AST.CPTLAST_NBR = PMT.CPTLAST_NBR AND AST.AST_DEPR_MTHD1_CD = '" + CamsConstants.Asset.DEPRECIATION_METHOD_SALVAGE_VALUE_CODE + "' GROUP BY PMT.CPTLAST_NBR", new ResultSetExtractor() {
352
353 public Object extractData(ResultSet rs) throws SQLException, DataAccessException {
354 while (rs != null && rs.next()) {
355 amountMap.put(rs.getLong(1), new KualiDecimal(rs.getBigDecimal(2)));
356 }
357 return amountMap;
358 }
359
360 });
361 return amountMap;
362 }
363
364 /**
365 * @see org.kuali.kfs.module.cam.document.dataaccess.DepreciationBatchDao#getTransferDocLockedAssetCount()
366 */
367 public Integer getTransferDocLockedAssetCount() {
368 return getJdbcTemplate().queryForInt("select count(1) from CM_AST_TRNFR_DOC_T t inner join FS_DOC_HEADER_T h on t.fdoc_nbr = h.fdoc_nbr where h.fdoc_status_cd ='" + KFSConstants.DocumentStatusCodes.ENROUTE + "'");
369 }
370
371 /**
372 * @see org.kuali.kfs.module.cam.document.dataaccess.DepreciationBatchDao#getRetireDocLockedAssetCount()
373 */
374 public Integer getRetireDocLockedAssetCount() {
375 return getJdbcTemplate().queryForInt("select count(1) from CM_AST_RETIRE_DTL_T t inner join FS_DOC_HEADER_T h on t.fdoc_nbr = h.fdoc_nbr where h.fdoc_status_cd ='" + KFSConstants.DocumentStatusCodes.ENROUTE + "'");
376 }
377
378 /**
379 * @see org.kuali.kfs.module.cam.document.dataaccess.DepreciationBatchDao#getLockedAssets()
380 */
381 public Set<Long> getLockedAssets() {
382 final Set<Long> assets = new HashSet<Long>();
383 getJdbcTemplate().query("select t.cptlast_nbr from CM_AST_RETIRE_DTL_T t inner join FS_DOC_HEADER_T h on t.fdoc_nbr = h.fdoc_nbr where h.fdoc_status_cd = '" + KFSConstants.DocumentStatusCodes.ENROUTE + "' union select t.cptlast_nbr from CM_AST_TRNFR_DOC_T t inner join FS_DOC_HEADER_T h on t.fdoc_nbr = h.fdoc_nbr where h.fdoc_status_cd = '" + KFSConstants.DocumentStatusCodes.ENROUTE + "'", new ResultSetExtractor() {
384 public Object extractData(ResultSet rs) throws SQLException, DataAccessException {
385 assets.add(rs.getLong(1));
386 return assets;
387 }
388 });
389 return assets;
390 }
391
392 /**
393 * This method the value of the system parameter NON_DEPRECIABLE_FEDERALLY_OWNED_OBJECT_SUB_TYPES
394 *
395 * @return
396 */
397 protected List<String> getFederallyOwnedObjectSubTypes() {
398 LOG.info(CamsConstants.Depreciation.DEPRECIATION_BATCH + "getting the federally owned object subtype codes.");
399
400 List<String> federallyOwnedObjectSubTypes = new ArrayList<String>();
401 if (parameterService.parameterExists(KfsParameterConstants.CAPITAL_ASSETS_BATCH.class, CamsConstants.Parameters.NON_DEPRECIABLE_FEDERALLY_OWNED_OBJECT_SUB_TYPES)) {
402 federallyOwnedObjectSubTypes = parameterService.getParameterValues(KfsParameterConstants.CAPITAL_ASSETS_BATCH.class, CamsConstants.Parameters.NON_DEPRECIABLE_FEDERALLY_OWNED_OBJECT_SUB_TYPES);
403 }
404 LOG.info(CamsConstants.Depreciation.DEPRECIATION_BATCH + "Finished getting the federally owned object subtype codes which are:" + federallyOwnedObjectSubTypes.toString());
405 return federallyOwnedObjectSubTypes;
406 }
407
408
409 /**
410 * Utility method that will convert a list into IN string clause for SQL
411 *
412 * @param list values
413 * @return concatenated string
414 */
415 protected String buildINValues(List<String> list) {
416 if (list.isEmpty()) {
417 return "''";
418 }
419 String returnValue = "";
420 for (String string : list) {
421 returnValue = returnValue + "'" + string + "',";
422 }
423 return returnValue.substring(0, returnValue.lastIndexOf(','));
424 }
425
426 /**
427 * Gets the kualiConfigurationService attribute.
428 *
429 * @return Returns the kualiConfigurationService.
430 */
431 public KualiConfigurationService getKualiConfigurationService() {
432 return kualiConfigurationService;
433 }
434
435 /**
436 * Sets the kualiConfigurationService attribute value.
437 *
438 * @param kualiConfigurationService The kualiConfigurationService to set.
439 */
440 public void setKualiConfigurationService(KualiConfigurationService kualiConfigurationService) {
441 this.kualiConfigurationService = kualiConfigurationService;
442 }
443
444 /**
445 * Gets the parameterService attribute.
446 *
447 * @return Returns the parameterService.
448 */
449 public ParameterService getParameterService() {
450 return parameterService;
451 }
452
453 /**
454 * Sets the parameterService attribute value.
455 *
456 * @param parameterService The parameterService to set.
457 */
458 public void setParameterService(ParameterService parameterService) {
459 this.parameterService = parameterService;
460 }
461
462 /**
463 * Gets the dateTimeService attribute.
464 *
465 * @return Returns the dateTimeService.
466 */
467 public DateTimeService getDateTimeService() {
468 return dateTimeService;
469 }
470
471 /**
472 * Sets the dateTimeService attribute value.
473 *
474 * @param dateTimeService The dateTimeService to set.
475 */
476 public void setDateTimeService(DateTimeService dateTimeService) {
477 this.dateTimeService = dateTimeService;
478 }
479
480 /**
481 * Gets the optionsService attribute.
482 *
483 * @return Returns the optionsService.
484 */
485 public OptionsService getOptionsService() {
486 return optionsService;
487 }
488
489 /**
490 * Sets the optionsService attribute value.
491 *
492 * @param optionsService The optionsService to set.
493 */
494 public void setOptionsService(OptionsService optionsService) {
495 this.optionsService = optionsService;
496 }
497
498 /**
499 * Gets the businessObjectService attribute.
500 *
501 * @return Returns the businessObjectService.
502 */
503 public BusinessObjectService getBusinessObjectService() {
504 return businessObjectService;
505 }
506
507 /**
508 * Sets the businessObjectService attribute value.
509 *
510 * @param businessObjectService The businessObjectService to set.
511 */
512 public void setBusinessObjectService(BusinessObjectService businessObjectService) {
513 this.businessObjectService = businessObjectService;
514 }
515
516 /**
517 * Gets the universityDateDao attribute.
518 *
519 * @return Returns the universityDateDao.
520 */
521 public UniversityDateDao getUniversityDateDao() {
522 return universityDateDao;
523 }
524
525 /**
526 * Sets the universityDateDao attribute value.
527 *
528 * @param universityDateDao The universityDateDao to set.
529 */
530 public void setUniversityDateDao(UniversityDateDao universityDateDao) {
531 this.universityDateDao = universityDateDao;
532 }
533 }