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 列存在但实测**全为 NULL**，
/// 且 152 行中 150 行 Location 为空 —— 既不能按 tenant_id 过滤（会把页面整体查空），
/// 也不能拿 Location 当租户边界（会误删绝大多数合法标签）。
/// 故业务行的租户归属仍是**未解决的数据模型债**，本服务不伪造租户边界；
/// 已做的收敛是：① 类级去 [AllowAnonymous]（原先可被完全未认证调用，实测匿名 GET 返回 200 + 152 行真实数据）；
/// ② 状态码表 JOIN 按 Domain + 当前租户限定（见下）。
/// </summary>
[ApiDescriptionSettings(Order = 302, Description = "标签查询")]
[Route("api/S5LabelQuery")]
[NonUnify]
public class LabelQueryService : IDynamicApiController, ITransient
{
    /// <summary>主/系统租户哨兵：超管未选择目标租户时其 JWT TenantId 即此值，拒绝作为业务租户。</summary>
    private const long MainTenantId = 1300000000001L;

    private readonly ISqlSugarClient _db;
    private readonly UserManager _userManager;

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

    /// <summary>严格可信租户解析：只来自认证后 JWT；无 Token 或超管未选目标租户 → 拒绝，无默认回退。</summary>
    private long ResolveTenantOrThrow()
    {
        var tid = _userManager.TenantId;
        if (tid <= 0)
            throw Oops.Oh("无法确定当前租户，请重新登录或选择目标租户");
        if (_userManager.SuperAdmin && tid == MainTenantId)
            throw Oops.Oh("超级管理员操作前必须选择目标租户");
        return tid;
    }

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

        // 仅用于状态码表 JOIN 的租户限定；业务行本身的租户边界见类注释（数据模型债，本批不伪造）
        var tenantId = ResolveTenantOrThrow();

        // 租户边界：MissedPrint.tenant_id 由生成标签写入（SupplierShipmentService）并已一次性回填历史行。
        // 不得退回按 Domain 过滤 —— 797 与 UAT 的 Domain 同为 8010，Domain 不是租户边界。
        var where = new List<string> { "m.tenant_id = @TenantId" };
        var pars = new List<SugarParameter> { new("@TenantId", tenantId) };

        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
            -- 码表 JOIN 必须按 Domain + 租户双限定（本仓既定规则，见
            -- StdInventoryTransactionReader.cs / OrderDeliveryService.cs / InoutQueryService.cs）。
            -- 原实现只按 (FldName, Val) 关联：BarcodeStatus 在 797(8010) 与 824(2410) 各有一套同名码值，
            -- 每行都命中 2 条 → 分页 SELECT 行数翻倍（实测 base 152 → 304，而 COUNT 仍按基表算 152），
            -- 且状态文案会取到**别的租户**的字典。
            -- 当前租户无该码表时 g 全为 NULL，由既有 COALESCE 回落显示原始状态码，不借用他租户文本。
            LEFT JOIN GeneralizedCodeMaster g
                   ON g.FldName = 'BarcodeStatus'
                  AND g.Val = m.Status
                  AND g.Domain = m.Domain
                  AND g.tenant_id = @TenantId
            WHERE {whereSql}
            -- 业务排序保持不变，末尾补主键 RecID 作唯一 tiebreaker：
            -- 原排序列均非唯一，OFFSET 分页在并列行上会跨页重复/漏行。
            ORDER BY {BuildOrderBy(input.OrderBy, input.OrderDir)}, m.RecID
            LIMIT {pageSize} OFFSET {offset}
            """,
            pars);

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

    /// <summary>
    /// 状态下拉选项（BarcodeStatus 码表，Val + 中文描述，只读）。
    /// </summary>
    [DisplayName("标签查询状态选项")]
    [HttpGet("status-options")]
    public async Task<object> GetStatusOptions()
    {
        // 与列表 JOIN 同口径：按当前租户取码表。
        // 原实现无租户条件 + GROUP BY Val,Comments —— 把多个租户的字典揉成一份，
        // 等于「任取一个别的租户的文案」，与列表侧口径也不一致。
        var tenantId = ResolveTenantOrThrow();
        var list = await _db.Ado.SqlQueryAsync<LabelQueryStatusOption>(
            """
            SELECT Val AS Val, Comments AS Label
            FROM GeneralizedCodeMaster
            WHERE FldName = 'BarcodeStatus' AND tenant_id = @TenantId
              AND Comments IS NOT NULL AND Comments <> ''
            ORDER BY Val
            """,
            new List<SugarParameter> { new("@TenantId", tenantId) });
        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 tenant_id = @TenantId
              AND Location IS NOT NULL AND Location <> ''
            ORDER BY Location
            """,
            new List<SugarParameter> { new("@TenantId", ResolveTenantOrThrow()) });
        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";
    }
}