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

using Hotline.Application.JudicialManagement;
using Hotline.Api.Filter;
using Exam.Infrastructure.Extensions;
using Hotline.Application.JudicialManagement;
using Hotline.JudicialManagement;
using Hotline.Orders;
using Hotline.Settings;
using Hotline.Share.Dtos.JudicialManagement;
using Hotline.Share.Enums.Order;
using Hotline.Share.Tools;
using MapsterMapper;
using Microsoft.AspNetCore.Authorization;
using Microsoft.AspNetCore.Mvc;
using SqlSugar;
using XF.Domain.Repository;

namespace Hotline.Api.Controllers.Bigscreen
{
    /// <summary>
    /// 司法行政监督管理大屏
    /// </summary>
    [ServiceFilter(typeof(ClientIpFilterAttribute))]
    public class JudicialManagementScreenController : BaseController
    {
        private readonly IMapper _mapper;
        private readonly IRepository<SystemArea> _systemAreaRepository;
        private readonly IRepository<JudicialManagementOrders> _judicialManagementOrdersRepository;
        private readonly IEnforcementApplication _enforcementApplication;

        /// <summary>
        /// 
        /// </summary>
        /// <param name="mapper"></param>
        /// <param name="systemAreaRepository"></param>
        /// <param name="judicialManagementOrdersRepository"></param>
        public JudicialManagementScreenController(IMapper mapper,
            IRepository<SystemArea> systemAreaRepository,
            IRepository<JudicialManagementOrders> judicialManagementOrdersRepository,
            IEnforcementApplication enforcementApplication)
        {
            _mapper = mapper;
            _systemAreaRepository = systemAreaRepository;
            _judicialManagementOrdersRepository = judicialManagementOrdersRepository;
            this._enforcementApplication = enforcementApplication;
        }

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

