Repository: fineract Updated Branches: refs/heads/develop 7368b1f04 -> b756fb70b
License Header is added in V322_1 and V322_2 migration scripts Project: http://git-wip-us.apache.org/repos/asf/fineract/repo Commit: http://git-wip-us.apache.org/repos/asf/fineract/commit/b756fb70 Tree: http://git-wip-us.apache.org/repos/asf/fineract/tree/b756fb70 Diff: http://git-wip-us.apache.org/repos/asf/fineract/diff/b756fb70 Branch: refs/heads/develop Commit: b756fb70b1ebf42c464fb4860544161b3d745147 Parents: 7368b1f Author: Nazeer Hussain Shaik <nazeer.sh...@confluxtechnologies.com> Authored: Tue Dec 5 11:36:07 2017 +0530 Committer: Nazeer Hussain Shaik <nazeer.sh...@confluxtechnologies.com> Committed: Tue Dec 5 11:36:07 2017 +0530 ---------------------------------------------------------------------- .../V322_1__scheduled_email_campaign.sql | 30 -------------------- .../core_db/V322_2__email_business_rules.sql | 18 ------------ 2 files changed, 48 deletions(-) ---------------------------------------------------------------------- http://git-wip-us.apache.org/repos/asf/fineract/blob/b756fb70/fineract-provider/src/main/resources/sql/migrations/core_db/V322_1__scheduled_email_campaign.sql ---------------------------------------------------------------------- diff --git a/fineract-provider/src/main/resources/sql/migrations/core_db/V322_1__scheduled_email_campaign.sql b/fineract-provider/src/main/resources/sql/migrations/core_db/V322_1__scheduled_email_campaign.sql index f581a0c..243e036 100644 --- a/fineract-provider/src/main/resources/sql/migrations/core_db/V322_1__scheduled_email_campaign.sql +++ b/fineract-provider/src/main/resources/sql/migrations/core_db/V322_1__scheduled_email_campaign.sql @@ -16,36 +16,6 @@ -- specific language governing permissions and limitations -- under the License. -- -create table if not exists scheduled_email_campaign ( -id bigint(20) NOT NULL AUTO_INCREMENT, -campaign_name varchar(100) NOT NULL, -campaign_type int NOT NULL, -businessRule_id int NOT NULL, -param_value text, -status_enum int NOT NULL, -closedon_date date, -closedon_userid bigint(20), -submittedon_date date, -submittedon_userid bigint(20), -approvedon_date date, -approvedon_userid bigint(20), -recurrence varchar(100), -next_trigger_date datetime, -last_trigger_date datetime, -recurrence_start_date datetime, -email_subject varchar(100) not null, -email_message text not null, -email_attachment_file_format varchar(10) not null, -stretchy_report_id int not null, -stretchy_report_param_map text null, -previous_run_status varchar(10) null, -previous_run_error_log text null, -previous_run_error_message text null, -is_visible tinyint(1) null, -foreign key (submittedon_userid) references m_appuser(id), -foreign key (stretchy_report_id) references stretchy_report(id), - PRIMARY KEY (id) -)ENGINE=InnoDB DEFAULT CHARSET=utf8; CREATE TABLE IF NOT EXISTS scheduled_email_messages_outbound ( `id` bigint(20) NOT NULL AUTO_INCREMENT, http://git-wip-us.apache.org/repos/asf/fineract/blob/b756fb70/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 index c17cb51..58440a5 100644 --- 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 @@ -16,24 +16,6 @@ -- 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
