using System;
using System.Text;
using System.Collections.Generic;
using Dfs.WayneChina.SpsDbManager.Models;
using Dfs.WayneChina.SpsDbManager.ResultSet;
using System.Linq;
using MySql.Data.MySqlClient;
using Microsoft.EntityFrameworkCore;

namespace Dfs.WayneChina.SpsDbManager
{
    /// <summary>
    /// Manages the database interactions, mostly through stored procedures.
    /// </summary>
    public class SpsManager
    {
        #region Fields

        private System.Timers.Timer versionCheckTimer;

        private IList<DataVersion> currentVersion = null;
        private object syncObj = new object();

        private string connectionString = 
            "server=localhost;database=sps_db;uid=root;password=HS1205;treattinyasboolean=false;convert zero datetime=true;";

        #endregion

        #region Constructor

        public SpsManager(string connectionString)
        {
            this.connectionString = connectionString;
        }

        #endregion

        #region Internal version info check

        public void Start()
        {
            //StartVersionCheckTimer();
        }

        private void StartVersionCheckTimer()
        {
            versionCheckTimer = new System.Timers.Timer();
            versionCheckTimer.Interval = 5000;
            versionCheckTimer.Elapsed += VersionCheckTimerElapsed;
            versionCheckTimer.Enabled = true;
        }

        private void VersionCheckTimerElapsed(object sender, System.Timers.ElapsedEventArgs e)
        {
            GetDataVersionInternal();
        }

        #endregion

        #region InitialData

        public InitialData GetInitialData(int posId)
        {
            IList<InitialData> results = new List<InitialData>();

            using (var context = new SpsDbContext(connectionString))
            {
                results = context.LoadStoredProc(SpsDbRoutines.InitialData)
                    .WithSqlParam("Pos_Id", posId)
                    .ExecuteStoredProc<InitialData>();
            }

            return results.FirstOrDefault();
        }

        #endregion

        #region Register

        public IList<RegisterTerminalResult> RegisterTerminal(string terminalId)
        {
            IList<RegisterReqResult> results = new List<RegisterReqResult>();

            using (var context = new SpsDbContext(connectionString))
            {
                results = context.LoadStoredProc(SpsDbRoutines.RegisterTerminal)
                    .WithSqlParam("PayTermId", terminalId)
                    .ExecuteStoredProc<RegisterReqResult>();
            }

            IList<RegisterTerminalResult> registerResult = new List<RegisterTerminalResult>();
            foreach (var r in results)
            {
                registerResult.Add(new RegisterTerminalResult
                {
                    SystemKey = r.Keyword,
                    StationNameLength = r.StationLength,
                    StationName = r.SName,
                    RegisterResultCode = r.RegistState
                });
            }

            return registerResult;
        }

        #endregion

        #region Version info

        private IList<DataVersion> GetDataVersionInternal()
        {
            IList<VersionInfo> versionInfos = new List<VersionInfo>();

            using (var context = new SpsDbContext(connectionString))
            {
                versionInfos = context.LoadStoredProc(SpsDbRoutines.VersionInfo)
                    .WithSqlParam("", "")
                    .ExecuteStoredProc<VersionInfo>();
            }

            if (versionInfos == null || versionInfos != null && versionInfos.Count == 0)
            {
                return null;
            }

            IList<DataVersion> dataVersions = new List<DataVersion>();
            foreach (var item in versionInfos)
            {
                dataVersions.Add(new DataVersion
                {
                    VersionType = (VersionType)item.VersionId,
                    VersionNo = item.VerNo,
                    Effectivetime = item.GetEffectiveTime(),
                    ExpiredTime = item.GetExpiredTime(),
                    Remark = item.Remark
                });
            }

            //lock (syncObj)
            //{
            //    currentVersion = dataVersions;
            //}

            //Console.WriteLine($"Retrieving version info from database at: {DateTime.Now:yyyy-MM-dd HH:mm:ss}");

            return dataVersions;
        }

        public IList<DataVersion> GetDataVersions()
        {
            lock (syncObj)
            {
                if (currentVersion != null)
                {
                    return currentVersion;
                }
            }

            return GetDataVersionInternal();
        }

        private int UpdateVersionInfo(int versionId, int versionNo, string time)
        {
            using (var context = new SpsDbContext(connectionString))
            {
                var result = context.LoadStoredProc(SpsDbRoutines.UpdateVersionInfo)
                    .WithSqlParam("Version_Id", versionId)
                    .WithSqlParam("Version_No", versionNo)
                    .WithSqlParam("Effect_Time", time)
                    .ExecuteStoredProc<int>();

                return result.FirstOrDefault();
            }
        }

        public int UpdateGeneralInfoVersion()
        {
            return UpdateVersionInfo(1, 0, DateTime.Now.ToString("yyyyMMddHHmmss"));
        }

        #endregion

        #region Update Fuel Price

        public bool UpdateFuelPrice(string fuelProductNo, int price)
        {
            using (var context = new SpsDbContext(connectionString))
            {
                var fuel = context.TFuellist.FirstOrDefault(f => f.FuelNo == fuelProductNo);

                if (fuel != null)
                {
                    var currentPrice = fuel.Price;
                    fuel.OldPrice = currentPrice;
                    fuel.Price = price;

                    context.TFuellist.Update(fuel);
                    int result = context.SaveChanges();

                    if (result == 1)
                        return true;
                }
            }

            return false;
        }

        #endregion

        #region Update fuel price version

        public int UpdateFuelPriceVersion()
        {
            return UpdateVersionInfo(2, 0, DateTime.Now.ToString("yyyyMMddHHmmss"));
        }

        #endregion

        #region Validate card

        public IList<CardResult> GetCheckCardResult(string logicalCardNo, long physicalCardNo, int pumpId)
        {
            IList<CheckCardResult> checkCardResults = new List<CheckCardResult>();

            using (var context = new SpsDbContext(connectionString))
            {
                checkCardResults = context.LoadStoredProc(SpsDbRoutines.CheckCard)
                    .WithSqlParam("ASN", logicalCardNo)
                    .WithSqlParam("PhysCardNo", physicalCardNo)
                    .WithSqlParam("Pump_No", pumpId)
                    .ExecuteStoredProc<CheckCardResult>();
            }

            IList<CardResult> cardResults = new List<CardResult>();
            if (checkCardResults != null)
            {
                foreach (var item in checkCardResults)
                {
                    cardResults.Add(new CardResult
                    {
                        ReturnValue = item.ReturnVar,
                        CardStatus = (CardState)item.CStatus,
                        MaxAllowedAmount = item.Max_Mon,
                        MaxAmountPerTrx = item.OnceMaxPay < 0 ? 0 : Convert.ToUInt32(item.OnceMaxPay),
                        DiscountNo = Convert.ToUInt32(item.DiscountNo),
                        TrxRestrictionIndicator = Convert.ToInt32(item.BalPay_Day),
                        CardStateDescLen = item.CStateLen,
                        CardStateDescription = item.CState
                    });
                }
            }

            return cardResults;
        }

