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