AiDOPWarehouse/server/Plugins/Admin.NET.Plugin.AiDOP/MaterialWarehouse/LabelQueryService.cs

using Admin.NET.Plugin.AiDOP.MaterialWarehouse.Dto;

namespace Admin.NET.Plugin.AiDOP.MaterialWarehouse;

/// <summary>
/// S5 标签查询 只读 list 服务。
///
/// 数据源：aidopdev.MissedPrint（legacy 标签/条码记录表）。状态描述经 GeneralizedCodeMaster(FldName='BarcodeStatus') 解码。
/// 参考本仓既有同表只读投影先例：Controllers/S0/Manufacturing/AdoS0MfgProcessFlowCardsController（同解 BarcodeStatus）。
///
/// 本服务仅 SELECT：无新增/编辑/删除/库位转移/库存事务/状态流转/物料卡写入；扁平列表，无 detail。
/// MissedPrint 无 tenant_id 列，不做租户/工厂域过滤（对齐既有 MissedPrint 只读投影先例；按工厂域隔离为后续独立议题）。
/// </summary>
[ApiDescriptionSettings(Order = 302, Description = "标签查询")]
[Route("api/S5LabelQuery")]
[AllowAnonymous]
[NonUnify]
public class LabelQueryService : IDynamicApiController, ITransient
{
    private readonly ISqlSugarClient _db;

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

    /// <summary>
    /// 标签查询列表（只读分页查询）。
    /// </summary>
    [DisplayName("标签查询列表")]
    [HttpGet("list")]
    public async Task<object> GetList([FromQuery] LabelQueryListInput input)
    {
        var page = input.Page <= 0 ? 1 : input.Page;
        var pageSize = input.PageSize <= 0 ? 10 : input.PageSize;
        var offset = (page - 1) * pageSize;

        var where = new List<string> { "1=1" };
        var pars = new List<SugarParameter>();

        if (!string.IsNullOrWhiteSpace(input.ItemNum))
        {
            where.Add("m.ItemNum LIKE @ItemNum");
            pars.Add(new SugarParameter("@ItemNum", $"%{input.ItemNum.Trim()}%"));
        }
        if (!string.IsNullOrWhiteSpace(input.LotSerial))
        {
            where.Add("m.LotSerial LIKE @LotSerial");
            pars.Add(new SugarParameter("@LotSerial", $"%{input.LotSerial.Trim()}%"));
        }
        if (!string.IsNullOrWhiteSpace(input.Location))
        {
            where.Add("m.Location = @Location");
            pars.Add(new SugarParameter("@Location", input.Location.Trim()));
        }
        if (!string.IsNullOrWhiteSpace(input.Shelf))
        {
            where.Add("m.Shelf LIKE @Shelf");
            pars.Add(new SugarParameter("@Shelf", $"%{input.Shelf.Trim()}%"));
        }
        if (!string.IsNullOrWhiteSpace(input.ShipperNbr))
        {
            where.Add("m.ShipperNbr LIKE @ShipperNbr");
            pars.Add(new SugarParameter("@ShipperNbr", $"%{input.ShipperNbr.Trim()}%"));
        }
        if (!string.IsNullOrWhiteSpace(input.Status))
        {
            where.Add("m.Status = @Status");
            pars.Add(new SugarParameter("@Status", input.Status.Trim()));
        }
        if (!string.IsNullOrWhiteSpace(input.BarCode))
        {
            where.Add("m.BarCode LIKE @BarCode");
            pars.Add(new SugarParameter("@BarCode", $"%{input.BarCode.Trim()}%"));
        }

        var whereSql = string.Join(" AND ", where);

        var total = await _db.Ado.GetIntAsync(
            $"SELECT COUNT(1) FROM MissedPrint m WHERE {whereSql}", pars);

        var list = await _db.Ado.SqlQueryAsync<LabelQueryListRow>(
            $"""
            SELECT
                m.RecID       AS Id,
                m.BarCode     AS BarCode,
                m.ItemNum     AS ItemNum,
                m.Descr       AS Descr,
                m.Product     AS Product,
                m.Qty         AS Qty,
                m.Status      AS Status,
                COALESCE(g.Comments, m.Status) AS StatusDesc,
                m.LotSerial   AS LotSerial,
                m.Location    AS Location,
                m.Shelf       AS Shelf,
                m.OrdNbr      AS OrdNbr,
                m.PackingQty  AS PackingQty,
                m.ProdDate    AS ProdDate,
                m.WorkOrd     AS WorkOrd,
                m.ProdLine    AS ProdLine,
                m.LabelFormat AS LabelFormat,
                m.Position    AS Position,
                m.Carton      AS Carton,
                m.Period      AS Period,
                m.Supply      AS Supply,
                m.PurOrd      AS PurOrd,
                m.PurLine     AS PurLine,
                m.ShipperNbr  AS ShipperNbr,
                m.ShipperLine AS ShipperLine,
                m.InvDate     AS InvDate,
                m.ExpireDate  AS ExpireDate,
                m.Remark      AS Remark
            FROM MissedPrint m
            LEFT JOIN GeneralizedCodeMaster g ON g.FldName = 'BarcodeStatus' AND g.Val = m.Status
            WHERE {whereSql}
            ORDER BY {BuildOrderBy(input.OrderBy, input.OrderDir)}
            LIMIT {pageSize} OFFSET {offset}
            """,
            pars);

        return new { total, page, pageSize, list };
    }

    /// <summary>
    /// 状态下拉选项（BarcodeStatus 码表，Val + 中文描述，只读）。
    /// </summary>
    [DisplayName("标签查询状态选项")]
    [HttpGet("status-options")]
    public async Task<object> GetStatusOptions()
    {
        var list = await _db.Ado.SqlQueryAsync<LabelQueryStatusOption>(
            """
            SELECT Val AS Val, Comments AS Label
            FROM GeneralizedCodeMaster
            WHERE FldName = 'BarcodeStatus' AND Comments IS NOT NULL AND Comments <> ''
            GROUP BY Val, Comments
            ORDER BY Val
            """);
        return list;
    }

    /// <summary>
    /// 库位下拉选项（MissedPrint.Location distinct，只读）。
    /// </summary>
    [DisplayName("标签查询库位选项")]
    [HttpGet("location-options")]
    public async Task<object> GetLocationOptions()
    {
        var list = await _db.Ado.SqlQueryAsync<LabelQueryLocationOption>(
            """
            SELECT DISTINCT Location AS Val
            FROM MissedPrint
            WHERE Location IS NOT NULL AND Location <> ''
            ORDER BY Location
            """);
        return list;
    }

    /// <summary>
    /// 排序白名单：仅允许按已展示列排序，杜绝 SQL 注入。
    /// </summary>
    private static string BuildOrderBy(string? orderBy, string? orderDir)
    {
        var column = orderBy switch
        {
            "barCode" => "m.BarCode",
            "itemNum" => "m.ItemNum",
            "qty" => "m.Qty",
            "status" => "m.Status",
            "lotSerial" => "m.LotSerial",
            "location" => "m.Location",
            "prodDate" => "m.ProdDate",
            "invDate" => "m.InvDate",
            "expireDate" => "m.ExpireDate",
            _ => "m.CreateTime",
        };
        var direction = string.Equals(orderDir, "asc", StringComparison.OrdinalIgnoreCase) ? "ASC" : "DESC";
        return $"{column} {direction}, m.RecID DESC";
    }
}