internal static bool ApproveRequestQuote(int quoteId, ulong approvedBy, ulong approvedIn)
{
(MySqlDataReader dr, MySqlConnection conn)reader =
DatabaseActivity.ExecuteReader("SELECT * FROM requested_quotes WHERE requestQuoteId = " + quoteId +
" LIMIT 1");
var quote = new RequestQuote();
while (reader.dr.Read())
{
quote.RequestId = reader.dr.GetInt32("requestQuoteId");
quote.CreatedBy = reader.dr.GetUInt64("requestedBy");
quote.QuoteText = reader.dr.GetString("quoteText");
quote.QCreatedTimestamp = reader.dr.GetDateTime("dateCreated");
quote.CreatedIn = reader.dr.GetUInt64("requestedIn");
}
reader.dr.Close();
reader.conn.Close();
var added = Quote.AddQuote(quote.QuoteText, quote.CreatedBy, quote.CreatedIn, approvedBy, approvedIn,
quote.QCreatedTimestamp);
RemoveRequestQuote(quote.RequestId);
return(added);
}
internal static List <RequestQuote> LoadAll()
{
var requests = new List <RequestQuote>();
(MySqlDataReader dr, MySqlConnection conn)reader =
DatabaseActivity.ExecuteReader("SELECT * FROM requested_quotes;");
while (reader.dr.Read())
{
var q = new RequestQuote
{
RequestId = reader.dr.GetInt32("requestQuoteId"),
CreatedBy = reader.dr.GetUInt64("requestedBy"),
QuoteText = reader.dr.GetString("quoteText"),
QCreatedTimestamp = reader.dr.GetDateTime("dateCreated"),
CreatedIn = reader.dr.GetUInt64("requestedIn")
};
requests.Add(q);
}
reader.dr.Close();
reader.conn.Close();
return(requests);
}
internal static bool AddQuote(string quote, ulong creatorId, ulong createdIn, ulong acceptedBy = 0,
ulong acceptedIn = 0, DateTime?createdTimestamp = null)
{
if (acceptedBy == 0)
{
acceptedBy = creatorId;
}
if (acceptedIn == 0)
{
acceptedIn = createdIn;
}
List <(string, string)> queryParams = new List <(string id, string value)>
{
("@createdBy", creatorId.ToString()),
("@acceptedBy", acceptedBy.ToString()),
("@quoteText", quote),
("@createdIn", createdIn.ToString()),
("@acceptedIn", acceptedIn.ToString())
};
var rowsUpdated = DatabaseActivity.ExecuteNonQueryCommand(
"INSERT IGNORE INTO " +
"quotes(quoteId,createdBy,acceptedBy,quoteText,dateCreated,createdIn,acceptedIn) " +
"VALUES (NULL, @createdBy, @acceptedBy, @quoteText, CURRENT_TIMESTAMP, @createdIn, @acceptedIn);",
queryParams);
// Add quote to the current loaded quote-list
(MySqlDataReader dr, MySqlConnection conn)reader =
DatabaseActivity.ExecuteReader("SELECT * FROM quotes ORDER BY quoteId DESC LIMIT 1");
var newId = 0;
while (reader.dr.Read())
{
newId = reader.dr.GetInt32("quoteId");
}
reader.dr.Close();
reader.conn.Close();
Quotes.Add(new Quote
{
QId = newId,
CreatorId = creatorId,
AcceptedBy = acceptedBy,
QuoteText = quote,
QCreatedTimestamp = DateTime.Now,
CreatedIn = createdIn,
AcceptedIn = acceptedIn
});
return(rowsUpdated == 1);
}
private static async Task ReadyAddBansToDatabase(SocketGuild g)
{
if (g.GetUser(DiscordBot.Bot.CurrentUser.Id).IsGuildAdministrator() || g.GetUser(DiscordBot.Bot.CurrentUser.Id).GuildPermissions.BanMembers)
{
var bans = await g.GetBansAsync();
foreach (IBan b in bans)
{
var(dataReader, mysqlConnection) = DatabaseActivity.ExecuteReader("SELECT * FROM bans WHERE issuedTo=" + b.User.Id + " AND inGuild=" + g.Id + ";");
int count = 0;
while (dataReader.Read())
{
count++;
}
dataReader.Close();
mysqlConnection.Close();
if (count != 0)
{
continue;
}
//Insert banned users into the database by using INSERT IGNORE
List <(string, string)> queryParams = new List <(string id, string value)>()
{
("@issuedTo", b.User.Id.ToString()),
("@issuedBy", DiscordBot.Bot.CurrentUser.Id.ToString()), // unable to get the issuedBy user ID, so use the Bot ID instead.
("@inGuild", g.Id.ToString()),
("@reason", b.Reason),
("@date", DateTime.Now.ToString("u"))
};
DatabaseActivity.ExecuteNonQueryCommand(
"INSERT IGNORE INTO " +
"bans(issuedTo,issuedBy,inGuild,banDescription,dateIssued) " +
"VALUES (@issuedTo, @issuedBy, @inGuild, @reason, @date);", queryParams);
//end.
}
}
else
{
await new LogMessage(LogSeverity.Info, "Guild Bans", "Unable to get banned users - Bot doesn't have the required permission(s).").PrintToConsole();
}
}
public static User Load(ulong uId)
{
User user = new User();
(MySqlDataReader dr, MySqlConnection conn)reader = DatabaseActivity.ExecuteReader("SELECT * FROM users WHERE id=" + uId + ";");
while (reader.dr.Read())
{
user.Level = reader.dr.GetInt32("level");
user.EXP = reader.dr.GetInt32("exp");
user.Name = reader.dr["name"].ToString();
user.Gender = reader.dr["gender"].ToString();
user.Pronouns = reader.dr["pronouns"].ToString();
user.About = reader.dr["about"].ToString();
user.CustomPrefix = reader.dr["customPrefix"].ToString();
user.AboutR = reader.dr.GetByte("aboutR");
user.AboutG = reader.dr.GetByte("aboutG");
user.AboutB = reader.dr.GetByte("aboutB");
if (reader.dr["teamMember"].ToString().ToUpper() == "Y")
{
user.TeamMember = true;
}
user.EmbedAuthorBuilderIconUrl = reader.dr["authorIconURL"].ToString();
user.EmbedFooterBuilderIconUrl = reader.dr["footerIconURL"].ToString();
user.FooterText = reader.dr["footerText"].ToString();
user.MinecraftUsername = reader.dr["minecraftUsername"].ToString();
user.SnapchatUsername = reader.dr["snapchatUsername"].ToString();
user.InstagramUsername = reader.dr["instagramUsername"].ToString();
user.GitHubUsername = reader.dr["githubUsername"].ToString();
user.PokemonGoFriendCode = reader.dr["pokemonGoFriendCode"].ToString();
user.WebsiteName = reader.dr["websiteName"].ToString();
user.WebsiteUrl = reader.dr["websiteURL"].ToString();
if (reader.dr["isBeingIgnored"].ToString().ToUpper() == "Y")
{
user.IsBotIgnoringUser = true;
}
}
reader.dr.Close();
reader.conn.Close();
return(user);
}
public static Channel Load(ulong channelID)
{
Channel channel = new Channel();
(MySqlDataReader dr, MySqlConnection conn)reader = DatabaseActivity.ExecuteReader("SELECT * FROM channels WHERE channelID=" + channelID + ";");
while (reader.dr.Read())
{
if ((bool)reader.dr["awardingEXP"])
{
channel.AwardingEXP = true;
}
}
reader.dr.Close();
reader.conn.Close();
return(channel);
}
internal static bool AddRequestQuote(string quote, ulong creatorId, ulong createdIn)
{
List <(string, string)> queryParams = new List <(string id, string value)>
{
("@requestedBy", creatorId.ToString()),
("@quoteText", quote),
("@createdIn", createdIn.ToString())
};
var rowsUpdated = DatabaseActivity.ExecuteNonQueryCommand(
"INSERT IGNORE INTO " +
"requested_quotes(requestQuoteId, requestedBy, quoteText, dateCreated, requestedIn) " +
"VALUES(NULL, @requestedBy, @quoteText, CURRENT_TIMESTAMP, @createdIn);", queryParams);
(MySqlDataReader dr, MySqlConnection conn)reader =
DatabaseActivity.ExecuteReader("SELECT * FROM requested_quotes ORDER BY requestQuoteId DESC LIMIT 1");
var id = 0;
while (reader.dr.Read())
{
id = reader.dr.GetInt32("requestQuoteId");
}
reader.dr.Close();
reader.conn.Close();
RequestQuotes.Add(new RequestQuote
{
RequestId = id,
CreatedBy = creatorId,
QuoteText = quote,
QCreatedTimestamp = DateTime.Now,
CreatedIn = createdIn
});
return(rowsUpdated == 1);
}
public static Guild Load(ulong guildID)
{
Guild guild = new Guild();
(MySqlDataReader dr, MySqlConnection conn)reader = DatabaseActivity.ExecuteReader("SELECT * FROM guilds WHERE guildID=" + guildID + ";");
while (reader.dr.Read())
{
guild.Prefix = reader.dr["guildPrefix"].ToString();
guild.WelcomeMessage = reader.dr["welcomeMessage"].ToString();
guild.WelcomeChannelID = GetChannelIDOrDefault(reader.dr, "welcomeChannelID");
guild.LogChannelID = GetChannelIDOrDefault(reader.dr, "logChannelID");
guild.BotChannelID = GetChannelIDOrDefault(reader.dr, "botChannelID");
guild.SenpaiEnabled = reader.dr.GetBoolean("senpaiEnabled");
guild.QuotesEnabled = reader.dr.GetBoolean("quotesEnabled");
guild.NSFWCommandsEnabled = reader.dr.GetBoolean("nsfwCommandsEnabled");
guild.RuleGambleChannelID = GetChannelIDOrDefault(reader.dr, "ruleGambleChannelID");
}
reader.dr.Close();
reader.conn.Close();
return(guild);
}
private static async Task Ready()
{
List <ulong> guildsInDatabase = new List <ulong>();
await Bot.SetGameAsync(Configuration.Load().StatusText, Configuration.Load().StatusLink,
(ActivityType)Configuration.Load().StatusActivity);
await Bot.SetStatusAsync(Configuration.Load().Status);
ModeratorModule.ActiveForDateTime = DateTime.Now;
(MySqlDataReader dr, MySqlConnection conn)reader = DatabaseActivity.ExecuteReader("SELECT * FROM guilds;");
while (reader.dr.Read())
{
ulong id = reader.dr.GetUInt64("guildID");
guildsInDatabase.Add(id);
}
await new LogMessage(LogSeverity.Info, "Startup", "-----------------------------------------------------------------").PrintToConsole();
foreach (SocketGuild g in Bot.Guilds)
{
Console.ResetColor();
await new LogMessage(LogSeverity.Info, "Startup", "Attempting to load " + g.Name).PrintToConsole();
await GuildHandler.InsertGuildToDB(g);
guildsInDatabase.Remove(g.Id);
await new LogMessage(LogSeverity.Info, "Startup", "-----------------------------------------------------------------").PrintToConsole();
foreach (SocketGuildChannel c in g.Channels)
{
await ChannelHandler.InsertChannelToDB(c);
}
await new LogMessage(LogSeverity.Info, "Startup", "-----------------------------------------------------------------").PrintToConsole();
await ReadyAddUsersToDatabase(g).ConfigureAwait(false);
await new LogMessage(LogSeverity.Info, "Startup", "-----------------------------------------------------------------").PrintToConsole();
await ReadyAddBansToDatabase(g).ConfigureAwait(false);
await new LogMessage(LogSeverity.Info, "Startup", "-----------------------------------------------------------------").PrintToConsole();
}
foreach (ulong id in guildsInDatabase)
{
await GuildHandler.RemoveGuildFromDB(id.GetGuild());
DatabaseActivity.ExecuteNonQueryCommand("DELETE FROM channels WHERE inGuildID=" + id);
Console.WriteLine(id + " has been removed from the database.");
}
if (offlineList.Any())
{
await new LogMessage(LogSeverity.Info, "Startup", offlineList.Count + " new users added.").PrintToConsole();
foreach (Tuple <SocketGuildUser, SocketGuild> tupleList in offlineList)
{
await new LogMessage(LogSeverity.Warning, "Startup", tupleList.Item1.Mention + " (" + tupleList.Item1.Id + ") joined " + tupleList.Item2.Name + " while the Bot was offline.").PrintToConsole();
}
}
else
{
await new LogMessage(LogSeverity.Info, "Startup", "No new users added.").PrintToConsole();
}
await new LogMessage(LogSeverity.Info, "Startup", Bot.CurrentUser.Username + " loaded.").PrintToConsole();
// Send message to log channel to announce bot is up and running.
Version v = Assembly.GetExecutingAssembly().GetName().Version;
EmbedBuilder eb = new EmbedBuilder()
.WithTitle("Startup Notification")
.WithColor(59, 212, 50)
.WithThumbnailUrl(Bot.CurrentUser.GetAvatarUrl())
.WithDescription("**" + Bot.CurrentUser.Username + "** : ready event executed.")
.AddField("Version", v.Major + "." + v.Minor + "." + v.Build + "." + v.Revision, true)
.AddField("MelissaNet", VersionInfo.Version, true)
.AddField("Latency", Bot.Latency + "ms", true)
.WithCurrentTimestamp();
await Configuration.Load().LogChannelId.GetTextChannel().SendMessageAsync("", false, eb.Build());
if (offlineList.Any())
{
foreach (Tuple <SocketGuildUser, SocketGuild> tupleList in offlineList)
{
await Configuration.Load().LogChannelId.GetTextChannel().SendMessageAsync("[ALERT] While " + Bot.CurrentUser.Username + " was offline, " + tupleList.Item1.Mention + " (" + tupleList.Item1.Id + ") joined " + tupleList.Item2.Name + ". They have been added to the database.");
}
}
}
public async Task ShowUserAwards(IUser user = null)
{
var userSpecified = user as SocketGuildUser ?? Context.User as SocketGuildUser;
if (userSpecified == null)
{
await ReplyAsync("User not found, please try again.");
return;
}
EmbedAuthorBuilder eab = new EmbedAuthorBuilder();
eab.IconUrl = Configuration.Load().AwardsIconUrl;
eab.Name = userSpecified.Username + "'s Awards";
EmbedBuilder eb = new EmbedBuilder()
.WithAuthor(eab)
.WithTitle("Award Name - Date Obtained")
.WithThumbnailUrl(userSpecified.GetAvatarUrl())
.WithColor(userSpecified.GetCustomRGB());
List <Award> awards = new List <Award>();
(MySqlDataReader dr, MySqlConnection conn)reader = DatabaseActivity.ExecuteReader("SELECT * FROM awards WHERE userId=" + userSpecified.Id + ";");
while (reader.dr.Read())
{
awards.Add(new Award()
{
AwardId = (ulong)reader.dr["awardID"],
UserId = (ulong)reader.dr["userID"],
AwardText = reader.dr["awardText"].ToString(),
DateAwarded = (DateTime)reader.dr["dateAwarded"]
});
}
reader.dr.Close();
reader.conn.Close();
// Swapping variables x and y in comparison will display oldest awards first - y.dateAwarded.CompareTo(x.dateAwarded)
awards.Sort((x, y) => x.DateAwarded.CompareTo(y.DateAwarded));
StringBuilder sb = new StringBuilder();
if (awards.Count == 0)
{
sb.Append("No awards found.");
}
else
{
DateTime date = DateTime.Now;
bool showAllAwards = Configuration.Load().ShowAllAwards;
foreach (Award a in awards)
{
if (!showAllAwards)
{
int result = DateTime.Compare(date, a.DateAwarded);
// result : -1:Future Date, 1:Past Date, 1:Today
if (result >= 0)
{
sb.Append(a.AwardText + " - " + a.DateAwarded.ToString("dd/MM/yyyy") + "\n");
}
}
else
{
sb.Append(a.AwardText + " - " + a.DateAwarded.ToString("dd/MM/yyyy") + "\n");
}
}
}
eb.WithDescription(sb.ToString());
await ReplyAsync("", false, eb.Build());
}
internal static async Task Ready()
{
if (Configuration.Load().ActivityStream == null)
{
IActivity activity = new Game(Configuration.Load().ActivityName, (ActivityType)Configuration.Load().ActivityType);
await DiscordBot.Bot.SetActivityAsync(activity);
}
else
{
IActivity activity = new StreamingGame(Configuration.Load().ActivityName, Configuration.Load().ActivityStream);
await DiscordBot.Bot.SetActivityAsync(activity);
}
await DiscordBot.Bot.SetStatusAsync(Configuration.Load().Status);
ModeratorModule.ActiveForDateTime = DateTime.Now;
List <ulong> guildsInDatabase = new List <ulong>();
var dataReader = DatabaseActivity.ExecuteReader("SELECT * FROM guilds;").Item1;
while (dataReader.Read())
{
ulong id = dataReader.GetUInt64("guildID");
guildsInDatabase.Add(id);
}
await new LogMessage(LogSeverity.Info, "Startup", "-----------------------------------------------------------------").PrintToConsole();
foreach (SocketGuild g in DiscordBot.Bot.Guilds)
{
Console.ResetColor();
await new LogMessage(LogSeverity.Info, "Startup", "Attempting to load " + g.Name).PrintToConsole();
await GuildHandler.InsertGuildToDB(g);
guildsInDatabase.Remove(g.Id);
await new LogMessage(LogSeverity.Info, "Startup", "-----------------------------------------------------------------").PrintToConsole();
foreach (SocketGuildChannel c in g.Channels)
{
await ChannelHandler.InsertChannelToDB(c);
}
await new LogMessage(LogSeverity.Info, "Startup", "-----------------------------------------------------------------").PrintToConsole();
await ReadyAddUsersToDatabase(g).ConfigureAwait(false);
await new LogMessage(LogSeverity.Info, "Startup", "-----------------------------------------------------------------").PrintToConsole();
await ReadyAddBansToDatabase(g).ConfigureAwait(false);
Methods.PrintConsoleSplitLine();
}
foreach (ulong id in guildsInDatabase)
{
await GuildHandler.RemoveGuildFromDB(id.GetGuild());
DatabaseActivity.ExecuteNonQueryCommand("DELETE FROM channels WHERE inGuildID=" + id);
Console.WriteLine(id + @" has been removed from the database.");
}
await LoadAllFromDatabase();
await new LogMessage(LogSeverity.Info, "Startup", DiscordBot.Bot.CurrentUser.Username + " loaded.").PrintToConsole();
// Send message to log channel to announce bot is up and running.
Version v = Assembly.GetExecutingAssembly().GetName().Version;
EmbedBuilder eb = new EmbedBuilder();
eb.WithTitle("Startup Notification");
eb.WithColor(59, 212, 50);
eb.WithThumbnailUrl(DiscordBot.Bot.CurrentUser.GetAvatarUrl());
eb.AddField("Bot Name", DiscordBot.Bot.CurrentUser.Username + "#" + DiscordBot.Bot.CurrentUser.Discriminator, true);
try
{
eb.AddField("Developer Name",
Configuration.Load().Developer.GetUser().Username + "#" +
Configuration.Load().Developer.GetUser().Discriminator, true);
}
catch (UserNotFoundException exception)
{
eb.AddField("Developer Name", "Melissa", true);
await new LogMessage(LogSeverity.Warning, "Startup", exception.Message + " - Using \"Melissa\" instead.").PrintToConsole();
}
eb.AddField("Developer ID", Configuration.Load().Developer, true);
eb.AddField("DiscordBot Version", "v" + v, true);
eb.AddField("MelissaNET Version", "v" + VersionInfo.Version, true);
eb.AddField(".NET Version", typeof(string).Assembly.ImageRuntimeVersion, true);
eb.AddField("Connection & Server Information",
"**Latency:** " + DiscordBot.Bot.Latency + "ms" + "\n" +
"**Server Time:** " + DateTime.Now.ToString("h:mm:ss tt") + "\n");
eb.AddField("Awards", Award.Awards.Count, true);
eb.AddField("Quotes", Quote.Quotes.Count, true);
eb.AddField("Quote Requests", RequestQuote.RequestQuotes.Count, true);
eb.WithCurrentTimestamp();
eb.WithFooter("Ready event executed");
await Configuration.Load().LogChannelId.GetTextChannel().SendMessageAsync("", false, eb.Build());
await NewUsersOfflineAlert();
}