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

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

namespace Admin.NET.Plugin.AiDOP.WorkOrder;

/// <summary>
/// 批量采购确认服务 — 列出初始(p)状态工单中未匹配到供应商的物料明细，
/// 确认后把（物料 → 采购员 → 供应商）关系写入货源清单基础数据 srm_purchase。
/// 路由前缀：/api/WorkOrder/purchase-confirm/...
///
/// 【未匹配口径】物料号在 srm_purchase 中不存在「未删除且有供应商编码」的记录。
/// 【写入约定】供应商编码/ID 从 SuppMaster 按名称回填（查不到编码为空）；
/// 物料 ID/工厂/公司从 ItemMaster 回填；同一（物料号+供应商）已存在则更新，否则新增。
///
/// 【租户安全边界】需认证访问；租户一律经
/// <c>AidopTenantScope.ResolveOrThrow</c> 从认证后 JWT 解析。
/// </summary>
[ApiDescriptionSettings(Order = 259, Description = "批量采购确认")]
[Route("api/WorkOrder")]
[NonUnify]
public class WorkOrderPurchaseConfirmService : IDynamicApiController, ITransient
{
    private readonly ISqlSugarClient _db;
    private readonly UserManager _userManager;
    private readonly ILogger<WorkOrderPurchaseConfirmService> _logger;

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

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

    // ══════════════════════════════════════════════════════════════
    // GET /api/WorkOrder/purchase-confirm/unmatched-list
    // ══════════════════════════════════════════════════════════════
    /// <summary>
    /// 获取未匹配到供应商的工单物料明细（仅初始 p 状态工单；
    /// 物料号在 srm_purchase 中无「未删除且有供应商编码」的有效货源记录）。
    /// </summary>
    [DisplayName("批量采购确认未匹配列表")]
    [HttpGet("purchase-confirm/unmatched-list")]
    public async Task<object> GetUnmatchedList()
    {
        var tid = ResolveTenantId();

        var list = await _db.Ado.SqlQueryAsync<UnmatchedPurchaseRow>(
            """
            SELECT
                wd.RecID            AS DetailRecId,
                wm.WorkOrd,
                wm.SalesJob         AS SalesOrderNo,
                co.bill_from        AS ContractNo,
                wm.ItemNum          AS ParentItemNum,
                wm.ItemName         AS ParentDescr,
                wm.Domain           AS Factory,
                wd.ItemNum,
                im.Descr,
                im.Descr1,
                wd.QtyRequired,
                wd.UM               AS Um,
                CAST(wd.Op AS CHAR(20)) AS ProjectText,
                IFNULL(TRIM(im.Buyer), '') AS Purchaser
            FROM WorkOrdDetail wd
            JOIN WorkOrdMaster wm
                ON wm.WorkOrd = wd.WorkOrd AND wm.tenant_id = wd.tenant_id
            LEFT JOIN ItemMaster im
                ON im.ItemNum = wd.ItemNum AND (im.tenant_id = wd.tenant_id OR im.tenant_id IS NULL)
            LEFT JOIN crm_seorder co
                ON co.Id = wm.BusinessID AND co.tenant_id = wm.tenant_id
            LEFT JOIN srm_purchase sp
                ON sp.number = wd.ItemNum AND sp.tenant_id = wd.tenant_id
                AND IFNULL(sp.IsDeleted, 0) = 0
                AND IFNULL(TRIM(sp.supplier_number), '') <> ''
            WHERE wd.tenant_id = @TenantId
              AND IFNULL(wd.IsActive, 0) = 1
              AND IFNULL(TRIM(wd.ItemNum), '') <> ''
              AND LOWER(TRIM(IFNULL(wm.Status, ''))) = 'p'
              AND sp.Id IS NULL
            ORDER BY wm.WorkOrd, wd.LineNum
            """,
            new SugarParameter("@TenantId", tid));

        return new { total = list.Count, list };
    }

