/// <summary>
/// Insert a command that was ran into the database.
/// </summary>
public static void InsertCommand(DateTime dateTime, ulong serverId, ulong channelId, ulong userId, string username, string userDisplay,
ulong messageId, string message, string command, string parameters, ulong?replyMessageId)
{
try {
using StatisticsContext statistics = new StatisticsContext();
Command item = new Command {
DateTime = dateTime,
ServerId = serverId,
ChannelId = channelId,
UserId = userId,
Username = username,
UserDisplay = userDisplay,
MessageId = messageId,
Message = message,
CommandName = command,
Parameters = parameters,
ReplyMessageId = replyMessageId
};
statistics.Commands.Add(item);
statistics.SaveChanges();
} catch (Exception ex) {
LoggingManager.Log.Error(ex);
}
}
/// <summary>
/// Insert a user change event into the database.
/// </summary>
public static void InsertUserChange(DateTime dateTime, ulong?serverId, ulong userId, NameChangeType changeType,
string oldUsername, string newUsername, string oldUsernameDiscriminator, string newUsernameDiscriminator, string oldUserDisplay, string newUserDisplay,
string backblazeAvatarBucket, string backblazeAvatarFilename, string backblazeAvatarUrl)
{
try {
using StatisticsContext statistics = new StatisticsContext();
UserChange item = new UserChange {
DateTime = dateTime,
ServerId = serverId,
UserId = userId,
ChangeType = changeType,
OldUsername = oldUsername,
NewUsername = newUsername,
OldUsernameDiscriminator = oldUsernameDiscriminator,
NewUsernameDiscriminator = newUsernameDiscriminator,
OldUserDisplay = oldUserDisplay,
NewUserDisplay = newUserDisplay,
BackblazeAvatarBucket = backblazeAvatarBucket,
BackblazeAvatarFilename = backblazeAvatarFilename,
BackblazeAvatarUrl = backblazeAvatarUrl
};
statistics.UserChanges.Add(item);
statistics.SaveChanges();
} catch (Exception ex) {
LoggingManager.Log.Error(ex);
}
}
/// <summary>
/// Get the number of times the true command was executed.
/// </summary>
public static long GetTrueCount(ulong serverId)
{
try {
using StatisticsContext statistics = new StatisticsContext();
return(statistics.Commands.AsNoTracking().Where(x => x.ServerId == serverId & x.CommandName == "ThatsTrue").LongCount());
} catch (Exception ex) {
LoggingManager.Log.Error(ex);
return(0);
}
}
/// <summary>
/// Get the number of times each says command was executed order by count.
/// </summary>
public static List <KeyValuePair <string, long> > GetSaysCount(ulong serverId)
{
try {
using StatisticsContext statistics = new StatisticsContext();
return(statistics.Commands.AsNoTracking().Where(x => x.ServerId == serverId & _CommandNameMap.Any(c => c.Key == x.CommandName))
.GroupBy(x => x.CommandName).Select(g => new {
CommandName = _CommandNameMap[g.Key],
Count = g.LongCount()
}).OrderByDescending(x => x.Count).ToDictionary(x => x.CommandName, x => x.Count).ToList());
} catch (Exception ex) {
LoggingManager.Log.Error(ex);
return(new List <KeyValuePair <string, long> >());
}
}
/// <summary>
/// Get the most recent message id and reply message id per-user in a channel.
/// </summary>
public static List <ulong> UndoMessages(ulong channelId, ulong userId, int count)
{
try {
using StatisticsContext statistics = new StatisticsContext();
ulong[] messageId = statistics.Commands.AsNoTracking().Where(x => x.ChannelId == channelId & x.UserId == userId).OrderByDescending(x => x.DateTime)
.Where(x => x.ReplyMessageId.HasValue).Take(count).Select(x => x.MessageId).Distinct().ToArray();
ulong[] replyMessageIds = statistics.Commands.AsNoTracking().Where(x => x.ChannelId == channelId & x.UserId == userId).OrderByDescending(x => x.DateTime)
.Where(x => x.ReplyMessageId.HasValue).Where(x => messageId.Contains(x.MessageId)).Select(x => x.ReplyMessageId.Value).Distinct().ToArray();
return(new List <ulong>(messageId.Concat(replyMessageIds)));
} catch (Exception ex) {
LoggingManager.Log.Error(ex);
return(new List <ulong>());
}
}
/// <summary>
/// Get avatar changes from a user.
/// </summary>
/// <param name="userId">User id.</param>
public static string GetUserAvatarChanges(ulong userId)
{
try {
using StatisticsContext statistics = new StatisticsContext();
IQueryable <UserChange> userChanges = statistics.UserChanges.AsNoTracking().OrderByDescending(x => x.DateTime).Where(x => x.UserId == userId).Where(x => x.ChangeType.HasFlag(NameChangeType.Avatar));
StringBuilder builder = new StringBuilder();
foreach (UserChange userChange in userChanges)
{
if (userChange.ChangeType != NameChangeType.None)
{
List <string> changes = new List <string>();
if (userChange.ChangeType.HasFlag(NameChangeType.Avatar))
{
if (!string.IsNullOrWhiteSpace(userChange.BackblazeAvatarUrl))
{
changes.Add($" ● {userChange.BackblazeAvatarUrl}");
}
}
if (changes.Count > 0)
{
builder.Append($"{userChange.DateTime.ToString(Constants.DateTimeFormatShort).ToLower()} utc");
if ((userChange.ChangeType & (userChange.ChangeType - 1)) != 0)
{
builder.Append(Environment.NewLine);
}
builder.Append(string.Join(Environment.NewLine, changes));
builder.Append(Environment.NewLine);
}
}
}
return(builder.ToString().Trim());
} catch (Exception ex) {
LoggingManager.Log.Error(ex);
return(string.Empty);
}
}
public static void Load()
{
using StatisticsContext statistics = new StatisticsContext();
if (statistics.Database.CanConnect())
{
if (statistics.Database.EnsureCreated())
{
LoggingManager.Log.Info("Database created");
}
else
{
LoggingManager.Log.Info("Database already exists, connected");
}
}
else
{
LoggingManager.Log.Error("Can connect check failed");
}
}
/// <summary>
/// Get the top users who have changes by count.
/// </summary>
/// <param name="count">How many users are in the 'top'.</param>
/// <returns>List of (user id, count)</returns>
public static List <KeyValuePair <ulong, long> > GetTopChangers(int count)
{
try {
using StatisticsContext statistics = new StatisticsContext();
return(statistics.UserChanges.AsNoTracking().OrderByDescending(x => x.DateTime)
.Where(x => x.ChangeType != NameChangeType.None)
.GroupBy(x => x.UserId)
.Select(g => new {
UserId = g.Key,
Count = g.LongCount()
}).OrderByDescending(x => x.Count)
.ToDictionary(x => x.UserId, x => x.Count)
.Take(count)
.ToList());
} catch (Exception ex) {
LoggingManager.Log.Error(ex);
return(new List <KeyValuePair <ulong, long> >());
}
}
/// <summary>
/// Get all changes from a user.
/// </summary>
/// <param name="serverId">Server id.</param>
/// <param name="userId">User id.</param>
public static string GetUserChanges(ulong serverId, ulong userId)
{
try {
using StatisticsContext statistics = new StatisticsContext();
IQueryable <UserChange> userChanges = statistics.UserChanges.AsNoTracking().OrderByDescending(x => x.DateTime).Where(x => x.UserId == userId);
StringBuilder builder = new StringBuilder();
foreach (UserChange userChange in userChanges)
{
if (userChange.ChangeType != NameChangeType.None)
{
List <string> changes = new List <string>();
if (userChange.ChangeType.HasFlag(NameChangeType.Username))
{
changes.Add($" ● [ user] {userChange.OldUsername} => {userChange.NewUsername}");
}
if (userChange.ChangeType.HasFlag(NameChangeType.Discriminator))
{
changes.Add($" ● [discrim] #{userChange.OldUsernameDiscriminator} => #{userChange.NewUsernameDiscriminator}");
}
if (userChange.ChangeType.HasFlag(NameChangeType.Display))
{
if (userChange.ServerId.HasValue && userChange.ServerId.Value == serverId)
{
string oldUserDisplay = string.IsNullOrEmpty(userChange.OldUserDisplay) ? "<no nick>" : userChange.OldUserDisplay;
string newUserDisplay = string.IsNullOrEmpty(userChange.NewUserDisplay) ? "<no nick>" : userChange.NewUserDisplay;
changes.Add($" ● [ nick] {oldUserDisplay} => {newUserDisplay}");
}
}
if (userChange.ChangeType.HasFlag(NameChangeType.Avatar))
{
if (!string.IsNullOrWhiteSpace(userChange.BackblazeAvatarUrl))
{
changes.Add($" ● [ avatar] {userChange.BackblazeAvatarUrl}");
}
}
if (changes.Count > 0)
{
builder.Append($"{userChange.DateTime.ToString(Constants.DateTimeFormatShort).ToLower()} utc");
if ((userChange.ChangeType & (userChange.ChangeType - 1)) != 0)
{
builder.Append(Environment.NewLine);
}
builder.Append(string.Join(Environment.NewLine, changes));
builder.Append(Environment.NewLine);
}
}
}
return(builder.ToString().Trim());
} catch (Exception ex) {
LoggingManager.Log.Error(ex);
return(string.Empty);
}
}
