hotline/src/Hotline.Api/Controllers/Bi/BiOrderController.cs

using Hotline.Application.Orders;
using Hotline.Application.StatisticalReport;
using Hotline.Caching.Interfaces;
using Hotline.CallCenter.Calls;
using Hotline.FlowEngine.Workflows;
using Hotline.Orders;
using Hotline.Repository.SqlSugar.Extensions;
using Hotline.Schedulings;
using Hotline.Settings;
using Hotline.Settings.Hotspots;
using Hotline.Settings.TimeLimits;
using Hotline.Share.Dtos;
using Hotline.Share.Dtos.Bi;
using Hotline.Share.Dtos.Bigscreen;
using Hotline.Share.Dtos.CallCenter;
using Hotline.Share.Dtos.FlowEngine;
using Hotline.Share.Dtos.Order;
using Hotline.Share.Enums.CallCenter;
using Hotline.Share.Enums.FlowEngine;
using Hotline.Share.Enums.Order;
using Hotline.Share.Requests;
using Hotline.Tools;
using MapsterMapper;
using Microsoft.AspNetCore.Mvc;
using SqlSugar;
using System.Data;
using XF.Domain.Authentications;
using XF.Domain.Exceptions;
using XF.Domain.Repository;
using XF.Utility.EnumExtensions;

namespace Hotline.Api.Controllers.Bi
{
    public class BiOrderController : BaseController
    {
        private readonly IOrderRepository _orderRepository;
        private readonly IRepository<Hotspot> _hotspotTypeRepository;
        private readonly ISystemDicDataCacheManager _sysDicDataCacheManager;
        private readonly IRepository<OrderVisitDetail> _orderVisitDetailRepository;
        private readonly IRepository<OrderDelay> _orderDelayRepository;
        private readonly IMapper _mapper;
        private readonly IRepository<WorkflowCountersign> _workflowCountersignRepository;
        private readonly IRepository<OrderSpecial> _orderSpecialRepository;
        private readonly IRepository<OrderVisit> _orderVisitRepository;
        private readonly IRepository<TrCallRecord> _trCallRecordRepository;
        private readonly IRepository<OrderPublish> _orderPublishRepository;
        private readonly ISystemOrganizeRepository _systemOrganizeRepository;
        private readonly IRepository<AiOrderVisitDetail> _aiOrderVisitDetailRepository;
        private readonly ISessionContext _sessionContext;
        private readonly ISystemSettingCacheManager _systemSettingCacheManager;
        private readonly IRepository<OrderSpecialDetail> _orderSpecialDetailRepository;
        private readonly IRepository<WorkflowTrace> _workflowTraceRepository;
        private readonly IRepository<OrderScreen> _orderScreenRepository;
        private readonly IOrderSecondaryHandlingApplication _orderSecondaryHandlingApplication;
        private readonly IOrderApplication _orderApplication;
        private readonly ITimeLimitDomainService _timeLimitDomainService;
        private readonly IOrderReportApplication _orderReportApplication;
        private readonly IRepository<SystemArea> _systemAreaRepository;
        private readonly IRepository<Hotspot> _hotspotRepository;
        private readonly IRepository<SystemDicData> _systemDicDataRepository;
        private readonly ISystemDicDataCacheManager _systemDicDataCacheManager;

        public BiOrderController(
            IOrderRepository orderRepository,
            IRepository<Hotspot> hotspotTypeRepository,
            ISystemDicDataCacheManager sysDicDataCacheManager,
            IRepository<OrderVisitDetail> orderVisitDetailRepository,
            IRepository<OrderDelay> orderDelayRepository,
            IRepository<WorkflowCountersign> workflowCountersignRepository,
            IRepository<OrderSpecial> orderSpecialRepository,
            IMapper mapper,
            IRepository<OrderVisit> orderVisitRepository,
            IRepository<TrCallRecord> trCallRecordRepository,
            IRepository<OrderPublish> orderPublishRepository,
            ISystemOrganizeRepository systemOrganizeRepository,
            IRepository<AiOrderVisitDetail> aiOrderVisitDetailRepository,
            ISessionContext sessionContext,
            ISystemSettingCacheManager systemSettingCacheManager,
            IRepository<OrderSpecialDetail> orderSpecialDetailRepository,
            IRepository<WorkflowTrace> workflowTraceRepository,
            IRepository<OrderScreen> orderScreenRepository,
            IOrderSecondaryHandlingApplication orderSecondaryHandlingApplication,
            IOrderApplication orderApplication,
            ITimeLimitDomainService timeLimitDomainService,
            IOrderReportApplication orderReportApplication,
            IRepository<SystemArea> systemAreaRepository,
            IRepository<Hotspot> hotspotRepository,
             IRepository<SystemDicData> systemDicDataRepository,
            ISystemDicDataCacheManager systemDicDataCacheManager
            )
        {
            _orderRepository = orderRepository;
            _hotspotTypeRepository = hotspotTypeRepository;
            _sysDicDataCacheManager = sysDicDataCacheManager;
            _orderVisitDetailRepository = orderVisitDetailRepository;
            _orderDelayRepository = orderDelayRepository;
            _workflowCountersignRepository = workflowCountersignRepository;
            _orderSpecialRepository = orderSpecialRepository;
            _mapper = mapper;
            _orderVisitRepository = orderVisitRepository;
            _trCallRecordRepository = trCallRecordRepository;
            _orderPublishRepository = orderPublishRepository;
            _systemOrganizeRepository = systemOrganizeRepository;
            _aiOrderVisitDetailRepository = aiOrderVisitDetailRepository;
            _sessionContext = sessionContext;
            _systemSettingCacheManager = systemSettingCacheManager;
            _orderSpecialDetailRepository = orderSpecialDetailRepository;
            _workflowTraceRepository = workflowTraceRepository;
            _orderScreenRepository = orderScreenRepository;
            _orderSecondaryHandlingApplication = orderSecondaryHandlingApplication;
            _orderApplication = orderApplication;
            _timeLimitDomainService = timeLimitDomainService;
            _orderReportApplication = orderReportApplication;
            _systemAreaRepository = systemAreaRepository;
            _hotspotRepository = hotspotRepository;
            _systemDicDataRepository = systemDicDataRepository;
            _systemDicDataCacheManager = systemDicDataCacheManager;
        }

        /// <summary>
        /// 部门超期统计明细
        /// </summary>
        /// <param name="dto"></param>
        /// <returns></returns>
        [HttpGet("org_data_list_detail")]
        public async Task<PagedDto<OrderDto>> OrgDataListDetail([FromQuery] OrgDataListDetailRequest dto)
        {
            var quer = _orderApplication.QueryOrgDataListDetail(dto);
            var (total, items) = await quer.ToPagedListAsync(dto.PageIndex, dto.PageSize);

            return new PagedDto<OrderDto>(total, _mapper.Map<IReadOnlyList<OrderDto>>(items));
        }

        /// <summary>
        /// 部门超期统计明细导出
        /// </summary>
        /// <returns></returns>
        [HttpPost("org_data_list_detail/_export")]
        public async Task<FileStreamResult> OrgDataListDetailExport([FromBody] ExportExcelDto<OrgDataListDetailRequest> dto)
        {
            var query = _orderApplication.QueryOrgDataListDetail(dto.QueryDto);
            List<Order> data;
            if (dto.IsExportAll)
            {
                data = await query.ToListAsync(HttpContext.RequestAborted);
            }
            else
            {
                var (_, items) = await query.ToPagedListAsync(dto.QueryDto, HttpContext.RequestAborted);
                data = items;
            }

            var dataDtos = _mapper.Map<ICollection<OrderDto>>(data);

            dynamic? dynamicClass = DynamicClassHelper.CreateDynamicClass(dto.ColumnInfos);

            var dtos = dataDtos
                .Select(stu => _mapper.Map(stu, typeof(OrderDto), dynamicClass))
                .Cast<object>()
                .ToList();

            var stream = ExcelHelper.CreateStream(dtos);

            return ExcelStreamResult(stream, "部门超期统计明细数据");
        }

        /// <summary>
        /// 全量部门超期统计明细
        /// </summary>
        /// <param name="dto"></param>
        /// <returns></returns>
        [HttpGet("org_data_list_detail_all")]
        public async Task<PagedDto<OrderDto>> OrgDataListDetail([FromQuery] OrgDataListAllDetailRequest dto)
        {
            var quer = _orderApplication.QueryOrgDataListDetail(dto);
            var (total, items) = await quer.ToPagedListAsync(dto.PageIndex, dto.PageSize);

            return new PagedDto<OrderDto>(total, _mapper.Map<IReadOnlyList<OrderDto>>(items));
        }

        /// <summary>
        /// 部门超期统计明细导出
        /// </summary>
        /// <returns></returns>
        [HttpPost("org_data_list_detail_all/_export")]
        public async Task<FileStreamResult> OrgDataListDetailExport([FromBody] ExportExcelDto<OrgDataListAllDetailRequest> dto)
        {
            var query = _orderApplication.QueryOrgDataListDetail(dto.QueryDto);
            List<Order> data;
            if (dto.IsExportAll)
            {
                data = await query.ToListAsync(HttpContext.RequestAborted);
            }
            else
            {
                var (_, items) = await query.ToPagedListAsync(dto.QueryDto, HttpContext.RequestAborted);
                data = items;
            }

            var dataDtos = _mapper.Map<ICollection<OrderDto>>(data);

            dynamic? dynamicClass = DynamicClassHelper.CreateDynamicClass(dto.ColumnInfos);

            var dtos = dataDtos
                .Select(stu => _mapper.Map(stu, typeof(OrderDto), dynamicClass))
                .Cast<object>()
                .ToList();

            var stream = ExcelHelper.CreateStream(dtos);

            return ExcelStreamResult(stream, "部门超期统计明细数据");
        }

        /// <summary>
        /// 列表页面基础数据
        /// </summary>
        /// <returns></returns>
        [HttpGet("org_data_list_detail_all/base-data")]
        public async Task<object> BaseData()
        {
            var rsp = new
            {
                OrderStatusOptions = EnumExts.GetDescriptions<EOrderStatus>()
            };
            return rsp;
        }

        /// <summary>
        /// 部门超期统计
        /// </summary>
        /// <param name="dto"></param>
        /// <returns></returns>
        [HttpGet("org_data_list")]
        public async Task<PagedDto<OrderBiOrgDataListVo>> OrgDataList([FromQuery] ReportPagedRequest dto)
        {
            var query = _orderApplication.QueryOrgDataList(dto);
            var (total, items) = await query.ToPagedListAsync(dto, HttpContext.RequestAborted);
            items.Add(new OrderBiOrgDataListVo
            {
                OrgName = "合计",
                HandlerExtendedNum = items.Select(s => s.HandlerExtendedNum).Sum(),
                CounterHandlerExtendedNum = items.Select(s => s.CounterHandlerExtendedNum).Sum(),
                NoHandlerExtendedNum = items.Select(s => s.NoHandlerExtendedNum).Sum(),
                CounterNoHandlerExtendedNum = items.Select(s => s.CounterNoHandlerExtendedNum).Sum(),
            });
            return new PagedDto<OrderBiOrgDataListVo>(total, items);
        }

        /// <summary>
        /// 部门超期统计导出
        /// </summary>
        /// <returns></returns>
        [HttpPost("org_data_list/_export")]
        public async Task<FileStreamResult> OrgDataListExport([FromBody] ExportExcelDto<ReportPagedRequest> dto)
        {
            var query = _orderApplication.QueryOrgDataList(dto.QueryDto);
            List<OrderBiOrgDataListVo> data;
            data = await query.ToListAsync(HttpContext.RequestAborted);
            data.Add(new OrderBiOrgDataListVo
            {
                OrgName = "合计",
                HandlerExtendedNum = data.Select(s => s.HandlerExtendedNum).Sum(),
                CounterHandlerExtendedNum = data.Select(s => s.CounterHandlerExtendedNum).Sum(),
                NoHandlerExtendedNum = data.Select(s => s.NoHandlerExtendedNum).Sum(),
                CounterNoHandlerExtendedNum = data.Select(s => s.CounterNoHandlerExtendedNum).Sum(),
            });
            dynamic? dynamicClass = DynamicClassHelper.CreateDynamicClass(dto.ColumnInfos);
            var dtos = data
             .Select(stu => _mapper.Map(stu, typeof(OrderBiOrgDataListVo), dynamicClass))
             .Cast<object>()
             .ToList();

            var stream = ExcelHelper.CreateStream(dtos);

            return ExcelStreamResult(stream, "部门超期统计数据");
        }

        /// <summary>
        /// 话务员办件统计
        /// </summary>
        /// <param name="dto"></param>
        /// <returns></returns>
        [HttpGet("centre_data_list")]
        public async Task<List<OrderBiCentreDataListVo>> CentreDataList([FromQuery] ReportPagedRequest dto)
        {
            if (!dto.StartTime.HasValue || !dto.EndTime.HasValue) throw UserFriendlyException.SameMessage("请选择时间!");

            dto.EndTime = dto.EndTime.Value.AddDays(1).AddSeconds(-1);

            var query = _orderRepository.Queryable(false, false, false)
                .WhereIF(dto.StartTime.HasValue, it => it.CreationTime >= dto.StartTime)
                .WhereIF(dto.EndTime.HasValue, it => it.CreationTime <= dto.EndTime)
                .WhereIF(!string.IsNullOrEmpty(dto.Keyword), it => it.SignerName.Contains(dto.Keyword!))
                .GroupBy(it => new { it.SignerId, it.SignerName })
                .Select(it => new OrderBiCentreDataListVo
                {
                    UserName = it.SignerName,
                    UserId = it.SignerId,
                    //Subtotal = SqlFunc.AggregateCount(x.AcceptorId),
                    CentreArchive = SqlFunc.AggregateSum(SqlFunc.IIF(it.Status >= EOrderStatus.Filed && it.ProcessType == EProcessType.Zhiban && it.AcceptType != "无效", 1, 0)), //中心归档件
                    //CentreCareOf = SqlFunc.AggregateSum(SqlFunc.IIF(it.Status >= EOrderStatus.Filed && (it.FileUserRole == EFileUserType.Org || it.FileUserRole == EFileUserType.Dispatch), 1, 0)), //转办信件
                    CentreCareOf = SqlFunc.AggregateSum(SqlFunc.IIF(it.AcceptType != "无效" && (it.ProcessType == EProcessType.Jiaoban || (it.ActualHandleStepName == "派单组" && it.Status < EOrderStatus.Filed) || (it.ActualHandleStepName == "班长审批" && it.Status < EOrderStatus.Filed)), 1, 0)),
                    NoCentreCareOf = SqlFunc.AggregateSum(SqlFunc.IIF(it.AcceptType != "无效" && (it.Status <= EOrderStatus.SpecialToUnAccept), 1, 0)), //坐席待办 //中心领导?市领导? 是否在统计条件中
                    //CentreCareOf = SqlFunc.AggregateSum(SqlFunc.IIF(it.Status >= EOrderStatus.Filed && it.ProcessType == EProcessType.Jiaoban, 1, 0)),
                    //NoCentreCareOf = SqlFunc.AggregateSum(SqlFunc.IIF((int)x.Status < 300 && x.ExpiredTime > x.FiledTime, 1, 0)),
                    Invalid = SqlFunc.AggregateSum(SqlFunc.IIF(it.AcceptType == "无效", 1, 0)),
                    Repeat = SqlFunc.AggregateSum(SqlFunc.IIF(it.DuplicateIds != null && SqlFunc.JsonArrayLength(it.DuplicateIds) > 0, 1, 0)),
                    Subtotal = SqlFunc.AggregateSum(SqlFunc.IIF((it.Status >= EOrderStatus.Filed && it.ProcessType == EProcessType.Zhiban && it.AcceptType != "无效") || (it.AcceptType != "无效" && (it.ProcessType == EProcessType.Jiaoban || (it.ActualHandleStepName == "派单组" && it.Status < EOrderStatus.Filed) || (it.ActualHandleStepName == "班长审批" && it.Status < EOrderStatus.Filed))) || (it.Status <= EOrderStatus.SpecialToUnAccept) || it.AcceptType == "无效" || (it.DuplicateIds != null && SqlFunc.JsonArrayLength(it.DuplicateIds) > 0), 1, 0))
                }).MergeTable();
            switch (dto.SortField)
            {
                case "centreArchive":
                    query = dto.SortRule is 0 ? query.OrderBy(x => x.CentreArchive) : query.OrderByDescending(x => x.CentreArchive);
                    break;
                case "centreCareOf":
                    query = dto.SortRule is 0 ? query.OrderBy(x => x.CentreCareOf) : query.OrderByDescending(x => x.CentreCareOf);
                    break;
                case "noCentreCareOf":
                    query = dto.SortRule is 0 ? query.OrderBy(x => x.NoCentreCareOf) : query.OrderByDescending(x => x.NoCentreCareOf);
                    break;
                case "invalid":
                    query = dto.SortRule is 0 ? query.OrderBy(x => x.Invalid) : query.OrderByDescending(x => x.Invalid);
                    break;
                case "repeat":
                    query = dto.SortRule is 0 ? query.OrderBy(x => x.Repeat) : query.OrderByDescending(x => x.Repeat);
                    break;
                case "subtotal":
                    query = dto.SortRule is 0 ? query.OrderBy(x => x.Subtotal) : query.OrderByDescending(x => x.Subtotal);
                    break;
            }

            if (string.IsNullOrEmpty(dto.SortField))
            {
                query = query.OrderByDescending(x => x.Subtotal);
            }

            var list = await query.Where(x => (x.CentreArchive + x.CentreCareOf + x.NoCentreCareOf + x.Invalid + x.Repeat) != 0).ToListAsync(HttpContext.RequestAborted);
            return list;
        }

        /// <summary>
        /// 热点数据小计统计
        /// </summary>
        /// <param name="dto"></param>
        /// <returns></returns>
        [HttpGet("hotspot_subtotal_data_list")]
        public async Task<PagedDto<HotspotDataLsitVo>> HotspotSubtotalDataLsit([FromQuery] HotspotSubtotalReportPagedRequest dto)
        {
            if (!dto.StartTime.HasValue || !dto.EndTime.HasValue) throw UserFriendlyException.SameMessage("请选择时间!");

            dto.EndTime = dto.EndTime.Value.AddDays(1).AddSeconds(-1);

            var query = _hotspotTypeRepository.Queryable(false, true)
                .LeftJoin<Order>((x, o) => o.HotspotSpliceName != null && (x.HotSpotFullName == o.HotspotSpliceName || o.HotspotSpliceName.Contains(x.HotSpotFullName)) && o.IsDeleted == false)
                .WhereIF(dto.StartTime.HasValue, (x, o) => o.CreationTime >= dto.StartTime)
                .WhereIF(dto.EndTime.HasValue, (x, o) => o.CreationTime <= dto.EndTime)
                .WhereIF(!string.IsNullOrEmpty(dto.Keyword), (x, o) => x.HotSpotName.Contains(dto.Keyword!))
                .Where((x, o) => x.ParentId == dto.Id)
                .Where((x, o) => x.IsDeleted == false)
                .GroupBy((x, o) => new { x.Id, x.HotSpotName })
                .Select((x, o) => new HotspotDataLsitVo
                {
                    Id = x.Id,
                    Name = x.HotSpotName,
                    Num = SqlFunc.AggregateSum(SqlFunc.IIF(o.Id != null, 1, 0)),
                }).MergeTable();
            var (total, items) = await query.ToPagedListAsync(dto, HttpContext.RequestAborted);
            return new PagedDto<HotspotDataLsitVo>(total, items);
        }

