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()); }
private void Worker() { WaitHandle[] wait = { _ready, _stop }; while (0 == WaitHandle.WaitAny(wait)) { HttpListenerContext context; lock (_queue) { if (_queue.Count > 0) { context = _queue.Dequeue(); } else { _ready.Reset(); continue; } } try { //ProcessRequest(context); this.HandleContext(context); } catch (ArgumentException) { Extras.WriteColoredLine("Incorrect request", ConsoleColor.Magenta); _statsApi.HandleIncorrect(context); } catch (ThreadAbortException) { return; } catch (Exception error) { _statsApi.HandleIncorrect(context); Extras.WriteColoredLine( $"Source: {error.Source}\nException: {error.Message}\nStack Trace: {error.StackTrace}", ConsoleColor.Red); } } }
public MatchInfo GetServerMatch(string endpoint, DateTime timestamp) { double unixTimestamp = Extras.DateTimeToUnixTime(timestamp); sqlCommand.CommandText = $"SELECT * FROM matches WHERE endpoint = \"{endpoint}\" AND timestamp = {unixTimestamp};"; this.PrintSqlQuery(); MatchInfo matchInfo; int matchId = 0; using (SQLiteDataReader reader = sqlCommand.ExecuteReader()) { if (reader.Read()) { matchId = (int)(long)reader["id"]; matchInfo = new MatchInfo() { 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"] }; } else { return(null); } } matchInfo.scoreboard = this.GetScoreboard(matchId).ToArray(); return(matchInfo); }
//public event Action<HttpListenerContext> ProcessRequest; private void HandleContext(HttpListenerContext listenerContext) { // TODO: Make routing with regexp var request = listenerContext.Request; var parts = request.RawUrl.Split('/'); Extras.WriteColoredLine(String.Format("{1} {0}", request.RawUrl, request.HttpMethod), ConsoleColor.DarkGreen); if (parts[1] == "servers") { if (parts[2] == "info") { // /servers/info GET if (request.HttpMethod == HttpMethod.Get.Method) { _statsApi.GetServersInfo(listenerContext); } // If method is not get else { _statsApi.HandleIncorrect(listenerContext); } } else { switch (parts[3]) { case "info": // /servers/<endpoint>/info PUT, GET if (request.HttpMethod == HttpMethod.Get.Method) { _statsApi.GetServerInfo(listenerContext); } else if (request.HttpMethod == HttpMethod.Put.Method) { _statsApi.PutServerInfo(listenerContext); } else { _statsApi.HandleIncorrect(listenerContext); } break; case "matches": // /servers/<endpoint>/matches/<timestamp> PUT, GET if (request.HttpMethod == HttpMethod.Get.Method) { _statsApi.GetServerMatch(listenerContext); } else if (request.HttpMethod == HttpMethod.Put.Method) { _statsApi.PutServerMatch(listenerContext); } else { _statsApi.HandleIncorrect(listenerContext); } break; case "stats": // /servers/<endpoint>/stats GET _statsApi.GetServerStats(listenerContext); break; default: _statsApi.HandleIncorrect(listenerContext); break; } } } else if (parts[1] == "reports" && request.HttpMethod == HttpMethod.Get.Method) { switch (parts[2]) { case "recent-matches": _statsApi.GetRecentMatchesReport(listenerContext); break; case "best-players": _statsApi.GetBestPlayersReport(listenerContext); break; case "popular-servers": _statsApi.GetPopularServersReport(listenerContext); break; default: _statsApi.HandleIncorrect(listenerContext); break; } } else if (parts[1] == "players" && parts[3] == "stats" && request.HttpMethod == HttpMethod.Get.Method) { _statsApi.GetPlayerStats(listenerContext); } else { _statsApi.HandleIncorrect(listenerContext); } }
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()); }