AiDOPWarehouse/server/Plugins/Admin.NET.Plugin.AiDOP/Manufacturing/S6MdpSyncTransformService.cs

using System.Text.Json;

namespace Admin.NET.Plugin.AiDOP.Manufacturing;

/// <summary>
/// S6 生产执行 — T8 KPI 数据底座与刷新转换服务。
/// 路径 A：方老师 v5.4 KPI 字段对照表 J 列 SQL 原逻辑直发 T8 SQL Server（ConfigId=t8_v5）。
/// 包含 KPI：S6_L1_001 工单制造满足率 / S6_L1_002 工单制造人效。
/// </summary>
public class S6MdpSyncTransformService : ITransient
{
    private readonly ISqlSugarClient _db;
    private const string JobCode = "S6_MDP_SYNC_TRANSFORM";
    private const string JobName = "S6 生产执行 MDP 同步与转换";
    private const string T8ConfigId = "t8_v5";
    private const string ModuleCode = "S6";

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

    public async Task<S6MdpSyncTransformResult> RunFullAsync(
        CancellationToken cancellationToken = default,
        string triggerType = "AUTO",
        S6MdpRefreshOption? option = null)
    {
        cancellationToken.ThrowIfCancellationRequested();
        option ??= S6MdpRefreshOption.Default();

        var now = DateTime.Now;
        var batchId = $"S6_MDP_FULL_{now:yyyyMMddHHmmss}";
        var normalizedTrigger = NormalizeTriggerType(triggerType);
        var runLogId = await InsertTransformRunLogAsync(batchId, now, normalizedTrigger);

        var result = new S6MdpSyncTransformResult
        {
            BatchId = batchId,
            RunLogId = runLogId,
            TriggerType = normalizedTrigger,
            SourceZtid = option.SourceZtid,
            BizDate = option.BizDate,
            BizMonth = option.BizMonth,
            MonthlyPeriodStart = option.MonthlyPeriodStart,
            MonthlyPeriodEnd = option.MonthlyPeriodEnd
        };

        try
        {
            result.StageRows = 0;
            result.StandardRows = 0;

            var sub22 = await BuildS6L1001WorkOrderMfgFulfillmentAsync(batchId, now, option, cancellationToken);
            result.MergeSub("S6_L1_001", sub22);

            var sub23 = await BuildS6L1002WorkOrderMfgEfficiencyAsync(batchId, now, option, cancellationToken);
            result.MergeSub("S6_L1_002", sub23);

            await MarkTransformRunSuccessAsync(runLogId, now, result);
            return result;
        }
        catch (Exception ex)
        {
            await MarkTransformRunFailedAsync(runLogId, now, ex.Message);
            throw;
        }
    }

    // ─────────────────────────────────────────────────────────────────────────

