| |
| update stretchy_report |
| set report_sql = |
| " |
| select l.id as loanId, l.account_no as loanAccountNo, c.id as clientId, c.account_no as clientAccountNo, |
| pgm.display_name as programName, |
| |
| (select count(*) |
| from m_loan cy |
| where cy.group_id = pgm.id and cy.client_id =c.id |
| and cy.disbursedon_date <= l.disbursedon_date) as loanCycleNo, |
| |
| c.display_name as clientDisplayName, |
| ifnull(cur.display_symbol, l.currency_code) as Currency, |
| ifnull(l.principal_repaid_derived,0.0) as loanRepaidAmount, |
| ifnull(l.principal_outstanding_derived, 0.0) as loanOutstandingAmount, |
| ifnull(lpa.principal_in_advance_derived,0.0) as LoanPaidInAdvance, |
| |
| ifnull(laa.principal_overdue_derived, 0.0) as loanInArrearsAmount, |
| if(ifnull(laa.principal_overdue_derived, 0.00) > 0, 'Yes', 'No') as inDefault, |
| |
| if(date_sub(curdate(), interval 28 day) > ifnull(laa.overdue_since_date_derived, curdate()), |
| l.principal_outstanding_derived,0) as portfolioAtRisk |
| |
| from m_group pgm |
| join m_office o on o.id = pgm.office_id |
| and o.hierarchy like concat('${currentUserHierarchy}', '%') |
| join m_loan l on l.group_id = pgm.id and l.client_id is not null |
| left join m_currency cur on cur.code = l.currency_code |
| join m_client c on c.id = l.client_id |
| left join m_loan_arrears_aging laa on laa.loan_id = l.id |
| left join m_loan_paid_in_advance lpa on lpa.loan_id = l.id |
| where pgm.id = ${programId} |
| and l.loan_status_id = 300 |
| order by c.display_name, l.account_no |
| |
| " |
| where report_name = 'ProgramDetails'; |
