FCC/Applications/PumpInfoToDatabase/AppBase.cs

using Applications.FDC;
using Edge.Core.Database;
using Edge.Core.Processor;
using Edge.Core.IndustryStandardInterface.Pump;
using Microsoft.Extensions.DependencyInjection;
using Microsoft.Extensions.Logging;
using Microsoft.Extensions.Logging.Abstractions;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.Common;
using System.Linq;
using System.Text;
using Wayne.FDCPOSLibrary;
using Edge.Core.Configuration;

namespace Applications.PumpInfoToSinoChemPosSqlServerOrMySql
{
    public abstract class AppBase
    {
        protected ILogger logger = NullLogger.Instance;
        protected FdcServerHostApp fdcServerHostApp;// = Configurator.Default.NozzleProductConfiguration.Mapping;

        protected AppConfigV1 appConfig;

        #region App config

        public class AppConfigV1
        {
            public string DatabaseConnStr { get; set; }
            public string JiaYouJiPeiZhi_TableName { get; set; }
            public List<YouJiConfigV1> YouJiConfigs { get; set; }
        }

        /// <summary>
        /// 对应一台物理油机
        /// </summary>
        public class YouJiConfigV1
        {
            /// <summary>
            /// 油机编号,由1开始编号
            ///     假设某个油站有四台油机, 则此号码分别为1、2、3、4
            /// </summary>
            public byte JiHao { get; set; }

            public List<YouMianConfigV1> YouMianConfigs { get; set; }
        }

        /// <summary>
        /// 每台物理油机有两个油面(A面或B面)
        /// </summary>
        public class YouMianConfigV1
        {
            /// <summary>
            /// 0 or 1, 0=A面 1=B面
            /// </summary>
            public int YouMianBianHao { get; set; }

            public List<YouQiangConfigV1> YouQiangConfigs { get; set; }
        }

        public class YouQiangConfigV1
        {
            /// <summary>
            /// 每台油面下的枪号,由1开始编号，作用范围是一个面，即换面则重置，重新从1开始。
            ///     如某个面上有2把枪，则此编号为1、2，另外一个面上有3把枪，则此编号为1、2、3.
            /// </summary>
            public int QiangHao { get; set; }

            /// <summary>
            /// 枪号,对应"加油流水表"和"油枪状态表"的枪号(jihao),整个油站的所有枪号由1开始编号
            ///    
            /// 应该与FDC SERVER中的site level nozzle id相匹配对应。
            /// </summary>

            public int LuoJiQiangHao { get; set; }
        }
        #endregion

        protected AppBase(AppConfigV1 appConfig, IServiceProvider services)
        {
            this.appConfig = appConfig;
            var loggerFactory = services.GetRequiredService<ILoggerFactory>();
            this.logger = loggerFactory.CreateLogger("Application");
        }

