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

using System.Globalization;
using Yitter.IdGenerator;

namespace Admin.NET.Plugin.AiDOP.Order;

/// <summary>
/// 订单评审通过后生成/更新工单（WorkOrdMaster、mes_morder、mes_moentry）。
/// </summary>
public class OrderWorkOrderGenerationService : ITransient
{
    private readonly ISqlSugarClient _db;

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

    public sealed class OrderHeader
    {
        public long Id { get; set; }
        public string? BillNo { get; set; }
        public string? CustomNo { get; set; }
        public int? Urgent { get; set; }
        public long? FactoryId { get; set; }
        public long TenantId { get; set; }
    }

    public sealed class OrderEntryLine
    {
        public long Id { get; set; }
        public long SeOrderId { get; set; }
        public string? BillNo { get; set; }
        public int? EntrySeq { get; set; }
        public string? ItemNumber { get; set; }
        public string? ItemName { get; set; }
        public string? Specification { get; set; }
        public string? Unit { get; set; }
        public string? BomNumber { get; set; }
        public decimal? Qty { get; set; }
        public DateTime? PlanDate { get; set; }
        public DateTime? SysCapacityDate { get; set; }
        public string? Progress { get; set; }
        public int? Urgent { get; set; }
        public long? FactoryId { get; set; }
        public long? CompanyId { get; set; }
        public long TenantId { get; set; }
    }

    public sealed class WorkOrderUpsertResult
    {
        public string WorkOrd { get; set; } = string.Empty;
        public bool Created { get; set; }
        public List<string> Warnings { get; set; } = new();
    }

