Light Collection sheet Query

CollectionSheetQuery.sql

SELECT loandata.*
,ifnull(group_concat(c.name,':',convert(lc.amount,decimal(10,2))  separator ', '),'') fees
, SUM(ifnull(lc.amount_outstanding_derived,0)) AS chargesDue
#,ld.principal_amount_proposed
FROM
 (
SELECT cn.display_name centerName
			,cn.id centerId
			,gp.display_name AS groupName
			,gp.id AS groupId
			,cl.account_no clientAccNo
			,cl.display_name AS clientName
			,pl.name AS productName
			,sf.id AS staffId
			,sf.display_name AS staffName
			,gl.id AS levelId
			,gl.level_name AS levelName
			,cl.id AS clientId
			,ln.id AS loanId
			,ln.account_no loanAccNo
			,ln.account_no AS accountId
			,ln.loan_status_id AS accountStatusId
			,concat(pl.short_name,'-',ln.id) productAndLoanID
			,ln.product_id AS productId
			,ln.currency_code AS currencyCode
			,ln.currency_digits AS currencyDigits
			,ln.currency_multiplesof AS inMultiplesOf
			,rc.`name` AS currencyName
			,rc.display_symbol AS currencyDisplaySymbol
			,rc.internationalized_name_code AS currencyNameCode
			,IF(ln.loan_status_id = 200, ln.principal_amount, NULL) AS disbursementAmount
			, SUM(IFNULL(IF(ln.loan_status_id = 300, ls.principal_amount, 0.0), 0.0) - IFNULL(IF(ln.loan_status_id = 300, ls.principal_completed_derived, 0.0), 0.0)) AS principalDue
			,ln.principal_repaid_derived AS principalPaid
			,SUM(IFNULL(IF(ln.loan_status_id = 300, ls.interest_amount, 0.0), 0.0) - IFNULL(IF(ln.loan_status_id = 300, ls.interest_completed_derived, 0.0), 0.0)) AS interestDue
			,ln.interest_repaid_derived AS interestPaid
			,ca.attendance_type_enum AS attendanceTypeId
			,max(ls.installment) ins
			,ln.principal_outstanding_derived
FROM m_group gp
LEFT JOIN m_office of ON of.id = gp.office_id AND of.id = ${branch}
JOIN m_group_level gl ON gl.id = gp.level_Id
LEFT JOIN m_staff sf ON sf.id = gp.staff_id
JOIN m_group_client gc ON gc.group_id = gp.id
JOIN m_client cl ON cl.id = gc.client_id
LEFT JOIN m_group cn ON cn.id = gp.parent_id
LEFT JOIN m_loan ln ON cl.id = ln.client_id AND ln.group_id=gp.id AND ln.group_id IS NOT NULL AND (ln.loan_status_id = 300)
LEFT JOIN m_product_loan pl ON pl.id = ln.product_id
LEFT JOIN m_currency rc ON rc.`code` = ln.currency_code
LEFT JOIN m_loan_repayment_schedule ls ON ls.loan_id = ln.id AND ls.completed_derived = 0 AND ls.duedate <= ${onDate}
LEFT JOIN m_calendar_instance ci ON gp.parent_id = ci.entity_id AND ci.entity_type_enum =4
LEFT JOIN m_meeting mt ON ci.id = mt.calendar_instance_id AND mt.meeting_date = ${onDate}
LEFT JOIN m_client_attendance ca ON ca.meeting_id=mt.id AND ca.client_id=cl.id
WHERE (gp.staff_id=${staff} or ${staff}=-1) AND (gp.parent_id = ${center} OR -1 = ${center}) AND (ln.loan_status_id != 200 AND ln.loan_status_id != 100) AND (gp.status_enum = 300 OR (gp.status_enum = 600 AND gp.closedon_date >= ${onDate})) AND (cl.status_enum = 300 OR (cl.status_enum = 600 AND cl.closedon_date >= ${onDate}))
GROUP BY gp.id,cl.id, ln.id
ORDER BY gp.id, cl.id, ln.id) loandata
#left join m_loan ld on ld.client_id=loandata.clientId and ld.group_id=loandata.groupId and ld.expected_disbursedon_date='' and ld.loan_status_id=200
LEFT JOIN m_loan_charge lc ON lc.loan_id = loandata.loanId AND lc.is_paid_derived = 0 AND lc.is_active = 1 AND (lc.due_for_collection_as_of_date <= ${onDate} OR lc.charge_time_enum = 1)
left join m_charge c on c.id=lc.charge_id
GROUP BY loandata.groupId,loandata.clientId,loandata.loanId
HAVING loandata.loanId > 0 and loandata.principalDue >0 and loandata.interestDue>0

