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

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

    // ══════════════════════════════════════════════════════════════
    // 列表 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);

        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 COLLATE utf8mb4_general_ci = b.ItemNum COLLATE utf8mb4_general_ci
            LEFT JOIN mes_morder m ON a.WorkOrd COLLATE utf8mb4_general_ci = m.morder_no COLLATE utf8mb4_general_ci
                AND CAST(a.Domain AS CHAR(64)) COLLATE utf8mb4_general_ci = CAST(m.factory_id AS CHAR(64)) COLLATE utf8mb4_general_ci
            LEFT JOIN NbrMaster nm
                ON a.Domain COLLATE utf8mb4_general_ci = nm.Domain COLLATE utf8mb4_general_ci
                AND a.WorkOrd COLLATE utf8mb4_general_ci = nm.WorkOrd COLLATE utf8mb4_general_ci
                AND nm.Type = 'SM' AND nm.TransType = 'PrevProcess'
            LEFT JOIN NbrMaster nm1
                ON a.Domain COLLATE utf8mb4_general_ci = nm1.Domain COLLATE utf8mb4_general_ci
                AND a.WorkOrd COLLATE utf8mb4_general_ci = nm1.WorkOrd COLLATE utf8mb4_general_ci
                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 };
    }

    // ══════════════════════════════════════════════════════════════
    // 工单下达 POST /api/WorkOrder/dispatch/release
    // ══════════════════════════════════════════════════════════════
    /// <summary>工单下达（更新开工日期、生产批号，状态改为 r）🏭</summary>
    [DisplayName("工单下达")]
    [ApiDescriptionSettings(Name = "ReleaseWorkOrder"), HttpPost("dispatch/release")]
    public async Task<object> ReleaseWorkOrder([FromBody] WorkOrderReleaseInput input)
    {
        // 参照 SysJobService.UpdateJobDetail：先查再改
        var pars = new List<SugarParameter>
        {
            new SugarParameter("@WorkOrd", input.WorkOrd),
            new SugarParameter("@Domain",  input.Domain)
        };
        var exists = await _db.Ado.GetIntAsync(
            "SELECT COUNT(*) FROM WorkOrdMaster WHERE WorkOrd = @WorkOrd AND Domain = @Domain AND Status = 'p'",
            pars);
        if (exists == 0)
            throw Oops.Oh("工单不存在或状态不为初始（p），无法下达");

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

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

        await _db.Ado.ExecuteCommandAsync(
            """
            UPDATE WorkOrdMaster
            SET Status      = @Status,
                OrdDate     = @OrdDate,
                LotSerial   = @LotSerial,
                ReleaseDate = @ReleaseDate,
                UpdateUser  = @UpdateUser,
                UpdateTime  = @UpdateTime
            WHERE WorkOrd = @WorkOrd AND Domain = @Domain
            """,
            updatePars);

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