Add top mate drinkers getter

diff --git a/cbdb.py b/cbdb.py
index 8bbc11c7bd3654c8c2b9c56daf12d087a9157d6e..5eda3d5e1fe4a2d0f94516ddd893c06e17f90a64 100644 (file)
--- a/cbdb.py
+++ b/cbdb.py
@@ -18,3 +18,37 @@ class Db:
     def __del__(self):
         if self.con:
             self.con.close()
+
+    def getTopMateDrinkers(self):
+        """Return list of pairs of name, count for Mate drinkers."""
+        users = {}
+        que = """
+            SELECT count(*), users.name FROM coffees
+            INNER JOIN users ON coffees.id = users.id
+            WHERE flavor = 'Club-Mate 0,5 l'
+            AND coffees.time BETWEEN
+                datetime('now', 'localtime', '-7 days') AND
+                datetime('now', 'localtime')
+            GROUP BY coffees.id
+        """
+        for (cnt, un) in self.cur.execute(que):
+            users[un] = cnt * 0.5
+        que = """
+            SELECT count(*), users.name FROM coffees
+            INNER JOIN users ON coffees.id = users.id
+            WHERE flavor = 'Club-Mate 0,33 l'
+            AND coffees.time BETWEEN
+                datetime('now', 'localtime', '-7 days') AND
+                datetime('now', 'localtime')
+            GROUP BY coffees.id
+        """
+        for (cnt, un) in self.cur.execute(que):
+            if un in users:
+                users[un] += cnt * 0.33
+            else:
+                users[un] = cnt * 0.33
+        top = []
+        for (un, cnt) in users.items():
+            top.append((un, cnt))
+        top.sort(key=lambda x: x[1], reverse=True)
+        return top

diff --git a/cbgetter.py b/cbgetter.py
index 727cacab589da520f141239ebf3daf0ebe8fdbee..d4fb60d3388df3ddcfed7aa2e243637e54b8b43b 100644 (file)
--- a/cbgetter.py
+++ b/cbgetter.py
@@ -12,6 +12,7 @@ class CoffeebotGetter:
         self.cfg = Conf()
         self.db = Db(self.cfg.getCoffeeDbPath())
         self.msg = ""
+        self.top = []
         return None
 
     def getMsg(self):
@@ -56,3 +57,28 @@ class CoffeebotGetter:
         ]
         ri = randint(0, len(smiles) - 1)
         return smiles[ri]
+
+    def loadTopMateDrinkers(self):
+        """Get list of top Mate drinkers, update message."""
+        self.msg = "*Top 5 Club-Mate drinkers*\n"
+        self.top = self.db.getTopMateDrinkers()
+        totl = [c for (un, c) in self.top]
+        tot = sum(totl)
+        isuff = {1: "st", 2: "nd", 3: "rd", 4: "th", 5: "th"}
+        i = 1
+        for (un, c) in self.top[:5]:
+            self.msg += "{}{} is *{}* who drank `{:.2f}` liters".format(
+                i, isuff[i],
+                un,
+                c
+            )
+            if i == 1:
+                self.msg += self.getHeroComment()
+            self.msg += "\n"
+            i += 1
+        self.msg += "\n"
+        self.msg += "Oh, and together we drank *{:.2f}* liters {}".format(
+            tot,
+            self.getSomeSmile()
+        )
+        return