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));
 }
Exemplo n.º 3
0
        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));
            }
        }
Exemplo n.º 6
0
        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));
            }
        }