AiDOPWarehouse/server/Plugins/Admin.NET.Plugin.AiDOP/ProcurementExecution/IqcReturnQueryService.cs

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

namespace Admin.NET.Plugin.AiDOP.ProcurementExecution;

/// <summary>
/// S4 IQC 退货查询（只读列表）。数据：qms_qcp_insappnentry + qms_qcp_inspecapplyn + qms_qcp_inspbill，可选 PurOrdDetail / SuppMaster。
/// 路由：/api/ProcurementExecution/iqc-return/list
///
/// 【租户安全边界】租户一律经 <see cref="AidopTenantScope.ResolveOrThrow"/> 从认证后 JWT 解析，
/// 无有效租户即拒绝，不读前端 tenantId、无默认回退。
/// <para>
/// 原实现：类级 <c>[AllowAnonymous]</c> + <c>AidopTenantHelper.Resolve(App.HttpContext)</c>
/// （解析链含 query 参数 tenantId 与 DefaultTenantId 兜底），匿名携带 <c>?tenantId=&lt;任意租户&gt;</c> 可读回该租户 IQC 退货数据。
/// </para>
/// </summary>
[ApiDescriptionSettings(Order = 322, Description = "S4 IQC退货查询")]
[Route("api/ProcurementExecution")]
[NonUnify]
public class IqcReturnQueryService : IDynamicApiController, ITransient
{
    private readonly ISqlSugarClient _db;
    private readonly UserManager _userManager;

    public IqcReturnQueryService(ISqlSugarClient db, UserManager userManager) { _db = db; _userManager = userManager; }

    /// <summary>
    /// 退货相关处理方式：NULL 视作 0；与 aidopdev 中 clfs 枚举一致时可再收紧为仅 clfs=3。
    /// </summary>
    private const string ReturnClfsPredicate = "(IFNULL(a.clfs, 0) IN (0, 3))";

    private static string NormalizeOrderBy(string field, string order)
    {
        var col = (field ?? "").Trim().ToLowerInvariant();
        var ord = string.Equals(order?.Trim(), "desc", StringComparison.OrdinalIgnoreCase) ? "DESC" : "ASC";
        var map = new Dictionary<string, string>(StringComparer.OrdinalIgnoreCase)
        {
            ["fbillno"] = "t.FBillNo",
            ["ordnbr"] = "t.OrdNbr",
            ["pch"] = "t.Pch",
            ["fmaterialcfg"] = "t.FMaterialCfg",
            ["sortname"] = "t.SortName",
            ["supp"] = "t.Supp",
            ["gysmc"] = "t.Gysmc",
            ["wlmc"] = "t.Wlmc",
            ["ggxh"] = "t.Ggxh",
            ["frinsqty"] = "t.FrInsQty",
            ["bhgsl"] = "t.Bhgsl",
            ["clfs"] = "t.Clfs",
            ["finspectstatus"] = "t.FInspectStatus",
            ["fapplytime"] = "t.FApplyTime",
            ["finspeenddate"] = "t.FInspeEndDate",
        };
        if (!map.TryGetValue(col, out var sqlCol))
            return "ORDER BY t.FApplyTime DESC, t.EntryId ASC";
        return $"ORDER BY {sqlCol} {ord}, t.EntryId ASC";
    }

