| /* Leaving as out-of-box (but non-core reports) for now |
| could be removed later if people think too many non-generic |
| reports are being added |
| used in quipo TEVI dashboard (landing pages) */ |
| |
| DELETE FROM `stretchy_report` where report_name = 'FieldAgentStats'; |
| DELETE FROM `stretchy_report` where report_name = 'FieldAgentPrograms'; |
| DELETE FROM `stretchy_report` where report_name = 'ProgramDetails'; |
| DELETE FROM `stretchy_report` where report_name = 'ChildrenStaffList'; |
| DELETE FROM `stretchy_report` where report_name = 'CoordinatorStats'; |
| DELETE FROM `stretchy_report` where report_name = 'BranchManagerStats'; |
| DELETE FROM `stretchy_report` where report_name = 'ProgramDirectorStats'; |
| DELETE FROM `stretchy_report` where report_name = 'ProgramStats'; |
| |
| DELETE FROM `m_permission` where entity_name = 'FieldAgentStats'; |
| DELETE FROM `m_permission` where entity_name = 'FieldAgentPrograms'; |
| DELETE FROM `m_permission` where entity_name = 'ProgramDetails'; |
| DELETE FROM `m_permission` where entity_name = 'ChildrenStaffList'; |
| DELETE FROM `m_permission` where entity_name = 'CoordinatorStats'; |
| DELETE FROM `m_permission` where entity_name = 'BranchManagerStats'; |
| DELETE FROM `m_permission` where entity_name = 'ProgramDirectorStats'; |
| DELETE FROM `m_permission` where entity_name = 'ProgramStats'; |
| |
| |
| INSERT INTO `stretchy_report` (`report_name`, `report_type`, `report_category`, `core_report`, `use_report`, `description`, `report_sql`) |
| VALUES ('FieldAgentStats', 'Table', 'Quipo', false, false, 'Field Agent Statistics', |
| |
| " |
| select ifnull(cur.display_symbol, l.currency_code) as Currency, |
| /*This query will return more than one entry if more than one currency is used */ |
| count(distinct(c.id)) as activeClients, count(*) as activeLoans, |
| sum(l.principal_disbursed_derived) as disbursedAmount, |
| sum(l.principal_outstanding_derived) as loanOutstandingAmount, |
| round((sum(l.principal_outstanding_derived) * 100) / sum(l.principal_disbursed_derived),2) as loanOutstandingPC, |
| '???' as loanOverPaymentAmount, |
| sum( |
| if(date_sub(curdate(), interval 28 day) > ifnull(laa.overdue_since_date_derived, curdate()), |
| l.principal_outstanding_derived,0)) as portfolioAtRisk, |
| |
| round((sum( |
| if(date_sub(curdate(), interval 28 day) > ifnull(laa.overdue_since_date_derived, curdate()), |
| l.principal_outstanding_derived,0)) * 100) / sum(l.principal_outstanding_derived), 2) as portfolioAtRiskPC, |
| |
| count(distinct( |
| if(date_sub(curdate(), interval 28 day) > ifnull(laa.overdue_since_date_derived, curdate()), |
| c.id,null))) as clientsInDefault, |
| round((count(distinct( |
| if(date_sub(curdate(), interval 28 day) > ifnull(laa.overdue_since_date_derived, curdate()), |
| c.id,null))) * 100) / count(distinct(c.id)),2) as clientsInDefaultPC, |
| (sum(l.principal_disbursed_derived) / count(*)) as averageLoanAmount |
| from m_staff fa |
| join m_office o on o.id = fa.office_id |
| and o.hierarchy like concat('${currentUserHierarchy}', '%') |
| join m_group pgm on pgm.staff_id = fa.id |
| 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 |
| left join m_loan_arrears_aging laa on laa.loan_id = l.id |
| join m_client c on c.id = l.client_id |
| where fa.id = ${staffId} |
| and l.loan_status_id = 300 |
| group by l.currency_code |
| "); |
| |
| |
| INSERT INTO `stretchy_report` (`report_name`, `report_type`, `report_category`, `core_report`, `use_report`, `description`, `report_sql`) |
| VALUES ('FieldAgentPrograms', 'Table', 'Quipo', false, false, 'List of Field Agent Programs', |
| |
| " |
| select pgm.id, pgm.display_name as `name`, sts.enum_message_property as status |
| from m_group pgm |
| join m_office o on o.id = pgm.office_id |
| and o.hierarchy like concat('${currentUserHierarchy}', '%') |
| left join r_enum_value sts on sts.enum_name = 'status_enum' and sts.enum_id = pgm.status_enum |
| where pgm.staff_id = ${staffId} |
| "); |
| |
| |
| INSERT INTO `stretchy_report` (`report_name`, `report_type`, `report_category`, `core_report`, `use_report`, `description`, `report_sql`) |
| VALUES ('ProgramDetails', 'Table', 'Quipo', false, false, 'List of Loans in a Program', |
| |
| " |
| 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, |
| '???' as loanInAdvanceAmount, |
| |
| 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 |
| where pgm.id = ${programId} |
| and l.loan_status_id = 300 |
| order by c.display_name, l.account_no |
| |
| "); |
| |
| |
| INSERT INTO `stretchy_report` (`report_name`, `report_type`, `report_category`, `core_report`, `use_report`, `description`, `report_sql`) |
| VALUES ('ChildrenStaffList', 'Table', 'Quipo', false, false, 'Get Next Level Down Staff', |
| |
| " |
| select s.id, s.display_name, |
| s.firstname, s.lastname, s.organisational_role_enum, |
| s.organisational_role_parent_staff_id, |
| sp.display_name as `organisational_role_parent_staff_display_name` |
| from m_staff s |
| join m_staff sp on s.organisational_role_parent_staff_id = sp.id |
| where s.organisational_role_parent_staff_id = ${staffId} |
| "); |
| |
| |
| INSERT INTO `stretchy_report` (`report_name`, `report_type`, `report_category`, `core_report`, `use_report`, `description`, `report_sql`) |
| VALUES ('CoordinatorStats', 'Table', 'Quipo', false, false, 'Coordinator Statistics', |
| |
| " |
| select ifnull(cur.display_symbol, l.currency_code) as Currency, |
| /*This query will return more than one entry if more than one currency is used */ |
| count(distinct(c.id)) as activeClients, count(*) as activeLoans, |
| sum(l.principal_disbursed_derived) as disbursedAmount, |
| sum(l.principal_outstanding_derived) as loanOutstandingAmount, |
| round((sum(l.principal_outstanding_derived) * 100) / sum(l.principal_disbursed_derived),2) as loanOutstandingPC, |
| '???' as loanOverPaymentAmount, |
| sum( |
| if(date_sub(curdate(), interval 28 day) > ifnull(laa.overdue_since_date_derived, curdate()), |
| l.principal_outstanding_derived,0)) as portfolioAtRisk, |
| |
| round((sum( |
| if(date_sub(curdate(), interval 28 day) > ifnull(laa.overdue_since_date_derived, curdate()), |
| l.principal_outstanding_derived,0)) * 100) / sum(l.principal_outstanding_derived), 2) as portfolioAtRiskPC, |
| |
| count(distinct( |
| if(date_sub(curdate(), interval 28 day) > ifnull(laa.overdue_since_date_derived, curdate()), |
| c.id,null))) as clientsInDefault, |
| round((count(distinct( |
| if(date_sub(curdate(), interval 28 day) > ifnull(laa.overdue_since_date_derived, curdate()), |
| c.id,null))) * 100) / count(distinct(c.id)),2) as clientsInDefaultPC, |
| (sum(l.principal_disbursed_derived) / count(*)) as averageLoanAmount |
| from m_staff coord |
| join m_staff fa on fa.organisational_role_parent_staff_id = coord.id |
| join m_office o on o.id = fa.office_id |
| and o.hierarchy like concat('${currentUserHierarchy}', '%') |
| join m_group pgm on pgm.staff_id = fa.id |
| 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 |
| left join m_loan_arrears_aging laa on laa.loan_id = l.id |
| join m_client c on c.id = l.client_id |
| where coord.id = ${staffId} |
| and l.loan_status_id = 300 |
| group by l.currency_code |
| "); |
| |
| |
| INSERT INTO `stretchy_report` (`report_name`, `report_type`, `report_category`, `core_report`, `use_report`, `description`, `report_sql`) |
| VALUES ('BranchManagerStats', 'Table', 'Quipo', false, false, 'Branch Manager Statistics', |
| |
| " |
| select ifnull(cur.display_symbol, l.currency_code) as Currency, |
| /*This query will return more than one entry if more than one currency is used */ |
| count(distinct(c.id)) as activeClients, count(*) as activeLoans, |
| sum(l.principal_disbursed_derived) as disbursedAmount, |
| sum(l.principal_outstanding_derived) as loanOutstandingAmount, |
| round((sum(l.principal_outstanding_derived) * 100) / sum(l.principal_disbursed_derived),2) as loanOutstandingPC, |
| '???' as loanOverPaymentAmount, |
| sum( |
| if(date_sub(curdate(), interval 28 day) > ifnull(laa.overdue_since_date_derived, curdate()), |
| l.principal_outstanding_derived,0)) as portfolioAtRisk, |
| |
| round((sum( |
| if(date_sub(curdate(), interval 28 day) > ifnull(laa.overdue_since_date_derived, curdate()), |
| l.principal_outstanding_derived,0)) * 100) / sum(l.principal_outstanding_derived), 2) as portfolioAtRiskPC, |
| |
| count(distinct( |
| if(date_sub(curdate(), interval 28 day) > ifnull(laa.overdue_since_date_derived, curdate()), |
| c.id,null))) as clientsInDefault, |
| round((count(distinct( |
| if(date_sub(curdate(), interval 28 day) > ifnull(laa.overdue_since_date_derived, curdate()), |
| c.id,null))) * 100) / count(distinct(c.id)),2) as clientsInDefaultPC, |
| (sum(l.principal_disbursed_derived) / count(*)) as averageLoanAmount |
| from m_staff bm |
| join m_staff coord on coord.organisational_role_parent_staff_id = bm.id |
| join m_staff fa on fa.organisational_role_parent_staff_id = coord.id |
| join m_office o on o.id = fa.office_id |
| and o.hierarchy like concat('${currentUserHierarchy}', '%') |
| join m_group pgm on pgm.staff_id = fa.id |
| 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 |
| left join m_loan_arrears_aging laa on laa.loan_id = l.id |
| join m_client c on c.id = l.client_id |
| where bm.id = ${staffId} |
| and l.loan_status_id = 300 |
| group by l.currency_code |
| "); |
| |
| |
| INSERT INTO `stretchy_report` (`report_name`, `report_type`, `report_category`, `core_report`, `use_report`, `description`, `report_sql`) |
| VALUES ('ProgramDirectorStats', 'Table', 'Quipo', false, false, 'Program DirectorStatistics', |
| |
| " |
| select ifnull(cur.display_symbol, l.currency_code) as Currency, |
| /*This query will return more than one entry if more than one currency is used */ |
| count(distinct(c.id)) as activeClients, count(*) as activeLoans, |
| sum(l.principal_disbursed_derived) as disbursedAmount, |
| sum(l.principal_outstanding_derived) as loanOutstandingAmount, |
| round((sum(l.principal_outstanding_derived) * 100) / sum(l.principal_disbursed_derived),2) as loanOutstandingPC, |
| '???' as loanOverPaymentAmount, |
| sum( |
| if(date_sub(curdate(), interval 28 day) > ifnull(laa.overdue_since_date_derived, curdate()), |
| l.principal_outstanding_derived,0)) as portfolioAtRisk, |
| |
| round((sum( |
| if(date_sub(curdate(), interval 28 day) > ifnull(laa.overdue_since_date_derived, curdate()), |
| l.principal_outstanding_derived,0)) * 100) / sum(l.principal_outstanding_derived), 2) as portfolioAtRiskPC, |
| |
| count(distinct( |
| if(date_sub(curdate(), interval 28 day) > ifnull(laa.overdue_since_date_derived, curdate()), |
| c.id,null))) as clientsInDefault, |
| round((count(distinct( |
| if(date_sub(curdate(), interval 28 day) > ifnull(laa.overdue_since_date_derived, curdate()), |
| c.id,null))) * 100) / count(distinct(c.id)),2) as clientsInDefaultPC, |
| (sum(l.principal_disbursed_derived) / count(*)) as averageLoanAmount |
| from m_staff pd |
| join m_staff bm on bm.organisational_role_parent_staff_id = pd.id |
| join m_staff coord on coord.organisational_role_parent_staff_id = bm.id |
| join m_staff fa on fa.organisational_role_parent_staff_id = coord.id |
| join m_office o on o.id = fa.office_id |
| and o.hierarchy like concat('${currentUserHierarchy}', '%') |
| join m_group pgm on pgm.staff_id = fa.id |
| 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 |
| left join m_loan_arrears_aging laa on laa.loan_id = l.id |
| join m_client c on c.id = l.client_id |
| where pd.id = ${staffId} |
| and l.loan_status_id = 300 |
| group by l.currency_code |
| "); |
| |
| |
| INSERT INTO `stretchy_report` (`report_name`, `report_type`, `report_category`, `core_report`, `use_report`, `description`, `report_sql`) |
| VALUES ('ProgramStats', 'Table', 'Quipo', false, false, 'Program Statistics', |
| |
| " |
| select ifnull(cur.display_symbol, l.currency_code) as Currency, |
| /*This query will return more than one entry if more than one currency is used */ |
| count(distinct(c.id)) as activeClients, count(*) as activeLoans, |
| sum(l.principal_disbursed_derived) as disbursedAmount, |
| sum(l.principal_outstanding_derived) as loanOutstandingAmount, |
| round((sum(l.principal_outstanding_derived) * 100) / sum(l.principal_disbursed_derived),2) as loanOutstandingPC, |
| '???' as loanOverPaymentAmount, |
| sum( |
| if(date_sub(curdate(), interval 28 day) > ifnull(laa.overdue_since_date_derived, curdate()), |
| l.principal_outstanding_derived,0)) as portfolioAtRisk, |
| |
| round((sum( |
| if(date_sub(curdate(), interval 28 day) > ifnull(laa.overdue_since_date_derived, curdate()), |
| l.principal_outstanding_derived,0)) * 100) / sum(l.principal_outstanding_derived), 2) as portfolioAtRiskPC, |
| |
| count(distinct( |
| if(date_sub(curdate(), interval 28 day) > ifnull(laa.overdue_since_date_derived, curdate()), |
| c.id,null))) as clientsInDefault, |
| round((count(distinct( |
| if(date_sub(curdate(), interval 28 day) > ifnull(laa.overdue_since_date_derived, curdate()), |
| c.id,null))) * 100) / count(distinct(c.id)),2) as clientsInDefaultPC, |
| (sum(l.principal_disbursed_derived) / count(*)) as averageLoanAmount |
| 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 |
| left join m_loan_arrears_aging laa on laa.loan_id = l.id |
| join m_client c on c.id = l.client_id |
| where pgm.id = ${programId} |
| and l.loan_status_id = 300 |
| group by l.currency_code |
| "); |
| |
| |
| |
| INSERT INTO `m_permission` (`grouping`, `code`, `entity_name`, `action_name`, `can_maker_checker`) |
| VALUES ('report', 'READ_FieldAgentStats', 'FieldAgentStats', 'READ', 0); |
| |
| INSERT INTO `m_permission` (`grouping`, `code`, `entity_name`, `action_name`, `can_maker_checker`) |
| VALUES ('report', 'READ_FieldAgentPrograms', 'FieldAgentPrograms', 'READ', 0); |
| |
| INSERT INTO `m_permission` (`grouping`, `code`, `entity_name`, `action_name`, `can_maker_checker`) |
| VALUES ('report', 'READ_ProgramDetails', 'ProgramDetails', 'READ', 0); |
| |
| INSERT INTO `m_permission` (`grouping`, `code`, `entity_name`, `action_name`, `can_maker_checker`) |
| VALUES ('report', 'READ_ChildrenStaffList', 'ChildrenStaffList', 'READ', 0); |
| |
| INSERT INTO `m_permission` (`grouping`, `code`, `entity_name`, `action_name`, `can_maker_checker`) |
| VALUES ('report', 'READ_CoordinatorStats', 'CoordinatorStats', 'READ', 0); |
| |
| INSERT INTO `m_permission` (`grouping`, `code`, `entity_name`, `action_name`, `can_maker_checker`) |
| VALUES ('report', 'READ_BranchManagerStats', 'BranchManagerStats', 'READ', 0); |
| |
| INSERT INTO `m_permission` (`grouping`, `code`, `entity_name`, `action_name`, `can_maker_checker`) |
| VALUES ('report', 'READ_ProgramDirectorStats', 'ProgramDirectorStats', 'READ', 0); |
| |
| INSERT INTO `m_permission` (`grouping`, `code`, `entity_name`, `action_name`, `can_maker_checker`) |
| VALUES ('report', 'READ_ProgramStats', 'ProgramStats', 'READ', 0); |
