ZLPMS/PMS.DBService/ExternalManage/ExternalManageService.cs

using PMS.BusinessModels.CloudMonitorManage;
using PMS.BusinessModels.ExternalManage;
using PMS.EntityModels.ExternalManage;
using PMS.Plugins.Common;
using QWPlatform.IService;
using QWPlatform.Models;
using QWPlatform.SystemLibrary;
using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Linq;
using System.Runtime.InteropServices;
using System.Runtime.Remoting.Messaging;
using System.Text;
using System.Text.RegularExpressions;
using System.Threading.Tasks;
using System.Xml.Linq;

namespace PMS.DBService.ExternalManage
{
    public class ExternalManageService : DataServiceBase
    {
        public static string GenerateRandomString(int length)
        {
            Guid guid = Guid.NewGuid();
            string uniqueString = guid.ToString("N").Substring(0, 8); // 获取 Guid 的前 8 位作为字符串

            return uniqueString;
        }
        /// <summary>
        /// 关键字查询外部服务分类
        /// </summary>
        /// <param name="keyword"></param>
        /// <returns></returns>
        public DataTable GetMenu (string keyword, string productId, int type)
        {
            if (type == 1)
            {
                string sql = "select a.*,(select b.分类名称 from 外部服务分类 b where a.上级ID = b.ID) as 上级名称 from 外部服务分类 a where a.产品ID = :产品ID";
                var sqlbuilder = this.SqlBuilder;
                if (!(keyword.IsNullOrEmpty()))
                {
                    sql += "and (upper(分类编码) like '%' || upper(:keyword) || '%' or upper(分类名称) like '%' || upper(:keyword) || '%' or upper(分类简码) like '%' || upper(:keyword) || '%')";
                    sqlbuilder.Parameters("keyword", keyword);
                }
                sql += " order by a.分类编码";
                return sqlbuilder.SqlText(sql).Parameters("产品ID", productId).Select();
            }else
            {
                string sql = @"WITH w AS ( SELECT a.* 
                            FROM 外部服务分类 a 
                            WHERE a.产品ID = :产品ID  UNION ALL 
                            SELECT b.ID, b.分类ID AS 上级ID, b.服务编码 AS 分类编码, b.服务名称 AS 分类名称, b.服务简码 AS 分类简码, b.创建时间 ,b.更新时间, :产品ID AS 产品ID 
                            FROM 外部服务 b, 外部服务分类 a 
                            WHERE a.ID = b.分类ID AND a.产品ID = :产品ID )
                             SELECT w.* FROM w where 1 = 1 ";
                var sqlbuilder = this.SqlBuilder;
                if (!(keyword.IsNullOrEmpty()))
                {
                    sql += "and (upper(w.分类编码) like '%' || upper(:keyword) || '%' or upper(w.分类名称) like '%' || upper(:keyword) || '%' or upper(w.分类简码) like '%' || upper(:keyword) || '%')";
                    sqlbuilder.Parameters("keyword", keyword);
                }
                sql += " order by w.分类编码";
                return sqlbuilder.SqlText(sql).Parameters("产品ID", productId).Select();
            }
            
        }

