using Edge.Core.Database;
using Edge.Core.Processor;using Edge.Core.IndustryStandardInterface.Pump;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Threading;
using System.Threading.Tasks;
using Wayne.FDCPOSLibrary;
using Edge.Core.Configuration;

namespace SinochemInternetPlusApp
{
    /// <summary>
    /// Manages pump transactions, inserting the transactions to POS database (table: Xiaofei2).
    /// </summary>
    public class ForecourtTrxManager
    {
        #region Logger

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

        #endregion

        #region Fields

        private IEnumerable<NozzleExtraInfo> nozzleProductConfig = Configurator.Default.NozzleExtraInfoConfiguration.Mapping;

        /// <summary>
        /// fdc pump handler is the entry for manage pumps.
        /// </summary>
        private IEnumerable<IFdcPumpController> fdcPumpControllers;

        /// <summary>
        /// PumpIdAsSideA:PumpIdAsSideB:JiHao
        /// </summary>
        private readonly IEnumerable<Tuple<int, int, int>> pumpSidePairs = null;

        /// <summary>
        /// pumpId:RealHoseLogicalId:HuiTianSiteLevelHoseId
        /// </summary>
        public static IEnumerable<Tuple<int, int, int>> nozzleRemappingPairs = null;

        /// <summary>
        /// rawName:FriendlyName
        /// </summary>
        private Dictionary<string, string> rawProductNameToPosProductNameMapping = new Dictionary<string, string>();

        private string sqlServerConnStr;

        /// <summary>
        /// mapping between PumpId, nozzleId, site level nozzle...
        /// </summary>
        private IOrderedEnumerable<DataRow> sortedSiteNozzlesDataRows;

        private bool initialized = false;

        #endregion

        #region Constructor

        /// <summary>
        /// 
        /// </summary>
        /// <param name="pumpSideMappingStr">PumpIdAsSideA:PumpIdAsSideB:JiHao, like 1:2:1; 3:4:2; 5:6:3; </param>
        /// <param name="forceMappingFusionHoseToHuiTianHoseStr">pumpId:RealHoseLogicalId:HuiTianSiteLevelHoseId, like 14:2:16;14:1:17</param>
        /// <param name="gradeNameRemapping">rawGradeName:HuiTianPosGradeName, like 93gas:93#;98gas:98#;</param>
        public ForecourtTrxManager(IEnumerable<IFdcPumpController> pumpControllers, string pumpSideMappingStr,
            string forceMappingFusionHoseToHuiTianHoseStr, string sqlServerConnStr, string rawProductNameToPosProductNameStr)
        {
            fdcPumpControllers = pumpControllers;

            var pumpSideMappingRawString = pumpSideMappingStr.Trim().Replace(" ", "");
            if (pumpSideMappingRawString.Substring(pumpSideMappingRawString.Length - 1) == ";")
                pumpSideMappingRawString = pumpSideMappingRawString.Substring(0, pumpSideMappingRawString.Length - 1);
            pumpSidePairs = pumpSideMappingRawString.Split(';')
                .Select(p => new Tuple<int, int, int>(int.Parse(p.Split(':')[0]), int.Parse(p.Split(':')[1]), int.Parse(p.Split(':')[2])));

            if (!string.IsNullOrEmpty(forceMappingFusionHoseToHuiTianHoseStr))
            {
                var nozzleRemappingRawString = forceMappingFusionHoseToHuiTianHoseStr.Trim().Replace(" ", "");
                if (nozzleRemappingRawString.Substring(nozzleRemappingRawString.Length - 1) == ";")
                    nozzleRemappingRawString = nozzleRemappingRawString.Substring(0, nozzleRemappingRawString.Length - 1);

                logger.Info("will parse forceMappingFusionHoseToHuiTianHose, raw string in config is: " + nozzleRemappingRawString);

                nozzleRemappingPairs = nozzleRemappingRawString.Split(';').Select(p =>
                                        new Tuple<int, int, int>(int.Parse(p.Split(':')[0]), int.Parse(p.Split(':')[1]),
                                            int.Parse(p.Split(':')[2])));
            }

            this.sqlServerConnStr = sqlServerConnStr;

            if (!string.IsNullOrEmpty(rawProductNameToPosProductNameStr))
            {
                rawProductNameToPosProductNameStr.Split(';')
                    .Where(w => !string.IsNullOrEmpty(w))
                    .Select(p => new KeyValuePair<string, string>(p.Split(':')[0], p.Split(':')[1])).ToList()
                    .ForEach(v => rawProductNameToPosProductNameMapping.Add(v.Key, v.Value));
            }
        }

