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**，不做租户过滤；FORGID 当前全 NULL，不做 FORGID 过滤。
/// TenantId/ForgId 接收即忽略。本服务仅 SELECT：无新增/编辑/删除/详情/判定录入/主管审核/SQE 处置/仓储通知/
/// 库存事务/流程实例；不写 qms_qcp_inspbill、不改 FBIZTYPE、不碰 ApprovalFlow。pd/clfs/FBILLSTATUS 字典未闭合，raw 展示。
/// </summary>
[ApiDescriptionSettings(Order = 309, Description = "来料检验单列表")]
[Route("api/S5IqcInspBill")]
[AllowAnonymous]
[NonUnify]
public class IqcInspBillService : IDynamicApiController, ITransient
{
    private readonly ISqlSugarClient _db;

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

    /// <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、FORGID 全 NULL：不做租户/组织过滤（TenantId/ForgId 接收即忽略）。
        var where = new List<string> { "1=1" };
        var pars = new List<SugarParameter>();

        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、FORGID 全 NULL：不做租户/组织过滤，与 list 口径一致。
    /// </summary>
    [DisplayName("来料检验单详情")]
    [HttpGet("detail")]
    public async Task<object?> GetDetail([FromQuery] long id)
    {
        if (id <= 0) return null;

        var pars = new List<SugarParameter> { new("@id", id) };
        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
            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;
    }
}