hotline/src/Hotline.Api/Controllers/Bigscreen/DataScreenController.cs

using DocumentFormat.OpenXml.Drawing;
using Hotline.Configurations;
using Hotline.KnowledgeBase;
using Hotline.Orders;
using Hotline.Repository.SqlSugar.Orders;
using Hotline.Settings;
using Hotline.Settings.Hotspots;
using Hotline.Share.Dtos;
using Hotline.Share.Dtos.Bigscreen;
using Hotline.Share.Dtos.Order;
using Hotline.Share.Enums.KnowledgeBase;
using Hotline.Share.Enums.Order;
using JiebaNet.Segmenter.Common;
using MapsterMapper;
using Microsoft.AspNetCore.Authorization;
using Microsoft.AspNetCore.Mvc;
using Microsoft.Extensions.Options;
using SqlSugar;
using System;
using XF.Domain.Repository;

namespace Hotline.Api.Controllers.Bigscreen
{
    public class DataScreenController : BaseController
    {
        private readonly IOrderRepository _orderRepository;
        private readonly IRepository<OrderDelay> _orderDelayRepository;
        private readonly IRepository<OrderVisit> _orderVisitRepository;
        private readonly IRepository<Knowledge> _knowledgeRepository;
        private readonly IRepository<KnowledgePv> _knowledgePvRepository;
        private readonly IMapper _mapper;
        private readonly IRepository<OrderVisitDetail> _orderVisitDetailRepository;
        private readonly IRepository<SystemArea> _systemAreaRepository;
        private readonly IOptionsSnapshot<AppConfiguration> _appOptions;
        private readonly IRepository<OrderSecondaryHandling> _orderSecondaryHandlingRepository;

        public DataScreenController(IOrderRepository orderRepository, IRepository<OrderVisit> orderVisitRepository,
            IRepository<OrderDelay> orderDelayRepository, IRepository<Knowledge> knowledgeRepository, IRepository<KnowledgePv> knowledgePvRepository,
            IMapper mapper, IRepository<OrderVisitDetail> orderVisitDetailRepository, IRepository<SystemArea> systemAreaRepository,
            IOptionsSnapshot<AppConfiguration> appOptions,
            IRepository<OrderSecondaryHandling> orderSecondaryHandlingRepository)
        {
            _orderRepository = orderRepository;
            _orderVisitRepository = orderVisitRepository;
            _orderDelayRepository = orderDelayRepository;
            _knowledgeRepository = knowledgeRepository;
            _knowledgePvRepository = knowledgePvRepository;
            _mapper = mapper;
            _orderVisitDetailRepository = orderVisitDetailRepository;
            _systemAreaRepository = systemAreaRepository;
            _appOptions = appOptions;
            _orderSecondaryHandlingRepository = orderSecondaryHandlingRepository;
        }

