FCC/Applications/SpsDataCourier/SpsDataCourierApp.cs

using Dfs.WayneChina.SpsDataCourier.Guard;
using Dfs.WayneChina.SpsDataCourier.Models;
using Dfs.WayneChina.SpsDataCourier.SpsData;
using Edge.Core.Processor;
using Edge.Core.Processor.Dispatcher.Attributes;
using Microsoft.EntityFrameworkCore;
using NLog.LayoutRenderers;
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using System.Threading;
using System.Threading.Tasks;

namespace Dfs.WayneChina.SpsDataCourier
{
    /// <summary>
    /// Application to upload account/card/transaction/card recharge and reductions.
    /// </summary>
    [MetaPartsDescriptor(
    "lang-zh-cn:Sps传输服务 Applang-en-us:Sps Data Courier",
    "lang-zh-cn:用于传输SPS数据库中的数据到远端服务器" +
    "lang-en-us:Used for sending SPS data to remote host",
        new[] { "lang-zh-cn:数据上传服务lang-en-us:Data Upload Service" })]
    public class SpsDataCourierApp : IAppProcessor
    {
        #region Fields

        public string SerialNumber { get; set; }
        public string Name { get; set; }
        private string username;
        private string password;
        private string authServiceBaseUrl;
        private string accountServiceBaseUrl;
        private string accountServiceRelativeUrl;
        private string deviceSN;

        public int Id { get; }
        public object OfflineAccount { get; private set; }
        public string MetaConfigName { get; set; }

        private DbMonitor dbMonitor;
        private DataCourier dataCourier;
        private int scanInterval;
        private string fuelMapping;
        private bool smartFuel05Compatible;
        private int retryCount;
        private bool enableSync;
        private bool excludeCurrentSite;
        private int syncInterval;
        private string checkVersionRelativeUrl;
        private string syncDataRelativeUrl;

        private ManualResetEvent mre = new ManualResetEvent(false);

        private Dictionary<int, int> fuelMappingDict = new Dictionary<int, int>();

        private Downloader downloader;
        private readonly ConnectionInfo connectionInfo;
        #endregion

        #region Logger

        static NLog.Logger logger = NLog.LogManager.LoadConfiguration("NLog.config").GetLogger("SpsDataCourier");

        #endregion

        private string _mySqlConn;

        #region Constructor

        [ParamsJsonSchemas("appCtorParamsJsonSchema")]
        public SpsDataCourierApp(DataCourierAppContructorParameterV1 config)
        {
            this.Id = config.Id;
            this.username = config.Username;
            this.password = config.Password;
            this.authServiceBaseUrl = config.AuthServiceBaseUrl;
            this.accountServiceBaseUrl = config.AccountServiceBaseUrl;
            this.accountServiceRelativeUrl = config.AccountServiceRelativeUrl;

            this.deviceSN = config.DeviceSN;
            this.scanInterval = config.ScanInterval;
            this.fuelMapping = string.Join(";", config.FuelMappingArr.Select(m => $"{m.Barcode}:{m.FuelNo}"));
            this.retryCount = config.RetryCount;
            this.enableSync = config.EnableSync;
            this.excludeCurrentSite = config.ExcludeCurrentSite;
            this.syncInterval = config.SyncInterval;
            this.checkVersionRelativeUrl = config.CheckVersionRelativeUrl;
            this.syncDataRelativeUrl = config.SyncDataRelativeUrl;

            _mySqlConn = FormatConnectionString(config.ConnectionString);
            SetFuelProductMapping();

            connectionInfo = new ConnectionInfo
            {
                AccountServiceBaseUrl = accountServiceBaseUrl,
                AccountServiceRelativeUrl = accountServiceRelativeUrl,
                AuthServiceBaseUrl = authServiceBaseUrl,
                DeviceSN = deviceSN,
                UserName = username,
                Password = password
            };
        }

        private string FormatConnectionString(SpsDbConnectionSetting spsDbConnectionSetting)
        {
            return $"server={spsDbConnectionSetting.Server};port={spsDbConnectionSetting.Port};uid={spsDbConnectionSetting.Username};password={spsDbConnectionSetting.Password};database=sps_db;TreatTinyAsBoolean=false;Convert Zero Datetime=true;";
        }

        public SpsDataCourierApp(int id, string username, string password, string authServiceBaseUrl, string accountServiceBaseUrl,
            string accountServiceRelativeUrl, string deviceSN, int scanInterval, string fuelMapping, string smartFuel05Compatible,
            int retryCount, int enableSync, int excludeCurrentSite, int syncInterval, string checkVersionRelativeUrl, string syncDataRelativeUrl, string connectionString)
        {
            Id = id;
            this.username = username;
            this.password = password;
            this.authServiceBaseUrl = authServiceBaseUrl;
            this.accountServiceBaseUrl = accountServiceBaseUrl;
            this.accountServiceRelativeUrl = accountServiceRelativeUrl;
            this.deviceSN = deviceSN;
            this.scanInterval = scanInterval;
            this.fuelMapping = fuelMapping;
            this.smartFuel05Compatible = Convert.ToBoolean(smartFuel05Compatible);
            this.retryCount = retryCount;
            this.enableSync = enableSync > 0 ? true : false;
            this.excludeCurrentSite = excludeCurrentSite > 0 ? true : false;
            this.syncInterval = syncInterval;
            this.checkVersionRelativeUrl = checkVersionRelativeUrl;
            this.syncDataRelativeUrl = syncDataRelativeUrl;

            _mySqlConn = connectionString;
            SetFuelProductMapping();

            connectionInfo = new ConnectionInfo
            {
                AccountServiceBaseUrl = accountServiceBaseUrl,
                AccountServiceRelativeUrl = accountServiceRelativeUrl,
                AuthServiceBaseUrl = authServiceBaseUrl,
                DeviceSN = deviceSN,
                UserName = username,
                Password = password
            };
        }

        #endregion

        #region Fuel barcode and fuel number

        private void SetFuelProductMapping()
        {
            if (!string.IsNullOrEmpty(fuelMapping))
            {
                var sequence = fuelMapping.Split(';')
                    .Select(s => s.Split(':'))
                    .Select(a => new { Barcode = int.Parse(a[0]), FuelNo = int.Parse(a[1]) });

                foreach (var pair in sequence)
                {
                    if (!fuelMappingDict.ContainsKey(pair.Barcode))
                    {
                        fuelMappingDict.Add(pair.Barcode, pair.FuelNo);
                    }
                }
            }
        }

        private int GetFuelNo(int barcode)
        {
            if (fuelMappingDict.ContainsKey(barcode))
                return fuelMappingDict[barcode];

            return -1;
        }

        private int GetBarcode(int fuelNo)
        {
            return fuelMappingDict.FirstOrDefault(x => x.Value == fuelNo).Key;
        }

        #endregion

        #region IApplication implementation, Init

        public void Init(IEnumerable<IProcessor> processors)
        {
            dbMonitor = new DbMonitor(scanInterval, _mySqlConn, smartFuel05Compatible);
            dataCourier =
                new DataCourier(username, password, authServiceBaseUrl, accountServiceBaseUrl, accountServiceRelativeUrl, deviceSN, retryCount);

            if (enableSync)
            {
                downloader = new Downloader(connectionInfo, dbMonitor, _mySqlConn, syncInterval, checkVersionRelativeUrl, 
                    syncDataRelativeUrl, excludeCurrentSite);
            }
        }

        #endregion

        #region Start

        public Task<bool> Start()
        {
            // Hook up the events.
            dbMonitor.OnCardTrxCreated += DbMonitor_OnCardTrxCreated;

            dbMonitor.OnRechargeCreated += DbMonitor_OnRechargeCreated;

            dbMonitor.OnAccountCreated += DbMonitor_OnAccountCreated;
            dbMonitor.OnAccountUpdated += DbMonitor_OnAccountUpdated;

            dbMonitor.OnCardInfoCreated += DbMonitor_OnCardInfoCreated;
            dbMonitor.OnCardInfoUpdated += DbMonitor_OnCardInfoUpdated;
            dbMonitor.OnCardInfoDeleted += DbMonitor_OnCardInfoDeleted;

            // cardreploss
            dbMonitor.OnLostCardCreated += DbMonitor_OnLostCardCreated;

            // cardlogout
            dbMonitor.OnClosedCardCreated += DbMonitor_OnClosedCardCreated;

            // base black card
            dbMonitor.OnBaseBlackCardCreated += DbMonitor_OnBaseBlackCardCreated;
            dbMonitor.OnBaseBlackCardDeleted += DbMonitor_OnBaseBlackCardDeleted;

            // add black card
            dbMonitor.OnBlacklistedCardCreated += DbMonitor_OnBlacklistedCardCreated;
            dbMonitor.OnBlacklistedCardUpdated += DbMonitor_OnBlacklistedCardUpdated;
            dbMonitor.OnBlacklistedCardDeleted += DbMonitor_OnBlacklistedCardDeleted;

            // delete black card
            dbMonitor.OnReleasedCardCreated += DbMonitor_OnReleasedCardCreated;
            dbMonitor.OnReleasedCardDeleted += DbMonitor_OnReleasedCardDeleted;

            dbMonitor.OnGrayInfoCreated += DbMonitor_OnGrayInfoCreated;
            dbMonitor.OnGrayInfoDeleted += DbMonitor_OnGrayInfoDeleted;

            dbMonitor.OnMultipleAccountsCreated += DbMonitor_OnMultipleAccountsCreated;
            dbMonitor.OnMultipleAccountsUpdated += DbMonitor_OnMultipleAccountsUpdated;
            dbMonitor.OnMultipleCardsCreated += DbMonitor_OnMultipleCardsCreated;
            dbMonitor.OnMultipleCardsUpdated += DbMonitor_OnMultipleCardsUpdated;

            // Migrate database
            using (var context = new GuardDbContext())
            {
                context.Database.Migrate();
            }

            ThreadPool.QueueUserWorkItem(arg =>
            {
                StartDbMonitor();

                if (downloader != null)
                    downloader.Start();
            });

            ThreadPool.QueueUserWorkItem(arg =>
            {
                UploadExistingData();
            });

            return Task.FromResult(true);
        }

        #endregion

        #region Stop

