hotline/src/Hotline.Application/Snapshot/BiSnapshotApplication.cs

using FluentValidation.Results;
using Hotline.FlowEngine.Workflows;
using Hotline.Orders;
using Hotline.Settings.Hotspots;
using Hotline.Share.Attributes;
using Hotline.Share.Dtos.Snapshot;
using Hotline.Share.Enums.FlowEngine;
using Hotline.Share.Enums.Order;
using Hotline.Share.Enums.Snapshot;
using Hotline.Share.Tools;
using Hotline.Snapshot;
using Hotline.Snapshot.Interfaces;
using Hotline.Tools;
using Mapster;
using NPOI.SS.Formula.Functions;
using SqlSugar;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using XF.Domain.Authentications;
using XF.Domain.Dependency;
using XF.Domain.Exceptions;
using XF.Domain.Repository;
using static NPOI.SS.Format.CellNumberFormatter;

namespace Hotline.Application.Snapshot;
public class BiSnapshotApplication : IBiSnapshotApplication, IScopeDependency
{
    private readonly IOrderSnapshotRepository _orderSnapshotRepository;
    private readonly IRedPackRecordRepository _redPackRecordRepository;
    private readonly IIndustryRepository _industryRepository;
    private readonly IIndustryCaseRepository _industryCaseRepository;
    private readonly IRedPackAuditRepository _redPackAuditRepository;
    private readonly IRepository<Hotspot> _hotspotTypeRepository;
    private readonly ISessionContext _sessionContext;

    public BiSnapshotApplication(IOrderSnapshotRepository orderSnapshotRepository, IRedPackRecordRepository redPackRecordRepository, IIndustryRepository industryRepository, IIndustryCaseRepository industryCaseRepository, IRedPackAuditRepository redPackAuditRepository, IRepository<Hotspot> hotspotTypeRepository, ISessionContext sessionContext)
    {
        _orderSnapshotRepository = orderSnapshotRepository;
        _redPackRecordRepository = redPackRecordRepository;
        _industryRepository = industryRepository;
        _industryCaseRepository = industryCaseRepository;
        _redPackAuditRepository = redPackAuditRepository;
        _hotspotTypeRepository = hotspotTypeRepository;
        _sessionContext = sessionContext;
    }

    public ISugarQueryable<HotspotStatisticsOutDto> GetHotspotStatistics(HotspotStatisticsInDto dto)
    {
        var IsCenter = _sessionContext.OrgIsCenter;
        string count = "2";
        string countx = string.Empty;
        if (dto.HotspotCode.NotNullOrEmpty())
        {
            count = (dto.HotspotCode.Length + 2).ToString();
            dto.HotspotCode.Length.ToString();
        }
        return _hotspotTypeRepository.Queryable()
        .LeftJoin<Order>((it, o) => it.Id == o.HotspotId)
        .LeftJoin<OrderSnapshot>((it, o, s) => o.Id == s.Id)
        .Where((it, o, s) => o.CreationTime >= dto.StartTime && o.CreationTime <= dto.EndTime && s.Id != null)
        .WhereIF(dto.HotspotCode.IsNullOrEmpty(), (it, o) => o.Id != null)
        .WhereIF(dto.HotspotCode.NotNullOrEmpty(), (it, o) => it.ParentId.Substring(SqlFunc.MappingColumn<int>("0"), SqlFunc.MappingColumn<int>(countx)) == dto.HotspotCode)
        .WhereIF(IsCenter == false, (it, o) => o.ActualHandleOrgCode.StartsWith(_sessionContext.RequiredOrgId))
        .GroupBy((it, o) => new { Id = it.Id.Substring(SqlFunc.MappingColumn<int>("0"), SqlFunc.MappingColumn<int>(count)) })
        .Select((it, o) => new
        {
            HotspotCode = it.Id.Substring(SqlFunc.MappingColumn<int>("0"), SqlFunc.MappingColumn<int>(count)),
            SumCount = SqlFunc.AggregateCount(it.HotSpotName)
        })
        .MergeTable()
        .LeftJoin<Hotspot>((x, q) => x.HotspotCode == q.Id)
        .Select((x, q) => new HotspotStatisticsOutDto
        {
            HotspotCode = x.HotspotCode,
            SumCount = x.SumCount,
            HotspotName = q.HotSpotName,
            HasChild = SqlFunc.Subqueryable<Hotspot>().Where(d => d.ParentId == x.HotspotCode).Any()
        });
    }