        /// <summary>
        /// 热点数据统计
        /// </summary>
        /// <param name="dto"></param>
        /// <returns></returns>
        [HttpGet("hotspot_data_list")]
        public async Task<object> HotspotDataLsit([FromQuery] HotspotReportPagedRequest dto)
        {
            if (!dto.StartTime.HasValue || !dto.EndTime.HasValue) throw UserFriendlyException.SameMessage("请选择时间!");
            if (dto.Type == 0 && (!dto.ChainStartTime.HasValue || !dto.ChainEndTime.HasValue)) throw UserFriendlyException.SameMessage("请选择环比时间!");

            dto.EndTime = dto.EndTime.Value.AddDays(1).AddSeconds(-1);

            var IsCenter = _sessionContext.OrgIsCenter;

            if (dto.Type == 0)
            {
                dto.ChainEndTime = dto.ChainEndTime.Value.AddDays(1).AddSeconds(-1);
            }

            var items = await _hotspotTypeRepository.Queryable(false, true)
                .LeftJoin<Order>((x, o) => o.HotspotSpliceName != null && (x.HotSpotName == o.HotspotSpliceName || o.HotspotSpliceName.Contains(x.HotSpotName)) && o.IsDeleted == false)
                .WhereIF(dto.StartTime.HasValue, (x, o) => o.CreationTime >= dto.StartTime)
                .WhereIF(dto.EndTime.HasValue, (x, o) => o.CreationTime <= dto.EndTime)
                .WhereIF(!string.IsNullOrEmpty(dto.Keyword), (x, o) => x.HotSpotName.Contains(dto.Keyword!))
                .WhereIF(IsCenter == false, (x, o) => o.ActualHandleOrgCode == _sessionContext.RequiredOrgId)
                .Where((x, o) => x.ParentId == dto.Id)
                .Where((x, o) => x.IsDeleted == false)
                .GroupBy((x, o) => new { x.Id, x.HotSpotName })
                .Select((x, o) => new HotspotDataLsitVo
                {
                    Id = x.Id,
                    Name = x.HotSpotName,
                    Num = SqlFunc.AggregateSum(SqlFunc.IIF(o.Id != null, 1, 0)),
                    Sublevel = SqlFunc.AggregateSum(SqlFunc.IIF(x.HotSpotName != o.HotspotName, 1, 0)) > 0,
                }).MergeTable().ToListAsync();
            var chainStartTime = dto.StartTime;
            var chainEndTime = dto.EndTime;
            switch (dto.Type)
            {
                case 1://日
                    chainStartTime = dto.StartTime.Value.AddDays(-1);
                    chainEndTime = dto.EndTime.Value.AddDays(-1);
                    break;
                case 2://月
                    chainStartTime = dto.StartTime.Value.AddMonths(-1);
                    chainEndTime = dto.EndTime.Value.AddMonths(-1);
                    break;
                case 3://年
                    chainStartTime = dto.StartTime.Value.AddYears(-1);
                    chainEndTime = dto.EndTime.Value.AddYears(-1);
                    break;
                case 0:
                    chainStartTime = dto.ChainStartTime.Value;
                    chainEndTime = dto.ChainEndTime.Value;
                    break;
            }
            var chainItems = await _hotspotTypeRepository.Queryable(false, true)
                .LeftJoin<Order>((x, o) => o.HotspotSpliceName != null && (x.HotSpotName == o.HotspotSpliceName || o.HotspotSpliceName.Contains(x.HotSpotName)) && o.IsDeleted == false)
                .WhereIF(dto.StartTime.HasValue, (x, o) => o.CreationTime >= chainStartTime)
                .WhereIF(dto.EndTime.HasValue, (x, o) => o.CreationTime <= chainEndTime)
                .WhereIF(!string.IsNullOrEmpty(dto.Keyword), (x, o) => x.HotSpotName.Contains(dto.Keyword!))
                .WhereIF(IsCenter == false, (x, o) => o.ActualHandleOrgCode == _sessionContext.RequiredOrgId)
                .Where((x, o) => x.ParentId == dto.Id)
                .Where((x, o) => x.IsDeleted == false)
                .GroupBy((x, o) => new { x.Id, x.HotSpotName })
                .Select((x, o) => new
                {
                    Id = x.Id,
                    ChainNum = SqlFunc.AggregateSum(SqlFunc.IIF(o.Id != null, 1, 0)),
                }).MergeTable().ToListAsync();
            var res = (from t1 in items
                       join t2 in chainItems on t1.Id equals t2.Id into t1_t2
                       from item in t1_t2.DefaultIfEmpty()
                       select new
                       {
                           Id = t1.Id,
                           Name = t1.Name,
                           Num = t1.Num,
                           Sublevel = t1.Sublevel,
                           Children = new List<HotspotDataLsitVo>(),
                           ChainNum = t1_t2.Select(x => x.ChainNum).FirstOrDefault(),
                           ChainRate = t1_t2.Select(x => x.ChainNum).FirstOrDefault() > 0 ?
                           ((double.Parse(t1.Num.ToString()) - double.Parse(t1_t2.Select(x => x.ChainNum).FirstOrDefault().ToString())) / double.Parse(t1_t2.Select(x => x.ChainNum).FirstOrDefault().ToString()) * 100).ToString("F2") + "%" : "100.00%",
                       }).ToList();
            var total = new
            {
                Id = "0",
                Name = "合计",
                Num = res.Sum(x => x.Num),
                Sublevel = false,
                Children = new List<HotspotDataLsitVo>(),
                ChainNum = res.Sum(x => x.ChainNum),
                ChainRate = res.Sum(x => x.ChainNum) > 0 ? ((double.Parse(res.Sum(x => x.Num).ToString()) - double.Parse(res.Sum(x => x.ChainNum).ToString())) / double.Parse(res.Sum(x => x.ChainNum).ToString()) * 100).ToString("F2") + "%" : "100.00%"
            };
            return new { List = res, Total = total };
        }

        /// <summary>
        /// 部门不满意统计
        /// 已加验证部门
        /// </summary>
        /// <param name="dto"></param>
        /// <returns></returns>
        [HttpGet("visit-nosatisfied")]
        public async Task<object> QueryVisitNoSatisfied([FromQuery] QueryVisitNoSatiisfiedRequest dto)
        {
            if (!dto.StartTime.HasValue || !dto.EndTime.HasValue) throw UserFriendlyException.SameMessage("请选择时间!");

            dto.EndTime = dto.EndTime.Value.AddDays(1).AddSeconds(-1);

            var IsCenter = _sessionContext.OrgIsCenter;


            var dissatisfiedReason = _sysDicDataCacheManager.GetSysDicDataCache(SysDicTypeConsts.DissatisfiedReason);
            List<dynamic>? list = new List<dynamic>();
            //DataTable dt = new DataTable();
            foreach (var item in dissatisfiedReason)
            {
                var table = _orderVisitDetailRepository.Queryable()
                .Includes(x => x.OrderVisit)
                .Where(x => x.VisitTarget == Share.Enums.Order.EVisitTarget.Org)
                .Where(x => x.OrgNoSatisfiedReason != null)
                .Where(x => x.OrderVisit.VisitState == EVisitState.Visited)
                .Where(x => !string.IsNullOrEmpty(x.VisitOrgName))
                .WhereIF(!string.IsNullOrEmpty(dto.OrgName), x => x.VisitOrgName.Contains(dto.OrgName))
                .WhereIF(dto.StartTime.HasValue, x => x.OrderVisit.VisitTime >= dto.StartTime.Value)
                .WhereIF(dto.EndTime.HasValue, x => x.OrderVisit.VisitTime <= dto.EndTime.Value)
                .WhereIF(IsCenter == false, x => x.VisitOrgCode.StartsWith(_sessionContext.RequiredOrgId))
                .GroupBy(x => new { x.VisitOrgName, x.VisitOrgCode })
                .Select(x => new BiVisitNoSatisfiedDto
                {
                    Count = SqlFunc.AggregateSum(SqlFunc.IIF(SqlFunc.JsonListObjectAny(x.OrgNoSatisfiedReason, "Key", item.DicDataValue), 1, 0)),
                    Key = item.DicDataValue,
                    OrgName = x.VisitOrgName,
                    OrgCode = x.VisitOrgCode
                })
                .OrderByDescending(x => x.Count)
                //.ToPivotTable(x => x.Key, x => x.OrgName, x => x.Sum(x => x.Count));
                .ToPivotList(x => x.Key, x => new { x.OrgCode, x.OrgName }, x => x.Sum(x => x.Count));

                list.AddRange(table);
            }
            return new { DicReason = dissatisfiedReason, Data = list };
        }

        /// <summary>
        /// 部门不满意统计明细
        /// </summary>
        /// <param name="dto"></param>
        /// <returns></returns>
        [HttpGet("visit-nosatisfied-detail")]
        public async Task<PagedDto<OrderVisitDetailDto>> BiQueryVisitNoSatisfiedDetail([FromQuery] BiQueryVisitNoSatisfiedDetailDto dto)
        {
            if (!dto.StartTime.HasValue || !dto.EndTime.HasValue) throw UserFriendlyException.SameMessage("请选择时间!");

            dto.EndTime = dto.EndTime.Value.AddDays(1).AddSeconds(-1);

            var IsCenter = _sessionContext.OrgIsCenter;

            var (total, items) = await _orderVisitDetailRepository.Queryable()
                .Includes(x => x.OrderVisit, d => d.Order)
                .Includes(x => x.OrderVisit, d => d.Employee)
                .Where(x => x.VisitOrgCode == dto.OrgCode)
                .Where(x => x.OrderVisit.VisitState == EVisitState.Visited)
                .Where(x => x.OrderVisit.VisitTime >= dto.StartTime.Value)
                .Where(x => x.OrderVisit.VisitTime <= dto.EndTime.Value)
                .Where(x => SqlFunc.JsonListObjectAny(x.OrgNoSatisfiedReason, "Key", dto.DissatisfiedKey))
                .WhereIF(IsCenter == false, x => x.VisitOrgCode.StartsWith(_sessionContext.RequiredOrgId))
                .WhereIF(!string.IsNullOrEmpty(dto.Keyword), x => x.OrderVisit.Order.No.Contains(dto.Keyword) || x.OrderVisit.Order.Title.Contains(dto.Keyword))
                .OrderBy(x => x.OrderVisit.VisitTime)
                .ToPagedListAsync(dto.PageIndex, dto.PageSize, HttpContext.RequestAborted);


            return new PagedDto<OrderVisitDetailDto>(total, _mapper.Map<IReadOnlyList<OrderVisitDetailDto>>(items));
        }

        /// <summary>
        /// 部门延期统计
        /// </summary>
        /// <param name="dto"></param>
        /// <returns></returns>
        [HttpGet("order-delay-data-list")]
        public async Task<IReadOnlyList<BiOrderDelayDataDto>> QueryOrderDelayDataList([FromQuery] QueryOrderDelayDataListRequest dto)
        {
            return await _orderReportApplication.QueryOrderDelayDataList(dto).ToListAsync();

        }

        /// <summary>
        /// 部门延期统计---导出
        /// </summary>
        /// <param name="dto"></param>
        /// <returns></returns>
        [HttpPost("order-delay-data-list-export")]
        public async Task<FileStreamResult> ExportQueryOrderDelayDataList([FromBody] ExportExcelDto<QueryOrderDelayDataListRequest> dto)
        {
            var query = _orderReportApplication.QueryOrderDelayDataList(dto.QueryDto);
            List<BiOrderDelayDataDto> list;
            if (dto.IsExportAll)
            {
                list = await query.ToListAsync(HttpContext.RequestAborted);
            }
            else
            {
                var (_, items) = await query.ToPagedListAsync(dto.QueryDto, HttpContext.RequestAborted);
                list = items;
            }

            list.Add(new BiOrderDelayDataDto()
            {
                OrgName = "合计",
                OrgCode = "",
                AllTotal = list.Sum(p => p.AllTotal),
                PassTotal = list.Sum(p => p.PassTotal),
                NoPassTotal = list.Sum(p => p.NoPassTotal),
                ExaminingTotal = list.Sum(p => p.ExaminingTotal)
            });

            var orderDtos = _mapper.Map<ICollection<BiOrderDelayDataDto>>(list);

            dynamic? dynamicClass = DynamicClassHelper.CreateDynamicClass(dto.ColumnInfos);
            var dtos = orderDtos
                .Select(stu => _mapper.Map(stu, typeof(BiOrderDelayDataDto), dynamicClass))
                .Cast<object>()
                .ToList();

            var stream = ExcelHelper.CreateStream(dtos);
            return ExcelStreamResult(stream, "部门延期统计数据");
        }

        /// <summary>
        /// 部门延期统计明细
        /// </summary>
        /// <param name="dto"></param>
        /// <returns></returns>
        [HttpGet("order-delay-data-detail")]
        public async Task<PagedDto<OrderDelayDto>> QueryOrderDelayDataDetail([FromQuery] QueryOrderDelayDataDetailRequest dto)
        {
            var (total, items) = await _orderReportApplication.QueryOrderDelayDataDetail(dto).ToPagedListAsync(dto.PageIndex, dto.PageSize, HttpContext.RequestAborted);

            return new PagedDto<OrderDelayDto>(total, _mapper.Map<IReadOnlyList<OrderDelayDto>>(items));
        }

        /// <summary>
        /// 部门延期统计明细--导出
        /// </summary>
        /// <param name="dto"></param>
        /// <returns></returns>
        [HttpPost("order-delay-data-detail-export")]
        public async Task<FileStreamResult> ExportQueryOrderDelayDataDetail([FromBody] ExportExcelDto<QueryOrderDelayDataDetailRequest> dto)
        {
            var query = _orderReportApplication.QueryOrderDelayDataDetail(dto.QueryDto);
            List<OrderDelay> list;
            if (dto.IsExportAll)
            {
                list = await query.ToListAsync(HttpContext.RequestAborted);
            }
            else
            {
                var (_, items) = await query.ToPagedListAsync(dto.QueryDto, HttpContext.RequestAborted);
                list = items;
            }


            var orderDtos = _mapper.Map<ICollection<OrderDelayDto>>(list);

            dynamic? dynamicClass = DynamicClassHelper.CreateDynamicClass(dto.ColumnInfos);

            var dtos = orderDtos
                .Select(stu => _mapper.Map(stu, typeof(OrderDelayDto), dynamicClass))
                .Cast<object>()
                .ToList();

            var stream = ExcelHelper.CreateStream(dtos);
            return ExcelStreamResult(stream, "部门延期统计明细数据");
        }

        /// <summary>
        /// 特提统计
        /// </summary>
        /// <param name="dto"></param>
        /// <returns></returns>
        [HttpGet("special_data_list")]
        public async Task<PagedDto<OrderBiSpecialListVo>> SpecialDataList([FromQuery] ReportPagedRequest dto)
        {
            if (!dto.StartTime.HasValue || !dto.EndTime.HasValue) throw UserFriendlyException.SameMessage("请选择时间!");
            dto.EndTime = dto.EndTime.Value.AddDays(1).AddSeconds(-1);

            var IsCenter = _sessionContext.OrgIsCenter;


            var query = _orderSpecialRepository.Queryable()
                .WhereIF(dto.StartTime.HasValue, x => x.CreationTime >= dto.StartTime)
                .WhereIF(dto.EndTime.HasValue, x => x.CreationTime <= dto.EndTime)
                .WhereIF(IsCenter == false, x => x.OrgId.StartsWith(_sessionContext.RequiredOrgId))
                .GroupBy(x => new { x.Cause })
                .Select(x => new OrderBiSpecialListVo
                {
                    Cause = x.Cause,
                    OrderNum = SqlFunc.AggregateSum(SqlFunc.IIF(true, 1, 0)),
                    MaxSpecialTime = SqlFunc.AggregateMax(x.CreationTime),
                })
                .MergeTable();
            switch (dto.SortField)
            {
                case "cause":
                    query = dto.SortRule is 0 ? query.OrderBy(x => x.Cause) : query.OrderByDescending(x => x.Cause);
                    break;
                case "orderNum":
                    query = dto.SortRule is 0 ? query.OrderBy(x => x.OrderNum) : query.OrderByDescending(x => x.OrderNum);
                    break;
                case "maxSpecialTime":
                    query = dto.SortRule is 0 ? query.OrderBy(x => x.MaxSpecialTime) : query.OrderByDescending(x => x.MaxSpecialTime);
                    break;
            }
            var (total, items) = await query.ToPagedListAsync(dto, HttpContext.RequestAborted);
            return new PagedDto<OrderBiSpecialListVo>(total, items);
        }

        /// <summary>
        /// 获取工单特提信息列表
        /// </summary>
        /// <param name="dto"></param>
        /// <returns></returns>
        [HttpGet("special_data_list/list")]
        public async Task<PagedDto<OrderSpecialDto>> List([FromQuery] OrderSpecialListDto dto)
        {
            if (!dto.StartTime.HasValue || !dto.EndTime.HasValue) throw UserFriendlyException.SameMessage("请选择时间!");
            dto.EndTime = dto.EndTime.Value.AddDays(1).AddSeconds(-1);

            var IsCenter = _sessionContext.OrgIsCenter;
            var (total, items) = await _orderSpecialRepository.Queryable()
                .Includes(x => x.Order)
                .WhereIF(!string.IsNullOrEmpty(dto.Keyword),
                    x => x.Order.No.Contains(dto.Keyword!) || x.Order.Title.Contains(dto.Keyword!))
                .WhereIF(!string.IsNullOrEmpty(dto.Cause),
                    x => x.Cause != null && x.Cause.Equals(dto.Cause))
                .WhereIF(dto.StartTime.HasValue, x => x.CreationTime >= dto.StartTime)
                .WhereIF(dto.EndTime.HasValue, x => x.CreationTime <= dto.EndTime)
                .WhereIF(dto.State.HasValue, x => x.State == dto.State)
                .WhereIF(IsCenter == false, x => x.OrgId.StartsWith(_sessionContext.OrgId))
                .OrderByDescending(x => x.CreationTime)
                .ToPagedListAsync(dto.PageIndex, dto.PageSize, HttpContext.RequestAborted);
            return new PagedDto<OrderSpecialDto>(total, _mapper.Map<IReadOnlyList<OrderSpecialDto>>(items));
        }

        /// <summary>
        /// 受理类型前十
        /// </summary>
        /// <param name="dto"></param>
        /// <returns></returns>
        [HttpGet("accept_type_top10_list")]
        public async Task<PagedDto<AcceptTypeTop10Vo>> AcceptTypeTop10List([FromQuery] ReportPagedRequest dto)
        {
            if (!dto.StartTime.HasValue || !dto.EndTime.HasValue) throw UserFriendlyException.SameMessage("请选择时间!");
            dto.EndTime = dto.EndTime.Value.AddDays(1).AddSeconds(-1);
            dto.PageIndex = 1;
            dto.PageSize = 10;
            var IsCenter = _sessionContext.OrgIsCenter;
            var query = _orderRepository.Queryable(false, false, false)
                .WhereIF(dto.StartTime.HasValue, x => x.CreationTime >= dto.StartTime)
                .WhereIF(dto.EndTime.HasValue, x => x.CreationTime <= dto.EndTime)
                .WhereIF(IsCenter == false, x => x.ActualHandleOrgCode.StartsWith(_sessionContext.RequiredOrgId))
                .Select(x => new
                {
                    AcceptType = x.AcceptType,
                    OneHotspot = SqlFunc.Substring(x.HotspotSpliceName, 0, SqlFunc.CharIndex("-", x.HotspotSpliceName + "-")),
                    Id = x.Id
                }).MergeTable()
                .GroupBy(x => new { x.OneHotspot })
                .Select(x => new AcceptTypeTop10Vo
                {
                    Name = x.OneHotspot,
                    ValidAccept = SqlFunc.AggregateSum(SqlFunc.IIF(true, 1, 0)),
                    Consult = SqlFunc.AggregateSum(SqlFunc.IIF("咨询".Equals(x.AcceptType), 1, 0)),
                    Report = SqlFunc.AggregateSum(SqlFunc.IIF("举报".Equals(x.AcceptType), 1, 0)),
                    Complaint = SqlFunc.AggregateSum(SqlFunc.IIF("投诉".Equals(x.AcceptType), 1, 0)),
                    SeekHelp = SqlFunc.AggregateSum(SqlFunc.IIF("求助".Equals(x.AcceptType), 1, 0)),
                    Suggest = SqlFunc.AggregateSum(SqlFunc.IIF("建议".Equals(x.AcceptType), 1, 0)),
                    Opinion = SqlFunc.AggregateSum(SqlFunc.IIF("意见".Equals(x.AcceptType), 1, 0)),
                    Rests = SqlFunc.AggregateSum(SqlFunc.IIF("其他".Equals(x.AcceptType), 1, 0)),
                    BenefitThePeople = SqlFunc.AggregateSum(SqlFunc.IIF("惠民帮助".Equals(x.AcceptType), 1, 0)),
                    Praise = SqlFunc.AggregateSum(SqlFunc.IIF("表扬".Equals(x.AcceptType), 1, 0)),
                }).MergeTable();
            switch (dto.SortField)
            {
                case "validAccept":
                    query = dto.SortRule is 0 ? query.OrderBy(x => x.ValidAccept) : query.OrderByDescending(x => x.ValidAccept);
                    break;
                case "consult":
                    query = dto.SortRule is 0 ? query.OrderBy(x => x.Consult) : query.OrderByDescending(x => x.Consult);
                    break;
                case "report":
                    query = dto.SortRule is 0 ? query.OrderBy(x => x.Report) : query.OrderByDescending(x => x.Report);
                    break;
                case "complaint":
                    query = dto.SortRule is 0 ? query.OrderBy(x => x.Complaint) : query.OrderByDescending(x => x.Complaint);
                    break;
                case "seekHelp":
                    query = dto.SortRule is 0 ? query.OrderBy(x => x.SeekHelp) : query.OrderByDescending(x => x.SeekHelp);
                    break;
                case "suggest":
                    query = dto.SortRule is 0 ? query.OrderBy(x => x.Suggest) : query.OrderByDescending(x => x.Suggest);
                    break;
                case "opinion":
                    query = dto.SortRule is 0 ? query.OrderBy(x => x.Opinion) : query.OrderByDescending(x => x.Opinion);
                    break;
                case "rests":
                    query = dto.SortRule is 0 ? query.OrderBy(x => x.Rests) : query.OrderByDescending(x => x.Rests);
                    break;
                case "benefitThePeople":
                    query = dto.SortRule is 0 ? query.OrderBy(x => x.BenefitThePeople) : query.OrderByDescending(x => x.BenefitThePeople);
                    break;
                case "praise":
                    query = dto.SortRule is 0 ? query.OrderBy(x => x.Praise) : query.OrderByDescending(x => x.Praise);
                    break;
                default:
                    query = query.OrderByDescending(x => x.ValidAccept);
                    break;
            }
            var (total, items) = await query.ToPagedListAsync(dto, HttpContext.RequestAborted);
            return new PagedDto<AcceptTypeTop10Vo>(total, items);
        }