union

SELECT disbDue.*
, ifnull(group_concat(ch.name,':',convert(lc.amount,decimal(10,2)) separator ', '),'') fees
, SUM(ifnull(lc.amount,0)) AS chargesDue
#,ld.principal_amount_proposed
FROM (select cn.display_name centerName
			,cn.id centerId
			,gp.display_name AS groupName
			,gp.id AS groupId
			,cl.account_no clientAccNo
			,cl.display_name AS clientName
			,pl.name AS productName
			,sf.id AS staffId
			,sf.display_name AS staffName
			,gl.id AS levelId
			,gl.level_name AS levelName
			,cl.id AS clientId
			,ln.id AS loanId
			,ln.account_no loanAccNo
			,ln.account_no AS accountId
			,ln.loan_status_id AS accountStatusId
			,concat(pl.short_name,'-',ln.id) productAndLoanID
			,ln.product_id AS productId
			,ln.currency_code AS currencyCode
			,ln.currency_digits AS currencyDigits
			,ln.currency_multiplesof AS inMultiplesOf
			,rc.`name` AS currencyName
			,rc.display_symbol AS currencyDisplaySymbol
			,rc.internationalized_name_code AS currencyNameCode
			,IF(ln.loan_status_id = 200, ln.principal_amount, NULL) AS disbursementAmount
			,0 AS principalDue
			,0 AS principalPaid
			,0 AS interestDue
			,0 AS interestPaid
			,ca.attendance_type_enum AS attendanceTypeId
			,'' ins
			,0 As principal_outstanding_derived
FROM m_group gp
LEFT JOIN m_office of ON of.id = gp.office_id AND of.id = 10
JOIN m_group_level gl ON gl.id = gp.level_Id
LEFT JOIN m_staff sf ON sf.id = gp.staff_id
JOIN m_group_client gc ON gc.group_id = gp.id
JOIN m_client cl ON cl.id = gc.client_id
LEFT JOIN m_group cn ON cn.id = gp.parent_id
LEFT JOIN m_loan ln ON cl.id = ln.client_id AND ln.group_id=gp.id AND ln.group_id IS NOT NULL AND (ln.loan_status_id = 200) and ln.expected_disbursedon_date=${onDate}
LEFT JOIN m_product_loan pl ON pl.id = ln.product_id
LEFT JOIN m_currency rc ON rc.`code` = ln.currency_code
LEFT JOIN m_calendar_instance ci ON gp.parent_id = ci.entity_id AND ci.entity_type_enum =4
LEFT JOIN m_meeting mt ON ci.id = mt.calendar_instance_id AND mt.meeting_date = ${onDate}
LEFT JOIN m_client_attendance ca ON ca.meeting_id=mt.id AND ca.client_id=cl.id
WHERE (gp.staff_id=${staff} or ${staff}=-1) 
AND (gp.parent_id = ${center} OR -1 = ${center}) 
AND (ln.loan_status_id = 200) 
AND (gp.status_enum = 300 OR (gp.status_enum = 600 AND gp.closedon_date >= ${onDate})) 
AND (cl.status_enum = 300 OR (cl.status_enum = 600 AND cl.closedon_date >= ${onDate}))
GROUP BY gp.id,cl.id, ln.id) disbDue
LEFT JOIN m_loan_charge lc ON lc.loan_id = disbDue.loanId AND lc.is_paid_derived = 0 AND lc.is_active = 1 AND ( lc.charge_time_enum = 1)
inner join m_charge ch on ch.id=lc.charge_id
GROUP BY disbDue.groupId,disbDue.clientId,disbDue.loanId
ORDER BY centerName,centerId,groupId,clientId,loanId