        /// <summary>
        /// 工单统计
        /// </summary>
        /// <returns></returns>
        [AllowAnonymous]
        [HttpGet("order-statistics")]
        public async Task<OrderStatisticsDto> OrderStatistics(DateTime StartTime, DateTime EndTime)
        {
            EndTime = EndTime.AddDays(1).AddSeconds(-1);
            var dto = new OrderStatisticsDto();

            #region 办结工单

            dto.CompletionCount = await _orderRepository.Queryable(false, false, false)
                .Where(x => x.StartTime >= StartTime && x.StartTime <= EndTime && x.Status >= EOrderStatus.Filed).CountAsync();
            int CompletionSum = await _orderRepository.Queryable(false, false, false)
                .Where(x => x.StartTime >= StartTime && x.StartTime <= EndTime && x.Status >= EOrderStatus.Handling).CountAsync();
            if (CompletionSum == 0)
            {
                dto.CompletionRate = 0;
            }
            else
            {
                dto.CompletionRate = Math.Round((dto.CompletionCount / (double)CompletionSum) * 100, 2);
            }

            #endregion

            #region 待受理工单

            dto.HaveToAcceptCount = await _orderRepository.Queryable(false, false, false)
                .Where(x => x.CreationTime > StartTime && x.CreationTime <= EndTime && x.Status == EOrderStatus.WaitForAccept).CountAsync();
            int HaveToAcceptSum = await _orderRepository.Queryable(false, false, false)
                .Where(x => x.CreationTime >= StartTime && x.CreationTime <= EndTime).CountAsync();
            if (HaveToAcceptSum == 0)
            {
                dto.HaveToAcceptRate = 0;
            }
            else
            {
                dto.HaveToAcceptRate = Math.Round((dto.HaveToAcceptCount / (double)HaveToAcceptSum) * 100, 2);
            }

            #endregion

            #region 超期工单

            dto.OverTimeCount = await _orderRepository.Queryable(false, false, false)
                .Where(x => x.StartTime >= StartTime && x.StartTime <= EndTime && x.ExpiredStatus == EExpiredStatus.Expired).CountAsync();
            if (CompletionSum == 0)
            {
                dto.OverTimeRate = 0;
            }
            else
            {
                dto.OverTimeRate = Math.Round((dto.OverTimeCount / (double)CompletionSum) * 100, 2);
            }

            #endregion

            #region 延期工单

            dto.DelayCount = await _orderDelayRepository.Queryable()
                .Where(x => x.ApplyDelayTime >= StartTime && x.ApplyDelayTime <= EndTime && x.DelayState == EDelayState.Pass).CountAsync();
            if (CompletionSum == 0)
            {
                dto.DelayRate = 0;
            }
            else
            {
                dto.DelayRate = Math.Round((dto.DelayCount / (double)CompletionSum) * 100, 2);
            }

            #endregion

            #region 满意工单

            dto.SatisfiedCount = await _orderVisitRepository.Queryable().Where(x =>
                x.VisitTime >= StartTime && x.VisitTime <= EndTime && x.VisitState == EVisitState.Visited &&
                SqlFunc.JsonField(x.NowEvaluate, "Key") != "1" && SqlFunc.JsonField(x.NowEvaluate, "Key") != "2").CountAsync();
            int SatisfiedSum = await _orderVisitRepository.Queryable()
                .Where(x => x.VisitTime >= StartTime && x.VisitTime <= EndTime && x.VisitState == EVisitState.Visited).CountAsync();
            if (SatisfiedSum == 0)
            {
                dto.SatisfiedRate = 0;
            }
            else
            {
                dto.SatisfiedRate = Math.Round((dto.SatisfiedCount / (double)SatisfiedSum) * 100, 2);
            }

            #endregion

            #region 省工单量

            dto.ProvinceOrderCount = await _orderRepository.Queryable(false, false, false)
                .Where(x => x.StartTime >= StartTime && x.StartTime <= EndTime && x.IsProvince).CountAsync();
            dto.ProvinceOrderCompletionCount = await _orderRepository.Queryable(false, false, false)
                .Where(x => x.StartTime >= StartTime && x.StartTime <= EndTime && x.IsProvince && x.Status >= EOrderStatus.Filed).CountAsync();

            #endregion

            return dto;
        }

        /// <summary>
        /// 知识库统计
        /// </summary>
        /// <returns></returns>
        [AllowAnonymous]
        [HttpGet("knowledge-statistics")]
        public async Task<KnowledgeStatisticsDto> KnowledgeStatistics()
        {
            var dto = new KnowledgeStatisticsDto();
            dto.KnowledgeCount = await _knowledgeRepository.Queryable().Where(x => x.Status == EKnowledgeStatus.OnShelf).CountAsync(); //总数
            dto.TodayAddCount = await _knowledgeRepository.Queryable().Where(x =>
                x.Renewaln == false && x.Status == EKnowledgeStatus.OnShelf && x.OnShelfTime.Value.Date == DateTime.Now.Date).CountAsync(); //今日新增
            dto.ThisMonthModifyCount = await _knowledgeRepository.Queryable().Where(x => x.Renewaln == true && x.Status == EKnowledgeStatus.OnShelf &&
                                                                                         x.OnShelfTime.Value.Year
                                                                                         == DateTime.Now.Year &&
                                                                                         x.OnShelfTime.Value.Month == DateTime.Now.Month)
                .CountAsync(); //本月修改
            dto.TodayReadCount = await _knowledgePvRepository.Queryable().Where(x => x.CreationTime.Date == DateTime.Now.Date).CountAsync();
            return dto;
        }

