AiDOPWarehouse/server/Plugins/Admin.NET.Plugin.AiDOP/SmartOps/SmartOpsKpiAtomicQueryService.cs

using Admin.NET.Core;
using Admin.NET.Plugin.AiDOP.Entity;
using Admin.NET.Plugin.AiDOP.Infrastructure;
using SqlSugar;

namespace Admin.NET.Plugin.AiDOP.SmartOps;

/// <summary>
/// 从 KPI 原子聚合层按筛选条件动态聚合并计算最终 KPI。
/// </summary>
public class SmartOpsKpiAtomicQueryService : ITransient
{
    private readonly ISqlSugarClient _db;

    public SmartOpsKpiAtomicQueryService(ISqlSugarClient db)
    {
        _db = db;
    }

    public async Task<FilteredKpiQueryResult> QueryAsync(
        long tenantId,
        long factoryId,
        string moduleCode,
        SmartOpsDashboardFilter filter,
        IReadOnlyList<AdoSmartOpsKpiMaster> kpis,
        CancellationToken cancellationToken = default)
    {
        AidopTenantMigration.EnsureKpiAtomicTable(_db);

        var result = new FilteredKpiQueryResult
        {
            FilterContext = filter.ToContextDictionary()
                .Where(x => !string.IsNullOrWhiteSpace(x.Value))
                .ToDictionary(x => x.Key, x => x.Value, StringComparer.OrdinalIgnoreCase)
        };

        if (filter.IsEmpty || kpis.Count == 0)
        {
            result.Scope = "module_summary";
            return result;
        }

        var filteredCount = 0;
        var summaryCount = 0;
        var partialCount = 0;

        foreach (var kpi in kpis)
        {
            cancellationToken.ThrowIfCancellationRequested();
            var metricCode = kpi.MetricCode;
            var rule = SmartOpsKpiAggregateRuleRegistry.TryGet(metricCode);
            var unsupported = SmartOpsKpiAggregateRuleRegistry.GetUnsupportedFilters(metricCode, filter).ToArray();
            var filterMode = SmartOpsKpiAggregateRuleRegistry.ResolveFilterMode(metricCode, filter);
            result.MetricFilterModes[metricCode] = filterMode;
            if (unsupported.Length > 0)
                result.UnsupportedFiltersByMetric[metricCode] = unsupported;

            if (rule == null || rule.FilterMode == SmartOpsKpiAggregateRuleRegistry.FilterSummaryOnly ||
                !SmartOpsKpiAggregateRuleRegistry.SupportsFilter(metricCode, filter))
            {
                if (filterMode == SmartOpsKpiAggregateRuleRegistry.FilterPartial)
                    partialCount++;
                else
                    summaryCount++;
                continue;
            }

            var valueSql = BuildAggregateSql(rule.AggregationType, groupByDate: false);
            var whereSql = BuildWhereSql(filter);
            var factorySql = BuildFactoryWhereSql(filter);
            var sql = $"""
                SELECT {valueSql} AS MetricValue
                FROM ado_smart_ops_kpi_atomic_day
                WHERE tenant_id = @tenantId
                  {factorySql}
                  AND domain_code = @domainCode
                  AND metric_code = @metricCode
                  AND is_deleted = 0
                  {whereSql}
                """;

            var parameters = BuildParameters(tenantId, factoryId, rule.DomainCode, metricCode, filter);
            var current = await _db.Ado.SqlQuerySingleAsync<AtomicAggregateRow>(sql, parameters);
            if (current?.MetricValue == null)
            {
                summaryCount++;
                result.MetricFilterModes[metricCode] = SmartOpsKpiAggregateRuleRegistry.FilterSummaryOnly;
                continue;
            }

            var trendSql = $"""
                SELECT stat_date AS StatDate, {BuildAggregateSql(rule.AggregationType, groupByDate: true)} AS MetricValue
                FROM ado_smart_ops_kpi_atomic_day
                WHERE tenant_id = @tenantId
                  {factorySql}
                  AND domain_code = @domainCode
                  AND metric_code = @metricCode
                  AND is_deleted = 0
                  {whereSql}
                GROUP BY stat_date
                ORDER BY stat_date
                """;
            var trendRows = await _db.Ado.SqlQueryAsync<AtomicTrendRow>(trendSql, parameters);

            result.Values[metricCode] = new SmartOpsKpiMetricValue
            {
                Level = kpi.MetricLevel,
                MetricValue = current.MetricValue,
                Trend = trendRows
                    .Select(x => new SmartOpsKpiTrendPoint(x.StatDate.ToString("yyyy-MM-dd"), x.MetricValue))
                    .ToList()
            };
            filteredCount++;
        }

        result.Scope = filteredCount switch
        {
            0 when summaryCount > 0 && partialCount == 0 => "summary_only_fallback",
            0 => "summary_only_fallback",
            var n when n == kpis.Count => "filtered_atomic",
            var n when n > 0 => "partial_filtered",
            _ => "summary_only_fallback"
        };

        return result;
    }

