public async Task <IActionResult> Get([FromQuery] ScheduleQueryFilter filter, [FromQuery] ScheduleQuerySort sort, [FromQuery] ScheduleQueryProjection projection, [FromQuery] ScheduleQueryPaging paging, [FromQuery] ScheduleQueryOptions options) { var validationResult = _service.ValidateGetSchedules( User, filter, sort, projection, paging, options); if (!validationResult.Valid) { return(BadRequest(validationResult.Result)); } var result = await _service.QueryScheduleDynamic( 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 ValidateGetSchedules( ClaimsPrincipal principal, ScheduleQueryFilter filter, ScheduleQuerySort sort, ScheduleQueryProjection projection, ScheduleQueryPaging paging, ScheduleQueryOptions options) { return(ValidationResult.Pass()); }
public static DynamicSql SqlJoin( this DynamicSql query, ScheduleQueryProjection model) { query = DynamicSql.DeepClone(query); var joins = model.GetFieldsArr() .Where(f => ScheduleQueryProjection.Joins.ContainsKey(f)) .Select(f => ScheduleQueryProjection.Joins[f]); if (joins.Any()) { var joinClause = string.Join('\n', joins); query.DynamicForm = query.DynamicForm .Replace(DynamicSql.JOIN, joinClause); } return(query); }
public static DynamicSql SqlExtras( this DynamicSql query, ScheduleQueryProjection model) { query = DynamicSql.DeepClone(query); var extras = model.GetFieldsArr() .Where(f => ScheduleQueryProjection.Extras.ContainsKey(f)) .Select(f => ScheduleQueryProjection.Extras[f]); if (extras.Any()) { var extraSqls = string.Join(';', extras); var originalQuery = query.PreparedViewForm; query.DynamicForm += ";\n" + extraSqls; query.DynamicForm = query.DynamicForm .Replace(ScheduleQueryPlaceholder.SCHEDULE_SUB_QUERY, originalQuery); } return(query); }
public QueryResult <IDictionary <string, object> > GetScheduleDynamic( IEnumerable <ScheduleQueryRow> rows, ScheduleQueryProjection projection, ScheduleQueryOptions options, int?totalCount = null) { var list = new List <IDictionary <string, object> >(); foreach (var o in rows) { var obj = GetScheduleDynamic(o, projection, options); list.Add(obj); } var resp = new QueryResult <IDictionary <string, object> >(); resp.Results = list; if (options.count_total) { resp.TotalCount = totalCount; } return(resp); }
public static DynamicSql SqlProjectFields( this DynamicSql query, ScheduleQueryProjection model) { query = DynamicSql.DeepClone(query); var finalFields = model.GetFieldsArr() .Where(f => ScheduleQueryProjection.Projections.ContainsKey(f)) .Select(f => ScheduleQueryProjection.Projections[f]); if (finalFields.Any()) { var projectionClause = string.Join(',', finalFields); query.DynamicForm = query.DynamicForm .Replace(DynamicSql.PROJECTION, projectionClause); } var finalResults = model.GetFieldsArr() .Where(f => ScheduleQueryProjection.Results.ContainsKey(f)) .Select(f => ScheduleQueryProjection.Results[f]); query.MultiResults.AddRange(finalResults); return(query); }
public IDictionary <string, object> GetScheduleDynamic( ScheduleQueryRow row, ScheduleQueryProjection projection, ScheduleQueryOptions options) { var obj = new Dictionary <string, object>(); foreach (var f in projection.GetFieldsArr()) { switch (f) { case ScheduleQueryProjection.INFO: { var entity = row.Schedule; obj["id"] = entity.Id; obj["code"] = entity.Code; obj["name"] = entity.Name; obj["description"] = entity.Description; obj["location_id"] = entity.LocationId; } break; case ScheduleQueryProjection.DETAILS: { var entities = row.Schedule.ScheduleDetails; obj["schedule_details"] = entities?.Select(o => { var fromTime = o.FromTime? .ToTimeZone(options.time_zone, options.culture); var fromTimeStr = fromTime? .ToString(options.date_format, options.culture); var toTime = o.ToTime? .ToTimeZone(options.time_zone, options.culture); var toTimeStr = toTime? .ToString(options.date_format, options.culture); return(new { name = o.Name, from_time = new { display = fromTimeStr, iso = $"{fromTime?.ToUniversalTime():s}Z" }, to_time = new { display = toTimeStr, iso = $"{toTime?.ToUniversalTime():s}Z" }, id = o.Id, is_default = o.IsDefault, }); }); } break; case ScheduleQueryProjection.LOCATION: { var entity = row.Location; obj["location"] = new { id = entity.Id, name = entity.Name, code = entity.Code }; } break; case ScheduleQueryProjection.SELECT: { var entity = row.Schedule; obj["id"] = entity.Id; obj["name"] = entity.Name; obj["code"] = entity.Code; } break; } } return(obj); }
public async Task <QueryResult <ScheduleQueryRow> > QuerySchedule( ScheduleQueryFilter filter = null, ScheduleQuerySort sort = null, ScheduleQueryProjection projection = null, ScheduleQueryPaging paging = null, ScheduleQueryOptions options = null) { var conn = context.Database.GetDbConnection(); var openConn = conn.OpenAsync(); var query = ScheduleQuery.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 || !ScheduleQueryOptions.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 => ScheduleQueryProjection.Extras.ContainsKey(f)); IEnumerable <ScheduleDetail> details = null; foreach (var key in extraKeys) { switch (key) { case ScheduleQueryProjection.DETAILS: details = multipleResult.Read <ScheduleDetail>().ToList(); break; } } ProcessExtras(queryResult, details); } if (options != null && options.single_only) { var single = queryResult.FirstOrDefault(); if (single == null) { return(null); } return(new QueryResult <ScheduleQueryRow> { SingleResult = single }); } if (options != null && options.count_total) { totalCount = await countTask; } return(new QueryResult <ScheduleQueryRow> { Results = queryResult, TotalCount = totalCount }); } }