hotline/src/Hotline.Repository.SqlSugar/Orders/OrderRepository.cs

using System.Collections;
using Hotline.CallCenter.Calls;
using Hotline.Orders;
using Hotline.Repository.SqlSugar.DataPermissions;
using Hotline.Repository.SqlSugar.Extensions;
using Hotline.Settings;
using Hotline.Settings.Hotspots;
using Hotline.Share.Dtos.Bi;
using Hotline.Share.Dtos.CallCenter;
using Hotline.Share.Dtos.Order;
using Hotline.Share.Enums.CallCenter;
using Hotline.Share.Enums.Order;
using Hotline.Share.Requests;
using Hotline.Share.Tools;
using Microsoft.EntityFrameworkCore.Storage.ValueConversion.Internal;
using SqlSugar;
using System.Data;
using System.Linq;
using System.Linq.Dynamic.Core;
using System.Net;
using System.Reflection;
using System.Reflection.Emit;
using Hotline.Caching.Interfaces;
using XF.Domain.Authentications;
using XF.Domain.Dependency;
using XF.Domain.Repository;
using System.Collections.Generic;
using Hotline.SeedData;
using Hotline.Share.Notifications.NewRockCallCenter;
using Hotline.Statistics;
using System.Dynamic;
using Hotline.Share.Dtos;
using Org.BouncyCastle.Crypto.Fpe;
using Org.BouncyCastle.Utilities.Zlib;
using static System.Runtime.InteropServices.JavaScript.JSType;
using System;
using static System.Runtime.CompilerServices.RuntimeHelpers;
using Hotline.Identity.Accounts;
using Hotline.Share.Dtos.Enterprise;

namespace Hotline.Repository.SqlSugar.Orders
{
    public class OrderRepository : BaseRepositoryWorkflow<Order>, IOrderRepository, IScopeDependency
    {
        private readonly IDataPermissionFilterBuilder _dataPermissionFilterBuilder;
        private readonly IRepository<OrderSendBackAudit> _orderSendBackAuditRepository;
        private readonly IRepository<OrderVisit> _orderVisitRepository;
        private readonly IRepository<OrderVisitDetail> _orderVisitDetailRepository;
        private readonly ISessionContext _sessionContext;
        private readonly ISystemSettingCacheManager _systemSettingCacheManager;
        private readonly IRepository<AiOrderVisitDetail> _aiOrderVisitDetailRepository;

        public OrderRepository(ISugarUnitOfWork<HotlineDbContext> uow,
            IDataPermissionFilterBuilder dataPermissionFilterBuilder,
            IRepository<OrderSendBackAudit> orderSendBackAuditRepository,
            IRepository<OrderVisit> orderVisitRepository,
            ISessionContext sessionContext,
            IRepository<OrderVisitDetail> orderVisitDetailRepository,
            ISystemSettingCacheManager systemSettingCacheManager,
            IRepository<AiOrderVisitDetail> aiOrderVisitDetailRepository
            ) : base(uow, dataPermissionFilterBuilder)
        {
            _dataPermissionFilterBuilder = dataPermissionFilterBuilder;
            _orderSendBackAuditRepository = orderSendBackAuditRepository;
            _orderVisitRepository = orderVisitRepository;
            _sessionContext = sessionContext;
            _orderVisitDetailRepository = orderVisitDetailRepository;
            _systemSettingCacheManager = systemSettingCacheManager;
            _aiOrderVisitDetailRepository = aiOrderVisitDetailRepository;
        }

        public async Task<string> AddOrderNavAsync(Order order, CancellationToken cancellationToken)
        {
            //弥补AddNav方法没有自动指派到创建人
            order.AssignToCreator(_dataPermissionFilterBuilder.DataPermissionManager);

            await AddNav(order).Include(d => d.OrderExtension).Include(d => d.OrderTags).ExecuteCommandAsync();

            return order.Id;
        }



        /// <summary>
        /// 工单词库批量删除
        /// </summary>
        /// <param name="ids"></param>
        /// <param name="cancellationToken"></param>
        /// <returns></returns>
        public async Task RemoveOrderWrodBatchAsync(List<string> ids, CancellationToken cancellationToken)
        {
            await Db.Deleteable<OrderWord>().In(x => x.Id, ids).ExecuteCommandAsync(cancellationToken);
        }


        /// <summary>
        /// 工单观察批量删除
        /// </summary>
        /// <param name="ids"></param>
        /// <param name="cancellationToken"></param>
        /// <returns></returns>
        public async Task RemoveOrderObserveBatchAsync(List<string> ids, CancellationToken cancellationToken)
        {
            await Db.Deleteable<OrderObserve>().In(x => x.Id, ids).ExecuteCommandAsync(cancellationToken);
        }

        /// <summary>
        /// 工单终结批量删除
        /// </summary>
        /// <param name="ids"></param>
        /// <param name="cancellationToken"></param>
        /// <returns></returns>
        public async Task RemoveOrderFinalityBatchAsync(List<string> ids, CancellationToken cancellationToken)
        {
            await Db.Deleteable<OrderFinality>().In(x => x.Id, ids).ExecuteCommandAsync(cancellationToken);
        }

        public async Task OrderScreenRevisionVisit(string VisitId, bool canHandle, CancellationToken cancellationToken)
        {

            await Db.Updateable<OrderVisit>().SetColumns(x => x.IsCanHandle == canHandle).Where(x => x.Id == VisitId).ExecuteCommandAsync(cancellationToken);
        }

        public async Task FileAsync(Order order, CancellationToken cancellationToken)
        {
            await Db.Updateable<Order>().SetColumns(x => x.FileJson == order.FileJson).Where(x => x.Id == order.Id).ExecuteCommandAsync(cancellationToken);
        }

        private static object GetPropertyValue(object obj, string property)
        {
            PropertyInfo propertyInfo = obj.GetType().GetProperty(property);
            return propertyInfo.GetValue(obj, null);
        }

        public async Task<object> HotPortJoinOrgStatistics(DateTime StartTime, DateTime EndTime, bool IsCenter, string? OrgCode)
        {
            //查询一级热点
            var hotSpotList = await Db.Queryable<Hotspot>().Where(x => string.IsNullOrEmpty(x.ParentId)).ToListAsync();
            List<dynamic>? listReturn = new List<dynamic>();
            foreach (var item in hotSpotList)
            {
                var table = await Db.Queryable<Order>()
                    .LeftJoin<SystemOrganize>((it, o) => it.ActualHandleOrgCode.Substring(SqlFunc.MappingColumn<int>("0"), SqlFunc.MappingColumn<int>("6")) == o.Id)
                    .Where((it, o) => it.CreationTime >= StartTime && it.CreationTime <= EndTime)
                    .WhereIF(IsCenter == false, (it, o) => o.Id.StartsWith(OrgCode))
                    .GroupBy((it, o) => new
                    {
                        CurrentHandleOrgCode = (it.ActualHandleOrgCode.Substring(SqlFunc.MappingColumn<int>("0"), SqlFunc.MappingColumn<int>("6"))),
                        o.Name
                    })
                    .Select((it, o) => new
                    {
                        OrgCode = it.ActualHandleOrgCode.Substring(SqlFunc.MappingColumn<int>("0"), SqlFunc.MappingColumn<int>("6")),
                        Count = SqlFunc.AggregateSum(SqlFunc.IIF(it.HotspotId.Substring(0, 2) == item.Id, 1, 0)),
                        Key = item.Id,
                        OrgName = o.Name,
                        HotSorpName = item.HotSpotName
                    }).ToPivotListAsync(x => x.Key, x => new { x.OrgCode, x.OrgName, x.HotSorpName }, x => x.Sum(x => x.Count));
                listReturn.AddRange(table);
            }
            return new { HotSpot = hotSpotList, Data = listReturn };
        }

        public ISugarQueryable<DepartmentalProcessingStatisticsDataDto> OrderUnionAll(ISugarQueryable<DepartmentalProcessingStatisticsDataDto> t1,
            ISugarQueryable<DepartmentalProcessingStatisticsDataDto> t2,
            ISugarQueryable<DepartmentalProcessingStatisticsDataDto> t3,
            ISugarQueryable<DepartmentalProcessingStatisticsDataDto> t4,
            ISugarQueryable<DepartmentalProcessingStatisticsDataDto> t5,
            ISugarQueryable<DepartmentalProcessingStatisticsDataDto> t6)
        {
            return Db.UnionAll(t1, t2, t3, t4, t5, t6)
                       .GroupBy(p => new { p.OrgCode })
                       .Select(p => new DepartmentalProcessingStatisticsDataDto
                       {
                           OrgCode = p.OrgCode,
                           //  OrderCountNum = SqlFunc.AggregateSum(p.OrderCountNum),
                           YbOrderCountNum = SqlFunc.AggregateSum(p.YbOrderCountNum),
                           ZbOrderCountNum = SqlFunc.AggregateSum(p.ZbOrderCountNum),
                           Archived = SqlFunc.AggregateSum(p.Archived),
                           ToBeArchived = SqlFunc.AggregateSum(p.ToBeArchived),
                           WaitPublished = SqlFunc.AggregateSum(p.WaitPublished),
                           PublishedOpen = SqlFunc.AggregateSum(p.PublishedOpen),
                           PublishedNoOpen = SqlFunc.AggregateSum(p.PublishedNoOpen),
                           YbOverdue = SqlFunc.AggregateSum(p.YbOverdue),
                           ZbOverdue = SqlFunc.AggregateSum(p.ZbOverdue),
                           CompleteOnTime = SqlFunc.AggregateSum(p.CompleteOnTime),
                           HqybOverdue = SqlFunc.AggregateSum(p.HqybOverdue),
                           HqzbOverdue = SqlFunc.AggregateSum(p.HqzbOverdue),
                           DelayEnd = SqlFunc.AggregateSum(p.DelayEnd),
                           DelayWait = SqlFunc.AggregateSum(p.DelayWait),
                           OrderDelayCount = SqlFunc.AggregateSum(p.OrderDelayCount),
                           ScreenCount = SqlFunc.AggregateSum(p.ScreenCount),
                           ScreenApproval = SqlFunc.AggregateSum(p.ScreenApproval),
                           ScreenPass = SqlFunc.AggregateSum(p.ScreenPass),
                           ScreenNotPass = SqlFunc.AggregateSum(p.ScreenNotPass),
                           SatisfactionCount = SqlFunc.AggregateSum(p.SatisfactionCount),
                           NotSatisfactionCount = SqlFunc.AggregateSum(p.NotSatisfactionCount)
                       })
                       .MergeTable();


        }

        public ISugarQueryable<T> UnionAll<T>(params ISugarQueryable<T>[] queryables) where T : class
        {
            return Db.UnionAll(queryables);
        }

        /// <summary>
        /// 部门办件统计
        /// </summary>
        /// <param name="sql"></param>
        /// <returns></returns>
        public List<DepartmentalProcessingStatisticsDataDto> DepartmentalProcessingStatisticsNew(string sql)
        {
            return Db.Ado.SqlQuery<DepartmentalProcessingStatisticsDataDto>(sql);

        }

        /// <summary>
        /// 区域分时统计
        /// </summary>
        /// <param name="dto"></param>
        /// <returns></returns>
        public async Task<object> OrderAreaTime(TimeSharingPagedKeywordRequest dto)
        {
            List<int> dts = new List<int>();
            for (int i = 0; i < 24; i++)
            {
                dts.Add(i);
            }

            var listHour = Db.Reportable(dts).ToQueryable<int>();
            var listOrder = Db.Queryable<Order>()
                 .Where(p => p.CreationTime >= dto.StartTime && p.CreationTime <= dto.EndTime)
                 .WhereIF(dto.TypeId != null && dto.TypeId == 1, p => p.IdentityType == EIdentityType.Citizen)
                 .WhereIF(dto.TypeId != null && dto.TypeId == 2, p => p.IdentityType == EIdentityType.Enterprise)
                 .Select(p => new
                 {
                     AreaCode = p.AreaCode.Substring(SqlFunc.MappingColumn<int>("0"), SqlFunc.MappingColumn<int>("6")),
                     Hour = p.CreationTime.Hour.ToString()
                 })
               .MergeTable();

            var listOrg = Db.Queryable<SystemArea>()
              .LeftJoin(listOrder, (s, p) => s.Id == p.AreaCode)
              .Where((s, p) => s.Id.Length == 6 && s.Id != "510000")
              .GroupBy((s, p) => s.Id).GroupBy((s, p) => s.AreaName)
              .GroupBy((s, p) => p.Hour)
              .OrderBy((s, p) => s.Id)
              .Select((s, p) => new
              {
                  count = SqlFunc.AggregateSum(SqlFunc.IIF(p.AreaCode != null && p.AreaCode != "", 1, 0)),
                  AreaCode = s.Id,
                  Hour = SqlFunc.IIF(p.Hour == null || p.Hour == "", "0", p.Hour),
                  AreaName = s.AreaName
              })
              .MergeTable();

            var list = await listHour.LeftJoin(listOrg, (x, p) => x.ColumnName.ToString() == p.Hour)
                .OrderBy(x => x.ColumnName)
               .Select((x, p) => new
               {
                   Hour = x.ColumnName.ToString() + ":00 - " + x.ColumnName.ToString() + ":59",
                   p.AreaCode,
                   p.count
               })
                .ToPivotListAsync(p => p.AreaCode, p => p.Hour, p => p.Sum(x => x.count));
            return list;

        }

        /// <summary>
        /// 区域分时统计--导出
        /// </summary>
        /// <param name="dto"></param>
        /// <returns></returns>
        public async Task<DataTable> OrderAreaTimeExport(TimeSharingPagedKeywordRequest dto)
        {
            List<int> dts = new();
            for (int i = 0; i < 24; i++)
            {
                dts.Add(i);
            }

            var listHour = Db.Reportable(dts).ToQueryable<int>();
            var listOrder = Db.Queryable<Order>()
                 .Where(p => p.CreationTime >= dto.StartTime && p.CreationTime <= dto.EndTime)
                   .WhereIF(dto.TypeId != null && dto.TypeId == 1, p => p.IdentityType == EIdentityType.Citizen)
                 .WhereIF(dto.TypeId != null && dto.TypeId == 2, p => p.IdentityType == EIdentityType.Enterprise)
                 .Select(p => new
                 {
                     AreaCode = p.AreaCode.Substring(SqlFunc.MappingColumn<int>("0"), SqlFunc.MappingColumn<int>("6")),
                     Hour = p.CreationTime.Hour.ToString()
                 })
               .MergeTable();

            var listOrg = Db.Queryable<SystemArea>()
              .LeftJoin(listOrder, (s, p) => s.Id == p.AreaCode)
              .Where((s, p) => s.Id.Length == 6 && s.Id != "510000")
              .GroupBy((s, p) => s.Id).GroupBy((s, p) => s.AreaName)
              .GroupBy((s, p) => p.Hour)
              .OrderBy((s, p) => s.Id)
              .Select((s, p) => new
              {
                  count = SqlFunc.AggregateSum(SqlFunc.IIF(p.AreaCode != null && p.AreaCode != "", 1, 0)),
                  AreaCode = s.Id,
                  Hour = SqlFunc.IIF(p.Hour == null || p.Hour == "", "0", p.Hour),
                  AreaName = s.AreaName
              })
              .MergeTable();

            var dt = await listHour.LeftJoin(listOrg, (x, p) => x.ColumnName.ToString() == p.Hour)
               .OrderBy(x => x.ColumnName)
              .Select((x, p) => new
              {
                  Hour = x.ColumnName.ToString() + ":00 - " + x.ColumnName.ToString() + ":59",
                  p.AreaName,
                  p.count
              })
               .ToPivotTableAsync(p => p.AreaName, p => p.Hour, p => p.Sum(x => x.count));

            return InitDatatTable(dt, dto.AddColumnName);
        }

