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

[Michal Sojka]

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 at rtime.felk.cvut.cz
> https://rtime.felk.cvut.cz/mailman/listinfo/coffee