Exemplo n.º 1
0
def collaborator_users(account_id=None, user_id=None, video_id=None):
    assert account_id or user_id or video_id
    if user_id:
        # Select all users from accounts than own the videos this user is
        # collaborating on: user -> collab -> video -> account -> user.
        username = AccountUser.query.filter_by(id=user_id).value('username')
        query = VideoCollaborator.query.join(Video).join(
            AccountUser,
            (AccountUser.account_id == Video.account_id)
        ).filter(
            (user_id == VideoCollaborator.account_user_id) |
            (func.lower(username) == func.lower(VideoCollaborator.email))
        )
    else:
        if account_id:
            # Select collaborators on all videos owned by this account:
            # account -> video -> collab (-> user)
            query = VideoCollaborator.query.join(
                Video,
                (Video.id == VideoCollaborator.video_id) &
                (Video.account_id == account_id)
            )
        else:
            # Select collaborators for a specific video:
            # video -> collab (-> user)
            query = VideoCollaborator.query.filter_by(video_id=video_id)

        query = query.outerjoin(
            AccountUser,
            ((AccountUser.id == VideoCollaborator.account_user_id) |
             (func.lower(AccountUser.username) == func.lower(VideoCollaborator.email)))
        )

    return query.with_entities(VideoCollaborator, AccountUser)
Exemplo n.º 2
0
def users(user_id=None):
    """Manage users of PyBossa"""
    try:
        form = SearchForm(request.form)
        users = db.session.query(model.User)\
                  .filter(model.User.admin == True)\
                  .filter(model.User.id != current_user.id)\
                  .all()

        if request.method == 'POST' and form.user.data:
            query = '%' + form.user.data.lower() + '%'
            found = db.session.query(model.User)\
                      .filter(or_(func.lower(model.User.name).like(query),
                                  func.lower(model.User.fullname).like(query)))\
                      .filter(model.User.id != current_user.id)\
                      .all()
            require.user.update(found)
            if not found:
                flash("<strong>Ooops!</strong> We didn't find a user "
                      "matching your query: <strong>%s</strong>" % form.user.data)
            return render_template('/admin/users.html', found=found, users=users,
                                   title=gettext("Manage Admin Users"),
                                   form=form)

        return render_template('/admin/users.html', found=[], users=users,
                               title=gettext("Manage Admin Users"), form=form)
    except HTTPException:
        return abort(403)
    except Exception as e:
        current_app.logger.error(e)
        return abort(500)
Exemplo n.º 3
0
def queue_forget(title=None, imdb_id=None, tmdb_id=None, session=None, movie_id=None, queue_name='default'):
    """
    Forget movie download  from the queue.

    :param title: Movie title
    :param imdb_id: Imdb id
    :param tmdb_id: Tmdb id
    :param session: Optional session to use, new session used otherwise
    :param queue_name: Name of movie queue to get items from
    :return: Title of forgotten movie
    :raises QueueError: If queued item could not be found with given arguments
    """
    log.debug('queue_forget - title=%s, imdb_id=%s, tmdb_id=%s, movie_id=%s, queue_name=%s', title, imdb_id, tmdb_id,
              movie_id, queue_name)
    query = session.query(QueuedMovie).filter(func.lower(QueuedMovie.queue_name) == queue_name.lower())
    if imdb_id:
        query = query.filter(QueuedMovie.imdb_id == imdb_id)
    elif tmdb_id:
        query = query.filter(QueuedMovie.tmdb_id == tmdb_id)
    elif title:
        query = query.filter(func.lower(QueuedMovie.title) == func.lower(title))
    elif movie_id:
        query = query.filter(QueuedMovie.id == movie_id)
    try:
        item = query.one()
        title = item.title
        if not item.downloaded:
            raise QueueError(message=('%s is not marked as downloaded' % title), errno=1)
        item.downloaded = None
        return item.to_dict()
    except NoResultFound as e:
        raise QueueError(message=('title=%s, imdb_id=%s, tmdb_id=%s, movie_id=%s, queue_name=%s not found in queue' %
                                  (title, imdb_id, tmdb_id, movie_id, queue_name)), errno=2)
def create_user_and_sample_Items(name, phone_num):
    customer = Customer(name, phone_num)
    cookie = Item(name + "_Cookie", 1.50)
    muffin = Item(name + "_Muffin", 2.00, "Carby stuff")
    latte = Item(name + "_Latte", 4.35, "Steamed milk over expresso")
    mocha = Item(name + "_Mocha", 5.00,
                 "Steamed milk over expresso and chocolate syrup")

    session.add(customer)
    session.add(cookie)
    session.add(muffin)
    session.add(latte)
    session.add(mocha)
    session.commit()

    result = {"customer": customer}
    item_names = ['cookie', 'muffin', 'latte', 'mocha']
    for name in item_names:

        item = session.query(Item).\
            filter(func.lower(Item.name).like('%' + name + '%')).\
            filter(func.lower(Item.name).like('%' + customer_name + '%')).\
            scalar()

        result[name] = item

    return result
Exemplo n.º 5
0
def select(q, **kwargs):
    if kwargs.get('ids'):
        print("ids == " + str(kwargs.get('ids')))
        q = q.filter(Flight.id.in_(kwargs.get('ids')))

    if kwargs.get('date_from'):
        try:
            date_from = datetime.strptime(kwargs.get('date_from'), "%Y-%m-%d")
            q = q.filter(Flight.takeoff_time >= date_from)
            print("takeoff_time >= " + str(date_from))
        except:
            print("Cannot parse date-from")
            return None

    if kwargs.get('date_to'):
        try:
            date_to = datetime.strptime(kwargs.get('date_to'), "%Y-%m-%d")
            q = q.filter(Flight.takeoff_time < date_to)
            print("takeoff_time < " + str(date_to))
        except:
            print("Cannot parse date-to")
            return None

    if kwargs.get('uploaded_from'):
        try:
            uploaded_from = datetime.strptime(kwargs.get('uploaded_from'), "%Y-%m-%d")
            q = q.filter(Flight.time_created >= uploaded_from)
            print("time_created >= " + str(uploaded_from))
        except:
            print("Cannot parse uploaded-from")
            return None

    if kwargs.get('uploaded_to'):
        try:
            uploaded_to = datetime.strptime(kwargs.get('uploaded_to'), "%Y-%m-%d")
            q = q.filter(Flight.time_created < uploaded_to)
            print("time_created < " + str(uploaded_to))
        except:
            print("Cannot parse uploaded-to")
            return None

    if not kwargs.get('private'):
        print("privacy_level == PUBLIC")
        q = q.filter(Flight.privacy_level == Flight.PrivacyLevel.PUBLIC)

    if kwargs.get('country_code'):
        country_code = kwargs.get('country_code')

        q = q.join(Flight.takeoff_airport)
        q = q.filter(func.lower(Airport.country_code) == func.lower(country_code))
        print("takeoff_airport country code: " + country_code)

    if kwargs.get('airport_name'):
        airport_name = kwargs.get('airport_name')

        q = q.join(Flight.takeoff_airport)
        q = q.filter(func.lower(Airport.name) == func.lower(airport_name))
        print("takeoff_airport name: " + airport_name)

    return q
Exemplo n.º 6
0
def get_monkey_by_email(email):
    """Get monkey by email."""
    try:
        return Monkey.query.filter(
            func.lower(Monkey.email) == func.lower(email)).first()
    except Exception:
        return None
