public async Task AddedBy(IUser user)
{
if (user is null)
{
user = Context.User;
}
// Get all species belonging to this user.
List <Species> species_list = new List <Species>();
using (SQLiteCommand cmd = new SQLiteCommand("SELECT * FROM Species WHERE owner = $owner OR user_id = $user_id;")) {
cmd.Parameters.AddWithValue("$owner", user.Username);
cmd.Parameters.AddWithValue("$user_id", user.Id);
using (DataTable rows = await Database.GetRowsAsync(cmd)) {
foreach (DataRow row in rows.Rows)
{
species_list.Add(await SpeciesUtils.SpeciesFromDataRow(row));
}
species_list.Sort((lhs, rhs) => lhs.ShortName.CompareTo(rhs.ShortName));
}
}
// Display the species belonging to this user.
await _displaySpeciesAddedBy(user.Username, user.GetAvatarUrl(size: 32), species_list);
}
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 <Species> born_species = new List <Species>();
using (SQLiteCommand cmd = new SQLiteCommand("SELECT * FROM Species WHERE timestamp > $start_ts AND timestamp <= $end_ts;")) {
cmd.Parameters.AddWithValue("$start_ts", period.GetStartTimestamp());
cmd.Parameters.AddWithValue("$end_ts", period.GetEndTimestamp());
using (DataTable table = await Database.GetRowsAsync(cmd))
foreach (DataRow row in table.Rows)
{
born_species.Add(await SpeciesUtils.SpeciesFromDataRow(row));
}
}
// Get all species that went extinct during this time period.
List <Species> died_species = new List <Species>();
using (SQLiteCommand cmd = new SQLiteCommand("SELECT * FROM Species WHERE id IN (SELECT species_id FROM Extinctions WHERE timestamp > $start_ts AND timestamp <= $end_ts);")) {
cmd.Parameters.AddWithValue("$start_ts", period.GetStartTimestamp());
cmd.Parameters.AddWithValue("$end_ts", period.GetEndTimestamp());
using (DataTable table = await Database.GetRowsAsync(cmd))
foreach (DataRow row in table.Rows)
{
died_species.Add(await SpeciesUtils.SpeciesFromDataRow(row));
}
}
// 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());
}
public async Task <SearchQueryResult> GetResultAsync()
{
// Build up a list of conditions to query for.
List <string> conditions = new List <string>();
// Create a condition for each basic search term.
for (int i = 0; i < searchTerms.Count(); ++i)
{
conditions.Add(string.Format("(name LIKE {0} OR description LIKE {0} OR common_name LIKE {0})", string.Format("$term{0}", i)));
}
// Build the SQL query.
string sql_command_str;
if (conditions.Count > 0)
{
sql_command_str = string.Format("SELECT * FROM Species WHERE {0};", string.Join(" AND ", conditions));
}
else
{
sql_command_str = "SELECT * FROM Species;";
}
List <Species> matches = new List <Species>();
using (SQLiteCommand cmd = new SQLiteCommand(sql_command_str)) {
// Replace all parameters with their respective terms.
for (int i = 0; i < searchTerms.Count(); ++i)
{
string term = "%" + searchTerms[i].Trim() + "%";
cmd.Parameters.AddWithValue(string.Format("$term{0}", i), term);
}
// Execute the query, and add all matching species to the list.
using (DataTable rows = await Database.GetRowsAsync(cmd))
foreach (DataRow row in rows.Rows)
{
matches.Add(await SpeciesUtils.SpeciesFromDataRow(row));
}
}
// Apply any post-match modifiers (e.g. groupings), and return the result.
SearchQueryResult result = await ApplyPostMatchModifiersAsync(matches);
// Return the result.
return(result);
}
public async Task Favs()
{
// Get all species fav'd by this user.
List <string> lines = new List <string>();
using (SQLiteCommand cmd = new SQLiteCommand("SELECT * FROM Species WHERE id IN (SELECT species_id FROM Favorites WHERE user_id = $user_id);")) {
cmd.Parameters.AddWithValue("$user_id", Context.User.Id);
using (DataTable rows = await Database.GetRowsAsync(cmd)) {
foreach (DataRow row in rows.Rows)
{
Species sp = await SpeciesUtils.SpeciesFromDataRow(row);
long fav_count = 0;
// Get the number of times this species has been favorited.
using (SQLiteCommand cmd2 = new SQLiteCommand("SELECT COUNT(*) FROM Favorites WHERE species_id = $species_id;")) {
cmd2.Parameters.AddWithValue("$species_id", sp.Id);
fav_count = await Database.GetScalar <long>(cmd2);
}
lines.Add(sp.ShortName + (fav_count > 1 ? string.Format(" (+{0})", fav_count) : ""));
}
lines.Sort();
}
}
// Display the species list.
if (lines.Count() <= 0)
{
await BotUtils.ReplyAsync_Info(Context, string.Format("**{0}** has not favorited any species.", Context.User.Username));
}
else
{
Bot.PaginatedMessageBuilder embed = new Bot.PaginatedMessageBuilder(EmbedUtils.LinesToEmbedPages(lines));
embed.SetTitle(string.Format("⭐ Species favorited by {0} ({1})", Context.User.Username, lines.Count()));
embed.SetThumbnailUrl(Context.User.GetAvatarUrl(size: 32));
embed.AddPageNumbers();
await Bot.DiscordUtils.SendMessageAsync(Context, embed.Build());
}
}
public async Task Random()
{
// Get a random species from the database.
using (SQLiteCommand cmd = new SQLiteCommand("SELECT * FROM Species WHERE id NOT IN (SELECT species_id FROM Extinctions) ORDER BY RANDOM() LIMIT 1;")) {
DataRow row = await Database.GetRowAsync(cmd);
if (row is null)
{
await BotUtils.ReplyAsync_Info(Context, "There are currently no extant species.");
}
else
{
await SpeciesCommands.ShowSpeciesInfoAsync(Context, await SpeciesUtils.SpeciesFromDataRow(row));
}
}
}
public async Task Extinct()
{
List <Species> sp_list = new List <Species>();
using (SQLiteCommand cmd = new SQLiteCommand("SELECT * FROM Species WHERE id IN (SELECT species_id FROM Extinctions);"))
using (DataTable rows = await Database.GetRowsAsync(cmd))
foreach (DataRow row in rows.Rows)
{
sp_list.Add(await SpeciesUtils.SpeciesFromDataRow(row));
}
sp_list.Sort((lhs, rhs) => lhs.ShortName.CompareTo(rhs.ShortName));
Bot.PaginatedMessageBuilder embed = new Bot.PaginatedMessageBuilder();
embed.AddPages(EmbedUtils.SpeciesListToEmbedPages(sp_list, fieldName: string.Format("Extinct species ({0})", sp_list.Count()), flags: EmbedPagesFlag.None));
await Bot.DiscordUtils.SendMessageAsync(Context, embed.Build(), "There are currently no extinct species.");
}
// Checks for species with no predators (that aren't themselves predators)
private async Task <string[]> _getNoPredatorIdeasAsync()
{
List <string> ideas = new List <string>();
string query = @"SELECT * FROM Species WHERE
id NOT IN (SELECT species_id FROM Extinctions) AND
id NOT IN (SELECT eats_id FROM Predates) AND
id NOT IN (SELECT species_id FROM Predates)" ;
using (SQLiteCommand cmd = new SQLiteCommand(query))
using (DataTable table = await Database.GetRowsAsync(cmd))
foreach (DataRow row in table.Rows)
{
Species species = await SpeciesUtils.SpeciesFromDataRow(row);
ideas.Add(string.Format("There are no species that feed on **{0}**. Why not make one?", species.ShortName));
}
return(ideas.ToArray());
}
public async Task ListSpecies()
{
// Get all species.
List <Species> species = new List <Species>();
using (SQLiteCommand cmd = new SQLiteCommand("SELECT * FROM Species;"))
using (DataTable table = await Database.GetRowsAsync(cmd))
foreach (DataRow row in table.Rows)
{
species.Add(await SpeciesUtils.SpeciesFromDataRow(row));
}
// If there are no species, state so.
if (species.Count <= 0)
{
await BotUtils.ReplyAsync_Info(Context, "No species have been added yet.");
return;
}
// Create embed pages.
species.Sort((lhs, rhs) => lhs.ShortName.CompareTo(rhs.ShortName));
List <EmbedBuilder> pages = EmbedUtils.SpeciesListToEmbedPages(species, fieldName: string.Format("All species ({0}):", species.Count()));
// Send the result.
Bot.PaginatedMessage reply = new Bot.PaginatedMessage();
foreach (EmbedBuilder page in pages)
{
reply.Pages.Add(page.Build());
}
await Bot.DiscordUtils.SendMessageAsync(Context, reply);
}
public static async Task <Bot.PaginatedMessageBuilder> BuildRecentEventsEmbedAsync(long startTimestamp, long endTimestamp, TimeUnits timeUnit = 0)
{
// Get all species created within the given timespan.
List <Species> new_species = new List <Species>();
TimeAmount time_amount = new TimeAmount(endTimestamp - startTimestamp, TimeUnits.Seconds);
if (timeUnit != 0)
{
time_amount = time_amount.ConvertTo(timeUnit);
}
else
{
time_amount = time_amount.Reduce();
}
using (SQLiteCommand cmd = new SQLiteCommand("SELECT * FROM Species WHERE timestamp >= $start_ts AND timestamp < $end_ts")) {
cmd.Parameters.AddWithValue("$start_ts", startTimestamp);
cmd.Parameters.AddWithValue("$end_ts", endTimestamp);
using (DataTable table = await Database.GetRowsAsync(cmd))
foreach (DataRow row in table.Rows)
{
new_species.Add(await SpeciesUtils.SpeciesFromDataRow(row));
}
}
new_species.Sort();
// Get all extinctions that occurred recently.
List <Species> extinct_species = new List <Species>();
using (SQLiteCommand cmd = new SQLiteCommand("SELECT * FROM Extinctions WHERE timestamp >= $start_ts AND timestamp < $end_ts")) {
cmd.Parameters.AddWithValue("$start_ts", startTimestamp);
cmd.Parameters.AddWithValue("$end_ts", endTimestamp);
using (DataTable table = await Database.GetRowsAsync(cmd))
foreach (DataRow row in table.Rows)
{
extinct_species.Add(await BotUtils.GetSpeciesFromDb(row.Field <long>("species_id")));
}
}
extinct_species.Sort();
// Build embed.
Bot.PaginatedMessageBuilder embed = new Bot.PaginatedMessageBuilder();
List <EmbedBuilder> pages = new List <EmbedBuilder>();
List <string> field_lines = new List <string>();
if (new_species.Count() > 0)
{
foreach (Species sp in new_species)
{
field_lines.Add(sp.FullName);
}
EmbedUtils.AddLongFieldToEmbedPages(pages, field_lines, fieldName: string.Format("New species ({0})", new_species.Count()));
field_lines.Clear();
}
if (extinct_species.Count() > 0)
{
foreach (Species sp in extinct_species)
{
field_lines.Add(sp.FullName);
}
EmbedUtils.AddLongFieldToEmbedPages(pages, field_lines, fieldName: string.Format("Extinctions ({0})", extinct_species.Count()));
field_lines.Clear();
}
embed.AddPages(pages);
embed.SetTitle(string.Format("Recent events ({0})", time_amount.ToString()));
embed.SetFooter(string.Empty); // remove page numbers added automatically
embed.AddPageNumbers();
if (embed.FieldCount <= 0)
{
embed.SetDescription("No events");
}
return(embed);
}
// Checks for species with empty lineage
private async Task <string[]> _getEmptyLineageIdeasAsync()
{
List <string> ideas = new List <string>();
using (SQLiteCommand cmd = new SQLiteCommand("SELECT * FROM Species WHERE id NOT IN (SELECT species_id FROM Ancestors) AND id NOT IN (SELECT ancestor_id FROM Ancestors) AND id NOT IN (SELECT species_id FROM Extinctions);"))
using (DataTable table = await Database.GetRowsAsync(cmd))
foreach (DataRow row in table.Rows)
{
ideas.Add(string.Format("Species **{0}** does not have any descendants. Why not derive one?", (await SpeciesUtils.SpeciesFromDataRow(row)).ShortName));
}
return(ideas.ToArray());
}
