fineract-commits mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From nazeer1100...@apache.org
Subject fineract git commit: License Header is added in V322_1 and V322_2 migration scripts
Date Tue, 05 Dec 2017 06:07:00 GMT
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.shaik@confluxtechnologies.com>
Authored: Tue Dec 5 11:36:07 2017 +0530
Committer: Nazeer Hussain Shaik <nazeer.shaik@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


Mime
View raw message