2777 lines
156 KiB
C#
2777 lines
156 KiB
C#
using FrontendWebApi.Models;
|
||
using Microsoft.AspNetCore.Localization;
|
||
using Microsoft.AspNetCore.Mvc;
|
||
using Microsoft.AspNetCore.Routing;
|
||
using Microsoft.Extensions.Logging;
|
||
using Microsoft.Extensions.Options;
|
||
using Newtonsoft.Json;
|
||
using Newtonsoft.Json.Linq;
|
||
using NPOI.SS.UserModel;
|
||
using NPOI.XSSF.UserModel;
|
||
using Repository.BackendRepository.Interface;
|
||
using Repository.FrontendRepository.Interface;
|
||
using System;
|
||
using System.Collections.Generic;
|
||
using System.IO;
|
||
using System.Linq;
|
||
using System.Net;
|
||
using System.Text;
|
||
using System.Threading.Tasks;
|
||
using System.Xml;
|
||
using System.Diagnostics;
|
||
using Microsoft.CodeAnalysis;
|
||
|
||
|
||
namespace FrontendWebApi.ApiControllers
|
||
{
|
||
public class HistoryController : MyBaseApiController<EmergencyRecordController>
|
||
{
|
||
private readonly IBackendRepository backendRepository;
|
||
private readonly IFrontendRepository frontendRepository;
|
||
private readonly IBackgroundServiceMsSqlRepository backgroundServiceMsSqlRepository;
|
||
|
||
public HistoryController
|
||
(
|
||
IBackendRepository backendRepository,
|
||
IFrontendRepository frontendRepository,
|
||
IBackgroundServiceMsSqlRepository backgroundServiceMsSqlRepository
|
||
)
|
||
{
|
||
this.backendRepository = backendRepository;
|
||
this.frontendRepository = frontendRepository;
|
||
this.backgroundServiceMsSqlRepository = backgroundServiceMsSqlRepository;
|
||
}
|
||
|
||
/// <summary>
|
||
/// 匯出excel
|
||
/// </summary>
|
||
/// <param name="lhe"></param>
|
||
/// <returns></returns>
|
||
[HttpPost]
|
||
[Route("api/ExportHistory")]
|
||
public FileResult OpeExportExcel([FromBody] HistoryExport lhe)
|
||
{
|
||
ApiResult<string> apiResult = new ApiResult<string>();
|
||
|
||
//if (lhe == null)
|
||
//{
|
||
// apiResult.Code = "0001";
|
||
// apiResult.Msg = "沒有資料匯入";
|
||
// return apiResult;
|
||
//}
|
||
string fileDateName = lhe.dateType == "today" ? lhe.starttime.ToString("yyyy-MM-dd")
|
||
: lhe.dateType == "month" ? lhe.starttime.ToString("yyyy-MM")
|
||
: lhe.starttime.ToString("yyyy-MM-dd") + "_" + ((DateTime)lhe.endtime).ToString("yyyy-MM-dd");
|
||
string fileName = "歷史資料_" + fileDateName + ".xlsx";
|
||
lhe.device_number = char.IsDigit(lhe.device_number.Split("_")[1][0])
|
||
? lhe.device_number.Replace(lhe.device_number.Split("_")[1], "$3" + lhe.device_number.Split("_")[1])
|
||
: lhe.device_number;
|
||
XSSFWorkbook workbook = new XSSFWorkbook();
|
||
try
|
||
{
|
||
#region 取得obix 設定
|
||
EDFunction ed = new EDFunction();
|
||
var obixApiConfig = new ObixApiConfig();
|
||
var sqlObix = $@"SELECT system_value as Value, system_key as Name FROM variable WHERE deleted = 0 AND system_type = 'obixConfig'";
|
||
var variableObix = backendRepository.GetAllAsync<KeyValue>(sqlObix).Result;
|
||
obixApiConfig.ApiBase = variableObix.Where(x => x.Name == "ApiBase").Select(x => x.Value).FirstOrDefault();
|
||
obixApiConfig.UserName = ed.AESDecrypt(variableObix.Where(x => x.Name == "UserName").Select(x => x.Value).FirstOrDefault());
|
||
obixApiConfig.Password = ed.AESDecrypt(variableObix.Where(x => x.Name == "Password").Select(x => x.Value).FirstOrDefault());
|
||
String encoded = System.Convert.ToBase64String(System.Text.Encoding.GetEncoding("ISO-8859-1").GetBytes(obixApiConfig.UserName + ":" + obixApiConfig.Password));
|
||
#endregion 取得obix 設定
|
||
var building = backendRepository.GetAllAsync<BuildList>("select * from building where deleted = 0").Result;
|
||
var buildStation = backendRepository.GetAllAsync<BuildStation>("select SUBSTRING_INDEX(system_value, '/', 1) system_value, system_key from variable where system_type = 'dashboard_total_elec' and deleted = 0 and (SUBSTRING_INDEX(system_value, '/', 1) != '' and SUBSTRING_INDEX(system_value, '/', 1) is not null)").Result;
|
||
|
||
#region get device and device_item(point)
|
||
var device = backendRepository.GetAllAsync<Device>($"select * from device where deleted = 0 and is_link = 1 and device_number = '{lhe.device_number}'").Result;
|
||
var devicePoint = backendRepository.GetAllAsync<Device_item>($"select * from device_item where deleted = 0 and is_link = 1 and is_show_history = 1 and device_building_tag = '{lhe.device_number.Split("_")[1]}' and device_system_tag = '{lhe.device_number.Split("_")[2]}' and device_name_tag = '{lhe.device_number.Split("_")[3]}'").Result;
|
||
#endregion
|
||
List<DeviceNumberPoint> listDevicePoint = new List<DeviceNumberPoint>();
|
||
|
||
#region combine device and point
|
||
var dp = devicePoint.Where(x => x.device_building_tag == lhe.device_number.Split("_")[1]).ToList();
|
||
foreach (var point in dp)
|
||
{
|
||
DeviceNumberPoint deviceNumberPoint = new DeviceNumberPoint();
|
||
deviceNumberPoint.DeviceNumber = lhe.device_number;
|
||
deviceNumberPoint.Point = point.points;
|
||
deviceNumberPoint.FullDeviceNumberPoint = string.Format("{0}_{1}", lhe.device_number, point.points);
|
||
|
||
listDevicePoint.Add(deviceNumberPoint);
|
||
}
|
||
#endregion
|
||
|
||
#region get data from niagara
|
||
lhe.endtime = lhe.dateType == "today" ? lhe.starttime : lhe.dateType == "month" ? lhe.starttime.AddMonths(1).AddDays(-1) : lhe.endtime;
|
||
var startTimestamp = string.Format("{0}T00:00:00.000+08:00", lhe.starttime.ToString("yyyy-MM-dd"));
|
||
var endTimestamp = string.Format("{0}T23:59:59.000+08:00", lhe.endtime?.ToString("yyyy-MM-dd"));
|
||
var historyQueryFilter = $@"<obj is='obix: HistoryFilter'>
|
||
<abstime name='start' val='{startTimestamp}' />
|
||
<abstime name='end' val='{endTimestamp}' />
|
||
<reltime name='interval' val='PT1M' />
|
||
</obj>";
|
||
|
||
List<HistoryExport> he = new List<HistoryExport>();
|
||
List<JsonDevice> jd = new List<JsonDevice>();
|
||
if (listDevicePoint.Count > 0)
|
||
{
|
||
foreach (var d in listDevicePoint)
|
||
{
|
||
string buildingTag = d.DeviceNumber.Split("_")[1];
|
||
var station = backendRepository.GetOneAsync<string>($@"select parent_path from import_niagara_item_history where device_building_tag = '{d.FullDeviceNumberPoint.Split("_")[1].Replace("$3", "")}' and
|
||
device_system_tag = '{d.FullDeviceNumberPoint.Split("_")[2]}' and device_name_tag = '{d.FullDeviceNumberPoint.Split("_")[3]}'
|
||
and device_floor_tag = '{d.FullDeviceNumberPoint.Split("_")[4]}' and device_master_tag = '{d.FullDeviceNumberPoint.Split("_")[5]}'
|
||
and device_last_name_tag = '{d.FullDeviceNumberPoint.Split("_")[6]}' and device_serial_tag = '{d.FullDeviceNumberPoint.Split("_")[7]}'
|
||
and device_point_name = '{d.FullDeviceNumberPoint.Split("_")[8]}'").Result;
|
||
|
||
if (!string.IsNullOrEmpty(station))
|
||
{
|
||
var archiveRequest = (HttpWebRequest)WebRequest.Create($"{obixApiConfig.ApiBase}obix/histories/{station}/{d.FullDeviceNumberPoint.Replace("$3", "")}/~historyRollup/");
|
||
archiveRequest.Method = "POST";
|
||
archiveRequest.Headers.Add("Authorization", "Basic " + encoded);
|
||
archiveRequest.PreAuthenticate = true;
|
||
|
||
byte[] byteArray = Encoding.UTF8.GetBytes(historyQueryFilter);
|
||
using (Stream reqStream = archiveRequest.GetRequestStream())
|
||
{
|
||
reqStream.Write(byteArray, 0, byteArray.Length);
|
||
}
|
||
|
||
var archiveResponse = (HttpWebResponse)archiveRequest.GetResponse();
|
||
var archiveResponseContent = new StreamReader(archiveResponse.GetResponseStream()).ReadToEnd();
|
||
archiveResponse.Dispose();
|
||
archiveResponse.Close();
|
||
|
||
XmlDocument xmlDocument = new XmlDocument();
|
||
xmlDocument.LoadXml(archiveResponseContent);
|
||
var archiveJson = JsonConvert.SerializeXmlNode(xmlDocument);
|
||
var archiveJsonResult = (JObject)JsonConvert.DeserializeObject(archiveJson);
|
||
|
||
if (!archiveJsonResult.ContainsKey("err"))
|
||
{
|
||
var jsonDevice = new JsonDevice();
|
||
jsonDevice.deviceNumberPoint = d;
|
||
jsonDevice.json = archiveJsonResult;
|
||
jsonDevice.building_tag = buildingTag;
|
||
jd.Add(jsonDevice);
|
||
}
|
||
}
|
||
else
|
||
{
|
||
Logger.LogError("【Device GetWay Error】, device_number: " + d.FullDeviceNumberPoint);
|
||
}
|
||
}
|
||
}
|
||
|
||
if (jd != null && jd.Count > 0)
|
||
{
|
||
foreach (var d in jd)
|
||
{
|
||
var ArrangeRawDatas = ArrangeRawData(d.deviceNumberPoint, d.json);
|
||
if (ArrangeRawDatas != null && ArrangeRawDatas.Count > 0)
|
||
{
|
||
foreach (var ard in ArrangeRawDatas)
|
||
{
|
||
HistoryExport hed = new HistoryExport();
|
||
hed.type = devicePoint.Where(x => x.device_building_tag == d.building_tag && x.points == ard["@point"].ToString()).Select(x => x.full_name).FirstOrDefault();
|
||
hed.deviceName = device.Where(x => x.device_number == ard["@device_number"].ToString()).Select(x => x.full_name).FirstOrDefault();
|
||
// 四捨六入
|
||
//hed.value = ard["@avg_rawdata"].ToString() == "-1" ? "NaN" : Math.Round((decimal.Parse(ard["@avg_rawdata"].ToString(), System.Globalization.NumberStyles.Float)), 2).ToString();
|
||
// 四捨五入
|
||
//ref: Math.Round(Convert.ToDecimal(45.365), 2, MidpointRounding.AwayFromZero)
|
||
hed.value = ard["@avg_rawdata"].ToString() == "-1" ? "NaN" : Math.Round(Convert.ToDecimal(ard["@avg_rawdata"].ToString()), 2, MidpointRounding.AwayFromZero).ToString();
|
||
hed.timestamp = Convert.ToDateTime(ard["@start_timestamp"].ToString());
|
||
hed.building_tag = d.building_tag;
|
||
he.Add(hed);
|
||
}
|
||
}
|
||
}
|
||
}
|
||
#endregion
|
||
|
||
#region export file progress
|
||
#region excel設定
|
||
IFont font12 = workbook.CreateFont();
|
||
font12.FontName = "新細明體";
|
||
font12.FontHeightInPoints = 12;
|
||
ICellStyle style12 = workbook.CreateCellStyle();
|
||
style12.SetFont(font12);
|
||
style12.Alignment = HorizontalAlignment.Center;
|
||
style12.VerticalAlignment = VerticalAlignment.Center;
|
||
IFont font12Times = workbook.CreateFont();
|
||
font12Times.FontName = "Times New Roman";
|
||
font12Times.FontHeightInPoints = 12;
|
||
IFont font18 = workbook.CreateFont();
|
||
font18.FontName = "新細明體";
|
||
font18.FontHeightInPoints = 18;
|
||
font18.IsBold = true;
|
||
ICellStyle styleTitle18 = workbook.CreateCellStyle();
|
||
styleTitle18.SetFont(font18);
|
||
styleTitle18.Alignment = HorizontalAlignment.Center;
|
||
styleTitle18.VerticalAlignment = VerticalAlignment.Center;
|
||
ICellStyle styleLeft12 = workbook.CreateCellStyle();
|
||
styleLeft12.SetFont(font12);
|
||
styleLeft12.Alignment = HorizontalAlignment.Left;
|
||
styleLeft12.VerticalAlignment = VerticalAlignment.Center;
|
||
ICellStyle styleLine12 = workbook.CreateCellStyle();
|
||
styleLine12.SetFont(font12);
|
||
styleLine12.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;
|
||
styleLine12.VerticalAlignment = VerticalAlignment.Center;
|
||
styleLine12.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin;
|
||
styleLine12.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;
|
||
styleLine12.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin;
|
||
styleLine12.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin;
|
||
ICellStyle stylein12 = workbook.CreateCellStyle();
|
||
stylein12.SetFont(font12Times);
|
||
stylein12.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Left;
|
||
stylein12.VerticalAlignment = VerticalAlignment.Center;
|
||
stylein12.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin;
|
||
stylein12.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;
|
||
stylein12.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin;
|
||
stylein12.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin;
|
||
stylein12.WrapText = true;
|
||
#endregion
|
||
var data = he.ToList();
|
||
if (data.Count > 0)
|
||
{
|
||
string lastDeviceItem = string.Empty;
|
||
int RowPosition = 0;
|
||
IRow row;
|
||
ISheet sheet;
|
||
#region set cell
|
||
sheet = workbook.CreateSheet($"{building.Where(x => x.building_tag == lhe.device_number.Split("_")[1]).Select(x => x.full_name).FirstOrDefault()}{"_" + data[0].type}");
|
||
row = sheet.CreateRow(RowPosition);
|
||
sheet.SetColumnWidth(0, 4 * 160 * 12);
|
||
sheet.SetColumnWidth(1, 4 * 160 * 12);
|
||
sheet.SetColumnWidth(2, 4 * 160 * 12);
|
||
sheet.SetColumnWidth(3, 4 * 160 * 12);
|
||
ICell cell = row.CreateCell(0);
|
||
cell.SetCellValue("類型");
|
||
cell.CellStyle = styleLine12;
|
||
cell = row.CreateCell(1);
|
||
cell.SetCellValue("設備名稱");
|
||
cell.CellStyle = styleLine12;
|
||
cell = row.CreateCell(2);
|
||
cell.SetCellValue("數值");
|
||
cell = row.CreateCell(3);
|
||
cell.SetCellValue("記錄時間");
|
||
cell.CellStyle = styleLine12;
|
||
#endregion
|
||
|
||
foreach (var d in data)
|
||
{
|
||
if (RowPosition == 0 && lastDeviceItem == "")
|
||
{
|
||
lastDeviceItem = d.type; //第一次不用建立 sheet;
|
||
}
|
||
if (d.type != lastDeviceItem)
|
||
{
|
||
RowPosition = 0;
|
||
lastDeviceItem = d.type;
|
||
sheet = workbook.CreateSheet($"{building.Where(x => x.building_tag == lhe.device_number.Split("_")[1]).Select(x => x.full_name).FirstOrDefault()}{"_" + d.type}");
|
||
//sheet = workbook.CreateSheet($"{d.deviceName}{"_" + d.type}");
|
||
#region set cell
|
||
row = sheet.CreateRow(RowPosition);
|
||
sheet.SetColumnWidth(0, 4 * 160 * 12);
|
||
sheet.SetColumnWidth(1, 4 * 160 * 12);
|
||
sheet.SetColumnWidth(2, 4 * 160 * 12);
|
||
sheet.SetColumnWidth(3, 4 * 160 * 12);
|
||
cell = row.CreateCell(0);
|
||
cell.SetCellValue("類型");
|
||
cell.CellStyle = styleLine12;
|
||
cell = row.CreateCell(1);
|
||
cell.SetCellValue("設備名稱");
|
||
cell.CellStyle = styleLine12;
|
||
cell = row.CreateCell(2);
|
||
cell.SetCellValue("數值");
|
||
cell = row.CreateCell(3);
|
||
cell.SetCellValue("記錄時間");
|
||
cell.CellStyle = styleLine12;
|
||
#endregion
|
||
RowPosition = 1;
|
||
}
|
||
else
|
||
{
|
||
RowPosition += 1;
|
||
}
|
||
|
||
row = sheet.CreateRow(RowPosition);
|
||
for (var i = 0; i < 4; i++)
|
||
{
|
||
cell = row.CreateCell(i);
|
||
if (i == 0)
|
||
{
|
||
cell.SetCellValue(d.type);
|
||
}
|
||
if (i == 1)
|
||
{
|
||
cell.SetCellValue(d.deviceName);
|
||
}
|
||
if (i == 2)
|
||
{
|
||
cell.SetCellValue(d.value);
|
||
}
|
||
if (i == 3)
|
||
{
|
||
cell.SetCellValue(d.timestamp.ToString("yyyy-MM-dd HH:mm") + ":00");//
|
||
}
|
||
|
||
cell.CellStyle = style12;
|
||
}
|
||
}
|
||
}
|
||
#endregion
|
||
}
|
||
catch (Exception exception)
|
||
{
|
||
apiResult.Code = "9999";
|
||
apiResult.Msg = "系統內部錯誤，請聯絡管理者。 Msg: " + exception.Message;
|
||
Logger.LogError("【" + controllerName + "/" + actionName + "】" + exception.Message);
|
||
throw exception;
|
||
}
|
||
|
||
var ms = new NpoiMemoryStream
|
||
{
|
||
AllowClose = false
|
||
};
|
||
workbook.Write(ms);
|
||
ms.Flush();
|
||
ms.Seek(0, SeekOrigin.Begin);
|
||
Response.Headers.Add("Access-Control-Expose-Headers", "Content-Disposition");
|
||
return File(ms, "application/vnd.ms-excel", fileName);
|
||
}
|
||
|
||
/// <summary>
|
||
/// 匯出excel
|
||
/// </summary>
|
||
/// <param name="post"></param>
|
||
/// <returns></returns>
|
||
[HttpPost]
|
||
[Route("api/ExportHistoryExcel")]
|
||
public IActionResult ExportHistoryExcel([FromBody] PostHistoryDataFilter post)
|
||
{
|
||
ApiResult<string> apiResult = new ApiResult<string>();
|
||
var fileDateName = post.Type == 1 ? $@"{post.Start_date}T{post.Start_time}~{post.End_date}T{post.End_time}" : $@"{post.Start_date}~{post.End_date}&T{post.Start_time}~T{post.End_time}";
|
||
var fileName = "歷史資料_" + fileDateName + ".xlsx";
|
||
|
||
XSSFWorkbook workbook = new XSSFWorkbook();
|
||
try
|
||
{
|
||
#region 取得obix 設定
|
||
EDFunction ed = new EDFunction();
|
||
var obixApiConfig = new ObixApiConfig();
|
||
var sqlObix = $@"SELECT system_value as Value, system_key as Name FROM variable WHERE deleted = 0 AND system_type = 'obixConfig'";
|
||
var variableObix = backendRepository.GetAllAsync<KeyValue>(sqlObix).Result;
|
||
obixApiConfig.ApiBase = variableObix.Where(x => x.Name == "ApiBase").Select(x => x.Value).FirstOrDefault();
|
||
obixApiConfig.UserName = ed.AESDecrypt(variableObix.Where(x => x.Name == "UserName").Select(x => x.Value).FirstOrDefault());
|
||
obixApiConfig.Password = ed.AESDecrypt(variableObix.Where(x => x.Name == "Password").Select(x => x.Value).FirstOrDefault());
|
||
String encoded = System.Convert.ToBase64String(System.Text.Encoding.GetEncoding("ISO-8859-1").GetBytes(obixApiConfig.UserName + ":" + obixApiConfig.Password));
|
||
#endregion 取得obix 設定
|
||
#region get device and device_item(point)
|
||
var device = backendRepository.GetAllAsync<Device>($"select * from device where deleted = 0 and is_link = 1 and device_number in @Device_list", new { Device_list = post.Device_list }).Result;
|
||
var devicePoint = backendRepository.GetAllAsync<Device_item>($"select * from device_item where deleted = 0 and is_link = 1 and is_show_history = 1 and device_building_tag in @building_tag", new { building_tag = post.Building_tag_list }).Result;
|
||
#endregion
|
||
var building = backendRepository.GetAllAsync<BuildList>("select * from building where deleted = 0").Result;
|
||
List<DeviceNumberPoint> listDevicePoint = new List<DeviceNumberPoint>();
|
||
List<HistoryExport> he = new List<HistoryExport>();
|
||
List<JsonDevice> jd = new List<JsonDevice>();
|
||
#region combine device and point
|
||
// 因傳進來point不是比照前端順序，故這邊又排序了一次
|
||
var Sql = $@"select c.full_name building_name,b.device_number,b.full_name device_name,a.full_name item_name,a.points,a.unit
|
||
from device_item a
|
||
join device b on a.device_system_tag = b.device_system_tag
|
||
and a.device_building_tag COLLATE utf8mb4_unicode_ci = b.device_building_tag
|
||
and a.device_name_tag COLLATE utf8mb4_unicode_ci = b.device_name_tag
|
||
join building c on c.building_tag = b.device_building_tag
|
||
join variable v1 on v1.system_value = b.device_system_tag and v1.deleted = 0 and v1.system_type = 'device_system_category_layer2'
|
||
join variable v2 on v2.system_value = b.device_name_tag and v2.deleted = 0 and v2.system_type = 'device_system_category_layer3'
|
||
where a.deleted = 0 and b.deleted = 0 and b.is_link = 1 and v2.deleted = 0 and v1.deleted = 0 and is_show_history = 1 and a.is_link = 1
|
||
and b.device_number = 'NTPC_G6_EE_E4_B1F_EMP_WHT_N1'
|
||
GROUP BY points
|
||
order by c.priority,b.priority,a.is_bool;";
|
||
var pointData = backendRepository.GetAllAsync<History_PostItem>(Sql).Result;
|
||
List<string> points = pointData.Select(x => x.points).ToList();
|
||
List<string> inputParams = post.Points;
|
||
inputParams.Sort((a, b) => points.IndexOf(a).CompareTo(points.IndexOf(b)));
|
||
foreach (var Device in post.Device_list)
|
||
{
|
||
foreach (var point in inputParams)
|
||
{
|
||
DeviceNumberPoint deviceNumberPoint = new DeviceNumberPoint();
|
||
deviceNumberPoint.DeviceNumber = Device;
|
||
deviceNumberPoint.Point = point;
|
||
deviceNumberPoint.FullDeviceNumberPoint = string.Format("{0}_{1}", Device, point);
|
||
|
||
listDevicePoint.Add(deviceNumberPoint);
|
||
}
|
||
}
|
||
#endregion
|
||
|
||
#region get data from niagara
|
||
if (post.Type == 1)
|
||
{
|
||
#region Niagara抓資料依據日期
|
||
|
||
//轉換日期格式
|
||
var start = string.Format("{0}T{1}:00.000+08:00", post.Start_date, post.Start_time);
|
||
var end = string.Format("{0}T{1}:10.000+08:00", post.End_date, post.End_time);
|
||
|
||
var historyQueryFilter = $@"<obj is='obix: HistoryFilter'>
|
||
<abstime name = 'start' val = '{start}' />
|
||
<abstime name = 'end' val = '{end}' />
|
||
</obj>";
|
||
|
||
XmlDocument xmlDocument = new XmlDocument();
|
||
List<string> errorPoints = new List<string>(); //錯誤的設備點位
|
||
if (listDevicePoint.Count > 0)
|
||
{
|
||
foreach (var d in listDevicePoint)
|
||
{
|
||
var station = backendRepository.GetOneAsync<string>($@"select parent_path from import_niagara_item_history where device_building_tag = '{d.FullDeviceNumberPoint.Split("_")[1].Replace("$3", "")}' and
|
||
device_system_tag = '{d.FullDeviceNumberPoint.Split("_")[2]}' and device_name_tag = '{d.FullDeviceNumberPoint.Split("_")[3]}'
|
||
and device_floor_tag = '{d.FullDeviceNumberPoint.Split("_")[4]}' and device_master_tag = '{d.FullDeviceNumberPoint.Split("_")[5]}'
|
||
and device_last_name_tag = '{d.FullDeviceNumberPoint.Split("_")[6]}' and device_serial_tag = '{d.FullDeviceNumberPoint.Split("_")[7]}'
|
||
and device_point_name = '{d.FullDeviceNumberPoint.Split("_")[8]}'").Result;
|
||
|
||
if (!string.IsNullOrEmpty(station))
|
||
{
|
||
|
||
HttpWebRequest request = (HttpWebRequest)WebRequest.Create($"{obixApiConfig.ApiBase}obix/histories/{station}/{d.FullDeviceNumberPoint}/~historyQuery/");
|
||
request.Method = "POST";
|
||
request.Headers.Add("Authorization", "Basic " + encoded);
|
||
request.PreAuthenticate = true;
|
||
|
||
byte[] byteArray = Encoding.UTF8.GetBytes(historyQueryFilter);
|
||
using (Stream reqStream = request.GetRequestStream())
|
||
{
|
||
reqStream.Write(byteArray, 0, byteArray.Length);
|
||
}
|
||
|
||
HttpWebResponse response = (HttpWebResponse)request.GetResponse();
|
||
var responseContent = new StreamReader(response.GetResponseStream()).ReadToEnd();
|
||
|
||
xmlDocument.LoadXml(responseContent);
|
||
string responseContentJson = JsonConvert.SerializeXmlNode(xmlDocument);
|
||
JObject responseContentJsonResult = (JObject)JsonConvert.DeserializeObject(responseContentJson);
|
||
|
||
if (responseContentJsonResult.ContainsKey("err")) //抓取錯誤
|
||
{
|
||
errorPoints.Add(d.DeviceNumber);
|
||
continue;
|
||
}
|
||
|
||
|
||
if (responseContentJsonResult.ContainsKey("obj")) //表示可以讀取到內容
|
||
{
|
||
var countObj = responseContentJsonResult["obj"]["int"];
|
||
if (countObj != null && countObj["@name"].ToString() == "count")
|
||
{ //判斷是否有資料筆數
|
||
if (Convert.ToInt32(countObj["@val"].ToString()) > 0)
|
||
{
|
||
var dataList = responseContentJsonResult["obj"]["list"];
|
||
if (dataList != null && dataList["obj"].HasValues)
|
||
{
|
||
//判斷單筆資料還是多筆
|
||
if (Convert.ToInt32(countObj["@val"].ToString()) == 1)
|
||
{
|
||
|
||
HistoryRawData historyRawData = new HistoryRawData();
|
||
//取得設備基本資訊
|
||
var device_number = d.DeviceNumber;
|
||
var point = d.Point;
|
||
string buildingTag = d.DeviceNumber.Split("_")[1];
|
||
//取得時間
|
||
var abstime = dataList["obj"]["abstime"]["@val"].ToString();
|
||
var val = dataList["obj"]["real"] != null ?
|
||
dataList["obj"]["real"]["@val"].ToString() : dataList["obj"]["bool"]["@val"].ToString();
|
||
|
||
HistoryExport hed = new HistoryExport();
|
||
hed.device_number = device_number;
|
||
hed.type = devicePoint.Where(x => x.device_building_tag == buildingTag && x.points == point).Select(x => x.full_name).FirstOrDefault();
|
||
hed.deviceName = device.Where(x => x.device_number == device_number).Select(x => x.full_name).FirstOrDefault();
|
||
hed.value = val;
|
||
hed.timestamp = Convert.ToDateTime(abstime);
|
||
hed.building_tag = buildingTag;
|
||
he.Add(hed);
|
||
|
||
}
|
||
else
|
||
{
|
||
//讀取資料
|
||
foreach (var obj in dataList["obj"])
|
||
{
|
||
|
||
HistoryRawData historyRawData = new HistoryRawData();
|
||
//取得設備基本資訊
|
||
var device_number = d.DeviceNumber;
|
||
var point = d.Point;
|
||
string buildingTag = d.DeviceNumber.Split("_")[1];
|
||
//取得時間
|
||
var abstime = obj["abstime"]["@val"].ToString();
|
||
var val = obj["real"] != null ?
|
||
obj["real"]["@val"].ToString() : obj["bool"]["@val"].ToString();
|
||
|
||
HistoryExport hed = new HistoryExport();
|
||
hed.device_number = device_number;
|
||
hed.type = devicePoint.Where(x => x.device_building_tag == buildingTag && x.points == point).Select(x => x.full_name).FirstOrDefault();
|
||
hed.deviceName = device.Where(x => x.device_number == device_number).Select(x => x.full_name).FirstOrDefault();
|
||
hed.value = val;
|
||
hed.timestamp = Convert.ToDateTime(abstime);
|
||
hed.building_tag = buildingTag;
|
||
he.Add(hed);
|
||
}
|
||
}
|
||
}
|
||
}
|
||
}
|
||
}
|
||
}
|
||
else
|
||
{
|
||
Logger.LogError("【Device GetWay Error】, device_number: " + d.FullDeviceNumberPoint);
|
||
}
|
||
}
|
||
}
|
||
|
||
if (errorPoints.Count() > 0)
|
||
{
|
||
Logger.LogError("【Device GetWay Error】, device_number: " + errorPoints);
|
||
}
|
||
|
||
#endregion
|
||
}
|
||
if (post.Type == 2)
|
||
{
|
||
#region Niagara抓資料依據時間段
|
||
DateTime startday = DateTime.Parse(post.Start_date);
|
||
DateTime endtday = DateTime.Parse(post.End_date);
|
||
List<DateTime> dateRange = Enumerable.Range(0, (endtday - startday).Days + 1)
|
||
.Select(a => startday.AddDays(a))
|
||
.ToList();
|
||
foreach (var day in dateRange)
|
||
{
|
||
//轉換日期格式
|
||
var start = string.Format("{0}T{1}:00.000+08:00", day.ToString("yyyy-MM-dd"), post.Start_time);
|
||
var end = string.Format("{0}T{1}:10.000+08:00", day.ToString("yyyy-MM-dd"), post.End_time);
|
||
|
||
var historyQueryFilter = $@"<obj is='obix: HistoryFilter'>
|
||
<abstime name = 'start' val = '{start}' />
|
||
<abstime name = 'end' val = '{end}' />
|
||
</obj>";
|
||
|
||
XmlDocument xmlDocument = new XmlDocument();
|
||
List<string> errorPoints = new List<string>(); //錯誤的設備點位
|
||
if (listDevicePoint.Count > 0)
|
||
{
|
||
foreach (var d in listDevicePoint)
|
||
{
|
||
var station = backendRepository.GetOneAsync<string>($@"select parent_path from import_niagara_item_history where device_building_tag = '{d.FullDeviceNumberPoint.Split("_")[1].Replace("$3", "")}' and
|
||
device_system_tag = '{d.FullDeviceNumberPoint.Split("_")[2]}' and device_name_tag = '{d.FullDeviceNumberPoint.Split("_")[3]}'
|
||
and device_floor_tag = '{d.FullDeviceNumberPoint.Split("_")[4]}' and device_master_tag = '{d.FullDeviceNumberPoint.Split("_")[5]}'
|
||
and device_last_name_tag = '{d.FullDeviceNumberPoint.Split("_")[6]}' and device_serial_tag = '{d.FullDeviceNumberPoint.Split("_")[7]}'
|
||
and device_point_name = '{d.FullDeviceNumberPoint.Split("_")[8]}'").Result;
|
||
|
||
if (!string.IsNullOrEmpty(station))
|
||
{
|
||
|
||
HttpWebRequest request = (HttpWebRequest)WebRequest.Create($"{obixApiConfig.ApiBase}obix/histories/{station}/{d.FullDeviceNumberPoint}/~historyQuery/");
|
||
request.Method = "POST";
|
||
request.Headers.Add("Authorization", "Basic " + encoded);
|
||
request.PreAuthenticate = true;
|
||
|
||
byte[] byteArray = Encoding.UTF8.GetBytes(historyQueryFilter);
|
||
using (Stream reqStream = request.GetRequestStream())
|
||
{
|
||
reqStream.Write(byteArray, 0, byteArray.Length);
|
||
}
|
||
|
||
HttpWebResponse response = (HttpWebResponse)request.GetResponse();
|
||
var responseContent = new StreamReader(response.GetResponseStream()).ReadToEnd();
|
||
|
||
xmlDocument.LoadXml(responseContent);
|
||
string responseContentJson = JsonConvert.SerializeXmlNode(xmlDocument);
|
||
JObject responseContentJsonResult = (JObject)JsonConvert.DeserializeObject(responseContentJson);
|
||
|
||
if (responseContentJsonResult.ContainsKey("err")) //抓取錯誤
|
||
{
|
||
errorPoints.Add(d.DeviceNumber);
|
||
continue;
|
||
}
|
||
|
||
|
||
if (responseContentJsonResult.ContainsKey("obj")) //表示可以讀取到內容
|
||
{
|
||
var countObj = responseContentJsonResult["obj"]["int"];
|
||
if (countObj != null && countObj["@name"].ToString() == "count")
|
||
{ //判斷是否有資料筆數
|
||
if (Convert.ToInt32(countObj["@val"].ToString()) > 0)
|
||
{
|
||
var dataList = responseContentJsonResult["obj"]["list"];
|
||
if (dataList != null && dataList["obj"].HasValues)
|
||
{
|
||
//判斷單筆資料還是多筆
|
||
if (Convert.ToInt32(countObj["@val"].ToString()) == 1)
|
||
{
|
||
|
||
HistoryRawData historyRawData = new HistoryRawData();
|
||
//取得設備基本資訊
|
||
var device_number = d.DeviceNumber;
|
||
var point = d.Point;
|
||
string buildingTag = d.DeviceNumber.Split("_")[1];
|
||
//取得時間
|
||
var abstime = dataList["obj"]["abstime"]["@val"].ToString();
|
||
var val = dataList["obj"]["real"] != null ?
|
||
dataList["obj"]["real"]["@val"].ToString() : dataList["obj"]["bool"]["@val"].ToString();
|
||
|
||
HistoryExport hed = new HistoryExport();
|
||
hed.device_number = device_number;
|
||
hed.type = devicePoint.Where(x => x.device_building_tag == buildingTag && x.points == point).Select(x => x.full_name).FirstOrDefault();
|
||
hed.deviceName = device.Where(x => x.device_number == device_number).Select(x => x.full_name).FirstOrDefault();
|
||
hed.value = val;
|
||
hed.timestamp = Convert.ToDateTime(abstime);
|
||
hed.building_tag = buildingTag;
|
||
he.Add(hed);
|
||
|
||
}
|
||
else
|
||
{
|
||
//讀取資料
|
||
foreach (var obj in dataList["obj"])
|
||
{
|
||
|
||
HistoryRawData historyRawData = new HistoryRawData();
|
||
//取得設備基本資訊
|
||
var device_number = d.DeviceNumber;
|
||
var point = d.Point;
|
||
string buildingTag = d.DeviceNumber.Split("_")[1];
|
||
//取得時間
|
||
var abstime = obj["abstime"]["@val"].ToString();
|
||
var val = obj["real"] != null ?
|
||
obj["real"]["@val"].ToString() : obj["bool"]["@val"].ToString();
|
||
|
||
HistoryExport hed = new HistoryExport();
|
||
hed.device_number = device_number;
|
||
hed.type = devicePoint.Where(x => x.device_building_tag == buildingTag && x.points == point).Select(x => x.full_name).FirstOrDefault();
|
||
hed.deviceName = device.Where(x => x.device_number == device_number).Select(x => x.full_name).FirstOrDefault();
|
||
hed.value = val;
|
||
hed.timestamp = Convert.ToDateTime(abstime);
|
||
hed.building_tag = buildingTag;
|
||
he.Add(hed);
|
||
}
|
||
}
|
||
}
|
||
}
|
||
}
|
||
}
|
||
}
|
||
else
|
||
{
|
||
Logger.LogError("【Device GetWay Error】, device_number: " + d.FullDeviceNumberPoint);
|
||
}
|
||
}
|
||
}
|
||
|
||
if (errorPoints.Count() > 0)
|
||
{
|
||
Logger.LogError("【Device GetWay Error】, device_number: " + errorPoints);
|
||
}
|
||
|
||
}
|
||
|
||
#endregion
|
||
}
|
||
|
||
|
||
|
||
#endregion
|
||
|
||
#region export file progress
|
||
#region excel設定
|
||
IFont font12 = workbook.CreateFont();
|
||
font12.FontName = "新細明體";
|
||
font12.FontHeightInPoints = 12;
|
||
ICellStyle style12 = workbook.CreateCellStyle();
|
||
style12.SetFont(font12);
|
||
style12.Alignment = HorizontalAlignment.Center;
|
||
style12.VerticalAlignment = VerticalAlignment.Center;
|
||
IFont font12Times = workbook.CreateFont();
|
||
font12Times.FontName = "Times New Roman";
|
||
font12Times.FontHeightInPoints = 12;
|
||
IFont font18 = workbook.CreateFont();
|
||
font18.FontName = "新細明體";
|
||
font18.FontHeightInPoints = 18;
|
||
font18.IsBold = true;
|
||
ICellStyle styleTitle18 = workbook.CreateCellStyle();
|
||
styleTitle18.SetFont(font18);
|
||
styleTitle18.Alignment = HorizontalAlignment.Center;
|
||
styleTitle18.VerticalAlignment = VerticalAlignment.Center;
|
||
ICellStyle styleLeft12 = workbook.CreateCellStyle();
|
||
styleLeft12.SetFont(font12);
|
||
styleLeft12.Alignment = HorizontalAlignment.Left;
|
||
styleLeft12.VerticalAlignment = VerticalAlignment.Center;
|
||
ICellStyle styleLine12 = workbook.CreateCellStyle();
|
||
styleLine12.SetFont(font12);
|
||
styleLine12.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;
|
||
styleLine12.VerticalAlignment = VerticalAlignment.Center;
|
||
styleLine12.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin;
|
||
styleLine12.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;
|
||
styleLine12.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin;
|
||
styleLine12.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin;
|
||
ICellStyle stylein12 = workbook.CreateCellStyle();
|
||
stylein12.SetFont(font12Times);
|
||
stylein12.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Left;
|
||
stylein12.VerticalAlignment = VerticalAlignment.Center;
|
||
stylein12.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin;
|
||
stylein12.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;
|
||
stylein12.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin;
|
||
stylein12.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin;
|
||
stylein12.WrapText = true;
|
||
#endregion
|
||
var data = he.GroupBy(x => new { x.deviceName, x.type })
|
||
.SelectMany(g => g.OrderByDescending(x => x.timestamp))
|
||
.ToList(); ;
|
||
|
||
if (data.Count > 0)
|
||
{
|
||
string lastDeviceItem = string.Empty;
|
||
string lastDevice = string.Empty;
|
||
int RowPosition = 0;
|
||
IRow row;
|
||
ISheet sheet;
|
||
#region set cell
|
||
//sheet = workbook.CreateSheet($"{building.Where(x => x.building_tag == data[0].device_number.Split("_")[1]).Select(x => x.full_name).FirstOrDefault()}{"_" + data[0].deviceName + "_" + data[0].type}");
|
||
sheet = workbook.CreateSheet($"歷史資料");
|
||
row = sheet.CreateRow(RowPosition);
|
||
sheet.SetColumnWidth(0, 4 * 160 * 12);
|
||
sheet.SetColumnWidth(1, 4 * 160 * 12);
|
||
sheet.SetColumnWidth(2, 4 * 160 * 12);
|
||
sheet.SetColumnWidth(3, 4 * 160 * 12);
|
||
sheet.SetColumnWidth(4, 4 * 160 * 12);
|
||
ICell cell = row.CreateCell(0);
|
||
cell.SetCellValue("棟別");
|
||
cell.CellStyle = styleLine12;
|
||
cell = row.CreateCell(1);
|
||
cell.SetCellValue("設備名稱");
|
||
cell.CellStyle = styleLine12;
|
||
cell = row.CreateCell(2);
|
||
cell.SetCellValue("類型");
|
||
cell.CellStyle = styleLine12;
|
||
cell = row.CreateCell(3);
|
||
cell.SetCellValue("數值");
|
||
cell.CellStyle = styleLine12;
|
||
cell = row.CreateCell(4);
|
||
cell.SetCellValue("記錄時間");
|
||
cell.CellStyle = styleLine12;
|
||
#endregion
|
||
|
||
foreach (var d in data)
|
||
{
|
||
//if (RowPosition == 0 && lastDeviceItem == "" && lastDevice == "")
|
||
//{
|
||
// lastDeviceItem = d.type; //第一次不用建立 sheet;
|
||
// lastDevice = d.deviceName;
|
||
//}
|
||
//if (d.type != lastDeviceItem || d.deviceName != lastDevice)
|
||
//{
|
||
// RowPosition = 0;
|
||
// lastDeviceItem = d.type;
|
||
// lastDevice = d.deviceName;
|
||
// sheet = workbook.CreateSheet($"{building.Where(x => x.building_tag == d.device_number.Split("_")[1]).Select(x => x.full_name).FirstOrDefault()}{"_" + d.deviceName + "_" + d.type}");
|
||
// //sheet = workbook.CreateSheet($"{d.deviceName}{"_" + d.type}");
|
||
// #region set cell
|
||
// row = sheet.CreateRow(RowPosition);
|
||
// sheet.SetColumnWidth(0, 4 * 160 * 12);
|
||
// sheet.SetColumnWidth(1, 4 * 160 * 12);
|
||
// sheet.SetColumnWidth(2, 4 * 160 * 12);
|
||
// sheet.SetColumnWidth(3, 4 * 160 * 12);
|
||
// cell = row.CreateCell(0);
|
||
// cell.SetCellValue("類型");
|
||
// cell.CellStyle = styleLine12;
|
||
// cell = row.CreateCell(1);
|
||
// cell.SetCellValue("設備名稱");
|
||
// cell.CellStyle = styleLine12;
|
||
// cell = row.CreateCell(2);
|
||
// cell.SetCellValue("數值");
|
||
// cell.CellStyle = styleLine12;
|
||
// cell = row.CreateCell(3);
|
||
// cell.SetCellValue("記錄時間");
|
||
// cell.CellStyle = styleLine12;
|
||
// #endregion
|
||
// RowPosition = 1;
|
||
//}
|
||
//else
|
||
//{
|
||
// RowPosition += 1;
|
||
//}
|
||
RowPosition += 1;
|
||
|
||
row = sheet.CreateRow(RowPosition);
|
||
for (var i = 0; i < 5; i++)
|
||
{
|
||
cell = row.CreateCell(i);
|
||
if (i == 0)
|
||
{
|
||
cell.SetCellValue(building.Where(x => x.building_tag == d.device_number.Split("_")[1]).Select(x => x.full_name).FirstOrDefault());
|
||
}
|
||
if (i == 1)
|
||
{
|
||
cell.SetCellValue(d.deviceName);
|
||
}
|
||
if (i == 2)
|
||
{
|
||
cell.SetCellValue(d.type);
|
||
}
|
||
if (i == 3)
|
||
{
|
||
cell.SetCellValue(d.value);
|
||
}
|
||
if (i == 4)
|
||
{
|
||
cell.SetCellValue(d.timestamp.ToString("yyyy-MM-dd HH:mm:ss"));
|
||
}
|
||
|
||
cell.CellStyle = style12;
|
||
}
|
||
}
|
||
}
|
||
else
|
||
{
|
||
apiResult.Code = "0001";
|
||
apiResult.Msg = "選擇的區間沒有資料，無法匯出。";
|
||
return Json(apiResult);
|
||
}
|
||
#endregion
|
||
}
|
||
catch (Exception exception)
|
||
{
|
||
apiResult.Code = "9999";
|
||
apiResult.Msg = "系統內部錯誤，請聯絡管理者。 Msg: " + exception.Message;
|
||
Logger.LogError("【" + controllerName + "/" + actionName + "】" + exception.Message);
|
||
return Json(apiResult);
|
||
}
|
||
|
||
var ms = new NpoiMemoryStream
|
||
{
|
||
AllowClose = false
|
||
};
|
||
workbook.Write(ms);
|
||
ms.Flush();
|
||
ms.Seek(0, SeekOrigin.Begin);
|
||
Response.Headers.Add("Access-Control-Expose-Headers", "Content-Disposition");
|
||
|
||
return File(ms, "application/vnd.ms-excel", fileName);
|
||
}
|
||
|
||
/// <summary>
|
||
/// 即時趨勢條件過濾條件面板
|
||
/// </summary>
|
||
/// <param name="account"></param>
|
||
/// <returns></returns>
|
||
[HttpPost]
|
||
[Route("api/History/GetMainSub")]
|
||
public async Task<ActionResult<ApiResult<History_MainSubBuildFloor>>> GetMainSub([FromBody] HistoryFind hf)
|
||
{
|
||
ApiResult<History_MainSubBuildFloor> apiResult = new ApiResult<History_MainSubBuildFloor>(jwt_str);
|
||
if (!jwtlife)
|
||
{
|
||
apiResult.Code = "5000";
|
||
return BadRequest(apiResult);
|
||
}
|
||
else if (string.IsNullOrEmpty(hf.building_tag))
|
||
{
|
||
apiResult.Code = "0002";
|
||
apiResult.Msg = "必須選擇東別";
|
||
return apiResult;
|
||
}
|
||
|
||
try
|
||
{
|
||
var dbsub = await frontendRepository.GetAllAsync<HistoryDBMainSub>(
|
||
@$"select distinct v1.system_key main_name, v1.system_value main_system_tag, v2.system_key sub_name, v2.system_value sub_system_tag, v1.system_priority, v2.system_priority,
|
||
d.device_number, d.full_name as device_full_name, d.device_serial_tag ,di.is_show_history device_item_is_show_hsitory
|
||
from role_auth a
|
||
join auth_page b on a.AuthCode = b.AuthCode
|
||
join userinfo c on c.role_guid = a.role_guid
|
||
join variable v2 on b.ShowView = v2.id and v2.system_type = @sub_system_type
|
||
join variable v1 on v1.id = v2.system_parent_id and v1.system_type = @main_system_type
|
||
join device d on v1.system_value = d.device_system_tag and v2.system_value = d.device_name_tag and d.deleted = 0 and d.visible = 1 and d.is_link = 1 and d.device_building_tag = @building_tag
|
||
join device_item di on d.device_system_tag=di.device_system_tag and d.device_name_tag=di.device_name_tag
|
||
and di.is_link = 1 and di.is_show_history = 1
|
||
where c.account = @account
|
||
order by v1.system_priority, v2.system_priority", new { @account = myUser.account, @sub_system_type = sub_system_type, @main_system_type = main_system_type, building_tag = hf.building_tag });
|
||
var dbbuilding = await frontendRepository.GetAllAsync<History_Build>(
|
||
@$"select distinct d.building_tag,d.full_name,d.priority from role_auth a
|
||
join auth_page b on a.AuthCode = b.AuthCode
|
||
join userinfo c on c.role_guid = a.role_guid
|
||
join building d on d.building_tag = b.building_tag
|
||
where c.account = @account and d.building_tag = @building_tag
|
||
order by d.priority
|
||
", new { @account = myUser.account, @building_tag = hf.building_tag });
|
||
var mains = dbsub.GroupBy(a => a.main_system_tag).ToList();
|
||
apiResult.Data = new History_MainSubBuildFloor();
|
||
apiResult.Data.history_Main_Systems = new List<History_Main_system>();
|
||
foreach (var main in mains)
|
||
{
|
||
History_Main_system history_Main_System = new History_Main_system();
|
||
history_Main_System.main_system_tag = main.Select(a => a.main_system_tag).FirstOrDefault();
|
||
history_Main_System.full_name = main.Select(a => a.main_name).FirstOrDefault();
|
||
|
||
var subs = dbsub.Where(x => x.main_system_tag == main.Select(m => m.main_system_tag).FirstOrDefault()).GroupBy(x => x.sub_system_tag).ToList();
|
||
history_Main_System.History_Sub_systems = subs.Count > 0 ? new List<History_Sub_system>() : null;
|
||
foreach (var sub in subs)
|
||
{
|
||
History_Sub_system history_Sub_System = new History_Sub_system();
|
||
if (main.Select(x => x.main_system_tag).FirstOrDefault() != "S")
|
||
{
|
||
history_Sub_System.full_name = sub.Select(x => x.sub_name).FirstOrDefault();
|
||
history_Sub_System.sub_system_tag = sub.Select(x => x.sub_system_tag).FirstOrDefault();
|
||
history_Sub_System.is_show_history = sub.Select(x => x.device_item_is_show_hsitory).FirstOrDefault();
|
||
}
|
||
else
|
||
{
|
||
string name = sub.Select(x => x.sub_name).FirstOrDefault();
|
||
bool check = true;
|
||
if (name == "人流計數")
|
||
check = sub.Any(x => x.sub_system_tag.StartsWith(hf.building_tag));
|
||
|
||
if (check)
|
||
{
|
||
history_Sub_System.full_name = sub.Select(x => x.sub_name).FirstOrDefault();
|
||
history_Sub_System.sub_system_tag = sub.Select(x => x.sub_system_tag).FirstOrDefault();
|
||
history_Sub_System.is_show_history = sub.Select(x => x.device_item_is_show_hsitory).FirstOrDefault();
|
||
}
|
||
}
|
||
|
||
var devices = dbsub.Where(x => x.main_system_tag == main.Select(m => m.main_system_tag).FirstOrDefault() && x.sub_system_tag == sub.Select(x => x.sub_system_tag).FirstOrDefault() && x.device_number != null).OrderBy(x => x.device_full_name.Length).ThenBy(x => x.device_full_name).ToList();
|
||
history_Sub_System.device = devices.Count > 0 ? new List<Device>() : null;
|
||
foreach (var d in devices)
|
||
{
|
||
Device device = new Device();
|
||
device.device_number = d.device_number;
|
||
device.device_serial_tag = d.device_serial_tag;
|
||
device.full_name = d.device_full_name;
|
||
history_Sub_System.device.Add(device);
|
||
}
|
||
|
||
if (history_Sub_System.is_show_history == 1)
|
||
{
|
||
history_Main_System.History_Sub_systems.Add(history_Sub_System);
|
||
}
|
||
|
||
}
|
||
apiResult.Data.history_Main_Systems.Add(history_Main_System);
|
||
}
|
||
apiResult.Data.history_Builds = dbbuilding;
|
||
apiResult.Code = "0000";
|
||
|
||
|
||
|
||
}
|
||
catch (Exception exception)
|
||
{
|
||
apiResult.Code = "9999";
|
||
apiResult.Msg = "系統內部錯誤，請聯絡管理者。";
|
||
Logger.LogError("【" + controllerName + "/" + actionName + "】" + exception.Message);
|
||
return Ok(apiResult);
|
||
}
|
||
return Ok(apiResult);
|
||
}
|
||
|
||
/// <summary>
|
||
/// 即時趨勢條件過濾條件面板
|
||
/// </summary>
|
||
/// <param name="account"></param>
|
||
/// <returns></returns>
|
||
[HttpPost]
|
||
[Route("api/History/GetDevPoi")]
|
||
public async Task<ActionResult<ApiResult<List<DeviceItem>>>> GetDevPoi([FromBody] HistoryFind hf)
|
||
{
|
||
ApiResult<List<DeviceItem>> apiResult = new ApiResult<List<DeviceItem>>(jwt_str);
|
||
if (!jwtlife)
|
||
{
|
||
apiResult.Code = "5000";
|
||
return BadRequest(apiResult);
|
||
}
|
||
else if (string.IsNullOrEmpty(hf.device_number))
|
||
{
|
||
apiResult.Code = "0002";
|
||
apiResult.Msg = "必須選擇設備";
|
||
return apiResult;
|
||
}
|
||
|
||
try
|
||
{
|
||
List<DeviceItem> deviceItems = new List<DeviceItem>();
|
||
var main_system_value = hf.device_number.Split('_')[2];
|
||
var sub_system_value = hf.device_number.Split('_')[3];
|
||
var building_tag = char.IsDigit(hf.device_number.Split('_')[1][0]) ? "$3" + hf.device_number.Split('_')[1] : hf.device_number.Split('_')[1];
|
||
var sqlString = $@"select * from device_item where deleted = 0 and device_system_tag = @main_system_value and device_name_tag = @sub_system_value and is_show_history = 1 and device_building_tag = @building_tag";
|
||
deviceItems = await frontendRepository.GetAllAsync<DeviceItem>(sqlString, new { @main_system_value = main_system_value, @sub_system_value = sub_system_value, building_tag = building_tag });
|
||
|
||
apiResult.Data = deviceItems;
|
||
apiResult.Code = "0000";
|
||
}
|
||
catch (Exception exception)
|
||
{
|
||
apiResult.Code = "9999";
|
||
apiResult.Msg = "系統內部錯誤，請聯絡管理者。";
|
||
Logger.LogError("【" + controllerName + "/" + actionName + "】" + exception.Message);
|
||
return Ok(apiResult);
|
||
}
|
||
return Ok(apiResult);
|
||
}
|
||
|
||
/// <summary>
|
||
/// 依據過濾條件取得設備列表
|
||
/// </summary>
|
||
/// <param name="postDevice"></param>
|
||
/// <returns></returns>
|
||
[HttpPost]
|
||
[Route("api/History/GetDevice")]
|
||
public async Task<ActionResult<ApiResult<List<History_GetDevice>>>> GetDevice(History_PostDevice postDevice)
|
||
{
|
||
ApiResult<List<History_GetDevice>> apiResult = new ApiResult<List<History_GetDevice>>(jwt_str);
|
||
if (!jwtlife)
|
||
{
|
||
apiResult.Code = "5000";
|
||
return BadRequest(apiResult);
|
||
}
|
||
|
||
try
|
||
{
|
||
List<string> wheres = new List<string>();
|
||
wheres.Add("d.deleted = 0");
|
||
if (postDevice.SelectBuildings != null && postDevice.SelectBuildings.Count() > 0)
|
||
{
|
||
wheres.Add("d.device_building_tag in @builds");
|
||
}
|
||
|
||
if (postDevice.SelectFloors != null && postDevice.SelectFloors.Count() > 0)
|
||
{
|
||
wheres.Add("d.device_floor_tag in @floor_tag");
|
||
}
|
||
|
||
if (postDevice.SelectSub != null && postDevice.SelectSub.Count() > 0)
|
||
{
|
||
wheres.Add("d.device_name_tag in @syss");
|
||
}
|
||
|
||
if (!string.IsNullOrEmpty(postDevice.Device_name))
|
||
{
|
||
wheres.Add("d.full_name LIKE CONCAT('%', @device_name, '%')");
|
||
}
|
||
|
||
var wheres_str = string.Join(" AND ", wheres);
|
||
|
||
var sql = $@"SELECT
|
||
a.device_guid,
|
||
b.full_name building_name,
|
||
v1.system_key main_name,
|
||
v2.system_key sub_name,
|
||
a.full_name device_name,
|
||
a.device_number
|
||
from (SELECT *
|
||
FROM device d
|
||
WHERE {wheres_str}) a
|
||
join building b on b.building_tag = a.device_building_tag
|
||
join variable v1 on v1.system_value = a.device_system_tag and v1.deleted = 0 and v1.system_type = 'device_system_category_layer2'
|
||
join variable v2 on v2.system_value = a.device_name_tag and v2.deleted = 0 and v2.system_type = 'device_system_category_layer3'
|
||
order by b.priority,v1.system_priority,v2.system_priority,a.priority";
|
||
|
||
var dbDevice = await backendRepository.GetAllAsync<History_GetDevice>(sql, new { builds = postDevice.SelectBuildings, floor_tag = postDevice.SelectFloors, syss = postDevice.SelectSub, device_name = postDevice.Device_name });
|
||
apiResult.Data = dbDevice;
|
||
apiResult.Code = "0000";
|
||
}
|
||
catch (Exception exception)
|
||
{
|
||
apiResult.Code = "9999";
|
||
apiResult.Msg = "系統內部錯誤，請聯絡管理者。";
|
||
Logger.LogError("【" + controllerName + "/" + actionName + "】" + exception.Message);
|
||
}
|
||
return Ok(apiResult);
|
||
}
|
||
|
||
/// <summary>
|
||
/// 依據小類過濾條件取得設備列表
|
||
/// </summary>
|
||
/// <param name="SelectSub"></param>
|
||
/// <returns></returns>
|
||
[HttpPost]
|
||
[Route("api/History/GetDeviceInfo")]
|
||
public async Task<ActionResult<ApiResult<List<HistoryBuildingDevice>>>> GetDeviceInfo([FromBody] History_Sub_system SelectSub)
|
||
{
|
||
ApiResult<List<HistoryBuildingDevice>> apiResult = new ApiResult<List<HistoryBuildingDevice>>(jwt_str);
|
||
if (!jwtlife)
|
||
{
|
||
apiResult.Code = "5000";
|
||
return BadRequest(apiResult);
|
||
}
|
||
|
||
try
|
||
{
|
||
List<string> wheres = new List<string>();
|
||
wheres.Add("d.deleted = 0 and is_link = 1");
|
||
|
||
|
||
if (SelectSub.sub_system_tag != null)
|
||
{
|
||
wheres.Add("d.device_name_tag = @syss");
|
||
}
|
||
|
||
var wheres_str = string.Join(" AND ", wheres);
|
||
|
||
var Sql = $@"SELECT
|
||
b.full_name building_name,
|
||
b.building_tag,
|
||
a.device_guid,
|
||
a.full_name device_name,
|
||
a.device_number
|
||
from (SELECT *
|
||
FROM device d
|
||
WHERE {wheres_str}) a
|
||
join building b on b.building_tag = a.device_building_tag and b.deleted = 0
|
||
join variable v1 on v1.system_value = a.device_system_tag and v1.deleted = 0 and v1.system_type = 'device_system_category_layer2'
|
||
join variable v2 on v2.system_value = a.device_name_tag and v2.deleted = 0 and v2.system_type = 'device_system_category_layer3'
|
||
order by b.priority,v1.system_priority,v2.system_priority,a.priority,a.device_serial_tag";
|
||
var data = await backendRepository.GetAllAsync<GetHistoryBuildingDevice>(Sql, new { syss = SelectSub.sub_system_tag });
|
||
var groupedData = data.GroupBy(x => x.building_tag);
|
||
List<HistoryBuildingDevice> historyBuildingDevices = groupedData.Select(group =>
|
||
new HistoryBuildingDevice
|
||
{
|
||
building_tag = group.Key,
|
||
building_name = group.First().building_name,
|
||
device_list = group.Select(item => new HistoryDevice
|
||
{
|
||
device_guid = item.device_guid,
|
||
device_number = item.device_number,
|
||
device_name = item.device_name
|
||
}).ToList()
|
||
}).ToList();
|
||
|
||
apiResult.Data = historyBuildingDevices;
|
||
apiResult.Code = "0000";
|
||
}
|
||
catch (Exception exception)
|
||
{
|
||
apiResult.Code = "9999";
|
||
apiResult.Msg = "系統內部錯誤，請聯絡管理者。";
|
||
Logger.LogError("【" + controllerName + "/" + actionName + "】" + exception.Message);
|
||
}
|
||
return Ok(apiResult);
|
||
}
|
||
|
||
/// <summary>
|
||
/// 依據選得的設備，查看所有設備點位
|
||
/// </summary>
|
||
/// <param name="postDevice"></param>
|
||
/// <returns></returns>
|
||
[HttpPost]
|
||
[Route("api/History/GetAllDevPoi")]
|
||
public async Task<ActionResult<ApiResult<List<History_PostItem>>>> GetAllDevPoi([FromBody] PostHistoryDataFilter postDevice)
|
||
{
|
||
ApiResult<List<History_PostItem>> apiResult = new ApiResult<List<History_PostItem>>(jwt_str);
|
||
if (!jwtlife)
|
||
{
|
||
apiResult.Code = "5000";
|
||
return BadRequest(apiResult);
|
||
}
|
||
try
|
||
{
|
||
var dbDevice = await backendRepository.GetAllAsync<History_PostItem>(
|
||
@$"select c.full_name building_name,b.device_number,b.full_name device_name,a.full_name item_name,a.points,a.unit
|
||
from device_item a
|
||
join device b on a.device_system_tag = b.device_system_tag
|
||
and a.device_building_tag COLLATE utf8mb4_unicode_ci = b.device_building_tag
|
||
and a.device_name_tag COLLATE utf8mb4_unicode_ci = b.device_name_tag
|
||
join building c on c.building_tag = b.device_building_tag
|
||
join variable v1 on v1.system_value = b.device_system_tag and v1.deleted = 0 and v1.system_type = 'device_system_category_layer2'
|
||
join variable v2 on v2.system_value = b.device_name_tag and v2.deleted = 0 and v2.system_type = 'device_system_category_layer3'
|
||
where a.deleted = 0 and b.deleted = 0 and b.is_link = 1 and v2.deleted = 0 and v1.deleted = 0 and is_show_history = 1 and a.is_link = 1
|
||
and b.device_number in @Device_number
|
||
GROUP BY points
|
||
order by c.priority,b.priority,a.is_bool;"
|
||
, new { Device_number = postDevice.Device_list }
|
||
);
|
||
apiResult.Data = dbDevice;
|
||
apiResult.Code = "0000";
|
||
}
|
||
catch (Exception exception)
|
||
{
|
||
apiResult.Code = "9999";
|
||
apiResult.Msg = "系統內部錯誤，請聯絡管理者。";
|
||
Logger.LogError("【" + controllerName + "/" + actionName + "】" + exception.Message);
|
||
}
|
||
return Ok(apiResult);
|
||
}
|
||
|
||
|
||
/// <summary>
|
||
/// 取得歷史資料
|
||
/// </summary>
|
||
/// <param name="post"></param>
|
||
/// <returns></returns>
|
||
[HttpPost]
|
||
[Route("api/History/GetHistoryData")]
|
||
public async Task<ActionResult<PageResultT<HistoryRawData>>> GetHistoryData([FromBody] PostHistoryDataFilter post)
|
||
{
|
||
ApiResult<PageResultT<HistoryRawData>> apiResult = new ApiResult<PageResultT<HistoryRawData>>(jwt_str);
|
||
//if (!jwtlife)
|
||
//{
|
||
// apiResult.Code = "5000";
|
||
// return BadRequest(apiResult);
|
||
//}
|
||
//else if (post.Device_list == null || !post.Device_list.Any() || post.Points == null || !post.Points.Any())
|
||
//{
|
||
// apiResult.Code = "0001";
|
||
// apiResult.Msg = "請選擇設備及點位。";
|
||
// return apiResult;
|
||
//}
|
||
try
|
||
{
|
||
Stopwatch stopwatch = new Stopwatch();
|
||
stopwatch.Start();
|
||
#region 取得obix 設定
|
||
EDFunction ed = new EDFunction();
|
||
var obixApiConfig = new ObixApiConfig();
|
||
var sqlObix = $@"SELECT system_value as Value, system_key as Name FROM variable WHERE deleted = 0 AND system_type = 'obixConfig'";
|
||
var variable = await frontendRepository.GetAllAsync<KeyValue>(sqlObix);
|
||
obixApiConfig.ApiBase = variable.Where(x => x.Name == "ApiBase").Select(x => x.Value).FirstOrDefault();
|
||
obixApiConfig.UserName = ed.AESDecrypt(variable.Where(x => x.Name == "UserName").Select(x => x.Value).FirstOrDefault());
|
||
obixApiConfig.Password = ed.AESDecrypt(variable.Where(x => x.Name == "Password").Select(x => x.Value).FirstOrDefault());
|
||
String encoded = System.Convert.ToBase64String(System.Text.Encoding.GetEncoding("ISO-8859-1").GetBytes(obixApiConfig.UserName + ":" + obixApiConfig.Password));
|
||
#endregion 取得obix 設定
|
||
|
||
List<DeviceNumberPoint> listDevicePoint = new List<DeviceNumberPoint>();
|
||
List<HistoryRawData> historyRawDatas = new List<HistoryRawData>();
|
||
int pageNumber = post.PageNumber;
|
||
int pageSize = 10;
|
||
int totalItems = 0;
|
||
#region combine device and point
|
||
// 因傳進來point不是比照前端順序，故這邊又排序了一次
|
||
var Sql = $@"select c.full_name building_name,b.device_number,b.full_name device_name,a.full_name item_name,a.points,a.unit
|
||
from device_item a
|
||
join device b on a.device_system_tag = b.device_system_tag
|
||
and a.device_building_tag COLLATE utf8mb4_unicode_ci = b.device_building_tag
|
||
and a.device_name_tag COLLATE utf8mb4_unicode_ci = b.device_name_tag
|
||
join building c on c.building_tag = b.device_building_tag
|
||
join variable v1 on v1.system_value = b.device_system_tag and v1.deleted = 0 and v1.system_type = 'device_system_category_layer2'
|
||
join variable v2 on v2.system_value = b.device_name_tag and v2.deleted = 0 and v2.system_type = 'device_system_category_layer3'
|
||
where a.deleted = 0 and b.deleted = 0 and b.is_link = 1 and v2.deleted = 0 and v1.deleted = 0 and is_show_history = 1 and a.is_link = 1
|
||
and b.device_number = 'NTPC_G6_EE_E4_B1F_EMP_WHT_N1'
|
||
GROUP BY points
|
||
order by c.priority,b.priority,a.is_bool;";
|
||
var pointData = backendRepository.GetAllAsync<History_PostItem>(Sql).Result;
|
||
List<string> points = pointData.Select(x => x.points).ToList();
|
||
List<string> inputParams = post.Points;
|
||
inputParams.Sort((a, b) => points.IndexOf(a).CompareTo(points.IndexOf(b)));
|
||
foreach (var Device in post.Device_list)
|
||
{
|
||
foreach (var point in inputParams)
|
||
{
|
||
DeviceNumberPoint deviceNumberPoint = new DeviceNumberPoint();
|
||
deviceNumberPoint.DeviceNumber = Device;
|
||
deviceNumberPoint.Point = point;
|
||
deviceNumberPoint.FullDeviceNumberPoint = string.Format("{0}_{1}", Device, point);
|
||
|
||
listDevicePoint.Add(deviceNumberPoint);
|
||
}
|
||
}
|
||
#endregion
|
||
|
||
//依據被選擇的設備找出相對應資料
|
||
var sqlDeviceItemInfo = $@"select
|
||
b.full_name as building_name,
|
||
v1.system_key as Main_system_name,
|
||
v2.system_key as Sub_system_name,
|
||
temp.device_number,
|
||
temp.device_name,
|
||
temp.full_name as item_name,
|
||
temp.points,
|
||
temp.device_building_tag,
|
||
temp.unit
|
||
from (
|
||
select
|
||
di.*,
|
||
d.device_number,
|
||
d.full_name as device_name,
|
||
d.priority
|
||
from device_item di
|
||
inner join device d on di.device_building_tag COLLATE utf8mb4_unicode_ci = d.device_building_tag
|
||
and di.device_system_tag COLLATE utf8mb4_unicode_ci = d.device_system_tag
|
||
and di.device_name_tag COLLATE utf8mb4_unicode_ci = d.device_name_tag
|
||
and d.deleted = 0
|
||
where di.deleted = 0
|
||
and di.points in @points
|
||
and d.device_number IN @Device_number
|
||
) temp
|
||
inner join building b on temp.device_building_tag COLLATE utf8mb4_unicode_ci = b.building_tag and b.deleted = 0
|
||
inner join variable v1 on temp.device_system_tag COLLATE utf8mb4_unicode_ci = v1.system_value and v1.deleted = 0 and v1.system_type = 'device_system_category_layer2'
|
||
inner join variable v2 on temp.device_name_tag COLLATE utf8mb4_unicode_ci = v2.system_value and v2.deleted = 0 and v2.system_type = 'device_system_category_layer3'
|
||
order by b.priority, v1.system_priority, v2.system_priority, temp.priority;";
|
||
|
||
var device_item_infos = await frontendRepository.GetAllAsync<DeviceItemInfo>(sqlDeviceItemInfo, new { Device_number = post.Device_list, points = post.Points });
|
||
|
||
if (post.Type == 1)
|
||
{
|
||
#region Niagara抓資料依據日期
|
||
|
||
//轉換日期格式
|
||
var start = string.Format("{0}T{1}:00.000+08:00", post.Start_date, post.Start_time);
|
||
var end = string.Format("{0}T{1}:10.000+08:00", post.End_date, post.End_time);
|
||
|
||
var historyQueryFilter = $@"<obj is='obix: HistoryFilter'>
|
||
<abstime name = 'start' val = '{start}' />
|
||
<abstime name = 'end' val = '{end}' />
|
||
</obj>";
|
||
|
||
XmlDocument xmlDocument = new XmlDocument();
|
||
List<string> errorPoints = new List<string>(); //錯誤的設備點位
|
||
if (listDevicePoint.Count > 0)
|
||
{
|
||
foreach (var d in listDevicePoint)
|
||
{
|
||
var station = backendRepository.GetOneAsync<string>($@"select parent_path from import_niagara_item_history where device_building_tag = '{d.FullDeviceNumberPoint.Split("_")[1].Replace("$3", "")}' and
|
||
device_system_tag = '{d.FullDeviceNumberPoint.Split("_")[2]}' and device_name_tag = '{d.FullDeviceNumberPoint.Split("_")[3]}'
|
||
and device_floor_tag = '{d.FullDeviceNumberPoint.Split("_")[4]}' and device_master_tag = '{d.FullDeviceNumberPoint.Split("_")[5]}'
|
||
and device_last_name_tag = '{d.FullDeviceNumberPoint.Split("_")[6]}' and device_serial_tag = '{d.FullDeviceNumberPoint.Split("_")[7]}'
|
||
and device_point_name = '{d.FullDeviceNumberPoint.Split("_")[8]}'").Result;
|
||
|
||
if (!string.IsNullOrEmpty(station))
|
||
{
|
||
|
||
HttpWebRequest request = (HttpWebRequest)WebRequest.Create($"{obixApiConfig.ApiBase}obix/histories/{station}/{d.FullDeviceNumberPoint}/~historyQuery/");
|
||
request.Method = "POST";
|
||
request.Headers.Add("Authorization", "Basic " + encoded);
|
||
request.PreAuthenticate = true;
|
||
|
||
byte[] byteArray = Encoding.UTF8.GetBytes(historyQueryFilter);
|
||
using (Stream reqStream = request.GetRequestStream())
|
||
{
|
||
reqStream.Write(byteArray, 0, byteArray.Length);
|
||
}
|
||
|
||
HttpWebResponse response = (HttpWebResponse)request.GetResponse();
|
||
var responseContent = new StreamReader(response.GetResponseStream()).ReadToEnd();
|
||
|
||
xmlDocument.LoadXml(responseContent);
|
||
string responseContentJson = JsonConvert.SerializeXmlNode(xmlDocument);
|
||
JObject responseContentJsonResult = (JObject)JsonConvert.DeserializeObject(responseContentJson);
|
||
|
||
if (responseContentJsonResult.ContainsKey("err")) //抓取錯誤
|
||
{
|
||
errorPoints.Add(d.DeviceNumber);
|
||
continue;
|
||
}
|
||
|
||
|
||
if (responseContentJsonResult.ContainsKey("obj")) //表示可以讀取到內容
|
||
{
|
||
var countObj = responseContentJsonResult["obj"]["int"];
|
||
totalItems += Convert.ToInt32(countObj["@val"].ToString());
|
||
if (countObj != null && countObj["@name"].ToString() == "count")
|
||
{ //判斷是否有資料筆數
|
||
if (Convert.ToInt32(countObj["@val"].ToString()) > 0)
|
||
{
|
||
var dataList = responseContentJsonResult["obj"]["list"];
|
||
if (dataList != null && dataList["obj"].HasValues)
|
||
{
|
||
//判斷單筆資料還是多筆
|
||
if (Convert.ToInt32(countObj["@val"].ToString()) == 1)
|
||
{
|
||
HistoryRawData historyRawData = new HistoryRawData();
|
||
|
||
//取得設備基本資訊
|
||
var device_number = d.DeviceNumber;
|
||
var point = d.Point;
|
||
var device_item_info = device_item_infos.Where(x => x.Device_number == device_number && x.Points == point).FirstOrDefault();
|
||
//取得時間
|
||
var abstime = dataList["obj"]["abstime"]["@val"].ToString();
|
||
historyRawData.Timestamp = Convert.ToDateTime(abstime).ToString("yyyy-MM-dd HH:mm:ss");
|
||
//取得資料
|
||
historyRawData.Value = dataList["obj"]["real"] != null ?
|
||
dataList["obj"]["real"]["@val"].ToString() : dataList["obj"]["bool"]["@val"].ToString();
|
||
historyRawData.Device_number = device_item_info.Device_number;
|
||
historyRawData.Building_name = device_item_info.Building_name;
|
||
historyRawData.Device_name = device_item_info.Device_name;
|
||
historyRawData.Points = device_item_info.Points;
|
||
historyRawDatas.Add(historyRawData);
|
||
}
|
||
else
|
||
{
|
||
//讀取資料
|
||
foreach (var obj in dataList["obj"])
|
||
{
|
||
HistoryRawData historyRawData = new HistoryRawData();
|
||
|
||
//取得設備基本資訊
|
||
var device_number = d.DeviceNumber;
|
||
var point = d.Point;
|
||
var device_item_info = device_item_infos.Where(x => x.Device_number == device_number && x.Points == point).FirstOrDefault();
|
||
//取得時間
|
||
var abstime = obj["abstime"]["@val"].ToString();
|
||
historyRawData.Timestamp = Convert.ToDateTime(abstime).ToString("yyyy-MM-dd HH:mm:ss");
|
||
//取得資料
|
||
historyRawData.Value = obj["real"] != null ?
|
||
obj["real"]["@val"].ToString() : obj["bool"]["@val"].ToString();
|
||
historyRawData.Device_number = device_item_info.Device_number;
|
||
historyRawData.Building_name = device_item_info.Building_name;
|
||
historyRawData.Device_name = device_item_info.Device_name;
|
||
historyRawData.Points = device_item_info.Points;
|
||
historyRawDatas.Add(historyRawData);
|
||
}
|
||
}
|
||
|
||
}
|
||
}
|
||
}
|
||
}
|
||
}
|
||
else
|
||
{
|
||
Logger.LogError("【Device GetWay Error】, device_number: " + d.FullDeviceNumberPoint);
|
||
}
|
||
}
|
||
}
|
||
|
||
if (errorPoints.Count() > 0)
|
||
{
|
||
Logger.LogError("【Device GetWay Error】, device_number: " + errorPoints);
|
||
}
|
||
|
||
#endregion
|
||
}
|
||
if (post.Type == 2)
|
||
{
|
||
#region Niagara抓資料依據時間段
|
||
DateTime startday = DateTime.Parse(post.Start_date);
|
||
DateTime endtday = DateTime.Parse(post.End_date);
|
||
List<DateTime> dateRange = Enumerable.Range(0, (endtday - startday).Days + 1)
|
||
.Select(a => startday.AddDays(a))
|
||
.ToList();
|
||
foreach (var day in dateRange)
|
||
{
|
||
//轉換日期格式
|
||
var start = string.Format("{0}T{1}:00.000+08:00", day.ToString("yyyy-MM-dd"), post.Start_time);
|
||
var end = string.Format("{0}T{1}:10.000+08:00", day.ToString("yyyy-MM-dd"), post.End_time);
|
||
|
||
var historyQueryFilter = $@"<obj is='obix: HistoryFilter'>
|
||
<abstime name = 'start' val = '{start}' />
|
||
<abstime name = 'end' val = '{end}' />
|
||
</obj>";
|
||
|
||
XmlDocument xmlDocument = new XmlDocument();
|
||
List<string> errorPoints = new List<string>(); //錯誤的設備點位
|
||
if (listDevicePoint.Count > 0)
|
||
{
|
||
foreach (var d in listDevicePoint)
|
||
{
|
||
var station = backendRepository.GetOneAsync<string>($@"select parent_path from import_niagara_item_history where device_building_tag = '{d.FullDeviceNumberPoint.Split("_")[1].Replace("$3", "")}' and
|
||
device_system_tag = '{d.FullDeviceNumberPoint.Split("_")[2]}' and device_name_tag = '{d.FullDeviceNumberPoint.Split("_")[3]}'
|
||
and device_floor_tag = '{d.FullDeviceNumberPoint.Split("_")[4]}' and device_master_tag = '{d.FullDeviceNumberPoint.Split("_")[5]}'
|
||
and device_last_name_tag = '{d.FullDeviceNumberPoint.Split("_")[6]}' and device_serial_tag = '{d.FullDeviceNumberPoint.Split("_")[7]}'
|
||
and device_point_name = '{d.FullDeviceNumberPoint.Split("_")[8]}'").Result;
|
||
|
||
if (!string.IsNullOrEmpty(station))
|
||
{
|
||
|
||
HttpWebRequest request = (HttpWebRequest)WebRequest.Create($"{obixApiConfig.ApiBase}obix/histories/{station}/{d.FullDeviceNumberPoint}/~historyQuery/");
|
||
request.Method = "POST";
|
||
request.Headers.Add("Authorization", "Basic " + encoded);
|
||
request.PreAuthenticate = true;
|
||
|
||
byte[] byteArray = Encoding.UTF8.GetBytes(historyQueryFilter);
|
||
using (Stream reqStream = request.GetRequestStream())
|
||
{
|
||
reqStream.Write(byteArray, 0, byteArray.Length);
|
||
}
|
||
|
||
HttpWebResponse response = (HttpWebResponse)request.GetResponse();
|
||
var responseContent = new StreamReader(response.GetResponseStream()).ReadToEnd();
|
||
|
||
xmlDocument.LoadXml(responseContent);
|
||
string responseContentJson = JsonConvert.SerializeXmlNode(xmlDocument);
|
||
JObject responseContentJsonResult = (JObject)JsonConvert.DeserializeObject(responseContentJson);
|
||
|
||
if (responseContentJsonResult.ContainsKey("err")) //抓取錯誤
|
||
{
|
||
errorPoints.Add(d.DeviceNumber);
|
||
continue;
|
||
}
|
||
|
||
|
||
if (responseContentJsonResult.ContainsKey("obj")) //表示可以讀取到內容
|
||
{
|
||
var countObj = responseContentJsonResult["obj"]["int"];
|
||
totalItems += Convert.ToInt32(countObj["@val"].ToString());
|
||
if (countObj != null && countObj["@name"].ToString() == "count")
|
||
{ //判斷是否有資料筆數
|
||
if (Convert.ToInt32(countObj["@val"].ToString()) > 0)
|
||
{
|
||
var dataList = responseContentJsonResult["obj"]["list"];
|
||
if (dataList != null && dataList["obj"].HasValues)
|
||
{
|
||
//判斷單筆資料還是多筆
|
||
if (Convert.ToInt32(countObj["@val"].ToString()) == 1)
|
||
{
|
||
HistoryRawData historyRawData = new HistoryRawData();
|
||
|
||
//取得設備基本資訊
|
||
var device_number = d.DeviceNumber;
|
||
var point = d.Point;
|
||
var device_item_info = device_item_infos.Where(x => x.Device_number == device_number && x.Points == point).FirstOrDefault();
|
||
//取得時間
|
||
var abstime = dataList["obj"]["abstime"]["@val"].ToString();
|
||
historyRawData.Timestamp = Convert.ToDateTime(abstime).ToString("yyyy-MM-dd HH:mm:ss");
|
||
//取得資料
|
||
historyRawData.Value = dataList["obj"]["real"] != null ?
|
||
dataList["obj"]["real"]["@val"].ToString() : dataList["obj"]["bool"]["@val"].ToString();
|
||
historyRawData.Device_number = device_item_info.Device_number;
|
||
historyRawData.Building_name = device_item_info.Building_name;
|
||
historyRawData.Device_name = device_item_info.Device_name;
|
||
historyRawData.Points = device_item_info.Points;
|
||
historyRawDatas.Add(historyRawData);
|
||
}
|
||
else
|
||
{
|
||
//讀取資料
|
||
foreach (var obj in dataList["obj"])
|
||
{
|
||
HistoryRawData historyRawData = new HistoryRawData();
|
||
|
||
//取得設備基本資訊
|
||
var device_number = d.DeviceNumber;
|
||
var point = d.Point;
|
||
var device_item_info = device_item_infos.Where(x => x.Device_number == device_number && x.Points == point).FirstOrDefault();
|
||
//取得時間
|
||
var abstime = obj["abstime"]["@val"].ToString();
|
||
historyRawData.Timestamp = Convert.ToDateTime(abstime).ToString("yyyy-MM-dd HH:mm:ss");
|
||
//取得資料
|
||
historyRawData.Value = obj["real"] != null ?
|
||
obj["real"]["@val"].ToString() : obj["bool"]["@val"].ToString();
|
||
historyRawData.Device_number = device_item_info.Device_number;
|
||
historyRawData.Building_name = device_item_info.Building_name;
|
||
historyRawData.Device_name = device_item_info.Device_name;
|
||
historyRawData.Points = device_item_info.Points;
|
||
historyRawDatas.Add(historyRawData);
|
||
}
|
||
}
|
||
|
||
}
|
||
}
|
||
}
|
||
}
|
||
}
|
||
else
|
||
{
|
||
Logger.LogError("【Device GetWay Error】, device_number: " + d.FullDeviceNumberPoint);
|
||
}
|
||
}
|
||
}
|
||
|
||
if (errorPoints.Count() > 0)
|
||
{
|
||
Logger.LogError("【Device GetWay Error】, device_number: " + errorPoints);
|
||
}
|
||
|
||
}
|
||
|
||
#endregion
|
||
}
|
||
|
||
var data = historyRawDatas.GroupBy(x => new { x.Device_number, x.Points })
|
||
.SelectMany(g => g.OrderByDescending(x => x.Timestamp))
|
||
.ToList();
|
||
var pagedDevices = data.Skip((pageNumber - 1) * pageSize).Take(pageSize);
|
||
stopwatch.Stop();
|
||
double elapsedTime = stopwatch.Elapsed.TotalSeconds;
|
||
var result = new PageResultT<HistoryRawData>
|
||
{
|
||
Items = data,
|
||
PageNumber = pageNumber,
|
||
PageSize = pageSize,
|
||
TotalItems = totalItems,
|
||
ElapsedTime = elapsedTime,
|
||
};
|
||
apiResult.Code = "0000";
|
||
apiResult.Data = result;
|
||
|
||
return Ok(apiResult);
|
||
}
|
||
catch (Exception exception)
|
||
{
|
||
apiResult.Code = "9999";
|
||
apiResult.Msg = "系統內部錯誤，請聯絡管理者。";
|
||
Logger.LogError("【" + controllerName + "/" + actionName + "】" + exception.ToString());
|
||
return Ok(apiResult);
|
||
}
|
||
}
|
||
|
||
/// <summary>
|
||
/// 取得歷史資料常用組合
|
||
/// </summary>
|
||
/// <param></param>
|
||
/// <returns></returns>
|
||
[HttpPost]
|
||
[Route("api/History/GetHistoryFavorite")]
|
||
public async Task<ActionResult<ApiResult<List<HistoryFavorite>>>> GetHistoryFavorite()
|
||
{
|
||
ApiResult<List<HistoryFavorite>> apiResult = new ApiResult<List<HistoryFavorite>>(jwt_str);
|
||
if (!jwtlife)
|
||
{
|
||
apiResult.Code = "5000";
|
||
return BadRequest(apiResult);
|
||
}
|
||
try
|
||
{
|
||
var sql = $@"SELECT * from history_favorite WHERE userinfo_guid = @userinfo_guid and deleted = 0";
|
||
var rawDatas = await frontendRepository.GetAllAsync<HistoryFavorite>(sql, new { userinfo_guid = myUser.userinfo_guid });
|
||
foreach (var item in rawDatas)
|
||
{
|
||
var detailSql = $@"SELECT * from history_favorite_detail WHERE favorite_guid = @favorite_guid";
|
||
var detailDatas = await frontendRepository.GetAllAsync<HistoryFavoriteDetail>(detailSql, new { favorite_guid = item.favorite_guid });
|
||
foreach (var detailData in detailDatas)
|
||
{
|
||
if (detailData.key == "device")
|
||
{
|
||
item.device_list.Add(detailData.value);
|
||
}
|
||
else
|
||
{
|
||
item.Points.Add(detailData.value);
|
||
}
|
||
}
|
||
}
|
||
|
||
apiResult.Data = rawDatas;
|
||
apiResult.Code = "0000";
|
||
}
|
||
catch (Exception exception)
|
||
{
|
||
apiResult.Code = "9999";
|
||
apiResult.Msg = "系統內部錯誤，請聯絡管理者。";
|
||
Logger.LogError("【" + controllerName + "/" + actionName + "】" + exception.Message);
|
||
}
|
||
return Ok(apiResult);
|
||
}
|
||
/// <summary>
|
||
/// 儲存歷史資料常用組合
|
||
/// </summary>
|
||
/// <param></param>
|
||
/// <returns></returns>
|
||
[HttpPost]
|
||
[Route("api/History/SaveHistoryFavorite")]
|
||
public async Task<ActionResult<ApiResult<List<HistoryFavorite>>>> SaveHistoryFavorite([FromBody] HistoryFavorite post)
|
||
{
|
||
ApiResult<List<HistoryFavorite>> apiResult = new ApiResult<List<HistoryFavorite>>(jwt_str);
|
||
var favorite = await frontendRepository.GetOneAsync<HistoryFavorite>("history_favorite", "favorite_name = @favorite_name and deleted = 0", new { favorite_name = post.favorite_name });
|
||
if (favorite != null)
|
||
{
|
||
apiResult.Code = "5000";
|
||
apiResult.Msg = "已有該組合名稱";
|
||
|
||
return Ok(apiResult);
|
||
}
|
||
if (!jwtlife)
|
||
{
|
||
apiResult.Code = "5000";
|
||
return BadRequest(apiResult);
|
||
}
|
||
try
|
||
{
|
||
//產生一組GUID
|
||
var guid = Guid.NewGuid(); //常用組合GUID
|
||
|
||
Dictionary<string, object> combination = new Dictionary<string, object>();
|
||
combination = new Dictionary<string, object>()
|
||
{
|
||
{ "@favorite_guid", guid},
|
||
{ "@favorite_name", post.favorite_name},
|
||
{ "@userinfo_guid", myUser.userinfo_guid},
|
||
{ "@device_name_tag", post.device_name_tag},
|
||
{ "@type", post.type},
|
||
{ "@created_at", DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss")}
|
||
};
|
||
await frontendRepository.AddOneByCustomTable(combination, "history_favorite");
|
||
|
||
List<Dictionary<string, object>> detailDics = new List<Dictionary<string, object>>();
|
||
foreach (var device in post.device_list)
|
||
{
|
||
Dictionary<string, object> detailDic = new Dictionary<string, object>()
|
||
{
|
||
{ "@favorite_guid", guid},
|
||
{ "@key", "device"},
|
||
{ "@value", device}
|
||
};
|
||
|
||
detailDics.Add(detailDic);
|
||
}
|
||
foreach (var point in post.Points)
|
||
{
|
||
Dictionary<string, object> detailDic = new Dictionary<string, object>()
|
||
{
|
||
{ "@favorite_guid", guid},
|
||
{ "@key", "point"},
|
||
{ "@value", point}
|
||
};
|
||
|
||
detailDics.Add(detailDic);
|
||
}
|
||
|
||
await frontendRepository.AddMutiByCustomTable(detailDics, "history_favorite_detail");
|
||
|
||
apiResult.Msg = "儲存成功";
|
||
apiResult.Code = "0000";
|
||
}
|
||
catch (Exception exception)
|
||
{
|
||
apiResult.Code = "9999";
|
||
apiResult.Msg = "系統內部錯誤，請聯絡管理者。";
|
||
Logger.LogError("【" + controllerName + "/" + actionName + "】" + exception.Message);
|
||
}
|
||
return Ok(apiResult);
|
||
}
|
||
/// <summary>
|
||
/// 修改歷史資料常用組合名稱
|
||
/// </summary>
|
||
/// <param name="post"></param>
|
||
/// <returns></returns>
|
||
[HttpPost]
|
||
[Route("api/History/EditHistoryFavorite")]
|
||
public async Task<ActionResult<ApiResult<string>>> EditHistoryFavoriten([FromBody] HistoryFavorite post)
|
||
{
|
||
ApiResult<string> apiResult = new ApiResult<string>(jwt_str);
|
||
if (!jwtlife)
|
||
{
|
||
apiResult.Code = "5000";
|
||
return BadRequest(apiResult);
|
||
}
|
||
try
|
||
{
|
||
var sWhere = $@"deleted = 0 AND favorite_guid = @favorite_guid";
|
||
|
||
var Combination = await frontendRepository.GetOneAsync<HistoryFavorite>("history_favorite", sWhere, new { favorite_guid = post.favorite_guid });
|
||
|
||
if (Combination == null)
|
||
{
|
||
apiResult.Code = "9998";
|
||
apiResult.Msg = "查無該組合";
|
||
return Ok(apiResult);
|
||
}
|
||
else
|
||
{
|
||
Dictionary<string, object> CombinationDic = new Dictionary<string, object>()
|
||
{
|
||
{ "@favorite_name", post.favorite_name },
|
||
{ "@updated_at", DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss")}
|
||
};
|
||
|
||
await frontendRepository.UpdateOneByCustomTable(CombinationDic, "history_favorite", $@"favorite_guid = '{post.favorite_guid}'");
|
||
|
||
}
|
||
apiResult.Msg = "修改成功。";
|
||
apiResult.Code = "0000";
|
||
}
|
||
catch (Exception exception)
|
||
{
|
||
apiResult.Code = "9999";
|
||
apiResult.Msg = "系統內部錯誤，請聯絡管理者。";
|
||
Logger.LogError("【" + controllerName + "/" + actionName + "】" + exception.Message);
|
||
}
|
||
return Ok(apiResult);
|
||
}
|
||
/// <summary>
|
||
/// 刪除歷史資料常用組合名稱
|
||
/// </summary>
|
||
/// <param name="post"></param>
|
||
/// <returns></returns>
|
||
[HttpPost]
|
||
[Route("api/History/DeleteHistoryFavorite")]
|
||
public async Task<ActionResult<ApiResult<string>>> DeleteHistoryFavorite([FromBody] HistoryFavorite post)
|
||
{
|
||
ApiResult<string> apiResult = new ApiResult<string>(jwt_str);
|
||
if (!jwtlife)
|
||
{
|
||
apiResult.Code = "5000";
|
||
return BadRequest(apiResult);
|
||
}
|
||
try
|
||
{
|
||
var sWhere = $@"deleted = 0 AND favorite_guid = @favorite_guid";
|
||
|
||
var Combination = await frontendRepository.GetOneAsync<RealTimeCombination>("history_favorite", sWhere, new { favorite_guid = post.favorite_guid });
|
||
|
||
if (Combination == null)
|
||
{
|
||
apiResult.Code = "9998";
|
||
apiResult.Msg = "查無該組合";
|
||
return Ok(apiResult);
|
||
}
|
||
else
|
||
{
|
||
await frontendRepository.DeleteOne(post.favorite_guid, "history_favorite", "favorite_guid");
|
||
}
|
||
apiResult.Msg = "刪除成功。";
|
||
apiResult.Code = "0000";
|
||
|
||
}
|
||
catch (Exception exception)
|
||
{
|
||
apiResult.Code = "9999";
|
||
apiResult.Msg = "系統內部錯誤，請聯絡管理者。";
|
||
Logger.LogError("【" + controllerName + "/" + actionName + "】" + exception.Message);
|
||
}
|
||
return Ok(apiResult);
|
||
}
|
||
/// <summary>
|
||
/// 歷史資料依據選得的設備，查看所有設備點位
|
||
/// </summary>
|
||
/// <param name="postDevice"></param>
|
||
/// <returns></returns>
|
||
[HttpPost]
|
||
[Route("api/History/LookRealTime")]
|
||
public async Task<ActionResult<ApiResult<List<History_PostItem>>>> LookRealTime(itemlist postDevice)
|
||
{
|
||
ApiResult<List<History_PostItem>> apiResult = new ApiResult<List<History_PostItem>>(jwt_str);
|
||
if (!jwtlife)
|
||
{
|
||
apiResult.Code = "5000";
|
||
return BadRequest(apiResult);
|
||
}
|
||
try
|
||
{
|
||
var dbDevice = await backendRepository.GetAllAsync<History_PostItem>(
|
||
@$"select c.full_name building_name,b.device_number,b.full_name device_name,a.full_name item_name,a.points,a.unit
|
||
from device_item a
|
||
join device b on a.device_system_tag = b.device_system_tag
|
||
and a.device_building_tag COLLATE utf8mb4_unicode_ci = b.device_building_tag
|
||
and a.device_name_tag COLLATE utf8mb4_unicode_ci = b.device_name_tag
|
||
join building c on c.building_tag = b.device_building_tag
|
||
join variable v1 on v1.system_value = b.device_system_tag and v1.deleted = 0 and v1.system_type = 'device_system_category_layer2'
|
||
join variable v2 on v2.system_value = b.device_name_tag and v2.deleted = 0 and v2.system_type = 'device_system_category_layer3'
|
||
where a.deleted = 0 and b.deleted = 0 and v2.deleted = 0 and v1.deleted = 0
|
||
and a.unit is not null and b.device_number in @Device_number
|
||
order by c.priority,b.priority;"
|
||
, new { Device_number = postDevice.select_data.Select(a => a.device_number).ToList() }
|
||
);
|
||
apiResult.Data = dbDevice;
|
||
apiResult.Code = "0000";
|
||
}
|
||
catch (Exception exception)
|
||
{
|
||
apiResult.Code = "9999";
|
||
apiResult.Msg = "系統內部錯誤，請聯絡管理者。";
|
||
Logger.LogError("【" + controllerName + "/" + actionName + "】" + exception.Message);
|
||
}
|
||
return Ok(apiResult);
|
||
}
|
||
|
||
/// <summary>
|
||
/// 取得即時趨勢常用組合
|
||
/// </summary>
|
||
/// <param name="postDevice"></param>
|
||
/// <returns></returns>
|
||
[HttpPost]
|
||
[Route("api/History/RealTimeCombination")]
|
||
public async Task<ActionResult<ApiResult<List<RealTimeCombination>>>> GetRealTimeCombination(PostRealTimeCombination post)
|
||
{
|
||
ApiResult<List<RealTimeCombination>> apiResult = new ApiResult<List<RealTimeCombination>>(jwt_str);
|
||
if (!jwtlife)
|
||
{
|
||
apiResult.Code = "5000";
|
||
return BadRequest(apiResult);
|
||
}
|
||
try
|
||
{
|
||
var sql = $@"
|
||
SELECT
|
||
rc.combination_guid,
|
||
rc.full_name AS combination_full_name,
|
||
rcd.device_guid,
|
||
d.device_number,
|
||
d.full_name AS Device_full_name,
|
||
b.full_name AS Building_full_name,
|
||
v1.system_key AS Main_system_full_name,
|
||
v2.system_key AS Sub_system_full_name
|
||
FROM realtime_combination_detail rcd
|
||
LEFT JOIN
|
||
(SELECT
|
||
rc.*
|
||
FROM realtime_combination rc
|
||
WHERE building_tag = @building_tag
|
||
AND userinfo_guid = @userinfo_guid
|
||
) rc ON rcd.combination_guid = rc.combination_guid
|
||
JOIN device d ON d.deleted = 0 AND d.device_guid = rcd.device_guid
|
||
JOIN building b ON b.deleted = 0 AND b.building_tag = d.device_building_tag
|
||
JOIN variable v1 ON v1.deleted = 0 AND v1.system_value = d.device_system_tag and v1.system_type = 'device_system_category_layer2'
|
||
JOIN variable v2 ON v2.deleted = 0 AND v2.system_value = d.device_name_tag and v2.system_type = 'device_system_category_layer3'
|
||
WHERE rc.deleted = 0";
|
||
|
||
var rawDatas = await frontendRepository.GetAllAsync<RealTimeCombinationRawData>(sql, new { building_tag = post.building_tag, userinfo_guid = myUser.userinfo_guid });
|
||
|
||
var rawDatas_groups = rawDatas.GroupBy(x => x.Combination_guid).ToList();
|
||
|
||
List<RealTimeCombination> realTimeCombinations = new List<RealTimeCombination>();
|
||
|
||
foreach (var rawDatas_group in rawDatas_groups)
|
||
{
|
||
RealTimeCombination realTimeCombination = new RealTimeCombination();
|
||
realTimeCombination.Combination_guid = rawDatas_group.Key;
|
||
realTimeCombination.Combination_full_name = rawDatas_group.First().Combination_full_name;
|
||
realTimeCombination.Details = new List<RealTimeCombinationDetail>();
|
||
|
||
foreach (var rawData in rawDatas_group)
|
||
{
|
||
RealTimeCombinationDetail realTimeCombinationDetail = new RealTimeCombinationDetail();
|
||
realTimeCombinationDetail.Combination_guid = rawData.Combination_guid;
|
||
realTimeCombinationDetail.Device_guid = rawData.Device_guid;
|
||
realTimeCombinationDetail.Device_number = rawData.Device_number;
|
||
realTimeCombinationDetail.Device_full_name = rawData.Device_full_name;
|
||
realTimeCombinationDetail.Building_full_name = rawData.Building_full_name;
|
||
realTimeCombinationDetail.Main_system_full_name = rawData.Main_system_full_name;
|
||
realTimeCombinationDetail.Sub_system_full_name = rawData.Sub_system_full_name;
|
||
|
||
realTimeCombination.Details.Add(realTimeCombinationDetail);
|
||
}
|
||
|
||
realTimeCombinations.Add(realTimeCombination);
|
||
}
|
||
|
||
apiResult.Data = realTimeCombinations;
|
||
apiResult.Code = "0000";
|
||
}
|
||
catch (Exception exception)
|
||
{
|
||
apiResult.Code = "9999";
|
||
apiResult.Msg = "系統內部錯誤，請聯絡管理者。";
|
||
Logger.LogError("【" + controllerName + "/" + actionName + "】" + exception.Message);
|
||
}
|
||
return Ok(apiResult);
|
||
}
|
||
|
||
/// <summary>
|
||
/// 新增常用組合(包含覆蓋、另存新檔)
|
||
/// </summary>
|
||
/// <param name="post"></param>
|
||
/// <returns></returns>
|
||
[HttpPost]
|
||
[Route("api/History/SaveRealTimeCombination")]
|
||
public async Task<ActionResult<ApiResult<string>>> SaveRealTimeCombination(PostSaveRealTimeCombination post)
|
||
{
|
||
ApiResult<string> apiResult = new ApiResult<string>(jwt_str);
|
||
if (!jwtlife)
|
||
{
|
||
apiResult.Code = "5000";
|
||
return BadRequest(apiResult);
|
||
}
|
||
try
|
||
{
|
||
if (post.Save_type == 1)
|
||
{ //另存新檔
|
||
|
||
//產生一組GUID
|
||
var guid = Guid.NewGuid(); //常用組合GUID
|
||
|
||
Dictionary<string, object> combination = new Dictionary<string, object>();
|
||
combination = new Dictionary<string, object>()
|
||
{
|
||
{ "@combination_guid", guid},
|
||
{ "@building_tag", post.Building_tag},
|
||
{ "@userinfo_guid", myUser.userinfo_guid},
|
||
{ "@full_name", post.Full_name},
|
||
{ "@created_by", myUser.userinfo_guid}
|
||
};
|
||
await frontendRepository.AddOneByCustomTable(combination, "realtime_combination");
|
||
|
||
List<Dictionary<string, object>> detailDics = new List<Dictionary<string, object>>();
|
||
foreach (var detail in post.Details)
|
||
{
|
||
Dictionary<string, object> detailDic = new Dictionary<string, object>()
|
||
{
|
||
{ "@combination_guid", guid},
|
||
{ "@device_guid", detail.Device_guid},
|
||
{ "@created_by", myUser.userinfo_guid}
|
||
};
|
||
|
||
detailDics.Add(detailDic);
|
||
}
|
||
|
||
await frontendRepository.AddMutiByCustomTable(detailDics, "realtime_combination_detail");
|
||
}
|
||
else
|
||
{ //覆蓋
|
||
var sWhere = $@"deleted = 0 AND combination_guid = @combination_guid";
|
||
|
||
var realTimeCombination = await frontendRepository.GetOneAsync<RealTimeCombination>("realtime_combination", sWhere, new { combination_guid = post.Combination_guid });
|
||
|
||
if (realTimeCombination == null)
|
||
{
|
||
apiResult.Code = "9996";
|
||
apiResult.Msg = "查無該常用組合";
|
||
|
||
return Ok(apiResult);
|
||
}
|
||
else
|
||
{
|
||
//刪除該組合的所有設備
|
||
await frontendRepository.PurgeOneAsync(realTimeCombination.Combination_guid, "realtime_combination_detail", "combination_guid");
|
||
|
||
//重新新增
|
||
List<Dictionary<string, object>> detailDics = new List<Dictionary<string, object>>();
|
||
foreach (var detail in post.Details)
|
||
{
|
||
Dictionary<string, object> detailDic = new Dictionary<string, object>()
|
||
{
|
||
{ "@combination_guid", realTimeCombination.Combination_guid},
|
||
{ "@device_guid", detail.Device_guid},
|
||
{ "@created_by", myUser.userinfo_guid}
|
||
};
|
||
|
||
detailDics.Add(detailDic);
|
||
}
|
||
|
||
await frontendRepository.AddMutiByCustomTable(detailDics, "realtime_combination_detail");
|
||
}
|
||
}
|
||
|
||
apiResult.Code = "0000";
|
||
}
|
||
catch (Exception exception)
|
||
{
|
||
apiResult.Code = "9999";
|
||
apiResult.Msg = "系統內部錯誤，請聯絡管理者。";
|
||
Logger.LogError("【" + controllerName + "/" + actionName + "】" + exception.Message);
|
||
}
|
||
return Ok(apiResult);
|
||
}
|
||
|
||
/// <summary>
|
||
/// 修改常用組合名稱
|
||
/// </summary>
|
||
/// <param name="post"></param>
|
||
/// <returns></returns>
|
||
[HttpPost]
|
||
[Route("api/History/EditRealTimeCombination")]
|
||
public async Task<ActionResult<ApiResult<string>>> EditRealTimeCombination(PostEditRealTimeCombination post)
|
||
{
|
||
ApiResult<string> apiResult = new ApiResult<string>(jwt_str);
|
||
if (!jwtlife)
|
||
{
|
||
apiResult.Code = "5000";
|
||
return BadRequest(apiResult);
|
||
}
|
||
try
|
||
{
|
||
var sWhere = $@"deleted = 0 AND combination_guid = @combination_guid";
|
||
|
||
var realTimeCombination = await frontendRepository.GetOneAsync<RealTimeCombination>("realtime_combination", sWhere, new { combination_guid = post.Combination_guid });
|
||
|
||
if (realTimeCombination == null)
|
||
{
|
||
apiResult.Code = "9996";
|
||
}
|
||
else
|
||
{
|
||
Dictionary<string, object> realTimeCombinationDic = new Dictionary<string, object>()
|
||
{
|
||
{ "@full_name", post.Full_name },
|
||
{ "@updated_by", myUser.userinfo_guid},
|
||
{ "@updated_at", DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss")}
|
||
};
|
||
|
||
await frontendRepository.UpdateOneByCustomTable(realTimeCombinationDic, "realtime_combination", "combination_guid = '" + post.Combination_guid + "'");
|
||
}
|
||
|
||
apiResult.Code = "0000";
|
||
}
|
||
catch (Exception exception)
|
||
{
|
||
apiResult.Code = "9999";
|
||
apiResult.Msg = "系統內部錯誤，請聯絡管理者。";
|
||
Logger.LogError("【" + controllerName + "/" + actionName + "】" + exception.Message);
|
||
}
|
||
return Ok(apiResult);
|
||
}
|
||
|
||
/// <summary>
|
||
/// 刪除常用組合名稱
|
||
/// </summary>
|
||
/// <param name="post"></param>
|
||
/// <returns></returns>
|
||
[HttpPost]
|
||
[Route("api/History/DeleteRealTimeCombination")]
|
||
public async Task<ActionResult<ApiResult<string>>> DeleteRealTimeCombination(string guid)
|
||
{
|
||
ApiResult<string> apiResult = new ApiResult<string>(jwt_str);
|
||
if (!jwtlife)
|
||
{
|
||
apiResult.Code = "5000";
|
||
return BadRequest(apiResult);
|
||
}
|
||
try
|
||
{
|
||
var sWhere = $@"deleted = 0 AND combination_guid = @combination_guid";
|
||
|
||
var realTimeCombination = await frontendRepository.GetOneAsync<RealTimeCombination>("realtime_combination", sWhere, new { combination_guid = guid });
|
||
|
||
if (realTimeCombination == null)
|
||
{
|
||
apiResult.Code = "9996";
|
||
}
|
||
else
|
||
{
|
||
await frontendRepository.DeleteOne(guid, "realtime_combination", "combination_guid");
|
||
}
|
||
|
||
apiResult.Code = "0000";
|
||
|
||
}
|
||
catch (Exception exception)
|
||
{
|
||
apiResult.Code = "9999";
|
||
apiResult.Msg = "系統內部錯誤，請聯絡管理者。";
|
||
Logger.LogError("【" + controllerName + "/" + actionName + "】" + exception.Message);
|
||
}
|
||
return Ok(apiResult);
|
||
}
|
||
|
||
|
||
//[HttpPost]
|
||
//[Route("api/History/GetHistoryMenu")]
|
||
//public async Task<ActionResult<ApiResult<List<History_Main_system>>>> GetHistoryMenu(string account)
|
||
//{
|
||
// ApiResult<List<History_Main_system>> apiResult = new ApiResult<List<History_Main_system>>(jwt_str);
|
||
// if (!jwtlife)
|
||
// {
|
||
// apiResult.Code = "5000";
|
||
// return BadRequest(apiResult);
|
||
// }
|
||
// try
|
||
// {
|
||
// var dbitems = await backendRepository.GetAllAsync<HistoryClass>(
|
||
// @$"select c.main_system_guid ,c.full_name main_name,b.sub_system_guid,b.full_name sub_name,e.building_guid,e.full_name building_name,a.device_item_guid,a.full_name item_name
|
||
// from device_item a
|
||
// join sub_system b on b.sub_system_guid = a.sub_system_guid
|
||
// join main_system c on c.main_system_guid = b.main_system_guid
|
||
// join auth_page d on d.ShowView = b.sub_system_guid
|
||
// join building e on e.building_guid = d.building_guid
|
||
// join role_auth f on f.AuthCode = d.AuthCode
|
||
// join userinfo g on g.role_guid = f.role_guid
|
||
// where g.account = '{account}' and a.deleted = 0");
|
||
|
||
// var dbdevice = await backendRepository.GetAllAsync<History_Sub_device>
|
||
// ("device", " deleted = 0");
|
||
|
||
// var main_systems = dbitems.GroupBy(a => a.main_system_guid).ToList();
|
||
// List<History_Main_system> History_Main_system = new List<History_Main_system>();
|
||
// foreach (var main_system in main_systems)
|
||
// {
|
||
// History_Main_system history_Main_System = new History_Main_system()
|
||
// {
|
||
// History_Sub_systems = new List<History_Sub_system>(),
|
||
// main_system_guid = main_system.Select(a => a.main_system_guid).FirstOrDefault(),
|
||
// full_name = main_system.Select(a => a.main_name).FirstOrDefault()
|
||
// };
|
||
// var sub_systems = main_system.GroupBy(a => a.sub_system_guid).ToList();
|
||
// foreach (var sub_system in sub_systems)
|
||
// {
|
||
// History_Sub_system history_Sub_System = new History_Sub_system()
|
||
// {
|
||
// sub_system_guid = sub_system.Select(a => a.sub_system_guid).FirstOrDefault(),
|
||
// full_name = sub_system.Select(a => a.sub_name).FirstOrDefault(),
|
||
// History_Builds = new List<History_Build>()
|
||
// };
|
||
// var builds = sub_system.GroupBy(a => a.building_guid).ToList();
|
||
// foreach (var build in builds)
|
||
// {
|
||
// History_Build history_Build = new History_Build()
|
||
// {
|
||
// building_guid = build.Select(a => a.building_guid).First(),
|
||
// full_name = build.Select(a => a.building_name).FirstOrDefault(),
|
||
// History_Devices = new List<History_Device>()
|
||
// };
|
||
// var devices = dbdevice.Where(a => a.building_guid == history_Build.building_guid & a.sub_system_guid == history_Sub_System.sub_system_guid).ToList();
|
||
// foreach (var device in devices)
|
||
// {
|
||
// History_Device history_Device = new History_Device()
|
||
// {
|
||
// device_guid = device.device_guid,
|
||
// full_name = device.full_name,
|
||
// History_Deviceitems = new List<History_Deviceitem>()
|
||
// };
|
||
// var items = build.GroupBy(a => a.device_item_guid).ToList();
|
||
// foreach (var item in items)
|
||
// {
|
||
// History_Deviceitem history_Deviceitem = new History_Deviceitem()
|
||
// {
|
||
// device_item_guid = item.Select(a => a.device_item_guid).FirstOrDefault(),
|
||
// full_name = item.Select(a => a.item_name).FirstOrDefault()
|
||
// };
|
||
// history_Device.History_Deviceitems.Add(history_Deviceitem);
|
||
// }
|
||
// history_Build.History_Devices.Add(history_Device);
|
||
// }
|
||
// history_Sub_System.History_Builds.Add(history_Build);
|
||
// }
|
||
// history_Main_System.History_Sub_systems.Add(history_Sub_System);
|
||
// }
|
||
// History_Main_system.Add(history_Main_System);
|
||
// }
|
||
// apiResult.Code = "0000";
|
||
// apiResult.Data = History_Main_system;
|
||
// }
|
||
// catch (Exception exception)
|
||
// {
|
||
// apiResult.Code = "9999";
|
||
// Logger.LogError("【" + controllerName + "/" + actionName + "】" + exception.Message);
|
||
// return Ok(apiResult);
|
||
// }
|
||
// return Ok(apiResult);
|
||
//}
|
||
|
||
/// <summary>
|
||
/// 歷史資料查詢過濾條件面板
|
||
/// </summary>
|
||
/// <param name="post"></param>
|
||
/// <returns></returns>
|
||
[HttpPost]
|
||
[Route("api/History/GetHistoryFilterPanel")]
|
||
public async Task<ActionResult<ApiResult<History_MainSubBuildFloor>>> GetHistoryFilterPanel(PostHistoryFilterPanel post)
|
||
{
|
||
ApiResult<History_MainSubBuildFloor> apiResult = new ApiResult<History_MainSubBuildFloor>(jwt_str);
|
||
if (!jwtlife)
|
||
{
|
||
apiResult.Code = "5000";
|
||
return BadRequest(apiResult);
|
||
}
|
||
try
|
||
{
|
||
#region 取得區域樓層
|
||
var sqlBuildingFloor = $@"SELECT
|
||
b.building_tag,
|
||
b.full_name AS building_name,
|
||
b.priority,
|
||
f.floor_guid,
|
||
f.full_name AS floor_name,
|
||
f.priority
|
||
FROM building b
|
||
JOIN floor f ON f.deleted = 0 AND f.building_tag = b.building_tag
|
||
WHERE b.building_tag IN (
|
||
SELECT
|
||
ap.building_tag
|
||
FROM role_auth ra
|
||
JOIN auth_page ap ON ra.AuthCode = ap.AuthCode
|
||
JOIN userinfo u ON u.role_guid = ra.role_guid WHERE u.account = @Account
|
||
)
|
||
ORDER BY b.priority, f.priority";
|
||
|
||
var buildingFloorRawDatas = await frontendRepository.GetAllAsync<BuildingFloorRawData>(sqlBuildingFloor, post);
|
||
|
||
var buildingFloorRawDatas_GroupBy_building_guid = buildingFloorRawDatas.GroupBy(x => x.building_tag).ToList();
|
||
|
||
List<History_Build> history_Builds = new List<History_Build>();
|
||
foreach (var buildingFloorRawData in buildingFloorRawDatas_GroupBy_building_guid)
|
||
{
|
||
List<History_Floor> history_Floors = new List<History_Floor>();
|
||
foreach (var floorRawData in buildingFloorRawData)
|
||
{
|
||
History_Floor history_Floor = new History_Floor()
|
||
{
|
||
floor_guid = floorRawData.floor_guid,
|
||
full_name = floorRawData.floor_name,
|
||
};
|
||
|
||
history_Floors.Add(history_Floor);
|
||
}
|
||
|
||
History_Build history_Build = new History_Build()
|
||
{
|
||
building_tag = buildingFloorRawData.Key,
|
||
full_name = buildingFloorRawData.First().building_name,
|
||
history_Floors = history_Floors
|
||
};
|
||
|
||
history_Builds.Add(history_Build);
|
||
}
|
||
#endregion
|
||
|
||
#region 取得系統大小類
|
||
var sqlMainSubSystem = $@"select
|
||
v1.system_value as main_system_tag, v1.system_key as main_name, v2.system_value as sub_system_tag, v2.system_key as sub_name
|
||
from variable v2
|
||
inner join variable v1 on v2.system_parent_id = v1.id
|
||
where v2.deleted = 0 and
|
||
v2.id in (
|
||
select ap.ShowView
|
||
from role_auth ra
|
||
inner join auth_page ap on ra.AuthCode = ap.AuthCode
|
||
inner join userinfo ui on ra.role_guid = ui.role_guid
|
||
where ui.account = @Account
|
||
)
|
||
order by v1.system_priority, v2.system_priority;";
|
||
|
||
var mainSubSystemRawDatas = await frontendRepository.GetAllAsync<HistoryDBMainSub>(sqlMainSubSystem, post);
|
||
|
||
var mainSubSystemRawDatas_GroupBy_main_system_guid = mainSubSystemRawDatas.GroupBy(x => x.main_system_tag).ToList();
|
||
|
||
List<History_Main_system> history_Main_Systems = new List<History_Main_system>();
|
||
foreach (var mainSubSystemRawData in mainSubSystemRawDatas_GroupBy_main_system_guid)
|
||
{
|
||
List<History_Sub_system> history_Sub_Systems = new List<History_Sub_system>();
|
||
foreach (var SubSystemRawData in mainSubSystemRawData)
|
||
{
|
||
History_Sub_system history_Sub_System = new History_Sub_system()
|
||
{
|
||
sub_system_tag = SubSystemRawData.sub_system_tag,
|
||
full_name = SubSystemRawData.sub_name,
|
||
};
|
||
|
||
history_Sub_Systems.Add(history_Sub_System);
|
||
}
|
||
|
||
History_Main_system history_Main_System = new History_Main_system()
|
||
{
|
||
main_system_tag = mainSubSystemRawData.Key,
|
||
full_name = mainSubSystemRawData.First().main_name,
|
||
History_Sub_systems = history_Sub_Systems
|
||
};
|
||
|
||
history_Main_Systems.Add(history_Main_System);
|
||
}
|
||
#endregion
|
||
|
||
History_MainSubBuildFloor history_MainSubBuildFloor = new History_MainSubBuildFloor()
|
||
{
|
||
history_Builds = history_Builds,
|
||
history_Main_Systems = history_Main_Systems
|
||
};
|
||
|
||
apiResult.Code = "0000";
|
||
apiResult.Data = history_MainSubBuildFloor;
|
||
|
||
}
|
||
catch (Exception exception)
|
||
{
|
||
apiResult.Code = "9999";
|
||
apiResult.Msg = "系統內部錯誤，請聯絡管理者。";
|
||
Logger.LogError("【" + controllerName + "/" + actionName + "】" + exception.Message);
|
||
return Ok(apiResult);
|
||
}
|
||
return Ok(apiResult);
|
||
}
|
||
|
||
/// <summary>
|
||
/// 取得歷史資料
|
||
/// </summary>
|
||
/// <param name="post"></param>
|
||
/// <returns></returns>
|
||
[HttpPost]
|
||
[Route("api/History/GetHistoryRawData")]
|
||
public async Task<ActionResult<ApiResult<List<HistoryRawData>>>> GetHistoryRawData(PostHistoryRawDataFilter post)
|
||
{
|
||
ApiResult<List<HistoryRawData>> apiResult = new ApiResult<List<HistoryRawData>>(jwt_str);
|
||
if (!jwtlife)
|
||
{
|
||
apiResult.Code = "5000";
|
||
return BadRequest(apiResult);
|
||
}
|
||
try
|
||
{
|
||
EDFunction ed = new EDFunction();
|
||
var obixApiConfig = new ObixApiConfig();
|
||
|
||
var sqlObix = $@"SELECT system_value as Value, system_key as Name FROM variable WHERE deleted = 0 AND system_type = 'obixConfig'";
|
||
|
||
var variable = await frontendRepository.GetAllAsync<KeyValue>(sqlObix);
|
||
obixApiConfig.ApiBase = variable.Where(x => x.Name == "ApiBase").Select(x => x.Value).FirstOrDefault();
|
||
obixApiConfig.UserName = ed.AESDecrypt(variable.Where(x => x.Name == "UserName").Select(x => x.Value).FirstOrDefault());
|
||
obixApiConfig.Password = ed.AESDecrypt(variable.Where(x => x.Name == "Password").Select(x => x.Value).FirstOrDefault());
|
||
|
||
String encoded = System.Convert.ToBase64String(System.Text.Encoding.GetEncoding("ISO-8859-1").GetBytes(obixApiConfig.UserName + ":" + obixApiConfig.Password));
|
||
|
||
//找出被選擇的設備編號
|
||
var device_numbers = post.HistoryItems.Select(x => x.Device_number_point.Split(":")[0]).Distinct().ToList();
|
||
|
||
//依據被選擇的設備找出相對應資料
|
||
var sqlDeviceItemInfo = $@"select
|
||
b.full_name as building_name,
|
||
v1.system_key as Main_system_name,
|
||
v2.system_key as Sub_system_name,
|
||
temp.device_number,
|
||
temp.device_name,
|
||
temp.full_name as item_name,
|
||
temp.points,
|
||
temp.unit
|
||
from (
|
||
select
|
||
di.*,
|
||
d.device_number,
|
||
d.full_name as device_name,
|
||
d.priority
|
||
from device_item di
|
||
inner join device d on di.device_building_tag COLLATE utf8mb4_unicode_ci = d.device_building_tag
|
||
and di.device_system_tag COLLATE utf8mb4_unicode_ci = d.device_system_tag
|
||
and di.device_name_tag COLLATE utf8mb4_unicode_ci = d.device_name_tag
|
||
and d.deleted = 0
|
||
where di.deleted = 0 and di.unit is not null and d.device_number IN @Device_number
|
||
) temp
|
||
inner join building b on temp.device_building_tag COLLATE utf8mb4_unicode_ci = b.building_tag and b.deleted = 0
|
||
inner join variable v1 on temp.device_system_tag COLLATE utf8mb4_unicode_ci = v1.system_value and v1.deleted = 0 and v1.system_type = 'device_system_category_layer2'
|
||
inner join variable v2 on temp.device_name_tag COLLATE utf8mb4_unicode_ci = v2.system_value and v2.deleted = 0 and v2.system_type = 'device_system_category_layer3'
|
||
order by b.priority, v1.system_priority, v2.system_priority, temp.priority;";
|
||
|
||
var device_item_infos = await frontendRepository.GetAllAsync<DeviceItemInfo>(sqlDeviceItemInfo, new { Device_number = device_numbers });
|
||
|
||
//轉換日期格式
|
||
var start = string.Format("{0}T00:00:00.000+08:00", post.Start_timestamp);
|
||
var end = string.Format("{0}T23:59:59.000+08:00", post.End_timestamp);
|
||
|
||
var historyQueryFilter = $@"<obj is='obix: HistoryFilter'>
|
||
<abstime name = 'start' val = '{start}' />
|
||
<abstime name = 'end' val = '{end}' />
|
||
</obj>";
|
||
|
||
XmlDocument xmlDocument = new XmlDocument();
|
||
List<HistoryRawData> historyRawDatas = new List<HistoryRawData>();
|
||
List<string> errorPoints = new List<string>(); //錯誤的設備點位
|
||
foreach (var historyItem in post.HistoryItems)
|
||
{
|
||
var device_number_point = historyItem.Device_number_point.Replace(":", "_");
|
||
|
||
HttpWebRequest request = (HttpWebRequest)WebRequest.Create($"{obixApiConfig.ApiBase}obix/histories/FIC_Center/{device_number_point}/~historyQuery/");
|
||
request.Method = "POST";
|
||
request.Headers.Add("Authorization", "Basic " + encoded);
|
||
request.PreAuthenticate = true;
|
||
|
||
byte[] byteArray = Encoding.UTF8.GetBytes(historyQueryFilter);
|
||
using (Stream reqStream = request.GetRequestStream())
|
||
{
|
||
reqStream.Write(byteArray, 0, byteArray.Length);
|
||
}
|
||
|
||
HttpWebResponse response = (HttpWebResponse)request.GetResponse();
|
||
var responseContent = new StreamReader(response.GetResponseStream()).ReadToEnd();
|
||
|
||
xmlDocument.LoadXml(responseContent);
|
||
string responseContentJson = JsonConvert.SerializeXmlNode(xmlDocument);
|
||
JObject responseContentJsonResult = (JObject)JsonConvert.DeserializeObject(responseContentJson);
|
||
|
||
if (responseContentJsonResult.ContainsKey("err")) //抓取錯誤
|
||
{
|
||
errorPoints.Add(device_number_point);
|
||
continue;
|
||
}
|
||
|
||
if (responseContentJsonResult.ContainsKey("obj")) //表示可以讀取到內容
|
||
{
|
||
var countObj = responseContentJsonResult["obj"]["int"];
|
||
if (countObj != null && countObj["@name"].ToString() == "count")
|
||
{ //判斷是否有資料筆數
|
||
if (Convert.ToInt32(countObj["@val"].ToString()) > 0)
|
||
{
|
||
var dataList = responseContentJsonResult["obj"]["list"];
|
||
if (dataList != null && dataList["obj"].HasValues)
|
||
{
|
||
//讀取資料
|
||
foreach (var obj in dataList["obj"])
|
||
{
|
||
HistoryRawData historyRawData = new HistoryRawData();
|
||
|
||
//取得設備基本資訊
|
||
var tempSplit = historyItem.Device_number_point.Split(":");
|
||
var device_number = tempSplit[0];
|
||
var point = tempSplit[1];
|
||
var device_item_info = device_item_infos.Where(x => x.Device_number == device_number && x.Points == point).FirstOrDefault();
|
||
|
||
historyRawData.Building_name = device_item_info.Building_name;
|
||
historyRawData.Main_system_name = device_item_info.Main_system_name;
|
||
historyRawData.Sub_system_name = device_item_info.Sub_system_name;
|
||
historyRawData.Device_number = device_item_info.Device_number;
|
||
historyRawData.Device_name = device_item_info.Device_name;
|
||
historyRawData.Item_name = device_item_info.Item_name;
|
||
historyRawData.Points = device_item_info.Points;
|
||
historyRawData.Unit = device_item_info.Unit;
|
||
|
||
//取得時間
|
||
var abstime = obj["abstime"]["@val"].ToString();
|
||
historyRawData.Timestamp = Convert.ToDateTime(abstime).ToString("yyyy-MM-dd HH:mm:ss");
|
||
//取得資料
|
||
historyRawData.Value = obj["real"]["@val"].ToString();
|
||
|
||
historyRawDatas.Add(historyRawData);
|
||
}
|
||
}
|
||
}
|
||
}
|
||
}
|
||
}
|
||
|
||
if (errorPoints.Count() > 0)
|
||
{
|
||
apiResult.Code = "9998";
|
||
apiResult.Msg = String.Join(",", errorPoints);
|
||
|
||
return Ok(apiResult);
|
||
}
|
||
|
||
apiResult.Code = "0000";
|
||
apiResult.Data = historyRawDatas;
|
||
}
|
||
catch (Exception exception)
|
||
{
|
||
apiResult.Code = "9999";
|
||
apiResult.Msg = "系統內部錯誤，請聯絡管理者。";
|
||
Logger.LogError("【" + controllerName + "/" + actionName + "】" + exception.ToString());
|
||
return Ok(apiResult);
|
||
}
|
||
return Ok(apiResult);
|
||
}
|
||
|
||
/// <summary>
|
||
/// 即使歷史資料(前7天)
|
||
/// </summary>
|
||
/// <param name="input"></param>
|
||
/// <returns></returns>
|
||
[HttpPost]
|
||
[Route("api/HistoryRealTime")]
|
||
public async Task<ActionResult<ApiResult<List<HistoryRawData>>>> GetHistoryRealTime(PostHistoryRawDataFilter input)
|
||
{
|
||
ApiResult<List<HistoryRawData>> apiResult = new ApiResult<List<HistoryRawData>>(jwt_str);
|
||
apiResult.Data = new List<HistoryRawData>();
|
||
if (!jwtlife)
|
||
{
|
||
apiResult.Code = "5000";
|
||
return BadRequest(apiResult);
|
||
}
|
||
if (input.HistoryItems.Count == 0)
|
||
{
|
||
apiResult.Code = "9998";
|
||
apiResult.Msg = "沒有設備被選擇";
|
||
return BadRequest(apiResult);
|
||
}
|
||
|
||
try
|
||
{
|
||
//依據被選擇的設備找出相對應資料
|
||
var sqlDeviceItemInfo = $@"select
|
||
b.full_name as building_name,
|
||
v1.system_key as Main_system_name,
|
||
v2.system_key as Sub_system_name,
|
||
temp.device_number,
|
||
temp.device_name,
|
||
temp.full_name as item_name,
|
||
temp.points,
|
||
temp.unit
|
||
from (
|
||
select
|
||
di.*,
|
||
d.device_number,
|
||
d.full_name as device_name,
|
||
d.priority
|
||
from device_item di
|
||
inner join device d on di.device_building_tag COLLATE utf8mb4_unicode_ci = d.device_building_tag
|
||
and di.device_system_tag COLLATE utf8mb4_unicode_ci = d.device_system_tag
|
||
and di.device_name_tag COLLATE utf8mb4_unicode_ci = d.device_name_tag
|
||
and d.deleted = 0
|
||
where di.deleted = 0 and di.unit is not null and d.device_number = @Device_number
|
||
) temp
|
||
inner join building b on temp.device_building_tag COLLATE utf8mb4_unicode_ci = b.building_tag and b.deleted = 0
|
||
inner join variable v1 on temp.device_system_tag COLLATE utf8mb4_unicode_ci = v1.system_value and v1.deleted = 0 and v1.system_type = 'device_system_category_layer2'
|
||
inner join variable v2 on temp.device_name_tag COLLATE utf8mb4_unicode_ci = v2.system_value and v2.deleted = 0 and v2.system_type = 'device_system_category_layer3'
|
||
order by b.priority, v1.system_priority, v2.system_priority, temp.priority;";
|
||
|
||
foreach (var hi in input.HistoryItems)
|
||
{
|
||
var device_number = hi.Device_number_point.Split(":")[0];
|
||
var point = hi.Device_number_point.Split(":")[1];
|
||
var tableName = await backgroundServiceMsSqlRepository.GetOneAsync<string>($"select table_name from INFORMATION_SCHEMA.TABLES where table_name like '%{device_number}_{point}%'");
|
||
if (!string.IsNullOrEmpty(tableName))
|
||
{
|
||
var device_item_infos = await frontendRepository.GetAllAsync<DeviceItemInfo>(sqlDeviceItemInfo, new { Device_number = device_number });
|
||
|
||
var device_item_info = device_item_infos.Where(x => x.Device_number == device_number && x.Points == point).FirstOrDefault();
|
||
|
||
var sql = $@"select '{device_item_info.Building_name}' as Building_name, '{device_item_info.Main_system_name}' as Main_system_name,
|
||
'{device_item_info.Sub_system_name}' as Sub_system_name, '{device_item_info.Device_number}' as Device_number,
|
||
'{device_item_info.Device_name}' as Device_name, '{device_item_info.Item_name}' as Item_name, '{device_item_info.Points}' as Points,
|
||
'{device_item_info.Unit}' as Unit, timestamp as Timestamp, round(value, 2) as Value
|
||
from FIC_CENTER_{device_number}_{point}
|
||
where replace(convert(varchar, [timestamp], 111), '/', '-') >= @startTime
|
||
and replace(convert(varchar, [timestamp], 111), '/', '-') <= @endTime order by timestamp";
|
||
apiResult.Data.AddRange(
|
||
await backgroundServiceMsSqlRepository.GetAllAsync<HistoryRawData>(sql, new { startTime = input.Start_timestamp, endTime = input.End_timestamp })
|
||
);
|
||
}
|
||
//foreach (var tn in tableName)
|
||
//{
|
||
//}
|
||
}
|
||
apiResult.Code = "0000";
|
||
apiResult.Data = apiResult.Data.OrderBy(x => x.Device_number).ThenBy(x => x.Timestamp).ToList();
|
||
}
|
||
catch (Exception exception)
|
||
{
|
||
apiResult.Code = "9999";
|
||
apiResult.Msg = "系統內部錯誤，請聯絡管理者。";
|
||
Logger.LogError("【" + controllerName + "/" + actionName + "】" + exception.Message);
|
||
return Ok(apiResult);
|
||
}
|
||
return Ok(apiResult);
|
||
}
|
||
|
||
private List<Dictionary<string, object>> ArrangeRawData(DeviceNumberPoint deviceNumberPoint, JObject jsonResult)
|
||
{
|
||
List<Dictionary<string, object>> arrangeRawDatas = new List<Dictionary<string, object>>();
|
||
var histories = jsonResult["obj"]["list"]["obj"];
|
||
var rawdateCount = Convert.ToInt32(jsonResult["obj"]["int"]["@val"].ToString());
|
||
|
||
if (rawdateCount == 0)
|
||
{
|
||
return null;
|
||
}
|
||
|
||
if (histories != null && histories.HasValues)
|
||
{
|
||
if (rawdateCount > 1)
|
||
{ //多筆資料
|
||
foreach (var history in histories)
|
||
{
|
||
Dictionary<string, object> arrangeRawData = new Dictionary<string, object>();
|
||
arrangeRawData.Add("@device_number", deviceNumberPoint.DeviceNumber);
|
||
arrangeRawData.Add("@point", deviceNumberPoint.Point);
|
||
|
||
//時間
|
||
if (history["abstime"] != null && history["abstime"].HasValues)
|
||
{
|
||
foreach (var abstime in history["abstime"])
|
||
{
|
||
var name = abstime["@name"].ToString();
|
||
switch (name)
|
||
{
|
||
case "start":
|
||
var startTimstamp = Convert.ToDateTime(abstime["@val"].ToString()).ToString("yyyy-MM-dd HH:mm:ss");
|
||
arrangeRawData.Add("@start_timestamp", startTimstamp);
|
||
break;
|
||
case "end":
|
||
var endTimstamp = Convert.ToDateTime(abstime["@val"].ToString()).ToString("yyyy-MM-dd HH:mm:ss");
|
||
arrangeRawData.Add("@end_timestamp", endTimstamp);
|
||
break;
|
||
}
|
||
}
|
||
}
|
||
|
||
//區間內資料筆數
|
||
if (history["int"] != null && history["int"].HasValues)
|
||
{
|
||
var count = Convert.ToInt32(history["int"]["@val"].ToString());
|
||
arrangeRawData.Add("@count_rawdata", count);
|
||
}
|
||
|
||
//整合數值(最大、最小、平均、總和)
|
||
if (history["real"] != null && history["real"].HasValues)
|
||
{
|
||
foreach (var real in history["real"])
|
||
{
|
||
var name = real["@name"].ToString();
|
||
switch (name)
|
||
{
|
||
case "min":
|
||
var min = real["@val"].ToString() == "NaN" ? -1 : Decimal.Parse(real["@val"].ToString(), System.Globalization.NumberStyles.Float);
|
||
arrangeRawData.Add("@min_rawdata", min);
|
||
break;
|
||
case "max":
|
||
var max = real["@val"].ToString() == "NaN" ? -1 : Decimal.Parse(real["@val"].ToString(), System.Globalization.NumberStyles.Float);
|
||
arrangeRawData.Add("@max_rawdata", max);
|
||
break;
|
||
case "avg":
|
||
var avg = real["@val"].ToString() == "NaN" ? -1 : Decimal.Parse(real["@val"].ToString(), System.Globalization.NumberStyles.Float);
|
||
arrangeRawData.Add("@avg_rawdata", avg);
|
||
break;
|
||
case "sum":
|
||
var sum = real["@val"].ToString() == "NaN" ? -1 : Decimal.Parse(real["@val"].ToString(), System.Globalization.NumberStyles.Float);
|
||
arrangeRawData.Add("@sum_rawdata", sum);
|
||
break;
|
||
}
|
||
}
|
||
}
|
||
arrangeRawData.Add("@is_complete", 1);
|
||
arrangeRawData.Add("@repeat_times", 0);
|
||
arrangeRawData.Add("@fail_reason", null);
|
||
arrangeRawData.Add("@updated_at", DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"));
|
||
|
||
arrangeRawDatas.Add(arrangeRawData);
|
||
}
|
||
}
|
||
else
|
||
{ //單筆資料
|
||
Dictionary<string, object> arrangeRawData = new Dictionary<string, object>();
|
||
arrangeRawData.Add("@device_number", deviceNumberPoint.DeviceNumber);
|
||
arrangeRawData.Add("@point", deviceNumberPoint.Point);
|
||
|
||
//時間
|
||
if (histories["obj"]["abstime"] != null && histories["obj"]["abstime"].HasValues)
|
||
{
|
||
foreach (var abstime in histories["obj"]["abstime"])
|
||
{
|
||
var name = abstime["@name"].ToString();
|
||
switch (name)
|
||
{
|
||
case "start":
|
||
var startTimstamp = Convert.ToDateTime(abstime["@val"].ToString()).ToString("yyyy-MM-dd HH:mm:ss");
|
||
arrangeRawData.Add("@start_timestamp", startTimstamp);
|
||
break;
|
||
case "end":
|
||
var endTimstamp = Convert.ToDateTime(abstime["@val"].ToString()).ToString("yyyy-MM-dd HH:mm:ss");
|
||
arrangeRawData.Add("@end_timestamp", endTimstamp);
|
||
break;
|
||
}
|
||
}
|
||
}
|
||
|
||
//區間內資料筆數
|
||
if (histories["obj"]["int"] != null && histories["obj"]["int"].HasValues)
|
||
{
|
||
var count = Convert.ToInt32(histories["obj"]["int"]["@val"].ToString());
|
||
arrangeRawData.Add("@count_rawdata", count);
|
||
}
|
||
|
||
//整合數值(最大、最小、平均、總和)
|
||
if (histories["obj"]["real"] != null && histories["obj"]["real"].HasValues)
|
||
{
|
||
foreach (var real in histories["obj"]["real"])
|
||
{
|
||
var name = real["@name"].ToString();
|
||
switch (name)
|
||
{
|
||
case "min":
|
||
var min = real["@val"].ToString() == "NaN" ? -1 : Decimal.Parse(real["@val"].ToString(), System.Globalization.NumberStyles.Float);
|
||
arrangeRawData.Add("@min_rawdata", min);
|
||
break;
|
||
case "max":
|
||
var max = real["@val"].ToString() == "NaN" ? -1 : Decimal.Parse(real["@val"].ToString(), System.Globalization.NumberStyles.Float);
|
||
arrangeRawData.Add("@max_rawdata", max);
|
||
break;
|
||
case "avg":
|
||
var avg = real["@val"].ToString() == "NaN" ? -1 : Decimal.Parse(real["@val"].ToString(), System.Globalization.NumberStyles.Float);
|
||
arrangeRawData.Add("@avg_rawdata", avg);
|
||
break;
|
||
case "sum":
|
||
var sum = real["@val"].ToString() == "NaN" ? -1 : Decimal.Parse(real["@val"].ToString(), System.Globalization.NumberStyles.Float);
|
||
arrangeRawData.Add("@sum_rawdata", sum);
|
||
break;
|
||
}
|
||
}
|
||
}
|
||
arrangeRawData.Add("@is_complete", 1);
|
||
arrangeRawData.Add("@repeat_times", 0);
|
||
arrangeRawData.Add("@fail_reason", null);
|
||
arrangeRawData.Add("@updated_at", DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"));
|
||
|
||
arrangeRawDatas.Add(arrangeRawData);
|
||
}
|
||
}
|
||
|
||
return arrangeRawDatas;
|
||
}
|
||
}
|
||
}
|