    // ══════════════════════════════════════════════════════════════
    // POST /api/WorkOrder/purchase-confirm/confirm
    // ══════════════════════════════════════════════════════════════
    /// <summary>
    /// 批量确认：把（物料 → 采购员 → 供应商）关系写入货源清单 srm_purchase。
    /// 供应商为空的行跳过；按（物料号+供应商）去重合并。
    /// </summary>
    [DisplayName("批量采购确认写入货源清单")]
    [ApiDescriptionSettings(Name = "PurchaseConfirm"), HttpPost("purchase-confirm/confirm")]
    public async Task<PurchaseConfirmResult> Confirm([FromBody] PurchaseConfirmInput input)
    {
        var tid = ResolveTenantId();
        if (input.Items == null || input.Items.Count == 0)
            throw Oops.Oh("确认列表为空");

        var account = _userManager.Account ?? "system";
        var now = DateTime.Now;

        // 1) 供应商为空的行跳过；其余按（物料号+供应商）去重合并
        var groups = input.Items
            .Where(x => !string.IsNullOrWhiteSpace(x.ItemNum) && !string.IsNullOrWhiteSpace(x.Supplier))
            .GroupBy(x => (ItemNum: x.ItemNum!.Trim(), Supplier: x.Supplier!.Trim()))
            .ToList();
        var skipped = input.Items.Count - input.Items.Count(x =>
            !string.IsNullOrWhiteSpace(x.ItemNum) && !string.IsNullOrWhiteSpace(x.Supplier));

        var inserted = 0;
        var updated = 0;

        await _db.Ado.BeginTranAsync();
        try
        {
            foreach (var g in groups)
            {
                var itemNum = g.Key.ItemNum;
                var supplierName = g.Key.Supplier;
                var first = g.First();
                var purchaser = first.Purchaser?.Trim();
                var unitPrice = g.Select(x => x.UnitPrice).FirstOrDefault(x => x.HasValue);

                // 物料主数据回填（ID/工厂/公司/名称/单位）
                var item = await _db.Ado.SqlQuerySingleAsync<ItemRefRow>(
                    """
                    SELECT RecID AS Id, company_ref_id AS CompanyRefId, factory_ref_id AS FactoryRefId,
                           Descr, UM
                    FROM ItemMaster
                    WHERE tenant_id = @TenantId AND TRIM(ItemNum) = @ItemNum
                    LIMIT 1
                    """,
                    new SugarParameter("@TenantId", tid),
                    new SugarParameter("@ItemNum", itemNum));
                var icitemId = item?.Id ?? 0;
                var companyRefId = item?.CompanyRefId ?? 0;
                var factoryRefId = item?.FactoryRefId ?? 0;
                var descr = first.Descr?.Trim() ?? item?.Descr ?? string.Empty;
                var um = first.Um?.Trim() ?? item?.UM ?? string.Empty;

                // 供应商主数据回填（ID/编码），按简称或编码匹配
                var supp = await _db.Ado.SqlQuerySingleAsync<SuppRefRow>(
                    """
                    SELECT RecID AS Id, Supp
                    FROM SuppMaster
                    WHERE tenant_id = @TenantId
                      AND (TRIM(IFNULL(SortName, '')) = @SupplierName OR TRIM(IFNULL(Supp, '')) = @SupplierName)
                    ORDER BY IFNULL(IsActive, 0) DESC
                    LIMIT 1
                    """,
                    new SugarParameter("@TenantId", tid),
                    new SugarParameter("@SupplierName", supplierName));
                var supplierId = supp?.Id ?? 0;
                var supplierNumber = supp?.Supp ?? string.Empty;

                // 已有货源记录？（同物料+同供应商）→ 更新，否则新增
                var existing = await _db.Ado.SqlQuerySingleAsync<ExistingSourceRow>(
                    """
                    SELECT Id, supplier_number AS SupplierNumber
                    FROM srm_purchase
                    WHERE tenant_id = @TenantId
                      AND TRIM(IFNULL(number, '')) = @ItemNum
                      AND TRIM(IFNULL(supplier_name, '')) = @SupplierName
                      AND IFNULL(IsDeleted, 0) = 0
                    LIMIT 1
                    """,
                    new SugarParameter("@TenantId", tid),
                    new SugarParameter("@ItemNum", itemNum),
                    new SugarParameter("@SupplierName", supplierName));

                if (existing != null)
                {
                    await _db.Ado.ExecuteCommandAsync(
                        """
                        UPDATE srm_purchase
                        SET order_rector_name = @Purchaser,
                            purcher           = @Purchaser,
                            order_price       = @UnitPrice,
                            supplier_number   = CASE
                                WHEN IFNULL(TRIM(supplier_number), '') = '' AND TRIM(@SupplierNumber) <> '' THEN @SupplierNumber
                                ELSE supplier_number
                            END,
                            supplier_id       = CASE
                                WHEN IFNULL(supplier_id, 0) = 0 AND @SupplierId > 0 THEN @SupplierId
                                ELSE supplier_id
                            END,
                            update_user       = @User,
                            update_time       = @Now
                        WHERE Id = @Id AND tenant_id = @TenantId
                        """,
                        new SugarParameter("@Purchaser", string.IsNullOrEmpty(purchaser) ? (object)DBNull.Value : purchaser),
                        new SugarParameter("@UnitPrice", unitPrice ?? (object)DBNull.Value),
                        new SugarParameter("@SupplierNumber", supplierNumber),
                        new SugarParameter("@SupplierId", supplierId),
                        new SugarParameter("@User", account),
                        new SugarParameter("@Now", now),
                        new SugarParameter("@Id", existing.Id),
                        new SugarParameter("@TenantId", tid));
                    updated++;
                }
                else
                {
                    await _db.Ado.ExecuteCommandAsync(
                        """
                        INSERT INTO srm_purchase (
                            company_ref_id, factory_ref_id, icitem_id, icitem_name,
                            is_active, supplier_id, supplier_name, supplier_number,
                            order_price, quota_rate, order_rector_name, purcher,
                            purchase_unit, IsRequireGoods, quota_priority,
                            create_user, create_time, update_user, update_time,
                            number, IsDeleted, tenant_id
                        ) VALUES (
                            @CompanyRefId, @FactoryRefId, @IcitemId, @IcitemName,
                            '是', @SupplierId, @SupplierName, @SupplierNumber,
                            @UnitPrice, 100, @Purchaser, @Purchaser,
                            @PurchaseUnit, 0, 0,
                            @User, @Now, @User, @Now,
                            @ItemNum, 0, @TenantId
                        )
                        """,
                        new SugarParameter("@CompanyRefId", companyRefId),
                        new SugarParameter("@FactoryRefId", factoryRefId),
                        new SugarParameter("@IcitemId", icitemId),
                        new SugarParameter("@IcitemName", string.IsNullOrEmpty(descr) ? (object)DBNull.Value : descr),
                        new SugarParameter("@SupplierId", supplierId),
                        new SugarParameter("@SupplierName", supplierName),
                        new SugarParameter("@SupplierNumber", string.IsNullOrEmpty(supplierNumber) ? string.Empty : supplierNumber),
                        new SugarParameter("@UnitPrice", unitPrice ?? (object)DBNull.Value),
                        new SugarParameter("@Purchaser", string.IsNullOrEmpty(purchaser) ? (object)DBNull.Value : purchaser),
                        new SugarParameter("@PurchaseUnit", string.IsNullOrEmpty(um) ? (object)DBNull.Value : um),
                        new SugarParameter("@User", account),
                        new SugarParameter("@Now", now),
                        new SugarParameter("@ItemNum", itemNum),
                        new SugarParameter("@TenantId", tid));
                    inserted++;
                }
            }

            await _db.Ado.CommitTranAsync();

            _logger.LogInformation(
                "[PurchaseConfirm] tenantId={TenantId} total={Total} inserted={Inserted} updated={Updated} skipped={Skipped}",
                tid, input.Items.Count, inserted, updated, skipped);

            return new PurchaseConfirmResult
            {
                Inserted = inserted,
                Updated = updated,
                Skipped = skipped,
                Total = input.Items.Count
            };
        }
        catch
        {
            await _db.Ado.RollbackTranAsync();
            throw;
        }
    }

    private sealed class ItemRefRow
    {
        public long Id { get; set; }
        public long CompanyRefId { get; set; }
        public long FactoryRefId { get; set; }
        public string? Descr { get; set; }
        public string? UM { get; set; }
    }

    private sealed class SuppRefRow
    {
        public long Id { get; set; }
        public string? Supp { get; set; }
    }

    private sealed class ExistingSourceRow
    {
        public long Id { get; set; }
        public string? SupplierNumber { get; set; }
    }
}