        public bool IsCardBlackListed(string cardNo)
        {
            IList<bool> results = new List<bool>();
            using (var context = new SpsDbContext(connectionString))
            {
                results = context.LoadStoredProc(SpsDbRoutines.CheckIfCardIsBlacklisted)
                    .WithSqlParam("Card_No", cardNo.TrimStart('0'))
                    .ExecuteStoredProc<bool>();
            }

            if (results.Count > 0)
            {
                return results[0];
            }

            return true;
        }

        #endregion

        #region Get amount after discount

        public DiscountedAmountResult GetDiscountedAmount(string cardNo, int amount, int volume, int unitPrice, int fuelProductCode)
        {
            IList<DiscountedAmountResult> results = new List<DiscountedAmountResult>();

            using (var context = new SpsDbContext(connectionString))
            {
                results = context.LoadStoredProc(SpsDbRoutines.CalculateDiscountedAmount)
                    .WithSqlParam("ASN", cardNo.TrimStart('0')) //"10010873"
                    .WithSqlParam("Mon", amount) //10139
                    .WithSqlParam("TrdVol", volume) //1305
                    .WithSqlParam("Price", unitPrice) //777
                    .WithSqlParam("Fuel_No", fuelProductCode) //"1030"
                    .ExecuteStoredProc<DiscountedAmountResult>();
            }

            return results.FirstOrDefault();
        }

        #endregion

        #region Account check and Insert auth info

        /// <summary>
        /// Gets the account info by card number.
        /// </summary>
        /// <param name="cardNo">The card number.</param>
        /// <returns></returns>
        public CardAccountInfoResult GetCardAccountInfo(string cardNo)
        {
            IList<CardAccountInfoResult> results = new List<CardAccountInfoResult>();

            using (var context = new SpsDbContext(connectionString))
            {
                results = context.LoadStoredProc(SpsDbRoutines.GetCardAccountInfo)
                    .WithSqlParam("Card_No", cardNo)
                    .ExecuteStoredProc<CardAccountInfoResult>();
            }

            return results?.FirstOrDefault();
        }

        /// <summary>
        /// Get the credits (or loyalty points) for the current card.
        /// </summary>
        /// <param name="fuelProductCode">The fuel product code, 1030, 1040 etc.</param>
        /// <param name="cardNo">The card number.</param>
        /// <param name="amount">The fueling amount.</param>
        /// <param name="volume">The fueling quantity.</param>
        /// <returns>The result which includes the credit amount.</returns>
        public CreditResult GetCredits(int fuelProductCode, string cardNo, int amount, int volume)
        {
            IList<CreditResult> results = new List<CreditResult>();

            using (var context = new SpsDbContext(connectionString))
            {
                results = context.LoadStoredProc(SpsDbRoutines.GetIcCardCredits)
                    .WithSqlParam("Fuel_No", fuelProductCode) //"1030"
                    .WithSqlParam("Card_No", cardNo) //"10010873"
                    .WithSqlParam("Money", amount) //10139
                    .WithSqlParam("Vol", volume) //1305
                    .ExecuteStoredProc<CreditResult>();
            }

            return results.FirstOrDefault();
        }

        /// <summary>
        /// Check if the transaction record already exists in the database table (t_trdinfo).
        /// </summary>
        /// <param name="fillingStartTime">TTCTime or fueling start time.</param>
        /// <param name="ttc">TTC (Transaction counter).</param>
        /// <param name="pumpNo">The pump number.</param>
        /// <param name="trxType">The transaction type.</param>
        /// <returns>Non-existing->returns false; Existing->returns true.</returns>
        public bool CheckIfTransactionExists(string fillingStartTime, int ttc, byte pumpNo, byte trxType)
        {
            IList<CheckTrxResult> results = new List<CheckTrxResult>();

            using (var context = new SpsDbContext(connectionString))
            {
                results = context.LoadStoredProc(SpsDbRoutines.CheckIfCardTrxExists)
                    .WithSqlParam("Ttc_Time", fillingStartTime)
                    .WithSqlParam("Pos_Ttc", ttc)
                    .WithSqlParam("Pump_No", pumpNo)
                    .WithSqlParam("Trade_Type", trxType)
                    .ExecuteStoredProc<CheckTrxResult>();
            }

            return results.First()?.FlagResult > 0;
        }