        /// <summary>
        /// 受理类型办件分析
        /// </summary>
        /// <param name="StartDate"></param>
        /// <param name="EndDate"></param>
        /// <returns></returns>
        [AllowAnonymous]
        [HttpGet("ordertype-statistics")]
        public async Task<List<OrderTypeHandleStatisticsDto>> OrderTypeHandleStatistics(DateTime StartTime, DateTime EndTime)
        {
            EndTime = EndTime.AddDays(1).AddSeconds(-1);
            var list = await _orderRepository.Queryable(false, false, false).Where(x =>
                    x.StartTime >= StartTime && x.StartTime <= EndTime && x.Status > EOrderStatus.Handling && !string.IsNullOrEmpty(x.AcceptType))
                .GroupBy(x => x.AcceptType)
                .Select(x => new OrderTypeHandleStatisticsDto
                {
                    AcceptType = x.AcceptType,
                    SumCount = SqlFunc.AggregateCount(x.Id),
                    HandlingCount = SqlFunc.AggregateSum(SqlFunc.IIF(x.Status >= EOrderStatus.Handling && x.Status < EOrderStatus.Filed, 1, 0)),
                    FiledCount = SqlFunc.AggregateSum(SqlFunc.IIF(x.Status >= EOrderStatus.Filed, 1, 0)),
                    OverTimeCount = SqlFunc.AggregateSum(SqlFunc.IIF(x.ExpiredStatus == EExpiredStatus.Expired, 1, 0))
                }).ToListAsync();
            return list;
        }

        /// <summary>
        /// 获取区域信息
        /// </summary>
        /// <returns></returns>
        [HttpGet("get_system_area")]
        [AllowAnonymous]
        public async Task<object> GetSystemAreaAsync()
        {
            var areaCode = _appOptions.Value.GetDefaultAppScopeConfiguration().AreaCode;
            return await _systemAreaRepository.Queryable()
                .Where(p => p.Id == areaCode || p.ParentId == areaCode)
                .Select(p => new
                {
                    p.AreaName,
                    p.Id
                })
                .OrderBy(p => p.Id)
                .ToListAsync();
        }

        /// <summary>
        /// 预警热点
        /// </summary>
        /// <param name="StartDate"></param>
        /// <param name="EndDate"></param>
        /// <param name="AreaCode"></param>
        /// <returns></returns>
        [AllowAnonymous]
        [HttpGet("earlywarning-statistics")]
        public async Task<List<EarlyWarningHotsPotsStatisticsDto>> EarlyWarningHotsPotsStatistics(DateTime StartTime, DateTime EndTime,
            string AreaCode)
        {
            EndTime = EndTime.AddDays(1).AddSeconds(-1);
            if (AreaCode.Length == 6 && AreaCode.IndexOf("00") == 4)
            {
                AreaCode = AreaCode.Remove(4);
            }

            var list = await _orderRepository.Queryable(false, false, false)
                .Where(x => x.StartTime >= StartTime && x.StartTime <= EndTime && x.AreaCode.StartsWith(AreaCode) && !x.HotspotId.StartsWith("18"))
                .GroupBy(x => new { x.HotspotId, x.HotspotName, x.HotspotSpliceName })
                .Select(x => new EarlyWarningHotsPotsStatisticsDto()
                {
                    HotspotId = x.HotspotId,
                    HotspotName = x.HotspotName,
                    HotspotSpliceName = x.HotspotSpliceName,
                    SumCount = SqlFunc.AggregateCount(x.Id)
                }).OrderByDescending(x => x.SumCount).Take(5).ToListAsync();
            return list;
        }

