using MySqlX.XDevAPI.Common;
using Org.BouncyCastle.Utilities;
using System.Data;

namespace Tiobon.Core.Services;

/// <summary>
/// 员工群组 (服务)
/// </summary>
public class Ghre_StaffGroupServices : BaseServices<Ghre_StaffGroup, Ghre_StaffGroupDto, InsertGhre_StaffGroupInput, EditGhre_StaffGroupInput>, IGhre_StaffGroupServices
{
    private readonly IBaseRepository<Ghre_StaffGroup> _dal;
    public Ghre_StaffGroupServices(ICaching caching, IBaseRepository<Ghre_StaffGroup> dal)
    {
        this._dal = dal;
        base.BaseDal = dal;
        base._caching = caching;
    }
    public override async Task<ServicePageResult<Ghre_StaffGroupDto>> QueryFilterPage(QueryBody filter, string condition, bool? IsEnable = true)
    {

        if (string.IsNullOrWhiteSpace(filter.orderBy))
            filter.orderBy = "CreateTime1 DESC";

        if (filter.pageSize == 0)
            filter.pageSize = 10000;

        Type entityType = typeof(Ghre_StaffGroup);

        var countSql = @$" SELECT COUNT(1) FROM  {entityType.GetEntityTableName()} A";
        var sql1 = @$"DECLARE @langId   INT = {filter.langId};";
        var sql = @$" SELECT *,
                           ISNULL ((SELECT CASE WHEN @langId = 1 THEN UserName ELSE UserEname END
                                    FROM Ghrs_User B
                                    WHERE B.UserId = A.CreateBy),
                                   '') CreateDataInfo,
                           ISNULL ((SELECT CASE WHEN @langId = 1 THEN UserName ELSE UserEname END
                                    FROM Ghrs_User B
                                    WHERE B.UserId = A.UpdateBy),
                                   '') UpdateDataInfo, ISNULL(A.UpdateTime, A.CreateTime) CreateTime1
                    FROM {entityType.GetEntityTableName()} A";

        string conditions = " WHERE 1=1 ";
        conditions += $" AND dbo.FUserDataBelongPriv ({App.User.ID}, A.DataBelongID, NULL) = 1";

        if (IsEnable == true)
            conditions += " AND IsEnable = 1";
        else if (IsEnable == false)
            conditions += " AND IsEnable = 0";

        if (!string.IsNullOrWhiteSpace(condition))
            conditions += " AND " + condition;

        var properties = entityType.GetGenericProperties();

        if (filter.jsonParam != null)
            foreach (JProperty jProperty in filter.jsonParam.Properties())
            {
                var name = jProperty.Name;
                var value = jProperty.Value.ToString();
                if (name == "page" || name == "pageSize" || !properties.Any(x => x.Name == name))
                    continue;
                if (name == "StaffType1" || name == "StaffType2")
                {
                    var jsonParam = JsonHelper.JsonToObj<JsonParam>(value);

                    switch (jsonParam.operationKey)
                    {
                        case "EqualAny":// 
                            if (jsonParam.columnValue != null)
                            {
                                //var ids1 = JsonHelper.JsonToObj<List<string>>(jsonParam.columnValue.ToString());

                                conditions += $" AND Exists(select 1 from openjson(A.{name}) depts where Exists(select 1 from openjson('{jsonParam.columnValue.ToString()}') where value =depts.[value] ) )";
                            }
                            break;
                        case "NotEqualAny":// 
                            if (jsonParam.columnValue != null)
                            {
                                conditions += $" AND NOT Exists(select 1 from openjson(A.{name}) depts where Exists(select 1 from openjson('{jsonParam.columnValue.ToString()}') where value =depts.[value] ) )";

                            }
                            break;
                        default:
                            break;
                    }
                    continue;
                }

                if (name == "ZoneId" || name == "DeptId" || name == "JobId" || name == "GradeId" || name == "TitleId" || name == "StaffId")
                {
                    var jsonParam = JsonHelper.JsonToObj<JsonParam>(value);

                    switch (jsonParam.operationKey)
                    {
                        case "EqualAny":// 
                            if (jsonParam.columnValue != null)
                                conditions += $" AND (Exists(select 1 from openjson(A.{name}) depts where Exists(select 1 from openjson('{jsonParam.columnValue.ToString()}') where value =depts.[value] ) ) OR " +
                                       $"Exists(select 1 from openjson(A.Excl{name}) depts where Exists(select 1 from openjson('{jsonParam.columnValue.ToString()}') where value =depts.[value] ) ))";
                            break;
                        case "NotEqualAny":// 
                            if (jsonParam.columnValue != null)
                                conditions += $" AND NOT (Exists(select 1 from openjson(A.{name}) depts where Exists(select 1 from openjson('{jsonParam.columnValue.ToString()}') where value =depts.[value] ) ) AND " +
                                       $"Exists(select 1 from openjson(A.Excl{name}) depts where Exists(select 1 from openjson('{jsonParam.columnValue.ToString()}') where value =depts.[value] ) ))";
                            break;
                        default:
                            break;
                    }
                    continue;
                }
                if (!string.IsNullOrWhiteSpace(value))
                    conditions = DealConditions(conditions, name, value);
            }

        sql += conditions;
        countSql += conditions;
        int total = await Db.Ado.GetIntAsync(countSql);

        sql = "SELECT * FROM (SELECT *, ROW_NUMBER() OVER (ORDER BY " + filter.orderBy + ") NUM FROM (SELECT * FROM (" + sql + " ";
        sql += ") A ) B ) C";

        sql += " WHERE NUM <= " + filter.pageNum * filter.pageSize + " AND NUM >" + (filter.pageNum - 1) * filter.pageSize;
        sql = sql1 + sql;
        var entitys = await Db.Ado.SqlQueryAsync<Ghre_StaffGroupDto>(sql);

        var result = new ServicePageResult<Ghre_StaffGroupDto>(filter.pageNum, total, filter.pageSize, entitys);

        var zoneIds = new List<int>();
        var exclZoneIds = new List<int>();
        var deptIds = new List<int>();
        var exclDeptIds = new List<int>();
        var titleIds = new List<int>();
        var exclTitleIds = new List<int>();
        var gradeIds = new List<int>();
        var exclgGradeIds = new List<int>();
        var jobIds = new List<int>();
        var exclJobIds = new List<int>();
        var staffIds = new List<int>();
        var exclStaffIds = new List<int>();
        result.result.DT_TableDataT1.ForEach(async x =>
        {
            x.StaffType1s = JsonHelper.JsonToObj<List<int?>>(x.StaffType1);
            x.StaffType2s = JsonHelper.JsonToObj<List<int?>>(x.StaffType2);
            x.ZoneIds = JsonHelper.JsonToObj<List<int>>(x.ZoneId);
            x.ExclZoneIds = JsonHelper.JsonToObj<List<int>>(x.ExclZoneId);
            x.DeptIds = JsonHelper.JsonToObj<List<int>>(x.DeptId);
            x.ExclDeptIds = JsonHelper.JsonToObj<List<int>>(x.ExclDeptId);
            x.TitleIds = JsonHelper.JsonToObj<List<int>>(x.TitleId);
            x.ExclTitleIds = JsonHelper.JsonToObj<List<int>>(x.ExclTitleId);
            x.GradeIds = JsonHelper.JsonToObj<List<int>>(x.GradeId);
            x.ExclGradeIds = JsonHelper.JsonToObj<List<int>>(x.ExclGradeId);
            x.JobIds = JsonHelper.JsonToObj<List<int>>(x.JobId);
            x.ExclJobIds = JsonHelper.JsonToObj<List<int>>(x.ExclJobId);
            x.StaffIds = JsonHelper.JsonToObj<List<int>>(x.StaffId);
            x.ExclStaffIds = JsonHelper.JsonToObj<List<int>>(x.ExclStaffId);

            zoneIds.AddRange(x.ZoneIds);
            zoneIds.AddRange(x.ExclZoneIds);
            deptIds.AddRange(x.DeptIds);
            deptIds.AddRange(x.ExclDeptIds);
            titleIds.AddRange(x.TitleIds);
            titleIds.AddRange(x.ExclTitleIds);
            gradeIds.AddRange(x.GradeIds);
            gradeIds.AddRange(x.ExclGradeIds);
            jobIds.AddRange(x.JobIds);
            jobIds.AddRange(x.ExclJobIds);
            staffIds.AddRange(x.StaffIds);
            staffIds.AddRange(x.ExclStaffIds);


            x.StaffType1 = null;
            x.StaffType2 = null;
            x.ZoneId = null;
            x.ExclZoneId = null;
            x.DeptId = null;
            x.ExclDeptId = null;
            x.TitleId = null;
            x.ExclTitleId = null;
            x.GradeId = null;
            x.ExclGradeId = null;
            x.JobId = null;
            x.ExclJobId = null;
            x.StaffId = null;
            x.ExclStaffId = null;


            x.GroupType = await GetParaLabel("TrainingGroupType", x.GroupType);

            var StaffType1s = new List<string>();
            var StaffType2s = new List<string>();
            for (int i = 0; i < x.StaffType1s.Count; i++)
            {
                StaffType1s.Add(await GetParaLabel(x.StaffType1s[i]));
            }
            for (int i = 0; i < x.StaffType2s.Count; i++)
            {
                StaffType2s.Add(await GetParaLabel(x.StaffType2s[i]));
            }

            x.StaffType1 = string.Join(",", StaffType1s);
            x.StaffType2 = string.Join(",", StaffType2s);
            x.DataBelongName = await GetDataBelongName(x.DataBelongID);

        });

        if (zoneIds.Any())
        {
            var zones = await Db.Queryable<Ghra_Zone>().Where(x => zoneIds.Contains(x.ZoneID.Value)).ToListAsync();

            result.result.DT_TableDataT1.ForEach(rule =>
            {
                rule.ZoneId = string.Join(",", zones.Where(o => rule.ZoneIds.Contains(o.ZoneID.Value)).Select(o => o.ZoneName));
                rule.ExclZoneId = string.Join(",", zones.Where(o => rule.ExclZoneIds.Contains(o.ZoneID.Value)).Select(o => o.ZoneName));
            });
        }

        if (deptIds.Any())
        {
            var depts = await Db.Queryable<Ghro_Dept>().Where(x => deptIds.Contains(x.DeptID.Value)).ToListAsync();

            result.result.DT_TableDataT1.ForEach(rule =>
            {
                rule.DeptId = string.Join(",", depts.Where(o => rule.DeptIds.Contains(o.DeptID.Value)).Select(o => o.DeptName));
                rule.ExclDeptId = string.Join(",", depts.Where(o => rule.ExclDeptIds.Contains(o.DeptID.Value)).Select(o => o.DeptName));
            });
        }

        if (titleIds.Any())
        {
            var titles = await Db.Queryable<Ghra_Title>().Where(x => titleIds.Contains(x.TitleID.Value)).ToListAsync();

            result.result.DT_TableDataT1.ForEach(rule =>
            {
                rule.TitleId = string.Join(",", titles.Where(o => rule.TitleIds.Contains(o.TitleID.Value)).Select(o => o.TitleName));
                rule.ExclTitleId = string.Join(",", titles.Where(o => rule.ExclTitleIds.Contains(o.TitleID.Value)).Select(o => o.TitleName));
            });
        }

        if (gradeIds.Any())
        {
            var grades = await Db.Queryable<Ghra_Grade>().Where(x => gradeIds.Contains(x.GradeID)).ToListAsync();

            result.result.DT_TableDataT1.ForEach(rule =>
            {
                rule.GradeId = string.Join(",", grades.Where(o => rule.GradeIds.Contains(o.GradeID)).Select(o => o.GradeName));
                rule.ExclGradeId = string.Join(",", grades.Where(o => rule.ExclGradeIds.Contains(o.GradeID)).Select(o => o.GradeName));
            });
        }

        if (jobIds.Any())
        {
            var jobs = await Db.Queryable<Ghra_Job>().Where(x => jobIds.Contains(x.JobID.Value)).ToListAsync();

            result.result.DT_TableDataT1.ForEach(rule =>
            {
                rule.JobId = string.Join(",", jobs.Where(o => rule.JobIds.Contains(o.JobID.Value)).Select(o => o.JobName));
                rule.ExclJobId = string.Join(",", jobs.Where(o => rule.ExclJobIds.Contains(o.JobID.Value)).Select(o => o.JobName));
            });
        }

        if (staffIds.Any())
        {
            var staffs = await Db.Queryable<Ghra_Staff>().Where(x => staffIds.Contains(x.StaffID)).ToListAsync();

            result.result.DT_TableDataT1.ForEach(rule =>
            {
                rule.StaffId = string.Join(",", staffs.Where(o => rule.StaffIds.Contains(o.StaffID)).Select(o => o.StaffName));
                rule.ExclStaffId = string.Join(",", staffs.Where(o => rule.ExclStaffIds.Contains(o.StaffID)).Select(o => o.StaffName));
            });
        }

        //sponsorIds = sponsorIds.Distinct().ToList();
        //var staffs = await Db.Queryable<Ghra_Staff>().Where(x => sponsorIds.Contains(x.StaffID)).ToListAsync();
        //var users = await Db.Queryable<Ghrs_User>().Where(x => userIds.Contains(x.UserId)).ToListAsync();
        //result.result.DT_TableDataT1.ForEach(async x =>
        //{
        //    x.StaffType1s
        //    x.CourseSourceLabel = await GetParaLabel("TrainingRequestCourseSource", x.CourseSource);
        //    x.RequestSourceLabel = await GetParaLabel("TrainingRequestRequestSource", x.RequestSource);
        //    x.TrainClassLabel = await GetParaLabel("TrainingRequestTrainClass", x.TrainClass);
        //    x.TrainLevelLabel = await GetParaLabel("TrainingRequestTrainLevel", x.TrainLevel);
        //    x.InOrOutLabel = await GetParaLabel("CourseInOrOut", x.InOrOut);
        //    x.TeacherClassLabel = await GetParaLabel("TrainingTeacherType", x.InOrOut);

        //    //if (x.CourseId != null)
        //    //    x.CourseName = courses.Where(o => x.CourseId == o.Id).FirstOrDefault()?.CourseName;

        //    if (x.DeptId != null)
        //        x.DeptName = depts.Where(o => x.DeptId == o.DeptID).FirstOrDefault()?.DeptName;

        //    if (x.SponsorId != null)
        //        x.SponsorName = staffs.Where(o => x.SponsorId == o.StaffID).FirstOrDefault()?.StaffName;
        //    if (x.TrainStaffIds.IsNotEmptyOrNull())
        //        x.TrainStaffNames = string.Join("、", staffs.Where(o => x.TrainStaffIds.Contains(o.StaffID)).Select(o => o.StaffName));

        //    if (x.AgreeUserId != null)
        //        x.AgreeUserName = users.Where(o => x.AgreeUserId == o.UserId).FirstOrDefault()?.UserName;


        //    if (x.RefuseUserId != null)
        //        x.RefuseUserName = users.Where(o => x.RefuseUserId == o.UserId).FirstOrDefault()?.UserName;

        //    if (x.WorkState.IsNotEmptyOrNull())
        //    {
        //        if (x.WorkState == 0) x.WorkStateLabel = "审批中";
        //        if (x.WorkState == 1) x.WorkStateLabel = "审批通过";
        //    }

        //    if (x.RequestNum.IsNotEmptyOrNull() && x.RequestNum > 0)
        //        if (x.TrainDays.IsNotEmptyOrNull() && x.TrainDays > 0)
        //        {
        //            var TotalTrainDays = x.TrainDays * x.RequestNum;
        //            x.TotalTrainDays = StringHelper.TrimDecimalString(TotalTrainDays);
        //        }
        //});
        return result;

    }
    public override async Task<long> Add(InsertGhre_StaffGroupInput entity)
    {
        entity.StaffType1 = JsonHelper.ObjToJson(entity.StaffType1s);
        entity.StaffType2 = JsonHelper.ObjToJson(entity.StaffType2s);
        entity.ZoneId = JsonHelper.ObjToJson(entity.ZoneIds);
        entity.ExclZoneId = JsonHelper.ObjToJson(entity.ExclZoneIds);
        entity.DeptId = JsonHelper.ObjToJson(entity.DeptIds);
        entity.ExclDeptId = JsonHelper.ObjToJson(entity.ExclDeptIds);
        entity.TitleId = JsonHelper.ObjToJson(entity.TitleIds);
        entity.ExclTitleId = JsonHelper.ObjToJson(entity.ExclTitleIds);
        entity.GradeId = JsonHelper.ObjToJson(entity.GradeIds);
        entity.ExclGradeId = JsonHelper.ObjToJson(entity.ExclGradeIds);
        entity.JobId = JsonHelper.ObjToJson(entity.JobIds);
        entity.ExclJobId = JsonHelper.ObjToJson(entity.ExclJobIds);
        entity.StaffId = JsonHelper.ObjToJson(entity.StaffIds);
        entity.ExclStaffId = JsonHelper.ObjToJson(entity.ExclStaffIds);
        var result = await base.Add(entity);


        return result;
    }

