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)