def put_tag(tag_name):
client = boto3.client('rds-data')
# query database to get next id number
sql = "SELECT MAX(tag_id) FROM tags;"
query_result = execute_statement(client, sql)
max_id = extract_records(query_result)
tag_id = 0 if len(max_id) == 0 else max_id[0][0] + 1
# insert the new tag into the database
sql = "INSERT INTO tags VALUES (:tag_id, :tag_name);"
sql_parameters = [{
'name': 'tag_id',
'value': {
'longValue': tag_id
}
}, {
'name': 'tag_name',
'value': {
'stringValue': f'{tag_name}'
}
}]
query_result = execute_statement(client, sql, sql_parameters)
print(query_result)
return tag_id
def put_option(table_name, option_name):
# get necessary field names
if (table_name == 'tags'):
id = 'tag_id'
name = 'tag_name'
elif (table_name == 'type_of_supporter'):
id = 'supp_type_id'
name = 'supp_type'
else:
id = 'type_id'
name = 'appointment_name'
# insert the new tag into the database
sql = f"INSERT INTO {table_name} ({name}) VALUES (:option_name);"
sql_parameters = [{
'name': 'option_name',
'value': {
'stringValue': f'{option_name}'
}
}]
query_result = execute_statement(sql, sql_parameters)
print(query_result)
# get id to return
sql = f"SELECT {id} FROM {table_name} WHERE {name} = :option_name;"
id_result = execute_statement(sql, sql_parameters)
print(id_result)
# return list(id_result['records'][0][0].values())[0]
return id_result['records'][0][0]['longValue']
def getAnalytics(event, context):
response_headers = {}
response_headers["X-Requested-With"] = "*"
response_headers["Access-Control-Allow-Origin"] = "*"
response_headers[
"Access-Control-Allow-Headers"] = "Content-Type,X-Amz-Date,Authorization,X-Api-Key,x-requested-with'"
response_headers[
"Access-Control-Allow-Methods"] = "OPTIONS,POST,GET,PUT,DELETE"
# query db for the types of appts, their frequencies and the appt names
query = "SELECT AT.type_id, COUNT(A.type_id), AT.appointment_name \
FROM appointments AS A RIGHT JOIN appointment_type AS AT \
ON A.type_id = AT.type_id \
WHERE active_type = true \
GROUP BY AT.type_id;"
appts_result = execute_statement(query)
# query db for the tags and their frequencies and the tag names
query = "SELECT T.tag_id, COUNT(ST.tag_id), T.tag_name \
FROM supporter_tags AS ST RIGHT JOIN tags T \
ON ST.tag_id = T.tag_id \
GROUP BY T.tag_id;"
tags_result = execute_statement(query)
appts_csv = "No appointment data"
# there's appt data
if appts_result['records'] != []:
# build the appt csv
appts_csv = [["Appointment type", "Frequency"]]
for tuple in appts_result['records']:
appt_type = tuple[2].get("stringValue")
frequency = tuple[1].get("longValue")
appts_csv.append([appt_type, frequency])
tags_csv = "No tag data"
# there's tag data
if tags_result['records'] != []:
# build the tag csv
tags_csv = [["Tag name", "Frequency"]]
for tuple in tags_result['records']:
tag_name = tuple[2].get("stringValue")
frequency = tuple[1].get("longValue")
tags_csv.append([tag_name, frequency])
body = {}
body["csv for appointments"] = appts_csv
body["csv for tags"] = tags_csv
return {
'statusCode': 200,
'body': json.dumps(body),
'headers': response_headers
}
def report(event, context):
supporter_id = event['body']['supporter_id']
# Connect to database
client = boto3.client('rds-data')
sql = 'SELECT supporter_id FROM `supporters` WHERE `supporter_id` = :supporter_id;'
sql_parameters = [{
'name': 'supporter_id',
'value': {
'longValue': supporter_id
}
}]
user = execute_statement(client, sql, sql_parameters)
# If the user does not exist
if (extract_records(user) == []):
return {
'body': json.dumps("The user does not exist"),
'statusCode': 404
}
# If the user exist
else:
sql = ''
def get_reports(event, context):
response_headers = {}
response_headers["X-Requested-With"] = "*"
response_headers["Access-Control-Allow-Origin"] = "*"
response_headers["Access-Control-Allow-Headers"] = "Content-Type,X-Amz-Date,Authorization,X-Api-Key,x-requested-with'"
response_headers["Access-Control-Allow-Methods"] = "OPTIONS,POST,GET,PUT,DELETE"
# query db for all the reports
query = "SELECT * FROM reports;"
result = execute_statement(query)
# no reports
if result['records'] == []:
return{
'statusCode': 200,
'body': json.dumps("No reports found"),
'headers': response_headers
}
# parse the response
response_body = extract_records(result)
return {
'statusCode': 200,
'body': json.dumps(response_body),
'headers': response_headers
}
def get_tags():
# query the database for all current tags
sql = "SELECT * FROM tags;"
query_result = execute_statement(sql)
# parse the result
response = extract_records(query_result)
return response
def remove_option(table_name, option_id):
# get necessary field names
if (table_name == 'tags'):
id = 'tag_id'
name = 'tag_name'
associated_table = 'supporter_tags'
elif (table_name == 'type_of_supporter'):
id = 'supp_type_id'
name = 'supp_type'
associated_table = 'supporter_type'
else:
id = 'type_id'
name = 'appointment_name'
# appointment_type is a special case
if (id == 'tag_id' or id == 'supp_type_id'):
# delete entries in associated_table
sql = f"DELETE FROM `{associated_table}` WHERE `{id}` = :option_id;"
sql_parameters = [{
'name': 'option_id',
'value': {
'longValue': option_id
}
}]
query_result = execute_statement(sql, sql_parameters)
# delete from main table
sql = f"DELETE FROM `{table_name}` WHERE `{id}` = :option_id;"
query_result = execute_statement(sql, sql_parameters)
else:
sql = f"UPDATE `appointment_type` SET active_type = false WHERE `{id}` = :option_id;"
sql_parameters = [{
'name': 'option_id',
'value': {
'longValue': option_id
}
}]
query_result = execute_statement(sql, sql_parameters)
# will always be true for appointment_type, probably not worth fixing right now
return not query_result['numberOfRecordsUpdated'] == 0
def getAvailabilityInfo(id):
#Database query for all users
query = "SELECT * FROM `availability_supp` WHERE `user_id_` = :id;"
sqlParameters = [{'name': "id", 'value': {'longValue': id}}]
userQuery = execute_statement(query, sqlParameters)
#Parse the result to prep for json.dumps
response = extract_records(userQuery)
return response
def getAppointmentTypes(id):
#Database query for all users
query = "SELECT Distinct appointment_name FROM appointment_type INNER JOIN supp_appt ON supp_appt.type_id = appointment_type.type_id where user_id_= :id;"
sqlParameters = [{'name': "id", 'value': {'longValue': id}}]
userQuery = execute_statement(query, sqlParameters)
#Parse the result to prep for json.dumps
response = extract_records(userQuery)
return response
def getTags(id):
#Database query for all users
query = "SELECT Distinct tag_name FROM supporter_tags INNER JOIN tags ON supporter_tags.tag_id = tags.tag_id where user_id_= :id;"
sqlParameters = [{'name': "id", 'value': {'longValue': id}}]
userQuery = execute_statement(query, sqlParameters)
#Parse the result to prep for json.dumps
response = extract_records(userQuery)
return response
def getFeedback(id):
#Database query for all users
query = "SELECT Distinct student_feedback.appointment_id, appointment_name, rating, recommended FROM student_feedback, appointments, appointment_type where student_feedback.appointment_id = appointments.appointment_id and appointments.supporter_id = :id and appointments.type_id=appointment_type.type_id;;"
sqlParameters = [{'name': "id", 'value': {'longValue': id}}]
userQuery = execute_statement(query, sqlParameters)
#Parse the result to prep for json.dumps
response = extract_records(userQuery)
return response
def get_options(table_name):
# query the database for all current tags
sql = f"SELECT * FROM {table_name};"
if table_name == 'appointment_type':
sql = f"SELECT * FROM {table_name} WHERE active_type = true;"
query_result = execute_statement(sql)
# parse the result
response = extract_records(query_result)
return response
def remove_tag(tag_id):
client = boto3.client('rds-data')
# delete the tag from the tags table
sql = "DELETE FROM `tags` WHERE `tag_id` = :tag_id;"
sql_parameters = [{'name': 'tag_id', 'value': {'longValue': tag_id}}]
query_result = execute_statement(client, sql, sql_parameters)
print("delete from tags table")
print(query_result)
print(query_result['numberOfRecordsUpdated'])
# tag_id didn't actually delete anything
if (query_result['numberOfRecordsUpdated'] == 0):
return False
# delete the tag from the supporter_tags table
sql = "DELETE FROM `supporter_tags` WHERE `tag_id` = :tag_id;"
sql_parameters = [{'name': 'tag_id', 'value': {'longValue': tag_id}}]
query_result = execute_statement(client, sql, sql_parameters)
print("delete from supporter_tags table")
print(query_result)
return True
def cancel_appt(event, context):
# Extract the appt id to delete
appt_id = int(event['pathParameters']['id'])
# Check if the appt id exists in database
query = "SELECT `appointment_id` FROM `appointments` WHERE `appointment_id` = :appt_id;"
sql_params = [{'name': 'appt_id', 'value':{'longValue': appt_id}}]
existing_appt = execute_statement(query, sql_params)
# Appt id not found
if existing_appt['records'] == []:
return {
'statusCode': 404,
'body': json.dumps('Appointment id not found in database')
}
# Cancel the appt
query = "UPDATE `appointments` SET `cancelled` = true WHERE `appointment_id` = :appt_id;"
query_response = execute_statement(query, sql_params)
# If the database does not update the appt to cancelled
if query_response['numberOfRecordsUpdated'] == 0:
return {
'statusCode': 500,
'body': json.dumps('Error in cancelling the appointment')
}
response_headers = {}
response_headers["X-Requested-With"] = "*"
response_headers["Access-Control-Allow-Origin"] = "*"
response_headers["Access-Control-Allow-Headers"] = "Content-Type,X-Amz-Date,Authorization,X-Api-Key,x-requested-with'"
response_headers["Access-Control-Allow-Methods"] = "OPTIONS,POST,GET,PUT,DELETE"
# Return success
return {
'statusCode': 200,
'body': json.dumps('Successfully cancelled appointment')
'headers': response_headers
}
def getStudentInfo(id):
#Database query for all users
query = "SELECT * FROM `student` WHERE `user_id_` = :id;"
sqlParameters = [{'name': "id", 'value': {'longValue': id}}]
userQuery = execute_statement(query, sqlParameters)
if userQuery['records'] == []:
return []
#Parse the result to prep for json.dumps
response = extract_records(userQuery)
return response
def getSupporterInfo(id):
#Database query for all users
query = "SELECT supporter.user_id_, title,current_employer, location calendar_ref, calendar_sync, calendar_sync_freq, supp_type_id FROM supporter Left JOIN supporter_type on supporter_type.user_id_=supporter.user_id_ where supporter.user_id_= :id"
sqlParameters = [{'name': "id", 'value': {'longValue': id}}]
userQuery = execute_statement(query, sqlParameters)
#Parse the result to prep for json.dumps
response = extract_records(userQuery)
availResponse = []
feedResponse = []
tagResponse = []
supTypeResponse = []
apptTypeResponse = []
extraQuery = getAvailabilityInfo(id) #Check supporter's availability
if extraQuery != []:
for record in extraQuery:
availResponse.append(record)
response.append(availResponse)
extraQuery = getFeedback(id) #Check supporter's feedback info
if extraQuery != []:
for record in extraQuery:
feedResponse.append(record)
response.append(feedResponse)
extraQuery = getTags(id) #Check supporter's tags
if extraQuery != []:
for record in extraQuery:
tagResponse.append(record)
response.append(tagResponse)
extraQuery = getSupporterTypes(id) #Check supporter's types
if extraQuery != []:
for record in extraQuery:
supTypeResponse.append(record)
response.append(supTypeResponse)
extraQuery = getAppointmentTypes(id) #Check Appointment's types
if extraQuery != []:
for record in extraQuery:
apptTypeResponse.append(record)
response.append(apptTypeResponse)
return response
def getUserInfo(id):
#Database query for all users
query = "SELECT * FROM `user` WHERE `user_id_` = :id;"
sqlParameters = [{'name': "id", 'value': {'longValue': id}}]
userQuery = execute_statement(query, sqlParameters)
if userQuery['records'] == []:
return []
#Parse the result to prep for json.dumps
response = extract_records(userQuery)
#Get additional info depending on if user is student or supporter
extraQuery = getStudentInfo(id) #Check if a student
if extraQuery == []:
extraQuery = getSupporterInfo(id)
#Combine the two queries and return a response
for record in extraQuery:
response.append(record)
return response
def lambda_handler(event, context):
#Getting the values to put into the row
student_id = int(json.loads(event["body"])["student_id"])
supporter_id = int(json.loads(event["body"])["supporter_id"])
appt_date = json.loads(event["body"])["appt_date"]
start_time = json.loads(event["body"])["start_time"]
duration_min = int(json.loads(event["body"])["duration"])
type = int(json.loads(event["body"])["type"])
cancelled = bool(json.loads(event["body"])["cancelled"])
rating = int(json.loads(event["body"])["rating"])
recommended = bool(json.loads(event["body"])["recommended"])
#CORS Header
response_headers = {}
response_headers["X-Requested-With"] = "*"
response_headers["Access-Control-Allow-Origin"] = "*"
response_headers[
"Access-Control-Allow-Headers"] = "Content-Type,X-Amz-Date,Authorization,X-Api-Key,x-requested-with'"
response_headers[
"Access-Control-Allow-Methods"] = "OPTIONS,POST,GET,PUT,DELETE"
#Finding the given student_id to check
query = "SELECT user_id_ FROM student WHERE user_id_ = :student_id"
sql_params = [{'name': 'student_id', 'value': {'longValue': student_id}}]
student_check = execute_statement(query, sql_params)
#Checking if the given student exists
if (student_check['records'] == []):
return {
'body': json.dumps("Student not found!"),
'headers': response_headers,
'statusCode': 404,
'isBase64Encoded': False
}
#Finding the given supporter_id
query = "SELECT user_id_ FROM supporter WHERE user_id_ = :supporter_id"
sql_params = [{
'name': 'supporter_id',
'value': {
'longValue': supporter_id
}
}]
supporter_check = execute_statement(query, sql_params)
#Checking if the given supporter_id exists
if (supporter_check['records'] == []):
return {
'body': json.dumps("Supporter not found!"),
'headers': response_headers,
'statusCode': 404,
'isBase64Encoded': False
}
#Finding the given type_id
query = "SELECT type_id FROM appointment_type WHERE type_id = :type"
sql_params = [{'name': 'type', 'value': {'longValue': type}}]
type_check = execute_statement(query, sql_params)
#Checking if the given type_id exists
if (type_check['records'] == []):
return {
'body': json.dumps("type not found!"),
'headers': response_headers,
'statusCode': 404,
'isBase64Encoded': False
}
#Checking if the given date string is in the correct format or not
date_re = re.compile('^([12]\d{3}-(0[1-9]|1[0-2])-(0[1-9]|[12]\d|3[01]))$')
#Returning bad request if the format is wrong
if not (date_re.match(appt_date)):
return {
'body': json.dumps("Date bad format!"),
'headers': response_headers,
'statusCode': 400,
'isBase64Encoded': False
}
#Checking if the given time string is in the correct format or not
time_re = re.compile('^([0-1][0-9]|[2][0-3]):([0-5][0-9]):([0-5][0-9])$')
#Returning bad request if the format is wrong
if not (time_re.match(start_time)):
return {
'body': json.dumps("Time bad format!"),
'headers': response_headers,
'statusCode': 400,
'isBase64Encoded': False
}
# Generating a new appointment_id for the current appointment by adding 1 to the last id
query = "SELECT appointment_id FROM appointments ORDER BY appointment_id DESC LIMIT 1"
sql_params = []
new_id = execute_statement(query, sql_params)
appointment_id = new_id['records'][0][0]['longValue'] + 1
#Inserting the values in the appointments table
query = """INSERT INTO appointments(appointment_id, supporter_id, date_of_appointment, start_time, duration_in_min, type_id, cancelled) \
VALUES (:appointment_id, :supporter_id, :appt_date, :start_time, :duration_min, :type, :cancelled)"""
sql_params = [{
'name': 'appointment_id',
'value': {
'longValue': appointment_id
}
}, {
'name': 'supporter_id',
'value': {
'longValue': supporter_id
}
}, {
'name': 'appt_date',
'typeHint': 'DATE',
'value': {
'stringValue': appt_date
}
}, {
'name': 'start_time',
'typeHint': 'TIME',
'value': {
'stringValue': start_time
}
}, {
'name': 'duration_min',
'value': {
'longValue': duration_min
}
}, {
'name': 'type',
'value': {
'longValue': type
}
}, {
'name': 'cancelled',
'value': {
'booleanValue': cancelled
}
}]
#Updating the appointment table
update = execute_statement(query, sql_params)
#Inserting the values in the student_feedback table
query = """INSERT INTO student_feedback(appointment_id, student_id, rating, recommended) \
VALUES (:appointment_id, :student_id, :rating, recommended)"""
sql_params = [{
'name': 'appointment_id',
'value': {
'longValue': appointment_id
}
}, {
'name': 'student_id',
'value': {
'longValue': student_id
}
}, {
'name': 'rating',
'value': {
'longValue': rating
}
}, {
'name': 'recommended',
'value': {
'booleanValue': recommended
}
}]
#Updating the student_feedback table
update = execute_statement(query, sql_params)
return {
'statusCode': 201,
'body': json.dumps('Appointment created!'),
'headers': response_headers,
'isBase64Encoded': False
}
def student_create(body, response_headers):
statusCode = 200
#Test to see if input properly formatted
if ("email" not in body.keys()):
statusCode = 400
if ("password" not in body.keys()):
statusCode = 400
if ("first_name" not in body.keys()):
statusCode = 400
if ("last_name" not in body.keys()):
statusCode = 400
if ("preferred_name" not in body.keys()):
statusCode = 400
if ("phone_number" not in body.keys()):
statusCode = 400
if ("profile_picture" not in body.keys()):
statusCode = 400
if ("request_supporter" not in body.keys()):
statusCode = 400
if ("active_account" not in body.keys()):
statusCode = 400
if ("description" not in body.keys()):
statusCode = 400
if ("pronouns" not in body.keys()):
statusCode = 400
if statusCode == 400:
response = {}
response['statusCode'] = statusCode
response['headers'] = response_headers
response['body'] = "Invalid Input Body Format!"
response['isBase64Encoded'] = False
return response
#Primarily for student
if ("GPA" not in body.keys()):
statusCode = 400
if ("grad_year" not in body.keys()):
statusCode = 400
if ("resume_ref" not in body.keys()):
statusCode = 400
if ("transcript_ref" not in body.keys()):
statusCode = 400
if ("github_link" not in body.keys()):
statusCode = 400
if ("linkedin_link" not in body.keys()):
statusCode = 400
if ("is_undergrad" not in body.keys()):
statusCode = 400
if ("college" not in body.keys()):
statusCode = 400
if ("program" not in body.keys()):
statusCode = 400
if ("job_search" not in body.keys()):
statusCode = 400
if ("work_auth" not in body.keys()):
statusCode = 400
if statusCode == 400:
response = {}
response['statusCode'] = statusCode
response['headers'] = response_headers
response['body'] = "Invalid Student Body Format!"
response['isBase64Encoded'] = False
return response
#Connect to database
client = boto3.client('rds-data')
#Extract user fields
email = body["email"]
password = body["password"]
first_name = body["first_name"]
last_name = body["last_name"]
preferred_name = body["preferred_name"]
phone_number = body["phone_number"]
profile_picture = body["profile_picture"]
request_supporter = body["request_supporter"]
active_account = body["active_account"]
description = body["description"]
pronouns = body["pronouns"]
#Check if email already exists
sql = "SELECT email FROM user WHERE email = :email;"
sql_parameters = [{'name': 'email', 'value': {'stringValue': email}}]
query_result = execute_statement(client, sql, sql_parameters)
email_exists = (extract_records(query_result) != [])
if email_exists:
response = {}
response['statusCode'] = 404
response['headers'] = response_headers
response['body'] = "Email Exists!"
response['isBase64Encoded'] = False
return response
#Find next available user id
sql = "SELECT MAX(user_id_) FROM user;"
query_result = execute_statement(client, sql)
max_id = extract_records(query_result)
user_id = 0 if len(max_id) == 0 else max_id[0][0] + 1
#Insert into user
sql = "INSERT INTO user VALUES (:user_id, :email, :password, :first_name, :last_name, :preferred_name, :phone_number, :profile_picture, :request_supporter, :active_account, :description, :pronouns);"
sql_parameters = [{
'name': 'user_id',
'value': {
'longValue': user_id
}
}, {
'name': 'email',
'value': {
'stringValue': email
}
}, {
'name': 'password',
'value': {
'stringValue': password
}
}, {
'name': 'first_name',
'value': {
'stringValue': first_name
}
}, {
'name': 'last_name',
'value': {
'stringValue': last_name
}
}, {
'name': 'preferred_name',
'value': {
'stringValue': preferred_name
}
}, {
'name': 'phone_number',
'value': {
'stringValue': phone_number
}
}, {
'name': 'profile_picture',
'value': {
'stringValue': profile_picture
}
}, {
'name': 'request_supporter',
'value': {
'booleanValue': request_supporter
}
}, {
'name': 'active_account',
'value': {
'booleanValue': active_account
}
}, {
'name': 'description',
'value': {
'stringValue': description
}
}, {
'name': 'pronouns',
'value': {
'stringValue': pronouns
}
}]
query_result = execute_statement(client, sql, sql_parameters)
#Extract student fields
GPA = body["GPA"]
grad_year = body["grad_year"]
resume_ref = body["resume_ref"]
transcript_ref = body["transcript_ref"]
github_link = body["github_link"]
linkedin_link = body["linkedin_link"]
is_undergrad = body["is_undergrad"]
college = body["college"]
program = body["program"]
job_search = body["job_search"]
work_auth = body["work_auth"]
#Insert into student
sql = "INSERT INTO student VALUES (:user_id, :GPA, :grad_year, :resume_ref, :transcript_ref, :github_link, :linkedin_link, :is_undergrad, :college, :program, :job_search, :work_auth);"
sql_parameters = [{
'name': 'user_id',
'value': {
'longValue': user_id
}
}, {
'name': 'GPA',
'value': {
'doubleValue': GPA
}
}, {
'name': 'grad_year',
'value': {
'longValue': grad_year
}
}, {
'name': 'resume_ref',
'value': {
'stringValue': resume_ref
}
}, {
'name': 'transcript_ref',
'value': {
'stringValue': transcript_ref
}
}, {
'name': 'github_link',
'value': {
'stringValue': github_link
}
}, {
'name': 'linkedin_link',
'value': {
'stringValue': linkedin_link
}
}, {
'name': 'is_undergrad',
'value': {
'booleanValue': is_undergrad
}
}, {
'name': 'college',
'value': {
'stringValue': college
}
}, {
'name': 'program',
'value': {
'stringValue': program
}
}, {
'name': 'job_search',
'value': {
'booleanValue': job_search
}
}, {
'name': 'work_auth',
'value': {
'stringValue': work_auth
}
}]
query_result = execute_statement(client, sql, sql_parameters)
response = {}
response["statusCode"] = statusCode
response["headers"] = response_headers
response["body"] = json.dumps("user-id: %d" % user_id)
response["isBase64Encoded"] = False
return response
def lambda_handler(event, context):
response_headers = {}
response_headers["X-Requested-With"] = "*"
response_headers["Access-Control-Allow-Origin"] = "*"
response_headers[
"Access-Control-Allow-Headers"] = "Content-Type,X-Amz-Date,Authorization,X-Api-Key,x-requested-with'"
response_headers[
"Access-Control-Allow-Methods"] = "OPTIONS,POST,GET,PUT,DELETE"
try:
# supp_id
user_id_ = int(event["pathParameters"]["id"])
except:
return {
'statusCode': 400,
'headers': response_headers,
'body': json.dumps({'message': 'Invalid user id input'})
}
# check supp exists
query = "SELECT * FROM supporter WHERE user_id_ = :user_id_"
sql_param = [{'name': 'user_id_', 'value': {'longValue': user_id_}}]
result = execute_statement(query, sql_param)
if result['records'] == []:
return {
'statusCode': 404,
'headers': response_headers,
'body': json.dumps({'message': 'Supporter does not exist'})
}
response = []
query = "SELECT * FROM supporter_questions WHERE user_id_ = :user_id_;"
sql_param = [{'name': 'user_id_', 'value': {'longValue': user_id_}}]
result = execute_statement(query, sql_param)
if result['records'] != []:
questions = []
for entry in result['records']:
q_id = list(entry[0].values())[0]
q = list(entry[2].values())[0]
block = {'question_id': q_id, 'question': q}
questions.append(block)
response.append(questions)
else:
response.append([])
# response.append("No Questions")
query = "SELECT show_feedback, rating, ask_recommended FROM feedback_settings WHERE user_id_ = :user_id_;"
sql_param = [{'name': 'user_id_', 'value': {'longValue': user_id_}}]
result = execute_statement(query, sql_param)
if result['records'] != []:
for entry in result['records']:
feedback = list(entry[0].values())[0]
rating = list(entry[1].values())[0]
rec = list(entry[2].values())[0]
block = {
"show_feedback": feedback,
"rating": rating,
"recommended": rec
}
response.append(block)
else:
response.append({})
return {
'body': json.dumps(response),
'statusCode': 200,
'headers': response_headers
}
def create_report(event, context):
response_headers = {}
response_headers["X-Requested-With"] = "*"
response_headers["Access-Control-Allow-Origin"] = "*"
response_headers[
"Access-Control-Allow-Headers"] = "Content-Type,X-Amz-Date,Authorization,X-Api-Key,x-requested-with'"
response_headers[
"Access-Control-Allow-Methods"] = "OPTIONS,POST,GET,PUT,DELETE"
request_body = json.loads(event["body"])
values = [
"reporter_id_", "reported_id_", "report_reason", "report_date",
"report_time"
]
# Check request body has all required values
if not all(x in request_body for x in values):
return {
'statusCode':
400,
'body':
json.dumps(
'Invalid request body, need to contain all the following values: "reporter_id_", \
"reported_id_", "report_reason", "report_date", "report_time"'
),
'headers':
response_headers
}
# Extract data from request body
reporter_id_ = int(request_body["reporter_id_"])
reported_id_ = int(request_body["reported_id_"])
report_reason = request_body["report_reason"]
report_date = request_body["report_date"]
report_time = request_body["report_time"]
# Check that the users exist
query = "SELECT user_id_ FROM user WHERE user_id_ = :reporter_id_;"
sql_params = [{
'name': 'reporter_id_',
'value': {
'longValue': reporter_id_
}
}]
result = execute_statement(query)
if result['records'] == []:
return {
'statusCode': 404,
'body': json.dumps('reporter_id_ is not an existing user'),
'headers': response_headers
}
query = "SELECT user_id_ FROM user WHERE user_id_ = :reported_id_;"
sql_params = [{
'name': 'reported_id_',
'value': {
'longValue': reported_id_
}
}]
result = execute_statement(query)
if result['records'] == []:
return {
'statusCode': 404,
'body': json.dumps('reported_id_ is not an existing user'),
'headers': response_headers
}
# Insert the report into db
query = "INSERT INTO reports VALUES(:reporter_id_, :reported_id_, :report_reason, :report_date, :report_time);"
sql_params = [{
'name': 'reporter_id_',
'value': {
'longValue': reporter_id_
}
}, {
'name': 'reported_id_',
'value': {
'longValue': reported_id_
}
}, {
'name': 'report_reason',
'value': {
'stringValue': report_reason
}
}, {
'name': 'report_date',
'value': {
'stringValue': report_date
}
}, {
'name': 'report_time',
'value': {
'stringValue': report_time
}
}]
result = execute_statement(query, sql_params)
# Check that the db was updated
if result['numberOfRecordsUpdated'] == 0:
return {
'statusCode': 500,
'body': json.dumps('Database error creating report'),
'headers': response_headers
}
# Success
return {
'statusCode': 201,
'body': json.dumps('Successfully created the report'),
'headers': response_headers
}
def get_appointments(event, context):
response_headers = {}
response_headers["X-Requested-With"] = "*"
response_headers["Access-Control-Allow-Origin"] = "*"
response_headers[
"Access-Control-Allow-Headers"] = "Content-Type,X-Amz-Date,Authorization,X-Api-Key,x-requested-with'"
response_headers[
"Access-Control-Allow-Methods"] = "OPTIONS,POST,GET,PUT,DELETE"
try:
u_id = int(event["pathParameters"]["id"])
except:
return {
'statusCode': 400,
'headers': response_headers,
'body': json.dumps({'message': 'Invalid user id input'})
}
# Checks if user_id_ is a student id
query = "SELECT user_id_ FROM student WHERE user_id_ = :u_id"
sql_params = [{'name': 'u_id', 'value': {'longValue': u_id}}]
student_check = execute_statement(query, sql_params)
# Checking if the given id does not exist in student then run query for supporter else run query for student
if (student_check['records'] == []): # Gather for supporters
query = (
"SELECT "
"A.*, U.first_name, U.last_name, T.appointment_name, S.location "
", SF.rating "
"FROM appointments A "
"LEFT JOIN user U ON U.user_id_ = A.supporter_id "
"LEFT JOIN appointment_type T ON T.type_id = A.type_id "
"LEFT JOIN supporter S ON S.user_id_ = U.user_id_ "
"LEFT JOIN student_feedback SF ON SF.appointment_id = A.appointment_id "
"LEFT JOIN student_responses SR ON SR.appointment_id = A.appointment_id "
"WHERE A.supporter_id = :u_id GROUP BY A.appointment_id ; ")
else: # Gather for students
query = (
"SELECT "
"A.*, U.first_name, U.last_name, T.appointment_name, SU.location "
", SF.rating "
"FROM appointments A "
"LEFT JOIN student_feedback SF ON SF.appointment_id = A.appointment_id "
"LEFT JOIN student S ON SF.student_id = S.user_id_ "
"LEFT JOIN user U ON U.user_id_ = A.supporter_id "
"LEFT JOIN appointment_type T ON T.type_id = A.type_id "
"LEFT JOIN supporter SU ON SU.user_id_ = U.user_id_ "
"LEFT JOIN student_responses SR ON SR.appointment_id = A.appointment_id "
"WHERE S.user_id_ = :u_id GROUP BY A.appointment_id; ")
sql_params = [{'name': 'u_id', 'value': {'longValue': u_id}}]
query_result = execute_statement(query, sql_params)
response = extract_records(query_result)
query = "SELECT user_id_ FROM student WHERE user_id_ = :u_id"
sql_params = [{'name': 'u_id', 'value': {'longValue': u_id}}]
student_check = execute_statement(query, sql_params)
# Checking again but for non-null ratings
if (student_check['records'] == []): # Gather for supporters
query2 = (
"SELECT "
"A.appointment_id, SF.rating "
"FROM appointments A "
"LEFT JOIN student_feedback SF ON SF.appointment_id = A.appointment_id "
"LEFT JOIN student_responses SR ON SR.appointment_id = A.appointment_id "
"WHERE A.supporter_id = :u_id AND SF.rating IS NOT NULL GROUP BY A.appointment_id; "
)
else: # Gather for students
query2 = (
"SELECT "
"A.appointment_id, SF.rating "
"FROM appointments A "
"LEFT JOIN student_feedback SF ON SF.appointment_id = A.appointment_id "
"LEFT JOIN student S ON SF.student_id = S.user_id_ "
"LEFT JOIN user U ON U.user_id_ = A.supporter_id "
"WHERE S.user_id_ = :u_id AND SF.rating IS NOT NULL GROUP BY A.appointment_id; "
)
query_result = execute_statement(query2, sql_params)
response2 = extract_records(query_result)
for entry in response:
if response2 == []:
entry[11] = False
elif entry[0] == response2[0][0] and entry[11]:
entry[11] = True
response2.pop(0)
else:
entry[11] = False
return {
'statusCode': 200,
'headers': response_headers,
'body': json.dumps(response)
}
def lambda_handler(event, context):
response_headers = {}
response_body = {}
request_body = json.loads(event["body"])
attr_list = ["appointment_id", "user_id_", "rating", "recommended", "questions"]
if (not all(item in attr_list for item in request_body)):
statusCode = 400
response_body = {
'message' : 'Invalid request format: request body does not contain all the required attributes',
'request body' : json.dumps(request_body)
}
else:
appointment_id = int(json.loads(event["body"])["appointment_id"])
user_id_ = int(json.loads(event["body"])["user_id_"])
rating = int(json.loads(event["body"])["rating"])
recommended = str(json.loads(event["body"])["recommended"])
question_list = json.loads(event["body"])["questions"]
#check if appointment_id and user_id exists in the database
query = "SELECT appointment_id FROM appointments WHERE appointment_id = :appointment_id;"
sql_params = [{'name': 'appointment_id', 'value':{'longValue': appointment_id}}]
existing_appt = execute_statement(query, sql_params)
query = "SELECT user_id_ FROM student WHERE user_id_ = :user_id_;"
sql_params = [{'name': 'user_id_', 'value':{'longValue': user_id_}}]
existing_user = execute_statement(query, sql_params)
if existing_appt['records'] == []:
return {
'statusCode': 404,
'body': json.dumps({ 'message' : 'appointment_id not in database' })
}
if existing_user['records'] == []:
return {
'statusCode': 404,
'body': json.dumps({ 'message' : 'user_id_ not in database' })
}
query = "INSERT INTO student_feedback VALUES (:appointment_id, :user_id_, :rating, :recommended);"
sql_parameters = [ {'name':'appointment_id', 'value':{'longValue': appointment_id}},
{'name':'user_id_', 'value':{'longValue': user_id_}},
{'name':'rating', 'value':{'longValue': rating}},
{'name':'recommended', 'value':{'stringValue': recommended}} ]
query_result = execute_statement(query, sql_parameters)
#Insert response to questions into the student_responses table
if(len(question_list) > 0):
for question in question_list:
question_id = int(question["question_id"])
response = str(question["response"])
#Check to see if q exists in db
query = "SELECT question_id FROM supporter_questions WHERE question_id = :question_id;"
sql_params = [{'name': 'question_id', 'value':{'longValue': question_id}}]
existing_question = execute_statement(query, sql_params)
if existing_question['records'] == []:
return {
'statusCode': 404,
'body': json.dumps({ 'message' : 'question_id not in database' })
}
else:
query = "INSERT INTO student_responses VALUES (:appointment_id, :user_id_, :question_id, :response);"
sql_parameters = [ {'name':'appointment_id','value':{'longValue': appointment_id}},
{'name':'user_id_', 'value':{'longValue': user_id_}},
{'name':'question_id', 'value':{'longValue': question_id}},
{'name':'response', 'value':{'stringValue': response}} ]
query_result = execute_statement(query, sql_parameters)
response_body = { 'message' : 'feedback added' }
statusCode = 201
response_headers["X-Requested-With"] = "*"
response_headers["Access-Control-Allow-Origin"] = "*"
response_headers["Access-Control-Allow-Headers"] = "Content-Type,X-Amz-Date,Authorization,X-Api-Key,x-requested-with'"
response_headers["Access-Control-Allow-Methods"] = "OPTIONS,POST,GET,PUT,DELETE"
return {
'statusCode': statusCode,
'headers' : response_headers,
'body': json.dumps(response_body),
'isBase64Encoded' : False
}
def supporter_create(body, response_headers):
statusCode = 200
#Test to see if input properly formatted
if ("email" not in body.keys()):
statusCode = 400
if ("password" not in body.keys()):
statusCode = 400
if ("first_name" not in body.keys()):
statusCode = 400
if ("last_name" not in body.keys()):
statusCode = 400
if ("preferred_name" not in body.keys()):
statusCode = 400
if ("phone_number" not in body.keys()):
statusCode = 400
if ("profile_picture" not in body.keys()):
statusCode = 400
if ("request_supporter" not in body.keys()):
statusCode = 400
if ("active_account" not in body.keys()):
statusCode = 400
if ("description" not in body.keys()):
statusCode = 400
if ("pronouns" not in body.keys()):
statusCode = 400
if statusCode == 400:
response = {}
response['statusCode'] = statusCode
response['headers'] = response_headers
response['body'] = "Invalid Input Body Format!"
response['isBase64Encoded'] = False
return response
#Primarily for supporter
if ("title" not in body.keys()):
statusCode = 400
if ("current_employer" not in body.keys()):
statusCode = 400
if ("location" not in body.keys()):
statusCode = 400
if ("calendar_ref" not in body.keys()):
statusCode = 400
if ("calendar_sync" not in body.keys()):
statusCode = 400
if ("calendar_sync_freq" not in body.keys()):
statusCode = 400
if statusCode == 400:
response = {}
response['statusCode'] = statusCode
response['headers'] = response_headers
response['body'] = "Invalid Supporter Body Format!"
response['isBase64Encoded'] = False
return response
#Connect to database
client = boto3.client('rds-data')
#Extract user fields
email = body["email"]
password = body["password"]
first_name = body["first_name"]
last_name = body["last_name"]
preferred_name = body["preferred_name"]
phone_number = body["phone_number"]
profile_picture = body["profile_picture"]
request_supporter = body["request_supporter"]
active_account = body["active_account"]
description = body["description"]
pronouns = body["pronouns"]
#Check if email already exists
sql = "SELECT email FROM user WHERE email = :email;"
sql_parameters = [{'name': 'email', 'value': {'stringValue': email}}]
query_result = execute_statement(client, sql, sql_parameters)
email_exists = (extract_records(query_result) != [])
if email_exists:
response = {}
response['statusCode'] = 404
response['headers'] = response_headers
response['body'] = "Email Exists!"
response['isBase64Encoded'] = False
return response
#Find next available user id
sql = "SELECT MAX(user_id_) FROM user;"
query_result = execute_statement(client, sql)
max_id = extract_records(query_result)
user_id = 0 if len(max_id) == 0 else max_id[0][0] + 1
#Insert into user
sql = "INSERT INTO user VALUES (:user_id, :email, :password, :first_name, :last_name, :preferred_name, :phone_number, :profile_picture, :request_supporter, :active_account, :description, :pronouns);"
sql_parameters = [{
'name': 'user_id',
'value': {
'longValue': user_id
}
}, {
'name': 'email',
'value': {
'stringValue': email
}
}, {
'name': 'password',
'value': {
'stringValue': password
}
}, {
'name': 'first_name',
'value': {
'stringValue': first_name
}
}, {
'name': 'last_name',
'value': {
'stringValue': last_name
}
}, {
'name': 'preferred_name',
'value': {
'stringValue': preferred_name
}
}, {
'name': 'phone_number',
'value': {
'stringValue': phone_number
}
}, {
'name': 'profile_picture',
'value': {
'stringValue': profile_picture
}
}, {
'name': 'request_supporter',
'value': {
'booleanValue': request_supporter
}
}, {
'name': 'active_account',
'value': {
'booleanValue': active_account
}
}, {
'name': 'description',
'value': {
'stringValue': description
}
}, {
'name': 'pronouns',
'value': {
'stringValue': pronouns
}
}]
query_result = execute_statement(client, sql, sql_parameters)
#Extract supporter fields
title = body["title"]
current_employer = body["current_employer"]
location = body["location"]
calendar_ref = body["calendar_ref"]
calendar_sync = body["calendar_sync"]
calendar_sync_freq = body["calendar_sync_freq"]
#Insert into supporter
sql = "INSERT INTO supporter VALUES (:user_id, :title, :current_employer, :location, :calendar_ref, :calendar_sync, :calendar_sync_freq);"
sql_parameters = [{
'name': 'user_id',
'value': {
'longValue': user_id
}
}, {
'name': 'title',
'value': {
'stringValue': title
}
}, {
'name': 'current_employer',
'value': {
'stringValue': current_employer
}
}, {
'name': 'location',
'value': {
'stringValue': location
}
}, {
'name': 'calendar_ref',
'value': {
'stringValue': calendar_ref
}
}, {
'name': 'calendar_sync',
'value': {
'booleanValue': calendar_sync
}
}, {
'name': 'calendar_sync_freq',
'value': {
'longValue': calendar_sync_freq
}
}]
query_result = execute_statement(client, sql, sql_parameters)
response = {}
response["statusCode"] = statusCode
response["headers"] = response_headers
response["body"] = json.dumps("user-id: %d" % user_id)
response["isBase64Encoded"] = False
return response
