def stars_stats(self, event, user=None):
if user:
try:
given_stars = list(StarboardEntry.select(
fn.COUNT('*'),
).join(Message).where(
(~ (StarboardEntry.star_message_id >> None)) &
(StarboardEntry.stars.contains(user.id)) &
(Message.guild_id == event.guild.id)
).tuples())[0][0]
recieved_stars_posts, recieved_stars_total = list(StarboardEntry.select(
fn.COUNT('*'),
fn.SUM(fn.array_length(StarboardEntry.stars, 1)),
).join(Message).where(
(~ (StarboardEntry.star_message_id >> None)) &
(Message.author_id == user.id) &
(Message.guild_id == event.guild.id)
).tuples())[0]
except:
return event.msg.reply(':warning: failed to crunch the numbers on that user')
embed = MessageEmbed()
embed.color = 0xffd700
embed.title = user.username
embed.set_thumbnail(url=user.avatar_url)
embed.add_field(name='Total Stars Given', value=str(given_stars), inline=True)
embed.add_field(name='Total Posts w/ Stars', value=str(recieved_stars_posts), inline=True)
embed.add_field(name='Total Stars Recieved', value=str(recieved_stars_total), inline=True)
# embed.add_field(name='Star Rank', value='#{}'.format(recieved_stars_rank), inline=True)
return event.msg.reply('', embed=embed)
total_starred_posts, total_stars = list(StarboardEntry.select(
fn.COUNT('*'),
fn.SUM(fn.array_length(StarboardEntry.stars, 1)),
).join(Message).where(
(~ (StarboardEntry.star_message_id >> None)) &
(Message.guild_id == event.guild.id)
).tuples())[0]
top_users = list(StarboardEntry.select(fn.SUM(fn.array_length(StarboardEntry.stars, 1)), User.user_id).join(
Message,
).join(
User,
on=(Message.author_id == User.user_id),
).where(
(~ (StarboardEntry.star_message_id >> None)) &
(fn.array_length(StarboardEntry.stars, 1) > 0) &
(Message.guild_id == event.guild.id)
).group_by(User).order_by(fn.SUM(fn.array_length(StarboardEntry.stars, 1)).desc()).limit(5).tuples())
embed = MessageEmbed()
embed.color = 0xffd700
embed.title = 'Star Stats'
embed.add_field(name='Total Stars Given', value=total_stars, inline=True)
embed.add_field(name='Total Starred Posts', value=total_starred_posts, inline=True)
embed.add_field(name='Top Star Recievers', value='\n'.join(
'{}. <@{}> ({})'.format(idx + 1, row[1], row[0]) for idx, row in enumerate(top_users)
))
event.msg.reply('', embed=embed)
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)
