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

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

namespace Admin.NET.Plugin.AiDOP.MaterialWarehouse;

/// <summary>
/// S5 来料检验单（qms_qcp_inspbill）只读 list 服务 —— 来料检验流程主入口。
///
/// 数据源：aidopdev.qms_qcp_inspbill 单表直读（不 join 申请主表/明细/SuppMaster/ApprovalFlow/SQE）。
/// 口径：检验单号=FBILLNO；来源单号=lydjbh；物料=FMATERIALCFG/wlmc/ggxh；供应商=gysmc（内嵌）；
/// 批次=pch；数量=FRINSQTY/jysl/dhsl/bhgsl；判定=pd(raw)；处理方式=clfs(raw)；状态=FBILLSTATUS(raw)；
/// 检验员=FINSPECTORID；检验起止=FINSPESTARTDATE/FINSPEENDDATE；创建=FCREATETIME。默认排序 FCREATETIME DESC, id DESC。
///
/// tenant/组织：qms_qcp_inspbill 已补 tenant_id 列，按严格可信租户过滤（需认证，租户仅取认证后 JWT，忽略前端 tenantId）；FORGID 当前全 NULL，不做 FORGID 过滤。
/// 本服务仅 SELECT：无新增/编辑/删除/详情/判定录入/主管审核/SQE 处置/仓储通知/
/// 库存事务/流程实例；不写 qms_qcp_inspbill、不改 FBIZTYPE、不碰 ApprovalFlow。pd/clfs/FBILLSTATUS 字典未闭合，raw 展示。
/// </summary>
[ApiDescriptionSettings(Order = 309, Description = "来料检验单列表")]
[Route("api/S5IqcInspBill")]
[NonUnify]
public class IqcInspBillService : IDynamicApiController, ITransient
{
    /// <summary>主/系统租户哨兵：超管未选择目标租户时其 JWT TenantId 即此值，拒绝作为业务租户。</summary>
    private const long MainTenantId = 1300000000001L;

    private readonly ISqlSugarClient _db;
    private readonly UserManager _userManager;

