using Apache.NMS;
using Apache.NMS.ActiveMQ;
using Rhea.Common;
using SqlSugar;
using System;
using System.Collections;
using System.Collections.Generic;
using System.Data.SqlTypes;
using System.Linq;
using System.Linq.Expressions;
using System.Text;
using System.Threading.Tasks;
using Tiger.IBusiness;
using Tiger.Model;

namespace Tiger.Business
{
    /// <summary>
    /// 数据库操作基础类
    /// </summary>
    public class DbBase : Rhea.Common.DbBase, IDbBase//Rhea.Common.DbBase
    {
        /// <summary>
        /// 数据库操作基础类
        /// </summary>
        /// <param name="db"></param>
        public DbBase(DbClient db) : base(db)
        {
        }

        #region 添加操作

        /// <summary>
        /// 把单个实体往数据库添加一行数据
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="entity">实体</param>
        /// <param name="isAsync">是否以异步方式执行</param>
        /// <returns></returns>
        public new async Task<ApiAction<T>> AddAsync<T>(T entity, bool isAsync = true) where T : class, new()
        {
            var action = new ApiAction<T>();
            try
            {
                var dbres = isAsync ? await Db.Insertable<T>(entity).ExecuteCommandAsync() : Db.Insertable<T>(entity).ExecuteCommand();
                action.Message = $"Insert {typeof(T).Name} entity into database success";
            }
            catch (Exception ex)
            {
                action.CatchExceptionWithLog(ex, $"Insert {typeof(T).Name} entity into database exception");
            }
            return action;
        }

        /// <summary>
        /// 把实体List往数据库批量添加数据
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="list">实体List</param>
        /// <param name="isAsync">是否以异步方式执行</param>
        /// <returns></returns>
        public new async Task<ApiAction<T>> AddListAsync<T>(List<T> list, bool isAsync = true) where T : class, new()
        {
            var action = new ApiAction<T>();
            try
            {
                var dbres = isAsync ? await Db.Insertable<T>(list).ExecuteCommandAsync() : Db.Insertable<T>(list).ExecuteCommand();
                action.Message = $"Insert {typeof(T).Name} entity list into database success";
            }
            catch (Exception ex)
            {
                action.CatchExceptionWithLog(ex, $"Insert {typeof(T).Name} entity list into database exception.");
            }
            return action;
        }

        #endregion 添加操作

        #region 保存操作

        /// <summary>
        /// 把单个实体往数据库保存一行数据，如果存在就更新，不存在就插入
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="entity">实体</param>
        /// <param name="isAsync">是否以异步方式执行</param>
        /// <returns></returns>
        public new async Task<ApiAction<T>> SaveAsync<T>(T entity, bool isAsync = true) where T : class, new()
        {
            var action = new ApiAction<T>();
            try
            {
                var dbres = isAsync ? await Db.Saveable<T>(entity).ExecuteCommandAsync() : Db.Saveable<T>(entity).ExecuteCommand();
                action.Message = $"Save {typeof(T).Name} entity into database success";
            }
            catch (Exception ex)
            {
                action.CatchExceptionWithLog(ex, $"Save {typeof(T).Name} entity into database exception");
            }
            return action;
        }

        /// <summary>
        /// 把实体List往数据库批量保存数据，如果存在就更新，不存在就插入
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="list">实体List</param>
        /// <param name="isAsync">是否以异步方式执行</param>
        /// <returns></returns>
        public new async Task<ApiAction<T>> SaveListAsync<T>(List<T> list, bool isAsync = true) where T : class, new()
        {
            var action = new ApiAction<T>();
            try
            {
                var dbres = isAsync ? await Db.Saveable<T>(list).ExecuteCommandAsync() : Db.Saveable<T>(list).ExecuteCommand();
                action.Message = $"Save {typeof(T).Name} entity list into database success";
            }
            catch (Exception ex)
            {
                action.CatchExceptionWithLog(ex, $"Save {typeof(T).Name} entity list into database exception.");
            }
            return action;
        }

