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

using System.Globalization;
using Admin.NET.Plugin.AiDOP.Infrastructure;

namespace Admin.NET.Plugin.AiDOP.WorkOrder;

/// <summary>
/// 工单下达服务 🏭
/// 路由前缀：/api/WorkOrder/dispatch/...
/// </summary>
[ApiDescriptionSettings(Order = 260, Description = "工单下达")]
[Route("api/WorkOrder")]
[AllowAnonymous]
[NonUnify]
public class WorkOrderDispatchService : IDynamicApiController, ITransient
{
    private readonly ISqlSugarClient _db;
    private readonly UserManager _userManager;
    private readonly WorkOrderKittingCheckService _kittingCheck;
    private readonly WorkOrderPickBillService _pickBill;
    private readonly AidopActionRunLogWriter _runLog;

    public WorkOrderDispatchService(
        ISqlSugarClient db,
        UserManager userManager,
        WorkOrderKittingCheckService kittingCheck,
        WorkOrderPickBillService pickBill,
        AidopActionRunLogWriter runLog)
    {
        _db = db;
        _userManager = userManager;
        _kittingCheck = kittingCheck;
        _pickBill = pickBill;
        _runLog = runLog;
    }

    private long ResolveTenantId(long? requestTenantId) =>
        AidopTenantHelper.Resolve(App.HttpContext, requestTenantId);

    /// <summary>校验：本租户下存在处于初始状态(p)的该工单。</summary>
    private async Task AssertInitialWorkOrderExistsAsync(long tenantId, string workOrd)
    {
        var wo = workOrd.Trim();
        var cnt = await _db.Ado.GetIntAsync(
            """
            SELECT COUNT(*) FROM WorkOrdMaster
            WHERE tenant_id = @TenantId
              AND WorkOrd = @WorkOrd
              AND LOWER(TRIM(IFNULL(Status,''))) = 'p'
            """,
            new SugarParameter("@TenantId", tenantId),
            new SugarParameter("@WorkOrd", wo));
        if (cnt == 0)
            throw Oops.Oh("未找到处于初始状态(p)的本租户工单，请确认工单号与租户是否正确");
    }

    // ══════════════════════════════════════════════════════════════
    // 列表 GET /api/WorkOrder/dispatch/list
    // ══════════════════════════════════════════════════════════════
    /// <summary>获取工单下达分页列表 🏭</summary>
    [DisplayName("获取工单下达列表")]
    [HttpGet("dispatch/list")]
    public async Task<object> GetDispatchList([FromQuery] WorkOrderDispatchListInput input)
    {
        var tid = AidopTenantHelper.Resolve(App.HttpContext);
        var pars = new List<SugarParameter> { new SugarParameter("@TenantId", tid) };
        // 与库内其它原生 SQL 一致：统一 COLLATE，避免 utf8mb4_general_ci / utf8mb4_0900_ai_ci 混用报错
        const string C = "utf8mb4_general_ci";
        var conditions = new List<string> { $"a.tenant_id = @TenantId", $"a.Status COLLATE {C} = 'p'" };

        if (!string.IsNullOrWhiteSpace(input.WorkOrd))
        {
            conditions.Add($"(a.WorkOrd COLLATE {C}) LIKE (@WorkOrd COLLATE {C})");
            pars.Add(new SugarParameter("@WorkOrd", $"%{input.WorkOrd.Trim()}%"));
        }
        if (!string.IsNullOrWhiteSpace(input.ItemNum))
        {
            conditions.Add($"(a.ItemNum COLLATE {C}) LIKE (@ItemNum COLLATE {C})");
            pars.Add(new SugarParameter("@ItemNum", $"%{input.ItemNum.Trim()}%"));
        }
        if (!string.IsNullOrWhiteSpace(input.Descr))
        {
            conditions.Add($"(b.Descr COLLATE {C}) LIKE (@Descr COLLATE {C})");
            pars.Add(new SugarParameter("@Descr", $"%{input.Descr.Trim()}%"));
        }
        if (!string.IsNullOrWhiteSpace(input.OrdDateFrom))
        {
            conditions.Add("a.OrdDate >= @OrdDateFrom");
            pars.Add(new SugarParameter("@OrdDateFrom", input.OrdDateFrom.Trim()));
        }

        var whereClause = "WHERE " + string.Join(" AND ", conditions);

        // mes_morder 与工单的关联：历史上用 factory_id=Domain 直连，但部分库 factory_id 存租户/组织数值而 Domain 为工厂编码，
        // 会导致 JOIN 匹配不到、齐套列为空。改为按工单号取齐套，优先 factory_id 与 Domain 文本一致的那条。
        var baseSql = $"""
            SELECT
                a.RecID           AS Id,
                a.Domain,
                a.WorkOrd,
                a.Priority,
                a.ItemNum,
                b.Descr,
                b.Descr1,
                a.QtyOrded,
                m.MaterialSituation,
                a.OrdDate,
                a.DueDate,
                LOWER(a.Status)   AS Status,
                a.LotSerial,
                IFNULL(nm.Nbr,'')  AS PrevNbr,
                IFNULL(nm1.Nbr,'') AS Nbr,
                CONCAT(IFNULL(b.ItemNum,''), IFNULL(b.Descr,''), IFNULL(b.Descr1,'')) AS Wl
            FROM WorkOrdMaster a
            LEFT JOIN ItemMaster b ON a.ItemNum = b.ItemNum AND (a.tenant_id = b.tenant_id OR a.tenant_id IS NULL)
            LEFT JOIN mes_morder m ON a.WorkOrd = m.morder_no
            LEFT JOIN NbrMaster nm ON a.WorkOrd= nm.WorkOrd
                AND nm.Type = 'SM' AND nm.TransType = 'PrevProcess'
            LEFT JOIN NbrMaster nm1 ON a.WorkOrd = nm1.WorkOrd
                AND nm1.Type = 'SM' AND nm1.TransType = ''
            {whereClause}
            """;

        var offset = (input.Page - 1) * input.PageSize;
        var total = await _db.Ado.GetIntAsync(
            $"SELECT COUNT(*) FROM ({baseSql}) AS t", pars);
        var list = await _db.Ado.SqlQueryAsync<WorkOrderDispatchRow>(
            $"SELECT * FROM ({baseSql}) AS t ORDER BY t.Id DESC LIMIT {input.PageSize} OFFSET {offset}",
            pars);

        return new { total, page = input.Page, pageSize = input.PageSize, list };
    }