    [DisplayName("IQC退货查询分页列表")]
    [HttpGet("iqc-return/list")]
    public async Task<object> GetList([FromQuery] IqcReturnListInput input)
    {
        var page = input.Page <= 0 ? 1 : input.Page;
        var pageSize = input.PageSize <= 0 ? 10 : input.PageSize;
        var offset = (page - 1) * pageSize;

        var tenantId = AidopTenantScope.ResolveOrThrow(_userManager);
        var cond = new List<string> { ReturnClfsPredicate };
        var pars = new List<SugarParameter>();

        var hasTenantColumn = await _db.Ado.GetIntAsync("""
            SELECT COUNT(1)
            FROM information_schema.COLUMNS
            WHERE TABLE_SCHEMA = DATABASE()
              AND TABLE_NAME = 'qms_qcp_insappnentry'
              AND COLUMN_NAME = 'tenant_id'
            """) > 0;
        if (hasTenantColumn)
        {
            // WP0-D4：申请明细/主表/检验单同租户
            cond.Add("c.tenant_id = @TenantId");
            cond.Add("b.tenant_id = @TenantId");
            cond.Add("(a.id IS NULL OR a.tenant_id = @TenantId)");
            pars.Add(new("@TenantId", tenantId));
        }

        if (!string.IsNullOrWhiteSpace(input.OrdNbr))
        {
            cond.Add("(TRIM(IFNULL(c.FSRCORDERNUM,'')) LIKE @OrdNbr OR TRIM(IFNULL(pod.PurOrd,'')) LIKE @OrdNbr)");
            pars.Add(new SugarParameter("@OrdNbr", $"%{input.OrdNbr.Trim()}%"));
        }

        if (!string.IsNullOrWhiteSpace(input.Pch))
        {
            cond.Add("TRIM(IFNULL(COALESCE(a.pch, c.FLOTNUMBER, ''),'')) LIKE @Pch");
            pars.Add(new SugarParameter("@Pch", $"%{input.Pch.Trim()}%"));
        }

        if (!string.IsNullOrWhiteSpace(input.FMaterialCfg))
        {
            cond.Add("TRIM(IFNULL(COALESCE(c.FMATERIALCFG, a.FMATERIALCFG, pod.ItemNum),'')) = TRIM(@FMat)");
            pars.Add(new SugarParameter("@FMat", input.FMaterialCfg.Trim()));
        }

        if (!string.IsNullOrWhiteSpace(input.SortName))
        {
            cond.Add("""
                (
                  EXISTS (
                    SELECT 1 FROM SuppMaster s
                    WHERE TRIM(IFNULL(c.FSUPPLIER,'')) <> ''
                      AND s.Supp = TRIM(IFNULL(c.FSUPPLIER,''))
                      AND (pod.Domain IS NULL OR s.Domain = pod.Domain)
                      AND TRIM(IFNULL(s.SortName,'')) LIKE @SortName
                    LIMIT 1
                  )
                  OR TRIM(IFNULL(COALESCE(c.gysmc, a.gysmc, ''),'')) LIKE @SortName
                )
                """);
            pars.Add(new SugarParameter("@SortName", $"%{input.SortName.Trim()}%"));
        }

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

        var baseSql = $"""
            SELECT
              CAST(c.id AS CHAR(32)) AS RowKey,
              c.id AS EntryId,
              TRIM(IFNULL(
                NULLIF(TRIM(CAST(IFNULL(b.FBILLNO,'') AS CHAR(512))), ''),
                TRIM(IFNULL(a.FBILLNO,''))
              )) AS FBillNo,
              TRIM(IFNULL(c.FSRCORDERNUM,'')) AS OrdNbr,
              TRIM(IFNULL(COALESCE(a.pch, c.FLOTNUMBER, ''),'')) AS Pch,
              TRIM(IFNULL(COALESCE(c.FMATERIALCFG, a.FMATERIALCFG, pod.ItemNum),'')) AS FMaterialCfg,
              (SELECT TRIM(IFNULL(s.SortName,''))
               FROM SuppMaster s
               WHERE TRIM(IFNULL(c.FSUPPLIER,'')) <> ''
                 AND s.Supp = TRIM(IFNULL(c.FSUPPLIER,''))
                 AND (pod.Domain IS NULL OR s.Domain = pod.Domain)
               LIMIT 1) AS SortName,
              TRIM(IFNULL(c.FSUPPLIER,'')) AS Supp,
              TRIM(IFNULL(COALESCE(c.gysmc, a.gysmc, ''),'')) AS Gysmc,
              TRIM(IFNULL(COALESCE(c.wlmc, a.wlmc, ''),'')) AS Wlmc,
              TRIM(IFNULL(COALESCE(c.ggxh, a.ggxh, ''),'')) AS Ggxh,
              CAST(IFNULL(a.FRINSQTY, 0) AS DECIMAL(18,4)) AS FrInsQty,
              CAST(IFNULL(a.bhgsl, 0) AS DECIMAL(18,4)) AS Bhgsl,
              a.clfs AS Clfs,
              TRIM(IFNULL(a.thyy,'')) AS Thyy,
              TRIM(IFNULL(c.FINSPECTSTATUS,'')) AS FInspectStatus,
              b.FAPPLYTIME AS FApplyTime,
              a.FINSPEENDDATE AS FInspeEndDate
            FROM qms_qcp_insappnentry c
            INNER JOIN qms_qcp_inspecapplyn b ON b.id = c.glid
            LEFT JOIN qms_qcp_inspbill a
              ON BINARY TRIM(IFNULL(a.lydjbh,'')) = BINARY TRIM(CAST(IFNULL(b.FBILLNO,'') AS CHAR(512)))
            LEFT JOIN PurOrdDetail pod
              ON TRIM(IFNULL(c.FSRCORDERNUM,'')) <> ''
              AND pod.PurOrd = TRIM(IFNULL(c.FSRCORDERNUM,''))
              AND TRIM(IFNULL(c.FSRCBILLENTRYSEQ,'')) <> ''
              AND CAST(pod.Line AS CHAR(32)) = TRIM(IFNULL(c.FSRCBILLENTRYSEQ,''))
            WHERE {where}
            """;

        var orderBy = NormalizeOrderBy(input.SortField, input.SortOrder);

        var total = await _db.Ado.GetIntAsync($"SELECT COUNT(1) FROM ({baseSql}) t", pars);
        var list = await _db.Ado.SqlQueryAsync<IqcReturnListRow>(
            $"SELECT * FROM ({baseSql}) t {orderBy} LIMIT {pageSize} OFFSET {offset}", pars);

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