AiDOPWarehouse/server/Plugins/Admin.NET.Plugin.AiDOP/FinishedWarehouse/FqcInspBillService.cs

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

namespace Admin.NET.Plugin.AiDOP.FinishedWarehouse;

/// <summary>
/// S7 FQC 成品质量：检验任务列表 / 检验单(结果)列表 只读 list 服务。
///
/// 数据源（均 aidopdev，只读）：
///   - task-list  ：qms_fqcbj（37 列，归口自旧系统 dopdemo，空表待上游抽数）。yxj/FINSPECTSTATUS 原样透出，
///                  rf_dictionary 优先级字典未接（CTO 拍板舍弃）。默认排序 FAPPLYTIME DESC, id DESC。
///   - result-list：qms_qcpp_inspbill 主表；SAP 工单号经 (SELECT MIN(wo.WorkOrd) FROM WorkOrdMaster wo
///                  WHERE wo.Batch = a.sczld) 标量子查询取得（Batch 非唯一仅因 NULL；非空 Batch 现 1:1，
///                  MIN 保确定性、杜绝行倍增）。默认排序 FINSPEENDDATE DESC, id DESC。
///
/// 归口说明（方案 A，复刻已闭环 S6 IPQC IpqcInspectionMdpSyncService.EnsureTablesAsync）：
///   qms_fqcbj 原仅存于旧系统 dopdemo（0 行）、aidopdev 无此表，且 dopdemo 非运行时连接。
///   EnsureFqcTaskTableAsync 在 aidopdev 建同构空表（CREATE TABLE IF NOT EXISTS，DDL 例外、仅一次、零 DML），
///   后续由上游一次性抽数填充，届时无需改代码即随查询流通。id 不设 AUTO_INCREMENT（抽数将携旧系统主键）。
///
/// 只读边界：纯 SELECT + 一次性 DDL 建空表；绝不 INSERT/UPDATE/DELETE 任何业务表、不写库存/仓储/生产状态、
///   不接 ApprovalFlow、不做详情/子表(qms_qcpp_inspbillst 留下一批)/录入/审核/处置。两表无 tenant_id，不做租户过滤。
/// </summary>
[ApiDescriptionSettings(Order = 330, Description = "FQC检验单列表")]
[Route("api/S7FqcInspBill")]
[AllowAnonymous]
[NonUnify]
public class FqcInspBillService : IDynamicApiController, ITransient
{
    private readonly ISqlSugarClient _db;

    /// <summary>归口空表建表守卫：每进程仅尝试一次 CREATE TABLE IF NOT EXISTS。</summary>
    private static int _taskTableEnsured;

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

    /// <summary>检验任务列表 排序白名单（前端 prop → 物理列，杜绝 orderBy 注入）。</summary>
    private static readonly Dictionary<string, string> TaskOrderWhitelist = new(StringComparer.OrdinalIgnoreCase)
    {
        ["applyTime"] = "t.FAPPLYTIME",
        ["productionBatchNo"] = "t.scph",
        ["productionOrderNo"] = "t.sczld",
        ["materialCode"] = "t.wlbm",
        ["priority"] = "t.yxj",
        ["inspectStartTime"] = "t.jykssj",
        ["inspectFinishTime"] = "t.jywcsj",
        ["inspectProgress"] = "t.FINSPECTSTATUS",
    };

    /// <summary>检验单列表 排序白名单（前端 prop → 物理列）。</summary>
    private static readonly Dictionary<string, string> ResultOrderWhitelist = new(StringComparer.OrdinalIgnoreCase)
    {
        ["inspectTime"] = "a.FINSPEENDDATE",
        ["billNo"] = "a.FBILLNO",
        ["productionBatchNo"] = "a.scph",
        ["materialCode"] = "a.FMATERIALCFG",
        ["inspector"] = "a.jyr",
        ["inspectQty"] = "a.jysj",
        ["productionOrderNo"] = "a.sczld",
    };

    /// <summary>
    /// FQC 检验任务列表（只读分页查询，源 qms_fqcbj）。
    /// </summary>
    [DisplayName("FQC检验任务列表")]
    [HttpGet("task-list")]
    public async Task<object> GetTaskList([FromQuery] FqcTaskListInput input)
    {
        await EnsureFqcTaskTableAsync();

        var page = input.Page <= 0 ? 1 : input.Page;
        var pageSize = input.PageSize <= 0 ? 10 : (input.PageSize > 200 ? 200 : input.PageSize);
        var offset = (page - 1) * pageSize;

        // qms_fqcbj 无 tenant_id：不做租户过滤（TenantId 接收即忽略）。
        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.ProductionOrderNo, "t.sczld", "ProductionOrderNo");
        AddLike(input.ProductionBatchNo, "t.scph", "ProductionBatchNo");
        AddLike(input.MaterialCode, "t.wlbm", "MaterialCode");
        AddLike(input.Priority, "t.yxj", "Priority");
        AddLike(input.Inspector, "t.jyfzr", "Inspector");

