You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@fineract.apache.org by vi...@apache.org on 2023/01/22 22:42:08 UTC
[fineract] branch develop updated: Update dashboard table reports for postgresql (#2907)
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 d0ec63d15 Update dashboard table reports for postgresql (#2907)
d0ec63d15 is described below
commit d0ec63d1501c8d7f94516d9f20eda4095c7d0553
Author: José Alberto Hernández <44...@users.noreply.github.com>
AuthorDate: Sun Jan 22 16:42:01 2023 -0600
Update dashboard table reports for postgresql (#2907)
Co-authored-by: Jose Alberto Hernandez <al...@MacBook-Pro.local>
---
.../parts/0087_update_dashboard_table_reports.xml | 129 ++++++++++++++++-----
1 file changed, 103 insertions(+), 26 deletions(-)
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
index 85b6e22f1..447bec969 100644
--- 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
@@ -33,7 +33,7 @@
</update>
</changeSet>
<!-- ClientTrendsByDay -->
- <changeSet author="fineract" id="2">
+ <changeSet author="fineract" id="22" context="mysql">
<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
@@ -45,7 +45,7 @@
</update>
</changeSet>
<!-- ClientTrendsByWeek -->
- <changeSet author="fineract" id="3">
+ <changeSet author="fineract" id="3" context="mysql">
<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
@@ -57,7 +57,7 @@
</update>
</changeSet>
<!-- ClientTrendsByMonth -->
- <changeSet author="fineract" id="4">
+ <changeSet author="fineract" id="4" context="mysql">
<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
@@ -69,20 +69,20 @@
</update>
</changeSet>
<!-- LoanTrendsByDay -->
- <changeSet author="fineract" id="5">
+ <changeSet author="fineract" id="25" context="mysql">
<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))
+ 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">
+ <changeSet author="fineract" id="6" context="mysql">
<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
@@ -96,38 +96,115 @@
</update>
</changeSet>
<!-- LoanTrendsByMonth -->
- <changeSet author="fineract" id="7">
+ <changeSet author="fineract" id="27" context="mysql">
<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()))
+ AND (ln.disbursedon_date BETWEEN (CURDATE() - INTERVAL 12 MONTH) AND DATE(NOW()))
+ GROUP BY Months
+ "/>
+ <where>id='154' AND report_name = 'LoanTrendsByMonth'</where>
+ </update>
+ </changeSet>
+ <!-- ClientTrendsByDay -->
+ <changeSet author="fineract" id="29" context="postgresql">
+ <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 (current_date - 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="10" context="postgresql">
+ <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 (current_date - INTERVAL '12 WEEK') AND DATE(NOW()))
+ GROUP BY Weeks
+ "/>
+ <where>id='150' AND report_name = 'ClientTrendsByWeek'</where>
+ </update>
+ </changeSet>
+ <!-- ClientTrendsByMonth -->
+ <changeSet author="fineract" id="11" context="postgresql">
+ <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 (current_date - INTERVAL '12 MONTH') AND DATE(NOW()))
+ GROUP BY Months
+ "/>
+ <where>id='151' AND report_name = 'ClientTrendsByMonth'</where>
+ </update>
+ </changeSet>
+ <!-- LoanTrendsByDay -->
+ <changeSet author="fineract" id="12" context="postgresql">
+ <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 (current_date - 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="13" context="postgresql">
+ <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 (current_date - INTERVAL '12 WEEK') AND DATE(NOW()))
+ GROUP BY Weeks
+ "/>
+ <where>id='153' AND report_name = 'LoanTrendsByWeek'</where>
+ </update>
+ </changeSet>
+ <!-- LoanTrendsByMonth -->
+ <changeSet author="fineract" id="14" context="postgresql">
+ <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 (current_date - 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">
+ <changeSet author="fineract" id="15">
<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)
+ (COALESCE(SUM(ls.principal_amount),0) - COALESCE(SUM(ls.principal_writtenoff_derived),0)
+ + COALESCE(SUM(ls.interest_amount),0) - COALESCE(SUM(ls.interest_writtenoff_derived),0)
+ - COALESCE(SUM(ls.interest_waived_derived),0)
+ + COALESCE(SUM(ls.fee_charges_amount),0) - COALESCE(SUM(ls.fee_charges_writtenoff_derived),0)
+ - COALESCE(SUM(ls.fee_charges_waived_derived),0)
+ + COALESCE(SUM(ls.penalty_charges_amount),0) - COALESCE(SUM(ls.penalty_charges_writtenoff_derived),0)
+ - COALESCE(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)
+ (COALESCE(SUM(ls.principal_completed_derived),0) - COALESCE(SUM(ls.principal_writtenoff_derived),0) + COALESCE(SUM(ls.interest_completed_derived),0) - COALESCE(SUM(ls.interest_writtenoff_derived),0)
+ - COALESCE(SUM(ls.interest_waived_derived),0)
+ + COALESCE(SUM(ls.fee_charges_completed_derived),0) - COALESCE(SUM(ls.fee_charges_writtenoff_derived),0)
+ - COALESCE(SUM(ls.fee_charges_waived_derived),0)
+ + COALESCE(SUM(ls.penalty_charges_completed_derived),0) - COALESCE(SUM(ls.penalty_charges_writtenoff_derived),0)
+ - COALESCE(SUM(ls.penalty_charges_waived_derived),0)
) AS AmountPaid
FROM m_office o
LEFT JOIN m_client cl ON o.id = cl.office_id
@@ -140,20 +217,20 @@
</update>
</changeSet>
<!-- Disbursal Vs Awaitingdisbursal -->
- <changeSet author="fineract" id="9">
+ <changeSet author="fineract" id="16">
<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
+ (SELECT COUNT(ln.id) AS noOfLoans, COALESCE(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
+ (SELECT COUNT(ltrxn.id) as count, COALESCE(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
+ WHERE ltrxn.is_reversed = false AND ltrxn.transaction_type_enum=1 AND
o.hierarchy like concat((select ino.hierarchy from m_office ino where ino.id = ${officeId}),'%' )
) disbursedAmount
"/>