void UpdateSummoner(SummonerDescription summoner, bool isNewSummoner)
{
AccountLock accountLock = Master.GetAccountLock(summoner.AccountId);
Profiler profiler = new Profiler(true, string.Format("{0} {1} {2}", RegionProfile.Abbreviation, WorkerLogin.Username, summoner.Name));
lock (accountLock)
{
SummonerMessage("Updating", summoner);
profiler.Start("RetrievePlayerStatsByAccountID");
PlayerLifeTimeStats lifeTimeStatistics = RPC.RetrievePlayerStatsByAccountID(summoner.AccountId, "CURRENT");
if (lifeTimeStatistics == null)
{
SummonerMessage("Unable to retrieve lifetime statistics", summoner);
return;
}
profiler.Stop();
profiler.Start("GetAggregatedStats");
AggregatedStats aggregatedStatistics = RPC.GetAggregatedStats(summoner.AccountId, "CLASSIC", "CURRENT");
if (aggregatedStatistics == null)
{
SummonerMessage("Unable to retrieve aggregated statistics", summoner);
return;
}
profiler.Stop();
profiler.Start("GetRecentGames");
RecentGames recentGameData = RPC.GetRecentGames(summoner.AccountId);
if (recentGameData == null)
{
SummonerMessage("Unable to retrieve recent games", summoner);
return;
}
profiler.Stop();
profiler.Start("SQL");
UpdateSummonerRatings(summoner, lifeTimeStatistics);
UpdateSummonerRankedStatistics(summoner, aggregatedStatistics);
UpdateSummonerGames(summoner, recentGameData);
if (!isNewSummoner)
{
//This means that the main summoner entry must be updated
UpdateSummonerLastModifiedTimestamp(summoner);
}
profiler.Stop();
Master.ReleaseAccountLock(summoner.AccountId, accountLock);
}
}
void ProcessSummary(string mapEnum, string gameModeEnum, string target, SummonerDescription summoner, List<PlayerStatSummary> summaries, bool forceNullRating = false)
{
foreach (var summary in summaries)
{
if (summary.playerStatSummaryType != target)
continue;
SQLCommand update = Command("update summoner_rating set wins = :wins, losses = :losses, leaves = :leaves, current_rating = :current_rating, top_rating = :top_rating where summoner_id = :summoner_id and rating_map = cast(:rating_map as map_type) and game_mode = cast(:game_mode as game_mode_type)");
if (forceNullRating)
{
update.Set("current_rating", null as int?);
update.Set("top_rating", null as int?);
}
else
{
update.Set("current_rating", summary.rating);
update.Set("top_rating", summary.maxRating);
}
update.Set("summoner_id", summoner.Id);
update.SetEnum("rating_map", mapEnum);
update.SetEnum("game_mode", gameModeEnum);
update.Set("wins", summary.wins);
update.Set("losses", summary.losses);
update.Set("leaves", summary.leaves);
int rowsAffected = update.Execute();
if (rowsAffected == 0)
{
//We're dealing with a new summoner rating entry, insert it
SQLCommand insert = Command("insert into summoner_rating (summoner_id, rating_map, game_mode, wins, losses, leaves, current_rating, top_rating) values (:summoner_id, cast(:rating_map as map_type), cast(:game_mode as game_mode_type), :wins, :losses, :leaves, :current_rating, :top_rating)");
insert.CopyParameters(update);
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 UpdateSummonerGame(SummonerDescription summoner, PlayerGameStats game)
{
//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
DatabaseCommand check = Command("select id, blue_team_id, purple_team_id, purple_team_won from game_result where game_result.game_id = :game_id");
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 purpleTeamId = reader.Integer();
int blueTeamId = reader.Integer();
bool PurpleTeamWon = reader.Boolean();
if (isBlueTeam)
summonerTeamId = blueTeamId;
else
summonerTeamId = purpleTeamId;
//Check if the game result for this player has already been stored
DatabaseCommand gameCheck = Command("select count(*) from player where (team_id = :blue_team_id or team_id = :purple_team_id) and summoner_id = :summoner_id");
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
DatabaseCommand delete = Command("delete from missing_team_player where team_id = :team_id and account_id = :account_id");
delete.Set("team_id", summonerTeamId);
delete.Set("account_id", summoner.AccountId);
delete.Execute();
}
else
{
//The game is not in the database yet
//Need to create the team entries first
DatabaseCommand newTeam = Command("insert into team default values");
newTeam.Execute();
int blueTeamId = GetInsertId("team");
newTeam.Execute();
int purpleTeamId = GetInsertId("team");
summonerTeamId = isBlueTeam ? blueTeamId : purpleTeamId;
int team2Id = GetInsertId("team");
string mapEnum;
string gameModeEnum;
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:
mapEnum = "summoners_rift";
break;
case 4:
mapEnum = "twisted_treeline";
break;
case 8:
mapEnum = "dominion";
break;
default:
throw new Exception(string.Format("Unknown game map ID in the match history of {0}: {1}", summoner.Name, game.gameMapId));
}
if (game.gameType == "PRACTICE_GAME")
gameModeEnum = "custom";
else
{
switch (game.queueType)
{
case "RANKED_TEAM_3x3":
case "RANKED_TEAM_5x5":
case "RANKED_PREMADE_3x3":
case "RANKED_PREMADE_5x5":
gameModeEnum = "premade";
break;
case "NORMAL":
gameModeEnum = "normal";
break;
case "ODIN_UNRANKED":
gameModeEnum = "normal";
break;
case "RANKED_SOLO_5x5":
gameModeEnum = "solo";
break;
case "BOT":
gameModeEnum = "bot";
break;
default:
throw new Exception(string.Format("Unknown queue type in the match history of {0}: {1}", summoner.Name, game.queueType));
}
}
List<string> fields = new List<string>()
{
"game_id",
"map",
"game_mode",
"game_time",
"blue_team_id",
"purple_team_id",
"blue_team_won",
};
DatabaseCommand newGame = Command("insert into game_result ({0}) values ({1})", GetGroupString(fields), GetPlaceholderString(fields));
newGame.SetFieldNames(fields);
newGame.Set(game.gameId);
newGame.Set(mapEnum);
newGame.Set(gameModeEnum);
newGame.Set(GetTimestamp(game.createDate));
newGame.Set(blueTeamId);
newGame.Set(purpleTeamId);
newGame.Set(gameResult.Win && isBlueTeam);
newGame.Execute();
gameId = GetInsertId("game_result");
//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)
{
DatabaseCommand missingPlayer = Command("insert into missing_team_player (team_id, champion_id, account_id) values (:team_id, :champion_id, :account_id)");
missingPlayer.Set("team_id", player.teamId == blueId ? blueTeamId : purpleTeamId);
missingPlayer.Set("champion_id", player.championId);
//It's called summonerId but it's really the account ID (I think)
missingPlayer.Set("account_id", player.summonerId);
missingPlayer.Execute();
}
}
}
InsertGameResult(summoner, gameId, summonerTeamId, game, gameResult);
}
void SummonerMessage(string message, SummonerDescription summoner, params object[] arguments)
{
WriteLine(string.Format("{0} ({1}): {2}", summoner.Name, summoner.AccountId, message), arguments);
}
void UpdateSummonerRankedStatistics(SummonerDescription summoner, AggregatedStats aggregatedStatistics)
{
List<string> fields = new List<string>()
{
"summoner_id",
"champion_id",
"wins",
"losses",
"kills",
"deaths",
"assists",
"minion_kills",
"gold",
"turrets_destroyed",
"damage_dealt",
"physical_damage_dealt",
"magical_damage_dealt",
"damage_taken",
"double_kills",
"triple_kills",
"quadra_kills",
"penta_kills",
"time_spent_dead",
"maximum_kills",
"maximum_deaths",
};
List<ChampionStatistics> statistics = ChampionStatistics.GetChampionStatistics(aggregatedStatistics);
foreach (var champion in statistics)
{
SQLCommand 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 champion_id = :champion_id");
championUpdate.SetFieldNames(fields);
championUpdate.Set(summoner.Id);
championUpdate.Set(champion.ChampionId);
championUpdate.Set(champion.Wins);
championUpdate.Set(champion.Losses);
championUpdate.Set(champion.Kills);
championUpdate.Set(champion.Deaths);
championUpdate.Set(champion.Assists);
championUpdate.Set(champion.MinionKills);
championUpdate.Set(champion.Gold);
championUpdate.Set(champion.TurretsDestroyed);
championUpdate.Set(champion.DamageDealt);
championUpdate.Set(champion.PhysicalDamageDealt);
championUpdate.Set(champion.MagicalDamageDealt);
championUpdate.Set(champion.DamageTaken);
championUpdate.Set(champion.DoubleKills);
championUpdate.Set(champion.TripleKills);
championUpdate.Set(champion.QuadraKills);
championUpdate.Set(champion.PentaKills);
championUpdate.Set(champion.TimeSpentDead);
championUpdate.Set(champion.MaximumKills);
championUpdate.Set(champion.MaximumDeaths);
int rowsAffected = championUpdate.Execute();
if (rowsAffected == 0)
{
//The champion entry didn't exist yet so we must create a new entry first
string queryFields = GetGroupString(fields);
string queryValues = GetPlaceholderString(fields);
SQLCommand championInsert = Command(string.Format("insert into summoner_ranked_statistics ({0}) values ({1})", queryFields, queryValues));
championInsert.CopyParameters(championUpdate);
championInsert.Execute();
}
}
}
void InsertGameResult(SummonerDescription summoner, int gameId, int teamId, PlayerGameStats game, GameResult gameResult)
{
List<string> fields = new List<string>()
{
"game_id",
"team_id",
"summoner_id",
"won",
"ping",
"time_spent_in_queue",
"premade_size",
"k_coefficient",
"probability_of_winning",
"rating",
"rating_change",
"adjusted_rating",
"team_rating",
"experience_earned",
"boosted_experience_earned",
"ip_earned",
"boosted_ip_earned",
"summoner_level",
"summoner_spell1",
"summoner_spell2",
"champion_id",
"skin_name",
"skin_index",
"champion_level",
//Items are stored as an SQL array
"items",
"kills",
"deaths",
"assists",
"minion_kills",
"gold",
"damage_dealt",
"physical_damage_dealt",
"magical_damage_dealt",
"damage_taken",
"physical_damage_taken",
"magical_damage_taken",
"total_healing_done",
"time_spent_dead",
"largest_multikill",
"largest_killing_spree",
"largest_critical_strike",
//Summoner's Rift/Twisted Treeline
"neutral_minions_killed",
"turrets_destroyed",
"inhibitors_destroyed",
//Dominion
"nodes_neutralised",
"node_neutralisation_assists",
"nodes_captured",
"victory_points",
"objectives",
"total_score",
"objective_score",
"combat_score",
"rank",
};
string queryFields = GetGroupString(fields);
string queryValues = GetPlaceholderString(fields);
SQLCommand insert = Command("insert into team_player ({0}) values ({1})", queryFields, queryValues);
insert.SetFieldNames(fields);
insert.Set(gameId);
insert.Set(teamId);
insert.Set(summoner.Id);
insert.Set(gameResult.Win);
insert.Set(game.userServerPing);
insert.Set(game.timeInQueue);
insert.Set(game.premadeSize);
insert.Set(game.KCoefficient);
insert.Set(game.predictedWinPct);
insert.Set(game.rating);
insert.Set(game.eloChange);
insert.Set(game.adjustedRating);
insert.Set(game.teamRating);
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);
//Items require special treatment
insert.Set(gameResult.Items);
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 UpdateSummonerRatings(SummonerDescription summoner, PlayerLifeTimeStats lifeTimeStatistics)
{
List<PlayerStatSummary> summaries = lifeTimeStatistics.playerStatSummaries.playerStatSummarySet;
ProcessSummary("summoners_rift", "normal", "Unranked", summoner, summaries, true);
ProcessSummary("twisted_treeline", "premade", "RankedPremade3x3", summoner, summaries);
ProcessSummary("summoners_rift", "solo", "RankedSolo5x5", summoner, summaries);
ProcessSummary("summoners_rift", "premade", "RankedPremade5x5", summoner, summaries);
ProcessSummary("dominion", "normal", "OdinUnranked", summoner, summaries);
}
void UpdateSummonerLastModifiedTimestamp(SummonerDescription summoner)
{
SQLCommand timeUpdate = Command(string.Format("update summoner set time_updated = {0} where id = :id", CurrentTimestamp()));
timeUpdate.Set("id", summoner.Id);
timeUpdate.Execute();
}
void UpdateSummonerGames(SummonerDescription summoner, RecentGames recentGameData)
{
var recentGames = recentGameData.gameStatistics;
recentGames.Sort(CompareGames);
foreach (var game in recentGames)
UpdateSummonerGame(summoner, game);
string query =
"with rating as " +
"( " +
"with source as " +
"(select game_result.game_time, team_player.rating, team_player.rating_change from game_result, team_player where game_result.id = team_player.game_id and game_result.result_map = cast('summoners_rift' as map_type) and game_result.game_mode = cast('normal' as game_mode_type) and team_player.summoner_id = :summoner_id) " +
"select current_rating.current_rating, top_rating.top_rating from " +
"(select (rating + rating_change) as current_rating from source order by game_time desc limit 1) " +
"as current_rating, " +
"(select max(rating + rating_change) as top_rating from source) " +
"as top_rating " +
") " +
"update summoner_rating set current_rating = (select current_rating from rating), top_rating = (select top_rating from rating) where summoner_id = :summoner_id and rating_map = cast('summoners_rift' as map_type) and game_mode = cast('normal' as game_mode_type);",
myquery =
@"UPDATE summoner_rating
SET current_rating = (SELECT current_rating FROM rating_current WHERE summoner_id = ?summoner_id),
top_rating = (SELECT top_rating FROM rating_top WHERE summoner_id = ?summoner_id)
WHERE summoner_id = ?summoner_id
AND rating_map = 'summoners_rift' AND game_mode = 'normal';";
SQLCommand update = Database is MySql.Data.MySqlClient.MySqlConnection ? Command(myquery) : Command(query);
update.Set("summoner_id", summoner.Id);
update.Execute();
}
void UpdateSummonerGame(SummonerDescription summoner, PlayerGameStats game)
{
//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;
//The update requires a transaction as multiple accounts might be querying data for the same game simultaneously
NpgsqlTransaction transaction = Database.BeginTransaction();
int gameId;
int summonerTeamId;
GameResult gameResult = new GameResult(game);
//At first we must determine if the game is already in the database
SQLCommand check = Command("select game_result.id, game_result.team1_id, game_result.team2_id, team.is_blue_team from game_result, team where game_result.game_id = :game_id and game_result.team1_id = team.id");
check.Set("game_id", game.gameId);
using (var reader = check.ExecuteReader())
{
if (reader.Read())
{
//The game is already in the database
gameId = (int)reader[0];
int team1Id = (int)reader[1];
int team2Id = (int)reader[2];
bool team1IsBlue = (bool)reader[3];
if (isBlueTeam && team1IsBlue)
summonerTeamId = team1Id;
else
summonerTeamId = team2Id;
//Check if the game result for this player has already been stored
SQLCommand gameCheck = Command("select count(*) from team_player where (team_id = :team1_id or team_id = :team2_id) and summoner_id = :summoner_id");
gameCheck.Set("team1_id", team1Id);
gameCheck.Set("team2_id", team2Id);
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
transaction.Rollback();
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
//I'm too lazy to figure out what team the player belongs to right now so let's just perform two deletions for now, one of which will fail
int[] teamIds = { team1Id, team2Id };
foreach (int teamId in teamIds)
{
SQLCommand delete = Command("delete from missing_team_player where team_id = :team_id and account_id = :account_id");
delete.Set("team_id", teamId);
delete.Set("account_id", summoner.AccountId);
delete.Execute();
}
}
else
{
//The game is not in the database yet
//Need to create the team entries first
SQLCommand newTeam = Command("insert into team (is_blue_team) values (:is_blue_team)");
newTeam.Set("is_blue_team", NpgsqlDbType.Boolean, isBlueTeam);
newTeam.Execute();
int team1Id = GetInsertId("team");
summonerTeamId = team1Id;
newTeam.Set("is_blue_team", NpgsqlDbType.Boolean, !isBlueTeam);
newTeam.Execute();
int team2Id = GetInsertId("team");
Dictionary<int, int> teamIdDictionary = new Dictionary<int, int>()
{
{game.teamId, team1Id},
{isBlueTeam ? purpleId : blueId, team2Id},
};
List<string> fields = new List<string>()
{
"game_id",
"result_map",
"game_mode",
"game_time",
"team1_won",
"team1_id",
"team2_id",
};
string mapEnum;
string gameModeEnum;
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:
mapEnum = "summoners_rift";
break;
case 4:
mapEnum = "twisted_treeline";
break;
case 8:
mapEnum = "dominion";
break;
default:
throw new Exception(string.Format("Unknown game map ID in the match history of {0}: {1}", summoner.Name, game.gameMapId));
}
if (game.gameType == "PRACTICE_GAME")
gameModeEnum = "custom";
else
{
switch (game.queueType)
{
case "RANKED_TEAM_3x3":
case "RANKED_TEAM_5x5":
case "RANKED_PREMADE_3x3":
case "RANKED_PREMADE_5x5":
gameModeEnum = "premade";
break;
case "NORMAL":
gameModeEnum = "normal";
break;
case "ODIN_UNRANKED":
gameModeEnum = "normal";
break;
case "RANKED_SOLO_5x5":
gameModeEnum = "solo";
break;
case "BOT":
gameModeEnum = "bot";
break;
default:
{
transaction.Rollback();
throw new Exception(string.Format("Unknown queue type in the match history of {0}: {1}", summoner.Name, game.queueType));
}
}
}
string queryFields = GetGroupString(fields);
string queryValues = ":game_id, cast(:result_map as map_type), cast(:game_mode as game_mode_type), to_timestamp(:game_time), :team1_won, :team1_id, :team2_id";
SQLCommand newGame = Command("insert into game_result ({0}) values ({1})", queryFields, queryValues);
newGame.SetFieldNames(fields);
newGame.Set(game.gameId);
newGame.Set(mapEnum);
newGame.Set(gameModeEnum);
newGame.Set(GetTimestamp(game.createDate));
newGame.Set(gameResult.Win);
newGame.Set(team1Id);
newGame.Set(team2Id);
newGame.Execute();
gameId = GetInsertId("game_result");
//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)
{
SQLCommand missingPlayer = Command("insert into missing_team_player (team_id, champion_id, account_id) values (:team_id, :champion_id, :account_id)");
missingPlayer.Set("team_id", teamIdDictionary[player.teamId]);
missingPlayer.Set("champion_id", player.championId);
//It's called summonerId but it's really the account ID (I think)
missingPlayer.Set("account_id", player.summonerId);
missingPlayer.Execute();
}
}
}
InsertGameResult(summoner, gameId, summonerTeamId, game, gameResult);
transaction.Commit();
}
