def get_balance(user_id, session=get_session()):
sql = text("""
SELECT user_id, count(*) AS amount
FROM scanevent
WHERE user_id = :user_id
GROUP BY user_id
""")
row = session.connection().execute(sql, user_id=user_id).fetchone()
if not row:
cost = 0
else:
cost = row.amount
sql = text("""
SELECT user_id, sum(amount) AS amount
FROM rechargeevent
WHERE user_id = :user_id
GROUP BY user_id
""")
# print(sql, user_id)
row = session.connection().execute(sql, user_id=user_id).fetchone()
if not row:
credit = 0
else:
credit = row.amount
return credit - cost
def sync_recharges_real():
data = requests.get(config.money_url,
auth=HTTPBasicAuth(config.money_user,
config.money_password))
recharges = data.json()
session = get_session()
got_by_user = get_existing(session)
for uid, charges in recharges.iteritems():
log.info("Syncing recharges for user %s", uid)
if uid not in got_by_user:
log.info("First recharge for user %s!", uid)
got_by_user[uid] = []
got = got_by_user[uid]
for charge in charges:
charge_date = datetime.strptime(charge['date'], "%Y-%m-%d")
charge_amount = Decimal(charge['amount'])
log.debug("charge: %s, %s", charge, charge_date)
found = False
for exist in got:
if exist.timestamp != charge_date: continue
if exist.amount != charge_amount: continue
# found a matching one
found = True
break
if found: continue
handle_transferred(charge, charge_amount, charge_date, got,
session, uid)
def delete_if_nomoney(user, session=get_session()):
if not user['path'].endswith(",ou=temp_members,dc=flipdot,dc=org"):
return
balance = Users.get_balance(user['id'], session=session)
if balance <= 0:
print "deleting user " + str(user['id']) + " because they are broke"
Users.delete(user)
def delete_if_nomoney(user, session=get_session()):
if not user['path'].endswith(",ou=temp_members,dc=flipdot,dc=org"):
return
balance = Users.get_balance(user['id'], session=session)
if balance <= 0:
print("deleting user " + str(user['id']) + " because they are broke")
Users.delete(user)
def get_balance(user_id, session=get_session()):
sql = text("""
SELECT user_id, count(*) AS amount
FROM scanevent
WHERE user_id = :user_id
GROUP BY user_id
""")
row = session.connection().execute(sql, user_id=user_id).fetchone()
if not row:
cost = 0
else:
cost = row.amount
sql = text("""
SELECT user_id, sum(amount) AS amount
FROM rechargeevent
WHERE user_id = :user_id
GROUP BY user_id
""")
# print sql, user_id
row = session.connection().execute(sql, user_id=user_id).fetchone()
if not row:
credit = 0
else:
credit = row.amount
return credit - cost
def recharge_doit():
user_id = request.form['user_user']
helper_id = request.form['helper_user']
amount = request.form['amount']
if not user_id or not helper_id or not amount:
return 'Please enter valid data!'
if amount == u"0":
return "Invalid amount"
if not uid_pattern.match(user_id):
return "Invalid user id"
if not uid_pattern.match(helper_id):
return "Invalid helper id"
users = Users.get_all(filters=['uidNumber=' + user_id])
helpers = Users.get_all(filters=['uidNumber=' + helper_id])
if not users:
return "user %s not found" % user_id
if not helpers:
return "user %s not found" % helper_id
user = users[0]
helper = helpers[0]
session = get_session()
ev = RechargeEvent(user['id'], helper['id'], amount)
session.add(ev)
session.commit()
return render_template('recharge_success.html', amount=amount, user=user)
def get_recharges(user_id, session=get_session(), limit=None):
# type: (str, session) -> RechargeEvent
q = session.query(RechargeEvent).filter(
RechargeEvent.user_id == user_id).order_by(
RechargeEvent.timestamp.desc())
if limit:
q = q.limit(limit)
return q.all()
def get_recharges(user_id, session=get_session(), limit=None):
# type: # (str, session) -> RechargeEvent
q = session.query(RechargeEvent).filter(
RechargeEvent.user_id == user_id).order_by(
RechargeEvent.timestamp.desc())
if limit:
q = q.limit(limit)
return q.all()
def stat_drink(self, args, pos):
drink = DrinksManager.get_instance().get_selected_drink()
if drink:
session = get_session()
ev = ScanEvent(drink['ean'], 0, datetime.datetime.now())
session.add(ev)
session.commit()
DrinksManager.get_instance().set_selected_drink(None)
self.reset()
def aufladen(user, euro):
session = get_session()
ev = RechargeEvent(
user["id"],
user["id"],
euro
)
session.add(ev)
session.commit()
def save_payment(self, param, pos):
session = get_session()
ev = RechargeEvent(self.user['id'], 'DISPLAY', self.payment_amount)
session.add(ev)
session.commit()
screen_manager = ScreenManager.get_instance()
screen_manager.set_active(
SuccessScreen(self.screen, self.user, None,
"EUR %s aufgeladen" % self.payment_amount, session))
self.payment_amount = None
def send_summaries():
session = get_session()
if config.FORCE_MAIL_TO:
send_summary(session, Users.get_by_id(config.FORCE_MAIL_TO), "Getränkeübersicht", force=True)
return
for user in Users.get_all():
try:
send_summary(session, user, "Getränkeübersicht")
except Exception:
logging.exception("While sending summary for %s", user)
continue
def stat_drink(self):
drink = DrinksManager.get_instance().get_selected_drink()
if drink:
session = get_session()
ev = ScanEvent(
drink['ean'],
0,
datetime.datetime.now()
)
session.add(ev)
session.commit()
DrinksManager.get_instance().set_selected_drink(None)
self.reset()
def send_low_balances(with_summary=True):
session = get_session()
if config.FORCE_MAIL_TO:
send_low_balance(session, Users.get_by_id(config.FORCE_MAIL_TO), with_summary, force=True)
return
for user in Users.get_all():
try:
send_low_balance(session, user, with_summary)
except Exception:
logging.exception("while sending lowbalances:")
continue
def send_low_balances(with_summary=True):
session = get_session()
if config.FORCE_MAIL_TO:
send_low_balance(session, Users.get_by_id(config.FORCE_MAIL_TO), with_summary, force=True)
return
for user in Users.get_all():
try:
send_low_balance(session, user, with_summary)
except Exception:
logger.exception("while sending lowbalances:")
continue
def get_stats(self):
session = get_session()
sql = text("""
SELECT COUNT(*) as count, barcode as name
FROM scanevent
WHERE user_id = :userid
GROUP BY barcode
ORDER by count DESC
""")
userid=self.user['id']
result = session.connection().execute(sql, userid=userid).fetchall()
return result
def send_summaries():
session = get_session()
if config.FORCE_MAIL_TO:
send_summary(session, Users.get_by_id(config.FORCE_MAIL_TO), "Getränkeübersicht", force=True)
return
for user in Users.get_all():
try:
send_summary(session, user, "Getränkeübersicht")
except Exception:
logger.exception("While sending summary for %s", user)
continue
def save_payment(self):
session = get_session()
ev = RechargeEvent(
self.user['id'],
'DISPLAY',
self.payment_amount
)
session.add(ev)
session.commit()
screen_manager = ScreenManager.get_instance()
screen_manager.set_active(
SuccessScreen(self.screen, self.user, None,
"EUR %s aufgeladen" % self.payment_amount, session))
self.payment_amount = None
def __get_drinks_count(self, date):
session = get_session()
result = session.execute("""
SELECT
timestamp::date date,
COUNT(timestamp) amountd
FROM scanevent
WHERE timestamp::date = '{today}'
GROUP BY timestamp::date
""".format(today=date)).fetchall()
row = result[0]
date = row[0]
amount = row[1]
return amount
def main():
users = Users.get_all(include_temp=True)
init_db()
session = get_session()
for user in users:
ldapName = user["name"]
ldapUser = LdapUser(ldapId=user["id"],
name=ldapName,
id_card=user["id_card"],
path=user["path"],
is_card=ldapName.startswith("geld"))
session.add(ldapUser)
session.commit()
def scans(limit=1000, hours=None):
session = get_session()
where = ""
params = {}
if hours:
where = "WHERE se.timestamp > NOW() - INTERVAL ':hours HOUR'"
params['hours'] = hours
sql = """
SELECT se.id, barcode, se.timestamp, name
FROM scanevent se
LEFT OUTER JOIN drink d on se.barcode = d.ean
%s
ORDER BY timestamp DESC
LIMIT %d
""" % (where, limit)
sql_scans = session.execute(sql, params).fetchall()
return [dict(zip(row.keys(), row)) for row in sql_scans]
def scans(limit=1000, hours=None):
session = get_session()
where = ""
params = {}
if hours:
where = "WHERE se.timestamp > NOW() - INTERVAL ':hours HOUR'"
params['hours'] = hours
sql = """
SELECT se.id, barcode, se.timestamp, name
FROM scanevent se
LEFT OUTER JOIN drink d on se.barcode = d.ean
%s
ORDER BY timestamp DESC
LIMIT %d
""" % (where, limit)
scans = session.execute(sql, params).fetchall()
return [dict(zip(row.keys(), row)) for row in scans]
def save_drink(self, args, pos):
session = get_session()
drink = DrinksManager.get_instance().get_selected_drink()
if not drink:
return
ev = ScanEvent(
drink['ean'],
self.user['id'],
datetime.datetime.now()
)
session.add(ev)
session.commit()
DrinksManager.get_instance().set_selected_drink(None)
Users.delete_if_nomoney(self.user)
screen_manager = ScreenManager.get_instance()
screen_manager.set_active(SuccessScreen(self.screen, self.user, drink,
"getrunken: %s" % drink['name'], session))
def upload_scanevents():
session = get_session()
result = session.execute("""
SELECT scanevent.id, scanevent.timestamp as time, drink.name, type
FROM scanevent
LEFT JOIN drink ON scanevent.barcode = drink.ean
WHERE scanevent.uploaded_to_influx = FALSE;
""")
for scanevent in result:
id = scanevent['id']
time = scanevent['time']
name = scanevent['name']
type = scanevent['type']
if name is None:
continue
json_body = [{
"measurement": "drink_scans",
"tags": {
"region": "kassel",
"type": type,
"name": name
},
"time": time,
"fields": {
"name": name,
"type": type,
"value": 1
}
}]
influx_client.write_points(json_body)
session.execute(
"""
UPDATE scanevent
SET uploaded_to_influx = TRUE
WHERE id = :id
""", {"id": id})
session.commit()
def main():
users = list(Users.get_all(include_temp=True))
init_db()
session = get_session()
for user in users:
ldap_name = user["name"]
ldap_user = LdapUser(
ldap_id=user["id"],
name=ldap_name,
id_card=user["id_card"],
path=user["path"],
is_card=ldap_name.startswith("geld")
)
session.add(ldap_user)
session.commit()
def upload_scanevents():
session = get_session()
result = session.execute("""
SELECT scanevent.id, scanevent.timestamp as time, drink.name, type
FROM scanevent
LEFT JOIN drink ON scanevent.barcode = drink.ean
WHERE scanevent.uploaded_to_influx = FALSE;
""")
for scanevent in result:
scan_id = scanevent['id']
scan_time = scanevent['time']
scan_name = scanevent['name']
scan_type = scanevent['type']
if scan_name is None:
continue
json_body = [
{
"measurement": "drink_scans",
"tags": {
"region": "kassel",
"type": scan_type,
"name": scan_name
},
"time": scan_time,
"fields": {
"name": scan_name,
"type": scan_type,
"value": 1
}
}
]
influx_client.write_points(json_body)
session.execute("""
UPDATE scanevent
SET uploaded_to_influx = TRUE
WHERE id = :id
""", {"id": scan_id})
session.commit()
def send_drink(user, drink, with_summary=False):
try:
if user['email'] and 'instant' in user['meta']['drink_notification']:
content_text = u"Du hast das folgende Getränk getrunken: {drink_name}.".format(drink_name=drink['name'])
content_html = render_jinja_html('instant.html', drink_name=drink['name'])
if not with_summary:
content_text += FOOTER.format(uid=user['id'])
content_html = render_jinja_html('main.html', prepend_html=content_html)
send_notification(user['email'], "Getränk getrunken", content_text, content_html, user['id'])
return
session = get_session()
send_summary(session, user,
subject="Getränk getrunken",
prepend_text=content_text,
prepend_html=content_html,
force=True)
except Exception:
logging.exception("while sending drink noti")
pass
def get_by_ean(ean):
if ean in _drink_cache:
return _drink_cache[ean]
session = get_session()
sql = text("""
SELECT *
FROM drink
WHERE ean = :ean
""")
row = session.connection().execute(sql, ean=ean).fetchone()
if row:
drink = dict(zip(row.keys(), row))
_drink_cache[ean] = drink
else:
drink = {
'name': 'Unbekannt (' + ean + ')',
'size': 0,
'tags': ['unknown'],
'ean': ean
}
return drink
def send_drink(user, drink, with_summary=False):
try:
if user['email'] and 'instant' in user['meta']['drink_notification']:
content_text = u"Du hast das folgende Getränk getrunken: {drink_name}.".format(drink_name=drink['name'])
content_html = render_jinja_html('instant.html', drink_name=drink['name'])
if not with_summary:
content_text += FOOTER.format(uid=user['id'])
content_html = render_jinja_html('main.html', prepend_html=content_html)
send_notification(user['email'], "Getränk getrunken", content_text, content_html, user['id'])
return
session = get_session()
send_summary(session, user,
subject="Getränk getrunken",
prepend_text=content_text,
prepend_html=content_html,
force=True)
except Exception:
logger.exception("while sending drink noti")
pass
def recharge_doit():
user_id = request.form['user_user']
helper_id = request.form['helper_user']
amount = request.form['amount']
if not user_id or not helper_id or not amount:
return 'Please enter valid data!'
if amount == u"0":
return "Invalid amount"
if not uid_pattern.match(user_id):
return "Invalid user id"
if not uid_pattern.match(helper_id):
return "Invalid helper id"
users = Users.get_all(filters=['uidNumber=' + user_id])
helpers = Users.get_all(filters=['uidNumber=' + helper_id])
if not users:
return "user %s not found" % user_id
if not helpers:
return "user %s not found" % helper_id
user = users[0]
helper = helpers[0]
session = get_session()
ev = RechargeEvent(
user['id'],
helper['id'],
amount
)
session.add(ev)
session.commit()
return render_template('recharge_success.html', amount=amount, user=user)
def get_by_ean(ean):
if ean in _drink_cache:
return _drink_cache[ean]
session = get_session()
sql = text("""
SELECT *
FROM drink
WHERE ean = :ean
""")
row = session.connection().execute(sql, ean=ean).fetchone()
if row:
drink = dict(zip(row.keys(), row))
_drink_cache[ean] = drink
else:
drink = {
'name': 'Unbekannt (' + ean + ')',
'size': 0,
'tags': ['unknown'],
'ean': ean
}
return drink
#!/usr/bin/env python
import argparse
from database.models.drink import Drink
from database.storage import get_session
from database.storage import init_db
init_db()
parser = argparse.ArgumentParser(description='Adds new drinks to the drink database.')
parser.add_argument('--ean', required=True, help='The EAN code of the barcode, just numbers without starting "E"')
parser.add_argument('--name', required=True, help='The name that should be displayed')
parser.add_argument('--size', required=True, type=float,
help='The size of the bottle, eg. 0.33l or 0,5l, ommitting the "l"')
args = parser.parse_args()
session = get_session()
ev = Drink(
args.ean,
args.name,
args.size
)
session.add(ev)
session.commit()
print('OK, done!')
from database.storage import init_db
from database.storage import get_session
from database.models.drink import Drink
init_db()
parser = argparse.ArgumentParser(
description='Adds new drinks to the drink database.')
parser.add_argument(
'--ean',
required=True,
help='The EAN code of the barcode, just numbers without starting "E"')
parser.add_argument('--name',
required=True,
help='The name that should be displayed')
parser.add_argument(
'--size',
required=True,
type=float,
help='The size of the bottle, eg. 0.33l or 0,5l, ommitting the "l"')
args = parser.parse_args()
session = get_session()
ev = Drink(args.ean, args.name, args.size)
session.add(ev)
session.commit()
print 'OK, done!'
def __write_db(self, barcode):
session = get_session()
ev = ScanEvent(barcode, datetime.datetime.now())
session.add(ev)
session.commit()
def aufladen(self, user, euro):
session = get_session()
ev = RechargeEvent(user["id"], user["id"], euro)
session.add(ev)
session.commit()
