def get_prefix(guild_id: int) -> int:
sql = "SELECT PREFIX FROM CONFIG WHERE GUILD_ID=%s"
val = (guild_id, )
cur_main.execute(sql, val)
data = cur_main.fetchone()
return data[0] if data else 0
def create_command(category: str, command: str, parameters: str,
description: str):
sql = "INSERT INTO COMMANDS (CATEGORY_ID, COMMAND, PARAMETERS, DESCRIPTION) VALUES ((SELECT ID FROM COMMAND_CATEGORIES WHERE CATEGORY=%s), %s, %s, %s)"
val = (category, command, parameters, description)
cur_main.execute(sql, val)
conn_main.commit()
async def get_user_invites(guild_id: int, user_id: int) -> list:
sql = "SELECT * FROM INVITES WHERE SERVER_ID=(SELECT ID FROM CONFIG WHERE GUILD_ID=%s) AND FROM_USER_ID=%s"
val = (guild_id, user_id)
cur_main.execute(sql, val)
invites = cur_main.fetchall()
return invites
def change_welcome_message(guild_id: int, welcome_msg: str):
sql = "UPDATE CONFIG SET WELCOME_MESSAGE=%s WHERE GUILD_ID=%s"
val = (welcome_msg, guild_id)
cur_main.execute(sql, val)
conn_main.commit()
def add_trashtalk(guild_id: int, added_on: str, added_by_user_id: int,
message: str):
sql = "INSERT INTO TRASHTALK (SERVER_ID, ADDED_ON, ADDED_BY_USER_ID, MESSAGE) VALUES ((SELECT ID FROM CONFIG WHERE GUILD_ID=%s), %s, %s, %s)"
val = (guild_id, added_on, added_by_user_id, message)
cur_main.execute(sql, val)
conn_main.commit()
def get_user_trashtalk(guild_id: int, user_id: int) -> list:
sql = "SELECT * FROM TRASHTALK_LOG WHERE SERVER_ID=(SELECT ID FROM CONFIG WHERE GUILD_ID=%s) AND FROM_USER_ID=%s"
val = (guild_id, user_id)
cur_main.execute(sql, val)
data = cur_main.fetchall()
return decode_data(data)
def get_invites_to_user(guild_id: int, invite_to_user_id: int) -> list:
sql = "SELECT * FROM INVITES WHERE SERVER_ID=(SELECT ID FROM CONFIG WHERE GUILD_ID=%s) AND TO_USER_ID=%s"
val = (guild_id, invite_to_user_id)
cur_main.execute(sql, val)
data = cur_main.fetchall()
return decode_data(data)
def log_trashtalk(guild_id: int, datum: str, from_user_id: int,
to_user_id: int):
sql = "INSERT INTO TRASHTALK_LOG (SERVER_ID, DATE, FROM_USER_ID, TO_USER_ID) VALUES ((SELECT ID FROM CONFIG WHERE GUILD_ID=%s), %s, %s, %s)"
val = (guild_id, datum, from_user_id, to_user_id)
cur_main.execute(sql, val)
conn_main.commit()
async def get_main_channel(ctx) -> discord.TextChannel:
try:
guild = ctx.guild
except AttributeError:
guild = ctx
cur_main.execute("SELECT MESSAGE_CHANNEL_ID FROM CONFIG WHERE GUILD_ID=%s",
(guild.id, ))
channel_db = cur_main.fetchone()
overwrites_main = {
guild.default_role:
discord.PermissionOverwrite(read_messages=True,
read_message_history=True,
send_messages=False)
}
if channel_db:
channel = discord.utils.get(guild.channels, id=int(channel_db[0]))
if not channel:
main_channel = await guild.create_text_channel(
name="zemo bot", overwrites=overwrites_main)
change_msg_welcome_channel(guild.id, main_channel.id,
main_channel.id)
return main_channel
else:
return channel
else:
main_channel = await guild.create_text_channel(
name="zemo bot", overwrites=overwrites_main)
change_msg_welcome_channel(guild.id, main_channel.id, main_channel.id)
return main_channel
def change_msg_welcome_channel(guild_id: int, main_channel_id: int,
welcome_channel_id: int):
sql = "UPDATE CONFIG SET MESSAGE_CHANNEL_ID=%s, WELCOME_CHANNEL_ID=%s WHERE GUILD_ID=%s"
val_1 = (main_channel_id, welcome_channel_id, guild_id)
cur_main.execute(sql, val_1)
conn_main.commit()
def get_trashtalk(guild_id: int) -> list:
sql = "SELECT MESSAGE FROM TRASHTALK WHERE SERVER_ID=(SELECT ID FROM CONFIG WHERE GUILD_ID=%s)"
val = (guild_id, )
cur_main.execute(sql, val)
data = cur_main.fetchall()
return [x[0] for x in decode_data(data) if x[0]]
def insert_user_xp(guild_id: int, user_id: int, xp: int):
sql = "INSERT INTO LEVEL (SERVER_ID, USER_ID, XP) VALUES ((SELECT ID FROM CONFIG WHERE GUILD_ID=%s), %s, %s)"
val_1 = (guild_id, user_id, xp)
try:
cur_main.execute(sql, val_1)
conn_main.commit()
except IntegrityError:
raise InvalidGuild
def update_user_xp(guild_id: int, user_id: int, xp: int):
cur_main.execute("DELETE FROM LEVEL WHERE SERVER_ID=(SELECT ID FROM CONFIG WHERE GUILD_ID=%s) AND USER_ID=%s", (guild_id, user_id))
conn_main.commit()
sql = "INSERT INTO LEVEL (SERVER_ID, USER_ID, XP) VALUES ((SELECT ID FROM CONFIG WHERE GUILD_ID=%s), %s, %s)"
val = (guild_id, user_id, xp)
cur_main.execute(sql, val)
conn_main.commit()
def get_welcome_message(guild_id: int) -> str:
sql = "SELECT WELCOME_MESSAGE FROM CONFIG WHERE GUILD_ID=%s"
val = (guild_id, )
cur_main.execute(sql, val)
data = cur_main.fetchone()
return data[0] if data and data[0] and data[0] else ""
def get_welcome_role_id(guild_id: int) -> int:
sql = "SELECT WELCOME_ROLE_ID FROM CONFIG WHERE GUILD_ID=%s"
val = (str(guild_id), )
cur_main.execute(sql, val)
data = cur_main.fetchone()
return data[0] if data else 0
def get_top_user_ranking():
cur_main.execute(
"SELECT (SELECT GUILD_ID FROM CONFIG WHERE ID=SERVER_ID), USER_ID, XP FROM LEVEL ORDER BY XP DESC LIMIT 4;"
)
data = cur_main.fetchall()
data = [(count + 1, get_username_by_id(x[1]), get_guild_name_by_id(x[0]),
xp_lvl(x[2]), x[2]) for count, x in enumerate(data)]
return data
def enable_command(guild_id: int, command: str):
if check_command_status_for_guild(guild_id, command):
return
sql = "DELETE FROM DISABLED_COMMANDS WHERE COMMAND_ID=(SELECT ID FROM COMMANDS WHERE COMMAND=%s) AND SERVER_ID=(SELECT ID FROM CONFIG WHERE GUILD_ID=%s)"
val = (command, guild_id)
cur_main.execute(sql, val)
conn_main.commit()
def disable_command(guild_id: int, command: str):
cur_main.execute("SELECT ID FROM COMMANDS WHERE COMMAND=%s", (command, ))
command_in_db = cur_main.fetchone()
if command_in_db:
sql = "INSERT INTO DISABLED_COMMANDS (SERVER_ID, COMMAND_ID) VALUES ((SELECT ID FROM CONFIG WHERE GUILD_ID=%s), %s)"
val = (guild_id, command_in_db[0])
cur_main.execute(sql, val)
conn_main.commit()
def create_admin_command(command: str, parameters: str, description: str):
sql = "INSERT INTO ADMIN_COMMANDS (COMMAND, PARAMETERS, DESCRIPTION) VALUES (%s, %s, %s);"
val = (command, parameters, description)
try:
cur_main.execute(sql, val)
conn_main.commit()
return 0
except Exception as e:
return e
def delete_command(command: str = "", command_id: int = 0):
if command:
sql = "DELETE FROM COMMANDS WHERE COMMAND=%s"
val = (command, )
elif command_id:
sql = "DELETE FROM COMMANDS WHERE ID=%s"
val = (command_id, )
else:
# TODO Create Error
raise Exception
cur_main.execute(sql, val)
conn_main.commit()
def get_all_disabled_commands_from_guild(guild_id: int) -> list:
cur_main.execute(
"SELECT COMMAND_ID FROM DISABLED_COMMANDS WHERE SERVER_ID=(SELECT ID FROM CONFIG WHERE GUILD_ID=%s)",
(guild_id, ))
command_ids = cur_main.fetchall()
data = []
for command_id in command_ids:
cur_main.execute("SELECT COMMAND FROM COMMANDS WHERE ID=%s",
command_id)
data.append(cur_main.fetchone()[0])
return data
def get_welcome_role(guild: discord.guild):
sql = "SELECT WELCOME_ROLE_ID FROM CONFIG WHERE GUILD_ID=%s"
val = (guild.id, )
cur_main.execute(sql, val)
data = cur_main.fetchone()
role = ""
if data and data[0]:
role = discord.utils.get(guild.roles, id=int(data[0]))
return role if role else 0
def get_all_guild_commands_and_category(guild_id: int):
cur_main.execute(
"SELECT CATEGORY_ID, COMMAND FROM COMMANDS WHERE ID NOT IN (SELECT COMMAND_ID FROM DISABLED_COMMANDS WHERE SERVER_ID=(SELECT ID FROM CONFIG WHERE GUILD_ID=%s))",
(guild_id, ))
commands = {
command: category
for (category, command) in cur_main.fetchall()
}
for command in commands:
cur_main.execute("SELECT CATEGORY FROM COMMAND_CATEGORIES WHERE ID=%s",
(commands[command], ))
commands[command] = cur_main.fetchone()[0]
return commands
def check_command_status_for_guild(guild_id: int, command: str):
sql = "SELECT ID FROM COMMANDS WHERE COMMAND=%s"
val = (command, )
cur_main.execute(sql, val)
data = cur_main.fetchone()
if data and data[0]:
sql_ = "SELECT * FROM DISABLED_COMMANDS WHERE COMMAND_ID=%s AND SERVER_ID=(SELECT ID FROM CONFIG WHERE GUILD_ID=%s)"
val_ = (command, guild_id)
cur_main.execute(sql_, val_)
data_ = cur_main.fetchone()
return not data_
else:
return False
def get_server_ranks(guild_id: int) -> list:
sql = "SELECT * FROM LEVEL WHERE SERVER_ID=(SELECT ID FROM CONFIG WHERE GUILD_ID=%s) ORDER BY XP ASC"
val = (guild_id,)
cur_main.execute(sql, val)
data = cur_main.fetchall()
new = {}
done_users = []
for entry in decode_data(data):
if entry[1] in done_users:
continue
for second in decode_data(data):
if second[0] == entry[0] and second[1] == entry[1]:
if second[1] in new:
new[second[1]] += second[2]
else:
new[second[1]] = second[2]
done_users.append(entry[1])
return [(k, v) for k, v in new.items()]
def get_categories_with_command_count():
cur_main.execute("SELECT CATEGORY_ID, COMMAND FROM COMMANDS;")
data = cur_main.fetchall()
dict_data = {}
for entry in data:
cur_main.execute(
"SELECT CATEGORY FROM COMMAND_CATEGORIES WHERE ID=%s;",
(entry[0], ))
category = cur_main.fetchone()
if category[0] + "_category" not in dict_data:
dict_data[category[0] + "_category"] = 1
else:
dict_data[category[0] +
"_category"] = dict_data[category[0] + "_category"] + 1
cur_main.execute("SELECT COMMAND FROM ADMIN_COMMANDS;")
dict_data["admin_commands"] = len(cur_main.fetchall())
return dict_data
def get_all_commands_with_id():
cur_main.execute("SELECT ID, COMMAND FROM COMMANDS;")
try:
return tuple([(x[0], x[1].capitalize()) for x in cur_main.fetchall()])
except IndexError:
return ()
def get_all_commands():
cur_main.execute("SELECT COMMAND FROM COMMANDS;")
return cur_main.fetchall()
def delete_admin_command(command: str):
sql = "DELETE FROM ADMIN_COMMANDS WHERE COMMAND=%s"
val = (command, )
cur_main.execute(sql, val)
conn_main.commit()
def get_xp_from_user(guild_id: int, user_id: int) -> int:
cur_main.execute("SELECT SUM(XP) FROM LEVEL WHERE SERVER_ID=(SELECT ID FROM CONFIG WHERE GUILD_ID=%s) AND USER_ID=%s", (guild_id, user_id))
data = cur_main.fetchone()
return data[0] if data and data[0] else 0