Update changelog

[hubacji1/coffee-getter.git] / cbdb.py

diff --git a/cbdb.py b/cbdb.py
index 8bbc11c7bd3654c8c2b9c56daf12d087a9157d6e..3f7f0569fc47453613b41a13d0c20a0ab84475e1 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], x[0]), reverse=True)
+        return top