def add_tags(event, context):
tag_type = event['tag_type']
name_param = {'name' : 'tag_type', 'value' : {'stringValue' : tag_type}}
id_sql = "SELECT tag_type_id FROM tag_type ORDER BY tag_type_id DESC LIMIT 1;"
try:
tag_type_id = query(id_sql)['records']
if len(tag_type_id) > 0:
if 'longValue' in tag_type_id[0][0]:
tag_type_id = tag_type_id[0][0]['longValue'] + 1
else:
raise LambdaException("500: tag_type_id is invalid")
else:
tag_type_id = 1
tag_type_id_param = {'name' : 'tag_type_id', 'value' : {'longValue' : tag_type_id}}
except Exception as e:
raise LambdaException("Failed to get tag_type ids: " + str(e))
add_sql = "INSERT INTO tag_type VALUES (:tag_type_id, :tag_type)"
params = [name_param, tag_type_id_param]
try:
query(add_sql, params)
return {
'statusCode': 201,
'body' : "tag_type successfully created"
}
except Exception as e:
raise LambdaException("Failed to add tag_type: " + str(e))
def handler(event, context):
get_faq_sql = "SELECT faq_id, question, answer FROM FAQ;"
try:
faqs = query(get_faq_sql)['records']
except Exception as e:
raise LambdaException("500: Unable to get FAQs, " + str(e))
response = {
'faqs': [],
}
if len(faqs) > 0:
for f_id, q, a in faqs:
curr_faqs = response['faqs']
curr_faqs.append({
'faq_id': f_id['longValue'],
'question': q['stringValue'],
'answer': a['stringValue']
})
response['faqs'] = curr_faqs
else:
raise LambdaException("204: No existing FAQs")
return response
def add_about_page(event, context):
page_text = event['page_text']
name_param = {'name': 'page_text', 'value': {'stringValue': page_text}}
id_sql = "SELECT about_page_id FROM about_page ORDER BY about_page_id DESC LIMIT 1;"
try:
about_page_id = query(id_sql)['records']
if len(about_page_id) > 0:
if 'longValue' in about_page_id[0][0]:
about_page_id = about_page_id[0][0]['longValue'] + 1
else:
raise LambdaException("500: about_page_id is invalid")
else:
about_page_id = 1
about_page_id_param = {
'name': 'about_page_id',
'value': {
'longValue': about_page_id
}
}
except Exception as e:
raise LambdaException("Failed to get about_page ids: " + str(e))
add_sql = "INSERT INTO about_page VALUES (:about_page_id, :page_text)"
params = [name_param, about_page_id_param]
try:
query(add_sql, params)
return {'body': "page_text successfully created"}
except Exception as e:
raise LambdaException("Failed to add page_text: " + str(e))
def add_colleges(event, context):
college = event['college']
name_param = {'name' : 'college', 'value' : {'stringValue' : college}}
id_sql = "SELECT college_id FROM college ORDER BY college_id DESC LIMIT 1;"
try:
college_id = query(id_sql)['records']
if len(college_id) > 0:
if 'longValue' in college_id[0][0]:
college_id = college_id[0][0]['longValue'] + 1
else:
raise LambdaException("500: college_id is invalid")
else:
college_id = 1
college_id_param = {'name' : 'college_id', 'value' : {'longValue' : college_id}}
except Exception as e:
raise LambdaException("Failed to get college ids: " + str(e))
add_sql = "INSERT INTO college VALUES (:college_id, :college)"
params = [name_param, college_id_param]
try:
query(add_sql, params)
return {
'body' : "college successfully created"
}
except Exception as e:
raise LambdaException("Failed to add college: " + str(e))
def add_mediums(event, context):
medium = event['medium']
name_param = {'name': 'medium', 'value': {'stringValue': medium}}
id_sql = "SELECT medium_id FROM medium ORDER BY medium_id DESC LIMIT 1;"
try:
medium_id = query(id_sql)['records']
if len(medium_id) > 0:
if 'longValue' in medium_id[0][0]:
medium_id = medium_id[0][0]['longValue'] + 1
else:
raise LambdaException("500: medium_id is invalid")
else:
medium_id = 1
medium_id_param = {
'name': 'medium_id',
'value': {
'longValue': medium_id
}
}
except Exception as e:
raise LambdaException("Failed to get medium ids: " + str(e))
add_sql = "INSERT INTO medium VALUES (:medium_id, :medium)"
params = [name_param, medium_id_param]
try:
query(add_sql, params)
return {'body': "medium successfully created"}
except Exception as e:
raise LambdaException("Failed to add medium: " + str(e))
def login(event, context):
print("Attempting to login:"******"UserEmail" not in event:
print("no email")
raise LambdaException("Invalid input: no email")
if 'Password' not in event:
print("no password")
raise LambdaException("Invalid input: no password")
given_email = event['UserEmail']
given_password = event['Password']
sql = "SELECT email FROM users WHERE email = '%s';" % (given_email)
existing_user = query(sql)
print("Checking if user exists...")
if(existing_user['records'] == []):
print("User DNE")
raise LambdaException("Invalid input: Invalid email, user does not exist")
print("User exists! Fetching name...")
sql = "SELECT first_name FROM users WHERE email = '%s';" % (given_email)
f_name = query(sql)
sql = "SELECT last_name FROM users WHERE email = '%s';" % (given_email)
l_name = query(sql)
#Get password from existing user and if does not match return a 400 http
print("Acquiring password...")
sql = "SELECT hashed_password FROM users WHERE email = '%s';" % (given_email)
existing_password = query(sql)['records'][0][0]['stringValue']
print("Checking password...")
if not given_password == existing_password:
raise LambdaException("Invalid input: Incorrect password")
#Get user type from Database
print("Password verified. Checking role...")
sql = "SELECT id FROM users WHERE email = '%s';" % (given_email)
user_id = query(sql)
role = 'student'
token_payload = {
'email' : given_email,
'role' : role,
'exp' : datetime.utcnow() + timedelta(seconds = JWT_EXP_DELTA_SECONDS)
}
token = jwt.encode(payload, JWT_SECRET, JWT_ALGORITHM)
print("Done!")
response_body = {
'token': token,
'email' : given_email,
'f_name':,
'l_name':,
'role':
}
def add_links(event, context):
link = event['link_type']
name_param = {'name' : 'link', 'value' : {'stringValue' : link}}
id_sql = "SELECT link_id FROM link ORDER BY link_id DESC LIMIT 1;"
try:
link_id = query(id_sql)['records']
if len(link_id) > 0:
if 'longValue' in link_id[0][0]:
link_id = link_id[0][0]['longValue'] + 1
else:
raise LambdaException("500: link_id is invalid")
else:
link_id = 1
link_id_param = {'name' : 'link_id', 'value' : {'longValue' : link_id}}
except Exception as e:
raise LambdaException("Failed to get link ids: " + str(e))
add_sql = "INSERT INTO link VALUES (:link_id, :link)"
params = [name_param, link_id_param]
try:
query(add_sql, params)
return {
'body' : "link successfully created"
}
except Exception as e:
raise LambdaException("Failed to add link: " + str(e))
def handler(event, context):
question = event['question']
question_param = {'name': 'question', 'value': {'stringValue': question}}
answer = event['answer']
answer_param = {'name': 'answer', 'value': {'stringValue': answer}}
faq_id_sql = "SELECT FAQ_id FROM FAQ ORDER BY FAQ_id DESC LIMIT 1;"
try:
faq_id = query(faq_id_sql)['records']
if len(faq_id) > 0:
if 'longValue' in faq_id[0][0]:
faq_id = faq_id[0][0]['longValue'] + 1
else:
raise LambdaException("500: FAQ_id is invalid")
else:
faq_id = 1
faq_id_param = {'name': 'faq_id', 'value': {'longValue': faq_id}}
except Exception as e:
raise LambdaException("Failed to get FAQ ids: " + str(e))
add_faq_sql = "INSERT INTO FAQ (FAQ_id, question, answer) VALUES (:faq_id, :question, :answer)"
params = [question_param, answer_param, faq_id_param]
try:
query(add_faq_sql, params)
return {'body': "FAQ successfully created"}
except Exception as e:
raise LambdaException("500: Failed to add FAQ: " + str(e))
def lambda_handler(event, context):
#supporter identifier
if 'supporter_id' in event:
supporter_id = int(event['supporter_id'])
else:
raise LambdaException("422 : Invalid input: No supporter_id")
#Check if supporter exists
sql = "SELECT supporter_id FROM supporters WHERE supporter_id= :supporter_id"
supporter_id_param = [{
'name': 'supporter_id',
'value': {
'longValue': supporter_id
}
}]
existing_supporter = query(sql, supporter_id_param)
if (existing_supporter['records'] == []):
raise LambdaException("404 : No existing Supporter")
#SQL to get all appointments
sql = """SELECT start_date, end_date, appointment_block_id, recurring_id FROM appointment_block
WHERE supporter_id = :supporter_id"""
sql_parameters = [{
'name': 'supporter_id',
'value': {
'longValue': supporter_id
}
}]
appointment_info = query(sql, sql_parameters)
# check if supporter types successfully loaded
if (appointment_info['records'] == []):
raise LambdaException(
"404 : There are no appointment blocks for supporter")
else:
appointment_data = []
#for each entry in the query data, extracts relevant data and stores it in a dictionary with appropriate key
for entry in appointment_info['records']:
block = dict()
block["start_date"] = entry[0].get("stringValue")
block["end_date"] = entry[1].get("stringValue")
block["appointment_block_id"] = entry[2].get("longValue")
block["recurring_id"] = entry[3].get("longValue")
appointment_data.append(block)
#Returns query contents in json format, success
return {'body': appointment_data, 'statusCode': 200}
def query(sql_query, sql_parameters=[]):
try:
client = boto3.client("rds-data")
except:
raise LambdaException("Cannot connect to Database")
try:
result = client.execute_statement(secretArn=secretArn,
database=dbName,
resourceArn=arn,
sql=sql_query,
parameters=sql_parameters)
except Exception as e:
raise LambdaException("Invalid query: " + str(e))
return result
def update_specialization_types(event, context):
specialization_type_id = int(event['specialization_type_id'])
id_param = {
'name': 'specialization_type_id',
'value': {
'longValue': specialization_type_id
}
}
specialization_type = event['specialization_type']
specialization_type_param = {
'name': 'specialization_type',
'value': {
'stringValue': specialization_type
}
}
update_sql = "UPDATE specialization_type SET specialization_type = :specialization_type WHERE specialization_type_id = :specialization_type_id"
params = [id_param, specialization_type_param]
try:
query(update_sql, params)
except Exception as e:
raise LambdaException("500: Failed to update specialization_type, " +
str(e))
return {'body': "Successfully updated specialization_type"}
def update_announcements(event, context):
accouncement_id = int(event['announcement_id'])
id_param = {
'name': 'accouncement_id',
'value': {
'longValue': accouncement_id
}
}
title = event['title']
title_param = {'name': 'title', 'value': {'stringValue': title}}
announcement = event['announcement']
announcement_param = {
'name': 'announcement',
'value': {
'stringValue': announcement
}
}
update_sql = "UPDATE announcements SET announcement = :announcement, title = :title WHERE accouncement_id = :accouncement_id"
params = [id_param, announcement_param, title_param]
try:
query(update_sql, params)
except Exception as e:
raise LambdaException("500: Failed to update announcement, " + str(e))
return {'body': "Successfully updated announcement"}
def get_unverified_supporters(event, context):
#Query for getting desired informaton
sql_select = """SELECT s.employer as s_employer, s.title as s_title, s.team_name as s_team_name, u.first_name as s_first_name, u.last_name as s_last_name, u.email as s_email, s.feedback as s_feedback, s.rating as s_rating, s.is_pending as s_is_pending, s.office as s_office, st.professional_staff as s_professional_staff, st.alumni as s_alumni, st.faculty as s_faculty, st.other as s_other, s.supporter_id as s_supporter_id, st.student_staff as s_student_staff\
FROM supporters s, users u, supporter_type st \
WHERE (s.is_pending = true or s.is_pending is NULL) AND s.user_id = u.id AND s.supporter_id = st.supporter_id;"""
sql_parameters = dictionary_to_list(
{}) #Need to add dummy sql_parameters for the query method to work
response = query(sql_select, sql_parameters)
if response['records'] == []:
raise LambdaException(
"404: there are no supporters"
) #Raising lambda exception if there are no supporters
else:
unverified_supporter_information = []
for entry in response[
'records']: #This for loop will append corrresponding data for JSON object
block = dict()
block["s_employer"] = entry[0].get("stringValue") #Employer
block["s_title"] = entry[1].get("stringValue") #Title of supporter
block["s_team_name"] = entry[2].get(
"stringValue") #Team name of supporter
block["s_first_name"] = entry[3].get(
"stringValue") #First name of supporter
block["s_last_name"] = entry[4].get(
"stringValue") #Last name of supporter
block["s_email"] = entry[5].get("stringValue") #Email of supporter
block["s_feedback"] = entry[6].get(
"booleanValue") #Feedback for supporter
block["s_rating"] = entry[7].get(
"doubleValue") #Rating for supporter
block["s_is_pending"] = entry[8].get(
"booleanValue"
) #Boolean for whether or not supporter is verified
block["s_office"] = entry[9].get(
"stringValue") #Office name for supporter
block["s_professional_staff"] = entry[10].get(
"booleanValue"
) #Boolean for whether or not supporter is professional staff
block["s_alumni"] = entry[11].get(
"booleanValue"
) #Boolean for whether or not supporter is alumni
block["s_faculty"] = entry[12].get(
"booleanValue"
) #Boolean for whether or not supporter is faculty
block["s_other"] = entry[13].get(
"booleanValue") #Boolean for other for supporter
block["s_supporter_id"] = entry[14].get(
"longValue") #Supporter id for the supporter
block["s_student_staff"] = entry[15].get(
"booleanValue"
) #Boolean for whether or not supporters is student staff
unverified_supporter_information.append(block)
#Returns the query contents in JSON format
return {'body': unverified_supporter_information, 'statusCode': 200}
def delete_tags(event, context):
tag_type_id = int(event['tag_type_id'])
id_param = {'name': 'tag_type_id', 'value': {'longValue': tag_type_id}}
delete_sql = "DELETE FROM tag_type WHERE tag_type_id = :tag_type_id"
params = [id_param]
try:
query(delete_sql, params)
except Exception as e:
raise LambdaException("500: Failed to delete tag_type, " + str(e))
return {'statusCode': 201, 'body': "Successfully deleted tag_type"}
def delete_majors(event, context):
major_id = int(event['major_id'])
id_param = {'name': 'major_id', 'value': {'longValue': major_id}}
delete_sql = "DELETE FROM major WHERE major_id = :major_id"
params = [id_param]
try:
query(delete_sql, params)
except Exception as e:
raise LambdaException("500: Failed to delete major, " + str(e))
return {'body': "Successfully deleted major"}
def delete_about_page(event, context):
about_page_id = int(event['about_page_id'])
id_param = {'name': 'about_page_id', 'value': {'longValue': about_page_id}}
delete_sql = "DELETE FROM about_page WHERE about_page_id = :about_page_id"
params = [id_param]
try:
query(delete_sql, params)
except Exception as e:
raise LambdaException("500: Failed to delete about_page, " + str(e))
return {'body': "Successfully deleted about_page"}
def delete_links(event, context):
link_id = int(event['link_id'])
id_param = {'name': 'link_id', 'value': {'longValue': link_id}}
delete_sql = "DELETE FROM link WHERE link_id = :link_id"
params = [id_param]
try:
query(delete_sql, params)
except Exception as e:
raise LambdaException("500: Failed to delete link, " + str(e))
return {'body': "Successfully deleted link"}
def handler(event, context):
params = []
if event['supporter_id'] != "":
supporter_id = int(event['supporter_id'])
supporter_id_param = {'name' : 'supporter_id', 'value' : {'longValue' : supporter_id}}
params.append(supporter_id_param)
else:
raise LambdaException("400: Request missing supporter id")
#Confirm that supporter exists
supporter_sql = "SELECT user_id FROM supporters WHERE supporter_id = :supporter_id"
try:
existing_supporter = query(supporter_sql, params)['records']
except Exception as e:
raise LambdaException("500: Unable to confirm that supporter exists, " + str(e))
if existing_supporter = []:
raise LambdaException("404: Supporter not found")
def add_announcements(event, context):
title = event['title']
title_param = {'name': 'title', 'value': {'stringValue': title}}
announcement = event['announcement']
announcement_param = {
'name': 'announcement',
'value': {
'stringValue': announcement
}
}
announcement_id_sql = "SELECT accouncement_id FROM announcements ORDER BY accouncement_id DESC LIMIT 1;"
try:
accouncement_id = query(announcement_id_sql)['records']
if len(accouncement_id) > 0:
if 'longValue' in accouncement_id[0][0]:
accouncement_id = accouncement_id[0][0]['longValue'] + 1
else:
raise LambdaException("500: accouncement_id is invalid")
else:
accouncement_id = 1
announcement_id_param = {
'name': 'accouncement_id',
'value': {
'longValue': accouncement_id
}
}
except Exception as e:
raise LambdaException("Failed to get announcement ids: " + str(e))
add_sql = "INSERT INTO announcements VALUES (:accouncement_id, :title, :announcement)"
params = [title_param, announcement_param, announcement_id_param]
try:
query(add_sql, params)
return {'body': "announcement successfully created"}
except Exception as e:
raise LambdaException("500: Failed to add announcement: " + str(e))
def verify_admin(event, context):
user_id = int(event['user_id'])
user_id_dic = {}
if user_id == None: #Making sure user_id was passed
raise LambdaException("400: user_id was not given")
user_id_dic['user_id'] = user_id
sql_parameters = dictionary_to_list(user_id_dic)
sql_select = """SELECT users.id FROM users WHERE users.id = :user_id""" #This query is ensuring that the user exists
response = query(sql_select, sql_parameters)
if response['records'] == []: #Returning error if user does not exist
raise LambdaException("404: user does not exist")
sql_select = """SELECT users.id FROM users WHERE users.id = :user_id and is_admin = true""" #This query is ensuring user is not already an admin
response = query(sql_select, sql_parameters)
if response['records'] != []: #Returning error if user is already an admin
raise LambdaException("405: user is already an admin")
else:
sql_update = """UPDATE users SET is_admin = true WHERE users.id = :user_id"""
response = query(sql_update, sql_parameters)
sql_insert = """INSERT INTO admins(admin_id, user_id, is_pending) VALUES(:user_id, :user_id, false)
"""
response = query(sql_insert, sql_parameters)
# send approval email
lambda_client = boto3_client('lambda')
email_event = {"user_id": user_id, "approved_role": "admin"}
try:
response = lambda_client.invoke(FunctionName="approval_email",
InvocationType='Event',
Payload=json.dumps(email_event))
except Exception as e:
raise LambdaException("404: Unable to send approval email " +
str(e))
return {"statusCode": 200}
def add_specialization_types(event, context):
specialization_type = event['specialization_type']
name_param = {
'name': 'specialization_type',
'value': {
'stringValue': specialization_type
}
}
id_sql = "SELECT specialization_type_id FROM specialization_type ORDER BY specialization_type_id DESC LIMIT 1;"
try:
specialization_type_id = query(id_sql)['records']
if len(specialization_type_id) > 0:
if 'longValue' in specialization_type_id[0][0]:
specialization_type_id = specialization_type_id[0][0][
'longValue'] + 1
else:
raise LambdaException("500: specialization_type_id is invalid")
else:
specialization_type_id = 1
specialization_type_id_param = {
'name': 'specialization_type_id',
'value': {
'longValue': specialization_type_id
}
}
except Exception as e:
raise LambdaException("Failed to get specialization_type ids: " +
str(e))
add_sql = "INSERT INTO specialization_type VALUES (:specialization_type_id, :specialization_type)"
params = [name_param, specialization_type_id_param]
try:
query(add_sql, params)
return {'body': "specialization_type successfully created"}
except Exception as e:
raise LambdaException("Failed to add specialization_type: " + str(e))
def get_picture(event, context):
id = int(event['id'])
id_param = {'name': 'id', 'value': {'longValue': id}}
sql = "SELECT picture FROM users WHERE id = :id"
params = [id_param]
try:
picture = query(sql, params)["records"]
except Exception as e:
raise LambdaException("500: Failed to get picture, " + str(e))
response = {}
if picture == []:
raise LambdaException("404: Picture does not exist, ")
elif "isNull" in picture[0][0]:
response["picture"] = ""
else:
response["picture"] = picture[0][0]["stringValue"]
return response
def delete_specialization_types(event, context):
specialization_type_id = int(event['specialization_type_id'])
id_param = {
'name': 'specialization_type_id',
'value': {
'longValue': specialization_type_id
}
}
delete_sql = "DELETE FROM specialization_type WHERE specialization_type_id = :specialization_type_id"
params = [id_param]
try:
query(delete_sql, params)
except Exception as e:
raise LambdaException("500: Failed to delete specialization_type, " +
str(e))
return {'body': "Successfully deleted specialization_type"}
def login_handler(event, context):
try:
response_body = login(event, context)
return response_body
except Exception as e:
exception_type = e.__class__.__name__
exception_message = str(e)
api_exception = {
"isError" : True,
"type" : exception_type,
"message" : exception_message
}
json_exception = json.dumps(api_exception)
raise LambdaException(json_exception)
def update_minors(event, context):
minor_id = int(event['minor_id'])
id_param = {'name': 'minor_id', 'value': {'longValue': minor_id}}
minor = event['minor']
minor_param = {'name': 'minor', 'value': {'stringValue': minor}}
update_sql = "UPDATE minor SET minor = :minor WHERE minor_id = :minor_id"
params = [id_param, minor_param]
try:
query(update_sql, params)
except Exception as e:
raise LambdaException("500: Failed to update minor, " + str(e))
return {'body': "Successfully updated minor"}
def update_links(event, context):
link_id = int(event['link_id'])
id_param = {'name': 'link_id', 'value': {'longValue': link_id}}
link_type = event['link_type']
link_param = {'name': 'link_type', 'value': {'stringValue': link_type}}
update_sql = "UPDATE link SET link_type = :link_type WHERE link_id = :link_id"
params = [id_param, link_param]
try:
query(update_sql, params)
except Exception as e:
raise LambdaException("500: Failed to update link, " + str(e))
return {'body': "Successfully updated link"}
def update_tags(event, context):
tag_type_id = int(event['tag_type_id'])
id_param = {'name': 'tag_type_id', 'value': {'longValue': tag_type_id}}
tag_type = event['tag_type']
tag_type_param = {'name': 'tag_type', 'value': {'stringValue': tag_type}}
update_sql = "UPDATE tag_type SET tag_type = :tag_type WHERE tag_type_id = :tag_type_id"
params = [id_param, tag_type_param]
try:
query(update_sql, params)
except Exception as e:
raise LambdaException("500: Failed to update tag, " + str(e))
return {'statusCode': 201, 'body': "Successfully updated tag"}
def update_mediums(event, context):
medium_id = int(event['medium_id'])
id_param = {'name' : 'medium_id', 'value' : {'longValue' : medium_id}}
medium = event['medium']
medium_param = {'name' : 'medium', 'value' : {'stringValue' : medium}}
update_sql = "UPDATE medium SET medium = :medium WHERE medium_id = :medium_id"
params = [id_param, medium_param]
try:
query(update_sql, params)
except Exception as e:
raise LambdaException("500: Failed to update medium, " + str(e))
return {
'body' : "Successfully updated medium"
}
def update_about_page(event, context):
about_page_id = int(event['about_page_id'])
id_param = {'name' : 'about_page_id', 'value' : {'longValue' : about_page_id}}
page_text = event['page_text']
page_text_param = {'name' : 'page_text', 'value' : {'stringValue' : page_text}}
update_sql = "UPDATE about_page SET page_text = :page_text WHERE about_page_id = :about_page_id"
params = [id_param, page_text_param]
try:
query(update_sql, params)
except Exception as e:
raise LambdaException("500: Failed to update about_page, " + str(e))
return {
'body' : "Successfully updated about_page"
}
def update_colleges(event, context):
college_id = int(event['college_id'])
id_param = {'name' : 'college_id', 'value' : {'longValue' : college_id}}
college = event['college']
college_param = {'name' : 'college', 'value' : {'stringValue' : college}}
update_sql = "UPDATE college SET college = :college WHERE college_id = :college_id"
params = [id_param, college_param]
try:
query(update_sql, params)
except Exception as e:
raise LambdaException("500: Failed to update college, " + str(e))
return {
'body' : "Successfully updated college"
}