hotline/src/Hotline.Application/Orders/OrderApplication.cs

using DotNetCore.CAP;
using Hotline.Application.Quality;
using Hotline.Caching.Interfaces;
using Hotline.File;
using Hotline.FlowEngine.Workflows;
using Hotline.Orders;
using Hotline.Repository.SqlSugar.Extensions;
using Hotline.Repository.SqlSugar.Ts;
using Hotline.Settings.TimeLimits;
using Hotline.Share.Dtos;
using Hotline.Share.Dtos.DataSharing.PusherHotlineDto;
using Hotline.Share.Dtos.File;
using Hotline.Share.Dtos.FlowEngine;
using Hotline.Share.Dtos.FlowEngine.Workflow;
using Hotline.Share.Dtos.Order;
using Hotline.Share.Dtos.Settings;
using Hotline.Share.Enums.Order;
using Hotline.Share.Enums.Quality;
using Hotline.Share.Enums.Settings;
using Hotline.Share.Requests;
using Hotline.Tools;
using MapsterMapper;
using Microsoft.AspNetCore.Mvc.Formatters;
using Microsoft.EntityFrameworkCore.Metadata.Internal;
using Novacode;
using SqlSugar;
using System.Data;
using System.Dynamic;
using Hotline.Settings;
using XF.Domain.Authentications;
using XF.Domain.Constants;
using XF.Domain.Dependency;
using XF.Domain.Exceptions;
using XF.Domain.Repository;
using Hotline.Repository.SqlSugar.System;
using Microsoft.AspNetCore.Mvc;
using Hotline.Share.Dtos.Bi;
using System.Net;
using PanGu;
using Hotline.Users;
using PanGu.Match;
using Hotline.Settings.Hotspots;
using Hotline.Share.Enums.FlowEngine;
using Hotline.Authentications;
using Hotline.Repository.SqlSugar;
using MediatR;
using Hotline.Share.Mq;
using JiebaNet.Segmenter;
using Microsoft.AspNetCore.Http;
using WordInfo = PanGu.WordInfo;
using Hotline.Schedulings;
using NPOI.SS.Formula.Functions;
using XF.Domain.Entities;
using Hotline.Settings.TimeLimitDomain;
using Hotline.FlowEngine.WorkflowModules;
using Hotline.SeedData;
using Hotline.Share.Enums.Push;
using Hotline.Push.Notifies;

namespace Hotline.Application.Orders;

public class OrderApplication : IOrderApplication, IScopeDependency
{

    private readonly IMediator _mediator;
    private readonly IOrderDomainService _orderDomainService;
    private readonly IWorkflowDomainService _workflowDomainService;
    private readonly IOrderRepository _orderRepository;
    //private readonly ITimeLimitDomainService _timeLimitDomainService;
    private readonly IMapper _mapper;
    private readonly ISystemSettingCacheManager _systemSettingCacheManager;
    private readonly IRepository<OrderWord> _orderWrodRepository;
    private readonly IRepositoryTextSearch<OrderTs> _repositoryts;
    private readonly IFileRepository _fileRepository;
    private readonly ISessionContext _sessionContext;
    private readonly IRepository<OrderVisit> _orderVisitRepository;
    private readonly IRepository<OrderVisitDetail> _orderVisitDetailRepository;
    private readonly IQualityApplication _qualityApplication;
    private readonly ICapPublisher _capPublisher;
    private readonly IRepository<SystemOrganize> _systemOrganizeRepository;
    private readonly IRepository<WorkflowCountersign> _workflowCountersignRepository;
    private readonly IRepository<OrderSpecialDetail> _orderSpecialDetailRepository;
    private readonly IRepository<SystemArea> _systemAreaRepository;
    private readonly IRepository<Hotspot> _hotspotRepository;
    private readonly IRepository<WorkflowStep> _workflowStepRepository;
    private readonly IRepository<WorkflowTrace> _workflowTraceRepository;
    private readonly IRepository<SystemDicData> _systemDicDataRepository;
    private readonly IRepository<OrderPublish> _orderPublishRepository;
    private readonly IRepository<OrderScreen> _orderScreenRepository;
    private readonly IRepository<OrderSendBackAudit> _orderSendBackAuditRepository;
    private readonly ICalcExpireTime _expireTime;


    public OrderApplication(
        IOrderDomainService orderDomainService,
        IOrderRepository orderRepository,
        IWorkflowDomainService workflowDomainService,
        //ITimeLimitDomainService timeLimitDomainService,
        ISystemSettingCacheManager systemSettingCacheManager,
        IMapper mapper,
        IRepository<OrderWord> orderWrodRepository,
        IRepositoryTextSearch<OrderTs> repositoryts,
        IFileRepository fileRepository,
        ISessionContext sessionContext,
        IRepository<OrderVisit> orderVisitRepository,
        IRepository<OrderVisitDetail> orderVisitDetailRepository,
        IQualityApplication qualityApplication,
        ICapPublisher capPublisher,
        IRepository<SystemOrganize> systemOrganizeRepository,
        IRepository<WorkflowCountersign> workflowCountersignRepository,
        IRepository<OrderSpecialDetail> orderSpecialDetailRepository,
        IRepository<SystemArea> systemAreaRepository,
        IRepository<Hotspot> hotspotRepository,
        IRepository<WorkflowStep> workflowStepRepository,
        IRepository<SystemDicData> systemDicDataRepository,
        IRepository<WorkflowTrace> workflowTraceRepository,
        IRepository<OrderPublish> orderPublishRepository,
        IRepository<OrderScreen> orderScreenRepository,
        IRepository<OrderSendBackAudit> orderSendBackAuditRepository,
        ICalcExpireTime expireTime,
        IMediator mediator)
    {
        _orderDomainService = orderDomainService;
        _workflowDomainService = workflowDomainService;
        _orderRepository = orderRepository;
        //_timeLimitDomainService = timeLimitDomainService;
        _mapper = mapper;
        _systemSettingCacheManager = systemSettingCacheManager;
        _orderWrodRepository = orderWrodRepository;
        _repositoryts = repositoryts;
        _fileRepository = fileRepository;
        _sessionContext = sessionContext;
        _orderVisitRepository = orderVisitRepository;
        _orderVisitDetailRepository = orderVisitDetailRepository;
        _qualityApplication = qualityApplication;
        _capPublisher = capPublisher;
        _systemOrganizeRepository = systemOrganizeRepository;
        _workflowCountersignRepository = workflowCountersignRepository;
        _orderSpecialDetailRepository = orderSpecialDetailRepository;
        _systemAreaRepository = systemAreaRepository;
        _hotspotRepository = hotspotRepository;
        _workflowStepRepository = workflowStepRepository;
        _systemDicDataRepository = systemDicDataRepository;
        _workflowTraceRepository = workflowTraceRepository;
        _orderScreenRepository = orderScreenRepository;
        _orderPublishRepository = orderPublishRepository;
        _orderSendBackAuditRepository = orderSendBackAuditRepository;
        _expireTime = expireTime;
        _mediator = mediator;
    }

    /// <summary>
    /// 更新工单办理期满时间(延期调用，其他不调用)
    /// 1.更新工单 2.更新流程 3.推送省平台
    /// </summary>
    /// <returns></returns>
    public async Task DelayOrderExpiredTimeAsync(string orderId, int timeCount, ETimeType timeType, bool IsProDelay, CancellationToken cancellationToken)
    {
        var order = await _orderDomainService.GetOrderAsync(orderId, cancellationToken: cancellationToken);
        var expiredTimeConfig =
             // _timeLimitDomainService.CalcEndTime(DateTime.Now, new TimeConfig(timeCount, timeType), order.AcceptTypeCode);
             await _expireTime.CalcEndTime(DateTime.Now, new TimeConfig(timeCount, timeType), order.AcceptTypeCode);
        order.TimeLimit = expiredTimeConfig.TimeText;
        order.TimeLimitCount = expiredTimeConfig.Count;
        order.TimeLimitUnit = expiredTimeConfig.TimeType;
        order.ExpiredTime = expiredTimeConfig.ExpiredTime;
        order.NearlyExpiredTime = expiredTimeConfig.NearlyExpiredTime;
        //TODO发送短信即将超期
        //_capPublisher.PublishDelay(expiredTimeConfig.NearlyExpiredTime - DateTime.Now, EventNames.HotlineOrderNearlyExpiredTimeSms, new PublishNearlyExpiredTimeSmsDto() { OrderId = order.Id });
        if (IsProDelay)
        {
            order.ExpiredTimeProvince = expiredTimeConfig.ExpiredTime;
        }
        //if (string.IsNullOrEmpty(order.WorkflowId))
        //    throw new UserFriendlyException("该工单流程id异常");
        //var workflow = await _workflowDomainService.GetWorkflowAsync(order.WorkflowId, cancellationToken: cancellationToken);
        //await _workflowDomainService.UpdateExpiredTimeAsync(workflow, expiredTimeConfig.ExpiredTime,
        //    expiredTimeConfig.TimeText, expiredTimeConfig.Count, expiredTimeConfig.TimeType, expiredTimeConfig.NearlyExpiredTime, cancellationToken);

        if (string.IsNullOrEmpty(order.WorkflowId))
            throw new UserFriendlyException("该工单流程id异常");
        await _workflowDomainService.UpdateUnhandleExpiredTimeAsync(order.WorkflowId, expiredTimeConfig.ExpiredTime, cancellationToken);

        await _orderRepository.UpdateAsync(order, cancellationToken);
    }

    /// <summary>
    /// 新增工单办理流程记录
    /// </summary>
    public async Task AddOrderTracesAsync(string orderId, ICollection<WorkflowTraceDto> traces, CancellationToken cancellationToken)
    {
        var order = await _orderRepository.GetAsync(orderId, cancellationToken);
        if (order is null)
            throw new UserFriendlyException("工单不存在");
        if (string.IsNullOrEmpty(order.WorkflowId))
            throw new UserFriendlyException("工单未开启流程");
        await _workflowDomainService.AddTracesAsync(order.WorkflowId, _mapper.Map<List<WorkflowTrace>>(traces),
            cancellationToken);
    }

    /// <summary>
    /// 撤销工单
    /// </summary>
    public async Task CancelOrderAsync(string orderId, string opinion, CancellationToken cancellationToken)
    {
        var order = await _orderRepository.GetAsync(orderId, cancellationToken);
        if (order is null)
            throw new UserFriendlyException("工单不存在");
        if (!string.IsNullOrEmpty(order.WorkflowId))
        {
            //结束流程
            await _workflowDomainService.TerminateAsync(new TerminateDto
            {
                WorkflowId = order.WorkflowId,
                Opinion = opinion
            }, cancellationToken);
        }

        //归档工单
        var now = DateTime.Now;
        var handleDuration = order.StartTime.HasValue
            ? //_timeLimitDomainService.CalcWorkTime(
            await _expireTime.CalcWorkTime(
                order.StartTime.Value,
                now, order.ProcessType is EProcessType.Zhiban)
            : 0;
        var fileDuration = order.CenterToOrgTime.HasValue
            ? // _timeLimitDomainService.CalcWorkTime(
             await _expireTime.CalcWorkTime(
                order.CenterToOrgTime.Value,
                now, order.ProcessType is EProcessType.Zhiban)
            : 0;
        var allDuration = order.StartTime.HasValue
            ?
            //_timeLimitDomainService.CalcWorkTime(
            await _expireTime.CalcWorkTime(
                order.StartTime.Value, now,
                order.ProcessType is EProcessType.Zhiban)
            : 0;
        var creationTimeHandleDurationWorkday = order.ActualHandleTime.HasValue
                    ? 
                    // _timeLimitDomainService.CalcWorkTime(
                    await _expireTime.CalcWorkTime(
                        order.CreationTime, now,
                    order.ProcessType is EProcessType.Zhiban)
                    : 0;
        var centerToOrgHandleDurationWorkday = order.ActualHandleTime.HasValue && order.CenterToOrgTime.HasValue
            ? 
            // _timeLimitDomainService.CalcWorkTime(
            await _expireTime.CalcWorkTime(
                order.CenterToOrgTime.Value, now,
            order.ProcessType is EProcessType.Zhiban)
            : 0;

        order.File(now, handleDuration, fileDuration, allDuration, creationTimeHandleDurationWorkday, centerToOrgHandleDurationWorkday);
        await _orderRepository.UpdateAsync(order, cancellationToken);
    }

    /// <summary>
    /// 即将超期列表
    /// </summary>
    /// <param name="dto"></param>
    /// <param name="cancellationToken"></param>
    /// <returns></returns>
    public ISugarQueryable<Order> GetAboutToExpireAsync(AboutToExpireListDto dto)
    {
        //var setting = _systemSettingCacheManager.GetSetting(SettingConstants.OrderAboutToExpire);
        //var value = setting?.SettingValue[0];
        //value = string.IsNullOrEmpty(value) ? "0" : value;
        //DateTime stTime = DateTime.Now.AddDays(int.Parse(value));
        //stTime = _timeLimitDomainService.WorkDay(stTime);
        //DateTime stTime2 = _timeLimitDomainService.WorkDay(DateTime.Now);
        DateTime? dateTime = DateTime.Now;
        var IsCenter = _sessionContext.OrgIsCenter;

        return _orderRepository.Queryable(canView: !IsCenter).Includes(d => d.OrderDelays)
            .WhereIF(dto.IsProvince.HasValue, d => d.IsProvince == dto.IsProvince)
            .WhereIF(!string.IsNullOrEmpty(dto.No), d => d.No.Contains(dto.No!))
            .WhereIF(!string.IsNullOrEmpty(dto.Title), d => d.Title.Contains(dto.Title!))
            .WhereIF(dto.Delay.HasValue && dto.Delay == 1, d => d.OrderDelays.Any() == true)
            .WhereIF(dto.Delay.HasValue && dto.Delay == 2, d => d.OrderDelays.Any() == false)
            //&& stTime >= d.ExpiredTime.Value && stTime2 <= d.ExpiredTime.Value
            //.Where(d => d.ExpiredTime != null &&
            //         d.Status != EOrderStatus.Filed && d.Status != EOrderStatus.Published && d.Status != EOrderStatus.Visited && stTime >= d.ExpiredTime.Value && stTime2 <= d.ExpiredTime.Value)
            .Where(d => d.Status < EOrderStatus.Filed && dateTime > d.NearlyExpiredTime && dateTime < d.ExpiredTime)
            .OrderByDescending(d => d.CreationTime);
    }

