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 b44a2f2b1 Business date - part 3: Fix reports
b44a2f2b1 is described below
commit b44a2f2b140f6b0f35baf6bb558cd09d3d91bb3a
Author: Adam Saghy <[email protected]>
AuthorDate: Mon Jul 4 14:36:11 2022 +0200
Business date - part 3: Fix reports
---
.../data/ResultsetColumnHeaderData.java | 6 +-
.../service/GenericDataServiceImpl.java | 3 +
.../service/ReadReportingServiceImpl.java | 11 +++-
.../db/changelog/tenant/changelog-tenant.xml | 1 +
.../tenant/parts/0017_fix_stretchy_reports.xml | 67 ++++++++++++++++++++++
5 files changed, 83 insertions(+), 5 deletions(-)
diff --git
a/fineract-provider/src/main/java/org/apache/fineract/infrastructure/dataqueries/data/ResultsetColumnHeaderData.java
b/fineract-provider/src/main/java/org/apache/fineract/infrastructure/dataqueries/data/ResultsetColumnHeaderData.java
index d5d6bf4cd..e318cd02d 100644
---
a/fineract-provider/src/main/java/org/apache/fineract/infrastructure/dataqueries/data/ResultsetColumnHeaderData.java
+++
b/fineract-provider/src/main/java/org/apache/fineract/infrastructure/dataqueries/data/ResultsetColumnHeaderData.java
@@ -179,7 +179,8 @@ public final class ResultsetColumnHeaderData implements
Serializable {
}
private boolean isChar() {
- return "char".equalsIgnoreCase(this.columnType) || "CHARACTER
VARYING".equalsIgnoreCase(this.columnType);
+ return "char".equalsIgnoreCase(this.columnType) || "CHARACTER
VARYING".equalsIgnoreCase(this.columnType)
+ || "bpchar".equalsIgnoreCase(this.columnType);
}
private boolean isVarchar() {
@@ -201,7 +202,8 @@ public final class ResultsetColumnHeaderData implements
Serializable {
}
private boolean isInteger() {
- return "integer".equalsIgnoreCase(this.columnType) ||
"int4".equalsIgnoreCase(this.columnType);
+ return "integer".equalsIgnoreCase(this.columnType) ||
"int2".equalsIgnoreCase(this.columnType)
+ || "int4".equalsIgnoreCase(this.columnType);
}
private boolean isSmallInt() {
diff --git
a/fineract-provider/src/main/java/org/apache/fineract/infrastructure/dataqueries/service/GenericDataServiceImpl.java
b/fineract-provider/src/main/java/org/apache/fineract/infrastructure/dataqueries/service/GenericDataServiceImpl.java
index bbd8e1243..925871aac 100644
---
a/fineract-provider/src/main/java/org/apache/fineract/infrastructure/dataqueries/service/GenericDataServiceImpl.java
+++
b/fineract-provider/src/main/java/org/apache/fineract/infrastructure/dataqueries/service/GenericDataServiceImpl.java
@@ -24,6 +24,7 @@ import java.time.temporal.ChronoField;
import java.util.ArrayList;
import java.util.List;
import javax.sql.DataSource;
+import lombok.extern.slf4j.Slf4j;
import
org.apache.fineract.infrastructure.core.exception.PlatformDataIntegrityException;
import org.apache.fineract.infrastructure.core.service.DateUtils;
import org.apache.fineract.infrastructure.core.service.RoutingDataSource;
@@ -41,6 +42,7 @@ import
org.springframework.jdbc.support.rowset.SqlRowSetMetaData;
import org.springframework.stereotype.Service;
@Service
+@Slf4j
public class GenericDataServiceImpl implements GenericDataService {
private final JdbcTemplate jdbcTemplate;
@@ -88,6 +90,7 @@ public class GenericDataServiceImpl implements
GenericDataService {
return new GenericResultsetData(columnHeaders, resultsetDataRows);
} catch (DataAccessException e) {
+ log.error("Reporting error: {}", e.getMessage());
throw new
PlatformDataIntegrityException("error.msg.report.unknown.data.integrity.issue",
e.getClass().getName(), e);
}
}
diff --git
a/fineract-provider/src/main/java/org/apache/fineract/infrastructure/dataqueries/service/ReadReportingServiceImpl.java
b/fineract-provider/src/main/java/org/apache/fineract/infrastructure/dataqueries/service/ReadReportingServiceImpl.java
index 4b518014e..8f9ca8b80 100644
---
a/fineract-provider/src/main/java/org/apache/fineract/infrastructure/dataqueries/service/ReadReportingServiceImpl.java
+++
b/fineract-provider/src/main/java/org/apache/fineract/infrastructure/dataqueries/service/ReadReportingServiceImpl.java
@@ -39,8 +39,10 @@ import java.util.Set;
import javax.ws.rs.core.StreamingOutput;
import lombok.RequiredArgsConstructor;
import lombok.extern.slf4j.Slf4j;
+import org.apache.commons.lang3.StringUtils;
import org.apache.fineract.infrastructure.core.domain.JdbcSupport;
import
org.apache.fineract.infrastructure.core.exception.PlatformDataIntegrityException;
+import
org.apache.fineract.infrastructure.core.service.database.DatabaseSpecificSQLGenerator;
import
org.apache.fineract.infrastructure.dataqueries.data.GenericResultsetData;
import org.apache.fineract.infrastructure.dataqueries.data.ReportData;
import org.apache.fineract.infrastructure.dataqueries.data.ReportParameterData;
@@ -69,6 +71,7 @@ public class ReadReportingServiceImpl implements
ReadReportingService {
private final PlatformSecurityContext context;
private final GenericDataService genericDataService;
private final SqlInjectionPreventerService sqlInjectionPreventerService;
+ private final DatabaseSpecificSQLGenerator sqlGenerator;
@Override
public StreamingOutput retrieveReportCSV(final String name, final String
type, final Map<String, String> queryParams,
@@ -187,9 +190,11 @@ public class ReadReportingServiceImpl implements
ReadReportingService {
// (typically used to return report lists containing only reports
// permitted to be run by the user
sql = this.genericDataService.replace(sql, "${currentUserId}",
currentUser.getId().toString());
-
- sql = this.genericDataService.replace(sql, "${isSelfServiceUser}",
Integer.toString(isSelfServiceUserReport ? 1 : 0));
-
+ sql = this.genericDataService.replace(sql, "${isSelfServiceUser}",
Boolean.toString(isSelfServiceUserReport));
+ sql = this.genericDataService.replace(sql, "${currentDate}",
sqlGenerator.currentBusinessDate());
+ sql = StringUtils.replaceIgnoreCase(sql, "NOW()",
sqlGenerator.currentTenantDateTime());
+ sql = StringUtils.replaceIgnoreCase(sql, "curdate()",
sqlGenerator.currentBusinessDate());
+ sql = StringUtils.replaceIgnoreCase(sql, "CURRENT_DATE",
sqlGenerator.currentBusinessDate());
sql = this.genericDataService.wrapSQL(sql);
return sql;
diff --git
a/fineract-provider/src/main/resources/db/changelog/tenant/changelog-tenant.xml
b/fineract-provider/src/main/resources/db/changelog/tenant/changelog-tenant.xml
index c5d7ef7f5..2b58405a6 100644
---
a/fineract-provider/src/main/resources/db/changelog/tenant/changelog-tenant.xml
+++
b/fineract-provider/src/main/resources/db/changelog/tenant/changelog-tenant.xml
@@ -36,4 +36,5 @@
<include file="parts/0014_remove_unused_jobs.xml"
relativeToChangelogFile="true"/>
<include file="parts/0015_add_business_date.xml"
relativeToChangelogFile="true"/>
<include file="parts/0016_changed_unique_constraint_of_ref_no.xml"
relativeToChangelogFile="true"/>
+ <include file="parts/0017_fix_stretchy_reports.xml"
relativeToChangelogFile="true"/>
</databaseChangeLog>
diff --git
a/fineract-provider/src/main/resources/db/changelog/tenant/parts/0017_fix_stretchy_reports.xml
b/fineract-provider/src/main/resources/db/changelog/tenant/parts/0017_fix_stretchy_reports.xml
new file mode 100644
index 000000000..331083ce2
--- /dev/null
+++
b/fineract-provider/src/main/resources/db/changelog/tenant/parts/0017_fix_stretchy_reports.xml
@@ -0,0 +1,67 @@
+<?xml version="1.0" encoding="UTF-8"?>
+<!--
+
+ Licensed to the Apache Software Foundation (ASF) under one
+ or more contributor license agreements. See the NOTICE file
+ distributed with this work for additional information
+ regarding copyright ownership. The ASF licenses this file
+ to you under the Apache License, Version 2.0 (the
+ "License"); you may not use this file except in compliance
+ with the License. You may obtain a copy of the License at
+
+ http://www.apache.org/licenses/LICENSE-2.0
+
+ Unless required by applicable law or agreed to in writing,
+ software distributed under the License is distributed on an
+ "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+ KIND, either express or implied. See the License for the
+ specific language governing permissions and limitations
+ under the License.
+
+-->
+<databaseChangeLog xmlns="http://www.liquibase.org/xml/ns/dbchangelog";
+ xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance";
+
xsi:schemaLocation="http://www.liquibase.org/xml/ns/dbchangelog
http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-4.1.xsd";>
+ <changeSet author="fineract" id="1">
+ <update tableName="stretchy_parameter">
+ <column name="parameter_sql" value="select p.id,
p.name from m_product_loan p where (p.currency_code =
'${currencyId}' or '-1'= '${currencyId}') order by 2"/>
+ <where>id='25' AND parameter_name='loanProductIdSelectAll'</where>
+ </update>
+ </changeSet>
+ <changeSet author="fineract" id="2" context="postgresql">
+ <update tableName="stretchy_parameter">
+ <column name="parameter_sql" value="select sp.parameter_name,
sp.parameter_variable, sp.parameter_label,
sp."parameter_displayType", sp."parameter_FormatType",
sp.parameter_default, sp."selectOne", sp."selectAll",
spp.parameter_name as parentParameterName from stretchy_parameter sp left join
stretchy_parameter spp on spp.id = sp.parent_id where sp.special is null and
exists (select 'f' from stretchy_report sr join stretchy [...]
+ <where>id='1002' AND parameter_name='FullParameterList'</where>
+ </update>
+ </changeSet>
+ <changeSet author="fineract" id="3" context="postgresql">
+ <update tableName="stretchy_report">
+ <column name="report_sql" value="select ml.id as loanId,
COALESCE(mc.id,mc2.id) as id, COALESCE(mc.firstname,mc2.firstname) as
firstname, COALESCE(mc.middlename,mc2.middlename,(\'\')) as middlename,
COALESCE(mc.lastname,mc2.lastname) as lastname,
COALESCE(mc.display_name,mc2.display_name) as display_name,
COALESCE(mc.status_enum,mc2.status_enum) as status_enum,
COALESCE(mc.mobile_no,mc2.mobile_no) as mobile_no,
COALESCE(mg.office_id,mc2.office_id) as office_id, COALES [...]
+ <where>id='190' AND report_name='Loan Approved - Email'</where>
+ </update>
+ </changeSet>
+ <changeSet author="fineract" id="4" context="postgresql">
+ <update tableName="stretchy_report">
+ <column name="report_sql" value="select ml.id as loanId,
COALESCE(mc.id,mc2.id) as id, COALESCE(mc.firstname,mc2.firstname) as
firstname, COALESCE(mc.middlename,mc2.middlename,(\'\')) as middlename,
COALESCE(mc.lastname,mc2.lastname) as lastname,
COALESCE(mc.display_name,mc2.display_name) as display_name,
COALESCE(mc.status_enum,mc2.status_enum) as status_enum,
COALESCE(mc.mobile_no,mc2.mobile_no) as mobile_no,
COALESCE(mg.office_id,mc2.office_id) as office_id, COALES [...]
+ <where>id='191' AND report_name='Loan Rejected - Email'</where>
+ </update>
+ </changeSet>
+ <changeSet author="fineract" id="5" context="postgresql">
+ <update tableName="stretchy_report">
+ <column name="report_sql" value="select ml.id as loanId,
COALESCE(mc.id,mc2.id) as id, COALESCE(mc.firstname,mc2.firstname) as
firstname, COALESCE(mc.middlename,mc2.middlename,(\'\')) as middlename,
COALESCE(mc.lastname,mc2.lastname) as lastname,
COALESCE(mc.display_name,mc2.display_name) as display_name,
COALESCE(mc.status_enum,mc2.status_enum) as status_enum,
COALESCE(mc.mobile_no,mc2.mobile_no) as mobile_no,
COALESCE(mg.office_id,mc2.office_id) as office_id, COAL [...]
+ <where>id='192' AND report_name='Loan Repayment - Email'</where>
+ </update>
+ </changeSet>
+ <changeSet author="fineract" id="6">
+ <update tableName="stretchy_parameter">
+ <column name="parameter_sql" value="select r.id as report_id,
r.report_name, r.report_type, r.report_subtype, r.report_category, rp.id as
parameter_id, rp.report_parameter_name, p.parameter_name from
stretchy_report r left join stretchy_report_parameter rp on rp.report_id
= r.id left join stretchy_parameter p on p.id = rp.parameter_id
where r.use_report is true and r.self_service_user_report =
${isSelfServiceUser} and exists ( select [...]
+ <where>id='1001' AND parameter_name='FullReportList'</where>
+ </update>
+ </changeSet>
+ <changeSet author="fineract" id="7">
+ <update tableName="stretchy_parameter">
+ <column name="parameter_sql" value="select r.id as report_id,
r.report_name, r.report_type, r.report_subtype, r.report_category, rp.id
as parameter_id, rp.report_parameter_name, p.parameter_name from
stretchy_report r left join stretchy_report_parameter rp on rp.report_id
= r.id left join stretchy_parameter p on p.id = rp.parameter_id
where r.report_category = '${reportCategory}' and r.use_report is true
and r.self_service_user_report = ${ [...]
+ <where>id='1003' AND parameter_name='reportCategoryList'</where>
+ </update>
+ </changeSet>
+</databaseChangeLog>
