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