        public Task<bool> Stop()
        {
            if (dbMonitor != null)
            {
                // Unhook the events.
                dbMonitor.OnCardTrxCreated -= DbMonitor_OnCardTrxCreated;

                dbMonitor.OnRechargeCreated -= DbMonitor_OnRechargeCreated;

                dbMonitor.OnAccountCreated -= DbMonitor_OnAccountCreated;
                dbMonitor.OnAccountUpdated -= DbMonitor_OnAccountUpdated;

                dbMonitor.OnCardInfoCreated -= DbMonitor_OnCardInfoCreated;
                dbMonitor.OnCardInfoUpdated -= DbMonitor_OnCardInfoUpdated;
                dbMonitor.OnCardInfoDeleted -= DbMonitor_OnCardInfoDeleted;

                // cardreploss
                dbMonitor.OnLostCardCreated -= DbMonitor_OnLostCardCreated;

                // cardlogout
                dbMonitor.OnClosedCardCreated -= DbMonitor_OnClosedCardCreated;

                // base black card
                dbMonitor.OnBaseBlackCardCreated -= DbMonitor_OnBaseBlackCardCreated;
                dbMonitor.OnBaseBlackCardDeleted -= DbMonitor_OnBaseBlackCardDeleted;

                // add black card
                dbMonitor.OnBlacklistedCardCreated -= DbMonitor_OnBlacklistedCardCreated;
                dbMonitor.OnBlacklistedCardUpdated -= DbMonitor_OnBlacklistedCardUpdated;
                dbMonitor.OnBlacklistedCardDeleted -= DbMonitor_OnBlacklistedCardDeleted;

                // delete black card
                dbMonitor.OnReleasedCardCreated -= DbMonitor_OnReleasedCardCreated;
                dbMonitor.OnReleasedCardDeleted -= DbMonitor_OnReleasedCardDeleted;

                dbMonitor.OnGrayInfoCreated -= DbMonitor_OnGrayInfoCreated;
                dbMonitor.OnGrayInfoDeleted -= DbMonitor_OnGrayInfoDeleted;

                dbMonitor.OnMultipleAccountsCreated -= DbMonitor_OnMultipleAccountsCreated;
                dbMonitor.OnMultipleAccountsUpdated -= DbMonitor_OnMultipleAccountsUpdated;
                dbMonitor.OnMultipleCardsCreated -= DbMonitor_OnMultipleCardsCreated;
                dbMonitor.OnMultipleCardsUpdated -= DbMonitor_OnMultipleCardsUpdated;


                dbMonitor.Stop();
            }

            return Task.FromResult(true);
        }

        #endregion

        #region Event handlers

        #region OnAccountCreated

        // New account created
        private void DbMonitor_OnAccountCreated(object sender, EntryFoundEventArgs e)
        {
            if (e.TableAudit.AccountCreated.HasValue && e.TableAudit.AccountCreated.Value != 0)
            {
                using (var context = new SpsDbContext(_mySqlConn))
                {
                    var record = context.TAcctinfo.FirstOrDefault(a => a.Gid == Convert.ToUInt64(e.TableAudit.AccountCreated.Value));

                    if (record != null)
                    {
                        var result = SubmitAccountRecordAsync(record, HostOperationType.CreateAccount).Result;

                        logger.Info($"Just try to submit a new Account for AccoutId: {record.AcctId}, GID: {record.Gid}, Success? {result.Success}");

                        if (result.Success)
                        {
                            context.TTableaudit.Remove(e.TableAudit);
                            var val = context.SaveChanges();
                        }
                        else
                        {
                            //In case that host returns failure, should retry later on.
                            logger.Warn("Result not success, don't remove the Created account record");
                            //context.TTableaudit.Remove(e.TableAudit);
                            //var val = context.SaveChanges();
                        }
                    }
                    else
                    {
                        logger.Error("Stange, could not find the record for created account!");

                        context.TTableaudit.Remove(e.TableAudit);
                        context.SaveChanges();
                    }
                }
            }
        }

        #endregion

        #region OnAccountUpdated

        //Account updated
        private async void DbMonitor_OnAccountUpdated(object sender, EntryFoundEventArgs e)
        {
            if (e.TableAudit.AccountUpdated.HasValue && e.TableAudit.AccountUpdated.Value != 0)
            {
                using (var context = new SpsDbContext(_mySqlConn))
                {
                    var record = await
                        context.TAcctinfo.FirstOrDefaultAsync(a => a.Gid == Convert.ToUInt64(e.TableAudit.AccountUpdated.Value));

                    if (record != null)
                    {
                        var result = SubmitAccountRecordAsync(record, HostOperationType.UpdateAccount).Result;
                        logger.Info($"Update account info, AccountId: {record.AcctId}, Name: {record.BelongTo}, Result: {result.Success}");

                        if (result.Success)
                        {
                            CleanupRecord(context, e.TableAudit);
                        }
                        else
                        {
                            // Upload failed, should retry later on.
                            logger.Warn("Result not success, shall retry uploading the Updated Account record");
                        }
                    }
                    else
                    {
                        logger.Error("Could not find the Updated Account record");

                        // Makes no sense to keep it.
                        CleanupRecord(context, e.TableAudit);
                    }
                }
            }
        }

        #endregion

        #region Record cleanup

        private int CleanupRecord(SpsDbContext context, TTableaudit entity)
        {
            logger.Info("Cleaning up...");

            try
            {
                context.TTableaudit.Remove(entity);
                return context.SaveChanges();
            }
            catch (Exception ex)
            {
                logger.Error(ex.ToString());
            }

            return 0;
        }

        #endregion

        #region CardInfoCreated

        //New card issued (card info created)
        private void DbMonitor_OnCardInfoCreated(object sender, EntryFoundEventArgs e)
        {
            if (e.TableAudit.CardInfoCreated.HasValue && e.TableAudit.CardInfoCreated.Value != 0)
            {
                using (var context = new SpsDbContext(_mySqlConn))
                {
                    var record = context.TCardinfo.FirstOrDefault(c => c.Gid == Convert.ToUInt64(e.TableAudit.CardInfoCreated.Value));
                    if (record != null)
                    {
                        var result = SubmitCardInfoAsync(record, HostOperationType.CreateCard).Result;

                        logger.Info($"Submit a new CardInfo for CardNo: {record.CardNo}, AccountId: {record.AcctId} Success? {result.Success}");

                        if (result.Success)
                        {
                            CleanupRecord(context, e.TableAudit);
                        }
                        else
                        {
                            // Retry later on when host returns failure.
                            logger.Warn("Result not success, shall retry uploading Created Card Info");
                            
                            if (result.Code == 4)
                            {
                                HandleMissingAccount(context, record.AcctId);
                            }
                        }
                    }
                    else
                    {
                        logger.Error("That's strange, a new CardInfo created, but I can't find it in the table...");

                        // Makes no sense to keep it here.
                        CleanupRecord(context, e.TableAudit);
                    }
                }
            }
        }

        #endregion

        #region CardInfoUpdated

        //Card info updated (could be personal inforation updated)
        private async void DbMonitor_OnCardInfoUpdated(object sender, EntryFoundEventArgs e)
        {
            if (e.TableAudit.CardInfoUpdated.HasValue && e.TableAudit.CardInfoUpdated.Value != 0)
            {
                using (var context = new SpsDbContext(_mySqlConn))
                {
                    var record = await context.TCardinfo.FirstOrDefaultAsync(c => c.Gid == Convert.ToUInt64(e.TableAudit.CardInfoUpdated.Value));

                    if (record != null)
                    {
                        // Find if there is a closed (deactivated) card.
                        var closedCard = await context.TCardlogout
                            .FirstOrDefaultAsync(cl => cl.AcctId == record.AcctId && cl.Holder == record.Holder);

                        if (closedCard != null)
                        {
                            if (closedCard.LgtDate.HasValue && (DateTime.Now - closedCard.LgtDate.Value) < TimeSpan.FromHours(24))
                            {
                                //Assume the card was replaced!
                                var result = SubmitCardInfoAsync(record, HostOperationType.CreateCard).Result;

                                logger.Info($"A card closed and a new card replacement created, " +
                                    $"CardNo: {record.CardNo}, Balance: {record.Money}, Result: {result.Success}");

                                if (result.Success)
                                {
                                    CleanupRecord(context, e.TableAudit);
                                }
                                else
                                {
                                    logger.Warn("Result not success, should try again on Updated Card Info");
                                }
                            }
                            else
                            {
                                var result = SubmitCardInfoAsync(record, HostOperationType.UpdateCard).Result;
                                logger.Info($"Update card info, CardNo: {record.CardNo}, Balance: {record.Money}, PreMalloc: {record.PreMalloc}, Result: {result.Success}");

                                if (result.Success)
                                {
                                    CleanupRecord(context, e.TableAudit);
                                }
                                else
                                {
                                    logger.Warn("Result not success, should try again on Updated Card Info");
                                }
                            }
                        }
                        else
                        {
                            var result = SubmitCardInfoAsync(record, HostOperationType.UpdateCard).Result;
                            logger.Info($"Update card info, CardNo: {record.CardNo}, Balance: {record.Money}, Result: {result.Success}");

                            if (result.Success)
                            {
                                CleanupRecord(context, e.TableAudit);
                            }
                            else
                            {
                                logger.Warn("Result not success, should try again on Updated Card Info");
                            }
                        }
                    }
                    else
                    {
                        logger.Error("*** Could not find the card info record");

                        CleanupRecord(context, e.TableAudit);
                    }
                }
            }
        }

        #endregion

        #region CardInfoDeleted

        //Card info deleted (could be balance updated or card closed)
        private async void DbMonitor_OnCardInfoDeleted(object sender, CardChangedEventArgs e)
        {
            if (!string.IsNullOrEmpty(e.CardNo))
            {
                using (var context = new SpsDbContext(_mySqlConn))
                {
                    var record = await context.TCardinfo
                        .OrderBy(r => r.CardNo)
                        .LastOrDefaultAsync(c => c.CardNo == e.CardNo);

                    if (record != null)
                    {
                        logger.Info("Card info deleted, what can we do about it?");
                    }
                    else
                    {
                        logger.Info($"*** The card of CardNo: {e.CardNo} must be closed/deactivated.");
                    }
                }

                if (e.TableAudit == null)
                {
                    logger.Error("TableAudit is null");
                }

                using (var context = new SpsDbContext(_mySqlConn))
                {
                    context.Attach(e.TableAudit);
                    context.TTableaudit.Remove(e.TableAudit);
                    context.SaveChanges();
                }
            }
        }

        #endregion

        #region CardTrxCreated

