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

using Microsoft.Extensions.Logging;

namespace Admin.NET.Plugin.AiDOP.SmartOps;

/// <summary>
/// 四个运营 L1 写入 ado_s9_kpi_value_l1_day。
/// 交付周期 = 评审 + 排程 + 物料计划 + 物料上线 + 制造 + 发货，六段同日都有值才写。
/// 交付满足率 = 计划发货日当天、已发数量达到订单数量的订单行 / 有订单数量的订单行。
/// 生产效率 = 产出工时 / (产出工时 + 停机)，休息不计入投入。无法区分停机时不写。按产线状态的工厂过滤。
/// 全库存周转 = 成品周转 + 在制周转 + 物料周转，三段同日都有值才写。
/// </summary>
public class S9CompositeKpiWriter : ITransient
{
    private const string ValueTable = "ado_s9_kpi_value_l1_day";
    private const string ModuleCode = "S9";
    private readonly ISqlSugarClient _db;
    private readonly IKpiTargetResolver _kpiTargetResolver;
    private readonly ILogger<S9CompositeKpiWriter> _logger;

    public S9CompositeKpiWriter(
        ISqlSugarClient db,
        IKpiTargetResolver kpiTargetResolver,
        ILogger<S9CompositeKpiWriter> logger)
    {
        _db = db;
        _kpiTargetResolver = kpiTargetResolver;
        _logger = logger;
    }

    public async Task<int> WriteRecentAsync(
        long tenantId, long factoryId, DateTime anchorDate, CancellationToken cancellationToken = default)
    {
        if (tenantId <= 0 || factoryId <= 0) return 0;
        var rows = 0;
        for (var offset = 13; offset >= 0; offset--)
        {
            cancellationToken.ThrowIfCancellationRequested();
            rows += await WriteDayAsync(tenantId, factoryId, anchorDate.Date.AddDays(-offset), cancellationToken);
        }
        return rows;
    }

    private async Task<int> WriteDayAsync(long tenantId, long factoryId, DateTime bizDate, CancellationToken cancellationToken)
    {
        try
        {
            var now = DateTime.Now;
            var rows = 0;
            var cycle = await SumSegmentsAsync(tenantId, factoryId, bizDate,
                ["S1_L1_001", "S2_L1_001", "S3_L1_001", "S5_L1_001", "S6_L1_001", "S7_L1_001"], 6);
            if (cycle.HasValue)
                rows += await UpsertAsync("S9_L1_002", bizDate, cycle, now, tenantId, factoryId);

            var fulfillment = await FulfillmentAsync(tenantId, factoryId, bizDate);
            if (fulfillment.HasValue)
                rows += await UpsertAsync("S9_L1_003", bizDate, fulfillment, now, tenantId, factoryId);

            var efficiency = await EfficiencyAsync(tenantId, factoryId, bizDate);
            if (efficiency.HasValue)
                rows += await UpsertAsync("S9_L1_004", bizDate, efficiency, now, tenantId, factoryId);

            var turnover = await SumSegmentsAsync(tenantId, factoryId, bizDate,
                ["S1_L1_004", "S2_L1_004", "S3_L1_004"], 3);
            if (turnover.HasValue)
                rows += await UpsertAsync("S9_L1_005", bizDate, turnover, now, tenantId, factoryId);

            cancellationToken.ThrowIfCancellationRequested();
            return rows;
        }
        catch (Exception ex) when (ex is not OperationCanceledException)
        {
            _logger.LogWarning(ex, "S9 复合指标 {Date} 写入跳过，不打断模块重建", bizDate.ToString("yyyy-MM-dd"));
            return 0;
        }
    }

