hotline/src/Hotline.Application/JudicialManagement/EnforcementApplication.cs

using Hotline.JudicialManagement;
using Hotline.Orders;
using Hotline.Settings;
using Hotline.Share.Dtos;
using Hotline.Share.Dtos.JudicialManagement;
using Hotline.Share.Dtos.Order;
using Hotline.Share.Enums.JudicialManagement;
using Hotline.Share.Enums.Order;
using Hotline.Share.Tools;
using Hotline.Snapshot;
using MapsterMapper;
using SqlSugar;
using XF.Domain.Authentications;
using XF.Domain.Dependency;
using XF.Domain.Repository;

namespace Hotline.Application.JudicialManagement
{
    public class EnforcementApplication : IEnforcementApplication, IScopeDependency
    {
        private readonly IRepository<JudicialManagementOrders> _judicialManagementOrdersRepository;
        private readonly IMapper _mapper;
        private readonly ISessionContext _sessionContext;
        private readonly IRepository<EnforcementOrdersHandler> _enforcementOrdersHandlerRepository;

        /// <summary>
        /// 
        /// </summary>
        /// <param name="judicialManagementOrdersRepository"></param>
        /// <param name="enforcementOrdersHandlerRepository"></param>
        /// <param name="mapper"></param>
        /// <param name="sessionContext"></param>
        /// <param name="lawEnforcementAgenciesRepository"></param>
        public EnforcementApplication(
           IRepository<JudicialManagementOrders> judicialManagementOrdersRepository,
           IMapper mapper,
           ISessionContext sessionContext,
           IRepository<EnforcementOrdersHandler> enforcementOrdersHandlerRepository)
        {
            _judicialManagementOrdersRepository = judicialManagementOrdersRepository;
            _mapper = mapper;
            _sessionContext = sessionContext;
            _enforcementOrdersHandlerRepository = enforcementOrdersHandlerRepository;
        }

        /// <summary>
        /// 受理工单
        /// </summary>
        /// <param name="dto"></param>
        /// <returns></returns>
        public ISugarQueryable<JudicialManagementOrders> GetOwnOrderList(QueryEnforcementOrderNewDto dto)
        {
            return _judicialManagementOrdersRepository.Queryable()
                .Where(d => d.CreatorId == _sessionContext.UserId)
                  .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.Title.Contains(dto.Title!)) //标题
                   .WhereIF(!string.IsNullOrEmpty(dto.No), d => d.No.Contains(dto.No)) //工单编码
                   .WhereIF(!string.IsNullOrEmpty(dto.AcceptTypeCode), d => d.AcceptTypeCode == dto.AcceptTypeCode) //受理类型
                   .WhereIF(!string.IsNullOrEmpty(dto.ChannelCode), d => d.SourceChannelCode == dto.ChannelCode) //来源渠道
                    .WhereIF(!string.IsNullOrEmpty(dto.HotspotId), d => d.TransferPhone == dto.HotspotId) //热点类型
                    .WhereIF(!string.IsNullOrEmpty(dto.NameOrNo), d => d.AcceptorName.Contains(dto.NameOrNo!) || d.AcceptorStaffNo.Contains(dto.NameOrNo!)) //受理人/坐席
                    .WhereIF(dto.CreationTimeStart.HasValue, d => d.CreationTime >= dto.CreationTimeStart) //受理时间开始
                   .WhereIF(dto.CreationTimeEnd.HasValue, d => d.CreationTime <= dto.CreationTimeEnd) //受理时间结束
                     .WhereIF(!string.IsNullOrEmpty(dto.FromName), d => d.FromName == dto.FromName) //来电人姓名
                     .WhereIF(!string.IsNullOrEmpty(dto.AreaCode), d => d.AreaCode == dto.AreaCode) //区域
                      .WhereIF(!string.IsNullOrEmpty(dto.OrgCode), d => SqlFunc.JsonListObjectAny(d.EnforcementOrdersHandler, "Value", dto.OrgCode))//接办部门
                       .WhereIF(!string.IsNullOrEmpty(dto.OrgCode), d => SqlFunc.JsonListObjectAny(d.LawEnforcementAgencies, "Value", dto.LawEnforcementOrgCode))//执法部门
                     .OrderByDescending(d => d.CreationTime)
                    .MergeTable();
        }