        /// <summary>
        /// 根据产品ID获取分类
        /// </summary>
        /// <param name="productIds"></param>
        /// <returns></returns>
        public DataTable GetMenuByProduct(List<string> productIds)
        {
            var parameterNames = productIds.Select((id, index) => $":产品ID{index}").ToArray();
            string inClause = string.Join(", ", parameterNames);

            string sql = $"select a.*, b.名称 as 产品名称 from 外部服务分类 a, 产品系统配置 b where a.产品ID = b.ID and a.产品ID in ({inClause}) order by b.编码, a.分类编码";

            var sqlBuilder = this.SqlBuilder.SqlText(sql);

            for (int i = 0; i < productIds.Count; i++)
            {
                sqlBuilder.Parameters($"产品ID{i}", productIds[i]);
            }
            return sqlBuilder.Select();
        }
        /// <summary>
        /// 获取编码
        /// </summary>
        /// <param name="tableName">表名</param>
        /// <returns></returns>
        public string GetMaxClassCode(string parent_id, int type, string productId)
        {
            var sql = "";
            var sqlbuilder = this.SqlBuilder;
            string parentCode = "";//上级编码

            if (!parent_id.IsNullOrEmpty())
            {
                parentCode = this.SelectBuilder.From("外部服务分类").Columns("分类编码").Where("ID", parent_id).Select<string>();
            }
            if (type == 1)
            {
                sql += "select MAX(分类编码) from 外部服务分类 where 产品ID = :产品ID ";
                sqlbuilder.Parameters("产品ID", productId);
                if (!parent_id.IsNullOrEmpty())
                {
                    sql += "and 上级ID = :parent_id";
                    sqlbuilder.Parameters("parent_id", parent_id);
                }
                else
                {
                    sql += "and 上级ID is null";
                }
            }
            else
            {
                sql += "select MAX(服务编码) from 外部服务 where 1 = 1";
            }
            var code = sqlbuilder.SqlText(sql).Select<string>();
            if (!code.IsNullOrEmpty() && !parentCode.IsNullOrEmpty())
            {
                code = code.Substring(parentCode.Length);
            }
            return parentCode + "" + GetNextCode(code);
        }

        /// <summary>
        /// 获取指定编码的下一编码，支持字母、数字
        /// </summary>
        /// <param name="code"></param>
        /// <returns></returns>
        public string GetNextCode(string code)
        {
            int bytAdd, bytUp = 0;
            Regex regNum = new Regex("^[0-9]");
            string nextCode = code.IsNullOrEmpty() ? "00" : code;
            for (int i = nextCode.Length; i >= 1; i--)
            {
                bytAdd = (i == nextCode.Length) ? 1 : 0;
                string str = nextCode.Substring(i - 1, 1);
                if (regNum.IsMatch(str))
                {
                    if ((str.ToInt32() + bytAdd + bytUp) < 10)
                    {
                        nextCode = nextCode.Substring(0, i - 1) + (str.ToInt32() + bytAdd + bytUp).ToString() + nextCode.Substring(i);
                        bytUp = 0;
                    }
                    else
                    {
                        nextCode = nextCode.Substring(0, i - 1) + "0" + nextCode.Substring(i);
                        bytUp = 1;
                    }
                }
                else
                {
                    if ((Encoding.ASCII.GetBytes(str)[0] + bytAdd + bytUp) <=
                        (Encoding.ASCII.GetBytes(str)[0] + Convert.ToInt16('Z') - Encoding.ASCII.GetBytes(str.ToUpper())[0]))
                    {
                        nextCode = nextCode.Substring(0, i - 1) + Convert.ToChar(Encoding.ASCII.GetBytes(str)[0] + bytAdd + bytUp) + nextCode.Substring(i);
                        bytUp = 0;
                    }
                    else
                    {
                        nextCode = nextCode.Substring(0, i - 1) + "0" + nextCode.Substring(i);
                        bytUp = 1;
                    }
                }
                if (bytUp == 0)
                {
                    break;
                }
            }
            if ("1".PadRight(nextCode.Length + 1, '0') == ("1" + nextCode))
            {
                nextCode = "1" + nextCode;
            }
            return nextCode;
        }

