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;
}
}