def channel_activity(channel=None):
connection = utils.connect()
cursor = connection.cursor()
# All channels
if channel is None:
cursor.execute("SELECT * FROM channel_activity")
return cursor.fetchall()
return getOrAddChannel(channel.id)
def add(userid, link):
user = get_user(userid)
connection = utils.connect()
cursor = connection.cursor()
if len(user) == 0:
cursor.execute("INSERT INTO twitch(userid, link) VALUES (%s, %s)", (int(userid), str(link),))
else:
cursor.execute("""UPDATE twitch SET link = %s WHERE userid = %s""", (link, int(userid),))
connection.commit()
def blacklist(user, bl=True):
connection = utils.connect()
cursor = connection.cursor()
cursor.execute("UPDATE info "
"SET poke_blacklist = %s WHERE userid = %s", (
bl,
int(user),
))
connection.commit()
def getAllPlatDinks():
connection = utils.connect()
cursor = connection.cursor()
cursor.execute("SELECT userid, amount FROM inventory WHERE itemid = 1")
result = cursor.fetchall()
dic = {}
for user in result:
dic[str(user[0])] = user[1]
return dic
def insert(id, name, fields):
if getMeme(name) is not None:
return "Deze meme staat al in de database."
connection = utils.connect()
cursor = connection.cursor()
cursor.execute("INSERT INTO memes(id, name, fields) VALUES (%s, %s, %s)", [int(id), name.lower(), int(fields)])
connection.commit()
return "{} is toegevoegd aan de database.".format(name[0].upper() + name[1:].lower())
def add(quote, date, location):
connection = utils.connect()
cursor = connection.cursor()
cursor.execute(
"INSERT INTO trump(quote, date, location) VALUES (%s, %s, %s)", (
str(quote),
str(date),
str(location),
))
connection.commit()
def _find_by_name(message):
"""
Find a command by its name
"""
connection = utils.connect()
cursor = connection.cursor()
cursor.execute("SELECT * FROM custom_commands WHERE name = %s", (message,))
return cursor.fetchone()
def add_user(userid, day, month, year):
connection = utils.connect()
cursor = connection.cursor()
if get_user(userid):
cursor.execute("UPDATE birthdays SET day = %s, month = %s, year = %s WHERE userid = %s",
(int(day), int(month), int(year), int(userid),))
else:
cursor.execute("INSERT INTO birthdays(userid, day, month, year) VALUES (%s, %s, %s, %s)",
(int(userid), int(day), int(month), int(year),))
connection.commit()
def update(userid, column, value):
_ = getOrAddUser(userid)
connection = utils.connect()
cursor = connection.cursor()
query = "UPDATE personalstats " \
"SET {} = %s " \
"WHERE userid = %s".format(column)
cursor.execute(query, (value, userid,))
connection.commit()
statsTracker(column)
def getOrAddUser(userid):
connection = utils.connect()
cursor = connection.cursor()
query = "SELECT * FROM currencytable WHERE userid = %s"
cursor.execute(query, (int(userid), ))
result = cursor.fetchall()
# User didn't exist yet, so create a new default file
if len(result) == 0:
createNewUser(userid, connection)
return getOrAddUser(userid)
return result[0]
def sell(userid, itemid, sold, amount):
connection = utils.connect()
cursor = connection.cursor()
# Don't store amount: 0
if sold == amount:
cursor.execute("DELETE FROM inventory WHERE userid = %s AND itemid = %s", (userid, itemid,))
return connection.commit()
cursor.execute("UPDATE inventory SET amount = %s WHERE userid = %s AND itemid = %s", (amount - sold, userid, itemid))
return connection.commit()
def getMeme(name):
connection = utils.connect()
cursor = connection.cursor()
cursor.execute("SELECT * FROM memes WHERE name like %s", ["%" + name.lower() + "%"])
result = cursor.fetchall()
if len(result) == 0:
return None
meme = Meme(result[0][0], result[0][1], result[0][2])
return meme
def imprison(userid, bailsum, days, daily):
connection = utils.connect()
cursor = connection.cursor()
cursor.execute(
"INSERT INTO prison(userid, bail, days, daily) VALUES (%s, %s, %s, %s)",
(
int(userid),
float(bailsum),
int(days),
float(daily),
))
connection.commit()
def add_command(name: str, response: str):
"""
Add a new custom command
"""
name = _clean(name.lower())
if not is_name_free(name):
return "Er is al een commando met deze naam."
connection = utils.connect()
cursor = connection.cursor()
cursor.execute("INSERT INTO custom_commands(name, response) VALUES (%s, E%s)", (name, response,))
connection.commit()
def addItem(userid, itemid, amount, inv):
connection = utils.connect()
cursor = connection.cursor()
# It's already in there, add more to the counter
if inv:
amount += inv[0][0]
cursor.execute("UPDATE inventory SET amount = %s WHERE userid = %s AND itemid = %s", (amount, userid, itemid))
connection.commit()
return
# Doesn't own this item yet, add a new row
cursor.execute("INSERT INTO inventory(userid, itemid, amount) VALUES (%s, %s, %s)", (userid, itemid, amount))
connection.commit()
def removeMuttn(message):
if str(message.author.id) == constants.didierId:
return
connection = utils.connect()
cursor = connection.cursor()
user = getOrAddUser(message.author.id)
cursor.execute("UPDATE muttn SET count = %s WHERE userid = %s", (
int(user[2]) - 1,
int(message.author.id),
))
connection.commit()
def getOrAddUser(userid):
connection = utils.connect()
cursor = connection.cursor()
cursor.execute("SELECT * FROM remind WHERE userid = %s", (int(userid), ))
res = cursor.fetchall()
if not res:
cursor.execute("INSERT INTO remind(userid) VALUES(%s)",
(int(userid), ))
connection.commit()
return getOrAddUser(userid)
return res[0]
def getOrAddChannel(channelid: int):
connection = utils.connect()
cursor = connection.cursor()
cursor.execute("SELECT * FROM channel_activity WHERE channel_id = %s", (channelid,))
res = cursor.fetchall()
if not res:
cursor.execute("INSERT INTO channel_activity(channel_id, message_count) VALUES (%s, 0)", (channelid,))
connection.commit()
return getOrAddChannel(channelid)
return res
def dinksAll(userid):
platinumDinks = 0
connection = utils.connect()
cursor = connection.cursor()
cursor.execute(
"SELECT amount FROM inventory WHERE userid = %s AND itemid = %s", (
int(userid),
1,
))
result = cursor.fetchall()
if result:
platinumDinks = result[0][0]
return {"dinks": dinks(userid), "platinum": platinumDinks}
def is_name_free(name) -> bool:
"""
Check if a name is already in use by a command
Includes aliases
"""
connection = utils.connect()
cursor = connection.cursor()
cursor.execute("SELECT id from custom_commands WHERE name = %s", (name,))
if cursor.fetchone():
return False
cursor.execute("SELECT id FROM custom_command_aliases WHERE alias = %s", (name,))
return cursor.fetchone() is None
def add(userid, link):
user = get_user(userid)
connection = utils.connect()
cursor = connection.cursor()
if len(user) == 0:
cursor.execute(
"INSERT INTO githubs(userid, githublink) VALUES (%s, %s)", (
int(userid),
str(link),
))
else:
cursor.execute(
"""UPDATE githubs SET githublink = %s WHERE userid = %s""", (
user[0][0] + "\n" + link,
int(userid),
))
connection.commit()
def _find_by_alias(message):
"""
Find a command by one of its aliases
"""
connection = utils.connect()
cursor = connection.cursor()
cursor.execute("SELECT command FROM custom_command_aliases WHERE alias = %s", (message,))
res = cursor.fetchone()
# No command matched this alias
if not res:
return ()
cursor.execute("SELECT * FROM custom_commands WHERE id = %s", (res,))
return cursor.fetchone()
def switchReminder(userid, column):
connection = utils.connect()
cursor = connection.cursor()
columns = ["id", "nightly", "les"]
res = getOrAddUser(userid)
# Switch the column value
to = not (res[columns.index(column)])
cursor.execute(
"UPDATE remind SET {} = %s WHERE userid = %s".format(column), (
to,
int(userid),
))
connection.commit()
return to
def dailyLowers():
connection = utils.connect()
cursor = connection.cursor()
# Release people from prison on their last day
cursor.execute("DELETE FROM prison WHERE days = 1")
connection.commit()
# Get all remaining users
cursor.execute("SELECT * FROM prison")
prisoners = cursor.fetchall()
for prisoner in prisoners:
cursor.execute(
"UPDATE prison "
"SET bail = %s, days = %s "
"WHERE userid = %s", (
float(prisoner[1]) - float(prisoner[3]),
int(prisoner[2]) - 1,
int(prisoner[0]),
))
connection.commit()
def add_alias(command: str, alias: str):
"""
Add an alias for a command
Assumes the command exists
"""
command = _clean(command.lower())
alias = _clean(alias.lower())
# Base command doesn't exist
if is_name_free(command):
return "Er is geen commando met deze naam."
# Alias already exists
if not is_name_free(alias):
return "Er is al een commando met deze naam."
# Find the id of the base command
command_id = CustomCommand(*_find_by_name(command)).id
connection = utils.connect()
cursor = connection.cursor()
cursor.execute("INSERT INTO custom_command_aliases(command, alias) VALUES(%s, %s)", (command_id, alias,))
connection.commit()
def getAllRows():
connection = utils.connect()
cursor = connection.cursor()
cursor.execute("""SELECT * FROM currencytable""")
return cursor.fetchall()
def getAllRows():
connection = utils.connect()
cursor = connection.cursor()
cursor.execute("SELECT * FROM muttn")
return cursor.fetchall()
def getUser(userId):
connection = utils.connect()
cursor = connection.cursor()
cursor.execute("SELECT * FROM prison WHERE userid = %s", (int(userId), ))
return cursor.fetchall()
def remove(userId):
connection = utils.connect()
cursor = connection.cursor()
cursor.execute("DELETE FROM prison WHERE userid = %s", (int(userId), ))
connection.commit()
def get_user(userid):
connection = utils.connect()
cursor = connection.cursor()
cursor.execute("SELECT githublink FROM githubs WHERE userid = %s",
(int(userid), ))
return cursor.fetchall()