        /// <summary>
        /// 保存服务分类
        /// </summary>
        /// <param name="model"></param>
        /// <returns></returns>
        public bool SaveExternalClass(ExternalClass model)
        {
            if(model.ID.IsNullOrEmpty())
            {
                return this.InsertBuilder.Insert("外部服务分类")
                                     .Column("ID", Guid.NewGuid().ToString())
                                     .Column("上级ID", model.上级ID)
                                     .Column("分类编码", model.分类编码)
                                     .Column("分类名称", model.分类名称)
                                     .Column("分类简码", model.分类简码)
                                     .Column("产品ID", model.产品ID)
                                     .Column("创建时间", DateTime.Now)
                                     .Execute() > 0;
            }else
            {
                return this.UpdateBuilder.Update("外部服务分类")
                                     .Column("上级ID", model.上级ID)
                                     .Column("分类编码", model.分类编码)
                                     .Column("分类名称", model.分类名称)
                                     .Column("分类简码", model.分类简码)
                                     .Column("产品ID", model.产品ID)
                                     .Column("更新时间", DateTime.Now)
                                     .Where("ID", model.ID)
                                     .Execute() > 0;
            }
        }
        /// <summary>
        /// 删除服务分类
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public bool DeleteClass(string id)
        {
            int i = this.SelectBuilder.From("外部服务")
                        .Columns("Count(1)")
                        .Where("分类ID", id)
                        .Select<int>();
            if(i == 0)
            {
                return this.DeleteBulider.Delete("外部服务分类")
                    .Where ("ID", id)
                    .Execute() > 0 ;
            }else
            {
                return false;
            }
        }
        /// <summary>
        /// 获取服务目录
        /// </summary>
        /// <param name="class_id">分类id</param>
        /// <param name="keyword">关键字</param>
        /// <returns></returns>
        public DataTable GetExternal (string class_id, string keyword, int pageSize, int pageNum, out int total)
        {
            string sql = "select a.*, ('【' || b.分类编码 || '】' || b.分类名称) as 分类名称 from 外部服务 a, 外部服务分类 b where a.分类ID  = b.ID and a.分类ID = :class_id ";
            var sqlbuilder = this.SqlBuilder;
            if (!(keyword.IsNullOrEmpty()))
            {
                sql += "and (upper(a.服务编码) like '%' || upper(:keyword) || '%' or a.服务名称 like '%' || :keyword || '%' or upper(a.服务简码) like '%' || upper(:keyword) || '%')";
                sqlbuilder.Parameters("keyword", keyword);
            }
            return sqlbuilder.SqlText(sql)
                .Parameters("class_id", class_id)
                .Paging(pageNum, pageSize)
                .Select(out total);
        }
        /// <summary>
        /// 保存服务
        /// </summary>
        /// <param name="model"></param>
        /// <returns></returns>
        public bool SaveExternal(External model)
        {
            if (model.ID.IsNullOrEmpty())
            {
                return this.InsertBuilder.Insert("外部服务")
                                     .Column("ID", Guid.NewGuid().ToString())
                                     .Column("分类ID", model.分类ID)
                                     .Column("服务编码", model.服务编码)
                                     .Column("服务名称", model.服务名称)
                                     .Column("服务简码", model.服务简码)
                                     .Column("服务地址", model.服务地址)
                                     .Column("功能简述", model.功能简述)
                                     .Column("详细描述", model.详细描述)
                                     .Column("请求方式", model.请求方式)
                                     .Column("创建时间", DateTime.Now)
                                     .Execute() > 0;
            }
            else
            {
                return this.UpdateBuilder.Update("外部服务")
                                     .Column("分类ID", model.分类ID)
                                     .Column("服务编码", model.服务编码)
                                     .Column("服务名称", model.服务名称)
                                     .Column("服务简码", model.服务简码)
                                     .Column("服务地址", model.服务地址)
                                     .Column("功能简述", model.功能简述)
                                     .Column("详细描述", model.详细描述)
                                     .Column("请求方式", model.请求方式)
                                     .Column("更新时间", DateTime.Now)
                                     .Where("ID", model.ID)
                                     .Execute() > 0;
            }
        }
        /// <summary>
        /// 获取参数
        /// </summary>
        /// <param name="type"></param>
        /// <param name="id"></param>
        /// <returns></returns>
        public List<dto_parmater> GetParameter(string type, string id)
        {
            var sql = "SELECT a.*, (SELECT b.参数名称 FROM 外部服务参数 b WHERE b.ID = a.父节点ID) AS 父节点 FROM 外部服务参数 a where a.服务ID = :服务ID and 参数类型= :参数类型 order by a.序号";
            return this.SqlBuilder.SqlText(sql)
                .Parameters("服务ID", id)
                .Parameters("参数类型", type)
                .Select().ToList<dto_parmater>();
        }

