public string MakeRecentMatchesReport(int count)
{
sqlCommand.CommandText = $"SELECT * FROM matches ORDER BY timestamp DESC LIMIT {count}";
this.PrintSqlQuery();
var recentMatchesReport = new Dictionary <int, JObject>();
using (SQLiteDataReader reader = sqlCommand.ExecuteReader())
{
while (reader.Read())
{
var matchInfo = new JObject
{
{ "server", (string)reader["endpoint"] },
{ "timestamp", Extras.UnixTimeToDateTime((double)(long)reader["timestamp"]).ToUniversalTime() },
{
"results", new JObject
{
{ "map", (string)reader["map"] },
{ "gameMode", (string)reader["gamemode"] },
{ "fragLimit", (int)(long)reader["frag_limit"] },
{ "timeLimit", (int)(long)reader["time_limit"] },
{ "timeElapsed", (double)reader["time_elapsed"] },
{ "scoreboard", new JArray() }
}
}
};
recentMatchesReport.Add((int)(long)reader["id"], matchInfo);
}
}
var recentMatchesJson = new JArray();
foreach (var match in recentMatchesReport)
{
// TODO: Fix converting to json from scoreboardItem
match.Value["results"]["scoreboard"] =
new JArray(
this.GetScoreboard(match.Key)
.Select(a => JsonConvert.DeserializeObject(JsonConvert.SerializeObject(a))));
recentMatchesJson.Add(match.Value);
}
return(recentMatchesJson.ToString());
}
public string MakePlayerStats(string name)
{
var queries = new Dictionary <string, string>()
{
{ "totalMatchesPlayed", "SELECT count(*) FROM scoreboard WHERE name = \"{0}\" COLLATE NOCASE" },
{
"totalMatchesWon",
"SELECT count(*) FROM (SELECT match_id, name, max(frags) FROM scoreboard GROUP BY match_id) WHERE name = \"{0}\" COLLATE NOCASE"
},
{
"favouriteServer",
"SELECT endpoint FROM (SELECT endpoint, count(*) AS cnt FROM matches WHERE id IN (SELECT match_id FROM scoreboard WHERE name = \"{0}\" COLLATE NOCASE) GROUP BY endpoint ORDER BY cnt DESC LIMIT 1)"
},
{
"uniqueServers",
"SELECT count(*) FROM (SELECT endpoint, count(*) AS cnt FROM matches WHERE id IN (SELECT match_id FROM scoreboard WHERE name = \"{0}\" COLLATE NOCASE) GROUP BY endpoint)"
},
{
"favouriteGameMode",
"SELECT gamemode FROM (SELECT gamemode, count(*) AS cnt FROM matches WHERE id IN (SELECT match_id FROM scoreboard WHERE name = \"{0}\" COLLATE NOCASE) GROUP BY gamemode ORDER BY cnt DESC LIMIT 1)"
},
{
"averageScoreboardPercent",
"SELECT avg(sp) FROM (SELECT match_id, bp * 100.0 / (tp - 1) AS sp FROM (SELECT match_id, count(*) AS bp, tp FROM scoreboard JOIN ( SELECT match_id AS mi, frags AS target_frags FROM scoreboard WHERE name = \"{0}\" ) ON scoreboard.match_id = mi JOIN ( SELECT match_id AS mid, count(*) AS tp FROM scoreboard GROUP BY match_id ) ON scoreboard.match_id = mid WHERE match_id IN (SELECT match_id FROM scoreboard WHERE name = \"{0}\" ) AND frags < target_frags GROUP BY match_id))"
},
{
"maximumMatchesPerDay",
"SELECT max(cnt) FROM ( SELECT timestamp/86400 AS day, count(*) AS cnt FROM matches WHERE id IN (SELECT match_id FROM scoreboard WHERE name = \"{0}\" COLLATE NOCASE) GROUP BY day)"
},
{
"averageMatchesPerDay",
"SELECT avg(cnt) FROM ( SELECT timestamp/86400 AS day, count(*) AS cnt FROM matches WHERE id IN (SELECT match_id FROM scoreboard WHERE name = \"{0}\" COLLATE NOCASE) GROUP BY day)"
},
{
"lastMatchPlayed",
"SELECT max(timestamp) FROM matches WHERE id IN (SELECT match_id FROM scoreboard WHERE name = \"{0}\" COLLATE NOCASE)"
},
{
"killToDeathRatio",
"SELECT sum(kills) * 1.0 / sum(deaths) FROM scoreboard WHERE name = \"{0}\" COLLATE NOCASE"
}
};
var stats = new JObject
{
{ "totalMatchesPlayed", this.GetOneInt(queries["totalMatchesPlayed"], name) },
{ "totalMatchesWon", this.GetOneInt(queries["totalMatchesWon"], name) },
{ "favouriteServer", this.GetStringArray(queries["favouriteServer"], name).ToArray()[0] },
{ "uniqueServers", this.GetOneInt(queries["uniqueServers"], name) },
{ "favouriteGameMode", this.GetStringArray(queries["favouriteGameMode"], name).ToArray()[0] },
{ "averageScoreboardPercent", this.GetOneDouble(queries["averageScoreboardPercent"], name) },
{ "maximumMatchesPerDay", this.GetOneInt(queries["maximumMatchesPerDay"], name) },
{ "averageMatchesPerDay", this.GetOneDouble(queries["averageMatchesPerDay"], name) },
{
"lastMatchPlayed",
Extras.UnixTimeToDateTime(this.GetOneDouble(queries["lastMatchPlayed"], name)).ToUniversalTime()
},
{ "killToDeathRatio", this.GetOneDouble(queries["killToDeathRatio"], name) }
};
return(stats.ToString());
}