[Coffee] [PATCH coffee-flask] Add functions for retrieving hiscores from the database

[Jaroslav Klapalek]

---
 coffee_db.py | 34 ++++++++++++++++++++++++++++++++++
 1 file changed, 34 insertions(+)

diff --git a/coffee_db.py b/coffee_db.py
index b708d03..7957920 100644
--- a/coffee_db.py
+++ b/coffee_db.py
@@ -1,5 +1,6 @@
 import sqlite3
 import os
+import datetime
 
 dbdir = os.path.dirname(__file__)
 dbdef = os.path.join(dbdir, "coffee_db.sql")
@@ -130,3 +131,36 @@ def coffee_count(uid=None, start=None, stop=None):
         res = "0"
 
     return res
+
+def coffee_hiscores(date_start, date_end, limit=-1):
+    """Shows total coffee consumption between dates per user, ordered desc.
+
+    Note: That means, the top 'consumer' will be first.
+    Note: Setting limit to different number will show only top 'limit' users.
+    """
+    conn, c = open_db()
+
+    res = list(c.execute(
+        """
+        select users.name, count(coffees.id) from coffees
+        left join users on coffees.id = users.id
+        where date(time, 'localtime') between date(?) and date(?)
+        group by coffees.id order by count(coffees.id) desc limit ?
+        """
+        , (date_start, date_end, limit,)))
+
+    close_db(conn)
+    return res
+
+def coffee_weekly_hiscores(week=0, limit=-1):
+    """Shows total coffee consumption in selected week per user.
+
+    Note: Week is relative, so 0 is this week (starting from Monday).
+    Note: Using local time.
+    """
+    today = datetime.datetime.today().date()
+
+    monday = today - datetime.timedelta(days=today.weekday()) + datetime.timedelta(weeks=week)
+    sunday = monday + datetime.timedelta(days=6)
+
+    return coffee_hiscores(monday, sunday, limit)
-- 
2.7.4