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()