    // /// <summary>
    // /// 即将超期节点列表
    // /// </summary>
    // /// <param name="dto"></param>
    // /// <param name="cancellationToken"></param>
    // /// <returns></returns>
    //public async Task<PagedDto<WorkflowOrderDto>> GetAboutToExpireNodeAsync(AboutToExpireListDto dto, CancellationToken cancellationToken)
    //{
    //	var setting = _systemSettingCacheManager.GetSetting(SettingConstants.OrderAboutToExpire);
    //	var value = setting?.SettingValue[0];
    //	value = string.IsNullOrEmpty(value) ? "0" : value;
    //	DateTime stTime = DateTime.Now.AddDays(int.Parse(value));
    //	stTime = _timeLimitDomainService.WorkDay(DateTime.Now);
    //	DateTime stTime2 = _timeLimitDomainService.WorkDay(DateTime.Now);
    //	RefAsync<int> total = 0;
    //	var items = await Db.Queryable<Workflow>()
    //		.LeftJoin<Order>((x, o) => x.ExternalId == o.Id)
    //		.Where(x => x.ModuleCode == "OrderHandle")
    //		.WhereIF(dto.IsProvince.HasValue, (x, o) => o.IsProvince == dto.IsProvince)
    //		.WhereIF(!string.IsNullOrEmpty(dto.Keyword), (x, o) => o.Title.Contains(dto.Keyword!) || o.No.Contains(dto.Keyword!))
    //		.Where((x, o) => (int)x.Status < 20 && stTime >= x.ExpiredTime && stTime2 <= x.ExpiredTime)
    //		.Select((x, o) => new WorkflowOrder { Order = o }, true)
    //		.OrderByDescending(x => x.CreationTime)
    //		.ToPageListAsync(dto.PageIndex, dto.PageSize, total, cancellationToken);
    //	return new PagedDto<WorkflowOrderDto>(total, _mapper.Map<IReadOnlyList<WorkflowOrderDto>>(items));
    //}

    /// <summary>
    /// 已超期列表
    /// </summary>
    /// <param name="dto"></param>
    /// <param name="cancellationToken"></param>
    /// <returns></returns>
    public ISugarQueryable<Order> GetToExpireAsync(AboutToExpireListDto dto)
    {
        // DateTime stTime = _timeLimitDomainService.WorkDay(DateTime.Now);
        DateTime stTime = _expireTime.WorkDay(DateTime.Now).GetAwaiter().GetResult();
        var IsCenter = _sessionContext.OrgIsCenter;

        return _orderRepository.Queryable(canView: false).Includes(d => d.OrderDelays)
            .Where(d => SqlFunc.Subqueryable<WorkflowStep>()
                    .Where(step => step.ExternalId == d.Id &&
                                   ((step.FlowAssignType == EFlowAssignType.User && !string.IsNullOrEmpty(step.HandlerId) && step.HandlerId == _sessionContext.RequiredUserId) ||
                                    (step.FlowAssignType == EFlowAssignType.Org && !string.IsNullOrEmpty(step.HandlerOrgId) && step.HandlerOrgId == _sessionContext.RequiredOrgId) ||
                                    (step.FlowAssignType == EFlowAssignType.Role && !string.IsNullOrEmpty(step.RoleId) && _sessionContext.Roles.Contains(step.RoleId))))
                    .Any())
            .WhereIF(dto.IsProvince.HasValue, d => d.IsProvince == dto.IsProvince)
            //.WhereIF(!string.IsNullOrEmpty(dto.Keyword), d => d.Title.Contains(dto.Keyword!) || d.No.Contains(dto.Keyword!))
            .WhereIF(!string.IsNullOrEmpty(dto.No), x => x.No.Contains(dto.No))
            .WhereIF(!string.IsNullOrEmpty(dto.Title), x => x.Title.Contains(dto.Title!))
            .WhereIF(dto.Delay.HasValue && dto.Delay == 1, d => d.OrderDelays.Any() == true)
            .WhereIF(dto.Delay.HasValue && dto.Delay == 2, d => d.OrderDelays.Any() == false)
            .Where(d => d.ExpiredTime != null &&
            (((d.Status == EOrderStatus.Filed || d.Status == EOrderStatus.Published || d.Status == EOrderStatus.Visited) && d.FiledTime >= d.ExpiredTime) ||
            ((d.Status != EOrderStatus.Filed && d.Status != EOrderStatus.Published && d.Status != EOrderStatus.Visited) && stTime >= d.ExpiredTime.Value)))
            .OrderByDescending(x => x.CreationTime);
    }

    //     /// <summary>
    //     /// 已超期节点列表
    //     /// </summary>
    //     /// <param name="dto"></param>
    //     /// <param name="cancellationToken"></param>
    //     /// <returns></returns>
    //     public async Task<PagedDto<WorkflowOrderDto>> GetToExpireNodeAsync(AboutToExpireListDto dto, CancellationToken cancellationToken)
    //     {
    //      DateTime stTime = _timeLimitDomainService.WorkDay(DateTime.Now);
    //RefAsync<int> total = 0;
    //var  items= await Db.Queryable<Workflow>()
    //             .LeftJoin<Order>((x,o)=>x.ExternalId == o.Id)
    //             .Where(x => x.ModuleCode == "OrderHandle")
    //             .WhereIF(dto.IsProvince.HasValue, (x, o) => o.IsProvince == dto.IsProvince)
    //             .WhereIF(!string.IsNullOrEmpty(dto.Keyword), (x, o) => o.Title.Contains(dto.Keyword!) || o.No.Contains(dto.Keyword!))
    //             .Where((x,o) => (((int)x.Status >= 20 && x.EndTime >= x.ExpiredTime) || ((int)x.Status < 20 && stTime >= x.ExpiredTime)))
    //             .Select((x, o) => new WorkflowOrder { Order = o }, true)
    //             .OrderByDescending(x => x.CreationTime)
    //             .ToPageListAsync(dto.PageIndex, dto.PageSize, total, cancellationToken);
    //      return new PagedDto<WorkflowOrderDto>(total, _mapper.Map<IReadOnlyList<WorkflowOrderDto>>(items));
    //     }

    /// <summary>
    /// 工单关键字分词
    /// </summary>
    /// <param name="inputStr"></param>
    /// <returns></returns>
    public async Task OrderParticiple(string inputStr, string orderId, DateTime time, CancellationToken cancellationToken)
    {
        var seg = new Segment();
        ICollection<WordInfo> splitWords = seg.DoSegment(inputStr);
        var words = new List<string>();
        for (int i = 0;i < splitWords.Count;i++)
        {
            var word = splitWords.ElementAt(i);
            if (word is { WordType: WordType.SimplifiedChinese, Word.Length: > 1 })
                words.Add(splitWords.ElementAt(i).Word);
        }
        if (words.Any())
        {
            var vector = await _repositoryts.SearchAsync(orderId, cancellationToken);
            if (vector != null && vector.Any()) await _repositoryts.UpdateVectorAsync(orderId, words, cancellationToken);
            else await _repositoryts.AddVectorAsync(orderId, time, words, cancellationToken);
        }
        // 结巴分词
        //var segmenter = new JiebaSegmenter();
        //var segments1 = segmenter.Cut(inputStr, cutAll: true);
        //var b = segments1;


        //var words = await _orderWrodRepository.Queryable().Where(x => x.IsEnable == 1 && x.Classify.Contains("普通标签")).Select(x => x.Tag).ToListAsync(cancellationToken);
        //var res = new List<string>();
        //if (words.Any()) res = ParticipleTool.SegMMDouble(inputStr, ref words);
        //var participles = await _orderWrodRepository.Queryable().In(x => x.Tag, res).ToListAsync(cancellationToken);
        //if (participles.Any())
        //{
        //    //关键词
        //    var tags = participles.Select(x => x.Tag).ToList();
        //    var tagsStr = string.Join(",", tags);
        //    await _orderRepository.Updateable().SetColumns(x => x.TagNames == tagsStr).Where(x => x.Id == orderId).ExecuteCommandAsync(cancellationToken);
        //    List<string> synonyms = participles.Select(x => x.Synonym).ToList();
        //    if (synonyms.Any())
        //    {
        //        var synonymsStr = string.Join(",", synonyms);
        //        synonyms = synonymsStr.Split(",").Distinct().ToList();
        //        tags.AddRange(synonyms);
        //    }
        //    var vector = await _orderRepository.Queryable().Where(x => x.Id == orderId).ToListAsync(cancellationToken);
        //    if (vector.Any()) await _repositoryts.UpdateVectorAsync(orderId, tags, cancellationToken);
        //    else await _repositoryts.AddVectorAsync(orderId, DateTime.Now, tags, cancellationToken);
        //}
    }

    public async Task OrderSensitiveParticiple(string inputStr, string orderId, CancellationToken cancellationToken)
    {
        var words = await _orderWrodRepository.Queryable().Where(x => x.IsEnable == 1 && x.Classify.Contains("敏感标签")).Select(x => x.Tag).ToListAsync(cancellationToken);
        var res = new List<string>();
        if (words.Any()) res = ParticipleTool.SegMMDouble(inputStr, ref words);
        if (res.Any())
        {
            var intersect = words.Intersect(res).ToList();
            await _orderRepository.Updateable().SetColumns(o => new Order() { Sensitive = intersect }).Where(o => o.Id == orderId).ExecuteCommandAsync(cancellationToken);
        }
    }

    /// <summary>
    /// 接收外部平台工单
    /// </summary>
    public Task<AddOrderResponse> ReceiveOrderFromExternalAsync(AddOrderDto dto, ISessionContext current, CancellationToken cancellationToken)
    {

        switch (dto.Source)
        {
            case ESource.ProvinceStraight:
                return ReceiveOrderFromProvinceAsync(dto, dto.Files, current, cancellationToken);
            case ESource.Police110:
            case ESource.CityDataExchangeLz:
            case ESource.CityDataExchangeYB:
            case ESource.CityDataExchangeZG:
            case ESource.CityDataExchangeNJ:
            case ESource.WebPortal:
            case ESource.ConvergenceMedia:
            case ESource.IYIBIN:
            case ESource.ZHYB:
            case ESource.ZZPT:
            case ESource.WLLZ:
                return ReceiveOrderFromOtherPlatformAsync(dto, dto.Files, current, cancellationToken);
            case ESource.Hotline:
            case ESource.HotlineImport:
            default:
                throw new ArgumentOutOfRangeException();
        }
    }

    /// <summary>
    /// 接收外部平台修改工单附件
    /// </summary>
    public async Task UpdateOrderFilesAnonymousAsync(UpdateOrderFilesDto dto, CancellationToken cancellationToken)
    {
        if (string.IsNullOrEmpty(dto.Id) && string.IsNullOrEmpty(dto.OrderNo))
            throw new UserFriendlyException("工单外部编号不能为空");

        var order = await _orderRepository.Queryable()
              .FirstAsync(d => d.Id == dto.Id || d.No == dto.OrderNo, cancellationToken);
        if (order != null && dto.Files != null && dto.Files.Any())
        {
            order.FileJson = await _fileRepository.AddFileAsync(dto.Files, order.Id, "", cancellationToken);
            await _orderRepository.UpdateAsync(order, cancellationToken);
        }
    }

    /// <summary>
    /// 发布量统计(按账号)
    /// </summary>
    /// <param name="dto"></param>
    /// <returns></returns>
    /// <exception cref="UserFriendlyException"></exception>
    public async Task<(int, IList<PublishedOrderStatisticsDto>)> QueryPublishedOrderAsync(QueryOrderPublishStatisticsDto dto, bool isFull)
    {
        dto.EndTime = dto.EndTime.AddDays(1).AddSeconds(-1);

        var query = _orderRepository.Queryable()
            .Includes(order => order.OrderPublish)
            .LeftJoin<User>((order, user) => order.WaitForPublisherId == user.Id)
            .Where((order, user) => order.CreationTime >= dto.StartTime && order.CreationTime <= dto.EndTime && !string.IsNullOrEmpty(order.WaitForPublisherId))
            .WhereIF(dto.ProcessType != null, (order, user) => order.ProcessType == dto.ProcessType)
            .GroupBy((order, user) => new { order.WaitForPublisherId, user.Name })
            .Select((order, user) => new PublishedOrderStatisticsDto
            {
                Id = order.WaitForPublisherId,
                Name = user.Name,
                //TotalCount = SqlFunc.AggregateCount(order.Id),
                WaitCount =    SqlFunc.AggregateSum(SqlFunc.IIF(order.Status == EOrderStatus.Filed, 1, 0)),
                PublicCount =  SqlFunc.AggregateSum(SqlFunc.IIF(order.OrderPublish.PublishState == true , 1, 0)),
                PrivateCount = SqlFunc.AggregateSum(SqlFunc.IIF(order.OrderPublish.PublishState == false, 1, 0))
            });

        var total = 0;
        var items = new List<PublishedOrderStatisticsDto>();
        if (isFull)
        {
            items = await query.ToListAsync();
        }
        else
        {
            (total, items) = await query.ToPagedListAsync(dto.PageIndex, dto.PageSize);
        }

        return (total, items);
    }

    /// <summary>
    /// 发布量统计(部门)
    /// </summary>
    /// <param name="dto"></param>
    /// <returns></returns>
    /// <exception cref="UserFriendlyException"></exception>
    public async Task<(int, IList<PublishedOrderStatisticsDto>)> QueryPublishedOrderDepartmentAsync(QueryOrderPublishStatisticsAllDto dto, bool isFull)
    {
        dto.EndTime = dto.EndTime.AddDays(1).AddSeconds(-1);

        var total = 0;
        var items = new List<PublishedOrderStatisticsDto>();

        var query = _orderRepository.Queryable()
           .Includes(order => order.OrderPublish)
           .Where(order => order.CreationTime >= dto.StartTime && order.CreationTime <= dto.EndTime && order.ActualHandleOrgName != null)
           .GroupBy(order => new { Name = order.ActualHandleOrgName, PublishTime = order.CreationTime.ToString("YYYY-MM-DD") })
           .Select(order => new PublishedOrderStatisticsDto
           {
               Name = order.ActualHandleOrgName,
               PublishTime = order.CreationTime.ToString("YYYY-MM-DD"),
               // TotalCount = SqlFunc.AggregateCount(order.Id),
               WaitCount = SqlFunc.AggregateSum(SqlFunc.IIF(order.Status == EOrderStatus.Filed, 1, 0)),
               PublicCount = SqlFunc.AggregateSum(SqlFunc.IIF(order.OrderPublish.PublishState == true, 1, 0)),
               PrivateCount = SqlFunc.AggregateSum(SqlFunc.IIF(order.OrderPublish.PublishState == false, 1, 0))
           });
        if (isFull)
        {
            items = await query.ToListAsync();
        }
        else
        {
            (total, items) = await query.ToPagedListAsync(dto.PageIndex, dto.PageSize);
        }

        return (total, items);
    }

