ibms-dome/Backend/Controllers/NiagaraDataSynchronizeController.cs

using Microsoft.AspNetCore.Mvc;
using Repository.BackendRepository.Interface;
using System.Collections.Generic;
using System.Threading.Tasks;
using System;
//using Backend.Models;
using Microsoft.Extensions.Logging;
using Backend.Services;
using Backend.Services.Implement;
using Repository.Models;
using System.Linq;
using Microsoft.Extensions.DependencyInjection;
using NPOI.SS.Formula.Functions;
using Repository.Helper;
using Microsoft.AspNetCore.Routing.Matching;
using Newtonsoft.Json;
using System.Diagnostics;

namespace Backend.Controllers
{
    public class NiagaraDataSynchronizeController : MybaseController<NiagaraDataSynchronizeController>
    {
        private readonly IBackendRepository backendRepository;
        //private readonly IDeviceImportRepository deviceImportRepository;
        private readonly INiagaraDataSynchronizeRepository niagaraDataSynchronizeRepository;
        private readonly IBackEndConfigHelper backEndConfig;
        private Stopwatch stopwatch = new Stopwatch();
        private Stopwatch stopwatchSection = new Stopwatch();
        private Stopwatch syncStopWatch = new Stopwatch();
        private static long syncWatchTime = 0;
        public static bool isFirstAsync = false;

        public NiagaraDataSynchronizeController(IBackendRepository backendRepository, INiagaraDataSynchronizeRepository niagaraDataSynchronizeRepository,IBackEndConfigHelper backEndConfig)
        {
            this.backendRepository = backendRepository;
            //this.deviceImportRepository = deviceImportRepository;
            this.niagaraDataSynchronizeRepository = niagaraDataSynchronizeRepository;
            this.backEndConfig = backEndConfig;
        }

        public IActionResult Index()
        {
            return View();
        }

        /// <summary>
        /// N4匯入列表
        /// </summary>
        /// <returns></returns>
        [HttpPost]
        public Task<ApiResult<List<Device_value>>> RawDataList()
        {
            stopwatch = new Stopwatch();
            stopwatch.Start();
            ApiResult<List<Device_value>> apiResult = new ApiResult<List<Device_value>>();
            try
            {
                var sqlObix = $@"SELECT system_value as Value, system_key as Name FROM variable WHERE deleted = 0 AND system_type = 'obixConfig'";
                var variableObix = backendRepository.GetAllAsync<Backend.Models.KeyValue>(sqlObix).Result;
                var obixApiConfig = new Backend.Models.ObixApiConfig();
                EDFunction ed = new EDFunction();
                obixApiConfig.ApiBase = variableObix.Where(x => x.Name == "ApiBase").Select(x => x.Value).FirstOrDefault() + "obix/config/Program/ObixQuery/query/";
                //obixApiConfig.UrlSlot =variableObix.Where(x => x.Name == "url_slot").Select(x => x.Value).FirstOrDefault();
                obixApiConfig.TagQuantity = variableObix.Where(x => x.Name == "tag_quantity").Select(x => x.Value).FirstOrDefault();
                obixApiConfig.UserName = variableObix.Where(x => x.Name == "UserName").Select(x => x.Value).FirstOrDefault();
                obixApiConfig.Password = variableObix.Where(x => x.Name == "Password").Select(x => x.Value).FirstOrDefault();

                var ds = new List<Device_value>();

                string top100 = "";//" top 100 ";
                webRequestService svc = new webRequestService();
                var urlSlots = backendRepository.GetAllAsync<string>("select obixSlot from building where deleted = 0").Result;
                stopwatchSection = new Stopwatch();
                stopwatchSection.Start();
                foreach (var us in urlSlots)
                {
                    string bql = us + "bql:select " + top100 + " * from baja:Folder ";
                    //ds = svc.obixQuery("http://192.168.0.136:8080/obix/config/Arena/Program/ObixQuery/query/", bql);
                    //ds = svc.obixQuery("http://localhost:8080/obix/config/Program/ObixQuery/query/", bql);
                    ds.AddRange(svc.obixQuery(obixApiConfig.ApiBase, bql, obixApiConfig.TagQuantity, obixApiConfig.UserName, obixApiConfig.Password));//三菱
                }
                stopwatchSection.Stop();
                niagaraDataSynchronizeRepository.KeepTimeLog("GetDataFromNiagara", stopwatchSection.ElapsedMilliseconds);
                apiResult.Code = "0000";
                apiResult.Data = ds;
            }
            catch (Exception exception)
            {
                apiResult.Code = "9999";
                apiResult.Msg = "【" + controllerName + "/" + actionName + "】" + exception.Message;
                Logger.LogError("【" + controllerName + "/" + actionName + "】" + exception.Message + Environment.NewLine + exception.StackTrace);
                // Logger.LogError("【" + controllerName + "/" + actionName + "】" + exception.Message);
            }

            //return apiResult;
            stopwatchSection.Stop();
            niagaraDataSynchronizeRepository.KeepTimeLog("RawDataList", stopwatch.ElapsedMilliseconds);
            return Task.FromResult(apiResult);
        }

