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_one(connection: Connection, submission_id: str, email: str) -> dict:
"""
Create a JSON representation of a submission.
:param connection: a SQLAlchemy Connection
:param submission_id: the UUID of the submission
:param email: the user's e-mail address
:return: a JSON dict
"""
submission = submission_select(connection, submission_id, email=email)
answers = _get_comparable(get_answers(connection, submission_id))
choices = _get_comparable(get_answer_choices(connection, submission_id))
# The merge is necessary to get the answers in sequence number order.
result = merge(answers, choices)
c = connection
sub_dict = {
'submission_id': submission_id,
'survey_id': submission.survey_id,
'submitter': submission.submitter,
'submitter_email': submission.submitter_email,
'submission_time': submission.submission_time.isoformat(),
'save_time': submission.save_time.isoformat(),
'answers': [_get_fields(c, answer) for num, answer in result]
}
return json_response(sub_dict)
def count(connection: Connection,
question_id: str,
auth_user_id: str=None,
email: str=None) -> dict:
"""
Get the number of submissions to the specified question. You must
provide either an auth_user_id or e-mail address.
:param connection: a SQLAlchemy Connection
:param question_id: the UUID of the question
:param auth_user_id: the UUID of the user
:param email: the e-mail address of the user.
:return: a JSON dict containing the result
"""
types = {'text', 'integer', 'decimal', 'multiple_choice', 'date', 'time',
'location', 'facility'}
regular = _scalar(connection, question_id, sqlcount,
auth_user_id=auth_user_id,
email=email, allowable_types=types)
other = _scalar(connection, question_id, sqlcount,
auth_user_id=auth_user_id,
email=email, is_type_exception=True, allowable_types=types)
response = json_response(regular + other)
response['query'] = 'count'
return response
def create_user(connection: Connection, data: dict) -> dict:
"""
Registers a new user account.
:param connection: a SQLAlchemy Connection
:param data: the user's e-mail
:return: a response containing the e-mail and whether it was created or
already exists in the database
"""
email = data['email']
try:
get_auth_user_by_email(connection, email)
except UserDoesNotExistError:
with connection.begin():
connection.execute(create_auth_user(email=email))
return json_response({'email': email, 'response': 'Created'})
return json_response({'email': email, 'response': 'Already exists'})
def bar_graph(connection: Connection,
question_id: str,
auth_user_id: str=None,
email: str=None,
limit: [int, None]=None,
count_order: bool=False) -> dict:
"""
Get a list of the number of times each submission value appears. You must
provide either an auth_user_id or e-mail address.
:param connection: a SQLAlchemy Connection
:param question_id: the UUID of the question
:param auth_user_id: the UUID of the user
:param email: the e-mail address of the user.
:param limit: a limit on the number of results
:param count_order: whether to order from largest count to smallest
:return: a JSON dict containing the result [[values], [counts]]
"""
user_id = _get_user_id(connection, auth_user_id, email)
allowable_types = {'text', 'integer', 'decimal', 'multiple_choice', 'date',
'time', 'location', 'facility'}
question = question_select(connection, question_id)
tcn = _get_type_constraint_name(allowable_types, question)
# Assume that you only want to consider the non-other answers
original_table, column_name = _table_and_column(tcn)
table = original_table.join(
question_table,
original_table.c.question_id == question_table.c.question_id
).join(survey_table)
conds = [question_table.c.question_id == question_id,
survey_table.c.auth_user_id == user_id]
column = get_column(original_table, column_name)
column_query = select(
[column, sqlcount(column)]
).select_from(table).group_by(column)
ordering = desc(sqlcount(column)) if count_order else column
ordered_query = column_query.order_by(ordering)
result = connection.execute(
ordered_query.where(and_(*conds)).limit(limit)
)
result = _return_sql(connection, result, question.survey_id, user_id,
question_id)
bar_graph_result = [[_jsonify(connection, r[0], question_id), r[1]] for r
in result]
response = json_response(
_return_sql(connection, bar_graph_result, question.survey_id,
user_id, question_id))
response['query'] = 'bar_graph'
return response
def display_survey(connection: Connection, survey_id: str) -> dict:
"""
Get a JSON representation of a survey. Use this to display a survey for
submission purposes.
:param connection: a SQLAlchemy Connection
:param survey_id: the UUID of the survey
:return: the JSON representation.
"""
return json_response(_to_json(connection, display(connection, survey_id)))
def get_all(connection: Connection, email: str) -> dict:
"""
Return a JSON representation of all the surveys for a user.
:param connection: a SQLAlchemy Connection
:param email: the user's e-mail address.
:return: the JSON string representation
"""
surveys = get_surveys_by_email(connection, email)
return json_response([_to_json(connection, survey) for survey in surveys])
def delete(connection: Connection, survey_id: str):
"""
Delete the survey specified by the given survey_id
:param connection: a SQLAlchemy connection
:param survey_id: the UUID of the survey
"""
with connection.begin():
connection.execute(delete_record(survey_table, 'survey_id', survey_id))
return json_response('Survey deleted')
def time_series(connection: Connection,
question_id: str,
auth_user_id: str=None,
email: str=None) -> dict:
"""
Get a list of submissions to the specified question over time. You must
provide either an auth_user_id or e-mail address.
:param connection: a SQLAlchemy Connection
:param question_id: the UUID of the question
:param auth_user_id: the UUID of the user
:param email: the e-mail address of the user.
:return: a JSON dict containing the result [[times], [values]]
"""
user_id = _get_user_id(connection, auth_user_id, email)
allowable_types = {'text', 'integer', 'decimal', 'multiple_choice', 'date',
'time', 'location'}
question = question_select(connection, question_id)
tcn = _get_type_constraint_name(allowable_types, question)
# Assume that you only want to consider the non-other answers
original_table, column_name = _table_and_column(tcn)
table = original_table.join(
survey_table,
original_table.c.survey_id == survey_table.c.survey_id
).join(
submission_table,
original_table.c.submission_id == submission_table.c.submission_id
)
column = get_column(original_table, column_name)
where_stmt = select(
[column, submission_table.c.submission_time]
).select_from(table).where(
original_table.c.question_id == question_id
).where(
survey_table.c.auth_user_id == user_id
)
result = _return_sql(
connection,
connection.execute(where_stmt.order_by('submission_time asc')),
question.survey_id, auth_user_id, question_id)
tsr = [[r.submission_time.isoformat(),
_jsonify(connection, r[column_name], question_id)]
for r in result]
time_series_result = tsr
response = json_response(
_return_sql(connection, time_series_result, question.survey_id,
user_id, question_id))
response['query'] = 'time_series'
return response
def get_one(connection: Connection,
survey_id: str,
auth_user_id: str=None,
email: str=None) -> dict:
"""
Get a JSON representation of a survey. You must supply either the
auth_user_id or the email of the user.
:param connection: a SQLAlchemy Connection
:param survey_id: the UUID of the survey
:param auth_user_id: the UUID of the user
:param email: the e-mail address of the user
:return: the JSON representation.
"""
survey = survey_select(connection, survey_id, auth_user_id=auth_user_id,
email=email)
return json_response(_to_json(connection, survey))
def submit(connection: Connection, data: dict) -> dict:
"""
Batch submit to a survey.
:param connection: a SQLAlchemy connection
:param data: representation of the submission (from json.loads)
:return: the UUIDs of the submissions in the database
"""
c = connection
survey_id = data["survey_id"]
required_ids = {q.question_id for q in get_required(connection, survey_id)}
with c.begin():
create = partial(_create_submission, c, survey_id, required_ids)
submission_ids = list(map(create, data["submissions"]))
return json_response(submission_ids)
def get_question_stats(connection: Connection,
survey_id: str,
auth_user_id: str=None,
email: str=None) -> list:
"""
Returns a JSON-y list of dicts containing statistics about submissions
to the questions in the given survey.
:param connection: a SQLAlchemy Connection
:param survey_id: the UUID of the survey
:param auth_user_id: the UUID of the user
:param email: the e-mail address of the user
:return: a list containing the results
"""
user_id = _get_user_id(connection, auth_user_id, email)
questions = get_questions(connection, survey_id, user_id, None)
return json_response(
list(_get_question_stats(connection, questions, user_id, None)))
def sum(connection: Connection,
question_id: str,
auth_user_id: str=None,
email: str=None) -> dict:
"""
Get the sum of submissions to the specified question. You must
provide either an auth_user_id or e-mail address.
:param connection: a SQLAlchemy Connection
:param question_id: the UUID of the question
:param auth_user_id: the UUID of the user
:param email: the e-mail address of the user.
:return: a JSON dict containing the result
"""
result = _scalar(connection, question_id, sqlsum,
auth_user_id=auth_user_id,
email=email)
response = json_response(result)
response['query'] = 'sum'
return response
def stddev_samp(connection: Connection,
question_id: str,
auth_user_id: str=None,
email: str=None) -> dict:
"""
Get the sample standard deviation of submissions to the specified
question. You must provide either an auth_user_id or e-mail address.
:param connection: a SQLAlchemy Connection
:param question_id: the UUID of the question
:param auth_user_id: the UUID of the user
:param email: the e-mail address of the user.
:return: a JSON dict containing the result
"""
result = _scalar(connection, question_id, func.stddev_samp,
auth_user_id=auth_user_id,
email=email)
response = json_response(float(result))
response['query'] = 'stddev_samp'
return response
def get_all(connection: Connection,
email: str,
survey_id: str=None,
submitters: Iterator=None,
filters: list=None,
order_by: str=None,
direction: str='ASC',
limit: int=None) -> dict:
"""
Create a JSON representation of the submissions to a given survey and
email.
:param connection: a SQLAlchemy Connection
:param survey_id: the UUID of the survey
:param email: the user's e-mail address
: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: a JSON dict
"""
submissions = get_submissions_by_email(
connection,
email=email,
survey_id=survey_id,
submitters=submitters,
filters=filters,
order_by=order_by,
direction=direction,
limit=limit
)
# TODO: Check if this is a performance problem
result = [
get_one(
connection,
sub.submission_id,
email=email
) for sub in submissions
]
return json_response(result)
def max(connection: Connection,
question_id: str,
auth_user_id: str=None,
email: str=None) -> dict:
"""
Get the maximum value of submissions to the specified question. You must
provide either an auth_user_id or e-mail address.
:param connection: a SQLAlchemy Connection
:param question_id: the UUID of the question
:param auth_user_id: the UUID of the user
:param email: the e-mail address of the user.
:return: a JSON dict containing the result
"""
result = _scalar(connection, question_id, sqlmax,
auth_user_id=auth_user_id,
email=email,
allowable_types={'integer', 'decimal', 'date', 'time'})
response = json_response(_jsonify(connection, result, question_id))
response['query'] = 'max'
return response
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 mode(connection: Connection,
question_id: str,
auth_user_id: str=None,
email: str=None) -> dict:
"""
Get the mode of answers to the specified question in a list (since there
may be multiple). You must provide either an auth_user_id or e-mail
address.
:param connection: a SQLAlchemy Connection
:param question_id: the UUID of the question
:param auth_user_id: the UUID of the user
:param email: the e-mail address of the user
:return: a JSON dict containing the result
"""
bar_graph_top = bar_graph(connection, question_id, auth_user_id, email,
None, True)
result = bar_graph_top['result']
most_frequent = result[0]
frequency = most_frequent[1]
the_mode = takewhile(lambda bar: bar[1] == frequency, result)
response = json_response(list(val_count[0] for val_count in the_mode))
response['query'] = 'mode'
return response
def generate_token(connection: Connection, data: dict) -> dict:
"""
Generates a new API token for a user specified by e-mail address. You
can supply a duration in seconds.
:param connection: a SQLAlchemy Connection
:param data: the user's e-mail and an optional duration
:return: the generated token and the token's expiration time
"""
user = get_auth_user_by_email(connection, data['email'])
token = generate_api_token()
params = {'token': token,
'auth_user_id': user.auth_user_id}
if 'duration' in data:
duration = float(data['duration'])
if duration > 31536000:
raise TokenDurationTooLong(data['duration'])
params['expiration'] = timedelta(seconds=duration)
with connection.begin():
connection.execute(set_api_token(**params))
updated_user = get_auth_user_by_email(connection, data['email'])
return json_response(
{'token': token, 'expires_on': updated_user.expires_on.isoformat()})
def get(self, question_id: str):
response = [self._apply_aggregation(arg, question_id) for arg in
self.request.arguments]
self.write(json_response(response))