hotline/src/Hotline.Api/Controllers/EnforcementOrderController.cs

using Hotline.Application.FlowEngine;
using Hotline.Caching.Interfaces;
using Hotline.FlowEngine.WorkflowModules;
using Hotline.JudicialManagement;
using Hotline.Orders;
using Hotline.Repository.SqlSugar.Extensions;
using Hotline.Settings;
using Hotline.Settings.TimeLimits;
using Hotline.Share.Dtos;
using Hotline.Share.Dtos.JudicialManagement;
using Hotline.Share.Dtos.Order;
using Hotline.Share.Enums.Order;
using MapsterMapper;
using Microsoft.AspNetCore.Authorization;
using Microsoft.AspNetCore.Mvc;
using Org.BouncyCastle.Utilities;
using Polly.Caching;
using SqlSugar;
using XF.Domain.Authentications;
using XF.Domain.Exceptions;
using XF.Domain.Repository;
using XF.Utility.EnumExtensions;

namespace Hotline.Api.Controllers
{
    public class EnforcementOrderController : BaseController
    {
        private readonly IRepository<EnforcementOrderHander> _enforcementOrderHanderRepository;
        private readonly IRepository<EnforcementOrders> _enforcementOrdersRepository;
        private readonly IRepository<JudicialComplaintsEventType> _judicialComplaintsEventTypeRepository;
        private readonly IRepository<EnforcementHotspot> _enforcementHotspotRepository;
        private readonly IMapper _mapper;
        private readonly IWorkflowApplication _workflowApplication;
        private readonly ISystemDicDataCacheManager _sysDicDataCacheManager;
        private readonly IRepository<SystemOrganize> _systemOrganizeRepository;
        private readonly ISessionContext _sessionContext;
        private readonly IRepository<OrderVisitDetail> _orderVisitDetailRepository;
        private readonly IRepository<SystemArea> _systemAreaRepository;

        public EnforcementOrderController(IRepository<EnforcementOrderHander> enforcementOrderHanderRepository,
         IRepository<EnforcementOrders> enforcementOrdersRepository,
         IRepository<JudicialComplaintsEventType> judicialComplaintsEventTypeRepository,
         IRepository<EnforcementHotspot> enforcementHotspotRepository,
         IMapper mapper,
         IWorkflowApplication workflowApplication,
         ISystemDicDataCacheManager sysDicDataCacheManager,
         IRepository<SystemOrganize> systemOrganizeRepository,
         ISessionContext sessionContext,
         IRepository<OrderVisitDetail> orderVisitDetailRepository,
         IRepository<SystemArea> systemAreaRepository)
        {
            _enforcementOrderHanderRepository = enforcementOrderHanderRepository;
            _enforcementOrdersRepository = enforcementOrdersRepository;
            _judicialComplaintsEventTypeRepository = judicialComplaintsEventTypeRepository;
            _enforcementHotspotRepository = enforcementHotspotRepository;
            _mapper = mapper;
            _workflowApplication = workflowApplication;
            _sysDicDataCacheManager = sysDicDataCacheManager;
            _systemOrganizeRepository = systemOrganizeRepository;
            _sessionContext = sessionContext;
            _orderVisitDetailRepository = orderVisitDetailRepository;
            _systemAreaRepository = systemAreaRepository;
        }

