Good day
My apologies thought i had responded the code that i was given it did not
work but i managed to make some changes from the ones that are available to
get the information that i wanted like this reported added some extra
field like ID numbers , Phone Numbers, ETC
select concat(repeat("..",
((LENGTH(ounder.`hierarchy`) - LENGTH(REPLACE(ounder.`hierarchy`, '.',
'')) - 1))), ounder.`name`) as "Office/Branch",
ifnull(cur.display_symbol, l.currency_code) as Currency,
lo.display_name as "Loan Officer",
c.display_name as "Client", c.mobile_no as "Phone Number", c.external_id as
"ID Number", c.id as "Client ID", l.account_no as "Loan Account No.",
l.closedon_date as "Closed Date", l.closedon_userid as "User Id Closed",
pl.`name` as "Product",
f.`name` as Fund, m_loan_repayment_schedule.installment as "Installment",
m_loan_charge.amount_outstanding_derived as "Loan amount not sure",
l.principal_amount as "Loan Amount",
l.annual_nominal_interest_rate as " Annual Nominal Interest Rate",
date(l.disbursedon_date) as "Disbursed Date",
date(l.expected_maturedon_date) as "Expected Matured On",
l.principal_repaid_derived as "Principal Repaid",
l.principal_outstanding_derived as "Principal Outstanding",
laa.principal_overdue_derived as "Principal Overdue",
l.interest_repaid_derived as "Interest Repaid",
l.interest_outstanding_derived as "Interest Outstanding",
laa.interest_overdue_derived as "Interest Overdue",
l.fee_charges_repaid_derived as "Fees Repaid",
l.fee_charges_outstanding_derived as "Fees Outstanding",
laa.fee_charges_overdue_derived as "Fees Overdue",
l.penalty_charges_repaid_derived as "Penalties Repaid",
l.penalty_charges_outstanding_derived as "Penalties Outstanding",
penalty_charges_overdue_derived as "Penalties Overdue"
from m_office o
join m_office ounder on ounder.hierarchy like concat(o.hierarchy, '%')
and ounder.hierarchy like concat('${currentUserHierarchy}', '%')
join m_client c on c.office_id = ounder.id
join m_loan l on l.client_id = c.id
join m_loan_repayment_schedule on l.id = m_loan_repayment_schedule.loan_id
join m_product_loan pl on pl.id = l.product_id
left join m_loan_charge on l.id = m_loan_charge.loan_id
left join m_staff lo on lo.id = l.loan_officer_id
left join m_currency cur on cur.code = l.currency_code
left join m_fund f on f.id = l.fund_id
left join m_loan_arrears_aging laa on laa.loan_id = l.id
where o.id = ${officeId}
and (l.currency_code = "${currencyId}" or "-1" = "${currencyId}")
and (l.product_id = "${loanProductId}" or "-1" = "${loanProductId}")
and (ifnull(l.loan_officer_id, -10) = "${loanOfficerId}" or "-1" =
"${loanOfficerId}")
and (ifnull(l.fund_id, -10) = ${fundId} or -1 = ${fundId})
and (ifnull(l.loanpurpose_cv_id, -10) = ${loanPurposeId} or -1 =
${loanPurposeId})
and l.loan_status_id = 300
group by l.id
order by ounder.hierarchy, l.currency_code, c.account_no, l.account_no
But in the current report code that i have shared i could not find the
database field name that shows the mostly installment rather there is only
the installment ID .
Best Regards
On Wed, Jan 26, 2022 at 10:32 AM Airsay Longcon <airsaylong...@gmail.com>
wrote:
> Tinotenda
> It will be great to get feedback about the custom report Bharat shared and
> whether it worked for your use case
>
> On 25 Jan 2022, at 09:17, Bharath Gowda <bgo...@mifos.org> wrote:
>
>
> Hi,
>
> Thank you for listing down your query here,
> OOB there are no reports to show the data you need, but you could easily
> create a custom report for the same.
>
> Refer to this document to help you in creating a stretchy custom report
> https://mifosforge.jira.com/wiki/spaces/docs/pages/67141770/Custom+Reports
>
> Enter the below parameter names
>
> Report Name: Matured Loans
> Report Type: Table
> Use Report: check this box
> Sql: use the query given below
> Report Parameters: do not enter anything
>
>
>
>
>
> Use the below query in the SQL section of the custom report to get the
> desired output
>
> "SELECT o.name,c.display_name,m.account_no,p.name
> ,m.principal_disbursed_derived,m.interest_charged_derived,
> m.principal_repaid_derived,m.interest_repaid_derived
> FROM m_loan m
> JOIN m_client c ON c.id = m.client_id
> JOIN m_product_loan p ON p.id = m.product_id
> JOIN m_office o ON o.id = c.office_id
> WHERE m.loan_status_id = 600"
>
> Note: you can modify the column outputs you need as per your requirements.
>
>
>
> Regards,
> Bharath
> Lead Implementation Analyst | Mifos Initiative
> Skype: live:cbharath4| Mobile: +91.7019635592
> http://mifos.org <http://facebook.com/mifos>
> <http://www.twitter.com/mifos>
>
>
> On Mon, Jan 24, 2022 at 8:23 PM tinotenda pfidze <pfidze...@gmail.com>
> wrote:
>
>> Good Day
>>
>> Am running mifos x build 18.03.01 and need help on how to view a report
>> of the loans that have been fully paid / or loans that have matured in the
>> system
>>
>> Regards
>>
>>
>> _______________________________________________
>> Mifos-users mailing list
>> Mifos-users@lists.sourceforge.net
>> https://lists.sourceforge.net/lists/listinfo/mifos-users
>>
> _______________________________________________
> Mifos-users mailing list
> Mifos-users@lists.sourceforge.net
> https://lists.sourceforge.net/lists/listinfo/mifos-users
>
> _______________________________________________
> Mifos-users mailing list
> Mifos-users@lists.sourceforge.net
> https://lists.sourceforge.net/lists/listinfo/mifos-users
>
_______________________________________________
Mifos-users mailing list
Mifos-users@lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/mifos-users
