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

using System.Globalization;

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;

    public WorkOrderDispatchService(ISqlSugarClient db, UserManager userManager)
    {
        _db = db;
        _userManager = userManager;
    }

    private long ResolveTenantId(long? requestTenantId)
    {
        var tid = requestTenantId is > 0 ? requestTenantId!.Value : _userManager.TenantId;
        if (tid == 0)
            throw Oops.Oh("租户ID无效，请传入 tenantId 或登录后重试");
        return tid;
    }

    /// <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 pars = new List<SugarParameter>();
        // 与库内其它原生 SQL 一致：统一 COLLATE，避免 utf8mb4_general_ci / utf8mb4_0900_ai_ci 混用报错
        const string C = "utf8mb4_general_ci";
        var conditions = new List<string> { $"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 
            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();

        await AssertInitialWorkOrderExistsAsync(tid, wo);

        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("生产批号已被本租户下其他工单占用，请重新获取或修改");
        }

        // 开工日仅取日期部分；与库中原开工日做日历差，同天数平移 DueDate（保持原「完工−开工」间隔）
        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 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 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,
                UpdateUser  = @UpdateUser,
                UpdateTime  = @UpdateTime
            WHERE tenant_id = @TenantId AND WorkOrd = @WorkOrd
            """,
            updatePars);

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

        return new { message = "工单下达成功" };
    }

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

    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; }
    }
}