        private void DbMonitor_OnCardTrxCreated(object sender, CardTrxEventArgs e)
        {
            if (e.TableAudit.TransCreated.HasValue)
            {
                using (var context = new SpsDbContext(_mySqlConn))
                {
                    FuelProductInfo fuelProduct = new FuelProductInfo();

                    var trans = context.TTrdinfo.FirstOrDefault(t => t.Gid == Convert.ToUInt64(e.TableAudit.TransCreated.Value));

                    if (trans != null)
                    {
                        TTrdinfo grayTrade = null;
                        //In case it's ungray trade.
                        if (trans.TrdType == 2)
                        {
                            grayTrade = context.TTrdinfo
                                .FirstOrDefault(t => t.TrdType == 1 && t.Ctc == trans.Ctc && t.Ttctime == trans.Ttctime);
                        }

                        var fuel = context.TFuellist.FirstOrDefault(f => f.FuelNo == trans.CommId);

                        if (fuel != null)
                        {
                            fuelProduct.FuelName = fuel.Name;
                            fuelProduct.Barcode = GetBarcode(Convert.ToInt32(fuel.FuelNo)).ToString();
                        }
                        else
                        {
                            if (grayTrade != null)
                            {
                                fuel = context.TFuellist.FirstOrDefault(f => f.FuelNo == grayTrade.CommId);
                                if (fuel != null)
                                {
                                    fuelProduct.FuelName = fuel.Name;
                                    fuelProduct.Barcode = GetBarcode(Convert.ToInt32(fuel.FuelNo)).ToString();
                                    fuelProduct.Price = grayTrade.Prc.Value;
                                }
                            }
                        }

                        if (trans.TrdType == 10 || trans.TrdType == 11 || trans.TrdType == 8)
                        {
                            logger.Error($"Fuel change record or bank card payment record or fuel price download record, trdType: {trans.TrdType}, should be ignored");
                            CleanupRecord(context, e.TableAudit);

                            return;
                        }

                        var result = SubmitCardTrxRecordAsync(trans, grayTrade, fuelProduct).Result;

                        logger.Info($"Just submit a new card payment record for " +
                            $"CardNo {trans.CardNo}, TTCTime: {trans.Ttctime}, GID: {trans.Gid}, Success? {result.Success}");

                        if (result.Success)
                        {
                            dbMonitor.AddToProcessedQueue(Convert.ToInt64(e.TableAudit.TransCreated.Value));

                            try
                            {
                                var val = CleanupRecord(context, e.TableAudit);
                                var spsDbMonitor = sender as DbMonitor;
                                if (spsDbMonitor != null)
                                {
                                    spsDbMonitor.ClearFromQueue(e.TableAudit);
                                }
                                logger.Info($"Removing the Trx record from table, result: {val}");
                            }
                            catch (Exception ex)
                            {
                                logger.Error(ex);
                            }
                        }
                        else
                        {
                            var spsDbMonitor = sender as DbMonitor;
                            
                            if (spsDbMonitor != null)
                            {
                                spsDbMonitor.ClearFromQueue(e.TableAudit);
                            }

                            logger.Warn("Upload result not success, should try again on New Card Transaction later");

                            if (result.Code == 3) // missing card
                            {
                                HandleMissingCard(context, trans.CardNo);
                            }
                            else if (result.Code == 4) // missing account
                            {
                                var account = context.TCardinfo.FirstOrDefault(c => c.CardNo == trans.CardNo);
                                if (account != null)
                                {
                                    HandleMissingAccount(context, account.AcctId);
                                }
                            }
                        }
                    }
                }
            }
        }

        #endregion

        #region Recharge and reduction created

        private void DbMonitor_OnRechargeCreated(object sender, EntryFoundEventArgs e)
        {
            if (e.TableAudit.RechargeCreated.HasValue && e.TableAudit.RechargeCreated.Value != 0)
            {
                using (var context = new SpsDbContext(_mySqlConn))
                {
                    var record = context.TRechdebitRep.FirstOrDefault(r => r.Gid == Convert.ToUInt64(e.TableAudit.RechargeCreated.Value));

                    if (record != null)
                    {
                        var result = SubmitRechargeReductionRecord(record).Result;

                        logger.Info($"Just try to submit a new recharge/reduction reocrd for " +
                            $"CardNo: {record.CardNo}, Amount: {record.Mon}, GID = {record.Gid}, Success? {result.Success}");

                        if (result.Success)
                        {
                            CleanupRecord(context, e.TableAudit);
                        }
                        else
                        {
                            if (result.Code == 3)
                            {
                                logger.Info("Missing card, try to upload it first");
                                HandleMissingCard(context, record.CardNo);
                            }
                            else if (result.Code == 4)
                            {
                                logger.Info("Missing account, try to upload it first");
                                HandleMissingAccount(context, record.AcctId);
                            }

                            logger.Warn("Upload result not success, should try again on Recharge/Reduction later");
                        }
                    }
                }
            }
        }

        #endregion

        #region Lost card created

        //挂失卡或者解挂卡
        private void DbMonitor_OnLostCardCreated(object sender, EntryFoundEventArgs e)
        {
            logger.Info($"New report of lost card or card released");

            if (e.TableAudit.LostCardCreated.HasValue && e.TableAudit.LostCardCreated.Value != 0)
            {
                using (var context = new SpsDbContext(_mySqlConn))
                {
                    var record = context.TCardreploss.FirstOrDefault(r => r.Gid == Convert.ToUInt64(e.TableAudit.LostCardCreated.Value));
                    if (record != null)
                    {
                        var cardRepLossResult = SubmitCardRepLossAsync(record).Result;
                        logger.Info($"Just try to submit a new CardRepLoss {record.CardNo}, GID = {record.Gid}, Success? {cardRepLossResult.Success}");

                        var result = SubmitCardOperationInfoAsync(record, string.Empty).Result;

                        logger.Info($"Just try to submit a new lost or released card for CardNo: {record.CardNo}, GID = {record.Gid}, Success? {result.Success}");

                        if (result.Success && cardRepLossResult.Success)
                        {
                            CleanupRecord(context, e.TableAudit);
                        }
                        else
                        {
                            logger.Warn("Upload result not success, should try again on Lost Card later");
                        }
                    }
                }
            }
        }

        #endregion

        #region 注销卡 Closed card created

        //注销卡
        private void DbMonitor_OnClosedCardCreated(object sender, EntryFoundEventArgs e)
        {
            logger.Info($"New closed/deactivated card");

            if (e.TableAudit.ClosedCardCreated.HasValue && e.TableAudit.ClosedCardCreated.Value != 0)
            {
                using (var context = new SpsDbContext(_mySqlConn))
                {
                    var record = context.TCardlogout.FirstOrDefault(r => r.Gid == Convert.ToUInt64(e.TableAudit.ClosedCardCreated.Value));
                    if (record != null)
                    {
                        var result = SubmitCardOperationInfoAsync(null, record.CardNo).Result;

                        logger.Info($"Just try to submit a new closed card for CardNo: {record.CardNo}, GID = {record.Gid}, Success? {result.Success}");

                        if (result.Success)
                        {
                            CleanupRecord(context, e.TableAudit);
                        }
                        else
                        {
                            logger.Warn("Upload not success, should try again on Closed/Deactivated card later");
                        }
                    }
                }
            }
        }

        #endregion

        #region 基础黑名单 (t_blackcard)

        private void DbMonitor_OnBaseBlackCardCreated(object sender, EntryFoundEventArgs e)
        {
            logger.Info("Handling BlackCard record [Create]");

            if (e.TableAudit != null && e.TableAudit.BaseBlackCardCreated.HasValue)
            {
                using (var context = new SpsDbContext(_mySqlConn))
                {
                    var record = context.TBlackcard
                        .FirstOrDefault(r => r.Gid == Convert.ToUInt64(e.TableAudit.BaseBlackCardCreated.Value));

                    if (record != null)
                    {
                        var listedCardInfo = new ListedCardInfo();
                        listedCardInfo.ListedType = ListedType.Base; // base black card
                        listedCardInfo.Gid = Convert.ToInt64(record.Gid);
                        listedCardInfo.CardNo = record.CardNo;
                        listedCardInfo.ListedDate =
                            record.BlackDate == null ? DateTime.MinValue : record.BlackDate.Value;
                        listedCardInfo.AccountGid = Convert.ToInt64(record.AcctGid);
                        listedCardInfo.AccountId = record.AcctId;
                        listedCardInfo.CardType = Convert.ToByte(record.CardType);
                        listedCardInfo.DiscountNo =
                            record.DiscountNo == null ? 0 : record.DiscountNo.Value;
                        listedCardInfo.Reason = record.Reason;
                        listedCardInfo.UploadFlag = Convert.ToByte(record.UploadFlag);
                        listedCardInfo.OperationType = 0; // Create

                        var result = SendListedCardAsync(listedCardInfo).Result;

                        if (result.Success)
                        {
                            logger.Info($"Upload Base BlackCard record [Create] {record.CardNo}, success");
                            CleanupRecord(context, e.TableAudit);
                        }
                        else
                        {
                            logger.Warn("Upload not successful, should try again later on Base BlackCard [CREATE]");
                        }
                    }
                    else
                    {
                        logger.Warn($"Base BlackCard [Create], record gid: {e.TableAudit.BaseBlackCardCreated.Value} not found!");
                        CleanupRecord(context, e.TableAudit);
                    }
                }
            }
        }

        private void DbMonitor_OnBaseBlackCardDeleted(object sender, CardChangedEventArgs e)
        {
            if (e.TableAudit != null && !string.IsNullOrEmpty(e.TableAudit.BaseBlackCardDeleted))
            {
                logger.Info($"Handling BlackCard record [Delete], original Card No: {e.TableAudit.BaseBlackCardDeleted}");

                var listedCard = new ListedCardInfo();
                listedCard.CardNo = e.TableAudit.BaseBlackCardDeleted;
                listedCard.ListedType = ListedType.Base; // base black card
                listedCard.OperationType = 2; //delete

                var result = SendListedCardAsync(listedCard).Result;
                if (result.Success)
                {
                    logger.Info($"Upload Base BlackCard [Deleted], Card No: {e.TableAudit.BaseBlackCardDeleted}, success");
                    using (var context = new SpsDbContext(_mySqlConn))
                    {
                        CleanupRecord(context, e.TableAudit);
                    }
                }
                else
                {
                    logger.Info($"Upload Base BlackCard [Deleted], Card No: {e.TableAudit.BaseBlackCardDeleted} failed, should try again later");
                }
            }
        }

        #endregion

        #region 增量黑名单卡 (t_addblackcard)

        //增量黑名单卡 - Created
        private void DbMonitor_OnBlacklistedCardCreated(object sender, EntryFoundEventArgs e)
        {
            logger.Info("Handling AddBlackCard record [Create]");

            if (e.TableAudit != null && e.TableAudit.BlacklistedCardCreated.HasValue)
            {
                using (var context = new SpsDbContext(_mySqlConn))
                {
                    var record = context.TAddblackcard
                        .FirstOrDefault(r => r.Gid == Convert.ToUInt64(e.TableAudit.BlacklistedCardCreated.Value));

                    if (record != null)
                    {
                        var listedCardInfo = new ListedCardInfo();
                        listedCardInfo.ListedType = ListedType.Add; // add black card
                        listedCardInfo.Gid = Convert.ToInt64(record.Gid);
                        listedCardInfo.CardNo = record.CardNo;
                        listedCardInfo.ListedDate =
                            record.BlackDate == null ? DateTime.MinValue : record.BlackDate.Value;
                        listedCardInfo.AccountGid = Convert.ToInt64(record.AcctGid);
                        listedCardInfo.AccountId = record.AcctId;
                        listedCardInfo.CardType = Convert.ToByte(record.CardType);
                        listedCardInfo.DiscountNo =
                            record.DiscountNo == null ? 0 : record.DiscountNo.Value;
                        listedCardInfo.Reason = record.Reason;
                        listedCardInfo.UploadFlag = Convert.ToByte(record.UploadFlag);
                        listedCardInfo.OperationType = 0; // Create

                        var result = SendListedCardAsync(listedCardInfo).Result;

                        if (result.Success)
                        {
                            logger.Info($"Upload AddBlackCard record [Create] {record.CardNo}, success");
                            CleanupRecord(context, e.TableAudit);
                        }
                        else
                        {
                            logger.Warn("Upload not successful, should try again later on AddBlackCard [CREATE]");
                        }
                    }
                    else
                    {
                        logger.Warn($"AddBlackCard [Create], record gid: {e.TableAudit.BlacklistedCardUpdated.Value} not found!");
                        CleanupRecord(context, e.TableAudit);
                    }
                }
            }
        }