    public async Task<WorkOrderUpsertResult> CreateOrUpdateForEntryAsync(
        OrderHeader order,
        OrderEntryLine entry,
        string account,
        List<string> warnings)
    {
        var domain = await ResolveDomainAsync(order, entry);
        var dueDate = entry.SysCapacityDate ?? entry.PlanDate
            ?? throw Oops.Oh($"订单行 {entry.EntrySeq} 缺少计划交期，无法生成工单");
        var qty = entry.Qty ?? throw Oops.Oh($"订单行 {entry.EntrySeq} 数量无效");
        if (qty <= 0)
            throw Oops.Oh($"订单行 {entry.EntrySeq} 数量必须大于 0");

        var itemNum = entry.ItemNumber?.Trim()
            ?? throw Oops.Oh($"订单行 {entry.EntrySeq} 物料编码不能为空");

        if (entry.TenantId <= 0)
            throw Oops.Oh($"订单行 {entry.EntrySeq} 数据异常：缺少有效租户");

        var itemCnt = await _db.Ado.GetIntAsync(
            "SELECT COUNT(*) FROM ItemMaster WHERE ItemNum = @ItemNum AND tenant_id = @TenantId",
            new SugarParameter("@ItemNum", itemNum),
            new SugarParameter("@TenantId", entry.TenantId));
        if (itemCnt == 0)
            throw Oops.Oh($"物料 {itemNum} 不存在于 ItemMaster，订单行 {entry.EntrySeq} 无法评审");

        // 扣减已有成品库存，工单数量 = lack_qty（订单数量 - 在库数量）
        var stockQty = 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
            """,
            new SugarParameter("@ItemNum", itemNum),
            new SugarParameter("@TenantId", entry.TenantId));
        var lackQty = Math.Max(0m, qty - stockQty);
        if (stockQty > 0)
        {
            warnings.Add($"订单行 {entry.EntrySeq}（{itemNum}）成品库存 {stockQty}，工单数量 {qty} → {lackQty}");
        }
        if (lackQty > 0)
            qty = lackQty;

        if (string.IsNullOrWhiteSpace(entry.BomNumber))
            warnings.Add($"订单行 {entry.EntrySeq}（{itemNum}）无 BOM 编号，工单已生成但未展开物料明细");

        var urgent = entry.Urgent ?? order.Urgent ?? 0;
        var priority = urgent switch
        {
            2 => 20m,
            1 => 10m,
            _ => 0m
        };

        var existing = await FindOpenWorkOrderByEntryAsync(entry.TenantId, entry.Id);
        var now = DateTime.Now;
        var workOrd = existing?.WorkOrd ?? await GenerateWorkOrdNoAsync(entry.TenantId);
        var created = existing is null;

        if (existing is null)
        {
            await InsertWorkOrdMasterAsync(order, entry, workOrd, domain, itemNum, qty, dueDate, priority, urgent, account, now);
            var morderId = await InsertMesMorderAsync(order, entry, workOrd, domain, itemNum, qty, urgent, account, now);
            await InsertMesMoentryAsync(order, entry, workOrd, morderId, qty, account, now);
        }
        else
        {
            await UpdateWorkOrdMasterAsync(existing, entry, itemNum, qty, dueDate, priority, urgent, account, now);
            await UpdateMesMorderAsync(existing.WorkOrd, domain, entry, itemNum, qty, urgent, account, now);
        }

        return new WorkOrderUpsertResult
        {
            WorkOrd = workOrd,
            Created = created,
            Warnings = warnings
        };
    }

    /// <summary>
    /// WorkOrdMaster.Domain 为 varchar(8) 工厂编码（如 8010）。
    /// UAT 订单行 factory_id 可能误存租户 ID，不可直接 ToString 写入 Domain。
    /// </summary>
    private async Task<string> ResolveDomainAsync(OrderHeader order, OrderEntryLine entry)
    {
        var fid = entry.FactoryId ?? order.FactoryId;
        if (fid is > 0)
        {
            var fidText = fid.Value.ToString(CultureInfo.InvariantCulture);
            if (fidText.Length <= 8)
                return fidText;
        }

        var fallback = await _db.Ado.GetStringAsync(
            """
            SELECT `Domain` FROM WorkOrdMaster
            WHERE tenant_id = @TenantId
              AND `Domain` IS NOT NULL AND TRIM(`Domain`) <> ''
            GROUP BY `Domain`
            ORDER BY COUNT(*) DESC
            LIMIT 1
            """,
            new SugarParameter("@TenantId", entry.TenantId));
        if (!string.IsNullOrWhiteSpace(fallback))
            return fallback.Trim();

        return "8010";
    }

    private async Task<ExistingWorkOrderRow?> FindOpenWorkOrderByEntryAsync(long tenantId, long entryId)
    {
        var rows = await _db.Ado.SqlQueryAsync<ExistingWorkOrderRow>(
            """
            SELECT RecID AS RecId, WorkOrd, `Domain`, Status
            FROM WorkOrdMaster
            WHERE tenant_id = @TenantId
              AND BusinessID = @EntryId
              AND LOWER(TRIM(IFNULL(Status,''))) <> 'c'
            ORDER BY RecID DESC
            LIMIT 1
            """,
            new SugarParameter("@TenantId", tenantId),
            new SugarParameter("@EntryId", entryId));
        return rows.FirstOrDefault();
    }

    private async Task<string> GenerateWorkOrdNoAsync(long tenantId)
    {
        const string sql = """
            SELECT IFNULL(MAX(CAST(SUBSTRING(TRIM(WorkOrd), 3) AS UNSIGNED)), 0)
            FROM WorkOrdMaster
            WHERE tenant_id = @TenantId
              AND TRIM(WorkOrd) REGEXP '^M5[0-9]+$'
            """;
        var maxSeq = await _db.Ado.GetIntAsync(sql, new SugarParameter("@TenantId", tenantId));
        for (var attempt = 0; attempt < 5; attempt++)
        {
            var next = maxSeq + 1 + attempt;
            var candidate = "M5" + next.ToString("D8", CultureInfo.InvariantCulture);
            var dup = await _db.Ado.GetIntAsync(
                "SELECT COUNT(*) FROM WorkOrdMaster WHERE tenant_id = @TenantId AND WorkOrd = @WorkOrd",
                new SugarParameter("@TenantId", tenantId),
                new SugarParameter("@WorkOrd", candidate));
            if (dup == 0)
                return candidate;
        }

        throw Oops.Oh("生成工单号失败：流水号冲突，请重试");
    }

    private async Task InsertWorkOrdMasterAsync(
        OrderHeader order,
        OrderEntryLine entry,
        string workOrd,
        string domain,
        string itemNum,
        decimal qty,
        DateTime dueDate,
        decimal priority,
        int urgent,
        string account,
        DateTime now)
    {
        await _db.Ado.ExecuteCommandAsync(
            """
            INSERT INTO WorkOrdMaster (
                `Domain`, WorkOrd, ItemNum, ItemName, Status, Typed,
                QtyOrded, QtyCompleted, LbrVar, OrdDate, DueDate, Priority, Urgent,
                CustNo, SalesJob, BusinessID, CreateUser, CreateTime, UpdateUser, UpdateTime,
                IsActive, IsConfirm, tenant_id, Remark
            ) VALUES (
                @Domain, @WorkOrd, @ItemNum, @ItemName, '', 'M',
                @Qty, 0, 0, @OrdDate, @DueDate, @Priority, @Urgent,
                @CustNo, @SalesJob, @BusinessId, @User, @Now, @User, @Now,
                1, 0, @TenantId, @Remark
            )
            """,
            new SugarParameter("@Domain", domain),
            new SugarParameter("@WorkOrd", workOrd),
            new SugarParameter("@ItemNum", itemNum),
            new SugarParameter("@ItemName", entry.ItemName ?? string.Empty),
            new SugarParameter("@Qty", qty),
            new SugarParameter("@OrdDate", now.Date),
            new SugarParameter("@DueDate", dueDate.Date),
            new SugarParameter("@Priority", priority),
            new SugarParameter("@Urgent", urgent),
            new SugarParameter("@CustNo", order.CustomNo ?? (object)DBNull.Value),
            new SugarParameter("@SalesJob", order.BillNo ?? (object)DBNull.Value),
            new SugarParameter("@BusinessId", entry.Id),
            new SugarParameter("@User", account),
            new SugarParameter("@Now", now),
            new SugarParameter("@TenantId", entry.TenantId),
            new SugarParameter("@Remark", $"S1评审生成 订单行{entry.EntrySeq}"));
    }

    private async Task UpdateWorkOrdMasterAsync(
        ExistingWorkOrderRow existing,
        OrderEntryLine entry,
        string itemNum,
        decimal qty,
        DateTime dueDate,
        decimal priority,
        int urgent,
        string account,
        DateTime now)
    {
        await _db.Ado.ExecuteCommandAsync(
            """
            UPDATE WorkOrdMaster
            SET ItemNum = @ItemNum,
                ItemName = @ItemName,
                QtyOrded = @Qty,
                DueDate = @DueDate,
                Priority = @Priority,
                Urgent = @Urgent,
                UpdateUser = @User,
                UpdateTime = @Now
            WHERE RecID = @RecId
            """,
            new SugarParameter("@ItemNum", itemNum),
            new SugarParameter("@ItemName", entry.ItemName ?? string.Empty),
            new SugarParameter("@Qty", qty),
            new SugarParameter("@DueDate", dueDate.Date),
            new SugarParameter("@Priority", priority),
            new SugarParameter("@Urgent", urgent),
            new SugarParameter("@User", account),
            new SugarParameter("@Now", now),
            new SugarParameter("@RecId", existing.RecId));
    }

    private async Task<long> InsertMesMorderAsync(
        OrderHeader order,
        OrderEntryLine entry,
        string workOrd,
        string domain,
        string itemNum,
        decimal qty,
        int urgent,
        string account,
        DateTime now)
    {
        var factoryId = entry.FactoryId ?? order.FactoryId ?? entry.TenantId;
        var companyId = entry.CompanyId ?? factoryId;

        var morderId = YitIdHelper.NextId();
        await _db.Ado.ExecuteCommandAsync(
            """
            INSERT INTO mes_morder (
                Id, morder_no, morder_type, morder_date, morder_state,
                product_code, product_name, fmodel, bom_number, unit,
                need_number, morder_production_number, MaterialSituation, urgent,
                create_by_name, create_time, update_by_name, update_time,
                tenant_id, factory_id, company_id, IsDeleted
            ) VALUES (
                @Id, @MorderNo, '生产工单', @Now, '新建',
                @ProductCode, @ProductName, @Fmodel, @BomNumber, @Unit,
                @Qty, @Qty, '待检查', @Urgent,
                @User, @Now, @User, @Now,
                @TenantId, @FactoryId, @CompanyId, 0
            )
            """,
            new SugarParameter("@Id", morderId),
            new SugarParameter("@MorderNo", workOrd),
            new SugarParameter("@Now", now),
            new SugarParameter("@ProductCode", itemNum),
            new SugarParameter("@ProductName", entry.ItemName ?? string.Empty),
            new SugarParameter("@Fmodel", entry.Specification ?? (object)DBNull.Value),
            new SugarParameter("@BomNumber", entry.BomNumber ?? (object)DBNull.Value),
            new SugarParameter("@Unit", entry.Unit ?? (object)DBNull.Value),
            new SugarParameter("@Qty", qty),
            new SugarParameter("@Urgent", urgent),
            new SugarParameter("@User", account),
            new SugarParameter("@TenantId", entry.TenantId),
            new SugarParameter("@FactoryId", factoryId),
            new SugarParameter("@CompanyId", companyId));

        return morderId;
    }

    private async Task InsertMesMoentryAsync(
        OrderHeader order,
        OrderEntryLine entry,
        string workOrd,
        long morderId,
        decimal qty,
        string account,
        DateTime now)
    {
        var factoryId = entry.FactoryId ?? order.FactoryId ?? entry.TenantId;
        var companyId = entry.CompanyId ?? factoryId;

        var moentryId = YitIdHelper.NextId();
        await _db.Ado.ExecuteCommandAsync(
            """
            INSERT INTO mes_moentry (
                Id, moentry_moid, moentry_mono, soentry_id, fbill_no, unit,
                need_number, morder_production_number, remaining_number,
                create_by_name, create_time, update_by_name, update_time,
                tenant_id, factory_id, company_id, IsDeleted
            ) VALUES (
                @Id, @Moid, @Mono, @SoentryId, @BillNo, @Unit,
                @Qty, @Qty, @Qty,
                @User, @Now, @User, @Now,
                @TenantId, @FactoryId, @CompanyId, 0
            )
            """,
            new SugarParameter("@Id", moentryId),
            new SugarParameter("@Moid", morderId),
            new SugarParameter("@Mono", workOrd),
            new SugarParameter("@SoentryId", entry.Id),
            new SugarParameter("@BillNo", order.BillNo ?? entry.BillNo ?? string.Empty),
            new SugarParameter("@Unit", entry.Unit ?? (object)DBNull.Value),
            new SugarParameter("@Qty", qty),
            new SugarParameter("@User", account),
            new SugarParameter("@Now", now),
            new SugarParameter("@TenantId", entry.TenantId),
            new SugarParameter("@FactoryId", factoryId),
            new SugarParameter("@CompanyId", companyId));
    }

    private async Task UpdateMesMorderAsync(
        string workOrd,
        string domain,
        OrderEntryLine entry,
        string itemNum,
        decimal qty,
        int urgent,
        string account,
        DateTime now)
    {
        var factoryId = entry.FactoryId ?? entry.TenantId;
        await _db.Ado.ExecuteCommandAsync(
            """
            UPDATE mes_morder
            SET product_code = @ProductCode,
                product_name = @ProductName,
                fmodel = @Fmodel,
                bom_number = @BomNumber,
                unit = @Unit,
                need_number = @Qty,
                morder_production_number = @Qty,
                urgent = @Urgent,
                update_by_name = @User,
                update_time = @Now
            WHERE morder_no = @MorderNo
              AND tenant_id = @TenantId
              AND IsDeleted = 0
            """,
            new SugarParameter("@ProductCode", itemNum),
            new SugarParameter("@ProductName", entry.ItemName ?? string.Empty),
            new SugarParameter("@Fmodel", entry.Specification ?? (object)DBNull.Value),
            new SugarParameter("@BomNumber", entry.BomNumber ?? (object)DBNull.Value),
            new SugarParameter("@Unit", entry.Unit ?? (object)DBNull.Value),
            new SugarParameter("@Qty", qty),
            new SugarParameter("@Urgent", urgent),
            new SugarParameter("@User", account),
            new SugarParameter("@Now", now),
            new SugarParameter("@MorderNo", workOrd),
            new SugarParameter("@TenantId", entry.TenantId));
    }

    private sealed class ExistingWorkOrderRow
    {
        public long RecId { get; set; }
        public string? WorkOrd { get; set; }
        public string? Domain { get; set; }
        public string? Status { get; set; }
    }
}