    private static string BuildAggregateSql(string aggregationType, bool groupByDate)
    {
        return aggregationType switch
        {
            SmartOpsKpiAggregateRuleRegistry.AggSum => "ROUND(SUM(sum_value), 4)",
            SmartOpsKpiAggregateRuleRegistry.AggRatio =>
                "ROUND(100 * SUM(numerator_value) / NULLIF(SUM(denominator_value), 0), 4)",
            SmartOpsKpiAggregateRuleRegistry.AggMin => "ROUND(MIN(min_value), 4)",
            SmartOpsKpiAggregateRuleRegistry.AggMax => "ROUND(MAX(max_value), 4)",
            _ => "ROUND(SUM(sum_value) / NULLIF(SUM(sample_count), 0), 4)"
        };
    }

    private static string BuildFactoryWhereSql(SmartOpsDashboardFilter filter)
    {
        // UAT/多工厂租户下 atomic 行的 factory_id 常与汇总层默认 factoryId=1 不一致；
        // 有业务维度筛选时按 tenant 聚合，避免筛选命中 0 行后回退模块汇总。
        return HasBusinessDimensionFilter(filter) ? string.Empty : "AND factory_id = @factoryId";
    }

    private static bool HasBusinessDimensionFilter(SmartOpsDashboardFilter filter) =>
        !string.IsNullOrWhiteSpace(filter.Customer) ||
        !string.IsNullOrWhiteSpace(filter.Product) ||
        !string.IsNullOrWhiteSpace(filter.OrderNo) ||
        !string.IsNullOrWhiteSpace(filter.ProductionLine) ||
        !string.IsNullOrWhiteSpace(filter.Supplier) ||
        !string.IsNullOrWhiteSpace(filter.Material) ||
        !string.IsNullOrWhiteSpace(filter.PoNo) ||
        !string.IsNullOrWhiteSpace(filter.Warehouse) ||
        !string.IsNullOrWhiteSpace(filter.WorkOrder) ||
        !string.IsNullOrWhiteSpace(filter.Equipment) ||
        !string.IsNullOrWhiteSpace(filter.OutboundNo);

    private static string BuildWhereSql(SmartOpsDashboardFilter filter)
    {
        var clauses = new List<string>();
        if (filter.DateStart.HasValue) clauses.Add("AND stat_date >= @dateStart");
        if (filter.DateEnd.HasValue) clauses.Add("AND stat_date <= @dateEnd");
        if (!string.IsNullOrWhiteSpace(filter.Customer)) clauses.Add("AND customer_code = @customer");
        if (!string.IsNullOrWhiteSpace(filter.Product)) clauses.Add("AND product_code = @product");
        if (!string.IsNullOrWhiteSpace(filter.OrderNo)) clauses.Add("AND order_no = @orderNo");
        if (!string.IsNullOrWhiteSpace(filter.ProductionLine)) clauses.Add("AND production_line = @productionLine");
        if (!string.IsNullOrWhiteSpace(filter.Supplier)) clauses.Add("AND supplier_code = @supplier");
        if (!string.IsNullOrWhiteSpace(filter.Material)) clauses.Add("AND material_code = @material");
        if (!string.IsNullOrWhiteSpace(filter.PoNo)) clauses.Add("AND po_no = @poNo");
        if (!string.IsNullOrWhiteSpace(filter.Warehouse)) clauses.Add("AND warehouse_code = @warehouse");
        if (!string.IsNullOrWhiteSpace(filter.WorkOrder)) clauses.Add("AND work_order_no = @workOrder");
        if (!string.IsNullOrWhiteSpace(filter.Equipment)) clauses.Add("AND equipment_code = @equipment");
        if (!string.IsNullOrWhiteSpace(filter.OutboundNo)) clauses.Add("AND outbound_no = @outboundNo");
        return string.Join(' ', clauses);
    }

    private static object BuildParameters(
        long tenantId,
        long factoryId,
        string domainCode,
        string metricCode,
        SmartOpsDashboardFilter filter)
    {
        return new
        {
            tenantId,
            factoryId,
            domainCode,
            metricCode,
            dateStart = filter.DateStart?.Date,
            dateEnd = filter.DateEnd?.Date,
            customer = filter.Customer?.Trim(),
            product = filter.Product?.Trim(),
            orderNo = filter.OrderNo?.Trim(),
            productionLine = filter.ProductionLine?.Trim(),
            supplier = filter.Supplier?.Trim(),
            material = filter.Material?.Trim(),
            poNo = filter.PoNo?.Trim(),
            warehouse = filter.Warehouse?.Trim(),
            workOrder = filter.WorkOrder?.Trim(),
            equipment = filter.Equipment?.Trim(),
            outboundNo = filter.OutboundNo?.Trim()
        };
    }
}