Esempio n. 1
0
def get_submissions_by_email(connection: Connection,
                             email: str,
                             survey_id: str=None,
                             submitters: Iterator=None,
                             filters: list=None,
                             order_by: str=None,
                             direction: str='ASC',
                             limit: int=None) -> ResultProxy:
    """
    Get submissions to a survey.

    :param connection: a SQLAlchemy Connection
    :param survey_id: the UUID of the survey
    :param email: the e-mail address of the user
    :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: an iterable of the submission records
    """

    table = submission_table.join(survey_table).join(auth_user_table)

    conds = [auth_user_table.c.email == email]

    if survey_id:
        conds.append(submission_table.c.survey_id == survey_id)

    if submitters is not None:
        conds.append(submission_table.c.submitter.in_(submitters))
    if filters is not None:
        filtered = set(_get_filtered_ids(connection, filters))
        conds.append(submission_table.c.submission_id.in_(filtered))
    if order_by is None:
        order_by = 'submission_time'
    return connection.execute(
        select(
            [submission_table]
        ).select_from(
            table
        ).where(
            and_(*conds)
        ).order_by(
            '{} {}'.format(order_by, direction)
        ).limit(
            limit
        )
    )
Esempio n. 2
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]
    )
Esempio n. 3
0
def submission_select(connection: Connection,
                      submission_id: str,
                      auth_user_id: str=None,
                      email: str=None) -> RowProxy:
    """
    Get a record from the submission table. You must supply either the
    auth_user_id or the email.

    :param connection: a SQLAlchemy Connection
    :param submission_id: the UUID of the submission
    :param auth_user_id: the UUID of the user
    :param email: the user's e-mail address
    :return: the corresponding records
    :raise SubmissionDoesNotExistError: if the submission_id is not in the
                                        table
    """

    table = submission_table.join(survey_table)
    conds = [submission_table.c.submission_id == submission_id]

    if auth_user_id is not None:
        if email is not None:
            raise TypeError('You cannot specify both auth_user_id and email')
        conds.append(survey_table.c.auth_user_id == auth_user_id)
    elif email is not None:
        table = table.join(auth_user_table)
        conds.append(auth_user_table.c.email == email)
    else:
        raise TypeError('You must specify either auth_user_id or email')

    submission = connection.execute(
        select([submission_table]).select_from(table).where(
            and_(*conds))).first()
    if submission is None:
        raise SubmissionDoesNotExistError(submission_id)
    return submission