This is an automated email from the ASF dual-hosted git repository.
manojvm 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 e90dd49c8 FINERACT-1541: Pentaho reports converted to table format
new 83dcc4413 Merge pull request #2404 from
logoutdhaval/pentaho-to-table-branch
e90dd49c8 is described below
commit e90dd49c819b3dcf94e945f8f4f6bcb619bf595a
Author: logoutdhaval <[email protected]>
AuthorDate: Mon Jul 4 21:55:27 2022 +0530
FINERACT-1541: Pentaho reports converted to table format
---
.../db/changelog/tenant/changelog-tenant.xml | 1 +
.../tenant/parts/0018_pentaho_reports_to_table.xml | 247 +++++++++++++++++++++
.../integrationtests/client/ReportsTest.java | 35 ++-
3 files changed, 282 insertions(+), 1 deletion(-)
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 2b58405a6..0294b2a76 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
@@ -37,4 +37,5 @@
<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"/>
+ <include file="parts/0018_pentaho_reports_to_table.xml"
relativeToChangelogFile="true"/>
</databaseChangeLog>
diff --git
a/fineract-provider/src/main/resources/db/changelog/tenant/parts/0018_pentaho_reports_to_table.xml
b/fineract-provider/src/main/resources/db/changelog/tenant/parts/0018_pentaho_reports_to_table.xml
new file mode 100644
index 000000000..8c5b1c802
--- /dev/null
+++
b/fineract-provider/src/main/resources/db/changelog/tenant/parts/0018_pentaho_reports_to_table.xml
@@ -0,0 +1,247 @@
+<?xml version="1.1" encoding="UTF-8" standalone="no"?>
+<!--
+
+ 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:ext="http://www.liquibase.org/xml/ns/dbchangelog-ext";
xmlns:pro="http://www.liquibase.org/xml/ns/pro";
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance";
xsi:schemaLocation="http://www.liquibase.org/xml/ns/dbchangelog-ext
http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-ext.xsd
http://www.liquibase.org/xml/ns/pro
http://www.liquibase.org/xml/ns/pro/liquibase-pro-4.6.xsd
http://www.liquibase.org/xml/ns/d [...]
+ <changeSet author="fineract" id="1-mysql" context="mysql">
+ <insert tableName="stretchy_report">
+ <column name="report_name" value="Trial Balance Table"/>
+ <column name="report_type" value="Table"/>
+ <column name="report_subtype"/>
+ <column name="report_category" value="Accounting"/>
+ <column name="report_sql" value="select * from (select
debits.glcode as 'glcode', debits.name as 'name', IF(debits.type = 1 or
debits.type = 5,
ifnull(debits.debitamount,0)-ifnull(credits.creditamount,0),null) as 'debit',
IF(debits.type = 4 or debits.type = 3 or debits.type = 2,
ifnull(credits.creditamount,0)-ifnull(debits.debitamount,0),null) as 'credit'
from (select acc_gl_account.gl_code as 'glcode', name,sum(amount) as
'debitamount', acc_gl_account.classification_enum [...]
+ <column name="description" value="Trial Balance Report"/>
+ <column name="core_report" valueBoolean="true"/>
+ <column name="use_report" valueBoolean="true"/>
+ <column name="self_service_user_report" valueBoolean="false"/>
+ </insert>
+ <insert tableName="stretchy_report">
+ <column name="report_name" value="GeneralLedgerReport Table"/>
+ <column name="report_type" value="Table"/>
+ <column name="report_subtype"/>
+ <column name="report_category" value="Accounting"/>
+ <column name="report_sql" value="select details.edate entry_date,
sum(details.debit_amount) debit_amount, sum(details.credit_amount)
credit_amount, details.description, ifnull(opb.openingbalance,0)
openingbalance, if (details.manual_entry=1,details.id,'0system') transtype, if
(actype in (1,5), (sum(details.debit_amount) - sum(details.credit_amount)),
(sum(details.credit_amount) - sum(details.debit_amount))) as cumulative_sum
from ( select a.account_id acid1 ,concat(g [...]
+ <column name="description"/>
+ <column name="core_report" valueBoolean="false"/>
+ <column name="use_report" valueBoolean="true"/>
+ <column name="self_service_user_report" valueBoolean="false"/>
+ </insert>
+ <insert tableName="stretchy_report">
+ <column name="report_name" value="Income Statement Table"/>
+ <column name="report_type" value="Table"/>
+ <column name="report_subtype"/>
+ <column name="report_category" value="Accounting"/>
+ <column name="report_sql" value="(select * from ( select
debits.glcode as 'glcode', debits.name as 'name', 'Expense'as IncomeOrExpense,
(ifnull(debits.debitamount,0)-ifnull(credits.creditamount,0)) as 'balance' from
(select acc_gl_account.gl_code as 'glcode',name,sum(amount) as 'debitamount'
from acc_gl_journal_entry,acc_gl_account where acc_gl_account.id =
acc_gl_journal_entry.account_id and acc_gl_journal_entry.type_enum=2 and
acc_gl_account.classification_enum in (5) and [...]
+ <column name="description" value="Profit and Loss Statement"/>
+ <column name="core_report" valueBoolean="true"/>
+ <column name="use_report" valueBoolean="true"/>
+ <column name="self_service_user_report" valueBoolean="false"/>
+ </insert>
+ <insert tableName="stretchy_report">
+ <column name="report_name" value="Balance Sheet Table"/>
+ <column name="report_type" value="Table"/>
+ <column name="report_subtype"/>
+ <column name="report_category" value="Accounting"/>
+ <column name="report_sql" value="(select debits.glcode as
'glcode', debits.name as 'name', 'Assets' as BalanceType,
(ifnull(debits.debitamount,0)-ifnull(credits.creditamount,0)) as 'balance' from
(select acc_gl_account.gl_code as 'glcode',name,sum(amount) as 'debitamount'
from acc_gl_journal_entry,acc_gl_account where acc_gl_account.id =
acc_gl_journal_entry.account_id and acc_gl_journal_entry.type_enum=2 and
acc_gl_account.classification_enum in (1) and acc_gl_journal_entry [...]
+ <column name="description" value="Balance Sheet"/>
+ <column name="core_report" valueBoolean="true"/>
+ <column name="use_report" valueBoolean="true"/>
+ <column name="self_service_user_report" valueBoolean="false"/>
+ </insert>
+
+
+ <insert tableName="stretchy_report_parameter">
+ <column name="report_id" valueComputed="(select sr.id from
stretchy_report sr where sr.report_name ='Trial Balance Table')"/>
+ <column name="parameter_id" valueNumeric="1"/>
+ <column name="report_parameter_name" value="fromDate"/>
+ </insert>
+ <insert tableName="stretchy_report_parameter">
+ <column name="report_id" valueComputed="(select sr.id from
stretchy_report sr where sr.report_name ='Trial Balance Table')"/>
+ <column name="parameter_id" valueNumeric="5"/>
+ <column name="report_parameter_name" value="branch"/>
+ </insert>
+ <insert tableName="stretchy_report_parameter">
+ <column name="report_id" valueComputed="(select sr.id from
stretchy_report sr where sr.report_name ='Trial Balance Table')"/>
+ <column name="parameter_id" valueNumeric="2"/>
+ <column name="report_parameter_name" value="toDate"/>
+ </insert>
+
+ <insert tableName="stretchy_report_parameter">
+ <column name="report_id" valueComputed="(select sr.id from
stretchy_report sr where sr.report_name ='GeneralLedgerReport Table')"/>
+ <column name="parameter_id" valueNumeric="1008"/>
+ <column name="report_parameter_name" value="GLAccountNO"/>
+ </insert>
+ <insert tableName="stretchy_report_parameter">
+ <column name="report_id" valueComputed="(select sr.id from
stretchy_report sr where sr.report_name ='GeneralLedgerReport Table')"/>
+ <column name="parameter_id" valueNumeric="5"/>
+ <column name="report_parameter_name" value="officeId"/>
+ </insert>
+ <insert tableName="stretchy_report_parameter">
+ <column name="report_id" valueComputed="(select sr.id from
stretchy_report sr where sr.report_name ='GeneralLedgerReport Table')"/>
+ <column name="parameter_id" valueNumeric="2"/>
+ <column name="report_parameter_name" value="endDate"/>
+ </insert>
+ <insert tableName="stretchy_report_parameter">
+ <column name="report_id" valueComputed="(select sr.id from
stretchy_report sr where sr.report_name ='GeneralLedgerReport Table')"/>
+ <column name="parameter_id" valueNumeric="1"/>
+ <column name="report_parameter_name" value="startDate"/>
+ </insert>
+
+ <insert tableName="stretchy_report_parameter">
+ <column name="report_id" valueComputed="(select sr.id from
stretchy_report sr where sr.report_name ='Balance Sheet Table')"/>
+ <column name="parameter_id" valueNumeric="5"/>
+ <column name="report_parameter_name" value="branch"/>
+ </insert>
+ <insert tableName="stretchy_report_parameter">
+ <column name="report_id" valueComputed="(select sr.id from
stretchy_report sr where sr.report_name ='Balance Sheet Table')"/>
+ <column name="parameter_id" valueNumeric="2"/>
+ <column name="report_parameter_name" value="date"/>
+ </insert>
+
+ <insert tableName="stretchy_report_parameter">
+ <column name="report_id" valueComputed="(select sr.id from
stretchy_report sr where sr.report_name ='Income Statement Table')"/>
+ <column name="parameter_id" valueNumeric="5"/>
+ <column name="report_parameter_name" value="branch"/>
+ </insert>
+ <insert tableName="stretchy_report_parameter">
+ <column name="report_id" valueComputed="(select sr.id from
stretchy_report sr where sr.report_name ='Income Statement Table')"/>
+ <column name="parameter_id" valueNumeric="1"/>
+ <column name="report_parameter_name" value="fromDate"/>
+ </insert>
+ <insert tableName="stretchy_report_parameter">
+ <column name="report_id" valueComputed="(select sr.id from
stretchy_report sr where sr.report_name ='Income Statement Table')"/>
+ <column name="parameter_id" valueNumeric="2"/>
+ <column name="report_parameter_name" value="toDate"/>
+ </insert>
+ </changeSet>
+
+ <changeSet author="fineract" id="1-postgresql" context="postgresql">
+ <insert tableName="stretchy_report">
+ <column name="report_name" value="Trial Balance Table"/>
+ <column name="report_type" value="Table"/>
+ <column name="report_subtype"/>
+ <column name="report_category" value="Accounting"/>
+ <column name="report_sql" value="select * from ( select
debits.glcode as "glcode", debits.name as "name", (case
when debits.type = 1 or debits.type = 5 then coalesce(debits.debitamount, 0) -
coalesce(credits.creditamount, 0)else null end ) as "debit", (case
when debits.type = 4 or debits.type = 3 or debits.type = 2 then
coalesce(credits.creditamount, 0) - coalesce(debits.debitamount, 0)else null
end ) as "credit" from ( select acc_gl_ac [...]
+ <column name="description" value="Trial Balance Report"/>
+ <column name="core_report" valueBoolean="true"/>
+ <column name="use_report" valueBoolean="true"/>
+ <column name="self_service_user_report" valueBoolean="false"/>
+ </insert>
+ <insert tableName="stretchy_report">
+ <column name="report_name" value="GeneralLedgerReport Table"/>
+ <column name="report_type" value="Table"/>
+ <column name="report_subtype"/>
+ <column name="report_category" value="Accounting"/>
+ <column name= "report_sql" value = "select details.edate
entry_date, sum(details.debit_amount) debit_amount, sum(details.credit_amount)
credit_amount, details.description, coalesce(opb.openingbalance, 0)
openingbalance, case when details.manual_entry then cast(details.id as text)
else cast('system' as text) end transtype, case when actype in (1, 5) then (
sum(details.debit_amount) - sum(details.credit_amount) ) else (
sum(details.credit_amount) - sum(details.debit_amount) ) e [...]
+ <column name="description"/>
+ <column name="core_report" valueBoolean="false"/>
+ <column name="use_report" valueBoolean="true"/>
+ <column name="self_service_user_report" valueBoolean="false"/>
+ </insert>
+ <insert tableName="stretchy_report">
+ <column name="report_name" value="Income Statement Table"/>
+ <column name="report_type" value="Table"/>
+ <column name="report_subtype"/>
+ <column name="report_category" value="Accounting"/>
+ <column name="report_sql" value="( select * from ( select
debits.glcode as "glcode", debits.name as "name", 'Expense'
as IncomeOrExpense, ( coalesce(debits.debitamount, 0) -
coalesce(credits.creditamount, 0) ) as "balance" from ( select
acc_gl_account.gl_code as "glcode", name, sum(amount) as
"debitamount" from acc_gl_journal_entry, acc_gl_account where
acc_gl_account.id = acc_gl_journal_entry.account_id and acc_gl_journal_ent [...]
+ <column name="description" value="Profit and Loss Statement"/>
+ <column name="core_report" valueBoolean="true"/>
+ <column name="use_report" valueBoolean="true"/>
+ <column name="self_service_user_report" valueBoolean="false"/>
+ </insert>
+ <insert tableName="stretchy_report">
+ <column name="report_name" value="Balance Sheet Table"/>
+ <column name="report_type" value="Table"/>
+ <column name="report_subtype"/>
+ <column name="report_category" value="Accounting"/>
+ <column name="report_sql" value=" ( select debits.glcode as
glcode, debits.name as "name", 'Assets' as BalanceType, (
coalesce(debits.debitamount, 0) - coalesce(credits.creditamount, 0) ) as
balance from ( select acc_gl_account.gl_code as glcode, name, sum(amount) as
debitamount from acc_gl_journal_entry, acc_gl_account where acc_gl_account.id =
acc_gl_journal_entry.account_id and acc_gl_journal_entry.type_enum = 2 and
acc_gl_account.classification_enum in (1) and a [...]
+ <column name="description" value="Balance Sheet"/>
+ <column name="core_report" valueBoolean="true"/>
+ <column name="use_report" valueBoolean="true"/>
+ <column name="self_service_user_report" valueBoolean="false"/>
+ </insert>
+
+
+ <insert tableName="stretchy_report_parameter">
+ <column name="report_id" valueComputed="(select sr.id from
stretchy_report sr where sr.report_name ='Trial Balance Table')"/>
+ <column name="parameter_id" valueNumeric="1"/>
+ <column name="report_parameter_name" value="fromDate"/>
+ </insert>
+ <insert tableName="stretchy_report_parameter">
+ <column name="report_id" valueComputed="(select sr.id from
stretchy_report sr where sr.report_name ='Trial Balance Table')"/>
+ <column name="parameter_id" valueNumeric="5"/>
+ <column name="report_parameter_name" value="branch"/>
+ </insert>
+ <insert tableName="stretchy_report_parameter">
+ <column name="report_id" valueComputed="(select sr.id from
stretchy_report sr where sr.report_name ='Trial Balance Table')"/>
+ <column name="parameter_id" valueNumeric="2"/>
+ <column name="report_parameter_name" value="toDate"/>
+ </insert>
+
+ <insert tableName="stretchy_report_parameter">
+ <column name="report_id" valueComputed="(select sr.id from
stretchy_report sr where sr.report_name ='GeneralLedgerReport Table')"/>
+ <column name="parameter_id" valueNumeric="1008"/>
+ <column name="report_parameter_name" value="GLAccountNO"/>
+ </insert>
+ <insert tableName="stretchy_report_parameter">
+ <column name="report_id" valueComputed="(select sr.id from
stretchy_report sr where sr.report_name ='GeneralLedgerReport Table')"/>
+ <column name="parameter_id" valueNumeric="5"/>
+ <column name="report_parameter_name" value="officeId"/>
+ </insert>
+ <insert tableName="stretchy_report_parameter">
+ <column name="report_id" valueComputed="(select sr.id from
stretchy_report sr where sr.report_name ='GeneralLedgerReport Table')"/>
+ <column name="parameter_id" valueNumeric="2"/>
+ <column name="report_parameter_name" value="endDate"/>
+ </insert>
+ <insert tableName="stretchy_report_parameter">
+ <column name="report_id" valueComputed="(select sr.id from
stretchy_report sr where sr.report_name ='GeneralLedgerReport Table')"/>
+ <column name="parameter_id" valueNumeric="1"/>
+ <column name="report_parameter_name" value="startDate"/>
+ </insert>
+
+ <insert tableName="stretchy_report_parameter">
+ <column name="report_id" valueComputed="(select sr.id from
stretchy_report sr where sr.report_name ='Balance Sheet Table')"/>
+ <column name="parameter_id" valueNumeric="5"/>
+ <column name="report_parameter_name" value="branch"/>
+ </insert>
+ <insert tableName="stretchy_report_parameter">
+ <column name="report_id" valueComputed="(select sr.id from
stretchy_report sr where sr.report_name ='Balance Sheet Table')"/>
+ <column name="parameter_id" valueNumeric="2"/>
+ <column name="report_parameter_name" value="date"/>
+ </insert>
+
+ <insert tableName="stretchy_report_parameter">
+ <column name="report_id" valueComputed="(select sr.id from
stretchy_report sr where sr.report_name ='Income Statement Table')"/>
+ <column name="parameter_id" valueNumeric="5"/>
+ <column name="report_parameter_name" value="branch"/>
+ </insert>
+ <insert tableName="stretchy_report_parameter">
+ <column name="report_id" valueComputed="(select sr.id from
stretchy_report sr where sr.report_name ='Income Statement Table')"/>
+ <column name="parameter_id" valueNumeric="1"/>
+ <column name="report_parameter_name" value="fromDate"/>
+ </insert>
+ <insert tableName="stretchy_report_parameter">
+ <column name="report_id" valueComputed="(select sr.id from
stretchy_report sr where sr.report_name ='Income Statement Table')"/>
+ <column name="parameter_id" valueNumeric="2"/>
+ <column name="report_parameter_name" value="toDate"/>
+ </insert>
+ </changeSet>
+</databaseChangeLog>
diff --git
a/integration-tests/src/test/java/org/apache/fineract/integrationtests/client/ReportsTest.java
b/integration-tests/src/test/java/org/apache/fineract/integrationtests/client/ReportsTest.java
index 98378b14a..9430ac556 100644
---
a/integration-tests/src/test/java/org/apache/fineract/integrationtests/client/ReportsTest.java
+++
b/integration-tests/src/test/java/org/apache/fineract/integrationtests/client/ReportsTest.java
@@ -23,6 +23,8 @@ import java.util.Map;
import okhttp3.MediaType;
import okhttp3.Request;
import okhttp3.ResponseBody;
+import org.apache.fineract.integrationtests.common.Utils;
+import org.junit.jupiter.api.BeforeEach;
import org.junit.jupiter.api.Disabled;
import org.junit.jupiter.api.Test;
@@ -33,9 +35,14 @@ import org.junit.jupiter.api.Test;
*/
public class ReportsTest extends IntegrationTest {
+ @BeforeEach
+ public void setup() {
+ Utils.initializeRESTAssured();
+ }
+
@Test
void listReports() {
- assertThat(ok(fineract().reports.retrieveReportList())).hasSize(120);
+ assertThat(ok(fineract().reports.retrieveReportList())).hasSize(124);
}
@Test
@@ -69,4 +76,30 @@ public class ReportsTest extends IntegrationTest {
"2013-04-30", "R_loanOfficerId", "-1", "R_officeId", "1",
"R_startDate", "2013-04-16", "output-type", "PDF"), false));
assertThat(r.contentType()).isEqualTo(MediaType.get("application/pdf"));
}
+
+ @Test
+ void testTrialBalanceTableReportRunsSuccessfully() {
+ assertThat(fineract().reportsRun.runReportGetData("Trial Balance
Table",
+ Map.of("R_endDate", "2013-04-30", "R_officeId", "1",
"R_startDate", "2013-04-16"), false)).hasHttpStatus(200);
+ }
+
+ @Test
+ void testIncomeStatementTableReportRunsSuccessfully() {
+ assertThat(fineract().reportsRun.runReportGetData("Income Statement
Table",
+ Map.of("R_endDate", "2013-04-30", "R_officeId", "1",
"R_startDate", "2013-04-16"), false)).hasHttpStatus(200);
+ }
+
+ @Test
+ void testGeneralLedgerReportTableReportRunsSuccessfully() {
+ assertThat(fineract().reportsRun.runReportGetData("GeneralLedgerReport
Table",
+ Map.of("R_endDate", "2013-04-30", "R_officeId", "1",
"R_startDate", "2013-04-16", "R_GLAccountNO", "1"), false))
+ .hasHttpStatus(200);
+ }
+
+ @Test
+ void testBalanceSheetTableReportRunsSuccessfully() {
+ assertThat(
+ fineract().reportsRun.runReportGetData("Balance Sheet Table",
Map.of("R_endDate", "2013-04-30", "R_officeId", "1"), false))
+ .hasHttpStatus(200);
+ }
}
