void UpdateSummoner(Summoner summoner, ConcurrentRPC concurrentRPC, DbConnection connection)
{
int accountId = summoner.AccountId;
lock (ActiveAccountIds)
{
//Avoid concurrent updates of the same account, it's asking for trouble and is redundant anyways
//We might obtain outdated results in one query but that's a minor issue in comparison to corrupted database results
if (ActiveAccountIds.Contains(accountId))
return;
ActiveAccountIds.Add(accountId);
}
//Use a transaction because we're going to insert a fair amount of data
using (var transaction = connection.BeginTransaction())
{
UpdateSummonerFields(summoner, connection, true);
UpdateRunes(summoner, concurrentRPC.PublicSummonerData, connection);
UpdateSummonerRatings(summoner, concurrentRPC.LifeTimeStatistics, connection);
//A season value of zero indicates the current season only
UpdateSummonerRankedStatistics(summoner, 0, concurrentRPC.AggregatedStatistics, connection);
UpdateSummonerGames(summoner, concurrentRPC.RecentGameData, connection);
transaction.Commit();
}
lock (ActiveAccountIds)
ActiveAccountIds.Remove(accountId);
}
void SetSummaryParameters(DatabaseCommand command, MapType map, GameModeType gameMode, Summoner summoner, PlayerStatSummary summary, bool forceNullRating)
{
if (forceNullRating)
{
command.Set("current_rating", DbType.Int32, null);
command.Set("top_rating", DbType.Int32, null);
}
else
{
//Zero rating means that the Elo is below 1200 and is not revealed by the server
if (summary.rating == 0)
command.Set("current_rating", DbType.Int32, null);
else
command.Set("current_rating", summary.rating);
command.Set("top_rating", summary.maxRating);
}
command.Set("summoner_id", summoner.Id);
command.Set("map", (int)map);
command.Set("game_mode", (int)gameMode);
command.Set("wins", summary.wins);
command.Set("losses", summary.losses);
command.Set("leaves", summary.leaves);
}
public void AddSummonerToCache(RegionType region, Summoner summoner)
{
lock (SummonerCache)
{
SummonerCache[summoner.Region][summoner.AccountId] = summoner;
}
}
void ProcessSummary(MapType map, GameModeType gameMode, string target, Summoner summoner, List<PlayerStatSummary> summaries, DbConnection connection, bool forceNullRating = false)
{
foreach (var summary in summaries)
{
if (summary.playerStatSummaryType != target)
continue;
using (var update = Command("update summoner_rating set wins = :wins, losses = :losses, leaves = :leaves, kills = :kills, deaths = :deaths, assists = :assists, current_rating = :current_rating, top_rating = :top_rating where summoner_id = :summoner_id and map = :map and game_mode = :game_mode", connection))
{
SetSummaryParameters(update, map, gameMode, summoner, summary, forceNullRating);
int rowsAffected = update.Execute();
if (rowsAffected == 0)
{
//We're dealing with a new summoner rating entry, insert it
using (var insert = Command("insert into summoner_rating (summoner_id, map, game_mode, wins, losses, leaves, kills, deaths, assists, current_rating, top_rating) values (:summoner_id, :map, :game_mode, :wins, :losses, :leaves, :kills, :deaths, :assists, :current_rating, :top_rating)", connection))
{
SetSummaryParameters(insert, map, gameMode, summoner, summary, forceNullRating);
insert.Execute();
//SummonerMessage(string.Format("New rating for mode {0}", target), summoner);
}
}
else
{
//This rating was already in the database and was updated
//SummonerMessage(string.Format("Updated rating for mode {0}", target), summoner);
}
break;
}
}
}
void UpdateRunes(Summoner summoner, AllPublicSummonerDataDTO publicSummonerData, DbConnection connection)
{
//Remove old rune pages from the database first
using (var delete = Command("delete from rune_page where summoner_id = :summoner_id", connection))
{
delete.Set("summoner_id", summoner.Id);
delete.Execute();
}
foreach (var page in publicSummonerData.spellBook.bookPages)
{
string[] pageFields =
{
"summoner_id",
"name",
"is_current_rune_page",
"time_created",
};
using (var insert = Command("insert into rune_page ({0}) values ({1})", connection, GetGroupString(pageFields), GetPlaceholderString(pageFields)))
{
insert.SetFieldNames(pageFields);
insert.Set(summoner.Id);
insert.Set(page.name);
insert.Set(page.current);
insert.Set(page.createDate.ToUnixTime());
insert.Execute();
}
int runePageId = GetInsertId("rune_page", connection);
string[] runeFields =
{
"rune_page_id",
"rune_slot",
"rune",
};
foreach (var rune in page.slotEntries)
{
using (var insert = Command("insert into rune_slot ({0}) values ({1})", connection, GetGroupString(runeFields), GetPlaceholderString(runeFields)))
{
insert.SetFieldNames(runeFields);
insert.Set(runePageId);
insert.Set(rune.runeSlotId);
insert.Set(rune.runeId);
insert.Execute();
}
}
}
}
void SetSummaryParameters(DatabaseCommand command, MapType map, GameModeType gameMode, Summoner summoner, PlayerStatSummary summary, bool forceNullRating)
{
if (forceNullRating)
{
command.Set("current_rating", DbType.Int32, DBNull.Value);
command.Set("top_rating", DbType.Int32, DBNull.Value);
}
else
{
//Zero rating means that the Elo is below 1200 and is not revealed by the server
if (summary.rating == 0)
command.Set("current_rating", DbType.Int32, DBNull.Value);
else
command.Set("current_rating", summary.rating);
command.Set("top_rating", summary.maxRating);
}
command.Set("summoner_id", summoner.Id);
command.Set("map", (int)map);
command.Set("game_mode", (int)gameMode);
command.Set("wins", summary.wins);
command.Set("losses", summary.losses);
command.Set("leaves", summary.leaves);
int k = 0, d = 0, a = 0;
var kills = summary.aggregatedStats.stats.FirstOrDefault(e => e.statType == "TOTAL_CHAMPION_KILLS");
if (kills != null)
{
k = kills.value;
}
var deaths = summary.aggregatedStats.stats.FirstOrDefault(e => e.statType == "TOTAL_DEATHS_PER_SESSION");
if (deaths != null)
{
d = deaths.value;
}
var assists = summary.aggregatedStats.stats.FirstOrDefault(e => e.statType == "TOTAL_ASSISTS");
if (assists != null)
{
a = assists.value;
}
command.Set("kills", k);
command.Set("deaths", d);
command.Set("assists", a);
}
List<ExtendedPlayer> LoadSummonerGames(Summoner summoner, DbConnection connection)
{
List<ExtendedPlayer> output = new List<ExtendedPlayer>();
using (var select = Command("select {0} from game, player where game.id = player.game_id and player.summoner_id = :summoner_id order by game.time desc", connection, ExtendedPlayer.GetFields()))
{
select.Set("summoner_id", summoner.Id);
using (var reader = select.ExecuteReader())
{
while (reader.Read())
{
ExtendedPlayer player = new ExtendedPlayer(reader);
output.Add(player);
}
}
}
return output;
}
void UpdateSummonerFields(Summoner summoner, DbConnection connection, bool isFullUpdate = false)
{
string[] fields =
{
"summoner_name",
"internal_name",
"summoner_level",
"profile_icon",
"has_been_updated",
"time_updated",
};
long currentTime = Time.UnixTime();
if (isFullUpdate)
{
summoner.HasBeenUpdated = true;
summoner.TimeUpdated = (int)currentTime;
}
using (var update = Command("update summoner set {0} where id = :summoner_id", connection, GetUpdateString(fields)))
{
update.Set("summoner_id", summoner.Id);
update.SetFieldNames(fields);
update.Set(summoner.SummonerName);
update.Set(summoner.InternalName);
update.Set(summoner.SummonerLevel);
update.Set(summoner.ProfileIcon);
update.Set(summoner.HasBeenUpdated);
update.Set(currentTime);
update.Execute();
}
//Inform the statistics service about the update
StatisticsService.AddSummonerToCache(Region, summoner);
}
List <SummonerRankedStatistics> GetSummonerRankedStatistics(Summoner summoner, int season, DbConnection connection)
{
using (var select = Command("select {0} from summoner_ranked_statistics where summoner_id = :summoner_id and season = :season", connection, SummonerRankedStatistics.GetFields()))
{
select.Set("summoner_id", summoner.Id);
select.Set("season", season);
using (var reader = select.ExecuteReader())
{
List <SummonerRankedStatistics> output = new List <SummonerRankedStatistics>();
while (reader.Read())
{
SummonerRankedStatistics statistics = new SummonerRankedStatistics(reader);
output.Add(statistics);
}
return(output);
}
}
}
List<SummonerRankedStatistics> GetSummonerRankedStatistics(Summoner summoner, int season, DbConnection connection)
{
using (var select = Command("select {0} from summoner_ranked_statistics where summoner_id = :summoner_id and season = :season", connection, SummonerRankedStatistics.GetFields()))
{
select.Set("summoner_id", summoner.Id);
select.Set("season", season);
using (var reader = select.ExecuteReader())
{
List<SummonerRankedStatistics> output = new List<SummonerRankedStatistics>();
while (reader.Read())
{
SummonerRankedStatistics statistics = new SummonerRankedStatistics(reader);
output.Add(statistics);
}
return output;
}
}
}
public OperationResult UpdateSummonerByAccountId(int accountId)
{
if (!Connected)
{
return(OperationResult.NotConnected);
}
WriteLine("Updating account {0}", accountId);
ConcurrentRPC concurrentRPC = new ConcurrentRPC(RPC, accountId);
OperationResult result = concurrentRPC.Run();
if (result == OperationResult.Success)
{
if (concurrentRPC.PublicSummonerData == null)
{
//This means that the summoner was not found, even though the other structures are actually non-null
return(OperationResult.NotFound);
}
Summoner newSummoner = new Summoner(concurrentRPC.PublicSummonerData, Region);
Summoner summoner = StatisticsService.GetSummoner(Region, accountId);
if (summoner == null)
{
//The summoner wasn't in the database yet, add them
using (var connection = Provider.GetConnection())
InsertNewSummoner(newSummoner, connection);
summoner = newSummoner;
}
else
{
//Copy data that might have been changed
summoner.SummonerName = newSummoner.SummonerName;
summoner.InternalName = newSummoner.InternalName;
summoner.SummonerLevel = newSummoner.SummonerLevel;
summoner.ProfileIcon = newSummoner.ProfileIcon;
}
//Perform a full update
using (var connection = Provider.GetConnection())
UpdateSummoner(summoner, concurrentRPC, connection);
return(OperationResult.Success);
}
return(result);
}
List <ExtendedPlayer> GetSummonerGames(Summoner summoner, DbConnection connection)
{
bool useItemArray = !connection.IsMySQL();
List <ExtendedPlayer> output = new List <ExtendedPlayer>();
using (var select = Command("select {0} from game, player where game.id = player.game_id and player.summoner_id = :summoner_id order by game.time desc", connection, ExtendedPlayer.GetFields(useItemArray)))
{
select.Set("summoner_id", summoner.Id);
using (var reader = select.ExecuteReader())
{
while (reader.Read())
{
ExtendedPlayer player = new ExtendedPlayer(reader, useItemArray);
output.Add(player);
}
}
return(output);
}
}
List <RunePage> GetRunePages(Summoner summoner)
{
List <RunePage> output = new List <RunePage>();
using (var connection = GetConnection())
{
using (var pageSelect = Command("select {0} from rune_page where summoner_id = :summoner_id", connection, RunePage.GetFields()))
{
pageSelect.Set("summoner_id", summoner.Id);
using (var pageReader = pageSelect.ExecuteReader())
{
while (pageReader.Read())
{
RunePage page = new RunePage(pageReader);
output.Add(page);
}
}
}
foreach (var page in output)
{
using (var slotSelect = Command("select {0} from rune_slot where rune_page_id = :rune_page_id", connection, RuneSlot.GetFields()))
{
slotSelect.Set("rune_page_id", page.Id);
using (var slotReader = slotSelect.ExecuteReader())
{
while (slotReader.Read())
{
RuneSlot slot = new RuneSlot(slotReader);
page.Slots.Add(slot);
}
}
}
page.Slots.Sort((x, y) => x.Slot.CompareTo(y.Slot));
}
}
return(output);
}
Reply ApiSummonerProfile(Request request)
{
PrivilegeCheck(request);
var arguments = request.Arguments;
string regionAbbreviation = (string)request.Arguments[0];
int accountId = (int)request.Arguments[1];
Worker worker = GetWorkerByAbbreviation(regionAbbreviation);
SummonerProfileResult output;
Summoner summoner = StatisticsService.GetSummoner(worker.Region, accountId);
if (summoner != null)
{
output = new SummonerProfileResult(summoner);
}
else
{
output = new SummonerProfileResult(OperationResult.NotFound);
}
return(GetJSONReply(output));
}
List<RunePage> GetRunePages(Summoner summoner)
{
List<RunePage> output = new List<RunePage>();
using (var connection = GetConnection())
{
using (var pageSelect = Command("select {0} from rune_page where summoner_id = :summoner_id", connection, RunePage.GetFields()))
{
pageSelect.Set("summoner_id", summoner.Id);
using (var pageReader = pageSelect.ExecuteReader())
{
while (pageReader.Read())
{
RunePage page = new RunePage(pageReader);
output.Add(page);
}
}
}
foreach (var page in output)
{
using (var slotSelect = Command("select {0} from rune_slot where rune_page_id = :rune_page_id", connection, RuneSlot.GetFields()))
{
slotSelect.Set("rune_page_id", page.Id);
using (var slotReader = slotSelect.ExecuteReader())
{
while (slotReader.Read())
{
RuneSlot slot = new RuneSlot(slotReader);
page.Slots.Add(slot);
}
}
}
page.Slots.Sort((x, y) => x.Slot.CompareTo(y.Slot));
}
}
return output;
}
Reply ApiSearch(Request request)
{
PrivilegeCheck(request);
var arguments = request.Arguments;
string regionAbbreviation = (string)request.Arguments[0];
string summonerName = (string)request.Arguments[1];
Worker worker = GetWorkerByAbbreviation(regionAbbreviation);
Summoner summoner = null;
OperationResult result = worker.FindSummoner(summonerName, ref summoner);
SummonerSearchResult output;
if (result == OperationResult.Success)
{
output = new SummonerSearchResult(summoner.AccountId);
}
else
{
output = new SummonerSearchResult(result);
}
return(GetJSONReply(output));
}
OperationResult SetSummonerAutomaticUpdates(Summoner summoner, bool updateAutomatically)
{
using (var connection = GetConnection())
{
using (var update = Command("update summoner set update_automatically = :update_automatically where region = :region and account_id = :account_id", connection))
{
update.Set("update_automatically", updateAutomatically);
update.Set("region", summoner.Region);
update.Set("account_id", summoner.AccountId);
int rowsAffected = update.Execute();
if (rowsAffected > 0)
{
summoner.UpdateAutomatically = updateAutomatically;
return(OperationResult.Success);
}
else
{
return(OperationResult.NotFound);
}
}
}
}
void SetSummonerRankedStatisticsParameters(DatabaseCommand update, Summoner summoner, int season, ChampionStatistics champion)
{
update.SetFieldNames(SummonerRankedStatisticsFields);
update.Set(summoner.Id);
update.Set(season);
update.Set(champion.ChampionId);
update.Set(champion.Wins);
update.Set(champion.Losses);
update.Set(champion.Kills);
update.Set(champion.Deaths);
update.Set(champion.Assists);
update.Set(champion.MinionKills);
update.Set(champion.Gold);
update.Set(champion.TurretsDestroyed);
update.Set(champion.DamageDealt);
update.Set(champion.PhysicalDamageDealt);
update.Set(champion.MagicalDamageDealt);
update.Set(champion.DamageTaken);
update.Set(champion.DoubleKills);
update.Set(champion.TripleKills);
update.Set(champion.QuadraKills);
update.Set(champion.PentaKills);
update.Set(champion.TimeSpentDead);
update.Set(champion.MaximumKills);
update.Set(champion.MaximumDeaths);
}
Reply ApiSetAutomaticUpdates(Request request)
{
PrivilegeCheck(request);
var arguments = request.Arguments;
string regionAbbreviation = (string)request.Arguments[0];
int accountId = (int)request.Arguments[1];
bool updateAutomatically = (int)request.Arguments[2] != 0;
Worker worker = GetWorkerByAbbreviation(regionAbbreviation);
SummonerAutomaticUpdatesResult output;
Summoner summoner = StatisticsService.GetSummoner(worker.Region, accountId);
if (summoner != null)
{
OperationResult result = SetSummonerAutomaticUpdates(summoner, updateAutomatically);
output = new SummonerAutomaticUpdatesResult(result);
}
else
{
output = new SummonerAutomaticUpdatesResult(OperationResult.NotFound);
}
return(GetJSONReply(output));
}
void SetSummonerRankedStatisticsParameters(DatabaseCommand update, Summoner summoner, int season, ChampionStatistics champion)
{
update.SetFieldNames(SummonerRankedStatisticsFields);
update.Set(summoner.Id);
update.Set(season);
update.Set(champion.ChampionId);
update.Set(champion.Wins);
update.Set(champion.Losses);
update.Set(champion.Kills);
update.Set(champion.Deaths);
update.Set(champion.Assists);
update.Set(champion.MinionKills);
update.Set(champion.Gold);
update.Set(champion.TurretsDestroyed);
update.Set(champion.DamageDealt);
update.Set(champion.PhysicalDamageDealt);
update.Set(champion.MagicalDamageDealt);
update.Set(champion.DamageTaken);
update.Set(champion.DoubleKills);
update.Set(champion.TripleKills);
update.Set(champion.QuadraKills);
update.Set(champion.PentaKills);
update.Set(champion.TimeSpentDead);
update.Set(champion.MaximumKills);
update.Set(champion.MaximumDeaths);
}
Reply ApiSummonerStatistics(Request request)
{
Profiler profiler = new Profiler(false, "ApiSummonerStatistics", GlobalHandler);
profiler.Start("PrivilegeCheck");
PrivilegeCheck(request);
profiler.Stop();
profiler.Start("GetSummoner");
var arguments = request.Arguments;
string regionAbbreviation = (string)request.Arguments[0];
int accountId = (int)request.Arguments[1];
Worker worker = GetWorkerByAbbreviation(regionAbbreviation);
SummonerStatisticsResult output;
Summoner summoner = StatisticsService.GetSummoner(worker.Region, accountId);
profiler.Stop();
if (summoner != null)
{
using (var connection = GetConnection())
{
profiler.Start("GetSummonerStatistics");
SummonerStatistics statistics = GetSummonerStatistics(summoner, connection);
profiler.Stop();
profiler.Start("SummonerStatisticsResult");
output = new SummonerStatisticsResult(statistics);
profiler.Stop();
}
}
else
{
output = new SummonerStatisticsResult(OperationResult.NotFound);
}
profiler.Start("GetJSONReply");
Reply reply = GetJSONReply(output);
profiler.Stop();
return(reply);
}
void UpdateSummoner(Summoner summoner, AllPublicSummonerDataDTO publicSummonerData, AggregatedStats[] aggregatedStats, PlayerLifeTimeStats lifeTimeStatistics, RecentGames recentGames, DbConnection connection)
{
int accountId = summoner.AccountId;
lock (ActiveAccountIds)
{
// Avoid concurrent updates of the same account, it's asking for trouble and is redundant anyways
// We might obtain outdated results in one query but that's a minor issue in comparison to corrupted database results
if (ActiveAccountIds.Contains(accountId))
{
return;
}
ActiveAccountIds.Add(accountId);
}
// Use a transaction because we're going to insert a fair amount of data
using (var transaction = connection.BeginTransaction())
{
UpdateSummonerFields(summoner, connection, true);
UpdateRunes(summoner, publicSummonerData, connection);
UpdateSummonerRatings(summoner, lifeTimeStatistics, connection);
// A season value of zero indicates the current season only
for (int season = 0; season < aggregatedStats.Length; season++)
{
UpdateSummonerRankedStatistics(summoner, season, aggregatedStats[season], connection);
}
UpdateSummonerGames(summoner, recentGames, connection);
transaction.Commit();
}
lock (ActiveAccountIds)
ActiveAccountIds.Remove(accountId);
}
Reply ApiSummonerGames(Request request)
{
PrivilegeCheck(request);
var arguments = request.Arguments;
string regionAbbreviation = (string)request.Arguments[0];
int accountId = (int)request.Arguments[1];
Worker worker = GetWorkerByAbbreviation(regionAbbreviation);
SummonerGamesResult output;
Summoner summoner = StatisticsService.GetSummoner(worker.Region, accountId);
if (summoner != null)
{
using (var connection = GetConnection())
{
List <ExtendedPlayer> games = GetSummonerGames(summoner, connection);
output = new SummonerGamesResult(games);
}
}
else
{
output = new SummonerGamesResult(OperationResult.NotFound);
}
return(GetJSONReply(output));
}
List<AggregatedChampionStatistics> LoadAggregatedChampionStatisticsWithCTE(Summoner summoner, MapType map, GameModeType gameMode, DbConnection connection)
{
const string query =
"with source as " +
"(select game.map, game.game_mode, game.blue_team_id, game.purple_team_id, game.blue_team_won, player.team_id, player.summoner_id, player.champion_id, player.kills, player.deaths, player.assists, player.gold, player.minion_kills from game, player where player.summoner_id = :summoner_id and game.map = :map and game.game_mode = :game_mode and (game.blue_team_id = player.team_id or game.purple_team_id = player.team_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 (blue_team_won = 1 and blue_team_id = team_id) or (blue_team_won = 0 and purple_team_id = team_id) 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 (blue_team_won = 0 and blue_team_id = team_id) or (blue_team_won = 1 and purple_team_id = team_id) group by champion_id) " +
"as champion_losses " +
"on statistics.champion_id = champion_losses.champion_id";
using (var select = Command(query, connection))
{
select.Set("map", map);
select.Set("game_mode", gameMode);
select.Set("summoner_id", summoner.Id);
using (var reader = select.ExecuteReader())
{
List<AggregatedChampionStatistics> output = new List<AggregatedChampionStatistics>();
while (reader.Read())
{
AggregatedChampionStatistics statistics = new AggregatedChampionStatistics(reader);
output.Add(statistics);
}
return output;
}
}
}
List<AggregatedChampionStatistics> LoadAggregatedChampionStatistics(Summoner summoner, MapType map, GameModeType gameMode, DbConnection connection)
{
if (DatabaseProvider.Type == DatabaseType.PostgreSQL)
return LoadAggregatedChampionStatisticsWithCTE(summoner, map, gameMode, connection);
else
return LoadAggregatedChampionStatisticsWithTemporaryView(summoner, map, gameMode, connection);
}
SummonerStatistics GetSummonerStatistics(Summoner summoner, DbConnection connection)
{
Profiler profiler = new Profiler(false, "GetSummonerStatistics", GlobalHandler);
profiler.Start("GetSummonerRatings");
List<SummonerRating> ratings = GetSummonerRatings(summoner, connection);
profiler.Stop();
profiler.Start("GetSummonerRankedStatistics");
List<List<SummonerRankedStatistics>> rankedStatistics = new List<List<SummonerRankedStatistics>>();
for (int i = 0; i < StatisticsService.Seasons; i++)
rankedStatistics.Add(GetSummonerRankedStatistics(summoner, i, connection));
profiler.Stop();
profiler.Start("twistedTreelineStatistics");
List<AggregatedChampionStatistics> twistedTreelineStatistics = LoadAggregatedChampionStatistics(summoner, MapType.TwistedTreeline, GameModeType.Normal, connection);
profiler.Stop();
profiler.Start("summonersRiftStatistics");
List<AggregatedChampionStatistics> summonersRiftStatistics = LoadAggregatedChampionStatistics(summoner, MapType.SummonersRift, GameModeType.Normal, connection);
profiler.Stop();
profiler.Start("dominionStatistics");
List<AggregatedChampionStatistics> dominionStatistics = LoadAggregatedChampionStatistics(summoner, MapType.Dominion, GameModeType.Normal, connection);
profiler.Stop();
profiler.Start("SummonerStatistics");
SummonerStatistics statistics = new SummonerStatistics(ratings, rankedStatistics, twistedTreelineStatistics, summonersRiftStatistics, dominionStatistics);
profiler.Stop();
return statistics;
}
void SummonerMessage(string message, Summoner summoner, params object[] arguments)
{
WriteLine(string.Format("{0} ({1}): {2}", summoner.SummonerName, summoner.AccountId, message), arguments);
}
List<AggregatedChampionStatistics> LoadAggregatedChampionStatisticsWithSP(Summoner summoner, MapType map, GameModeType gameMode, DbConnection connection)
{
const string query = "exec sp_getAggregatedChampionStatistics :summoner_id, :map, :game_mode";
using (var select = Command(query, connection))
{
select.Set("summoner_id", summoner.Id);
select.Set("map", map);
select.Set("game_mode", gameMode);
using (var reader = select.ExecuteReader())
{
var output = new List<AggregatedChampionStatistics>();
while (reader.Read())
{
var statistics = new AggregatedChampionStatistics(reader);
output.Add(statistics);
}
return output;
}
}
}
OperationResult SetSummonerAutomaticUpdates(Summoner summoner, bool updateAutomatically)
{
using (var connection = GetConnection())
{
using (var update = Command("update summoner set update_automatically = :update_automatically where region = :region and account_id = :account_id", connection))
{
update.Set("update_automatically", updateAutomatically);
update.Set("region", summoner.Region);
update.Set("account_id", summoner.AccountId);
int rowsAffected = update.Execute();
if (rowsAffected > 0)
{
summoner.UpdateAutomatically = updateAutomatically;
return OperationResult.Success;
}
else
return OperationResult.NotFound;
}
}
}
void InsertGameResult(Summoner summoner, int gameId, int teamId, PlayerGameStats game, GameResult gameResult, DbConnection connection)
{
using (var insert = GetCommand(gameResult, connection))
{
insert.SetFieldNames(InsertGameResultFields);
insert.Set(gameId);
insert.Set(teamId);
insert.Set(summoner.Id);
insert.Set(game.userServerPing);
insert.Set(game.timeInQueue);
insert.Set(game.premadeSize);
insert.Set(game.experienceEarned);
insert.Set(game.boostXpEarned);
insert.Set(game.ipEarned);
insert.Set(game.boostIpEarned);
insert.Set(game.level);
insert.Set(game.spell1);
insert.Set(game.spell2);
insert.Set(game.championId);
insert.Set(game.skinName);
insert.Set(game.skinIndex);
insert.Set(gameResult.Level);
insert.Set(gameResult.Kills);
insert.Set(gameResult.Deaths);
insert.Set(gameResult.Assists);
insert.Set(gameResult.MinionsKilled);
insert.Set(gameResult.GoldEarned);
insert.Set(gameResult.TotalDamageDealt);
insert.Set(gameResult.PhysicalDamageDealt);
insert.Set(gameResult.MagicalDamageDealt);
insert.Set(gameResult.TotalDamageTaken);
insert.Set(gameResult.PhysicalDamageTaken);
insert.Set(gameResult.MagicalDamageTaken);
insert.Set(gameResult.TotalHealingDone);
insert.Set(gameResult.TimeSpentDead);
insert.Set(gameResult.LargestMultiKill);
insert.Set(gameResult.LargestKillingSpree);
insert.Set(gameResult.LargestCriticalStrike);
//Summoner's Rift/Twisted Treeline
insert.Set(gameResult.NeutralMinionsKilled);
insert.Set(gameResult.TurretsDestroyed);
insert.Set(gameResult.InhibitorsDestroyed);
//Dominion
insert.Set(gameResult.NodesNeutralised);
insert.Set(gameResult.NodeNeutralisationAssists);
insert.Set(gameResult.NodesCaptured);
insert.Set(gameResult.VictoryPoints);
insert.Set(gameResult.Objectives);
insert.Set(gameResult.TotalScore);
insert.Set(gameResult.ObjectiveScore);
insert.Set(gameResult.CombatScore);
insert.Set(gameResult.Rank);
insert.Execute();
}
}
void UpdateSummonerFields(Summoner summoner, DbConnection connection, bool isFullUpdate = false)
{
string[] fields =
{
"summoner_name",
"internal_name",
"summoner_level",
"profile_icon",
"has_been_updated",
"time_updated",
"update_trial",
"revision_date",
};
long currentTime = Time.UnixTime();
if (isFullUpdate)
{
summoner.HasBeenUpdated = true;
summoner.TimeUpdated = (int)currentTime;
}
using (var update = Command("update summoner set {0} where id = :summoner_id", connection, GetUpdateString(fields)))
{
update.Set("summoner_id", summoner.Id);
update.SetFieldNames(fields);
update.Set(summoner.SummonerName);
update.Set(summoner.InternalName);
update.Set(summoner.SummonerLevel);
update.Set(summoner.ProfileIcon);
update.Set(summoner.HasBeenUpdated);
update.Set(currentTime);
update.Set(summoner.LastUpdateTrial);
update.Set(summoner.RevisionDate);
update.Execute();
}
//Inform the statistics service about the update
StatisticsService.AddSummonerToCache(Region, summoner);
}
void UpdateSummonerGames(Summoner summoner, RecentGames recentGameData, DbConnection connection, bool lookUpFurther = true)
{
var recentGames = recentGameData.gameStatistics;
recentGames.Sort(CompareGames);
foreach (var game in recentGames)
UpdateSummonerGame(summoner, game, connection, lookUpFurther);
}
void UpdateSummonerRatings(Summoner summoner, PlayerLifeTimeStats lifeTimeStatistics, DbConnection connection)
{
List<PlayerStatSummary> summaries = lifeTimeStatistics.playerStatSummaries.playerStatSummarySet;
ProcessSummary(MapType.SummonersRift, GameModeType.Normal, "Unranked", summoner, summaries, connection, true);
ProcessSummary(MapType.TwistedTreeline, GameModeType.Premade, "RankedPremade3x3", summoner, summaries, connection);
ProcessSummary(MapType.SummonersRift, GameModeType.Solo, "RankedSolo5x5", summoner, summaries, connection);
ProcessSummary(MapType.SummonersRift, GameModeType.Premade, "RankedPremade5x5", summoner, summaries, connection);
ProcessSummary(MapType.Dominion, GameModeType.Normal, "OdinUnranked", summoner, summaries, connection, true);
}
void InsertGameResult(Summoner summoner, int gameId, int teamId, PlayerGameStats game, GameResult gameResult, DbConnection connection)
{
using (var insert = GetCommand(gameResult, connection))
{
insert.SetFieldNames(InsertGameResultFields);
insert.Set(gameId);
insert.Set(teamId);
insert.Set(summoner.Id);
insert.Set(game.userServerPing);
insert.Set(game.timeInQueue);
insert.Set(game.premadeSize);
insert.Set(game.experienceEarned);
insert.Set(game.boostXpEarned);
insert.Set(game.ipEarned);
insert.Set(game.boostIpEarned);
insert.Set(game.level);
insert.Set(game.spell1);
insert.Set(game.spell2);
insert.Set(game.championId);
insert.Set(game.skinName);
insert.Set(game.skinIndex);
insert.Set(gameResult.Level);
insert.Set(gameResult.Kills);
insert.Set(gameResult.Deaths);
insert.Set(gameResult.Assists);
insert.Set(gameResult.MinionsKilled);
insert.Set(gameResult.GoldEarned);
insert.Set(gameResult.TotalDamageDealt);
insert.Set(gameResult.PhysicalDamageDealt);
insert.Set(gameResult.MagicalDamageDealt);
insert.Set(gameResult.TotalDamageTaken);
insert.Set(gameResult.PhysicalDamageTaken);
insert.Set(gameResult.MagicalDamageTaken);
insert.Set(gameResult.TotalHealingDone);
insert.Set(gameResult.TimeSpentDead);
insert.Set(gameResult.LargestMultiKill);
insert.Set(gameResult.LargestKillingSpree);
insert.Set(gameResult.LargestCriticalStrike);
//Summoner's Rift/Twisted Treeline
insert.Set(gameResult.NeutralMinionsKilled);
insert.Set(gameResult.TurretsDestroyed);
insert.Set(gameResult.InhibitorsDestroyed);
//Dominion
insert.Set(gameResult.NodesNeutralised);
insert.Set(gameResult.NodeNeutralisationAssists);
insert.Set(gameResult.NodesCaptured);
insert.Set(gameResult.VictoryPoints);
insert.Set(gameResult.Objectives);
insert.Set(gameResult.TotalScore);
insert.Set(gameResult.ObjectiveScore);
insert.Set(gameResult.CombatScore);
insert.Set(gameResult.Rank);
insert.Execute();
}
}
List<SummonerRating> GetSummonerRatings(Summoner summoner, DbConnection connection)
{
using (var select = Command("select {0} from summoner_rating where summoner_id = :summoner_id", connection, SummonerRating.GetFields()))
{
select.Set("summoner_id", summoner.Id);
using (var reader = select.ExecuteReader())
{
List<SummonerRating> output = new List<SummonerRating>();
while (reader.Read())
{
SummonerRating rating = new SummonerRating(reader);
output.Add(rating);
}
return output;
}
}
}
void UpdateSummonerRankedStatistics(Summoner summoner, int season, AggregatedStats aggregatedStatistics, DbConnection connection)
{
// Remove the existing ranked stats for this season
using (var championUpdate = Command("delete from summoner_ranked_statistics where summoner_id = :summoner_id and season = :season", connection))
{
championUpdate.Set("summoner_id", summoner.Id);
championUpdate.Set("season", season);
championUpdate.Execute();
}
List<ChampionStatistics> statistics = ChampionStatistics.GetChampionStatistics(aggregatedStatistics);
foreach (var champion in statistics)
{
using (var championUpdate = Command("update summoner_ranked_statistics set wins = :wins, losses = :losses, kills = :kills, deaths = :deaths, assists = :assists, minion_kills = :minion_kills, gold = :gold, turrets_destroyed = :turrets_destroyed, damage_dealt = :damage_dealt, physical_damage_dealt = :physical_damage_dealt, magical_damage_dealt = :magical_damage_dealt, damage_taken = :damage_taken, double_kills = :double_kills, triple_kills = :triple_kills, quadra_kills = :quadra_kills, penta_kills = :penta_kills, time_spent_dead = :time_spent_dead, maximum_kills = :maximum_kills, maximum_deaths = :maximum_deaths where summoner_id = :summoner_id and season = :season and champion_id = :champion_id", connection))
{
SetSummonerRankedStatisticsParameters(championUpdate, summoner, season, champion);
int rowsAffected = championUpdate.Execute();
if (rowsAffected == 0)
{
//The champion entry didn't exist yet so we must create a new entry first
string query = string.Format("insert into summoner_ranked_statistics ({0}) values ({1})", GetGroupString(SummonerRankedStatisticsFields), GetPlaceholderString(SummonerRankedStatisticsFields));
using (var championInsert = Command(query, connection))
{
SetSummonerRankedStatisticsParameters(championInsert, summoner, season, champion);
championInsert.Execute();
}
}
}
}
}
List<AggregatedChampionStatistics> LoadAggregatedChampionStatisticsWithTemporaryView(Summoner summoner, MapType map, GameModeType gameMode, DbConnection connection)
{
Profiler profiler = new Profiler(false, "LoadAggregatedChampionStatisticsWithTemporaryView", GlobalHandler);
string viewName = GetViewName();
try
{
//Create a temporary view with a dynamically generated name to emulate the former CTE
string temporaryString = connection.IsMySQL() ? "" : "temporary ";
string createViewQuery = "create " + temporaryString + "view {0} as select game.map, game.game_mode, game.blue_team_id, game.purple_team_id, game.blue_team_won, player.team_id, player.summoner_id, player.champion_id, player.kills, player.deaths, player.assists, player.gold, player.minion_kills from game, player where game.blue_team_id = player.team_id or game.purple_team_id = player.team_id";
using (var createView = Command(createViewQuery, connection, viewName))
{
createView.Execute();
string commonWhereClause = string.Format("{0}.summoner_id = :summoner_id and {0}.map = :map and {0}.game_mode = :game_mode", viewName);
string selectQuery =
"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 {0}.champion_id, sum({0}.kills) as kills, sum({0}.deaths) as deaths, sum({0}.assists) as assists, sum({0}.gold) as gold, sum({0}.minion_kills) as minion_kills from {0} where {1} group by {0}.champion_id) " +
"as statistics " +
"left outer join " +
"(select champion_id, count(*) as wins from {0} where {1} and ((blue_team_won = 1 and blue_team_id = team_id) or (blue_team_won = 0 and purple_team_id = team_id)) 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 {0} where {1} and ((blue_team_won = 0 and blue_team_id = team_id) or (blue_team_won = 1 and purple_team_id = team_id)) group by champion_id) " +
"as champion_losses " +
"on statistics.champion_id = champion_losses.champion_id";
using (var select = Command(selectQuery, connection, viewName, commonWhereClause))
{
select.Set("map", map);
select.Set("game_mode", gameMode);
select.Set("summoner_id", summoner.Id);
profiler.Start("ExecuteReader");
using (var reader = select.ExecuteReader())
{
profiler.Stop();
profiler.Start("AggregatedChampionStatistics/drop");
List<AggregatedChampionStatistics> output = new List<AggregatedChampionStatistics>();
while (reader.Read())
{
AggregatedChampionStatistics statistics = new AggregatedChampionStatistics(reader);
output.Add(statistics);
}
reader.Close();
using (var dropView = Command("drop view {0}", connection, viewName))
dropView.Execute();
profiler.Stop();
return output;
}
}
}
}
finally
{
ReleaseViewName(viewName);
}
}
void SetSummaryParameters(DatabaseCommand command, MapType map, GameModeType gameMode, Summoner summoner, PlayerStatSummary summary, bool forceNullRating)
{
if (forceNullRating)
{
command.Set("current_rating", DbType.Int32, null);
command.Set("top_rating", DbType.Int32, null);
}
else
{
//Zero rating means that the Elo is below 1200 and is not revealed by the server
if (summary.rating == 0)
{
command.Set("current_rating", DbType.Int32, null);
}
else
{
command.Set("current_rating", summary.rating);
}
command.Set("top_rating", summary.maxRating);
}
command.Set("summoner_id", summoner.Id);
command.Set("map", (int)map);
command.Set("game_mode", (int)gameMode);
command.Set("wins", summary.wins);
command.Set("losses", summary.losses);
command.Set("leaves", summary.leaves);
}
SummonerStatistics GetSummonerStatistics(Summoner summoner, DbConnection connection)
{
List<SummonerRating> ratings = GetSummonerRatings(summoner, connection);
List<List<SummonerRankedStatistics>> rankedStatistics = new List<List<SummonerRankedStatistics>>();
for (int i = 0; i <= ProgramConfiguration.RankedSeason; i++)
rankedStatistics.Add(GetSummonerRankedStatistics(summoner, i, connection));
List<AggregatedChampionStatistics> twistedTreelineStatistics = LoadAggregatedChampionStatistics(summoner, MapType.TwistedTreeline, GameModeType.Normal, connection);
List<AggregatedChampionStatistics> summonersRiftStatistics = LoadAggregatedChampionStatistics(summoner, MapType.SummonersRift, GameModeType.Normal, connection);
List<AggregatedChampionStatistics> dominionStatistics = LoadAggregatedChampionStatistics(summoner, MapType.Dominion, GameModeType.Normal, connection);
SummonerStatistics statistics = new SummonerStatistics(ratings, rankedStatistics, twistedTreelineStatistics, summonersRiftStatistics, dominionStatistics);
return statistics;
}
void UpdateSummonerGame(Summoner summoner, PlayerGameStats game, DbConnection connection)
{
//Don't store tutorial games
if (game.gameMode == "TUTORIAL" || game.gameType == "TUTORIAL_GAME")
{
return;
}
const int blueId = 100;
//const int purpleId = 200;
bool isBlueTeam = game.teamId == blueId;
int gameId;
int summonerTeamId;
GameResult gameResult = new GameResult(game);
//At first we must determine if the game is already in the database
using (var check = Command("select id, blue_team_id, purple_team_id from game where game.game_id = :game_id", connection))
{
check.Set("game_id", game.gameId);
using (var reader = check.ExecuteReader())
{
if (reader.Read())
{
//The game is already in the database
gameId = reader.Integer();
int blueTeamId = reader.Integer();
int purpleTeamId = reader.Integer();
reader.Close();
if (isBlueTeam)
{
summonerTeamId = blueTeamId;
}
else
{
summonerTeamId = purpleTeamId;
}
//Check if the game result for this player has already been stored
using (var gameCheck = Command("select count(*) from player where (team_id = :blue_team_id or team_id = :purple_team_id) and summoner_id = :summoner_id", connection))
{
gameCheck.Set("blue_team_id", blueTeamId);
gameCheck.Set("purple_team_id", purpleTeamId);
gameCheck.Set("summoner_id", summoner.Id);
long count = (long)gameCheck.ExecuteScalar();
if (count > 0)
{
//The result of this game for this player has already been stored in the database, there is no work to be done
return;
}
}
//The game is already stored in the database but the results of this player were previously unknown
//This means that this player must be removed from the list of unknown players for this game
using (var delete = Command("delete from unknown_player where team_id = :team_id and summoner_id = :summoner_id", connection))
{
delete.Set("team_id", summonerTeamId);
delete.Set("summoner_id", summoner.SummonerId);
delete.Execute();
}
}
else
{
reader.Close();
//The game is not in the database yet
//Need to create the team entries first
using (var newTeam = Command("insert into team default values", connection))
{
newTeam.Execute();
int blueTeamId = GetInsertId("team", connection);
newTeam.Execute();
int purpleTeamId = GetInsertId("team", connection);
summonerTeamId = isBlueTeam ? blueTeamId : purpleTeamId;
MapType map;
GameModeType gameMode;
switch (game.gameMapId)
{
//Autumn
case 1:
//No idea what 2 means
case 2:
//Not sure either, encountered this in some games from 4 months ago on an inactive account
case 3:
//Winter
case 6:
map = MapType.SummonersRift;
break;
case 4:
map = MapType.TwistedTreeline;
break;
case 7:
map = MapType.ProvingGrounds;
break;
case 8:
map = MapType.Dominion;
break;
// Apparently this is from the Twisted Treeline remake
case 10:
map = MapType.TwistedTreeline;
break;
default:
throw new Exception(string.Format("Unknown game map ID in the match history of {0}: {1}", summoner.SummonerName, game.gameMapId));
}
if (game.gameType == "PRACTICE_GAME" || game.gameType == "CUSTOM_GAME")
{
gameMode = GameModeType.Custom;
}
else
{
switch (game.queueType)
{
case "RANKED_TEAM_3x3":
case "RANKED_TEAM_5x5":
case "RANKED_PREMADE_3x3":
case "RANKED_PREMADE_5x5":
gameMode = GameModeType.Premade;
break;
case "NORMAL":
case "NORMAL_3x3":
case "ODIN_UNRANKED":
gameMode = GameModeType.Normal;
break;
case "RANKED_SOLO_5x5":
gameMode = GameModeType.Solo;
break;
case "BOT":
gameMode = GameModeType.Bot;
break;
case "BOT_3x3":
gameMode = GameModeType.Bot;
break;
default:
throw new Exception(string.Format("Unknown queue type in the match history of {0}: {1}", summoner.SummonerName, game.queueType));
}
}
using (var newGame = Command("insert into game ({0}) values ({1})", connection, GetGroupString(NewGameFields), GetPlaceholderString(NewGameFields)))
{
newGame.SetFieldNames(NewGameFields);
newGame.Set(game.gameId);
newGame.Set(map);
newGame.Set(gameMode);
newGame.Set(game.createDate.ToUnixTime());
newGame.Set(blueTeamId);
newGame.Set(purpleTeamId);
newGame.Set(gameResult.Win == isBlueTeam);
newGame.Execute();
gameId = GetInsertId("game", connection);
//We need to create a list of unknown players for this game so they can get updated in future if necessary
//Otherwise it is unclear who participated in this game
//Retrieving their stats at this point is too expensive and hence undesirable
foreach (var player in game.fellowPlayers)
{
using (var missingPlayer = Command("insert into unknown_player (team_id, champion_id, summoner_id) values (:team_id, :champion_id, :summoner_id)", connection))
{
missingPlayer.Set("team_id", player.teamId == blueId ? blueTeamId : purpleTeamId);
missingPlayer.Set("champion_id", player.championId);
missingPlayer.Set("summoner_id", player.summonerId);
missingPlayer.Execute();
}
}
}
}
}
}
}
InsertGameResult(summoner, gameId, summonerTeamId, game, gameResult, connection);
}
void UpdateSummonerGame(Summoner summoner, PlayerGameStats game, DbConnection connection)
{
//Don't store tutorial games
if (game.gameMode == "TUTORIAL" || game.gameType == "TUTORIAL_GAME")
return;
const int blueId = 100;
//const int purpleId = 200;
bool isBlueTeam = game.teamId == blueId;
int gameId;
int summonerTeamId;
GameResult gameResult = new GameResult(game);
//At first we must determine if the game is already in the database
using (var check = Command("select id, blue_team_id, purple_team_id from game where game.game_id = :game_id", connection))
{
check.Set("game_id", game.gameId);
using (var reader = check.ExecuteReader())
{
if (reader.Read())
{
//The game is already in the database
gameId = reader.Integer();
int blueTeamId = reader.Integer();
int purpleTeamId = reader.Integer();
reader.Close();
if (isBlueTeam)
summonerTeamId = blueTeamId;
else
summonerTeamId = purpleTeamId;
//Check if the game result for this player has already been stored
using (var gameCheck = Command("select count(*) from player where (team_id = :blue_team_id or team_id = :purple_team_id) and summoner_id = :summoner_id", connection))
{
gameCheck.Set("blue_team_id", blueTeamId);
gameCheck.Set("purple_team_id", purpleTeamId);
gameCheck.Set("summoner_id", summoner.Id);
long count = (long)gameCheck.ExecuteScalar();
if (count > 0)
{
//The result of this game for this player has already been stored in the database, there is no work to be done
return;
}
}
//The game is already stored in the database but the results of this player were previously unknown
//This means that this player must be removed from the list of unknown players for this game
using (var delete = Command("delete from unknown_player where team_id = :team_id and summoner_id = :summoner_id", connection))
{
delete.Set("team_id", summonerTeamId);
delete.Set("summoner_id", summoner.SummonerId);
delete.Execute();
}
}
else
{
reader.Close();
//The game is not in the database yet
//Need to create the team entries first
using (var newTeam = Command("insert into team default values", connection))
{
newTeam.Execute();
int blueTeamId = GetInsertId("team", connection);
newTeam.Execute();
int purpleTeamId = GetInsertId("team", connection);
summonerTeamId = isBlueTeam ? blueTeamId : purpleTeamId;
MapType map;
GameModeType gameMode;
switch (game.gameMapId)
{
//Autumn
case 1:
//No idea what 2 means
case 2:
//Not sure either, encountered this in some games from 4 months ago on an inactive account
case 3:
//Winter
case 6:
map = MapType.SummonersRift;
break;
case 4:
map = MapType.TwistedTreeline;
break;
case 7:
map = MapType.ProvingGrounds;
break;
case 8:
map = MapType.Dominion;
break;
// Apparently this is from the Twisted Treeline remake
case 10:
map = MapType.TwistedTreeline;
break;
case 12:
map = MapType.HowlingAbyss;
break;
default:
throw new Exception(string.Format("Unknown game map ID in the match history of {0}: {1}", summoner.SummonerName, game.gameMapId));
}
if (game.gameType == "PRACTICE_GAME" || game.gameType == "CUSTOM_GAME")
gameMode = GameModeType.Custom;
else
{
switch (game.queueType)
{
case "RANKED_TEAM_3x3":
case "RANKED_TEAM_5x5":
case "RANKED_PREMADE_3x3":
case "RANKED_PREMADE_5x5":
gameMode = GameModeType.Premade;
break;
case "NORMAL":
case "NORMAL_3x3":
case "ODIN_UNRANKED":
case "ARAM_UNRANKED_5x5":
gameMode = GameModeType.Normal;
break;
case "RANKED_SOLO_5x5":
gameMode = GameModeType.Solo;
break;
case "BOT":
gameMode = GameModeType.Bot;
break;
case "BOT_3x3":
gameMode = GameModeType.Bot;
break;
default:
throw new Exception(string.Format("Unknown queue type in the match history of {0}: {1}", summoner.SummonerName, game.queueType));
}
}
using (var newGame = Command("insert into game ({0}) values ({1})", connection, GetGroupString(NewGameFields), GetPlaceholderString(NewGameFields)))
{
newGame.SetFieldNames(NewGameFields);
newGame.Set(game.gameId);
newGame.Set(map);
newGame.Set(gameMode);
newGame.Set(game.createDate.ToUnixTime());
newGame.Set(blueTeamId);
newGame.Set(purpleTeamId);
newGame.Set(gameResult.Win == isBlueTeam);
newGame.Execute();
gameId = GetInsertId("game", connection);
//We need to create a list of unknown players for this game so they can get updated in future if necessary
//Otherwise it is unclear who participated in this game
//Retrieving their stats at this point is too expensive and hence undesirable
foreach (var player in game.fellowPlayers)
{
using (var missingPlayer = Command("insert into unknown_player (team_id, champion_id, summoner_id) values (:team_id, :champion_id, :summoner_id)", connection))
{
missingPlayer.Set("team_id", player.teamId == blueId ? blueTeamId : purpleTeamId);
missingPlayer.Set("champion_id", player.championId);
missingPlayer.Set("summoner_id", player.summonerId);
missingPlayer.Execute();
}
}
}
}
}
}
}
InsertGameResult(summoner, gameId, summonerTeamId, game, gameResult, connection);
}
List <AggregatedChampionStatistics> LoadAggregatedChampionStatisticsWithTemporaryView(Summoner summoner, MapType map, GameModeType gameMode, DbConnection connection)
{
Profiler profiler = new Profiler(false, "LoadAggregatedChampionStatisticsWithTemporaryView", GlobalHandler);
string viewName = GetViewName();
try
{
//Create a temporary view with a dynamically generated name to emulate the former CTE
string temporaryString = connection.IsMySQL() ? "" : "temporary ";
string createViewQuery = "create " + temporaryString + "view {0} as select game.map, game.game_mode, game.blue_team_id, game.purple_team_id, game.blue_team_won, player.team_id, player.summoner_id, player.champion_id, player.kills, player.deaths, player.assists, player.gold, player.minion_kills from game, player where game.blue_team_id = player.team_id or game.purple_team_id = player.team_id";
using (var createView = Command(createViewQuery, connection, viewName))
{
createView.Execute();
string commonWhereClause = string.Format("{0}.summoner_id = :summoner_id and {0}.map = :map and {0}.game_mode = :game_mode", viewName);
string selectQuery =
"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 {0}.champion_id, sum({0}.kills) as kills, sum({0}.deaths) as deaths, sum({0}.assists) as assists, sum({0}.gold) as gold, sum({0}.minion_kills) as minion_kills from {0} where {1} group by {0}.champion_id) " +
"as statistics " +
"left outer join " +
"(select champion_id, count(*) as wins from {0} where {1} and ((blue_team_won = 1 and blue_team_id = team_id) or (blue_team_won = 0 and purple_team_id = team_id)) 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 {0} where {1} and ((blue_team_won = 0 and blue_team_id = team_id) or (blue_team_won = 1 and purple_team_id = team_id)) group by champion_id) " +
"as champion_losses " +
"on statistics.champion_id = champion_losses.champion_id";
using (var select = Command(selectQuery, connection, viewName, commonWhereClause))
{
select.Set("map", map);
select.Set("game_mode", gameMode);
select.Set("summoner_id", summoner.Id);
profiler.Start("ExecuteReader");
using (var reader = select.ExecuteReader())
{
profiler.Stop();
profiler.Start("AggregatedChampionStatistics/drop");
List <AggregatedChampionStatistics> output = new List <AggregatedChampionStatistics>();
while (reader.Read())
{
AggregatedChampionStatistics statistics = new AggregatedChampionStatistics(reader);
output.Add(statistics);
}
reader.Close();
using (var dropView = Command("drop view {0}", connection, viewName))
dropView.Execute();
profiler.Stop();
return(output);
}
}
}
}
finally
{
ReleaseViewName(viewName);
}
}
void InitialiseSummonerCache()
{
SummonerCache = new Dictionary<RegionType, Dictionary<int, Summoner>>();
foreach (RegionType regionType in Enum.GetValues(typeof(RegionType)))
SummonerCache[regionType] = new Dictionary<int, Summoner>();
using (var connection = Provider.GetConnection())
{
using (var select = new DatabaseCommand("select {0} from summoner", connection, null, Summoner.GetFields()))
{
using (var reader = select.ExecuteReader())
{
while (reader.Read())
{
Summoner summoner = new Summoner(reader);
SummonerCache[summoner.Region][summoner.AccountId] = summoner;
}
}
}
}
}
void SummonerMessage(string message, Summoner summoner, params object[] arguments)
{
WriteLine(string.Format("{0} ({1}): {2}", summoner.SummonerName, summoner.AccountId, message), arguments);
}
void InitialiseSummonerCache()
{
SummonerCache = new Dictionary <RegionType, Dictionary <int, Summoner> >();
foreach (RegionType regionType in Enum.GetValues(typeof(RegionType)))
{
SummonerCache[regionType] = new Dictionary <int, Summoner>();
}
using (var connection = Provider.GetConnection())
{
using (var select = new DatabaseCommand("select {0} from summoner", connection, null, Summoner.GetFields()))
{
using (var reader = select.ExecuteReader())
{
while (reader.Read())
{
Summoner summoner = new Summoner(reader);
SummonerCache[summoner.Region][summoner.AccountId] = summoner;
}
}
}
}
}
List<ExtendedPlayer> GetSummonerGames(Summoner summoner, DbConnection connection)
{
bool useItemArray = (connection.IsMySQL() || connection.IsMsSQL()) == false;
List<ExtendedPlayer> output = new List<ExtendedPlayer>();
using (var select = Command("select {0} from game, player where game.id = player.game_id and player.summoner_id = :summoner_id order by game.time desc", connection, ExtendedPlayer.GetFields(useItemArray)))
{
select.Set("summoner_id", summoner.Id);
using (var reader = select.ExecuteReader())
{
while (reader.Read())
{
ExtendedPlayer player = new ExtendedPlayer(reader, useItemArray);
output.Add(player);
}
}
}
foreach(var player in output)
{
using (var select = Command("select champion_id, team_id from player where game_id = ( select TOP 1 id from game where game_id = :game_id )", connection))
{
select.Set("game_id", player.InternalGameId);
using (var reader = select.ExecuteReader())
{
int cnt = 0;
while (reader.Read())
{
long teamChampions = ((long)reader.Integer() << 32) | (reader.Integer());
player.TeamChampions[cnt++] = teamChampions;
}
}
}
}
return output;
}