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 info(self, event, user=None):
if user is None:
user = event.author
user_id = 0
if isinstance(user, (int, long)):
user_id = user
user = self.state.users.get(user)
if user and not user_id:
user = self.state.users.get(user.id)
if not user:
if user_id:
try:
user = self.client.api.users_get(user_id)
except APIException:
raise CommandFail('unknown user')
User.from_disco_user(user)
else:
raise CommandFail('unknown user')
self.client.api.channels_typing(event.channel.id)
content = []
content.append(u'**\u276F User Information**')
content.append(u'**ID:** {}'.format(user.id))
content.append(u'**Profile:** <@{}>'.format(user.id))
if user.presence:
emoji, status = get_status_emoji(user.presence)
content.append('**Status:** {} <{}>'.format(status, emoji))
game = user.presence.game
if game and game.name:
activity = ['Playing', 'Stream', 'Listening to', 'Watching', 'Custom Status'][int(game.type or 0)]
if not game.type:
activity = None
if activity:
game_name = game.state if game.type == GameType.CUSTOM_STATUS else game.name
content.append(u'**{}:** {}'.format(activity,
u'[{}]({})'.format(game_name, game.url) if game.url else game_name
))
if user.public_flags and user.public_flags != 0:
flags = []
for flag, emoji in BADGE_EMOJI.items():
if user.public_flags.check(flag):
flags.append('<{}>'.format(emoji))
if len(flags) > 0:
content.append('**Badges**: {}'.format(' '.join(flags)))
created_dt = to_datetime(user.id)
content.append('**Created:** {} ago ({})'.format(
humanize.naturaldelta(datetime.utcnow() - created_dt),
created_dt.isoformat()
))
member = event.guild.get_member(user.id) if event.guild else None
if member:
content.append(u'\n**\u276F Member Information**')
if member.nick:
content.append(u'**Nickname:** {}'.format(member.nick))
content.append('**Joined:** {} ago ({})'.format(
humanize.naturaldelta(datetime.utcnow() - member.joined_at),
member.joined_at.isoformat(),
))
if member.roles:
content.append(u'**Roles:** {}'.format(
' '.join((member.guild.roles.get(r).mention for r in sorted(member.roles, key=lambda r: member.guild.roles.get(r).position, reverse=True)))
))
# "is not None" does not work with Unset types for some reason
if bool(member.premium_since):
content.append('**Boosting since:** {} ago ({})'.format(
humanize.naturaldelta(datetime.utcnow() - member.premium_since),
member.premium_since.isoformat(),
))
# Execute a bunch of queries async
newest_msg = Message.select(Message.timestamp).where(
(Message.author_id == user.id) &
(Message.guild_id == event.guild.id)
).limit(1).order_by(Message.timestamp.desc()).async()
oldest_msg = Message.select(Message.timestamp).where(
(Message.author_id == user.id) &
(Message.guild_id == event.guild.id)
).limit(1).order_by(Message.timestamp.asc()).async()
infractions = Infraction.select(
Infraction.guild_id,
fn.COUNT('*')
).where(
(Infraction.user_id == user.id)
).group_by(Infraction.guild_id).tuples().async()
voice = GuildVoiceSession.select(
GuildVoiceSession.user_id,
fn.COUNT('*'),
fn.SUM(GuildVoiceSession.ended_at - GuildVoiceSession.started_at)
).where(
(GuildVoiceSession.user_id == user.id) &
(~(GuildVoiceSession.ended_at >> None))
).group_by(GuildVoiceSession.user_id).tuples().async()
# Wait for them all to complete (we're still going to be as slow as the
# slowest query, so no need to be smart about this.)
wait_many(newest_msg, oldest_msg, infractions, voice, timeout=10)
tags = to_tags(guild_id=event.msg.guild.id)
if newest_msg.value and oldest_msg.value:
statsd.timing('sql.duration.newest_msg', newest_msg.value._query_time, tags=tags)
statsd.timing('sql.duration.oldest_msg', oldest_msg.value._query_time, tags=tags)
newest_msg = newest_msg.value.get()
oldest_msg = oldest_msg.value.get()
content.append(u'\n **\u276F Activity**')
content.append('**Last Message:** {} ago ({})'.format(
humanize.naturaldelta(datetime.utcnow() - newest_msg.timestamp),
newest_msg.timestamp.isoformat(),
))
content.append('**First Message:** {} ago ({})'.format(
humanize.naturaldelta(datetime.utcnow() - oldest_msg.timestamp),
oldest_msg.timestamp.isoformat(),
))
if infractions.value:
statsd.timing('sql.duration.infractions', infractions.value._query_time, tags=tags)
infractions = list(infractions.value)
total = sum(i[1] for i in infractions)
content.append(u'\n**\u276F Infractions**')
content.append('**Total Infractions:** {:,}'.format(total))
content.append('**Unique Servers:** {}'.format(len(infractions)))
if voice.value:
statsd.timing('plugin.utilities.info.sql.voice', voice.value._query_time, tags=tags)
voice = list(voice.value)
content.append(u'\n**\u276F Voice**')
content.append(u'**Sessions:** {:,}'.format(voice[0][1]))
content.append(u'**Time:** {}'.format(humanize.naturaldelta(
voice[0][2]
)))
embed = MessageEmbed()
avatar = user.avatar
if avatar:
avatar = user.avatar_url
else:
avatar = u'https://cdn.discordapp.com/embed/avatars/{}.png'.format(
int(user.discriminator) % 5
)
embed.set_author(name=u'{}#{}'.format(
user.username,
user.discriminator,
), icon_url=avatar)
embed.set_thumbnail(url=user.avatar_url if user.avatar else avatar)
embed.description = '\n'.join(content)
embed.color = get_dominant_colors_user(user, user.get_avatar_url('png') if user.avatar else avatar)
event.msg.reply('', embed=embed)
def info(self, event, user):
content = []
content.append(u'**\u276F User Information**')
content.append(u'ID: {}'.format(user.id))
content.append(u'Profile: <@{}>'.format(user.id))
if user.presence:
emoji, status = get_status_emoji(user.presence)
content.append('Status: {} <{}>'.format(status, emoji))
if user.presence.game and user.presence.game.name:
if user.presence.game.type == GameType.DEFAULT:
content.append(u'Game: {}'.format(user.presence.game.name))
else:
content.append(u'Stream: [{}]({})'.format(
user.presence.game.name, user.presence.game.url))
created_dt = to_datetime(user.id)
content.append('Created: {} ago ({})'.format(
humanize.naturaldelta(datetime.utcnow() - created_dt),
created_dt.isoformat()))
member = event.guild.get_member(user.id) if event.guild else None
if member:
content.append(u'\n**\u276F Member Information**')
if member.nick:
content.append(u'Nickname: {}'.format(member.nick))
content.append('Joined: {} ago ({})'.format(
humanize.naturaldelta(datetime.utcnow() - member.joined_at),
member.joined_at.isoformat(),
))
if member.roles:
content.append(u'Roles: {}'.format(', '.join(
(member.guild.roles.get(r).name for r in member.roles))))
# Execute a bunch of queries async
infractions = Infraction.select(
Infraction.guild_id, fn.COUNT('*')).where(
(Infraction.user_id == user.id)).group_by(
Infraction.guild_id).tuples(). async ()
# Wait for them all to complete (we're still going to be as slow as the
# slowest query, so no need to be smart about this.)
wait_many(infractions, timeout=10)
tags = to_tags(guild_id=event.msg.guild.id)
if infractions.value:
statsd.timing('sql.duration.infractions',
infractions.value._query_time,
tags=tags)
infractions = list(infractions.value)
total = sum(i[1] for i in infractions)
content.append(u'\n**\u276F Infractions**')
content.append('Total Infractions: {}'.format(total))
content.append('Unique Servers: {}'.format(len(infractions)))
embed = MessageEmbed()
avatar = u'https://cdn.discordapp.com/avatars/{}/{}.png'.format(
user.id,
user.avatar,
)
embed.set_author(name=u'{}#{}'.format(
user.username,
user.discriminator,
),
icon_url=avatar)
embed.set_thumbnail(url=avatar)
embed.description = '\n'.join(content)
embed.color = get_dominant_colors_user(user, avatar)
event.msg.reply('', embed=embed)
def info(self, event, user):
if isinstance(user, (int, long)):
try:
r = self.bot.client.api.http(Routes.USERS_GET, dict(user=user)) # hacky method cause this old version of Disco doesn't have a method for this and we're too lazy to update
data = r.json()
User = namedtuple('User', [
'avatar',
'discriminator',
'id',
'username',
'presence'
])
user = User(
avatar=data["avatar"],
discriminator=data["discriminator"],
id=int(data["id"]),
username=data["username"],
presence=None
)
except APIException as e:
raise CommandFail('invalid user')
content = []
content.append(u'**\u276F User Information**')
content.append(u'ID: {}'.format(user.id))
content.append(u'Profile: <@{}>'.format(user.id))
if user.presence:
emoji, status = get_status_emoji(user.presence)
content.append('Status: {} <{}>'.format(status, emoji))
if user.presence.game and user.presence.game.name:
if user.presence.game.type == GameType.DEFAULT:
content.append(u'Game: {}'.format(user.presence.game.name))
else:
content.append(u'Stream: [{}]({})'.format(user.presence.game.name, user.presence.game.url))
created_dt = to_datetime(user.id)
content.append('Created: {} ago ({})'.format(
humanize.naturaldelta(datetime.utcnow() - created_dt),
created_dt.isoformat()
))
member = event.guild.get_member(user.id) if event.guild else None
if member:
content.append(u'\n**\u276F Member Information**')
if member.nick:
content.append(u'Nickname: {}'.format(member.nick))
content.append('Joined: {} ago ({})'.format(
humanize.naturaldelta(datetime.utcnow() - member.joined_at),
member.joined_at.isoformat(),
))
if member.roles:
content.append(u'Roles: {}'.format(
', '.join((member.guild.roles.get(r).name for r in member.roles))
))
# Execute a bunch of queries async
newest_msg = Message.select(Message.timestamp).where(
(Message.author_id == user.id) &
(Message.guild_id == event.guild.id)
).limit(1).order_by(Message.timestamp.desc()).async()
oldest_msg = Message.select(Message.timestamp).where(
(Message.author_id == user.id) &
(Message.guild_id == event.guild.id)
).limit(1).order_by(Message.timestamp.asc()).async()
infractions = Infraction.select(
Infraction.guild_id,
fn.COUNT('*')
).where(
(Infraction.user_id == user.id)
).group_by(Infraction.guild_id).tuples().async()
voice = GuildVoiceSession.select(
GuildVoiceSession.user_id,
fn.COUNT('*'),
fn.SUM(GuildVoiceSession.ended_at - GuildVoiceSession.started_at)
).where(
(GuildVoiceSession.user_id == user.id) &
(~(GuildVoiceSession.ended_at >> None))
).group_by(GuildVoiceSession.user_id).tuples().async()
# Wait for them all to complete (we're still going to be as slow as the
# slowest query, so no need to be smart about this.)
wait_many(newest_msg, oldest_msg, infractions, voice, timeout=10)
tags = to_tags(guild_id=event.msg.guild.id)
if newest_msg.value and oldest_msg.value:
statsd.timing('sql.duration.newest_msg', newest_msg.value._query_time, tags=tags)
statsd.timing('sql.duration.oldest_msg', oldest_msg.value._query_time, tags=tags)
newest_msg = newest_msg.value.get()
oldest_msg = oldest_msg.value.get()
content.append(u'\n **\u276F Activity**')
content.append('Last Message: {} ago ({})'.format(
humanize_duration(datetime.utcnow() - newest_msg.timestamp),
newest_msg.timestamp.isoformat(),
))
content.append('First Message: {} ago ({})'.format(
humanize_duration(datetime.utcnow() - oldest_msg.timestamp),
oldest_msg.timestamp.isoformat(),
))
if infractions.value:
statsd.timing('sql.duration.infractions', infractions.value._query_time, tags=tags)
infractions = list(infractions.value)
total = sum(i[1] for i in infractions)
content.append(u'\n**\u276F Infractions**')
content.append('Total Infractions: {}'.format(total))
content.append('Unique Servers: {}'.format(len(infractions)))
if voice.value:
statsd.timing('plugin.utilities.info.sql.voice', voice.value._query_time, tags=tags)
voice = list(voice.value)
content.append(u'\n**\u276F Voice**')
content.append(u'Sessions: {}'.format(voice[0][1]))
content.append(u'Time: {}'.format(humanize.naturaldelta(
voice[0][2]
)))
embed = MessageEmbed()
avatar = u'https://cdn.discordapp.com/avatars/{}/{}.png'.format(
user.id,
user.avatar,
)
embed.set_author(name=u'{}#{}'.format(
user.username,
user.discriminator,
), icon_url=avatar)
embed.set_thumbnail(url=avatar)
embed.description = '\n'.join(content)
embed.color = get_dominant_colors_user(user, avatar)
event.msg.reply('', embed=embed)
def info(self, event, user=None):
if user is None:
user = event.author
user_id = 0
if isinstance(user, (int, long)):
user_id = user
user = self.state.users.get(user)
if user and not user_id:
user = self.state.users.get(user.id)
if not user:
if user_id:
user = self.fetch_user(user_id)
User.from_disco_user(user)
else:
raise CommandFail('unknown user')
content = []
content.append(u'**\u276F User Information**')
content.append(u'ID: {}'.format(user.id))
content.append(u'Profile: <@{}>'.format(user.id))
if user.presence:
emoji, status = get_status_emoji(user.presence)
content.append('Status: {} <{}>'.format(status, emoji))
game = user.presence.game
if game and game.name:
activity = ['Playing', 'Stream'][int(game.type)] if game.type < 2 else None
if not game.type:
if game.name == 'Spotify':
activity = 'Listening to'
else:
activity = None
if activity:
content.append(u'{}: {}'.format(activity,
u'[{}]({})'.format(game.name, game.url) if game.url else game.name
))
created_dt = to_datetime(user.id)
content.append('Created: {} ago ({})'.format(
humanize.naturaldelta(datetime.utcnow() - created_dt),
created_dt.isoformat()
))
for i in self.server_owners:
if i == str(user.id):
content.append('Server Ownership: {}'.format(self.server_owners[i]))
for i in self.server_managers:
if i == str(user.id):
content.append('Community Manager: {}'.format(self.server_managers[i]))
if user.id == self.state.me.id:
content.append('Documentation: https://aetherya.stream/')
elif rdb.sismember('global_admins', user.id):
content.append('Airplane Staff: Global Administrator')
elif rdb.sismember('server_managers', user.id):
content.append('Server Manager')
elif rdb.sismember('server_owner', user.id):
content.append('Server Owner')
member = event.guild.get_member(user.id) if event.guild else None
if member:
content.append(u'\n**\u276F Member Information**')
if member.nick:
content.append(u'Nickname: {}'.format(member.nick))
content.append('Joined: {} ago ({})'.format(
humanize.naturaldelta(datetime.utcnow() - member.joined_at),
member.joined_at.isoformat(),
))
if member.roles:
roles = []
for r in member.roles:
roles.append(member.guild.roles.get(r))
roles = sorted(roles, key=lambda r: r.position, reverse=True)
total = len(member.roles)
roles = roles[:20]
content.append(u'Roles ({}): {}{}'.format(
total, ' '.join(r.mention for r in roles),
' (+{})'.format(total-20) if total > 20 else ''
))
# Execute a bunch of queries async
newest_msg = Message.select(Message.timestamp).where(
(Message.author_id == user.id) &
(Message.guild_id == event.guild.id)
).order_by(Message.timestamp.desc()).limit(1).async()
# oldest_msg = Message.select(Message.timestamp).where(
# (Message.author_id == user.id) &
# (Message.guild_id == event.guild.id)
# ).order_by(Message.timestamp.asc()).limit(1).async()
infractions = Infraction.select(
Infraction.guild_id,
fn.COUNT('*')
).where(
(Infraction.user_id == user.id) &
(Infraction.type_ != 6) & # Unban
(~(Infraction.reason ** '[NOTE]%'))
).group_by(Infraction.guild_id).tuples().async()
voice = GuildVoiceSession.select(
GuildVoiceSession.user_id,
fn.COUNT('*'),
fn.SUM(GuildVoiceSession.ended_at - GuildVoiceSession.started_at)
).where(
(GuildVoiceSession.user_id == user.id) &
(~(GuildVoiceSession.ended_at >> None))
).group_by(GuildVoiceSession.user_id).tuples().async()
# Wait for them all to complete (we're still going to be as slow as the
# slowest query, so no need to be smart about this.)
try:
wait_many(newest_msg, infractions, voice, timeout=3)
except gevent.Timeout:
pass
tags = to_tags(guild_id=event.msg.guild.id)
if newest_msg.value:
content.append(u'\n **\u276F Activity**')
statsd.timing('sql.duration.newest_msg', newest_msg.value._query_time, tags=tags)
newest_msg = newest_msg.value.get()
content.append('Last Message: {} ago ({})'.format(
humanize.naturaldelta(datetime.utcnow() - newest_msg.timestamp),
newest_msg.timestamp.isoformat(),
))
# if oldest_msg.value:
# statsd.timing('sql.duration.oldest_msg', oldest_msg.value._query_time, tags=tags)
# oldest_msg = oldest_msg.value.get()
# content.append('First Message: {} ago ({})'.format(
# humanize.naturaldelta(datetime.utcnow() - oldest_msg.timestamp),
# oldest_msg.timestamp.isoformat(),
# ))
if infractions.value:
statsd.timing('sql.duration.infractions', infractions.value._query_time, tags=tags)
infractions = list(infractions.value)
total = sum(i[1] for i in infractions)
content.append(u'\n**\u276F Infractions**')
content.append('Total Infractions: {}'.format(total))
content.append('Unique Servers: {}'.format(len(infractions)))
if voice.value:
statsd.timing('plugin.utilities.info.sql.voice', voice.value._query_time, tags=tags)
voice = list(voice.value)
content.append(u'\n**\u276F Voice**')
content.append(u'Sessions: {}'.format(voice[0][1]))
content.append(u'Time: {}'.format(humanize.naturaldelta(
voice[0][2]
)))
embed = MessageEmbed()
avatar = user.avatar
if avatar:
avatar = u'https://cdn.discordapp.com/avatars/{}/{}.{}'.format(
user.id, avatar, u'gif' if avatar.startswith('a_') else u'png'
)
else:
avatar = u'https://cdn.discordapp.com/embed/avatars/{}.png'.format(
int(user.discriminator) % 5
)
embed.set_author(name=u'{}#{}'.format(
user.username,
user.discriminator,
), icon_url=avatar)
embed.set_thumbnail(url=avatar)
embed.description = '\n'.join(content)
try:
embed.color = get_dominant_colors_user(user, avatar)
except:
pass
event.msg.reply('', embed=embed)
