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

using Hotline.Caching.Interfaces;
using Hotline.CallCenter.Calls;
using Hotline.FlowEngine.WorkflowModules;
using Hotline.FlowEngine.Workflows;
using Hotline.Orders;
using Hotline.Repository.SqlSugar.Extensions;
using Hotline.Schedulings;
using Hotline.Settings;
using Hotline.Settings.Hotspots;
using Hotline.Share.Dtos;
using Hotline.Share.Dtos.Bi;
using Hotline.Share.Dtos.Bigscreen;
using Hotline.Share.Dtos.CallCenter;
using Hotline.Share.Dtos.FlowEngine.Workflow;
using Hotline.Share.Dtos.Order;
using Hotline.Share.Enums.CallCenter;
using Hotline.Share.Enums.FlowEngine;
using Hotline.Share.Enums.Order;
using Hotline.Share.Requests;
using MapsterMapper;
using Microsoft.AspNetCore.Mvc;
using SqlSugar;
using XF.Domain.Authentications;
using XF.Domain.Constants;
using XF.Domain.Exceptions;
using XF.Domain.Repository;

namespace Hotline.Api.Controllers.Bi
{
    public class BiOrderController : BaseController
    {
        private readonly IOrderRepository _orderRepository;
        private readonly IRepository<Hotspot> _hotspotTypeRepository;
        private readonly ISystemDicDataCacheManager _sysDicDataCacheManager;
        private readonly IRepository<OrderVisitDetail> _orderVisitDetailRepository;
        private readonly IRepository<OrderDelay> _orderDelayRepository;
        private readonly IMapper _mapper;
        private readonly IRepository<WorkflowCountersign> _workflowCountersignRepository;
        private readonly IRepository<WorkflowStepHandler> _workflowStepHandleRepository;
        private readonly IRepository<OrderSpecial> _orderSpecialRepository;
        private readonly IRepository<OrderVisit> _orderVisitRepository;
        private readonly IRepository<TrCallRecord> _trCallRecordRepository;
        private readonly IRepository<OrderPublish> _orderPublishRepository;
        private readonly IRepository<SystemOrganize> _systemOrganizeRepository;
        private readonly IRepository<AiOrderVisitDetail> _aiOrderVisitDetailRepository;
        private readonly ISessionContext _sessionContext;
        private readonly ISystemSettingCacheManager _systemSettingCacheManager;
        private readonly IRepository<OrderSpecialDetail> _orderSpecialDetailRepository;
        private readonly IRepository<WorkflowTrace> _workflowTraceRepository;
        private readonly IRepository<OrderScreen> _orderScreenRepository;

        public BiOrderController(
            IOrderRepository orderRepository,
            IRepository<Hotspot> hotspotTypeRepository,
            ISystemDicDataCacheManager sysDicDataCacheManager,
            IRepository<OrderVisitDetail> orderVisitDetailRepository,
            IRepository<OrderDelay> orderDelayRepository,
            IRepository<WorkflowCountersign> workflowCountersignRepository,
            IRepository<OrderSpecial> orderSpecialRepository,
            IMapper mapper,
            IRepository<OrderVisit> orderVisitRepository,
            IRepository<TrCallRecord> trCallRecordRepository,
            IRepository<OrderPublish> orderPublishRepository,
            IRepository<SystemOrganize> systemOrganizeRepository,
            IRepository<AiOrderVisitDetail> aiOrderVisitDetailRepository,
            ISessionContext sessionContext,
            ISystemSettingCacheManager systemSettingCacheManager,
            IRepository<OrderSpecialDetail> orderSpecialDetailRepository,
            IRepository<WorkflowTrace> workflowTraceRepository,
            IRepository<OrderScreen> orderScreenRepository,
            IRepository<WorkflowStepHandler> workflowStepHandleRepository
            )
        {
            _orderRepository = orderRepository;
            _hotspotTypeRepository = hotspotTypeRepository;
            _sysDicDataCacheManager = sysDicDataCacheManager;
            _orderVisitDetailRepository = orderVisitDetailRepository;
            _orderDelayRepository = orderDelayRepository;
            _workflowCountersignRepository = workflowCountersignRepository;
            _orderSpecialRepository = orderSpecialRepository;
            _mapper = mapper;
            _orderVisitRepository = orderVisitRepository;
            _trCallRecordRepository = trCallRecordRepository;
            _orderPublishRepository = orderPublishRepository;
            _systemOrganizeRepository = systemOrganizeRepository;
            _aiOrderVisitDetailRepository = aiOrderVisitDetailRepository;
            _sessionContext = sessionContext;
            _systemSettingCacheManager = systemSettingCacheManager;
            _orderSpecialDetailRepository = orderSpecialDetailRepository;
            _workflowTraceRepository = workflowTraceRepository;
            _orderScreenRepository = orderScreenRepository;
            _workflowStepHandleRepository = workflowStepHandleRepository;
        }

        /// <summary>
        /// 部门超期统计明细
        /// </summary>
        /// <param name="dto"></param>
        /// <returns></returns>
        [HttpGet("org_data_list_detail")]
        public async Task<PagedDto<OrderDto>> OrgDataListDetail([FromQuery] OrgDataListDetailRequest dto)
        {
            dto.EndTime = dto.EndTime.AddDays(1).AddSeconds(-1);

            var (total, items) = await _orderRepository.Queryable()
            .Where(x => x.CreationTime >= dto.StartTime && x.CreationTime <= dto.EndTime)
            .WhereIF(dto.QueryType == 1, x => x.Status >= EOrderStatus.Filed && x.ExpiredTime < x.FiledTime) //业务已办超期
            //.WhereIF(dto.QueryType== 2,) //会签已办超期
            .WhereIF(dto.QueryType == 3, x => x.Status < EOrderStatus.Filed && x.ExpiredTime < SqlFunc.GetDate()) //业务待办超期
            .WhereIF(string.IsNullOrEmpty(dto.OrgCode), x => x.ActualHandleOrgCode == dto.OrgCode)
            //.WhereIF(dto.QueryType ==4,) //会签待办超期
            //.MergeTable();
            .ToPagedListAsync(dto.PageIndex, dto.PageSize);

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


        /// <summary>
        /// 部门超期统计
        /// </summary>
        /// <param name="dto"></param>
        /// <returns></returns>
        [HttpGet("org_data_list")]
        public async Task<PagedDto<OrderBiOrgDataListVo>> OrgDataList([FromQuery] ReportPagedRequest dto)
        {
            if (!dto.StartTime.HasValue || !dto.EndTime.HasValue) throw UserFriendlyException.SameMessage("请选择时间!");

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

            var IsCenter = _sessionContext.OrgIsCenter;


            var 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 queryOrder = _orderRepository.Queryable(false, false, false)
            //    .LeftJoin<SystemOrganize>((x, o) => x.ActualHandleOrgCode == o.Id)
            //    .WhereIF(dto.StartTime.HasValue, (x, o) => x.CreationTime >= dto.StartTime)
            //    .WhereIF(dto.EndTime.HasValue, (x, o) => x.CreationTime <= dto.EndTime)
            //    .WhereIF(IsCenter == false, (x, o) => x.ActualHandleOrgCode == _sessionContext.RequiredOrgId)
            //    .GroupBy((x, o) => new { x.ActualHandleOrgCode, o.Name })
            //    .Select((x, o) => new OrderBiOrgDataListVo
            //    {
            //        OrgName = o.Name,
            //        OrgId = x.ActualHandleOrgCode,
            //        HandlerExtendedNum = SqlFunc.AggregateSum(SqlFunc.IIF(x.Status >= EOrderStatus.Filed && x.ExpiredTime < x.FiledTime, 1, 0)),
            //        NoHandlerExtendedNum = SqlFunc.AggregateSum(SqlFunc.IIF(x.Status < EOrderStatus.Filed && x.ExpiredTime < SqlFunc.GetDate(), 1, 0)),
            //    }).MergeTable();

            var queryCountersign = _workflowCountersignRepository.Queryable()
                .LeftJoin<WorkflowCountersignMember>((x, o) => x.Id == o.WorkflowCountersignId)
                .WhereIF(dto.StartTime.HasValue, x => x.CreationTime >= dto.StartTime)
                .WhereIF(dto.EndTime.HasValue, x => x.CreationTime <= dto.EndTime)
                .GroupBy((x, o) => o.Key)
                .Select((x, o) => new OrderBiOrgDataListVo
                {
                    OrgId = o.Key,
                    CounterHandlerExtendedNum = SqlFunc.AggregateSum(SqlFunc.IIF(o.IsHandled, 1, 0)),
                    CounterNoHandlerExtendedNum = SqlFunc.AggregateSum(SqlFunc.IIF(o.IsHandled == false, 1, 0)),
                }).MergeTable();

            var query = queryOrder.LeftJoin(queryCountersign, (or, co) => or.OrgId == co.OrgId)
                .Select((or, co) => new OrderBiOrgDataListVo
                {
                    OrgName = or.OrgName,
                    OrgId = or.OrgId,
                    HandlerExtendedNum = or.HandlerExtendedNum,
                    NoHandlerExtendedNum = or.NoHandlerExtendedNum,
                    CounterHandlerExtendedNum = co.CounterHandlerExtendedNum,
                    CounterNoHandlerExtendedNum = co.CounterNoHandlerExtendedNum
                }).MergeTable();

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

            var (total, items) = await query.ToPagedListAsync(dto, HttpContext.RequestAborted);
            return new PagedDto<OrderBiOrgDataListVo>(total, items);
        }

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

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

            var query = _orderRepository.Queryable(false, false, false)
                .WhereIF(dto.StartTime.HasValue, it => it.CreationTime >= dto.StartTime)
                .WhereIF(dto.EndTime.HasValue, it => it.CreationTime <= dto.EndTime)
                .WhereIF(!string.IsNullOrEmpty(dto.Keyword), it => it.SignerName.Contains(dto.Keyword!))
                .GroupBy(it => new { it.SignerId, it.SignerName })
                .Select(it => new OrderBiCentreDataListVo
                {
                    UserName = it.SignerName,
                    UserId = it.SignerId,
                    //Subtotal = SqlFunc.AggregateCount(x.AcceptorId),
                    CentreArchive = SqlFunc.AggregateSum(SqlFunc.IIF(it.Status >= EOrderStatus.Filed && it.ProcessType == EProcessType.Zhiban, 1, 0)),
                    //CentreCareOf = SqlFunc.AggregateSum(SqlFunc.IIF(it.Status >= EOrderStatus.Filed && it.ProcessType == EProcessType.Jiaoban, 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.AcceptType == "无效" || (it.DuplicateIds != null && SqlFunc.JsonArrayLength(it.DuplicateIds) > 0), 1, 0))
                }).MergeTable();
            switch (dto.SortField)
            {
                case "centreArchive":
                    query = dto.SortRule == 0 ? query.OrderBy(x => x.CentreArchive) : query.OrderByDescending(x => x.CentreArchive);
                    break;
                case "centreCareOf":
                    query = dto.SortRule == 0 ? query.OrderBy(x => x.CentreCareOf) : query.OrderByDescending(x => x.CentreCareOf);
                    break;
                case "noCentreCareOf":
                    query = dto.SortRule == 0 ? query.OrderBy(x => x.NoCentreCareOf) : query.OrderByDescending(x => x.NoCentreCareOf);
                    break;
                case "invalid":
                    query = dto.SortRule == 0 ? query.OrderBy(x => x.Invalid) : query.OrderByDescending(x => x.Invalid);
                    break;
                case "repeat":
                    query = dto.SortRule == 0 ? query.OrderBy(x => x.Repeat) : query.OrderByDescending(x => x.Repeat);
                    break;
                case "subtotal":
                    query = dto.SortRule == 0 ? query.OrderBy(x => x.Subtotal) : query.OrderByDescending(x => x.Subtotal);
                    break;
            }

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

            var (total, items) = await query.Where(x => (x.CentreArchive + x.CentreCareOf + x.Invalid + x.Repeat) != 0).ToPagedListAsync(dto, HttpContext.RequestAborted);
            return new PagedDto<OrderBiCentreDataListVo>(total, items);
        }

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

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

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

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

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

