def load_people(where='1 = 1', order_by='`season_num_decks` DESC, `all_num_decks` DESC, name'):
sql = """
SELECT
p.id,
{person_query} AS name,
p.mtgo_username,
p.tappedout_username,
p.mtggoldfish_username,
p.discord_id,
p.elo,
{all_select},
SUM(DISTINCT CASE WHEN d.competition_id IS NOT NULL THEN 1 ELSE 0 END) AS `all_num_competitions`,
{season_select},
SUM(DISTINCT CASE WHEN d.created_date >= %s AND d.competition_id IS NOT NULL THEN 1 ELSE 0 END) AS `season_num_competitions`
FROM
person AS p
LEFT JOIN
deck AS d ON p.id = d.person_id
{nwdl_join}
WHERE
{where}
GROUP BY
p.id
ORDER BY
{order_by}
""".format(person_query=query.person_query(), all_select=deck.nwdl_all_select(), season_select=deck.nwdl_season_select(), nwdl_join=deck.nwdl_join(), where=where, order_by=order_by)
people = [Person(r) for r in db().execute(sql, [int(rotation.last_rotation().timestamp())])]
if len(people) > 0:
set_decks(people)
set_achievements(people)
set_head_to_head(people)
return people
def load_archetypes(where: str = '1 = 1', merge: bool = False, season_id: int = None) -> List[Archetype]:
decks = deck.load_decks(where, season_id=season_id)
archetypes: Dict[str, Archetype] = {}
for d in decks:
if d.archetype_id is None:
continue
key = 'merge' if merge else d.archetype_id
archetype = archetypes.get(key, Archetype())
archetype.id = d.archetype_id
archetype.name = d.archetype_name
archetype.decks = archetype.get('decks', []) + [d]
archetype.all_wins = archetype.get('all_wins', 0) + (d.get('all_wins') or 0)
archetype.all_losses = archetype.get('all_losses', 0) + (d.get('all_losses') or 0)
archetype.all_draws = archetype.get('all_draws', 0) + (d.get('all_draws') or 0)
if d.get('finish') == 1:
archetype.all_tournament_wins = archetype.get('all_tournament_wins', 0) + 1
if (d.get('finish') or sys.maxsize) <= 8:
archetype.all_top8s = archetype.get('all_top8s', 0) + 1
archetype.all_perfect_runs = archetype.get('all_perfect_runs', 0) + 1
if d.active_date >= rotation.last_rotation():
archetype.season_wins = archetype.get('season_wins', 0) + (d.get('season_wins') or 0)
archetype.season_losses = archetype.get('season_losses', 0) + (d.get('season_losses') or 0)
archetype.season_draws = archetype.get('season_draws', 0) + (d.get('season_draws') or 0)
if d.get('finish') == 1:
archetype.season_tournament_wins = archetype.get('season_tournament_wins', 0) + 1
if (d.get('finish') or sys.maxsize) <= 8:
archetype.season_top8s = archetype.get('season_top8s', 0) + 1
if d.source_name == 'League' and d.wins >= 5 and d.losses == 0:
archetype.season_perfect_runs = archetype.get('season_all_perfect_runs', 0) + 1
archetypes[key] = archetype
archetype_list = list(archetypes.values())
return archetype_list
def load_people(where='1 = 1'):
sql = """
SELECT p.id, {person_query} AS name,
COUNT(d.id) AS `all.num_decks`,
SUM(d.wins) AS `all.wins`,
SUM(d.losses) AS `all.losses`,
SUM(d.draws) AS `all.draws`,
IFNULL(ROUND((SUM(d.wins) / SUM(d.wins + d.losses)) * 100, 1), '') AS `all.win_percent`,
SUM(CASE WHEN d.competition_id IS NOT NULL THEN 1 ELSE 0 END) AS `all.num_competitions`,
SUM(CASE WHEN d.created_date >= %s THEN 1 ELSE 0 END) AS `season.num_decks`,
SUM(CASE WHEN d.created_date >= %s THEN wins ELSE 0 END) AS `season.wins`,
SUM(CASE WHEN d.created_date >= %s THEN losses ELSE 0 END) AS `season.losses`,
SUM(CASE WHEN d.created_date >= %s THEN draws ELSE 0 END) AS `season.draws`,
IFNULL(ROUND((SUM(CASE WHEN d.created_date >= %s THEN wins ELSE 0 END) / SUM(CASE WHEN d.created_date >= %s THEN wins ELSE 0 END + CASE WHEN d.created_date >= %s THEN losses ELSE 0 END)) * 100, 1), '') AS `season.win_percent`,
SUM(CASE WHEN d.created_date >= %s AND d.competition_id IS NOT NULL THEN 1 ELSE 0 END) AS `season.num_competitions`
FROM person AS p
LEFT JOIN deck AS d ON p.id = d.person_id
WHERE {where}
GROUP BY p.id
ORDER BY `season.num_decks` DESC, `all.num_decks` DESC, name
""".format(person_query=query.person_query(), where=where)
people = [Person(r) for r in db().execute(sql, [rotation.last_rotation().timestamp()] * 8)]
if len(people) > 0:
set_decks(people)
return people
def cache() -> None:
db = database.get_database(configuration.get_str('prices_database'))
now = round(time.time())
week = now - 60 * 60 * 24 * 7
month = now - 60 * 60 * 24 * 30
last_rotation = int(rotation.last_rotation().timestamp())
sql = 'SELECT MAX(`time`) FROM low_price'
latest = db.value(sql)
db.begin('cache')
db.execute('DELETE FROM cache')
sql = """
INSERT INTO cache (`time`, name, price, low, high, week, month, season)
SELECT
MAX(`time`) AS `time`,
name,
MIN(CASE WHEN `time` = %s THEN price END) AS price,
MIN(CASE WHEN `time` > %s THEN price END) AS low,
MAX(CASE WHEN `time` > %s THEN price END) AS high,
AVG(CASE WHEN `time` > %s AND price = 1 THEN 1 WHEN `time` > %s THEN 0 END) AS week,
AVG(CASE WHEN `time` > %s AND price = 1 THEN 1 WHEN `time` > %s THEN 0 END) AS month,
AVG(CASE WHEN `time` > %s AND price = 1 THEN 1 WHEN `time` > %s THEN 0 END) AS season
FROM low_price
GROUP BY name;
"""
db.execute(sql, [
latest, last_rotation, last_rotation, week, week, month, month,
last_rotation, last_rotation
])
db.commit('cache')
def load_card(name):
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)))
c.season = Container()
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 or c.all.draws:
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)
season_decks = [
d for d in c.decks if d.created_date > rotation.last_rotation()
]
c.season.wins = sum(filter(None, [d.wins for d in season_decks]))
c.season.losses = sum(filter(None, [d.losses for d in season_decks]))
c.season.draws = sum(filter(None, [d.draws for d in season_decks]))
if c.season.wins or c.season.losses or c.season.draws:
c.season.win_percent = round(
(c.season.wins / (c.season.wins + c.season.losses)) * 100, 1)
else:
c.season.win_percent = ''
c.season.num_decks = len(season_decks)
c.played_competitively = c.all.wins or c.all.losses or c.all.draws
return c
def load_archetypes(where='1 = 1', merge=False):
decks = deck.load_decks(where)
archetypes = {}
for d in decks:
if d.archetype_id is None:
continue
key = 'merge' if merge else d.archetype_id
archetype = archetypes.get(key, Archetype())
archetype.id = d.archetype_id
archetype.name = d.archetype_name
archetype.decks = archetype.get('decks', []) + [d]
archetype.all = archetype.get('all', Archetype())
archetype.season = archetype.all.get('season', Archetype())
archetype.all.wins = archetype.all.get('wins', 0) + (d.get('wins')
or 0)
archetype.all.losses = archetype.all.get('losses',
0) + (d.get('losses') or 0)
archetype.all.draws = archetype.all.get('draws', 0) + (d.get('draws')
or 0)
if d.created_date >= rotation.last_rotation():
archetype.season.wins = archetype.season.get(
'wins', 0) + (d.get('wins') or 0)
archetype.season.losses = archetype.season.get(
'losses', 0) + (d.get('losses') or 0)
archetype.season.draws = archetype.season.get(
'draws', 0) + (d.get('draws') or 0)
archetypes[key] = archetype
archetypes = list(archetypes.values())
return archetypes
def played_cards(where='1 = 1'):
sql = """
SELECT
card AS name,
{all_select},
{season_select},
{week_select}
FROM
deck_card AS dc
INNER JOIN
deck AS d ON dc.deck_id = d.id
{nwdl_join}
WHERE
{where}
GROUP BY
dc.card
ORDER BY
season_num_decks DESC,
SUM(CASE WHEN dsum.created_date >= %s THEN dsum.wins - dsum.losses ELSE 0 END) DESC,
name
""".format(all_select=deck.nwdl_all_select(),
season_select=deck.nwdl_season_select(),
week_select=deck.nwdl_week_select(),
nwdl_join=deck.nwdl_join(),
where=where)
cs = [
Container(r)
for r in db().execute(sql, [int(rotation.last_rotation().timestamp())])
]
cards = oracle.cards_by_name()
for c in cs:
c.update(cards[c.name])
return cs
def cleanup(count: int = 0) -> None:
beginning_of_season = rotation.last_rotation()
one_month_ago = dtutil.now(dtutil.WOTC_TZ) - datetime.timedelta(31)
oldest_needed = min(beginning_of_season, one_month_ago)
limit = ''
if count > 0:
limit = f'LIMIT {count * 2}'
execute('DELETE FROM low_price WHERE `time` < %s ' + limit,
[dtutil.dt2ts(oldest_needed)])
def setup_rotation(self) -> None:
self.season_start_display = dtutil.display_date(
rotation.last_rotation())
self.season_end_display = dtutil.display_date(rotation.next_rotation())
self.scryfall_url = 'https://scryfall.com/search?q=f%3Apd'
self.legal_cards_url = 'http://pdmtgo.com/legal_cards.txt'
self.in_rotation = rotation.in_rotation()
self.rotation_msg = 'Rotation is in progress.'
self.rotation_url = url_for('rotation')
def played_cards(where='1 = 1'):
sql = """
SELECT
card AS name,
COUNT(card) AS `all.n_decks`,
SUM(CASE WHEN maindeck_n > 0 THEN 1 ELSE 0 END) AS `all.n_maindecks`,
SUM(CASE WHEN sideboard_n > 0 THEN 1 ELSE 0 END) AS `all.n_sideboards`,
SUM(maindeck_n + sideboard_n) AS `all.count_decks`,
SUM(maindeck_n) AS `all.count_maindecks`,
SUM(sideboard_n) AS `all.count_sideboards`,
SUM(wins) AS `all.wins`,
SUM(losses) AS `all.losses`,
SUM(draws) AS `all.draws`,
IFNULL(ROUND((SUM(wins) / SUM(wins + losses)) * 100, 1), '') AS `all.win_percent`,
SUM(CASE WHEN created_date >= %s THEN 1 ELSE 0 END) AS `season.n_decks`,
SUM(CASE WHEN created_date >= %s AND maindeck_n > 0 THEN 1 ELSE 0 END) AS `season.n_maindecks`,
SUM(CASE WHEN created_date >= %s AND sideboard_n > 0 THEN 1 ELSE 0 END) AS `season.n_sideboards`,
SUM(CASE WHEN created_date >= %s THEN maindeck_n + sideboard_n ELSE 0 END) AS `season.count_decks`,
SUM(CASE WHEN created_date >= %s THEN maindeck_n ELSE 0 END) AS `season.count_maindecks`,
SUM(CASE WHEN created_date >= %s THEN sideboard_n ELSE 0 END) AS `season.count_sideboards`,
SUM(CASE WHEN created_date >= %s THEN wins ELSE 0 END) AS `season.wins`,
SUM(CASE WHEN created_date >= %s THEN losses ELSE 0 END) AS `season.losses`,
SUM(CASE WHEN created_date >= %s THEN draws ELSE 0 END) AS `season.draws`,
ROUND((SUM(CASE WHEN created_date >= %s THEN wins ELSE 0 END) / SUM(CASE WHEN created_date >= %s THEN wins ELSE 0 END + CASE WHEN created_date >= %s THEN losses ELSE 0 END)) * 100, 1) AS `season.win_percent`
FROM
(SELECT
d.created_date,
d.person_id,
dc.card,
SUM(CASE WHEN NOT dc.sideboard THEN n ELSE 0 END) AS maindeck_n,
SUM(CASE WHEN dc.sideboard THEN n ELSE 0 END) AS sideboard_n,
d.wins,
d.draws,
d.losses
FROM
deck_card AS dc
INNER JOIN
deck AS d ON d.id = dc.deck_id
GROUP BY
deck_id, card) AS deck_card_agg
WHERE {where}
GROUP BY card
ORDER BY `season.n_decks` DESC, `season.count_decks` DESC, `season.n_maindecks` DESC, `season.count_maindecks` DESC, `all.n_decks` DESC, `all.count_decks` DESC, `all.n_maindecks` DESC, `all.count_maindecks` DESC
""".format(where=where)
cs = [
Container(r)
for r in db().execute(sql, [rotation.last_rotation().timestamp()] * 12)
]
cards = oracle.cards_by_name()
for c in cs:
c.update(cards[c.name])
return cs
def stats() -> Dict[str, int]:
sql = """
SELECT
SUM(CASE WHEN FROM_UNIXTIME(`date`) >= NOW() - INTERVAL 1 DAY THEN 1 ELSE 0 END) AS num_matches_today,
SUM(CASE WHEN FROM_UNIXTIME(`date`) >= NOW() - INTERVAL 7 DAY THEN 1 ELSE 0 END) AS num_matches_this_week,
SUM(CASE WHEN FROM_UNIXTIME(`date`) >= NOW() - INTERVAL 30 DAY THEN 1 ELSE 0 END) AS num_matches_this_month,
SUM(CASE WHEN `date` >= %s THEN 1 ELSE 0 END) AS num_matches_this_season,
COUNT(*) AS num_matches_all_time
FROM
`match`
"""
return db().select(sql, [dtutil.dt2ts(rotation.last_rotation())])[0]
def load_matchups(archetype_id):
sql = """
SELECT
oa.id, oa.name,
SUM(CASE WHEN d.created_date < ? THEN 0 WHEN dm.games = 2 THEN 1 ELSE 0 END) AS `season.wins`,
SUM(CASE WHEN d.created_date < ? THEN 0 WHEN odm.games = 2 THEN 1 ELSE 0 END) AS `season.losses`,
SUM(CASE WHEN d.created_date < ? THEN 0 WHEN dm.games = odm.games THEN 1 ELSE 0 END) AS `season.draws`,
IFNULL(ROUND(AVG(CASE WHEN d.created_date < ? THEN NULL WHEN dm.games = 2 THEN 1 WHEN odm.games = 2 THEN 0 END) * 100, 1), '') AS `season.win_percent`,
SUM(CASE WHEN dm.games = 2 THEN 1 ELSE 0 END) AS `all.wins`,
SUM(CASE WHEN odm.games = 2 THEN 1 ELSE 0 END) AS `all.losses`,
SUM(CASE WHEN dm.games = odm.games THEN 1 ELSE 0 END) AS `all.draws`,
IFNULL(ROUND(AVG(CASE WHEN dm.games = 2 THEN 1 WHEN odm.games = 2 THEN 0 END) * 100, 1), '') AS `all.win_percent`
FROM
archetype AS a
INNER JOIN
deck AS d
ON
d.archetype_id IN (SELECT descendant FROM archetype_closure WHERE ancestor = a.id)
INNER JOIN
deck_match AS dm
ON
d.id = dm.deck_id
INNER JOIN
deck_match AS odm
ON
dm.match_id = odm.match_id
AND odm.deck_id <> d.id
INNER JOIN
deck AS od
ON
od.id = odm.deck_id
INNER JOIN
archetype AS oa
ON
od.archetype_id IN (SELECT descendant FROM archetype_closure WHERE ancestor = oa.id)
WHERE
a.id = ?
GROUP BY
oa.id
ORDER BY
`season.wins` DESC, `all.wins` DESC
"""
return [
Container(m)
for m in db().execute(sql, [rotation.last_rotation().timestamp()] * 4 +
[archetype_id])
]
def load_archetypes_deckless(
where='1 = 1',
order_by='`season.num_decks` DESC, `all.num_decks` DESC, `season.wins` DESC, `all.wins` DESC'
):
sql = """
SELECT
a.id,
a.name,
aca.ancestor AS parent_id,
COUNT(DISTINCT d.id) AS `all.num_decks`,
SUM(d.wins) AS `all.wins`,
SUM(d.losses) AS `all.losses`,
SUM(d.draws) AS `all.draws`,
IFNULL(ROUND((SUM(wins) / SUM(wins + losses)) * 100, 1), '') AS `all.win_percent`,
SUM(CASE WHEN d.created_date >= %s THEN 1 ELSE 0 END) AS `season.num_decks`,
SUM(CASE WHEN d.created_date >= %s THEN wins ELSE 0 END) AS `season.wins`,
SUM(CASE WHEN d.created_date >= %s THEN losses ELSE 0 END) AS `season.losses`,
SUM(CASE WHEN d.created_date >= %s THEN draws ELSE 0 END) AS `season.draws`,
IFNULL(ROUND((SUM(CASE WHEN d.created_date >= %s THEN wins ELSE 0 END) / SUM(CASE WHEN d.created_date >= %s THEN wins ELSE 0 END + CASE WHEN d.created_date >= %s THEN losses ELSE 0 END)) * 100, 1), '') AS `season.win_percent`
FROM archetype AS a
LEFT JOIN archetype_closure AS aca ON a.id = aca.descendant AND aca.depth = 1
LEFT JOIN archetype_closure AS acd ON a.id = acd.ancestor
LEFT JOIN deck AS d ON acd.descendant = d.archetype_id
WHERE {where}
GROUP BY a.id
ORDER BY {order_by}
""".format(where=where, order_by=order_by)
archetypes = [
Archetype(a)
for a in db().execute(sql, [rotation.last_rotation().timestamp()] * 7)
]
archetypes_by_id = {a.id: a for a in archetypes}
for a in archetypes:
a.decks = []
a.parent = archetypes_by_id.get(a.parent_id, None)
return archetypes
def load_matchups(archetype_id):
sql = """
SELECT
oa.id,
oa.name,
SUM(CASE WHEN dm.games > IFNULL(odm.games, 0) THEN 1 ELSE 0 END) AS all_wins, -- IFNULL so we still count byes as wins.
SUM(CASE WHEN dm.games < odm.games THEN 1 ELSE 0 END) AS all_losses,
SUM(CASE WHEN dm.games = odm.games THEN 1 ELSE 0 END) AS all_draws,
IFNULL(ROUND((SUM(CASE WHEN dm.games > odm.games THEN 1 ELSE 0 END) / NULLIF(SUM(CASE WHEN dm.games <> IFNULL(odm.games, 0) THEN 1 ELSE 0 END), 0)) * 100, 1), '') AS all_win_percent,
SUM(CASE WHEN d.created_date > %s AND dm.games > IFNULL(odm.games, 0) THEN 1 ELSE 0 END) AS season_wins, -- IFNULL so we still count byes as wins.
SUM(CASE WHEN d.created_date > %s AND dm.games < odm.games THEN 1 ELSE 0 END) AS season_losses,
SUM(CASE WHEN d.created_date > %s AND dm.games = odm.games THEN 1 ELSE 0 END) AS season_draws,
IFNULL(ROUND((SUM(CASE WHEN d.created_date > %s AND dm.games > odm.games THEN 1 ELSE 0 END) / NULLIF(SUM(CASE WHEN d.created_date > %s AND dm.games <> IFNULL(odm.games, 0) THEN 1 ELSE 0 END), 0)) * 100, 1), '') AS season_win_percent
FROM
archetype AS a
INNER JOIN
deck AS d ON d.archetype_id IN (SELECT descendant FROM archetype_closure WHERE ancestor = a.id)
INNER JOIN
deck_match AS dm ON d.id = dm.deck_id
INNER JOIN
deck_match AS odm ON dm.match_id = odm.match_id AND odm.deck_id <> d.id
INNER JOIN
deck AS od ON od.id = odm.deck_id
INNER JOIN
archetype AS oa ON od.archetype_id IN (SELECT descendant FROM archetype_closure WHERE ancestor = oa.id)
WHERE
a.id = %s
GROUP BY
oa.id
ORDER BY
`season_wins` DESC, `all_wins` DESC
""".format(all_select=deck.nwdl_all_select(),
season_select=deck.nwdl_season_select(),
nwdl_join=deck.nwdl_join())
return [
Container(m) for m in
db().execute(sql, [int(rotation.last_rotation().timestamp())] * 5 +
[archetype_id])
]
def nwdl_season_select():
return nwdl_select(
'season_', 'dsum.created_date >= {season_start}'.format(
season_start=int(rotation.last_rotation().timestamp())))
def load_cards(season_id: Optional[int] = None,
person_id: Optional[int] = None,
retry: bool = False) -> List[Card]:
table = '_card_person_stats' if person_id else '_card_stats'
where = 'TRUE'
group_by = 'name'
if person_id:
group_by += ', person_id'
where = 'person_id = {person_id}'.format(
person_id=sqlescape(person_id))
sql = """
SELECT
name,
SUM(num_decks) AS all_num_decks,
SUM(wins) AS all_wins,
SUM(losses) AS all_losses,
SUM(draws) AS all_draws,
SUM(wins - losses) AS record,
SUM(perfect_runs) AS all_perfect_runs,
SUM(tournament_wins) AS all_tournament_wins,
SUM(tournament_top8s) AS all_tournament_top8s,
IFNULL(ROUND((SUM(wins) / NULLIF(SUM(wins + losses), 0)) * 100, 1), '') AS all_win_percent,
SUM(CASE WHEN `day` > {season_start} THEN num_decks ELSE 0 END) AS season_num_decks,
SUM(CASE WHEN `day` > {season_start} THEN wins ELSE 0 END) AS season_wins,
SUM(CASE WHEN `day` > {season_start} THEN losses ELSE 0 END) AS season_losses,
SUM(CASE WHEN `day` > {season_start} THEN draws ELSE 0 END) AS season_draws,
SUM(CASE WHEN `day` > {season_start} THEN perfect_runs ELSE 0 END) AS season_perfect_runs,
SUM(CASE WHEN `day` > {season_start} THEN tournament_wins ELSE 0 END) AS season_tournament_wins,
SUM(CASE WHEN `day` > {season_start} THEN tournament_top8s ELSE 0 END) AS season_tournament_top8s,
IFNULL(ROUND((SUM(CASE WHEN `day` > {season_start} THEN wins ELSE 0 END) / NULLIF(SUM(CASE WHEN `day` > {season_start} THEN wins + losses ELSE 0 END), 0)) * 100, 1), '') AS `season_win_percent`,
SUM(CASE WHEN `day` > UNIX_TIMESTAMP(NOW() - INTERVAL 1 WEEK) THEN num_decks ELSE 0 END) AS week_num_decks,
SUM(CASE WHEN `day` > UNIX_TIMESTAMP(NOW() - INTERVAL 1 WEEK) THEN wins ELSE 0 END) AS week_wins,
SUM(CASE WHEN `day` > UNIX_TIMESTAMP(NOW() - INTERVAL 1 WEEK) THEN losses ELSE 0 END) AS week_losses,
SUM(CASE WHEN `day` > UNIX_TIMESTAMP(NOW() - INTERVAL 1 WEEK) THEN draws ELSE 0 END) AS week_draws,
SUM(CASE WHEN `day` > UNIX_TIMESTAMP(NOW() - INTERVAL 1 WEEK) THEN perfect_runs ELSE 0 END) AS week_perfect_runs,
SUM(CASE WHEN `day` > UNIX_TIMESTAMP(NOW() - INTERVAL 1 WEEK) THEN tournament_wins ELSE 0 END) AS week_tournament_wins,
SUM(CASE WHEN `day` > UNIX_TIMESTAMP(NOW() - INTERVAL 1 WEEK) THEN tournament_top8s ELSE 0 END) AS week_tournament_top8s,
IFNULL(ROUND((SUM(CASE WHEN `day` > UNIX_TIMESTAMP(NOW() - INTERVAL 1 WEEK) THEN wins ELSE 0 END) / NULLIF(SUM(CASE WHEN `day` > UNIX_TIMESTAMP(NOW() - INTERVAL 1 WEEK) THEN wins + losses ELSE 0 END), 0)) * 100, 1), '') AS `week_win_percent`
FROM
{table} AS cs
LEFT JOIN
({season_table}) AS season ON season.start_date <= cs.day AND (season.end_date IS NULL OR season.end_date > cs.day)
WHERE
{season_query} AND {where}
GROUP BY
{group_by}
ORDER BY
all_num_decks DESC,
record,
name
""".format(table=table,
season_table=query.season_table(),
season_start=int(rotation.last_rotation().timestamp()),
season_query=query.season_query(season_id),
where=where,
group_by=group_by)
try:
cs = [Container(r) for r in db().select(sql)]
cards = oracle.cards_by_name()
for c in cs:
c.update(cards[c.name])
return cs
except DatabaseException as e:
if not retry:
print(
f"Got {e} trying to load_cards so trying to preaggregate. If this is happening on user time that's undesirable."
)
preaggregate()
return load_cards(season_id, person_id, retry=True)
print(f'Failed to preaggregate. Giving up.')
raise e
def cleanup() -> None:
beginning_of_season = rotation.last_rotation()
one_month_ago = dtutil.now(dtutil.WOTC_TZ) - datetime.timedelta(31)
oldest_needed = min(beginning_of_season, one_month_ago)
execute('DELETE FROM low_price WHERE `time` < %s',
[dtutil.dt2ts(oldest_needed)])
