async def get_roll_leaderboards_info(*character):
from commands.database_handler import create_connection
conn = create_connection()
c = conn.cursor()
table = "user_roll_stats" if not character else character[0]
r = c.execute(
f"SELECT * FROM {table} INNER JOIN users ON users.discord_id = {table}.discord_id ORDER BY two_stars_count DESC LIMIT 20"
)
return r.fetchall()
async def get_roll_info(user, *character):
from commands.database_handler import create_connection
conn = create_connection()
c = conn.cursor()
table = "user_roll_stats" if not character else character[0]
if user == 523337807847227402: # Bot
query = f"SELECT SUM(two_stars_count), SUM(three_stars_count), SUM(four_stars_count) FROM user_roll_stats"
else:
query = f"SELECT * FROM {table} WHERE discord_id = {user}"
r = c.execute(query)
return r.fetchall()
async def update_rolls_db(roll_info):
from commands.database_handler import create_connection
conn = create_connection()
update_user_info_query = '''
INSERT OR IGNORE INTO users(discord_id, discord_name) VALUES (?, ?)
'''
update_users_rolls_info_query = '''
INSERT OR IGNORE INTO user_roll_stats(discord_id, two_stars_count, three_stars_count, four_stars_count) VALUES (?, ?, ?, ?)
'''
update_users_rolls_stats_query = '''
UPDATE user_roll_stats
SET two_stars_count = two_stars_count + ?,
three_stars_count = three_stars_count + ?,
four_stars_count = four_stars_count + ?
WHERE discord_id = ?
'''
c = conn.cursor()
c.execute(update_user_info_query,
(roll_info['user_id'], roll_info['user_name']))
c.execute(update_users_rolls_info_query, (roll_info['user_id'], 0, 0, 0))
c.execute(update_users_rolls_stats_query,
(roll_info['two_stars'], roll_info['three_stars'],
roll_info['four_stars'], roll_info['user_id']))
for chara in roll_info['chara']:
# have to use format here because sqlite doesn't let you use ? for table names
c.execute(
"INSERT OR IGNORE INTO {}(discord_id, two_stars_count, three_stars_count, four_stars_count) VALUES (?, ?, ?, ?)"
.format(chara), (roll_info['user_id'], 0, 0, 0))
c.execute(
'''
UPDATE {}
SET
two_stars_count = two_stars_count + ?,
three_stars_count = three_stars_count + ?,
four_stars_count = four_stars_count + ?
WHERE discord_id = ?
'''.format(chara),
(roll_info['chara'][chara]['2'], roll_info['chara'][chara]['3'],
roll_info['chara'][chara]['4'], roll_info['user_id']))
conn.commit()