Light Cash Flow LIve

CashFlowLive.sql

select a.cid centerId
,a.branch
,a.center
,a.loanOfficer
,a.dueDate
,sum(a.disbursementAmount) disbursementAmount
,sum(a.principalDue) principalDue
,sum(a.interestDue) interestDue
,sum(a.feesDue) feesDue
,sum(a.disbursedAmt) disbursedAmt
,sum(a.principalPaid) principalPaid
,sum(a.interestPaid) interestPaid
,sum(a.feesPaid) feesPaid from (

select cn.id cid 
,ounder.name branch
,cn.display_name center
,ifnull(s.display_name,'NA') loanOfficer
,l.expected_disbursedon_date dueDate
,sum(l.principal_amount) disbursementAmount
,0 principalDue
,0 interestDue
,0 as feesDue
,0 disbursedAmt
,0 principalPaid
,0 interestPaid
,0 feesPaid
from m_office o 
     join m_office ounder on ounder.hierarchy like concat(o.hierarchy, '%') 
     and ounder.hierarchy like concat(${userhierarchy}, '%') 
inner join m_group gr on gr.office_id = ounder.id
inner join m_group cn on cn.id=gr.parent_id
inner join m_group_client gc on gc.group_id=gr.id
inner join m_client cl on cl.id=gc.client_id
inner join m_loan l on l.client_id=cl.id and l.group_id=gr.id
left join m_staff s on s.id=l.loan_officer_id

where #cn.display_name='Baldana A'and 
l.expected_disbursedon_date=${onDate}
and l.loan_status_id=200
and o.id=${branch}
group by cn.id
union

select cn.id cid 
,ounder.name branch
,cn.display_name center
,ifnull(s.display_name,'NA') loanOfficer
,mr.duedate
,0 disburswementAmount
,sum(ifnull(mr.principal_amount,0))-sum(ifnull(mr.principal_completed_derived,0)) principalDue
,sum(ifnull(mr.interest_amount,0)) - sum(ifnull(mr.interest_completed_derived,0)) interestDue
,0 as feesdue
,0 disbursedAmt
,0 principalPaid
,0 interestPaid
,0 feesPaid
from m_office o 
     join m_office ounder on ounder.hierarchy like concat(o.hierarchy, '%') 
     and ounder.hierarchy like concat(${userhierarchy}, '%') 
inner join m_group gr on gr.office_id = ounder.id
inner join m_group cn on cn.id=gr.parent_id
inner join m_group_client gc on gc.group_id=gr.id
inner join m_client cl on cl.id=gc.client_id
inner join m_loan l on l.client_id=cl.id and l.group_id=gr.id
left join m_staff s on s.id=l.loan_officer_id
inner join m_loan_repayment_schedule mr on mr.loan_id=l.id
where mr.duedate=${onDate} and mr.completed_derived=0
and l.loan_status_id in (300)
and l.loan_type_enum=3
and o.id=${branch}
group by cn.id

union all

select cn.id cid 
,ounder.name branch
,cn.display_name center
,ifnull(s.display_name,'NA') loanOfficer
,case ch.charge_time_enum
when 1 then l.expected_disbursedon_date
else  ch.due_for_collection_as_of_date end as dueDate
,0 disburswementAmount
,0 principalDue
,0 interestDue
,sum(ch.amount) feesDue
,0 disbursedAmt
,0 principalPaid
,0 interestPaid
,0 feesPaid
from m_office o 
     join m_office ounder on ounder.hierarchy like concat(o.hierarchy, '%') 
     and ounder.hierarchy like concat(${userhierarchy}, '%') 
inner join m_group gr on gr.office_id = ounder.id
inner join m_group cn on cn.id=gr.parent_id
inner join m_group_client gc on gc.group_id=gr.id
inner join m_client cl on cl.id=gc.client_id
inner join m_loan l on l.client_id=cl.id and l.group_id=gr.id
left join m_staff s on s.id=l.loan_officer_id
inner join m_loan_charge ch on ch.loan_id=l.id
where l.loan_status_id in (200,300)
and l.loan_type_enum=3
and o.id=${branch}
and ch.is_paid_derived=0 and ch.is_active=1
and case ch.charge_time_enum
when 1 then l.expected_disbursedon_date
else  ch.due_for_collection_as_of_date end = ${onDate}
group by cn.id

union

select cn.id cid 
,ounder.name branch
,cn.display_name center
,ifnull(s.display_name,'NA') loanOfficer
,tr.transaction_date dueDate
,0 disburswementAmount
,0 principalDue
,0 interestDue
,0 feesDue
,sum(tr.amount) disbursedAmt
,0 principalPaid
,0 interestPaid
,0 feesPaid
from m_office o 
     join m_office ounder on ounder.hierarchy like concat(o.hierarchy, '%') 
     and ounder.hierarchy like concat(${userhierarchy}, '%') 
inner join m_group gr on gr.office_id = ounder.id
inner join m_group cn on cn.id=gr.parent_id
inner join m_group_client gc on gc.group_id=gr.id
inner join m_client cl on cl.id=gc.client_id
left join m_loan l on l.client_id=cl.id and l.group_id=gr.id 
left join m_staff s on s.id=l.loan_officer_id
inner join m_loan_transaction tr on tr.loan_id=l.id and tr.transaction_type_enum=1
where tr.is_reversed=0
and tr.transaction_date=${onDate}
and o.id=${branch}
group by cn.id

union

select cn.id cid 
,ounder.name branch
,cn.display_name center
,ifnull(s.display_name,'NA') loanOfficer
,tr.transaction_date dueDate
,0 disburswementAmount
,0 principalDue
,0 interestDue
,0 feesDue
,0 disbursedAmt
,sum(ifnull(tr.principal_portion_derived,0)) principalPaid
,sum(ifnull(tr.interest_portion_derived,0)) interestPaid
,0 feesPaid
from m_office o 
     join m_office ounder on ounder.hierarchy like concat(o.hierarchy, '%') 
     and ounder.hierarchy like concat(${userhierarchy}, '%') 
inner join m_group gr on gr.office_id = ounder.id
inner join m_group cn on cn.id=gr.parent_id
inner join m_group_client gc on gc.group_id=gr.id
inner join m_client cl on cl.id=gc.client_id
left join m_loan l on l.client_id=cl.id and l.group_id=gr.id 
left join m_staff s on s.id=l.loan_officer_id
inner join m_loan_transaction tr on tr.loan_id=l.id and tr.transaction_type_enum=2
where tr.is_reversed=0
and tr.transaction_date=${onDate}
and o.id=${branch}
group by cn.id

union

select cn.id cid 
,ounder.name branch
,cn.display_name center
,ifnull(s.display_name,'NA') loanOfficer
,tr.transaction_date dueDate
,0 disburswementAmount
,0 principalDue
,0 interestDue
,0 feesDue
,0 disbursedAmt
,0 principalPaid
,0 interestPaid
,sum(ifnull(tr.fee_charges_portion_derived,0)) feesPaid
from m_office o 
     join m_office ounder on ounder.hierarchy like concat(o.hierarchy, '%') 
     and ounder.hierarchy like concat(${userhierarchy}, '%') 
inner join m_group gr on gr.office_id = ounder.id
inner join m_group cn on cn.id=gr.parent_id
inner join m_group_client gc on gc.group_id=gr.id
inner join m_client cl on cl.id=gc.client_id
left join m_loan l on l.client_id=cl.id and l.group_id=gr.id 
left join m_staff s on s.id=l.loan_officer_id
inner join m_loan_transaction tr on tr.loan_id=l.id and tr.transaction_type_enum=5
where tr.is_reversed=0
and tr.transaction_date=${onDate}
and o.id=${branch}
group by cn.id
order by 2,3) a
group by a.cid
order by 2,3