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