    public override async Task<bool> Update(long Id, EditGhre_StaffGroupInput entity)
    {

        entity.StaffType1 = JsonHelper.ObjToJson(entity.StaffType1s);
        entity.StaffType2 = JsonHelper.ObjToJson(entity.StaffType2s);
        entity.ZoneId = JsonHelper.ObjToJson(entity.ZoneIds);
        entity.ExclZoneId = JsonHelper.ObjToJson(entity.ExclZoneIds);
        entity.DeptId = JsonHelper.ObjToJson(entity.DeptIds);
        entity.ExclDeptId = JsonHelper.ObjToJson(entity.ExclDeptIds);
        entity.TitleId = JsonHelper.ObjToJson(entity.TitleIds);
        entity.ExclTitleId = JsonHelper.ObjToJson(entity.ExclTitleIds);
        entity.GradeId = JsonHelper.ObjToJson(entity.GradeIds);
        entity.ExclGradeId = JsonHelper.ObjToJson(entity.ExclGradeIds);
        entity.JobId = JsonHelper.ObjToJson(entity.JobIds);
        entity.ExclJobId = JsonHelper.ObjToJson(entity.ExclJobIds);
        entity.StaffId = JsonHelper.ObjToJson(entity.StaffIds);
        entity.ExclStaffId = JsonHelper.ObjToJson(entity.ExclStaffIds);
        var result = await base.Update(Id, entity);

        return result;
    }