        /// <summary>
        /// 参数设置
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public v_parameter GetExternalParameter(string id)
        {
            var parmeter = new v_parameter();
            var external = this.SelectBuilder.From("外部服务")
                                .Columns("*")
                                .Where ("ID", id)
                                .Select().ToList<External>()?.FirstOrDefault();
            parmeter.服务ID = id;
            parmeter.出参 = external.出参;
            parmeter.入参 = external.入参;
            var sql = "SELECT a.*, (SELECT b.参数名称 FROM 外部服务参数 b WHERE b.ID = a.父节点ID) AS 父节点 FROM 外部服务参数 a where a.服务ID = :服务ID and 参数类型= :参数类型 order by a.序号";
            parmeter.outParameter = this.SqlBuilder.SqlText(sql)
                                        .Parameters("服务ID", id)
                                        .Parameters("参数类型", "out")
                                        .Select().ToList<dto_parmater>();
            parmeter.inParameter = this.SqlBuilder.SqlText(sql)
                                        .Parameters("服务ID", id)
                                        .Parameters("参数类型", "in")
                                        .Select().ToList<dto_parmater>();
            return parmeter;
        }
        /// <summary>
        /// 保存参数设置
        /// </summary>
        /// <param name="model"></param>
        /// <returns></returns>
        public bool SavaExternalSet(v_parameter model)
        {
            bool res = this.UpdateBuilder.Update("外部服务")
                                     .Column("入参", model.入参)
                                     .Column("出参", model.出参)
                                     .Where("ID",  model.服务ID)
                                     .Execute() > 0;
            res = this.DeleteBulider.Delete("外部服务参数")
                               .Where("服务ID", model.服务ID)
                               .Execute() > 0;
            var inParameter = new List<ExternalParmater>();
            var outParameter = new List<ExternalParmater>();
            if (model.inParameter.Count > 0)
            {
                foreach (var item in model.inParameter)
                {
                    item.ID = Guid.NewGuid().ToString();
                }
                var sno = 1;
                foreach (var item in model.inParameter)
                {
                    res = this.InsertBuilder.Insert("外部服务参数")
                                         .Column("ID", item.ID)
                                         .Column("服务ID", model.服务ID)
                                         .Column("参数名称", item.参数名称)
                                         .Column("数据类型", item.数据类型)
                                         .Column("是否必填", item.是否必填)
                                         .Column("是否数组", item.是否数组)
                                         .Column("参数说明", item.参数说明)
                                         .Column("参数类型", "in")
                                         .Column("序号", sno)
                                         .Column("父节点ID", (from s in model.inParameter where item.父节点 == s.参数名称 select s.ID).FirstOrDefault())
                                         .Execute() > 0;
                    sno++;
                }
            }
            if(model.outParameter != null && model.outParameter.Count > 0)
            {
                foreach (var item in model.outParameter)
                {
                    item.ID = Guid.NewGuid().ToString();
                }
                var sno = 1;
                foreach (var item in model.outParameter)
                {
                    res = this.InsertBuilder.Insert("外部服务参数")
                                         .Column("ID", item.ID)
                                         .Column("服务ID", model.服务ID)
                                         .Column("参数名称", item.参数名称)
                                         .Column("数据类型", item.数据类型)
                                         .Column("是否必填", item.是否必填)
                                         .Column("是否数组", item.是否数组)
                                         .Column("参数说明", item.参数说明)
                                         .Column("参数类型", "out")
                                         .Column("序号", sno)
                                         .Column("父节点ID", (from s in model.outParameter where item.父节点 == s.参数名称 select s.ID).FirstOrDefault())
                                         .Execute() > 0;
                    sno++;
                }
            }

            return res;
        }
        /// <summary>
        /// 删除服务和出入参信息
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public bool deleteExternal(string id)
        {
            bool res = this.DeleteBulider.Delete("外部服务参数")
                                .Where("服务ID", id)
                                .Execute() > 0;
            res = this.DeleteBulider.Delete("外部服务")
                            .Where("ID", id)
                            .Execute() > 0;

            return res;
        }
        