        /// <summary>
        /// 工单当日统计及环比
        /// </summary>
        /// <returns></returns>
        [AllowAnonymous]
        [HttpGet("ordercount-statistics")]
        public async Task<OrderCountStatisticsDto> OrderCountStatistics()
        {
            var today = DateTime.Now;
            var dto = new OrderCountStatisticsDto();

            #region 当日工单量

            dto.ToDayCount = await _orderRepository.Queryable(false, false, false)
                .Where(x => x.StartTime.Value.Date == DateTime.Now.Date && x.Status > EOrderStatus.WaitForAccept).CountAsync();
            var beforToDayCount = await _orderRepository.Queryable(false, false, false)
                //.Where(x => x.StartTime.Value.Date == today.AddDays(-1).Date && x.Status > EOrderStatus.WaitForAccept)
                .Where(x => x.StartTime.Value.Date == DateTime.Now.AddDays(-1).Date && x.Status > EOrderStatus.WaitForAccept)
                .CountAsync();


            if (beforToDayCount == 0)
            {
                dto.ToDayQoQ = 0;
            }
            else
            {
                dto.ToDayQoQ = Math.Round(((dto.ToDayCount - beforToDayCount) / (double)beforToDayCount) * 100, 2);
            }

            #endregion

            #region 当月工单量

            dto.ToMonthCount = await _orderRepository.Queryable(false, false, false).Where(x =>
                x.StartTime.Value.ToString("yyyy-MM") == today.ToString("yyyy-MM") && x.Status > EOrderStatus.WaitForAccept).CountAsync();

            var beforToMonthCount = await _orderRepository.Queryable(false, false, false).Where(x =>
                    x.StartTime.Value.ToString("yyyy-MM") == today.AddMonths(-1).ToString("yyyy-MM") && x.Status > EOrderStatus.WaitForAccept)
                .CountAsync();

            if (beforToMonthCount == 0)
            {
                dto.ToMonthQoQ = 0;
            }
            else
            {
                dto.ToMonthQoQ = Math.Round(((dto.ToMonthCount - beforToMonthCount) / (double)beforToMonthCount) * 100, 2);
            }

            #endregion

            #region 当年工单量

            dto.ToYearCount = await _orderRepository.Queryable(false, false, false)
                .Where(x => x.StartTime.Value.ToString("yyyy") == today.ToString("yyyy") && x.Status > EOrderStatus.WaitForAccept).CountAsync();

            var beforToYearCount = await _orderRepository.Queryable(false, false, false).Where(x =>
                x.StartTime.Value.ToString("yyyy") == today.AddYears(-1).ToString("yyyy") && x.Status > EOrderStatus.WaitForAccept).CountAsync();

            if (beforToYearCount == 0)
            {
                dto.ToYearQoQ = 0;
            }
            else
            {
                dto.ToYearQoQ = Math.Round(((dto.ToYearCount - beforToYearCount) / (double)beforToYearCount) * 100, 2);
            }

            #endregion

            return dto;
        }

        /// <summary>
        /// 区域受理排行
        /// </summary>
        /// <returns></returns>
        [AllowAnonymous]
        [HttpGet("orderarea-accept-statistics")]
        public async Task<List<OrderAreaAcceptStatisticsDto>> OrderAreaAcceptStatistics(DateTime StartTime, DateTime EndTime)
        {
            EndTime = EndTime.AddDays(1).AddSeconds(-1);

            var list = await _orderRepository.Queryable(false, false, false)
                .Where(x => x.StartTime >= StartTime && x.StartTime <= EndTime && x.Status > EOrderStatus.WaitForAccept)
                .LeftJoin<SystemArea>((it, o) => it.AreaCode.Substring(SqlFunc.MappingColumn<int>("0"), SqlFunc.MappingColumn<int>("6")) == o.Id)
                .GroupBy((it, o) => new
                {
                    AreaCode = it.AreaCode.Substring(SqlFunc.MappingColumn<int>("0"), SqlFunc.MappingColumn<int>("6")),
                    o.AreaName,
                })
                .Select((it, o) => new OrderAreaAcceptStatisticsDto()
                {
                    AreaCode = it.AreaCode.Substring(SqlFunc.MappingColumn<int>("0"), SqlFunc.MappingColumn<int>("6")),
                    AreaName = o.AreaName,
                    AcceptedCount = SqlFunc.AggregateCount(it.AreaCode.Substring(SqlFunc.MappingColumn<int>("0"), SqlFunc.MappingColumn<int>("6"))),
                    CompletionCount = SqlFunc.AggregateSum(SqlFunc.IIF(it.Status >= EOrderStatus.Filed, 1, 0))
                }).MergeTable()
                .Where(x => x.AreaCode != "519800" && x.AreaCode != "519900").OrderByDescending(it => it.AcceptedCount).ToListAsync();
            return list;
        }