        /// <summary>
        /// 司法行政监督管理工单查询
        /// </summary>
        /// <param name="dto"></param>
        /// <returns></returns>
        [HttpGet("getorderlist")]
        public async Task<PagedDto<EnforcementOrderListDto>> GetOrderList([FromQuery] QueryEnforcementOrderDto dto)
        {
            var (total, items) = await _enforcementOrdersRepository.Queryable()
           .Includes(x => x.Order)
           .Where(d => d.Order.Id != null)
           .WhereIF(dto.IsEnforcementOrder.HasValue, d => d.IsEnforcementOrder == dto.IsEnforcementOrder)//是否行政执法类
           .WhereIF(dto.IsPassTheBuckOrder.HasValue, d => d.IsPassTheBuckOrder == dto.IsPassTheBuckOrder)//是否推诿
           .WhereIF(dto.IsTheClueTrue.HasValue, d => d.IsTheClueTrue == dto.IsTheClueTrue)//线索是否属实
           .WhereIF(!string.IsNullOrEmpty(dto.EventTypeId), d => dto.EventTypeId == d.EventTypeId)//事项分类
           .WhereIF(!string.IsNullOrEmpty(dto.Title), d => d.Order.Title.Contains(dto.Title!)) //标题
           .WhereIF(!string.IsNullOrEmpty(dto.ProvinceNo), d => d.Order.ProvinceNo.Contains(dto.ProvinceNo)) //省本地编号
           .WhereIF(!string.IsNullOrEmpty(dto.No), d => d.Order.No.Contains(dto.No)) //工单编码
           .WhereIF(dto.AcceptTypes.Any(), d => dto.AcceptTypes.Contains(d.Order.AcceptTypeCode)) //受理类型
           .WhereIF(dto.Channels.Any(), d => dto.Channels.Contains(d.Order.SourceChannelCode)) //来源渠道
           .WhereIF(dto.HotspotIds.Any(), d => dto.HotspotIds.Contains(d.Order.HotspotId)) //热点类型
           .WhereIF(!string.IsNullOrEmpty(dto.TransferPhone), d => d.Order.TransferPhone.Contains(dto.TransferPhone!)) //转接号码
           .WhereIF(dto.OrgCodes.Any(), d => dto.OrgCodes.Contains(d.Order.ActualHandleOrgCode)) //接办部门
           .WhereIF(!string.IsNullOrEmpty(dto.NameOrNo), d => d.Order.AcceptorName.Contains(dto.NameOrNo!) || d.Order.AcceptorStaffNo.Contains(dto.NameOrNo!)) //受理人/坐席
           .WhereIF(dto.CreationTimeStart.HasValue, d => d.Order.CreationTime >= dto.CreationTimeStart) //受理时间开始
           .WhereIF(dto.CreationTimeEnd.HasValue, d => d.Order.CreationTime <= dto.CreationTimeEnd) //受理时间结束
           .WhereIF(dto.EmergencyLevels.Any(), d => dto.EmergencyLevels.Contains(d.Order.EmergencyLevel))  //紧急程度
           .WhereIF(!string.IsNullOrEmpty(dto.FromPhone), d => d.Order.FromPhone.Contains(dto.FromPhone)) //来电号码
           .WhereIF(!string.IsNullOrEmpty(dto.PhoneNo), d => d.Order.Contact.Contains(dto.PhoneNo!)) //联系电话
           .WhereIF(!string.IsNullOrEmpty(dto.PushTypeCode), d => d.Order.PushTypeCode == dto.PushTypeCode) //推送分类
           .WhereIF(dto.ExpiredTimeStart.HasValue, d => d.Order.ExpiredTime >= dto.ExpiredTimeStart) //超期时间开始
           .WhereIF(dto.ExpiredTimeEnd.HasValue, d => d.Order.ExpiredTime <= dto.ExpiredTimeEnd) //超期时间结束
           .WhereIF(dto.Statuses.Any(), d => dto.Statuses.Contains(d.Order.Status))  //工单状态
           .WhereIF(dto.Statuses.Any(d => d == EOrderStatus.SpecialToUnAccept), d => d.Order.Status <= EOrderStatus.SpecialToUnAccept)
           .WhereIF(!string.IsNullOrEmpty(dto.ActualHandlerName), d => d.Order.ActualHandlerName.Contains(dto.ActualHandlerName)) //接办人
           .WhereIF(!string.IsNullOrEmpty(dto.CurrentStepCode), d => d.Order.ActualHandleStepCode == dto.CurrentStepCode) //当前办理节点
           .WhereIF(dto.ActualHandleTimeStart.HasValue, d => d.Order.ActualHandleTime >= dto.ActualHandleTimeStart) //办结时间开始
           .WhereIF(dto.ActualHandleTimeEnd.HasValue, d => d.Order.ActualHandleTime <= dto.ActualHandleTimeEnd) //办结时间结束
           .WhereIF(dto.IsOverTime == true, d => (d.Order.ExpiredTime < DateTime.Now && d.Order.Status < EOrderStatus.Filed) || (d.Order.ExpiredTime < d.Order.ActualHandleTime && d.Order.Status >= EOrderStatus.Filed)) //是 超期
           .WhereIF(dto.IsOverTime == false, d => (d.Order.ExpiredTime > DateTime.Now && d.Order.Status < EOrderStatus.Filed) || (d.Order.ExpiredTime > d.Order.ActualHandleTime && d.Order.Status >= EOrderStatus.Filed)) //否 超期
           .WhereIF(dto.IdentityType != null, d => d.Order.IdentityType == dto.IdentityType) //来电主体
           .WhereIF(!string.IsNullOrEmpty(dto.FromName), d => d.Order.FromName.Contains(dto.FromName)) //来电人姓名
           .WhereIF(dto.AreaCodes.Any(), d => dto.AreaCodes.Contains(d.Order.AreaCode)) //区域
           .WhereIF(dto.IsProvinceOrder.HasValue && dto.IsProvinceOrder == true, x => x.Order.IsProvince == true)
           .WhereIF(dto.IsProvinceOrder.HasValue && dto.IsProvinceOrder == false, x => x.Order.IsProvince == false)
           .OrderByDescending(d => d.CreationTime)
           .ToPagedListAsync(dto, HttpContext.RequestAborted);

            var data = _mapper.Map<List<EnforcementOrderListDto>>(items);

            if (_sessionContext.RequiredOrgId == "001188")
            {
                foreach (var item in data)
                {
                    if (item.IsTheClueTrue.HasValue)
                        item.IsShowUpdateButton = true;
                }
            }
            return new PagedDto<EnforcementOrderListDto>(total, data);
        }

