def get_submissions_by_email(connection: Connection,
email: str,
survey_id: str=None,
submitters: Iterator=None,
filters: list=None,
order_by: str=None,
direction: str='ASC',
limit: int=None) -> ResultProxy:
"""
Get submissions to a survey.
:param connection: a SQLAlchemy Connection
:param survey_id: the UUID of the survey
:param email: the e-mail address of the user
:param submitters: if supplied, filters results by all given submitters
:param filters: if supplied, filters results by answers
:param order_by: if supplied, the column for the ORDER BY clause
:param direction: optional sort direction for order_by (default ASC)
:param limit: if supplied, the limit to apply to the number of results
:return: an iterable of the submission records
"""
table = submission_table.join(survey_table).join(auth_user_table)
conds = [auth_user_table.c.email == email]
if survey_id:
conds.append(submission_table.c.survey_id == survey_id)
if submitters is not None:
conds.append(submission_table.c.submitter.in_(submitters))
if filters is not None:
filtered = set(_get_filtered_ids(connection, filters))
conds.append(submission_table.c.submission_id.in_(filtered))
if order_by is None:
order_by = 'submission_time'
return connection.execute(
select(
[submission_table]
).select_from(
table
).where(
and_(*conds)
).order_by(
'{} {}'.format(order_by, direction)
).limit(
limit
)
)
def get_activity(connection: Connection,
email: str,
survey_id: str=None) -> dict:
"""
Get the number of submissions per day for the last 30 days for the given
survey.
:param connection: a SQLAlchemy Connection
:param email: the user's e-mail address
:param survey_id: the UUID of the survey, or None if fetching for all
user's surveys
:return: a JSON dict of the result
"""
submission_date = cast(submission_table.c.submission_time, Date)
conditions = [
submission_date > (current_date() - 30),
auth_user_table.c.email == email
]
if survey_id is not None:
conditions.append(submission_table.c.survey_id == survey_id)
result = connection.execute(
select(
[count(), submission_date]
).select_from(
submission_table.join(survey_table).join(auth_user_table)
).where(
and_(*conditions)
).group_by(
submission_date
).order_by(
submission_date
)
).fetchall()
return json_response(
[[num, sub_time.isoformat()] for num, sub_time in result]
)
def submission_select(connection: Connection,
submission_id: str,
auth_user_id: str=None,
email: str=None) -> RowProxy:
"""
Get a record from the submission table. You must supply either the
auth_user_id or the email.
:param connection: a SQLAlchemy Connection
:param submission_id: the UUID of the submission
:param auth_user_id: the UUID of the user
:param email: the user's e-mail address
:return: the corresponding records
:raise SubmissionDoesNotExistError: if the submission_id is not in the
table
"""
table = submission_table.join(survey_table)
conds = [submission_table.c.submission_id == submission_id]
if auth_user_id is not None:
if email is not None:
raise TypeError('You cannot specify both auth_user_id and email')
conds.append(survey_table.c.auth_user_id == auth_user_id)
elif email is not None:
table = table.join(auth_user_table)
conds.append(auth_user_table.c.email == email)
else:
raise TypeError('You must specify either auth_user_id or email')
submission = connection.execute(
select([submission_table]).select_from(table).where(
and_(*conds))).first()
if submission is None:
raise SubmissionDoesNotExistError(submission_id)
return submission