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 <[email protected]>
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) {
