12D: Email automation, the generator

“Can you help us? We need to send mails to users before (and after) they are migrated to a new Active Directory.”.

I had an idea in the back of my head to turn my Data Warehouse into a mail generator.

In some previous posts I described the setup for my mail generator. Create a database with recipients and rules for the mail distribution and transform the outlook mail templates so they can be used by the mail-generator which I describe in this mail. Without much further ado here is an overview of the ITL schedule:

First we remove recipients not eligible for these mails, then we create a list of all mails we will send this execution, then cleanup work tables, then log all mails, then send the mails and finally send an Excel with all all mails sent this time to the Project manager. Since mails should be delivered early morning local time, this schedule is fired off from Cron hourly as there is always early morning somewhere on the globe.

The first job ‘filterRecipients’ with global symbolics needed along the way is trivial:

First is the symbolics and then the job ‘filterRecipients’ which creates a working copy of the recipients and then remove UK recipients as they will get their mails from elsewhere.

Next is the big thing, the ’createMailList’ job select the recipents who should have a mail today and whose local time is 05:00:00. There is migration date for each recipients, and the recipient should have 5 mails the first 2 weeks before and the last mail 2 weeks after the migration date. There are many ways to solve this, I choosed to create an iterator with dates spanning from 2 weeks before the first migation date until 2 weeks after the last migration date. Normally start and end date is current date, but varying these dates I can simulate parts or a whole mail campaign.

One further complication is the mail receiving date is a local date while the server is on CET, this means some mails must be sent the day before which is pretty hard to select, so I have introduced a ‘delivery date’ which is dependent on the recipient's time zone.

This first part of the job sets up start and end date and the forevery iterator function is setting up the date array which is consumed by the forevery iterator itself.

This iterator traverse through the subsequent SQL day by day producing a list of mails to be sent.

At the end you see 2 SQL-converters, the default one creates an iterator-array and the Excel one creates an Excel sheet used in subsequent jobs.

The next job logs the mails to be sent, inserting rows one by one from the iterator

After the mail list is created and logged it is time to send the mails:

And finally send the Excel with all mails to whom it may concern:

The prereq in the beginning is a boolean gate, this one is is true if there is mails sent above.

This schedule is the run every hour, I use CRON for scheduling.

Here is the complete ITL code for the mail generator:

<?xml version='1.0' encoding='UTF-8' standalone='yes'?>

<tag name='TID'> <function>return microtime()</function></tag>

use @C_DB;

drop table if exists `@RECIPIENTS_ACTIVE`;

create table `@RECIPIENTS_ACTIVE` (SELECT * from RECIPIENTS);

</sql>

delete FROM `@RECIPIENTS_ACTIVE` WHERE `NOIMP:Country Code` = 'UK';

</sql>

</job>

<sql>SELECT CURDATE() as STARTDATE</sql>

</tag>

<sql>SELECT CURDATE() as ENDDATE</sql>

</tag>

$dt1 = date_create('@STARTDATE');

$dt2 = date_create('@ENDDATE');

$days = date_diff($dt2, $dt1);

$days = $days->format('%a');

$days = $days + 1;

$rows = array();

$dt = date('Ymd', strtotime("@STARTDATE"));

for ($i = 0; $i < $days; $i++) {

$rows[] = array(MAILDATE=> date('Y-m-d', strtotime("$dt"." +$i day")));

}

return $rows;

</function><parm/>

</forevery>

use @C_DB;

