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

using Admin.NET.Plugin.AiDOP.Supply;
using Yitter.IdGenerator;

namespace Admin.NET.Plugin.AiDOP.WorkOrder;

/// <summary>
/// 本库领料单生成（NbrMaster / NbrDetail，Type=SM）。WP9 Local 路径 / LocalPickBillCreator。
/// </summary>
public class WorkOrderPickBillService : ITransient
{
    private readonly ISqlSugarClient _db;
    private readonly NumberRuleService _numberRule;
    private readonly WorkOrderMaterialDetailSyncService _detailSync;

    public WorkOrderPickBillService(
        ISqlSugarClient db,
        NumberRuleService numberRule,
        WorkOrderMaterialDetailSyncService detailSync)
    {
        _db = db;
        _numberRule = numberRule;
        _detailSync = detailSync;
    }

    /// <summary>兼容旧调用；等价于 CreateAsync（domain 由工单推导）。</summary>
    public Task<PickBillResult> CreateForWorkOrderAsync(long tenantId, string workOrd, string account)
        => CreateAsync(tenantId, domain: "", workOrd, account);

    public async Task<PickBillResult> CreateAsync(
        long tenantId,
        string domain,
        string workOrd,
        string account,
        CancellationToken ct = default)
    {
        workOrd = workOrd.Trim();
        var existing = await FindExistingPickBillAsync(tenantId, workOrd);
        if (!string.IsNullOrWhiteSpace(existing))
            return new PickBillResult { Nbr = existing, Created = false, Message = "领料单已存在", Source = "Local" };

        var wo = await LoadWorkOrderAsync(tenantId, workOrd)
            ?? throw Oops.Oh($"工单 {workOrd} 不存在");

        await _detailSync.EnsureFromResourceCheckAsync(tenantId, workOrd, account);

        var details = await LoadPickDetailsAsync(tenantId, workOrd);
        if (details.Count == 0)
            throw Oops.Oh($"工单 {workOrd} 无物料明细，请先完成订单评审资源检查");

        var resolvedDomain = !string.IsNullOrWhiteSpace(domain)
            ? domain.Trim()
            : (string.IsNullOrWhiteSpace(wo.Domain) ? tenantId.ToString() : wo.Domain.Trim());
        var lineTo = await ResolveLineSideLocationAsync(tenantId, wo.ItemNum);
        var now = DateTime.Now;
        var effDate = wo.OrdDate?.Date ?? now.Date;
        var pickDate = effDate < now.Date ? now.Date : effDate;

        string nbrNo;
        var remark = "下达自动领料";
        var source = "Local";
        try
        {
            var numbers = await _numberRule.NextBatchInCurrentTransactionAsync("SM", resolvedDomain, 1, account);
            nbrNo = numbers.FirstOrDefault() ?? throw Oops.Oh("领料单号生成失败");
        }
        catch
        {
            // WP9 S2：降级号与 165 号段可区分
            nbrNo = "SMX" + now.ToString("yyyyMMddHHmmss") + YitIdHelper.NextId().ToString()[^5..];
            remark = "本地降级";
            source = "LocalFallback";
        }

        var masterRecId = await NextRecIdAsync("NbrMaster");
        await _db.Ado.ExecuteCommandAsync(
            """
            INSERT INTO NbrMaster (
                RecID, `Domain`, Type, Nbr, Status, Remark, Date, EffDate,
                WorkOrd, QtyOrd, QtyRec, ProdLine, Department, Name,
                TransType, IsActive, IsChanged, IsConfirm, BusinessID,
                CreateUser, CreateTime, UpdateUser, UpdateTime, tenant_id
            ) VALUES (
                @RecId, @Domain, 'SM', @Nbr, '', @Remark, @PickDate, @EffDate,
                @WorkOrd, @QtyOrd, 0, @ProdLine, '101', @Name,
                '', 1, 1, 0, 0,
                @User, @Now, @User, @Now, @TenantId
            )
            """,
            new SugarParameter("@RecId", masterRecId),
            new SugarParameter("@Domain", resolvedDomain.Length > 8 ? resolvedDomain[..8] : resolvedDomain),
            new SugarParameter("@Nbr", nbrNo),
            new SugarParameter("@Remark", remark),
            new SugarParameter("@PickDate", pickDate),
            new SugarParameter("@EffDate", effDate),
            new SugarParameter("@WorkOrd", workOrd),
            new SugarParameter("@QtyOrd", wo.QtyOrded ?? 0),
            new SugarParameter("@ProdLine", wo.ProdLine ?? (object)DBNull.Value),
            new SugarParameter("@Name", account.Length > 12 ? account[..12] : account),
            new SugarParameter("@User", account.Length > 24 ? account[..24] : account),
            new SugarParameter("@Now", now),
            new SugarParameter("@TenantId", tenantId));

        short line = 1;
        foreach (var d in details)
        {
            var detailRecId = await NextRecIdAsync("NbrDetail");
            await _db.Ado.ExecuteCommandAsync(
                """
                INSERT INTO NbrDetail (
                    RecID, `Domain`, Type, Nbr, Line, ItemNum, Dimension1, Dimension2,
                    LocationFrom, LocationTo, QtyFrom, QtyTo, QtyOrd, QtyRec,
                    CurrQtyOpened, UM, WorkOrd, ItemName, Status,
                    IsActive, IsConfirm, IsChanged, BusinessID, NbrRecID,
                    CreateUser, CreateTime, UpdateUser, UpdateTime, tenant_id
                ) VALUES (
                    @RecId, @Domain, 'SM', @Nbr, @Line, @ItemNum, '', '',
                    @LocationFrom, @LocationTo, 0, 0, @QtyOrd, 0,
                    @CurrQtyOpened, @UM, @WorkOrd, @ItemName, '',
                    1, 0, 1, 0, @NbrRecId,
                    @User, @Now, @User, @Now, @TenantId
                )
                """,
                new SugarParameter("@RecId", detailRecId),
                new SugarParameter("@Domain", resolvedDomain.Length > 8 ? resolvedDomain[..8] : resolvedDomain),
                new SugarParameter("@Nbr", nbrNo),
                new SugarParameter("@Line", line),
                new SugarParameter("@ItemNum", d.ItemNum),
                new SugarParameter("@LocationFrom", d.LocationFrom ?? (object)DBNull.Value),
                new SugarParameter("@LocationTo", lineTo ?? (object)DBNull.Value),
                new SugarParameter("@QtyOrd", d.QtyRequired),
                new SugarParameter("@CurrQtyOpened", d.QtyRequired),
                new SugarParameter("@UM", d.Unit ?? (object)DBNull.Value),
                new SugarParameter("@WorkOrd", workOrd),
                new SugarParameter("@ItemName", d.ItemName ?? (object)DBNull.Value),
                new SugarParameter("@NbrRecId", masterRecId),
                new SugarParameter("@User", account.Length > 24 ? account[..24] : account),
                new SugarParameter("@Now", now),
                new SugarParameter("@TenantId", tenantId));
            line++;
        }

        await _db.Ado.ExecuteCommandAsync(
            """
            UPDATE mes_morder
            SET morder_state = '下达',
                MaterialSituation = CASE
                    WHEN MaterialSituation = '缺料' THEN '未备料'
                    ELSE IFNULL(MaterialSituation, '齐套')
                END,
                update_by_name = @User,
                update_time = @Now
            WHERE tenant_id = @TenantId AND morder_no = @WorkOrd AND IsDeleted = 0
            """,
            new SugarParameter("@User", account),
            new SugarParameter("@Now", now),
            new SugarParameter("@TenantId", tenantId),
            new SugarParameter("@WorkOrd", workOrd));

        return new PickBillResult
        {
            Nbr = nbrNo,
            Created = true,
            LineCount = details.Count,
            Message = source == "LocalFallback" ? "领料单已生成本地降级号" : "领料单已生成",
            Source = source
        };
    }

