[PATCH coffee-flask] Add functions for retrieving hiscores from the database
--- 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
Dík za patch. Já na to mrknu později. Teď jsem hodně busy. -M. On Wed, Sep 26 2018, Jaroslav Klapalek wrote:
--- 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
_______________________________________________ Coffee mailing list Coffee@rtime.felk.cvut.cz https://rtime.felk.cvut.cz/mailman/listinfo/coffee
On Wed, Sep 26 2018, Michal Sojka wrote:
Dík za patch. Já na to mrknu později. Teď jsem hodně busy.
Ale jak na to koukám, tak definuješ funkce, ale nikde je nepoužíváš. Přidej tam i nějaký kód, který je používá, ať víme, k čemu to má být dobré. -M.
-M.
On Wed, Sep 26 2018, Jaroslav Klapalek wrote:
--- 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
_______________________________________________ Coffee mailing list Coffee@rtime.felk.cvut.cz https://rtime.felk.cvut.cz/mailman/listinfo/coffee
To je trochu schválně, serverovou část jsem napsal a funguje, ke klientovi jsem se ještě nedostal (mám čistě jen ukázku, ale myslím, že nepůjde integrovat do rozhraní). Přidám ji do dalšího commitu. J. Cituji Michal Sojka <michal.sojka@cvut.cz>:
On Wed, Sep 26 2018, Michal Sojka wrote:
Dík za patch. Já na to mrknu později. Teď jsem hodně busy.
Ale jak na to koukám, tak definuješ funkce, ale nikde je nepoužíváš. Přidej tam i nějaký kód, který je používá, ať víme, k čemu to má být dobré.
-M.
-M.
On Wed, Sep 26 2018, Jaroslav Klapalek wrote:
--- 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
_______________________________________________ Coffee mailing list Coffee@rtime.felk.cvut.cz https://rtime.felk.cvut.cz/mailman/listinfo/coffee
participants (3)
-
Jaroslav Klapalek -
klapajar＠fel.cvut.cz
-
Michal Sojka