        /// <summary>
        /// 比對N4與資料表，有差異在同步至資料表中
        /// </summary>
        /// <returns></returns>
        [HttpPost]
        public async Task<ApiResult<bool>> CompareData([FromBody] List<Device_value> ds)
        {
            syncStopWatch = new Stopwatch();
            syncStopWatch.Start();
            syncWatchTime = 0;
            stopwatch = new Stopwatch();
            stopwatch.Start();
            bool result = false;
            ApiResult<bool> apiResult = new ApiResult<bool>();
            var sqlObix = $@"SELECT system_value as Value, system_key as Name FROM variable WHERE deleted = 0 AND system_type = 'obixConfig'";
            var variableObix = await backendRepository.GetAllAsync<Backend.Models.KeyValue>(sqlObix);
            string tag_quantity = variableObix.Where(x => x.Name == "tag_quantity").Select(x => x.Value).FirstOrDefault();

            try
            {
                stopwatchSection = new Stopwatch();
                stopwatchSection.Start();
                string ss = ds.Where(x => x.tag_name != "").FirstOrDefault().tag_name;
                var data = ds.Where(x => x.tag_name != "");
                List<string> building = null;
                if (tag_quantity == "5")
                {
                    //building.Add(ss.Split("_")[0]);
                    building = data.GroupBy(x => x.tag_name.Split("_")[0]).Select(x => x.Key).ToList();
                }
                else
                {
                    //building.Add(ss.Split("_")[1]);
                    building = data.GroupBy(x => x.tag_name.Split("_")[1]).Select(x => x.Key).ToList();
                }
                stopwatchSection.Stop();
                await niagaraDataSynchronizeRepository.KeepTimeLog("filter building tag", stopwatchSection.ElapsedMilliseconds);

                stopwatchSection = new Stopwatch();
                stopwatchSection.Start();
                string LightSwitchLevel = await niagaraDataSynchronizeRepository.getLightSwitchLevel(); //獲取照明開關 是否在 device or device_node 
                stopwatchSection.Stop();
                await niagaraDataSynchronizeRepository.KeepTimeLog("getLightSwitchLevel", stopwatchSection.ElapsedMilliseconds);

                stopwatchSection = new Stopwatch();
                stopwatchSection.Start();
                await niagaraDataSynchronizeRepository.InsertNiagaraTagList(ds, building, tag_quantity); // 匯入 MySQL table: import_niagara_tag
                stopwatchSection.Stop();
                await niagaraDataSynchronizeRepository.KeepTimeLog("InsertNiagaraTagList", stopwatchSection.ElapsedMilliseconds);

                stopwatchSection = new Stopwatch();
                stopwatchSection.Start();
                await niagaraDataSynchronizeRepository.DeviceComparison(LightSwitchLevel);
                stopwatchSection.Stop();
                await niagaraDataSynchronizeRepository.KeepTimeLog("DeviceComparison", stopwatchSection.ElapsedMilliseconds);

                stopwatchSection = new Stopwatch();
                stopwatchSection.Start();
                await niagaraDataSynchronizeRepository.CheckDiffFullNameAndCover(); // *
                stopwatchSection.Stop();
                await niagaraDataSynchronizeRepository.KeepTimeLog("CheckDiffFullNameAndCover", stopwatchSection.ElapsedMilliseconds);

                stopwatchSection = new Stopwatch();
                stopwatchSection.Start();
                await niagaraDataSynchronizeRepository.CheckFullNameEmptyReplaceByDeviceName();
                stopwatchSection.Stop();
                await niagaraDataSynchronizeRepository.KeepTimeLog("CheckFullNameEmptyReplaceByDeviceName", stopwatchSection.ElapsedMilliseconds);

                stopwatchSection = new Stopwatch();
                stopwatchSection.Start();
                await niagaraDataSynchronizeRepository.InsertBuildingMenu();
                stopwatchSection.Stop();
                await niagaraDataSynchronizeRepository.KeepTimeLog("InsertBuildingMenu", stopwatchSection.ElapsedMilliseconds);

                stopwatchSection = new Stopwatch();
                stopwatchSection.Start();
                await niagaraDataSynchronizeRepository.UpdateAuthPageByBuildingMenu();
                stopwatchSection.Stop();
                await niagaraDataSynchronizeRepository.KeepTimeLog("UpdateAuthPageByBuildingMenu", stopwatchSection.ElapsedMilliseconds);

                stopwatchSection = new Stopwatch();
                stopwatchSection.Start();
                await niagaraDataSynchronizeRepository.InsertSubSystemFloor();
                stopwatchSection.Stop();
                await niagaraDataSynchronizeRepository.KeepTimeLog("InsertSubSystemFloor", stopwatchSection.ElapsedMilliseconds);

                stopwatchSection = new Stopwatch();
                stopwatchSection.Start();
                await niagaraDataSynchronizeRepository.InsertFloor();
                stopwatchSection.Stop();
                await niagaraDataSynchronizeRepository.KeepTimeLog("InsertFloor", stopwatchSection.ElapsedMilliseconds);

                stopwatchSection = new Stopwatch();
                stopwatchSection.Start();
                await this.DeviceDisasterAsync();
                stopwatchSection.Stop();
                await niagaraDataSynchronizeRepository.KeepTimeLog("DeviceDisasterAsync", stopwatchSection.ElapsedMilliseconds);
                result = true;

                apiResult.Code = "0000";
                apiResult.Data = result;
            }
            catch (Exception exception)
            {
                apiResult.Code = "9999";
                apiResult.Msg = "【" + controllerName + "/" + actionName + "】" + exception.Message;
                Logger.LogError("【" + controllerName + "/" + actionName + "】" + exception.Message + Environment.NewLine + exception.StackTrace);
            }

            stopwatch.Stop();
            await niagaraDataSynchronizeRepository.KeepTimeLog("CompareData", stopwatch.ElapsedMilliseconds);

            syncStopWatch.Stop();
            syncWatchTime += syncStopWatch.ElapsedMilliseconds;

            return apiResult;
        }

