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 16:33:45 UTC
[fineract] branch develop updated: FINERACT-984: Query changes for database independence
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 088df46 FINERACT-984: Query changes for database independence
088df46 is described below
commit 088df46a49bd148e577f49a6fbc69523d944b131
Author: Arnold Galovics <ga...@gmail.com>
AuthorDate: Sun Feb 20 15:16:44 2022 +0100
FINERACT-984: Query changes for database independence
---
...ntInterestRateChartReadPlatformServiceImpl.java | 25 ++++++++++-------
.../DepositAccountReadPlatformServiceImpl.java | 16 +++++------
.../service/GSIMReadPlatformServiceImpl.java | 6 ++---
...avingsAccountChargeReadPlatformServiceImpl.java | 6 ++---
.../SavingsAccountReadPlatformServiceImpl.java | 31 +++++++++-------------
.../service/SearchReadPlatformServiceImpl.java | 10 +++----
...elfBeneficiariesTPTReadPlatformServiceImpl.java | 8 +++---
.../PurchasedSharesReadPlatformServiceImpl.java | 2 +-
.../ShareAccountChargeReadPlatformServiceImpl.java | 4 +--
.../ShareAccountReadPlatformServiceImpl.java | 10 ++++++-
10 files changed, 62 insertions(+), 56 deletions(-)
diff --git a/fineract-provider/src/main/java/org/apache/fineract/portfolio/savings/service/DepositAccountInterestRateChartReadPlatformServiceImpl.java b/fineract-provider/src/main/java/org/apache/fineract/portfolio/savings/service/DepositAccountInterestRateChartReadPlatformServiceImpl.java
index 639c374..1ecf513 100644
--- a/fineract-provider/src/main/java/org/apache/fineract/portfolio/savings/service/DepositAccountInterestRateChartReadPlatformServiceImpl.java
+++ b/fineract-provider/src/main/java/org/apache/fineract/portfolio/savings/service/DepositAccountInterestRateChartReadPlatformServiceImpl.java
@@ -19,6 +19,7 @@
package org.apache.fineract.portfolio.savings.service;
import java.math.BigDecimal;
+import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.time.LocalDate;
@@ -94,13 +95,13 @@ public class DepositAccountInterestRateChartReadPlatformServiceImpl implements D
sql.append(this.chartExtractor.schema());
sql.append(" where irc.id = ? order by irc.id asc, ");
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");
Collection<DepositAccountInterestRateChartData> chartDatas = this.jdbcTemplate.query(sql.toString(), this.chartExtractor,
new Object[] { chartId });
@@ -140,17 +141,21 @@ public class DepositAccountInterestRateChartReadPlatformServiceImpl implements D
sql.append(this.chartExtractor.schema());
sql.append(" where irc.savings_account_id = ? order by irc.id asc, ");
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");
- Collection<DepositAccountInterestRateChartData> chartDatas = this.jdbcTemplate.query(sql.toString(), this.chartExtractor,
- new Object[] { accountId });
+ Collection<DepositAccountInterestRateChartData> chartDatas = this.jdbcTemplate.query(con -> {
+ PreparedStatement preparedStatement = con.prepareStatement(sql.toString(), ResultSet.TYPE_SCROLL_SENSITIVE,
+ ResultSet.CONCUR_UPDATABLE);
+ preparedStatement.setLong(1, accountId);
+ return preparedStatement;
+ }, this.chartExtractor);
if (chartDatas == null || chartDatas.isEmpty()) {
throw new DepositAccountInterestRateChartNotFoundException(accountId);
}
diff --git a/fineract-provider/src/main/java/org/apache/fineract/portfolio/savings/service/DepositAccountReadPlatformServiceImpl.java b/fineract-provider/src/main/java/org/apache/fineract/portfolio/savings/service/DepositAccountReadPlatformServiceImpl.java
index 59bafad..4018a7c 100644
--- a/fineract-provider/src/main/java/org/apache/fineract/portfolio/savings/service/DepositAccountReadPlatformServiceImpl.java
+++ b/fineract-provider/src/main/java/org/apache/fineract/portfolio/savings/service/DepositAccountReadPlatformServiceImpl.java
@@ -507,11 +507,11 @@ public class DepositAccountReadPlatformServiceImpl implements DepositAccountRead
AccountTransferMapper mapper = new AccountTransferMapper();
sqlBuilder.append("SELECT ");
sqlBuilder.append(mapper.schema());
- sqlBuilder.append(" where da.transfer_interest_to_linked_account = 1 and ");
+ sqlBuilder.append(" where da.transfer_interest_to_linked_account = true and ");
sqlBuilder.append(
- "st.transaction_date > (select IFNULL(max(sat.transaction_date),sa.activatedon_date) from m_savings_account_transaction sat where sat.transaction_type_enum = ? and sat.savings_account_id = sa.id and sat.is_reversed=0) ");
+ "st.transaction_date > (select coalesce(max(sat.transaction_date),sa.activatedon_date) from m_savings_account_transaction sat where sat.transaction_type_enum = ? and sat.savings_account_id = sa.id and sat.is_reversed=false) ");
sqlBuilder.append(
- "and st.transaction_type_enum = ? and sa.status_enum = ? and st.is_reversed=0 and st.transaction_date > IFNULL(sa.lockedin_until_date_derived,sa.activatedon_date)");
+ "and st.transaction_type_enum = ? and sa.status_enum = ? and st.is_reversed=false and st.transaction_date > coalesce(sa.lockedin_until_date_derived,sa.activatedon_date)");
return this.jdbcTemplate.query(sqlBuilder.toString(), mapper, new Object[] { SavingsAccountTransactionType.WITHDRAWAL.getValue(),
SavingsAccountTransactionType.INTEREST_POSTING.getValue(), SavingsAccountStatusType.ACTIVE.getValue() });
@@ -531,7 +531,7 @@ public class DepositAccountReadPlatformServiceImpl implements DepositAccountRead
sb.append(" inner join m_calendar mc on mc.id = mci.calendar_id and mc.calendar_type_enum = ?");
sb.append(" inner join m_mandatory_savings_schedule ms on ms.savings_account_id = dat.savings_account_id and ms.duedate > ?");
sb.append(" where dat.deposit_period is null");
- sb.append(" group by ms.savings_account_id, rd.mandatory_recommended_deposit_amount, mc.recurrence");
+ sb.append(" group by ms.savings_account_id, rd.mandatory_recommended_deposit_amount, mc.recurrence, rd.savings_account_id");
return this.jdbcTemplate.queryForList(sb.toString(), SavingsAccountStatusType.ACTIVE.getValue(),
CalendarEntityType.SAVINGS.getValue(), CalendarType.COLLECTION.getValue(),
@@ -1425,14 +1425,14 @@ public class DepositAccountReadPlatformServiceImpl implements DepositAccountRead
sqlBuilder.append("curr.name as currencyName, curr.internationalized_name_code as currencyNameCode, ");
sqlBuilder.append("curr.display_symbol as currencyDisplaySymbol, ");
sqlBuilder.append("sa.account_balance_derived as runningBalance, ");
- sqlBuilder
- .append("mss.duedate as duedate, (mss.deposit_amount - ifnull(mss.deposit_amount_completed_derived,0)) as dueamount, ");
- sqlBuilder.append("IFNULL(sac.amount_outstanding_derived,0.0) AS outstandingChargeAmount ");
+ sqlBuilder.append(
+ "mss.duedate as duedate, (mss.deposit_amount - coalesce(mss.deposit_amount_completed_derived,0)) as dueamount, ");
+ sqlBuilder.append("coalesce(sac.amount_outstanding_derived,0.0) AS outstandingChargeAmount ");
sqlBuilder.append("from m_savings_account sa ");
sqlBuilder.append("join m_mandatory_savings_schedule mss on mss.savings_account_id=sa.id and mss.completed_derived = false ");
sqlBuilder.append("join m_currency curr on curr.code = sa.currency_code ");
sqlBuilder.append("LEFT JOIN(SELECT s.savings_account_id AS savings_account_id ");
- sqlBuilder.append(",SUM(IFNULL(s.amount_outstanding_derived,0.0)) AS amount_outstanding_derived ");
+ sqlBuilder.append(",SUM(COALESCE(s.amount_outstanding_derived,0.0)) AS amount_outstanding_derived ");
sqlBuilder.append("FROM m_savings_account_charge s ");
sqlBuilder.append("JOIN m_charge c ON c.id = s.charge_id AND c.charge_time_enum = 3 ");
sqlBuilder.append("WHERE s.savings_account_id = ? ");
diff --git a/fineract-provider/src/main/java/org/apache/fineract/portfolio/savings/service/GSIMReadPlatformServiceImpl.java b/fineract-provider/src/main/java/org/apache/fineract/portfolio/savings/service/GSIMReadPlatformServiceImpl.java
index f6421d8..72ea8ae 100644
--- a/fineract-provider/src/main/java/org/apache/fineract/portfolio/savings/service/GSIMReadPlatformServiceImpl.java
+++ b/fineract-provider/src/main/java/org/apache/fineract/portfolio/savings/service/GSIMReadPlatformServiceImpl.java
@@ -210,7 +210,7 @@ public class GSIMReadPlatformServiceImpl implements GSIMReadPlatformService {
final GSIMMapper rm = new GSIMMapper();
final String sql = "select " + rm.schema() + " where gsim.group_id=?";
- return this.jdbcTemplate.query(sql, rm, new Object[] { groupId });
+ return this.jdbcTemplate.query(sql, rm, new Object[] { Long.parseLong(groupId) });
}
@Override
@@ -273,9 +273,9 @@ public class GSIMReadPlatformServiceImpl implements GSIMReadPlatformService {
accountsSummary.append("avbu.firstname as activatedByFirstname, avbu.lastname as activatedByLastname,");
accountsSummary.append("sa.sub_status_enum as subStatusEnum, ");
- accountsSummary.append("(select IFNULL(max(sat.transaction_date),sa.activatedon_date) ");
+ accountsSummary.append("(select coalesce(max(sat.transaction_date),sa.activatedon_date) ");
accountsSummary.append("from m_savings_account_transaction as sat ");
- accountsSummary.append("where sat.is_reversed = 0 ");
+ accountsSummary.append("where sat.is_reversed = false ");
accountsSummary.append("and sat.transaction_type_enum in (1,2) ");
accountsSummary.append("and sat.savings_account_id = sa.id) as lastActiveTransactionDate, ");
diff --git a/fineract-provider/src/main/java/org/apache/fineract/portfolio/savings/service/SavingsAccountChargeReadPlatformServiceImpl.java b/fineract-provider/src/main/java/org/apache/fineract/portfolio/savings/service/SavingsAccountChargeReadPlatformServiceImpl.java
index 0d68fc0..c921455 100644
--- a/fineract-provider/src/main/java/org/apache/fineract/portfolio/savings/service/SavingsAccountChargeReadPlatformServiceImpl.java
+++ b/fineract-provider/src/main/java/org/apache/fineract/portfolio/savings/service/SavingsAccountChargeReadPlatformServiceImpl.java
@@ -206,9 +206,9 @@ public class SavingsAccountChargeReadPlatformServiceImpl implements SavingsAccou
final StringBuilder sqlBuilder = new StringBuilder();
sqlBuilder.append("select ").append(rm.schema()).append(" where sc.savings_account_id=? ");
if (status.equalsIgnoreCase("active")) {
- sqlBuilder.append(" and sc.is_active = 1 ");
+ sqlBuilder.append(" and sc.is_active = true ");
} else if (status.equalsIgnoreCase("inactive")) {
- sqlBuilder.append(" and sc.is_active = 0 ");
+ sqlBuilder.append(" and sc.is_active = false ");
}
sqlBuilder.append(" order by sc.charge_time_enum ASC, sc.charge_due_date ASC, sc.is_penalty ASC");
@@ -259,7 +259,7 @@ public class SavingsAccountChargeReadPlatformServiceImpl implements SavingsAccou
@Override
public Collection<SavingsAccountAnnualFeeData> retrieveChargesWithDue() {
final String sql = "select " + this.chargeDueMapper.schema()
- + " where sac.charge_due_date is not null and sac.charge_due_date <= NOW() and sac.waived = 0 and sac.is_paid_derived=0 and sac.is_active=1 and sa.status_enum = ? "
+ + " where sac.charge_due_date is not null and sac.charge_due_date <= NOW() and sac.waived = false and sac.is_paid_derived=false and sac.is_active=true and sa.status_enum = ? "
+ " order by sac.charge_due_date ";
return this.jdbcTemplate.query(sql, this.chargeDueMapper, new Object[] { SavingsAccountStatusType.ACTIVE.getValue() });
diff --git a/fineract-provider/src/main/java/org/apache/fineract/portfolio/savings/service/SavingsAccountReadPlatformServiceImpl.java b/fineract-provider/src/main/java/org/apache/fineract/portfolio/savings/service/SavingsAccountReadPlatformServiceImpl.java
index 26e041d..14504c9 100644
--- a/fineract-provider/src/main/java/org/apache/fineract/portfolio/savings/service/SavingsAccountReadPlatformServiceImpl.java
+++ b/fineract-provider/src/main/java/org/apache/fineract/portfolio/savings/service/SavingsAccountReadPlatformServiceImpl.java
@@ -268,7 +268,7 @@ public class SavingsAccountReadPlatformServiceImpl implements SavingsAccountRead
+ "join (select a.id from m_savings_account a where a.id > ? and a.status_enum = ? limit ?) b on b.id = sa.id ";
if (backdatedTxnsAllowedTill) {
sql = sql
- + "where if (sa.interest_posted_till_date is not null, tr.transaction_date >= sa.interest_posted_till_date, tr.transaction_date >= sa.activatedon_date) ";
+ + "where (CASE WHEN sa.interest_posted_till_date is not null THEN tr.transaction_date >= sa.interest_posted_till_date ELSE tr.transaction_date >= sa.activatedon_date END) ";
}
sql = sql + "and apm.product_type=2 and sa.interest_posted_till_date<" + java.sql.Date.valueOf(currentDate);
@@ -295,20 +295,13 @@ public class SavingsAccountReadPlatformServiceImpl implements SavingsAccountRead
sqlBuilder.append("sa.status_enum as statusEnum, ");
sqlBuilder.append("sa.sub_status_enum as subStatusEnum, ");
sqlBuilder.append("sa.submittedon_date as submittedOnDate,");
-
sqlBuilder.append("sa.rejectedon_date as rejectedOnDate,");
-
sqlBuilder.append("sa.withdrawnon_date as withdrawnOnDate,");
-
sqlBuilder.append("sa.approvedon_date as approvedOnDate,");
-
sqlBuilder.append("sa.activatedon_date as activatedOnDate,");
-
sqlBuilder.append("sa.closedon_date as closedOnDate,");
-
sqlBuilder.append(
"sa.currency_code as currencyCode, sa.currency_digits as currencyDigits, sa.currency_multiplesof as inMultiplesOf, ");
-
sqlBuilder.append("sa.nominal_annual_interest_rate as nominalAnnualInterestRate, ");
sqlBuilder.append("sa.interest_compounding_period_enum as interestCompoundingPeriodType, ");
sqlBuilder.append("sa.interest_posting_period_enum as interestPostingPeriodType, ");
@@ -342,9 +335,9 @@ public class SavingsAccountReadPlatformServiceImpl implements SavingsAccountRead
sqlBuilder.append("sa.total_savings_amount_on_hold as onHoldAmount, ");
sqlBuilder.append("sa.interest_posted_till_date as interestPostedTillDate, ");
sqlBuilder.append("tg.id as taxGroupId, ");
- sqlBuilder.append("(select IFNULL(max(sat.transaction_date),sa.activatedon_date) ");
+ sqlBuilder.append("(select COALESCE(max(sat.transaction_date),sa.activatedon_date) ");
sqlBuilder.append("from m_savings_account_transaction as sat ");
- sqlBuilder.append("where sat.is_reversed = 0 ");
+ sqlBuilder.append("where sat.is_reversed = false ");
sqlBuilder.append("and sat.transaction_type_enum in (1,2) ");
sqlBuilder.append("and sat.savings_account_id = sa.id) as lastActiveTransactionDate, ");
sqlBuilder.append("sp.id as productId, ");
@@ -821,9 +814,9 @@ public class SavingsAccountReadPlatformServiceImpl implements SavingsAccountRead
sqlBuilder.append("sa.total_savings_amount_on_hold as onHoldAmount, ");
sqlBuilder.append("sa.withdrawal_fee_for_transfer as withdrawalFeeForTransfers, ");
sqlBuilder.append("tg.id as taxGroupId, tg.name as taxGroupName, ");
- sqlBuilder.append("(select IFNULL(max(sat.transaction_date),sa.activatedon_date) ");
+ sqlBuilder.append("(select COALESCE(max(sat.transaction_date),sa.activatedon_date) ");
sqlBuilder.append("from m_savings_account_transaction as sat ");
- sqlBuilder.append("where sat.is_reversed = 0 ");
+ sqlBuilder.append("where sat.is_reversed = false ");
sqlBuilder.append("and sat.transaction_type_enum in (1,2) ");
sqlBuilder.append("and sat.savings_account_id = sa.id) as lastActiveTransactionDate, ");
sqlBuilder.append("sp.is_dormancy_tracking_active as isDormancyTrackingActive, ");
@@ -1663,9 +1656,9 @@ public class SavingsAccountReadPlatformServiceImpl implements SavingsAccountRead
sql.append(" inner join m_savings_product as sp on (sa.product_id = sp.id and sp.is_dormancy_tracking_active = 1) ");
sql.append(" where sa.status_enum = 300 ");
sql.append(" and sa.sub_status_enum = 0 ");
- sql.append(" and DATEDIFF(?,(select IFNULL(max(sat.transaction_date),sa.activatedon_date) ");
+ sql.append(" and DATEDIFF(?,(select COALESCE(max(sat.transaction_date), sa.activatedon_date) ");
sql.append(" from m_savings_account_transaction as sat ");
- sql.append(" where sat.is_reversed = 0 ");
+ sql.append(" where sat.is_reversed = false ");
sql.append(" and sat.transaction_type_enum in (1,2) ");
sql.append(" and sat.savings_account_id = sa.id)) >= sp.days_to_inactive ");
@@ -1688,9 +1681,9 @@ public class SavingsAccountReadPlatformServiceImpl implements SavingsAccountRead
sql.append(" inner join m_savings_product as sp on (sa.product_id = sp.id and sp.is_dormancy_tracking_active = 1) ");
sql.append(" where sa.status_enum = 300 ");
sql.append(" and sa.sub_status_enum = 100 ");
- sql.append(" and DATEDIFF(?,(select IFNULL(max(sat.transaction_date),sa.activatedon_date) ");
+ sql.append(" and DATEDIFF(?,(select COALESCE(max(sat.transaction_date),sa.activatedon_date) ");
sql.append(" from m_savings_account_transaction as sat ");
- sql.append(" where sat.is_reversed = 0 ");
+ sql.append(" where sat.is_reversed = false ");
sql.append(" and sat.transaction_type_enum in (1,2) ");
sql.append(" and sat.savings_account_id = sa.id)) >= sp.days_to_dormancy ");
@@ -1710,12 +1703,12 @@ public class SavingsAccountReadPlatformServiceImpl implements SavingsAccountRead
List<Long> ret = null;
StringBuilder sql = new StringBuilder("select sa.id ");
sql.append(" from m_savings_account as sa ");
- sql.append(" inner join m_savings_product as sp on (sa.product_id = sp.id and sp.is_dormancy_tracking_active = 1) ");
+ sql.append(" inner join m_savings_product as sp on (sa.product_id = sp.id and sp.is_dormancy_tracking_active = true) ");
sql.append(" where sa.status_enum = 300 ");
sql.append(" and sa.sub_status_enum = 200 ");
- sql.append(" and DATEDIFF(?,(select IFNULL(max(sat.transaction_date),sa.activatedon_date) ");
+ sql.append(" and DATEDIFF(?,(select COALESCE(max(sat.transaction_date),sa.activatedon_date) ");
sql.append(" from m_savings_account_transaction as sat ");
- sql.append(" where sat.is_reversed = 0 ");
+ sql.append(" where sat.is_reversed = false ");
sql.append(" and sat.transaction_type_enum in (1,2) ");
sql.append(" and sat.savings_account_id = sa.id)) >= sp.days_to_escheat ");
diff --git a/fineract-provider/src/main/java/org/apache/fineract/portfolio/search/service/SearchReadPlatformServiceImpl.java b/fineract-provider/src/main/java/org/apache/fineract/portfolio/search/service/SearchReadPlatformServiceImpl.java
index a298dde..f0bf5b3 100644
--- a/fineract-provider/src/main/java/org/apache/fineract/portfolio/search/service/SearchReadPlatformServiceImpl.java
+++ b/fineract-provider/src/main/java/org/apache/fineract/portfolio/search/service/SearchReadPlatformServiceImpl.java
@@ -96,11 +96,11 @@ public class SearchReadPlatformServiceImpl implements SearchReadPlatformService
+ " from m_client c join m_office o on o.id = c.office_id where o.hierarchy like :hierarchy and (c.account_no like :search or c.display_name like :search or c.external_id like :search or c.mobile_no like :search)) ";
final String loanMatchSql = " (select 'LOAN' as entityType, l.id as entityId, pl.name as entityName, l.external_id as entityExternalId, l.account_no as entityAccountNo "
- + " , IFNULL(c.id,g.id) as parentId, IFNULL(c.display_name,g.display_name) as parentName, null as entityMobileNo, l.loan_status_id as entityStatusEnum, null as subEntityType, IF(g.id is null, 'client', 'group') as parentType "
+ + " , coalesce(c.id,g.id) as parentId, coalesce(c.display_name,g.display_name) as parentName, null as entityMobileNo, l.loan_status_id as entityStatusEnum, null as subEntityType, CASE WHEN g.id is null THEN 'client' ELSE 'group' END as parentType "
+ " from m_loan l left join m_client c on l.client_id = c.id left join m_group g ON l.group_id = g.id left join m_office o on o.id = c.office_id left join m_product_loan pl on pl.id=l.product_id where (o.hierarchy IS NULL OR o.hierarchy like :hierarchy) and (l.account_no like :search or l.external_id like :search)) ";
final String savingMatchSql = " (select 'SAVING' as entityType, s.id as entityId, sp.name as entityName, s.external_id as entityExternalId, s.account_no as entityAccountNo "
- + " , IFNULL(c.id,g.id) as parentId, IFNULL(c.display_name,g.display_name) as parentName, null as entityMobileNo, s.status_enum as entityStatusEnum, s.deposit_type_enum as subEntityType,IF(g.id is null, 'client', 'group') as parentType "
+ + " , coalesce(c.id,g.id) as parentId, coalesce(c.display_name,g.display_name) as parentName, null as entityMobileNo, s.status_enum as entityStatusEnum, s.deposit_type_enum as subEntityType, CASE WHEN g.id is null THEN 'client' ELSE 'group' END as parentType "
+ " from m_savings_account s left join m_client c on s.client_id = c.id left join m_group g ON s.group_id = g.id left join m_office o on o.id = c.office_id left join m_savings_product sp on sp.id=s.product_id "
+ " where (o.hierarchy IS NULL OR o.hierarchy like :hierarchy) and (s.account_no like :search or s.external_id like :search)) ";
@@ -217,9 +217,9 @@ public class SearchReadPlatformServiceImpl implements SearchReadPlatformService
final StringBuilder sql = new StringBuilder();
sql.append(
"Select a.name as officeName, a.Product as productName, a.cnt as 'count', a.outstandingAmt as outstanding, a.percentOut as percentOut ")
- .append("from (select mo.name, mp.name Product, sum(ifnull(ml.total_expected_repayment_derived,0.0)) TotalAmt, count(*) cnt, ")
- .append("sum(ifnull(ml.total_outstanding_derived,0.0)) outstandingAmt, ")
- .append("(sum(ifnull(ml.total_outstanding_derived,0.0)) * 100 / sum(ifnull(ml.total_expected_repayment_derived,0.0))) percentOut ")
+ .append("from (select mo.name, mp.name Product, SUM(COALESCE(ml.total_expected_repayment_derived,0.0)) TotalAmt, count(*) cnt, ")
+ .append("SUM(COALESCE(ml.total_outstanding_derived,0.0)) outstandingAmt, ")
+ .append("(SUM(COALESCE(ml.total_outstanding_derived,0.0)) * 100 / SUM(COALESCE(ml.total_expected_repayment_derived,0.0))) percentOut ")
.append("from m_loan ml inner join m_product_loan mp on mp.id=ml.product_id ")
.append("inner join m_client mc on mc.id=ml.client_id ").append("inner join m_office mo on mo.id=mc.office_id ");
diff --git a/fineract-provider/src/main/java/org/apache/fineract/portfolio/self/account/service/SelfBeneficiariesTPTReadPlatformServiceImpl.java b/fineract-provider/src/main/java/org/apache/fineract/portfolio/self/account/service/SelfBeneficiariesTPTReadPlatformServiceImpl.java
index aeddb14..fd9b8c7 100644
--- a/fineract-provider/src/main/java/org/apache/fineract/portfolio/self/account/service/SelfBeneficiariesTPTReadPlatformServiceImpl.java
+++ b/fineract-provider/src/main/java/org/apache/fineract/portfolio/self/account/service/SelfBeneficiariesTPTReadPlatformServiceImpl.java
@@ -78,7 +78,7 @@ public class SelfBeneficiariesTPTReadPlatformServiceImpl implements SelfBenefici
sqlBuilder.append(" inner join m_office as o on b.office_id = o.id ");
sqlBuilder.append(" inner join m_client as c on b.client_id = c.id ");
sqlBuilder.append(" inner join m_savings_account as s on b.account_id = s.id ");
- sqlBuilder.append(" where b.is_active = 1 ");
+ sqlBuilder.append(" where b.is_active = true ");
sqlBuilder.append(" and b.account_type = 2 ");
sqlBuilder.append(" and b.app_user_id = ?) ");
sqlBuilder.append(" union all ");
@@ -93,7 +93,7 @@ public class SelfBeneficiariesTPTReadPlatformServiceImpl implements SelfBenefici
sqlBuilder.append(" inner join m_office as o on b.office_id = o.id ");
sqlBuilder.append(" inner join m_client as c on b.client_id = c.id ");
sqlBuilder.append(" inner join m_loan as l on b.account_id = l.id ");
- sqlBuilder.append(" where b.is_active = 1 ");
+ sqlBuilder.append(" where b.is_active = true ");
sqlBuilder.append(" and b.account_type = 1 ");
sqlBuilder.append(" and b.app_user_id = ?) ");
@@ -136,7 +136,7 @@ public class SelfBeneficiariesTPTReadPlatformServiceImpl implements SelfBenefici
sqlBuilder.append(" inner join m_office as o on b.office_id = o.id ");
sqlBuilder.append(" inner join m_client as c on b.client_id = c.id ");
sqlBuilder.append(" inner join m_savings_account as s on b.account_id = s.id ");
- sqlBuilder.append(" where b.is_active = 1 ");
+ sqlBuilder.append(" where b.is_active = true ");
sqlBuilder.append(" and b.account_type = 2 ");
sqlBuilder.append(" and b.app_user_id = ?) ");
sqlBuilder.append(" union all ");
@@ -151,7 +151,7 @@ public class SelfBeneficiariesTPTReadPlatformServiceImpl implements SelfBenefici
sqlBuilder.append(" inner join m_office as o on b.office_id = o.id ");
sqlBuilder.append(" inner join m_client as c on b.client_id = c.id ");
sqlBuilder.append(" inner join m_loan as l on b.account_id = l.id ");
- sqlBuilder.append(" where b.is_active = 1 ");
+ sqlBuilder.append(" where b.is_active = true ");
sqlBuilder.append(" and b.account_type = 1 ");
sqlBuilder.append(" and b.app_user_id = ?) ");
diff --git a/fineract-provider/src/main/java/org/apache/fineract/portfolio/shareaccounts/service/PurchasedSharesReadPlatformServiceImpl.java b/fineract-provider/src/main/java/org/apache/fineract/portfolio/shareaccounts/service/PurchasedSharesReadPlatformServiceImpl.java
index 2730104..b1e29b1 100644
--- a/fineract-provider/src/main/java/org/apache/fineract/portfolio/shareaccounts/service/PurchasedSharesReadPlatformServiceImpl.java
+++ b/fineract-provider/src/main/java/org/apache/fineract/portfolio/shareaccounts/service/PurchasedSharesReadPlatformServiceImpl.java
@@ -45,7 +45,7 @@ public class PurchasedSharesReadPlatformServiceImpl implements PurchasedSharesRe
@Override
public Collection<ShareAccountTransactionData> retrievePurchasedShares(Long accountId) {
PurchasedSharesDataRowMapper mapper = new PurchasedSharesDataRowMapper();
- final String sql = "select " + mapper.schema() + " where saps.account_id=? and saps.is_active = 1";
+ final String sql = "select " + mapper.schema() + " where saps.account_id=? and saps.is_active = true";
return this.jdbcTemplate.query(sql, mapper, new Object[] { accountId });
}
diff --git a/fineract-provider/src/main/java/org/apache/fineract/portfolio/shareaccounts/service/ShareAccountChargeReadPlatformServiceImpl.java b/fineract-provider/src/main/java/org/apache/fineract/portfolio/shareaccounts/service/ShareAccountChargeReadPlatformServiceImpl.java
index 8de0cf6..76cb4da 100644
--- a/fineract-provider/src/main/java/org/apache/fineract/portfolio/shareaccounts/service/ShareAccountChargeReadPlatformServiceImpl.java
+++ b/fineract-provider/src/main/java/org/apache/fineract/portfolio/shareaccounts/service/ShareAccountChargeReadPlatformServiceImpl.java
@@ -50,9 +50,9 @@ public class ShareAccountChargeReadPlatformServiceImpl implements ShareAccountCh
final StringBuilder sqlBuilder = new StringBuilder();
sqlBuilder.append("select ").append(rm.schema()).append(" where sc.account_id=? ");
if (status.equalsIgnoreCase("active")) {
- sqlBuilder.append(" and sc.is_active = 1 ");
+ sqlBuilder.append(" and sc.is_active = true ");
} else if (status.equalsIgnoreCase("inactive")) {
- sqlBuilder.append(" and sc.is_active = 0 ");
+ sqlBuilder.append(" and sc.is_active = false ");
}
sqlBuilder.append(" order by sc.charge_time_enum ASC");
diff --git a/fineract-provider/src/main/java/org/apache/fineract/portfolio/shareaccounts/service/ShareAccountReadPlatformServiceImpl.java b/fineract-provider/src/main/java/org/apache/fineract/portfolio/shareaccounts/service/ShareAccountReadPlatformServiceImpl.java
index eee0cf4..5b42f6a 100644
--- a/fineract-provider/src/main/java/org/apache/fineract/portfolio/shareaccounts/service/ShareAccountReadPlatformServiceImpl.java
+++ b/fineract-provider/src/main/java/org/apache/fineract/portfolio/shareaccounts/service/ShareAccountReadPlatformServiceImpl.java
@@ -19,6 +19,7 @@
package org.apache.fineract.portfolio.shareaccounts.service;
import java.math.BigDecimal;
+import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.time.LocalDate;
@@ -232,7 +233,14 @@ public class ShareAccountReadPlatformServiceImpl implements ShareAccountReadPlat
sb.append(" and saps.status_enum = ?");
params.add(PurchasedSharesStatusType.APPROVED.getValue());
Object[] whereClauseItems = params.toArray();
- return this.jdbcTemplate.query(sb.toString(), mapper, whereClauseItems);
+ return this.jdbcTemplate.query(con -> {
+ PreparedStatement preparedStatement = con.prepareStatement(sb.toString(), ResultSet.TYPE_SCROLL_INSENSITIVE,
+ ResultSet.CONCUR_UPDATABLE);
+ for (int i = 0; i < whereClauseItems.length; i++) {
+ preparedStatement.setObject(i + 1, whereClauseItems[i]);
+ }
+ return preparedStatement;
+ }, mapper);
}
public Collection<ShareAccountChargeData> convertChargesToShareAccountCharges(Collection<ChargeData> productCharges) {