public static DynamicSql SqlSort(this DynamicSql query, ScheduleDetailQuerySort model) { query = DynamicSql.DeepClone(query); var listSorts = new List <string>(); foreach (var s in model._sortsArr) { var asc = s[0] == 'a'; var fieldName = s.Remove(0, 1); switch (fieldName) { case ScheduleDetailQuerySort.NAME: { listSorts.Add($"{nameof(ScheduleDetail)}" + $".{nameof(ScheduleDetail.Name)}{(asc ? "" : " DESC")}"); } break; case ScheduleDetailQuerySort.START_TIME: { listSorts.Add($"{nameof(ScheduleDetail)}" + $".{nameof(ScheduleDetail.FromTime)}{(asc ? "" : " DESC")}"); } break; } } if (listSorts.Any()) { var orderByClause = "ORDER BY " + string.Join(',', listSorts); query.SortClause = orderByClause; } return(query); }
public async Task <IActionResult> Get([FromQuery] ScheduleDetailQueryFilter filter, [FromQuery] ScheduleDetailQuerySort sort, [FromQuery] ScheduleDetailQueryProjection projection, [FromQuery] ScheduleDetailQueryPaging paging, [FromQuery] ScheduleDetailQueryOptions options) { var validationResult = _service.ValidateGetScheduleDetails( User, filter, sort, projection, paging, options); if (!validationResult.Valid) { return(BadRequest(validationResult.Result)); } var result = await _service.QueryScheduleDetailDynamic( projection, options, filter, sort, paging); if (options.single_only) { if (result == null) { return(NotFound(new AppResultBuilder().NotFound())); } return(Ok(new AppResultBuilder().Success(result.SingleResult))); } return(Ok(new AppResultBuilder().Success(result))); }
public ValidationResult ValidateGetScheduleDetails( ClaimsPrincipal principal, ScheduleDetailQueryFilter filter, ScheduleDetailQuerySort sort, ScheduleDetailQueryProjection projection, ScheduleDetailQueryPaging paging, ScheduleDetailQueryOptions options) { return(ValidationResult.Pass()); }
public async Task <QueryResult <ScheduleDetailQueryRow> > QueryScheduleDetail( ScheduleDetailQueryFilter filter = null, ScheduleDetailQuerySort sort = null, ScheduleDetailQueryProjection projection = null, ScheduleDetailQueryPaging paging = null, ScheduleDetailQueryOptions options = null) { var conn = context.Database.GetDbConnection(); var openConn = conn.OpenAsync(); var query = ScheduleDetailQuery.CreateDynamicSql(); #region General if (filter != null) { query = query.SqlFilter(filter); } if (projection != null) { query = query.SqlJoin(projection); } DynamicSql countQuery = null; int?totalCount = null; Task <int> countTask = null; if (options != null && options.count_total) { countQuery = query.SqlCount("*"); } if (projection != null) { query = query.SqlProjectFields(projection); } #endregion await openConn; if (options != null && !options.single_only) { #region List query if (sort != null) { query = query.SqlSort(sort); } if (paging != null && (!options.load_all || !ScheduleDetailQueryOptions.IsLoadAllAllowed)) { query = query.SqlSelectPage(paging.page, paging.limit); } #endregion #region Count query if (options.count_total) { countTask = conn.ExecuteScalarAsync <int>( sql: countQuery.PreparedForm, param: countQuery.DynamicParameters); } #endregion } if (projection != null) { query = query.SqlExtras(projection); } var multipleResult = await conn.QueryMultipleAsync( sql: query.PreparedForm, param : query.DynamicParameters); using (multipleResult) { var queryResult = multipleResult.Read( types: query.GetTypesArr(), map: (objs) => ProcessMultiResults(query, objs), splitOn: string.Join(',', query.GetSplitOns())); if (projection != null) { var extraKeys = projection.GetFieldsArr() .Where(f => ScheduleDetailQueryProjection.Extras.ContainsKey(f)); IEnumerable <ScheduleWeekConfig> configs = null; foreach (var key in extraKeys) { switch (key) { case ScheduleDetailQueryProjection.CONFIGS: configs = multipleResult.Read <ScheduleWeekConfig>().ToList(); break; } } ProcessExtras(queryResult, configs); } if (options != null && options.single_only) { var single = queryResult.FirstOrDefault(); if (single == null) { return(null); } return(new QueryResult <ScheduleDetailQueryRow> { SingleResult = single }); } if (options != null && options.count_total) { totalCount = await countTask; } return(new QueryResult <ScheduleDetailQueryRow> { Results = queryResult, TotalCount = totalCount }); } }