        /// <summary>
        /// 把单个实体往数据库保存一行数据，如果存在就更新，不存在就插入
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="entity">实体</param>
        /// <param name="isAsync">是否以异步方式执行</param>
        /// <returns></returns>
        public async Task<ApiAction<T>> StorageableAsync<T>(T entity, bool isAsync = true) where T : class, new()
        {
            var action = new ApiAction<T>();
            try
            {
                var dbres = isAsync ? await Db.Storageable<T>(entity).ExecuteCommandAsync() : Db.Storageable<T>(entity).ExecuteCommand();
                action.Message = $"Storageable {typeof(T).Name} entity into database success";
            }
            catch (Exception ex)
            {
                action.CatchExceptionWithLog(ex, $"Storageable {typeof(T).Name} entity into database exception");
            }
            return action;
        }

        /// <summary>
        /// 把实体List往数据库批量保存数据，如果存在就更新，不存在就插入
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="list">实体List</param>
        /// <param name="isAsync">是否以异步方式执行</param>
        /// <returns></returns>
        public async Task<ApiAction<T>> StorageableListAsync<T>(List<T> list, bool isAsync = true) where T : class, new()
        {
            var action = new ApiAction<T>();
            try
            {
                var dbres = isAsync ? await Db.Storageable<T>(list).ExecuteCommandAsync() : Db.Storageable<T>(list).ExecuteCommand();
                action.Message = $"Storageable {typeof(T).Name} entity list into database success";
            }
            catch (Exception ex)
            {
                action.CatchExceptionWithLog(ex, $"Storageable {typeof(T).Name} entity list into database exception.");
            }
            return action;
        }

        #endregion 保存操作

        #region 修改操作

        /// <summary>
        /// 从数据库中修改单个实体
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="entity">实体</param>
        /// <param name="isAsync">是否以异步方式执行</param>
        /// <returns></returns>
        public new async Task<ApiAction> UpdateAsync<T>(T entity, bool isAsync = true) where T : class, new()
        {
            var action = new ApiAction();
            try
            {
                var dbres = isAsync ? await Db.Updateable<T>(entity).ExecuteCommandAsync() : Db.Updateable<T>(entity).ExecuteCommand();
                action.Message = $"Update {typeof(T).Name} entity from database success";
            }
            catch (Exception ex)
            {
                action.CatchExceptionWithLog(ex, $"Update {typeof(T).Name} entity from database exception");
            }
            return action;
        }

        /// <summary>
        /// 根据实体List在数据库批量修改数据
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="list">实体List</param>
        /// <param name="isAsync">是否以异步方式执行</param>
        /// <returns></returns>
        public new async Task<ApiAction> UpdateAsync<T>(List<T> list, bool isAsync = true) where T : class, new()
        {
            var action = new ApiAction();
            try
            {
                var dbres = isAsync ? await Db.Updateable<T>(list).ExecuteCommandAsync() : Db.Updateable<T>(list).ExecuteCommand();
                action.Message = $"Update {typeof(T).Name} entity list from database success";
            }
            catch (Exception ex)
            {
                action.CatchExceptionWithLog(ex, $"Update {typeof(T).Name} entity list from database exception.");
            }
            return action;
        }

        /// <summary>
        /// 按UpdaterAble&lt;T>对象从数据库中修改多个实体的某些字段
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="updater">实体List</param>
        /// <param name="isAsync">是否以异步方式执行</param>
        /// <returns></returns>
        public new async Task<ApiAction> UpdateAsync<T>(UpdateAble<T> updater, bool isAsync = true) where T : class, new()
        {
            var action = new ApiAction();
            try
            {
                var dbres = isAsync ? await Db.Updateable<T>(updater.Items).UpdateColumns(updater.UpdateColumnList).ExecuteCommandAsync() : Db.Updateable<T>(updater.Items).UpdateColumns(updater.UpdateColumnList).ExecuteCommand();
                action.Message = $"Update some columns of {typeof(T).Name} entity list from database success";
            }
            catch (Exception ex)
            {
                action.CatchExceptionWithLog(ex, $"Update some columns of {typeof(T).Name} entity list from database exception");
            }
            return action;
        }

        #endregion 修改操作

        #region 删除操作

