|
||||||||
|
This message is automatically generated by JIRA. If you think it was sent incorrectly, please contact your JIRA administrators For more information on JIRA, see: http://www.atlassian.com/software/jira |
||||||||
------------------------------------------------------------------------------ Free Next-Gen Firewall Hardware Offer Buy your Sophos next-gen firewall before the end March 2013 and get the hardware for free! Learn more. http://p.sf.net/sfu/sophos-d2d-feb
_______________________________________________ Mifos-issues mailing list [email protected] https://lists.sourceforge.net/lists/listinfo/mifos-issues
Thanks Sughosh... some of this already mentioned in an email
There is already a loan product parameter (loanProductIdSelectAll) in the base data for report.
The sql that picks up offices/branches that are allowed for the user under the office selected needs to be added
also, and here's where Keith can chime in if I get the domain logic wrong.
A write-off transaction writes off the remaining outstanding amounts and can't be undone. That means there is one write-off transaction per written off loan and that write-off transaction may have a note against it (with the reason in it).
So I think the sql needs to reflect that rather than look at the the derived fields on the repayment schedule (its harder to get the note from there too).
Also,
add office name to display,
order by office hierarchy, loan account no
use written off date in relation to the start and end dates
I think the sql below (based on yours) meets all these needs... if so you can resolve the issue
SELECT o.`name` as "Office/Branch", c.display_name AS 'Client Name',
{currentUserHierarchy}ml.account_no AS 'Account Number',
mpl.name AS 'Product Name',
ml.disbursedon_date AS 'Disbursed Date',
lt.transaction_date AS 'Written Off date',
lt.principal_portion_derived AS 'Written-Off Principal',
lt.interest_portion_derived AS 'Written-Off Interest',
n.note AS 'Reason',
IFNULL(ms.display_name,'-') AS 'Loan Officer Name'
FROM m_office o
JOIN m_office ounder ON ounder.hierarchy like concat(o.hierarchy, '%')
AND ounder.hierarchy like CONCAT('$
', '%')
{officeId}JOIN m_client c ON c.office_id = ounder.id
JOIN m_loan ml ON ml.client_id = c.id
JOIN m_product_loan mpl ON mpl.id=ml.product_id
LEFT JOIN m_staff ms ON ms.id=ml.loan_officer_id
JOIN m_loan_transaction lt ON lt.loan_id = ml.id
LEFT JOIN m_note n ON n.loan_transaction_id = lt.id
WHERE lt.transaction_type_enum = 6 /*write-off */
AND ml.loan_status_id=601
AND o.id=$
AND (mpl.id=$
{loanProductId} OR ${loanProductId}=-1)
{startDate}AND lt.transaction_date BETWEEN '$
' AND '$
{endDate}'
ORDER BY ounder.hierarchy, ml.account_no