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; } }