This is an automated email from the ASF dual-hosted git repository.
victorromero 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 4479196c3 Update dashboard table reports (#2905)
4479196c3 is described below
commit 4479196c3ead2d5a7eb15f13faa1de3f674090e5
Author: José Alberto Hernández
<[email protected]>
AuthorDate: Sat Jan 21 23:25:13 2023 -0600
Update dashboard table reports (#2905)
Co-authored-by: Jose Alberto Hernandez <[email protected]>
---
.../db/changelog/tenant/changelog-tenant.xml | 1 +
.../parts/0087_update_dashboard_table_reports.xml | 163 +++++++++++++++++++++
2 files changed, 164 insertions(+)
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 9c6ed1e14..1812cb54d 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
@@ -106,4 +106,5 @@
<include file="parts/0084_add_general_accounting_table_reports.xml"
relativeToChangelogFile="true" />
<include file="parts/0085_add_aggregate_root_id_external_events.xml"
relativeToChangelogFile="true" />
<include file="parts/0086_add_cob_business_date_to_loan_account_locks.xml"
relativeToChangelogFile="true" />
+ <include file="parts/0087_update_dashboard_table_reports.xml"
relativeToChangelogFile="true" />
</databaseChangeLog>
diff --git
a/fineract-provider/src/main/resources/db/changelog/tenant/parts/0087_update_dashboard_table_reports.xml
b/fineract-provider/src/main/resources/db/changelog/tenant/parts/0087_update_dashboard_table_reports.xml
new file mode 100644
index 000000000..85b6e22f1
--- /dev/null
+++
b/fineract-provider/src/main/resources/db/changelog/tenant/parts/0087_update_dashboard_table_reports.xml
@@ -0,0 +1,163 @@
+<?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">
+ <update tableName="stretchy_report">
+ <column name="report_name" value="Disbursal Vs Awaitingdisbursal"
/>
+ <where>report_name = 'Disbursal_Vs_Awaitingdisbursal'</where>
+ </update>
+ <update tableName="stretchy_report">
+ <column name="report_name" value="Demand Vs Collection" />
+ <where>report_name = 'Demand_Vs_Collection'</where>
+ </update>
+ </changeSet>
+ <!-- ClientTrendsByDay -->
+ <changeSet author="fineract" id="2">
+ <update tableName="stretchy_report">
+ <column name="report_sql" value="SELECT COUNT(cl.id) AS count,
cl.activation_date AS days
+ FROM m_office o LEFT JOIN m_client cl on o.id = cl.office_id
+ WHERE o.hierarchy like concat((select ino.hierarchy from m_office
ino where ino.id = ${officeId}),'%' )
+ AND (cl.activation_date BETWEEN DATE_SUB(CURDATE(), INTERVAL
12 DAY) AND DATE(NOW()- INTERVAL 1 DAY))
+ GROUP BY days
+ "/>
+ <where>id='149' AND report_name = 'ClientTrendsByDay'</where>
+ </update>
+ </changeSet>
+ <!-- ClientTrendsByWeek -->
+ <changeSet author="fineract" id="3">
+ <update tableName="stretchy_report">
+ <column name="report_sql" value="SELECT COUNT(cl.id) AS count,
WEEK(cl.activation_date) AS Weeks
+ FROM m_office o LEFT JOIN m_client cl on o.id = cl.office_id
+ WHERE o.hierarchy like concat((select ino.hierarchy from m_office
ino where ino.id = ${officeId}),'%' )
+ AND (cl.activation_date BETWEEN DATE_SUB(CURDATE(), INTERVAL
12 WEEK) AND DATE(NOW()))
+ GROUP BY Weeks
+ "/>
+ <where>id='150' AND report_name = 'ClientTrendsByWeek'</where>
+ </update>
+ </changeSet>
+ <!-- ClientTrendsByMonth -->
+ <changeSet author="fineract" id="4">
+ <update tableName="stretchy_report">
+ <column name="report_sql" value="SELECT COUNT(cl.id) AS count,
MONTHNAME(cl.activation_date) AS Months
+ FROM m_office o LEFT JOIN m_client cl on o.id = cl.office_id
+ WHERE o.hierarchy like concat((select ino.hierarchy from m_office
ino where ino.id = ${officeId}),'%' )
+ AND (cl.activation_date BETWEEN DATE_SUB(CURDATE(), INTERVAL
12 MONTH) AND DATE(NOW()))
+ GROUP BY Months
+ "/>
+ <where>id='151' AND report_name = 'ClientTrendsByMonth'</where>
+ </update>
+ </changeSet>
+ <!-- LoanTrendsByDay -->
+ <changeSet author="fineract" id="5">
+ <update tableName="stretchy_report">
+ <column name="report_sql" value="SELECT COUNT(ln.id) AS lcount,
ln.disbursedon_date AS days
+ FROM m_office o LEFT JOIN m_client cl on o.id = cl.office_id
+ LEFT JOIN m_loan ln on cl.id = ln.client_id
+ WHERE o.hierarchy like concat((select ino.hierarchy from m_office
ino where ino.id = ${officeId}),'%' )
+ AND (ln.disbursedon_date BETWEEN DATE_SUB(CURDATE(), INTERVAL
12 DAY) AND DATE(NOW()- INTERVAL 1 DAY))
+ GROUP BY days
+ "/>
+ <where>id='152' AND report_name = 'LoanTrendsByDay'</where>
+ </update>
+ </changeSet>
+ <!-- LoanTrendsByWeek -->
+ <changeSet author="fineract" id="6">
+ <update tableName="stretchy_report">
+ <column name="report_sql" value="SELECT COUNT(ln.id) AS lcount,
WEEK(ln.disbursedon_date) AS Weeks
+ FROM m_office o
+ LEFT JOIN m_client cl on o.id = cl.office_id
+ LEFT JOIN m_loan ln on cl.id = ln.client_id
+ WHERE o.hierarchy like concat((select ino.hierarchy from m_office
ino where ino.id = ${officeId}),'%' )
+ AND (ln.disbursedon_date BETWEEN DATE_SUB(CURDATE(), INTERVAL
12 WEEK) AND DATE(NOW()))
+ GROUP BY Weeks
+ "/>
+ <where>id='153' AND report_name = 'LoanTrendsByWeek'</where>
+ </update>
+ </changeSet>
+ <!-- LoanTrendsByMonth -->
+ <changeSet author="fineract" id="7">
+ <update tableName="stretchy_report">
+ <column name="report_sql" value="SELECT COUNT(ln.id) AS lcount,
MONTHNAME(ln.disbursedon_date) AS Months
+ FROM m_office o
+ LEFT JOIN m_client cl on o.id = cl.office_id
+ LEFT JOIN m_loan ln on cl.id = ln.client_id
+ WHERE o.hierarchy like concat((select ino.hierarchy from m_office
ino where ino.id = ${officeId}),'%' )
+ AND (ln.disbursedon_date BETWEEN DATE_SUB(CURDATE(), INTERVAL
12 MONTH) AND DATE(NOW()))
+ GROUP BY Months
+ "/>
+ <where>id='154' AND report_name = 'LoanTrendsByMonth'</where>
+ </update>
+ </changeSet>
+ <!-- Demand Vs Collection -->
+ <changeSet author="fineract" id="8">
+ <update tableName="stretchy_report">
+ <column name="report_sql" value="SELECT
amount.AmountDue-amount.AmountPaid as AmountDue, amount.AmountPaid as
AmountPaid FROM
+ (SELECT
+ (IFNULL(SUM(ls.principal_amount),0) -
IFNULL(SUM(ls.principal_writtenoff_derived),0)
+ + IFNULL(SUM(ls.interest_amount),0) -
IFNULL(SUM(ls.interest_writtenoff_derived),0)
+ - IFNULL(SUM(ls.interest_waived_derived),0)
+ + IFNULL(SUM(ls.fee_charges_amount),0) -
IFNULL(SUM(ls.fee_charges_writtenoff_derived),0)
+ - IFNULL(SUM(ls.fee_charges_waived_derived),0)
+ + IFNULL(SUM(ls.penalty_charges_amount),0) -
IFNULL(SUM(ls.penalty_charges_writtenoff_derived),0)
+ - IFNULL(SUM(ls.penalty_charges_waived_derived),0)
+ ) AS AmountDue,
+ (IFNULL(SUM(ls.principal_completed_derived),0) -
IFNULL(SUM(ls.principal_writtenoff_derived),0) +
IFNULL(SUM(ls.interest_completed_derived),0) -
IFNULL(SUM(ls.interest_writtenoff_derived),0)
+ - IFNULL(SUM(ls.interest_waived_derived),0)
+ + IFNULL(SUM(ls.fee_charges_completed_derived),0) -
IFNULL(SUM(ls.fee_charges_writtenoff_derived),0)
+ - IFNULL(SUM(ls.fee_charges_waived_derived),0)
+ + IFNULL(SUM(ls.penalty_charges_completed_derived),0) -
IFNULL(SUM(ls.penalty_charges_writtenoff_derived),0)
+ - IFNULL(SUM(ls.penalty_charges_waived_derived),0)
+ ) AS AmountPaid
+ FROM m_office o
+ LEFT JOIN m_client cl ON o.id = cl.office_id
+ LEFT JOIN m_loan ln ON cl.id = ln.client_id
+ LEFT JOIN m_loan_repayment_schedule ls ON ln.id = ls.loan_id
+ WHERE
+ (o.hierarchy LIKE CONCAT((SELECT ino.hierarchy FROM m_office ino
WHERE ino.id = ${officeId}),'%'))) as amount
+ "/>
+ <where>id='155' AND report_name = 'Demand Vs Collection'</where>
+ </update>
+ </changeSet>
+ <!-- Disbursal Vs Awaitingdisbursal -->
+ <changeSet author="fineract" id="9">
+ <update tableName="stretchy_report">
+ <column name="report_sql" value="SELECT
awaitinddisbursal.amount-disbursedAmount.amount as amountToBeDisburse,
disbursedAmount.amount as disbursedAmount from
+ (SELECT COUNT(ln.id) AS noOfLoans,
IFNULL(SUM(ln.principal_amount),0) AS amount FROM m_office o
+ LEFT JOIN m_client cl ON cl.office_id = o.id
+ LEFT JOIN m_loan ln ON cl.id = ln.client_id
+ WHERE (ln.loan_status_id=200 OR ln.loan_status_id=300) AND
+ o.hierarchy like concat((select ino.hierarchy from m_office
ino where ino.id = ${officeId}),'%' )
+ ) awaitinddisbursal,
+ (SELECT COUNT(ltrxn.id) as count, IFNULL(SUM(ltrxn.amount),0) as
amount FROM m_office o
+ LEFT JOIN m_client cl ON cl.office_id = o.id
+ LEFT JOIN m_loan ln ON cl.id = ln.client_id
+ LEFT JOIN m_loan_transaction ltrxn ON ln.id = ltrxn.loan_id
+ WHERE ltrxn.is_reversed = 0 AND ltrxn.transaction_type_enum=1 AND
+ o.hierarchy like concat((select ino.hierarchy from m_office
ino where ino.id = ${officeId}),'%' )
+ ) disbursedAmount
+ "/>
+ <where>id='156' AND report_name = 'Disbursal Vs
Awaitingdisbursal'</where>
+ </update>
+ </changeSet>
+</databaseChangeLog>