        public long AddTrade(int stationNo, string cardNo, string operCardNo, int payModeId, string payModeNo, byte trxType, string fuelProductCode,
            int unitPrice, int volume, int amount, int actualAmount, int cardBalance, byte cardType, int ctc, DateTime ttcTime,
            DateTime ttcEndTime, int ttc, ushort seqNo, int billNo, byte nozzleNo, byte pumpNo, long payTermId, long volumeTotal,
            int discountNo, int operatorId, byte posId, string psamAsn, uint psamTac, string psamTid, uint psamTtc, uint tac, uint gMac,
            uint tMac, int credit, int shiftNo, string carId, string lineNo, string carLicenseNo, byte pumpType)
        {
            using (MySqlConnection conn = new MySqlConnection(connectionString))
            {
                try
                {
                    string query = "INSERT INTO `sps_db`.`t_trdinfo` " +
                        "(`SNo`,`CardNo`, `OperCardNo`, `PaymodeID`,`PaymodeNo`, `TrdType`, `CommID`, `PRC`, `VOL`, `MON`, `RealMON`, `CardBal`,`CardType`, " +
                        "`CTC`, `TTCTime`, `TTCTimeEnd`,`TTC`, `SeqNo`,`BillNo`, `NozNo`, `PumpNo`,`PayTemID`, `EndPumpID`, `DiscountNo`, " +
                        "`Operator`, `POSID`,`PSAMASN`,`PSAMtac`,`PSAMTID`,`PSAMTTC`,`TAC`,`GMAC`,`TMAC`,`Integral`,`ShiftNo`,`CarID`,`LineNo`," +
                        "`CarLicsNo`,`PumpType`) " +
                        "VALUES(@stationno, @asn, @operAsn, @paytype, @paymode_no, @trade_type, @fuelcode, @prc, @vol, @amn, @realamn, @bal, @card_type, " +
                        "@ctc, @time, @endtime, @posttc, @seqno, @billno, @nozzleno, @pump_no, @terminalid, @totalvol, @discountno, " +
                        "@userno, @pos_id, @trd_psamasn, @trd_psamtac, @trd_psamtid, @trd_psamttc, @trd_tac, @trd_gmac, @trd_tmac, @trd_integral, " +
                        "@shift_no, @carID, @lineNo, @carLicsNo, @pumpType); SELECT LAST_INSERT_ID();";

                    if (conn.State != System.Data.ConnectionState.Open)
                    {
                        conn.Open();
                    }

                    using (MySqlCommand cmd = new MySqlCommand(query, conn))
                    {
                        cmd.Parameters.Add("@stationNo", MySqlDbType.UInt32).Value = stationNo;
                        cmd.Parameters.Add("@asn", MySqlDbType.VarChar).Value = cardNo;
                        cmd.Parameters.Add("@operAsn", MySqlDbType.VarChar).Value = operCardNo;
                        cmd.Parameters.Add("@paytype", MySqlDbType.UInt32).Value = payModeId;
                        cmd.Parameters.Add("@paymode_no", MySqlDbType.VarChar).Value = payModeNo;
                        cmd.Parameters.Add("@trade_type", MySqlDbType.UInt32).Value = trxType;
                        cmd.Parameters.Add("@fuelcode", MySqlDbType.VarChar).Value = fuelProductCode;
                        cmd.Parameters.Add("@prc", MySqlDbType.UInt32).Value = unitPrice;
                        cmd.Parameters.Add("@vol", MySqlDbType.UInt32).Value = volume;
                        cmd.Parameters.Add("@amn", MySqlDbType.UInt32).Value = amount;
                        cmd.Parameters.Add("@realamn", MySqlDbType.UInt32).Value = actualAmount;
                        cmd.Parameters.Add("@bal", MySqlDbType.UInt32).Value = cardBalance;
                        cmd.Parameters.Add("@card_type", MySqlDbType.UByte).Value = cardType;

                        cmd.Parameters.Add("@ctc", MySqlDbType.UInt32).Value = ctc;
                        cmd.Parameters.Add("@time", MySqlDbType.DateTime).Value = ttcTime;
                        cmd.Parameters.Add("@endtime", MySqlDbType.DateTime).Value = ttcEndTime;
                        cmd.Parameters.Add("@posttc", MySqlDbType.UInt32).Value = ttc;
                        cmd.Parameters.Add("@seqno", MySqlDbType.UInt32).Value = seqNo;
                        cmd.Parameters.Add("@billno", MySqlDbType.UInt32).Value = billNo;

                        cmd.Parameters.Add("@nozzleno", MySqlDbType.UInt32).Value = nozzleNo;
                        cmd.Parameters.Add("@pump_no", MySqlDbType.UInt32).Value = pumpNo;
                        cmd.Parameters.Add("@terminalid", MySqlDbType.UInt64).Value = payTermId;
                        cmd.Parameters.Add("@totalvol", MySqlDbType.UInt64).Value = volumeTotal;
                        cmd.Parameters.Add("@discountno", MySqlDbType.UInt32).Value = discountNo;
                        cmd.Parameters.Add("@userno", MySqlDbType.UInt32).Value = operatorId;
                        cmd.Parameters.Add("@pos_id", MySqlDbType.UInt32).Value = posId;

                        cmd.Parameters.Add("@trd_psamasn", MySqlDbType.VarChar).Value = psamAsn;
                        cmd.Parameters.Add("@trd_psamtac", MySqlDbType.UInt32).Value = psamTac;
                        cmd.Parameters.Add("@trd_psamtid", MySqlDbType.VarChar).Value = psamTid;
                        cmd.Parameters.Add("@trd_psamttc", MySqlDbType.UInt32).Value = psamTtc;
                        cmd.Parameters.Add("@trd_tac", MySqlDbType.UInt32).Value = tac;
                        cmd.Parameters.Add("@trd_gmac", MySqlDbType.UInt32).Value = gMac;
                        cmd.Parameters.Add("@trd_tmac", MySqlDbType.UInt32).Value = tMac;
                        cmd.Parameters.Add("@trd_integral", MySqlDbType.UInt32).Value = credit;
                        cmd.Parameters.Add("@shift_no", MySqlDbType.UInt32).Value = shiftNo;
                        cmd.Parameters.Add("@carID", MySqlDbType.VarChar).Value = carId;
                        cmd.Parameters.Add("@lineNo", MySqlDbType.VarChar).Value = lineNo;
                        cmd.Parameters.Add("@carLicsNo", MySqlDbType.VarChar).Value = carLicenseNo;
                        cmd.Parameters.Add("@pumpType", MySqlDbType.UInt32).Value = pumpType;

                        object result = cmd.ExecuteScalar();
                        return long.Parse(result.ToString()); //(long)result;
                    }
                }
                catch (MySqlException ex)
                {
                    Console.WriteLine("Error in adding mysql row. Error: " + ex.Message);
                }
                catch (Exception ex)
                {
                    Console.WriteLine($"Exception: {ex.ToString()}");
                }
                finally
                {
                    conn.Close();
                }
            }

            return -1;
        }

        public int AddTrxInfo(int stationNo, string cardNo, int payModeId, string payModeNo, byte trxType, string fuelProductCode,
            int unitPrice, int volume, int amount, int actualAmount, int cardBalance, byte cardType, int ctc, DateTime ttcTime,
            DateTime ttcEndTime, int ttc, ushort seqNo, int billNo, byte nozzleNo, byte pumpNo, long payTermId, long volumeTotal,
            int discountNo, int operatorId, byte posId, string psamAsn, int psamTac, string psamTid, int psamTtc, int tac, int gMac,
            int tMac, int credit, int shiftNo, string carId, string lineNo, string carLicenseNo, byte pumpType)
        {
            TTrdinfo trdinfo = new TTrdinfo
            {
                Sno = stationNo,
                CardNo = cardNo,
                PaymodeId = payModeId,
                PaymodeNo = payModeNo,
                TrdType = trxType,
                CommId = fuelProductCode,
                Prc = unitPrice,
                Vol = volume,
                Mon = amount,
                RealMon = actualAmount,
                CardBal = cardBalance,
                CardType = cardType,
                Ctc = ctc,
                Ttctime = ttcTime,
                TtctimeEnd = ttcEndTime,
                Ttc = ttc,
                SeqNo = seqNo,
                BillNo = billNo,
                NozNo = nozzleNo,
                PumpNo = pumpNo,
                PayTemId = payTermId,
                EndPumpId = volumeTotal,
                DiscountNo = discountNo,
                Operator = Convert.ToString(operatorId),
                Posid = posId,
                Psamasn = psamAsn,
                Psamtac = psamTac,
                Psamtid = psamTid,
                Psamttc = psamTtc,
                Tac = tac,
                Gmac = gMac,
                Tmac = tMac,
                Integral = credit,
                ShiftNo = shiftNo,
                CarId = carId,
                LineNo = lineNo,
                CarLicsNo = carLicenseNo,
                PumpType = pumpType
            };

            using (var context = new SpsDbContext(connectionString))
            {
                try
                {
                    context.TTrdinfo.Add(trdinfo);
                    int result = context.SaveChanges();
                    return result;
                }
                catch (Exception ex)
                {

                }
            }

            return -1;
        }

        /// <summary>
        /// Update the card info after transaction is inserted.
        /// </summary>
        /// <param name="cardNo">The card number.</param>
        /// <param name="amount">The amount of the current fueling.</param>
        /// <param name="cardBalance">The balance of the current card.</param>
        /// <param name="cardType">The card type, customer card, operator card...</param>
        /// <param name="credit">The credits of the transaction earned.</param>
        /// <param name="cardAppType">Card application type, e.g. contactless card, CPU card...</param>
        /// <returns>The rows affected.</returns>
        public int UpdateCardInfo(string cardNo, int amount, int cardBalance, byte cardType, int credit, byte cardAppType)
        {
            using (var context = new SpsDbContext(connectionString))
            {
                int affectedRowCount = context.LoadStoredProc(SpsDbRoutines.UpdateCardInfo)
                    .WithSqlParam("ASN", cardNo) //"10010873"
                    .WithSqlParam("RealAmn", amount) //4444
                    .WithSqlParam("Bal", cardBalance) //83694645
                    .WithSqlParam("Card_Type", cardType) //3
                    .WithSqlParam("Trd_Integral", credit) //0
                    .WithSqlParam("Card_App", cardAppType) //1
                    .ExecuteStoredProc();

                return affectedRowCount;
            }
        }

