ibms-dome/Repository/Services/Implement/OntimeDeviceSubscripService.cs
2022-10-14 16:08:54 +08:00

661 lines
33 KiB
C#
Raw Permalink Blame History

This file contains ambiguous Unicode characters

This file contains Unicode characters that might be confused with other characters. If you think that this is intentional, you can safely ignore this warning. Use the Escape button to reveal them.

using System;
using System.Collections.Generic;
using Dapper;
using System.Data;
using System.Linq;
using System.Threading.Tasks;
using System.Text;
using System.Net;
using Repository.Models;
using Microsoft.Extensions.Options;
using System.IO;
using System.Xml;
using Newtonsoft.Json;
using Newtonsoft.Json.Linq;
namespace Repository.Services.Implement
{
public class ObixApiConfig
{
public string ApiBase { get; set; }
public string UserName { get; set; }
public string Password { get; set; }
}
public class Variable
{
public string System_type { get; set; }
public string System_key { get; set; }
public string system_value { get; set; }
}
public class DeviceSubscrip
{
public string building_guid { get; set; }
public string main_system_guid { get; set; }
public string sub_system_guid { get; set; }
public string device_number { get; set; }
}
public class OntimeDeviceSubscripService
{
private readonly IDbConnection conn;
private readonly IDbTransaction trans;
private readonly IOptions<ObixApiConfig> _obixApiConfig;
public OntimeDeviceSubscripService(IDbConnection conn, IDbTransaction trans, IOptions<ObixApiConfig> _obixApiConfig)
{
this.conn = conn;
this.trans = trans;
this._obixApiConfig = _obixApiConfig;
}
private string InsertGenerateString(List<string> properties, string table_name)
{
var insertQuery = new StringBuilder($"INSERT INTO {table_name} ");
insertQuery.Append("(");
properties.ForEach(prop => { insertQuery.Append($"{table_name}.{prop.Replace("@", "")},"); });
insertQuery
.Remove(insertQuery.Length - 1, 1)
.Append(") VALUES (");
properties.ForEach(prop => { insertQuery.Append($"{prop},"); });
insertQuery
.Remove(insertQuery.Length - 1, 1)
.Append(");");
return insertQuery.ToString();
}
public void MaintainOntimeDeviceSubscription(string targetTable, string mode, Dictionary<string, object> targetData, string origData = null)
{
List<Dictionary<string, object>> final_device_subscrip = new List<Dictionary<string, object>>();
List<DeviceSubscrip> tempDeviceSubscrips = new List<DeviceSubscrip>();
//判斷來源是什麼資料表
if ("device" == targetTable)
{ //設備表格
switch (mode)
{
case "delete": //軟刪除
//直接刪除該設備
object device_guid = null;
var has_device_guid = targetData.TryGetValue("device_guid", out device_guid);
var sql_device = $@"SELECT device_number FROM device WHERE device_guid = @device_guid";
var device_number = conn.QueryFirstOrDefault<string>(sql_device, new { device_guid = device_guid.ToString() }, trans);
if (!string.IsNullOrEmpty(device_number))
{
var purge_for_delete_sql = $@"DELETE FROM ontime_device_subscription WHERE device_number = @device_number";
conn.Execute(purge_for_delete_sql, new { device_number = device_number }, trans);
}
break;
}
}
else if ("device_item" == targetTable)
{ //點位表格
List<DeviceSubscrip> deviceSubscrips = new List<DeviceSubscrip>();
// step 1 找出所有該系統小類的設備
if (mode != "delete")
{
var sql_device = $@"SELECT building_guid, main_system_guid, sub_system_guid, device_number FROM device WHERE deleted = 0 AND sub_system_guid = @sub_system_guid";
object sub_system_guid = null;
var has_value = targetData.TryGetValue("@sub_system_guid", out sub_system_guid);
deviceSubscrips = conn.Query<DeviceSubscrip>(sql_device, new { sub_system_guid = sub_system_guid.ToString() }).ToList();
tempDeviceSubscrips = deviceSubscrips;
}
else
{ //需先找出該點位的sub_system_guid
object device_item_guid = null;
var has_value = targetData.TryGetValue("device_item_guid", out device_item_guid);
if (has_value)
{
var sql_device_item = $@"SELECT sub_system_guid FROM device_item WHERE device_item_guid = @device_item_guid";
var sub_system_guid = conn.QueryFirstOrDefault<string>(sql_device_item, new { device_item_guid = device_item_guid.ToString() });
var sql_device = $@"SELECT building_guid, main_system_guid, sub_system_guid, device_number FROM device WHERE deleted = 0 AND sub_system_guid = @sub_system_guid";
deviceSubscrips = conn.Query<DeviceSubscrip>(sql_device, new { sub_system_guid = sub_system_guid.ToString() }).ToList();
tempDeviceSubscrips = deviceSubscrips;
}
}
//判斷是什麼模式ex:insert、insert_list、update...等
switch (mode)
{
case "insert": //新增
List<Dictionary<string, object>> insertDics = new List<Dictionary<string, object>>();
object point_name = null;
var has_point_value = targetData.TryGetValue("@points", out point_name);
if (has_point_value)
{
foreach (var deviceSubscrip in deviceSubscrips)
{
Dictionary<string, object> insertDic = new Dictionary<string, object>()
{
{ "@building_guid", deviceSubscrip.building_guid },
{ "@main_system_guid", deviceSubscrip.main_system_guid },
{ "@sub_system_guid", deviceSubscrip.sub_system_guid },
{ "@device_number", deviceSubscrip.device_number },
{ "@name", point_name}
};
insertDics.Add(insertDic);
}
}
List<string> properties = insertDics.First().Keys.ToList();
string sql = InsertGenerateString(properties, "ontime_device_subscription");
conn.Execute(sql, insertDics, trans);
final_device_subscrip = insertDics;
break;
case "update": //更新
if (origData != null)
{
var device_point_name = origData;
//刪除訂閱表的資料
var purge_sql = $@"DELETE FROM ontime_device_subscription WHERE name=@name AND device_number IN @device_number";
conn.Execute(purge_sql, new { name = device_point_name, device_number = deviceSubscrips.Select(x => x.device_number).ToList() }, trans);
}
//新增訂閱表的資料
List<Dictionary<string, object>> insertDics2 = new List<Dictionary<string, object>>();
object point_name2 = null;
var has_point_value2 = targetData.TryGetValue("@points", out point_name);
if (has_point_value2)
{
foreach (var deviceSubscrip in deviceSubscrips)
{
Dictionary<string, object> insertDic = new Dictionary<string, object>()
{
{ "@building_guid", deviceSubscrip.building_guid },
{ "@main_system_guid", deviceSubscrip.main_system_guid },
{ "@sub_system_guid", deviceSubscrip.sub_system_guid },
{ "@device_number", deviceSubscrip.device_number },
{ "@name", point_name}
};
insertDics2.Add(insertDic);
}
}
List<string> properties2 = insertDics2.First().Keys.ToList();
string sql2 = InsertGenerateString(properties2, "ontime_device_subscription");
conn.Execute(sql2, insertDics2, trans);
final_device_subscrip = insertDics2;
break;
case "delete": //軟刪除
//找出原本的
object delete_device_item_guid = null;
var has_delete_device_item_guid = targetData.TryGetValue("device_item_guid", out delete_device_item_guid);
if (has_delete_device_item_guid)
{
var sql_device_item = $@"SELECT points FROM device_item WHERE device_item_guid = @device_item_guid";
var delete_device_point_name = conn.QueryFirst<string>(sql_device_item, new { device_item_guid = delete_device_item_guid.ToString() });
//刪除訂閱表的資料
var purge_for_delete_sql = $@"DELETE FROM ontime_device_subscription WHERE name=@name AND device_number IN @device_number";
conn.Execute(purge_for_delete_sql, new { name = delete_device_point_name, device_number = deviceSubscrips.Select(x => x.device_number).ToList() }, trans);
}
break;
}
}
#region step 2
var subscripUriFormat = @" <uri val='/obix/config/Arena/{0}/{1}/{2}/out/' />";
List<string> subscripStrings = new List<string>();
foreach (var deviceSubscrip in final_device_subscrip)
{
List<string> devicePaths = new List<string>();
object device_number = null;
var has_device_number = deviceSubscrip.TryGetValue("@device_number", out device_number);
//拆解設備編號來組出路徑
var deviceSubscripSplit = device_number.ToString().Split("_");
foreach (var temp in deviceSubscripSplit)
{
if (temp[0].ToString().All(char.IsDigit))
{
devicePaths.Add($"$3{temp}");
}
else
{
devicePaths.Add(temp);
}
}
//剔除最後一個,因為是設備名稱
devicePaths.RemoveAt(devicePaths.Count() - 1);
object point_name = null;
var has_point_name = deviceSubscrip.TryGetValue("@name", out point_name);
subscripStrings.Add(string.Format(subscripUriFormat, string.Join('/', devicePaths), device_number.ToString(), point_name.ToString()));
}
#endregion step 2
#region step 3
EDFunction ed = new EDFunction();
string userName = ed.AESDecrypt(_obixApiConfig.Value.UserName);
string password = ed.AESDecrypt(_obixApiConfig.Value.Password);
String encoded = System.Convert.ToBase64String(System.Text.Encoding.GetEncoding("ISO-8859-1").GetBytes(userName + ":" + password));
var sql_watch = $@"SELECT * FROM variable WHERE deleted = 0 AND system_type = @system_type AND system_key = @system_key";
var watch = conn.QueryFirstOrDefault<Variable>(sql_watch, new { system_type = "obixConfig", system_key = "watch_id" });
HttpWebRequest addWatchServiceRequest = (HttpWebRequest)WebRequest.Create($"{_obixApiConfig.Value.ApiBase}obix/watchService/watch{watch.system_value}/add/");
addWatchServiceRequest.Method = "POST";
addWatchServiceRequest.Headers.Add("Authorization", "Basic " + encoded);
addWatchServiceRequest.PreAuthenticate = true;
var subscripFullString = $@"<obj is='obix: WatchIn'>
<list names = 'hrefs'>
{string.Join(null, subscripStrings)}
</list>
</obj>";
byte[] byteArray = Encoding.UTF8.GetBytes(subscripFullString);
using (Stream reqStream = addWatchServiceRequest.GetRequestStream())
{
reqStream.Write(byteArray, 0, byteArray.Length);
}
HttpWebResponse addWatchServiceResponse = (HttpWebResponse)addWatchServiceRequest.GetResponse();
var addWatchServiceResponseContent = new StreamReader(addWatchServiceResponse.GetResponseStream()).ReadToEnd();
XmlDocument xmlDocument = new XmlDocument();
xmlDocument.LoadXml(addWatchServiceResponseContent);
string addWatchServiceJson = JsonConvert.SerializeXmlNode(xmlDocument);
JObject addWatchServiceJsonResult = (JObject)JsonConvert.DeserializeObject(addWatchServiceJson);
if (addWatchServiceJsonResult.ContainsKey("err")) //抓取錯誤
{
throw new NotImplementedException("設備點位訂閱失敗");
//logger.LogError("【OnTimeDeviceRawDataJob】【設備點位訂閱失敗】");
//logger.LogError("【OnTimeDeviceRawDataJob】【設備點位訂閱失敗】[錯誤內容]{0}", addWatchServiceJsonResult);
}
if (addWatchServiceJsonResult.ContainsKey("obj")) //表示可以讀取到內容
{
List<Dictionary<string, object>> ontimeRawDatas = new List<Dictionary<string, object>>();
var ontimeList = addWatchServiceJsonResult["obj"]["list"];
if (ontimeList["real"] != null && ontimeList["real"].HasValues)
{
//讀取數值型
foreach (var real in ontimeList["real"])
{
var valueSplit = real["@display"].ToString().Split();
var value = valueSplit[0];
var href = real["@href"].ToString();
var hrefSplit = href.Split("/");
var findOutIndex = 0;
var outIndex = 0;
foreach (var tempHref in hrefSplit) //找出out的Index
{
if (tempHref == "out")
{
outIndex = findOutIndex;
}
findOutIndex++;
}
var device_number = hrefSplit[outIndex - 2]; //透過outIndex找出設備編號
var name = hrefSplit[outIndex - 1];//透過outIndex找出點位
var selectDevice = tempDeviceSubscrips.Where(x => x.device_number == device_number).FirstOrDefault();
Dictionary<string, object> ontimeRawData = new Dictionary<string, object>()
{
{ "@building_guid", selectDevice.building_guid },
{ "@main_system_guid", selectDevice.main_system_guid },
{ "@sub_system_guid", selectDevice.sub_system_guid },
{ "@device_number", device_number},
{ "@name", name},
{ "@value", value},
{ "@is_bool", 0},
};
ontimeRawDatas.Add(ontimeRawData);
}
}
if (ontimeList["bool"] != null && ontimeList["bool"].HasValues)
{
//讀取布林型
foreach (var real in ontimeList["bool"])
{
var valueSplit = real["@display"].ToString().Split();
var value = valueSplit[0];
var href = real["@href"].ToString();
var hrefSplit = href.Split("/");
var findOutIndex = 0;
var outIndex = 0;
foreach (var tempHref in hrefSplit) //找出out的Index
{
if (tempHref == "out")
{
outIndex = findOutIndex;
}
findOutIndex++;
}
var device_number = hrefSplit[outIndex - 2]; //透過outIndex找出設備編號
var name = hrefSplit[outIndex - 1];//透過outIndex找出點位
var selectDevice = tempDeviceSubscrips.Where(x => x.device_number == device_number).FirstOrDefault();
Dictionary<string, object> ontimeRawData = new Dictionary<string, object>()
{
{ "@building_guid", selectDevice.building_guid },
{ "@main_system_guid", selectDevice.main_system_guid },
{ "@sub_system_guid", selectDevice.sub_system_guid },
{ "@device_number", device_number},
{ "@name", name},
{ "@value", value},
{ "@is_bool", 1},
};
ontimeRawDatas.Add(ontimeRawData);
}
}
if (ontimeRawDatas.Count() > 0)
{
try
{
var insert_update_sql = $@"INSERT INTO ontime_device_rawdata (building_guid, main_system_guid, sub_system_guid, device_number, name, value, is_bool) VALUES(@building_guid, @main_system_guid, @sub_system_guid, @device_number, @name, @value, @is_bool)
ON DUPLICATE KEY UPDATE value=@value, is_bool = @is_bool";
conn.Execute(insert_update_sql, ontimeRawDatas);
}
catch (Exception exception)
{
throw new NotImplementedException("新增設備點位原始資料失敗", exception);
//logger.LogError("【OnTimeDeviceSubscriptionJob】【新增設備點位原始資料失敗】");
//logger.LogError("【OnTimeDeviceSubscriptionJob】【新增設備點位原始資料失敗】[Exception]{0}", exception.ToString());
}
}
//logger.LogInformation("【OnTimeDeviceSubscriptionJob】【任務完成】");
}
#endregion step 3
}
/// <summary>
/// 維護多筆的設備點位訂閱表
/// </summary>
/// <param name="targetTable"></param>
/// <param name="mode"></param>
/// <param name="targetDataList"></param>
public void MaintainMutiOntimeDeviceSubscription(string targetTable, List<Dictionary<string, object>> targetDataList)
{
//只有新增設備時,會有多筆
if (targetTable == "device")
{
List<string> points = new List<string>();
// step 1 找出所有該系統小類的點位
var sql_points = $@"SELECT points FROM device_item WHERE deleted = 0 AND sub_system_guid = @sub_system_guid";
object sub_system_guid = null;
var has_value = targetDataList.First().TryGetValue("@sub_system_guid", out sub_system_guid);
points = conn.Query<string>(sql_points, new { sub_system_guid = sub_system_guid.ToString() }).ToList();
List<Dictionary<string, object>> insertDics = new List<Dictionary<string, object>>();
List<DeviceSubscrip> tempDeviceSubscrips = new List<DeviceSubscrip>();
foreach (var targetData in targetDataList)
{
object device_number = null;
var has_device_number_value = targetData.TryGetValue("@device_number", out device_number);
if (has_device_number_value)
{
object building_guid = null;
targetData.TryGetValue("@building_guid", out building_guid);
object main_system_guid = null;
targetData.TryGetValue("@main_system_guid", out main_system_guid);
object local_sub_system_guid = null;
targetData.TryGetValue("@sub_system_guid", out local_sub_system_guid);
foreach (var point in points)
{
Dictionary<string, object> insertDic = new Dictionary<string, object>()
{
{ "@building_guid", building_guid },
{ "@main_system_guid", main_system_guid },
{ "@sub_system_guid", local_sub_system_guid },
{ "@device_number", device_number },
{ "@name", point}
};
insertDics.Add(insertDic);
DeviceSubscrip deviceSubscrip = new DeviceSubscrip()
{
building_guid = building_guid.ToString(),
main_system_guid = main_system_guid.ToString(),
sub_system_guid = local_sub_system_guid.ToString(),
device_number = device_number.ToString(),
};
tempDeviceSubscrips.Add(deviceSubscrip);
}
}
}
List<string> properties = insertDics.First().Keys.ToList();
string sql = InsertGenerateString(properties, "ontime_device_subscription");
conn.Execute(sql, insertDics, trans);
#region step 2
var subscripUriFormat = @" <uri val='/obix/config/Arena/{0}/{1}/{2}/out/' />";
List<string> subscripStrings = new List<string>();
foreach (var deviceSubscrip in insertDics)
{
List<string> devicePaths = new List<string>();
object device_number = null;
var has_device_number = deviceSubscrip.TryGetValue("@device_number", out device_number);
//拆解設備編號來組出路徑
var deviceSubscripSplit = device_number.ToString().Split("_");
foreach (var temp in deviceSubscripSplit)
{
if (temp[0].ToString().All(char.IsDigit))
{
devicePaths.Add($"$3{temp}");
}
else
{
devicePaths.Add(temp);
}
}
//剔除最後一個,因為是設備名稱
devicePaths.RemoveAt(devicePaths.Count() - 1);
object point_name = null;
var has_point_name = deviceSubscrip.TryGetValue("@name", out point_name);
subscripStrings.Add(string.Format(subscripUriFormat, string.Join('/', devicePaths), device_number.ToString(), point_name.ToString()));
}
#endregion step 2
#region step 3
EDFunction ed = new EDFunction();
string userName = ed.AESDecrypt(_obixApiConfig.Value.UserName);
string password = ed.AESDecrypt(_obixApiConfig.Value.Password);
String encoded = System.Convert.ToBase64String(System.Text.Encoding.GetEncoding("ISO-8859-1").GetBytes(userName + ":" + password));
var sql_watch = $@"SELECT * FROM variable WHERE deleted = 0 AND system_type = @system_type AND system_key = @system_key";
var watch = conn.QueryFirstOrDefault<Variable>(sql_watch, new { system_type = "obixConfig", system_key = "watch_id" });
HttpWebRequest addWatchServiceRequest = (HttpWebRequest)WebRequest.Create($"{_obixApiConfig.Value.ApiBase}obix/watchService/watch{watch.system_value}/add/");
addWatchServiceRequest.Method = "POST";
addWatchServiceRequest.Headers.Add("Authorization", "Basic " + encoded);
addWatchServiceRequest.PreAuthenticate = true;
var subscripFullString = $@"<obj is='obix: WatchIn'>
<list names = 'hrefs'>
{string.Join(null, subscripStrings)}
</list>
</obj>";
byte[] byteArray = Encoding.UTF8.GetBytes(subscripFullString);
using (Stream reqStream = addWatchServiceRequest.GetRequestStream())
{
reqStream.Write(byteArray, 0, byteArray.Length);
}
HttpWebResponse addWatchServiceResponse = (HttpWebResponse)addWatchServiceRequest.GetResponse();
var addWatchServiceResponseContent = new StreamReader(addWatchServiceResponse.GetResponseStream()).ReadToEnd();
XmlDocument xmlDocument = new XmlDocument();
xmlDocument.LoadXml(addWatchServiceResponseContent);
string addWatchServiceJson = JsonConvert.SerializeXmlNode(xmlDocument);
JObject addWatchServiceJsonResult = (JObject)JsonConvert.DeserializeObject(addWatchServiceJson);
if (addWatchServiceJsonResult.ContainsKey("err")) //抓取錯誤
{
throw new NotImplementedException("設備點位訂閱失敗");
//logger.LogError("【OnTimeDeviceRawDataJob】【設備點位訂閱失敗】");
//logger.LogError("【OnTimeDeviceRawDataJob】【設備點位訂閱失敗】[錯誤內容]{0}", addWatchServiceJsonResult);
}
if (addWatchServiceJsonResult.ContainsKey("obj")) //表示可以讀取到內容
{
List<Dictionary<string, object>> ontimeRawDatas = new List<Dictionary<string, object>>();
var ontimeList = addWatchServiceJsonResult["obj"]["list"];
if (ontimeList["real"] != null && ontimeList["real"].HasValues)
{
//讀取數值型
foreach (var real in ontimeList["real"])
{
var valueSplit = real["@display"].ToString().Split();
var value = valueSplit[0];
var href = real["@href"].ToString();
var hrefSplit = href.Split("/");
var findOutIndex = 0;
var outIndex = 0;
foreach (var tempHref in hrefSplit) //找出out的Index
{
if (tempHref == "out")
{
outIndex = findOutIndex;
}
findOutIndex++;
}
var device_number = hrefSplit[outIndex - 2]; //透過outIndex找出設備編號
var name = hrefSplit[outIndex - 1];//透過outIndex找出點位
var selectDevice = tempDeviceSubscrips.Where(x => x.device_number == device_number).FirstOrDefault();
Dictionary<string, object> ontimeRawData = new Dictionary<string, object>()
{
{ "@building_guid", selectDevice.building_guid },
{ "@main_system_guid", selectDevice.main_system_guid },
{ "@sub_system_guid", selectDevice.sub_system_guid },
{ "@device_number", device_number},
{ "@name", name},
{ "@value", value},
{ "@is_bool", 0},
};
ontimeRawDatas.Add(ontimeRawData);
}
}
if (ontimeList["bool"] != null && ontimeList["bool"].HasValues)
{
//讀取布林型
foreach (var real in ontimeList["bool"])
{
var valueSplit = real["@display"].ToString().Split();
var value = valueSplit[0];
var href = real["@href"].ToString();
var hrefSplit = href.Split("/");
var findOutIndex = 0;
var outIndex = 0;
foreach (var tempHref in hrefSplit) //找出out的Index
{
if (tempHref == "out")
{
outIndex = findOutIndex;
}
findOutIndex++;
}
var device_number = hrefSplit[outIndex - 2]; //透過outIndex找出設備編號
var name = hrefSplit[outIndex - 1];//透過outIndex找出點位
var selectDevice = tempDeviceSubscrips.Where(x => x.device_number == device_number).FirstOrDefault();
Dictionary<string, object> ontimeRawData = new Dictionary<string, object>()
{
{ "@building_guid", selectDevice.building_guid },
{ "@main_system_guid", selectDevice.main_system_guid },
{ "@sub_system_guid", selectDevice.sub_system_guid },
{ "@device_number", device_number},
{ "@name", name},
{ "@value", value},
{ "@is_bool", 1},
};
ontimeRawDatas.Add(ontimeRawData);
}
}
if (ontimeRawDatas.Count() > 0)
{
try
{
var insert_update_sql = $@"INSERT INTO ontime_device_rawdata (building_guid, main_system_guid, sub_system_guid, device_number, name, value, is_bool) VALUES(@building_guid, @main_system_guid, @sub_system_guid, @device_number, @name, @value, @is_bool)
ON DUPLICATE KEY UPDATE value=@value, is_bool = @is_bool";
conn.Execute(insert_update_sql, ontimeRawDatas);
}
catch (Exception exception)
{
throw new NotImplementedException("新增設備點位原始資料失敗", exception);
//logger.LogError("【OnTimeDeviceSubscriptionJob】【新增設備點位原始資料失敗】");
//logger.LogError("【OnTimeDeviceSubscriptionJob】【新增設備點位原始資料失敗】[Exception]{0}", exception.ToString());
}
}
//logger.LogInformation("【OnTimeDeviceSubscriptionJob】【任務完成】");
}
#endregion step 3
}
}
}
}