        /// <summary>
        /// 从数据库中删除多个实体
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="newEntity">固定用法：通过ApiAction.NewDataEntity()创建ApiAction中指定类型对象</param>
        /// <param name="primaryKeys">实体的主键ID数组</param>
        /// <param name="isAsync">是否以异步方式执行</param>
        /// <returns></returns>
        public new async Task<ApiAction> DeleteAsync<T>(T newEntity, string[] primaryKeys, bool isAsync = true) where T : class, new()
        {
            var action = new ApiAction(false);
            try
            {
                var dbres = isAsync ? await Db.Deleteable<T>().In(primaryKeys).ExecuteCommandAsync() : Db.Deleteable<T>().In(primaryKeys).ExecuteCommand();
                action.Message = $"Delete {typeof(T).Name} entity by primary keys from database success";
                action.IsSuccessed = true;
            }
            catch (Exception ex)
            {
                action.CatchExceptionWithLog(ex, $"Delete {typeof(T).Name} entity by primary keys from database exception");
            }
            return action;
        }

        /// <summary>
        /// 从数据库中删除单个实体
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="entity">实体</param>
        /// <param name="isAsync">是否以异步方式执行</param>
        /// <returns></returns>
        public new async Task<ApiAction> DeleteAsync<T>(T entity, bool isAsync = true) where T : class, new()
        {
            var action = new ApiAction(false);
            try
            {
                var dbres = isAsync ? await Db.Deleteable<T>().Where(entity).ExecuteCommandAsync() : Db.Deleteable<T>().Where(entity).ExecuteCommand();
                action.Message = $"Delete {typeof(T).Name} entity from database success";
                action.IsSuccessed = true;
            }
            catch (Exception ex)
            {
                action.CatchExceptionWithLog(ex, $"Delete {typeof(T).Name} entity from database exception");
            }
            return action;
        }

        /// <summary>
        /// 从数据库中删除实体List
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="list">实体List</param>
        /// <param name="isAsync">是否以异步方式执行</param>
        /// <returns></returns>
        public new async Task<ApiAction> DeleteAsync<T>(List<T> list, bool isAsync = true) where T : class, new()
        {
            var action = new ApiAction();
            try
            {
                var dbres = isAsync ? await Db.Deleteable<T>().Where(list).ExecuteCommandAsync() : Db.Deleteable<T>().Where(list).ExecuteCommand();
                action.Message = $"Delete {typeof(T).Name} entity list from database success";
            }
            catch (Exception ex)
            {
                action.CatchExceptionWithLog(ex, $"Delete {typeof(T).Name} entity list from database exception");
            }
            return action;
        }

        /// <summary>
        /// 根据Where语句删除满足条件的数据
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="newEntity">固定用法：通过ApiAction.NewDataEntity()创建ApiAction中指定类型对象</param>
        /// <param name="where">Where语句</param>
        /// <param name="isAsync">是否以异步方式执行</param>
        /// <returns></returns>
        public new async Task<ApiAction> DeleteAsync<T>(T newEntity, string where, bool isAsync = true) where T : class, new()
        {
            var action = new ApiAction();
            try
            {
                var dbres = isAsync ? await Db.Deleteable<T>().Where(where).ExecuteCommandAsync() : Db.Deleteable<T>().Where(where).ExecuteCommand();
                action.Message = $"Delete {typeof(T).Name} entity by where from database success";
            }
            catch (Exception ex)
            {
                action.CatchExceptionWithLog(ex, $"Delete {typeof(T).Name} entity by where from database exception");
            }
            return action;
        }

        #endregion 删除操作

        #region 查询操作

        /// <summary>
        /// 从数据库中查询实体
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="newEntity">固定用法：通过ApiAction.NewDataEntity()创建ApiAction中指定类型对象</param>
        /// <param name="isAsync">是否以异步方式执行</param>
        /// <param name="needInclude">是否需要导航查询</param>
        /// <returns></returns>
        public async Task<ApiAction<List<T>>> QueryAsync<T>(T newEntity, bool isAsync = true, bool needInclude = false)
        {
            var action = new ApiAction<List<T>>();
            try
            {
                var queryable = Db.Queryable<T>();
                if (needInclude)
                {
                    queryable = queryable.IncludesAllFirstLayer();
                }
                action.Data = isAsync ? await queryable.ToListAsync() : queryable.ToList();
                action.Message = $"Query {typeof(T).Name} entity list from database success";
            }
            catch (Exception ex)
            {
                action.CatchExceptionWithLog(ex, $"Query {typeof(T).Name} entity list from database exception");
            }
            return action;
        }

