AiDOPWarehouse/server/Plugins/Admin.NET.Plugin.AiDOP/WorkOrder/WorkOrderKittingCheckService.cs

using Admin.NET.Plugin.AiDOP.Order;

namespace Admin.NET.Plugin.AiDOP.WorkOrder;

/// <summary>
/// 工单齐套检查动作：重新执行资源检查（BOM 展开 + 库存/在途缺口计算），
/// 将新结果写入 b_examine_result / b_bom_child_examine，并更新 mes_morder.MaterialSituation。
/// </summary>
public class WorkOrderKittingCheckService : ITransient
{
    private readonly ISqlSugarClient _db;
    private readonly MaterialRequirementCalculator _calculator;
    private readonly ResourceCheckResultWriter _writer;
    private readonly WorkOrderMaterialDetailSyncService _materialDetailSync;

    public WorkOrderKittingCheckService(
        ISqlSugarClient db,
        MaterialRequirementCalculator calculator,
        ResourceCheckResultWriter writer,
        WorkOrderMaterialDetailSyncService materialDetailSync)
    {
        _db = db;
        _calculator = calculator;
        _writer = writer;
        _materialDetailSync = materialDetailSync;
    }

    /// <summary>批量齐套检查：租户下所有 Status='p'/'r' 的工单逐一重新检查。</summary>
    public async Task<KittingCheckResult> CheckTenantWorkOrdersAsync(long tenantId, string account)
    {
        var workOrds = await _db.Ado.SqlQueryAsync<string>(
            """
            SELECT WorkOrd FROM WorkOrdMaster
            WHERE tenant_id = @TenantId
              AND LOWER(TRIM(IFNULL(Status,''))) IN ('p', 'r')
            ORDER BY WorkOrd
            """,
            new SugarParameter("@TenantId", tenantId));

        var result = new KittingCheckResult();
        foreach (var wo in workOrds)
        {
            var one = await CheckSingleAsync(tenantId, wo, account, bangId: 0, checkScope: "BATCH");
            result.CheckedCount++;
            if (one.IsKitted) result.KittedCount++;
            else result.ShortageCount++;
        }
        return result;
    }

    /// <summary>
    /// 单工单齐套检查：以工单为主，重新 BOM 展开 + 库存计算 → 写入检查结果。
    /// BusinessID>0 时从订单明细行加载数据；BusinessID=0 时从工单/生产工单构造参数。
    /// </summary>
    /// <param name="bangId">
    /// 排程版本。&gt; 0 表示由排产驱动（净口径 + 写占用）；
    /// = 0 表示手动/下达触发，此时自动复用当前有效排程版本，保持口径一致但只读不写。
    /// </param>
    /// <param name="checkScope">触发来源，写入 b_examine_result.check_scope 供事后追溯。</param>
    public async Task<SingleKittingCheckResult> CheckSingleAsync(
        long tenantId, string workOrd, string account, long bangId = 0, string checkScope = "SINGLE")
    {
        var wo = workOrd.Trim();

        // 口径统一（C-D1）：手动/下达触发时也按净口径算——复用当前有效排程版本的占用账，
        // 否则每个工单都以为自己能独占全部库存，多工单同时下达必然超发。
        var isScheduleDriven = bangId > 0;
        if (!isScheduleDriven)
            bangId = await ResolveCurrentScheduleBangIdAsync(tenantId) ?? 0;

        // 1. 从 WorkOrdMaster + mes_morder 获取工单数据
        var wm = await LoadWorkOrdMasterAsync(tenantId, wo);
        if (wm is null)
            throw Oops.Oh($"工单 {wo} 不存在或已关闭");

        OrderWorkOrderGenerationService.OrderEntryLine entry;
        OrderWorkOrderGenerationService.OrderHeader order;

        if (wm.BusinessId > 0)
        {
            // 2a. 有关联订单明细行：从 crm_seorderentry / crm_seorder 加载
            var loadedEntry = await LoadOrderEntryAsync(wm.BusinessId, tenantId);
            if (loadedEntry is not null)
            {
                entry = loadedEntry;
                var loadedOrder = await LoadOrderHeaderAsync(entry.SeOrderId, tenantId);
                order = loadedOrder ?? BuildFallbackOrderFromWorkOrder(wm);
            }
            else
            {
                // 明细行已删除/不存在，回退到工单数据
                entry = BuildEntryFromWorkOrder(wm);
                order = BuildFallbackOrderFromWorkOrder(wm);
            }
        }
        else
        {
            // 2b. 无关联订单明细行（BusinessID=0）：从工单构造检查参数
            entry = BuildEntryFromWorkOrder(wm);
            order = BuildFallbackOrderFromWorkOrder(wm);
        }

        // 3. 重新执行资源检查（BOM 展开 + 库存/在途缺口计算）
        //    非排产触发：只读不写占用（C-D3），并排除本工单自身占用（C-D2）
        var warnings = new List<string>();
        var lines = await _calculator.BuildLinesAsync(
            order, entry, warnings, bangId, wo, wm.MorderId ?? 0,
            writeOccupy: isScheduleDriven,
            excludeOccupyWorkOrd: wo);

        // 4. 将新结果写入 b_examine_result / b_bom_child_examine 并更新 mes_morder
        var checkResult = await _writer.WriteAsync(
            order, entry, wo, wm.MorderId, lines, account, DateTime.Now,
            bangId: bangId, checkScope: checkScope);

        // 5. 同步工单物料明细（WorkOrdDetail）
        await _materialDetailSync.EnsureFromResourceCheckAsync(tenantId, wo, account);

        // 6. 计算齐套率并回写到领料单（NbrMaster.MFRate / NbrDetail.SatisfyQty）
        await WriteBackMFRateAsync(tenantId, wo);

        return new SingleKittingCheckResult
        {
            WorkOrd = wo,
            IsKitted = !checkResult.HasShortage,
            ShortageLineCount = checkResult.HasShortage
                ? lines.Count(x => x.IsUse == 1 && x.LackQty > 0)
                : 0,
            MaterialSituation = checkResult.HasShortage ? "缺料" : "齐套"
        };
    }

