Hello Ivo,
I'm forwarding your email to our users' mailing list and Sangamesh, our
support lead, to help you troubleshoot.
Please also post a question to our Gitter chatroom (
https://gitter.im/openMF/mifos
<https://web.chilipiper.com/link/mifos.org/56b0f43fe4b02bbe46268722?link=aHR0cHMlM0ElMkYlMkZnaXR0ZXIuaW0lMkZvcGVuTUYlMkZtaWZvcw==>)
if you have questions.
Ed
On Mon, Feb 1, 2016 at 9:24 AM, Ivo Meijers <[email protected]
<https://web.chilipiper.com/link/mifos.org/56b0f43fe4b02bbe46268722?link=bWFpbHRvJTNBaW1laWplcnMlNDBtdGdjYXBpdGFsLmNo>
> wrote:
> Hello!
>
>
> Ive been struggling with Sql code for a report ive created.
>
> I thought maybe you could direct me to someone who could help me with this
> issue?
>
> The code for the report currently looks like this: select
> concat(repeat("..",
> ((LENGTH(ounder.`hierarchy`) - LENGTH(REPLACE(ounder.`hierarchy`, '.',
> '')) - 1))), ounder.`name`) as "Office/Branch",
>
> c.mobile_no 'Mobile No',
> c.display_name as "Client",
> l.principal_amount as "Loan Principal Amount",
> date(l.disbursedon_date) as "Disbursed Date",
>
> l.total_expected_repayment_derived as "Total Loan (P+I+F+Pen)",
> l.total_repayment_derived as "Total Repaid (P+I+F+Pen)",
> lo.display_name as "Loan Officer"
>
> 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
> <https://web.chilipiper.com/link/mifos.org/56b0f43fe4b02bbe46268722?link=aHR0cCUzQSUyRiUyRm91bmRlci5pZA==>
> join m_loan l on l.client_id = c.id
> <https://web.chilipiper.com/link/mifos.org/56b0f43fe4b02bbe46268722?link=aHR0cCUzQSUyRiUyRmMuaWQ=>
> join m_product_loan pl on pl.id
> <https://web.chilipiper.com/link/mifos.org/56b0f43fe4b02bbe46268722?link=aHR0cCUzQSUyRiUyRnBsLmlk>
> = l.product_id
> left join m_staff lo on lo.id
> <https://web.chilipiper.com/link/mifos.org/56b0f43fe4b02bbe46268722?link=aHR0cCUzQSUyRiUyRmxvLmlk>
> = l.loan_officer_id
> left join m_currency cur on cur.code = l.currency_code
> left join m_fund f on f.id
> <https://web.chilipiper.com/link/mifos.org/56b0f43fe4b02bbe46268722?link=aHR0cCUzQSUyRiUyRmYuaWQ=>
> = l.fund
> <https://web.chilipiper.com/link/mifos.org/56b0f43fe4b02bbe46268722?link=aHR0cCUzQSUyRiUyRmwuZnVuZA==>
> _id
> left join m_loan_arrears_aging laa on laa.loan_id = l.id
> <https://web.chilipiper.com/link/mifos.org/56b0f43fe4b02bbe46268722?link=aHR0cCUzQSUyRiUyRmwuaWQ=>
> where o.id
> <https://web.chilipiper.com/link/mifos.org/56b0f43fe4b02bbe46268722?link=aHR0cCUzQSUyRiUyRm8uaWQ=>
> = ${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.disbursedon_date between '${startDate}' and '${endDate}'
> and l.loan_status_id = 300
> group by l.id
> <https://web.chilipiper.com/link/mifos.org/56b0f43fe4b02bbe46268722?link=aHR0cCUzQSUyRiUyRmwuaWQ=>
> order by ounder.hierarchy, l.currency_code, c.account_no, l.account_no
>
>
>
> Ive been searching internet with no results so i really do hope for some
> help.
> I need to add to this report one crucial row - a row which would showe me
> to which group the customer belongs.
>
> Thank you very much for your time and effort.
> Help would be greatly appreciated.
>
> Regards,
> Ivo
>
--
*Ed Cable*
Director of Community Programs, Mifos Initiative
[email protected]
<https://web.chilipiper.com/link/mifos.org/56b0f43fe4b02bbe46268722?link=bWFpbHRvJTNBZWRjYWJsZSU0MG1pZm9zLm9yZw==>
|
Skype: edcable | Mobile: +1.484.477.8649
*Collectively Creating a World of 3 Billion Maries | *http://mifos.org
<https://web.chilipiper.com/link/mifos.org/56b0f43fe4b02bbe46268722?link=aHR0cCUzQSUyRiUyRm1pZm9zLm9yZw==>
<https://web.chilipiper.com/link/mifos.org/56b0f43fe4b02bbe46268722?link=aHR0cCUzQSUyRiUyRmZhY2Vib29rLmNvbSUyRm1pZm9z>
<https://web.chilipiper.com/link/mifos.org/56b0f43fe4b02bbe46268722?link=aHR0cCUzQSUyRiUyRnd3dy50d2l0dGVyLmNvbSUyRm1pZm9z>
------------------------------------------------------------------------------
Site24x7 APM Insight: Get Deep Visibility into Application Performance
APM + Mobile APM + RUM: Monitor 3 App instances at just $35/Month
Monitor end-to-end web transactions and take corrective actions now
Troubleshoot faster and improve end-user experience. Signup Now!
http://pubads.g.doubleclick.net/gampad/clk?id=267308311&iu=/4140
_______________________________________________
Mifos-users mailing list
[email protected]
https://lists.sourceforge.net/lists/listinfo/mifos-users
