async def addegg(self, ctx):
# Set flow to 'ADD' to mark we're in the adding flow
global egglist_temp
for egg in egglist_temp:
if egg.user == ctx.author.id:
await ctx.send("Du hast schon ein Ei offen, du Ei!")
if not egg.triggers:
await ctx.send("Gib mir ein Triggerwort, pls.")
else:
await ctx.send("Sag mir was der bot antworten soll, pls.")
return
# If no open egg is found, make a new one for this author
# First grab the highest current id from the DB and add 1
self.cnx = check_connection(self.cnx)
self.cursor = self.cnx.cursor(buffered=True)
query = (f'SELECT MAX(id) FROM eastereggs')
self.cursor.execute(query)
self.cnx.commit()
row = self.cursor.fetchone()
if row[0] is None:
egg_id = 0
else:
egg_id = int(row[0]) + 1
new_egg = Easteregg(ctx.author.id, egg_id)
egglist_temp.append(new_egg)
await ctx.send("Kk, los geht's! Erstes Triggerwort?")
def getUserStreak(self, isMax, user, resetDate, today):
# Check DB connection
self.cnx = check_connection(self.cnx)
self.cursor = self.cnx.cursor(buffered=True)
query = (f"""SELECT c.dt,
IFNULL(a.asdf, '0')
FROM `calendar` c
LEFT OUTER JOIN
(SELECT DATE, COUNT(asdf) AS asdf
FROM `asdf`
WHERE asdf > 0
AND author = {user}
GROUP BY 1) AS a ON c.dt = a.date
WHERE dt BETWEEN '{resetDate}' AND '{today}'
ORDER BY c.dt;""")
self.cursor.execute(query)
self.cnx.commit()
# If max streak is requested, grab max
if isMax:
if self.cursor.rowcount > 0:
rows = self.cursor.fetchall()
maxStreak = 0
currStreak = 0
for row in rows:
if int(row[1]) > 0:
currStreak += 1
elif int(row[1]) == 0:
if currStreak > maxStreak:
maxStreak = currStreak
currStreak = 0
return max(maxStreak, currStreak)
# Otherwise grab the active streak and return it
else:
if self.cursor.rowcount > 0:
rows = self.cursor.fetchall()
rows_sorted = sorted(rows, key=lambda x: x[0], reverse=True)
activeStreak = 0
for row in rows_sorted:
if int(row[1]) > 0:
activeStreak += 1
elif int(row[1]) == 0:
break
return activeStreak
def dbOperation(self, operation, egg):
# Check DB connection
self.cnx = check_connection(self.cnx)
self.cursor = self.cnx.cursor(buffered=True)
if operation.name == 'CREATE':
triggers = "|".join(egg.triggers)
responses = "|".join(egg.responses)
query = (
f'INSERT INTO `eastereggs` (`id`, `date`, `author`, `triggers`, `responses`) VALUES ("{egg.id}", CURRENT_DATE(), "{egg.user}", "{triggers}", "{responses}")'
)
self.cursor.execute(query)
self.cnx.commit()
async def levels(self, ctx):
async with ctx.channel.typing():
# Check DB connection
self.cnx = check_connection(self.cnx)
self.cursor = self.cnx.cursor(buffered=True)
# Grab all records
query = (f'SELECT author, xp, level FROM levels ORDER BY xp DESC')
self.cursor.execute(query)
self.cnx.commit()
if self.cursor.rowcount == 0:
await ctx.message.channel.send(
f'Keiner da, keiner hat levels. :person_shrugging:')
# Build Lists with authors, author_urls, levels, xp, lvlxp and nlvlxp
else:
author = []
authorurl = []
level = []
xp = []
lvlxp = []
nlvlxp = []
for row in self.cursor.fetchall():
user = ctx.guild.get_member(row[0])
if user == None: # Skip User if not found in Guild
continue
else: # User in guild > check if Nick or use Name instead
if user.nick == None:
name = user.name
else:
name = user.nick
author.append(name)
authorurl.append(user.avatar_url)
xp.append(row[1])
level.append(row[2])
nlvlxp.append(5 * (row[2]**2) + 50 * row[2] + 100)
rest_xp = row[1]
for i in range(0, 500):
xp_needed = 5 * (i**2) + 50 * i + 100
rest_xp -= xp_needed
if rest_xp < 0:
rest_xp += xp_needed
break
lvlxp.append(rest_xp)
# create Image
await createLeaderboard(author, authorurl, level, xp, lvlxp,
nlvlxp)
await ctx.send(
file=discord.File('storage/levels/leaderboard.png'))
async def printStats(self, ctx, keyword):
async with ctx.channel.typing():
# Check DB connection
self.cnx = check_connection(self.cnx)
self.cursor = self.cnx.cursor(buffered=True)
query = (f"""SELECT author,
count({keyword})
FROM `asdf`
WHERE {keyword} > 0
GROUP BY 1
ORDER BY 2 DESC;""")
self.cursor.execute(query)
self.cnx.commit()
total = 0
if self.cursor.rowcount > 0:
rows = self.cursor.fetchall()
r = ''
# ASDF ranking should be green
if keyword == "asdf":
colour = discord.Colour.from_rgb(25, 100, 25)
else:
colour = discord.Colour.from_rgb(153, 0, 0)
asdfEmbed = discord.Embed(title=f'{keyword.upper()} ranking',
colour=colour)
for row in rows:
total += int(row[1])
user = ctx.guild.get_member(int(row[0]))
if user == None: # Skip User if not found in Guild
continue
else: # User in guild > check if Nick or use Name instead
if user.nick == None:
user = user.name
else:
user = user.nick
r += f'{user}: {row[1]}\n'
asdfEmbed.add_field(name=f'**Gesamt: {total}**', value=r)
if total == 0:
await ctx.send(f'```Noch keine {keyword}s ... bis jetzt.```')
else:
await ctx.send(embed=asdfEmbed)
async def howmanyeggs(self, ctx):
# Check DB connection
self.cnx = check_connection(self.cnx)
self.cursor = self.cnx.cursor(buffered=True)
query = (f'SELECT count(*) FROM eastereggs')
self.cursor.execute(query)
self.cnx.commit()
if self.cursor.rowcount == 0:
await ctx.send(
"Das Nichts nichtet. !addegg verwenden, um ein neues zu erstellen."
)
else:
row = self.cursor.fetchone()
count = row[0]
await ctx.send(f'Es gibt {count} custom eastereggs!1elf')
async def quote(self, ctx, offset=3, id=None):
'''returns a random quote from our history'''
if id is not None:
try:
number = int(id)
except:
await ctx.message.add_reaction('🥚')
await ctx.message.add_reaction('👏')
await ctx.send(
f'Schaut "{id}" für dich aus wie eine Zahl, du Nudeldrucker?! :F'
)
return
else:
number = random.randint(1, 453299)
query = (f"""SELECT number, `time`, author, message
FROM wlc_quotes_v2 WHERE number IN
(SELECT nr FROM
(SELECT {number} AS nr""")
for i in range(1, offset):
query += f' UNION SELECT {number+i} AS nr'
query += ') AS result)'
# Check DB connection
self.cnx = check_connection(self.cnx)
self.cursor = self.cnx.cursor(buffered=True)
# Execute query
self.cursor.execute(query)
self.cnx.commit()
if self.cursor.rowcount > 0:
rows = self.cursor.fetchall()
r = f'**#{rows[0][0]}**\n\n'
timestamp = rows[0][1]
for row in rows:
r += f'**{row[2]}**\n *- {row[3]}*\n'
embedQuote = discord.Embed(colour=discord.Colour.from_rgb(
22, 136, 173),
description=r)
embedQuote.set_footer(text=timestamp)
await ctx.send(embed=embedQuote)
else:
await ctx.send("Kaputt.")
async def rank(self, ctx):
# Check DB connection
self.cnx = check_connection(self.cnx)
self.cursor = self.cnx.cursor(buffered=True)
# Grab the author's record
query = (
f'SELECT author, xp, level FROM levels WHERE author = {ctx.message.author.id}'
)
self.cursor.execute(query)
self.cnx.commit()
if self.cursor.rowcount == 0:
await ctx.message.channel.send(
f"Du hast noch keinen Rank, du Nudel!")
# Build an embed with the current level and xp and send it
else:
row = self.cursor.fetchone()
xp = row[1]
level_current = row[2]
nlvlxp = 5 * (level_current**2) + 50 * level_current + 100
rest_xp = xp
for i in range(0, 500):
xp_needed = 5 * (i**2) + 50 * i + 100
rest_xp -= xp_needed
if rest_xp < 0:
rest_xp += xp_needed
break
if ctx.author.nick == None:
name = ctx.author.name
else:
name = ctx.author.nick
query = ('SELECT author, xp FROM levels ORDER BY xp DESC')
self.cursor.execute(query)
self.cnx.commit()
rank = 0
for row in self.cursor.fetchall():
rank += 1
if ctx.author.id == row[0]:
break
await createRankcard(name, ctx.author.avatar_url, rank, xp,
level_current, rest_xp, nlvlxp)
await ctx.send(file=discord.File(f"storage/levels/{name}.png"))
def updatePoints(self, message, keyword):
if keyword == 'asdf':
asdf = 1
fail = 0
else:
asdf = 0
fail = 1
# Check DB connection
self.cnx = check_connection(self.cnx)
self.cursor = self.cnx.cursor(buffered=True)
# Add the record
query = (f"""INSERT INTO `asdf` (`date`, `author`, `asdf`, `fail`)
VALUES (CURRENT_DATE(), '{message.author.id}', '{asdf}', '{fail}')"""
)
self.cursor.execute(query)
self.cnx.commit()
# Execute updateXp from levels to give or remove bonus xp
asyncio.create_task(self.levels.updateXp(message, keyword))
async def scrapegg(self, ctx, delete_id=None):
global egglist_active
if delete_id is None:
await ctx.send(
"Ohne ID wird's nix. Um die IDs anzuzeigen, !listeggs verwenden."
)
return
else:
try:
int(delete_id)
# Check DB connection
self.cnx = check_connection(self.cnx)
self.cursor = self.cnx.cursor(buffered=True)
query = (
f'SELECT id, author FROM eastereggs WHERE id = {delete_id}'
)
self.cursor.execute(query)
self.cnx.commit()
if self.cursor.rowcount == 0:
await ctx.send(
"ID nicht gefunden. !addegg verwenden, um ein neues zu erstellen."
)
else:
row = self.cursor.fetchone()
if int(row[1]) != ctx.author.id:
await ctx.send(
"Eastereggs von anderen löschen is uncool! !listeggs um deine eigenen anzuzeigen."
)
else:
query = (
f'DELETE FROM eastereggs WHERE id = {delete_id}')
self.cursor.execute(query)
self.cnx.commit()
del egglist_active[int(delete_id)]
await ctx.send("Gelöscht :(")
except Exception:
await ctx.send("Kaputte ID, bitte nur Zahlen eingeben.")
async def listeggs(self, ctx):
# Check DB connection
self.cnx = check_connection(self.cnx)
self.cursor = self.cnx.cursor(buffered=True)
query = (
f'SELECT id, date, triggers, responses FROM eastereggs WHERE author = {ctx.author.id}'
)
self.cursor.execute(query)
self.cnx.commit()
if self.cursor.rowcount == 0:
await ctx.send(
"Das Nichts nichtet. !addegg verwenden, um eins zu erstellen.")
else:
rows = self.cursor.fetchall()
r = '```\n'
for row in rows:
r += ('#' + str(row[0]) + ' - ' + str(row[1]) +
' - Trigger(s): ' + row[2] + ' - Antwort: ' + row[3] +
'\n')
r += '```'
await ctx.send(r)
async def updateXp(self, message, keyword=None):
# Wait before the asdf period is over to distribute bonus xp
if keyword is not None:
await asyncio.sleep(180)
# Check DB connection
self.cnx = check_connection(self.cnx)
self.cursor = self.cnx.cursor(buffered=True)
# Grab the author's record
query = (
f'SELECT author, xp, level FROM levels WHERE author = {message.author.id}'
)
self.cursor.execute(query)
self.cnx.commit()
# If the author is new, create a row, give them 20xp and level 1
if self.cursor.rowcount == 0:
query = (
f'INSERT INTO `levels` (`author`, `xp`, `level`) VALUES ("{message.author.id}", "20", "0")'
)
self.cursor.execute(query)
self.cnx.commit()
else:
row = self.cursor.fetchone()
# Check the keyword to determine xp modifier
if keyword == 'asdf':
new_xp = row[1] + 1337
elif keyword == 'fail':
if row[1] < 1337:
new_xp = 0
else:
new_xp = row[1] - 1337
else:
new_xp = row[1] + 20
level_current = row[2]
loop_xp = 0
# Calculate the level limits and check if there was a level-up
for i in range(0, 500):
xp_needed = 5 * (i**2) + 50 * i + 100
loop_xp += xp_needed
if loop_xp > new_xp:
level_reached = i
break
# Check for levelup, leveldown or no change
if level_reached > level_current:
query = (
f'UPDATE `levels` SET `xp`={new_xp}, `level`={level_reached} WHERE author = {message.author.id}'
)
await message.channel.send(
f"Grz {message.author.mention}, du Heisl! **Level {level_reached}!**"
)
elif level_reached < level_current:
query = (
f'UPDATE `levels` SET `xp`={new_xp}, `level`={level_reached} WHERE author = {message.author.id}'
)
await message.channel.send(
f"Grz zum verlorenen Level, {message.author.mention}, du Heisl! **Level {level_reached}!**"
)
else:
query = (
f'UPDATE `levels` SET `xp`={new_xp} WHERE author = {message.author.id}'
)
self.cursor.execute(query)
self.cnx.commit()
# If regular message, wait for 30 seconds and then remove the author from the active list to allow for new XP
if keyword == None:
await asyncio.sleep(30)
self.active_authors.remove(message.author.id)
else:
return
async def printStreak(self, ctx):
async with ctx.channel.typing():
# Check DB connection
self.cnx = check_connection(self.cnx)
self.cursor = self.cnx.cursor(buffered=True)
# Determine the last date for the streak, which is yesterday if today's asdf hasn't happened
now = datetime.now(timezone('Europe/Vienna'))
asdfTime = now.replace(hour=13, minute=38, second=0, microsecond=0)
if now > asdfTime:
today = datetime.now(
timezone('Europe/Vienna')).strftime('%Y-%m-%d')
else:
today = datetime.now(
timezone('Europe/Vienna')) - timedelta(days=1)
today = today.strftime('%Y-%m-%d')
resetDate = '2020-06-04'
# Grab wlc with the correct date
query = (f"""SELECT c.dt,
IFNULL(a.asdf, '0')
FROM `calendar` c
LEFT OUTER JOIN
(SELECT DATE, COUNT(asdf) AS asdf
FROM `asdf`
WHERE asdf > 0
GROUP BY 1) AS a ON c.dt = a.date
WHERE dt BETWEEN '{resetDate}' AND '{today}'
ORDER BY c.dt;""")
self.cursor.execute(query)
self.cnx.commit()
wlc_streak = 0
streak_end = "läuft lohnt"
if self.cursor.rowcount > 0:
rows = self.cursor.fetchall()
maxStreak = 0
currStreak = 0
# Grab max streak for wlc
for row in rows:
if int(row[1]) > 0:
currStreak += 1
elif int(row[1]) == 0:
if currStreak > maxStreak:
maxStreak = currStreak
streak_end = row[0]
currStreak = 0
wlc_streak = max(maxStreak, currStreak)
# Grab active streak for wlc
rows_sorted = sorted(rows, key=lambda x: x[0], reverse=True)
activeStreak = 0
for row in rows_sorted:
if int(row[1]) > 0:
activeStreak += 1
elif int(row[1]) == 0:
break
wlc_active_streak = activeStreak
# Set streak end to the day before
if streak_end != "läuft lohnt":
streak_end -= timedelta(days=1)
# Grab users
query = (f"SELECT DISTINCT(author) FROM `asdf`")
self.cursor.execute(query)
self.cnx.commit()
if self.cursor.rowcount > 0:
rows = self.cursor.fetchall()
user_max_streaks = {}
user_active_streaks = {}
r = ''
asdfEmbed = discord.Embed(
title=f'Top and (active) ASDF streaks',
colour=discord.Colour.from_rgb(102, 153, 255))
for row in rows:
user_max_streak = self.getUserStreak(
True, int(row[0]), resetDate, today)
user_active_streak = self.getUserStreak(
False, int(row[0]), resetDate, today)
user = ctx.guild.get_member(int(row[0]))
if user == None: # Skip User if not found in Guild
continue
else: # User in guild > check if Nick or use Name instead
if user.nick == None:
user = user.name
else:
user = user.nick
user_max_streaks[user] = user_max_streak
user_active_streaks[user] = user_active_streak
user_max_streaks_sorted = sorted(user_max_streaks.items(),
key=lambda x: x[1],
reverse=True)
for user in user_max_streaks_sorted:
r += f'{user[0]}: {user[1]} ({user_active_streaks[user[0]]})\n'
asdfEmbed.add_field(
name=
f'**WLC max: {wlc_streak}**\nWLC active: {wlc_active_streak}\nMax ended: {streak_end}',
value=r)
if wlc_streak == 0:
await ctx.send(f'```Noch keine streak ... bis jetzt.```')
else:
await ctx.send(embed=asdfEmbed)