    private async Task<string?> FindExistingPickBillAsync(long tenantId, string workOrd)
    {
        return await _db.Ado.GetStringAsync(
            """
            SELECT Nbr FROM NbrMaster
            WHERE tenant_id = @TenantId AND WorkOrd = @WorkOrd AND Type = 'SM'
              AND IFNULL(TransType, '') = ''
              AND IFNULL(IsActive, 1) = 1
            ORDER BY RecID DESC LIMIT 1
            """,
            new List<SugarParameter>
            {
                new("@TenantId", tenantId),
                new("@WorkOrd", workOrd)
            });
    }

    private async Task<WorkOrderPickRow?> LoadWorkOrderAsync(long tenantId, string workOrd)
    {
        var rows = await _db.Ado.SqlQueryAsync<WorkOrderPickRow>(
            """
            SELECT
                w.RecID AS RecId,
                w.`Domain`,
                w.ItemNum,
                w.QtyOrded,
                w.OrdDate,
                (
                    SELECT LEFT(IFNULL(r.ProdLine, ''), 8)
                    FROM WorkOrdRouting r
                    WHERE r.WorkOrd = w.WorkOrd AND r.tenant_id = w.tenant_id AND IFNULL(r.IsActive, 0) = 1
                    ORDER BY (r.OP + 0) LIMIT 1
                ) AS ProdLine
            FROM WorkOrdMaster w
            WHERE w.tenant_id = @TenantId AND w.WorkOrd = @WorkOrd
            LIMIT 1
            """,
            new SugarParameter("@TenantId", tenantId),
            new SugarParameter("@WorkOrd", workOrd));
        return rows.FirstOrDefault();
    }