    /// <summary>
    /// 市民红包审批统计
    /// </summary>
    /// <param name="dto"></param>
    /// <param name="requestAborted"></param>
    /// <returns></returns>
    /// <exception cref="NotImplementedException"></exception>
    [ExportExcel("市民红包审核统计")]
    public IList<RedPackStatisticsOutDto> GetRedPackAuditStatistics(RedPackStatisticsInDto dto)
    {
        var industries = _industryRepository.Queryable(includeDeleted: true)
            .LeftJoin<IndustryCase>((industry, industryCase) => industry.Id == industryCase.IndustryId && industryCase.IsEnable == true)
            .Select((industry, industryCase) => new RedPackStatisticsOutDto
            {
                Id = industry.Id,
                Name = industry.Name,
                CaseId = industryCase.Id,
                CaseName = industryCase.Name,
                ShouldAmount = industryCase.CitizenReadPackAmount == null ? industry.CitizenReadPackAmount : industryCase.CitizenReadPackAmount,
            }).ToList();

        var redPackOutDto = _redPackAuditRepository.Queryable(includeDeleted: true)
            .LeftJoin<OrderSnapshot>((audit, snapshot) => audit.OrderId == snapshot.Id)
            .LeftJoin<RedPackRecord>((audit, snapshot, record) => record.RedPackAuditId == audit.Id)
            .LeftJoin<SupplementRecord>((audit, snapshot, record, supplement) => supplement.RedPackAuditId == audit.Id)
            .Where((audit, snapshot) => audit.CreationTime >= dto.StartTime && audit.CreationTime <= dto.EndTime)
            .GroupBy((audit, snapshot) => new { snapshot.IndustryCase, snapshot.IndustryId, snapshot.IndustryName })
            .Select((audit, snapshot, record, supplement) => new RedPackStatisticsOutDto
            {
                Id = snapshot.IndustryId,
                Name = snapshot.IndustryName,
                CaseId = snapshot.IndustryCase,
                ApprovalAmount = SqlFunc.AggregateSum(SqlFunc.IIF(audit.Status == ERedPackAuditStatus.Agree, audit.ApprovedAmount, 0)), //审批同意总金额
                ApprovalCount = SqlFunc.AggregateSum(SqlFunc.IIF(audit.Status == ERedPackAuditStatus.Agree, 1, 0)), // 审批同意总个数
                SentAmount = SqlFunc.AggregateSum(SqlFunc.IIF(audit.IsSend == true, audit.AcutalAmount, 0)), // 发送成功金额
                SentCount = SqlFunc.AggregateSum(SqlFunc.IIF(audit.IsSend == true, 1, 0)), // 发送成功个数
                SendFailAmount = SqlFunc.AggregateSum(SqlFunc.IIF(record.DistributionState == EReadPackSendStatus.Fail, record.Amount, 0)), //发送失败金额
                SendFailCount = SqlFunc.AggregateSum(SqlFunc.IIF(record.DistributionState == EReadPackSendStatus.Fail, 1, 0)), // 发送失败个数
                PendingAmount = SqlFunc.AggregateSum(SqlFunc.IIF(record.DistributionState == EReadPackSendStatus.Unsend, audit.ApprovedAmount, 0)), // 待发金额
                PendingCount = SqlFunc.AggregateSum(SqlFunc.IIF(record.DistributionState == EReadPackSendStatus.Unsend, 1, 0)), // 待发个数
                SupplementAmount = SqlFunc.AggregateSum(supplement.ReplenishAmount), // 补充红包金额
                SupplementCount = SqlFunc.AggregateCount(supplement.Id), // 补充红包数
            }).ToList();

        foreach (var industry in industries)
        {
            var item = redPackOutDto
                .WhereIF(industry.CaseId != null, m => m.CaseId == industry.CaseId)
                .WhereIF(industry.CaseId == null, m => m.Id == industry.Id)
                .FirstOrDefault();

            var config = new TypeAdapterConfig();
            config.ForType<RedPackStatisticsOutDto, RedPackStatisticsOutDto>()
                .Ignore(dest => dest.CaseId)
                .Ignore(dest => dest.CaseName)
                .Ignore(dest => dest.ShouldAmount);
            item?.Adapt(industry, config);
            if (industry.CaseId == null)
            {
                industry.IndustryName = $"{industry.Name}({industry.ShouldAmount?.ToString("f2")})";
                industry.IndustryType = 1;
                industry.IndustryId = industry.Id;
            }
            else
            {
                industry.IndustryType = 2;
                industry.IndustryId = industry.CaseId;
                if (industry.CaseName == industry.Name)
                    industry.IndustryName = $"{industry.Name}({industry.ShouldAmount?.ToString("f2")})";
                else
                {
                    industry.IndustryName = $"{industry.Name}-{industry.CaseName}({industry.ShouldAmount?.ToString("f2")})";
                }
            }
        }
        return industries;
    }

