def words_top(self, event, target):
if isinstance(target, DiscoUser):
q = 'author_id'
elif isinstance(target, DiscoChannel):
q = 'channel_id'
elif isinstance(target, DiscoGuild):
q = 'guild_id'
else:
raise Exception("You should not be here")
sql = """
SELECT word, count(*)
FROM (
SELECT regexp_split_to_table(content, '\s') as word
FROM messages
WHERE {}=%s
LIMIT 3000000
) t
GROUP BY word
ORDER BY 2 DESC
LIMIT 30
""".format(q)
t = MessageTable()
t.set_header('Word', 'Count')
for word, count in Message.raw(sql, target.id).tuples():
if '```' in word:
continue
t.add(word, count)
event.msg.reply(t.compile())
def guild_stats_messages(guild):
unit = request.values.get('unit', 'days')
amount = int(request.values.get('amount', 7))
sql = '''
SELECT date, coalesce(count, 0) AS count
FROM
generate_series(
NOW() - interval %s,
NOW(),
%s
) AS date
LEFT OUTER JOIN (
SELECT date_trunc(%s, timestamp) AS dt, count(*) AS count
FROM messages
WHERE
timestamp >= (NOW() - interval %s) AND
timestamp < (NOW()) AND
guild_id=%s AND
GROUP BY dt
) results
ON (date_trunc(%s, date) = results.dt);
'''
tuples = list(
Message.raw(sql, '{} {}'.format(amount, unit), '1 {}'.format(unit),
unit, '{} {}'.format(amount,
unit), guild.guild_id, unit).tuples())
return jsonify(tuples)
def words_usage(self, event, word, unit='days', amount=7):
sql = '''
SELECT date, coalesce(count, 0) AS count
FROM
generate_series(
NOW() - interval %s,
NOW(),
%s
) AS date
LEFT OUTER JOIN (
SELECT date_trunc(%s, timestamp) AS dt, count(*) AS count
FROM messages
WHERE
timestamp >= (NOW() - interval %s) AND
timestamp < (NOW()) AND
guild_id=%s AND
(SELECT count(*) FROM regexp_matches(content, %s)) >= 1
GROUP BY dt
) results
ON (date_trunc(%s, date) = results.dt);
'''
msg = event.msg.reply(':alarm_clock: One moment pls...')
start = time.time()
tuples = list(
Message.raw(sql, '{} {}'.format(amount, unit), '1 {}'.format(unit),
unit, '{} {}'.format(amount, unit), event.guild.id,
'\s?{}\s?'.format(word), unit).tuples())
sql_duration = time.time() - start
start = time.time()
chart = pygal.Line()
chart.title = 'Usage of {} Over {} {}'.format(
word,
amount,
unit,
)
if unit == 'days':
chart.x_labels = [i[0].strftime('%a %d') for i in tuples]
elif unit == 'minutes':
chart.x_labels = [i[0].strftime('%X') for i in tuples]
else:
chart.x_labels = [i[0].strftime('%x %X') for i in tuples]
chart.x_labels = [i[0] for i in tuples]
chart.add(word, [i[1] for i in tuples])
pngdata = cairosvg.svg2png(bytestring=chart.render(), dpi=72)
chart_duration = time.time() - start
event.msg.reply('_SQL: {}ms_ - _Chart: {}ms_'.format(
int(sql_duration * 1000),
int(chart_duration * 1000),
),
attachments=[('chart.png', pngdata)])
msg.delete()
def msgstats(self, event, user):
# Query for the basic aggregate message statistics
message_stats = Message.select(
fn.Count('*'),
fn.Sum(fn.char_length(Message.content)),
fn.Sum(fn.array_length(Message.emojis, 1)),
fn.Sum(fn.array_length(Message.mentions, 1)),
fn.Sum(fn.array_length(Message.attachments, 1)),
).where(
(Message.author_id == user.id)
).tuples().async()
reactions_given = Reaction.select(
fn.Count('*'),
Reaction.emoji_id,
Reaction.emoji_name,
).join(
Message,
on=(Message.id == Reaction.message_id)
).where(
(Reaction.user_id == user.id)
).group_by(
Reaction.emoji_id, Reaction.emoji_name
).order_by(fn.Count('*').desc()).tuples().async()
# Query for most used emoji
emojis = Message.raw('''
SELECT gm.emoji_id, gm.name, count(*)
FROM (
SELECT unnest(emojis) as id
FROM messages
WHERE author_id=%s
) q
JOIN guild_emojis gm ON gm.emoji_id=q.id
GROUP BY 1, 2
ORDER BY 3 DESC
LIMIT 1
''', (user.id, )).tuples().async()
deleted = Message.select(
fn.Count('*')
).where(
(Message.author_id == user.id) &
(Message.deleted == 1)
).tuples().async()
wait_many(message_stats, reactions_given, emojis, deleted, timeout=10)
# If we hit an exception executing the core query, throw an exception
if message_stats.exception:
message_stats.get()
q = message_stats.value[0]
embed = MessageEmbed()
embed.fields.append(
MessageEmbedField(name='Total Messages Sent', value=q[0] or '0', inline=True))
embed.fields.append(
MessageEmbedField(name='Total Characters Sent', value=q[1] or '0', inline=True))
if deleted.value:
embed.fields.append(
MessageEmbedField(name='Total Deleted Messages', value=deleted.value[0][0], inline=True))
embed.fields.append(
MessageEmbedField(name='Total Custom Emojis', value=q[2] or '0', inline=True))
embed.fields.append(
MessageEmbedField(name='Total Mentions', value=q[3] or '0', inline=True))
embed.fields.append(
MessageEmbedField(name='Total Attachments', value=q[4] or '0', inline=True))
if reactions_given.value:
reactions_given = reactions_given.value
embed.fields.append(
MessageEmbedField(name='Total Reactions', value=sum(i[0] for i in reactions_given), inline=True))
emoji = (
reactions_given[0][2]
if not reactions_given[0][1] else
'<:{}:{}>'.format(reactions_given[0][2], reactions_given[0][1])
)
embed.fields.append(
MessageEmbedField(name='Most Used Reaction', value=u'{} (used {} times)'.format(
emoji,
reactions_given[0][0],
), inline=True))
if emojis.value:
emojis = list(emojis.value)
if emojis:
embed.add_field(
name='Most Used Emoji',
value=u'<:{1}:{0}> (`{1}`, used {2} times)'.format(*emojis[0]))
embed.thumbnail = MessageEmbedThumbnail(url=user.avatar_url)
embed.color = get_dominant_colors_user(user)
event.msg.reply('', embed=embed)
def guild_stats_self(guild):
def serialize_user(gcc):
for i in gcc:
user_raw = '''
SELECT username, discriminator
FROM users
WHERE
user_id=%s AND
bot=false;
'''
user = list(User.raw(user_raw, i[1]).tuples())
if user:
return {
'user': {
'username': user[0][0],
'discrim': str(user[0][1]),
'id': i[1]
},
'user_count': int(i[0]),
}
return {
'user': '******',
'user_count': 0,
}
def serialize_emoji(gcc):
for i in gcc:
emoji_raw = '''
SELECT emoji_id
FROM guild_emojis
WHERE
emoji_id=%s AND
guild_id=%s;
'''
emoji = list(
GuildEmoji.raw(emoji_raw, i[0], guild.guild_id).tuples())
if emoji:
return str(emoji[0][0])
return '230870076126003200'
data = json.loads(
rdb.get('web:guild:{}:stats'.format(guild.guild_id)) or '{}')
if not data:
# Totals
totals_messages = Message.select(Message.id).where(
(Message.guild_id == guild.guild_id)).count()
totals_infractions = Infraction.select(Infraction.id).where(
(Infraction.guild_id == guild.guild_id)).count()
# Peaks
## Messages
peaks_messages_raw = '''
SELECT count(id), author_id
FROM
messages
WHERE
guild_id=%s
GROUP BY author_id
ORDER BY count DESC
LIMIT 5;
'''
peaks_messages = list(
Message.raw(peaks_messages_raw, guild.guild_id).tuples())
## Infractions
peaks_infractions_raw = '''
SELECT count(id), user_id
FROM
infractions
WHERE
guild_id=%s
GROUP BY user_id
ORDER BY count DESC
LIMIT 5;
'''
peaks_infractions = list(
Infraction.raw(peaks_infractions_raw, guild.guild_id).tuples())
## Emoji
peaks_emoji_raw = '''
SELECT id, count(*)
FROM (
SELECT unnest(emojis) as id
FROM messages
WHERE guild_id=%s and
cardinality(emojis) > 0
) q
GROUP BY 1
ORDER BY 2 DESC
LIMIT 5
'''
peaks_emoji = list(
Message.raw(peaks_emoji_raw, guild.guild_id).tuples())
## Command
peaks_command_raw = '''
SELECT count(c.command), c.command
FROM
commands c
INNER JOIN messages m
ON (c.message_id = m.id)
WHERE
m.guild_id=%s
GROUP BY 2
ORDER BY 1 DESC
LIMIT 1;
'''
peaks_command = list(
Command.raw(peaks_command_raw, guild.guild_id).tuples())
if totals_messages:
totals_messages = totals_messages
else:
totals_messages = 0
if totals_infractions:
totals_infractions = totals_infractions
else:
totals_infractions = 0
if peaks_messages:
pm = serialize_user(peaks_messages)
else:
pm = {
'user': '******',
'user_count': 0,
}
if peaks_infractions:
pi = serialize_user(peaks_infractions)
else:
pi = {
'user': '******',
'user_count': 0,
}
if peaks_emoji:
anim = False
peaks_emoji_id = serialize_emoji(peaks_emoji)
url = 'https://discordapp.com/api/emojis/{}.gif'.format(
peaks_emoji_id)
r = requests.get(url)
try:
r.raise_for_status()
anim = True
except requests.HTTPError:
pass
if anim:
peaks_emoji_ext = 'gif'
else:
peaks_emoji_ext = 'png'
else:
peaks_emoji_id = '230870076126003200'
peaks_emoji_ext = 'png'
if peaks_command:
peaks_command = '{1}'.format(*peaks_command[0])
else:
peaks_command = 'N/A'
data = {
'totals': {
'messages': totals_messages,
'infractions': totals_infractions,
},
'peaks': {
'messages': pm,
'infractions': pi,
'emoji': {
'id': peaks_emoji_id,
'ext': peaks_emoji_ext,
},
'command': peaks_command,
},
}
rdb.setex('web:guild:{}:stats'.format(guild.guild_id),
json.dumps(data), 600)
return jsonify(data)