        /// <summary>
        /// 热点类型分时统计
        /// </summary>
        /// <param name="dto"></param>
        /// <returns></returns>
        public async Task<object> OrderHotspotTime(TimeSharingPagedKeywordRequest dto)
        {
            List<int> dts = new List<int>();
            for (int i = 0; i < 24; i++)
            {
                dts.Add(i);
            }

            var listHour = Db.Reportable(dts).ToQueryable<int>();
            var listOrder = Db.Queryable<Order>()
                 .Where(p => p.CreationTime >= dto.StartTime && p.CreationTime <= dto.EndTime)
                  .WhereIF(dto.TypeId != null && dto.TypeId == 1, p => p.IdentityType == EIdentityType.Citizen)
                 .WhereIF(dto.TypeId != null && dto.TypeId == 2, p => p.IdentityType == EIdentityType.Enterprise)
                 .Select(p => new
                 {
                     HotspotCode = p.HotspotId.Substring(SqlFunc.MappingColumn<int>("0"), SqlFunc.MappingColumn<int>("2")),
                     Hour = p.CreationTime.Hour.ToString()
                 })
               .MergeTable();

            var listOrg = Db.Queryable<Hotspot>()
              .LeftJoin(listOrder, (s, p) => s.Id == p.HotspotCode)
              .Where((s, p) => s.Id.Length == 2 && (s.ParentId == null || s.ParentId == ""))
              .GroupBy((s, p) => s.Id).GroupBy((s, p) => s.HotSpotName)
              .GroupBy((s, p) => p.Hour)
              .OrderBy((s, p) => s.Id)
              .Select((s, p) => new
              {
                  count = SqlFunc.AggregateSum(SqlFunc.IIF(p.HotspotCode != null && p.HotspotCode != "", 1, 0)),
                  HotspotCode = s.Id,
                  Hour = SqlFunc.IIF(p.Hour == null || p.Hour == "", "0", p.Hour),
                  HotSpotName = s.HotSpotName
              })
              .MergeTable();

            var list = await listHour.LeftJoin(listOrg, (x, p) => x.ColumnName.ToString() == p.Hour)
                .OrderBy(x => x.ColumnName)
               .Select((x, p) => new
               {
                   Hour = x.ColumnName.ToString() + ":00 - " + x.ColumnName.ToString() + ":59",
                   p.HotspotCode,
                   p.count
               })
                .ToPivotListAsync(p => p.HotspotCode, p => p.Hour, p => p.Sum(x => x.count));
            return list;

        }

        /// <summary>
        /// 热点类型分时统计---导出
        /// </summary>
        /// <param name="dto"></param>
        /// <returns></returns>
        public async Task<DataTable> OrderHotspotTimeExport(TimeSharingPagedKeywordRequest dto)
        {
            List<int> dts = new();
            for (int i = 0; i < 24; i++)
            {
                dts.Add(i);
            }

            var listHour = Db.Reportable(dts).ToQueryable<int>();
            var listOrder = Db.Queryable<Order>()
                 .Where(p => p.CreationTime >= dto.StartTime && p.CreationTime <= dto.EndTime)
                  .WhereIF(dto.TypeId != null && dto.TypeId == 1, p => p.IdentityType == EIdentityType.Citizen)
                 .WhereIF(dto.TypeId != null && dto.TypeId == 2, p => p.IdentityType == EIdentityType.Enterprise)
                 .Select(p => new
                 {
                     HotspotCode = p.HotspotId.Substring(SqlFunc.MappingColumn<int>("0"), SqlFunc.MappingColumn<int>("2")),
                     Hour = p.CreationTime.Hour.ToString()
                 })
               .MergeTable();

            var listOrg = Db.Queryable<Hotspot>()
              .LeftJoin(listOrder, (s, p) => s.Id == p.HotspotCode)
              .Where((s, p) => s.Id.Length == 2 && (s.ParentId == null || s.ParentId == ""))
              .GroupBy((s, p) => s.Id).GroupBy((s, p) => s.HotSpotName)
              .GroupBy((s, p) => p.Hour)
              .OrderBy((s, p) => s.Id)
              .Select((s, p) => new
              {
                  count = SqlFunc.AggregateSum(SqlFunc.IIF(p.HotspotCode != null && p.HotspotCode != "", 1, 0)),
                  HotspotCode = s.Id,
                  Hour = SqlFunc.IIF(p.Hour == null || p.Hour == "", "0", p.Hour),
                  HotSpotName = s.HotSpotName
              })
              .MergeTable();

            var dt = await listHour.LeftJoin(listOrg, (x, p) => x.ColumnName.ToString() == p.Hour)
               .OrderBy(x => x.ColumnName)
              .Select((x, p) => new
              {
                  Hour = x.ColumnName.ToString() + ":00 - " + x.ColumnName.ToString() + ":59",
                  p.HotSpotName,
                  p.count
              })
               .ToPivotTableAsync(p => p.HotSpotName, p => p.Hour, p => p.Sum(x => x.count));

            return InitDatatTable(dt, dto.AddColumnName);
        }

        /// <summary>
        /// 受理类型分时统计
        /// </summary>
        /// <param name="dto"></param>
        /// <returns></returns>
        public async Task<object> OrderAcceptanceTime(TimeSharingPagedKeywordRequest dto)
        {
            List<int> dts = new List<int>();
            for (int i = 0; i < 24; i++)
            {
                dts.Add(i);
            }

            var listHour = Db.Reportable(dts).ToQueryable<int>();
            var listOrder = Db.Queryable<Order>()
                 .Where(p => p.CreationTime >= dto.StartTime && p.CreationTime <= dto.EndTime)
                  .WhereIF(dto.TypeId != null && dto.TypeId == 1, p => p.IdentityType == EIdentityType.Citizen)
                 .WhereIF(dto.TypeId != null && dto.TypeId == 2, p => p.IdentityType == EIdentityType.Enterprise)
                 .Select(p => new
                 {
                     AcceptTypeCode = p.AcceptTypeCode,
                     Hour = p.CreationTime.Hour.ToString()
                 })
               .MergeTable();

            var listOrg = Db.Queryable<SystemDicData>()
              .LeftJoin(listOrder, (s, p) => s.DicDataValue == p.AcceptTypeCode)
              .Where((s, p) => s.DicTypeCode == "AcceptType")
              .GroupBy((s, p) => s.DicDataValue).GroupBy((s, p) => s.DicDataName)
              .GroupBy((s, p) => p.Hour)
              .OrderBy((s, p) => s.DicDataValue)
              .Select((s, p) => new
              {
                  count = SqlFunc.AggregateSum(SqlFunc.IIF(p.AcceptTypeCode != null && p.AcceptTypeCode != "", 1, 0)),
                  DicDataValue = s.DicDataValue,
                  Hour = SqlFunc.IIF(p.Hour == null || p.Hour == "", "0", p.Hour),
                  DicDataName = s.DicDataName
              })
              .MergeTable();

            var list = await listHour.LeftJoin(listOrg, (x, p) => x.ColumnName.ToString() == p.Hour)
                //.Where((x,p)=> !string.IsNullOrEmpty(p.DicDataValue))
                .OrderBy((x, p) => x.ColumnName)
               .Select((x, p) => new
               {
                   Hour = x.ColumnName.ToString() + ":00 - " + x.ColumnName.ToString() + ":59",
                   p.DicDataValue,
                   p.count
               })
                .MergeTable()
               .ToPivotTableAsync(p => p.DicDataValue, p => p.Hour, p => p.Sum(x => x.count));

            var col = list.Columns.Contains("Column1");
            if (col)
            {
                list.Columns.Remove("Column1");
            }
            var dtList = list.Clone();

            if (await Db.Queryable<StatisticsPurTypeSatisfied>().Where(x => x.Time >= dto.StartTime && x.Time <= dto.EndTime).AnyAsync())
            {
                // 老系统数据
                var oldData = await Db.Queryable<StatisticsPurTypeSatisfied>()
                    .Where(x => x.Time >= dto.StartTime && x.Time <= dto.EndTime)
                    .GroupBy(x => x.Month)
                    .Select(x => new
                    {
                        Hour = x.Month.ToString() + ":00 - " + x.Month.ToString() + ":59",
                        _1 = SqlFunc.AggregateSum(x.Opinion),
                        _2 = SqlFunc.AggregateSum(x.PeopleHelp),
                        _3 = SqlFunc.AggregateSum(x.PollutionReporting),
                        _10 = SqlFunc.AggregateSum(x.Consult),
                        _15 = SqlFunc.AggregateSum(x.Suggest),
                        _20 = SqlFunc.AggregateSum(x.SeekHelp),
                        _25 = SqlFunc.AggregateSum(x.Praise),
                        _30 = SqlFunc.AggregateSum(x.Report),
                        _35 = SqlFunc.AggregateSum(x.Complaint),
                        _40 = SqlFunc.AggregateSum(x.Rests) + SqlFunc.AggregateSum(x.Invalid),
                        _50 = SqlFunc.AggregateSum(x.Epidemic),
                        _SB = SqlFunc.AggregateSum(x.Declare),
                    }).ToDataTableAsync();

                #region  处理数据

                foreach (DataColumn column in dtList.Columns)
                {
                    if (column.ColumnName != "Hour") column.DataType = typeof(Int64);
                }

                for (int i = 0; i < list.Rows.Count; i++)
                {
                    DataRow newRow = dtList.NewRow();
                    for (int j = 0; j < list.Columns.Count; j++)
                    {
                        if (oldData.Columns[j].ColumnName == "Hour")
                        {
                            newRow[j] = list.Rows[i][j];
                        }
                        else
                        {
                            var num = string.IsNullOrEmpty(list.Rows[i][j].ToString()) ? 0 : int.Parse(list.Rows[i][j].ToString());
                            var oldRow = oldData.Select("Hour ='" + list.Rows[i]["Hour"] + "'").FirstOrDefault();
                            var oldColName = "_" + dtList.Columns[j].ColumnName;
                            var allNum = oldRow == null ? num : oldRow.Field<Int64>(oldColName) + num;
                            newRow[j] = allNum;
                        }
                    }
                    dtList.Rows.Add(newRow);
                }

                #endregion
            }
            return ToDynamicList(dtList);
        }

        /// <summary>
        /// 中心报表统计
        /// </summary>
        /// <param name="StartTime"></param>
        /// <param name="EndTime"></param>
        /// <returns></returns>
        public async Task<DataTable> CenterReportFormsStatistics(DateTime StartTime, DateTime EndTime)
        {
            var listAny = await Db.Queryable<StatisticsCenter>()
                .Where(x => x.Time >= StartTime && x.Time <= EndTime).AnyAsync();
            if (!listAny)
            {
                return null;
            }
            var list = await Db.Queryable<StatisticsCenter>()
                .Where(x => x.Time >= StartTime && x.Time <= EndTime)
                .Select(x => new StatisticsCenter
                {
                    CallNum = SqlFunc.AggregateSum(x.CallNum),
                    FamilyCallNum = SqlFunc.AggregateSum(x.FamilyCallNum),
                    FamilyCallConnNum = SqlFunc.AggregateSum(x.FamilyCallConnNum),
                    CallInConn = SqlFunc.AggregateSum(x.CallInConn),
                    CallInNotConn = SqlFunc.AggregateSum(x.CallInNotConn),
                    CallInIVR = SqlFunc.AggregateSum(x.CallInIVR),
                    CallInQueue = SqlFunc.AggregateSum(x.CallInQueue),
                    VisitAlreadyNum = SqlFunc.AggregateSum(x.VisitAlreadyNum),
                    VisitWaitNum = SqlFunc.AggregateSum(x.VisitWaitNum),
                    SatisfactionSeat = SqlFunc.AggregateAvg(x.SatisfactionSeat),
                    SatisfactionDepartment = SqlFunc.AggregateAvg(x.SatisfactionDepartment),
                    OrderAllNum = SqlFunc.AggregateSum(x.OrderAllNum),
                    OrderNormalNum = SqlFunc.AggregateSum(x.OrderNormalNum),
                    OrderInvalidNum = SqlFunc.AggregateSum(x.OrderInvalidNum),
                    OrderEndNum = SqlFunc.AggregateSum(x.OrderEndNum),
                    OrderWaitNum = SqlFunc.AggregateSum(x.OrderWaitNum),
                    FromAllNum = SqlFunc.AggregateSum(x.FromAllNum),
                    FromPhone = SqlFunc.AggregateSum(x.FromPhone),
                    FromInternet = SqlFunc.AggregateSum(x.FromInternet),
                    FromOther = SqlFunc.AggregateSum(x.FromOther),
                    FromSelfBuild = SqlFunc.AggregateSum(x.FromSelfBuild),
                    FromWeChat = SqlFunc.AggregateSum(x.FromWeChat),
                    FromWeibo = SqlFunc.AggregateSum(x.FromWeibo),
                    FromApp = SqlFunc.AggregateSum(x.FromApp),
                    FromSmartYibin = SqlFunc.AggregateSum(x.FromSmartYibin),
                    FromPlatformZZ = SqlFunc.AggregateSum(x.FromPlatformZZ),
                    FromPlatform12328 = SqlFunc.AggregateSum(x.FromPlatform12328),
                    FromMayorNetizen = SqlFunc.AggregateSum(x.FromMayorNetizen),
                    FromPlatformRMT = SqlFunc.AggregateSum(x.FromPlatformRMT),
                    FromPlatformProvince = SqlFunc.AggregateSum(x.FromPlatformProvince),
                    FromPlatformZMHD = SqlFunc.AggregateSum(x.FromPlatformZMHD),
                    FromPlatformYBS = SqlFunc.AggregateSum(x.FromPlatformYBS),
                    FromPlatformSZHZ = SqlFunc.AggregateSum(x.FromPlatformSZHZ),
                    FromPlatform110 = SqlFunc.AggregateSum(x.FromPlatform110),
                    FromPlatformBBCS = SqlFunc.AggregateSum(x.FromPlatformBBCS),
                    FromPlatformIYB = SqlFunc.AggregateSum(x.FromPlatformIYB)
                })
                .ToDataTableAsync();
            return list;
        }

        /// <summary>
        /// 中心报表统计（受理类型）
        /// </summary>
        /// <param name="StartTime"></param>
        /// <param name="EndTime"></param>
        /// <returns></returns>
        public async Task<DataTable> CenterReportPurTypeStatistics(DateTime StartTime, DateTime EndTime)
        {
            var any = await Db.Queryable<StatisticsPurTypeSatisfied>()
                .Where(x => x.Time >= StartTime && x.Time <= EndTime).AnyAsync();
            if (!any)
            {
                return null;
            }

            var list = await Db.Queryable<StatisticsPurTypeSatisfied>()
                .Where(x => x.Time >= StartTime && x.Time <= EndTime)
                .Select(x => new StatisticsPurTypeSatisfied
                {
                    Total = SqlFunc.AggregateSum(x.Total),
                    Consult = SqlFunc.AggregateSum(x.Consult),
                    Suggest = SqlFunc.AggregateSum(x.Suggest),
                    SeekHelp = SqlFunc.AggregateSum(x.SeekHelp),
                    Praise = SqlFunc.AggregateSum(x.Praise),
                    Report = SqlFunc.AggregateSum(x.Report),
                    Complaint = SqlFunc.AggregateSum(x.Complaint),
                    Rests = SqlFunc.AggregateSum(x.Rests),
                    PollutionReporting = SqlFunc.AggregateSum(x.PollutionReporting),
                    PeopleHelp = SqlFunc.AggregateSum(x.PeopleHelp),
                    Opinion = SqlFunc.AggregateSum(x.Opinion),
                    Epidemic = SqlFunc.AggregateSum(x.Epidemic),
                    Declare = SqlFunc.AggregateSum(x.Declare),
                    Invalid = SqlFunc.AggregateSum(x.Invalid)
                })
                .ToDataTableAsync();
            return list;
        }

        /// <summary>
        /// 中心报表统计（部门办件）
        /// </summary>
        /// <param name="StartTime"></param>
        /// <param name="EndTime"></param>
        /// <param name="Type">市级部门 区县部门</param>
        /// <returns></returns>
        public async Task<DataTable> CenterReportDepartStatistics(DateTime StartTime, DateTime EndTime, string Type)
        {
            var any = await Db.Queryable<StatisticsDepart>()
                .LeftJoin<SystemOrganize>((x, so) => x.DepartmentId == so.oldBmid)
                .Where(x => x.Time >= StartTime && x.Time <= EndTime && x.Type == Type).AnyAsync();
            if (!any)
            {
                return null;
            }


            var list = await Db.Queryable<StatisticsDepart>()
                .LeftJoin<SystemOrganize>((x, so) => x.DepartmentId == so.oldBmid)
                .Where(x => x.Time >= StartTime && x.Time <= EndTime && x.Type == Type)
                .GroupBy((x, so) => new
                {
                    OrgCode = so.Id.Substring(SqlFunc.MappingColumn<int>("0"), SqlFunc.MappingColumn<int>("6")),
                })
                .Select((x, so) => new
                {
                    OrgCode = so.Id.Substring(SqlFunc.MappingColumn<int>("0"), SqlFunc.MappingColumn<int>("6")),
                    OrderAllNum = SqlFunc.AggregateSum(x.OrderAllNum)
                })
                .MergeTable()
                .LeftJoin<SystemOrganize>((temp, so) => temp.OrgCode == so.Id)
                .Select((temp, so) => new
                {
                    OrgCode = temp.OrgCode,
                    Name = so.Name,
                    OrderAllNum = temp.OrderAllNum
                })
                .ToDataTableAsync();
            return list;
        }