        #endregion

        public void Init()
        {
            if (initialized)
                return;

            foreach (var fdcPumpController in fdcPumpControllers)
            {
                fdcPumpController.OnStateChange += (s, a) =>
                {
                    var pump = s as IFdcPumpController;
                    try
                    {
                        if (a.NewPumpState == LogicalDeviceState.FDC_READY)
                        {
                            /* indicate for nozzle if replaced back */
                            var sizeLevelNozzleIdsOnPump = SiteConfigUtility.Default.GetSiteLevelNozzleIdsByPumpId(pump.PumpId);
                            if (!sizeLevelNozzleIdsOnPump.Any())
                            {
                                logger.Info("Could not found any site level nozzle ids for pump: " + pump.PumpId);
                                return;
                            }

                            using (var posSqlConnection = new SqlConnection(this.sqlServerConnStr))
                            {
                                try
                                {
                                    /* idle would not carry nozzle id, so here reset all nozzles on target pump.*/
                                    var setPumpOnIdleCommand
                                        = new SqlCommand(sizeLevelNozzleIdsOnPump.Select(siteLevelNozzleId =>
                                        {
                                            SqliteDbContext dbContext = new SqliteDbContext();
                                            //var lastTrx = dbContext.PumpTransactionModels.OrderByDescending(f => f.SaleEndTime)
                                            //    .FirstOrDefault(f => f.PumpId == pump.PumpId && f.LogicalNozzleId == SiteConfigUtility.Default.GetNozzleLogicalIdBySiteLevelNozzleId(siteLevelNozzleId));
                                            //var totalizer =
                                            //    new Tuple<float, float>((float)((lastTrx?.VolumeTotalizer ?? 0) * Math.Pow(10, 2)),
                                            //        (float)((lastTrx?.AmountTotalizer ?? 0) * Math.Pow(10, 2)));
                                            //return
                                            //    string.Format(
                                            //        "Update jy_info set [status] = '{1}', qty=0, amount=0, fzqty='{2}', fzamount={3}" +
                                            //        " where jihao = {0}", siteLevelNozzleId, 'F',
                                            //        totalizer.Item1, totalizer.Item2);
                                            return
                                                string.Format(
                                                    "Update jy_info set [status] = '{1}', qty=0, amount=0" +
                                                    " where jihao = {0}", siteLevelNozzleId, 'F');
                                        }).Aggregate((acc, n) => acc + "   " + n), posSqlConnection);
                                    logger.Debug("setPumpOnIdleCommand(via Fdc): " + setPumpOnIdleCommand.CommandText);
                                    posSqlConnection.Open();
                                    setPumpOnIdleCommand.ExecuteNonQuery();
                                }
                                catch (Exception ex)
                                {
                                    logger.Error("executing setPumpOnIdleCommand(via Fdc) failed, exception detail: " + ex);
                                }
                            }
                        }
                        else if (a.NewPumpState == LogicalDeviceState.FDC_CALLING)
                        {
                            using (var posSqlConnection = new SqlConnection(this.sqlServerConnStr))
                            {
                                try
                                {
                                    var operatingNozzleLogicalId = a?.StateChangedNozzles?.FirstOrDefault()?.LogicalId ?? (byte)0;
                                    var rawProductNo = nozzleProductConfig.First(f => f.PumpId == pump.PumpId
                                        && f.NozzleLogicalId == operatingNozzleLogicalId).ProductBarcode;
                                    var gradeFriendlyName = rawProductNameToPosProductNameMapping[rawProductNo.ToString()];
                                    //var updatePumpOnFuelingCommand =
                                    //    new SqlCommand(string.Format("Update jy_info set [status] = '{1}', youpin = N'{2}', qty= {3}, amount= {4} where jihao = '{0}'"
                                    //                                 , SiteConfigUtility.Default.GetSiteLevelNozzleIdByLogicalNozzleId(pump.PumpId, a?.Transaction?.Nozzle?.LogicalId ?? 0),
                                    //                                 'B',
                                    //                                 gradeFriendlyName,
                                    //                                 a.Transaction.Volumn,
                                    //                                 a.Transaction.Amount),
                                    //        posSqlConnection);
                                    var updatePumpOnFuelingCommand =
                                        new SqlCommand(string.Format("Update jy_info set [status] = '{1}' where jihao = '{0}'"
                                                                     , SiteConfigUtility.Default.GetSiteLevelNozzleIdByLogicalNozzleId(pump.PumpId, operatingNozzleLogicalId),
                                                                     'B',
                                                                     gradeFriendlyName),
                                            posSqlConnection);
                                    logger.Debug("updatePumpOnFuelingCommand: " + updatePumpOnFuelingCommand.CommandText);
                                    posSqlConnection.Open();
                                    updatePumpOnFuelingCommand.ExecuteNonQuery();
                                }
                                catch (Exception ex)
                                {
                                    logger.Error("executing updatePumpOnFuelingCommand failed, exception detail: " + ex);
                                }
                            }
                        }
                    }
                    catch (Exception exx)
                    { }
                };

                fdcPumpController.OnCurrentFuellingStatusChange += async (s, a) =>
                {
                    try
                    {
                        var pump = s as IFdcPumpController;
                        if (!a.Transaction.Finished)
                        {
                            logger.Info("Start to query accumulator");

                            var acc = await pump.QueryTotalizerAsync(a.Transaction.Nozzle.LogicalId);

                            logger.Info($"Amount accumulator: {acc.Item1}, Volume accumulator: {acc.Item2}");

                            using (var posSqlConnection = new SqlConnection(this.sqlServerConnStr))
                            {
                                try
                                {
                                    var rawProductNo = nozzleProductConfig.First(f => f.PumpId == pump.PumpId && f.NozzleLogicalId == a.Transaction.Nozzle.LogicalId).ProductBarcode;
                                    var gradeFriendlyName = rawProductNameToPosProductNameMapping[rawProductNo.ToString()];
                                    //var updatePumpOnFuelingCommand =
                                    //    new SqlCommand(string.Format("Update jy_info set [status] = '{1}', youpin = N'{2}', qty= {3}, amount= {4} where jihao = '{0}'"
                                    //                                 , SiteConfigUtility.Default.GetSiteLevelNozzleIdByLogicalNozzleId(pump.PumpId, a?.Transaction?.Nozzle?.LogicalId ?? 0),
                                    //                                 'B',
                                    //                                 gradeFriendlyName,
                                    //                                 a.Transaction.Volumn,
                                    //                                 a.Transaction.Amount),
                                    //        posSqlConnection);
                                    var updatePumpOnFuelingCommand =
                                        new SqlCommand(string.Format("Update jy_info set youpin = N'{2}', qty= {3}, amount= {4}, fzqty= {5}, fzamount= {6} where jihao = '{0}'"
                                                                     , SiteConfigUtility.Default.GetSiteLevelNozzleIdByLogicalNozzleId(pump.PumpId, a?.Transaction?.Nozzle?.LogicalId ?? 0),
                                                                     "",
                                                                     gradeFriendlyName,
                                                                     a.Transaction.Volumn / Math.Pow(10, pump.VolumeDecimalDigits),
                                                                     a.Transaction.Amount / Math.Pow(10, pump.AmountDecimalDigits),
                                                                     acc.Item2 / Math.Pow(10, pump.VolumeDecimalDigits),
                                                                     acc.Item1 / Math.Pow(10, pump.AmountDecimalDigits)),
                                            posSqlConnection);
                                    logger.Debug("updatePumpOnFuelingCommand: " + updatePumpOnFuelingCommand.CommandText);
                                    posSqlConnection.Open();
                                    updatePumpOnFuelingCommand.ExecuteNonQuery();
                                }
                                catch (Exception ex)
                                {
                                    logger.Error("executing updatePumpOnFuelingCommand failed, exception detail: " + ex);
                                }
                            }
                        }
                        else
                        {
                            logger.Info("Fueling trx finished, start to query accumulator");

                            var acc = await pump.QueryTotalizerAsync(a.Transaction.Nozzle.LogicalId);

                            logger.Info($"Amount accumulator: {acc.Item1}, Volume accumulator: {acc.Item2}");

                            var posSqlConnection = new SqlConnection(this.sqlServerConnStr);
                            using (posSqlConnection)
                            {
                                try
                                {
                                    var rawProductNo = nozzleProductConfig.First(f => f.PumpId == pump.PumpId && f.NozzleLogicalId == a.Transaction.Nozzle.LogicalId).ProductBarcode;
                                    var gradeFriendlyName = rawProductNameToPosProductNameMapping[rawProductNo.ToString()];
                                    //var totalizer = SiteConfigUtility.Default.GetTotalizer(e.Fuelling.Pump.Id, e.Fuelling.Nozzle.Id);
                                    var updateFuelingTrxDoneCommand =
                                        new SqlCommand(
                                            string.Format(
                                                "insert xiaofei2 (jihao, youpin, qty, danjia, amount, xf_date, xf_time, liushuino, fzqty, fzamount)" +
                                                " values({0}, N'{1}', {2}, {3}, {4}, '{5}', '{6}', {7}, {8}, {9})",
                                                SiteConfigUtility.Default.GetSiteLevelNozzleIdByLogicalNozzleId(pump.PumpId, a.Transaction.Nozzle.LogicalId),
                                                gradeFriendlyName,
                                                 a.Transaction.Volumn / Math.Pow(10, pump.VolumeDecimalDigits),
                                                 a.Transaction.Price / Math.Pow(10, pump.PriceDecimalDigits),
                                                 a.Transaction.Amount / Math.Pow(10, pump.AmountDecimalDigits),
                                                DateTime.Now.Date.ToString("yyyy-MM-dd"),
                                                DateTime.Now.ToString("HH:mm:ss"),
                                               a.Transaction.SequenceNumberGeneratedOnPhysicalPump,
                                                 acc.Item2 / Math.Pow(10, pump.VolumeDecimalDigits),
                                                 acc.Item1 / Math.Pow(10, pump.AmountDecimalDigits)),
                                            posSqlConnection);

                                    logger.Info("updateFuelingTrxDoneCommand: " + updateFuelingTrxDoneCommand.CommandText);
                                    logger.Info($"Inserted a finished fueling transaction into xiaofei2");

                                    posSqlConnection.Open();
                                    updateFuelingTrxDoneCommand.ExecuteNonQuery();
                                }
                                catch (Exception ex)
                                {
                                    logger.Error("executing updateFuelingTrxDoneCommand failed, exception detail: " + ex);
                                }
                            }

                            using (var sqlConnection = new SqlConnection(this.sqlServerConnStr))
                            {
                                try
                                {
                                    //var rawProductNo = nozzleProductConfig.First(f => f.PumpId == pump.PumpId
                                    //    && f.NozzleLogicalId == operatingNozzleLogicalId).ProductBarcode;
                                    //var gradeFriendlyName = rawProductNameToPosProductNameMapping[rawProductNo.ToString()];
                                    //var updatePumpOnFuelingCommand =
                                    //    new SqlCommand(string.Format("Update jy_info set [status] = '{1}', youpin = N'{2}', qty= {3}, amount= {4} where jihao = '{0}'"
                                    //                                 , SiteConfigUtility.Default.GetSiteLevelNozzleIdByLogicalNozzleId(pump.PumpId, a?.Transaction?.Nozzle?.LogicalId ?? 0),
                                    //                                 'B',
                                    //                                 gradeFriendlyName,
                                    //                                 a.Transaction.Volumn,
                                    //                                 a.Transaction.Amount),
                                    //        posSqlConnection);
                                    var updatePumpOnFuelingCommand =
                                        new SqlCommand(string.Format("Update jy_info set fzqty = '{1}', fzamount={2} where jihao = '{0}'"
                                                                     ,
                                                                     SiteConfigUtility.Default.GetSiteLevelNozzleIdByLogicalNozzleId(
                                                                         pump.PumpId, a.Transaction.Nozzle.LogicalId),
                                                                     acc.Item2 / Math.Pow(10, pump.VolumeDecimalDigits),
                                                                     acc.Item1 / Math.Pow(10, pump.AmountDecimalDigits)),
                                            sqlConnection);
                                    logger.Debug("updatePumpOnFuelingCommand: " + updatePumpOnFuelingCommand.CommandText);
                                    sqlConnection.Open();
                                    updatePumpOnFuelingCommand.ExecuteNonQuery();
                                }
                                catch (Exception ex)
                                {
                                    logger.Error("executing updatePumpOnFuelingCommand failed, exception detail: " + ex);
                                }
                            }
                        }
                    }
                    catch (Exception exxx)
                    { }
                };

            }

            #region table [jyjpz]

            try
            {
                var siteNozzlesDataTableInFcDb = new DataTable();
                siteNozzlesDataTableInFcDb.Columns.Add("pumpid");
                siteNozzlesDataTableInFcDb.Columns.Add("HoseLogicalId");
                siteNozzlesDataTableInFcDb.Columns.Add("productNo");

                foreach (var fdcPumpController in this.fdcPumpControllers)
                {
                    foreach (var nozzle in fdcPumpController.Nozzles)
                    {
                        var bindProduct = nozzleProductConfig.FirstOrDefault(n =>
                            n.PumpId == fdcPumpController.PumpId
                                && n.NozzleLogicalId == nozzle.LogicalId);
                        var fuelPrice = nozzle.RealPriceOnPhysicalPump == null ? 0 :
                                 (nozzle.RealPriceOnPhysicalPump.Value / Math.Pow(10, fdcPumpController.PriceDecimalDigits));
                        var newRow = siteNozzlesDataTableInFcDb.NewRow();//.Rows.Add( new DataRow)
                        newRow["pumpid"] = fdcPumpController.PumpId;
                        newRow["HoseLogicalId"] = nozzle.LogicalId;
                        newRow["productNo"] = bindProduct.ProductBarcode;
                        siteNozzlesDataTableInFcDb.Rows.Add(newRow);
                    }
                }

                // SinoChem defined a id of: 'jiHao' which stands for a physical pump(2 sides).
                siteNozzlesDataTableInFcDb.Columns.Add(new DataColumn("jiHao"));
                // SinoChem need another id of site overall id for a nozzle, start from 1.
                // we cacualte this based on jiHao and HostLogicalId
                siteNozzlesDataTableInFcDb.Columns.Add(new DataColumn("siteLevelNozzleId"));
                // as doc defined, side A is 0, side B is 1.
                siteNozzlesDataTableInFcDb.Columns.Add(new DataColumn("sideId"));
                var siteNozzlesDataRowsInFcDb = siteNozzlesDataTableInFcDb.Rows.Cast<DataRow>();
                foreach (var row in siteNozzlesDataRowsInFcDb)
                {
                    var pumpId = int.Parse(row["pumpid"].ToString());
                    var possibleSideA = this.pumpSidePairs.FirstOrDefault(p => p.Item1 == pumpId);
                    Tuple<int, int, int> possibleSideB = null;
                    if (possibleSideA != null)
                    {
                        row["sideId"] = "0";
                        row["jiHao"] = possibleSideA.Item3;
                    }
                    else if ((possibleSideB = this.pumpSidePairs.FirstOrDefault(p => p.Item2 == pumpId)) != null)
                    {
                        row["sideId"] = "1";
                        row["jiHao"] = possibleSideB.Item3;
                    }
                    else
                    {
                        throw new ArgumentException("Pump with pumpId: " + pumpId +
                                                    " is neither side A nor side B, pls check Side definition config file.");
                    }
                }

                this.sortedSiteNozzlesDataRows =
                                            siteNozzlesDataRowsInFcDb.OrderBy(r => int.Parse(r["jiHao"].ToString()))
                                                .ThenBy(r => r["sideId"])
                                                .ThenBy(r => r["HoseLogicalId"]);
                SiteConfigUtility.Default.UpdateLatestSiteConfig(this.sortedSiteNozzlesDataRows);
                logger.Debug("sortedSiteNozzlesDataRows: \r\n");
                // finally we get the ordered sequence, now give them sitelevel nozzle id.
                for (var i = 0; i < sortedSiteNozzlesDataRows.Count(); i++)
                {
                    var oneRow = sortedSiteNozzlesDataRows.ElementAt(i);
                    oneRow["siteLevelNozzleId"] = i + 1;
                    logger.Debug("   JiHao: " + oneRow["jiHao"] + ", sideId: " + oneRow["sideId"] +
                        ", nozzleLogicalId: " + oneRow["HoseLogicalId"] +
                        ", siteLevelNozzleId: " + oneRow["siteLevelNozzleId"]
                        + ", pumpId: " + oneRow["pumpid"]
                        + ", productNo: " + oneRow["productNo"]
                       //+ ", hoseName: " + oneRow["hosename"]
                       );
                    if (nozzleRemappingPairs != null)
                    {
                        var pumpId = int.Parse(oneRow["pumpid"].ToString());
                        var fusionHoseLogicalId = int.Parse(oneRow["HoseLogicalId"].ToString());
                        var matched = nozzleRemappingPairs.FirstOrDefault(m => m.Item1 == pumpId && m.Item2 == fusionHoseLogicalId);
                        if (matched != null)
                        {
                            logger.Debug("Found nozzleRemappingPairs for pumpId: " + pumpId + " with HoseLogicalId: " + fusionHoseLogicalId + ", " +
                                "will remapping the bound siteLevelNozzleId from: " + oneRow["siteLevelNozzleId"] + " to: " + matched.Item3);
                            oneRow["siteLevelNozzleId"] = matched.Item3;
                        }
                    }
                }

                // for now, always overwrite the whole table in POS database without further check for rows or columns level.
                using (var posConn = new SqlConnection(this.sqlServerConnStr))
                {
                    /* configInFcDb structure like below */
                    //"   JiHao: " + oneRow["jiHao"] + ", sideId: " + oneRow["sideId"] +
                    //", nozzleLogicalId: " + oneRow["HoseLogicalId"] +
                    //", siteLevelNozzleId: " + oneRow["siteLevelNozzleId"]
                    string bulkInsertCmd = sortedSiteNozzlesDataRows.Select(
                        r => string.Format("Select {0}, '{1}', {2}, {3}",
                            r["jiHao"],
                            r["sideId"],
                            r["HoseLogicalId"],
                            r["siteLevelNozzleId"])).Aggregate((acc, n) => acc + " Union all " + n);
                    var truncateAndInsertCmd =
                        new SqlCommand(
                            "TRUNCATE table jyjpz; INSERT jyjpz (jihao, abtype, qianghao, luojiqh) " + bulkInsertCmd,
                            posConn);
                    logger.Info("truncateAndInsertSiteConfigCmd: " + truncateAndInsertCmd.CommandText);
                    posConn.Open();
                    truncateAndInsertCmd.ExecuteNonQuery();
                    //return true;
                }
            }
            catch (Exception ex)
            {
                logger.Error("PerformCompareAndUpdate exceptioned, detail: " + ex);
            }

            #endregion

            #region table jy_info

            foreach (var nozzle in sortedSiteNozzlesDataRows)//.Select(n => n["siteLevelNozzleId"]))
            {
                var siteLevelNozzleId = nozzle["siteLevelNozzleId"];
                var pumpId = int.Parse(nozzle["pumpid"].ToString());
                var pump = this.fdcPumpControllers.First(f => f.PumpId == pumpId);
                var nozzleLogicalId = int.Parse(nozzle["HoseLogicalId"].ToString());
                var productNo = nozzle["productNo"].ToString();
                using (
                    var posSqlConnection = new SqlConnection(this.sqlServerConnStr))
                {
                    try
                    {
                        var gradeFriendlyName = rawProductNameToPosProductNameMapping[productNo];// Translator.GetFriendlyGradeName(SiteConfigUtility.Default.GetGradeName(siteLevelNozzleId));
                        SqliteDbContext dbContext = new SqliteDbContext();
                        var lastTrx = dbContext.PumpTransactionModels.OrderByDescending(f => f.SaleEndTime)
                            .FirstOrDefault(f => f.PumpId == pumpId && f.LogicalNozzleId == nozzleLogicalId);
                        var totalizer =
                            new Tuple<float, float>((float)((lastTrx?.VolumeTotalizer / Math.Pow(10, pump.VolumeDecimalDigits) ?? 0)),
                                (float)((lastTrx?.AmountTotalizer / Math.Pow(10, pump.AmountDecimalDigits) ?? 0) * Math.Pow(10, 2)));
                        var setPumpOnStartingCommand =
                            new SqlCommand(string.Format("if not exists(select * from jy_info where jihao={0})" +
                                                         "	BEGIN" +
                                                         "    	insert jy_info (jihao, [status], youpin, qty, amount, fzqty, fzamount) values({0}, '{1}', N'{2}', 0, 0, '{3}', {4})" +
                                                         "	END", siteLevelNozzleId, 'F', gradeFriendlyName,
                                totalizer.Item1, totalizer.Item2), posSqlConnection);
                        logger.Debug("initTable_jy_info_Command: " + setPumpOnStartingCommand.CommandText);
                        posSqlConnection.Open();
                        setPumpOnStartingCommand.ExecuteNonQuery();
                    }
                    catch (Exception ex)
                    {
                        logger.Error("executing initTable_jy_info_Command failed for siteLevelNozzleId: " + siteLevelNozzleId + ", exception detail: " + ex);
                        throw;
                    }
                }
            }

            #endregion

            initialized = true;
        }

        private void PrepareInsertTransToXiaofei2()
        {
        }

        private Tuple<int, int> GetAccumulator(IFdcPumpController pumpController, byte logicalNozzleId)
        {
            return pumpController.QueryTotalizerAsync(logicalNozzleId).Result;
        }

        public Dictionary<int, string> GetNozzleFuelMapping()
        {
            var dict = new Dictionary<int, string>();
            foreach (var pump in fdcPumpControllers)
            {
                foreach (var nozzle in pump.Nozzles)
                {
                    var currentNozzleProductBinding =
                        nozzleProductConfig.FirstOrDefault(f => f.PumpId == pump.PumpId && f.NozzleLogicalId == nozzle.LogicalId);
                    if (currentNozzleProductBinding != null)
                    {
                        var rawProductNo = currentNozzleProductBinding.ProductBarcode;
                        var gradeFriendlyName = rawProductNameToPosProductNameMapping[rawProductNo.ToString()];

                        if (!dict.ContainsKey(currentNozzleProductBinding.SiteLevelNozzleId ?? 0))
                        {
                            dict.Add(currentNozzleProductBinding.SiteLevelNozzleId ?? 0, gradeFriendlyName);
                        }
                    }

                }
            }

            return dict;
        }
    }
}