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

using Hotline.JudicialManagement;
using Hotline.Orders;
using Hotline.Settings;
using Hotline.Share.Dtos.JudicialManagement;
using Hotline.Share.Enums.Order;
using MapsterMapper;
using Microsoft.AspNetCore.Authorization;
using Microsoft.AspNetCore.Mvc;
using SqlSugar;
using XF.Domain.Repository;

namespace Hotline.Api.Controllers.Bigscreen
{
    /// <summary>
    /// 司法行政监督管理大屏  
    /// </summary>
    public class EnforcementScreenController : BaseController
    {
        private readonly IMapper _mapper;
        private readonly IRepository<EnforcementOrders> _enforcementOrdersRepository;
        private readonly IRepository<SystemArea> _systemAreaRepository;

        /// <summary>
        /// 
        /// </summary>
        /// <param name="mapper"></param>
        /// <param name="enforcementOrdersRepository"></param>
        /// <param name="systemAreaRepository"></param>
        public EnforcementScreenController(IMapper mapper,
            IRepository<EnforcementOrders> enforcementOrdersRepository,
            IRepository<SystemArea> systemAreaRepository)
        {
            _mapper = mapper;
            _enforcementOrdersRepository = enforcementOrdersRepository;
            _systemAreaRepository = systemAreaRepository;
        }

        /// <summary>
        /// 左上工单数据模块
        /// </summary>
        /// <returns></returns>
        [AllowAnonymous]
        [HttpGet("order-module-statistics")]
        public async Task<OrderModuleDto> GetOrderModuleStatisticsAsync(DateTime StartDate, DateTime EndDate)
        {
            EndDate = EndDate.AddDays(1).AddSeconds(-1);
            var dto = new OrderModuleDto();

            var data = await _enforcementOrdersRepository.Queryable()
                .LeftJoin<Order>((x, o) => x.Id == o.Id)
                .Where((x, o) => o.Id != null)
                .Where((x, o) => o.StartTime >= StartDate && o.StartTime <= EndDate)
                .Select((x, o) => new
                {
                    x.Id,
                    x.IsEnforcementOrder,
                    x.IsTheClueTrue,
                    x.IsPassTheBuckOrder
                })
                .MergeTable()
                .Select(d => new OrderModuleDto
                {
                    OrderCountNum = SqlFunc.AggregateSum(SqlFunc.IIF(d.Id != null, 1, 0)),
                    EnforcementOrderNum = SqlFunc.AggregateSum(SqlFunc.IIF(d.IsEnforcementOrder, 1, 0)),
                    TheClueIsTrueNum = SqlFunc.AggregateSum(SqlFunc.IIF(d.IsTheClueTrue.HasValue && d.IsTheClueTrue.Value == true, 1, 0)),
                    TheClueIsNotTrueNum = SqlFunc.AggregateSum(SqlFunc.IIF(d.IsTheClueTrue.HasValue && d.IsTheClueTrue.Value == false, 1, 0)),
                    PassTheBuckOrderNum = SqlFunc.AggregateSum(SqlFunc.IIF(d.IsPassTheBuckOrder, 1, 0))
                })
                .ToListAsync();
            if (data != null && data.Count > 0)
            {
                dto.OrderCountNum = data[0].OrderCountNum;
                dto.EnforcementOrderNum = data[0].EnforcementOrderNum;
                dto.TheClueIsTrueNum = data[0].TheClueIsTrueNum;
                dto.TheClueIsNotTrueNum = data[0].TheClueIsNotTrueNum;
                dto.PassTheBuckOrderNum = data[0].PassTheBuckOrderNum;
            }

            dto.SatisfiedCount = await _enforcementOrdersRepository.Queryable()
                .LeftJoin<OrderVisit>((x, o) => x.Id == o.OrderId)
                .LeftJoin<OrderVisitDetail>((x, o, p) => o.Id == p.VisitId)
               .Where((x, o, p) => o.VisitTime >= StartDate && o.VisitTime <= EndDate && p.VisitTarget == EVisitTarget.Org)
               .Where((x, o, p) => o.VisitState == EVisitState.Visited && !string.IsNullOrEmpty(p.VisitOrgCode))
               .Where((x, o, p) => SqlFunc.JsonField(o.NowEvaluate, "Key") != "1" && SqlFunc.JsonField(o.NowEvaluate, "Key") != "2")
               .CountAsync();

            return dto;
        }