        /// <summary>
        /// 区域明细数据
        /// </summary>
        /// <param name="StartTime"></param>
        /// <param name="EndTime"></param>
        /// <param name="AreaCode"></param>
        /// <returns></returns>
        [AllowAnonymous]
        [HttpGet("orderareaaccept-query")]
        public async Task<List<OrderAreaAcceptQueryDto>> OrderAreaAcceptQuery(DateTime StartTime, DateTime EndTime)
        {
            EndTime = EndTime.AddDays(1).AddSeconds(-1);


            var areaList = await _systemAreaRepository.Queryable()
                .Where(x => !x.Id.EndsWith("00"))
                .GroupBy(x => new
                {
                    Id = x.Id.Substring(SqlFunc.MappingColumn<int>("0"), SqlFunc.MappingColumn<int>("6")),
                })
                .Select(x => new
                {
                    Id = x.Id.Substring(SqlFunc.MappingColumn<int>("0"), SqlFunc.MappingColumn<int>("6")),
                })
                .MergeTable()
                .LeftJoin<SystemArea>((it, o) => it.Id == o.Id)
                .Select((it, o) => new
                {
                    Id = it.Id,
                    Name = o.AreaName
                })
                .ToListAsync();

            var list = new List<OrderAreaAcceptQueryDto>();

            foreach (var item in areaList)
            {
                #region 单个获取

                var dto = new OrderAreaAcceptQueryDto();
                dto.AreaCode = item.Id;
                dto.AreaName = item.Name;
                dto.HandlingCount = await _orderRepository.Queryable(false, false, false).Where(x =>
                    x.AreaCode == item.Id && x.StartTime >= StartTime && x.StartTime <= EndTime && x.Status > EOrderStatus.WaitForAccept &&
                    x.Status < EOrderStatus.Filed).CountAsync();

                dto.FiledCount = await _orderRepository.Queryable(false, false, false).Where(x =>
                    x.AreaCode == item.Id && x.StartTime >= StartTime && x.StartTime <= EndTime && x.Status >= EOrderStatus.Filed).CountAsync();

                dto.OverTimeCount = await _orderRepository.Queryable(false, false, false).Where(x =>
                        x.AreaCode == item.Id && x.StartTime >= StartTime && x.StartTime <= EndTime && x.ExpiredStatus == EExpiredStatus.Expired)
                    .CountAsync();

                var hotsPot = await _orderRepository.Queryable(false, false, false)
                    .Where(x => x.AreaCode == item.Id && x.StartTime >= StartTime && x.StartTime <= EndTime && x.Status > EOrderStatus.WaitForAccept)
                    .GroupBy(x => new { x.HotspotId, x.HotspotName })
                    .Select(x => new
                    {
                        HotsPotName = x.HotspotName,
                        HotCount = SqlFunc.AggregateCount(x.HotspotId)
                    }).OrderByDescending(x => x.HotCount).FirstAsync();

                dto.HotspotName = hotsPot?.HotsPotName;

                #region 满意度

                var SatisfiedCount = await _orderRepository.Queryable(false, false, false)
                    .LeftJoin<OrderVisit>((it, o) => it.Id == o.OrderId && o.VisitState == EVisitState.Visited)
                    .Where((it, o) => it.AreaCode == item.Id && o.VisitTime >= StartTime && o.VisitTime <= EndTime
                                      && SqlFunc.JsonField(o.NowEvaluate, "Key") != "1" && SqlFunc.JsonField(o.NowEvaluate, "Key") != "2")
                    .CountAsync();
                var VisitCount = await _orderRepository.Queryable(false, false, false)
                    .LeftJoin<OrderVisit>((it, o) => it.Id == o.OrderId && o.VisitState == EVisitState.Visited)
                    .Where((it, o) => it.AreaCode == item.Id && o.VisitTime >= StartTime && o.VisitTime <= EndTime)
                    .CountAsync();

                if (SatisfiedCount != 0 && VisitCount != 0)
                {
                    dto.SatisfiedRate = Math.Round((SatisfiedCount / (double)VisitCount) * 100, 2);
                }

                #endregion

                list.Add(dto);

                #endregion
            }

            return list;
        }

        /// <summary>
        /// 办理中工单概览
        /// </summary>
        /// <returns></returns>
        [AllowAnonymous]
        [HttpGet("order-handling-query")]
        public async Task<List<OrderDto>> OrderHandlingDetailQuery()
        {
            var list = await _orderRepository
                .Queryable(false, false, false)
                .Where(x => x.Status > EOrderStatus.WaitForAccept && x.StartTime.Value.Date == DateTime.Now.Date)
                //.Where(x => x.Status > EOrderStatus.WaitForAccept && x.StartTime.Value.Date == DateTime.Parse("2024-03-14").Date)
                .OrderByDescending(x => x.StartTime)
                .Take(50)
                .ToListAsync();
            return _mapper.Map<List<OrderDto>>(list);
        }

