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