        /// <summary>
        /// 列表页面基础数据
        /// </summary>
        /// <returns></returns>
        [HttpGet("base-data")]
        public async Task<object> BaseData()
        {
            var wfModule = await _workflowApplication.GetWorkflowModuleAsync(WorkflowModuleConsts.OrderHandle, HttpContext.RequestAborted);
            var definition = wfModule.Definition;
            var orgList = await _systemOrganizeRepository.Queryable().OrderBy(d => d.Id).ToTreeAsync(x => x.Children, it => it.ParentId, null);
            var judicialManagementOrg = _sysDicDataCacheManager.GetSysDicDataCache(SysDicTypeConsts.JudicialManagementOrg);

            List<SystemOrganize> OrgsOptions = new();
            foreach (var item in judicialManagementOrg)
            {
                var tempOrg = orgList.Find(p => p.Id == item.DicDataValue);
                if (tempOrg != null)
                    OrgsOptions.Add(tempOrg);
            }
            var rsp = new
            {
                AcceptTypeOptions = _sysDicDataCacheManager.GetSysDicDataCache(SysDicTypeConsts.AcceptType),
                ChannelOptions = _sysDicDataCacheManager.GetSysDicDataCache(TimeLimitBaseDataConsts.SourceChannel),
                OrgsOptions,
                EmergencyLevelOptions = EnumExts.GetDescriptions<EEmergencyLevel>(),
                PushTypeOptions = _sysDicDataCacheManager.GetSysDicDataCache(SysDicTypeConsts.PushType),
                OrderStatusOptions = EnumExts.GetDescriptions<EOrderStatus>(),
                CurrentStepOptions = definition?.Steps.Select(x => new Kv(x.Code, x.Name)),
                IdentityTypeOptions = EnumExts.GetDescriptions<EIdentityType>(),
                EnforcementHotspot = await _enforcementHotspotRepository.Queryable().ToTreeAsync(it => it.Children, it => it.ParentId, null, it => it.Id)
            };
            return rsp;
        }

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

