sys/Hh.Mes.Service/Repository/RepositorySqlSugar.cs

using Hh.Mes.Common;
using Hh.Mes.Common.config;
using Hh.Mes.Common.Http;
using Hh.Mes.Common.Infrastructure;
using Hh.Mes.Common.Json;
using Hh.Mes.Common.log;
using Hh.Mes.Common.Request;
using Hh.Mes.Pojo.System;
using Hh.Mes.POJO.Entity;
using Hh.Mes.POJO.EnumEntitys;
using Hh.Mes.POJO.Response;
using Hh.Mes.Service.Logs;
using Hh.Mes.Service.SystemAuth;
using Microsoft.AspNetCore.Http;
using Microsoft.Extensions.Caching.Distributed;
using Newtonsoft.Json;
using SqlSugar;
using System;
using System.Collections.Generic;
using System.ComponentModel.DataAnnotations;
using System.ComponentModel.DataAnnotations.Schema;
using System.Diagnostics;
using System.IO;
using System.Linq;
using System.Linq.Expressions;
using System.Net;
using System.Reflection;
using System.Text;
namespace Hh.Mes.Service.Repository
{
    /// <summary>
    /// SqlSugar
    /// http://www.codeisbug.com/Home/Doc?typeId=1228 
    /// C#拼接SQL中in条件 https://www.cnblogs.com/zhuyuchao/p/10412031.html
    /// </summary>
    /// <typeparam name="T"></typeparam>
    public class RepositorySqlSugar<T> : SimpleClient<T> where T : class, new()
    {
        private readonly IDistributedCache _cache;
        #region 属性
        /// <summary>
        /// 用户信息web（xxxServer注入的时候传入）
        /// </summary>
        public dynamic sysWebUser { get; set; }
        /// <summary>
        /// api接口身份信息(类混乱结果)
        /// </summary>
        public UserAuthSession sysUserApi { get; set; }
        /// <summary>
        /// api接口身份信息(类混乱结果)   Account
        /// </summary>
        public string sysUserApiAccount { get; set; }
        /// <summary>
        /// 上传文件夹目录
        /// </summary>
        public string uploadFolder { get; set; }
        /// <summary>
        ///动态值
        /// </summary>
        public dynamic ObjValue { get; set; }
        #endregion
        public RepositorySqlSugar(IAuth auth, IDistributedCache cache, ISqlSugarClient context = null) : this(auth, context)
        {
            _cache = cache;
        }
        public RepositorySqlSugar(IAuth auth, ISqlSugarClient context = null) : this(context)
        {
            var authStrategyContext = auth.GetCurrentUser();
            if (authStrategyContext != null)
            {
                sysWebUser = new LoginInfo
                {
                    Id = authStrategyContext.User.Id,
                    Account = authStrategyContext.User.Account,
                    Name = authStrategyContext.User.Name,
                };
            }
        }
        public RepositorySqlSugar(ISqlSugarClient context = null) : base(context)//注意这里要有默认值等于null
        {
            if (context == null)
            {
                var conStr = ConfigRead.GetInstance.GetAppsetConnection().BaseDBContext;
                base.Context = new SqlSugarClient(new ConnectionConfig()
                {
                    //数据库类型
                    DbType = DbType.SqlServer,
                    //从特性读取主键自增信息
                    InitKeyType = InitKeyType.Attribute,
                    //自动释放和关闭数据库连接，如果有事务事务结束时关闭，否则每次操作后关闭
                    IsAutoCloseConnection = true,
                    //连接符字串
                    ConnectionString = conStr,
                    //增加对C#自带的实体特性支持，比如实体的[Table]特性，字段的[Key]特性
                    ConfigureExternalServices = SetExternalServices(),
                    MoreSettings = new ConnMoreSettings()
                    {
                        IsWithNoLockQuery = true//全局 With(nolock) 
                    },
                });
                //OnError 写日志
                base.Context.Aop.OnError = ExceptionLog;
                base.Context.Aop.OnLogExecuting = (sql, pars) => //SQL执行前
                {
                    //获取原生SQL推荐 5.1.4.63  性能OK
                    //UtilMethods.GetNativeSql(sql,pars)
                    //获取无参数化SQL 影响性能只适合调试
                    //UtilMethods.GetSqlString(DbType.SqlServer,sql,pars)
                };
                //SQL执行完
                base.Context.Aop.OnLogExecuted = (sql, pars) =>
                {
                    //执行完了可以输出SQL执行时间 (OnLogExecutedDelegate) 
                    var isInterfacelog = sql.IndexOf("sys_interface_log") > 0;
                    if (base.Context.Ado.SqlExecutionTime.TotalSeconds > 15 && !isInterfacelog)
                    {
                        Console.WriteLine("数据库脚本执行时间超过15秒请查看日志,核实脚本是否存在优化");
                        QueueInterLog.GetInstance.EnqueueInterLog(null, null, EnumLog.AOP脚本执行超过15秒.ToString(), sql, 0, user: "AOP", sysTitle: EnumLog.AOP脚本执行超过15秒.ToString());
                    }
                };
            }
            uploadFolder = "UploadFile";
        }
        #region SqlSugar   Aop、队列方法 、跨上下文队列方法
        /// <summary>
        /// 设置自定义特性（增加对C#自带的实体特性支持，比如实体的[Table]特性，字段的[Key]特性等）
        /// </summary>
        /// <returns></returns>
        private static ConfigureExternalServices SetExternalServices()
        {
            return new ConfigureExternalServices()
            {
                //设置实体的SqlSugar特性，从而把C#自带的[Table]特性变成SqlSugar特性
                EntityNameService = (type, entity) =>
                {
                    var attributes = type.GetCustomAttributes(true);
                    if (attributes.Any(it => it is TableAttribute))
                    {
                        entity.DbTableName = (attributes.First(it => it is TableAttribute) as TableAttribute).Name;
                    }
                },
                //设置字段的SqlSugar特性，从而把C#自带的字段特性变成SqlSugar特性
                EntityService = (property, column) =>
                {
                    //获取所有特性
                    var attributes = property.GetCustomAttributes(true);//get all attributes 
                    // 如果特性含有字段的[Key]特性，就设置字段SqlSugar的IsPrimaryKey特性
                    if (attributes.Any(it => it is KeyAttribute))
                    {
                        column.IsPrimarykey = true;
                    }
                    // 如果特性含有字段的[NotMapped]特性，就设置字段SqlSugar的IsIgnore特性
                    if (attributes.Any(it => it is NotMappedAttribute))
                    {
                        column.IsIgnore = true;
                    }
                    // 如果特性含有字段的[Column]特性，就设置字段SqlSugar的DbColumnName特性
                    if (attributes.Any(it => it is ColumnAttribute))
                    {
                        column.DbColumnName = (attributes.First(it => it is ColumnAttribute) as ColumnAttribute).Name;
                    }
                }
            };
        }
        /// <summary>
        /// 记录错误SQL日志 第一次启动失败判断 不写入数据库日志
        /// </summary>
        /// <param name="exp">SqlSugar异常实体</param>
        private static void ExceptionLog(SqlSugarException exp)
        {
            if (exp.Message.IndexOf("连接数据库过程中发生错误") > -1) return;
            StringBuilder sqlString = new StringBuilder();
            StringBuilder parameterString = new StringBuilder();
            sqlString.Append(exp.Sql);
            SugarParameter[] sugarParameters = exp.Parametres as SugarParameter[];
            if (sugarParameters != null && sugarParameters.Length > 0)
            {
                //参数从长到短来循环，这样替换sql中的参数的时候，避免@Parame11被@Parame给替换的情况
                foreach (var item in sugarParameters.OrderByDescending(t => t.ParameterName.Length))
                {
                    if (item.DbType.ToString().Contains("String") || item.DbType.ToString().Contains("Date") ||
                        item.DbType == System.Data.DbType.Guid || item.DbType == System.Data.DbType.Time ||
                        item.DbType == System.Data.DbType.Object || item.DbType == System.Data.DbType.Xml)
                    {
                        sqlString.Replace(item.ParameterName, "'" + item.Value.ToString() + "'");
                        parameterString.Append(item.ParameterName + " = '" + item.Value + "',");
                    }
                    else
                    {
                        sqlString.Replace(item.ParameterName, item.Value.ToString());
                        parameterString.Append(item.ParameterName + " = " + item.Value + ",");
                    }
                }
                //删除逗号
                parameterString.Remove(parameterString.Length - 1, 1);
            }
            var errorMsg = $"【SqlSugar】执行SQL语句【{sqlString}】，参数【{parameterString}】,发生错误【{exp.Message}】";
            QueueInterLog.GetInstance.EnqueueInterLog(null, null, EnumLog.AOP全局错误.ToString(), errorMsg, 0, user: "AOP", sysTitle: EnumLog.AOP全局错误.ToString());
            Log4NetHelper.Instance.Error(errorMsg);
        }
        /// <summary>
        /// 执行SqlSugar的队列 封装方法（取代打包的执行方法，因为打包的执行不能超过200个变量，很容易超出）
        /// </summary>
        public int ExecuteQueues(ISqlSugarClient context)
        {
            int count = 0;
            try
            {
                if (context.Queues.Count == 0)
                {
                    return count;
                }
                #region 将context.Queues中的sql语句和参数，一条一条取出来，然后再拼接成大SQL，减少发送次数。
                //StringBuilder sb = new StringBuilder();
                //List<SugarParameter> paramList = new List<SugarParameter>();
                //List<Tuple<string, List<SugarParameter>>> sugarCommandList = new List<Tuple<string, List<SugarParameter>>>();
                //for (var i = 0; i < context.Queues.Count; i++)
                //{
                //    int sqlStringCount = sb.Length + context.Queues[i].Sql.Length;
                //    int paramCount = paramList.Count + context.Queues[i].Parameters.Length;
                //    //SQL Server数据库的SQL语句，参数不能多于2000个，字符几乎无限就暂定为200万字(4M的大小)
                //    if (sqlStringCount > 2 * 1024 * 1024 || paramCount > 1999)
                //    {
                //        //超出了一条SQL语句的最大范围，就把SQL放到sugarCommandList中，然后再新实例化新的对象
                //        sugarCommandList.Add(new Tuple<string, List<SugarParameter>>(sb.ToString(), paramList));
                //        sb = new StringBuilder();
                //        paramList = new List<SugarParameter>();
                //    }
                //    var row = i + 1;
                //    string sqlString = context.Queues[i].Sql;
                //    // 先排序是因为变量有id, id0等，要把字符短的在前，长的放在后面，不然先替换id0，再替换id，就会产生多次替换
                //    SugarParameter[] sugarParameters = context.Queues[i].Parameters;
                //    foreach (var item in sugarParameters)
                //    {
                //        //因为传入的有List类型，所以每个变量名都是一样的，拼接后改为"变量_#$行号"，这样每个变量名就唯一了。
                //        string ParameterNewName = item.ParameterName + "_#$" + row;
                //        //将 （"\b"表示非字符，包括空格，换行等）
                //        sqlString = sqlString.Replace(item.ParameterName + "\b" , ParameterNewName);
                //        item.ParameterName = ParameterNewName;
                //    }
                //    //将替换好的SQL和参数存放起来
                //    sb.AppendLine(sqlString);
                //    paramList.AddRange(sugarParameters);
                //    if (i == context.Queues.Count - 1)
                //    {
                //        sugarCommandList.Add(new Tuple<string, List<SugarParameter>>(sb.ToString(), paramList));
                //    }
                //}
                // 从sugarCommandList中取出拼接好的SQL，来执行。
                //context.Ado.BeginTran();
                //foreach (var item in sugarCommandList)
                //{
                //    if (!string.IsNullOrWhiteSpace(item.Item1))
                //    {
                //        count += context.Ado.ExecuteCommand(item.Item1, item.Item2);
                //    }
                //}
                //context.Ado.CommitTran();
                #endregion 
                context.Ado.BeginTran();
                foreach (var item in context.Queues)
                {
                    if (!string.IsNullOrWhiteSpace(item.Sql))
                    {
                        count += context.Ado.ExecuteCommand(item.Sql, item.Parameters);
                    }
                }
                context.Ado.CommitTran();
            }
            catch (Exception)
            {
                context.Ado.RollbackTran();
            }
            finally
            {
                context.Queues.Clear();
            }
            return count;
        }
        /// <summary>
        /// 多个上下文调用次方法
        /// 执行SqlSugar的队列 封装方法
        /// </summary>
        public int ExecuteQueues(List<ISqlSugarClient> contextList)
        {
            int count = 0;
            try
            {
                if (contextList.Count == 0)
                {
                    return count;
                }
                if (!contextList.Exists(t => t.Queues.Count > 0))
                {
                    return count;
                }
                Context.Ado.BeginTran();
                foreach (var context in contextList)
                {
                    foreach (var item in context.Queues)
                    {
                        if (!string.IsNullOrWhiteSpace(item.Sql))
                        {
                            count += Context.Ado.ExecuteCommand(item.Sql, item.Parameters);
                        }
                    }
                }
                //base.Context.Ado.RollbackTran();
                Context.Ado.CommitTran();
            }
            catch (Exception)
            {
                Context.Ado.RollbackTran();
            }
            finally
            {
                contextList.ForEach(x =>
                {
                    x.Queues.Clear();
                });
            }
            return count;
        }
        #endregion
        #region 增删改 上传文件、拼接Order条件
        /// <summary>
        /// 写入数据  封装方法 主子表写入参考 ：MateLoadTemplateService （打包提交默认带有事务：一些自定义的sql或者存储过程一起打包可能会不支持）
        /// </summary>
        /// <param name="obj">model，或者list，或者 Dictionary </param>
        /// <param name="isTransaction">true开启事务</param>
        public virtual bool Add(dynamic obj, bool isTransaction = false)
        {
            if (isTransaction)
            {
                Context.Insertable(obj).AddQueue();
                return Context.SaveQueuesAsync().Result > 0;
            }
            return Context.Insertable(obj).ExecuteCommand() > 0;
        }
        /// <summary>
        /// Update 封装方法
        /// </summary>
        public virtual bool Update<M>(M m, Expression<Func<M, bool>> expression) where M : class, new()
        {
            return Context.Updateable<M>(m).Where(expression).ExecuteCommand() > 0;
        }
        /// <summary>
        /// 删除 封装方法
        /// </summary>
        public virtual bool Delete<M>(M m, Expression<Func<M, bool>> expression) where M : class, new()
        {
            return Context.Deleteable(m).Where(expression).ExecuteCommand() > 0;
        }
        /// <summary>
        /// 上传文件,写入附件表sys_File targetTableName,targetId 必填
        /// </summary>
        public bool UploadFile(IFormFileCollection files, string contentRootPath, List<sys_File> sysFileModel)
        {
            if (files.Count != sysFileModel.Count || files.Count == 0) return false;
            var upFloder = Path.Combine(contentRootPath, uploadFolder);
            if (!Directory.Exists(upFloder)) Directory.CreateDirectory(upFloder);
            for (int i = 0; i < files.Count; i++)
            {
                if (files[i].Length == 0) continue;
                #region 文件保存磁盘
                var suffix = Path.GetExtension(files[i].FileName);
                var fileName = Path.GetFileName(files[i].FileName);
                var newFileName = Guid.NewGuid().ToString("N") + suffix;
                var filePath = Path.Combine(upFloder, newFileName);
                using (var fs = File.Create(filePath))
                {
                    files[i].CopyTo(fs);
                    fs.Flush();
                }
                #endregion
                #region 保存文件记录到数据库
                var relativePaths = $"/{uploadFolder}/" + newFileName;
                var config = ConfigRead.GetInstance.GetAppsetConnection();
                var ip = ComputerHelp.GetAddressIP();
                var httpsPort = config.HttpPort == 0 ? config.HttpsPort : config.HttpPort;
                sysFileModel[i].host = ip + ":" + httpsPort;
                sysFileModel[i].suffix = suffix;
                sysFileModel[i].createTime = DateTime.Now;
                sysFileModel[i].createBy = sysWebUser == null ? sysUserApi.Account : sysWebUser?.Account;
                sysFileModel[i].isDelete = SystemVariable.AddOrUpdateFlag;
                sysFileModel[i].fileName = fileName;
                sysFileModel[i].url = ObjValue + "://" + sysFileModel[i].host + relativePaths;
                #endregion
            }
            Context.Insertable(sysFileModel).IgnoreColumns().AddQueue();
            return Context.SaveQueues() > 0;
        }
        /// <summary>
        /// 保存文件
        /// </summary>
        public void SaveFile(byte[] files, string path)
        {
            BinaryWriter bw = new BinaryWriter(System.IO.File.Open(path, FileMode.OpenOrCreate));
            bw.Write(files);
            bw.Close();
        }
        /// <summary>
        /// 获取最大值
        /// </summary>
        /// <param name="maxField"></param>
        /// <returns></returns>
        public int GetTableMaxValue<M>(string maxField) where M : class, new()
        {
            var value = Context.Queryable<M>().Max<int>(maxField);
            return value == 0 ? 1 : value + 1;
        }
        /// <summary>
        /// 拼接Order条件
        /// </summary>
        /// <param name="pageReq">PageReq类型的分页实体</param>
        /// <param name="entity">查询对象的实体</param>
        /// <returns></returns>
        /// <exception cref="Exception"></exception>
        public Expression<Func<T, object>> LinqOrder(PageReq pageReq, T entity)
        {
            try
            {
                string propertyName = "id";
                if (pageReq != null && pageReq.field != null)
                {
                    PropertyInfo propertyInfo = entity.GetType().GetProperty(pageReq.field);
                    if (propertyInfo != null)
                    {
                        propertyName = pageReq.field;
                    }
                }
                var memberExpression = Expression.PropertyOrField(Expression.Constant(entity), propertyName);
                var expression = Expression.Lambda<Func<T, object>>(memberExpression);
                return expression;
            }
            catch (Exception ex)
            {
                throw new Exception($"{ex.Message}");
            }
        }
        #endregion
        #region Redis
        /// <summary>
        /// 清空当前用户的redis授权缓存，这样用的时候就会自动从数据库更新一次
        /// </summary>
        public void ClearCurrentUserRedis(string user = "")
        {
            var account = user;
            _cache.Remove(account);
        }
        /// <summary>
        /// 清空所有登录用户的redis授权缓存，这样用的时候就会自动从数据库更新一次
        /// </summary>
        public void ClearOnlineUserRedis()
        {
            //清空被删除用户的Redis缓存
            var userOnlineList = Context.Queryable<sys_user_online>().Where(t => true).ToList();
            foreach (var item in userOnlineList)
            {
                _cache.Remove(item.account);
            }
        }
        #endregion
        #region http
        /// <summary>
        /// http   code不是200内部记录日志，不需要外部方法单独判断写日志 
        /// </summary>
        public Response HttpMan(string url, dynamic requestData, string title, string method = "post", string contentType = "application/json", string token = null)
        {
            var stopwatch = new Stopwatch();
            stopwatch.Start();
            var response = new Response(false);
            var httpItem = new HttpItem
            {
                URL = url,
                Method = method,
                ContentType = contentType,
                Postdata = JsonConvert.SerializeObject(requestData),
                Timeout = 80000
            };
            if (!string.IsNullOrEmpty(token))
            {
                httpItem.Header.Add("Authorization", token);
            }
            var httpHelper = new HttpHelper();
            var httpResult = httpHelper.GetHtml(httpItem);
            response.Result = httpResult.Html;
            stopwatch.Stop();
            var resultMsg = title + ",失败原因：" + httpResult.Html;
            var user = sysUserApi == null ? (sysWebUser == null ? "定时器" : sysWebUser?.Account) : sysUserApi.Account;
            QueueInterLog.GetInstance.EnqueueInterLog(httpItem, httpResult, title, resultMsg, stopwatch.Elapsed.TotalMilliseconds, user: user, sysTitle: title);
            if (httpResult.StatusCode == HttpStatusCode.OK)
            {
                //根据实际情况处理
                var resultTemp = httpResult.Html.TrimStart('[').TrimEnd(']');
                dynamic result = DynamicJson.Parse(resultTemp);
                if (result == null)
                {
                    QueueInterLog.GetInstance.EnqueueInterLog(httpItem, httpResult, title, "异常错误:" + resultMsg, stopwatch.Elapsed.TotalMilliseconds, user: user, sysTitle: title);
                    return response.ResponseError(resultMsg);
                }
                //请求返回成功
                var type = result.code.GetType();
                if (type == typeof(string))
                {
                    if (result.code == "0" || result.code == "200") return response.ResponseSuccess("请求返回成功");
                }
                else if (type == typeof(int) || type == typeof(double))
                {
                    if (result.code == 0 || result.code == 200) return response.ResponseSuccess("请求返回成功");
                }
            }
            if (httpResult.StatusCode == HttpStatusCode.InternalServerError)
            {
                response.Message = resultMsg;
                QueueInterLog.GetInstance.EnqueueInterLog(httpItem, httpResult, title, "异常错误:" + response.Message, stopwatch.Elapsed.TotalMilliseconds, user: user, sysTitle: title);
                return response;
            }
            if (httpResult.StatusCode == HttpStatusCode.NotFound)
            {
                response.Message = "url:" + httpItem.URL + "错误、请求地址未找到404" + resultMsg;
                QueueInterLog.GetInstance.EnqueueInterLog(httpItem, httpResult, title, "异常错误:" + response.Message, stopwatch.Elapsed.TotalMilliseconds, user: user, sysTitle: title);
                return response;
            }
            QueueInterLog.GetInstance.EnqueueInterLog(httpItem, httpResult, title, "异常错误:" + resultMsg, stopwatch.Elapsed.TotalMilliseconds, user: user, sysTitle: title);
            response.Message = resultMsg;
            return response;
        }
        /// <summary>
        /// http  code不是200内部记录日志，不需要外部方法单独判断写日志 
        /// </summary>
        public Response HttpManWCS(string url, dynamic requestData, string title, string method = "post", string contentType = "application/json", string token = null)
        {
            var stopwatch = new Stopwatch();
            stopwatch.Start();
            var response = new Response(false);
            var httpItem = new HttpItem
            {
                URL = url,
                Method = method,
                ContentType = contentType,
                Postdata = JsonConvert.SerializeObject(requestData),
                Timeout = 80000
            };
            if (!string.IsNullOrEmpty(token))
            {
                httpItem.Header.Add("Authorization", token);
            }
            var httpHelper = new HttpHelper();
            var httpResult = httpHelper.GetHtml(httpItem);
            response.Result = httpResult.Html;
            stopwatch.Stop();
            var resultMsg = title + ",失败原因：" + httpResult.Html;
            var user = sysUserApi == null ? (sysWebUser == null ? "定时器" : sysWebUser?.Account) : sysUserApi.Account;
            QueueInterLog.GetInstance.EnqueueInterLog(httpItem, httpResult, title, resultMsg, stopwatch.Elapsed.TotalMilliseconds, user: user, sysTitle: title);
            if (httpResult.StatusCode == HttpStatusCode.OK)
            {
                //根据实际情况处理
                var resultTemp = httpResult.Html.TrimStart('[').TrimEnd(']');
                dynamic result = DynamicJson.Parse(resultTemp);
                if (result == null)
                {
                    QueueInterLog.GetInstance.EnqueueInterLog(httpItem, httpResult, title, "异常错误:" + resultMsg, stopwatch.Elapsed.TotalMilliseconds, user: user, sysTitle: title);
                    return response.ResponseError(resultMsg);
                }
                //请求返回成功
                var type = result.Code.GetType();
                if (type == typeof(string))
                {
                    if (result.Code == "0" || result.Code == "200") return response.ResponseSuccess("请求返回成功");
                }
                else if (type == typeof(int) || type == typeof(double))
                {
                    if (result.Code == 0 || result.Code == 200) return response.ResponseSuccess("请求返回成功");
                }
            }
            if (httpResult.StatusCode == HttpStatusCode.InternalServerError)
            {
                response.Message = resultMsg;
                QueueInterLog.GetInstance.EnqueueInterLog(httpItem, httpResult, title, "异常错误:" + response.Message, stopwatch.Elapsed.TotalMilliseconds, user: user, sysTitle: title);
                return response;
            }
            if (httpResult.StatusCode == HttpStatusCode.NotFound)
            {
                response.Message = "url:" + httpItem.URL + "错误、请求地址未找到404" + resultMsg;
                QueueInterLog.GetInstance.EnqueueInterLog(httpItem, httpResult, title, "异常错误:" + response.Message, stopwatch.Elapsed.TotalMilliseconds, user: user, sysTitle: title);
                return response;
            }
            QueueInterLog.GetInstance.EnqueueInterLog(httpItem, httpResult, title, "异常错误:" + resultMsg, stopwatch.Elapsed.TotalMilliseconds, user: user, sysTitle: title);
            response.Message = resultMsg;
            return response;
        }
        #endregion
        #region 业务代码 
        /// <summary>
        ///  获取字典 select * from[dbo].[sys_dict_data] where dictType = 'sysCode'
        /// </summary>
        /// <param name="dictLabel">值</param>
        /// <param name="dictType">类型</param>
        /// <returns>返回dictValue 字段值</returns>
        public virtual string GetDictionaryDictValue(string dictLabel, string dictType = "sysCode")
        {
            return Context.Queryable<sys_dict_data>().With(SqlWith.NoLock).Where(x => x.dictType == dictType && x.dictLabel == dictLabel).First()?.dictValue;
        }
        /// <summary>
        /// 获取ID 并加1, 解决同步并发问题 默认从1000开始
        /// </summary>
        /// <returns></returns>
        public string GetWorkOrderBarCode()
        {
            var id = Context.Queryable<bus_identify_config>().Max(x => x.id);
            if (id == 0)
            {
                //第一次加载 添加一行数据 
                Context.Insertable(new bus_identify_config { id = 1001 }).ExecuteCommand();
                id = 1000;
            }
            else
            {
                var bic = new bus_identify_config() { id = id + 1 };
                base.Context.Updateable(bic).Where(x => x.id == id).ExecuteCommand();
            }
            return id.ToString();
        }
        /// <summary>
        /// 获取用户的项目角色Key
        /// </summary>
        /// <param name="userAccount">用户账号</param>
        /// <returns></returns>
        public List<Guid> GetProjectRoleKeys(string userAccount)
        {
            try
            {
                var projectRoles = Context.Queryable<sys_user_project_roles_rel>().Where(x => x.account == userAccount).Select(x => x.project_roles_key).Distinct().ToList();
                return projectRoles;
            }
            catch (Exception)
            {
                return new List<Guid>();
            }
        }
        /// <summary>
        /// 根据用户读取用户角色下面的项目数据
        /// </summary>
        /// <param name="userAccount"></param>
        /// <returns></returns>
        public Dictionary<Guid, string> GetProjectInfo(string userAccount)
        {
            if (userAccount != SystemVariable.DefaultCreated)
            {
                var projectRoleKeys = GetProjectRoleKeys(userAccount);
                var result = Context.Queryable<base_project>().Where(x => SqlFunc.Subqueryable<sys_role_projects_rel>().Where(c => projectRoleKeys.Contains(c.project_roles_key) && c.project_key == x.keys).Any()).Select(x => new
                {
                    x.keys,
                    x.projectName
                }).Distinct().ToList();
                return result.ToDictionary(x => x.keys, y => y.projectName);
            }
            //超级管理员返回所有数据
            return Context.Queryable<base_project>().Select(x => new
            {
                x.keys,
                x.projectName
            }).ToList().ToDictionary(x => x.keys, y => y.projectName);
        }
        /// <summary>
        /// 根据用户读取用户角色下面的项目数据Keys 返回项目所有的keys 
        /// </summary>
        public Guid[] GetProjectInfoKeys(string userAccount)
        {
            if (userAccount != SystemVariable.DefaultCreated)
            {
                var projectRoleKeys = GetProjectRoleKeys(userAccount);
                var result = Context.Queryable<base_project>().Where(x => SqlFunc.Subqueryable<sys_role_projects_rel>().Where(c => projectRoleKeys.Contains(c.project_roles_key) && c.project_key == x.keys).Any())
                                    .Select(x => x.keys).Distinct().ToList().ToArray();
                return result;
            }
            //超级管理员返回所有数据
            return Context.Queryable<base_project>().Select(x => x.keys).ToList().ToArray();
        }
        #endregion
    }
}