    /// <summary>S6_L1_001 工单制造满足率 = 计划完工时间内累计报工 / 工单计划生产数量。</summary>
    private async Task<KpiBuildSubResult> BuildS6L1001WorkOrderMfgFulfillmentAsync(
        string batchId, DateTime now, S6MdpRefreshOption option, CancellationToken ct)
    {
        var sub = new KpiBuildSubResult();

        const string sql = @"
select a.noid as noid, b.rwnoid as rwnoid, b.code as code, b.sl as sl, sum(d.slzx) as slzx
 from kc_dd_head a with(nolock)
 left join kc_dd_list b with(nolock) on a.Id=b.idid
 left join (
   select b.lynoid as lynoid, b.code as code,
          convert(varchar(10), a.shtime, 23) as shtime, b.slzx as slzx
    from kc_tz_head a with(nolock)
    inner join kc_tz_list b with(nolock) on a.Id=b.idid
    where a.ztid=@ztid and a.lbs='生产入库' and a.hzyn=0 and a.zfyn=0 and a.shyn=1
 ) d on b.rwnoid=d.lynoid and b.code=d.code
 where a.ztid=@ztid and a.lbs='生产任务' and a.zf=0 and a.shyn=1 and d.shtime<=b.jhdate
 group by a.noid, b.rwnoid, b.code, b.sl";

        var rows = await QueryT8Async<S6MfgFulfillmentRow>(sql, new[] { new SugarParameter("@ztid", option.SourceZtid) });
        sub.T8Rows = rows.Count;

        var dwdAffected = 0;
        var rateList = new List<decimal>();
        foreach (var r in rows)
        {
            ct.ThrowIfCancellationRequested();
            if (string.IsNullOrEmpty(r.noid)) continue;
            decimal? rate = (r.sl.HasValue && r.sl.Value > 0m && r.slzx.HasValue)
                ? Math.Round(r.slzx.Value / r.sl.Value, 4)
                : null;
            if (rate.HasValue) rateList.Add(rate.Value);

            dwdAffected += await _db.Ado.ExecuteCommandAsync(@"
INSERT INTO dwd_t8_work_order_mfg_fulfillment
  (tenant_id, factory_id, biz_date, source_ztid, order_no, task_no, item_code,
   plan_qty, done_qty_in_window, fulfillment_rate, batch_id, create_time)
VALUES
  (0, 1, @bizDate, @ztid, @orderNo, @taskNo, @itemCode,
   @planQty, @doneQty, @rate, @batchId, @now)
ON DUPLICATE KEY UPDATE
  plan_qty=VALUES(plan_qty), done_qty_in_window=VALUES(done_qty_in_window),
  fulfillment_rate=VALUES(fulfillment_rate),
  batch_id=VALUES(batch_id), update_time=@now",
                new SugarParameter("@bizDate", option.BizDate),
                new SugarParameter("@ztid", option.SourceZtid),
                new SugarParameter("@orderNo", r.noid),
                new SugarParameter("@taskNo", r.rwnoid ?? ""),
                new SugarParameter("@itemCode", r.code ?? ""),
                new SugarParameter("@planQty", r.sl),
                new SugarParameter("@doneQty", r.slzx),
                new SugarParameter("@rate", rate),
                new SugarParameter("@batchId", batchId),
                new SugarParameter("@now", now));
        }
        sub.DwdRows = dwdAffected;

        decimal? metricValue = rateList.Count > 0
            ? Math.Round(rateList.Average() * 100m, 4) // 百分号
            : null;
        sub.KpiRows = await UpsertKpiValueAsync("S6_L1_001", option.BizDate, metricValue, now);
        sub.DenominatorStatus = rateList.Count > 0 ? "OK" : "NO_VALID_WORK_ORDER";
        return sub;
    }