Exemplo n.º 7
0
def queue_del(title=None, imdb_id=None, tmdb_id=None, session=None, movie_id=None, queue_name='default'):
    """
    Delete the given item from the queue.

    :param title: Movie title
    :param imdb_id: Imdb id
    :param tmdb_id: Tmdb id
    :param session: Optional session to use, new session used otherwise
    :param queue_name: Name of movie queue to get items from
    :return: Title of forgotten movie
    :raises QueueError: If queued item could not be found with given arguments
    """
    log.debug('queue_del - title=%s, imdb_id=%s, tmdb_id=%s, movie_id=%s, queue_name=%s',
              title, imdb_id, tmdb_id, movie_id, queue_name)
    query = session.query(QueuedMovie).filter(func.lower(QueuedMovie.queue_name) == queue_name.lower())
    if imdb_id:
        query = query.filter(QueuedMovie.imdb_id == imdb_id)
    elif tmdb_id:
        query = query.filter(QueuedMovie.tmdb_id == tmdb_id)
    elif title:
        query = query.filter(func.lower(QueuedMovie.title) == func.lower(title))
    elif movie_id:
        query = query.filter(QueuedMovie.id == movie_id)
    try:
        item = query.one()
        title = item.title
        session.delete(item)
        return title
    except NoResultFound as e:
        raise QueueError(
            'title=%s, imdb_id=%s, tmdb_id=%s, movie_id=%s not found in queue %s' % (
                title, imdb_id, tmdb_id, movie_id, queue_name))
    except MultipleResultsFound:
        raise QueueError('title=%s, imdb_id=%s, tmdb_id=%s, movie_id=%s matches multiple results in queue %s' %
                         (title, imdb_id, tmdb_id, movie_id, queue_name))
Exemplo n.º 8
0
def group(id):
    group = Group.query.filter_by(id=id).first_or_404()
    if not current_user in group.users:
        abort(404)

    form = None
    if group.changeable:
        form = GroupAddMemberForm()
        if current_user == group.admin and form.validate_on_submit():
            user = User.query.filter(func.lower(User.username) == func.lower(form.member.data)).first()

            if not user:
                flash(gettext("User not found."), "error")
                return redirect(url_for("group", id=group.id))

            if user in group.users:
                flash(gettext("User is already a member of the group."), "info")
                return redirect(url_for("group", id=group.id))

            group.users.append(user)
            db.session.commit()
            flash(gettext("The user was added to the group."), "success")
            return redirect(url_for("group", id=group.id))

    return render_template("user/group.jade", group=group, form=form)
Exemplo n.º 9
0
def _by_name(session, gene_id, chromosome_ids):
    gene_db = (session
               .query(Gene)
               .filter(func.lower(Gene.name) == func.lower(gene_id))
               .filter(Gene.chromosome_id.in_(chromosome_ids))
               .all())
    return gene_db, False
Exemplo n.º 10
0
 def createGroup(self, name, description='', visible=True, automatic_approval=False):
     
     created = False
     
     try:
         session = self.Session()
         
         group = session.query(ESGFGroup).filter( func.lower(ESGFGroup.name) == func.lower(name) ).one()
         print "Group with name=%s already exists" % group.name
         created = False
         
         return group
         
     except NoResultFound:
         group = ESGFGroup(name=name, description=description, visible=visible, automatic_approval=automatic_approval)
         session.add(group)
         session.commit()
         created = True
         
         return group
         
     finally:
         session.close()
     
     return created
Exemplo n.º 11
0
    def login(self):
        request = self.request
        params = request.params
        if self.logged_in:
            return HTTPForbidden()
        login_url = request.route_url('login')
        referrer = request.referrer
        if referrer == login_url:
            referrer = '/'
        came_from = request.params.get('came_from', referrer)
        if 'form.submitted' in params:
            user = DBSession.query(User).filter(func.lower(User.email) == func.lower(params['email'])).first()

            if user is not None:
                if user.confirmed is False:
                    request.session.flash('Account must be activated before logging in.',
                                          queue='notifications')
                elif check_password(params['email'], params['password']):
                    userid = user.id
                    print("userid: " + str(userid))
                    print("email: " + str(user.email))
                    headers = remember(request, str(userid))
                    self.gibs['current_user'] = user.email
                    request.session.flash('Successfully logged in as ' + str(user.email),
                                        queue='successes')
                    return HTTPFound(location=came_from,
                                    headers=headers)

            request.session.flash('Email or password was incorrect, or user does not exist.',
                                  queue='warnings')
        return {'gibs': self.gibs,
                'came_from': came_from,
                }
Exemplo n.º 12
0
def get_user_by_name_or_email(name_or_email):
    return db.session \
        .query(db.User) \
        .filter(
            (func.lower(db.User.name) == func.lower(name_or_email))
            | (func.lower(db.User.email) == func.lower(name_or_email))) \
        .first()
def event_contains(value, query):
    value = value.lower()
    q = query.filter(or_(
        func.lower(Event.name).contains(value),
        func.lower(Event.description).contains(value)
    ))
    return q
Exemplo n.º 14
0
 def find_user_by_twitter(cls, twitter):
     """ Finds user by EMAIL, making sure to look in all lowercase
         Returns Query object not user! Then can use first_or_404.
     """
     user_query = cls.query.filter(
                 func.lower(cls.twitter_handle) == func.lower(twitter))
     return user_query
Exemplo n.º 15
0
    def _search_video(self, query, location, size, start=0):
        videos = Video.query.filter_by(deleted=False, status="published").outerjoin(
            VideoLocaleMeta, (VideoLocaleMeta.video_id == Video.id) & (VideoLocaleMeta.locale == "")
        )

        owner_filter = VIDEO_OWNER_USER_QUERY_RE.match(query.strip())
        if owner_filter:
            videos = videos.join(
                AccountUser, (AccountUser.account_id == Video.account_id) & (AccountUser.id == owner_filter.group(1))
            ).distinct()
            query = ""

        collab_filter = VIDEO_COLLABORATOR_QUERY_RE.match(query.strip())
        if collab_filter:
            videos = (
                videos.join(VideoCollaborator, VideoCollaborator.video_id == Video.id)
                .join(
                    AccountUser,
                    (func.lower(AccountUser.username) == func.lower(VideoCollaborator.email))
                    & (AccountUser.id == collab_filter.group(1)),
                )
                .distinct()
            )
            query = ""

        videos, total = self._db_match(videos, size, start, query, Video.search_vector)
        items = [self._video_item(v) for v in videos]
        return dict(items=items, total=total)
Exemplo n.º 16
0
 def find_user_by_name(cls, name):
     """ Finds user by name, making sure to look in all lowercase
         Returns Query object not user! Then can use first_or_404.
     """
     user_query = cls.query.filter(
                 func.lower(cls.name) == func.lower(name))
     return user_query
Exemplo n.º 17
0
 def find_user_by_email(cls, email):
     """ Finds user by EMAIL, making sure to look in all lowercase
         Returns Query object not user! Then can use first_or_404.
     """
     user_query = cls.query.filter(
                 func.lower(cls.email) == func.lower(email))
     return user_query
Exemplo n.º 18
0
 def get_active_subscriptions_for_a_type(cls, ttype):
     return cls.query.filter(
         and_(
             cls.active,
             func.lower(cls.type) == func.lower(ttype)
         )
     ).all()
Exemplo n.º 19
0
 def get_templates_for_uri_and_type(cls, uri, ttype):
     return cls.query.filter(
         and_(
             func.lower(cls.uri) == func.lower(uri),
             func.lower(cls.type) == func.lower(ttype)
         )
     ).all()
Exemplo n.º 20
0
    def get_badges_from_tags(self, tags, match_all=False):
        """
        Return badges matching tags.

        :type tags: list
        :param tags: A list of string badge tags

        :type match_all: boolean
        :param match_all: Returned badges must have all tags in list
        """

        badges = list()

        if match_all:
            # Return badges matching all tags
            # ... by doing argument-expansion on a list comprehension
            badges.extend(self.session.query(Badge).filter(and_(*[
                func.lower(Badge.tags).contains(str(tag + ',').lower())
                for tag in tags])))
        else:
            # Return badges matching any of the tags
            for tag in tags:
                badges.extend(self.session.query(Badge).filter(
                              func.lower(Badge.tags).contains(
                              str(tag + ',').lower())).all())

        # Eliminate any duplicates.
        unique_badges = list()
        for badge in badges:
            if badge not in unique_badges:
                unique_badges.append(badge)

        return unique_badges
