Merge branch 'hotfix/0.4.3-stats-for-3-plus-people-only'

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

diff --git a/cbdb.py b/cbdb.py
index b78aaf5926f568a140cf245e88ce08eb72bcffe9..2c53f41aa016cb94c060e33fd3455f1fc18558b9 100644 (file)
--- a/cbdb.py
+++ b/cbdb.py
@@ -22,6 +22,24 @@ class Db:
         if self.con:
             self.con.close()
 
+    def get_top_drinks(self):
+        """Return list of pairs of drink name and count."""
+        q = """
+
+        SELECT count(*), flavor
+        FROM coffees
+        WHERE time BETWEEN
+            datetime('now', 'localtime', '-7 days')
+            AND datetime('now', 'localtime')
+        GROUP BY flavor
+
+        """
+        top = []
+        for (cnt, dn) in self.cur.execute(q):
+            top.append((dn, cnt))
+        top.sort(key=lambda x: (x[1], x[0]), reverse=True)
+        return top
+
     def getTopMateDrinkers(self):
         """Return list of pairs of name, count for Mate drinkers."""
         users = {}
@@ -56,6 +74,25 @@ class Db:
         top.sort(key=lambda x: (x[1], x[0]), reverse=True)
         return top
 
+    def get_top_tea_drinkers(self):
+        """Return list of pairs of name, count for tea drinkers."""
+        q = """
+
+        SELECT count(*), users.name FROM coffees
+        INNER JOIN users ON coffees.id = users.id
+        WHERE flavor = 'tea'
+        AND coffees.time BETWEEN
+            datetime('now', 'localtime', '-7 days') AND
+            datetime('now', 'localtime')
+        GROUP BY coffees.id
+
+        """
+        top = []
+        for (cnt, un) in self.cur.execute(q):
+            top.append((un, cnt))
+        top.sort(key=lambda x: (x[1], x[0]), reverse=True)
+        return top
+
     def getDrunkSum(self, *args, **kwargs):
         """Return list of drunken ``flavor`` from ``dtf`` to ``dtt``.