AiDOPWarehouse/server/Plugins/Admin.NET.Plugin.AiDOP/Order/MaterialRequirementCalculator.cs

using Yitter.IdGenerator;

namespace Admin.NET.Plugin.AiDOP.Order;

/// <summary>
/// 订单评审资源检查：多层 BOM 展开与库存/采购在途缺口计算。
/// </summary>
public class MaterialRequirementCalculator : ITransient
{
    private const int MaxBomDepth = 6;
    private readonly ISqlSugarClient _db;

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

    public async Task<List<ResourceCheckBomLine>> BuildLinesAsync(
        OrderWorkOrderGenerationService.OrderHeader order,
        OrderWorkOrderGenerationService.OrderEntryLine entry,
        List<string> warnings,
        long bangId = 0)
    {
        var itemNum = entry.ItemNumber!.Trim();
        var orderQty = entry.Qty ?? 0;
        var needTime = entry.SysCapacityDate ?? entry.PlanDate ?? DateTime.Today;
        var lines = new List<ResourceCheckBomLine>();

        var rootFid = YitIdHelper.NextId();
        lines.Add(new ResourceCheckBomLine
        {
            Fid = rootFid,
            Num = "1",
            Level = 1,
            ItemNumber = itemNum,
            ItemName = entry.ItemName,
            Model = entry.Specification,
            Unit = entry.Unit,
            BomNumber = entry.BomNumber,
            ErpCls = 1,
            ErpClsName = "自制",
            NeedCount = orderQty,
            NeedCountNoLoss = orderQty,
            KittingTime = needTime,
            SatisfyTime = needTime,
            IsUse = 1
        });

        var bomId = await ResolveBomIdAsync(entry, itemNum);
        if (bomId is null)
        {
            warnings.Add($"订单行 {entry.EntrySeq}（{itemNum}）未匹配到 BOM，仅写入成品行资源检查结果");
            await ApplySupplyAsync(lines, entry.TenantId, bangId);
            return lines;
        }

        var children = await LoadBomChildrenAsync(bomId.Value, entry.TenantId);
        if (children.Count == 0)
        {
            warnings.Add($"订单行 {entry.EntrySeq} BOM {entry.BomNumber ?? bomId.ToString()} 无子件明细");
            await ApplySupplyAsync(lines, entry.TenantId, bangId);
            return lines;
        }

        var seq = 1;
        foreach (var child in children)
        {
            seq = await AppendBomChildAsync(
                lines, rootFid, "1", 1, orderQty, child, entry, needTime, warnings, seq, depth: 1);
        }

        await ApplySupplyAsync(lines, entry.TenantId, bangId);
        return lines;
    }

    private async Task<int> AppendBomChildAsync(
        List<ResourceCheckBomLine> lines,
        long parentFid,
        string parentNum,
        int parentLevel,
        decimal parentNeedQty,
        BomChildRow child,
        OrderWorkOrderGenerationService.OrderEntryLine entry,
        DateTime needTime,
        List<string> warnings,
        int seq,
        int depth)
    {
        var unitQty = child.Qty <= 0 ? 1m : child.Qty;
        var wastageFactor = 1m + (child.Wastage / 100m);
        var scrapFactor = 1m + (child.Scrap / 100m);
        var needNoLoss = parentNeedQty * unitQty;
        var needCount = RoundQty(needNoLoss * wastageFactor * scrapFactor);

        if (needCount > 10000 && parentNeedQty > 0 && unitQty > 100)
        {
            warnings.Add($"物料 {child.ItemNumber} BOM用量({unitQty}) × 父需求({parentNeedQty}) = 需求数量({needCount})，请核实 BOM 配置");
        }
        var isVirtual = child.ErpCls == 4;
        var num = $"{parentNum}.{seq}";
        var fid = YitIdHelper.NextId();
        var nextSeq = seq + 1;

        if (!isVirtual)
        {
            lines.Add(new ResourceCheckBomLine
            {
                Fid = fid,
                ParentFid = parentFid,
                BomChildId = child.Id,
                Num = num,
                Level = parentLevel + 1,
                Type = child.Type,
                ItemNumber = child.ItemNumber ?? string.Empty,
                ItemName = child.ItemName,
                Model = child.Model,
                Unit = child.Unit,
                BomNumber = child.BomNumber,
                ErpCls = child.ErpCls,
                ErpClsName = MapErpClsName(child.ErpCls),
                Backflush = child.Backflush,
                Qty = unitQty,
                Wastage = child.Wastage,
                Scrap = child.Scrap,
                NeedCount = needCount,
                NeedCountNoLoss = RoundQty(needNoLoss),
                KittingTime = needTime,
                SatisfyTime = needTime,
                IsBom = child.IsBom,
                HaveIcSubs = child.HaveIcSubs,
                SubstituteCode = child.SubstituteCode,
                IsUse = 1
            });
        }

        seq = nextSeq;
        var expandParentFid = isVirtual ? parentFid : fid;
        var expandParentNum = num;
        var expandNeed = needCount;

        if (child.IsBom == 1 && depth < MaxBomDepth && !string.IsNullOrWhiteSpace(child.ItemNumber))
        {
            var subBomId = await ResolveBomIdByItemAsync(child.ItemNumber!, entry.TenantId);
            if (subBomId is null)
            {
                if (!isVirtual)
                    warnings.Add($"物料 {child.ItemNumber} 标记为 BOM 但未找到子阶 BOM，按单层处理");
                return seq;
            }

            var grandchildren = await LoadBomChildrenAsync(subBomId.Value, entry.TenantId);
            if (grandchildren.Count == 0)
            {
                warnings.Add($"物料 {child.ItemNumber} 子阶 BOM 无明细");
                return seq;
            }

            var childSeq = 1;
            foreach (var gc in grandchildren)
            {
                childSeq = await AppendBomChildAsync(
                    lines,
                    expandParentFid,
                    expandParentNum,
                    parentLevel + 1,
                    expandNeed,
                    gc,
                    entry,
                    needTime,
                    warnings,
                    childSeq,
                    depth + 1);
            }
        }
        else if (child.IsBom == 1 && depth >= MaxBomDepth)
        {
            warnings.Add($"物料 {child.ItemNumber} 超过最大 BOM 展开层级 {MaxBomDepth}，停止下钻");
        }

        return seq;
    }

