def update_event(): conn = create_connection() try: alans_procedures._s26_update_event_description(conn) data = request.get_json() with conn.cursor() as cursor: sql = 'call get_assigned_staff_for_event(%s, %s, %s)' args = [ data.get('event_name'), session.get('site_name'), data.get('start_date') ] cursor.execute(sql, args) old_staffs = set(row['username'] for row in cursor.fetchall()) staffs = set([el['username'] for el in data.get('staff')]) to_add = [(staff, *args) for staff in staffs - old_staffs] to_delete = [(staff, *args) for staff in old_staffs - staffs] alans_procedures._s26_remove_assignments(conn, to_delete) alans_procedures._s26_add_assignments(conn, to_add) return ("success") except MySQLError as e: print(e) return jsonify(str(e)), 400 finally: conn.close()
def update_transit(): conn = create_connection() try: data = request.get_json() alans_procedures._s23_update_transit_info(conn) with conn.cursor() as cursor: sql = f'call show_current_connected_sites(%s, %s)' args = [data.get('type'), data.get('route')] cursor.execute(sql, args) old_sites = set(row.get('site_name') for row in cursor.fetchall()) sites = set(data.get('sites')) to_add = [(data.get('type'), data.get('route'), site) for site in sites - old_sites] to_delete = [(data.get('type'), data.get('route'), site) for site in old_sites - sites] alans_procedures._s23_remove_connected_sites(conn, to_delete) alans_procedures._s23_add_connected_sites(conn, to_add) return jsonify("success") except MySQLError as e: print(e) return jsonify(str(e)), 400 finally: conn.close()
def login(): conn = create_connection() try: with conn.cursor() as cursor: sql = "call user_login(%s, %s)" data = request.get_json() credentials = [data.get('email', ''), data.get('password', '')] cursor.execute(sql, credentials) result = cursor.fetchone() if result is None: return jsonify("no such username and password"), 400 if result['status'] != 'Approved': return jsonify(f"Account is {result['status']}"), 400 session['username'] = result['username'] session['status'] = result['status'] session['logged_in'] = True classification = _classify_user(cursor) if session.get('type') == 'manager': site_object = alans_procedures._s25_get_site_for_manager(conn) if site_object is None: session['site_name'] = None else: session['site_name'] = site_object.get('site_name') return jsonify({ 'username': session['username'], **classification}), 200 finally: conn.close()
def filter_site_report(): conn = create_connection() try: return jsonify(alans_procedures._s29_filter_daily_details(conn)), 200 except MySQLError as e: print(e) return jsonify(str(e)), 400 finally: conn.close()
def daily_event_info(): conn = create_connection() try: return jsonify(alans_procedures._s26_get_daily_details(conn)), 200 except MySQLError as e: print(e) return jsonify(str(e)), 400 finally: conn.close()
def filter_explore_event(): conn = create_connection() try: return jsonify(alans_procedures._s33_filter_events(conn)), 200 except MySQLError as e: print(e) return jsonify(str(e)), 400 finally: conn.close()
def get_transit_details(): conn = create_connection() try: return jsonify(alans_procedures._s36_populate_table_with_filter(conn)), 200 except MySQLError as e: print(e) return jsonify(str(e)), 400 finally: conn.close()
def event_detail(): conn = create_connection() try: return jsonify(bryans_procedures._s34_visitor_view_event(conn)), 200 except MySQLError as e: print(e) return jsonify(str(e)), 400 finally: conn.close()
def daily_site_report(): conn = create_connection() try: return jsonify(alans_procedures._s30_populate_table(conn)), 200 except MySQLError as e: print(e) return jsonify(str(e)), 400 finally: conn.close()
def get_site_name(): conn = create_connection() try: return jsonify(alans_procedures._s25_get_site_for_manager(conn)) except MySQLError as e: print(e) return jsonify(str(e)), 400 finally: conn.close()
def filter_schedule(): conn = create_connection() try: return jsonify(alans_procedures._s31_get_staff_counts(conn)), 200 except MySQLError as e: print(e) return jsonify(str(e)), 400 finally: conn.close()
def create_site(): conn = create_connection() try: return jsonify(alans_procedures._s21_create_site(conn)), 200 except MySQLError as e: print(e) return jsonify(str(e)), 400 finally: conn.close()
def filter_manage_transit(): conn = create_connection() try: return jsonify(alans_procedures._s22_get_info_for_table(conn)), 200 except MySQLError as e: print(e) return jsonify(str(e)), 400 finally: conn.close()
def update_user_status(): conn = create_connection() try: return jsonify(alans_procedures._s18_update_user_status(conn)), 200 except MySQLError as e: print(e) return jsonify(str(e)), 400 finally: conn.close()
def schedule_details(): conn = create_connection() try: return jsonify(alans_procedures._s32_view_staff_detail(conn)), 200 except MySQLError as e: print(e) return jsonify(str(e)), 400 finally: conn.close()
def get_available_staff(): conn = create_connection() try: return jsonify(alans_procedures._s27_get_available_staff(conn)), 200 except MySQLError as e: print(e) return jsonify(str(e)), 400 finally: conn.close()
def get_managers(): conn = create_connection() try: return jsonify(alans_procedures._s19_populate_managers(conn)), 200 except MySQLError as e: print(e) return jsonify(str(e)), 400 finally: conn.close()
def filter_manage_site(): conn = create_connection() try: return jsonify( alans_procedures._s19_get_manager_names_for_sites(conn)), 200 except MySQLError as e: print(e) return jsonify(str(e)), 400 finally: conn.close()
def create_transit(): conn = create_connection() try: alans_procedures._s24_create_transit(conn) return jsonify("success") except MySQLError as e: print(e) return jsonify(str(e)), 400 finally: conn.close()
def visit_history(): conn = create_connection() try: return jsonify(bryans_procedures._s38_filter_visits(conn)), 200 except MySQLError as e: print(e) return jsonify(str(e)), 400 finally: conn.close()
def log_site_visit(): conn = create_connection() try: bryans_procedures._s37_log_visit_site(conn) return jsonify("success"), 200 except MySQLError as e: print(e) return jsonify(str(e)), 400 finally: conn.close()
def get_unassigned_managers(): conn = create_connection() try: return jsonify( alans_procedures._s20_get_unassigned_manager_names(conn)), 200 except MySQLError as e: print(e) return jsonify(str(e)), 400 finally: conn.close()
def filter_manage_event(): conn = create_connection() try: return jsonify(alans_procedures._s25_filter_event_get_attributes(conn)) except MySQLError as e: print(e) return jsonify(str(e)), 400 finally: conn.close()
def delete_site(): conn = create_connection() try: alans_procedures._s19_delete_site(conn) return jsonify('success') except MySQLError as e: print(e) return jsonify(str(e)), 400 finally: conn.close()
def get_event_info(): conn = create_connection() try: event = alans_procedures._s26_get_event_information(conn) event['staff'] = alans_procedures._s26_get_assigned_staff(conn) return jsonify(event), 200 except MySQLError as e: print(e) return jsonify(str(e)), 400 finally: conn.close()
def get_all_sites(): if not session.get('logged_in'): return jsonify("not logged in"), 401 conn = create_connection() try: with conn.cursor() as cursor: sql = "call get_site_names();" cursor.execute(sql) result = cursor.fetchall() return jsonify([row['site_name'] for row in result]), 200 finally: conn.close()
def get_all_transport_types(): if not session.get('logged_in'): return jsonify("not logged in"), 401 conn = create_connection() try: with conn.cursor() as cursor: sql = "select distinct type from transit;" cursor.execute(sql) result = cursor.fetchall() return jsonify([row['type'] for row in result]), 200 finally: conn.close()
def for_testing(): conn = create_connection() try: result = _s25_filter_events(conn) return jsonify(result), 200 except Exception as err: return jsonify(err.__str__()), 400 finally: conn.close() return jsonify('f****d up'), 400
def update_employee_profile(): if not session.get('logged_in'): return jsonify("not logged in"), 401 if not session.get('is_employee'): return jsonify( f"api not accessible for {session.get('is_employee')}"), 401 conn = create_connection() try: with conn.cursor() as cursor: data = request.get_json() sql = 'call update_profile_employee(%s,%s,%s,%s)' args = [ session.get('username'), data.get('first_name'), data.get('last_name'), data.get('phone') ] cursor.execute(sql, args) conn.commit() with conn.cursor() as cursor: old_sql = f"select email from user_email where username = '******'username')}';" cursor.execute(old_sql) old_emails = set(row.get('email') for row in cursor.fetchall()) emails = set(data.get('emails')) to_add = [(session.get('username'), email) for email in emails - old_emails] to_delete = [email for email in old_emails - emails] _add_emails(conn, emails=to_add) _delete_emails(conn, to_delete) if session['is_visitor'] != data.get('is_visitor'): session['is_visitor'] = data.get('is_visitor') _add_visitor(conn) if data.get('is_visitor') else _delete_visitor( conn) finally: conn.close() return jsonify('success'), 200
def filter_transits(): if not session.get('logged_in'): return jsonify("not logged in"), 401 conn = create_connection() try: with conn.cursor() as cursor: sql = f'call take_transit_filter(%s, %s, %s, %s)' args = [ request.args.get('type', None), request.args.get('site', None), request.args.get('low_price', None), request.args.get('high_price', None) ] cursor.execute(sql, args) result = cursor.fetchall() for row in result: row['price'] = float(row['price']) return jsonify(result), 200 finally: conn.close()