internal Match_RocketLeague(string json_data) : base(json_data)
{
Blue = new Team_RocketLeague(_ParsedData["team_0"]?.ToString() ?? "");
Orange = new Team_RocketLeague(_ParsedData["team_1"]?.ToString() ?? "");
Playlist = (RLPlaylist)GetInt("playlist");
RemainingSeconds = GetInt("time");
}
public static async Task <List <Leaderboard> > GetLeaderboardTop5Async(RLPlaylist playlist, bool monthly, SqlConnection conn)
{
List <Leaderboard> records = new List <Leaderboard>();
using (SqlCommand cmd = conn.CreateCommand())
{
cmd.Parameters.AddWithValue("@Playlist", DbType.Byte).Value = (byte)playlist;
if (monthly)
{
cmd.CommandText = "select TOP 5 * from (select row_number() OVER (ORDER BY x.Wins DESC, x.TotalGames ASC) as Rank, x.UserID, x.Wins, x.TotalGames from (SELECT qp.UserID, ISNULL(SUM(CASE WHEN ((qp.Team = 0 AND q.ScoreTeamA > q.ScoreTeamB) OR (qp.Team = 1 AND q.ScoreTeamA < q.ScoreTeamB)) THEN 1 END), 0) as Wins, COUNT(1) as TotalGames FROM Queue q INNER JOIN QueuePlayer qp ON qp.QueueID = q.QueueID WHERE ((q.ScoreTeamA > 0 OR q.ScoreTeamB > 0) OR (DATEDIFF(hour, q.Created, GetDate()) > 24)) AND q.Created >= CAST(DATEADD(dd, -DAY(GETDATE()) + 1, GETDATE()) AS DATE) AND q.Created < CAST(DATEADD(month, DATEDIFF(month, 0, GETDATE()) + 1, 0) AS DATE) AND q.Playlist = @Playlist GROUP BY qp.UserID) x ) y order by y.Rank";
}
else
{
cmd.CommandText = "select TOP 5 * from (select row_number() OVER (ORDER BY x.Wins DESC, x.TotalGames ASC) as Rank, x.UserID, x.Wins, x.TotalGames from (SELECT qp.UserID, ISNULL(SUM(CASE WHEN ((qp.Team = 0 AND q.ScoreTeamA > q.ScoreTeamB) OR (qp.Team = 1 AND q.ScoreTeamA < q.ScoreTeamB)) THEN 1 END), 0) as Wins, COUNT(1) as TotalGames FROM Queue q INNER JOIN QueuePlayer qp ON qp.QueueID = q.QueueID WHERE ((q.ScoreTeamA > 0 OR q.ScoreTeamB > 0) OR (DATEDIFF(hour, q.Created, GetDate()) > 24)) AND q.Playlist = @Playlist GROUP BY qp.UserID) x ) y order by y.Rank";
}
using (SqlDataReader reader = cmd.ExecuteReader())
{
while (await reader.ReadAsync())
{
Leaderboard rec = new Leaderboard()
{
UserID = (ulong)(decimal)reader["UserID"],
Wins = (int)reader["Wins"],
TotalGames = (int)reader["TotalGames"]
};
records.Add(rec);
}
reader.Close();
}
}
return(records);
}
private RLQueue(RLPlaylist playlist, SocketGuildChannel channel, bool isLeaderboardQueue)
{
Created = DateTime.Now;
Playlist = playlist;
IsLeaderboardQueue = isLeaderboardQueue;
Channel = channel;
Users = new ConcurrentDictionary <ulong, SocketUser>();
}
public static RLRank GetRank(RLPlaylist playlist, short elo)
{
_playlistRanks.TryGetValue(playlist, out List <RLRank> ranks);
if (ranks == null)
{
throw new RLException("Invalid playlist");
}
return(ranks.Where(x => x.RequiredElo <= elo).LastOrDefault());
}
public static async Task <long> InsertQueueAsync(ulong guildId, RLPlaylist type, List <SocketUser> team_a, List <SocketUser> team_b)
{
long queueId = -1;
using (SqlConnection conn = GetSqlConnection())
{
await conn.OpenAsync();
using (SqlTransaction tr = conn.BeginTransaction())
{
try
{
using (SqlCommand cmd = conn.CreateCommand())
{
cmd.Transaction = tr;
cmd.Parameters.AddWithValue("@GuildID", DbType.Decimal).Value = (decimal)guildId;
cmd.Parameters.AddWithValue("@Playlist", DbType.Byte).Value = (byte)type;
cmd.CommandText = "INSERT INTO Queue(GuildID, ScoreTeamA, ScoreTeamB, Created, Playlist) OUTPUT INSERTED.QueueID VALUES(@GuildID, 0, 0, GETDATE(), @Playlist);";
var res = await cmd.ExecuteScalarAsync();
queueId = (long)res;
}
var tasks = new Task[team_a.Count + team_b.Count];
int i = 0;
foreach (SocketUser user in team_a)
{
tasks[i] = InsertQueuePlayerAsync(conn, tr, guildId, queueId, user.Id, 0);
i++;
}
foreach (SocketUser user in team_b)
{
tasks[i] = InsertQueuePlayerAsync(conn, tr, guildId, queueId, user.Id, 1);
i++;
}
await Task.WhenAll(tasks);
tr.Commit();
}
catch (Exception ex)
{
tr.Rollback();
throw ex;
}
}
}
return(queueId);
}
public async Task Top5Async([OverrideTypeReader(typeof(RLPlaylistTypeReader))] RLPlaylist playlist)
{
await Context.Channel.TriggerTypingAsync();
Leaderboard[] monthlyTop5 = null;
Leaderboard[] allTimeTop5 = null;
using (SqlConnection conn = Database.GetSqlConnection())
{
await conn.OpenAsync();
try
{
var monthlyTask = Database.GetLeaderboardTop5Async(playlist, true, conn);
var allTimeTask = Database.GetLeaderboardTop5Async(playlist, false, conn);
await Task.WhenAll(monthlyTask, allTimeTask);
monthlyTop5 = (await monthlyTask).ToArray();
allTimeTop5 = (await allTimeTask).ToArray();
}
catch (Exception ex)
{
await ReplyAsync(ex.Message);
throw ex;
}
finally
{
conn.Close();
}
}
var builder = new EmbedBuilder()
.WithColor(RLBot.EMBED_COLOR)
.WithTitle($":trophy: {playlist} Leaderboard :trophy:");
EmbedWithTop5(builder, "Monthly", monthlyTop5);
EmbedWithTop5(builder, "All-Time", allTimeTop5);
if (builder.Fields.Count > 0)
{
await ReplyAsync("", false, builder.Build());
}
else
{
await ReplyAsync("No leaderboard data found.");
}
}
public async Task StatsAsync([OverrideTypeReader(typeof(RLPlaylistTypeReader))] RLPlaylist playlist, IUser user = null)
{
await Context.Channel.TriggerTypingAsync();
var userInfo = user ?? Context.Message.Author;
Leaderboard recordTotal = null;
Leaderboard recordMonthly = null;
using (SqlConnection conn = Database.GetSqlConnection())
{
await conn.OpenAsync();
try
{
var queueTotalTask = Database.GetLeaderboardUserStatsAsync(userInfo.Id, playlist, false, conn);
var queueMonthlyTask = Database.GetLeaderboardUserStatsAsync(userInfo.Id, playlist, true, conn);
await Task.WhenAll(queueTotalTask, queueMonthlyTask);
recordTotal = await queueTotalTask;
recordMonthly = await queueMonthlyTask;
}
catch (Exception ex)
{
await ReplyAsync(ex.Message);
throw ex;
}
finally
{
conn.Close();
}
}
string monthlyStats = (recordMonthly != null ? $"#{recordMonthly.Rank}\nWins: {recordMonthly.Wins}\nGames Played: {recordMonthly.TotalGames}" : "Unranked");
string totalStats = (recordTotal != null ? $"#{recordTotal.Rank}\nWins: {recordTotal.Wins}\nGames Played: {recordTotal.TotalGames}" : "Unranked");
await ReplyAsync("", embed : new EmbedBuilder()
.WithColor(RLBot.EMBED_COLOR)
.WithTitle($":trophy: {playlist} Leaderboard - {userInfo} :trophy:")
.AddField("Monthly", monthlyStats, true)
.AddField("All-Time", totalStats, true)
.Build());
}
public static async Task <Leaderboard> GetLeaderboardUserStatsAsync(ulong guildId, ulong userId, RLPlaylist playlist, bool monthly, SqlConnection conn = null)
{
Leaderboard rec = null;
using (SqlCommand cmd = conn.CreateCommand())
{
cmd.Parameters.AddWithValue("@GuildID", DbType.Decimal).Value = (decimal)guildId;
cmd.Parameters.AddWithValue("@Playlist", DbType.Byte).Value = (byte)playlist;
cmd.Parameters.AddWithValue("@UserID", DbType.Decimal).Value = (decimal)userId;
if (monthly)
{
cmd.CommandText = "SELECT * FROM ( SELECT row_number() OVER (ORDER BY x.Wins DESC, x.TotalGames ASC) AS Rank, x.UserID, x.Wins, x.TotalGames FROM ( SELECT qp.UserID, ISNULL(SUM(CASE WHEN ((qp.Team = 0 AND q.ScoreTeamA > q.ScoreTeamB) OR (qp.Team = 1 AND q.ScoreTeamA < q.ScoreTeamB)) THEN 1 END), 0) AS Wins, COUNT(1) AS TotalGames FROM Queue q INNER JOIN QueuePlayer qp ON qp.GuildID = q.GuildID AND qp.QueueID = q.QueueID WHERE ((q.ScoreTeamA > 0 OR q.ScoreTeamB > 0) OR (DATEDIFF(hour, q.Created, GetDate()) > 24)) AND q.Created >= CAST(DATEADD(dd, -DAY(GETDATE()) + 1, GETDATE()) AS DATE) AND q.Created < CAST(DATEADD(month, DATEDIFF(month, 0, GETDATE()) + 1, 0) AS DATE) AND q.GuildID = @GuildID AND q.Playlist = @Playlist GROUP BY qp.UserID) x ) y WHERE y.UserID = @UserID;";
}
else
{
cmd.CommandText = "SELECT * FROM ( SELECT row_number() OVER (ORDER BY x.Wins DESC, x.TotalGames ASC) AS Rank, x.UserID, x.Wins, x.TotalGames FROM ( SELECT qp.UserID, ISNULL(SUM(CASE WHEN ((qp.Team = 0 AND q.ScoreTeamA > q.ScoreTeamB) OR (qp.Team = 1 AND q.ScoreTeamA < q.ScoreTeamB)) THEN 1 END), 0) AS Wins, COUNT(1) AS TotalGames FROM Queue q INNER JOIN QueuePlayer qp ON qp.GuildID = q.GuildID AND qp.QueueID = q.QueueID WHERE ((q.ScoreTeamA > 0 OR q.ScoreTeamB > 0) OR (DATEDIFF(hour, q.Created, GetDate()) > 24)) AND q.GuildID = @GuildID AND q.Playlist = @Playlist GROUP BY qp.UserID) x ) y WHERE y.UserID = @UserID;";
}
using (SqlDataReader reader = cmd.ExecuteReader())
{
if (reader.HasRows)
{
await reader.ReadAsync();
rec = new Leaderboard()
{
UserID = userId,
Rank = (long)reader["Rank"],
Wins = (int)reader["Wins"],
TotalGames = (int)reader["TotalGames"]
};
}
reader.Close();
}
}
return(rec);
}
public static async Task SetQueueResultAsync(ulong guildId, long queueId, byte scoreTeamA, byte scoreTeamB, RLPlaylist playlist, List <QueuePlayer> players)
{
using (SqlConnection conn = GetSqlConnection())
{
await conn.OpenAsync();
using (SqlTransaction tr = conn.BeginTransaction())
{
try
{
// check if the queue exists and if the score hasn't been submitted yet
var queue = await GetQueueAsync(conn, tr, guildId, queueId);
if (queue == null)
{
throw new Exception($"Didn't find queue {queueId}!");
}
if (queue.ScoreTeamA != 0 && queue.ScoreTeamB != 0)
{
throw new Exception($"The score for queue {queueId} has already been submitted!");
}
// update the queue score
await UpdateQueueAsync(conn, tr, guildId, queueId, scoreTeamA, scoreTeamB);
// update player elos
foreach (QueuePlayer player in players)
{
await UpdateUserInfoAsync(conn, tr, guildId, player.UserId, playlist, player.Elo);
}
tr.Commit();
}
catch (Exception ex)
{
tr.Rollback();
throw ex;
}
}
}
}
public static async Task UpdateQueueChannelAsync(ulong guildId, ulong channelId, RLPlaylist playlist, bool ranked, int?requiredElo)
{
using (SqlConnection conn = GetSqlConnection())
{
await conn.OpenAsync();
using (SqlTransaction tr = conn.BeginTransaction())
{
try
{
using (SqlCommand cmd = conn.CreateCommand())
{
cmd.Transaction = tr;
cmd.Parameters.AddWithValue("@GuildID", DbType.Decimal).Value = (decimal)guildId;
cmd.Parameters.AddWithValue("@ChannelID", DbType.Decimal).Value = (decimal)channelId;
cmd.Parameters.AddWithValue("@Playlist", DbType.Byte).Value = (byte)playlist;
cmd.Parameters.AddWithValue("@Ranked", DbType.Boolean).Value = ranked;
if (ranked)
{
cmd.Parameters.AddWithValue("@RequiredElo", DbType.Int32).Value = requiredElo;
}
else
{
cmd.Parameters.AddWithValue("@RequiredElo", DBNull.Value);
}
cmd.CommandText = "UPDATE QueueChannel SET Playlist = @Playlist, Ranked = @Ranked, RequiredElo = @RequiredElo;";
await cmd.ExecuteNonQueryAsync();
}
tr.Commit();
}
catch (Exception ex)
{
tr.Rollback();
throw ex;
}
}
}
}
private static async Task UpdateUserInfoAsync(SqlConnection conn, SqlTransaction tr, ulong guildId, ulong userId, RLPlaylist playlist, short elo)
{
using (SqlCommand cmd = conn.CreateCommand())
{
cmd.Transaction = tr;
cmd.Parameters.AddWithValue("@GuildID", DbType.Decimal).Value = (decimal)guildId;
cmd.Parameters.AddWithValue("@UserID", DbType.Decimal).Value = (decimal)userId;
cmd.Parameters.AddWithValue("@Elo", DbType.Int16).Value = elo;
switch (playlist)
{
case RLPlaylist.Duel:
cmd.CommandText = "UPDATE UserInfo set Elo1s = @Elo WHERE GuildID = @GuildID AND UserID = @UserID";
break;
case RLPlaylist.Doubles:
cmd.CommandText = "UPDATE UserInfo set Elo2s = @Elo WHERE GuildID = @GuildID AND UserID = @UserID";
break;
case RLPlaylist.Standard:
cmd.CommandText = "UPDATE UserInfo set Elo3s = @Elo WHERE GuildID = @GuildID AND UserID = @UserID";
break;
}
await cmd.ExecuteNonQueryAsync();
}
}
public static List <RLRank> GetRanks(RLPlaylist playlist)
{
_playlistRanks.TryGetValue(playlist, out List <RLRank> ranks);
return(ranks);
}
