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