        /// <summary>
        /// 根据Where语句从数据库中查询实体
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="newEntity">固定用法：通过ApiAction.NewDataEntity()创建ApiAction中指定类型对象</param>
        /// <param name="where">Where语句</param>
        /// <param name="isAsync">是否以异步方式执行</param>
        /// <param name="needInclude">是否需要导航查询</param>
        /// <returns></returns>
        public async Task<ApiAction<List<T>>> QueryAsync<T>(T newEntity, string where, bool isAsync = true, bool needInclude = false) where T : class, new()
        {
            var action = new ApiAction<List<T>>();
            try
            {
                var queryable = Db.Queryable<T>().Where(where);
                if (needInclude)
                {
                    queryable = queryable.IncludesAllFirstLayer();
                }
                action.Data = isAsync ? await queryable.ToListAsync() : queryable.ToList();
                action.Message = $"Query {typeof(T).Name} entity list by where from database success";
            }
            catch (Exception ex)
            {
                action.CatchExceptionWithLog(ex, $"Query {typeof(T).Name} entity list by where from database exception");
            }
            return action;
        }

        /// <summary>
        /// 按PageAble&lt;T>对象返回实体当前Page的数据
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="page">分页的参数实体</param>
        /// <param name="isAsync">是否以异步方式执行</param>
        /// <param name="needInclude">是否需要导航查询</param>
        /// <returns></returns>
        public async Task<ApiAction<PageAble<T>>> QueryAsync<T>(PageAble<T> page, bool isAsync = true, bool needInclude = false)
        {
            var action = new ApiAction<PageAble<T>>();
            try
            {
                var queryable = Db.Queryable<T>();
                if (needInclude)
                {
                    queryable = queryable.IncludesAllFirstLayer();
                }
                action.Data = isAsync ? await queryable.ToPageAsync(page.pageIndex, page.pageSize) : queryable.ToPage(page.pageIndex, page.pageSize);
                action.Message = $"Query {typeof(T).Name} entity page from database success";
            }
            catch (Exception ex)
            {
                action.CatchExceptionWithLog(ex, $"Query {typeof(T).Name} entity page from database exception");
            }
            return action;
        }

        /// <summary>
        /// 分页直接返回PageAble&lt;T>
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="page"></param>
        /// <param name="isAsync"></param>
        /// <param name="needInclude">是否需要导航查询</param>
        /// <returns></returns>
        public async Task<ApiAction<PageAble<T>>> QueryPageAsync<T>(PageAble<T> page, bool isAsync = true, bool needInclude = false)
        {
            var action = new ApiAction<PageAble<T>>();
            try
            {
                string where = page.sqlcmd;
                var queryable = Db.Queryable<T>().Where(where);
                if (needInclude)
                {
                    queryable = queryable.IncludesAllFirstLayer();
                }
                action.Data = isAsync ? await queryable.ToPageAsync(page.pageIndex, page.pageSize) : queryable.ToPage(page.pageIndex, page.pageSize);
                action.Data.draw = page.draw;
                action.Message = $"Query {typeof(T).Name} entity page from database success";
            }
            catch (Exception ex)
            {
                action.CatchExceptionWithLog(ex, $"Query {typeof(T).Name} entity page from database exception");
            }
            return action;
        }

        /// <summary>
        /// 按QueryAble&lt;T>对象从数据库查询满足条件的数据
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="query">QueryAble的参数实体</param>
        /// <param name="isAsync">是否以异步方式执行</param>
        /// <param name="needInclude">是否需要导航查询</param>
        /// <returns></returns>
        public async Task<ApiAction<QueryAble<T>>> QueryAsync<T>(QueryAble<T> query, bool isAsync = true, bool needInclude = false)
        {
            var action = new ApiAction<QueryAble<T>>();
            try
            {
                var queryable = Db.Queryable<T>().WhereIF(!query.where.IsNullOrEmpty(), query.where).OrderByIF(!query.order.IsNullOrEmpty(), query.order);
                if (needInclude)
                {
                    queryable = queryable.IncludesAllFirstLayer();
                }
                if (query.page == null)
                {
                    query.Items = isAsync ? await queryable.ToListAsync() : queryable.ToList();
                }
                else
                {
                    query.page = isAsync ? (await queryable.ToPageAsync(query.page.pageIndex, query.page.pageSize)) : queryable.ToPage(query.page.pageIndex, query.page.pageSize);
                    query.Items = query.page.data;
                }
                action.Data = query;
                action.Message = $"Query {typeof(T).Name} entity by QueryAble from database success";
            }
            catch (Exception ex)
            {
                action.CatchExceptionWithLog(ex, $"Query {typeof(T).Name} entity by QueryAble from database exception");
            }
            return action;
        }