            var IsCenter = _sessionContext.OrgIsCenter;

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

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


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

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

            var IsCenter = _sessionContext.OrgIsCenter;


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

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

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

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

            var IsCenter = _sessionContext.OrgIsCenter;

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


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

        /// <summary>
        /// 部门延期统计
        /// </summary>
        /// <param name="dto"></param>
        /// <returns></returns>
        [HttpGet("order-delay-data-list")]
        public async Task<IReadOnlyList<BiOrderDelayDataDto>> QueryOrderDelayDataList([FromQuery] QueryOrderDelayDataListRequest dto)
        {
            if (!dto.StartTime.HasValue || !dto.EndTime.HasValue) throw UserFriendlyException.SameMessage("请选择时间!");

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

            var IsCenter = _sessionContext.OrgIsCenter;

            var list = await _orderDelayRepository.Queryable()
                .LeftJoin<SystemOrganize>((x, o) => x.ApplyOrgCode == o.Id)
                .WhereIF(dto.StartTime.HasValue, (x, o) => x.CreationTime >= dto.StartTime)
                .WhereIF(dto.EndTime.HasValue, (x, o) => x.CreationTime <= dto.EndTime)
                .WhereIF(!string.IsNullOrEmpty(dto.OrgName), x => x.ApplyOrgName.Contains(dto.OrgName))
                .WhereIF(IsCenter == false, x => x.ApplyOrgCode.StartsWith(_sessionContext.RequiredOrgId))
                .GroupBy(x => new { x.ApplyOrgCode, x.ApplyOrgName })
                .Select(x => new BiOrderDelayDataDto
                {
                    OrgName = x.ApplyOrgName,
                    OrgCode = x.ApplyOrgCode,
                    PassTotal = SqlFunc.AggregateSum(SqlFunc.IIF(x.DelayState == EDelayState.Pass, 1, 0)),
                    NoPassTotal = SqlFunc.AggregateSum(SqlFunc.IIF(x.DelayState == EDelayState.NoPass, 1, 0)),
                    ExaminingTotal = SqlFunc.AggregateSum(SqlFunc.IIF(x.DelayState == EDelayState.Examining, 1, 0)),
                    AllTotal = SqlFunc.AggregateSum(SqlFunc.IIF(x.DelayState == EDelayState.Pass, 1, 0)) + SqlFunc.AggregateSum(SqlFunc.IIF(x.DelayState == EDelayState.NoPass, 1, 0)) + SqlFunc.AggregateSum(SqlFunc.IIF(x.DelayState == EDelayState.Examining, 1, 0))

                }).ToListAsync();

            return list;
        }

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

            var IsCenter = _sessionContext.OrgIsCenter;


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

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

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

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

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

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

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

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

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

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

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


            return returnModel;
        }

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

