On Tue, Apr 09 2019, Tomas Prochazka wrote:
The patch solves the problem of separate counting of individual types drinks. --- app.py | 6 ++++-- coffee_db.py | 11 +++++------ templates/user.html | 2 +- 3 files changed, 10 insertions(+), 9 deletions(-)
diff --git a/app.py b/app.py index 62f79b8..5b94cc6 100644 --- a/app.py +++ b/app.py @@ -51,10 +51,12 @@ def logout(): def user(): if "uid" in session: uid = session["uid"] + count_all=db.drink_count(uid, 0) return render_template('user.html', name=db.get_name(uid), flavors=[_name for (_name, _ord) in db.flavors()], - count=db.coffee_count(uid, 0), + count_mate=count_all[0], + count=count_all[1], stamp=time.time() ) # TODO: Replace stamp parameter with proper cache control HTTP @@ -173,7 +175,7 @@ def coffee_add(): def coffee_count(): start = request.args.get("start") stop = request.args.get("stop") - return str(db.coffee_count(session.get("uid"), start, stop)) + return str(db.drink_count(session.get("uid"), start, stop)[0])
@app.route('/js') diff --git a/coffee_db.py b/coffee_db.py index 348113a..1857c14 100644 --- a/coffee_db.py +++ b/coffee_db.py @@ -122,7 +122,7 @@ def coffee_history(uid=None): close_db(conn) return res
-def coffee_count(uid=None, start=None, stop=None): +def drink_count(uid=None, start=None, stop=None): conn, c = open_db()
args = [] @@ -138,13 +138,12 @@ def coffee_count(uid=None, start=None, stop=None): if stop is not None: clauses.append("date(time, 'localtime') <= date('now', 'localtime', '-%d days')" % int(stop))
- for count, in c.execute( - "select count(*) from coffees where " + + for count_mate, count_coffee in c.execute( + "select COUNT(CASE WHEN flavor like 'Club-Mate%' then 1 ELSE NULL END) as 'Mate',COUNT(CASE WHEN flavor not like 'Club-Mate%' then 1 ELSE NULL END) as 'Coffee' from coffees where " + " and ".join(clauses) , args):
Ještě bych to zjednodušil. Stačí udělat jeden SQL dotaz místo dvou: SELECT COUNT(), CASE WHEN flavor LIKE 'Club%' THEN 'Club-Mate' ELSE 'Coffee' END AS drink FROM coffees GROUP BY drink, ... -Michal