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 21013749f FINERACT-1853: General accounting summary table reports
21013749f is described below
commit 21013749f43a39ab7fbf94ca1f1f1932ff09d478
Author: Jose Alberto Hernandez <[email protected]>
AuthorDate: Tue Jan 17 13:31:10 2023 -0600
FINERACT-1853: General accounting summary table reports
---
.../db/changelog/tenant/changelog-tenant.xml | 1 +
.../0084_add_general_accounting_table_reports.xml | 128 +++++++++++++++++++++
.../integrationtests/client/ReportsTest.java | 2 +-
3 files changed, 130 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 c7eb5a792..932f1fec5 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
@@ -103,4 +103,5 @@
<include file="parts/0081_add_configuration_event_producer_batch_size.xml"
relativeToChangelogFile="true" />
<include file="parts/0082_add_external_event_default_configuration.xml"
relativeToChangelogFile="true" />
<include file="parts/0083_add_loan_transaction_enum_values.xml"
relativeToChangelogFile="true" />
+ <include file="parts/0084_add_general_accounting_table_reports.xml"
relativeToChangelogFile="true" />
</databaseChangeLog>
diff --git
a/fineract-provider/src/main/resources/db/changelog/tenant/parts/0084_add_general_accounting_table_reports.xml
b/fineract-provider/src/main/resources/db/changelog/tenant/parts/0084_add_general_accounting_table_reports.xml
new file mode 100644
index 000000000..07854d7da
--- /dev/null
+++
b/fineract-provider/src/main/resources/db/changelog/tenant/parts/0084_add_general_accounting_table_reports.xml
@@ -0,0 +1,128 @@
+<?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.3.xsd";>
+ <changeSet author="fineract" id="1">
+ <insert tableName="stretchy_report">
+ <column name="report_name" value="Trial Balance Summary Report"/>
+ <column name="report_type" value="Table"/>
+ <column name="report_subtype"/>
+ <column name="report_category" value="Accounting"/>
+ <column name="report_sql" value="SELECT * FROM ( SELECT
'${endDate}' AS PostingDate, loan.pname AS Product, loan.gl_code AS GlAcct,
loan.glname AS Description,
+ loan.openingbalance AS BeginningBalance,
(loan.debitamount*1) AS DebitMovement, (loan.creditamount*-1) AS CreditMovement,
+ (loan.openingbalance + loan.debitamount -
loan.creditamount) AS EndingBalance FROM (SELECT g.pname, g.gl_code, g.glname,
+ COALESCE(debits.debitamount, 0) -
COALESCE(debits.creditamount, 0) AS openingbalance,
COALESCE(loanproduct.debitamount, 0) AS debitamount,
COALESCE(loanproduct.creditamount, 0)
+ AS creditamount FROM (SELECT ag.gl_code, pl.name AS pname,
ag.name AS glname FROM acc_gl_account ag JOIN acc_product_mapping am ON
am.gl_account_id = ag.id AND
+ am.product_type = 1 JOIN m_product_loan pl ON pl.id =
am.product_id) g LEFT JOIN (SELECT lp.name AS productname,
acc_gl_account.gl_code AS glcode, acc_gl_account.name AS glname,
+ sum(case when acc_gl_journal_entry.type_enum=2 then amount
ELSE 0 end) AS debitamount, sum(case when acc_gl_journal_entry.type_enum=1 then
amount ELSE 0 end) AS creditamount
+ FROM acc_gl_account JOIN acc_gl_journal_entry on
acc_gl_account.id = acc_gl_journal_entry.account_id JOIN m_loan m ON m.id =
acc_gl_journal_entry.entity_id
+ JOIN m_product_loan lp ON lp.id = m.product_id WHERE
acc_gl_journal_entry.entity_type_enum = 1 AND acc_gl_journal_entry.manual_entry
= false
+ and acc_gl_journal_entry.submitted_on_date <
'${endDate}' and (acc_gl_journal_entry.office_id = ${officeId}) group by
productname, glcode, glname
+ order by glcode ) debits ON g.gl_code = debits.glcode AND
debits.productname = g.pname LEFT JOIN (SELECT lp.name AS productname,
acc_gl_account.gl_code AS glcode,
+ acc_gl_account.name AS glname, sum(case when
acc_gl_journal_entry.type_enum=2 then amount ELSE 0 END) AS debitamount,
sum(case when acc_gl_journal_entry.type_enum=1 then
+ amount ELSE 0 END) AS creditamount FROM
acc_gl_journal_entry JOIN acc_gl_account on acc_gl_account.id =
acc_gl_journal_entry.account_id JOIN m_loan m ON m.id =
+ acc_gl_journal_entry.entity_id JOIN m_product_loan lp ON
lp.id = m.product_id WHERE acc_gl_journal_entry.entity_type_enum = 1 AND
acc_gl_journal_entry.manual_entry = false
+ and acc_gl_journal_entry.submitted_on_date ='${endDate}'
AND (acc_gl_journal_entry.office_id = ${officeId}) group by productname,
glcode, glname
+ order by glcode ) loanproduct ON g.gl_code =
loanproduct.glcode AND loanproduct.productname = g.pname ) loan UNION SELECT
'${endDate}' AS PostingDate,
+ loan.pname AS Product, loan.gl_code AS GlAcct, loan.glname
AS Description, loan.openingbalance AS Beginning_Balance, loan.debitamount AS
Debit_Movement,
+ loan.creditamount AS Credit_Movement, (loan.openingbalance
+ loan.debitamount - loan.creditamount) AS Ending_Balance FROM (SELECT g.pname,
g.gl_code, g.glname,
+ COALESCE(debits.debitamount, 0) -
COALESCE(debits.creditamount, 0) AS openingbalance,
COALESCE(loanproduct.debitamount, 0) AS debitamount,
COALESCE(loanproduct.creditamount, 0)
+ AS creditamount FROM (SELECT ag.gl_code, pl.name AS
pname,ag.name AS glname FROM acc_gl_account ag JOIN acc_product_mapping am ON
am.gl_account_id = ag.id AND
+ am.product_type = 2 JOIN m_savings_product pl ON pl.id =
am.product_id) g LEFT join (SELECT lp.name productname,acc_gl_account.gl_code
AS glcode,acc_gl_account.name AS
+ glname, sum(case when acc_gl_journal_entry.type_enum=2
then amount ELSE 0 end) AS debitamount, sum(case when
acc_gl_journal_entry.type_enum=1 then amount ELSE 0 end) AS
+ creditamount FROM acc_gl_account join acc_gl_journal_entry
on acc_gl_account.id = acc_gl_journal_entry.account_id JOIN m_savings_account m
ON m.id =
+ acc_gl_journal_entry.entity_id JOIN m_savings_product lp
ON lp.id = m.product_id WHERE acc_gl_journal_entry.entity_type_enum = 2 AND
acc_gl_journal_entry.manual_entry = false
+ and acc_gl_journal_entry.submitted_on_date <
'${endDate}' and (acc_gl_journal_entry.office_id = ${officeId}) group by
productname, glcode, glname
+ order by glcode) debits ON g.gl_code = debits.glcode AND
debits.productname = g.pname left JOIN (SELECT lp.name
productname,acc_gl_account.gl_code AS
+ glcode,acc_gl_account.name AS glname, sum(case when
acc_gl_journal_entry.type_enum=2 then amount ELSE 0 end) AS debitamount,
sum(case when acc_gl_journal_entry.type_enum=1
+ then amount ELSE 0 end) AS creditamount from
acc_gl_journal_entry join acc_gl_account on acc_gl_account.id =
acc_gl_journal_entry.account_id JOIN m_savings_account m ON m.id =
+ acc_gl_journal_entry.entity_id JOIN m_savings_product lp
ON lp.id = m.product_id WHERE acc_gl_journal_entry.entity_type_enum = 2 AND
acc_gl_journal_entry.manual_entry = false
+ and acc_gl_journal_entry.submitted_on_date ='${endDate}'
and (acc_gl_journal_entry.office_id = ${officeId}) group by productname,
glcode, glname
+ order by glcode ) loanproduct ON g.gl_code =
loanproduct.glcode AND loanproduct.productname = g.pname ) loan UNION SELECT
'${endDate}' AS PostingDate,
+ 'manual' AS Product, loan.gl_code AS GlAcct, loan.glname
AS Description, loan.openingbalance AS Beginning_Balance, loan.debitamount AS
Debit_Movement,
+ loan.creditamount AS Credit_Movement, (loan.openingbalance
+ loan.debitamount - loan.creditamount) AS Ending_Balance FROM (SELECT
g.gl_code, g.name AS glname,
+ COALESCE(debits.debitamount, 0) -
COALESCE(debits.creditamount, 0) AS openingbalance,
COALESCE(loanproduct.debitamount, 0) AS debitamount,
COALESCE(loanproduct.creditamount, 0)
+ AS creditamount FROM acc_gl_account g LEFT join (SELECT
acc_gl_account.gl_code AS glcode, acc_gl_account.name AS glname, sum(case when
acc_gl_journal_entry.type_enum=2
+ then amount ELSE 0 end) AS debitamount, sum(case when
acc_gl_journal_entry.type_enum=1 then amount ELSE 0 end) AS creditamount FROM
acc_gl_account JOIN acc_gl_journal_entry on
+ acc_gl_account.id = acc_gl_journal_entry.account_id WHERE
acc_gl_journal_entry.manual_entry = true and
acc_gl_journal_entry.submitted_on_date <
+ '${endDate}' and (acc_gl_journal_entry.office_id =
${officeId}) group by glcode, glname order by glcode) debits ON g.gl_code =
debits.glcode left JOIN (SELECT
+ acc_gl_account.gl_code AS glcode, acc_gl_account.name AS
glname, sum(case when acc_gl_journal_entry.type_enum=2 then amount ELSE 0 end)
AS debitamount, sum(case when
+ acc_gl_journal_entry.type_enum=1 then amount ELSE 0 end)
AS creditamount FROM acc_gl_journal_entry join acc_gl_account on
acc_gl_account.id =
+ acc_gl_journal_entry.account_id where
acc_gl_journal_entry.manual_entry = true and
acc_gl_journal_entry.submitted_on_date ='${endDate}' and
+ (acc_gl_journal_entry.office_id = ${officeId}) group by
glcode, glname order by glcode ) loanproduct ON g.gl_code = loanproduct.glcode
) loan ) a where a.EndingBalance <> 0 "/>
+ <column name="description" value="Trial Balance Summary Report"/>
+ <column name="core_report" valueBoolean="false"/>
+ <column name="use_report" valueBoolean="true"/>
+ <column name="self_service_user_report" valueBoolean="false"/>
+ </insert>
+ </changeSet>
+ <changeSet author="fineract" id="2">
+ <sql>
+ INSERT INTO stretchy_report_parameter (report_id, parameter_id,
report_parameter_name) VALUES
+ ((SELECT id FROM stretchy_report WHERE report_name = 'Trial
Balance Summary Report'), 5, 'officeId');
+ </sql>
+ <sql>
+ INSERT INTO stretchy_report_parameter (report_id, parameter_id,
report_parameter_name) VALUES
+ ((SELECT id FROM stretchy_report WHERE report_name = 'Trial
Balance Summary Report'), 2, 'endDate');
+ </sql>
+ </changeSet>
+ <!-- Transaction Summary report -->
+ <changeSet author="fineract" id="3">
+ <insert tableName="stretchy_report">
+ <column name="report_name" value="Transaction Summary Report"/>
+ <column name="report_type" value="Table"/>
+ <column name="report_subtype"/>
+ <column name="report_category" value="Accounting"/>
+ <column name="report_sql" value="SELECT a.transactiondate AS
TransactionDate, a.product AS Product, a.transaction_type AS TransactionType,
(SELECT enum_message_property FROM r_enum_value
+ WHERE enum_name = 'transaction_type_enum' and enum_id =
a.transaction_type) TransactionType_Name, a.reversal_indicator AS Reversed,
a.Allocation_Type AS Allocation_Type,
+ '' AS Chargeoff_ReasonCode, case when a.transaction_type IN
(2,23,21,22,24,4,5,8,6) AND a.reversal_indicator = false then sum(a.amount)*-1
when a.transaction_type IN
+ (2,23,21,22,24,4,5,8,6) AND a.reversal_indicator = true then
sum(a.amount)*+1 when a.transaction_type IN (1,25,20,26) AND
a.reversal_indicator = false then sum(a.amount)*+1 when
+ a.transaction_type IN (1,25,20,26) AND a.reversal_indicator = true
then sum(a.amount)*-1 end AS Transaction_Amount FROM (SELECT t.transaction_date
AS transactiondate, t.id, l.name AS product,
+ t.transaction_type_enum AS transaction_type, t.is_reversed AS
reversal_indicator, 'Principal' AS Allocation_Type, CASE
t.transaction_type_enum when 1 then t.amount else t.principal_portion_derived
end amount
+ FROM m_loan_transaction t JOIN m_loan m ON m.id = t.loan_id JOIN
m_product_loan l ON l.id = m.product_id WHERE t.submitted_on_date <=
'${endDate}' and (t.office_id = ${officeId}) UNION ALL
+ SELECT t.transaction_date AS transactiondate, t.id, l.name AS
product, t.transaction_type_enum AS transaction_type, t.is_reversed AS
reversal_indicator, 'Interest' AS Allocation_Type,
+ t.interest_portion_derived AS amount FROM m_loan_transaction t
JOIN m_loan m ON m.id = t.loan_id JOIN m_product_loan l ON l.id = m.product_id
WHERE t.submitted_on_date <= '${endDate}' and
+ (t.office_id = ${officeId}) UNION ALL SELECT t.transaction_date AS
transactiondate, t.id, l.name AS product, t.transaction_type_enum AS
transaction_type, t.is_reversed AS reversal_indicator, 'Fees' AS
Allocation_Type,
+ t.fee_charges_portion_derived as amount FROM m_loan_transaction t
JOIN m_loan m ON m.id = t.loan_id JOIN m_product_loan l ON l.id = m.product_id
WHERE t.submitted_on_date <= '${endDate}' and
+ (t.office_id = ${officeId}) UNION ALL SELECT t.transaction_date AS
transactiondate, t.id, l.name AS product, t.transaction_type_enum AS
transaction_type, t.is_reversed AS reversal_indicator, 'Penalty' AS
Allocation_Type,
+ t.penalty_charges_portion_derived as amount FROM
m_loan_transaction t JOIN m_loan m ON m.id = t.loan_id JOIN m_product_loan l ON
l.id = m.product_id WHERE t.submitted_on_date <= '${endDate}' and
+ (t.office_id = ${officeId}) UNION ALL SELECT t.transaction_date AS
transactiondate, t.id, l.name AS product, t.transaction_type_enum AS
transaction_type, t.is_reversed AS reversal_indicator, 'Unallocated Credit
(UNC)'
+ AS Allocation_Type, t.overpayment_portion_derived as amount FROM
m_loan_transaction t JOIN m_loan m ON m.id = t.loan_id JOIN m_product_loan l ON
l.id = m.product_id WHERE t.submitted_on_date <= '${endDate}'
+ and (t.office_id = ${officeId})) a GROUP BY
a.transactiondate,a.product, a.transaction_type, a.reversal_indicator,
a.Allocation_Type "/>
+ <column name="description" value="Transaction Summary Report"/>
+ <column name="core_report" valueBoolean="false"/>
+ <column name="use_report" valueBoolean="true"/>
+ <column name="self_service_user_report" valueBoolean="false"/>
+ </insert>
+ </changeSet>
+ <changeSet author="fineract" id="4">
+ <sql>
+ INSERT INTO stretchy_report_parameter (report_id, parameter_id,
report_parameter_name) VALUES
+ ((SELECT id FROM stretchy_report WHERE report_name = 'Transaction
Summary Report'), 5, 'officeId');
+ </sql>
+ <sql>
+ INSERT INTO stretchy_report_parameter (report_id, parameter_id,
report_parameter_name) VALUES
+ ((SELECT id FROM stretchy_report WHERE report_name = 'Transaction
Summary Report'), 2, 'endDate');
+ </sql>
+ </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 9430ac556..72bfcef1d 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
@@ -42,7 +42,7 @@ public class ReportsTest extends IntegrationTest {
@Test
void listReports() {
- assertThat(ok(fineract().reports.retrieveReportList())).hasSize(124);
+ assertThat(ok(fineract().reports.retrieveReportList())).hasSize(126);
}
@Test