        /// <summary>
        /// 根据SQL语句从数据库中查询实体
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="newEntity">固定用法：通过ApiAction.NewDataEntity()创建ApiAction中指定类型对象</param>
        /// <param name="sqlcmd">SQL语句</param>
        /// <param name="isAsync">是否以异步方式执行</param>
        /// <param name="needInclude">是否需要导航查询</param>
        /// <returns></returns>
        public async Task<ApiAction<List<T>>> QuerySqlAsync<T>(T newEntity, string sqlcmd, bool isAsync = true, bool needInclude = false) where T : class, new()
        {
            var action = new ApiAction<List<T>>();
            try
            {
                var queryable = Db.SqlQueryable<T>(sqlcmd);
                if (needInclude)
                {
                    queryable = queryable.IncludesAllFirstLayer();
                }
                action.Data = isAsync ? await queryable.ToListAsync() : queryable.ToList();
                action.Message = $"Query {typeof(T).Name} entity list from database by sql command success";
            }
            catch (Exception ex)
            {
                action.CatchExceptionWithLog(ex, $"Query {typeof(T).Name} entity list from database by sql command exception");
            }
            return action;
        }

        /// <summary>
        /// 根据SQL语句从数据库中查询并按PageAble&lt;T>对象返回当前Page的数据
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="page">分页的参数实体</param>
        /// <param name="isAsync">是否以异步方式执行</param>
        /// <param name="needInclude">是否需要导航查询</param>
        /// <returns></returns>
        public async Task<ApiAction<PageAble<T>>> QuerySqlAsync<T>(PageAble<T> page, bool isAsync = true, bool needInclude = false) where T : class, new()
        {
            var action = new ApiAction<PageAble<T>>();
            try
            {
                var queryable = Db.SqlQueryable<T>(page.sqlcmd);
                if (needInclude)
                {
                    queryable = queryable.IncludesAllFirstLayer();
                }
                action.Data = isAsync ? await queryable.ToPageAsync(page.pageIndex, page.pageSize) : queryable.ToPage(page.pageIndex, page.pageSize);
                action.Data.draw = page.draw;
                action.Message = $"Query {typeof(T).Name} entity page from database by sql command success";
            }
            catch (Exception ex)
            {
                action.CatchExceptionWithLog(ex, $"Query {typeof(T).Name} entity page from database by sql command exception");
            }
            return action;
        }

        /// <summary>
        /// 从数据库中查询实体
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="newEntity">固定用法：通过ApiAction.NewDataEntity()创建ApiAction中指定类型对象</param>
        /// <param name="options">授权查询配置项</param>
        /// <param name="isAsync">是否以异步方式执行</param>
        /// <param name="needInclude">是否需要导航查询</param>
        /// <returns></returns>
        public async Task<ApiAction<List<T>>> QueryByAuthAsync<T>(T newEntity, AuthOption options, bool isAsync = true, bool needInclude = false) where T : iTableHasAuth, new()
        {
            var action = new ApiAction<List<T>>();
            try
            {
                var queryable = Db.Queryable<T>().ByAuth(options);
                if (needInclude)
                {
                    queryable = queryable.IncludesAllFirstLayer();
                }
                action.Data = isAsync ? await queryable.ToListAsync() : queryable.ToList();
                action.Message = $"Query {typeof(T).Name} entity list from database success";
            }
            catch (Exception ex)
            {
                action.CatchExceptionWithLog(ex, $"Query {typeof(T).Name} entity list from database exception");
            }
            return action;
        }

        /// <summary>
        /// 根据Where语句从数据库中查询实体
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="newEntity">固定用法：通过ApiAction.NewDataEntity()创建ApiAction中指定类型对象</param>
        /// <param name="where">Where语句</param>
        /// <param name="options">授权查询配置项</param>
        /// <param name="isAsync">是否以异步方式执行</param>
        /// <param name="needInclude">是否需要导航查询</param>
        /// <returns></returns>
        public async Task<ApiAction<List<T>>> QueryByAuthAsync<T>(T newEntity, string where, AuthOption options, bool isAsync = true, bool needInclude = false) where T : iTableHasAuth, new()
        {
            var action = new ApiAction<List<T>>();
            try
            {
                var queryable = Db.Queryable<T>().ByAuth(options).Where(where);
                if (needInclude)
                {
                    queryable = queryable.IncludesAllFirstLayer();
                }
                action.Data = isAsync ? await queryable.ToListAsync() : queryable.ToList();
                action.Message = $"Query {typeof(T).Name} entity list by where from database success";
            }
            catch (Exception ex)
            {
                action.CatchExceptionWithLog(ex, $"Query {typeof(T).Name} entity list by where from database exception");
            }
            return action;
        }

