private async Task <int> ReadTotalPlayersWithData(string fieldName, StatisticsFilter filter, bool fieldValueCanBeNegative)
{
var(where, parameters) = _statisticsQueryBuilder.BuildWhereClause(filter);
using (var connection = _databaseConnectionFactory.CreateDatabaseConnection())
{
var minimumRequirement = fieldValueCanBeNegative ? $"{fieldName} IS NOT NULL" : $"{fieldName} > 0";
return(await connection.ExecuteScalarAsync <int>($"SELECT COUNT(DISTINCT PlayerId) FROM {Tables.PlayerInMatchStatistics} WHERE {minimumRequirement} {where}", parameters).ConfigureAwait(false));
}
}
private async Task <int> ReadTotalBestFiguresInAMatch(string primaryFieldName, int?minimumValue, StatisticsFilter filter)
{
var(where, parameters) = _statisticsQueryBuilder.BuildWhereClause(filter);
where = $"WHERE {primaryFieldName} IS NOT NULL {where}";
if (minimumValue != null)
{
where += $" AND {primaryFieldName} >= {minimumValue}";
}
return(await ReadTotalResultsForPagedQuery(where, parameters).ConfigureAwait(false));
}
public async Task <InningsStatistics> ReadInningsStatistics(StatisticsFilter statisticsFilter)
{
if (statisticsFilter == null)
{
statisticsFilter = new StatisticsFilter();
}
var(where, parameters) = _statisticsQueryBuilder.BuildWhereClause(statisticsFilter);
var sql = $@"SELECT AVG(CAST(TeamRunsScored AS DECIMAL)) AS AverageRunsScored,
MAX(TeamRunsScored) AS HighestRunsScored,
MIN(TeamRunsScored) AS LowestRunsScored,
AVG(CAST(TeamRunsConceded AS DECIMAL)) AS AverageRunsConceded,
MAX(TeamRunsConceded) AS HighestRunsConceded,
MIN(TeamRunsConceded) AS LowestRunsConceded,
AVG(CAST(TeamWicketsLost AS DECIMAL)) AS AverageWicketsLost,
AVG(CAST(TeamWicketsTaken AS DECIMAL)) AS AverageWicketsTaken
FROM (
SELECT SUM(DISTINCT TeamRunsScored) AS TeamRunsScored,
SUM(DISTINCT TeamWicketsLost) AS TeamWicketsLost,
SUM(DISTINCT TeamRunsConceded) AS TeamRunsConceded,
SUM(DISTINCT TeamWicketsTaken) AS TeamWicketsTaken
FROM {Tables.PlayerInMatchStatistics}
WHERE 1=1 {where}
GROUP BY MatchId, MatchTeamId, MatchInningsPair
) AS MatchData";
using (var connection = _databaseConnectionFactory.CreateDatabaseConnection())
{
return(await connection.QuerySingleAsync <InningsStatistics>(sql, parameters).ConfigureAwait(false));
}
}
private async Task <int> ReadTotalPlayersWithData(string divideThisField, string byThisField, bool requireBothFields, StatisticsFilter filter)
{
var(where, parameters) = _statisticsQueryBuilder.BuildWhereClause(filter);
using (var connection = _databaseConnectionFactory.CreateDatabaseConnection())
{
var sql = $@"SELECT COUNT(PlayerId) FROM (
SELECT PlayerId FROM {Tables.PlayerInMatchStatistics}
WHERE {divideThisField} IS NOT NULL {(requireBothFields ? $"AND {byThisField} IS NOT NULL" : string.Empty)} {where}
GROUP BY PlayerId
HAVING SUM({byThisField}) > 0 AND
COUNT(PlayerInMatchStatisticsId) >= @MinimumQualifyingInnings
) AS Total";
parameters.Add("@MinimumQualifyingInnings", filter.MinimumQualifyingInnings ?? 1);
return(await connection.ExecuteScalarAsync <int>(sql, parameters).ConfigureAwait(false));
}
}
public async Task <BattingStatistics> ReadBattingStatistics(StatisticsFilter filter)
{
if (filter == null)
{
throw new ArgumentNullException(nameof(filter));
}
if (filter?.Player?.PlayerId == null)
{
throw new ArgumentException("Player.PlayerId must be specified", nameof(filter));
}
var(where, parameters) = _statisticsQueryBuilder.BuildWhereClause(filter);
var sql = $@"SELECT TotalInnings, TotalInningsWithRunsScored, TotalInningsWithRunsScoredAndBallsFaced, NotOuts, TotalRunsScored, Fifties, Hundreds, BestInningsRunsScored, BestInningsWasDismissed, StrikeRate,
CASE WHEN DismissalsWithRunsScored > 0 THEN CAST(TotalRunsScored AS DECIMAL)/DismissalsWithRunsScored ELSE NULL END AS Average
FROM (
SELECT
(SELECT COUNT(PlayerInMatchStatisticsId) FROM {Tables.PlayerInMatchStatistics} WHERE DismissalType != {(int)DismissalType.DidNotBat} {where}) AS TotalInnings,
(SELECT COUNT(PlayerInMatchStatisticsId) FROM {Tables.PlayerInMatchStatistics} WHERE DismissalType != {(int)DismissalType.DidNotBat} AND RunsScored IS NOT NULL {where}) AS TotalInningsWithRunsScored,
(SELECT COUNT(PlayerInMatchStatisticsId) FROM {Tables.PlayerInMatchStatistics} WHERE DismissalType != {(int)DismissalType.DidNotBat} AND RunsScored IS NOT NULL AND BallsFaced IS NOT NULL {where}) AS TotalInningsWithRunsScoredAndBallsFaced,
(SELECT COUNT(PlayerInMatchStatisticsId) FROM {Tables.PlayerInMatchStatistics} WHERE DismissalType IN ({(int)DismissalType.NotOut},{(int)DismissalType.Retired},{(int)DismissalType.RetiredHurt}) {where}) AS NotOuts,
(SELECT COUNT(PlayerInMatchStatisticsId) FROM {Tables.PlayerInMatchStatistics} WHERE PlayerWasDismissed = 1 AND RunsScored IS NOT NULL {where}) AS DismissalsWithRunsScored,
(SELECT SUM(RunsScored) FROM {Tables.PlayerInMatchStatistics} WHERE RunsScored IS NOT NULL {where}) AS TotalRunsScored,
(SELECT COUNT(PlayerInMatchStatisticsId) FROM {Tables.PlayerInMatchStatistics} WHERE RunsScored >= 50 {where}) AS Fifties,
(SELECT COUNT(PlayerInMatchStatisticsId) FROM {Tables.PlayerInMatchStatistics} WHERE RunsScored >= 100 {where}) AS Hundreds,
(SELECT CASE WHEN SUM(BallsFaced) > 0 THEN (CAST(SUM(RunsScored) AS DECIMAL)/SUM(BallsFaced))*100 ELSE NULL END
FROM {Tables.PlayerInMatchStatistics} WHERE DismissalType != {(int)DismissalType.DidNotBat} AND RunsScored IS NOT NULL AND BallsFaced IS NOT NULL {where}) AS StrikeRate,
(SELECT TOP 1 RunsScored FROM {Tables.PlayerInMatchStatistics} WHERE 1=1 {where} ORDER BY RunsScored DESC, PlayerWasDismissed ASC) AS BestInningsRunsScored,
(SELECT TOP 1 PlayerWasDismissed FROM {Tables.PlayerInMatchStatistics} WHERE 1=1 {where} ORDER BY RunsScored DESC, PlayerWasDismissed ASC) AS BestInningsWasDismissed
) AS BattingStatistics" ;
using (var connection = _databaseConnectionFactory.CreateDatabaseConnection())
{
return(await connection.QuerySingleAsync <BattingStatistics>(sql, parameters)
.ConfigureAwait(false));
}
}
public async Task <IEnumerable <StatisticsResult <PlayerInnings> > > ReadPlayerInnings(StatisticsFilter filter)
{
if (filter == null)
{
throw new ArgumentNullException(nameof(filter));
}
var sql = $@"SELECT MatchName, MatchRoute, MatchStartTime AS StartTime, PlayerIdentityName, PlayerRoute, PlayerInningsId, DismissalType, RunsScored, BowledByPlayerIdentityName AS PlayerIdentityName, BowledByPlayerRoute AS PlayerRoute
FROM {Tables.PlayerInMatchStatistics}
<<WHERE>>
ORDER BY MatchStartTime DESC
OFFSET @PageOffset ROWS FETCH NEXT @PageSize ROWS ONLY";
var(where, parameters) = _statisticsQueryBuilder.BuildWhereClause(filter);
sql = sql.Replace("<<WHERE>>", $"WHERE 1=1 {where}");
parameters.Add("PageOffset", (filter.Paging.PageNumber - 1) * filter.Paging.PageSize);
parameters.Add("PageSize", filter.Paging.PageSize);
using (var connection = _databaseConnectionFactory.CreateDatabaseConnection())
{
return(await connection.QueryAsync <MatchListing, PlayerIdentity, Player, PlayerInnings, PlayerIdentity, Player, StatisticsResult <PlayerInnings> >(sql,
(match, batterIdentity, batter, playerInnings, bowlerIdentity, bowler) =>
{
batterIdentity.Player = batter;
playerInnings.Batter = batterIdentity;
if (bowlerIdentity != null)
{
bowlerIdentity.Player = bowler;
playerInnings.Bowler = bowlerIdentity;
}
return new StatisticsResult <PlayerInnings>
{
Match = match,
Result = playerInnings
};
},
parameters,
splitOn : "PlayerIdentityName, PlayerRoute, PlayerInningsId, PlayerIdentityName, PlayerRoute").ConfigureAwait(false));
}
}