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); }