def get_user(user_id: int):
cursor = dbc.cursor()
cursor.execute("select * from users where user_id = ? LIMIT 1",
(user_id, ))
user = cursor.fetchone()
if user:
return row_to_dictionary(cursor, user)
def pitch_game(game_id, user_id, pitch):
cursor = dbc.cursor()
sql = """
insert into game_pitches(game_id, user_id, pitch)
values (?, ?, ?)
"""
cursor.execute(sql, (game_id, user_id, pitch))
dbc.commit()
def set_can_vote(user_id, can_vote):
cursor = dbc.cursor()
sql = """
update users
set can_vote = ?
where user_id = ?
"""
cursor.execute(sql, (can_vote, user_id))
dbc.commit()
def register_user(user_id, username, avatar_url):
cursor = dbc.cursor()
sql = """
insert into users (user_id, username, avatar_url)
values (?,?,?)
ON CONFLICT(user_id) DO
UPDATE SET username = ?, avatar_url = ?
where user_id = ?
"""
cursor.execute(
sql, (user_id, username, avatar_url, username, avatar_url, user_id))
dbc.commit()
def get_weeb_games():
cursor = dbc.cursor()
sql = """
select message_id, weeb_status
from joes_weeblist
"""
cursor.execute(sql)
games = cursor.fetchall()
games = [row_to_dictionary(cursor, row) for row in games]
return games
def vote_game(poll, game_id, user_id, upvote):
cursor = dbc.cursor()
sql = """
insert into votes(poll, game_id, user_id, vote)
values (?, ?, ?, ?)
ON CONFLICT(poll, game_id, user_id) DO
UPDATE SET vote = ?
where poll = ? and game_id = ? and user_id = ?
"""
cursor.execute(
sql, (poll, game_id, user_id, upvote, upvote, poll, game_id, user_id))
dbc.commit()
def get_game_platforms(id):
cursor = dbc.cursor()
sql = """
select p.name
from igdb_game_platforms as gp
left join igdb_platforms as p on gp.platform_id = p.id
where game_id = ?
"""
cursor.execute(sql, (id, ))
games = cursor.fetchall()
games = [row_to_dictionary(cursor, row) for row in games]
return games
def get_game_pitches(game_id):
cursor = dbc.cursor()
sql = """
select gp.*, u.username, u.avatar_url
from game_pitches as gp
left join users as u on u.user_id = gp.user_id
where gp.game_id = ?
order by gp.pinned desc, gp.rowid
"""
cursor.execute(sql, (game_id, ))
pitches = cursor.fetchall()
pitches = [row_to_dictionary(cursor, row) for row in pitches]
return pitches
def can_vote_messages(user_id):
cursor = dbc.cursor()
sql = """
select message_count
from message_counts
where user_id = ?
"""
cursor.execute(sql, (user_id, ))
count = cursor.fetchone()
count = row_to_dictionary(cursor, count)
if count:
return count['message_count'] >= 100
def get_game(message_id, user_id=0):
cursor = dbc.cursor()
sql = """
select gm.message_id as id, g.name, g.release_date, ifnull(g.summary,'') as summary , g.cover_url_big, u.can_vote
,case when gp.user_id is null then 1 else 0 end as can_pitch
from discord_game_map as gm
left join users as u on u.user_id = ?
left join igdb_game as g on g.id = gm.game_id
left join game_pitches_discord as gp on gp.message_id = ? and gp.user_id = u.user_id
where gm.message_id = ?
"""
cursor.execute(sql, (user_id, message_id, message_id))
game = cursor.fetchone()
return row_to_dictionary(cursor, game)
def get_game_voters(poll, game_id):
cursor = dbc.cursor()
sql = """
select u.user_id, u.username, u.avatar_url
from votes as v
left join users as u on u.user_id = v.user_id
where poll = ?
and v.vote = 1
and v.game_id = ?
order by v.rowid
"""
cursor.execute(sql, (poll, game_id))
voters = cursor.fetchall()
voters = [row_to_dictionary(cursor, row) for row in voters]
return voters
def get_game_search(search_terms):
cursor = dbc.cursor()
search_terms = search_terms.replace(" ", "%")
search_terms = f"%{search_terms}%"
sql = """
select id, name, cover_url, release_date
from igdb_game
where name_normal like ?
LIMIT 50
"""
cursor.execute(sql, (search_terms, ))
games = cursor.fetchall()
games = [row_to_dictionary(cursor, row) for row in games]
return games
def get_game(game_id, user_id=0, poll=0):
cursor = dbc.cursor()
sql = """
select id, name, cover_url_big, release_date, summary, v.vote, u.can_vote, b.block_level, b.reason,
case when gp.user_id is null then 1 else 0 end as can_pitch
from igdb_game as ig
left join users as u on u.user_id = ?
left join votes as v on v.game_id = ig.id and v.poll = ? and v.user_id = u.user_id
left join blocked_games as b on b.game_id = ig.id and b.poll = ?
left join game_pitches as gp on gp.game_id = ig.id and gp.user_id = u.user_id
where ig.id = ?
"""
cursor.execute(sql, (user_id, poll, poll, game_id))
game = cursor.fetchone()
return row_to_dictionary(cursor, game)
def get_latest_pitches():
cursor = dbc.cursor()
sql = """
select cast(gp.message_id as text) as message_id, u.user_id, gp.pitch, u.username, u.avatar_url, dm.game_name, ig.cover_url_big
from game_pitches_discord as gp
left join users as u on u.user_id = gp.user_id
left join discord_game_map as dm on dm.message_id = gp.message_id
left join igdb_game as ig on ig.id = dm.game_id
where u.user_id <> 141021676040224768
and gp.pinned <> 1
order by gp.created_at desc
limit 10
"""
cursor.execute(sql)
pitches = cursor.fetchall()
pitches = [row_to_dictionary(cursor, row) for row in pitches]
return pitches
async def calc_votes():
cursor = dbc.cursor()
sql = """
select v.game_id, sum(vote) as votes, g.name
from votes as v
left join igdb_game as g on g.id = v.game_id
left join blocked_games as b on b.game_id = g.id and b.poll = 0
where v.poll = 0
and b.block_level is null
group by v.game_id
having sum(vote) > 0
order by sum(vote) desc
"""
cursor.execute(sql)
_votes = cursor.fetchall()
_votes = [row_to_dictionary(cursor, row) for row in _votes]
votes.data = _votes
await send_votes()
def get_random_pitches():
cursor = dbc.cursor()
from app.discordbot import bot
valid_message_ids = (str(id_) for id_ in bot.valid_message_ids)
valid_message_ids = ",".join(valid_message_ids)
sql = f"""
select cast(gp.message_id as text) as message_id, u.user_id, gp.pitch, u.username, u.avatar_url, dm.game_name, ig.cover_url_big
from game_pitches_discord as gp
left join users as u on u.user_id = gp.user_id
left join discord_game_map as dm on dm.message_id = gp.message_id
left join igdb_game as ig on ig.id = dm.game_id
where gp.message_id in ({valid_message_ids})
and gp.pinned <> 1
and ig.id < 690000
order by random()
limit 10
"""
cursor.execute(sql)
pitches = cursor.fetchall()
pitches = [row_to_dictionary(cursor, row) for row in pitches]
return pitches