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)
#!/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()