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' 追加“-挑选使用”。
/// Domain 由 GeneralizedCodeMaster(SystemConfig/CompanyCode) 解析，默认 8010。
///
/// 本服务仅 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>解析公司域（GeneralizedCodeMaster SystemConfig/CompanyCode，默认 8010）。</summary>
    private async Task<string> ResolveDomainAsync()
    {
        var list = await _db.Ado.SqlQueryAsync<string>(
            "SELECT Domain FROM GeneralizedCodeMaster WHERE FldName='SystemConfig' AND Val='CompanyCode' ORDER BY RecID LIMIT 1");
        var domain = list.FirstOrDefault();
        return string.IsNullOrWhiteSpace(domain) ? "8010" : domain;
    }

    /// <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 domain = await ResolveDomainAsync();

        var where = new List<string>
        {
            "m.Domain = @Domain",
            "m.Status = 'I'",
            "m.Qty > 0",
            "IFNULL(m.Location,'') = ''",
        };
        // 同库分租户：主表 MissedPrint 无 tenant_id（只能靠 Domain 公司域隔离，无法做租户行级过滤）；
        // LEFT JOIN 的 qms 申请/明细表已补 tenant_id，租户过滤置于 JOIN ON（避免退化为 INNER JOIN 丢失 MissedPrint 锚行）。
        // 租户改用严格可信解析（仅认证后 JWT，忽略前端 tenantId）；配合类级已去 [AllowAnonymous]，杜绝匿名跨租户读。
        // 残留边界：MissedPrint 锚行本身仍按 Domain（非租户）可见，此为旧系统 pr_WMS_GetMissedTempQty 无租户列的既有约束。
        var tenantId = ResolveTenantOrThrow();
        var pars = new List<SugarParameter> { new("@Domain", domain), 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
            LEFT JOIN ItemMaster i ON i.Domain = @Domain AND t.ItemNum = i.ItemNum
            LEFT JOIN SuppMaster s ON s.Domain = @Domain 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()
    {
        // 残留边界（注明）：本选项来自 MissedPrint（无 tenant_id）+ SuppMaster（无 tenant_id），无 tenant-safe 父键可挂载租户过滤，
        // 只能按 Domain 公司域收敛，无法做租户行级过滤（既有约束）。类级已去 [AllowAnonymous] → 需认证后方可访问，杜绝匿名读。
        var domain = await ResolveDomainAsync();
        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.Domain = @Domain AND m.Supply = s.Supp
            WHERE m.Domain = @Domain AND m.Status = 'I' AND m.Qty > 0 AND IFNULL(m.Location,'') = ''
              AND IFNULL(m.Supply,'') <> ''
            ORDER BY m.Supply
            """,
            new List<SugarParameter> { new("@Domain", domain) });
        return list;
    }
}