public void addGameStats(int rGameId, int summonerId, PlayerGameStats pStats, GameResult stats)
{
MySqlCommand cmd = new MySqlCommand();
cmd.Connection = conn;
cmd.CommandText = "INSERT INTO gameStats SET " +
"gameId = \"" + getGameId(rGameId) + "\", " +
"playerId = \"" + getPlayerId(summonerId) + "\", " +
"championId = \"" + pStats.championId + "\", " +
"skinId = \"" + pStats.skinIndex + "\", " +
"spell1Id = \"" + pStats.spell1 + "\", " +
"spell2Id = \"" + pStats.spell2 + "\", " +
"serverPing = \"" + pStats.userServerPing + "\", " +
"kills = \"" + stats.Kills + "\", " +
"deaths = \"" + stats.Deaths + "\", " +
"assists = \"" + stats.Assists + "\", " +
"level = \"" + stats.Level + "\", " +
"minionsKilled = \"" + stats.MinionsKilled + "\", " +
"neutralMinionsKilled = \"" + stats.NeutralMinionsKilled + "\", " +
"goldEarned = \"" + stats.GoldEarned + "\", " +
"magicDamageDealt = \"" + stats.MagicalDamageDealt + "\", " +
"physicalDamageDealt = \"" + stats.PhysicalDamageDealt + "\", " +
"totalDamageDealt = \"" + stats.TotalDamageDealt + "\", " +
"magicDamageTaken = \"" + stats.MagicalDamageTaken + "\", " +
"physicalDamageTaken = \"" + stats.PhysicalDamageTaken + "\", " +
"totalDamageTaken = \"" + stats.TotalDamageTaken + "\", " +
"totalHealingDone = \"" + stats.TotalHealingDone + "\", " +
"largestMultiKill = \"" + stats.LargestMultiKill + "\", " +
"largestKillingSpree = \"" + stats.LargestKillingSpree + "\", " +
"timeSpentDead = \"" + stats.TimeSpentDead + "\", " +
"turretsDestroyed = \"" + stats.TurretsDestroyed + "\", " +
"inhibitorsDestroyed = \"" + stats.InhibitorsDestroyed + "\", " +
"item0 = \"" + stats.Items[0] + "\", " +
"item1 = \"" + stats.Items[1] + "\", " +
"item2 = \"" + stats.Items[2] + "\", " +
"item3 = \"" + stats.Items[3] + "\", " +
"item4 = \"" + stats.Items[4] + "\", " +
"item5 = \"" + stats.Items[5] + "\"";
cmd.ExecuteNonQuery();
}
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 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);
}
void AnalyseRecentGames(List<string> arguments)
{
string summonerName = GetNameFromArguments(arguments);
PublicSummoner publicSummoner = new PublicSummoner();
List<PlayerGameStats> recentGames = new List<PlayerGameStats>();
bool foundSummoner = GetRecentGames(summonerName, ref publicSummoner, ref recentGames);
if (!foundSummoner)
{
NoSuchSummoner();
return;
}
foreach (var stats in recentGames)
{
GameResult result = new GameResult(stats);
Console.Write("[{0}] [{1}] [{2}] ", stats.gameId, stats.createDate, result.Win ? "W" : "L");
if (stats.ranked)
Console.Write("Ranked ");
if (stats.gameType == "PRACTICE_GAME")
{
Console.Write("Custom ");
switch (stats.gameMode)
{
case "CLASSIC":
Console.Write("Summoner's Rift");
break;
case "ODIN":
Console.Write("Dominion");
break;
default:
Console.Write(stats.gameMode);
break;
}
}
else
{
switch (stats.queueType)
{
case "RANKED_TEAM_3x3":
Output.Write("Twisted Treeline");
break;
case "NORMAL":
case "RANKED_SOLO_5x5":
Console.Write("Summoner's Rift");
break;
case "RANKED_TEAM_5x5":
Console.Write("Summoner's Rift (team)");
break;
case "ODIN_UNRANKED":
Console.Write("Dominion");
break;
case "BOT":
Console.Write("Co-op vs. AI");
break;
default:
Console.Write(stats.queueType);
break;
}
}
Console.WriteLine(", {0}, {1}/{2}/{3}", GetChampionName(stats.championId), result.Kills, result.Deaths, result.Assists);
List<string> units = new List<string>();
if (stats.adjustedRating != 0)
units.Add(string.Format("Rating: {0} ({1})", stats.rating + stats.eloChange, SignPrefix(stats.eloChange)));
if (stats.adjustedRating != 0)
units.Add(string.Format("adjusted {0}", stats.adjustedRating));
if (stats.teamRating != 0)
units.Add(string.Format("team {0} ({1})", stats.teamRating, SignPrefix(stats.teamRating - stats.rating)));
PrintUnits(units);
if (stats.predictedWinPct != 0.0)
units.Add(string.Format("Predicted winning percentage {0}", Percentage(stats.predictedWinPct)));
if (stats.premadeSize > 1)
units.Add(string.Format("Queued with {0}", stats.premadeSize));
if (stats.leaver)
units.Add("Left the game");
if (stats.afk)
units.Add("AFK");
units.Add(string.Format("{0} ms ping", stats.userServerPing));
units.Add(string.Format("{0} s spent in queue", stats.timeInQueue));
PrintUnits(units);
}
}
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 AnalyseEnvironmentalRating(List<string> arguments, bool ranked, string season)
{
if (arguments.Count == 0)
return;
string summonerName = GetSummonerName(arguments[0]);
var excludedNames = new List<string>();
for (int i = 1; i < arguments.Count; i++)
excludedNames.Add(GetSummonerName(arguments[i]));
PublicSummoner publicSummoner = new PublicSummoner();
List<PlayerGameStats> recentGames = new List<PlayerGameStats>();
bool foundSummoner = GetRecentGames(summonerName, ref publicSummoner, ref recentGames);
if (!foundSummoner)
{
NoSuchSummoner();
return;
}
var knownSummoners = new HashSet<string>();
var currentRatings = new List<int>();
var topRatings = new List<int>();
int gameCount = 0;
foreach (var stats in recentGames)
{
GameResult result = new GameResult(stats);
if (
(stats.gameType == "PRACTICE_GAME") ||
(!ranked && stats.queueType != "NORMAL") ||
(ranked && stats.queueType != "RANKED_SOLO_5x5")
)
continue;
var ids = new List<int>();
foreach (var fellowPlayer in stats.fellowPlayers)
ids.Add(fellowPlayer.summonerId);
var names = RPC.GetSummonerNames(ids);
bool isValidGame = true;
foreach (var name in excludedNames)
{
if (names.IndexOf(name) >= 0)
{
isValidGame = false;
break;
}
}
if (!isValidGame)
continue;
gameCount++;
foreach (var name in names)
{
if (knownSummoners.Contains(name))
continue;
knownSummoners.Add(name);
PublicSummoner summoner = RPC.GetSummonerByName(name);
if (summoner == null)
{
Console.WriteLine("Unable to load summoner {0}", name);
return;
}
PlayerLifeTimeStats lifeTimeStatistics = RPC.RetrievePlayerStatsByAccountID(summoner.acctId, season);
if (lifeTimeStatistics == null)
{
Console.WriteLine("Unable to retrieve lifetime statistics for summoner {0}", name);
return;
}
List<PlayerStatSummary> summaries = lifeTimeStatistics.playerStatSummaries.playerStatSummarySet;
const string target = "RankedSolo5x5";
foreach (var summary in summaries)
{
if (summary.playerStatSummaryType == target)
{
int games = summary.wins + summary.losses;
if (games == 0)
break;
Console.Write("{0}: ", name);
if (summary.maxRating >= 2200)
Console.ForegroundColor = ConsoleColor.White;
else if (summary.maxRating >= 1850)
Console.ForegroundColor = ConsoleColor.DarkCyan;
else if (summary.maxRating >= 1500)
Console.ForegroundColor = ConsoleColor.DarkYellow;
else if (summary.maxRating < 1150 && summary.maxRating != 0)
Console.ForegroundColor = ConsoleColor.DarkGray;
Console.Write("{0} (top {1}), ", summary.rating, summary.maxRating);
Console.ResetColor();
Console.Write("{0} W - {1} L ({2})", summary.wins, summary.losses, SignPrefix(summary.wins - summary.losses));
if (summary.leaves > 0)
Console.Write(", left {0} {1}", summary.leaves, (summary.leaves > 1 ? "games" : "game"));
Console.WriteLine("");
currentRatings.Add(summary.rating);
topRatings.Add(summary.maxRating);
break;
}
}
}
}
currentRatings.Sort();
topRatings.Sort();
PrintRatings("Current ratings", currentRatings);
PrintRatings("Top ratings", topRatings);
int playerCount = 9 * gameCount;
int rankedPlayers = topRatings.Count;
float rankedRatio = (float)rankedPlayers / playerCount;
Console.WriteLine("Ranked players: {0}/{1} ({2:F1}%)", rankedPlayers, playerCount, rankedRatio * 100);
}
DatabaseCommand GetCommand(GameResult gameResult, DbConnection connection)
{
string queryFields = GetGroupString(InsertGameResultFields);
string queryValues = GetPlaceholderString(InsertGameResultFields);
if (connection.IsMySQL())
{
//MySQL doesn't support arrays so we employ separate fields in this case
string itemFieldString = string.Join(", ", Player.GetItemFields());
string itemValueString = string.Join(", ", gameResult.Items);
return Command("insert into player ({0}, {1}) values ({2}, {3})", connection, queryFields, itemFieldString, queryValues, itemValueString);
}
else
{
//This is the code for PostgreSQL and SQLite
//Items are an array of integers and require special treatment
string itemString = string.Format("'{{{0}}}'", string.Join(", ", gameResult.Items));
return Command("insert into player ({0}, items) values ({1}, {2})", connection, queryFields, queryValues, itemString);
}
}
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 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();
}
void InsertGameResult(Summoner summoner, int gameId, int teamId, PlayerGameStats game, GameResult gameResult, DbConnection connection)
{
string queryFields = GetGroupString(InsertGameResultFields);
string queryValues = GetPlaceholderString(InsertGameResultFields);
using (var insert = Command("insert into player ({0}) values ({1})", connection, queryFields, queryValues))
{
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);
//Items are an array of integers and require special treatment
string itemString = string.Format("'{{{0}}}'", string.Join(", ", gameResult.Items));
insert.Set(itemString);
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();
}
}
public void finishEndGameStats()
{
PlayerGameStats pStats;
PlayerLifeTimeStats lifeStats;
GameResult gRes;
string summName;
ConsoleOut("\tGameUpdate: FIRST Time = " + (getUnixTimestamp() - startTime).ToString());
db.updateEndGame(reportingSummoner.lastGameId, reportingSummoner.accountId);
ConsoleOut("\tGameUpdate: Updating end game done, now doing fellow players");
for (int i = 0; i < endSummonerStats.Count; i++)
{
pStats = endSummonerStats.ElementAt(i).gameStats;
gRes = new GameResult(pStats);
summName = findSummNameInPublicSumm(endSummonerStats.ElementAt(i).accountId);
lifeStats = findLifeStats(endSummonerStats.ElementAt(i).accountId);
db.addGameStats(endSummonerStats.ElementAt(i).accountId, pStats, gRes, lifeStats, summName);
}
db.removeLiveGame(reportingSummoner.lastGameId);
ConsoleOut("\tGameUpdate: Finished Updating End of Game Stats ----- Time = " + (getUnixTimestamp() - startTime).ToString());
}