Exemplo n.º 21
0
def get_profile_from_id(id, id_type="url_slug", show_secrets=False, include_products=True, include_product_relationships=True):
    if include_products:
        if include_product_relationships:
            query_base = Profile.query
        else:
            query_base = db.session.query(Profile).options(orm.noload('*'), orm.subqueryload(Profile.products))
    else:
        query_base = db.session.query(Profile).options(orm.noload('*'))

    if id_type == "id":
        try:
           profile = query_base.get(id)
        except DataError:  # id has to be an int
            logger.debug(u"get_profile_from_id no profile found from profile id {id}".format(
                id=id))
            profile = None

    elif id_type == "email":
        profile = query_base.filter(func.lower(Profile.email) == func.lower(id)).first()

    elif id_type == "url_slug":
        profile = query_base.filter(func.lower(Profile.url_slug) == func.lower(id)).first()

    if not show_secrets:
        profile = hide_profile_secrets(profile)

    return profile
Exemplo n.º 22
0
    def person_exists(self, email=None, id=None, nickname=None):
        """
        Check if a Person with this email is stored in the database

        :type email: str
        :param email: An email address to search the database for

        :type id: str
        :param id: A user id to search for.

        :type nickname: str
        :param nickname: A nickname to search for.
        """

        query = self.session.query(Person)
        if email:
            return query.filter(
                    func.lower(Person.email) == func.lower(email)).count() != 0
        elif id:
            return query.filter_by(id=id).count() != 0
        elif nickname:
            return query.filter(
                    func.lower(Person.nickname) == func.lower(
                             nickname)).count() != 0
        else:
            return False
Exemplo n.º 23
0
def modifyNotesEntry(session, teamnum, comp, old_notes, new_notes, notestag):
    notes = session.query(NotesEntry).filter(NotesEntry.team==teamnum).\
                                      filter(func.lower(NotesEntry.competition)==func.lower(comp)).\
                                      filter(NotesEntry.data==old_notes)
    if notes:
        notes.data = new_notes
        notes.tag = notestag                                  
Exemplo n.º 24
0
def get_quadbounds(location):
    try:
        quads = DBSession.query(
                                Quadrant.latitude,
                                Quadrant.longitude,
                                Quadrant.name,
                            ).filter(
                                func.lower(Quadrant.location)==
                                    func.lower(location)
                            ).all()
        center = DBSession.query(
                                Location.latitude,
                                Location.longitude,
                            ).filter(
                                func.lower(location)==
                                    func.lower(Location.name),
                            ).first()
    except DBAPIError as e:
        print e
        return []
    
    bounds = []

    for i in quads:
        if i[1] <= center[1]:
            bounds.append([i[0], i[1], center[0], center[1], i[2]])
        else:
            bounds.append([center[0], center[1], i[0], i[1], i[2]])

    return bounds
Exemplo n.º 25
0
def recommended_books_by_publisher(books_read_by_user, recommended_books):
    read_books_isbn_list = [book.book_isbn for book in books_read_by_user]
    # Get publishers and the number of books read by the user of the publisher
    # in descending order
    publisher_count = db.session.query(
        func.lower(Books.publisher),
        func.count(Books.publisher)
    ).filter(
        Books.isbn.in_(read_books_isbn_list)
    ).group_by(
        func.lower(Books.publisher)
    ).order_by(
        func.count(Books.publisher).desc()
    ).all()
    publisher_count_dict = {str(publisher): count for (
        publisher, count) in publisher_count}
    unread_books_by_same_publishers = db.session.query(
        Books.isbn,
        func.lower(Books.publisher)
    ).filter(
        and_(
            func.lower(Books.publisher).in_([x[0] for x in publisher_count]),
            ~Books.isbn.in_(read_books_isbn_list)
        )
    ).all()
    # Gets the books of the above publishers which are not read by the user
    for unread_book in unread_books_by_same_publishers:
        isbn = unread_book[0]
        publisher = unread_book[1]
        weight = g.user.publisher_weight * publisher_count_dict[publisher]
        if isbn not in recommended_books:
            recommended_books.update({isbn: weight})
        else:
            recommended_books[isbn] += weight
Exemplo n.º 26
0
def get_rainData(start_date, end_date, location):
    data = {}
    try:
        readings = DBSession.query(
                                Gauge.quadrant,
                                RainReading.date,
                                func.avg(RainReading.amount),
                            ).filter(
                                Gauge.gauge_id==RainReading.gauge_id,
                                func.lower(Gauge.location)==func.lower(location),
                                func.lower(RainReading.location)==
                                    func.lower(location),
                                RainReading.date.between(start_date, end_date),
                            ).group_by(
                                Gauge.quadrant,
                                RainReading.date,
                            ).all()
    except DBAPIError as e:
        print e
        return {}

    for i in readings:
        new_item = [str(i[1]), int(i[2])]
        if i[0] in data:
            data[i[0]].append(new_item)
        else:
            data[i[0]] = [new_item]

    return data
Exemplo n.º 27
0
def get_recentTrapCount(location, date):
    try:
        dates = DBSession.query(
                    func.max(Trap.date).label('date'),
                    Region.quadrant.label('quad'),
                    Region.name.label('region'),
                ).filter(
                    Region.name==Trap.region,
                    Trap.date <= date,
                    func.lower(Region.location)==func.lower(location),
                ).group_by(Trap.region).subquery()
        
        counts = DBSession.query(
                    dates.c.date,
                    dates.c.quad,
                    Trap.count,
                ).filter(
                    Region.name==Trap.region,
                    Trap.date==dates.c.date,
                    Trap.region==dates.c.region,
                ).all()
    except DBAPIError as e:
        print e
        return []

    counts = [[str(i[0]), str(i[1]), int(i[2])] for i in counts]

    return counts
Exemplo n.º 28
0
 def get_asset_type_model_user_name_num(self, asset_type,
                                        model_id, user_name, store_state):
     return self.session.query(AssetConsumeInfoModel).filter(
         and_(AssetConsumeInfoModel.asset_type_id == asset_type,
              AssetConsumeInfoModel.model_id == model_id,
              AssetConsumeInfoModel.store_state_id == store_state,
              func.lower(AssetConsumeInfoModel.user_name) == func.lower(user_name))).first()
Exemplo n.º 29
0
 def search_sample(self, sample_code):
     """ return a single Sample from the current batch with sample_code """
     try:
         return self.samples.filter( func.lower(Sample.code) == func.lower(sample_code),
             Sample.batch_id == self.id ).one()
     except NoResultFound:
         return None
Exemplo n.º 30
0
    def get_person(self, person_email=None, id=None, nickname=None):
        """
        Convenience function to retrieve a person object from an email,
        id, or nickname.

        :type person_email: str
        :param person_email: The email address of a Person in the database

        :type id: str
        :param id: The id of a Person in the database

        :type nickname: str
        :param nickname: The nickname of a Person in the database
        """

        query = self.session.query(Person)

        if person_email and self.person_exists(email=person_email):
            return query.filter(
                    func.lower(Person.email) == \
                            func.lower(person_email)).one()
        elif id and self.person_exists(id=id):
            return query.filter_by(id=id).one()
        elif nickname and self.person_exists(nickname=nickname):
            return query.filter(
                    func.lower(Person.nickname) == \
                            func.lower(nickname)).one()
        else:
            return None
Exemplo n.º 31
0
 def get_by_email(cls, email):
     return User.query.filter(
         func.lower(User.email) == func.lower(email)).one_or_none()
Exemplo n.º 32
0
 def lowercase_name(cls):
     "The SQLAlchemy expression for the property above."
     return func.lower(cls.name)
Exemplo n.º 33
0
def query_definition(term):
    ''' Query the definition for a term from the database
    '''
    return Definition.query.filter(
        func.lower(Definition.term) == func.lower(term)).first()
Exemplo n.º 34
0
def county_cases(state):
    result = db.session.query(County).join(State).filter(
        func.lower(State.name) == func.lower(state)).all()
    print(result)
    return str(result)
