ZLPMS/PMS.DBService/ExternalManage/ExternalViewService.cs

using PMS.BusinessModels.ExternalManage;
using PMS.EntityModels.ExternalManage;
using PMS.Plugins.Common;
using QWPlatform.IService;
using QWPlatform.SystemLibrary;
using System;
using System.Activities.Debugger.Symbol;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace PMS.DBService.ExternalManage
{
    public class ExternalViewService : DataServiceBase
    {
        private static readonly Random random = new Random();

        public string GenerateCaptcha(int length = 6)
        {
            const string chars = "ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789";
            lock (random) // Ensure thread-safety for the Random instance
            {
                return new string(Enumerable.Repeat(chars, length)
                    .Select(s => s[random.Next(s.Length)]).ToArray());
            }
        }
        public DataTable GetAccredit(string id)
        {
            return this.SelectBuilder.From("外部服务产品授权")
                                .Columns("*")
                                .Where("地址标识", id)
                                .Select();
        }


        public ExternalValidate GetAccreditByUrl(string id, string projectId)
        {
            string sql = "select a.* from 外部服务授权验证 a, 外部服务产品授权 b where a.授权产品ID = b.ID and b.地址标识 = :标识 and a.项目ID = :项目ID";
            return this.SqlBuilder.SqlText(sql).Parameters("标识", id)
                            .Parameters("项目ID", projectId)
                            .Select()
                            .ToList<ExternalValidate>()?
                            .FirstOrDefault();
        }

        public ExternalValidate ApplyVerifyCode(ExternalValidate model)
        {
            bool res = false;
            if (model.ID.IsNullOrEmpty())
            {
                model.ID = Guid.NewGuid().ToString();
                res  = this.InsertBuilder.Insert("外部服务授权验证")
                                        .Column("ID", model.ID)
                                        .Column("授权产品ID", model.授权产品ID)
                                        .Column("项目ID", model.项目ID)
                                        .Column("申请时间", DateTime.Now)
                                        .Column("状态", 1)
                                        .Execute() > 0;
            }
            else
            {
                if(model.过期时间.HasValue)
                {
                    res = this.UpdateBuilder.Update("外部服务授权验证")
                                        .Column("授权验证码", GenerateCaptcha())
                                        .Column("过期时间", model.过期时间)
                                        .Column("受理时间", DateTime.Now)
                                        .Where("ID", model.ID)
                                        .Execute() > 0;
                }else
                {
                    res = this.UpdateBuilder.Update("外部服务授权验证")
                                        .Column("状态", model.状态)
                                        .Where("ID", model.ID)
                                        .Execute() > 0;
                }
            }
            if(res)
            {
                return this.SelectBuilder.From("外部服务授权验证")
                                .Columns("*")
                                .Where("ID", model.ID)
                                .Select()
                                .ToList<ExternalValidate>().FirstOrDefault();
            }else
            {
                return model;
            }
        }
        public ExternalValidate VerifyCode(ExternalValidate model)
        {
            return this.SelectBuilder.From("外部服务授权验证")
                               .Columns("*")
                               .Where("授权验证码", model.授权验证码)
                               .Where("ID", model.ID)
                               .Select()
                               .ToList<ExternalValidate>()?.FirstOrDefault();
        }
        public DataTable GetExternalClass(string keyword, List<string> classIds, string id, int type)
        {
            if(type == 2)
            {
                var res = this.SelectBuilder.From("外部服务产品授权")
                                .Columns("分类IDS")
                                .Where("地址标识", id)
                                .Select<string>();

                classIds = res.Split(new[] { ','}, StringSplitOptions.RemoveEmptyEntries).ToList<string>();
            }
            string classIdsParam = string.Join(",", classIds.Select(id1 => $"'{id1}'"));
            string sql = $@"WITH w AS ( SELECT a.* 
                            FROM 外部服务分类 a 
                            WHERE a.ID in ({classIdsParam})  UNION ALL 
                            SELECT b.ID, b.分类ID AS 上级ID, b.服务编码 AS 分类编码, b.服务名称 AS 分类名称, b.服务简码 AS 分类简码, b.创建时间 ,b.更新时间, a.产品ID 
                            FROM 外部服务 b, 外部服务分类 a 
                            WHERE a.ID = b.分类ID AND a.ID in ({classIdsParam}) )
                             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).Select();
        }

        /// <summary>
        /// 获取服务列表
        /// </summary>
        /// <param name="class_id">分类id</param>
        /// <param name="keyword">关键字</param>
        /// <returns></returns>
        public List<v_external> GetExternalView(string class_id, string keyword)
        {
            var parmeter = new List<v_external>();
            string sql = "select a.* from 外部服务 a where  (a.分类ID = :class_id or 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);
            }
            parmeter = sqlbuilder.SqlText(sql)
                                .Parameters("class_id", class_id)
                                .Select().ToList<v_external>();
            if (parmeter != null && parmeter.Count > 0)
            {
                foreach (var item in parmeter)
                {
                    var sql2 = "SELECT a.*, (SELECT b.参数名称 FROM 外部服务参数 b WHERE b.ID = a.父节点ID) AS 父节点 FROM 外部服务参数 a where a.服务ID = :服务ID and 参数类型= :参数类型 order by a.序号";
                    item.outParameter = this.SqlBuilder.SqlText(sql2)
                                                .Parameters("服务ID", item.ID)
                                                .Parameters("参数类型", "out")
                                                .Select().ToList<dto_parmater>();
                    item.inParameter = this.SqlBuilder.SqlText(sql2)
                                                .Parameters("服务ID", item.ID)
                                                .Parameters("参数类型", "in")
                                                .Select().ToList<dto_parmater>();
                }
            }

            return parmeter;
        }
        /// <summary>
        /// 获取全部渠道信息
        /// </summary>
        /// <returns></returns>
        public DataTable Datagrid()
        {
            return this.SelectBuilder.From("渠道信息")
                        .Columns("*")
                        .Where("状态", 1)
                        .OrderBy("编码")
                        .Select();
        }

        public bool AddAccessRecords(ExternalRecords model)
        {
            return this.InsertBuilder.Insert("外部服务文档访问记录")
                                      .Column("id", Guid.NewGuid().ToString())
                                      .Column("访问IP", model.访问IP)
                                      .Column("项目ID", model.项目ID)
                                      .Column("渠道ID", model.渠道ID)
                                      .Column("访问操作", (int)model.访问操作)
                                      .Column("操作时间", DateTime.Now)
                                      .Execute() > 0;
        }
    }
}