    private async Task<decimal?> SumSegmentsAsync(
        long tenantId, long factoryId, DateTime bizDate, string[] codes, int required)
    {
        var row = await _db.Ado.SqlQueryAsync<S9SegmentSumRow>(
            """
            SELECT COUNT(*) AS N, SUM(v) AS Total
            FROM (
                SELECT metric_code, MAX(metric_value) AS v
                FROM ado_s9_kpi_value_l1_day
                WHERE tenant_id=@TenantId AND factory_id=@FactoryId AND biz_date=@BizDate
                  AND is_deleted=0 AND metric_value IS NOT NULL
                  AND FIND_IN_SET(metric_code, @Codes)
                  AND module_code = SUBSTRING_INDEX(metric_code, '_', 1)
                GROUP BY metric_code
            ) s
            """,
            new { TenantId = tenantId, FactoryId = factoryId, BizDate = bizDate, Codes = string.Join(",", codes) });
        var hit = row.FirstOrDefault();
        if (hit == null || hit.N < required || hit.Total == null) return null;
        return Math.Round(hit.Total.Value, 4);
    }

    private async Task<decimal?> FulfillmentAsync(long tenantId, long factoryId, DateTime bizDate)
    {
        var row = await _db.Ado.SqlQueryAsync<S9RatioRow>(
            """
            SELECT
                SUM(CASE WHEN IFNULL(order_qty,0) > 0 AND IFNULL(delivered_qty,0) >= order_qty THEN 1 ELSE 0 END) AS Numer,
                SUM(CASE WHEN IFNULL(order_qty,0) > 0 THEN 1 ELSE 0 END) AS Denom
            FROM mdp_std_so
            WHERE tenant_id=@TenantId
              AND COALESCE(NULLIF(factory_id,0),1)=@FactoryId
              AND deleted_flag=0
              AND plan_delivery_date >= @DayStart AND plan_delivery_date < @DayEnd
            """,
            new
            {
                TenantId = tenantId,
                FactoryId = factoryId,
                DayStart = bizDate.Date,
                DayEnd = bizDate.Date.AddDays(1)
            });
        var hit = row.FirstOrDefault();
        if (hit?.Denom is not > 0 || hit.Numer == null) return null;
        return Math.Round(hit.Numer.Value / hit.Denom.Value * 100m, 4);
    }

    /// <summary>
    /// 产线状态贴源里的 ProdTime / RestTime / ProdDownTime。
    /// 休息或停机列缺失时无法区分有效工时，不写 100%。
    /// LineRunRestDet 的 TransType 取值未登记，不拿它猜开工或休息。
    /// </summary>
    private async Task<decimal?> EfficiencyAsync(long tenantId, long factoryId, DateTime bizDate)
    {
        try
        {
            var row = await _db.Ado.SqlQueryAsync<S9RatioRow>(
                """
                SELECT
                    SUM(CASE WHEN JSON_EXTRACT(raw_data,'$.ProdTime') IS NULL THEN NULL
                             ELSE CAST(JSON_UNQUOTE(JSON_EXTRACT(raw_data,'$.ProdTime')) AS DECIMAL(18,4)) END) AS Numer,
                    SUM(CASE WHEN JSON_EXTRACT(raw_data,'$.ProdDownTime') IS NULL THEN NULL
                             ELSE IFNULL(CAST(JSON_UNQUOTE(JSON_EXTRACT(raw_data,'$.ProdTime')) AS DECIMAL(18,4)),0)
                                + IFNULL(CAST(JSON_UNQUOTE(JSON_EXTRACT(raw_data,'$.ProdDownTime')) AS DECIMAL(18,4)),0)
                        END) AS Denom
                FROM mdp_stg_line_status
                WHERE tenant_id=@TenantId
                  AND factory_id=@FactoryId
                  AND DATE(COALESCE(
                        STR_TO_DATE(LEFT(JSON_UNQUOTE(JSON_EXTRACT(raw_data,'$.ProdDate')),10),'%Y-%m-%d'),
                        sync_time)) = @BizDate
                """,
                new { TenantId = tenantId, FactoryId = factoryId.ToString(), BizDate = bizDate.Date });
            var hit = row.FirstOrDefault();
            if (hit?.Numer == null || hit.Denom is not > 0) return null;
            return Math.Round(hit.Numer.Value / hit.Denom.Value * 100m, 4);
        }
        catch (Exception ex) when (ex is not OperationCanceledException)
        {
            _logger.LogWarning(ex, "S9_L1_004 产线运行贴源不可用，生产效率保持空");
            return null;
        }
    }

