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

using Admin.NET.Plugin.AiDOP.Infrastructure;
using Microsoft.Extensions.Logging;

namespace Admin.NET.Plugin.AiDOP.WorkOrder;

/// <summary>
/// 工单 BOM 上传服务 — 从 Excel 解析结果按（订单号+父物料号）匹配未下达工单，
/// 批量写入 ItemMaster + WorkOrdDetail。
/// 路由前缀：/api/WorkOrder/dispatch/bom/...
///
/// 【租户安全边界】需认证访问；租户一律经
/// <c>AidopTenantScope.ResolveOrThrow</c> 从认证后 JWT 解析。
/// </summary>
[ApiDescriptionSettings(Order = 258, Description = "工单BOM上传")]
[Route("api/WorkOrder")]
[NonUnify]
public class WorkOrderBomUploadService : IDynamicApiController, ITransient
{
    private readonly ISqlSugarClient _db;
    private readonly UserManager _userManager;
    private readonly ILogger<WorkOrderBomUploadService> _logger;

    public WorkOrderBomUploadService(
        ISqlSugarClient db,
        UserManager userManager,
        ILogger<WorkOrderBomUploadService> logger)
    {
        _db = db;
        _userManager = userManager;
        _logger = logger;
    }

    private long ResolveTenantId() => AidopTenantScope.ResolveOrThrow(_userManager);

    // ══════════════════════════════════════════════════════════════
    // POST /api/WorkOrder/dispatch/bom/check-materials
    // ══════════════════════════════════════════════════════════════
    /// <summary>校验 BOM 子物料是否在 ItemMaster 中存在。</summary>
    [DisplayName("BOM物料存在性校验")]
    [ApiDescriptionSettings(Name = "BomCheckMaterials"), HttpPost("dispatch/bom/check-materials")]
    public async Task<BomCheckResult> CheckMaterials([FromBody] BomCheckMaterialsInput input)
    {
        var tid = ResolveTenantId();
        if (input.Items == null || input.Items.Count == 0)
            throw Oops.Oh("BOM 物料列表为空");

        var itemNums = input.Items
            .Where(x => !string.IsNullOrWhiteSpace(x.ItemNum))
            .Select(x => x.ItemNum.Trim())
            .Distinct()
            .ToList();

        if (itemNums.Count == 0)
            throw Oops.Oh("BOM 中无有效的物料编码");

        // 查询本租户下已存在的物料编码
        var existingSet = new HashSet<string>(StringComparer.OrdinalIgnoreCase);
        var pars = new List<SugarParameter> { new("@TenantId", tid) };
        for (var i = 0; i < itemNums.Count; i++)
            pars.Add(new SugarParameter($"@IN{i}", itemNums[i]));
        var inClause = string.Join(", ", Enumerable.Range(0, itemNums.Count).Select(i => $"@IN{i}"));

        var existing = await _db.Ado.SqlQueryAsync<string>(
            $"""
            SELECT DISTINCT ItemNum FROM ItemMaster
            WHERE tenant_id = @TenantId AND ItemNum IN ({inClause})
            """,
            pars);
        foreach (var e in existing)
            existingSet.Add(e);

        return new BomCheckResult
        {
            Existing = existingSet.ToList(),
            Missing = input.Items
                .Where(x => !string.IsNullOrWhiteSpace(x.ItemNum) && !existingSet.Contains(x.ItemNum.Trim()))
                .GroupBy(x => x.ItemNum.Trim())
                .Select(g => g.First())
                .ToList()
        };
    }