    /// <summary>从 WorkOrdMaster + mes_morder 数据构造 OrderEntryLine。</summary>
    private static OrderWorkOrderGenerationService.OrderEntryLine BuildEntryFromWorkOrder(WorkOrdMasterRow wm)
    {
        return new OrderWorkOrderGenerationService.OrderEntryLine
        {
            Id = wm.BusinessId,
            SeOrderId = 0,
            BillNo = wm.SalesJob,
            EntrySeq = 0,
            ItemNumber = wm.WoItemNum,
            ItemName = wm.WoItemName,
            Specification = wm.MorderModel,
            Unit = wm.MorderUnit,
            BomNumber = wm.MorderBomNumber ?? wm.WoBomFormula,
            Qty = wm.WoQtyOrded,
            PlanDate = wm.WoDueDate,
            SysCapacityDate = wm.WoDueDate,
            Progress = null,
            Urgent = wm.WoUrgent,
            FactoryId = null,
            CompanyId = null,
            TenantId = wm.WoTenantId
        };
    }

    /// <summary>从 WorkOrdMaster 数据构造兜底 OrderHeader。</summary>
    private static OrderWorkOrderGenerationService.OrderHeader BuildFallbackOrderFromWorkOrder(WorkOrdMasterRow wm)
    {
        return new OrderWorkOrderGenerationService.OrderHeader
        {
            Id = 0,
            BillNo = wm.SalesJob,
            CustomNo = wm.WoCustNo,
            Urgent = wm.WoUrgent,
            FactoryId = null,
            TenantId = wm.WoTenantId
        };
    }

    // ──────────────── 数据加载 ────────────────

    /// <summary>
    /// 取当前有效排程版本 id（= 最近一次成功排产的 aidop_action_run_log.id，与 bang_id 同源）。
    /// 与 ProductionScheduleGenerationService.ResolveCurrentScheduleBangIdAsync 同逻辑；
    /// 无成功排产记录时返回 null，口径退化为毛口径（与改动前行为一致，不会更差）。
    /// </summary>
    private async Task<long?> ResolveCurrentScheduleBangIdAsync(long tenantId)
    {
        var id = await _db.Ado.GetLongAsync(
            """
            SELECT id FROM aidop_action_run_log
            WHERE tenant_id = @TenantId AND action_code = @ActionCode AND status = 'SUCCESS'
            ORDER BY start_time DESC, id DESC
            LIMIT 1
            """,
            new List<SugarParameter>
            {
                new("@TenantId", tenantId),
                new("@ActionCode", "S2_SCHEDULE_GENERATE")
            });
        return id > 0 ? id : null;
    }

