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

using FluentValidation.Results;
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 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.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;

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

    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(d => new SnapshotProcessingStatisticsDetailsOutDto
        {
            OrderScreenStatus = SqlFunc.Subqueryable<OrderScreen>().Where(q => q.OrderId == d.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;
    }
}