    /// <summary>
    /// 回访来源统计
    /// </summary>
    /// <param name="dto"></param>
    /// <returns></returns>
    /// <exception cref="NotImplementedException"></exception>
    public async Task<IList<OrderVisitSourceChannelDto>> QueryOrderVisitSourceChannelAsync(QueryOrderVisitSourceChannelDto dto)
    {
        dto.EndTime = dto.EndTime.AddDays(1).AddSeconds(-1);
        var result = await _orderVisitRepository.Queryable()
            .LeftJoin<Order>((visit, order) => order.Id == visit.OrderId)
            .Where((visit, order) => visit.VisitTime >= dto.StartTime && visit.VisitTime <= dto.EndTime)
            .Where((visit, order) => visit.VisitState == EVisitState.Visited)
            .GroupBy((visit, order) => new { order.SourceChannel })
            .Select((visit, order) => new OrderVisitSourceChannelDto
            {
                Count = SqlFunc.AggregateCount(visit.OrderId),
                SourceChannel = order.SourceChannel
            })
            .ToListAsync();
        return result;
    }

    /// <summary>
    /// 工单回访
    /// </summary>
    /// <param name="dto"></param>
    /// <param name="cancellationToken"></param>
    /// <returns></returns>
    public async Task OrderVisitWeb(OrderVisitWebDto dto, CancellationToken cancellationToken)
    {
        var visit = await _orderVisitRepository.Queryable()
            .Includes(x => x.Order)
            .Includes(x => x.OrderVisitDetails)
            .FirstAsync(x => x.Id == dto.Id);
        if (visit != null)
        {
            var first = dto.OrderVisitDetailDto.FirstOrDefault(x => x.VisitTarget == EVisitTarget.Org);

            if (first != null)
            {
                visit.NowEvaluate = first.OrgProcessingResults;
                visit.Order.Visited(first.OrgProcessingResults.Key, first.OrgProcessingResults.Value);
            }
            visit.VisitState = EVisitState.Visited;
            visit.VisitTime = dto.VisitTime;
            visit.VisitType = dto.VisitType;

            for (int i = 0;i < visit.OrderVisitDetails.Count;i++)
            {
                var detail = visit.OrderVisitDetails[i];
                var detaildto = dto.OrderVisitDetailDto.FirstOrDefault(x => x.Id == detail.Id);
                if (detaildto != null)
                {
                    _mapper.Map(detaildto, visit.OrderVisitDetails[i]);
                }
            }

            await _orderVisitRepository.UpdateAsync(visit, cancellationToken);
            await _orderVisitDetailRepository.UpdateRangeAsync(visit.OrderVisitDetails, cancellationToken);
            await _orderRepository.UpdateAsync(visit.Order, cancellationToken);
            var orderDto = _mapper.Map<OrderDto>(visit.Order);
            if (first != null)
            {
                //推省上
                await _capPublisher.PublishAsync(Hotline.Share.Mq.EventNames.HotlineOrderVisited,
                        new PublishVisitDto()
                        {
                            Order = orderDto,
                            No = visit.No,
                            VisitType = visit.VisitType,
                            VisitName = visit.CreatorName,
                            VisitTime = visit.VisitTime,
                            VisitRemark = string.IsNullOrEmpty(first.VisitContent) ? first.OrgProcessingResults?.Value : first.VisitContent,
                            AreaCode = visit.Order.AreaCode!,
                            SubjectResultSatifyCode = first.OrgProcessingResults.Key,
                            FirstSatisfactionCode = visit.Order.FirstVisitResultCode!,
                            ClientGuid = ""
                        }, cancellationToken: cancellationToken);
            }
            //写入质检
            await _qualityApplication.AddQualityAsync(EQualitySource.Visit, orderDto.Id, visit.Id,
                cancellationToken);
        }
    }


    /// <summary>
    /// 发送回访短信
    /// </summary>
    /// <param name="dto"></param>
    /// <returns></returns>
    /// <exception cref="NotImplementedException"></exception>
    public async Task VisitPushSMSAsync(VisitSmsInDto dto, CancellationToken cancellationToken)
    {
        var orderVisitList = await _orderVisitRepository.Queryable()
            .Includes(d => d.Order)
            .Where(d => dto.Ids.Contains(d.Id) && d.VisitState == EVisitState.WaitForVisit)
            .Select(d => new { d.Id, d.Order.SourceChannelCode,  d.Order.Contact, d.Order.Password, d.No, d.OrderId, d.Order.Title, d.Order.FromName })
            .ToListAsync(cancellationToken);

        foreach (var item in orderVisitList)
        {
            var code = "1013";
            if (item.SourceChannelCode == "ZGSSP") code = "1012";
            var messageDto = new Share.Dtos.Push.MessageDto
            {
                PushBusiness = EPushBusiness.VisitSms,
                ExternalId = item.Id,
                OrderId = item.OrderId,
                PushPlatform = EPushPlatform.Sms,
                Remark = item.Title,
                Name = item.FromName,
                TemplateCode = code,
                Params = new List<string>() { item.No, item.Password },
                TelNumber = item.Contact,
            };
            await _mediator.Publish(new PushMessageNotify(messageDto), cancellationToken);
            await _orderVisitRepository.Updateable()
                .Where(m => m.Id == item.Id)
                .SetColumns(m => m.VisitState == EVisitState.SMSVisiting)
                .ExecuteCommandAsync();
        }
    }

