public async Task Read_match_locations_supports_filter_by_season()
{
var routeNormaliser = new Mock <IRouteNormaliser>();
var season = _databaseFixture.Seasons.First(x => x.Teams.Any(t => t.Team.MatchLocations.Any()));
var matchLocationDataSource = new SqlServerMatchLocationDataSource(_databaseFixture.ConnectionFactory, routeNormaliser.Object);
var query = new MatchLocationFilter
{
Paging = new Paging
{
PageSize = _databaseFixture.MatchLocations.Count
},
SeasonIds = new List <Guid> {
season.SeasonId.Value
}
};
var results = await matchLocationDataSource.ReadMatchLocations(query).ConfigureAwait(false);
var teamIdsInSeason = season.Teams.Select(st => st.Team.TeamId.Value);
Assert.Equal(_databaseFixture.MatchLocations.Count(x => x.Teams.Any(t => teamIdsInSeason.Contains(t.TeamId.Value))), results.Count);
foreach (var result in results)
{
Assert.NotEmpty(result.Teams);
foreach (var team in result.Teams)
{
Assert.Contains(team.TeamId.Value, teamIdsInSeason);
}
}
}
public async Task Read_total_locations_supports_case_insensitive_filter_town()
{
var routeNormaliser = new Mock <IRouteNormaliser>();
var matchLocationDataSource = new SqlServerMatchLocationDataSource(_databaseFixture.ConnectionFactory, routeNormaliser.Object);
var query = new MatchLocationFilter {
Query = "ToWn"
};
var result = await matchLocationDataSource.ReadTotalMatchLocations(query).ConfigureAwait(false);
Assert.Equal(_databaseFixture.MatchLocations.Count(x => x.Town.Contains(query.Query, StringComparison.OrdinalIgnoreCase)), result);
}
/// <summary>
/// Gets the number of match locations that match a query
/// </summary>
/// <returns></returns>
public async Task <int> ReadTotalMatchLocations(MatchLocationFilter matchLocationQuery)
{
if (matchLocationQuery == null)
{
matchLocationQuery = new MatchLocationFilter();
}
using (var connection = _databaseConnectionFactory.CreateDatabaseConnection())
{
var(sql, parameters) = BuildMatchLocationQuery(matchLocationQuery, $@"SELECT COUNT(DISTINCT ml.MatchLocationId)
FROM {Tables.MatchLocation} AS ml <<JOIN>> <<WHERE>>", Array.Empty <string>());
return(await connection.ExecuteScalarAsync <int>(sql, new DynamicParameters(parameters)).ConfigureAwait(false));
}
}
/// <inheritdoc />
public async Task <List <MatchLocation> > ReadMatchLocations(MatchLocationFilter filter)
{
filter = filter ?? new MatchLocationFilter();
if (CacheDisabled())
{
return(await _matchLocationDataSource.ReadMatchLocations(filter).ConfigureAwait(false));
}
else
{
var cachePolicy = _policyRegistry.Get <IAsyncPolicy>(CacheConstants.MatchLocationsPolicy);
var cacheKey = CacheConstants.MatchLocationsCacheKeyPrefix + nameof(ReadMatchLocations) + _matchLocationFilterSerializer.Serialize(filter);
return(await cachePolicy.ExecuteAsync(async context => await _matchLocationDataSource.ReadMatchLocations(filter).ConfigureAwait(false), new Context(cacheKey)));
}
}
public string Serialize(MatchLocationFilter filter)
{
filter = filter ?? new MatchLocationFilter();
ResetSerializer();
Serialize(filter.ExcludeMatchLocationIds, "exclude");
Serialize(filter.HasActiveTeams, "active");
Serialize(filter.Query, "q");
Serialize(filter.SeasonIds, "season");
Serialize(filter.TeamTypes, "teamtype");
Serialize(filter.Paging.PageNumber, "page");
Serialize(filter.Paging.PageSize, "pagesize");
return(Serializer.ToQueryString());
}
private async Task <List <MatchLocation> > QueryMatchLocations(string query, string[] not, bool?hasActiveTeams, string[] teamTypes, Guid?seasonId)
{
var filter = new MatchLocationFilter {
Query = query, HasActiveTeams = hasActiveTeams
};
if (not != null)
{
foreach (var guid in not)
{
if (guid == null)
{
continue;
}
try
{
filter.ExcludeMatchLocationIds.Add(new Guid(guid));
}
catch (FormatException)
{
// ignore that one
}
}
}
if (teamTypes != null)
{
foreach (var teamType in teamTypes)
{
if (Enum.TryParse <TeamType>(teamType, true, out var parsedType))
{
filter.TeamTypes.Add(parsedType);
}
}
}
if (seasonId.HasValue)
{
filter.SeasonIds.Add(seasonId.Value);
}
return(await _locationDataSource.ReadMatchLocations(filter).ConfigureAwait(false));
}
public async Task Read_match_locations_supports_excluding_locations_by_id()
{
var routeNormaliser = new Mock <IRouteNormaliser>();
var matchLocationDataSource = new SqlServerMatchLocationDataSource(_databaseFixture.ConnectionFactory, routeNormaliser.Object);
var query = new MatchLocationFilter
{
Paging = new Paging
{
PageSize = _databaseFixture.MatchLocations.Count
},
ExcludeMatchLocationIds = new List <Guid> {
_databaseFixture.MatchLocationWithMinimalDetails.MatchLocationId.Value
}
};
var result = await matchLocationDataSource.ReadMatchLocations(query).ConfigureAwait(false);
Assert.Equal(_databaseFixture.MatchLocations.Count - 1, result.Count);
Assert.Null(result.SingleOrDefault(x => x.MatchLocationId == _databaseFixture.MatchLocationWithMinimalDetails.MatchLocationId));
}
public async Task Read_match_locations_supports_case_insensitive_filter_town()
{
var routeNormaliser = new Mock <IRouteNormaliser>();
var matchLocationDataSource = new SqlServerMatchLocationDataSource(_databaseFixture.ConnectionFactory, routeNormaliser.Object);
var query = new MatchLocationFilter {
Paging = new Paging {
PageSize = _databaseFixture.MatchLocations.Count
}, Query = "ToWn"
};
var result = await matchLocationDataSource.ReadMatchLocations(query).ConfigureAwait(false);
var expected = _databaseFixture.MatchLocations.Where(x => x.Town.Contains("town", StringComparison.OrdinalIgnoreCase));
Assert.Equal(expected.Count(), result.Count);
foreach (var location in expected)
{
Assert.NotNull(result.SingleOrDefault(x => x.MatchLocationId == location.MatchLocationId));
}
}
public async Task Read_match_locations_supports_filter_by_team_type()
{
var routeNormaliser = new Mock <IRouteNormaliser>();
var matchLocationDataSource = new SqlServerMatchLocationDataSource(_databaseFixture.ConnectionFactory, routeNormaliser.Object);
var query = new MatchLocationFilter
{
Paging = new Paging
{
PageSize = _databaseFixture.MatchLocations.Count
},
TeamTypes = new List <TeamType> {
TeamType.Regular
}
};
var results = await matchLocationDataSource.ReadMatchLocations(query).ConfigureAwait(false);
Assert.Equal(_databaseFixture.MatchLocations.Count(x => x.Teams.Any(t => t.TeamType == TeamType.Regular)), results.Count);
foreach (var result in results)
{
Assert.Empty(result.Teams.Where(x => x.TeamType != TeamType.Regular));
}
}
public async Task Read_match_locations_supports_excluding_locations_with_no_active_teams()
{
var routeNormaliser = new Mock <IRouteNormaliser>();
var matchLocationDataSource = new SqlServerMatchLocationDataSource(_databaseFixture.ConnectionFactory, routeNormaliser.Object);
var query = new MatchLocationFilter
{
Paging = new Paging
{
PageSize = _databaseFixture.MatchLocations.Count
},
HasActiveTeams = true
};
var result = await matchLocationDataSource.ReadMatchLocations(query).ConfigureAwait(false);
var expected = _databaseFixture.MatchLocations.Where(x => x.Teams.Any(t => !t.UntilYear.HasValue));
Assert.Equal(expected.Count(), result.Count);
foreach (var location in expected)
{
Assert.NotNull(result.SingleOrDefault(x => x.MatchLocationId == location.MatchLocationId));
}
}
private static (string filteredSql, Dictionary <string, object> parameters) BuildMatchLocationQuery(MatchLocationFilter matchLocationQuery, string sql, string[] currentJoins)
{
var join = new List <string>();
var where = new List <string>();
var parameters = new Dictionary <string, object>();
if (!string.IsNullOrEmpty(matchLocationQuery.Query))
{
where.Add(@"(ml.SecondaryAddressableObjectName LIKE @Query OR
ml.PrimaryAddressableObjectName LIKE @Query OR
ml.Locality LIKE @Query OR
ml.Town LIKE @Query)");
parameters.Add("@Query", $"%{matchLocationQuery.Query}%");
}
if (matchLocationQuery.ExcludeMatchLocationIds?.Count > 0)
{
where.Add("ml.MatchLocationId NOT IN @ExcludeMatchLocationIds");
parameters.Add("@ExcludeMatchLocationIds", matchLocationQuery.ExcludeMatchLocationIds.Select(x => x.ToString()));
}
if (matchLocationQuery.HasActiveTeams.HasValue)
{
if (matchLocationQuery.HasActiveTeams.Value)
{
if (!currentJoins.Contains(Tables.TeamMatchLocation))
{
join.Add($"LEFT JOIN {Tables.TeamMatchLocation} AS tml ON ml.MatchLocationId = tml.MatchLocationId");
}
if (!currentJoins.Contains(Tables.Team))
{
join.Add($"LEFT JOIN {Tables.Team} AS t ON tml.TeamId = t.TeamId");
}
if (!currentJoins.Contains(Tables.TeamVersion))
{
join.Add($"LEFT JOIN {Tables.TeamVersion} AS tv ON t.TeamId = tv.TeamId");
}
where.Add("tv.TeamVersionId IS NOT NULL AND tv.UntilDate IS NULL");
}
else
{
throw new NotImplementedException();
}
}
if (matchLocationQuery.TeamTypes.Count > 0)
{
if (!currentJoins.Contains(Tables.TeamMatchLocation))
{
join.Add($"LEFT JOIN {Tables.TeamMatchLocation} AS tml ON ml.MatchLocationId = tml.MatchLocationId");
}
if (!currentJoins.Contains(Tables.Team))
{
join.Add($"LEFT JOIN {Tables.Team} AS t ON tml.TeamId = t.TeamId");
}
where.Add("t.TeamType IN @TeamTypes");
parameters.Add("@TeamTypes", matchLocationQuery.TeamTypes.Select(x => x.ToString()));
}
if (matchLocationQuery.SeasonIds.Count > 0)
{
if (!currentJoins.Contains(Tables.TeamMatchLocation))
{
join.Add($"LEFT JOIN {Tables.TeamMatchLocation} AS tml ON ml.MatchLocationId = tml.MatchLocationId");
}
if (!currentJoins.Contains(Tables.SeasonTeam))
{
join.Add($"LEFT JOIN {Tables.SeasonTeam} AS st ON tml.TeamId = st.TeamId");
}
where.Add("st.SeasonId IN @SeasonIds");
parameters.Add("@SeasonIds", matchLocationQuery.SeasonIds.Select(x => x.ToString()));
}
sql = sql.Replace("<<JOIN>>", join.Count > 0 ? string.Join(" ", join) : string.Empty)
.Replace("<<WHERE>>", where.Count > 0 ? "WHERE " + string.Join(" AND ", where) : "WHERE 1=1"); // Ensure there's always a WHERE clause we can append to
return(sql, parameters);
}
/// <summary>
/// Gets a list of match locations based on a query
/// </summary>
/// <returns>A list of <see cref="MatchLocation"/> objects. An empty list if no match locations are found.</returns>
public async Task <List <MatchLocation> > ReadMatchLocations(MatchLocationFilter filter)
{
if (filter == null)
{
filter = new MatchLocationFilter();
}
using (var connection = _databaseConnectionFactory.CreateDatabaseConnection())
{
// order by clause places locations with active teams above those which have none
var sql = $@"SELECT ml2.MatchLocationId, ml2.MatchLocationRoute, ml2.Latitude, ml2.Longitude,
ml2.SecondaryAddressableObjectName, ml2.PrimaryAddressableObjectName, ml2.Locality, ml2.Town,
t2.TeamId, tv2.TeamName, t2.TeamRoute, t2.TeamType, t2.PlayerType, YEAR(tv2.UntilDate) AS UntilYear
FROM {Tables.MatchLocation} AS ml2
LEFT JOIN {Tables.TeamMatchLocation} AS tml2 ON ml2.MatchLocationId = tml2.MatchLocationId
LEFT JOIN {Tables.Team} AS t2 ON tml2.TeamId = t2.TeamId
LEFT JOIN {Tables.TeamVersion} AS tv2 ON t2.TeamId = tv2.TeamId
<<JOIN-T2-SEASON>>
WHERE tml2.UntilDate IS NULL
AND (tv2.TeamVersionId = (SELECT TOP 1 TeamVersionId FROM {Tables.TeamVersion} WHERE TeamId = t2.TeamId ORDER BY ISNULL(UntilDate, '{SqlDateTime.MaxValue.Value.Date.ToString("yyyy-MM-dd", CultureInfo.InvariantCulture)}') DESC) OR tv2.TeamVersionId IS NULL)
<<WHERE-T2-TYPE>>
<<WHERE-T2-SEASON>>
AND ml2.MatchLocationId IN (
SELECT MatchLocationId FROM (
SELECT DISTINCT ml.MatchLocationId, ml.ComparableName,
CASE WHEN (
SELECT COUNT(t4.TeamId) FROM {Tables.TeamMatchLocation} AS tml4
LEFT JOIN {Tables.Team} AS t4 ON tml4.TeamId = t4.TeamId
LEFT JOIN {Tables.TeamVersion} AS tv4 ON t4.TeamId = tv4.TeamId
WHERE tml4.MatchLocationId = ml.MatchLocationId
AND (tv4.TeamVersionId = (SELECT TOP 1 TeamVersionId FROM {Tables.TeamVersion} WHERE TeamId = t4.TeamId ORDER BY ISNULL(UntilDate, '9999-12-31') DESC) OR tv4.TeamVersionId IS NULL)
AND tv4.UntilDate IS NULL
AND tml4.UntilDate IS NULL
<<WHERE-T4-TYPE>>
) > 0 THEN 1 ELSE 0 END AS HasActiveTeams
FROM {Tables.MatchLocation} AS ml
LEFT JOIN {Tables.TeamMatchLocation} AS tml ON ml.MatchLocationId = tml.MatchLocationId
LEFT JOIN {Tables.Team} AS t ON tml.TeamId = t.TeamId
LEFT JOIN {Tables.TeamVersion} AS tv ON t.TeamId = tv.TeamId
<<JOIN>>
<<WHERE>>
AND tml.UntilDate IS NULL
) AS DistinctIdsForPaging
ORDER BY HasActiveTeams DESC, ComparableName
OFFSET @PageOffset ROWS FETCH NEXT @PageSize ROWS ONLY
)
ORDER BY
CASE WHEN (
SELECT COUNT(t3.TeamId) FROM {Tables.TeamMatchLocation} AS tml3
LEFT JOIN {Tables.Team} AS t3 ON tml3.TeamId = t3.TeamId
LEFT JOIN {Tables.TeamVersion} AS tv3 ON t3.TeamId = tv3.TeamId
WHERE tml3.MatchLocationId = ml2.MatchLocationId
AND (tv3.TeamVersionId = (SELECT TOP 1 TeamVersionId FROM {Tables.TeamVersion} WHERE TeamId = t3.TeamId ORDER BY ISNULL(UntilDate, '{SqlDateTime.MaxValue.Value.Date.ToString("yyyy-MM-dd", CultureInfo.InvariantCulture)}') DESC) OR tv3.TeamVersionId IS NULL)
AND tv3.UntilDate IS NULL
AND tml3.UntilDate IS NULL
<<WHERE-T3-TYPE>>
) > 0 THEN 1 ELSE 0 END DESC,
ml2.ComparableName";
var(filteredSql, parameters) = BuildMatchLocationQuery(filter, sql, new[] { Tables.TeamMatchLocation, Tables.Team, Tables.TeamVersion });
parameters.Add("@PageOffset", (filter.Paging.PageNumber - 1) * filter.Paging.PageSize);
parameters.Add("@PageSize", filter.Paging.PageSize);
filteredSql = filteredSql.Replace("<<WHERE-T2-TYPE>>", filter.TeamTypes.Count > 0 ? "AND t2.TeamType IN @TeamTypes" : string.Empty);
filteredSql = filteredSql.Replace("<<WHERE-T3-TYPE>>", filter.TeamTypes.Count > 0 ? "AND t3.TeamType IN @TeamTypes" : string.Empty);
filteredSql = filteredSql.Replace("<<WHERE-T4-TYPE>>", filter.TeamTypes.Count > 0 ? "AND t4.TeamType IN @TeamTypes" : string.Empty);
// when filtering by season, return only the teams in the season in case there are other teams also based at the same locations
filteredSql = filteredSql.Replace("<<JOIN-T2-SEASON>>", filter.SeasonIds.Count > 0 ? $"LEFT JOIN {Tables.SeasonTeam} AS st2 ON tml2.TeamId = st2.TeamId" : string.Empty);
filteredSql = filteredSql.Replace("<<WHERE-T2-SEASON>>", filter.SeasonIds.Count > 0 ? "AND st2.SeasonId IN @SeasonIds" : string.Empty);
var locations = await connection.QueryAsync <MatchLocation, Team, MatchLocation>(filteredSql,
(location, team) =>
{
if (team != null)
{
location.Teams.Add(team);
}
return(location);
},
new DynamicParameters(parameters),
splitOn : "TeamId").ConfigureAwait(false);
var resolvedLocations = locations.GroupBy(location => location.MatchLocationId).Select(copiesOfLocation =>
{
var resolvedLocation = copiesOfLocation.First();
resolvedLocation.Teams = copiesOfLocation.Select(location => location.Teams.SingleOrDefault()).OfType <Team>().Distinct(new TeamEqualityComparer()).ToList();
return(resolvedLocation);
}).ToList();
return(resolvedLocations);
}
}
