def is_valid(ballot_id):
with get_cnx() as db:
cursor = db.cursor()
cursor.execute(
f"""
SELECT COUNT(*)
FROM {ballot_table} B
INNER JOIN {scores_table} S ON B.id = S.ballot_id
WHERE B.id = %s
""",
(ballot_id,)
)
(num,) = cursor.fetchone()
assert(num == 28)
cursor.execute(
f"""
SELECT COUNT(*)
FROM {ballot_table} B
INNER JOIN {ranks_table} R ON B.id = R.ballot_id
WHERE B.id = %s
""",
(ballot_id,)
)
(ranks_cnt,) = cursor.fetchone()
def _get_info_for_exam(matchup_id: int, side: str, order: int):
with get_cnx() as db:
cursor = db.cursor()
cursor.execute(
f"""
SELECT witness_id, attorney_id, crossing_id, witness_name
FROM {examination_table}
WHERE matchup_id = %s AND side = %s AND `order` = %s
""",
(matchup_id, side, order),
)
try:
(witness, attorney, crosser, name) = cursor.fetchone()
return {
"witness": witness,
"attorney": attorney,
"crosser": crosser,
"name": name,
}
except:
return {
"witness": None,
"attorney": None,
"crosser": None,
"name": None,
}
def get_exam_section(ballot_id, side, exam_num, role, exam_type):
with get_cnx() as db:
cursor = db.cursor()
sql_attorney = BallotSections._sql_attorney(role)
sql_cross = BallotSections._sql_cross(exam_type)
sql_pl = BallotSections._sql_pl(side)
cursor.execute(
f"""
SELECT id, score, note
FROM {exam_data_table}
WHERE ballot_id = %s AND exam_num = %s AND attorney = %s AND `cross` = %s AND pl = %s
""",
(ballot_id, exam_num, sql_attorney, sql_cross, sql_pl),
)
results = cursor.fetchall()
if len(results) == 0:
return None
for (score_id, score, note) in results:
pass
return {"id": score_id, "score": score, "notes": note}
def get_speech_section(ballot_id: int, side: Literal["pl", "def"],
speech: Literal["open", "close"]):
with get_cnx() as db:
cursor = db.cursor()
sql_pl = BallotSections._sql_pl(side)
sql_open = BallotSections._sql_opening(speech)
cursor.execute(
f"""
SELECT score, note, id
FROM {speech_data_table}
WHERE ballot_id = %s AND pl = %s AND opening = %s
""",
(
ballot_id,
sql_pl,
sql_open,
),
)
result = cursor.fetchone()
if result is None:
return None
score, note, score_id = result
return {"score": score, "notes": note, "id": score_id}
def get_student(student_id):
with get_cnx() as db:
cursor = db.cursor()
cursor.execute(
f"SELECT student_name FROM {student_table} WHERE id = %s",
(student_id, ))
return {"name": cursor.fetchone()[0]}
def add_school(tournament_id: int, name: str):
with get_cnx() as db:
cursor = db.cursor()
cursor.execute(
f"INSERT INTO {school_table} (tournament_id, name) VALUES (%s, %s)",
(tournament_id, name),
)
db.commit()
def get_is_complete(ballot_id):
with get_cnx() as db:
cursor = db.cursor()
cursor.execute(
f"SELECT complete FROM {ballot_table} WHERE id = %s", (ballot_id,)
)
(complete,) = cursor.fetchone()
return complete == 1
def get_conflicts(tournament_id: int, id: int):
with get_cnx() as db:
cursor = db.cursor()
cursor.execute(
f"SELECT school_name FROM {conflict_table} WHERE judge_id = %s",
(id, ))
conflicts = [name for (name, ) in cursor.fetchall()]
return conflicts
def get_ballots(tournament_id: int, judge_id: int):
with get_cnx() as db:
cursor = db.cursor()
cursor.execute(
f"SELECT id FROM {ballots_table} WHERE judge_id = %s",
(judge_id, ))
ballot_ids = [b_id for (b_id, ) in cursor.fetchall()]
return ballot_ids
def get_ballots(matchup_id: int):
with get_cnx() as db:
cursor = db.cursor()
cursor.execute(
f"SELECT id FROM {ballots_table} WHERE matchup_id = %s",
(matchup_id, ))
ballot_ids = [bid for (bid, ) in cursor.fetchall()]
return ballot_ids
def create_tournament(name):
with get_cnx() as db:
cursor = db.cursor()
cursor.execute(
f"INSERT INTO {tournament_table} (name) VALUES (%s)", (name, ))
db.commit()
return cursor.lastrowid
def get_all_tournaments():
with get_cnx() as db:
cursor = db.cursor()
cursor.execute(f"SELECT * FROM {tournament_table}")
tournaments = []
for (id, name) in cursor.fetchall():
tournaments.append({"id": id, "name": name})
return tournaments
def add_student(tournament_id, team_num, name):
with get_cnx() as db:
cursor = db.cursor()
cursor.execute(
f"INSERT INTO {student_table} (tournament_id, team_num, student_name) VALUES (%s, %s, %s)",
(tournament_id, team_num, name),
)
db.commit()
return cursor.lastrowid
def delete_tournament(id):
with get_cnx() as db:
cursor = db.cursor()
cursor.execute(
f"DELETE FROM {tournament_table} WHERE tournament_id = %s",
(id, ))
db.commit()
return True
def get_judge(tournament_id: int, id: int):
with get_cnx() as db:
cursor = db.cursor()
cursor.execute(
f"SELECT tournament_id, id, name FROM {judge_table} WHERE id = %s",
(id, ),
)
tourn_id, judge_id, name = cursor.fetchone()
return {"id": judge_id, "name": name, "tournament_id": tourn_id}
def add_judge(tournament_id: int, name: str):
with get_cnx() as db:
cursor = db.cursor()
cursor.execute(
f"INSERT INTO {judge_table} (tournament_id, name) VALUES (%s, %s)",
(tournament_id, name),
)
db.commit()
return cursor.lastrowid
def assign_role(tournament_id, matchup_id, team_num, student_id, role):
with get_cnx() as db:
cursor = db.cursor()
cursor.execute(
f"INSERT INTO {role_table} (tournament_id, matchup_id, team_num, student_id, role) VALUES (%s, %s, %s, %s, %s)",
(tournament_id, matchup_id, team_num, student_id, role),
)
db.commit()
return cursor.lastrowid
def add_conflict(tournament_id: int, id: int, school: str):
with get_cnx() as db:
cursor = db.cursor()
cursor.execute(
f"INSERT INTO {conflict_table} (tournament_id, judge_id, school_name) VALUES (%s, %s, %s)",
(tournament_id, id, school),
)
db.commit()
return cursor.lastrowid
def get_all_rounds(tournament_id: int):
with get_cnx() as db:
cursor = db.cursor()
cursor.execute(
f"SELECT DISTINCT round_num FROM {matchup_table} WHERE tournament_id = %s",
(tournament_id, ),
)
rounds = [num for (num, ) in cursor.fetchall()]
return rounds
def get_all_info_for_tournament(id):
with get_cnx() as db:
cursor = db.cursor()
cursor.execute(
f"SELECT tournament_id, name FROM {tournament_table} WHERE tournament_id = %s",
(id, ),
)
(id, name) = cursor.fetchone()
return {"id": id, "name": name}
def set_email(judge_id: int, email: str):
with get_cnx() as db:
cursor = db.cursor()
cursor.execute(
f"""
UPDATE {judge_table}
SET email = %s
WHERE id = %s
""",
(email, judge_id),
)
db.commit()
def set_is_complete(ballot_id, complete):
with get_cnx() as db:
cursor = db.cursor()
cursor.execute(
f"""
UPDATE {ballot_table}
SET complete = {1 if complete else 0}
WHERE id = %s
""",
(ballot_id,),
)
db.commit()
def set_score_only(ballot_id, score_only):
with get_cnx() as db:
cursor = db.cursor()
cursor.execute(
f"""
UPDATE {ballot_table}
SET note_only = %s
WHERE id = %s
""",
(Ballot._bool_to_SQL(score_only), ballot_id),
)
db.commit()
def get_teams_for_school(tournament_id: int, name: str):
with get_cnx() as db:
cursor = db.cursor()
cursor.execute(
f"SELECT team_num, name FROM {team_table} WHERE tournament_id = %s AND school_name = %s",
(tournament_id, name),
)
teams = []
for team_num, name in cursor.fetchall():
teams.append({"num": team_num, "name": name})
return teams
def _set_section_note(section_id, note, db_cnx=None):
with get_cnx() as db:
cursor = db.cursor()
cursor.execute(
f"""
UPDATE {score_table}
SET note = %s
WHERE id = %s
""",
(note, section_id),
)
db.commit()
def _set_section_score(section_id: int, new_score: int):
with get_cnx() as db:
cursor = db.cursor()
cursor.execute(
f"""
UPDATE {score_table}
SET score = %s
WHERE id = %s
""",
(new_score, section_id),
)
db.commit()
def _add_single_section(ballot_id, side):
with get_cnx() as db:
cursor = db.cursor()
cursor.execute(
f"""
INSERT INTO {score_table} (ballot_id, pl)
VALUES (%s, %s)
""",
(ballot_id, BallotSections._sql_pl(side)),
)
db.commit()
return cursor.lastrowid
def set_rank_for_ballot(ballot_id, witness: bool, rank, student):
with get_cnx() as db:
cursor = db.cursor()
cursor.execute(
f"""
INSERT INTO {ranks_table} (ballot_id, rank, witness, student)
VALUES (%s, %s, %s, %s)
ON DUPLICATE KEY UPDATE student = %s
""",
(ballot_id, rank, Ballot._bool_to_SQL(witness), student, student),
)
db.commit()
def assign_witness_name(matchup_id: int, side: str, order: int,
witness_name: str):
with get_cnx() as db:
cursor = db.cursor()
cursor.execute(
f"""
INSERT INTO {examination_table} (matchup_id, side, `order`, witness_name)
VALUES (%s, %s, %s, %s)
ON DUPLICATE KEY UPDATE witness_name = %s
""",
(matchup_id, side, order, witness_name, witness_name),
)
db.commit()
def assign_attorney_to_cross(matchup_id: int, side: str, order: int,
student_id: int):
with get_cnx() as db:
cursor = db.cursor()
cursor.execute(
f"""
INSERT INTO {examination_table} (matchup_id, side, `order`, crossing_id)
VALUES (%s, %s, %s, %s)
ON DUPLICATE KEY UPDATE crossing_id = %s
""",
(matchup_id, side, order, student_id, student_id),
)
db.commit()