        public bool InsertAuthInfo(int posId, int terminalId, string cardNo, int amount, int volume, string fillingStartTime, 
            string fillingEndTime, int posTtc, ushort seqNo, int accountType, int fuelProductCode, int unitPrice, int ctc, 
            byte paymentType,byte pumpNo, byte nozzleNo, int cardBalance, int cardType, uint discountNo, byte cardState, int volumeTotal)
        {
            using (var context = new SpsDbContext(connectionString))
            {
                int affectedRowCount = context.LoadStoredProc(SpsDbRoutines.InsertAuthInfo)
                    .WithSqlParam("Pos_Id", posId) //1
                    .WithSqlParam("Term", terminalId) //0
                    .WithSqlParam("ASN", cardNo) //"10010873"
                    .WithSqlParam("MaxMoney", amount) //10139
                    .WithSqlParam("MaxVol", volume) //1305
                    .WithSqlParam("DisTime", fillingStartTime) //"20190408095056"
                    .WithSqlParam("DisEndTime", fillingEndTime) //"20190408095119"
                    .WithSqlParam("PosTtc", posTtc) //4696
                    .WithSqlParam("SeqNo", seqNo) //0
                    .WithSqlParam("AcountType", accountType) //80
                    .WithSqlParam("FuelCode", fuelProductCode) //1030
                    .WithSqlParam("FuelPrc", unitPrice) //777
                    .WithSqlParam("Ctc", ctc) //511
                    .WithSqlParam("PayType", paymentType) //100
                    .WithSqlParam("Pump_No", pumpNo) //3
                    .WithSqlParam("NozzleNo", nozzleNo) //3
                    .WithSqlParam("Bal", cardBalance) //84043503
                    .WithSqlParam("CardType", cardType) //3
                    .WithSqlParam("DiscountNo", discountNo) //0
                    .WithSqlParam("State", cardState) //1
                    .WithSqlParam("TotalVol", volumeTotal) //61231111
                    .ExecuteStoredProc();

                if (affectedRowCount == 1)
                {
                    return true;
                }

                return false;
            }
        }

        #endregion

        #region Delete auth info

        public DeleteAuthInfoResult DeleteAuthInfo(byte pumpNo, int posTtc, string ttcTime)
        {
            IList<DeleteAuthInfoResult> results = new List<DeleteAuthInfoResult>();

            using (var context = new SpsDbContext(connectionString))
            {
                results = context.LoadStoredProc(SpsDbRoutines.DeleteAuthInfo)
                    .WithSqlParam("Pump_No", pumpNo) //3
                    .WithSqlParam("PosTtc", posTtc) //4696
                    .WithSqlParam("TtcTimes", ttcTime) //"20190408095056"
                    .ExecuteStoredProc<DeleteAuthInfoResult>();
            }

            return results?.FirstOrDefault();
        }

        #endregion

        #region Insert gray info

        public bool InsertGrayInfo(string cardNo, int payType, byte trdType, string productCode, ushort price, int volume, int amount,
            int payAmount, int cardBalance, ushort ctc, string time, string endTime, uint ttc, ushort seqNo, byte nozzleNo, byte pumpNo,
            string terminalId, ulong volumeTotal, ushort discountNo, string psamAsn, uint psamTac, string psamTid, uint psamTtc, uint tac,
            uint gmac, uint tmac, byte pumpType)
        {
            using (var context = new SpsDbContext(connectionString))
            {
                var affectedRowCount = context.LoadStoredProc(SpsDbRoutines.AddGrayInfo)
                    .WithSqlParam("asn", cardNo)
                    .WithSqlParam("payType", payType)
                    .WithSqlParam("tradeType", trdType)
                    .WithSqlParam("fuelCode", productCode)
                    .WithSqlParam("prc", price)
                    .WithSqlParam("vol", volume)
                    .WithSqlParam("amn", amount)
                    .WithSqlParam("realAmn", payAmount)
                    .WithSqlParam("bal", cardBalance)
                    .WithSqlParam("grayctc", ctc)
                    .WithSqlParam("time", time)
                    .WithSqlParam("endTime", endTime)
                    .WithSqlParam("posttc", ttc)
                    .WithSqlParam("SeqNo", seqNo)
                    .WithSqlParam("NozzleNo", nozzleNo)
                    .WithSqlParam("PumpNo", pumpNo) 
                    .WithSqlParam("TerminalId", terminalId)
                    .WithSqlParam("TotalVol", volumeTotal)
                    .WithSqlParam("DiscountNo", discountNo)
                    .WithSqlParam("Trd_PSAMASN", psamAsn)
                    .WithSqlParam(MySqlDbType.UInt32, "Trd_PSAMtac", psamTac)
                    .WithSqlParam("Trd_PSAMTID", psamTid)
                    .WithSqlParam(MySqlDbType.UInt32, "Trd_PSAMTTC", psamTtc)
                    .WithSqlParam(MySqlDbType.UInt32, "Trd_TAC", tac)
                    .WithSqlParam(MySqlDbType.UInt32, "Trd_GMAC", gmac)
                    .WithSqlParam(MySqlDbType.UInt32, "Trd_TMAC", tmac)
                    .WithSqlParam("Pump_Type", pumpType)
                    .ExecuteStoredProc();

                if (affectedRowCount == 1)
                    return true;

                return false;
            }
        }

        #endregion

        #region Free gray card

        public bool ReleaseGrayCard(string cardNo, uint grayCtc, string ttcTime)
        {
            using (var context = new SpsDbContext(connectionString))
            {
                var result = context.LoadStoredProc(SpsDbRoutines.ReleaseGrayCard)
                    .WithSqlParam("asn", cardNo)
                    .WithSqlParam("grayCtc", grayCtc)
                    .WithSqlParam("ttcTimes", ttcTime)
                    .ExecuteStoredProc();

                //0 = No such record or success
                return result == 0;
            }
        }

        #endregion

        #region Select gray info

        public GrayInfoResult SelectGrayInfo(string cardNo, int ctc, string ttcTime)
        {
            using (var context = new SpsDbContext(connectionString))
            {
                var result = context.LoadStoredProc(SpsDbRoutines.SelectGrayInfo)
                    .WithSqlParam("asn", cardNo)
                    .WithSqlParam("grayCtc", ctc)
                    .WithSqlParam("ttcTimes", ttcTime)
                    .ExecuteStoredProc<GrayInfoResult>();

                return result?.FirstOrDefault();
            }
        }

        #endregion

        #region Get Pump Info

        public IList<PumpInfoResult> GetPumpInfo(byte pumpId)
        {
            IList<PumpInfoResult> pumpInfos = new List<PumpInfoResult>();
            using (var context = new SpsDbContext(connectionString))
            {
                pumpInfos = context.LoadStoredProc(SpsDbRoutines.PumpInfo)
                    .WithSqlParam("PumpNo", pumpId)
                    .ExecuteStoredProc<PumpInfoResult>();

                return pumpInfos;
            }
        }

