private void ShowRankings()
{
// Check cache
if (!base.CacheFileExpired($"{Pid}_rankings", 30))
{
base.SendCachedResponse($"{Pid}_rankings");
return;
}
// Load our Model
PlayerRankingsModel Model = new PlayerRankingsModel(Client);
Model.Player = Rows[0];
Model.SearchBarValue = Pid.ToString();
string value;
#region Player Rankings
// Global Score
RankingPosition Score = new RankingPosition() { RankingType = "score" };
string query = "SELECT COUNT(id) FROM player WHERE score > @P0";
Score.Value = Model.FormatInteger(Int32.Parse(Model.Player["score"].ToString()));
Score.Position = Database.ExecuteScalar<int>(query, Model.Player["score"]) + 1;
Score.CtrPosition = Database.ExecuteScalar<int>(query + " AND country=@P1", Model.Player["score"], Model.Player["country"]) + 1;
Score.PageNumber = GetPage(Score.Position);
Score.CtrPageNumber = GetPage(Score.CtrPosition);
Model.Rankings.Add(Score);
// Score Per Min
Score = new RankingPosition() { RankingType = "spm" };
query = "SELECT score / (time / 60.0) AS spm FROM player WHERE id=@P0";
try
{
value = Database.ExecuteScalar<string>(query, Pid);
Score.Value = Math.Round(Decimal.Parse(value, CultureInfo.InvariantCulture), 4);
}
catch
{
value = "0.000";
Score.Value = 0.0000m;
}
query = "SELECT COUNT(id), ROUND(score / (time / 60.0), 8) AS spm FROM player WHERE spm > " + value;
Score.Position = Database.ExecuteScalar<int>(query) + 1;
Score.CtrPosition = Database.ExecuteScalar<int>(query + " AND country=@P0", Model.Player["country"]) + 1;
Score.PageNumber = GetPage(Score.Position);
Score.CtrPageNumber = GetPage(Score.CtrPosition);
Model.Rankings.Add(Score);
// Win-Loss Ratio
Score = new RankingPosition() { RankingType = "wlr" };
query = "SELECT (wins * 1.0 / losses) AS wlr FROM player WHERE id=@P0";
try
{
value = Database.ExecuteScalar<string>(query, Pid);
Score.Value = Math.Round(Decimal.Parse(value, CultureInfo.InvariantCulture), 4);
}
catch
{
value = "0.000";
Score.Value = 0.0000m;
}
query = "SELECT COUNT(id), ROUND(wins * 1.0 / losses, 8) AS wlr FROM player WHERE wlr > " + value;
Score.Position = Database.ExecuteScalar<int>(query) + 1;
Score.CtrPosition = Database.ExecuteScalar<int>(query + " AND country=@P0", Model.Player["country"]) + 1;
Score.PageNumber = GetPage(Score.Position);
Score.CtrPageNumber = GetPage(Score.CtrPosition);
Model.Rankings.Add(Score);
// K/D Ratio
Score = new RankingPosition() { RankingType = "kdr" };
query = "SELECT (kills * 1.0 / deaths) AS value FROM player WHERE id=@P0";
try
{
value = Database.ExecuteScalar<string>(query, Pid);
Score.Value = Math.Round(Decimal.Parse(value, CultureInfo.InvariantCulture), 4);
}
catch
{
value = "0.000";
Score.Value = 0.0000m;
}
query = "SELECT COUNT(id), ROUND(kills * 1.0 / deaths, 8) AS value FROM player WHERE value > " + value;
Score.Position = Database.ExecuteScalar<int>(query) + 1;
Score.CtrPosition = Database.ExecuteScalar<int>(query + " AND country=@P0", Model.Player["country"]) + 1;
Score.PageNumber = GetPage(Score.Position);
Score.CtrPageNumber = GetPage(Score.CtrPosition);
Model.Rankings.Add(Score);
// Knife Ratio
Score = new RankingPosition() { RankingType = "knife_kdr" };
query = "SELECT (knifekills * 1.0 / knifedeaths) AS value FROM weapons WHERE id=@P0";
try
{
value = Database.ExecuteScalar<string>(query, Pid);
Score.Value = Math.Round(Decimal.Parse(value, CultureInfo.InvariantCulture), 4);
}
catch
{
value = "0.000";
Score.Value = 0.0000m;
}
query = "SELECT COUNT(id), ROUND(knifekills * 1.0 / knifedeaths, 8) AS value FROM weapons WHERE value > " + value;
Score.Position = Database.ExecuteScalar<int>(query) + 1;
query = "SELECT COUNT(w.id), ROUND(w.knifekills * 1.0 / w.knifedeaths, 8) AS value "
+ "FROM weapons AS w "
+ "JOIN player AS p "
+ "WHERE p.id = w.id AND p.country=@P0 AND value > " + Score.Value;
Score.CtrPosition = Database.ExecuteScalar<int>(query, Model.Player["country"]) + 1;
Score.PageNumber = GetPage(Score.Position);
Score.CtrPageNumber = GetPage(Score.CtrPosition);
Model.Rankings.Add(Score);
// Sniper Accuracy
Score = new RankingPosition() { RankingType = "sniper_acc" };
query = "SELECT (hit4 * 1.0 / fired4) AS value FROM weapons WHERE id=@P0";
try
{
value = Database.ExecuteScalar<string>(query, Pid);
Score.Value = Math.Round(Decimal.Parse(value, CultureInfo.InvariantCulture), 4);
}
catch
{
value = "0.000";
Score.Value = 0.0000m;
}
query = "SELECT COUNT(id), ROUND(hit4 * 1.0 / fired4, 8) AS value FROM weapons WHERE value > " + value;
Score.Position = Database.ExecuteScalar<int>(query) + 1;
query = "SELECT COUNT(w.id), ROUND(w.hit4 * 1.0 / w.fired4, 8) AS value "
+ "FROM weapons AS w "
+ "JOIN player AS p "
+ "WHERE p.id = w.id AND p.country=@P0 AND value > " + Score.Value;
Score.CtrPosition = Database.ExecuteScalar<int>(query, Model.Player["country"]) + 1;
Score.PageNumber = GetPage(Score.Position);
Score.CtrPageNumber = GetPage(Score.CtrPosition);
Model.Rankings.Add(Score);
// Hours Per Day
// Timeframe: ((Last Online timstamp - Joined Timestamp) / 1 day (86400 seconds)) Gets the timespan of days played
// Divide Timeframe by: hours played (seconds played (`time` column) / 1 hr (3600 seconds))
Score = new RankingPosition() { RankingType = "hpd" };
query = "SELECT (time / 3600.0) / ((lastonline - joined) / 86400.0) AS value FROM player WHERE id=@P0";
try
{
value = Database.ExecuteScalar<string>(query, Pid);
Score.Value = Math.Round(Decimal.Parse(value, CultureInfo.InvariantCulture), 4);
}
catch
{
value = "0.000";
Score.Value = 0.0000m;
}
query = "SELECT COUNT(id), ROUND((time / 3600.0) / ((lastonline - joined) / 86400.0), 8) AS value FROM player WHERE value > " + value;
Rows = Database.Query(query);
Score.Position = Database.ExecuteScalar<int>(query) + 1;
Score.CtrPosition = Database.ExecuteScalar<int>(query + " AND country=@P0", Model.Player["country"]) + 1;
Score.PageNumber = GetPage(Score.Position);
Score.CtrPageNumber = GetPage(Score.CtrPosition);
Model.Rankings.Add(Score);
// Command Score
Score = new RankingPosition() { RankingType = "command" };
query = "SELECT COUNT(id) FROM player WHERE cmdscore > @P0";
Score.Value = Model.FormatInteger(Int32.Parse(Model.Player["cmdscore"].ToString()));
Score.Position = Database.ExecuteScalar<int>(query, Model.Player["cmdscore"]) + 1;
Score.CtrPosition = Database.ExecuteScalar<int>(query + " AND country=@P1", Model.Player["cmdscore"], Model.Player["country"]) + 1;
Score.PageNumber = GetPage(Score.Position);
Score.CtrPageNumber = GetPage(Score.CtrPosition);
Model.Rankings.Add(Score);
// Relative Command Score
Score = new RankingPosition() { RankingType = "rcmds" };
query = "SELECT COALESCE((cmdscore * 1.0 / cmdtime), 0.0) AS value FROM player WHERE id=@P0";
try
{
value = Database.ExecuteScalar<string>(query, Pid);
Score.Value = Math.Round(Decimal.Parse(value, CultureInfo.InvariantCulture), 4);
}
catch
{
value = "0.000";
Score.Value = 0.0000m;
}
query = "SELECT COUNT(id), COALESCE((cmdscore * 1.0 / cmdtime), 0.0) AS value FROM player WHERE value > " + value;
Score.Position = Database.ExecuteScalar<int>(query) + 1;
Score.CtrPosition = Database.ExecuteScalar<int>(query + " AND country=@P0", Model.Player["country"]) + 1;
Score.PageNumber = GetPage(Score.Position);
Score.CtrPageNumber = GetPage(Score.CtrPosition);
Model.Rankings.Add(Score);
#endregion
#region Player Stats
// Define our play time
TimeSpan PlayerTime = TimeSpan.FromSeconds(Int32.Parse(Model.Player["time"].ToString()));
int Kills = Int32.Parse(Model.Player["kills"].ToString());
int Deaths = Int32.Parse(Model.Player["deaths"].ToString());
int Rounds = Int32.Parse(Model.Player["wins"].ToString()) + Int32.Parse(Model.Player["losses"].ToString());
decimal Result;
// Player Stats
foreach (string Query in StatsQueries)
{
PlayerRankingStats Stats = new PlayerRankingStats();
Result = Database.ExecuteScalar<decimal>(Query, Pid);
Stats.PerMinute = (PlayerTime.TotalMinutes > 0) ? Math.Round(Result / (decimal)PlayerTime.TotalMinutes, 4) : Result;
Stats.PerDay = (PlayerTime.TotalDays > 0) ? Math.Round(Result / (decimal)PlayerTime.TotalDays, 4) : Result;
Stats.PerHour = (PlayerTime.TotalHours > 0) ? Math.Round(Result / (decimal)PlayerTime.TotalHours, 4) : Result;
Stats.PerRound = (Rounds > 0) ? Math.Round(Result / Rounds, 4) : Result;
Stats.PerKill = (Kills > 0) ? Math.Round(Result / Kills, 4) : Result;
Stats.PerDeath = (Deaths > 0) ? Math.Round(Result / Deaths, 4) : Result;
Model.Stats.Add(Stats);
}
#endregion
// Send the response
base.SendTemplateResponse("player_rankings", typeof(PlayerRankingsModel), Model, Pid + "_rankings");
}
private void ShowRankings()
{
// Check cache
if (!base.CacheFileExpired($"{Pid}_rankings", 30))
{
base.SendCachedResponse($"{Pid}_rankings");
return;
}
// Load our Model
PlayerRankingsModel Model = new PlayerRankingsModel(Client);
Model.Player = Rows[0];
Model.SearchBarValue = Pid.ToString();
string value;
#region Player Rankings
// Global Score
RankingPosition Score = new RankingPosition()
{
RankingType = "score"
};
string query = "SELECT COUNT(id) FROM player WHERE score > @P0";
Score.Value = Model.FormatInteger(Int32.Parse(Model.Player["score"].ToString()));
Score.Position = Database.ExecuteScalar <int>(query, Model.Player["score"]) + 1;
Score.CtrPosition = Database.ExecuteScalar <int>(query + " AND country=@P1", Model.Player["score"], Model.Player["country"]) + 1;
Score.PageNumber = GetPage(Score.Position);
Score.CtrPageNumber = GetPage(Score.CtrPosition);
Model.Rankings.Add(Score);
// Score Per Min
Score = new RankingPosition()
{
RankingType = "spm"
};
query = "SELECT score / (time / 60.0) AS spm FROM player WHERE id=@P0";
try
{
value = Database.ExecuteScalar <string>(query, Pid);
Score.Value = Math.Round(Decimal.Parse(value, CultureInfo.InvariantCulture), 4);
}
catch
{
value = "0.000";
Score.Value = 0.0000m;
}
query = "SELECT COUNT(id), ROUND(score / (time / 60.0), 8) AS spm FROM player WHERE spm > " + value;
Score.Position = Database.ExecuteScalar <int>(query) + 1;
Score.CtrPosition = Database.ExecuteScalar <int>(query + " AND country=@P0", Model.Player["country"]) + 1;
Score.PageNumber = GetPage(Score.Position);
Score.CtrPageNumber = GetPage(Score.CtrPosition);
Model.Rankings.Add(Score);
// Win-Loss Ratio
Score = new RankingPosition()
{
RankingType = "wlr"
};
query = "SELECT (wins * 1.0 / losses) AS wlr FROM player WHERE id=@P0";
try
{
value = Database.ExecuteScalar <string>(query, Pid);
Score.Value = Math.Round(Decimal.Parse(value, CultureInfo.InvariantCulture), 4);
}
catch
{
value = "0.000";
Score.Value = 0.0000m;
}
query = "SELECT COUNT(id), ROUND(wins * 1.0 / losses, 8) AS wlr FROM player WHERE wlr > " + value;
Score.Position = Database.ExecuteScalar <int>(query) + 1;
Score.CtrPosition = Database.ExecuteScalar <int>(query + " AND country=@P0", Model.Player["country"]) + 1;
Score.PageNumber = GetPage(Score.Position);
Score.CtrPageNumber = GetPage(Score.CtrPosition);
Model.Rankings.Add(Score);
// K/D Ratio
Score = new RankingPosition()
{
RankingType = "kdr"
};
query = "SELECT (kills * 1.0 / deaths) AS value FROM player WHERE id=@P0";
try
{
value = Database.ExecuteScalar <string>(query, Pid);
Score.Value = Math.Round(Decimal.Parse(value, CultureInfo.InvariantCulture), 4);
}
catch
{
value = "0.000";
Score.Value = 0.0000m;
}
query = "SELECT COUNT(id), ROUND(kills * 1.0 / deaths, 8) AS value FROM player WHERE value > " + value;
Score.Position = Database.ExecuteScalar <int>(query) + 1;
Score.CtrPosition = Database.ExecuteScalar <int>(query + " AND country=@P0", Model.Player["country"]) + 1;
Score.PageNumber = GetPage(Score.Position);
Score.CtrPageNumber = GetPage(Score.CtrPosition);
Model.Rankings.Add(Score);
// Knife Ratio
Score = new RankingPosition()
{
RankingType = "knife_kdr"
};
query = "SELECT (knifekills * 1.0 / knifedeaths) AS value FROM weapons WHERE id=@P0";
try
{
value = Database.ExecuteScalar <string>(query, Pid);
Score.Value = Math.Round(Decimal.Parse(value, CultureInfo.InvariantCulture), 4);
}
catch
{
value = "0.000";
Score.Value = 0.0000m;
}
query = "SELECT COUNT(id), ROUND(knifekills * 1.0 / knifedeaths, 8) AS value FROM weapons WHERE value > " + value;
Score.Position = Database.ExecuteScalar <int>(query) + 1;
query = "SELECT COUNT(w.id), ROUND(w.knifekills * 1.0 / w.knifedeaths, 8) AS value "
+ "FROM weapons AS w "
+ "JOIN player AS p "
+ "WHERE p.id = w.id AND p.country=@P0 AND value > " + Score.Value;
Score.CtrPosition = Database.ExecuteScalar <int>(query, Model.Player["country"]) + 1;
Score.PageNumber = GetPage(Score.Position);
Score.CtrPageNumber = GetPage(Score.CtrPosition);
Model.Rankings.Add(Score);
// Sniper Accuracy
Score = new RankingPosition()
{
RankingType = "sniper_acc"
};
query = "SELECT (hit4 * 1.0 / fired4) AS value FROM weapons WHERE id=@P0";
try
{
value = Database.ExecuteScalar <string>(query, Pid);
Score.Value = Math.Round(Decimal.Parse(value, CultureInfo.InvariantCulture), 4);
}
catch
{
value = "0.000";
Score.Value = 0.0000m;
}
query = "SELECT COUNT(id), ROUND(hit4 * 1.0 / fired4, 8) AS value FROM weapons WHERE value > " + value;
Score.Position = Database.ExecuteScalar <int>(query) + 1;
query = "SELECT COUNT(w.id), ROUND(w.hit4 * 1.0 / w.fired4, 8) AS value "
+ "FROM weapons AS w "
+ "JOIN player AS p "
+ "WHERE p.id = w.id AND p.country=@P0 AND value > " + Score.Value;
Score.CtrPosition = Database.ExecuteScalar <int>(query, Model.Player["country"]) + 1;
Score.PageNumber = GetPage(Score.Position);
Score.CtrPageNumber = GetPage(Score.CtrPosition);
Model.Rankings.Add(Score);
// Hours Per Day
// Timeframe: ((Last Online timstamp - Joined Timestamp) / 1 day (86400 seconds)) Gets the timespan of days played
// Divide Timeframe by: hours played (seconds played (`time` column) / 1 hr (3600 seconds))
Score = new RankingPosition()
{
RankingType = "hpd"
};
query = "SELECT (time / 3600.0) / ((lastonline - joined) / 86400.0) AS value FROM player WHERE id=@P0";
try
{
value = Database.ExecuteScalar <string>(query, Pid);
Score.Value = Math.Round(Decimal.Parse(value, CultureInfo.InvariantCulture), 4);
}
catch
{
value = "0.000";
Score.Value = 0.0000m;
}
query = "SELECT COUNT(id), ROUND((time / 3600.0) / ((lastonline - joined) / 86400.0), 8) AS value FROM player WHERE value > " + value;
Rows = Database.Query(query);
Score.Position = Database.ExecuteScalar <int>(query) + 1;
Score.CtrPosition = Database.ExecuteScalar <int>(query + " AND country=@P0", Model.Player["country"]) + 1;
Score.PageNumber = GetPage(Score.Position);
Score.CtrPageNumber = GetPage(Score.CtrPosition);
Model.Rankings.Add(Score);
// Command Score
Score = new RankingPosition()
{
RankingType = "command"
};
query = "SELECT COUNT(id) FROM player WHERE cmdscore > @P0";
Score.Value = Model.FormatInteger(Int32.Parse(Model.Player["cmdscore"].ToString()));
Score.Position = Database.ExecuteScalar <int>(query, Model.Player["cmdscore"]) + 1;
Score.CtrPosition = Database.ExecuteScalar <int>(query + " AND country=@P1", Model.Player["cmdscore"], Model.Player["country"]) + 1;
Score.PageNumber = GetPage(Score.Position);
Score.CtrPageNumber = GetPage(Score.CtrPosition);
Model.Rankings.Add(Score);
// Relative Command Score
Score = new RankingPosition()
{
RankingType = "rcmds"
};
query = "SELECT COALESCE((cmdscore * 1.0 / cmdtime), 0.0) AS value FROM player WHERE id=@P0";
try
{
value = Database.ExecuteScalar <string>(query, Pid);
Score.Value = Math.Round(Decimal.Parse(value, CultureInfo.InvariantCulture), 4);
}
catch
{
value = "0.000";
Score.Value = 0.0000m;
}
query = "SELECT COUNT(id), COALESCE((cmdscore * 1.0 / cmdtime), 0.0) AS value FROM player WHERE value > " + value;
Score.Position = Database.ExecuteScalar <int>(query) + 1;
Score.CtrPosition = Database.ExecuteScalar <int>(query + " AND country=@P0", Model.Player["country"]) + 1;
Score.PageNumber = GetPage(Score.Position);
Score.CtrPageNumber = GetPage(Score.CtrPosition);
Model.Rankings.Add(Score);
#endregion
#region Player Stats
// Define our play time
TimeSpan PlayerTime = TimeSpan.FromSeconds(Int32.Parse(Model.Player["time"].ToString()));
int Kills = Int32.Parse(Model.Player["kills"].ToString());
int Deaths = Int32.Parse(Model.Player["deaths"].ToString());
int Rounds = Int32.Parse(Model.Player["wins"].ToString()) + Int32.Parse(Model.Player["losses"].ToString());
decimal Result;
// Player Stats
foreach (string Query in StatsQueries)
{
PlayerRankingStats Stats = new PlayerRankingStats();
Result = Database.ExecuteScalar <decimal>(Query, Pid);
Stats.PerMinute = (PlayerTime.TotalMinutes > 0) ? Math.Round(Result / (decimal)PlayerTime.TotalMinutes, 4) : Result;
Stats.PerDay = (PlayerTime.TotalDays > 0) ? Math.Round(Result / (decimal)PlayerTime.TotalDays, 4) : Result;
Stats.PerHour = (PlayerTime.TotalHours > 0) ? Math.Round(Result / (decimal)PlayerTime.TotalHours, 4) : Result;
Stats.PerRound = (Rounds > 0) ? Math.Round(Result / Rounds, 4) : Result;
Stats.PerKill = (Kills > 0) ? Math.Round(Result / Kills, 4) : Result;
Stats.PerDeath = (Deaths > 0) ? Math.Round(Result / Deaths, 4) : Result;
Model.Stats.Add(Stats);
}
#endregion
// Send the response
base.SendTemplateResponse("player_rankings", typeof(PlayerRankingsModel), Model, Pid + "_rankings");
}