        /// <summary>
        /// 中心报表统计（新增加的）
        /// </summary>
        /// <param name="StartTime"></param>
        /// <param name="EndTime"></param>
        /// <returns></returns>
        public async Task<DataTable> CenterReportNewStatistics(DateTime StartTime, DateTime EndTime)
        {
            var any = await Db.Queryable<StatisticsBaseInfo>()
                .Where(x => x.AddDate >= StartTime && x.AddDate <= EndTime).AnyAsync();

            if (!any)
            {
                return null;
            }
            var list = await Db.Queryable<StatisticsBaseInfo>()
                .Where(x => x.AddDate >= StartTime && x.AddDate <= EndTime)
                .Select(x => new
                {
                    // 回访情况
                    AllCount = SqlFunc.IIF(string.IsNullOrEmpty(x.AppTypeName) == false, 1, 0),                                                                                         // 总体条数
                    SatisfactionCount = SqlFunc.IIF(string.IsNullOrEmpty(x.AppTypeName) == false && x.AppTypeName.Contains("不满意") == true, 1, 0),                                    // 总体不满意条数
                    DepartCount = SqlFunc.IIF(string.IsNullOrEmpty(x.AppTypeName) == false && x.DepartID != 23, 1, 0),                                                                  // 部门总体条数
                    DepartSatisfactionCount = SqlFunc.IIF(string.IsNullOrEmpty(x.AppTypeName) == false && x.DepartID != 23 && x.AppTypeName.Contains("不满意") == true, 1, 0),          // 部门总体不满意条数
                    CenterCount = SqlFunc.IIF(string.IsNullOrEmpty(x.AppTypeName) == false && x.DepartID == 23, 1, 0),                                                                  // 中心总体条数
                    CenterSatisfactionCount = SqlFunc.IIF(string.IsNullOrEmpty(x.AppTypeName) == false && x.DepartID == 23 && x.AppTypeName.Contains("不满意") == true, 1, 0),          // 中心总体不满意条数
                    CityCount = SqlFunc.IIF(string.IsNullOrEmpty(x.AppTypeName) == false && x.TypeName == "市直部门", 1, 0),                                                            // 市级总体条数
                    CitySatisfactionCount = SqlFunc.IIF(string.IsNullOrEmpty(x.AppTypeName) == false && x.TypeName == "市直部门" && x.AppTypeName.Contains("不满意") == true, 1, 0),    // 市级总体不满意条数
                    CountyCount = SqlFunc.IIF(string.IsNullOrEmpty(x.AppTypeName) == false && x.TypeName == "区县部门", 1, 0),                                                          // 区县总体条数
                    CountySatisfactionCount = SqlFunc.IIF(string.IsNullOrEmpty(x.AppTypeName) == false && x.TypeName == "区县部门" && x.AppTypeName.Contains("不满意") == true, 1, 0),   // 区县总体不满意条数

                    // 信件情况
                    OrderAllCount = 1,                                                   // 来件总计
                    OrderAlready = SqlFunc.IIF(x.StateFlag == "办理完成", 1, 0),         // 已办件数
                    OrderWait = SqlFunc.IIF(x.StateFlag != "办理完成", 1, 0),            // 在办件数
                    OrderCenterCount = SqlFunc.IIF(x.DepartID == 23, 1, 0),              // 中心受理
                    OrderCityCount = SqlFunc.IIF(x.TypeName == "市直部门", 1, 0),        // 市直受理
                    OrderCountyCount = SqlFunc.IIF(x.TypeName == "区县部门", 1, 0),      // 区县受理

                    // 按时办结情况
                    CompleteOnTime = SqlFunc.IIF(x.CompleteOnTime == 1, 1, 0),           // 按时办结
                    DepartCompleteOnTime = SqlFunc.IIF(x.DepartID != 23 && x.CompleteOnTime == 1, 1, 0),           // 部门按时办结
                    CityCompleteOnTime = SqlFunc.IIF(x.TypeName == "市直部门" && x.CompleteOnTime == 1, 1, 0),     // 市级部门按时办结
                    CountyCompleteOnTime = SqlFunc.IIF(x.TypeName == "区县部门" && x.CompleteOnTime == 1, 1, 0),   // 县（区）按时办结
                    CenterCompleteOnTime = SqlFunc.IIF(x.DepartID == 23 && x.CompleteOnTime == 1, 1, 0),           // 12345中心按时办结
                    DepartAlready = SqlFunc.IIF(x.StateFlag == "办理完成" && x.DepartID != 23, 1, 0),              // 部门已办
                    CenterAlready = SqlFunc.IIF(x.StateFlag == "办理完成" && x.DepartID == 23, 1, 0),              // 中心已办
                    CityAlready = SqlFunc.IIF(x.StateFlag == "办理完成" && x.TypeName == "市直部门", 1, 0),        // 市直已办
                    CountyAlready = SqlFunc.IIF(x.StateFlag == "办理完成" && x.TypeName == "区县部门", 1, 0),      // 区县已办

                    // 办理时效情况
                    OrderWorkTime = SqlFunc.IIF(x.StateFlag == "办理完成", x.CallHandToEndWork, 0),                // 总办理时长
                    CityWorkTime = SqlFunc.IIF(x.TypeName == "市直部门", x.CallHandToEndWork, 0),                  // 市直办理时长
                    CountyWorkTime = SqlFunc.IIF(x.TypeName == "区县部门", x.CallHandToEndWork, 0),                // 区县办理时长
                    CenterWorkTime = SqlFunc.IIF(x.DepartID == 23, x.CallHandToEndWork, 0),                        // 中心办理时长

                    // 企业服务办件情况
                    EnterpriseAllCount = SqlFunc.IIF(x.PersonnelType == "企业", 1, 0),
                    EnterpriseAlready = SqlFunc.IIF(x.PersonnelType == "企业" && x.StateFlag == "办理完成", 1, 0),                          // 已办信件
                    EnterpriseWait = SqlFunc.IIF(x.PersonnelType == "企业" && x.StateFlag != "办理完成", 1, 0),                             // 在办信件
                    EnterpriseVisit = SqlFunc.IIF(x.PersonnelType == "企业" && string.IsNullOrWhiteSpace(x.AppTypeName) == false, 1, 0),    // 回访总量
                    EnterpriseSatisfaction = SqlFunc.IIF(x.PersonnelType == "企业" && x.AppTypeName.Contains("不满意") == false, 1, 0),     // 满意量
                    EnterpriseCenter = SqlFunc.IIF(x.PersonnelType == "企业" && x.DepartID == 23, 1, 0),                                    // 12345直办件
                    EnterpriseCity = SqlFunc.IIF(x.PersonnelType == "企业" && x.TypeName == "市直部门", 1, 0),                              // 市级部门办件
                    EnterpriseCounty = SqlFunc.IIF(x.PersonnelType == "企业" && x.TypeName == "区县部门", 1, 0),                            // 县（区）办件
                    EnterpriseCitySatisfaction = SqlFunc.IIF(x.PersonnelType == "企业" && x.TypeName == "市直部门" && x.AppTypeName.Contains("不满意") == false, 1, 0),     // 市级部门满意量
                    EnterpriseCountySatisfaction = SqlFunc.IIF(x.PersonnelType == "企业" && x.TypeName == "区县部门" && x.AppTypeName.Contains("不满意") == false, 1, 0),   // 县（区）满意量
                    EnterpriseWorkTime = SqlFunc.IIF(x.PersonnelType == "企业" && x.StateFlag == "办理完成", x.CallHandToEndWork, 0),        // 企业信件办理平均时长（工作日）
                    EnterpriseCityWorkTime = SqlFunc.IIF(x.PersonnelType == "企业" && x.TypeName == "市直部门", x.CallHandToEndWork, 0),     // 市级部门企业信件平均时长（工作日）		
                    EnterpriseCountyWorkTime = SqlFunc.IIF(x.PersonnelType == "企业" && x.TypeName == "区县部门", x.CallHandToEndWork, 0),   // 县（区）企业信件平均时长（工作日）
                    EnterpriseCenterWorkTime = SqlFunc.IIF(x.PersonnelType == "企业" && x.DepartID == 23, x.CallHandToEndWork, 0),           // 12345直办企业信件平均时长（工作日）
                    EnterpriseConsult = SqlFunc.IIF(x.PersonnelType == "企业" && x.PurName == "咨询", 1, 0),           // 企业咨询
                    EnterpriseSeekHelp = SqlFunc.IIF(x.PersonnelType == "企业" && x.PurName == "求助", 1, 0),          // 企业求助
                    EnterpriseComplaint = SqlFunc.IIF(x.PersonnelType == "企业" && x.PurName == "投诉", 1, 0),         // 企业投诉
                    EnterpriseReport = SqlFunc.IIF(x.PersonnelType == "企业" && x.PurName == "举报", 1, 0),            // 企业举报
                    EnterpriseSuggest = SqlFunc.IIF(x.PersonnelType == "企业" && x.PurName == "建议", 1, 0),           // 企业建议
                    EnterprisePraise = SqlFunc.IIF(x.PersonnelType == "企业" && x.PurName == "表扬", 1, 0),            // 企业表扬
                    EnterpriseOpinion = SqlFunc.IIF(x.PersonnelType == "企业" && x.PurName == "意见", 1, 0)            // 企业意见
                })
                .MergeTable()
                .Select(x => new
                {
                    // 回访情况
                    AllCount = SqlFunc.AggregateSum(x.AllCount),
                    SatisfactionCount = SqlFunc.AggregateSum(x.SatisfactionCount),
                    DepartCount = SqlFunc.AggregateSum(x.DepartCount),
                    DepartSatisfactionCount = SqlFunc.AggregateSum(x.DepartSatisfactionCount),
                    CenterCount = SqlFunc.AggregateSum(x.CenterCount),
                    CenterSatisfactionCount = SqlFunc.AggregateSum(x.CenterSatisfactionCount),
                    CityCount = SqlFunc.AggregateSum(x.CityCount),
                    CitySatisfactionCount = SqlFunc.AggregateSum(x.CitySatisfactionCount),
                    CountyCount = SqlFunc.AggregateSum(x.CountyCount),
                    CountySatisfactionCount = SqlFunc.AggregateSum(x.CountySatisfactionCount),

                    // 信件情况
                    OrderAllCount = SqlFunc.AggregateSum(x.OrderAllCount),
                    OrderAlready = SqlFunc.AggregateSum(x.OrderAlready),
                    OrderWait = SqlFunc.AggregateSum(x.OrderWait),
                    OrderCenterCount = SqlFunc.AggregateSum(x.OrderCenterCount),
                    OrderCityCount = SqlFunc.AggregateSum(x.OrderCityCount),
                    OrderCountyCount = SqlFunc.AggregateSum(x.OrderCountyCount),

                    // 按时办结情况
                    CompleteOnTime = SqlFunc.AggregateSum(x.CompleteOnTime),
                    DepartCompleteOnTime = SqlFunc.AggregateSum(x.DepartCompleteOnTime),
                    CityCompleteOnTime = SqlFunc.AggregateSum(x.CityCompleteOnTime),
                    CountyCompleteOnTime = SqlFunc.AggregateSum(x.CountyCompleteOnTime),
                    CenterCompleteOnTime = SqlFunc.AggregateSum(x.CenterCompleteOnTime),
                    DepartAlready = SqlFunc.AggregateSum(x.DepartAlready),
                    CenterAlready = SqlFunc.AggregateSum(x.CenterAlready),
                    CityAlready = SqlFunc.AggregateSum(x.CityAlready),
                    CountyAlready = SqlFunc.AggregateSum(x.CountyAlready),

                    // 办理时效情况
                    OrderWorkTime = SqlFunc.AggregateSum(x.OrderWorkTime),
                    CityWorkTime = SqlFunc.AggregateSum(x.CityWorkTime),
                    CountyWorkTime = SqlFunc.AggregateSum(x.CountyWorkTime),
                    CenterWorkTime = SqlFunc.AggregateSum(x.CenterWorkTime),

                    // 企业服务办件情况
                    EnterpriseAllCount = SqlFunc.AggregateSum(x.EnterpriseAllCount),
                    EnterpriseAlready = SqlFunc.AggregateSum(x.EnterpriseAlready),
                    EnterpriseWait = SqlFunc.AggregateSum(x.EnterpriseWait),
                    EnterpriseVisit = SqlFunc.AggregateSum(x.EnterpriseVisit),
                    EnterpriseSatisfaction = SqlFunc.AggregateSum(x.EnterpriseSatisfaction),
                    EnterpriseCenter = SqlFunc.AggregateSum(x.EnterpriseCenter),
                    EnterpriseCity = SqlFunc.AggregateSum(x.EnterpriseCity),
                    EnterpriseCounty = SqlFunc.AggregateSum(x.EnterpriseCounty),
                    EnterpriseCitySatisfaction = SqlFunc.AggregateSum(x.EnterpriseCitySatisfaction),
                    EnterpriseCountySatisfaction = SqlFunc.AggregateSum(x.EnterpriseCountySatisfaction),
                    EnterpriseWorkTime = SqlFunc.AggregateSum(x.EnterpriseWorkTime),
                    EnterpriseCityWorkTime = SqlFunc.AggregateSum(x.EnterpriseCityWorkTime),
                    EnterpriseCountyWorkTime = SqlFunc.AggregateSum(x.EnterpriseCountyWorkTime),
                    EnterpriseCenterWorkTime = SqlFunc.AggregateSum(x.EnterpriseCenterWorkTime),
                    EnterpriseConsult = SqlFunc.AggregateSum(x.EnterpriseConsult),
                    EnterpriseSeekHelp = SqlFunc.AggregateSum(x.EnterpriseSeekHelp),
                    EnterpriseComplaint = SqlFunc.AggregateSum(x.EnterpriseComplaint),
                    EnterpriseReport = SqlFunc.AggregateSum(x.EnterpriseReport),
                    EnterpriseSuggest = SqlFunc.AggregateSum(x.EnterpriseSuggest),
                    EnterprisePraise = SqlFunc.AggregateSum(x.EnterprisePraise),
                    EnterpriseOpinion = SqlFunc.AggregateSum(x.EnterpriseOpinion)
                })
                .MergeTable()
                .Select(x => new
                {
                    AllCount = x.AllCount,
                    // 回访情况
                    SatisfactionRate = x.AllCount > 0 ? SqlFunc.Round((decimal)(x.AllCount - x.SatisfactionCount) * 100 / (decimal)x.AllCount, 2) : 0,                     //总体满意率
                    DepartSatisfactionRate = x.DepartCount > 0 ? SqlFunc.Round((decimal)(x.DepartCount - x.DepartSatisfactionCount) * 100 / (decimal)x.DepartCount, 2) : 0,   //部门满意率
                    CenterSatisfactionRate = x.CenterCount > 0 ? SqlFunc.Round((decimal)(x.CenterCount - x.CenterSatisfactionCount) * 100 / (decimal)x.CenterCount, 2) : 0,   //12345中心满意率
                    CitySatisfactionRate = x.CityCount > 0 ? SqlFunc.Round((decimal)(x.CityCount - x.CitySatisfactionCount) * 100 / (decimal)x.CityCount, 2) : 0,           //市级部门满意率
                    CountySatisfactionRate = x.CountyCount > 0 ? SqlFunc.Round((decimal)(x.CountyCount - x.CountySatisfactionCount) * 100 / (decimal)x.CountyCount, 2) : 0,   //县（区）满意率
                    SatisfactionCount = x.SatisfactionCount,                                                                                          //不满意件
                    CenterSatisfactionCount = x.CenterSatisfactionCount,                                                                              //市级部门不满意件
                    CountySatisfactionCount = x.CountySatisfactionCount,
                    CitySatisfactionCount = x.CitySatisfactionCount,                                                                           //县（区）不满意件
                    CityCount = x.CityCount,
                    CountyCount = x.CountyCount,

                    // 信件情况
                    OrderAllCount = x.OrderAllCount,                                                                    // 来件总数
                    OrderAlready = x.OrderAlready,                                                                      // 已办件数
                    OrderWait = x.OrderWait,                                                                            // 在办件数
                    OrderAlreadyRate = x.OrderAllCount > 0 ? SqlFunc.Round((decimal)(x.OrderAlready) * 100 / (decimal)x.OrderAllCount, 2) : 0,    //总体办结率
                    OrderCityCount = x.OrderCityCount,                                                                  //市级部门受理
                    OrderCountyCount = x.OrderCountyCount,                                                              //县（区）受理
                    OrderCenterCount = x.OrderCenterCount,                                                              //12345中心受理
                    //OrderCityRate = SqlFunc.Round((decimal)(x.OrderCityCount) * 100 / (decimal)x.OrderAllCount, 2),     //市级部门受理占比                                                        
                    //OrderCountyRate = SqlFunc.Round((decimal)(x.OrderCountyCount) * 100 / (decimal)x.OrderAllCount, 2), //县（区）受理占比                                                        
                    //OrderCenterRate = SqlFunc.Round((decimal)(x.OrderCenterCount) * 100 / (decimal)x.OrderAllCount, 2), //12345中心受理占比

                    // 按时办结情况
                    CompleteOnTime = x.CompleteOnTime,
                    DepartCompleteOnTime = x.DepartCompleteOnTime,
                    CityCompleteOnTime = x.CityCompleteOnTime,
                    CountyCompleteOnTime = x.CountyCompleteOnTime,
                    CenterCompleteOnTime = x.CenterCompleteOnTime,
                    CityAlready = x.CityAlready,
                    CountyAlready = x.CountyAlready,
                    CenterAlready = x.CenterAlready,
                    //CompleteOnTimeRate = SqlFunc.Round((decimal)(x.CompleteOnTime) * 100 / (decimal)x.OrderAlready, 2),                 //总体按时办结率
                    //DepartCompleteOnTimeRate = SqlFunc.Round((decimal)(x.DepartCompleteOnTime) * 100 / (decimal)x.DepartAlready, 2),    //部门按时办结率
                    //CityCompleteOnTimeRate = SqlFunc.Round((decimal)(x.CityCompleteOnTime) * 100 / (decimal)x.CityAlready, 2),          //市级部门按时办结率
                    //CountyCompleteOnTimeRate = SqlFunc.Round((decimal)(x.CountyCompleteOnTime) * 100 / (decimal)x.CountyAlready, 2),    //县（区）按时办结率
                    //CenterCompleteOnTimeRate = SqlFunc.Round((decimal)(x.CenterCompleteOnTime) * 100 / (decimal)x.CenterAlready, 2),    //12345中心按时办结率

                    // 办理时效情况
                    OrderWorkTime = x.OrderWorkTime,
                    CityWorkTime = x.CityWorkTime,
                    CountyWorkTime = x.CountyWorkTime,
                    CenterWorkTime = x.CenterWorkTime,
                    OrderWorkTimeRate = x.OrderAlready > 0 ? SqlFunc.Round((decimal)(x.OrderWorkTime) / (decimal)x.OrderAlready / 60 / 8, 2) : 0,          //信件办理平均时长（工作日）
                    CityWorkTimeRate = x.CityAlready > 0 ? SqlFunc.Round((decimal)(x.CityWorkTime) / (decimal)x.CityAlready / 60 / 8, 2) : 0,             //市级部门平均时长（工作日）
                    CountyWorkTimeRate = x.CountyAlready > 0 ? SqlFunc.Round((decimal)(x.CountyWorkTime) / (decimal)x.CountyAlready / 60 / 8, 2) : 0,       //县（区）平均时长（工作日）
                    CenterWorkTimeRate = x.CenterAlready > 0 ? SqlFunc.Round((decimal)(x.CenterWorkTime) / (decimal)x.CenterAlready / 60 / 8, 2) : 0,        //12345中心平均时长（工作日）

                    // 企业服务办件情况
                    EnterpriseAllCount = x.EnterpriseAllCount,
                    EnterpriseAlready = x.EnterpriseAlready,                                                                                            //已办信件
                    EnterpriseWait = x.EnterpriseWait,                                                                                                  //在办信件
                    EnterpriseVisit = x.EnterpriseVisit,                                                                                                //回访总量
                    EnterpriseSatisfactionRate = x.EnterpriseVisit > 0 ? SqlFunc.Round((decimal)(x.EnterpriseSatisfaction) * 100 / (decimal)x.EnterpriseVisit, 2) : 0,              //总满意率
                    EnterpriseCenter = x.EnterpriseCenter,                                                                                              //12345直办件
                    EnterpriseCity = x.EnterpriseCity,                                                                                                  //市级部门办件
                    EnterpriseCounty = x.EnterpriseCounty,                                                                                              //县（区）办件
                    EnterpriseDisSatisfaction = x.EnterpriseVisit - x.EnterpriseSatisfaction,                                                           //不满意信件总量
                    EnterpriseCitySatisfaction = x.EnterpriseCitySatisfaction,
                    EnterpriseCitySatisfactionRate = x.EnterpriseCity > 0 ? SqlFunc.Round((decimal)(x.EnterpriseCitySatisfaction) * 100 / (decimal)x.EnterpriseCity, 2) : 0,       //市级部门满意率
                    EnterpriseCountySatisfaction = x.EnterpriseCountySatisfaction,
                    EnterpriseCountySatisfactionRate = x.EnterpriseCounty > 0 ? SqlFunc.Round((decimal)(x.EnterpriseCountySatisfaction) * 100 / (decimal)x.EnterpriseCounty, 2) : 0, //县（区）满意率
                    EnterpriseWorkTime = x.EnterpriseWorkTime,
                    EnterpriseCityWorkTime = x.EnterpriseCityWorkTime,
                    EnterpriseCountyWorkTime = x.EnterpriseCountyWorkTime,
                    EnterpriseCenterWorkTime = x.EnterpriseCenterWorkTime,
                    //EnterpriseWorkTime = SqlFunc.Round((decimal)(x.EnterpriseWorkTime) / (decimal)x.EnterpriseAlready / 60 / 8, 2),                     //企业信件办理平均时长（工作日）	
                    //EnterpriseCityWorkTime = SqlFunc.Round((decimal)(x.EnterpriseCityWorkTime) / (decimal)x.EnterpriseCenter / 60 / 8, 2),              //市级部门企业信件平均时长（工作日）
                    //EnterpriseCountyWorkTime = SqlFunc.Round((decimal)(x.EnterpriseCountyWorkTime) / (decimal)x.EnterpriseCounty / 60 / 8, 2),          //县（区）企业信件平均时长（工作日）
                    //EnterpriseCenterWorkTime = SqlFunc.Round((decimal)(x.EnterpriseCenterWorkTime) / (decimal)x.EnterpriseCenter / 60 / 8, 2),          //12345直办企业信件平均时长（工作日）
                    EnterpriseConsult = x.EnterpriseConsult,                                                                                            //咨询
                    EnterpriseSeekHelp = x.EnterpriseSeekHelp,                                                                                          //求助
                    EnterpriseComplaint = x.EnterpriseComplaint,                                                                                        //投诉
                    EnterpriseReport = x.EnterpriseReport,                                                                                              //举报
                    EnterpriseSuggest = x.EnterpriseSuggest,                                                                                            //建议
                    EnterprisePraise = x.EnterprisePraise,                                                                                              //表扬
                    EnterpriseOpinion = x.EnterprisePraise                                                                                              //意见
                })
                .ToDataTableAsync();
            return list;
        }

