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);
}
}