    private async Task<long?> ResolveBomIdAsync(
        OrderWorkOrderGenerationService.OrderEntryLine entry,
        string itemNum)
    {
        var bomNumber = entry.BomNumber?.Trim();
        var rows = await _db.Ado.SqlQueryAsync<BomHeaderRow>(
            """
            SELECT Id, bom_number AS BomNumber, item_number AS ItemNumber
            FROM ic_bom
            WHERE tenant_id = @TenantId AND IsDeleted = 0
              AND (
                    (@BomNumber <> '' AND bom_number = @BomNumber)
                 OR (@BomNumber = '' AND item_number = @ItemNum)
                 OR item_number = @ItemNum
              )
            ORDER BY
                CASE WHEN @BomNumber <> '' AND bom_number = @BomNumber THEN 0 ELSE 1 END,
                Id DESC
            LIMIT 1
            """,
            new SugarParameter("@TenantId", entry.TenantId),
            new SugarParameter("@BomNumber", bomNumber ?? string.Empty),
            new SugarParameter("@ItemNum", itemNum));
        return rows.FirstOrDefault()?.Id;
    }

    private async Task<long?> ResolveBomIdByItemAsync(string itemNum, long tenantId)
    {
        var rows = await _db.Ado.SqlQueryAsync<BomHeaderRow>(
            """
            SELECT Id
            FROM ic_bom
            WHERE tenant_id = @TenantId AND IsDeleted = 0 AND item_number = @ItemNum
            ORDER BY Id DESC
            LIMIT 1
            """,
            new SugarParameter("@TenantId", tenantId),
            new SugarParameter("@ItemNum", itemNum.Trim()));
        return rows.FirstOrDefault()?.Id;
    }

    private async Task<List<BomChildRow>> LoadBomChildrenAsync(long bomId, long tenantId)
    {
        return await _db.Ado.SqlQueryAsync<BomChildRow>(
            """
            SELECT
                c.Id,
                c.bom_number AS BomNumber,
                c.item_number AS ItemNumber,
                c.item_name AS ItemName,
                IFNULL(im.Descr1, '') AS Model,
                c.unit AS Unit,
                IFNULL(c.qty, 1) AS Qty,
                IFNULL(c.wastage, 0) AS Wastage,
                IFNULL(c.scrap, 0) AS Scrap,
                IFNULL(c.backflush, 0) AS Backflush,
                IFNULL(c.type, 0) AS Type,
                IFNULL(c.erp_cls, 3) AS ErpCls,
                IFNULL(c.is_bom, 0) AS IsBom,
                IFNULL(c.haveicsubs, 0) AS HaveIcSubs,
                c.substitute_code AS SubstituteCode
            FROM ic_bom_child c
            LEFT JOIN ItemMaster im ON c.item_number = im.ItemNum
            WHERE c.bom_id = @BomId
              AND c.tenant_id = @TenantId
              AND c.IsDeleted = 0
            ORDER BY IFNULL(c.child_num, 0), IFNULL(c.entryid, 0), c.Id
            """,
            new SugarParameter("@BomId", bomId),
            new SugarParameter("@TenantId", tenantId));
    }

