def close_round(tid, rnd):
"""
Check to see if all matches report
"""
with get_db().begin() as conn:
if not all_reported(tid, rnd):
raise PairingException("Not all matches have reported result")
update_byes_recieved(tid)
conn.execute(
text(
"UPDATE player SET active=false WHERE is_bye = true AND tid = :tid"
),
{"tid": tid},
)
update_scores(tid)
update_bias(tid)
update_sos(tid)
update_esos(tid)
t = get_tournament(tid)
if rnd == t["current_rnd"]:
conn.execute(
text("UPDATE tournament SET current_rnd = :rnd WHERE id = :tid"
),
{
"rnd": rnd + 1,
"tid": tid,
},
)
return True
def existing_pairings(tid, rnd):
q = (get_db().connect().execute(
text("SELECT * FROM match where tid = :tid and rnd = :rnd"),
{
"tid": tid,
"rnd": rnd
},
).fetchone())
return q
def all_reported(tid, rnd):
q = (get_db().connect().execute(
text(
"SELECT * FROM match WHERE tid = :tid AND rnd = :rnd AND corp_score IS NULL"
),
{
"tid": tid,
"rnd": rnd
},
).fetchone())
return q is None
def add_bye_player(tid):
"""
Adds a bye player- the bye_number is future proofing for multiple 1st round byes
"""
with get_db().begin() as conn:
conn.execute(
text(
"INSERT INTO player (tid, p_name, is_bye, score) VALUES (:tid, 'Bye', true, -9)"
),
{"tid": tid},
)
return get_active_players(tid)
def record_result(mid, corp_score, runner_score):
db = get_db()
table_match = metadata.tables["match"]
with db.begin() as conn:
match = conn.execute(
select(table_match).where(table_match.c.id == mid)).fetchone()
if get_player(match["corp_id"]).is_bye or get_player(
match["runner_id"]).is_bye:
return
conn.execute(
update(table_match).where(table_match.c.id == mid).values(
corp_score=corp_score, runner_score=runner_score))
def pair_round(tid, rnd):
plrs = get_active_players(tid)
if len(plrs) % 2 == 1:
plrs = add_bye_player(tid)
pairings = make_pairings(plrs)
match_list = make_matches(pairings)
db = get_db()
table_match = metadata.tables["match"]
with db.begin() as conn:
for i, match in enumerate(match_list):
conn.execute(
insert(table_match).values(
corp_id=match[0],
runner_id=match[1],
tid=tid,
rnd=rnd,
match_num=i + 1,
))
score_byes(tid, rnd)
def update_esos(tid):
with get_db().begin() as conn:
sos_table = conn.execute(
text("""
SELECT id, sum(coalesce(sos,0)) as total_opp_sos,
sum(coalesce(opg, 0)) as total_opp_games_played
FROM (
select p.id, sum(coalesce(o.games_played,0)) as opg, sum(coalesce(o.sos,0)) as sos
FROM player p
INNER JOIN match m on p.id = m.runner_id
INNER JOIN player o ON m.corp_id = o.id
where p.tid = :tid AND p.is_bye = false AND o.is_bye = false
group by p.id
UNION
select p.id, sum(coalesce(o.games_played,0)) as opg, sum(coalesce(o.sos,0)) as sos
FROM player p
INNER JOIN match m on p.id = m.corp_id
INNER JOIN player o ON m.runner_id = o.id
where p.tid = :tid AND p.is_bye = false AND o.is_bye = false
group by p.id) as t
group by t.id
"""),
{
"tid": tid
},
).fetchall()
for player in sos_table:
conn.execute(
text("UPDATE player SET esos = :esos WHERE id = :pid"),
{
"esos":
round(
decimal.Decimal(player["total_opp_sos"]) /
decimal.Decimal(
max(player["total_opp_games_played"], 1)),
4,
),
"pid":
player["id"],
},
)
def score_byes(tid, rnd):
with get_db().begin() as conn:
conn.execute(
text(
"UPDATE match SET runner_score = 3, corp_score = 0 FROM player WHERE player.id = match.corp_id AND player.is_bye = true AND match.tid = :tid AND rnd = :rnd"
),
{
"tid": tid,
"rnd": rnd
},
)
conn.execute(
text(
"UPDATE match SET corp_score = 3, runner_score = 0 FROM player WHERE player.id = match.runner_id AND player.is_bye = true AND match.tid = :tid and rnd = :rnd"
),
{
"tid": tid,
"rnd": rnd
},
)
def update_byes_recieved(tid):
with get_db().begin() as conn:
byes = conn.execute(
text("""
SELECT p.id
FROM player p
INNER JOIN match m
ON p.id = m.corp_id
INNER JOIN player o
ON o.id = m.runner_id
WHERE o.is_bye = true AND p.tid = :tid
"""),
{
"tid": tid
},
).fetchall()
for i in byes:
conn.execute(
text("UPDATE player SET received_bye = true WHERE id = :pid"),
{"pid": i["id"]},
)
byes = conn.execute(
text("""
SELECT p.id
FROM player p
INNER JOIN match m
ON p.id = m.runner_id
INNER JOIN player o
ON o.id = m.corp_id
WHERE o.is_bye = true AND p.tid = :tid
"""),
{
"tid": tid
},
).fetchall()
for i in byes:
conn.execute(
text("UPDATE player SET received_bye = true WHERE id = :pid"),
{"pid": i["id"]},
)
def update_bias(tid):
with get_db().begin() as conn:
bias_table = conn.execute(
text("""
SELECT id, name, sum(coalesce(corp_games,0)) AS corp_games,
sum(coalesce(runner_games,0)) AS runner_games
FROM(
SELECT p.id AS id, p.p_name AS name, count(m.corp_id) AS corp_games, 0 AS runner_games
FROM player p
INNER JOIN match m on p.id = m.corp_id
INNER JOIN player o ON o.id = m.runner_id
WHERE p.tid = :tid AND o.is_bye = false AND p.is_bye = false
group by p.id
UNION
SELECT p.id AS id, p.p_name AS name, 0 AS corp_games, count(m.runner_id) AS runner_games
FROM player p
INNER JOIN match m on p.id = m.runner_id
INNER JOIN player o ON o.id = m.corp_id
WHERE p.tid = :tid AND o.is_bye = false AND p.is_bye = false
group by p.id
) as t
group by t.id, t.name
"""),
{
"tid": tid
},
).fetchall()
for player in bias_table:
conn.execute(
text(
"UPDATE player SET bias = :bias, games_played = :games_played WHERE id = :pid"
),
{
"bias": player["corp_games"] - player["runner_games"],
"games_played":
player["corp_games"] + player["runner_games"],
"pid": player["id"],
},
)
def update_scores(tid):
with get_db().begin() as conn:
scores_table = conn.execute(
text("""
SELECT id, name, SUM(coalesce(corp_points,0)) AS corp_points,
SUM(coalesce(runner_points,0)) AS runner_points
FROM(
SELECT p.id AS id, p.p_name AS name, sum(m.corp_score) AS corp_points, 0 AS runner_points
FROM player p
INNER JOIN match m on p.id = m.corp_id
WHERE p.tid = :tid
group by p.id
UNION
SELECT p.id AS id, p.p_name AS name, 0 AS corp_points, sum(m.runner_score) AS runner_points
FROM player p
INNER JOIN match m on p.id = m.runner_id
WHERE p.tid = :tid
group by p.id
) as t
group by t.id, t.name
"""),
{
"tid": tid
},
).fetchall()
for player in scores_table:
conn.execute(
text(
"UPDATE player SET score = :score WHERE id = :pid AND is_bye = false",
),
{
"score":
int(player["corp_points"]) + int(player["runner_points"]),
"pid":
player["id"],
},
)
update_bias(tid)
def can_corp(p1, p2):
"""
Using the player[opponents] field, deserialize from json
Check to see if they've played any matches
If they have not, return 0
Otherwise if they total side balance is 0 (they've played both) return None
Otherwise return the ID of the player who has to Corp
"""
with get_db().begin() as conn:
p1_corped_query = conn.execute(
text(
"SELECT * from match where corp_id = :p1_id AND runner_id = :p2_id"
),
{
"p1_id": p1["id"],
"p2_id": p2["id"]
},
).fetchone()
p2_corped_query = conn.execute(
text(
"SELECT * from match where corp_id = :p2_id AND runner_id = :p1_id"
),
{
"p1_id": p1["id"],
"p2_id": p2["id"]
},
).fetchone()
if p1_corped_query is None and p2_corped_query is None:
return 0
elif p1_corped_query is None:
return p1["id"]
elif p2_corped_query is None:
return p2["id"]
else:
return None