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.
select
'${startDate}' AS Date, 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 mt on mt.group_id=m.id and l.disbursedon_date=mt.Date
LEFT JOIN m_code_value cv 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 m.office_id in (od.office_id) and
((l.expected_maturedon_date>'${startDate}') or (l.closedon_date
>'${startDate}') or
'${startDate}'<ifnull(l.closedon_date,adddate('${startDate}',+1))) and
(l.disbursedon_date<='${startDate}') and (l.loan_status_id=300
or'${startDate}'<ifnull(l.closedon_date,adddate('${startDate}',+1)))
group by m.id
UNION
select
'TOTAL',
NULL,
NULL,
COUNT(DISTINCT m.id) as "groups",
count(DISTINCT l.client_id) as 'Clients',
NULL,
-- m.id as "group_id",
-- count(DISTINCT l.id) as 'loan_id',
NULL,
-- l.disbursedon_date,
NULL, null
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 mt on mt.group_id=m.id and l.disbursedon_date=mt.Date
LEFT JOIN m_code_value cv 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 m.office_id in (od.office_id) and
((l.expected_maturedon_date>'${startDate}') or (l.closedon_date
>'${startDate}') or
'${startDate}'<ifnull(l.closedon_date,adddate('${startDate}',+1))) and
(l.disbursedon_date<='${startDate}') and (l.loan_status_id=300
or'${startDate}'<ifnull(l.closedon_date,adddate('${startDate}',+1)))
group by od.idSELECT
a.Details,a.`Incoming Credit`,FORMAT(a.`Outgoing Debit`,0) as 'Outgoing Debit'
from
(
SELECT NULL,a.txs-b.ot as 'Incoming Credit','0' as 'Outgoing Debit',o.id as
office_id, '${startDate}','Opening Balance' as Details
from m_office o
LEFT JOIN
(SELECT a.*,sum(amount) as 'txs'
from
(
SELECT
jl.account_id,
SUM(jl.amount) as amount,
od.id as office_id,
max(jl.entry_date) entry_date
FROM acc_gl_journal_entry jl
inner join acc_gl_account account_type on jl.account_id=account_type.id
INNER JOIN (select office_id,id from OFFICE_LIST where id=${officeId}) od on
od.office_id=jl.office_id
WHERE jl.office_id in (od.office_id) and jl.account_id IN (99,87,157,176,177)
and manual_entry=1 and type_enum=1 and account_type.classification_enum in
(2,4,1) and jl.reversed=0
and jl.entry_date between '2017-03-31' and ADDDATE( '${startDate}',-1)
GROUP BY od.id
UNION
SELECT
jl.account_id,
SUM(jl.amount) as amount,
od.id as office_id,
max(jl.entry_date) entry_date
FROM acc_gl_journal_entry jl
LEFT JOIN m_loan_transaction mlt ON jl.loan_transaction_id=mlt.id
LEFT JOIN m_loan ml ON ml.id=mlt.loan_id
INNER JOIN (select office_id,id from OFFICE_LIST where id=${officeId}) od on
od.office_id=jl.office_id
WHERE jl.office_id in (od.office_id) and jl.account_id IN (5,6,94,95,84,92)
AND jl.type_enum=1 and jl.reversed=0
and jl.entry_date between '2017-03-31' and '2017-12-01' and ADDDATE(
'${startDate}',-1)>='2017-12-01' and mlt.is_reversed!=1 and
ml.loan_status_id!=500
GROUP BY od.id
UNION
select 6,7335,37,'2017-04-24' from (select office_id,id from OFFICE_LIST where
id=${officeId})a where a.office_id like '%37%' and '2017-04-24'<=ADDDATE(
'${startDate}',-1)
UNION
select 6,7335,37,'2017-04-24' from (select office_id,id from OFFICE_LIST where
id=${officeId})a where a.office_id like '%37%' and '2017-04-24'<=ADDDATE(
'${startDate}',-1)
UNION
select 6,9780,37,'2017-04-20' from (select office_id,id from OFFICE_LIST where
id=${officeId})a where a.office_id like '%37%' and '2017-04-20'<=ADDDATE(
'${startDate}',-1)
UNION
select 6,7335,37,'2017-05-05' from (select office_id,id from OFFICE_LIST where
id=${officeId})a where a.office_id like '%37%' and '2017-05-05'<=ADDDATE(
'${startDate}',-1)
UNION
select 6,8313,37,'2017-05-15' from (select office_id,id from OFFICE_LIST where
id=${officeId})a where a.office_id like '%37%' and '2017-05-15'<=ADDDATE(
'${startDate}',-1)
UNION
select 6,7335,37,'2017-05-18' from (select office_id,id from OFFICE_LIST where
id=${officeId})a where a.office_id like '%37%' and '2017-05-18'<=ADDDATE(
'${startDate}',-1)
UNION
SELECT
jl.account_id,
SUM(jl.amount) as amount,
od.id as office_id,
max(jl.entry_date) entry_date
FROM acc_gl_journal_entry jl
LEFT JOIN m_loan_transaction mlt ON jl.loan_transaction_id=mlt.id
LEFT JOIN m_loan ml ON ml.id=mlt.loan_id
INNER JOIN (select office_id,id from OFFICE_LIST where id=${officeId}) od on
od.office_id=jl.office_id
WHERE jl.office_id in (od.office_id) and jl.account_id IN (7,5,6,94,95,84,92)
AND jl.type_enum=1 and jl.reversed=0
and jl.entry_date between '2017-12-02' and ADDDATE( '${startDate}',-1) and
'${startDate}'>='2017-12-02' and mlt.is_reversed!=1 and ml.loan_status_id!=500
GROUP BY od.id) a
where a.entry_date< '${startDate}')a on a.office_id=o.id
LEFT JOIN (select b.*,sum(amount) as ot
from (
select
jl.account_id,
SUM(jl.amount) AS amount,
od.id as office_id,max(jl.entry_date) entry_date
FROM acc_gl_journal_entry jl
inner join acc_gl_account account_type on jl.account_id=account_type.id
INNER JOIN (select office_id,id from OFFICE_LIST where id=${officeId}) od on
od.office_id=jl.office_id
WHERE jl.office_id in (od.office_id) and manual_entry=1 and type_enum=2 and
jl.account_id!=85 and jl.reversed=0 and (jl.account_id=99 or jl.account_id=142
or jl.account_id=120 or jl.account_id=177 or
account_type.classification_enum=2 or account_type.classification_enum=5) and
jl.entry_date between '2017-03-31' and ADDDATE( '${startDate}',-1)
GROUP BY od.id
UNION
SELECT
jl.account_id,
SUM(jl.amount) as amount,
od.id as office_id,
max(jl.entry_date) entry_date
FROM acc_gl_journal_entry jl
LEFT JOIN m_loan_transaction mlt ON jl.loan_transaction_id=mlt.id
LEFT JOIN m_loan ml ON ml.id=mlt.loan_id
INNER JOIN (select office_id,id from OFFICE_LIST where id=${officeId}) od on
od.office_id=jl.office_id
WHERE jl.office_id in (od.office_id) and (jl.account_id=2 or jl.account_id=92)
and jl.reversed=0 AND jl.type_enum=1 and ml.loan_status_id!=500 and
mlt.is_reversed!=1
and jl.entry_date between '2017-03-31' and ADDDATE( '${startDate}',-1)
GROUP BY od.id)b
where entry_date< '${startDate}') b on b.office_id=o.id
where o.id=${officeId}
GROUP BY o.id, '${startDate}'
UNION
select a.* from
(
SELECT d.account_id,d.amount as Credit,'0' as
'Debit',d.office_id,d.entry_date,d.details from
(SELECT
jl.account_id,
SUM(jl.amount) as amount,
od.id as office_id,
(jl.entry_date) entry_date,
'Inbound Cash Transfer' as details
FROM acc_gl_journal_entry jl
inner join acc_gl_account account_type on jl.account_id=account_type.id
INNER JOIN (select office_id,id from OFFICE_LIST where id=${officeId}) od on
od.office_id=jl.office_id
WHERE jl.office_id in (od.office_id) and jl.account_id IN (99,87,157,176,177)
and manual_entry=1 and type_enum=1 and account_type.classification_enum in
(2,4,1) and jl.reversed=0
and jl.entry_date = '${startDate}'
GROUP BY od.id
UNION
SELECT
jl.account_id,
SUM(jl.amount) as amount,
od.id as office_id,
(jl.entry_date) entry_date,
CONCAT('Collection for ',mg.display_name) as details
FROM acc_gl_journal_entry jl
LEFT JOIN m_loan_transaction mlt ON jl.loan_transaction_id=mlt.id
LEFT JOIN m_loan ml ON ml.id=mlt.loan_id
LEFT JOIN m_group mg on mg.id=ml.group_id
INNER JOIN (select office_id,id from OFFICE_LIST where id=${officeId}) od on
od.office_id=jl.office_id
WHERE jl.office_id in (od.office_id) and jl.account_id IN (5,6,94,95,84,92)
AND jl.type_enum=1 and jl.reversed=0
and jl.entry_date= '${startDate}' and '${startDate}'<='2017-12-01' and
mlt.is_reversed!=1 and ml.loan_status_id!=500
GROUP BY od.id,mg.id,jl.entry_date
UNION
SELECT
jl.account_id,
SUM(jl.amount) as amount,
od.id as office_id,
max(jl.entry_date) entry_date,
CONCAT('Collection for ',mg.display_name) as details
FROM acc_gl_journal_entry jl
LEFT JOIN m_loan_transaction mlt ON jl.loan_transaction_id=mlt.id
LEFT JOIN m_loan ml ON ml.id=mlt.loan_id
LEFT JOIN m_group mg on mg.id=ml.group_id
INNER JOIN (select office_id,id from OFFICE_LIST where id=${officeId}) od on
od.office_id=jl.office_id
WHERE jl.office_id in (od.office_id) and jl.account_id IN (7,5,6,94,95,84,92)
AND jl.type_enum=1 and jl.reversed=0
and jl.entry_date= '${startDate}' and '${startDate}'>='2017-12-02' and
mlt.is_reversed!=1 and ml.loan_status_id!=500
GROUP BY od.id,mg.id,jl.entry_date)d
UNION
SELECT c.account_id,NULL,c.amount as 'debit',c.office_id,c.entry_date,c.details
from
(
select
jl.account_id,
SUM(jl.amount) AS amount,
od.id as office_id,(jl.entry_date) entry_date,
account_type.name as details
FROM acc_gl_journal_entry jl
inner join acc_gl_account account_type on jl.account_id=account_type.id
INNER JOIN (select office_id,id from OFFICE_LIST where id=${officeId}) od on
od.office_id=jl.office_id
WHERE jl.office_id in (od.office_id) and manual_entry=1 and type_enum=2 and
jl.account_id!=85 and jl.reversed=0 and (jl.account_id=99 or jl.account_id=142
or jl.account_id=120 or jl.account_id=177 or
account_type.classification_enum=2 or account_type.classification_enum=5) and
jl.entry_date= '${startDate}'
GROUP BY od.id,account_type.name,jl.entry_date
UNION
SELECT
jl.account_id,
SUM(jl.amount) as amount,
od.id as office_id,
(jl.entry_date) entry_date,
CONCAT('Loan disbursal for ',mg.display_name) as details
FROM acc_gl_journal_entry jl
LEFT JOIN m_loan_transaction mlt ON jl.loan_transaction_id=mlt.id
LEFT JOIN m_loan ml ON ml.id=mlt.loan_id
INNER JOIN m_group mg on mg.id=ml.group_id
INNER JOIN (select office_id,id from OFFICE_LIST where id=${officeId}) od on
od.office_id=jl.office_id
WHERE jl.office_id in (od.office_id) and (jl.account_id=2 or jl.account_id=92)
and jl.reversed=0 AND jl.type_enum=1 and ml.loan_status_id!=500 and
mlt.is_reversed!=1
and jl.entry_date= '${startDate}'
GROUP BY od.id,mg.id,jl.entry_date)c
)a
UNION
select NULL,NULL,IFNULL(a.txs,0)-IFNULL(b.ot,0) as 'Outgoing Debit',o.id,
'${startDate}','Closing Balance' as Details
from m_office o
LEFT JOIN
(SELECT a.*,sum(amount) as 'txs'
from
(
SELECT
jl.account_id,
SUM(jl.amount) as amount,
od.id as office_id,
max(jl.entry_date) entry_date
FROM acc_gl_journal_entry jl
inner join acc_gl_account account_type on jl.account_id=account_type.id
INNER JOIN (select office_id,id from OFFICE_LIST where id=${officeId}) od on
od.office_id=jl.office_id
WHERE jl.office_id in (od.office_id) and jl.account_id IN (99,87,157,176,177)
and manual_entry=1 and type_enum=1 and account_type.classification_enum in
(2,4,1) and jl.reversed=0
and jl.entry_date between '2017-03-31' and '${startDate}'
GROUP BY od.id
UNION
SELECT
jl.account_id,
SUM(jl.amount) as amount,
od.id as office_id,
(jl.entry_date) entry_date
FROM acc_gl_journal_entry jl
LEFT JOIN m_loan_transaction mlt ON jl.loan_transaction_id=mlt.id
LEFT JOIN m_loan ml ON ml.id=mlt.loan_id
INNER JOIN (select office_id,id from OFFICE_LIST where id=${officeId}) od on
od.office_id=jl.office_id
WHERE jl.office_id in (od.office_id) and jl.account_id IN (5,6,94,95,84,92)
AND jl.type_enum=1 and jl.reversed=0
and jl.entry_date between '2017-03-31' and '2017-12-01' and
'${startDate}'>='2017-12-01' and mlt.is_reversed!=1 and ml.loan_status_id!=500
GROUP BY od.id
UNION
select 6,7335,37,'2017-04-24' from (select office_id,id from OFFICE_LIST where
id=${officeId})a where a.office_id like '%37%' and '2017-04-24'<= '${startDate}'
UNION
select 6,7335,37,'2017-04-24' from (select office_id,id from OFFICE_LIST where
id=${officeId})a where a.office_id like '%37%' and '2017-04-24'<=
'${startDate}'
UNION
select 6,9780,37,'2017-04-20' from (select office_id,id from OFFICE_LIST where
id=${officeId})a where a.office_id like '%37%' and '2017-04-20'<= '${startDate}'
UNION
select 6,7335,37,'2017-05-05' from (select office_id,id from OFFICE_LIST where
id=${officeId})a where a.office_id like '%37%' and '2017-05-05'<= '${startDate}'
UNION
select 6,8313,37,'2017-05-15' from (select office_id,id from OFFICE_LIST where
id=${officeId})a where a.office_id like '%37%' and '2017-05-15'<= '${startDate}'
UNION
select 6,7335,37,'2017-05-18' from (select office_id,id from OFFICE_LIST where
id=${officeId})a where a.office_id like '%37%' and '2017-05-18'<= '${startDate}'
UNION
SELECT
jl.account_id,
SUM(jl.amount) as amount,
od.id as office_id,
(jl.entry_date) entry_date
FROM acc_gl_journal_entry jl
LEFT JOIN m_loan_transaction mlt ON jl.loan_transaction_id=mlt.id
LEFT JOIN m_loan ml ON ml.id=mlt.loan_id
INNER JOIN (select office_id,id from OFFICE_LIST where id=${officeId}) od on
od.office_id=jl.office_id
WHERE jl.office_id in (od.office_id) and jl.account_id IN (7,5,6,94,95,84,92)
AND jl.type_enum=1 and jl.reversed=0
and jl.entry_date between '2017-12-02' and '${startDate}' and
'${startDate}'>='2017-12-02' and mlt.is_reversed!=1 and
ml.loan_status_id!=500
GROUP BY od.id) a
where a.entry_date<= '${startDate}')a on a.office_id=o.id
LEFT JOIN (select b.*,sum(amount) as ot
from (
select
jl.account_id,
SUM(jl.amount) AS amount,
od.id as office_id,(jl.entry_date) entry_date
FROM acc_gl_journal_entry jl
inner join acc_gl_account account_type on jl.account_id=account_type.id
INNER JOIN (select office_id,id from OFFICE_LIST where id=${officeId}) od on
od.office_id=jl.office_id
WHERE jl.office_id in (od.office_id) and manual_entry=1 and type_enum=2 and
jl.account_id!=85 and jl.reversed=0 and (jl.account_id=99 or jl.account_id=142
or jl.account_id=120 or jl.account_id=177 or
account_type.classification_enum=2 or account_type.classification_enum=5) and
jl.entry_date between '2017-03-31' and '${startDate}'
GROUP BY od.id
UNION
SELECT
jl.account_id,
SUM(jl.amount) as amount,
od.id as office_id,
(jl.entry_date) entry_date
FROM acc_gl_journal_entry jl
LEFT JOIN m_loan_transaction mlt ON jl.loan_transaction_id=mlt.id
LEFT JOIN m_loan ml ON ml.id=mlt.loan_id
INNER JOIN (select office_id,id from OFFICE_LIST where id=${officeId}) od on
od.office_id=jl.office_id
WHERE jl.office_id in (od.office_id) and (jl.account_id=2 or jl.account_id=92)
and jl.reversed=0 AND jl.type_enum=1 and ml.loan_status_id!=500 and
mlt.is_reversed!=1
and jl.entry_date between '2017-03-31' and '${startDate}'
GROUP BY od.id)b
where entry_date<= '${startDate}') b on b.office_id=o.id
where o.id=${officeId}
GROUP BY o.id, '${startDate}')a
UNION
SELECT
'Total Credit/Debit' as 'Details',SUM(a.`Incoming
Credit`),FORMAT(SUM(a.`Outgoing Debit`),0)
from
(
SELECT NULL,a.txs-b.ot as 'Incoming Credit','NULL' as 'Outgoing Debit',o.id as
office_id, '${startDate}','Opening Balance' as Details
from m_office o
LEFT JOIN
(SELECT a.*,sum(amount) as 'txs'
from
(
SELECT
jl.account_id,
SUM(jl.amount) as amount,
od.id as office_id,
max(jl.entry_date) entry_date
FROM acc_gl_journal_entry jl
inner join acc_gl_account account_type on jl.account_id=account_type.id
INNER JOIN (select office_id,id from OFFICE_LIST where id=${officeId}) od on
od.office_id=jl.office_id
WHERE jl.office_id in (od.office_id) and jl.account_id IN (99,87,157,176,177)
and manual_entry=1 and type_enum=1 and account_type.classification_enum in
(2,4,1) and jl.reversed=0
and jl.entry_date between '2017-03-31' and ADDDATE( '${startDate}',-1)
GROUP BY od.id
UNION
SELECT
jl.account_id,
SUM(jl.amount) as amount,
od.id as office_id,
max(jl.entry_date) entry_date
FROM acc_gl_journal_entry jl
LEFT JOIN m_loan_transaction mlt ON jl.loan_transaction_id=mlt.id
LEFT JOIN m_loan ml ON ml.id=mlt.loan_id
INNER JOIN (select office_id,id from OFFICE_LIST where id=${officeId}) od on
od.office_id=jl.office_id
WHERE jl.office_id in (od.office_id) and jl.account_id IN (5,6,94,95,84,92)
AND jl.type_enum=1 and jl.reversed=0
and jl.entry_date between '2017-03-31' and '2017-12-01' and ADDDATE(
'${startDate}',-1)>='2017-12-01' and mlt.is_reversed!=1 and
ml.loan_status_id!=500
GROUP BY od.id
UNION
select 6,7335,37,'2017-04-24' from (select office_id,id from OFFICE_LIST where
id=${officeId})a where a.office_id like '%37%' and '2017-04-24'<=ADDDATE(
'${startDate}',-1)
UNION
select 6,7335,37,'2017-04-24' from (select office_id,id from OFFICE_LIST where
id=${officeId})a where a.office_id like '%37%' and '2017-04-24'<=ADDDATE(
'${startDate}',-1)
UNION
select 6,9780,37,'2017-04-20' from (select office_id,id from OFFICE_LIST where
id=${officeId})a where a.office_id like '%37%' and '2017-04-20'<=ADDDATE(
'${startDate}',-1)
UNION
select 6,7335,37,'2017-05-05' from (select office_id,id from OFFICE_LIST where
id=${officeId})a where a.office_id like '%37%' and '2017-05-05'<=ADDDATE(
'${startDate}',-1)
UNION
select 6,8313,37,'2017-05-15' from (select office_id,id from OFFICE_LIST where
id=${officeId})a where a.office_id like '%37%' and '2017-05-15'<=ADDDATE(
'${startDate}',-1)
UNION
select 6,7335,37,'2017-05-18' from (select office_id,id from OFFICE_LIST where
id=${officeId})a where a.office_id like '%37%' and '2017-05-18'<=ADDDATE(
'${startDate}',-1)
UNION
SELECT
jl.account_id,
SUM(jl.amount) as amount,
od.id as office_id,
max(jl.entry_date) entry_date
FROM acc_gl_journal_entry jl
LEFT JOIN m_loan_transaction mlt ON jl.loan_transaction_id=mlt.id
LEFT JOIN m_loan ml ON ml.id=mlt.loan_id
INNER JOIN (select office_id,id from OFFICE_LIST where id=${officeId}) od on
od.office_id=jl.office_id
WHERE jl.office_id in (od.office_id) and jl.account_id IN (7,5,6,94,95,84,92)
AND jl.type_enum=1 and jl.reversed=0
and jl.entry_date between '2017-12-02' and ADDDATE( '${startDate}',-1) and
'${startDate}'>='2017-12-02' and mlt.is_reversed!=1 and ml.loan_status_id!=500
GROUP BY od.id) a
where a.entry_date< '${startDate}')a on a.office_id=o.id
LEFT JOIN (select b.*,sum(amount) as ot
from (
select
jl.account_id,
SUM(jl.amount) AS amount,
od.id as office_id,max(jl.entry_date) entry_date
FROM acc_gl_journal_entry jl
inner join acc_gl_account account_type on jl.account_id=account_type.id
INNER JOIN (select office_id,id from OFFICE_LIST where id=${officeId}) od on
od.office_id=jl.office_id
WHERE jl.office_id in (od.office_id) and manual_entry=1 and type_enum=2 and
jl.account_id!=85 and jl.reversed=0 and (jl.account_id=99 or jl.account_id=142
or jl.account_id=120 or jl.account_id=177 or
account_type.classification_enum=2 or account_type.classification_enum=5) and
jl.entry_date between '2017-03-31' and ADDDATE( '${startDate}',-1)
GROUP BY od.id
UNION
SELECT
jl.account_id,
SUM(jl.amount) as amount,
od.id as office_id,
max(jl.entry_date) entry_date
FROM acc_gl_journal_entry jl
LEFT JOIN m_loan_transaction mlt ON jl.loan_transaction_id=mlt.id
LEFT JOIN m_loan ml ON ml.id=mlt.loan_id
INNER JOIN (select office_id,id from OFFICE_LIST where id=${officeId}) od on
od.office_id=jl.office_id
WHERE jl.office_id in (od.office_id) and (jl.account_id=2 or jl.account_id=92)
and jl.reversed=0 AND jl.type_enum=1 and ml.loan_status_id!=500 and
mlt.is_reversed!=1
and jl.entry_date between '2017-03-31' and ADDDATE( '${startDate}',-1)
GROUP BY od.id)b
where entry_date< '${startDate}') b on b.office_id=o.id
where o.id=${officeId}
GROUP BY o.id, '${startDate}'
UNION
select a.* from
(
SELECT d.account_id,d.amount as Credit,'NULL' as
'Debit',d.office_id,d.entry_date,d.details from
(SELECT
jl.account_id,
SUM(jl.amount) as amount,
od.id as office_id,
(jl.entry_date) entry_date,
'Inbound Cash Transfer' as details
FROM acc_gl_journal_entry jl
inner join acc_gl_account account_type on jl.account_id=account_type.id
INNER JOIN (select office_id,id from OFFICE_LIST where id=${officeId}) od on
od.office_id=jl.office_id
WHERE jl.office_id in (od.office_id) and jl.account_id IN (99,87,157,176,177)
and manual_entry=1 and type_enum=1 and account_type.classification_enum in
(2,4,1) and jl.reversed=0
and jl.entry_date = '${startDate}'
GROUP BY od.id
UNION
SELECT
jl.account_id,
SUM(jl.amount) as amount,
od.id as office_id,
(jl.entry_date) entry_date,
CONCAT('Collection for ',mg.display_name) as details
FROM acc_gl_journal_entry jl
LEFT JOIN m_loan_transaction mlt ON jl.loan_transaction_id=mlt.id
LEFT JOIN m_loan ml ON ml.id=mlt.loan_id
LEFT JOIN m_group mg on mg.id=ml.group_id
INNER JOIN (select office_id,id from OFFICE_LIST where id=${officeId}) od on
od.office_id=jl.office_id
WHERE jl.office_id in (od.office_id) and jl.account_id IN (5,6,94,95,84,92)
AND jl.type_enum=1 and jl.reversed=0
and jl.entry_date= '${startDate}' and '${startDate}'<='2017-12-01' and
mlt.is_reversed!=1 and ml.loan_status_id!=500
GROUP BY od.id,mg.id,jl.entry_date
UNION
SELECT
jl.account_id,
SUM(jl.amount) as amount,
od.id as office_id,
max(jl.entry_date) entry_date,
CONCAT('Collection for ',mg.display_name) as details
FROM acc_gl_journal_entry jl
LEFT JOIN m_loan_transaction mlt ON jl.loan_transaction_id=mlt.id
LEFT JOIN m_loan ml ON ml.id=mlt.loan_id
LEFT JOIN m_group mg on mg.id=ml.group_id
INNER JOIN (select office_id,id from OFFICE_LIST where id=${officeId}) od on
od.office_id=jl.office_id
WHERE jl.office_id in (od.office_id) and jl.account_id IN (7,5,6,94,95,84,92)
AND jl.type_enum=1 and jl.reversed=0
and jl.entry_date= '${startDate}' and '${startDate}'>='2017-12-02' and
mlt.is_reversed!=1 and ml.loan_status_id!=500
GROUP BY od.id,mg.id,jl.entry_date)d
UNION
SELECT c.account_id,NULL,c.amount as 'debit',c.office_id,c.entry_date,c.details
from
(
select
jl.account_id,
SUM(jl.amount) AS amount,
od.id as office_id,(jl.entry_date) entry_date,
account_type.name as details
FROM acc_gl_journal_entry jl
inner join acc_gl_account account_type on jl.account_id=account_type.id
INNER JOIN (select office_id,id from OFFICE_LIST where id=${officeId}) od on
od.office_id=jl.office_id
WHERE jl.office_id in (od.office_id) and manual_entry=1 and type_enum=2 and
jl.account_id!=85 and jl.reversed=0 and (jl.account_id=99 or jl.account_id=142
or jl.account_id=120 or jl.account_id=177 or
account_type.classification_enum=2 or account_type.classification_enum=5) and
jl.entry_date= '${startDate}'
GROUP BY od.id,account_type.name,jl.entry_date
UNION
SELECT
jl.account_id,
SUM(jl.amount) as amount,
od.id as office_id,
(jl.entry_date) entry_date,
CONCAT('Loan disbursal for ',mg.display_name) as details
FROM acc_gl_journal_entry jl
LEFT JOIN m_loan_transaction mlt ON jl.loan_transaction_id=mlt.id
LEFT JOIN m_loan ml ON ml.id=mlt.loan_id
INNER JOIN m_group mg on mg.id=ml.group_id
INNER JOIN (select office_id,id from OFFICE_LIST where id=${officeId}) od on
od.office_id=jl.office_id
WHERE jl.office_id in (od.office_id) and (jl.account_id=2 or jl.account_id=92)
and jl.reversed=0 AND jl.type_enum=1 and ml.loan_status_id!=500 and
mlt.is_reversed!=1
and jl.entry_date= '${startDate}'
GROUP BY od.id,mg.id,jl.entry_date)c
)a
UNION
select NULL,NULL,a.txs-b.ot as 'Outgoing Debit',o.id, '${startDate}','Closing
Balance' as Details
from m_office o
LEFT JOIN
(SELECT a.*,sum(amount) as 'txs'
from
(
SELECT
jl.account_id,
SUM(jl.amount) as amount,
od.id as office_id,
max(jl.entry_date) entry_date
FROM acc_gl_journal_entry jl
inner join acc_gl_account account_type on jl.account_id=account_type.id
INNER JOIN (select office_id,id from OFFICE_LIST where id=${officeId}) od on
od.office_id=jl.office_id
WHERE jl.office_id in (od.office_id) and jl.account_id IN (99,87,157,176,177)
and manual_entry=1 and type_enum=1 and account_type.classification_enum in
(2,4,1) and jl.reversed=0
and jl.entry_date between '2017-03-31' and '${startDate}'
GROUP BY od.id
UNION
SELECT
jl.account_id,
SUM(jl.amount) as amount,
od.id as office_id,
(jl.entry_date) entry_date
FROM acc_gl_journal_entry jl
LEFT JOIN m_loan_transaction mlt ON jl.loan_transaction_id=mlt.id
LEFT JOIN m_loan ml ON ml.id=mlt.loan_id
INNER JOIN (select office_id,id from OFFICE_LIST where id=${officeId}) od on
od.office_id=jl.office_id
WHERE jl.office_id in (od.office_id) and jl.account_id IN (5,6,94,95,84,92)
AND jl.type_enum=1 and jl.reversed=0
and jl.entry_date between '2017-03-31' and '2017-12-01' and
'${startDate}'>='2017-12-01' and mlt.is_reversed!=1 and ml.loan_status_id!=500
GROUP BY od.id
UNION
select 6,7335,37,'2017-04-24' from (select office_id,id from OFFICE_LIST where
id=${officeId})a where a.office_id like '%37%' and '2017-04-24'<= '${startDate}'
UNION
select 6,7335,37,'2017-04-24' from (select office_id,id from OFFICE_LIST where
id=${officeId})a where a.office_id like '%37%' and '2017-04-24'<=
'${startDate}'
UNION
select 6,9780,37,'2017-04-20' from (select office_id,id from OFFICE_LIST where
id=${officeId})a where a.office_id like '%37%' and '2017-04-20'<= '${startDate}'
UNION
select 6,7335,37,'2017-05-05' from (select office_id,id from OFFICE_LIST where
id=${officeId})a where a.office_id like '%37%' and '2017-05-05'<= '${startDate}'
UNION
select 6,8313,37,'2017-05-15' from (select office_id,id from OFFICE_LIST where
id=${officeId})a where a.office_id like '%37%' and '2017-05-15'<= '${startDate}'
UNION
select 6,7335,37,'2017-05-18' from (select office_id,id from OFFICE_LIST where
id=${officeId})a where a.office_id like '%37%' and '2017-05-18'<= '${startDate}'
UNION
SELECT
jl.account_id,
SUM(jl.amount) as amount,
od.id as office_id,
(jl.entry_date) entry_date
FROM acc_gl_journal_entry jl
LEFT JOIN m_loan_transaction mlt ON jl.loan_transaction_id=mlt.id
LEFT JOIN m_loan ml ON ml.id=mlt.loan_id
INNER JOIN (select office_id,id from OFFICE_LIST where id=${officeId}) od on
od.office_id=jl.office_id
WHERE jl.office_id in (od.office_id) and jl.account_id IN (7,5,6,94,95,84,92)
AND jl.type_enum=1 and jl.reversed=0
and jl.entry_date between '2017-12-02' and '${startDate}' and
'${startDate}'>='2017-12-02' and mlt.is_reversed!=1 and
ml.loan_status_id!=500
GROUP BY od.id) a
where a.entry_date<= '${startDate}')a on a.office_id=o.id
LEFT JOIN (select b.*,sum(amount) as ot
from (
select
jl.account_id,
SUM(jl.amount) AS amount,
od.id as office_id,(jl.entry_date) entry_date
FROM acc_gl_journal_entry jl
inner join acc_gl_account account_type on jl.account_id=account_type.id
INNER JOIN (select office_id,id from OFFICE_LIST where id=${officeId}) od on
od.office_id=jl.office_id
WHERE jl.office_id in (od.office_id) and manual_entry=1 and type_enum=2 and
jl.account_id!=85 and jl.reversed=0 and (jl.account_id=99 or jl.account_id=142
or jl.account_id=120 or jl.account_id=177 or
account_type.classification_enum=2 or account_type.classification_enum=5) and
jl.entry_date between '2017-03-31' and '${startDate}'
GROUP BY od.id
UNION
SELECT
jl.account_id,
SUM(jl.amount) as amount,
od.id as office_id,
(jl.entry_date) entry_date
FROM acc_gl_journal_entry jl
LEFT JOIN m_loan_transaction mlt ON jl.loan_transaction_id=mlt.id
LEFT JOIN m_loan ml ON ml.id=mlt.loan_id
INNER JOIN (select office_id,id from OFFICE_LIST where id=${officeId}) od on
od.office_id=jl.office_id
WHERE jl.office_id in (od.office_id) and (jl.account_id=2 or jl.account_id=92)
and jl.reversed=0 AND jl.type_enum=1 and ml.loan_status_id!=500 and
mlt.is_reversed!=1
and jl.entry_date between '2017-03-31' and '${startDate}'
GROUP BY od.id)b
where entry_date<= '${startDate}') b on b.office_id=o.id
where o.id=${officeId}
GROUP BY o.id, '${startDate}')aSELECT
CONCAT('N-',@m:=@m+1) AS 'S No',
a.* from
(SELECT @m:= 0) AS m,
(SELECT a.office_name as Office,a.staff AS CRO,DAYNAME(a.day_id) as
Day,a.day_id as DueDate,a.group_name as GroupName,a.client_id as
Clients,IFNULL(CAST(a.installment as CHAR) ,'OD')as
Due,IFNULL(a.amount_except,0) as Demand,cv.code_value as
Time,IFNULL(a.pending,0) as Pending,a.TotalDemand,IFNULL(a.collected_amount,0)
as 'CollectedAmount',a.loan as Loan,a.bal as 'Bal OA',a.CANow as 'CA As Of Now'
from m_office o
INNER JOIN m_office ounder ON ounder.hierarchy LIKE CONCAT(o.hierarchy, '%%')
INNER JOIN (select a.*,ms.display_name as staff ,sh.name as staff_id from
(select a.group_id,a.client_id,a.day_id,a.amount_except,(dm.ae-ifnull(d.ca,0))
as pending ,IFNULL(a.installment,min(a.installment)) as installment,
IFNULL(a.amount_except+IFNULL((dm.ae-ifnull(d.ca,0)),0),(dm.ae-ifnull(d.ca,0)))
as TotalDemand,m.amt as loan,cs.collected_amount,
m.amt-IFNULL(s.ca,0) as bal,IFNULL(s.ca,0)
CANow,a.loan_officer_id,a.office_id,a.office_name,a.group_name,a.loan_disbursed_date
from
(
select
o.id as office_id,
o.name as office_name,
g.display_name as group_name,
l.group_id as "group_id",
count(distinct(l.client_id)) as 'client_id',
l.disbursedon_date as loan_disbursed_date,
s.day_id,
lr.installment,
l.loan_officer_id,
sum(lr.principal_amount+lr.interest_amount) as 'amount_except'
from m_office o
INNER JOIN m_office ounder ON ounder.hierarchy LIKE CONCAT(o.hierarchy, '%%')
inner join m_group g on g.office_id=ounder.id
inner join m_loan l on l.group_id=g.id
JOIN(
select s.* from
(select a.* , d.day_id from (select max(duedate) due,
l.group_id,l.disbursedon_date from m_office o
INNER JOIN m_office ounder ON ounder.hierarchy LIKE CONCAT(o.hierarchy, '%%')
inner join m_group g on g.office_id=ounder.id
inner join m_loan l on l.group_id=g.id
inner join m_loan_repayment_schedule lr on lr.loan_id=l.id
where o.id=${officeId} and l.loan_status_id not in (100,200,500)
group by l.group_id,l.disbursedon_date)a
left join day_master d on DAYNAME(day_id)=DAYNAME(due)
where a.due<day_id
group by a.group_id,d.day_id,a.disbursedon_date)s
union
select null, l.group_id,l.disbursedon_date ,lr.duedate
from m_office o
INNER JOIN m_office ounder ON ounder.hierarchy LIKE CONCAT(o.hierarchy, '%%')
inner join m_group g on g.office_id=ounder.id
inner join m_loan l on g.id=l.group_id
inner join m_loan_repayment_schedule lr on lr.loan_id=l.id
where o.id=${officeId} and l.loan_status_id not in (100,200,500)
group by l.group_id,lr.duedate,l.disbursedon_date) s on s.group_id=l.group_id
and l.disbursedon_date=s.disbursedon_date
left join m_loan_repayment_schedule lr on lr.loan_id=l.id
and lr.duedate=s.day_id
where o.id=${officeId} and
(((lr.obligations_met_on_date>=s.day_id) OR lr.obligations_met_on_date IS NULL)
and l.loan_status_id!=500)
AND
(((l.loan_status_id=300 or l.closedon_date >=s.day_id) or l.closedon_date is
null)) and (s.day_id>=l.disbursedon_date) and
(l.loan_status_id=300 or
s.day_id<=ifnull(l.closedon_date,adddate(s.day_id,+1))) and s.day_id between
'${startDate}' and '${endDate}'
group by l.group_id,s.day_id) a
LEFT JOIN
(select sum(d.collected_amount) as
ca,s.day_id,d.group_id,d.loan_disbursed_date,d.office_id
from
(select sum(a.amount) as 'collected_amount',
lt.transaction_date,
g.office_id,
r.obligations_met_on_date,
r.duedate,
l.group_id,
l.id,
l.disbursedon_date as loan_disbursed_date,
adddate(r.duedate, +(datediff(lt.transaction_date,r.duedate) div 7 )*7) as
rollup_date
from m_office o
INNER JOIN m_office ounder ON ounder.hierarchy LIKE CONCAT(o.hierarchy, '%%')
inner join m_group g on g.office_id=ounder.id
INNER JOIN m_loan l ON g.id=l.group_id
INNER JOIN m_loan_repayment_schedule r ON l.id=r.loan_id
INNER JOIN m_loan_transaction lt ON lt.loan_id=l.id
INNER JOIN m_loan_transaction_repayment_schedule_mapping a on
a.loan_transaction_id=lt.id and r.id=a.loan_repayment_schedule_id
where o.id=${officeId} and is_reversed=0 and r.duedate>'2017-01-01'
group by l.disbursedon_date,lt.transaction_date,l.group_id,l.id
)d
JOIN(
select s.* from
(select a.* , d.day_id from
(select max(duedate) due, l.group_id,l.disbursedon_date,g.office_id
from m_office o
INNER JOIN m_office ounder ON ounder.hierarchy LIKE CONCAT(o.hierarchy, '%%')
inner join m_group g on g.office_id=ounder.id
inner join m_loan l on g.id=l.group_id
inner join m_loan_repayment_schedule lr on lr.loan_id=l.id
where o.id=${officeId} and l.loan_status_id not in (100,200,500)
group by l.group_id,l.disbursedon_date)a
left join day_master d on DAYNAME(day_id)=DAYNAME(due)
where a.due<day_id
group by a.group_id,d.day_id,a.disbursedon_date)s
union
select null, l.group_id,l.disbursedon_date,g.office_id,lr.duedate
from m_office o
INNER JOIN m_office ounder ON ounder.hierarchy LIKE CONCAT(o.hierarchy, '%%')
inner join m_group g on g.office_id=ounder.id
inner join m_loan l on g.id=l.group_id
inner join m_loan_repayment_schedule lr on lr.loan_id=l.id
where o.id=${officeId} and l.loan_status_id not in (100,200,500)
group by l.group_id,lr.duedate,l.disbursedon_date) s on s.group_id=d.group_id
and d.loan_disbursed_date=s.disbursedon_date
where (d.transaction_date<ADDDATE('${endDate}',-6)) and s.day_id between
'${startDate}' and '${endDate}'
group by s.day_id,d.group_id,d.loan_disbursed_date) d on d.group_id=a.group_id
and d.day_id=a.day_id
and d.loan_disbursed_date=a.loan_disbursed_date
LEFT JOIN (select sum(a.amount_except) as
ae,s.day_id,a.group_id,a.loan_disbursed_date,a.office_id
from (select
l.group_id as "group_id",
g.office_id,
l.disbursedon_date as loan_disbursed_date,
s.day_id,
lr.installment,
l.loan_status_id,
l.loan_officer_id,
l.id,
sum(lr.principal_amount+lr.interest_amount) as 'amount_except'
from m_office o
INNER JOIN m_office ounder ON ounder.hierarchy LIKE CONCAT(o.hierarchy, '%%')
inner join m_group g on g.office_id=ounder.id
inner join m_loan l on g.id=l.group_id
JOIN(
select s.* from
(select a.* , d.day_id
from (select max(duedate) due, l.group_id,l.disbursedon_date
from m_office o
INNER JOIN m_office ounder ON ounder.hierarchy LIKE CONCAT(o.hierarchy, '%%')
inner join m_group g on g.office_id=ounder.id
inner join m_loan l on g.id=l.group_id
inner join m_loan_repayment_schedule lr on lr.loan_id=l.id
where o.id=${officeId} and l.loan_status_id not in (100,200,500)
group by l.group_id,l.disbursedon_date)a
left join day_master d on DAYNAME(day_id)=DAYNAME(due)
where a.due<day_id
group by a.group_id,d.day_id,a.disbursedon_date)s
union
select null, l.group_id,l.disbursedon_date ,lr.duedate
from m_office o
INNER JOIN m_office ounder ON ounder.hierarchy LIKE CONCAT(o.hierarchy, '%%')
inner join m_group g on g.office_id=ounder.id
inner join m_loan l on g.id=l.group_id
inner join m_loan_repayment_schedule lr on lr.loan_id=l.id
where o.id=${officeId} and l.loan_status_id not in (100,200,500)
group by l.group_id,lr.duedate,l.disbursedon_date) s on s.group_id=l.group_id
and l.disbursedon_date=s.disbursedon_date
left join m_loan_repayment_schedule lr on lr.loan_id=l.id
and lr.duedate=s.day_id
where o.id=${officeId}
and l.loan_status_id not in (100,200,500) and (rescheduledon_date is null or
rescheduledon_date>=s.day_id )
group by l.group_id,s.day_id,l.disbursedon_date) a
JOIN(
select s.* from
(select a.* , d.day_id from (select max(duedate) due,
l.group_id,l.disbursedon_date from m_office o
INNER JOIN m_office ounder ON ounder.hierarchy LIKE CONCAT(o.hierarchy, '%%')
inner join m_group g on g.office_id=ounder.id
inner join m_loan l on g.id=l.group_id
inner join m_loan_repayment_schedule lr on lr.loan_id=l.id
where o.id=${officeId} and l.loan_status_id not in (100,200,500)
group by l.group_id,l.disbursedon_date)a
left join day_master d on DAYNAME(day_id)=DAYNAME(due)
where a.due<day_id
group by a.group_id,d.day_id,a.disbursedon_date)s
union
select null, l.group_id,l.disbursedon_date ,lr.duedate
from m_office o
INNER JOIN m_office ounder ON ounder.hierarchy LIKE CONCAT(o.hierarchy, '%%')
inner join m_group g on g.office_id=ounder.id
inner join m_loan l on g.id=l.group_id
inner join m_loan_repayment_schedule lr on lr.loan_id=l.id
where o.id=${officeId} and l.loan_status_id not in (100,200,500)
group by l.group_id,lr.duedate,l.disbursedon_date) s on s.group_id=a.group_id
and a.loan_disbursed_date=s.disbursedon_date
where ((a.day_id<s.day_id) and (s.day_id<='${endDate}') ) and s.day_id
between '${startDate}' and '${endDate}' group by
a.group_id,s.day_id,a.loan_disbursed_date) dm on dm.group_id=a.group_id and
a.day_id=dm.day_id and a.loan_disbursed_date=dm.loan_disbursed_date
LEFT JOIN (select
(sum(principal_disbursed_derived)+sum(interest_charged_derived)) as
amt,l.group_id,l.disbursedon_date,count(l.id) as loans,l.loan_status_id as
'status',
l.product_id
from m_office o
INNER JOIN m_office ounder ON ounder.hierarchy LIKE CONCAT(o.hierarchy, '%%')
inner join m_group g on g.office_id=ounder.id
inner join m_loan l on g.id=l.group_id
where o.id=${officeId} and l.loan_status_id not in(500,200,100)
group by l.group_id,l.disbursedon_date,o.id)m on m.group_id=a.group_id and
m.disbursedon_date=a.loan_disbursed_date
LEFT JOIN
(select sum(d.collected_amount) as ca,s.day_id,d.group_id,d.loan_disbursed_date
from
(select sum(a.amount) as 'collected_amount',
lt.transaction_date,
r.obligations_met_on_date,
r.duedate,
l.group_id,
l.id,
l.disbursedon_date as loan_disbursed_date
from m_office o
INNER JOIN m_office ounder ON ounder.hierarchy LIKE CONCAT(o.hierarchy, '%%')
inner join m_group g on g.office_id=ounder.id
INNER JOIN m_loan l ON g.id=l.group_id
INNER JOIN m_loan_repayment_schedule r ON l.id=r.loan_id
INNER JOIN m_loan_transaction lt ON lt.loan_id=l.id
INNER JOIN m_loan_transaction_repayment_schedule_mapping a on
a.loan_transaction_id=lt.id and r.id=a.loan_repayment_schedule_id
where o.id=${officeId} and is_reversed=0 and r.duedate>'2017-01-01'
group by l.disbursedon_date,lt.transaction_date,l.group_id,l.id
)d
JOIN(
select s.* from
(select a.* , d.day_id from (select max(duedate) due,
l.group_id,l.disbursedon_date from m_office o
INNER JOIN m_office ounder ON ounder.hierarchy LIKE CONCAT(o.hierarchy, '%%')
inner join m_group g on g.office_id=ounder.id
inner join m_loan l on g.id=l.group_id
inner join m_loan_repayment_schedule lr on lr.loan_id=l.id
where o.id=${officeId} and l.loan_status_id not in (100,200,500)
group by l.group_id,l.disbursedon_date)a
left join day_master d on DAYNAME(day_id)=DAYNAME(due)
where a.due<day_id
group by a.group_id,d.day_id,a.disbursedon_date)s
union
select null, l.group_id,l.disbursedon_date ,lr.duedate
from m_office o
INNER JOIN m_office ounder ON ounder.hierarchy LIKE CONCAT(o.hierarchy, '%%')
inner join m_group g on g.office_id=ounder.id
inner join m_loan l on g.id=l.group_id
inner join m_loan_repayment_schedule lr on lr.loan_id=l.id
where o.id=${officeId} and l.loan_status_id not in (100,200,500)
group by l.group_id,lr.duedate,l.disbursedon_date) s on s.group_id=d.group_id
and d.loan_disbursed_date=s.disbursedon_date
where (d.transaction_date<='${endDate}') and s.day_id between '${startDate}'
and '${endDate}'
group by s.day_id,d.group_id,d.loan_disbursed_date) s on s.group_id=a.group_id
and s.day_id=a.day_id
and s.loan_disbursed_date=a.loan_disbursed_date
LEFT JOIN (select sum(a.amount) as 'collected_amount',
lt.transaction_date,
g.office_id,
r.obligations_met_on_date,
r.duedate,
l.group_id,
l.id,
l.disbursedon_date as loan_disbursed_date,
adddate(r.duedate, +(datediff(lt.transaction_date,r.duedate) div 7 )*7) as
rollup_date
from m_office o
INNER JOIN m_office ounder ON ounder.hierarchy LIKE CONCAT(o.hierarchy, '%%')
inner join m_group g on g.office_id=ounder.id
INNER JOIN m_loan l ON g.id=l.group_id
INNER JOIN m_loan_repayment_schedule r ON l.id=r.loan_id
INNER JOIN m_loan_transaction lt ON lt.loan_id=l.id
INNER JOIN m_loan_transaction_repayment_schedule_mapping a on
a.loan_transaction_id=lt.id and r.id=a.loan_repayment_schedule_id
where o.id=${officeId} and is_reversed=0 and lt.transaction_date between
'${startDate}' and '${endDate}'
group by l.disbursedon_date,lt.transaction_date,l.group_id
)cs on cs.group_id=a.group_id and cs.transaction_date=a.day_id
group by a.group_id,a.day_id,a.loan_disbursed_date)a
LEFT JOIN
(select
IF( (a.day_id<IFNULL(sh.end_date,ADDDATE(curdate(),+100)) and
a.day_id>=sh.start_date),sh.staff_id,0) as
name,a.day_id,sh.group_id,sh.office_id
from
(select
a.id as staff_his_id,
b.group_id,
b.office_id,
s.display_name as 'Staff_Name',
a.staff_id,
b.parent_id as 'center_id',
a.start_date,
a.end_date
from m_office o
INNER JOIN m_office ounder ON ounder.hierarchy LIKE CONCAT(o.hierarchy, '%%')
INNER JOIN (select a.id as 'group_id',a.parent_id,a.office_id from m_group a
where a.parent_id is not null)b ON b.office_id=ounder.id
INNER JOIN m_staff_assignment_history a ON b.parent_id=a.centre_id
LEFT JOIN m_staff s ON s.id=a.staff_id
where o.id=${officeId}
)sh
join day_master a on 1=1
where a.day_id between '${startDate}' and '${endDate}'
group by sh.group_id,a.day_id,sh.staff_id,sh.staff_his_id )sh on
sh.group_id=a.group_id and a.day_id=sh.day_id and name !=0
left join m_staff ms on ms.id=sh.name
group by a.group_id,a.day_id,a.loan_disbursed_date
ORDER BY a.day_id)a on a.office_id=ounder.id
LEFT JOIN MeetingTime mt on mt.group_id=a.group_id and
mt.Date=a.loan_disbursed_date
LEFT JOIN m_code_value cv on cv.id=mt.MeetingTime_cd_Time
where o.id=${officeId} and a.day_id between '${startDate}' and '${endDate}'
GROUP BY a.group_id)a
UNION
select
null,null,null,null,null,NULL,sum(a.Clients),null,sum(a.Demand),null,sum(Pending),sum(a.TotalDemand),sum(a.CollectedAmount),null,null,null
from
(SELECT a.office_name as Office,a.staff AS CRO,DAYNAME(a.day_id) as
Day,a.day_id as DueDate,a.group_name as GroupName,a.client_id as
Clients,a.installment as Due,a.amount_except as Demand,cv.code_value as
Time,a.pending as Pending,a.TotalDemand,a.collected_amount as
'CollectedAmount',a.loan as Loan,a.bal as 'Bal OA',a.CANow as 'CA As Of Now'
from m_office o
INNER JOIN m_office ounder ON ounder.hierarchy LIKE CONCAT(o.hierarchy, '%%')
INNER JOIN (select a.*,ms.display_name as staff ,sh.name as staff_id from
(select a.group_id,a.client_id,a.day_id,a.amount_except,(dm.ae-ifnull(d.ca,0))
as pending ,IFNULL(a.installment,min(a.installment)) as installment,
IFNULL(a.amount_except+IFNULL((dm.ae-ifnull(d.ca,0)),0),(dm.ae-ifnull(d.ca,0)))
as TotalDemand,m.amt as loan,cs.collected_amount,
m.amt-IFNULL(s.ca,0) as bal,IFNULL(s.ca,0)
CANow,a.loan_officer_id,a.office_id,a.office_name,a.group_name,a.loan_disbursed_date
from
(
select
o.id as office_id,
o.name as office_name,
g.display_name as group_name,
l.group_id as "group_id",
count(distinct(l.client_id)) as 'client_id',
l.disbursedon_date as loan_disbursed_date,
s.day_id,
lr.installment,
l.loan_officer_id,
sum(lr.principal_amount+lr.interest_amount) as 'amount_except'
from m_office o
INNER JOIN m_office ounder ON ounder.hierarchy LIKE CONCAT(o.hierarchy, '%%')
inner join m_group g on g.office_id=ounder.id
inner join m_loan l on l.group_id=g.id
JOIN(
select s.* from
(select a.* , d.day_id from (select max(duedate) due,
l.group_id,l.disbursedon_date from m_office o
INNER JOIN m_office ounder ON ounder.hierarchy LIKE CONCAT(o.hierarchy, '%%')
inner join m_group g on g.office_id=ounder.id
inner join m_loan l on l.group_id=g.id
inner join m_loan_repayment_schedule lr on lr.loan_id=l.id
where o.id=${officeId} and l.loan_status_id not in (100,200,500)
group by l.group_id,l.disbursedon_date)a
left join day_master d on DAYNAME(day_id)=DAYNAME(due)
where a.due<day_id
group by a.group_id,d.day_id,a.disbursedon_date)s
union
select null, l.group_id,l.disbursedon_date ,lr.duedate
from m_office o
INNER JOIN m_office ounder ON ounder.hierarchy LIKE CONCAT(o.hierarchy, '%%')
inner join m_group g on g.office_id=ounder.id
inner join m_loan l on g.id=l.group_id
inner join m_loan_repayment_schedule lr on lr.loan_id=l.id
where o.id=${officeId} and l.loan_status_id not in (100,200,500)
group by l.group_id,lr.duedate,l.disbursedon_date) s on s.group_id=l.group_id
and l.disbursedon_date=s.disbursedon_date
left join m_loan_repayment_schedule lr on lr.loan_id=l.id
and lr.duedate=s.day_id
where o.id=${officeId} and
(((lr.obligations_met_on_date>=s.day_id) OR lr.obligations_met_on_date IS NULL)
and l.loan_status_id!=500)
AND
(((l.loan_status_id=300 or l.closedon_date >=s.day_id) or l.closedon_date is
null)) and (s.day_id>=l.disbursedon_date) and
(l.loan_status_id=300 or
s.day_id<=ifnull(l.closedon_date,adddate(s.day_id,+1))) and s.day_id between
'${startDate}' and '${endDate}'
group by l.group_id,s.day_id) a
LEFT JOIN
(select sum(d.collected_amount) as
ca,s.day_id,d.group_id,d.loan_disbursed_date,d.office_id
from
(select sum(a.amount) as 'collected_amount',
lt.transaction_date,
g.office_id,
r.obligations_met_on_date,
r.duedate,
l.group_id,
l.id,
l.disbursedon_date as loan_disbursed_date,
adddate(r.duedate, +(datediff(lt.transaction_date,r.duedate) div 7 )*7) as
rollup_date
from m_office o
INNER JOIN m_office ounder ON ounder.hierarchy LIKE CONCAT(o.hierarchy, '%%')
inner join m_group g on g.office_id=ounder.id
INNER JOIN m_loan l ON g.id=l.group_id
INNER JOIN m_loan_repayment_schedule r ON l.id=r.loan_id
INNER JOIN m_loan_transaction lt ON lt.loan_id=l.id
INNER JOIN m_loan_transaction_repayment_schedule_mapping a on
a.loan_transaction_id=lt.id and r.id=a.loan_repayment_schedule_id
where o.id=${officeId} and is_reversed=0 and r.duedate>'2017-01-01'
group by l.disbursedon_date,lt.transaction_date,l.group_id,l.id
)d
JOIN(
select s.* from
(select a.* , d.day_id from
(select max(duedate) due, l.group_id,l.disbursedon_date,g.office_id
from m_office o
INNER JOIN m_office ounder ON ounder.hierarchy LIKE CONCAT(o.hierarchy, '%%')
inner join m_group g on g.office_id=ounder.id
inner join m_loan l on g.id=l.group_id
inner join m_loan_repayment_schedule lr on lr.loan_id=l.id
where o.id=${officeId} and l.loan_status_id not in (100,200,500)
group by l.group_id,l.disbursedon_date)a
left join day_master d on DAYNAME(day_id)=DAYNAME(due)
where a.due<day_id
group by a.group_id,d.day_id,a.disbursedon_date)s
union
select null, l.group_id,l.disbursedon_date,g.office_id,lr.duedate
from m_office o
INNER JOIN m_office ounder ON ounder.hierarchy LIKE CONCAT(o.hierarchy, '%%')
inner join m_group g on g.office_id=ounder.id
inner join m_loan l on g.id=l.group_id
inner join m_loan_repayment_schedule lr on lr.loan_id=l.id
where o.id=${officeId} and l.loan_status_id not in (100,200,500)
group by l.group_id,lr.duedate,l.disbursedon_date) s on s.group_id=d.group_id
and d.loan_disbursed_date=s.disbursedon_date
where (d.transaction_date<ADDDATE('${endDate}',-6)) and s.day_id between
'${startDate}' and '${endDate}'
group by s.day_id,d.group_id,d.loan_disbursed_date) d on d.group_id=a.group_id
and d.day_id=a.day_id
and d.loan_disbursed_date=a.loan_disbursed_date
LEFT JOIN (select sum(a.amount_except) as
ae,s.day_id,a.group_id,a.loan_disbursed_date,a.office_id
from (select
l.group_id as "group_id",
g.office_id,
l.disbursedon_date as loan_disbursed_date,
s.day_id,
lr.installment,
l.loan_status_id,
l.loan_officer_id,
l.id,
sum(lr.principal_amount+lr.interest_amount) as 'amount_except'
from m_office o
INNER JOIN m_office ounder ON ounder.hierarchy LIKE CONCAT(o.hierarchy, '%%')
inner join m_group g on g.office_id=ounder.id
inner join m_loan l on g.id=l.group_id
JOIN(
select s.* from
(select a.* , d.day_id
from (select max(duedate) due, l.group_id,l.disbursedon_date
from m_office o
INNER JOIN m_office ounder ON ounder.hierarchy LIKE CONCAT(o.hierarchy, '%%')
inner join m_group g on g.office_id=ounder.id
inner join m_loan l on g.id=l.group_id
inner join m_loan_repayment_schedule lr on lr.loan_id=l.id
where o.id=${officeId} and l.loan_status_id not in (100,200,500)
group by l.group_id,l.disbursedon_date)a
left join day_master d on DAYNAME(day_id)=DAYNAME(due)
where a.due<day_id
group by a.group_id,d.day_id,a.disbursedon_date)s
union
select null, l.group_id,l.disbursedon_date ,lr.duedate
from m_office o
INNER JOIN m_office ounder ON ounder.hierarchy LIKE CONCAT(o.hierarchy, '%%')
inner join m_group g on g.office_id=ounder.id
inner join m_loan l on g.id=l.group_id
inner join m_loan_repayment_schedule lr on lr.loan_id=l.id
where o.id=${officeId} and l.loan_status_id not in (100,200,500)
group by l.group_id,lr.duedate,l.disbursedon_date) s on s.group_id=l.group_id
and l.disbursedon_date=s.disbursedon_date
left join m_loan_repayment_schedule lr on lr.loan_id=l.id
and lr.duedate=s.day_id
where o.id=${officeId}
and l.loan_status_id not in (100,200,500) and (rescheduledon_date is null or
rescheduledon_date>=s.day_id )
group by l.group_id,s.day_id,l.disbursedon_date) a
JOIN(
select s.* from
(select a.* , d.day_id from (select max(duedate) due,
l.group_id,l.disbursedon_date from m_office o
INNER JOIN m_office ounder ON ounder.hierarchy LIKE CONCAT(o.hierarchy, '%%')
inner join m_group g on g.office_id=ounder.id
inner join m_loan l on g.id=l.group_id
inner join m_loan_repayment_schedule lr on lr.loan_id=l.id
where o.id=${officeId} and l.loan_status_id not in (100,200,500)
group by l.group_id,l.disbursedon_date)a
left join day_master d on DAYNAME(day_id)=DAYNAME(due)
where a.due<day_id
group by a.group_id,d.day_id,a.disbursedon_date)s
union
select null, l.group_id,l.disbursedon_date ,lr.duedate
from m_office o
INNER JOIN m_office ounder ON ounder.hierarchy LIKE CONCAT(o.hierarchy, '%%')
inner join m_group g on g.office_id=ounder.id
inner join m_loan l on g.id=l.group_id
inner join m_loan_repayment_schedule lr on lr.loan_id=l.id
where o.id=${officeId} and l.loan_status_id not in (100,200,500)
group by l.group_id,lr.duedate,l.disbursedon_date) s on s.group_id=a.group_id
and a.loan_disbursed_date=s.disbursedon_date
where ((a.day_id<s.day_id) and (s.day_id<='${endDate}') ) and s.day_id
between '${startDate}' and '${endDate}' group by
a.group_id,s.day_id,a.loan_disbursed_date) dm on dm.group_id=a.group_id and
a.day_id=dm.day_id and a.loan_disbursed_date=dm.loan_disbursed_date
LEFT JOIN (select
(sum(principal_disbursed_derived)+sum(interest_charged_derived)) as
amt,l.group_id,l.disbursedon_date,count(l.id) as loans,l.loan_status_id as
'status',
l.product_id
from m_office o
INNER JOIN m_office ounder ON ounder.hierarchy LIKE CONCAT(o.hierarchy, '%%')
inner join m_group g on g.office_id=ounder.id
inner join m_loan l on g.id=l.group_id
where o.id=${officeId} and l.loan_status_id not in(500,200,100)
group by l.group_id,l.disbursedon_date,o.id)m on m.group_id=a.group_id and
m.disbursedon_date=a.loan_disbursed_date
LEFT JOIN
(select sum(d.collected_amount) as ca,s.day_id,d.group_id,d.loan_disbursed_date
from
(select sum(a.amount) as 'collected_amount',
lt.transaction_date,
r.obligations_met_on_date,
r.duedate,
l.group_id,
l.id,
l.disbursedon_date as loan_disbursed_date
from m_office o
INNER JOIN m_office ounder ON ounder.hierarchy LIKE CONCAT(o.hierarchy, '%%')
inner join m_group g on g.office_id=ounder.id
INNER JOIN m_loan l ON g.id=l.group_id
INNER JOIN m_loan_repayment_schedule r ON l.id=r.loan_id
INNER JOIN m_loan_transaction lt ON lt.loan_id=l.id
INNER JOIN m_loan_transaction_repayment_schedule_mapping a on
a.loan_transaction_id=lt.id and r.id=a.loan_repayment_schedule_id
where o.id=${officeId} and is_reversed=0 and r.duedate>'2017-01-01'
group by l.disbursedon_date,lt.transaction_date,l.group_id,l.id
)d
JOIN(
select s.* from
(select a.* , d.day_id from (select max(duedate) due,
l.group_id,l.disbursedon_date from m_office o
INNER JOIN m_office ounder ON ounder.hierarchy LIKE CONCAT(o.hierarchy, '%%')
inner join m_group g on g.office_id=ounder.id
inner join m_loan l on g.id=l.group_id
inner join m_loan_repayment_schedule lr on lr.loan_id=l.id
where o.id=${officeId} and l.loan_status_id not in (100,200,500)
group by l.group_id,l.disbursedon_date)a
left join day_master d on DAYNAME(day_id)=DAYNAME(due)
where a.due<day_id
group by a.group_id,d.day_id,a.disbursedon_date)s
union
select null, l.group_id,l.disbursedon_date ,lr.duedate
from m_office o
INNER JOIN m_office ounder ON ounder.hierarchy LIKE CONCAT(o.hierarchy, '%%')
inner join m_group g on g.office_id=ounder.id
inner join m_loan l on g.id=l.group_id
inner join m_loan_repayment_schedule lr on lr.loan_id=l.id
where o.id=${officeId} and l.loan_status_id not in (100,200,500)
group by l.group_id,lr.duedate,l.disbursedon_date) s on s.group_id=d.group_id
and d.loan_disbursed_date=s.disbursedon_date
where (d.transaction_date<='${endDate}') and s.day_id between '${startDate}'
and '${endDate}'
group by s.day_id,d.group_id,d.loan_disbursed_date) s on s.group_id=a.group_id
and s.day_id=a.day_id
and s.loan_disbursed_date=a.loan_disbursed_date
LEFT JOIN (select sum(a.amount) as 'collected_amount',
lt.transaction_date,
g.office_id,
r.obligations_met_on_date,
r.duedate,
l.group_id,
l.id,
l.disbursedon_date as loan_disbursed_date,
adddate(r.duedate, +(datediff(lt.transaction_date,r.duedate) div 7 )*7) as
rollup_date
from m_office o
INNER JOIN m_office ounder ON ounder.hierarchy LIKE CONCAT(o.hierarchy, '%%')
inner join m_group g on g.office_id=ounder.id
INNER JOIN m_loan l ON g.id=l.group_id
INNER JOIN m_loan_repayment_schedule r ON l.id=r.loan_id
INNER JOIN m_loan_transaction lt ON lt.loan_id=l.id
INNER JOIN m_loan_transaction_repayment_schedule_mapping a on
a.loan_transaction_id=lt.id and r.id=a.loan_repayment_schedule_id
where o.id=${officeId} and is_reversed=0 and lt.transaction_date between
'${startDate}' and '${endDate}'
group by l.disbursedon_date,lt.transaction_date,l.group_id
)cs on cs.group_id=a.group_id and cs.transaction_date=a.day_id
group by a.group_id,a.day_id,a.loan_disbursed_date)a
LEFT JOIN
(select
IF( (a.day_id<IFNULL(sh.end_date,ADDDATE(curdate(),+100)) and
a.day_id>=sh.start_date),sh.staff_id,0) as
name,a.day_id,sh.group_id,sh.office_id
from
(select
a.id as staff_his_id,
b.group_id,
b.office_id,
s.display_name as 'Staff_Name',
a.staff_id,
b.parent_id as 'center_id',
a.start_date,
a.end_date
from m_office o
INNER JOIN m_office ounder ON ounder.hierarchy LIKE CONCAT(o.hierarchy, '%%')
INNER JOIN (select a.id as 'group_id',a.parent_id,a.office_id from m_group a
where a.parent_id is not null)b ON b.office_id=ounder.id
INNER JOIN m_staff_assignment_history a ON b.parent_id=a.centre_id
LEFT JOIN m_staff s ON s.id=a.staff_id
where o.id=${officeId}
)sh
join day_master a on 1=1
where a.day_id between '${startDate}' and '${endDate}'
group by sh.group_id,a.day_id,sh.staff_id,sh.staff_his_id )sh on
sh.group_id=a.group_id and a.day_id=sh.day_id and name !=0
left join m_staff ms on ms.id=sh.name
group by a.group_id,a.day_id,a.loan_disbursed_date
ORDER BY a.day_id)a on a.office_id=ounder.id
LEFT JOIN MeetingTime mt on mt.group_id=a.group_id and
mt.Date=a.loan_disbursed_date
LEFT JOIN m_code_value cv on cv.id=mt.MeetingTime_cd_Time
where o.id=${officeId} and a.day_id between '${startDate}' and '${endDate}'
GROUP BY a.group_id)a------------------------------------------------------------------------------
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