    public ISugarQueryable<RedPackStatisticsDetailsOutDto> GetRedPackAuditStatisticsDetails(RedPackStatisticsDetailsInDto dto)
    {
        dto.ValidateObject();
        dto.FieldName = dto.FieldName.ToLower();
        var query = _orderSnapshotRepository.Queryable(includeDeleted: true)
            .LeftJoin<Order>((snapshot, order) => order.Id == snapshot.Id)
            .LeftJoin<RedPackAudit>((snapshot, order, audit) => snapshot.Id == audit.OrderId)
            .LeftJoin<RedPackRecord>((snapshot, order, audit, record) => audit.Id == record.RedPackAuditId)
            .LeftJoin<SupplementRecord>((snapshot, order, audit, record, supplement) => audit.Id == supplement.RedPackAuditId)
            .Where((snapshot, order, audit) => audit.CreationTime >= dto.StartTime && audit.CreationTime <= dto.EndTime)
            .WhereIF(dto.IndustryType == 1, (snapshot, order, audit) => snapshot.IndustryId == dto.IndustryId)
            .WhereIF(dto.IndustryType == 2, (snapshot, order, audit) => snapshot.IndustryCase == dto.IndustryId);
        query = dto.FieldName switch
        {
            "approvalamount" => query.Where((snapshot, order, audit) => audit.Status == ERedPackAuditStatus.Agree),
            "approvalcount" => query.Where((snapshot, order, audit) => audit.Status == ERedPackAuditStatus.Agree),
            "sentamount" => query.Where((snapshot, order, audit) => audit.IsSend == true),
            "sentcount" => query.Where((snapshot, order, audit) => audit.IsSend == true),
            "sendfailamount" => query.Where((snapshot, order, audit, record) => record.DistributionState == EReadPackSendStatus.Fail),
            "sendfailcount" => query.Where((snapshot, order, audit, record) => record.DistributionState == EReadPackSendStatus.Fail),
            "pendingamount" => query.Where((snapshot, order, audit, record) => record.DistributionState == EReadPackSendStatus.Unsend),
            "pendingcount" => query.Where((snapshot, order, audit, record) => record.DistributionState == EReadPackSendStatus.Unsend),
            "supplementamount" => query.Where((snapshot, order, audit, record, supplement) => supplement.Id != null),
            "supplementcount" => query.Where((snapshot, order, audit, record, supplement) => supplement.Id != null),
            _ => throw new UserFriendlyException($"不支持输入字段: {dto.FieldName}")
        };
        return query.Select((snapshot, order, audit, record, supplement) => new RedPackStatisticsDetailsOutDto
        {
            CreationTime = order.CreationTime
        }, true);
    }

