AiDOPWarehouse/server/Plugins/Admin.NET.Plugin.AiDOP/Service/S8/S8KpiSnapshotService.cs

using Admin.NET.Plugin.AiDOP.SmartOps;

namespace Admin.NET.Plugin.AiDOP.Service.S8;

/// <summary>
/// S8 独立异常链汇总：把 ado_s8_exception 写成九宫格可读的 L1 日值。
/// 不把未闭环异常的处理时效伪装成 0。
/// </summary>
public class S8KpiSnapshotService : ITransient
{
    private const string ModuleCode = "S8";
    private const string ValueTable = "ado_s9_kpi_value_l1_day";

    private readonly ISqlSugarClient _db;
    private readonly IKpiTargetResolver _kpiTargetResolver;

    public S8KpiSnapshotService(ISqlSugarClient db, IKpiTargetResolver kpiTargetResolver)
    {
        _db = db;
        _kpiTargetResolver = kpiTargetResolver;
    }

    public async Task<int> RefreshAsync(long tenantId, long factoryId, DateTime? bizDate = null, CancellationToken cancellationToken = default)
    {
        cancellationToken.ThrowIfCancellationRequested();
        if (tenantId <= 0 || factoryId <= 0 || factoryId == tenantId)
            return 0;

        var day = (bizDate ?? DateTime.Now).Date;
        var now = DateTime.Now;
        var count = await _db.Ado.GetIntAsync(
            """
            SELECT COUNT(1)
            FROM ado_s8_exception
            WHERE tenant_id=@TenantId
              AND IFNULL(is_deleted,0)=0
              AND IFNULL(exception_code,'')<>''
              AND (factory_id IS NULL OR factory_id=0 OR factory_id=@FactoryId
                   OR (@FactoryId IN (0,1) AND factory_id<>@TenantId))
            """,
            new SugarParameter("@TenantId", tenantId),
            new SugarParameter("@FactoryId", factoryId));

        var avgHours = await _db.Ado.GetDecimalAsync(
            """
            SELECT ROUND(AVG(TIMESTAMPDIFF(MINUTE, created_at, closed_at) / 60), 4)
            FROM ado_s8_exception
            WHERE tenant_id=@TenantId
              AND IFNULL(is_deleted,0)=0
              AND UPPER(IFNULL(status,''))='CLOSED'
              AND closed_at IS NOT NULL
              AND closed_at >= created_at
              AND (factory_id IS NULL OR factory_id=0 OR factory_id=@FactoryId
                   OR (@FactoryId IN (0,1) AND factory_id<>@TenantId))
            """,
            new SugarParameter("@TenantId", tenantId),
            new SugarParameter("@FactoryId", factoryId));

        var affected = await UpsertAsync("S8_L1_001", tenantId, factoryId, day, now, count);
        affected += await UpsertAsync(
            "S8_L1_002",
            tenantId,
            factoryId,
            day,
            now,
            avgHours > 0 ? avgHours : null);
        return affected;
    }

    private async Task<int> UpsertAsync(string metricCode, long tenantId, long factoryId, DateTime bizDate, DateTime now, decimal? metricValue)
    {
        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, is_active=1
                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 = await _db.Ado.GetLongAsync($"SELECT COALESCE(MAX(id), 0) + 1 FROM {ValueTable}");
        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));
    }
}