        /// <summary>
        /// 司法行政监督管理工单查询
        /// </summary>
        /// <param name="dto"></param>
        /// <returns></returns>
        public ISugarQueryable<JudicialManagementOrders> GetOrderList(QueryEnforcementOrderNewDto dto)
        {
            var areaCode = _sessionContext.OrgAreaCode ?? "511500";

            return _judicialManagementOrdersRepository.Queryable()
                .Where(d => d.IsItCounted == true)
                    .WhereIF(areaCode != "511500", d => d.AreaCode.StartsWith(areaCode))
                     // .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(dto.IsTheClueTrueText == "1", d => d.IsTheClueTrue == null)//待核实
                     .WhereIF(dto.IsTheClueTrueText == "2", d => d.IsTheClueTrue == true)//线索属实
                     .WhereIF(dto.IsTheClueTrueText == "3", d => d.IsEnforcementOrder == false)//线索不属实 调整为非行政执法类工单
                     .WhereIF(dto.IsTheClueTrueText == "4", d => d.IsPassTheBuckOrder == true)//推诿工单
                     .WhereIF(dto.IsTheClueTrueText == "5", d => d.IsEnforcementOrder == true)//行政执法类工单

                     .WhereIF(!string.IsNullOrEmpty(dto.EventTypeId), d => dto.EventTypeId == d.EventTypeId)//事项分类
                     .WhereIF(!string.IsNullOrEmpty(dto.Title), d => d.Title.Contains(dto.Title!)) //标题
                     .WhereIF(!string.IsNullOrEmpty(dto.No), d => d.No.Contains(dto.No)) //工单编码
                     .WhereIF(!string.IsNullOrEmpty(dto.AcceptTypeCode), d => d.AcceptTypeCode == dto.AcceptTypeCode) //受理类型
                     .WhereIF(!string.IsNullOrEmpty(dto.ChannelCode), d => d.SourceChannelCode == dto.ChannelCode) //来源渠道
                      .WhereIF(!string.IsNullOrEmpty(dto.HotspotId), d => d.TransferPhone == dto.HotspotId) //热点类型
                      .WhereIF(!string.IsNullOrEmpty(dto.NameOrNo), d => d.AcceptorName.Contains(dto.NameOrNo!) || d.AcceptorStaffNo.Contains(dto.NameOrNo!)) //受理人/坐席
                      .WhereIF(dto.CreationTimeStart.HasValue, d => d.CreationTime >= dto.CreationTimeStart) //受理时间开始
                     .WhereIF(dto.CreationTimeEnd.HasValue, d => d.CreationTime <= dto.CreationTimeEnd) //受理时间结束
                     .WhereIF(!string.IsNullOrEmpty(dto.FromName), d => d.FromName == dto.FromName) //来电人姓名
                     .WhereIF(!string.IsNullOrEmpty(dto.AreaCode), d => d.AreaCode == dto.AreaCode) //区域
                     .WhereIF(!string.IsNullOrEmpty(dto.OrgCode), d => SqlFunc.JsonListObjectAny(d.EnforcementOrdersHandler, "Value", dto.OrgCode))//接办部门
                       .WhereIF(!string.IsNullOrEmpty(dto.OrgCode), d => SqlFunc.JsonListObjectAny(d.LawEnforcementAgencies, "Value", dto.LawEnforcementOrgCode))//执法部门
                       .OrderByDescending(d => d.CreationTime)
                      .MergeTable();

        }

