async Task <string> LoadPokemonNameByIdentifierAsync(string identifier, int language, CancellationToken token) { try { string query = String.Format(@" SELECT ps.id, psn.name FROM pokemon_species ps LEFT JOIN (SELECT e.pokemon_species_id AS id, COALESCE(o.name, e.name) AS name FROM pokemon_species_names e LEFT OUTER JOIN pokemon_species_names o ON e.pokemon_species_id = o.pokemon_species_id AND o.local_language_id = {1} WHERE e.local_language_id = 9 GROUP BY e.pokemon_species_id) AS psn ON ps.id = psn.id WHERE ps.identifier = '{0}' ", identifier, language); IEnumerable <DbPokemonSpecies> pokemon = await _connection.QueryAsync <DbPokemonSpecies>(token, query, new object[0]).ConfigureAwait(false); DbPokemonSpecies species = pokemon.Single(); return(species.Name); } catch (Exception) { return(null); } }
public async Task <Species> LoadSpeciesAsync(int id, GameVersion version, int displayLanguage, CancellationToken token) { try { string query = "SELECT ps.id, psn.name, psn.genus, ps.gender_rate, ps.capture_rate, ps.base_happiness, ps.hatch_counter, ps.growth_rate_id FROM pokemon_v2_pokemonspecies AS ps\n" + "LEFT JOIN\n(SELECT def.pokemon_species_id AS id, IFNULL(curr.name, def.name) AS name, IFNULL(curr.genus, def.genus) AS genus FROM pokemon_v2_pokemonspeciesname def\n" + "LEFT JOIN pokemon_v2_pokemonspeciesname curr ON def.pokemon_species_id = curr.pokemon_species_id AND def.language_id = 9 AND curr.language_id = ?\n" + "GROUP BY def.pokemon_species_id)\nAS psn ON ps.id = psn.id\n" + "WHERE ps.id = ?"; IEnumerable <DbPokemonSpecies> list = await _connection.QueryAsync <DbPokemonSpecies>(token, query, new object[] { displayLanguage, id }); query = "SELECT p.id, pn.name FROM pokemon_v2_pokedex p\n" + "LEFT JOIN\n(SELECT e.pokedex_id AS id, COALESCE(o.name, e.name) AS name FROM pokemon_v2_pokedexdescription e\n" + "LEFT OUTER JOIN pokemon_v2_pokedexdescription o ON e.pokedex_id = o.pokedex_id and o.language_id = ?\n" + "WHERE e.language_id = 9\nGROUP BY e.pokedex_id)\nAS pn ON p.id = pn.id\n" + "LEFT JOIN pokemon_v2_pokedexversiongroup pvg ON p.id = pvg.pokedex_id\n" + "WHERE pvg.version_group_id = ?"; IEnumerable <DbPokedex> temp = await _connection.QueryAsync <DbPokedex>(token, query, new object[] { displayLanguage, version.VersionGroup }); query = "SELECT egg_group_id FROM pokemon_v2_pokemonegggroup WHERE pokemon_species_id = ?"; var eggGroups = new List <DbPokemonEggGroup>(await _connection.QueryAsync <DbPokemonEggGroup>(token, query, new object[] { id })); DbPokemonSpecies dbSpecies = list.First(); DbPokedex dex = temp.First(); var species = new Species { BaseHappiness = dbSpecies.BaseHappiness, CatchRate = dbSpecies.CaptureRate, HatchCounter = dbSpecies.HatchCounter, Id = dbSpecies.Id, Name = dbSpecies.Name }; species.GrowthRate = await GetGrowthRateAsync(dbSpecies.GrowthRateId); species.DexEntry = await LoadPokedexEntryAsync(dex.Id, dbSpecies.Id, displayLanguage, token); species.DexEntry.Name = dex.Name; species.EggGroup1 = await LoadEggGroupAsync(eggGroups[0].EggGroupId, displayLanguage, token); if (eggGroups.Count > 1) { species.EggGroup2 = await LoadEggGroupAsync(eggGroups[1].EggGroupId, displayLanguage, token); } //species.PossibleEvolutions = await LoadPossibleEvolutionsAsync(id, version, displayLanguage, token); species.GenderRate = CalculateGenderRate(dbSpecies.GenderRate); return(species); } catch (Exception) { return(null); } }
public async Task <Species> LoadSpeciesByIdAsync(int id, int versionGroupId, int language, CancellationToken token) { try { Task <List <EggGroup> > loadEggGroupsTask = Task.Factory.StartNew(() => { return(LoadEggGroupsAsync(id, language, token).Result); }, token); string query = String.Format(@" SELECT ps.id, psn.name, psn.genus, ps.gender_rate, ps.capture_rate, ps.base_happiness, ps.hatch_counter, ps.growth_rate_id, grn.name AS growth_rate, dexEntry.id AS dex_id, dexEntry.pokedex_number, dexEntry.name AS dex_name FROM pokemon_species AS ps LEFT JOIN (SELECT e.pokemon_species_id AS id, COALESCE(o.name, e.name) AS name, COALESCE(o.genus, e.genus) AS genus FROM pokemon_species_names e LEFT OUTER JOIN pokemon_species_names o ON e.pokemon_species_id = o.pokemon_species_id AND o.local_language_id = {0} WHERE e.local_language_id = 9 GROUP BY e.pokemon_species_id) AS psn ON ps.id = psn.id LEFT JOIN (SELECT dex.id, pdn.species_id, pdn.pokedex_number, pdn.name, pvg.version_group_id FROM pokemon_dex_numbers pdn LEFT JOIN pokedexes dex ON pdn.pokedex_id = dex.id LEFT JOIN pokedex_version_groups pvg ON dex.id = pvg.pokedex_id LEFT JOIN (SELECT e.pokedex_id AS id, COALESCE(o.name, e.name) AS name FROM pokedex_prose e LEFT OUTER JOIN pokedex_prose o ON e.pokedex_id = o.pokedex_id AND o.local_language_id = {0} WHERE e.local_language_id = 9 GROUP BY e.pokedex_id) AS pdn ON dex.id = pdn.id WHERE pvg.version_group_id = {2}) AS dexEntry ON ps.id = dexEntry.species_id LEFT JOIN (SELECT e.growth_rate_id AS id, COALESCE(o.name, e.name) AS name FROM growth_rate_prose e LEFT OUTER JOIN growth_rate_prose o ON e.growth_rate_id = o.growth_rate_id AND o.local_language_id = {0} WHERE e.local_language_id = 9 GROUP BY e.growth_rate_id) AS grn ON ps.growth_rate_id = grn.id WHERE ps.id = {1} ", language, id, versionGroupId); IEnumerable <DbPokemonSpecies> speciesList = await _connection.QueryAsync <DbPokemonSpecies>(token, query, new object[0]).ConfigureAwait(false); DbPokemonSpecies temp = speciesList.Single(); var species = new Species { BaseHappiness = temp.BaseHappiness, CatchRate = temp.CaptureRate, GenderRate = CalculateGenderRate(temp.GenderRate), GrowthRate = new GrowthRate { Id = temp.GrowthRateId, Name = temp.GrowthRate }, HatchCounter = temp.HatchCounter, Id = temp.Id, Name = temp.Name }; if (temp.PokedexNumber != null) { species.DexEntry = new PokedexEntry { DexNumber = (int)temp.PokedexNumber, Id = (int)temp.PokedexId, Name = temp.PokedexName } } ; await Task.WhenAll(new Task[] { loadEggGroupsTask }).ConfigureAwait(false); List <EggGroup> eggGroups = loadEggGroupsTask.Result; species.EggGroup1 = new EggGroup { Id = eggGroups[0].Id, Name = eggGroups[0].Name }; if (eggGroups.Count == 2) { species.EggGroup2 = new EggGroup { Id = eggGroups[1].Id, Name = eggGroups[1].Name } } ; return(species); } catch (Exception) { return(null); } }