        //增量黑名单卡 - Updated
        private void DbMonitor_OnBlacklistedCardUpdated(object sender, EntryFoundEventArgs e)
        {
            logger.Info($"Handling AddBlackCard record [Update]");

            if (e.TableAudit != null && e.TableAudit.BlacklistedCardUpdated.HasValue)
            {
                using (var context = new SpsDbContext(_mySqlConn))
                {
                    var record = context.TAddblackcard
                        .FirstOrDefault(r => r.Gid == Convert.ToUInt64(e.TableAudit.BlacklistedCardUpdated.Value));

                    if (record != null)
                    {
                        var listedCardInfo = new ListedCardInfo();
                        listedCardInfo.ListedType = ListedType.Add; // add black card
                        listedCardInfo.Gid = Convert.ToInt64(record.Gid);
                        listedCardInfo.CardNo = record.CardNo;
                        listedCardInfo.ListedDate =
                            record.BlackDate == null ? DateTime.MinValue : record.BlackDate.Value;
                        listedCardInfo.AccountGid = Convert.ToInt64(record.AcctGid);
                        listedCardInfo.AccountId = record.AcctId;
                        listedCardInfo.CardType = Convert.ToByte(record.CardType);
                        listedCardInfo.DiscountNo =
                            record.DiscountNo == null ? 0 : record.DiscountNo.Value;
                        listedCardInfo.Reason = record.Reason;
                        listedCardInfo.UploadFlag = Convert.ToByte(record.UploadFlag);
                        listedCardInfo.OperationType = 1; // Update

                        var result = SendListedCardAsync(listedCardInfo).Result;

                        if (result.Success)
                        {
                            logger.Info($"Upload AddBlackCard record [Update] {record.CardNo}, success");
                            CleanupRecord(context, e.TableAudit);
                        }
                        else
                        {
                            logger.Warn("Upload not successful, should try again later on AddBlackCard [UPDATE]");
                        }
                    }
                    else
                    {
                        logger.Warn($"AddBlackCard [Update], record gid: {e.TableAudit.BlacklistedCardUpdated.Value} not found!");
                        CleanupRecord(context, e.TableAudit);
                    }
                }
            }
        }

        //增量黑名单卡 - Deleted
        private void DbMonitor_OnBlacklistedCardDeleted(object sender, CardChangedEventArgs e)
        {
            if (e.TableAudit != null && !string.IsNullOrEmpty(e.TableAudit.BlacklistedCardDeleted))
            {
                logger.Info($"Handling AddBlackCard record [Delete], original Card No: {e.TableAudit.BlacklistedCardDeleted}");

                var listedCard = new ListedCardInfo();
                listedCard.CardNo = e.TableAudit.BlacklistedCardDeleted;
                listedCard.ListedType = ListedType.Add; // add black card
                listedCard.OperationType = 2; //delete

                var result = SendListedCardAsync(listedCard).Result;
                if (result.Success)
                {
                    logger.Info($"Upload AddBlackCard [Deleted], Card No: {e.TableAudit.BlacklistedCardDeleted}, success");
                    using (var context = new SpsDbContext(_mySqlConn))
                    {
                        CleanupRecord(context, e.TableAudit);
                    }
                }
                else
                {
                    logger.Info($"Upload AddBlackCard [Deleted], Card No: {e.TableAudit.BlacklistedCardDeleted} failed, should try again later");
                }
            }
        }

        #endregion

        #region 减量黑名单 (t_deleteBlackCard)

        //减量黑名单卡
        private void DbMonitor_OnReleasedCardDeleted(object sender, CardChangedEventArgs e)
        {
            if (e.TableAudit != null && !string.IsNullOrEmpty(e.TableAudit.ReleasedCardDeleted))
            {
                logger.Info($"Handling DeleteBlackCard record [Delete], Card No: {e.TableAudit.ReleasedCardDeleted}");

                var listedCard = new ListedCardInfo();
                listedCard.CardNo = e.TableAudit.ReleasedCardDeleted;
                listedCard.ListedType = ListedType.Deleted; // Delete black card
                listedCard.OperationType = 2; //delete

                var result = SendListedCardAsync(listedCard).Result;
                if (result.Success)
                {
                    logger.Info($"Upload DeleteBlackCard record [Deleted], Card No: {e.TableAudit.BlacklistedCardDeleted}, success");
                    using (var context = new SpsDbContext(_mySqlConn))
                    {
                        CleanupRecord(context, e.TableAudit);
                    }
                }
                else
                {
                    logger.Info($"Upload DeleteBlackCard record [Deleted], Card No: {e.TableAudit.BlacklistedCardDeleted} failed, should try again later");
                }
            }
        }

        private void DbMonitor_OnReleasedCardCreated(object sender, EntryFoundEventArgs e)
        {
            logger.Info("Handling DeleteBlackCard record [Create]");

            if (e.TableAudit != null && e.TableAudit.ReleasedCardCreated.HasValue)
            {
                using (var context = new SpsDbContext(_mySqlConn))
                {
                    var record = context.TDeleteblackcard
                        .FirstOrDefault(r => r.Gid == Convert.ToUInt64(e.TableAudit.ReleasedCardCreated.Value));

                    if (record != null)
                    {
                        var listedCardInfo = new ListedCardInfo();
                        listedCardInfo.ListedType = ListedType.Deleted; //delete black card
                        listedCardInfo.Gid = Convert.ToInt64(record.Gid);
                        listedCardInfo.CardNo = record.CardNo;
                        listedCardInfo.ListedDate =
                            record.BlackDate == null ? DateTime.MinValue : record.BlackDate.Value;
                        listedCardInfo.AccountGid = Convert.ToInt64(record.AcctGid);
                        listedCardInfo.AccountId = record.AcctId;
                        listedCardInfo.CardType = Convert.ToByte(record.CardType);
                        listedCardInfo.DiscountNo =
                            record.DiscountNo == null ? 0 : record.DiscountNo.Value;
                        listedCardInfo.Reason = record.Reason;
                        listedCardInfo.UploadFlag = Convert.ToByte(record.UploadFlag);
                        listedCardInfo.OperationType = 0; // Create

                        var result = SendListedCardAsync(listedCardInfo).Result;

                        if (result.Success)
                        {
                            logger.Info($"Upload DeleteBlackCard record [Create]: {record.CardNo}, success");
                            CleanupRecord(context, e.TableAudit);
                        }
                        else
                        {
                            logger.Warn("Upload not successful, should try again later on DeleteBlackCard [Create]");
                        }
                    }
                    else
                    {
                        logger.Warn($"DeleteBlackCard record, gid: {e.TableAudit.ReleasedCardCreated.Value} not found!");
                        CleanupRecord(context, e.TableAudit);
                    }
                }
            }
        }

        #endregion

        #region 基础黑名单 (t_blackcard)

        // to be implemented...

        #endregion

        #region 灰名单 (t_grayinfo)

        private void DbMonitor_OnGrayInfoCreated(object sender, EntryFoundEventArgs e)
        {
            if (e.TableAudit.GrayInfoDeleted != null && e.TableAudit.GrayInfoCreated.Value != 0)
            {
                logger.Info($"Handling GrayInfo record [Create] Gid: {e.TableAudit.GrayInfoCreated.Value}");

                using (var context = new SpsDbContext(_mySqlConn))
                {
                    var record = context.TGrayinfo
                        .FirstOrDefault(g => g.Gid == Convert.ToUInt64(e.TableAudit.GrayInfoCreated.Value));

                    if (record != null)
                    {
                        var grayInfo = CreateGrayInfoFromRecord(record);

                        var result = SendGrayInfoAsync(grayInfo).Result;

                        if (result.Success)
                        {
                            logger.Info($"Upload GrayInfo record [Create]: {record.CardNo}, success");
                            CleanupRecord(context, e.TableAudit);
                        }
                        else
                        {
                            logger.Warn("Upload not successful, should try again later on GrayInfo [Create]");
                        }
                    }
                    else
                    {
                        logger.Info($"Gray info, gid={e.TableAudit.GrayInfoCreated.Value} could not be found.");
                        CleanupRecord(context, e.TableAudit);
                    }
                }
            }
        }

        private GrayInfo CreateGrayInfoFromRecord(TGrayinfo record)
        {
            if (record == null)
                return null;

            var grayInfo = new GrayInfo();
            grayInfo.Gid = record.Gid;
            grayInfo.SNo = (int)record.Sno;
            grayInfo.PumpType = (byte)record.PumpType;
            grayInfo.CardNo = record.CardNo;
            grayInfo.PaymodeId = (byte)record.PaymodeId;
            grayInfo.TrdType = (byte)record.TrdType;
            grayInfo.CommId = record.CommId;
            grayInfo.Price = (uint)record.Prc;
            grayInfo.Volume = (uint)record.Vol;
            grayInfo.Money = (uint)record.Mon;
            grayInfo.RealMon = (uint)record.RealMon;
            grayInfo.CardBalance = (uint)record.CardBal;
            grayInfo.Ctc = (uint)record.Ctc;
            grayInfo.TtcTime =
                record.Ttctime.HasValue ? record.Ttctime.Value.ToString("yyyy-MM-dd HH:mm:ss") : "0001-01-01 00:00:00";
            grayInfo.TtcTimeEnd =
                record.TtctimeEnd.HasValue ? record.TtctimeEnd.Value.ToString("yyyy-MM-dd HH:mm:ss") : "0001-01-01 00:00:00";
            grayInfo.Ttc = (uint)record.Ttc;
            grayInfo.SeqNo = Convert.ToUInt16(record.SeqNo);
            grayInfo.NozNo = (byte)record.NozNo;
            grayInfo.PumpNo = (byte)record.PumpNo;
            grayInfo.PayTermId = (ulong)record.PayTemId;
            grayInfo.EndPumpId = (ulong)record.EndPumpId;
            grayInfo.DiscountNo = (uint)record.DiscountNo;
            grayInfo.PsamAsn = record.Psamasn;
            grayInfo.PsamTac = (uint)record.Psamtac;
            grayInfo.PsamTid = record.Psamtid;
            grayInfo.PsamTtc = (uint)record.Psamttc;
            grayInfo.Tac = (uint)record.Tac;
            grayInfo.Gmac = (uint)record.Gmac;
            grayInfo.Tmac = (uint)record.Tmac;
            grayInfo.UploadFlag = (byte)record.UploadFlag;
            grayInfo.OperationType = 0; // Record creation

            return grayInfo;
        }

