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);
}
private void Sum_Ski_Visits()
{
string[,] sv = new string[13, 2] {
{ "TS", "0" }, { "PC", "0" }, { "EX", "0" }, { "SB", "0" }, { "MC", "0" }, { "CC", "0" }, { "SL", "0" }, { "NG", "0" }, { "SP", "0" }, { "S3", "0" }, { "EM", "0" }, { "IK", "0" }, { "SO", "0" }
}; //, { "SR", "0" }, { "OS", "0" }};
if (CF.RowExists(DW.dwConn, $"{DW.ActiveDatabase}.skivisits", $"readdate='{mStart.ToString(Mirror.AxessDateFormat)}' AND altaflag='Y'"))
{
string SkiVisitsQuery = $"SELECT COUNT(*) FROM {DW.ActiveDatabase}.skivisits WHERE readdate='{mStart.ToString(Mirror.AxessDateFormat)}' AND altaflag='Y'";
long TotalRides = 0;
for (int i = 0; i < 12; i++)
{
Int32 RideCount = Convert.ToInt32(CF.GetSQLField(DW.dwConn, $"{SkiVisitsQuery} AND tgroup='{sv[i, 0]}'"));
TotalRides += RideCount;
sv[i, 1] = RideCount.ToString();
}
//long SBOnlyRides = CF.RowCount(DW.dwConn, $"{DW.ActiveDatabase}.skivisits", $"readdate='{mStart.ToString(Mirror.AxessDateFormat)}' AND tgroup='MC' AND sbflag='Y' AND altaflag='N'");
//TotalRides -= SBOnlyRides;
//sv[4, 1] = (Convert.ToInt32(sv[4, 1]) - SBOnlyRides).ToString();
sv[12, 1] = CF.GetSQLField(DW.dwConn, $"SELECT COUNT(*) AS passes FROM {DW.ActiveDatabase}.skivisits WHERE readdate='{mStart.ToString(Mirror.AxessDateFormat)}' AND passtoalta<>0");
ValidateRow();
//first do mySQL
string tQuery = $"UPDATE {DW.ActiveDatabase}.altasum SET " +
$"tickvisit = {sv[0, 1]}, " + // TS
$"powvisit = {sv[1, 1]}, " + // PC
//$"expvisit = {sv[2, 1]}, " + // EX
$"sbvisit = {sv[3, 1]}, " + // SB
$"mcvisit = {sv[4, 1]}, " + // MC
$"ccvisit = {sv[5, 1]}, " + // CC
$"slvisit = {sv[6, 1]}, " + // SL
$"ngvisit = {sv[7, 1]}, " + // NG
$"spvisit = {sv[8, 1]}, " + // SP
$"s3visit = {sv[9, 1]}, " + // S3
$"empspvisit = {sv[10, 1]}, " + // EM
//$"ikonvisits = {sv[11,1]}, " + //IKON visits
$"passreads = {sv[12, 1]}, " + // sugarloaf pass reads
$"skivisits = {TotalRides} " + //{(Convert.ToDecimal(sv[0, 1]) + Convert.ToDecimal(sv[1, 1]) + Convert.ToDecimal(sv[2, 1]) + Convert.ToDecimal(sv[3, 1]) + Convert.ToDecimal(sv[4, 1]) + Convert.ToDecimal(sv[5, 1]) + Convert.ToDecimal(sv[6, 1]) + Convert.ToDecimal(sv[7, 1]) + Convert.ToDecimal(sv[8, 1]) + Convert.ToDecimal(sv[9, 1]) + Convert.ToDecimal(sv[10, 1])).ToString() + Convert.ToDecimal(sv[11, 1])} " +
$"WHERE saledate = ";
CF.ExecuteSQL(DW.dwConn, $"{tQuery}'{mStart.ToString(Mirror.AxessDateFormat)}'");
//then do FoxPro
//tQuery = tQuery.Replace($"{DW.ActiveDatabase}.", string.Empty);
//VFPConn.ConnectionString = @"provider=vfpoledb.1;data source = G:\FPData\altaax\altasum_x.dbf";
//CF.ExecuteSQL(VFPConn, $"{tQuery.Replace("altasum","altasum_x")}{CF.SetFoxProDate(mStart)}");
//express visits
tQuery = $"UPDATE {DW.ActiveDatabase}.altasum AS S SET expvisit = (SELECT COUNT(*) FROM {DW.ActiveDatabase}.skivisits WHERE tgroup = 'EX' AND readdate = S.saledate) WHERE type= 'DT' and saledate = '{mStart.ToString(Mirror.AxessDateFormat)}'";
CF.ExecuteSQL(DW.dwConn, tQuery);
//IKON visits
tQuery = $"UPDATE {DW.ActiveDatabase}.altasum AS S SET ikonvisits = (SELECT COUNT(*) FROM {DW.ActiveDatabase}.skivisits WHERE tgroup = 'IK' AND readdate = S.saledate) WHERE type= 'DT' and saledate >= '{mStart.ToString(Mirror.AxessDateFormat)}'";
CF.ExecuteSQL(DW.dwConn, tQuery);
//fix express cards
tQuery = $"UPDATE {DW.ActiveDatabase}.altasum AS S SET expsales = (SELECT IFNULL(sum(tariff), 0) AS tariff FROM {DW.ActiveDatabase}.salesdata WHERE tgroup = 'EX' and saledate > '2018-06-01' and saledate = S.saledate and nkassanr <> 33)";
CF.ExecuteSQL(DW.dwConn, tQuery);
tQuery = $"UPDATE {DW.ActiveDatabase}.altasum AS S SET expsalesq = (SELECT COUNT(*) AS uses FROM {DW.ActiveDatabase}.salesdata WHERE tgroup = 'EX' and saledate > '2018-06-01' and saledate = S.saledate and nkassanr <> 33)";
CF.ExecuteSQL(DW.dwConn, tQuery);
tQuery = $"UPDATE {DW.ActiveDatabase}.altasum AS S SET expuse = (SELECT IFNULL(sum(tariff), 0) AS tariff FROM {DW.ActiveDatabase}.salesdata WHERE tgroup = 'EX' and saledate > '2018-06-01' and saledate = S.saledate and nkassanr = 33)";
CF.ExecuteSQL(DW.dwConn, tQuery);
tQuery = $"UPDATE {DW.ActiveDatabase}.altasum AS S SET expuseq = (SELECT COUNT(*) AS uses FROM {DW.ActiveDatabase}.salesdata WHERE tgroup = 'EX' and saledate > '2018-06-01' and saledate = S.saledate and nkassanr = 33)";
CF.ExecuteSQL(DW.dwConn, tQuery);
//fix MTC
tQuery = $"UPDATE {DW.ActiveDatabase}.altasum AS S SET mcsalesq = (SELECT COUNT(*) AS Uses FROM {DW.ActiveDatabase}.skivisits WHERE tgroup = 'MC' AND readdate = S.saledate and readdate >= '2018-06-01')";
CF.ExecuteSQL(DW.dwConn, tQuery);
tQuery = $"UPDATE {DW.ActiveDatabase}.altasum AS S SET mcsales = mcsalesq * 66.59 WHERE saledate > '2018-06-01'";
CF.ExecuteSQL(DW.dwConn, tQuery);
Int32 X = Convert.ToInt32(CF.GetSQLField(DW.dwConn, $"{SkiVisitsQuery} AND nticktype<1"));
Int32 Y = Convert.ToInt32(CF.GetSQLField(DW.dwConn, $"{SkiVisitsQuery} AND nticktype<1 AND nkassanr>=50 AND nkassanr<=55"));
Int32 Z = Convert.ToInt32(CF.GetSQLField(DW.dwConn, $"{SkiVisitsQuery} AND nticktype<1 AND nkassanr<=30 AND nkassanr NOT IN (20, 21, 22) AND NOT (nkassanr = 27 AND nseriennr IN (1,2))"));
if (Z + Y > 0)
{
string MText = $"Some passes read at the gates on {mStart.ToString(Mirror.AxessDateFormat)} - No matching sales in Axess.\r\n\n";
if (Z > 0)
{
MText += $"Unmatched from Alta Lodges ({Z.ToString()}).\r\n\n";
}
if (Y > 0)
{
MText += $"Unmatched from Alta Ticket Offices ({Y.ToString()}).\r\n\n";
}
DataTable TblPOEReads = CF.LoadTable(DW.dwConn, $"SELECT * FROM {DW.ActiveDatabase}.skivisits WHERE readdate='{mStart.ToString(Mirror.AxessDateFormat)}' AND altaflag='Y' AND nticktype<1 AND ((nkassanr<=30 AND nkassanr NOT IN (20, 21, 22)) OR (nkassanr>=50 AND nkassanr<=55)) AND NOT (nkassanr = 27 AND nseriennr IN (1,2))", "POEReads");
if (TblPOEReads != null)
{
MText += $"<table width=100%><tr><th>POS</th><th>Serial#</th><th>Uni</th><th>T Group</th><th>Ticket Type</th><th>Ticket Description</th><th>Person Type</th><th>Person Description</th><th>Web ID</th><th>Rides</th></tr> ";
foreach (DataRow RPOE in TblPOEReads.Rows)
{
MText += $"<tr><td>{RPOE["nkassanr"].ToString()}</td><td>{RPOE["nseriennr"].ToString()}</td><td>{RPOE["nunicodenr"].ToString()}</td><td>{RPOE["tgroup"].ToString()}</td><td>{RPOE["nticktype"].ToString()}</td><td>{RPOE["tickdesc"].ToString()}</td><td>{RPOE["nperstype"].ToString()}</td><td>{RPOE["persdesc"].ToString()}</td><td>{RPOE["Rides"].ToString()}</td></tr>";
}
MText += "</table>";
//mail tblpoereads to pete and daryl
Email.IsHTML = true;
Email.ToAddress = "[email protected];[email protected];[email protected]";
Email.Subject = "UNMATCHED READS";
Email.FromAddress = "*****@*****.**";
Email.Body = MText;
Email.CreateEmail();
Email.SendEmail();
}
}
}
}
private void EmailTransactions(string aQuery)
{
DataTable Transactions = CF.LoadTable(DW.dwConn, aQuery, "Transactions");
if (Transactions != null)
{
foreach (DataRow Transaction in Transactions.Rows)
{
string TransKey = Transaction["Transkey"].ToString();
string[,] Receipt = BuildReceipt(TransKey);
if (Receipt != null)
{
string FormattedReceipt = string.Empty;
decimal TotalBilled = 0;
for (int i = Receipt.GetLowerBound(0); i <= Receipt.GetUpperBound(0); i++)
{
TotalBilled += Convert.ToDecimal(Receipt[i, 1]);
FormattedReceipt += $"<tr><td align='left'>{Receipt[i, 0].Trim()}</td><td align='right'>${Receipt[i, 1].Trim()}</td></tr>";
}
if (FormattedReceipt != string.Empty)
{
//build the receipt
FormattedReceipt = $"<table align='right'; width=100%><tr><th align='left';width=75%></th><th align='right';width=25%></th></tr>{FormattedReceipt}<tr><td align='left'>Total:</td><td align='right'>{TotalBilled.ToString("$0.00")}</td></tr></table>";
//build purchaser address box
DataRow Purchaser = CF.LoadDataRow(AM.MirrorConn, $"SELECT lastname, firstname, gender, dob, cheight, phone, email, street, city, state, zip, country FROM {AM.ActiveDatabase}.tabaxcust WHERE nperskassa={Transaction["nperskassa"].ToString()} AND npersnr={Transaction["npersnr"].ToString()} LIMIT 1");
string FormattedUserData = $"<table><tr><td>{Purchaser["firstname"].ToString()} {Purchaser["lastname"].ToString()}</td></tr>" +
$"<tr><td>{Purchaser["street"].ToString()}</td></tr>" +
$"<tr><td>{Purchaser["city"].ToString()}, {Purchaser["state"].ToString()} {Purchaser["zip"].ToString()}</td></tr></table>";
//build Transaction Table
string TransactionTable = $"<table width=100%><th align='left'; width = 80%><b>Transaction# {TransKey}, Sale Date: {Transaction.Field<DateTime>("saledate").ToString(Mirror.AxessDateFormat)}</b></th><th align='center'; width=20%><b>Payment{(Receipt.GetUpperBound(0) != 0 ? "s" : "")} Received</b></th><tr><td>{FormattedUserData}</td><td valign='top'>{FormattedReceipt}</td></tr></table>";
//get the sales rows and build their tables.
DataTable Sales = CF.LoadTable(DW.dwConn, $"SELECT SERIALKEY, SUM(tfactor) AS Active, COUNT(*) AS Cnt, NPERSKASSA, NPERSNR, tickdesc, persdesc, tariff, wtp64, nkassanr, nseriennr, nunicodenr FROM {DW.ActiveDatabase}.salesdata WHERE transkey='{TransKey}' GROUP BY serialkey", "SalesData");
string FormattedSalesData = $"<table width=100%><th text-align: left;width=100%;height=56px></th><th text-align: right;width=210px;height=56px></th>";
string[] Attachments = new string[Sales.Rows.Count];
int AttachmentsCount = 0;
foreach (DataRow Sale in Sales.Rows)
{
if (Sale.Field <decimal>("Active") > 0)
{
string WTP64 = Sale["wtp64"].ToString().Trim();
DataRow Skier = CF.LoadDataRow(AM.MirrorConn, $"SELECT lastname, firstname, gender, dob, cheight FROM {AM.ActiveDatabase}.tabaxcust WHERE nperskassa={Sale["nperskassa"].ToString()} AND npersnr={Sale["npersnr"].ToString()} LIMIT 1");
string PassInfo = (WTP64 == string.Empty ? "NEW PASS (See Note B)" : $"WTP# {WTP64} RELOAD (See Note A)");
string SkierName = "";
if (Skier != null)
{
SkierName = $"{Skier["firstname"].ToString()} {Skier["lastname"].ToString()}";
}
FormattedSalesData += $"<tr><td><b>{SkierName}</b></td><td></td></tr>" +
$"<tr><td>{Tab5}{Sale["tickdesc"].ToString()} -- {Sale["persdesc"].ToString()}</td><td align='right'>{Sale.Field<decimal>("tariff").ToString("$0.00")}</td></tr>";
if (!Sale["tickdesc"].ToString().ToUpper().Contains("PACKAGE"))
{
if (WTP64 != string.Empty)
{
FormattedSalesData += $"<tr><td>{Tab5}<b>RELOAD</b> (See note A)</td><td></td></tr>" +
$"<tr><td>{Tab5}<b>Web ID:</b> {WTP64}</td><td></td></tr>";
}
else
{
string ExternalID = CF.GetSQLField(AM.MirrorConn, $"SELECT szauftragext FROM {AM.ActiveDatabase}.tabprepaidtickets WHERE nkassanr={Sale["nkassanr"].ToString()} AND nseriennr={Sale["nseriennr"].ToString()} AND nunicodenr={Sale["nunicodenr"].ToString()}");
if (ExternalID != string.Empty)
{
System.Diagnostics.Debug.Print($"{ExternalID} -- nkassanr={Sale["nkassanr"].ToString()} AND nseriennr={Sale["nseriennr"].ToString()} AND nunicodenr={Sale["nunicodenr"].ToString()}");
BarcodeLib.Barcode BCode = new BarcodeLib.Barcode();
System.Drawing.Image BCodeImage = BCode.Encode(TYPE.CODE128, ExternalID.Trim(), System.Drawing.Color.Black, System.Drawing.Color.White, 200, 50);
string tFile = $"{ExternalID}.jpg";
bool Found = false;
for (int i = 0; i < AttachmentsCount; i++)
{
if (Attachments[i] == tFile)
{
Found = true;
break;
}
}
if (!Found)
{
Attachments[AttachmentsCount++] = tFile;
}
string tImage = $@"C:/temp/{tFile}";
if (File.Exists(tImage))
{
//File.Delete(tImage);
}
else
{
BCodeImage.Save(tImage, System.Drawing.Imaging.ImageFormat.Jpeg);
}
FormattedSalesData += $"<tr><td>{Tab5}NEW PASS (See note B)</td><td></td></tr>" +
$"<tr><td>{Tab5}OrderID: {ExternalID}</td><td>{$@"<img src=""{tFile}"" alt='{ExternalID}'>"}</td></tr>";
}
}
}
FormattedSalesData += $"<tr><td>{AlternateBar("white",5)}</td><td></td></tr>";
}
}
FormattedSalesData += $"</table>";
string EmailData = $"<img src='http://www.alta.com/resources/Media/logos/altalogo.jpg' alt='Alta Ski Area'; width=25%><br>" +
$"<h1>Thank you for your purchase.</h1><br>Your order information appears below." +
GrayBar + TransactionTable + GrayBar + FormattedSalesData +
$"{AlternateBar("white", 10)}<b>What You Need To Know:<br><br>(A) RELOADS - </b>Your pass is activated. Enjoy direct-to-lift access and remember the pass works best when placed in a pocket by itself. Please verify the Web ID on your pass matches the Web ID on the receipt.<br>{AlternateBar("white", 5)}<b>(B) NEW PASSES - </b>Your pass must be picked up in person.<br>{AlternateBar("white", 5)}" +
//regular season
//$"<b>Season Pass Office Locations & Hours:<br>Alta Season Pass Offices<br>Summer</b> through November 5th<br>Monday - Thursday, 8:00am - 4:30pm " +
//END regular season
//Summer Season
$"<table style='width:7.5in'>" +
$"<table style='width:7.5in'><tr><td align='left'; width=100%><b>PASS PICKUP OPTIONS</b></td></tr></table>" +
$"<table cellspacing='0';cellpadding='0';width=100%><tr><th width=5%></th><th align='left';width=20%></th><th align='left';width=35%></th><th align='left';width=5%></th><th align='left';width=35%></th></tr>" +
$"<tr><td></td><td>Monday — Thursday</td><td>(Through Nov. 4th, 7 days a week following)</td><td></td><td><table width=100%><tr><td width=30%;align='left'>7:30 to 4:30 pm</td><td width=70%;align='right'>Skier Services</td></tr></table></td></tr>" +
$"<tr><td></td><td>Saturday</td><td>(Through Nov. 10th)</td><td></td><td><table width=100%><tr><td align='left';width = 30%>9 to 5 pm</td><td align='right';width=70%>Wasatch Powder House<br>3138 E 6200 S Holladay, UT</td></tr></table></td></tr></table><br><br>" +
$"<table width=100%>There is a chill in the air which means that winter is coming. The countdown has begun with Alta's opening day only {(new DateTime(2018, 11, 24) - DateTime.Now).Days.ToString()} days away (the day after Thanksgiving). Thanks for skiing Alta.<br></table>" +
//END Summer Season
$"<br>For questions or further assistance <i><b>do not</b></i> reply to this email. Call us at (801) 359-1078 or email <i>[email protected]</i>." +
$"<br>Thank you for skiing Alta." +
$"<br><b>Alta Ski Area</b>";
//send email
if (Purchaser["email"].ToString().Contains("@"))
{
EM.ToAddress = Purchaser["email"].ToString();
EM.FromAddress = "*****@*****.**";
//EM.BCCAddress = "*****@*****.**";
EM.Subject = $"Alta Purchase ({Transaction["saledate"].ToString()})";
EM.Body = EmailData;
EM.CreateEmail();
EM.IsHTML = true;
for (int i = 0; i < Sales.Rows.Count; i++)
{
if (Attachments[i] != string.Empty && Attachments[i] != null)
{
EM.Body.Replace(Attachments[i], EM.AttachJpeg($@"C:\temp\{Attachments[i]}"));
//File.Delete($@"C:\temp\{Attachments[i]}");
}
}
if (EM.SendEmail())
{
CF.ExecuteSQL(DW.dwConn, $"UPDATE {DW.ActiveDatabase}.salesdata SET email_sent='Y' WHERE transkey='{Transaction["transkey"].ToString().Trim()}' AND email_send='Y'");
}
}
for (int i = 0; i < Sales.Rows.Count; i++)
{
//File.Delete($@"C:\temp\{Attachments[i]}");
}
}
}
}
}
}