Update changelog

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

diff --git a/cbdb.py b/cbdb.py
index 1283d51489b0bb28af802d248724d6b3296c68b4..22e00581ae4326a79e7724c32764a61239fe5fc8 100644 (file)
--- a/cbdb.py
+++ b/cbdb.py
@@ -1,11 +1,13 @@
 # -*- coding: utf-8 -*-
 """Database access."""
 from sqlite3 import connect
-from cbconf import Conf
 
 class FileNotSetError(ValueError):
     pass
 
+class ArgCountError(ValueError):
+    pass
+
 class Db:
     def __init__(self, dbpath=False):
         if dbpath:
@@ -19,3 +21,135 @@ 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
+
+    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``.
+
+        Keyword arguments:
+        flavor -- The flavor of beverage.
+        dtf -- Date and time *from*.
+        dtt -- Date and time *to*.
+        """
+        if not ((len(args) == 3 and len(kwargs) == 0) or
+                (len(args) == 0 and len(kwargs) == 3)):
+            raise ArgCountError("3 arguments needed: flavor, from, and to")
+        if args:
+            flavor = args[0]
+            dtf = args[1]
+            dtt = args[2]
+        elif kwargs:
+            flavor = kwargs["flavor"]
+            dtf = kwargs["dtf"]
+            dtt = kwargs["dtt"]
+        flavors = flavor.split(";")
+        que = """
+            SELECT count(*), users.name FROM coffees
+            INNER JOIN users ON coffees.id = users.id
+        """
+        for f in flavors:
+            if f is flavors[0]:
+                que += "WHERE flavor = '{}'".format(f)
+            else:
+                que += "OR flavor = '{}'".format(f)
+        que += """
+            AND coffees.time BETWEEN
+                datetime('{}', 'localtime') AND
+                datetime('{}', 'localtime')
+            GROUP BY coffees.id
+        """.format(dtf, dtt)
+        drunk = []
+        for (cnt, un) in self.cur.execute(que):
+            drunk.append((un, cnt))
+        return drunk
+
+    def getDrunkList(self, *args, **kwargs):
+        """Return dict of lists of drunken ``flavor`` from ``dtf`` to ``dtt``.
+
+        Keyword arguments:
+        flavor -- The flavor of beverage.
+        dtf -- Date and time *from*.
+        dtt -- Date and time *to*.
+        """
+        if not ((len(args) == 3 and len(kwargs) == 0) or
+                (len(args) == 0 and len(kwargs) == 3)):
+            raise ArgCountError("3 arguments needed: flavor, from, and to")
+        if args:
+            flavor = args[0]
+            dtf = args[1]
+            dtt = args[2]
+        elif kwargs:
+            flavor = kwargs["flavor"]
+            dtf = kwargs["dtf"]
+            dtt = kwargs["dtt"]
+        flavors = flavor.split(";")
+        drunk = {}
+        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
+            """.format(f, dtf, dtt)
+            for (cnt, un) in self.cur.execute(que):
+                if not un in drunk:
+                    drunk[un] = [0 for j in range(i)]
+                drunk[un].append(cnt)
+            i += 1
+        return drunk