        /// <summary>
        /// 事项分类统计--明细
        /// </summary>
        /// <param name="dto"></param>
        /// <returns></returns>
        public ISugarQueryable<JudicialManagementOrders> GetEventClassificationStatisticsOrderListAsync(QueryEventClassificationStatisticsDto dto)
        {
            string areCodeText = "";
            if (string.IsNullOrEmpty(dto.AreaCode))
            {
                if (_sessionContext.OrgAreaCode != "511500")
                    areCodeText = _sessionContext.OrgAreaCode;
            }
            else
                areCodeText = dto.AreaCode;

            return _judicialManagementOrdersRepository.Queryable()
            .Where(d => d.IsItCounted == true && d.CreationTime >= dto.StartTime && d.CreationTime <= dto.EndTime && d.EventTypeId != null)
            .WhereIF(!string.IsNullOrEmpty(dto.EventTypeId), d => d.EventTypeId.StartsWith(dto.EventTypeId))
            .WhereIF(!string.IsNullOrEmpty(areCodeText), d => d.AreaCode.StartsWith(areCodeText))
            .OrderByDescending(d => d.CreationTime)
            .MergeTable();
        }

        /// <summary>
        /// 执法部门办件统计
        /// </summary>
        /// <param name="StartTime"></param>
        /// <param name="EndTime"></param>
        /// <returns></returns>
        public ISugarQueryable<EmDepartmentalProcessingStatisticsDto> GetDepartmentalProcessingStatisticsAsync(DateTime StartTime, DateTime EndTime)
        {
            return _enforcementOrdersHandlerRepository.Queryable()
                   .LeftJoin<JudicialManagementOrders>((h, o) => h.OrderId == o.Id)
                   .Where((h, o) => o.CreationTime >= StartTime && o.CreationTime <= EndTime && o.IsItCounted == true)
                    .GroupBy((h, o) => new
                    {
                        OrgCode = h.OrgCode,
                        OrgName = h.OrgName
                    })
                    .Select((h, o) => new EmDepartmentalProcessingStatisticsDto
                    {
                        OrgCode = h.OrgCode,
                        OrgName = h.OrgName,
                        CountNum = SqlFunc.AggregateSum(SqlFunc.IIF(h.OrgCode != null, 1, 0)),
                        TheClueIsTrue = SqlFunc.AggregateSum(SqlFunc.IIF(o.IsTheClueTrue.HasValue && o.IsTheClueTrue.Value == true, 1, 0)),
                        TheClueIsNotTrue = SqlFunc.AggregateSum(SqlFunc.IIF(o.IsTheClueTrue.HasValue && o.IsTheClueTrue.Value == false, 1, 0)),
                        EnforcementOrder = SqlFunc.AggregateSum(SqlFunc.IIF(o.IsEnforcementOrder.HasValue && o.IsEnforcementOrder.Value == true, 1, 0))
                    })
                      .MergeTable();
        }

