private async Task <IEnumerable <StatisticsResult <BestStatistic> > > ReadBestPlayerAverage(string divideThisField, string byThisField, int multiplier, string sortOrder, bool isFieldingStatistic, string inningsFilter, StatisticsFilter filter)
{
var clonedFilter = filter.Clone();
clonedFilter.SwapBattingFirstFilter = isFieldingStatistic;
var(where, parameters) = _statisticsQueryBuilder.BuildWhereClause(clonedFilter);
var sql = $@"SELECT PlayerId, PlayerRoute, TotalMatches, TotalInnings, Average
FROM(
SELECT PlayerId, PlayerRoute, (CAST(SUM({divideThisField}) AS DECIMAL) / SUM({byThisField}))*{multiplier} AS Average,
(SELECT COUNT(DISTINCT MatchId) FROM { Tables.PlayerInMatchStatistics} WHERE PlayerId = s.PlayerId {where}) AS TotalMatches,
(SELECT COUNT(PlayerInMatchStatisticsId) FROM { Tables.PlayerInMatchStatistics} WHERE PlayerId = s.PlayerId {inningsFilter} {where}) AS TotalInnings
FROM {Tables.PlayerInMatchStatistics} AS s
WHERE 1=1 {inningsFilter} {where}
GROUP BY PlayerId, PlayerRoute
HAVING SUM({byThisField}) > 0
AND (SELECT COUNT(PlayerInMatchStatisticsId) FROM {Tables.PlayerInMatchStatistics} WHERE PlayerId = s.PlayerId {inningsFilter} {where}) >= @MinimumQualifyingInnings
ORDER BY CAST(SUM({divideThisField}) AS DECIMAL) / SUM({byThisField}) {sortOrder}, TotalInnings DESC, TotalMatches DESC
OFFSET @PageOffset ROWS FETCH NEXT @PageSize ROWS ONLY
) AS BestAverage
ORDER BY Average {sortOrder}, TotalInnings DESC, TotalMatches DESC";
parameters.Add("@MinimumQualifyingInnings", filter.MinimumQualifyingInnings ?? 1);
parameters.Add("@PageOffset", clonedFilter.Paging.PageSize * (clonedFilter.Paging.PageNumber - 1));
parameters.Add("@PageSize", clonedFilter.Paging.PageSize);
using (var connection = _databaseConnectionFactory.CreateDatabaseConnection())
{
var results = await connection.QueryAsync <Player, BestStatistic, StatisticsResult <BestStatistic> >(sql,
(player, totals) =>
{
totals.Player = player;
return(new StatisticsResult <BestStatistic>
{
Result = totals
});
},
parameters,
splitOn : $"TotalMatches",
commandTimeout : 60).ConfigureAwait(false);
var players = await _playerDataSource.ReadPlayers(new PlayerFilter { PlayerIds = results.Select(x => x.Result.Player.PlayerId.Value).ToList() }).ConfigureAwait(false);
foreach (var result in results)
{
result.Result.Player = players.Single(x => x.PlayerId == result.Result.Player.PlayerId);
}
return(results);
}
}
private async Task <IEnumerable <StatisticsResult <BestStatistic> > > ReadBestPlayerTotal(string fieldName, bool fieldValueCanBeNegative, bool isFieldingStatistic, string extraSelectFields, string outerQueryIncludingOrderBy, string totalInningsFilter, StatisticsFilter filter)
{
var clonedFilter = filter.Clone();
clonedFilter.SwapBattingFirstFilter = isFieldingStatistic;
var(where, parameters) = _statisticsQueryBuilder.BuildWhereClause(clonedFilter);
var group = "GROUP BY PlayerId, PlayerRoute";
var having = fieldValueCanBeNegative ? "HAVING 1=1" : $"HAVING SUM({fieldName}) > 0";
var minimumValue = fieldValueCanBeNegative ? string.Empty : $"AND {fieldName} >= 0";
// The result set can be limited in two mutually-exlusive ways:
// 1. Max results (eg top ten) but where results beyond but equal to the max are also included
// 2. Paging
var preQuery = string.Empty;
var offsetWithExtraResults = string.Empty;
var offsetPaging = string.Empty;
if (clonedFilter.MaxResultsAllowingExtraResultsIfValuesAreEqual.HasValue)
{
// Get the values from what should be the last row according to the maximum number of results.
preQuery = $@"DECLARE @MaxResult int;
SELECT @MaxResult = SUM({fieldName}) FROM {Tables.PlayerInMatchStatistics} WHERE {fieldName} IS NOT NULL {minimumValue} {where} {group} {having} ORDER BY SUM({fieldName}) DESC
OFFSET {clonedFilter.MaxResultsAllowingExtraResultsIfValuesAreEqual - 1} ROWS FETCH NEXT 1 ROWS ONLY; ";
// If @MaxResult IS NULL there are fewer rows than the requested maximum, so just fetch all.
// Otherwise look for results that are greater than or equal to the value(s) in the last row retrieved above.
offsetWithExtraResults = $"AND (@MaxResult IS NULL OR SUM({fieldName}) >= @MaxResult) ";
// Add an ORDER BY clause to sort the results, unless we're relying on an outer query to do that because it's not valid in a sub-query
if (string.IsNullOrEmpty(outerQueryIncludingOrderBy))
{
offsetWithExtraResults += $"ORDER BY SUM({fieldName}) DESC, TotalInnings ASC, TotalMatches ASC";
}
}
else
{
offsetPaging = $"ORDER BY SUM({fieldName}) DESC, TotalInnings ASC, TotalMatches ASC OFFSET @PageOffset ROWS FETCH NEXT @PageSize ROWS ONLY";
parameters.Add("@PageOffset", clonedFilter.Paging.PageSize * (clonedFilter.Paging.PageNumber - 1));
parameters.Add("@PageSize", clonedFilter.Paging.PageSize);
}
var totalInningsQuery = !string.IsNullOrEmpty(totalInningsFilter) ? $"SELECT COUNT(PlayerInMatchStatisticsId) FROM { Tables.PlayerInMatchStatistics} WHERE PlayerId = s.PlayerId {totalInningsFilter} {where}" : "NULL";
var sql = $@"SELECT PlayerId, PlayerRoute,
(SELECT COUNT(DISTINCT MatchId) FROM { Tables.PlayerInMatchStatistics} WHERE PlayerId = s.PlayerId {where}) AS TotalMatches,
({totalInningsQuery}) AS TotalInnings,
(SELECT SUM({ fieldName}) FROM { Tables.PlayerInMatchStatistics} WHERE PlayerId = s.PlayerId {where}) AS Total
<<SELECT>>
FROM {Tables.PlayerInMatchStatistics} AS s
WHERE {fieldName} IS NOT NULL {minimumValue} {where}
{group}
{having}
{offsetWithExtraResults}
{offsetPaging}";
if (!string.IsNullOrEmpty(extraSelectFields))
{
extraSelectFields = extraSelectFields.Replace("<<WHERE>>", where);
sql = sql.Replace("<<SELECT>>", extraSelectFields);
}
else
{
sql = sql.Replace("<<SELECT>>", string.Empty);
}
if (!string.IsNullOrEmpty(outerQueryIncludingOrderBy))
{
sql = outerQueryIncludingOrderBy.Replace("<<QUERY>>", sql);
}
using (var connection = _databaseConnectionFactory.CreateDatabaseConnection())
{
var results = await connection.QueryAsync <Player, BestStatistic, StatisticsResult <BestStatistic> >($"{preQuery} {sql}",
(player, totals) =>
{
totals.Player = player;
return(new StatisticsResult <BestStatistic>
{
Result = totals
});
},
parameters,
splitOn : $"TotalMatches",
commandTimeout : 60).ConfigureAwait(false);
var players = await _playerDataSource.ReadPlayers(new PlayerFilter { PlayerIds = results.Select(x => x.Result.Player.PlayerId.Value).ToList() }).ConfigureAwait(false);
foreach (var result in results)
{
result.Result.Player = players.Single(x => x.PlayerId == result.Result.Player.PlayerId);
}
return(results);
}
}