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());
}
