// Get leaderboard public static async Task <LoriLeaderboard> GetLeaderboardAsync(string lbName, int count = 10) { LoriLeaderboard lb = null; string tableName; tableName = LoriLeaderboard.GetTableName(lbName); var dbCon = DBConnection.Instance(); dbCon.DatabaseName = LCommandHandler.DATABASE_NAME; if (dbCon.IsConnect()) { var cmd = new MySqlCommand($"SELECT * FROM {tableName}", dbCon.Connection); var reader = cmd.ExecuteReader(); if (reader.HasRows) { List <LeaderboardRow> rows = new List <LeaderboardRow>(); while (reader.Read()) { LeaderboardRow row = new LeaderboardRow(reader.GetUInt64(0), reader.GetString(1), reader.GetInt32(2)); rows.Add(row); } lb = new LoriLeaderboard(LoriLeaderboard.GetLeaderboardName(tableName), rows); cmd.Dispose(); reader.Dispose(); } dbCon.Close(); } return(lb); }
// Add score public static async Task AddScoreAsync(ulong userId, string lbName) { var user = CommandHandler.GetBot().GetUser(userId); string username = user.Username; await CheckAsync(userId, username, lbName); string tableName; tableName = LoriLeaderboard.GetTableName(lbName); var dbCon = DBConnection.Instance(); dbCon.DatabaseName = LCommandHandler.DATABASE_NAME; if (dbCon.IsConnect()) { var cmd = new MySqlCommand($"SELECT 1 FROM {tableName} WHERE id = {userId}", dbCon.Connection); var reader = cmd.ExecuteReader(); if (reader.HasRows) { while (reader.Read()) { while (LCommandHandler.Saving) { await Task.Delay(50); } LCommandHandler.Saving = true; int newScore = reader.GetInt32(2) + 1; var updateCmd = new MySqlCommand($"UPDATE {tableName} SET score = @score WHERE id = @id", dbCon.Connection); updateCmd.Parameters.Add("@id", MySqlDbType.UInt64).Value = userId; updateCmd.Parameters.Add("@score", MySqlDbType.Int32).Value = newScore; try { await updateCmd.ExecuteNonQueryAsync(); updateCmd.Dispose(); } catch (Exception ex) { await Util.LoggerAsync(new LogMessage(LogSeverity.Error, "Leaderboards", ex.Message)); updateCmd.Dispose(); } LCommandHandler.Saving = false; } cmd.Dispose(); reader.Dispose(); } dbCon.Close(); } }
private async Task LeaderboardAsync([Remainder] string lb = "") { await Context.Message.DeleteAsync(); if (lb.Equals("")) { BotConfig conf = BotConfig.Load(); var gconf = conf.GetConfig(Context.Guild.Id); await MessageUtil.SendErrorAsync((Context.Channel as ITextChannel), "Incorrect Command Usage", $"Correct Usage: `{gconf.Prefix}leaderboard <game name>`", false); return; } string leaderboard = ""; if (lb.ToLower().Equals("connect 4") || lb.ToLower().Equals("connect4") || lb.ToLower().Equals("c4")) { leaderboard = "Connect 4"; } else if (lb.ToLower().Equals("tic tac toe") || lb.ToLower().Equals("tictactoe") || lb.ToLower().Equals("ttt")) { leaderboard = "Tic Tac Toe"; } else { await MessageUtil.SendErrorAsync((Context.Channel as ITextChannel), "Leaderboard Error", $"Leaderboard with name '{lb}' could not be found."); return; } LoriLeaderboard fullLb = await LeaderboardDatabase.GetLeaderboardAsync(leaderboard, 10); var top10 = fullLb.GetTop(10); string board = $"`{"pos", 5} {"Name", 30} {"score",5}`"; for (int i = 0; i < top10.Count; i++) { board += $"\n`[{i+1,3}] {top10[i].Name,30} - {top10[i].Score,5}`"; } EmbedBuilder embed = new EmbedBuilder() { Title = $"{leaderboard} - Top 10", Color = Color.DarkPurple, Description = board, Footer = new EmbedFooterBuilder() { Text = fullLb.GetPositionAsString(Context.User.Id) } }; await Context.Channel.SendMessageAsync(null, false, embed.Build()); }
// Check if user is on scoreboard public static async Task CheckAsync(ulong id, string name, string lbName) { string tableName; tableName = LoriLeaderboard.GetTableName(lbName); var dbCon = DBConnection.Instance(); dbCon.DatabaseName = LCommandHandler.DATABASE_NAME; if (dbCon.IsConnect()) { var cmd = new MySqlCommand($"SELECT 1 FROM {tableName} WHERE id = {id}", dbCon.Connection); var reader = cmd.ExecuteReader(); if (!reader.HasRows) { while (LCommandHandler.Saving) { await Task.Delay(50); } LCommandHandler.Saving = true; var insertCmd = new MySqlCommand($"INSERT INTO {tableName} (id, name, score) VALUES (@id, @name, @score)", dbCon.Connection); insertCmd.Parameters.Add("@id", MySqlDbType.UInt64).Value = id; insertCmd.Parameters.Add("@name", MySqlDbType.String).Value = name; insertCmd.Parameters.Add("@score", MySqlDbType.Int32).Value = 0; try { await insertCmd.ExecuteNonQueryAsync(); insertCmd.Dispose(); } catch (Exception ex) { await Util.LoggerAsync(new LogMessage(LogSeverity.Error, "Leaderboards", ex.Message)); insertCmd.Dispose(); } LCommandHandler.Saving = false; cmd.Dispose(); reader.Dispose(); } dbCon.Close(); } }
public async Task <LoriLeaderboard> GetLeaderboardAsync(string lbName) { if (String.IsNullOrEmpty(lbName)) { return(null); } string tableName = LoriLeaderboard.GetTableName(lbName); List <LeaderboardRow> rows = new List <LeaderboardRow>(); // Connect to db SqlConnection connection = SqlConnection.Instance(); connection.DatabaseName = "lorisangel"; // connection.DatabasePassword = ""; Guess we gonna need a local file for this... try { string[] columns = { "id", "name", "score" }; var sqlRows = SqlHelper.SelectDataFromTable <LeaderboardDBRow>(connection, tableName, columns); foreach (var row in sqlRows) { rows.Add(new LeaderboardRow((ulong)row.id, row.name, row.score)); } } catch (Exception ex) { Util.Log(LogType.Error, "MySql", ex.Message); } // Create leaderboard with rows LoriLeaderboard leaderboard = new LoriLeaderboard(lbName, rows); return(leaderboard); }