[
http://mifosforge.jira.com/browse/MIFOS-2863?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=57773#action_57773
]
Van Mittal-Henkle commented on MIFOS-2863:
------------------------------------------
Looking at #4 from Jeff's list, I have confirmed that the difference between
the two numbers is the whether or not the constraint of only looking at
accounts that are active in bad standing (state 9) is used or not.
Currently in the "Profile of Loans in Arrears"-"Amount in Arrears" entry the
bad standing constraint is not used and for the "Staff Summary"-"Arrears
Amount" the constraint is used.
At the hibernate level, these queries are used:
the "unpaidBalance" below is the "Amount in Arrears" entry for "Profile of
Loans in Arrears"
application/src/main/resources/org/mifos/reports/branchreport/BranchReportLoanArrearsProfileBO.hbm.xml
<sql-query name="branchReport.extractLoansInArrearsForBranch">
<return-scalar column="loansInArrears" type="integer" />
<return-scalar column="clientsInArrears" type="integer" />
<return-scalar column="overDueBalance" type="big_decimal" />
<return-scalar column="unpaidBalance" type="big_decimal" />
<![CDATA[
SELECT COUNT(*) as loansInArrears,
COUNT(DISTINCT la.CUSTOMER_ID) as
clientsInArrears,
ROUND(IFNULL(SUM(COALESCE(OVERDUE_PRINCIPAL,0)),0),3) as overDueBalance,
ROUND(IFNULL(SUM(COALESCE(UNPAID_PRINCIPAL,0)),0),3) as unpaidBalance
FROM
LOAN_ARREARS_AGING la
WHERE
la.OFFICE_ID=:officeId
]]>
</sql-query>
"loanArrearsAmount" below is "Staff Summary"-"Arrears Amount" in the report
application/src/main/resources/org/mifos/reports/branchreport/BranchReportStaffSummaryBO.hbm.xml
<sql-query name="branchReport.extractLoanArrearsAmountForPersonnel">
<return-scalar column="personnelId" type="short"/>
<return-scalar column="loanArrearsAmount" type="big_decimal"/>
<![CDATA[
select
c.loan_officer_id as personnelId,
round(sum(laa.overdue_principal),2) as
loanArrearsAmount
from
customer c,
loan_arrears_aging laa
left join
account acc on acc.account_id=laa.account_id
where
acc.account_state_id in (9)
and
laa.customer_id = c.customer_id
and
c.loan_officer_id in (:personnelIdList)
group by
c.loan_officer_id
]]>
</sql-query>
Running the report for the "Pasay Branch" on the http://ci.mifos.org:8085/mifos
test server I get Staff Summary Arrears amounts which sum to 10,384.6.
Running the "Profile of Loans in Arrears" query as is:
mysql> select ROUND(IFNULL(SUM(COALESCE(OVERDUE_PRINCIPAL,0)),0),3) from
loan_arrears_aging la where la.office_id = 2;
+-------------------------------------------------------+
| ROUND(IFNULL(SUM(COALESCE(OVERDUE_PRINCIPAL,0)),0),3) |
+-------------------------------------------------------+
| 19649.900 |
+-------------------------------------------------------+
Running the "Profile of Loans in Arrears" query with the active in bad standing
constraint added:
mysql> select ROUND(IFNULL(SUM(COALESCE(OVERDUE_PRINCIPAL,0)),0),3) from
loan_arrears_aging la left join account acc on acc.account_id=la.account_id
where la.office_id = 2 and acc.account_state_id in (9);
+-------------------------------------------------------+
| ROUND(IFNULL(SUM(COALESCE(OVERDUE_PRINCIPAL,0)),0),3) |
+-------------------------------------------------------+
| 10384.600 |
+-------------------------------------------------------+
Again, seems like a product decision what to do here, if the queries should be
made the same or not (should each area of the report reporting on something
slightly different or not).
> 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