    private async Task<int> UpsertAsync(
        string metricCode, DateTime bizDate, decimal? metricValue, DateTime now, long tenantId, long factoryId)
    {
        bizDate = bizDate.Date;
        var snap = await _kpiTargetResolver.ResolveAsync(tenantId, factoryId, metricCode, ModuleCode, bizDate);
        var existingId = await _db.Ado.GetLongAsync(
            $"SELECT IFNULL((SELECT id FROM {ValueTable} WHERE tenant_id=@TenantId AND factory_id=@FactoryId " +
            "AND module_code=@ModuleCode AND metric_code=@MetricCode AND biz_date=@BizDate AND is_deleted=0 " +
            "ORDER BY id LIMIT 1), 0)",
            new List<SugarParameter>
            {
                new("@TenantId", tenantId),
                new("@FactoryId", factoryId),
                new("@ModuleCode", ModuleCode),
                new("@MetricCode", metricCode),
                new("@BizDate", bizDate)
            });

        if (existingId > 0)
        {
            return await _db.Ado.ExecuteCommandAsync(
                $"UPDATE {ValueTable} SET metric_value=@MetricValue, target_value=@TargetValue, " +
                "target_config_id=@TargetConfigId, target_source=@TargetSource, target_resolved_at=@TargetResolvedAt, " +
                "calc_time=@Now, update_time=@Now, is_deleted=0 WHERE id=@Id",
                new SugarParameter("@MetricValue", metricValue),
                new SugarParameter("@TargetValue", KpiTargetSnapshotSql.ValueOrDbNull(snap)),
                new SugarParameter("@TargetConfigId", KpiTargetSnapshotSql.ConfigIdOrDbNull(snap)),
                new SugarParameter("@TargetSource", KpiTargetSnapshotSql.SourceOrDbNull(snap)),
                new SugarParameter("@TargetResolvedAt", snap.ResolvedAt),
                new SugarParameter("@Now", now),
                new SugarParameter("@Id", existingId));
        }

        var nextId = Yitter.IdGenerator.YitIdHelper.NextId();
        return await _db.Ado.ExecuteCommandAsync($@"
INSERT INTO {ValueTable}
  (id, tenant_id, org_id, company_id, factory_id, status, biz_date,
   create_time, update_time, is_deleted, is_active,
   module_code, metric_code, metric_value, target_value, calc_time,
   target_config_id, target_source, target_resolved_at)
VALUES
  (@Id, @TenantId, NULL, NULL, @FactoryId, NULL, @BizDate,
   @Now, @Now, 0, 1,
   @ModuleCode, @MetricCode, @MetricValue, @TargetValue, @Now,
   @TargetConfigId, @TargetSource, @TargetResolvedAt)",
            new SugarParameter("@Id", nextId),
            new SugarParameter("@TenantId", tenantId),
            new SugarParameter("@FactoryId", factoryId),
            new SugarParameter("@BizDate", bizDate),
            new SugarParameter("@Now", now),
            new SugarParameter("@ModuleCode", ModuleCode),
            new SugarParameter("@MetricCode", metricCode),
            new SugarParameter("@MetricValue", metricValue),
            new SugarParameter("@TargetValue", KpiTargetSnapshotSql.ValueOrDbNull(snap)),
            new SugarParameter("@TargetConfigId", KpiTargetSnapshotSql.ConfigIdOrDbNull(snap)),
            new SugarParameter("@TargetSource", KpiTargetSnapshotSql.SourceOrDbNull(snap)),
            new SugarParameter("@TargetResolvedAt", snap.ResolvedAt));
    }
}

internal sealed class S9SegmentSumRow
{
    public int N { get; set; }
    public decimal? Total { get; set; }
}

internal sealed class S9RatioRow
{
    public decimal? Numer { get; set; }
    public decimal? Denom { get; set; }
}