    // ══════════════════════════════════════════════════════════════
    // 下一生产批号 GET /api/WorkOrder/dispatch/next-lot-serial
    // 规则：以服务器「当前日期」的 yyMMdd + 三位流水；同一 tenant_id 下按已占用流水递增，避免重复。
    // 须存在：tenant_id + 工单号 + 状态 p。
    // ══════════════════════════════════════════════════════════════
    /// <summary>获取下一可用生产批号（当前日期 + 三位流水）🏭</summary>
    [DisplayName("获取下一生产批号")]
    [HttpGet("dispatch/next-lot-serial")]
    public async Task<object> GetNextLotSerial([FromQuery] long? tenantId, [FromQuery] string workOrd)
    {
        var tid = ResolveTenantId(tenantId);
        if (string.IsNullOrWhiteSpace(workOrd))
            throw Oops.Oh("工单编号不能为空");

        await AssertInitialWorkOrderExistsAsync(tid, workOrd);

        var prefix = DateTime.Today.ToString("yyMMdd", CultureInfo.InvariantCulture);

        var maxSql = """
            SELECT IFNULL(MAX(CAST(RIGHT(TRIM(w.LotSerial), 3) AS UNSIGNED)), 0)
            FROM WorkOrdMaster w
            WHERE w.tenant_id = @TenantId
              AND TRIM(w.LotSerial) REGEXP '^[0-9]{9}$'
              AND LEFT(TRIM(w.LotSerial), 6) = @Prefix
            """;

        var maxSeq = await _db.Ado.GetIntAsync(maxSql,
            new SugarParameter("@TenantId", tid),
            new SugarParameter("@Prefix", prefix));
        var next = maxSeq + 1;
        if (next > 999)
            throw Oops.Oh($"当日生产批号流水已超过 999（前缀 {prefix}），请联系管理员");

        var lotSerial = prefix + next.ToString("D3", CultureInfo.InvariantCulture);
        return new { lotSerial };
    }

