You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@fineract.apache.org by ar...@apache.org on 2022/05/26 10:01:34 UTC
[fineract] branch develop updated: Fix for queries using postgres
This is an automated email from the ASF dual-hosted git repository.
arnold pushed a commit to branch develop
in repository https://gitbox.apache.org/repos/asf/fineract.git
The following commit(s) were added to refs/heads/develop by this push:
new 5942488bd Fix for queries using postgres
5942488bd is described below
commit 5942488bde35899dac1d8939f0a1318134376d2e
Author: Jose Alberto Hernandez <al...@MacBook-Pro.local>
AuthorDate: Wed May 25 16:03:38 2022 -0500
Fix for queries using postgres
---
.../service/AuditReadPlatformServiceImpl.java | 10 +++--
.../service/LoanReadPlatformServiceImpl.java | 45 +++++++++-------------
2 files changed, 24 insertions(+), 31 deletions(-)
diff --git a/fineract-provider/src/main/java/org/apache/fineract/commands/service/AuditReadPlatformServiceImpl.java b/fineract-provider/src/main/java/org/apache/fineract/commands/service/AuditReadPlatformServiceImpl.java
index 39a87ca24..481c20232 100644
--- a/fineract-provider/src/main/java/org/apache/fineract/commands/service/AuditReadPlatformServiceImpl.java
+++ b/fineract-provider/src/main/java/org/apache/fineract/commands/service/AuditReadPlatformServiceImpl.java
@@ -417,15 +417,17 @@ public class AuditReadPlatformServiceImpl implements AuditReadPlatformService {
final Collection<AppUserData> appUsers = this.appUserReadPlatformService.retrieveSearchTemplate();
- String sql = " SELECT distinct(action_name) as actionName FROM m_permission p ";
+ String sql = " SELECT distinct(action_name) as actionName, CASE WHEN action_name in ('CREATE', 'DELETE', 'UPDATE') THEN action_name ELSE 'ZZZ' END as classifier "
+ + " FROM m_permission p ";
sql += makercheckerCapabilityOnly(useType, currentUser);
- sql += " order by (CASE WHEN action_name in ('CREATE', 'DELETE', 'UPDATE') THEN action_name ELSE 'ZZZ' END), action_name";
+ sql += " order by classifier, action_name";
final ActionNamesMapper mapper = new ActionNamesMapper();
final List<String> actionNames = this.jdbcTemplate.query(sql, mapper); // NOSONAR
- sql = " select distinct(entity_name) as entityName from m_permission p ";
+ sql = " select distinct(entity_name) as entityName, CASE WHEN " + sqlGenerator.escape("grouping")
+ + " = 'datatable' THEN 'ZZZ' ELSE entity_name END as classifier " + " from m_permission p ";
sql += makercheckerCapabilityOnly(useType, currentUser);
- sql += " order by (CASE WHEN " + sqlGenerator.escape("grouping") + " = 'datatable' THEN 'ZZZ' ELSE entity_name END), entity_name";
+ sql += " order by classifier, entity_name";
final EntityNamesMapper mapper2 = new EntityNamesMapper();
final List<String> entityNames = this.jdbcTemplate.query(sql, mapper2); // NOSONAR
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 9afbe449e..8eff49ca9 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
@@ -439,7 +439,7 @@ public class LoanReadPlatformServiceImpl implements LoanReadPlatformService {
RepaymentTransactionTemplateMapper mapper = new RepaymentTransactionTemplateMapper(sqlGenerator);
String sql = "select " + mapper.schema();
LoanTransactionData loanTransactionData = this.jdbcTemplate.queryForObject(sql, mapper, // NOSONAR
- new Object[] { LoanTransactionType.REPAYMENT.getValue(), loanId, loanId });
+ new Object[] { LoanTransactionType.REPAYMENT.getValue(), LoanTransactionType.REPAYMENT.getValue(), loanId, loanId });
final Collection<PaymentTypeData> paymentOptions = this.paymentTypeReadPlatformService.retrieveAllPaymentTypes();
return LoanTransactionData.templateOnTop(loanTransactionData, paymentOptions);
}
@@ -2273,41 +2273,32 @@ public class LoanReadPlatformServiceImpl implements LoanReadPlatformService {
public String schema() {
StringBuilder sqlBuilder = new StringBuilder();
-
- sqlBuilder.append(
- "(CASE WHEN max(tr.transaction_date)>ls.dueDate THEN max(tr.transaction_date) ELSE ls.dueDate END) as transactionDate, ");
+ sqlBuilder.append("(CASE ");
sqlBuilder.append(
- "ls.principal_amount - coalesce(ls.principal_writtenoff_derived,0) - coalesce(ls.principal_completed_derived,0) as principalDue, ");
+ "WHEN (select max(tr.transaction_date) as transaction_date from m_loan_transaction tr where tr.loan_id = l.id AND tr.transaction_type_enum = ? AND tr.is_reversed = false) > ls.dueDate ");
sqlBuilder.append(
- "ls.interest_amount - coalesce(ls.interest_completed_derived,0) - coalesce(ls.interest_waived_derived,0) - coalesce(ls.interest_writtenoff_derived,0) as interestDue, ");
+ "THEN (select max(tr.transaction_date) as transaction_date from m_loan_transaction tr where tr.loan_id = l.id AND tr.transaction_type_enum = ? AND tr.is_reversed = false) ");
+ sqlBuilder.append("ELSE ls.dueDate END) as transactionDate, ");
sqlBuilder.append(
- "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, ");
+ "ls.principal_amount - coalesce(ls.principal_writtenoff_derived, 0) - coalesce(ls.principal_completed_derived, 0) as principalDue, ");
sqlBuilder.append(
- "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, ");
+ "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(
- "l.currency_code as currencyCode, l.currency_digits as currencyDigits, l.currency_multiplesof as inMultiplesOf, l.net_disbursal_amount as netDisbursalAmount, rc."
- + sqlGenerator.escape("name") + " as currencyName, ");
- sqlBuilder.append("rc.display_symbol as currencyDisplaySymbol, rc.internationalized_name_code as currencyNameCode ");
- sqlBuilder.append("FROM m_loan l ");
+ "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(
- "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." + sqlGenerator.escape("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( ");
- sqlBuilder.append("(select min(ls.duedate) datedue,ls.loan_id from m_loan_repayment_schedule ls ");
- sqlBuilder.append("where ls.loan_id = ? and ls.completed_derived = 0) )asq ");
- sqlBuilder.append("on asq.loan_id = ls.loan_id and asq.datedue = ls.duedate ");
- sqlBuilder.append("WHERE l.id = ? ");
- sqlBuilder.append("GROUP BY ls.duedate, ");
- sqlBuilder.append("ls.principal_amount,ls.principal_completed_derived,ls.principal_writtenoff_derived, ");
- sqlBuilder
- .append("ls.interest_amount,ls.interest_completed_derived,ls.interest_waived_derived,ls.interest_writtenoff_derived, ");
+ "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(
- "ls.fee_charges_amount,ls.fee_charges_completed_derived, ls.fee_charges_writtenoff_derived, ls.fee_charges_waived_derived, ");
+ "l.currency_code as currencyCode, l.currency_digits as currencyDigits, l.currency_multiplesof as inMultiplesOf, l.net_disbursal_amount as netDisbursalAmount, ");
+ sqlBuilder.append("rc." + sqlGenerator.escape("name")
+ + " as currencyName, rc.display_symbol as currencyDisplaySymbol, rc.internationalized_name_code as currencyNameCode ");
+ sqlBuilder.append("FROM m_loan l ");
+ sqlBuilder.append("JOIN m_currency rc on rc." + sqlGenerator.escape("code") + " = l.currency_code ");
+ sqlBuilder.append("JOIN m_loan_repayment_schedule ls ON ls.loan_id = l.id AND ls.completed_derived = false ");
sqlBuilder.append(
- "ls.penalty_charges_amount, ls.penalty_charges_completed_derived, ls.penalty_charges_writtenoff_derived, ls.penalty_charges_waived_derived ");
+ "JOIN((SELECT ls.loan_id, ls.duedate as datedue FROM m_loan_repayment_schedule ls WHERE ls.loan_id = ? and ls.completed_derived = false ORDER BY ls.duedate LIMIT 1)) asq on asq.loan_id = ls.loan_id ");
+ sqlBuilder.append("AND asq.datedue = ls.duedate ");
+ sqlBuilder.append("WHERE l.id = ?");
return sqlBuilder.toString();
-
}
@Override