        private void DbMonitor_OnGrayInfoDeleted(object sender, EntryFoundEventArgs e)
        {
            logger.Info($"Handling GrayInfo record [Delete], Card No: {e.TableAudit.GrayInfoDeleted}");

            var grayInfo = new GrayInfo();
            grayInfo.CardNo = e.TableAudit.GrayInfoDeleted;
            grayInfo.OperationType = 2; // Record Deletion
            var result = SendGrayInfoAsync(grayInfo).Result;

            if (result.Success)
            {
                logger.Info($"Upload GrayInfo record [Delete], Card No: {e.TableAudit.GrayInfoDeleted}, success");

                using (var context = new SpsDbContext(_mySqlConn))
                {
                    logger.Info($"Removing GrayInfo [Delete] audit entry");
                    CleanupRecord(context, e.TableAudit);
                }
            }
            else
            {
                logger.Warn("Upload not successful, should try again later on GrayInfo [Delete]");
            }
        }

        private async Task<SendResult> SendGrayInfoAsync(GrayInfo grayInfo)
        {
            var offlineRequest = new OfflineRequest();
            offlineRequest.GrayInfoList = new List<GrayInfo>();
            offlineRequest.GrayInfoList.Add(grayInfo);
            var result = await dataCourier.SendRequest(offlineRequest, HostOperationType.GrayInfo);
            return result;
        }

        #endregion

        #region Multiple records handling

        // Multiple `CREATED` acctinfo
        private void DbMonitor_OnMultipleAccountsCreated(object sender, MultipleAccountRecordsEventArgs e)
        {
            var auditCount = e.AuditRecords.Count();
            var accountInfoCount = e.AccountInfoRecords.Count();

            if (accountInfoCount > 0)
                logger.Info($"Multiple created account, count: {auditCount}, to be submitted count: {accountInfoCount}");

            try
            {
                using (var context = new SpsDbContext(_mySqlConn))
                {
                    var result = SubmitMultipleAccountRecordsAsync(e.AccountInfoRecords, HostOperationType.CreateAccount).GetAwaiter().GetResult();

                    if (result.Success)
                    {
                        context.RemoveRange(e.AuditRecords);
                        var affectedRowCount = context.SaveChanges();

                        logger.Info($"Cleaning up multiple created account info, affected row count: {affectedRowCount}");
                    }
                }
            }
            catch (Exception ex)
            {
                logger.Error(ex.ToString());
            }
        }

        // Multiple `UPDATED` acctinfo
        private void DbMonitor_OnMultipleAccountsUpdated(object sender, MultipleAccountRecordsEventArgs e)
        {
            var auditCount = e.AuditRecords.Count();
            var accountInfoCount = e.AccountInfoRecords.Count();

            if (accountInfoCount > 0)
                logger.Info($"Multiple updated account, count: {auditCount}, to be submitted count: {accountInfoCount}");

            try
            {
                using (var context = new SpsDbContext(_mySqlConn))
                {
                    var result = SubmitMultipleAccountRecordsAsync(e.AccountInfoRecords, HostOperationType.UpdateAccount).GetAwaiter().GetResult();

                    if (result.Success)
                    {
                        context.RemoveRange(e.AuditRecords);
                        var affectedRowCount = context.SaveChanges();

                        logger.Info($"Cleaning up multiple updated account info, affected row count: {affectedRowCount}");
                    }
                }
            }
            catch (Exception ex)
            {
                logger.Error(ex.ToString());
            }
        }

        // Multiple `CREATED` cardinfo
        private void DbMonitor_OnMultipleCardsCreated(object sender, MultipleCardRecordsEventArgs e)
        {
            var auditCount = e.AuditRecords.Count();
            var cardInfoCount = e.CardInfoRecords.Count();

            if (cardInfoCount > 0)
                logger.Info($"Multiple created cards, count: {auditCount}, to be submitted count: {cardInfoCount}");

            try
            {
                using (var context = new SpsDbContext(_mySqlConn))
                {
                    var result = SubmitMultipleCardInfoAsync(e.CardInfoRecords, HostOperationType.CreateCard).GetAwaiter().GetResult();

                    if (result.Success)
                    {
                        context.RemoveRange(e.AuditRecords);
                        var affectedRowCount = context.SaveChanges();

                        logger.Info($"Cleaning up multiple created cards, affected row count: {affectedRowCount}");
                    }
                }
            }
            catch (Exception ex)
            {
                logger.Error(ex.ToString());
            }
        }

        // Multiple `UPDATED` cardinfo
        private void DbMonitor_OnMultipleCardsUpdated(object sender, MultipleCardRecordsEventArgs e)
        {
            var auditCount = e.AuditRecords.Count();
            var cardInfoCount = e.CardInfoRecords.Count();
            if (cardInfoCount > 0)
                logger.Info($"Multiple updated cards, count: {auditCount}, to be submitted count: {cardInfoCount}");

            try
            {
                using (var context = new SpsDbContext(_mySqlConn))
                {
                    var result = SubmitMultipleCardInfoAsync(e.CardInfoRecords, HostOperationType.UpdateCard).GetAwaiter().GetResult();

                    if (result.Success)
                    {
                        context.RemoveRange(e.AuditRecords);
                        var affectedRowCount = context.SaveChanges();

                        logger.Info($"Cleaning up multiple updated cards, affected row count: {affectedRowCount}");
                    }
                }
            }
            catch (Exception ex)
            {
                logger.Error(ex.ToString());
            }
        }

        #endregion

        #endregion

        #region Force upload transaction

        public async Task ForceTransactionUploadAsync(long gid)
        {
            logger.Info("Force uploading a transaction record...");

            if (dbMonitor.IsTransAlreadyUploaded(gid))
            {
                logger.Warn($"The transaction of GID: {gid} was uploaded, do nothing about it!");
                return;
            }

            var addResult = dbMonitor.AddToQueue(gid);

            if (!addResult)
            {
                logger.Info("No need to handle it now, since db monitor already found it");
                return;
            }

            logger.Info($"Ready to go ahead to upload transaction of GID: {gid}");
            using (var context = new SpsDbContext(_mySqlConn))
            {
                FuelProductInfo fuelProduct = new FuelProductInfo();

                var trans = await context.TTrdinfo.FirstOrDefaultAsync(t => t.Gid == Convert.ToUInt64(gid));

                if (trans != null)
                {
                    logger.Info($"   The transaction information of GID: {gid} is retrieved");

                    TTrdinfo grayTrade = null;
                    //In case it's ungray trade.
                    if (trans.TrdType == 2)
                    {
                        grayTrade = context.TTrdinfo
                            .FirstOrDefault(t => t.TrdType == 1 && t.CardNo == trans.CardNo
                                && t.Ctc == trans.Ctc && t.Ttctime == trans.Ttctime);
                    }

                    var fuel = context.TFuellist.FirstOrDefault(f => f.FuelNo == trans.CommId);

                    if (fuel != null)
                    {
                        logger.Info("fuel is not null");

                        fuelProduct.FuelName = fuel.Name;
                        fuelProduct.Barcode = GetBarcode(Convert.ToInt32(fuel.FuelNo)).ToString();
                    }
                    else
                    {
                        logger.Info("Fuel is null");
                        if (grayTrade != null)
                        {
                            logger.Info("get fuel from gray trans");

                            fuel = context.TFuellist.FirstOrDefault(f => f.FuelNo == grayTrade.CommId);
                            if (fuel != null)
                            {
                                fuelProduct.FuelName = fuel.Name;
                                fuelProduct.Barcode = GetBarcode(Convert.ToInt32(fuel.FuelNo)).ToString();
                                fuelProduct.Price = grayTrade.Prc.Value;
                            }
                        }
                    }

                    //var result = SubmitCardTrxRecordAsync(trans, fuelProduct).Result;
                    var result = await SubmitCardTrxRecordAsync(trans, grayTrade, fuelProduct);

                    logger.Info($"Force submit a new card payment record for " +
                        $"CardNo {trans.CardNo}, TTCTime: {trans.Ttctime}, GID: {trans.Gid}, Success? {result.Success}");

                    if (result.Success)
                    {
                        dbMonitor.AddToProcessedQueue(gid);

                        try
                        {
                            var currentTableAudit = await context.TTableaudit.FirstOrDefaultAsync(t => t.TransCreated == gid);
                            if (currentTableAudit != null)
                            {
                                var val = CleanupRecord(context, currentTableAudit);
                                logger.Info($"Cleaning up the Trx record from table, result: {val}");
                            }

                            dbMonitor.ClearFromQueue(gid);
                        }
                        catch (Exception ex)
                        {
                            logger.Error(ex);
                        }
                    }
                    else
                    {
                        dbMonitor.ClearFromQueue(gid);
                        logger.Warn($"Force upload result not success, should try again on New Card Transaction later, clear {gid} from queue");
                    }
                }
                else
                {
                    logger.Info($"This won't look good, the record of GID: {gid} could not be found! Clear it!");
                    dbMonitor.ClearFromQueue(gid);
                }
            }
        }

        #endregion

        private async Task<SendResult> SubmitAccountUpdate(TAcctinfo info)
        {
            var accountInfo = CreateAccountInfo(info);

            var result = await dataCourier.SendRequest(new OfflineRequest { AccountInfo = accountInfo }, HostOperationType.UpdateAccount);

            return result;
        }

        #region CreateAccountInfo - convert TAcctinfo to host account

        private AccountInfo CreateAccountInfo(TAcctinfo info)
        {
            AccountInfo accountInfo = new AccountInfo();

            accountInfo.Gid = Convert.ToInt64(info.Gid);
            accountInfo.SNo = Convert.ToInt16(info.Sno);
            accountInfo.AcctDate =
                info.AcctDate.HasValue ? info.AcctDate.Value.ToString("yyyy-MM-dd HH:mm:ss") /*.Replace(' ', 'T')*/ : "0001-01-01";
            accountInfo.AcctId = info.AcctId;
            accountInfo.AcctSNo = Convert.ToInt16(info.AcctSno);
            accountInfo.AcctState = Convert.ToByte(info.AcctState);
            accountInfo.AcctType = Convert.ToByte(info.AcctType);
            accountInfo.Address = info.Address;
            accountInfo.Amount = Convert.ToInt64(info.Amount);
            accountInfo.BelongTo = info.BelongTo;
            accountInfo.CertfType = info.CertfType;
            accountInfo.CertfNo = info.CertfNo;
            accountInfo.EnableSms = Convert.ToByte(info.EnableSms);
            accountInfo.FuelNo = info.FuelNo;
            accountInfo.Gift = Convert.ToInt32(info.Gift);
            accountInfo.PhoneNo = info.PhoneNo;
            accountInfo.RechgTotal = Convert.ToInt64(info.RechgTotal);
            accountInfo.TMac = Convert.ToInt64(info.Tmac);
            accountInfo.UploadFlag = Convert.ToByte(info.UploadFlag);

            accountInfo.WaitMalloc = info.Waitmalloc.Value > int.MaxValue ? 0 : Convert.ToUInt32(info.Waitmalloc);

            return accountInfo;
        }