    /// <summary>S6_L1_002 工单制造人效 = 完成制造工单数(lbs=生产入库 AND (slzx>=sl OR gdyn=1)) / count(gw=生产)。</summary>
    private async Task<KpiBuildSubResult> BuildS6L1002WorkOrderMfgEfficiencyAsync(
        string batchId, DateTime now, S6MdpRefreshOption option, CancellationToken ct)
    {
        var sub = new KpiBuildSubResult();

        const string sqlNumer = @"
select count(*) as ddnum
 from kc_tz_head a with(nolock)
 inner join kc_tz_list b with(nolock) on a.Id=b.idid
 where a.ztid=@ztid and a.lbs='生产入库' and a.hzyn=0 and a.zfyn=0 and a.shyn=1
  and a.date0 between @startDate and @endDate
  and b.slzx>0 and (b.slzx>=b.sl or b.gdyn=1)";
        const string sqlDenom = @"
select count(*) as penum
 from sys_pelist with(nolock)
 where ztid=@ztid and zzzt='在职' and gw='生产'";

        var pNumer = new[]
        {
            new SugarParameter("@ztid", option.SourceZtid),
            new SugarParameter("@startDate", option.MonthlyPeriodStart),
            new SugarParameter("@endDate", option.MonthlyPeriodEnd)
        };
        var pDenom = new[] { new SugarParameter("@ztid", option.SourceZtid) };

        var numerRows = await QueryT8Async<S6CountRow>(sqlNumer, pNumer);
        var denomRows = await QueryT8Async<S6PeNumRow>(sqlDenom, pDenom);
        sub.T8Rows = numerRows.Count + denomRows.Count;

        int? doneCount = numerRows.FirstOrDefault()?.ddnum;
        int? headcount = denomRows.FirstOrDefault()?.penum;

        decimal? efficiency = null;
        string denomStatus;
        if (!headcount.HasValue || headcount.Value <= 0)
            denomStatus = "NO_HEADCOUNT";
        else if (!doneCount.HasValue)
            denomStatus = "NO_NUMERATOR";
        else
        {
            efficiency = Math.Round((decimal)doneCount.Value / headcount.Value, 4);
            denomStatus = "OK";
        }
        sub.DenominatorStatus = denomStatus;

        var dwdAffected = await _db.Ado.ExecuteCommandAsync(@"
INSERT INTO dwd_t8_work_order_mfg_efficiency
  (tenant_id, factory_id, biz_month, source_ztid, period_start, period_end,
   done_count, production_headcount, efficiency, denominator_status, batch_id, create_time)
VALUES
  (0, 1, @bizMonth, @ztid, @periodStart, @periodEnd,
   @doneCount, @headcount, @efficiency, @denomStatus, @batchId, @now)
ON DUPLICATE KEY UPDATE
  period_start=VALUES(period_start), period_end=VALUES(period_end),
  done_count=VALUES(done_count), production_headcount=VALUES(production_headcount),
  efficiency=VALUES(efficiency), denominator_status=VALUES(denominator_status),
  batch_id=VALUES(batch_id), update_time=@now",
            new SugarParameter("@bizMonth", option.BizMonth),
            new SugarParameter("@ztid", option.SourceZtid),
            new SugarParameter("@periodStart", option.MonthlyPeriodStart),
            new SugarParameter("@periodEnd", option.MonthlyPeriodEnd),
            new SugarParameter("@doneCount", doneCount),
            new SugarParameter("@headcount", headcount),
            new SugarParameter("@efficiency", efficiency),
            new SugarParameter("@denomStatus", denomStatus),
            new SugarParameter("@batchId", batchId),
            new SugarParameter("@now", now));
        sub.DwdRows = dwdAffected;

        sub.KpiRows = await UpsertKpiValueAsync("S6_L1_002", option.MonthlyPeriodEnd, efficiency, now);
        return sub;
    }

    // ─────────────────────────────────────────────────────────────────────────

    private async Task<List<T>> QueryT8Async<T>(string sql, SugarParameter[] parameters)
    {
        var t8 = _db.AsTenant().GetConnectionScope(T8ConfigId);
        return await t8.Ado.SqlQueryAsync<T>(sql, parameters);
    }

    private async Task<int> UpsertKpiValueAsync(string metricCode, DateTime bizDate, decimal? metricValue, DateTime now)
    {
        // 沿用 S3 UpsertS3KpiValueAsync 范式：先查现存行 → UPDATE；不存在 → SELECT MAX(id)+1 显式生成 id 后 INSERT。
        // ado_s9_kpi_value_l1_day.id 为手工分配主键（无 AUTO_INCREMENT），必须显式 set；
        // metric_value 允许 NULL（分母缺失不得伪装真实 0）。
        // FIX-2：截断时分秒（月度 KPI 入参可能为 YYYY-MM-DD 23:59:59），保证 SELECT WHERE biz_date=@BizDate 与 DB date 列匹配，避免重复 INSERT。
        bizDate = bizDate.Date;
        var existingId = await _db.Ado.GetLongAsync(
            "SELECT IFNULL((SELECT id FROM ado_s9_kpi_value_l1_day WHERE tenant_id=0 AND factory_id=1 " +
            "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("@ModuleCode", ModuleCode),
                new("@MetricCode", metricCode),
                new("@BizDate", bizDate)
            });