        /// <summary>
        /// 按PageAble&lt;T>对象返回实体当前Page的数据
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="page">分页的参数实体</param>
        /// <param name="options">授权查询配置项</param>
        /// <param name="isAsync">是否以异步方式执行</param>
        /// <param name="needInclude">是否需要导航查询</param>
        /// <returns></returns>
        public async Task<ApiAction<PageAble<T>>> QueryByAuthAsync<T>(PageAble<T> page, AuthOption options, bool isAsync = true, bool needInclude = false) where T : iTableHasAuth, new()
        {
            var action = new ApiAction<PageAble<T>>();
            try
            {
                var queryable = Db.Queryable<T>().ByAuth(options);
                if (needInclude)
                {
                    queryable = queryable.IncludesAllFirstLayer();
                }
                action.Data = isAsync ? await queryable.ToPageAsync(page.pageIndex, page.pageSize) : queryable.ToPage(page.pageIndex, page.pageSize);
                action.Message = $"Query {typeof(T).Name} entity page from database success";
            }
            catch (Exception ex)
            {
                action.CatchExceptionWithLog(ex, $"Query {typeof(T).Name} entity page from database exception");
            }
            return action;
        }

        /// <summary>
        /// 分页直接返回PageAble&lt;T>
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="page"></param>
        /// <param name="options">授权查询配置项</param>
        /// <param name="isAsync">是否以异步方式执行</param>
        /// <param name="needInclude">是否需要导航查询</param>
        /// <returns></returns>
        public async Task<ApiAction<PageAble<T>>> QueryByAuthPageAsync<T>(PageAble<T> page, AuthOption options, bool isAsync = true, bool needInclude = false) where T : iTableHasAuth, new()
        {
            var action = new ApiAction<PageAble<T>>();
            try
            {
                string where = page.sqlcmd;
                var queryable = Db.Queryable<T>().ByAuth(options).Where(where);
                if (needInclude)
                {
                    queryable = queryable.IncludesAllFirstLayer();
                }
                action.Data = isAsync ? await queryable.ToPageAsync(page.pageIndex, page.pageSize) : queryable.ToPage(page.pageIndex, page.pageSize);
                action.Data.draw = page.draw;
                action.Message = $"Query {typeof(T).Name} entity page from database success";
            }
            catch (Exception ex)
            {
                action.CatchExceptionWithLog(ex, $"Query {typeof(T).Name} entity page from database exception");
            }
            return action;
        }

        /// <summary>
        /// 按QueryAble&lt;T>对象从数据库查询满足条件的数据
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="query">QueryAble的参数实体</param>
        /// <param name="options">授权查询配置项</param>
        /// <param name="isAsync">是否以异步方式执行</param>
        /// <param name="needInclude">是否需要导航查询</param>
        /// <returns></returns>
        public async Task<ApiAction<QueryAble<T>>> QueryByAuthAsync<T>(QueryAble<T> query, AuthOption options, bool isAsync = true, bool needInclude = false) where T : iTableHasAuth, new()
        {
            var action = new ApiAction<QueryAble<T>>();
            try
            {
                var queryable = Db.Queryable<T>().ByAuth(options).WhereIF(!query.where.IsNullOrEmpty(), query.where).OrderByIF(!query.order.IsNullOrEmpty(), query.order);
                if (needInclude)
                {
                    queryable = queryable.IncludesAllFirstLayer();
                }
                if (query.page == null)
                {
                    query.Items = isAsync ? await queryable.ToListAsync() : queryable.ToList();
                }
                else
                {
                    query.page = isAsync ? (await queryable.ToPageAsync(query.page.pageIndex, query.page.pageSize)) : queryable.ToPage(query.page.pageIndex, query.page.pageSize);
                    query.Items = query.page.data;
                }
                action.Data = query;
                action.Message = $"Query {typeof(T).Name} entity by QueryAble from database success";
            }
            catch (Exception ex)
            {
                action.CatchExceptionWithLog(ex, $"Query {typeof(T).Name} entity by QueryAble from database exception");
            }
            return action;
        }