    private async Task ApplySupplyAsync(List<ResourceCheckBomLine> lines, long tenantId, long bangId = 0)
    {
        var stockMap = new Dictionary<string, decimal>(StringComparer.OrdinalIgnoreCase);
        var transitMap = new Dictionary<string, decimal>(StringComparer.OrdinalIgnoreCase);

        // 查询本次计算批次(bangId)已记录的库存占用和采购在途占用
        var occupiedStockMap = new Dictionary<string, decimal>(StringComparer.OrdinalIgnoreCase);
        var occupiedTransitMap = new Dictionary<string, decimal>(StringComparer.OrdinalIgnoreCase);
        if (bangId > 0)
        {
            var occupiedRows = await _db.Ado.SqlQueryAsync<OccupyRow>(
                """
                SELECT icitem_number AS ItemNumber, IFNULL(SUM(quantity), 0) AS TotalOccupied
                FROM ic_item_stockoccupy
                WHERE tenant_id = @TenantId AND bang_id = @BangId AND IsDeleted = 0
                GROUP BY icitem_number
                """,
                new SugarParameter("@TenantId", tenantId),
                new SugarParameter("@BangId", bangId));
            foreach (var r in occupiedRows)
                occupiedStockMap[r.ItemNumber] = r.TotalOccupied;

            var occupiedTransitRows = await _db.Ado.SqlQueryAsync<OccupyRow>(
                """
                SELECT ItemNumber, IFNULL(SUM(OccupyQty), 0) AS TotalOccupied
                FROM srm_po_occupy
                WHERE tenant_id = @TenantId AND bang_id = @BangId AND IsDeleted = 0
                GROUP BY ItemNumber
                """,
                new SugarParameter("@TenantId", tenantId),
                new SugarParameter("@BangId", bangId));
            foreach (var r in occupiedTransitRows)
                occupiedTransitMap[r.ItemNumber] = r.TotalOccupied;
        }

        foreach (var line in lines)
        {
            if (string.IsNullOrWhiteSpace(line.ItemNumber))
                continue;

            if (!stockMap.TryGetValue(line.ItemNumber, out var stock))
            {
                var rawStock = await QueryStockQtyAsync(line.ItemNumber, tenantId);
                // 扣减已被同批次其他工单占用的库存
                var occupied = occupiedStockMap.TryGetValue(line.ItemNumber, out var occ) ? occ : 0m;
                stock = Math.Max(0m, rawStock - occupied);
                stockMap[line.ItemNumber] = stock;
            }

            if (!transitMap.TryGetValue(line.ItemNumber, out var inTransit))
            {
                var rawTransit = await QueryOpenPurchaseQtyAsync(line.ItemNumber, tenantId);
                // 扣减已被同批次其他工单占用的在途
                var occupied = occupiedTransitMap.TryGetValue(line.ItemNumber, out var occ) ? occ : 0m;
                inTransit = Math.Max(0m, rawTransit - occupied);
                transitMap[line.ItemNumber] = inTransit;
            }

            line.StockQty = stock;
            line.PurchaseOccupyQty = inTransit;
            var available = RoundQty(stock + inTransit);
            line.Sqty = RoundQty(Math.Min(line.NeedCount, available));
            line.LackQty = RoundQty(Math.Max(0m, line.NeedCount - line.Sqty));
            line.SelfLackQty = line.LackQty;
            line.UseQty = line.Sqty;
            line.MoQty = 0;
            line.MakeQty = line.ErpCls == 1 ? line.LackQty : 0;
            line.PurchaseQty = line.ErpCls == 3 ? line.LackQty : 0;

            var consumedFromStock = RoundQty(Math.Min(stock, line.Sqty));
            stockMap[line.ItemNumber] = RoundQty(Math.Max(0m, stock - consumedFromStock));
            var remainingSqty = RoundQty(Math.Max(0m, line.Sqty - consumedFromStock));
            transitMap[line.ItemNumber] = RoundQty(Math.Max(0m, inTransit - remainingSqty));
        }

        // 写入占用记录（库存占用和在途占用）
        if (bangId > 0)
        {
            foreach (var line in lines)
            {
                if (string.IsNullOrWhiteSpace(line.ItemNumber) || line.IsUse != 1)
                    continue;
                if (line.UseQty > 0)
                {
                    var stockUsed = RoundQty(Math.Min(line.StockQty, line.UseQty));
                    if (stockUsed > 0)
                    {
                        await _db.Ado.ExecuteCommandAsync(
                            """
                            INSERT INTO ic_item_stockoccupy (
                                Id, icitem_number, quantity, morder_mo, bang_id,
                                tenant_id, occupy_time, create_time, update_time
                            ) VALUES (
                                @Id, @ItemNumber, @Qty, @WorkOrd, @BangId,
                                @TenantId, @Now, @Now, @Now
                            )
                            """,
                            new SugarParameter("@Id", YitIdHelper.NextId()),
                            new SugarParameter("@ItemNumber", line.ItemNumber),
                            new SugarParameter("@Qty", stockUsed),
                            new SugarParameter("@WorkOrd", ""),
                            new SugarParameter("@BangId", bangId),
                            new SugarParameter("@TenantId", tenantId),
                            new SugarParameter("@Now", DateTime.Now));
                    }

                    var transitUsed = RoundQty(Math.Max(0m, line.UseQty - stockUsed));
                    if (transitUsed > 0)
                    {
                        await _db.Ado.ExecuteCommandAsync(
                            """
                            INSERT INTO srm_po_occupy (
                                Id, ItemNumber, OccupyQty, morder_mo, bang_id,
                                tenant_id, create_time, update_time
                            ) VALUES (
                                @Id, @ItemNumber, @Qty, @WorkOrd, @BangId,
                                @TenantId, @Now, @Now
                            )
                            """,
                            new SugarParameter("@Id", YitIdHelper.NextId()),
                            new SugarParameter("@ItemNumber", line.ItemNumber),
                            new SugarParameter("@Qty", transitUsed),
                            new SugarParameter("@WorkOrd", ""),
                            new SugarParameter("@BangId", bangId),
                            new SugarParameter("@TenantId", tenantId),
                            new SugarParameter("@Now", DateTime.Now));
                    }
                }
            }
        }
    }

