public void Trim()
{
using var cmd = _sql.CreateCommand();
cmd.CommandText = "DELETE FROM log WHERE message_id < @message_id";
var cutoff = SnowflakeUtils.ToSnowflake(DateTime.UtcNow.AddDays(-7));
cmd.Parameters.AddWithValue("message_id", (long)cutoff);
cmd.ExecuteNonQuery();
}
private async void CleanUpOldMessages(SocketTextChannel channel, TimeSpan toDeleteOlderThan)
{
DateTime oneWeekAgo = DateTime.Now.Add(toDeleteOlderThan);
ulong oneWeekAgoSnowflake = SnowflakeUtils.ToSnowflake(oneWeekAgo);
var oldMessages = await channel.GetMessagesAsync(oneWeekAgoSnowflake, Direction.Before, 100 /*100 should be enought for a while*/).FlattenAsync();
await channel.DeleteMessagesAsync(oldMessages);
//var messageDelete = await channel.SendMessageAsync($"Deleting {oldMessages.Count()} messages"); // enable when this message is correct
//Task.Delay(TimeSpan.FromMinutes(5));
//messageDelete.DeleteAsync();
}
public static async Task <string> GenerateMovieForUsers(ulong guildId, int hoursAmount = -1, int fps = 30, int groupByHours = 24, int groupByMinutes = -1, bool stacked = true, bool drawDots = true, string filePrefix = "", params ulong[] channelIds)
{
Stopwatch watch = new Stopwatch();
watch.Start();
var messageInfos = GetUserMessageInfos(hoursAmount < 0 ? null : SnowflakeUtils.ToSnowflake(DateTimeOffset.Now.AddHours(hoursAmount * (-1))));
watch.Stop();
var userIdsOfTopPosters = messageInfos.GroupBy(i => i.KeyId).OrderByDescending(i => i.Count()).Take(50).Select(i => i.Key);
//await Context.Channel.SendMessageAsync($"Retrieved data in {watch.ElapsedMilliseconds}ms");
var groups = GroupGraphEntryInfoBy(groupByHours, groupByMinutes, messageInfos);
var keys = groups.Select(x => x.Key);
keys = FillMissingEntries(keys, new TimeSpan(Math.Max(groupByHours, 0), Math.Max(groupByMinutes, 0), 0));
var users = DatabaseManager.Instance().GetDiscordUsers();
users = users.Where(i => userIdsOfTopPosters.Contains(i.DiscordUserId)).ToList();
//await Context.Channel.SendMessageAsync($"Total frames {groups.Count()}");
var parsedMessageInfos = GetUserParsedMessageInfos(groups, users);
(string basePath, string baseOutputPath) = CleanAndCreateMovieFolders();
if (stacked)
{
StackResults(parsedMessageInfos);
}
try
{
await GenerateFrames(keys, parsedMessageInfos, basePath, drawDots);
string fileName = await RunFFMpeg(basePath, baseOutputPath, fps, filePrefix);
return(fileName);
}
catch (Exception ex)
{
//_logger.LogError(ex, "Error while creating movie");
return(null);
}
}
public async Task ReportAsync(IUser user, [Remainder] string reportMessage = null)
{
if (Context.Guild != null)
{
await Context.Channel.DeleteMessageAsync(Context.Message);
}
if (string.IsNullOrEmpty(reportMessage))
{
throw new Exception("Please enter in a description of the report!");
}
var previous = Context.Guild != null?GetPreviousMessage(user) : null;
var reportId = previous?.Id ?? SnowflakeUtils.ToSnowflake(DateTimeOffset.UtcNow);
await QuickReportWatcher.FileReport(reportId, Context.User, user, Context.Channel, previous, reportMessage);
}
public async Task CountMessages(SocketGuildUser userAccount = null)
{
var user = Context.User as SocketGuildUser;
var weekFilter = SnowflakeUtils.ToSnowflake(DateTimeOffset.UtcNow - TimeSpan.FromDays(7));
var monthFilter = SnowflakeUtils.ToSnowflake(DateTimeOffset.UtcNow - TimeSpan.FromDays(31));
var getWeek = await Context.Channel.GetMessagesAsync(weekFilter, Direction.After, 1000, CacheMode.AllowDownload).FlattenAsync();
var getMonth = await Context.Channel.GetMessagesAsync(monthFilter, Direction.After, 1000, CacheMode.AllowDownload).FlattenAsync();
if (userAccount == null)
{
var userWeekMessages = getWeek.Where(x => x.Author.Id == Context.User.Id);
var userMonthMessages = getMonth.Where(x => x.Author.Id == Context.User.Id);
var weekCount = userWeekMessages.Count();
var monthCount = userMonthMessages.Count();
var embed = new EmbedBuilder();
embed.WithAuthor(user);
embed.WithDescription($"**User Message Counts**\n7 Days: `{weekCount} messages`\n31 Days: `{monthCount} messages`");
embed.WithColor(Color.Blue);
embed.WithCurrentTimestamp();
await Context.Channel.SendMessageAsync("", false, embed.Build());
}
else
{
var userWeekMessages = getWeek.Where(x => x.Author.Id == userAccount.Id);
var userMonthMessages = getMonth.Where(x => x.Author.Id == userAccount.Id);
var weekCount = userWeekMessages.Count();
var monthCount = userMonthMessages.Count();
var embed = new EmbedBuilder();
embed.WithAuthor(userAccount);
embed.WithDescription($"**User Message Counts**\n7 Days: `{weekCount} messages`\n31 Days: `{monthCount} messages`");
embed.WithColor(Color.Blue);
embed.WithCurrentTimestamp();
await Context.Channel.SendMessageAsync("", false, embed.Build());
}
}
protected override void OnMessage(MessageEventArgs e)
{
Program.LogMsg(e.Data, LogSeverity.Verbose, "AppealsMsg");
var json = JObject.Parse(e.Data);
var type = json["type"].ToString();
if (type == "GetMessages")
{
var before = json["before"].ToObject <long>();
var msgId = SnowflakeUtils.ToSnowflake(DateTimeOffset.FromUnixTimeMilliseconds(before));
var messages = Appeal.AppealChannel.GetMessagesAsync(msgId, Direction.Before).FlattenAsync().Result;
foreach (var x in messages)
{
if (x is IUserMessage u)
{
NewMessage(u);
}
}
}
else if (type == "SendMessage")
{
if (Appeal.IsMuted)
{
var time = Program.FormatTimeSpan(Appeal.MutedUntil.Value - DateTime.Now, true);
SendInfo("Error: This ban appeal is muted.", time);
}
else
{
var content = json["content"].ToString();
try
{
Appeal.SendMessageAsync(content, User.Name, User.GetAvatarUrl()).Wait();
} catch (Exception ex)
{
Program.LogMsg(ex, "BanAppeal");
SendInfo($"Exception occured: {ex.Message}");
}
}
}
}
public async ValueTask HandleLoggerBotCleanup(SocketMessage msg, GuildConfig cachedGuild)
{
// Bail if not enabled, or if we don't have permission here
if (!cachedGuild.LogCleanupEnabled)
{
return;
}
if (!(msg.Channel is SocketTextChannel channel))
{
return;
}
if (!channel.Guild.GetUser(_client.CurrentUser.Id).GetPermissions(channel).ManageMessages)
{
return;
}
// If this message is from a *webhook*, check if the name matches one of the bots we know
// TODO: do we need to do a deeper webhook origin check, or would that be too hard on the rate limit?
// If it's from a *bot*, check the bot ID to see if we know it.
LoggerBot bot = null;
if (msg.Author.IsWebhook)
{
_botsByWebhookName.TryGetValue(msg.Author.Username, out bot);
}
else if (msg.Author.IsBot)
{
_bots.TryGetValue(msg.Author.Id, out bot);
}
// If we didn't find anything before, or what we found is an unsupported bot, bail
if (bot == null)
{
return;
}
// We try two ways of extracting the actual message, depending on the bots
if (bot.FuzzyExtractFunc != null)
{
// Some bots (Carl, Circle, etc) only give us a user ID and a rough timestamp, so we try our best to
// "cross-reference" those with the message DB. We know the deletion event happens *after* the message
// was sent, so we're checking for any messages sent in the same guild within 3 seconds before the
// delete event timestamp, which is... good enough, I think? Potential for false positives and negatives
// either way but shouldn't be too much, given it's constrained by user ID and guild.
var fuzzy = bot.FuzzyExtractFunc(msg);
if (fuzzy == null)
{
return;
}
using var conn = await _db.Obtain();
var mid = await conn.QuerySingleOrDefaultAsync <ulong?>(
"select mid from messages where sender = @User and mid > @ApproxID and guild = @Guild",
new
{
fuzzy.Value.User,
Guild = (msg.Channel as ITextChannel)?.GuildId ?? 0,
ApproxId = SnowflakeUtils.ToSnowflake(fuzzy.Value.ApproxTimestamp - TimeSpan.FromSeconds(3))
});
if (mid == null)
{
return; // If we didn't find a corresponding message, bail
}
// Otherwise, we can *reasonably assume* that this is a logged deletion, so delete the log message.
await msg.DeleteAsync();
}
else if (bot.ExtractFunc != null)
{
// Other bots give us the message ID itself, and we can just extract that from the database directly.
var extractedId = bot.ExtractFunc(msg);
if (extractedId == null)
{
return; // If we didn't find anything, bail.
}
using var conn = await _db.Obtain();
// We do this through an inline query instead of through DataStore since we don't need all the joins it does
var mid = await conn.QuerySingleOrDefaultAsync <ulong?>("select mid from messages where original_mid = @Mid", new { Mid = extractedId.Value });
if (mid == null)
{
return;
}
// If we've gotten this far, we found a logged deletion of a trigger message. Just yeet it!
await msg.DeleteAsync();
} // else should not happen, but idk, it might
}
public override async Task DoWork(CancellationToken cancellationToken)
{
_logger.LogInformation($"{DateTime.Now:hh:mm:ss} {Name} is working.");
// get settings per channel
var botChannelSettings = DatabaseManager.Instance().GetAllChannelSettings(); // TODO filter for guild
string result = "";
// todo config
ulong guildId = 747752542741725244;
ulong spamChannel = 768600365602963496;
var guild = Program.Client.GetGuild(guildId);
var textChannel = guild.GetTextChannel(spamChannel);
foreach (var item in Program.Client.Guilds)
{
if (item.Id != Program.BaseGuild)
{
continue; // only sync eth dinfk for now
}
foreach (var botChannelSetting in botChannelSettings)
{
// TODO fix channel where oldest post is year 2154
try
{
var channel = item.GetTextChannel(botChannelSetting.DiscordChannelId);
if (botChannelSetting != null && ((BotPermissionType)botChannelSetting?.ChannelPermissionFlags).HasFlag(BotPermissionType.Read))
{
if (channel != null)
{
// TODO alot of dupe code clean up and generalize
if (!botChannelSetting.ReachedOldestPreload)
{
// should be 50 calls -> around 1 min per channel
int amount = 20_000; // we do only 50k a day to not overload the requests + db
// we still need to go back
DateTimeOffset fromDate = DateTimeOffset.Now;
if (botChannelSetting.OldestPostTimePreloaded.HasValue)
{
fromDate = botChannelSetting.OldestPostTimePreloaded.Value;
}
ulong fromSnowflake = SnowflakeUtils.ToSnowflake(fromDate);
var processResponse = await ProcessLoadedMessages(channel, fromSnowflake, Direction.Before, amount);
if (processResponse?.SuccessCount > 0 && !processResponse.ReachedEndOfChannel)
{
// we synced some successfully
result += $"{channel.Name} synced. Msg: {processResponse.SuccessCount}/{processResponse.DuplicateCount} Users: {processResponse.NewUsers} Emotes: {processResponse.EmotesAdded} " +
$"Time: {processResponse.ElapsedMilliseconds / 1000}s Time: {SnowflakeUtils.FromSnowflake(processResponse.OldestMessageId)}/{SnowflakeUtils.FromSnowflake(processResponse.NewestMessageId)}" + Environment.NewLine;
DatabaseManager.Instance().UpdateChannelSetting(botChannelSetting.DiscordChannelId, -1, processResponse.OldestMessageId, processResponse.NewestMessageId);
}
else if (processResponse.ReachedEndOfChannel)
{
result += $"{channel.Name} no new messages. Reached the end..." + Environment.NewLine;
// reached the end
DatabaseManager.Instance().UpdateChannelSetting(botChannelSetting.DiscordChannelId, -1, processResponse.OldestMessageId, processResponse.NewestMessageId, true);
}
else
{
// error which "hopefully got logged and we just ignore this case xD
}
//var messagesFromMsg = await channel.GetMessagesAsync(fromSnowflake, Direction.Before, amount).FlattenAsync();
}
else
{
// should be 50 calls -> around 1 min per channel
int amount = 20_000; // we do only 50k a day to not overload the requests + db
// we still need to go back
DateTimeOffset fromDate = DateTimeOffset.Now;
if (botChannelSetting.NewestPostTimePreloaded.HasValue)
{
fromDate = botChannelSetting.NewestPostTimePreloaded.Value;
}
ulong fromSnowflake = SnowflakeUtils.ToSnowflake(fromDate);
var processResponse = await ProcessLoadedMessages(channel, fromSnowflake, Direction.After, amount);
if (processResponse?.SuccessCount > 0 || processResponse?.DuplicateCount > 0)
{
if (processResponse.SuccessCount > 0)
{
// we synced some successfully
result += $"{channel.Name} synced (new). Msg: {processResponse.SuccessCount}/{processResponse.DuplicateCount} Users: {processResponse.NewUsers} Emotes: {processResponse.EmotesAdded} " +
$"Time: {processResponse.ElapsedMilliseconds / 1000}s Time: {SnowflakeUtils.FromSnowflake(processResponse.OldestMessageId)}/{SnowflakeUtils.FromSnowflake(processResponse.NewestMessageId)}" + Environment.NewLine;
}
DatabaseManager.Instance().UpdateChannelSetting(botChannelSetting.DiscordChannelId, -1, processResponse.OldestMessageId, processResponse.NewestMessageId);
}
else
{
// error which "hopefully got logged and we just ignore this case xD
}
}
// read all channels that we can read from the current guild
// see preload status
// do preload
// <- 25k msg at a time
// -> from newest to now compare delta
}
else
{
//result += $"Ignored {channel?.Name ?? "Channel deleted but has active settings"}" + Environment.NewLine;
}
}
else
{
//result += $"Ignored {channel?.Name ?? "Channel not found"}" + Environment.NewLine;
}
}
catch (Exception ex)
{
// contain exception for one channel only
}
if (result.Length > 1500)
{
textChannel.SendMessageAsync(result.Substring(0, Math.Min(2000, result.Length)));
result = "";
}
}
}
if (result.Length > 0)
{
textChannel.SendMessageAsync(result.Substring(0, Math.Min(2000, result.Length)));
}
// todo if result > 2k chars
//return Task.CompletedTask;
}