    // ══════════════════════════════════════════════════════════════
    // 工单下达 POST /api/WorkOrder/dispatch/release
    // ══════════════════════════════════════════════════════════════
    /// <summary>工单下达（更新开工日期、生产批号，状态改为 r）。若原开工日、新开工日、原完工日均非空，则完工日按日历同天数平移。</summary>
    [DisplayName("工单下达")]
    [ApiDescriptionSettings(Name = "ReleaseWorkOrder"), HttpPost("dispatch/release")]
    public async Task<object> ReleaseWorkOrder([FromBody] WorkOrderReleaseInput input)
    {
        var tid = ResolveTenantId(input.TenantId > 0 ? input.TenantId : null);
        var wo = input.WorkOrd.Trim();
        var logId = await _runLog.StartAsync("S2_WORK_ORDER_RELEASE", tid, "WorkOrdMaster", null, wo);

        try
        {
            await AssertInitialWorkOrderExistsAsync(tid, wo);

            // ── 下达前置校验 ──
            // 1. 生产批号必须有值
            if (string.IsNullOrWhiteSpace(input.LotSerial))
                throw Oops.Oh("工单下达失败：生产批号不能为空，请先获取或输入生产批号");

            // 2. 工单物料明细至少一条
            var detailCount = await _db.Ado.GetIntAsync(
                """
                SELECT COUNT(*) FROM WorkOrdDetail
                WHERE tenant_id = @TenantId AND WorkOrd = @WorkOrd
                """,
                new SugarParameter("@TenantId", tid),
                new SugarParameter("@WorkOrd", wo));
            if (detailCount == 0)
                throw Oops.Oh("工单下达失败：工单物料明细为空，请先同步物料明细");

            // 3. 工单工序明细至少一条
            var routingCount = await _db.Ado.GetIntAsync(
                """
                SELECT COUNT(*) FROM WorkOrdRouting
                WHERE tenant_id = @TenantId AND WorkOrd = @WorkOrd
                """,
                new SugarParameter("@TenantId", tid),
                new SugarParameter("@WorkOrd", wo));
            if (routingCount == 0)
                throw Oops.Oh("工单下达失败：工单工序明细为空，请先同步工艺路线");

            if (!string.IsNullOrWhiteSpace(input.LotSerial))
            {
                var ls = input.LotSerial.Trim();
                var dup = await _db.Ado.GetIntAsync(
                    """
                    SELECT COUNT(*) FROM WorkOrdMaster
                    WHERE tenant_id = @TenantId
                      AND TRIM(IFNULL(LotSerial,'')) = @LotSerial
                      AND WorkOrd <> @WorkOrd
                    """,
                    new SugarParameter("@TenantId", tid),
                    new SugarParameter("@LotSerial", ls),
                    new SugarParameter("@WorkOrd", wo));
                if (dup > 0)
                    throw Oops.Oh("生产批号已被本租户下其他工单占用，请重新获取或修改");
            }

            DateTime? ordDate = string.IsNullOrWhiteSpace(input.OrdDate)
                ? null
                : DateTime.Parse(input.OrdDate.Trim(), CultureInfo.InvariantCulture).Date;
            var releaseDate = DateTime.Now;
            var account = _userManager.Account ?? "system";

            var kitting = await _kittingCheck.CheckSingleAsync(tid, wo, account);

            WorkOrderPickBillService.PickBillResult pickResult;
            await _db.Ado.BeginTranAsync();
            try
            {
                pickResult = await _pickBill.CreateForWorkOrderAsync(tid, wo, account);

                var updatePars = new List<SugarParameter>
                {
                    new SugarParameter("@Status",      "r"),
                    new SugarParameter("@OrdDate",     ordDate ?? (object)DBNull.Value),
                    new SugarParameter("@LotSerial",   string.IsNullOrWhiteSpace(input.LotSerial) ? (object)DBNull.Value : input.LotSerial.Trim()),
                    new SugarParameter("@ReleaseDate", releaseDate),
                    new SugarParameter("@UpdateUser",  account),
                    new SugarParameter("@UpdateTime",  releaseDate),
                    new SugarParameter("@WorkOrd",     wo),
                    new SugarParameter("@TenantId",   tid)
                };

                var affected = await _db.Ado.ExecuteCommandAsync(
                    """
                    UPDATE WorkOrdMaster
                    SET Status      = @Status,
                        OrdDate     = @OrdDate,
                        DueDate     = CASE
                            WHEN OrdDate IS NOT NULL AND @OrdDate IS NOT NULL AND DueDate IS NOT NULL THEN
                                DATE_ADD(DATE(DueDate), INTERVAL DATEDIFF(DATE(@OrdDate), DATE(OrdDate)) DAY)
                            ELSE DueDate
                        END,
                        LotSerial   = @LotSerial,
                        ReleaseDate = @ReleaseDate,
                        Eff         = @ReleaseDate,
                        UpdateUser  = @UpdateUser,
                        UpdateTime  = @UpdateTime,
                        Batch       = SUBSTRING(@WorkOrd, 2)
                    WHERE tenant_id = @TenantId AND WorkOrd = @WorkOrd
                    """,
                    updatePars);

                if (affected == 0)
                    throw Oops.Oh("工单下达失败：未更新到对应工单，请确认工单号、租户及状态仍为初始(p)");

                await _db.Ado.ExecuteCommandAsync(
                    """
                    UPDATE WorkOrdRouting
                    SET Status = 'r', UpdateUser = @User, UpdateTime = @Now
                    WHERE tenant_id = @TenantId AND WorkOrd = @WorkOrd
                      AND IFNULL(MilestoneOp, 0) = 1 AND IFNULL(IsActive, 0) = 1
                    """,
                    new SugarParameter("@User", account),
                    new SugarParameter("@Now", releaseDate),
                    new SugarParameter("@TenantId", tid),
                    new SugarParameter("@WorkOrd", wo));

                await _db.Ado.CommitTranAsync();
            }
            catch
            {
                await _db.Ado.RollbackTranAsync();
                throw;
            }

            var response = new
            {
                message = "工单下达成功",
                materialSituation = kitting.MaterialSituation,
                pickBillNbr = pickResult.Nbr,
                pickBillCreated = pickResult.Created,
                pickBillLineCount = pickResult.LineCount
            };
            await _runLog.SuccessAsync(logId, "工单下达成功", response);
            return response;
        }
        catch (Exception ex)
        {
            await _runLog.FailedAsync(logId, ex.Message, new { workOrd = wo, tenantId = tid });
            throw;
        }
    }

