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

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

namespace Admin.NET.Plugin.AiDOP.MaterialWarehouse;

/// <summary>
/// S5 暂收在检列表 只读 list 服务。
///
/// 数据源：aidopdev.MissedPrint（Status='I' 暂收待检、Qty>0、Location 空），忠实复刻旧系统存储过程
/// pr_WMS_GetMissedTempQty：LEFT JOIN qms_qcp_inspecapplyn/qms_qcp_insappnentry 取检验状态，按
/// Supply/PurOrd/PurLine/ShipperNbr/ItemNum/LotSerial 聚合(MAX Shelf/SUM Qty/MAX CheckNo/MAX FINSPECTSTATUS)，
/// 再 LEFT JOIN ItemMaster(描述/单位)、SuppMaster(简称)，clfs='3' 追加“-挑选使用”。
/// 租户边界取 MissedPrint.tenant_id（正式租户列，已由生成标签写入 + 1.0.442 回填补齐）。
/// Domain **不再**参与 MissedPrint 及其主数据关联的过滤，理由见 GetList 内注释。
///
/// 本服务仅 SELECT：无新增/编辑/删除/库存事务/状态流转；MissedPrint 无 tenant_id，不做租户过滤。
/// </summary>
[ApiDescriptionSettings(Order = 301, Description = "暂收在检列表")]
[Route("api/S5PendingInspection")]
[NonUnify]
public class PendingInspectionService : IDynamicApiController, ITransient
{
    /// <summary>主/系统租户哨兵：超管未选择目标租户时其 JWT TenantId 即此值，拒绝作为业务租户。</summary>
    private const long MainTenantId = 1300000000001L;

    private readonly ISqlSugarClient _db;
    private readonly UserManager _userManager;

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