        /// <summary>
        /// 线索核实  
        /// </summary>
        /// <param name="dto"></param>
        /// <returns></returns>
        [HttpPost("clue_verification")]
        public async Task ClueVerificationAsync([FromBody] ClueVerificationDto dto)
        {
            if (dto.OrderIds == null || dto.OrderIds.Count == 0)
                throw UserFriendlyException.SameMessage("工单不能为空");

            if (dto.IsTheClueTrue.HasValue && dto.IsTheClueTrue.Value == true)
            {
                if (string.IsNullOrEmpty(dto.EventTypeId) || string.IsNullOrEmpty(dto.EventTypeName))
                    throw UserFriendlyException.SameMessage("事项不能为空");
            }

            foreach (var item in dto.OrderIds)
            {
                var order = await _enforcementOrdersRepository.GetAsync(p => p.Id == item, HttpContext.RequestAborted);
                if (order == null)
                    throw UserFriendlyException.SameMessage("工单查询失败");

                order.IsTheClueTrue = dto.IsTheClueTrue;

                if (!string.IsNullOrEmpty(dto.EventTypeName))
                    order.EventTypeName = dto.EventTypeName;
                else
                    order.EventTypeName = null;

                if (!string.IsNullOrEmpty(dto.EventTypeSpliceName))
                    order.EventTypeSpliceName = dto.EventTypeSpliceName;
                else
                    order.EventTypeSpliceName = null;

                if (!string.IsNullOrEmpty(dto.EventTypeId))
                {
                    order.EventTypeOneLevelId = dto.EventTypeId.Substring(0, 2);
                    order.EventTypeId = dto.EventTypeId;
                }
                else
                {
                    order.EventTypeOneLevelId = null;
                    order.EventTypeId = null;
                }
                await _enforcementOrdersRepository.UpdateAsync(order, HttpContext.RequestAborted);
            }
        }

        /// <summary>
        /// 行政执法活动投诉事项类型- 获取层级分类
        /// </summary>
        /// <returns></returns>
        [HttpGet("treelist")]
        public async Task<List<JudicialComplaintsEventType>> GetTreeList()
        {
            return await _judicialComplaintsEventTypeRepository.Queryable().ToTreeAsync(it => it.Children, it => it.ParentId, null, it => it.Id);
        }

        /// <summary>
        /// 事项分类统计
        /// </summary>
        /// <param name="StartDate"></param>
        /// <param name="EndDate"></param>
        /// <param name="Id"></param>
        /// <param name="AreaCode"></param>
        /// <returns></returns>
        [HttpGet("event_classification_statistics")]
        public async Task<object> GetEventClassificationStatisticsAsync(DateTime StartDate, DateTime EndDate, string Id, string AreaCode)
        {

            EndDate = EndDate.AddDays(1).AddSeconds(-1);

            var items = await _judicialComplaintsEventTypeRepository.Queryable()
             .LeftJoin<EnforcementOrders>((x, o) => o.EventTypeSpliceName != null && (x.EventTypeName == o.EventTypeSpliceName || o.EventTypeSpliceName.Contains(x.EventTypeName)))
            .LeftJoin<Order>((x, o, p) => p.Id == o.Id)
            .Where((x, o, p) => p.CreationTime >= StartDate && p.CreationTime <= EndDate)
            .WhereIF(!string.IsNullOrEmpty(AreaCode), (x, o, p) => p.AreaCode == AreaCode)
             .Where((x, o, p) => x.ParentId == Id)
             .GroupBy((x, o, p) => new { x.Id, x.EventTypeName })
             .Select((x, o, p) => new
             {
                 Id = x.Id,
                 Name = x.EventTypeName,
                 Num = SqlFunc.AggregateSum(SqlFunc.IIF(p.Id != null, 1, 0)),
                 Sublevel = SqlFunc.AggregateSum(SqlFunc.IIF(x.EventTypeName != o.EventTypeName, 1, 0)) > 0,
             })
             .MergeTable()
             .ToListAsync();

            var total = new
            {
                Id = "0",
                Name = "合计",
                Num = items.Sum(x => x.Num),
                Sublevel = false
            };

            var data = await _enforcementOrdersRepository.Queryable()
                .Includes(x => x.Order)
                .Where(d => d.Order.Id != null)
                .Where(d => d.Order.CreationTime >= StartDate && d.Order.CreationTime <= EndDate)
                .GroupBy(d => d.Id)
                 .Select(d => new EventClassificationOrderCountDto
                 {
                     TheClueIsTrue = SqlFunc.AggregateSum(SqlFunc.IIF(d.IsTheClueTrue.HasValue && d.IsTheClueTrue.Value == true, 1, 0)),
                     TheClueIsNotTrue = SqlFunc.AggregateSum(SqlFunc.IIF(d.IsTheClueTrue.HasValue && d.IsTheClueTrue.Value == false, 1, 0)),
                     EnforcementOrder = SqlFunc.AggregateSum(SqlFunc.IIF(d.IsEnforcementOrder, 1, 0)),
                     PassTheBuckOrder = SqlFunc.AggregateSum(SqlFunc.IIF(d.IsPassTheBuckOrder, 1, 0))
                 })
                   .ToListAsync();

            var orderCount = new
            {
                TheClueIsTrue = data.Sum(x => x.TheClueIsTrue),
                TheClueIsNotTrue = data.Sum(x => x.TheClueIsNotTrue),
                EnforcementOrder = data.Sum(x => x.EnforcementOrder),
                PassTheBuckOrder = data.Sum(x => x.PassTheBuckOrder),
            };

            return new { List = items, Total = total, OrderCount = orderCount };
        }

