From 4f9c088f6b0458eb0ebb52ef899cdfdc57f2c43c Mon Sep 17 00:00:00 2001
From: Boris Muratov <8bee278@gmail.com>
Date: Sun, 7 Mar 2021 00:59:41 +0200
Subject: Add route to get a member's data for helper review

Added route for getting a user's join date, total messages, and top 3 channels by activity.

This information will be used to auto-review nominees.
---
 pydis_site/apps/api/models/bot/metricity.py | 39 +++++++++++++++++++++++++++++
 1 file changed, 39 insertions(+)

(limited to 'pydis_site/apps/api/models/bot')

diff --git a/pydis_site/apps/api/models/bot/metricity.py b/pydis_site/apps/api/models/bot/metricity.py
index cae630f1..af5e1f3b 100644
--- a/pydis_site/apps/api/models/bot/metricity.py
+++ b/pydis_site/apps/api/models/bot/metricity.py
@@ -89,3 +89,42 @@ class Metricity:
             raise NotFound()
 
         return values[0]
+
+    def top_channel_activity(self, user_id: str) -> int:
+        """
+        Query the top three channels in which the user is most active.
+
+        Help channels are grouped under "the help channels",
+        and off-topic channels are grouped under "off-topic".
+        """
+        self.cursor.execute(
+            """
+            SELECT
+                CASE
+                    WHEN channels.name ILIKE 'help-%%' THEN 'the help channels'
+                    WHEN channels.name ILIKE 'ot%%' THEN 'off-topic'
+                    ELSE channels.name
+                END,
+                COUNT(1)
+            FROM
+                messages
+                LEFT JOIN channels ON channels.id = messages.channel_id
+            WHERE
+                author_id = '%s'
+            GROUP BY
+                1
+            ORDER BY
+                2 DESC
+            LIMIT
+                3;
+            """,
+            [user_id]
+        )
+
+        values = self.cursor.fetchall()
+        print(values)
+
+        if not values:
+            raise NotFound()
+
+        return values
-- 
cgit v1.2.3


From d2690bbedb5f5ef221cbfaa42ad78ff8fcc263f2 Mon Sep 17 00:00:00 2001
From: Boris Muratov <8bee278@gmail.com>
Date: Sun, 7 Mar 2021 01:05:09 +0200
Subject: Amend top_channel_activity return type

---
 pydis_site/apps/api/models/bot/metricity.py | 4 +++-
 1 file changed, 3 insertions(+), 1 deletion(-)

(limited to 'pydis_site/apps/api/models/bot')

diff --git a/pydis_site/apps/api/models/bot/metricity.py b/pydis_site/apps/api/models/bot/metricity.py
index af5e1f3b..29a43513 100644
--- a/pydis_site/apps/api/models/bot/metricity.py
+++ b/pydis_site/apps/api/models/bot/metricity.py
@@ -1,3 +1,5 @@
+from typing import List, Tuple
+
 from django.db import connections
 
 BLOCK_INTERVAL = 10 * 60  # 10 minute blocks
@@ -90,7 +92,7 @@ class Metricity:
 
         return values[0]
 
-    def top_channel_activity(self, user_id: str) -> int:
+    def top_channel_activity(self, user_id: str) -> List[Tuple[str, int]]:
         """
         Query the top three channels in which the user is most active.
 
-- 
cgit v1.2.3


From 07847e959c9b2ac6a79ab38b900abc2d179d0478 Mon Sep 17 00:00:00 2001
From: Boris Muratov <8bee278@gmail.com>
Date: Sun, 7 Mar 2021 11:58:30 +0200
Subject: Get rid of stray print

Oops.
---
 pydis_site/apps/api/models/bot/metricity.py | 1 -
 1 file changed, 1 deletion(-)

(limited to 'pydis_site/apps/api/models/bot')

diff --git a/pydis_site/apps/api/models/bot/metricity.py b/pydis_site/apps/api/models/bot/metricity.py
index 29a43513..7e2a68f2 100644
--- a/pydis_site/apps/api/models/bot/metricity.py
+++ b/pydis_site/apps/api/models/bot/metricity.py
@@ -124,7 +124,6 @@ class Metricity:
         )
 
         values = self.cursor.fetchall()
-        print(values)
 
         if not values:
             raise NotFound()
-- 
cgit v1.2.3


From fdc1be68a90d6ebd9dfe29369bc8a974bcaa8214 Mon Sep 17 00:00:00 2001
From: Boris Muratov <8bee278@gmail.com>
Date: Thu, 11 Mar 2021 02:37:44 +0200
Subject: Ignore deleted messaages in message counts

Co-authored-by: Joe Banks <joseph@josephbanks.me>
---
 pydis_site/apps/api/models/bot/metricity.py | 2 +-
 1 file changed, 1 insertion(+), 1 deletion(-)

(limited to 'pydis_site/apps/api/models/bot')

diff --git a/pydis_site/apps/api/models/bot/metricity.py b/pydis_site/apps/api/models/bot/metricity.py
index 7e2a68f2..db975d4e 100644
--- a/pydis_site/apps/api/models/bot/metricity.py
+++ b/pydis_site/apps/api/models/bot/metricity.py
@@ -112,7 +112,7 @@ class Metricity:
                 messages
                 LEFT JOIN channels ON channels.id = messages.channel_id
             WHERE
-                author_id = '%s'
+                author_id = '%s' AND NOT messages.is_deleted
             GROUP BY
                 1
             ORDER BY
-- 
cgit v1.2.3


From cd797f801abaff8874e75b1ed093e17f67572a37 Mon Sep 17 00:00:00 2001
From: Boris Muratov <8bee278@gmail.com>
Date: Fri, 12 Mar 2021 16:00:14 +0200
Subject: Add case in query for voice chat activity

---
 pydis_site/apps/api/models/bot/metricity.py | 1 +
 1 file changed, 1 insertion(+)

(limited to 'pydis_site/apps/api/models/bot')

diff --git a/pydis_site/apps/api/models/bot/metricity.py b/pydis_site/apps/api/models/bot/metricity.py
index db975d4e..5daa5c66 100644
--- a/pydis_site/apps/api/models/bot/metricity.py
+++ b/pydis_site/apps/api/models/bot/metricity.py
@@ -105,6 +105,7 @@ class Metricity:
                 CASE
                     WHEN channels.name ILIKE 'help-%%' THEN 'the help channels'
                     WHEN channels.name ILIKE 'ot%%' THEN 'off-topic'
+                    WHEN channels.name ILIKE '%%voice%%' THEN 'voice chats'
                     ELSE channels.name
                 END,
                 COUNT(1)
-- 
cgit v1.2.3