            var IsCenter = _sessionContext.OrgIsCenter;

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

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

            }
        }

        /// <summary>
        /// 部门满意度统计
        /// </summary>
        /// <param name="StartDate"></param>
        /// <param name="EndDate"></param>
        /// <param name="OrgName"></param>
        /// <param name="TypeId">1:办件结果 2:办件态度</param>
        /// <param name="LineNum"></param>
        /// <returns></returns>
        [HttpGet("visit-org-satisfaction-statistics")]
        public async Task<VisitAndOrgSatisfactionStatisticsResultDto> VisitAndOrgSatisfactionStatistics(DateTime StartDate, DateTime EndDate, string OrgName, int TypeId, string? LineNum)
        {
            EndDate = EndDate.AddDays(1).AddSeconds(-1);

            bool IsCenter = _sessionContext.OrgIsCenter;

            var list = _orderVisitDetailRepository.Queryable()
                .Where(x => x.OrderVisit.VisitTime >= StartDate && x.OrderVisit.VisitTime <= EndDate && x.VisitTarget == EVisitTarget.Org && x.OrderVisit.VisitState == EVisitState.Visited && !string.IsNullOrEmpty(x.VisitOrgCode))
                .WhereIF(string.IsNullOrEmpty(OrgName) == false, x => x.VisitOrgName.Contains(OrgName))
                .WhereIF(string.IsNullOrEmpty(LineNum) == false, x => x.OrderVisit.Order.CallRecord.Gateway.Contains(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(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(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(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(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(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(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(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(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(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(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(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(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(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(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();
            }


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

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

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


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



        /// <summary>
        /// 子部门满意度明细
        /// </summary>
        /// <param name="StartDate"></param>
        /// <param name="EndDate"></param>
        /// <param name="OrgCode"></param>
        /// <param name="TypeId"></param>
        /// <param name="LineNum"></param>
        /// <returns></returns>
        [HttpGet("visit-org-statisfaction-org-detail")]
        public async Task<VisitAndOrgSatisfactionStatisticsResultDto> VisitAndOrgStatisfactionOrgDetail(DateTime StartDate, DateTime EndDate, string OrgCode, int TypeId, string? LineNum)
        {
            EndDate = EndDate.AddDays(1).AddSeconds(-1);

            bool IsCenter = _sessionContext.OrgIsCenter;

            var list = await _systemOrganizeRepository.Queryable().Where(x => x.Id.StartsWith(OrgCode))
                .LeftJoin<OrderVisitDetail>((x, it) => x.Id == it.VisitOrgCode)
                .Where((x, it) => it.OrderVisit.VisitTime >= StartDate && it.OrderVisit.VisitTime <= EndDate && it.VisitTarget == EVisitTarget.Org && it.OrderVisit.VisitState == EVisitState.Visited)
                .WhereIF(OrgCode == "001", (x, it) => it.VisitOrgCode == OrgCode)
                .WhereIF(OrgCode != "001", (x, it) => it.VisitOrgCode.StartsWith(OrgCode))
                .WhereIF(!string.IsNullOrEmpty(LineNum), (x, it) => it.OrderVisit.Order.CallRecord.Gateway.Contains(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(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(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(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(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(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(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(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();

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

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

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

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


        /// <summary>
        /// 部门满意度明细
        /// </summary>
        /// <param name="StartDate"></param>
        /// <param name="EndDate"></param>
        /// <param name="OrgCode"></param>
        /// <param name="TypeId"></param>
        /// <param name="LineNum"></param>
        /// <returns></returns>
        [HttpGet("visit-org-satisfaction-detail")]
        public async Task<PagedDto<OrderVisitDetailDto>> VisitAndOrgSatisfactionDetail([FromQuery] VisitAndOrgSatisfactionDetailDto dto)
        {
            dto.EndDate = dto.EndDate.AddDays(1).AddSeconds(-1);


            var (total, items) = await _orderVisitDetailRepository.Queryable()
                .Includes(x => x.OrderVisit, o => o.Order, d => d.CallRecord)
                .Where(x => x.OrderVisit.VisitTime >= dto.StartDate && x.OrderVisit.VisitTime <= dto.EndDate && 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.StartsWith(dto.OrgCode))
                .WhereIF(dto.TypeId == 1, x => SqlFunc.JsonField(x.OrgProcessingResults, "Key") == dto.DateValue)
                .WhereIF(dto.TypeId == 2, x => SqlFunc.JsonField(x.OrgHandledAttitude, "Key") == dto.DateValue)
                .WhereIF(!string.IsNullOrEmpty(dto.LineNum), x => x.OrderVisit.Order.CallRecord.Gateway == dto.LineNum)
                .ToPagedListAsync(dto.PageIndex, dto.PageSize, HttpContext.RequestAborted);
            return new PagedDto<OrderVisitDetailDto>(total, _mapper.Map<IReadOnlyList<OrderVisitDetailDto>>(items));
        }




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

            CenterReportStatisticsDto centerReportStatisticsDto = new();

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

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

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

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

            centerReportStatisticsDto.CenterReportOrderSourceChannels = sourceChannel;
            #endregion

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

            centerReportStatisticsDto.CenterReportOrderAcceptTypes = acceptType;
            #endregion

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

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

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

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

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

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

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

            centerReportStatisticsDto.CenterReportVisitd = centerReportVisitd;
            #endregion

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

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

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

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

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

            return centerReportStatisticsDto;
        }

        /// <summary>
        /// 部门受理类型统计周期
        /// </summary>
        /// <param name="StartDate"></param>
        /// <param name="EndDate"></param>
        /// <param name="TypeCode">0:全部，1：中心，2：部门</param>
        /// <returns></returns>
        [HttpGet("department_acceptance_type_statistics")]
        public async Task<List<DepartmentAcceptanceTypeStatisticsDto>> DepartmentAcceptanceTypeStatistics(DateTime StartDate, DateTime EndDate, int TypeCode)
        {
            EndDate = EndDate.AddDays(1).AddSeconds(-1);

            var IsCenter = _sessionContext.OrgIsCenter;

            var orderData = await _orderRepository.Queryable()
              .Where(it => it.CreationTime >= StartDate && it.CreationTime <= EndDate && it.Status > EOrderStatus.WaitForAccept)
              .Select(it => new
              {
                  OrgCode = IsCenter == true ? it.ActualHandleOrgCode.Substring(SqlFunc.MappingColumn<int>("0"), SqlFunc.MappingColumn<int>("6")) : it.ActualHandleOrgCode.Substring(0, _sessionContext.RequiredOrgId.Length + 3),
                  it.AcceptTypeCode,
                  it.FileDurationWorkday,
                  it.AllDuration
              })
              .MergeTable()
              .LeftJoin<SystemOrganize>((it, o) => it.OrgCode == o.Id)
              .WhereIF(TypeCode == 1, (it, o) => it.OrgCode == "001")
              .WhereIF(TypeCode == 2, (it, o) => it.OrgCode != "001")
              .WhereIF(IsCenter == false, (it, o) => it.OrgCode.StartsWith(_sessionContext.RequiredOrgId))
               .GroupBy((it, o) => new
               {
                   it.OrgCode,
                   o.Name,
                   o.OrgType
               })
                .Select((it, o) => new DepartmentAcceptanceTypeStatisticsDto
                {
                    OrgName = it.OrgCode == "001" ? "热线中心" : o.Name,
                    OrgCode = it.OrgCode,
                    OrgType = (int)o.OrgType == 2 ? "区县部门" : "市直部门",
                    ZxAllCount = SqlFunc.AggregateSum(SqlFunc.IIF(it.AcceptTypeCode == "10", 1, 0)),
                    ZxAllTimes = SqlFunc.AggregateSum(SqlFunc.IIF(it.AcceptTypeCode == "10" && it.AllDuration != null, it.AllDuration, 0)),
                    ZxAcceptanceTypeCode = "10",

                    JyAllCount = SqlFunc.AggregateSum(SqlFunc.IIF(it.AcceptTypeCode == "15", 1, 0)),
                    JyAllTimes = SqlFunc.AggregateSum(SqlFunc.IIF(it.AcceptTypeCode == "15" && it.FileDurationWorkday != null, it.FileDurationWorkday, 0)),
                    JyAcceptanceTypeCode = "15",

                    QzAllCount = SqlFunc.AggregateSum(SqlFunc.IIF(it.AcceptTypeCode == "20", 1, 0)),
                    QzAllTimes = SqlFunc.AggregateSum(SqlFunc.IIF(it.AcceptTypeCode == "20" && it.FileDurationWorkday != null, it.FileDurationWorkday, 0)),
                    QzAcceptanceTypeCode = "20",

                    ByAllCount = SqlFunc.AggregateSum(SqlFunc.IIF(it.AcceptTypeCode == "25", 1, 0)),
                    ByAllTimes = SqlFunc.AggregateSum(SqlFunc.IIF(it.AcceptTypeCode == "25" && it.FileDurationWorkday != null, it.FileDurationWorkday, 0)),
                    ByAcceptanceTypeCode = "25",

                    JbAllCount = SqlFunc.AggregateSum(SqlFunc.IIF(it.AcceptTypeCode == "30", 1, 0)),
                    JbAllTimes = SqlFunc.AggregateSum(SqlFunc.IIF(it.AcceptTypeCode == "30" && it.FileDurationWorkday != null, it.FileDurationWorkday, 0)),
                    JbAcceptanceTypeCode = "30",

                    TsAllCount = SqlFunc.AggregateSum(SqlFunc.IIF(it.AcceptTypeCode == "35", 1, 0)),
                    TsAllTimes = SqlFunc.AggregateSum(SqlFunc.IIF(it.AcceptTypeCode == "35" && it.FileDurationWorkday != null, it.FileDurationWorkday, 0)),
                    TsAcceptanceTypeCode = "35",

                    QtAllCount = SqlFunc.AggregateSum(SqlFunc.IIF(it.AcceptTypeCode == "40", 1, 0)),
                    QtAllTimes = SqlFunc.AggregateSum(SqlFunc.IIF(it.AcceptTypeCode == "40" && it.FileDurationWorkday != null, it.FileDurationWorkday, 0)),
                    QtAcceptanceTypeCode = "40",

                    YjAllCount = SqlFunc.AggregateSum(SqlFunc.IIF(it.AcceptTypeCode == "1", 1, 0)),
                    YjAllTimes = SqlFunc.AggregateSum(SqlFunc.IIF(it.AcceptTypeCode == "1" && it.FileDurationWorkday != null, it.FileDurationWorkday, 0)),
                    YjAcceptanceTypeCode = "1"
                })
              .ToListAsync();


            return orderData;
        }

        /// <summary>
        /// 部门受理类型统计周期---子级
        /// </summary>
        /// <param name="StartDate"></param>
        /// <param name="EndDate"></param>
        /// <param name="TypeCode">0:全部，1：中心，2：部门</param>
        /// <param name="OrgCode"></param>
        /// <returns></returns>
        [HttpGet("department_acceptance_type_child_statistics")]
        public async Task<object> DepartmentAcceptanceTypeChildStatistics(DateTime StartDate, DateTime EndDate, int TypeCode, string OrgCode)
        {
            EndDate = EndDate.AddDays(1).AddSeconds(-1);

            // var IsCenter = _sessionContext.OrgIsCenter;

            var orderData = await _orderRepository.Queryable()
              .Where(it => it.CreationTime >= StartDate && it.CreationTime <= EndDate && it.Status > EOrderStatus.WaitForAccept)// && it.Status >= EOrderStatus.Filed
              .WhereIF(OrgCode == "001", it => it.ActualHandleOrgCode == OrgCode)
              .WhereIF(OrgCode != "001", it => it.ActualHandleOrgCode.StartsWith(OrgCode))

              .Select(it => new
              {
                  // OrgCode = OrgCode == "001" ? OrgCode : it.ActualHandleOrgCode.Substring(0, OrgCode.Length + 3),
                  OrgCode = it.ActualHandleOrgCode == "001" ? it.ActualHandleOrgCode : it.ActualHandleOrgCode.Substring(0, OrgCode.Length + 3),
                  it.AcceptTypeCode,
                  it.FileDurationWorkday,
                  it.AllDuration
              })
              .MergeTable()
              .LeftJoin<SystemOrganize>((it, o) => it.OrgCode == o.Id)

              .WhereIF(TypeCode == 1, (it, o) => it.OrgCode == "001")
              .WhereIF(TypeCode == 2, (it, o) => it.OrgCode != "001")
               //.WhereIF(IsCenter == false, (it, o) => it.OrgCode.StartsWith(_sessionContext.RequiredOrgId))

               .GroupBy((it, o) => new
               {
                   it.OrgCode,
                   o.Name,
                   o.OrgType
               })
                .Select((it, o) => new DepartmentAcceptanceTypeStatisticsDto
                {
                    OrgName = it.OrgCode == "001" ? "热线中心" : o.Name,
                    OrgCode = it.OrgCode,
                    OrgType = (int)o.OrgType == 2 ? "区县部门" : "市直部门",
                    ZxAllCount = SqlFunc.AggregateSum(SqlFunc.IIF(it.AcceptTypeCode == "10", 1, 0)),
                    ZxAllTimes = SqlFunc.AggregateSum(SqlFunc.IIF(it.AcceptTypeCode == "10" && it.AllDuration != null, it.AllDuration, 0)),
                    ZxAcceptanceTypeCode = "10",

                    JyAllCount = SqlFunc.AggregateSum(SqlFunc.IIF(it.AcceptTypeCode == "15", 1, 0)),
                    JyAllTimes = SqlFunc.AggregateSum(SqlFunc.IIF(it.AcceptTypeCode == "15" && it.FileDurationWorkday != null, it.FileDurationWorkday, 0)),
                    JyAcceptanceTypeCode = "15",

                    QzAllCount = SqlFunc.AggregateSum(SqlFunc.IIF(it.AcceptTypeCode == "20", 1, 0)),
                    QzAllTimes = SqlFunc.AggregateSum(SqlFunc.IIF(it.AcceptTypeCode == "20" && it.FileDurationWorkday != null, it.FileDurationWorkday, 0)),
                    QzAcceptanceTypeCode = "20",

                    ByAllCount = SqlFunc.AggregateSum(SqlFunc.IIF(it.AcceptTypeCode == "25", 1, 0)),
                    ByAllTimes = SqlFunc.AggregateSum(SqlFunc.IIF(it.AcceptTypeCode == "25" && it.FileDurationWorkday != null, it.FileDurationWorkday, 0)),
                    ByAcceptanceTypeCode = "25",

                    JbAllCount = SqlFunc.AggregateSum(SqlFunc.IIF(it.AcceptTypeCode == "30", 1, 0)),
                    JbAllTimes = SqlFunc.AggregateSum(SqlFunc.IIF(it.AcceptTypeCode == "30" && it.FileDurationWorkday != null, it.FileDurationWorkday, 0)),
                    JbAcceptanceTypeCode = "30",

                    TsAllCount = SqlFunc.AggregateSum(SqlFunc.IIF(it.AcceptTypeCode == "35", 1, 0)),
                    TsAllTimes = SqlFunc.AggregateSum(SqlFunc.IIF(it.AcceptTypeCode == "35" && it.FileDurationWorkday != null, it.FileDurationWorkday, 0)),
                    TsAcceptanceTypeCode = "35",

                    QtAllCount = SqlFunc.AggregateSum(SqlFunc.IIF(it.AcceptTypeCode == "40", 1, 0)),
                    QtAllTimes = SqlFunc.AggregateSum(SqlFunc.IIF(it.AcceptTypeCode == "40" && it.FileDurationWorkday != null, it.FileDurationWorkday, 0)),
                    QtAcceptanceTypeCode = "40"
                })
              .ToListAsync();

            var sumModel = new DepartmentAcceptanceTypeStatisticsDto
            {
                OrgName = "合计",
                ZxAllCount = orderData.Sum(x => x.ZxAllCount),
                ZxAllTimes = orderData.Sum(x => x.ZxAllTimes),
                ZxAcceptanceTypeCode = "10",

                JyAllCount = orderData.Sum(x => x.JyAllCount),
                JyAllTimes = orderData.Sum(x => x.JyAllTimes),
                JyAcceptanceTypeCode = "15",

                QzAllCount = orderData.Sum(x => x.QzAllCount),
                QzAllTimes = orderData.Sum(x => x.QzAllTimes),
                QzAcceptanceTypeCode = "20",

                ByAllCount = orderData.Sum(x => x.ByAllCount),
                ByAllTimes = orderData.Sum(x => x.ByAllTimes),
                ByAcceptanceTypeCode = "25",

                JbAllCount = orderData.Sum(x => x.JbAllCount),
                JbAllTimes = orderData.Sum(x => x.JbAllTimes),
                JbAcceptanceTypeCode = "30",

                TsAllCount = orderData.Sum(x => x.TsAllCount),
                TsAllTimes = orderData.Sum(x => x.TsAllTimes),
                TsAcceptanceTypeCode = "35",

                QtAllCount = orderData.Sum(x => x.QtAllCount),
                QtAllTimes = orderData.Sum(x => x.QtAllTimes),
                QtAcceptanceTypeCode = "40"
            };
            return new { DataList = orderData, SumModel = sumModel };
        }


        /// <summary>
        /// 部门受理类型统计周期--明细列表
        /// </summary>
        /// <param name="dto"></param>
        /// <returns></returns>
        [HttpGet("department_acceptance_type_order_list")]
        public async Task<PagedDto<OrderDto>> DepartmentAcceptanceTypeOrderList([FromQuery] DepartmentKeyWordRequest dto)
        {
            dto.EndDate = dto.EndDate.AddDays(1).AddSeconds(-1);
            var (total, items) = await _orderRepository.Queryable()
                .Where(p => p.CreationTime >= dto.StartDate && p.CreationTime <= dto.EndDate && p.Status > EOrderStatus.WaitForAccept)//&& p.Status >= EOrderStatus.Filed
                .WhereIF(!string.IsNullOrEmpty(dto.OrgCode) && dto.OrgCode == "001", p => p.ActualHandleOrgCode == dto.OrgCode)
                .WhereIF(!string.IsNullOrEmpty(dto.OrgCode) && dto.OrgCode != "001" && _sessionContext.RequiredOrgId != dto.OrgCode, p => p.ActualHandleOrgCode.StartsWith(dto.OrgCode))
                .WhereIF(!string.IsNullOrEmpty(dto.OrgCode) && dto.OrgCode != "001" && _sessionContext.RequiredOrgId == dto.OrgCode, p => p.ActualHandleOrgCode == dto.OrgCode)
                .WhereIF(!string.IsNullOrEmpty(dto.AcceptTypeCode), p => p.AcceptTypeCode == dto.AcceptTypeCode)
                .WhereIF(dto.TypeCode == 1, p => p.ActualHandleOrgCode == "001")
                .WhereIF(dto.TypeCode == 2, p => p.ActualHandleOrgCode != "001")
                .OrderByDescending(d => d.CreationTime)
                .ToPagedListAsync(dto, HttpContext.RequestAborted);

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

        /// <summary>
        /// 部门办件统计表
        /// </summary>
        /// <param name="StartDate"></param>
        /// <param name="EndDate"></param>
        /// <param name="OrgName"></param>
        /// <returns></returns>
        [HttpGet("departmental_processing_statistics")]
        public async Task<object> DepartmentalProcessingStatistics(DateTime StartDate, DateTime EndDate, string? OrgName)
        {
            EndDate = EndDate.AddDays(1).AddSeconds(-1);
            var IsCenter = _sessionContext.OrgIsCenter;

            //工单
            #region 工单
            var queryOrder = _orderRepository.Queryable()
                         .Where(it => it.CreationTime >= StartDate && it.CreationTime <= EndDate && it.Status > EOrderStatus.WaitForAccept)
                         .Select(it => new
                         {
                             it.Id,
                             OrgCode = IsCenter == true ? it.ActualHandleOrgCode.Substring(SqlFunc.MappingColumn<int>("0"), SqlFunc.MappingColumn<int>("6")) : it.ActualHandleOrgCode.Substring(0, _sessionContext.RequiredOrgId.Length + 3),
                             it.ActualHandleOrgCode,
                             it.Status,//工单状态
                             it.ExpiredTime,//期满时间
                             it.ActualHandleTime,//办理时间
                             it.CounterSignType,//会签
                         })
                        .MergeTable()
                         .WhereIF(IsCenter == false, it => it.OrgCode.StartsWith(_sessionContext.RequiredOrgId))
                         .GroupBy(it => new
                         {
                             it.OrgCode
                         })
                         .Select(it => new DepartmentalProcessingStatisticsDataDto
                         {
                             OrgCode = it.OrgCode,
                             OrderCountNum = SqlFunc.AggregateCount(it.OrgCode),//总量
                             YBOrderCountNum = SqlFunc.AggregateSum(SqlFunc.IIF(it.Status >= EOrderStatus.Filed, 1, 0)),//已办
                             ZBOrderCountNum = SqlFunc.AggregateSum(SqlFunc.IIF(it.Status < EOrderStatus.Filed, 1, 0)),//在办
                             Archived = SqlFunc.AggregateSum(SqlFunc.IIF(it.Status >= EOrderStatus.Filed, 1, 0)),//已归档
                             ToBeArchived = 0,
                             WaitPublished = 0,
                             PublishedOpen = 0,
                             PublishedNoOpen = 0,
                             YBOverdue = SqlFunc.AggregateSum(SqlFunc.IIF(it.Status >= EOrderStatus.Filed && it.ActualHandleTime > it.ExpiredTime, 1, 0)),//已办超期
                             ZBOverdue = SqlFunc.AggregateSum(SqlFunc.IIF(it.Status < EOrderStatus.Filed && it.ExpiredTime < SqlFunc.GetDate(), 1, 0)),//待办超期
                             CompleteOnTime= SqlFunc.AggregateSum(SqlFunc.IIF(it.Status >= EOrderStatus.Filed && it.ActualHandleTime <= it.ExpiredTime, 1, 0)),//按时办结
                             HQYBOverdue = 0,
                             HQZBOverdue = 0,
                             DelayEnd = 0,
                             DelayWait = 0,
                             OrderDelayCount = 0,
                             ScreenCount = 0,
                             ScreenApproval = 0,
                             ScreenPass = 0,
                             ScreenNotPass = 0,
                             SatisfactionCount = 0,
                             NotSatisfactionCount = 0
                         })
                         .MergeTable();
            #endregion

            #region 发布
            //发布
            var queryPublish = _orderPublishRepository.Queryable()
                .LeftJoin<Order>((x, o) => x.OrderId == o.Id)
                .Where((x, o) => x.CreationTime >= StartDate && x.CreationTime <= EndDate)
                .Select((x, o) => new
                {
                    o.Id,
                    OrgCode = IsCenter == true ? o.ActualHandleOrgCode.Substring(SqlFunc.MappingColumn<int>("0"), SqlFunc.MappingColumn<int>("6")) : o.ActualHandleOrgCode.Substring(0, _sessionContext.RequiredOrgId.Length + 3),
                    PublishState = x.PublishState,
                    Status = o.Status
                })
                .MergeTable()
                .WhereIF(IsCenter == false, it => it.OrgCode.StartsWith(_sessionContext.RequiredOrgId))
                .GroupBy(it => new { it.OrgCode })
                .Select(it => new DepartmentalProcessingStatisticsDataDto
                {
                    OrgCode = it.OrgCode,
                    OrderCountNum = 0,//总量
                    YBOrderCountNum = 0,//已办
                    ZBOrderCountNum = 0,//在办
                    Archived = 0,
                    ToBeArchived = 0,
                    WaitPublished = SqlFunc.AggregateSum(SqlFunc.IIF(it.Status == EOrderStatus.Filed, 1, 0)),//待发布  --已归档的就是待发布
                    PublishedOpen = SqlFunc.AggregateSum(SqlFunc.IIF(it.Status >= EOrderStatus.Published && it.PublishState, 1, 0)),//已发布公开
                    PublishedNoOpen = SqlFunc.AggregateSum(SqlFunc.IIF(it.Status >= EOrderStatus.Published && !it.PublishState, 1, 0)),//已发布不公开
                    YBOverdue = 0,
                    ZBOverdue = 0,
                    CompleteOnTime = 0,
                    HQYBOverdue = 0,
                    HQZBOverdue = 0,
                    DelayEnd = 0,
                    DelayWait = 0,
                    OrderDelayCount = 0,
                    ScreenCount = 0,
                    ScreenApproval = 0,
                    ScreenPass = 0,
                    ScreenNotPass = 0,
                    SatisfactionCount = 0,
                    NotSatisfactionCount = 0,
                })
                .MergeTable();
            #endregion

            #region 会签(已办超期、待办超期)
            //会签(已办超期、待办超期)
            var queryCountersign = _workflowStepHandleRepository.Queryable()
             .LeftJoin<WorkflowTrace>((x, o) => x.WorkflowStepId == o.StepId)
             .Where((x, o) => o.ModuleCode == WorkflowModuleConsts.OrderHandle && o.CountersignPosition == ECountersignPosition.Multi && o.CountersignPosition == ECountersignPosition.Single && o.CreationTime >= StartDate && o.CreationTime <= EndDate)
             .Select((x, o) => new
             {
                 OrgCode = IsCenter == true ? x.OrgId.Substring(SqlFunc.MappingColumn<int>("0"), SqlFunc.MappingColumn<int>("6")) : x.OrgId.Substring(0, _sessionContext.RequiredOrgId.Length + 3),// d.OrgId,
                 HandleTime = o.HandleTime,
                 StepExpiredTime = o.StepExpiredTime,
                 Status = o.Status
             })
              .MergeTable()
              .WhereIF(IsCenter == false, it => it.OrgCode.StartsWith(_sessionContext.RequiredOrgId))
             .GroupBy(d => new { d.OrgCode })
             .Select(d => new DepartmentalProcessingStatisticsDataDto
             {
                 OrgCode = d.OrgCode,
                 OrderCountNum = 0,//总量
                 YBOrderCountNum = 0,//已办
                 ZBOrderCountNum = 0,//在办
                 Archived = 0,
                 ToBeArchived = 0,
                 WaitPublished = 0,
                 PublishedOpen = 0,
                 PublishedNoOpen = 0,
                 YBOverdue = 0,
                 ZBOverdue = 0,
                 CompleteOnTime = 0,
                 HQYBOverdue = SqlFunc.AggregateSum(SqlFunc.IIF(d.Status == EWorkflowStepStatus.Handled && d.HandleTime > d.StepExpiredTime, 1, 0)),
                 HQZBOverdue = SqlFunc.AggregateSum(SqlFunc.IIF(d.Status != EWorkflowStepStatus.Handled && DateTime.Now >= d.StepExpiredTime, 1, 0)),
                 DelayEnd = SqlFunc.AggregateSum(SqlFunc.IIF(d.Status == EWorkflowStepStatus.Handled, 1, 0)),
                 DelayWait = SqlFunc.AggregateSum(SqlFunc.IIF(d.Status != EWorkflowStepStatus.Handled, 1, 0)),
                 OrderDelayCount = 0,
                 ScreenCount = 0,
                 ScreenApproval = 0,
                 ScreenPass = 0,
                 ScreenNotPass = 0,
                 SatisfactionCount = 0,
                 NotSatisfactionCount = 0,
             })
            .MergeTable();
            #endregion

            #region 延期
            //延期
            var orderDelay = _orderDelayRepository.Queryable()
                .Where(x => x.CreationTime >= StartDate&& x.CreationTime <= EndDate)
                .Select(x => new
                {
                    OrgCode = IsCenter == true ? x.ApplyOrgCode.Substring(SqlFunc.MappingColumn<int>("0"), SqlFunc.MappingColumn<int>("6")) : x.ApplyOrgCode.Substring(0, _sessionContext.RequiredOrgId.Length + 3),
                    x.DelayState
                })
                .MergeTable()
                .WhereIF(IsCenter == false, it => it.OrgCode.StartsWith(_sessionContext.RequiredOrgId))
                .GroupBy(x => new { x.OrgCode })
                .Select(x => new DepartmentalProcessingStatisticsDataDto
                {
                    OrgCode = x.OrgCode,
                    OrderCountNum = 0,//总量
                    YBOrderCountNum = 0,//已办
                    ZBOrderCountNum = 0,//在办
                    Archived = 0,
                    ToBeArchived = 0,
                    WaitPublished = 0,
                    PublishedOpen = 0,
                    PublishedNoOpen = 0,
                    YBOverdue = 0,
                    ZBOverdue = 0,
                    CompleteOnTime = 0,
                    HQYBOverdue = 0,
                    HQZBOverdue = 0,
                    DelayEnd = 0,
                    DelayWait = 0,
                    OrderDelayCount = SqlFunc.AggregateSum(SqlFunc.IIF(x.DelayState != EDelayState.Withdraw, 1, 0)),
                    ScreenCount = 0,
                    ScreenApproval = 0,
                    ScreenPass = 0,
                    ScreenNotPass = 0,
                    SatisfactionCount = 0,
                    NotSatisfactionCount = 0,
                })
                 .MergeTable();
            #endregion

            #region 甄别
            //甄别
            var orderScreen = _orderScreenRepository.Queryable()
                .Where(x => x.CreationTime >= StartDate&& x.CreationTime <= EndDate)
                .WhereIF(!string.IsNullOrEmpty(OrgName), x => x.CreatorOrgId.Contains(OrgName))
                .Select(x => new
                {
                    x.Id,
                    OrgCode = IsCenter == true ? x.CreatorOrgId.Substring(SqlFunc.MappingColumn<int>("0"), SqlFunc.MappingColumn<int>("6")) : x.CreatorOrgId.Substring(0, _sessionContext.RequiredOrgId.Length + 3),
                    Status = x.Status
                })
                .MergeTable()
                .WhereIF(IsCenter == false, it => it.OrgCode.StartsWith(_sessionContext.RequiredOrgId))
                .GroupBy(x => new { x.OrgCode })
                .Select(x => new DepartmentalProcessingStatisticsDataDto
                {
                    OrgCode = x.OrgCode,
                    OrderCountNum = 0,//总量
                    YBOrderCountNum = 0,//已办
                    ZBOrderCountNum = 0,//在办
                    Archived = 0,
                    ToBeArchived = 0,
                    WaitPublished = 0,
                    PublishedOpen = 0,
                    PublishedNoOpen = 0,
                    YBOverdue = 0,
                    ZBOverdue = 0,
                    CompleteOnTime = 0,
                    HQYBOverdue = 0,
                    HQZBOverdue = 0,
                    DelayEnd = 0,
                    DelayWait = 0,
                    OrderDelayCount = 0,
                    ScreenCount = SqlFunc.AggregateSum(SqlFunc.IIF(x.Id != null, 1, 0)),
                    ScreenApproval = SqlFunc.AggregateSum(SqlFunc.IIF(x.Status == EScreenStatus.Approval || x.Status == EScreenStatus.Apply, 1, 0)),
                    ScreenPass = SqlFunc.AggregateSum(SqlFunc.IIF(x.Status == EScreenStatus.End, 1, 0)),
                    ScreenNotPass = SqlFunc.AggregateSum(SqlFunc.IIF(x.Status == EScreenStatus.Refuse, 1, 0)),
                    SatisfactionCount = 0,
                    NotSatisfactionCount = 0,
                })
                .MergeTable();
            #endregion

            #region 满意度
            //满意度
            var orderVisit = _orderVisitDetailRepository.Queryable()
            .Where(x => x.OrderVisit.VisitTime >= StartDate && x.OrderVisit.VisitTime <= EndDate && x.VisitTarget == EVisitTarget.Org && x.OrderVisit.VisitState == EVisitState.Visited && !string.IsNullOrEmpty(x.VisitOrgCode))
            .Select(x => new
            {
                OrgCode = IsCenter == true ? x.VisitOrgCode.Substring(SqlFunc.MappingColumn<int>("0"), SqlFunc.MappingColumn<int>("6")) : x.VisitOrgCode.Substring(0, _sessionContext.RequiredOrgId.Length + 3),
                x.OrgProcessingResults
            })
            .MergeTable()
            .WhereIF(IsCenter == false, it => it.OrgCode.StartsWith(_sessionContext.RequiredOrgId))
            .GroupBy(x => new { x.OrgCode })
            .Select(x => new DepartmentalProcessingStatisticsDataDto
            {
                OrgCode = x.OrgCode,
                OrderCountNum = 0,//总量
                YBOrderCountNum = 0,//已办
                ZBOrderCountNum = 0,//在办
                Archived = 0,
                ToBeArchived = 0,
                WaitPublished = 0,
                PublishedOpen = 0,
                PublishedNoOpen = 0,
                YBOverdue = 0,
                ZBOverdue = 0,
                CompleteOnTime = 0,
                HQYBOverdue = 0,
                HQZBOverdue = 0,
                DelayEnd = 0,
                DelayWait = 0,
                OrderDelayCount = 0,
                ScreenCount = 0,
                ScreenApproval = 0,
                ScreenPass = 0,
                ScreenNotPass = 0,
                SatisfactionCount = SqlFunc.AggregateSum(SqlFunc.IIF(SqlFunc.JsonField(x.OrgProcessingResults, "Key") != "2", 1, 0)),
                NotSatisfactionCount = SqlFunc.AggregateSum(SqlFunc.IIF(SqlFunc.JsonField(x.OrgProcessingResults, "Key") == "2", 1, 0))
            })
          .MergeTable();
            #endregion

            //联合查询
            var items = await _orderRepository.OrderUnionAll(queryOrder, queryPublish, queryCountersign, orderDelay, orderScreen, orderVisit)
                 .LeftJoin<SystemOrganize>((p, o) => p.OrgCode == o.Id)
                .Select((p, o) => new DepartmentalProcessingStatisticsDataDto
                {
                    OrgCode = p.OrgCode,
                    OrgName = o.Name,
                    OrgType = o.OrgType,
                    OrderCountNum = p.OrderCountNum,
                    YBOrderCountNum = p.YBOrderCountNum,
                    ZBOrderCountNum = p.ZBOrderCountNum,
                    Archived = p.Archived,
                    ToBeArchived = p.ToBeArchived,
                    WaitPublished = p.WaitPublished,
                    PublishedOpen = p.PublishedOpen,
                    PublishedNoOpen = p.PublishedNoOpen,
                    YBOverdue = p.YBOverdue,
                    ZBOverdue = p.ZBOverdue,
                    CompleteOnTime = p.CompleteOnTime,
                    HQYBOverdue = p.HQYBOverdue,
                    HQZBOverdue = p.HQZBOverdue,
                    DelayEnd = p.DelayEnd,
                    DelayWait = p.DelayWait,
                    OrderDelayCount = p.OrderDelayCount,
                    ScreenCount = p.ScreenCount,
                    ScreenApproval = p.ScreenApproval,
                    ScreenPass = p.ScreenPass,
                    ScreenNotPass = p.ScreenNotPass,
                    SatisfactionCount = p.SatisfactionCount,
                    NotSatisfactionCount = p.NotSatisfactionCount
                })
                .MergeTable()
                .WhereIF(string.IsNullOrEmpty(OrgName) == false, x => x.OrgName.Contains(OrgName))
                .ToListAsync();

            var total = new DepartmentalProcessingStatisticsDataDto
            {
                OrgCode = "",
                OrgName = "合计",
                OrgType = EOrgType.City,
                OrderCountNum = items.Sum(p => p.OrderCountNum),
                YBOrderCountNum = items.Sum(p => p.YBOrderCountNum),
                ZBOrderCountNum = items.Sum(p => p.ZBOrderCountNum),
                Archived = items.Sum(p => p.Archived),
                ToBeArchived = items.Sum(p => p.ToBeArchived),
                WaitPublished = items.Sum(p => p.WaitPublished),
                PublishedOpen = items.Sum(p => p.PublishedOpen),
                PublishedNoOpen = items.Sum(p => p.PublishedNoOpen),
                YBOverdue = items.Sum(p => p.YBOverdue),
                ZBOverdue = items.Sum(p => p.ZBOverdue),
                CompleteOnTime=items.Sum(p=>p.CompleteOnTime),
                HQYBOverdue = items.Sum(p => p.HQYBOverdue),
                HQZBOverdue = items.Sum(p => p.HQZBOverdue),
                DelayEnd = items.Sum(p => p.DelayEnd),
                DelayWait = items.Sum(p => p.DelayWait),
                OrderDelayCount = items.Sum(p => p.OrderDelayCount),
                ScreenCount = items.Sum(p => p.ScreenCount),
                ScreenApproval = items.Sum(p => p.ScreenApproval),
                ScreenPass = items.Sum(p => p.ScreenPass),
                ScreenNotPass = items.Sum(p => p.ScreenNotPass),
                SatisfactionCount = items.Sum(p => p.SatisfactionCount),
                NotSatisfactionCount = items.Sum(p => p.NotSatisfactionCount)
            };
            return new { List = items, Total = total };
        }

        /// <summary>
        /// 部门办件统计表--子级
        /// </summary>
        /// <param name="StartDate"></param>
        /// <param name="EndDate"></param>
        /// <param name="OrgCode"></param>
        /// <param name="OrgName"></param>
        /// <returns></returns>
        [HttpGet("departmental_processing_child_statistics")]
        public async Task<object> DepartmentalProcessingChildStatistics(DateTime StartDate, DateTime EndDate, string OrgCode, string? OrgName)
        {
            EndDate = EndDate.AddDays(1).AddSeconds(-1);

            #region 工单
            //工单
            var queryOrder = _orderRepository.Queryable()
                      .Where(it => it.CreationTime >= StartDate && it.CreationTime <= EndDate && it.Status > EOrderStatus.WaitForAccept)
                      .WhereIF(OrgCode == "001", it => it.ActualHandleOrgCode == OrgCode)
                      .WhereIF(OrgCode != "001", it => it.ActualHandleOrgCode.StartsWith(OrgCode))
                      .Select(it => new
                      {
                          it.Id,
                          OrgCode = it.ActualHandleOrgCode.Substring(0, OrgCode.Length + 3),
                          it.ActualHandleOrgCode,
                          it.Status,//工单状态
                          it.ExpiredTime,//期满时间
                          it.ActualHandleTime,//办理时间
                          it.CounterSignType,//会签
                      })
                     .MergeTable()
                      .GroupBy(it => new
                      {
                          it.OrgCode
                      })
                      .Select(it => new DepartmentalProcessingStatisticsDataDto
                      {
                          OrgCode = it.OrgCode,
                          OrderCountNum = SqlFunc.AggregateCount(it.OrgCode),//总量
                          YBOrderCountNum = SqlFunc.AggregateSum(SqlFunc.IIF(it.Status >= EOrderStatus.Filed, 1, 0)),//已办
                          ZBOrderCountNum = SqlFunc.AggregateSum(SqlFunc.IIF(it.Status < EOrderStatus.Filed, 1, 0)),//在办
                          Archived = SqlFunc.AggregateSum(SqlFunc.IIF(it.Status >= EOrderStatus.Filed, 1, 0)),//已归档
                          ToBeArchived = 0,
                          WaitPublished = 0,
                          PublishedOpen = 0,
                          PublishedNoOpen = 0,
                          YBOverdue = SqlFunc.AggregateSum(SqlFunc.IIF(it.Status >= EOrderStatus.Filed && it.ActualHandleTime > it.ExpiredTime, 1, 0)),//已办超期
                          ZBOverdue = SqlFunc.AggregateSum(SqlFunc.IIF(it.Status < EOrderStatus.Filed && it.ExpiredTime < SqlFunc.GetDate(), 1, 0)),//待办超期
                          CompleteOnTime = SqlFunc.AggregateSum(SqlFunc.IIF(it.Status >= EOrderStatus.Filed && it.ActualHandleTime <= it.ExpiredTime, 1, 0)),//按时办结
                          HQYBOverdue = 0,
                          HQZBOverdue = 0,
                          DelayEnd = 0,
                          DelayWait = 0,
                          OrderDelayCount = 0,
                          ScreenCount = 0,
                          ScreenApproval = 0,
                          ScreenPass = 0,
                          ScreenNotPass = 0,
                          SatisfactionCount = 0,
                          NotSatisfactionCount = 0
                      })
                      .MergeTable();
            #endregion

            #region 发布
            //发布
            var queryPublish = _orderPublishRepository.Queryable()
                .LeftJoin<Order>((x, o) => x.OrderId == o.Id)
                .Where((x, o) => x.CreationTime >= StartDate && x.CreationTime <= EndDate)
                .WhereIF(OrgCode == "001", (x, o) => o.ActualHandleOrgCode == OrgCode)
                .WhereIF(OrgCode != "001", (x, o) => o.ActualHandleOrgCode.StartsWith(OrgCode))
                .Select((x, o) => new
                {
                    o.Id,
                    OrgCode = o.ActualHandleOrgCode.Substring(0, OrgCode.Length + 3),
                    PublishState = x.PublishState,
                    Status = o.Status
                })
                .MergeTable()
                .GroupBy(it => new { it.OrgCode })
                .Select(it => new DepartmentalProcessingStatisticsDataDto
                {
                    OrgCode = it.OrgCode,
                    OrderCountNum = 0,//总量
                    YBOrderCountNum = 0,//已办
                    ZBOrderCountNum = 0,//在办
                    Archived = 0,
                    ToBeArchived = 0,
                    WaitPublished = SqlFunc.AggregateSum(SqlFunc.IIF(it.Status == EOrderStatus.Filed, 1, 0)),//待发布  --已归档的就是待发布
                    PublishedOpen = SqlFunc.AggregateSum(SqlFunc.IIF(it.Status >= EOrderStatus.Published && it.PublishState, 1, 0)),//已发布公开
                    PublishedNoOpen = SqlFunc.AggregateSum(SqlFunc.IIF(it.Status >= EOrderStatus.Published && !it.PublishState, 1, 0)),//已发布不公开
                    YBOverdue = 0,
                    ZBOverdue = 0,
                    CompleteOnTime =0,
                    HQYBOverdue = 0,
                    HQZBOverdue = 0,
                    DelayEnd = 0,
                    DelayWait = 0,
                    OrderDelayCount = 0,
                    ScreenCount = 0,
                    ScreenApproval = 0,
                    ScreenPass = 0,
                    ScreenNotPass = 0,
                    SatisfactionCount = 0,
                    NotSatisfactionCount = 0,
                })
                .MergeTable();
            #endregion

            #region 会签(已办超期、待办超期)
            //会签(已办超期、待办超期)
            var queryCountersign = _workflowStepHandleRepository.Queryable()
             .LeftJoin<WorkflowTrace>((x, o) => x.WorkflowStepId == o.StepId)
             .Where((x, o) => o.ModuleCode == WorkflowModuleConsts.OrderHandle && o.CountersignPosition == ECountersignPosition.Multi && o.CountersignPosition == ECountersignPosition.Single && o.CreationTime >= StartDate && o.CreationTime <= EndDate)
             .WhereIF(OrgCode == "001", (x, o) => x.OrgId == OrgCode)
             .WhereIF(OrgCode != "001", (x, o) => x.OrgId.StartsWith(OrgCode))
             .Select((x, o) => new
             {
                 OrgCode = x.OrgId.Substring(0, OrgCode.Length + 3),
                 HandleTime = o.HandleTime,
                 StepExpiredTime = o.StepExpiredTime,
                 Status = o.Status
             })
              .MergeTable()
             .GroupBy(d => new { d.OrgCode })
             .Select(d => new DepartmentalProcessingStatisticsDataDto
             {
                 OrgCode = d.OrgCode,
                 OrderCountNum = 0,//总量
                 YBOrderCountNum = 0,//已办
                 ZBOrderCountNum = 0,//在办
                 Archived = 0,
                 ToBeArchived = 0,
                 WaitPublished = 0,
                 PublishedOpen = 0,
                 PublishedNoOpen = 0,
                 YBOverdue = 0,
                 ZBOverdue = 0,
                 CompleteOnTime = 0,
                 HQYBOverdue = SqlFunc.AggregateSum(SqlFunc.IIF(d.Status == EWorkflowStepStatus.Handled && d.HandleTime > d.StepExpiredTime, 1, 0)),
                 HQZBOverdue = SqlFunc.AggregateSum(SqlFunc.IIF(d.Status != EWorkflowStepStatus.Handled && DateTime.Now >= d.StepExpiredTime, 1, 0)),
                 DelayEnd = SqlFunc.AggregateSum(SqlFunc.IIF(d.Status == EWorkflowStepStatus.Handled, 1, 0)),
                 DelayWait = SqlFunc.AggregateSum(SqlFunc.IIF(d.Status != EWorkflowStepStatus.Handled, 1, 0)),
                 OrderDelayCount = 0,
                 ScreenCount = 0,
                 ScreenApproval = 0,
                 ScreenPass = 0,
                 ScreenNotPass = 0,
                 SatisfactionCount = 0,
                 NotSatisfactionCount = 0,
             })
            .MergeTable();
            #endregion

            #region 延期
            //延期
            var orderDelay = _orderDelayRepository.Queryable()
                .Where(x => x.CreationTime >= StartDate&&  x.CreationTime <= EndDate)
                .WhereIF(OrgCode == "001", x => x.ApplyOrgCode == OrgCode)
                .WhereIF(OrgCode != "001", x => x.ApplyOrgCode.StartsWith(OrgCode))
                .Select(x => new
                {
                    OrgCode = x.ApplyOrgCode.Substring(0, OrgCode.Length + 3),
                    x.DelayState
                })
                .MergeTable()
                .GroupBy(x => new { x.OrgCode })
                .Select(x => new DepartmentalProcessingStatisticsDataDto
                {
                    OrgCode = x.OrgCode,
                    OrderCountNum = 0,//总量
                    YBOrderCountNum = 0,//已办
                    ZBOrderCountNum = 0,//在办
                    Archived = 0,
                    ToBeArchived = 0,
                    WaitPublished = 0,
                    PublishedOpen = 0,
                    PublishedNoOpen = 0,
                    YBOverdue = 0,
                    ZBOverdue = 0,
                    CompleteOnTime = 0,
                    HQYBOverdue = 0,
                    HQZBOverdue = 0,
                    DelayEnd = 0,
                    DelayWait = 0,
                    OrderDelayCount = SqlFunc.AggregateSum(SqlFunc.IIF(x.DelayState != EDelayState.Withdraw, 1, 0)),
                    ScreenCount = 0,
                    ScreenApproval = 0,
                    ScreenPass = 0,
                    ScreenNotPass = 0,
                    SatisfactionCount = 0,
                    NotSatisfactionCount = 0,
                })
                 .MergeTable();
            #endregion

            #region 甄别
            //甄别
            var orderScreen = _orderScreenRepository.Queryable()
                .Where(x => x.CreationTime >= StartDate&& x.CreationTime <= EndDate)
                .WhereIF(!string.IsNullOrEmpty(OrgName), x => x.CreatorOrgId.Contains(OrgName))
                  .WhereIF(OrgCode == "001", x => x.CreatorOrgId == OrgCode)
                .WhereIF(OrgCode != "001", x => x.CreatorOrgId.StartsWith(OrgCode))
                .Select(x => new
                {
                    x.Id,
                    OrgCode = x.CreatorOrgId.Substring(0, OrgCode.Length + 3),
                    Status = x.Status
                })
                .MergeTable()
                .GroupBy(x => new { x.OrgCode })
                .Select(x => new DepartmentalProcessingStatisticsDataDto
                {
                    OrgCode = x.OrgCode,
                    OrderCountNum = 0,//总量
                    YBOrderCountNum = 0,//已办
                    ZBOrderCountNum = 0,//在办
                    Archived = 0,
                    ToBeArchived = 0,
                    WaitPublished = 0,
                    PublishedOpen = 0,
                    PublishedNoOpen = 0,
                    YBOverdue = 0,
                    ZBOverdue = 0,
                    CompleteOnTime = 0,
                    HQYBOverdue = 0,
                    HQZBOverdue = 0,
                    DelayEnd = 0,
                    DelayWait = 0,
                    OrderDelayCount = 0,
                    ScreenCount = SqlFunc.AggregateSum(SqlFunc.IIF(x.Id != null, 1, 0)),
                    ScreenApproval = SqlFunc.AggregateSum(SqlFunc.IIF(x.Status == EScreenStatus.Approval || x.Status == EScreenStatus.Apply, 1, 0)),
                    ScreenPass = SqlFunc.AggregateSum(SqlFunc.IIF(x.Status == EScreenStatus.End, 1, 0)),
                    ScreenNotPass = SqlFunc.AggregateSum(SqlFunc.IIF(x.Status == EScreenStatus.Refuse, 1, 0)),
                    SatisfactionCount = 0,
                    NotSatisfactionCount = 0,
                })
                .MergeTable();
            #endregion

            #region 满意度
            //满意度
            var orderVisit = _orderVisitDetailRepository.Queryable()
              .Where(x => x.OrderVisit.VisitTime >= StartDate && x.OrderVisit.VisitTime <= EndDate && x.VisitTarget == EVisitTarget.Org && x.OrderVisit.VisitState == EVisitState.Visited && !string.IsNullOrEmpty(x.VisitOrgCode))
              .WhereIF(OrgCode == "001", x => x.VisitOrgCode == OrgCode)
              .WhereIF(OrgCode != "001", x => x.VisitOrgCode.StartsWith(OrgCode))
            .Select(x => new
            {
                OrgCode = x.VisitOrgCode.Substring(0, OrgCode.Length + 3),
                x.OrgProcessingResults
            })
            .MergeTable()
            .GroupBy(x => new { x.OrgCode })
            .Select(x => new DepartmentalProcessingStatisticsDataDto
            {
                OrgCode = x.OrgCode,
                OrderCountNum = 0,//总量
                YBOrderCountNum = 0,//已办
                ZBOrderCountNum = 0,//在办
                Archived = 0,
                ToBeArchived = 0,
                WaitPublished = 0,
                PublishedOpen = 0,
                PublishedNoOpen = 0,
                YBOverdue = 0,
                ZBOverdue = 0,
                CompleteOnTime = 0,
                HQYBOverdue = 0,
                HQZBOverdue = 0,
                DelayEnd = 0,
                DelayWait = 0,
                OrderDelayCount = 0,
                ScreenCount = 0,
                ScreenApproval = 0,
                ScreenPass = 0,
                ScreenNotPass = 0,
                SatisfactionCount = SqlFunc.AggregateSum(SqlFunc.IIF(SqlFunc.JsonField(x.OrgProcessingResults, "Key") != "2", 1, 0)),
                NotSatisfactionCount = SqlFunc.AggregateSum(SqlFunc.IIF(SqlFunc.JsonField(x.OrgProcessingResults, "Key") == "2", 1, 0))
            })
          .MergeTable();
            #endregion

            //联合查询
            var items = await _orderRepository.OrderUnionAll(queryOrder, queryPublish, queryCountersign, orderDelay, orderScreen, orderVisit)
                 .LeftJoin<SystemOrganize>((p, o) => p.OrgCode == o.Id)
                .Select((p, o) => new DepartmentalProcessingStatisticsDataDto
                {
                    OrgCode = p.OrgCode,
                    OrgName = o.Name,
                    OrgType = o.OrgType,
                    OrderCountNum = p.OrderCountNum,
                    YBOrderCountNum = p.YBOrderCountNum,
                    ZBOrderCountNum = p.ZBOrderCountNum,
                    Archived = p.Archived,
                    ToBeArchived = p.ToBeArchived,
                    WaitPublished = p.WaitPublished,
                    PublishedOpen = p.PublishedOpen,
                    PublishedNoOpen = p.PublishedNoOpen,
                    YBOverdue = p.YBOverdue,
                    ZBOverdue = p.ZBOverdue,
                    CompleteOnTime = p.CompleteOnTime,
                    HQYBOverdue = p.HQYBOverdue,
                    HQZBOverdue = p.HQZBOverdue,
                    DelayEnd = p.DelayEnd,
                    DelayWait = p.DelayWait,
                    OrderDelayCount = p.OrderDelayCount,
                    ScreenCount = p.ScreenCount,
                    ScreenApproval = p.ScreenApproval,
                    ScreenPass = p.ScreenPass,
                    ScreenNotPass = p.ScreenNotPass,
                    SatisfactionCount = p.SatisfactionCount,
                    NotSatisfactionCount = p.NotSatisfactionCount
                })
                .MergeTable()
                .WhereIF(string.IsNullOrEmpty(OrgName) == false, x => x.OrgName.Contains(OrgName))
                .ToListAsync();

           //计算合计
            var total = new DepartmentalProcessingStatisticsDataDto
            {
                OrgCode = "",
                OrgName = "合计",
                OrgType = EOrgType.City,
                OrderCountNum = items.Sum(p => p.OrderCountNum),
                YBOrderCountNum = items.Sum(p => p.YBOrderCountNum),
                ZBOrderCountNum = items.Sum(p => p.ZBOrderCountNum),
                Archived = items.Sum(p => p.Archived),
                ToBeArchived = items.Sum(p => p.ToBeArchived),
                WaitPublished = items.Sum(p => p.WaitPublished),
                PublishedOpen = items.Sum(p => p.PublishedOpen),
                PublishedNoOpen = items.Sum(p => p.PublishedNoOpen),
                YBOverdue = items.Sum(p => p.YBOverdue),
                ZBOverdue = items.Sum(p => p.ZBOverdue),
                CompleteOnTime =items.Sum(p=>p.CompleteOnTime),
                HQYBOverdue = items.Sum(p => p.HQYBOverdue),
                HQZBOverdue = items.Sum(p => p.HQZBOverdue),
                DelayEnd = items.Sum(p => p.DelayEnd),
                DelayWait = items.Sum(p => p.DelayWait),
                OrderDelayCount = items.Sum(p => p.OrderDelayCount),
                ScreenCount = items.Sum(p => p.ScreenCount),
                ScreenApproval = items.Sum(p => p.ScreenApproval),
                ScreenPass = items.Sum(p => p.ScreenPass),
                ScreenNotPass = items.Sum(p => p.ScreenNotPass),
                SatisfactionCount = items.Sum(p => p.SatisfactionCount),
                NotSatisfactionCount = items.Sum(p => p.NotSatisfactionCount)
            };
            return new { List = items, Total = total };
        }

        /// <summary>
        /// 部门办件统计表--明细
        /// </summary>
        /// <param name="dto"></param>
        /// <returns></returns>

        [HttpGet("departmental_processing_statistics_list")]
        public async Task<PagedDto<OrderDto>> GetDepartmentalProcessingStatisticsList([FromQuery] DepartmentalProcessingStatisticsRequest dto)
        {
            dto.EndDate = dto.EndDate.AddDays(1).AddSeconds(-1);

            RefAsync<int> total = 0;
            string stye = "";
            switch (dto.StatisticsType)
            {
                case EStatisticsType.YBOrderCountNum:
                case EStatisticsType.ZBOrderCountNum:
                case EStatisticsType.ToBeArchived:
                case EStatisticsType.Archived:
                case EStatisticsType.YBOverdue:
                case EStatisticsType.ZBOverdue:
                    //工单
                    stye = "1";
                    break;

                case EStatisticsType.WaitPublished:
                case EStatisticsType.PublishedOpen:
                case EStatisticsType.PublishedNoOpen:
                    //发布
                    stye = "2";
                    break;

                case EStatisticsType.OrderDelayCount:
                    //延期
                    stye = "3";
                    break;

                case EStatisticsType.ScreenCount:
                case EStatisticsType.ScreenApproval:
                case EStatisticsType.ScreenPass:
                case EStatisticsType.ScreenNotPass:
                    //甄别
                    stye = "4";
                    break;

                case EStatisticsType.HQYBOverdue:
                case EStatisticsType.HQZBOverdue:
                case EStatisticsType.DelayEnd:
                case EStatisticsType.DelayWait:
                    //会签
                    stye = "5";
                    break;

                case EStatisticsType.SubtotalOverdue:
                    //超期总数
                    stye = "6";
                    break;

                default:
                    break;
            }

            #region 工单
            if (stye == "1")
            {
                //工单
                var (totalOrder, items) = await _orderRepository.Queryable()
                          .Where(it => it.CreationTime >= dto.StartDate && it.CreationTime <= dto.EndDate && it.Status > EOrderStatus.WaitForAccept)
                          .WhereIF(dto.OrgCode == "001", it => it.ActualHandleOrgCode == dto.OrgCode)
                          .WhereIF(dto.OrgCode != "001", it => it.ActualHandleOrgCode.StartsWith(dto.OrgCode))
                         .WhereIF(dto.StatisticsType == EStatisticsType.YBOrderCountNum, it => it.Status >= EOrderStatus.Filed)//已办
                           .WhereIF(dto.StatisticsType == EStatisticsType.ZBOrderCountNum, it => it.Status < EOrderStatus.Filed)//在办
                           .WhereIF(dto.StatisticsType == EStatisticsType.Archived, it => it.Status >= EOrderStatus.Filed)//已归档
                            .WhereIF(dto.StatisticsType == EStatisticsType.ToBeArchived, it => it.Status < EOrderStatus.WaitForAccept)//待归档--没得待归档数据
                           .WhereIF(dto.StatisticsType == EStatisticsType.YBOverdue, it => it.Status >= EOrderStatus.Filed && it.ActualHandleTime > it.ExpiredTime)//已办超期
                           .WhereIF(dto.StatisticsType == EStatisticsType.ZBOverdue, it => it.Status < EOrderStatus.Filed && it.ExpiredTime < SqlFunc.GetDate())//待办超期
                           .OrderByDescending(it => it.CreationTime)
                           .ToPagedListAsync(dto.PageIndex, dto.PageSize, HttpContext.RequestAborted);
                return new PagedDto<OrderDto>(totalOrder, _mapper.Map<IReadOnlyList<OrderDto>>(items));
            }
            #endregion

            #region 发布
            if (stye == "2")
            {
                //发布
                var queryPublish = await _orderPublishRepository.Queryable()
                    .LeftJoin<Order>((x, o) => x.OrderId == o.Id)
                    .Where((x, o) => x.CreationTime >= dto.StartDate && x.CreationTime <= dto.EndDate)
                    .WhereIF(dto.OrgCode == "001", (x, o) => o.ActualHandleOrgCode == dto.OrgCode)
                    .WhereIF(dto.OrgCode != "001", (x, o) => o.ActualHandleOrgCode.StartsWith(dto.OrgCode))
                    .WhereIF(dto.StatisticsType == EStatisticsType.WaitPublished, (x, o) => o.Status == EOrderStatus.Filed)//待发布  --已归档的就是待发布
                    .WhereIF(dto.StatisticsType == EStatisticsType.PublishedOpen, (x, o) => o.Status >= EOrderStatus.Published && x.PublishState)//已发布公开
                    .WhereIF(dto.StatisticsType == EStatisticsType.PublishedNoOpen, (x, o) => o.Status == EOrderStatus.Published && !x.PublishState)//已发布不公开
                    .OrderByDescending((x, o) => o.CreationTime)
                    .Select((x, o) => new { o })
                    .ToPageListAsync(dto.PageIndex, dto.PageSize, total, HttpContext.RequestAborted);

                var dtos = queryPublish.Select(d =>
                {
                    var dto = _mapper.Map<OrderDto>(d.o);
                    return dto;
                }).ToList();

                return new PagedDto<OrderDto>(total, dtos);
            }
            #endregion

            #region 延期
            if (stye == "3")
            {
                //延期
                var orderDelay = await _orderDelayRepository.Queryable()
                .LeftJoin<Order>((x, o) => x.OrderId == o.Id)
                .Where((x, o) => x.CreationTime >= dto.StartDate && x.CreationTime <= dto.EndDate)
                .WhereIF(dto.OrgCode == "001", (x, o) => x.ApplyOrgCode == dto.OrgCode)
                .WhereIF(dto.OrgCode != "001", (x, o) => x.ApplyOrgCode.StartsWith(dto.OrgCode))
              .WhereIF(dto.StatisticsType == EStatisticsType.OrderDelayCount, (x, o) => x.DelayState != EDelayState.Withdraw)//延期次数
                .OrderByDescending((x, o) => o.CreationTime)
                .Select((x, o) => new { o })
                .ToPageListAsync(dto.PageIndex, dto.PageSize, total, HttpContext.RequestAborted);
                var dtos = orderDelay.Select(d =>
                {
                    var dto = _mapper.Map<OrderDto>(d.o);
                    return dto;
                }).ToList();

                return new PagedDto<OrderDto>(total, dtos);
            }
            #endregion

            #region 甄别
            if (stye == "4")
            {
                //甄别
                var orderScreen = await _orderScreenRepository.Queryable()
                 .LeftJoin<Order>((x, o) => x.OrderId == o.Id)
                .Where((x, o) => x.CreationTime >= dto.StartDate && x.CreationTime <= dto.EndDate)
                  .WhereIF(dto.OrgCode == "001", (x, o) => x.CreatorOrgId == dto.OrgCode)
                .WhereIF(dto.OrgCode != "001", (x, o) => x.CreatorOrgId.StartsWith(dto.OrgCode))
                .WhereIF(dto.StatisticsType == EStatisticsType.ScreenCount, (x, o) => x.Id != null)//申请总量
                .WhereIF(dto.StatisticsType == EStatisticsType.ScreenApproval, (x, o) => x.Status == EScreenStatus.Approval || x.Status == EScreenStatus.Apply)//待甄别
                .WhereIF(dto.StatisticsType == EStatisticsType.ScreenPass, (x, o) => x.Status == EScreenStatus.End)//甄别通过
                .WhereIF(dto.StatisticsType == EStatisticsType.ScreenNotPass, (x, o) => x.Status == EScreenStatus.Refuse)//甄别不通过
                 .OrderByDescending((x, o) => o.CreationTime)
                .Select((x, o) => new { o })
                .ToPageListAsync(dto.PageIndex, dto.PageSize, total, HttpContext.RequestAborted);
                var dtos = orderScreen.Select(d =>
                {
                    var dto = _mapper.Map<OrderDto>(d.o);
                    return dto;
                }).ToList();

                return new PagedDto<OrderDto>(total, dtos);
            }
            #endregion

            #region 会签(已办超期、待办超期)
            if (stye == "5")
            {
                //会签(已办超期、待办超期)
                var queryCountersign = await _workflowStepHandleRepository.Queryable()
               .LeftJoin<WorkflowTrace>((x, o) => x.WorkflowStepId == o.StepId)
               .LeftJoin<Order>((x, o, p) => o.WorkflowId == p.WorkflowId)
               .Where((x, o, p) => o.ModuleCode == WorkflowModuleConsts.OrderHandle && o.CountersignPosition == ECountersignPosition.Multi && o.CountersignPosition == ECountersignPosition.Single && o.CreationTime >= dto.StartDate && o.CreationTime <= dto.EndDate)
               .WhereIF(dto.OrgCode == "001", (x, o, p) => x.OrgId == dto.OrgCode)
               .WhereIF(dto.OrgCode != "001", (x, o, p) => x.OrgId.StartsWith(dto.OrgCode))
               .WhereIF(dto.StatisticsType == EStatisticsType.HQYBOverdue, (x, o, p) => o.Status == EWorkflowStepStatus.Handled && o.HandleTime > o.StepExpiredTime)//会签已办超期
               .WhereIF(dto.StatisticsType == EStatisticsType.HQZBOverdue, (x, o, p) => o.Status != EWorkflowStepStatus.Handled && DateTime.Now >= o.StepExpiredTime)//会签待办超期
               .WhereIF(dto.StatisticsType == EStatisticsType.DelayEnd, (x, o, p) => o.Status == EWorkflowStepStatus.Handled)//会签已办
               .WhereIF(dto.StatisticsType == EStatisticsType.DelayWait, (x, o, p) => o.Status != EWorkflowStepStatus.Handled)//会签待办
               .OrderByDescending((x, o, p) => p.CreationTime)
                .Select((x, o, p) => new { p })
                .ToPageListAsync(dto.PageIndex, dto.PageSize, total, HttpContext.RequestAborted);
                var dtos = queryCountersign.Select(d =>
                {
                    var dto = _mapper.Map<OrderDto>(d.p);
                    return dto;
                }).ToList();

                return new PagedDto<OrderDto>(total, dtos);
            }

            #endregion

            #region 超期件数
            //超期件数
            if (stye == "6")
            {
                var queryOrder = _orderRepository.Queryable()
                         .Where(it => it.CreationTime >= dto.StartDate && it.CreationTime <= dto.EndDate && it.Status > EOrderStatus.WaitForAccept)
                         .WhereIF(dto.OrgCode == "001", it => it.ActualHandleOrgCode == dto.OrgCode)
                         .WhereIF(dto.OrgCode != "001", it => it.ActualHandleOrgCode.StartsWith(dto.OrgCode))
                          .WhereIF(dto.StatisticsType == EStatisticsType.SubtotalOverdue, it => (it.Status >= EOrderStatus.Filed && it.ActualHandleTime > it.ExpiredTime)
                                     || (it.Status < EOrderStatus.Filed && it.ExpiredTime < SqlFunc.GetDate()))//已办超期/待办超期
                         .Select(it => new SelectOrderId { Id = it.Id })
                         .MergeTable();

                //会签(已办超期、待办超期)
                var queryCountersign = _workflowStepHandleRepository.Queryable()
               .LeftJoin<WorkflowTrace>((x, o) => x.WorkflowStepId == o.StepId)
               .LeftJoin<Order>((x, o, p) => o.WorkflowId == p.WorkflowId)
               .Where((x, o, p) => o.ModuleCode == WorkflowModuleConsts.OrderHandle && o.CountersignPosition == ECountersignPosition.Multi
               && o.CountersignPosition == ECountersignPosition.Single && o.CreationTime >= dto.StartDate && o.CreationTime <= dto.EndDate)
               .WhereIF(dto.OrgCode == "001", (x, o, p) => x.OrgId == dto.OrgCode)
               .WhereIF(dto.OrgCode != "001", (x, o, p) => x.OrgId.StartsWith(dto.OrgCode))
               .WhereIF(dto.StatisticsType == EStatisticsType.HQYBOverdue, (x, o, p) => (o.Status == EWorkflowStepStatus.Handled && o.HandleTime > o.StepExpiredTime)
                        || (o.Status != EWorkflowStepStatus.Handled && DateTime.Now >= o.StepExpiredTime))//会签已办超期/会签待办超期
                .Select((x, o, p) => new SelectOrderId { Id = p.Id })
               .MergeTable();

                var queryData = await _orderRepository.OrderListUnionAll(queryOrder, queryCountersign)
                    .LeftJoin<Order>((x, o) => x.Id == o.Id)
                     .Select((x, o) => new { o })
                    .ToPageListAsync(dto.PageIndex, dto.PageSize, total, HttpContext.RequestAborted);

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

                return new PagedDto<OrderDto>(total, dtos);

            }
            #endregion

            return new PagedDto<OrderDto>(total, new List<OrderDto>());
        }

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

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

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

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

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

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


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

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

        }

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

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

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

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

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

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

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

        /// <summary>
        /// 回退错件
        /// </summary>
        /// <param name="dto"></param>
        /// <returns></returns>
        [HttpGet("reTransact")]
        public async Task<PagedDto<OrderReTransactVo>> OrderReTransact([FromQuery] QueryOrderReTransactRequest dto)
        {
            if (!dto.StartTime.HasValue || !dto.EndTime.HasValue)
                throw UserFriendlyException.SameMessage("请选择时间!");
            dto.EndTime = dto.EndTime.Value.AddDays(1).AddSeconds(-1);
            var (total, items) = await _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 == 0, x => x.Num, OrderByType.Asc)
                .OrderByIF(dto.SortRule == 1, x => x.Num, OrderByType.Desc)
                .ToPagedListAsync(dto, HttpContext.RequestAborted);
            return new PagedDto<OrderReTransactVo>(total, _mapper.Map<IReadOnlyList<OrderReTransactVo>>(items));
        }

        /// <summary>
        /// 回退错件明细
        /// </summary>
        /// <param name="dto"></param>
        /// <returns></returns>
        [HttpGet("reTransact_detail")]
        public async Task<PagedDto<OrderSpecialDetailDto>> OrderReTransactDetail([FromQuery] QueryOrderReTransactDetailRequest dto)
        {
            if (!dto.StartTime.HasValue || !dto.EndTime.HasValue)
                throw UserFriendlyException.SameMessage("请选择时间!");
            dto.EndTime = dto.EndTime.Value.AddDays(1).AddSeconds(-1);
            var (total, items) = await _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)
                .ToPagedListAsync(dto, HttpContext.RequestAborted);
            return new PagedDto<OrderSpecialDetailDto>(total, _mapper.Map<IReadOnlyList<OrderSpecialDetailDto>>(items));
        }

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

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

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

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

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

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

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



    }
}