Ejemplo n.º 1
0
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])
    })
Ejemplo n.º 2
0
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)
Ejemplo n.º 3
0
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
Ejemplo n.º 4
0
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'})
Ejemplo n.º 5
0
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
Ejemplo n.º 6
0
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)))
Ejemplo n.º 7
0
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])
Ejemplo n.º 8
0
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')
Ejemplo n.º 9
0
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
Ejemplo n.º 10
0
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))
Ejemplo n.º 11
0
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)
Ejemplo n.º 12
0
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)))
Ejemplo n.º 13
0
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
Ejemplo n.º 14
0
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
Ejemplo n.º 15
0
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)
Ejemplo n.º 16
0
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
Ejemplo n.º 17
0
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]
    )
Ejemplo n.º 18
0
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
Ejemplo n.º 19
0
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()})
Ejemplo n.º 20
0
 def get(self, question_id: str):
     response = [self._apply_aggregation(arg, question_id) for arg in
                 self.request.arguments]
     self.write(json_response(response))