        if (!string.IsNullOrWhiteSpace(input.InspectProgress))
        {
            where.Add("t.FINSPECTSTATUS = @InspectProgress");
            pars.Add(new SugarParameter("@InspectProgress", input.InspectProgress.Trim()));
        }
        if (!string.IsNullOrWhiteSpace(input.ApplyTimeStart))
        {
            where.Add("t.FAPPLYTIME >= @ApplyTimeStart");
            pars.Add(new SugarParameter("@ApplyTimeStart", $"{input.ApplyTimeStart.Trim()} 00:00:00"));
        }
        if (!string.IsNullOrWhiteSpace(input.ApplyTimeEnd))
        {
            where.Add("t.FAPPLYTIME <= @ApplyTimeEnd");
            pars.Add(new SugarParameter("@ApplyTimeEnd", $"{input.ApplyTimeEnd.Trim()} 23:59:59"));
        }

        var whereSql = string.Join(" AND ", where);
        var orderSql = ResolveOrder(input.OrderBy, input.OrderDir, TaskOrderWhitelist, "t.FAPPLYTIME DESC, t.id DESC", "t.id");

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

        var list = await _db.Ado.SqlQueryAsync<FqcTaskListRow>(
            $"""
            SELECT
                t.id            AS Id,
                t.FBILLNO       AS BillNo,
                t.sczld         AS ProductionOrderNo,
                t.FORGID        AS ApplyOrgId,
                t.FAPPLYTIME    AS ApplyTime,
                t.wlbm          AS MaterialCode,
                t.cpmc          AS ProductName,
                t.cpxh          AS ProductModel,
                t.scph          AS ProductionBatchNo,
                t.sczldsl       AS OrderQty,
                t.sl            AS Qty,
                t.FAPPLYUSER    AS Applicant,
                t.FCOMMENT      AS Remark,
                t.yxj           AS Priority,
                t.jykssj        AS InspectStartTime,
                t.jywcsj        AS InspectFinishTime,
                t.jyfzr         AS Inspector,
                t.FINSPECTSTATUS AS InspectProgress
            FROM qms_fqcbj t
            WHERE {whereSql}
            ORDER BY {orderSql}
            LIMIT {pageSize} OFFSET {offset}
            """,
            pars);

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

    /// <summary>
    /// FQC 检验单(结果)列表（只读分页查询，源 qms_qcpp_inspbill + WorkOrdMaster 取 SAP 工单号）。
    /// </summary>
    [DisplayName("FQC检验单结果列表")]
    [HttpGet("result-list")]
    public async Task<object> GetResultList([FromQuery] FqcResultListInput input)
    {
        var page = input.Page <= 0 ? 1 : input.Page;
        var pageSize = input.PageSize <= 0 ? 10 : (input.PageSize > 200 ? 200 : input.PageSize);
        var offset = (page - 1) * pageSize;

        // qms_qcpp_inspbill 无 tenant_id：不做租户过滤（TenantId 接收即忽略）。
        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, "a.FBILLNO", "BillNo");
        AddLike(input.ProductionBatchNo, "a.scph", "ProductionBatchNo");
        AddLike(input.MaterialCode, "a.FMATERIALCFG", "MaterialCode");
        AddLike(input.MaterialName, "a.wlmc", "MaterialName");
        AddLike(input.Inspector, "a.jyr", "Inspector");
        AddLike(input.SourceBillNo, "a.lydjbh", "SourceBillNo");
        AddLike(input.ProductionOrderNo, "a.sczld", "ProductionOrderNo");

        if (!string.IsNullOrWhiteSpace(input.Judgment))
        {
            where.Add("a.pd = @Judgment");
            pars.Add(new SugarParameter("@Judgment", input.Judgment.Trim()));
        }
        if (!string.IsNullOrWhiteSpace(input.InspectDateStart))
        {
            where.Add("a.FINSPEENDDATE >= @InspectDateStart");
            pars.Add(new SugarParameter("@InspectDateStart", $"{input.InspectDateStart.Trim()} 00:00:00"));
        }
        if (!string.IsNullOrWhiteSpace(input.InspectDateEnd))
        {
            where.Add("a.FINSPEENDDATE <= @InspectDateEnd");
            pars.Add(new SugarParameter("@InspectDateEnd", $"{input.InspectDateEnd.Trim()} 23:59:59"));
        }

        var whereSql = string.Join(" AND ", where);
        var orderSql = ResolveOrder(input.OrderBy, input.OrderDir, ResultOrderWhitelist, "a.FINSPEENDDATE DESC, a.id DESC", "a.id");

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