create temporary table `@TEMPTAB` (

SELECT '@MAILDATE' as xQmailDate

,a.`OLD Mail` as xQoldMail, a.`New e-mail` as xQnewMail, a.`OLD UPN` as xQoldUPN, a.`New UPN` as xQnewUPN

, a.`Time Zone` as xQtimeZone, a.`Location/City` as xQlocation

,a.`Short Name` as xQshortName

,a.`Migration date` as xQmigrationDate

,coalesce(b.`ITcontact`,d.`ITcontact`) xQITcontact

,c.`ISO6392` as xQlanguage

,coalesce(b.`migrationRoom`,d.`migrationRoom`) xQmigrationRoom

,coalesce(b.`ITcontactRoom`,d.`ITcontactRoom`) xQITroom

,coalesce(b.`replyto`,d.`replyto`) xQreplyTo

,e.`tz_offset` as xQtz_offset

, case '@MAILDATE'

when SUBDATE(a.`deliveryDate`, INTERVAL 2 week) then concat(c.`ISO6392`, '-2w.htm')

when SUBDATE(a.`deliveryDate`, INTERVAL 1 week) then concat(c.`ISO6392`, '-1w.htm')

when SUBDATE(a.`deliveryDate`, INTERVAL 1 day) then concat(c.`ISO6392`, '-1d.htm')

when ADDDATE(a.`deliveryDate`, INTERVAL 1 day) then concat(c.`ISO6392`, '+1d.htm')

when ADDDATE(a.`deliveryDate`, INTERVAL 2 week) then concat(c.`ISO6392`, '+2w.htm')

else 'Not Found'

END as mailForm

from `@RECIPIENTS_ACTIVE` a

left join LocationDate b on b.`Location/City` = a.`Location/City` and b.`Migration date` = a.`Migration date`

left join Language c on c.Language = a.Language

left join Location d on d.`Location/City` = a.`Location/City`

left join TimeZone e on e.`Time Zone` = a.`Time Zone`

where a.`Status` = '@STATUS' and

a.`OLD Mail` != '' and

time(convert_tz(UTC_TIMESTAMP(),'+00:00',e.tz_offset)) BETWEEN '04:45:00' AND '05:15:00' and

ADDDATE('@MAILDATE', INTERVAL 0 day) in (

SUBDATE(a.`deliveryDate`, INTERVAL 2 week)

,SUBDATE(a.`deliveryDate`, INTERVAL 1 week)

,SUBDATE(a.`deliveryDate`, INTERVAL 1 day)

,ADDDATE(a.`deliveryDate`, INTERVAL 1 day)

,ADDDATE(a.`deliveryDate`, INTERVAL 2 week)

)

and ADDDATE('@MAILDATE', INTERVAL 0 day) in (

SUBDATE(a.`deliveryDate`, INTERVAL 1 week))

);

SELECT a.*

, coalesce(b.`subject`, c.`subject`) as xQsubject

, coalesce(b.`Mailform`, c.`Mailform`) as xQmailForm

FROM `@TEMPTAB` a

left join mailTemplate b on b.`ISO6392` = a.`xQlanguage` and b.`Mailform` = a.`mailForm`

left join mailTemplate c on 'eng' = c.`ISO6392` and c.`Mailform` = concat('eng', substr(a.`mailForm`,4))

where coalesce(b.`Mailform`, c.`Mailform`) is not NULL

;

DROP table if exists `@TEMPTAB`;

</sql>

</job>

<sql>

use @C_DB;

drop table if exists `@RECIPIENTS_ACTIVE`;

</sql>

</job>

use @C_DB;

CREATE TABLE if not exists `maillog` (

`Email` varchar(80) DEFAULT NULL,

`Timezone` varchar(48) DEFAULT NULL,

`Location` varchar(64) DEFAULT NULL,

`Shortname` varchar(64) DEFAULT NULL,

`Migrationdate` varchar(10) DEFAULT NULL,

`Language` char(3) DEFAULT NULL,

Mailform varchar(10) DEFAULT NULL,

cdttm timestamp DEFAULT CURRENT_TIMESTAMP

) ENGINE=MyISAM DEFAULT CHARSET=utf8

</Xsql>

use @C_DB;

insert into maillog (Email,Timezone,Location,Shortname,Migrationdate,Language,Mailform)

values('@xQoldMail','@xQtz_offset','@xQlocation','@xQshortName','@xQmigrationDate','@xQlanguage','@xQmailForm')

</sql>

</job>

<subject>@xQlanguage - @xQsubject</subject>

<replyto>@xQreplyTo,EpiRoc AD migration</replyto>

<recipient>@ZZxQoldMail</recipient>

</job>

<prereq type='boolean'>(count(unserialize(file_get_contents('@J_createMailList/driver0'))))</prereq>

<subject>Mails sent by @C_application mail generator</subject>

<recipient>lars.a.johansson@se.atlascopco.com</recipient>

<htmlbody><![CDATA[

Hi, in the attached Excel you find a list of mails sent.

</p></body></html>

]]></htmlbody>

</job>

</schedule>

2017-10-31

Email automation, the generator - 6

No comments:

Post a Comment