public async Task <IActionResult> Get([FromQuery] EntityCategoryQueryFilter filter, [FromQuery] EntityCategoryQuerySort sort, [FromQuery] EntityCategoryQueryProjection projection, [FromQuery] EntityCategoryQueryPaging paging, [FromQuery] EntityCategoryQueryOptions options) { var validationResult = _service.ValidateGetEntityCategories( User, filter, sort, projection, paging, options); if (!validationResult.Valid) { return(BadRequest(validationResult.Result)); } var result = await _service.QueryEntityCategoryDynamic( 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 static DynamicSql SqlFilter( this DynamicSql query, EntityCategoryQueryFilter filter) { query = DynamicSql.DeepClone(query); var listFilters = new List <string>(); if (filter.id != null) { var paramName = query.AddAutoIncrParam(filter.id); listFilters.Add($"{nameof(EntityCategory)}.{nameof(EntityCategory.Id)}=@{paramName}"); } if (filter.name_contains != null) { var paramName = query.AddAutoIncrParam(filter.name_contains); listFilters.Add($"CHARINDEX(@{paramName}, {nameof(EntityCategoryContent)}" + $".{nameof(EntityCategoryContent.Name)}) > 0"); } if (filter.archived != 2) { var paramName = query.AddAutoIncrParam(filter.archived); listFilters.Add($"{nameof(EntityCategory)}.{nameof(EntityCategory.Archived)}=@{paramName}"); } if (listFilters.Any()) { var whereClause = "WHERE " + string.Join(" AND ", listFilters); query.DynamicForm = query.DynamicForm.Replace(DynamicSql.FILTER, whereClause); } return(query); }
public static DynamicSql SqlJoin( this DynamicSql query, EntityCategoryQueryProjection model, EntityCategoryQueryFilter filter) { query = DynamicSql.DeepClone(query); var joins = model.GetFieldsArr() .Where(f => EntityCategoryQueryProjection.Joins.ContainsKey(f)) .Select(f => EntityCategoryQueryProjection.Joins[f]); if (joins.Any()) { var joinClause = string.Join('\n', joins); query.DynamicForm = query.DynamicForm .Replace(DynamicSql.JOIN, joinClause); if (filter != null) { var contentFilters = new List <string>(); if (filter.lang != null) { var paramName = query.AddAutoIncrParam(filter.lang); var postContentLang = $"{nameof(EntityCategoryContent)}.{nameof(EntityCategoryContent.Lang)}"; contentFilters.Add($"{postContentLang}=@{paramName}"); } if (contentFilters.Any()) { var whereClause = "WHERE " + string.Join(" AND ", contentFilters); query.DynamicForm = query.DynamicForm .Replace(EntityCategoryQueryPlaceholder.EC_CONTENT_FILTER, whereClause); } } } return(query); }
public ValidationResult ValidateGetEntityCategories( ClaimsPrincipal principal, EntityCategoryQueryFilter filter, EntityCategoryQuerySort sort, EntityCategoryQueryProjection projection, EntityCategoryQueryPaging paging, EntityCategoryQueryOptions options) { return(ValidationResult.Pass()); }
public static DynamicSql SqlExtras( this DynamicSql query, EntityCategoryQueryProjection model, EntityCategoryQueryFilter filter) { query = DynamicSql.DeepClone(query); var extras = model.GetFieldsArr() .Where(f => EntityCategoryQueryProjection.Extras.ContainsKey(f)) .Select(f => EntityCategoryQueryProjection.Extras[f]); if (extras.Any()) { var extraSqls = string.Join(';', extras); var originalQuery = query.PreparedViewForm; query.DynamicForm += ";\n" + extraSqls; query.DynamicForm = query.DynamicForm .Replace(EntityCategoryQueryPlaceholder.EC_SUB_QUERY, originalQuery); if (filter != null) { if (model.fields.Contains(EntityCategoryQueryProjection.RESOURCES)) { var contentFilters = new List <string>(); if (filter.floor_id != null) { var paramName = query.AddAutoIncrParam(filter.floor_id); var floorId = $"{nameof(Resource)}.{nameof(Resource.FloorId)}"; contentFilters.Add($"{floorId}=@{paramName}"); } if (contentFilters.Any()) { var whereClause = "WHERE " + string.Join(" AND ", contentFilters); query.DynamicForm = query.DynamicForm .Replace(EntityCategoryQueryPlaceholder.RESOURCES_FILTER, whereClause); } } } } return(query); }
public async Task <QueryResult <EntityCategoryQueryRow> > QueryEntityCategory( EntityCategoryQueryFilter filter = null, EntityCategoryQuerySort sort = null, EntityCategoryQueryProjection projection = null, EntityCategoryQueryPaging paging = null, EntityCategoryQueryOptions options = null) { var conn = context.Database.GetDbConnection(); var openConn = conn.OpenAsync(); var query = EntityCategoryQuery.CreateDynamicSql(); #region General if (filter != null) { query = query.SqlFilter(filter); } if (projection != null) { query = query.SqlJoin(projection, filter); } 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 || !EntityCategoryQueryOptions.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, filter); } 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 => EntityCategoryQueryProjection.Extras.ContainsKey(f)); IEnumerable <CateOfResQueryRow> resources = null; foreach (var key in extraKeys) { switch (key) { case EntityCategoryQueryProjection.RESOURCES: resources = GetCategoriesQueryResult(multipleResult); break; } } ProcessExtras(queryResult, resources); } if (options != null && options.single_only) { var single = queryResult.FirstOrDefault(); if (single == null) { return(null); } return(new QueryResult <EntityCategoryQueryRow> { SingleResult = single }); } if (options != null && options.count_total) { totalCount = await countTask; } return(new QueryResult <EntityCategoryQueryRow> { Results = queryResult, TotalCount = totalCount }); } }