This should be work..
please exclude the commented lines.., (start with --)..,
select
--od.name as 'Office',
s.display_name as CRO,
m.display_name as "group_name",
-- m.id as "group_id",
count(DISTINCT l.client_id) as 'Clients',
-- count(DISTINCT l.id) as 'loan_id',
(CASE WHEN SUBSTRING(recurrence,-2)='SU' then 'Sunday' when
SUBSTRING(recurrence,-2)='MO' then 'Monday' WHEN
SUBSTRING(recurrence,-2)='TU' then 'Tuesday' when
SUBSTRING(recurrence,-2)='WE' then 'Wednesday'
when SUBSTRING(recurrence,-2)='TH' then 'Thursday' WHEN
SUBSTRING(recurrence,-2)='FR' then 'Friday' when
SUBSTRING(recurrence,-2)='SA' then 'Saturday' end) as Day
-- l.disbursedon_date,
--gl.LatLong,
--concat (gl.Address,',',gl.Landmark) as 'Meeting Place',
--cv.`code_value` as 'Time'
from m_group m
inner join m_loan l on l.group_id=m.id
-- LEFT JOIN (select office_id,id,name from OFFICE_LIST where
id=${officeId}) od on od.office_id=m.office_id
-- LEFT JOIN GroupLocation gl ON gl.group_id=m.id
-- LEFT JOIN MeetingTime on mt.group_id=m.id and l.disbursedon_date=mt.Date
-- LEFT JOIN m_code_value ON cv.id=mt.MeetingTime_cd_Time
INNER JOIN (select a.id as 'group_id',a.parent_id from m_group a where
a.parent_id is not null)b ON b.group_id=m.id
LEFT JOIN m_calendar_instance ci on ci.entity_id=b.parent_id and
entity_type_enum=4
INNER JOIN m_calendar mc on mc.id=ci.calendar_id
LEFT JOIN m_staff s on s.id=m.staff_id
where ((l.expected_maturedon_date>'2018-02-10') or (l.closedon_date
>'2018-02-10') or
'2018-02-10'<ifnull(l.closedon_date,adddate('2018-02-10',+1))) and
(l.disbursedon_date<='2018-02-10') and (l.loan_status_id=300 or
'2018-02-10'<ifnull(l.closedon_date,adddate('2018-02-10',+1)))
group by m.id
On Sat, Jun 23, 2018 at 1:48 PM, <sif...@skyburgsystems.org> wrote:
> Hi Santosh,
>
>
>
> I have noticed that the select concat queries that run in stretchy reports
> and pentaho do not run in the database directly. They produce errors there.
> All the queries in the pentaho reports in Mifos perform the same way.
>
>
>
> I have a simulation attached for active client loans as at a date report
> that you can test attached for inclusion into fineract.
>
>
>
> I have more if you would like to test and include, if you’re interested.
>
>
>
> Kind regards,
>
>
>
> Sifiso
>
>
>
>
>
> *From:* Santosh Math <sant...@confluxtechnologies.com>
> *Sent:* Saturday, June 23, 2018 2:49 AM
> *To:* mifos-users <mifos-users@lists.sourceforge.net>
> *Cc:* Mifos software development <mifos-develo...@lists.sourceforge.net>;
> Dev <d...@fineract.apache.org>
> *Subject:* Re: [Mifos-users] hi , i have created the DayBook report,
> Active clients on date report and also Demand sheet for for JLG loans .
>
>
>
> Hi Ramesh,
>
>
>
> Could u share the query which I can run in my database?
>
>
>
> Thanks
>
> Santosh
>
>
>
> On Fri 22 Jun, 2018, 11:42 PM Ramesh Ram, <ramramesh1...@gmail.com> wrote:
>
> Hi santhosh..,
>
>
>
> We have modified the database for our business model. So it didn't run in
> demo site.,but you can alter the query for your database.. Some tables
> are varried..but rule is same. Kindly you review your database.
>
>
>
> Thanks.,
>
> Ramesh
>
>
>
> On Fri 22 Jun, 2018, 6:44 PM Santosh Math, <santosh@confluxtechnologies.
> com> wrote:
>
> Hi Ramesh,
>
>
>
> I need to test your following reports on https://demo.openmf.org ( I
> created stretchy reports with same name). However, after running the
> reports , I am not getting any output.
>
>
>
>
>
> 1) Active Query
>
> 2) Demand Sheet Query
>
> 3) DailySheet query.
>
>
>
> Could you check there?
>
>
>
> Thanks
>
> Santosh
>
>
>
> On Fri, Jun 22, 2018 at 12:21 PM, Ramesh Ram <ramramesh1...@gmail.com>
> wrote:
>
> ok Abiy
>
>
>
> On Fri, Jun 22, 2018 at 6:06 AM, Abiy Atsbha <abiy...@gmail.com> wrote:
>
> I will skype you Ramesh.
>
>
>
> On Fri, Jun 22, 2018 at 9:33 AM Ramesh Ram <ramramesh1...@gmail.com>
> wrote:
>
> Hi Abiy,
> I can't understand this concept., Do you want to create a new report or
> Implement the these report., Please elobarate this .
>
> Thanks,
>
> Ramesh
>
>
>
> On Thu, Jun 21, 2018 at 1:00 PM, Abiy Atsbha <abiy...@gmail.com> wrote:
>
> Corrected Mail,
>
>
>
> Dear Ramesh,
>
>
>
> Hey bro, I am glad you are doing this.
> And Since Ed, wants to review this and add it to standard Mifos X report,
> I have BROUGHT you sample DAILY TRANSACTION LIST report of T24-Temenos
> Banking Software, this report was not shipped with it originally, it was
> locally developed. It records all the daily transactions including
> Accounts, Funds Transfers, Teller Transactions, Loan, Deposits & Pastdue
> Loans. All transactions that have effect on the accounting are recorded
> inside this report.
>
>
> I am attaching this report with this mail, so as to help you in designing
> the report layout and its inside components.
>
> Good Luck bro.
>
> Abiy A.
>
>
>
>
>
> On Thu, Jun 21, 2018 at 11:38 AM Ramesh Ram <ramramesh1...@gmail.com>
> wrote:
>
>
> I am glad to on work with you on this process, please let me know the
> timings for our discussions
>
> Thank you,
> Ramesh
>
>
>
> On Wed, Jun 20, 2018 at 3:41 AM, Ed Cable <edca...@mifos.org> wrote:
>
> Can we work on reviewing this and potentially adding as another standard
> report in Mifos X?
>
>
> Thanks,
>
>
> Ed
>
>
>
> On Sat, Jun 16, 2018 at 8:12 PM solomon anaman <solomonanam...@gmail.com>
> wrote:
>
> Hello can you please help me with a report on transactions by a cashiers
> where each cahiers report will be pulled separately?
>
> Thank you
>
>
>
> On Mon, Apr 30, 2018 at 11:16 AM, Ramesh Ram <ramramesh1...@gmail.com>
> wrote:
>
> im happy for your reply. please askme if you have any doubts. i also
> have code for group find group status which is renewal or new group.
>
>
>
> On Mon, Apr 30, 2018 at 6:00 PM, Abiy Atsbha <abiy...@gmail.com> wrote:
>
> Dear Ramosh,
>
>
>
> That is very helpful. I will use it for my production.
>
>
>
> Thanks & Regards,
>
> Abiy A.
>
> Ethiotechs Innovative Solutions
>
>
>
> On Mon, Apr 30, 2018 at 2:59 PM, Ramesh Ram <ramramesh1...@gmail.com>
> wrote:
>
> I have use the mifosx application for jlg loans. so these reports are
> very help full for daily activities.So i'm sharing these code .if you need
> this you will use it.
>
>
>
> Thank you.
>
>
> ------------------------------------------------------------
> ------------------
> Check out the vibrant tech community on one of the world's most
> engaging tech sites, Slashdot.org! http://sdm.link/slashdot
> _______________________________________________
> Mifos-users mailing list
> Mifos-users@lists.sourceforge.net
> https://lists.sourceforge.net/lists/listinfo/mifos-users
>
>
>
>
> ------------------------------------------------------------
> ------------------
> Check out the vibrant tech community on one of the world's most
> engaging tech sites, Slashdot.org! http://sdm.link/slashdot
> _______________________________________________
> Mifos-users mailing list
> Mifos-users@lists.sourceforge.net
> https://lists.sourceforge.net/lists/listinfo/mifos-users
>
>
>
>
> ------------------------------------------------------------
> ------------------
> Check out the vibrant tech community on one of the world's most
> engaging tech sites, Slashdot.org! http://sdm.link/slashdot
> _______________________________________________
> Mifos-users mailing list
> Mifos-users@lists.sourceforge.net
> https://lists.sourceforge.net/lists/listinfo/mifos-users
>
>
>
> ------------------------------------------------------------
> ------------------
> Check out the vibrant tech community on one of the world's most
> engaging tech sites, Slashdot.org! http://sdm.link/slashdot______
> _________________________________________
> Mifos-users mailing list
> Mifos-users@lists.sourceforge.net
> https://lists.sourceforge.net/lists/listinfo/mifos-users
>
>
>
>
> --
>
> *Ed Cable*
>
> President/CEO, Mifos Initiative
>
> edca...@mifos.org | Skype: edcable | Mobile: +1.484.477.8649
>
>
>
> *Collectively Creating a World of 3 Billion Maries | *http://mifos.org
> <http://facebook.com/mifos> <http://www.twitter.com/mifos>
>
>
>
>
> ------------------------------------------------------------
> ------------------
> Check out the vibrant tech community on one of the world's most
> engaging tech sites, Slashdot.org! http://sdm.link/slashdot
> _______________________________________________
> Mifos-users mailing list
> Mifos-users@lists.sourceforge.net
> https://lists.sourceforge.net/lists/listinfo/mifos-users
>
>
>
> ------------------------------------------------------------
> ------------------
> Check out the vibrant tech community on one of the world's most
> engaging tech sites, Slashdot.org! http://sdm.link/slashdot______
> _________________________________________
> Mifos-users mailing list
> Mifos-users@lists.sourceforge.net
> https://lists.sourceforge.net/lists/listinfo/mifos-users
>
>
> ------------------------------------------------------------
> ------------------
> Check out the vibrant tech community on one of the world's most
> engaging tech sites, Slashdot.org! http://sdm.link/slashdot
> _______________________________________________
> Mifos-users mailing list
> Mifos-users@lists.sourceforge.net
> https://lists.sourceforge.net/lists/listinfo/mifos-users
>
>
>
> ------------------------------------------------------------
> ------------------
> Check out the vibrant tech community on one of the world's most
> engaging tech sites, Slashdot.org! http://sdm.link/slashdot______
> _________________________________________
> Mifos-users mailing list
> Mifos-users@lists.sourceforge.net
> https://lists.sourceforge.net/lists/listinfo/mifos-users
>
>
> ------------------------------------------------------------
> ------------------
> Check out the vibrant tech community on one of the world's most
> engaging tech sites, Slashdot.org! http://sdm.link/slashdot
> _______________________________________________
> Mifos-users mailing list
> Mifos-users@lists.sourceforge.net
> https://lists.sourceforge.net/lists/listinfo/mifos-users
>
>
>
>
> ------------------------------------------------------------
> ------------------
> Check out the vibrant tech community on one of the world's most
> engaging tech sites, Slashdot.org! http://sdm.link/slashdot
> _______________________________________________
> Mifos-users mailing list
> Mifos-users@lists.sourceforge.net
> https://lists.sourceforge.net/lists/listinfo/mifos-users
>
>
>
>
>
> --
>
> Thanks & Regards
>
> Santosh Math
>
> *QA Engineer*
>
> *Conflux Technologies Pvt Ltd <http://www.confluxtechnologies.com/>*
>
> | *Office*: +91-080-41208662 |
>
> *Address*: #304, 2nd Floor, 7th Main Road, HRBR Layout 1st Block,
> Bengaluru, Karnataka, 560043 INDIA
>
> ------------------------------------------------------------
> ------------------
> Check out the vibrant tech community on one of the world's most
> engaging tech sites, Slashdot.org! http://sdm.link/slashdot______
> _________________________________________
> Mifos-users mailing list
> Mifos-users@lists.sourceforge.net
> https://lists.sourceforge.net/lists/listinfo/mifos-users
>
> ------------------------------------------------------------
> ------------------
> Check out the vibrant tech community on one of the world's most
> engaging tech sites, Slashdot.org! http://sdm.link/slashdot______
> _________________________________________
> Mifos-users mailing list
> Mifos-users@lists.sourceforge.net
> https://lists.sourceforge.net/lists/listinfo/mifos-users
>
>
>
> ------------------------------
> [image: Avast logo] <https://www.avast.com/antivirus>
>
> This email has been checked for viruses by Avast antivirus software.
> www.avast.com <https://www.avast.com/antivirus>
>
> <#m_2224944549293774661_DAB4FAD8-2DD7-40BB-A1B8-4E2AA1F9FDF2>
>
> ------------------------------------------------------------
> ------------------
> Check out the vibrant tech community on one of the world's most
> engaging tech sites, Slashdot.org! http://sdm.link/slashdot
> _______________________________________________
> Mifos-users mailing list
> Mifos-users@lists.sourceforge.net
> https://lists.sourceforge.net/lists/listinfo/mifos-users
>
>
------------------------------------------------------------------------------
Check out the vibrant tech community on one of the world's most
engaging tech sites, Slashdot.org! http://sdm.link/slashdot
_______________________________________________
Mifos-users mailing list
Mifos-users@lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/mifos-users
