//id, associatedTokenId, customerPaymentProfileId, orderId, projNr, posNr, serialNr, nunicodenr, authnet_custid, activeflag, journalNr, workPos, lastUpdated, firstname, lastname, dateInserted, webId, mediaID, WTP64 public void MirrorTokenXRef() { DateTime StartMirror = DateTime.Now; Cursor OldCursor = Cursor.Current; Cursor.Current = Cursors.WaitCursor; //update Applications.TokenXref from AxessUtility.TokenXRef DataRow LastUpdate = CF.LoadDataRow(DW.dwConn, $"SELECT MAX(lastUpdated) FROM {DW.ActiveDatabase}.tokenxref"); string UpdateWhere = string.Empty; if (CF.RowHasData(LastUpdate)) { UpdateWhere = $"WHERE lastUpdated > '{LastUpdate.Field<DateTime>(0).ToString(Mirror.AxessDateTimeFormat)}'"; } using (DataTable AUTokenXRef = CF.LoadTable(BUY.Buy_Alta_ComConn, $"SELECT * FROM axessutility.tokenxref {UpdateWhere} ORDER BY id", "AUTokenXRef")) { pbSyncTokenFiles.Maximum = AUTokenXRef.Rows.Count; foreach (DataRow ATR in AUTokenXRef.Rows) { pbSyncTokenFiles.Value = AUTokenXRef.Rows.IndexOf(ATR); SslStatus.Text = $"Mirroring TokenXRef {pbSyncTokenFiles.Value + 1} of {pbSyncTokenFiles.Maximum}"; Refresh(); Int32 ID = ATR.Field <Int32>("id"); DateTime lastUpdated = ATR.Field <DateTime>("lastUpdated"); string LastUpdatedS = lastUpdated.ToString(Mirror.AxessDateTimeFormat); DateTime dateInserted = ATR.Field <DateTime>("dateInserted"); string DateInsertedS = dateInserted.ToString(Mirror.AxessDateTimeFormat); string UnicodeNrS = ATR["nunicodenr"].ToString().Trim(); if (UnicodeNrS == string.Empty) { UnicodeNrS = "null"; } //lblSyncTokenFiles.Text = "Pass 1 of 2"; DataRow AppTokenRow = CF.LoadDataRow(DW.dwConn, $"SELECT * FROM {DW.ActiveDatabase}.TokenXRef WHERE id={ID}"); if (!CF.RowHasData(AppTokenRow)) //Row not in Applications so insert and check for a salesdata match based on SerialKey { string TQ = $"INSERT INTO {DW.ActiveDatabase}.TokenXRef (id, associatedTokenId, customerPaymentProfileId, orderId, projNr, posNr," + $" serialNr, nunicodenr, authnet_custid, activeflag, journalNr, workPos," + $" lastUpdated, firstname, lastname, dateInserted, webId, mediaID, WTP64) VALUES (" + $"{ID}, {ATR["associatedTokenId"].ToString()}, {ATR["customerPaymentProfileId"].ToString()}, {ATR["orderId"].ToString()}, {ATR["projNr"].ToString()}, {ATR["posNr"].ToString()}, " + $"{ATR["serialNr"].ToString()}, {UnicodeNrS}, {ATR["authnet_custid"].ToString()}, {ATR["activeflag"].ToString()}, {ATR["journalNr"].ToString()}, {ATR["workPos"].ToString()}, " + $"'{LastUpdatedS}', '{CF.EscapeChar(ATR["firstname"].ToString().Trim())}', '{CF.EscapeChar(ATR["lastname"].ToString().Trim())}', '{DateInsertedS}', '{ATR["webId"].ToString().Trim()}', '{ATR["mediaID"].ToString().Trim()}', '{ATR["WTP64"].ToString().Trim()}')"; CF.ExecuteSQL(DW.dwConn, TQ); } else { if (ATR.Field <DateTime>("lastUpdated") != AppTokenRow.Field <DateTime>("lastUpdated")) { string TQ = $"UPDATE {DW.ActiveDatabase}.tokenxref SET associatedTokenId = {ATR["associatedTokenId"].ToString()}, customerPaymentProfileId = {ATR["customerPaymentProfileId"].ToString()}, " + $"orderId = {ATR["orderId"].ToString()}, projNr = {ATR["projNr"].ToString()}, posNr = {ATR["posNr"].ToString()}, serialNr = {ATR["serialNr"].ToString()}, nunicodenr = {UnicodeNrS}, " + $"authnet_custid = {ATR["authnet_custid"].ToString()}, activeflag = {ATR["activeflag"].ToString()}, journalNr = {ATR["journalNr"].ToString()}, workPos = {ATR["workPos"].ToString()}, " + $"lastUpdated = '{LastUpdatedS}', firstname = '{CF.EscapeChar(ATR["firstname"].ToString().Trim())}', lastname = '{CF.EscapeChar(ATR["lastname"].ToString().Trim())}', dateInserted = '{DateInsertedS}', webId = '{ATR["webId"].ToString().Trim()}', " + $"mediaID = '{ATR["mediaID"].ToString().Trim()}', WTP64 = '{ATR["WTP64"].ToString().Trim()}'"; CF.ExecuteSQL(DW.dwConn, TQ); } } } if (CF.RowCount(BUY.Buy_Alta_ComConn, $"axessutility.tokenxref") < CF.RowCount(DW.dwConn, $"{DW.ActiveDatabase}.tokenxref")) { using (DataTable AUTokenXRef2 = CF.LoadTable(BUY.Buy_Alta_ComConn, $"SELECT id FROM axessutility.tokenxref order by id", "AUTokenXRef")) { AUTokenXRef2.PrimaryKey = new DataColumn[1] { AUTokenXRef2.Columns["id"] }; DataTable AppTokenXRef = CF.LoadTable(DW.dwConn, $"SELECT id FROM {DW.ActiveDatabase}.tokenxref", "AppTokenXRef"); if (CF.TableHasData(AppTokenXRef)) { foreach (DataRow AppRow in AppTokenXRef.Rows) { SslStatus.Text = $"{AppTokenXRef.Rows.IndexOf(AppRow)} of {AppTokenXRef.Rows.Count}"; Refresh(); if (!CF.RowHasData(AUTokenXRef2.Rows.Find(AppRow.Field <Int32>("id")))) { CF.ExecuteSQL(DW.dwConn, $"DELETE FROM {DW.ActiveDatabase}.tokenxref WHERE id={AppRow["id"].ToString()}"); } } } } } } TimeSpan ts = TimeSpan.FromTicks(DateTime.Now.Ticks - StartMirror.Ticks); lblSyncTokenFiles.Text = string.Format($"{ts.Hours.ToString("0#")}:{ts.Minutes.ToString("0#")}:{ts.Seconds.ToString("0#")}"); lblSyncTokenFiles.Visible = true; }