Repository: fineract Updated Branches: refs/heads/develop a4c0a6826 -> 2614ffdb2
http://git-wip-us.apache.org/repos/asf/fineract/blob/c338c175/fineract-provider/src/main/resources/sql/migrations/core_db/V322_2__email_business_rules.sql ---------------------------------------------------------------------- diff --git a/fineract-provider/src/main/resources/sql/migrations/core_db/V322_2__email_business_rules.sql b/fineract-provider/src/main/resources/sql/migrations/core_db/V322_2__email_business_rules.sql new file mode 100644 index 0000000..990f18d --- /dev/null +++ b/fineract-provider/src/main/resources/sql/migrations/core_db/V322_2__email_business_rules.sql @@ -0,0 +1,127 @@ +-- +-- Licensed to the Apache Software Foundation (ASF) under one +-- or more contributor license agreements. See the NOTICE file +-- distributed with this work for additional information +-- regarding copyright ownership. The ASF licenses this file +-- to you under the Apache License, Version 2.0 (the +-- "License"); you may not use this file except in compliance +-- with the License. You may obtain a copy of the License at +-- +-- http://www.apache.org/licenses/LICENSE-2.0 +-- +-- Unless required by applicable law or agreed to in writing, +-- software distributed under the License is distributed on an +-- "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY +-- KIND, either express or implied. See the License for the +-- specific language governing permissions and limitations +-- under the License. +-- +INSERT INTO `stretchy_report` (`report_name`, `report_type`, `report_sql`, `description`, `core_report`, `use_report`) VALUES ('Active Clients - Email', 'Email', 'SELECT mc.id, mc.firstname, ifnull(mc.middlename,\'\') as middlename, mc.lastname, mc.display_name as FullName, mc.email_address as EmailAddress,\nmo.name as officename, ifnull(od.phoneNumber,\'\') as officenummber\nFROM\nm_office mo\nJOIN m_office ounder ON ounder.hierarchy LIKE CONCAT(mo.hierarchy, \'%\')\nAND ounder.hierarchy like CONCAT(\'.\', \'%\')\nLEFT JOIN m_client mc on mc.office_id = ounder.id\nleft join ml_office_details as od on od.office_id = mo.id\nWHERE mc.status_enum = 300 and mc.email_address is not null\nand (mo.id = ${officeId} or ${officeId} = -1)\nand (mc.staff_id = ${staffId} or ${staffId} = -1)', 'All clients with the status âActiveâ', '0', '0'); +INSERT INTO `stretchy_report` (`report_name`, `report_type`, `report_sql`, `description`, `core_report`, `use_report`) VALUES ('Prospective Clients - Email', 'Email', 'select mc.id,mo.name as OfficeName, mc.firstname, ifnull(mc.middlename,\'\') as middlename, mc.lastname, mc.display_name as FullName, mc.email_address as EmailAddress,ifnull(od.phoneNumber,\'\') as officenummber\nFROM\nm_office mo\nJOIN m_office ounder ON ounder.hierarchy LIKE CONCAT(mo.hierarchy, \'%\')\nAND ounder.hierarchy like CONCAT(\'.\', \'%\')\nLEFT JOIN m_client mc on mc.office_id = ounder.id\nleft join ml_office_details as od on od.office_id = mo.id\nwhere\n(\nSELECT count(ml.id) as loansCount\nFROM m_loan ml\nWhere ml.client_id = mc.id and (ml.writtenoffon_date>=CURDATE() OR ml.writtenoffon_date IS NULL)\nAND disbursedon_date<=CURDATE()\n) = 0\nAND mc.activation_date<CURDATE()\nAND IFNULL(DATEDIFF(CURDATE(), mc.`activation_date`),0) < 90\nAND (mc.closedon_date >CURDATE() OR mc.closedon_date IS NULL)\nand (m o.id = ${officeId} or ${officeId} = -1)\nand (mc.staff_id = ${staffId} or ${staffId} = -1)', 'All clients with the status âActiveâ who have never had a loan before', '0', '0'); +INSERT INTO `stretchy_report` (`report_name`, `report_type`, `report_sql`, `description`, `core_report`, `use_report`) VALUES ('Active Loan Clients - Email', 'Email', '(select mc.id, mc.firstname, ifnull(mc.middlename,\'\') as middlename, mc.lastname, mc.display_name as FullName, email_address as EmailAddress, round(ml.principal_amount, ml.currency_digits) as LoanAmount, round(ml.`total_outstanding_derived`, ml.currency_digits) as LoanOutstanding, ml.`disbursedon_date` as LoanDisbursed,\nifnull(od.phoneNumber,\'\') as officenummber,ml.`account_no` as LoanAccountId,ifnull(GROUP_CONCAT(DISTINCT t.lastname SEPARATOR \', \') ,\'\') as guarantorLastname,sum(t.totalGuarantors) as numberOfGuarantors,ifnull(mg.display_name,\'individual\') as groupName\nFROM m_office mo\nJOIN m_office ounder ON ounder.hierarchy LIKE CONCAT(mo.hierarchy, \'%\')\nAND ounder.hierarchy like CONCAT(\'.\', \'%\')\nLEFT JOIN m_client mc on mc.office_id = ounder.id\nLEFT join m_group_client mgc on mgc.client_id = m c.id\nLeft join m_group mg on mg.id = mgc.group_id\nleft join ml_office_details as od on od.office_id = mo.id\nright join m_loan as ml on mc.id = ml.client_id\nleft join (\nselect mg.loan_id,mc.lastname,count(mg.id) as totalGuarantors\nfrom m_guarantor mg\nleft join m_client mc on mc.id = mg.entity_id\nright join m_guarantor_funding_details mgf on mgf.guarantor_id = mg.id\nwhere mg.entity_id is not null\ngroup by mg.id\n) as t on t.loan_id = ml.id\nwhere mc.status_enum = 300\nand email_address is not null\nand ml.id is not null\nand ml.`loan_status_id` = 300\nand (mo.id = ${officeId} or ${officeId} = -1)\nand (mc.staff_id = ${staffId} or ${staffId} = -1)\nand ifnull(ml.loan_counter,0) between ${cycleX} and ${cycleY}\ngroup by mc.id,ml.id )\nunion\n(\nselect mc.id, mc.firstname, ifnull(mc.middlename,\'\') as middlename, mc.lastname, mc.display_name as FullName, email_address as EmailAddress, round(ml.principal_amount, ml.currency_digits) as LoanAmount, round(ml.`total_outstanding_der ived`, ml.currency_digits) as LoanOutstanding, ml.`disbursedon_date` as LoanDisbursed,\nifnull(od.phoneNumber,\'\') as officenummber,ml.`account_no` as LoanAccountId,ifnull(GROUP_CONCAT(DISTINCT t.lastname SEPARATOR \', \') ,\'\') as guarantorLastname,sum(t.totalGuarantors) as numberOfGuarantors,mg.display_name as groupName\nFROM m_office mo\nJOIN m_office ounder ON ounder.hierarchy LIKE CONCAT(mo.hierarchy, \'%\')\nAND ounder.hierarchy like CONCAT(\'.\', \'%\')\nLEFT JOIN m_group mg on mg.office_id = ounder.id\nLEFT join m_group_client mgc on mgc.group_id = mg.id\nleft join m_client as mc on mgc.client_id = mc.id\nleft join ml_office_details as od on od.office_id = mo.id\nright join m_loan as ml on ml.group_id = mg.id and ml.group_id is not null\nleft join (\nselect mg.loan_id,mc.lastname,count(mg.id) as totalGuarantors\nfrom m_guarantor mg\nleft join m_client mc on mc.id = mg.entity_id\nright join m_guarantor_funding_details mgf on mgf.guarantor_id = mg.id\nwhere mg.entity_id is not null\ngroup by mg.id\n) as t on t.loan_id = ml.id\nwhere mc.status_enum = 300 and mg.status_enum = 300\nand email_address is not null\nand ml.id is not null\nand ml.`loan_status_id` = 300\nand (mo.id = ${officeId} or ${officeId} = -1)\nand (mg.staff_id = ${staffId} or ${staffId} = -1)\nand ml.group_id is not null\nand ifnull(ml.loan_counter,0) between ${cycleX} and ${cycleY}\ngroup by mc.id,ml.id\n)', 'All clients with an outstanding loan', '0', '0'); +INSERT INTO `stretchy_report` (`report_name`, `report_type`, `report_sql`, `description`, `core_report`, `use_report`) VALUES ('Loans in arrears - Email', 'Email', '(select mc.id, mc.firstname, ifnull(mc.middlename,\'\') as middlename, mc.lastname, mc.display_name as FullName, email_address as EmailAddress, round(ml.principal_amount, ml.currency_digits) as LoanAmount, round(ml.`total_outstanding_derived`, ml.currency_digits) as LoanOutstanding, ml.`disbursedon_date` as LoanDisbursed, mls.`duedate` as PaymentDueDate,ifnull(laa.total_overdue_derived,0) as TotalDue,\nifnull(od.phoneNumber,\'\') as officenummber,ml.`account_no` as LoanAccountId,ifnull(GROUP_CONCAT(DISTINCT t.lastname SEPARATOR \', \') ,\'\') as guarantorLastname,sum(t.totalGuarantors) as numberOfGuarantors,ifnull(mg.display_name,\'individual\') as groupName\nFROM m_office mo\nJOIN m_office ounder ON ounder.hierarchy LIKE CONCAT(mo.hierarchy, \'%\')\nAND ounder.hierarchy like CONCAT(\'.\', \'%\')\nLEFT JOIN m_client mc on mc.office_id = ounder.id\nLEFT join m_group_client mgc on mgc.client_id = mc.id\nLeft join m_group mg on mg.id = mgc.group_id\nleft join ml_office_details as od on od.office_id = mo.id\nright join m_loan as ml on mc.id = ml.client_id\nleft join (\nselect mg.loan_id,mc.lastname,count(mg.id) as totalGuarantors\nfrom m_guarantor mg\nleft join m_client mc on mc.id = mg.entity_id\nright join m_guarantor_funding_details mgf on mgf.guarantor_id = mg.id\nwhere mg.entity_id is not null\ngroup by mg.id\n) as t on t.loan_id = ml.id\nright join m_loan_arrears_aging as laa on laa.loan_id = ml.id\nright join m_loan_repayment_schedule as mls on ml.id = mls.loan_id and (mls.`duedate` BETWEEN DATE_SUB(CURDATE(), Interval ${toY} Day) and DATE_SUB(CURDATE(), Interval ${fromX} Day)) and mls.`completed_derived` = 0\nand mls.installment = (SELECT MIN(installment) from m_loan_repayment_schedule where loan_id = ml.id and duedate <= CURDATE() and completed_derived=0)\nwhere mc.status_enum = 300 and emai l_address is not null and ml.`loan_status_id` = 300\nand (mo.id = ${officeId} or ${officeId} = -1)\nand (mc.staff_id = ${staffId} or ${staffId} = -1)\ngroup by ml.id )\nunion\n(\nselect mc.id, mc.firstname, ifnull(mc.middlename,\'\') as middlename, mc.lastname, mc.display_name as FullName, email_address as EmailAddress, round(ml.principal_amount, ml.currency_digits) as LoanAmount, round(ml.`total_outstanding_derived`, ml.currency_digits) as LoanOutstanding, ml.`disbursedon_date` as LoanDisbursed, mls.`duedate` as PaymentDueDate,ifnull(laa.total_overdue_derived,0) as TotalDue,\nifnull(od.phoneNumber,\'\') as officenummber,ml.`account_no` as LoanAccountId,ifnull(GROUP_CONCAT(DISTINCT t.lastname SEPARATOR \', \') ,\'\') as guarantorLastname,sum(t.totalGuarantors) as numberOfGuarantors,mg.display_name as groupName\nFROM m_office mo\nJOIN m_office ounder ON ounder.hierarchy LIKE CONCAT(mo.hierarchy, \'%\')\nAND ounder.hierarchy like CONCAT(\'.\', \'%\')\nLEFT JOIN m_group mg on mg.offi ce_id = ounder.id\nLEFT join m_group_client mgc on mgc.group_id = mg.id\nleft join m_client as mc on mgc.client_id = mc.id\nleft join ml_office_details as od on od.office_id = mo.id\nright join m_loan as ml on ml.group_id = mg.id and ml.group_id is not null\nleft join (\nselect mg.loan_id,mc.lastname,count(mg.id) as totalGuarantors\nfrom m_guarantor mg\nleft join m_client mc on mc.id = mg.entity_id\nright join m_guarantor_funding_details mgf on mgf.guarantor_id = mg.id\nwhere mg.entity_id is not null\ngroup by mg.id\n) as t on t.loan_id = ml.id\nright join m_loan_arrears_aging as laa on laa.loan_id = ml.id\nright join m_loan_repayment_schedule as mls on ml.id = mls.loan_id and (mls.`duedate` BETWEEN DATE_SUB(CURDATE(), Interval ${toY} Day) and DATE_SUB(CURDATE(), Interval ${fromX} Day)) and mls.`completed_derived` = 0\nand mls.installment = (SELECT MIN(installment) from m_loan_repayment_schedule where loan_id = ml.id and duedate <= CURDATE() and completed_derived=0)\nwhere mc.status _enum = 300 and email_address is not null and ml.`loan_status_id` = 300 and mg.status_enum = 300 and ml.group_id is not null\nand (mo.id = ${officeId} or ${officeId} = -1)\nand (mc.staff_id = ${staffId} or ${staffId} = -1)\ngroup by mc.id,ml.id)', 'All clients with an outstanding loan in arrears between X and Y days', '0', '0'); +INSERT INTO `stretchy_report` (`report_name`, `report_type`, `report_sql`, `description`, `core_report`, `use_report`) VALUES ('Loans disbursed to clients - Email', 'Email', '(select mc.id, mc.firstname, ifnull(middlename,\'\') as middlename, mc.lastname, mc.display_name as FullName, email_address as EmailAddress, round(ml.principal_amount, ml.currency_digits) as LoanAmount, round(ml.`total_outstanding_derived`, ml.currency_digits) as LoanOutstanding, ml.`disbursedon_date` as LoanDisbursed,\nifnull(od.phoneNumber,\'\') as officenummber,ml.`account_no` as LoanAccountId,ifnull(GROUP_CONCAT(DISTINCT t.lastname SEPARATOR \', \') ,\'\') as guarantorLastname,sum(t.totalGuarantors) as numberOfGuarantors,mls.duedate,(ifnull(mls.principal_amount,0) + ifnull(mls.interest_amount,0) + ifnull(mls.fee_charges_amount,0) + ifnull(mls.penalty_charges_amount,0)) - (ifnull(mls.principal_completed_derived,0) + ifnull(mls.interest_completed_derived,0) + ifnull(mls.fee_charges_completed_derived,0) + ifn ull(mls.penalty_charges_completed_derived,0)) as TotalDue,ifnull(mg.display_name,\'individual\') as groupName\nFROM m_office mo\nJOIN m_office ounder ON ounder.hierarchy LIKE CONCAT(mo.hierarchy, \'%\')\nAND ounder.hierarchy like CONCAT(\'.\', \'%\')\nLEFT JOIN m_client mc on mc.office_id = ounder.id\nLEFT join m_group_client mgc on mgc.client_id = mc.id\nLeft join m_group mg on mg.id = mgc.group_id\nleft join ml_office_details as od on od.office_id = mo.id\nright join m_loan as ml on mc.id = ml.client_id and (ml.`disbursedon_date` BETWEEN DATE_SUB(CURDATE(), Interval ${toY} Day) and DATE_SUB(CURDATE(), Interval ${fromX} Day))\nright join m_loan_repayment_schedule as mls on ml.id = mls.loan_id\nand mls.installment = (SELECT MIN(installment) from m_loan_repayment_schedule where loan_id = ml.id and duedate >= CURDATE() and obligations_met_on_date is null)\nleft join (\nselect mg.loan_id,mc.lastname,count(mg.id) as totalGuarantors\nfrom m_guarantor mg\nleft join m_client mc on mc.id = mg.entity_id\nright join m_guarantor_funding_details mgf on mgf.guarantor_id = mg.id\nwhere mg.entity_id is not null\ngroup by mg.id\n) as t on t.loan_id = ml.id\nwhere mc.status_enum = 300 and email_address is not null and ml.`loan_status_id` = 300\nand (mo.id = ${officeId} or ${officeId} = -1)\nand (mc.staff_id = ${staffId} or ${staffId} = -1)\nand IFNULL(ml.loan_counter,0) between ${cycleX} and ${cycleY}\ngroup by ml.id )\nunion\n(select mc.id, mc.firstname, ifnull(middlename,\'\') as middlename, mc.lastname, mc.display_name as FullName, email_address as EmailAddress, round(ml.principal_amount, ml.currency_digits) as LoanAmount, round(ml.`total_outstanding_derived`, ml.currency_digits) as LoanOutstanding, ml.`disbursedon_date` as LoanDisbursed,\nifnull(od.phoneNumber,\'\') as officenummber,ml.`account_no` as LoanAccountId,ifnull(GROUP_CONCAT(DISTINCT t.lastname SEPARATOR \', \') ,\'\') as guarantorLastname,sum(t.totalGuarantors) as numberOfGuarantors,mls.duedate,(ifnull(mls.prin cipal_amount,0) + ifnull(mls.interest_amount,0) + ifnull(mls.fee_charges_amount,0) + ifnull(mls.penalty_charges_amount,0)) - (ifnull(mls.principal_completed_derived,0) + ifnull(mls.interest_completed_derived,0) + ifnull(mls.fee_charges_completed_derived,0) + ifnull(mls.penalty_charges_completed_derived,0)) as TotalDue,mg.display_name as groupName\nFROM m_office mo\nJOIN m_office ounder ON ounder.hierarchy LIKE CONCAT(mo.hierarchy, \'%\')\nAND ounder.hierarchy like CONCAT(\'.\', \'%\')\nLEFT JOIN m_group mg on mg.office_id = ounder.id\nLEFT join m_group_client mgc on mgc.group_id = mg.id\nleft join m_client as mc on mgc.client_id = mc.id\n\nleft join ml_office_details as od on od.office_id = mo.id\nright join m_loan as ml on ml.group_id = mg.id and (ml.`disbursedon_date` BETWEEN DATE_SUB(CURDATE(), Interval ${toY} Day) and DATE_SUB(CURDATE(), Interval ${fromX} Day))\nright join m_loan_repayment_schedule as mls on ml.id = mls.loan_id\nand mls.installment = (SELECT MIN(installment) fr om m_loan_repayment_schedule where loan_id = ml.id and duedate >= CURDATE() and obligations_met_on_date is null)\nleft join (\nselect mg.loan_id,mc.lastname,count(mg.id) as totalGuarantors\nfrom m_guarantor mg\nleft join m_client mc on mc.id = mg.entity_id\nright join m_guarantor_funding_details mgf on mgf.guarantor_id = mg.id\nwhere mg.entity_id is not null\ngroup by mg.id\n) as t on t.loan_id = ml.id\nwhere mc.status_enum = 300 and email_address is not null and ml.`loan_status_id` = 300 and mg.status_enum = 300\nand (mo.id = ${officeId} or ${officeId} = -1) and ml.group_id is not null\nand (mc.staff_id = ${staffId} or ${staffId} = -1)\nand IFNULL(ml.loan_counter,0) between ${cycleX} and ${cycleY}\ngroup by ml.id,mc.id)', 'All clients who have had a loan disbursed to them in the last X to Y days', '0', '0'); +INSERT INTO `stretchy_report` (`report_name`, `report_type`, `report_sql`, `description`, `core_report`, `use_report`) VALUES ('Loan payments due - Email', 'Email', '(select mc.id, mc.firstname, ifnull(middlename,\'\') as middlename, mc.lastname, mc.display_name as FullName, email_address as EmailAddress, round(ml.principal_amount, ml.currency_digits) as LoanAmount,ifnull(od.phoneNumber,\'\') as officenummber,\nround(ml.`total_outstanding_derived`, ml.currency_digits) as LoanOutstanding, ml.`disbursedon_date` as LoanDisbursed, mls.`duedate` as PaymentDueDate,\n(ifnull(mls.principal_amount,0) + ifnull(mls.interest_amount,0) + ifnull(mls.fee_charges_amount,0) + ifnull(mls.penalty_charges_amount,0)) - (ifnull(mls.principal_completed_derived,0) + ifnull(mls.interest_completed_derived,0) + ifnull(mls.fee_charges_completed_derived,0) + ifnull(mls.penalty_charges_completed_derived,0)) + ifnull(laa.total_overdue_derived,0) as TotalDue,\nifnull(laa.total_overdue_derived,0) as TotalOverdue,ml .`account_no` as LoanAccountId,ifnull(GROUP_CONCAT(DISTINCT t.lastname SEPARATOR \', \') ,\'\') as guarantorLastname,sum(t.totalGuarantors) as numberOfGuarantors,ifnull(mg.display_name,\'individual\') as groupName\nFROM m_office mo\nJOIN m_office ounder ON ounder.hierarchy LIKE CONCAT(mo.hierarchy, \'%\')\nAND ounder.hierarchy like CONCAT(\'.\', \'%\')\nLEFT JOIN m_client mc on mc.office_id = ounder.id\nLEFT join m_group_client mgc on mgc.client_id = mc.id\nLeft join m_group mg on mg.id = mgc.group_id\nleft join ml_office_details as od on od.office_id = mo.id\nright join m_loan as ml on mc.id = ml.client_id\nleft join (\nselect mg.loan_id,mc.lastname,count(mg.id) as totalGuarantors\nfrom m_guarantor mg\nleft join m_client mc on mc.id = mg.entity_id\nright join m_guarantor_funding_details mgf on mgf.guarantor_id = mg.id\nwhere mg.entity_id is not null\ngroup by mg.id\n) as t on t.loan_id = ml.id\nleft join m_loan_arrears_aging as laa on laa.loan_id = ml.id\nright join m_loan_repayme nt_schedule as mls on ml.id = mls.loan_id and (mls.`duedate` BETWEEN DATE_ADD(CURDATE(), Interval ${fromX} Day) and DATE_ADD(CURDATE(), Interval ${toY} Day)) and mls.`completed_derived` = 0\nwhere mc.status_enum = 300 and email_address is not null and ml.`loan_status_id` = 300\nand (mo.id = ${officeId} or ${officeId} = -1)\nand (mc.staff_id = ${staffId} or ${staffId} = -1) group by ml.id )\nunion\n(\nselect mc.id, mc.firstname, ifnull(middlename,\'\') as middlename, mc.lastname, mc.display_name as FullName, email_address as EmailAddress, round(ml.principal_amount, ml.currency_digits) as LoanAmount,ifnull(od.phoneNumber,\'\') as officenummber,\nround(ml.`total_outstanding_derived`, ml.currency_digits) as LoanOutstanding, ml.`disbursedon_date` as LoanDisbursed, mls.`duedate` as PaymentDueDate,\n(ifnull(mls.principal_amount,0) + ifnull(mls.interest_amount,0) + ifnull(mls.fee_charges_amount,0) + ifnull(mls.penalty_charges_amount,0)) - (ifnull(mls.principal_completed_derived,0) + ifnull( mls.interest_completed_derived,0) + ifnull(mls.fee_charges_completed_derived,0) + ifnull(mls.penalty_charges_completed_derived,0)) + ifnull(laa.total_overdue_derived,0) as TotalDue,\nifnull(laa.total_overdue_derived,0) as TotalOverdue,ml.`account_no` as LoanAccountId,ifnull(GROUP_CONCAT(DISTINCT t.lastname SEPARATOR \', \') ,\'\') as guarantorLastname,sum(t.totalGuarantors) as numberOfGuarantors,mg.display_name as groupName\nFROM m_office mo\nJOIN m_office ounder ON ounder.hierarchy LIKE CONCAT(mo.hierarchy, \'%\')\nAND ounder.hierarchy like CONCAT(\'.\', \'%\')\nLEFT JOIN m_group mg on mg.office_id = ounder.id\nLEFT join m_group_client mgc on mgc.group_id = mg.id\nleft join m_client as mc on mgc.client_id = mc.id\nleft join ml_office_details as od on od.office_id = mo.id\nright join m_loan as ml on ml.group_id = mg.id and ml.group_id is not null\nleft join (\nselect mg.loan_id,mc.lastname,count(mg.id) as totalGuarantors\nfrom m_guarantor mg\nleft join m_client mc on mc.id = mg.ent ity_id\nright join m_guarantor_funding_details mgf on mgf.guarantor_id = mg.id\nwhere mg.entity_id is not null\ngroup by mg.id\n) as t on t.loan_id = ml.id\nleft join m_loan_arrears_aging as laa on laa.loan_id = ml.id\nright join m_loan_repayment_schedule as mls on ml.id = mls.loan_id and (mls.`duedate` BETWEEN DATE_ADD(CURDATE(), Interval ${fromX} Day) and DATE_ADD(CURDATE(), Interval ${toY} Day)) and mls.`completed_derived` = 0\nwhere mc.status_enum = 300 and email_address is not null and ml.`loan_status_id` = 300 and mg.status_enum = 300 and ml.group_id is not null\nand (mo.id = ${officeId} or ${officeId} = -1)\nand (mc.staff_id = ${staffId} or ${staffId} = -1) group by ml.id,mc.id)', 'All clients with an unpaid installment due on their loan between X and Y days', '0', '0'); +INSERT INTO `stretchy_report` (`report_name`, `report_type`, `report_sql`, `description`, `core_report`, `use_report`) VALUES ('Dormant Prospects - Email', 'Email', 'select mo.name as OfficeName, mc.firstname, ifnull(mc.middlename,\"\") as middlename, mc.lastname, mc.display_name as FullName, mc.email_address as EmailAddress,ifnull(od.phoneNumber,\"\") as officenummber\nFROM\nm_office mo\nJOIN m_office ounder ON ounder.hierarchy LIKE CONCAT(mo.hierarchy, \"%\")\nAND ounder.hierarchy like CONCAT(\".\", \"%\")\nLEFT JOIN m_client mc on mc.office_id = ounder.id\nleft join ml_office_details as od on od.office_id = mo.id\nwhere\n(\nSELECT count(ml.id) as loansCount\nFROM m_loan ml\nWHERE ml.client_id = mc.id\nand (ml.writtenoffon_date>=CURDATE()\nOR ml.writtenoffon_date IS NULL )\nAND disbursedon_date<=CURDATE()\n) = 0\nAND IFNULL(DATEDIFF(CURDATE(), mc.`activation_date`),0) >90\nAND (mc.closedon_date >CURDATE() OR mc.closedon_date IS NULL)\nAND mc.activation_date<CURDATE()\nand (mo.id = ${officeId} or ${officeId} = -1)\nand (mc.staff_id = ${staffId} or ${staffId} = -1)', 'All individuals who have not yet received a loan but were also entered into the system more than 3 months', '0', '0'); +INSERT INTO `stretchy_report` (`report_name`, `report_type`, `report_sql`, `description`, `core_report`, `use_report`) VALUES ('Active Group Leaders - Email', 'Email', 'SELECT mc.id, mc.firstname, ifnull(mc.middlename,\'\') as middlename, mc.lastname, mc.display_name as FullName, mc.email_address as EmailAddress,\nmo.name as officename, ifnull(od.phoneNumber,\'\') as officenummber\nFROM\nm_office mo\nJOIN m_office ounder ON ounder.hierarchy LIKE CONCAT(mo.hierarchy, \'%\')\nAND ounder.hierarchy like CONCAT(\'.\', \'%\')\nLEFT JOIN m_client mc on mc.office_id = ounder.id\nleft join ml_office_details as od on od.office_id = mo.id\nright join m_group_roles mgr on mgr.client_id = mc.id\nright join m_code_value mcv on mcv.id = mgr.role_cv_id and mcv.code_value = \"Chairman\"\nWHERE mc.status_enum = 300 and mc.email_address is not null\nand (mo.id = ${officeId} or ${officeId} = -1)\nand (mc.staff_id = ${staffId} or ${staffId} = -1) group by mc.id', 'All active group chairmen ', '0', '0'); +INSERT INTO `stretchy_report` (`report_name`, `report_type`, `report_sql`, `description`, `core_report`, `use_report`) VALUES ('Loan Payments Due (Overdue Loans) - Email', 'Email', 'select mc.id, mc.firstname, ifnull(middlename,\'\') as middlename, mc.lastname, mc.display_name as FullName, email_address as EmailAddress, round(ml.principal_amount, ml.currency_digits) as LoanAmount,ifnull(od.phoneNumber,\'\') as officenummber,\nround(ml.`total_outstanding_derived`, ml.currency_digits) as LoanOutstanding, ml.`disbursedon_date` as LoanDisbursed, mls.`duedate` as PaymentDueDate,\nround((ifnull(mls.principal_amount,0) + ifnull(mls.interest_amount,0) + ifnull(mls.fee_charges_amount,0) + ifnull(mls.penalty_charges_amount,0)) - (ifnull(mls.principal_completed_derived,0) + ifnull(mls.interest_completed_derived,0) + ifnull(mls.fee_charges_completed_derived,0) + ifnull(mls.penalty_charges_completed_derived,0)) + ifnull(laa.total_overdue_derived,0), ml.currency_digits) as TotalDue,\nround(ifnull (laa.total_overdue_derived,0), ml.currency_digits) as TotalOverdue,ml.`account_no` as LoanAccountId,ifnull(GROUP_CONCAT(DISTINCT t.lastname SEPARATOR \', \') ,\'\') as guarantorLastname,sum(t.totalGuarantors) as numberOfGuarantors\nFROM m_office mo\nJOIN m_office ounder ON ounder.hierarchy LIKE CONCAT(mo.hierarchy, \'%\')\nAND ounder.hierarchy like CONCAT(\'.\', \'%\')\nLEFT JOIN m_client mc on mc.office_id = ounder.id\nleft join ml_office_details as od on od.office_id = mo.id\nright join m_loan as ml on mc.id = ml.client_id\nleft join (\nselect mg.loan_id,mc.lastname,count(mg.id) as totalGuarantors\nfrom m_guarantor mg\nleft join m_client mc on mc.id = mg.entity_id\nright join m_guarantor_funding_details mgf on mgf.guarantor_id = mg.id\nwhere mg.entity_id is not null\ngroup by mg.id\n) as t on t.loan_id = ml.id\nleft join m_loan_arrears_aging as laa on laa.loan_id = ml.id\nright join m_loan_repayment_schedule as mls on ml.id = mls.loan_id and (mls.`duedate` BETWEEN DATE_ADD(CURDAT E(), Interval ${fromX} Day) and DATE_ADD(CURDATE(), Interval ${toY} Day)) and mls.`completed_derived` = 0\nright join m_loan_repayment_schedule as mls1 on ml.id = mls1.loan_id and (mls1.`duedate` BETWEEN DATE_SUB(CURDATE(), Interval ${overdueY} Day) and DATE_SUB(CURDATE(), Interval ${overdueX} Day)) and mls1.`completed_derived` = 0\nand mls1.installment = (SELECT MIN(installment) from m_loan_repayment_schedule where loan_id = ml.id and duedate <= CURDATE() and completed_derived=0)\nwhere status_enum = 300 and email_address is not null and ml.`loan_status_id` = 300\nand ml.id in (select mla.loan_id from m_loan_arrears_aging mla)\nand (mo.id = ${officeId} or ${officeId} = -1)\nand (mc.staff_id = ${staffId} or ${staffId} = -1) group by ml.id', 'Loan Payments Due between X to Y days for clients in arrears between X and Y days', '0', '0'); +INSERT INTO `stretchy_report` (`report_name`, `report_type`, `report_sql`, `description`, `core_report`, `use_report`) VALUES ('Loan Payments Received (Active Loans) - Email', 'Email', 'select mc.id, mc.firstname, ifnull(mc.middlename,\'\') as middlename, mc.lastname, mc.display_name as FullName, email_address as EmailAddress, round(ml.principal_amount, ml.currency_digits) as LoanAmount, round(ml.`total_outstanding_derived`, ml.currency_digits) as LoanOutstanding,\nifnull(od.phoneNumber,\'\') as officenummber,ml.`account_no` as LoanAccountId, round(mlt.amountPaid, ml.currency_digits) as repaymentAmount\nFROM m_office mo\nJOIN m_office ounder ON ounder.hierarchy LIKE CONCAT(mo.hierarchy, \'%\')\nAND ounder.hierarchy like CONCAT(\'.\', \'%\')\nLEFT JOIN m_client mc on mc.office_id = ounder.id\nleft join ml_office_details as od on od.office_id = mo.id\nright join m_loan as ml on mc.id = ml.client_id\nright join(\nselect sum(mlt.amount) as amountPaid,mlt.id,mlt.loan_id\nfrom m_loan_tran saction mlt\nwhere mlt.is_reversed = 0 and mlt.transaction_date BETWEEN DATE_SUB(CURDATE(), Interval ${toY} Day) and DATE_SUB(CURDATE(), Interval ${fromX} Day) and mlt.transaction_type_enum = 2\ngroup by mlt.loan_id\n) as mlt on mlt.loan_id = ml.id\nwhere mc.status_enum = 300 and email_address is not null and ml.`loan_status_id` = 300\nand (mo.id = ${officeId} or ${officeId} = -1)\nand (mc.staff_id = ${staffId} or ${staffId} = -1)\nand ml.id not in (select mla.loan_id from m_loan_arrears_aging mla)\ngroup by ml.id', 'Payments received in the last X to Y days for any loan with the status Active (on-time)', '0', '0'); +INSERT INTO `stretchy_report` (`report_name`, `report_type`, `report_sql`, `description`, `core_report`, `use_report`) VALUES ('Loan Payments Received (Overdue Loans) - Email', 'Email', 'select ml.id as loanId,mc.id, mc.firstname, ifnull(mc.middlename,\'\') as middlename, mc.lastname, mc.display_name as FullName, email_address as EmailAddress, round(ml.principal_amount, ml.currency_digits) as LoanAmount, round(ml.`total_outstanding_derived`, ml.currency_digits) as LoanOutstanding,\nifnull(od.phoneNumber,\'\') as officenummber,ml.`account_no` as LoanAccountId, round(mlt.amountPaid, ml.currency_digits) as repaymentAmount\nFROM m_office mo\nJOIN m_office ounder ON ounder.hierarchy LIKE CONCAT(mo.hierarchy, \'%\')\nAND ounder.hierarchy like CONCAT(\'.\', \'%\')\nLEFT JOIN m_client mc on mc.office_id = ounder.id\nleft join ml_office_details as od on od.office_id = mo.id\nright join m_loan as ml on mc.id = ml.client_id\nright join(\nselect sum(mlt.amount) as amountPaid,mlt.id,mlt.loan_id \nfrom m_loan_transaction mlt\nwhere mlt.is_reversed = 0 and mlt.transaction_date BETWEEN DATE_SUB(CURDATE(), Interval ${toY} Day) and DATE_SUB(CURDATE(), Interval ${fromX} Day) and mlt.transaction_type_enum = 2\ngroup by mlt.loan_id\n) as mlt on mlt.loan_id = ml.id\nright join m_loan_repayment_schedule as mls1 on ml.id = mls1.loan_id and (mls1.`duedate` BETWEEN DATE_SUB(CURDATE(), Interval ${overdueY} Day) and DATE_SUB(CURDATE(), Interval ${overdueX} Day)) and mls1.`completed_derived` = 0\nand mls1.installment = (SELECT MIN(installment) from m_loan_repayment_schedule where loan_id = ml.id and duedate <= CURDATE() and completed_derived=0)\nwhere mc.status_enum = 300 and email_address is not null and ml.`loan_status_id` = 300\nand (mo.id = ${officeId} or ${officeId} = -1)\nand (mc.staff_id = ${staffId} or ${staffId} = -1)\nand ml.id in (select mla.loan_id from m_loan_arrears_aging mla)\ngroup by ml.id', 'Payments received in the last X to Y days for any loan with the status Overdue ( arrears) between X and Y days', '0', '0'); +INSERT INTO `stretchy_report` (`report_name`, `report_type`, `report_sql`, `description`, `core_report`, `use_report`) VALUES ('Loan Fully Repaid - Email', 'Email', '(select mc.id, mc.firstname, ifnull(middlename,\'\') as middlename, mc.lastname, mc.display_name as FullName, email_address as EmailAddress, ml.principal_amount as LoanAmount, ml.`total_outstanding_derived` as LoanOutstanding, ml.`disbursedon_date` as LoanDisbursed,\nifnull(od.phoneNumber,\'\') as officenummber,ml.`account_no` as LoanAccountId,ifnull(GROUP_CONCAT(DISTINCT t.lastname SEPARATOR \", \") ,\'\') as guarantorLastname,sum(t.totalGuarantors) as numberOfGuarantors,mls.duedate,(ifnull(mls.principal_amount,0) + ifnull(mls.interest_amount,0) + ifnull(mls.fee_charges_amount,0) + ifnull(mls.penalty_charges_amount,0)) - (ifnull(mls.principal_completed_derived,0) + ifnull(mls.interest_completed_derived,0) + ifnull(mls.fee_charges_completed_derived,0) + ifnull(mls.penalty_charges_completed_derived,0)) as TotalDue,ifnul l(mg.display_name,\'individual\') as groupName,ml.total_repayment_derived as totalFullyPaid\nFROM m_office mo\nJOIN m_office ounder ON ounder.hierarchy LIKE CONCAT(mo.hierarchy, \'%\')\nAND ounder.hierarchy like CONCAT(\'.\', \'%\')\nLEFT JOIN m_client mc on mc.office_id = ounder.id\nLEFT join m_group_client mgc on mgc.client_id = mc.id\nLeft join m_group mg on mg.id = mgc.group_id\nleft join ml_office_details as od on od.office_id = mo.id\nright join m_loan as ml on mc.id = ml.client_id and (ml.`loan_status_id` = 600 or ml.`loan_status_id` = 700 )\nright join m_loan_repayment_schedule as mls on ml.id = mls.loan_id\nand mls.installment = (SELECT Max(installment) from m_loan_repayment_schedule where loan_id = ml.id and obligations_met_on_date BETWEEN DATE_SUB(CURDATE(), Interval ${toY} Day) and DATE_SUB(CURDATE(), Interval ${fromX} Day) and completed_derived is true and obligations_met_on_date is not null)\nleft join (\nselect mg.loan_id,mc.lastname,count(mg.id) as totalGuarantors\nf rom m_guarantor mg\nleft join m_client mc on mc.id = mg.entity_id\nright join m_guarantor_funding_details mgf on mgf.guarantor_id = mg.id\nwhere mg.entity_id is not null\ngroup by mg.id\n) as t on t.loan_id = ml.id\nwhere mc.status_enum = 300 and email_address is not null\nand (mo.id = ${officeId} or ${officeId} = -1)\nand (mc.staff_id = ${staffId} or ${staffId} = -1)\ngroup by ml.id )\nunion\n(select mc.id, mc.firstname, ifnull(middlename,\'\') as middlename, mc.lastname, mc.display_name as FullName, email_address as EmailAddress, ml.principal_amount as LoanAmount, ml.`total_outstanding_derived` as LoanOutstanding, ml.`disbursedon_date` as LoanDisbursed,\nifnull(od.phoneNumber,\'\') as officenummber,ml.`account_no` as LoanAccountId,ifnull(GROUP_CONCAT(DISTINCT t.lastname SEPARATOR \", \") ,\'\') as guarantorLastname,sum(t.totalGuarantors) as numberOfGuarantors,mls.duedate,(ifnull(mls.principal_amount,0) + ifnull(mls.interest_amount,0) + ifnull(mls.fee_charges_amount,0) + ifnull(ml s.penalty_charges_amount,0)) - (ifnull(mls.principal_completed_derived,0) + ifnull(mls.interest_completed_derived,0) + ifnull(mls.fee_charges_completed_derived,0) + ifnull(mls.penalty_charges_completed_derived,0)) as TotalDue,mg.display_name as groupName,ml.total_repayment_derived as totalFullyPaid\nFROM m_office mo\nJOIN m_office ounder ON ounder.hierarchy LIKE CONCAT(mo.hierarchy, \'%\')\nAND ounder.hierarchy like CONCAT(\'.\', \'%\')\nLEFT JOIN m_group mg on mg.office_id = ounder.id\nLEFT join m_group_client mgc on mgc.group_id = mg.id\nleft join m_client as mc on mgc.client_id = mc.id\nleft join ml_office_details as od on od.office_id = mo.id\nright join m_loan as ml on ml.group_id = mg.id and (ml.`loan_status_id` = 600 or ml.`loan_status_id` = 700 )\nright join m_loan_repayment_schedule as mls on ml.id = mls.loan_id\nand mls.installment = (SELECT Max(installment) from m_loan_repayment_schedule where loan_id = ml.id and obligations_met_on_date BETWEEN DATE_SUB(CURDATE(), Interva l ${toY} Day) and DATE_SUB(CURDATE(), Interval ${fromX} Day) and completed_derived is true and obligations_met_on_date is not null)\nleft join (\nselect mg.loan_id,mc.lastname,count(mg.id) as totalGuarantors\nfrom m_guarantor mg\nleft join m_client mc on mc.id = mg.entity_id\nright join m_guarantor_funding_details mgf on mgf.guarantor_id = mg.id\nwhere mg.entity_id is not null\ngroup by mg.id\n) as t on t.loan_id = ml.id\nwhere mc.status_enum = 300 and email_address is not null\nand (mo.id = ${officeId} or ${officeId} = -1) and ml.group_id is not null\nand (mc.staff_id = ${staffId} or ${staffId} = -1)\ngroup by ml.id,mc.id)\n', 'All loans that have been fully repaid (Closed or Overpaid) in the last X to Y days', '0', '0'); +INSERT INTO `stretchy_report` (`report_name`, `report_type`, `report_sql`, `description`, `core_report`, `use_report`) VALUES ('Loans Outstanding after final instalment date - Email', 'Email', '(select mc.id, mc.firstname, ifnull(mc.middlename,\'\') as middlename, mc.lastname, mc.display_name as FullName, email_address as EmailAddress, ml.principal_amount as LoanAmount, ml.`total_outstanding_derived` as LoanOutstanding, ml.`disbursedon_date` as LoanDisbursed, mls.`duedate` as PaymentDueDate,ifnull(laa.total_overdue_derived,0) as TotalDue,\nifnull(od.phoneNumber,\'\') as officenummber,ml.`account_no` as LoanAccountId,ifnull(GROUP_CONCAT(DISTINCT t.lastname SEPARATOR \", \") ,\'\') as guarantorLastname,sum(t.totalGuarantors) as numberOfGuarantors,ifnull(mg.display_name,\'individual\') as groupName\nFROM m_office mo\nJOIN m_office ounder ON ounder.hierarchy LIKE CONCAT(mo.hierarchy, \'%\')\nAND ounder.hierarchy like CONCAT(\'.\', \'%\')\nLEFT JOIN m_client mc on mc.office_id = ounder .id\nLEFT join m_group_client mgc on mgc.client_id = mc.id\nLeft join m_group mg on mg.id = mgc.group_id\nleft join ml_office_details as od on od.office_id = mo.id\nright join m_loan as ml on mc.id = ml.client_id and ml.`loan_status_id` = 300 and curdate() > ml.expected_maturedon_date and (ml.expected_maturedon_date BETWEEN DATE_SUB(CURDATE(), Interval ${toY} Day) and DATE_SUB(CURDATE(), Interval ${fromX} Day))\nleft join (\nselect mg.loan_id,mc.lastname,count(mg.id) as totalGuarantors\nfrom m_guarantor mg\nleft join m_client mc on mc.id = mg.entity_id\nright join m_guarantor_funding_details mgf on mgf.guarantor_id = mg.id\nwhere mg.entity_id is not null\ngroup by mg.id\n) as t on t.loan_id = ml.id\nright join m_loan_arrears_aging as laa on laa.loan_id = ml.id\nright join m_loan_repayment_schedule as mls on ml.id = mls.loan_id and (mls.`duedate` BETWEEN DATE_SUB(CURDATE(), Interval ${toY} Day) and DATE_SUB(CURDATE(), Interval ${fromX} Day)) and mls.`completed_derived` = 0\nwhere mc. status_enum = 300 and email_address is not null\nand (mo.id = ${officeId} or ${officeId} = -1)\nand (mc.staff_id = ${staffId} or ${staffId} = -1)\ngroup by ml.id )\nunion\n(\nselect mc.id, mc.firstname, ifnull(mc.middlename,\'\') as middlename, mc.lastname, mc.display_name as FullName, email_address as EmailAddress, ml.principal_amount as LoanAmount, ml.`total_outstanding_derived` as LoanOutstanding, ml.`disbursedon_date` as LoanDisbursed, mls.`duedate` as PaymentDueDate,ifnull(laa.total_overdue_derived,0) as TotalDue,\nifnull(od.phoneNumber,\'\') as officenummber,ml.`account_no` as LoanAccountId,ifnull(GROUP_CONCAT(DISTINCT t.lastname SEPARATOR \", \") ,\'\') as guarantorLastname,sum(t.totalGuarantors) as numberOfGuarantors,mg.display_name as groupName\nFROM m_office mo\nJOIN m_office ounder ON ounder.hierarchy LIKE CONCAT(mo.hierarchy, \'%\')\nAND ounder.hierarchy like CONCAT(\'.\', \'%\')\nLEFT JOIN m_group mg on mg.office_id = ounder.id\nLEFT join m_group_client mgc on mgc.gro up_id = mg.id\nleft join m_client as mc on mgc.client_id = mc.id\nleft join ml_office_details as od on od.office_id = mo.id\nright join m_loan as ml on ml.group_id = mg.id and ml.group_id is not null and curdate() > ml.expected_maturedon_date and (ml.expected_maturedon_date BETWEEN DATE_SUB(CURDATE(), Interval ${toY} Day) and DATE_SUB(CURDATE(), Interval ${fromX} Day))\nleft join (\nselect mg.loan_id,mc.lastname,count(mg.id) as totalGuarantors\nfrom m_guarantor mg\nleft join m_client mc on mc.id = mg.entity_id\nright join m_guarantor_funding_details mgf on mgf.guarantor_id = mg.id\nwhere mg.entity_id is not null\ngroup by mg.id\n) as t on t.loan_id = ml.id\nright join m_loan_arrears_aging as laa on laa.loan_id = ml.id\nright join m_loan_repayment_schedule as mls on ml.id = mls.loan_id and (mls.`duedate` BETWEEN DATE_SUB(CURDATE(), Interval ${toY} Day) and DATE_SUB(CURDATE(), Interval ${fromX} Day)) and mls.`completed_derived` = 0\nwhere mc.status_enum = 300 and email_address is not null and ml.`loan_status_id` = 300 and mg.status_enum = 300 and ml.group_id is not null\nand (mo.id = ${officeId} or ${officeId} = -1)\nand (mc.staff_id = ${staffId} or ${staffId} = -1)\ngroup by mc.id,ml.id)', 'All active loans (with an outstanding balance) between X to Y days after the final instalment date on their loan schedule', '0', '0'); +INSERT INTO `stretchy_report` (`report_name`, `report_type`, `report_sql`, `description`, `core_report`, `use_report`) VALUES ('Happy Birthday - Email', 'Email', 'SELECT mc.id, mc.firstname, ifnull(mc.middlename,\'\') as middlename, mc.lastname, mc.display_name as FullName, mc.email_address as EmailAddress,\nmo.name as officename, ifnull(od.phoneNumber,\'\') as officenummber, mc.date_of_birth as dateOfBirth,TIMESTAMPDIFF(YEAR,mc.date_of_birth,CURDATE()) AS age\nFROM\nm_office mo\nJOIN m_office ounder ON ounder.hierarchy LIKE CONCAT(mo.hierarchy, \'%\')\nAND ounder.hierarchy like CONCAT(\'.\', \'%\')\nLEFT JOIN m_client mc on mc.office_id = ounder.id\nleft join ml_office_details as od on od.office_id = mo.id\nWHERE mc.status_enum = 300 and mc.email_address is not null\nand (mo.id = 1 or 1 = -1)\nand (mc.staff_id = -1 or -1 = -1)\n AND (\n MONTH(mc.date_of_birth) = MONTH(NOW())\n AND DAY(mc.date_of_birth) = DAY(NOW())\n ) OR (\n MONTH(mc.date_of_birth) = 2 AND DAY(mc.date_of_birth) = 29\n AND MONTH(NOW()) = 3 AND DAY(NOW()) = 1\n AND (YEAR(NOW()) % 4 = 0)\n AND ((YEAR(NOW()) % 100 != 0) OR (YEAR(NOW()) % 400 = 0))\n )\n group by mc.id', 'This sends a message to all clients with the status Active on their Birthday', '0', '0'); +INSERT INTO `stretchy_report` (`report_name`, `report_type`, `report_subtype`, `report_sql`, `description`, `core_report`, `use_report`) VALUES ('Loan Rejected - Email', 'Email', 'Triggered', 'SELECT mc.id, mc.firstname, mc.middlename as middlename, mc.lastname, mc.display_name as FullName, mc.email_address as EmailAddress, mc.group_name as GroupName,\n mo.name as officename, ifnull(od.phoneNumber,\'\') as officenummber, ml.id as loanId, ml.account_no as accountnumber, ml.principal_amount_proposed as loanamount, ml.annual_nominal_interest_rate as annualinterestrate\n FROM\n m_office mo\n JOIN m_office ounder ON ounder.hierarchy LIKE CONCAT(mo.hierarchy, \'%\')\n AND ounder.hierarchy like CONCAT(\'.\', \'%\')\n LEFT JOIN (\n select \n ml.id as loanId, \n ifnull(mc.id,mc2.id) as id, \n ifnull(mc.firstname,mc2.firstname) as firstname, \n ifnull(mc.middlename,ifnull(mc2.middlename,(\'\'))) as middlename, \n ifnull(mc.lastname,mc2.lastname) as lastname, \n ifnull(mc.display_name,mc2.disp lay_name) as display_name, \n ifnull(mc.status_enum,mc2.status_enum) as status_enum,\n ifnull(mc.email_address,mc2.email_address) as email_address,\n ifnull(mg.office_id,mc2.office_id) as office_id,\n ifnull(mg.staff_id,mc2.staff_id) as staff_id,\n mg.id as group_id, \nmg.display_name as group_name\n from\n m_loan ml\n left join m_group mg on mg.id = ml.group_id\n left join m_group_client mgc on mgc.group_id = mg.id\n left join m_client mc on mc.id = mgc.client_id\n left join m_client mc2 on mc2.id = ml.client_id\n order by loanId\n ) mc on mc.office_id = ounder.id\n left join ml_office_details as od on od.office_id = mo.id\n left join m_loan ml on ml.id = mc.loanId\n WHERE mc.status_enum = 300 and mc.email_address is not null\n and (mo.id = ${officeId} or ${officeId} = -1)\n and (mc.staff_id = ${staffId} or ${staffId} = -1)\nand (ml.id = ${loanId} or ${loanId} = -1)\nand (mc.id = ${clientId} or ${clientId} = -1)\nand (mc.group_id = ${groupId} or ${groupId} = -1) \nand (ml.loan_type _enum = ${loanType} or ${loanType} = -1)', 'Loan and client data of rejected loan', '0', '0'); +INSERT INTO `stretchy_report` (`report_name`, `report_type`, `report_subtype`, `report_sql`, `description`, `core_report`, `use_report`) VALUES ('Loan Approved - Email', 'Email', 'Triggered', 'SELECT mc.id, mc.firstname, mc.middlename as middlename, mc.lastname, mc.display_name as FullName, mc.email_address as EmailAddress, mc.group_name as GroupName,\n mo.name as officename, ifnull(od.phoneNumber,\'\') as officenummber, ml.id as loanId, ml.account_no as accountnumber, ml.principal_amount_proposed as loanamount, ml.annual_nominal_interest_rate as annualinterestrate\n FROM\n m_office mo\n JOIN m_office ounder ON ounder.hierarchy LIKE CONCAT(mo.hierarchy, \'%\')\n AND ounder.hierarchy like CONCAT(\'.\', \'%\')\n LEFT JOIN (\n select \n ml.id as loanId, \n ifnull(mc.id,mc2.id) as id, \n ifnull(mc.firstname,mc2.firstname) as firstname, \n ifnull(mc.middlename,ifnull(mc2.middlename,(\'\'))) as middlename, \n ifnull(mc.lastname,mc2.lastname) as lastname, \n ifnull(mc.display_name,mc2.disp lay_name) as display_name, \n ifnull(mc.status_enum,mc2.status_enum) as status_enum,\n ifnull(mc.email_address,mc2.email_address) as email_address,\n ifnull(mg.office_id,mc2.office_id) as office_id,\n ifnull(mg.staff_id,mc2.staff_id) as staff_id,\n mg.id as group_id, \nmg.display_name as group_name\n from\n m_loan ml\n left join m_group mg on mg.id = ml.group_id\n left join m_group_client mgc on mgc.group_id = mg.id\n left join m_client mc on mc.id = mgc.client_id\n left join m_client mc2 on mc2.id = ml.client_id\n order by loanId\n ) mc on mc.office_id = ounder.id\n left join ml_office_details as od on od.office_id = mo.id\n left join m_loan ml on ml.id = mc.loanId\n WHERE mc.status_enum = 300 and mc.email_address is not null\n and (mo.id = ${officeId} or ${officeId} = -1)\n and (mc.staff_id = ${staffId} or ${staffId} = -1)\nand (ml.id = ${loanId} or ${loanId} = -1)\nand (mc.id = ${clientId} or ${clientId} = -1)\nand (mc.group_id = ${groupId} or ${groupId} = -1)\nand (ml.loan_type_ enum = ${loanType} or ${loanType} = -1)', 'Loan and client data of approved loan', '0', '0'); +INSERT INTO `stretchy_report` (`report_name`, `report_type`, `report_subtype`, `report_sql`, `description`, `core_report`, `use_report`) VALUES ('Loan Repayment - Email', 'Email', 'Triggered', 'select ml.id as loanId,mc.id, mc.firstname, ifnull(mc.middlename,\'\') as middlename, mc.lastname, mc.display_name as FullName, email_address as EmailAddress, mc.group_name as GroupName, round(ml.principal_amount, ml.currency_digits) as LoanAmount, round(ml.`total_outstanding_derived`, ml.currency_digits) as LoanOutstanding,\nifnull(od.phoneNumber,\'\') as officenummber,ml.`account_no` as LoanAccountId, round(mlt.amountPaid, ml.currency_digits) as repaymentAmount\nFROM m_office mo\nJOIN m_office ounder ON ounder.hierarchy LIKE CONCAT(mo.hierarchy, \'%\')\nAND ounder.hierarchy like CONCAT(\'.\', \'%\')\nLEFT JOIN (\n select \n ml.id as loanId, \n ifnull(mc.id,mc2.id) as id, \n ifnull(mc.firstname,mc2.firstname) as firstname, \n ifnull(mc.middlename,ifnull(mc2.middlename,(\'\'))) as middlename, \n ifnull(mc.lastname,mc2.lastname) as lastname, \n ifnull(mc.display_name,mc2.display_name) as display_name, \n ifnull(mc.status_enum,mc2.status_enum) as status_enum,\n ifnull(mc.email_address,mc2.email_address) as email_address,\n ifnull(mg.office_id,mc2.office_id) as office_id,\n ifnull(mg.staff_id,mc2.staff_id) as staff_id,\n mg.id as group_id, \nmg.display_name as group_name\n from\n m_loan ml\n left join m_group mg on mg.id = ml.group_id\n left join m_group_client mgc on mgc.group_id = mg.id\n left join m_client mc on mc.id = mgc.client_id\n left join m_client mc2 on mc2.id = ml.client_id\n order by loanId\n ) mc on mc.office_id = ounder.id\nleft join ml_office_details as od on od.office_id = mo.id\nright join m_loan as ml on mc.loanId = ml.id\nright join(\nselect mlt.amount as amountPaid,mlt.id,mlt.loan_id\nfrom m_loan_transaction mlt\nwhere mlt.is_reversed = 0 \ngroup by mlt.loan_id\n) as mlt on mlt.loan_id = ml.id\nright join m_loan_repayment_schedule as mls1 on ml.id = ml s1.loan_id and mls1.`completed_derived` = 0\nand mls1.installment = (SELECT MIN(installment) from m_loan_repayment_schedule where loan_id = ml.id and duedate <= CURDATE() and completed_derived=0)\nwhere mc.status_enum = 300 and email_address is not null and ml.`loan_status_id` = 300\nand (mo.id = ${officeId} or ${officeId} = -1)\nand (mc.staff_id = ${staffId} or ${staffId} = -1)\nand (ml.loan_type_enum = ${loanType} or ${loanType} = -1)\nand ml.id in (select mla.loan_id from m_loan_arrears_aging mla)\ngroup by ml.id', 'Loan Repayment', '0', '0'); + +INSERT INTO `stretchy_report_parameter` (`report_id`, `parameter_id`, `report_parameter_name`) +VALUES +( (SELECT id from stretchy_report where report_name = 'Active Clients - Email'), (SELECT id from stretchy_parameter where parameter_name = 'OfficeIdSelectOne'), 'Office'), +( (SELECT id from stretchy_report where report_name = 'Active Clients - Email'), (SELECT id from stretchy_parameter where parameter_name = 'LoanOfficerSelectOneRec'), 'Loanofficer'), +( (SELECT id from stretchy_report where report_name = 'Prospective Clients - Email'), (SELECT id from stretchy_parameter where parameter_name = 'OfficeIdSelectOne'), 'Office'), +( (SELECT id from stretchy_report where report_name = 'Prospective Clients - Email'), (SELECT id from stretchy_parameter where parameter_name = 'LoanOfficerSelectOneRec'), 'Loanofficer'), +( (SELECT id from stretchy_report where report_name = 'Active Loan Clients - Email'), (SELECT id from stretchy_parameter where parameter_name = 'OfficeIdSelectOne'), 'Office'), +( (SELECT id from stretchy_report where report_name = 'Active Loan Clients - Email'), (SELECT id from stretchy_parameter where parameter_name = 'LoanOfficerSelectOneRec'), 'Loanofficer'), +( (SELECT id from stretchy_report where report_name = 'Active Loan Clients - Email'), (SELECT id from stretchy_parameter where parameter_name = 'cycleX'), 'Cycle X'), +( (SELECT id from stretchy_report where report_name = 'Active Loan Clients - Email'), (SELECT id from stretchy_parameter where parameter_name = 'cycleY'), 'Cycle Y'), +( (SELECT id from stretchy_report where report_name = 'Loans in arrears - Email'), (SELECT id from stretchy_parameter where parameter_name = 'OfficeIdSelectOne'), 'Office'), +( (SELECT id from stretchy_report where report_name = 'Loans in arrears - Email'), (SELECT id from stretchy_parameter where parameter_name = 'LoanOfficerSelectOneRec'), 'Loanofficer'), +( (SELECT id from stretchy_report where report_name = 'Loans in arrears - Email'), (SELECT id from stretchy_parameter where parameter_name = 'fromX'), 'fromX'), +( (SELECT id from stretchy_report where report_name = 'Loans in arrears - Email'), (SELECT id from stretchy_parameter where parameter_name = 'toY'), 'toY'), +( (SELECT id from stretchy_report where report_name = 'Loans disbursed to clients - Email'), (SELECT id from stretchy_parameter where parameter_name = 'OfficeIdSelectOne'), 'Office'), +( (SELECT id from stretchy_report where report_name = 'Loans disbursed to clients - Email'), (SELECT id from stretchy_parameter where parameter_name = 'LoanOfficerSelectOneRec'), 'Loanofficer'), +( (SELECT id from stretchy_report where report_name = 'Loans disbursed to clients - Email'), (SELECT id from stretchy_parameter where parameter_name = 'fromX'), 'fromX'), +( (SELECT id from stretchy_report where report_name = 'Loans disbursed to clients - Email'), (SELECT id from stretchy_parameter where parameter_name = 'toY'), 'toY'), +( (SELECT id from stretchy_report where report_name = 'Loans disbursed to clients - Email'), (SELECT id from stretchy_parameter where parameter_name = 'cycleX'), 'Cycle X'), +( (SELECT id from stretchy_report where report_name = 'Loans disbursed to clients - Email'), (SELECT id from stretchy_parameter where parameter_name = 'cycleY'), 'Cycle Y'), +( (SELECT id from stretchy_report where report_name = 'Loan payments due - Email'), (SELECT id from stretchy_parameter where parameter_name = 'OfficeIdSelectOne'), 'Office'), +( (SELECT id from stretchy_report where report_name = 'Loan payments due - Email'), (SELECT id from stretchy_parameter where parameter_name = 'LoanOfficerSelectOneRec'), 'Loanofficer'), +( (SELECT id from stretchy_report where report_name = 'Loan payments due - Email'), (SELECT id from stretchy_parameter where parameter_name = 'fromX'), 'fromX'), +( (SELECT id from stretchy_report where report_name = 'Loan payments due - Email'), (SELECT id from stretchy_parameter where parameter_name = 'toY'), 'toY'), +( (SELECT id from stretchy_report where report_name = 'Dormant Prospects - Email'), (SELECT id from stretchy_parameter where parameter_name = 'OfficeIdSelectOne'), 'Office'), +( (SELECT id from stretchy_report where report_name = 'Dormant Prospects - Email'), (SELECT id from stretchy_parameter where parameter_name = 'LoanOfficerSelectOneRec'), 'Loanofficer'), +( (SELECT id from stretchy_report where report_name = 'Active Group Leaders - Email'), (SELECT id from stretchy_parameter where parameter_name = 'OfficeIdSelectOne'), 'Office'), +( (SELECT id from stretchy_report where report_name = 'Active Group Leaders - Email'), (SELECT id from stretchy_parameter where parameter_name = 'LoanOfficerSelectOneRec'), 'Loanofficer'), +( (SELECT id from stretchy_report where report_name = 'Loan Payments Due (Overdue Loans) - Email'), (SELECT id from stretchy_parameter where parameter_name = 'OfficeIdSelectOne'), 'Office'), +( (SELECT id from stretchy_report where report_name = 'Loan Payments Due (Overdue Loans) - Email'), (SELECT id from stretchy_parameter where parameter_name = 'LoanOfficerSelectOneRec'), 'Loanofficer'), +( (SELECT id from stretchy_report where report_name = 'Loan Payments Due (Overdue Loans) - Email'), (SELECT id from stretchy_parameter where parameter_name = 'fromX'), 'fromX'), +( (SELECT id from stretchy_report where report_name = 'Loan Payments Due (Overdue Loans) - Email'), (SELECT id from stretchy_parameter where parameter_name = 'toY'), 'toY'), +( (SELECT id from stretchy_report where report_name = 'Loan Payments Due (Overdue Loans) - Email'), (SELECT id from stretchy_parameter where parameter_name = 'overdueX'), 'overdueX'), +( (SELECT id from stretchy_report where report_name = 'Loan Payments Due (Overdue Loans) - Email'), (SELECT id from stretchy_parameter where parameter_name = 'overdueY'), 'overdueY'), +( (SELECT id from stretchy_report where report_name = 'Loan Payments Received (Active Loans) - Email'), (SELECT id from stretchy_parameter where parameter_name = 'OfficeIdSelectOne'), 'Office'), +( (SELECT id from stretchy_report where report_name = 'Loan Payments Received (Active Loans) - Email'), (SELECT id from stretchy_parameter where parameter_name = 'LoanOfficerSelectOneRec'), 'Loanofficer'), +( (SELECT id from stretchy_report where report_name = 'Loan Payments Received (Active Loans) - Email'), (SELECT id from stretchy_parameter where parameter_name = 'fromX'), 'fromX'), +( (SELECT id from stretchy_report where report_name = 'Loan Payments Received (Active Loans) - Email'), (SELECT id from stretchy_parameter where parameter_name = 'toY'), 'toY'), +( (SELECT id from stretchy_report where report_name = 'Loan Payments Received (Overdue Loans) - Email'), (SELECT id from stretchy_parameter where parameter_name = 'OfficeIdSelectOne'), 'Office'), +( (SELECT id from stretchy_report where report_name = 'Loan Payments Received (Overdue Loans) - Email'), (SELECT id from stretchy_parameter where parameter_name = 'LoanOfficerSelectOneRec'), 'Loanofficer'), +( (SELECT id from stretchy_report where report_name = 'Loan Payments Received (Overdue Loans) - Email'), (SELECT id from stretchy_parameter where parameter_name = 'fromX'), 'fromX'), +( (SELECT id from stretchy_report where report_name = 'Loan Payments Received (Overdue Loans) - Email'), (SELECT id from stretchy_parameter where parameter_name = 'toY'), 'toY'), +( (SELECT id from stretchy_report where report_name = 'Loan Payments Received (Overdue Loans) - Email'), (SELECT id from stretchy_parameter where parameter_name = 'overdueX'), 'overdueX'), +( (SELECT id from stretchy_report where report_name = 'Loan Payments Received (Overdue Loans) - Email'), (SELECT id from stretchy_parameter where parameter_name = 'overdueY'), 'overdueY'), +( (SELECT id from stretchy_report where report_name = 'Happy Birthday - Email'), (SELECT id from stretchy_parameter where parameter_name = 'OfficeIdSelectOne'), 'Office'), +( (SELECT id from stretchy_report where report_name = 'Happy Birthday - Email'), (SELECT id from stretchy_parameter where parameter_name = 'LoanOfficerSelectOneRec'), 'Loanofficer'), +( (SELECT id from stretchy_report where report_name = 'Loan Fully Repaid - Email'), (SELECT id from stretchy_parameter where parameter_name = 'OfficeIdSelectOne'), 'Office'), +( (SELECT id from stretchy_report where report_name = 'Loan Fully Repaid - Email'), (SELECT id from stretchy_parameter where parameter_name = 'LoanOfficerSelectOneRec'), 'Loanofficer'), +( (SELECT id from stretchy_report where report_name = 'Loan Fully Repaid - Email'), (SELECT id from stretchy_parameter where parameter_name = 'fromX'), 'fromX'), +( (SELECT id from stretchy_report where report_name = 'Loan Fully Repaid - Email'), (SELECT id from stretchy_parameter where parameter_name = 'toY'), 'toY'), +( (SELECT id from stretchy_report where report_name = 'Loans Outstanding after final instalment date - Email'), (SELECT id from stretchy_parameter where parameter_name = 'OfficeIdSelectOne'), 'Office'), +( (SELECT id from stretchy_report where report_name = 'Loans Outstanding after final instalment date - Email'), (SELECT id from stretchy_parameter where parameter_name = 'toY'), 'toY'), +( (SELECT id from stretchy_report where report_name = 'Loans Outstanding after final instalment date - Email'), (SELECT id from stretchy_parameter where parameter_name = 'fromX'), 'fromX'), +( (SELECT id from stretchy_report where report_name = 'Loans Outstanding after final instalment date - Email'), (SELECT id from stretchy_parameter where parameter_name = 'LoanOfficerSelectOneRec'), 'Loanofficer'), +( (SELECT id from stretchy_report where report_name = 'Loan Rejected - Email'), (SELECT id from stretchy_parameter where parameter_name = 'OfficeIdSelectOne'), 'Office'), +( (SELECT id from stretchy_report where report_name = 'Loan Rejected - Email'), (SELECT id from stretchy_parameter where parameter_name = 'LoanOfficerSelectOneRec'), 'Loanofficer'), +( (SELECT id from stretchy_report where report_name = 'Loan Rejected - Email'), (SELECT id from stretchy_parameter where parameter_name = 'DefaultLoan'), 'loanId'), +( (SELECT id from stretchy_report where report_name = 'Loan Rejected - Email'), (SELECT id from stretchy_parameter where parameter_name = 'DefaultClient'), 'clientId'), +( (SELECT id from stretchy_report where report_name = 'Loan Rejected - Email'), (SELECT id from stretchy_parameter where parameter_name = 'DefaultGroup'), 'groupId'), +( (SELECT id from stretchy_report where report_name = 'Loan Rejected - Email'), (SELECT id from stretchy_parameter where parameter_name = 'SelectLoanType'), 'loanType'), +( (SELECT id from stretchy_report where report_name = 'Loan Approved - Email'), (SELECT id from stretchy_parameter where parameter_name = 'OfficeIdSelectOne'), 'Office'), +( (SELECT id from stretchy_report where report_name = 'Loan Approved - Email'), (SELECT id from stretchy_parameter where parameter_name = 'LoanOfficerSelectOneRec'), 'Loanofficer'), +( (SELECT id from stretchy_report where report_name = 'Loan Approved - Email'), (SELECT id from stretchy_parameter where parameter_name = 'DefaultLoan'), 'loanId'), +( (SELECT id from stretchy_report where report_name = 'Loan Approved - Email'), (SELECT id from stretchy_parameter where parameter_name = 'DefaultClient'), 'clientId'), +( (SELECT id from stretchy_report where report_name = 'Loan Approved - Email'), (SELECT id from stretchy_parameter where parameter_name = 'DefaultGroup'), 'groupId'), +( (SELECT id from stretchy_report where report_name = 'Loan Approved - Email'), (SELECT id from stretchy_parameter where parameter_name = 'SelectLoanType'), 'loanType'), +( (SELECT id from stretchy_report where report_name = 'Loan Repayment - Email'), (SELECT id from stretchy_parameter where parameter_name = 'OfficeIdSelectOne'), 'Office'), +( (SELECT id from stretchy_report where report_name = 'Loan Repayment - Email'), (SELECT id from stretchy_parameter where parameter_name = 'LoanOfficerSelectOneRec'), 'Loanofficer'), +( (SELECT id from stretchy_report where report_name = 'Loan Repayment - Email'), (SELECT id from stretchy_parameter where parameter_name = 'SelectLoanType'), 'loanType'); + + + +INSERT INTO `m_permission` (`grouping`, `code`, `entity_name`, `action_name`, `can_maker_checker`) +VALUES +('report', 'READ_Active Clients - Email', 'Active Clients - Email', 'READ', 0), +('report', 'READ_Prospective Clients - Email', 'Prospective Clients - Email', 'READ', 0), +('report', 'READ_Active Loan Clients - Email', 'Active Loan Clients - Email', 'READ', 0), +('report', 'READ_Loans in arrears - Email', 'Loans in arrears - Email', 'READ', 0), +('report', 'READ_Loans disbursed to clients - Email', 'Loans disbursed to clients - Email', 'READ', 0), +('report', 'READ_Loan payments due - Email', 'Loan payments due - Email', 'READ', 0), +('report', 'READ_Dormant Prospects - Email', 'Dormant Prospects - Email', 'READ', 0), +('report', 'READ_Active Group Leaders - Email', 'Active Group Leaders - Email', 'READ', 0), +('report', 'READ_Loan Payments Due (Overdue Loans) - Email', 'Loan Payments Due (Overdue Loans) - Email', 'READ', 0), +('report', 'READ_Loan Payments Received (Active Loans) - Email', 'Loan Payments Received (Active Loans) - Email', 'READ', 0), +('report', 'READ_Loan Payments Received (Overdue Loans) - Email', 'Loan Payments Received (Overdue Loans) - Email', 'READ', 0), +('report', 'READ_Loan Fully Repaid - Email', 'Loan Fully Repaid - Email', 'READ', 0), +('report', 'READ_Loans Outstanding after final instalment date - Email', 'Loans Outstanding after final instalment date - Email', 'READ', 0), +('report', 'READ_Happy Birthday - Email', 'Happy Birthday - Email', 'READ', 0), +('report', 'READ_Loan Rejected - Email', 'Loan Rejected - Email', 'READ', 0), +('report', 'READ_Loan Approved - Email', 'Loan Approved - Email', 'READ', 0), +('report', 'READ_Loan Repayment - Email', 'Loan Repayment - Email', 'READ', 0); \ No newline at end of file