        #endregion

        #region Fuel price config

        public IList<FuelPriceConfigResult> GetFuelPriceConfig()
        {
            using (var context = new SpsDbContext(connectionString))
            {
                var result = context.LoadStoredProc(SpsDbRoutines.FuelPriceConfig)
                    .ExecuteStoredProc<FuelPriceConfigResult>();

                return result;
            }
        }

        #endregion

        #region Fuel name setup

        public Dictionary<string, string> GetFuelNames()
        {
            Dictionary<string, string> dict = new Dictionary<string, string>();
            var fuels = GetFuelPriceConfig();
            foreach (var item in fuels)
            {
                dict.Add(item.FurlNo, item.FuelName);
            }

            return dict;
        }

        #endregion

        #region Get current fuel prices (cache)

        public Dictionary<string, uint> GetCurrentFuelPrices()
        {
            Dictionary<string, uint> dict = new Dictionary<string, uint>();
            var fuels = GetFuelPriceConfig();
            foreach (var item in fuels)
            {
                dict.Add(item.FurlNo, item.Price);
            }

            return dict;
        }

        #endregion

        #region Fuel price change for pump

        public IList<FuelPriceChangeConfigResult> GetFuelPriceChangeConfig(int pumpId)
        {
            using (var context = new SpsDbContext(connectionString))
            {
                var result = context.LoadStoredProc(SpsDbRoutines.GetFuelPriceChange)
                    .WithSqlParam("PumpNodeId", pumpId)
                    .ExecuteStoredProc<FuelPriceChangeConfigResult>();

                return result;
            }
        }

        #endregion

        #region Whitelist/(Newly-added, Newly-deleted, Base)Blacklist cards

        public IList<ListedCard> GetBaseBlacklistedCards(CardAppType cardAppType)
        {
            return GetListedCards(1, cardAppType);
        }

        public IList<ListedCard> GetNewlyAddedBlacklistedCards(CardAppType cardAppType)
        {
            return GetListedCards(2, cardAppType);
        }

        public IList<ListedCard> GetNewlyDeletedBlacklistedCards(CardAppType cardAppType)
        {
            return GetListedCards(3, cardAppType);
        }

        public IList<ListedCard> GetWhitelistedCards(CardAppType cardAppType)
        {
            return GetListedCards(4, cardAppType);
        }

        public IList<ListedCard> GetListedCards(int indexNo, CardAppType cardAppType)
        {
            using (var context = new SpsDbContext(connectionString))
            {
                var result = context.LoadStoredProc(SpsDbRoutines.GetListedCards)
                    .WithSqlParam("IndexNo", indexNo)
                    .WithSqlParam("Card_App", (int)cardAppType)
                    .ExecuteStoredProc<ListedCard>();

                return result;
            }
        }

        #endregion

        #region Pump work mode

        public byte GetPumpWorkMode(byte pumpId)
        {
            using (var context = new SpsDbContext(connectionString))
            {
                var pump = context.TPumpinfo
                    .AsNoTracking()
                    .Where(p => p.LgNode == pumpId)
                    .Select(f => new PumpMode
                    {
                        PumpId = f.LgNode,
                        WorkMode = Convert.ToUInt16(f.Mode.Value)
                    })
                    .FirstOrDefault();

                if (pump != null)
                    return Convert.ToByte(pump.WorkMode);

                return Convert.ToByte(-1);
            }
        }

        #endregion

        #region Station Info

        public TStationInfo GetStationInfo()
        {
            using var context = new SpsDbContext(connectionString);
            return context.TStationInfo.FirstOrDefault();
        }

        public void UpdateStationInfo(int stationNo, string name)
        {
            using (var context = new SpsDbContext(connectionString))
            {
                var currentStationInfo = context.TStationInfo.FirstOrDefault();
                try
                {
                    if (currentStationInfo == null)
                    {
                        TStationInfo stationInfo = new TStationInfo();
                        stationInfo.Sno = stationNo;
                        stationInfo.Sname = name;
                        context.TStationInfo.Add(stationInfo);
                        context.SaveChanges();
                    }
                    else
                    {
                        currentStationInfo.Sno = stationNo;
                        currentStationInfo.Sname = name;
                        context.SaveChanges();
                    }
                }
                catch (Exception ex)
                {

                }
            }
        }

        #endregion

        #region Database interactions - Forecourt config

        // TANK

        #region Tank

        public bool AddVirtualTank(byte tankId, string productCode)
        {
            using (var context = new SpsDbContext(connectionString))
            {
                TTanklist tanklist = new TTanklist();

                tanklist.TankId = tankId;
                tanklist.FuelNo = productCode;
                tanklist.ProbeId = "";
                tanklist.ProbeAddr = "";

                context.TTanklist.Add(tanklist);
                var result = context.SaveChanges();
                Console.WriteLine($"parameterized add: {result} rows affected!");

                return result == 1 ? true : false;
            }
        }

        public bool UpdateTankInfo(byte tankId, string productCode)
        {
            using (var context = new SpsDbContext(connectionString))
            {
                var tank = context.TTanklist.FirstOrDefault(t => t.TankId == tankId);
                if (tank != null)
                {
                    tank.FuelNo = productCode;
                    var result = context.SaveChanges();

                    Console.WriteLine($"Update tank: {result} rows affected!");

                    return result == 1 ? true : false;
                }
            }

            return false;
        }

        #endregion

        // FUEL

        #region Fuel

        public void InitializeFuelConfig(TFuellist[] fuellists)
        {
            using (var context = new SpsDbContext(connectionString))
            {
                context.TFuellist.RemoveRange(context.TFuellist);
                var result = context.SaveChanges();

                Console.WriteLine($"{result} rows removed!");

                if (context.TFuellist.Count() == 0)
                {
                    Console.WriteLine("All fuels deleted");
                }

                AddFuel(context, fuellists);

                var results = context.LoadStoredProc(SpsDbRoutines.UpdateVersionInfo)
                    .WithSqlParam("Version_Id", 2) // 2 means fuel price
                    .WithSqlParam("Version_No", 0) // With Version No set to '0', it will bump the version number up by 1.
                    .WithSqlParam("Effect_Time", DateTime.Now.ToString("yyyyMMddHHmmss"))
                    .ExecuteStoredProc<int>()
                    .FirstOrDefault();

                Console.WriteLine($"Update fuel price version, result code: {results}");
            }
        }

        static void AddFuel(SpsDbContext context, TFuellist[] fuellists)
        {
            foreach (var fuel in fuellists)
            {
                if (context.TFuellist.Count(f => f.FuelNo == fuel.FuelNo) == 0)
                {
                    context.TFuellist.Add(fuel);
                }
            }

            var result = context.SaveChanges();

            Console.WriteLine($"Adding fuels to TFuellist table, {result} rows affected");
        }

        #endregion

        // PUMP

        #region Pumps