        /// <summary>
        /// N4匯入列表(device_item)
        /// </summary>
        /// <returns></returns>
        [HttpPost]
        public Task<ApiResult<List<ImpNiaItem>>> RawDataDevItemList([FromBody] Device_value building)
        {
            syncStopWatch = new Stopwatch();
            syncStopWatch.Start();
            ApiResult<List<ImpNiaItem>> apiResult = new ApiResult<List<ImpNiaItem>>();
            stopwatch = new Stopwatch();
            stopwatch.Start();
            try
            {
                stopwatchSection = new Stopwatch();
                stopwatchSection.Start();
                var ds = new List<ImpNiaItem>();

                webRequestService svc = new webRequestService();
                var sqlObix = $@"SELECT system_value as Value, system_key as Name FROM variable WHERE deleted = 0 AND system_type = 'obixConfig'";
                var variableObix = backendRepository.GetAllAsync<Backend.Models.KeyValue>(sqlObix).Result;
                var obixApiConfig = new Backend.Models.ObixApiConfig();
                EDFunction ed = new EDFunction();
                obixApiConfig.ApiBase = variableObix.Where(x => x.Name == "ApiBase").Select(x => x.Value).FirstOrDefault();
                obixApiConfig.UserName = variableObix.Where(x => x.Name == "UserName").Select(x => x.Value).FirstOrDefault();
                obixApiConfig.Password = variableObix.Where(x => x.Name == "Password").Select(x => x.Value).FirstOrDefault();
                obixApiConfig.UrlSlot = variableObix.Where(x => x.Name == "url_slot").Select(x => x.Value).FirstOrDefault();
                var urlSlots = backendRepository.GetAllAsync<BuildingDetail>("select obixSlot, area_tag from building where deleted = 0").Result;
                var data2 = new List<DeviceControlPoint>();
                foreach (var us in urlSlots)
                {
                    var data = svc.obixHisQuery(obixApiConfig.ApiBase + "obix/config/Program/ObixQuery/query/", obixApiConfig.ApiBase + "obix/histories", us.obixSlot,
                                obixApiConfig.UserName, obixApiConfig.Password, us.area_tag);

                    data2.AddRange(svc.obixAllDevicePoint(obixApiConfig.ApiBase + "obix/config/Program/ObixQuery/query/", obixApiConfig.ApiBase + "obix/histories", us.obixSlot,
                                obixApiConfig.UserName, obixApiConfig.Password, us.area_tag));

                    ds.AddRange(data);
                }

                if (data2.Any())
                {
                    niagaraDataSynchronizeRepository.DeviceControlPoint(data2);
                }
                stopwatchSection.Stop();
                niagaraDataSynchronizeRepository.KeepTimeLog("get device_item from niagara and record all device point", stopwatchSection.ElapsedMilliseconds);
                var buildings = backendRepository.GetAllAsync<string>("select building_tag from building where deleted = 0").Result;
                apiResult.Code = "0000";
                apiResult.Data = ds.Where(x => buildings.Contains(x.device_building_tag)).ToList();
            }
            catch (Exception exception)
            {
                apiResult.Code = "9999";
                apiResult.Msg = "【" + controllerName + "/" + actionName + "】" + exception.Message;
                Logger.LogError("【" + controllerName + "/" + actionName + "】" + exception.Message + Environment.NewLine + exception.StackTrace);
            }

            //return apiResult;
            stopwatch.Stop();
            niagaraDataSynchronizeRepository.KeepTimeLog("RawDataDevItemList", stopwatch.ElapsedMilliseconds);

            syncStopWatch.Stop();
            syncWatchTime += syncStopWatch.ElapsedMilliseconds;
            return Task.FromResult(apiResult);
        }