Exemplo n.º 35
0
def airport_filter_min_helper(num_cases, airport_entry):
    name = airport_entry["properties"]["name"]
    airport = db.session.query(Airport).filter(
        func.lower(name) == func.lower(Airport.code)).first()
    return airport.county.new_cases >= int(num_cases)
Exemplo n.º 36
0
    def get(self):
        try:
            filters = []

            # Validate the request
            if len(request.args) == 0:
                raise InvalidInputError(
                    message='No query parameters were specified in the request'
                )

            nr_num_query_str = get_query_param_str('nrNum')
            email_address_query_str = get_query_param_str('emailAddress')
            phone_number_query_str = get_query_param_str('phoneNumber')

            if not nr_num_query_str:
                raise InvalidInputError(message='An nrNum must be provided')
            else:
                if not email_address_query_str and not phone_number_query_str:
                    raise InvalidInputError(
                        message=
                        'Either an emailAddress or phoneNumber must be provided'
                    )

            # Continue
            nr_num = parse_nr_num(nr_num_query_str)
            email_address = email_address_query_str

            phone_number = get_query_param_str('phoneNumber')
            # Filter on addresses
            # address_line = get_query_param_str('addrLine1')

            if nr_num:
                filters.append(func.lower(Request.nrNum) == nr_num.lower())
            if phone_number:
                strip_phone_number_chars_regex = r"[^0-9]"
                filters.append(
                    Request.applicants.any(
                        func.regexp_replace(
                            Applicant.phoneNumber,
                            strip_phone_number_chars_regex, '', 'g').contains(
                                re.sub(strip_phone_number_chars_regex, '',
                                       phone_number))))

            if email_address:
                filters.append(
                    Request.applicants.any(
                        func.lower(Applicant.emailAddress).startswith(
                            email_address.lower())))
            '''
            Filter on addresses
            if address_line:
                filters.append(
                    Request.applicants.any(
                        func.lower(Applicant.addrLine1).startswith(address_line.lower())
                    )
                )
            '''

            criteria = RequestQueryCriteria(nr_num=nr_num, filters=filters)

            results = Request.find_by_criteria(criteria)

            if not results:
                results = []

        except InvalidInputError as err:
            return handle_exception(err, err.message, 400)
        except Exception as err:
            return handle_exception(err,
                                    'Error retrieving the NR from the db.',
                                    500)

        if nr_num and len(results) == 1:
            nr_model = results[0]

            if nr_model.requestTypeCd and (not nr_model.entity_type_cd
                                           or not nr_model.request_action_cd):
                # If requestTypeCd is set, but a request_entity (entity_type_cd) and a request_action (request_action_cd)
                # are not, use get_mapped_entity_and_action_code to map the values from the requestTypeCd
                entity_type, request_action = get_mapped_entity_and_action_code(
                    nr_model.requestTypeCd)
                nr_model.entity_type_cd = entity_type
                nr_model.request_action_cd = request_action

            response_data = nr_model.json()
            # Add the list of valid Name Request actions for the given state to the response
            response_data['actions'] = get_nr_state_actions(
                results[0].stateCd, results[0])
            return jsonify(response_data), 200
        elif len(results) > 0:
            # We won't add the list of valid Name Request actions for the given state to the response if we're sending back a list
            # If the user / client accessing this data needs the Name Request actions, GET the individual record using NameRequest.get
            # This method, NameRequests.get is for Existing NR Search
            return jsonify(list(map(lambda result: result.json(),
                                    results))), 200

        # We won't add the list of valid Name Request actions for the given state to the response if we're sending back a list
        # If the user / client accessing this data needs the Name Request actions, GET the individual record using NameRequest.get
        # This method, NameRequests.get is for Existing NR Search
        return jsonify(results), 200
Exemplo n.º 37
0
 def normalized_name(cls):
     return func.replace(func.replace(func.lower(cls.name), '_', ''), ' ', '')
Exemplo n.º 38
0
 async def get_by_email(self, email: str) -> Optional[UD]:
     query = self.users.select().where(
         func.lower(self.users.c.email) == func.lower(email))
     user = await self.database.fetch_one(query)
     return await self._make_user(user) if user else None
Exemplo n.º 39
0
def admin_booze():
    form = BoozeForm(request.form)
    boozes = Booze.query.order_by(asc(func.lower(Booze.name)))
    return render_template("admin/booze", options=app.options, boozes=boozes, form=form, title="Booze")
Exemplo n.º 40
0
    def get_by_bar_training_token(cls, code):
        uid = verify_bar_training_token(app.config["SECRET_KEY"], code)
        if uid is None:
            raise ValueError("Invalid token")

        return User.query.filter_by(id=uid).one()

    @property
    def is_cfp_accepted(self):
        for proposal in self.proposals:
            if proposal.state in {"accepted", "finished"}:
                return True
        return False


Index("ix_user_email_lower", func.lower(User.email), unique=True)
Index(
    "ix_user_email_tsearch",
    text("to_tsvector('simple', replace(email, '@', ' '))"),
    postgresql_using="gin",
)
Index("ix_user_name_tsearch",
      text("to_tsvector('simple', name)"),
      postgresql_using="gin")