        public static List<dynamic> ToDynamicList(DataTable dt)
        {
            List<dynamic> dynamicList = new List<dynamic>();

            foreach (DataRow row in dt.Rows)
            {
                dynamic dynamicObj = new ExpandoObject();
                var expandoDict = (IDictionary<string, object>)dynamicObj;
                foreach (DataColumn column in dt.Columns)
                {
                    expandoDict[column.ColumnName] = row[column];
                }
                dynamicList.Add(dynamicObj);
            }

            return dynamicList;
        }

        /// <summary>
        /// 受理类型分时统计---导出
        /// </summary>
        /// <param name="dto"></param>
        /// <returns></returns>
        public async Task<DataTable> OrderAcceptanceTimeExport(TimeSharingPagedKeywordRequest dto, List<Kv> title)
        {
            List<int> dts = new();
            for (int i = 0; i < 24; i++)
            {
                dts.Add(i);
            }

            var listHour = Db.Reportable(dts).ToQueryable<int>();
            var listOrder = Db.Queryable<Order>()
                 .Where(p => p.CreationTime >= dto.StartTime && p.CreationTime <= dto.EndTime)
                   .WhereIF(dto.TypeId != null && dto.TypeId == 1, p => p.IdentityType == EIdentityType.Citizen)
                 .WhereIF(dto.TypeId != null && dto.TypeId == 2, p => p.IdentityType == EIdentityType.Enterprise)
                 .Select(p => new
                 {
                     AcceptTypeCode = p.AcceptTypeCode,
                     Hour = p.CreationTime.Hour.ToString()
                 })
               .MergeTable();

            var listOrg = Db.Queryable<SystemDicData>()
              .LeftJoin(listOrder, (s, p) => s.DicDataValue == p.AcceptTypeCode)
              .Where((s, p) => s.DicTypeCode == "AcceptType")
              .GroupBy((s, p) => s.DicDataValue).GroupBy((s, p) => s.DicDataName)
              .GroupBy((s, p) => p.Hour)
              .OrderBy((s, p) => s.DicDataValue)
              .Select((s, p) => new
              {
                  count = SqlFunc.AggregateSum(SqlFunc.IIF(p.AcceptTypeCode != null && p.AcceptTypeCode != "", 1, 0)),
                  DicDataValue = s.DicDataValue,
                  Hour = SqlFunc.IIF(p.Hour == null || p.Hour == "", "0", p.Hour),
                  DicDataName = s.DicDataName
              })
              .MergeTable();

            var list = await listHour.LeftJoin(listOrg, (x, p) => x.ColumnName.ToString() == p.Hour)
               .OrderBy(x => x.ColumnName)
              .Select((x, p) => new
              {
                  Hour = x.ColumnName.ToString() + ":00 - " + x.ColumnName.ToString() + ":59",
                  p.DicDataName,
                  p.count
              })
               .ToPivotTableAsync(p => p.DicDataName, p => p.Hour, p => p.Sum(x => x.count));
            var col1 = list.Columns.Contains("Column1");
            if (col1)
            {
                list.Columns.Remove("Column1");
            }
            var dtList = list.Clone();

            if (await Db.Queryable<StatisticsPurTypeSatisfied>().Where(x => x.Time >= dto.StartTime && x.Time <= dto.EndTime).AnyAsync())
            {
                // 老系统数据
                var oldData = await Db.Queryable<StatisticsPurTypeSatisfied>()
                    .Where(x => x.Time >= dto.StartTime && x.Time <= dto.EndTime)
                    .GroupBy(x => x.Month)
                    .Select(x => new
                    {
                        Hour = x.Month.ToString() + ":00 - " + x.Month.ToString() + ":59",
                        _1 = SqlFunc.AggregateSum(x.Opinion),
                        _2 = SqlFunc.AggregateSum(x.PeopleHelp),
                        _3 = SqlFunc.AggregateSum(x.PollutionReporting),
                        _10 = SqlFunc.AggregateSum(x.Consult),
                        _15 = SqlFunc.AggregateSum(x.Suggest),
                        _20 = SqlFunc.AggregateSum(x.SeekHelp),
                        _25 = SqlFunc.AggregateSum(x.Praise),
                        _30 = SqlFunc.AggregateSum(x.Report),
                        _35 = SqlFunc.AggregateSum(x.Complaint),
                        _40 = SqlFunc.AggregateSum(x.Rests) + SqlFunc.AggregateSum(x.Invalid),
                        _50 = SqlFunc.AggregateSum(x.Epidemic),
                        _SB = SqlFunc.AggregateSum(x.Declare),
                    }).ToDataTableAsync();

                #region  处理数据

                foreach (var item in title)
                {
                    var isColumn = oldData.Columns.Contains("_" + item.Key);
                    if (isColumn)
                    {
                        oldData.Columns["_" + item.Key].ColumnName = item.Value;
                    }
                }

                foreach (DataColumn col in dtList.Columns)
                {
                    if (col.ColumnName != "Hour") col.DataType = typeof(Int64);
                }

                for (int i = 0; i < list.Rows.Count; i++)
                {
                    DataRow newRow = dtList.NewRow();
                    for (int j = 0; j < list.Columns.Count; j++)
                    {
                        if (oldData.Columns[j].ColumnName == "Hour")
                        {
                            newRow[j] = list.Rows[i][j];
                        }
                        else
                        {
                            var num = string.IsNullOrEmpty(list.Rows[i][j].ToString()) ? 0 : int.Parse(list.Rows[i][j].ToString());
                            var oldRow = oldData.Select("Hour ='" + list.Rows[i]["Hour"] + "'").FirstOrDefault();
                            //var oldColName = "_" + dtList.Columns[j].ColumnName;
                            var allNum = oldRow == null ? num : oldRow.Field<Int64>(dtList.Columns[j].ColumnName) + num;
                            newRow[j] = allNum;
                        }
                    }
                    dtList.Rows.Add(newRow);
                }

                #endregion
            }
            return InitDatatTable(dtList, dto.AddColumnName);
        }

        /// <summary>
        /// 信件来源分时统计
        /// </summary>
        /// <param name="dto"></param>
        /// <returns></returns>
        public async Task<object> OrderSourceTime(TimeSharingPagedKeywordRequest dto)
        {
            List<int> dts = new List<int>();
            for (int i = 0; i < 24; i++)
            {
                dts.Add(i);
            }

            var listHour = Db.Reportable(dts).ToQueryable<int>();
            var listOrder = Db.Queryable<Order>()
                 .Where(p => p.CreationTime >= dto.StartTime && p.CreationTime <= dto.EndTime)
                  .WhereIF(dto.TypeId != null && dto.TypeId == 1, p => p.IdentityType == EIdentityType.Citizen)
                 .WhereIF(dto.TypeId != null && dto.TypeId == 2, p => p.IdentityType == EIdentityType.Enterprise)
                 .Select(p => new
                 {
                     SourceChannelCode = p.SourceChannelCode,
                     Hour = p.CreationTime.Hour.ToString()
                 })
               .MergeTable();

            var listOrg = Db.Queryable<SystemDicData>()
              .LeftJoin(listOrder, (s, p) => s.DicDataValue == p.SourceChannelCode)
              .Where((s, p) => s.DicTypeCode == "SourceChannel")
              .GroupBy((s, p) => new
              {
                  DicDataValue = s.DicDataValue,
                  DicDataName = s.DicDataName,
                  Hour = p.Hour,
              })
              .OrderBy((s, p) => s.DicDataValue)
              .Select((s, p) => new
              {
                  Count = SqlFunc.AggregateSum(SqlFunc.IIF(p.SourceChannelCode != null && p.SourceChannelCode != "", 1, 0)),
                  DicDataValue = s.DicDataValue,
                  Hour = SqlFunc.IIF(p.Hour == null || p.Hour == "", "0", p.Hour),
                  DicDataName = s.DicDataName
              })
              .MergeTable();

            var list = await listHour.LeftJoin(listOrg, (x, p) => x.ColumnName.ToString() == p.Hour)
                .OrderBy(x => x.ColumnName)
               .Select((x, p) => new
               {
                   Hour = x.ColumnName.ToString() + ":00 - " + x.ColumnName.ToString() + ":59",
                   p.DicDataValue,
                   p.Count
               })
                .ToPivotListAsync(p => p.DicDataValue, p => p.Hour, p => p.Sum(x => x.Count));
            return list;

        }

