public async Task<Ability> LoadAbilityAsync(int id, int versionGroup, int displayLanguage, CancellationToken token)
{
try
{
string query = String.Format(@"
SELECT ab.id, abn.name, abft.flavor_text, abp.short_effect, abp.effect, acp.effect_change
FROM abilities AS ab
LEFT JOIN (SELECT e.ability_id AS id, COALESCE(o.name, e.name) AS name
FROM ability_names e
LEFT OUTER JOIN ability_names o ON e.ability_id = o.ability_id AND o.local_language_id = {2}
WHERE e.local_language_id = 9
GROUP BY e.ability_id)
AS abn ON ab.id = abn.id
LEFT JOIN (SELECT e.ability_id AS id, COALESCE(o.flavor_text, e.flavor_text) AS flavor_text, e.version_group_id
FROM ability_flavor_text e
LEFT OUTER JOIN ability_flavor_text o ON e.ability_id = o.ability_id AND o.language_id = {2}
WHERE e.language_id = 9 AND e.version_group_id = 15
GROUP BY e.ability_id)
AS abft ON ab.id = abft.id
LEFT JOIN (SELECT e.ability_id AS id, COALESCE(o.short_effect, e.short_effect) AS short_effect, COALESCE(o.effect, e.effect) AS effect
FROM ability_prose e
LEFT OUTER JOIN ability_prose o ON e.ability_id = o.ability_id AND o.local_language_id = {2}
WHERE e.local_language_id = 9
GROUP BY e.ability_id)
AS abp ON ab.id = abp.id
LEFT JOIN ability_changelog ac ON ab.id = ac.ability_id AND ac.changed_in_version_group_id <= {1}
LEFT JOIN (SELECT e.ability_changelog_id AS id, COALESCE(o.effect, e.effect) AS effect_change
FROM ability_changelog_prose e
LEFT OUTER JOIN ability_changelog_prose o ON e.ability_changelog_id = o.ability_changelog_id AND o.local_language_id = {2}
WHERE e.local_language_id = 9
GROUP BY e.ability_changelog_id)
AS acp ON ac.id = acp.id
WHERE ab.is_main_series AND ab.id = {0}
ORDER BY ac.changed_in_version_group_id DESC
LIMIT 1
", id, versionGroup, displayLanguage);
IEnumerable<DbAbility> abilities = await _connection.QueryAsync<DbAbility>(token, query, new object[0]).ConfigureAwait(false);
DbAbility ability = abilities.FirstOrDefault();
if (ability == null)
return null;
var ab = new Ability
{
FlavorText = ability.FlavorText,
Id = ability.Id,
Name = ability.Name
};
ab.Description = await ProzessAbilityText(ability.ShortEffect, displayLanguage, token);
ab.Effect = await ProzessAbilityText(ability.Effect, displayLanguage, token);
ab.EffectChange = await ProzessAbilityText(ability.EffectChange, displayLanguage, token);
return ab;
}
catch (Exception)
{
return null;
}
}
public async Task<ObservableCollection<Ability>> LoadAbilitiesAsync(int displayLanguage, CancellationToken token)
{
try
{
string query = String.Format(@"
SELECT ab.id, abn.name, abft.flavor_text, abp.short_effect, abp.effect
FROM abilities AS ab
LEFT JOIN (SELECT e.ability_id AS id, COALESCE(o.name, e.name) AS name
FROM ability_names e
LEFT OUTER JOIN ability_names o ON e.ability_id = o.ability_id AND o.local_language_id = {0}
WHERE e.local_language_id = 9
GROUP BY e.ability_id)
AS abn ON ab.id = abn.id
LEFT JOIN (SELECT e.ability_id AS id, COALESCE(o.flavor_text, e.flavor_text) AS flavor_text, e.version_group_id
FROM ability_flavor_text e
LEFT OUTER JOIN ability_flavor_text o ON e.ability_id = o.ability_id AND o.language_id = {0}
WHERE e.language_id = 9 AND e.version_group_id = 15
GROUP BY e.ability_id)
AS abft ON ab.id = abft.id
LEFT JOIN (SELECT e.ability_id AS id, COALESCE(o.short_effect, e.short_effect) AS short_effect, COALESCE(o.effect, e.effect) AS effect
FROM ability_prose e
LEFT OUTER JOIN ability_prose o ON e.ability_id = o.ability_id AND o.local_language_id = {0}
WHERE e.local_language_id = 9
GROUP BY e.ability_id)
AS abp ON ab.id = abp.id
WHERE ab.is_main_series
", displayLanguage);
IEnumerable<DbAbility> abilities = await _connection.QueryAsync<DbAbility>(token, query, new object[0]).ConfigureAwait(false);
var result = new List<Ability>();
foreach (DbAbility ability in abilities)
{
var ab = new Ability
{
FlavorText = ability.FlavorText,
Id = ability.Id,
Name = ability.Name
};
ab.Description = ProzessAbilityText(ability.ShortEffect);
result.Add(ab);
}
return new ObservableCollection<Ability>(result);
}
catch (Exception)
{
return null;
}
}