        /// <summary>
        /// 热点类型部门统计
        /// </summary>
        /// <param name="dto"></param>
        /// <returns></returns>
        [HttpGet("hotport-org-statistics")]
        public async Task<object> HotPortJoinOrgStatistics([FromQuery] HotPortJoinOrgStatisticsRequest dto)
        {
            dto.EndTime = dto.EndTime.AddDays(1).AddSeconds(-1);
            var IsCenter = _sessionContext.OrgIsCenter;
            return await _orderRepository.HotPortJoinOrgStatistics(dto.StartTime, dto.EndTime, IsCenter, _sessionContext.OrgId);
        }

        /// <summary>
        /// 回访量统计
        /// </summary>
        /// <param name="StartTime"></param>
        /// <param name="EndTime"></param>
        /// <param name="VisitName"></param>
        /// <returns></returns>
        [HttpGet("visit-measure-statistics")]
        public async Task<VisitMeasureStatisticsDto> VisitMeasureStatistics(DateTime StartTime, DateTime EndTime, string? VisitName)
        {
            EndTime = EndTime.AddDays(1).AddSeconds(-1);

            var list = await _orderVisitRepository.Queryable()
                .Includes(x => x.Employee)
                 .Where(x => x.VisitTime >= StartTime && x.VisitTime <= EndTime && x.VisitState == EVisitState.Visited)
                 .WhereIF(!string.IsNullOrEmpty(VisitName), x => x.Employee.Name.Contains(VisitName))
                 .GroupBy(x => new { x.EmployeeId, x.Employee.Name })
                 .Select(x => new VisitMeasureStatisticsModelDto()
                 {
                     VisitName = x.Employee.Name,
                     CallVisitCount = SqlFunc.AggregateSum(SqlFunc.IIF(x.VisitType == EVisitType.CallVisit, 1, 0)),
                     ArtificialVisitCount = SqlFunc.AggregateSum(SqlFunc.IIF(x.VisitType != EVisitType.CallVisit, 1, 0)),
                     SumCount = SqlFunc.AggregateCount(x.EmployeeId)
                 })
                 .ToListAsync();
            var returnModel = new VisitMeasureStatisticsDto();
            returnModel.VisitMeasureStatisticsModelList = list;

            //查询AIVisit
            returnModel.AiVisitCount = await _aiOrderVisitDetailRepository.Queryable()
                .Where(x => x.AiVisitTime >= StartTime && x.AiVisitTime <= EndTime && x.IsSuccess != null).CountAsync();

            returnModel.AiVisitSatisfiedCount = await _aiOrderVisitDetailRepository.Queryable()
                .Includes(x => x.OrderVisit)
                .Where(x => x.AiVisitTime >= StartTime && x.AiVisitTime <= EndTime && x.IsSuccess == true && SqlFunc.JsonField(x.OrderVisit.NowEvaluate, "Key") != "1" && SqlFunc.JsonField(x.OrderVisit.NowEvaluate, "Key") != "2").CountAsync();

            returnModel.AiVisitNoSatisfiedCount = await _aiOrderVisitDetailRepository.Queryable()
                .Includes(x => x.OrderVisit)
                .Where(x => x.AiVisitTime >= StartTime && x.AiVisitTime <= EndTime && x.IsSuccess == true && SqlFunc.JsonField(x.OrderVisit.NowEvaluate, "Key") == "1" && SqlFunc.JsonField(x.OrderVisit.NowEvaluate, "Key") == "2").CountAsync();

            returnModel.AIVisitFailCount = await _aiOrderVisitDetailRepository.Queryable()
                .Where(x => x.AiVisitTime >= StartTime && x.AiVisitTime <= EndTime && x.IsSuccess == false).CountAsync();


            return returnModel;
        }

        /// <summary>
        /// 热点类型小类统计
        /// </summary>
        /// <param name="StartTime"></param>
        /// <param name="EndTime"></param>
        /// <param name="TypeId">0：全部 ，1：市民，2：企业</param>
        /// <returns></returns>
        [HttpGet("hotspot-statistics")]
        public async Task<object> HotspotStatistics(DateTime StartTime, DateTime EndTime, int TypeId, string? HotspotCode)
        {
            EndTime = EndTime.AddDays(1).AddSeconds(-1);

            var IsCenter = _sessionContext.OrgIsCenter;

            if (string.IsNullOrEmpty(HotspotCode))
            {
                var list = await _hotspotTypeRepository.Queryable()
                .LeftJoin<Order>((it, o) => it.Id == o.HotspotId)
                .Where((it, o) => o.CreationTime >= StartTime && o.CreationTime <= EndTime && o.Id != null)
                .WhereIF(TypeId == 1, (it, o) => o.IdentityType == EIdentityType.Citizen)
                .WhereIF(TypeId == 2, (it, o) => o.IdentityType == EIdentityType.Enterprise)
                .WhereIF(IsCenter == false, (it, o) => o.ActualHandleOrgCode.StartsWith(_sessionContext.RequiredOrgId))
                .GroupBy((it, o) => new { Id = it.Id.Substring(SqlFunc.MappingColumn<int>("0"), SqlFunc.MappingColumn<int>("2")) })
                .Select((it, o) => new
                {
                    HotspotCode = it.Id.Substring(SqlFunc.MappingColumn<int>("0"), SqlFunc.MappingColumn<int>("2")),
                    SumCount = SqlFunc.AggregateCount(it.HotSpotName)
                })
                .MergeTable()
                .LeftJoin<Hotspot>((x, q) => x.HotspotCode == q.Id)
                .Select((x, q) => new
                {
                    HotspotCode = x.HotspotCode,
                    SumCount = x.SumCount,
                    HotspotName = q.HotSpotName,
                    HasChild = SqlFunc.Subqueryable<Hotspot>().Where(d => d.ParentId == x.HotspotCode).Any()
                })
                .ToListAsync();

                return list;
            }
            else
            {
                string count = (HotspotCode.Length + 2).ToString();
                string countx = HotspotCode.Length.ToString();
                var list = await _hotspotTypeRepository.Queryable()
                .LeftJoin<Order>((it, o) => it.Id == o.HotspotId)
                .Where((it, o) => o.CreationTime >= StartTime && o.CreationTime <= EndTime && it.ParentId.Substring(SqlFunc.MappingColumn<int>("0"), SqlFunc.MappingColumn<int>(countx)) == HotspotCode)
                .WhereIF(TypeId == 1, (it, o) => o.IdentityType == EIdentityType.Citizen)
                .WhereIF(TypeId == 2, (it, o) => o.IdentityType == EIdentityType.Enterprise)
                .WhereIF(IsCenter == false, (it, o) => o.ActualHandleOrgCode.StartsWith(_sessionContext.RequiredOrgId))
                .GroupBy((it, o) => new { Id = it.Id.Substring(SqlFunc.MappingColumn<int>("0"), SqlFunc.MappingColumn<int>(count)) })
                .Select((it, o) => new
                {
                    HotspotCode = it.Id.Substring(SqlFunc.MappingColumn<int>("0"), SqlFunc.MappingColumn<int>(count)),
                    SumCount = SqlFunc.AggregateCount(it.HotSpotName)
                })
                .MergeTable()
                .LeftJoin<Hotspot>((x, q) => x.HotspotCode == q.Id)
                .Select((x, q) => new
                {
                    HotspotCode = x.HotspotCode,
                    SumCount = x.SumCount,
                    HotspotName = q.HotSpotName,
                    HasChild = SqlFunc.Subqueryable<Hotspot>().Where(d => d.ParentId == x.HotspotCode).Any()
                })
                .ToListAsync();
                return list;

            }
        }

        /// <summary>
        /// 部门满意度统计
        /// </summary>
        /// <param name="dto"></param>
        /// <returns></returns>
        [HttpGet("visit-org-satisfaction-statistics")]
        public async Task<VisitAndOrgSatisfactionStatisticsResultDto> VisitAndOrgSatisfactionStatistics([FromQuery] PagedKeywordSonRequest dto)
        {
            var data = await _orderApplication.VisitAndOrgSatisfactionStatistics(dto);

            var countySumModel = new VisitAndOrgSatisfactionStatisticsDto()
            {
                OrgName = "区县合计",
                TotalSumCount = data.Where(x => x.OrgType == EOrgType.County).Sum(x => x.TotalSumCount),
                VerySatisfiedCount = data.Where(x => x.OrgType == EOrgType.County).Sum(x => x.VerySatisfiedCount),
                SatisfiedCount = data.Where(x => x.OrgType == EOrgType.County).Sum(x => x.SatisfiedCount),
                RegardedAsSatisfiedCount = data.Where(x => x.OrgType == EOrgType.County).Sum(x => x.RegardedAsSatisfiedCount),
                DefaultSatisfiedCount = data.Where(x => x.OrgType == EOrgType.County).Sum(x => x.DefaultSatisfiedCount),
                NoSatisfiedCount = data.Where(x => x.OrgType == EOrgType.County).Sum(x => x.NoSatisfiedCount),
                NoEvaluateCount = data.Where(x => x.OrgType == EOrgType.County).Sum(x => x.NoEvaluateCount),
                NoPutThroughCount = data.Where(x => x.OrgType == EOrgType.County).Sum(x => x.NoPutThroughCount),
            };

            var citySumModel = new VisitAndOrgSatisfactionStatisticsDto()
            {
                OrgName = "市直合计",
                TotalSumCount = data.Where(x => x.OrgType == EOrgType.City).Sum(x => x.TotalSumCount),
                VerySatisfiedCount = data.Where(x => x.OrgType == EOrgType.City).Sum(x => x.VerySatisfiedCount),
                SatisfiedCount = data.Where(x => x.OrgType == EOrgType.City).Sum(x => x.SatisfiedCount),
                RegardedAsSatisfiedCount = data.Where(x => x.OrgType == EOrgType.City).Sum(x => x.RegardedAsSatisfiedCount),
                DefaultSatisfiedCount = data.Where(x => x.OrgType == EOrgType.City).Sum(x => x.DefaultSatisfiedCount),
                NoSatisfiedCount = data.Where(x => x.OrgType == EOrgType.City).Sum(x => x.NoSatisfiedCount),
                NoEvaluateCount = data.Where(x => x.OrgType == EOrgType.City).Sum(x => x.NoEvaluateCount),
                NoPutThroughCount = data.Where(x => x.OrgType == EOrgType.City).Sum(x => x.NoPutThroughCount),
            };

            var sumModel = new VisitAndOrgSatisfactionStatisticsDto()
            {
                OrgName = "总计",
                TotalSumCount = data.Sum(x => x.TotalSumCount),
                VerySatisfiedCount = data.Sum(x => x.VerySatisfiedCount),
                SatisfiedCount = data.Sum(x => x.SatisfiedCount),
                RegardedAsSatisfiedCount = data.Sum(x => x.RegardedAsSatisfiedCount),
                DefaultSatisfiedCount = data.Sum(x => x.DefaultSatisfiedCount),
                NoSatisfiedCount = data.Sum(x => x.NoSatisfiedCount),
                NoEvaluateCount = data.Sum(x => x.NoEvaluateCount),
                NoPutThroughCount = data.Sum(x => x.NoPutThroughCount),
            };


            return new VisitAndOrgSatisfactionStatisticsResultDto { DataList = data, CountySumModel = countySumModel, CitySumModel = citySumModel, SumModel = sumModel };
        }

        /// <summary>
        /// 部门满意度统计导出
        /// </summary>
        /// <param name="dto"></param>
        /// <returns></returns>
        [HttpPost("visit-org-satisfaction-statistics/_export")]
        public async Task<FileStreamResult> VisitAndOrgSatisfactionStatisticsExport([FromBody] ExportExcelDto<PagedKeywordSonRequest> dto)
        {
            List<VisitAndOrgSatisfactionStatisticsDto> data = await _orderApplication.VisitAndOrgSatisfactionStatistics(dto.QueryDto);
            var countySumModel = new VisitAndOrgSatisfactionStatisticsDto()
            {
                OrgName = "区县合计",
                TotalSumCount = data.Where(x => x.OrgType == EOrgType.County).Sum(x => x.TotalSumCount),
                VerySatisfiedCount = data.Where(x => x.OrgType == EOrgType.County).Sum(x => x.VerySatisfiedCount),
                SatisfiedCount = data.Where(x => x.OrgType == EOrgType.County).Sum(x => x.SatisfiedCount),
                RegardedAsSatisfiedCount = data.Where(x => x.OrgType == EOrgType.County).Sum(x => x.RegardedAsSatisfiedCount),
                DefaultSatisfiedCount = data.Where(x => x.OrgType == EOrgType.County).Sum(x => x.DefaultSatisfiedCount),
                NoSatisfiedCount = data.Where(x => x.OrgType == EOrgType.County).Sum(x => x.NoSatisfiedCount),
                NoEvaluateCount = data.Where(x => x.OrgType == EOrgType.County).Sum(x => x.NoEvaluateCount),
                NoPutThroughCount = data.Where(x => x.OrgType == EOrgType.County).Sum(x => x.NoPutThroughCount),
            };
            var citySumModel = new VisitAndOrgSatisfactionStatisticsDto()
            {
                OrgName = "市直合计",
                TotalSumCount = data.Where(x => x.OrgType == EOrgType.City).Sum(x => x.TotalSumCount),
                VerySatisfiedCount = data.Where(x => x.OrgType == EOrgType.City).Sum(x => x.VerySatisfiedCount),
                SatisfiedCount = data.Where(x => x.OrgType == EOrgType.City).Sum(x => x.SatisfiedCount),
                RegardedAsSatisfiedCount = data.Where(x => x.OrgType == EOrgType.City).Sum(x => x.RegardedAsSatisfiedCount),
                DefaultSatisfiedCount = data.Where(x => x.OrgType == EOrgType.City).Sum(x => x.DefaultSatisfiedCount),
                NoSatisfiedCount = data.Where(x => x.OrgType == EOrgType.City).Sum(x => x.NoSatisfiedCount),
                NoEvaluateCount = data.Where(x => x.OrgType == EOrgType.City).Sum(x => x.NoEvaluateCount),
                NoPutThroughCount = data.Where(x => x.OrgType == EOrgType.City).Sum(x => x.NoPutThroughCount),
            };
            var sumModel = new VisitAndOrgSatisfactionStatisticsDto()
            {
                OrgName = "总计",
                TotalSumCount = data.Sum(x => x.TotalSumCount),
                VerySatisfiedCount = data.Sum(x => x.VerySatisfiedCount),
                SatisfiedCount = data.Sum(x => x.SatisfiedCount),
                RegardedAsSatisfiedCount = data.Sum(x => x.RegardedAsSatisfiedCount),
                DefaultSatisfiedCount = data.Sum(x => x.DefaultSatisfiedCount),
                NoSatisfiedCount = data.Sum(x => x.NoSatisfiedCount),
                NoEvaluateCount = data.Sum(x => x.NoEvaluateCount),
                NoPutThroughCount = data.Sum(x => x.NoPutThroughCount),
            };
            data.Add(countySumModel);
            data.Add(citySumModel);
            data.Add(sumModel);
            dynamic? dynamicClass = DynamicClassHelper.CreateDynamicClass(dto.ColumnInfos);
            var dtos = data
             .Select(stu => _mapper.Map(stu, typeof(VisitAndOrgSatisfactionStatisticsDto), dynamicClass))
             .Cast<object>()
             .ToList();

            var stream = ExcelHelper.CreateStream(dtos);

            return ExcelStreamResult(stream, "部门满意度统计数据");
        }

        /// <summary>
        /// 子部门满意度明细
        /// </summary>
        /// <param name="dto"></param>
        /// <returns></returns>
        [HttpGet("visit-org-statisfaction-org-detail")]
        public async Task<VisitAndOrgSatisfactionStatisticsResultDto> VisitAndOrgStatisfactionOrgDetail([FromQuery] PagedKeywordSonRequest dto)
        {
            var list = await _orderApplication.VisitAndOrgStatisfactionOrgDetail(dto);
            var countySumModel = new VisitAndOrgSatisfactionStatisticsDto()
            {
                OrgName = "区县合计",
                TotalSumCount = list.Where(x => x.OrgType == EOrgType.County).Sum(x => x.TotalSumCount),
                VerySatisfiedCount = list.Where(x => x.OrgType == EOrgType.County).Sum(x => x.VerySatisfiedCount),
                SatisfiedCount = list.Where(x => x.OrgType == EOrgType.County).Sum(x => x.SatisfiedCount),
                RegardedAsSatisfiedCount = list.Where(x => x.OrgType == EOrgType.County).Sum(x => x.RegardedAsSatisfiedCount),
                DefaultSatisfiedCount = list.Where(x => x.OrgType == EOrgType.County).Sum(x => x.DefaultSatisfiedCount),
                NoSatisfiedCount = list.Where(x => x.OrgType == EOrgType.County).Sum(x => x.NoSatisfiedCount),
                NoEvaluateCount = list.Where(x => x.OrgType == EOrgType.County).Sum(x => x.NoEvaluateCount),
                NoPutThroughCount = list.Where(x => x.OrgType == EOrgType.County).Sum(x => x.NoPutThroughCount),
            };

            var citySumModel = new VisitAndOrgSatisfactionStatisticsDto()
            {
                OrgName = "市直合计",
                TotalSumCount = list.Where(x => x.OrgType == EOrgType.City).Sum(x => x.TotalSumCount),
                VerySatisfiedCount = list.Where(x => x.OrgType == EOrgType.City).Sum(x => x.VerySatisfiedCount),
                SatisfiedCount = list.Where(x => x.OrgType == EOrgType.City).Sum(x => x.SatisfiedCount),
                RegardedAsSatisfiedCount = list.Where(x => x.OrgType == EOrgType.City).Sum(x => x.RegardedAsSatisfiedCount),
                DefaultSatisfiedCount = list.Where(x => x.OrgType == EOrgType.City).Sum(x => x.DefaultSatisfiedCount),
                NoSatisfiedCount = list.Where(x => x.OrgType == EOrgType.City).Sum(x => x.NoSatisfiedCount),
                NoEvaluateCount = list.Where(x => x.OrgType == EOrgType.City).Sum(x => x.NoEvaluateCount),
                NoPutThroughCount = list.Where(x => x.OrgType == EOrgType.City).Sum(x => x.NoPutThroughCount),
            };

            var sumModel = new VisitAndOrgSatisfactionStatisticsDto()
            {
                OrgName = "总计",
                TotalSumCount = list.Sum(x => x.TotalSumCount),
                VerySatisfiedCount = list.Sum(x => x.VerySatisfiedCount),
                SatisfiedCount = list.Sum(x => x.SatisfiedCount),
                RegardedAsSatisfiedCount = list.Sum(x => x.RegardedAsSatisfiedCount),
                DefaultSatisfiedCount = list.Sum(x => x.DefaultSatisfiedCount),
                NoSatisfiedCount = list.Sum(x => x.NoSatisfiedCount),
                NoEvaluateCount = list.Sum(x => x.NoEvaluateCount),
                NoPutThroughCount = list.Sum(x => x.NoPutThroughCount),
            };

            return new VisitAndOrgSatisfactionStatisticsResultDto { DataList = list, CountySumModel = countySumModel, CitySumModel = citySumModel, SumModel = sumModel };
        }

        /// <summary>
        /// 部门满意度统计导出
        /// </summary>
        /// <param name="dto"></param>
        /// <returns></returns>
        [HttpPost("visit-org-statisfaction-org-detail/_export")]
        public async Task<FileStreamResult> VisitAndOrgStatisfactionOrgDetailExport([FromBody] ExportExcelDto<PagedKeywordSonRequest> dto)
        {
            var list = await _orderApplication.VisitAndOrgStatisfactionOrgDetail(dto.QueryDto);
            var countySumModel = new VisitAndOrgSatisfactionStatisticsDto()
            {
                OrgName = "区县合计",
                TotalSumCount = list.Where(x => x.OrgType == EOrgType.County).Sum(x => x.TotalSumCount),
                VerySatisfiedCount = list.Where(x => x.OrgType == EOrgType.County).Sum(x => x.VerySatisfiedCount),
                SatisfiedCount = list.Where(x => x.OrgType == EOrgType.County).Sum(x => x.SatisfiedCount),
                RegardedAsSatisfiedCount = list.Where(x => x.OrgType == EOrgType.County).Sum(x => x.RegardedAsSatisfiedCount),
                DefaultSatisfiedCount = list.Where(x => x.OrgType == EOrgType.County).Sum(x => x.DefaultSatisfiedCount),
                NoSatisfiedCount = list.Where(x => x.OrgType == EOrgType.County).Sum(x => x.NoSatisfiedCount),
                NoEvaluateCount = list.Where(x => x.OrgType == EOrgType.County).Sum(x => x.NoEvaluateCount),
                NoPutThroughCount = list.Where(x => x.OrgType == EOrgType.County).Sum(x => x.NoPutThroughCount),
            };
            var citySumModel = new VisitAndOrgSatisfactionStatisticsDto()
            {
                OrgName = "市直合计",
                TotalSumCount = list.Where(x => x.OrgType == EOrgType.City).Sum(x => x.TotalSumCount),
                VerySatisfiedCount = list.Where(x => x.OrgType == EOrgType.City).Sum(x => x.VerySatisfiedCount),
                SatisfiedCount = list.Where(x => x.OrgType == EOrgType.City).Sum(x => x.SatisfiedCount),
                RegardedAsSatisfiedCount = list.Where(x => x.OrgType == EOrgType.City).Sum(x => x.RegardedAsSatisfiedCount),
                DefaultSatisfiedCount = list.Where(x => x.OrgType == EOrgType.City).Sum(x => x.DefaultSatisfiedCount),
                NoSatisfiedCount = list.Where(x => x.OrgType == EOrgType.City).Sum(x => x.NoSatisfiedCount),
                NoEvaluateCount = list.Where(x => x.OrgType == EOrgType.City).Sum(x => x.NoEvaluateCount),
                NoPutThroughCount = list.Where(x => x.OrgType == EOrgType.City).Sum(x => x.NoPutThroughCount),
            };
            var sumModel = new VisitAndOrgSatisfactionStatisticsDto()
            {
                OrgName = "总计",
                TotalSumCount = list.Sum(x => x.TotalSumCount),
                VerySatisfiedCount = list.Sum(x => x.VerySatisfiedCount),
                SatisfiedCount = list.Sum(x => x.SatisfiedCount),
                RegardedAsSatisfiedCount = list.Sum(x => x.RegardedAsSatisfiedCount),
                DefaultSatisfiedCount = list.Sum(x => x.DefaultSatisfiedCount),
                NoSatisfiedCount = list.Sum(x => x.NoSatisfiedCount),
                NoEvaluateCount = list.Sum(x => x.NoEvaluateCount),
                NoPutThroughCount = list.Sum(x => x.NoPutThroughCount),
            };
            list.Add(countySumModel);
            list.Add(citySumModel);
            list.Add(sumModel);
            dynamic? dynamicClass = DynamicClassHelper.CreateDynamicClass(dto.ColumnInfos);
            var dtos = list
             .Select(stu => _mapper.Map(stu, typeof(VisitAndOrgSatisfactionStatisticsDto), dynamicClass))
             .Cast<object>()
             .ToList();

            var stream = ExcelHelper.CreateStream(dtos);

            return ExcelStreamResult(stream, "部门满意度统计数据");
        }

