public async Task <long> GetTimesUnlockedAsync(Trophy trophy)
{
using (SQLiteCommand cmd = new SQLiteCommand("SELECT COUNT(*) FROM Trophies WHERE trophy_name=$trophy_name;")) {
cmd.Parameters.AddWithValue("$trophy_name", trophy.GetIdentifier());
return(await Database.GetScalar <long>(cmd));
}
}
public async Task UnlockAsync(ulong userId, Trophy trophy)
{
using (SQLiteCommand cmd = new SQLiteCommand("INSERT OR IGNORE INTO Trophies(user_id, trophy_name, timestamp) VALUES($user_id, $trophy_name, $timestamp);")) {
cmd.Parameters.AddWithValue("$user_id", userId);
cmd.Parameters.AddWithValue("$trophy_name", trophy.GetIdentifier());
cmd.Parameters.AddWithValue("$timestamp", DateTimeOffset.UtcNow.ToUnixTimeSeconds());
await Database.ExecuteNonQuery(cmd);
}
}
private async Task _popTrophyAsync(ScannerQueueItem item, Trophy trophy)
{
EmbedBuilder embed = new EmbedBuilder();
embed.WithTitle(string.Format("🏆 Trophy unlocked!"));
embed.WithDescription(string.Format("Congratulations {0}! You've earned the **{1}** trophy.", (await item.Context.Guild.GetUserAsync(item.UserId)).Mention, trophy.GetName()));
embed.WithFooter(trophy.GetDescription());
embed.WithColor(new Color(255, 204, 77));
await item.Context.Channel.SendMessageAsync("", false, embed.Build());
}
public async Task <double> GetCompletionRateAsync(Trophy trophy)
{
// The completion rate is determined from the number of users who have earned the trophy and the number of users who have submitted species.
long times_unlocked = await GetTimesUnlockedAsync(trophy);
long total_users = 0;
using (SQLiteCommand cmd = new SQLiteCommand("SELECT COUNT(*) FROM (SELECT user_id FROM Species GROUP BY user_id)"))
total_users = await Database.GetScalar <long>(cmd);
return((total_users <= 0) ? 0.0 : (100.0 * times_unlocked / total_users));
}
public async Task <TrophyUser[]> GetUsersUnlockedAsync(Trophy trophy)
{
List <TrophyUser> user_ids = new List <TrophyUser>();
using (SQLiteCommand cmd = new SQLiteCommand("SELECT user_id, timestamp FROM Trophies WHERE trophy_name = $trophy_name")) {
cmd.Parameters.AddWithValue("$trophy_name", trophy.GetIdentifier());
using (DataTable rows = await Database.GetRowsAsync(cmd))
foreach (DataRow row in rows.Rows)
{
user_ids.Add(new TrophyUser((ulong)row.Field <long>("user_id"), row.Field <long>("timestamp")));
}
}
return(user_ids.ToArray());
}
public async Task <double> GetUserCompletionRateAsync(ulong userId, bool includeOneTimeTrophies = false)
{
UnlockedTrophyInfo[] unlocked = await GetUnlockedTrophiesAsync(userId);
int unlocked_count = unlocked
.Where(x => {
if (includeOneTimeTrophies)
{
return(true);
}
Trophy t = GetTrophyByIdentifierAsync(x.identifier).Result;
return(t != null && !t.Flags.HasFlag(TrophyFlags.OneTime));
})
.Count();
int trophy_count = (await GetTrophiesAsync()).Where(x => includeOneTimeTrophies || !x.Flags.HasFlag(TrophyFlags.OneTime)).Count();
return(trophy_count <= 0 ? 0.0 : (100.0 * unlocked_count / trophy_count));
}