    public async Task<SnapshotStatisticsOutDto> GetSnapshotStatisticsAsync(SnapshotStatisticsInDto dto, CancellationToken token)
    {
        var query = _orderSnapshotRepository.Queryable(includeDeleted: true)
            .LeftJoin<Order>((snapshot, order) => order.Id == snapshot.Id)
            .LeftJoin<RedPackAudit>((snapshot, order, redPackAudit) => snapshot.Id == redPackAudit.OrderId)
            .LeftJoin<SpecialRedPackAudit>((snapshot, order, redPackAudit, special) => snapshot.Id == special.OrderId)
            .LeftJoin<RedPackGuiderAudit>((snapshot, order, redPackAudit, special, guiderAudit) => snapshot.Id == guiderAudit.OrderId)
            .LeftJoin<RedPackRecord>((snapshot, order, redPackAudit, special, guiderAudit, record) => redPackAudit.Id == record.RedPackAuditId)
            .LeftJoin<OrderSecondaryHandling>((snapshot, order, redPackAudit, special, guiderAudit, record, second) => snapshot.Id == second.OrderId)
             .LeftJoin<OrderSpecial>((snapshot, order, redPackAudit, special, guiderAudit, record, second, orderSpecial) => snapshot.Id == orderSpecial.OrderId)
            .Where((snapshot) => snapshot.CreationTime >= dto.StartTime && snapshot.CreationTime <= dto.EndTime)
            .Select((snapshot, order, redPackAudit, special, guiderAudit, record, second, orderSpecial) => new SnapshotStatisticsOutDto
            {
                WZSLFWNJS = SqlFunc.AggregateSum(SqlFunc.IIF(order.HotspotName == "非受理范围", 1, 0)), // 未在受理范围内件数,
                SSPZ12345JS = SqlFunc.AggregateSum(SqlFunc.IIF(order.SourceChannelCode == "SJP12345", 1, 0)), // 随手拍转12345件数,
                SLFWNZJS = SqlFunc.AggregateSum(SqlFunc.IIF(order.HotspotName != "非受理范围" && order.SourceChannelCode == "SJP12345", 1, 0)), // 受理范围内总件数,
                SLFWNPGWGYSXSNHFJS = SqlFunc.AggregateSum(SqlFunc.IIF(snapshot.IsGuidSystemCallBack == true && snapshot.GuidSystemCallBackTime.Value.AddHours(-4) > snapshot.SendGuidSystemTime, 1, 0)), // 受理范围内派给网格员四小时内回复件数,
                SLFWNPGWGYCGSXSHFJS = SqlFunc.AggregateSum(SqlFunc.IIF(snapshot.IsGuidSystemCallBack == true && snapshot.GuidSystemCallBackTime.Value.AddHours(-4) <= snapshot.SendGuidSystemTime, 1, 0)), // 受理范围内派给网格员超过四小时回复件数
                SLFWNPGWGYWHFJS = SqlFunc.AggregateSum(SqlFunc.IIF(snapshot.IsGuidSystemCallBack == false, 1, 0)), // 受理范围内派给网格员未回复件数
                SLFWNA12345ZPGGBMJS = 0, //受理范围内按12345直派给各部门件数,
                SLFWNA12345ZPGGQXJS = 0, //受理范围内按12345直派给各区县件数
                ZXYB = SqlFunc.AggregateSum(SqlFunc.IIF(order.FileOrgIsCenter == true, 1, 0)), // 中心已办
                BMYB = SqlFunc.AggregateSum(SqlFunc.IIF(order.FileOrgIsCenter == false, 1, 0)), // 部门已办
                SLFWMYD = 0, //受理范围满意度
                MYL = SqlFunc.AggregateSum(SqlFunc.IIF(SqlFunc.JsonListObjectAny(order.OrgProcessingResults, "key", "1") || SqlFunc.JsonListObjectAny(order.OrgProcessingResults, "key", "2"), 0, 1)), // 满意量
                BMYL = SqlFunc.AggregateSum(SqlFunc.IIF(SqlFunc.JsonListObjectAny(order.OrgProcessingResults, "key", "1") || SqlFunc.JsonListObjectAny(order.OrgProcessingResults, "key", "2"), 1, 0)), // 不满意量
                SPBTYHBGS = SqlFunc.AggregateSum(SqlFunc.IIF(redPackAudit.Status == ERedPackAuditStatus.Refuse, 1, 0)), // 审批不同意红包个数
                SPTYHBGS = SqlFunc.AggregateSum(SqlFunc.IIF(redPackAudit.Status == ERedPackAuditStatus.Agree, 1, 0)), // 审批同意红包个数
                TSHBSP = 0, //特殊红包审批统计
                SPTYGS = SqlFunc.AggregateSum(SqlFunc.IIF(special.Status == ERedPackAuditStatus.Agree, 1, 0)), //审批同意个数
                YFJE = SqlFunc.AggregateSum(SqlFunc.IIF(special.IsSend == true, special.AcutalAmount, 0)), // 已发金额
                JSHFFWGJLGS = 0, //局审核发放网格员奖励个数
                SPTYWGYHBGS = SqlFunc.AggregateSum(SqlFunc.IIF(guiderAudit.LevelTwoStatus == ERedPackAuditStatus.Agree, 1, 0)), //审批同意（网格员）红包个数
                SPBTYWGYHBGS = SqlFunc.AggregateSum(SqlFunc.IIF(guiderAudit.LevelOneStatus == ERedPackAuditStatus.Refuse || guiderAudit.LevelTwoStatus == ERedPackAuditStatus.Refuse, 1, 0)),  //审批不同意（网格员）红包个数
                SMJLZE = SqlFunc.AggregateSum(redPackAudit.AcutalAmount), // 市民奖励总额
                SMYFFJLZE = SqlFunc.AggregateSum(SqlFunc.IIF(redPackAudit.IsSend == true, redPackAudit.AcutalAmount, 0)),  // 市民已发放奖励总额
                SMDFFJLZE = SqlFunc.AggregateSum(SqlFunc.IIF(redPackAudit.IsSend == false, redPackAudit.ApprovedAmount, 0)),  // 市民待发奖励总额
                YFG = SqlFunc.AggregateSum(SqlFunc.IIF(redPackAudit.IsSend == true, 1, 0)), //已发（个）
                WFLXG = 0, // 无法联系（个）
                WJHBG = SqlFunc.AggregateSum(SqlFunc.IIF(record.FailCase == ERedPackPickupFailCase.Excuse, 1, 0)),  // 婉拒红包（个）
                WGYYFJLJE = SqlFunc.AggregateSum(SqlFunc.IIF(guiderAudit.LevelTwoStatus == ERedPackAuditStatus.Agree, guiderAudit.AcutalAmount, 0)), //网格员应发奖励金额
                WGYYFFJLZE = SqlFunc.AggregateSum(SqlFunc.IIF(guiderAudit.IsSend == true, guiderAudit.AcutalAmount, 0)), // 网格员已发放奖励总额
                WGYDFFJLZE = SqlFunc.AggregateSum(SqlFunc.IIF(guiderAudit.LevelTwoStatus == ERedPackAuditStatus.Agree && guiderAudit.IsSend == false, guiderAudit.ApprovedAmount, 0)), // 网格员待发放奖励总额
                WGYKKZEYF = 0, // 网格员扣款总额（已发）
                WGYKKZEDF = 0, // 网格员扣款总额（待发）
                SLFWNDBMHQJJS = SqlFunc.AggregateSum(SqlFunc.IIF(order.CounterSignType != null, 1, 0)), // 受理范围内多部门会签件件数
                SLFWNRXZXGDJS = SqlFunc.AggregateSum(SqlFunc.IIF(order.FileOrgIsCenter == true, 1, 0)), // 受理范围内热线中心归档件数
                RXZXFQHQJJS = SqlFunc.AggregateSum(SqlFunc.IIF(order.CounterSignType != null && order.CounterSignType == ECounterSignType.Center, 1, 0)), // 热线中心发起会签件件数
                AQYH = SqlFunc.AggregateSum(SqlFunc.IIF(snapshot.IsDangerDepartment == true, 1, 0)), // 安全隐患
                YWCAQYHZG = SqlFunc.AggregateSum(SqlFunc.IIF(snapshot.IsRectifyDepartment == true, 1, 0)), // 已完成安全隐患整改
                SQYQGDJS = SqlFunc.AggregateSum(SqlFunc.IIF(order.OrderDelays.Count(m => m.DelayState == EDelayState.Pass) > 0, 1, 0)), // 申请延期工单件数
                SQYQGDCS = SqlFunc.AggregateSum(SqlFunc.IIF(order.OrderDelays.Count() > 0, order.OrderDelays.Count(m => m.DelayState == EDelayState.Pass), 0)), // 申请延期工单次数
                CQJ = SqlFunc.AggregateSum(SqlFunc.IIF(order.Status >= EOrderStatus.Filed && order.ExpiredTime < order.FiledTime, 1, 0)), // 超期件
                ECBLJSTHBM = SqlFunc.AggregateSum(SqlFunc.IIF(second.State == ESecondaryHandlingState.Handled && second.ApplyOrgId != "001", 1, 0)), // 二次办理件数 - 退回部门
                ECBLJSHFBMYCB = SqlFunc.AggregateSum(SqlFunc.IIF(second.State == ESecondaryHandlingState.Handled, 1, 0)), // 二次办理件数-回访不满意重办
                ECBLJSTTDYYJBM = SqlFunc.AggregateSum(SqlFunc.IIF(second.State == ESecondaryHandlingState.Handled, 1, 0)), // 二次办理件数-特提到原一级部门
                ECBLJSHFMY = SqlFunc.AggregateSum(SqlFunc.IIF(second.State == ESecondaryHandlingState.Handled && (SqlFunc.JsonField(order.OrgProcessingResults, "Key") == "5" || SqlFunc.JsonField(order.OrgProcessingResults, "Key") == "4" || SqlFunc.JsonField(order.OrgProcessingResults, "Key") == "-1" || SqlFunc.JsonField(order.OrgProcessingResults, "Key") == "0"), 1, 0)), // 二次办理件数-回访满意
                // ECBLGDMYL = , // 二次办理工单满意率
                ECBLGDJSTMBMHFMYD = SqlFunc.AggregateSum(SqlFunc.IIF(second.State == ESecondaryHandlingState.Handled && second.ApplyOrgId != "001" && (SqlFunc.JsonField(order.OrgProcessingResults, "Key") == "5" || SqlFunc.JsonField(order.OrgProcessingResults, "Key") == "4" || SqlFunc.JsonField(order.OrgProcessingResults, "Key") == "-1" || SqlFunc.JsonField(order.OrgProcessingResults, "Key") == "0"), 1, 0)), // 二次办理工单件数-退回部门回访满意
                // ECBLGDMYLTHBM = , // 二次办理工单满意率-退回部门
                // ECBLGDMYLHFBMYCB = 0, // 二次办理工单满意率-回访不满意重办

                TTDYYJBMJS = SqlFunc.AggregateSum(SqlFunc.IIF(orderSpecial.State == 1 && orderSpecial.SpecialType == ESpecialType.Special && orderSpecial.NextStepName == "一级部门", 1, 0)), // 特提到原一级部门件数
                TTDPDZJS = SqlFunc.AggregateSum(SqlFunc.IIF(orderSpecial.State == 1 && orderSpecial.SpecialType == ESpecialType.Special && orderSpecial.NextStepName == "派单组", 1, 0)), // 特提到派单组件数
                QTTTJS = SqlFunc.AggregateSum(SqlFunc.IIF(orderSpecial.State == 1 && orderSpecial.SpecialType == ESpecialType.Special && (orderSpecial.NextStepName != "一级部门" || orderSpecial.NextStepName != "派单组"), 1, 0)), // 其他特提件数


            });
        return await query.FirstAsync();
    }

