FCC/Applications/PetroChina/PetroChinaOnlineWatchPlugin/SqlClientHelper.cs

using Microsoft.Data.SqlClient;
using Microsoft.Extensions.Logging;
using PetroChinaOnlineWatchPlugin.MessageEntity.Outgoing;
using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.IO;

namespace PetroChinaOnlineWatchPlugin
{
    internal class SqlClientHelper
    {
        public static List<IfsfMessageDataOut> ReadOnlineMonitorData(AppConfigV1 appConfig, ILogger logger, ref Dictionary<string, object> tempDic, DbAddressType dbAddressType)
        {
            List<IfsfMessageDataOut> dataList = null;
            try
            {
                var sqlSettings = ConfigurationManager.OpenMappedExeConfiguration(new ExeConfigurationFileMap()
                {
                    ExeConfigFilename = Path.Combine(appConfig.MainAppDirectory, "SqlConnect.config")
                }, ConfigurationUserLevel.None).AppSettings.Settings;
                using (var connection = new SqlConnection(sqlSettings["SqlConn"].Value))
                {
                    if (connection.State != ConnectionState.Open)
                        connection.Open();

                    string pressureDataTable = $"PressureDataNew_{ DateTime.Today.ToString("yyyyMM")}";
                    string queryString = null;
                    if (dbAddressType == DbAddressType.Configure)
                    {
                        queryString = "select top 1 StartDate, JYQS, PVZ, PVF from T_GasStationConfig";
                    }
                    else if (dbAddressType == DbAddressType.EnvironmentData)
                    {
                        queryString = "select top 1 A.Id as pdId, B.Id as alId, A.ReceiveTime, A.YGYL, A.YZYL, A.WQND, A.YGWD, B.AlrmType, B.IsAlarm " +
                            $"from (select top 1 Id, ReceiveTime, YGYL, YZYL, WQND, YGWD, isupload_bd from {pressureDataTable} where isupload_bd=0) A " +
                            "full outer join (select top 1 Id, AlrmType, IsAlarm, isupload from AlarmLog where isupload=0) B on A.isupload_bd=B.isupload";
                    }
                    else if (dbAddressType == DbAddressType.PumpData)
                    {
                        queryString = "select * from " +
                            "(select * from (select top 1 Id as ogtId, ReceiveTime, MacCode, GunCode, TempRatio, MeanGasQ, TempGas, MeanOilQ, TempOil, isupload " +
                            "from OilGunTemp_D where isupload=0) A full outer join " +
                            "(select top 1 StartDate, JYQS, PVZ, PVF, isupload_bd from T_GasStationConfig) B on A.isupload=B.isupload_bd) D " +
                            "full outer join (select top 1 Id as ogpId, opertime, macname, gunname, eventcode, operate, isupload " +
                            "from OilGunOperLog where isupload=0) C on D.isupload=C.isupload";
                    }
                    else if (dbAddressType == DbAddressType.FaultData)
                    {
                        queryString = "select top 1 Id, repairTime, EquipmentType, EquipmentNumber, repairCode " +
                            "from T_RepairRecord where isupload=0";
                    }

                    using (var command = new SqlCommand(queryString, connection))
                    {
                        using (var reader = command.ExecuteReader())
                        {
                            reader.Read();
                            if (!reader.HasRows)
                                return null;

                            switch (dbAddressType)
                            {
                                case DbAddressType.Configure:
                                    var oilSettings = ConfigurationManager.OpenMappedExeConfiguration(new ExeConfigurationFileMap()
                                    {
                                        ExeConfigFilename = Path.Combine(appConfig.MainAppDirectory, "OilGun.config")
                                    }, ConfigurationUserLevel.None).AppSettings.Settings;
                                    var dataSettings = ConfigurationManager.OpenMappedExeConfiguration(new ExeConfigurationFileMap()
                                    {
                                        ExeConfigFilename = Path.Combine(appConfig.MainAppDirectory, "DataPressure.config")
                                    }, ConfigurationUserLevel.None).AppSettings.Settings;

                                    // CF_ID 01H, CF_Message - Data_Id 64H, Enable_Time - Data_Id 02H, Pump_Number - Data_Id 03H
                                    // Positive_Pressure_Value - Data_Id 04H, Negative_Pressure_Value - Data_Id 05H, Post_Treatmen_Pressuret - Data_Id 06H
                                    // Dispenser_Number - Data_Id 07H, 气液比预警下限值 - Data_Id 08H, 气液比预警上限值 - Data_Id 09H, 预警次数比例 - Data_Id 0AH
                                    // 气液比预警转换时间 - Data_Id 0BH, 环境数据采集间隔 - Data_Id 0CH, P/V 阀压力预报警转换时间 - Data_Id 0DH, 罐压预警上限值 - Data_Id 0EH
                                    // 罐压预警下限值 - Data_Id 0FH, 罐压预警时间 - Data_Id 10H, 罐压报警时间 - Data_Id 11H, 油气浓度报警阈值 - Data_Id 12H
                                    //    new Bin8Message(0x07, 8), 
                                    dataList = new List<IfsfMessageDataOut>() { new DateTimeMessage(0x02, reader["StartDate"]), new Bin8Message(0x03, reader["JYQS"]),
                                        new Bin8Bcd6Message(0x04, reader["PVZ"]), new Bin8Bcd6Message(0x05, reader["PVF"]), new Bin8Bcd6Message(0x06, oilSettings["CLZZQDYL"].Value),
                                        new Bin8Message(0x07, appConfig.DispenserNumber), new Bin8Bcd4Message(0x08, oilSettings["QYBMIN"].Value), new Bin8Bcd4Message(0x09, oilSettings["QYBMAX"].Value), new Bin8Bcd4Message(0x0A, oilSettings["QYBBL"].Value),
                                        new Bin8Message(0x0B, oilSettings["QYBBJ"].Value), new Bin8Message(0x0C, dataSettings["HCLDelay"].Value), new Bin8Message(0x0D, oilSettings["ZKFBJ"].Value), new Bin8Bcd6Message(0x0E, oilSettings["YGLYMAX"].Value),
                                        new Bin8Bcd6Message(0x0F, oilSettings["YGLYMIN"].Value), new Bin8Message(0x10, oilSettings["YGLYYJ"].Value), new Bin8Message(0x11, oilSettings["YGLYBJ"].Value), new Bcd4Message(0x12, oilSettings["XielouConcentration"].Value)
                                    };
                                    break;
                                case DbAddressType.EnvironmentData:
                                    // EM_ID 11H, Monitor_Time - Data_Id 02H, Gas_Tank_Pressure - Data_Id 03H, Liquid_Resistance_Pressure - Data_Id 04H
                                    // Gas_Volume - Data_Id 05H, Concentration - Data_Id 06H, Tank_Temp - Data_Id 07H
                                    string alrmLogId = reader["alId"].ToString();
                                    updateString = $"update {pressureDataTable} set isupload_bd=1 where Id='{reader["pdId"]}';";
                                    updateString += alrmLogId == "" ? "" : $"update AlarmLog set isupload=1 where Id='{reader["alId"]}'";
                                    tempDic["LiquidResistance"] = reader["YZYL"];
                                    dataList = new List<IfsfMessageDataOut>() { new DateTimeMessage(0x02, reader["ReceiveTime"]), new Bin8Bcd6Message(0x03, reader["YGYL"]),
                                        new Bin8Bcd6Message(0x04, reader["YZYL"]), new Bin8Bcd8Message(0x05, 0m), new Bcd4Message(0x06, reader["WQND"]), new Bin8Bcd4Message(0x07, reader["YGWD"])
                                    };
                                    // EM_ID 11H, Zero_Ppressure_Alarm - Data_Id 10H, Tank_Pressure_Alarm - Data_Id 11H, Critical_Pressure_Alarm - Data_Id 12H
                                    // Post_Process_Alarm - Data_Id 13H, Airtight_Alarm - Data_Id 14H, Liquid_Resistance_Alarm - Data_Id 15H, 处理装置启动状态报警 - Data_Id 16H
                                    // 处理装置停止状态报警 - Data_Id 17H, 卸油回气管状态报警 - Data_Id 18H
                                    string alrmType = reader["AlrmType"].ToString();
                                    if (alrmTypeDataIdMap.ContainsKey(alrmType))
                                    {
                                        alrmTypeDataIdMap[alrmType][1] = 2; //2：报警
                                    }
                                    else if (alrmType == "1")
                                    {
                                        tempDic["GasLiquidRatioAlarm"] = 2; //2：报警
                                    }
                                    foreach (byte[] ar in alrmTypeDataIdMap.Values)
                                    {
                                        dataList.Add(new Bin8Message(ar[0], ar[1]));
                                    }
                                    break;
                                case DbAddressType.PumpData:
                                    // DP_ID 21H, Monitor_Time - Data_Id 02H, Dispenser_ID - Data_Id 03H, Pump_ID - Data_Id 04H
                                    // Gas_Liquid_Ratio - Data_Id 05H, Gas_Current_Speed - Data_Id 06H, Gas_Flow - Data_Id 07H, Fueling_Speed - Data_Id 08H
                                    // Fueling_Volume - Data_Id 09H, Liquid_Resistance - Data_Id 0AH
                                    // DP_ID 21H, Monitor_Time - Data_Id 02H, Dispenser_ID - Data_Id 03H, Pump_ID - Data_Id 04H
                                    // DP_ID 21H, Gas_Liquid_Ratio_Alarm - Data_Id 14H
                                    // DP_ID 21H, Monitor_Time - Data_Id 02H, Dispenser_ID - Data_Id 03H, Pump_ID - Data_Id 04H
                                    // DP_ID 21H, Pump_Start_Stop_State - Data_Id 1EH, Operation_Type - Data_Id 1FH
                                    string ogpId = reader["ogpId"].ToString();
                                    updateString = $"update OilGunTemp_D set isupload=1 where Id='{reader["ogtId"]}';";
                                    updateString += ogpId == "" ? "" : $"update OilGunOperLog set isupload=1 where Id='{reader["ogpId"]}'";
                                    object liquidResistance = tempDic.ContainsKey("LiquidResistance") ? tempDic["LiquidResistance"] : "0";
                                    object gasLiquidRatioAlarm = tempDic.ContainsKey("GasLiquidRatioAlarm") ? tempDic["GasLiquidRatioAlarm"] : "0";
                                    dataList = new List<IfsfMessageDataOut>() { new DateTimeMessage(0x02, reader["ReceiveTime"]), new Bin8Message(0x03, reader["MacCode"]), new Bin8Message(0x04, reader["GunCode"]),
                                        new Bin8Bcd4Message(0x05, reader["TempRatio"]), new Bin8Bcd6Message(0x06, reader["MeanGasQ"]), new Bin8Bcd6Message(0x07, reader["TempGas"]),
                                        new Bin8Bcd6Message(0x08, reader["MeanOilQ"]), new Bin8Bcd8Message(0x09, reader["TempOil"]), new Bin8Bcd6Message(0x0A, liquidResistance),
                                        new Bin8Message(0x14, gasLiquidRatioAlarm),
                                        new Bin8Message(0x1E, reader["eventcode"]), new Bin8Message(0x1F, reader["operate"])
                                    };
                                    break;
                                case DbAddressType.FaultData:
                                    // ER_ID 41H, Fault_Time - Data_Id 01H, Device_Type - Data_Id 02H, Device_ID - Data_Id 03H, Fault_ID - Data_Id 04H
                                    updateString = $"update T_RepairRecord set isupload=1 where Id='{reader["Id"]}'";
                                    int faultId = 5;
                                    //3．第3位故障码表示故障类型：1：通信故障，2：设备故障，3：软件故障，4：数据库故障，其它数字：其它故障
                                    int faultCode = int.Parse(reader["repairCode"].ToString().Substring(2, 1));
                                    if (faultCode > 0 && faultCode < 5)
                                        faultId = faultCode;
                                    dataList = new List<IfsfMessageDataOut>() { new DateTimeMessage(0x01, reader["repairTime"]), new Bin8Message(0x02, deviceTypeMap[reader["EquipmentType"].ToString()]),
                                        new Bin8Message(0x03, reader["EquipmentNumber"]), new Bin8Message(0x04, faultId)
                                    };
                                    break;
                                default:
                                    break;
                            }
                        }
                    }
                    return dataList;
                }
            }
            catch (Exception ex)
            {
                logger.LogError($"ReadOnlineMonitorData error, exception detail: {ex}");
                return null;
            }
        }