        /// <summary>
        /// 复制
        /// </summary>
        /// <param name="model"></param>
        /// <returns></returns>
        public bool CopyExternalByClass(dto_copy_class model)
        {
            bool res = false;
            foreach (var item in model.classIds)
            {
                var externalClass = this.SelectBuilder.From("外部服务分类").Columns("*")
                                            .Where("ID", item)
                                            .Where("产品ID", model.oldProductId)
                                            .Select()
                                            .ToList<ExternalClass>()?
                                            .FirstOrDefault();
                if(externalClass != null)
                {
                    return true;
                }
                var externalClass1 = this.SelectBuilder.From("外部服务分类").Columns("*")
                                            .Where("分类编码", externalClass.分类编码)
                                            .Where("分类名称", externalClass.分类名称)
                                            .Where("产品ID", model.productId)
                                            .Select()
                                            .ToList<ExternalClass>()?
                                            .FirstOrDefault();
                var class_id = Guid.NewGuid().ToString();
                if (externalClass1 != null)
                {
                    class_id = externalClass1.ID;
                }
                else
                {
                    res = this.InsertBuilder.Insert("外部服务分类")
                                     .Column("ID", class_id)
                                     .Column("分类编码", externalClass.分类编码)
                                     .Column("分类名称", externalClass.分类名称)
                                     .Column("分类简码", externalClass.分类简码)
                                     .Column("产品ID", model.productId)
                                     .Column("创建时间", DateTime.Now)
                                     .Execute() > 0;
                }

                var external = this.SelectBuilder.From("外部服务").Columns("*")
                                                .Where("分类ID", externalClass.ID)
                                                .Select()
                                                .ToList<External>();
                if(external != null)
                {
                    foreach (var data in external)
                    {
                        var external1 = this.SelectBuilder.From("外部服务").Columns("*")
                                                .Where("服务名称", data.服务名称)
                                                .Where("服务编码", data.服务编码)
                                                .Where("分类ID", class_id)
                                                .Select()
                                                .ToList<External>()?
                                                .FirstOrDefault();

                        var external_id = Guid.NewGuid().ToString();
                        if (external1 != null)
                        {
                            external_id = external1.ID;
                        }
                        else
                        {
                            res = this.InsertBuilder.Insert("外部服务")
                                         .Column("ID", external_id)
                                         .Column("分类ID", class_id)
                                         .Column("服务编码", data.服务编码)
                                         .Column("服务名称", data.服务名称)
                                         .Column("服务简码", data.服务简码)
                                         .Column("服务地址", data.服务地址)
                                         .Column("功能简述", data.功能简述)
                                         .Column("详细描述", data.详细描述)
                                         .Column("入参", data.入参)
                                         .Column("出参", data.出参)
                                         .Column("创建时间", DateTime.Now)
                                         .Execute() > 0;
                        }

                        var externalParmaters = this.SelectBuilder.From("外部服务参数").Columns("*")
                                                .Where("服务ID", data.ID)
                                                .Select()
                                                .ToList<ExternalParmater>();
                        res = this.DeleteBulider.Delete("外部服务参数")
                                   .Where("服务ID", external_id)
                                   .Execute() > 0;
                        if(externalParmaters != null)
                        {
                            foreach (var parmater in externalParmaters)
                            {
                                res = this.InsertBuilder.Insert("外部服务参数")
                                                 .Column("ID", Guid.NewGuid().ToString())
                                                 .Column("服务ID", external_id)
                                                 .Column("参数名称", parmater.参数名称)
                                                 .Column("数据类型", parmater.数据类型)
                                                 .Column("是否必填", parmater.是否必填)
                                                 .Column("是否数组", parmater.是否数组)
                                                 .Column("参数说明", parmater.参数说明)
                                                 .Column("参数类型", parmater.参数类型)
                                                 .Column("序号", parmater.序号)
                                                 .Column("父节点ID", parmater.父节点ID)
                                                 .Execute() > 0;
                            }
                        }
                    }
                }
            }
            return res;     
        }