    public ISugarQueryable<SnapshotStatisticsDetailOutDto> GetSnapshotStatisticsDetail(SnapshotStatisticsDetailInDto dto)
    {
        dto.FieldName = dto.FieldName.ToUpper();
        var query = _orderSnapshotRepository.Queryable(includeDeleted: true)
            .LeftJoin<Order>((snapshot, order) => order.Id == snapshot.Id)
            .LeftJoin<RedPackAudit>((snapshot, order, redPackAudit) => snapshot.Id == redPackAudit.OrderId)
            .LeftJoin<SpecialRedPackAudit>((snapshot, order, redPackAudit, special) => snapshot.Id == special.OrderId)
            .LeftJoin<RedPackGuiderAudit>((snapshot, order, redPackAudit, special, guiderAudit) => snapshot.Id == guiderAudit.OrderId)
            .Where((snapshot, order) => order.CreationTime >= dto.StartTime && order.CreationTime <= dto.EndTime);
        query = dto.FieldName switch
        {
            "WZSLFWNJS" => query.Where((snapshot, order) => order.SourceChannelCode != "SSP"),
            "SSPZ12345JS" => query.Where((snapshot, order) => order.SourceChannelCode != "SSP"),
            "SLFWNPGWGYSXSNHFJS" => query.Where((snapshot, order) => snapshot.IsGuidSystemCallBack == true && snapshot.GuidSystemCallBackTime!.Value.AddHours(-4) <= snapshot.SendGuidSystemTime),
            "SLFWNPGWGYCGSXSHFJS" => query.Where((snapshot, order) => snapshot.IsGuidSystemCallBack == true && snapshot.GuidSystemCallBackTime!.Value.AddHours(-4) <= snapshot.SendGuidSystemTime),
            "SLFWNPGWGYWHFJS" => query.Where((snapshot, order) => snapshot.IsGuidSystemCallBack == false),
            "ZXYB" => query.Where((snapshop, order) => order.FileOrgIsCenter == true),
            "BMYB" => query.Where((snapshop, order) => order.FileOrgIsCenter == false),
            "MYL" => query.Where((snapshot, order) => SqlFunc.JsonListObjectAny(order.OrgProcessingResults, "key", "-1") || SqlFunc.JsonListObjectAny(order.OrgProcessingResults, "key", "3") || SqlFunc.JsonListObjectAny(order.OrgProcessingResults, "key", "4") || SqlFunc.JsonListObjectAny(order.OrgProcessingResults, "key", "5")),
            "BMYL" => query.Where((snapshot, order) => SqlFunc.JsonListObjectAny(order.OrgProcessingResults, "key", "1") || SqlFunc.JsonListObjectAny(order.OrgProcessingResults, "key", "2")),
            "SPBTYHBGS" => query.Where((snapshot, order, redPackAudit) => redPackAudit.Status == ERedPackAuditStatus.Refuse),
            "SPTYHBGS" => query.Where((snapshot, order, redPackAudit) => redPackAudit.Status == ERedPackAuditStatus.Agree),
            "SPTYGS" => query.Where((snapshot, order, redPackAudit, special) => special.Status == ERedPackAuditStatus.Agree),
            "YFJE" => query.Where((snapshot, order, redPackAudit, special) => special.IsSend == true),
            "SPTYWGYHBGS" => query.Where((snapshot, order, redPackAudit, special, guiderAudit) => guiderAudit.LevelTwoStatus == ERedPackAuditStatus.Agree),
            "SPBTYWGYHBGS" => query.Where((snapshot, order, redPackAudit, special, guiderAudit) => guiderAudit.LevelOneStatus == ERedPackAuditStatus.Refuse || guiderAudit.LevelTwoStatus == ERedPackAuditStatus.Refuse),
            _ => throw new UserFriendlyException("入参错误:" + dto.FieldName + " 未实现")
        };

        return query.Select((snapshot, order, redPackAudit, special, guiderAudit) => new SnapshotStatisticsDetailOutDto
        {
            CreationTime = order.CreationTime
        }, true);
    }
}