Beispiel #1
0
def create_tournament(title, date=datetime.date.today()):
    db = get_db()
    tournament_table = metadata.tables["tournament"]
    with db.begin() as conn:
        stmt = (insert(tournament_table).values(
            title=title, t_date=date).returning(tournament_table))
        return conn.execute(stmt).fetchone()
Beispiel #2
0
def update_player(pid, name, corp_id, runner_id):
    db = get_db()
    plr = metadata.tables["player"]
    with db.begin() as conn:
        conn.execute(
            update(plr).where(plr.c.id == pid).values(p_name=name,
                                                      corp_id=corp_id,
                                                      runner_id=runner_id))
Beispiel #3
0
def get_rnd_list(tid):
    return (get_db().connect().execute(
        text(
            "SELECT DISTINCT(rnd) as rnds FROM match WHERE tid=:tid ORDER BY rnd"
        ),
        {
            "tid": tid
        },
    ).fetchall())
Beispiel #4
0
def get_tournament(tid):
    db = get_db()
    tourney = metadata.tables["tournament"]
    t = db.execute(select(tourney).where(tourney.c.id == tid)).fetchone()

    if t is None:
        abort(404, f"Tournament id {tid} does not exist")

    return t
Beispiel #5
0
def get_active_players(tid):
    return (get_db().execute(
        text(
            "SELECT * FROM player WHERE tid = :tid AND active = true ORDER BY score DESC, sos DESC, esos DESC"
        ),
        {
            "tid": tid
        },
    ).fetchall())
Beispiel #6
0
def add_player(tid, name, corp_id, runner_id):
    db = get_db()
    player = metadata.tables["player"]
    with db.begin() as conn:
        conn.execute(
            insert(player).values(p_name=name,
                                  tid=tid,
                                  corp_id=corp_id,
                                  runner_id=runner_id))
    return name
Beispiel #7
0
def switch_tournament_activity(tid):
    db = get_db()
    tourn = metadata.tables["tournament"]
    with db.begin() as conn:
        t = conn.execute(select(tourn).where(tourn.c.id == tid)).fetchone()
        if t.active:
            setto = False
        else:
            setto = True
        conn.execute(
            update(tourn).where(tourn.c.id == tid).values(active=setto))
Beispiel #8
0
def delete_pairings(tid, rnd):
    db = get_db()
    with db.begin() as conn:
        conn.execute(
            text("DELETE FROM match WHERE tid=:tid AND rnd = :rnd"),
            {
                "tid": tid,
                "rnd": rnd
            },
        )
        conn.execute(
            text(
                "UPDATE player SET active = false WHERE is_bye = true AND tid = :tid"
            ),
            {"tid": tid},
        )
Beispiel #9
0
def get_stats(tid):
    db = get_db()
    with db.begin() as conn:
        match_table = conn.execute(
            text(
                """select match.id, match.corp_id, match.runner_id, match.corp_score, match.runner_score,
                corp_plr.p_name, corp_plr.corp_id, corp_plr.is_bye,
                runner_plr.p_name, runner_plr.runner_id, runner_plr.is_bye
                FROM match
                LEFT join player corp_plr
                ON match.corp_id = corp_plr.id
                LEFT JOIN player runner_plr
                ON match.runner_id = runner_plr.id
                WHERE corp_plr.is_bye = false AND runner_plr.is_bye = false AND match.tid = :tid""",
                {
                    "tid": tid
                },
            ).fetchall())
    return match_table
Beispiel #10
0
def get_matches(tid, rnd):
    """
    tid: Tournament ID
    round: Round to get matches from
    """
    return (get_db().connect().execute(
        text(
            """SELECT match.id, match.corp_id, match.runner_id, match.corp_score,
            match.runner_score, match.match_num,
            corp_plr.p_name as corp_player, runner_plr.p_name as runner_player
            FROM match
            LEFT JOIN player corp_plr
            ON match.corp_id = corp_plr.id
            LEFT JOIN player runner_plr
            ON match.runner_id = runner_plr.id
            WHERE match.tid = :tid AND match.rnd = :rnd
            ORDER BY match.match_num"""),
        {
            "tid": tid,
            "rnd": rnd,
        },
    ).fetchall())
Beispiel #11
0
def get_players(tid):
    db = get_db()
    # player = metadata.tables["player"]
    # return db.execute(
    #     select(player)
    #     .where(player.c.tid == tid)
    #     .where(player.c.is_bye == False)
    #     .order_by(player.c.score.desc(), player.c.sos.desc(), player.c.esos.desc())
    # ).fetchall()

    return db.execute(
        text("""
            SELECT id, tid, name, corp_id, runner_id, score, sos, esos, bias, active,
            sum(coalesce(corp_points,0)) AS corp_points,
            sum(coalesce(runner_points,0)) AS runner_points
            from(
                select p.id as id, p.tid as tid, p.p_name as name, p.corp_id as corp_id, p.runner_id as runner_id,
                p.score as score, p.sos as sos, p.esos as esos, p.bias as bias, p.active as active,
                sum(m.corp_score) AS corp_points, 0 AS runner_points
                FROM player p
                LEFT JOIN match m on p.id = m.corp_id
                WHERE p.tid = :tid and p.is_bye = false
                group by p.id
                UNION
                SELECT p.id as id, p.tid as tid, p.p_name as name, p.corp_id as corp_id, p.runner_id as runner_id,
                p.score as score, p.sos as sos, p.esos as esos, p.bias as bias, p.active as active,
                0 AS corp_points, sum(m.runner_score) AS runner_points
                FROM player p
                LEFT JOIN match m on p.id = m.runner_id
                WHERE p.tid = :tid and p.is_bye=false
                group by p.id
            ) as t
            group by t.id, t.name, t.tid, t.corp_id, t.runner_id, t.score, t.sos, t.esos, t.bias, t.active
            order by t.score DESC, t.sos DESC, t.esos DESC 
            """),
        {
            "tid": tid
        },
    ).fetchall()
Beispiel #12
0
def rnd_one_start(tid):
    with get_db().begin() as conn:
        tourn = metadata.tables["tournament"]
        conn.execute(
            update(tourn).where(tourn.c.id == tid).values(current_rnd=1))
Beispiel #13
0
def remove_player(pid):
    db = get_db()
    with db.begin() as conn:
        conn.execute(text("DELETE FROM player WHERE id = :pid"), {"pid": pid})
Beispiel #14
0
def get_player(pid):
    return (get_db().execute(text("SELECT * FROM player WHERE id = :pid"), {
        "pid": pid
    }).fetchone())
Beispiel #15
0
def init_db():
    db = get_db()
    with current_app.open_resource("postgres.sql", "r") as f:
        for statement in sqlparse.split(f):
            db.connect().execute(text(statement))
Beispiel #16
0
def get_match(mid):
    db = get_db()
    match = metadata.tables["match"]
    return db.connect().execute(
        select(match).where(match.c.id == mid)).fetchone()
Beispiel #17
0
def get_tournaments():
    return get_db().execute("SELECT * FROM tournament ORDER BY id DESC")
Beispiel #18
0
def get_last_rnd(tid):
    q = (get_db().connect().execute(
        text("SELECT MAX(rnd) as rnd FROM match WHERE tid=:tid"), {
            "tid": tid
        }).fetchone())
    return q["rnd"]
Beispiel #19
0
def db_undrop_player(pid):
    with get_db().begin() as conn:
        conn.execute(text("UPDATE player SET active = true WHERE id = :pid"),
                     {"pid": pid})