    private async Task<WorkOrdMasterRow?> LoadWorkOrdMasterAsync(long tenantId, string workOrd)
    {
        var rows = await _db.Ado.SqlQueryAsync<WorkOrdMasterRow>(
            """
            SELECT wm.RecID, wm.WorkOrd, wm.BusinessID, wm.SalesJob,
                   wm.ItemNum AS WoItemNum, wm.ItemName AS WoItemName,
                   wm.QtyOrded AS WoQtyOrded, wm.DueDate AS WoDueDate,
                   wm.Urgent AS WoUrgent, wm.CustNo AS WoCustNo,
                   wm.BOMFormula AS WoBomFormula,
                   wm.tenant_id AS WoTenantId,
                   morder.Id AS MorderId,
                   morder.bom_number AS MorderBomNumber,
                   morder.product_code AS MorderProductCode,
                   morder.product_name AS MorderProductName,
                   morder.fmodel AS MorderModel,
                   morder.unit AS MorderUnit
            FROM WorkOrdMaster wm
            LEFT JOIN mes_morder morder
                ON morder.morder_no = wm.WorkOrd
               AND morder.tenant_id = wm.tenant_id
               AND morder.IsDeleted = 0
            WHERE wm.tenant_id = @TenantId
              AND wm.WorkOrd = @WorkOrd
              AND LOWER(TRIM(IFNULL(wm.Status,''))) <> 'c'
            ORDER BY wm.RecID DESC
            LIMIT 1
            """,
            new SugarParameter("@TenantId", tenantId),
            new SugarParameter("@WorkOrd", workOrd));
        return rows.FirstOrDefault();
    }

    private async Task<OrderWorkOrderGenerationService.OrderEntryLine?> LoadOrderEntryAsync(long entryId, long tenantId)
    {
        var rows = await _db.Ado.SqlQueryAsync<OrderWorkOrderGenerationService.OrderEntryLine>(
            """
            SELECT
                Id, seorder_id AS SeOrderId, bill_no AS BillNo, entry_seq AS EntrySeq,
                item_number AS ItemNumber, item_name AS ItemName, specification AS Specification,
                unit AS Unit, bom_number AS BomNumber, qty AS Qty,
                plan_date AS PlanDate, sys_capacity_date AS SysCapacityDate,
                progress AS Progress, urgent AS Urgent,
                factory_id AS FactoryId, company_id AS CompanyId, tenant_id AS TenantId
            FROM crm_seorderentry
            WHERE Id = @Id AND tenant_id = @TenantId AND IsDeleted = 0
            LIMIT 1
            """,
            new SugarParameter("@Id", entryId),
            new SugarParameter("@TenantId", tenantId));
        return rows.FirstOrDefault();
    }

    private async Task<OrderWorkOrderGenerationService.OrderHeader?> LoadOrderHeaderAsync(long orderId, long tenantId)
    {
        var rows = await _db.Ado.SqlQueryAsync<OrderWorkOrderGenerationService.OrderHeader>(
            """
            SELECT Id, bill_no AS BillNo, custom_no AS CustomNo, urgent AS Urgent,
                   factory_id AS FactoryId, tenant_id AS TenantId
            FROM crm_seorder
            WHERE Id = @Id AND tenant_id = @TenantId AND IsDeleted = 0
            LIMIT 1
            """,
            new SugarParameter("@Id", orderId),
            new SugarParameter("@TenantId", tenantId));
        return rows.FirstOrDefault();
    }

