def booking_remove(booking_id: int):
token_data = simple_jwt.read(
request.headers.get('Authorization').split(' ')[1])
user_perms = get_role_perms(token_data.get('role'))
if user_perms.get('booking_delete', 0) or user_perms.get(
'booking_delete_others', 0):
user_id = token_data.get('user')
if user_perms.get('booking_delete_others', 0):
req_data = request.get_json()
if req_data:
user_id = req_data.get('user_id', token_data.get('user'))
with get_db_conn() as database:
try:
cursor = database.cursor()
cursor.execute(
'DELETE FROM bookings WHERE id = ? AND user_id = ?',
[booking_id, user_id])
database.commit()
result = make_response({'ok': True}, 200)
except sqlite3.Error:
database.rollback()
result = server_error('BOOKING_NOT_FOUND')
finally:
cursor.close()
return result
return abort(401)
def course_add():
token_data = simple_jwt.read(
request.headers.get('Authorization').split(' ')[1])
user_perms = get_role_perms(token_data.get('user'))
req_data = request.get_json()
if req_data:
if user_perms.get('course_add', 0):
with get_db_conn() as database:
try:
cursor = database.cursor()
cursor.execute("""INSERT INTO courses (name) VALUES (?)""",
[req_data.get('name')])
last_id_inserted = cursor.lastrowid
database.commit()
result = make_response(
{
'ok': True,
'data': last_id_inserted
}, 200)
except sqlite3.Error as e:
database.rollback()
result = abort(500)
print(e)
finally:
cursor.close()
return result
return abort(401)
return abort(400)
def user_update(user_id: int):
token_data = simple_jwt.read(
request.headers.get('Authorization').split(' ')[1])
user_perms = get_role_perms(token_data.get('role'))
req_data = request.get_json()
if req_data:
if user_perms.get('user_get', 0) or user_perms.get(
'user_get_others', 0):
if not user_perms.get('user_get_others', 0):
if user_id != token_data.get('user'):
return abort(401)
req_data.pop('id', None)
sql_str = 'UPDATE users SET ' + (', '.join(
f'{v} = ?' for v in req_data.keys())) + ' WHERE id = ?'
with get_db_conn() as database:
try:
cursor = database.cursor()
cursor.execute(sql_str,
list(req_data.values()) + [user_id])
database.commit()
result = make_response({'ok': True}, 200)
except sqlite3.Error as e:
database.rollback()
result = server_error('USER_NOT_FOUND')
print(e)
finally:
cursor.close()
return result
abort(400)
def user_list():
token_data = simple_jwt.read(
request.headers.get('Authorization').split(' ')[1])
user_perms = get_role_perms(token_data.get('role'))
if user_perms.get('user_list', 0):
with get_db_conn(True) as database:
cursor = database.cursor()
cursor.execute(
"""SELECT users.id, users.email, users.name, users.surname, users.gender, users.role_id
FROM users""")
db_data = cursor.fetchall()
cursor.close()
db_results = []
if db_data:
for row in db_data:
db_results.append({
'id': row[0],
'email': row[1],
'name': row[2],
'surname': row[3],
'gender': row[4],
'role': {
'id': row[5]
}
})
return make_response({'ok': True, 'data': db_results}, 200)
abort(401)
def user_add():
token_data = simple_jwt.read(
request.headers.get('Authorization').split(' ')[1])
user_perms = get_role_perms(token_data.get('role'))
req_data = request.get_json()
if req_data:
if user_perms.get('user_add', 0):
req_data.pop('id', None)
if req_data.get('password') is None:
req_data.set('password', '')
req_data.set('role_id', 0)
sql_str = 'INSERT INTO users (' + (', '.join(req_data.keys())) + ') VALUES (' \
+ (', '.join('?' for x in req_data.keys())) + ')'
with get_db_conn() as database:
try:
cursor = database.cursor()
cursor.execute(sql_str, list(req_data.values()))
last_id_inserted = cursor.lastrowid
database.commit()
result = make_response(
{
'ok': True,
'data': last_id_inserted
}, 200)
except sqlite3.Error as e:
database.rollback()
result = abort(500)
print(e)
finally:
cursor.close()
return result
abort(401)
abort(400)
def course_list():
with get_db_conn(True) as database:
cursor = database.cursor()
cursor.execute('SELECT * FROM courses')
db_data = cursor.fetchall()
db_desc = cursor.description
cursor.close()
db_result = db_data_to_list(db_data, db_desc)
return make_response({'ok': True, 'data': db_result}, 200)
def get_role_perms(role_id: int):
if role_id:
perms = get_role_perms.__cache.get(role_id)
if perms:
return perms
else:
with get_db_conn(True) as database:
cursor = database.cursor()
cursor.execute('SELECT * FROM roles WHERE id = ?', [role_id])
db_data = cursor.fetchone()
db_desc = cursor.description
cursor.close()
get_role_perms.__cache[role_id] = dict(
map(lambda x, y: (x[0], y), db_desc, db_data))
return get_role_perms.__cache.get(role_id)
return {}
def lesson_get(day: int, course: int):
with get_db_conn(True) as database:
cursor = database.cursor()
cursor.execute(
"""SELECT courses.id, courses.name, users.id, users.name, users.surname,
bookings.hour FROM courses
LEFT JOIN teachers ON teachers.course_id = courses.id
LEFT JOIN users ON users.id = teachers.user_id
LEFT JOIN bookings ON bookings.teacher_id = users.id AND bookings.day = ?
AND bookings.status != 'CANCELED'
WHERE courses.id = ?
ORDER BY courses.id, users.id, bookings.day""",
[day, course])
db_data = cursor.fetchall()
cursor.close()
if db_data:
teachers = []
for key, rows in itertools.groupby(db_data, key=lambda x: x[2]):
t = None
available_on = [46800, 50400, 54000, 57600, 61200]
for r in rows:
if t is None:
t = r
try:
available_on.remove(int(r[5]))
except (ValueError, TypeError):
pass
if len(available_on) > 0:
teachers.append({
'id': t[2],
'name': t[3],
'surname': t[4],
'available_on': available_on
})
course = {'id': db_data[0][0], 'name': db_data[0][1]}
return make_response(
{
'ok': True,
'data': {
'day': day,
'course': course,
'teachers': teachers
}
}, 200)
return server_error('LESSON_NOT_FOUND')
def booking_add():
token_data = simple_jwt.read(
request.headers.get('Authorization').split(' ')[1])
user_perms = get_role_perms(token_data.get('role'))
req_data = request.get_json()
if req_data:
if user_perms.get('booking_add', 0) or user_perms.get(
'booking_add_other', 0):
user_id = token_data.get('user')
if user_perms.get('booking_add_other', 0):
user_id = req_data.get('user_id', token_data.get('user'))
with get_db_conn() as database:
try:
cursor = database.cursor()
for hour in req_data.get('hours'):
cursor.execute(
"""INSERT INTO bookings (user_id, course_id, course_name, teacher_id,
teacher_name, teacher_surname, day, hour)
SELECT ?, c.id, c.name, t.id, t.name, t.surname, ?, ? FROM users AS t
JOIN courses AS c ON c.id = ? WHERE t.id = ? AND
NOT EXISTS(SELECT b2.id FROM bookings as b2 WHERE b2.teacher_id = t.id
AND b2.day = ? AND b2.hour = ?
AND (b2.status = 'DONE' OR b2.status = 'RESERVED'))""",
[
user_id,
req_data.get('day'), hour,
req_data.get('course_id'),
req_data.get('teacher_id'),
req_data.get('day'), hour
])
last_id_inserted = cursor.lastrowid
database.commit()
if last_id_inserted > 0:
result = make_response({'ok': True}, 200)
else:
result = abort(400)
except sqlite3.Error as e:
print(e)
database.rollback()
result = abort(500)
finally:
cursor.close()
return result
return abort(401)
return abort(400)
def booking_get(booking_id: int):
token_data = simple_jwt.read(
request.headers.get('Authorization').split(' ')[1])
user_perms = get_role_perms(token_data.get('role'))
if user_perms.get('booking_get', 0) or user_perms.get(
'booking_get_others', 0):
user_id = token_data.get('user')
if user_perms.get('booking_get_others', 0):
req_data = request.get_json()
if req_data:
user_id = req_data.get('user_id', token_data.get('user'))
with get_db_conn(True) as database:
cursor = database.cursor()
cursor.execute(
"""SELECT bookings.id, bookings.status, bookings.day, bookings.hour, bookings.course_id,
bookings.course_name, bookings.teacher_id, bookings.teacher_name,
bookings.teacher_surname FROM bookings
WHERE bookings.id = ? AND bookings.user_id = ?""",
[booking_id, user_id])
db_data = cursor.fetchone()
cursor.close()
if db_data:
course = {'id': db_data[4], 'name': db_data[5]}
teacher = {
'id': db_data[6],
'name': db_data[7],
'surname': db_data[8]
}
lesson = {
'day': db_data[2],
'hour': db_data[3],
'course': course,
'teachers': [teacher]
}
return make_response(
{
'ok': True,
'data': {
'id': db_data[0],
'status': db_data[1],
'lesson': lesson
}
}, 200)
return server_error('BOOKING_NOT_FOUND')
return abort(401)
def course_get(course_id: int):
with get_db_conn(True) as database:
cursor = database.cursor()
cursor.execute("""SELECT courses.id, courses.name, users.id, users.name, users.surname FROM courses
LEFT JOIN teachers ON teachers.course_id = courses.id
LEFT JOIN users ON users.id = teachers.user_id
WHERE courses.id = ? ORDER BY courses.id""", [course_id])
db_data = cursor.fetchall()
cursor.close()
if db_data:
users = []
for key, rows in itertools.groupby(db_data, key=lambda x: x[0]):
for r in rows:
if r[2] is not None:
users.append({'id': r[2], 'name': r[3], 'surname': r[4]})
db_result = {'id': db_data[0][0], 'name': db_data[0][1], 'teachers': users}
return make_response({'ok': True, 'data': db_result}, 200)
return server_error('COURSE_NOT_FOUND')
def course_remove(course_id: int):
token_data = simple_jwt.read(
request.headers.get('Authorization').split(' ')[1])
user_perms = get_role_perms(token_data.get('role'))
if user_perms.get('course_delete', 0):
with get_db_conn() as database:
try:
cursor = database.cursor()
cursor.execute('DELETE FROM courses WHERE id = ?', [course_id])
database.commit()
result = make_response({'ok': True}, 200)
except sqlite3.Error:
database.rollback()
result = server_error('COURSE_NOT_FOUND')
finally:
cursor.close()
return result
return abort(401)
def user_get(user_id: int):
token_data = simple_jwt.read(
request.headers.get('Authorization').split(' ')[1])
user_perms = get_role_perms(token_data.get('role'))
if user_perms.get('user_get', 0) or user_perms.get('user_get_others', 0):
if not user_perms.get('user_get_others', 0):
if user_id != token_data.get('user'):
return abort(401)
with get_db_conn(True) as database:
cursor = database.cursor()
cursor.execute(
"""SELECT users.id, users.email, users.name, users.surname, users.gender,
roles.id, roles.name, courses.id, courses.name FROM users
JOIN roles ON roles.id = users.role_id
LEFT JOIN teachers ON teachers.user_id = users.id
LEFT JOIN courses ON courses.id = teachers.course_id
WHERE users.id = ?
ORDER BY courses.name""", [user_id])
db_data = cursor.fetchall()
cursor.close()
if db_data:
user = {
'id': db_data[0][0],
'email': db_data[0][1],
'name': db_data[0][2],
'surname': db_data[0][3],
'gender': db_data[0][4],
'role': {
'id': db_data[0][5],
'name': db_data[0][6]
}
}
for key, rows in itertools.groupby(db_data, key=lambda x: x[0]):
courses = []
for r in rows:
if r[7] is not None:
courses.append({'id': r[7], 'name': r[8]})
user['courses'] = courses
return make_response({'ok': True, 'data': user}, 200)
else:
return server_error('USER_NOT_FOUND')
def booking_list():
token_data = simple_jwt.read(
request.headers.get('Authorization').split(' ')[1])
user_perms = get_role_perms(token_data.get('role'))
if user_perms.get('booking_list', 0) or user_perms.get(
'booking_list_others', 0):
user_id = token_data.get('user')
if user_perms.get('booking_list_others', 0):
req_data = request.get_json()
if req_data:
user_id = req_data.get('user_id', token_data.get('user'))
with get_db_conn(True) as database:
cursor = database.cursor()
cursor.execute(
"""SELECT bookings.id, bookings.status, bookings.day, bookings.hour, bookings.course_id,
bookings.course_name, bookings.teacher_id, bookings.teacher_name, bookings.teacher_surname
FROM bookings WHERE bookings.user_id = ?
ORDER BY bookings.day DESC, bookings.hour DESC""",
[user_id])
db_data = cursor.fetchall()
cursor.close()
db_results = []
if db_data:
for row in db_data:
course = {'id': row[4], 'name': row[5]}
teacher = {'id': row[6], 'name': row[7], 'surname': row[8]}
lesson = {
'day': row[2],
'hour': row[3],
'course': course,
'teachers': [teacher]
}
db_results.append({
'id': row[0],
'status': row[1],
'lesson': lesson
})
return make_response({'ok': True, 'data': db_results}, 200)
return abort(401)
def lesson_list_day(day: int):
with get_db_conn(True) as database:
cursor = database.cursor()
cursor.execute(
"""SELECT courses.id, courses.name, COUNT(teachers.user_id) FROM courses
LEFT JOIN teachers ON teachers.course_id = courses.id
WHERE NOT EXISTS (SELECT * FROM bookings WHERE bookings.status != 'CANCELED'
AND bookings.teacher_id = teachers.user_id AND bookings.day = ?
GROUP BY bookings.day HAVING COUNT(bookings.hour) >= 5)
GROUP BY courses.name ORDER BY courses.name""",
[day])
db_data = cursor.fetchall()
cursor.close()
db_results = []
if db_data:
for row in db_data:
course = {'id': row[0], 'name': row[1]}
db_results.append({
'day': day,
'free_teachers': row[2],
'course': course
})
return make_response({'ok': True, 'data': db_results}, 200)
def account_register():
req_data = request.get_json()
result = {'ok': True}
if req_data:
with get_db_conn() as database:
try:
cursor = database.cursor()
cursor.execute(
'INSERT INTO users (email, password) VALUES (?, ?)',
[req_data.get('email'),
req_data.get('password')])
database.commit()
except sqlite3.IntegrityError:
database.rollback()
result = server_error('LOGIN_FAILED')
except sqlite3.Error as e:
database.rollback()
result = abort(500)
print(e)
finally:
cursor.close()
return result
return abort(400)
def booking_update(booking_id: int):
token_data = simple_jwt.read(
request.headers.get('Authorization').split(' ')[1])
user_perms = get_role_perms(token_data.get('role'))
req_data = request.get_json()
if req_data:
if user_perms.get('booking_update', 0) or user_perms.get(
'booking_update_others', 0):
if user_perms.get('booking_update', 0) and not user_perms.get(
'booking_update_others', 0):
if req_data.get('status') not in ["DONE", "CANCELED"]:
return server_error('UNAUTHORIZED')
req_data.pop('id', None)
user_id = req_data.get('user_id', token_data.get('user'))
if not user_perms.get('booking_update_others', 0):
user_id = token_data.get('user')
req_data.pop('user_id', None)
req_data.pop('teacher_id', None)
req_data.pop('lesson_id', None)
sql_str = 'UPDATE bookings SET ' + (', '.join(f'{v} = ?' for v in req_data.keys())) \
+ ' WHERE id = ? AND user_id = ?'
with get_db_conn() as database:
try:
cursor = database.cursor()
cursor.execute(
sql_str,
list(req_data.values()) + [booking_id, user_id])
database.commit()
result = make_response({'ok': True}, 200)
except sqlite3.Error:
database.rollback()
result = server_error('BOOKING_NOT_FOUND')
finally:
cursor.close()
return result
return abort(401)
return abort(400)
def account_login():
req_data = request.get_json()
if req_data:
with get_db_conn(True) as database:
cursor = database.cursor()
cursor.execute(
"""SELECT id, role_id FROM users WHERE email = ? AND password = ?""",
[req_data.get('email'),
req_data.get('password')])
db_data = cursor.fetchone()
cursor.close()
if db_data:
if db_data[1] == 0 or db_data[1] == 2: # Deleted and Teachers
return server_error('LOGIN_DISABLED')
json_token = {'user': db_data[0], 'role': db_data[1]}
return make_response({
'ok': True,
'data': {
'user_id': db_data[0],
'token': simple_jwt.generate(json_token)
}
})
return server_error('LOGIN_FAILED')
return abort(400)
def lesson_list():
with get_db_conn(True) as database:
cursor = database.cursor()
cursor.execute(
"""WITH week(day) AS (SELECT (strftime('%s', 'now', 'localtime', 'start of day')
+ weeksum.to_sum) FROM weeksum) SELECT courses.id, courses.name, COUNT(teachers.user_id),
week.day FROM courses JOIN week ON true LEFT JOIN teachers ON
teachers.course_id = courses.id WHERE NOT EXISTS (SELECT * FROM bookings WHERE
bookings.status != 'CANCELED' AND bookings.teacher_id = teachers.user_id AND
bookings.day = week.day GROUP BY bookings.day HAVING COUNT(bookings.hour) >= 5)
GROUP BY courses.name, week.day ORDER BY week.day, courses.name"""
)
db_data = cursor.fetchall()
cursor.close()
db_results = []
if db_data:
for row in db_data:
course = {'id': row[0], 'name': row[1]}
db_results.append({
'day': row[3],
'free_teachers': row[2],
'course': course
})
return make_response({'ok': True, 'data': db_results}, 200)