ZLPMS/PMS.DBService/CloudMonitorManage/CloudMonitorService.cs

using PMS.BusinessModels.CloudMonitorManage;
using PMS.Plugins.Common;
using QWPlatform.IService;
using QWPlatform.SystemLibrary;
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace PMS.DBService.CloudMonitorManage
{
    public class CloudMonitorService : DataServiceBase
    {
        /// <summary>
        /// 根据项目id获取所有自定义预警配置
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public List<CustomAlertModel> GetCustomAlertModelByItemId(string id)
        {
            var sql = @"SELECT ID, 渠道ID,(SELECT q.名称  FROM 渠道信息 q WHERE q.ID =a.渠道ID) AS 渠道名称,
		项目ID,(SELECT x.名称  FROM 项目信息 x WHERE x.ID =a.项目ID) AS 项目名称,
		服务器ID,(SELECT f.IP地址  FROM 项目服务器 f WHERE f.ID =a.服务器ID) AS 服务器名称,
		预警类型, 指标名称, 频率,
		创建人ID, (SELECT x.姓名  FROM 系统账户 x WHERE x.ID  =a.创建人ID) AS 创建人名称,
        数据库ID,(SELECT x.数据库类型  FROM 数据库信息 x WHERE x.ID  =a.数据库ID) AS 数据库类型,
        (SELECT x.名称  FROM 数据库信息 x WHERE x.ID  =a.数据库ID) AS 数据库名称,
        (SELECT x.数据库连接串  FROM 数据库信息 x WHERE x.ID  =a.数据库ID) AS 数据库连接串,
		创建时间, 最后修改时间, 数据库查询SQL, 预警通知内容模板，是否启用
		FROM 自定义预警配置 a WHERE a.项目ID in (Select Column_Value From Table(f_Split_String(:项目ID, ',')))";
            var dt = SqlBuilder.SqlText(sql)
                 .Parameters("项目ID", id)
                 .Select();
            return dt.ToList<CustomAlertModel>() ?? new List<CustomAlertModel>();
        }

        /// <summary>
        /// 根据项目id获取 启动的自定义预警配置
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public List<CustomAlertModel> GetCustomAlertModelByItemIdAndStart(string id)
        {
            var sql = @"SELECT ID, 渠道ID,(SELECT q.名称  FROM 渠道信息 q WHERE q.ID =a.渠道ID) AS 渠道名称,
		项目ID,(SELECT x.名称  FROM 项目信息 x WHERE x.ID =a.项目ID) AS 项目名称,
		服务器ID,(SELECT f.IP地址  FROM 项目服务器 f WHERE f.ID =a.服务器ID) AS 服务器名称,
		预警类型, 指标名称, 频率,
		创建人ID, (SELECT x.姓名  FROM 系统账户 x WHERE x.ID  =a.创建人ID) AS 创建人名称,
        数据库ID,(SELECT x.数据库类型  FROM 数据库信息 x WHERE x.ID  =a.数据库ID) AS 数据库类型,
        (SELECT x.名称  FROM 数据库信息 x WHERE x.ID  =a.数据库ID) AS 数据库名称,
        (SELECT x.数据库连接串  FROM 数据库信息 x WHERE x.ID  =a.数据库ID) AS 数据库连接串,
		创建时间, 最后修改时间, 数据库查询SQL, 预警通知内容模板，是否启用
		FROM 自定义预警配置 a WHERE a.项目ID in (Select Column_Value From Table(f_Split_String(:项目ID, ','))) and 是否启用 = 1";
            var dt = SqlBuilder.SqlText(sql)
                 .Parameters("项目ID", id)
                 .Select();
            return dt.ToList<CustomAlertModel>() ?? new List<CustomAlertModel>();
        }

        /// <summary>
        /// 根据项目id获取 启动的自定义服务配置
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public List<CustomAPIModel> GetCustomServerModelByItemIdAndStart(string id)
        {
            var sql = @"SELECT ID, 渠道ID,(SELECT q.名称  FROM 渠道信息 q WHERE q.ID =a.渠道ID) AS 渠道名称,
		项目ID,(SELECT x.名称  FROM 项目信息 x WHERE x.ID =a.项目ID) AS 项目名称,
	    服务名称, 频率,
		创建人ID, (SELECT x.姓名  FROM 系统账户 x WHERE x.ID  =a.创建人ID) AS 创建人名称,
		创建时间, 最后修改时间, 配置, 是否启用
		FROM 自定义服务配置 a WHERE a.项目ID in (Select Column_Value From Table(f_Split_String(:项目ID, ','))) and 是否启用 = 1";
            var dt = SqlBuilder.SqlText(sql)
                 .Parameters("项目ID", id)
                 .Select();
            return dt.ToList<CustomAPIModel>() ?? new List<CustomAPIModel>();
        }

        /// <summary>
        /// 查询单条数据
        /// </summary>
        /// <param name="model"></param>
        /// <returns></returns>
        public CustomAPIModel GetCustomServiceInfoById(string id)
        {
            var dt = this.SelectBuilder.From("自定义服务配置")
                        .Columns("*")
                        .Where("ID", id)
                        .Select();
            return dt.ToList<CustomAPIModel>()?.FirstOrDefault();
        }

        /// <summary>
        /// 根据渠道id获取所有自定义预警配置
        /// </summary>
        /// <param name="channelId"></param>
        /// <returns></returns>
        public List<CustomAlertModel> GetCustomAlertModelByChannelId(string channelId, string search)
        {
            var sql = @"SELECT * FROM (SELECT ID, 渠道ID,(SELECT q.名称  FROM 渠道信息 q WHERE q.ID =a.渠道ID) AS 渠道名称,
		项目ID,(SELECT x.名称  FROM 项目信息 x WHERE x.ID =a.项目ID) AS 项目名称,
		服务器ID,(SELECT f.IP地址  FROM 项目服务器 f WHERE f.ID =a.服务器ID) AS 服务器名称,
		预警类型, 指标名称, 频率,
		创建人ID, (SELECT x.姓名  FROM 系统账户 x WHERE x.ID  =a.创建人ID) AS 创建人名称,
        数据库ID,(SELECT x.数据库类型  FROM 数据库信息 x WHERE x.ID  =a.数据库ID) AS 数据库类型,
        (SELECT x.名称  FROM 数据库信息 x WHERE x.ID  =a.数据库ID) AS 数据库名称,
        (SELECT x.数据库连接串  FROM 数据库信息 x WHERE x.ID  =a.数据库ID) AS 数据库连接串,
		创建时间, 最后修改时间, 数据库查询SQL, 预警通知内容模板，是否启用
		FROM 自定义预警配置 a WHERE a.渠道ID in (Select Column_Value From Table(f_Split_String(:渠道ID, ',')))) 
        Where 渠道名称 Like :查询条件 Or 项目名称 Like :查询条件 Or 指标名称 Like :查询条件";
            var dt = SqlBuilder.SqlText(sql)
                 .Parameters("渠道ID", channelId)
                 .Parameters("查询条件", "%" + search + "%")
                 .Select();
            return dt.ToList<CustomAlertModel>() ?? new List<CustomAlertModel>();
        }

        /// <summary>
        /// 根据渠道id获取所有自定义API预警配置
        /// </summary>
        /// <param name="channelId"></param>
        /// <returns></returns>
        public List<CustomAPIModel> GetCustomAPIModelByChannelId(string channelId, string search)
        {
            var sql = @"SELECT * FROM (SELECT ID, 渠道ID,(SELECT q.名称  FROM 渠道信息 q WHERE q.ID =a.渠道ID) AS 渠道名称,
		项目ID,(SELECT x.名称  FROM 项目信息 x WHERE x.ID =a.项目ID) AS 项目名称,
	    服务名称, 频率,
		创建人ID, (SELECT x.姓名  FROM 系统账户 x WHERE x.ID  =a.创建人ID) AS 创建人名称,
		创建时间, 最后修改时间, 配置, 是否启用
		FROM 自定义服务配置 a WHERE a.渠道ID in (Select Column_Value From Table(f_Split_String(:渠道ID, ',')))) 
        Where 渠道名称 Like :查询条件 Or 项目名称 Like :查询条件 Or 服务名称 Like :查询条件";
            var dt = SqlBuilder.SqlText(sql)
                 .Parameters("渠道ID", channelId)
                 .Parameters("查询条件", "%" + search + "%")
                 .Select();
            return dt.ToList<CustomAPIModel>() ?? new List<CustomAPIModel>();
        }

        /// <summary>
        /// 根据创建人id获取所有自定义预警配置
        /// </summary>
        /// <param name="creatorId"></param>
        /// <returns></returns>
        public List<CustomAlertModel> GetCustomAlertModelByCreatorId(string creatorId)
        {
            var sql = @"SELECT ID, 渠道ID,(SELECT q.名称  FROM 渠道信息 q WHERE q.ID =a.渠道ID) AS 渠道名称,
		项目ID,(SELECT x.名称  FROM 项目信息 x WHERE x.ID =a.项目ID) AS 项目名称,
		服务器ID,(SELECT f.IP地址  FROM 项目服务器 f WHERE f.ID =a.服务器ID) AS 服务器名称,
		预警类型, 指标名称, 频率,
		创建人ID, (SELECT x.姓名  FROM 系统账户 x WHERE x.ID  =a.创建人ID) AS 创建人名称,
        数据库ID,(SELECT x.数据库类型  FROM 数据库信息 x WHERE x.ID  =a.数据库ID) AS 数据库类型,
        (SELECT x.名称  FROM 数据库信息 x WHERE x.ID  =a.数据库ID) AS 数据库名称,
        (SELECT x.数据库连接串  FROM 数据库信息 x WHERE x.ID  =a.数据库ID) AS 数据库连接串,
		创建时间, 最后修改时间, 数据库查询SQL, 预警通知内容模板，是否启用
		FROM 自定义预警配置 a WHERE a.创建人ID in (Select Column_Value From Table(f_Split_String(:创建人ID, ',')))";
            var dt = SqlBuilder.SqlText(sql)
                 .Parameters("创建人ID", creatorId)
                 .Select();
            return dt.ToList<CustomAlertModel>() ?? new List<CustomAlertModel>();
        }

        /// <summary>
        /// 获取启动的所有自定义预警配置
        /// </summary>
        /// <returns></returns>
        public List<CustomAlertModel> GetCustomAlertModelByStart()
        {
            var sql = @"SELECT ID, 渠道ID,(SELECT q.名称  FROM 渠道信息 q WHERE q.ID =a.渠道ID) AS 渠道名称,
		项目ID,(SELECT x.名称  FROM 项目信息 x WHERE x.ID =a.项目ID) AS 项目名称,
		服务器ID,(SELECT f.IP地址  FROM 项目服务器 f WHERE f.ID =a.服务器ID) AS 服务器名称,
		预警类型, 指标名称, 频率,
		创建人ID, (SELECT x.姓名  FROM 系统账户 x WHERE x.ID  =a.创建人ID) AS 创建人名称,
        数据库ID,(SELECT x.数据库类型  FROM 数据库信息 x WHERE x.ID  =a.数据库ID) AS 数据库类型,
        (SELECT x.名称  FROM 数据库信息 x WHERE x.ID  =a.数据库ID) AS 数据库名称,
        (SELECT x.数据库连接串  FROM 数据库信息 x WHERE x.ID  =a.数据库ID) AS 数据库连接串,
		创建时间, 最后修改时间, 数据库查询SQL, 预警通知内容模板，是否启用
		FROM 自定义预警配置 a WHERE 是否启用 = 1";
            var dt = SqlBuilder.SqlText(sql)
                 .Select();
            return dt.ToList<CustomAlertModel>() ?? new List<CustomAlertModel>();
        }

        /// <summary>
        /// 获取服务器配置，根据项目id过滤
        /// </summary>
        /// <param name="itemid"></param>
        /// <returns></returns>
        public List<ServerInfoModel> GetServerByItemId(string itemid)
        {
            var sql = @"SELECT  ID, 项目ID,IP地址,
		(SELECT LISTAGG (显示名, ',') WITHIN GROUP (ORDER BY 显示名) FROM 基础编码 WHERE  分类id = '1A2416AF-B98A-45E6-BAD0-F7C32651E0E8' AND 代码 IN (SELECT Column_Value FROM TABLE (f_Split_String (类型, ',')))) AS 类型显示,类型,
		(SELECT LISTAGG (显示名, ',') WITHIN GROUP (ORDER BY 显示名) FROM 基础编码 WHERE  分类id = 'C6883DEB-D4AF-4EE7-A0D8-5E19922D35FE' AND 代码 IN (SELECT Column_Value FROM TABLE (f_Split_String (安装服务, ',')))) AS 安装服务显示,安装服务,
        账号,密码,服务发现端口,创建时间
FROM 项目服务器
WHERE   项目ID in (Select Column_Value From Table(f_Split_String(:项目ID, ',')))
ORDER BY 创建时间 DESC";
            var dt = SqlBuilder.SqlText(sql)
                 .Parameters("项目ID", itemid)
                 .Select();
            return dt.ToList<ServerInfoModel>() ?? new List<ServerInfoModel>();
        }

        public bool InsertCustomAlert(CustomAlertModel model)
        {
            return this.InsertBuilder.Insert("自定义预警配置")
                     .Column("ID", model.ID)
                     .Column("渠道ID", model.渠道ID)
                     .Column("项目ID", model.项目ID)
                     .Column("服务器ID", model.服务器ID)
                     .Column("预警类型", model.预警类型)
                     .Column("指标名称", model.指标名称)
                     .Column("频率", model.频率)
                     .Column("创建人ID", model.创建人ID)
                     .Column("数据库ID", model.数据库ID)
                     .Column("创建时间", DateTime.Now)
                     .Column("最后修改时间", DateTime.Now)
                     .Column("数据库查询SQL", model.数据库查询SQL)
                     .Column("预警通知内容模板", model.预警通知内容模板)
                     .Column("是否启用", model.是否启用)
                     .Execute() > 0;
        }

        public bool InsertCustomAPI(CustomAPIModel model)
        {
            return this.InsertBuilder.Insert("自定义服务配置")
                     .Column("ID", model.ID)
                     .Column("渠道ID", model.渠道ID)
                     .Column("项目ID", model.项目ID)
                     .Column("服务名称", model.服务名称)
                     .Column("频率", model.频率)
                     .Column("配置", model.配置)
                     .Column("创建人ID", model.创建人ID)
                     .Column("创建时间", DateTime.Now)
                     .Column("最后修改时间", DateTime.Now)
                     .Column("最后修改人ID", model.创建人ID)
                     .Column("是否启用", model.是否启用)
                     .Execute() > 0;
        }

        public bool UpdateCustomAlert(CustomAlertModel model)
        {
            return this.UpdateBuilder.Update("自定义预警配置")
                     .Column("渠道ID", model.渠道ID)
                     .Column("项目ID", model.项目ID)
                     .Column("服务器ID", model.服务器ID)
                     .Column("预警类型", model.预警类型)
                     .Column("指标名称", model.指标名称)
                     .Column("频率", model.频率)
                     .Column("数据库ID", model.数据库ID)
                     .Column("最后修改时间", DateTime.Now)
                     .Column("数据库查询SQL", model.数据库查询SQL)
                     .Column("预警通知内容模板", model.预警通知内容模板)
                     .Column("是否启用", model.是否启用)
                     .Where("ID", model.ID)
                     .Execute() > 0;
        }

        public bool UpdateCustomAPI(CustomAPIModel model)
        {
            return this.UpdateBuilder.Update("自定义服务配置")
                     .Column("渠道ID", model.渠道ID)
                     .Column("项目ID", model.项目ID)
                     .Column("服务名称", model.服务名称)
                     .Column("频率", model.频率)
                     .Column("配置", model.配置)
                     .Column("最后修改时间", DateTime.Now)
                     .Column("是否启用", model.是否启用)
                     .Where("ID", model.ID)
                     .Execute() > 0;
        }

        /// <summary>
        /// 根据id删除
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public bool DeleteCustomAlertById(string id)
        {
            return this.DeleteBulider.Delete("自定义预警配置")
                .Where("ID", id)
                .Execute() > 0;
        }

        /// <summary>
        /// 根据id删除API
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public bool DeleteCustomAPIById(string id)
        {
            return this.DeleteBulider.Delete("自定义服务配置")
                .Where("ID", id)
                .Execute() > 0;
        }

        /// <summary>
        /// 插入服务器指标
        /// </summary>
        /// <param name="model"></param>
        /// <returns></returns>
        public bool InsertServerInfo(ServerIndexInfoModel model)
        {
            var sql = @"INSERT INTO 服务器指标 (""ID"", ""渠道ID"", ""项目ID"", ""服务器ID"", ""CPU使用率"", ""内存使用率"", ""磁盘使用率"", ""最后修改时间"")
SELECT  :ID AS ID, b.渠道ID, a.项目ID, a.ID AS 服务器ID, :CPU使用率 AS CPU使用率, :内存使用率 AS 内存使用率, :磁盘使用率 AS 磁盘使用率, SYSDATE
FROM    项目服务器 a
LEFT JOIN 项目信息 b ON a.项目ID = b.ID
WHERE   a.ID = :服务器ID ";
            return SqlBuilder.SqlText(sql)
                .Parameters("ID", model.ID)
                .Parameters("CPU使用率", model.CPU使用率)
                .Parameters("内存使用率", model.内存使用率)
                .Parameters("磁盘使用率", model.磁盘使用率)
                .Parameters("服务器ID", model.服务器ID)
                .Execute() > 0;
        }

        /// <summary>
        /// 更新服务器指标
        /// </summary>
        /// <param name="model"></param>
        /// <returns></returns>
        public bool UpdateServerInfo(ServerIndexInfoModel model)
        {
            return this.UpdateBuilder.Update("服务器指标")
                       .Column("CPU使用率", model.CPU使用率)
                       .Column("内存使用率", model.内存使用率)
                       .Column("磁盘使用率", model.磁盘使用率)
                       .Column("最后修改时间", model.最后修改时间)
                       .Where("ID", model.ID)
                       .Execute() > 0;
        }

        /// <summary>
        /// 插入服务器详情
        /// </summary>
        /// <param name="model"></param>
        /// <returns></returns>
        public bool InsertServerDetail(ServerIndexDetailModel model)
        {
            return this.InsertBuilder.Insert("服务器指标明细")
                       .Column("ID", model.ID)
                       .Column("指标ID", model.指标ID)
                       .Column("服务器ID", model.服务器ID)
                       .Column("CPU使用率", model.CPU使用率)
                       .Column("内存使用", model.内存使用)
                       .Column("内存可用", model.内存可用)
                       .Column("磁盘使用", model.磁盘使用)
                       .Column("磁盘可用", model.磁盘可用)
                       .Column("创建时间", model.创建时间)
                       .Execute() > 0;
        }

        /// <summary>
        /// 插入服务器预警处理
        /// </summary>
        /// <param name="model"></param>
        /// <returns></returns>
        public bool InsertServerAlertProcess(ServerAlertProcessModel model)
        {
            return this.InsertBuilder.Insert("服务器预警处理")
                       .Column("ID", model.ID)
                       .Column("预警ID", model.预警ID)
                       .Column("处理人", model.处理人)
                       .Column("处理说明", model.处理说明)
                       .Column("处理状态", model.处理状态)
                       .Column("创建时间", model.创建时间)
                       .Execute() > 0;
        }

        /// <summary>
        /// 查询单条数据
        /// </summary>
        /// <param name="model"></param>
        /// <returns></returns>
        public ServerIndexInfoModel GetServerInfoByServerId(string serverId)
        {
            var dt = this.SelectBuilder.From("服务器指标")
                        .Columns("*")
                        .Where("服务器ID", serverId)
                        .Select();
            return dt.ToList<ServerIndexInfoModel>()?.FirstOrDefault();
        }

        /// <summary>
        /// 获取所有服务器信息
        /// </summary>
        /// <returns></returns>
        public List<ServerIndexInfoModel> GetServerIndexInfoAll()
        {
            var sql = @"SELECT a.ID, 
a.渠道ID,(SELECT 名称  FROM 渠道信息  WHERE ID = a.渠道ID) AS 渠道名称,
a.项目ID, c.名称 AS 项目名称,
a.服务器ID, b.IP地址 AS 服务器IP,
a.CPU使用率, a.内存使用率, a.磁盘使用率, a.最后修改时间,
b.CPU ,b.内存,c.负责人,
(SELECT LISTAGG (显示名, ',') WITHIN GROUP (ORDER BY 显示名) FROM 基础编码 WHERE  分类id = '1A2416AF-B98A-45E6-BAD0-F7C32651E0E8' AND 代码 IN (SELECT Column_Value FROM TABLE (f_Split_String (b.类型, ',')))) AS 服务器类型显示,b.类型 AS 服务器类型
FROM 服务器指标 a
LEFT JOIN 项目服务器 b ON b.ID = a.服务器ID
LEFT JOIN 项目信息 c ON c.ID = b.项目ID
";
            var dt = SqlBuilder.SqlText(sql)
                 .Select();
            return dt.ToList<ServerIndexInfoModel>() ?? new List<ServerIndexInfoModel>();
        }

        /// <summary>
        /// 获取所有服务器信息 通过项目id过滤
        /// </summary>
        /// <returns></returns>
        public List<ServerIndexInfoModel> GetServerIndexInfoByProjectId(string itemId)
        {
            var sql = @"SELECT a.ID, 
a.渠道ID,(SELECT 名称  FROM 渠道信息  WHERE ID = a.渠道ID) AS 渠道名称,
a.项目ID, c.名称 AS 项目名称,
a.服务器ID, b.IP地址 AS 服务器IP,
a.CPU使用率, a.内存使用率, a.磁盘使用率, a.最后修改时间,
b.CPU ,b.内存,c.负责人,
(SELECT LISTAGG (显示名, ',') WITHIN GROUP (ORDER BY 显示名) FROM 基础编码 WHERE  分类id = '1A2416AF-B98A-45E6-BAD0-F7C32651E0E8' AND 代码 IN (SELECT Column_Value FROM TABLE (f_Split_String (b.类型, ',')))) AS 服务器类型显示,b.类型 AS 服务器类型
FROM 服务器指标 a
LEFT JOIN 项目服务器 b ON b.ID = a.服务器ID
LEFT JOIN 项目信息 c ON c.ID = b.项目ID
WHERE b.项目ID in (Select Column_Value From Table(f_Split_String(:项目ID, ',')))";
            var dt = SqlBuilder.SqlText(sql)
                 .Parameters("项目ID", itemId)
                 .Select();
            return dt.ToList<ServerIndexInfoModel>() ?? new List<ServerIndexInfoModel>();
        }

        /// <summary>
        /// 获取所有服务器信息 通过渠道id过滤
        /// </summary>
        /// <returns></returns>
        public List<ServerIndexInfoModel> GetServerIndexInfoByChannelId(string channelId, string search)
        {
            var sql = @"SELECT * FROM (SELECT a.ID, 
a.渠道ID,(SELECT 名称  FROM 渠道信息  WHERE ID = a.渠道ID) AS 渠道名称,
a.项目ID, c.名称 AS 项目名称,
a.服务器ID, b.IP地址 AS 服务器IP,
a.CPU使用率, a.内存使用率, a.磁盘使用率, a.最后修改时间,
b.CPU ,b.内存,c.负责人,
(SELECT LISTAGG (显示名, ',') WITHIN GROUP (ORDER BY 显示名) FROM 基础编码 WHERE  分类id = '1A2416AF-B98A-45E6-BAD0-F7C32651E0E8' AND 代码 IN (SELECT Column_Value FROM TABLE (f_Split_String (b.类型, ',')))) AS 服务器类型显示,b.类型 AS 服务器类型
FROM 服务器指标 a
LEFT JOIN 项目服务器 b ON b.ID = a.服务器ID
LEFT JOIN 项目信息 c ON c.ID = b.项目ID
WHERE c.渠道ID in (Select Column_Value From Table(f_Split_String(:渠道ID, ',')))) 
WHERE 渠道名称 Like :查询条件 Or 项目名称 Like :查询条件 Or 负责人 Like :查询条件 Or 服务器类型显示 Like :查询条件";
            var dt = SqlBuilder.SqlText(sql)
                 .Parameters("渠道ID", channelId)
                  .Parameters("查询条件", "%" + search + "%")
                 .Select();
            return dt.ToList<ServerIndexInfoModel>() ?? new List<ServerIndexInfoModel>();
        }

        /// <summary>
        /// 获取指标详情 根据指标id
        /// </summary>
        /// <param name="indexId"></param>
        public List<ServerIndexDetailModel> GetServerIndexDetailByIndexId(string indexId, DateTime startTime, DateTime endTime)
        {
            if (endTime <= startTime) throw new Exception("结束时间需大于开始时间");
            var timeSpan = (endTime - startTime).TotalMinutes;
            var multiplier = Math.Floor(timeSpan / 1440);
            if (multiplier <= 0) multiplier = 1;
            var sql = @"SELECT  ID, 指标ID, 服务器ID, CPU使用率, 内存使用, 内存可用, 磁盘使用, 磁盘可用, 创建时间, (创建时间 - to_date('1970-01-01 08:00:00','yyyy-mm-dd hh24:mi:ss')) * 86400000 AS 时间戳创建时间, 
TO_CHAR (ROUND ( (内存使用 / (内存使用 + 内存可用)) * 100, 2), 'FM90.00') AS 内存使用率,
TO_CHAR (ROUND ( (磁盘使用 / (磁盘使用 + 磁盘可用)) * 100, 2), 'FM90.00') AS 磁盘使用率
FROM  (SELECT t.*, ROW_NUMBER() OVER (ORDER BY 创建时间) AS rn, COUNT(*) OVER () AS total_rows
  FROM  服务器指标明细 t WHERE TRUNC (创建时间) = TRUNC (SYSDATE) AND 指标ID = :指标ID )
WHERE MOD (rn, :倍率) = 0 OR rn = 1 OR rn = total_rows;";

            var dt = SqlBuilder.SqlText(sql)
                 .Parameters("倍率", multiplier)
                 .Parameters("指标ID", indexId)
                 .Select();
            return dt.ToList<ServerIndexDetailModel>() ?? new List<ServerIndexDetailModel>();
        }

        /// <summary>
        /// 获取指标详情 根据服务器id
        /// </summary>
        /// <param name="indexId"></param>
        public List<ServerIndexDetailModel> GetServerIndexDetailByServerId(string serverId, DateTime startTime, DateTime endTime)
        {
            if (endTime <= startTime) throw new Exception("结束时间需大于开始时间");
            var timeSpan = (endTime - startTime).TotalMinutes;
            var multiplier = Math.Floor(timeSpan / 1440);
            if (multiplier <= 0) multiplier = 1;
            var sql = @"SELECT  ID, 指标ID, 服务器ID, CPU使用率, 内存使用, 内存可用, 磁盘使用, 磁盘可用, 创建时间, (创建时间 - to_date('1970-01-01 08:00:00','yyyy-mm-dd hh24:mi:ss')) * 86400000 AS 时间戳创建时间, 
TO_CHAR (ROUND ( (内存使用 / (内存使用 + 内存可用)) * 100, 2), 'FM90.00') AS 内存使用率,
TO_CHAR (ROUND ( (磁盘使用 / (磁盘使用 + 磁盘可用)) * 100, 2), 'FM90.00') AS 磁盘使用率
FROM  (SELECT t.*, ROW_NUMBER() OVER (ORDER BY 创建时间) AS rn, COUNT(*) OVER () AS total_rows
  FROM  服务器指标明细 t WHERE 创建时间 >= :开始时间 AND 创建时间 <= :结束时间 AND 服务器ID = :服务器ID )
WHERE MOD (rn, :倍率) = 0 OR rn = 1 OR rn = total_rows";
            var dt = SqlBuilder.SqlText(sql)
                 .Parameters("开始时间", startTime)
                 .Parameters("结束时间", endTime)
                 .Parameters("倍率", multiplier)
                 .Parameters("服务器ID", serverId)
                 .Select();
            return dt.ToList<ServerIndexDetailModel>() ?? new List<ServerIndexDetailModel>();
        }

        /// <summary>
        /// 插入服务器预警
        /// </summary>
        /// <param name="model"></param>
        /// <returns></returns>
        public bool InsertServerAlert(ServerAlertModel model)
        {
            if (!string.IsNullOrEmpty(model.服务器ID))
            {
                var sql = @"INSERT INTO 服务器预警 (ID, 渠道ID, 项目ID, 服务器ID, 预警类型, 预警状态, 创建时间, 外部唯一键)
SELECT :ID AS ID, b.渠道ID, a.项目ID, a.ID AS 服务器ID, :预警类型 AS 预警类型, 1 AS 预警状态, :创建时间 AS 创建时间, :外部唯一键 AS 外部唯一键
FROM 项目服务器 a
LEFT JOIN 项目信息 b ON a.项目ID = b.ID
WHERE a.ID = :服务器ID";
                return SqlBuilder.SqlText(sql)
                    .Parameters("ID", model.ID)
                    .Parameters("预警类型", model.预警类型)
                    .Parameters("创建时间", model.创建时间)
                    .Parameters("外部唯一键", model.外部唯一键)
                    .Parameters("服务器ID", model.服务器ID)
                    .Execute() > 0;
            }
            else if (!string.IsNullOrEmpty(model.项目ID))
            {
                var sql = @"INSERT INTO 服务器预警 (ID, 渠道ID, 项目ID, 服务器ID, 预警类型, 预警状态, 创建时间, 外部唯一键)
SELECT :ID AS ID, a.渠道ID, a.ID AS 项目ID, '' AS 服务器ID, :预警类型 AS 预警类型, 1 AS 预警状态, :创建时间 AS 创建时间, :外部唯一键 AS 外部唯一键
FROM 项目信息 a
WHERE a.ID = :项目ID";
                return SqlBuilder.SqlText(sql)
                    .Parameters("ID", model.ID)
                    .Parameters("预警类型", model.预警类型)
                    .Parameters("创建时间", model.创建时间)
                    .Parameters("外部唯一键", model.外部唯一键)
                    .Parameters("项目ID", model.项目ID)
                    .Execute() > 0;
            }
            return false;
        }

        /// <summary>
        /// 插入服务器预警详情
        /// </summary>
        /// <param name="model"></param>
        /// <returns></returns>
        public bool InsertServerAlertDetail(ServerAlertDetailModel model)
        {
            return this.InsertBuilder.Insert("服务器预警详情")
                .Column("ID", model.ID)
                .Column("预警ID", model.预警ID)
                .Column("预警名称", model.预警名称)
                .Column("预警说明", model.预警说明)
                .Column("当前值", model.当前值)
                .Column("预警等级", (int)model.预警等级)
                .Column("预警次数", model.预警次数)
                .Column("最后修改时间", model.最后修改时间)
                .Execute() > 0;
        }

        /// <summary>
        /// 更新服务器预警详情的预警次数
        /// </summary>
        /// <param name="model"></param>
        /// <returns></returns>
        public bool UpdateServerAlertDetailNumber(ServerAlertDetailModel model)
        {
            return this.UpdateBuilder.Update("服务器预警详情")
                       .Column("当前值", model.当前值)
                       .Column("预警说明", model.预警说明)
                       .Column("预警次数", model.预警次数)
                       .Column("最后修改时间", model.最后修改时间)
                       .Where("ID", model.ID)
                       .Execute() > 0;
        }

        /// <summary>
        /// 根据服务器id、预警类型和预警状态不是已完成查询数据
        /// </summary>
        /// <param name="serverId"></param>
        /// <param name="alertType"></param>
        /// <returns></returns>
        public ServerAlertDetailModel GetServerAlertDetailByAlertIdAndAlertNameAndAlertLevel(string alertId, string alertName, int alertLevel)
        {
            var sql = @"SELECT ID, 预警ID, 预警名称, 预警说明, 当前值, 预警等级, 预警次数, 最后修改时间
FROM 服务器预警详情 a
WHERE a.预警ID = :预警ID AND a.预警名称 = :预警名称 AND 预警等级 = :预警等级";
            var dt = SqlBuilder.SqlText(sql)
                 .Parameters("预警ID", alertId)
                 .Parameters("预警名称", alertName)
                 .Parameters("预警等级", alertLevel)
                 .Select();
            return dt.ToList<ServerAlertDetailModel>()?.FirstOrDefault();
        }

        /// <summary>
        /// 根据服务器id、预警类型和预警状态不是已完成查询数据
        /// </summary>
        /// <param name="serverId"></param>
        /// <param name="alertType"></param>
        /// <returns></returns>
        public ServerAlertModel GetServerAlertByServerIdAndAlertType(string externalId = "")
        {
            var sql = @"SELECT ID, 渠道ID, 项目ID, 服务器ID, 预警类型, 预警状态, 创建时间, 处理时间
FROM 服务器预警 a
WHERE a.外部唯一键 = :外部唯一键 AND a.预警状态 <> :预警状态 ORDER BY 创建时间 DESC";

            var dt = SqlBuilder.SqlText(sql)
                    .Parameters("外部唯一键", externalId)
                    .Parameters("预警状态", (int)AlertStateEnum.已处理)
                    .Select();
            return dt.ToList<ServerAlertModel>()?.FirstOrDefault();
        }

        /// <summary>
        /// 处理服务器预警
        /// </summary>
        /// <param name="model"></param>
        /// <returns></returns>
        public bool ProcessServerAlert(string alertId, int alertState, DateTime newTime = default)
        {

            if (alertState == (int)AlertStateEnum.已处理)
            {
                return this.UpdateBuilder.Update("服务器预警")
                           .Column("处理时间", newTime)
                           .Column("预警状态", alertState)
                           .Where("ID", alertId)
                           .Execute() > 0;
            }
            else
            {
                return this.UpdateBuilder.Update("服务器预警")
                       .Column("预警状态", alertState)
                       .Where("ID", alertId)
                       .Execute() > 0;

            }
        }

        /// <summary>
        /// 查询服务器预警处理过程
        /// </summary>
        /// <param name="alertId"></param>
        /// <returns></returns>
        public List<ServerAlertProcessModel> GetServerAlertProcessByAlertId(string alertId)
        {
            var sql = @"SELECT ID, 预警ID, 处理人, 处理说明, (SELECT 显示名  FROM 基础编码 f WHERE 分类id = '4F2D87F0-D581-47C7-BC4D-153930C65D9C' AND 代码 = 处理状态) AS 处理状态显示,处理状态, 创建时间
FROM 服务器预警处理 WHERE 预警ID = :预警ID
ORDER BY 处理状态,创建时间";
            var dt = SqlBuilder.SqlText(sql)
                 .Parameters("预警ID", alertId)
                 .Select();
            return dt.ToList<ServerAlertProcessModel>() ?? new List<ServerAlertProcessModel>();
        }

        /// <summary>
        /// 获取所有服务器预警
        /// </summary>
        /// <returns></returns>
        public List<ServerAlertModel> GetServerAlertAll(string search)
        {
            var sql = @"SELECT * FROM (SELECT a.ID, 
a.渠道ID,(SELECT 名称  FROM 渠道信息  WHERE ID = a.渠道ID) AS 渠道名称,
a.项目ID, c.名称 AS 项目名称,
a.服务器ID, b.IP地址 AS 服务器IP,
a.预警类型, (Select 显示名 From 基础编码 Where 分类id = 'BFB25633-0901-447D-8755-C56D8BCCE74E' And 代码 = 预警类型) as 预警类型显示,
a.预警状态, (Select 显示名 From 基础编码 Where 分类id = '4F2D87F0-D581-47C7-BC4D-153930C65D9C' And 代码 = 预警状态) as 预警状态显示,
a.创建时间, a.处理时间,
(SELECT
        CASE WHEN FLOOR (minutes / (30 * 24 * 60)) > 0 THEN TO_CHAR (FLOOR (minutes / (30 * 24 * 60)), 'FM99999990') ||
                        '月' ELSE NULL END ||
        CASE WHEN FLOOR ( (minutes - (30 * 24 * 60) * FLOOR (minutes / (30 * 24 * 60))) / (24 * 60)) > 0 THEN
                        TO_CHAR (FLOOR ( (minutes - (30 * 24 * 60) * FLOOR (minutes / (30 * 24 * 60))) / (24 * 60)),
                        'FM99999990') || '天' ELSE NULL END ||
        CASE WHEN FLOOR ( (minutes - (30 * 24 * 60) * FLOOR (minutes / (30 * 24 * 60)) - (24 * 60) * FLOOR ( (minutes -
                        (30 * 24 * 60) * FLOOR (minutes / (30 * 24 * 60))) / (24 * 60))) / 60) > 0 THEN
                        TO_CHAR (FLOOR ( (minutes - (30 * 24 * 60) * FLOOR (minutes / (30 * 24 * 60)) - (24 * 60) *
                        FLOOR ( (minutes - (30 * 24 * 60) * FLOOR (minutes / (30 * 24 * 60))) / (24 * 60))) / 60),
                        'FM99999990') || '小时' ELSE NULL END ||
        CASE WHEN MOD (minutes, 60) > 0 THEN TO_CHAR (MOD (minutes, 60), 'FM99999990') || '分钟' ELSE NULL END AS
        formatted_duration
FROM (SELECT (NVL(a.处理时间,SYSDATE) - a.创建时间) * 60 * 24  AS minutes FROM Dual)) AS 持续时间,
c.负责人,
(SELECT LISTAGG (显示名, ',') WITHIN GROUP (ORDER BY 显示名) FROM 基础编码 WHERE  分类id = '1A2416AF-B98A-45E6-BAD0-F7C32651E0E8' AND 代码 IN (SELECT Column_Value FROM TABLE (f_Split_String (b.类型, ',')))) AS 服务器类型显示,b.类型 AS 服务器类型
FROM 服务器预警 a
LEFT JOIN 项目服务器 b ON b.ID = a.服务器ID
LEFT JOIN 项目信息 c ON c.ID = b.项目ID
ORDER BY 创建时间 desc) 
WHERE 渠道名称 Like :查询条件 Or 项目名称 Like :查询条件 Or 预警类型显示 Like :查询条件 Or 负责人 Like :查询条件 Or 服务器类型显示 Like :查询条件
";
            var dt = SqlBuilder.SqlText(sql)
                 .Parameters("查询条件", "%" + search + "%")
                 .Select();
            return dt.ToList<ServerAlertModel>() ?? new List<ServerAlertModel>();
        }

        /// <summary>
        /// 根据条件获取服务器预警
        /// </summary>
        /// <returns></returns>
        public List<ServerAlertModel> GetServerAlertBySearch(ServerAlertSearch search, AlertConfig config)
        {
            var sql = @"SELECT * FROM (SELECT a.ID, 
a.渠道ID,(SELECT 名称  FROM 渠道信息  WHERE ID = a.渠道ID) AS 渠道名称,
a.项目ID, c.名称 AS 项目名称,
a.服务器ID, b.IP地址 AS 服务器IP,
a.预警类型, (Select 显示名 From 基础编码 Where 分类id = 'BFB25633-0901-447D-8755-C56D8BCCE74E' And 代码 = 预警类型) as 预警类型显示,
a.预警状态, (Select 显示名 From 基础编码 Where 分类id = '4F2D87F0-D581-47C7-BC4D-153930C65D9C' And 代码 = 预警状态) as 预警状态显示,
a.创建时间, a.处理时间, 
'' AS 未处理状态,
c.负责人,
(SELECT LISTAGG (显示名, ',') WITHIN GROUP (ORDER BY 显示名) FROM 基础编码 WHERE  分类id = '1A2416AF-B98A-45E6-BAD0-F7C32651E0E8' AND 代码 IN (SELECT Column_Value FROM TABLE (f_Split_String (b.类型, ',')))) AS 服务器类型显示,b.类型 AS 服务器类型
FROM 服务器预警 a
LEFT JOIN 项目服务器 b ON b.ID = a.服务器ID
LEFT JOIN 项目信息 c ON c.ID = a.项目ID
ORDER BY 预警状态 ASC,创建时间 DESC) 
WHERE (渠道名称 Like :查询条件 Or 项目名称 Like :查询条件 Or 预警类型显示 Like :查询条件 Or 负责人 Like :查询条件 Or 服务器类型显示 Like :查询条件)
";
            var sqlbuilder = SqlBuilder;
            sqlbuilder.Parameters("查询条件", "%" + search.CONTENT + "%");

            if (!String.IsNullOrEmpty(search.QDID))
            {
                sql += " And 渠道ID IN (Select Column_Value From Table(f_Split_String(:渠道ID, ','))) ";
                sqlbuilder.Parameters("渠道ID", search.QDID);
            }
            if (!String.IsNullOrEmpty(search.XMID))
            {
                sql += "And 项目ID=:项目ID ";
                sqlbuilder.Parameters("项目ID", search.XMID);
            }
            if (!String.IsNullOrEmpty(search.YJLXID))
            {
                sql += "And 预警类型=:预警类型 ";
                sqlbuilder.Parameters("预警类型", search.YJLXID);
            }

            if (!String.IsNullOrEmpty(search.ZT))
            {
                sql += "And 预警状态=:预警状态 ";
                sqlbuilder.Parameters("预警状态", search.ZT);
            }

            var dt = sqlbuilder.SqlText(sql)
                 .Select();
            return dt.ToList<ServerAlertModel>() ?? new List<ServerAlertModel>();
        }

        /// <summary>
        /// 根据预警id获取服务器预警
        /// </summary>
        /// <returns></returns>
        public ServerAlertModel GetServerAlertByAlertlId(string alertlId)
        {
            var sql = @"SELECT a.ID,a.渠道ID,(SELECT 名称  FROM 渠道信息  WHERE ID = a.渠道ID) AS 渠道名称,
a.项目ID,(SELECT 名称  FROM 项目信息  WHERE ID = a.项目ID) AS 项目名称,
a.服务器ID, b.IP地址 AS 服务器IP,
a.预警类型, (Select 显示名 From 基础编码 Where 分类id = 'BFB25633-0901-447D-8755-C56D8BCCE74E' And 代码 = 预警类型) as 预警类型显示,
a.预警状态, (Select 显示名 From 基础编码 Where 分类id = '4F2D87F0-D581-47C7-BC4D-153930C65D9C' And 代码 = 预警状态) as 预警状态显示,
a.创建时间, a.处理时间,
b.类型 AS 服务器类型, (SELECT LISTAGG (显示名, ',') WITHIN GROUP (ORDER BY 显示名) FROM 基础编码 WHERE  分类id = '1A2416AF-B98A-45E6-BAD0-F7C32651E0E8' AND 代码 IN (SELECT Column_Value FROM TABLE (f_Split_String (b.类型, ',')))) AS 服务器类型显示
FROM 服务器预警 a
LEFT JOIN 项目服务器 b ON b.ID = a.服务器ID
WHERE a.ID = :预警ID";
            var sqlbuilder = SqlBuilder;
            sqlbuilder.Parameters("预警ID", alertlId);
            var dt = sqlbuilder.SqlText(sql)
                 .Select();
            return dt.ToList<ServerAlertModel>()?.FirstOrDefault();
        }

        /// <summary>
        /// 根据预警id获取服务器预警
        /// </summary>
        /// <returns></returns>
        public List<ServerAlertModel> GetServerAlertByAlertlId(List<string> alertlId)
        {
            var sql = @"SELECT a.ID,a.渠道ID,(SELECT 名称  FROM 渠道信息  WHERE ID = a.渠道ID) AS 渠道名称,
a.项目ID,(SELECT 名称  FROM 项目信息  WHERE ID = a.项目ID) AS 项目名称,
a.服务器ID, b.IP地址 AS 服务器IP,
a.预警类型, (Select 显示名 From 基础编码 Where 分类id = 'BFB25633-0901-447D-8755-C56D8BCCE74E' And 代码 = 预警类型) as 预警类型显示,
a.预警状态, (Select 显示名 From 基础编码 Where 分类id = '4F2D87F0-D581-47C7-BC4D-153930C65D9C' And 代码 = 预警状态) as 预警状态显示,
a.创建时间, a.处理时间,
b.类型 AS 服务器类型, (SELECT LISTAGG (显示名, ',') WITHIN GROUP (ORDER BY 显示名) FROM 基础编码 WHERE  分类id = '1A2416AF-B98A-45E6-BAD0-F7C32651E0E8' AND 代码 IN (SELECT Column_Value FROM TABLE (f_Split_String (b.类型, ',')))) AS 服务器类型显示
FROM 服务器预警 a
LEFT JOIN 项目服务器 b ON b.ID = a.服务器ID
WHERE a.ID IN (Select Column_Value From Table(f_Split_String(:预警ID, ',')))";
            var sqlbuilder = SqlBuilder;
            sqlbuilder.Parameters("预警ID", string.Join(",", alertlId));
            var dt = sqlbuilder.SqlText(sql)
                 .Select();
            return dt.ToList<ServerAlertModel>() ?? new List<ServerAlertModel>();
        }

        /// <summary>
        /// 获取项目信息
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public DataTable GetProInfo(string id)
        {
            string sql = "select * from 项目信息 WHERE ID = :id";
            return SqlBuilder.SqlText(sql).Parameters("id", id).Select();
        }

        /// <summary>
        /// 获取渠道信息
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public DataTable GetChannelInfo(string id)
        {
            string sql = "select * from 渠道信息 WHERE ID = :id";
            return SqlBuilder.SqlText(sql).Parameters("id", id).Select();
        }

        /// <summary>
        /// 根据手机号获取openid
        /// </summary>
        /// <param name="phoneList"></param>
        /// <returns></returns>
        public List<string> GetUserOpenIdByPhone(List<string> phoneList)
        {
            var sql = @"SELECT 微信ID 
FROM 系统账户 a
WHERE a.账户 IN (SELECT Column_Value FROM TABLE (f_Split_String (:手机号,';')))";
            var dt = SqlBuilder.SqlText(sql)
                 .Parameters("手机号", string.Join(";", phoneList))
                 .Select();
            var ret = new List<string>();
            if (dt != null && dt.Rows.Count > 0)
            {
                for (int i = 0; i < dt.Rows.Count; i++)
                {
                    ret.Add(dt.Rows[i][0].ToString());
                }
            }
            return ret;
        }

        /// <summary>
        /// 获取所有服务器预警详情信息 通过预警id过滤
        /// </summary>
        /// <returns></returns>
        public List<ServerAlertDetailModel> GetServerAlertDetailByAlertlId(string alertlId)
        {
            var sql = @"SELECT ID, 预警ID, 预警名称, 预警说明, 当前值, 预警等级, 预警次数, 最后修改时间
FROM 服务器预警详情
WHERE 预警ID IN (Select Column_Value From Table(f_Split_String(:预警ID, ',')))";
            var dt = SqlBuilder.SqlText(sql)
                 .Parameters("预警ID", alertlId)
                 .Select();
            return dt.ToList<ServerAlertDetailModel>() ?? new List<ServerAlertDetailModel>();
        }

        /// <summary>
        /// 获取全部项目监控数据
        /// </summary>
        /// <returns></returns>
        public List<ProjectMonitorModel> GetProjectMonitorAll()
        {
            var sql = @"SELECT  c.id AS 渠道ID, c.名称 AS 渠道名称, p.id AS 项目ID, p.名称 AS 项目名称, COUNT(DISTINCT ps.id) AS 服务器数量, COALESCE (SUM (pd.预警次数), 0) AS
        预警次数,xx.首次监控日期,xx.最后监控日期,NVL(xx.监控状态,3) AS 监控状态 , (Select 显示名 From 基础编码 Where 分类id = '8B9114B4-0D2D-447B-A016-CC5F5A388AEA' And 代码 = NVL(xx.监控状态,3)) as 监控状态显示
FROM    渠道信息 c
LEFT JOIN    项目信息 p       ON c.id = p.渠道id
LEFT JOIN    项目监控信息 xx       ON p.id= xx.项目id
LEFT JOIN 项目服务器 ps   ON p.id = ps.项目id
LEFT JOIN 服务器预警 sp   ON ps.id = sp.服务器id
LEFT JOIN 服务器预警详情 pd ON sp.id = pd.预警id
GROUP BY c.id, c.名称, p.id, p.名称,xx.首次监控日期,xx.最后监控日期,xx.监控状态
ORDER BY xx.监控状态,xx.首次监控日期";
            var dt = SqlBuilder.SqlText(sql)
                .Select();
            return dt.ToList<ProjectMonitorModel>() ?? new List<ProjectMonitorModel>();
        }

        /// <summary>
        /// 获取项目的监控状态，根据渠道id过滤
        /// </summary>
        /// <param name="channelId"></param>
        /// <returns></returns>
        public List<ProjectMonitorModel> GetProjectMonitorByChannelId(string channelId)
        {
            var sql = @"SELECT  a.ID AS 项目ID, a.名称 AS 项目名称, NVL (b.监控状态, 3) 监控状态, b.客户端ID as 客户端ID,
(SELECT 显示名 FROM 基础编码 WHERE 分类id = '8B9114B4-0D2D-447B-A016-CC5F5A388AEA' AND 代码 = NVL (b.监控状态, 3)) AS 监控状态显示
FROM 项目信息 a
LEFT JOIN 项目监控信息 b ON a.id = b.项目id
WHERE   a.启用监控 = 1 AND a.渠道ID IN (Select Column_Value From Table(f_Split_String(:渠道ID, ',')))";
            var dt = SqlBuilder.SqlText(sql)
                 .Parameters("渠道ID", channelId)
                .Select();
            return dt.ToList<ProjectMonitorModel>() ?? new List<ProjectMonitorModel>();
        }

        /// <summary>
        /// 根据查询获取全部项目监控
        /// </summary>
        /// <returns></returns>
        public List<ProjectMonitorModel> GetProjectMonitorBySearch(ServerAlertSearch search)
        {
            var sql = @"SELECT * FROM (SELECT  c.id AS 渠道ID, c.名称 AS 渠道名称, p.id AS 项目ID, p.名称 AS 项目名称, COUNT(DISTINCT ps.id) AS 服务器数量, COALESCE (SUM (pd.预警次数), 0) AS
        预警次数,xx.首次监控日期,xx.最后监控日期,NVL(xx.监控状态,3) AS 监控状态 , (Select 显示名 From 基础编码 Where 分类id = '8B9114B4-0D2D-447B-A016-CC5F5A388AEA' And 代码 = NVL(xx.监控状态,3)) as 监控状态显示
FROM    渠道信息 c
LEFT JOIN    项目信息 p       ON c.id = p.渠道id
LEFT JOIN    项目监控信息 xx       ON p.id= xx.项目id
LEFT JOIN 项目服务器 ps   ON p.id = ps.项目id
LEFT JOIN 服务器预警 sp   ON ps.id = sp.服务器id
LEFT JOIN 服务器预警详情 pd ON sp.id = pd.预警id
GROUP BY c.id, c.名称, p.id, p.名称,xx.首次监控日期,xx.最后监控日期,xx.监控状态
ORDER BY xx.监控状态,xx.首次监控日期) 
WHERE (渠道名称 Like :查询条件 Or 项目名称 Like :查询条件)";
            var sqlbuilder = SqlBuilder;
            sqlbuilder.Parameters("查询条件", "%" + search.CONTENT + "%");

            if (!String.IsNullOrEmpty(search.QDID))
            {
                sql += " And 渠道ID IN (Select Column_Value From Table(f_Split_String(:渠道ID, ','))) ";
                sqlbuilder.Parameters("渠道ID", search.QDID);
            }
            if (!String.IsNullOrEmpty(search.XMID))
            {
                sql += "And 项目ID=:项目ID ";
                sqlbuilder.Parameters("项目ID", search.XMID);
            }

            if (!String.IsNullOrEmpty(search.ZT))
            {
                sql += "And 监控状态=:监控状态 ";
                sqlbuilder.Parameters("监控状态", search.ZT);
            }

            var dt = sqlbuilder.SqlText(sql)
                 .Select();
            return dt.ToList<ProjectMonitorModel>() ?? new List<ProjectMonitorModel>();
        }

        /// <summary>
        /// 根据项目id获取项目监控数据
        /// </summary>
        /// <param name="projectId"></param>
        /// <returns></returns>
        public ProjectMonitorModel GetProjectMonitorByProjectId(string projectId)
        {
            var dt = this.SelectBuilder.From("项目监控信息")
            .Columns("*")
            .Where("项目ID", projectId)
            .Select();
            return dt.ToList<ProjectMonitorModel>()?.FirstOrDefault();
        }

        /// <summary>
        /// 获取状态为正常的项目监控数据
        /// </summary>
        /// <param name="projectId"></param>
        /// <returns></returns>
        public List<ProjectMonitorModel> GetProjectMonitor()
        {
            var dt = this.SelectBuilder.From("项目监控信息")
            .Columns("*")
            .Where("监控状态", 1)
            .Select();
            return dt.ToList<ProjectMonitorModel>() ?? new List<ProjectMonitorModel>();
        }

        /// <summary>
        /// 插入项目监控数据
        /// </summary>
        /// <param name="model"></param>
        /// <returns></returns>
        public bool InsertProjectMonitor(ProjectMonitorModel model)
        {
            var sql = @"INSERT INTO 项目监控信息
(ID, 项目ID, 渠道ID, 首次监控日期, 最后监控日期, 监控状态, 客户端ID)
SELECT :ID AS ID, :项目ID AS 项目ID, xm.渠道ID, :首次监控日期 AS 首次监控日期, :最后监控日期 AS 最后监控日期, :监控状态 AS 监控状态, :客户端ID AS 客户端ID
FROM  项目信息 xm
WHERE xm.ID = :项目ID";
            return SqlBuilder.SqlText(sql)
                .Parameters("ID", model.ID)
                .Parameters("项目ID", model.项目ID)
                .Parameters("首次监控日期", model.首次监控日期)
                .Parameters("最后监控日期", model.最后监控日期)
                .Parameters("监控状态", model.监控状态)
                .Parameters("客户端ID", model.客户端ID)
                .Execute() > 0;
        }

        /// <summary>
        /// 修改项目监控数据
        /// </summary>
        /// <param name="model"></param>
        /// <returns></returns>
        public bool UpdateProjectMonitor(ProjectMonitorModel model)
        {
            return this.UpdateBuilder.Update("项目监控信息")
               .Column("最后监控日期", model.最后监控日期)
               .Column("监控状态", model.监控状态)
               .Column("客户端ID", model.客户端ID)
               .Where("ID", model.ID)
               .Execute() > 0;
        }

        /// <summary>
        /// 插入数据库数据
        /// </summary>
        /// <param name="model"></param>
        /// <returns></returns>
        public bool InsertDbInfo(DbInfoModel model)
        {
            var sql = @"INSERT INTO 数据库信息 (ID, 名称, 渠道ID, 项目ID, 服务器ID, 数据库类型, 数据库连接串)
                                SELECT :ID AS ID, :名称 AS 名称, b.渠道ID AS 渠道ID, a.项目ID AS 项目ID, a.ID AS 服务器ID, :数据库类型 AS 数据库类型, :数据库连接串 AS 数据库连接串
                                FROM 项目服务器 a 
                                LEFT JOIN 项目信息 b ON b.ID = a.项目ID
                                WHERE a.ID = :服务器ID";
            return SqlBuilder.SqlText(sql)
                .Parameters("ID", model.ID)
                .Parameters("名称", model.名称)
                .Parameters("数据库类型", model.数据库类型)
                .Parameters("数据库连接串", model.数据库连接串)
                .Parameters("服务器ID", model.服务器ID)
                .Execute() > 0;
        }

        /// <summary>
        /// 修改数据库数据
        /// </summary>
        /// <param name="model"></param>
        /// <returns></returns>
        public bool UpdateDbInfo(DbInfoModel model)
        {
            return this.UpdateBuilder.Update("数据库信息")
               .Column("名称", model.名称)
               .Column("数据库类型", model.数据库类型)
               .Column("数据库连接串", model.数据库连接串)
               .Where("ID", model.ID)
               .Execute() > 0;
        }

        /// <summary>
        /// 获取数据库数据，根据服务器ID过滤
        /// </summary>
        /// <param name="channelId"></param>
        /// <returns></returns>
        public List<DbInfoModel> GetDbInfoByServerId(string serverId)
        {
            var sql = @"SELECT a.ID,a.名称,
a.渠道ID,(SELECT b.名称 FROM 渠道信息 b WHERE b.ID = a.渠道ID) AS 渠道名称,
a.项目ID,(SELECT b.名称 FROM 项目信息 b WHERE b.ID = a.项目ID) AS 项目名称,
a.服务器ID,(SELECT b.IP地址 FROM 项目服务器 b WHERE b.ID = a.服务器ID) AS 服务器IP,
a.数据库类型,(SELECT 显示名  FROM 基础编码 f WHERE 分类id = '19AEF47C-A8AC-455C-8BA8-191BEC4DDB93' AND 代码 = 数据库类型) AS 数据库类型显示,
a.数据库连接串
FROM 数据库信息 a
WHERE a.服务器ID IN (Select Column_Value From Table(f_Split_String(:服务器ID, ',')))";
            var dt = SqlBuilder.SqlText(sql)
                 .Parameters("服务器ID", serverId)
                .Select();
            return dt.ToList<DbInfoModel>() ?? new List<DbInfoModel>();
        }

        /// <summary>
        /// 获取数据库数据，根据ID过滤
        /// </summary>
        /// <param name="channelId"></param>
        /// <returns></returns>
        public DbInfoModel GetDbInfoById(string id)
        {
            var sql = @"SELECT a.ID,a.名称,
a.渠道ID,(SELECT b.名称 FROM 渠道信息 b WHERE b.ID = a.渠道ID) AS 渠道名称,
a.项目ID,(SELECT b.名称 FROM 项目信息 b WHERE b.ID = a.项目ID) AS 项目名称,
a.服务器ID,(SELECT b.IP地址 FROM 项目服务器 b WHERE b.ID = a.服务器ID) AS 服务器IP,
a.数据库类型,(SELECT 显示名  FROM 基础编码 f WHERE 分类id = '19AEF47C-A8AC-455C-8BA8-191BEC4DDB93' AND 代码 = 数据库类型) AS 数据库类型显示,
a.数据库连接串
FROM 数据库信息 a
WHERE a.ID = :ID";
            var dt = SqlBuilder.SqlText(sql)
                 .Parameters("ID", id)
                .Select();
            return dt.ToList<DbInfoModel>()?.FirstOrDefault();
        }

        /// <summary>
        /// 删除数据库数据，根据ID
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public bool DeleteDbInfoById(string id)
        {
            return this.DeleteBulider.Delete("数据库信息")
                    .Where("ID", id)
                    .Execute() > 0;
        }

        /// <summary>
        /// 通过服务器Id新增数据
        /// </summary>
        /// <param name="model"></param>
        /// <returns></returns>
        public bool InsertPushIndexDataByServerId(PushIndexDataModel model)
        {
            //数据长度超过3000，需要先插入其他数据，在修改上报原始数据，不然会报错
            if (model.上报原始数据.Length > 3000)
            {
                var sql = @"INSERT INTO 上报指标记录
(ID, 渠道ID, 项目ID, 服务器ID, 预警ID, 类型, 状态, 创建时间, 外部唯一键)
 (SELECT :ID AS ID, b.渠道ID AS 渠道ID, a.项目ID AS 项目ID, a.ID AS 服务器ID, :预警ID AS 预警ID, :类型 AS 类型, :状态 AS 状态, :创建时间 AS 创建时间, :外部唯一键 AS 外部唯一键
FROM 项目服务器 a 
LEFT JOIN 项目信息 b ON b.ID = a.项目ID
WHERE a.ID = :服务器ID)";
                var ret = SqlBuilder.SqlText(sql)
                   .Parameters("ID", model.ID)
                   .Parameters("预警ID", model.预警ID)
                   .Parameters("类型", model.类型)
                   .Parameters("状态", model.状态)
                   .Parameters("创建时间", model.创建时间)
                   .Parameters("外部唯一键", model.外部唯一键)
                   .Parameters("服务器ID", model.服务器ID)
                   .Execute();
                if (ret > 0)
                {
                    UpdateBuilder.Update("上报指标记录")
                        .Column("上报原始数据", model.上报原始数据)
                        .Where("ID", model.ID)
                        .Execute();
                }
                return ret > 0;
            }
            else
            {
                var sql = @"INSERT INTO 上报指标记录
(ID, 渠道ID, 项目ID, 服务器ID, 预警ID, 类型, 状态, 上报原始数据, 创建时间, 外部唯一键)
 (SELECT :ID AS ID, b.渠道ID AS 渠道ID, a.项目ID AS 项目ID, a.ID AS 服务器ID, :预警ID AS 预警ID, :类型 AS 类型, :状态 AS 状态, :上报原始数据 AS 上报原始数据, :创建时间 AS 创建时间, :外部唯一键 AS 外部唯一键
FROM 项目服务器 a 
LEFT JOIN 项目信息 b ON b.ID = a.项目ID
WHERE a.ID = :服务器ID)";
                return SqlBuilder.SqlText(sql)
                    .Parameters("ID", model.ID)
                    .Parameters("预警ID", model.预警ID)
                    .Parameters("类型", model.类型)
                    .Parameters("状态", model.状态)
                    .Parameters("上报原始数据", model.上报原始数据)
                    .Parameters("创建时间", model.创建时间)
                    .Parameters("外部唯一键", model.外部唯一键)
                    .Parameters("服务器ID", model.服务器ID)
                    .Execute() > 0;
            }
        }

        /// <summary>
        /// 查询上传指标数据
        /// </summary>
        /// <returns></returns>
        public int GetPushIndexDataTotal(IndexSearch search)
        {
            var sql = @"SELECT count(1) FROM (SELECT * FROM (SELECT a.ID,
a.渠道ID,(SELECT b.名称 FROM 渠道信息 b WHERE b.ID = a.渠道ID) AS 渠道名称,
a.项目ID,(SELECT b.名称 FROM 项目信息 b WHERE b.ID = a.项目ID) AS 项目名称,
a.服务器ID,(SELECT b.IP地址 FROM 项目服务器 b WHERE b.ID = a.服务器ID) AS 服务器IP,
a.预警ID,
a.类型,(SELECT b.显示名  FROM 基础编码 b WHERE b.分类id = 'ECBC9F93-2B9D-44A1-8CD6-D43E03C5AD03' AND b.代码 = a.类型) AS 类型显示,
a.状态,(SELECT b.显示名  FROM 基础编码 b WHERE b.分类id = 'B72312D8-BC9E-4445-A3D5-4E85AEAC722F' AND b.代码 = a.状态) AS 状态显示,
a.上报原始数据,a.创建时间,ROW_NUMBER() OVER (ORDER BY a.创建时间) AS row_num 
FROM 上报指标记录 a WHERE";
            var sqlbuilder = SqlBuilder;
            sqlbuilder.Parameters("查询条件", "%" + search.CONTENT + "%");

            if (!String.IsNullOrEmpty(search.QDID))
            {
                sql += " 渠道ID IN (Select Column_Value From Table(f_Split_String(:渠道ID, ',')))";
                sqlbuilder.Parameters("渠道ID", search.QDID);
            }
            if (!String.IsNullOrEmpty(search.XMID))
            {
                sql += " And 项目ID = :项目ID";
                sqlbuilder.Parameters("项目ID", search.XMID);
            }

            if (!String.IsNullOrEmpty(search.ZBLX))
            {
                sql += " And 类型 = :类型";
                sqlbuilder.Parameters("类型", search.ZBLX);
            }

            if (!String.IsNullOrEmpty(search.ZT))
            {
                sql += " And 状态=:状态";
                sqlbuilder.Parameters("状态", search.ZT);
            }

            if (search.KSSJ != DateTime.MinValue && search.JSSJ != DateTime.MinValue)
            {
                sql += " And 创建时间 >= :开始时间";
                sqlbuilder.Parameters("开始时间", search.KSSJ);
                sql += " And 创建时间 <= :结束时间";
                sqlbuilder.Parameters("结束时间", search.JSSJ);
            }
            sql +=@"))
WHERE 渠道名称 Like :查询条件 Or 项目名称 Like :查询条件 Or 服务器IP Like :查询条件 Or 类型显示 Like :查询条件 Or 状态显示 Like :查询条件";
            
            var dt = sqlbuilder.SqlText(sql)
                 .Select();
            if (dt != null && dt.Rows.Count > 0)
            {
                return int.Parse(dt.Rows[0][0].ToString());
            }
            return 0;
        }

        /// <summary>
        /// 查询上传指标数据
        /// </summary>
        /// <returns></returns>
        public List<PushIndexDataModel> GetPushIndexData(IndexSearch search)
        {
            var sql = @"SELECT * FROM (SELECT ROW_NUMBER() OVER (ORDER BY 创建时间 DESC) AS row_num,b.* FROM (SELECT * FROM (SELECT a.ID,
                            a.渠道ID,(SELECT b.名称 FROM 渠道信息 b WHERE b.ID = a.渠道ID) AS 渠道名称,
                            a.项目ID,(SELECT b.名称 FROM 项目信息 b WHERE b.ID = a.项目ID) AS 项目名称,
                            a.服务器ID,(SELECT b.IP地址 FROM 项目服务器 b WHERE b.ID = a.服务器ID) AS 服务器IP,
                            a.预警ID,
                            a.类型,(SELECT b.显示名  FROM 基础编码 b WHERE b.分类id = 'ECBC9F93-2B9D-44A1-8CD6-D43E03C5AD03' AND b.代码 = a.类型) AS 类型显示,
                            a.状态,(SELECT b.显示名  FROM 基础编码 b WHERE b.分类id = 'B72312D8-BC9E-4445-A3D5-4E85AEAC722F' AND b.代码 = a.状态) AS 状态显示,
                            a.上报原始数据,a.创建时间 
                            FROM 上报指标记录 a)
                            WHERE (渠道名称 Like :查询条件 Or 项目名称 Like :查询条件 Or 服务器IP Like :查询条件 Or 类型显示 Like :查询条件 Or 状态显示 Like :查询条件)";
            var sqlbuilder = SqlBuilder;
            sqlbuilder.Parameters("查询条件", "%" + search.CONTENT + "%");

            if (!String.IsNullOrEmpty(search.QDID))
            {
                sql += " And 渠道ID IN (Select Column_Value From Table(f_Split_String(:渠道ID, ',')))";
                sqlbuilder.Parameters("渠道ID", search.QDID);
            }
            if (!String.IsNullOrEmpty(search.XMID))
            {
                sql += " And 项目ID = :项目ID";
                sqlbuilder.Parameters("项目ID", search.XMID);
            }

            if (!String.IsNullOrEmpty(search.ZBLX))
            {
                sql += " And 类型 = :类型";
                sqlbuilder.Parameters("类型", search.ZBLX);
            }

            if (!String.IsNullOrEmpty(search.ZT))
            {
                sql += " And 状态 = :状态";
                sqlbuilder.Parameters("状态", search.ZT);
            }

            if (search.KSSJ != DateTime.MinValue && search.JSSJ != DateTime.MinValue)
            {
                sql += " And 创建时间 >= :开始时间";
                sqlbuilder.Parameters("开始时间", search.KSSJ);
                sql += " And 创建时间 <= :结束时间";
                sqlbuilder.Parameters("结束时间", search.JSSJ);
            }
            //分页
            sql += ") b) WHERE row_num BETWEEN :开始行 AND :结束行";
            sqlbuilder.Parameters("开始行", Startnumber(search.page, search.rows, 5));
            sqlbuilder.Parameters("结束行", Endnumbers(search.page, search.rows, 5));
            var dt = sqlbuilder.SqlText(sql)
                 .Select();
            return dt.ToList<PushIndexDataModel>() ?? new List<PushIndexDataModel>();
        }

        /// <summary>
        /// 计算开始行
        /// </summary>
        /// <param name="page"></param>
        /// <param name="row"></param>
        /// <param name="defaultrow"></param>
        /// <returns></returns>
        private int Endnumbers(int page, int row, int defaultrow)
        {
            if (page == 0 && row == 0)
            {
                row = defaultrow;
                page = 1;

            }
            return (page) * row;
        }

        private int Startnumber(int page, int row, int defaultrow)
        {
            if (page == 0 && row == 0)
            {
                row = defaultrow;
                page = 1;

            }
            return (page - 1) * row + 1;
        }


        /// <summary>
        /// 获取当前项目的指标数据，多少分钟内的
        /// </summary>
        /// <param name="projectId"></param>
        /// <param name="minute"></param>
        /// <returns></returns>
        public List<PushIndexDataModel> GetPushIndexDataByProjectId(string projectId, int minute)
        {
            var sql = $@"SELECT a.*
FROM 上报指标记录 a
INNER JOIN (
    SELECT 服务器ID, 类型, 状态, 外部唯一键, MAX(创建时间) AS 最后创建时间
    FROM 上报指标记录
    WHERE 项目ID = :项目ID
    AND 创建时间 >= SYSDATE - INTERVAL '{minute}' MINUTE
    GROUP BY 服务器ID, 类型, 状态, 外部唯一键
) b ON a.服务器ID = b.服务器ID
    AND a.类型 = b.类型
    AND a.状态 = b.状态
    AND (a.外部唯一键 = b.外部唯一键 OR (a.外部唯一键 IS NULL AND b.外部唯一键 IS NULL))
    AND a.创建时间 = b.最后创建时间";
            var dt = SqlBuilder.SqlText(sql)
                .Parameters("项目ID", projectId)
                .Select();
            return dt.ToList<PushIndexDataModel>() ?? new List<PushIndexDataModel>();
        }

        /// <summary>
        /// 获取当前项目的指标数据
        /// </summary>
        /// <param name="projectId"></param>
        /// <param name="minute"></param>
        /// <returns></returns>
        public List<PushIndexDataModel> GetPushIndexDataByServerInfo(string projectId, DateTime time)
        {
            var sql = $@"SELECT a.*
FROM 上报指标记录 a
INNER JOIN (
    SELECT 服务器ID, 类型, MAX(创建时间) AS 最后创建时间
    FROM 上报指标记录
    WHERE 项目ID = :项目ID
    AND 类型 = 1
    AND 创建时间 <= :创建时间
    GROUP BY 服务器ID, 类型
) b ON a.服务器ID = b.服务器ID
    AND a.类型 = b.类型
    AND a.创建时间 = b.最后创建时间";
            var dt = SqlBuilder.SqlText(sql)
                .Parameters("项目ID", projectId)
                .Parameters("创建时间", time)
                .Select();
            return dt.ToList<PushIndexDataModel>() ?? new List<PushIndexDataModel>();
        }

        /// <summary>
        /// 删除90天之前的数据
        /// </summary>
        /// <returns></returns>
        public int DeletePushIndexData()
        {
            var sql = $@"DELETE FROM 上报指标记录
WHERE 创建时间 < SYSDATE - 30";
            var ret = SqlBuilder.SqlText(sql)
                .Execute();
            return ret;
        }

        /// <summary>
        /// 删除90天之前的数据
        /// </summary>
        /// <returns></returns>
        public int DeleteServerIndexData()
        {
            var sql = $@"DELETE FROM 服务器指标明细
WHERE 创建时间 < SYSDATE - 30";
            var ret = SqlBuilder.SqlText(sql)
                .Execute();
            return ret;
        }
    }
}