        /// <summary>
        /// 30天高频事项预警
        /// </summary>
        /// <returns></returns>
        [AllowAnonymous]
        [HttpGet("highmatter-warning")]
        public async Task<List<HighMatterWarningDto>> HighMatterWarning(DateTime StartTime, DateTime EndTime)
        {
            //var endDate = DateTime.Now.Date.AddDays(1).AddSeconds(-1);
            //var startDate = endDate.AddDays(-30).Date;


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


            var list = await _orderRepository.Queryable(false, false, false)
                .Where(x => x.CreationTime >= StartTime && x.CreationTime <= EndTime)
                .Where(x => filterTitle.Any(s => x.Title.Contains(s)) == false)
                .LeftJoin<SystemArea>((it, o) => it.AreaCode.Substring(SqlFunc.MappingColumn<int>("0"), SqlFunc.MappingColumn<int>("6")) == o.Id)
                .GroupBy((it, o) => new
                {
                    it.AcceptTypeCode,
                    it.HotspotId,
                    it.HotspotName,
                    AreaCode = it.AreaCode.Substring(SqlFunc.MappingColumn<int>("0"), SqlFunc.MappingColumn<int>("6")),
                    o.AreaName,
                })
                .Having((it, o) => SqlFunc.AggregateCount(it.HotspotName) >= 5)
                .Select((it, o) => new HighMatterWarningDto()
                {
                    AreaName = o.AreaName,
                    //Title = it.Title,
                    HotspotName = it.HotspotName,
                    SumCount = SqlFunc.AggregateCount(it.HotspotName),
                    Id = SqlFunc.AggregateMin(it.Id)
                })
                .MergeTable()
                //.Where(x=>x.SumCount>=5)
                .LeftJoin<Order>((x, d) => x.Id == d.Id)
                .Select((x, d) => new HighMatterWarningDto()
                {
                    AreaName = x.AreaName,
                    HotspotName = x.HotspotName,
                    Title = d.Title,
                    SumCount = x.SumCount,
                    Id = d.Id,
                }).Take(50).ToListAsync();

            return list;
        }