    public override async Task<ServiceFormResult<Ghre_StaffGroupDto>> QueryForm(QueryForm body)
    {
        var result = await base.QueryForm(body);
        var DT_TableDataT1 = result.result.DT_TableDataT1[0];
        DT_TableDataT1.StaffType1s = JsonHelper.JsonToObj<List<int?>>(DT_TableDataT1.StaffType1);
        DT_TableDataT1.StaffType2s = JsonHelper.JsonToObj<List<int?>>(DT_TableDataT1.StaffType2);
        DT_TableDataT1.ZoneIds = JsonHelper.JsonToObj<List<int>>(DT_TableDataT1.ZoneId);
        DT_TableDataT1.ExclZoneIds = JsonHelper.JsonToObj<List<int>>(DT_TableDataT1.ExclZoneId);
        DT_TableDataT1.DeptIds = JsonHelper.JsonToObj<List<int>>(DT_TableDataT1.DeptId);
        DT_TableDataT1.ExclDeptIds = JsonHelper.JsonToObj<List<int>>(DT_TableDataT1.ExclDeptId);
        DT_TableDataT1.TitleIds = JsonHelper.JsonToObj<List<int>>(DT_TableDataT1.TitleId);
        DT_TableDataT1.ExclTitleIds = JsonHelper.JsonToObj<List<int>>(DT_TableDataT1.ExclTitleId);
        DT_TableDataT1.GradeIds = JsonHelper.JsonToObj<List<int>>(DT_TableDataT1.GradeId);
        DT_TableDataT1.ExclGradeIds = JsonHelper.JsonToObj<List<int>>(DT_TableDataT1.ExclGradeId);
        DT_TableDataT1.JobIds = JsonHelper.JsonToObj<List<int>>(DT_TableDataT1.JobId);
        DT_TableDataT1.ExclJobIds = JsonHelper.JsonToObj<List<int>>(DT_TableDataT1.ExclJobId);
        DT_TableDataT1.StaffIds = JsonHelper.JsonToObj<List<int>>(DT_TableDataT1.StaffId);
        DT_TableDataT1.ExclStaffIds = JsonHelper.JsonToObj<List<int>>(DT_TableDataT1.ExclStaffId);
        result.result.DT_TableDataT1[0] = DT_TableDataT1;
        return result;

    }

