List<AggregatedChampionStatistics> LoadAggregatedChampionStatistics(Summoner summoner, MapType map, GameModeType gameMode, NpgsqlConnection database) { const string query = "with source as " + "(select player.champion_id, player.won, player.kills, player.deaths, player.assists, player.gold, player.minion_kills from game_result, player where game_result.map = cast(:map as map_type) and game_result.game_mode = cast(:game_mode as game_mode_type) and (game_result.team1_id = player.team_id or game_result.team2_id = player.team_id) and player.summoner_id = :summoner_id) " + "select statistics.champion_id, coalesce(champion_wins.wins, 0) as wins, coalesce(champion_losses.losses, 0) as losses, statistics.kills, statistics.deaths, statistics.assists, statistics.gold, statistics.minion_kills from " + "(select source.champion_id, sum(source.kills) as kills, sum(source.deaths) as deaths, sum(source.assists) as assists, sum(source.gold) as gold, sum(source.minion_kills) as minion_kills from source group by source.champion_id) " + "as statistics " + "left outer join " + "(select champion_id, count(*) as wins from source where won = true group by champion_id) " + "as champion_wins " + "on statistics.champion_id = champion_wins.champion_id " + "left outer join " + "(select champion_id, count(*) as losses from source where won = false group by champion_id) " + "as champion_losses " + "on statistics.champion_id = champion_losses.champion_id;"; DatabaseCommand select = GetCommand(query, database); select.SetEnum("map", map.ToEnumString()); select.SetEnum("game_mode", gameMode.ToEnumString()); select.Set("summoner_id", summoner.Id); using (NpgsqlDataReader reader = select.ExecuteReader()) { List<AggregatedChampionStatistics> output = new List<AggregatedChampionStatistics>(); while (reader.Read()) { AggregatedChampionStatistics statistics = new AggregatedChampionStatistics(reader); statistics.ChampionName = GetChampionName(statistics.ChampionId); output.Add(statistics); } output.Sort(); return output; } }
List<AggregatedChampionStatistics> LoadAggregatedChampionStatistics(Summoner summoner, MapType map, GameModeType gameMode, DbConnection database) { const string query = "with source as " + "(select team_player.champion_id, team_player.won, team_player.kills, team_player.deaths, team_player.assists, team_player.gold, team_player.minion_kills from game_result, team_player where game_result.result_map = cast(:result_map as map_type) and game_result.game_mode = cast(:game_mode as game_mode_type) and (game_result.team1_id = team_player.team_id or game_result.team2_id = team_player.team_id) and team_player.summoner_id = :summoner_id) " + "select statistics.champion_id, coalesce(champion_wins.wins, 0) as wins, coalesce(champion_losses.losses, 0) as losses, statistics.kills, statistics.deaths, statistics.assists, statistics.gold, statistics.minion_kills from " + "(select source.champion_id, sum(source.kills) as kills, sum(source.deaths) as deaths, sum(source.assists) as assists, sum(source.gold) as gold, sum(source.minion_kills) as minion_kills from source group by source.champion_id) " + "as statistics " + "left outer join " + "(select champion_id, count(*) as wins from source where won = true group by champion_id) " + "as champion_wins " + "on statistics.champion_id = champion_wins.champion_id " + "left outer join " + "(select champion_id, count(*) as losses from source where won = false group by champion_id) " + "as champion_losses " + "on statistics.champion_id = champion_losses.champion_id;", myquery = @"SELECT statistics.champion_id, coalesce(champion_wins.wins, 0) as wins, coalesce(champion_losses.losses, 0) as losses, statistics.kills, statistics.deaths, statistics.assists, statistics.gold, statistics.minion_kills FROM ( SELECT source.champion_id, sum(source.kills) as kills, sum(source.deaths) as deaths, sum(source.assists) as assists, sum(source.gold) as gold, sum(source.minion_kills) as minion_kills FROM source WHERE source.result_map = ?result_map AND source.game_mode = ?game_mode AND source.summoner_id = ?summoner_id GROUP BY source.champion_id ) AS statistics LEFT OUTER JOIN ( SELECT champion_id, count(*) as wins FROM source WHERE source.won = true AND source.result_map = ?result_map AND source.game_mode = ?game_mode AND source.summoner_id = ?summoner_id GROUP BY champion_id ) champion_wins ON statistics.champion_id = champion_wins.champion_id LEFT OUTER JOIN ( SELECT champion_id, count(*) as losses FROM source WHERE source.won = false AND source.result_map = ?result_map AND source.game_mode = ?game_mode AND source.summoner_id = ?summoner_id GROUP BY champion_id ) champion_losses ON statistics.champion_id = champion_losses.champion_id;"; SQLCommand select; if (database is MySql.Data.MySqlClient.MySqlConnection) select = GetCommand(myquery, database); else select = GetCommand(query, database); select.SetEnum("result_map", map.ToEnumString()); select.SetEnum("game_mode", gameMode.ToEnumString()); select.Set("summoner_id", summoner.Id); using (DbDataReader reader = select.ExecuteReader()) { List<AggregatedChampionStatistics> output = new List<AggregatedChampionStatistics>(); while (reader.Read()) { AggregatedChampionStatistics statistics = new AggregatedChampionStatistics(reader); statistics.ChampionName = GetChampionName(statistics.ChampionId); output.Add(statistics); } output.Sort(); return output; } }