        /// <summary>
        /// 信件来源分时统计---导出
        /// </summary>
        /// <param name="dto"></param>
        /// <returns></returns>
        public async Task<DataTable> OrderSourceTimeExport(TimeSharingPagedKeywordRequest dto)
        {
            List<int> dts = new();
            for (int i = 0; i < 24; i++)
            {
                dts.Add(i);
            }

            var listHour = Db.Reportable(dts).ToQueryable<int>();
            var listOrder = Db.Queryable<Order>()
                 .Where(p => p.CreationTime >= dto.StartTime && p.CreationTime <= dto.EndTime)
                  .WhereIF(dto.TypeId != null && dto.TypeId == 1, p => p.IdentityType == EIdentityType.Citizen)
                 .WhereIF(dto.TypeId != null && dto.TypeId == 2, p => p.IdentityType == EIdentityType.Enterprise)
                 .Select(p => new
                 {
                     SourceChannelCode = p.SourceChannelCode,
                     Hour = p.CreationTime.Hour.ToString()
                 })
               .MergeTable();

            var listOrg = Db.Queryable<SystemDicData>()
              .LeftJoin(listOrder, (s, p) => s.DicDataValue == p.SourceChannelCode)
              .Where((s, p) => s.DicTypeCode == "SourceChannel")
              .GroupBy((s, p) => s.DicDataValue).GroupBy((s, p) => s.DicDataName)
              .GroupBy((s, p) => p.Hour)
              .OrderBy((s, p) => s.DicDataValue)
              .Select((s, p) => new
              {
                  count = SqlFunc.AggregateSum(SqlFunc.IIF(p.SourceChannelCode != null && p.SourceChannelCode != "", 1, 0)),
                  DicDataValue = s.DicDataValue,
                  Hour = SqlFunc.IIF(p.Hour == null || p.Hour == "", "0", p.Hour),
                  DicDataName = s.DicDataName
              })
              .MergeTable();

            var dt = await listHour.LeftJoin(listOrg, (x, p) => x.ColumnName.ToString() == p.Hour)
               .OrderBy(x => x.ColumnName)
              .Select((x, p) => new
              {
                  Hour = x.ColumnName.ToString() + ":00 - " + x.ColumnName.ToString() + ":59",
                  p.DicDataName,
                  p.count
              })
               .ToPivotTableAsync(p => p.DicDataName, p => p.Hour, p => p.Sum(x => x.count));
            return InitDatatTable(dt, dto.AddColumnName);
        }

        /// <summary>
        /// 信件来源统计
        /// </summary>
        /// <param name="dto"></param>
        /// <returns></returns>
        public async Task<object> OrderSource(QueryOrderSourceRequest dto)
        {
            var listOrder = Db.Queryable<Order>()
                 .Where(p => p.CreationTime >= dto.StartTime && p.CreationTime <= dto.EndTime)
                 .WhereIF(dto.TypeId != null && dto.TypeId == 1, p => p.IdentityType == EIdentityType.Citizen)
                 .WhereIF(dto.TypeId != null && dto.TypeId == 2, p => p.IdentityType == EIdentityType.Enterprise)
                 .Select(p => new
                 {
                     SourceChannelCode = p.SourceChannelCode,
                     Hour = p.CreationTime.ToString("yyyy-MM-dd")
                 })
               .MergeTable();

            var listOrg = await Db.Queryable<SystemDicData>()
              .LeftJoin(listOrder, (s, p) => s.DicDataValue == p.SourceChannelCode)
              .Where((s, p) => s.DicTypeCode == "SourceChannel")
              .GroupBy((s, p) => s.DicDataValue).GroupBy((s, p) => s.DicDataName)
              .GroupBy((s, p) => p.Hour)
              .OrderBy((s, p) => p.Hour)
              .Select((s, p) => new
              {
                  Hour = SqlFunc.IIF(p.Hour == null || p.Hour == "", "0", p.Hour),
                  count = SqlFunc.AggregateSum(SqlFunc.IIF(p.SourceChannelCode != null && p.SourceChannelCode != "", 1, 0)),
                  DicDataValue = s.DicDataValue,
                  DicDataName = s.DicDataName
              })
              .ToPivotListAsync(p => p.DicDataValue, p => p.Hour, p => p.Sum(x => x.count));
            return listOrg;

        }

        /// <summary>
        /// 信件来源统计---导出
        /// </summary>
        /// <param name="dto"></param>
        /// <returns></returns>
        public async Task<DataTable> OrderSourceExport(QueryOrderSourceRequest dto)
        {
            var listOrder = Db.Queryable<Order>()
                 .Where(p => p.CreationTime >= dto.StartTime && p.CreationTime <= dto.EndTime)
                 .WhereIF(dto.TypeId != null && dto.TypeId == 1, p => p.IdentityType == EIdentityType.Citizen)
                 .WhereIF(dto.TypeId != null && dto.TypeId == 2, p => p.IdentityType == EIdentityType.Enterprise)
                 .Select(p => new
                 {
                     SourceChannelCode = p.SourceChannelCode,
                     Hour = p.CreationTime.ToString("yyyy-MM-dd")
                 })
               .MergeTable();

            var listOrg = await Db.Queryable<SystemDicData>()
              .LeftJoin(listOrder, (s, p) => s.DicDataValue == p.SourceChannelCode)
              .Where((s, p) => s.DicTypeCode == "SourceChannel")
              .GroupBy((s, p) => s.DicDataValue).GroupBy((s, p) => s.DicDataName)
              .GroupBy((s, p) => p.Hour)
              .OrderBy((s, p) => p.Hour)
              .Select((s, p) => new
              {
                  Hour = SqlFunc.IIF(p.Hour == null || p.Hour == "", "0", p.Hour),
                  count = SqlFunc.AggregateSum(SqlFunc.IIF(p.SourceChannelCode != null && p.SourceChannelCode != "", 1, 0)),
                  DicDataValue = s.DicDataValue,
                  DicDataName = s.DicDataName
              })
              .ToPivotTableAsync(p => p.DicDataName, p => p.Hour, p => p.Sum(x => x.count));
            listOrg.Columns["Hour"].ColumnName = "日期";
            if (listOrg.Rows[0][listOrg.Rows.Count - 1] == "0")
                listOrg.Rows[listOrg.Rows.Count - 1].Delete();
            return InitDatatTable(listOrg, dto.AddColumnName);
        }

        /// <summary>
        /// 处理导出数据
        /// </summary>
        /// <param name="dt"></param>
        /// <param name="AddColumnName"></param>
        /// <returns></returns>
        public DataTable InitDatatTable(DataTable dt, List<string> AddColumnName)
        {
            //修改列名
            if (dt.Columns.Contains("Hour"))
                dt.Columns["Hour"].ColumnName = "时间段";

            //增加小计
            DataColumn totalColumn = new DataColumn("小计", typeof(decimal));
            dt.Columns.Add(totalColumn);
            if (dt.Columns.Contains("Column1"))
                dt.Columns.Remove("Column1");
            //计算小计
            for (int i = 0; i < dt.Rows.Count; i++)
            {
                int sumcount = 0;
                for (int j = 1; j < dt.Columns.Count - 1; j++)
                {
                    sumcount += Convert.ToInt32(dt.Rows[i][j].ToString());
                }
                dt.Rows[i][dt.Columns.Count - 1] = sumcount;
            }

            //增加合计
            DataRow totalRow = dt.NewRow();
            if (dt.Columns[0].ColumnName == "日期") totalRow["日期"] = "合计";
            else totalRow["时间段"] = "合计";
            for (int i = 1; i < dt.Columns.Count; i++)
            {
                int sumcount = 0;
                for (int j = 0; j < dt.Rows.Count; j++)
                {
                    sumcount += Convert.ToInt32(dt.Rows[j][i].ToString());
                }
                totalRow[i] = sumcount;
            }
            dt.Rows.Add(totalRow);

            //创建新表
            DataTable dt2 = new DataTable();

            //添加表头
            foreach (var item in AddColumnName)
            {
                if (dt.Columns.Contains(item))
                    dt2.Columns.Add(item);
            }

            //处理数据
            foreach (DataRow sourceRow in dt.Rows)
            {
                DataRow targetRow = dt2.NewRow();
                foreach (var item in AddColumnName)
                {
                    targetRow[item] = sourceRow[item];
                }
                dt2.Rows.Add(targetRow);
            }
            return dt2;
        }


        /// <summary>
        /// 热点区域明细
        /// </summary>
        /// <param name="dto"></param>
        /// <returns></returns>
        public ISugarQueryable<HotspotAndAreaStatisticsDetailDto> HotspotAndAreaStatisticsDetail(HotspotAndAreaStatisticsDetailReq dto)
        {
            var query = Db.Queryable<Order>()
                //.Includes(x => x.OrderScreens.OrderByDescending(x => x.CreationTime).FirstOrDefault())
                .Where(x => x.HotspotId.StartsWith(dto.HotspotId) && x.CreationTime >= dto.StartTime && x.CreationTime < dto.EndTime)
                .WhereIF(!string.IsNullOrEmpty(dto.AreaCode), x => x.AreaCode.StartsWith(dto.AreaCode))
                .Select(x => new HotspotAndAreaStatisticsDetailDto
                {
                    Id = x.Id,
                    Status = x.Status,
                    SourceChannel = x.SourceChannel,
                    ActualHandleStepName = x.ActualHandleStepName,
                    ReTransactNum = x.ReTransactNum,
                    IsUrgent = x.IsUrgent,
                    OrderScreenStatus = SqlFunc.Subqueryable<OrderScreen>().Where(d => d.OrderId == x.Id).OrderByDesc(d => d.CreationTime).Select(d => d.Status),
                    No = x.No,
                    CreationTime = x.CreationTime,
                    Title = x.Title,
                    ExpiredTime = x.ExpiredTime,
                    ActualHandleOrgName = x.ActualHandleOrgName,
                    FiledTime = x.FiledTime,
                    ActualHandleTime = x.ActualHandleTime,
                    AcceptType = x.AcceptType,
                    HotspotName = x.HotspotName,
                    AcceptorName = x.AcceptorName,
                    NearlyExpiredTime = x.NearlyExpiredTime,
                    NearlyExpiredTimeOne = x.NearlyExpiredTimeOne
                }).OrderByDescending(x => x.CreationTime);
            return query;
        }

        /// <summary>
        /// 受理类型明细
        /// </summary>
        /// <param name="dto"></param>
        /// <returns></returns>
        public ISugarQueryable<HotspotAndAreaStatisticsDetailDto> AcceptTypeStatisticsByDateDetail(AcceptTypeStatisticsByDateDetailReq dto)
        {
            string Date = dto.Date.ToString("yyyy-MM-dd");
            var query = Db.Queryable<Order>()
               //.Includes(x => x.OrderScreens.OrderByDescending(x => x.CreationTime).FirstOrDefault())
               .Where(x => x.AcceptTypeCode == dto.AcceptTypeCode && x.CreationTime.ToString("yyyy-MM-dd") == Date)
               .WhereIF(!string.IsNullOrEmpty(dto.No), x => x.No == dto.No)
               .WhereIF(!string.IsNullOrEmpty(dto.Title), x => x.Title.Contains(dto.Title))
               .WhereIF(!string.IsNullOrEmpty(dto.ActualHandleOrgName), x => x.ActualHandleOrgName.Contains(dto.ActualHandleOrgName))
               .Select(x => new HotspotAndAreaStatisticsDetailDto
               {
                   Id = x.Id,
                   Status = x.Status,
                   SourceChannel = x.SourceChannel,
                   ActualHandleStepName = x.ActualHandleStepName,
                   ReTransactNum = x.ReTransactNum,
                   IsUrgent = x.IsUrgent,
                   OrderScreenStatus = SqlFunc.Subqueryable<OrderScreen>().Where(d => d.OrderId == x.Id).OrderByDesc(d => d.CreationTime).Select(d => d.Status),
                   No = x.No,
                   CreationTime = x.CreationTime,
                   Title = x.Title,
                   ExpiredTime = x.ExpiredTime,
                   ActualHandleOrgName = x.ActualHandleOrgName,
                   FiledTime = x.FiledTime,
                   ActualHandleTime = x.ActualHandleTime,
                   AcceptType = x.AcceptType,
                   HotspotName = x.HotspotName,
                   AcceptorName = x.AcceptorName,
                   NearlyExpiredTime = x.NearlyExpiredTime,
                   NearlyExpiredTimeOne = x.NearlyExpiredTimeOne
               }).OrderByDescending(x => x.CreationTime);
            return query;
        }

        /// <summary>
        /// 根据时间查询受理类型统计
        /// </summary>
        /// <param name="dto"></param>
        /// <returns></returns>
        public async Task<(List<SystemDicData> acceptType, object items)> AcceptTypeStatisticsByDate(AcceptTypeStatisticsByDateReq dto)
        {
            //处理时间
            var diffDays = (dto.EndTime - dto.StartTime).Days;
            List<string> dateList = new List<string>();
            for (int i = 0; i <= diffDays; i++)
            {
                dateList.Add(dto.StartTime.AddDays(i).ToString("yyyy-MM-dd"));
            }
            //第一列
            var dateQuery = Db.Reportable(dateList).ToQueryable<string>().MergeTable();

            //表头
            var acceptTypeList = Db.Queryable<SystemDicData>().Where(x => x.DicTypeCode == SysDicTypeConsts.AcceptType).MergeTable();

            //工单
            var orderList = Db.Queryable<Order>()
                .Where(x => x.CreationTime >= dto.StartTime && x.CreationTime < dto.EndTime)
                .WhereIF(dto.TypeId != null && dto.TypeId == 1, x => x.IdentityType == EIdentityType.Citizen)
                .WhereIF(dto.TypeId != null && dto.TypeId == 2, x => x.IdentityType == EIdentityType.Enterprise)
                .WhereIF(!string.IsNullOrEmpty(dto.Gateway), x => x.TransferPhone == dto.Gateway)
                .Select(x => new
                {
                    Date = x.CreationTime.ToString("yyyy-MM-dd"),
                    AcceptTypeCode = x.AcceptTypeCode,
                })
                .MergeTable();

            //时间关联工单
            var dateAndOrderList = dateQuery.LeftJoin(orderList, (it, o) => it.ColumnName == o.Date)
                .Where((it, o) => string.IsNullOrEmpty(o.AcceptTypeCode) == false)
                .GroupBy((it, o) => new
                {
                    it.ColumnName,
                    o.AcceptTypeCode,
                })
                .Select((it, o) => new
                {
                    Date = it.ColumnName,
                    AcceptTypeCode = o.AcceptTypeCode,
                    Count = SqlFunc.AggregateCount(o.AcceptTypeCode)
                }).MergeTable();
            //类型关联工单
            var returnList = await acceptTypeList.LeftJoin(dateAndOrderList, (it, o) => it.DicDataValue == o.AcceptTypeCode)
                .Where((it, o) => o.Date != null)
                .GroupBy((it, o) => new
                {
                    o.Date,
                    it.DicDataValue,
                })
                .OrderBy((it, o) => o.Date)
                .Select((it, o) => new
                {
                    Date = o.Date,
                    AcceptTypeCode = it.DicDataValue,
                    Count = SqlFunc.AggregateSum(o.Count)
                })
                .ToPivotListAsync(q => q.AcceptTypeCode, q => new { q.Date }, q => q.Sum(x => x.Count));

            //表头
            var acceptTypeTitleList = await Db.Queryable<SystemDicData>().Where(x => x.DicTypeCode == SysDicTypeConsts.AcceptType).ToListAsync();
            return (acceptTypeTitleList, returnList);
        }

