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