def mark_as_done(e_id, done):
query = """UPDATE emergency SET
done = %(done)s
where id = %(id)s;"""
data = {'done': done, 'id': e_id}
MySql.get_db_conn().insert(query, data)
def update_explanation(e_id, explanation):
query = """UPDATE emergency SET
explanation = %(explanation)s
where id = %(id)s;"""
data = {'explanation': explanation, 'id': e_id}
MySql.get_db_conn().insert(query, data)
def publish_public_key(auth_token, public_key_pem):
query = """UPDATE user SET
public_key_pem = %(public_key_pem)s
where auth_token = %(auth_token)s;"""
data = {'public_key_pem': public_key_pem, 'auth_token': auth_token}
MySql.get_db_conn().insert(query, data)
def update_key_s3(i_id, key_s3):
query = """UPDATE image SET
key_s3 = %(key_s3)s
where id = %(id)s;"""
data = {'key_s3': key_s3, 'id': i_id}
MySql.get_db_conn().insert(query, data)
def update_callme(e_id, callme):
query = """UPDATE emergency SET
callme = %(callme)s
where id = %(id)s;"""
data = {'callme': callme, 'id': e_id}
MySql.get_db_conn().insert(query, data)
def publish_public_key(a_id, public_key_pem):
query = """UPDATE admin SET
public_key_pem = %(public_key_pem)s
where id = %(id)s;"""
data = {
'public_key_pem': public_key_pem,
'id': a_id
}
MySql.get_db_conn().insert(query, data)
def update_status(e_id, handled_status, timestamp):
query = """UPDATE emergency SET
handled_status = %(handled_status)s,
handled_time = %(handled_time)s
where id = %(id)s;"""
data = {
'handled_status': handled_status,
'handled_time': timestamp,
'id': e_id
}
MySql.get_db_conn().insert(query, data)
def archive_report(r_id, archived, archived_time):
query = """UPDATE report SET
archived = %(archived)s,
archived_time = %(archived_time)s
where id = %(id)s;"""
data = {
'archived': archived,
'archived_time': archived_time,
'id': r_id
}
MySql.get_db_conn().insert(query, data)
def update_location(e_id, longitude, latitude, timestamp):
query = """UPDATE emergency SET
longitude = %(longitude)s,
latitude = %(latitude)s,
location_last_updated = %(location_last_updated)s
where id = %(id)s;"""
data = {
'longitude': longitude,
'latitude': latitude,
'location_last_updated': timestamp,
'id': e_id
}
MySql.get_db_conn().insert(query, data)
def record_message(
report_id,
content,
from_admin,
timestamp
):
query = """INSERT INTO message_user(
report_id,
content,
from_admin,
timestamp
) VALUES (
%(report_id)s,
%(content)s,
%(from_admin)s,
%(timestamp)s
);"""
data = {
'report_id': report_id,
'content': content,
'from_admin': from_admin,
'timestamp': timestamp
}
return MySql.get_db_conn().insert(query, data)
def get_non_archived_reports():
query = """SELECT * FROM report where archived = %(archived)s
ORDER BY created DESC;"""
data = {
'archived': False
}
return MySql.get_db_conn().get_all(query, data)
def get_messages(report_id):
query = """SELECT * FROM message_user where
report_id = %(report_id)s
ORDER BY timestamp ASC;"""
data = {
'report_id': report_id
}
return MySql.get_db_conn().get_all(query, data)
def add_reporter(r_id, name, dorm, email, phone, id_num):
query = """UPDATE report SET
reporer_name = %(reporer_name)s,
reporter_dorm = %(reporter_dorm)s,
reporter_email = %(reporter_email)s,
reporter_phone = %(reporter_phone)s,
reporter_id_num = %(reporter_id_num)s
where id_dummy = %(id_dummy)s;"""
data = {
'reporer_name': name,
'reporter_dorm': dorm,
'reporter_email': email,
'reporter_phone': phone,
'reporter_id_num': id_num,
'id_dummy': r_id
}
MySql.get_db_conn().insert(query, data)
def get_non_archived_records():
# query1 = "SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;"
# MySql.get_db_conn().query(query1)
query = """SELECT * FROM emergency where archived = %(archived)s
ORDER BY created DESC;"""
data = {'archived': False}
records = MySql.get_db_conn().get_all(query, data)
# query2 = "SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ ;"
# MySql.get_db_conn().query(query2)
return records
def register_user(user_id, auth_token):
query = """INSERT INTO user(
user_id,
auth_token
) VALUES (
%(user_id)s,
%(auth_token)s
);"""
data = {'user_id': user_id, 'auth_token': auth_token}
return MySql.get_db_conn().insert(query, data)
def record_emergency(created, name, phone, id_num, email, dorm, latitude,
longitude, handled_status, explanation, archived,
done):
query = """INSERT INTO emergency(
created,
name,
phone,
id_num,
email,
dorm,
longitude,
latitude,
location_last_updated,
handled_status,
explanation,
archived,
done
) VALUES (
%(created)s,
%(name)s,
%(phone)s,
%(id_num)s,
%(email)s,
%(dorm)s,
%(longitude)s,
%(latitude)s,
%(location_last_updated)s,
%(handled_status)s,
%(explanation)s,
%(archived)s,
%(done)s
);"""
data = {
'created': created,
'name': name,
'phone': phone,
'id_num': id_num,
'email': email,
'dorm': dorm,
'longitude': longitude,
'latitude': latitude,
'location_last_updated': created,
'handled_status': handled_status,
'explanation': explanation,
'archived': archived,
'done': done
}
return MySql.get_db_conn().insert(query, data)
def record_image(report_id, content, iv, aes_key):
query = """INSERT INTO image(
report_id,
content,
iv,
aes_key
) VALUES (
%(report_id)s,
%(content)s,
%(iv)s,
%(aes_key)s
);"""
data = {
'report_id': report_id,
'content': content,
'iv': iv,
'aes_key': aes_key
}
return MySql.get_db_conn().insert(query, data)
def create_admin(
email,
password,
public_key_pem
):
query = """INSERT INTO admin(
email,
password,
public_key_pem
) VALUES (
%(email)s,
%(password)s,
%(public_key_pem)s
);"""
data = {
'email': email,
'password': password,
'public_key_pem': public_key_pem
}
return MySql.get_db_conn().insert(query, data)
def get_user(auth_token):
query = """SELECT * FROM user where auth_token = %(auth_token)s"""
data = {'auth_token': auth_token}
return MySql.get_db_conn().get(query, data)
def record_report(
report_id,
user_pub_key,
created,
rtype,
urgency,
date,
location,
description,
is_anonymous,
is_res_emp,
follow_up,
archived,
followup_initiated,
image_sym_key
):
query = """INSERT INTO report(
id,
user_pub_key,
type,
created,
urgency,
date,
location,
description,
is_anonymous,
is_res_emp,
follow_up,
archived,
followup_initiated,
image_sym_key
) VALUES (
%(id)s,
%(user_pub_key)s,
%(type)s,
%(created)s,
%(urgency)s,
%(date)s,
%(location)s,
%(description)s,
%(is_anonymous)s,
%(is_res_emp)s,
%(follow_up)s,
%(archived)s,
%(followup_initiated)s,
%(image_sym_key)s
);"""
data = {
'id': report_id,
'user_pub_key': user_pub_key,
'type': rtype,
'created': created,
'urgency': urgency,
'date': date,
'location': location,
'description': description,
'is_anonymous': is_anonymous,
'is_res_emp': is_res_emp,
'follow_up': follow_up,
'archived': archived,
'followup_initiated': followup_initiated,
'image_sym_key': image_sym_key
}
return MySql.get_db_conn().insert(query, data)
def get_admin_by_email(email):
query = """SELECT * FROM admin where email = %(email)s"""
data = {
'email': email
}
return MySql.get_db_conn().get(query, data)
def get_report(r_id):
query = """SELECT * FROM report where id = %(id)s"""
data = {
'id': r_id
}
return MySql.get_db_conn().get(query, data)
def get_all_reports():
query = """SELECT * FROM report ORDER BY created DESC;"""
return MySql.get_db_conn().get_all(query)
def get_images(report_id):
query = """SELECT * FROM image where
report_id = %(report_id)s"""
data = {'report_id': report_id}
return MySql.get_db_conn().get_all(query, data)
def get_all_records():
query = """SELECT * FROM emergency ORDER BY created DESC;"""
return MySql.get_db_conn().get_all(query)
def get_status(e_id):
query = """SELECT handled_status, handled_time
FROM emergency where id = %(id)s"""
data = {'id': e_id}
return MySql.get_db_conn().get(query, data)
def get_public_key(admin_email):
query = """SELECT public_key_pem FROM admin where email = %(email)s"""
data = {
'email': admin_email
}
return MySql.get_db_conn().get(query, data)
def get_admins():
query = """SELECT * FROM admin"""
return MySql.get_db_conn().get_all(query)
def get_public_key(u_id):
query = """SELECT public_key_pem FROM user where id = %(id)s"""
data = {'id': u_id}
return MySql.get_db_conn().get(query, data)
def get_emergency(e_id):
query = """SELECT * FROM emergency where id = %(id)s"""
data = {'id': e_id}
return MySql.get_db_conn().get(query, data)