        /// <summary>
        /// 根据时间查询受理类型统计-导出
        /// </summary>
        /// <param name="dto"></param>
        /// <returns></returns>
        public async Task<DataTable> AcceptTypeStatisticsByDateExport(AcceptTypeStatisticsByDateReq dto)
        {
            //处理时间
            var diffDays = (dto.EndTime - dto.StartTime).Days;
            List<string> dateList = new List<string>();
            for (int i = 0; i <= diffDays; i++)
            {
                dateList.Add(dto.StartTime.AddDays(i).ToString("yyyy-MM-dd"));
            }
            //第一列
            var dateQuery = Db.Reportable(dateList).ToQueryable<string>().MergeTable();

            //表头
            var acceptTypeList = Db.Queryable<SystemDicData>().Where(x => x.DicTypeCode == SysDicTypeConsts.AcceptType).MergeTable();

            //工单
            var orderList = Db.Queryable<Order>()
                .Where(x => x.CreationTime >= dto.StartTime && x.CreationTime < dto.EndTime)
                .WhereIF(dto.TypeId != null && dto.TypeId == 1, x => x.IdentityType == EIdentityType.Citizen)
                .WhereIF(dto.TypeId != null && dto.TypeId == 2, x => x.IdentityType == EIdentityType.Enterprise)
                .WhereIF(!string.IsNullOrEmpty(dto.Gateway), x => x.TransferPhone == dto.Gateway)
                .Select(x => new
                {
                    Date = x.CreationTime.ToString("yyyy-MM-dd"),
                    AcceptTypeCode = x.AcceptTypeCode,
                })
                .MergeTable();

            //时间关联工单
            var dateAndOrderList = dateQuery.LeftJoin(orderList, (it, o) => it.ColumnName == o.Date)
                .Where((it, o) => string.IsNullOrEmpty(o.AcceptTypeCode) == false)
                .GroupBy((it, o) => new
                {
                    it.ColumnName,
                    o.AcceptTypeCode,
                })
                .Select((it, o) => new
                {
                    Date = it.ColumnName,
                    AcceptTypeCode = o.AcceptTypeCode,
                    Count = SqlFunc.AggregateCount(o.AcceptTypeCode)
                }).MergeTable();
            //类型关联工单
            var returnList = await acceptTypeList.LeftJoin(dateAndOrderList, (it, o) => it.DicDataValue == o.AcceptTypeCode)
                .Where((it, o) => o.Date != null)
                .GroupBy((it, o) => new
                {
                    o.Date,
                    it.DicDataValue,
                    it.DicDataName
                })
                .OrderBy((it, o) => o.Date)
                .Select((it, o) => new
                {
                    Date = o.Date,
                    AcceptTypeCode = it.DicDataValue,
                    AcceptType = it.DicDataName,
                    Count = SqlFunc.AggregateSum(o.Count)
                })
                .ToPivotTableAsync(q => q.AcceptType, q => new { q.Date }, q => q.Sum(x => x.Count));

            return returnList;
        }

        public DataTable InitAcceptTypeTable(DataTable dt, List<string> AddColumnName)
        {
            //修改列名
            if (dt.Columns.Contains("Date"))
                dt.Columns["Date"].ColumnName = "日期";

            //增加小计
            DataColumn totalColumn = new DataColumn("小计", typeof(decimal));
            dt.Columns.Add(totalColumn);
            if (dt.Columns.Contains("Column1"))
                dt.Columns.Remove("Column1");

            //计算小计
            for (int i = 0; i < dt.Rows.Count; i++)
            {
                int sumcount = 0;
                for (int j = 1; j < dt.Columns.Count - 1; j++)
                {
                    sumcount += Convert.ToInt32(dt.Rows[i][j].ToString());
                }
                dt.Rows[i][dt.Columns.Count - 1] = sumcount;
            }

            //增加合计
            DataRow totalRow = dt.NewRow();
            if (dt.Columns[0].ColumnName == "Date") totalRow["Date"] = "合计";
            else totalRow["日期"] = "合计";
            for (int i = 1; i < dt.Columns.Count; i++)
            {
                int sumcount = 0;
                for (int j = 0; j < dt.Rows.Count; j++)
                {
                    sumcount += Convert.ToInt32(dt.Rows[j][i].ToString());
                }
                totalRow[i] = sumcount;
            }
            dt.Rows.Add(totalRow);

            //创建新表
            DataTable dt2 = new DataTable();

            //添加表头
            foreach (var item in AddColumnName)
            {
                dt2.Columns.Add(item);
            }

            //处理数据
            foreach (DataRow sourceRow in dt.Rows)
            {
                DataRow targetRow = dt2.NewRow();
                foreach (var item in AddColumnName)
                {
                    try
                    {
                        targetRow[item] = sourceRow[item];
                    }
                    catch
                    {
                        targetRow[item] = 0;
                    }
                }
                dt2.Rows.Add(targetRow);
            }

            return dt2;
        }

        /// <summary>
        /// 中心受理统计（时间段）
        /// </summary>
        /// <returns></returns>
        public ISugarQueryable<OrderCenterAcceptHourVo> OrderCenterAcceptHour(OrderCenterAcceptPagedRequest dto)
        {
            if (dto.EndTime.HasValue)
                dto.EndTime = dto.EndTime.Value.AddDays(1).AddSeconds(-1);
            var sendBack = _orderSendBackAuditRepository.Queryable()
                .Where(x => x.State == ESendBackAuditState.End)
                .GroupBy(x => x.OrderId).Select(x => new { OrderId = x.OrderId });

            List<int> dts = new();
            for (int i = 0; i < 24; i++)
            {
                dts.Add(i);
            }

            var listHour = Db.Reportable(dts).ToQueryable<int>();

            var data = Db.Queryable<Order>()
                .LeftJoin(sendBack, (d, s) => d.Id == s.OrderId)
                .WhereIF(dto.StartTime.HasValue && dto.EndTime.HasValue, d => d.CreationTime >= dto.StartTime && d.CreationTime <= dto.EndTime)
                .WhereIF(dto.TypeCode != null && dto.TypeCode == 1, d => d.IdentityType == EIdentityType.Citizen)
                .WhereIF(dto.TypeCode != null && dto.TypeCode == 2, d => d.IdentityType == EIdentityType.Enterprise)
                .GroupBy(d => d.CreationTime.Hour.ToString())
                .Select((d, s) => new OrderCenterAcceptHourVo
                {
                    Hour = d.CreationTime.Hour.ToString(),
                    AcceptNum = SqlFunc.AggregateCount(1),
                    ValidNum = SqlFunc.AggregateSum(SqlFunc.IIF(d.AcceptType != "无效", 1, 0)),
                    RepetitionNum = SqlFunc.AggregateSum(SqlFunc.IIF(d.DuplicateIds == null || SqlFunc.JsonArrayLength(d.DuplicateIds) > 0, 1, 0)),
                    InvalidNum = SqlFunc.AggregateSum(SqlFunc.IIF(d.AcceptType == "无效", 1, 0)),
                    HandleNum = SqlFunc.AggregateSum(SqlFunc.IIF(d.Status >= EOrderStatus.Filed, 1, 0)),
                    NoHandleNum = SqlFunc.AggregateSum(SqlFunc.IIF(d.Status < EOrderStatus.Filed, 1, 0)),
                    BackNum = SqlFunc.AggregateSum(SqlFunc.IIF(s.OrderId != null && s.OrderId != "", 1, 0)),
                    DutyDeskNum = SqlFunc.AggregateDistinctCount(d.AcceptorId)
                });
            var query = listHour.LeftJoin(data, (h, d) => h.ColumnName.ToString() == d.Hour)
                 .GroupBy((h, d) => h.ColumnName)
                 .OrderBy((h, d) => h.ColumnName)
                 .Select((h, d) => new OrderCenterAcceptHourVo
                 {
                     Hour = h.ColumnName.ToString() + ":00 - " + h.ColumnName.ToString() + ":59",
                     AcceptNum = SqlFunc.AggregateSum(d.AcceptNum),
                     ValidNum = SqlFunc.AggregateSum(d.ValidNum),
                     RepetitionNum = SqlFunc.AggregateSum(d.RepetitionNum),
                     InvalidNum = SqlFunc.AggregateSum(d.InvalidNum),
                     HandleNum = SqlFunc.AggregateSum(d.HandleNum),
                     NoHandleNum = SqlFunc.AggregateSum(d.NoHandleNum),
                     BackNum = SqlFunc.AggregateSum(d.BackNum),
                     DutyDeskNum = SqlFunc.AggregateSum(d.DutyDeskNum)
                 });
            return query;
        }
        public ISugarQueryable<SelectOrderId> OrderListUnionAll(ISugarQueryable<SelectOrderId> t1, ISugarQueryable<SelectOrderId> t2)
        {
            return Db.UnionAll(t1, t2).Select(it => new SelectOrderId { Id = it.Id }).MergeTable();
        }


        /// <summary>
        /// 数据清单
        /// </summary>
        /// <param name="dto"></param>
        /// <returns></returns>
        public ISugarQueryable<OrderDataInventoryRep> OrderDataInventory(OrderDataInventoryReq dto)
        {
            dto.EndTime = dto.EndTime.AddDays(1).AddSeconds(-1);

            var result = Db.Queryable<Order>()
                //.Includes(x => x.OrderScreens.OrderByDescending(x => x.CreationTime).FirstOrDefault(),it=>it.Status)
                //.Includes(x => x.OrderVisits.Where(x => x.VisitState == EVisitState.Visited).FirstOrDefault(),it=>it.OrderVisitDetails)
                .Where(x => x.CreationTime >= dto.StartTime) //开始时间
                .Where(x => x.CreationTime <= dto.EndTime)//开始时间
                .WhereIF(!string.IsNullOrEmpty(dto.No), x => x.No == dto.No) //工单编号
                .WhereIF(!string.IsNullOrEmpty(dto.Title), x => x.Title.Contains(dto.Title)) //工单标题
                .WhereIF(dto.OrderStatus.HasValue, x => x.Status == dto.OrderStatus) //工单状态
                .WhereIF(!string.IsNullOrEmpty(dto.Channel), x => x.SourceChannelCode == dto.Channel) //来源渠道
                .WhereIF(!string.IsNullOrEmpty(dto.TransferPhone), x => x.TransferPhone == dto.TransferPhone!) //转接号码
                .WhereIF(!string.IsNullOrEmpty(dto.CurrentStepCode), x => x.CurrentStepCode.Contains(dto.CurrentStepCode)) //当前节点
                .WhereIF(dto.ScreenStatus.HasValue, x => x.OrderScreens.Any(x => x.Status == dto.ScreenStatus)) //甄别状态
                .WhereIF(!string.IsNullOrEmpty(dto.ProvinceNo), x => x.ProvinceNo == dto.ProvinceNo)//省工单编号
                .WhereIF(dto.ExpiredTimeStart.HasValue, x => x.ExpiredTime >= dto.ExpiredTimeStart) //期满时间开始
                .WhereIF(dto.ExpiredTimeEnd.HasValue, x => x.ExpiredTime <= dto.ExpiredTimeEnd.Value.AddDays(1).AddSeconds(-1)) //期满时间结束
                .WhereIF(!string.IsNullOrEmpty(dto.OrgLevelOneName), x => x.OrgLevelOneName.Contains(dto.OrgLevelOneName))//一级部门名称
                .WhereIF(!string.IsNullOrEmpty(dto.OrgLevelTwoName), x => x.OrgLevelTwoName.Contains(dto.OrgLevelTwoName)) //二级部门名称
                .WhereIF(!string.IsNullOrEmpty(dto.CurrentHandleOrgId), x => x.CurrentHandleOrgId.Contains(dto.CurrentHandleOrgId)) //接办部门
                .WhereIF(dto.FiledTimeStart.HasValue, x => x.FiledTime >= dto.FiledTimeStart) //办结时间开始
                .WhereIF(dto.FiledTimeEnd.HasValue, x => x.FiledTime <= dto.FiledTimeEnd.Value.AddDays(1).AddSeconds(-1)) //办结时间结束
                .WhereIF(!string.IsNullOrEmpty(dto.AcceptType), x => x.AcceptTypeCode == dto.AcceptType)//受理类型
                .WhereIF(!string.IsNullOrEmpty(dto.Hotspot), x => x.HotspotSpliceName != null && x.HotspotSpliceName.Contains(dto.Hotspot)) //热点分类
                .WhereIF(!string.IsNullOrEmpty(dto.AreaCode), x => x.AreaCode == dto.AreaCode)//区域
                .WhereIF(!string.IsNullOrEmpty(dto.NameOrNo), x => x.AcceptorName == dto.NameOrNo! || x.AcceptorStaffNo == dto.NameOrNo!) //受理人/坐席
                .WhereIF(!string.IsNullOrEmpty(dto.FromName), x => x.FromName.Contains(dto.FromName)) //来电人姓名
                .WhereIF(!string.IsNullOrEmpty(dto.FromPhone), x => x.FromPhone == dto.FromPhone) //来电号码
                .WhereIF(!string.IsNullOrEmpty(dto.PhoneNo), x => x.Contact == dto.PhoneNo!) //联系电话
                .WhereIF(dto.IdentityType.HasValue, x => x.IdentityType == dto.IdentityType) //来电主体
                .WhereIF(dto.FromGender.HasValue, x => x.FromGender == dto.FromGender) //来电性别
                                                                                       //.WhereIF(!string.IsNullOrEmpty(dto.PushTypeCode), x => x.PushTypeCode == dto.PushTypeCode)//推送分类
                .WhereIF(!string.IsNullOrEmpty(dto.PushTypeCode), x => x.OrderPushTypes.Any(opt => opt.PushTypeCode == dto.PushTypeCode))//推送分类
                .WhereIF(dto.ExpiredStatus.HasValue && dto.ExpiredStatus == EExpiredStatusEx.Normal, x => (x.ExpiredTime.HasValue && DateTime.Now < x.ExpiredTime && x.Status >= EOrderStatus.Filed) || (x.NearlyExpiredTime.HasValue && DateTime.Now < x.NearlyExpiredTime && x.Status < EOrderStatus.Filed)) //超期状态：正常
                .WhereIF(dto.ExpiredStatus.HasValue && dto.ExpiredStatus == EExpiredStatusEx.GoingToExpired, x => (x.Status < EOrderStatus.Filed && DateTime.Now > x.NearlyExpiredTime && DateTime.Now < x.ExpiredTime) || (x.Status >= EOrderStatus.Filed && x.FiledTime > x.NearlyExpiredTime && x.FiledTime < x.ExpiredTime)) // 超期状态：即将超期
                .WhereIF(dto.ExpiredStatus.HasValue && dto.ExpiredStatus == EExpiredStatusEx.Expired, x => (x.Status < EOrderStatus.Filed && DateTime.Now > x.ExpiredTime) || (x.Status >= EOrderStatus.Filed && x.FiledTime > x.ExpiredTime))
                .Select(x => new OrderDataInventoryRep()
                {
                    Id = x.Id,
                    OrderStatus = x.Status,
                    ActualHandleTime = x.ActualHandleTime,
                    NearlyExpiredTime = x.NearlyExpiredTime,
                    SourceChannel = x.SourceChannel,
                    TransferPhone = x.TransferPhone,
                    CurrentStepName = x.CurrentStepName,
                    ReTransactNum = x.ReTransactNum,
                    //甄别状态
                    OrderScreenStatus = SqlFunc.Subqueryable<OrderScreen>().Where(q => q.OrderId == x.Id).OrderByDesc(q => q.CreationTime).Select(q => q.Status), //x.OrderScreens.FirstOrDefault().Status,
                    No = x.No,
                    ProvinceNo = x.ProvinceNo,
                    CreationTime = x.CreationTime,
                    Title = x.Title,
                    ExpiredTime = x.ExpiredTime,
                    OrgLevelOneName = x.OrgLevelOneName,
                    OrgLevelTwoName = x.OrgLevelTwoName,
                    CurrentHandleOrgName = x.CurrentHandleOrgName,
                    FiledTime = x.FiledTime,
                    AcceptType = x.AcceptType,
                    HotspotName = x.HotspotName,
                    HotspotSpliceName = x.HotspotSpliceName,
                    AreaName = SqlFunc.IIF(string.IsNullOrEmpty(x.Town) == false, x.Town, SqlFunc.IIF(string.IsNullOrEmpty(x.County) == false, x.County, x.City)),
                    FullAreaName = x.FullAddress,
                    AcceptorName = x.AcceptorName,
                    FromName = x.FromName,
                    Contact = x.Contact,
                    FromPhone = x.FromPhone,
                    Address = x.Street,
                    IdentityType = x.IdentityType,
                    FromGender = x.FromGender,
                    //坐席评价
                    SeatEvaluate = SqlFunc.Subqueryable<OrderVisitDetail>()
                          .Where(w => w.VisitId == SqlFunc.Subqueryable<OrderVisit>().Where(q => q.OrderId == x.Id && q.VisitState == EVisitState.Visited).Select(q => q.Id) && w.VisitTarget == EVisitTarget.Seat)
                          .Select(w => w.SeatEvaluate),
                    //办结结果
                    OrgProcessingResults = SqlFunc.Subqueryable<OrderVisitDetail>()
                          .Where(w => w.VisitId == SqlFunc.Subqueryable<OrderVisit>().Where(q => q.OrderId == x.Id && q.VisitState == EVisitState.Visited).Select(q => q.Id) && w.VisitTarget == EVisitTarget.Org)
                          .Select(w => SqlFunc.JsonField(w.OrgProcessingResults, "Value")),
                    PushType = x.PushType,
                    Content = x.Content,
                    ActualOpinion = x.ActualOpinion,
                    FileOpinion = x.FileOpinion,
                }).OrderByDescending(x => x.CreationTime);
            return result;
        }

