2017-10-31

Email automation, the generator - 6

“Can you help us? We need to send  mails to users before (and after) they are migrated  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'?>
<schedule mustcomplete='yes' logresult='yes' period='day' notify='admfail.xml' logmsg='Send NGAD migration mails'>

 <tag name='C_DB' value='NGAD' cmt='The mail database'/>
 <tag name='STATUS' value='Planned' cmt='the status of eligible users'/>
 <tag name='TID'> <function>return microtime()</function></tag> <!-- a unique id -->
 <tag name='RECIPIENTS_ACTIVE' value='RECIPIENTS_TEMP@TID'/>

 <job name='filterRecipients'                    cmt='Remove recipients not eligible for mails'>
   <sql name='recipientsCopy'>
        use @C_DB;
drop table if exists `@RECIPIENTS_ACTIVE`;
create table `@RECIPIENTS_ACTIVE` (SELECT * from RECIPIENTS);
   </sql>
   <sql name='brexit'>
delete FROM `@RECIPIENTS_ACTIVE` WHERE `NOIMP:Country Code` = 'UK';
   </sql>  
 </job>
 
 <job name='createMailList'                      cmt='Create an iterator with each mail we will send this time'>
    <tag name='STARTDATE'>
     <sql>SELECT CURDATE() as STARTDATE</sql>
   </tag>
   <tag name='ENDDATE'>
     <sql>SELECT CURDATE() as ENDDATE</sql>
   </tag>
   <tag name='TEMPTAB' value='mailusers'/>
   <forevery>
     <function cmt='Generates an array of arrays with one date for each date between @STARTDATE and @ENDDATE'>
$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 &lt; $days; $i++) {
   $rows[] = array(MAILDATE=> date('Y-m-d', strtotime("$dt"." +$i day")));
}
return $rows;
     </function><parm/>
   </forevery>  
   <sql cmt='get a row for each user that should receive a mail now'>
     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>
   <!--time(convert_tz(UTC_TIMESTAMP(),'+00:00',e.tz_offset)) BETWEEN '04:45:00' AND '05:15:00' and -->
   <!-- time(convert_tz(UTC_TIMESTAMP(),'+00:00',e.tz_offset)) BETWEEN '04:45:00' AND '05:15:00' and -->
   <sqlconverter name='sqlconverter_default.php' target='driver0'/>
   <sqlconverter name='sqlconverter_PHPExcel04.php' target='driver0'/>
   <Zsqlconverter name='sqlconverter_CSV.php' target='driver0'/>
 </job>
 <job name='cleanupTables'                       cmt='Remove work tables'>
   <sql>
        use @C_DB;
drop table if exists `@RECIPIENTS_ACTIVE`;
   </sql>
 </job>

 <job name='logMails'                            cmt='Insert mails in the log table'>
   <forevery array='@J_createMailList/driver0'/>
   <Xsql name='createtable'>
     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>
   <sql name='insertrows'>  
     use @C_DB;
     insert into maillog (Email,Timezone,Location,Shortname,Migrationdate,Language,Mailform)
     values('@xQoldMail','@xQtz_offset','@xQlocation','@xQshortName','@xQmigrationDate','@xQlanguage','@xQmailForm')  
   </sql>
 </job>

 <job name='sendMail' type='sendmail' send='yes' cmt='Send all the mails'>
   <forevery array='@J_createMailList/driver0'/>
       <mailer info='mailerNGAD.xml' pgm='phpmailer'/>
   <tag name='THECSS' file='@template/css/mysqltable01.css'/>
   <subject>@xQlanguage - @xQsubject</subject>
   <replyto>@xQreplyTo,EpiRoc AD migration</replyto>
    <attachment file='/home/tooljn/NGAD/image/image001.jpg' name='image001.jpg'/>
   <attachment file='/home/tooljn/NGAD/image/image002.png' name='image002.png'/>
   <attachment file='/home/tooljn/NGAD/image/image003.gif' name='image003.gif'/>
   <recipient>@ZZxQoldMail</recipient>
   
    <filebody file='/home/tooljn/NGAD/mail/@xQmailForm'/>
 </job>  
 <job name='sendRecipentsList' type='sendmail'   cmt='Send an Excel with all mails sent to PM'>
   <prereq type='boolean'>(count(unserialize(file_get_contents('@J_createMailList/driver0'))))</prereq>
   <!--The prereq is true if at leat one mail sent -->
   <mailer pgm='phpmailer'/>
   <subject>Mails sent by @C_application mail generator</subject>
   <attachment file='@J_createMailList/driver0.xlsx' name='sentMails.xlsx'/>
      <recipient>lars.a.johansson@se.atlascopco.com</recipient>
      <htmlbody><![CDATA[
     <html><body><p>
Hi, in the attached Excel you find a list of mails sent.
     </p></body></html>
   ]]></htmlbody>
 </job>
</schedule>

No comments:

Post a Comment