        public bool AddUpdatePump(int pumpId, int tankNo, int posId, string dispenserNo, int protocolNo, int protocolType, 
            string ipAddress, int portId, int workMode, int maxUnpaidTrans, int checkOutMode, int nodeId, int subNodeId, int nozzleNo,
            int pumpType, uint maxAmount, uint maxVolume)
        {
            IList<PumpNozzleInfoOperateResult> results = new List<PumpNozzleInfoOperateResult>();

            using (var context = new SpsDbContext(connectionString))
            {
                var currentPump = context.TPumpinfo.FirstOrDefault(p => p.LgNode == pumpId);

                // An existing pump
                if (currentPump != null)
                {
                    return UpdatePumpInfo(pumpId, tankNo, posId, dispenserNo, protocolNo, protocolType, ipAddress, portId, workMode,
                        maxUnpaidTrans, checkOutMode, nodeId, subNodeId, nozzleNo, pumpType, maxAmount, maxVolume);
                }

                // A new pump
                results = context.LoadStoredProc(SpsDbRoutines.InsertPumpInfo)
                   .WithSqlParam("pump_id", pumpId)
                   .WithSqlParam("dsp_no", dispenserNo)
                   .WithSqlParam("tank_no", tankNo)
                   .WithSqlParam("pos_id", posId)
                   .WithSqlParam("protocol_no", protocolNo)
                   .WithSqlParam("prot_type", protocolType)
                   .WithSqlParam("ip_address", ipAddress)
                   .WithSqlParam("port_id", portId)
                   .WithSqlParam("work_mode", workMode)
                   .WithSqlParam("MaxUnpaidTrans", maxUnpaidTrans)
                   .WithSqlParam("checkOutMode", checkOutMode)
                   .WithSqlParam("node_id", nodeId)
                   .WithSqlParam("subNode_id", subNodeId)
                   .WithSqlParam("nozzle_num", nozzleNo)
                   .WithSqlParam("pump_type", pumpType)
                   .WithSqlParam("max_mon", maxAmount)
                   .WithSqlParam("max_vol", maxVolume)
                   .ExecuteStoredProc<PumpNozzleInfoOperateResult>();
            }

            if (results.Count == 1)
            {
                // 0 = Success, 2 = Pump exists
                return results.First().Result == 0 ? true : false;
            }

            return false;
        }

        public bool UpdatePumpInfo(int pumpId, int tankNo, int posId, string dispenserNo, int protocolNo, int protocolType, 
            string ipAddress, int portId, int workMode, int maxUnpaidTrans, int checkOutMode, int nodeId, int subNodeId, int nozzleNo,
            int pumpType,  uint maxAmount, uint maxVolume)
        {
            IList<PumpNozzleInfoOperateResult> results = new List<PumpNozzleInfoOperateResult>();

            using (var context = new SpsDbContext(connectionString))
            {
                results = context.LoadStoredProc(SpsDbRoutines.UpdatePumpInfo)
                   .WithSqlParam("pump_id", pumpId)
                   .WithSqlParam("tank_no", tankNo)
                   .WithSqlParam("pos_id", posId)
                   .WithSqlParam("dsp_no", dispenserNo)
                   .WithSqlParam("protocol_no", protocolNo)
                   .WithSqlParam("prot_type", protocolType)
                   .WithSqlParam("ip_address", ipAddress)
                   .WithSqlParam("port_id", portId)
                   .WithSqlParam("work_mode", workMode)
                   .WithSqlParam("MaxUnpaidTrans", maxUnpaidTrans)
                   .WithSqlParam("checkOutMode", checkOutMode)
                   .WithSqlParam("node_id", nodeId)
                   .WithSqlParam("subNode_id", subNodeId)
                   .WithSqlParam("nozzle_num", nozzleNo)
                   .WithSqlParam("pump_type", pumpType)
                   .WithSqlParam("max_mon", maxAmount)
                   .WithSqlParam("max_vol", maxVolume)
                   .ExecuteStoredProc<PumpNozzleInfoOperateResult>();
            }

            if (results.Count == 1)
            {
                // 0 = Success, 2 = Pump exists
                return results.First().Result == 0 ? true : false;
            }

            return false;
        }

        #endregion

        // NOZZLE

        #region Nozzle

        public bool AddUpdateNozzle(int logicalNozzleNo, int physicalId, int tankId, int pumpId, int? useState, 
            string fuelType, string carIdenAddress)
        {
            IList<PumpNozzleInfoOperateResult> results = new List<PumpNozzleInfoOperateResult>();

            using (var context = new SpsDbContext(connectionString))
            {
                // An existing nozzle
                var currentNozzle = context.TNozzleinfo.FirstOrDefault(n => n.LogicId == logicalNozzleNo);
                if (currentNozzle != null)
                {
                    return UpdateNozzleInfo(logicalNozzleNo, physicalId, tankId, pumpId, useState, fuelType, carIdenAddress);
                }

                // A new nozzle
                results = context.LoadStoredProc(SpsDbRoutines.InsertNozzleInfo)
                    .WithSqlParam("logic_id", logicalNozzleNo)
                    .WithSqlParam("physical_id", physicalId)
                    .WithSqlParam("tank_id", tankId)
                    .WithSqlParam("pump_id", pumpId)
                    .WithSqlParam("usestate", useState)
                    .WithSqlParam("oiltype_id", fuelType)
                    .WithSqlParam("cariden_addr", carIdenAddress)
                    .ExecuteStoredProc<PumpNozzleInfoOperateResult>();
            }

            foreach (var item in results)
            {
                Console.WriteLine($"Result: {item.Result}");
            }

            if (results.Count == 1)
            {
                return results.First().Result == 0 ? true : false;
            }

            return false;
        }


        public bool UpdateNozzleInfo(int logicalNozzleNo, int physicalId, int tankId, int pumpId, int? useState,
            string fuelType, string carIdenAddress)
        {
            IList<PumpNozzleInfoOperateResult> results = new List<PumpNozzleInfoOperateResult>();

            using (var context = new SpsDbContext(connectionString))
            {
                results = context.LoadStoredProc(SpsDbRoutines.UpdateNozzleInfo)
                    .WithSqlParam("logic_id", logicalNozzleNo)
                    .WithSqlParam("physical_id", physicalId)
                    .WithSqlParam("tank_id", tankId)
                    .WithSqlParam("pump_id", pumpId)
                    .WithSqlParam("usestate", useState)
                    .WithSqlParam("oiltype_id", fuelType)
                    .WithSqlParam("cariden_addr", carIdenAddress)
                    .ExecuteStoredProc<PumpNozzleInfoOperateResult>();
            }

            foreach (var item in results)
            {
                Console.WriteLine($"Result: {item.Result}");
            }

            if (results.Count == 1)
            {
                return results.First().Result == 0 ? true : false;
            }

            return false;
        }

        #endregion

        // TERMINAL

        #region Terminal

