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