AiDOPWarehouse/server/Plugins/Admin.NET.Plugin.AiDOP/Order/RequirementExamineDetailService.cs

namespace Admin.NET.Plugin.AiDOP.Order;

/// <summary>
/// 需求明细核验（资源检查运算明细，只读查询）
/// 路由前缀：/api/Order/examine-detail/...
/// </summary>
[ApiDescriptionSettings(Order = 255, Description = "需求明细核验")]
[Route("api/Order")]
[AllowAnonymous]
[NonUnify]
public class RequirementExamineDetailService : IDynamicApiController, ITransient
{
    private readonly ISqlSugarClient _db;

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

    /// <summary>
    /// 分页查询需求明细核验（扁平行；前端组树）
    /// </summary>
    [DisplayName("需求明细分页列表")]
    [HttpGet("examine-detail/list")]
    public async Task<object> PageRequirementExamineDetail([FromQuery] RequirementExamineDetailListInput input)
    {
        var pars = new List<SugarParameter>();
        var where = new List<string> { "bce.is_use = 1" };

        if (!string.IsNullOrWhiteSpace(input.BillNo))
        {
            where.Add("sen.bill_no LIKE @BillNo");
            pars.Add(new SugarParameter("@BillNo", $"%{input.BillNo.Trim()}%"));
        }

        if (!string.IsNullOrWhiteSpace(input.MorderNo))
        {
            where.Add("ber.morder_no LIKE @MorderNo");
            pars.Add(new SugarParameter("@MorderNo", $"%{input.MorderNo.Trim()}%"));
        }

        if (!string.IsNullOrWhiteSpace(input.ItemNumber))
        {
            where.Add("bce.item_number = @ItemNumber");
            pars.Add(new SugarParameter("@ItemNumber", input.ItemNumber.Trim()));
        }

        var whereSql = string.Join(" AND ", where);
        var orderExpr = ResolveOrderClause(input.OrderBy, input.Sort);

        var baseSql = $"""
            SELECT
                bce.id AS RowId,
                CASE
                    WHEN bce.num = 1 THEN NULL
                    ELSE (
                        SELECT MIN(x.id)
                        FROM b_bom_child_examine x
                        WHERE x.examine_id = bce.examine_id AND x.num = 1 AND x.is_use = 1
                    )
                END AS ParentRowId,
                CAST(bce.num AS CHAR) AS Num,
                bce.item_number AS ItemNumber,
                bce.item_name AS ItemName,
                bce.bom_number AS BomNumber,
                bce.model AS Model,
                DATE_FORMAT(bce.kitting_time, '%Y-%m-%d') AS KittingTime,
                CASE WHEN bce.type = 1 THEN '替代件' ELSE '标准件' END AS Type,
                CASE bce.erp_cls
                    WHEN 0 THEN '配置类'
                    WHEN 1 THEN '自制'
                    WHEN 2 THEN '委外加工'
                    WHEN 3 THEN '外购'
                    WHEN 4 THEN '虚拟件'
                END AS ErpClsName,
                CAST(bce.qty AS DECIMAL(18,4)) AS Qty,
                CAST(IFNULL(bce.wastage, 0) AS DECIMAL(18,4)) AS Wastage,
                CAST(bce.needCount AS DECIMAL(18,4)) AS NeedCount,
                CAST(bce.sqty AS DECIMAL(18,4)) AS Sqty,
                CAST(bce.use_qty AS DECIMAL(18,4)) AS UseQty,
                CAST(bce.self_lack_qty AS DECIMAL(18,4)) AS SelfLackQty,
                CAST(bce.lack_qty AS DECIMAL(18,4)) AS LackQty,
                CAST(bce.mo_qty AS DECIMAL(18,4)) AS MoQty,
                CAST(IF(bce.make_qty = 0 AND bce.erp_cls = 1, bce.lack_qty, bce.make_qty) AS DECIMAL(18,4)) AS MakeQty,
                CAST(bce.purchase_qty AS DECIMAL(18,4)) AS PurchaseQty,
                CAST(bce.purchase_occupy_qty AS DECIMAL(18,4)) AS PurchaseOccupyQty,
                DATE_FORMAT(bce.satisfy_time, '%Y-%m-%d') AS SatisfyTime,
                CASE WHEN bce.haveicsubs = 1 THEN '是' ELSE '否' END AS HaveIcSubs,
                bce.substitute_code AS SubstituteCode,
                sen.bill_no AS BillNo,
                ber.morder_no AS MorderNo,
                ber.create_time AS CreateTime
            FROM b_examine_result ber
            INNER JOIN b_bom_child_examine bce ON ber.Id = bce.examine_id
            LEFT JOIN crm_seorderentry sen ON ber.sentry_id = sen.id
            WHERE {whereSql}
            """;

        var countSql = $"SELECT COUNT(*) FROM ({baseSql}) AS t";
        var total = await _db.Ado.GetIntAsync(countSql, pars);

        var offset = (input.Page - 1) * input.PageSize;
        var dataSql = $"""
            SELECT * FROM ({baseSql}) AS t
            ORDER BY {orderExpr}
            LIMIT {input.PageSize} OFFSET {offset}
            """;

        var list = await _db.Ado.SqlQueryAsync<RequirementExamineDetailRow>(dataSql, pars);

        // 分页内重算序号（与当前页一致）
        var snoStart = offset + 1;
        for (var i = 0; i < list.Count; i++)
            list[i].Sno = snoStart + i;

        return new
        {
            total,
            page = input.Page,
            pageSize = input.PageSize,
            list
        };
    }

    private static string ResolveOrderClause(string? orderBy, string? sort)
    {
        var desc = string.Equals(sort?.Trim(), "desc", StringComparison.OrdinalIgnoreCase);
        var dir = desc ? "DESC" : "ASC";
        var key = orderBy?.Trim();

        var col = key?.ToLowerInvariant() switch
        {
            "sno" => "t.RowId",
            "rowid" => "t.RowId",
            "billno" => "t.BillNo",
            "morderno" => "t.MorderNo",
            "itemnumber" => "t.ItemNumber",
            "kittingtime" => "t.KittingTime",
            _ => "t.RowId"
        };

        return $"{col} {dir}, t.RowId ASC";
    }
}