        /// <summary>
        /// 执法部门办件统计---明细
        /// </summary>
        /// <param name="dto"></param>
        /// <returns></returns>
        public ISugarQueryable<EnforcementOrderListDto> GetDepartmentalProcessingStatisticsOrderListAsync(QueryDepartmentalProcessingStatisticsDto dto)
        {
            return _enforcementOrdersHandlerRepository.Queryable()
                 .LeftJoin<JudicialManagementOrders>((x, o) => x.OrderId == o.Id)
                 .Where((x, o) => o.CreationTime >= dto.StartTime && o.CreationTime <= dto.EndTime && o.Id != null && o.IsItCounted == true)
                 .WhereIF(!string.IsNullOrEmpty(dto.OrgCode) && dto.OrgCode == "001", (x, o) => x.OrgCode == dto.OrgCode)
                 .WhereIF(!string.IsNullOrEmpty(dto.OrgCode) && dto.OrgCode != "001", (x, o) => x.OrgCode.StartsWith(dto.OrgCode))
                 .WhereIF(dto.TheClueIsTrue == true, (x, o) => o.IsTheClueTrue == true)
                 .WhereIF(dto.TheClueIsNotTrue == true, (x, o) => o.IsTheClueTrue == false)
                 .WhereIF(dto.EnforcementOrder == true, (x, o) => o.IsEnforcementOrder == true)
                 .OrderByDescending((x, o) => o.CreationTime)
                   .Select((x, o) => new EnforcementOrderListDto
                   {
                       Id = o.Id,
                       No = o.No,
                       SourceChannel = o.SourceChannel,
                       SourceChannelCode = o.SourceChannelCode,
                       FromPhone = o.FromPhone,
                       TransferPhone = o.TransferPhone,
                       FromName = o.FromName,
                       Contact = o.Contact,
                       AcceptType = o.AcceptType,
                       AcceptTypeCode = o.AcceptTypeCode,
                       Title = o.Title,
                       HotspotId = o.HotspotId,
                       HotspotName = o.HotspotName,
                       HotspotSpliceName = o.HotspotSpliceName,
                       CreationTime = o.CreationTime,
                       Province = o.Province,
                       City = o.City,
                       County = o.County,
                       Town = o.Town,
                       IsEnforcementOrder = o.IsEnforcementOrder,
                       IsPassTheBuckOrder = o.IsPassTheBuckOrder,
                       IsTheClueTrue = o.IsTheClueTrue,
                       EventTypeId = o.EventTypeId,
                       EventTypeName = o.EventTypeName,
                       EventTypeSpliceName = o.EventTypeSpliceName,
                       AcceptorId = o.AcceptorId,
                       AcceptorName = o.AcceptorName,
                       AcceptorStaffNo = o.AcceptorStaffNo,
                       AcceptorOrgCode = o.AcceptorOrgCode,
                       AcceptorOrgName = o.AcceptorOrgName,
                   })
                .MergeTable();
        }

