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 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>

2017-10-28

Email automation, templates - 5

In the previous posts I have described how I grabbed the recipients of mails from SharePoint to a database in the Data Warehouse, now it’s time to deal with the mail templates there were five template in eight languages forty in all.. I asked for one english template, to my utmost stupefying horror the reply was “Oh the templates are Outlook documents.”
I began to sweat, “Outlook documents I cannot handle Outlook documents, surly the templates will be simple proper HTML scripts” I stuttered in a whisper. “No we work with Outlook documents, it must be Outlook documents, but they can easily be converted into HTML”. Now when I thought I was more or less done, I realised I was out on a quagmire of mojibake hell. I could well imagine what Outlook html looked like, but of course it was worse, first I could not believe my eyes when I saw the shitload of crap the html conversion cranked out, this is the reason every sane person prefers JSON notation. Structural fascists have kidnapped, raped and abused XML/HTML by introducing an inconceivable complex standard which some interprets as “everything in the standard must be used in every document”. To my surprise the the first test template could be converted to utf-8 by:
iconv -f iso-8859-1 -t utf-8 templatename > utf-8templatename


After many hours and harsh words I had replaced “personal text” with my ‘@’ symbols like ‘@xQname’, removed distorting HTML code and reapplied images I was ready to produce my first mail. Of course the mail looked like shit anything other than english letters was utterly destroyed. I was in deep despair, I went through all I had done to fix the Outlook document and I was sure I had converted the entire document to UTF-8 and I processed it all the way as UTF-8, still it was trashed by some stupid translation process. I called a friend, Andreas he told me there is probably a meta tag in the document specifying the character set or encoding used creating the document, and yes I found charset=windows-1252 a character set I never had heard of “What is that and why is it there?”
“That you have to ask Microsoft about, welcome to the wonderful world of email encodings” Andreas replied. I changed the tag to UTF-8, lo and behold a correct mail entered my Outlook mailbox, I was so happy I almost started to cry. I use PHPmailer for the mail delivery, a fine piece of software, that most likely hide away a lot of encoding details. I not for a second think my ordeal is over yet, each document will certainly expose new HTML features I will trip on, but until then I can enjoy my small victory over encodings.
In this post I describe my encoding problems and how I deal with them. I found some encodings in outlook msg template sent to me, one of them was  UTF16le, noone of the guys sending templates to me knew about the concept of text encoding  neither did the lady who sent the wonderful UTF16le template but I did not care, I just requested she should send all templates to me no one else  With templates in UTF16le I could build a reliable process from outlook msg template to an HTML UTF-8 version I can work with, substitute personalised text as email addresses and deliver to recipients via my mail generator.Here you see part of one template in simplified chinese.
 
My process is manual:

  • Replace ‘personal text’ with my symbolics, the red above is replace by @xQmigrationDate
  • Convert the outlook msg template to HTM
  • FTP relevant HTM parts to the Data Warehouse
  • Convert the template from UTF16le to UTF8.
  • Change meta tag charset to UTF8
  • Remove some distorting crap from the templates.
  • 'Reapply images' 
  • Save the template in a map

That’s it. Now I only have to create the mail generator, which finally is the topic for the next post.