        /// <summary>
        /// 复制
        /// </summary>
        /// <param name="model"></param>
        /// <returns></returns>
        public bool CopyExternal(dto_copy_external model)
        {
            bool res = false;
            foreach (var item in model.externalIds)
            {
                var external = this.SelectBuilder.From("外部服务").Columns("*")
                                                .Where("ID", item)
                                                .Select()
                                                .ToList<External>().FirstOrDefault();

                var external1 = this.SelectBuilder.From("外部服务").Columns("*")
                                                .Where("服务名称", external.服务名称)
                                                .Where("服务编码", external.服务编码)
                                                .Where("分类ID", model.classId)
                                                .Select()
                                                .ToList<External>()?
                                                .FirstOrDefault();
                if(external1 == null)
                {
                    var external_id = Guid.NewGuid().ToString();
                    res = this.InsertBuilder.Insert("外部服务")
                                         .Column("ID", external_id)
                                         .Column("分类ID", model.classId)
                                         .Column("服务编码", external.服务编码)
                                         .Column("服务名称", external.服务名称)
                                         .Column("服务简码", external.服务简码)
                                         .Column("服务地址", external.服务地址)
                                         .Column("功能简述", external.功能简述)
                                         .Column("详细描述", external.详细描述)
                                         .Column("入参", external.入参)
                                         .Column("出参", external.出参)
                                         .Column("创建时间", DateTime.Now)
                                         .Execute() > 0;

                    var externalParmaters = this.SelectBuilder.From("外部服务参数").Columns("*")
                                                .Where("服务ID", external.ID)
                                                .Select()
                                                .ToList<ExternalParmater>();
                    res = this.DeleteBulider.Delete("外部服务参数")
                               .Where("服务ID", external_id)
                               .Execute() > 0;
                    if (externalParmaters != null)
                    {
                        foreach (var parmater in externalParmaters)
                        {
                            res = this.InsertBuilder.Insert("外部服务参数")
                                             .Column("ID", Guid.NewGuid().ToString())
                                             .Column("服务ID", external_id)
                                             .Column("参数名称", parmater.参数名称)
                                             .Column("数据类型", parmater.数据类型)
                                             .Column("是否必填", parmater.是否必填)
                                             .Column("是否数组", parmater.是否数组)
                                             .Column("参数说明", parmater.参数说明)
                                             .Column("参数类型", parmater.参数类型)
                                             .Column("序号", parmater.序号)
                                             .Column("父节点ID", parmater.父节点ID)
                                             .Execute() > 0;
                        }
                    }
                }
            }
            return res;
        }