        /// <summary>
        /// 扭转列表
        /// </summary>
        /// <param name="dto"></param>
        /// <returns></returns>
        public ISugarQueryable<OrderVisit> VisitJudgeQuery(VisitJudgeQueryReq dto)
        {
            return _orderVisitRepository.Queryable()
            .Includes(x => x.Order)
            .Includes(x => x.Employee)
            .Where(x => x.VisitState == EVisitState.Visited)
            .WhereIF(!string.IsNullOrEmpty(dto.No), x => x.No == dto.No)
            .WhereIF(!string.IsNullOrEmpty(dto.Title), x => x.Order.Title.Contains(dto.Title))
            .WhereIF(!string.IsNullOrEmpty(dto.VisitUserName), x => x.Employee.Name.Contains(dto.VisitUserName))
            .WhereIF(!string.IsNullOrEmpty(dto.AcceptType), d => d.Order.AcceptTypeCode == dto.AcceptType)//受理类型
            .WhereIF(!string.IsNullOrEmpty(dto.Hotspot), d => d.Order.HotspotSpliceName != null && d.Order.HotspotSpliceName.Contains(dto.Hotspot))//热点类型
            .WhereIF(!string.IsNullOrEmpty(dto.OrgId), d => d.Order.CurrentHandleOrgId == dto.OrgId)//接办部门
            .WhereIF(!string.IsNullOrEmpty(dto.NameOrNo),
                d => d.Order.AcceptorName.Contains(dto.NameOrNo!) || d.Order.AcceptorStaffNo.Contains(dto.NameOrNo!)) //受理人/坐席
            .WhereIF(dto.CreationTimeStart.HasValue, d => d.CreationTime >= dto.CreationTimeStart) //受理时间开始
            .WhereIF(dto.CreationTimeEnd.HasValue, d => d.CreationTime <= dto.CreationTimeEnd) //受理时间结束
            .WhereIF(dto.ActualHandleTimeStart.HasValue, d => d.Order.ActualHandleTime >= dto.ActualHandleTimeStart) //办结时间开始
            .WhereIF(dto.ActualHandleTimeEnd.HasValue, d => d.Order.ActualHandleTime <= dto.ActualHandleTimeEnd) //办结时间结束
            .WhereIF(dto.VisitTimeStart.HasValue, d => d.VisitTime >= dto.VisitTimeStart) //回访开始时间
            .WhereIF(dto.VisitTimeEnd.HasValue, d => d.VisitTime <= dto.VisitTimeEnd)
            .WhereIF(dto.IsIng == true, d => d.JudgeState == EJudgeState.Judging)
            .WhereIF(dto.IsIng == false, d => d.JudgeState != EJudgeState.Judging)
            .WhereIF(dto.JudgeState != null, d => d.JudgeState == dto.JudgeState)
            .WhereIF(dto.OrgJudge != null, d => d.OrgJudge == dto.OrgJudge)
            .WhereIF(dto.SeatJudge != null, d => d.SeatJudge == dto.SeatJudge)
            //.WhereIF(dto.FiledType != null && dto.FiledType == FiledType.CenterFiled, d => d.Order.ProcessType == EProcessType.Zhiban)
            //.WhereIF(dto.FiledType != null && dto.FiledType == FiledType.OrgFiled, d => d.Order.ProcessType == EProcessType.Jiaoban)
                  .WhereIF(dto.FiledType != null && dto.FiledType == FiledType.CenterFiled, d => d.Order.FileOrgIsCenter == true)
            .WhereIF(dto.FiledType != null && dto.FiledType == FiledType.OrgFiled, d => d.Order.FileOrgIsCenter == false)
            .WhereIF(!string.IsNullOrEmpty(dto.TransferPhone), x => x.Order.TransferPhone == dto.TransferPhone)
            .OrderByDescending(x => x.VisitTime);
        }

        /// <summary>
        /// 满意度明细
        /// </summary>
        /// <param name="dto"></param>
        /// <returns></returns>
        public ISugarQueryable<OrgVisitDetailListResp> OrgVisitDetailList(OrgVisitDetailListReq dto)
        {
            var IsCenter = _sessionContext.OrgIsCenter;
            if (dto.OrgProcessingResults == null || !dto.OrgProcessingResults.Any() || dto.OrgProcessingResults.First() == null)
                dto.OrgProcessingResults = null;

            return _orderVisitDetailRepository.Queryable()
                .Includes(x => x.OrderVisit, x => x.Order)
                .Includes(x => x.OrderVisit, x => x.Employee)
                .Where(x => x.OrderVisit.VisitState == EVisitState.Visited && x.VisitTarget == EVisitTarget.Org)
                .WhereIF(IsCenter == false, x => x.VisitOrgCode.StartsWith(_sessionContext.RequiredOrgId))
                // .WhereIF(dto.OrgVisitStatisticsType.HasValue, x => x.OrderVisit.Order.ProcessType == (EProcessType)((int)dto.OrgVisitStatisticsType))
                .WhereIF(dto.OrgVisitStatisticsType.HasValue && dto.OrgVisitStatisticsType == EOrgVisitStatisticsType.CallCenter,
                    x => x.OrderVisit.Order.ActualHandleOrgCode == OrgSeedData.CenterId)
                .WhereIF(dto.OrgVisitStatisticsType.HasValue && dto.OrgVisitStatisticsType == EOrgVisitStatisticsType.Org,
                    x => x.OrderVisit.Order.ActualHandleOrgCode != OrgSeedData.CenterId)
                .WhereIF(dto.Keyword.NotNullOrEmpty(), x => x.OrderVisit.Order.Title.Contains(dto.Keyword)) // 根据关键字匹配
                .WhereIF(dto.TypeCode != 0, x => x.OrderVisit.Order.IdentityType == (EIdentityType)dto.TypeCode)
                //任务 218 市州通用-部门满意度明细：办件结果查询优化为多选
                //.WhereIF(dto.OrgProcessingResults != null && dto.OrgProcessingResults.Any(),
                //      x => dto.OrgProcessingResults.Contains(SqlFunc.JsonField(x.OrgProcessingResults, "Key")))
                //.WhereIF(dto.OrgHandledAttitude != null && dto.OrgHandledAttitude.Any(),
                //     x => dto.OrgHandledAttitude.Contains(SqlFunc.JsonField(x.OrgHandledAttitude, "Key")))
                .WhereIF(!string.IsNullOrEmpty(dto.OrgProcessingResults), dto.AttitudeType == EAttitudeType.ProcessingResult ? x => SqlFunc.JsonField(x.OrgProcessingResults, "Key") == dto.OrgProcessingResults : x => SqlFunc.JsonField(x.OrgHandledAttitude, "Key") == dto.OrgProcessingResults)
                .WhereIF(!string.IsNullOrEmpty(dto.VisitUser), x => x.OrderVisit.Employee.Name.Contains(dto.VisitUser))
                .WhereIF(!string.IsNullOrEmpty(dto.No), x => x.OrderVisit.Order.No == dto.No)
                .WhereIF(!string.IsNullOrEmpty(dto.Title), x => x.OrderVisit.Order.Title.Contains(dto.Title))
                .WhereIF(!string.IsNullOrEmpty(dto.OrgId), x => x.VisitOrgCode == dto.OrgId)//接办部门
                .WhereIF(!string.IsNullOrEmpty(dto.Hotspot), x => x.OrderVisit.Order.HotspotSpliceName != null && x.OrderVisit.Order.HotspotSpliceName.Contains(dto.Hotspot))//热点类型
                .WhereIF(!string.IsNullOrEmpty(dto.Channel), x => x.OrderVisit.Order.SourceChannelCode == dto.Channel)//来源渠道
                .WhereIF(dto.CreationTimeStart.HasValue, x => x.OrderVisit.Order.CreationTime >= dto.CreationTimeStart) //受理时间开始
                .WhereIF(dto.CreationTimeEnd.HasValue, x => x.OrderVisit.Order.CreationTime <= dto.CreationTimeEnd) //受理时间结束
                .WhereIF(dto.ActualHandleTimeStart.HasValue, x => x.OrderVisit.Order.ActualHandleTime >= dto.ActualHandleTimeStart) //办结时间开始
                .WhereIF(dto.ActualHandleTimeEnd.HasValue, x => x.OrderVisit.Order.ActualHandleTime <= dto.ActualHandleTimeEnd)//办结时间结束
                .WhereIF(dto.VisitTimeStart.HasValue, x => x.OrderVisit.VisitTime >= dto.VisitTimeStart) //回访时间
                .WhereIF(dto.VisitTimeEnd.HasValue, x => x.OrderVisit.VisitTime < dto.VisitTimeEnd) //回访时间
                .WhereIF(dto.VisitType != null, x => x.OrderVisit.VisitType == dto.VisitType) //回访方式
                .WhereIF(dto.IsProvinceOrder.HasValue && dto.IsProvinceOrder == true, x => x.OrderVisit.Order.Source == ESource.ProvinceStraight)
                .WhereIF(dto.IsProvinceOrder.HasValue && dto.IsProvinceOrder == false, x => x.OrderVisit.Order.Source != ESource.ProvinceStraight)
                .WhereIF(!string.IsNullOrEmpty(dto.ContentRetrieval), x => x.VisitContent.Contains(dto.ContentRetrieval!))
                .WhereIF(!string.IsNullOrEmpty(dto.LevelOneOrg), x => x.OrderVisit.Order.OrgLevelOneName.Contains(dto.LevelOneOrg))//一级部门名称
                .WhereIF(!string.IsNullOrEmpty(dto.ActualHandleOrgName), x => x.OrderVisit.Order.ActualHandleOrgName.Contains(dto.ActualHandleOrgName))// 接办部门
                .Select(x => new OrgVisitDetailListResp()
                {
                    Id = x.Id,
                    OrderId = x.OrderVisit.Order.Id,
                    VisitId = x.OrderVisit.Id,
                    No = x.OrderVisit.No,
                    ReTransactNum = x.OrderVisit.Order.ReTransactNum,
                    OrderScreenStatus = SqlFunc.Subqueryable<OrderScreen>().Where(q => q.OrderId == x.OrderVisit.OrderId && q.VisitDetailId == x.Id).OrderByDesc(q => q.CreationTime).Select(q => q.Status),
                    VisitContent = x.VisitContent,
                    HotspotSpliceName = x.OrderVisit.Order.HotspotSpliceName,
                    OrgLevelOneName = x.OrderVisit.Order.OrgLevelOneName,
                    CurrentHandleOrgName = x.OrderVisit.Order.CurrentHandleOrgName,
                    CreationTime = x.OrderVisit.Order.CreationTime,
                    Title = x.OrderVisit.Order.Title,
                    VisitUser = x.OrderVisit.Employee.Name,
                    VisitType = x.OrderVisit.VisitType,
                    VisitTime = x.OrderVisit.VisitTime,
                    OrgProcessingResults = dto.AttitudeType == EAttitudeType.ProcessingResult ? SqlFunc.JsonField(x.OrgProcessingResults, "Value")
                    : SqlFunc.JsonField(x.OrgHandledAttitude, "Value"),
                    Content = x.OrderVisit.Order.Content,
                    FileOpinion = x.OrderVisit.Order.FileOpinion,
                    FiledTime = x.OrderVisit.Order.FiledTime,
                    VisitOrgName = x.VisitOrgName,
                    ActualHandleOrgName = x.OrderVisit.Order.ActualHandleOrgName,
                    IsProvinceOrder = x.OrderVisit.Order.Source == ESource.ProvinceStraight ? true : false
                }).MergeTable().OrderByIF(string.IsNullOrEmpty(dto.SortField), x => x.VisitTime, OrderByType.Desc)
                .OrderByIF(dto is { SortField: "creationTime", SortRule: 0 }, x => x.CreationTime, OrderByType.Asc) //受理时间升序
                .OrderByIF(dto is { SortField: "creationTime", SortRule: 1 }, x => x.CreationTime, OrderByType.Desc) //受理时间降序
                .OrderByIF(dto is { SortField: "visitTime", SortRule: 0 }, x => x.VisitTime, OrderByType.Asc) //回访时间升序
                .OrderByIF(dto is { SortField: "visitTime", SortRule: 1 }, x => x.VisitTime, OrderByType.Desc) //回访时间降序
                .OrderByIF(dto is { SortField: "filedTime", SortRule: 0 }, x => x.FiledTime, OrderByType.Asc) //办结时间升序
                .OrderByIF(dto is { SortField: "filedTime", SortRule: 1 }, x => x.FiledTime, OrderByType.Desc); //办结时间降序;

        }

        /// <summary>
        /// 部门满意度明细统计
        /// </summary>
        /// <param name="dto"></param>
        /// <returns></returns>
        public ISugarQueryable<OrgVisitDetailListResp> VisitAndOrgSatisfactionDetail(VisitAndOrgSatisfactionDetailDto dto)
        {
            bool IsCenter = _sessionContext.OrgIsCenter;
            if (IsCenter == false && string.IsNullOrEmpty(dto.PickOrgCode))
                dto.PickOrgCode = _sessionContext.RequiredOrgId;

            return _orderVisitDetailRepository.Queryable()
                .Includes(x => x.OrderVisit, o => o.Order, d => d.CallRecord)
                .Where(x => x.OrderVisit.VisitTime >= dto.StartTime && x.OrderVisit.VisitTime <= dto.EndTime && x.VisitTarget == EVisitTarget.Org && x.OrderVisit.VisitState == EVisitState.Visited)
                .WhereIF(dto.OrgCode == "001", x => x.VisitOrgCode == dto.OrgCode)
                .WhereIF(dto.TypeCode != null && dto.TypeCode == 1, x => x.OrderVisit.Order.IdentityType == EIdentityType.Citizen)
                .WhereIF(dto.TypeCode != null && dto.TypeCode == 2, x => x.OrderVisit.Order.IdentityType == EIdentityType.Enterprise)
                //.WhereIF(dto.OrgCode != "001", x => x.VisitOrgCode == dto.OrgCode).
                //.WhereIF(dto.IsOnlyMy == true, x => x.VisitOrgCode == dto.OrgCode)
                //.WhereIF(IsCenter == true && dto.IsOnlyMy == true, x => x.VisitOrgCode == dto.OrgCode)
                //.WhereIF(IsCenter == true && dto.IsOnlyMy == null, x => x.VisitOrgCode.StartsWith(dto.OrgCode))
                //.WhereIF(IsCenter == true , x=> x.VisitOrgCode.StartsWith(dto.OrgCode))
                //.WhereIF(IsCenter == false, x => x.VisitOrgCode == dto.OrgCode)
                .WhereIF(!string.IsNullOrEmpty(dto.OrgCode) && (string.IsNullOrEmpty(dto.PickOrgCode) || dto.OrgCode != dto.PickOrgCode), x => x.VisitOrgCode.StartsWith(dto.OrgCode))
                .WhereIF(!string.IsNullOrEmpty(dto.OrgCode) && (!string.IsNullOrEmpty(dto.PickOrgCode) && dto.PickOrgCode == dto.OrgCode), x => x.VisitOrgCode == dto.OrgCode)
                .WhereIF(dto.TypeId is 1, x => SqlFunc.JsonField(x.OrgProcessingResults, "Key") == dto.DateValue)
                .WhereIF(dto.TypeId is 2, x => SqlFunc.JsonField(x.OrgHandledAttitude, "Key") == dto.DateValue)
                .WhereIF(!string.IsNullOrEmpty(dto.LineNum), x => x.OrderVisit.Order.CallRecord.Gateway == dto.LineNum)
                .WhereIF(dto.VisitType != null, x => x.OrderVisit.VisitType == dto.VisitType)
                .WhereIF(dto.IsProvinceOrder.HasValue && dto.IsProvinceOrder == true, x => x.OrderVisit.Order.Source == ESource.ProvinceStraight)
                .WhereIF(dto.IsProvinceOrder.HasValue && dto.IsProvinceOrder == false, x => x.OrderVisit.Order.Source != ESource.ProvinceStraight)
                .WhereIF(!string.IsNullOrEmpty(dto.ContentRetrieval), x => x.VisitContent.Contains(dto.ContentRetrieval!))
                .WhereIF(!string.IsNullOrEmpty(dto.ActualHandleOrgName), x => x.OrderVisit.Order.ActualHandleOrgName.Contains(dto.ActualHandleOrgName!))
                .Select(x => new OrgVisitDetailListResp
                {
                    Id = x.Id,
                    OrderId = x.OrderVisit.Order.Id,
                    VisitId = x.OrderVisit.Id,
                    No = x.OrderVisit.No,
                    ReTransactNum = x.OrderVisit.Order.ReTransactNum,
                    OrderScreenStatus = SqlFunc.Subqueryable<OrderScreen>().Where(q => q.OrderId == x.OrderVisit.OrderId && q.VisitDetailId == x.Id).OrderByDesc(q => q.CreationTime).Select(q => q.Status),
                    VisitContent = x.VisitContent,
                    HotspotSpliceName = x.OrderVisit.Order.HotspotSpliceName,
                    OrgLevelOneName = x.OrderVisit.Order.OrgLevelOneName,
                    CurrentHandleOrgName = x.OrderVisit.Order.CurrentHandleOrgName,
                    CreationTime = x.OrderVisit.Order.CreationTime,
                    Title = x.OrderVisit.Order.Title,
                    VisitUser = x.OrderVisit.Employee.Name,
                    VisitType = x.OrderVisit.VisitType,
                    VisitTime = x.OrderVisit.VisitTime,
                    OrgProcessingResults = SqlFunc.JsonField(x.OrgProcessingResults, "Value"),
                    Content = x.OrderVisit.Order.Content,
                    FileOpinion = x.OrderVisit.Order.FileOpinion,
                    FiledTime = x.OrderVisit.Order.FiledTime,
                    VisitOrgName = x.VisitOrgName,
                    IsProvinceOrder = x.OrderVisit.Order.Source == ESource.ProvinceStraight ? true : false,
                    ActualHandleOrgName = x.OrderVisit.Order.ActualHandleOrgName
                }).MergeTable()
                .OrderByIF(string.IsNullOrEmpty(dto.SortField), x => x.VisitTime, OrderByType.Desc)
                .OrderByIF(dto is { SortField: "creationTime", SortRule: 0 }, x => x.CreationTime, OrderByType.Asc) //受理时间升序
                .OrderByIF(dto is { SortField: "creationTime", SortRule: 1 }, x => x.CreationTime, OrderByType.Desc) //受理时间降序
                .OrderByIF(dto is { SortField: "visitTime", SortRule: 0 }, x => x.VisitTime, OrderByType.Asc) //回访时间升序
                .OrderByIF(dto is { SortField: "visitTime", SortRule: 1 }, x => x.VisitTime, OrderByType.Desc) //回访时间降序
                .OrderByIF(dto is { SortField: "filedTime", SortRule: 0 }, x => x.FiledTime, OrderByType.Asc) //办结时间升序
                .OrderByIF(dto is { SortField: "filedTime", SortRule: 1 }, x => x.FiledTime, OrderByType.Desc); //办结时间降序;
        }