            var data = await _judicialManagementOrdersRepository.Queryable().
                LeftJoin<Order>((o, t) => o.Id == t.Id)
                .Where((o, t) => o.CreationTime >= StartTime && o.CreationTime <= EndTime && o.IsItCounted == true)
                .WhereIF(!string.IsNullOrEmpty(AreaCode), (o, t) => o.AreaCode != null && o.AreaCode.StartsWith(AreaCode))
                .Select((o, t) => new
                {
                    o.Id,
                    o.IsEnforcementOrder,
                    o.IsTheClueTrue,
                    o.IsPassTheBuckOrder
                })
                .MergeTable()
                .Select(d => new OrderModuleDto
                {
                    OrderCountNum = SqlFunc.AggregateSum(SqlFunc.IIF(d.Id != null, 1, 0)),
                    EnforcementOrderNum = SqlFunc.AggregateSum(SqlFunc.IIF(d.IsEnforcementOrder.HasValue && d.IsEnforcementOrder.Value == true, 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 == true, 1, 0)),
                    ToBeVerifiedOrderCount = SqlFunc.AggregateCount(SqlFunc.IIF(d.IsEnforcementOrder == null, 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 _judicialManagementOrdersRepository.Queryable()
                .LeftJoin<OrderVisit>((x, o) => x.Id == o.OrderId)
                .LeftJoin<OrderVisitDetail>((x, o, p) => o.Id == p.VisitId)
               .Where((x, o, p) => o.VisitTime >= StartTime && o.VisitTime <= EndTime && 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="StartTime"></param>
        /// <param name="EndTime"></param>
        /// <param name="AreaCode"></param>
        /// <returns></returns>
        [AllowAnonymous]
        [HttpGet("order-event-type-statistics")]
        public async Task<List<OrderEventTypeStatisticsDto>> GetOrderEventTypeStatisticsAsync(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 _judicialManagementOrdersRepository.Queryable()
               .LeftJoin<JudicialComplaintsEventType>((o, j) => o.EventTypeOneLevelId == j.Id)
               .Where((o, j) => o.EventTypeOneLevelId != null && j.Id != null && o.IsItCounted == true)
               .Where((o, j) => o.CreationTime >= StartTime && o.CreationTime <= EndTime && o.AreaCode.StartsWith(AreaCode))
               .Select((o, j) => new
               {
                   o.Id,
                   EventTypeId = o.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(10)
                .ToListAsync();

            return list;
        }

        /// <summary>
        /// 中上区域地图数据
        /// </summary>
        /// <param name="StartTime"></param>
        /// <param name="EndTime"></param>
        /// <returns></returns>
        [AllowAnonymous]
        [HttpGet("order-area-query")]
        public async Task<List<OrderAreaQueryDto>> GetOrderAreaQueryAsync(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
                {
                    it.Id,
                    Name = o.AreaName,
                    ParentId = o.ParentId
                })
                .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 _judicialManagementOrdersRepository.Queryable().
                 LeftJoin<Order>((o, t) => o.Id == t.Id)
                 .Where((o, t) => o.CreationTime >= StartTime && o.CreationTime <= EndTime && o.IsItCounted == true)
                 .WhereIF(!string.IsNullOrEmpty(item.ParentId), (o, t) => o.AreaCode != null && o.AreaCode.StartsWith(item.ParentId))
                 .Select((o, t) => new
                 {
                     o.Id,
                     o.IsEnforcementOrder,
                     o.IsTheClueTrue,
                     o.IsPassTheBuckOrder
                 })
                 .MergeTable()
                 .Select(d => new OrderModuleDto
                 {
                     OrderCountNum = SqlFunc.AggregateSum(SqlFunc.IIF(d.Id != null, 1, 0)),
                     EnforcementOrderNum = SqlFunc.AggregateSum(SqlFunc.IIF(d.IsEnforcementOrder.HasValue && d.IsEnforcementOrder.Value == true, 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 == true, 1, 0)),
                     ToBeVerifiedOrderCount = SqlFunc.AggregateCount(SqlFunc.IIF(d.IsEnforcementOrder == null, 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 _judicialManagementOrdersRepository.Queryable()
                  .LeftJoin<SystemArea>((o, s) => o.AreaCode == s.Id)
                  .Where((o, s) => o.CreationTime.Date >= DateTime.Now.Date && o.IsItCounted == true)
                  .Select((o, s) => new
                  {
                      o.Id,
                      o.SourceChannel,
                      o.SourceChannelCode,
                      o.Title,
                      o.AcceptType,
                      o.AcceptTypeCode,
                      o.HotspotId,
                      o.HotspotName,
                      o.EnforcementOrdersHandler,
                      s.AreaName,
                      o.CreationTime
                  })
                  .OrderByDescending(o => o.CreationTime)
                  .Take(50)
                  .ToListAsync();

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

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

            var list = await _judicialManagementOrdersRepository.Queryable()
                .LeftJoin<Order>((x, o) => x.Id == o.Id)
                 .LeftJoin<OrderVisit>((x, o, v) => x.Id == v.OrderId)
                 .LeftJoin<OrderVisitDetail>((x, o, v, p) => v.Id == p.VisitId)
                .Where((x, o, v, p) => v.VisitTime >= StartTime && v.VisitTime <= EndTime && p.VisitTarget == EVisitTarget.Org &&
                v.VisitState == EVisitState.Visited && !string.IsNullOrEmpty(p.VisitOrgCode) && p.VisitOrgCode.Length >= 6 && x.IsItCounted == true)
                .WhereIF(!string.IsNullOrEmpty(AreaCode), (x, o, v, p) => o.AreaCode != null && o.AreaCode.StartsWith(AreaCode))
                .Select((x, o, v, 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 _judicialManagementOrdersRepository.Queryable()
                       .Where(o => o.CreationTime >= StartDate && o.CreationTime <= EndDate && o.IsItCounted == true)
                       .Select(o => new
                       {
                           o.Id,
                           Month = o.CreationTime.Month,
                           IsEnforcementOrder = o.IsEnforcementOrder
                       })
                       .MergeTable()
                       .GroupBy(x => x.Month)
                       .Select(x => new OrderMonthlyTrendDto
                       {
                           Month = x.Month,
                           EnforcementOrderNum = SqlFunc.AggregateSum(SqlFunc.IIF(x.IsEnforcementOrder.HasValue && x.IsEnforcementOrder.Value == true, 1, 0)),
                           UnEnforcementOrderNum = SqlFunc.AggregateSum(SqlFunc.IIF(x.IsEnforcementOrder.HasValue && x.IsEnforcementOrder.Value == false, 1, 0))
                       })
                       .ToListAsync();

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

            }

            return orderMonthlyTrends;
        }

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

            var SumCount = await _judicialManagementOrdersRepository.Queryable()
               .Where(o => o.CreationTime >= StartTime && o.CreationTime <= EndTime && o.IsItCounted == true)
               .CountAsync();

            if (IsSource)
            {
                var list = await _judicialManagementOrdersRepository.Queryable()
                .Where(o => o.CreationTime >= StartTime && o.CreationTime <= EndTime && o.IsItCounted == true)
                .GroupBy(o => new { o.SourceChannelCode, o.SourceChannel })
                .Select(o => new OrderProportionStatisticsDto()
                {
                    Name = o.SourceChannel,
                    SumCount = SumCount,
                    HasCount = SqlFunc.AggregateCount(o.SourceChannel)
                })
                .ToListAsync();
                return list;
            }
            else
            {
                var list = await _judicialManagementOrdersRepository.Queryable()
               .Where(o => o.CreationTime >= StartTime && o.CreationTime <= EndTime && o.IsItCounted == true)
                .GroupBy(o => new { o.AcceptTypeCode, o.AcceptType })
                .Select(o => new OrderProportionStatisticsDto()
                {
                    Name = o.AcceptType,
                    SumCount = SumCount,
                    HasCount = SqlFunc.AggregateCount(o.AcceptTypeCode),
                })
                .ToListAsync();
                return list;
            }
        }

        /// <summary>
        /// 执法领域统计
        /// </summary>
        /// <param name="queryIndustryOrderStatisticsRateRequest"></param>
        /// <returns></returns>
        [AllowAnonymous]
        [HttpGet("order-industry-statistics")]
        public async Task<List<IndustryOrderStaticsRateDto>> QueryIndustryOrderStaticsRateAsync([FromQuery] QueryIndustryOrderStatisticsRateRequest queryIndustryOrderStatisticsRateRequest)
        {
            return await _enforcementApplication.QueryIndustryOrderStaticsRate(queryIndustryOrderStatisticsRateRequest);
        }

        /// <summary>
        /// 问题类型统计
        /// </summary>
        /// <param name="queryEventTypeOrderStaticsRequest"></param>
        /// <returns></returns>
        [AllowAnonymous]
        [HttpGet("order-eventType-statistics")]
        public async Task<List<EventTypeOrderStaticsDto>> QueryIndustryOrderStaticsRateAsync([FromQuery] QueryEventTypeOrderStaticsRequest queryEventTypeOrderStaticsRequest)
        {
            return await _enforcementApplication.QueryEventTypeOrderStatics(queryEventTypeOrderStaticsRequest);
        }
    }
}