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 _jsonify(connection: Connection, answer: object, question_id: str) -> object: """ This function returns a "nice" representation of an answer which can be serialized as JSON. :param connection: a SQLAlchemy Connection :param answer: a submitted value :param type_constraint_name: the UUID of the question :return: the nice representation """ type_constraint_name = question_select(connection, question_id).type_constraint_name if type_constraint_name in {'location', 'facility'}: geo_json = connection.execute(func.ST_AsGeoJSON(answer)).scalar() return json_decode(geo_json)['coordinates'] elif type_constraint_name in {'date', 'time'}: return maybe_isoformat(answer) elif type_constraint_name == 'decimal': return float(answer) elif type_constraint_name == 'multiple_choice': question_choice = question_choice_select(connection, answer) return question_choice.choice else: return answer
def _data_formatter(record: tuple) -> list: title, num, latest_sub, survey_id = record return [ title, str(num), # created_on.isoformat(), maybe_isoformat(latest_sub), survey_id ]