public bool MTCSPUpload() //update MtnCol Passholder table using ClistPerm
{
CurrentFunction = "MTCSPUpload";
//remove all Alta passholders from MtnCol passholder table.
statusStrip1.Items[0].Text = "Preparing database";
SetTaskStatus("", "", pbMTCUpload);
CF.ExecuteSQL(new SqlConnection(MCReciprocityConnectionString), $"DELETE FROM premiumpass.dbo.passholder WHERE resortid={AltaResortID.ToString()}", 120);
//load all SPCards with a WTP64 value, testflag set to false, cardstatus is active and mtnrepflag = "Y".
statusStrip1.Items[0].Text = "Loading records from Alta...";
SetTaskStatus("", "", pbMTCUpload);
DataSet tDS = CF.LoadDataSet(DW.dwConn, $"SELECT * FROM {DW.ActiveDatabase}.spcards WHERE mtnrepflag='Y' AND cardstatus='A' AND testflag=false AND LENGTH(wtp64)<>0", new DataSet(), "spcards");
//loop through Alta passholders and insert into MtnCol passholder table
pbMTCUpload.Maximum = tDS.Tables["spcards"].Rows.Count;
foreach (DataRow tRow in tDS.Tables["spcards"].Rows)
{
pbMTCUpload.Value = tDS.Tables["spcards"].Rows.IndexOf(tRow) + 1;
statusStrip1.Items[0].Text = "Uploading records to MTC...";
SetTaskStatus("", "", pbMTCUpload);
int age = (tRow.Field <DateTime>("dob") == null ? 0 : CF.CalcAge(tRow.Field <DateTime>("dob")));
string tSQL = "INSERT INTO PremiumPass.dbo.PASSHOLDER(resortid, firstname, lastname, city, state, age, passnumber, customerid) values ";
tSQL += $"({AltaResortID}, '{CF.EscapeChar(tRow.Field<string>("firstname")).ToUpper().Trim()}','{CF.EscapeChar(tRow.Field<string>("lastname")).ToUpper().Trim()}',";
tSQL += $"'{tRow.Field<string>("city").Replace(",", "")}','{tRow.Field<string>("state")}',{age.ToString()},'{tRow.Field<string>("wtp64")}','{tRow.Field<string>("perskey")}')";
if (!CF.ExecuteSQL(new SqlConnection(MCReciprocityConnectionString), tSQL))
{
//cannot insert, currently do nothing but in the future create an email report for all processing issues.
}
}
return(true);
}
public void UpdateASBShared()
{
using (DataTable tDS = CF.LoadTable(DW.dwConn, $"SELECT S.recid, S.serialkey, S.cardstatus, S.dob, S.firstname, S.lastname, S.city, S.saledate, S.expdate, S.wtp64 FROM {DW.ActiveDatabase}.spcards AS S INNER JOIN applications.salesdata AS D ON D.SERIALKEY=S.serialkey WHERE D.NPOOLNR IN (91, 92, 96, 98) AND NOT (S.wtp32 is null) AND NOT (S.lastname = 'TEST' OR S.firstname = 'TEST') AND S.saledate > '2018-04-23' GROUP BY S.recid", "spcards"))
{
pbUpdateASBShared.Maximum = 100;
foreach (DataRow tRow in tDS.Rows)
{
//((BackgroundWorker)sender).ReportProgress(tDS.Tables["spcards"].Rows.IndexOf(tRow), "Updating asb_passholders");
pbUpdateASBShared.Value = tDS.Rows.IndexOf(tRow) * 100 / tDS.Rows.Count;
statusStrip1.Items[0].Text = $"{tDS.Rows.IndexOf(tRow).ToString()} of {tDS.Rows.Count.ToString()}";
string tSQL = string.Empty;
string mkey = tRow.Field <string>("serialkey");
if (CF.RowExists(BUY.Buy_Alta_ComConn, "asbshared.asb_passholders", $"issued_by='ALTA' AND serialkey='{mkey}'"))
{
tSQL = $"UPDATE asbshared.asb_passholders SET pass_status='{tRow.Field<string>("cardstatus")}' WHERE recid={tRow.Field<int>("recid").ToString()}";
}
else
{
string mkassanr = mkey.Substring(0, mkey.IndexOf("-"));
string mserialnr = mkey.Substring(mkey.IndexOf("-") + 1);
string municodenr = mserialnr.Substring(mserialnr.IndexOf("-") + 1);
mserialnr = mserialnr.Replace("-" + municodenr, "");
int tAge = (tRow.Field <DateTime?>("dob") == null ? 0 : CF.CalcAge(tRow.Field <DateTime>("dob")));
tSQL = "INSERT asbshared.asb_passholders (serialkey,nkassanr,nserialnr,nunicodenr,firstname,lastname,city,age,pass_status,issue_date,exp_date,wtp64,issued_by)";
tSQL += $" Values ('{mkey}','{mkassanr}','{mserialnr}','{municodenr}','{CF.EscapeChar(tRow.Field<string>("firstname"))}','";
tSQL += $"{CF.EscapeChar(tRow.Field<string>("lastname"))}','{tRow.Field<string>("city").Trim()}','{tAge.ToString()}','{tRow.Field<string>("cardstatus")}','";
tSQL += $"{tRow.Field<DateTime>("saledate").ToString(Mirror.AxessDateTimeFormat)}','{tRow.Field<DateTime>("expdate").ToString(Mirror.AxessDateFormat)}','{tRow.Field<string>("wtp64")}','ALTA')";
}
CF.ExecuteSQL(BUY.Buy_Alta_ComConn, tSQL);
}
}
return;
//pbUpdateASBShared.Maximum = 2000;
//var bgw = new BackgroundWorker();
//bgw.ProgressChanged += UpdateASBShared_ProgressChanged;
//bgw.DoWork += UpdateASBSharedBG;
//bgw.WorkerReportsProgress = true;
//bgw.RunWorkerAsync();
}
//public bool UpdateWillcallFromIssued()
//{
// return false;
//}
public void FixWillcall()
{
string tQuery = $"SELECT * FROM {ActiveDatabase}.willcall WHERE persdesc LIKE 'MTC%'";
DataSet tDS = CF.LoadDataSet(dwConn, tQuery, new DataSet(), "willcall");
foreach (DataRow tRow in tDS.Tables["willcall"].Rows)
{
int tAge = CF.CalcAge(tRow.Field <DateTime>("DOB"));
string tVal = "MTC " + ((tAge <= 12) && (tAge != 0) ? "CHILD" : "ADULT");
if (tRow["persdesc"].ToString() != tVal)
{
CF.ExecuteSQL(dwConn, "UPDATE " + ActiveDatabase + ".willcall SET persdesc='" + tVal + "' WHERE recid=" + tRow["recid"].ToString());
System.Diagnostics.Debug.Print(tDS.Tables["willcall"].Rows.IndexOf(tRow).ToString() + " of " + tDS.Tables["willcall"].Rows.Count.ToString());
}
}
}