(SELECT a.OfficeName,
a.OfficeID,
a.StaffName,
PAROLB_30, IFNULL(((PAROLB_30 / StaffOLB)),0) as PAR_30, PAR30_NOL, IFNULL((PAR30_NOL / StaffCount),0) as PAR30_COUNT,
PAROLB_60, IFNULL(((PAROLB_60 / StaffOLB)),0) as PAR_60, PAR60_NOL, IFNULL((PAR60_NOL / StaffCount),0) as PAR60_COUNT,
PAROLB_90, IFNULL(((PAROLB_90 / StaffOLB)),0) as PAR_90, PAR90_NOL, IFNULL((PAR90_NOL / StaffCount),0) as PAR90_COUNT,
PAROLB_91, IFNULL(((PAROLB_91 / StaffOLB)),0) as PAR_91, PAR91_NOL, IFNULL((PAR91_NOL / StaffCount),0) as PAR91_COUNT,
PAROLB_0, IFNULL((PAROLB_0 / StaffOLB),0) as PAR_0, PAR0_NOL, IFNULL((PAR0_NOL / StaffCount),0) as PAR0_COUNT,
IFNULL(PAROLB_1,0) as PAROLB_1, IFNULL((PAROLB_1 / StaffOLB),0) as PAR_1, PAR1_NOL, IFNULL((PAR1_NOL / StaffCount),0) as PAR1_COUNT,
IFNULL(PAROLB_31,0) as PAROLB_31, IFNULL((PAROLB_31 / StaffOLB),0) as PAR_31, PAR31_NOL, IFNULL((PAR31_NOL / StaffCount),0) as PAR31_COUNT,
IFNULL(StaffOLB,0) as StaffOLB, IFNULL(StaffCount,0) as StaffCount,
IFNULL(StaffIOUT,0) as StaffIOUT,
IFNULL(StaffFOUT,0) as StaffFOUT,
IFNULL(StaffPIFOUT,0) as StaffPIFOUT,
IFNULL(CompSavings,0) as CompSavings,
IFNULL(VolSavings,0) as VolSavings
FROM
-- inline table to calculate all the loan balances and arrears bands:
(select
mo.name as OfficeName,
mo.id as OfficeID,
mst.display_name as StaffName,
mst.id as StaffID,
SUM(CASE WHEN IFNULL(days_in_arrears,0) BETWEEN 1 AND 30 THEN (IFNULL(ml.`principal_disbursed_derived`,0) - IFNULL(mlt.principal_paid,0)) ELSE 0 END) as PAROLB_30,
SUM(
IF(
IFNULL(days_in_arrears,0) BETWEEN 1 AND 30
,1,0)) as PAR30_NOL,
SUM(CASE WHEN IFNULL(days_in_arrears,0) BETWEEN 31 AND 60 THEN (IFNULL(ml.`principal_disbursed_derived`,0) - IFNULL(mlt.principal_paid,0)) ELSE 0 END) as PAROLB_60,
SUM(IF(IFNULL(days_in_arrears,0) BETWEEN 31 AND 60,1,0)) as PAR60_NOL,
SUM(CASE WHEN IFNULL(days_in_arrears,0) BETWEEN 61 AND 90 THEN (IFNULL(ml.`principal_disbursed_derived`,0) - IFNULL(mlt.principal_paid,0)) ELSE 0 END) as PAROLB_90,
SUM(IF(IFNULL(days_in_arrears,0) BETWEEN 61 AND 90,1,0)) as PAR90_NOL,
SUM(CASE WHEN IFNULL(days_in_arrears,0) >= 90 THEN (IFNULL(ml.`principal_disbursed_derived`,0) - IFNULL(mlt.principal_paid,0)) ELSE 0 END) as PAROLB_91,
SUM(IF(IFNULL(days_in_arrears,0) >= 90,1,0)) as PAR91_NOL,
SUM(CASE WHEN IFNULL(days_in_arrears,0) = 0 THEN (IFNULL(ml.`principal_disbursed_derived`,0) - IFNULL(mlt.principal_paid,0)) ELSE 0 END) as PAROLB_0,
SUM(IF((days_in_arrears IS NULL AND ml.loan_status_id IN (300,602)),1,0)) as PAR0_NOL, -- All ontime loans
SUM(CASE WHEN IFNULL(days_in_arrears,0) > 0 THEN (IFNULL(ml.`principal_disbursed_derived`,0) - IFNULL(mlt.principal_paid,0)) ELSE 0 END) as PAROLB_1,
SUM(IF(IFNULL(days_in_arrears,0) > 0,1,0)) as PAR1_NOL,
SUM(CASE WHEN IFNULL(days_in_arrears,0) > 30 THEN (IFNULL(ml.`principal_disbursed_derived`,0) - IFNULL(mlt.principal_paid,0)) ELSE 0 END) as PAROLB_31,
SUM(IF(IFNULL(days_in_arrears,0) > 30,1,0)) as PAR31_NOL, -- All over 30 days
SUM((IFNULL(ml.`principal_disbursed_derived`,0) - IFNULL(mlt.principal_paid,0))) as StaffOLB,
SUM((IFNULL(ml.`interest_charged_derived`,0) - IFNULL(mlt.interest_paid,0))) as StaffIOUT,
SUM((IFNULL((fee_charges_charged_derived + penalty_charges_charged_derived),0) - (IFNULL(mlt.fee_charges_paid,0) + IFNULL(mlt.penalty_charges_paid,0)))) as StaffFOUT,
SUM(IFNULL((principal_disbursed_derived + interest_charged_derived + fee_charges_charged_derived + penalty_charges_charged_derived),0) - IFNULL(mlt.total_paid,0)) as StaffPIFOUT,
COUNT(ml.id) as StaffCount
FROM
m_office mo
JOIN m_office ounder ON ounder.hierarchy LIKE CONCAT(mo.hierarchy, '%')
AND ounder.hierarchy like CONCAT('.', '%')
LEFT JOIN m_client mc ON mc.office_id=ounder.id
JOIN m_loan ml ON ml.client_id=mc.id AND ml.loan_status_id IN (300,602,700)
JOIN m_staff mst ON ml.loan_officer_id = mst.id
left join
( SELECT
loan_id,
SUM(IFNULL(principal_portion_derived,0)) as principal_paid,
SUM(IFNULL(interest_portion_derived,0)) as interest_paid,
SUM(IFNULL(fee_charges_portion_derived,0)) as fee_charges_paid,
SUM(IFNULL(penalty_charges_portion_derived,0)) as penalty_charges_paid,
IFNULL((SUM(IFNULL(principal_portion_derived,0)) + SUM(IFNULL(interest_portion_derived,0)) + SUM(IFNULL(fee_charges_portion_derived,0)) + SUM(IFNULL(penalty_charges_portion_derived,0))),0) AS total_paid
FROM m_loan_transaction
where is_reversed = 0
and `transaction_type_enum` = 2
and transaction_date <= ${toDate}
group by loan_id)
as mlt
on ml.id = mlt.loan_id
LEFT JOIN
( SELECT
loan_id,
SUM(IFNULL(principal_amount,0)) as expected_principal,
SUM(IFNULL(interest_amount,0)) as expected_interest,
SUM(IFNULL(fee_charges_amount,0)) as expected_fee_charges,
SUM(IFNULL(penalty_charges_amount,0)) as expected_penalty_charges,
IFNULL((SUM(IFNULL(principal_amount,0)) + SUM(IFNULL(interest_amount,0)) + SUM(IFNULL(fee_charges_amount,0)) + SUM(IFNULL(penalty_charges_amount,0))),0) AS total_expected
FROM
m_loan_repayment_schedule
WHERE `duedate` <= ${toDate}
group by loan_id
) as mls
on ml.id = mls.loan_id
left JOIN
(
SELECT
DATEDIFF(DATE(${toDate}), duedate) as "days_in_arrears",
lrs.duedate as arrears_duedate,
lrs.loan_id
FROM m_loan_repayment_schedule lrs
WHERE `duedate` < ${toDate}
and (( obligations_met_on_date > duedate
and obligations_met_on_date > ${toDate})
OR (obligations_met_on_date is null))
GROUP BY loan_id HAVING duedate = MIN(duedate)
) as arr ON ml.id = arr.loan_id
WHERE ml.`loan_status_id` = 300
and ml.disbursedon_date <= ${toDate}
and mo.id = ${selectOffice}
GROUP BY mo.id, mst.id
ORDER BY OfficeId)
as a
LEFT JOIN
(SELECT
ounder.id as OfficeID,
mst.id as StaffID,
SUM(IF(cv.code_value = 'Compulsory',msa.account_balance_derived,0)) as CompSavings,
SUM(IF(cv.code_value = 'Voluntary',msa.account_balance_derived,0)) as VolSavings
FROM
m_office mo
JOIN m_office ounder ON ounder.hierarchy LIKE CONCAT(mo.hierarchy, '%')
AND ounder.hierarchy like CONCAT('.', '%')
right JOIN m_client mc ON mc.office_id=ounder.id AND mc.status_enum > 200
LEFT JOIN m_savings_account as msa ON msa.`client_id` = mc.id
JOIN m_staff mst ON msa.`field_officer_id` = mst.id
LEFT JOIN
ml_product_savings_category as msc ON msa.product_id = msc.savings_product_id LEFT JOIN m_code_value as cv ON msc.`SavingsCategory_cd` = cv.id
WHERE mo.id=${selectOffice}
GROUP BY mo.id, mst.id) as b ON a.OfficeID = b.OfficeID AND a.StaffID = b.StaffID
GROUP BY a.OfficeID, a.StaffName);
