def get_oofs(
cls,
server: discord.Guild,
user: Union[discord.User, discord.Member] = None) -> Optional[int]:
"""Returns the number of times the provided user oofed on the provided server.
If not provided a user, returns the total number of oofs on the server.
"""
server_data_manager.ensure_table_existence_for_server(
server.id, cls.TABLE_NAME, cls.TABLE_COLUMNS)
if user is None:
server_data_manager.servers[server.id]['db_cursor'].execute(
'SELECT oofs FROM oof', )
result = server_data_manager.servers[
server.id]['db_cursor'].fetchall()
if result:
oofs = 0
for oofer in result:
oofs += oofer['oofs']
else:
oofs = None
else:
server_data_manager.servers[server.id]['db_cursor'].execute(
'SELECT oofs FROM oof WHERE user_id = ?', (user.id, ))
result = server_data_manager.servers[
server.id]['db_cursor'].fetchone()
oofs = None if result is None else result['oofs']
return oofs
def get_oofers(cls, server: discord.Guild) -> List[Dict[str, int]]:
"""Returns a tuple of users who have oofed on the provided server, sorted by the number of oofs, descending."""
server_data_manager.ensure_table_existence_for_server(
server.id, cls.TABLE_NAME, cls.TABLE_COLUMNS)
server_data_manager.servers[server.id]['db_cursor'].execute(
'SELECT user_id, oofs FROM oof')
rows = (server_data_manager.dict_from_row(row) for row in
server_data_manager.servers[server.id]['db_cursor'].fetchall())
sorted_rows = sorted(rows, key=lambda row: row['oofs'], reverse=True)
return sorted_rows
def is_member_registered(cls, server: discord.Guild,
member: discord.Member) -> bool:
"""Returns the provided member's birthday or None if the member hasn't set their birthday."""
server_data_manager.ensure_table_existence_for_server(
server.id, cls.TABLE_NAME, cls.TABLE_COLUMNS)
server_data_manager.servers[server.id]['db_cursor'].execute(
'SELECT birthday_date FROM birthday WHERE user_id = ?',
(member.id, ))
result = server_data_manager.servers[server.id]['db_cursor'].fetchone()
is_member_registered = False if result is None else True
return is_member_registered
def get_birthday_date(cls, server: discord.Guild,
member: discord.Member) -> Optional[dt.date]:
"""Returns the provided member's birthday or None if the member hasn't set their birthday."""
server_data_manager.ensure_table_existence_for_server(
server.id, cls.TABLE_NAME, cls.TABLE_COLUMNS)
server_data_manager.servers[server.id]['db_cursor'].execute(
'SELECT birthday_date FROM birthday WHERE user_id = ?',
(member.id, ))
result = server_data_manager.servers[server.id]['db_cursor'].fetchone()
birthday_date = (
None if result is None or result['birthday_date'] is None else
dt.datetime.strptime(result['birthday_date'], '%Y-%m-%d').date())
return birthday_date
def add_event(
cls, *, event_type: str, server: discord.Guild, channel: discord.TextChannel = None,
executing_user: discord.Member = None, subject_user = Union[discord.Guild, discord.TextChannel,
discord.VoiceChannel, discord.Member, discord.Message], posix_timestamp: int = None, reason: str = None
):
"""Adds an event with specified parameters to the database of the provided server."""
channel_id = None if channel is None else channel.id
executing_user_id = None if executing_user is None else executing_user.id
if posix_timestamp is None:
posix_timestamp = int(dt.datetime.utcnow().timestamp())
server_data_manager.ensure_table_existence_for_server(server.id, cls.TABLE_NAME, cls.TABLE_COLUMNS)
server_data_manager.servers[server.id]['db_cursor'].execute(
f'''INSERT INTO {cls.TABLE_NAME}(event_type, channel_id, executing_user_id, subject_user_id,
posix_timestamp, reason) VALUES (?, ?, ?, ?, ?, ?)''',
(event_type, channel_id, executing_user_id, subject_user.id, posix_timestamp, reason)
)
server_data_manager.servers[server.id]['db'].commit()
def get_events(
cls, *, server: discord.Guild, event_types: Union[str, tuple, list] = None,
channel: discord.TextChannel = None, subject_user: Union[
discord.Guild, discord.TextChannel, discord.VoiceChannel, discord.Member, discord.Message
] = None
) -> tuple:
"""Returns a list of events on the provided server."""
server_data_manager.ensure_table_existence_for_server(server.id, cls.TABLE_NAME, cls.TABLE_COLUMNS)
condition_strings = []
condition_variables = []
if isinstance(event_types, str):
condition_strings.append('event_type = ?')
condition_variables.append(event_types)
elif isinstance(event_types, (tuple, list)) and event_types:
condition_strings.append(f'({" OR ".join(["event_type = ?" for _ in event_types])})')
condition_variables.extend(event_types)
if channel is not None:
condition_strings.append('channel_id = ?')
condition_variables.append(channel.id)
if subject_user is not None:
condition_strings.append('subject_user_id = ?')
condition_variables.append(subject_user.id)
combined_condition_string = f'WHERE {" AND ".join(condition_strings)}'
server_data_manager.servers[server.id]['db_cursor'].execute(
f'''SELECT event_id, event_type, channel_id, executing_user_id, subject_user_id,
posix_timestamp, reason FROM {cls.TABLE_NAME} {combined_condition_string if condition_strings else ""}''',
condition_variables
)
results_rows = server_data_manager.servers[server.id]['db_cursor'].fetchall()
results_dicts = map(server_data_manager.dict_from_row, results_rows)
events = [
cls.Event(
event_id=result['event_id'], event_type=result['event_type'], server_id=server.id,
channel_id=result['channel_id'], executing_user_id=result['executing_user_id'],
subject_user_id=result['subject_user_id'], posix_timestamp=result['posix_timestamp'],
reason=result['reason']
) for result in results_dicts
]
new_to_old_events = tuple(reversed(events)) # reverse the order of events so that the'yre ordered new to old
return new_to_old_events
def get_members_with_birthday(
cls,
server: discord.Guild,
*,
year: int = None,
month: int = None,
day: int = None) -> Tuple[Dict[str, Union[int, dt.date]]]:
server_data_manager.ensure_table_existence_for_server(
server.id, cls.TABLE_NAME, cls.TABLE_COLUMNS)
condition_strings = []
condition_variables = []
if year is not None:
condition_strings.append(
'''CAST(strftime('%Y', birthday_date) AS integer) = ?''')
condition_variables.append(year)
if month is not None:
condition_strings.append(
'''CAST(strftime('%m', birthday_date) AS integer) = ?''')
condition_variables.append(month)
if day is not None:
condition_strings.append(
'''CAST(strftime('%d', birthday_date) AS integer) = ?''')
condition_variables.append(day)
combined_condition_string = f'WHERE {" AND ".join(condition_strings)}'
server_data_manager.servers[server.id]['db_cursor'].execute(
f'SELECT user_id, birthday_date FROM birthday {combined_condition_string if condition_strings else ""}',
condition_variables)
rows = ({
'user_id':
row['user_id'],
'birthday_date':
dt.datetime.strptime(row['birthday_date'], '%Y-%m-%d').date()
} for row in server_data_manager.servers[server.id]
['db_cursor'].fetchall())
sorted_rows = tuple(sorted(rows, key=lambda row: row['birthday_date']))
return sorted_rows