public static async Task <string> TimestampToDateStringAsync(long timestamp, IOfcBotContext context, TimestampToDateStringFormat format = TimestampToDateStringFormat.Default)
{
if (context.Configuration.GenerationsEnabled)
{
IGeneration gen = await context.Database.GetGenerationByDateAsync(DateUtilities.GetDateFromTimestamp(timestamp));
return(gen is null ? "Gen ???" : gen.Name);
}
return(DateUtils.TimestampToDateString(timestamp, format));
}
public async Task GetPeriod(Period period)
{
string embed_title = string.Format("📅 {0} ({1}—{2}, {3})", period.GetName(), period.GetStartTimestampString(), period.GetEndTimestampString(), period.GetHowLongAgoString());
// Get all species that were born during this time period.
List <ISpecies> born_species = new List <ISpecies>();
born_species.AddRange(await Db.GetSpeciesAsync(DateUtilities.GetDateFromTimestamp(period.GetStartTimestamp()), DateUtilities.GetDateFromTimestamp(period.GetEndTimestamp())));
// Get all species that went extinct during this time period.
List <ISpecies> died_species = new List <ISpecies>();
died_species.AddRange(await Db.GetExtinctSpeciesAsync(DateUtilities.GetDateFromTimestamp(period.GetStartTimestamp()), DateUtilities.GetDateFromTimestamp(period.GetEndTimestamp())));
// Create the embed pages.
List <EmbedBuilder> pages = new List <EmbedBuilder>();
// Create the first page, showing the period name and description.
EmbedBuilder page_1 = new EmbedBuilder();
page_1.WithTitle(embed_title);
page_1.WithDescription(period.GetDescriptionOrDefault());
page_1.AddField("New species", born_species.Count(), inline: true);
page_1.AddField("Extinctions", died_species.Count(), inline: true);
pages.Add(page_1);
// #todo Add additional pages listing species names?
//CommandUtils.PaginatedMessage message = new CommandUtils.PaginatedMessage();
//for (int i = 0; i < pages.Count(); ++i)
// message.pages.Add(pages[i].Build());
//await CommandUtils.ReplyAsync_SendPaginatedMessage(Context, message);
await ReplyAsync("", false, page_1.Build());
}
// Private members
private static IGeneration CreateGenerationFromDataRow(DataRow row)
{
if (row is null)
{
return(null);
}
IGeneration generation = new Generation {
Id = row.Field <long>("id"),
Number = int.Parse(Regex.Match(row.Field <string>("name"), @"\d+$").Value)
};
long minTimestamp = DateUtilities.GetMinTimestamp();
long maxTimestamp = DateUtilities.GetMaxTimestamp();
long startTimestamp = DateUtilities.ParseTimestamp(row.Field <string>("start_ts"));
long endTimestamp = DateUtilities.ParseTimestamp(row.Field <string>("end_ts"));
generation.StartDate = DateUtilities.GetDateFromTimestamp(Math.Min(Math.Max(startTimestamp, minTimestamp), maxTimestamp));
generation.EndDate = DateUtilities.GetDateFromTimestamp(Math.Min(Math.Max(endTimestamp, minTimestamp), maxTimestamp));
return(generation);
}
public static async Task <bool> RevertGenerationAsync(this SQLiteDatabase database)
{
IGeneration generation = await database.GetCurrentGenerationAsync();
if (generation.Number <= 1)
{
return(false);
}
// Delete the current generation.
await database.DeleteGenerationAsync(generation);
generation = await database.GetCurrentGenerationAsync();
// Update the end timestamp of the previous generation so that it is now the current generation.
generation.EndDate = DateUtilities.GetDateFromTimestamp(DateUtilities.GetMaxTimestamp());
await database.UpdateGenerationAsync(generation);
return(true);
}
public static async Task <IEnumerable <IUnlockedTrophyInfo> > GetUnlockedTrophiesAsync(this SQLiteDatabase database, ICreator creator, IEnumerable <ITrophy> trophyList)
{
List <IUnlockedTrophyInfo> unlocked = new List <IUnlockedTrophyInfo>();
using (SQLiteCommand cmd = new SQLiteCommand("SELECT * FROM Trophies WHERE user_id = $user_id ")) {
cmd.Parameters.AddWithValue("$user_id", creator.UserId);
foreach (DataRow row in await database.GetRowsAsync(cmd))
{
string trophyName = row.Field <string>("trophy_name");
long timesUnlocked = 0;
using (SQLiteCommand cmd2 = new SQLiteCommand("SELECT COUNT(*) FROM Trophies WHERE trophy_name = $trophy_name ")) {
cmd2.Parameters.AddWithValue("$trophy_name", trophyName);
timesUnlocked = await database.GetScalarAsync <long>(cmd2);
}
ITrophy trophy = trophyList
.Where(t => t.Identifier.Equals(trophyName, StringComparison.OrdinalIgnoreCase))
.FirstOrDefault();
if (trophy != null)
{
IUnlockedTrophyInfo info = new UnlockedTrophyInfo(creator, trophy)
{
TimesUnlocked = (int)timesUnlocked,
DateUnlocked = DateUtilities.GetDateFromTimestamp(row.Field <long>("timestamp"))
};
unlocked.Add(info);
}
}
}
return(unlocked);
}
public static async Task <IEnumerable <IUnlockedTrophyInfo> > GetCreatorsWithTrophyAsync(this SQLiteDatabase database, ITrophy trophy)
{
List <IUnlockedTrophyInfo> results = new List <IUnlockedTrophyInfo>();
using (SQLiteCommand cmd = new SQLiteCommand("SELECT user_id, timestamp FROM Trophies WHERE trophy_name = $trophy_name")) {
cmd.Parameters.AddWithValue("$trophy_name", trophy.Identifier);
IEnumerable <DataRow> rows = await database.GetRowsAsync(cmd);
foreach (DataRow row in rows)
{
ICreator creator = new Creator((ulong)row.Field <long>("user_id"), string.Empty);
DateTimeOffset dateEarned = DateUtilities.GetDateFromTimestamp(row.Field <long>("timestamp"));
results.Add(new UnlockedTrophyInfo(creator, trophy)
{
DateUnlocked = dateEarned,
TimesUnlocked = rows.Count()
});
}
}
return(results);
}
public static async Task <ICreator> GetCreatorAsync(this SQLiteDatabase database, ICreator creator, UserInfoQueryFlags flags = UserInfoQueryFlags.Default)
{
ICreator result = null;
// Note that we may have a null username or a null user ID.
// At least one of them has to be non-null.
if (!string.IsNullOrEmpty(creator.Name) || creator.UserId.HasValue)
{
// If we've been given a user ID, get all species records where that user is the owner.
// If we've just been given a username, we'll go by username instead.
string query;
if (flags.HasFlag(UserInfoQueryFlags.MatchEither))
{
query = "SELECT owner, user_id, timestamp FROM Species WHERE user_id = $user_id OR owner = $owner";
}
else
{
if (creator.UserId.HasValue)
{
query = "SELECT owner, user_id, timestamp FROM Species WHERE user_id = $user_id";
}
else
{
query = "SELECT owner, user_id, timestamp FROM Species WHERE owner = $owner COLLATE NOCASE";
}
}
using (SQLiteCommand cmd = new SQLiteCommand(query)) {
cmd.Parameters.AddWithValue("$user_id", creator.UserId);
cmd.Parameters.AddWithValue("$owner", creator.Name);
IEnumerable <DataRow> rows = await database.GetRowsAsync(cmd);
if (rows.Count() > 0)
{
ulong?userId = rows.Select(row => row.IsNull("user_id") ? null : (ulong?)row.Field <long>("user_id"))
.FirstOrDefault(id => id.HasValue);
string username = rows.Select(row => row.IsNull("owner") ? string.Empty : row.Field <string>("owner"))
.FirstOrDefault(name => !string.IsNullOrEmpty(name));
if (userId.HasValue || !string.IsNullOrEmpty(username))
{
long firstSpeciesTimestamp = rows.Select(row => (long)row.Field <decimal>("timestamp")).OrderBy(timestamp => timestamp).FirstOrDefault();
long lastSpeciesTimestamp = rows.Select(rowx => (long)rowx.Field <decimal>("timestamp")).OrderByDescending(timestamp => timestamp).FirstOrDefault();
result = new Creator(userId, username)
{
SpeciesCount = rows.Count(),
FirstSpeciesDate = firstSpeciesTimestamp > 0 ? DateUtilities.GetDateFromTimestamp(firstSpeciesTimestamp) : default(DateTimeOffset?),
LastSpeciesDate = lastSpeciesTimestamp > 0 ? DateUtilities.GetDateFromTimestamp(lastSpeciesTimestamp) : default(DateTimeOffset?)
};
}
}
}
}
return(result);
}