        /// <summary>
        /// 比對N4與資料表，有差異在同步至資料表中(device_item)
        /// </summary>
        /// <returns></returns>
        [HttpPost]
        public async Task<ApiResult<bool>> DevIteComData()
        {
            syncStopWatch = new Stopwatch();
            syncStopWatch.Start();
            stopwatch = new Stopwatch();
            stopwatch.Start();
            bool result = false;
            //var oldDs = JsonConvert.DeserializeObject<List<ImpNiaItem>>(JsonConvert.SerializeObject(ds));
            ApiResult<bool> apiResult = new ApiResult<bool>();
            try
            {
                //string ss = ds.Where(x => x.tag_name != "").FirstOrDefault().tag_name;
                //var building = ss.Split("_")[0];
                //var building = ds.GroupBy(x => x.device_building_tag).Select(x => x.Key).ToList();

                stopwatchSection = new Stopwatch();
                stopwatchSection.Start();
                await niagaraDataSynchronizeRepository.DeviceItemComparison();  //insert  device_item
                stopwatchSection.Stop();
                await niagaraDataSynchronizeRepository.KeepTimeLog("DeviceItemComparison", stopwatchSection.ElapsedMilliseconds);

                stopwatchSection = new Stopwatch();
                stopwatchSection.Start();
                await niagaraDataSynchronizeRepository.CheckItemDiffFullNameAndCover(); // update device_item.fullname
                stopwatchSection.Stop();
                await niagaraDataSynchronizeRepository.KeepTimeLog("CheckItemDiffFullNameAndCover", stopwatchSection.ElapsedMilliseconds);

                stopwatchSection = new Stopwatch();
                stopwatchSection.Start();
                await niagaraDataSynchronizeRepository.ItemCheckFullNameEmptyReplaceByDeviceName(); // 檢查device_item內FullName為空的值，以points取代
                stopwatchSection.Stop();
                await niagaraDataSynchronizeRepository.KeepTimeLog("ItemCheckFullNameEmptyReplaceByDeviceName", stopwatchSection.ElapsedMilliseconds);

                //stopwatchSection = new Stopwatch();
                //stopwatchSection.Start();
                //await niagaraDataSynchronizeRepository.CheckItemIsShowHistory();
                //stopwatchSection.Stop();
                //await niagaraDataSynchronizeRepository.KeepTimeLog("CheckItemIsShowHistory", stopwatchSection.ElapsedMilliseconds);

                if (isFirstAsync)
                    isFirstAsync = false;

                result = true;
                apiResult.Code = "0000";
                apiResult.Data = result;
            }
            catch (Exception exception)
            {
                isFirstAsync = false;
                apiResult.Code = "9999";
                apiResult.Msg = "【" + controllerName + "/" + actionName + "】" + exception.Message;
                Logger.LogError("【" + controllerName + "/" + actionName + "】" + exception.Message + Environment.NewLine + exception.StackTrace);
            }

            stopwatch.Stop();
            await niagaraDataSynchronizeRepository.KeepTimeLog("DevIteComData", stopwatch.ElapsedMilliseconds);

            syncStopWatch.Stop();
            syncWatchTime += syncStopWatch.ElapsedMilliseconds;
            await niagaraDataSynchronizeRepository.KeepTimeLog("Full sync usage record", syncWatchTime);
            return apiResult;
        }