    // ══════════════════════════════════════════════════════════════
    // POST /api/WorkOrder/dispatch/bom/upload
    // ══════════════════════════════════════════════════════════════
    /// <summary>
    /// 写入 BOM：按（订单号+父物料号）匹配未下达工单，可选新建 ItemMaster 缺失物料，
    /// 然后 Upsert WorkOrdDetail。
    /// </summary>
    [DisplayName("BOM写入工单物料明细")]
    [ApiDescriptionSettings(Name = "BomUpload"), HttpPost("dispatch/bom/upload")]
    public async Task<BomUploadResult> Upload([FromBody] BomUploadInput input)
    {
        var tid = ResolveTenantId();
        if (input.Items == null || input.Items.Count == 0)
            throw Oops.Oh("BOM 物料列表为空");

        var account = _userManager.Account ?? "system";
        var factoryRefId = await ResolveFactoryRefIdAsync(tid);

        // 1) 查找哪些子物料需要新建（ItemMaster 存在性检查）
        var itemNums = input.Items
            .Where(x => !string.IsNullOrWhiteSpace(x.ItemNum))
            .Select(x => x.ItemNum.Trim())
            .Distinct()
            .ToList();

        var existingSet = new HashSet<string>(StringComparer.OrdinalIgnoreCase);
        if (itemNums.Count > 0)
        {
            var pars = new List<SugarParameter> { new("@TenantId", tid) };
            for (var i = 0; i < itemNums.Count; i++)
                pars.Add(new SugarParameter($"@IN{i}", itemNums[i]));
            var inClause = string.Join(", ", Enumerable.Range(0, itemNums.Count).Select(i => $"@IN{i}"));
            var existing = await _db.Ado.SqlQueryAsync<string>(
                $"SELECT DISTINCT ItemNum FROM ItemMaster WHERE tenant_id = @TenantId AND ItemNum IN ({inClause})",
                pars);
            foreach (var e in existing)
                existingSet.Add(e);
        }

        var newItemsCount = 0;

        // 2) 按（SalesOrderNo, ParentItemNum）分组 → 每组对应一个工单
        var groups = input.Items
            .Where(x => !string.IsNullOrWhiteSpace(x.ItemNum)
                        && !string.IsNullOrWhiteSpace(x.SalesOrderNo)
                        && !string.IsNullOrWhiteSpace(x.ParentItemNum))
            .GroupBy(x => (SalesOrderNo: x.SalesOrderNo!.Trim(), ParentItemNum: x.ParentItemNum!.Trim()))
            .ToList();

        // 没有有效分组 key 的行视为无法匹配
        var unmatched = new List<string>();
        var noKeyItems = input.Items
            .Where(x => !string.IsNullOrWhiteSpace(x.ItemNum)
                        && (string.IsNullOrWhiteSpace(x.SalesOrderNo) || string.IsNullOrWhiteSpace(x.ParentItemNum)))
            .ToList();
        if (noKeyItems.Count > 0)
            unmatched.Add($"缺少订单号或父物料号：{noKeyItems.Count} 行");

        // 3) 预匹配：在写入之前先将所有分组匹配到工单，有未匹配则整体中止
        var matchedGroups = new List<(IGrouping<(string SalesOrderNo, string ParentItemNum), BomItemDto> Grp, WoInfo Wo)>();
        foreach (var grp in groups)
        {
            var woRow = await _db.Ado.SqlQuerySingleAsync<WoInfo>(
                """
                SELECT RecID AS RecId, WorkOrd, `Domain`, LOWER(TRIM(IFNULL(Status,''))) AS Status
                FROM WorkOrdMaster
                WHERE tenant_id = @TenantId
                  AND TRIM(IFNULL(SalesJob,'')) = @SalesOrderNo
                  AND TRIM(IFNULL(ItemNum,'')) = @ParentItemNum
                  AND LOWER(TRIM(IFNULL(Status,''))) = 'p'
                LIMIT 1
                """,
                new SugarParameter("@TenantId", tid),
                new SugarParameter("@SalesOrderNo", grp.Key.SalesOrderNo),
                new SugarParameter("@ParentItemNum", grp.Key.ParentItemNum));

            if (woRow == null)
                unmatched.Add($"订单[{grp.Key.SalesOrderNo}] + 物料[{grp.Key.ParentItemNum}]");
            else
                matchedGroups.Add((grp, woRow));
        }

        // 有未匹配组 → 整体中止，不写入任何数据
        if (unmatched.Count > 0)
        {
            return new BomUploadResult
            {
                Inserted = 0, Updated = 0, NewItems = 0, Total = input.Items.Count,
                MatchedWorkOrders = 0, Unmatched = unmatched
            };
        }

        await _db.Ado.BeginTranAsync();
        try
        {
            // 4) 创建缺失物料到 ItemMaster
            if (input.CreateMissingItems)
            {
                var missingItems = input.Items
                    .Where(x => !string.IsNullOrWhiteSpace(x.ItemNum) && !existingSet.Contains(x.ItemNum.Trim()))
                    .GroupBy(x => x.ItemNum.Trim())
                    .Select(g => g.First())
                    .ToList();

                var now = DateTime.Now;
                // 获取一个默认 Domain（取第一个匹配工单的，或兜底 8010）
                var defaultDomain = "8010";
                foreach (var item in missingItems)
                {
                    var itemNum = item.ItemNum.Trim();
                    await _db.Ado.ExecuteCommandAsync(
                        """
                        INSERT IGNORE INTO ItemMaster (
                            tenant_id, ItemNum, Descr, Descr1, UM,
                            Status, IsActive, Domain, factory_ref_id,
                            CreateTime, CreateUser, UpdateTime, UpdateUser
                        ) VALUES (
                            @TenantId, @ItemNum, @Descr, @Descr1, @UM,
                            '', 1, @Domain, @FactoryRefId,
                            @Now, @User, @Now, @User
                        )
                        """,
                        new SugarParameter("@TenantId", tid),
                        new SugarParameter("@ItemNum", itemNum),
                        new SugarParameter("@Descr", item.Descr ?? string.Empty),
                        new SugarParameter("@Descr1", item.Descr1 ?? (object)DBNull.Value),
                        new SugarParameter("@UM", item.UM ?? (object)DBNull.Value),
                        new SugarParameter("@Domain", defaultDomain),
                        new SugarParameter("@FactoryRefId", factoryRefId),
                        new SugarParameter("@Now", now),
                        new SugarParameter("@User", account));
                    newItemsCount++;
                    existingSet.Add(itemNum);
                }
            }

            // 5) 逐组写入 WorkOrdDetail（已预匹配，无需再查）
            var inserted = 0;
            var updated = 0;
            var matchedWorkOrders = 0;

            foreach (var (grp, woRow) in matchedGroups)
            {
                var domain = string.IsNullOrWhiteSpace(woRow.Domain) ? "8010" : woRow.Domain;

                // 加载该工单已有的 WorkOrdDetail
                var existingDetails = await _db.Ado.SqlQueryAsync<ExistingDetailRow>(
                    """
                    SELECT RecID, ItemNum, QtyRequired
                    FROM WorkOrdDetail
                    WHERE tenant_id = @TenantId AND WorkOrd = @WorkOrd AND IFNULL(IsActive, 0) = 1
                    """,
                    new SugarParameter("@TenantId", tid),
                    new SugarParameter("@WorkOrd", woRow.WorkOrd));
                var existingMap = existingDetails.ToDictionary(x => x.ItemNum, x => x, StringComparer.OrdinalIgnoreCase);

                var line = existingDetails.Count + 1;
                var now2 = DateTime.Now;

                // 按子物料 ItemNum 去重（同一物料累加需求数量）
                var groupedItems = grp
                    .GroupBy(x => x.ItemNum.Trim())
                    .Select(g => new
                    {
                        ItemNum = g.Key,
                        Descr = g.First().Descr,
                        Descr1 = g.First().Descr1,
                        UM = g.First().UM,
                        QtyRequired = g.Sum(x => x.QtyRequired),
                        Op = g.First().Op
                    })
                    .ToList();

                foreach (var item in groupedItems)
                {
                    var itemNum = item.ItemNum.Trim();
                    if (existingMap.TryGetValue(itemNum, out var ex))
                    {
                        // 已有 → UPDATE QtyRequired
                        await _db.Ado.ExecuteCommandAsync(
                            """
                            UPDATE WorkOrdDetail
                            SET QtyRequired = @QtyRequired, UpdateUser = @User, UpdateTime = @Now
                            WHERE RecID = @RecID AND tenant_id = @TenantId
                            """,
                            new SugarParameter("@QtyRequired", item.QtyRequired),
                            new SugarParameter("@User", account),
                            new SugarParameter("@Now", now2),
                            new SugarParameter("@RecID", ex.RecID),
                            new SugarParameter("@TenantId", tid));
                        updated++;
                    }
                    else
                    {
                        // 新增 → INSERT
                        await _db.Ado.ExecuteCommandAsync(
                            """
                            INSERT INTO WorkOrdDetail (
                                WorkOrdMasterRecID, `Domain`, WorkOrd, LineNum, ItemNum, Op,
                                Location, QtyRequired, QtyPosted, UM, IsActive, IsConfirm,
                                CreateUser, CreateTime, UpdateUser, UpdateTime, tenant_id
                            ) VALUES (
                                @MasterRecId, @Domain, @WorkOrd, @LineNum, @ItemNum, @Op,
                                @Location, @QtyRequired, 0, @UM, 1, 0,
                                @User, @Now, @User, @Now, @TenantId
                            )
                            """,
                            new SugarParameter("@MasterRecId", woRow.RecId),
                            new SugarParameter("@Domain", domain),
                            new SugarParameter("@WorkOrd", woRow.WorkOrd),
                            new SugarParameter("@LineNum", line),
                            new SugarParameter("@ItemNum", itemNum),
                            new SugarParameter("@Op", item.Op),
                            new SugarParameter("@Location", DBNull.Value),
                            new SugarParameter("@QtyRequired", item.QtyRequired),
                            new SugarParameter("@UM", item.UM ?? (object)DBNull.Value),
                            new SugarParameter("@User", account),
                            new SugarParameter("@Now", now2),
                            new SugarParameter("@TenantId", tid));
                        inserted++;
                        line++;
                    }
                }

                matchedWorkOrders++;
                _logger.LogInformation(
                    "[BomUpload] workOrd={WorkOrd} salesOrderNo={SalesOrderNo} parentItem={ParentItem} items={Items}",
                    woRow.WorkOrd, grp.Key.SalesOrderNo, grp.Key.ParentItemNum, groupedItems.Count);
            }

            await _db.Ado.CommitTranAsync();

            _logger.LogInformation(
                "[BomUpload] tenantId={TenantId} matched={Matched} inserted={Inserted} updated={Updated} newItems={NewItems} unmatched={Unmatched}",
                tid, matchedWorkOrders, inserted, updated, newItemsCount, unmatched.Count);

            return new BomUploadResult
            {
                Inserted = inserted,
                Updated = updated,
                NewItems = newItemsCount,
                Total = input.Items.Count,
                MatchedWorkOrders = matchedWorkOrders,
                Unmatched = unmatched
            };
        }
        catch
        {
            await _db.Ado.RollbackTranAsync();
            throw;
        }
    }

    /// <summary>获取租户的 factory_ref_id（取 ItemMaster 中已有的最小 factory_ref_id，缺省 1）。</summary>
    private async Task<long> ResolveFactoryRefIdAsync(long tenantId)
    {
        var val = await _db.Ado.GetIntAsync(
            """
            SELECT IFNULL(MIN(factory_ref_id), 1) FROM ItemMaster
            WHERE tenant_id = @TenantId AND factory_ref_id > 0
            LIMIT 1
            """,
            new SugarParameter("@TenantId", tenantId));
        return val <= 0 ? 1 : val;
    }

    private sealed class WoInfo
    {
        public long RecId { get; set; }
        public string WorkOrd { get; set; } = string.Empty;
        public string? Domain { get; set; }
        public string? Status { get; set; }
    }

    private sealed class ExistingDetailRow
    {
        public long RecID { get; set; }
        public string ItemNum { get; set; } = string.Empty;
        public decimal QtyRequired { get; set; }
    }
}