302 lines
6.9 KiB
Python
302 lines
6.9 KiB
Python
from psycopg2.extensions import connection, cursor
|
|
|
|
|
|
async def user_exists(conn, user_id, guild_id):
|
|
cur = conn.cursor()
|
|
|
|
cur.execute("SELECT user_id FROM member WHERE user_id=%s AND guild_id=%s", (user_id, guild_id))
|
|
result = cur.fetchone()
|
|
|
|
cur.close()
|
|
|
|
return result is not None
|
|
|
|
|
|
async def insert_vote_options(conn, poll_id: str, vote_id: str, vote_option: str):
|
|
cur = conn.cursor()
|
|
|
|
cur.execute(
|
|
"INSERT INTO "
|
|
"vote (poll_id, vote_id, vote_option) "
|
|
"VALUES (%s, %s, %s)",
|
|
(poll_id, vote_id, vote_option)
|
|
)
|
|
|
|
conn.commit()
|
|
cur.close()
|
|
|
|
|
|
async def change_bet(conn: connection, user_id: str, guild_id: str, new_bet_amount: int):
|
|
cur: cursor = conn.cursor()
|
|
|
|
cur.execute("UPDATE member SET bet_value = %s WHERE user_id=%s AND guild_id=%s",
|
|
(new_bet_amount, user_id, guild_id))
|
|
|
|
conn.commit()
|
|
cur.close()
|
|
|
|
|
|
async def is_last_poll_created_opened(conn: connection, creator: str):
|
|
cur: cursor = conn.cursor()
|
|
|
|
cur.execute("select opened "
|
|
"from poll "
|
|
"where creator=%s "
|
|
"and opened=true",
|
|
(creator,)
|
|
)
|
|
result = cur.fetchone()
|
|
cur.close()
|
|
|
|
if result is not None:
|
|
return result[0]
|
|
return
|
|
|
|
|
|
async def close_poll(conn: connection, poll_id: str):
|
|
cur: cursor = conn.cursor()
|
|
|
|
cur.execute("UPDATE poll SET opened = false WHERE poll_id=%s",
|
|
(poll_id,))
|
|
|
|
conn.commit()
|
|
cur.close()
|
|
|
|
|
|
async def get_poll_id_opened(conn: connection, creator: str):
|
|
cur: cursor = conn.cursor()
|
|
|
|
cur.execute("select opened, poll_id, question "
|
|
"from poll "
|
|
"where creator=%s "
|
|
"and opened=true",
|
|
(creator,)
|
|
)
|
|
result = cur.fetchone()
|
|
cur.close()
|
|
|
|
return result
|
|
|
|
|
|
async def get_user_vote(conn: connection, user_id: str, poll_id: str):
|
|
cur: cursor = conn.cursor()
|
|
|
|
cur.execute(""
|
|
"SELECT vote_id "
|
|
"FROM vote_member "
|
|
"WHERE user_id=%s "
|
|
"AND poll_id=%s",
|
|
(user_id, poll_id)
|
|
)
|
|
|
|
result = cur.fetchone()
|
|
cur.close()
|
|
|
|
return result
|
|
|
|
|
|
async def get_user_by_id(conn: connection, user_id: str, guild_id: str):
|
|
cur: cursor = conn.cursor()
|
|
|
|
cur.execute(""
|
|
"SELECT username, points, bet_value "
|
|
"FROM member "
|
|
"WHERE user_id=%s "
|
|
"AND guild_id=%s",
|
|
(user_id, guild_id)
|
|
)
|
|
|
|
result = cur.fetchone()
|
|
cur.close()
|
|
|
|
return result
|
|
|
|
|
|
async def get_user_points(conn: connection, user_id: str, guild_id: str):
|
|
cur: cursor = conn.cursor()
|
|
|
|
cur.execute(""
|
|
"SELECT points "
|
|
"FROM member "
|
|
"WHERE user_id=%s "
|
|
"AND member.guild_id=%s",
|
|
(user_id, guild_id)
|
|
)
|
|
|
|
result = cur.fetchone()
|
|
cur.close()
|
|
|
|
if result is not None:
|
|
return result[0]
|
|
return
|
|
|
|
|
|
async def get_bet_value(conn: connection, user_id: str, guild_id: str):
|
|
cur: cursor = conn.cursor()
|
|
|
|
cur.execute(""
|
|
"SELECT bet_value "
|
|
"FROM member "
|
|
"WHERE user_id=%s "
|
|
"AND member.guild_id=%s",
|
|
(user_id, guild_id)
|
|
)
|
|
|
|
result = cur.fetchone()
|
|
cur.close()
|
|
|
|
if result is not None:
|
|
return result[0]
|
|
return
|
|
|
|
|
|
async def bet(conn, vote_id: str, poll_id: str, user_id: str, guild_id: str, bet_value: int):
|
|
cur = conn.cursor()
|
|
|
|
cur.execute(
|
|
"INSERT INTO "
|
|
"vote_member (user_id, poll_id, guild_id, vote_id, bet_value) "
|
|
"VALUES (%s, %s, %s, %s, %s)",
|
|
(user_id, poll_id, guild_id, vote_id, bet_value)
|
|
)
|
|
|
|
conn.commit()
|
|
cur.close()
|
|
|
|
|
|
async def get_bet_value_multipliers(conn: connection, poll_id: str):
|
|
cur = conn.cursor()
|
|
|
|
cur.execute("SELECT vote_id,(1 + (1 - (SUM(vote_member.bet_value) / total.sum))) "
|
|
"FROM vote_member "
|
|
"CROSS JOIN (SELECT SUM(bet_value) AS sum FROM vote_member WHERE poll_id=%s) AS total "
|
|
"WHERE poll_id=%s "
|
|
"GROUP BY vote_id, total.sum",
|
|
(poll_id, poll_id))
|
|
result = cur.fetchall()
|
|
|
|
return result
|
|
|
|
|
|
async def get_vote_id_by_vote_option_and_poll_id(conn: connection, vote_option: str, poll_id: str):
|
|
cur = conn.cursor()
|
|
|
|
cur.execute("SELECT vote_id FROM vote "
|
|
"WHERE vote_option=%s "
|
|
"AND poll_id=%s",
|
|
(vote_option, poll_id))
|
|
result = cur.fetchone()
|
|
|
|
if result is not None:
|
|
return result[0]
|
|
return
|
|
|
|
|
|
async def get_users_that_voted(conn: connection, vote_id: str):
|
|
cur = conn.cursor()
|
|
|
|
cur.execute("select user_id "
|
|
"from vote_member "
|
|
"where vote_id=%s",
|
|
(vote_id,))
|
|
results = cur.fetchall()
|
|
|
|
return results
|
|
|
|
|
|
async def guild_exists(conn, guild_id):
|
|
cur = conn.cursor()
|
|
|
|
cur.execute("SELECT guild_id FROM guild WHERE guild_id=%s", (guild_id,))
|
|
result = cur.fetchone()
|
|
|
|
cur.close()
|
|
|
|
return result is not None
|
|
|
|
|
|
async def insert_poll(conn, creator, poll_id, guild_id, question):
|
|
cur = conn.cursor()
|
|
|
|
cur.execute(
|
|
"INSERT INTO "
|
|
"poll (creator, poll_id, guild_id, question, opened) "
|
|
"VALUES (%s, %s, %s, %s, true)",
|
|
(creator, poll_id, guild_id, question)
|
|
)
|
|
|
|
conn.commit()
|
|
cur.close()
|
|
|
|
|
|
async def insert_guild(conn, guild_id):
|
|
cur = conn.cursor()
|
|
cur.execute(
|
|
"INSERT INTO "
|
|
"guild (guild_id) "
|
|
"VALUES (%s)",
|
|
(guild_id,)
|
|
)
|
|
|
|
conn.commit()
|
|
cur.close()
|
|
|
|
|
|
async def insert_user(conn, user_id, guild_id, username):
|
|
cur = conn.cursor()
|
|
|
|
cur.execute(
|
|
"INSERT INTO "
|
|
"member (user_id, guild_id, username) "
|
|
"VALUES (%s, %s, %s)",
|
|
(user_id, guild_id, username)
|
|
)
|
|
|
|
conn.commit()
|
|
cur.close()
|
|
|
|
|
|
async def get_users_and_bet_value_by_vote_id(conn: connection, vote_id: str):
|
|
cur = conn.cursor()
|
|
|
|
cur.execute("SELECT user_id, bet_value FROM vote_member "
|
|
"WHERE vote_id=%s",
|
|
(vote_id,)
|
|
)
|
|
result = cur.fetchall()
|
|
|
|
cur.close()
|
|
|
|
return result
|
|
|
|
|
|
async def add_user_points(conn: connection, user_id: str, bet_value: int):
|
|
cur = conn.cursor()
|
|
|
|
cur.execute(
|
|
"UPDATE member "
|
|
"SET points = points + %s "
|
|
"WHERE user_id=%s",
|
|
(bet_value, user_id)
|
|
)
|
|
|
|
conn.commit()
|
|
cur.close()
|
|
|
|
|
|
async def minus_user_points(conn: connection, user_id: str, bet_value: int):
|
|
try:
|
|
cur = conn.cursor()
|
|
|
|
cur.execute(
|
|
"UPDATE member "
|
|
"SET points = points - %s "
|
|
"WHERE user_id=%s",
|
|
(bet_value, user_id)
|
|
)
|
|
|
|
conn.commit()
|
|
cur.close()
|
|
except Exception:
|
|
conn.rollback()
|