class UserDiversity(db.Model):
    __tablename__ = "diversity"
    user_id = db.Column(db.Integer,
                        db.ForeignKey("user.id"),
                        nullable=False,
Exemplo n.º 41
0
def search_datasets(dataset_id=None,
                    dataset_name=None,
                    collection_name=None,
                    data_type=None,
                    dimension=None,
                    unit=None,
                    scenario_id=None,
                    metadata_name=None,
                    metadata_val=None,
                    attr_id=None,
                    type_id=None,
                    unconnected=None,
                    inc_metadata='N',
                    inc_val='N',
                    page_start=0,
                    page_size=2000,
                    **kwargs):
    """
        Get multiple datasets, based on several
        filters. If all filters are set to None, all
        datasets in the DB (that the user is allowe to see)
        will be returned.
    """

    log.info("Searching datasets: \ndatset_id: %s,\n"
             "datset_name: %s,\n"
             "collection_name: %s,\n"
             "data_type: %s,\n"
             "dimension: %s,\n"
             "unit: %s,\n"
             "scenario_id: %s,\n"
             "metadata_name: %s,\n"
             "metadata_val: %s,\n"
             "attr_id: %s,\n"
             "type_id: %s,\n"
             "unconnected: %s,\n"
             "inc_metadata: %s,\n"
             "inc_val: %s,\n"
             "page_start: %s,\n"
             "page_size: %s" %
             (dataset_id, dataset_name, collection_name, data_type, dimension,
              unit, scenario_id, metadata_name, metadata_val, attr_id, type_id,
              unconnected, inc_metadata, inc_val, page_start, page_size))

    if page_size is None:
        page_size = config.get('SEARCH', 'page_size', 2000)

    user_id = int(kwargs.get('user_id'))

    dataset_qry = db.DBSession.query(
        Dataset.dataset_id, Dataset.data_type, Dataset.data_units,
        Dataset.data_dimen, Dataset.data_name, Dataset.hidden, Dataset.cr_date,
        Dataset.created_by, DatasetOwner.user_id,
        null().label('metadata'), Dataset.start_time, Dataset.frequency,
        Dataset.value)

    #Dataset ID is unique, so there's no point using the other filters.
    #Only use other filters if the datset ID is not specified.
    if dataset_id is not None:
        dataset_qry = dataset_qry.filter(Dataset.dataset_id == dataset_id)

    else:
        if dataset_name is not None:
            dataset_qry = dataset_qry.filter(
                func.lower(Dataset.data_name).like("%%%s%%" %
                                                   dataset_name.lower()))
        if collection_name is not None:
            dc = aliased(DatasetCollection)
            dci = aliased(DatasetCollectionItem)
            dataset_qry = dataset_qry.join(
                dc,
                func.lower(dc.collection_name).like(
                    "%%%s%%" % collection_name.lower())).join(
                        dci,
                        and_(dci.collection_id == dc.collection_id,
                             dci.dataset_id == Dataset.dataset_id))

        if data_type is not None:
            dataset_qry = dataset_qry.filter(
                func.lower(Dataset.data_type) == data_type.lower())

        #null is a valid dimension, so we need a way for the searcher
        #to specify that they want to search for datasets with a null dimension
        #rather than ignoring the dimension in the filter. We use 'null' to do this.
        if dimension is not None:
            dimension = dimension.lower()
            if dimension == 'null':
                dimension = None
            if dimension is not None:
                dataset_qry = dataset_qry.filter(
                    func.lower(Dataset.data_dimen) == dimension)
            else:
                dataset_qry = dataset_qry.filter(
                    Dataset.data_dimen == dimension)

        #null is a valid unit, so we need a way for the searcher
        #to specify that they want to search for datasets with a null unit
        #rather than ignoring the unit. We use 'null' to do this.
        if unit is not None:
            unit = unit.lower()
            if unit == 'null':
                unit = None
            if unit is not None:
                dataset_qry = dataset_qry.filter(
                    func.lower(Dataset.data_units) == unit)
            else:
                dataset_qry = dataset_qry.filter(Dataset.data_units == unit)

        if scenario_id is not None:
            dataset_qry = dataset_qry.join(
                ResourceScenario,
                and_(ResourceScenario.dataset_id == Dataset.dataset_id,
                     ResourceScenario.scenario_id == scenario_id))

        if attr_id is not None:
            dataset_qry = dataset_qry.join(
                ResourceScenario,
                ResourceScenario.dataset_id == Dataset.dataset_id).join(
                    ResourceAttr,
                    and_(
                        ResourceAttr.resource_attr_id ==
                        ResourceScenario.resource_attr_id,
                        ResourceAttr.attr_id == attr_id))

        if type_id is not None:
            dataset_qry = dataset_qry.join(
                ResourceScenario,
                ResourceScenario.dataset_id == Dataset.dataset_id).join(
                    ResourceAttr, ResourceAttr.resource_attr_id ==
                    ResourceScenario.resource_attr_id).join(
                        TypeAttr,
                        and_(TypeAttr.attr_id == ResourceAttr.attr_id,
                             TypeAttr.type_id == type_id))

        if unconnected == 'Y':
            stmt = db.DBSession.query(
                distinct(ResourceScenario.dataset_id).label('dataset_id'),
                literal_column("0").label('col')).subquery()
            dataset_qry = dataset_qry.outerjoin(
                stmt, stmt.c.dataset_id == Dataset.dataset_id)
            dataset_qry = dataset_qry.filter(stmt.c.col == None)
        elif unconnected == 'N':
            #The dataset has to be connected to something
            stmt = db.DBSession.query(
                distinct(ResourceScenario.dataset_id).label('dataset_id'),
                literal_column("0").label('col')).subquery()
            dataset_qry = dataset_qry.join(
                stmt, stmt.c.dataset_id == Dataset.dataset_id)
        if metadata_name is not None and metadata_val is not None:
            dataset_qry = dataset_qry.join(
                Metadata,
                and_(
                    Metadata.dataset_id == Dataset.dataset_id,
                    func.lower(Metadata.metadata_name).like(
                        "%%%s%%" % metadata_name.lower()),
                    func.lower(Metadata.metadata_val).like(
                        "%%%s%%" % metadata_val.lower())))
        elif metadata_name is not None and metadata_val is None:
            dataset_qry = dataset_qry.join(
                Metadata,
                and_(
                    Metadata.dataset_id == Dataset.dataset_id,
                    func.lower(Metadata.metadata_name).like(
                        "%%%s%%" % metadata_name.lower())))
        elif metadata_name is None and metadata_val is not None:
            dataset_qry = dataset_qry.join(
                Metadata,
                and_(
                    Metadata.dataset_id == Dataset.dataset_id,
                    func.lower(Metadata.metadata_val).like(
                        "%%%s%%" % metadata_val.lower())))

    #All datasets must be joined on dataset owner so only datasets that the
    #user can see are retrieved.
    dataset_qry = dataset_qry.outerjoin(
        DatasetOwner,
        and_(DatasetOwner.dataset_id == Dataset.dataset_id,
             DatasetOwner.user_id == user_id))

    dataset_qry = dataset_qry.filter(
        or_(Dataset.hidden == 'N',
            and_(DatasetOwner.user_id is not None, Dataset.hidden == 'Y')))

    log.info(str(dataset_qry))

    datasets = dataset_qry.all()

    log.info("Retrieved %s datasets", len(datasets))

    #page the datasets:
    if page_start + page_size > len(datasets):
        page_end = None
    else:
        page_end = page_start + page_size

    datasets = datasets[page_start:page_end]

    log.info("Datasets paged from result %s to %s", page_start, page_end)

    datasets_to_return = []
    for dataset_row in datasets:

        dataset_dict = dataset_row._asdict()

        if inc_val == 'N':
            dataset_dict['value'] = None
        else:
            #convert the value row into a string as it is returned as a binary
            if dataset_row.value is not None:
                dataset_dict['value'] = str(dataset_row.value)

        if inc_metadata == 'Y':
            metadata = db.DBSession.query(Metadata).filter(
                Metadata.dataset_id == dataset_row.dataset_id).all()
            dataset_dict['metadata'] = metadata
        else:
            dataset_dict['metadata'] = []

        dataset = namedtuple('Dataset', dataset_dict.keys())(**dataset_dict)

        datasets_to_return.append(dataset)

    return datasets_to_return
Exemplo n.º 42
0
def admin_booze_edit(id):
    saved = int(request.args.get('saved', "0"))
    booze = Booze.query.filter_by(id=int(id)).first()
    form = BoozeForm(obj=booze)
    boozes = Booze.query.order_by(asc(func.lower(Booze.name)))
    return render_template("admin/booze", options=app.options, booze=booze, boozes=boozes, form=form, title="Booze", saved=saved)
def get_bucketlist(user, id):
    """
    Returns a bucket along with a list of its items
    ---
    tags:
        - bucketlists
    consumes:
        - "application/json"
    produces:
        - "application/json"
    parameters:
        - name: X-Token
          in: header
          description: The user's token
          required: true
          type: string
        - name: id
          in: path
          type: integer
          required: true
          description: The id of the bucket
        - name: limit
          in: query
          required: false
          description: The number of results that should be returned
          type: integer
        - name: page
          in: query
          required: false
          description: The current page. Pages start from zero.
          type: integer
        - name: q
          in: query
          description: The keywords to search based on item title
          required: false
          type: string
    responses:
        200:
            description: Operation was successful
            schema:
                type: object
                properties:
                    id:
                        type: integer
                    name:
                        type: string
                    description:
                        type: string
                    items:
                        schema:
                            type: object
                            properties:
                                id:
                                    type: integer
                                title:
                                    type: string
                                description:
                                    type: string
                                due_date:
                                    type: string
                                is_complete:
                                    type: boolean
                                created_at:
                                    type: string
        400:
            description: Parameter error
            schema:
                type: object
                properties:
                    message:
                        type: string
        401:
            description: Invalid token error
            schema:
                type: object
                properties:
                    message:
                        type: string
    """

    if request.method == 'OPTIONS' or not user:
        return jsonify()

    bucketlist = BucketList.query.filter_by(user_id=user.id, id=id).first()

    if bucketlist == None:
        return jsonify(message='The bucket you requested does not exist'), 404

    bucket_result = bucketlist.dict()
    bucket_result['items'] = list()
    limit, page = get_pagination_params(request)
    offset = limit * page
    query = request.args.get('q')
    items = BucketItem.query.filter(BucketItem.bucketlist_id == BucketList.id)

    if query:
        query = query.replace(' ', '%')
        items = items.filter(
            func.lower(BucketItem.title).like('%' + func.lower(query) + '%'))

    items = items.order_by(desc(
        BucketItem.created_at)).limit(limit).offset(offset)

    for item in items:
        bucket_result['items'].append(item.dict())

    previous = '/bucketlists/{}?limit={}'.format(id, limit)
    next = '/bucketlists/{}?limit={}'.format(id, limit)

    if page >= 0:
        next = next + '&page={}'.format(page + 1)
        previous = previous + '&page={}'.format(page - 1)

    if page == 0:
        previous = ''

    return jsonify(bucketlist=bucket_result,
                   paging=dict(previous=previous, next=next))
Exemplo n.º 44
0
    def lookup(title=None, year=None, only_cached=False, session=None):
        if not title:
            raise LookupError('No criteria specified for blu-ray.com lookup')
        title_year = title + ' ({})'.format(year) if year else title

        movie_filter = session.query(BlurayMovie).filter(
            func.lower(BlurayMovie.name) == title.lower())
        if year:
            movie_filter = movie_filter.filter(BlurayMovie.year == year)
        movie = movie_filter.first()
        if not movie:
            found = session.query(BluraySearchResult). \
                filter(BluraySearchResult.search == title_year.lower()).first()
            if found and found.movie:
                movie = found.movie

        if movie:
            # Movie found in cache, check if cache has expired. Shamefully stolen from api_tmdb
            refresh_time = timedelta(days=2)
            if movie.release_date:
                if movie.release_date > datetime.now().date() - timedelta(
                        days=7):
                    # Movie is less than a week old, expire after 1 day
                    refresh_time = timedelta(days=1)
                else:
                    age_in_years = (datetime.now().date() -
                                    movie.release_date).days / 365
                    refresh_time += timedelta(days=age_in_years * 5)
            if movie.updated < datetime.now(
            ) - refresh_time and not only_cached:
                log.debug(
                    'Cache has expired for %s, attempting to refresh from blu-ray.com.',
                    movie.name)
                try:
                    updated_movie = BlurayMovie(title=title, year=year)
                except LookupError:
                    log.error(
                        'Error refreshing movie details for %s from blu-ray.com, cached info being used.',
                        title)
                else:
                    movie = session.merge(updated_movie)
            else:
                log.debug('Movie %s information restored from cache.',
                          movie.name)
        else:
            if only_cached:
                raise LookupError('Movie %s not found from cache' % title_year)
            # There was no movie found in the cache, do a lookup from blu-ray.com
            log.verbose('Searching from blu-ray.com `%s`', title)

            # Add/merge movie to db
            movie = BlurayMovie(title=title, year=year)

            # Add to search results table if necessary
            if title.lower() != movie.name.lower():
                session.add(
                    BluraySearchResult(search=title_year.lower(),
                                       movie_id=movie.id))

            session.merge(movie)

            if not movie:
                raise LookupError(
                    'Unable to find movie on blu-ray: {}'.format(title_year))

        return movie
def get_bucketlists(user):
    """
    Returns a list of bucketlists
    ---
    tags:
        - bucketlists
    consumes:
        - "application/json"
    produces:
        - "application/json"
    parameters:
        - name: X-Token
          in: header
          description: The user's token
          required: true
          type: string
        - name: limit
          in: query
          required: false
          description: The number of results that should be returned
          type: integer
        - name: page
          in: query
          required: false
          description: The current page. Pages start from zero.
          type: integer
        - name: q
          in: query
          required: false
          description: The keywords to search.
          type: string
    security:
        - X-Token
    responses:
        200:
            description: Operation was successful
            schema:
                type: object
                properties:
                    id:
                        type: integer
                    name:
                        type: string
                    description:
                        type: string
                    
        400:
            description: Parameter error
            schema:
                type: object
                properties:
                    message:
                        type: string
        401:
            description: Invalid token error
            schema:
                type: object
                properties:
                    message:
                        type: string
    """

    if request.method == 'OPTIONS' or not user:
        return jsonify()

    limit, page = get_pagination_params(request)
    query = request.args.get('q')

    offset = page * limit

    bucketlists = BucketList.query.filter(BucketList.user_id == user.id)

    if query:
        query = query.replace(' ', '%')
        bucketlists = bucketlists.filter(
            func.lower(BucketList.name).like('%' + func.lower(query) + '%'))

    bucketlists = bucketlists.order_by(desc(
        BucketList.created_at)).limit(limit).offset(offset)
    bucket_list = list()

    for bucketlist in bucketlists:
        _bucketlist = bucketlist.dict()
        bucket_list.append(_bucketlist)

    previous = '/bucketlists?limit={}'.format(limit)
    next = '/bucketlists?limit={}'.format(limit)

    if page >= 0:
        next = next + '&page={}'.format(page + 1)
        previous = previous + '&page={}'.format(page - 1)

    if page == 0:
        previous = ''

    return jsonify(bucketlists=bucket_list,
                   paging=dict(previous=previous, next=next))
def edit_bucketlist(user, id):
    """
    Edits a bucket
    ---
    tags:
        - bucketlists
    consumes:
        - "application/json"
    produces:
        - "application/json"
    parameters:
        - name: X-Token
          in: header
          description: The user's token
          required: true
          type: string
        - name: id
          in: path
          type: integer
          required: true
          description: The id of the bucket
        - name: payload
          in: body
          required: true
          description: The name and title of the new Bucket
          type: object
          schema:
            properties:
                name:
                    type: string
                    required: false
                description:
                    type: string
                    required: false
    responses:
        200:
            description: Operation was successful
            schema:
                type: object
                properties:
                    id:
                        type: integer
                    name:
                        type: string
                    description:
                        type: string 
                    created_at:
                        type: string
        400:
            description: Parameter error
            schema:
                type: object
                properties:
                    message:
                        type: string
        401:
            description: Invalid token error
            schema:
                type: object
                properties:
                    message:
                        type: string
    """

    if request.method == 'OPTIONS':
        return jsonify()

    body = get_request_body(request)
    name = body.get('name')
    description = body.get('description')
    bucketlist = BucketList.query.filter(
        and_(func.lower(BucketList.name) == func.lower(name)),
        BucketList.id != id).first()

    if bucketlist:
        return jsonify(message='A bucket with that name already exists',
                       parameter='name'), 400

    bucketlist = BucketList.query.filter_by(user_id=user.id, id=id).first()

    if bucketlist == None:
        return jsonify(message='The bucket you requested does not exist'), 404

    if not body:
        return jsonify(message='At least one parameter is required',
                       parameter=list("name", 'description')), 400

    if name:
        bucketlist.name = name
    if description:
        bucketlist.description = description

    db.session.commit()
    return jsonify(bucketlist.dict())
Exemplo n.º 47
0
 def all_codes_for(cls, email):
     return (cls.query.filter(func.lower(cls.email) == email.lower())).all()
def create_bucketlist(user):
    """
    Creates a new bucket
    ---
    tags:
        - bucketlists
    consumes:
        - "application/json"
    produces:
        - "application/json"
    parameters:
        - name: X-Token
          in: header
          description: The user's token
          required: true
          type: string
        - name: payload
          in: body
          required: true
          description: The name and title of the new Bucket
          type: object
          schema:
            properties:
                name:
                    type: string
                description:
                    type: string
    security:
        - X-Token
    responses:
        201:
            description: Operation was successful
            schema:
                type: object
                properties:
                    id:
                        type: integer
                    name:
                        type: string
                    description:
                        type: string
                    created_at:
                        type: string
        400:
            description: Parameter error
            schema:
                type: object
                properties:
                    message:
                        type: string
        401:
            description: Invalid token error
            schema:
                type: object
                properties:
                    message:
                        type: string
    """
    if request.method == 'OPTIONS':
        return jsonify()

    body = get_request_body(request)
    name = body.get('name')
    description = body.get('description')

    if not name:
        return jsonify(message='Bucket name is missing', parameter='name'), 400

    if not description:
        return jsonify(message='Bucket description is missing',
                       parameter='description'), 400

    bucketlist = BucketList.query.filter(
        func.lower(BucketList.name) == func.lower(name)).first()

    if bucketlist:
        return jsonify(message='A bucket with that name already exists',
                       parameter='name'), 400

    bucketlist = BucketList(name, description, owner=user)
    db.session.add(bucketlist)
    db.session.commit()

    return jsonify(bucketlist.dict()), 201
Exemplo n.º 49
0
 def bind_expression(self, bindvalue):
     return func.lower(bindvalue)
Exemplo n.º 50
0
 def __eq__(self, other):
     return func.lower(self.__clause_element__()) == func.lower(other)
Exemplo n.º 51
0
def getmname(name):
    return Medicines.query.filter(
        func.lower(Medicines.name) == func.lower(name)).first().mid
Exemplo n.º 52
0
 def column_expression(self, col):
     return func.lower(col)
Exemplo n.º 53
0
Arquivo: chat.py Projeto: wnku/newparp
def invite(chat, pm_user, url, fmt):

    if request.form["username"] == g.user.username:
        abort(404)

    if chat.type != "group" or chat.publicity != "private":
        abort(404)

    try:
        own_chat_user = g.db.query(ChatUser).filter(
            and_(
                ChatUser.chat_id == chat.id,
                ChatUser.user_id == g.user.id,
            )).one()
    except NoResultFound:
        abort(404)

    if not own_chat_user.can("invite"):
        abort(403)

    try:
        invite_user = g.db.query(User).filter(
            func.lower(
                User.username) == request.form["username"].lower(), ).one()
    except NoResultFound:
        if ("X-Requested-With" in request.headers
                and request.headers["X-Requested-With"] == "XMLHttpRequest"):
            abort(404)
        return redirect((request.headers.get("Referer")
                         or url_for("rp_invites", url=url)).split("?")[0] +
                        "?invite_error=no_user")

    if g.db.query(func.count("*")).select_from(Invite).filter(
            and_(
                Invite.chat_id == chat.id,
                Invite.user_id == invite_user.id,
            )).scalar() == 0:
        g.db.add(
            Invite(chat_id=chat.id,
                   user_id=invite_user.id,
                   creator_id=g.user.id))
        # Subscribe them to the chat and make it unread so they get a notification about it.
        try:
            invite_chat_user = g.db.query(ChatUser).filter(
                and_(
                    ChatUser.chat_id == chat.id,
                    ChatUser.user_id == invite_user.id,
                )).one()
        except NoResultFound:
            new_number = (g.db.query(func.max(ChatUser.number)).filter(
                ChatUser.chat_id == chat.id).scalar() or 0) + 1
            invite_chat_user = ChatUser.from_user(user=invite_user,
                                                  chat_id=chat.id,
                                                  number=new_number)
            # Disable typing notifications by default in group chats.
            invite_chat_user.typing_notifications = False
            g.db.add(invite_chat_user)
        invite_chat_user.subscribed = True
        invite_chat_user.last_online = chat.last_message - datetime.timedelta(
            0, 1)
        send_message(
            g.db, g.redis,
            Message(
                chat_id=chat.id,
                user_id=invite_user.id,
                type="user_action",
                name=own_chat_user.name,
                text="%s [%s] invited %s to the chat." % (
                    own_chat_user.name,
                    own_chat_user.acronym,
                    invite_user.username,
                ),
            ), g.user_list)

    if ("X-Requested-With" in request.headers
            and request.headers["X-Requested-With"] == "XMLHttpRequest"):
        return "", 204

    if "Referer" in request.headers:
        return redirect(request.headers["Referer"].split("?")[0])
    return redirect(url_for("rp_invites", url=url))
Exemplo n.º 54
0
    def ordered_query(self, request, query=None):
        """Order query according to request args.

        If query is None, the query is generated according to request
        args with self.query(request)
        """
        if query is None:
            query = self.query(request)

        engine = query.session.get_bind(self.managed_class.__mapper__)
        args = request.args
        sort_col = int(args.get("iSortCol_0", 1))
        sort_dir = args.get("sSortDir_0", "asc")
        sort_col_def = self.list_view_columns[sort_col]
        sort_col_name = sort_col_def['name']
        rel_sort_names = sort_col_def.get('sort_on', (sort_col_name, ))
        sort_cols = []

        for rel_col in rel_sort_names:
            sort_col = getattr(self.managed_class, rel_col)
            if hasattr(sort_col, 'property') and isinstance(
                    sort_col.property,
                    orm.properties.RelationshipProperty,
            ):
                # this is a related model: find attribute to filter on
                query = query.outerjoin(sort_col_name, aliased=True)

                rel_model = sort_col.property.mapper.class_
                default_sort_name = 'name'
                if issubclass(rel_model, BaseVocabulary):
                    default_sort_name = 'label'

                rel_sort_name = sort_col_def.get('relationship_sort_on', None)
                if rel_sort_name is None:
                    rel_sort_name = sort_col_def.get(
                        'sort_on',
                        default_sort_name,
                    )
                sort_col = getattr(rel_model, rel_sort_name)

            # XXX: Big hack, date are sorted in reverse order by default
            if isinstance(sort_col, _DateAffinity):
                sort_dir = 'asc' if sort_dir == 'desc' else 'desc'
            elif isinstance(sort_col, sa.types.String) or \
                    hasattr(sort_col, 'property') and \
                    isinstance(sort_col.property.columns[0].type, sa.types.String):
                sort_col = func.lower(sort_col)

            try:
                direction = desc if sort_dir == 'desc' else asc
                sort_col = direction(sort_col)
            except BaseException:
                # FIXME
                pass

            # sqlite does not support 'NULLS FIRST|LAST' in ORDER BY clauses
            if engine.name != 'sqlite':
                nullsorder = nullslast if sort_dir == 'desc' else nullsfirst
                try:
                    sort_col = nullsorder(sort_col)
                except BaseException:
                    # FIXME
                    pass

            sort_cols.append(sort_col)

        try:
            query = query.order_by(*sort_cols)
        except BaseException:
            # FIXME
            pass
        query.reset_joinpoint()
        return query
Exemplo n.º 55
0
def like_domain_query(term):
    domain_query = database.session_query(Domain.id)
    domain_query = domain_query.filter(func.lower(Domain.name).like(term.lower()))
    assoc_query = database.session_query(certificate_associations.c.certificate_id)
    assoc_query = assoc_query.filter(certificate_associations.c.domain_id.in_(domain_query))
    return assoc_query
Exemplo n.º 56
0
Arquivo: chat.py Projeto: wnku/newparp
def uninvite(chat, pm_user, url, fmt):

    if request.form["username"] == g.user.username:
        abort(404)

    if chat.type != "group" or chat.publicity != "private":
        abort(404)

    try:
        own_chat_user = g.db.query(ChatUser).filter(
            and_(
                ChatUser.chat_id == chat.id,
                ChatUser.user_id == g.user.id,
            )).one()
    except NoResultFound:
        abort(404)

    if not own_chat_user.can("invite"):
        abort(403)

    try:
        invite_user = g.db.query(User).filter(
            func.lower(
                User.username) == request.form["username"].lower(), ).one()
    except NoResultFound:
        abort(404)

    if g.db.query(func.count("*")).select_from(Invite).filter(
            and_(
                Invite.chat_id == chat.id,
                Invite.user_id == invite_user.id,
            )).scalar() != 0:
        g.db.query(Invite).filter(
            and_(
                Invite.chat_id == chat.id,
                Invite.user_id == invite_user.id,
            )).delete()
        # Unsubscribing is impossible if they don't have access to the chat, so
        # we need to force-unsubscribe them here.
        try:
            invite_chat_user = g.db.query(ChatUser).filter(
                and_(
                    ChatUser.chat_id == chat.id,
                    ChatUser.user_id == invite_user.id,
                )).one()
            invite_chat_user.subscribed = False
        except NoResultFound:
            pass
        send_message(
            g.db, g.redis,
            Message(
                chat_id=chat.id,
                user_id=invite_user.id,
                type="user_action",
                name=own_chat_user.name,
                text="%s [%s] un-invited %s from the chat." % (
                    own_chat_user.name,
                    own_chat_user.acronym,
                    invite_user.username,
                ),
            ), g.user_list)

    if ("X-Requested-With" in request.headers
            and request.headers["X-Requested-With"] == "XMLHttpRequest"):
        return "", 204

    if "Referer" in request.headers:
        return redirect(request.headers["Referer"].split("?")[0])
    return redirect(url_for("rp_invites", url=url))
Exemplo n.º 57
0
 def find(cls, text):
     """Find users whose name matches with the given text"""
     return cls.query.filter(func.lower(cls.name).contains(text.lower()))
Exemplo n.º 58
0
Arquivo: chat.py Projeto: wnku/newparp
    def decorated_function(url, fmt=None, *args, **kwargs):

        # Helper for doing some special URL stuff with PM chats.
        # Normally we just query for a Chat object with the url. However, PM chat
        # URLs take the form "pm/<username>", so we have to look up the username,
        # find the User it belongs to, and use our URL and theirs to create a
        # special URL.

        if url == "pm":
            abort(404)

        elif url.startswith("pm/"):

            username = url[3:]
            if username == "":
                abort(404)

            # You can't PM yourself.
            if g.user is None or username.lower() == g.user.username.lower():
                abort(404)

            try:
                pm_user = g.db.query(User).filter(
                    func.lower(User.username) == username.lower()).one()
            except NoResultFound:
                abort(404)

            # Fix case if necessary.
            if pm_user.username != username:
                if request.method != "GET":
                    abort(404)
                return redirect(
                    url_for(request.endpoint,
                            url="pm/" + pm_user.username,
                            fmt=fmt))

            # Generate URL from our user ID and their user ID.
            # Sort so they're always in the same order.
            pm_url = "pm/" + ("/".join(
                sorted([str(g.user.id), str(pm_user.id)])))
            try:
                chat = g.db.query(PMChat).filter(PMChat.url == pm_url, ).one()
            except NoResultFound:
                # Only create a new PMChat on the main chat page.
                if request.endpoint != "rp_chat":
                    abort(404)

                # Reject if age-restricted.
                if (not g.user.is_admin and pm_user.pm_age_restriction
                        and pm_user.age_group == AgeGroup.under_18
                        and g.user.age_group != AgeGroup.under_18):
                    abort(404)

                chat = PMChat(url=pm_url)
                g.db.add(chat)
                g.db.flush()

                # Create ChatUser for the other user.
                pm_chat_user = ChatUser.from_user(pm_user,
                                                  chat_id=chat.id,
                                                  number=1,
                                                  subscribed=True)
                g.db.add(pm_chat_user)
                g.db.flush()

            return f(chat, pm_user, url, fmt, *args, **kwargs)

        # Force lower case.
        if url != url.lower():
            if request.method != "GET":
                abort(404)
            return redirect(url_for(request.endpoint, url=url.lower(),
                                    fmt=fmt))

        try:
            chat = g.db.query(AnyChat).filter(AnyChat.url == url).one()
        except NoResultFound:
            abort(404)

        g.chat = chat
        g.chat_id = chat.id
        g.user_list = UserListStore(
            NewparpRedis(connection_pool=redis_chat_pool), chat.id)
        try:
            authorize_joining(g.db, g)
        except BannedException:
            if request.endpoint != "rp_chat" or chat.url == "theoubliette":
                abort(403)
            if request.method != "GET":
                abort(404)
            return redirect(
                url_for(request.endpoint, url="theoubliette", fmt=fmt))
        except UnauthorizedException:
            if request.endpoint != "rp_chat_unsubscribe":
                return render_template("chat/private.html", chat=chat), 403
        except BadAgeException:
            if request.endpoint != "rp_chat_unsubscribe":
                if g.user is None:
                    return render_template("chat/log_in_required.html",
                                           chat=chat)
                elif g.user.age_group == AgeGroup.under_18:
                    return render_template("chat/age_under_18.html", chat=chat)
                return render_template("chat/age_check.html", chat=chat), 403
        except TooManyPeopleException:
            if request.endpoint == "rp_chat":
                return render_template("chat/too_many_people.html",
                                       chat=chat), 403

        return f(chat, None, url, fmt, *args, **kwargs)
Exemplo n.º 59
0
 class Foo(AbstractFoo):
     __tablename__ = "foo"
     column_prop = column_property(
         func.lower(AbstractFoo.unrelated_data))
Exemplo n.º 60
0
def render(args):
    """
    Helper function that allows use to render our REST Api.

    :param args:
    :return:
    """
    query = database.session_query(Certificate)

    show_expired = args.pop("showExpired")
    if show_expired != 1:
        one_month_old = (
            arrow.now()
            .shift(months=current_app.config.get("HIDE_EXPIRED_CERTS_AFTER_MONTHS", -1))
            .format("YYYY-MM-DD")
        )
        query = query.filter(Certificate.not_after > one_month_old)

    time_range = args.pop("time_range")

    destination_id = args.pop("destination_id")
    notification_id = args.pop("notification_id", None)
    show = args.pop("show")
    # owner = args.pop('owner')
    # creator = args.pop('creator')  # TODO we should enabling filtering by owner

    filt = args.pop("filter")

    if filt:
        terms = filt.split(";")
        term = "%{0}%".format(terms[1])
        # Exact matches for quotes. Only applies to name, issuer, and cn
        if terms[1].startswith('"') and terms[1].endswith('"'):
            term = terms[1][1:-1]

        if "issuer" in terms:
            # we can't rely on issuer being correct in the cert directly so we combine queries
            sub_query = (
                database.session_query(Authority.id)
                .filter(Authority.name.ilike(term))
                .subquery()
            )

            query = query.filter(
                or_(
                    Certificate.issuer.ilike(term),
                    Certificate.authority_id.in_(sub_query),
                )
            )

        elif "destination" in terms:
            query = query.filter(
                Certificate.destinations.any(Destination.id == terms[1])
            )
        elif "notify" in filt:
            query = query.filter(Certificate.notify == truthiness(terms[1]))
        elif "active" in filt:
            query = query.filter(Certificate.active == truthiness(terms[1]))
        elif "cn" in terms:
            query = query.filter(
                or_(
                    func.lower(Certificate.cn).like(term.lower()),
                    Certificate.id.in_(like_domain_query(term)),
                )
            )
        elif "id" in terms:
            query = query.filter(Certificate.id == cast(terms[1], Integer))
        elif "name" in terms:
            query = query.filter(
                or_(
                    func.lower(Certificate.name).like(term.lower()),
                    Certificate.id.in_(like_domain_query(term)),
                    func.lower(Certificate.cn).like(term.lower()),
                )
            )
        elif "fixedName" in terms:
            # only what matches the fixed name directly if a fixedname is provided
            query = query.filter(Certificate.name == terms[1])
        else:
            query = database.filter(query, Certificate, terms)

    if show:
        sub_query = (
            database.session_query(Role.name)
            .filter(Role.user_id == args["user"].id)
            .subquery()
        )
        query = query.filter(
            or_(
                Certificate.user_id == args["user"].id, Certificate.owner.in_(sub_query)
            )
        )

    if destination_id:
        query = query.filter(
            Certificate.destinations.any(Destination.id == destination_id)
        )

    if notification_id:
        query = query.filter(
            Certificate.notifications.any(Notification.id == notification_id)
        )

    if time_range:
        to = arrow.now().shift(weeks=+time_range).format("YYYY-MM-DD")
        now = arrow.now().format("YYYY-MM-DD")
        query = query.filter(Certificate.not_after <= to).filter(
            Certificate.not_after >= now
        )

    if current_app.config.get("ALLOW_CERT_DELETION", False):
        query = query.filter(Certificate.deleted == false())

    result = database.sort_and_page(query, Certificate, args)
    return result