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

using Hotline.Orders;
using Hotline.Repository.SqlSugar.Extensions;
using Hotline.Settings;
using Hotline.Settings.Hotspots;
using Hotline.Share.Dtos;
using Hotline.Share.Dtos.Order;
using Hotline.Share.Enums.Order;
using Microsoft.AspNetCore.Mvc;
using SqlSugar;
using Hotline.Share.Requests;
using XF.Domain.Repository;
using Hotline.Caching.Interfaces;
using Hotline.FlowEngine.Workflows;
using Hotline.Share.Dtos.CallCenter;
using MapsterMapper;
using XF.Domain.Exceptions;
using Microsoft.AspNetCore.Authorization;
using Hotline.CallCenter.Calls;
using Hotline.Share.Enums.CallCenter;

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;


        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
            )
        {
            _orderRepository = orderRepository;
            _hotspotTypeRepository = hotspotTypeRepository;
            _sysDicDataCacheManager = sysDicDataCacheManager;
            _orderVisitDetailRepository = orderVisitDetailRepository;
            _orderDelayRepository = orderDelayRepository;
            _workflowCountersignRepository = workflowCountersignRepository;
            _orderSpecialRepository = orderSpecialRepository;
            _mapper = mapper;
            _orderVisitRepository = orderVisitRepository;
            _trCallRecordRepository = trCallRecordRepository;
        }

        /// <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("请选择时间!");
            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("请选择时间!");
            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("请选择环比时间!");
            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("请选择时间!");
            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)
                .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("请选择时间!");
            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(!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("请选择时间!");
            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))
                .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("请选择时间!");
            var query = _orderSpecialRepository.Queryable()
                .WhereIF(dto.StartTime.HasValue, x => x.CreationTime >= dto.StartTime)
                .WhereIF(dto.EndTime.HasValue, x => x.CreationTime <= dto.EndTime)
                .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("请选择时间!");
            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)
                .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.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)
        {
            return await _orderRepository.HotPortJoinOrgStatistics(dto.StartTime, dto.EndTime);
        }

        /// <summary>
        /// 回访量统计
        /// </summary>
        /// <returns></returns>
        [HttpGet("visit-measure-statistics")]
        public async Task<List<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 VisitMeasureStatisticsDto()
                {
                    VisitName = x.Employee.Name,
                    AiVisitCount = SqlFunc.AggregateSum(SqlFunc.IIF(x.VisitType == EVisitType.ChipVoiceVisit, 1, 0)),
                    ArtificialVisitCount = SqlFunc.AggregateSum(SqlFunc.IIF(x.VisitType == EVisitType.ArtificialVisit, 1, 0)),
                    SumCount = SqlFunc.AggregateCount(x.EmployeeId)
                })
                .ToListAsync();
            return list;
        }

        /// <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.StartTime >= StartDate && o.StartTime <= 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.StartTime >= StartDate && o.StartTime <= 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<List<VisitAndOrgSatisfactionStatisticsDto>> VisitAndOrgSatisfactionStatistics(DateTime StartDate, DateTime EndDate, string OrgName, int TypeId, string? LineNum)
        {
            EndDate = EndDate.AddDays(1).AddSeconds(-1);
            var list = await _orderVisitDetailRepository.Queryable()
                .Includes(x => x.OrderVisit)
                .Where(x => x.OrderVisit.VisitTime >= StartDate && x.OrderVisit.VisitTime <= EndDate && x.VisitTarget == EVisitTarget.Org && x.OrderVisit.VisitState == EVisitState.Visited)
                .WhereIF(!string.IsNullOrEmpty(OrgName), x => x.VisitOrgName.Contains(OrgName))
                .GroupBy(x => new { x.VisitOrgCode, x.VisitOrgName })
                .Select(x => new VisitAndOrgSatisfactionStatisticsDto()
                {
                    OrgName = x.VisitOrgName,
                    OrgCode = x.VisitOrgCode,
                    TotalSumCount = SqlFunc.AggregateCount(x.VisitOrgCode),
                    VerySatisfiedCount = SqlFunc.AggregateSum(SqlFunc.IIF(SqlFunc.JsonListObjectAny(x.OrgProcessingResults, "key", "5"), 1, 0)),//非常满意数
                })
                .ToListAsync();

            return list;
        }

        /// <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(false, false, false).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(false, false, false)
                .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(false, false, false)
                .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 (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 (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 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")
               .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)),

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

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

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

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

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

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

            return orderData;
        }
    }
}