        /// <summary>
        /// 事项分类统计--明细
        /// </summary>
        /// <param name="dto"></param>
        /// <returns></returns>
        [HttpGet("event_classification_statistics_order_list")]
        public async Task<PagedDto<EnforcementOrderListDto>> GetEventClassificationStatisticsOrderListAsync([FromQuery] QueryEventClassificationStatisticsDto dto)
        {
            dto.EndDate = dto.EndDate.AddDays(1).AddSeconds(-1);

            var (total, items) = await _enforcementOrdersRepository.Queryable()
            .Includes(x => x.Order)
            .Where(d => d.Order.Id != null)
            .Where(d => d.Order.CreationTime >= dto.StartDate && d.Order.CreationTime <= dto.EndDate)
            .WhereIF(!string.IsNullOrEmpty(dto.EventTypeId), d => d.EventTypeId == dto.EventTypeId)
            .WhereIF(!string.IsNullOrEmpty(dto.AreaCode), d => d.Order.AreaCode == dto.AreaCode)
              .OrderByDescending(d => d.CreationTime)
           .ToPagedListAsync(dto, HttpContext.RequestAborted);

            return new PagedDto<EnforcementOrderListDto>(total, _mapper.Map<List<EnforcementOrderListDto>>(items));
        }

        /// <summary>
        /// 执法部门办件统计
        /// </summary>
        /// <param name="StartDate"></param>
        /// <param name="EndDate"></param>
        /// <returns></returns>
        [HttpGet("enforcement_departmental_processing_statistics")]
        public async Task<object> GetDepartmentalProcessingStatisticsAsync(DateTime StartDate, DateTime EndDate)
        {

            EndDate = EndDate.AddDays(1).AddSeconds(-1);
            var data = await _enforcementOrdersRepository.Queryable()
                   .Includes(x => x.Order)
                   .Where(d => d.Order.Id != null)
                   .Where(d => d.Order.CreationTime >= StartDate && d.Order.CreationTime <= EndDate)
                    .GroupBy(d => new
                    {
                        OrgCode = d.Order.OrgLevelOneCode,
                        OrgName = d.Order.OrgLevelOneName
                    })
                    .Select(d => new
                    {
                        OrgCode = d.Order.OrgLevelOneCode,
                        OrgName = d.Order.OrgLevelOneName,
                        CountNum = SqlFunc.AggregateCount(d.Order.OrgLevelOneCode),
                        TheClueIsTrue = SqlFunc.AggregateSum(SqlFunc.IIF(d.IsTheClueTrue.HasValue && d.IsTheClueTrue.Value == true, 1, 0)),
                        TheClueIsNotTrue = SqlFunc.AggregateSum(SqlFunc.IIF(d.IsTheClueTrue.HasValue && d.IsTheClueTrue.Value == false, 1, 0)),
                        EnforcementOrder = SqlFunc.AggregateSum(SqlFunc.IIF(d.IsEnforcementOrder, 1, 0))
                    })
                   .ToListAsync();

            return data;
        }