        private async Task DeviceDisasterAsync()
        {
            stopwatchSection = new Stopwatch();
            stopwatchSection.Start();
            var sqlObix = $@"SELECT system_value as Value, system_key as Name FROM variable WHERE deleted = 0 AND system_type = 'obixConfig'";
            var variableObix = await backendRepository.GetAllAsync<Backend.Models.KeyValue>(sqlObix);
            var obixApiConfig = new Models.ObixApiConfig();
            obixApiConfig.ApiBase = variableObix.Where(x => x.Name == "ApiBase").Select(x => x.Value).FirstOrDefault();
            obixApiConfig.UserName = variableObix.Where(x => x.Name == "UserName").Select(x => x.Value).FirstOrDefault();
            obixApiConfig.Password = variableObix.Where(x => x.Name == "Password").Select(x => x.Value).FirstOrDefault();
            obixApiConfig.TagQuantity = variableObix.Where(x => x.Name == "tag_quantity").Select(x => x.Value).FirstOrDefault();

            if (obixApiConfig.TagQuantity == "5")
            {
                List<Device_value_disaster> dv = new List<Device_value_disaster>();
                List<string> urlSlots = await backendRepository.GetAllAsync<string>("select obixSlot from building where deleted = 0");
                List<string> disasterTag = await backendRepository.GetAllAsync<string>("select system_value from variable where system_type = 'disaster' and deleted = 0");
                webRequestService svc = new webRequestService();
                foreach(var dt in disasterTag)
                {
                    string bql = "neql:hs:geoAddr=\"ER" + dt + "\"";
                    dv.AddRange(svc.obixDevDisaster(obixApiConfig.ApiBase + "obix/config/Arena/Program/ObixQuery/query/", bql, obixApiConfig.TagQuantity, obixApiConfig.UserName, 
                                                        obixApiConfig.Password, dt));
                }

                await niagaraDataSynchronizeRepository.DeviceDisasterAysnc(dv);
            }
            stopwatchSection.Stop();
            await niagaraDataSynchronizeRepository.KeepTimeLog("DeviceDisasterAsync", stopwatchSection.ElapsedMilliseconds);
        }

