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(self):
email = self.get_email()
# Shorter variable names
auth_user = auth_user_table
survey = survey_table
submission = submission_table
submission_id = submission_table.c.submission_id
submission_time = submission_table.c.submission_time
result = self._get_records(
table=auth_user.join(survey).outerjoin(submission),
email=email,
selected=[
survey_table.c.survey_title,
count(submission_id).label('num_submissions'),
# survey_table.c.created_on,
sqlmax(submission_time).label('latest_submission'),
survey_table.c.survey_id,
],
text_filter_column=survey_table.c.survey_title,
default_sort_column_name='latest_submission',
total_records=get_number_of_surveys(self.db, email)
)
self.write(result)
def get_free_sequence_number(connection: Connection, survey_id: str) -> int:
"""
Return the highest existing sequence number + 1 (or 1 if there aren't
any) associated with the given survey_id.
:param connection: a SQLAlchemy Connection
:param survey_id: the UUID of the survey
:return: the free sequence number
"""
sequence_number = question_table.c.sequence_number
return connection.execute(select(
[coalesce(sqlmax(sequence_number, type_=Integer), 0)])).scalar() + 1
def get_previous_step_data(conn, station_ids, time_start, hourly=False):
"""
Try to acquire the last timestamp before the selected dates,
so that the step check can pass for the first entry in the day.
"""
obs = conn.get_table('observation')
q = select([sqlmax(obs.c.time)])
q = q.where(
obs.c.station_id.in_(station_ids)).where(obs.c.time < time_start)
if hourly:
q = q.where(obs.c.time == func.date_trunc('hour', obs.c.time))
with conn.trans() as wht:
result = wht.get_data(q)
date = result[0][0]
if date is None or time_start.replace(
tzinfo=utc) - date > GAP_TOLERANCE_DEFAULT:
return None
return date