        /// <summary>
        /// 部门满意度明细
        /// </summary>
        /// <param name="dto"></param>
        /// <returns></returns>
        [HttpGet("visit-org-satisfaction-detail")]
        public async Task<PagedDto<OrderVisitDetailDto>> VisitAndOrgSatisfactionDetail([FromQuery] VisitAndOrgSatisfactionDetailDto dto)
        {
            var (total, items) = await _orderApplication.VisitAndOrgSatisfactionDetail(dto)
                .ToPagedListAsync(dto.PageIndex, dto.PageSize, HttpContext.RequestAborted);
            return new PagedDto<OrderVisitDetailDto>(total, _mapper.Map<IReadOnlyList<OrderVisitDetailDto>>(items));
        }

        /// <summary>
        /// 部门满意度明细导出
        /// </summary>
        /// <param name="dto"></param>
        /// <returns></returns>
        [HttpPost("visit-org-satisfaction-detail/_export")]
        public async Task<FileStreamResult> VisitAndOrgSatisfactionDetailExport([FromBody] ExportExcelDto<VisitAndOrgSatisfactionDetailDto> dto)
        {
            var query = _orderApplication.VisitAndOrgSatisfactionDetail(dto.QueryDto);
            List<OrderVisitDetail> data;
            if (dto.IsExportAll)
            {
                data = await query.ToListAsync(HttpContext.RequestAborted);
            }
            else
            {
                var (_, items) = await query.ToPagedListAsync(dto.QueryDto, HttpContext.RequestAborted);
                data = items;
            }

            var dataDtos = _mapper.Map<ICollection<OrderVisitDetailDto>>(data);

            dynamic? dynamicClass = DynamicClassHelper.CreateDynamicClass(dto.ColumnInfos);

            var dtos = dataDtos
                .Select(stu => _mapper.Map(stu, typeof(OrderVisitDetailDto), dynamicClass))
                .Cast<object>()
                .ToList();

            var stream = ExcelHelper.CreateStream(dtos);

            return ExcelStreamResult(stream, "部门满意度明细数据");
        }

        /// <summary>
        /// 中心报表统计
        /// </summary>
        /// <param name="StartTime"></param>
        /// <param name="EndTime"></param>
        /// <returns></returns>
        [HttpGet("center_report_forms_statistics")]
        public async Task<CenterReportStatisticsDto> CenterReportFormsStatistics(DateTime StartTime, DateTime EndTime)
        {
            EndTime = EndTime.AddDays(1).AddSeconds(-1);

            CenterReportStatisticsDto centerReportStatisticsDto = new();

            //信件总量
            int sourceChannelCount = await _orderRepository.Queryable().Where(p => p.CreationTime >= StartTime && p.CreationTime <= EndTime).CountAsync();

            #region 通话记录
            //通话记录
            var callData = await _trCallRecordRepository.Queryable()
                .Where(p => p.CreatedTime >= StartTime && p.CreatedTime <= EndTime && p.Gateway != "82826886" && SqlFunc.Length(p.Gateway) != 4)
                .Select(o => new CenterReportCallDto
                {
                    EffectiveCount = SqlFunc.AggregateSum(SqlFunc.IIF(o.OnState == EOnState.On, 1, 0)),//有效
                    InvalidCount = SqlFunc.AggregateSum(SqlFunc.IIF(o.OnState == EOnState.NoOn /*&& o.BeginIvrTime.HasValue && o.BeginQueueTime.HasValue && o.BeginRingTime.HasValue*/, 1, 0)), //无效(排除队列挂断和IVR挂断)
                    QueueByeCount = SqlFunc.AggregateSum(SqlFunc.IIF(o.CallDirection == ECallDirection.In && o.QueueTims > 0 && o.RingTimes == 0 && o.OnState == EOnState.NoOn, 1, 0)), //队列挂断
                    IvrByeCount = SqlFunc.AggregateSum(SqlFunc.IIF(o.CallDirection == ECallDirection.In && o.BeginIvrTime.HasValue && !o.BeginQueueTime.HasValue && !o.BeginRingTime.HasValue && o.OnState == EOnState.NoOn, 1, 0)), //IVR挂断
                })
                .FirstAsync();
            if (callData != null)
                callData.InvalidCount = callData.InvalidCount - callData.QueueByeCount - callData.IvrByeCount;
            centerReportStatisticsDto.CenterReportCall = callData;
            #endregion

            #region 工单
            //工单
            var orderData = await _orderRepository.Queryable()
                .Where(p => p.CreationTime >= StartTime && p.CreationTime <= EndTime)
                .Select(x => new CenterReportOrderDto
                {
                    EffectiveCount = SqlFunc.AggregateSum(SqlFunc.IIF(true, 1, 0)),
                    InvalidCount = 0,
                    CompletedCount = SqlFunc.AggregateSum(SqlFunc.IIF((int)x.Status >= 300, 1, 0)),
                    InProgressCount = SqlFunc.AggregateSum(SqlFunc.IIF((int)x.Status < 300, 1, 0))
                })
                .ToListAsync();
            if (orderData != null && orderData.Count > 0)
                centerReportStatisticsDto.CenterReportOrder = orderData[0];
            #endregion

            #region 信件来源
            //信件来源
            var sourceChannelData = await _orderRepository.Queryable()
                .Where(p => p.CreationTime >= StartTime && p.CreationTime <= EndTime)
                .Select(it => new
                {
                    SourceChannelCode = SqlFunc.IIF(SqlFunc.IsNullOrEmpty(it.SourceChannelCode), "QT", it.SourceChannelCode)
                })
                .MergeTable()//将查询出来的结果合并成一个新表
                 .GroupBy(it => new { it.SourceChannelCode })//对新表进行分组
                 .Select(it => new CenterReportOrderSourceChannelDto
                 {
                     Code = it.SourceChannelCode,
                     CountNum = SqlFunc.AggregateCount(it.SourceChannelCode)
                 })
                 .ToListAsync();
            List<CenterReportOrderSourceChannelDto> sourceChannel = new()
            {
                new CenterReportOrderSourceChannelDto
                {
                    Name = "来源总量",
                    Code = "All",
                    CountNum = sourceChannelCount
                }
            };
            var sourceChannelDic = _sysDicDataCacheManager.GetSysDicDataCache(SysDicTypeConsts.SourceChannel);
            foreach (var item in sourceChannelDic)
            {
                sourceChannel.Add(new CenterReportOrderSourceChannelDto
                {
                    Name = item.DicDataName,
                    Code = item.DicTypeCode,
                    CountNum = sourceChannelData.Find(p => p.Code == item.DicDataValue)?.CountNum ?? 0
                });
            }

            centerReportStatisticsDto.CenterReportOrderSourceChannels = sourceChannel;
            #endregion

            #region 信件分类
            //信件来源
            var acceptTypeData = await _orderRepository.Queryable(false, false, false)
                .Where(p => p.CreationTime >= StartTime && p.CreationTime <= EndTime)
                .Select(it => new
                {
                    AcceptTypeCode = SqlFunc.IIF(SqlFunc.IsNullOrEmpty(it.AcceptTypeCode), "40", it.AcceptTypeCode)
                })
                .MergeTable()//将查询出来的结果合并成一个新表
                 .GroupBy(it => new { it.AcceptTypeCode })//对新表进行分组
                 .Select(it => new CenterReportOrderSourceChannelDto
                 {
                     Code = it.AcceptTypeCode,
                     CountNum = SqlFunc.AggregateCount(it.AcceptTypeCode)
                 })
                 .ToListAsync();
            List<CenterReportOrderSourceChannelDto> acceptType = new();
            var acceptTypeDic = _sysDicDataCacheManager.GetSysDicDataCache(SysDicTypeConsts.AcceptType);
            foreach (var item in acceptTypeDic)
            {
                acceptType.Add(new CenterReportOrderSourceChannelDto
                {
                    AllCountNum = sourceChannelCount,
                    Name = item.DicDataName,
                    Code = item.DicTypeCode,
                    CountNum = acceptTypeData.Find(p => p.Code == item.DicDataValue)?.CountNum ?? 0
                });
            }

            centerReportStatisticsDto.CenterReportOrderAcceptTypes = acceptType;
            #endregion

            #region 信件回访量
            //信件回访量
            CenterReportVisitdDto centerReportVisitd = new()
            {
                Visitd = await _orderVisitRepository.Queryable()
              .Where(x => x.VisitTime >= StartTime && x.VisitTime <= EndTime && x.VisitState == EVisitState.Visited).CountAsync(),
                WaitVisitd = await _orderVisitRepository.Queryable()
             .Where(x => x.VisitTime >= StartTime && x.VisitTime <= EndTime && x.VisitState != EVisitState.None && x.VisitState != EVisitState.Visited).CountAsync()
            };

            //部门
            var listOrg = await _orderVisitDetailRepository.Queryable()
                .LeftJoin<OrderVisit>((it, o) => it.VisitId == o.Id)
                .Where((it, o) => it.VisitTarget == EVisitTarget.Org && o.VisitTime >= StartTime && o.VisitTime <= EndTime && o.VisitState == EVisitState.Visited)
                 .Select((it, o) => new Satisfaction
                 {
                     Dissatisfied = SqlFunc.AggregateSum(SqlFunc.IIF(SqlFunc.JsonListObjectAny(it.OrgProcessingResults, "key", "1") || SqlFunc.JsonListObjectAny(it.OrgProcessingResults, "key", "2"), 1, 0)),
                     Satisfied = SqlFunc.AggregateSum(SqlFunc.IIF(SqlFunc.JsonListObjectAny(it.OrgProcessingResults, "key", "1") || SqlFunc.JsonListObjectAny(it.OrgProcessingResults, "key", "2"), 0, 1)),
                 })
                .ToListAsync();

            if (listOrg != null && listOrg.Count > 0)
            {
                var SatisfiedCount = listOrg[0].Satisfied + listOrg[0].Satisfied;
                if (SatisfiedCount > 0 && listOrg[0].Satisfied > 0)
                    centerReportVisitd.OrgRate = Math.Round((listOrg[0].Satisfied / (double)SatisfiedCount) * 100, 2);
            }

            //if (centerReportVisitd.Visitd > 0 && listOrg != null && listOrg.Count > 0 && listOrg[0].Satisfied > 0)
            //centerReportVisitd.OrgRate = Math.Round((listOrg[0].Satisfied / (double)centerReportVisitd.Visitd) * 100, 2);

            //坐席
            var listSet = await _orderVisitDetailRepository.Queryable()
                .LeftJoin<OrderVisit>((it, o) => it.VisitId == o.Id)
                .Where((it, o) => it.VisitTarget == EVisitTarget.Seat && o.VisitTime >= StartTime && o.VisitTime <= EndTime && o.VisitState == EVisitState.Visited)
                .Select((it, o) => new Satisfaction
                {
                    Dissatisfied = SqlFunc.AggregateSum(SqlFunc.IIF(it.SeatEvaluate == ESeatEvaluate.NoSatisfied, 1, 0)),
                    Satisfied = SqlFunc.AggregateSum(SqlFunc.IIF(it.SeatEvaluate != ESeatEvaluate.NoSatisfied, 1, 0)),
                }).ToListAsync();

            if (listSet != null && listSet.Count > 0)
            {
                var SatisfiedCount = listSet[0].Satisfied + listSet[0].Satisfied;
                if (SatisfiedCount > 0 && listSet[0].Satisfied > 0)
                    centerReportVisitd.OrgRate = Math.Round((listSet[0].Satisfied / (double)SatisfiedCount) * 100, 2);
            }

            //if (centerReportVisitd.Visitd > 0 && listSet != null && listSet.Count > 0 && listSet[0].Satisfied > 0)
            //    centerReportVisitd.SeatsRate = Math.Round((listSet[0].Satisfied / (double)centerReportVisitd.Visitd) * 100, 2);

            centerReportStatisticsDto.CenterReportVisitd = centerReportVisitd;
            #endregion

            #region 信件分布情况
            //市直部门
            var listOrgStatisticsCityAll = await _orderRepository.Queryable()
              .LeftJoin<SystemOrganize>((it, o) => it.OrgLevelOneCode == o.Id)
              .Where((it, o) => (o.OrgType == EOrgType.City || o.OrgType == EOrgType.Province) && it.CreationTime >= StartTime && it.CreationTime <= EndTime)
             .GroupBy((it, o) => new
             {
                 it.OrgLevelOneCode,
                 o.Name
             })
              .Select((it, o) => new OrgStatistics
              {
                  CountNum = SqlFunc.AggregateCount(it.OrgLevelOneCode),

                  OrgName = it.OrgLevelOneCode == "001" ? "市民热线服务中心" : o.Name
              }).ToListAsync();

            centerReportStatisticsDto.OrgStatisticsCityAll = new OrgStatisticsAll
            {
                OrgStatistics = listOrgStatisticsCityAll
            };

            //区县部门
            var listOrgStatisticsAreaAll = await _orderRepository.Queryable()
             .LeftJoin<SystemOrganize>((it, o) => it.OrgLevelOneCode == o.Id)
             .Where((it, o) => o.OrgType == EOrgType.County && it.CreationTime >= StartTime && it.CreationTime <= EndTime)
            .GroupBy((it, o) => new
            {
                it.OrgLevelOneCode,
                o.Name
            })
             .Select((it, o) => new OrgStatistics
             {
                 CountNum = SqlFunc.AggregateCount(it.OrgLevelOneCode),
                 OrgName = it.OrgLevelOneCode == "001" ? "市民热线服务中心" : o.Name
             }).ToListAsync();

            centerReportStatisticsDto.OrgStatisticsAreaAll = new OrgStatisticsAll
            {
                OrgStatistics = listOrgStatisticsAreaAll
            };
            #endregion

            return centerReportStatisticsDto;
        }

        /// <summary>
        /// 部门受理类型统计周期
        /// </summary>
        /// <param name="dto"></param>
        /// <returns></returns>
        [HttpGet("department_acceptance_type_statistics")]
        public async Task<object> DepartmentAcceptanceTypeStatistics([FromQuery] DepartmentKeyWordRequest dto)
        {
            var items = await _orderReportApplication.DepartmentAcceptanceTypeStatistics(dto).ToListAsync();

            foreach (var item in items)
            {
                item.ZxAllTimes = Math.Round(((double)item.ZxAllTimes / 3600), 2);
                item.JyAllTimes = Math.Round(((double)item.JyAllTimes / 3600), 2);
                item.QzAllTimes = Math.Round(((double)item.QzAllTimes / 3600), 2);
                item.ByAllTimes = Math.Round(((double)item.ByAllTimes / 3600), 2);
                item.JbAllTimes = Math.Round(((double)item.JbAllTimes / 3600), 2);
                item.TsAllTimes = Math.Round(((double)item.TsAllTimes / 3600), 2);
                item.QtAllTimes = Math.Round(((double)item.QtAllTimes / 3600), 2);
                item.YjAllTimes = Math.Round(((double)item.YjAllTimes / 3600), 2);
            }


            var total = new DepartmentAcceptanceTypeStatisticsDto
            {
                OrgName = "合计",
                OrgCode = "",
                OrgType = "",
                ZxAllCount = items.Sum(p => p.ZxAllCount),
                ZxAllTimes = items.Sum(p => p.ZxAllTimes),
                ZxAcceptanceTypeCode = "10",

                JyAllCount = items.Sum(p => p.JyAllCount),
                JyAllTimes = items.Sum(p => p.JyAllTimes),
                JyAcceptanceTypeCode = "15",

                QzAllCount = items.Sum(p => p.QzAllCount),
                QzAllTimes = items.Sum(p => p.QzAllTimes),
                QzAcceptanceTypeCode = "20",

                ByAllCount = items.Sum(p => p.ByAllCount),
                ByAllTimes = items.Sum(p => p.ByAllTimes),
                ByAcceptanceTypeCode = "25",

                JbAllCount = items.Sum(p => p.JbAllCount),
                JbAllTimes = items.Sum(p => p.JbAllTimes),
                JbAcceptanceTypeCode = "30",

                TsAllCount = items.Sum(p => p.TsAllCount),
                TsAllTimes = items.Sum(p => p.TsAllTimes),
                TsAcceptanceTypeCode = "35",

                QtAllCount = items.Sum(p => p.QtAllCount),
                QtAllTimes = items.Sum(p => p.QtAllTimes),
                QtAcceptanceTypeCode = "40",

                YjAllCount = items.Sum(p => p.YjAllCount),
                YjAllTimes = items.Sum(p => p.YjAllTimes),
                YjAcceptanceTypeCode = "1"
            };

            return new { List = items, Total = total };
        }

        /// <summary>
        /// 部门受理类型统计周期---导出
        /// </summary>
        /// <param name="dto"></param>
        /// <returns></returns>
        [HttpPost("department_acceptance_type_statistics_export")]
        public async Task<FileStreamResult> ExportDepartmentAcceptanceTypeStatistics([FromBody] ExportExcelDto<DepartmentKeyWordRequest> dto)
        {
            var query = _orderReportApplication.DepartmentAcceptanceTypeStatistics(dto.QueryDto);
            List<DepartmentAcceptanceTypeStatisticsDto> list;
            if (dto.IsExportAll)
            {
                list = await query.ToListAsync(HttpContext.RequestAborted);
            }
            else
            {
                var (_, items) = await query.ToPagedListAsync(dto.QueryDto, HttpContext.RequestAborted);
                list = items;
            }
            foreach (var item in list)
            {
                item.ZxAllTimes = Math.Round(((double)item.ZxAllTimes / 3600), 2);
                item.JyAllTimes = Math.Round(((double)item.JyAllTimes / 3600), 2);
                item.QzAllTimes = Math.Round(((double)item.QzAllTimes / 3600), 2);
                item.ByAllTimes = Math.Round(((double)item.ByAllTimes / 3600), 2);
                item.JbAllTimes = Math.Round(((double)item.JbAllTimes / 3600), 2);
                item.TsAllTimes = Math.Round(((double)item.TsAllTimes / 3600), 2);
                item.QtAllTimes = Math.Round(((double)item.QtAllTimes / 3600), 2);
                item.YjAllTimes = Math.Round(((double)item.YjAllTimes / 3600), 2);
            }
            //增加合计
            list.Add(new DepartmentAcceptanceTypeStatisticsDto
            {
                OrgName = "合计",
                OrgCode = "",
                OrgType = "",
                ZxAllCount = list.Sum(p => p.ZxAllCount),
                ZxAllTimes = list.Sum(p => p.ZxAllTimes),
                ZxAcceptanceTypeCode = "10",

                JyAllCount = list.Sum(p => p.JyAllCount),
                JyAllTimes = list.Sum(p => p.JyAllTimes),
                JyAcceptanceTypeCode = "15",

                QzAllCount = list.Sum(p => p.QzAllCount),
                QzAllTimes = list.Sum(p => p.QzAllTimes),
                QzAcceptanceTypeCode = "20",

                ByAllCount = list.Sum(p => p.ByAllCount),
                ByAllTimes = list.Sum(p => p.ByAllTimes),
                ByAcceptanceTypeCode = "25",

                JbAllCount = list.Sum(p => p.JbAllCount),
                JbAllTimes = list.Sum(p => p.JbAllTimes),
                JbAcceptanceTypeCode = "30",

                TsAllCount = list.Sum(p => p.TsAllCount),
                TsAllTimes = list.Sum(p => p.TsAllTimes),
                TsAcceptanceTypeCode = "35",

                QtAllCount = list.Sum(p => p.QtAllCount),
                QtAllTimes = list.Sum(p => p.QtAllTimes),
                QtAcceptanceTypeCode = "40",

                YjAllCount = list.Sum(p => p.YjAllCount),
                YjAllTimes = list.Sum(p => p.YjAllTimes),
                YjAcceptanceTypeCode = "1"
            });

            var orderDtos = _mapper.Map<ICollection<DepartmentAcceptanceTypeStatisticsDto>>(list);

            dynamic? dynamicClass = DynamicClassHelper.CreateDynamicClass(dto.ColumnInfos);

            var dtos = orderDtos
                .Select(stu => _mapper.Map(stu, typeof(DepartmentAcceptanceTypeStatisticsDto), dynamicClass))
                .Cast<object>()
                .ToList();

            var stream = ExcelHelper.CreateStream(dtos);

            return ExcelStreamResult(stream, "部门受理类型统计周期数据");
        }