    /// <summary>
    /// 计算工单齐套率并回写到领料单：
    /// NbrMaster.MFRate = MIN(UseQty / Qty)（所有有效物料 erp_cls!=4），上限 1.0；
    /// NbrDetail.SatisfyQty = (QtyOrd - QtyRec) * MFRate。
    /// </summary>
    public async Task WriteBackMFRateAsync(long tenantId, string workOrd)
    {
        if (string.IsNullOrWhiteSpace(workOrd)) return;

        // 1. 取最新一次齐套检查的 examine_id
        var examineId = await _db.Ado.GetLongAsync(
            """
            SELECT Id FROM b_examine_result
            WHERE tenant_id = @TenantId AND morder_no = @WorkOrd AND IsDeleted = 0
            ORDER BY create_time DESC
            LIMIT 1
            """,
            new List<SugarParameter>
            {
                new("@TenantId", tenantId),
                new("@WorkOrd", workOrd)
            });

        if (examineId <= 0) return;

        // 2. 查询所有有效物料行（is_use=1, erp_cls!=4, qty>0）
        var materials = await _db.Ado.SqlQueryAsync<MFRateMaterialRow>(
            """
            SELECT IFNULL(use_qty, 0) AS UseQty, IFNULL(qty, 0) AS Qty
            FROM b_bom_child_examine
            WHERE examine_id = @ExamineId AND is_use = 1 AND IFNULL(erp_cls, 0) <> 4 AND IFNULL(qty, 0) > 0
            """,
            new SugarParameter("@ExamineId", examineId));

        // 3. 计算 MFRate = MIN(UseQty / Qty)，上限 1.0
        decimal? mfRate = null;
        if (materials.Count > 0)
        {
            decimal minRate = 1.0m;
            foreach (var mat in materials)
            {
                if (mat.Qty <= 0) continue;
                var rate = mat.UseQty / mat.Qty;
                if (rate < minRate) minRate = rate;
            }
            mfRate = Math.Round(Math.Min(minRate, 1.0m), 5, MidpointRounding.AwayFromZero);
        }

        // 4. 更新 NbrMaster.MFRate
        await _db.Ado.ExecuteCommandAsync(
            """
            UPDATE NbrMaster
            SET MFRate = @MFRate, UpdateTime = NOW()
            WHERE tenant_id = @TenantId AND WorkOrd = @WorkOrd AND Type = 'SM'
              AND IFNULL(IsActive, 0) = 1
            """,
            new SugarParameter("@MFRate", (object?)mfRate ?? DBNull.Value),
            new SugarParameter("@TenantId", tenantId),
            new SugarParameter("@WorkOrd", workOrd));

        // 5. 更新 NbrDetail.SatisfyQty = GREATEST(QtyOrd - QtyRec, 0) * MFRate
        if (mfRate.HasValue)
        {
            await _db.Ado.ExecuteCommandAsync(
                """
                UPDATE NbrDetail
                SET SatisfyQty = ROUND(GREATEST(IFNULL(QtyOrd, 0) - IFNULL(QtyRec, 0), 0) * @MFRate, 5),
                    UpdateTime = NOW()
                WHERE tenant_id = @TenantId AND WorkOrd = @WorkOrd AND Type = 'SM'
                  AND IFNULL(IsActive, 0) = 1 AND UPPER(IFNULL(Status, '')) <> 'C'
                """,
                new SugarParameter("@MFRate", mfRate.Value),
                new SugarParameter("@TenantId", tenantId),
                new SugarParameter("@WorkOrd", workOrd));
        }
    }

    // ──────────────── 内部类 ────────────────

    private sealed class MFRateMaterialRow
    {
        public decimal UseQty { get; set; }
        public decimal Qty { get; set; }
    }

    private sealed class WorkOrdMasterRow
    {
        public long RecID { get; set; }
        public string WorkOrd { get; set; } = string.Empty;
        public long BusinessId { get; set; }
        public string? SalesJob { get; set; }
        public long? MorderId { get; set; }
        // WorkOrdMaster 字段
        public string? WoItemNum { get; set; }
        public string? WoItemName { get; set; }
        public decimal WoQtyOrded { get; set; }
        public DateTime? WoDueDate { get; set; }
        public int WoUrgent { get; set; }
        public string? WoCustNo { get; set; }
        public string? WoBomFormula { get; set; }
        public long WoTenantId { get; set; }
        // mes_morder 字段
        public string? MorderBomNumber { get; set; }
        public string? MorderProductCode { get; set; }
        public string? MorderProductName { get; set; }
        public string? MorderModel { get; set; }
        public string? MorderUnit { get; set; }
    }

    public sealed class KittingCheckResult
    {
        public int CheckedCount { get; set; }
        public int KittedCount { get; set; }
        public int ShortageCount { get; set; }
    }

    public sealed class SingleKittingCheckResult
    {
        public string WorkOrd { get; set; } = string.Empty;
        public bool IsKitted { get; set; }
        public int ShortageLineCount { get; set; }
        public string MaterialSituation { get; set; } = string.Empty;
    }
}