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;

namespace Hotline.Application.Orders;

public class OrderApplication : IOrderApplication, IScopeDependency
{
    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;



    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
        )
    {
        _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;
    }

    /// <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);
        order.TimeLimit = expiredTimeConfig.TimeText;
        order.TimeLimitCount = expiredTimeConfig.Count;
        order.TimeLimitUnit = expiredTimeConfig.TimeType;
        order.ExpiredTime = expiredTimeConfig.ExpiredTime;
        order.NearlyExpiredTime = expiredTimeConfig.NearlyExpiredTime;
        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(order.StartTime.Value,
                now, order.ProcessType is EProcessType.Zhiban)
            : 0;
        var fileDuration = order.CenterToOrgTime.HasValue
            ? _timeLimitDomainService.CalcWorkTime(order.CenterToOrgTime.Value,
                now, order.ProcessType is EProcessType.Zhiban)
            : 0;
        var allDuration = order.StartTime.HasValue
            ? _timeLimitDomainService.CalcWorkTime(order.StartTime.Value, now,
                order.ProcessType is EProcessType.Zhiban)
            : 0;
        var creationTimeHandleDurationWorkday = order.ActualHandleTime.HasValue
                    ? _timeLimitDomainService.CalcWorkTime(order.CreationTime, now,
                    order.ProcessType is EProcessType.Zhiban)
                    : 0;
        var centerToOrgHandleDurationWorkday = order.ActualHandleTime.HasValue && order.CenterToOrgTime.HasValue
            ? _timeLimitDomainService.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.NearlyExpiredTimeOne || 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);
        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.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 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>
    /// <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);
        }
    }

    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.AcceptType == dto.AcceptType)//受理类型
            //.WhereIF(dto.AcceptTypes.Any(), d => dto.AcceptTypes.Contains(d.AcceptTypeCode)) //受理类型
            .WhereIF(!string.IsNullOrEmpty(dto.Channel), d=> d.SourceChannel == 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.OrgName), d=>d.CurrentHandleOrgName == dto.OrgName)//接办部门
            .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(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.ActualHandleStepCode == dto.CurrentStepCode) //当前办理节点
            .WhereIF(dto.ActualHandleTimeStart.HasValue, d => d.ActualHandleTime >= dto.ActualHandleTimeStart) //办结时间开始
            .WhereIF(dto.ActualHandleTimeEnd.HasValue, d => d.ActualHandleTime <= dto.ActualHandleTimeEnd) //办结时间结束
            .WhereIF(dto.IsOverTime == true, d => (d.ExpiredTime < DateTime.Now && d.Status < EOrderStatus.Filed) || (d.ExpiredTime < d.ActualHandleTime && d.Status >= EOrderStatus.Filed)) //是 超期
            .WhereIF(dto.IsOverTime == false, d => (d.ExpiredTime > DateTime.Now && d.Status < EOrderStatus.Filed) || (d.ExpiredTime > d.ActualHandleTime && d.Status >= EOrderStatus.Filed)) //否 超期
            .WhereIF(dto.IdentityType != null, d => d.IdentityType == dto.IdentityType) //来电主体
            .WhereIF(!string.IsNullOrEmpty(dto.FromName), d => d.FromName == 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)
    {
        if (dto.EndTime.HasValue)
            dto.EndTime = dto.EndTime.Value.AddDays(1).AddSeconds(-1);
        return _orderRepository.Queryable()
            .WhereIF(dto.StartTime.HasValue, d => d.CreationTime >= dto.StartTime)
            .WhereIF(dto.EndTime.HasValue, d => d.CreationTime <= dto.EndTime)
            .WhereIF(dto.IdentityType.HasValue, d => d.IdentityType == dto.IdentityType)
            .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.IdentityType.HasValue, d => d.IdentityType == dto.IdentityType)
            .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(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.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(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(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 ISugarQueryable<OrderVisitDetail> VisitAndOrgSatisfactionDetail(VisitAndOrgSatisfactionDetailDto dto)
    {
        dto.EndTime = dto.EndTime.AddDays(1).AddSeconds(-1);
        bool IsCenter = _sessionContext.OrgIsCenter;
        return _orderVisitDetailRepository.Queryable()
            .Includes(x => x.OrderVisit, o => o.Order, d => d.CallRecord)
            .Where(x => x.OrderVisit.VisitTime >= dto.StartTime && x.OrderVisit.VisitTime <= dto.EndTime && x.VisitTarget == EVisitTarget.Org && x.OrderVisit.VisitState == EVisitState.Visited)
            .WhereIF(dto.OrgCode == "001", x => x.VisitOrgCode == dto.OrgCode)
            //.WhereIF(dto.OrgCode != "001", x => x.VisitOrgCode == dto.OrgCode).
            .WhereIF(dto.IsOnlyMy == true, x => x.VisitOrgCode == dto.OrgCode)
            .WhereIF(IsCenter == true && dto.IsOnlyMy == true, x => x.VisitOrgCode == dto.OrgCode)
            .WhereIF(IsCenter == true && dto.IsOnlyMy == null, x => x.VisitOrgCode.StartsWith(dto.OrgCode))
            .WhereIF(IsCenter == false, x => x.VisitOrgCode == dto.OrgCode)
            .WhereIF(dto.TypeId is 1, x => SqlFunc.JsonField(x.OrgProcessingResults, "Key") == dto.DateValue)
            .WhereIF(dto.TypeId is 2, x => SqlFunc.JsonField(x.OrgHandledAttitude, "Key") == dto.DateValue)
            .WhereIF(!string.IsNullOrEmpty(dto.LineNum), x => x.OrderVisit.Order.CallRecord.Gateway == dto.LineNum);
    }

    /// <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)
           }).MergeTable().Where(q => q.HotspotName != "").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)
           }).Where(q => q.HotspotName != "").ToPivotTableAsync(q => q.AreaName, q => new { q.HotspotName }, q => q.Sum(x => x.Count));
        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)
        {
            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
                {
                    targetRow[item] = sourceRow[item];
                }
            }
            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));

        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;
        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.HotspotId), x => x.OrderVisit.Order.HotspotId.StartsWith(dto.HotspotId))
            .WhereIF(IsCenter == false, x => x.VisitOrgCode.StartsWith(_sessionContext.OrgId))
            .WhereIF(dto.TypeId is 1, x => SqlFunc.JsonField(x.OrgProcessingResults, "Key") == dto.TitleCode)
            .WhereIF(dto.TypeId is 2, x => SqlFunc.JsonField(x.OrgHandledAttitude, "Key") == dto.TitleCode);
    }
    #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))
                await _workflowDomainService.RecallToStartStepAsync(order.WorkflowId, "省工单重派", current, cancellationToken);
        }
        return _mapper.Map<AddOrderResponse>(order);
    }

    #endregion
}