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;

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).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);
        }
        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 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;

            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)
                .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) //回访方式
                .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 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)
                .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
                }).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(!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)
        {
        }
    }

}