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