ibms-dome/FrontendWebApi/ApiControllers/HistoryController.cs

using FrontendWebApi.Models;
using Microsoft.AspNetCore.Mvc;
using Microsoft.Extensions.Logging;
using Microsoft.Extensions.Options;
using Newtonsoft.Json;
using Newtonsoft.Json.Linq;
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 NPOI.XSSF.UserModel;
using NPOI.SS.UserModel;

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

            var 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");
            var fileName = "歷史資料_" + fileDateName + ".xlsx";
            IWorkbook 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 = '{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.building_tag}'").Result;
                #endregion

                #region combine device and point
                List<DeviceNumberPoint> listDevicePoint = new List<DeviceNumberPoint>();
                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
                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='PT15M' />
                                            </obj>";

                List<HistoryExport> he = new List<HistoryExport>();
                List<JsonDevice> jd = new List<JsonDevice>();

                if (listDevicePoint.Count > 0)
                {
                    foreach (var d in listDevicePoint)
                    {
                        var archiveRequest = (HttpWebRequest)WebRequest.Create($"{obixApiConfig.ApiBase}obix/histories/{lhe.deviceComName}/{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 = lhe.building_tag;
                            jd.Add(jsonDevice);
                        }
                    }
                }

                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();
                                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($"{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)
                        {
                            lastDeviceItem = d.type;
                            sheet = workbook.CreateSheet($"{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 = 0;
                        }
                        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);
        }

        [HttpPost]
        [Route("api/ExportCSV")]
        public async Task<IActionResult> ExportCSV(PostHistoryRawDataFilter input)
        {
            ApiResult<string> apiResult = new ApiResult<string>();

            try
            {
                input.EnableLimit = false;  // CSV關閉5W筆限制
                var apiData = await GetHistoryRealTime(input);
                if (apiData.Value == null)
                {
                    var msg = new { Code = "0003", Msg = "無資料可匯出。" };
                    return StatusCode(400, msg);
                }
                var rawData = apiData.Value.Data;
                var FileName = $"歷史資料_{DateTime.Now}.csv";
                // 生成CSV文件
                string Csv = null;

                if (rawData.Count > 0)
                {
                    StringBuilder csv = new StringBuilder();

                    // 添加CSV標題行
                    csv.AppendLine("區域,系統大類,系統小類,設備編號,設備名稱,設備項目,數值,紀錄時間");

                    // 添加數據行
                    foreach (var item in rawData)
                    {
                        csv.AppendLine($"{item.Building_name},{item.Main_system_name},{item.Sub_system_name},{item.Device_number},{item.Item_name},{item.Points},{item.Value},{item.Timestamp}");
                    }
                    Csv = csv.ToString();
                }

                // 返回CSV文件
                if (Csv != null)
                {
                    using (var electricMemoryStream = new MemoryStream())
                    {
                        using (var streamWriter = new StreamWriter(electricMemoryStream, Encoding.UTF8))
                        {
                            streamWriter.Write(Csv);
                        }
                        return File(electricMemoryStream.ToArray(), "text/csv", FileName);
                    }
                }
                else
                {
                    var msg = new { Code = "0003", Msg = "無資料可匯出。" };
                    return StatusCode(400, msg);
                }
            }
            catch (Exception exception)
            {
                apiResult.Code = "9999";
                apiResult.Msg = "系統內部錯誤，請聯絡管理者。 Msg: " + exception.Message;
                Logger.LogError("【" + controllerName + "/" + actionName + "】" + exception.Message);
                return Json(apiResult);
            }
        }


        /// <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 (
	                        select distinct device_system_tag, device_name_tag, device_serial_tag, device_number, full_name from device d where d.deleted = 0 and  d.visible = 1 and d.is_link = 1 limit 1000000
                        ) d on v1.system_value = d.device_system_tag and v2.system_value = d.device_name_tag
                        join (
	                        select distinct device_system_tag, device_name_tag, is_show_history from device_item where deleted = 0 and is_link = 1 and is_show_history = 1
                        ) di on d.device_system_tag=di.device_system_tag and d.device_name_tag=di.device_name_tag
                        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 });
                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 b.ShowView = 'DataAnalyst'
                        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 = 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 and d.is_link = 1");
                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 DISTINCT
                                a.device_guid,
                                c.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 device_item b on b.device_system_tag = a.device_system_tag 
																			and b.device_building_tag COLLATE utf8mb4_unicode_ci = a.device_building_tag 
																			and b.device_name_tag COLLATE utf8mb4_unicode_ci = a.device_name_tag
                            join building c on c.building_tag = a.device_building_tag
							join import_niagara_item_history h on a.device_number = h.device_number
                            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'
														where a.deleted = 0 and b.deleted = 0 and v2.deleted = 0 and v1.deleted = 0 and b.is_link = 1 and b.is_show_history = 1
                            order by c.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="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 import_niagara_item_history h on b.device_number = h.device_number  and a.points = h.device_point_name 
                        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 b.device_number in @Device_number and a.is_link = 1 and a.is_show_history = 1
                        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 distinct d.building_tag,
                                            d.full_name AS building_name,
                                            d.priority,
                                            f.floor_guid,
                                            f.full_name AS floor_name,
                                            f.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
                        JOIN floor f ON f.deleted = 0 AND f.building_tag = b.building_tag
                        where c.account = @account and b.ShowView = 'HistorySearch'
                        order by d.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 
                                            join variable v1 on v2.system_parent_id = v1.id
																						join (SELECT DISTINCT a.device_system_tag,a.device_name_tag
																						from device a 
																						join device_item b on b.device_system_tag = a.device_system_tag 
																			and b.device_building_tag COLLATE utf8mb4_unicode_ci = a.device_building_tag 
																			and b.device_name_tag COLLATE utf8mb4_unicode_ci = a.device_name_tag
																			      where a.deleted = 0 and b.deleted = 0 and a.is_link = 1 and b.is_link = 1
																						and b.is_show_history = 1) d on d.device_system_tag = v1.system_value and d.device_name_tag = v2.system_value
                                            where v2.deleted = 0 and v1.deleted = 0 and
                                            v2.id in (
	                                            select ap.ShowView
	                                            from role_auth ra
	                                            join auth_page ap on ra.AuthCode = ap.AuthCode
	                                            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>
        /// 取得歷史資料(從Niagara拿資料)
        /// </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,
                                                        temp.is_bool
		                                            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 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 });
                string tagQuantity = await frontendRepository.GetOneAsync<string>("select system_value from variable where system_type = 'obixConfig' and system_key = 'tag_quantity' and deleted = 0;");
                string station = string.Empty;

                //轉換日期格式
                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(":", "_");

                    if (tagQuantity.Equals("5"))
                    {
                        //if (device_number_point.Split("_")[1].Equals("S") || device_number_point.Split("_")[1].Equals("CWB") || device_number_point.Split("_")[1].Equals("B") )
                        //{
                        //    station = await frontendRepository.GetOneAsync<string>($@"select parent_path from import_niagara_item_history
                        //                        where device_building_tag = '{device_number_point.Split("_")[0]}' and device_floor_tag = '{device_number_point.Split("_")[2]}' 
                        //                        and device_name_tag = '{device_number_point.Split("_")[3]}' and device_point_name = '{device_number_point.Split("_")[5]}'");
                        //}
                        //else
                        //{
                        //    station = await frontendRepository.GetOneAsync<string>($@"select parent_path from import_niagara_item_history
                        //                        where device_building_tag = '{device_number_point.Split("_")[0]}' and device_floor_tag = '{device_number_point.Split("_")[2]}' 
                        //                        and device_name_tag = '{device_number_point.Split("_")[1]}' and device_point_name = '{device_number_point.Split("_")[5]}'");
                        //}

                        station = await frontendRepository.GetOneAsync<string>($@"select distinct parent_path from import_niagara_item_history
                                        where device_building_tag = '{device_number_point.Split("_")[0].Replace("$3", "")}' and (parent_path != '' or parent_path is not null) limit 1;");
                    }
                    else
                        station = frontendRepository.GetOneAsync<string>($@"select parent_path from import_niagara_item_history 
                                                where device_building_tag = '{device_number_point.Split("_")[1].Replace("$3", "")}' and 
                                                device_system_tag = '{device_number_point.Split("_")[2]}' and device_name_tag = '{device_number_point.Split("_")[3]}' 
                                                and device_floor_tag = '{device_number_point.Split("_")[4]}' and device_master_tag = '{device_number_point.Split("_")[5]}' 
                                                and device_last_name_tag = '{device_number_point.Split("_")[6]}' and device_serial_tag = '{device_number_point.Split("_")[7]}'
                                                and device_point_name = '{device_number_point.Split("_")[8]}'").Result;

                    HttpWebRequest request = (HttpWebRequest)WebRequest.Create($"{obixApiConfig.ApiBase}obix/histories/{station}/{device_number_point.Replace("$3", "")}/~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)
                                {
                                    if (Convert.ToInt32(countObj["@val"].ToString()) > 1)
                                    {
                                        //讀取資料
                                        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");
                                            //取得資料
                                            string tag = device_item_info.Is_bool == "1" ? "bool" : "real";
                                            historyRawData.Value = obj[tag]["@val"].ToString();

                                            historyRawDatas.Add(historyRawData);
                                        }
                                    }
                                    else
                                    {
                                        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 = dataList["obj"]["abstime"]["@val"].ToString();
                                        historyRawData.Timestamp = Convert.ToDateTime(abstime).ToString("yyyy-MM-dd HH:mm:ss");
                                        //取得資料
                                        string tag = device_item_info.Is_bool == "1" ? "bool" : "real";
                                        historyRawData.Value = dataList["obj"][tag]["@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>
        /// 即時歷史資料(從MSSQL拿資料)
        /// </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>();
            List<string> noDataDevice = new List<string>();

            if (!jwtlife)
            {
                apiResult.Code = "5000";
                return apiResult;
            }
            if (input.HistoryItems == null)
            {
                apiResult.Code = "9998";
                apiResult.Msg = "沒有設備被選擇";
                return 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 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;";

                int totalRecordCount = 0;

                // 先計算總記錄數
                if (input.EnableLimit)
                {
                    foreach (var hi in input.HistoryItems)
                    {
                        var device_number = hi.Device_number_point.Split(":")[0];
                        var point = hi.Device_number_point.Split(":")[1];
                        var stationName = await backendRepository.GetOneAsync<string>($"select distinct parent_path from import_niagara_item_history where device_building_tag = '{device_number.Split('_')[0]}' limit 1");
                        var tableName = await backgroundServiceMsSqlRepository.GetOneAsync<string>($"select table_name from INFORMATION_SCHEMA.TABLES where table_name like '%{stationName}_{device_number}_{point}%'");

                        if (!string.IsNullOrEmpty(tableName))
                        {
                            var sqlCount = $@"select count(*) from {tableName}
                          where replace(convert(varchar, [timestamp], 111), '/', '-') >= @startTime 
                            and replace(convert(varchar, [timestamp], 111), '/', '-') <= @endTime";

                            var recordCount = await backgroundServiceMsSqlRepository.GetOneAsync<int>(sqlCount, new { startTime = input.Start_timestamp, endTime = input.End_timestamp });
                            totalRecordCount += recordCount;

                            if (totalRecordCount > 50000)
                            {
                                // 如果總記錄數超過5萬筆，返回提示信息
                                apiResult.Code = "5000";
                                apiResult.Msg = "資料量超過5萬筆，請減少選擇區間或設備";
                                return apiResult;
                            }
                        }
                    }
                }
                // 正式撈取資料
                foreach (var hi in input.HistoryItems)
                {
                    var device_number = hi.Device_number_point.Split(":")[0];
                    var point = hi.Device_number_point.Split(":")[1];
                    var stationName = await backendRepository.GetOneAsync<string>($"select distinct parent_path from import_niagara_item_history where device_number =  '{device_number}' limit 1");
                    var tableName = await backgroundServiceMsSqlRepository.GetOneAsync<string>($"select table_name from INFORMATION_SCHEMA.TABLES where table_name like '%{stationName}_{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 valueType = await backgroundServiceMsSqlRepository.GetOneAsync<string>($"select Data_Type from INFORMATION_SCHEMA.COLUMNS where table_name = '{tableName}' and column_name = 'Value'");
                        var valueFacets = await backgroundServiceMsSqlRepository.GetOneAsync<string>($"select VALUEFACETS from HISTORY_CONFIG WHERE TABLE_NAME = '{tableName}'");
                        //valueFacets = "range=E:{$u5176$u4ed6$u72c0$u614b=1,$u6b63$u5e38=2,$u4f4e$u96fb$u4f4d=3,$u8017$u76e1=4,$u672a$u9023$u63a5=5,$u7570$u5e38=6}";

                        // 檢查 valueFacets 是否包含 "range=E"
                        string valueDisplaySql = string.Empty;
                        // 直接給千分位數值會讓前台套件顯示有問題，故頁面千分位顯示由前端處理，CSV千分位後端處理
                        string DisplaySql = input.EnableLimit ? "ROUND(value, 2) AS Value" : " FORMAT(ROUND(value, 2), 'N2') AS Value"; 

                        if (valueFacets != null)
                        {
                            // 存儲匹配條目的列表
                            var caseWhenStatements = new List<string>();

                            // 轉換 Unicode 序列為中文字符的函數
                            string ConvertUnicodeToChinese(string unicodeSequence)
                            {
                                var chineseCharacters = new StringBuilder();
                                var unicodeParts = unicodeSequence.Split("$u");
                                foreach (var unicodePart in unicodeParts)
                                {
                                    if (unicodePart.Length == 4)
                                    {
                                        var unicodeValue = Convert.ToInt32(unicodePart, 16);
                                        chineseCharacters.Append(char.ConvertFromUtf32(unicodeValue));
                                    }
                                }
                                return chineseCharacters.ToString();
                            }

                            // 處理 range=E 的部分
                            if (valueFacets.Contains("range=E"))
                            {
                                // 提取 "range=E:" 之後的部分
                                string rangePart = valueFacets.Substring(valueFacets.IndexOf("range=E:") + "range=E:".Length);
                                rangePart = rangePart.Trim('{', '}'); // 去除大括號
                                var entries = rangePart.Split(',');

                                foreach (var entry in entries)
                                {
                                    var parts = entry.Split('=');
                                    if (parts.Length == 2)
                                    {
                                        string unicodeSequence = parts[0];
                                        int correspondingValue = int.Parse(parts[1]);

                                        // 轉換 Unicode 為中文
                                        string chineseCharacters = ConvertUnicodeToChinese(unicodeSequence);

                                        // 添加 Case When 語句
                                        caseWhenStatements.Add($"when value = {correspondingValue} then '{chineseCharacters}'");
                                    }
                                }

                                valueDisplaySql = $"Case {string.Join(" ", caseWhenStatements)} else 'Unknow' End as Value";
                            }
                            // 處理 trueText 和 falseText 的部分
                            else if (valueFacets.Contains("trueText=s:") && valueFacets.Contains("falseText=s:"))
                            {
                                // 提取 trueText 和 falseText 的部分
                                string trueTextPart = valueFacets.Substring(valueFacets.IndexOf("trueText=s:") + "trueText=s:".Length);
                                trueTextPart = trueTextPart.Split('|')[0]; // 只保留 trueText 的部分

                                string falseTextPart = valueFacets.Substring(valueFacets.IndexOf("falseText=s:") + "falseText=s:".Length);
                                falseTextPart = falseTextPart.Split('|')[0]; // 只保留 falseText 的部分

                                // 如果不包含 $u，就直接使用這些字串
                                if (trueTextPart.Contains("$u"))
                                {
                                    trueTextPart = ConvertUnicodeToChinese(trueTextPart);
                                }

                                if (falseTextPart.Contains("$u"))
                                {
                                    falseTextPart = ConvertUnicodeToChinese(falseTextPart);
                                }

                                // 添加 Case When 語句
                                caseWhenStatements.Add($"when value = 1 then '{trueTextPart}'");
                                caseWhenStatements.Add($"when value = 0 then '{falseTextPart}'");

                                valueDisplaySql = $"Case {string.Join(" ", caseWhenStatements)} else 'Unknow' End as Value";
                            }
                            else
                            {
                                // 使用原始的顯示方式
                                valueDisplaySql = valueType.Equals("bit")
                                    ? "Case when value = 1 then 'true' when value = 0 then 'false' else 'Unknow' End as Value"
                                    : DisplaySql;
                            }

                        }


                        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, FORMAT(timestamp, 'yyyy-MM-dd HH:mm:ss') as Timestamp, 
                            {valueDisplaySql}
                        from {tableName}
                        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 })
                        );
                    }
                    else
                    {
                        noDataDevice.Add(hi.Device_number_point);
                    }
                }


                apiResult.Code = "0000";
                apiResult.Msg = @$"DB無資料的設備：{string.Join(".", noDataDevice)}";
                apiResult.Data = apiResult.Data.OrderBy(x => x.Device_number).ThenBy(x => x.Points).ThenByDescending(x => x.Timestamp).ToList();
            }
            catch (Exception exception)
            {
                apiResult.Code = "9999";
                apiResult.Msg = "系統內部錯誤，請聯絡管理者。";
                Logger.LogError("【" + controllerName + "/" + actionName + "】" + exception.Message + exception.StackTrace);
                return apiResult;
            }
            return apiResult;
        }

        private List<Dictionary<string, object>> ArrangeRawData(DeviceNumberPoint deviceNumberPoint, JObject jsonResult)
        {
            List<Dictionary<string, object>> arrangeRawDatas = new List<Dictionary<string, object>>();
            try
            {
                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;
            }
            catch (Exception ex)
            {
                return arrangeRawDatas;
            }
        }
    }
}