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;

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;


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

        /// <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;
		}
	}
}