...
By using the following query we can get all active loan ids along with product id, number of days overdue, outstanding loan balance, provision category id, provision percentage, due date
SELECT loan.id as loanid,
loan.product_id as productid,
GREATEST(datediff(CURDATE(),sch.duedate),0) as numberofdaysoverdue,
loan.total_outstanding_derived as outstandingbalance,
lpc.category_id,
lpc.provision_percentage,
sch.duedate
FROM m_loan_repayment_schedule as sch
LEFT JOIN m_loan as loan
ON sch.loan_id = loan.id
LEFT JOIN m_loanproduct_provision_category lpc
ON loan.product_id=lpc.product_id
and lpc.min_age <= GREATEST(datediff(CURDATE(),sch.duedate),0) and GREATEST(datediff(CURDATE(),sch.duedate),0) < lpc.max_age
WHERE loan.loan_status_id=300 and sch.principal_amount > 0 and sch.completed_derived=false and
lpc.category_id > 0 and lpc.provision_percentage > 0
GROUP BY sch.loan_id
HAVING sch.duedate=min(sch.duedate)
...
| loanid | productid | numberofdaysoverdue | outstandingbalance | category_id | provision_percentage | duedate |
|---|---|---|---|---|---|---|
| 4 | 4 | 0 | 11150.000000 | 1 | 5 | 2015-10-28 |
| 5 | 5 | 0 | 11310.000000 | 1 | 9 | 2015-10-28 |
| 13 | 11 | 692 | 16300.000000 | 4 | 100 | 2013-10-15 |
The charge calculation for above data is shown below.
| loanid | productid | numberofdaysoverdue | outstandingbalance | category_id | provision_percentage | duedate | Amount to be reserved |
|---|---|---|---|---|---|---|---|
| 4 | 4 | 0 | 11150.000000 | 1 | 5 | 2015-10-28 | 557.500000 |
| 5 | 5 | 0 | 11310.000000 | 1 | 9 | 2015-10-28 | 1017.900000 |
| 13 | 11 | 692 | 16300.000000 | 4 | 100 | 2013-10-15 | 13040.000000 |