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