public bool MTCSPDownload() //update ClistPerm from MtnCol Passholder table.
{
CurrentFunction = "MTCSPDownload";
string LFileName = "MCReciprocity";
statusStrip1.Items[0].Text = "Initializing";
SetTaskStatus("", "", pbMTCDownload);
Application.DoEvents();
//if Resorts list has not been loaded, load it now.
if (Resorts == null)
{
GetMTCResorts();
if (Resorts == null)
{
return(false);
}
if (Resorts.Rows.Count == 0)
{
return(false);
}
}
//load passholder data from Mountain Collective Server for all but Alta.
statusStrip1.Items[0].Text = "Loading data from MTC";
SetTaskStatus("", "", pbMTCDownload);
Application.DoEvents();
DataSet tDS = new DataSet();
using (SqlConnection tSQLCon = new SqlConnection(MCReciprocityConnectionString))
{
string tSQL = $"SELECT resortid, firstname, lastname, passnumber, age, city, CAST(customerID AS VarChar(20)) AS custkey FROM premiumpass.dbo.PASSHOLDER Passholder WHERE resortid <> {AltaResortID.ToString()}";
tDS = CF.LoadDataSet(tSQLCon, tSQL, tDS, LFileName);
}
//load CSV files from MtnCol reciprocity partners.
statusStrip1.Items[0].Text = "Loading data from CSVs";
SetTaskStatus("", "", pbMTCDownload);
tDS = CF.CSV2DS($"{CF.DataFolder}NZ.csv", LFileName, tDS, true, true);
//save MtnCol reciprocity passholder table to CSV file.
statusStrip1.Items[0].Text = "Saving data to CSV";
SetTaskStatus("", "", pbMTCDownload);
CF.Table2CSV(tDS.Tables["MCReciprocity"], $"{CF.DataFolder}{LFileName}.csv");
//clear okayflag in clistperm for all MtnCol passes.
statusStrip1.Items[0].Text = "Preparing to process";
SetTaskStatus("", "", pbMTCDownload);
CF.ExecuteSQL(DW.dwConn, $"UPDATE {DW.ActiveDatabase}.clistperm SET okayflag=false WHERE passcat='MC'");
//loop through each pass in reciprocity passholder table.
pbMTCDownload.Maximum = tDS.Tables[LFileName].Rows.Count;
foreach (DataRow tRow in tDS.Tables[LFileName].Rows)
{
pbMTCDownload.Value = tDS.Tables[LFileName].Rows.IndexOf(tRow) + 1;
System.Diagnostics.Debug.Print($"{pbMTCDownload.Value} of {pbMTCDownload.Maximum}");
pbMTCDownload.Refresh();
statusStrip1.Items[0].Text = "Processing MTC data";
SetTaskStatus("", "", pbMTCDownload);
//clean up last and first name so they don't trash the database.
string LastName = (tRow["lastname"].ToString().Replace('"', '~').Replace("~", "").Replace("'", " ").ToUpper() + new string(' ', 40)).Substring(0, 40).Trim();
string FirstName = (tRow["firstname"].ToString().Trim().Replace('"', '~').Replace("~", "").Replace("'", " ").ToUpper() + new string(' ', 30)).Substring(0, 30).Trim();
Regex RgxUrl = new Regex("[^A-Za-z0-9/-/'/#/ /.]");
//if the passholder name is valid then process, otherwise skip
if (!string.IsNullOrEmpty(LastName + FirstName)) // || RgxUrl.IsMatch(LastName + FirstName))
{
//get resort information (number and name)
Int32 ResortID = tRow.Field <int>("ResortID");
string ResortName = Resorts.Rows.Find(ResortID)["ResortName"].ToString().Trim();
//clean up passnumber.
string PassNo = tRow["passnumber"].ToString().Trim().Replace(" ", "").Replace(@" ", "").Replace(",", "").Replace("'", "").Replace("\"", "").Replace("\r", "");
string PassKey = ResortID.ToString() + "-" + PassNo;
int? Age = tRow.Field <int?>("age");
if (Age == null)
{
Age = 0;
}
double SalePrice = ChildPrice;
string PersonType = "MTC CHILD";
if (Age > 12 || Age == 0)
{
SalePrice = AdultPrice;
PersonType = "MTC ADULT";
}
string tSQL = string.Empty;
DataRow OldRow = CF.LoadDataRow(DW.dwConn, $"SELECT season, fname, lname, company FROM {DW.ActiveDatabase}.CListPerm WHERE passkey='{PassKey}'");
if (OldRow[0].ToString() != string.Empty)
{
tSQL = $"UPDATE {DW.ActiveDatabase}.clistperm SET season='{CF.SeasonShort}', okayflag=true, fname='{FirstName}', lname='{LastName}', company='{ResortName}', reason='MOUNTAIN COLLECTIVE RECIPROCITY', tickdesc='AREA DAY TICKET', price={SalePrice.ToString("0#.00")} WHERE passkey='{PassKey}'";
}
else
{
tSQL = $"INSERT INTO {DW.ActiveDatabase}.clistperm(dexpires,season,lname,fname,category,reason,passkey,passcat,dent,passtype,tickets,barcode,passnbr,tickdesc,media,appby,dept,okayflag,company,maxuses,price,tickpers,uses,extuses) ";
tSQL += $"Values ('{CF.MTCYearEnd.ToString(Mirror.AxessDateFormat)}','{CF.SeasonShort}','{LastName}','{FirstName}','";
tSQL += $"{ResortName.ToUpper()} SP','MOUNTAIN COLLECTIVE RECIPROCITY','{PassKey}','MC','{DateTime.Now.ToString(Mirror.AxessDateFormat)}','";
tSQL += $"AD',1,'{PassNo}','{PassNo}','AREA DAY TICKET','ONE USE','Mountain Collective','ADMIN',True,'{ResortName}',";
tSQL += $"{CF.MTCMaxUse.ToString()}, {SalePrice.ToString("0#.00")},'MTC {PersonType}',0,0)";
}
CF.ExecuteSQL(DW.dwConn, tSQL);
}
else
{
//passholder name issue, currently do nothing but in the future create an email report for all processing issues.
}
}
//remove any passes from clistperm that are no longer in the passholder table or imported CSV files.
statusStrip1.Items[0].Text = "Cleaning up";
SetTaskStatus("", "", pbMTCDownload);
Application.DoEvents();
CF.ExecuteSQL(DW.dwConn, $"DELETE FROM {DW.ActiveDatabase}.clistperm WHERE passcat='MC' AND okayflag=false");
return(true);
}
private void UpdateOtherBG(object sender, DoWorkEventArgs e)
{
CurrentFunction = "UpdateOther";
double AdultPrice = 58.00;
double ChildPrice = 30.00;
((BackgroundWorker)sender).ReportProgress(1, "Loading Alta SPCards");
string tQuery = $"SELECT firstname, lastname, wtp64, mediaid, city, CAST((DATEDIFF(now(), dob) / 365) AS signed) AS age, 'ALTA' as resortname, 1 as cardstatus FROM {DW.ActiveDatabase}.spcards WHERE cardstatus='A' AND osaflag='Y' AND testflag=false";
DataSet tDS = CF.LoadDataSet(DW.dwConn, tQuery, new DataSet(), "altapass");
if (tDS.Tables.Contains("altapass"))
{
CF.Table2CSV(tDS.Tables["altapass"], @"\\taskman\c$\acctg\winscp\data\altasp.csv");
CF.Table2CSV(tDS.Tables["altapass"], CF.ITDataFolder + "altasp.csv");
tDS.Tables.Remove("altapass");
}
((BackgroundWorker)sender).ReportProgress(2, "Loading Red Lodge");
if (File.Exists(@"\\taskman\c$\acctg\winscp\data\redlodgesp.csv"))
{
tDS = CF.CSV2DS(@"\\taskman\c$\acctg\winscp\data\redlodgesp.csv", "osaload", tDS, false, true);
File.Copy(@"\\taskman\c$\acctg\winscp\data\redlodgesp.csv", CF.ITDataFolder + "redlodgesp.csv", true);
}
((BackgroundWorker)sender).ReportProgress(3, "Loading Waschusetts");
if (File.Exists(@"\\taskman\c$\acctg\winscp\data\waschusettsp.csv"))
{
tDS = CF.CSV2DS(@"\\taskman\c$\acctg\winscp\data\waschusettsp.csv", "osaload", tDS, true, true);
File.Copy(@"\\taskman\c$\acctg\winscp\data\waschusettsp.csv", CF.ITDataFolder + "waschusettsp.csv", true);
}
((BackgroundWorker)sender).ReportProgress(4, "Loading Bridger");
if (File.Exists(@"\\taskman\c$\acctg\winscp\data\bridgersp.csv"))
{
tDS = CF.CSV2DS(@"\\taskman\c$\acctg\winscp\data\bridgersp.csv", "osaload", tDS, true, true);
File.Copy(@"\\taskman\c$\acctg\winscp\data\bridgersp.csv", CF.ITDataFolder + "bridgersp.csv", true);
}
((BackgroundWorker)sender).ReportProgress(5, "Loading Brundage");
if (File.Exists(@"\\taskman\c$\acctg\winscp\data\brundagesp.csv"))
{
tDS = CF.CSV2DS(@"\\taskman\c$\acctg\winscp\data\brundagesp.csv", "osaload", tDS, true, true);
File.Copy(@"\\taskman\c$\acctg\winscp\data\brundagesp.csv", CF.ITDataFolder + "brundagesp.csv", true);
}
((BackgroundWorker)sender).ReportProgress(6, "Loading Homewood");
if (File.Exists(@"\\taskman\c$\acctg\winscp\data\homewoodsp.csv"))
{
tDS = CF.CSV2DS(@"\\taskman\c$\acctg\winscp\data\homewoodsp.csv", "osaload", tDS, true, true);
File.Copy(@"\\taskman\c$\acctg\winscp\data\homewoodsp.csv", CF.ITDataFolder + "homewoodsp.csv", true);
}
if (btnRunSelectedUpdates.Enabled)
{
string mkey = string.Empty;
((BackgroundWorker)sender).ReportProgress(0, "Loading Alta SPCards");
if (tDS.Tables.Contains("osaload"))
{
foreach (DataRow tRow in tDS.Tables["osaload"].Rows)
{
if ((tRow[2].ToString() != mkey) && (tRow[2].ToString() != string.Empty))
{
string tResortName = CF.EscapeChar(tRow[6].ToString().ToUpper()); //resortname
mkey = tRow[2].ToString().ToUpper(); //skip duplicates //passnumber
string tPassKey = mkey; //passnumber
string tPassNumber = mkey; //passnumber
string tLastName = CF.EscapeChar(tRow[1].ToString().ToUpper()); //lastname
string tFirstName = CF.EscapeChar(tRow[0].ToString().ToUpper()); //firstname
string tBarCode = tRow[3].ToString().ToUpper(); //barcode
double tPrice = AdultPrice;
string tTickPers = "AD OTHER SKI AREA";
int tAge = 0;
if ((tRow[5].ToString() != "NULL") && (tRow[5].ToString() != string.Empty))
{
tAge = Convert.ToInt32(tRow[5].ToString());
}
if ((tAge <= 12) && (tAge != 0)) //age
{
tPrice = ChildPrice;
tTickPers = "CH OTHER SKI AREA";
}
if (!CF.RowExists(DW.dwConn, $"{DW.ActiveDatabase}.clistperm", $"passcat='OS' AND passnbr='{tPassNumber}'"))
{
tQuery = $"INSERT INTO {DW.ActiveDatabase}.clistperm (dexpires,season,lname,fname,category,reason,passkey,passcat,dent,passtype,tickets,barcode,passnbr,tickdesc,media,appby,dept,okayflag,company,maxuses,price,tickpers,uses,extuses) " +
$" Values ('{CF.YearEnd.ToString(Mirror.AxessDateFormat)}','{CF.SeasonShort}','{CF.EscapeChar(tLastName.ToUpper())}','{CF.EscapeChar(tFirstName.ToUpper())}','{CF.EscapeChar(tResortName.ToUpper())} SP','OTHER SKI AREA','{tPassKey}','OS','{DateTime.Now.ToString(Mirror.AxessDateTimeFormat)}','" +
$"AD',1,'{tBarCode}','{tPassNumber}','ALTA AREA DAY','ALTA ONE USE','OTHER SKI AREA','ADMIN',True,'{CF.EscapeChar(tResortName.ToUpper())}',999,{tPrice.ToString("#.00")},'{tTickPers}',0,0)";
CF.ExecuteSQL(DW.dwConn, tQuery);
}
}
}
tDS.Tables.Remove("osaload");
}
//now create file and transmit
//tQuery = "SELECT 10 AS resortid, 'ALTA' AS resortname, 2 AS status, 'A' AS cardstatus, upper(city), upper(lastname), upper(firstname) from " + DW.activeDatabase + ".spcards where TRIM(passcat) = 'WB' AND okayflag = 1 AND NOT (resortname='SNOWBIRD' AND barcode LIKE 'Y%')";
tQuery = $"SELECT 10 AS resortid, perskey, UPPER(firstname) AS firstname, UPPER(replace(lastname,',','')) AS lastname, UPPER(replace(city,',','')) AS city, CAST((DATEDIFF(now(), dob) / 365) AS signed) AS age, mediaid, wtp64, 1 AS status FROM {DW.ActiveDatabase}.spcards WHERE cardstatus='A' AND osaflag = 'Y' AND testflag=false";
tDS = CF.LoadDataSet(DW.dwConn, tQuery, tDS, "spcards");
if (tDS.Tables.Contains("spcards"))
{
CF.Table2CSV(tDS.Tables["spcards"], $"{CF.DataFolder}altasp.csv", true);
CF.Table2CSV(tDS.Tables["spcards"], $"{CF.ITDataFolder}altasp.csv", true);
}
}
}
public bool GetLiftopiaMTCFile(DateTime?StartDate = null, DateTime?EndDate = null)
{
CurrentFunction = "GetLiftopiaMTCFile";
statusStrip1.Items[0].Text = "Preparing Liftopia MTC Import...";
SetTaskStatus();
ServicePointManager.SecurityProtocol = SecurityProtocolType.Tls12 | SecurityProtocolType.Tls11;
if (StartDate != null)
{
MTCURL += @"&orderDateGreaterThan=" + StartDate.Value.AddDays(-1).ToString(Mirror.AxessDateFormat);
}
if (EndDate != null)
{
if (EndDate >= StartDate)
{
MTCURL += @"&orderDateLessThan=" + EndDate.Value.AddDays(1).ToString(Mirror.AxessDateFormat);
}
}
WebRequest request = WebRequest.Create(MTCURL);
request.Method = "POST";
request.ContentType = "application/x-www-form-urlencoded";
WebResponse response = request.GetResponse();
StreamReader reader = new StreamReader(response.GetResponseStream());
File.WriteAllText(MTCDataPath, reader.ReadToEnd().Replace("\0", string.Empty));
response.Close();
response.Dispose();
reader.Close();
reader.Dispose();
statusStrip1.Items[0].Text = "Updating MTC_Sales...";
SetTaskStatus();
CommonFunctions cf = new CommonFunctions();
DataWarehouse dw = new DataWarehouse();
DataSet myDS = cf.CSV2DS(MTCDataPath, MTCDataFile, new DataSet());
int tRowCount = myDS.Tables[MTCDataFile].Rows.Count;
pbMTC.Maximum = tRowCount;
foreach (DataRow myRow in myDS.Tables[MTCDataFile].Rows)
{
pbMTC.Value = myDS.Tables[MTCDataFile].Rows.IndexOf(myRow) + 1;
System.Diagnostics.Debug.Print(pbMTC.Value.ToString() + " of " + myDS.Tables[MTCDataFile].Rows.Count.ToString());
//if (myRow["Order Id"].ToString() == "11490976")
//if (pbMTC.Value > 59999 || myRow.Field<string>("pre arrival approved").ToUpper() == "YES" || myRow.Field<string>("order status").ToUpper() == "CANCELLED") // || myRow["estimated arrival date"].ToString() != string.Empty)
{
//SetTaskStatus("", "", pbMTC);
string GFN = myRow["guest first name"].ToString().Replace("’", "'").Replace("é", "e").Replace("ä", "a").Replace("/", "").Replace(@"&", "").Trim();
string GLN = myRow["guest last name"].ToString().Replace("’", "'").Replace("é", "e").Replace("ä", "a").Replace("/", "").Trim();
string GEM = myRow["guest email"].ToString();
string GN = GFN + " " + GLN;
string dob = myRow["guest birth date"].ToString();
bool legalName = (myRow["guest last name"].ToString().ToUpper().Trim() != "INVALID");
foreach (char a in GN)
{
legalName |= ((int)a <= 127);
}
if (myRow["product name"].ToString().Contains(cf.Season.Replace("-", "/")) && legalName)
{
//if (myRow.Field<int>("recid") > 572739)
{
if (Buy.SalesFromMTC(myRow)) //test for row existing and either update or insert
{
DataSet tDS = CF.LoadDataSet(AM.MirrorConn, $"SELECT * FROM axess_cwc.tabpersonen WHERE SZNAME = '{CF.EscapeChar(GLN)}' AND dtgeburt = '{dob}' AND nperskassanr <= 40", new DataSet(), "tabPersonen");
if (tDS.Tables.Contains("tabPersonen"))
{
if (tDS.Tables["tabPersonen"].Rows.Count != 0)
{
int FoundRow = 0;
bool IsFound = (tDS.Tables["tabPersonen"].Rows.Count == 1);
if (!IsFound)
{
foreach (DataRow Personen in tDS.Tables["tabPersonen"].Rows)
{
if (Personen["szvorname"].ToString().ToUpper() == GFN.ToUpper())
{
FoundRow = tDS.Tables["tabPersonen"].Rows.IndexOf(Personen);
IsFound = true;
break;
}
}
}
if (IsFound)
{
int nPersNr = tDS.Tables["tabPersonen"].Rows[FoundRow].Field <int>("nPersNr");
int nKassaNr = tDS.Tables["tabPersonen"].Rows[FoundRow].Field <Int16>("nPersKassaNr");
if (nKassaNr <= 40 || (nKassaNr >= 117 && nKassaNr <= 120))
{
cf.ExecuteSQL(Buy.Buy_Alta_ComConn, $"UPDATE asbshared.mtncol_sales SET axess_perskassa={nKassaNr.ToString()}, axess_persnr={nPersNr.ToString()} WHERE mcpnbr = '{myRow.Field<string>("Barcode")}'");
}
}
}
}
//dw.WillcallFromMTC(myRow);
}
}
}
}
}
return(true);
}
public bool GetLiftopiaAltaFile()
{
CurrentFunction = "GetLiftopiaAltaFile";
statusStrip1.Items[0].Text = "Preparing Liftopia Alta Import...";
SetTaskStatus();
WebRequest request = WebRequest.Create(AltaURL);
request.Method = "POST";
request.ContentType = "application/x-www-form-urlencoded";
WebResponse response = request.GetResponse();
StreamReader reader = new StreamReader(response.GetResponseStream());
File.WriteAllText(AltaDataPath, reader.ReadToEnd().Replace("\0", string.Empty));
response.Close();
reader.Close();
statusStrip1.Items[0].Text = "Updating Willcall...";
SetTaskStatus();
string tQuery = string.Empty;
DataSet tDS = CF.CSV2DS(AltaDataPath, AltaDataFile, new DataSet());
int tRowCount = tDS.Tables[AltaDataFile].Rows.Count;
pbAlta.Maximum = tRowCount;
DataTable Willcall = CF.LoadTable(DW.dwConn, $"SELECT res_no, itemstatus, arr_date FROM {DW.ActiveDatabase}.willcall", "Willcall");
DataColumn[] WillcallKey = new DataColumn[1];
WillcallKey[0] = Willcall.Columns["res_no"];
Willcall.PrimaryKey = WillcallKey;
foreach (DataRow tRow in tDS.Tables[AltaDataFile].Rows)
{
tQuery = string.Empty;
pbAlta.Value = tDS.Tables[AltaDataFile].Rows.IndexOf(tRow) + 1;
SetTaskStatus("", "", pbAlta);
if (tRow["guest_first_name"].ToString().Trim() != "ADULT 1" && tRow["guest_last_name"].ToString().Trim() != "ADULT 1 " && tRow["net_rate_revenue"].ToString() != string.Empty)
{
bool isPaid = (tRow["order_status"].ToString().ToUpper() == "PAID");
DateTime TripDate = Convert.ToDateTime(tRow["trip_date"].ToString());
string TickID = tRow["ticket_id"].ToString();
//DataRow oldRow = CF.LoadDataRow(DW.dwConn, $"SELECT itemstatus, arr_date FROM {DW.ActiveDatabase}.willcall WHERE res_no='{TickID}' LIMIT 1");
DataRow oldRow = Willcall.Rows.Find(TickID);
if (CF.RowHasData(oldRow))
{
if (!isPaid)
{
string oldStatus = oldRow["itemstatus"].ToString().Trim();
if (oldStatus == "U")
{
tQuery += "itemstatus='C'";
}
else
{
if (oldStatus != "C")
{
myError.UpdateErrorLog("ExternalSources", -668, $"Res_No={TickID} is trying to cancel an issued or refunded ticket.", "GetLiftopiaAltaFile");
}
}
}
if (oldRow.Field <DateTime>("arr_date") != TripDate)
{
tQuery += $"{(tQuery.Length != 0 ? ", " : "")}arr_date='{TripDate.ToString(Mirror.AxessDateFormat)}'";
}
if (tQuery.Length != 0)
{
tQuery = $"UPDATE {DW.ActiveDatabase}.willcall SET {tQuery} WHERE res_no='{TickID}'";
}
}
else
{
tDS = CF.LoadDataSet(DW.dwConn, $"SELECT nticktype, nperstype FROM {DW.ActiveDatabase}.tickpersxref WHERE extsource='LIFTOPIA_ALTA' AND extcode='{tRow["product"].ToString().Substring(0, 6)}{tRow["ticket_type"].ToString().Substring(0, 1)}'", tDS, "tickpersxref");
if (tDS.Tables.Contains("tickpersxref"))
{
string nperstypenr = tDS.Tables["tickpersxref"].Rows[0].Field <int>("nperstype").ToString();
string nticktypenr = tDS.Tables["tickpersxref"].Rows[0].Field <int>("nticktype").ToString();
string nPersType = CF.GetSQLField(AM.MirrorConn, $"SELECT szname FROM axess_cwc.tabperstypdef WHERE nperstypnr='{nperstypenr}'").ToUpper();
string nTickType = CF.GetSQLField(AM.MirrorConn, $"SELECT szname FROM axess_cwc.tabkundenkartentypdef WHERE nkundenkartentypnr='{nticktypenr}'").ToUpper();
tQuery = $"INSERT INTO {DW.ActiveDatabase}.willcall (res_no, arr_date, tickcode, tickdesc, persdesc, firstname, lastname, qty, uses, resprice, axtotal, itemstatus, company, bookdate, arcustid, arname, purfname, purlname, puremail, purstreet, purcity, purstate, purzip, ordernbr) VALUES ('";
tQuery += $"{tRow["ticket_id"].ToString()}','{Convert.ToDateTime(tRow["trip_date"].ToString()).ToString(Mirror.AxessDateFormat)}','NEW','{nTickType}','{nPersType}','";
tQuery += $"{CF.EscapeChar(tRow["guest_first_name"].ToString().ToUpper())}','{CF.EscapeChar(tRow["guest_last_name"].ToString().ToUpper())}',1,0,{Convert.ToDouble(tRow["net_rate_revenue"].ToString())},{Convert.ToDouble(tRow["net_rate_revenue"].ToString())},'";
tQuery += $"{(isPaid ? "U" : "C")}','LT','{Convert.ToDateTime(tRow["order_date"].ToString()).ToString(Mirror.AxessDateFormat)}',11733,'LIFTOPIA RESERVATIONS','";
tQuery += $"{CF.EscapeChar(tRow["purchaser_first_name"].ToString().ToUpper())}','{CF.EscapeChar(tRow["purchaser_last_name"].ToString().ToUpper())}','{tRow["purchaser_email"].ToString()}','{CF.EscapeChar(tRow["purchaser_address"].ToString())}','";
tQuery += $"{CF.EscapeChar(tRow["purchaser_city"].ToString().ToUpper())}','{tRow["purchaser_state"].ToString().ToUpper()}','{tRow["purchaser_zip"].ToString()}','{tRow["order_id"].ToString()}')";
tDS.Tables.Remove("tickpersxref");
}
}
}
if (tQuery.Length != 0)
{
if (!CF.ExecuteSQL(DW.dwConn, tQuery))
{
return(false);
}
}
}
statusStrip1.Items[0].Text = string.Empty;
return(true);
}
public bool UpdateWasatchBenefit()
{
CurrentFunction = "updateWasatchBenefit";
for (Int32 I = 0; I <= WB_Resorts.GetUpperBound(0); I++)
{
string tResortName = WB_Resorts[I, 0].ToUpper();
string tTableName = $"WB_{tResortName}";
string tResortFilter = WB_Resorts[I, 1];
string tWBEnd = WBDateEnd.ToString(Mirror.AxessDateFormat);
string tSeason = CF.SeasonShort;
//string TickDesc = "Wasatch Benefit";
string PersDesc = string.Empty;
string MediaType = (tResortFilter == "20" ? "AB SB Upgrade" : "One Use");
decimal Price = 0;
CF.ExecuteSQL(DW.dwConn, $"UPDATE {DW.ActiveDatabase}.clistperm SET okayflag=0 WHERE passcat='WB' AND company='{tResortName}'");
string tFileName = $"{CF.DataFolder}{tResortName.Replace(" ", "")}.csv";
File.Copy(tFileName, $"{CF.ITDataFolder}{tResortName.Replace(" ", "")}.csv", true);
DataSet tDS2 = CF.CSV2DS(tFileName, tTableName, new DataSet());
if (tDS2.Tables.Contains(tTableName))
{
tDS2.Tables[tTableName].Columns[0].ColumnName = "ResortID";
tDS2.Tables[tTableName].Columns[1].ColumnName = "PersKey";
tDS2.Tables[tTableName].Columns[2].ColumnName = "FirstName";
tDS2.Tables[tTableName].Columns[3].ColumnName = "LastName";
tDS2.Tables[tTableName].Columns[4].ColumnName = "City";
tDS2.Tables[tTableName].Columns[5].ColumnName = "Age";
tDS2.Tables[tTableName].Columns[6].ColumnName = "PassNbr";
tDS2.Tables[tTableName].Columns[7].ColumnName = "Barcode";
tDS2.Tables[tTableName].Columns[8].ColumnName = "Status";
tDS2.Tables[tTableName].Columns.Add("Uses");
tDS2.Tables[tTableName].Columns.Add("Resortname");
tDS2.Tables[tTableName].Columns.Add("dob");
tDS2.Tables[tTableName].Columns.Add("nKartenNr");
tDS2.Tables[tTableName].Columns.Add("cStat");
tDS2.Tables[tTableName].Columns.Add("PersKassa");
tDS2.Tables[tTableName].Columns.Add("PersNr");
tDS2.Tables[tTableName].Columns.Add("MtnFlag");
tDS2.Tables[tTableName].Columns.Add("WasBenFlag");
tDS2.Tables[tTableName].Columns.Add("MediaID");
tDS2.Tables[tTableName].Columns.Add("wtp64");
tDS2.Tables[tTableName].Columns.Add("CardStatus");
foreach (DataRow tRow in tDS2.Tables[tTableName].Rows)
{
System.Diagnostics.Debug.Print($"{tDS2.Tables[tTableName].Rows.IndexOf(tRow).ToString()} of {tDS2.Tables[tTableName].Rows.Count}");
string tFName = tRow["FirstName"].ToString();
string tLName = tRow["LastName"].ToString().ToString();
if (((tRow["Perskey"] != null) || (tRow["ResortID"].ToString().Trim() != tResortFilter)) && (tFName.Length != 0) && (tLName.Length != 0))
{
string tPassKey = $"{tRow["ResortID"]}-{tRow["PersKey"]}";
if (CF.RowExists(DW.dwConn, $"{DW.ActiveDatabase}.clistperm", $"passkey='{tPassKey}'")) //update or insert?
{ //update
if (tRow["status"].ToString() != "1")
{
CF.ExecuteSQL(DW.dwConn, $"UPDATE {DW.ActiveDatabase}.clistperm SET okayflag=false WHERE company='{tResortName}'", 300);
}
}
else
{ //insert
Int32 tAge = tRow.Field <Int32?>("age") == null ? 0 : tRow.Field <Int32>("age");
PersDesc = $"WB {((tAge <= 12) && (tAge > 0) ? "CHILD" : "ADULT")}";
if (tResortFilter == "30")
{
PersDesc = $"DV {PersDesc}";
}
string tQuery2 = $"INSERT INTO {DW.ActiveDatabase}.clistperm (dexpires,season,lname,fname,category,reason,passkey,passcat,dent,passtype,tickets,barcode,passnbr,tickdesc,media,appby,dept,okayflag,company,maxuses,price,tickpers,uses,extuses) VALUES ('";
tQuery2 += $"{tWBEnd}','{tSeason}','{CF.EscapeChar(tRow["LastName"].ToString().ToUpper().Trim())}','{CF.EscapeChar(tRow["FirstName"].ToString().ToUpper().Trim())}','{tResortName}','WASATCH BENEFIT','{tPassKey}','WB','";
tQuery2 += $"{DateTime.Now.ToString(Mirror.AxessDateFormat)}','AD',2,'{tRow["barcode"].ToString().Trim()}','{tRow["PassNbr"].ToString().Trim()}','Wasatch Benefit','{MediaType}','WASATCH BENEFIT','ADMIN',True,'";
tQuery2 += $"{tResortName}',2,{Price.ToString("#0.00")},'{PersDesc}',0,0)";
CF.ExecuteSQL(DW.dwConn, tQuery2);
}
}
}
tDS2.Tables.Remove(tTableName);
}
}
CF.ExecuteSQL(DW.dwConn, $"UPDATE {DW.ActiveDatabase}.clistperm AS a INNER JOIN (SELECT COUNT(*) AS tt, passkey FROM {DW.ActiveDatabase}.clist WHERE tickdesc = 'WASATCH BENEFIT' GROUP BY passkey) b ON a.passkey = b.passkey SET uses = b.tt WHERE a.reason = 'WASATCH BENEFIT'");
string tQuery = $"SELECT 10 AS resortid, perskey, UPPER(firstname) AS firstname, UPPER(replace(lastname,',','')) AS lastname, UPPER(replace(city,',','')) AS city, CAST((DATEDIFF(now(), dob) / 365) AS signed) AS age, mediaid, wtp64, 1 AS status FROM {DW.ActiveDatabase}.spcards WHERE wasbenflag = 'Y' AND cardstatus='A' AND testflag=false";
string tFileName2 = @"\\taskman\c$\acctg\winscp\data\alta.csv";
DataSet tDS = CF.LoadDataSet(DW.dwConn, tQuery, new DataSet(), "spcards");
if (tDS.Tables.Contains("spcards"))
{
CF.Table2CSV(tDS.Tables["spcards"], tFileName2, true);
CF.Table2CSV(tDS.Tables["spcards"], CF.ITDataFolder + "alta.csv", true);
}
return(true);
}