    private async Task<decimal> QueryStockQtyAsync(string itemNum, long tenantId)
    {
        var qty = await _db.Ado.GetDecimalAsync(
            """
            SELECT COALESCE(SUM(
                CASE
                    WHEN AvailStatusQty IS NOT NULL THEN AvailStatusQty
                    WHEN QtyOnHand IS NOT NULL THEN QtyOnHand
                    ELSE 0
                END
            ), 0)
            FROM InvMaster
            WHERE ItemNum = @ItemNum
              AND (tenant_id = @TenantId OR @TenantId = 0)
            """,
            new List<SugarParameter>
            {
                new("@ItemNum", itemNum),
                new("@TenantId", tenantId)
            });
        return RoundQty(qty);
    }

    private async Task<decimal> QueryOpenPurchaseQtyAsync(string itemNum, long tenantId)
    {
        var qty = await _db.Ado.GetDecimalAsync(
            """
            SELECT COALESCE(SUM(
                GREATEST(IFNULL(d.QtyOrded, 0) - IFNULL(d.QtyReceived, 0), 0)
            ), 0)
            FROM PurOrdDetail d
            INNER JOIN PurOrdMaster m ON m.RecID = d.PurOrdRecID
            WHERE d.ItemNum = @ItemNum
              AND m.tenant_id = @TenantId
              AND IFNULL(d.IsActive, 1) = 1
              AND IFNULL(m.IsActive, 1) = 1
            """,
            new SugarParameter("@ItemNum", itemNum),
            new SugarParameter("@TenantId", tenantId));
        return RoundQty(qty);
    }

    private static decimal RoundQty(decimal value) =>
        Math.Round(value, 4, MidpointRounding.AwayFromZero);

    private static string MapErpClsName(int erpCls) => erpCls switch
    {
        0 => "配置类",
        1 => "自制",
        2 => "委外加工",
        3 => "外购",
        4 => "虚拟件",
        _ => string.Empty
    };

    private sealed class BomHeaderRow
    {
        public long Id { get; set; }
        public string? BomNumber { get; set; }
        public string? ItemNumber { get; set; }
    }

    private sealed class BomChildRow
    {
        public long Id { get; set; }
        public string? BomNumber { get; set; }
        public string? ItemNumber { get; set; }
        public string? ItemName { get; set; }
        public string? Model { get; set; }
        public string? Unit { get; set; }
        public decimal Qty { get; set; }
        public decimal Wastage { get; set; }
        public decimal Scrap { get; set; }
        public int Backflush { get; set; }
        public int Type { get; set; }
        public int ErpCls { get; set; }
        public int IsBom { get; set; }
        public int HaveIcSubs { get; set; }
        public string? SubstituteCode { get; set; }
    }

    private sealed class OccupyRow
    {
        public string ItemNumber { get; set; } = string.Empty;
        public decimal TotalOccupied { get; set; }
    }
}