async def saveold(self, ctx: commands.Context, channel_id: int,
count: int):
tracking_cog = get_tracking_cog(self.bot)
channel = self.bot.get_channel(channel_id)
if not isinstance(channel, discord.TextChannel):
await ctx.reply(f"Impossible de trouver ce salon ({channel_id})")
return
db = tracking_cog.tracked_guilds[channel.guild.id]
# Si channel ignoré, passer
if channel.id in tracking_cog.ignored_channels:
await ctx.send(f"Channel {channel.name} ignoré")
return
time_debut = time.time()
msg_bot = await ctx.send("Enregistrement...")
# Récupérer le plus ancien message du channel
with db:
with db.bind_ctx([Message]):
oldest = (Message.select().where(
Message.channel_id == channel_id).order_by(
Message.message_id).get())
# discord.Message correspondant
oldest_msg: discord.Message = await channel.fetch_message(oldest)
# Enregistrement
save_result = await self._save_from_channel(channel,
count,
before=oldest_msg)
print("Fin", time.time() - time_debut)
await msg_bot.edit(content=f"{count} demandés\n{save_result}")
async def purge(self, ctx: commands.Context):
"""Vérifier les messages supprimés sur cette guild"""
author = ctx.author
author_id = hashlib.pbkdf2_hmac(hash_name,
str(author.id).encode(), salt,
iterations).hex()
supprimes = 0
trouves = 0
guild = ctx.guild
if guild is None:
await ctx.reply("Guild inconnue")
return
tracking_cog = get_tracking_cog(self.bot)
db = tracking_cog.tracked_guilds[guild.id]
msg_bot = await ctx.author.send(
f"Je nettoie vos données concernant la guild **{guild.name}**... 🐝"
)
to_delete = []
# TODO: édition des anciens messages si nécessaire
# Récupérer les messages de l'utilisateur enregistrés pour cette guild
# TODO: mettre la connexion à la db dans le for
with db:
with db.bind_ctx([Message]):
for message in Message.select().where(
Message.author_id == author_id):
trouves += 1
# discord.Message correspondant
try:
# TODO: faire channel.fetch_message (sur le channel correspondant)
await author.fetch_message(message)
except discord.NotFound:
to_delete.append(message.message_id)
supprimes += 1
except discord.Forbidden:
to_delete.append(message.message_id)
supprimes += 1
# Suppression
with db:
with db.bind_ctx([Message]):
for message_id in to_delete:
Message.delete_by_id(message_id)
result = (
f"Sur les **{trouves}** messages de vous que j'avais récoltés",
f"sur **{guild.name}**, **{supprimes}** n'ont pas été retrouvés sur",
"Discord et ont donc été supprimés de ma ruche.",
)
await msg_bot.edit(content=" ".join(result))
async def delete(self, ctx: commands.Context, message_id: int):
"""Supprimer un message oublié par Abeille"""
author = ctx.author
author_id = hashlib.pbkdf2_hmac(hash_name,
str(author.id).encode(), salt,
iterations).hex()
guild = ctx.guild
if guild is None:
await ctx.reply("Guild inconnue")
return
tracking_cog = get_tracking_cog(self.bot)
db = tracking_cog.tracked_guilds[guild.id]
# TODO: Parcourir chaque db
with db:
with db.bind_ctx([Message]):
try:
message = (Message.select().where(
Message.author_id == author_id).where(
Message.message_id == message_id).get())
except DoesNotExist:
await ctx.author.send(
"Je n'ai pas ce message dans ma ruche 🐝")
return
# Récupérer channel du message
channel = self.bot.get_channel(message.channel_id)
if not isinstance(channel, discord.TextChannel):
with db:
with db.bind_ctx([Message]):
Message.delete_by_id(message_id)
await ctx.author.send(
"Je n'ai plus accès au channel correspondant, message supprimé 🐝"
)
return
# Récupérer discord.Message pour voir s'il existe toujours
try:
await channel.fetch_message(message)
await ctx.author.send(
"Ce message est toujours visible sur Discord 🐝")
return
except (discord.NotFound, discord.Forbidden):
pass
with db:
with db.bind_ctx([Message]):
Message.delete_by_id(message_id)
await ctx.author.send("Je viens de supprimer ce message de ma ruche 🐝")
async def _check(self, ctx: commands.Context, guild_id: int):
"""Vérifie quels channels sont enregistrés"""
tracking_cog = get_tracking_cog(self.bot)
db = tracking_cog.tracked_guilds[guild_id]
guild = self.bot.get_guild(guild_id)
if guild is None:
await ctx.reply(f"Guild inconnue ({guild_id})")
return
ok_channels = []
nok_channels = []
ignored = []
start_msg = await ctx.author.send("Je fais un récap... 🐝")
for channel in guild.channels:
if not isinstance(channel, discord.TextChannel):
continue
# Si channel ignoré
if channel.id in tracking_cog.ignored_channels:
ignored.append(f"🚫 {channel.name}")
continue
try:
await channel.history(limit=10).flatten()
except discord.Forbidden as exc:
nok_channels.append(f"⭕ {channel.name}")
continue
# Compter nombre de messages enregistrés
with db:
with db.bind_ctx([Message]):
count = (
Message.select().where(Message.channel_id == channel.id).count()
)
ok_channels.append(f"✅ {channel.name} ({count} messages enregistrés)")
await start_msg.delete()
await ctx.author.send(
f"🐝 J'enregistre actuellement **{len(ok_channels)}** salon(s) écrit(s).\n"
+ "\n".join(ok_channels)
)
await ctx.author.send(
f"🐝 Je n'ai pas la permission d'enregistrer **{len(nok_channels)}** salon(s) écrit(s).\n"
+ "\n".join(nok_channels)
)
await ctx.author.send(
f"🐝 J'ignore **{len(ignored)}** salon(s) écrit(s).\n" + "\n".join(ignored)
)
async def _save_from_channel(
self,
channel: discord.TextChannel,
count: int = 100,
before: Union[Snowflake, datetime.datetime] = None,
after: Union[Snowflake, datetime.datetime] = None,
around: Union[Snowflake, datetime.datetime] = None,
oldest_first: bool = None,
) -> SaveResult:
"""Enregistre les messages d'un channel dans la BDD associée"""
tracking_cog = get_tracking_cog(self.bot)
guild = channel.guild
db: Database = tracking_cog.tracked_guilds[guild.id]
save_result = SaveResult()
async for message in channel.history(
limit=count,
before=before,
after=after,
around=around,
oldest_first=oldest_first,
):
save_result.trouves += 1
# Ignorer messages bot
if message.author.bot:
save_result.from_bot += 1
continue
with db:
with db.bind_ctx([Message]):
# Vérifier si le message existe avant d'enregistrer
# TODO: Plutôt faire select().count() ?
try:
Message.get_by_id(message.id)
save_result.deja_sauves += 1
continue
except DoesNotExist:
pass
# Créer le message inexistant
msg = get_message(message)
msg.save(force_insert=True)
save_result.sauves += 1
return save_result
async def export_slash(self, ctx: SlashContext):
await ctx.defer()
author = ctx.author
author_id = hashlib.pbkdf2_hmac(hash_name,
str(author.id).encode(), salt,
iterations).hex()
guild = ctx.guild
tracking_cog = get_tracking_cog(self.bot)
db = tracking_cog.tracked_guilds[guild.id]
temp_csv_path = f"/tmp/export_{author_id[:5]}.csv"
with db:
with db.bind_ctx([Message]):
with open(temp_csv_path, "w") as fh:
writer = csv.writer(fh)
for message in (Message.select().where(
Message.author_id ==
author_id).tuples().iterator()):
writer.writerow(message)
result = (
"Voici les messages de vous que j'ai récoltés.",
"Si vous souhaitez supprimer définitivement",
"un message de ma ruche, utilisez la commande",
"`@Abeille delete <message_id>`.",
"L'ID du message est la première information de chaque",
"ligne du fichier que j'ai envoyé 🐝",
)
await ctx.author.send(
" ".join(result),
file=discord.File(temp_csv_path),
)
os.remove(temp_csv_path)
await ctx.send(
"Les données vous concernant vous ont été envoyées par message privé. 🐝",
hidden=True,
)
async def rank_slash(self, ctx: SlashContext, expression: str):
await ctx.defer()
expression = expression.strip()
author = ctx.author
author_id = hashlib.pbkdf2_hmac(hash_name,
str(author.id).encode(), salt,
iterations).hex()
guild_id = ctx.guild.id
tracking_cog = get_tracking_cog(self.bot)
db = tracking_cog.tracked_guilds[guild_id]
with db:
with db.bind_ctx([Message]):
rank_query = fn.rank().over(
order_by=[fn.COUNT(Message.message_id).desc()])
subq = (Message.select(
Message.author_id, rank_query.alias("rank")).where(
Message.content.contains(expression)).group_by(
Message.author_id))
# Here we use a plain Select() to create our query.
query = (Select(columns=[subq.c.rank]).from_(subq).where(
subq.c.author_id == author_id).bind(db)
) # We must bind() it to the database.
rank = query.scalar()
if rank is None:
result = f"Vous n'avez jamais employé l'expression *{expression}*."
elif rank == 1:
result = f"🥇 Vous êtes le membre ayant le plus employé l'expression *{expression}*."
elif rank == 2:
result = f"🥈 Vous êtes le 2ème membre à avoir le plus employé l'expression *{expression}*."
elif rank == 3:
result = f"🥉 Vous êtes le 3ème membre à avoir le plus employé l'expression *{expression}*."
else:
result = f"Vous êtes le **{rank}ème** membre à avoir le plus employé l'expression *{expression}*."
await ctx.send(result)
async def saveall(self,
ctx: commands.Context,
guild_id: int,
count: int = 20):
"""Sauvegarde les messages de tous les channels possibles à partir d'ici"""
save_results = {}
impossible_channels = []
tracking_cog = get_tracking_cog(self.bot)
guild = self.bot.get_guild(guild_id)
if guild is None:
await ctx.reply("Je ne trouve pas cette guild")
return
# Détection des channels
for channel in guild.channels:
if not isinstance(channel, discord.TextChannel):
continue
# Si channel ignoré, passer
if channel.id in tracking_cog.ignored_channels:
await ctx.send(f"Channel {channel.name} ignoré")
continue
try:
save_results[channel.name] = await self._save_from_channel(
channel, count)
except:
impossible_channels.append(channel)
for name, save_result in save_results.items():
await ctx.send(f"**{name}**\n{save_result}")
impossible_channels_str = []
for impossible_channel in impossible_channels:
impossible_channels_str.append(impossible_channel.name)
await ctx.send("Je n'ai pas pu enregistrer les autres channels")
async def save(self,
ctx: commands.Context,
channel_id: int,
count: int = 20):
"""Sauvegarde les messages dans le passé à partir d'ici"""
channel = self.bot.get_channel(channel_id)
tracking_cog = get_tracking_cog(self.bot)
if not isinstance(channel, discord.TextChannel):
await ctx.reply(f"Impossible de trouver ce salon ({channel_id})")
return
# Si channel ignoré, passer
if channel.id in tracking_cog.ignored_channels:
await ctx.send(f"Channel {channel.name} ignoré")
return
time_debut = time.time()
print("Début")
save_result = await self._save_from_channel(channel,
count) # type: ignore
print("Fin", time.time() - time_debut)
await ctx.send(f"{count} demandés\n{save_result}")
async def saveoldall(self,
ctx: commands.Context,
guild_id: int,
count: int = 20):
"""Save old sur les channels connus en db"""
tracking_cog = get_tracking_cog(self.bot)
db = tracking_cog.tracked_guilds[guild_id]
guild = self.bot.get_guild(guild_id)
if guild is None:
await ctx.send("Je ne trouve pas cette guild")
return
# Récupérer liste channels connus
known_channels = await self._get_known_channels(db)
if not known_channels:
await ctx.send(
"Aucun channel connu, d'abord utiliser saveall ou save")
return
await ctx.send(
f"J'ai trouvé **{len(known_channels)}** channels connus en db...")
# Parcours channels
for channel in known_channels:
msg_bot = await ctx.send(
f"J'enregistre le channel **{channel.name}**...")
# Récupérer le plus ancien message du channel
with db:
with db.bind_ctx([Message]):
try:
oldest = (Message.select().where(
Message.channel_id == channel.id).order_by(
Message.message_id).get())
except DoesNotExist:
print(
f"Pas de message en db pour le channel {channel}")
continue
# discord.Message correspondant
try:
oldest_msg: discord.Message = await channel.fetch_message(
oldest)
except discord.NotFound:
print(
f"Le plus ancien message enregistré du channel {channel} n'existe plus"
)
except discord.Forbidden:
print(f"Problème de droit pour le channel {channel}")
# Enregistrement
try:
save_result = await self._save_from_channel(channel,
count,
before=oldest_msg)
await msg_bot.edit(
content=
f"**{channel.name}**\n{save_result} ({oldest_msg.created_at})"
)
except discord.Forbidden as exc:
await msg_bot.edit(content=f"**{channel.name}**\nErreur: {exc}"
)
await ctx.send("Fini !")
async def _get_compare_img(self, guild_id: int, expression1: str,
expression2: str, periode: int) -> Any:
jour_debut = date.today() - timedelta(days=periode)
jour_fin = date.today() - timedelta(days=1)
tracking_cog = get_tracking_cog(self.bot)
db = tracking_cog.tracked_guilds[guild_id]
guild_name = self.bot.get_guild(guild_id)
with db:
with db.bind_ctx([Message]):
# Messages de l'utilisateur dans la période
query = (Message.select(
fn.DATE(Message.timestamp).alias("date"),
(fn.SUM(Message.content.contains(expression1)) /
fn.COUNT(Message.message_id)).alias("expression1"),
(fn.SUM(Message.content.contains(expression2)) /
fn.COUNT(Message.message_id)).alias("expression2"),
).where(fn.DATE(Message.timestamp) >= jour_debut).where(
fn.DATE(Message.timestamp) <= jour_fin).group_by(
fn.DATE(Message.timestamp)))
cur = db.cursor()
query_sql = cur.mogrify(*query.sql())
df = pandas.read_sql(query_sql, db.connection())
# Si emote custom : simplifier le nom pour titre DW
custom_emoji_str = emoji_to_str(expression1)
if custom_emoji_str:
expression1 = custom_emoji_str
custom_emoji_str = emoji_to_str(expression2)
if custom_emoji_str:
expression2 = custom_emoji_str
# Renommage des colonnes
df = df.rename(columns={
"expression1": expression1,
"expression2": expression2
})
# Remplir les dates manquantes
df = df.set_index("date")
df.index = pandas.DatetimeIndex(df.index)
df.reset_index(level=0, inplace=True)
df = df.rename(columns={"index": "date"})
# Rolling average
df[expression1] = df.get(expression1).rolling(ROLLING_AVERAGE).mean()
df[expression2] = df.get(expression2).rolling(ROLLING_AVERAGE).mean()
title_lines = textwrap.wrap(
f"<b>'{expression1}'</b> vs <b>'{expression2}'</b>")
title_lines.append(f"<i style='font-size: 10px'>Sur {guild_name}.</i>")
title = "<br>".join(title_lines)
fig: go.Figure = px.line(
df,
x="date",
y=[expression1, expression2],
color_discrete_sequence=["yellow", "#4585e6"],
template="plotly_dark",
title=title,
render_mode="svg",
labels={
"date": "",
"variable": ""
},
)
# Hide y-axis
fig.update_yaxes(visible=False, fixedrange=True)
# Legend position
fig.update_layout(legend=dict(
title=None,
orientation="h",
y=1,
yanchor="bottom",
x=0.5,
xanchor="center",
))
fig.add_layout_image(
dict(
source="https://i.imgur.com/Eqy58rg.png",
xref="paper",
yref="paper",
x=1.1,
y=-0.22,
sizex=0.25,
sizey=0.25,
xanchor="right",
yanchor="bottom",
opacity=0.8,
))
return fig.to_image(format="png", scale=2)
async def _comparedw(self, ctx: commands.Context, guild_id: int,
terme1: str, terme2: str):
"""Compare deux tendances"""
if False in (str_input_ok(terme1), str_input_ok(terme2)):
await ctx.send(
"Je ne peux pas faire de tendance avec une expression vide.")
return
# Si les deux mêmes, faire un _trend
if terme1 == terme2:
return await self._trenddw(ctx, guild_id, terme1)
temp_msg: discord.Message = await ctx.send(
f"Je génère les tendances comparées de **{terme1}** et **{terme2}**... 🐝"
)
jour_debut = date.today() - timedelta(days=PERIODE)
jour_fin = date.today() - timedelta(days=1)
tracking_cog = get_tracking_cog(self.bot)
db = tracking_cog.tracked_guilds[guild_id]
guild_name = self.bot.get_guild(guild_id)
with db:
with db.bind_ctx([Message]):
# Messages de l'utilisateur dans la période
query = (Message.select(
fn.DATE(Message.timestamp).alias("date"),
(fn.SUM(Message.content.contains(terme1)) /
fn.COUNT(Message.message_id)).alias("terme1"),
(fn.SUM(Message.content.contains(terme2)) /
fn.COUNT(Message.message_id)).alias("terme2"),
).where(fn.DATE(Message.timestamp) >= jour_debut).where(
fn.DATE(Message.timestamp) <= jour_fin).group_by(
fn.DATE(Message.timestamp)))
cur = db.cursor()
query_sql = cur.mogrify(*query.sql())
msg_par_jour = pandas.read_sql(query_sql, db.connection())
# Si emote custom : simplifier le nom pour titre DW
custom_emoji_str = emoji_to_str(terme1)
if custom_emoji_str:
terme1 = custom_emoji_str
custom_emoji_str = emoji_to_str(terme2)
if custom_emoji_str:
terme2 = custom_emoji_str
# Renommage des colonnes
msg_par_jour = msg_par_jour.rename(columns={
"terme1": terme1,
"terme2": terme2
})
# Remplir les dates manquantes
msg_par_jour = msg_par_jour.set_index("date")
msg_par_jour.index = pandas.DatetimeIndex(msg_par_jour.index)
msg_par_jour.reset_index(level=0, inplace=True)
msg_par_jour = msg_par_jour.rename(columns={"index": "date"})
# Rolling average
msg_par_jour[terme1] = msg_par_jour.get(terme1).rolling(
ROLLING_AVERAGE).mean()
msg_par_jour[terme2] = msg_par_jour.get(terme2).rolling(
ROLLING_AVERAGE).mean()
properties = {
"annotate": {
"notes":
f"Moyenne mobile sur les {ROLLING_AVERAGE} derniers jours. Insensible à la casse et aux accents."
},
"visualize": {
"labeling": "top",
"base-color": "#DFC833",
"line-widths": {
terme1: 1,
terme2: 1
},
"custom-colors": {
terme1: "#DFC833",
terme2: 0
},
"y-grid": "off",
},
}
# Send chart
await self.__send_chart(
ctx,
f"'{terme1}' vs '{terme2}'",
f"Tendances dans les messages postés sur {guild_name}",
"d3-lines",
msg_par_jour,
properties,
)
await temp_msg.delete()
async def _get_trend_img(self, guild_id: int, terme: str,
periode: int) -> Any:
jour_debut = date.today() - timedelta(days=periode)
jour_fin = date.today() - timedelta(days=1)
tracking_cog = get_tracking_cog(self.bot)
db = tracking_cog.tracked_guilds[guild_id]
guild_name = self.bot.get_guild(guild_id)
with db:
with db.bind_ctx([Message]):
# Messages de l'utilisateur dans la période
query = (Message.select(
fn.DATE(Message.timestamp).alias("date"),
(fn.SUM(Message.content.contains(terme)) /
fn.COUNT(Message.message_id)).alias("messages"),
).where(fn.DATE(Message.timestamp) >= jour_debut).where(
fn.DATE(Message.timestamp) <= jour_fin).group_by(
fn.DATE(Message.timestamp)))
# Exécution requête SQL
cur = db.cursor()
query_sql = cur.mogrify(*query.sql())
df = pandas.read_sql(query_sql, db.connection())
# Remplir les dates manquantes
df = df.set_index("date")
df.index = pandas.DatetimeIndex(df.index)
df.reset_index(level=0, inplace=True)
df = df.rename(columns={"index": "date"})
# Rolling average
df["messages"] = df.rolling(ROLLING_AVERAGE).mean()
# Si emote custom : simplifier le nom pour titre DW
custom_emoji_str = emoji_to_str(terme)
if custom_emoji_str:
terme = custom_emoji_str
title_lines = textwrap.wrap(f"Tendances de <b>'{terme}'</b>")
title_lines.append(f"<i style='font-size: 10px'>Sur {guild_name}.</i>")
title = "<br>".join(title_lines)
fig: go.Figure = px.area(
df,
x="date",
y="messages",
color_discrete_sequence=["yellow"],
# line_shape="spline",
template="plotly_dark",
title=title,
labels={
"date": "",
"messages": ""
},
)
fig.add_layout_image(
dict(
source="https://i.imgur.com/Eqy58rg.png",
xref="paper",
yref="paper",
x=1.1,
y=-0.22,
sizex=0.25,
sizey=0.25,
xanchor="right",
yanchor="bottom",
opacity=0.8,
))
return fig.to_image(format="png", scale=2)
