You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@fineract.apache.org by al...@apache.org on 2022/02/21 17:31:26 UTC
[fineract] branch develop updated: FINERACT-984: More MySQL independence in native queries
This is an automated email from the ASF dual-hosted git repository.
aleks 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 16207ef FINERACT-984: More MySQL independence in native queries
16207ef is described below
commit 16207ef95259f0f1581eba2490eba3273a35e529
Author: Arnold Galovics <ga...@gmail.com>
AuthorDate: Mon Feb 21 08:59:13 2022 +0100
FINERACT-984: More MySQL independence in native queries
---
.../ClientChargeReadPlatformServiceImpl.java | 8 +-
...llateralManagementWritePlatformServiceImpl.java | 2 +-
.../CollectionSheetReadPlatformServiceImpl.java | 28 +++----
.../FloatingRatesReadPlatformServiceImpl.java | 22 ++---
.../service/CenterReadPlatformServiceImpl.java | 16 ++--
.../InterestRateChartReadPlatformServiceImpl.java | 8 +-
.../domain/LoanAccountDomainService.java | 2 +-
.../domain/LoanAccountDomainServiceJpa.java | 2 +-
.../domain/LoanCollateralManagement.java | 10 +--
.../service/GuarantorReadPlatformServiceImpl.java | 28 +++++--
.../GLIMAccountInfoReadPlatformServiceImpl.java | 4 +-
.../LoanAccrualWritePlatformServiceImpl.java | 2 +-
...ationWritePlatformServiceJpaRepositoryImpl.java | 4 +-
.../service/LoanArrearsAgingServiceImpl.java | 48 +++++------
.../service/LoanChargeReadPlatformServiceImpl.java | 26 +++---
.../service/LoanReadPlatformServiceImpl.java | 93 ++++++++++++----------
.../LoanWritePlatformServiceJpaRepositoryImpl.java | 6 +-
17 files changed, 163 insertions(+), 146 deletions(-)
diff --git a/fineract-provider/src/main/java/org/apache/fineract/portfolio/client/service/ClientChargeReadPlatformServiceImpl.java b/fineract-provider/src/main/java/org/apache/fineract/portfolio/client/service/ClientChargeReadPlatformServiceImpl.java
index 10148d3..16a772b 100644
--- a/fineract-provider/src/main/java/org/apache/fineract/portfolio/client/service/ClientChargeReadPlatformServiceImpl.java
+++ b/fineract-provider/src/main/java/org/apache/fineract/portfolio/client/service/ClientChargeReadPlatformServiceImpl.java
@@ -139,16 +139,16 @@ public class ClientChargeReadPlatformServiceImpl implements ClientChargeReadPlat
// filter for active charges
if (status.equalsIgnoreCase(ClientApiConstants.CLIENT_CHARGE_QUERY_PARAM_STATUS_VALUE_ACTIVE)) {
- sqlBuilder.append(" and cc.is_active = 1 ");
+ sqlBuilder.append(" and cc.is_active = true ");
} else if (status.equalsIgnoreCase(ClientApiConstants.CLIENT_CHARGE_QUERY_PARAM_STATUS_VALUE_INACTIVE)) {
- sqlBuilder.append(" and cc.is_active = 0 ");
+ sqlBuilder.append(" and cc.is_active = false ");
}
// filter for paid charges
if (pendingPayment != null && pendingPayment) {
- sqlBuilder.append(" and ( cc.is_paid_derived = 0 and cc.waived = 0) ");
+ sqlBuilder.append(" and ( cc.is_paid_derived = false and cc.waived = false) ");
} else if (pendingPayment != null && !pendingPayment) {
- sqlBuilder.append(" and (cc.is_paid_derived = 1 or cc.waived = 1) ");
+ sqlBuilder.append(" and (cc.is_paid_derived = true or cc.waived = true) ");
}
sqlBuilder.append(" order by cc.charge_time_enum ASC, cc.charge_due_date DESC, cc.is_penalty ASC ");
diff --git a/fineract-provider/src/main/java/org/apache/fineract/portfolio/collateralmanagement/service/ClientCollateralManagementWritePlatformServiceImpl.java b/fineract-provider/src/main/java/org/apache/fineract/portfolio/collateralmanagement/service/ClientCollateralManagementWritePlatformServiceImpl.java
index 477034d..8170bfa 100644
--- a/fineract-provider/src/main/java/org/apache/fineract/portfolio/collateralmanagement/service/ClientCollateralManagementWritePlatformServiceImpl.java
+++ b/fineract-provider/src/main/java/org/apache/fineract/portfolio/collateralmanagement/service/ClientCollateralManagementWritePlatformServiceImpl.java
@@ -169,7 +169,7 @@ public class ClientCollateralManagementWritePlatformServiceImpl implements Clien
if (clientCollateralManagement.getLoanCollateralManagementSet().size() > 0) {
for (LoanCollateralManagement loanCollateralManagement : clientCollateralManagement.getLoanCollateralManagementSet()) {
- if (loanCollateralManagement.getIsReleased() == 0) {
+ if (!loanCollateralManagement.isReleased()) {
throw new ClientCollateralCannotBeDeletedException(
ClientCollateralCannotBeDeletedException.ClientCollateralCannotBeDeletedReason.CLIENT_COLLATERAL_IS_ALREADY_ATTACHED,
clientCollateralId);
diff --git a/fineract-provider/src/main/java/org/apache/fineract/portfolio/collectionsheet/service/CollectionSheetReadPlatformServiceImpl.java b/fineract-provider/src/main/java/org/apache/fineract/portfolio/collectionsheet/service/CollectionSheetReadPlatformServiceImpl.java
index 50bef40..1784822 100644
--- a/fineract-provider/src/main/java/org/apache/fineract/portfolio/collectionsheet/service/CollectionSheetReadPlatformServiceImpl.java
+++ b/fineract-provider/src/main/java/org/apache/fineract/portfolio/collectionsheet/service/CollectionSheetReadPlatformServiceImpl.java
@@ -224,12 +224,12 @@ public class CollectionSheetReadPlatformServiceImpl implements CollectionSheetRe
.append("ln.account_no As accountId, ").append("ln.loan_status_id As accountStatusId, ")
.append("pl.short_name As productShortName, ").append("ln.product_id As productId, ")
.append("ln.currency_code as currencyCode, ln.currency_digits as currencyDigits, ln.currency_multiplesof as inMultiplesOf, rc.`name` as currencyName, rc.display_symbol as currencyDisplaySymbol, rc.internationalized_name_code as currencyNameCode, ")
- .append("if(ln.loan_status_id = 200 , ln.principal_amount , null) As disbursementAmount, ")
- .append("sum(ifnull(if(ln.loan_status_id = 300, ls.principal_amount, 0.0), 0.0) - ifnull(if(ln.loan_status_id = 300, ls.principal_completed_derived, 0.0), 0.0)) As principalDue, ")
+ .append("(CASE WHEN ln.loan_status_id = 200 THEN ln.principal_amount ELSE null END) As disbursementAmount, ")
+ .append("sum(COALESCE((CASE WHEN ln.loan_status_id = 300 THEN ls.principal_amount ELSE 0.0 END), 0.0) - COALESCE((CASE WHEN ln.loan_status_id = 300 THEN ls.principal_completed_derived ELSE 0.0 END), 0.0)) As principalDue, ")
.append("ln.principal_repaid_derived As principalPaid, ")
- .append("sum(ifnull(if(ln.loan_status_id = 300, ls.interest_amount, 0.0), 0.0) - ifnull(if(ln.loan_status_id = 300, ls.interest_completed_derived, 0.0), 0.0) - IFNULL(IF(ln.loan_status_id = 300, ls.interest_waived_derived, 0.0), 0.0)) As interestDue, ")
+ .append("sum(COALESCE((CASE WHEN ln.loan_status_id = 300 THEN ls.interest_amount ELSE 0.0 END), 0.0) - COALESCE((CASE WHEN ln.loan_status_id = 300 THEN ls.interest_completed_derived ELSE 0.0 END), 0.0) - COALESCE((CASE WHEN ln.loan_status_id = 300 THEN ls.interest_waived_derived ELSE 0.0 END), 0.0)) As interestDue, ")
.append("ln.interest_repaid_derived As interestPaid, ")
- .append("sum(ifnull(if(ln.loan_status_id = 300, ls.fee_charges_amount, 0.0), 0.0) - ifnull(if(ln.loan_status_id = 300, ls.fee_charges_completed_derived, 0.0), 0.0)) As feeDue, ")
+ .append("sum(COALESCE((CASE WHEN ln.loan_status_id = 300 THEN ls.fee_charges_amount ELSE 0.0 END), 0.0) - COALESCE((CASE WHEN ln.loan_status_id = 300 THEN ls.fee_charges_completed_derived ELSE 0.0 END), 0.0)) As feeDue, ")
.append("ln.fee_charges_repaid_derived As feePaid, ").append("ca.attendance_type_enum as attendanceTypeId ")
.append("FROM m_group gp ")
.append("LEFT JOIN m_office of ON of.id = gp.office_id AND of.hierarchy like :officeHierarchy ")
@@ -253,7 +253,7 @@ public class CollectionSheetReadPlatformServiceImpl implements CollectionSheetRe
sql.append("and (gp.status_enum = 300 or (gp.status_enum = 600 and gp.closedon_date >= :dueDate)) ")
.append("and (cl.status_enum = 300 or (cl.status_enum = 600 and cl.closedon_date >= :dueDate)) ")
.append("GROUP BY gp.id, cl.id, ln.id, ca.attendance_type_enum ORDER BY gp.id , cl.id , ln.id ").append(") loandata ")
- .append("LEFT JOIN m_loan_charge lc ON lc.loan_id = loandata.loanId AND lc.is_paid_derived = 0 AND lc.is_active = 1 ")
+ .append("LEFT JOIN m_loan_charge lc ON lc.loan_id = loandata.loanId AND lc.is_paid_derived = false AND lc.is_active = true ")
.append("AND ( lc.due_for_collection_as_of_date <= :dueDate OR lc.charge_time_enum = 1) ")
.append("GROUP BY loandata.groupId, loandata.clientId, loandata.loanId ")
.append(", loandata.principalDue, loandata.interestDue, loandata.feeDue, loandata.attendanceTypeId ")
@@ -499,9 +499,9 @@ public class CollectionSheetReadPlatformServiceImpl implements CollectionSheetRe
.append("sa.currency_code as currencyCode, ").append("sa.currency_digits as currencyDigits, ")
.append("sa.currency_multiplesof as inMultiplesOf, ").append("rc.`name` as currencyName, ")
.append("rc.display_symbol as currencyDisplaySymbol, ")
- .append("if(sa.deposit_type_enum=100,'Saving Deposit',if(sa.deposit_type_enum=300,'Recurring Deposit','Current Deposit')) as depositAccountType, ")
+ .append("(CASE WHEN sa.deposit_type_enum=100 THEN 'Saving Deposit' ELSE (CASE WHEN sa.deposit_type_enum=300 THEN 'Recurring Deposit' ELSE 'Current Deposit' END) END) as depositAccountType, ")
.append("rc.internationalized_name_code as currencyNameCode, ")
- .append("sum(ifnull(mss.deposit_amount,0) - ifnull(mss.deposit_amount_completed_derived,0)) as dueAmount ")
+ .append("SUM(COALESCE(mss.deposit_amount,0) - coalesce(mss.deposit_amount_completed_derived,0)) as dueAmount ")
.append("FROM m_group gp ")
.append("LEFT JOIN m_office of ON of.id = gp.office_id AND of.hierarchy like :officeHierarchy ")
@@ -723,15 +723,15 @@ public class CollectionSheetReadPlatformServiceImpl implements CollectionSheetRe
sb.append("ln.currency_code as currencyCode, ln.currency_digits as currencyDigits, ln.currency_multiplesof as inMultiplesOf, ");
sb.append(
"rc.`name` as currencyName, rc.display_symbol as currencyDisplaySymbol, rc.internationalized_name_code as currencyNameCode, ");
- sb.append("if(ln.loan_status_id = 200 , ln.principal_amount , null) As disbursementAmount, ");
+ sb.append("(CASE WHEN ln.loan_status_id = 200 THEN ln.principal_amount ELSE null END) As disbursementAmount, ");
sb.append(
- "sum(ifnull(if(ln.loan_status_id = 300, ls.principal_amount, 0.0), 0.0) - ifnull(if(ln.loan_status_id = 300, ls.principal_completed_derived, 0.0), 0.0)) As principalDue, ");
+ "sum(COALESCE((CASE WHEN ln.loan_status_id = 300 THEN ls.principal_amount ELSE 0.0 END), 0.0) - COALESCE((CASE WHEN ln.loan_status_id = 300 THEN ls.principal_completed_derived ELSE 0.0 END), 0.0)) As principalDue, ");
sb.append("ln.principal_repaid_derived As principalPaid, ");
sb.append(
- "sum(ifnull(if(ln.loan_status_id = 300, ls.interest_amount, 0.0), 0.0) - ifnull(if(ln.loan_status_id = 300, ls.interest_completed_derived, 0.0), 0.0)) As interestDue, ");
+ "sum(COALESCE((CASE WHEN ln.loan_status_id = 300 THEN ls.interest_amount ELSE 0.0 END), 0.0) - COALESCE((CASE WHEN ln.loan_status_id = 300 THEN ls.interest_completed_derived ELSE 0.0 END), 0.0)) As interestDue, ");
sb.append("ln.interest_repaid_derived As interestPaid, ");
sb.append(
- "sum(ifnull(if(ln.loan_status_id = 300, ls.fee_charges_amount, 0.0), 0.0) - ifnull(if(ln.loan_status_id = 300, ls.fee_charges_completed_derived, 0.0), 0.0)) As feeDue, ");
+ "sum(COALESCE((CASE WHEN ln.loan_status_id = 300 THEN ls.fee_charges_amount ELSE 0.0 END), 0.0) - COALESCE((CASE WHEN ln.loan_status_id = 300 THEN ls.fee_charges_completed_derived ELSE 0.0 END), 0.0)) As feeDue, ");
sb.append("ln.fee_charges_repaid_derived As feePaid ");
sb.append("FROM m_loan ln ");
sb.append("JOIN m_client cl ON cl.id = ln.client_id ");
@@ -749,7 +749,7 @@ public class CollectionSheetReadPlatformServiceImpl implements CollectionSheetRe
sb.append("(ln.loan_status_id = 300) ");
sb.append("and ln.group_id is null GROUP BY cl.id , ln.id ORDER BY cl.id , ln.id ) loandata ");
sb.append(
- "LEFT JOIN m_loan_charge lc ON lc.loan_id = loandata.loanId AND lc.is_paid_derived = 0 AND lc.is_active = 1 AND ( lc.due_for_collection_as_of_date <= :dueDate OR lc.charge_time_enum = 1) ");
+ "LEFT JOIN m_loan_charge lc ON lc.loan_id = loandata.loanId AND lc.is_paid_derived = false AND lc.is_active = true AND ( lc.due_for_collection_as_of_date <= :dueDate OR lc.charge_time_enum = 1) ");
sb.append("GROUP BY loandata.clientId, loandata.loanId ORDER BY loandata.clientId, loandata.loanId ");
sql = sb.toString();
@@ -808,13 +808,13 @@ public class CollectionSheetReadPlatformServiceImpl implements CollectionSheetRe
final StringBuilder sb = new StringBuilder(400);
sb.append(
- "SELECT if(sa.deposit_type_enum=100,'Saving Deposit',if(sa.deposit_type_enum=300,'Recurring Deposit','Current Deposit')) as depositAccountType, cl.display_name As clientName, cl.id As clientId, ");
+ "SELECT (CASE WHEN sa.deposit_type_enum=100 THEN 'Saving Deposit' ELSE (CASE WHEN sa.deposit_type_enum=300 THEN 'Recurring Deposit' ELSE 'Current Deposit' END) END) as depositAccountType, cl.display_name As clientName, cl.id As clientId, ");
sb.append("sa.id As savingsId, sa.account_no As accountId, sa.status_enum As accountStatusId, ");
sb.append("sp.short_name As productShortName, sp.id As productId, ");
sb.append("sa.currency_code as currencyCode, sa.currency_digits as currencyDigits, sa.currency_multiplesof as inMultiplesOf, ");
sb.append(
"rc.`name` as currencyName, rc.display_symbol as currencyDisplaySymbol, rc.internationalized_name_code as currencyNameCode, ");
- sb.append("sum(ifnull(mss.deposit_amount,0) - ifnull(mss.deposit_amount_completed_derived,0)) as dueAmount ");
+ sb.append("SUM(COALESCE(mss.deposit_amount,0) - coalesce(mss.deposit_amount_completed_derived,0)) as dueAmount ");
sb.append("FROM m_savings_account sa ");
sb.append("JOIN m_client cl ON cl.id = sa.client_id ");
sb.append("JOIN m_savings_product sp ON sa.product_id=sp.id ");
diff --git a/fineract-provider/src/main/java/org/apache/fineract/portfolio/floatingrates/service/FloatingRatesReadPlatformServiceImpl.java b/fineract-provider/src/main/java/org/apache/fineract/portfolio/floatingrates/service/FloatingRatesReadPlatformServiceImpl.java
index 9786af3..0d45a40 100644
--- a/fineract-provider/src/main/java/org/apache/fineract/portfolio/floatingrates/service/FloatingRatesReadPlatformServiceImpl.java
+++ b/fineract-provider/src/main/java/org/apache/fineract/portfolio/floatingrates/service/FloatingRatesReadPlatformServiceImpl.java
@@ -56,14 +56,14 @@ public class FloatingRatesReadPlatformServiceImpl implements FloatingRatesReadPl
@Override
public List<FloatingRateData> retrieveAllActive() {
FloatingRateRowMapper rateMapper = new FloatingRateRowMapper(false);
- final String sql = "select " + rateMapper.schema() + " where rate.is_active = 1 ";
+ final String sql = "select " + rateMapper.schema() + " where rate.is_active = true ";
return this.jdbcTemplate.query(sql, rateMapper);
}
@Override
public List<FloatingRateData> retrieveLookupActive() {
FloatingRateLookupMapper rateMapper = new FloatingRateLookupMapper();
- final String sql = "select " + rateMapper.schema() + " where rate.is_active = 1 ";
+ final String sql = "select " + rateMapper.schema() + " where rate.is_active = true ";
return this.jdbcTemplate.query(sql, rateMapper);
}
@@ -92,7 +92,7 @@ public class FloatingRatesReadPlatformServiceImpl implements FloatingRatesReadPl
public FloatingRateData retrieveBaseLendingRate() {
try {
FloatingRateRowMapper rateMapper = new FloatingRateRowMapper(true);
- final String sql = "select " + rateMapper.schema() + " where rate.is_base_lending_rate = 1 and rate.is_active = 1";
+ final String sql = "select " + rateMapper.schema() + " where rate.is_base_lending_rate = true and rate.is_active = true";
return this.jdbcTemplate.queryForObject(sql, rateMapper);
} catch (final EmptyResultDataAccessException e) {
throw new FloatingRateNotFoundException("error.msg.floatingrate.base.lending.rate.not.found", e);
@@ -127,8 +127,8 @@ public class FloatingRatesReadPlatformServiceImpl implements FloatingRatesReadPl
List<FloatingRatePeriodData> ratePeriods = null;
if (addRatePeriods) {
FloatingRatePeriodRowMapper ratePeriodMapper = new FloatingRatePeriodRowMapper();
- final String sql = "select " + ratePeriodMapper.schema() + " where period.is_active = 1 and period.floating_rates_id = ? "
- + " order by period.from_date desc ";
+ final String sql = "select " + ratePeriodMapper.schema()
+ + " where period.is_active = true and period.floating_rates_id = ? " + " order by period.from_date desc ";
ratePeriods = jdbcTemplate.query(sql, ratePeriodMapper, new Object[] { id });
}
return new FloatingRateData(id, name, isBaseLendingRate, isActive, createdBy, createdOn, modifiedBy, modifiedOn, ratePeriods,
@@ -199,19 +199,19 @@ public class FloatingRatesReadPlatformServiceImpl implements FloatingRatesReadPl
.append(" from m_product_loan as lp ")
.append(" join m_product_loan_floating_rates as plfr on lp.id = plfr.loan_product_id ")
.append(" join m_floating_rates as linkedrate on linkedrate.id = plfr.floating_rates_id ")
- .append("left join m_floating_rates_periods as linkedrateperiods on (linkedrate.id = linkedrateperiods.floating_rates_id and linkedrateperiods.is_active = 1) ")
+ .append("left join m_floating_rates_periods as linkedrateperiods on (linkedrate.id = linkedrateperiods.floating_rates_id and linkedrateperiods.is_active = true) ")
.append("left join ( ").append(" select blr.name, ").append(" blr.is_base_lending_rate, ")
.append(" blr.is_active, ").append(" blrperiods.from_date, ").append(" blrperiods.interest_rate ")
.append(" from m_floating_rates as blr ")
- .append(" left join m_floating_rates_periods as blrperiods on (blr.id = blrperiods.floating_rates_id and blrperiods.is_active = 1) ")
- .append(" where blr.is_base_lending_rate = 1 and blr.is_active = 1 ")
- .append(") as baserate on (linkedrateperiods.is_differential_to_base_lending_rate = 1 and linkedrate.is_base_lending_rate = 0) ")
+ .append(" left join m_floating_rates_periods as blrperiods on (blr.id = blrperiods.floating_rates_id and blrperiods.is_active = true) ")
+ .append(" where blr.is_base_lending_rate = true and blr.is_active = true ")
+ .append(") as baserate on (linkedrateperiods.is_differential_to_base_lending_rate = true and linkedrate.is_base_lending_rate = false) ")
.append("where (baserate.from_date is null ").append(" or baserate.from_date = (select MAX(b.from_date) ")
.append(" from (select blr.name, ").append(" blr.is_base_lending_rate, ")
.append(" blr.is_active, ").append(" blrperiods.from_date, ")
.append(" blrperiods.interest_rate ").append(" from m_floating_rates as blr ")
- .append(" left join m_floating_rates_periods as blrperiods on (blr.id = blrperiods.floating_rates_id and blrperiods.is_active = 1) ")
- .append(" where blr.is_base_lending_rate = 1 and blr.is_active = 1 ").append(" ) as b ")
+ .append(" left join m_floating_rates_periods as blrperiods on (blr.id = blrperiods.floating_rates_id and blrperiods.is_active = true) ")
+ .append(" where blr.is_base_lending_rate = true and blr.is_active = true ").append(" ) as b ")
.append(" where b.from_date <= linkedrateperiods.from_date)) ").append("and lp.id = ? ")
.append("order by linkedratePeriods_from_date desc ");
diff --git a/fineract-provider/src/main/java/org/apache/fineract/portfolio/group/service/CenterReadPlatformServiceImpl.java b/fineract-provider/src/main/java/org/apache/fineract/portfolio/group/service/CenterReadPlatformServiceImpl.java
index b99d4fe..3d952bc 100644
--- a/fineract-provider/src/main/java/org/apache/fineract/portfolio/group/service/CenterReadPlatformServiceImpl.java
+++ b/fineract-provider/src/main/java/org/apache/fineract/portfolio/group/service/CenterReadPlatformServiceImpl.java
@@ -212,14 +212,12 @@ public class CenterReadPlatformServiceImpl implements CenterReadPlatformService
+ " g.hierarchy as hierarchy, c.id as calendarId, ci.id as calendarInstanceId, ci.entity_id as entityId,"
+ " ci.entity_type_enum as entityTypeId, c.title as title, c.description as description,"
+ "c.location as location, c.start_date as startDate, c.end_date as endDate, c.recurrence as recurrence,c.meeting_time as meetingTime,"
- + "sum(if(l.loan_status_id=300 and lrs.duedate = ?,"
- + "(ifnull(lrs.principal_amount,0)) + (ifnull(lrs.interest_amount,0)),0)) as installmentDue,"
- + "sum(if(l.loan_status_id=300 and lrs.duedate = ?,"
- + "(ifnull(lrs.principal_completed_derived,0)) + (ifnull(lrs.interest_completed_derived,0)),0)) as totalCollected,"
- + "sum(if(l.loan_status_id=300 and lrs.duedate <= ?, (ifnull(lrs.principal_amount,0)) + (ifnull(lrs.interest_amount,0)),0))"
- + "- sum(if(l.loan_status_id=300 and lrs.duedate <= ?, (ifnull(lrs.principal_completed_derived,0)) + (ifnull(lrs.interest_completed_derived,0)),0)) as totaldue, "
- + "sum(if(l.loan_status_id=300 and lrs.duedate < ?, (ifnull(lrs.principal_amount,0)) + (ifnull(lrs.interest_amount,0)),0))"
- + "- sum(if(l.loan_status_id=300 and lrs.duedate < ?, (ifnull(lrs.principal_completed_derived,0)) + (ifnull(lrs.interest_completed_derived,0)),0)) as totaloverdue"
+ + "sum(CASE WHEN l.loan_status_id=300 and lrs.duedate = ? THEN COALESCE(lrs.principal_amount,0)) + (COALESCE(lrs.interest_amount,0) ELSE 0 END)) as installmentDue,"
+ + "sum(CASE WHEN l.loan_status_id=300 and lrs.duedate = ? THEN COALESCE(lrs.principal_completed_derived,0) + COALESCE(lrs.interest_completed_derived,0) ELSE 0 END) as totalCollected,"
+ + "sum(CASE WHEN l.loan_status_id=300 and lrs.duedate <= ? THEN COALESCE(lrs.principal_amount,0) + COALESCE(lrs.interest_amount,0) ELSE 0 END)"
+ + "- sum(CASE WHEN l.loan_status_id=300 and lrs.duedate <= ? THEN COALESCE(lrs.principal_completed_derived,0) + COALESCE(lrs.interest_completed_derived,0) ELSE 0 END) as totaldue, "
+ + "sum(CASE WHEN l.loan_status_id=300 and lrs.duedate < ? THEN COALESCE(lrs.principal_amount,0) + COALESCE(lrs.interest_amount,0) ELSE 0 END)"
+ + "- sum(CASE WHEN l.loan_status_id=300 and lrs.duedate < ? THEN COALESCE(lrs.principal_completed_derived,0) + COALESCE(lrs.interest_completed_derived,0) ELSE 0 END) as totaloverdue"
+ " from m_calendar c join m_calendar_instance ci on ci.calendar_id=c.id and ci.entity_type_enum=4"
+ " join m_group ce on ce.id = ci.entity_id" + " join m_group g on g.parent_id = ce.id"
+ " join m_group_client gc on gc.group_id=g.id" + " join m_client cl on cl.id=gc.client_id"
@@ -500,7 +498,7 @@ public class CenterReadPlatformServiceImpl implements CenterReadPlatformService
@Override
public Collection<GroupGeneralData> retrieveAssociatedGroups(final Long centerId) {
- final String sql = "select " + this.groupDataMapper.schema() + " where g.parent_id = ? ";
+ final String sql = "select " + this.groupDataMapper.schema() + " where g.parent_id = ? order by g.id";
return this.jdbcTemplate.query(sql, this.groupDataMapper, new Object[] { centerId });
}
diff --git a/fineract-provider/src/main/java/org/apache/fineract/portfolio/interestratechart/service/InterestRateChartReadPlatformServiceImpl.java b/fineract-provider/src/main/java/org/apache/fineract/portfolio/interestratechart/service/InterestRateChartReadPlatformServiceImpl.java
index e490cd7..84877b2 100644
--- a/fineract-provider/src/main/java/org/apache/fineract/portfolio/interestratechart/service/InterestRateChartReadPlatformServiceImpl.java
+++ b/fineract-provider/src/main/java/org/apache/fineract/portfolio/interestratechart/service/InterestRateChartReadPlatformServiceImpl.java
@@ -90,13 +90,13 @@ public class InterestRateChartReadPlatformServiceImpl implements InterestRateCha
sql.append(this.chartExtractor.schema());
sql.append(" where sp.id = ? order by irc.id, ");
sql.append("CASE ");
- sql.append("WHEN isPrimaryGroupingByAmount then ircd.amount_range_from ");
- sql.append("WHEN isPrimaryGroupingByAmount then ircd.amount_range_to ");
+ sql.append("WHEN irc.is_primary_grouping_by_amount then ircd.amount_range_from ");
+ sql.append("WHEN irc.is_primary_grouping_by_amount then ircd.amount_range_to ");
sql.append("END,");
sql.append("ircd.from_period, ircd.to_period,");
sql.append("CASE ");
- sql.append("WHEN !isPrimaryGroupingByAmount then ircd.amount_range_from ");
- sql.append("WHEN !isPrimaryGroupingByAmount then ircd.amount_range_to ");
+ sql.append("WHEN NOT irc.is_primary_grouping_by_amount then ircd.amount_range_from ");
+ sql.append("WHEN NOT irc.is_primary_grouping_by_amount then ircd.amount_range_to ");
sql.append("END");
return this.jdbcTemplate.query(
diff --git a/fineract-provider/src/main/java/org/apache/fineract/portfolio/loanaccount/domain/LoanAccountDomainService.java b/fineract-provider/src/main/java/org/apache/fineract/portfolio/loanaccount/domain/LoanAccountDomainService.java
index bb2879a..71db423 100644
--- a/fineract-provider/src/main/java/org/apache/fineract/portfolio/loanaccount/domain/LoanAccountDomainService.java
+++ b/fineract-provider/src/main/java/org/apache/fineract/portfolio/loanaccount/domain/LoanAccountDomainService.java
@@ -51,7 +51,7 @@ public interface LoanAccountDomainService {
void updateLoanCollateralTransaction(Set<LoanCollateralManagement> loanCollateralManagementList);
- void updateLoanCollateralStatus(Set<LoanCollateralManagement> loanCollateralManagementSet, Integer isReleased);
+ void updateLoanCollateralStatus(Set<LoanCollateralManagement> loanCollateralManagementSet, boolean isReleased);
/**
* This method is to recalculate and accrue the income till the last accrued date. this method is used when the
diff --git a/fineract-provider/src/main/java/org/apache/fineract/portfolio/loanaccount/domain/LoanAccountDomainServiceJpa.java b/fineract-provider/src/main/java/org/apache/fineract/portfolio/loanaccount/domain/LoanAccountDomainServiceJpa.java
index d20cd26..230d511 100644
--- a/fineract-provider/src/main/java/org/apache/fineract/portfolio/loanaccount/domain/LoanAccountDomainServiceJpa.java
+++ b/fineract-provider/src/main/java/org/apache/fineract/portfolio/loanaccount/domain/LoanAccountDomainServiceJpa.java
@@ -162,7 +162,7 @@ public class LoanAccountDomainServiceJpa implements LoanAccountDomainService {
@Transactional
@Override
- public void updateLoanCollateralStatus(Set<LoanCollateralManagement> loanCollateralManagementSet, Integer isReleased) {
+ public void updateLoanCollateralStatus(Set<LoanCollateralManagement> loanCollateralManagementSet, boolean isReleased) {
for (LoanCollateralManagement loanCollateralManagement : loanCollateralManagementSet) {
loanCollateralManagement.setIsReleased(isReleased);
}
diff --git a/fineract-provider/src/main/java/org/apache/fineract/portfolio/loanaccount/domain/LoanCollateralManagement.java b/fineract-provider/src/main/java/org/apache/fineract/portfolio/loanaccount/domain/LoanCollateralManagement.java
index f5d840c..94fbfa7 100644
--- a/fineract-provider/src/main/java/org/apache/fineract/portfolio/loanaccount/domain/LoanCollateralManagement.java
+++ b/fineract-provider/src/main/java/org/apache/fineract/portfolio/loanaccount/domain/LoanCollateralManagement.java
@@ -43,8 +43,8 @@ public class LoanCollateralManagement extends AbstractPersistableCustom {
@JoinColumn(name = "loan_id", referencedColumnName = "id", nullable = false)
private Loan loan;
- @Column(name = "is_released", nullable = false, columnDefinition = "0")
- private Integer isReleased = Integer.valueOf(0);
+ @Column(name = "is_released", nullable = false)
+ private boolean isReleased = false;
@ManyToOne(optional = false)
@JoinColumn(name = "client_collateral_id", nullable = false)
@@ -54,7 +54,7 @@ public class LoanCollateralManagement extends AbstractPersistableCustom {
}
- public LoanCollateralManagement(final BigDecimal quantity, final Integer isReleased) {
+ public LoanCollateralManagement(final BigDecimal quantity, final boolean isReleased) {
this.quantity = quantity;
this.isReleased = isReleased;
}
@@ -89,7 +89,7 @@ public class LoanCollateralManagement extends AbstractPersistableCustom {
this.loanTransaction = loanTransaction;
}
- public void setIsReleased(final Integer isReleased) {
+ public void setIsReleased(final boolean isReleased) {
this.isReleased = isReleased;
}
@@ -113,7 +113,7 @@ public class LoanCollateralManagement extends AbstractPersistableCustom {
return this.clientCollateralManagement;
}
- public Integer getIsReleased() {
+ public boolean isReleased() {
return this.isReleased;
}
diff --git a/fineract-provider/src/main/java/org/apache/fineract/portfolio/loanaccount/guarantor/service/GuarantorReadPlatformServiceImpl.java b/fineract-provider/src/main/java/org/apache/fineract/portfolio/loanaccount/guarantor/service/GuarantorReadPlatformServiceImpl.java
index 492fe12..8a56002 100644
--- a/fineract-provider/src/main/java/org/apache/fineract/portfolio/loanaccount/guarantor/service/GuarantorReadPlatformServiceImpl.java
+++ b/fineract-provider/src/main/java/org/apache/fineract/portfolio/loanaccount/guarantor/service/GuarantorReadPlatformServiceImpl.java
@@ -19,6 +19,7 @@
package org.apache.fineract.portfolio.loanaccount.guarantor.service;
import java.math.BigDecimal;
+import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.time.LocalDate;
@@ -75,9 +76,15 @@ public class GuarantorReadPlatformServiceImpl implements GuarantorReadPlatformSe
public List<GuarantorData> retrieveGuarantorsForLoan(final Long loanId) {
final GuarantorMapper rm = new GuarantorMapper();
String sql = "select " + rm.schema();
- sql += " where loan_id = ? group by g.id,gfd.id, gt.id";
- final List<GuarantorData> guarantorDatas = this.jdbcTemplate.query(sql, rm,
- new Object[] { AccountAssociationType.GUARANTOR_ACCOUNT_ASSOCIATION.getValue(), loanId });
+ sql += " where loan_id = ? group by g.id,gfd.id, gt.id, sa.id, oht.id, cv.id";
+ String finalSql = sql;
+ final List<GuarantorData> guarantorDatas = this.jdbcTemplate.query(con -> {
+ PreparedStatement preparedStatement = con.prepareStatement(finalSql, ResultSet.TYPE_SCROLL_SENSITIVE,
+ ResultSet.CONCUR_UPDATABLE);
+ preparedStatement.setInt(1, AccountAssociationType.GUARANTOR_ACCOUNT_ASSOCIATION.getValue());
+ preparedStatement.setLong(2, loanId);
+ return preparedStatement;
+ }, rm);
final List<GuarantorData> mergedGuarantorDatas = new ArrayList<>();
@@ -91,9 +98,16 @@ public class GuarantorReadPlatformServiceImpl implements GuarantorReadPlatformSe
public GuarantorData retrieveGuarantor(final Long loanId, final Long guarantorId) {
final GuarantorMapper rm = new GuarantorMapper();
String sql = "select " + rm.schema();
- sql += " where g.loan_id = ? and g.id = ? group by g.id, gfd.id, gt.id";
- final GuarantorData guarantorData = this.jdbcTemplate.queryForObject(sql, rm,
- new Object[] { AccountAssociationType.GUARANTOR_ACCOUNT_ASSOCIATION.getValue(), loanId, guarantorId });
+ sql += " where g.loan_id = ? and g.id = ? group by g.id, gfd.id, gt.id, sa.id, oht.id, cv.id";
+ String finalSql = sql;
+ final GuarantorData guarantorData = this.jdbcTemplate.query(con -> {
+ PreparedStatement preparedStatement = con.prepareStatement(finalSql, ResultSet.TYPE_SCROLL_SENSITIVE,
+ ResultSet.CONCUR_UPDATABLE);
+ preparedStatement.setInt(1, AccountAssociationType.GUARANTOR_ACCOUNT_ASSOCIATION.getValue());
+ preparedStatement.setLong(2, loanId);
+ preparedStatement.setLong(3, guarantorId);
+ return preparedStatement;
+ }, rm).get(0);
return mergeDetailsForClientOrStaffGuarantor(guarantorData);
}
@@ -114,7 +128,7 @@ public class GuarantorReadPlatformServiceImpl implements GuarantorReadPlatformSe
.append(" FROM m_guarantor g") //
.append(" left JOIN m_code_value cv on g.client_reln_cv_id = cv.id")//
.append(" left JOIN m_guarantor_funding_details gfd on g.id = gfd.guarantor_id")//
- .append(" left JOIN m_portfolio_account_associations aa on gfd.account_associations_id = aa.id and aa.is_active = 1 and aa.association_type_enum = ?")//
+ .append(" left JOIN m_portfolio_account_associations aa on gfd.account_associations_id = aa.id and aa.is_active = true and aa.association_type_enum = ?")//
.append(" left JOIN m_savings_account sa on sa.id = aa.linked_savings_account_id ")//
.append(" left join m_guarantor_transaction gt on gt.guarantor_fund_detail_id = gfd.id") //
.append(" left join m_deposit_account_on_hold_transaction oht on oht.id = gt.deposit_on_hold_transaction_id");
diff --git a/fineract-provider/src/main/java/org/apache/fineract/portfolio/loanaccount/service/GLIMAccountInfoReadPlatformServiceImpl.java b/fineract-provider/src/main/java/org/apache/fineract/portfolio/loanaccount/service/GLIMAccountInfoReadPlatformServiceImpl.java
index 9f1ea50..cc5e265 100644
--- a/fineract-provider/src/main/java/org/apache/fineract/portfolio/loanaccount/service/GLIMAccountInfoReadPlatformServiceImpl.java
+++ b/fineract-provider/src/main/java/org/apache/fineract/portfolio/loanaccount/service/GLIMAccountInfoReadPlatformServiceImpl.java
@@ -104,7 +104,7 @@ public class GLIMAccountInfoReadPlatformServiceImpl implements GLIMAccountInfoRe
final GLIMFieldsMapper rm = new GLIMFieldsMapper();
final String sql = "select " + rm.schema() + " and ln.group_id=?";
- return this.jdbcTemplate.query(sql, rm, new Object[] { groupId });
+ return this.jdbcTemplate.query(sql, rm, new Object[] { Long.parseLong(groupId) });
}
@Override
@@ -115,7 +115,7 @@ public class GLIMAccountInfoReadPlatformServiceImpl implements GLIMAccountInfoRe
final String sql = "select " + rm.schema() + " where glim.group_id=?";
- return this.jdbcTemplate.query(sql, rm, new Object[] { groupId });
+ return this.jdbcTemplate.query(sql, rm, new Object[] { Long.parseLong(groupId) });
}
@Override
diff --git a/fineract-provider/src/main/java/org/apache/fineract/portfolio/loanaccount/service/LoanAccrualWritePlatformServiceImpl.java b/fineract-provider/src/main/java/org/apache/fineract/portfolio/loanaccount/service/LoanAccrualWritePlatformServiceImpl.java
index 2db468a..dd3140a 100644
--- a/fineract-provider/src/main/java/org/apache/fineract/portfolio/loanaccount/service/LoanAccrualWritePlatformServiceImpl.java
+++ b/fineract-provider/src/main/java/org/apache/fineract/portfolio/loanaccount/service/LoanAccrualWritePlatformServiceImpl.java
@@ -269,7 +269,7 @@ public class LoanAccrualWritePlatformServiceImpl implements LoanAccrualWritePlat
BigDecimal totalAccInterest, BigDecimal feeportion, BigDecimal totalAccFee, BigDecimal penaltyportion,
BigDecimal totalAccPenalty, final LocalDate accruedTill) throws DataAccessException {
String transactionSql = "INSERT INTO m_loan_transaction (loan_id,office_id,is_reversed,transaction_type_enum,transaction_date,amount,interest_portion_derived,"
- + "fee_charges_portion_derived,penalty_charges_portion_derived, submitted_on_date) VALUES (?, ?, 0, ?, ?, ?, ?, ?, ?, ?)";
+ + "fee_charges_portion_derived,penalty_charges_portion_derived, submitted_on_date) VALUES (?, ?, false, ?, ?, ?, ?, ?, ?, ?)";
this.jdbcTemplate.update(transactionSql, scheduleAccrualData.getLoanId(), scheduleAccrualData.getOfficeId(),
LoanTransactionType.ACCRUAL.getValue(), Date.from(accruedTill.atStartOfDay(ZoneId.systemDefault()).toInstant()), amount,
interestportion, feeportion, penaltyportion, DateUtils.getDateOfTenant());
diff --git a/fineract-provider/src/main/java/org/apache/fineract/portfolio/loanaccount/service/LoanApplicationWritePlatformServiceJpaRepositoryImpl.java b/fineract-provider/src/main/java/org/apache/fineract/portfolio/loanaccount/service/LoanApplicationWritePlatformServiceJpaRepositoryImpl.java
index a0658d0..815d840 100644
--- a/fineract-provider/src/main/java/org/apache/fineract/portfolio/loanaccount/service/LoanApplicationWritePlatformServiceJpaRepositoryImpl.java
+++ b/fineract-provider/src/main/java/org/apache/fineract/portfolio/loanaccount/service/LoanApplicationWritePlatformServiceJpaRepositoryImpl.java
@@ -1303,7 +1303,7 @@ public class LoanApplicationWritePlatformServiceJpaRepositoryImpl implements Loa
BigDecimal quantity = loanCollateralManagement.getQuantity();
ClientCollateralManagement clientCollateralManagement = loanCollateralManagement.getClientCollateralManagement();
clientCollateralManagement.updateQuantityAfterLoanClosed(quantity);
- loanCollateralManagement.setIsReleased(Integer.valueOf(1));
+ loanCollateralManagement.setIsReleased(true);
loanCollateralManagement.setClientCollateralManagement(clientCollateralManagement);
}
@@ -1662,7 +1662,7 @@ public class LoanApplicationWritePlatformServiceJpaRepositoryImpl implements Loa
clientCollateralManagement
.updateQuantity(clientCollateralManagement.getQuantity().add(loanCollateralManagement.getQuantity()));
loanCollateralManagement.setClientCollateralManagement(clientCollateralManagement);
- loanCollateralManagement.setIsReleased(Integer.valueOf(1));
+ loanCollateralManagement.setIsReleased(true);
}
loan.updateLoanCollateral(loanCollateralManagements);
}
diff --git a/fineract-provider/src/main/java/org/apache/fineract/portfolio/loanaccount/service/LoanArrearsAgingServiceImpl.java b/fineract-provider/src/main/java/org/apache/fineract/portfolio/loanaccount/service/LoanArrearsAgingServiceImpl.java
index 2673656..5c25c21 100644
--- a/fineract-provider/src/main/java/org/apache/fineract/portfolio/loanaccount/service/LoanArrearsAgingServiceImpl.java
+++ b/fineract-provider/src/main/java/org/apache/fineract/portfolio/loanaccount/service/LoanArrearsAgingServiceImpl.java
@@ -91,16 +91,16 @@ public class LoanArrearsAgingServiceImpl implements LoanArrearsAgingService, Bus
this.jdbcTemplate.execute("truncate table m_loan_arrears_aging");
final StringBuilder updateSqlBuilder = new StringBuilder(900);
- final String principalOverdueCalculationSql = "SUM(ifnull(mr.principal_amount, 0) - ifnull(mr.principal_completed_derived, 0) - ifnull(mr.principal_writtenoff_derived, 0))";
- final String interestOverdueCalculationSql = "SUM(ifnull(mr.interest_amount, 0) - ifnull(mr.interest_writtenoff_derived, 0) - ifnull(mr.interest_waived_derived, 0) - "
- + "ifnull(mr.interest_completed_derived, 0))";
- final String feeChargesOverdueCalculationSql = "SUM(ifnull(mr.fee_charges_amount, 0) - ifnull(mr.fee_charges_writtenoff_derived, 0) - "
- + "ifnull(mr.fee_charges_waived_derived, 0) - ifnull(mr.fee_charges_completed_derived, 0))";
- final String penaltyChargesOverdueCalculationSql = "SUM(ifnull(mr.penalty_charges_amount, 0) - ifnull(mr.penalty_charges_writtenoff_derived, 0) - "
- + "ifnull(mr.penalty_charges_waived_derived, 0) - ifnull(mr.penalty_charges_completed_derived, 0))";
+ 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`)");
+ "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,");
@@ -114,8 +114,8 @@ public class LoanArrearsAgingServiceImpl implements LoanArrearsAgingService, Bus
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 < SUBDATE(CURDATE(),INTERVAL ifnull(ml.grace_on_arrears_ageing,0) day) ");
- updateSqlBuilder.append(" and (prd.arrears_based_on_original_schedule = 0 or prd.arrears_based_on_original_schedule is null) ");
+ updateSqlBuilder.append(" and mr.duedate < SUBDATE(CURDATE(),INTERVAL COALESCE(ml.grace_on_arrears_ageing, 0) day) ");
+ 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();
@@ -144,7 +144,7 @@ public class LoanArrearsAgingServiceImpl implements LoanArrearsAgingService, Bus
if (updateStatement.size() == 1) {
this.jdbcTemplate.update(updateStatement.get(0));
} else {
- String deletestatement = "DELETE FROM `m_loan_arrears_aging` WHERE `loan_id`=" + loan.getId();
+ String deletestatement = "DELETE FROM m_loan_arrears_aging WHERE loan_id=" + loan.getId();
this.jdbcTemplate.update(deletestatement);
}
}
@@ -156,7 +156,7 @@ public class LoanArrearsAgingServiceImpl implements LoanArrearsAgingService, Bus
Integer.class, loan.getId());
String updateStatement = constructUpdateStatement(loan, count == 0);
if (updateStatement == null) {
- String deletestatement = "DELETE FROM `m_loan_arrears_aging` WHERE `loan_id`=" + loan.getId();
+ String deletestatement = "DELETE FROM m_loan_arrears_aging WHERE loan_id=" + loan.getId();
this.jdbcTemplate.update(deletestatement);
} else {
this.jdbcTemplate.update(updateStatement);
@@ -203,9 +203,9 @@ public class LoanArrearsAgingServiceImpl implements LoanArrearsAgingService, Bus
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 = 1 ");
+ "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 < SUBDATE(CURDATE(),INTERVAL ifnull(ml.grace_on_arrears_ageing,0) day) group by ml.id");
+ "WHERE ml.loan_status_id = 300 and mr.completed_derived is false and mr.duedate < SUBDATE(CURDATE(),INTERVAL COALESCE(ml.grace_on_arrears_ageing, 0) day) group by ml.id");
List<Long> loanIds = this.jdbcTemplate.queryForList(loanIdentifier.toString(), Long.class);
if (!loanIds.isEmpty()) {
String loanIdsAsString = loanIds.toString();
@@ -300,8 +300,8 @@ public class LoanArrearsAgingServiceImpl implements LoanArrearsAgingService, Bus
private String constructInsertStatement(final Long loanId, BigDecimal principalOverdue, BigDecimal interestOverdue,
BigDecimal feeOverdue, BigDecimal penaltyOverdue, LocalDate overDueSince) {
final StringBuilder insertStatementBuilder = new StringBuilder(900);
- insertStatementBuilder.append("INSERT INTO m_loan_arrears_aging(`loan_id`,`principal_overdue_derived`,`interest_overdue_derived`,")
- .append("`fee_charges_overdue_derived`,`penalty_charges_overdue_derived`,`total_overdue_derived`,`overdue_since_date_derived`) VALUES(");
+ insertStatementBuilder.append("INSERT INTO m_loan_arrears_aging(loan_id,principal_overdue_derived,interest_overdue_derived,")
+ .append("fee_charges_overdue_derived,penalty_charges_overdue_derived,total_overdue_derived,overdue_since_date_derived) VALUES(");
insertStatementBuilder.append(loanId).append(",");
insertStatementBuilder.append(principalOverdue).append(",");
insertStatementBuilder.append(interestOverdue).append(",");
@@ -316,15 +316,15 @@ public class LoanArrearsAgingServiceImpl implements LoanArrearsAgingService, Bus
private String constructUpdateStatement(final Long loanId, BigDecimal principalOverdue, BigDecimal interestOverdue,
BigDecimal feeOverdue, BigDecimal penaltyOverdue, LocalDate overDueSince) {
final StringBuilder insertStatementBuilder = new StringBuilder(900);
- insertStatementBuilder.append("UPDATE m_loan_arrears_aging mla SET mla.principal_overdue_derived=");
- insertStatementBuilder.append(principalOverdue).append(", mla.interest_overdue_derived=");
- insertStatementBuilder.append(interestOverdue).append(", mla.fee_charges_overdue_derived=");
- insertStatementBuilder.append(feeOverdue).append(", mla.penalty_charges_overdue_derived=");
- insertStatementBuilder.append(penaltyOverdue).append(", mla.total_overdue_derived=");
+ insertStatementBuilder.append("UPDATE m_loan_arrears_aging SET principal_overdue_derived=");
+ insertStatementBuilder.append(principalOverdue).append(", interest_overdue_derived=");
+ insertStatementBuilder.append(interestOverdue).append(", fee_charges_overdue_derived=");
+ insertStatementBuilder.append(feeOverdue).append(", penalty_charges_overdue_derived=");
+ insertStatementBuilder.append(penaltyOverdue).append(", total_overdue_derived=");
BigDecimal totalOverDue = principalOverdue.add(interestOverdue).add(feeOverdue).add(penaltyOverdue);
- insertStatementBuilder.append(totalOverDue).append(",mla.overdue_since_date_derived= '");
+ insertStatementBuilder.append(totalOverDue).append(",overdue_since_date_derived= '");
insertStatementBuilder.append(this.formatter.format(overDueSince)).append("' ");
- insertStatementBuilder.append("WHERE mla.loan_id=").append(loanId);
+ insertStatementBuilder.append("WHERE loan_id=").append(loanId);
return insertStatementBuilder.toString();
}
@@ -425,7 +425,7 @@ public class LoanArrearsAgingServiceImpl implements LoanArrearsAgingService, Bus
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 < SUBDATE(CURDATE(),INTERVAL ifnull(ml.grace_on_arrears_ageing,0) day) and ");
+ scheduleDetail.append("where mr.duedate < SUBDATE(CURDATE(),INTERVAL COALESCE(ml.grace_on_arrears_ageing, 0) day) and ");
scheduleDetail.append("ml.id IN(").append(loanIdsAsString).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");
diff --git a/fineract-provider/src/main/java/org/apache/fineract/portfolio/loanaccount/service/LoanChargeReadPlatformServiceImpl.java b/fineract-provider/src/main/java/org/apache/fineract/portfolio/loanaccount/service/LoanChargeReadPlatformServiceImpl.java
index 954f89e..fcded5b 100644
--- a/fineract-provider/src/main/java/org/apache/fineract/portfolio/loanaccount/service/LoanChargeReadPlatformServiceImpl.java
+++ b/fineract-provider/src/main/java/org/apache/fineract/portfolio/loanaccount/service/LoanChargeReadPlatformServiceImpl.java
@@ -77,7 +77,7 @@ public class LoanChargeReadPlatformServiceImpl implements LoanChargeReadPlatform
+ "lc.charge_payment_mode_enum as chargePaymentMode, " + "lc.is_paid_derived as paid, " + "lc.waived as waied, "
+ "lc.min_cap as minCap, lc.max_cap as maxCap, " + "lc.charge_amount_or_percentage as amountOrPercentage, "
+ "c.currency_code as currencyCode, oc.name as currencyName, "
- + "date(ifnull(dd.disbursedon_date,dd.expected_disburse_date)) as disbursementDate, "
+ + "date(coalesce(dd.disbursedon_date,dd.expected_disburse_date)) as disbursementDate, "
+ "oc.decimal_places as currencyDecimalPlaces, oc.currency_multiplesof as inMultiplesOf, oc.display_symbol as currencyDisplaySymbol, "
+ "oc.internationalized_name_code as currencyNameCode from m_charge c "
+ "join m_organisation_currency oc on c.currency_code = oc.code " + "join m_loan_charge lc on lc.charge_id = c.id "
@@ -188,8 +188,8 @@ public class LoanChargeReadPlatformServiceImpl implements LoanChargeReadPlatform
final LoanChargeMapper rm = new LoanChargeMapper();
- final String sql = "select " + rm.schema() + " where lc.loan_id=? AND lc.is_active = 1"
- + " order by ifnull(lc.due_for_collection_as_of_date,date(ifnull(dd.disbursedon_date,dd.expected_disburse_date))),lc.charge_time_enum ASC, lc.due_for_collection_as_of_date ASC, lc.is_penalty ASC";
+ final String sql = "select " + rm.schema() + " where lc.loan_id=? AND lc.is_active = true"
+ + " order by coalesce(lc.due_for_collection_as_of_date,date(coalesce(dd.disbursedon_date,dd.expected_disburse_date))),lc.charge_time_enum ASC, lc.due_for_collection_as_of_date ASC, lc.is_penalty ASC";
return this.jdbcTemplate.query(sql, rm, new Object[] { loanId });
}
@@ -198,7 +198,7 @@ public class LoanChargeReadPlatformServiceImpl implements LoanChargeReadPlatform
public Collection<LoanChargeData> retrieveLoanChargesForFeePayment(final Integer paymentMode, final Integer loanStatus) {
final LoanChargeMapperWithLoanId rm = new LoanChargeMapperWithLoanId();
final String sql = "select " + rm.schema()
- + "where loan.loan_status_id= ? and lc.charge_payment_mode_enum=? and lc.waived =0 and lc.is_paid_derived=0 and lc.is_active = 1";
+ + "where loan.loan_status_id= ? and lc.charge_payment_mode_enum=? and lc.waived = false and lc.is_paid_derived=false and lc.is_active = true";
return this.jdbcTemplate.query(sql, rm, new Object[] { loanStatus, paymentMode });
}
@@ -229,7 +229,7 @@ public class LoanChargeReadPlatformServiceImpl implements LoanChargeReadPlatform
final LoanInstallmentChargeMapper rm = new LoanInstallmentChargeMapper();
String sql = "select " + rm.schema() + "where lic.loan_charge_id= ? ";
if (onlyPaymentPendingCharges) {
- sql = sql + "and lic.waived =0 and lic.is_paid_derived=0";
+ sql = sql + "and lic.waived = false and lic.is_paid_derived=false";
}
sql = sql + " order by lsi.installment";
return this.jdbcTemplate.query(sql, rm, new Object[] { loanChargeId });
@@ -262,7 +262,7 @@ public class LoanChargeReadPlatformServiceImpl implements LoanChargeReadPlatform
public Collection<Integer> retrieveOverdueInstallmentChargeFrequencyNumber(final Long loanId, final Long chargeId,
final Integer periodNumber) {
String sql = "select oic.frequency_number from m_loan_overdue_installment_charge oic inner join m_loan_charge lc on lc.id=oic.loan_charge_id inner join m_loan_repayment_schedule rs on rs.id = oic.loan_schedule_id inner join m_loan loan on loan.id=rs.loan_id "
- + "where lc.is_active = 1 and loan.id = ? and rs.installment=?";
+ + "where lc.is_active = true and loan.id = ? and rs.installment=?";
Object[] params = { loanId, periodNumber };
if (chargeId != null) {
sql += " and lc.charge_id = ? ";
@@ -276,7 +276,7 @@ public class LoanChargeReadPlatformServiceImpl implements LoanChargeReadPlatform
final LoanChargeAccrualMapper rm = new LoanChargeAccrualMapper();
- final String sql = "select " + rm.schema() + " where lc.loan_id=? AND lc.is_active = 1 group by lc.id "
+ final String sql = "select " + rm.schema() + " where lc.loan_id=? AND lc.is_active = true group by lc.id "
+ " order by lc.charge_time_enum ASC, lc.due_for_collection_as_of_date ASC, lc.is_penalty ASC";
Collection<LoanChargeData> charges = this.jdbcTemplate.query(sql, rm,
@@ -320,7 +320,7 @@ public class LoanChargeReadPlatformServiceImpl implements LoanChargeReadPlatform
sb.append("select lcp.loan_charge_id, lcp.amount");
sb.append(" from m_loan_charge_paid_by lcp ");
sb.append(
- "inner join m_loan_transaction lt on lt.id = lcp.loan_transaction_id and lt.is_reversed = 0 and lt.transaction_type_enum = ? and lt.loan_id = ?");
+ "inner join m_loan_transaction lt on lt.id = lcp.loan_transaction_id and lt.is_reversed = false and lt.transaction_type_enum = ? and lt.loan_id = ?");
sb.append(") cp on cp.loan_charge_id= lc.id ");
schemaSql = sb.toString();
@@ -354,7 +354,7 @@ public class LoanChargeReadPlatformServiceImpl implements LoanChargeReadPlatform
final LoanChargeUnRecognizedIncomeMapper rm = new LoanChargeUnRecognizedIncomeMapper(loanChargeDatas);
- final String sql = "select " + rm.schema() + " where lc.loan_id=? AND lc.is_active = 1 group by lc.id "
+ final String sql = "select " + rm.schema() + " where lc.loan_id=? AND lc.is_active = true group by lc.id "
+ " order by lc.charge_time_enum ASC, lc.due_for_collection_as_of_date ASC, lc.is_penalty ASC";
return this.jdbcTemplate.query(sql, rm, new Object[] { LoanTransactionType.WAIVE_CHARGES.getValue(), loanId, loanId });
@@ -380,7 +380,7 @@ public class LoanChargeReadPlatformServiceImpl implements LoanChargeReadPlatform
sb.append("select cpb.loan_charge_id, lt.unrecognized_income_portion");
sb.append(" from m_loan_charge_paid_by cpb ");
sb.append(
- "inner join m_loan_transaction lt on lt.id = cpb.loan_transaction_id and lt.is_reversed = 0 and lt.transaction_type_enum = ? and lt.loan_id = ? ");
+ "inner join m_loan_transaction lt on lt.id = cpb.loan_transaction_id and lt.is_reversed = false and lt.transaction_type_enum = ? and lt.loan_id = ? ");
sb.append(") wt on wt.loan_charge_id= lc.id ");
schemaSql = sb.toString();
@@ -438,7 +438,7 @@ public class LoanChargeReadPlatformServiceImpl implements LoanChargeReadPlatform
sb.append("select lcp.loan_charge_id, lcp.amount as amount, lcp.installment_number ");
sb.append(" from m_loan_charge_paid_by lcp ");
sb.append(
- "inner join m_loan_transaction lt on lt.id = lcp.loan_transaction_id and lt.is_reversed = 0 and lt.transaction_type_enum = ?");
+ "inner join m_loan_transaction lt on lt.id = lcp.loan_transaction_id and lt.is_reversed = false and lt.transaction_type_enum = ?");
sb.append(") cp on cp.loan_charge_id= lic.loan_charge_id and cp.installment_number = lsi.installment ");
schemaSql = sb.toString();
}
@@ -482,7 +482,7 @@ public class LoanChargeReadPlatformServiceImpl implements LoanChargeReadPlatform
sb.append(" sum(lt.unrecognized_income_portion) as amountUnrecognized ");
sb.append(" from m_loan_charge_paid_by cpb ");
sb.append(
- "inner join m_loan_transaction lt on lt.id = cpb.loan_transaction_id and lt.is_reversed = 0 and lt.transaction_type_enum = ?");
+ "inner join m_loan_transaction lt on lt.id = cpb.loan_transaction_id and lt.is_reversed = false and lt.transaction_type_enum = ?");
schemaSql = sb.toString();
}
@@ -533,7 +533,7 @@ public class LoanChargeReadPlatformServiceImpl implements LoanChargeReadPlatform
sb.append("lcp.loan_transaction_id as transactionId, ");
sb.append("lcp.installment_number as installmentNumber ");
sb.append(" from m_loan_charge_paid_by lcp ");
- sb.append(" join m_loan_transaction lt on lt.id = lcp.loan_transaction_id and lt.is_reversed=0");
+ sb.append(" join m_loan_transaction lt on lt.id = lcp.loan_transaction_id and lt.is_reversed=false");
schemaSql = sb.toString();
}
diff --git a/fineract-provider/src/main/java/org/apache/fineract/portfolio/loanaccount/service/LoanReadPlatformServiceImpl.java b/fineract-provider/src/main/java/org/apache/fineract/portfolio/loanaccount/service/LoanReadPlatformServiceImpl.java
index 98233d0..5872fb2 100644
--- a/fineract-provider/src/main/java/org/apache/fineract/portfolio/loanaccount/service/LoanReadPlatformServiceImpl.java
+++ b/fineract-provider/src/main/java/org/apache/fineract/portfolio/loanaccount/service/LoanReadPlatformServiceImpl.java
@@ -281,7 +281,7 @@ public class LoanReadPlatformServiceImpl implements LoanReadPlatformService {
* TODO Vishwas: Remove references to "Contra" from the codebase
***/
final String sql = "select " + rm.loanPaymentsSchema()
- + " where tr.loan_id = ? and tr.transaction_type_enum not in (0, 3) and (tr.is_reversed=0 or tr.manually_adjusted_or_reversed = 1) order by tr.transaction_date ASC,id ";
+ + " where tr.loan_id = ? and tr.transaction_type_enum not in (0, 3) and (tr.is_reversed=false or tr.manually_adjusted_or_reversed = true) order by tr.transaction_date ASC,id ";
return this.jdbcTemplate.query(sql, rm, new Object[] { loanId });
} catch (final EmptyResultDataAccessException e) {
return null;
@@ -1530,8 +1530,8 @@ public class LoanReadPlatformServiceImpl implements LoanReadPlatformService {
final StringBuilder sqlBuilder = new StringBuilder(400);
sqlBuilder.append("select ").append(rm.schema()).append(" where DATE_SUB(CURDATE(),INTERVAL ? DAY) > ls.duedate ")
- .append(" and ls.completed_derived <> 1 and mc.charge_applies_to_enum =1 ")
- .append(" and ls.recalculated_interest_component <> 1 ")
+ .append(" and ls.completed_derived <> true and mc.charge_applies_to_enum =1 ")
+ .append(" and ls.recalculated_interest_component <> true ")
.append(" and mc.charge_time_enum = 9 and ml.loan_status_id = 300 ");
if (backdatePenalties) {
@@ -1571,7 +1571,7 @@ public class LoanReadPlatformServiceImpl implements LoanReadPlatformService {
public String schema() {
return "dd.id as id,dd.expected_disburse_date as expectedDisbursementdate, dd.disbursedon_date as actualDisbursementdate,dd.principal as principal,dd.net_disbursal_amount as netDisbursalAmount,sum(lc.amount) chargeAmount, lc.amount_waived_derived waivedAmount,group_concat(lc.id) loanChargeId "
+ "from m_loan l inner join m_loan_disbursement_detail dd on dd.loan_id = l.id left join m_loan_tranche_disbursement_charge tdc on tdc.disbursement_detail_id=dd.id "
- + "left join m_loan_charge lc on lc.id=tdc.loan_charge_id and lc.is_active=1";
+ + "left join m_loan_charge lc on lc.id=tdc.loan_charge_id and lc.is_active=true";
}
@Override
@@ -1638,11 +1638,11 @@ public class LoanReadPlatformServiceImpl implements LoanReadPlatformService {
Date organisationStartDate = this.configurationDomainService.retrieveOrganisationStartDate();
final StringBuilder sqlBuilder = new StringBuilder(400);
sqlBuilder.append("select ").append(mapper.schema()).append(
- " where (recaldet.is_compounding_to_be_posted_as_transaction is null or recaldet.is_compounding_to_be_posted_as_transaction = 0) ")
- .append(" and (((ls.fee_charges_amount <> if(ls.accrual_fee_charges_derived is null,0, ls.accrual_fee_charges_derived))")
- .append(" or ( ls.penalty_charges_amount <> if(ls.accrual_penalty_charges_derived is null,0,ls.accrual_penalty_charges_derived))")
- .append(" or ( ls.interest_amount <> if(ls.accrual_interest_derived is null,0,ls.accrual_interest_derived)))")
- .append(" and loan.loan_status_id=:active and mpl.accounting_type=:type and loan.is_npa=0 and ls.duedate <= CURDATE()) ");
+ " where (recaldet.is_compounding_to_be_posted_as_transaction is null or recaldet.is_compounding_to_be_posted_as_transaction = false) ")
+ .append(" and (((ls.fee_charges_amount <> COALESCE(ls.accrual_fee_charges_derived, 0))")
+ .append(" or ( ls.penalty_charges_amount <> COALESCE(ls.accrual_penalty_charges_derived, 0))")
+ .append(" or ( ls.interest_amount <> COALESCE(ls.accrual_interest_derived, 0)))")
+ .append(" and loan.loan_status_id=:active and mpl.accounting_type=:type and loan.is_npa=false and ls.duedate <= CURDATE()) ");
if (organisationStartDate != null) {
sqlBuilder.append(" and ls.duedate > :organisationstartdate ");
}
@@ -1664,12 +1664,14 @@ public class LoanReadPlatformServiceImpl implements LoanReadPlatformService {
Date organisationStartDate = this.configurationDomainService.retrieveOrganisationStartDate();
final StringBuilder sqlBuilder = new StringBuilder(400);
sqlBuilder.append("select ").append(mapper.schema()).append(
- " where (recaldet.is_compounding_to_be_posted_as_transaction is null or recaldet.is_compounding_to_be_posted_as_transaction = 0) ")
- .append(" and (((ls.fee_charges_amount <> if(ls.accrual_fee_charges_derived is null,0, ls.accrual_fee_charges_derived))")
- .append(" or (ls.penalty_charges_amount <> if(ls.accrual_penalty_charges_derived is null,0,ls.accrual_penalty_charges_derived))")
- .append(" or (ls.interest_amount <> if(ls.accrual_interest_derived is null,0,ls.accrual_interest_derived)))")
- .append(" and loan.loan_status_id=:active and mpl.accounting_type=:type and (loan.closedon_date <= :tilldate or loan.closedon_date is null)")
- .append(" and loan.is_npa=0 and (ls.duedate <= :tilldate or (ls.duedate > :tilldate and ls.fromdate < :tilldate))) ");
+ " where (recaldet.is_compounding_to_be_posted_as_transaction is null or recaldet.is_compounding_to_be_posted_as_transaction = false) ")
+ .append(" and (((ls.fee_charges_amount <> COALESCE(ls.accrual_fee_charges_derived, 0))")
+ .append(" or (ls.penalty_charges_amount <> COALESCE(ls.accrual_penalty_charges_derived, 0))")
+ .append(" or (ls.interest_amount <> COALESCE(ls.accrual_interest_derived, 0)))")
+ .append(" and loan.loan_status_id=:active and mpl.accounting_type=:type and (loan.closedon_date <= :tilldate"
+ + " or loan.closedon_date is null)")
+ .append(" and loan.is_npa=false and (ls.duedate <= :tilldate or (ls.duedate > :tilldate"
+ + " and ls.fromdate < :tilldate))) ");
if (organisationStartDate != null) {
sqlBuilder.append(" and ls.duedate > :organisationstartdate ");
}
@@ -1689,7 +1691,7 @@ public class LoanReadPlatformServiceImpl implements LoanReadPlatformService {
public String schema() {
final StringBuilder sqlBuilder = new StringBuilder(400);
- sqlBuilder.append("loan.id as loanId ,if(loan.client_id is null,mg.office_id,mc.office_id) as officeId,")
+ sqlBuilder.append("loan.id as loanId , (CASE WHEN loan.client_id is null THEN mg.office_id ELSE mc.office_id END) as officeId,")
.append("loan.accrued_till as accruedTill, loan.repayment_period_frequency_enum as frequencyEnum, ")
.append("loan.interest_calculated_from_date as interestCalculatedFrom, ").append("loan.repay_every as repayEvery,")
.append("ls.installment as installmentNumber, ")
@@ -1751,7 +1753,7 @@ public class LoanReadPlatformServiceImpl implements LoanReadPlatformService {
public String schema() {
final StringBuilder sqlBuilder = new StringBuilder(400);
- sqlBuilder.append("loan.id as loanId ,if(loan.client_id is null,mg.office_id,mc.office_id) as officeId,")
+ sqlBuilder.append("loan.id as loanId, (CASE WHEN loan.client_id is null THEN mg.office_id ELSE mc.office_id END) as officeId,")
.append("ls.duedate as duedate,ls.fromdate as fromdate,ls.id as scheduleId,loan.product_id as productId,")
.append("ls.installment as installmentNumber, ")
.append("ls.interest_amount as interest, ls.interest_waived_derived as interestWaived,")
@@ -1840,15 +1842,15 @@ public class LoanReadPlatformServiceImpl implements LoanReadPlatformService {
sqlBuilder.append(" LEFT JOIN m_loan_disbursement_detail dd on dd.loan_id=ml.id and dd.disbursedon_date is null ");
// For Floating rate changes
sqlBuilder.append(
- " left join m_product_loan_floating_rates pfr on ml.product_id = pfr.loan_product_id and ml.is_floating_interest_rate = 1");
+ " left join m_product_loan_floating_rates pfr on ml.product_id = pfr.loan_product_id and ml.is_floating_interest_rate = true");
sqlBuilder.append(" left join m_floating_rates fr on pfr.floating_rates_id = fr.id");
sqlBuilder.append(" left join m_floating_rates_periods frp on fr.id = frp.floating_rates_id ");
sqlBuilder.append(" left join m_loan_reschedule_request lrr on lrr.loan_id = ml.id");
// this is to identify the applicable rates when base rate is changed
- sqlBuilder.append(" left join m_floating_rates bfr on bfr.is_base_lending_rate = 1");
+ sqlBuilder.append(" left join m_floating_rates bfr on bfr.is_base_lending_rate = true");
sqlBuilder.append(" left join m_floating_rates_periods bfrp on bfr.id = bfrp.floating_rates_id and bfrp.created_date >= ?");
sqlBuilder.append(" WHERE ml.loan_status_id = ? ");
- sqlBuilder.append(" and ml.is_npa = 0 ");
+ sqlBuilder.append(" and ml.is_npa = false ");
sqlBuilder.append(" and ((");
sqlBuilder.append("ml.interest_recalculation_enabled = 1 ");
sqlBuilder.append(" and (ml.interest_recalcualated_on is null or ml.interest_recalcualated_on <> ?)");
@@ -1857,9 +1859,10 @@ public class LoanReadPlatformServiceImpl implements LoanReadPlatformService {
sqlBuilder.append(" and mr.duedate < ? )");
sqlBuilder.append(" or dd.expected_disburse_date < ? )) ");
sqlBuilder.append(" or (");
- sqlBuilder.append(" fr.is_active = 1 and frp.is_active = 1");
+ sqlBuilder.append(" fr.is_active = true and frp.is_active = true");
sqlBuilder.append(" and (frp.created_date >= ? or ");
- sqlBuilder.append("(bfrp.id is not null and frp.is_differential_to_base_lending_rate = 1 and frp.from_date >= bfrp.from_date)) ");
+ sqlBuilder
+ .append("(bfrp.id is not null and frp.is_differential_to_base_lending_rate = true and frp.from_date >= bfrp.from_date)) ");
sqlBuilder.append("and lrr.loan_id is null");
sqlBuilder.append(" ))");
sqlBuilder.append(" group by ml.id");
@@ -1884,26 +1887,27 @@ public class LoanReadPlatformServiceImpl implements LoanReadPlatformService {
sqlBuilder.append(" LEFT JOIN m_loan_disbursement_detail dd on dd.loan_id=ml.id and dd.disbursedon_date is null ");
// For Floating rate changes
sqlBuilder.append(
- " left join m_product_loan_floating_rates pfr on ml.product_id = pfr.loan_product_id and ml.is_floating_interest_rate = 1");
+ " left join m_product_loan_floating_rates pfr on ml.product_id = pfr.loan_product_id and ml.is_floating_interest_rate = true");
sqlBuilder.append(" left join m_floating_rates fr on pfr.floating_rates_id = fr.id");
sqlBuilder.append(" left join m_floating_rates_periods frp on fr.id = frp.floating_rates_id ");
sqlBuilder.append(" left join m_loan_reschedule_request lrr on lrr.loan_id = ml.id");
// this is to identify the applicable rates when base rate is changed
- sqlBuilder.append(" left join m_floating_rates bfr on bfr.is_base_lending_rate = 1");
+ sqlBuilder.append(" left join m_floating_rates bfr on bfr.is_base_lending_rate = true");
sqlBuilder.append(" left join m_floating_rates_periods bfrp on bfr.id = bfrp.floating_rates_id and bfrp.created_date >= ?");
sqlBuilder.append(" WHERE ml.loan_status_id = ? ");
- sqlBuilder.append(" and ml.is_npa = 0 ");
+ sqlBuilder.append(" and ml.is_npa = false ");
sqlBuilder.append(" and ((");
- sqlBuilder.append("ml.interest_recalculation_enabled = 1 ");
+ sqlBuilder.append("ml.interest_recalculation_enabled = true ");
sqlBuilder.append(" and (ml.interest_recalcualated_on is null or ml.interest_recalcualated_on <> ?)");
sqlBuilder.append(" and ((");
sqlBuilder.append(" mr.completed_derived is false ");
sqlBuilder.append(" and mr.duedate < ? )");
sqlBuilder.append(" or dd.expected_disburse_date < ? )) ");
sqlBuilder.append(" or (");
- sqlBuilder.append(" fr.is_active = 1 and frp.is_active = 1");
+ sqlBuilder.append(" fr.is_active = true and frp.is_active = true");
sqlBuilder.append(" and (frp.created_date >= ? or ");
- sqlBuilder.append("(bfrp.id is not null and frp.is_differential_to_base_lending_rate = 1 and frp.from_date >= bfrp.from_date)) ");
+ sqlBuilder
+ .append("(bfrp.id is not null and frp.is_differential_to_base_lending_rate = true and frp.from_date >= bfrp.from_date)) ");
sqlBuilder.append("and lrr.loan_id is null");
sqlBuilder.append(" ))");
sqlBuilder.append(" and ml.id >= ? and o.hierarchy like ? ");
@@ -1928,7 +1932,7 @@ public class LoanReadPlatformServiceImpl implements LoanReadPlatformService {
final LoanTransactionDerivedComponentMapper rm = new LoanTransactionDerivedComponentMapper();
final String sql = "select " + rm.schema()
- + " where tr.loan_id = ? and tr.transaction_type_enum = ? and tr.is_reversed=0 order by tr.transaction_date ASC,id ";
+ + " where tr.loan_id = ? and tr.transaction_type_enum = ? and tr.is_reversed=false order by tr.transaction_date ASC,id ";
return this.jdbcTemplate.query(sql, rm, new Object[] { loanId, LoanTransactionType.WAIVE_INTEREST.getValue() });
} catch (final EmptyResultDataAccessException e) {
return null;
@@ -2068,14 +2072,14 @@ public class LoanReadPlatformServiceImpl implements LoanReadPlatformService {
public PaidInAdvanceData retrieveTotalPaidInAdvance(Long loanId) {
// TODO Auto-generated method stub
try {
- final String sql = " select (SUM(ifnull(mr.principal_completed_derived, 0)) +"
- + " + SUM(ifnull(mr.interest_completed_derived, 0)) " + " + SUM(ifnull(mr.fee_charges_completed_derived, 0)) "
- + " + SUM(ifnull(mr.penalty_charges_completed_derived, 0))) as total_in_advance_derived "
+ final String sql = " select (SUM(COALESCE(mr.principal_completed_derived, 0)) +"
+ + " + SUM(COALESCE(mr.interest_completed_derived, 0)) " + " + SUM(COALESCE(mr.fee_charges_completed_derived, 0)) "
+ + " + SUM(COALESCE(mr.penalty_charges_completed_derived, 0))) as total_in_advance_derived "
+ " from m_loan ml INNER JOIN m_loan_repayment_schedule mr on mr.loan_id = ml.id "
+ " where ml.id=? and mr.duedate >= CURDATE() group by ml.id having "
- + " (SUM(ifnull(mr.principal_completed_derived, 0)) " + " + SUM(ifnull(mr.interest_completed_derived, 0)) "
- + " + SUM(ifnull(mr.fee_charges_completed_derived, 0)) "
- + "+ SUM(ifnull(mr.penalty_charges_completed_derived, 0))) > 0";
+ + " (SUM(COALESCE(mr.principal_completed_derived, 0)) " + " + SUM(COALESCE(mr.interest_completed_derived, 0)) "
+ + " + SUM(COALESCE(mr.fee_charges_completed_derived, 0)) "
+ + "+ SUM(COALESCE(mr.penalty_charges_completed_derived, 0))) > 0";
BigDecimal bigDecimal = this.jdbcTemplate.queryForObject(sql, BigDecimal.class, loanId);
return new PaidInAdvanceData(bigDecimal);
} catch (DataAccessException e) {
@@ -2155,11 +2159,11 @@ public class LoanReadPlatformServiceImpl implements LoanReadPlatformService {
@Override
public Collection<Long> retrieveLoanIdsWithPendingIncomePostingTransactions() {
StringBuilder sqlBuilder = new StringBuilder().append(" select distinct loan.id ").append(" from m_loan as loan ").append(
- " inner join m_loan_recalculation_details as recdet on (recdet.loan_id = loan.id and recdet.is_compounding_to_be_posted_as_transaction is not null and recdet.is_compounding_to_be_posted_as_transaction = 1) ")
+ " inner join m_loan_recalculation_details as recdet on (recdet.loan_id = loan.id and recdet.is_compounding_to_be_posted_as_transaction is not null and recdet.is_compounding_to_be_posted_as_transaction = true) ")
.append(" inner join m_loan_repayment_schedule as repsch on repsch.loan_id = loan.id ")
.append(" inner join m_loan_interest_recalculation_additional_details as adddet on adddet.loan_repayment_schedule_id = repsch.id ")
- .append(" left join m_loan_transaction as trans on (trans.is_reversed <> 1 and trans.transaction_type_enum = 19 and trans.loan_id = loan.id and trans.transaction_date = adddet.effective_date) ")
- .append(" where loan.loan_status_id = 300 ").append(" and loan.is_npa = 0 ")
+ .append(" left join m_loan_transaction as trans on (trans.is_reversed <> true and trans.transaction_type_enum = 19 and trans.loan_id = loan.id and trans.transaction_date = adddet.effective_date) ")
+ .append(" where loan.loan_status_id = 300 ").append(" and loan.is_npa = false ")
.append(" and adddet.effective_date is not null ").append(" and trans.transaction_date is null ")
.append(" and adddet.effective_date < ? ");
try {
@@ -2223,21 +2227,22 @@ public class LoanReadPlatformServiceImpl implements LoanReadPlatformService {
public String schema() {
StringBuilder sqlBuilder = new StringBuilder();
- sqlBuilder.append("if(max(tr.transaction_date)>ls.dueDate,max(tr.transaction_date),ls.dueDate) as transactionDate, ");
sqlBuilder.append(
- "ls.principal_amount - IFNULL(ls.principal_writtenoff_derived,0) - IFNULL(ls.principal_completed_derived,0) as principalDue, ");
+ "(CASE WHEN max(tr.transaction_date)>ls.dueDate THEN max(tr.transaction_date) ELSE ls.dueDate END) as transactionDate, ");
+ sqlBuilder.append(
+ "ls.principal_amount - coalesce(ls.principal_writtenoff_derived,0) - coalesce(ls.principal_completed_derived,0) as principalDue, ");
sqlBuilder.append(
- "ls.interest_amount - IFNULL(ls.interest_completed_derived,0) - IFNULL(ls.interest_waived_derived,0) - IFNULL(ls.interest_writtenoff_derived,0) as interestDue, ");
+ "ls.interest_amount - coalesce(ls.interest_completed_derived,0) - coalesce(ls.interest_waived_derived,0) - coalesce(ls.interest_writtenoff_derived,0) as interestDue, ");
sqlBuilder.append(
- "ls.fee_charges_amount - IFNULL(ls.fee_charges_completed_derived,0) - IFNULL(ls.fee_charges_writtenoff_derived,0) - IFNULL(ls.fee_charges_waived_derived,0) as feeDue, ");
+ "ls.fee_charges_amount - coalesce(ls.fee_charges_completed_derived,0) - coalesce(ls.fee_charges_writtenoff_derived,0) - coalesce(ls.fee_charges_waived_derived,0) as feeDue, ");
sqlBuilder.append(
- "ls.penalty_charges_amount - IFNULL(ls.penalty_charges_completed_derived,0) - IFNULL(ls.penalty_charges_writtenoff_derived,0) - IFNULL(ls.penalty_charges_waived_derived,0) as penaltyDue, ");
+ "ls.penalty_charges_amount - coalesce(ls.penalty_charges_completed_derived,0) - coalesce(ls.penalty_charges_writtenoff_derived,0) - coalesce(ls.penalty_charges_waived_derived,0) as penaltyDue, ");
sqlBuilder.append(
"l.currency_code as currencyCode, l.currency_digits as currencyDigits, l.currency_multiplesof as inMultiplesOf, l.net_disbursal_amount as netDisbursalAmount, rc.`name` as currencyName, ");
sqlBuilder.append("rc.display_symbol as currencyDisplaySymbol, rc.internationalized_name_code as currencyNameCode ");
sqlBuilder.append("FROM m_loan l ");
sqlBuilder.append(
- "LEFT JOIN m_loan_transaction tr ON tr.loan_id = l.id AND tr.transaction_type_enum = ? and tr.is_reversed = 0 ");
+ "LEFT JOIN m_loan_transaction tr ON tr.loan_id = l.id AND tr.transaction_type_enum = ? and tr.is_reversed = false ");
sqlBuilder.append("join m_currency rc on rc.`code` = l.currency_code ");
sqlBuilder.append("JOIN m_loan_repayment_schedule ls ON ls.loan_id = l.id AND ls.completed_derived = 0 ");
sqlBuilder.append("join( ");
diff --git a/fineract-provider/src/main/java/org/apache/fineract/portfolio/loanaccount/service/LoanWritePlatformServiceJpaRepositoryImpl.java b/fineract-provider/src/main/java/org/apache/fineract/portfolio/loanaccount/service/LoanWritePlatformServiceJpaRepositoryImpl.java
index 51cca57..7389c9d 100644
--- a/fineract-provider/src/main/java/org/apache/fineract/portfolio/loanaccount/service/LoanWritePlatformServiceJpaRepositoryImpl.java
+++ b/fineract-provider/src/main/java/org/apache/fineract/portfolio/loanaccount/service/LoanWritePlatformServiceJpaRepositoryImpl.java
@@ -987,7 +987,7 @@ public class LoanWritePlatformServiceJpaRepositoryImpl implements LoanWritePlatf
ClientCollateralManagement clientCollateralManagement = loanCollateralManagement.getClientCollateralManagement();
if (loan.status().isClosed()) {
- loanCollateralManagement.setIsReleased(Integer.valueOf(1));
+ loanCollateralManagement.setIsReleased(true);
BigDecimal quantity = loanCollateralManagement.getQuantity();
clientCollateralManagement.updateQuantity(clientCollateralManagement.getQuantity().add(quantity));
loanCollateralManagement.setClientCollateralManagement(clientCollateralManagement);
@@ -1402,7 +1402,7 @@ public class LoanWritePlatformServiceJpaRepositoryImpl implements LoanWritePlatf
ClientCollateralManagement clientCollateralManagement = loanCollateralManagement.getClientCollateralManagement();
if (loan.status().isClosed()) {
- loanCollateralManagement.setIsReleased(Integer.valueOf(1));
+ loanCollateralManagement.setIsReleased(true);
BigDecimal quantity = loanCollateralManagement.getQuantity();
clientCollateralManagement.updateQuantity(clientCollateralManagement.getQuantity().add(quantity));
loanCollateralManagement.setClientCollateralManagement(clientCollateralManagement);
@@ -1469,7 +1469,7 @@ public class LoanWritePlatformServiceJpaRepositoryImpl implements LoanWritePlatf
ClientCollateralManagement clientCollateralManagement = loanCollateralManagement.getClientCollateralManagement();
if (loan.status().isClosed()) {
- loanCollateralManagement.setIsReleased(Integer.valueOf(1));
+ loanCollateralManagement.setIsReleased(true);
BigDecimal quantity = loanCollateralManagement.getQuantity();
clientCollateralManagement.updateQuantity(clientCollateralManagement.getQuantity().add(quantity));
loanCollateralManagement.setClientCollateralManagement(clientCollateralManagement);