        if (existingId > 0)
        {
            return await _db.Ado.ExecuteCommandAsync(
                "UPDATE ado_s9_kpi_value_l1_day SET metric_value=@MetricValue, calc_time=@Now, " +
                "update_time=@Now, is_deleted=0, is_active=1 WHERE id=@Id",
                new SugarParameter("@MetricValue", metricValue),
                new SugarParameter("@Now", now),
                new SugarParameter("@Id", existingId));
        }

        var nextId = await _db.Ado.GetLongAsync(
            "SELECT COALESCE(MAX(id), 0) + 1 FROM ado_s9_kpi_value_l1_day");
        return await _db.Ado.ExecuteCommandAsync(@"
INSERT INTO ado_s9_kpi_value_l1_day
  (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, calc_time)
VALUES
  (@Id, 0, NULL, NULL, 1, NULL, @BizDate,
   @Now, @Now, 0, 1,
   @ModuleCode, @MetricCode, @MetricValue, @Now)",
            new SugarParameter("@Id", nextId),
            new SugarParameter("@BizDate", bizDate),
            new SugarParameter("@Now", now),
            new SugarParameter("@ModuleCode", ModuleCode),
            new SugarParameter("@MetricCode", metricCode),
            new SugarParameter("@MetricValue", metricValue));
    }

    private async Task<long> InsertTransformRunLogAsync(string batchId, DateTime startedAt, string triggerType)
    {
        await _db.Ado.ExecuteCommandAsync(@"
INSERT INTO mdp_transform_run_log
  (tenant_id, job_code, job_name, trigger_type, batch_id, status, start_time, stage_rows, standard_rows, dwd_rows, create_time, update_time)
VALUES
  (0, @JobCode, @JobName, @TriggerType, @BatchId, 'RUNNING', @StartTime, 0, 0, 0, @StartTime, @StartTime)",
            new SugarParameter("@JobCode", JobCode),
            new SugarParameter("@JobName", JobName),
            new SugarParameter("@TriggerType", triggerType),
            new SugarParameter("@BatchId", batchId),
            new SugarParameter("@StartTime", startedAt));
        return await _db.Ado.GetLongAsync(
            "SELECT id FROM mdp_transform_run_log WHERE batch_id=@BatchId ORDER BY id DESC LIMIT 1",
            new List<SugarParameter> { new("@BatchId", batchId) });
    }

    private async Task MarkTransformRunSuccessAsync(long runLogId, DateTime startedAt, S6MdpSyncTransformResult result)
    {
        var finishedAt = DateTime.Now;
        await _db.Ado.ExecuteCommandAsync(@"
UPDATE mdp_transform_run_log
SET status='SUCCESS', end_time=@EndTime, duration_ms=@DurationMs,
    stage_rows=@StageRows, standard_rows=@StandardRows, dwd_rows=@DwdRows,
    summary_json=@SummaryJson, update_time=CURRENT_TIMESTAMP
WHERE id=@Id",
            new SugarParameter("@EndTime", finishedAt),
            new SugarParameter("@DurationMs", (int)(finishedAt - startedAt).TotalMilliseconds),
            new SugarParameter("@StageRows", result.StageRows),
            new SugarParameter("@StandardRows", result.StandardRows),
            new SugarParameter("@DwdRows", result.DwdRows),
            new SugarParameter("@SummaryJson", JsonSerializer.Serialize(new
            {
                batchId = result.BatchId,
                sourceZtid = result.SourceZtid,
                bizDate = result.BizDate.ToString("yyyy-MM-dd"),
                bizMonth = result.BizMonth,
                dwdRows = result.DwdRows,
                kpiRows = result.KpiRows,
                perKpiDwdRows = result.PerKpiDwdRows,
                perKpiKpiRows = result.PerKpiKpiRows,
                denominatorStatus = result.KpiDenominatorStatus
            })),
            new SugarParameter("@Id", runLogId));
    }

    private async Task MarkTransformRunFailedAsync(long runLogId, DateTime startedAt, string message)
    {
        try
        {
            var finishedAt = DateTime.Now;
            await _db.Ado.ExecuteCommandAsync(@"
UPDATE mdp_transform_run_log
SET status='FAILED', end_time=@EndTime, duration_ms=@DurationMs,
    error_message=@ErrorMessage, update_time=CURRENT_TIMESTAMP
WHERE id=@Id",
                new SugarParameter("@EndTime", finishedAt),
                new SugarParameter("@DurationMs", (int)(finishedAt - startedAt).TotalMilliseconds),
                new SugarParameter("@ErrorMessage", Truncate(message, 2000)),
                new SugarParameter("@Id", runLogId));
        }
        catch (Exception ex)
        {
            Console.Error.WriteLine($"[S6MdpSyncTransform] MarkTransformRunFailed write failed (runLogId={runLogId}): {ex.Message}");
        }
    }

    private static string NormalizeTriggerType(string s) =>
        string.IsNullOrWhiteSpace(s) ? "AUTO" : s.Trim().ToUpperInvariant();

    private static string Truncate(string s, int max) =>
        string.IsNullOrEmpty(s) ? "" : (s.Length <= max ? s : s.Substring(0, max));
}