        /// <summary>
        /// 执法部门办件统计---子级
        /// </summary>
        /// <param name="StartDate"></param>
        /// <param name="EndDate"></param>
        /// <param name="OrgCode"></param>
        /// <returns></returns>
        [HttpGet("enforcement_departmental_processing_statistics_child")]
        public async Task<object> GetDepartmentalProcessingStatisticsAsync(DateTime StartDate, DateTime EndDate, string OrgCode)
        {

            EndDate = EndDate.AddDays(1).AddSeconds(-1);
            if (string.IsNullOrEmpty(OrgCode))
                throw UserFriendlyException.SameMessage("部门code不能为空!");

            var data = await _enforcementOrdersRepository.Queryable()
                   .Includes(x => x.Order)
                   .Where(d => d.Order.Id != null)
                   .Where(d => d.Order.CreationTime >= StartDate && d.Order.CreationTime <= EndDate)
                   .Where(d => d.Order.ActualHandleOrgCode.StartsWith(OrgCode))
                    .GroupBy(d => new
                    {
                        OrgCode = d.Order.ActualHandleOrgCode,
                        OrgName = d.Order.ActualHandleOrgName
                    })
                    .Select(d => new
                    {
                        OrgCode = d.Order.ActualHandleOrgCode,
                        OrgName = d.Order.ActualHandleOrgName,
                        CountNum = SqlFunc.AggregateCount(d.Order.ActualHandleOrgCode),
                        TheClueIsTrue = SqlFunc.AggregateSum(SqlFunc.IIF(d.IsTheClueTrue.HasValue && d.IsTheClueTrue.Value == true, 1, 0)),
                        TheClueIsNotTrue = SqlFunc.AggregateSum(SqlFunc.IIF(d.IsTheClueTrue.HasValue && d.IsTheClueTrue.Value == false, 1, 0)),
                        EnforcementOrder = SqlFunc.AggregateSum(SqlFunc.IIF(d.IsEnforcementOrder, 1, 0)),

                    })
                   .ToListAsync();

            return data;
        }

        /// <summary>
        /// 执法部门办件统计---明细
        /// </summary>
        /// <param name="dto"></param>
        /// <returns></returns>
        [HttpGet("enforcement_departmental_processing_statistics_order_list")]
        public async Task<PagedDto<EnforcementOrderListDto>> GetDepartmentalProcessingStatisticsOrderListAsync([FromQuery] QueryDepartmentalProcessingStatisticsDto dto)
        {
            dto.EndDate = dto.EndDate.AddDays(1).AddSeconds(-1);

            var (total, items) = await _enforcementOrdersRepository.Queryable()
            .Includes(x => x.Order)
            .Where(d => d.Order.Id != null)
            .Where(d => d.Order.CreationTime >= dto.StartDate && d.Order.CreationTime <= dto.EndDate)
            .WhereIF(!string.IsNullOrEmpty(dto.OrgCode) && dto.OrgCode == "001", d => d.Order.OrgLevelOneCode == dto.OrgCode)
            .WhereIF(!string.IsNullOrEmpty(dto.OrgCode) && dto.OrgCode != "001", d => d.Order.ActualHandleOrgCode.StartsWith(dto.OrgCode))
            .WhereIF(dto.TheClueIsTrue == true, d => d.IsTheClueTrue == true)
            .WhereIF(dto.TheClueIsNotTrue == true, d => d.IsTheClueTrue == false)
            .WhereIF(dto.EnforcementOrder == true, d => d.IsEnforcementOrder == true)
              .OrderByDescending(d => d.CreationTime)
           .ToPagedListAsync(dto, HttpContext.RequestAborted);

            return new PagedDto<EnforcementOrderListDto>(total, _mapper.Map<List<EnforcementOrderListDto>>(items));
        }

        /// <summary>
        /// 区域分类统计
        /// </summary>
        /// <returns></returns>
        [HttpGet("regional_classification_statistics")]
        public async Task<object> GetRegionalClassificationStatisticsAsync(DateTime StartDate, DateTime EndDate)
        {
            EndDate = EndDate.AddDays(1).AddSeconds(-1);

            var list = await _enforcementOrdersRepository.Queryable()
               .Includes(x => x.Order)
               .Where(x => x.Order.Id != null)
               .Where(x => x.Order.CreationTime >= StartDate && x.Order.CreationTime <= EndDate)
               .LeftJoin<SystemArea>((x, o) => x.Order.AreaCode.Substring(SqlFunc.MappingColumn<int>("0"), SqlFunc.MappingColumn<int>("6")) == o.Id)
               .Where((x, o) => x.Order.Status >= EOrderStatus.Filed)
               .GroupBy((x, o) => new
               {
                   AreaCode = x.Order.AreaCode.Substring(SqlFunc.MappingColumn<int>("0"), SqlFunc.MappingColumn<int>("6")),
                   o.AreaName,
               })
               .Select((x, o) => new
               {
                   AreaCode = x.Order.AreaCode.Substring(SqlFunc.MappingColumn<int>("0"), SqlFunc.MappingColumn<int>("6")),
                   AreaName = o.AreaName,
                   OrderCountNum = SqlFunc.AggregateCount(x.Order.AreaCode.Substring(SqlFunc.MappingColumn<int>("0"), SqlFunc.MappingColumn<int>("6"))),
                   TheClueIsTrue = SqlFunc.AggregateSum(SqlFunc.IIF(x.IsTheClueTrue.HasValue && x.IsTheClueTrue.Value == true, 1, 0)),
                   TheClueIsNotTrue = SqlFunc.AggregateSum(SqlFunc.IIF(x.IsTheClueTrue.HasValue && x.IsTheClueTrue.Value == false, 1, 0)),
                   EnforcementOrder = SqlFunc.AggregateSum(SqlFunc.IIF(x.IsEnforcementOrder, 1, 0))
               }).MergeTable()
                .Where(x => x.AreaCode != "519800" && x.AreaCode != "519900")
                .OrderByDescending(it => it.OrderCountNum)
                .ToListAsync();
            return list;
        }