        public void UpdateTerminals(TPayterm[] payterms)
        {
            using (var context = new SpsDbContext(connectionString))
            {
                foreach (var term in payterms)
                {
                    var terminal = context.TPayterm.FirstOrDefault(t => t.PayDevId == term.PayDevId);

                    if (terminal.PayDevId == term.PayDevId && terminal.PayType == term.PayType && terminal.Protocl == term.Protocl 
                        && terminal.Address == term.Address)
                    {
                        continue;
                    }

                    if (terminal != null)
                    {
                        terminal.PayType = term.PayType;
                        terminal.Protocl = term.Protocl;
                        terminal.Address = term.Address;

                        var result = context.SaveChanges();

                        Console.WriteLine($"Update terminal, Id: {term.PayDevId}, pay type: {term.PayType}, protocol: {term.Protocl}, " +
                            $"address: {term.Address}, {result} rows affected");
                    }
                    else
                    {
                        context.Add(term);
                        var result = context.SaveChanges();
                        Console.WriteLine($"Add terminal, Id: {term.PayDevId}, pay type: {term.PayType}, protocol: {term.Protocl}, " +
                            $"address: {term.Address}, {result} rows affected");
                    }
                }
            }
        }

        #endregion

        // Retrieve existing settings

        #region Get forecourt configuration (pumps, nozzles, terminals, etc.)

        public void GetAllNozzleInfo()
        {
            IList<NozzleInfoResult> nozzleInfoResults = new List<NozzleInfoResult>();

            using (var context = new SpsDbContext(connectionString))
            {
                nozzleInfoResults = context.LoadStoredProc(SpsDbRoutines.GetAllNozzleInfo)
                    .WithSqlParam("", "")
                    .ExecuteStoredProc<NozzleInfoResult>();
            }

            foreach (var item in nozzleInfoResults)
            {
                Console.Write($"Logic Id: {item.Logic_Id} ");
                Console.Write($"Physical Id: {item.Physical_Id} ");
                Console.Write($"Tank Id: {item.Tank_Id} ");
                Console.Write($"Pump Id: {item.Pump_Id} ");
                Console.Write($"UseState: {item.UseState} ");
                Console.Write($"OilType Id: {item.OilType_Id} ");
                Console.WriteLine($"Car Iden Addr: {item.CarIdenAddr} ");
            }
        }

        public void GetAllTankInformation()
        {
            IList<TankInfoResult> tankInfoResults = new List<TankInfoResult>();
            using (var context = new SpsDbContext(connectionString))
            {
                tankInfoResults = context.LoadStoredProc(SpsDbRoutines.GetAllTankInfo)
                    .WithSqlParam("", "")
                    .ExecuteStoredProc<TankInfoResult>();
            }

            foreach (var item in tankInfoResults)
            {
                Console.Write($"Tank No: {item.Tank_No} ");
                Console.Write($"Tank Height: {item.Tank_Height} ");
                Console.Write($"Fuel No: {item.Fuel_No} ");
                Console.Write($"Tank Max Height: {item.Tank_MaxHeight} ");
                Console.Write($"Tank Min Height: {item.Tank_MinHeight} ");
                Console.Write($"Tank Max Vol: {item.Tank_MaxVol} ");
                Console.Write($"Tank Min Vol: {item.Tank_MinVol} ");
                Console.Write($"Tank Max Water Height: {item.Tank_MaxwHeight} ");
                Console.Write($"Tank Max Temp: {item.Tank_MaxTemp} ");
                Console.Write($"Probe ID: {item.ProbeId} ");
                Console.Write($"Offset: {item.Offset} ");
                Console.Write($"Water Offset: {item.WaterOffset} ");
                Console.Write($"Probe Address: {item.ProbeAddr} ");
                Console.WriteLine($"Oil Offset: {item.OilOffset} ");
            }
        }

        public void GetAllPumpNozzleInformation()
        {
            IList<PumpNozzleInfoResult> pumpNozzleInfoResults = new List<PumpNozzleInfoResult>();

            using (var context = new SpsDbContext(connectionString))
            {
                pumpNozzleInfoResults = context.LoadStoredProc(SpsDbRoutines.GetAllPumpNozzleInfo)
                    .WithSqlParam("", "")
                    .ExecuteStoredProc<PumpNozzleInfoResult>();
            }

            foreach (var item in pumpNozzleInfoResults)
            {
                Console.Write($"Logical Node: {item.LgNode} ");
                Console.Write($"Tank No: {item.TankNo} ");
                Console.Write($"POS ID: {item.PosId} ");
                Console.Write($"Protocol: {item.Protocol} ");
                Console.Write($"ProtcType: {item.ProtcType} ");
                Console.Write($"Port: {item.Port} ");
                Console.Write($"LinkPort: {item.LinkPort} ");
                Console.Write($"Mode: {item.Mode} ");
                Console.Write($"BuffSize: {item.BuffSize} ");
                Console.Write($"CheckMode: {item.CheckMode} ");
                Console.Write($"Node: {item.Node} ");
                Console.Write($"SubNode: {item.SubNode} ");
                Console.Write($"NozzleNum: {item.NozzleNum} ");
                Console.Write($"PumpType: {item.PumpType} ");
                Console.Write($"DspNo: {item.DspNo} ");
                Console.Write($"MaxMon: {item.MaxMon} ");
                Console.Write($"MaxVol: {item.MaxVol} ");
                Console.WriteLine();
            }
        }

        public void GetPosConfigInfo()
        {
            IList<PosConfigInfoResult> posConfigInfoResults = new List<PosConfigInfoResult>();
            using (var context = new SpsDbContext(connectionString))
            {
                posConfigInfoResults = context.LoadStoredProc(SpsDbRoutines.GetAllPosConfigInfo)
                    .WithSqlParam("", "")
                    .ExecuteStoredProc<PosConfigInfoResult>();
            }

            foreach (var item in posConfigInfoResults)
            {
                Console.Write($"POS ID: {item.Pos_Id} ");
                Console.Write($"POS IP: {item.Pos_IP} ");
                Console.WriteLine($"Shift_Num: {item.Shift_Num}");
            }
        }

        public TTrdinfo SelectGrayTrade(string cardNo, int ctc, string time)
        {
            return null;
        }


        public TTrdinfo GetGrayTrdInfo(string cardNo, int ctc, int pumpId)
        {
            string selectStament = "SELECT * FROM sps_db.t_trdinfo WHERE CardNo=@cardNo AND CTC=@ctc AND PumpNo=@pumpId";
            byte grayTrdType = 1;

            try
            {
                using (MySqlConnection conn = new MySqlConnection(connectionString))
                {
                    List<TTrdinfo> trdInfoList = new List<TTrdinfo>();

                    using (MySqlCommand cmd = new MySqlCommand(selectStament, conn))
                    {
                        cmd.Parameters.Add("@TrdType", MySqlDbType.Byte).Value = grayTrdType;
                        cmd.Parameters.Add("@cardNo", MySqlDbType.String).Value = cardNo;
                        cmd.Parameters.Add("@ctc", MySqlDbType.UInt32).Value = ctc;
                        cmd.Parameters.Add("@pumpId", MySqlDbType.Byte).Value = pumpId;
                        conn.Open();

                        using (MySqlDataReader dr = cmd.ExecuteReader())
                        {
                            if (dr.HasRows)
                            {
                                while (dr.Read())
                                {
                                    var trdInfo = new TTrdinfo();
                                    trdInfo.CommId = Convert.ToString(dr["CommID"]);
                                    trdInfo.Mon = Convert.ToInt32(dr["Mon"]);
                                    trdInfo.EndPumpId = Convert.ToInt64(dr["EndPumpID"]);
                                    trdInfo.Ttctime = Convert.ToDateTime(dr["TTCTime"]);
                                    trdInfoList.Add(trdInfo);
                                }
                            }
                        }

                        return trdInfoList.OrderByDescending(t => t.Ttctime).FirstOrDefault();
                    }
                }
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.ToString());
            }

