private async Task <UserPlay> GetLastStoredPlay(User user)
{
await using var db = new FMBotDbContext(this._connectionString);
return(await db.UserPlays
.OrderByDescending(o => o.TimePlayed)
.FirstOrDefaultAsync(f => f.UserId == user.UserId));
}
public async Task <DailyOverview> GetDailyOverview(User user, int amountOfDays)
{
await using var db = new FMBotDbContext(ConfigData.Data.Database.ConnectionString);
var now = DateTime.UtcNow;
var minDate = DateTime.UtcNow.AddDays(-amountOfDays);
var plays = await db.UserPlays
.AsQueryable()
.Where(w => w.TimePlayed.Date <= now.Date &&
w.TimePlayed.Date > minDate.Date &&
w.UserId == user.UserId)
.ToListAsync();
var overview = new DailyOverview
{
Days = plays
.OrderByDescending(o => o.TimePlayed)
.GroupBy(g => g.TimePlayed.Date)
.Select(s => new DayOverview
{
Date = s.Key,
Playcount = s.Count(),
TopTrack = GetTopTrackForPlays(s.ToList()),
TopAlbum = GetTopAlbumForPlays(s.ToList()),
TopArtist = GetTopArtistForPlays(s.ToList())
}).ToList(),
Playcount = plays.Count,
Uniques = GetUniqueCount(plays.ToList()),
AvgPerDay = GetAvgPerDayCount(plays.ToList()),
};
return(overview);
}
// Random user
public async Task <string> GetRandomLastFMUserAsync()
{
await using var db = new FMBotDbContext(ConfigData.Data.Database.ConnectionString);
var featuredUser = await db.Users
.AsQueryable()
.FirstOrDefaultAsync(f => f.Featured == true);
if (featuredUser != null)
{
featuredUser.Featured = false;
db.Entry(featuredUser).State = EntityState.Modified;
}
var users = db.Users
.AsQueryable()
.Where(w => w.Blacklisted != true).ToList();
var rand = new Random();
var user = users[rand.Next(users.Count)];
user.Featured = true;
db.Entry(user).State = EntityState.Modified;
await db.SaveChangesAsync();
return(user.UserNameLastFM);
}
public async Task StoreGuildUsers(IGuild guild, IReadOnlyCollection <IGuildUser> guildUsers)
{
var userIds = guildUsers.Select(s => s.Id).ToList();
await using var db = new FMBotDbContext(ConfigData.Data.Database.ConnectionString);
var existingGuild = await db.Guilds
.Include(i => i.GuildUsers)
.FirstAsync(f => f.DiscordGuildId == guild.Id);
var users = await db.Users
.Include(i => i.Artists)
.Where(w => userIds.Contains(w.DiscordUserId))
.Select(s => new GuildUser
{
GuildId = existingGuild.GuildId,
UserId = s.UserId
})
.ToListAsync();
var connString = db.Database.GetDbConnection().ConnectionString;
var copyHelper = new PostgreSQLCopyHelper <GuildUser>("public", "guild_users")
.MapInteger("guild_id", x => x.GuildId)
.MapInteger("user_id", x => x.UserId);
await using var connection = new NpgsqlConnection(connString);
connection.Open();
await using var deleteCurrentArtists = new NpgsqlCommand($"DELETE FROM public.guild_users WHERE guild_id = {existingGuild.GuildId};", connection);
await deleteCurrentArtists.ExecuteNonQueryAsync().ConfigureAwait(false);
await copyHelper.SaveAllAsync(connection, users).ConfigureAwait(false);
Log.Information("Stored guild users for guild with id {guildId}", existingGuild.GuildId);
}
// Featured
public async Task <User> GetFeaturedUserAsync()
{
await using var db = new FMBotDbContext(ConfigData.Data.Database.ConnectionString);
return(await db.Users
.AsQueryable()
.FirstOrDefaultAsync(f => f.Featured == true));
}
// Remove user
public async Task DeleteUser(int userID)
{
await using var db = new FMBotDbContext(ConfigData.Data.Database.ConnectionString);
try
{
var user = await db.Users
.AsQueryable()
.FirstOrDefaultAsync(f => f.UserId == userID);
this._cache.Remove($"user-settings-{user.DiscordUserId}");
this._cache.Remove($"user-isRegistered-{user.DiscordUserId}");
await using var connection = new NpgsqlConnection(ConfigData.Data.Database.ConnectionString);
connection.Open();
await using var deleteArtists = new NpgsqlCommand($"DELETE FROM public.user_artists WHERE user_id = {user.UserId};", connection);
await deleteArtists.ExecuteNonQueryAsync();
await using var deleteAlbums = new NpgsqlCommand($"DELETE FROM public.user_albums WHERE user_id = {user.UserId};", connection);
await deleteAlbums.ExecuteNonQueryAsync();
await using var deleteTracks = new NpgsqlCommand($"DELETE FROM public.user_tracks WHERE user_id = {user.UserId};", connection);
await deleteTracks.ExecuteNonQueryAsync();
db.Users.Remove(user);
await db.SaveChangesAsync();
}
catch (Exception e)
{
Log.Error(e, "Error while deleting user!");
throw;
}
}
public async Task AddLastFMFriendAsync(ulong discordSenderId, string lastfmusername, int?discordFriendId)
{
await using var db = new FMBotDbContext(ConfigData.Data.Database.ConnectionString);
var user = db.Users.FirstOrDefault(f => f.DiscordUserId == discordSenderId);
if (user == null)
{
var newUser = new User
{
DiscordUserId = discordSenderId,
UserType = UserType.User
};
await db.Users.AddAsync(newUser);
user = newUser;
}
var friend = new Friend
{
User = user,
LastFMUserName = lastfmusername,
FriendUserId = discordFriendId
};
await db.Friends.AddAsync(friend);
await db.SaveChangesAsync();
await Task.CompletedTask;
}
public async Task <int> GetTotalUserCountAsync()
{
await using var db = new FMBotDbContext(ConfigData.Data.Database.ConnectionString);
return(await db.Users
.AsQueryable()
.CountAsync());
}
// User settings
public async Task <User> GetUserSettingsAsync(IUser discordUser)
{
await using var db = new FMBotDbContext(ConfigData.Data.Database.ConnectionString);
return(await db.Users
.AsQueryable()
.FirstOrDefaultAsync(f => f.DiscordUserId == discordUser.Id));
}
public async Task <string[]> AddDisabledCommandAsync(IGuild guild, string command)
{
await using var db = new FMBotDbContext(ConfigData.Data.Database.ConnectionString);
var existingGuild = await db.Guilds
.AsQueryable()
.FirstOrDefaultAsync(f => f.DiscordGuildId == guild.Id);
if (existingGuild == null)
{
var newGuild = new Guild
{
DiscordGuildId = guild.Id,
TitlesEnabled = true,
ChartTimePeriod = ChartTimePeriod.Monthly,
FmEmbedType = FmEmbedType.embedmini,
Name = guild.Name,
DisabledCommands = new[] { command }
};
await db.Guilds.AddAsync(newGuild);
await db.SaveChangesAsync();
return(newGuild.DisabledCommands);
}
else
{
if (existingGuild.DisabledCommands != null && existingGuild.DisabledCommands.Length > 0)
{
var newDisabledCommands = existingGuild.DisabledCommands;
Array.Resize(ref newDisabledCommands, newDisabledCommands.Length + 1);
newDisabledCommands[^ 1] = command;
public async Task <IReadOnlyList <ListArtist> > GetTopArtistsForGuild(IReadOnlyList <User> guildUsers,
OrderType orderType)
{
var userIds = guildUsers.Select(s => s.UserId);
await using var db = new FMBotDbContext(ConfigData.Data.Database.ConnectionString);
var query = db.UserArtists
.AsQueryable()
.Where(w => userIds.Contains(w.UserId))
.GroupBy(o => o.Name);
query = orderType == OrderType.Playcount ?
query.OrderByDescending(o => o.Sum(s => s.Playcount)) :
query.OrderByDescending(o => o.Count());
return(await query
.Take(14)
.Select(s => new ListArtist
{
ArtistName = s.Key,
Playcount = s.Sum(su => su.Playcount),
ListenerCount = s.Count()
})
.ToListAsync());
}
// TODO: figure out how to do this
public async Task <int> GetWeekArtistListenerCountForGuildAsync(IEnumerable <User> guildUsers, string artistName)
{
var now = DateTime.UtcNow;
var minDate = DateTime.UtcNow.AddDays(-7);
var userIds = guildUsers.Select(s => s.UserId);
try
{
await using var db = new FMBotDbContext(ConfigData.Data.Database.ConnectionString);
return(await db.UserPlays
.AsQueryable()
.Where(w => w.TimePlayed.Date <= now.Date &&
w.TimePlayed.Date > minDate.Date &&
w.ArtistName.ToLower() == artistName.ToLower() &&
userIds.Contains(w.UserId))
.GroupBy(x => new { x.UserId, x.ArtistName, x.UserPlayId })
.CountAsync());
}
catch (Exception e)
{
Console.WriteLine(e);
throw;
}
}
// User settings
public async Task <bool> UserRegisteredAsync(IUser discordUser)
{
await using var db = new FMBotDbContext(ConfigData.Data.Database.ConnectionString);
return(await db.Users
.AsQueryable()
.AnyAsync(f => f.DiscordUserId == discordUser.Id));
}
public async Task SetGuildPrefixAsync(IGuild guild, string prefix)
{
await using var db = new FMBotDbContext(ConfigData.Data.Database.ConnectionString);
var existingGuild = await db.Guilds
.AsQueryable()
.FirstOrDefaultAsync(f => f.DiscordGuildId == guild.Id);
if (existingGuild == null)
{
var newGuild = new Guild
{
DiscordGuildId = guild.Id,
TitlesEnabled = true,
ChartTimePeriod = ChartTimePeriod.Monthly,
FmEmbedType = FmEmbedType.embedmini,
Name = guild.Name,
Prefix = prefix
};
await db.Guilds.AddAsync(newGuild);
await db.SaveChangesAsync();
}
else
{
existingGuild.Prefix = prefix;
existingGuild.Name = guild.Name;
db.Entry(existingGuild).State = EntityState.Modified;
await db.SaveChangesAsync();
}
}
public async Task <string[]> GetDisabledCommandsForGuild(IGuild guild)
{
await using var db = new FMBotDbContext(ConfigData.Data.Database.ConnectionString);
var existingGuild = await db.Guilds
.AsQueryable()
.FirstOrDefaultAsync(f => f.DiscordGuildId == guild.Id);
return(existingGuild?.DisabledCommands);
}
// User settings
public async Task <bool> UserHasSessionAsync(IUser discordUser)
{
await using var db = new FMBotDbContext(ConfigData.Data.Database.ConnectionString);
var user = await db.Users
.AsQueryable()
.FirstOrDefaultAsync(f => f.DiscordUserId == discordUser.Id);
return(!string.IsNullOrEmpty(user.SessionKeyLastFm));
}
// User settings
public async Task <User> GetFullUserAsync(IUser discordUser)
{
await using var db = new FMBotDbContext(ConfigData.Data.Database.ConnectionString);
return(await db.Users
.Include(i => i.Artists)
.Include(i => i.Friends)
.Include(i => i.FriendedByUsers)
.FirstOrDefaultAsync(f => f.DiscordUserId == discordUser.Id));
}
public async Task <Guild> GetGuildAsync(ulong guildId)
{
await using var db = new FMBotDbContext(ConfigData.Data.Database.ConnectionString);
return(await db.Guilds
.AsQueryable()
.Include(i => i.GuildUsers)
.ThenInclude(t => t.User)
.FirstOrDefaultAsync(f => f.DiscordGuildId == guildId));
}
public async Task ResetChartTimerAsync(User user)
{
await using var db = new FMBotDbContext(ConfigData.Data.Database.ConnectionString);
user.LastGeneratedChartDateTimeUtc = DateTime.Now;
db.Entry(user).State = EntityState.Modified;
await db.SaveChangesAsync();
}
/// <inheritdoc />
public async Task LoadAllDisabledCommands()
{
await using var db = new FMBotDbContext(ConfigData.Data.Database.ConnectionString);
var servers = await db.Guilds.Where(w => w.DisabledCommands != null).ToListAsync();
foreach (var server in servers)
{
StoreDisabledCommands(server.DisabledCommands, server.DiscordGuildId);
}
}
public async Task <IReadOnlyList <User> > GetOutdatedUsers(DateTime timeLastUpdated)
{
await using var db = new FMBotDbContext(ConfigData.Data.Database.ConnectionString);
return(await db.Users
.AsQueryable()
.Where(f => f.LastIndexed != null &&
f.LastUpdated != null &&
f.LastUpdated <= timeLastUpdated)
.ToListAsync());
}
/// <inheritdoc />
public async Task LoadAllPrefixes()
{
using var db = new FMBotDbContext(ConfigData.Data.Database.ConnectionString);
var servers = await db.Guilds.Where(w => w.Prefix != null).ToListAsync();
foreach (var server in servers)
{
StorePrefix(server.Prefix, server.DiscordGuildId);
}
}
public async Task <IReadOnlyList <User> > GetUsersToIndex(IReadOnlyCollection <IGuildUser> guildUsers)
{
var userIds = guildUsers.Select(s => s.Id).ToList();
await using var db = new FMBotDbContext(ConfigData.Data.Database.ConnectionString);
return(await db.Users
.Include(i => i.Artists)
.Where(w => userIds.Contains(w.DiscordUserId) &&
(w.LastIndexed == null || w.LastUpdated == null))
.ToListAsync());
}
public async Task <int> GetIndexedUsersCount(IReadOnlyCollection <IGuildUser> guildUsers)
{
var userIds = guildUsers.Select(s => s.Id).ToList();
await using var db = new FMBotDbContext(ConfigData.Data.Database.ConnectionString);
return(await db.Users
.AsQueryable()
.Where(w => userIds.Contains(w.DiscordUserId) &&
w.LastIndexed != null)
.CountAsync());
}
// Remove user
public async Task DeleteUser(int userID)
{
await using var db = new FMBotDbContext(ConfigData.Data.Database.ConnectionString);
var user = await db.Users
.AsQueryable()
.FirstOrDefaultAsync(f => f.UserId == userID);
db.Users.Remove(user);
await db.SaveChangesAsync();
}
public async Task <int> GetArtistListenerCountForServer(IReadOnlyList <User> guildUsers, string artistName)
{
var userIds = guildUsers.Select(s => s.UserId);
await using var db = new FMBotDbContext(ConfigData.Data.Database.ConnectionString);
return(await db.UserArtists
.AsQueryable()
.Where(w => w.Name.ToLower() == artistName.ToLower() &&
userIds.Contains(w.UserId))
.CountAsync());
}
public static async Task <UserSettingsModel> GetUser(
string[] extraOptions,
string username,
ICommandContext context)
{
var settingsModel = new UserSettingsModel
{
DifferentUser = false,
UserNameLastFm = username,
DiscordUserId = context.User.Id,
};
foreach (var extraOption in extraOptions)
{
if (!extraOption.Contains("<@") && extraOption.Length != 18)
{
continue;
}
var id = extraOption.Trim('@', '!', '<', '>');
if (!ulong.TryParse(id, out var discordUserId))
{
continue;
}
if (context.Guild == null)
{
continue;
}
var guildUser = await context.Guild.GetUserAsync(discordUserId);
if (guildUser == null)
{
continue;
}
await using var db = new FMBotDbContext(ConfigData.Data.Database.ConnectionString);
var user = await db.Users
.AsQueryable()
.FirstOrDefaultAsync(f => f.DiscordUserId == discordUserId);
if (user == null)
{
continue;
}
settingsModel.DifferentUser = true;
settingsModel.DiscordUserId = discordUserId;
settingsModel.UserNameLastFm = user.UserNameLastFM;
}
return(settingsModel);
}
public async Task RemoveAllFriendsAsync(int userId)
{
await using var db = new FMBotDbContext(ConfigData.Data.Database.ConnectionString);
var friends = db.Friends
.AsQueryable()
.Where(f => f.UserId == userId).ToList();
if (friends.Count > 0)
{
db.Friends.RemoveRange(friends);
await db.SaveChangesAsync();
}
}
public async Task <IReadOnlyList <User> > GetUsersToIndex(IReadOnlyCollection <IGuildUser> guildUsers)
{
var userIds = guildUsers.Select(s => s.Id).ToList();
var tooRecent = DateTime.UtcNow.Add(-Constants.GuildIndexCooldown);
await using var db = new FMBotDbContext(ConfigData.Data.Database.ConnectionString);
return(await db.Users
.Include(i => i.Artists)
.Where(w => userIds.Contains(w.DiscordUserId) &&
(w.LastIndexed == null || w.LastIndexed <= tooRecent))
.ToListAsync());
}
public async Task <int> GetWeekArtistPlaycountAsync(int userId, string artistName)
{
var now = DateTime.UtcNow;
var minDate = DateTime.UtcNow.AddDays(-7);
await using var db = new FMBotDbContext(ConfigData.Data.Database.ConnectionString);
return(await db.UserPlays
.AsQueryable()
.CountAsync(a => a.TimePlayed.Date <= now.Date &&
a.TimePlayed.Date > minDate.Date &&
a.ArtistName.ToLower() == artistName.ToLower() &&
a.UserId == userId));
}
