public static async Task <IEnumerable <ITaxon> > GetSubtaxaAsync(this SQLiteDatabase database, ITaxon taxon)
{
List <ITaxon> result = new List <ITaxon>();
string tableName = GetTableNameForRank(taxon.GetChildRank());
string parentColumnName = GetFieldNameForRank(taxon.GetRank());
if (string.IsNullOrEmpty(tableName) || string.IsNullOrEmpty(parentColumnName) || !taxon.Id.HasValue)
{
return(Enumerable.Empty <ITaxon>());
}
string query = "SELECT * FROM {0} WHERE {1} = $parent_id";
using (SQLiteCommand cmd = new SQLiteCommand(string.Format(query, tableName, parentColumnName))) {
cmd.Parameters.AddWithValue("$parent_id", taxon.Id);
foreach (DataRow row in await database.GetRowsAsync(cmd))
{
result.Add(await database.CreateTaxonFromDataRowAsync(row, taxon.GetChildRank()));
}
}
// Sort taxa alphabetically by name.
result.Sort((lhs, rhs) => lhs.Name.CompareTo(rhs.Name));
return(result);
}
public static async Task DeleteTaxonAsync(this SQLiteDatabase database, ITaxon taxon)
{
string tableName = GetTableNameForRank(taxon.GetRank());
string subtaxaCommonName = GetTableNameForRank(taxon.GetChildRank());
string subtaxaColumnName = GetFieldNameForRank(taxon.GetRank());
// Set to NULL any references subtaxa have to this taxon.
// Note that this can also happen automatically if the foreign key is set up correctly when creating the database.
if (!string.IsNullOrEmpty(tableName) && !string.IsNullOrEmpty(subtaxaColumnName))
{
using (SQLiteCommand cmd = new SQLiteCommand(string.Format("UPDATE {0} SET {1} = NULL WHERE {1} = $id", subtaxaCommonName, subtaxaColumnName))) {
cmd.Parameters.AddWithValue("$id", taxon.Id);
await database.ExecuteNonQueryAsync(cmd);
}
}
// Delete the taxon.
if (!string.IsNullOrEmpty(tableName))
{
using (SQLiteCommand cmd = new SQLiteCommand(string.Format("DELETE FROM {0} WHERE id = $id", tableName))) {
cmd.Parameters.AddWithValue("$id", taxon.Id);
await database.ExecuteNonQueryAsync(cmd);
}
}
}
public async Task <IPaginatedMessage> BuildTaxonMessageAsync(ITaxon taxon)
{
if (!taxon.IsValid())
{
return(null);
}
List <string> subItems = new List <string>();
if (taxon.Rank.Type == TaxonRankType.Species)
{
ISpecies species = await Db.GetSpeciesAsync(taxon.Id);
return(await BuildSpeciesMessageAsync(species));
}
else if (taxon.Rank.Type == TaxonRankType.Genus)
{
// For genera, get all species underneath it.
// This will let us check if the species is extinct, and cross it out if that's the case.
List <ISpecies> speciesList = new List <ISpecies>();
foreach (ITaxon subtaxon in await Db.GetSubtaxaAsync(taxon))
{
speciesList.Add(await Db.GetSpeciesAsync(subtaxon.Id));
}
speciesList.Sort((lhs, rhs) => TaxonFormatter.GetString(lhs, false).CompareTo(TaxonFormatter.GetString(rhs, false)));
foreach (ISpecies species in speciesList.Where(s => s.IsValid()))
{
subItems.Add(TaxonFormatter.GetString(species));
}
}
else
{
// Get all subtaxa under this taxon.
IEnumerable <ITaxon> subtaxa = await Db.GetSubtaxaAsync(taxon);
// Add all subtaxa to the list.
foreach (ITaxon subtaxon in subtaxa)
{
if (subtaxon.Rank.Type == TaxonRankType.Species)
{
// Do not attempt to count sub-taxa for species.
subItems.Add(subtaxon.GetName());
}
else
{
// Count the number of species under this taxon.
// Taxa with no species under them will not be displayed.
long speciesCount = await Db.GetSpeciesCountAsync(subtaxon);
if (speciesCount > 0)
{
// Count the sub-taxa under this taxon.
long subtaxaCount = (await Db.GetSubtaxaAsync(subtaxon)).Count();
// Add the taxon to the list.
if (subtaxaCount > 0)
{
subItems.Add(string.Format("{0} ({1})", subtaxon.GetName(), subtaxaCount));
}
}
}
}
}
// Generate embed pages.
string title = taxon.CommonNames.Count() <= 0 ? taxon.GetName() : string.Format("{0} ({1})", taxon.GetName(), taxon.GetCommonName());
string fieldTitle = string.Format("{0} in this {1} ({2}):", taxon.GetChildRank().GetName(true).ToTitle(), taxon.GetRank().GetName().ToLowerInvariant(), subItems.Count());
string thumbnailUrl = taxon.GetPictureUrl();
StringBuilder description = new StringBuilder();
description.AppendLine(taxon.GetDescriptionOrDefault());
if (subItems.Count() <= 0)
{
description.AppendLine();
description.AppendLine(string.Format("This {0} contains no {1}.", taxon.GetRank().GetName(), taxon.GetChildRank().GetName(true)));
}
List <Discord.Messaging.IEmbed> pages = new List <Discord.Messaging.IEmbed>(EmbedUtilities.CreateEmbedPages(fieldTitle, subItems, options: EmbedPaginationOptions.AddPageNumbers));
if (!pages.Any())
{
pages.Add(new Discord.Messaging.Embed());
}
IPaginatedMessage paginatedMessage = new PaginatedMessage(pages);
foreach (Discord.Messaging.IEmbed page in paginatedMessage.Select(m => m.Embed))
{
page.Title = title;
page.ThumbnailUrl = thumbnailUrl;
page.Description = description.ToString();
if (subItems.Count() > 0 && taxon.GetRank() != TaxonRankType.Genus)
{
page.Footer += string.Format(" — Empty {0} are not listed.", taxon.GetChildRank().GetName(true));
}
}
return(paginatedMessage);
}