        /// <summary>
        /// 部门满意度排行榜
        /// </summary>
        /// <param name="StartTime"></param>
        /// <param name="EndTime"></param>
        /// <returns></returns>
        [AllowAnonymous]
        [HttpGet("ordervisit-orgsatisfaction-rank")]
        public async Task<List<OrderVisitOrgSatisfactionRankDto>> OrderVisitOrgSatisfactionRank(DateTime StartTime, DateTime EndTime)
        {
            var list = new List<OrderVisitOrgSatisfactionRankDto>();

            if (_appOptions.Value.IsLuZhou)
            {
                list = await _orderVisitDetailRepository.Queryable()
                      .Includes(x => x.OrderVisit)
                      .Where(x => x.OrderVisit.VisitTime >= StartTime && x.OrderVisit.VisitTime <= EndTime && x.VisitTarget == EVisitTarget.Org &&
                                  x.VisitOrgCode.Length >= 6 && x.OrderVisit.VisitState == EVisitState.Visited)
                      .GroupBy(x => new
                      {
                          VisitOrgCode = x.VisitOrgCode.Substring(SqlFunc.MappingColumn<int>("0"), SqlFunc.MappingColumn<int>("6"))
                      })
                      .Select(x => new OrderVisitOrgSatisfactionRankDto()
                      {
                          VisitOrgCode = x.VisitOrgCode.Substring(SqlFunc.MappingColumn<int>("0"), SqlFunc.MappingColumn<int>("6")),
                          SatisfiedCount =
                              SqlFunc.AggregateSum(SqlFunc.IIF(
                                  SqlFunc.JsonField(x.OrgProcessingResults, "Key") != "1" && SqlFunc.JsonField(x.OrgProcessingResults, "Key") != "2", 1,
                                  0)),
                          VisitCount = SqlFunc.AggregateCount(x.VisitOrgName)
                      }).MergeTable()
                      .LeftJoin<SystemOrganize>((x, so) => x.VisitOrgCode == so.Id)
                      .Select((x, so) => new OrderVisitOrgSatisfactionRankDto()
                      {
                          VisitOrgCode = x.VisitOrgCode,
                          VisitOrgName = so.Name,
                          SatisfiedCount = x.SatisfiedCount,
                          VisitCount = x.VisitCount
                      })
                      .OrderByDescending(x => x.SatisfiedCount).Take(10).ToListAsync();
                list = list.OrderByDescending(x => x.SatisfiedRate).ToList();
            }
            else
            {
                list = await _orderVisitDetailRepository.Queryable()
                    .Includes(x => x.OrderVisit)
                    .Where(x => x.OrderVisit.VisitTime >= StartTime && x.OrderVisit.VisitTime <= EndTime && x.VisitTarget == EVisitTarget.Org &&
                            x.VisitOrgCode.Length >= 6 && x.OrderVisit.VisitState == EVisitState.Visited)
                    .GroupBy(x => new
                    {
                        VisitOrgCode = x.VisitOrgCode.Substring(SqlFunc.MappingColumn<int>("0"), SqlFunc.MappingColumn<int>("6")),
                        x.VisitOrgName
                    })
                    .Select(x => new OrderVisitOrgSatisfactionRankDto()
                    {
                        VisitOrgCode = x.VisitOrgCode.Substring(SqlFunc.MappingColumn<int>("0"), SqlFunc.MappingColumn<int>("6")),
                        VisitOrgName = x.VisitOrgName,
                        SatisfiedCount =
                        SqlFunc.AggregateSum(SqlFunc.IIF(
                            SqlFunc.JsonField(x.OrgProcessingResults, "Key") != "1" && SqlFunc.JsonField(x.OrgProcessingResults, "Key") != "2", 1,
                            0)),
                        VisitCount = SqlFunc.AggregateCount(x.VisitOrgName)
                    }).MergeTable().OrderByDescending(x => x.SatisfiedCount).Take(10).ToListAsync();
                                    list = list.OrderByDescending(x => x.SatisfiedRate).ToList();
            }
			return list;
		}

        /// <summary>
        /// 占比分析
        /// </summary>
        /// <param name="StartTime"></param>
        /// <param name="EndTime"></param>
        /// <param name="IsSource"></param>
        /// <returns></returns>
        [AllowAnonymous]
        [HttpGet("order-source-accepttype-statistics")]
        public async Task<List<OrderSourceAndAcceptTtoeStatisticsDto>> OrderSourceAndAcceptTtoeStatistics(DateTime StartTime, DateTime EndTime,
            bool IsSource)
        {
            EndTime = EndTime.AddDays(1).AddSeconds(-1);

            int SumCount = await _orderRepository.Queryable(false, false, false)
                .Where(x => x.StartTime >= StartTime && x.StartTime <= EndTime && x.Status > EOrderStatus.WaitForAccept).CountAsync();
            if (IsSource)
            {
                var list = await _orderRepository.Queryable(false, false, false)
                    .Where(x => x.StartTime >= StartTime && x.StartTime <= EndTime && x.Status > EOrderStatus.WaitForAccept)
                    .GroupBy(x => new { x.SourceChannelCode, x.SourceChannel })
                    .Select(x => new OrderSourceAndAcceptTtoeStatisticsDto()
                    {
                        Name = x.SourceChannel,
                        SumCount = SumCount,
                        HasCount = SqlFunc.AggregateCount(x.SourceChannel)
                    }).ToListAsync();
                return list;
            }
            else
            {
                var list = await _orderRepository.Queryable(false, false, false)
                    .Where(x => x.StartTime >= StartTime && x.StartTime <= EndTime && x.Status > EOrderStatus.WaitForAccept)
                    .GroupBy(x => new { x.AcceptTypeCode, x.AcceptType })
                    .Select(x => new OrderSourceAndAcceptTtoeStatisticsDto()
                    {
                        Name = x.AcceptType,
                        SumCount = SumCount,
                        HasCount = SqlFunc.AggregateCount(x.AcceptTypeCode),
                    }).ToListAsync();
                return list;
            }
        }

