public bool UTASend(bool aSendAll = false, bool aReset = false)
{
//set UTAFlag in SPCards.
string tQuery = $"UPDATE {DW.ActiveDatabase}.spcards SET S.utaflag = 'N'";
//tQuery = $"SELECT concat(substr(mediaid,15,2), substr(mediaid,13,2), substr(mediaid,11,2), substr(mediaid,9,2), substr(mediaid,7,2), substr(mediaid,5,2), substr(mediaid,3,2), substr(mediaid,1,2)) AS ChipID, 'DEACTIVATE' as status, '{UTABegin.ToString(CF.FileNameDateFormat)}' AS UTAStartDate,'{UTAEnd.ToString(CF.FileNameDateFormat)}' AS UTAEndDate, nlfdzaehle, nticktype, nperstype, tickdesc, persdesc FROM {DW.ActiveDatabase}.spcards AS S INNER JOIN {DW.ActiveDatabase}.tickettypes AS T ON T.ticktype=S.nticktype WHERE S.saledate > '2018-04-14' AND S.utaflag='Y' AND T.utaflag='N' AND LENGTH(S.MediaID) = 16";
CF.ExecuteSQL(DW.dwConn, tQuery);
tQuery = $"UPDATE {DW.ActiveDatabase}.spcards SET utaflag='Y' WHERE nticktype=3000 OR nperstype IN (30071,30072,30073,30074)";
CF.ExecuteSQL(DW.dwConn, tQuery);
string tDBName = "utapass";
string tDate = DateTime.Now.ToString("yyyy-MM-dd HH:mm");
string tUTAFileName = @"\alta_" + tDate.Replace("-", "").Replace(" ", "_").Replace(":", "") + ".csv";
string tMailFileName = UTAFolder + tUTAFileName;
tQuery = $"SELECT concat(substr(mediaid,15,2), substr(mediaid,13,2), substr(mediaid,11,2), substr(mediaid,9,2), substr(mediaid,7,2), substr(mediaid,5,2), substr(mediaid,3,2), substr(mediaid,1,2)) AS ChipID, ";
tQuery += aReset ? "'DEACTIVATE'" : "CASE cardstatus WHEN 'A' THEN 'ACTIVATE' ELSE 'DEACTIVATE' END";
tQuery += $" AS status, '{UTABegin.ToString(CF.FileNameDateFormat)}' AS UTAStartDate,'{UTAEnd.ToString(CF.FileNameDateFormat)}' AS UTAEndDate, nlfdzaehle, nticktype, nperstype, tickdesc, persdesc FROM {DW.ActiveDatabase}.spcards WHERE saledate > '2018-04-14' AND length(mediaid)=16 AND testflag=false AND utaflag='Y' OR ((nticktype=144 AND nperstype BETWEEN 510 AND 550) OR (nperstype IN (30059, 30071, 30072, 30073, 30074))";
if (!(aSendAll || aReset))
{
tQuery += " AND utasent=false";
}
tQuery += " ORDER BY saledate";
using (DataSet tDS = CF.LoadDataSet(DW.dwConn, tQuery, new DataSet(), tDBName))
{
if (tDS.Tables.Contains(tDBName))
{
//create CSV, UTA does not want a header row.
CF.Table2CSV(tDS.Tables[tDBName], tMailFileName, false);
//save a copy to the IT folder
CF.Table2CSV(tDS.Tables[tDBName], CF.ITDataFolder + tUTAFileName, false);
//only send an email if there is something to send
if (tDS.Tables[tDBName].Rows.Count != 0)
{
//send email with attachment
ASLEmail tEmail = new ASLEmail()
{
BCCAddress = EmailFromAddress,
ToAddress = UTABulkEmail,
FromAddress = EmailFromAddress,
Subject = $"ALTA RFID Passes - {tDate}",
Body = $"RFID chip file for Alta Ski Area as of {tDate}."
};
tEmail.CreateEmail();
tEmail.Attach(tMailFileName);
tEmail.SendEmail();
}
}
}
CF.ExecuteSQL(DW.dwConn, $"UPDATE {DW.ActiveDatabase}.spcards SET utasent=true WHERE utaflag='Y' AND LENGTH(mediaid)=16");
return(true);
}
