From 5cb927cb17939d241e5eab8c88909c88a7b271d2 Mon Sep 17 00:00:00 2001 From: Someone Date: Mon, 13 Nov 2023 13:09:02 +0100 Subject: [PATCH] [stats] posts_with_reactions --- stats/posts_with_reactions.py | 28 ++++++++++++++++++++++++++++ 1 file changed, 28 insertions(+) create mode 100644 stats/posts_with_reactions.py diff --git a/stats/posts_with_reactions.py b/stats/posts_with_reactions.py new file mode 100644 index 0000000..85d7461 --- /dev/null +++ b/stats/posts_with_reactions.py @@ -0,0 +1,28 @@ +#!/usr/bin/env python3 +# Someone's Mattermost scripts. +# Copyright (c) 2016-2022 by Someone (aka. Jan Vales ) +# published under MIT-License +# +# Posts with most reactions in the last 6 months. +# + +import psycopg2 +import psycopg2.extras + +import config + + +def main(dbconn): + cur = dbconn.cursor(cursor_factory=psycopg2.extras.DictCursor) + cur.execute("""SELECT 'https://mattermost.fsinf.at/'||teams.name||'/pl/'||postid AS url, teams.name||'::'||channels.name AS channelname, count(*) AS cnt, postid + FROM reactions JOIN posts ON (postid=posts.id) JOIN channels ON (posts.channelid=channels.id) left JOIN teams ON (teams.id=channels.teamid) + WHERE channels.type='O' and posts.createat > extract(epoch FROM (NOW() - INTERVAL '6 month'))*1000 GROUP BY teams.name, channels.name, postid ORDER BY cnt DESC LIMIT 30 + """) + + msg = "#posts_with_most_reactions #mmstats top30 posts in the last 6 months with the most reactions.\n**``DO NOT ADD ANY REACTIONS IF YOU FOUND THEM VIA THESE LINKS. IT WILL SKEW THE STATS``**\n\n|team::channel + link|cnt|\n|---|---:|\n" + for record in cur.fetchall(): + try: + msg += "|["+record["channelname"]+"]("+record["url"]+")|"+str(record["cnt"])+"|\n" + except TypeError: + pass + return msg -- 2.43.0