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)