--- 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)