[
http://mifosforge.jira.com/browse/MIFOS-2863?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=57777#action_57777
]
Van Mittal-Henkle commented on MIFOS-2863:
------------------------------------------
Looking at #2 and running the report for the "Pasay Branch" on the
http://ci.mifos.org:8085/mifos test server, yielded the following:
Profile of Loans in Arrears
Loans in Arrears
Clients in Arrears 16
Loans in Arrears 18
Amount in Arrears 19649.90
Amount Outstanding in Arrears 44071.60
Aging of Loans in Arrears
# Clients, # Loans, Amount, Interest, Amt Out
Total 12, 13, 11771.50, 836.50, 28555.40
The difference here is due to what customers are being counted in each section.
"Profile of Loans in Arrears" includes all customers (both clients and groups)
in any state.
"Aging of Loans in Arrears" includes only clients with status 'Customer Was
Active' (value 3).
Expanding query for "Profile of Loans in Arrears" counting of clients with some
added information we get:
mysql> select distinct(la.customer_id), cust.status_id, cust.customer_level_id
from loan_arrears_aging la left join customer cust on
cust.customer_id=la.customer_id where la.office_id = 2;
+-------------+-----------+-------------------+
| customer_id | status_id | customer_level_id |
+-------------+-----------+-------------------+
| 43 | 3 | 1 |
| 39 | 3 | 1 |
| 30 | 3 | 1 |
| 45 | 6 | 1 |
| 51 | 3 | 1 |
| 3 | 3 | 1 |
| 60 | 3 | 1 |
| 58 | 3 | 1 |
| 44 | 4 | 1 |
| 49 | 3 | 1 |
| 54 | 3 | 1 |
| 46 | 9 | 2 |
| 156 | 3 | 1 |
| 57 | 3 | 1 |
| 34 | 3 | 1 |
| 27 | 9 | 2 |
+-------------+-----------+-------------------+
16 rows in set (0.00 sec)
This includes 2 groups (customer_ids 46 and 27).
Adding the constraint for groups, gets us down to 14:
mysql> select distinct(la.customer_id), cust.status_id, cust.customer_level_id
from loan_arrears_aging la left join customer cust on
cust.customer_id=la.customer_id where cust.customer_level_id=1 and la.office_id
= 2;
+-------------+-----------+-------------------+
| customer_id | status_id | customer_level_id |
+-------------+-----------+-------------------+
| 43 | 3 | 1 |
| 39 | 3 | 1 |
| 30 | 3 | 1 |
| 45 | 6 | 1 |
| 51 | 3 | 1 |
| 3 | 3 | 1 |
| 60 | 3 | 1 |
| 58 | 3 | 1 |
| 44 | 4 | 1 |
| 49 | 3 | 1 |
| 54 | 3 | 1 |
| 156 | 3 | 1 |
| 57 | 3 | 1 |
| 34 | 3 | 1 |
+-------------+-----------+-------------------+
14 rows in set (0.00 sec)
Constraining the status to be 'Customer Was Active' (3) we get down to 12:
mysql> select distinct(la.customer_id), cust.status_id, cust.customer_level_id
from loan_arrears_aging la left join customer cust on
cust.customer_id=la.customer_id where cust.status_id = 3 and
cust.customer_level_id=1 and la.office_id = 2;
+-------------+-----------+-------------------+
| customer_id | status_id | customer_level_id |
+-------------+-----------+-------------------+
| 43 | 3 | 1 |
| 39 | 3 | 1 |
| 30 | 3 | 1 |
| 51 | 3 | 1 |
| 3 | 3 | 1 |
| 60 | 3 | 1 |
| 58 | 3 | 1 |
| 49 | 3 | 1 |
| 54 | 3 | 1 |
| 156 | 3 | 1 |
| 57 | 3 | 1 |
| 34 | 3 | 1 |
+-------------+-----------+-------------------+
12 rows in set (0.01 sec)
For reference, states (statuses) that apply to clients are:
mysql> select * from customer_state;
+-----------+------------------+----------+-----------------------+------------------+
| STATUS_ID | STATUS_LOOKUP_ID | LEVEL_ID | DESCRIPTION |
CURRENTLY_IN_USE |
+-----------+------------------+----------+-----------------------+------------------+
| 1 | 1 | 1 | Customer Was Partial |
1 |
| 2 | 2 | 1 | Customer Was Pending |
1 |
| 3 | 3 | 1 | Customer Was Active |
1 |
| 4 | 4 | 1 | Customer Was Hold |
1 |
| 5 | 5 | 1 | Customer Was Cancel |
1 |
| 6 | 6 | 1 | Customer Was Close |
1 |
The differences in the totals are the result of the difference in which
customers are being summed up.
So the question is which customers do we want to include in these two sets of
totals and should they be the same set of customers.
> Branch Progress Report has several incorrect values
> ---------------------------------------------------
>
> Key: MIFOS-2863
> URL: http://mifosforge.jira.com/browse/MIFOS-2863
> Project: mifos
> Issue Type: Bug
> Components: Reports Module
> Reporter: emilytucker
> Assignee: Van Mittal-Henkle
> Priority: Major
> Fix For: Shamim D
>
> Attachments: Branch Progress Report.doc, Branch_report.txt,
> PasayBranchProgressReport.pdf
>
>
> See attached report from Secdep. There are several problems:
> (*) Number of staff not displaying correctly
> (*) The arrears break-down by staff is not correct-- it also doesn't add up
> to the total arrears in the upper section of the report
> (*) Summary of amount of loans and # of clients in arrears does not match the
> arrears aging table.
--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
http://mifosforge.jira.com/secure/Administrators.jspa
-
For more information on JIRA, see: http://www.atlassian.com/software/jira
------------------------------------------------------------------------------
_______________________________________________
Mifos-issues mailing list
[email protected]
https://lists.sourceforge.net/lists/listinfo/mifos-issues
