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 }