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

using FluentValidation.Results;
using Hotline.Configurations;
using Hotline.FlowEngine.Workflows;
using Hotline.Orders;
using Hotline.Repository.SqlSugar.Orders;
using Hotline.Settings;
using Hotline.Settings.Hotspots;
using Hotline.Share.Attributes;
using Hotline.Share.Dtos;
using Hotline.Share.Dtos.Order;
using Hotline.Share.Dtos.Snapshot;
using Hotline.Share.Enums.FlowEngine;
using Hotline.Share.Enums.Order;
using Hotline.Share.Enums.Snapshot;
using Hotline.Share.Requests;
using Hotline.Share.Tools;
using Hotline.Snapshot;
using Hotline.Snapshot.Interfaces;
using Hotline.Tools;
using Mapster;
using MediatR;
using Microsoft.AspNetCore.Http;
using Microsoft.AspNetCore.Mvc;
using Microsoft.Extensions.Options;
using NPOI.SS.Formula.Functions;
using Org.BouncyCastle.Asn1.Pkcs;
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.HSSF.Util.HSSFColor;
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;
    private readonly IOrderRepository _orderRepository;
    private readonly ICommunityInfoRepository _communityInfoRepository;
    private readonly IRepository<SystemArea> _systemAreaRepository;
    private readonly IOptionsSnapshot<AppConfiguration> _appOptions;

    public BiSnapshotApplication(IOrderSnapshotRepository orderSnapshotRepository, IRedPackRecordRepository redPackRecordRepository, IIndustryRepository industryRepository, IIndustryCaseRepository industryCaseRepository, IRedPackAuditRepository redPackAuditRepository, IRepository<Hotspot> hotspotTypeRepository, ISessionContext sessionContext, IOrderRepository orderRepository, ICommunityInfoRepository communityInfoRepository, IRepository<SystemArea> systemAreaRepository, IOptionsSnapshot<AppConfiguration> appOptions)
    {
        _orderSnapshotRepository = orderSnapshotRepository;
        _redPackRecordRepository = redPackRecordRepository;
        _industryRepository = industryRepository;
        _industryCaseRepository = industryCaseRepository;
        _redPackAuditRepository = redPackAuditRepository;
        _hotspotTypeRepository = hotspotTypeRepository;
        _sessionContext = sessionContext;
        _orderRepository = orderRepository;
        _communityInfoRepository = communityInfoRepository;
        _systemAreaRepository = systemAreaRepository;
        _appOptions = appOptions;
    }

    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();
            countx = 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>
    /// <returns></returns>
    public ISugarQueryable<HotspotStatisticsDetailsOutDto> HotspotStatisticsDetail([FromQuery] HotspotStatisticsDetailsInDto dto)
    {
        var IsCenter = _sessionContext.OrgIsCenter;
        IsCenter = true;
        var query = _orderRepository.Queryable()
            .Includes(d => d.OrderVisits)
            .Where(d => d.CreationTime >= dto.StartTime && d.CreationTime <= dto.EndTime)
            .WhereIF(dto.HotspotCode.NotNullOrEmpty(), d => d.HotspotId.StartsWith(dto.HotspotCode))
            .WhereIF(IsCenter == false, d => d.ActualHandleOrgCode.StartsWith(_sessionContext.RequiredOrgId))
            .Select(d => new HotspotStatisticsDetailsOutDto
            {
                OrderScreenStatus = SqlFunc.Subqueryable<OrderScreen>().Where(q => q.OrderId == d.Id).OrderByDesc(q => q.CreationTime).Select(q => q.Status), //x.OrderScreens.FirstOrDefault().Status,
            }, true);
        return query;
    }

    /// <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);
    }

    /// <summary>
    /// 办件统计-随手拍
    /// </summary>
    /// <param name="dto"></param>
    /// <returns></returns>
    /// <exception cref="NotImplementedException"></exception>
    public ISugarQueryable<SnapshotProcessingStatisticsOutDto> GetSnapshotProcessingStatistics(SnapshotProcessingStatisticsInDto dto)
    {
        bool IsCenter = _sessionContext.OrgIsCenter;
        var orgLevel = _sessionContext.OrgLevel;
        string orgLevelStr = (_sessionContext.RequiredOrgId.Length + 3).ToString();

        var query = _orderSnapshotRepository.Queryable(includeDeleted: true)
            .LeftJoin<Order>((snapshot, order) => snapshot.Id == order.Id)
            .LeftJoin<OrderSendBackAudit>((snapshot, order, back) => snapshot.Id == back.OrderId && back.State == ESendBackAuditState.End)
            .LeftJoin<OrderVisit>((snapshot, order, back, visit) => snapshot.Id == visit.OrderId && visit.VisitState == EVisitState.Visited)
            .LeftJoin<OrderSecondaryHandling>((snapshot, order, back, visit, second) => snapshot.Id == second.OrderId && second.State == ESecondaryHandlingState.End)
            .Where(snapshot => snapshot.CreationTime >= dto.StartTime && snapshot.CreationTime <= dto.EndTime)
            .WhereIF(dto.IndustryId.NotNullOrEmpty(), snapshot => snapshot.IndustryId == dto.IndustryId)
            .GroupBy((snapshot, order) => new
            {
                OrgCode = order.ActualHandleOrgCode.Substring(SqlFunc.MappingColumn<int>("0"), SqlFunc.MappingColumn<int>("6"))
            })
            .Select((snapshot, order, back, visit, second) => new SnapshotProcessingStatisticsOutDto()
            {
                OrgCode = order.ActualHandleOrgCode.Substring(SqlFunc.MappingColumn<int>("0"), SqlFunc.MappingColumn<int>("6")),
                YbOrderCountNum = SqlFunc.AggregateCount(SqlFunc.IIF(order.Status >= EOrderStatus.Filed, 1, 0)),
                ZbOrderCountNum = SqlFunc.AggregateCount(SqlFunc.IIF(order.Status < EOrderStatus.Filed, 1, 0)),
                ReceiveIn20Minutes = SqlFunc.AggregateCount(SqlFunc.IIF(order.ActualHandleStepCreateTime != null && order.ActualHandleStepCreateTime.Value.AddMinutes(-20) <= order.CreationTime, 1, 0)),
                ReceiveOut20Minutes = SqlFunc.AggregateCount(SqlFunc.IIF(order.ActualHandleStepCreateTime == null || order.ActualHandleStepCreateTime.Value.AddMinutes(-20) > order.CreationTime, 1, 0)),
                BackNum = SqlFunc.AggregateCount(SqlFunc.IIF(back.OrderId != null && back.OrderId != "", 1, 0)),
                TotalHandleDuration = SqlFunc.AggregateSum(order.HandleDuration),
                End3Day = SqlFunc.AggregateSum(SqlFunc.IIF(order.FiledTime != null && order.FiledTime.Value.AddDays(-3) < order.CreationTime, 1, 0)),
                End3To5Day = SqlFunc.AggregateSum(SqlFunc.IIF(order.FiledTime != null && order.FiledTime.Value.AddDays(-3) > order.CreationTime && order.FiledTime.Value.AddDays(-5) < order.CreationTime, 1, 0)),
                End5To7Day = SqlFunc.AggregateSum(SqlFunc.IIF(order.FiledTime != null && order.FiledTime.Value.AddDays(-5) > order.CreationTime && order.FiledTime.Value.AddDays(-7) < order.CreationTime, 1, 0)),
                End7Day = SqlFunc.AggregateSum(SqlFunc.IIF(order.FiledTime != null && order.FiledTime.Value.AddDays(-7) < order.CreationTime, 1, 0)),
                OnTimeCount = SqlFunc.AggregateSum(SqlFunc.IIF(order.FiledTime <= order.ExpiredTime, 1, 0)),
                SatisfiedCount = SqlFunc.AggregateSum(SqlFunc.IIF(SqlFunc.JsonListObjectAny(visit.NowEvaluate, "key", "1") || SqlFunc.JsonListObjectAny(visit.NowEvaluate, "key", "2"), 1, 0)),
                NoSatisfiedCount = SqlFunc.AggregateSum(SqlFunc.IIF(SqlFunc.JsonListObjectAny(visit.NowEvaluate, "key", "1") || SqlFunc.JsonListObjectAny(visit.NowEvaluate, "key", "2"), 0, 1)),
                SecondNum = SqlFunc.AggregateCount(second.Id)
            })
            .MergeTable()
            .LeftJoin<SystemOrganize>((it, o) => it.OrgCode == o.Id)
            .Select((it, o) => new SnapshotProcessingStatisticsOutDto()
            {
                OrgName = o.Name,
                OrgCode = it.OrgCode,
                YbOrderCountNum = it.YbOrderCountNum,
                ZbOrderCountNum = it.ZbOrderCountNum,
                ReceiveIn20Minutes = it.ReceiveIn20Minutes,
                ReceiveOut20Minutes = it.ReceiveOut20Minutes,
                BackNum = it.BackNum,
                TotalHandleDuration = it.TotalHandleDuration,
                End3Day = it.End3Day,
                End3To5Day = it.End3To5Day,
                End5To7Day = it.End5To7Day,
                End7Day = it.End7Day,
                OnTimeCount = it.OnTimeCount,
                SatisfiedCount = it.SatisfiedCount,
                NoSatisfiedCount = it.NoSatisfiedCount,
                SecondNum = it.SecondNum
            });

        return query;
    }

    public ISugarQueryable<SnapshotProcessingStatisticsDetailsOutDto> GetSnapshotProcessingStatisticsDetails(SnapshotProcessingStatisticsDetailsInDto dto)
    {
        dto.FieldName = dto.FieldName.ToLower();
        var IsCenter = _sessionContext.OrgIsCenter;
        IsCenter = true;
        var query = _orderSnapshotRepository.Queryable(includeDeleted: true)
            .LeftJoin<Order>((snapshot, order) => snapshot.Id == order.Id)
            .LeftJoin<OrderSendBackAudit>((snapshot, order, back) => snapshot.Id == back.OrderId && back.State == ESendBackAuditState.End)
            .LeftJoin<OrderVisit>((snapshot, order, back, visit) => snapshot.Id == visit.OrderId && visit.VisitState == EVisitState.Visited)
            .LeftJoin<OrderSecondaryHandling>((snapshot, order, back, visit, second) => snapshot.Id == second.OrderId && second.State == ESecondaryHandlingState.End)
            .Where((snapshot, order, back) => order.CreationTime >= dto.StartTime && order.CreationTime <= dto.EndTime && order.ActualHandleOrgCode.StartsWith(dto.OrgId))
            .WhereIF(IsCenter == false, (snapshot, order, back) => order.ActualHandleOrgCode.StartsWith(_sessionContext.RequiredOrgId))
            .WhereIF(dto.IndustryId.NotNullOrEmpty(), (snapshot, order, back) => snapshot.IndustryId == dto.IndustryId);

        query = dto.FieldName switch
        {
            "ybordercountnum" => query.Where((snapshot, order, back) => order.Status >= EOrderStatus.Filed),
            "zbordercountnum" => query.Where((snapshot, order, back) => order.Status < EOrderStatus.Filed),
            "receivein20minutes" => query.Where((snapshot, order, back) => order.ActualHandleStepCreateTime != null && order.ActualHandleStepCreateTime.Value.AddMinutes(-20) <= order.CreationTime),
            "receiveout20minutes" => query.Where((snapshot, order, back) => order.ActualHandleStepCreateTime == null || order.ActualHandleStepCreateTime.Value.AddMinutes(-20) > order.CreationTime),
            "backnum" => query.Where((snapshot, order, back) => back.OrderId != null && back.OrderId != ""),
            "end3day" => query.Where((snapshot, order, back) => order.FiledTime != null && order.FiledTime.Value.AddDays(-3) < order.CreationTime),
            "end3to5day" => query.Where((snapshot, order, back) => order.FiledTime != null && order.FiledTime.Value.AddDays(-3) > order.CreationTime && order.FiledTime.Value.AddDays(-5) < order.CreationTime),
            "end5to7day" => query.Where((snapshot, order, back) => order.FiledTime != null && order.FiledTime.Value.AddDays(-5) > order.CreationTime && order.FiledTime.Value.AddDays(-7) < order.CreationTime),
            "end7day" => query.Where((snapshot, order, back) => order.FiledTime != null && order.FiledTime.Value.AddDays(-7) < order.CreationTime),
            "satisfiedcount" => query.Where((snapshot, order, back, visit) => SqlFunc.JsonListObjectAny(visit.NowEvaluate, "key", "1") || SqlFunc.JsonListObjectAny(visit.NowEvaluate, "key", "2")),
            "nosatisfiedcount" => query.Where((snapshot, order, back, visit) => SqlFunc.JsonListObjectAny(visit.NowEvaluate, "key", "1") || SqlFunc.JsonListObjectAny(visit.NowEvaluate, "key", "2")),
            "secondnum" => query.Where((snapshot, order, back, visit, second) => second.Id != null || second.Id != ""),
            _ => throw new UserFriendlyException($"入参: {dto.FieldName} 异常")
        };
        return query.Select((snapshot, order, back, visit, second) => new SnapshotProcessingStatisticsDetailsOutDto
        {
            OrderScreenStatus = SqlFunc.Subqueryable<OrderScreen>().Where(q => q.OrderId == order.Id).OrderByDesc(q => q.CreationTime).Select(q => q.Status),
        }, true);
    }

    public ISugarQueryable<GuiderWorkStatisticsOutDto> GetGuiderWorkStatisticsAsync(GuiderWorkStatisticsInDto dto)
    {
        var query = _orderSnapshotRepository.Queryable(includeDeleted: true)
            .Where(snapshot => snapshot.CreationTime >= dto.StartTime && snapshot.CreationTime <= dto.EndTime && snapshot.MemberName != null)
            .GroupBy(snapshot => new { snapshot.MemberName, snapshot.MemberMobile })
            .Select(snapshot => new GuiderWorkStatisticsOutDto
            {
                MemberMobile = snapshot.MemberMobile,
                MemberName = snapshot.MemberName,
                ReplyIn4HourCount = SqlFunc.AggregateSum(SqlFunc.IIF(snapshot.ReplyResultType == EGuiderSystemReplyType.Field && snapshot.GuidSystemCallBackTime.Value.AddHours(-4) <= snapshot.SendGuidSystemTime, 1, 0)),
                ReplyOut4HourCount = SqlFunc.AggregateSum(SqlFunc.IIF(snapshot.ReplyResultType == EGuiderSystemReplyType.Field && snapshot.GuidSystemCallBackTime.Value.AddHours(-4) > snapshot.SendGuidSystemTime, 1, 0)),
                UnReplyCount = SqlFunc.AggregateSum(SqlFunc.IIF(snapshot.ReplyResultType != EGuiderSystemReplyType.Field, 1, 0)),
            });

        return query;
    }

    public ISugarQueryable<GuiderWorkStatisticsDetailsOutDto> GetGuiderWorkStatisticsDetails(GuiderWorkStatisticsDetailsInDto dto)
    {
        dto.FieldName = dto.FieldName.ToLower();
        var query = _orderSnapshotRepository.Queryable(includeDeleted: true)
            .LeftJoin<Order>((snapshot, order) => order.Id == snapshot.Id)
            .Where(snapshot => snapshot.CreationTime >= dto.StartTime && snapshot.CreationTime <= dto.EndTime && snapshot.MemberMobile == dto.MemberMobile);

        query = dto.FieldName switch
        {
            "replyin4hourcount" => query.Where(snapshot => snapshot.ReplyResultType == EGuiderSystemReplyType.Field && snapshot.GuidSystemCallBackTime.Value.AddHours(-4) <= snapshot.SendGuidSystemTime),
            "replyout4hourcount" => query.Where(snapshot => snapshot.ReplyResultType == EGuiderSystemReplyType.Field && snapshot.GuidSystemCallBackTime.Value.AddHours(-4) > snapshot.SendGuidSystemTime),
            "unreplycount" => query.Where(snapshot => snapshot.ReplyResultType != EGuiderSystemReplyType.Field),
            _ => throw new UserFriendlyException($"入参: {dto.FieldName} 异常")
        };
        return query.Select((snapshot, order) => new GuiderWorkStatisticsDetailsOutDto
        {
        }, true);
    }

    public ISugarQueryable<HotspotDataStatisticsOutDto> GetHotspotDataStatisticsAsync(HotspotDataStatisticsInDto dto)
    {
        var isCenter = _sessionContext.OrgIsCenter;
        var query = _hotspotTypeRepository.Queryable(includeDeleted: true)
            .LeftJoin<Order>((hotspot, order) => order.HotspotSpliceName != null && (hotspot.HotSpotName == order.HotspotSpliceName || order.HotspotSpliceName.Contains(hotspot.HotSpotName)) && order.IsDeleted == false && order.CreationTime >= dto.StartTime && order.CreationTime <= dto.EndTime)
            .WhereIF(isCenter == false, (hotspot, order) => order.ActualHandleOrgCode == _sessionContext.RequiredOrgId)
            .Where(hotspot => hotspot.ParentId == null)
            .GroupBy((hotspot, order) => new { hotspot.Id, hotspot.HotSpotName })
            .Select((hotspot, order) => new HotspotDataStatisticsOutDto
            {
                Name = hotspot.HotSpotName,
                OrderCount = SqlFunc.AggregateSum(SqlFunc.IIF(order.Id != null, 1, 0)),
            });
        return query;
    }

    public ISugarQueryable<GuiderWorkLogsOutDto> GetGuiderWorkLogs(GuiderWorkLogsInDto dto)
    {
        var query = _orderSnapshotRepository.Queryable()
            .LeftJoin<Order>((snapshot, order) => order.Id == snapshot.Id)
            .Where((snapshot, order) => snapshot.IsGuidSystemCallBack == true)
            .WhereIF(dto.MemberMobile.NotNullOrEmpty(), snapshot => snapshot.MemberMobile.Contains(dto.MemberMobile))
            .WhereIF(dto.BeginCreationTime != null && dto.EndCreationTime != null, snapshot => snapshot.CreationTime >= dto.BeginCreationTime && snapshot.CreationTime <= dto.EndCreationTime)
            .WhereIF(dto.MemberName.NotNullOrEmpty(), snapshot => snapshot.MemberName.Contains(dto.MemberName))
            .WhereIF(dto.No.NotNullOrEmpty(), (snapshot, order) => order.No.Contains(dto.No))
            .WhereIF(dto.Title.NotNullOrEmpty(), (snapshot, order) => order.Title.Contains(dto.Title))
            .WhereIF(dto.NetworkENumber.NotNullOrEmpty(), (snapshot, order) => snapshot.NetworkENumber.Contains(dto.NetworkENumber))
            .WhereIF(dto.Status != null, (snapshot, order) => order.Status == dto.Status)
            .OrderByDescending(snapshot => snapshot.CreationTime)
            .Select(snapshot => new GuiderWorkLogsOutDto(), true);
        return query;
    }

    public ISugarQueryable<DuplicateItemsOutDto> GetDuplicateItems(DuplicateItemsInDto dto)
    {
        var query = _orderSnapshotRepository.Queryable(includeDeleted: true)
            .LeftJoin<Order>((snapshot, order) => order.Id == snapshot.Id)
            .WhereIF(dto.No.NotNullOrEmpty(), (snapshot, order) => order.No.Contains(dto.No))
            .WhereIF(dto.Title.NotNullOrEmpty(), (snapshot, order) => order.Title.Contains(dto.Title))
            .WhereIF(dto.FromName.NotNullOrEmpty(), (snapshot, order) => order.FromName.Contains(dto.FromName))
            .WhereIF(dto.FromPhone.NotNullOrEmpty(), (snapshot, order) => order.FromPhone.Contains(dto.FromPhone))
            .WhereIF(dto.BeginCreationTime != null && dto.EndCreationTime != null, (snapshot, order) => order.CreationTime >= dto.BeginCreationTime && order.CreationTime <= dto.EndCreationTime)
            .WhereIF(dto.BeginExpiredTime != null && dto.EndExpiredTime != null, (snapshot, order) => order.ExpiredTime >= dto.BeginExpiredTime && order.ExpiredTime <= dto.EndExpiredTime)
            .WhereIF(dto.ActualHandleOrgName.NotNullOrEmpty(), (snapshot, order) => order.ActualHandleOrgName == dto.ActualHandleOrgName)
            .WhereIF(dto.AcceptType.NotNullOrEmpty(), (snapshot, order) => order.AcceptType == dto.AcceptType)
            .WhereIF(dto.HotspotName.NotNullOrEmpty(), (snapshot, order) => order.HotspotName == dto.HotspotName)
            .WhereIF(dto.AcceptorName.NotNullOrEmpty(), (snapshot, order) => order.AcceptorName == dto.AcceptorName)
            .WhereIF(dto.IndustryId.NotNullOrEmpty(), (snapshot, order) => snapshot.IndustryId == dto.IndustryId)
            .Select((snapshot, order) => new DuplicateItemsOutDto(), true);
        return query;
    }

    public ISugarQueryable<CommunityStatisticsOutDto> GetCommunityStatistics(CommunityStatisticsInDto dto)
    {
        var query = _communityInfoRepository.Queryable(includeDeleted: true)
        .LeftJoin<OrderSnapshot>((it, s) => s.CommunityFullName.Contains(it.FullName) && s.CreationTime >= dto.StartTime && s.CreationTime <= dto.EndTime)
        .WhereIF(dto.CommunityCode.IsNullOrEmpty(), (it, s) => it.ParentCode == null)
        .WhereIF(dto.CommunityCode.NotNullOrEmpty(), (it, s) => it.ParentCode == dto.CommunityCode)
        .WhereIF(dto.IndustryId.NotNullOrEmpty(), (it, s) => s.IndustryId == dto.IndustryId)
        .GroupBy((it, s) => new { it.Name, it.Id })
        .Select((it, s) => new CommunityStatisticsOutDto
        {
            CommunityCode = it.Id,
            CommunityName = it.Name,
            CommunityFullName = it.FullName,
            SumCount = SqlFunc.Subqueryable<OrderSnapshot>().Where(snapshot => snapshot.CommunityFullName!.Contains(it.FullName)).Count(),
            HasChild = SqlFunc.Subqueryable<CommunityInfo>().Where(c => c.ParentCode == it.Id).Any()
        });
#if DEBUG
        var sql = query.ToSqlString();
#endif
        return query;
    }

    public ISugarQueryable<CommunityStatisticsDetailsOutDto> GetCommunityStatisticsDetails(CommunityStatisticsDetailsInDto dto)
    {
        var communityFullName = _communityInfoRepository.Queryable().Where(c => c.Id == dto.CommunityCode).Select(c => c.FullName).First();
        var query = _orderSnapshotRepository.Queryable(includeDeleted: true)
            .LeftJoin<Order>((snapshot, order) => snapshot.Id == order.Id)
            .Where((snapshot, order) => snapshot.CreationTime >= dto.StartTime && snapshot.CreationTime <= dto.EndTime && snapshot.CommunityFullName!.Contains(communityFullName))
            .WhereIF(dto.IndustryId.NotNullOrEmpty(), (snapshot, order) => snapshot.IndustryId == dto.IndustryId)
            .Select((snapshot, order) => new CommunityStatisticsDetailsOutDto(), true);
#if DEBUG
        var sql = query.ToSqlString();
#endif
        return query;
    }

    [ExportExcel("随手拍区域统计", "County")]
    public ISugarQueryable<CountyRedPackStatisticsOutDto> GetCountyRedPackStatistics(CountyRedPackStatisticsInDto dto)
    {
        var parentId = _appOptions.Value.AreaCode;
        var query = _systemAreaRepository.Queryable(includeDeleted: true)
            .LeftJoin<Order>((area, order) => order.County == area.AreaName && order.CreationTime >= dto.StartTime && order.CreationTime <= dto.EndTime && order.County != null)
            .LeftJoin<OrderSnapshot>((area, order, snapshot) => snapshot.Id == order.Id && (dto.IndustryId.IsNullOrEmpty() || snapshot.IndustryId == dto.IndustryId))
            .LeftJoin<RedPackAudit>((area, order, snapshot, audit) => snapshot.Id == audit.OrderId)
            .LeftJoin<RedPackRecord>((area, order, snapshot, audit, record) => audit.Id == record.RedPackAuditId)
            .LeftJoin<SupplementRecord>((area, order, snapshot, audit, record, supplement) => supplement.RedPackAuditId == audit.Id)
            .LeftJoin<RedPackGuiderAudit>((area, order, snapshot, audit, record, supplement, guiderAudit) => guiderAudit.OrderId == snapshot.Id)
            .LeftJoin<RedPackRecord>((area, order, snapshot, audit, record, supplement, guiderAudit, guiderRecord) => guiderAudit.Id == guiderRecord.RedPackAuditId)
            .LeftJoin<Industry>((area, order, snapshot, audit, record, supplement, guiderAudit, guiderRecord, industry) => snapshot.IndustryId == industry.Id)
            .Where((area, order, snapshot, audit, record, supplement, guiderAudit, guiderRecord) => area.ParentId == parentId || area.AreaName == "外地")
            .GroupBy((area, order, snapshot, audit, record, supplement, guiderAudit, guiderRecord) => new { area.AreaName, order.County })
            .Select((area, order, snapshot, audit, record, supplement, guiderAudit, guiderRecord, industry) => new CountyRedPackStatisticsOutDto
            {
                County = area.AreaName,
                JuBaoZongShu = SqlFunc.AggregateSum(SqlFunc.IIF(industry.IndustryType == EIndustryType.Clue, 1, 0)),
                YiBanJieShu = SqlFunc.AggregateSum(SqlFunc.IIF(order.Status >= EOrderStatus.Filed && industry.IndustryType == EIndustryType.Clue, 1, 0)),
                ShiMinShenHeTongGuoShu = SqlFunc.AggregateSum(SqlFunc.IIF(record.PeopleType == EReadPackUserType.Citizen && audit.Status == ERedPackAuditStatus.Agree, 1, 0)),
                ShiMinZongJinE = SqlFunc.AggregateSum(SqlFunc.IIF(record.PeopleType == EReadPackUserType.Citizen && audit.Status == ERedPackAuditStatus.Agree, audit.ApprovedAmount, 0)),
                ShiMinYiLing = SqlFunc.AggregateSum(SqlFunc.IIF(record.PeopleType == EReadPackUserType.Citizen && record.PickupStatus == ERedPackPickupStatus.Received, record.Amount, 0)),
                ShiMinDaiLing = SqlFunc.AggregateSum(SqlFunc.IIF(record.PeopleType == EReadPackUserType.Citizen && record.PickupStatus == ERedPackPickupStatus.Unreceived, record.Amount, 0)),
                ShiMinDaiBuLing = SqlFunc.AggregateSum(SqlFunc.IIF(record.PeopleType == EReadPackUserType.Citizen && supplement.Id != null, supplement.ReplenishAmount, 0)),
                WangGeYuanZongJianShu = SqlFunc.AggregateSum(SqlFunc.IIF(snapshot.IsDeal == true, 1, 0)),
                WangGeYuanShenHeTongGuoShu = SqlFunc.AggregateSum(SqlFunc.IIF(guiderAudit.LevelTwoStatus == ERedPackAuditStatus.Agree, 1, 0)),
                WangGeYuanZongJinE = SqlFunc.AggregateSum(SqlFunc.IIF(guiderAudit.LevelTwoStatus == ERedPackAuditStatus.Agree, guiderAudit.ApprovedAmount, 0)),
                WangGeYuanYiLing = SqlFunc.AggregateSum(SqlFunc.IIF(guiderRecord.PickupStatus == ERedPackPickupStatus.Received, guiderRecord.Amount, 0)),
                WangGeYuanDaiLing = SqlFunc.AggregateSum(SqlFunc.IIF(guiderRecord.PickupStatus == ERedPackPickupStatus.Unreceived, guiderRecord.Amount, 0)),
            });

#if DEBUG
        var sql = query.ToSqlString();
#endif
        return query;
    }

    /// <summary>
    /// 部门考核统计-随手拍
    /// </summary>
    /// <param name="dto"></param>
    /// <returns></returns>
    /// <exception cref="NotImplementedException"></exception>
    public ISugarQueryable<SnapshotDepartmentStatisticsOutDto> GetSnapshotDepartmentStatistics(SnapshotDepartmentStatisticsInDto dto)
    {
        bool IsCenter = _sessionContext.OrgIsCenter;
        var orgLevel = _sessionContext.OrgLevel;
        string orgLevelStr = (_sessionContext.RequiredOrgId.Length + 3).ToString();

        var query = _orderSnapshotRepository.Queryable(includeDeleted: true)
            .LeftJoin<Order>((snapshot, order) => snapshot.Id == order.Id)
            .LeftJoin<OrderSendBackAudit>((snapshot, order, back) => snapshot.Id == back.OrderId && back.State == ESendBackAuditState.End)
            .LeftJoin<OrderVisit>((snapshot, order, back, visit) => snapshot.Id == visit.OrderId && visit.VisitState == EVisitState.Visited)
            .LeftJoin<OrderSecondaryHandling>((snapshot, order, back, visit, second) => snapshot.Id == second.OrderId && second.State == ESecondaryHandlingState.End)
            .Where((snapshot, order) => snapshot.CreationTime >= dto.StartTime && snapshot.CreationTime <= dto.EndTime && order.ActualHandleOrgCode != null)
            .GroupBy((snapshot, order) => new
            {
                OrgCode = order.ActualHandleOrgCode.Substring(SqlFunc.MappingColumn<int>("0"), SqlFunc.MappingColumn<int>("6"))
            })
            .Select((snapshot, order, back, visit, second) => new SnapshotDepartmentStatisticsOutDto()
            {
                OrgCode = order.ActualHandleOrgCode.Substring(SqlFunc.MappingColumn<int>("0"), SqlFunc.MappingColumn<int>("6")),
                OrderCountNum = SqlFunc.AggregateCount(snapshot.Id),
                ReceiveIn4Hour = SqlFunc.AggregateCount(SqlFunc.IIF(order.ActualHandleStepCreateTime != null && order.ActualHandleStepCreateTime.Value.AddHours(-4) <= order.CreationTime, 1, 0)),
                TimeOutField = SqlFunc.AggregateCount(SqlFunc.IIF(order.Status >= EOrderStatus.Filed && order.FiledTime > order.ExpiredTime, 1, 0)),
                TimeOutNoField = SqlFunc.AggregateCount(SqlFunc.IIF(order.Status < EOrderStatus.Filed && DateTime.Now > order.ExpiredTime, 1, 0)),
                OnTimeCount = SqlFunc.AggregateSum(SqlFunc.IIF(order.FiledTime <= order.ExpiredTime, 1, 0)),
                SatisfiedCount = SqlFunc.AggregateSum(SqlFunc.IIF(SqlFunc.JsonListObjectAny(visit.NowEvaluate, "key", "1") || SqlFunc.JsonListObjectAny(visit.NowEvaluate, "key", "2"), 1, 0)),
                NoSatisfiedCount = SqlFunc.AggregateSum(SqlFunc.IIF(SqlFunc.JsonListObjectAny(visit.NowEvaluate, "key", "1") || SqlFunc.JsonListObjectAny(visit.NowEvaluate, "key", "2"), 0, 1)),
                SecondNum = SqlFunc.AggregateCount(second.Id),
                FirstFiledOrderCount = SqlFunc.AggregateCount(SqlFunc.IIF(order.Status >= EOrderStatus.Filed && second.Id == null, 1, 0)),
                SecondSatisfied = SqlFunc.AggregateCount(SqlFunc.IIF(second.State == ESecondaryHandlingState.Handled && (SqlFunc.JsonField(order.OrgProcessingResults, "Key") == "1" || SqlFunc.JsonField(order.OrgProcessingResults, "Key") == "2"), 1, 0)),
            })
            .MergeTable()
            .LeftJoin<SystemOrganize>((it, o) => it.OrgCode == o.Id)
            .Select((it, o) => new SnapshotDepartmentStatisticsOutDto()
            {
                OrgName = o.Name,
                OrgCode = it.OrgCode,
                ReceiveIn4Hour = it.ReceiveIn4Hour,
                TimeOutField = it.TimeOutField,
                TimeOutNoField = it.TimeOutNoField,
                OnTimeCount = it.OnTimeCount,
                SatisfiedCount = it.SatisfiedCount,
                NoSatisfiedCount = it.NoSatisfiedCount,
                SecondNum = it.SecondNum,
                FirstFiledOrderCount = it.FirstFiledOrderCount,
                SecondSatisfied = it.SecondSatisfied
            });

        return query;
    }

    public ISugarQueryable<SnapshotDepartmentAveTimeStatisticsOutDto> GetSnapshotDepartmentAveTimeStatistics(SnapshotDepartmentAveTimeStatisticsInDto dto)
    {
        bool IsCenter = _sessionContext.OrgIsCenter;
        var orgLevel = _sessionContext.OrgLevel;
        string orgLevelStr = (_sessionContext.RequiredOrgId.Length + 3).ToString();

        var query = _orderSnapshotRepository.Queryable(includeDeleted: true)
            .LeftJoin<Order>((snapshot, order) => snapshot.Id == order.Id)
            .LeftJoin<OrderSendBackAudit>((snapshot, order, back) => snapshot.Id == back.OrderId && back.State == ESendBackAuditState.End)
            .LeftJoin<OrderVisit>((snapshot, order, back, visit) => snapshot.Id == visit.OrderId && visit.VisitState == EVisitState.Visited)
            .LeftJoin<OrderSecondaryHandling>((snapshot, order, back, visit, second) => snapshot.Id == second.OrderId && second.State == ESecondaryHandlingState.End)
            .Where((snapshot, order) => snapshot.CreationTime >= dto.StartTime && snapshot.CreationTime <= dto.EndTime && order.ActualHandleOrgCode != null)
            .GroupBy((snapshot, order) => new
            {
                OrgCode = order.ActualHandleOrgCode.Substring(SqlFunc.MappingColumn<int>("0"), SqlFunc.MappingColumn<int>("6"))
            })
            .Select((snapshot, order, back, visit, second) => new SnapshotDepartmentAveTimeStatisticsOutDto()
            {
                OrgCode = order.ActualHandleOrgCode.Substring(SqlFunc.MappingColumn<int>("0"), SqlFunc.MappingColumn<int>("6")),
                OrderCountNum = SqlFunc.AggregateCount(snapshot.Id),
                TotalSeconds = SqlFunc.AggregateSum(order.AllDuration),
            })
            .MergeTable()
            .LeftJoin<SystemOrganize>((it, o) => it.OrgCode == o.Id)
            .Select((it, o) => new SnapshotDepartmentAveTimeStatisticsOutDto()
            {
                OrgName = o.Name,
                OrgCode = it.OrgCode,
                TotalSeconds = it.TotalSeconds,
                OrderCountNum = it.OrderCountNum
            });

        return query;
    }

    public ISugarQueryable<SnapshotDepartmentAveTimeStatisticsDetailsOutDto> GetSnapshotDepartmentAveTimeStatisticsDtails(SnapshotDepartmentAveTimeStatisticsDetailsInDto dto)
    {
        var query = _orderSnapshotRepository.Queryable(includeDeleted: true)
            .LeftJoin<Order>((snapshot, order) => snapshot.Id == order.Id)
            .Where((snapshot, order) => order.CreationTime >= dto.StartTime && order.CreationTime <= dto.EndTime && order.ActualHandleOrgCode.Substring(SqlFunc.MappingColumn<int>("0"), SqlFunc.MappingColumn<int>("6")) == dto.OrgCode)
            .Select((snapshot, order) => new SnapshotDepartmentAveTimeStatisticsDetailsOutDto(), true);
        return query;
    }

    [ExportExcel("检查合规统计", "OrgName")]
    public ISugarQueryable<CompliantStatisticsOutDto> GetCompliantStatistics(CompliantStatisticsInDto dto)
    {
        var query = _orderSnapshotRepository.Queryable(includeDeleted: true)
            .LeftJoin<Order>((snapshot, order) => snapshot.Id == order.Id)
            .LeftJoin<OrderSendBackAudit>((snapshot, order, back) => snapshot.Id == back.OrderId && back.State == ESendBackAuditState.End)
            .LeftJoin<OrderVisit>((snapshot, order, back, visit) => snapshot.Id == visit.OrderId && visit.VisitState == EVisitState.Visited)
            .LeftJoin<OrderSecondaryHandling>((snapshot, order, back, visit, second) => snapshot.Id == second.OrderId && second.State == ESecondaryHandlingState.End)
            .Where((snapshot, order) => snapshot.CreationTime >= dto.StartTime && snapshot.CreationTime <= dto.EndTime && order.ActualHandleOrgCode != null)
            .GroupBy((snapshot, order) => new
            {
                OrgCode = order.ActualHandleOrgCode.Substring(SqlFunc.MappingColumn<int>("0"), SqlFunc.MappingColumn<int>("6"))
            })
            .Select((snapshot, order, back, visit, second) => new CompliantStatisticsOutDto()
            {
                OrgCode = order.ActualHandleOrgCode.Substring(SqlFunc.MappingColumn<int>("0"), SqlFunc.MappingColumn<int>("6")),
                OrderCountNum = SqlFunc.AggregateCount(snapshot.Id),
                First = SqlFunc.AggregateSum(SqlFunc.IIF(snapshot.CompliantType == ECompliantType.First, 1, 0)),
                Second = SqlFunc.AggregateSum(SqlFunc.IIF(snapshot.CompliantType == ECompliantType.Second, 1, 0)),
                Third = SqlFunc.AggregateSum(SqlFunc.IIF(snapshot.CompliantType == ECompliantType.Third, 1, 0)),
                Not = SqlFunc.AggregateSum(SqlFunc.IIF(snapshot.CompliantType == ECompliantType.Not, 1, 0)),
            })
            .MergeTable()
            .LeftJoin<SystemOrganize>((it, o) => it.OrgCode == o.Id)
            .Select((it, o) => new CompliantStatisticsOutDto()
            {
                OrgName = o.Name,
                OrgCode = it.OrgCode,
                OrderCountNum = it.OrderCountNum,
                First = it.First,
                Second = it.Second,
                Third = it.Third,
                Not = it.Not
            });

        return query;
    }

    public ISugarQueryable<CompliantStatisticsDetailsOutDto> GetCompliantStatisticsDetails(CompliantStatisticsDetailsInDto dto)
    {
        dto.FieldName = dto.FieldName.ToLower();
        var query = _orderSnapshotRepository.Queryable(includeDeleted: true)
            .LeftJoin<Order>((snapshot, order) => order.Id == snapshot.Id)
            .Where((snapshot, order) => snapshot.CreationTime >= dto.StartTime && snapshot.CreationTime <= dto.EndTime && order.ActualHandleOrgCode.Substring(SqlFunc.MappingColumn<int>("0"), SqlFunc.MappingColumn<int>("6")) == dto.OrgCode);

        query = dto.FieldName switch
        {
            "ordercountnum" => query,
            "first" => query.Where(snapshot => snapshot.CompliantType == ECompliantType.First),
            "second" => query.Where(snapshot => snapshot.CompliantType == ECompliantType.Second),
            "third" => query.Where(snapshot => snapshot.CompliantType == ECompliantType.Third),
            "not" => query.Where(snapshot => snapshot.CompliantType == ECompliantType.Not),
            _ => throw new UserFriendlyException($"入参: {dto.FieldName} 异常")
        };
        return query.Select((snapshot, order) => new CompliantStatisticsDetailsOutDto
        {
        }, true);
    }

    public List<Dictionary<string, object>> GetReTransactStatistics(ReTransactStatisticsInDto dto)
    {
        var rawData = _orderSnapshotRepository.Queryable(includeDeleted: true)
            .LeftJoin<Order>((snapshot, order) => order.Id == snapshot.Id)
            .Where((snapshot, order) => snapshot.SpecialReasonId != null)
            .GroupBy((snapshot, order) => new
            {
                OrgCode = order.ActualHandleOrgCode.Substring(SqlFunc.MappingColumn<int>("0"), SqlFunc.MappingColumn<int>("6")),
                SpecialReasonId = snapshot.SpecialReasonId,
                snapshot.SpecialReasonName
            })
            .Select((snapshot, order) => new 
            { 
                OrgCode = order.ActualHandleOrgCode.Substring(SqlFunc.MappingColumn<int>("0"), SqlFunc.MappingColumn<int>("6")),
                OrderCountNum = SqlFunc.AggregateCount(snapshot.Id),
                SpecialReasonId = snapshot.SpecialReasonId,
                SpecialReasonName = snapshot.SpecialReasonName
            }, true) 
            .MergeTable()
            .LeftJoin<SystemOrganize>((it, o) => it.OrgCode == o.Id)
            .Select((it, o) => new
            {
                OrgName = o.Name,
                OrgCode = it.OrgCode,
                OrderCountNum = it.OrderCountNum,
                SpecialReasonId = it.SpecialReasonId,
                SpecialReasonName = it.SpecialReasonName
            }).ToList();

        var pivotResult = new List<Dictionary<string, object>>();

        var orgGroups = rawData.GroupBy(x => new { x.OrgCode, x.OrgName });

        var specialReasons = rawData
            .Select(x => x.SpecialReasonId)
            .Distinct()
            .ToList();

        foreach (var orgGroup in orgGroups)
        {
            var row = new Dictionary<string, object>
            {
                ["OrgName"] = orgGroup.Key.OrgName,
                ["OrgCode"] = orgGroup.Key.OrgCode
            };

            foreach (var reason in specialReasons)
            {
                row[reason] = 0; 
            }

            foreach (var item in orgGroup)
            {
                if (item.SpecialReasonId != null)
                {
                    row[item.SpecialReasonId] = item.OrderCountNum;
                }
            }

            pivotResult.Add(row);
        }
        pivotResult.AddSumLine("OrgName");
        return pivotResult;
    }

    public ISugarQueryable<ReTransactStatisticsDetailsOutDto> GetReTransactStatisticsDetail(ReTransactStatisticsDetailsInDto dto)
    {
        var query = _orderSnapshotRepository.Queryable(includeDeleted: true)
            .LeftJoin<Order>((snapshot, order) => order.Id == snapshot.Id)
            .LeftJoin<OrderSpecial>((snapshot, order, special) => special.OrderId == order.Id)
            .LeftJoin<SystemOrganize>((snapshot, order, special, org) => org.Id == special.OrgId.Substring(SqlFunc.MappingColumn<int>("0"), SqlFunc.MappingColumn<int>("6")))
            .Where((snapshot, order, special) => order.CreationTime >= dto.StartTime && order.CreationTime <= dto.EndTime && order.ActualHandleOrgCode.Substring(SqlFunc.MappingColumn<int>("0"), SqlFunc.MappingColumn<int>("6")) == dto.OrgCode && special.Id != null && snapshot.SpecialReasonId != null && special.SpecialType == ESpecialType.ReTransact)
            .WhereIF(dto.FieldName.NotNullOrEmpty(), (snapshot, order) => dto.FieldName == snapshot.SpecialReasonId)
            .OrderByDescending((snapshot, order) => order.CreationTime)
            .Select((snapshot, order, special, org) => new ReTransactStatisticsDetailsOutDto 
            {
                ReTransactOrgName = special.OrgName, // 被重办部门
                ReTransactOneOrgName = org.Name, // 被重办一级部门
                ReTransactTime = special.CreationTime, // 重办时间
                ReTransactHandlerName = special.CreatorName, // 重办操作人
                ReTransactContent = special.Opinion // 重办理由
            }, true);
#if DEBUG
        var sql = query.ToSqlString();
#endif
        return query;
    }
}