    public IqcInspBillService(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>排序字段白名单（前端 prop → 物理列），杜绝 orderBy 注入。</summary>
    private static readonly Dictionary<string, string> OrderByWhitelist = new(StringComparer.OrdinalIgnoreCase)
    {
        ["billNo"] = "ib.FBILLNO",
        ["sourceBillNo"] = "ib.lydjbh",
        ["materialCode"] = "ib.FMATERIALCFG",
        ["materialName"] = "ib.wlmc",
        ["supplierName"] = "ib.gysmc",
        ["lotNo"] = "ib.pch",
        ["billStatus"] = "ib.FBILLSTATUS",
        ["judgement"] = "ib.pd",
        ["inspectDate"] = "ib.FINSPESTARTDATE",
        ["createTime"] = "ib.FCREATETIME",
    };

    /// <summary>
    /// 来料检验单列表（只读分页查询）。
    /// </summary>
    [DisplayName("来料检验单列表")]
    [HttpGet("list")]
    public async Task<object> GetList([FromQuery] IqcInspBillListInput input)
    {
        var page = input.Page <= 0 ? 1 : input.Page;
        var pageSize = input.PageSize <= 0 ? 20 : (input.PageSize > 200 ? 200 : input.PageSize);
        var offset = (page - 1) * pageSize;

        // 同库分租户：qms_qcp_inspbill 已补 tenant_id 列，按严格可信租户（仅认证后 JWT，忽略前端 tenantId）过滤，杜绝匿名跨租户读。FORGID 全 NULL 不做组织过滤。
        var tenantId = ResolveTenantOrThrow();
        var where = new List<string> { "ib.tenant_id = @TenantId" };
        var pars = new List<SugarParameter> { new("@TenantId", tenantId) };

        void AddLike(string? v, string col, string p)
        {
            if (string.IsNullOrWhiteSpace(v)) return;
            where.Add($"{col} LIKE @{p}");
            pars.Add(new SugarParameter($"@{p}", $"%{v.Trim()}%"));
        }

        AddLike(input.BillNo, "ib.FBILLNO", "BillNo");
        AddLike(input.SourceBillNo, "ib.lydjbh", "SourceBillNo");
        AddLike(input.MaterialCode, "ib.FMATERIALCFG", "MaterialCode");
        AddLike(input.MaterialName, "ib.wlmc", "MaterialName");
        AddLike(input.SupplierName, "ib.gysmc", "SupplierName");
        AddLike(input.LotNo, "ib.pch", "LotNo");
        AddLike(input.Inspector, "ib.FINSPECTORID", "Inspector");

        if (!string.IsNullOrWhiteSpace(input.BillStatus))
        {
            where.Add("ib.FBILLSTATUS = @BillStatus");
            pars.Add(new SugarParameter("@BillStatus", input.BillStatus.Trim()));
        }
        if (input.Judgement.HasValue)
        {
            where.Add("ib.pd = @Judgement");
            pars.Add(new SugarParameter("@Judgement", input.Judgement.Value));
        }
        if (input.ProcessMethod.HasValue)
        {
            where.Add("ib.clfs = @ProcessMethod");
            pars.Add(new SugarParameter("@ProcessMethod", input.ProcessMethod.Value));
        }
        if (!string.IsNullOrWhiteSpace(input.InspectDateFrom))
        {
            where.Add("ib.FINSPESTARTDATE >= @InspectDateFrom");
            pars.Add(new SugarParameter("@InspectDateFrom", $"{input.InspectDateFrom.Trim()} 00:00:00"));
        }
        if (!string.IsNullOrWhiteSpace(input.InspectDateTo))
        {
            where.Add("ib.FINSPESTARTDATE <= @InspectDateTo");
            pars.Add(new SugarParameter("@InspectDateTo", $"{input.InspectDateTo.Trim()} 23:59:59"));
        }
        if (!string.IsNullOrWhiteSpace(input.CreateDateFrom))
        {
            where.Add("ib.FCREATETIME >= @CreateDateFrom");
            pars.Add(new SugarParameter("@CreateDateFrom", $"{input.CreateDateFrom.Trim()} 00:00:00"));
        }
        if (!string.IsNullOrWhiteSpace(input.CreateDateTo))
        {
            where.Add("ib.FCREATETIME <= @CreateDateTo");
            pars.Add(new SugarParameter("@CreateDateTo", $"{input.CreateDateTo.Trim()} 23:59:59"));
        }

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

        // 排序：白名单回落，方向 asc/desc 回落 desc；默认 FCREATETIME DESC, id DESC。
        string orderSql;
        if (!string.IsNullOrWhiteSpace(input.OrderBy) && OrderByWhitelist.TryGetValue(input.OrderBy.Trim(), out var col))
        {
            var dir = string.Equals(input.OrderDir?.Trim(), "asc", StringComparison.OrdinalIgnoreCase) ? "ASC" : "DESC";
            orderSql = $"{col} {dir}, ib.id DESC";
        }
        else
        {
            orderSql = "ib.FCREATETIME DESC, ib.id DESC";
        }

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

        var list = await _db.Ado.SqlQueryAsync<IqcInspBillListRow>(
            $"""
            SELECT
                ib.id              AS Id,
                ib.FBILLNO         AS BillNo,
                ib.lydjbh          AS SourceBillNo,
                ib.FMATERIALCFG    AS MaterialCode,
                ib.wlmc            AS MaterialName,
                ib.ggxh            AS Spec,
                ib.gysmc           AS SupplierName,
                ib.pch             AS LotNo,
                ib.FRINSQTY        AS ReportQty,
                ib.jysl            AS InspectQty,
                ib.dhsl            AS QualifiedQty,
                ib.bhgsl           AS UnqualifiedQty,
                ib.pd              AS Judgement,
                ib.clfs            AS ProcessMethod,
                ib.FBILLSTATUS     AS BillStatus,
                ib.FINSPECTORID    AS Inspector,
                ib.FINSPESTARTDATE AS InspectStartDate,
                ib.FINSPEENDDATE   AS InspectEndDate,
                ib.FCREATETIME     AS CreateTime,
                ib.FORGID          AS OrgId
            FROM qms_qcp_inspbill ib
            WHERE {whereSql}
            ORDER BY {orderSql}
            LIMIT {pageSize} OFFSET {offset}
            """,
            pars);

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

    /// <summary>
    /// 来料检验单详情（只读，按 id 单表直读 qms_qcp_inspbill）。
    ///
    /// 仅 SELECT 单行：不 join 申请表/明细/SuppMaster/ApprovalFlow/SQE，不查字典，不写任何表。
    /// id&lt;=0 或不存在返回 null（HTTP 200）。字典字段 pd/clfs/FBILLSTATUS/pcsq/blwtflid/FBIZTYPE 全 raw。
    /// 流程占位 BizType/BizId/BizCode 由本方法拼装，**不查 ApprovalFlowInstance、不生成流程实例**。
    /// qms_qcp_inspbill 已补 tenant_id：详情按 id + 当前租户过滤（与 list 口径一致），FORGID 全 NULL 不做组织过滤。
    /// </summary>
    [DisplayName("来料检验单详情")]
    [HttpGet("detail")]
    public async Task<object?> GetDetail([FromQuery] long id)
    {
        if (id <= 0) return null;

        // 同库分租户：详情按 id + 严格可信租户（仅认证后 JWT，忽略前端 tenantId）过滤，杜绝匿名跨租户越权取单（最高优先越权点）。
        var tenantId = ResolveTenantOrThrow();
        var pars = new List<SugarParameter> { new("@id", id), new("@TenantId", tenantId) };
        var list = await _db.Ado.SqlQueryAsync<IqcInspBillDetailRow>(
            """
            SELECT
                ib.id              AS Id,
                ib.FBILLNO         AS BillNo,
                ib.lydjbh          AS SourceBillNo,
                ib.FBILLSTATUS     AS BillStatus,
                ib.FBIZTYPE        AS BizTypeRaw,
                ib.FORGID          AS OrgId,
                ib.FCREATETIME     AS CreateTime,
                ib.FCREATORID      AS CreateUserId,
                ib.FMODIFYTIME     AS UpdateTime,
                ib.FMODIFIERID     AS UpdateUserId,
                ib.FMATERIALCFG    AS MaterialCode,
                ib.wlmc            AS MaterialName,
                ib.ggxh            AS Spec,
                ib.gysmc           AS SupplierName,
                ib.pch             AS LotNo,
                ib.jgbh            AS InspectionSpecNo,
                ib.jgbb            AS InspectionSpecVersion,
                ib.jgsxrq          AS InspectionSpecEffectiveDate,
                ib.FRINSQTY        AS ReportQty,
                ib.jysl            AS InspectQty,
                ib.SampleQty       AS SampleQty,
                ib.TestQty         AS TestQty,
                ib.dhsl            AS QualifiedQty,
                ib.bhgsl           AS UnqualifiedQty,
                ib.phsl            AS DestructiveQty,
                ib.lysl            AS RetainSampleQty,
                ib.FINSPECTORID    AS Inspector,
                ib.jyr             AS InspectorName,
                ib.FINSPESTARTDATE AS InspectStartDate,
                ib.FINSPEENDDATE   AS InspectEndDate,
                ib.pd              AS Judgement,
                ib.clfs            AS ProcessMethod,
                ib.UnqualifiedDescription AS UnqualifiedDescription,
                ib.thyy            AS ReturnReason,
                ib.blwtflid        AS DefectCategoryId,
                ib.blwtflmc        AS DefectCategoryName,
                ib.pcsq            AS DeviationApply,
                ib.pcsm            AS DeviationDescription,
                ib.leader          AS Leader,
                ib.FAUDITORID      AS AuditorId,
                ib.FAUDITDATE      AS AuditDate
            FROM qms_qcp_inspbill ib
            WHERE ib.id = @id AND ib.tenant_id = @TenantId
            LIMIT 1
            """,
            pars);

        var row = list.FirstOrDefault();
        if (row == null) return null;

        // 流程占位：固定 BizType、BizId=id、BizCode=FBILLNO，供后续 C'/D'/E' 批次接入 ApprovalFlow 用。本批不查/不建流程实例。
        row.BizType = "S5_IQC_INSPBILL";
        row.BizId = row.Id;
        row.BizCode = row.BillNo;
        row.FlowHint = "流程将在后续 C'/D'/E' 批次接入";
        return row;
    }
}