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;

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;

		public BiOrderController(
            IOrderRepository orderRepository,
            IRepository<Hotspot> hotspotTypeRepository,
            ISystemDicDataCacheManager sysDicDataCacheManager,
            IRepository<OrderVisitDetail> orderVisitDetailRepository,
            IRepository<OrderDelay> orderDelayRepository,
            IRepository<WorkflowCountersign> workflowCountersignRepository,
            IRepository<OrderSpecial> orderSpecialRepository,
			IMapper mapper
            )
        {
            _orderRepository = orderRepository;
            _hotspotTypeRepository = hotspotTypeRepository;
            _sysDicDataCacheManager = sysDicDataCacheManager;
            _orderVisitDetailRepository = orderVisitDetailRepository;
            _orderDelayRepository = orderDelayRepository;
            _workflowCountersignRepository = workflowCountersignRepository;
            _orderSpecialRepository = orderSpecialRepository;
			_mapper = mapper;
        }

        /// <summary>
        /// 部门数据统计
        /// </summary>
        /// <param name="dto"></param>
        /// <returns></returns>
        [HttpGet("org_data_list")]
        public async Task<PagedDto<OrderBiOrgDataListVo>> OrgDataList([FromQuery] ReportPagedRequest dto)
        {
            //TODO 会签统计待处理 目前缺少关联关系
            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 > x.FiledTime, 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)
        {
            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_data_list")]
        public async Task<PagedDto<HotspotDataLsitVo>> HotspotDataLsit([FromQuery] HotspotReportPagedRequest dto)
        {
            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("visit-nosatisfied")]
        public async Task<object> QueryVisitNoSatisfied([FromQuery] QueryVisitNoSatiisfiedRequest dto)
        {
            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)
        {
            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)
        {
            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)
		{
			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));
		}
	}
}