    /// <summary>批量齐套检查（租户下 p/r 工单）。</summary>
    [DisplayName("批量齐套检查")]
    [HttpPost("dispatch/kitting-check")]
    public async Task<object> BatchKittingCheck([FromQuery] string domain, [FromQuery] string? userAccount)
    {
        var tid = ResolveTenantId(
            !string.IsNullOrWhiteSpace(domain) && long.TryParse(domain.Trim(), out var d) && d > 0 ? d : null);
        var account = string.IsNullOrWhiteSpace(userAccount)
            ? (_userManager.Account ?? "system")
            : userAccount.Trim();
        var logId = await _runLog.StartAsync("S2_KITTING_CHECK_BATCH", tid, "tenant", tid, domain ?? tid.ToString());
        try
        {
            var result = await _kittingCheck.CheckTenantWorkOrdersAsync(tid, account);
            await _runLog.SuccessAsync(logId, "批量齐套检查完成", result);
            return new
            {
                message = "ok",
                checkedCount = result.CheckedCount,
                kittedCount = result.KittedCount,
                shortageCount = result.ShortageCount
            };
        }
        catch (Exception ex)
        {
            await _runLog.FailedAsync(logId, ex.Message, new { tenantId = tid, domain });
            throw;
        }
    }

    /// <summary>单工单齐套检查（下达后或手动触发）。</summary>
    [DisplayName("单工单齐套检查")]
    [HttpPost("dispatch/work-order-kitting-check")]
    public async Task<object> SingleKittingCheck([FromQuery] string workord, [FromQuery] string domain, [FromQuery] string? userAccount)
    {
        var tid = ResolveTenantId(
            !string.IsNullOrWhiteSpace(domain) && long.TryParse(domain.Trim(), out var d) && d > 0 ? d : null);
        var account = string.IsNullOrWhiteSpace(userAccount)
            ? (_userManager.Account ?? "system")
            : userAccount.Trim();
        var wo = workord.Trim();
        var logId = await _runLog.StartAsync("S2_KITTING_CHECK_SINGLE", tid, "WorkOrdMaster", null, wo);
        try
        {
            var result = await _kittingCheck.CheckSingleAsync(tid, wo, account);
            if (!result.IsKitted)
            {
                await _runLog.FailedAsync(logId, $"缺料行 {result.ShortageLineCount}", result);
                throw Oops.Oh($"工单 {workord} 齐套检查未通过（缺料行 {result.ShortageLineCount}）");
            }

            await _runLog.SuccessAsync(logId, "单工单齐套检查通过", result);
            return new { message = "ok", materialSituation = result.MaterialSituation };
        }
        catch (Exception ex)
        {
            if (ex.Message.Contains("齐套检查未通过", StringComparison.Ordinal))
                throw;
            await _runLog.FailedAsync(logId, ex.Message, new { workOrd = wo, tenantId = tid });
            throw;
        }
    }

    // ──────────────── 内部查询结果映射类 ────────────────

    private sealed class WorkOrderDispatchRow
    {
        public int Id { get; set; }
        public string? Domain { get; set; }
        public string? WorkOrd { get; set; }
        public string? Priority { get; set; }
        public string? ItemNum { get; set; }
        public string? Descr { get; set; }
        public string? Descr1 { get; set; }
        public decimal? QtyOrded { get; set; }
        public string? MaterialSituation { get; set; }
        public DateTime? OrdDate { get; set; }
        public DateTime? DueDate { get; set; }
        public string? Status { get; set; }
        public string? LotSerial { get; set; }
        public string? PrevNbr { get; set; }
        public string? Nbr { get; set; }
        public string? Wl { get; set; }
    }
}