        /// <summary>
        /// 二次办理统计
        /// </summary>
        /// <param name="StartTime"></param>
        /// <param name="EndTime"></param>
        /// <returns></returns>
        [AllowAnonymous]
        [HttpGet("order-secondary-statistics")]
        public async Task<SecondaryProcessingOrderStatisticsDto> OrderSecondaryStatistics(DateTime StartTime, DateTime EndTime)
        {
            DateTime? dateTime = DateTime.Now;
            EndTime = EndTime.AddDays(1).AddSeconds(-1);
            var data = new SecondaryProcessingOrderStatisticsDto
            {
                OrderCount = await _orderSecondaryHandlingRepository.Queryable()
                 .Where(x => x.AuditTime >= StartTime && x.AuditTime <= EndTime && x.State != ESecondaryHandlingState.NotApply
                 && x.State != ESecondaryHandlingState.Apply && x.State != ESecondaryHandlingState.Refuse).CountAsync(),

                OrderOverdueCount = await _orderSecondaryHandlingRepository.Queryable()
                .Includes(x => x.Order)
                 //.Where(x => x.Order.Status < EOrderStatus.Filed)
                 .Where(x => x.Order.ExpiredTime != null &&
                        (((x.Order.Status == EOrderStatus.Filed || x.Order.Status == EOrderStatus.Published || x.Order.Status == EOrderStatus.Visited) &&
                           x.Order.FiledTime >= x.Order.ExpiredTime) ||
                         ((x.Order.Status != EOrderStatus.Filed && x.Order.Status != EOrderStatus.Published && x.Order.Status != EOrderStatus.Visited) &&
                          dateTime >= x.Order.ExpiredTime.Value)))
                 .Where(x => x.AuditTime >= StartTime && x.AuditTime <= EndTime
                 && x.State != ESecondaryHandlingState.NotApply
                 && x.State != ESecondaryHandlingState.Apply && x.State != ESecondaryHandlingState.Refuse)
                 .CountAsync(),

                OrderSoonOverdueCount = await _orderSecondaryHandlingRepository.Queryable()
                .Includes(x => x.Order)
                 .Where(x => x.Order.Status < EOrderStatus.Filed && dateTime > x.Order.NearlyExpiredTime && dateTime < x.Order.ExpiredTime)
                 .Where(x => x.AuditTime >= StartTime && x.AuditTime <= EndTime
                 && x.State != ESecondaryHandlingState.NotApply
                 && x.State != ESecondaryHandlingState.Apply && x.State != ESecondaryHandlingState.Refuse)
                 .CountAsync()
            };
            var da = await _orderSecondaryHandlingRepository.Queryable()
                .LeftJoin<OrderVisit>((os, ov) => os.OrderId == ov.OrderId)
                    .LeftJoin<OrderVisitDetail>((os, ov, od) => ov.Id == od.VisitId)
                    .Where((os, ov, od) => ov.VisitState == EVisitState.Visited && od.VisitTarget == EVisitTarget.Org && ov.VisitTime >= StartTime && ov.VisitTime <= EndTime
                    && os.State != ESecondaryHandlingState.NotApply
                    && os.State != ESecondaryHandlingState.Apply && os.State != ESecondaryHandlingState.Refuse)
                    .Select((os, ov, od) => new SecondarySatisfactionDto()
                    {
                        Count = SqlFunc.AggregateCount(os.Id),
                        NoSatisfiedCount = SqlFunc.AggregateSum(SqlFunc.IIF(SqlFunc.JsonField(od.OrgProcessingResults, "Key") == "1"
                        || SqlFunc.JsonField(od.OrgProcessingResults, "Key") == "2", 1, 0)),//不满意数
                    }).FirstAsync();
            if (da != null)
                data.SatisfactionRate = da.SatisfiedRate;
            return data;
        }

        /// <summary>
        /// 二次办理中工单概览
        /// </summary>
        /// <returns></returns>
        [AllowAnonymous]
        [HttpGet("order-secondary-handling-query")]
        public async Task<List<OrderSecondaryHandlingDto>> OrderSecondaryHandlingDetailQuery()
        {
            var quer = await _orderSecondaryHandlingRepository.Queryable()
                 .Includes(x => x.Order)
                  .Where(x => x.CreationTime.Date == DateTime.Now.Date)
                  .OrderByDescending(x => x.CreationTime)
                  .Take(50)
                 .ToListAsync();
            return _mapper.Map<List<OrderSecondaryHandlingDto>>(quer);
        }
    }
}