ibms-dome/Backend/Controllers/SystemCategoryController.cs

using Backend.Models;
using Microsoft.AspNetCore.Mvc;
using Microsoft.Extensions.Logging;
using Repository.BackendRepository.Interface;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace Backend.Controllers
{
    public class SystemCategoryController : MybaseController<SystemCategoryController>
    {

        private readonly IBackendRepository backendRepository;

        public SystemCategoryController(IBackendRepository backendRepository)
        {
            this.backendRepository = backendRepository;
        }

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

        /// <summary>
        /// 取得系統大類清單
        /// </summary>
        /// <returns></returns>
        [HttpPost]
        public async Task<ApiResult<List<VariableInfo>>> SystemMainList()
        {
            ApiResult<List<VariableInfo>> apiResult = new ApiResult<List<VariableInfo>>();

            try
            {
                var sWhere = "deleted = 0 AND system_type = @System_type";

                var param = new { System_type = main_system_type };

                var systemMainList = await backendRepository.GetAllAsync<VariableInfo>("variable", sWhere, param, "system_priority ASC, created_at DESC");

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

            return apiResult;
        }

        /// <summary>
        /// 取得單一系統大類
        /// </summary>
        /// <param name="guid"></param>
        /// <returns></returns>
        [HttpPost]
        public async Task<ApiResult<VariableInfo>> GetOneSystemMain(int id)
        {
            ApiResult<VariableInfo> apiResult = new ApiResult<VariableInfo>();

            try
            {
                string sWhere = @$"deleted = @Deleted AND id = @id";

                object param = new { Deleted = 0, id = id};

                var systemMain = await backendRepository.GetOneAsync<VariableInfo>("variable", sWhere, param);

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

            return apiResult;
        }

        /// <summary>
        /// 新增 / 修改 系統大類資料
        /// </summary>
        /// <param name="post"></param>
        /// <returns></returns>
        [HttpPost]
        public async Task<ApiResult<string>> SaveSystemMain(VariableInfo post)
        {
            ApiResult<string> apiResult = new ApiResult<string>();

            try
            {
                string sWhere = @$"deleted = @Deleted AND id = @id";

                object param = new { Deleted = 0, id = post.id };

                var check = await backendRepository.GetOneAsync<string>($"select id from variable where id != {post.id} and system_type = '{main_system_type}' and system_value = '{post.system_value}' and deleted = 0;");
                if (!string.IsNullOrEmpty(check))
                {
                    apiResult.Code = "9998";
                    apiResult.Msg = "已有相同大類";
                    return apiResult;
                }
                var systemMain = await backendRepository.GetOneAsync<VariableInfo>("variable", sWhere, param);

                if (systemMain == null)
                {
                    //新增
                    //獲取最新的大類
                    sWhere = @$"deleted = @Deleted AND system_type = @System_type";
                    param = new { Deleted = 0, System_type = main_system_type };
                    var sOrder = @"id DESC LIMIT 1";
                    var latestVariable = await backendRepository.GetOneAsync<VariableInfo>("variable", sWhere, param, sOrder);

                    Dictionary<string, object> variableMainDic = new Dictionary<string, object>()
                    {
                        { "@system_type", main_system_type},
                        { "@system_key", post.System_key},
                        { "@system_value", post.system_value},
                        { "@system_remark", "系統類別(第2層)"},
                        { "@system_priority", latestVariable.system_priority + 1},
                        { "@created_by", myUserInfo.Userinfo_guid},
                        { "@created_at", DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss")}
                    };
                    await backendRepository.AddOneByCustomTable(variableMainDic, "variable");

                    apiResult.Code = "0000";
                    apiResult.Msg = "新增成功";
                }
                else
                {
                    Dictionary<string, object> variableMainDic = new Dictionary<string, object>()
                    {
                        { "@system_key", post.System_key},
                        { "@system_value", post.system_value},
                        { "@updated_by", myUserInfo.Userinfo_guid},
                        { "@updated_at", DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss")},
                    };

                    await backendRepository.UpdateOneByCustomTable(variableMainDic, "variable", "id='" + systemMain.id + "' AND deleted = 0");

                    var AuthCodes = await backendRepository.GetAllAsync<string>(
                        @$"select AuthCode
                            from auth_page ap
                            join variable sv on ap.ShowView = sv.id and sv.system_type = 'device_system_category_layer1'
                            where sv.system_parent_id = '{systemMain.id}'");

                    if(AuthCodes.Count > 0)
                    {
                        await backendRepository.ExecuteSql($@"UPDATE auth_page
                        SET MainName = '{post.System_key}'
                        WHERE AuthCode IN @authCode;",new { authCode = AuthCodes });
                    } 

                    apiResult.Code = "0000";
                    apiResult.Msg = "修改成功";
                }
            }
            catch (Exception exception)
            {
                apiResult.Code = "9999";
                apiResult.Msg = "系統內部錯誤，請聯絡管理者。";
                string json = System.Text.Json.JsonSerializer.Serialize(post);
                Logger.LogError("【" + controllerName + "/" + actionName + "】" + json);
                Logger.LogError("【" + controllerName + "/" + actionName + "】" + exception.Message);
            }

            return apiResult;
        }

        /// <summary>
        /// 刪除單一系統大類
        /// </summary>
        /// <param name="guid"></param>
        /// <returns></returns>
        [HttpPost]
        public async Task<ApiResult<string>> DeleteOneSystemMain(int id)
        {
            ApiResult<string> apiResult = new ApiResult<string>();

            try
            {
                string sWhere = @$"deleted = @Deleted AND id = @id";

                object param = new { Deleted = 0, id = id };

                var systemMain = await backendRepository.GetOneAsync<VariableInfo>("variable", sWhere, param);

                if (systemMain == null)
                {
                    apiResult.Code = "9998";
                    apiResult.Msg = "查無該系統大類";
                    return apiResult;
                }

                //檢查是否有未刪除的區域選單
                var sbuildMenu = $@"SELECT
                                        b.full_name
                                    FROM building_menu bm
                                    LEFT JOIN building b ON bm.building_tag = b.building_tag AND b.deleted = 0
                                    WHERE bm.main_system_tag = @System_Value
                                    GROUP BY b.full_name";

                var buildMenus = await backendRepository.GetAllAsync<string>(sbuildMenu, new { System_Value = systemMain.system_value });
                if (buildMenus.Count > 0)
                {
                    apiResult.Code = "9997";
                    apiResult.Msg = "區域選單中尚有棟別正在使用該系統大類，故無法刪除";
                    apiResult.Data = string.Join("<br>", buildMenus);
                    return apiResult;
                }

                //檢查底下是否有未刪除的系統小類
                string sqlSub = @$"SELECT id FROM variable
                                    WHERE deleted = @Deleted AND system_parent_id = @id";
                object sub_param = new { Deleted = 0, id = id };
                var v = await backendRepository.GetAllAsync<VariableInfo>(sqlSub, sub_param);

                if (v.Count > 0)
                {
                    apiResult.Code = "9997";
                    apiResult.Msg = "系統小類中尚有小類正在使用系統大類，故無法刪除";
                    apiResult.Data = string.Join("<br>", v.Where(x => x.id == id).Select(x => x.System_key).ToList());
                    return apiResult;
                }

                await backendRepository.DeleteOne(id.ToString(), "variable", "id");

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

            return apiResult;
        }

        /// <summary>
        /// 取得系統小類清單
        /// </summary>
        /// <param name="main_system_guid"></param>
        /// <returns></returns>
        [HttpPost]
        public async Task<ApiResult<List<VariableInfo>>> SystemSubList(int id)
        {
            ApiResult<List<VariableInfo>> apiResult = new ApiResult<List<VariableInfo>>();

            try
            {
                var sWhere = @"deleted = @Deleted AND system_parent_id = @id";

                object param = new { Deleted = 0, id = id};

                var systemSubs = await backendRepository.GetAllAsync<VariableInfo>("variable", sWhere, param, "system_priority ASC, created_at DESC");

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

            return apiResult;
        }

        /// <summary>
        /// 取得單一系統小類
        /// </summary>
        /// <param name="guid"></param>
        /// <returns></returns>
        [HttpPost]
        public async Task<ApiResult<VariableInfo>> GetOneSystemSub(int id)
        {
            ApiResult<VariableInfo> apiResult = new ApiResult<VariableInfo>();

            try
            {
                string sWhere = @$"deleted = @Deleted AND id = @id";

                object param = new { Deleted = 0, id = id };

                var systemSub = await backendRepository.GetOneAsync<VariableInfo>("variable", sWhere, param);

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

            return apiResult;
        }

        /// <summary>
        /// 新增 / 修改 系統小類資料
        /// </summary>
        /// <param name="post"></param>
        /// <returns></returns>
        [HttpPost]
        public async Task<ApiResult<string>> SaveSystemSub(VariableInfo post)
        {
            ApiResult<string> apiResult = new ApiResult<string>();

            try
            {
                var check = await backendRepository.GetOneAsync<string>($"select id from variable where id != {post.id} and system_type = '{sub_system_type}' and system_value = '{post.system_value}' and system_parent_id = {post.system_parent_id} and deleted = 0;");
                if (!string.IsNullOrEmpty(check))
                {
                    apiResult.Code = "9998";
                    apiResult.Msg = "已有相同小類";
                    return apiResult;
                }

                string sWhere = @$"deleted = @Deleted AND id = @id";

                object param = new { Deleted = 0, id = post.id };

                var systemSub = await backendRepository.GetOneAsync<VariableInfo>("variable", sWhere, param);

                if (systemSub == null)
                { 
                    //新增
                    //產生一組GUID
                    //獲取最新的大類
                    sWhere = @$"deleted = @Deleted AND system_type = @System_type";
                    param = new { Deleted = 0, System_type = sub_system_type };
                    var sOrder = @"id DESC LIMIT 1";
                    var latestVariable = await backendRepository.GetOneAsync<VariableInfo>("variable", sWhere, param, sOrder);

                    Dictionary<string, object> systemSubDic = new Dictionary<string, object>()
                    {
                        { "@system_type", sub_system_type},
                        { "@system_key", post.System_key},
                        { "@system_value", post.system_value},
                        { "@system_parent_id", post.system_parent_id},
                        { "@system_remark", "系統類別(第3層)"},
                        { "@system_priority", latestVariable.system_priority + 1},
                        { "@created_by", myUserInfo.Userinfo_guid},
                        { "@created_at", DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss")}
                    };
                    await backendRepository.AddOneByCustomTable(systemSubDic, "variable");

                    // 加入權限的功能統一由區域選單管理新增，故先註解
                    //var systemId = await backendRepository.GetOneAsync<int>($@"select id from variable where system_value = '{post.system_value}' and deleted = 0 and system_parent_id = '{post.system_parent_id}' and system_type = '{sub_system_type}'");
                    //var buildings = await backendRepository.GetAllAsync<string>($@"select building_tag from building where deleted = 0");
                    //var authCode = await backendRepository.GetOneAsync<int>($@"select max(cast(SUBSTRING(authcode, 2) AS UNSIGNED)) from auth_page where authcode like 'F%'");
                    //var parentSystemName = await backendRepository.GetOneAsync<string>($@"select system_key from variable where id = {post.system_parent_id}");
                    //StringBuilder sb = new StringBuilder();
                    //foreach (var b in buildings)
                    //{
                    //    sb.Append($@" insert into auth_page(AuthCode, AuthType, MainName, SubName, building_tag, ShowView, created_at)
                    //                    values ('F{++authCode}', '1', '{parentSystemName}', '{post.System_key}', '{b}', '{systemId}', Now());");
                    //}

                    //await backendRepository.ExecuteSql(sb.ToString());

                    apiResult.Code = "0000";
                    apiResult.Msg = "新增成功";
                }
                else
                {
                    Dictionary<string, object> systemSubDic = new Dictionary<string, object>()
                    {
                        
                        { "@system_key", post.System_key},
                        { "@system_value", post.system_value},
                        { "@updated_by", myUserInfo.Userinfo_guid},
                        { "@updated_at", DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss")},
                    };

                    await backendRepository.UpdateOneByCustomTable(systemSubDic, "variable", "id='" + systemSub.id + "'");

                    var AuthCodes = await backendRepository.GetAllAsync<string>(
                        @$"select AuthCode from auth_page ap 
                        where ap.ShowView = '{systemSub.id}'");

                    if (AuthCodes.Count > 0)
                    {
                        await backendRepository.ExecuteSql($@"UPDATE auth_page
                        SET SubName = '{post.System_key}'
                        WHERE AuthCode IN @authCode;", new { authCode = AuthCodes });
                    }

                    apiResult.Code = "0000";
                    apiResult.Msg = "修改成功";
                }
            }
            catch (Exception exception)
            {
                apiResult.Code = "9999";
                apiResult.Msg = "系統內部錯誤，請聯絡管理者。";
                string json = System.Text.Json.JsonSerializer.Serialize(post);
                Logger.LogError("【" + controllerName + "/" + actionName + "】" + json);
                Logger.LogError("【" + controllerName + "/" + actionName + "】" + exception.Message);
            }

            return apiResult;
        }

        /// <summary>
        /// 刪除單一系統小類
        /// </summary>
        /// <param name="guid"></param>
        /// <returns></returns>
        [HttpPost]
        public async Task<ApiResult<string>> DeleteOneSystemSub(string id)
        {
            ApiResult<string> apiResult = new ApiResult<string>();

            try
            {
                string sWhere = @$"deleted = @Deleted AND id = @id";

                object param = new { Deleted = 0, id = id };

                var systemSub = await backendRepository.GetOneAsync<VariableInfo>("variable", sWhere, param);

                if (systemSub == null)
                {
                    apiResult.Code = "9998";
                    apiResult.Msg = "查無該系統小類";
                    return apiResult;
                }

                //檢查是否有未刪除的區域選單
                var sbuildMenu = $@"SELECT
	                                    CONCAT(b.full_name, ' - ', sv.system_key)
                                    FROM building_menu bm
                                    LEFT JOIN building b ON bm.building_tag = b.building_tag AND b.deleted = 0
                                    LEFT JOIN variable sv ON bm.sub_system_tag = sv.system_value AND sv.deleted = 0
                                    LEFT JOIN variable mv ON sv.system_parent_id = mv.id AND mv.deleted = 0
                                    WHERE sv.id = @id and bm.is_link > 0";

                var buildMenus = await backendRepository.GetAllAsync<string>(sbuildMenu, new { id = id });
                if (buildMenus.Count > 0)
                {
                    apiResult.Code = "9997";
                    apiResult.Msg = "區域選單中尚有選單正在使用該系統小類，故無法刪除";
                    apiResult.Data = string.Join("<br>", buildMenus);
                    return apiResult;
                }

                //檢查是否有未刪除的系統小類樓層
                var ssubSystemFloor = $@"SELECT CONCAT(b.full_name, '-', mv.system_key, '-', sv.system_key, '-', f.full_name)
                                        FROM sub_system_floor ssf
                                        LEFT JOIN building b ON ssf.building_tag = b.building_tag AND b.deleted = 0
                                        LEFT JOIN variable sv ON sv.system_value = ssf.sub_system_tag AND sv.deleted = 0
                                        LEFT JOIN variable mv ON sv.system_parent_id = mv.id AND mv.deleted = 0
                                        LEFT JOIN floor f ON ssf.floor_tag = f.full_name AND f.deleted = 0
                                        WHERE sv.id = @id AND ssf.deleted = 0 and is_link = 1";

                var subSystemFloor = await backendRepository.GetAllAsync<string>(sbuildMenu, new { id = id });
                if (subSystemFloor.Count > 0)
                {
                    apiResult.Code = "9997";
                    apiResult.Msg = "區域選單中尚有樓層正在使用該系統小類，故無法刪除";
                    apiResult.Data = string.Join("<br>", subSystemFloor);
                    return apiResult;
                }

                //檢查是否有未刪除的設備項目
                var sdeviceItem = $@"SELECT 
		                                di.full_name
                                    FROM device_item di
                                    INNER JOIN variable sv on di.device_name_tag = sv.system_value
                                    WHERE sv.deleted = 0 AND sv.id = @id AND di.deleted = 0 and di.is_link = 1";

                var deviceItems = await backendRepository.GetAllAsync<string>(sdeviceItem, new { id = id });
                if (deviceItems.Count > 0)
                {
                    apiResult.Code = "9997";
                    apiResult.Msg = "設備項目中尚有項目正在使用該系統小類，故無法刪除";
                    apiResult.Data = string.Join("<br>", deviceItems);
                    return apiResult;
                }

                await backendRepository.DeleteOne(id, "variable", "id");

                await backendRepository.ExecuteSql($@"delete from auth_page where AuthCode like 'F%' and AuthType = 1 and ShowView = '{id}'");
                apiResult.Code = "0000";
                apiResult.Msg = "刪除成功";
            }
            catch (Exception exception)
            {
                apiResult.Code = "9999";
                apiResult.Msg = "系統內部錯誤，請聯絡管理者。";
                Logger.LogError("【" + controllerName + "/" + actionName + "】" + "id=" + id);
                Logger.LogError("【" + controllerName + "/" + actionName + "】" + exception.Message);
            }

            return apiResult;
        }

        [HttpPost]
        public async Task<ApiResult<string>> Savedevice_item(Device_item device_Item)
        {
            ApiResult<string> apiResult = new ApiResult<string>();

            try
            {
                var main_tag = await backendRepository.GetOneAsync<string>($@"SELECT system_value FROM variable WHERE id = @id", new { id = device_Item.device_system_tag });
                var sub_tag = await backendRepository.GetOneAsync<string>($@"SELECT system_value FROM variable WHERE id = @id", new { id = device_Item.device_name_tag });
                //檢查是否有未刪除的區域選單
                if (device_Item.is_show_riserDiagram == 1)
                {
                    var sql_show_riserDiagram = $@"SELECT di.id FROM device_item di 
                                                    WHERE di.id != @id AND di.deleted = 0 AND is_show_riserDiagram = 1 and device_system_tag = @device_system_tag 
                                                            and device_name_tag = @device_name_tag";

                    var deviceItemId = await backendRepository.GetAllAsync<int>(sql_show_riserDiagram,
                                            new { id = device_Item.id, device_system_tag = main_tag, device_name_tag = sub_tag });

                    if (deviceItemId.Count() > 0)
                    {
                        foreach (var id in deviceItemId)
                        {
                            Dictionary<string, object> Device_itemDic = new Dictionary<string, object>()
                            {
                                { "@is_show_riserDiagram", false},
                                { "@updated_by", myUserInfo.Userinfo_guid},
                                { "@updated_at", DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss")},
                            };

                            await backendRepository.UpdateOneByCustomTable(Device_itemDic, "device_item", "id='" + id + "'");
                        }
                    }
                }

                if (device_Item.id == 0)
                {
                    var building = await backendRepository.GetAllAsync<string>("select building_tag from building where deleted = 0");
                    foreach (var b in building)
                    {
                        //新增
                        Dictionary<string, object> Device_itemDic = new Dictionary<string, object>()
                        {
                            { "@device_system_tag", main_tag},
                            { "@device_name_tag", sub_tag},
                            { "@full_name", device_Item.full_name},
                            { "@points", device_Item.points},
                            { "@unit", device_Item.unit},
                            { "@is_show", device_Item.is_show},
                            { "@is_show_riserDiagram", device_Item.is_show_riserDiagram},
                            { "@is_controll", device_Item.is_controll},
                            { "@is_bool", device_Item.is_bool},
                            { "@is_show_history", device_Item.is_show_history},
                            { "@created_by", myUserInfo.Userinfo_guid},
                            { "@device_building_tag", b},
                            { "@is_link", "1"},
                        };
                        await backendRepository.AddOneByCustomTable(Device_itemDic, "device_item");

                    }

                    apiResult.Code = "0000";
                    apiResult.Msg = "新增成功";
                }
                else
                {
                    var sql = $@"SELECT di.id FROM device_item di 
                                                    WHERE di.deleted = 0 AND device_system_tag = @device_system_tag and device_name_tag = @device_name_tag and points = @points";

                    var deviceItemId = await backendRepository.GetAllAsync<int>(sql,
                                            new { points = device_Item.points, device_system_tag = main_tag, device_name_tag = sub_tag });

                    if (deviceItemId.Count() > 0)
                    {
                        foreach (var id in deviceItemId)
                        {
                            Dictionary<string, object> Device_itemDic = new Dictionary<string, object>()
                            {
                                { "@full_name", device_Item.full_name},
                                { "@points", device_Item.points},
                                { "@unit", device_Item.unit},
                                { "@is_show", device_Item.is_show},
                                { "@is_show_riserDiagram", device_Item.is_show_riserDiagram},
                                { "@is_controll", device_Item.is_controll},
                                { "@is_bool", device_Item.is_bool},
                                { "@is_show_history", device_Item.is_show_history},
                                { "@updated_by", myUserInfo.Userinfo_guid},
                                { "@updated_at", DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss")},
                            };

                            await backendRepository.UpdateOneByCustomTable(Device_itemDic, "device_item", "id='" + id + "'");
                        }
                    }

                    apiResult.Code = "0000";
                    apiResult.Msg = "修改成功";
                }
            }
            catch (Exception exception)
            {
                apiResult.Code = "9999";
                apiResult.Msg = "系統內部錯誤，請聯絡管理者。";
                string json = System.Text.Json.JsonSerializer.Serialize(device_Item);
                Logger.LogError("【" + controllerName + "/" + actionName + "】" + json);
                Logger.LogError("【" + controllerName + "/" + actionName + "】" + exception.Message);
            }

            return apiResult;
        }

        [HttpPost]
        public async Task<ApiResult<List<Device_item>>> DeviceItemTable(int id)
        {
            ApiResult<List<Device_item>> apiResult = new ApiResult<List<Device_item>>();
            try
            {
                var building_tag = await backendRepository.GetOneAsync<string>($@"select system_value from variable where system_type = 'project_name' and deleted = 0");
                //var sql = @"SELECT di.*
                //            FROM device_item di
                //            JOIN variable sv ON di.device_name_tag = sv.system_value
                //            JOIN variable mv ON sv.system_parent_id = mv.id AND di.device_system_tag = mv.system_value
                //            WHERE sv.id = @id AND di.deleted = @Deleted and di.device_building_tag = @building_tag and di.is_link = 1";
                var sql = @"SELECT min(di.id) as id, di.full_name, di.points, di.unit, di.is_show, di.is_show_riserDiagram, di.is_controll, di.is_bool, di.is_link, di.is_show_history, di.device_system_tag, di.device_name_tag
                            FROM device_item di
                            JOIN variable sv ON di.device_name_tag = sv.system_value
                            JOIN variable mv ON sv.system_parent_id = mv.id AND di.device_system_tag = mv.system_value
                            WHERE sv.id = @id and di.deleted = 0 and di.is_link = 1
                            group by di.full_name, di.points, di.unit, di.is_show, di.is_show_riserDiagram, di.is_controll, di.is_bool, di.is_link, di.is_show_history, di.device_system_tag, di.device_name_tag";

                object param = new { Deleted = 0, id = id, building_tag = building_tag.Split("/")[1] };

                var systemSubs = await backendRepository.GetAllAsync<Device_item>(sql, param);

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

            return apiResult;
        }

        [HttpPost]
        public async Task<ApiResult<Device_item>> GetOneDeviceItem(int id)
        {
            ApiResult<Device_item> apiResult = new ApiResult<Device_item>();

            try
            {
                string sWhere = @$"deleted = @Deleted AND id = @id";

                object param = new { Deleted = 0, id = id };

                var Deviceitem = await backendRepository.GetOneAsync<Device_item>("device_item", sWhere, param);

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

            return apiResult;
        }

        [HttpPost]
        public async Task<ApiResult<string>> DeleteOneSystemSubDeviceItem(int id)
        {
            ApiResult<string> apiResult = new ApiResult<string>();

            try
            {
                string sWhere = @$"deleted = @Deleted AND id = @id";

                object param = new { Deleted = 0, id = id };

                var device_Item = await backendRepository.GetOneAsync<Device_item>("device_item", sWhere, param);

                if (device_Item == null)
                {
                    apiResult.Code = "9998";
                    apiResult.Msg = "查無該設備項目";
                    return apiResult;
                }

                string sql = $@"update device_item set deleted = 1 where device_system_tag = '{device_Item.device_system_tag}' and device_name_tag = '{device_Item.device_name_tag}' and deleted = 0
                                and points = '{device_Item.points}'";
                await backendRepository.ExecuteSql(sql);

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

            return apiResult;
        }

        public async Task<ApiResult<bool>> HaveSamePoints(Checksame post)
        {
            ApiResult<bool> apiResult = new ApiResult<bool>();
            try
            {
                var sql = $@"SELECT *
                            FROM device_item di
                            INNER JOIN variable v ON di.device_name_tag = v.system_value
                            WHERE v.id = @SubId AND di.id != @Id AND points = @Points";

                var param = new { SubId = post.subId, Points = post.points, id = post.id };
                var point = await backendRepository.GetOneAsync<Device_item>(sql, param);
                if (point != null)
                {
                    apiResult.Data = true;
                }
                else
                {
                    apiResult.Data = false;
                }
                apiResult.Code = "0000";
            }
            catch (Exception exception)
            {
                apiResult.Code = "9999";
                apiResult.Msg = "系統內部錯誤，請聯絡管理者。";
                string json = System.Text.Json.JsonSerializer.Serialize(post);
                Logger.LogError("【" + controllerName + "/" + actionName + "】" + json);
                Logger.LogError("【" + controllerName + "/" + actionName + "】" + exception.Message);
            }
            return apiResult;
        }

        public async Task<ApiResult<Deletebool>> CheckCanDelete(guidandsubguid post)
        {
            ApiResult<Deletebool> apiResult = new ApiResult<Deletebool>();
            try
            {
                var tags = await backendRepository.GetAllAsync<Tags>(
                    @$"select * from (select dk.device_building_tag ,dk.device_name_tag,dk.device_system_tag from device_kind dk  where dk.device_normal_point_id = @id and is_link = 1) dkn
                    union(select dk.device_building_tag, dk.device_name_tag, dk.device_system_tag from device_kind dk  where dk.device_close_point_id = @id)
                    union(select dk.device_building_tag, dk.device_name_tag, dk.device_system_tag from device_kind dk  where dk.device_error_point_id = @id)", new { id = post.guid});

                if (tags.Count == 0)
                {
                    Deletebool deletebool = new Deletebool()
                    {
                        Delete = false,
                        Reason = ""
                    };
                    apiResult.Data = deletebool;
                }
                else
                {
                    Deletebool deletebool = new Deletebool()
                    {
                        Delete = true,
                        Reason = ""
                    };
                    var unionsql = "";
                    var last = tags.Last();
                    var sub_system = await backendRepository.GetOneAsync<VariableInfo>($@"SELECT * FROM variable WHERE id = @id", new { id = post.subguid });
                    foreach (var tag in tags)
                    {
                        unionsql += $@"select d.building_tag,d.device_system_tag,d.device_name_tag,d.device_last_name from device d where d.device_name_tag = '{sub_system.system_value}' 
                                        and d.device_building_tag = '{tag.device_building_tag}' and d.device_system_tag = '{tag.device_system_tag}' and d.device_name_tag = '{tag.device_name_tag}' 
                                        group by d.building_guid,d.main_system_guid,d.sub_system_guid,d.device_name_tag";
                        if (!last.Equals(tag))
                        {
                            unionsql += " union ";
                        }
                    }
                    var sql = @$"select mv.system_key msname, b.full_name bname, sv.system_key subname,de.device_last_name as device_name_tag from 
                                ({unionsql}) de
                                left join variable mv on mv.system_value = de.device_system_tag and mv.system_type = @main_system_type
                                left join building b on b.building_tag = de.building_tag
                                left join variable sv on sv.system_value = de.device_name_tag and sv.system_type = @sub_system_type";

                    var param = new { main_system_type = main_system_type, sub_system_type = sub_system_type};
                    var names = await backendRepository.GetAllAsync<GetCheckName>(sql, param);
                    var count = 0;
                    foreach (var name in names)
                    {
                        count++;
                        deletebool.Reason += count.ToString() + "." + name.bname + "-" + name.msname + "-" + name.subname + "-" + name.device_name_tag + "<br>";
                    }
                    apiResult.Data = deletebool;
                }
                apiResult.Code = "0000";

            }
            catch (Exception exception)
            {
                apiResult.Code = "9999";
                apiResult.Msg = "系統內部錯誤，請聯絡管理者。";
                string json = System.Text.Json.JsonSerializer.Serialize(post);
                Logger.LogError("【" + controllerName + "/" + actionName + "】" + json);
                Logger.LogError("【" + controllerName + "/" + actionName + "】" + exception.Message);
            }
            return apiResult;
        }

        public async Task<ApiResult<Deletebool>> CheckCanSubDelete(string guid)
        {
            ApiResult<Deletebool> apiResult = new ApiResult<Deletebool>();
            try
            {
                var text = "";
                var item = await backendRepository.GetAllAsync<string>(@$"select device_item_guid from device_item where deleted = 0 and sub_system_guid = '{guid}'");
                if(item.Count > 0)
                {
                    text += "項目還有尚未刪除<br>";
                }
                var menu = await backendRepository.GetAllAsync<string>($"select sub_system_guid from building_menu where sub_system_guid = '{guid}'");
                if (menu.Count > 0)
                {
                    text += "區域選單還有尚未刪除<br>";
                }
                Deletebool deletebool = new Deletebool()
                {
                    Delete = false,
                    Reason = ""
                };
                if (text != "")
                {
                    deletebool.Delete = true;
                    deletebool.Reason = text;
                }
                else
                {
                    deletebool.Delete = false;
                }
                apiResult.Data = deletebool;
                apiResult.Code = "0000";
            }
            catch (Exception exception)
            {
                apiResult.Code = "9999";
                apiResult.Msg = "系統內部錯誤，請聯絡管理者。";
                Logger.LogError("【" + controllerName + "/" + actionName + "】" + guid);
                Logger.LogError("【" + controllerName + "/" + actionName + "】" + exception.Message);
            }
            return apiResult;
        }
    }
}