        #endregion

        #region SUBMIT newly created Card Transaction

        private async Task<SendResult> SubmitCardTrxRecordAsync(TTrdinfo trx, TTrdinfo grayTrd, FuelProductInfo fuel = null)
        {
            OfflineTransactionInfo transInfo = new OfflineTransactionInfo();

            transInfo.Gid = Convert.ToInt64(trx.Gid);
            transInfo.SNo = Convert.ToInt16(trx.Sno);
            transInfo.ShiftNo = Convert.ToInt32(trx.ShiftNo);
            transInfo.PosId = Convert.ToByte(trx.Posid);
            transInfo.PumpType = Convert.ToByte(trx.PumpType);
            transInfo.Operator = trx.Operator;
            transInfo.CardNo = trx.CardNo;
            transInfo.OperCardNo = trx.OperCardNo;
            transInfo.PayModeId = trx.PaymodeId.HasValue ? (byte)trx.PaymodeId.Value : Convert.ToByte(0);
            transInfo.PayModeNo = trx.PaymodeNo;
            transInfo.TrdType = trx.TrdType;
            transInfo.CommId = trx.CommId;
            transInfo.Barcode = fuel == null ? "" : fuel.Barcode;
            transInfo.FuelName = fuel == null ? "" : fuel.FuelName;
            transInfo.Prc = trx.Prc == 0 ? Convert.ToInt32(fuel.Price) : Convert.ToInt32(trx.Prc);
            transInfo.Vol = Convert.ToInt32(trx.Vol);
            transInfo.Mon = Convert.ToInt32(trx.Mon);
            transInfo.RealMon = Convert.ToInt32(trx.RealMon);
            transInfo.CardBal = Convert.ToInt32(trx.CardBal);
            transInfo.CardType = Convert.ToByte(trx.CardType);
            transInfo.CTC = Convert.ToInt32(trx.Ctc);
            transInfo.TTCTime =
                trx.Ttctime.ToString("yyyy-MM-dd HH:mm:ss") /*.Replace(' ', 'T')*/;
            transInfo.TTCTimeEnd =
                trx.TtctimeEnd.HasValue ? trx.TtctimeEnd.Value.ToString("yyyy-MM-dd HH:mm:ss") /*.Replace(' ', 'T')*/ : "0001-01-01 00:00:00";

            if (transInfo.Mon == 0 && transInfo.RealMon == 0)
            {
                if (grayTrd != null)
                {
                    transInfo.CardBal += Convert.ToInt32(grayTrd.Mon - grayTrd.RealMon);
                    transInfo.TTCTimeEnd = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss");
                }
            }

            transInfo.TTC = Convert.ToInt32(trx.Ttc);
            transInfo.SeqNo = Convert.ToInt32(trx.SeqNo);
            transInfo.BillNo = Convert.ToInt32(trx.BillNo);
            transInfo.NozNo = Convert.ToByte(trx.NozNo);
            transInfo.PumpNo = trx.PumpNo;
            transInfo.PayTermId = Convert.ToInt32(trx.PayTemId);
            transInfo.EndPump = Convert.ToInt64(trx.EndPumpId);
            transInfo.DiscountNo = Convert.ToInt32(trx.DiscountNo);
            transInfo.PsamAsn = trx.Psamasn;
            transInfo.PsamTac = Convert.ToInt64(trx.Psamtac);
            transInfo.PsamTid = trx.Psamtid;
            transInfo.PsamTTC = Convert.ToInt32(trx.Psamttc);
            transInfo.Tac = Convert.ToInt64(trx.Tac);
            transInfo.GMac = Convert.ToInt64(trx.Gmac);
            transInfo.Integral = Convert.ToInt32(trx.Integral);
            transInfo.UploadFlag = Convert.ToByte(trx.UploadFlag);
            transInfo.CarId = trx.CarId;
            transInfo.CarLicsNo = trx.CarLicsNo;
            transInfo.LineNo = trx.LineNo;
            transInfo.BillType = Convert.ToByte(trx.BillType);
            transInfo.BillFlag = Convert.ToByte(trx.BillFlag);

            var result = await
                dataCourier.SendRequest(new OfflineRequest { OfflineTrxInfo = transInfo }, HostOperationType.CreatePayRecord);

            return result;
        }

        private async Task<SendResult> SubmitCardTrxRecordAsync(TCardtrx trx)
        {
            OfflineTransactionInfo transInfo = new OfflineTransactionInfo();

            transInfo.Gid = Convert.ToInt64(trx.Gid);
            transInfo.SNo = Convert.ToInt16(trx.Sno);
            transInfo.ShiftNo = Convert.ToInt32(trx.ShiftNo);
            transInfo.PosId = Convert.ToByte(trx.Posid);
            transInfo.PumpType = Convert.ToByte(trx.PumpType);
            transInfo.Operator = trx.Operator;
            transInfo.CardNo = trx.CardNo;
            trx.PaymodeId = trx.PaymodeId;
            trx.PaymodeNo = trx.PaymodeNo;
            trx.TrdType = trx.TrdType;
            transInfo.CommId = trx.CommId;
            transInfo.Prc = Convert.ToInt32(trx.Prc);
            transInfo.Vol = Convert.ToInt32(trx.Vol);
            transInfo.Mon = Convert.ToInt32(trx.Mon);
            transInfo.RealMon = Convert.ToInt32(trx.RealMon);
            transInfo.CardBal = Convert.ToInt32(trx.CardBal);
            transInfo.CardType = Convert.ToByte(trx.CardType);
            transInfo.CTC = Convert.ToInt32(trx.Ctc);
            transInfo.TTCTime =
                trx.Ttctime.ToString("yyyy-MM-dd HH:mm:ss") /*.Replace(' ', 'T')*/;
            transInfo.TTCTimeEnd =
                trx.TtctimeEnd.HasValue ? trx.TtctimeEnd.Value.ToString("yyyy-MM-dd HH:mm:ss") /*.Replace(' ', 'T')*/ : "0001-01-01 00:00:00";
            transInfo.TTC = Convert.ToInt32(trx.Ttc);
            transInfo.SeqNo = Convert.ToInt32(trx.SeqNo);
            transInfo.BillNo = Convert.ToInt32(trx.BillNo);
            transInfo.NozNo = Convert.ToByte(trx.NozNo);
            transInfo.PumpNo = trx.PumpNo;
            transInfo.PayTermId = Convert.ToInt32(trx.PayTemId);
            transInfo.EndPump = Convert.ToInt64(trx.EndPumpId);
            transInfo.DiscountNo = Convert.ToInt32(trx.DiscountNo);
            transInfo.PsamAsn = trx.Psamasn;
            transInfo.PsamTac = Convert.ToInt64(trx.Psamtac);
            transInfo.PsamTid = trx.Psamtid;
            transInfo.PsamTTC = Convert.ToInt32(trx.Psamttc);
            transInfo.Tac = Convert.ToInt64(trx.Tac);
            transInfo.GMac = Convert.ToInt64(trx.Gmac);
            transInfo.Integral = Convert.ToInt32(trx.Integral);
            transInfo.UploadFlag = Convert.ToByte(trx.UploadFlag);
            transInfo.CarId = trx.CarId;
            transInfo.CarLicsNo = trx.CarLicsNo;
            transInfo.LineNo = trx.LineNo;
            transInfo.BillType = Convert.ToByte(trx.BillType);
            transInfo.BillFlag = Convert.ToByte(trx.BillFlag);

            var result = await
                dataCourier.SendRequest(new OfflineRequest { OfflineTrxInfo = transInfo }, HostOperationType.CreatePayRecord);

            return result;
        }

        #endregion

        #region Single and multiple accounts

        private async Task<SendResult> SubmitAccountRecordAsync(TAcctinfo info, HostOperationType operationType)
        {
            var accountInfo = CreateAccountInfo(info);

            var result = await dataCourier.SendRequest(new OfflineRequest { AccountInfo = accountInfo }, operationType);
            return result;
        }

        private async Task<SendResult> SubmitMultipleAccountRecordsAsync(IEnumerable<TAcctinfo> accountRecords, HostOperationType operationType)
        {
            List<AccountInfo> accountList = new List<AccountInfo>();
            foreach (var item in accountRecords)
            {
                var acct = CreateAccountInfo(item);
                accountList.Add(acct);
            }

            var result = await dataCourier.SendRequest(new OfflineRequest { AccountInfoList = accountList }, operationType);

            return result;
        }

        #endregion

        #region Single and multiple card info

        private async Task<SendResult> SubmitCardInfoAsync(TCardinfo info, HostOperationType operationType)
        {
            var cardInfo = ConvertDbCardInfoToHostCardInfo(info);

            var result = await dataCourier.SendRequest(new OfflineRequest { CardInfo = cardInfo }, operationType);

            return result;
        }

        private async Task<SendResult> SubmitMultipleCardInfoAsync(IEnumerable<TCardinfo> cardInfoRecords, HostOperationType operationType)
        {
            var cardInfoList = new List<CardInfo>();
            foreach (var item in cardInfoRecords)
            {
                var cardInfo = ConvertDbCardInfoToHostCardInfo(item);
                cardInfoList.Add(cardInfo);
            }

            var result = await dataCourier.SendRequest(new OfflineRequest { CardInfoList = cardInfoList }, operationType);
            return result;
        }

        #endregion

        #region Convert db cardinfo to host cardinfo