    public ISugarQueryable<Order> QueryOrders(QueryOrderDto dto)
    {
        var isCenter = _sessionContext.OrgIsCenter;

        var query = _orderRepository.Queryable();
        if (!isCenter)
        {
            //todo 可优化
            query.Where(d => SqlFunc.Subqueryable<WorkflowTrace>()
                .Where(step => step.ExternalId == d.Id &&
                               !string.IsNullOrEmpty(step.HandlerOrgId) &&
                               step.HandlerOrgId.StartsWith(_sessionContext.RequiredOrgId) &&
                               step.TraceState != EWorkflowTraceState.StepRemoveByPrevious &&
                               step.TraceState != EWorkflowTraceState.StepRemoveByRecall).Any());
        }

        return query
            .Includes(x => x.OrderScreens)
            .WhereIF(!string.IsNullOrEmpty(dto.Keyword), d => d.Title.StartsWith(dto.Keyword!)) //标题
            .WhereIF(!string.IsNullOrEmpty(dto.ProvinceNo), d => d.ProvinceNo == dto.ProvinceNo) //省本地编号
            .WhereIF(!string.IsNullOrEmpty(dto.No), d => d.No == dto.No) //工单编码
            .WhereIF(!string.IsNullOrEmpty(dto.AcceptType), d => d.AcceptTypeCode == dto.AcceptType)//受理类型
                                                                                                    //.WhereIF(dto.AcceptTypes.Any(), d => dto.AcceptTypes.Contains(d.AcceptTypeCode)) //受理类型
            .WhereIF(!string.IsNullOrEmpty(dto.Channel), d => d.SourceChannelCode == dto.Channel)
            //.WhereIF(dto.Channels.Any(), d => dto.Channels.Contains(d.SourceChannelCode)) //来源渠道
            //.WhereIF(dto.HotspotIds.Any(), d => dto.HotspotIds.Contains(d.HotspotId)) //热点类型
            .WhereIF(!string.IsNullOrEmpty(dto.Hotspot), d => d.HotspotSpliceName != null && d.HotspotSpliceName.Contains(dto.Hotspot))
            .WhereIF(!string.IsNullOrEmpty(dto.TransferPhone), d => d.TransferPhone == dto.TransferPhone!) //转接号码
                                                                                                           //.WhereIF(dto.OrgCodes.Any(), d => d.Workflow.Assigns.Any(s => dto.OrgCodes.Contains(s.OrgCode)))
                                                                                                           //.WhereIF(dto.OrgCodes.Any(), d => dto.OrgCodes.Contains(d.ActualHandleOrgCode)) //接办部门
            .WhereIF(!string.IsNullOrEmpty(dto.OrgId), d => d.CurrentHandleOrgId == dto.OrgId)//接办部门
            .WhereIF(!string.IsNullOrEmpty(dto.NameOrNo), d => d.AcceptorName == dto.NameOrNo! || d.AcceptorStaffNo == 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 == dto.FromPhone) //来电号码
            .WhereIF(!string.IsNullOrEmpty(dto.PhoneNo), d => d.Contact == dto.PhoneNo!) //联系电话
			//.WhereIF(!string.IsNullOrEmpty(dto.PushTypeCode), d => d.PushTypeCode == dto.PushTypeCode) //推送分类
            .WhereIF(!string.IsNullOrEmpty(dto.PushTypeCode), x => x.OrderPushTypes.Any(opt => opt.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.Status.HasValue, d => d.Status == dto.Status)//工单状态
                                                                      //.WhereIF(dto.Statuses.Any(d => d == EOrderStatus.SpecialToUnAccept), d => d.Status <= EOrderStatus.SpecialToUnAccept)
            .WhereIF(!string.IsNullOrEmpty(dto.ActualHandlerName), d => d.ActualHandlerName == 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.CurrentStepCode == 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 == dto.FromName) //来电人姓名
                                                                                           //.WhereIF(dto.AreaCodes.Any(), d => dto.AreaCodes.Contains(d.AreaCode)) //区域
            .WhereIF(!string.IsNullOrEmpty(dto.AreaCode), d => d.AreaCode == dto.AreaCode)//区域
            .WhereIF(dto.IsProvinceOrder.HasValue && dto.IsProvinceOrder == true, d => d.Source == ESource.ProvinceStraight)
            .WhereIF(dto.IsProvinceOrder.HasValue && dto.IsProvinceOrder == false, d => d.Source != ESource.ProvinceStraight)
            .WhereIF(!string.IsNullOrEmpty(dto.SensitiveWord), d => SqlFunc.JsonArrayAny(d.Sensitive, dto.SensitiveWord))
            .WhereIF(dto.IsSensitiveWord.HasValue && dto.IsSensitiveWord == true, d => d.Sensitive != null && SqlFunc.JsonArrayLength(d.Sensitive) > 0)
            .WhereIF(dto.IsUrgent.HasValue, d => d.IsUrgent == dto.IsUrgent.Value)
            .WhereIF(!string.IsNullOrEmpty(dto.ProvinceChannel) && dto.ProvinceChannel == "1", d => d.Source == ESource.ProvinceStraight &&
            d.SourceChannelCode == "SZMHD" && d.IsProvince == false)//政民互动直派
            .WhereIF(!string.IsNullOrEmpty(dto.ProvinceChannel) && dto.ProvinceChannel == "2", d => d.Source == ESource.ProvinceStraight &&
            d.SourceChannelCode == "SZMHD" && d.IsProvince == true)//政民互动
            .WhereIF(!string.IsNullOrEmpty(dto.ProvinceChannel) && dto.ProvinceChannel == "3", d => d.Source == ESource.ProvinceStraight &&
            d.SourceChannelCode == "S12345" && d.IsProvince == true)//省12345
            .OrderByDescending(d => d.CreationTime);
    }

    /// <summary>
    /// 未签收统计
    /// </summary>
    /// <param name="dto"></param>
    /// <returns></returns>
    public ISugarQueryable<Order, WorkflowStep> QueryUnsignedOrders(QueryUnsignedOrdersRequest dto)
    {
        if (dto.EndTime.HasValue)
            dto.EndTime = dto.EndTime.Value.AddDays(1).AddSeconds(-1);
        var IsCenter = _sessionContext.OrgIsCenter;
        return _orderRepository.Queryable()
            .LeftJoin<WorkflowStep>((x, ws) => x.Id == ws.ExternalId)
            .WhereIF(dto.StartTime.HasValue, (x, ws) => ws.CreationTime >= dto.StartTime)
            .WhereIF(dto.EndTime.HasValue, (x, ws) => ws.CreationTime <= dto.EndTime)
            .WhereIF(dto.Level == 0 && IsCenter == false, (x, ws) => ws.AcceptorOrgId.StartsWith(_sessionContext.OrgId))
            .WhereIF(dto.Level == 1, (x, ws) => ws.AcceptorOrgId == _sessionContext.OrgId)
            .WhereIF(dto.Level == 2, (x, ws) => ws.AcceptorOrgId.StartsWith(_sessionContext.OrgId))
            .WhereIF(dto.Signed == 0, (x, ws) => ws.Status == Share.Enums.FlowEngine.EWorkflowStepStatus.WaitForAccept)
            .WhereIF(dto.Signed == 1, (x, ws) => ws.Status == Share.Enums.FlowEngine.EWorkflowStepStatus.WaitForHandle)
            .Where((x, ws) => ws.CountersignPosition == Share.Enums.FlowEngine.ECountersignPosition.None && x.Status > EOrderStatus.WaitForAccept)
            .OrderByDescending((x, ws) => ws.CreationTime);
    }

    /// <summary>
    /// 信件来源统计
    /// </summary>
    /// <param name="dto"></param>
    /// <returns></returns>
    public ISugarQueryable<Order> QueryOrderSource(QueryOrderSourceRequest dto)
    {
        return _orderRepository.Queryable()
            .WhereIF(dto.StartTime.HasValue, d => d.CreationTime >= dto.StartTime)
            .WhereIF(dto.EndTime.HasValue, d => d.CreationTime <= dto.EndTime)
            .WhereIF(dto.TypeId != null && dto.TypeId == 1, x => x.IdentityType == EIdentityType.Citizen)
            .WhereIF(dto.TypeId != null && dto.TypeId == 2, x => x.IdentityType == EIdentityType.Enterprise)
            .Where(d => d.SourceChannel != null && d.SourceChannel != "");
    }

    /// <summary>
    /// 信件来源统计明细
    /// </summary>
    /// <param name="dto"></param>
    /// <returns></returns>
    public ISugarQueryable<Order> QueryOrderSourceDetail(QueryOrderSourceDetailRequest dto)
    {

        return _orderRepository.Queryable()
            .WhereIF(!string.IsNullOrEmpty(dto.SourceChannel), d => d.SourceChannel == dto.SourceChannel)
            .WhereIF(dto.Time.HasValue, d => d.CreationTime.ToString("yyyy-MM-dd") == dto.Time.Value.ToString("yyyy-MM-dd"))
                .WhereIF(dto.TypeId != null && dto.TypeId == 1, x => x.IdentityType == EIdentityType.Citizen)
                .WhereIF(dto.TypeId != null && dto.TypeId == 2, x => x.IdentityType == EIdentityType.Enterprise)
            .Where(d => d.SourceChannel != null && d.SourceChannel != "");
    }

    /// <summary>
    /// 部门超期统计
    /// </summary>
    /// <param name="dto"></param>
    /// <returns></returns>
    public ISugarQueryable<OrderBiOrgDataListVo> QueryOrgDataList(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 queryOrder = _systemOrganizeRepository.Queryable()
            .LeftJoin<Order>((x, o) => x.Id == o.ActualHandleOrgCode)
            .WhereIF(dto.StartTime.HasValue, (x, o) => o.CreationTime >= dto.StartTime)
            .WhereIF(dto.EndTime.HasValue, (x, o) => o.CreationTime <= dto.EndTime)
            .WhereIF(dto.TypeId != null && dto.TypeId == 1, (x, o) => o.IdentityType == EIdentityType.Citizen)
            .WhereIF(dto.TypeId != null && dto.TypeId == 2, (x, o) => o.IdentityType == EIdentityType.Enterprise)
            .WhereIF(IsCenter == false, (x, o) => o.ActualHandleOrgCode == _sessionContext.RequiredOrgId)
            .GroupBy((x, o) => new { x.Id, x.Name })
            .Select((x, o) => new OrderBiOrgDataListVo
            {
                OrgName = x.Name,
                OrgId = x.Id,
                HandlerExtendedNum = SqlFunc.AggregateSum(SqlFunc.IIF(o.Status >= EOrderStatus.Filed && o.ExpiredTime < o.FiledTime, 1, 0)),
                NoHandlerExtendedNum = SqlFunc.AggregateSum(SqlFunc.IIF(o.Status < EOrderStatus.Filed && o.ExpiredTime < SqlFunc.GetDate(), 1, 0)),
            }).MergeTable();

        var queryCountersign = _workflowStepRepository.Queryable()
            .Where(x => x.CountersignPosition != ECountersignPosition.None)
            .WhereIF(dto.StartTime.HasValue, x => x.CreationTime >= dto.StartTime)
            .WhereIF(dto.EndTime.HasValue, x => x.CreationTime <= dto.EndTime)
            .GroupBy(x => x.HandlerOrgId)
            .Select(x => new OrderBiOrgDataListVo
            {
                OrgId = x.HandlerOrgId,
                CounterHandlerExtendedNum = SqlFunc.AggregateSum(SqlFunc.IIF(x.Status == Share.Enums.FlowEngine.EWorkflowStepStatus.Handled && x.StepExpiredTime < x.HandleTime, 1, 0)),
                CounterNoHandlerExtendedNum = SqlFunc.AggregateSum(SqlFunc.IIF(x.Status != Share.Enums.FlowEngine.EWorkflowStepStatus.Handled && x.StepExpiredTime < SqlFunc.GetDate(), 1, 0)),
            }).MergeTable();

        var query =
            _systemOrganizeRepository.Queryable()
            .LeftJoin(queryOrder, (x, or) => x.Id == or.OrgId)
            .LeftJoin(queryCountersign, (x, or, co) => x.Id == co.OrgId)
            //.LeftJoin<SystemOrganize>((or, co, so) => or.OrgId.Substring(SqlFunc.MappingColumn<int>("0"), SqlFunc.MappingColumn<int>("6")) == so.Id)
            .LeftJoin<SystemOrganize>((x, or, co, so) => or.OrgId == so.Id && so.Level == 1)
            .GroupBy((x, or, co, so) => new { so.Id, so.Name })
            .Select((x, or, co, so) => new OrderBiOrgDataListVo
            {
                OrgName = so.Name,
                OrgId = so.Id,
                HandlerExtendedNum = SqlFunc.AggregateSum(or.HandlerExtendedNum),
                NoHandlerExtendedNum = SqlFunc.AggregateSum(or.NoHandlerExtendedNum),
                CounterHandlerExtendedNum = SqlFunc.AggregateSum(co.CounterHandlerExtendedNum),
                CounterNoHandlerExtendedNum = SqlFunc.AggregateSum(co.CounterNoHandlerExtendedNum)
            }).MergeTable();

        query = query.WhereIF(!string.IsNullOrEmpty(dto.Keyword), x => x.OrgName.Contains(dto.Keyword!))
            .Where(x => x.OrgName != null).Where(x => x.HandlerExtendedNum > 0 || x.NoHandlerExtendedNum > 0 || x.CounterHandlerExtendedNum > 0 || x.CounterNoHandlerExtendedNum > 0);
        switch (dto.SortField)
        {
            case "handlerExtendedNum":
                query = dto.SortRule is 0 ? query.OrderBy(x => x.HandlerExtendedNum) : query.OrderByDescending(x => x.HandlerExtendedNum);
                break;
            case "counterHandlerExtendedNum":
                query = dto.SortRule is 0 ? query.OrderBy(x => x.CounterHandlerExtendedNum) : query.OrderByDescending(x => x.CounterHandlerExtendedNum);
                break;
            case "noHandlerExtendedNum":
                query = dto.SortRule is 0 ? query.OrderBy(x => x.NoHandlerExtendedNum) : query.OrderByDescending(x => x.NoHandlerExtendedNum);
                break;
            case "counterNoHandlerExtendedNum":
                query = dto.SortRule is 0 ? query.OrderBy(x => x.CounterNoHandlerExtendedNum) : query.OrderByDescending(x => x.CounterNoHandlerExtendedNum);
                break;
        }
        return query;
    }

    /// <summary>
    /// 部门超期统计明细
    /// </summary>
    /// <param name="dto"></param>
    /// <returns></returns>
	public ISugarQueryable<Order> QueryOrgDataListDetail(OrgDataListDetailRequest dto)
    {
        dto.EndTime = dto.EndTime.AddDays(1).AddSeconds(-1);

        var quer = _orderRepository.Queryable()
            .InnerJoin<SystemOrganize>((x, so) => x.ActualHandleOrgCode == so.Id && so.Level == 1)
            .Where((x, so) => x.CreationTime >= dto.StartTime && x.CreationTime <= dto.EndTime)
            //.WhereIF(dto.Statuses.Any(), x => dto.Statuses.Contains(x.Status))  //工单状态
            .WhereIF(dto.QueryType == 1, (x, so) => x.Status >= EOrderStatus.Filed && x.ExpiredTime < x.FiledTime) //业务已办超期
            .WhereIF(dto.QueryType == 3, (x, so) => x.Status < EOrderStatus.Filed && x.ExpiredTime < SqlFunc.GetDate()) //业务待办超期
            .WhereIF(dto.TypeId != null && dto.TypeId == 1, (x, so) => x.IdentityType == EIdentityType.Citizen)
            .WhereIF(dto.TypeId != null && dto.TypeId == 2, (x, so) => x.IdentityType == EIdentityType.Enterprise)
            .WhereIF(dto.QueryType == 5, (x, so) =>
                (x.Status >= EOrderStatus.Filed && x.ExpiredTime < x.FiledTime) || (x.Status < EOrderStatus.Filed && x.ExpiredTime < SqlFunc.GetDate()))
            .WhereIF(!string.IsNullOrEmpty(dto.OrgCode), (x, so) => x.ActualHandleOrgCode == dto.OrgCode)
            .Select(x => new Order { DaysOverdueOrgName = x.ActualHandleOrgName, Id = x.Id.SelectAll() });
        if (dto.QueryType is 2 or 4)
        {
            var queryCountersign = _workflowStepRepository.Queryable()
                .Includes(x => x.Workflow)
                .InnerJoin<SystemOrganize>((x, so) => x.HandlerOrgId == so.Id && so.Level == 1)
                .Where((x, so) => x.CountersignPosition != ECountersignPosition.None && x.Workflow.ModuleCode == "OrderHandle")
                .Where((x, so) => x.CreationTime >= dto.StartTime && x.CreationTime <= dto.EndTime)
                .WhereIF(dto.QueryType == 2, (x, so) => x.Status == Share.Enums.FlowEngine.EWorkflowStepStatus.Handled && x.StepExpiredTime < x.HandleTime) //会签已办超期
                .WhereIF(dto.QueryType == 4, (x, so) => x.Status != Share.Enums.FlowEngine.EWorkflowStepStatus.Handled && x.StepExpiredTime < SqlFunc.GetDate())//会签待办超期
                .WhereIF(!string.IsNullOrEmpty(dto.OrgCode), (x, so) => x.HandlerOrgId == dto.OrgCode)
                .GroupBy(x => new { x.ExternalId, x.HandlerOrgName })
                .Select(x => new { Id = x.ExternalId, WorkflowId = x.ExternalId, HandlerOrgName = x.HandlerOrgName });

            quer = _orderRepository.Queryable().InnerJoin(queryCountersign, (o, w) => o.Id == w.Id)
                .Select((o, w) => new Order { DaysOverdueOrgName = w.HandlerOrgName, Id = o.Id.SelectAll() });
            //quer = _orderRepository.UnionAll(quer, queryCountersignOrder);
        }
        return quer;
    }

    /// <summary>
    /// 部门超期统计明细
    /// </summary>
    /// <param name="dto"></param>
    /// <returns></returns>
    public ISugarQueryable<Order> QueryOrgDataListDetail(OrgDataListAllDetailRequest dto)
    {
        dto.EndTime = dto.EndTime.AddDays(1).AddSeconds(-1);
        bool IsCenter = _sessionContext.OrgIsCenter;

        var quer = _orderRepository.Queryable()
            .InnerJoin<SystemOrganize>((x, so) => x.ActualHandleOrgCode == so.Id && so.Level == 1)
            .Where((x, so) => x.CreationTime >= dto.StartTime && x.CreationTime <= dto.EndTime)
            .WhereIF(dto.Statuses.Any(), (x, so) => dto.Statuses.Contains(x.Status))  //工单状态
            .WhereIF(dto.ExpiredType is 2, (x, so) => x.OrderDelays.Any(x => x.DelayState == EDelayState.Pass))
            .Where((x, so) =>
                (x.Status >= EOrderStatus.Filed && x.ExpiredTime < x.FiledTime) || (x.Status < EOrderStatus.Filed && x.ExpiredTime < SqlFunc.GetDate()))
            .WhereIF(IsCenter == false, (x, so) => x.ActualHandleOrgCode == _sessionContext.RequiredOrgId)
            .WhereIF(!string.IsNullOrEmpty(dto.OrgName), (x, so) => x.AcceptorOrgName == dto.OrgName)
            .Select(x => new Order { DaysOverdueOrgName = x.ActualHandleOrgName, Id = x.Id.SelectAll() });

        var queryCountersign = _workflowStepRepository.Queryable()
            .Includes(x => x.Workflow)
            .InnerJoin<SystemOrganize>((x, so) => x.HandlerOrgId == so.Id && so.Level == 1)
            .LeftJoin<Order>((x, so, o) => x.ExternalId == o.Id)
            .Where((x, so) => x.CountersignPosition != ECountersignPosition.None && x.Workflow.ModuleCode == "OrderHandle")
            .Where((x, so) => x.CreationTime >= dto.StartTime && x.CreationTime <= dto.EndTime)
            .Where((x, so) => (x.Status == Share.Enums.FlowEngine.EWorkflowStepStatus.Handled && x.StepExpiredTime < x.HandleTime) || (x.Status != Share.Enums.FlowEngine.EWorkflowStepStatus.Handled && x.StepExpiredTime < SqlFunc.GetDate()))
            .WhereIF(IsCenter == false, (x, so) => x.HandlerOrgId == _sessionContext.RequiredOrgId)
            .WhereIF(dto.ExpiredType is 2, (x, so, o) => o.OrderDelays.Any(x => x.DelayState == EDelayState.Pass))
            .GroupBy(x => new { x.ExternalId, x.HandlerOrgName })
            .Select(x => new { Id = x.ExternalId, WorkflowId = x.ExternalId, HandlerOrgName = x.HandlerOrgName });

        var queryCountersignOrder = _orderRepository.Queryable().InnerJoin(queryCountersign, (o, w) => o.Id == w.Id)
            .Select((o, w) => new Order { DaysOverdueOrgName = w.HandlerOrgName, Id = o.Id.SelectAll() });
        quer = _orderRepository.UnionAll(quer, queryCountersignOrder).MergeTable().InnerJoin<SystemOrganize>((x, so) => x.ActualHandleOrgCode == so.Id);
        return quer;
    }

    /// <summary>
    /// 回退错件统计
    /// </summary>
    /// <param name="dto"></param>
    /// <returns></returns>
    public ISugarQueryable<OrderReTransactVo> OrderReTransact(QueryOrderReTransactRequest dto)
    {

        return _orderSpecialDetailRepository.Queryable()
            .Includes(x => x.OrderSpecial)
            .WhereIF(!string.IsNullOrEmpty(dto.OrgName), x => x.OrgName.Contains(dto.OrgName!))
            .Where(x => x.OrderSpecial.ESpecialType == ESpecialType.ReTransact)
            .Where(x => x.OrderSpecial.CreationTime >= dto.StartTime)
            .Where(x => x.OrderSpecial.CreationTime <= dto.EndTime)
            .GroupBy(x => new { Time = x.OrderSpecial.CreationTime.ToString("yyyy-MM-dd"), x.OrgId, x.OrgName })
            .Select(x => new OrderReTransactVo
            {
                Time = x.OrderSpecial.CreationTime.ToString("yyyy-MM-dd"),
                OrgId = x.OrgId,
                OrgName = x.OrgName,
                Num = SqlFunc.AggregateCount(1)
            }).MergeTable()
            .OrderByIF(dto.SortRule is 0, x => x.Num, OrderByType.Asc)
            .OrderByIF(dto.SortRule is 1, x => x.Num, OrderByType.Desc); ;
    }
    /// <summary>
    /// 回退错件明细统计
    /// </summary>
    /// <param name="dto"></param>
    /// <returns></returns>
    public ISugarQueryable<OrderSpecialDetail> QueryOrderSourceDetail(QueryOrderReTransactDetailRequest dto)
    {
        if (!dto.StartTime.HasValue || !dto.EndTime.HasValue)
            throw UserFriendlyException.SameMessage("请选择时间!");
        dto.EndTime = dto.EndTime.Value.AddDays(1).AddSeconds(-1);
        return _orderSpecialDetailRepository.Queryable()
            .Includes(x => x.OrderSpecial, s => s.Order)
            .WhereIF(!string.IsNullOrEmpty(dto.OrgName), x => x.OrgName.Contains(dto.OrgName!))
            .WhereIF(!string.IsNullOrEmpty(dto.ErrorName), x => x.ErrorName.Contains(dto.ErrorName!))
            .WhereIF(!string.IsNullOrEmpty(dto.No), x => x.OrderSpecial!.Order!.No!.Contains(dto.No!))
            .Where(x => x.OrderSpecial.ESpecialType == ESpecialType.ReTransact)
            .Where(x => x.OrderSpecial.CreationTime >= dto.StartTime)
            .Where(x => x.OrderSpecial.CreationTime <= dto.EndTime);
    }
    /// <summary>
    /// 部门满意度统计
    /// </summary>
    /// <returns></returns>
    public async Task<List<VisitAndOrgSatisfactionStatisticsDto>> VisitAndOrgSatisfactionStatistics(PagedKeywordSonRequest dto)
    {
        dto.EndTime = dto.EndTime.Value.AddDays(1).AddSeconds(-1);

        bool IsCenter = _sessionContext.OrgIsCenter;

        var list = _orderVisitDetailRepository.Queryable()
            .Where(x => x.OrderVisit.VisitTime >= dto.StartTime.Value && x.OrderVisit.VisitTime <= dto.EndTime.Value && x.VisitTarget == EVisitTarget.Org && x.OrderVisit.VisitState == EVisitState.Visited && !string.IsNullOrEmpty(x.VisitOrgCode))
            .WhereIF(string.IsNullOrEmpty(dto.OrgName) == false, x => x.VisitOrgName.Contains(dto.OrgName))
            .WhereIF(string.IsNullOrEmpty(dto.LineNum) == false, x => x.OrderVisit.Order.CallRecord.Gateway.Contains(dto.LineNum))
            .WhereIF(dto.TypeCode != null && dto.TypeCode == 1, x => x.OrderVisit.Order.IdentityType == EIdentityType.Citizen)
            .WhereIF(dto.TypeCode != null && dto.TypeCode == 2, x => x.OrderVisit.Order.IdentityType == EIdentityType.Enterprise)
            .WhereIF(IsCenter == false, x => x.VisitOrgCode.StartsWith(_sessionContext.OrgId));

        var data = new List<VisitAndOrgSatisfactionStatisticsDto>();

        if (IsCenter && list != null)
        {
            data = await list.GroupBy(x => new
            {
                VisitOrgCode = x.VisitOrgCode.Substring(SqlFunc.MappingColumn<int>("0"), SqlFunc.MappingColumn<int>("6"))
            })
            .Select(x => new VisitAndOrgSatisfactionStatisticsDto()
            {
                OrgCode = x.VisitOrgCode.Substring(SqlFunc.MappingColumn<int>("0"), SqlFunc.MappingColumn<int>("6")),
                TotalSumCount = SqlFunc.AggregateCount(x.VisitOrgCode.Substring(SqlFunc.MappingColumn<int>("0"), SqlFunc.MappingColumn<int>("6"))),
                VerySatisfiedCount = SqlFunc.IIF(dto.TypeId == 1, SqlFunc.AggregateSum(SqlFunc.IIF(SqlFunc.JsonField(x.OrgProcessingResults, "Key") == "5", 1, 0)), SqlFunc.AggregateSum(SqlFunc.IIF(SqlFunc.JsonField(x.OrgHandledAttitude, "Key") == "5", 1, 0))),//非常满意数
                SatisfiedCount = SqlFunc.IIF(dto.TypeId == 1, SqlFunc.AggregateSum(SqlFunc.IIF(SqlFunc.JsonField(x.OrgProcessingResults, "Key") == "4", 1, 0)), SqlFunc.AggregateSum(SqlFunc.IIF(SqlFunc.JsonField(x.OrgHandledAttitude, "Key") == "4", 1, 0))), //满意数
                RegardedAsSatisfiedCount = SqlFunc.IIF(dto.TypeId == 1, SqlFunc.AggregateSum(SqlFunc.IIF(SqlFunc.JsonField(x.OrgProcessingResults, "Key") == "-1", 1, 0)), SqlFunc.AggregateSum(SqlFunc.IIF(SqlFunc.JsonField(x.OrgHandledAttitude, "Key") == "-1", 1, 0))),//视为满意
                DefaultSatisfiedCount = SqlFunc.IIF(dto.TypeId == 1, SqlFunc.AggregateSum(SqlFunc.IIF(SqlFunc.JsonField(x.OrgProcessingResults, "Key") == "0", 1, 0)), SqlFunc.AggregateSum(SqlFunc.IIF(SqlFunc.JsonField(x.OrgHandledAttitude, "Key") == "0", 1, 0))),//默认满意
                NoSatisfiedCount = SqlFunc.IIF(dto.TypeId == 1, SqlFunc.AggregateSum(SqlFunc.IIF(SqlFunc.JsonField(x.OrgProcessingResults, "Key") == "2", 1, 0)), SqlFunc.AggregateSum(SqlFunc.IIF(SqlFunc.JsonField(x.OrgHandledAttitude, "Key") == "2", 1, 0))),//不满意
                NoEvaluateCount = SqlFunc.IIF(dto.TypeId == 1, SqlFunc.AggregateSum(SqlFunc.IIF(SqlFunc.JsonField(x.OrgProcessingResults, "Key") == "7", 1, 0)), SqlFunc.AggregateSum(SqlFunc.IIF(SqlFunc.JsonField(x.OrgHandledAttitude, "Key") == "7", 1, 0))),//未做评价
                NoPutThroughCount = SqlFunc.IIF(dto.TypeId == 1, SqlFunc.AggregateSum(SqlFunc.IIF(SqlFunc.JsonField(x.OrgProcessingResults, "Key") == "6", 1, 0)), SqlFunc.AggregateSum(SqlFunc.IIF(SqlFunc.JsonField(x.OrgHandledAttitude, "Key") == "6", 1, 0))),//未接通
            })
            .MergeTable()
            .LeftJoin<SystemOrganize>((it, o) => it.OrgCode == o.Id)
            .Select((it, o) => new VisitAndOrgSatisfactionStatisticsDto()
            {
                OrgName = o.Name,
                OrgCode = it.OrgCode,
                OrgType = o.OrgType,
                TotalSumCount = it.TotalSumCount,
                VerySatisfiedCount = it.VerySatisfiedCount,//非常满意数
                SatisfiedCount = it.SatisfiedCount, //满意数
                RegardedAsSatisfiedCount = it.RegardedAsSatisfiedCount,//视为满意
                DefaultSatisfiedCount = it.DefaultSatisfiedCount,//默认满意
                NoSatisfiedCount = it.NoSatisfiedCount,//不满意
                NoEvaluateCount = it.NoEvaluateCount,//未做评价
                NoPutThroughCount = it.NoPutThroughCount,//未接通
            })
            .ToListAsync();
        }
        else
        {
            data = await list.GroupBy(x => new
            {
                x.VisitOrgCode
            })
            .Select(x => new VisitAndOrgSatisfactionStatisticsDto()
            {
                OrgCode = x.VisitOrgCode,
                TotalSumCount = SqlFunc.AggregateCount(x.VisitOrgCode.Substring(SqlFunc.MappingColumn<int>("0"), SqlFunc.MappingColumn<int>("6"))),
                VerySatisfiedCount = SqlFunc.IIF(dto.TypeId == 1, SqlFunc.AggregateSum(SqlFunc.IIF(SqlFunc.JsonField(x.OrgProcessingResults, "Key") == "5", 1, 0)), SqlFunc.AggregateSum(SqlFunc.IIF(SqlFunc.JsonField(x.OrgHandledAttitude, "Key") == "5", 1, 0))),//非常满意数
                SatisfiedCount = SqlFunc.IIF(dto.TypeId == 1, SqlFunc.AggregateSum(SqlFunc.IIF(SqlFunc.JsonField(x.OrgProcessingResults, "Key") == "4", 1, 0)), SqlFunc.AggregateSum(SqlFunc.IIF(SqlFunc.JsonField(x.OrgHandledAttitude, "Key") == "4", 1, 0))), //满意数
                RegardedAsSatisfiedCount = SqlFunc.IIF(dto.TypeId == 1, SqlFunc.AggregateSum(SqlFunc.IIF(SqlFunc.JsonField(x.OrgProcessingResults, "Key") == "-1", 1, 0)), SqlFunc.AggregateSum(SqlFunc.IIF(SqlFunc.JsonField(x.OrgHandledAttitude, "Key") == "-1", 1, 0))),//视为满意
                DefaultSatisfiedCount = SqlFunc.IIF(dto.TypeId == 1, SqlFunc.AggregateSum(SqlFunc.IIF(SqlFunc.JsonField(x.OrgProcessingResults, "Key") == "0", 1, 0)), SqlFunc.AggregateSum(SqlFunc.IIF(SqlFunc.JsonField(x.OrgHandledAttitude, "Key") == "0", 1, 0))),//默认满意
                NoSatisfiedCount = SqlFunc.IIF(dto.TypeId == 1, SqlFunc.AggregateSum(SqlFunc.IIF(SqlFunc.JsonField(x.OrgProcessingResults, "Key") == "2", 1, 0)), SqlFunc.AggregateSum(SqlFunc.IIF(SqlFunc.JsonField(x.OrgHandledAttitude, "Key") == "2", 1, 0))),//不满意
                NoEvaluateCount = SqlFunc.IIF(dto.TypeId == 1, SqlFunc.AggregateSum(SqlFunc.IIF(SqlFunc.JsonField(x.OrgProcessingResults, "Key") == "7", 1, 0)), SqlFunc.AggregateSum(SqlFunc.IIF(SqlFunc.JsonField(x.OrgHandledAttitude, "Key") == "7", 1, 0))),//未做评价
                NoPutThroughCount = SqlFunc.IIF(dto.TypeId == 1, SqlFunc.AggregateSum(SqlFunc.IIF(SqlFunc.JsonField(x.OrgProcessingResults, "Key") == "6", 1, 0)), SqlFunc.AggregateSum(SqlFunc.IIF(SqlFunc.JsonField(x.OrgHandledAttitude, "Key") == "6", 1, 0))),//未接通
            })
            .MergeTable()
            .LeftJoin<SystemOrganize>((it, o) => it.OrgCode == o.Id)
            .Select((it, o) => new VisitAndOrgSatisfactionStatisticsDto()
            {
                OrgName = o.Name,
                OrgCode = it.OrgCode,
                OrgType = o.OrgType,
                TotalSumCount = it.TotalSumCount,
                VerySatisfiedCount = it.VerySatisfiedCount,//非常满意数
                SatisfiedCount = it.SatisfiedCount, //满意数
                RegardedAsSatisfiedCount = it.RegardedAsSatisfiedCount,//视为满意
                DefaultSatisfiedCount = it.DefaultSatisfiedCount,//默认满意
                NoSatisfiedCount = it.NoSatisfiedCount,//不满意
                NoEvaluateCount = it.NoEvaluateCount,//未做评价
                NoPutThroughCount = it.NoPutThroughCount,//未接通
            })
            .ToListAsync();
        }
        return data;
    }

    /// <summary>
    /// 子部门满意度
    /// </summary>
    /// <returns></returns>
    public async Task<List<VisitAndOrgSatisfactionStatisticsDto>> VisitAndOrgStatisfactionOrgDetail(PagedKeywordSonRequest dto)
    {
        dto.EndTime = dto.EndTime.Value.AddDays(1).AddSeconds(-1);

        bool IsCenter = _sessionContext.OrgIsCenter;

        var list = await _systemOrganizeRepository.Queryable().Where(x => x.Id.StartsWith(dto.OrgCode))
            .LeftJoin<OrderVisitDetail>((x, it) => x.Id == it.VisitOrgCode)
            .Where((x, it) => it.OrderVisit.VisitTime >= dto.StartTime.Value && it.OrderVisit.VisitTime <= dto.EndTime.Value && it.VisitTarget == EVisitTarget.Org && it.OrderVisit.VisitState == EVisitState.Visited)
            .WhereIF(dto.OrgCode == "001", (x, it) => it.VisitOrgCode == dto.OrgCode)
            .WhereIF(dto.OrgCode != "001", (x, it) => it.VisitOrgCode.StartsWith(dto.OrgCode))
            .WhereIF(!string.IsNullOrEmpty(dto.LineNum), (x, it) => it.OrderVisit.Order.CallRecord.Gateway.Contains(dto.LineNum))
            .WhereIF(dto.TypeCode != null && dto.TypeCode == 1, (x, it) => it.OrderVisit.Order.IdentityType == EIdentityType.Citizen)
            .WhereIF(dto.TypeCode != null && dto.TypeCode == 2, (x, it) => it.OrderVisit.Order.IdentityType == EIdentityType.Enterprise)
            .WhereIF(IsCenter == false, (x, it) => it.VisitOrgCode.StartsWith(_sessionContext.OrgId))
             .GroupBy((x, it) => new
             {
                 VisitOrgCode = it.VisitOrgCode
             })
             .Select((x, it) => new VisitAndOrgSatisfactionStatisticsDto()
             {
                 OrgCode = it.VisitOrgCode,
                 TotalSumCount = SqlFunc.AggregateCount(it.VisitOrgCode.Substring(SqlFunc.MappingColumn<int>("0"), SqlFunc.MappingColumn<int>("9"))),
                 VerySatisfiedCount = SqlFunc.IIF(dto.TypeId == 1, SqlFunc.AggregateSum(SqlFunc.IIF(SqlFunc.JsonField(it.OrgProcessingResults, "Key") == "5", 1, 0)), SqlFunc.AggregateSum(SqlFunc.IIF(SqlFunc.JsonField(it.OrgHandledAttitude, "Key") == "5", 1, 0))),//非常满意数
                 SatisfiedCount = SqlFunc.IIF(dto.TypeId == 1, SqlFunc.AggregateSum(SqlFunc.IIF(SqlFunc.JsonField(it.OrgProcessingResults, "Key") == "4", 1, 0)), SqlFunc.AggregateSum(SqlFunc.IIF(SqlFunc.JsonField(it.OrgHandledAttitude, "Key") == "4", 1, 0))), //满意数
                 RegardedAsSatisfiedCount = SqlFunc.IIF(dto.TypeId == 1, SqlFunc.AggregateSum(SqlFunc.IIF(SqlFunc.JsonField(it.OrgProcessingResults, "Key") == "-1", 1, 0)), SqlFunc.AggregateSum(SqlFunc.IIF(SqlFunc.JsonField(it.OrgHandledAttitude, "Key") == "-1", 1, 0))),//视为满意
                 DefaultSatisfiedCount = SqlFunc.IIF(dto.TypeId == 1, SqlFunc.AggregateSum(SqlFunc.IIF(SqlFunc.JsonField(it.OrgProcessingResults, "Key") == "0", 1, 0)), SqlFunc.AggregateSum(SqlFunc.IIF(SqlFunc.JsonField(it.OrgHandledAttitude, "Key") == "0", 1, 0))),//默认满意
                 NoSatisfiedCount = SqlFunc.IIF(dto.TypeId == 1, SqlFunc.AggregateSum(SqlFunc.IIF(SqlFunc.JsonField(it.OrgProcessingResults, "Key") == "2", 1, 0)), SqlFunc.AggregateSum(SqlFunc.IIF(SqlFunc.JsonField(it.OrgHandledAttitude, "Key") == "2", 1, 0))),//不满意
                 NoEvaluateCount = SqlFunc.IIF(dto.TypeId == 1, SqlFunc.AggregateSum(SqlFunc.IIF(SqlFunc.JsonField(it.OrgProcessingResults, "Key") == "7", 1, 0)), SqlFunc.AggregateSum(SqlFunc.IIF(SqlFunc.JsonField(it.OrgHandledAttitude, "Key") == "7", 1, 0))),//未做评价
                 NoPutThroughCount = SqlFunc.IIF(dto.TypeId == 1, SqlFunc.AggregateSum(SqlFunc.IIF(SqlFunc.JsonField(it.OrgProcessingResults, "Key") == "6", 1, 0)), SqlFunc.AggregateSum(SqlFunc.IIF(SqlFunc.JsonField(it.OrgHandledAttitude, "Key") == "6", 1, 0)))//未接通
             })
             .MergeTable()
             .LeftJoin<SystemOrganize>((x, it) => x.OrgCode == it.Id)
            .Select((x, it) => new VisitAndOrgSatisfactionStatisticsDto()
            {
                OrgName = it.Name,
                OrgCode = x.OrgCode,
                OrgType = it.OrgType,
                TotalSumCount = x.TotalSumCount,
                VerySatisfiedCount = x.VerySatisfiedCount,//非常满意数
                SatisfiedCount = x.SatisfiedCount, //满意数
                RegardedAsSatisfiedCount = x.RegardedAsSatisfiedCount,//视为满意
                DefaultSatisfiedCount = x.DefaultSatisfiedCount,//默认满意
                NoSatisfiedCount = x.NoSatisfiedCount,//不满意
                NoEvaluateCount = x.NoEvaluateCount,//未做评价
                NoPutThroughCount = x.NoPutThroughCount,//未接通
            })
            .ToListAsync();
        return list;
    }

    

    /// <summary>
    /// 热点区域统计
    /// </summary>
    /// <param name="dto"></param>
    /// <returns></returns>
    public async Task<(List<SystemArea> area, object items)> HotspotAndAreaStatistics(HotspotAndAreaStatisticsReq dto)
    {
        dto.EndTime = dto.EndTime.AddDays(1).AddSeconds(-1);
        var areaList = _systemAreaRepository.Queryable().Where(x => SqlFunc.Length(x.Id) == 6 && x.Id != "510000").OrderBy(x => x.Id).MergeTable();

        var endIndex = (2 * dto.HotspotLevel).ToString();
        var hotspotList = _hotspotRepository.Queryable().Where(x => SqlFunc.Length(x.Id) == int.Parse(endIndex))
            .Select(x => new
            {
                HotspotId = x.Id,
                HotspotName = x.HotSpotFullName,
            }).MergeTable();


        var orderList = _orderRepository.Queryable().Where(x => x.CreationTime >= dto.StartTime && x.CreationTime < dto.EndTime)
            .Select(x => new
            {
                HotspotId = x.HotspotId.Substring(0, int.Parse(endIndex)),
                AreaCode = x.AreaCode.Substring(SqlFunc.MappingColumn<int>("0"), SqlFunc.MappingColumn<int>("6")),
            }).MergeTable();


        var hotListAndOrder = hotspotList.LeftJoin(orderList, (it, o) => it.HotspotId == o.HotspotId)
             .GroupBy((it, o) => new
             {
                 it.HotspotId,
                 it.HotspotName,
                 AreaCode = o.AreaCode.Substring(SqlFunc.MappingColumn<int>("0"), SqlFunc.MappingColumn<int>("6")),
             })
             .OrderBy((it, o) => it.HotspotId)
             .Select((it, o) => new
             {
                 HotspotId = it.HotspotId,
                 HotspotName = it.HotspotName,
                 AreaCode = o.AreaCode.Substring(SqlFunc.MappingColumn<int>("0"), SqlFunc.MappingColumn<int>("6")),
                 Count = SqlFunc.AggregateCount(it.HotspotId)
             }).MergeTable();


        var returnList = await areaList.LeftJoin(hotListAndOrder, (pp, dd) => pp.Id == dd.AreaCode)
           .GroupBy((pp, dd) => new
           {
               HotspotId = dd.HotspotId,
               HotspotName = dd.HotspotName,
               AreaCode = pp.Id,
               AreaName = pp.AreaName,
           })
           .OrderBy((pp, dd) => dd.HotspotId)
           .Select((pp, dd) => new
           {
               HotspotId = dd.HotspotId,
               HotspotName = dd.HotspotName,
               AreaCode = pp.Id,
               AreaName = pp.AreaName,
               Count = SqlFunc.AggregateSum(dd.Count)
           }).ToPivotListAsync(q => q.AreaCode, q => new { q.HotspotName, q.HotspotId }, q => q.Sum(x => x.Count));

        var areaTitleList = await _systemAreaRepository.Queryable().Where(x => SqlFunc.Length(x.Id) == 6 && x.Id != "510000").OrderBy(x => x.Id).ToListAsync();
        return (areaTitleList, returnList);
    }




    /// <summary>
    /// 热点区域统计--导出
    /// </summary>
    /// <param name="dto"></param>
    /// <returns></returns>
    public async Task<DataTable> HotspotAndAreaStatisticsExport(HotspotAndAreaStatisticsReq dto)
    {
        dto.EndTime = dto.EndTime.AddDays(1).AddSeconds(-1);
        var areaList = _systemAreaRepository.Queryable().Where(x => SqlFunc.Length(x.Id) == 6 && x.Id != "510000").OrderBy(x => x.Id).MergeTable();

        var endIndex = (2 * dto.HotspotLevel).ToString();
        var hotspotList = _hotspotRepository.Queryable().Where(x => SqlFunc.Length(x.Id) == int.Parse(endIndex))
            .Select(x => new
            {
                HotspotId = x.Id,
                HotspotName = x.HotSpotFullName,
            }).MergeTable();


        var orderList = _orderRepository.Queryable().Where(x => x.CreationTime >= dto.StartTime && x.CreationTime < dto.EndTime)
            .Select(x => new
            {
                HotspotId = x.HotspotId.Substring(0, int.Parse(endIndex)),
                AreaCode = x.AreaCode.Substring(SqlFunc.MappingColumn<int>("0"), SqlFunc.MappingColumn<int>("6")),
            }).MergeTable();


        var hotListAndOrder = hotspotList.LeftJoin(orderList, (it, o) => it.HotspotId == o.HotspotId)
             .GroupBy((it, o) => new
             {
                 it.HotspotId,
                 it.HotspotName,
                 AreaCode = o.AreaCode.Substring(SqlFunc.MappingColumn<int>("0"), SqlFunc.MappingColumn<int>("6")),
             })
             .OrderBy((it, o) => it.HotspotId)
             .Select((it, o) => new
             {
                 HotspotId = it.HotspotId,
                 HotspotName = it.HotspotName,
                 AreaCode = o.AreaCode.Substring(SqlFunc.MappingColumn<int>("0"), SqlFunc.MappingColumn<int>("6")),
                 Count = SqlFunc.AggregateCount(it.HotspotId)
             }).MergeTable();


        var returnList = await areaList.LeftJoin(hotListAndOrder, (pp, dd) => pp.Id == dd.AreaCode)
           .GroupBy((pp, dd) => new
           {
               HotspotId = dd.HotspotId,
               HotspotName = dd.HotspotName,
               AreaCode = pp.Id,
               AreaName = pp.AreaName,
           })
           .OrderBy((pp, dd) => dd.HotspotId)
           .Select((pp, dd) => new
           {
               HotspotId = dd.HotspotId,
               HotspotName = dd.HotspotName,
               AreaCode = pp.Id,
               AreaName = pp.AreaName,
               Count = SqlFunc.AggregateSum(dd.Count)
           }).ToPivotTableAsync(q => q.AreaName, q => new { q.HotspotName }, q => q.Sum(x => x.Count));
        //returnList.Rows.RemoveAt(returnList.Rows.Count-1);
        return returnList;
    }





    public DataTable InitHotspotTable(DataTable dt, List<string> AddColumnName, int HotspotLevel)
    {
        //修改列名
        if (dt.Columns.Contains("HotspotName"))
            dt.Columns["HotspotName"].ColumnName = "一级热点";

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

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

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

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

        //添加表头
        foreach (var item in AddColumnName)
        {
            if (item.Equals("HotspotName") || item.Equals("热点分类"))
            {
                dt2.Columns.Add("一级热点");
                if (HotspotLevel == 2)
                {
                    dt2.Columns.Add("二级热点");
                }
                else if (HotspotLevel == 3)
                {
                    dt2.Columns.Add("二级热点");
                    dt2.Columns.Add("三级热点");
                }
                else if (HotspotLevel == 4)
                {
                    dt2.Columns.Add("二级热点");
                    dt2.Columns.Add("三级热点");
                    dt2.Columns.Add("四级热点");
                }
            }
            else
            {
                //if (dt.Columns.Contains(item))
                dt2.Columns.Add(item);
            }
        }

        //处理数据
        foreach (DataRow sourceRow in dt.Rows)
        {
            if (sourceRow["一级热点"].ToString() == "")
            {
                continue;
            }
            DataRow targetRow = dt2.NewRow();
            foreach (var item in AddColumnName)
            {
                if (item.Equals("HotspotName") || item.Equals("热点分类"))
                {
                    targetRow["一级热点"] = sourceRow["一级热点"].ToString()?.Split('-')[0];
                    if (HotspotLevel == 2)
                    {
                        targetRow["二级热点"] = sourceRow["一级热点"].ToString()?.Split('-')[1];
                    }
                    else if (HotspotLevel == 3)
                    {
                        try
                        {
                            targetRow["二级热点"] = sourceRow["一级热点"].ToString()?.Split('-')[1];
                        }
                        catch
                        {
                            targetRow["二级热点"] = "";
                        }
                        try
                        {
                            targetRow["三级热点"] = sourceRow["一级热点"].ToString()?.Split('-')[2];
                        }
                        catch
                        {
                            targetRow["三级热点"] = "";
                        }
                    }
                    else if (HotspotLevel == 4)
                    {
                        try
                        {
                            targetRow["二级热点"] = sourceRow["一级热点"].ToString()?.Split('-')[1];
                        }
                        catch
                        {
                            targetRow["二级热点"] = "";
                        }
                        try
                        {
                            targetRow["三级热点"] = sourceRow["一级热点"].ToString()?.Split('-')[2];
                        }
                        catch
                        {
                            targetRow["三级热点"] = "";
                        }
                        try
                        {
                            targetRow["四级热点"] = sourceRow["一级热点"].ToString()?.Split('-')[3];
                        }
                        catch
                        {
                            targetRow["四级热点"] = "";
                        }

                    }
                }
                else
                {
                    try
                    {
                        targetRow[item] = sourceRow[item];
                    }
                    catch
                    {
                        targetRow[item] = 0;
                    }
                }
            }
            dt2.Rows.Add(targetRow);
        }

        return dt2;
    }

    /// <summary>
    /// 热点类型满意度统计
    /// </summary>
    /// <returns></returns>
    public async Task<List<VisitAndHotspotSatisfactionStatisticsDto>> VisitAndHotspotSatisfactionStatistics(VisitAndHotspotPagedKeywordRequest dto)
    {
        dto.EndTime = dto.EndTime.Value.AddDays(1).AddSeconds(-1);

        bool IsCenter = _sessionContext.OrgIsCenter;

        var list = _orderVisitDetailRepository.Queryable()
            .Includes(x => x.OrderVisit, v => v.Order)
            .InnerJoin<Hotspot>((x, h) => h.Id == x.OrderVisit.Order.HotspotId.Substring(SqlFunc.MappingColumn<int>("0"), SqlFunc.MappingColumn<int>("2")) && h.ParentId == null)
            .Where(x => x.OrderVisit.VisitTime >= dto.StartTime.Value && x.OrderVisit.VisitTime <= dto.EndTime.Value && x.VisitTarget == EVisitTarget.Org && x.OrderVisit.VisitState == EVisitState.Visited && !string.IsNullOrEmpty(x.VisitOrgCode))
            .WhereIF(string.IsNullOrEmpty(dto.HotspotName) == false, (x, h) => h.HotSpotName == dto.HotspotName)
            .WhereIF(IsCenter == false, x => x.VisitOrgCode.StartsWith(_sessionContext.OrgId))
            .WhereIF(dto.TypeId == 1, x => x.OrgProcessingResults != null)
            .WhereIF(dto.TypeId == 2, x => x.OrgHandledAttitude != null);

        var data = new List<VisitAndHotspotSatisfactionStatisticsDto>();

        if (IsCenter && list != null)
        {
            data = await list.GroupBy((x, h) => new
            {
                HotspotName = h.HotSpotName,
                HotspotId = h.Id
            })
            .Select((x, h) => new VisitAndHotspotSatisfactionStatisticsDto()
            {
                HotspotName = h.HotSpotName,
                HotspotId = h.Id,
                TotalSumCount = SqlFunc.AggregateCount(x.OrderVisit.Order.HotspotId.Substring(SqlFunc.MappingColumn<int>("0"), SqlFunc.MappingColumn<int>("2"))),
                VerySatisfiedCount = SqlFunc.IIF(dto.TypeId == 1, SqlFunc.AggregateSum(SqlFunc.IIF(SqlFunc.JsonField(x.OrgProcessingResults, "Key") == "5", 1, 0)), SqlFunc.AggregateSum(SqlFunc.IIF(SqlFunc.JsonField(x.OrgHandledAttitude, "Key") == "5", 1, 0))),//非常满意数
                SatisfiedCount = SqlFunc.IIF(dto.TypeId == 1, SqlFunc.AggregateSum(SqlFunc.IIF(SqlFunc.JsonField(x.OrgProcessingResults, "Key") == "4", 1, 0)), SqlFunc.AggregateSum(SqlFunc.IIF(SqlFunc.JsonField(x.OrgHandledAttitude, "Key") == "4", 1, 0))), //满意数
                RegardedAsSatisfiedCount = SqlFunc.IIF(dto.TypeId == 1, SqlFunc.AggregateSum(SqlFunc.IIF(SqlFunc.JsonField(x.OrgProcessingResults, "Key") == "-1", 1, 0)), SqlFunc.AggregateSum(SqlFunc.IIF(SqlFunc.JsonField(x.OrgHandledAttitude, "Key") == "-1", 1, 0))),//视为满意
                DefaultSatisfiedCount = SqlFunc.IIF(dto.TypeId == 1, SqlFunc.AggregateSum(SqlFunc.IIF(SqlFunc.JsonField(x.OrgProcessingResults, "Key") == "0", 1, 0)), SqlFunc.AggregateSum(SqlFunc.IIF(SqlFunc.JsonField(x.OrgHandledAttitude, "Key") == "0", 1, 0))),//默认满意
                NoSatisfiedCount = SqlFunc.IIF(dto.TypeId == 1, SqlFunc.AggregateSum(SqlFunc.IIF(SqlFunc.JsonField(x.OrgProcessingResults, "Key") == "2", 1, 0)), SqlFunc.AggregateSum(SqlFunc.IIF(SqlFunc.JsonField(x.OrgHandledAttitude, "Key") == "2", 1, 0))),//不满意
                NoEvaluateCount = SqlFunc.IIF(dto.TypeId == 1, SqlFunc.AggregateSum(SqlFunc.IIF(SqlFunc.JsonField(x.OrgProcessingResults, "Key") == "7", 1, 0)), SqlFunc.AggregateSum(SqlFunc.IIF(SqlFunc.JsonField(x.OrgHandledAttitude, "Key") == "7", 1, 0))),//未做评价
                NoPutThroughCount = SqlFunc.IIF(dto.TypeId == 1, SqlFunc.AggregateSum(SqlFunc.IIF(SqlFunc.JsonField(x.OrgProcessingResults, "Key") == "6", 1, 0)), SqlFunc.AggregateSum(SqlFunc.IIF(SqlFunc.JsonField(x.OrgHandledAttitude, "Key") == "6", 1, 0))),//未接通
            })
            .ToListAsync();
        }
        else
        {
            data = await list.GroupBy((x, h) => new
            {
                HotspotName = h.HotSpotName,
                HotspotId = h.Id
            })
            .Select((x, h) => new VisitAndHotspotSatisfactionStatisticsDto()
            {
                HotspotName = h.HotSpotName,
                HotspotId = h.Id,
                TotalSumCount = SqlFunc.AggregateCount(x.OrderVisit.Order.HotspotId.Substring(SqlFunc.MappingColumn<int>("0"), SqlFunc.MappingColumn<int>("2"))),
                VerySatisfiedCount = SqlFunc.IIF(dto.TypeId == 1, SqlFunc.AggregateSum(SqlFunc.IIF(SqlFunc.JsonField(x.OrgProcessingResults, "Key") == "5", 1, 0)), SqlFunc.AggregateSum(SqlFunc.IIF(SqlFunc.JsonField(x.OrgHandledAttitude, "Key") == "5", 1, 0))),//非常满意数
                SatisfiedCount = SqlFunc.IIF(dto.TypeId == 1, SqlFunc.AggregateSum(SqlFunc.IIF(SqlFunc.JsonField(x.OrgProcessingResults, "Key") == "4", 1, 0)), SqlFunc.AggregateSum(SqlFunc.IIF(SqlFunc.JsonField(x.OrgHandledAttitude, "Key") == "4", 1, 0))), //满意数
                RegardedAsSatisfiedCount = SqlFunc.IIF(dto.TypeId == 1, SqlFunc.AggregateSum(SqlFunc.IIF(SqlFunc.JsonField(x.OrgProcessingResults, "Key") == "-1", 1, 0)), SqlFunc.AggregateSum(SqlFunc.IIF(SqlFunc.JsonField(x.OrgHandledAttitude, "Key") == "-1", 1, 0))),//视为满意
                DefaultSatisfiedCount = SqlFunc.IIF(dto.TypeId == 1, SqlFunc.AggregateSum(SqlFunc.IIF(SqlFunc.JsonField(x.OrgProcessingResults, "Key") == "0", 1, 0)), SqlFunc.AggregateSum(SqlFunc.IIF(SqlFunc.JsonField(x.OrgHandledAttitude, "Key") == "0", 1, 0))),//默认满意
                NoSatisfiedCount = SqlFunc.IIF(dto.TypeId == 1, SqlFunc.AggregateSum(SqlFunc.IIF(SqlFunc.JsonField(x.OrgProcessingResults, "Key") == "2", 1, 0)), SqlFunc.AggregateSum(SqlFunc.IIF(SqlFunc.JsonField(x.OrgHandledAttitude, "Key") == "2", 1, 0))),//不满意
                NoEvaluateCount = SqlFunc.IIF(dto.TypeId == 1, SqlFunc.AggregateSum(SqlFunc.IIF(SqlFunc.JsonField(x.OrgProcessingResults, "Key") == "7", 1, 0)), SqlFunc.AggregateSum(SqlFunc.IIF(SqlFunc.JsonField(x.OrgHandledAttitude, "Key") == "7", 1, 0))),//未做评价
                NoPutThroughCount = SqlFunc.IIF(dto.TypeId == 1, SqlFunc.AggregateSum(SqlFunc.IIF(SqlFunc.JsonField(x.OrgProcessingResults, "Key") == "6", 1, 0)), SqlFunc.AggregateSum(SqlFunc.IIF(SqlFunc.JsonField(x.OrgHandledAttitude, "Key") == "6", 1, 0))),//未接通
            })
            .ToListAsync();
        }
        return data;
    }

    /// <summary>
    /// 热点满意度明细统计
    /// </summary>
    /// <param name="dto"></param>
    /// <returns></returns>
    public ISugarQueryable<OrderVisitDetail> VisitAndHotspotSatisfactionDetail(VisitAndHotspotPagedKeywordRequest dto)
    {
        dto.EndTime = dto.EndTime.Value.AddDays(1).AddSeconds(-1);
        bool IsCenter = _sessionContext.OrgIsCenter;
        var key = string.Empty;
        if (!string.IsNullOrEmpty(dto.TitleCode))
        {
            switch (dto.TitleCode)
            {
                case "verySatisfiedCount":
                    key = "5";
                    break;
                case "satisfiedCount":
                    key = "4";
                    break;
                case "regardedAsSatisfiedCount":
                    key = "-1";
                    break;
                case "defaultSatisfiedCount":
                    key = "0";
                    break;
                case "noSatisfiedCount":
                    key = "2";
                    break;
                case "noEvaluateCount":
                    key = "7";
                    break;
                case "noPutThroughCount":
                    key = "6";
                    break;
            }
        }
        return _orderVisitDetailRepository.Queryable()
            .Includes(x => x.OrderVisit, o => o.Order)
            .Where(x => x.OrderVisit.VisitTime >= dto.StartTime && x.OrderVisit.VisitTime <= dto.EndTime && x.VisitTarget == EVisitTarget.Org && x.OrderVisit.VisitState == EVisitState.Visited)
            .WhereIF(!string.IsNullOrEmpty(dto.HotspotName), x => x.OrderVisit.Order.Hotspot.HotSpotFullName.StartsWith(dto.HotspotName))
            .WhereIF(IsCenter == false, x => x.VisitOrgCode.StartsWith(_sessionContext.OrgId))
            .WhereIF(dto.TypeId is 1, x => SqlFunc.JsonField(x.OrgProcessingResults, "Key") == key)
            .WhereIF(dto.TypeId is 2, x => SqlFunc.JsonField(x.OrgHandledAttitude, "Key") == key);
    }

    /// <summary>
    /// 话务员办件统计
    /// </summary>
    /// <param name="dto"></param>
    /// <returns></returns>
    public ISugarQueryable<OrderBiCentreDataListVo> CentreDataList(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.HandOverToUnAccept), 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.HandOverToUnAccept) || 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);
        }
        return query;
    }

    /// <summary>
    /// 热点受理类型统计
    /// </summary>
    /// <param name="dto"></param>
    /// <returns></returns>
    public async Task<(List<SystemDicData> acceptTypes, object items)> HotspotAndAcceptTypeStatistics(HotspotAndAcceptTypeStatisticsReq dto)
    {
        dto.EndTime = dto.EndTime.AddDays(1).AddSeconds(-1);
        var dicList = _systemDicDataRepository.Queryable().Where(x => x.DicTypeCode == "AcceptType").OrderBy(x => x.Sort).MergeTable();
        var endIndex = (2 * dto.HotspotLevel).ToString();
        var hotspotList = _hotspotRepository.Queryable().Where(x => SqlFunc.Length(x.Id) == int.Parse(endIndex))
            .Select(x => new
            {
                HotspotId = x.Id,
                HotspotName = x.HotSpotFullName,
            }).MergeTable();

        var orderList = _orderRepository.Queryable().Where(x => x.CreationTime >= dto.StartTime && x.CreationTime < dto.EndTime)
            .Select(x => new
            {
                HotspotId = x.HotspotId.Substring(0, int.Parse(endIndex)),
                AcceptTypeCode = x.AcceptTypeCode,
            }).MergeTable();

        var hotListAndOrder = hotspotList.LeftJoin(orderList, (it, o) => it.HotspotId == o.HotspotId)
             .GroupBy((it, o) => new
             {
                 it.HotspotId,
                 it.HotspotName,
                 AcceptTypeCode = o.AcceptTypeCode,
             })
             .OrderBy((it, o) => it.HotspotId)
             .Select((it, o) => new
             {
                 HotspotId = it.HotspotId,
                 HotspotName = it.HotspotName,
                 AcceptTypeCode = o.AcceptTypeCode,
                 Count = SqlFunc.AggregateCount(it.HotspotId)
             }).MergeTable();

        var returnList = await dicList.LeftJoin(hotListAndOrder, (pp, dd) => pp.DicDataValue == dd.AcceptTypeCode)
           .GroupBy((pp, dd) => new
           {
               HotspotId = dd.HotspotId,
               HotspotName = dd.HotspotName,
               AcceptTypeCode = pp.DicDataValue,
               AcceptType = pp.DicDataName,
           })
           .OrderBy((pp, dd) => dd.HotspotId)
           .Select((pp, dd) => new
           {
               HotspotId = dd.HotspotId,
               HotspotName = dd.HotspotName,
               AcceptTypeCode = pp.DicDataValue,
               AcceptType = pp.DicDataName,
               Count = SqlFunc.AggregateSum(dd.Count)
           }).ToPivotListAsync(q => q.AcceptTypeCode, q => new { q.HotspotName, q.HotspotId }, q => q.Sum(x => x.Count));

        var titleList = await _systemDicDataRepository.Queryable().Where(x => x.DicTypeCode == "AcceptType").OrderBy(x => x.Sort).ToListAsync();
        return (titleList, returnList);
    }

    /// <summary>
    /// 热点受理类型统计--导出
    /// </summary>
    /// <param name="dto"></param>
    /// <returns></returns>
    public async Task<DataTable> HotspotAndAcceptTypeStatisticsExport(HotspotAndAcceptTypeStatisticsReq dto)
    {
        dto.EndTime = dto.EndTime.AddDays(1).AddSeconds(-1);
        var dicList = _systemDicDataRepository.Queryable().Where(x => x.DicTypeCode == "AcceptType").OrderBy(x => x.Sort).MergeTable();
        var endIndex = (2 * dto.HotspotLevel).ToString();
        var hotspotList = _hotspotRepository.Queryable().Where(x => SqlFunc.Length(x.Id) == int.Parse(endIndex))
            .Select(x => new
            {
                HotspotId = x.Id,
                HotspotName = x.HotSpotFullName,
            }).MergeTable();

        var orderList = _orderRepository.Queryable().Where(x => x.CreationTime >= dto.StartTime && x.CreationTime < dto.EndTime)
            .Select(x => new
            {
                HotspotId = x.HotspotId.Substring(0, int.Parse(endIndex)),
                AcceptTypeCode = x.AcceptTypeCode,
            }).MergeTable();

        var hotListAndOrder = hotspotList.LeftJoin(orderList, (it, o) => it.HotspotId == o.HotspotId)
             .GroupBy((it, o) => new
             {
                 it.HotspotId,
                 it.HotspotName,
                 AcceptTypeCode = o.AcceptTypeCode,
             })
             .OrderBy((it, o) => it.HotspotId)
             .Select((it, o) => new
             {
                 HotspotId = it.HotspotId,
                 HotspotName = it.HotspotName,
                 AcceptTypeCode = o.AcceptTypeCode,
                 Count = SqlFunc.AggregateCount(it.HotspotId)
             }).MergeTable();


        var returnList = await dicList.LeftJoin(hotListAndOrder, (pp, dd) => pp.DicDataValue == dd.AcceptTypeCode)
           .GroupBy((pp, dd) => new
           {
               HotspotId = dd.HotspotId,
               HotspotName = dd.HotspotName,
               AcceptTypeCode = pp.DicDataValue,
               AcceptType = pp.DicDataName,
           })
           .OrderBy((pp, dd) => dd.HotspotId)
           .Select((pp, dd) => new
           {
               HotspotId = dd.HotspotId,
               HotspotName = dd.HotspotName,
               AcceptTypeCode = pp.DicDataValue,
               AcceptType = pp.DicDataName,
               Count = SqlFunc.AggregateSum(dd.Count)
           }).ToPivotTableAsync(q => q.AcceptTypeCode, q => new { q.HotspotName }, q => q.Sum(x => x.Count));
        //returnList.Rows.RemoveAt(returnList.Rows.Count-1);
        return returnList;
    }

    /// <summary>
    /// 热点受理类型统计明细
    /// </summary>
    /// <param name="dto"></param>
    /// <returns></returns>
    public ISugarQueryable<Order> HotspotAndAcceptTypeStatisticsDetail(HotspotAndAcceptTypeStatisticsDetailReq dto)
    {
        dto.EndTime = dto.EndTime.AddDays(1).AddSeconds(-1);
        var query = _orderRepository.Queryable()
            .Where(x => x.HotspotId.StartsWith(dto.HotspotId) && x.CreationTime >= dto.StartTime && x.CreationTime < dto.EndTime)
            .WhereIF(!string.IsNullOrEmpty(dto.AcceptTypeCode), x => x.AcceptTypeCode.StartsWith(dto.AcceptTypeCode))
            .OrderByDescending(x => x.CreationTime);
        return query;
    }

    /// <summary>
    /// 甄别申请统计
    /// </summary>
    /// <returns></returns>
	public ISugarQueryable<OrderScreenApplyVo> OrderScreenApply(OrderScreenApplyPagedRequest dto)
    {
        if (dto.EndTime.HasValue)
            dto.EndTime = dto.EndTime.Value.AddDays(1).AddSeconds(-1);
        var query = _orderScreenRepository.Queryable()
            .WhereIF(dto.StartTime.HasValue && dto.EndTime.HasValue, x => x.CreationTime >= dto.StartTime && x.CreationTime <= dto.EndTime)
            .WhereIF(!string.IsNullOrEmpty(dto.ApplyUserName), x => x.CreatorName == dto.ApplyUserName)
            .WhereIF(!string.IsNullOrEmpty(dto.ApplyOrgName), x => x.CreatorOrgName == dto.ApplyOrgName)
            .GroupBy(x => new { ApplyTime = x.CreationTime.ToString("yyyy-MM-dd"), x.CreatorName, x.CreatorOrgName })
            .Select(x => new OrderScreenApplyVo
            {
                ApplyTime = x.CreationTime.ToString("yyyy-MM-dd"),
                ApplyUserName = x.CreatorName,
                ApplyOrgName = x.CreatorOrgName,
                ApplyNum = SqlFunc.AggregateCount(1)
            });
        return query;
    }

    /// <summary>
    /// 甄别审批统计
    /// </summary>
    /// <returns></returns>
    public ISugarQueryable<OrderScreenAuditVo> OrderScreenAudit(OrderScreenAuditPagedRequest dto)
    {
	    if (dto.EndTime.HasValue)
		    dto.EndTime = dto.EndTime.Value.AddDays(1).AddSeconds(-1);
	    var query = _workflowTraceRepository.Queryable()
		    .WhereIF(dto.StartTime.HasValue && dto.EndTime.HasValue, x => x.HandleTime >= dto.StartTime && x.HandleTime <= dto.EndTime)
		    .WhereIF(!string.IsNullOrEmpty(dto.AuditUserName), x => x.HandlerName == dto.AuditUserName)
            .Where(x=> x.HandlerName != null && x.HandlerName != "" )
            .WhereIF(dto.AuditType is 1,x=>x.Name == "班长审批")
		    .WhereIF(dto.AuditType is 2, x => x.Name == "中心领导")
			.GroupBy(x => new { x.HandlerName })
		    .Select(x => new OrderScreenAuditVo
			{
			  AuditName = x.HandlerName,
              AuditNum = SqlFunc.AggregateSum(SqlFunc.IIF(x.TraceType  ==  EWorkflowTraceType.Normal, 1, 0)),
              AuditBackNum = SqlFunc.AggregateSum(SqlFunc.IIF( x.TraceType == EWorkflowTraceType.Previous , 1, 0)),
			});
	    return query;
    }

    /// <summary>
    /// 中心受理统计（日期）
    /// </summary>
    /// <param name="dto"></param>
    /// <returns></returns>
    public ISugarQueryable<OrderCenterAcceptVo> OrderCenterAccept(OrderCenterAcceptPagedRequest dto)
    {
        if (dto.EndTime.HasValue)
            dto.EndTime = dto.EndTime.Value.AddDays(1).AddSeconds(-1);
        var sendBack = _orderSendBackAuditRepository.Queryable()
            .Where(x => x.State == ESendBackAuditState.End)
            .GroupBy(x => x.OrderId).Select(x => new { OrderId = x.OrderId });

        var query = _orderRepository.Queryable()
            .LeftJoin(sendBack, (d, s) => d.Id == s.OrderId)
            .WhereIF(dto.StartTime.HasValue && dto.EndTime.HasValue, d => d.CreationTime >= dto.StartTime && d.CreationTime <= dto.EndTime)
            .WhereIF(dto.TypeCode != null && dto.TypeCode == 1, d => d.IdentityType == EIdentityType.Citizen)
            .WhereIF(dto.TypeCode != null && dto.TypeCode == 2, d => d.IdentityType == EIdentityType.Enterprise)
            .GroupBy(d => d.CreationTime.ToString("yyyy-MM-dd"))
            .Select((d, s) => new OrderCenterAcceptVo
            {
                Time = d.CreationTime.ToString("yyyy-MM-dd"),
                AcceptNum = SqlFunc.AggregateCount(1),
                ValidNum = SqlFunc.AggregateSum(SqlFunc.IIF(d.AcceptType != "无效", 1, 0)),
                RepetitionNum = SqlFunc.AggregateSum(SqlFunc.IIF(d.DuplicateIds == null || SqlFunc.JsonArrayLength(d.DuplicateIds) > 0, 1, 0)),
                InvalidNum = SqlFunc.AggregateSum(SqlFunc.IIF(d.AcceptType == "无效", 1, 0)),
                HandleNum = SqlFunc.AggregateSum(SqlFunc.IIF(d.Status >= EOrderStatus.Filed, 1, 0)),
                NoHandleNum = SqlFunc.AggregateSum(SqlFunc.IIF(d.Status < EOrderStatus.Filed, 1, 0)),
                BackNum = SqlFunc.AggregateSum(SqlFunc.IIF(s.OrderId != null &&  s.OrderId != "", 1, 0)),
                DutyDeskNum = SqlFunc.AggregateDistinctCount(d.AcceptorId)
            });
        return query;
    }

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

	    var query = _orderRepository.Queryable()
		    .LeftJoin(sendBack, (d, s) => d.Id == s.OrderId)
		    .WhereIF(dto.StartTime.HasValue && dto.EndTime.HasValue, d => d.CreationTime >= dto.StartTime && d.CreationTime <= dto.EndTime)
		    .WhereIF(dto.TypeCode != null && dto.TypeCode == 1, d => d.IdentityType == EIdentityType.Citizen)
		    .WhereIF(dto.TypeCode != null && dto.TypeCode == 2, d => d.IdentityType == EIdentityType.Enterprise)
		    .GroupBy(d => d.AcceptorName)
		    .Select((d, s) => new OrderCenterAcceptUserVo
			{
			    AcceptUserName = d.AcceptorName,
			    AcceptNum = SqlFunc.AggregateCount(1),
			    ValidNum = SqlFunc.AggregateSum(SqlFunc.IIF(d.AcceptType != "无效", 1, 0)),
			    RepetitionNum = SqlFunc.AggregateSum(SqlFunc.IIF(d.DuplicateIds == null || SqlFunc.JsonArrayLength(d.DuplicateIds) > 0, 1, 0)),
			    InvalidNum = SqlFunc.AggregateSum(SqlFunc.IIF(d.AcceptType == "无效", 1, 0)),
			    HandleNum = SqlFunc.AggregateSum(SqlFunc.IIF(d.Status >= EOrderStatus.Filed, 1, 0)),
			    NoHandleNum = SqlFunc.AggregateSum(SqlFunc.IIF(d.Status < EOrderStatus.Filed, 1, 0)),
			    BackNum = SqlFunc.AggregateSum(SqlFunc.IIF(s.OrderId != null && s.OrderId != "", 1, 0))
		    });
	    return query;
    }




    #region private

    /// <summary>
    /// 接受外部工单（除省平台）
    /// </summary>
    /// <param name="dto"></param>
    /// <param name="cancellationToken"></param>
    /// <returns></returns>
    private async Task<AddOrderResponse> ReceiveOrderFromOtherPlatformAsync(AddOrderDto dto, List<FileDto> files,
        ISessionContext current, CancellationToken cancellationToken)
    {
        if (string.IsNullOrEmpty(dto.ExternalId))
            throw new UserFriendlyException("工单外部编号不能为空");

        var order = await _orderRepository.Queryable()
            .FirstAsync(d => d.ExternalId == dto.ExternalId, cancellationToken);
        if (order == null)
        {
            order = _mapper.Map<Order>(dto);
            order.InitId();
            if (files != null && files.Any())
                order.FileJson = await _fileRepository.AddFileAsync(files, order.Id, "", cancellationToken);
            await _orderDomainService.AddAsync(order, cancellationToken: cancellationToken);
        }
        else
        {
            _mapper.Map(dto, order);
            if (files != null && files.Any())
                order.FileJson = await _fileRepository.AddFileAsync(files, order.Id, "", cancellationToken);
            else
                order.FileJson = new List<Share.Dtos.File.FileJson>();
            await _orderRepository.UpdateAsync(order, cancellationToken);
        }

        return _mapper.Map<AddOrderResponse>(order);
    }

    /// <summary>
    /// 接受省平台工单
    /// </summary>
    private async Task<AddOrderResponse> ReceiveOrderFromProvinceAsync(AddOrderDto dto, List<FileDto> files,
        ISessionContext current, CancellationToken cancellationToken)
    {
        if (string.IsNullOrEmpty(dto.ProvinceNo))
            throw new UserFriendlyException("无效省工单编号");

        var orderExtension = await _orderDomainService.GetOrderExtensionsAsync(dto.ProvinceNo, cancellationToken);

        var order = await _orderRepository.GetAsync(d => d.ProvinceNo == dto.ProvinceNo, cancellationToken);
        if (order is null)
        {
            order = _mapper.Map<Order>(dto);
            order.InitId();
            if (files != null && files.Any())
                order.FileJson = await _fileRepository.AddFileAsync(files, order.Id, "", cancellationToken);
            await _orderDomainService.AddAsync(order, cancellationToken: cancellationToken);

            if (orderExtension is not null)
            {
                orderExtension.Id = order.Id;
                if (dto.OrderExtension != null)
                    _mapper.Map(dto.OrderExtension, orderExtension);
                await _orderDomainService.UpdateExtensionAsync(orderExtension, cancellationToken);
            }
        }
        else
        {
            _mapper.Map(dto, order);
            if (files != null && files.Any())
                order.FileJson = await _fileRepository.AddFileAsync(files, order.Id, "", cancellationToken);
            else
                order.FileJson = new List<Share.Dtos.File.FileJson>();
            order.ReTransactNum++;
            await _orderRepository.UpdateAsync(order, cancellationToken);

            if (orderExtension is not null)
            {
                orderExtension.Id = order.Id;
                if (dto.OrderExtension != null)
                    _mapper.Map(dto.OrderExtension, orderExtension);
                await _orderDomainService.UpdateExtensionAsync(orderExtension, cancellationToken);
            }

            //特提（撤回至发起)
            if (!string.IsNullOrEmpty(order.WorkflowId))
            {
                current = SessionContextCreator.CreateSessionContext("province");
                await _workflowDomainService.RecallToStartStepAsync(order.WorkflowId, "省工单重派", current, cancellationToken);
            }
        }
        return _mapper.Map<AddOrderResponse>(order);
    }

    /// <summary>
    /// 派单量统计
    /// </summary>
    /// <param name="dto"></param>
    /// <returns></returns>
    public async Task<List<SendOrderReportOutDto>> SendOrderReportAsync(QuerySendOrderRequest dto)
    {
        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 == WorkflowModuleConsts.OrderHandle && x.BusinessType == EBusinessType.Send && x.Status == EWorkflowStepStatus.Handled 
                && !string.IsNullOrEmpty(x.NextMainHandler) && x.NextMainHandler != OrgSeedData.CenterId)
                .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 SendOrderReportOutDto
                   {
                       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>
    public ISugarQueryable<OrderVisitJudeStatisticsRep> OrderVisitJudeStatistics(OrderVisitJudeStatisticsReq dto)
    {
        if (!dto.EndTime.HasValue)
        {
            dto.EndTime = dto.StartTime?.AddDays(1).AddSeconds(-1);
        }
        else
        {
            dto.EndTime = dto.EndTime?.AddDays(1).AddSeconds(-1);
        }
       return _orderVisitRepository.Queryable()
            .WhereIF(dto.StartTime.HasValue, x => x.VisitTime >= dto.StartTime)
            .WhereIF(dto.EndTime.HasValue, x => x.VisitTime <= dto.EndTime)
            .GroupBy(x => new { EmployeeId = x.EmployeeId, Name = x.Employee.Name })
            .Select(x => new OrderVisitJudeStatisticsRep()
            {
                EmpId = x.EmployeeId,
                EmpName = x.Employee.Name,
                OrgJudeCount = SqlFunc.AggregateSum(SqlFunc.IIF(x.OrgJudge.HasValue && x.OrgJudge == true && x.JudgeState !=null, 1, 0)), //部门扭转总件
                OrgJudeApprovalingCount = SqlFunc.AggregateSum(SqlFunc.IIF(x.OrgJudge.HasValue && x.OrgJudge == true && x.JudgeState == EJudgeState.Judging, 1, 0)),
                OrgJudeSuccessCount = SqlFunc.AggregateSum(SqlFunc.IIF(x.OrgJudge.HasValue && x.OrgJudge== true && x.JudgeState == EJudgeState.Agreed, 1, 0)),
                OrgJudeFailCount = SqlFunc.AggregateSum(SqlFunc.IIF(x.OrgJudge.HasValue && x.OrgJudge == true && x.JudgeState == EJudgeState.UnAgreed, 1, 0)),
                SeatJudeCount = SqlFunc.AggregateSum(SqlFunc.IIF(x.SeatJudge.HasValue && x.SeatJudge == true && x.JudgeState!=null, 1, 0)),
                SeatJudeApprovalingCount = SqlFunc.AggregateSum(SqlFunc.IIF(x.SeatJudge.HasValue && x.SeatJudge == true && x.JudgeState == EJudgeState.Judging, 1, 0)),
                SeatJudeSuccessCount = SqlFunc.AggregateSum(SqlFunc.IIF(x.SeatJudge.HasValue && x.SeatJudge == true && x.JudgeState == EJudgeState.Agreed, 1, 0)),
                SeatJudeFailCount = SqlFunc.AggregateSum(SqlFunc.IIF(x.SeatJudge.HasValue && x.SeatJudge == true && x.JudgeState == EJudgeState.UnAgreed, 1, 0))
            });
    }

    #endregion
}