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 } };
}
}