    public async Task<ServicePageResult<Ghra_StaffDto>> QueryStaff(long id, QueryBody filter, string condition, bool? IsEnable = true)
    {
        var x = await base.QueryById(id);

        if (x is null)
            return new ServicePageResult<Ghra_StaffDto>(filter.pageNum, 0, filter.pageSize, new List<Ghra_StaffDto>());

        x.StaffType1s = JsonHelper.JsonToObj<List<int?>>(x.StaffType1);
        x.StaffType2s = JsonHelper.JsonToObj<List<int?>>(x.StaffType2);
        x.ZoneIds = JsonHelper.JsonToObj<List<int>>(x.ZoneId);
        x.ExclZoneIds = JsonHelper.JsonToObj<List<int>>(x.ExclZoneId);
        x.DeptIds = JsonHelper.JsonToObj<List<int>>(x.DeptId);
        x.ExclDeptIds = JsonHelper.JsonToObj<List<int>>(x.ExclDeptId);
        x.TitleIds = JsonHelper.JsonToObj<List<int>>(x.TitleId);
        x.ExclTitleIds = JsonHelper.JsonToObj<List<int>>(x.ExclTitleId);
        x.GradeIds = JsonHelper.JsonToObj<List<int>>(x.GradeId);
        x.ExclGradeIds = JsonHelper.JsonToObj<List<int>>(x.ExclGradeId);
        x.JobIds = JsonHelper.JsonToObj<List<int>>(x.JobId);
        x.ExclJobIds = JsonHelper.JsonToObj<List<int>>(x.ExclJobId);
        x.StaffIds = JsonHelper.JsonToObj<List<int>>(x.StaffId);
        x.ExclStaffIds = JsonHelper.JsonToObj<List<int>>(x.ExclStaffId);

        var query = Db.Queryable<Ghra_Staff>();

        #region 处理查询条件 
        var whereExpression = Expressionable.Create<Ghra_Staff>();
        if (filter.jsonParam != null)
            foreach (JProperty jProperty in filter.jsonParam.Properties())
            {
                var name = jProperty.Name;
                var value = jProperty.Value.ToString();
                if (name == "page" || name == "pageSize")
                    continue;
                if (value.IsNotEmptyOrNull())
                {
                    var jsonParam = JsonHelper.JsonToObj<JsonParam>(value);

                    switch (name)
                    {
                        //case "WaitRecommend":
                        //    if (jsonParam.columnValue.ObjToInt() == 1)
                        //        whereExpression.And(x => x.Status == DIC_INTERVIEW_ORDER_STATUS.WaitRecommended);
                        //    break;



                        //    break;
                        default:
                            break;
                    }
                }
            }

        if (x.StaffType1s.Any())
            whereExpression.And(o => x.StaffType1s.Contains(o.StaffType1));
        if (x.StaffType2s.Any())
            whereExpression.And(o => x.StaffType1s.Contains(o.StaffType2));

        if (x.ZoneIds.Any())
            whereExpression.And(o => o.ZoneID != null && x.ZoneIds.Contains(o.ZoneID.Value));
        if (x.ExclZoneIds.Any())
            whereExpression.And(o => o.ZoneID != null && x.ExclZoneIds.Contains(o.ZoneID.Value));

        if (x.DeptIds.Any())
            whereExpression.And(o => o.ZoneID != null && x.DeptIds.Contains(o.DeptID.Value));
        if (x.ExclDeptIds.Any())
            whereExpression.And(o => o.ZoneID != null && x.ExclDeptIds.Contains(o.DeptID.Value));

        if (x.TitleIds.Any())
            whereExpression.And(o => o.ZoneID != null && x.TitleIds.Contains(o.TitleID.Value));
        if (x.ExclTitleIds.Any())
            whereExpression.And(o => o.ZoneID != null && x.ExclTitleIds.Contains(o.TitleID.Value));

        if (x.GradeIds.Any())
            whereExpression.And(o => o.ZoneID != null && x.GradeIds.Contains(o.GradeID.Value));
        if (x.ExclGradeIds.Any())
            whereExpression.And(o => o.ZoneID != null && x.ExclGradeIds.Contains(o.GradeID.Value));

        if (x.GradeIds.Any())
            whereExpression.And(o => o.ZoneID != null && x.GradeIds.Contains(o.GradeID.Value));
        if (x.ExclGradeIds.Any())
            whereExpression.And(o => o.ZoneID != null && x.ExclGradeIds.Contains(o.GradeID.Value));

        if (x.JobIds.Any())
            whereExpression.And(o => o.ZoneID != null && x.JobIds.Contains(o.JobID.Value));
        if (x.ExclJobIds.Any())
            whereExpression.And(o => o.ZoneID != null && x.ExclJobIds.Contains(o.JobID.Value));

        if (x.StaffIds.Any())
            whereExpression.And(o => o.ZoneID != null && x.StaffIds.Contains(o.StaffID));
        if (x.ExclStaffIds.Any())
            whereExpression.And(o => o.ZoneID != null && x.ExclStaffIds.Contains(o.StaffID));
        #endregion

        RefAsync<int> total = 0;
        var entitys = await query
            .WhereIF(condition.IsNotEmptyOrNull(), condition)
            .Where(whereExpression.ToExpression())
            .OrderBy(it => new { UpdateTime = SqlFunc.Desc(it.UpdateTime), CreateTime = SqlFunc.Desc(it.CreateTime) })
            .ToPageListAsync(filter.pageNum, filter.pageSize, total);
        var data = Mapper.Map(entitys).ToANew<List<Ghra_StaffDto>>();

        var zoneIds = data.Where(x => x.ZoneID != null).Select(x => x.ZoneID.Value).ToList();
        var zones = await Db.Queryable<Ghra_Zone>().Where(x => x.ZoneID != null && zoneIds.Contains(x.ZoneID.Value)).ToListAsync();

        var deptIds = data.Where(x => x.DeptID != null).Select(x => x.DeptID.Value).ToList();
        var depts = await Db.Queryable<Ghro_Dept>().Where(x => x.DeptID != null && deptIds.Contains(x.DeptID.Value)).ToListAsync();

        var titleIds = data.Where(x => x.TitleID != null).Select(x => x.TitleID.Value).ToList();
        var titles = await Db.Queryable<Ghra_Title>().Where(x => x.TitleID != null && titleIds.Contains(x.TitleID.Value)).ToListAsync();

        var gradeIds = data.Where(x => x.GradeID != null).Select(x => x.GradeID.Value).ToList();
        var grades = await Db.Queryable<Ghra_Grade>().Where(x => gradeIds.Contains(x.GradeID)).ToListAsync();

        var jobIds = data.Where(x => x.JobID != null).Select(x => x.JobID.Value).ToList();
        var jobs = await Db.Queryable<Ghra_Job>().Where(x => x.JobID != null && jobIds.Contains(x.JobID.Value)).ToListAsync();

        data.ForEach(async x =>
        {
            x.GenderLabel = await GetParaLabel("Gender", x.Gender);
            x.IndateLabel = DateTimeHelper.ConvertToDayString(x.Indate);
            x.StaffType1Label = await GetParaLabel(x.StaffType1);
            x.StaffType2Label = await GetParaLabel(x.StaffType2);
            if (x.ZoneID != null)
                x.ZoneName = zones.Where(o => o.ZoneID == x.ZoneID).FirstOrDefault()?.ZoneName;
            if (x.DeptID != null)
                x.DeptName = depts.Where(o => o.DeptID == x.DeptID).FirstOrDefault()?.DeptName;
            if (x.GradeID != null)
                x.GradeName = grades.Where(o => o.GradeID == x.GradeID).FirstOrDefault()?.GradeName;
            if (x.JobID != null)
                x.JobName = jobs.Where(o => o.JobID == x.JobID).FirstOrDefault()?.JobName;
            if (x.TitleID != null)
                x.TitleName = titles.Where(o => o.TitleID == x.TitleID).FirstOrDefault()?.TitleName;
        });

        return new ServicePageResult<Ghra_StaffDto>(filter.pageNum, total, filter.pageSize, data);
    }
}