    /// <summary>严格可信租户解析：只来自认证后 JWT；无 Token(&lt;=0) 或超管未选主租户 → 拒绝；不读前端 tenantId、无默认回退。</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] PendingInspectionListInput input)
    {
        var page = input.Page <= 0 ? 1 : input.Page;
        var pageSize = input.PageSize <= 0 ? 10 : input.PageSize;
        var offset = (page - 1) * pageSize;

        var tenantId = ResolveTenantOrThrow();

        // 租户边界 = MissedPrint.tenant_id。
        //
        // 这里**刻意不再带 m.Domain = @Domain**，依据（2026-09-02 实测 aidopdev）：
        // ① Domain 不是租户边界 —— 797 与 UAT 的 Domain 解析结果同为 '8010'；
        // ② MissedPrint 里每个租户只有单一 Domain 值（797 全为 '8010'、UAT 全为空串），
        //    所以叠加 Domain 对**租户内**没有任何过滤作用，只可能整租户命中或整租户清零；
        // ③ 而 UAT 恰好就是被整租户清零的那一种：SourceDomainTenantResolver 经 LocationMaster
        //    回落给出 '8010'，UAT 自己的 48 行 Domain 却是空串 —— 旧写法下 UAT 的候选宇宙
        //    100% 由 797 的 104 行构成，自己的数据一行都进不来，方向是反的。
        // 结论：Domain 在本查询里既无过滤价值又会误杀，由 tenant_id 完全取代。
        var where = new List<string>
        {
            "m.tenant_id = @TenantId",
            "m.Status = 'I'",
            "m.Qty > 0",
            "IFNULL(m.Location,'') = ''",
        };
        // qms 申请/明细的租户过滤置于 JOIN ON（避免 LEFT JOIN 退化为 INNER JOIN 丢失 MissedPrint 锚行）
        var pars = new List<SugarParameter> { new("@TenantId", tenantId) };

        if (!string.IsNullOrWhiteSpace(input.DemandNo))
        {
            where.Add("m.PurOrd LIKE @DemandNo");
            pars.Add(new SugarParameter("@DemandNo", $"%{input.DemandNo.Trim()}%"));
        }
        if (!string.IsNullOrWhiteSpace(input.Shelf))
        {
            where.Add("m.Shelf LIKE @Shelf");
            pars.Add(new SugarParameter("@Shelf", $"%{input.Shelf.Trim()}%"));
        }
        if (!string.IsNullOrWhiteSpace(input.BatchNo))
        {
            where.Add("m.LotSerial LIKE @BatchNo");
            pars.Add(new SugarParameter("@BatchNo", $"%{input.BatchNo.Trim()}%"));
        }
        if (!string.IsNullOrWhiteSpace(input.MaterialCode))
        {
            where.Add("m.ItemNum LIKE @MaterialCode");
            pars.Add(new SugarParameter("@MaterialCode", $"%{input.MaterialCode.Trim()}%"));
        }
        if (!string.IsNullOrWhiteSpace(input.InspectStatus))
        {
            where.Add("d.FINSPECTSTATUS = @InspectStatus");
            pars.Add(new SugarParameter("@InspectStatus", input.InspectStatus.Trim()));
        }
        if (!string.IsNullOrWhiteSpace(input.Supplier))
        {
            where.Add("m.Supply = @Supplier");
            pars.Add(new SugarParameter("@Supplier", input.Supplier.Trim()));
        }

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

        // 复刻存储过程 @tbLocDet 聚合层
        var groupedInner =
            $"""
            SELECT m.Supply, m.PurOrd, m.PurLine, m.ItemNum, m.ShipperNbr, m.LotSerial,
                   MAX(m.Shelf) AS Shelf, SUM(m.Qty) AS Qty, MAX(m.CheckNo) AS CheckNo,
                   MAX(IFNULL(d.FINSPECTSTATUS,'')) AS FINSPECTSTATUS
            FROM MissedPrint m
            LEFT JOIN qms_qcp_inspecapplyn q ON m.CheckNo = q.FBILLNO AND q.tenant_id = @TenantId
            LEFT JOIN qms_qcp_insappnentry d ON q.id = d.glid AND d.tenant_id = @TenantId
            WHERE {whereSql}
            GROUP BY m.Supply, m.PurOrd, m.PurLine, m.ShipperNbr, m.ItemNum, m.LotSerial
            """;

        var total = await _db.Ado.GetIntAsync($"SELECT COUNT(1) FROM ({groupedInner}) t", pars);

        var grandList = await _db.Ado.SqlQueryAsync<decimal>($"SELECT IFNULL(SUM(t.Qty),0) FROM ({groupedInner}) t", pars);
        var grandTotalQty = grandList.FirstOrDefault();

        var list = await _db.Ado.SqlQueryAsync<PendingInspectionListRow>(
            $"""
            SELECT
                t.PurOrd     AS DemandNo,
                t.PurLine    AS DemandLineNo,
                t.ItemNum    AS MaterialCode,
                i.Descr      AS MaterialName,
                t.LotSerial  AS BatchNo,
                t.Qty        AS Qty,
                i.UM         AS Unit,
                t.Shelf      AS PendingShelf,
                t.ShipperNbr AS DeliveryNo,
                t.CheckNo    AS InspectNo,
                CONCAT(t.FINSPECTSTATUS,
                    CASE WHEN (SELECT MAX(ib.clfs) FROM qms_qcp_inspbill ib WHERE ib.lydjbh = t.CheckNo AND ib.tenant_id = @TenantId) = '3'
                         THEN '-挑选使用' ELSE '' END) AS InspectStatus,
                t.Supply     AS Supplier,
                s.SortName   AS SupplierShortName
            FROM ({groupedInner}) t
            -- 主数据按租户关联，不按 Domain：(tenant_id, ItemNum) 与 (tenant_id, Supp) 都唯一，
            -- 而 (Domain, ItemNum) 有 391 组重复、(Domain, Supp) 有 14 组重复，
            -- Domain-only 关联会一对多放大，使分页取回行数与 COUNT 出来的 total 对不上。
            LEFT JOIN ItemMaster i ON i.tenant_id = @TenantId AND t.ItemNum = i.ItemNum
            LEFT JOIN SuppMaster s ON s.tenant_id = @TenantId AND t.Supply = s.Supp
            ORDER BY t.Supply, t.PurOrd, t.PurLine
            LIMIT {pageSize} OFFSET {offset}
            """,
            pars);

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

    /// <summary>
    /// 检验状态下拉选项（qms_qcp_insappnentry.FINSPECTSTATUS distinct，只读）。
    /// </summary>
    [DisplayName("暂收在检检验状态选项")]
    [HttpGet("status-options")]
    public async Task<object> GetStatusOptions()
    {
        // 同库分租户：qms_qcp_insappnentry 已补 tenant_id，选项按严格可信租户（仅认证后 JWT，忽略前端 tenantId）收敛。
        var tenantId = ResolveTenantOrThrow();
        var list = await _db.Ado.SqlQueryAsync<PendingInspectionStatusOption>(
            """
            SELECT DISTINCT FINSPECTSTATUS AS Val
            FROM qms_qcp_insappnentry
            WHERE IFNULL(FINSPECTSTATUS,'') <> '' AND tenant_id = @TenantId
            ORDER BY FINSPECTSTATUS
            """,
            new List<SugarParameter> { new("@TenantId", tenantId) });
        return list;
    }

    /// <summary>
    /// 供应商下拉选项（暂收在检集合内 distinct Supply + SuppMaster.SortName，只读）。
    /// </summary>
    [DisplayName("暂收在检供应商选项")]
    [HttpGet("supplier-options")]
    public async Task<object> GetSupplierOptions()
    {
        // 与主查询同口径：租户边界取 tenant_id，MissedPrint 与 SuppMaster 都按租户收敛，
        // 下拉集合不会再出现别的租户的供应商（Supply 编码跨租户重名不影响，集合本身已按租户切分）。
        var tenantId = ResolveTenantOrThrow();
        var list = await _db.Ado.SqlQueryAsync<PendingInspectionSupplierOption>(
            """
            SELECT DISTINCT m.Supply AS Val, s.SortName AS Label
            FROM MissedPrint m
            LEFT JOIN SuppMaster s ON s.tenant_id = @TenantId AND m.Supply = s.Supp
            WHERE m.tenant_id = @TenantId AND m.Status = 'I' AND m.Qty > 0 AND IFNULL(m.Location,'') = ''
              AND IFNULL(m.Supply,'') <> ''
            ORDER BY m.Supply
            """,
            new List<SugarParameter> { new("@TenantId", tenantId) });
        return list;
    }
}