public async Task Read_total_competitions_supports_case_insensitive_filter_by_partial_player_type() { var routeNormaliser = new Mock <IRouteNormaliser>(); var competitionDataSource = new SqlServerCompetitionDataSource(_databaseFixture.ConnectionFactory, routeNormaliser.Object); var query = new CompetitionFilter { Query = "JuNioR" }; var result = await competitionDataSource.ReadTotalCompetitions(query).ConfigureAwait(false); Assert.Equal(_databaseFixture.Competitions.Count(x => x.PlayerType.ToString().Contains(query.Query, StringComparison.OrdinalIgnoreCase)), result); }
/// <summary> /// Gets the number of competitions that match a query /// </summary> /// <returns></returns> public async Task <int> ReadTotalCompetitions(CompetitionFilter competitionQuery) { using (var connection = _databaseConnectionFactory.CreateDatabaseConnection()) { var(where, parameters) = BuildWhereClause(competitionQuery); return(await connection.ExecuteScalarAsync <int>($@"SELECT COUNT(co.CompetitionId) FROM {Tables.Competition} AS co INNER JOIN {Tables.CompetitionVersion} AS cv ON co.CompetitionId = cv.CompetitionId {where} AND cv.CompetitionVersionId = (SELECT TOP 1 CompetitionVersionId FROM {Tables.CompetitionVersion} WHERE CompetitionId = co.CompetitionId ORDER BY ISNULL(UntilDate, '{SqlDateTime.MaxValue.Value.Date.ToString("yyyy-MM-dd", CultureInfo.InvariantCulture)}') DESC)", new DynamicParameters(parameters)).ConfigureAwait(false)); } }
/// <inheritdoc /> public async Task <List <Competition> > ReadCompetitions(CompetitionFilter filter) { filter = filter ?? new CompetitionFilter(); if (CacheDisabled()) { return(await _competitionDataSource.ReadCompetitions(filter).ConfigureAwait(false)); } else { var cachePolicy = _policyRegistry.Get <IAsyncPolicy>(CacheConstants.CompetitionsPolicy); var cacheKey = CacheConstants.CompetitionsPolicyCacheKeyPrefix + nameof(ReadCompetitions) + _competitionFilterSerializer.Serialize(filter); return(await cachePolicy.ExecuteAsync(async context => await _competitionDataSource.ReadCompetitions(filter).ConfigureAwait(false), new Context(cacheKey))); } }
public string Serialize(CompetitionFilter filter) { filter = filter ?? new CompetitionFilter(); ResetSerializer(); Serialize(filter.EnableTournaments, "tournaments"); Serialize(filter.FromYear, "from"); Serialize(filter.UntilYear, "until"); Serialize(filter.MatchTypes, "matchtype"); Serialize(filter.PlayerTypes, "playertype"); Serialize(filter.Query, "q"); Serialize(filter.Paging.PageNumber, "page"); Serialize(filter.Paging.PageSize, "pagesize"); return(Serializer.ToQueryString()); }
/// <summary> /// Gets a list of seasons based on a query /// </summary> /// <returns>A list of <see cref="Season"/> objects. An empty list if no seasons are found.</returns> public async Task <List <Season> > ReadSeasons(CompetitionFilter filter) { if (filter is null) { filter = new CompetitionFilter(); } using (var connection = _databaseConnectionFactory.CreateDatabaseConnection()) { var(where, parameters) = BuildWhereClause(filter); var sql = $@"SELECT s2.SeasonId, s2.FromYear, s2.UntilYear, cv2.CompetitionName, YEAR(cv2.UntilDate) AS UntilYear FROM {Tables.Season} AS s2 INNER JOIN {Tables.Competition} AS co2 ON co2.CompetitionId = s2.CompetitionId INNER JOIN {Tables.CompetitionVersion} AS cv2 ON cv2.CompetitionId = s2.CompetitionId WHERE s2.SeasonId IN( SELECT s.SeasonId FROM {Tables.Season} AS s INNER JOIN {Tables.Competition} AS co ON co.CompetitionId = s.CompetitionId INNER JOIN {Tables.CompetitionVersion} cv ON co.CompetitionId = cv.CompetitionId {where} AND cv.CompetitionVersionId = (SELECT TOP 1 CompetitionVersionId FROM {Tables.CompetitionVersion} WHERE CompetitionId = co.CompetitionId ORDER BY ISNULL(UntilDate, '{SqlDateTime.MaxValue.Value.Date.ToString("yyyy-MM-dd", CultureInfo.InvariantCulture)}') DESC) ORDER BY CASE WHEN cv.UntilDate IS NULL THEN 0 WHEN cv.UntilDate IS NOT NULL THEN 1 END, s.FromYear DESC, s.UntilYear DESC, cv.ComparableName OFFSET @PageOffset ROWS FETCH NEXT @PageSize ROWS ONLY ) AND cv2.CompetitionVersionId = (SELECT TOP 1 CompetitionVersionId FROM {Tables.CompetitionVersion} WHERE CompetitionId = s2.CompetitionId ORDER BY ISNULL(UntilDate, '{SqlDateTime.MaxValue.Value.Date.ToString("yyyy-MM-dd", CultureInfo.InvariantCulture)}') DESC) ORDER BY CASE WHEN cv2.UntilDate IS NULL THEN 0 WHEN cv2.UntilDate IS NOT NULL THEN 1 END, s2.FromYear DESC, s2.UntilYear DESC, cv2.ComparableName"; parameters.Add("@PageOffset", (filter.Paging.PageNumber - 1) * filter.Paging.PageSize); parameters.Add("@PageSize", filter.Paging.PageSize); var seasons = await connection.QueryAsync <Season, Competition, Season>(sql, (season, competition) => { season.Competition = competition; return(season); }, new DynamicParameters(parameters), splitOn : "CompetitionName").ConfigureAwait(false); return(seasons.ToList()); } }
public async Task Read_competitions_supports_case_insensitive_filter_by_partial_player_type() { var routeNormaliser = new Mock <IRouteNormaliser>(); var competitionDataSource = new SqlServerCompetitionDataSource(_databaseFixture.ConnectionFactory, routeNormaliser.Object); var query = new CompetitionFilter { Query = "JuNioR", Paging = new Paging { PageSize = _databaseFixture.Competitions.Count } }; var result = await competitionDataSource.ReadCompetitions(query).ConfigureAwait(false); foreach (var competition in _databaseFixture.Competitions.Where(x => x.PlayerType.ToString().Contains(query.Query, StringComparison.OrdinalIgnoreCase))) { Assert.NotNull(result.Single(x => x.CompetitionId == competition.CompetitionId)); } }
/// <summary> /// Filtre sur le classement des membres /// Même fonctionnement que pour celui alphabetique /// </summary> /// <param name="sender"></param> /// <param name="e"></param> private void ClassementButtonClick(object sender, RoutedEventArgs e) { this._classementSorted = !this._classementSorted; ClassementButton.Background = this._classementSorted ? SolidColorBrushHelper.Green() : SolidColorBrushHelper.Transparent(); CompetitionFilter filter = new CompetitionFilter(); if (this._classementSorted) { this._memberVM.AddFilter(filter); } else { this._memberVM.RemoveFilter(filter); } this._memberVM.SelectedUserControl = new MemberListUserControl(this._memberVM.Sort(), this._memberVM.Save); }
public async Task <AutocompleteResultSet> Autocomplete([FromUri] string query = null, [FromUri] string[] matchType = null) { var competitionQuery = new CompetitionFilter { Query = query }; if (matchType != null) { foreach (var mt in matchType) { if (mt == null) { continue; } try { competitionQuery.MatchTypes.Add((MatchType)Enum.Parse(typeof(MatchType), mt, true)); } catch (ArgumentException) { // ignore that one } } } var seasons = await _seasonDataSource.ReadSeasons(competitionQuery).ConfigureAwait(false); return(new AutocompleteResultSet { suggestions = seasons.Select(x => new AutocompleteResult { value = x.SeasonFullName(), data = x.SeasonId.ToString() }) }); }
public async override Task <ActionResult> Index(ContentModel contentModel) { if (contentModel is null) { throw new ArgumentNullException(nameof(contentModel)); } var model = new EditTournamentViewModel(contentModel.Content, Services?.UserService) { Tournament = await _tournamentDataSource.ReadTournamentByRoute(Request.RawUrl).ConfigureAwait(false), DateFormatter = _dateFormatter, UrlReferrer = Request.UrlReferrer }; if (model.Tournament == null) { return(new HttpNotFoundResult()); } else { // By filtering for all match types it excludes seasons for annual tournaments like Expo and Seaford, which may support training sessions // but otherwise no match types. Although those tournaments can be listed in other seasons (they're of interest to the league teams), we // don't want other tournaments listed on pages which are supposed to be just about those annual tournaments. For any tournaments which // actually are meant to be in those seasons, they will be added back when the tournament's current seasons are added to the list. var seasonDates = _seasonEstimator.EstimateSeasonDates(model.Tournament.StartTime); var filter = new CompetitionFilter { FromYear = seasonDates.fromDate.Year, UntilYear = seasonDates.untilDate.Year, PlayerTypes = new List <PlayerType> { model.Tournament.PlayerType }, MatchTypes = new List <MatchType> { MatchType.FriendlyMatch, MatchType.KnockoutMatch, MatchType.LeagueMatch }, EnableTournaments = true }; model.PossibleSeasons.AddRange(await _seasonDataSource.ReadSeasons(filter).ConfigureAwait(false)); foreach (var season in model.Tournament.Seasons) { if (!model.PossibleSeasons.Select(x => x.SeasonId.Value).Contains(season.SeasonId.Value)) { model.PossibleSeasons.Add(season); } } model.PossibleSeasons.Sort(new SeasonComparer()); model.IsAuthorized = _authorizationPolicy.IsAuthorized(model.Tournament); model.TournamentDate = model.Tournament.StartTime; model.Metadata.PageTitle = "Where to list " + model.Tournament.TournamentFullName(x => _dateFormatter.FormatDate(x, false, false, false)); model.Breadcrumbs.Add(new Breadcrumb { Name = Constants.Pages.Tournaments, Url = new Uri(Constants.Pages.TournamentsUrl, UriKind.Relative) }); model.Breadcrumbs.Add(new Breadcrumb { Name = model.Tournament.TournamentName, Url = new Uri(model.Tournament.TournamentRoute, UriKind.Relative) }); return(CurrentTemplate(model)); } }
private static (string sql, Dictionary <string, object> parameters) BuildWhereClause(CompetitionFilter filter) { var where = new List <string>(); var parameters = new Dictionary <string, object>(); if (!string.IsNullOrEmpty(filter.Query)) { where.Add("(CONCAT(CompetitionName, ', ', s.FromYear, ' season') LIKE @Query OR CONCAT(CompetitionName, ', ', s.FromYear, '/', RIGHT(s.UntilYear,2), ' season') LIKE @Query OR co.PlayerType LIKE @Query)"); parameters.Add("@Query", $"%{filter.Query}%"); } if (filter.PlayerTypes?.Count > 0) { where.Add("co.PlayerType IN @PlayerTypes"); parameters.Add("@PlayerTypes", filter.PlayerTypes.Select(x => x.ToString())); } if (filter.MatchTypes?.Count > 0) { where.Add($"s.SeasonId IN (SELECT SeasonId FROM {Tables.SeasonMatchType} WHERE MatchType IN @MatchTypes)"); parameters.Add("@MatchTypes", filter.MatchTypes.Select(x => x.ToString())); } if (filter.FromYear.HasValue) { where.Add("s.FromYear = @FromYear"); parameters.Add("@FromYear", filter.FromYear.Value); } if (filter.UntilYear.HasValue) { where.Add("s.UntilYear = @UntilYear"); parameters.Add("@UntilYear", filter.UntilYear.Value); } if (filter.EnableTournaments.HasValue) { where.Add("s.EnableTournaments = @EnableTournaments"); parameters.Add("@EnableTournaments", filter.EnableTournaments.Value); } return(where.Count > 0 ? $@"WHERE " + string.Join(" AND ", where) : "WHERE 1=1", parameters); // There must always be a WHERE clause so it can be appended to }
private static (string sql, Dictionary <string, object> parameters) BuildWhereClause(CompetitionFilter competitionQuery) { var where = new List <string>(); var parameters = new Dictionary <string, object>(); if (!string.IsNullOrEmpty(competitionQuery?.Query)) { where.Add("(cv.CompetitionName LIKE @Query OR co.PlayerType LIKE @Query)"); parameters.Add("@Query", $"%{competitionQuery.Query}%"); } return(where.Count > 0 ? $@"WHERE " + string.Join(" AND ", where) : "WHERE 1=1", parameters); // Ensure there's always a WHERE clause so that it can be appended to }
/// <summary> /// Gets a list of competitions based on a query /// </summary> /// <returns>A list of <see cref="Competition"/> objects. An empty list if no competitions are found.</returns> public async Task <List <Competition> > ReadCompetitions(CompetitionFilter filter) { if (filter == null) { filter = new CompetitionFilter(); } using (var connection = _databaseConnectionFactory.CreateDatabaseConnection()) { var(where, parameters) = BuildWhereClause(filter); var sql = $@"SELECT co2.CompetitionId, cv2.CompetitionName, co2.CompetitionRoute, YEAR(cv2.UntilDate) AS UntilYear, co2.PlayerType, s2.SeasonId, s2.SeasonRoute, st2.TeamId FROM {Tables.Competition} AS co2 INNER JOIN {Tables.CompetitionVersion} AS cv2 ON co2.CompetitionId = cv2.CompetitionId LEFT JOIN {Tables.Season} AS s2 ON co2.CompetitionId = s2.CompetitionId LEFT JOIN {Tables.SeasonTeam} AS st2 ON s2.SeasonId = st2.SeasonId WHERE co2.CompetitionId IN( SELECT co.CompetitionId FROM {Tables.Competition} AS co INNER JOIN {Tables.CompetitionVersion} AS cv ON co.CompetitionId = cv.CompetitionId LEFT JOIN {Tables.Season} AS s ON co.CompetitionId = s.CompetitionId {where} AND cv.CompetitionVersionId = (SELECT TOP 1 CompetitionVersionId FROM {Tables.CompetitionVersion} WHERE CompetitionId = co.CompetitionId ORDER BY ISNULL(UntilDate, '{SqlDateTime.MaxValue.Value.Date.ToString("yyyy-MM-dd", CultureInfo.InvariantCulture)}') DESC) AND (s.FromYear = (SELECT MAX(FromYear) FROM {Tables.Season} WHERE CompetitionId = co.CompetitionId) OR s.FromYear IS NULL) ORDER BY CASE WHEN cv.UntilDate IS NULL THEN 0 ELSE 1 END, s.UntilYear DESC, s.FromYear DESC, cv.ComparableName OFFSET @PageOffset ROWS FETCH NEXT @PageSize ROWS ONLY ) AND cv2.CompetitionVersionId = (SELECT TOP 1 CompetitionVersionId FROM {Tables.CompetitionVersion} WHERE CompetitionId = co2.CompetitionId ORDER BY ISNULL(UntilDate, '{SqlDateTime.MaxValue.Value.Date.ToString("yyyy-MM-dd", CultureInfo.InvariantCulture)}') DESC) AND (s2.FromYear = (SELECT MAX(FromYear) FROM {Tables.Season} WHERE CompetitionId = co2.CompetitionId) OR s2.FromYear IS NULL) ORDER BY CASE WHEN cv2.UntilDate IS NULL THEN 0 ELSE 1 END, s2.UntilYear DESC, s2.FromYear DESC, cv2.ComparableName"; parameters.Add("@PageOffset", (filter.Paging.PageNumber - 1) * filter.Paging.PageSize); parameters.Add("@PageSize", filter.Paging.PageSize); var competitions = await connection.QueryAsync <Competition, Season, Team, Competition>(sql, (competition, season, team) => { if (season != null) { competition.Seasons.Add(season); } if (team != null) { season.Teams.Add(new TeamInSeason { Team = team }); } return(competition); }, new DynamicParameters(parameters), splitOn : "SeasonId,TeamId").ConfigureAwait(false); var resolvedCompetitions = competitions.GroupBy(competition => competition.CompetitionId).Select(copiesOfCompetition => { var resolvedCompetition = copiesOfCompetition.First(); if (resolvedCompetition.Seasons.Count > 0) { resolvedCompetition.Seasons.First().Teams = copiesOfCompetition .Select(competition => competition.Seasons.SingleOrDefault()?.Teams.SingleOrDefault()) .OfType <TeamInSeason>().ToList(); } return(resolvedCompetition); }).ToList(); return(resolvedCompetitions); } }