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    }