def is_admin(username):
db, c = get_dbc()
c.execute('''SELECT admin
FROM user
WHERE username = ?''', (username, ))
result = c.fetchone()
return result['admin'] == 1
def get_availible_id(username, app_id):
db, c = get_dbc()
c.execute(
'''SELECT id
FROM availible
WHERE user = ?
AND app_id = ?''', (username, app_id))
return c.fetchone()['id']
def user_has_app(username, app_id):
db, c = get_dbc()
c.execute(
'''SELECT count(*) AS count
FROM availible
WHERE user = ?
AND app_id = ?''', (username, app_id))
result = c.fetchone()
return result['count'] > 0
def user_get_user_slot_ids(username, limit=3):
"""Return list of specified (default 3) amount of user_slot ids for user"""
db, c = get_dbc()
c.execute(
'''SELECT id
FROM user_slot
WHERE user = ?
LIMIT ?''', (username, limit))
return list(s['id'] for s in c.fetchall())
def ajax_admin_slots_add_slot():
if loggedin():
if is_admin(session['username']):
# add slot
db, c = get_dbc()
c.execute('''INSERT INTO slot
VALUES (null, ?)''', (request.json['slot'], ))
db.commit()
return ('', 204)
else:
abort(403)
def user_exists(username):
"""Checks whether user exists. Returns True if yes, False otherwise"""
db, c = get_dbc()
# count all occurences of username
c.execute('SELECT COUNT(*) FROM user WHERE username = ? GROUP BY username',
(username, ))
result = c.fetchone()
# username should occur once if it exists, fetchone yields None if not
return result is not None
def ajax_admin_slots_remove_slot():
if loggedin():
if is_admin(session['username']):
# remove slot
db, c = get_dbc()
c.execute(
'''DELETE FROM slot
WHERE start_time = ?''', (request.json['slot']))
db.commit()
return ('', 204)
else:
abort(403)
def ajax_admin_remove_user():
if loggedin():
if is_admin(session['username']):
user = request.json['user']
if user_exists(user):
# remove user
db, c = get_dbc()
c.execute(
'''DELETE FROM user
WHERE username = ?''', (user, ))
db.commit()
return jsonify(success='True', user=user)
# if anything went wrong
return jsonify(success='False')
def optimal_slot():
db, c = get_dbc()
# get all slots with the amount of user_slot references
c.execute('''SELECT slot.start_time, user_slot.day, COUNT(*) AS votes
FROM slot
JOIN user_slot ON slot.id = user_slot.slot_id
GROUP BY slot.id, user_slot.day''')
slots = c.fetchall()
# find slots with the most votes and, for slots with the same amount, fitting the secondary criteria best
optimal = {'day': 4, 'start_time': 36, 'votes': 0}
for s in slots:
if s['votes'] > optimal['votes']:
optimal = s
return (optimal['day'], optimal['start_time'])
def updateTimes():
if not loggedin():
return redirect(url_for('login'))
username = session['username']
db, c = get_dbc()
# if form was submitted
if request.method == 'POST':
# delete all user_slot entries for user
c.execute('''DELETE FROM user_slot
WHERE user = ?''', (username, ))
# get slots
c.execute('''SELECT id
FROM slot
ORDER BY start_time ASC''')
slots = list(s['id'] for s in c.fetchall())
# for each slot and each weekday: add user_slot entry if checked
new_entries = []
for s in slots:
for day in range(7):
if request.form.get('slot:{}-day:{}'.format(s,
day)) is not None:
new_entries.append((username, s, day))
c.executemany(
'''INSERT INTO user_slot
VALUES (null, ?, ?, ?)''', new_entries)
db.commit()
return redirect(url_for('overview'))
# if page was requested
else:
# get slots
c.execute('''SELECT id, start_time
FROM slot
ORDER BY start_time ASC''')
slots = c.fetchall()
# get slot length
slot_length = get_setting('slot_length')
# get slots associated with user
c.execute(
'''SELECT slot_id, day
FROM user_slot
WHERE user = ?''', (username, ))
user_slots = c.fetchall()
return render_template('updateTimes.html.j2',
username=username,
slots=slots,
slot_length=slot_length,
user_slots=user_slots)
def create_user(username,
firstname,
lastname,
password,
admin=False,
canVote=True):
# secure password
salt = generate_salt(32)
secure_pw = hash_password(password, salt)
# write to database
db, c = get_dbc()
c.execute('INSERT INTO user VALUES (?, ?, ?, ?, ?, ?, ?)',
(username, firstname, lastname, secure_pw, salt, int(admin),
int(canVote)))
db.commit()
def user_query():
users = []
searchterm = request.args.get('searchterm')
# only search if searchterm is not empty
if searchterm:
db, c = get_dbc()
# get all users that begin with searchterm
var = searchterm + '%'
c.execute(
'''SELECT username
FROM user
WHERE username LIKE ?''', (var, ))
result = c.fetchall()
# extract usernames
if result is not None:
users.extend(u['username'] for u in result)
return jsonify(users=users)
def get_setting(setting):
db, c = get_dbc()
# fetch from db
c.execute('''SELECT value, type
FROM setting
WHERE key = ?''',
(setting,))
result = c.fetchone()
val = result['value']
t = result['type']
# convert type if necessary
if t == 'int':
val = int(val)
elif t == 'bool':
val = val == 'True'
elif t == 'date':
val = datetime.strptime(val, '%Y-%m-%d %H:%M:%S')
return val
def set_setting(setting, value):
db, c = get_dbc()
# get type
t = 'string'
if type(value) is int:
t = 'int'
elif type(value) is bool:
t = 'bool'
elif type(value) is datetime:
t = 'date'
# convert value to string
value = str(value)
# write to db
c.execute('''UPDATE setting
SET value = ?, type = ?
WHERE key = ?''',
(value, t, setting))
db.commit()
def admin_users():
# user needs to be logged in
if not loggedin():
return redirect(url_for('login'))
username = session['username']
# forbidden if user does not have access (operator for app or admin)
if not is_admin(username):
abort(403)
# get all users
db, c = get_dbc()
c.execute('''SELECT username
FROM user''')
result_users = c.fetchall()
users = list((u['username'] for u in result_users))
return render_template('admin_users.html.j2',
username=username,
users=users)
def login_user(username, password):
"""Logs in user. Return True if successful, False otherwise"""
# stop if user doesn't exist
# TODO: merge user doesn't exist and read user data -> one sql statement
if not user_exists(username):
return False
# read user data
db, c = get_dbc()
c.execute('SELECT password, salt FROM user WHERE username = ?',
(username, ))
result = c.fetchone()
# check if password matches
if check_password(password, result['salt'], result['password']):
# TODO: Consider saving logged in unser on server instead of clientside
session['username'] = username
print("%s successful login" % username)
return True
else:
print("%s attempted login" % username)
return False
def admin_slots():
# user needs to be logged in
if not loggedin():
return redirect(url_for('login'))
username = session['username']
# forbidden if user does not have access (operator for app or admin)
if not is_admin(username):
abort(403)
# get all slots
db, c = get_dbc()
c.execute('''SELECT start_time
FROM slot''')
result_slots = c.fetchall()
slots = list((s['start_time'] for s in result_slots))
# get slot length
slot_length = get_setting('slot_length')
return render_template('admin_slots.html.j2',
username=username,
slots=slots,
slot_length=slot_length)