        /// <summary>
        /// 部门受理类型统计周期--明细列表
        /// </summary>
        /// <param name="dto"></param>
        /// <returns></returns>
        [HttpGet("department_acceptance_type_order_list")]
        public async Task<PagedDto<OrderDto>> DepartmentAcceptanceTypeOrderList([FromQuery] DepartmentKeyWordRequest dto)
        {
            var (total, items) = await _orderReportApplication.DepartmentAcceptanceTypeOrderList(dto)
                .ToPagedListAsync(dto, HttpContext.RequestAborted);

            return new PagedDto<OrderDto>(total, _mapper.Map<IReadOnlyList<OrderDto>>(items));
        }

        /// <summary>
        /// 部门受理类型统计周期--明细列表---导出
        /// </summary>
        /// <param name="dto"></param>
        /// <returns></returns>
        [HttpPost("department_acceptance_type_order_list_export")]
        public async Task<FileStreamResult> ExportDepartmentAcceptanceTypeOrderList([FromBody] ExportExcelDto<DepartmentKeyWordRequest> dto)
        {
            var query = _orderReportApplication.DepartmentAcceptanceTypeOrderList(dto.QueryDto);
            List<Order> list;
            if (dto.IsExportAll)
            {
                list = await query.ToListAsync(HttpContext.RequestAborted);
            }
            else
            {
                var (_, items) = await query.ToPagedListAsync(dto.QueryDto, HttpContext.RequestAborted);
                list = items;
            }

            var orderDtos = _mapper.Map<ICollection<OrderDto>>(list);

            dynamic? dynamicClass = DynamicClassHelper.CreateDynamicClass(dto.ColumnInfos);

            var dtos = orderDtos
                .Select(stu => _mapper.Map(stu, typeof(OrderDto), dynamicClass))
                .Cast<object>()
                .ToList();

            var stream = ExcelHelper.CreateStream(dtos);

            return ExcelStreamResult(stream, "部门受理类型统计周期数据");
        }

        /// <summary>
        /// 部门办件统计表
        /// </summary>
        /// <param name="dto"></param>
        /// <returns></returns>
        [HttpGet("departmental_processing_statistics")]
        public async Task<object> DepartmentalProcessingStatistics([FromQuery] DepartmentalProcessingStatisticsRequest dto)
        {
            //联合查询
            //var items = await _orderReportApplication.DepartmentalProcessingStatistics(dto)
            //    .ToListAsync();

            var items = await _orderReportApplication.DepartmentalProcessingStatisticsNew(dto);

            var total = new DepartmentalProcessingStatisticsDataDto
            {
                OrgCode = "",
                OrgName = "合计",
                OrgType = EOrgType.City,
                // OrderCountNum = items.Sum(p => p.OrderCountNum),
                YbOrderCountNum = items.Sum(p => p.YbOrderCountNum),
                ZbOrderCountNum = items.Sum(p => p.ZbOrderCountNum),
                Archived = items.Sum(p => p.Archived),
                ToBeArchived = items.Sum(p => p.ToBeArchived),
                WaitPublished = items.Sum(p => p.WaitPublished),
                PublishedOpen = items.Sum(p => p.PublishedOpen),
                PublishedNoOpen = items.Sum(p => p.PublishedNoOpen),
                YbOverdue = items.Sum(p => p.YbOverdue),
                ZbOverdue = items.Sum(p => p.ZbOverdue),
                CompleteOnTime = items.Sum(p => p.CompleteOnTime),
                HqybOverdue = items.Sum(p => p.HqybOverdue),
                HqzbOverdue = items.Sum(p => p.HqzbOverdue),
                DelayEnd = items.Sum(p => p.DelayEnd),
                DelayWait = items.Sum(p => p.DelayWait),
                OrderDelayCount = items.Sum(p => p.OrderDelayCount),
                ScreenCount = items.Sum(p => p.ScreenCount),
                ScreenApproval = items.Sum(p => p.ScreenApproval),
                ScreenPass = items.Sum(p => p.ScreenPass),
                ScreenNotPass = items.Sum(p => p.ScreenNotPass),
                SatisfactionCount = items.Sum(p => p.SatisfactionCount),
                NotSatisfactionCount = items.Sum(p => p.NotSatisfactionCount)
            };
            return new { List = items, Total = total };
        }

        /// <summary>
        /// 部门办件统计表--导出
        /// </summary>
        /// <param name="dto"></param>
        /// <returns></returns>
        [HttpPost("departmental_processing_statistics_export")]
        public async Task<FileStreamResult> ExportDepartmentalProcessingStatistics([FromBody] ExportExcelDto<DepartmentalProcessingStatisticsRequest> dto)
        {
            var list = await _orderReportApplication.DepartmentalProcessingStatisticsNew(dto.QueryDto);

            //增加合计
            list.Add(new DepartmentalProcessingStatisticsDataDto
            {
                OrgCode = "",
                OrgName = "合计",
                OrgType = EOrgType.City,
                // OrderCountNum = items.Sum(p => p.OrderCountNum),
                YbOrderCountNum = list.Sum(p => p.YbOrderCountNum),
                ZbOrderCountNum = list.Sum(p => p.ZbOrderCountNum),
                Archived = list.Sum(p => p.Archived),
                ToBeArchived = list.Sum(p => p.ToBeArchived),
                WaitPublished = list.Sum(p => p.WaitPublished),
                PublishedOpen = list.Sum(p => p.PublishedOpen),
                PublishedNoOpen = list.Sum(p => p.PublishedNoOpen),
                YbOverdue = list.Sum(p => p.YbOverdue),
                ZbOverdue = list.Sum(p => p.ZbOverdue),
                CompleteOnTime = list.Sum(p => p.CompleteOnTime),
                HqybOverdue = list.Sum(p => p.HqybOverdue),
                HqzbOverdue = list.Sum(p => p.HqzbOverdue),
                DelayEnd = list.Sum(p => p.DelayEnd),
                DelayWait = list.Sum(p => p.DelayWait),
                OrderDelayCount = list.Sum(p => p.OrderDelayCount),
                ScreenCount = list.Sum(p => p.ScreenCount),
                ScreenApproval = list.Sum(p => p.ScreenApproval),
                ScreenPass = list.Sum(p => p.ScreenPass),
                ScreenNotPass = list.Sum(p => p.ScreenNotPass),
                SatisfactionCount = list.Sum(p => p.SatisfactionCount),
                NotSatisfactionCount = list.Sum(p => p.NotSatisfactionCount)
            });

            var orderDtos = _mapper.Map<ICollection<DepartmentalProcessingStatisticsDataDto>>(list);

            dynamic? dynamicClass = DynamicClassHelper.CreateDynamicClass(dto.ColumnInfos);

            var dtos = orderDtos
                .Select(stu => _mapper.Map(stu, typeof(DepartmentalProcessingStatisticsDataDto), dynamicClass))
                .Cast<object>()
                .ToList();

            var stream = ExcelHelper.CreateStream(dtos);

            return ExcelStreamResult(stream, "部门办件统计表数据");
        }

        /// <summary>
        /// 部门办件统计表--子级
        /// </summary>
        /// <param name="dto"></param>
        /// <returns></returns>
        [HttpGet("departmental_processing_child_statistics")]
        public async Task<object> DepartmentalProcessingChildStatistics([FromQuery] DepartmentalProcessingStatisticsRequest dto)
        {
            var items = await _orderReportApplication.DepartmentalProcessingChildStatisticsNew(dto);

            //计算合计
            var total = new DepartmentalProcessingStatisticsDataDto
            {
                OrgCode = "",
                OrgName = "合计",
                OrgType = EOrgType.City,
                // OrderCountNum = items.Sum(p => p.OrderCountNum),
                YbOrderCountNum = items.Sum(p => p.YbOrderCountNum),
                ZbOrderCountNum = items.Sum(p => p.ZbOrderCountNum),
                Archived = items.Sum(p => p.Archived),
                ToBeArchived = items.Sum(p => p.ToBeArchived),
                WaitPublished = items.Sum(p => p.WaitPublished),
                PublishedOpen = items.Sum(p => p.PublishedOpen),
                PublishedNoOpen = items.Sum(p => p.PublishedNoOpen),
                YbOverdue = items.Sum(p => p.YbOverdue),
                ZbOverdue = items.Sum(p => p.ZbOverdue),
                CompleteOnTime = items.Sum(p => p.CompleteOnTime),
                HqybOverdue = items.Sum(p => p.HqybOverdue),
                HqzbOverdue = items.Sum(p => p.HqzbOverdue),
                DelayEnd = items.Sum(p => p.DelayEnd),
                DelayWait = items.Sum(p => p.DelayWait),
                OrderDelayCount = items.Sum(p => p.OrderDelayCount),
                ScreenCount = items.Sum(p => p.ScreenCount),
                ScreenApproval = items.Sum(p => p.ScreenApproval),
                ScreenPass = items.Sum(p => p.ScreenPass),
                ScreenNotPass = items.Sum(p => p.ScreenNotPass),
                SatisfactionCount = items.Sum(p => p.SatisfactionCount),
                NotSatisfactionCount = items.Sum(p => p.NotSatisfactionCount)
            };
            return new { List = items, Total = total };
        }

        /// <summary>
        /// 部门办件统计表--子级--导出
        /// </summary>
        /// <param name="dto"></param>
        /// <returns></returns>
        [HttpPost("departmental_processing_child_statistics_export")]
        public async Task<FileStreamResult> ExportDepartmentalProcessingChildStatistics([FromBody] ExportExcelDto<DepartmentalProcessingStatisticsRequest> dto)
        {
            var list = await _orderReportApplication.DepartmentalProcessingChildStatisticsNew(dto.QueryDto);
            //增加合计
            list.Add(new DepartmentalProcessingStatisticsDataDto
            {
                OrgCode = "",
                OrgName = "合计",
                OrgType = EOrgType.City,
                // OrderCountNum = items.Sum(p => p.OrderCountNum),
                YbOrderCountNum = list.Sum(p => p.YbOrderCountNum),
                ZbOrderCountNum = list.Sum(p => p.ZbOrderCountNum),
                Archived = list.Sum(p => p.Archived),
                ToBeArchived = list.Sum(p => p.ToBeArchived),
                WaitPublished = list.Sum(p => p.WaitPublished),
                PublishedOpen = list.Sum(p => p.PublishedOpen),
                PublishedNoOpen = list.Sum(p => p.PublishedNoOpen),
                YbOverdue = list.Sum(p => p.YbOverdue),
                ZbOverdue = list.Sum(p => p.ZbOverdue),
                CompleteOnTime = list.Sum(p => p.CompleteOnTime),
                HqybOverdue = list.Sum(p => p.HqybOverdue),
                HqzbOverdue = list.Sum(p => p.HqzbOverdue),
                DelayEnd = list.Sum(p => p.DelayEnd),
                DelayWait = list.Sum(p => p.DelayWait),
                OrderDelayCount = list.Sum(p => p.OrderDelayCount),
                ScreenCount = list.Sum(p => p.ScreenCount),
                ScreenApproval = list.Sum(p => p.ScreenApproval),
                ScreenPass = list.Sum(p => p.ScreenPass),
                ScreenNotPass = list.Sum(p => p.ScreenNotPass),
                SatisfactionCount = list.Sum(p => p.SatisfactionCount),
                NotSatisfactionCount = list.Sum(p => p.NotSatisfactionCount)
            });

            var orderDtos = _mapper.Map<ICollection<DepartmentalProcessingStatisticsDataDto>>(list);

            dynamic? dynamicClass = DynamicClassHelper.CreateDynamicClass(dto.ColumnInfos);

            var dtos = orderDtos
                .Select(stu => _mapper.Map(stu, typeof(DepartmentalProcessingStatisticsDataDto), dynamicClass))
                .Cast<object>()
                .ToList();

            var stream = ExcelHelper.CreateStream(dtos);

            return ExcelStreamResult(stream, "部门办件统计表数据");
        }

        /// <summary>
        /// 部门办件统计表--明细
        /// </summary>
        /// <param name="dto"></param>
        /// <returns></returns>
        [HttpGet("departmental_processing_statistics_list")]
        public async Task<PagedDto<OrderDto>> GetDepartmentalProcessingStatisticsList([FromQuery] DepartmentalProcessingStatisticsRequest dto)
        {
            RefAsync<int> total = 0;

            var queryData = await _orderReportApplication.GetDepartmentalProcessingStatisticsListNew(dto, HttpContext.RequestAborted)
                        .LeftJoin<Order>((x, o) => x.Id == o.Id)
                        .OrderByDescending((x, o) => o.CreationTime)
                         .Select((x, o) => new { o })
                        .ToPageListAsync(dto.PageIndex, dto.PageSize, total, HttpContext.RequestAborted);

            var dtos = queryData.Select(d =>
            {
                var dto = _mapper.Map<OrderDto>(d.o);
                return dto;
            }).ToList();
            return new PagedDto<OrderDto>(total, dtos);
        }

        /// <summary>
        /// 部门办件统计表--明细--导出
        /// </summary>
        /// <param name="dto"></param>
        /// <returns></returns>
        [HttpPost("departmental_processing_statistics_list_export")]
        public async Task<FileStreamResult> ExportGetDepartmentalProcessingStatisticsList([FromBody] ExportExcelDto<DepartmentalProcessingStatisticsRequest> dto)
        {
            var query = _orderReportApplication.GetDepartmentalProcessingStatisticsListNew(dto.QueryDto, HttpContext.RequestAborted)
                 .LeftJoin<Order>((x, o) => x.Id == o.Id)
                 .OrderByDescending((x, o) => o.CreationTime)
                 .Select((x, o) => new { o });
            List<OrderDto> list = new List<OrderDto>();
            if (dto.IsExportAll)
            {
                var listData = await query.ToListAsync(HttpContext.RequestAborted);
                list = listData.Select(d =>
                {
                    var dto = _mapper.Map<OrderDto>(d.o);
                    return dto;
                }).ToList();
            }
            else
            {
                RefAsync<int> total = 0;
                var listData = await query.ToPageListAsync(dto.QueryDto.PageIndex, dto.QueryDto.PageSize, total, HttpContext.RequestAborted);
                list = listData.Select(d =>
                {
                    var dto = _mapper.Map<OrderDto>(d.o);
                    return dto;
                }).ToList();
            }

            dynamic? dynamicClass = DynamicClassHelper.CreateDynamicClass(dto.ColumnInfos);

            var dtos = list
                .Select(stu => _mapper.Map(stu, typeof(OrderDto), dynamicClass))
                .Cast<object>()
                .ToList();

            var stream = ExcelHelper.CreateStream(dtos);

            return ExcelStreamResult(stream, "部门办件统计明细数据");
        }

        /// <summary>
        /// 部门办件统计明细表
        /// </summary>
        /// <param name="dto"></param>
        /// <returns></returns>
        [HttpGet("departmental_processing_statistics_details_list")]
        public async Task<PagedDto<OrderDto>> DepartmentalProcessingStatisticsDetailsList([FromQuery] DepartmentalProcessingStatisticsRequest dto)
        {
            RefAsync<int> total = 0;

            var queryData = await _orderReportApplication.DepartmentalProcessingStatisticsDetailsList(dto)
                        .LeftJoin<Order>((x, o) => x.Id == o.Id)
                        .OrderByDescending((x, o) => o.CreationTime)
                         .Select((x, o) => new { o })
                        .ToPageListAsync(dto.PageIndex, dto.PageSize, total, HttpContext.RequestAborted);

            var dtos = queryData.Select(d =>
            {
                var dto = _mapper.Map<OrderDto>(d.o);
                return dto;
            }).ToList();
            return new PagedDto<OrderDto>(total, dtos);
        }

        /// <summary>
        /// 部门办件统计明细表--导出
        /// </summary>
        /// <param name="dto"></param>
        /// <returns></returns>
        [HttpPost("departmental_processing_statistics_details_list_export")]
        public async Task<FileStreamResult> ExportDepartmentalProcessingStatisticsDetailsList([FromBody] ExportExcelDto<DepartmentalProcessingStatisticsRequest> dto)
        {
            var query = _orderReportApplication.DepartmentalProcessingStatisticsDetailsList(dto.QueryDto)
                  .LeftJoin<Order>((x, o) => x.Id == o.Id)
                  .OrderByDescending((x, o) => o.CreationTime)
                  .Select((x, o) => new { o });
            var list = new List<OrderDto>();
            if (dto.IsExportAll)
            {
                var listData = await query.ToListAsync(HttpContext.RequestAborted);
                list = listData.Select(d =>
                {
                    var dto = _mapper.Map<OrderDto>(d.o);
                    return dto;
                }).ToList();
            }
            else
            {
                RefAsync<int> total = 0;
                var listData = await query.ToPageListAsync(dto.QueryDto.PageIndex, dto.QueryDto.PageSize, total, HttpContext.RequestAborted);
                list = listData.Select(d =>
                {
                    var dto = _mapper.Map<OrderDto>(d.o);
                    return dto;
                }).ToList();
            }

            dynamic? dynamicClass = DynamicClassHelper.CreateDynamicClass(dto.ColumnInfos);

            var dtos = list
                .Select(stu => _mapper.Map(stu, typeof(OrderDto), dynamicClass))
                .Cast<object>()
                .ToList();

            var stream = ExcelHelper.CreateStream(dtos);

            return ExcelStreamResult(stream, "部门办件统计明细表数据");
        }

        /// <summary>
        /// 高频来电统计
        /// </summary>
        /// <param name="dto"></param>
        /// <returns></returns>
        [HttpGet("high_frequency_call_statistics")]
        public async Task<PagedDto<HighFrequencyCallStatisticsDto>> HighFrequencyCallStatistics([FromQuery] HighFrequencyCallStatisticsRequest dto)
        {
            if (!dto.StartTime.HasValue || !dto.EndTime.HasValue)
                throw UserFriendlyException.SameMessage("请选择时间!");

            dto.EndTime = dto.EndTime.Value.AddDays(1).AddSeconds(-1);
            int CallCount = 2;
            var HighFrequencyCallStatistics = _systemSettingCacheManager.GetSetting(SettingConstants.HighFrequencyCallStatistics)?.SettingValue[0];
            if (HighFrequencyCallStatistics != null)
                CallCount = int.Parse(HighFrequencyCallStatistics);

            var (total, items) = await _trCallRecordRepository.Queryable()
                     .LeftJoin<Order>((p, o) => p.ExternalId == o.Id)
                   .Where((p, o) => p.OverTime >= dto.StartTime && p.OverTime <= dto.EndTime)
                   .Where((p, o) => p.CallOrderType == ECallOrderType.Order)
                   .Where((p, o) => p.ExternalId != null && o.Id != null)
                   .Where((p, o) => p.CallDirection == ECallDirection.In)
                    .WhereIF(!string.IsNullOrEmpty(dto.PhoneNum), (p, o) => p.CPN == dto.PhoneNum)
                   .Select((p, o) => new
                   {
                       p.CPN,
                       p.ExternalId
                   })
                   .MergeTable()
                   .GroupBy(p => p.CPN)
                   .Select(p => new HighFrequencyCallStatisticsDto
                   {
                       Callnum = p.CPN,
                       OrderCountNum = SqlFunc.AggregateCount(p.CPN),//总量
                   })
                  .MergeTable()
                 .Where(p => p.OrderCountNum >= CallCount)
                 .OrderByDescending(p => p.OrderCountNum)
                 .ToPagedListAsync(dto.PageIndex, dto.PageSize, HttpContext.RequestAborted);

            return new PagedDto<HighFrequencyCallStatisticsDto>(total, _mapper.Map<IReadOnlyList<HighFrequencyCallStatisticsDto>>(items));
        }

        /// <summary>
        /// 高频来电统计列表详情
        /// </summary>
        /// <param name="dto"></param>
        /// <returns></returns>
        [HttpGet("high_frequency_call_statistics_order_list")]
        public async Task<PagedDto<OrderDto>> HighFrequencyCallStatisticsOrderList([FromQuery] HighFrequencyCallStatisticsListRequest dto)
        {
            if (!dto.StartTime.HasValue || !dto.EndTime.HasValue)
                throw UserFriendlyException.SameMessage("请选择时间!");
            if (string.IsNullOrEmpty(dto.FromPhone))
                throw UserFriendlyException.SameMessage("号码不能为空!");

            dto.EndTime = dto.EndTime.Value.AddDays(1).AddSeconds(-1);
            var data = await _trCallRecordRepository.Queryable()
                    .LeftJoin<Order>((p, o) => p.ExternalId == o.Id)
                   .Where((p, o) => p.OverTime >= dto.StartTime && p.OverTime <= dto.EndTime)
                   .Where((p, o) => p.CallOrderType == ECallOrderType.Order)
                   .Where((p, o) => p.ExternalId != null && o.Id != null)
                    .Where((p, o) => p.CPN == dto.FromPhone)
                   .Where((p, o) => p.CallDirection == ECallDirection.In)
                   .Select((p, o) =>
                       p.ExternalId
                   )
                  .ToListAsync();


            var (total, items) = await _orderRepository.Queryable()
           .Includes(x => x.OrderScreens)
           .Where(p => data.Contains(p.Id))
           .WhereIF(!string.IsNullOrEmpty(dto.Keyword), d => d.Title.Contains(dto.Keyword!)) //标题
           .WhereIF(!string.IsNullOrEmpty(dto.ProvinceNo), d => d.ProvinceNo.Contains(dto.ProvinceNo)) //省本地编号
           .WhereIF(!string.IsNullOrEmpty(dto.No), d => d.No.Contains(dto.No)) //工单编码
           .WhereIF(dto.AcceptTypes.Any(), d => dto.AcceptTypes.Contains(d.AcceptTypeCode)) //受理类型
           .WhereIF(dto.Channels.Any(), d => dto.Channels.Contains(d.SourceChannelCode)) //来源渠道
           .WhereIF(dto.HotspotIds.Any(), d => dto.HotspotIds.Contains(d.HotspotId)) //热点类型
           .WhereIF(!string.IsNullOrEmpty(dto.TransferPhone), d => d.TransferPhone.Contains(dto.TransferPhone!)) //转接号码
           .WhereIF(dto.OrgCodes.Any(), d => dto.OrgCodes.Contains(d.ActualHandleOrgCode)) //接办部门
           .WhereIF(!string.IsNullOrEmpty(dto.NameOrNo), d => d.AcceptorName.Contains(dto.NameOrNo!) || d.AcceptorStaffNo.Contains(dto.NameOrNo!)) //受理人/坐席
           .WhereIF(dto.CreationTimeStart.HasValue, d => d.CreationTime >= dto.CreationTimeStart) //受理时间开始
           .WhereIF(dto.CreationTimeEnd.HasValue, d => d.CreationTime <= dto.CreationTimeEnd) //受理时间结束
           .WhereIF(dto.EmergencyLevels.Any(), d => dto.EmergencyLevels.Contains(d.EmergencyLevel))  //紧急程度
                                                                                                     //  .WhereIF(!string.IsNullOrEmpty(dto.FromPhone), d => d.FromPhone.Contains(dto.FromPhone)) //来电号码
           .WhereIF(!string.IsNullOrEmpty(dto.PhoneNo), d => d.Contact.Contains(dto.PhoneNo!)) //联系电话
           .WhereIF(!string.IsNullOrEmpty(dto.PushTypeCode), d => d.PushTypeCode == dto.PushTypeCode) //推送分类
           .WhereIF(dto.ExpiredTimeStart.HasValue, d => d.ExpiredTime >= dto.ExpiredTimeStart) //超期时间开始
           .WhereIF(dto.ExpiredTimeEnd.HasValue, d => d.ExpiredTime <= dto.ExpiredTimeEnd) //超期时间结束
           .WhereIF(dto.Statuses.Any(), d => dto.Statuses.Contains(d.Status))  //工单状态
           .WhereIF(dto.Statuses.Any(d => d == EOrderStatus.SpecialToUnAccept), d => d.Status <= EOrderStatus.SpecialToUnAccept)
           .WhereIF(!string.IsNullOrEmpty(dto.ActualHandlerName), d => d.ActualHandlerName.Contains(dto.ActualHandlerName)) //接办人
            .WhereIF(dto.IsScreen == true, d => d.OrderScreens.Any(x => x.Status != EScreenStatus.Refuse)) //有甄别
            .WhereIF(dto.IsScreen == false, d => !d.OrderScreens.Any(x => x.Status != EScreenStatus.Refuse)) //无甄别
            .WhereIF(!string.IsNullOrEmpty(dto.CurrentStepCode), d => d.ActualHandleStepCode == dto.CurrentStepCode) //当前办理节点
            .WhereIF(dto.ActualHandleTimeStart.HasValue, d => d.ActualHandleTime >= dto.ActualHandleTimeStart) //办结时间开始
            .WhereIF(dto.ActualHandleTimeEnd.HasValue, d => d.ActualHandleTime <= dto.ActualHandleTimeEnd) //办结时间结束
            .WhereIF(dto.IsOverTime == true, d => (d.ExpiredTime < DateTime.Now && d.Status < EOrderStatus.Filed) || (d.ExpiredTime < d.ActualHandleTime && d.Status >= EOrderStatus.Filed)) //是 超期
            .WhereIF(dto.IsOverTime == false, d => (d.ExpiredTime > DateTime.Now && d.Status < EOrderStatus.Filed) || (d.ExpiredTime > d.ActualHandleTime && d.Status >= EOrderStatus.Filed)) //否 超期
            .WhereIF(dto.IdentityType != null, d => d.IdentityType == dto.IdentityType) //来电主体
            .WhereIF(!string.IsNullOrEmpty(dto.FromName), d => d.FromName.Contains(dto.FromName)) //来电人姓名
            .WhereIF(dto.AreaCodes.Any(), d => dto.AreaCodes.Contains(d.AreaCode)) //区域
            .WhereIF(dto.IsProvinceOrder.HasValue && dto.IsProvinceOrder == true, x => x.IsProvince == true)
            .WhereIF(dto.IsProvinceOrder.HasValue && dto.IsProvinceOrder == false, x => x.IsProvince == false)
           .OrderByDescending(d => d.CreationTime)
           .ToPagedListAsync(dto, HttpContext.RequestAborted);

            return new PagedDto<OrderDto>(total, _mapper.Map<IReadOnlyList<OrderDto>>(items));

        }

        /// <summary>
        /// 高频事项预警
        /// </summary>
        /// <param name="dto"></param>
        /// <returns></returns>
        [HttpGet("highmatter-warning")]
        public async Task<PagedDto<HighMatterWarningDto>> HighMatterWarning([FromQuery] HighMatterWarningRequest dto)
        {

            dto.EndTime = dto.EndTime.AddDays(1).AddSeconds(-1);

            List<string> filterTitle = new List<string>();
            filterTitle.Add("无声");
            filterTitle.Add("骚扰");
            filterTitle.Add("错拨");
            filterTitle.Add("测试");

            var (total, items) = await _orderRepository.Queryable()
                .Where(x => x.CreationTime >= dto.StartTime && x.CreationTime <= dto.EndTime)
                .Where(x => filterTitle.Any(s => x.Title.Contains(s)) == false)
                .LeftJoin<SystemArea>((it, o) => it.AreaCode.Substring(SqlFunc.MappingColumn<int>("0"), SqlFunc.MappingColumn<int>("6")) == o.Id)
                .WhereIF(dto.AreaCodes.Any(), (it, o) => dto.AreaCodes.Contains(it.AreaCode)) //区域
                .WhereIF(dto.HotspotIds.Any(), (it, o) => dto.HotspotIds.Contains(it.HotspotId)) //热点类型
                .WhereIF(dto.AcceptTypeCodes.Any(), (it, o) => dto.AcceptTypeCodes.Contains(it.AcceptTypeCode)) //受理类型
                .GroupBy((it, o) => new
                {
                    it.AcceptTypeCode,
                    it.AcceptType,
                    it.HotspotId,
                    it.HotspotName,
                    AreaCode = it.AreaCode.Substring(SqlFunc.MappingColumn<int>("0"), SqlFunc.MappingColumn<int>("6")),
                    o.AreaName,
                })
                .Having((it, o) => SqlFunc.AggregateCount(it.HotspotName) >= 5)
                .Select((it, o) => new HighMatterWarningDto()
                {
                    AcceptTypeCode = it.AcceptTypeCode,
                    AcceptType = it.AcceptType,
                    AreaName = o.AreaName,
                    HotspotName = it.HotspotName,
                    HotspotId = it.HotspotId,
                    SumCount = SqlFunc.AggregateCount(it.HotspotName),
                    Id = SqlFunc.AggregateMin(it.Id),
                    AreaCode = it.AreaCode.Substring(SqlFunc.MappingColumn<int>("0"), SqlFunc.MappingColumn<int>("6"))
                })
                .MergeTable()
                .LeftJoin<Order>((x, d) => x.Id == d.Id)
                .Select((x, d) => new HighMatterWarningDto()
                {
                    AreaName = x.AreaName,
                    HotspotName = x.HotspotName,
                    HotspotId = x.HotspotId,
                    Title = d.Title,
                    SumCount = x.SumCount,
                    Id = d.Id,
                    AcceptTypeCode = x.AcceptTypeCode,
                    AcceptType = x.AcceptType,
                    AreaCode = x.AreaCode
                })
                .MergeTable()
                .OrderByDescending(d => d.SumCount).ToPagedListAsync(dto.PageIndex, dto.PageSize, HttpContext.RequestAborted);

            return new PagedDto<HighMatterWarningDto>(total, _mapper.Map<IReadOnlyList<HighMatterWarningDto>>(items));
        }

        /// <summary>
        /// 高频事项预警明细
        /// </summary>
        /// <param name="dto"></param>
        /// <returns></returns>
        [HttpGet("highmatter-warning-detail")]
        public async Task<PagedDto<OrderDto>> HighMatterWarningDetail([FromQuery] HighMatterWarningDetailRequest dto)
        {
            dto.EndTime = dto.EndTime.AddDays(1).AddSeconds(-1);
            List<string> filterTitle = new List<string>();
            filterTitle.Add("无声");
            filterTitle.Add("骚扰");
            filterTitle.Add("错拨");
            filterTitle.Add("测试");

            var (total, items) = await _orderRepository.Queryable()
            .Includes(x => x.OrderScreens)
            .Where(d => d.AcceptTypeCode == dto.AcceptTypeCode) //受理类型
            .Where(d => d.HotspotId == dto.HotspotId) //热点类型
            .Where(d => d.CreationTime >= dto.StartTime) //受理时间开始
            .Where(d => d.CreationTime <= dto.EndTime) //受理时间结束
            .Where(d => d.AreaCode == dto.AreaCode) //区域
            .Where(x => filterTitle.Any(s => x.Title.Contains(s)) == false)
            .OrderByDescending(d => d.CreationTime)
            .ToPagedListAsync(dto, HttpContext.RequestAborted);
            return new PagedDto<OrderDto>(total, _mapper.Map<IReadOnlyList<OrderDto>>(items));
        }

        /// <summary>
        /// 回退错件
        /// </summary>
        /// <param name="dto"></param>
        /// <returns></returns>
        [HttpGet("reTransact")]
        public async Task<PagedDto<OrderReTransactVo>> OrderReTransact([FromQuery] QueryOrderReTransactRequest dto)
        {
            if (!dto.StartTime.HasValue || !dto.EndTime.HasValue)
                throw UserFriendlyException.SameMessage("请选择时间!");
            dto.EndTime = dto.EndTime.Value.AddDays(1).AddSeconds(-1);
            var query = _orderApplication.OrderReTransact(dto);
            var (total, items) = await query
                .ToPagedListAsync(dto, HttpContext.RequestAborted);
            items.Add(new OrderReTransactVo
            {
                OrgName = "合计",
                Num = items.Select(s => s.Num).Sum(),
            });
            return new PagedDto<OrderReTransactVo>(total, _mapper.Map<IReadOnlyList<OrderReTransactVo>>(items));
        }

        /// <summary>
        /// 回退错件导出
        /// </summary>
        /// <returns></returns>
        [HttpPost("reTransact/_export")]
        public async Task<FileStreamResult> OrderReTransactExport([FromBody] ExportExcelDto<QueryOrderReTransactRequest> dto)
        {
            var query = _orderApplication.OrderReTransact(dto.QueryDto);
            List<OrderReTransactVo> data;
            data = await query.ToListAsync(HttpContext.RequestAborted);
            data.Add(new OrderReTransactVo
            {
                OrgName = "合计",
                Num = data.Select(s => s.Num).Sum(),
            });
            dynamic? dynamicClass = DynamicClassHelper.CreateDynamicClass(dto.ColumnInfos);
            var dtos = data
             .Select(stu => _mapper.Map(stu, typeof(OrderReTransactVo), dynamicClass))
             .Cast<object>()
             .ToList();

            var stream = ExcelHelper.CreateStream(dtos);

            return ExcelStreamResult(stream, "回退错件统计数据");
        }

        /// <summary>
        /// 回退错件明细
        /// </summary>
        /// <param name="dto"></param>
        /// <returns></returns>
        [HttpGet("reTransact_detail")]
        public async Task<PagedDto<OrderSpecialDetailDto>> OrderReTransactDetail([FromQuery] QueryOrderReTransactDetailRequest dto)
        {
            if (!dto.StartTime.HasValue || !dto.EndTime.HasValue)
                throw UserFriendlyException.SameMessage("请选择时间!");
            dto.EndTime = dto.EndTime.Value.AddDays(1).AddSeconds(-1);

            var query = _orderApplication.QueryOrderSourceDetail(dto);
            var (total, items) = await
                query.ToPagedListAsync(dto, HttpContext.RequestAborted);
            return new PagedDto<OrderSpecialDetailDto>(total, _mapper.Map<IReadOnlyList<OrderSpecialDetailDto>>(items));
        }

        /// <summary>
        /// 回退错件明细导出
        /// </summary>
        /// <returns></returns>
        [HttpPost("reTransact_detail/_export")]
        public async Task<FileStreamResult> OrderReTransactDetailExport([FromBody] ExportExcelDto<QueryOrderReTransactDetailRequest> dto)
        {
            var query = _orderApplication.QueryOrderSourceDetail(dto.QueryDto);
            List<OrderSpecialDetail> data;
            if (dto.IsExportAll)
            {
                data = await query.ToListAsync(HttpContext.RequestAborted);
            }
            else
            {
                var (_, items) = await query.ToPagedListAsync(dto.QueryDto, HttpContext.RequestAborted);
                data = items;
            }

            var dataDtos = _mapper.Map<ICollection<OrderSpecialDetailDto>>(data);

            dynamic? dynamicClass = DynamicClassHelper.CreateDynamicClass(dto.ColumnInfos);

            var dtos = dataDtos
                .Select(stu => _mapper.Map(stu, typeof(OrderSpecialDetailDto), dynamicClass))
                .Cast<object>()
                .ToList();

            var stream = ExcelHelper.CreateStream(dtos);

            return ExcelStreamResult(stream, "回退错件明细列表数据");
        }

        /// <summary>
        /// 获取基本信息
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        [HttpGet("reTransact_base")]
        public async Task<object> ReTransactBaseData()
        {
            var rsp = new
            {
                ReTransactErrorType = _sysDicDataCacheManager.GetSysDicDataCache(SysDicTypeConsts.ReTransactErrorType),
            };
            return rsp;
        }

        /// <summary>
        /// 派单量统计
        /// </summary>
        /// <param name="dto"></param>
        /// <returns></returns>
        [HttpGet("send_order_report")]
        public async Task<object> SendOrderReport([FromQuery] QuerySendOrderRequest dto)
        {
            if (!dto.StartTime.HasValue || !dto.EndTime.HasValue)
                throw UserFriendlyException.SameMessage("请选择时间!");
            dto.EndTime = dto.EndTime.Value.AddDays(1).AddSeconds(-1);

            var items = await _workflowTraceRepository.Queryable()
                .LeftJoin<Workflow>((x, w) => x.WorkflowId == w.Id)
                //.LeftJoin<WorkflowStepHandler>((x, w, wsh) => x.StepId == wsh.WorkflowStepId && wsh.IsActualHandler == true)
                .InnerJoin<SchedulingUser>((x, w, su) => x.HandlerId == su.UserId)
                .Where((x, w, su) => w.ModuleCode == "OrderHandle" && x.BusinessType == EBusinessType.Send && x.Status == EWorkflowStepStatus.Handled)
                .Where((x, w, su) => x.CreationTime >= dto.StartTime.Value)
                .Where((x, w, su) => x.CreationTime <= dto.EndTime.Value)
                .WhereIF(!string.IsNullOrEmpty(dto.UserName), (x, w, su) => su.UserName == dto.UserName)
                .GroupBy((x, w, su) => new { su.UserId, su.UserName })
                //.Having((x, w, wsh, su) => SqlFunc.AggregateCount(x.WorkflowId) == 1)
                .Select((x, w, su) => new BiOrderSendVo
                {
                    UserId = su.UserId,
                    UserName = su.UserName,
                    SendOrderNum = SqlFunc.AggregateDistinctCount(w.ExternalId),
                    NoSendOrderNum = SqlFunc.AggregateSum(SqlFunc.IIF(x.HandlerId == null || x.HandlerId == "", 1, 0)),
                }).ToListAsync();

            var items2 = await _workflowTraceRepository.Queryable()
                .LeftJoin<Workflow>((x, w) => x.WorkflowId == w.Id)
                //.LeftJoin<WorkflowStepHandler>((x, w, wfsh) => x.StepId == wfsh.WorkflowStepId && wfsh.IsActualHandler == true)
                .InnerJoin<SchedulingUser>((x, w, su) => x.HandlerId == su.UserId)
                .Where((x, w, su) => w.ModuleCode == "OrderHandle" && x.BusinessType == EBusinessType.Send && x.Status == EWorkflowStepStatus.Handled)
                .Where((x, w, su) => x.CreationTime >= dto.StartTime.Value)
                .Where((x, w, su) => x.CreationTime <= dto.EndTime.Value)
                .GroupBy((x, w, su) => x.WorkflowId)
                .Having((x, w, su) => SqlFunc.AggregateCount(x.WorkflowId) > 1)
                .Select((x, w, su) => new { Id = x.WorkflowId, CreationTime = SqlFunc.AggregateMin(x.CreationTime) })
                .MergeTable()
                .LeftJoin<WorkflowTrace>((a, wt) => a.Id == wt.WorkflowId)
                .LeftJoin<Workflow>((a, wt, wf) => wt.WorkflowId == wf.Id)
                //.LeftJoin<WorkflowStepHandler>((a, wt, wf, wsh) => wt.StepId == wsh.WorkflowStepId && wsh.CreationTime == a.CreationTime)
                .InnerJoin<SchedulingUser>((a, wt, wf, su) => wt.HandlerId == su.UserId)
                .WhereIF(!string.IsNullOrEmpty(dto.UserName), ((a, wt, wf, su) => su.UserName == dto.UserName))
                .GroupBy((a, wt, wf, su) => new { su.UserId, su.UserName })
                .Select((a, wt, wf, su) => new BiOrderSendVo
                {
                    UserId = su.UserId,
                    UserName = su.UserName,
                    SendOrderNum = 0,
                    NoSendOrderNum = 0,
                    ReSendOrderNum = SqlFunc.AggregateDistinctCount(wf.ExternalId),
                }).ToListAsync();

            var res = (from t1 in items
                       join t2 in items2 on t1.UserId equals t2.UserId into t1_t2
                       from item in t1_t2.DefaultIfEmpty()
                       select new
                       {
                           UserId = t1.UserId,
                           UserName = t1.UserName,
                           SendOrderNum = t1.SendOrderNum,
                           NoSendOrderNum = t1.NoSendOrderNum,
                           ReSendOrderNum = t1_t2.Select(x => x.ReSendOrderNum).FirstOrDefault(),
                           ChainRate = t1_t2.Select(x => x.ReSendOrderNum).FirstOrDefault() > 0 ?
                                     ((double.Parse(t1.SendOrderNum.ToString()) - double.Parse(t1_t2.Select(x => x.ReSendOrderNum).FirstOrDefault().ToString())) / double.Parse(t1.SendOrderNum.ToString()) * 100).ToString("F2") + "%" : "100.00%",
                       }).ToList();

            return res;
        }

        /// <summary>
        /// 派单量统计明细
        /// </summary>
        /// <param name="dto"></param>
        /// <returns></returns>
        [HttpGet("send_order_detail_report")]
        public async Task<PagedDto<OrderDto>> QuerySendOrderDetail([FromQuery] QuerySendOrderDetailRequest dto)
        {
            if (!dto.StartTime.HasValue || !dto.EndTime.HasValue)
                throw UserFriendlyException.SameMessage("请选择时间!");
            dto.EndTime = dto.EndTime.Value.AddDays(1).AddSeconds(-1);

            var (total, items) = await _workflowTraceRepository.Queryable()
                .LeftJoin<Workflow>((x, w) => x.WorkflowId == w.Id)
                //.LeftJoin<WorkflowStepHandler>((x, w, wsh) => x.StepId == wsh.WorkflowStepId && wsh.IsActualHandler == true)
                .InnerJoin<SchedulingUser>((x, w, su) => x.HandlerId == su.UserId)
                .Where((x, w, su) => w.ModuleCode == "OrderHandle" && x.BusinessType == EBusinessType.Send)
                .Where((x, w, su) => x.CreationTime >= dto.StartTime.Value)
                .Where((x, w, su) => x.CreationTime <= dto.EndTime.Value)
                .Where((x, w, su) => su.UserId == dto.UserId)
                .WhereIF(dto.TitleCode.ToUpper() == "NOSENDORDERNUM", (x, w, su) => x.HandlerId == null || x.HandlerId == "")
                .GroupBy((x, w, su) => w.ExternalId)
                .Select((x, w, su) => new { Id = w.ExternalId })
                .MergeTable()
                .LeftJoin<Order>((a, b) => a.Id == b.Id)
                .Select((a, b) => b)
                .ToPagedListAsync(dto, HttpContext.RequestAborted);

            if (dto.TitleCode.ToUpper() == "RESENDORDERNUM")
            {
                (total, items) = await _workflowTraceRepository.Queryable()
                       .LeftJoin<Workflow>((x, w) => x.WorkflowId == w.Id)
                       //.LeftJoin<WorkflowStepHandler>((x, w, wfsh) => x.StepId == wfsh.WorkflowStepId && wfsh.IsActualHandler == true)
                       .InnerJoin<SchedulingUser>((x, w, su) => x.HandlerId == su.UserId)
                       .Where((x, w, su) => w.ModuleCode == "OrderHandle" && x.BusinessType == EBusinessType.Send)
                       .Where((x, w, su) => x.CreationTime >= dto.StartTime.Value)
                       .Where((x, w, su) => x.CreationTime <= dto.EndTime.Value)
                       .GroupBy((x, w, su) => x.WorkflowId)
                       .Having((x, w, su) => SqlFunc.AggregateCount(x.WorkflowId) > 1)
                        .Select((x, w, su) => new { Id = x.WorkflowId, CreationTime = SqlFunc.AggregateMin(x.CreationTime) })
                       .MergeTable()
                       .LeftJoin<WorkflowTrace>((a, wt) => a.Id == wt.WorkflowId)
                       .LeftJoin<Workflow>((a, wt, wf) => wt.WorkflowId == wf.Id)
                       //.LeftJoin<WorkflowStepHandler>((a, wt, wf, wsh) => wt.StepId == wsh.WorkflowStepId && wsh.CreationTime == a.CreationTime)
                       .InnerJoin<SchedulingUser>((a, wt, wf, su) => wt.HandlerId == su.UserId)
                       .Where((a, wt, wf, su) => su.UserId == dto.UserId)
                       .GroupBy((a, wt, wf, su) => wf.ExternalId)
                       .Select((a, wt, wf, su) => new { Id = wf.ExternalId })
                       .MergeTable()
                       .LeftJoin<Order>((a, b) => a.Id == b.Id)
                       .Select((a, b) => b)
                       .ToPagedListAsync(dto, HttpContext.RequestAborted);
            }
            return new PagedDto<OrderDto>(total, _mapper.Map<IReadOnlyList<OrderDto>>(items));
        }

        /// <summary>
        /// 二次办理统计
        /// </summary>
        /// <param name="dto"></param>
        /// <returns></returns>
        [HttpGet("secondary_handling_report")]
        public async Task<List<SecondaryHandlingVo>> SecondaryHandlingReport([FromQuery] QuerySecondaryHandlingRequest dto)
        {
            var query = _orderSecondaryHandlingApplication.SecondaryHandlingReport(dto, HttpContext.RequestAborted);
            return await query.ToListAsync();
        }

        /// <summary>
        /// 二次办理统计导出
        /// </summary>
        /// <returns></returns>
        [HttpPost("secondary_handling_report/_export")]
        public async Task<FileStreamResult> SecondaryHandlingReportExport([FromBody] ExportExcelDto<QuerySecondaryHandlingRequest> dto)
        {
            var query = _orderSecondaryHandlingApplication.SecondaryHandlingReport(dto.QueryDto, HttpContext.RequestAborted);
            List<SecondaryHandlingVo> secondaryHandling;
            secondaryHandling = await query.ToListAsync(HttpContext.RequestAborted);
            dynamic? dynamicClass = DynamicClassHelper.CreateDynamicClass(dto.ColumnInfos);
            var dtos = secondaryHandling
                .Select(stu => _mapper.Map(stu, typeof(SecondaryHandlingVo), dynamicClass))
                .Cast<object>()
                .ToList();

            var stream = ExcelHelper.CreateStream(dtos);

            return ExcelStreamResult(stream, "二次办理统计数据");
        }

        /// <summary>
        /// 二次办理明细
        /// </summary>
        /// <param name="dto"></param>
        /// <returns></returns>
        [HttpGet("secondary_handling_detail_report")]
        public async Task<PagedDto<OrderSecondaryHandlingDto>> SecondaryHandlingDetailReport([FromQuery] QuerySecondaryHandlingRequest dto)
        {
            var query = _orderSecondaryHandlingApplication.SecondaryHandlingDetailReport(dto, HttpContext.RequestAborted);
            var (total, items) = await query.ToPagedListAsync(dto, HttpContext.RequestAborted);

            return new PagedDto<OrderSecondaryHandlingDto>(total, _mapper.Map<IReadOnlyList<OrderSecondaryHandlingDto>>(items));
        }

        /// <summary>
        /// 二次办理明细导出
        /// </summary>
        /// <returns></returns>
        [HttpPost("secondary_handling_detail_report/_export")]
        public async Task<FileStreamResult> SecondaryHandlingDetailReportExport([FromBody] ExportExcelDto<QuerySecondaryHandlingRequest> dto)
        {
            var query = _orderSecondaryHandlingApplication.SecondaryHandlingDetailReport(dto.QueryDto, HttpContext.RequestAborted);
            List<OrderSecondaryHandling> secondaryHandling;
            if (dto.IsExportAll)
            {
                secondaryHandling = await query.ToListAsync(HttpContext.RequestAborted);
            }
            else
            {
                var (_, items) = await query.ToPagedListAsync(dto.QueryDto, HttpContext.RequestAborted);
                secondaryHandling = items;
            }

            var secondaryHandlingDtos = _mapper.Map<ICollection<OrderSecondaryHandlingDto>>(secondaryHandling);

            dynamic? dynamicClass = DynamicClassHelper.CreateDynamicClass(dto.ColumnInfos);

            var dtos = secondaryHandlingDtos
                .Select(stu => _mapper.Map(stu, typeof(OrderSecondaryHandlingDto), dynamicClass))
                .Cast<object>()
                .ToList();

            var stream = ExcelHelper.CreateStream(dtos);

            return ExcelStreamResult(stream, "二次办理列表数据");
        }

        /// <summary>
        /// 二次办理满意度统计
        /// </summary>
        /// <param name="dto"></param>
        /// <returns></returns>
        [HttpGet("secondary_handling_satisfaction_report")]
        public async Task<List<SecondaryHandlingSatisfactionVo>> SecondaryHandlingSatisfactionReport([FromQuery] QuerySecondaryHandlingRequest dto)
        {
            var query = _orderSecondaryHandlingApplication.SecondaryHandlingSatisfactionReport(dto, HttpContext.RequestAborted);
            var list = await query.ToListAsync();
            //总计
            var total = new SecondaryHandlingSatisfactionVo
            {
                OrgId = "0",
                OrgName = "总计",
                TotalSumCount = list.Select(x => x.TotalSumCount).Sum(),
                VerySatisfiedCount = list.Select(x => x.VerySatisfiedCount).Sum(),
                SatisfiedCount = list.Select(x => x.SatisfiedCount).Sum(),
                RegardedAsSatisfiedCount = list.Select(x => x.RegardedAsSatisfiedCount).Sum(),
                DefaultSatisfiedCount = list.Select(x => x.DefaultSatisfiedCount).Sum(),
                NoSatisfiedCount = list.Select(x => x.NoSatisfiedCount).Sum(),
                NoEvaluateCount = list.Select(x => x.NoEvaluateCount).Sum(),
                NoPutThroughCount = list.Select(x => x.NoPutThroughCount).Sum()
            };
            list.Add(total);
            //区县合计
            var countyList = list.Where(x => x.OrgType == EOrgType.County).ToList();
            var countyTotal = new SecondaryHandlingSatisfactionVo
            {
                OrgId = "0",
                OrgName = "区县合计",
                TotalSumCount = countyList.Select(x => x.TotalSumCount).Sum(),
                VerySatisfiedCount = countyList.Select(x => x.VerySatisfiedCount).Sum(),
                SatisfiedCount = countyList.Select(x => x.SatisfiedCount).Sum(),
                RegardedAsSatisfiedCount = countyList.Select(x => x.RegardedAsSatisfiedCount).Sum(),
                DefaultSatisfiedCount = countyList.Select(x => x.DefaultSatisfiedCount).Sum(),
                NoSatisfiedCount = countyList.Select(x => x.NoSatisfiedCount).Sum(),
                NoEvaluateCount = countyList.Select(x => x.NoEvaluateCount).Sum(),
                NoPutThroughCount = countyList.Select(x => x.NoPutThroughCount).Sum()
            };
            list.Add(countyTotal);
            //市直合计
            var cityList = list.Where(x => x.OrgType == EOrgType.City).ToList();
            var cityTotal = new SecondaryHandlingSatisfactionVo
            {
                OrgId = "0",
                OrgName = "市直合计",
                TotalSumCount = cityList.Select(x => x.TotalSumCount).Sum(),
                VerySatisfiedCount = cityList.Select(x => x.VerySatisfiedCount).Sum(),
                SatisfiedCount = cityList.Select(x => x.SatisfiedCount).Sum(),
                RegardedAsSatisfiedCount = cityList.Select(x => x.RegardedAsSatisfiedCount).Sum(),
                DefaultSatisfiedCount = cityList.Select(x => x.DefaultSatisfiedCount).Sum(),
                NoSatisfiedCount = cityList.Select(x => x.NoSatisfiedCount).Sum(),
                NoEvaluateCount = cityList.Select(x => x.NoEvaluateCount).Sum(),
                NoPutThroughCount = cityList.Select(x => x.NoPutThroughCount).Sum()
            };
            list.Add(cityTotal);
            return list;
        }

        /// <summary>
        /// 二次办理满意度统计导出
        /// </summary>
        /// <returns></returns>
        [HttpPost("secondary_handling_satisfaction_report/_export")]
        public async Task<FileStreamResult> SecondaryHandlingSatisfactionReportExport([FromBody] ExportExcelDto<QuerySecondaryHandlingRequest> dto)
        {
            var query = _orderSecondaryHandlingApplication.SecondaryHandlingSatisfactionReport(dto.QueryDto, HttpContext.RequestAborted);
            List<SecondaryHandlingSatisfactionVo> secondaryHandling;
            secondaryHandling = await query.ToListAsync(HttpContext.RequestAborted);
            //总计
            var total = new SecondaryHandlingSatisfactionVo
            {
                OrgId = "0",
                OrgName = "总计",
                TotalSumCount = secondaryHandling.Select(x => x.TotalSumCount).Sum(),
                VerySatisfiedCount = secondaryHandling.Select(x => x.VerySatisfiedCount).Sum(),
                SatisfiedCount = secondaryHandling.Select(x => x.SatisfiedCount).Sum(),
                RegardedAsSatisfiedCount = secondaryHandling.Select(x => x.RegardedAsSatisfiedCount).Sum(),
                DefaultSatisfiedCount = secondaryHandling.Select(x => x.DefaultSatisfiedCount).Sum(),
                NoSatisfiedCount = secondaryHandling.Select(x => x.NoSatisfiedCount).Sum(),
                NoEvaluateCount = secondaryHandling.Select(x => x.NoEvaluateCount).Sum(),
                NoPutThroughCount = secondaryHandling.Select(x => x.NoPutThroughCount).Sum()
            };
            secondaryHandling.Add(total);
            //区县合计
            var countyList = secondaryHandling.Where(x => x.OrgType == EOrgType.County).ToList();
            var countyTotal = new SecondaryHandlingSatisfactionVo
            {
                OrgId = "0",
                OrgName = "区县合计",
                TotalSumCount = countyList.Select(x => x.TotalSumCount).Sum(),
                VerySatisfiedCount = countyList.Select(x => x.VerySatisfiedCount).Sum(),
                SatisfiedCount = countyList.Select(x => x.SatisfiedCount).Sum(),
                RegardedAsSatisfiedCount = countyList.Select(x => x.RegardedAsSatisfiedCount).Sum(),
                DefaultSatisfiedCount = countyList.Select(x => x.DefaultSatisfiedCount).Sum(),
                NoSatisfiedCount = countyList.Select(x => x.NoSatisfiedCount).Sum(),
                NoEvaluateCount = countyList.Select(x => x.NoEvaluateCount).Sum(),
                NoPutThroughCount = countyList.Select(x => x.NoPutThroughCount).Sum()
            };
            secondaryHandling.Add(countyTotal);
            //市直合计
            var cityList = secondaryHandling.Where(x => x.OrgType == EOrgType.City).ToList();
            var cityTotal = new SecondaryHandlingSatisfactionVo
            {
                OrgId = "0",
                OrgName = "市直合计",
                TotalSumCount = cityList.Select(x => x.TotalSumCount).Sum(),
                VerySatisfiedCount = cityList.Select(x => x.VerySatisfiedCount).Sum(),
                SatisfiedCount = cityList.Select(x => x.SatisfiedCount).Sum(),
                RegardedAsSatisfiedCount = cityList.Select(x => x.RegardedAsSatisfiedCount).Sum(),
                DefaultSatisfiedCount = cityList.Select(x => x.DefaultSatisfiedCount).Sum(),
                NoSatisfiedCount = cityList.Select(x => x.NoSatisfiedCount).Sum(),
                NoEvaluateCount = cityList.Select(x => x.NoEvaluateCount).Sum(),
                NoPutThroughCount = cityList.Select(x => x.NoPutThroughCount).Sum()
            };
            secondaryHandling.Add(cityTotal);

            dynamic? dynamicClass = DynamicClassHelper.CreateDynamicClass(dto.ColumnInfos);
            var dtos = secondaryHandling
                .Select(stu => _mapper.Map(stu, typeof(SecondaryHandlingSatisfactionVo), dynamicClass))
                .Cast<object>()
                .ToList();

            var stream = ExcelHelper.CreateStream(dtos);

            return ExcelStreamResult(stream, "二次办理满意度统计数据");
        }

        /// <summary>
        /// 二次办理满意度明细
        /// </summary>
        /// <param name="dto"></param>
        /// <returns></returns>
        [HttpGet("secondary_handling_satisfaction_detail_report")]
        public async Task<PagedDto<OrderSecondaryHandlingDto>> SecondaryHandlingSatisfactionDetailReport([FromQuery] QuerySecondaryHandlingRequest dto)
        {
            var query = _orderSecondaryHandlingApplication.SecondaryHandlingSatisfactionDetailReport(dto, HttpContext.RequestAborted);
            var (total, items) = await query.ToPagedListAsync(dto, HttpContext.RequestAborted);

            return new PagedDto<OrderSecondaryHandlingDto>(total, _mapper.Map<IReadOnlyList<OrderSecondaryHandlingDto>>(items));
        }

        /// <summary>
        /// 二次办理满意度明细导出
        /// </summary>
        /// <returns></returns>
        [HttpPost("secondary_handling_satisfaction_detail_report/_export")]
        public async Task<FileStreamResult> SecondaryHandlingSatisfactionDetailReport([FromBody] ExportExcelDto<QuerySecondaryHandlingRequest> dto)
        {
            var query = _orderSecondaryHandlingApplication.SecondaryHandlingSatisfactionDetailReport(dto.QueryDto, HttpContext.RequestAborted);
            List<OrderSecondaryHandling> secondaryHandling;
            if (dto.IsExportAll)
            {
                secondaryHandling = await query.ToListAsync(HttpContext.RequestAborted);
            }
            else
            {
                var (_, items) = await query.ToPagedListAsync(dto.QueryDto, HttpContext.RequestAborted);
                secondaryHandling = items;
            }

            var secondaryHandlingDtos = _mapper.Map<ICollection<OrderSecondaryHandlingDto>>(secondaryHandling);

            dynamic? dynamicClass = DynamicClassHelper.CreateDynamicClass(dto.ColumnInfos);

            var dtos = secondaryHandlingDtos
                .Select(stu => _mapper.Map(stu, typeof(OrderSecondaryHandlingDto), dynamicClass))
                .Cast<object>()
                .ToList();

            var stream = ExcelHelper.CreateStream(dtos);

            return ExcelStreamResult(stream, "二次办理满意度列表数据");
        }

        /// <summary>
        /// 未签收统计
        /// </summary>
        /// <param name="dto"></param>
        /// <returns></returns>
        [HttpGet("unsigned_order_report")]
        public async Task<PagedDto<UnsignedOrderDto>> UnsignedOrderReport([FromQuery] QueryUnsignedOrdersRequest dto)
        {
            var query = _orderApplication.QueryUnsignedOrders(dto);
            var (total, items) = await query.Select((x, ws) => new UnsignedOrder { Order = x, WorkflowStep = ws }).ToPagedListAsync(dto, HttpContext.RequestAborted);
            var itemsVo = _mapper.Map<IReadOnlyList<UnsignedOrderDto>>(items);
            foreach (var item in itemsVo)
            {
                item.UnsignedTime = _timeLimitDomainService.CalcWorkTimeToHour(item.WorkflowStep.CreationTime, item.WorkflowStep.Status == EWorkflowStepStatus.WaitForAccept ? DateTime.Now : item.WorkflowStep.AcceptTime!.Value, false);
            }
            return new PagedDto<UnsignedOrderDto>(total, itemsVo);
        }

        /// <summary>
        /// 未签收统计导出
        /// </summary>
        /// <returns></returns>
        [HttpPost("unsigned_order_report/_export")]
        public async Task<FileStreamResult> UnsignedOrdersReport([FromBody] ExportExcelDto<QueryUnsignedOrdersRequest> dto)
        {
            var query = _orderApplication.QueryUnsignedOrders(dto.QueryDto).Select((x, ws) => new UnsignedOrder { Order = x, WorkflowStep = ws });
            List<UnsignedOrder> unsignedOrders;
            if (dto.IsExportAll)
            {
                unsignedOrders = await query.ToListAsync(HttpContext.RequestAborted);
            }
            else
            {
                var (_, items) = await query.ToPagedListAsync(dto.QueryDto, HttpContext.RequestAborted);
                unsignedOrders = items;
            }

            var unsignedOrderDtos = _mapper.Map<ICollection<UnsignedOrderDto>>(unsignedOrders);

            foreach (var item in unsignedOrderDtos)
            {
                item.UnsignedTime = _timeLimitDomainService.CalcWorkTimeToHour(item.WorkflowStep.CreationTime, item.WorkflowStep.Status == EWorkflowStepStatus.WaitForAccept ? DateTime.Now : item.WorkflowStep.AcceptTime!.Value, false);
            }

            dynamic? dynamicClass = DynamicClassHelper.CreateDynamicClass(dto.ColumnInfos);

            var dtos = unsignedOrderDtos
                .Select(stu => _mapper.Map(stu, typeof(UnsignedOrderDto), dynamicClass))
                .Cast<object>()
                .ToList();

            var stream = ExcelHelper.CreateStream(dtos);

            return ExcelStreamResult(stream, "未签收统计列表数据");
        }

        /// <summary>
        /// 信件来源统计
        /// </summary>
        /// <param name="dto"></param>
        /// <returns></returns>
        [HttpGet("order_source_report")]
        public async Task<IReadOnlyList<OrderSourceVo>> QueryOrderSourceReport([FromQuery] QueryOrderSourceRequest dto)
        {
            var count = await _orderApplication.QueryOrderSource(dto).CountAsync();
            var query = _orderApplication.QueryOrderSource(dto);
            var items = await query.GroupBy(d => d.SourceChannel).Select(d => new OrderSourceVo { Source = d.SourceChannel, Num = SqlFunc.AggregateCount(d.Id), TotalSumCount = count }).ToListAsync(HttpContext.RequestAborted);
            items.Add(new OrderSourceVo { Source = "合计", Num = count, TotalSumCount = count });
            return items;
        }

        /// <summary>
        /// 信件来源统计导出
        /// </summary>
        /// <returns></returns>
        [HttpPost("order_source/_export")]
        public async Task<FileStreamResult> QueryOrderSourceReport([FromBody] ExportExcelDto<QueryOrderSourceRequest> dto)
        {
            var count = await _orderApplication.QueryOrderSource(dto.QueryDto).CountAsync();
            var query = _orderApplication.QueryOrderSource(dto.QueryDto).GroupBy(d => d.SourceChannel).Select(d => new OrderSourceVo { Source = d.SourceChannel, Num = SqlFunc.AggregateCount(d.Id), TotalSumCount = count });
            List<OrderSourceVo> orderSources;
            if (dto.IsExportAll)
            {
                orderSources = await query.ToListAsync(HttpContext.RequestAborted);
            }
            else
            {
                var (_, items) = await query.ToPagedListAsync(dto.QueryDto, HttpContext.RequestAborted);
                orderSources = items;
            }
            orderSources.Add(new OrderSourceVo { Source = "合计", Num = count, TotalSumCount = count });
            dynamic? dynamicClass = DynamicClassHelper.CreateDynamicClass(dto.ColumnInfos);
            var dtos = orderSources
                .Select(stu => _mapper.Map(stu, typeof(OrderSourceVo), dynamicClass))
                .Cast<object>()
                .ToList();
            var stream = ExcelHelper.CreateStream(dtos);
            return ExcelStreamResult(stream, "信件来源统计数据");
        }

        /// <summary>
        /// 信件来源统计列表
        /// </summary>
        /// <param name="dto"></param>
        /// <returns></returns>
        [HttpGet("order_source_report_list")]
        public async Task<Object> QueryOrderSourceReportList([FromQuery] QueryOrderSourceRequest dto)
        {
            var item = await _orderRepository.OrderSource(dto);

            var titleData = await _systemDicDataRepository.Queryable()
                .Where(p => p.DicTypeCode == "SourceChannel")
                .Select(p => new
                {
                    Key = p.DicDataValue,
                    Value = p.DicDataName
                }).ToListAsync();

            return new { Item = item, TitleData = titleData };
        }