        private CardInfo ConvertDbCardInfoToHostCardInfo(TCardinfo info)
        {
            CardInfo cardInfo = new CardInfo();

            cardInfo.Gid = Convert.ToInt64(info.Gid);
            cardInfo.AcctId = info.AcctId;
            cardInfo.BLimitCar = Convert.ToByte(info.BLimitCar);
            cardInfo.BLmtGood = info.BLmtGood.HasValue ? Convert.ToBoolean(info.BLmtGood.Value) : false; //0=No restriction, 1=Restricted.
            cardInfo.CStatus = Convert.ToByte(info.CStatus);
            cardInfo.CardClass = Convert.ToByte(info.CardClass);
            cardInfo.CardId = Convert.ToInt64(info.CardId);
            cardInfo.CardNo = info.CardNo;
            cardInfo.CardSNo = Convert.ToInt16(info.CardSno);
            cardInfo.CardType = Convert.ToByte(info.CardType);
            cardInfo.CarNo = info.Carno;
            cardInfo.CTC = Convert.ToInt32(info.Ctc);
            cardInfo.CtcFlag = Convert.ToByte(info.Ctcflag);
            cardInfo.CtcTime = info.Ctctime;
            cardInfo.DiscountNo = Convert.ToInt32(info.DiscountNo);
            cardInfo.DMaxPay = Convert.ToInt32(info.DmaxPay);
            cardInfo.EnableSms = Convert.ToByte(info.EnableSms);
            cardInfo.Holder = info.Holder;
            cardInfo.IntegralTotal = Convert.ToInt32(info.IntegralTotal);
            cardInfo.KcDate =
                info.KcDate.HasValue ? info.KcDate.Value.ToString("yyyy-MM-dd HH:mm:ss") /*.Replace(' ', 'T')*/ : "0001-01-01 00:00:00";
            cardInfo.LimitTimes = Convert.ToByte(info.LimitTimes);
            cardInfo.LmtOil = info.LmtOil;
            cardInfo.MMaxPay = Convert.ToInt32(info.MmaxPay);
            cardInfo.Money = Convert.ToInt32(info.Money);
            cardInfo.OnceMaxPay = Convert.ToInt32(info.OnceMaxPay);
            cardInfo.OperNo = info.OperNo;
            cardInfo.OverDate =
                info.OverDate.HasValue ? info.OverDate.Value.ToString("yyyy-MM-dd HH:mm:ss") /*.Replace(' ', 'T')*/ : "9999-12-12 00:00:00";
            cardInfo.PhoneNo = info.PhoneNo;
            cardInfo.Pre_Malloc = Convert.ToInt32(info.PreMalloc);
            cardInfo.RechgTotal = Convert.ToUInt64(info.RechgTotal);
            cardInfo.SNo = Convert.ToInt16(info.Sno);
            cardInfo.StartDate =
                info.Startdate.HasValue ? info.Startdate.Value.ToString("yyyy-MM-dd HH:mm:ss") /*.Replace(' ', 'T')*/ : "0001-01-01 00:00:00";
            cardInfo.TMac = Convert.ToInt64(info.Tmac);
            cardInfo.UploadFlag = Convert.ToByte(info.UploadFlag);
            cardInfo.UserNo = info.UserNo;
            cardInfo.UserPin = info.UserPin;

            return cardInfo;
        }

        #endregion

        #region submit recharge/debit record

        private async Task<SendResult> SubmitRechargeReductionRecord(TRechdebitRep rep)
        {
            RechargeDebitInfo rdInfo = new RechargeDebitInfo();

            rdInfo.AcctId = rep.AcctId;
            rdInfo.Bal = Convert.ToInt32(rep.Bal);
            rdInfo.BillFlag = Convert.ToByte(rep.BillFlag);
            rdInfo.BillType = Convert.ToByte(rep.BillType);
            rdInfo.CardNo = rep.CardNo;
            rdInfo.CTC = Convert.ToInt32(rep.Ctc);
            rdInfo.DisIntegral = Convert.ToInt32(rep.DisIntegral);
            rdInfo.DisMoney = Convert.ToInt32(rep.DisMoney);
            rdInfo.DiscountNo = Convert.ToInt32(rep.DiscountNo);
            rdInfo.Gid = Convert.ToInt64(rep.Gid);
            rdInfo.Mon = Convert.ToInt32(rep.Mon);
            rdInfo.OperNo = rep.OperNo;
            rdInfo.RechgType = Convert.ToByte(rep.RechgType);
            rdInfo.SNo = Convert.ToInt16(rep.Sno);
            rdInfo.TMac = Convert.ToInt64(rep.Tmac);
            rdInfo.TrdType = Convert.ToByte(rep.TrdType);
            rdInfo.TTC = Convert.ToInt32(rep.Ttc);
            rdInfo.TTCTime =
                rep.Ttctime.HasValue ? rep.Ttctime.Value.ToString("yyyy-MM-dd HH:mm:ss") /*.Replace(' ', 'T')*/ : "0001-01-01 00:00:00";
            rdInfo.UploadFlag = Convert.ToByte(rep.UploadFlag);

            var result = await dataCourier.SendRequest(new OfflineRequest { RechargeDebitInfo = rdInfo }, HostOperationType.CreateRechargeRecord);

            return result;
        }

        #endregion

        #region Card Operation

        private async Task<SendResult> SubmitCardOperationInfoAsync(TCardreploss rl, string cardNo)
        {
            CardOperationInfo info = new CardOperationInfo();

            if (rl != null)
            {
                info.CardNo = rl.CardNo;
                info.AccountId = "";
                info.TimeStamp = rl.LossTime.Value.ToString("yyyy-MM-dd HH:mm:ss") /*.Replace(' ', 'T')*/;
                info.OperationType = rl.OperType.Value;
            }
            else if (!string.IsNullOrEmpty(cardNo))
            {
                info.CardNo = cardNo;
                info.AccountId = "";
                info.TimeStamp = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss") /*.Replace(' ', 'T')*/;
                info.OperationType = 3;
            }

            var result = await dataCourier.SendRequest(new OfflineRequest { CardOperationInfo = info }, HostOperationType.CardOperation);

            return result;
        }

        #endregion

        private async Task<SendResult> SubmitCardRepLossAsync(TCardreploss rl)
        {
            ReportedLostCard cardRepLoss = new ReportedLostCard();
            cardRepLoss.Gid = Convert.ToInt64(rl.Gid);
            cardRepLoss.CardNo = rl.CardNo;
            cardRepLoss.SNo = Convert.ToUInt16(rl.Sno);
            cardRepLoss.LossTime = rl.LossTime.Value.ToString("yyyy-MM-dd HH:mm:ss");
            cardRepLoss.OperType = rl.OperType.Value;
            cardRepLoss.OperNo = rl.OperNo;
            cardRepLoss.Reason = rl.Reason;
            cardRepLoss.OperationType = 0; // 0=created.

            OfflineRequest request = new OfflineRequest();
            request.CardRepLossInfoList = new List<ReportedLostCard>();
            request.CardRepLossInfoList.Add(cardRepLoss);

            var result = await dataCourier.SendRequest(request, HostOperationType.CardRepLoss);

            return result;
        }

        #region Blacklisted card / Unblocked card

        private async Task<SendResult> SendListedCardAsync(ListedCardInfo listedCardInfo)
        {
            var offlineRequest = new OfflineRequest();
            offlineRequest.BlackCardInfoList = new List<BlackCardInfo>();

            var blackCard = new BlackCardInfo();
            blackCard.Gid = listedCardInfo.Gid;
            blackCard.CardNo = listedCardInfo.CardNo;
            blackCard.AcctGid = listedCardInfo.AccountGid;
            blackCard.AcctId = listedCardInfo.AccountId;
            blackCard.CardType = listedCardInfo.CardType;
            blackCard.DiscountNo = listedCardInfo.DiscountNo;
            blackCard.Reason = listedCardInfo.Reason;
            blackCard.UploadFlag = listedCardInfo.UploadFlag;
            blackCard.BlackDate = listedCardInfo.ListedDate.ToString("yyyy-MM-dd HH:mm:ss");
            blackCard.OperationType = listedCardInfo.OperationType; // record added, removed or updated

            if (listedCardInfo.ListedType == ListedType.Base)
            {
                blackCard.BlackType = 0; //base black card
                offlineRequest.BlackCardInfoList.Add(blackCard);
            }
            else if (listedCardInfo.ListedType == ListedType.Add)
            {
                blackCard.BlackType = 1; //AddBlackCard
                offlineRequest.BlackCardInfoList.Add(blackCard);
            }
            else if (listedCardInfo.ListedType == ListedType.Deleted)
            {
                blackCard.BlackType = 2; //DeleteBlackCard
                offlineRequest.BlackCardInfoList.Add(blackCard);
            }

            var result = await dataCourier.SendRequest(offlineRequest, HostOperationType.ListedCard);

            return result;
        }

        #endregion

        private void StartDbMonitor()
        {
            if (mre != null)
                mre.WaitOne();
            else
                logger.Error("DbMonitor mre is null");

            if (dbMonitor != null)
                dbMonitor.Start();
            else
                logger.Error("DbMonitor instance is null");
        }

        private bool CheckTable()
        {
            using (var context = new SpsDbContext(_mySqlConn))
            {
                var conn = context.Database.GetDbConnection();

                if (conn.State.Equals(ConnectionState.Closed))
                {
                    conn.Open();
                }

                using (var command = conn.CreateCommand())
                {
                    command.CommandText = @"SELECT COUNT(*) FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 't_tableaudit'";

                    var exists = Convert.ToInt32(command.ExecuteScalar()) != 0;

                    logger.Info($"The table t_tableaudit exists? {exists}");

                    return exists;
                }
            }
        }

        private bool UploadExistingData()
        {
            if (dataCourier != null)
                dataCourier.GetToken();
            else
                logger.Error("DataCourier instance is null");

            if (!CheckTable())
            {
                //mre.Set();
                logger.Error("*** The table t_tableaudit does not exist! Consider initialize the table first!");
                return true;
            }

            //Existing data already being uploaded.
            if (System.IO.File.Exists("sps.txt"))
            {
                mre.Set();
                logger.Info("Existing data already uploaded!");
                return true;
            }

            var uploadAccount = UploadAccounts();
            var uploadCardInfo = UploadCardInfo();
            var uploadClosedCard = UploadClosedCards();
            var uploadRechRedt = UploadRechargeReductionRecords();
            var uploadCardTrx = UploadCardTransactions();

            if (uploadAccount && uploadCardInfo && uploadCardTrx && uploadRechRedt)
            {
                logger.Info("All existing records are uploaded successfully, WOW!");
            }

            if (uploadClosedCard)
                logger.Info("Uploaded all from t_cardlogout");

            System.IO.File.Create("sps.txt");
            mre.Set();

            return true;
        }

        #region Process existing records

        private IEnumerable<TAcctinfo> GetExistingAccounts()
        {
            using (var guardDbContext = new GuardDbContext())
            using (var context = new SpsDbContext(_mySqlConn))
            {
                var account = context.TTableaudit.FirstOrDefault(t => t.AccountCreated.HasValue && t.AccountCreated.Value != 0);

                if (account != null)
                {
                    var existingAccounts = context.TAcctinfo.Where(t => t.Gid < Convert.ToUInt64(account.AccountCreated.Value)).AsEnumerable();

                    if (guardDbContext.AccountUpload.Any())
                    {
                        var maxGid = guardDbContext.AccountUpload.Max(a => a.Gid);
                        logger.Info($"Already uploaded some Account info, max existing Gid: {maxGid}");
                        return existingAccounts.Where(a => Convert.ToInt64(a.Gid) > maxGid).ToList();
                    }

                    logger.Info($"Existing account count: {existingAccounts.Count()}");

                    return existingAccounts.ToList();
                }
                else
                {
                    logger.Info($"No new account created, get all the accounts from `t_acctinfo`");

                    return context.TAcctinfo.ToList();
                }
            }
        }

        private bool UploadAccounts()
        {
            var accounts = GetExistingAccounts();

            if (accounts == null)
            {
                logger.Error("** No existing account identified, skip uploading");
                return false;
            }

            logger.Info($"Existing account count: {accounts.Count()}");

            using (var context = new GuardDbContext())
            {
                foreach (var account in accounts)
                {
                    var result = SubmitAccountRecordAsync(account, HostOperationType.CreateAccount).Result;

                    context.Add(new AccountUpload
                    {
                        Gid = Convert.ToInt64(account.Gid),
                        AccountId = account.AcctId,
                        Operation = 1,
                        OperationTime = DateTime.Now,
                        Status = result.Success ? 0 : 1
                    });

                    context.SaveChanges();
                }

                var failedUploads = context.AccountUpload.Where(a => a.Status > 0);

                if (failedUploads != null && failedUploads.Count() > 0)
                {
                    logger.Info("Existing accounts uploaded with failures\n");
                    return false;
                }
                else
                {
                    logger.Info("Existing accounts uploaded successfully\n");
                    return true;
                }
            }
        }

