开发笔记 第66页

/// <summary>
/// 应用层服务接口实现
/// </summary>
public class CustomerService : MyCrudService<CustomerInfo, string, CustomerPagedDto>, ICustomerService
{
       ....
}

    /// <summary>
    /// 应用层服务接口实现
    /// </summary>
    public class CustomerService : MyCrudService<CustomerInfo, string, CustomerPagedDto>, ICustomerService
    {
        /// <summary>
        /// 自定义条件处理
        /// </summary>
        /// <param name="input">查询条件Dto</param>
        /// <returns></returns>
        protected override ISugarQueryable<CustomerInfo> CreateFilteredQueryAsync(CustomerPagedDto input)
        {
            var query = base.CreateFilteredQueryAsync(input);

            query = query
                .WhereIF(!input.ExcludeId.IsNullOrWhiteSpace(), t => t.Id != input.ExcludeId) //不包含排除ID
                .WhereIF(!input.Name.IsNullOrWhiteSpace(), t => t.Name.Contains(input.Name)) //如需要精确匹配则用Equals
                                                                                             //年龄区间查询
                .WhereIF(input.AgeStart.HasValue, s => s.Age >= input.AgeStart.Value)
                .WhereIF(input.AgeEnd.HasValue, s => s.Age <= input.AgeEnd.Value)

                //创建日期区间查询
                .WhereIF(input.CreateTimeStart.HasValue, s => s.CreateTime >= input.CreateTimeStart.Value)
                .WhereIF(input.CreateTimeEnd.HasValue, s => s.CreateTime <= input.CreateTimeEnd.Value)
                ;

            return query;
        }

        /// <summary>
        /// 根据条件获取列表
        /// </summary>
        /// <param name="input">分页查询条件</param>
        /// <returns></returns>
        public virtual async Task<PagedResultDto<TEntity>> GetListAsync(TGetListInput input)
        {
            var query = CreateFilteredQueryAsync(input);
            var totalCount = await query.CountAsync();

            query = ApplySorting(query, input);
            query = ApplyPaging(query, input);

            var list = await query.ToListAsync();

            return new PagedResultDto<TEntity>(
               totalCount,
               list
           );
        }

        /// <summary>
        /// 获取所有记录
        /// </summary>
        [HttpGet]
        [Route("all")]
        [HttpGet]public virtual async Task<ListResultDto<TEntity>> GetAllAsync()
        {
            //检查用户是否有权限，否则抛出MyDenyAccessException异常
            base.CheckAuthorized(AuthorizeKey.ListKey);

            return await _service.GetAllAsync();
        }

        /// <summary>
        /// 获取数据
        /// </summary>
        /// <returns></returns>
        private async Task<IPagedResult<CustomerInfo>> GetData()
        {
            CustomerPagedDto pagerDto = null;
            if (advanceCondition != null)
            {
                //如果有高级查询，那么根据输入信息构建查询条件
                pagerDto = new CustomerPagedDto(this.winGridViewPager1.PagerInfo);
                pagerDto = dlg.GetPagedResult(pagerDto);
            }
            else
            {
                //构建分页的条件和查询条件
                pagerDto = new CustomerPagedDto(this.winGridViewPager1.PagerInfo)
                {
                    //添加所需条件
                    Name = this.txtName.Text.Trim(),
                };

                //日期和数值范围定义
                //年龄，需在CustomerPagedDto中添加 int? 类型字段AgeStart和AgeEnd
                var Age = new ValueRange<int?>(this.txtAge1.Text, this.txtAge2.Text); //数值类型
                pagerDto.AgeStart = Age.Start;
                pagerDto.AgeEnd = Age.End;

                //创建时间，需在CustomerPagedDto中添加 DateTime? 类型字段CreationTimeStart和CreationTimeEnd
                var CreationTime = new TimeRange(this.txtCreationTime1.Text, this.txtCreationTime2.Text); //日期类型
                pagerDto.CreateTimeStart = CreationTime.Start;
                pagerDto.CreateTimeEnd = CreationTime.End;
            }

            var result = await BLLFactory<CustomerService>.Instance.GetListAsync(pagerDto);
            return result;
        }

        /// <summary>
        /// 根据字典类型名称获取所有该类型的字典列表集合(Key为名称，Value为值）
        /// </summary>
        /// <param name="dictTypeName">字典类型名称</param>
        /// <returns></returns>
        public async Task<Dictionary<string, string>> GetDictByDictType(string dictTypeName)
        {
            var query = this.Client.Queryable<DictDataInfo, DictTypeInfo>(
                (d, t) => d.DictType_ID == t.Id && t.Name == dictTypeName)
                .Select(d => d); //联合条件获取对象

            query = query.OrderBy(d => d.DictType_ID).OrderBy(d => d.Seq);//排序
            var list = await query.ToListAsync();//获取列表

            var dict = new Dictionary<string, string>();
            foreach (var info in list)
            {
                if (!dict.ContainsKey(info.Name))
                {
                    dict.Add(info.Name, info.Value);
                }
            }
            return dict;
        }

            this.Client = new SqlSugarScope(new ConnectionConfig()
            {
                DbType = this.DbType,
                ConnectionString = this.ConnectionString,
                InitKeyType = InitKeyType.Attribute,
                IsAutoCloseConnection = true, //是否自动关闭连接
                AopEvents = new AopEvents
                {
                    OnLogExecuting = (sql, p) =>
                    {
                        //Log.Information(sql);
                        //Log.Information(string.Join(",", p?.Select(it => it.ParameterName + ":" + it.Value)));
                    }
                }
            });

        /// <summary>
        /// 根据用户ID获取对应的角色列表
        /// </summary>
        /// <param name="userID">用户ID</param>
        /// <returns></returns>
        private async Task<List<RoleInfo>> GetByUser(int userID)
        {
            var query = this.Client.Queryable<RoleInfo, User_RoleInfo>(
            (t, m) => t.Id == m.Role_ID && m.User_ID == userID)
            .Select(t => t); //联合条件获取对象

            query = query.OrderBy(t => t.CreateTime);//排序
            var list = await query.ToListAsync();//获取列表
            return list;
        }

        /// <summary>
        /// 根据机构获取对应的角色列表(判断机构角色中间表）
        /// </summary>
        /// <param name="ouID">机构的ID</param>
        /// <returns></returns>
        public async Task<List<RoleInfo>> GetRolesByOu(int ouID)
        {
            var query = this.Client.Queryable<RoleInfo, OU_RoleInfo>(
            (t, m) => t.Id == m.Role_ID && m.Ou_ID == ouID)
            .Select(t => t); //联合条件获取对象

            query = query.OrderBy(t => t.CreateTime);//排序
            var list = await query.ToListAsync();//获取列表
            return list;
        }

    /// <summary>
    /// 用户角色关联
    /// </summary>
    [SugarTable("T_ACL_User_Role")]
    public class User_RoleInfo
    {         
        /// <summary>
        /// 用户ID
        /// </summary>
        [Required]
        public virtual int User_ID { get; set; }

        /// <summary>
        /// 角色ID
        /// </summary>
        [Required]
        public virtual int Role_ID { get; set; }

    }

    /// <summary>
    /// 机构角色关联
    /// </summary>
    [SugarTable("T_ACL_OU_Role")]
    public class OU_RoleInfo 
    {         
        /// <summary>
        /// 机构ID
        /// </summary>
        [Required]
        public virtual int Ou_ID { get; set; }

        /// <summary>
        /// 角色ID
        /// </summary>
        [Required]
        public virtual int Role_ID { get; set; }
    }

    /// <summary>
    /// 角色信息
    /// </summary>
    [SugarTable("T_ACL_Role")]
    public class RoleInfo : Entity<int> {  }


    /// <summary>
    /// 功能菜单
    /// </summary>
    [SugarTable("T_ACL_Menu")]
    public class MenuInfo : Entity<string> { }

    /// <summary>
    /// 客户信息
    /// 继承自Entity，拥有Id主键属性
    /// </summary>
    [SugarTable("T_Customer")]
    public class CustomerInfo : Entity<string>
    {
        /// <summary>
        /// 默认构造函数（需要初始化属性的在此处理）
        /// </summary>
        public CustomerInfo()
        {
            this.CreateTime = System.DateTime.Now;
        }

        #region Property Members

        /// <summary>
        /// 姓名
        /// </summary>
        public virtual string Name { get; set; }

        /// <summary>
        /// 年龄
        /// </summary>
        public virtual int Age { get; set; }

        /// <summary>
        /// 创建人
        /// </summary>
        public virtual string Creator { get; set; }

        /// <summary>
        /// 创建时间
        /// </summary>
        public virtual DateTime CreateTime { get; set; }

        #endregion
    }

    [Serializable]
    public abstract class Entity<TPrimaryKey> : IEntity<TPrimaryKey>
    {
        /// <summary>
        /// 实体类唯一主键
        /// </summary>
        [SqlSugar.SugarColumn(IsPrimaryKey = true, ColumnDescription = "主键")]
        public virtual TPrimaryKey Id { get; set; }
    }

    public interface IEntity<TPrimaryKey>
    {
        /// <summary>
        /// 实体类唯一主键
        /// </summary>
        TPrimaryKey Id { get; set; }
    }

    /// <summary>
    /// 用于根据条件分页查询，DTO对象
    /// </summary>
    public class CustomerPagedDto : PagedAndSortedInputDto, IPagedAndSortedResultRequest
    {
        /// <summary>
        /// 默认构造函数
        /// </summary>
        public CustomerPagedDto() : base() { }

        /// <summary>
        /// 参数化构造函数
        /// </summary>
        /// <param name="skipCount">跳过的数量</param>
        /// <param name="resultCount">最大结果集数量</param>
        public CustomerPagedDto(int skipCount, int resultCount) : base(skipCount, resultCount)
        {
        }

        /// <summary>
        /// 使用分页信息进行初始化SkipCount 和 MaxResultCount
        /// </summary>
        /// <param name="pagerInfo">分页信息</param>
        public CustomerPagedDto(PagerInfo pagerInfo) : base(pagerInfo)
        {
        }

        #region Property Members

        /// <summary>
        /// 不包含的对象的ID,用于在查询的时候排除对应记录
        /// </summary>
        public virtual string ExcludeId { get; set; }

        /// <summary>
        /// 姓名
        /// </summary>
        public virtual string Name { get; set; }

        /// <summary>
        /// 年龄-开始
        /// </summary>
        public virtual int? AgeStart { get; set; }
        /// <summary>
        /// 年龄-结束
        /// </summary>
        public virtual int? AgeEnd { get; set; }

        /// <summary>
        /// 创建时间-开始
        /// </summary>
        public DateTime? CreateTimeStart { get; set; }
        /// <summary>
        /// 创建时间-结束
        /// </summary>
        public DateTime? CreateTimeEnd { get; set; }

        #endregion
    }

    /// <summary>
    /// 基于SqlSugar的数据库访问操作的基类对象
    /// </summary>
    /// <typeparam name="TEntity">定义映射的实体类</typeparam>
    /// <typeparam name="TKey">主键的类型，如int，string等</typeparam>
    /// <typeparam name="TGetListInput">或者分页信息的条件对象</typeparam>
    public abstract class MyCrudService<TEntity, TKey, TGetListInput> : 
        IMyCrudService<TEntity, TKey, TGetListInput>
        where TEntity : class, IEntity<TKey>, new()
        where TGetListInput : IPagedAndSortedResultRequest

    /// <summary>
    /// 客户信息服务接口
    /// </summary>
    public interface ICustomerService : IMyCrudService<CustomerInfo, string, CustomerPagedDto>, ITransientDependency
    {

    }

    /// <summary>
    /// 应用层服务接口实现
    /// </summary>
    public class CustomerService : MyCrudService<CustomerInfo, string, CustomerPagedDto>, ICustomerService

    /// <summary>
    /// 基于SqlSugar的数据库访问操作的基类对象
    /// </summary>
    /// <typeparam name="TEntity">定义映射的实体类</typeparam>
    /// <typeparam name="TKey">主键的类型，如int，string等</typeparam>
    /// <typeparam name="TGetListInput">或者分页信息的条件对象</typeparam>
    public abstract class MyCrudService<TEntity, TKey, TGetListInput> : 
        IMyCrudService<TEntity, TKey, TGetListInput>
        where TEntity : class, IEntity<TKey>, new()
        where TGetListInput : IPagedAndSortedResultRequest
    {
        /// <summary>
        /// 留给子类实现过滤条件的处理
        /// </summary>
        /// <returns></returns>
        protected virtual ISugarQueryable<TEntity> CreateFilteredQueryAsync(TGetListInput input)
        {
            return EntityDb.AsQueryable();
        }
        /// <summary>
        /// 默认排序，通过ID进行排序
        /// </summary>
        /// <param name="query"></param>
        /// <returns></returns>
        protected virtual ISugarQueryable<TEntity> ApplyDefaultSorting(ISugarQueryable<TEntity> query)
        {
            if (typeof(TEntity).IsAssignableTo<IEntity<TKey>>())
            {
                return query.OrderBy(e => e.Id);
            }
            else
            {
                return query.OrderBy("Id");
            }
        }        
    }

    /// <summary>
    /// 应用层服务接口实现
    /// </summary>
    public class CustomerService : MyCrudService<CustomerInfo, string, CustomerPagedDto>, ICustomerService
    {
        /// <summary>
        /// 自定义条件处理
        /// </summary>
        /// <param name="input">查询条件Dto</param>
        /// <returns></returns>
        protected override ISugarQueryable<CustomerInfo> CreateFilteredQueryAsync(CustomerPagedDto input)
        {
            var query = base.CreateFilteredQueryAsync(input);

            query = query
                .WhereIF(!input.ExcludeId.IsNullOrWhiteSpace(), t => t.Id != input.ExcludeId) //不包含排除ID
                .WhereIF(!input.Name.IsNullOrWhiteSpace(), t => t.Name.Contains(input.Name)) //如需要精确匹配则用Equals
                                                                                             //年龄区间查询
                .WhereIF(input.AgeStart.HasValue, s => s.Age >= input.AgeStart.Value)
                .WhereIF(input.AgeEnd.HasValue, s => s.Age <= input.AgeEnd.Value)

                //创建日期区间查询
                .WhereIF(input.CreateTimeStart.HasValue, s => s.CreateTime >= input.CreateTimeStart.Value)
                .WhereIF(input.CreateTimeEnd.HasValue, s => s.CreateTime <= input.CreateTimeEnd.Value)
                ;

            return query;
        }

        /// <summary>
        /// 自定义排序处理
        /// </summary>
        /// <param name="query">可查询LINQ</param>
        /// <returns></returns>
        protected override ISugarQueryable<CustomerInfo> ApplyDefaultSorting(ISugarQueryable<CustomerInfo> query)
        {
            return query.OrderBy(t => t.CreateTime, OrderByType.Desc);

            //先按第一个字段排序，然后再按第二字段排序
            //return base.ApplySorting(query, input).OrderBy(s=>s.Customer_ID).OrderBy(s => s.Seq);
        }
    }

        /// <summary>
        /// 根据条件获取列表
        /// </summary>
        /// <param name="input">分页查询条件</param>
        /// <returns></returns>
        public virtual async Task<PagedResultDto<TEntity>> GetListAsync(TGetListInput input)
        {
            var query = CreateFilteredQueryAsync(input);
            var totalCount = await query.CountAsync();

            query = ApplySorting(query, input);
            query = ApplyPaging(query, input);

            var list = await query.ToListAsync();

            return new PagedResultDto<TEntity>(
               totalCount,
               list
           );
        }

        /// <summary>
        /// 记录排序处理
        /// </summary>
        /// <returns></returns>
        protected virtual ISugarQueryable<TEntity> ApplySorting(ISugarQueryable<TEntity> query, TGetListInput input)
        {
            //Try to sort query if available
            if (input is ISortedResultRequest sortInput)
            {
                if (!sortInput.Sorting.IsNullOrWhiteSpace())
                {
                    return query.OrderBy(sortInput.Sorting);
                }
            }

            //IQueryable.Task requires sorting, so we should sort if Take will be used.
            if (input is ILimitedResultRequest)
            {
                return ApplyDefaultSorting(query);
            }

            //No sorting
            return query;
        }

        /// <summary>
        /// 根据ID获取单一对象
        /// </summary>
        /// <param name="id">主键ID</param>
        /// <returns></returns>
        public virtual async Task<TEntity> GetAsync(TKey id)
        {
            return await EntityDb.GetByIdAsync(id);
        }

        /// <summary>
        /// 删除指定ID的对象
        /// </summary>
        /// <param name="id">记录ID</param>
        /// <returns></returns>
        public virtual async Task<bool> DeleteAsync(TKey id)
        {
            return await EntityDb.DeleteByIdAsync(id);
        }

/// <summary>
        /// 根据指定条件，删除集合
        /// </summary>
        /// <param name="input">表达式条件</param>
        /// <returns></returns>
        public virtual async Task<bool> DeleteAsync(Expression<Func<TEntity, bool>> input)
        {
            var result = await EntityDb.DeleteAsync(input);
            return result;
        }

        /// <summary>
        /// 判断是否存在指定条件的记录
        /// </summary>
        /// <param name="id">ID 主键</param>
        /// <returns></returns>
        public virtual async Task<bool> IsExistAsync(TKey id)
        {
            var info = await EntityDb.GetByIdAsync(id);
            var result = (info != null);
            return result;
        }

        /// <summary>
        /// 判断是否存在指定条件的记录
        /// </summary>
        /// <param name="input">表达式条件</param>
        /// <returns></returns>
        public virtual async Task<bool> IsExistAsync(Expression<Func<TEntity, bool>> input)
        {
            var result = await EntityDb.IsAnyAsync(input);
            return result;
        }

        /// <summary>
        /// 当前用户身份ID
        /// </summary>
        protected virtual string? CurrentUserId => HttpContext.User.FindFirst(JwtClaimTypes.Id)?.Value;

    /// <summary>
    /// 本控制器基类专门为访问数据业务对象而设的基类
    /// </summary>
    /// <typeparam name="TEntity">定义映射的实体类</typeparam>
    /// <typeparam name="TKey">主键的类型，如int，string等</typeparam>
    /// <typeparam name="TGetListInput">或者分页信息的条件对象</typeparam>
    [Route("[controller]")]
    [Authorize] //需要授权登录访问
    public class BusinessController<TEntity, TKey, TGetListInput> : BaseApiController
        where TEntity : class, IEntity<TKey>, new()
        where TGetListInput : IPagedAndSortedResultRequest
    {
        /// <summary>
        /// 通用基础操作接口
        /// </summary>
        protected IMyCrudService<TEntity, TKey, TGetListInput> _service { get; set; }

        /// <summary>
        /// 构造函数，初始化基础接口
        /// </summary>
        /// <param name="service">通用基础操作接口</param>
        public BusinessController(IMyCrudService<TEntity, TKey, TGetListInput> service)
        {
            this._service = service;
        }

....

    /// <summary>
    /// 客户信息的控制器对象
    /// </summary>
    public class CustomerController : BusinessController<CustomerInfo, string, CustomerPagedDto>
    {
        private ICustomerService _customerService;

        /// <summary>
        /// 构造函数，并注入基础接口对象
        /// </summary>
        /// <param name="customerService"></param>
        public CustomerController(ICustomerService customerService) :base(customerService)
        {
            this._customerService = customerService;
        }
    }

        /// <summary>
        /// 根据字典类型ID获取所有该类型的字典列表集合(Key为名称，Value为值）
        /// </summary>
        /// <param name="dictTypeId">字典类型ID</param>
        /// <returns></returns>
        [HttpGet]
        [Route("by-typeid/{dictTypeId}")]
        public async Task<Dictionary<string, string>> GetDictByTypeID(string dictTypeId)
        {
            return await _dictDataService.GetDictByTypeID(dictTypeId);
        }

        /// <summary>
        /// 根据字典类型名称获取所有该类型的字典列表集合(Key为名称，Value为值）
        /// </summary>
        /// <param name="dictTypeName">字典类型名称</param>
        /// <returns></returns>
        [HttpGet]
        [Route("by-typename/{dictTypeName}")]
        public async Task<Dictionary<string, string>> GetDictByDictType(string dictTypeName)
        {
            return await _dictDataService.GetDictByDictType(dictTypeName);
        }