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)