        /// <summary>
        /// 区域分类统计
        /// </summary>
        /// <returns></returns>
        public ISugarQueryable<RegionalClassificationStatisticsDto> GetRegionalClassificationStatisticsAsync(DateTime StartTime, DateTime EndTime)
        {
            var areaCode = _sessionContext.OrgAreaCode ?? "511500";
            return _judicialManagementOrdersRepository.Queryable()
                .Where(x => x.CreationTime >= StartTime && x.CreationTime <= EndTime && x.IsItCounted == true)
                .LeftJoin<SystemArea>((x, o) => x.AreaCode.Substring(SqlFunc.MappingColumn<int>("0"), SqlFunc.MappingColumn<int>("6")) == o.Id)
                .WhereIF(areaCode != "511500", (x, o) => x.AreaCode.StartsWith(areaCode))
                .GroupBy((x, o) => new
                {
                    AreaCode = x.AreaCode.Substring(SqlFunc.MappingColumn<int>("0"), SqlFunc.MappingColumn<int>("6")),
                    o.AreaName,
                })
                .Select((x, o) => new RegionalClassificationStatisticsDto
                {
                    AreaCode = x.AreaCode.Substring(SqlFunc.MappingColumn<int>("0"), SqlFunc.MappingColumn<int>("6")),
                    AreaName = o.AreaName,
                    OrderCountNum = SqlFunc.AggregateCount(x.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.HasValue && x.IsEnforcementOrder.Value == true, 1, 0))
                }).MergeTable()
                 .Where(x => x.AreaCode != "519800" && x.AreaCode != "519900")
                 .OrderByDescending(it => it.OrderCountNum)
               .MergeTable();
        }

        /// <summary>
        /// 区域分类统计---明细
        /// </summary>
        /// <param name="dto"></param>
        /// <returns></returns>
        public ISugarQueryable<JudicialManagementOrders> GetRegionalClassificationStatisticsOrderListAsync(QueryRegionalClassificationStatisticsDto dto)
        {
            return _judicialManagementOrdersRepository.Queryable()
            .Where(d => d.CreationTime >= dto.StartTime && d.CreationTime <= dto.EndTime && d.IsItCounted == true)
            .WhereIF(!string.IsNullOrEmpty(dto.AreaCode), d => d.AreaCode.StartsWith(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)
            .MergeTable();

        }

        /// <summary>
        /// 部门满意度统计
        /// </summary>
        /// <param name="StartTime"></param>
        /// <param name="EndTime"></param>
        /// <param name="TypeId">1:办件结果 2:办件态度</param>
        /// <returns></returns>
        public ISugarQueryable<VisitAndOrgSatisfactionStatisticsDto> GetVisitAndOrgSatisfactionStatisticsAsync(DateTime StartTime, DateTime EndTime, int TypeId)
        {
            return _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 && x.IsItCounted == true && p.VisitTarget == EVisitTarget.Org
                && o.VisitState == EVisitState.Visited && !string.IsNullOrEmpty(p.VisitOrgCode) && x.OrderSoure == EOrderSoure.Hotline)
                .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,//未接通
                })
                .MergeTable();

        }

        /// <summary>
        /// 部门满意度统计---明细
        /// </summary>
        /// <param name="dto"></param>
        /// <returns></returns>
        public ISugarQueryable<EnforcementOrgSatisfactionOrderListDto> GetVisitAndOrgSatisfactionStatisticsOrderListAsync(QueryOrgSatisfactionStatisticsDto dto)
        {
            return _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 >= dto.StartTime && o.VisitTime <= dto.EndTime && x.IsItCounted == true && p.VisitTarget == EVisitTarget.Org && x.OrderSoure == EOrderSoure.Hotline)
                .Where((x, o, p) => o.VisitState == EVisitState.Visited && !string.IsNullOrEmpty(p.VisitOrgCode))
                .WhereIF(!string.IsNullOrEmpty(dto.OrgCode) && dto.OrgCode == "001", (x, o, p) => p.VisitOrgCode == dto.OrgCode)
                .WhereIF(!string.IsNullOrEmpty(dto.OrgCode) && dto.OrgCode != "001", (x, o, p) => p.VisitOrgCode.StartsWith(dto.OrgCode))
                 .WhereIF(dto.TypeId == 1, (x, o, p) => SqlFunc.JsonField(p.OrgProcessingResults, "Key") == dto.DateValue)
                .WhereIF(dto.TypeId == 2, (x, o, p) => SqlFunc.JsonField(p.OrgHandledAttitude, "Key") == dto.DateValue)
                .Select((x, o, p) => new EnforcementOrgSatisfactionOrderListDto
                {
                    Id = x.Id,
                    No = x.No,
                    SourceChannel = x.SourceChannel,
                    SourceChannelCode = x.SourceChannelCode,
                    FromPhone = x.FromPhone,
                    TransferPhone = x.TransferPhone,
                    FromName = x.FromName,
                    Contact = x.Contact,
                    AcceptType = x.AcceptType,
                    AcceptTypeCode = x.AcceptTypeCode,
                    Title = x.Title,
                    HotspotId = x.HotspotId,
                    HotspotName = x.HotspotName,
                    HotspotSpliceName = x.HotspotSpliceName,
                    CreationTime = x.CreationTime,
                    Province = x.Province,
                    City = x.City,
                    County = x.County,
                    Town = x.Town,
                    IsEnforcementOrder = x.IsEnforcementOrder,
                    IsPassTheBuckOrder = x.IsPassTheBuckOrder,
                    IsTheClueTrue = x.IsTheClueTrue,
                    EventTypeId = x.EventTypeId,
                    EventTypeName = x.EventTypeName,
                    EventTypeSpliceName = x.EventTypeSpliceName,
                    AcceptorId = x.AcceptorId,
                    AcceptorName = x.AcceptorName,
                    AcceptorStaffNo = x.AcceptorStaffNo,
                    AcceptorOrgCode = x.AcceptorOrgCode,
                    AcceptorOrgName = x.AcceptorOrgName,
                    VisitId = p.VisitId
                })
               .MergeTable();


        }

        public async Task<List<IndustryOrderStaticsDto>> QueryIndustryOrderStatics(QueryIndustryOrderStatisticsRequest queryIndustryOrderStatisticsRequest)
        {
            var judicialManagementOrderTable = _judicialManagementOrdersRepository.Queryable()
                .Where(x => x.CreationTime >= queryIndustryOrderStatisticsRequest.StartTime && x.CreationTime <= queryIndustryOrderStatisticsRequest.EndTime);
            var queryable = judicialManagementOrderTable.InnerJoin<OrderSnapshot>((j, o) => j.Id == o.Id)
                .GroupBy((j, o) => o.IndustryName)
                .Select((j, o) => new IndustryOrderStaticsDto
                {
                    IndustryName = o.IndustryName,
                    TotalOrderCount = SqlFunc.AggregateCount(o.Id),
                    EnforcementOrderCount = SqlFunc.AggregateCount(SqlFunc.IIF(j.IsEnforcementOrder.HasValue && j.IsEnforcementOrder == true, 1, 0)),
                    NotEnforcementOrderCount = SqlFunc.AggregateCount(SqlFunc.IIF(j.IsEnforcementOrder.HasValue && j.IsEnforcementOrder != true, 1, 0)),
                    ToBeVerifiedOrderCount = SqlFunc.AggregateCount(SqlFunc.IIF(j.IsEnforcementOrder == null, 1, 0))
                });


            var result = await queryable.ToListAsync();

            return result;
        }

        public async Task<List<IndustryOrderStaticsRateDto>> QueryIndustryOrderStaticsRate(QueryIndustryOrderStatisticsRateRequest queryIndustryOrderStatisticsRateRequest)
        {
            var judicialManagementOrderTable = _judicialManagementOrdersRepository.Queryable()
                .LeftJoin<Order>((x, o) => x.Id == o.Id)
                .Where((x, o) => x.CreationTime >= queryIndustryOrderStatisticsRateRequest.StartTime && x.CreationTime <= queryIndustryOrderStatisticsRateRequest.EndTime)
                .WhereIF(string.IsNullOrEmpty(queryIndustryOrderStatisticsRateRequest.AreaCode), (x, o) => o.AreaCode != null && o.AreaCode.StartsWith(queryIndustryOrderStatisticsRateRequest.AreaCode));

            var queryable = judicialManagementOrderTable.InnerJoin<OrderSnapshot>((x, o, s) => x.Id == s.Id)
               .GroupBy((x, o, s) => s.IndustryName)
               .Select((x, o, s) => new IndustryOrderStaticsRateDto
               {
                   IndustryName = s.IndustryName,
                   Rate = SqlFunc.AggregateCount(SqlFunc.IIF(o.IsEnforcementOrder.HasValue && o.IsEnforcementOrder == true, 1, 0)) / SqlFunc.AggregateCount(o.Id),
                   Count = SqlFunc.AggregateCount(SqlFunc.IIF(o.IsEnforcementOrder.HasValue && o.IsEnforcementOrder == true, 1, 0))
               });

            var result = await queryable.ToListAsync();

            return result;
        }

        public async Task<List<EventTypeOrderStaticsDto>> QueryEventTypeOrderStatics(QueryEventTypeOrderStaticsRequest queryEventTypeOrderStaticsRequest)
        {
            var judicialManagementOrderTable = _judicialManagementOrdersRepository.Queryable()
                .LeftJoin<Order>((x, o) => x.Id == o.Id)
                .Where((x, o) => x.CreationTime >= queryEventTypeOrderStaticsRequest.StartTime && x.CreationTime <= queryEventTypeOrderStaticsRequest.EndTime)
                .WhereIF(string.IsNullOrEmpty(queryEventTypeOrderStaticsRequest.AreaCode), (x, o) => o.AreaCode != null && o.AreaCode.StartsWith(queryEventTypeOrderStaticsRequest.AreaCode));

            var queryable = judicialManagementOrderTable
               .GroupBy((x, o) => x.EventTypeName)
               .Select((x, o) => new EventTypeOrderStaticsDto
               {
                   EventTypeName = x.EventTypeName,
                   Count = SqlFunc.AggregateCount(o.Id),
               });

            var result = await queryable.ToListAsync();

            return result;
        }
    }
}