        /// <summary>
        /// 区域分类统计---明细
        /// </summary>
        /// <param name="dto"></param>
        /// <returns></returns>
        [HttpGet("regional_classification_statistics_order_list")]
        public async Task<PagedDto<EnforcementOrderListDto>> GetRegionalClassificationStatisticsOrderListAsync([FromQuery] QueryRegionalClassificationStatisticsDto dto)
        {
            dto.EndDate = dto.EndDate.AddDays(1).AddSeconds(-1);

            var (total, items) = await _enforcementOrdersRepository.Queryable()
            .Includes(x => x.Order)
            .Where(d => d.Order.Id != null)
            .Where(d => d.Order.CreationTime >= dto.StartDate && d.Order.CreationTime <= dto.EndDate)
            .WhereIF(!string.IsNullOrEmpty(dto.AreaCode), d => d.Order.AreaCode == dto.AreaCode)
            .WhereIF(dto.TheClueIsTrue == true, d => d.IsTheClueTrue == true)
            .WhereIF(dto.TheClueIsNotTrue == true, d => d.IsTheClueTrue == false)
            .WhereIF(dto.EnforcementOrder == true, d => d.IsEnforcementOrder == true)
              .OrderByDescending(d => d.CreationTime)
           .ToPagedListAsync(dto, HttpContext.RequestAborted);

            return new PagedDto<EnforcementOrderListDto>(total, _mapper.Map<List<EnforcementOrderListDto>>(items));
        }

