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;
    private readonly UserManager _userManager;

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

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

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

        if (!string.IsNullOrWhiteSpace(input.MorderNo))
        {
            where.Add("er.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,
                bce.parent_id AS ParentRowId,
                bce.num 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,
                bce.type AS Type,
                bce.erp_cls_name AS ErpClsName,
                bce.qty AS Qty,
                IFNULL(bce.wastage, 0) AS Wastage,
                bce.needCount AS NeedCount,
                bce.sqty AS Sqty,
                bce.use_qty AS UseQty,
                bce.self_lack_qty AS SelfLackQty,
                bce.lack_qty AS LackQty,
                bce.mo_qty AS MoQty,
                bce.make_qty AS MakeQty,
                bce.purchase_qty AS PurchaseQty,
                bce.purchase_occupy_qty AS PurchaseOccupyQty,
                DATE_FORMAT(bce.satisfy_time, '%Y-%m-%d') AS SatisfyTime,
                bce.haveicsubs AS HaveIcSubs,
                bce.substitute_code AS SubstituteCode,
                er.bill_no AS BillNo,
                er.morder_no AS MorderNo,
                bce.create_time AS CreateTime
            FROM b_examine_result er
            INNER JOIN (
                SELECT morder_no, MAX(Id) AS MaxId
                FROM b_examine_result
                WHERE tenant_id = @TenantId AND IFNULL(IsDeleted, 0) = 0
                GROUP BY morder_no
            ) latest ON er.Id = latest.MaxId
            INNER JOIN b_bom_child_examine bce ON er.Id = bce.examine_id AND bce.tenant_id = er.tenant_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";
    }
}