        /// <summary>
        /// Truncate table jyjpz and jy_info, and then re-insert site nozzles info to them.
        /// </summary>
        /// <param name="dbConnCreator"></param>
        /// <param name="dbCommandCreator"></param>
        /// <returns></returns>
        protected bool ReCreatePumpLayoutToDatabase(Func<DbConnection> dbConnCreator,
            Func<DbCommand> dbCommandCreator)
        {
            try
            {

                #region truncate table [jyjpz] and [jy_info], and then insert data into table [jyjpz]

                // for now, always overwrite the whole table in POS database without further check for rows or columns level.
                using (var posConn = dbConnCreator())
                {
                    /* configInFcDb structure like below */
                    //"   JiHao: " + oneRow["jiHao"] + ", sideId: " + oneRow["sideId"] +
                    //", nozzleLogicalId: " + oneRow["HoseLogicalId"] +
                    //", siteLevelNozzleId: " + oneRow["siteLevelNozzleId"]

                    string bulkInsertCmd = "";
                    const string sqlConcateAppendix = "Union all ";
                    foreach (var youJiConfig in this.appConfig.YouJiConfigs)
                    {
                        foreach (var youMianConfig in youJiConfig.YouMianConfigs)
                        {
                            foreach (var youQiangConfig in youMianConfig.YouQiangConfigs)
                            {
                                bulkInsertCmd +=
                                    $"Select {youJiConfig.JiHao}, '{youMianConfig.YouMianBianHao}', {youQiangConfig.QiangHao}, {youQiangConfig.LuoJiQiangHao} {sqlConcateAppendix}";
                            }
                        }
                    }

                    var truncateAndInsertCmd = dbCommandCreator();
                    truncateAndInsertCmd.Connection = posConn;
                    truncateAndInsertCmd.CommandText =
                            "TRUNCATE table " + this.appConfig.JiaYouJiPeiZhi_TableName + "; TRUNCATE table jy_info; INSERT " + this.appConfig.JiaYouJiPeiZhi_TableName + " (jihao, abtype, qianghao, luojiqh) " + bulkInsertCmd.Substring(0, bulkInsertCmd.Length - sqlConcateAppendix.Length);
                    logger.LogInformation("truncateAndInsertSiteConfigCmd: " + truncateAndInsertCmd.CommandText);
                    posConn.Open();
                    truncateAndInsertCmd.ExecuteNonQuery();
                    //return true;
                };

                #endregion

                #region insert data into table [jy_info]

                //foreach (var nozzle in sortedSiteNozzlesDataRows)//.Select(n => n["siteLevelNozzleId"]))
                //{
                //var siteLevelNozzleId = nozzle["siteLevelNozzleId"];
                foreach (var nozzle in fdcServerHostApp.FdcPumpControllers.SelectMany(c => c.Nozzles))
                {
                    //var pumpId = int.Parse(nozzle["pumpid"].ToString());
                    //var pump = fdcServerHostApp.FdcPumpControllers.First(f => f.PumpId == pumpId);
                    //var nozzleLogicalId = int.Parse(nozzle["HoseLogicalId"].ToString());
                    using (var posSqlConnection = dbConnCreator())
                    {
                        var bindingNozzleExtraInfo = this.fdcServerHostApp.GetNozzleExtraInfos()?.FirstOrDefault(f => f.PumpId == nozzle.PumpId
                                   && f.NozzleLogicalId == nozzle.LogicalId);
                        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 lastTrx = fdcServerHostApp.GetAvailableFuelSaleTrxsWithDetailsAsync(nozzle.PumpId, nozzle.LogicalId, 1).Result.FirstOrDefault();
                            var totalizer = fdcServerHostApp.GetFuelPointTotalsAsync(nozzle.PumpId, nozzle.LogicalId).Result;
                            //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 = dbCommandCreator();
                            setPumpOnStartingCommand.Connection = posSqlConnection;
                            setPumpOnStartingCommand.CommandText = this.InitTable_jy_info_SqlCommand(
                                (bindingNozzleExtraInfo?.SiteLevelNozzleId ?? -1).ToString(),
                                (bindingNozzleExtraInfo?.ProductName ?? "undefined"),
                                totalizer.Item2, totalizer.Item1);
                            logger.LogDebug("initTable_jy_info_Command: " + setPumpOnStartingCommand.CommandText);
                            posSqlConnection.Open();
                            setPumpOnStartingCommand.ExecuteNonQuery();
                        }
                        catch (Exception ex)
                        {
                            logger.LogError("executing initTable_jy_info_Command failed for siteLevelNozzleId: " + (bindingNozzleExtraInfo?.SiteLevelNozzleId ?? -1) + ", exception detail: " + ex);
                            throw;
                        }
                    }
                }

                #endregion

            }
            catch (Exception ex)
            {
                logger.LogError("PerformCompareAndUpdate exceptioned, detail: " + ex);
                return false;
            }

            return true;
        }

