Hello Lvo,
The group need to be linked to the client with the help of the group client
mapping table m_group_client
it can be added as
m_client c
join m_group_client mgc on mgc.client_id = c.id
join m_group mg on mg.id = mgc.group_id
the you can select the columns from the group which need to be displayed
note :- join will be returning only the client that are mapped to group
you can use left join where there are total clients are not mapped to
groups.
On Wed, Feb 3, 2016 at 1:19 AM Ed Cable <[email protected]> wrote:
> 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
>
--
Thanks and Regards,
Venkat
Skype Id: venkat.ganeshconflux
------------------------------------------------------------------------------
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=272487151&iu=/4140
_______________________________________________
Mifos-users mailing list
[email protected]
https://lists.sourceforge.net/lists/listinfo/mifos-users