        /// <summary>
        /// 部门满意度明细 排除不满意 非常不满意 未作评价
        /// </summary>
        /// <param name="dto"></param>
        /// <returns></returns>
        public ISugarQueryable<OrgVisitDetailListResp> OrgVisitDetailFiltrationList(OrgVisitDetailListReq dto, string orgId)
        {
            var IsCenter = _sessionContext.OrgIsCenter;
            var hiddenOrder = _systemSettingCacheManager.GetSetting(SettingConstants.OrgVisitDetailHiddenOrderNo).SettingValue;

            return _orderVisitDetailRepository.Queryable()
                .Includes(x => x.OrderVisit, x => x.Order)
                .Includes(x => x.OrderVisit, x => x.Employee)
                .Where(x => x.OrderVisit.VisitState == EVisitState.Visited && x.VisitTarget == EVisitTarget.Org)
                .WhereIF(hiddenOrder.Any(), x => !hiddenOrder.Contains(x.OrderVisit.Order.No))
                .WhereIF(IsCenter == false, x => x.VisitOrgCode.StartsWith(orgId))
                .WhereIF(dto.OrgVisitStatisticsType.HasValue, x => x.OrderVisit.Order.FileOrgIsCenter == (dto.OrgVisitStatisticsType == EOrgVisitStatisticsType.CallCenter))
                .WhereIF(dto.Keyword.NotNullOrEmpty(), x => x.OrderVisit.Order.Title.Contains(dto.Keyword)) // 根据关键字匹配
                .WhereIF(dto.TypeCode != 0, x => x.OrderVisit.Order.IdentityType == (EIdentityType)dto.TypeCode)
                  //任务 218 市州通用-部门满意度明细：办件结果查询优化为多选
                  //.WhereIF(dto.OrgProcessingResults != null && dto.OrgProcessingResults.Any(),
                  //      x => dto.OrgProcessingResults.Contains(SqlFunc.JsonField(x.OrgProcessingResults, "Key")))
                  //.WhereIF(dto.OrgHandledAttitude != null && dto.OrgHandledAttitude.Any(),
                  //     x => dto.OrgHandledAttitude.Contains(SqlFunc.JsonField(x.OrgHandledAttitude, "Key")))
                  .WhereIF(dto.OrgProcessingResults != null && dto.OrgProcessingResults.Any(),
                    dto.AttitudeType == EAttitudeType.ProcessingResult ?
                    x => dto.OrgProcessingResults.Contains(SqlFunc.JsonField(x.OrgProcessingResults, "Key")) :
                    x => dto.OrgProcessingResults.Contains(SqlFunc.JsonField(x.OrgHandledAttitude, "Key")))

                .WhereIF(!string.IsNullOrEmpty(dto.VisitUser), x => x.OrderVisit.Employee.Name.Contains(dto.VisitUser))
                .WhereIF(!string.IsNullOrEmpty(dto.No), x => x.OrderVisit.Order.No == dto.No)
                .WhereIF(!string.IsNullOrEmpty(dto.Title), x => x.OrderVisit.Order.Title.Contains(dto.Title))
                .WhereIF(!string.IsNullOrEmpty(dto.OrgId), x => x.VisitOrgCode == dto.OrgId)//接办部门
                .WhereIF(!string.IsNullOrEmpty(dto.Hotspot), x => x.OrderVisit.Order.HotspotSpliceName != null && x.OrderVisit.Order.HotspotSpliceName.Contains(dto.Hotspot))//热点类型
                .WhereIF(!string.IsNullOrEmpty(dto.Channel), x => x.OrderVisit.Order.SourceChannelCode == dto.Channel)//受理类型
                .WhereIF(dto.CreationTimeStart.HasValue, x => x.OrderVisit.Order.CreationTime >= dto.CreationTimeStart) //受理时间开始
                .WhereIF(dto.CreationTimeEnd.HasValue, x => x.OrderVisit.Order.CreationTime <= dto.CreationTimeEnd) //受理时间结束
                .WhereIF(dto.ActualHandleTimeStart.HasValue, x => x.OrderVisit.Order.ActualHandleTime >= dto.ActualHandleTimeStart) //办结时间开始
                .WhereIF(dto.ActualHandleTimeEnd.HasValue, x => x.OrderVisit.Order.ActualHandleTime <= dto.ActualHandleTimeEnd)//办结时间结束
                .WhereIF(dto.VisitTimeStart.HasValue, x => x.OrderVisit.VisitTime >= dto.VisitTimeStart) //回访时间
                .WhereIF(dto.VisitTimeEnd.HasValue, x => x.OrderVisit.VisitTime < dto.VisitTimeEnd) //回访时间
                                                                                                    //特殊需求：要求新增查询条件【一级部门】，如查询【富顺县人民政府】则显示该一级部门以及下级部门的工单数据
                .WhereIF(!string.IsNullOrEmpty(dto.LevelOneOrg), x => x.OrderVisit.Order.OrgLevelOneName == dto.LevelOneOrg || x.OrderVisit.Order.OrgLevelOneCode == dto.LevelOneOrg)

                .Where(x => SqlFunc.JsonField(x.OrgProcessingResults, "Key") != "2" && SqlFunc.JsonField(x.OrgProcessingResults, "Key") != "-1" && SqlFunc.JsonField(x.OrgProcessingResults, "Key") != "0" && SqlFunc.JsonField(x.OrgProcessingResults, "Key") != "7")
                .Where(x => x.VisitOrgCode.StartsWith(orgId))
                .Select(x => new OrgVisitDetailListResp()
                {
                    Id = x.Id,
                    OrderId = x.OrderVisit.Order.Id,
                    VisitId = x.OrderVisit.Id,
                    No = x.OrderVisit.No,
                    ReTransactNum = x.OrderVisit.Order.ReTransactNum,
                    OrderScreenStatus = SqlFunc.Subqueryable<OrderScreen>().Where(q => q.OrderId == x.OrderVisit.OrderId && q.VisitDetailId == x.Id).OrderByDesc(q => q.CreationTime).Select(q => q.Status),
                    VisitContent = x.VisitContent,
                    HotspotSpliceName = x.OrderVisit.Order.HotspotSpliceName,
                    OrgLevelOneName = x.OrderVisit.Order.OrgLevelOneName,
                    CurrentHandleOrgName = x.OrderVisit.Order.CurrentHandleOrgName,
                    CreationTime = x.OrderVisit.Order.CreationTime,
                    Title = x.OrderVisit.Order.Title,
                    VisitUser = x.OrderVisit.Employee.Name,
                    VisitType = x.OrderVisit.VisitType,
                    VisitTime = x.OrderVisit.VisitTime,
                    OrgProcessingResults = dto.AttitudeType == EAttitudeType.ProcessingResult ? SqlFunc.JsonField(x.OrgProcessingResults, "Value")
                    : SqlFunc.JsonField(x.OrgHandledAttitude, "Value"),
                    Content = x.OrderVisit.Order.Content,
                    FileOpinion = x.OrderVisit.Order.FileOpinion,
                    FiledTime = x.OrderVisit.Order.FiledTime,
                    VisitOrgName = x.VisitOrgName,
                    ActualHandleOrgName = x.OrderVisit.Order.ActualHandleOrgName
                }).MergeTable()
                .OrderByIF(string.IsNullOrEmpty(dto.SortField), x => x.VisitTime, OrderByType.Desc)
                .OrderByIF(dto is { SortField: "creationTime", SortRule: 0 }, x => x.CreationTime, OrderByType.Asc) //受理时间升序
                .OrderByIF(dto is { SortField: "creationTime", SortRule: 1 }, x => x.CreationTime, OrderByType.Desc) //受理时间降序
                .OrderByIF(dto is { SortField: "visitTime", SortRule: 0 }, x => x.VisitTime, OrderByType.Asc) //回访时间升序
                .OrderByIF(dto is { SortField: "visitTime", SortRule: 1 }, x => x.VisitTime, OrderByType.Desc) //回访时间降序
                .OrderByIF(dto is { SortField: "filedTime", SortRule: 0 }, x => x.FiledTime, OrderByType.Asc) //办结时间升序
                .OrderByIF(dto is { SortField: "filedTime", SortRule: 1 }, x => x.FiledTime, OrderByType.Desc) //办结时间降序
                ;

        }

        /// <summary>
        /// 智能回访数据统计
        /// </summary>
        /// <param name="dto"></param>
        /// <returns></returns>
        public async Task<QueryAiVisitStatisticsResp> QueryAiVisitStatistics(QueryAiVisitStatisticsRequest dto)
        {
            var list = await _aiOrderVisitDetailRepository.Queryable().Includes(x => x.OrderVisit).Where(x => x.CreationTime >= dto.StartTime && x.CreationTime <= dto.EndTime && x.OrderVisit.VisitState == EVisitState.Visited).ToListAsync();
            return new QueryAiVisitStatisticsResp()
            {
                AiVisitCount = list.Count,
                AiVisitSuccessCount = list.Where(x => x.OrderVisit.IsEffectiveAiVisit == true).Count(), // SqlFunc.AggregateSum(SqlFunc.IIF(x.OrderVisit.IsEffectiveAiVisit == true, 1, 0)),
                AiVisitArtificialReviewCount = list.Where(x => x.OrderVisit.IsEffectiveAiVisit == false).Count(),
                AiVisitCallReviewCount = list.Where(x => x.OrderVisit.IsEffectiveAiVisit == false && !string.IsNullOrEmpty(x.OrderVisit.CallId)).Count(),
                AiVisitHandReviewCount = list.Where(x => x.OrderVisit.IsEffectiveAiVisit == false && string.IsNullOrEmpty(x.OrderVisit.CallId)).Count()
            };
        }

        /// <summary>
        /// 智能回访有效性分析
        /// </summary>
        /// <param name="dto"></param>
        /// <returns></returns>
        public async Task<QueryAiVisitEffectiveAnalysisResp> QueryAiVisitEffectiveAnalysis(QueryAiVisitStatisticsRequest dto)
        {
            var list = await _aiOrderVisitDetailRepository.Queryable().Includes(x => x.OrderVisit).Where(x => x.CreationTime >= dto.StartTime && x.CreationTime <= dto.EndTime && x.OrderVisit.VisitState == EVisitState.Visited).ToListAsync();
            return new QueryAiVisitEffectiveAnalysisResp()
            {
                AiVisitCount = list.Count,
                AiVisitSuccessCount = list.Where(x => x.OrderVisit.IsEffectiveAiVisit == true).Count(),
                AiVisitCallTwoFailCount = list.Where(x => x.CallTimes >= 1 && x.AiIsContact == null && x.AiVolved == null && (x.AiOrgProcessingResults == null || x.AiOrgProcessingResults?.Key == null)).Count(),
                AiVisitHaveOneAnswerCount = list.Where(x => x.OrderVisit.IsEffectiveAiVisit == false &&
                                                                            (x.AiIsContact != null && x.AiVolved == null && (x.AiOrgProcessingResults == null || x.AiOrgProcessingResults.Key == null)) ||
                                                                            (x.AiIsContact == null && x.AiVolved != null && (x.AiOrgProcessingResults == null || x.AiOrgProcessingResults?.Key == null)) ||
                                                                            (x.AiIsContact == null && x.AiVolved == null && (x.AiOrgProcessingResults != null || x.AiOrgProcessingResults?.Key != null))).Count(), //只有一个答案
                AiVisitHaveTwoAnswerCount = list.Where(x => x.OrderVisit.IsEffectiveAiVisit == false &&
                                                                            (x.AiIsContact != null && x.AiVolved != null && (x.AiOrgProcessingResults == null || x.AiOrgProcessingResults?.Key == null)) ||
                                                                            (x.AiIsContact != null && x.AiVolved == null && (x.AiOrgProcessingResults != null || x.AiOrgProcessingResults?.Key != null)) ||
                                                                            (x.AiIsContact == null && x.AiVolved != null && (x.AiOrgProcessingResults != null || x.AiOrgProcessingResults?.Key != null))).Count(),//只有两个答案
                AiVisitHaveThreeAnswerCount = list.Where(x => x.OrderVisit.IsEffectiveAiVisit == false && x.AiIsContact != null && x.AiVolved != null && (x.AiOrgProcessingResults != null || x.AiOrgProcessingResults?.Key != null)).Count()//有三个答案
            };
        }

    }

    public class OrderScreenRepository : BaseRepositoryWorkflow<OrderScreen>, IOrderScreenRepository, IScopeDependency
    {
        public OrderScreenRepository(ISugarUnitOfWork<HotlineDbContext> uow, IDataPermissionFilterBuilder dataPermissionFilterBuilder) : base(uow, dataPermissionFilterBuilder)
        {
        }
    }

    public class OrderTerminateRepository : BaseRepositoryWorkflow<OrderTerminate>, IOrderTerminateRepository, IScopeDependency
    {
        public OrderTerminateRepository(ISugarUnitOfWork<HotlineDbContext> uow, IDataPermissionFilterBuilder dataPermissionFilterBuilder) : base(uow, dataPermissionFilterBuilder)
        {
        }
    }

    public class OrderDelayRepository : BaseRepositoryWorkflow<OrderDelay>, IOrderDelayRepository, IScopeDependency
    {
        public OrderDelayRepository(ISugarUnitOfWork<HotlineDbContext> uow, IDataPermissionFilterBuilder dataPermissionFilterBuilder) : base(uow, dataPermissionFilterBuilder)
        {
        }
    }

}