        /// <summary>
        /// 左中受理类型
        /// </summary>
        /// <param name="StartDate"></param>
        /// <param name="EndDate"></param>
        /// <returns></returns>
        [AllowAnonymous]
        [HttpGet("order-accept-type-statistics")]
        public async Task<List<OrderAcceptTypeStatisticsDto>> GetOrderAcceptTypeStatisticsAsync(DateTime StartDate, DateTime EndDate)
        {
            EndDate = EndDate.AddDays(1).AddSeconds(-1);

            var list = await _enforcementOrdersRepository.Queryable()
                .LeftJoin<Order>((x, o) => x.Id == o.Id)
                .Where((x, o) => o.Id != null && o.Status > EOrderStatus.Handling && !string.IsNullOrEmpty(o.AcceptType))
                .Where((x, o) => o.StartTime >= StartDate && o.StartTime <= EndDate)
                .Select((x, o) => new
                {
                    x.Id,
                    o.AcceptType,
                    o.Status,
                    o.ExpiredStatus
                })
                .MergeTable()
                 .GroupBy(x => x.AcceptType)
                .Select(x => new OrderAcceptTypeStatisticsDto
                {
                    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>
        /// <param name="StartDate"></param>
        /// <param name="EndDate"></param>
        /// <param name="AreaCode"></param>
        /// <returns></returns>
        [AllowAnonymous]
        [HttpGet("order-event-type-statistics")]
        public async Task<List<OrderEventTypeStatisticsDto>> GetOrderEventTypeStatisticsAsync(DateTime StartDate, DateTime EndDate, string AreaCode)
        {
            EndDate = EndDate.AddDays(1).AddSeconds(-1);
            if (AreaCode.Length == 6 && AreaCode.IndexOf("00") == 4)
                AreaCode = AreaCode.Remove(4);

            var list = await _enforcementOrdersRepository.Queryable()
               .LeftJoin<Order>((x, o) => x.Id == o.Id)
               .LeftJoin<JudicialComplaintsEventType>((x, o, j) => x.EventTypeOneLevelId == j.Id)
               .Where((x, o, j) => o.Id != null && x.EventTypeOneLevelId != null && j.Id != null)
               .Where((x, o, j) => o.StartTime >= StartDate && o.StartTime <= EndDate && o.AreaCode.StartsWith(AreaCode))
               .Select((x, o, j) => new
               {
                   x.Id,
                   EventTypeId = x.EventTypeOneLevelId,
                   j.EventTypeName
               })
               .MergeTable()
                .GroupBy(p => new { p.EventTypeId, p.EventTypeName })
                .Select(p => new OrderEventTypeStatisticsDto
                {
                    EventTypeId = p.EventTypeId,
                    EventTypeName = p.EventTypeName,
                    SumCount = SqlFunc.AggregateCount(p.Id)
                })
                .OrderByDescending(p => p.SumCount)
                .Take(5)
                .ToListAsync();

            return list;
        }

        /// <summary>
        /// 中上区域地图数据
        /// </summary>
        /// <param name="StartDate"></param>
        /// <param name="EndDate"></param>
        /// <returns></returns>
        [AllowAnonymous]
        [HttpGet("order-area-query")]
        public async Task<List<OrderAreaQueryDto>> GetOrderAreaQueryAsync(DateTime StartDate, DateTime EndDate)
        {
            EndDate = EndDate.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
                {
                    it.Id,
                    Name = o.AreaName
                })
                .ToListAsync();

            var list = new List<OrderAreaQueryDto>();

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

                var dto = new OrderAreaQueryDto
                {
                    AreaCode = item.Id,
                    AreaName = item.Name
                };

                var data = await _enforcementOrdersRepository.Queryable()
                .LeftJoin<Order>((x, o) => x.Id == o.Id)
                .Where((x, o) => o.Id != null)
                .Where((x, o) => o.StartTime >= StartDate && o.StartTime <= EndDate && o.AreaCode.StartsWith(item.Id))
                .Select((x, o) => new
                {
                    x.Id,
                    x.IsEnforcementOrder,
                    x.IsTheClueTrue,
                    x.IsPassTheBuckOrder
                })
                .MergeTable()
                .Select(d => new OrderModuleDto
                {
                    OrderCountNum = SqlFunc.AggregateSum(SqlFunc.IIF(d.Id != null, 1, 0)),
                    EnforcementOrderNum = SqlFunc.AggregateSum(SqlFunc.IIF(d.IsEnforcementOrder, 1, 0)),
                    TheClueIsTrueNum = SqlFunc.AggregateSum(SqlFunc.IIF(d.IsTheClueTrue.HasValue && d.IsTheClueTrue.Value == true, 1, 0)),
                    TheClueIsNotTrueNum = SqlFunc.AggregateSum(SqlFunc.IIF(d.IsTheClueTrue.HasValue && d.IsTheClueTrue.Value == false, 1, 0)),
                    PassTheBuckOrderNum = SqlFunc.AggregateSum(SqlFunc.IIF(d.IsPassTheBuckOrder, 1, 0))
                })
                .ToListAsync();
                if (data != null && data.Count > 0)
                {
                    dto.OrderCountNum = data[0].OrderCountNum;
                    dto.EnforcementOrderNum = data[0].EnforcementOrderNum;
                    dto.TheClueIsTrueNum = data[0].TheClueIsTrueNum;
                    dto.TheClueIsNotTrueNum = data[0].TheClueIsNotTrueNum;
                    dto.PassTheBuckOrderNum = data[0].PassTheBuckOrderNum;
                }

                list.Add(dto);

                #endregion
            }
            return list;
        }

        /// <summary>
        /// 中下行政执法工单概览
        /// </summary>
        /// <returns></returns>
        [AllowAnonymous]
        [HttpGet("order-overview")]
        public async Task<List<OrderOverviewDto>> GetOrderOverviewAsync()
        {
            var list = await _enforcementOrdersRepository.Queryable()
                  .LeftJoin<Order>((x, o) => x.Id == o.Id)
                  .LeftJoin<SystemArea>((x, o, p) => o.AreaCode == p.Id)
                  .Where((x, o, p) => o.Id != null)
                  .Where((x, o) => o.Status > EOrderStatus.WaitForAccept && o.StartTime.Value.Date == DateTime.Now.Date)
                  .Select((x, o, p) => new OrderOverviewDto
                  {
                      Id = o.Id,
                      WorkflowId = o.WorkflowId,
                      SourceChannel = o.SourceChannel,
                      SourceChannelCode = o.SourceChannelCode,
                      Title = o.Title,
                      AcceptType = o.AcceptType,
                      AcceptTypeCode = o.AcceptTypeCode,
                      HotspotId = o.HotspotId,
                      HotspotName = o.HotspotName,
                      ActualHandleOrgName = o.ActualHandleOrgName,
                      ActualHandleOrgCode = o.ActualHandleOrgCode,
                      Status = o.Status,
                      ActualHandleTime = o.ActualHandleTime,
                      ExpiredTime = o.ExpiredTime,
                      NearlyExpiredTime = o.NearlyExpiredTime,
                      StartTime = o.StartTime,
                      AreaName = p.AreaName
                  })
                  .MergeTable()
                  .OrderByDescending(d => d.StartTime)
                  .Take(50)
                  .ToListAsync();

            return _mapper.Map<List<OrderOverviewDto>>(list);
        }

        /// <summary>
        /// 右上部门满意度排行榜
        /// </summary>
        /// <param name="StartDate"></param>
        /// <param name="EndDate"></param>
        /// <returns></returns>
        [AllowAnonymous]
        [HttpGet("order-visitorg-statistics")]
        public async Task<List<OrderVisitOrgStatisticsDto>> GetOrderVisitOrgStatisticsAsync(DateTime StartDate, DateTime EndDate)
        {
            EndDate = EndDate.AddDays(1).AddSeconds(-1);

            var list = await _enforcementOrdersRepository.Queryable()
                 .LeftJoin<OrderVisit>((x, o) => x.Id == o.OrderId)
                 .LeftJoin<OrderVisitDetail>((x, o, p) => o.Id == p.VisitId)
                .Where((x, o, p) => o.VisitTime >= StartDate && o.VisitTime <= EndDate && p.VisitTarget == EVisitTarget.Org &&
                o.VisitState == EVisitState.Visited && !string.IsNullOrEmpty(p.VisitOrgCode) && p.VisitOrgCode.Length >= 6)
                .Select((x, o, p) => new
                {
                    p.VisitOrgCode,
                    p.VisitOrgName,
                    p.OrgProcessingResults
                })
                .MergeTable()
                .GroupBy(x => new
                {
                    VisitOrgCode = x.VisitOrgCode.Substring(SqlFunc.MappingColumn<int>("0"), SqlFunc.MappingColumn<int>("6")),
                    x.VisitOrgName
                })
                 .Select(x => new OrderVisitOrgStatisticsDto()
                 {
                     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();

            return list;
        }

        /// <summary>
        /// 右中工单月份趋势图
        /// </summary>
        /// <returns></returns>
        [AllowAnonymous]
        [HttpGet("order-monthly-trend")]
        public async Task<List<OrderMonthlyTrendDto>> GetOrderMonthlyTrendStatisticsAsync(int Year)
        {
            if (Year == 0)
                Year = DateTime.Now.Year;

            int monthCount = 12;
            DateTime StartDate = DateTime.Parse(Year + "-01-01 00:00:00");
            DateTime EndDate = StartDate.AddYears(1).AddSeconds(-1);
            if (Year == DateTime.Now.Year)
            {
                monthCount = DateTime.Now.Month;
                EndDate = DateTime.Now;
            }

            var queryData = await _enforcementOrdersRepository.Queryable()
                       .LeftJoin<Order>((x, o) => x.Id == o.Id)
                       .Where((x, o) => o.Id != null)
                       .Where((x, o) => o.Status > EOrderStatus.WaitForAccept && o.StartTime >= StartDate && o.StartTime <= EndDate)
                       .Select((x, o) => new
                       {
                           o.Id,
                           Month = o.StartTime.Value.Month
                       })
                       .MergeTable()
                       .GroupBy(x => x.Month)
                       .Select(x => new OrderMonthlyTrendDto
                       {
                           Month = x.Month,
                           SumCount = SqlFunc.AggregateCount(x.Month)
                       })
                       .ToListAsync();

            var orderMonthlyTrends = new List<OrderMonthlyTrendDto>();
            for (int i = 1; i <= monthCount; i++)
            {
                var data = queryData.Find(p => p.Month == i);
                var SumCount = 0;
                if (data != null)
                    SumCount = data.SumCount;
                orderMonthlyTrends.Add(new OrderMonthlyTrendDto { Month = i, SumCount = SumCount });
            }

            return orderMonthlyTrends;
        }

        /// <summary>
        /// 右下占比分析
        /// </summary>
        /// <param name="StartDate"></param>
        /// <param name="EndDate"></param>
        /// <param name="IsSource"></param>
        /// <returns></returns>
        [AllowAnonymous]
        [HttpGet("order-proportion-statistics")]
        public async Task<List<OrderProportionStatisticsDto>> GetOrderProportionStatisticsAsync(DateTime StartDate, DateTime EndDate, bool IsSource)
        {
            EndDate = EndDate.AddDays(1).AddSeconds(-1);

            var SumCount = await _enforcementOrdersRepository.Queryable()
               .LeftJoin<Order>((x, o) => x.Id == o.Id)
                .Where((x, o) => o.Id != null)
               .Where((x, o) => o.StartTime >= StartDate && o.StartTime <= EndDate && o.Status > EOrderStatus.WaitForAccept)
               .CountAsync();

            if (IsSource)
            {
                var list = await _enforcementOrdersRepository.Queryable()
                .LeftJoin<Order>((x, o) => x.Id == o.Id)
                .Where((x, o) => o.Id != null)
                .Where((x, o) => o.StartTime >= StartDate && o.StartTime <= EndDate && o.Status > EOrderStatus.WaitForAccept)
                .GroupBy((x, o) => new { o.SourceChannelCode, o.SourceChannel })
                .Select((x, o) => new OrderProportionStatisticsDto()
                {
                    Name = o.SourceChannel,
                    SumCount = SumCount,
                    HasCount = SqlFunc.AggregateCount(o.SourceChannel)
                })
                .ToListAsync();
                return list;
            }
            else
            {
                var list = await _enforcementOrdersRepository.Queryable()
                .LeftJoin<Order>((x, o) => x.Id == o.Id)
                .Where((x, o) => o.Id != null)
                .Where((x, o) => o.StartTime >= StartDate && o.StartTime <= EndDate && o.Status > EOrderStatus.WaitForAccept)
                .GroupBy((x, o) => new { o.AcceptTypeCode, o.AcceptType })
                .Select((x, o) => new OrderProportionStatisticsDto()
                {
                    Name = o.AcceptType,
                    SumCount = SumCount,
                    HasCount = SqlFunc.AggregateCount(o.AcceptTypeCode),
                })
                .ToListAsync();
                return list;
            }
        }

    }
}