Update queries to new database structure

diff --git a/cbdb.py b/cbdb.py
index 2c53f41aa016cb94c060e33fd3455f1fc18558b9..896ddc9f8bd553a73ffa12bc6aba0241fddfcc1e 100644 (file)
--- a/cbdb.py
+++ b/cbdb.py
@@ -44,24 +44,30 @@ class Db:
         """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
+
+        SELECT count(*), users.name FROM coffees
+        LEFT JOIN identifiers on coffees.id = identifiers.id
+        LEFT JOIN users on identifiers.userid = users.id
+        WHERE flavor = 'Club-Mate 0,5 l'
+        AND coffees.time BETWEEN
+            datetime('now', 'localtime', '-7 days') AND
+            datetime('now', 'localtime')
+        GROUP BY identifiers.userid
+
         """
         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
+
+        SELECT count(*), users.name FROM coffees
+        LEFT JOIN identifiers on coffees.id = identifiers.id
+        LEFT JOIN users on identifiers.userid = users.id
+        WHERE flavor = 'Club-Mate 0,33 l'
+        AND coffees.time BETWEEN
+            datetime('now', 'localtime', '-7 days') AND
+            datetime('now', 'localtime')
+        GROUP BY identifiers.userid
+
         """
         for (cnt, un) in self.cur.execute(que):
             if un in users:
@@ -79,12 +85,13 @@ class Db:
         q = """
 
         SELECT count(*), users.name FROM coffees
-        INNER JOIN users ON coffees.id = users.id
+        LEFT JOIN identifiers on coffees.id = identifiers.id
+        LEFT JOIN users on identifiers.userid = users.id
         WHERE flavor = 'tea'
         AND coffees.time BETWEEN
             datetime('now', 'localtime', '-7 days') AND
             datetime('now', 'localtime')
-        GROUP BY coffees.id
+        GROUP BY identifiers.userid
 
         """
         top = []
@@ -157,13 +164,16 @@ class Db:
         i = 0
         for f in flavors:
             que = """
-                SELECT count(*), users.name FROM coffees
-                INNER JOIN users ON coffees.id = users.id
-                WHERE flavor = '{}'
-                AND coffees.time BETWEEN
-                    datetime('{}', 'localtime') AND
-                    datetime('{}', 'localtime')
-                GROUP BY coffees.id
+
+            SELECT count(*), users.name FROM coffees
+            LEFT JOIN identifiers on coffees.id = identifiers.id
+            LEFT JOIN users on identifiers.userid = users.id
+            WHERE flavor = '{}'
+            AND coffees.time BETWEEN
+                datetime('{}', 'localtime') AND
+                datetime('{}', 'localtime')
+            GROUP BY identifiers.userid
+
             """.format(f, dtf, dtt)
             for (cnt, un) in self.cur.execute(que):
                 if not un in drunk: