In the last post on email automation I wrote this post would describe how I tackle the mail templates, but it will not. If you read the last two posts ‘from SharePoint to MySQL’ you may sense my dissatisfaction with the solution, which can be described as ‘brittle’, actually it doesn't hold water. Before I start, I like USA if I could I probably settle down in New England.
However I cannot express strong enough how I loath USA standards, it is a pity a land still rooted in seventeenth century european idea world, arming themselves to the teeth in fear of the red coats, a nation who have based measurements on medieval European systems, it is a pity such a realm has been allowed to set a de facto standards for measurements. Napoleon Bonaparte created the new modern world around 1800, with it Systeme International SI, now maintained by ISO (international standardisation organisation) hence ISO standards. ISO standards are acknowledged by the world, except for a few medieval realms which still use the thumb of Henry the VIII as one base for measurements. Those realms and their companies do not recognize ISO, e.g. ISO would be the most practical Locale as default, but ISO is not even an option in Microsoft collaboration software SharePoint.
In the post I complain about the odd behavior of PowerShell script or lack of respect of the Windows locale when creating a CSV file. The comma is a bad word delimiter, I use a semicolon also a bad delimiter but better than the comma. Giving the user an option of choosing a delimiter of choice and respecting the Locale is the right thing to do but neglected by PS scripting (Local=true is a documented option, but how do you specify it?).
During the week I learnt a few things up to 8.1 Windows do not do UTF-8 encoding terribly well, with version 10 Windows seems to master UTF-8 (I’m still on 8.1). In all fairness unicode is very complicated. However Windows 8.1 seem to understand UTF16le, so instead of converting my CSV file to UTF8 UTF16le is a better option. After consulting my friend Google I found the option FileFormat::xlUnicodeText which correctly encode the output in UTF16le, you specify this as
rather cryptic if you ask me but it works and that is what matters, and now the output is tab separated, which is great, unicode tab is unique hex ‘0009’, after a day of chasing red herrings I finally had a solid solution, only to find PSS spat out dates in the horrid USA format no matter what I tried. But here MySQL str-to-date() function came to the rescue
STR_TO_DATE(‘USA date string’,'%m/%d/%Y')
Reformats a USA date into a proper ISO 8601 format.
At last I had that right, only to find I overlooked a problem, my mail generator which is the ultimate goal of these posts should emit mails in local time of the recipients which meant some mails had to be sent the day before. I have a local delivery date `Migration date` in my file, since this date is the base for several mail deliveries I decided to introduce an absolute `deliveryDate` which make it much easier to select recipients of mails later on.
truncate table `@ITAB`;
load data local infile '@J_fetchConvert/@USERFILE8'
into table `@ITAB` fields terminated by '\t' optionally enclosed by '"' lines terminated by '\r\n'
ignore 1 lines
(`Status` ,@migdat ,`Status from local HR` ,`Blueprint 5.0` ,`Cpy Class`
,`Company` ,`Guid` ,`Short Name` ,`NOIMP:AD sAMAccountName` ,`Computer name`
,`OLD UPN` ,`OLD Mail` ,`NEW UPN` ,`New e-mail` ,`count` ,`Strong Authentication Requirements`
,`VOP` ,`Last Name` ,`Middle Name` ,`First Name` ,`SF Operational Unit Code`
,`Location/City` ,`Time Zone` ,`Language` ,`NOIMP:Company Country` ,`NOIMP:Country Code`
,`Legal Fam code` ,`Region` ,`Region Code` ,`Didicated/Shared` ,`Owner of legan entity`
,`Company type` ,`Owner of Operational entity` ,`Unique Staff ID`
,`NOIMP:Company Country Code` ,`NOIMP:Company City` ,`NOIMP:Company FAM Code`
,`NOIMP:Company FAM Code Legal` ,`NOIMP:Company Business Area`
,`NOIMP:Company Division` ,`Division`)
set `Migration date` = STR_TO_DATE(@migdat,'%m/%d/%Y')
,`deliveryDate` = STR_TO_DATE(@migdat,'%m/%d/%Y')
update RECIPIENTS a
join TimeZone e on e.`Time Zone` = a.`Time Zone`
set deliveryDate = date(convert_tz(concat(`Migration date`,' 05:00:00'), e.tz_offset,'+00:00'))