You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@fineract.apache.org by ar...@apache.org on 2023/02/03 11:33:00 UTC
[fineract] branch develop updated: FINERACT-1855-Arrears-aging-job-as-business-step
This is an automated email from the ASF dual-hosted git repository.
arnold pushed a commit to branch develop
in repository https://gitbox.apache.org/repos/asf/fineract.git
The following commit(s) were added to refs/heads/develop by this push:
new 38b46f412 FINERACT-1855-Arrears-aging-job-as-business-step
38b46f412 is described below
commit 38b46f4125c0c2c5955d159e1289e31280a85fa4
Author: Ruchi Dhamankar <ru...@gmail.com>
AuthorDate: Wed Jan 25 18:42:28 2023 +0530
FINERACT-1855-Arrears-aging-job-as-business-step
---
.../loan/UpdateLoanArrearsAgingBusinessStep.java | 49 ++++
.../LoanArrearsAgeingUpdateHandler.java | 273 +++++++++++++++++++++
.../UpdateLoanArrearsAgeingConfig.java | 12 +-
.../UpdateLoanArrearsAgeingTasklet.java | 94 +------
.../db/changelog/tenant/changelog-tenant.xml | 1 +
...add_update_loan_arrears_aging_business_step.xml | 32 +++
...oanAccountArrearsAgeingCOBBusinessStepTest.java | 170 +++++++++++++
7 files changed, 530 insertions(+), 101 deletions(-)
diff --git a/fineract-provider/src/main/java/org/apache/fineract/cob/loan/UpdateLoanArrearsAgingBusinessStep.java b/fineract-provider/src/main/java/org/apache/fineract/cob/loan/UpdateLoanArrearsAgingBusinessStep.java
new file mode 100644
index 000000000..47b05c396
--- /dev/null
+++ b/fineract-provider/src/main/java/org/apache/fineract/cob/loan/UpdateLoanArrearsAgingBusinessStep.java
@@ -0,0 +1,49 @@
+/**
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements. See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership. The ASF licenses this file
+ * to you under the Apache License, Version 2.0 (the
+ * "License"); you may not use this file except in compliance
+ * with the License. You may obtain a copy of the License at
+ *
+ * http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing,
+ * software distributed under the License is distributed on an
+ * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+ * KIND, either express or implied. See the License for the
+ * specific language governing permissions and limitations
+ * under the License.
+ */
+package org.apache.fineract.cob.loan;
+
+import java.util.Arrays;
+import lombok.RequiredArgsConstructor;
+import org.apache.fineract.portfolio.loanaccount.domain.Loan;
+import org.apache.fineract.portfolio.loanaccount.jobs.updateloanarrearsageing.LoanArrearsAgeingUpdateHandler;
+import org.springframework.stereotype.Component;
+
+@Component
+@RequiredArgsConstructor
+public class UpdateLoanArrearsAgingBusinessStep implements LoanCOBBusinessStep {
+
+ private final LoanArrearsAgeingUpdateHandler loanArrearsAgeingUpdateHandler;
+
+ @Override
+ public Loan execute(Loan loan) {
+ Long loanId = loan.getId();
+ loanArrearsAgeingUpdateHandler.updateLoanArrearsAgeingDetails(Arrays.asList(loanId));
+ return loan;
+ }
+
+ @Override
+ public String getEnumStyledName() {
+ return "UPDATE_LOAN_ARREARS_AGING";
+ }
+
+ @Override
+ public String getHumanReadableName() {
+ return "Update loan arrears aging";
+ }
+}
diff --git a/fineract-provider/src/main/java/org/apache/fineract/portfolio/loanaccount/jobs/updateloanarrearsageing/LoanArrearsAgeingUpdateHandler.java b/fineract-provider/src/main/java/org/apache/fineract/portfolio/loanaccount/jobs/updateloanarrearsageing/LoanArrearsAgeingUpdateHandler.java
new file mode 100644
index 000000000..aac6e0df9
--- /dev/null
+++ b/fineract-provider/src/main/java/org/apache/fineract/portfolio/loanaccount/jobs/updateloanarrearsageing/LoanArrearsAgeingUpdateHandler.java
@@ -0,0 +1,273 @@
+/**
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements. See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership. The ASF licenses this file
+ * to you under the Apache License, Version 2.0 (the
+ * "License"); you may not use this file except in compliance
+ * with the License. You may obtain a copy of the License at
+ *
+ * http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing,
+ * software distributed under the License is distributed on an
+ * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+ * KIND, either express or implied. See the License for the
+ * specific language governing permissions and limitations
+ * under the License.
+ */
+package org.apache.fineract.portfolio.loanaccount.jobs.updateloanarrearsageing;
+
+import java.math.BigDecimal;
+import java.sql.ResultSet;
+import java.sql.SQLException;
+import java.sql.Types;
+import java.time.LocalDate;
+import java.util.ArrayList;
+import java.util.HashMap;
+import java.util.List;
+import java.util.Map;
+import lombok.RequiredArgsConstructor;
+import lombok.extern.slf4j.Slf4j;
+import org.apache.fineract.infrastructure.core.domain.JdbcSupport;
+import org.apache.fineract.infrastructure.core.service.database.DatabaseSpecificSQLGenerator;
+import org.apache.fineract.portfolio.loanaccount.loanschedule.data.LoanSchedulePeriodData;
+import org.apache.fineract.portfolio.loanaccount.service.LoanArrearsAgingService;
+import org.springframework.dao.DataAccessException;
+import org.springframework.jdbc.core.JdbcTemplate;
+import org.springframework.jdbc.core.ResultSetExtractor;
+import org.springframework.jdbc.core.namedparam.MapSqlParameterSource;
+import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
+import org.springframework.jdbc.core.namedparam.SqlParameterSource;
+import org.springframework.stereotype.Component;
+
+@Slf4j
+@Component
+@RequiredArgsConstructor
+public class LoanArrearsAgeingUpdateHandler {
+
+ private final JdbcTemplate jdbcTemplate;
+ private final DatabaseSpecificSQLGenerator sqlGenerator;
+ private final LoanArrearsAgingService loanArrearsAgingService;
+
+ private void truncateLoanArrearsAgingDetails() {
+ jdbcTemplate.execute("truncate table m_loan_arrears_aging");
+ }
+
+ private void deleteLoanArrearsAgingDetails(List<Long> loanIds) {
+ // delete existing record for loan from m_loan_arrears_aging table
+ for (Long loanId : loanIds) {
+ jdbcTemplate.update("delete from m_loan_arrears_aging where loan_id=?", loanId);
+ }
+ }
+
+ public void updateLoanArrearsAgeingDetailsForAllLoans() {
+ truncateLoanArrearsAgingDetails();
+ String insertSQLStatement = buildQueryForInsertAgeingDetails(Boolean.TRUE);
+ List<String> insertStatements = updateLoanArrearsAgeingDetailsWithOriginalScheduleForAllLoans();
+ insertStatements.add(0, insertSQLStatement);
+ final int[] records = this.jdbcTemplate.batchUpdate(insertStatements.toArray(new String[0]));
+ if (log.isDebugEnabled()) {
+ int result = 0;
+ for (int record : records) {
+ result += record;
+ }
+ log.debug("Records affected by updateLoanArrearsAgeingDetails: {}", result);
+ }
+ }
+
+ public void updateLoanArrearsAgeingDetails(List<Long> loanIdsForUpdate) {
+
+ deleteLoanArrearsAgingDetails(loanIdsForUpdate);
+ String insertSQLStatement = buildQueryForInsertAgeingDetails(Boolean.FALSE);
+ List<Object[]> batch = new ArrayList<Object[]>();
+ if (!loanIdsForUpdate.isEmpty()) {
+ for (Long loanId : loanIdsForUpdate) {
+ Object[] values = new Object[] { loanId };
+ batch.add(values);
+ }
+ }
+ final int[] recordsUpdatedWithoutOriginalSchedule = this.jdbcTemplate.batchUpdate(insertSQLStatement, batch);
+ int[] recordsUpdatedWithOriginalSchedule = new int[0];
+ List<String> insertStatements = updateLoanArrearsAgeingDetailsWithOriginalSchedule(loanIdsForUpdate);
+ if (!insertStatements.isEmpty()) {
+ recordsUpdatedWithOriginalSchedule = this.jdbcTemplate.batchUpdate(insertStatements.toArray(new String[0]));
+
+ }
+ if (log.isDebugEnabled()) {
+ int result = 0;
+ for (int recordWithoutOriginalSchedule : recordsUpdatedWithoutOriginalSchedule) {
+ result += recordWithoutOriginalSchedule;
+ }
+ if (recordsUpdatedWithOriginalSchedule.length > 0) {
+ for (int recordWithOriginalSchedule : recordsUpdatedWithOriginalSchedule) {
+ result += recordWithOriginalSchedule;
+ }
+ }
+ log.debug("Records affected by updateLoanArrearsAgeingDetails: {}", result);
+ }
+
+ }
+
+ private String buildQueryForInsertAgeingDetails(boolean isForAllLoans) {
+ final StringBuilder insertSqlStatementBuilder = new StringBuilder(900);
+ final String principalOverdueCalculationSql = "SUM(COALESCE(mr.principal_amount, 0) - coalesce(mr.principal_completed_derived, 0) - coalesce(mr.principal_writtenoff_derived, 0))";
+ final String interestOverdueCalculationSql = "SUM(COALESCE(mr.interest_amount, 0) - coalesce(mr.interest_writtenoff_derived, 0) - coalesce(mr.interest_waived_derived, 0) - "
+ + "coalesce(mr.interest_completed_derived, 0))";
+ final String feeChargesOverdueCalculationSql = "SUM(COALESCE(mr.fee_charges_amount, 0) - coalesce(mr.fee_charges_writtenoff_derived, 0) - "
+ + "coalesce(mr.fee_charges_waived_derived, 0) - coalesce(mr.fee_charges_completed_derived, 0))";
+ final String penaltyChargesOverdueCalculationSql = "SUM(COALESCE(mr.penalty_charges_amount, 0) - coalesce(mr.penalty_charges_writtenoff_derived, 0) - "
+ + "coalesce(mr.penalty_charges_waived_derived, 0) - coalesce(mr.penalty_charges_completed_derived, 0))";
+
+ insertSqlStatementBuilder.append(
+ "INSERT INTO m_loan_arrears_aging(loan_id,principal_overdue_derived,interest_overdue_derived,fee_charges_overdue_derived,penalty_charges_overdue_derived,total_overdue_derived,overdue_since_date_derived)");
+ insertSqlStatementBuilder.append("select ml.id as loanId,");
+ insertSqlStatementBuilder.append(principalOverdueCalculationSql + " as principal_overdue_derived,");
+ insertSqlStatementBuilder.append(interestOverdueCalculationSql + " as interest_overdue_derived,");
+ insertSqlStatementBuilder.append(feeChargesOverdueCalculationSql + " as fee_charges_overdue_derived,");
+ insertSqlStatementBuilder.append(penaltyChargesOverdueCalculationSql + " as penalty_charges_overdue_derived,");
+ insertSqlStatementBuilder.append(principalOverdueCalculationSql + "+" + interestOverdueCalculationSql + "+");
+ insertSqlStatementBuilder
+ .append(feeChargesOverdueCalculationSql + "+" + penaltyChargesOverdueCalculationSql + " as total_overdue_derived,");
+ insertSqlStatementBuilder.append("MIN(mr.duedate) as overdue_since_date_derived ");
+ insertSqlStatementBuilder.append(" FROM m_loan ml ");
+ insertSqlStatementBuilder.append(" INNER JOIN m_loan_repayment_schedule mr on mr.loan_id = ml.id ");
+ insertSqlStatementBuilder.append(" left join m_product_loan_recalculation_details prd on prd.product_id = ml.product_id ");
+ insertSqlStatementBuilder.append(" WHERE ml.loan_status_id = 300 ");// active
+ if (!isForAllLoans) {
+ insertSqlStatementBuilder.append(" and ml.id IN (?)");
+ }
+ insertSqlStatementBuilder.append(" and mr.completed_derived is false ");
+ insertSqlStatementBuilder.append(" and mr.duedate < ")
+ .append(sqlGenerator.subDate(sqlGenerator.currentBusinessDate(), "COALESCE(ml.grace_on_arrears_ageing, 0)", "day"))
+ .append(" ");
+ insertSqlStatementBuilder
+ .append(" and (prd.arrears_based_on_original_schedule = false or prd.arrears_based_on_original_schedule is null) ");
+ insertSqlStatementBuilder.append(" GROUP BY ml.id");
+ return insertSqlStatementBuilder.toString();
+ }
+
+ private List<String> updateLoanArrearsAgeingDetailsWithOriginalSchedule(List<Long> loanIdsForUpdate) {
+ List<String> insertStatement = new ArrayList<>();
+ String sqlForLoanIdentifiers = buildQueryForLoanIdentifiersWithOriginalSchedule(Boolean.FALSE);
+ List<Object> loanIdsForQuery = new ArrayList<>();
+ for (Long loanId : loanIdsForUpdate) {
+ loanIdsForQuery.add(loanId);
+ }
+ List<Long> loanIds = this.jdbcTemplate.queryForList(sqlForLoanIdentifiers, loanIdsForQuery.toArray(), new int[] { Types.BIGINT },
+ Long.class);
+ if (!loanIds.isEmpty()) {
+ Map<Long, List<LoanSchedulePeriodData>> scheduleDate = getScheduleDate(loanIds);
+ List<Map<String, Object>> loanSummary = getLoanSummary(loanIds);
+ loanArrearsAgingService.updateScheduleWithPaidDetail(scheduleDate, loanSummary);
+ loanArrearsAgingService.createInsertStatements(insertStatement, scheduleDate, true);
+ }
+
+ return insertStatement;
+ }
+
+ private List<String> updateLoanArrearsAgeingDetailsWithOriginalScheduleForAllLoans() {
+ List<String> insertStatement = new ArrayList<>();
+ String sqlForLoanIdentifiers = buildQueryForLoanIdentifiersWithOriginalSchedule(Boolean.TRUE);
+ List<Long> loanIds = this.jdbcTemplate.queryForList(sqlForLoanIdentifiers, Long.class);
+ if (!loanIds.isEmpty()) {
+ Map<Long, List<LoanSchedulePeriodData>> scheduleDate = getScheduleDate(loanIds);
+ List<Map<String, Object>> loanSummary = getLoanSummary(loanIds);
+ loanArrearsAgingService.updateScheduleWithPaidDetail(scheduleDate, loanSummary);
+ loanArrearsAgingService.createInsertStatements(insertStatement, scheduleDate, true);
+ }
+
+ return insertStatement;
+ }
+
+ private String buildQueryForLoanIdentifiersWithOriginalSchedule(boolean isForAllLoans) {
+ final StringBuilder loanIdentifier = new StringBuilder();
+ loanIdentifier.append("select ml.id as loanId FROM m_loan ml ");
+ loanIdentifier.append("INNER JOIN m_loan_repayment_schedule mr on mr.loan_id = ml.id ");
+ loanIdentifier.append(
+ "inner join m_product_loan_recalculation_details prd on prd.product_id = ml.product_id and prd.arrears_based_on_original_schedule = true ");
+ loanIdentifier.append("WHERE ml.loan_status_id = 300 ");
+ if (!isForAllLoans) {
+ loanIdentifier.append(" and ml.id IN (?)");
+ }
+ loanIdentifier.append(" and mr.completed_derived is false and mr.duedate < ")
+ .append(sqlGenerator.subDate(sqlGenerator.currentBusinessDate(), "COALESCE(ml.grace_on_arrears_ageing, 0)", "day"))
+ .append(" group by ml.id");
+ return loanIdentifier.toString();
+ }
+
+ private List<Map<String, Object>> getLoanSummary(final List<Long> loanIds) {
+ final StringBuilder transactionsSql = new StringBuilder();
+ transactionsSql.append("select ml.id as loanId, ");
+ transactionsSql
+ .append("ml.principal_repaid_derived as principalAmtPaid, ml.principal_writtenoff_derived as principalAmtWrittenoff, ");
+ transactionsSql.append(" ml.interest_repaid_derived as interestAmtPaid, ml.interest_waived_derived as interestAmtWaived, ");
+ transactionsSql.append("ml.fee_charges_repaid_derived as feeAmtPaid, ml.fee_charges_waived_derived as feeAmtWaived, ");
+ transactionsSql
+ .append("ml.penalty_charges_repaid_derived as penaltyAmtPaid, ml.penalty_charges_waived_derived as penaltyAmtWaived ");
+ transactionsSql.append("from m_loan ml ");
+ transactionsSql.append("where ml.id IN (:loanIds)").append(" order by ml.id");
+
+ final NamedParameterJdbcTemplate namedJdbcTemplate = new NamedParameterJdbcTemplate(jdbcTemplate);
+ SqlParameterSource parameters = new MapSqlParameterSource("loanIds", loanIds);
+ List<Map<String, Object>> loanSummary = namedJdbcTemplate.queryForList(transactionsSql.toString(), parameters);
+ return loanSummary;
+ }
+
+ private Map<Long, List<LoanSchedulePeriodData>> getScheduleDate(List<Long> loanIds) {
+ LoanOriginalScheduleExtractor loanOriginalScheduleExtractor = new LoanOriginalScheduleExtractor(sqlGenerator);
+ final NamedParameterJdbcTemplate namedJdbcTemplate = new NamedParameterJdbcTemplate(jdbcTemplate);
+ SqlParameterSource parameters = new MapSqlParameterSource("loanIds", loanIds);
+ return namedJdbcTemplate.query(loanOriginalScheduleExtractor.schema, parameters, loanOriginalScheduleExtractor);
+ }
+
+ private static final class LoanOriginalScheduleExtractor implements ResultSetExtractor<Map<Long, List<LoanSchedulePeriodData>>> {
+
+ private final String schema;
+
+ LoanOriginalScheduleExtractor(DatabaseSpecificSQLGenerator sqlGenerator) {
+ final StringBuilder scheduleDetail = new StringBuilder();
+ scheduleDetail.append("select ml.id as loanId, mr.duedate as dueDate, mr.principal_amount as principalAmount, ");
+ scheduleDetail.append(
+ "mr.interest_amount as interestAmount, mr.fee_charges_amount as feeAmount, mr.penalty_charges_amount as penaltyAmount ");
+ scheduleDetail.append("from m_loan ml INNER JOIN m_loan_repayment_schedule_history mr on mr.loan_id = ml.id ");
+ scheduleDetail.append("where mr.duedate < "
+ + sqlGenerator.subDate(sqlGenerator.currentBusinessDate(), "COALESCE(ml.grace_on_arrears_ageing, 0)", "day") + " and ");
+ scheduleDetail.append("ml.id IN(:loanIds)").append(" and mr.version = (");
+ scheduleDetail.append("select max(lrs.version) from m_loan_repayment_schedule_history lrs where mr.loan_id = lrs.loan_id");
+ scheduleDetail.append(") order by ml.id,mr.duedate");
+ this.schema = scheduleDetail.toString();
+ }
+
+ @Override
+ public Map<Long, List<LoanSchedulePeriodData>> extractData(ResultSet rs) throws SQLException, DataAccessException {
+ Map<Long, List<LoanSchedulePeriodData>> scheduleDate = new HashMap<>();
+
+ while (rs.next()) {
+ Long loanId = rs.getLong("loanId");
+ List<LoanSchedulePeriodData> periodDatas = scheduleDate.computeIfAbsent(loanId, k -> new ArrayList<>());
+ periodDatas.add(fetchLoanSchedulePeriodData(rs));
+ }
+
+ return scheduleDate;
+ }
+
+ private LoanSchedulePeriodData fetchLoanSchedulePeriodData(ResultSet rs) throws SQLException {
+ final LocalDate dueDate = JdbcSupport.getLocalDate(rs, "dueDate");
+ final BigDecimal principalDue = JdbcSupport.getBigDecimalDefaultToZeroIfNull(rs, "principalAmount");
+ final BigDecimal interestDueOnPrincipalOutstanding = JdbcSupport.getBigDecimalDefaultToZeroIfNull(rs, "interestAmount");
+ final BigDecimal totalInstallmentAmount = principalDue.add(interestDueOnPrincipalOutstanding);
+ final BigDecimal feeChargesDueForPeriod = JdbcSupport.getBigDecimalDefaultToZeroIfNull(rs, "feeAmount");
+ final BigDecimal penaltyChargesDueForPeriod = JdbcSupport.getBigDecimalDefaultToZeroIfNull(rs, "penaltyAmount");
+ final Integer periodNumber = null;
+ final LocalDate fromDate = null;
+ final BigDecimal principalOutstanding = null;
+ final BigDecimal totalDueForPeriod = null;
+ return LoanSchedulePeriodData.repaymentOnlyPeriod(periodNumber, fromDate, dueDate, principalDue, principalOutstanding,
+ interestDueOnPrincipalOutstanding, feeChargesDueForPeriod, penaltyChargesDueForPeriod, totalDueForPeriod,
+ totalInstallmentAmount);
+
+ }
+ }
+
+}
diff --git a/fineract-provider/src/main/java/org/apache/fineract/portfolio/loanaccount/jobs/updateloanarrearsageing/UpdateLoanArrearsAgeingConfig.java b/fineract-provider/src/main/java/org/apache/fineract/portfolio/loanaccount/jobs/updateloanarrearsageing/UpdateLoanArrearsAgeingConfig.java
index 0041f97d3..949227d2e 100644
--- a/fineract-provider/src/main/java/org/apache/fineract/portfolio/loanaccount/jobs/updateloanarrearsageing/UpdateLoanArrearsAgeingConfig.java
+++ b/fineract-provider/src/main/java/org/apache/fineract/portfolio/loanaccount/jobs/updateloanarrearsageing/UpdateLoanArrearsAgeingConfig.java
@@ -18,9 +18,7 @@
*/
package org.apache.fineract.portfolio.loanaccount.jobs.updateloanarrearsageing;
-import org.apache.fineract.infrastructure.core.service.database.DatabaseSpecificSQLGenerator;
import org.apache.fineract.infrastructure.jobs.service.JobName;
-import org.apache.fineract.portfolio.loanaccount.service.LoanArrearsAgingService;
import org.springframework.batch.core.Job;
import org.springframework.batch.core.Step;
import org.springframework.batch.core.configuration.annotation.JobBuilderFactory;
@@ -29,7 +27,6 @@ import org.springframework.batch.core.launch.support.RunIdIncrementer;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
-import org.springframework.jdbc.core.JdbcTemplate;
@Configuration
public class UpdateLoanArrearsAgeingConfig {
@@ -39,12 +36,9 @@ public class UpdateLoanArrearsAgeingConfig {
@Autowired
private StepBuilderFactory steps;
+
@Autowired
- private JdbcTemplate jdbcTemplate;
- @Autowired
- private DatabaseSpecificSQLGenerator sqlGenerator;
- @Autowired
- private LoanArrearsAgingService loanArrearsAgingService;
+ private LoanArrearsAgeingUpdateHandler updateLoanArrearsAgingService;
@Bean
protected Step updateLoanArrearsAgeingStep() {
@@ -59,6 +53,6 @@ public class UpdateLoanArrearsAgeingConfig {
@Bean
public UpdateLoanArrearsAgeingTasklet updateLoanArrearsAgeingTasklet() {
- return new UpdateLoanArrearsAgeingTasklet(jdbcTemplate, sqlGenerator, loanArrearsAgingService);
+ return new UpdateLoanArrearsAgeingTasklet(updateLoanArrearsAgingService);
}
}
diff --git a/fineract-provider/src/main/java/org/apache/fineract/portfolio/loanaccount/jobs/updateloanarrearsageing/UpdateLoanArrearsAgeingTasklet.java b/fineract-provider/src/main/java/org/apache/fineract/portfolio/loanaccount/jobs/updateloanarrearsageing/UpdateLoanArrearsAgeingTasklet.java
index b48435111..ce8fb3d88 100644
--- a/fineract-provider/src/main/java/org/apache/fineract/portfolio/loanaccount/jobs/updateloanarrearsageing/UpdateLoanArrearsAgeingTasklet.java
+++ b/fineract-provider/src/main/java/org/apache/fineract/portfolio/loanaccount/jobs/updateloanarrearsageing/UpdateLoanArrearsAgeingTasklet.java
@@ -18,113 +18,23 @@
*/
package org.apache.fineract.portfolio.loanaccount.jobs.updateloanarrearsageing;
-import java.util.ArrayList;
-import java.util.List;
-import java.util.Map;
import lombok.RequiredArgsConstructor;
import lombok.extern.slf4j.Slf4j;
-import org.apache.fineract.infrastructure.core.service.ThreadLocalContextUtil;
-import org.apache.fineract.infrastructure.core.service.database.DatabaseSpecificSQLGenerator;
-import org.apache.fineract.portfolio.loanaccount.loanschedule.data.LoanSchedulePeriodData;
-import org.apache.fineract.portfolio.loanaccount.service.LoanArrearsAgingService;
import org.springframework.batch.core.StepContribution;
import org.springframework.batch.core.scope.context.ChunkContext;
import org.springframework.batch.core.step.tasklet.Tasklet;
import org.springframework.batch.repeat.RepeatStatus;
-import org.springframework.jdbc.core.JdbcTemplate;
@Slf4j
@RequiredArgsConstructor
public class UpdateLoanArrearsAgeingTasklet implements Tasklet {
- private final JdbcTemplate jdbcTemplate;
- private final DatabaseSpecificSQLGenerator sqlGenerator;
- private final LoanArrearsAgingService loanArrearsAgingService;
+ private final LoanArrearsAgeingUpdateHandler loanArrearsAgeingUpdateHandler;
@Override
public RepeatStatus execute(StepContribution contribution, ChunkContext chunkContext) throws Exception {
- jdbcTemplate.execute("truncate table m_loan_arrears_aging");
-
- final StringBuilder updateSqlBuilder = new StringBuilder(900);
- final String principalOverdueCalculationSql = "SUM(COALESCE(mr.principal_amount, 0) - coalesce(mr.principal_completed_derived, 0) - coalesce(mr.principal_writtenoff_derived, 0))";
- final String interestOverdueCalculationSql = "SUM(COALESCE(mr.interest_amount, 0) - coalesce(mr.interest_writtenoff_derived, 0) - coalesce(mr.interest_waived_derived, 0) - "
- + "coalesce(mr.interest_completed_derived, 0))";
- final String feeChargesOverdueCalculationSql = "SUM(COALESCE(mr.fee_charges_amount, 0) - coalesce(mr.fee_charges_writtenoff_derived, 0) - "
- + "coalesce(mr.fee_charges_waived_derived, 0) - coalesce(mr.fee_charges_completed_derived, 0))";
- final String penaltyChargesOverdueCalculationSql = "SUM(COALESCE(mr.penalty_charges_amount, 0) - coalesce(mr.penalty_charges_writtenoff_derived, 0) - "
- + "coalesce(mr.penalty_charges_waived_derived, 0) - coalesce(mr.penalty_charges_completed_derived, 0))";
-
- updateSqlBuilder.append(
- "INSERT INTO m_loan_arrears_aging(loan_id,principal_overdue_derived,interest_overdue_derived,fee_charges_overdue_derived,penalty_charges_overdue_derived,total_overdue_derived,overdue_since_date_derived)");
- updateSqlBuilder.append("select ml.id as loanId,");
- updateSqlBuilder.append(principalOverdueCalculationSql + " as principal_overdue_derived,");
- updateSqlBuilder.append(interestOverdueCalculationSql + " as interest_overdue_derived,");
- updateSqlBuilder.append(feeChargesOverdueCalculationSql + " as fee_charges_overdue_derived,");
- updateSqlBuilder.append(penaltyChargesOverdueCalculationSql + " as penalty_charges_overdue_derived,");
- updateSqlBuilder.append(principalOverdueCalculationSql + "+" + interestOverdueCalculationSql + "+");
- updateSqlBuilder.append(feeChargesOverdueCalculationSql + "+" + penaltyChargesOverdueCalculationSql + " as total_overdue_derived,");
- updateSqlBuilder.append("MIN(mr.duedate) as overdue_since_date_derived ");
- updateSqlBuilder.append(" FROM m_loan ml ");
- updateSqlBuilder.append(" INNER JOIN m_loan_repayment_schedule mr on mr.loan_id = ml.id ");
- updateSqlBuilder.append(" left join m_product_loan_recalculation_details prd on prd.product_id = ml.product_id ");
- updateSqlBuilder.append(" WHERE ml.loan_status_id = 300 "); // active
- updateSqlBuilder.append(" and mr.completed_derived is false ");
- updateSqlBuilder.append(" and mr.duedate < ")
- .append(sqlGenerator.subDate(sqlGenerator.currentBusinessDate(), "COALESCE(ml.grace_on_arrears_ageing, 0)", "day"))
- .append(" ");
- updateSqlBuilder.append(" and (prd.arrears_based_on_original_schedule = false or prd.arrears_based_on_original_schedule is null) ");
- updateSqlBuilder.append(" GROUP BY ml.id");
-
- List<String> insertStatements = updateLoanArrearsAgeingDetailsWithOriginalSchedule();
- insertStatements.add(0, updateSqlBuilder.toString());
- final int[] results = this.jdbcTemplate.batchUpdate(insertStatements.toArray(new String[0]));
- int result = 0;
- for (int i : results) {
- result += i;
- }
-
- log.debug("{}: Records affected by updateLoanArrearsAgeingDetails: {}", ThreadLocalContextUtil.getTenant().getName(), result);
+ loanArrearsAgeingUpdateHandler.updateLoanArrearsAgeingDetailsForAllLoans();
return RepeatStatus.FINISHED;
}
- private List<String> updateLoanArrearsAgeingDetailsWithOriginalSchedule() {
- List<String> insertStatement = new ArrayList<>();
-
- final StringBuilder loanIdentifier = new StringBuilder();
- loanIdentifier.append("select ml.id as loanId FROM m_loan ml ");
- loanIdentifier.append("INNER JOIN m_loan_repayment_schedule mr on mr.loan_id = ml.id ");
- loanIdentifier.append(
- "inner join m_product_loan_recalculation_details prd on prd.product_id = ml.product_id and prd.arrears_based_on_original_schedule = true ");
- loanIdentifier.append("WHERE ml.loan_status_id = 300 and mr.completed_derived is false and mr.duedate < ")
- .append(sqlGenerator.subDate(sqlGenerator.currentBusinessDate(), "COALESCE(ml.grace_on_arrears_ageing, 0)", "day"))
- .append(" group by ml.id");
- List<Long> loanIds = this.jdbcTemplate.queryForList(loanIdentifier.toString(), Long.class);
- if (!loanIds.isEmpty()) {
- String loanIdsAsString = loanIds.toString();
- loanIdsAsString = loanIdsAsString.substring(1, loanIdsAsString.length() - 1);
- Map<Long, List<LoanSchedulePeriodData>> scheduleDate = loanArrearsAgingService.getScheduleDate(loanIdsAsString);
-
- List<Map<String, Object>> loanSummary = getLoanSummary(loanIdsAsString);
- loanArrearsAgingService.updateScheduleWithPaidDetail(scheduleDate, loanSummary);
- loanArrearsAgingService.createInsertStatements(insertStatement, scheduleDate, true);
- }
-
- return insertStatement;
- }
-
- private List<Map<String, Object>> getLoanSummary(final String loanIdsAsString) {
- final StringBuilder transactionsSql = new StringBuilder();
- transactionsSql.append("select ml.id as loanId, ");
- transactionsSql
- .append("ml.principal_repaid_derived as principalAmtPaid, ml.principal_writtenoff_derived as principalAmtWrittenoff, ");
- transactionsSql.append(" ml.interest_repaid_derived as interestAmtPaid, ml.interest_waived_derived as interestAmtWaived, ");
- transactionsSql.append("ml.fee_charges_repaid_derived as feeAmtPaid, ml.fee_charges_waived_derived as feeAmtWaived, ");
- transactionsSql
- .append("ml.penalty_charges_repaid_derived as penaltyAmtPaid, ml.penalty_charges_waived_derived as penaltyAmtWaived ");
- transactionsSql.append("from m_loan ml ");
- transactionsSql.append("where ml.id IN (").append(loanIdsAsString).append(") order by ml.id");
-
- List<Map<String, Object>> loanSummary = this.jdbcTemplate.queryForList(transactionsSql.toString());
- return loanSummary;
- }
}
diff --git a/fineract-provider/src/main/resources/db/changelog/tenant/changelog-tenant.xml b/fineract-provider/src/main/resources/db/changelog/tenant/changelog-tenant.xml
index 225537762..21ea1ef16 100644
--- a/fineract-provider/src/main/resources/db/changelog/tenant/changelog-tenant.xml
+++ b/fineract-provider/src/main/resources/db/changelog/tenant/changelog-tenant.xml
@@ -108,4 +108,5 @@
<include file="parts/0086_add_cob_business_date_to_loan_account_locks.xml" relativeToChangelogFile="true" />
<include file="parts/0087_update_dashboard_table_reports.xml" relativeToChangelogFile="true" />
<include file="parts/0088_drop_m_loan_transaction_version_column.xml" relativeToChangelogFile="true" />
+ <include file="parts/0089_add_update_loan_arrears_aging_business_step.xml" relativeToChangelogFile="true" />
</databaseChangeLog>
diff --git a/fineract-provider/src/main/resources/db/changelog/tenant/parts/0089_add_update_loan_arrears_aging_business_step.xml b/fineract-provider/src/main/resources/db/changelog/tenant/parts/0089_add_update_loan_arrears_aging_business_step.xml
new file mode 100644
index 000000000..3ca99f395
--- /dev/null
+++ b/fineract-provider/src/main/resources/db/changelog/tenant/parts/0089_add_update_loan_arrears_aging_business_step.xml
@@ -0,0 +1,32 @@
+<?xml version="1.0" encoding="UTF-8"?>
+<!--
+
+ Licensed to the Apache Software Foundation (ASF) under one
+ or more contributor license agreements. See the NOTICE file
+ distributed with this work for additional information
+ regarding copyright ownership. The ASF licenses this file
+ to you under the Apache License, Version 2.0 (the
+ "License"); you may not use this file except in compliance
+ with the License. You may obtain a copy of the License at
+
+ http://www.apache.org/licenses/LICENSE-2.0
+
+ Unless required by applicable law or agreed to in writing,
+ software distributed under the License is distributed on an
+ "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+ KIND, either express or implied. See the License for the
+ specific language governing permissions and limitations
+ under the License.
+
+-->
+<databaseChangeLog xmlns="http://www.liquibase.org/xml/ns/dbchangelog"
+ xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
+ xsi:schemaLocation="http://www.liquibase.org/xml/ns/dbchangelog http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-4.1.xsd">
+ <changeSet id="1" author="fineract">
+ <insert tableName="m_batch_business_steps">
+ <column name="job_name" value="LOAN_CLOSE_OF_BUSINESS"/>
+ <column name="step_name" value="UPDATE_LOAN_ARREARS_AGING"/>
+ <column name="step_order" value="5"/>
+ </insert>
+ </changeSet>
+</databaseChangeLog>
diff --git a/integration-tests/src/test/java/org/apache/fineract/integrationtests/LoanAccountArrearsAgeingCOBBusinessStepTest.java b/integration-tests/src/test/java/org/apache/fineract/integrationtests/LoanAccountArrearsAgeingCOBBusinessStepTest.java
new file mode 100644
index 000000000..f74310e27
--- /dev/null
+++ b/integration-tests/src/test/java/org/apache/fineract/integrationtests/LoanAccountArrearsAgeingCOBBusinessStepTest.java
@@ -0,0 +1,170 @@
+/**
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements. See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership. The ASF licenses this file
+ * to you under the Apache License, Version 2.0 (the
+ * "License"); you may not use this file except in compliance
+ * with the License. You may obtain a copy of the License at
+ *
+ * http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing,
+ * software distributed under the License is distributed on an
+ * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+ * KIND, either express or implied. See the License for the
+ * specific language governing permissions and limitations
+ * under the License.
+ */
+package org.apache.fineract.integrationtests;
+
+import static org.junit.jupiter.api.Assertions.assertEquals;
+import static org.junit.jupiter.api.Assertions.assertNotNull;
+import static org.junit.jupiter.api.Assertions.assertTrue;
+
+import io.restassured.builder.RequestSpecBuilder;
+import io.restassured.builder.ResponseSpecBuilder;
+import io.restassured.http.ContentType;
+import io.restassured.specification.RequestSpecification;
+import io.restassured.specification.ResponseSpecification;
+import java.time.LocalDate;
+import java.time.format.DateTimeFormatter;
+import java.time.format.DateTimeFormatterBuilder;
+import java.util.HashMap;
+import java.util.UUID;
+import org.apache.fineract.client.models.GetDelinquencyBucketsResponse;
+import org.apache.fineract.client.models.GetLoanProductsProductIdResponse;
+import org.apache.fineract.client.models.GetLoansLoanIdResponse;
+import org.apache.fineract.client.models.GetLoansLoanIdSummary;
+import org.apache.fineract.cob.data.JobBusinessStepConfigData;
+import org.apache.fineract.infrastructure.businessdate.domain.BusinessDateType;
+import org.apache.fineract.integrationtests.common.BusinessDateHelper;
+import org.apache.fineract.integrationtests.common.BusinessStepConfigurationHelper;
+import org.apache.fineract.integrationtests.common.ClientHelper;
+import org.apache.fineract.integrationtests.common.GlobalConfigurationHelper;
+import org.apache.fineract.integrationtests.common.SchedulerJobHelper;
+import org.apache.fineract.integrationtests.common.Utils;
+import org.apache.fineract.integrationtests.common.loans.LoanApplicationTestBuilder;
+import org.apache.fineract.integrationtests.common.loans.LoanProductTestBuilder;
+import org.apache.fineract.integrationtests.common.loans.LoanTransactionHelper;
+import org.apache.fineract.integrationtests.common.products.DelinquencyBucketsHelper;
+import org.junit.jupiter.api.BeforeEach;
+import org.junit.jupiter.api.Test;
+
+public class LoanAccountArrearsAgeingCOBBusinessStepTest {
+
+ private ResponseSpecification responseSpec;
+ private ResponseSpecification responseSpecErr400;
+ private ResponseSpecification responseSpecErr503;
+ private RequestSpecification requestSpec;
+ private ClientHelper clientHelper;
+ private LoanTransactionHelper loanTransactionHelper;
+ private DateTimeFormatter dateFormatter = new DateTimeFormatterBuilder().appendPattern("dd MMMM yyyy").toFormatter();
+ public static final String UPDATE_LOAN_ARREARS_AGING = "UPDATE_LOAN_ARREARS_AGING";
+
+ @BeforeEach
+ public void setup() {
+ Utils.initializeRESTAssured();
+ this.requestSpec = new RequestSpecBuilder().setContentType(ContentType.JSON).build();
+ this.requestSpec.header("Authorization", "Basic " + Utils.loginIntoServerAndGetBase64EncodedAuthenticationKey());
+ this.responseSpec = new ResponseSpecBuilder().expectStatusCode(200).build();
+ this.responseSpecErr400 = new ResponseSpecBuilder().expectStatusCode(400).build();
+ this.responseSpecErr503 = new ResponseSpecBuilder().expectStatusCode(503).build();
+ this.loanTransactionHelper = new LoanTransactionHelper(this.requestSpec, this.responseSpec);
+ this.clientHelper = new ClientHelper(this.requestSpec, this.responseSpec);
+ }
+
+ @Test
+ public void loanArrearsAgeingCOBBusinessStepTest() {
+ // Set Business Date
+ GlobalConfigurationHelper.updateIsBusinessDateEnabled(requestSpec, responseSpec, Boolean.TRUE);
+ LocalDate businessDate = Utils.getLocalDateOfTenant();
+ BusinessDateHelper.updateBusinessDate(requestSpec, responseSpec, BusinessDateType.BUSINESS_DATE, businessDate);
+
+ LocalDate operationDate = businessDate.minusDays(40);
+ String loanOperationDate = Utils.dateFormatter.format(operationDate);
+
+ // create Client
+ final Integer clientId = clientHelper.createClient(ClientHelper.defaultClientCreationRequest()).getClientId().intValue();
+
+ // create Loan Product
+
+ // Delinquency Bucket
+ final Integer delinquencyBucketId = DelinquencyBucketsHelper.createDelinquencyBucket(requestSpec, responseSpec);
+ final GetDelinquencyBucketsResponse delinquencyBucket = DelinquencyBucketsHelper.getDelinquencyBucket(requestSpec, responseSpec,
+ delinquencyBucketId);
+
+ final GetLoanProductsProductIdResponse getLoanProductsProductResponse = createLoanProduct(loanTransactionHelper,
+ delinquencyBucketId);
+ assertNotNull(getLoanProductsProductResponse);
+
+ // Loan1 ExternalId
+ String loan1ExternalIdStr = UUID.randomUUID().toString();
+
+ // create Loan Account for Client with Loan Product type 1
+ Long loanProductId = getLoanProductsProductResponse.getId();
+ final Integer loanId_1 = createLoanAccount(loanOperationDate, clientId, loanProductId, loan1ExternalIdStr);
+
+ String loan2ExternalIdStr = UUID.randomUUID().toString();
+ final Integer loanId_2 = createLoanAccount(loanOperationDate, clientId, loanProductId, loan2ExternalIdStr);
+
+ // Run Loan cob with verfying business step for Update Arrears ageing details
+ final SchedulerJobHelper schedulerJobHelper = new SchedulerJobHelper(requestSpec);
+
+ // COB Step Validation
+ final JobBusinessStepConfigData jobBusinessStepConfigData = BusinessStepConfigurationHelper
+ .getConfiguredBusinessStepsByJobName(requestSpec, responseSpec, BusinessConfigurationApiTest.LOAN_JOB_NAME);
+ assertNotNull(jobBusinessStepConfigData);
+ assertEquals(BusinessConfigurationApiTest.LOAN_JOB_NAME, jobBusinessStepConfigData.getJobName());
+ assertTrue(jobBusinessStepConfigData.getBusinessSteps().size() > 0);
+ assertTrue(jobBusinessStepConfigData.getBusinessSteps().stream()
+ .anyMatch(businessStep -> UPDATE_LOAN_ARREARS_AGING.equals(businessStep.getStepName())));
+
+ // Run the Loan COB Job
+ final String jobName = "Loan COB";
+ schedulerJobHelper.executeAndAwaitJob(jobName);
+
+ // verify Arrears details are updated for both the loans, by verifying loan summary fields for
+ // principalOverdue,totalOverdue,overdueSinceddate
+
+ // Retrieve Loan 1 with loanId
+ GetLoansLoanIdResponse loan1Details = loanTransactionHelper.getLoanDetails((long) loanId_1);
+ GetLoansLoanIdSummary loan1Summary = loan1Details.getSummary();
+ assertNotNull(loan1Summary);
+ assertNotNull(loan1Summary.getOverdueSinceDate());
+ assertEquals(loan1Summary.getPrincipalOverdue(), 1000.00);
+ assertEquals(loan1Summary.getTotalOverdue(), 1000.00);
+
+ // Retrieve Loan 2 with loanId
+ GetLoansLoanIdResponse loan2Details = loanTransactionHelper.getLoanDetails((long) loanId_2);
+ GetLoansLoanIdSummary loan2Summary = loan2Details.getSummary();
+ assertNotNull(loan2Summary);
+ assertNotNull(loan2Summary.getOverdueSinceDate());
+ assertEquals(loan2Summary.getPrincipalOverdue(), 1000.00);
+ assertEquals(loan2Summary.getTotalOverdue(), 1000.00);
+ GlobalConfigurationHelper.updateIsBusinessDateEnabled(requestSpec, responseSpec, Boolean.FALSE);
+ }
+
+ private GetLoanProductsProductIdResponse createLoanProduct(final LoanTransactionHelper loanTransactionHelper,
+ final Integer delinquencyBucketId) {
+ final HashMap<String, Object> loanProductMap = new LoanProductTestBuilder().build(null, delinquencyBucketId);
+ final Integer loanProductId = loanTransactionHelper.getLoanProductId(Utils.convertToJson(loanProductMap));
+ return loanTransactionHelper.getLoanProduct(loanProductId);
+ }
+
+ private Integer createLoanAccount(final String operationDate, final Integer clientID, final Long loanProductID,
+ final String externalId) {
+
+ String loanApplicationJSON = new LoanApplicationTestBuilder().withPrincipal("1000").withLoanTermFrequency("1")
+ .withLoanTermFrequencyAsMonths().withNumberOfRepayments("1").withRepaymentEveryAfter("1")
+ .withRepaymentFrequencyTypeAsMonths().withInterestRatePerPeriod("0").withInterestTypeAsFlatBalance()
+ .withAmortizationTypeAsEqualPrincipalPayments().withInterestCalculationPeriodTypeSameAsRepaymentPeriod()
+ .withExpectedDisbursementDate(operationDate).withSubmittedOnDate(operationDate).withLoanType("individual")
+ .withExternalId(externalId).build(clientID.toString(), loanProductID.toString(), null);
+
+ final Integer loanId = loanTransactionHelper.getLoanId(loanApplicationJSON);
+ loanTransactionHelper.approveLoan(operationDate, "1000", loanId, null);
+ loanTransactionHelper.disburseLoanWithNetDisbursalAmount(operationDate, loanId, "1000");
+ return loanId;
+ }
+}