        /// <summary>
        /// 歷史資料處理
        /// </summary>
        /// <returns></returns>
        [HttpPost]
        public async Task<ApiResult<bool>> GetHistoryData()
        {
            ApiResult<bool> apiResult = new ApiResult<bool>();
            apiResult.Data = false;
            apiResult.Code = "9999";
            try
            {
                #region 歷史資料處理
                List<ImpNiaItem> ds = new List<ImpNiaItem>();
                var building = await backendRepository.GetAllAsync<string>("select building_tag from building where deleted = 0");
                var isDome = await backendRepository.GetOneAsync<string>("select system_value from variable where deleted = 0 and system_type = 'project_name';");
                var sqlObix = $@"SELECT system_value as Value, system_key as Name FROM variable WHERE deleted = 0 AND system_type = 'obixConfig'";
                var variableObix = backendRepository.GetAllAsync<Backend.Models.KeyValue>(sqlObix).Result;
                var obixApiConfig = new Backend.Models.ObixApiConfig();
                EDFunction ed = new EDFunction();
                obixApiConfig.ApiBase = variableObix.Where(x => x.Name == "ApiBase").Select(x => x.Value).FirstOrDefault();
                obixApiConfig.UserName = variableObix.Where(x => x.Name == "UserName").Select(x => x.Value).FirstOrDefault();
                obixApiConfig.Password = variableObix.Where(x => x.Name == "Password").Select(x => x.Value).FirstOrDefault();
                //obixApiConfig.UrlSlot =  variableObix.Where(x => x.Name == "url_slot").Select(x => x.Value).FirstOrDefault();
                webRequestService svc = new webRequestService();
                stopwatchSection = new Stopwatch();
                stopwatchSection.Start();
                var data = svc.obixHisQuery_v2(obixApiConfig.ApiBase + "obix/config/Program/ObixQuery/query/", obixApiConfig.ApiBase + "obix/histories", "",
                            obixApiConfig.UserName, obixApiConfig.Password);
                ds.AddRange(data);
                stopwatchSection.Stop();
                await niagaraDataSynchronizeRepository.KeepTimeLog("Get history device point from niagara", stopwatchSection.ElapsedMilliseconds);

                // save to history 
                stopwatchSection = new Stopwatch();
                stopwatchSection.Start();
                await niagaraDataSynchronizeRepository.InsertItemFromNiagara_History(ds, building); // insert 暫存table import_niagara_item_history
                stopwatchSection.Stop();
                await niagaraDataSynchronizeRepository.KeepTimeLog("InsertItemFromNiagara_History", stopwatchSection.ElapsedMilliseconds);
                if (!string.IsNullOrEmpty(isDome) && isDome.Contains("ibms_dome"))
                {
                    stopwatchSection = new Stopwatch();
                    stopwatchSection.Start();
                    await niagaraDataSynchronizeRepository.RecoverSysTag("import_niagara_item_history"); // recover missing deivce_system_tag
                    stopwatchSection.Stop();
                    await niagaraDataSynchronizeRepository.KeepTimeLog("RecoverSysTag import_niagara_item_history", stopwatchSection.ElapsedMilliseconds);
                }
                #endregion

                apiResult.Code = "0000";
                apiResult.Data = true;
                isFirstAsync = true;
            }
            catch (Exception exception)
            {
                apiResult.Code = "9999";
                apiResult.Msg = "【" + controllerName + "/" + actionName + "】" + exception.Message;
                Logger.LogError("【" + controllerName + "/" + actionName + "】" + exception.Message + Environment.NewLine + exception.StackTrace);
            }

            return apiResult;
        }

        /// <summary>
        /// 設備點位處理
        /// </summary>
        /// <returns></returns>
        [HttpPost]
        public async Task<ApiResult<bool>> DevItemInsert([FromBody] List<ImpNiaItem> ds)
        {
            var oldDs = JsonConvert.DeserializeObject<List<ImpNiaItem>>(JsonConvert.SerializeObject(ds));
            ApiResult<bool> apiResult = new ApiResult<bool>();
            apiResult.Data = false;
            apiResult.Code = "9999";
            try
            {
                if (ds.Count > 0)
                {
                    var building = await backendRepository.GetAllAsync<string>("select building_tag from building where deleted = 0");

                    stopwatchSection = new Stopwatch();
                    stopwatchSection.Start();
                    await niagaraDataSynchronizeRepository.InsertItemFromNiagara(oldDs, building, isFirstAsync); // insert 暫存table import_niagara_item
                    stopwatchSection.Stop();
                    await niagaraDataSynchronizeRepository.KeepTimeLog("InsertItemFromNiagara", stopwatchSection.ElapsedMilliseconds);

                    if (isFirstAsync)
                        isFirstAsync = false;
                }

                apiResult.Code = "0000";
                apiResult.Data = true;
            }
            catch (Exception exception)
            {
                apiResult.Code = "9999";
                apiResult.Msg = "【" + controllerName + "/" + actionName + "】" + exception.Message;
                Logger.LogError("【" + controllerName + "/" + actionName + "】" + exception.Message + Environment.NewLine + exception.StackTrace);
            }

            return apiResult;
        }
    }
}