        var list = await _db.Ado.SqlQueryAsync<FqcResultListRow>(
            $"""
            SELECT
                a.id            AS Id,
                a.FBILLNO       AS BillNo,
                a.FINSPEENDDATE AS InspectTime,
                a.scph          AS ProductionBatchNo,
                a.FMATERIALCFG  AS MaterialCode,
                a.wlmc          AS MaterialName,
                a.ggxh          AS Spec,
                a.jyr           AS Inspector,
                a.jysj          AS InspectQty,
                a.lydjbh        AS SourceBillNo,
                a.sczld         AS ProductionOrderNo,
                a.sczldsl       AS OrderQty,
                (SELECT MIN(wo.WorkOrd) FROM WorkOrdMaster wo WHERE wo.Batch = a.sczld) AS SapWorkOrderNo,
                a.pd            AS Judgment,
                a.hgsl          AS QualifiedQty,
                a.bhgsl         AS UnqualifiedQty
            FROM qms_qcpp_inspbill a
            WHERE {whereSql}
            ORDER BY {orderSql}
            LIMIT {pageSize} OFFSET {offset}
            """,
            pars);

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

    /// <summary>
    /// 排序解析：白名单命中则 "col dir, tie"，否则回落默认；方向仅 asc/desc，非法回落 desc。
    /// </summary>
    private static string ResolveOrder(string? orderBy, string? orderDir, Dictionary<string, string> whitelist, string fallback, string tie)
    {
        if (!string.IsNullOrWhiteSpace(orderBy) && whitelist.TryGetValue(orderBy.Trim(), out var col))
        {
            var dir = string.Equals(orderDir?.Trim(), "asc", StringComparison.OrdinalIgnoreCase) ? "ASC" : "DESC";
            return $"{col} {dir}, {tie} DESC";
        }
        return fallback;
    }

    /// <summary>
    /// 归口空表建表（方案 A，DDL 例外、每进程一次、零 DML）：
    /// aidopdev 无 qms_fqcbj 时 CREATE TABLE IF NOT EXISTS 建同构 dopdemo 的 37 列空表，待上游抽数填充。
    /// 仅建表，不写任何数据行。
    /// </summary>
    private async Task EnsureFqcTaskTableAsync()
    {
        if (Interlocked.Exchange(ref _taskTableEnsured, 1) == 1) return;
        try
        {
            await _db.Ado.ExecuteCommandAsync(
                """
                CREATE TABLE IF NOT EXISTS qms_fqcbj (
                    id BIGINT NOT NULL PRIMARY KEY COMMENT '主表id',
                    FBILLNO VARCHAR(80) NULL COMMENT '单据编号',
                    FBILLSTATUS VARCHAR(50) NULL COMMENT '单据状态',
                    FCREATORID BIGINT NULL,
                    FMODIFIERID BIGINT NULL,
                    FAUDITORID BIGINT NULL,
                    FAUDITDATE DATETIME NULL,
                    FMODIFYTIME DATETIME NULL,
                    FCREATETIME DATETIME NULL,
                    FORGID BIGINT NULL COMMENT '申请组织',
                    FQUALITYORG BIGINT NULL,
                    FAPPLYUSER VARCHAR(50) NULL COMMENT '申请人',
                    FAPPLYTIME DATETIME NULL COMMENT '申请时间',
                    FBILLTYPE VARCHAR(50) NULL COMMENT '单据类型',
                    FBIZTYPE BIGINT NULL COMMENT '业务类型',
                    FINSPECORGID BIGINT NULL,
                    FCOMMENT TEXT NULL COMMENT '备注',
                    FAUTHORIZEOBJID BIGINT NULL,
                    FINTERFACEID VARCHAR(50) NULL,
                    cpmc VARCHAR(255) NULL COMMENT '产品名称',
                    cpxh VARCHAR(255) NULL COMMENT '产品型号',
                    scph VARCHAR(255) NULL COMMENT '生产批号',
                    sl DECIMAL(10,0) NULL COMMENT '数量',
                    jfbh TEXT NULL COMMENT '击发编号',
                    wlbm VARCHAR(255) NULL COMMENT '物料编码',
                    jyfzr VARCHAR(255) NULL COMMENT '检验负责人',
                    yxj VARCHAR(255) NULL COMMENT '检验优先级',
                    jykssj DATETIME NULL COMMENT '检验开始时间',
                    jywcsj DATETIME NULL COMMENT '检验完成时间',
                    mjpc VARCHAR(255) NULL,
                    tsyq VARCHAR(255) NULL,
                    FINSPECTSTATUS VARCHAR(255) NULL COMMENT '检验进度',
                    sczld VARCHAR(255) NULL COMMENT '生产指令单',
                    sczldsl DECIMAL(10,0) NULL COMMENT '指令单数量',
                    lydjbh VARCHAR(100) NULL COMMENT '来源单据编号',
                    ISscrap INT NULL,
                    CirculationCard TEXT NULL
                ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='FQC检验任务(报检) 归口自旧系统dopdemo，空表待上游抽数'
                """);
        }
        catch
        {
            // 建表失败（如权限/并发）不吞成静默通过：复位守卫，让后续调用可再尝试，并向上抛出真实错误。
            Interlocked.Exchange(ref _taskTableEnsured, 0);
            throw;
        }
    }
}