        /// <summary>
        /// 信件来源统计导出
        /// </summary>
        /// <returns></returns>
        [HttpPost("order_source_list/_export")]
        public async Task<FileStreamResult> QueryOrderSourceListExport([FromBody] QueryOrderSourceRequest dto)
        {
            var dataTable = await _orderRepository.OrderSourceExport(dto);
            var stream = ExcelHelper.CreateStream(dataTable);
            return ExcelStreamResult(stream, "信件来源统计数据");
        }

        /// <summary>
        /// 信件来源统计明细
        /// </summary>
        /// <param name="dto"></param>
        /// <returns></returns>
        [HttpGet("order_source_detail_report")]
        public async Task<PagedDto<OrderDto>> QueryOrderSourceDetailReport([FromQuery] QueryOrderSourceDetailRequest dto)
        {
            var (total, items) = await _orderApplication.QueryOrderSourceDetail(dto).ToPagedListAsync(dto, HttpContext.RequestAborted);
            return new PagedDto<OrderDto>(total, _mapper.Map<IReadOnlyList<OrderDto>>(items));
        }

        /// <summary>
        /// 信件来源统计明细导出
        /// </summary>
        /// <returns></returns>
        [HttpPost("order_source_detail/_export")]
        public async Task<FileStreamResult> QueryOrderSourceDetailReport([FromBody] ExportExcelDto<QueryOrderSourceDetailRequest> dto)
        {
            var query = _orderApplication.QueryOrderSourceDetail(dto.QueryDto);
            List<Order> orders;
            if (dto.IsExportAll)
            {
                orders = await query.ToListAsync(HttpContext.RequestAborted);
            }
            else
            {
                var (_, items) = await query.ToPagedListAsync(dto.QueryDto, HttpContext.RequestAborted);
                orders = items;
            }
            var ordersDtos = _mapper.Map<ICollection<OrderDto>>(orders);
            dynamic? dynamicClass = DynamicClassHelper.CreateDynamicClass(dto.ColumnInfos);
            var dtos = ordersDtos
                .Select(stu => _mapper.Map(stu, typeof(OrderDto), dynamicClass))
                .Cast<object>()
                .ToList();
            var stream = ExcelHelper.CreateStream(dtos);
            return ExcelStreamResult(stream, "信件来源统计明细数据");
        }

        /// <summary>
        /// 区域分时统计
        /// </summary>
        /// <param name="dto"></param>
        /// <returns></returns>
        [HttpGet("order_area_time")]
        public async Task<object> OrderAreaTime([FromQuery] TimeSharingPagedKeywordRequest dto)
        {
            var item = await _orderRepository.OrderAreaTime(dto);

            var titleData = await _systemAreaRepository.Queryable()
                .Where(p => p.Id.Length == 6 && p.Id != "510000")
                .Select(p => new
                {
                    Key = p.Id,
                    Value = p.AreaName
                }).ToListAsync();

            return new { Item = item, TitleData = titleData };
        }

        /// <summary>
        /// 区域分时统计-导出
        /// </summary>
        /// <param name="dto"></param>
        /// <returns></returns>
        [HttpPost("order_area_time_export")]
        public async Task<FileStreamResult> OrderAreaTimeExport([FromBody] TimeSharingPagedKeywordRequest dto)
        {
            var dataTable = await _orderRepository.OrderAreaTimeExport(dto);
            var stream = ExcelHelper.CreateStream(dataTable);
            return ExcelStreamResult(stream, "区域分时统计数据");
        }

        /// <summary>
        /// 热点类型分时统计
        /// </summary>
        /// <param name="dto"></param>
        /// <returns></returns>
        [HttpGet("order_hotspot_time")]
        public async Task<object> OrderHotspotTime([FromQuery] TimeSharingPagedKeywordRequest dto)
        {
            var item = await _orderRepository.OrderHotspotTime(dto);

            var titleData = await _hotspotRepository.Queryable()
                .Where(p => p.Id.Length == 2 && (p.ParentId == null || p.ParentId == ""))
                .Select(p => new
                {
                    Key = p.Id,
                    Value = p.HotSpotName
                }).ToListAsync();

            return new { Item = item, TitleData = titleData };
        }

        /// <summary>
        /// 热点类型分时统计-导出
        /// </summary>
        /// <param name="dto"></param>
        /// <returns></returns>
        [HttpPost("order_hotspot_time_export")]
        public async Task<FileStreamResult> OrderHotspotTimeExport([FromBody] TimeSharingPagedKeywordRequest dto)
        {
            var dataTable = await _orderRepository.OrderHotspotTimeExport(dto);
            var stream = ExcelHelper.CreateStream(dataTable);
            return ExcelStreamResult(stream, "热点类型分时统计数据");
        }

        /// <summary>
        /// 受理类型分时统计
        /// </summary>
        /// <param name="dto"></param>
        /// <returns></returns>
        [HttpGet("order_acceptance_time")]
        public async Task<object> OrderAcceptanceTime([FromQuery] TimeSharingPagedKeywordRequest dto)
        {
            var item = await _orderRepository.OrderAcceptanceTime(dto);

            var titleData = await _systemDicDataRepository.Queryable()
                .Where(p => p.DicTypeCode == "AcceptType")
                .Select(p => new
                {
                    Key = p.DicDataValue,
                    Value = p.DicDataName
                }).ToListAsync();

            return new { Item = item, TitleData = titleData };
        }

        /// <summary>
        /// 受理类型分时统计-导出
        /// </summary>
        /// <param name="dto"></param>
        /// <returns></returns>
        [HttpPost("order_acceptance_time_export")]
        public async Task<FileStreamResult> OrderAcceptanceTimeExport([FromBody] TimeSharingPagedKeywordRequest dto)
        {
            var dataTable = await _orderRepository.OrderAcceptanceTimeExport(dto);
            var stream = ExcelHelper.CreateStream(dataTable);
            return ExcelStreamResult(stream, "受理类型分时统计数据");
        }

        /// <summary>
        /// 信件来源分时统计
        /// </summary>
        /// <param name="dto"></param>
        /// <returns></returns>
        [HttpGet("order_source_time")]
        public async Task<object> OrderSourceTime([FromQuery] TimeSharingPagedKeywordRequest dto)
        {
            var item = await _orderRepository.OrderSourceTime(dto);

            var titleData = await _systemDicDataRepository.Queryable()
                .Where(p => p.DicTypeCode == "SourceChannel")
                .Select(p => new
                {
                    Key = p.DicDataValue,
                    Value = p.DicDataName
                }).ToListAsync();

            return new { Item = item, TitleData = titleData };
        }

        /// <summary>
        /// 信件来源分时统计-导出
        /// </summary>
        /// <param name="dto"></param>
        /// <returns></returns>
        [HttpPost("order_source_time_export")]
        public async Task<FileStreamResult> OrderSourceTimeExport([FromBody] TimeSharingPagedKeywordRequest dto)
        {
            var dataTable = await _orderRepository.OrderSourceTimeExport(dto);
            var stream = ExcelHelper.CreateStream(dataTable);
            return ExcelStreamResult(stream, "信件来源分时统计数据");
        }

        /// <summary>
        /// 部门满意度明细
        /// </summary>
        /// <param name="dto"></param>
        /// <returns></returns>
        [HttpGet("org-visitdetail-list")]
        public async Task<PagedDto<OrgVisitDetailListResp>> OrgVisitDetailList([FromQuery] OrgVisitDetailListReq dto)
        {
            var query = _orderReportApplication.OrgVisitDetailList(dto);
            var (total, items) = await query.ToPagedListAsync(dto.PageIndex, dto.PageSize, HttpContext.RequestAborted);

            return new PagedDto<OrgVisitDetailListResp>(total, _mapper.Map<IReadOnlyList<OrgVisitDetailListResp>>(items));
        }

        /// <summary>
        /// 部门满意度明细页面基础数据
        /// </summary>
        /// <returns></returns>
        [HttpGet("org-visitdetail-list-basedata")]
        public async Task<object> OrgVisitDetailListBaseData()
        {
            var VisitSatisfaction = _systemDicDataCacheManager.GetSysDicDataCache(SysDicTypeConsts.VisitSatisfaction);

            if (_sessionContext.OrgIsCenter)
            {
                return new { OrgsOptions = await _systemOrganizeRepository.GetOrgJson(), VisitSatisfaction = VisitSatisfaction };
            }
            else
            {
                return new { OrgsOptions = await _systemOrganizeRepository.GetOrgJsonForUser(_sessionContext.RequiredOrgId), VisitSatisfaction = VisitSatisfaction };
            }
        }

        /// <summary>
        /// 部门满意度明细导出
        /// </summary>
        /// <param name="dto"></param>
        /// <returns></returns>
        [HttpPost("org-visitdetail-list-export")]
        public async Task<FileStreamResult> OrgVisitDetailListExport([FromBody] ExportExcelDto<OrgVisitDetailListReq> dto)
        {
            var query = _orderReportApplication.OrgVisitDetailList(dto.QueryDto);

            List<OrderVisitDetail> orders;
            if (dto.IsExportAll)
            {
                orders = await query.ToListAsync(HttpContext.RequestAborted);
            }
            else
            {
                var (_, items) = await query.ToPagedListAsync(dto.QueryDto, HttpContext.RequestAborted);
                orders = items;
            }
            var ordersDtos = _mapper.Map<ICollection<OrgVisitDetailListResp>>(orders);
            dynamic? dynamicClass = DynamicClassHelper.CreateDynamicClass(dto.ColumnInfos);
            var dtos = ordersDtos
                .Select(stu => _mapper.Map(stu, typeof(OrgVisitDetailListResp), dynamicClass))
                .Cast<object>()
                .ToList();

            var stream = ExcelHelper.CreateStream(dtos);
            return ExcelStreamResult(stream, "部门满意度明细");
        }

        /// <summary>
        /// 热点区域统计
        /// </summary>
        /// <param name="dto"></param>
        /// <returns></returns>
        [HttpGet("hotspot-area-statistics")]
        public async Task<object> HotspotAndAreaStatistics([FromQuery] HotspotAndAreaStatisticsReq dto)
        {

            var (areaList, returnList) = await _orderApplication.HotspotAndAreaStatistics(dto);

            return new { AreaList = areaList, Data = returnList };
        }

        /// <summary>
        /// 热点区域统计导出
        /// </summary>
        /// <param name="dto"></param>
        /// <returns></returns>
        [HttpPost("hotspot-area-statistics-export")]
        public async Task<FileStreamResult> HotspotAndAreaStatisticsExport([FromBody] HotspotAndAreaStatisticsReq dto)
        {
            var returnList = await _orderApplication.HotspotAndAreaStatisticsExport(dto);
            var table = _orderApplication.InitHotspotTable(returnList, dto.AddColumnName, dto.HotspotLevel);
            var stream = ExcelHelper.CreateStream(table);
            return ExcelStreamResult(stream, "热点区域统计");
        }

        /// <summary>
        /// 热点区域明细
        /// </summary>
        /// <param name="dto"></param>
        /// <returns></returns>
        [HttpGet("hotspot-area-statistics-detail")]
        public async Task<PagedDto<HotspotAndAreaStatisticsDetailDto>> HotspotAndAreaStatisticsDetail([FromQuery] HotspotAndAreaStatisticsDetailReq dto)
        {
            var (total, items) = await _orderRepository.HotspotAndAreaStatisticsDetail(dto).ToPagedListAsync(dto.PageIndex, dto.PageSize, HttpContext.RequestAborted);
            return new PagedDto<HotspotAndAreaStatisticsDetailDto>(total, items);
        }

        /// <summary>
        /// 受理类型明细
        /// </summary>
        /// <param name="dto"></param>
        /// <returns></returns>
        [HttpGet("accepttype-statistics-bydate-detail")]
        public async Task<PagedDto<HotspotAndAreaStatisticsDetailDto>> AcceptTypeStatisticsByDateDetail([FromQuery] AcceptTypeStatisticsByDateDetailReq dto)
        {
            var (total, items) = await _orderRepository.AcceptTypeStatisticsByDateDetail(dto).ToPagedListAsync(dto.PageIndex, dto.PageSize, HttpContext.RequestAborted);
            return new PagedDto<HotspotAndAreaStatisticsDetailDto>(total, items);
        }

        /// <summary>
        /// 受理类型明细导出
        /// </summary>
        /// <param name="dto"></param>
        /// <returns></returns>
        [HttpPost("accepttype-statistics-bydate-detail-export")]
        public async Task<FileStreamResult> AcceptTypeStatisticsByDateDetailExport([FromBody] ExportExcelDto<AcceptTypeStatisticsByDateDetailReq> dto)
        {
            var query = _orderRepository.AcceptTypeStatisticsByDateDetail(dto.QueryDto);

            List<HotspotAndAreaStatisticsDetailDto> orders;
            if (dto.IsExportAll)
            {
                orders = await query.ToListAsync(HttpContext.RequestAborted);
            }
            else
            {
                var (_, items) = await query.ToPagedListAsync(dto.QueryDto, HttpContext.RequestAborted);
                orders = items;
            }
            var ordersDtos = _mapper.Map<ICollection<HotspotAndAreaStatisticsDetailDto>>(orders);
            dynamic? dynamicClass = DynamicClassHelper.CreateDynamicClass(dto.ColumnInfos);
            var dtos = ordersDtos
                .Select(stu => _mapper.Map(stu, typeof(HotspotAndAreaStatisticsDetailDto), dynamicClass))
                .Cast<object>()
                .ToList();

            var stream = ExcelHelper.CreateStream(dtos);
            return ExcelStreamResult(stream, "受理类型明细");
        }

        /// <summary>
        /// 热点区域明细导出
        /// </summary>
        /// <param name="dto"></param>
        /// <returns></returns>
        [HttpPost("hotspot-area-statistics-detail-export")]
        public async Task<FileStreamResult> HotspotAndAreaStatisticsDetailExport([FromBody] ExportExcelDto<HotspotAndAreaStatisticsDetailReq> dto)
        {
            var query = _orderRepository.HotspotAndAreaStatisticsDetail(dto.QueryDto);

            List<HotspotAndAreaStatisticsDetailDto> orders;
            if (dto.IsExportAll)
            {
                orders = await query.ToListAsync(HttpContext.RequestAborted);
            }
            else
            {
                var (_, items) = await query.ToPagedListAsync(dto.QueryDto, HttpContext.RequestAborted);
                orders = items;
            }
            var ordersDtos = _mapper.Map<ICollection<HotspotAndAreaStatisticsDetailDto>>(orders);
            dynamic? dynamicClass = DynamicClassHelper.CreateDynamicClass(dto.ColumnInfos);
            var dtos = ordersDtos
                .Select(stu => _mapper.Map(stu, typeof(HotspotAndAreaStatisticsDetailDto), dynamicClass))
                .Cast<object>()
                .ToList();

            var stream = ExcelHelper.CreateStream(dtos);
            return ExcelStreamResult(stream, "热点区域明细");
        }

        /// <summary>
        /// 受理类型统计
        /// </summary>
        /// <param name="dto"></param>
        /// <returns></returns>
        [HttpGet("accepttype-statistics")]
        public async Task<List<AcceptTypeStatisticsDto>> AcceptTypeStatistics([FromQuery] AcceptTypeStatisticsReq dto)
        {
            dto.EndTime = dto.EndTime.AddDays(1).AddSeconds(-1);
            var list = await _orderReportApplication.AcceptTypeStatistics(dto).ToListAsync();
            int SumCount = list.Sum(x => x.SumCount);
            list.Add(new AcceptTypeStatisticsDto()
            {
                AcceptType = "合计",
                SumCount = SumCount,
                CompletionCount = list.Sum(x => x.CompletionCount),
                VisitCount = list.Sum(x => x.VisitCount)
            });

            list.ForEach(x =>
            {
                x.SumCountRate = SumCount > 0 ? Math.Round((double)x.SumCount / (double)SumCount * 100, digits: 3) + "%" : 0.000 + "%";
            });

            return list;
        }

        /// <summary>
        /// 受理类型统计-导出
        /// </summary>
        /// <param name="dto"></param>
        /// <returns></returns>
        [HttpPost("accepttype-statistics-export")]
        public async Task<FileStreamResult> AcceptTypeStatisticsExport([FromBody] ExportExcelDto<AcceptTypeStatisticsReq> dto)
        {

            var list = await _orderReportApplication.AcceptTypeStatistics(dto.QueryDto).ToListAsync();
            int SumCount = list.Sum(x => x.SumCount);
            list.Add(new AcceptTypeStatisticsDto()
            {
                AcceptType = "合计",
                SumCount = SumCount,
                CompletionCount = list.Sum(x => x.CompletionCount),
                VisitCount = list.Sum(x => x.VisitCount)
            });

            list.ForEach(x =>
            {
                x.SumCountRate = SumCount > 0 ? Math.Round((double)x.SumCount / (double)SumCount * 100, digits: 3) + "%" : 0.000 + "%";
            });
            var orderDtos = _mapper.Map<ICollection<AcceptTypeStatisticsDto>>(list);
            dynamic? dynamicClass = DynamicClassHelper.CreateDynamicClass(dto.ColumnInfos);
            var dtos = orderDtos
                .Select(stu => _mapper.Map(stu, typeof(AcceptTypeStatisticsDto), dynamicClass))
                .Cast<object>()
                .ToList();
            var stream = ExcelHelper.CreateStream(dtos);
            return ExcelStreamResult(stream, "受理类型统计表数据");
        }

        /// <summary>
        /// 根据时间查询受理类型统计
        /// </summary>
        /// <param name="dto"></param>
        /// <returns></returns>
        [HttpGet("accepttype-statistics-bydate")]
        public async Task<object> AcceptTypeStatisticsByDate([FromQuery] AcceptTypeStatisticsByDateReq dto)
        {
            var (acceptList, items) = await _orderRepository.AcceptTypeStatisticsByDate(dto);

            return new { AcceptType = acceptList, Data = items };
        }

        /// <summary>
        /// 根据时间查询受理类型统计导出
        /// </summary>
        /// <param name="dto"></param>
        /// <returns></returns>
        [HttpPost("accepttype-statistics-bydate-export")]
        public async Task<FileStreamResult> AcceptTypeStatisticsByDateExport([FromBody] AcceptTypeStatisticsByDateReq dto)
        {
            var returnList = await _orderRepository.AcceptTypeStatisticsByDateExport(dto);
            var table = _orderRepository.InitAcceptTypeTable(returnList, dto.AddColumnName);
            var stream = ExcelHelper.CreateStream(table);
            return ExcelStreamResult(stream, "受理类型时间统计");
        }



        /// <summary>
        /// 下级区域统计
        /// </summary>
        /// <param name="StartTime">开始时间</param>
        /// <param name="EndTime">结束时间</param>
        /// <param name="TypeId">0：全部 ，1：市民，2：企业</param>
        /// <param name="AreaCode">上级区域Id</param>
        /// <param name="Line">线路号</param>
        /// <returns></returns>
        [HttpGet("area_subordinate")]
        public async Task<object> AreaSubordinate(DateTime StartTime, DateTime EndTime, int? TypeId, string? AreaCode, string? Line)
        {
            EndTime = EndTime.AddDays(1).AddSeconds(-1);

            var IsCenter = _sessionContext.OrgIsCenter;

            string count = "6";
            if (!string.IsNullOrEmpty(AreaCode) && AreaCode != "511500")
                count = (AreaCode.Length + 2).ToString();

            if (string.IsNullOrEmpty(AreaCode))
                AreaCode = "510000";

            var query = _orderRepository.Queryable()
                  .Where(p => p.CreationTime >= StartTime && p.CreationTime <= EndTime)
                    .WhereIF(TypeId != null && TypeId == 1, p => p.IdentityType == EIdentityType.Citizen)
                  .WhereIF(TypeId != null && TypeId == 2, p => p.IdentityType == EIdentityType.Enterprise)
                  .WhereIF(!string.IsNullOrEmpty(Line), p => p.TransferPhone == Line)
                  .WhereIF(IsCenter == false, p => p.ActualHandleOrgCode.StartsWith(_sessionContext.RequiredOrgId))
                   .GroupBy(p => new { Id = p.AreaCode.Substring(SqlFunc.MappingColumn<int>("0"), SqlFunc.MappingColumn<int>(count)) })
                  .Select(p => new
                  {
                      AreaCode = p.AreaCode.Substring(SqlFunc.MappingColumn<int>("0"), SqlFunc.MappingColumn<int>(count)),
                      SumCount = SqlFunc.AggregateCount(p.AreaCode)
                  })
                  .MergeTable();

            var list = await _systemAreaRepository.Queryable()
                  .LeftJoin(query, (s, o) => s.Id == o.AreaCode)
                  .Where((s, o) => s.ParentId == AreaCode)
                  .Select((s, o) => new
                  {
                      AreaCode = s.Id,
                      SumCount = o.SumCount,
                      AreaName = s.AreaName,
                      HasChild = SqlFunc.Subqueryable<SystemArea>().Where(d => d.ParentId == o.AreaCode).Any()
                  })
                .ToListAsync();

            return list;



        }
    }
}