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
/// </summary>
[ApiDescriptionSettings(Order = 322, Description = "S4 IQC退货查询")]
[Route("api/ProcurementExecution")]
[AllowAnonymous]
[NonUnify]
public class IqcReturnQueryService : IDynamicApiController, ITransient
{
    private readonly ISqlSugarClient _db;

    public IqcReturnQueryService(ISqlSugarClient db) => _db = db;

    /// <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))
            sqlCol = "t.FApplyTime";
        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 cond = new List<string> { ReturnClfsPredicate };
        var pars = new List<SugarParameter>();

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