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