// DTO ────────────────────────────────────────────────────────────────────────

public sealed class S6MdpRefreshOption
{
    public string SourceZtid { get; set; } = "pbxfxp";
    public DateTime BizDate { get; set; }
    public string BizMonth { get; set; } = "";
    public DateTime MonthlyPeriodStart { get; set; }
    public DateTime MonthlyPeriodEnd { get; set; }

    public static S6MdpRefreshOption Default()
    {
        var today = DateTime.Today;
        var yesterday = today.AddDays(-1);
        var lastMonth = today.AddMonths(-1);
        var monthStart = new DateTime(lastMonth.Year, lastMonth.Month, 1);
        var monthEnd = monthStart.AddMonths(1).AddDays(-1);
        return new S6MdpRefreshOption
        {
            SourceZtid = "pbxfxp",
            BizDate = yesterday,
            BizMonth = lastMonth.ToString("yyyy-MM"),
            MonthlyPeriodStart = monthStart,
            MonthlyPeriodEnd = monthEnd.AddDays(1).AddSeconds(-1)
        };
    }
}

public sealed class S6MdpSyncTransformResult
{
    public string BatchId { get; set; } = "";
    public long RunLogId { get; set; }
    public string TriggerType { get; set; } = "AUTO";
    public string SourceZtid { get; set; } = "";
    public DateTime BizDate { get; set; }
    public string BizMonth { get; set; } = "";
    public DateTime MonthlyPeriodStart { get; set; }
    public DateTime MonthlyPeriodEnd { get; set; }

    public int StageRows { get; set; }
    public int StandardRows { get; set; }
    public int DwdRows { get; set; }
    public int KpiRows { get; set; }

    public Dictionary<string, int> PerKpiDwdRows { get; } = new();
    public Dictionary<string, int> PerKpiKpiRows { get; } = new();
    public List<string> KpiDenominatorStatus { get; } = new();

    public void MergeSub(string kpiCode, KpiBuildSubResult sub)
    {
        PerKpiDwdRows[kpiCode] = sub.DwdRows;
        PerKpiKpiRows[kpiCode] = sub.KpiRows;
        DwdRows += sub.DwdRows;
        KpiRows += sub.KpiRows;
        KpiDenominatorStatus.Add($"{kpiCode}:{sub.DenominatorStatus}");
    }
}

public sealed class KpiBuildSubResult
{
    public int T8Rows { get; set; }
    public int DwdRows { get; set; }
    public int KpiRows { get; set; }
    public string DenominatorStatus { get; set; } = "OK";
}

// T8 result set 投影类型 ──────────────────────────────────────────────────────

internal sealed class S6MfgFulfillmentRow
{
    public string? noid { get; set; }
    public string? rwnoid { get; set; }
    public string? code { get; set; }
    public decimal? sl { get; set; }
    public decimal? slzx { get; set; }
}

internal sealed class S6CountRow
{
    public int ddnum { get; set; }
}

internal sealed class S6PeNumRow
{
    public int penum { get; set; }
}