        /// <summary>
        /// 部门满意度统计
        /// </summary>
        /// <param name="StartDate"></param>
        /// <param name="EndDate"></param>
        /// <param name="TypeId">1:办件结果 2:办件态度</param>
        /// <returns></returns>
        [HttpGet("enforcement_visit_org_satisfaction_statistics")]
        public async Task<object> GetVisitAndOrgSatisfactionStatisticsAsync(DateTime StartDate, DateTime EndDate, int TypeId)
        {
            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))
                .GroupBy((x, o, p) => new
                {
                    VisitOrgCode = p.VisitOrgCode.Substring(SqlFunc.MappingColumn<int>("0"), SqlFunc.MappingColumn<int>("6"))
                })
                .Select((x, o, p) => new VisitAndOrgSatisfactionStatisticsDto()
                {
                    OrgCode = p.VisitOrgCode.Substring(SqlFunc.MappingColumn<int>("0"), SqlFunc.MappingColumn<int>("6")),
                    TotalSumCount = SqlFunc.AggregateCount(p.VisitOrgCode.Substring(SqlFunc.MappingColumn<int>("0"), SqlFunc.MappingColumn<int>("6"))),
                    VerySatisfiedCount = SqlFunc.IIF(TypeId == 1, SqlFunc.AggregateSum(SqlFunc.IIF(SqlFunc.JsonField(p.OrgProcessingResults, "Key") == "5", 1, 0)), SqlFunc.AggregateSum(SqlFunc.IIF(SqlFunc.JsonField(p.OrgHandledAttitude, "Key") == "5", 1, 0))),//非常满意数
                    SatisfiedCount = SqlFunc.IIF(TypeId == 1, SqlFunc.AggregateSum(SqlFunc.IIF(SqlFunc.JsonField(p.OrgProcessingResults, "Key") == "4", 1, 0)), SqlFunc.AggregateSum(SqlFunc.IIF(SqlFunc.JsonField(p.OrgHandledAttitude, "Key") == "4", 1, 0))), //满意数
                    RegardedAsSatisfiedCount = SqlFunc.IIF(TypeId == 1, SqlFunc.AggregateSum(SqlFunc.IIF(SqlFunc.JsonField(p.OrgProcessingResults, "Key") == "-1", 1, 0)), SqlFunc.AggregateSum(SqlFunc.IIF(SqlFunc.JsonField(p.OrgHandledAttitude, "Key") == "-1", 1, 0))),//视为满意
                    DefaultSatisfiedCount = SqlFunc.IIF(TypeId == 1, SqlFunc.AggregateSum(SqlFunc.IIF(SqlFunc.JsonField(p.OrgProcessingResults, "Key") == "0", 1, 0)), SqlFunc.AggregateSum(SqlFunc.IIF(SqlFunc.JsonField(p.OrgHandledAttitude, "Key") == "0", 1, 0))),//默认满意
                    NoSatisfiedCount = SqlFunc.IIF(TypeId == 1, SqlFunc.AggregateSum(SqlFunc.IIF(SqlFunc.JsonField(p.OrgProcessingResults, "Key") == "2", 1, 0)), SqlFunc.AggregateSum(SqlFunc.IIF(SqlFunc.JsonField(p.OrgHandledAttitude, "Key") == "2", 1, 0))),//不满意
                    NoEvaluateCount = SqlFunc.IIF(TypeId == 1, SqlFunc.AggregateSum(SqlFunc.IIF(SqlFunc.JsonField(p.OrgProcessingResults, "Key") == "7", 1, 0)), SqlFunc.AggregateSum(SqlFunc.IIF(SqlFunc.JsonField(p.OrgHandledAttitude, "Key") == "7", 1, 0))),//未做评价
                    NoPutThroughCount = SqlFunc.IIF(TypeId == 1, SqlFunc.AggregateSum(SqlFunc.IIF(SqlFunc.JsonField(p.OrgProcessingResults, "Key") == "6", 1, 0)), SqlFunc.AggregateSum(SqlFunc.IIF(SqlFunc.JsonField(p.OrgHandledAttitude, "Key") == "6", 1, 0))),//未接通
                })
                .MergeTable()
                .LeftJoin<SystemOrganize>((it, o) => it.OrgCode == o.Id)
                .Select((it, o) => new VisitAndOrgSatisfactionStatisticsDto()
                {
                    OrgName = o.Name,
                    OrgCode = it.OrgCode,
                    OrgType = o.OrgType,
                    TotalSumCount = it.TotalSumCount,
                    VerySatisfiedCount = it.VerySatisfiedCount,//非常满意数
                    SatisfiedCount = it.SatisfiedCount, //满意数
                    RegardedAsSatisfiedCount = it.RegardedAsSatisfiedCount,//视为满意
                    DefaultSatisfiedCount = it.DefaultSatisfiedCount,//默认满意
                    NoSatisfiedCount = it.NoSatisfiedCount,//不满意
                    NoEvaluateCount = it.NoEvaluateCount,//未做评价
                    NoPutThroughCount = it.NoPutThroughCount,//未接通
                })
                .ToListAsync();


            var sumModel = new VisitAndOrgSatisfactionStatisticsDto()
            {
                OrgName = "总计",
                TotalSumCount = list.Sum(x => x.TotalSumCount),
                VerySatisfiedCount = list.Sum(x => x.VerySatisfiedCount),
                SatisfiedCount = list.Sum(x => x.SatisfiedCount),
                RegardedAsSatisfiedCount = list.Sum(x => x.RegardedAsSatisfiedCount),
                DefaultSatisfiedCount = list.Sum(x => x.DefaultSatisfiedCount),
                NoSatisfiedCount = list.Sum(x => x.NoSatisfiedCount),
                NoEvaluateCount = list.Sum(x => x.NoEvaluateCount),
                NoPutThroughCount = list.Sum(x => x.NoPutThroughCount),
            };


            return new { DataList = list, SumModel = sumModel };
        }

    }
}