        public static void UpdateOnlineMonitorData(AppConfigV1 appConfig, ILogger logger)
        {
            try
            {
                var sqlSettings = ConfigurationManager.OpenMappedExeConfiguration(new ExeConfigurationFileMap()
                {
                    ExeConfigFilename = Path.Combine(appConfig.MainAppDirectory, "SqlConnect.config")
                }, ConfigurationUserLevel.None).AppSettings.Settings;
                using (var connection = new SqlConnection(sqlSettings["SqlConn"].Value))
                {
                    if (connection.State != ConnectionState.Open)
                        connection.Open();

                    using (var command = new SqlCommand(updateString, connection))
                    {
                        command.ExecuteNonQuery();
                    }
                }
            }
            catch (Exception ex)
            {
                logger.LogError($"updateString: {updateString}");
                logger.LogError($"UpdateOnlineMonitorData error, exception detail: {ex}");
            }
        }

        private static string updateString = null;
        // EM_ID 11H, Zero_Ppressure_Alarm - Data_Id 10H, Tank_Pressure_Alarm - Data_Id 11H, Critical_Pressure_Alarm - Data_Id 12H
        // Post_Process_Alarm - Data_Id 13H, Airtight_Alarm - Data_Id 14H, Liquid_Resistance_Alarm - Data_Id 15H, 处理装置启动状态报警 - Data_Id 16H
        // 处理装置停止状态报警 - Data_Id 17H, 卸油回气管状态报警 - Data_Id 18H
        private static Dictionary<string, byte[]> alrmTypeDataIdMap = new Dictionary<string, byte[]>() { { "11", new byte[] { 0x10, 0 } }, { "6", new byte[] { 0x11, 0 } },
            { "4", new byte[] { 0x12, 0 } }, { "5", new byte[] { 0x13, 0 } }, { "2", new byte[] { 0x14, 0 } }, { "3", new byte[] { 0x15, 0 } }, { "7", new byte[] { 0x16, 0 } },
            { "8", new byte[] { 0x17, 0 } }, { "9", new byte[] { 0x18, 0 } } };
        private static Dictionary<string, int> deviceTypeMap = new Dictionary<string, int>() { { "采集器", 1 }, { "油气流量传感器", 2 }, { "压力传感器", 3 },
            { "浓度传感器", 4 }, { "温度传感器", 5 }, { "控制台", 6 } };
    }
}