            return null;
        }

        #endregion

        #endregion

        #region Operations against legacy iFuel config in `sps_db` tables

        public int AddOrUpdateFuelProduct(string productCode, string classNo, string productName, int price)
        {
            using (var context = new SpsDbContext(connectionString))
            {
                try
                {
                    var entry = context.TFuellist.FirstOrDefault(f => f.FuelNo == productCode);

                    if (entry == null)
                    {
                        var fuelEntry = new TFuellist();
                        fuelEntry.FuelNo = productCode;
                        fuelEntry.ClassNo = classNo;
                        fuelEntry.Name = productName;
                        fuelEntry.Price = price;
                        fuelEntry.Unit = "升";

                        context.TFuellist.Add(fuelEntry);
                    }
                    else
                    {
                        entry.ClassNo = classNo;
                        entry.Name = productName;
                        entry.Unit = "升";
                    }

                    context.SaveChanges();
                }
                catch (Exception ex)
                {
                    Console.WriteLine(ex.ToString());
                }
            }

            return -1;
        }

        public int AddOrUpdateTankConfig(int tankNo, string product)
        {
            using (var context = new SpsDbContext(connectionString))
            {
                try
                {
                    var currentTank = context.TTanklist.FirstOrDefault(t => t.TankId == tankNo);
                    if (currentTank == null)
                    {
                        var tank = new TTanklist();
                        tank.TankId = Convert.ToByte(tankNo);
                        tank.FuelNo = product;

                        tank.Height = 0;
                        tank.MaxHeight = 0;
                        tank.MinHeight = 0;
                        tank.MaxVolume = 0;
                        tank.MinVolume = 0;
                        
                        tank.MaxWaterHeight = 0;
                        tank.MaxTemp = 0;
                        tank.OffSet = 0;
                        tank.OilOffSet = 0;
                        tank.WaterOffSet = 0;
                        tank.FixDate = new DateTime(1900, 01, 01);
                        tank.FactoryDate = new DateTime(1900, 01, 01);
                        tank.FactoryNo = "0";
                        tank.LimitDate = 0;
                        tank.ProbeId = "";
                        tank.ProbeAddr = "";
                        tank.FactName = "0";

                        context.TTanklist.Add(tank);
                    }
                    else
                    {
                        currentTank.FuelNo = product;
                    }

                    context.SaveChanges();
                }
                catch (Exception ex)
                {
                    Console.WriteLine(ex.ToString());
                }
            }

            return -1;
        }

        public int AddOrUpdatePump(int pumpMode, byte pumpId, int comPortNo, byte subAddress, int maxAmount, int maxVol = 999900)
        {
            using (var context = new SpsDbContext(connectionString))
            {
                try
                {
                    var currentPumpInfo = context.TPumpinfo.FirstOrDefault(p => p.LgNode == pumpId);

                    if (currentPumpInfo == null)
                    {
                        var pumpInfo = new TPumpinfo();
                        pumpInfo.LgNode = pumpId;
                        pumpInfo.DspNo = "1";
                        pumpInfo.TankNo = 0;
                        pumpInfo.PosId = 1;
                        pumpInfo.PumpType = 0;
                        pumpInfo.Protocol = 2;
                        pumpInfo.ProtcType = 2;
                        pumpInfo.Port = "0";
                        pumpInfo.LinkPort = comPortNo;
                        pumpInfo.Node = 1;
                        pumpInfo.SubNode = subAddress;
                        pumpInfo.Mode = pumpMode; //1;
                        pumpInfo.NozzleNum = 1;
                        pumpInfo.DelayPara = 0;
                        pumpInfo.BuffSize = 4;
                        pumpInfo.CheckMode = 2;
                        pumpInfo.MaxMon = maxAmount;
                        pumpInfo.MaxVol = maxVol;

                        context.TPumpinfo.Add(pumpInfo);
                    }
                    else
                    {
                        currentPumpInfo.DspNo = "1";
                        currentPumpInfo.TankNo = 0;
                        currentPumpInfo.PosId = 1;
                        currentPumpInfo.PumpType = 0;
                        currentPumpInfo.Protocol = 2;
                        currentPumpInfo.ProtcType = 2;
                        currentPumpInfo.Port = "0";
                        currentPumpInfo.LinkPort = comPortNo;
                        currentPumpInfo.Node = 1;
                        currentPumpInfo.SubNode = subAddress;
                        currentPumpInfo.Mode = pumpMode;//1;
                        currentPumpInfo.NozzleNum = 1;
                        currentPumpInfo.DelayPara = 0;
                        currentPumpInfo.BuffSize = 4;
                        currentPumpInfo.CheckMode = 2;
                        currentPumpInfo.MaxMon = maxAmount;
                        currentPumpInfo.MaxVol = maxVol;
                    }

                    context.SaveChanges();
                }
                catch (Exception ex)
                {
                    Console.WriteLine(ex.ToString());
                }
            }

            return -1;
        }

        public int AddOrUpdateNozzle(byte nozzleId, byte pumpId, byte tankId, string fuelNo)
        {
            using (var context = new SpsDbContext(connectionString))
            {
                try
                {
                    var currentNozzleInfo = context.TNozzleinfo.FirstOrDefault(n => n.LogicId == nozzleId);

                    if (currentNozzleInfo == null)
                    {
                        var nozzleInfo = new TNozzleinfo();
                        nozzleInfo.LogicId = nozzleId;
                        nozzleInfo.PhysId = 1;
                        nozzleInfo.DspNo = "0";
                        nozzleInfo.TankId = tankId;
                        nozzleInfo.PumpNode = pumpId;
                        nozzleInfo.State = "";
                        nozzleInfo.OilTypeId = fuelNo;
                        nozzleInfo.CarIdenAddr = "0";

                        context.TNozzleinfo.Add(nozzleInfo);
                    }
                    else
                    {
                        currentNozzleInfo.PhysId = 1;
                        currentNozzleInfo.DspNo = "0";
                        currentNozzleInfo.TankId = tankId;
                        currentNozzleInfo.PumpNode = pumpId;
                        currentNozzleInfo.State = "";
                        currentNozzleInfo.OilTypeId = fuelNo;
                        currentNozzleInfo.CarIdenAddr = "0";
                    }

                    context.SaveChanges();
                }
                catch (Exception ex)
                {
                    Console.WriteLine(ex.ToString());
                }
            }

            return -1;
        }

        #endregion

        #region Get card holder and car number

        public TCardinfo GetCardInfoByCardNo(string cardNo) 
        {
            using (var context = new SpsDbContext(connectionString))
            {
                return context.TCardinfo.FirstOrDefault(c => c.CardNo == cardNo);
            }
        }

        #endregion
    }
}