        private IEnumerable<TCardinfo> GetExistingCards()
        {
            using (var guardDbContext = new GuardDbContext())
            using (var context = new SpsDbContext(_mySqlConn))
            {
                var cardInfo = context.TTableaudit.FirstOrDefault(t => t.CardInfoCreated.HasValue && t.CardInfoCreated.Value != 0);

                if (cardInfo != null)
                {
                    var cards = context.TCardinfo.Where(t => t.Gid < Convert.ToUInt64(cardInfo.CardInfoCreated.Value)).AsEnumerable();

                    if (guardDbContext.CardUpload.Any())
                    {
                        var maxGid = guardDbContext.CardUpload.Max(c => c.Gid);
                        logger.Info($"Already uploaded some Card info, max existing Gid: {maxGid}");
                        return cards.Where(c => Convert.ToInt64(c.Gid) > maxGid);
                    }

                    logger.Info($"Existing card info count: {cards.Count()}");
                    return cards.ToList();
                }
                else
                {
                    logger.Info("No new card info, get all card info from table `t_cardinfo`");

                    return context.TCardinfo.ToList();
                }
            }
        }

        private bool UploadCardInfo()
        {
            var cards = GetExistingCards();

            logger.Info($"Existing card count: {cards.Count()}");

            using (var context = new GuardDbContext())
            {
                foreach (var card in cards)
                {
                    var result = SubmitCardInfoAsync(card, HostOperationType.CreateCard).Result;

                    context.Add(new CardUpload
                    {
                        Gid = Convert.ToInt64(card.Gid),
                        CardNo = card.CardNo,
                        Operation = 1, //Insert
                        OperationTime = DateTime.Now,
                        Status = result.Success ? 0 : 1 //0 = OK, 1 = NOK
                    });

                    context.SaveChanges();
                }

                var failedUploads = context.CardUpload.Where(c => c.Status > 0);

                if (failedUploads != null && failedUploads.Count() > 0)
                {
                    logger.Info("Existing card uploaded with failures\n");
                    return false;
                }
                else
                {
                    logger.Info("Existing card uploaded successfully\n");
                    return true;
                }
            }
        }

        private IEnumerable<TCardlogout> GetExistingClosedCards()
        {
            using (var context = new SpsDbContext(_mySqlConn))
            {
                logger.Info("Get all card info from table `t_cardlogout`");
                return context.TCardlogout.ToList();
            }
        }

        private bool UploadClosedCards()
        {
            var cards = GetExistingClosedCards();

            logger.Info($"Existing closed card count: {cards.Count()}");

            using (var context = new GuardDbContext())
            {
                foreach (var c in cards)
                {
                    var card = ConvertClosedCardToCardInfo(c);
                    var result = SubmitCardInfoAsync(card, HostOperationType.CreateCard).Result;

                    context.Add(new CardUpload
                    {
                        Gid = Convert.ToInt64(card.Gid),
                        CardNo = card.CardNo,
                        Operation = 1, //Insert
                        OperationTime = DateTime.Now,
                        Status = result.Success ? 0 : 1 //0 = OK, 1 = NOK
                    });

                    context.SaveChanges();
                }

                var failedUploads = context.CardUpload.Where(c => c.Status > 0);

                if (failedUploads != null && failedUploads.Count() > 0)
                {
                    logger.Info("Existing cardlogout uploaded with failures\n");
                    return false;
                }
                else
                {
                    logger.Info("Existing cardlogout uploaded successfully\n");
                    return true;
                }
            }
        }

        private TCardinfo ConvertClosedCardToCardInfo(TCardlogout closedCard)
        {
            var cardInfo = new TCardinfo();
            cardInfo.AcctGid = closedCard.AcctGid;
            cardInfo.AcctId = closedCard.AcctId;
            cardInfo.CardNo = closedCard.CardNo;
            cardInfo.CardId = closedCard.CardId.HasValue ? closedCard.CardId.Value : 0;
            cardInfo.CardType = closedCard.CardType.HasValue? Convert.ToByte(closedCard.CardType.Value): (byte)1;
            cardInfo.Holder = closedCard.Holder;
            cardInfo.PhoneNo = closedCard.PhoneNo;
            cardInfo.KcDate = closedCard.KcDate;
            cardInfo.Startdate = closedCard.Startdate;
            cardInfo.CStatus = 2; // 2 = 注销卡
            return cardInfo;
        }

        private IEnumerable<TTrdinfo> GetExistingCardTrans()
        {
            using (var context = new SpsDbContext(_mySqlConn))
            {
                var trx = context.TTableaudit.FirstOrDefault(a => a.TransCreated.HasValue && a.TransCreated.Value != 0);

                if (trx != null)
                {
                    var transactions = context.TTrdinfo.Where(t => t.Gid < trx.Gid && t.Mon > 0);

                    logger.Info($"Existing card transactions count: {transactions.Count()}");

                    return transactions.ToList();
                }
                else
                {
                    return context.TTrdinfo.ToList();
                }
            }
        }

        private bool UploadCardTransactions()
        {
            logger.Info("Start to get existing card transactions");
            var transactions = GetExistingCardTrans();
            logger.Info("Pulled all the existing card transactions from database");

            using (var spsContext = new SpsDbContext(_mySqlConn))
            using (var context = new GuardDbContext())
            {
                foreach (var trans in transactions)
                {
                    FuelProductInfo fuelProduct = new FuelProductInfo();
                    var fuel = spsContext.TFuellist.FirstOrDefault(f => f.FuelNo == trans.CommId);

                    if (fuel != null)
                    {
                        fuelProduct.FuelName = fuel.Name;
                        fuelProduct.Barcode = GetBarcode(Convert.ToInt32(fuel.FuelNo)).ToString();
                    }

                    var result = SubmitCardTrxRecordAsync(trans, null, fuelProduct).Result;

                    context.TradeUpload.Add(new TradeUpload
                    {
                        Gid = Convert.ToInt64(trans.Gid),
                        Operation = 1,
                        OperationTime = DateTime.Now,
                        Status = result.Success ? 0 : 1
                    });

                    context.SaveChanges();
                }

                var failedUploads = context.TradeUpload.Where(c => c.Status > 0);

                if (failedUploads != null && failedUploads.Count() > 0)
                {
                    logger.Info("Existing card trx uploaded with failures\n");
                    return false;
                }
                else
                {
                    logger.Info("Existing card trx uploaded successfully\n");
                    return true;
                }
            }
        }

        private IEnumerable<TRechdebitRep> GetExistingRechargeReductions()
        {
            using (var context = new SpsDbContext(_mySqlConn))
            {
                var record = context.TTableaudit.FirstOrDefault(t => t.RechargeCreated.HasValue && t.RechargeCreated.Value != 0);

                if (record != null)
                {
                    var records = context.TRechdebitRep.Where(r => r.Gid <= Convert.ToUInt64(record.RechargeCreated.Value)).AsEnumerable();

                    logger.Info($"Existing recharge reduction operations count: {records.Count()}");

                    return records.ToList();
                }
                else
                {
                    logger.Info("No new recharge reduction records");
                    return context.TRechdebitRep.ToList();
                }
            }
        }

        private bool UploadRechargeReductionRecords()
        {
            var records = GetExistingRechargeReductions();

            using (var context = new GuardDbContext())
            {
                foreach (var r in records)
                {
                    var result = SubmitRechargeReductionRecord(r).Result;

                    context.RechargeUpload.Add(new RechargeUpload
                    {
                        Gid = Convert.ToInt64(r.Gid),
                        AccountId = r.AcctId,
                        CardNo = r.CardNo,
                        Operation = 1,
                        OperationTime = DateTime.Now,
                        Status = result.Success ? 0 : 1
                    });

                    context.SaveChanges();
                }

                var failedUploads = context.RechargeUpload.Where(r => r.Status > 0);

                if (failedUploads != null && failedUploads.Count() > 0)
                {
                    logger.Info("Existing recharge/reductions uploaded with failures\n");
                    return false;
                }
                else
                {
                    logger.Info("Existing recharge/reductions uploaded successfully\n");
                    return true;
                }
            }
        }

        #endregion

        private void HandleMissingCard(SpsDbContext spsDbContext, string cardNo)
        {
            if (!string.IsNullOrEmpty(cardNo))
            {
                var missingCard = spsDbContext.TCardinfo.FirstOrDefault(c => c.CardNo == cardNo);
                if (missingCard != null)
                {
                    var result = SubmitCardInfoAsync(missingCard, HostOperationType.CreateCard).Result;

                    logger.Info($"Submit required CardInfo for CardNo: {missingCard.CardNo}, GID: {missingCard.Gid}, Success? {result.Success}");

                    if (result.Code == 4)
                    {
                        HandleMissingAccount(spsDbContext, missingCard.AcctId);
                    }
                }
            }
        }

        private void HandleMissingAccount(SpsDbContext spsDbContext, string accountId)
        {
            if (!string.IsNullOrEmpty(accountId))
            {
                var missingAccount = spsDbContext.TAcctinfo.FirstOrDefault(a => a.AcctId == accountId);
                if (missingAccount != null)
                {
                    var result = SubmitAccountRecordAsync(missingAccount, HostOperationType.CreateAccount).Result;

                    logger.Info($"Submit required AccountInfo for AccoutId: {missingAccount.AcctId}, " +
                        $"GID: {missingAccount.Gid}, Success? {result.Success}");
                }
            }
        }
    }


    #region Config parameters

    public class DataCourierAppContructorParameterV1
    {
        public int Id { get; set; }

        public string Username { get; set; }

        public string Password { get; set; }

        public string AuthServiceBaseUrl { get; set; }

        public string AccountServiceBaseUrl { get; set; }

        public string AccountServiceRelativeUrl { get; set; }

        public string DeviceSN { get; set; }

        public int ScanInterval { get; set; }

        public List<FuelMappingV1> FuelMappingArr { get; set; }

        public int RetryCount { get; set; }

        public bool EnableSync { get; set; }

        public bool ExcludeCurrentSite { get; set; }

        public int SyncInterval { get; set; }

        public string CheckVersionRelativeUrl { get; set; }

        public string SyncDataRelativeUrl { get; set; }

        public SpsDbConnectionSetting ConnectionString { get; set; }
    }

    public class FuelMappingV1
    {
        public int Barcode { get; set; }

        public string FuelNo { get; set; }
    }

    public class SpsDbConnectionSetting
    {
        public string Server { get; set; }

        public int Port { get; set; }

        public string Username { get; set; }

        public string Password { get; set; }
    }

    #endregion
}