def get_stats(connection: Connection, survey_id: str, email: str) -> dict: """ Get statistics about the specified survey: creation time, number of submissions, time of the earliest submission, and time of the latest submission. :param connection: a SQLAlchemy Connection :param survey_id: the UUID of the survey :param email: the e-mail address of the user :return: a JSON representation of the statistics. """ result = connection.execute( select([ survey_table.c.created_on, count(submission_table.c.submission_id), sqlmin(submission_table.c.submission_time), sqlmax(submission_table.c.submission_time) ]).select_from( auth_user_table.join(survey_table).outerjoin(submission_table) ).where( survey_table.c.survey_id == survey_id ).where( auth_user_table.c.email == email ).group_by( survey_table.c.survey_id ) ).first() return json_response({ 'created_on': maybe_isoformat(result[0]), 'num_submissions': result[1], 'earliest_submission_time': maybe_isoformat(result[2]), 'latest_submission_time': maybe_isoformat(result[3]) })
def get_email_address(connection: Connection, survey_id: str) -> str: """ Dangerous function! Do not use this to circumvent the restriction that a survey can only be seen by its owner! Gets the e-mail address associated with a survey. :param connection: a SQLAlchemy Connection :param survey_id: the UUID of the survey :return: the user's e-mail address """ table = auth_user_table.join(survey_table) return connection.execute( select([auth_user_table.c.email]).select_from(table).where( survey_table.c.survey_id == survey_id)).first().email
def get(self, survey_id): email = self.get_email() result = self._get_records( table=auth_user_table.join(survey_table).join(submission_table), email=email, selected=[ submission_table.c.submission_id, submission_table.c.submitter, submission_table.c.submission_time, submission_table.c.save_time ], where=submission_table.c.survey_id == survey_id, text_filter_column=submission_table.c.submitter, default_sort_column_name='submission_time', total_records=get_number_of_submissions(self.db, survey_id) ) self.write(result)
def get_number_of_surveys(connection: Connection, email: str) -> int: """ Return the number of surveys for the given user. :param connection: a SQLAlchemy Connection :param email: the user's e-mail address :return: the number of surveys the user has """ return connection.execute( select( [count(survey_table.c.survey_id)] ).select_from( auth_user_table.join(survey_table) ).where( auth_user_table.c.email == email ) ).scalar()