namespace Admin.NET.Plugin.AiDOP.Order; /// /// 需求明细核验(资源检查运算明细,只读查询) /// 路由前缀:/api/Order/examine-detail/... /// [ApiDescriptionSettings(Order = 255, Description = "需求明细核验")] [Route("api/Order")] [AllowAnonymous] [NonUnify] public class RequirementExamineDetailService : IDynamicApiController, ITransient { private readonly ISqlSugarClient _db; public RequirementExamineDetailService(ISqlSugarClient db) { _db = db; } /// /// 分页查询需求明细核验(扁平行;前端组树) /// [DisplayName("需求明细分页列表")] [HttpGet("examine-detail/list")] public async Task PageRequirementExamineDetail([FromQuery] RequirementExamineDetailListInput input) { var pars = new List(); var where = new List { "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(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"; } }