/// <summary>
/// Gets a list of schools based on a query
/// </summary>
/// <returns>A list of <see cref="School"/> objects. An empty list if no schools are found.</returns>
public async Task <List <School> > ReadSchools(SchoolFilter filter)
{
if (filter == null)
{
filter = new SchoolFilter();
}
using (var connection = _databaseConnectionFactory.CreateDatabaseConnection())
{
var(where, parameters) = BuildWhereClause(filter);
var sql = $@"SELECT sc2.SchoolId, sv2.SchoolName, sc2.SchoolRoute, YEAR(sv2.UntilDate) as UntilYear,
t2.TeamId, t2.TeamType, t2.PlayerType, YEAR(tv2.UntilDate) AS UntilYear
FROM {Tables.School} AS sc2
INNER JOIN {Tables.SchoolVersion} AS sv2 ON sc2.SchoolId = sv2.SchoolId
LEFT JOIN {Tables.Team} t2 ON sc2.SchoolId = t2.SchoolId
LEFT JOIN {Tables.TeamVersion} tv2 ON t2.TeamId = tv2.TeamId
WHERE sc2.SchoolId IN (
SELECT SchoolId FROM (
SELECT DISTINCT sc.SchoolId, CASE WHEN sv.UntilDate IS NULL THEN 1 ELSE 0 END AS Active, sv.ComparableName
FROM {Tables.School} AS sc
INNER JOIN {Tables.SchoolVersion} AS sv ON sc.SchoolId = sv.SchoolId
LEFT JOIN {Tables.Team} t ON sc.SchoolId = t.SchoolId
LEFT JOIN {Tables.TeamMatchLocation} tml ON t.TeamId = tml.TeamId
LEFT JOIN {Tables.MatchLocation} ml ON tml.MatchLocationid = ml.MatchLocationId
{where}
AND sv.SchoolVersionId = (SELECT TOP 1 SchoolVersionId FROM {Tables.SchoolVersion} WHERE SchoolId = sc.SchoolId ORDER BY ISNULL(UntilDate, '{SqlDateTime.MaxValue.Value.Date.ToString("yyyy-MM-dd", CultureInfo.InvariantCulture)}') DESC)
ORDER BY Active, sv.ComparableName
OFFSET @PageOffset ROWS FETCH NEXT @PageSize ROWS ONLY
) AS SchoolIds
)
AND sv2.SchoolVersionId = (SELECT TOP 1 SchoolVersionId FROM {Tables.SchoolVersion} WHERE SchoolId = sc2.SchoolId ORDER BY ISNULL(UntilDate, '{SqlDateTime.MaxValue.Value.Date.ToString("yyyy-MM-dd", CultureInfo.InvariantCulture)}') DESC)
AND (tv2.TeamVersionId IS NULL OR 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))
ORDER BY CASE WHEN sv2.UntilDate IS NULL THEN 0 ELSE 1 END, sv2.ComparableName";
parameters.Add("@PageOffset", (filter.Paging.PageNumber - 1) * filter.Paging.PageSize);
parameters.Add("@PageSize", filter.Paging.PageSize);
var schools = await connection.QueryAsync <School, Team, School>(sql,
(school, team) =>
{
if (team != null)
{
school.Teams.Add(team);
}
return(school);
},
new DynamicParameters(parameters),
splitOn : "TeamId").ConfigureAwait(false);
var resolvedSchools = schools.GroupBy(school => school.SchoolId).Select(copiesOfSchool =>
{
var resolvedSchool = copiesOfSchool.First();
resolvedSchool.Teams = copiesOfSchool.SelectMany(x => x.Teams).Distinct(new TeamEqualityComparer()).ToList();
return(resolvedSchool);
}).ToList();
return(resolvedSchools);
}
}
public async Task Read_total_schools_supports_case_insensitive_filter_by_partial_name()
{
var schoolDataSource = new SqlServerSchoolDataSource(_databaseFixture.ConnectionFactory, Mock.Of <IRouteNormaliser>());
var schoolName = PartialNameOfAnySchool();
var query = new SchoolFilter {
Query = schoolName.ToLower(CultureInfo.CurrentCulture)
};
var result = await schoolDataSource.ReadTotalSchools(query).ConfigureAwait(false);
Assert.Equal(SchoolsMatchingQuery(query.Query).Count, result);
}
public async Task Read_total_schools_supports_case_insensitive_filter_by_town()
{
var schoolDataSource = new SqlServerSchoolDataSource(_databaseFixture.ConnectionFactory, Mock.Of <IRouteNormaliser>());
var schoolWithTown = _databaseFixture.TestData.Schools.First(x => x.Teams.Any(t => t.MatchLocations.Any(ml => !string.IsNullOrEmpty(ml.Town))));
var locality = schoolWithTown.Teams.First(x => x.MatchLocations.Any(ml => !string.IsNullOrEmpty(ml.Town))).MatchLocations.First(ml => !string.IsNullOrEmpty(ml.Town)).Town;
var query = new SchoolFilter {
Query = locality.ToLower(CultureInfo.CurrentCulture)
};
var result = await schoolDataSource.ReadTotalSchools(query).ConfigureAwait(false);
Assert.Equal(SchoolsMatchingQuery(query.Query).Count, result);
}
/// <summary>
/// Gets the number of schools that match a query
/// </summary>
/// <returns></returns>
public async Task <int> ReadTotalSchools(SchoolFilter schoolQuery)
{
using (var connection = _databaseConnectionFactory.CreateDatabaseConnection())
{
var(where, parameters) = BuildWhereClause(schoolQuery);
return(await connection.ExecuteScalarAsync <int>($@"SELECT COUNT(DISTINCT sc.SchoolId)
FROM {Tables.School} AS sc
INNER JOIN {Tables.SchoolVersion} AS sv ON sc.SchoolId = sv.SchoolId
LEFT JOIN {Tables.Team} t ON sc.SchoolId = t.SchoolId
LEFT JOIN {Tables.TeamMatchLocation} tml ON t.TeamId = tml.TeamId
LEFT JOIN {Tables.MatchLocation} ml ON tml.MatchLocationid = ml.MatchLocationId
{where}
AND sv.SchoolVersionId = (SELECT TOP 1 SchoolVersionId FROM {Tables.SchoolVersion} WHERE SchoolId = sc.SchoolId ORDER BY ISNULL(UntilDate, '{SqlDateTime.MaxValue.Value.Date.ToString("yyyy-MM-dd", CultureInfo.InvariantCulture)}') DESC)",
new DynamicParameters(parameters)).ConfigureAwait(false));
}
}
public async Task Read_schools_supports_case_insensitive_filter_by_partial_name()
{
var schoolDataSource = new SqlServerSchoolDataSource(_databaseFixture.ConnectionFactory, Mock.Of <IRouteNormaliser>());
var schoolName = PartialNameOfAnySchool();
var query = new SchoolFilter
{
Query = schoolName.ToLower(CultureInfo.CurrentCulture),
Paging = new Paging {
PageSize = _databaseFixture.TestData.Schools.Count
}
};
var result = await schoolDataSource.ReadSchools(query).ConfigureAwait(false);
foreach (var school in SchoolsMatchingQuery(query.Query))
{
Assert.NotNull(result.Single(x => x.SchoolId == school.SchoolId));
}
}
public async Task Read_schools_supports_case_insensitive_filter_by_administrative_area()
{
var schoolDataSource = new SqlServerSchoolDataSource(_databaseFixture.ConnectionFactory, Mock.Of <IRouteNormaliser>());
var schoolWithLocality = _databaseFixture.TestData.Schools.First(x => x.Teams.Any(t => t.MatchLocations.Any(ml => !string.IsNullOrEmpty(ml.AdministrativeArea))));
var administrativeArea = schoolWithLocality.Teams.First(x => x.MatchLocations.Any(ml => !string.IsNullOrEmpty(ml.AdministrativeArea))).MatchLocations.First(ml => !string.IsNullOrEmpty(ml.AdministrativeArea)).AdministrativeArea;
var query = new SchoolFilter
{
Query = administrativeArea.ToLower(CultureInfo.CurrentCulture),
Paging = new Paging {
PageSize = _databaseFixture.TestData.Schools.Count
}
};
var result = await schoolDataSource.ReadSchools(query).ConfigureAwait(false);
foreach (var school in SchoolsMatchingQuery(query.Query))
{
Assert.NotNull(result.Single(x => x.SchoolId == school.SchoolId));
}
}
private static (string sql, Dictionary <string, object> parameters) BuildWhereClause(SchoolFilter filter)
{
var where = new List <string>();
var parameters = new Dictionary <string, object>();
if (!string.IsNullOrEmpty(filter?.Query))
{
where.Add("(sv.SchoolName LIKE @Query OR ml.Locality LIKE @Query OR ml.Town LIKE @Query OR ml.AdministrativeArea LIKE @Query)");
parameters.Add("@Query", $"%{filter.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
}