        #endregion 查询操作

        #region 查询 Count

        /// <summary>
        /// 根据Where语句查询满足条件的实体的Count数量
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="newEntity">固定用法：通过ApiAction.NewDataEntity()创建ApiAction中指定类型对象</param>
        /// <param name="where">Where语句</param>
        /// <param name="isAsync">是否以异步方式执行</param>
        /// <returns></returns>
        public new async Task<ApiAction> CountAsync<T>(T newEntity, string where, bool isAsync = true)
        {
            var action = new ApiAction();
            try
            {
                action.Data = isAsync ? await Db.Queryable<T>().Where(where).CountAsync() : Db.Queryable<T>().Where(where).Count();
                action.Message = $"Select Count[{typeof(T).Name} entity) success";
            }
            catch (Exception ex)
            {
                action.CatchExceptionWithLog(ex, $"Select Count[{typeof(T).Name} entity) exception");
            }
            return action;
        }

        /// <summary>
        /// 根据Where语句查询满足条件的实体的Count数量
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="newEntity">固定用法：通过ApiAction.NewDataEntity()创建ApiAction中指定类型对象</param>
        /// <param name="where">Where语句</param>
        /// <param name="options">授权查询配置项</param>
        /// <param name="isAsync">是否以异步方式执行</param>
        /// <returns></returns>
        public async Task<ApiAction> CountByAuthAsync<T>(T newEntity, string where, AuthOption options, bool isAsync = true) where T : iTableHasAuth, new()
        {
            var action = new ApiAction();
            try
            {
                action.Data = isAsync ? await Db.Queryable<T>().ByAuth(options).Where(where).CountAsync() : Db.Queryable<T>().ByAuth(options).Where(where).Count();
                action.Message = $"Select Count[{typeof(T).Name} entity) success";
            }
            catch (Exception ex)
            {
                action.CatchExceptionWithLog(ex, $"Select Count[{typeof(T).Name} entity) exception");
            }
            return action;
        }

        #endregion 查询 Count

        #region 查询 Exist

        /// <summary>
        /// 根据Where语句查询满足条件的Data实体是否存在
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="newEntity">固定用法：通过ApiAction.NewDataEntity()创建ApiAction中指定类型对象</param>
        /// <param name="where">Where语句</param>
        /// <param name="isAsync">是否以异步方式执行</param>
        /// <returns></returns>
        public new async Task<ApiAction> IsExistAsync<T>(T newEntity, string where, bool isAsync = true)
        {
            var action = new ApiAction();
            try
            {
                action.Data = isAsync ? await Db.Queryable<T>().Where(where).AnyAsync() : Db.Queryable<T>().Where(where).Any();
                action.Message = $"Check {typeof(T).Name} entity is exist success";
            }
            catch (Exception ex)
            {
                action.CatchExceptionWithLog(ex, $"Check {typeof(T).Name} entity is exist exception");
            }
            return action;
        }

        /// <summary>
        /// 根据Where语句查询满足条件的Data实体是否存在
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="newEntity">固定用法：通过ApiAction.NewDataEntity()创建ApiAction中指定类型对象</param>
        /// <param name="where">Where语句</param>
        /// <param name="options">授权查询配置项</param>
        /// <param name="isAsync">是否以异步方式执行</param>
        /// <returns></returns>
        public async Task<ApiAction> IsExistByAuthAsync<T>(T newEntity, string where, AuthOption options, bool isAsync = true) where T : iTableHasAuth, new()
        {
            var action = new ApiAction();
            try
            {
                action.Data = isAsync ? await Db.Queryable<T>().ByAuth(options).Where(where).AnyAsync() : Db.Queryable<T>().ByAuth(options).Where(where).Any();
                action.Message = $"Check {typeof(T).Name} entity is exist success";
            }
            catch (Exception ex)
            {
                action.CatchExceptionWithLog(ex, $"Check {typeof(T).Name} entity is exist exception");
            }
            return action;
        }

        #endregion 查询 Exist

        #region 执行SQL

