Exemple #1
0
def is_admin(username):
    db, c = get_dbc()
    c.execute('''SELECT admin
        FROM user
        WHERE username = ?''', (username, ))
    result = c.fetchone()
    return result['admin'] == 1
Exemple #2
0
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']
Exemple #3
0
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
Exemple #4
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())
Exemple #5
0
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)
Exemple #6
0
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
Exemple #7
0
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)
Exemple #8
0
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')
Exemple #9
0
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'])
Exemple #10
0
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)
Exemple #11
0
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()
Exemple #12
0
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)
Exemple #13
0
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
Exemple #14
0
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()
Exemple #15
0
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)
Exemple #16
0
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
Exemple #17
0
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)