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

using Hotline.Caching.Interfaces;
using Hotline.CallCenter.Calls;
using Hotline.FlowEngine.Workflows;
using Hotline.Orders;
using Hotline.Repository.SqlSugar.Extensions;
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.Order;
using Hotline.Share.Enums.CallCenter;
using Hotline.Share.Enums.Order;
using Hotline.Share.Requests;
using MapsterMapper;
using Microsoft.AspNetCore.Authorization;
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<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;


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

        /// <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("请选择时间!");
            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)
                .GroupBy((x, o) => new { x.ActualHandleOrgCode, o.Name })
                .Select((x, o) => new OrderBiOrgDataListVo
                {
                    OrgName = o.Name,
                    OrgId = x.ActualHandleOrgCode,
                    HandlerExtendedNum = SqlFunc.AggregateSum(SqlFunc.IIF((int)x.Status >= 300 && x.ExpiredTime < x.FiledTime, 1, 0)),
                    NoHandlerExtendedNum = SqlFunc.AggregateSum(SqlFunc.IIF((int)x.Status < 300 && 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, 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, x => x.CreationTime >= dto.StartTime)
                .WhereIF(dto.EndTime.HasValue, x => x.CreationTime <= dto.EndTime)
                .WhereIF(!string.IsNullOrEmpty(dto.Keyword), x => x.AcceptorName.Contains(dto.Keyword!))
                .GroupBy(x => new { x.AcceptorId, x.AcceptorName })
                .Select(x => new OrderBiCentreDataListVo
                {
                    UserName = x.AcceptorName,
                    UserId = x.AcceptorId,
                    CentreArchive = SqlFunc.AggregateSum(SqlFunc.IIF((int)x.Status >= 300 && x.ProcessType == EProcessType.Zhiban, 1, 0)),
                    CentreCareOf = SqlFunc.AggregateSum(SqlFunc.IIF((int)x.Status >= 300 && x.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(x.AcceptType == "无效", 1, 0)),
                    Repeat = SqlFunc.AggregateSum(SqlFunc.IIF(x.DuplicateIds != null && SqlFunc.JsonArrayLength(x.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;
            }
            var (total, items) = await query.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);

            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!))
                .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!))
                .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 query = _orderRepository.Queryable(false, false, false)
                .WhereIF(dto.StartTime.HasValue, x => x.CreationTime >= dto.StartTime)
                .WhereIF(dto.EndTime.HasValue, x => x.CreationTime <= dto.EndTime)
                .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);


            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)
                .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)
                .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 = await _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), x => x.VisitOrgName.Contains(OrgName))
                .WhereIF(!string.IsNullOrEmpty(LineNum), x => x.OrderVisit.Order.CallRecord.Gateway.Contains(LineNum))
                .WhereIF(IsCenter == false, x => x.VisitOrgCode.StartsWith(_sessionContext.OrgId))
                .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();

            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-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(!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.Substring(SqlFunc.MappingColumn<int>("0"), SqlFunc.MappingColumn<int>("9"))
                 })
                 .Select((x, it) => new VisitAndOrgSatisfactionStatisticsDto()
                 {
                     OrgCode = it.VisitOrgCode.Substring(SqlFunc.MappingColumn<int>("0"), SqlFunc.MappingColumn<int>("9")),
                     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()
              .LeftJoin<SystemOrganize>((it, o) => it.OrgLevelOneCode == o.Id)
              .Where((it, o) => it.CreationTime >= StartDate && it.CreationTime <= EndDate && (int)it.Status >= 300)
                .WhereIF(TypeCode == 1, (it, o) => it.OrgLevelOneCode == "001")
                .WhereIF(TypeCode == 2, (it, o) => it.OrgLevelOneCode != "001")
                .WhereIF(IsCenter == false, (it, o) => it.OrgLevelOneCode.StartsWith(_sessionContext.RequiredOrgId))
               .GroupBy((it, o) => new
               {
                   it.OrgLevelOneCode,
                   o.Name,
                   o.OrgType
               })
                .Select((it, o) => new DepartmentAcceptanceTypeStatisticsDto
                {
                    OrgName = it.OrgLevelOneCode == "001" ? "热线中心" : o.Name,
                    OrgCode = it.OrgLevelOneCode,
                    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();

            return orderData;
        }

        /// <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 && (int)p.Status >= 300)
                .WhereIF(!string.IsNullOrEmpty(dto.OrgLevelOneCode), p => p.OrgLevelOneCode == dto.OrgLevelOneCode)
                .WhereIF(!string.IsNullOrEmpty(dto.AcceptTypeCode), p => p.AcceptTypeCode == dto.AcceptTypeCode)
                .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="OrgCode"></param>
        /// <param name="OrgName"></param>
        /// <returns></returns>
        [HttpGet("departmental_processing_statistics")]
        [AllowAnonymous]
        public async Task<object> DepartmentalProcessingStatistics(DateTime StartDate, DateTime EndDate, string? OrgCode, string? OrgName)
        {
            EndDate = EndDate.AddDays(1).AddSeconds(-1);
            var handeOrgDownNum = 0;
            if (!string.IsNullOrEmpty(OrgCode) && OrgCode != "001")
            {
                handeOrgDownNum = OrgCode.Length + 3;
            }
            //工单
            var query = _orderRepository.Queryable()
                      .Where(it => it.CreationTime >= StartDate && it.CreationTime <= EndDate)
                      .Select(it => new
                      {
                          it.Id,
                          OrgLevelOneCode = SqlFunc.IIF(it.ActualHandleOrgCode != "001", SqlFunc.Substring(it.ActualHandleOrgCode, 0, 6), it.ActualHandleOrgCode),

                          OrgLevelDownCode = SqlFunc.IIF(!string.IsNullOrEmpty(OrgCode) && OrgCode != "001" && it.ActualHandleOrgCode.Length >= handeOrgDownNum
                          , SqlFunc.Substring(it.ActualHandleOrgCode, 0, handeOrgDownNum), it.ActualHandleOrgCode),

                          it.ActualHandleOrgCode,
                          it.Status,//工单状态
                          it.ExpiredTime,//期满时间
                          it.ActualHandleTime,//办理时间
                          it.CounterSignType,//会签
                      }).MergeTable()
                      .WhereIF(!string.IsNullOrEmpty(OrgCode) && OrgCode == "001", it => it.OrgLevelOneCode == OrgCode)
                      .WhereIF(!string.IsNullOrEmpty(OrgCode) && OrgCode != "001", it => it.OrgLevelDownCode.Contains(OrgCode))
                      .MergeTable()
                       .Select(it => new DepartmentalProcessingStatisticsDto
                       {
                           Id = it.Id,
                           OrgCode = SqlFunc.IIF(!string.IsNullOrEmpty(OrgCode), it.OrgLevelDownCode, it.OrgLevelOneCode),
                           ActualHandleOrgCode = it.ActualHandleOrgCode,
                           Status = it.Status,//工单状态
                           ExpiredTime = it.ExpiredTime,//期满时间
                           ActualHandleTime = it.ActualHandleTime,//办理时间
                           CounterSignType = it.CounterSignType,//会签

                       }).MergeTable();
            //发布
            var queryPublish = _orderPublishRepository.Queryable()
                .Where(p => p.CreationTime >= StartDate && p.CreationTime <= EndDate)
                .GroupBy(it => new
                {
                    it.OrderId,
                    it.PublishState
                })
                .Select(it => new DepartmentalProcessingStatisticsDto
                {
                    Id = it.OrderId,
                    PublishState = it.PublishState
                });
            //会签
            var queryCountersign = _workflowCountersignRepository.Queryable()
                .LeftJoin<WorkflowCountersignMember>((x, o) => x.Id == o.WorkflowCountersignId)
                 .Where(x => x.CreationTime >= StartDate && x.CreationTime <= EndDate)
                .GroupBy((x, o) => o.Key)
                .Select((x, o) => new DepartmentalProcessingStatisticsDataDto
                {
                    OrgCode = o.Key,
                    HQYBOverdue = SqlFunc.AggregateSum(SqlFunc.IIF(o.IsHandled, 1, 0)),
                    HQZBOverdue = SqlFunc.AggregateSum(SqlFunc.IIF(!o.IsHandled, 1, 0)),
                    DelayEnd = SqlFunc.AggregateSum(SqlFunc.IIF(!o.IsHandled, 1, 0)),
                    DelayWait = SqlFunc.AggregateSum(SqlFunc.IIF(!o.IsHandled, 1, 0)),
                }).MergeTable();
            var queryPush = query.LeftJoin(queryPublish, (it, o) => it.Id == o.Id).Where(it => it.OrgCode != null);








            return await queryPush.GroupBy((it, o) => new
            {
                it.OrgCode
            })
                      .Select((it, o) => new
                      {
                          //办件信息完整
                          OrgCode = it.OrgCode,
                          OrderCountNum = SqlFunc.AggregateCount(it.OrgCode),//总量
                          YBOrderCountNum = SqlFunc.AggregateSum(SqlFunc.IIF((int)it.Status >= 300, 1, 0)),//已办
                          ZBOrderCountNum = SqlFunc.AggregateSum(SqlFunc.IIF((int)it.Status < 300, 1, 0)),//在办


                          YBOverdue = SqlFunc.AggregateSum(SqlFunc.IIF((int)it.Status >= 300 && it.ActualHandleTime > it.ExpiredTime, 1, 0)),//已办超期
                          ZBOverdue = SqlFunc.AggregateSum(SqlFunc.IIF((int)it.Status < 300 && it.ExpiredTime < SqlFunc.GetDate(), 1, 0)),//待办超期

                          HQYBOverdue = SqlFunc.AggregateSum(SqlFunc.IIF((int)it.Status >= 300 && it.CounterSignType != null && it.ActualHandleTime > it.ExpiredTime, 1, 0)),//会签已办超期
                          HQZBOverdue = SqlFunc.AggregateSum(SqlFunc.IIF((int)it.Status < 300 && it.CounterSignType != null && it.ExpiredTime < SqlFunc.GetDate(), 1, 0)),//会签待办超期

                          //归档完整
                          Archived = SqlFunc.AggregateSum(SqlFunc.IIF((int)it.Status >= 300, 1, 0)),//已归档
                          ToBeArchived = 0,//待归档

                          //发布完整
                          WaitPublished = SqlFunc.AggregateSum(SqlFunc.IIF((int)it.Status == 300, 1, 0)),//待发布  --已归档的就是待发布
                          PublishedOpen = SqlFunc.AggregateSum(SqlFunc.IIF((int)it.Status >= 400 && o.PublishState, 1, 0)),//已发布公开
                          PublishedNoOpen = SqlFunc.AggregateSum(SqlFunc.IIF((int)it.Status >= 400 && !o.PublishState, 1, 0)),//已发布不公开
                      }).ToListAsync();
        }

        /// <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)
                    .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)
                   .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)
        {
            var (total, items) = await _orderRepository.Queryable(false, false, false)
                .Where(x => x.CreationTime >= dto.StartDate && x.CreationTime <= dto.EndDate)
                .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,
                    o.Id,
                })
                .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 = o.Id
                })
                .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)
        {
            var (total, items) = await _orderRepository.Queryable(viewFilter: true)
            .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) //区域
            .OrderByDescending(d => d.CreationTime)
            .ToPagedListAsync(dto, HttpContext.RequestAborted);
            return new PagedDto<OrderDto>(total, _mapper.Map<IReadOnlyList<OrderDto>>(items));
        }

    }
}