        protected virtual string InitTable_jy_info_SqlCommand(string siteLevelNozzleId, string gradeFriendlyName,
            double totalizerVol, double totalizerAmt)
        {
            return 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,
                                totalizerVol, totalizerAmt);
        }

        /// <summary>
        /// attach pump event for inserting correlated data to database tables.
        /// </summary>
        /// <param name="dbConnCreator"></param>
        /// <param name="dbCommandCreator"></param>
        /// <returns></returns>
        protected bool HandlePumpStateChangeToDatabase(Func<DbConnection> dbConnCreator,
            Func<DbCommand> dbCommandCreator)
        {
            var siteLevelNozzldIdsInAppConfig = this.appConfig.YouJiConfigs.SelectMany(youJiConfig => youJiConfig.YouMianConfigs.SelectMany(youMianConfig => youMianConfig.YouQiangConfigs))
                .Select(youQiangConfig => youQiangConfig.LuoJiQiangHao);
            var siteLevelNozzldIdsInFdcServerApp = this.fdcServerHostApp.GetNozzleExtraInfos().Where(ri => ri.SiteLevelNozzleId.HasValue).Select(ri => ri.SiteLevelNozzleId.Value);
            var missedInFdcServerApp = siteLevelNozzldIdsInAppConfig.Except(siteLevelNozzldIdsInFdcServerApp);
            var missedInAppConfig = siteLevelNozzldIdsInFdcServerApp.Except(siteLevelNozzldIdsInAppConfig);
            if (missedInFdcServerApp.Any())
            {
                throw new ArgumentException("油枪(以此App中 全站枪号 为标识): "
                    + missedInFdcServerApp.Select(n => n.ToString()).Aggregate((acc, n) => acc + ", " + n) + " 未配置于FdcServerApp中, 请配置后再试");
            }

            if (missedInAppConfig.Any())
            {
                throw new ArgumentException("油枪(以 FdcServerApp中的全站枪号 为标识): "
                    + missedInAppConfig.Select(n => n.ToString()).Aggregate((acc, n) => acc + ", " + n) + " 未配置于此App中, 请配置后再试");
            }

            fdcServerHostApp.OnStateChange += (s, a) =>
            {
                var pump = s as IFdcPumpController;
                try
                {
                    if (a.NewPumpState == LogicalDeviceState.FDC_READY)
                    {
                        /* indicate for nozzle if replaced back */
                        var sizeLevelNozzleIdsOnPump = this.fdcServerHostApp.GetNozzleExtraInfos().Where(ri => ri.PumpId == pump.PumpId && ri.SiteLevelNozzleId.HasValue).Select(ri => ri.SiteLevelNozzleId.Value);
                        if (!sizeLevelNozzleIdsOnPump.Any())
                        {
                            logger.LogInformation("Could not found any site level nozzle id defined on pump: " + pump.PumpId);
                            return;
                        }

                        using (var posSqlConnection = dbConnCreator())
                        {
                            try
                            {
                                /* idle would not carry nozzle id, so here reset all nozzles on target pump.*/
                                var setPumpOnIdleCommand = dbCommandCreator();
                                setPumpOnIdleCommand.Connection = posSqlConnection;
                                setPumpOnIdleCommand.CommandText = sizeLevelNozzleIdsOnPump.Select(
                                    siteLevelNozzleId =>
                                        string.Format(
                                            "Update jy_info set status = '{1}', qty=0, amount=0 where jihao = {0};", siteLevelNozzleId, 'F')
                                    ).Aggregate((acc, n) => acc + "    " + n);
                                logger.LogDebug("setPumpOnIdleCommand(via Fdc): " + setPumpOnIdleCommand.CommandText);
                                posSqlConnection.Open();
                                setPumpOnIdleCommand.ExecuteNonQuery();
                            }
                            catch (Exception ex)
                            {
                                logger.LogError("executing setPumpOnIdleCommand(via Fdc) failed, exception detail: " + ex);
                            }
                        }
                    }
                    else if (a.NewPumpState == LogicalDeviceState.FDC_CALLING)
                    {
                        using (var posSqlConnection = dbConnCreator())
                        {
                            try
                            {
                                var operatingNozzleLogicalId = a?.StateChangedNozzles?.FirstOrDefault()?.LogicalId ?? (byte)0;
                                var bindingNozzleExtraInfo = this.fdcServerHostApp.GetNozzleExtraInfos()?.FirstOrDefault(f => f.PumpId == pump.PumpId
                                    && f.NozzleLogicalId == operatingNozzleLogicalId);
                                //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 = dbCommandCreator();
                                updatePumpOnFuelingCommand.Connection = posSqlConnection;
                                updatePumpOnFuelingCommand.CommandText =
                                    string.Format("Update jy_info set status = '{1}' where jihao = '{0}';"
                                        , (bindingNozzleExtraInfo?.SiteLevelNozzleId ?? -1),
                                        'B',
                                        (bindingNozzleExtraInfo?.ProductName ?? "undefined"));
                                logger.LogDebug("updatePumpOnFuelingCommand: " + updatePumpOnFuelingCommand.CommandText);
                                posSqlConnection.Open();
                                updatePumpOnFuelingCommand.ExecuteNonQuery();
                            }
                            catch (Exception ex)
                            {
                                logger.LogError("executing updatePumpOnFuelingCommand failed, exception detail: " + ex);
                            }
                        }
                    }
                }
                catch (Exception exx) { }
            };
            fdcServerHostApp.OnCurrentFuellingStatusChange += async (s, a) =>
            {
                try
                {
                    var pump = s as IFdcPumpController;
                    if (!a.Transaction.Finished)
                    {
                        //add some random drops to reduce the operations in db, this is for performance improve.
                        if (DateTime.Now.Millisecond % 2 == 0 || DateTime.Now.Millisecond % 3 == 0) return;
                        using (var posSqlConnection = dbConnCreator())
                        {
                            try
                            {
                                var bindingNozzleExtraInfo = this.fdcServerHostApp.GetNozzleExtraInfos().FirstOrDefault(f => f.PumpId == pump.PumpId
                                    && f.NozzleLogicalId == a.Transaction.Nozzle.LogicalId);
                                //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 = dbCommandCreator();
                                updatePumpOnFuelingCommand.Connection = posSqlConnection;
                                updatePumpOnFuelingCommand.CommandText =
                                    string.Format("Update jy_info set youpin = N'{2}', qty= {3}, amount= {4} where jihao = '{0}';"
                                        , (bindingNozzleExtraInfo?.SiteLevelNozzleId ?? -1),
                                        "",
                                        (bindingNozzleExtraInfo?.ProductName ?? "undefined"),
                                        a.Transaction.Volumn / Math.Pow(10, pump.VolumeDecimalDigits),
                                        a.Transaction.Amount / Math.Pow(10, pump.AmountDecimalDigits));
                                logger.LogDebug("updatePumpOnFuelingCommand: " + updatePumpOnFuelingCommand.CommandText);
                                posSqlConnection.Open();
                                updatePumpOnFuelingCommand.ExecuteNonQuery();
                            }
                            catch (Exception ex)
                            {
                                logger.LogError("executing updatePumpOnFuelingCommand failed, exception detail: " + ex);
                            }
                        }
                    }
                    else
                    {

                        #region totalizer value reading

                        double volumeTotalizerValue = 0, amountTotalizerValue = 0;
                        if (a.Transaction.VolumeTotalizer.HasValue)
                        {
                            /*indicates this kind of device will carry totalizer with trx, use it directly*/
                            volumeTotalizerValue = a.Transaction.VolumeTotalizer.Value / Math.Pow(10, pump.VolumeDecimalDigits);
                            amountTotalizerValue = a.Transaction.AmountTotalizer ?? -1;
                        }
                        else
                        {
                            /*indicates this kind of device will NOT carry totalizer with trx, 
                             * have to query it*/
                            var queried = await fdcServerHostApp.GetFuelPointTotalsAsync(pump.PumpId, a.Transaction.Nozzle.LogicalId);
                            volumeTotalizerValue = queried.Item2;
                            amountTotalizerValue = queried.Item1;
                        }

                        #endregion

                        var bindingNozzleExtraInfo = this.fdcServerHostApp.GetNozzleExtraInfos()?.FirstOrDefault(f => f.PumpId == pump.PumpId
                                   && f.NozzleLogicalId == a.Transaction.Nozzle.LogicalId);
                        var posSqlConnection = dbConnCreator();
                        using (posSqlConnection)
                        {
                            try
                            {
                                //var rawProductNo = nozzleProductConfig.FirstOrDefault(f => f.PumpId == pump.PumpId
                                //    && f.NozzleLogicalId == a.Transaction.Nozzle.LogicalId)?.ProductBarcode;

                                //var totalizer = SiteConfigUtility.Default.GetTotalizer(e.Fuelling.Pump.Id, e.Fuelling.Nozzle.Id);
                                var updateFuelingTrxDoneCommand = dbCommandCreator();
                                updateFuelingTrxDoneCommand.Connection = posSqlConnection;
                                updateFuelingTrxDoneCommand.CommandText =
                                    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})",
                                        (bindingNozzleExtraInfo?.SiteLevelNozzleId ?? -1),
                                        (bindingNozzleExtraInfo?.ProductName ?? "undefined"),
                                        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,
                                        volumeTotalizerValue,
                                        amountTotalizerValue);
                                logger.LogInformation("updateFuelingTrxDoneCommand: " + updateFuelingTrxDoneCommand.CommandText);
                                posSqlConnection.Open();
                                updateFuelingTrxDoneCommand.ExecuteNonQuery();
                            }
                            catch (Exception ex)
                            {
                                logger.LogError("executing updateFuelingTrxDoneCommand failed, exception detail: " + ex);
                            }
                        }

                        using (var sqlConnection = dbConnCreator())
                        {
                            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 = dbCommandCreator();
                                updatePumpOnFuelingCommand.Connection = sqlConnection;
                                updatePumpOnFuelingCommand.CommandText =
                                    string.Format("Update jy_info set fzqty = '{1}', fzamount={2} where jihao = '{0}'",
                                        (bindingNozzleExtraInfo?.SiteLevelNozzleId ?? -1),
                                        volumeTotalizerValue,
                                        amountTotalizerValue);
                                logger.LogDebug("updatePumpOnFuelingCommand: " + updatePumpOnFuelingCommand.CommandText);
                                sqlConnection.Open();
                                updatePumpOnFuelingCommand.ExecuteNonQuery();
                            }
                            catch (Exception ex)
                            {
                                logger.LogError("executing updatePumpOnFuelingCommand failed, exception detail: " + ex);
                            }
                        }
                    }
                }
                catch (Exception exxx) { }
            };
            return true;
        }
    }
}