    private async Task<List<PickDetailRow>> LoadPickDetailsAsync(long tenantId, string workOrd)
    {
        return await _db.Ado.SqlQueryAsync<PickDetailRow>(
            """
            SELECT
                d.ItemNum,
                SUM(d.QtyRequired) AS QtyRequired,
                MAX(IFNULL(d.Location, im.Location)) AS LocationFrom,
                MAX(IFNULL(d.UM, im.Um)) AS Unit,
                MAX(im.Descr) AS ItemName
            FROM WorkOrdDetail d
            LEFT JOIN ItemMaster im ON d.ItemNum = im.ItemNum
            WHERE d.tenant_id = @TenantId AND d.WorkOrd = @WorkOrd AND IFNULL(d.IsActive, 0) = 1
            GROUP BY d.ItemNum
            HAVING SUM(d.QtyRequired) > 0
            ORDER BY d.ItemNum
            """,
            new SugarParameter("@TenantId", tenantId),
            new SugarParameter("@WorkOrd", workOrd));
    }

    private async Task<string?> ResolveLineSideLocationAsync(long tenantId, string? itemNum)
    {
        if (string.IsNullOrWhiteSpace(itemNum))
            return null;

        var loc = await _db.Ado.GetStringAsync(
            """
            SELECT LEFT(IFNULL(lm.Location, lm.PickingLocation), 8)
            FROM ProdLineDetail pld
            INNER JOIN LineMaster lm ON pld.Line = lm.Line AND lm.tenant_id = pld.tenant_id
            WHERE pld.Part = @ItemNum AND pld.tenant_id = @TenantId AND IFNULL(pld.IsActive, 1) = 1
            ORDER BY IFNULL(pld.Sequence, 0)
            LIMIT 1
            """,
            new List<SugarParameter>
            {
                new("@ItemNum", itemNum.Trim()),
                new("@TenantId", tenantId)
            });

        return string.IsNullOrWhiteSpace(loc) ? null : loc;
    }

    private async Task<int> NextRecIdAsync(string table)
    {
        return await _db.Ado.GetIntAsync($"SELECT IFNULL(MAX(RecID), 0) + 1 FROM {table}");
    }

    private sealed class WorkOrderPickRow
    {
        public long RecId { get; set; }
        public string? Domain { get; set; }
        public string? ItemNum { get; set; }
        public decimal? QtyOrded { get; set; }
        public DateTime? OrdDate { get; set; }
        public string? ProdLine { get; set; }
    }

    private sealed class PickDetailRow
    {
        public string ItemNum { get; set; } = string.Empty;
        public decimal QtyRequired { get; set; }
        public string? LocationFrom { get; set; }
        public string? Unit { get; set; }
        public string? ItemName { get; set; }
    }
}