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 = _userManager.TenantId;
        var pars = new List<SugarParameter> { new("@TenantId", tenantId) };
        var where = new List<string>
        {
            "d.tenant_id = @TenantId",
            """
            d.calc_batch_id = (
                SELECT r.batch_id
                FROM mdp_transform_run_log r
                WHERE r.job_code = 'S1_MDP_SYNC_TRANSFORM'
                  AND r.status = 'SUCCESS'
                ORDER BY r.start_time DESC, r.id DESC
                LIMIT 1
            )
            """
        };

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

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

        if (!string.IsNullOrWhiteSpace(input.ItemNumber))
        {
            where.Add("d.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
                d.row_id AS RowId,
                d.parent_row_id AS ParentRowId,
                d.num AS Num,
                d.item_number AS ItemNumber,
                d.item_name AS ItemName,
                d.bom_number AS BomNumber,
                d.model AS Model,
                DATE_FORMAT(d.kitting_time, '%Y-%m-%d') AS KittingTime,
                d.item_type AS Type,
                d.erp_cls_name AS ErpClsName,
                d.qty AS Qty,
                IFNULL(d.wastage, 0) AS Wastage,
                d.need_count AS NeedCount,
                d.sqty AS Sqty,
                d.use_qty AS UseQty,
                d.self_lack_qty AS SelfLackQty,
                d.lack_qty AS LackQty,
                d.mo_qty AS MoQty,
                d.make_qty AS MakeQty,
                d.purchase_qty AS PurchaseQty,
                d.purchase_occupy_qty AS PurchaseOccupyQty,
                DATE_FORMAT(d.satisfy_time, '%Y-%m-%d') AS SatisfyTime,
                d.have_ic_subs AS HaveIcSubs,
                d.substitute_code AS SubstituteCode,
                d.bill_no AS BillNo,
                d.morder_no AS MorderNo,
                d.create_time AS CreateTime
            FROM dwd_requirement_examine_detail d
            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";
    }
}