def adjust_elo(winning_deck_id: int, losing_deck_id: int) -> None: if not losing_deck_id: return # Intentional draws do not affect Elo. winner = guarantee.exactly_one(person.load_people('p.id IN (SELECT person_id FROM deck WHERE id = {winning_deck_id})'.format(winning_deck_id=sqlescape(winning_deck_id)))) loser = guarantee.exactly_one(person.load_people('p.id IN (SELECT person_id FROM deck WHERE id = {losing_deck_id})'.format(losing_deck_id=sqlescape(losing_deck_id)))) adj = adjustment(winner.elo or STARTING_ELO, loser.elo or STARTING_ELO) sql = 'UPDATE person SET elo = IFNULL(elo, {starting_elo}) + %s WHERE id = %s'.format(starting_elo=sqlescape(STARTING_ELO)) db().execute(sql, [adj, winner.id]) db().execute(sql, [-adj, loser.id])
def load_archetype(archetype: Union[int, str], season_id: Optional[int] = None, tournament_only: bool = False) -> Archetype: try: archetype_id = int(archetype) except ValueError: name = titlecase.titlecase(archetype) name_without_dashes = name.replace('-', ' ') archetype_id = db().value( "SELECT id FROM archetype WHERE REPLACE(name, '-', ' ') = %s", [name_without_dashes]) if not archetype_id: raise DoesNotExistException( 'Did not find archetype with name of `{name}`'.format( name=name)) where = query.archetype_where(archetype_id) if tournament_only: where = '({where}) AND ({tournament_only_clause})'.format( where=where, tournament_only_clause=query.tournament_only_clause()) archetypes = load_archetypes(where=where, merge=True, season_id=season_id) arch = guarantee.exactly_one(archetypes, 'archetypes') if archetypes else Archetype() # Because load_archetypes loads the root archetype and all below merged the id and name might not be those of the root archetype. Overwrite. arch.id = int(archetype_id) arch.name = db().value('SELECT name FROM archetype WHERE id = %s', [archetype_id]) if len(archetypes) == 0: arch.decks = [] return arch
def load_archetype(archetype: Union[int, str], season_id: int = None) -> Archetype: try: archetype_id = int(archetype) except ValueError: name = titlecase.titlecase(archetype) name_without_dashes = name.replace('-', ' ') archetype_id = db().value( "SELECT id FROM archetype WHERE REPLACE(name, '-', ' ') = %s", [name_without_dashes]) if not archetype_id: raise DoesNotExistException( 'Did not find archetype with name of `{name}`'.format( name=name)) archetypes = load_archetypes( where= 'd.archetype_id IN (SELECT descendant FROM archetype_closure WHERE ancestor = {archetype_id})' .format(archetype_id=sqlescape(archetype_id)), merge=True, season_id=season_id) arch = guarantee.exactly_one(archetypes, 'archetypes') if archetypes else Archetype() # Because load_archetypes loads the root archetype and all below merged the id and name might not be those of the root archetype. Overwrite. arch.id = int(archetype_id) arch.name = db().value('SELECT name FROM archetype WHERE id = %s', [archetype_id]) if len(archetypes) == 0: arch.decks = [] arch.decks_tournament = arch.get('decks_tournament', []) return arch
def load_person(person: Union[int, str], season_id: Optional[int] = None) -> Person: try: person_id = int(person) username = "******".format(person=person) except ValueError: person_id = 0 username = sqlescape(person) return guarantee.exactly_one(load_people('p.id = {person_id} OR p.mtgo_username = {username} OR p.discord_id = {person_id}'.format(person_id=person_id, username=username), season_id=season_id))
def matchup(hero: Dict[str, str], enemy: Dict[str, str], season_id: int = None) -> Dict[str, Union[str, int, List[int]]]: where = 'TRUE' prefix = None args: List[Union[str, int]] = [] if season_id: where += ' AND (season.id = %s)' args.append(season_id) for criteria in [hero, enemy]: prefix = '' if prefix is None else 'o' if criteria.get('person_id'): where += f' AND ({prefix}d.person_id = %s)' args.append(criteria['person_id']) if criteria.get('archetype_id'): where += f' AND ({prefix}d.archetype_id IN (SELECT descendant FROM archetype_closure WHERE ancestor = %s))' args.append(criteria['archetype_id']) if criteria.get('card'): where += f' AND ({prefix}d.id IN (SELECT deck_id FROM deck_card WHERE card = %s))' args.append(criteria['card']) season_join = query.season_join() sql = f""" SELECT GROUP_CONCAT(DISTINCT d.id) AS hero_deck_ids, GROUP_CONCAT(DISTINCT od.id) AS enemy_deck_ids, GROUP_CONCAT(DISTINCT m.id) AS match_ids, IFNULL(SUM(CASE WHEN dm.games > odm.games THEN 1 ELSE 0 END), 0) AS wins, IFNULL(SUM(CASE WHEN dm.games = odm.games THEN 1 ELSE 0 END), 0) AS draws, IFNULL(SUM(CASE WHEN odm.games > dm.games THEN 1 ELSE 0 END), 0) AS losses FROM deck AS d LEFT JOIN deck_match AS dm ON dm.deck_id = d.id LEFT JOIN `match` AS m ON dm.match_id = m.id LEFT JOIN deck_match AS odm ON m.id = odm.match_id AND odm.deck_id <> d.id LEFT JOIN deck AS od ON odm.deck_id = od.id {season_join} WHERE {where} """ results = guarantee.exactly_one(db().select(sql, args)) results['hero_deck_ids'] = results['hero_deck_ids'].split( ',') if results['hero_deck_ids'] else [] results['hero_decks'] = deck.load_decks( 'd.id IN (' + ', '.join(results['hero_deck_ids']) + ')') if results['hero_deck_ids'] else [] results['enemy_deck_ids'] = results['enemy_deck_ids'].split( ',') if results['enemy_deck_ids'] else [] results['match_ids'] = results['match_ids'].split( ',') if results['match_ids'] else [] results['matches'] = match.load_matches( where='m.id IN (' + ', '.join(results['match_ids']) + ')') if results['match_ids'] else [] return results
def load_person(where: str, season_id: Optional[int] = None) -> Person: people = load_people(where, season_id=season_id) if len( people ) == 0: # We didn't find an entry for that person with decks, what about without? person = load_person_statless(where, season_id) else: person = guarantee.exactly_one(people) set_achievements([person], season_id) return person
def active_league() -> competition.Competition: where = 'c.id = ({id_query})'.format(id_query=active_competition_id_query()) leagues = competition.load_competitions(where) if len(leagues) == 0: start_date = dtutil.now(tz=dtutil.WOTC_TZ) end_date = determine_end_of_league(start_date) name = determine_league_name(end_date) comp_id = competition.get_or_insert_competition(start_date, end_date, name, 'League', None, competition.Top.EIGHT) leagues = [competition.load_competition(comp_id)] return guarantee.exactly_one(leagues)
def active_league(should_load_decks: bool = False) -> competition.Competition: where = 'c.id = ({id_query})'.format(id_query=active_competition_id_query()) leagues = competition.load_competitions(where, should_load_decks=should_load_decks) if len(leagues) == 0: start_date = dtutil.now(tz=dtutil.WOTC_TZ) end_date = determine_end_of_league(start_date, seasons.next_rotation()) name = determine_league_name(start_date, end_date) comp_id = competition.get_or_insert_competition(start_date, end_date, name, 'League', None, competition.Top.EIGHT) if not comp_id: raise InvalidDataException(f'No competition id with {start_date}, {end_date}, {name}') leagues = [competition.load_competition(comp_id)] return guarantee.exactly_one(leagues)
def drop(person): error = validate_api_key() if error: return error decks = league.active_decks_by(person) if len(decks) == 0: return return_json(generate_error('NO_ACTIVE_RUN', 'That person does not have an active run')) run = guarantee.exactly_one(decks) league.retire_deck(run) result = {'success':True} return return_json(result)
def load_card(name: str, season_id: Optional[int] = None) -> Card: c = guarantee.exactly_one(oracle.load_cards([name])) c.decks = deck.load_decks(query.card_where(name), season_id=season_id) c.wins, c.losses, c.draws, c.tournament_wins, c.tournament_top8s, c.perfect_runs = 0, 0, 0, 0, 0, 0 for d in c.decks: c.wins += d.get('wins', 0) c.losses += d.get('losses', 0) c.draws += d.get('draws', 0) c.tournament_wins += 1 if d.get('finish') == 1 else 0 c.tournament_top8s += 1 if (d.get('finish') or sys.maxsize) <= 8 else 0 c.perfect_runs += 1 if d.get('source_name') == 'League' and d.get('wins', 0) >= 5 and d.get('losses', 0) == 0 else 0 if c.wins or c.losses: c.win_percent = round((c.wins / (c.wins + c.losses)) * 100, 1) else: c.win_percent = '' c.num_decks = len(c.decks) c.played_competitively = c.wins or c.losses or c.draws return c
def load_card(name: str, season_id: Optional[int] = None) -> Card: c = guarantee.exactly_one(oracle.load_cards([name])) c.decks = deck.load_decks( 'd.id IN (SELECT deck_id FROM deck_card WHERE card = {name})'.format( name=sqlescape(name)), season_id=season_id) c.all = Container() c.all_wins = sum(filter(None, [d.wins for d in c.decks])) c.all_losses = sum(filter(None, [d.losses for d in c.decks])) c.all_draws = sum(filter(None, [d.draws for d in c.decks])) if c.all_wins or c.all_losses: c.all_win_percent = round( (c.all_wins / (c.all_wins + c.all_losses)) * 100, 1) else: c.all_win_percent = '' c.all_num_decks = len(c.decks) c.played_competitively = c.all_wins or c.all_losses or c.all_draws return c
def load_person_statless(where: str = 'TRUE', season_id: Optional[int] = None) -> Person: person_query = query.person_query() sql = f""" SELECT p.id, {person_query} AS name, p.mtgo_username, p.tappedout_username, p.mtggoldfish_username, p.discord_id, p.elo, p.locale FROM person AS p WHERE {where} """ people = [Person(r) for r in db().select(sql)] for p in people: p.season_id = season_id return guarantee.exactly_one(people)
def load_card(name: str, season_id: Optional[int] = None) -> Card: c = guarantee.exactly_one(oracle.load_cards([name])) c.decks = deck.load_decks( 'd.id IN (SELECT deck_id FROM deck_card WHERE card = {name})'.format( name=sqlescape(name)), season_id=season_id) c.all_wins, c.all_losses, c.all_draws, c.all_tournament_wins, c.all_tournament_top8s, c.all_perfect_runs = 0, 0, 0, 0, 0, 0 for d in c.decks: c.all_wins += d.get('wins', 0) c.all_losses += d.get('losses', 0) c.all_draws += d.get('draws', 0) c.all_tournament_wins += 1 if d.get('finish') == 1 else 0 c.all_tournament_top8s += 1 if (d.get('finish') or sys.maxsize) <= 8 else 0 c.all_perfect_runs += 1 if d.get('source_name') == 'League' and d.get( 'wins', 0) >= 5 and d.get('losses', 0) == 0 else 0 if c.all_wins or c.all_losses: c.all_win_percent = round( (c.all_wins / (c.all_wins + c.all_losses)) * 100, 1) else: c.all_win_percent = '' c.all_num_decks = len(c.decks) c.played_competitively = c.all_wins or c.all_losses or c.all_draws return c
def load_person(where: str, season_id: Optional[int] = None) -> Person: person = guarantee.exactly_one(load_people(where, season_id=season_id)) set_achievements([person], season_id) set_head_to_head([person], season_id) return person
def associate(d, discord_id): person = guarantee.exactly_one( load_people('d.id = {deck_id}'.format(deck_id=sqlescape(d.id)))) sql = 'UPDATE person SET discord_id = %s WHERE id = %s' return db().execute(sql, [discord_id, person.id])
def load_deck(deck_id: int) -> Deck: return guarantee.exactly_one(load_decks('d.id = {deck_id}'.format(deck_id=sqlescape(deck_id))))
def series_info(name: str) -> Container: return guarantee.exactly_one( [s for s in all_series_info() if s.name == name])
def get_set(set_id: int) -> Container: rs = db().select( 'SELECT ' + (', '.join(property for property in card.set_properties())) + ' FROM `set` WHERE id = %s', [set_id]) return guarantee.exactly_one([Container(r) for r in rs])
def load_competition(competition_id: int) -> Competition: return guarantee.exactly_one( load_competitions('c.id = {competition_id}'.format( competition_id=sqlescape(competition_id))))
def series_info(tournament_id: int) -> Container: return guarantee.exactly_one([s for s in all_series_info() if s.tournament_id == tournament_id])
def load_match(match_id: int, deck_id: int) -> Container: return guarantee.exactly_one( load_matches(f'm.id = {match_id} AND d.id = {deck_id}'))