        /// <summary>
        /// 查询授权接口
        /// </summary>
        /// <param name="keyword"></param>
        /// <param name="pageSize"></param>
        /// <param name="pageNum"></param>
        /// <returns></returns>
        public DataTable GetAccredit(string keyword, int pageSize, int pageNum,int is_stop, out int total)
        {
            string sql = @"WITH w AS (
                        SELECT a.ID, a.地址标识, a.状态, a.产品ID, b.名称 AS 产品名称, '【' || c.分类编码 || '】' || c.分类名称 AS 分类名称, a.创建人, a.创建时间, a.分类IDS
                        FROM  外部服务产品授权 a
                        LEFT JOIN 产品系统配置 b ON a.产品ID = b.ID
                        LEFT JOIN 外部服务分类 c ON ',' || a.分类IDS || ',' LIKE '%,' || c.ID || ',%'
                    )
                    SELECT w.ID, w.地址标识, w.状态, w.产品ID, w.产品名称, LISTAGG(w.分类名称, ',') WITHIN GROUP (ORDER BY w.分类名称) AS 授权分类, w.创建人, w.创建时间, w.分类IDS
                    FROM  w where 1= 1 ";
            if(is_stop != 1)
            {
                sql += "and w.状态 <> 0";
            }
            var sqlbuilder = this.SqlBuilder;
            if (!(keyword.IsNullOrEmpty()))
            {
                sql += "and (upper(w.地址标识) like '%' || upper(:keyword) || '%' or upper(w.产品名称) like '%' || upper(:keyword) || '%')";
                sqlbuilder.Parameters("keyword", keyword);
            }
            sql += "GROUP BY w.ID, w.地址标识, w.状态, w.产品ID, w.产品名称, w.创建人, w.创建时间, w.分类IDS";
            return sqlbuilder.SqlText(sql)
                .Paging(pageNum, pageSize)
                .Select(out total);
        }
        /// <summary>
        /// 保存校验时间
        /// </summary>
        /// <param name="model"></param>
        /// <returns></returns>
        public bool SavaAccredit(ExternalAccredit model)
        {
            if(model.ID.IsNullOrEmpty())
            {
                return this.InsertBuilder.Insert("外部服务产品授权")
                                    .Column("ID", Guid.NewGuid().ToString())
                                    .Column("产品ID", model.产品ID)
                                     .Column("分类IDS", model.分类IDS)
                                     .Column("状态", model.状态)
                                     .Column("创建人", model.创建人)
                                     .Column("创建时间", DateTime.Now)
                                     .Column("地址标识", GenerateRandomString(8))
                                    .Execute() > 0;
            }else
            {
                var updateBuilder = this.UpdateBuilder.Update("外部服务产品授权")
                                                .Column("状态", model.状态);
                if(!model.产品ID.IsNullOrEmpty())
                {
                    updateBuilder.Column("产品ID", model.产品ID)
                                 .Column("分类IDS", model.分类IDS)
                                 .Column("更新人", model.更新人)
                                 .Column("更新时间", DateTime.Now);
                }
                return updateBuilder.Where("ID", model.ID).Execute() > 0;
            }
        }
        /// <summary>
        /// 查询授权接口
        /// </summary>
        /// <param name="keyword"></param>
        /// <param name="pageSize"></param>
        /// <param name="pageNum"></param>
        /// <returns></returns>
        public DataTable GetAccredView(string id, string channel_id, string project_id, int pageSize, int pageNum, int is_stop, out int total)
        {
            string sql = @"SELECT a.ID, a.过期时间, b.名称 AS 项目名称, a.授权验证码 , a.申请时间 , a.受理时间 , a.状态 , b.状态 as 项目状态
                        FROM 外部服务授权验证 a, 项目信息 b WHERE a.项目ID  = b.ID";
            if(is_stop != 1)
            {
                sql += " and a.状态 <> 0";
            }
            var sqlbuilder = this.SqlBuilder;
            if (!id.IsNullOrEmpty())
            {
                sql += " and a.授权产品ID = :授权产品ID";
                sqlbuilder.Parameters("授权产品ID", id);
            }else
            {
                sql += " and a.授权验证码 is null";
            }
            if(!channel_id.IsNullOrEmpty())
            {
                sql += " and b.渠道ID = :渠道ID";
                sqlbuilder.Parameters("渠道ID", channel_id);
            }

            if (!project_id.IsNullOrEmpty())
            {
                sql += " and a.项目ID = :项目ID";
                sqlbuilder.Parameters("项目D", project_id);
            }
            return sqlbuilder.SqlText(sql)
                .Paging(pageNum, pageSize)
                .Select(out total);
        }

        public DataTable GetRecords(dto_records_in model, out int total)
        {
            var sql = @"SELECT a.*, b.名称 AS 渠道名称, c.名称 AS 项目名称, 
                        d.地址标识  from 外部服务文档访问记录 a, 渠道信息 b, 
                        项目信息 c, 外部服务产品授权 d 
                        WHERE a.渠道ID = b.ID AND a.项目ID = c.ID 
                        AND a.授权ID = d.ID ";
            var sqlBuilder = this.SqlBuilder;
            if(!model.渠道ID.IsNullOrEmpty())
            {
                sql += " and a.渠道ID = :渠道ID";
                sqlBuilder.Parameters("渠道ID", model.渠道ID);
            }
            if (!model.项目ID.IsNullOrEmpty())
            {
                sql += " and a.项目ID = :项目ID";
                sqlBuilder.Parameters("项目ID", model.项目ID);
            }
            if (!model.访问操作.IsNullOrEmpty())
            {
                sql += " and a.访问操作 = :访问操作";
                sqlBuilder.Parameters("访问操作", model.访问操作);
            }
            return sqlBuilder.SqlText(sql)
                .Paging(model.pageNum, model.pageSize)
                .Select(out total);
        }
    }
}