        /// <summary>
        /// 执行SQL语句返回影响行数
        /// </summary>
        /// <param name="sqlcmd">要执行的SQL语句</param>
        /// <param name="parameters">SQL语句的参数</param>
        /// <param name="isAsync">是否以异步方式执行</param>
        /// <returns></returns>
        public new async Task<ApiAction> ExecuteSqlCommandAsync(string sqlcmd, List<SugarParameter> parameters, bool isAsync = true)
        {
            var action = new ApiAction();
            try
            {
                action.Data = isAsync ? await Db.Ado.ExecuteCommandAsync(sqlcmd, parameters) : Db.Ado.ExecuteCommand(sqlcmd, parameters);
                action.Message = $"Execute command success(SQL:{sqlcmd}, Params:{Db.Utilities.SerializeObject(parameters.Select(q => new { ParameterName = q.ParameterName, Value = q.Value }))})";
            }
            catch (Exception ex)
            {
                action.CatchExceptionWithLog(ex, $"Execute command success(SQL:{sqlcmd}, Params:{Db.Utilities.SerializeObject(parameters)})");
            }
            return action;
        }

        /// <summary>
        /// 执行SQL语句返回影响行数
        /// </summary>
        /// <param name="sqlcmd">要执行的SQL语句</param>
        /// <param name="parameters">SQL语句的参数</param>
        /// <param name="isAsync">是否以异步方式执行</param>
        /// <returns></returns>
        public new async Task<ApiAction> ExecuteSqlCommandAsync(string sqlcmd, bool isAsync = true, params SugarParameter[] parameters)
        {
            var action = new ApiAction();
            try
            {
                action.Data = isAsync ? await Db.Ado.ExecuteCommandAsync(sqlcmd, parameters) : Db.Ado.ExecuteCommand(sqlcmd, parameters);
                action.Message = $"Execute command success(SQL:{sqlcmd}, Params:{Db.Utilities.SerializeObject(parameters.ToDictionary(it => it.ParameterName, it => it.Value))})";
            }
            catch (Exception ex)
            {
                action.CatchExceptionWithLog(ex, $"Execute command success(SQL:{sqlcmd}, Params:{Db.Utilities.SerializeObject(parameters.ToDictionary(it => it.ParameterName, it => it.Value))})");
            }
            return action;
        }

        /// <summary>
        /// 异步方式执行SQL语句返回影响行数
        /// </summary>
        /// <param name="sqlcmd">要执行的SQL语句</param>
        /// <param name="parameters">SQL语句的参数</param>
        /// <returns></returns>
        public new async Task<ApiAction> ExecuteSqlCommandAsync(string sqlcmd, params SugarParameter[] parameters)
        {
            var action = new ApiAction();
            try
            {
                action.Data = await Db.Ado.ExecuteCommandAsync(sqlcmd, parameters);
                action.Message = $"Execute command success(SQL:{sqlcmd}, Params:{Db.Utilities.SerializeObject(parameters.ToDictionary(it => it.ParameterName, it => it.Value))})";
            }
            catch (Exception ex)
            {
                action.CatchExceptionWithLog(ex, $"Execute command success(SQL:{sqlcmd}, Params:{Db.Utilities.SerializeObject(parameters.ToDictionary(it => it.ParameterName, it => it.Value))})");
            }
            return action;
        }

        #endregion 执行SQL

        #region 先删除后新增
        /// <summary>
        /// 删除完更新
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="query"></param>
        /// <param name="list"></param>
        /// <returns></returns>
        public new async Task<ApiAction> AddAfterDeleteAsync<T>(QueryAble<T> query) where T : class, new()
        {
            var action = new ApiAction();
            try
            {
                //保存到数据库
                var db = Business.Biz.Db;
                var dbTran = db.UseTran(() =>
                {
                    var dbres = !query.where.IsNullOrEmpty() ? db.Deleteable<T>().Where(query.where).ExecuteCommand() : db.Deleteable<T>().ExecuteCommand();
                    if (query.Items.Count > 0)
                    {
                        db.Insertable<T>(query.Items).ExecuteCommand();
                    }
                });
                if (!dbTran.IsSuccess)
                {
                    action.GetResponse().CatchExceptionWithLog(dbTran.ErrorException, $"作业保存到数据库异常");
                }
                action.Message = $"Delete {typeof(T).Name} entity by where and add list in database success";
            }
            catch (Exception ex)
            {
                action.CatchExceptionWithLog(ex, $"Delete {typeof(T).Name} entity by where and add list in database exception");
            }
            return action;
        }

        #endregion

    }//endClass
}