ibms-dome/FrontendWebApi/ApiControllers/LightScheduleController.cs

using FrontendWebApi.Models;
using Microsoft.AspNetCore.Mvc;
using Microsoft.CodeAnalysis.CSharp.Syntax;
using Microsoft.Extensions.Logging;
using Newtonsoft.Json;
using Repository.BackendRepository.Implement;
using Repository.BackendRepository.Interface;
using Repository.FrontendRepository.Interface;
using Serilog.Core;
using System;
using System.Collections.Generic;
using System.IO;
using System.Linq;
using System.Linq.Expressions;
using System.Net;
using System.Net.Http;
using System.Reflection;
using System.Runtime.Serialization.Formatters.Binary;
using System.Text;
using System.Threading.Tasks;

namespace FrontendWebApi.ApiControllers
{
    public class LightScheduleController : MyBaseApiController<LightScheduleController>
    {
        private readonly IBackendRepository backendRepository;
        private readonly IFrontendRepository frontendRepository;

        public LightScheduleController
        (
            IBackendRepository backendRepository,
            IFrontendRepository frontendRepository
        )
        {
            this.backendRepository = backendRepository;
            this.frontendRepository = frontendRepository;
        }

        [HttpPost]
        [Route("api/LightSchedule/GetLightDevice")]
        public async Task<ActionResult<ApiResult<List<lightDevice>>>> GetLightDevice(GetDevicePost post)
        {
            List<lightDevice> lightDevices = new List<lightDevice>();
            ApiResult<List<lightDevice>> apiResult = new ApiResult<List<lightDevice>>();
            try
            {
                var floor_tag = await backendRepository.GetOneAsync<string>($@"
                select full_name from floor where floor_guid = @floor_guid and deleted = 0
                ",new { floor_guid = post.floor_guid});

                lightDevices = await backendRepository.GetAllAsync<lightDevice>($@"
                select * from device where device_building_tag = '{post.building_tag}' and device_name_tag = '{post.sub_system_tag}' and device_floor_tag = '{floor_tag}' and deleted = 0 order by priority
                ");
                
                if(!String.IsNullOrEmpty(post.schedule_guid))
                {
                    var devicechecklist = await backendRepository.GetAllAsync<string>(@$"
                    select sd.device_guid from schedule_device sd where light_schedule_guid = '{post.schedule_guid}'
                    ");
                    foreach(var a in lightDevices)
                    {
                        if(devicechecklist.Contains(a.device_guid))
                        {
                            a.check = 1;
                        }
                        else
                        {
                            a.check = 0;
                        }
                    }
                }

                apiResult.Code = "0000";
                apiResult.Data = lightDevices;
            }
            catch (Exception exception)
            {
                apiResult.Code = "9999";
                apiResult.Msg = "系統內部錯誤，請聯絡管理者。";
                Logger.LogError("【" + controllerName + "/" + actionName + "】" + exception.Message);
            }
            return Ok(apiResult);
        }

        [HttpPost]
        [Route("api/LightSchedule/SaveSchedule")]
        public async Task<ActionResult<ApiResult<string>>> SaveSchedule (SaveSchedule saveSchedule)
        {
            ApiResult<string> apiResult = new ApiResult<string>();
            try
            {
                // Operation_log 輸入參數
                OperationLog opeInput = new OperationLog() { operation_type = 2 };

                // 取得對應樓層資料
                var targetFloor = await backendRepository.GetOneAsync<Floor>($@"
                            select * from floor where floor_guid = @floor_guid",
                            new { floor_guid = saveSchedule.floor_guid});
                // 取得對應燈控排程主表資料
                var targetScheduleLight = await backendRepository.GetOneAsync<SaveSchedule>($@"
                            select * from light_schedule where light_schedule_guid = @light_schedule_guid",
                            new { light_schedule_guid = saveSchedule.light_schedule_guid });
                // 取得對應燈控排程設備資料
                var targetScheduleDevice = await backendRepository.GetAllAsync<string>($@"
                            select device_guid from schedule_device where light_schedule_guid = @light_schedule_guid",
                            new { light_schedule_guid = saveSchedule.light_schedule_guid });


                opeInput.building_tag = targetFloor.building_tag;
                opeInput.floor_tag = targetFloor.full_name;

                if (String.IsNullOrEmpty(saveSchedule.light_schedule_guid))
                {
                    opeInput.action_name = "新增";
                    Dictionary<string, object> Schedule = new Dictionary<string, object>();
                    var newguid = Guid.NewGuid();
                    Schedule = new Dictionary<string, object>()
                    {
                        { "@light_schedule_guid", newguid},
                        { "@status", saveSchedule.status},
                        { "@full_name", saveSchedule.full_name},
                        { "@week", saveSchedule.week},
                        { "@cycle", saveSchedule.cycle},
                        { "@floor_guid", saveSchedule.floor_guid},
                        { "@start_time", saveSchedule.start_time},
                        { "@end_time", saveSchedule.end_time},
                        { "@created_by", myUser.userinfo_guid}
                    };

                    await backendRepository.AddOneByCustomTable(Schedule, "light_schedule");
                    List<Dictionary<string, object>> ScheduleDevices = new List<Dictionary<string, object>>();
                    foreach (var a in saveSchedule.devicelist)
                    {
                        Dictionary<string, object> ScheduleDevice = new Dictionary<string, object>();
                        ScheduleDevice = new Dictionary<string, object>()
                        {
                            { "@light_schedule_guid", newguid},
                            { "@device_guid", a}
                        };
                        ScheduleDevices.Add(ScheduleDevice);
                    }
                    await backendRepository.AddMutiByCustomTable(ScheduleDevices, "schedule_device");
                    saveSchedule.light_schedule_guid = newguid.ToString();
                    opeInput.parameter = JsonConvert.SerializeObject(saveSchedule);
                    await InsertOperation(opeInput);
                }
                else
                {
                    opeInput.action_name = "修改";
                    Dictionary<string, object> Schedule = new Dictionary<string, object>();
                    // 配合燈控排程背景程式更新guid(因排程根據guid執行起始結束各一次，若中途修改條件則不會執行，故作此修改)
                    var newguid = Guid.NewGuid();

                    Schedule = new Dictionary<string, object>()
                    {
                        { "@light_schedule_guid",newguid},
                        { "@status", saveSchedule.status},
                        { "@full_name", saveSchedule.full_name},
                        { "@week", saveSchedule.week},
                        { "@cycle", saveSchedule.cycle},
                        { "@floor_guid", saveSchedule.floor_guid},
                        { "@start_time", saveSchedule.start_time},
                        { "@end_time", saveSchedule.end_time},
                        { "@updated_by", myUser.userinfo_guid},
                        { "@updated_at", DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss")}
                    };

                    // 比較欄位
                    List<string> compareTargetProps = new List<string>() { "full_name", "week", "cycle", "floor_guid", "start_time", "end_time" };
                    List<string> compareTargetValues = new List<string>();

                    Type modelType = saveSchedule.GetType();
                    // 根據每個欄位比較
                    foreach(var prop in compareTargetProps){
                        PropertyInfo propertyInfo = modelType.GetProperty(prop);
                        if (propertyInfo == null) continue;
                        // 比較 saveSchedule 與 targetSchedule
                        var value = propertyInfo.GetValue(saveSchedule,null)?.ToString();
                        var newValue = propertyInfo.GetValue(targetScheduleLight, null)?.ToString();
                        // 只要不對就是排程變更
                        if (value != newValue) {
                            saveSchedule.changeNames.Add("排程變更");
                            break;
                        }
                    }
                    // 判斷是否為狀態變更
                    if (targetScheduleLight.status != saveSchedule.status) {
                        saveSchedule.changeNames.Add("狀態變更");
                    }

                    // 兩邊設備 guid 排序後比較
                    saveSchedule.devicelist.Sort();
                    targetScheduleDevice.Sort();
                    if (!saveSchedule.devicelist.SequenceEqual(targetScheduleDevice)) {
                        saveSchedule.changeNames.Add("設備變更");
                    }

                    await backendRepository.UpdateOneByCustomTable(Schedule, "light_schedule", $" light_schedule_guid = '{saveSchedule.light_schedule_guid}'");
                    await backendRepository.PurgeOneByGuidWithCustomDBNameAndTable("schedule_device", $" light_schedule_guid = '{saveSchedule.light_schedule_guid}'");
                    List<Dictionary<string, object>> ScheduleDevices = new List<Dictionary<string, object>>();
                    foreach (var a in saveSchedule.devicelist)
                    {
                        Dictionary<string, object> ScheduleDevice = new Dictionary<string, object>();
                        ScheduleDevice = new Dictionary<string, object>()
                        {
                            { "@light_schedule_guid", newguid},
                            { "@device_guid", a}
                        };
                        ScheduleDevices.Add(ScheduleDevice);
                    }
                    await backendRepository.AddMutiByCustomTable(ScheduleDevices, "schedule_device");

                    opeInput.parameter = JsonConvert.SerializeObject(saveSchedule);
                    // 若有變更才寫入 operation_log
                    if (saveSchedule.changeNames.Count > 0) {
                        await InsertOperation(opeInput);
                    }
                }
                apiResult.Code = "0000";
                apiResult.Data = "成功";
            }
            catch (Exception exception)
            {
                apiResult.Code = "9999";
                apiResult.Msg = "系統內部錯誤，請聯絡管理者。";
                Logger.LogError("【" + controllerName + "/" + actionName + "】" + exception.Message);
            }
            return Ok(apiResult);
        }


        [HttpPost]
        [Route("api/LightSchedule/ScheduleTable")]
        public async Task<ActionResult<ApiResult<List<ScheduleTable>>>> GetLightDevice(listfloors post)
        {
            List<ScheduleTable> ScheduleTable = new List<ScheduleTable>();
            ApiResult<List<ScheduleTable>> apiResult = new ApiResult<List<ScheduleTable>>();
            try
            {
                ScheduleTable = await backendRepository.GetAllAsync<ScheduleTable>($@"
                    select  ls.*,sd.devicecount from light_schedule ls
                    left join (select sd.light_schedule_guid, COUNT(*) devicecount from schedule_device sd group by sd.light_schedule_guid) sd on sd.light_schedule_guid = ls.light_schedule_guid
                    where ls.floor_guid in @floors and ls.deleted = 0
                    ",new { floors = post.Floors});
                apiResult.Code = "0000";
                apiResult.Data = ScheduleTable;
            }
            catch (Exception exception)
            {
                apiResult.Code = "9999";
                apiResult.Msg = "系統內部錯誤，請聯絡管理者。";
                Logger.LogError("【" + controllerName + "/" + actionName + "】" + exception.Message);
            }
            return Ok(apiResult);
        }

        [HttpPost]
        [Route("api/LightSchedule/GetOneschedule")]
        public async Task<ActionResult<ApiResult<Schedule>>> GetOneschedule(string schedule_guid)
        {
            ApiResult<Schedule> apiResult = new ApiResult<Schedule>();
            try
            {
                var OneScheduleTable = await backendRepository.GetOneAsync<Schedule>($@"
                select * from light_schedule where light_schedule_guid = '{schedule_guid}'
                ");
                apiResult.Code = "0000";
                apiResult.Data = OneScheduleTable;
            }
            catch (Exception exception)
            {
                apiResult.Code = "9999";
                apiResult.Msg = "系統內部錯誤，請聯絡管理者。";
                Logger.LogError("【" + controllerName + "/" + actionName + "】" + exception.Message);
            }
            return Ok(apiResult);
        }

        [HttpPost]
        [Route("api/LightSchedule/DeleteSchedule")]
        public async Task<ActionResult<ApiResult<string>>> DeleteSchedule(string schedule_guid)
        {
            ApiResult<string> apiResult = new ApiResult<string>();
            try
            {
                await backendRepository.DeleteOne(schedule_guid, "light_schedule", "light_schedule_guid");
                apiResult.Code = "0000";
            }
            catch (Exception ex)
            {
                apiResult.Code = "9999";
                apiResult.Msg = "系統內部錯誤，請聯絡管理者。";
                Logger.LogError("【" + controllerName + "/" + actionName + "】" + ex.Message);
            }
            return apiResult;
        }

        [HttpPost]
        [Route("api/LightSchedule/SendAPI")]
        public async Task<ActionResult<ApiResult<string>>> SendAPI(string guid)
        {
            ApiResult<string> apiResult = new ApiResult<string>();
            try
            {
                var TimeNow = DateTime.Now.ToString("dddd HH:mm");
                var oneSchedule = await backendRepository.GetOneAsync<Schedule>("light_schedule", $" light_schedule_guid = '{guid}'");
                var weeklistN = oneSchedule.week.Split(',');
                List<string> weeklist = new List<string>();
                foreach (var weekN in weeklistN)
                {
                    var week = weekN switch
                    {
                        "0" => "星期日",
                        "1" => "星期一",
                        "2" => "星期二",
                        "3" => "星期三",
                        "4" => "星期四",
                        "5" => "星期五",
                        "6" => "星期六",
                        _ => ""
                    };
                    weeklist.Add(week);
                }
                var Time = TimeNow.Split(" ");
                string check = "<real val='false' />";
                if(DateTime.Parse(Time[1])> DateTime.Parse(oneSchedule.start_time) && DateTime.Parse(Time[1]) <= DateTime.Parse(oneSchedule.end_time) && weeklist.Contains(Time[0]))
                {
                    check = "<real val='true' />";
                }
                else
                {
                    check = "<real val='false' />";
                }

                var deviceNumList = await backendRepository.GetAllAsync<string>(@$"select d.device_number from schedule_device sd left join device d on sd.device_guid = d.device_guid
                                                                                where light_schedule_guid = '{guid}'");

                var variableObix = await backendRepository.GetAllAsync<KeyValue>("SELECT system_value as Value, system_key as Name FROM variable WHERE deleted = 0 AND system_type = 'obixConfig'");
                string url = variableObix.Where(x => x.Name == "ApiBase").Select(x => x.Value).FirstOrDefault();
                string account = variableObix.Where(x => x.Name == "UserName").Select(x => x.Value).FirstOrDefault();
                string pass = variableObix.Where(x => x.Name == "Password").Select(x => x.Value).FirstOrDefault();
                foreach (var deviceNum in deviceNumList)
                {
                    Repository.Models.TagChangeFunction tagChange = new Repository.Models.TagChangeFunction();
                    var d = tagChange.AddStringIfStartsWithDigit(deviceNum, "$3");
                    var html = $"{url}obix/config/Arena/" + $"{d[0]}/{d[1]}/{d[2]}/{d[3]}/{deviceNum}/SSC/set";
                    string authInfo = account + ":" + pass;
                    authInfo = Convert.ToBase64String(Encoding.Default.GetBytes(authInfo));
                    HttpWebRequest request = (HttpWebRequest)WebRequest.Create(html);
                    request.Method = "POST";
                    request.Accept = "application/json; charset=utf-8";
                    request.Headers["Authorization"] = "Basic " + authInfo;
                    byte[] byteArray = Encoding.UTF8.GetBytes(check);
                    using (Stream reqStream = request.GetRequestStream())
                    {
                        reqStream.Write(byteArray, 0, byteArray.Length);
                    }
                    var response = (HttpWebResponse)request.GetResponse();
                    string strResponse = "";

                    using (var sr = new StreamReader(response.GetResponseStream()))
                    {
                        strResponse = sr.ReadToEnd();
                    }
                    if (strResponse.Contains("<err"))
                    {
                        Logger.LogWarning($"【LightScheduleJob 】【set niagara light value fail】[排程 名稱]：{oneSchedule.full_name},[設備 名稱]：{deviceNum}");
                    }
                }
                apiResult.Msg = "執行成功";
                apiResult.Code = "0000";
            }
            catch (Exception ex)
            {
                apiResult.Code = "9999";
                apiResult.Msg = "系統內部錯誤，請聯絡管理者。";
                Logger.LogError("【" + controllerName + "/" + actionName + "】" + ex.Message);
            }
            return Ok(apiResult);
        }

        public async Task<bool> InsertOperation(OperationLog input)
        {
            try
            {
                //記錄使用者操作紀錄
                Dictionary<string, object> userOperatorLog = new Dictionary<string, object>()
                        {
                            { "@user_guid", myUser.userinfo_guid },
                            { "@operation_type", input.operation_type }, //1：名稱修改
                            { "@building_tag", input.building_tag },
                            { "@main_system_tag", input.main_system_tag },
                            { "@sub_system_tag", input.sub_system_tag },
                            { "@floor_tag", input.floor_tag },
                            { "@device_guid", input.device_guid },
                            { "@action_name", input.action_name  },
                            { "@parameter", input.parameter },
                        };

                await backendRepository.AddOneByCustomTable(userOperatorLog, "operation_log");

                return true;
            }
            catch (Exception ex)
            {
                return false;
            }
        }
    }
}