def _wall_events_query(self):
        """WallMixin implementation."""
        from ututi.lib.wall import generic_events_query
        evts_generic = generic_events_query()

        t_evt = meta.metadata.tables['events']
        t_wall_posts = meta.metadata.tables['wall_posts']

        locations = [loc.id for loc in c.location.flatten]
        subjects = meta.Session.query(Subject)\
            .filter(Subject.location_id.in_(locations))\
            .all()
        if self.feed_filter == 'sub_department':
            subject_ids = [subject.id for subject in self.sub_department.subjects
                           if check_crowds(["subject_accessor"], c.user, subject)]
        else:
            subject_ids = [subject.id for subject in subjects
                           if check_crowds(["subject_accessor"], c.user, subject)]
        public_groups = meta.Session.query(Group)\
            .filter(Group.location_id.in_(locations))\
            .filter(Group.forum_is_public == True)\
            .all()
        ids = [obj.id for obj in subjects + public_groups]

        obj_id_in_list = t_evt.c.object_id.in_(ids) if ids else False
        events_query = evts_generic
        if self.feed_filter == 'subjects':
            return events_query.where(or_(obj_id_in_list, t_wall_posts.c.subject_id.in_(subject_ids)))
        elif self.feed_filter == 'sub_department':
            return events_query.where(or_(t_evt.c.object_id.in_(subject_ids) if subject_ids else False, t_wall_posts.c.subject_id.in_(subject_ids)))
        elif self.feed_filter == 'discussions':
            return events_query.where(or_(t_wall_posts.c.target_location_id.in_(locations), t_wall_posts.c.subject_id.in_(subject_ids)))
        else:
            return events_query.where(or_(obj_id_in_list, t_wall_posts.c.target_location_id.in_(locations),
                                          t_wall_posts.c.subject_id.in_(subject_ids)))

    def get_wall_events_query(self):
        user_is_admin_of_groups = [membership.group_id
                                   for membership in self.memberships
                                   if membership.membership_type == 'administrator']
        subjects = self.all_watched_subjects
        if self.is_teacher:
            subjects += self.taught_subjects
        from ututi.lib.wall import generic_events_query
        evts_generic = generic_events_query()

        t_evt = meta.metadata.tables['events']
        t_evt_comments = meta.metadata.tables['event_comments']
        t_wall_posts = meta.metadata.tables['wall_posts']
        t_content_items = meta.metadata.tables['content_items']
        subject_ids = [s.id for s in subjects]
        group_ids = [m.group.id for m in self.memberships]
        user_commented_evts_select = select([t_evt_comments.c.event_id],
                                            from_obj=[t_evt_comments.join(t_content_items,
                                                                          t_content_items.c.id == t_evt_comments.c.id)],)\
            .where(t_content_items.c.created_by == self.id)
        user_commented_evts = map(lambda r: r[0], meta.Session.execute(user_commented_evts_select).fetchall())

        query = evts_generic\
            .where(or_(or_(t_evt.c.object_id.in_(subject_ids),
                           t_wall_posts.c.subject_id.in_(subject_ids)) if subject_ids else False,  # subject wall posts
                       and_(or_(t_evt.c.author_id == self.id,  # location wall posts
                                # XXX User comments may grow to 1k-10k scale, consider a different implementation.
                                t_evt.c.id.in_(user_commented_evts) if user_commented_evts else False),
                            t_evt.c.event_type.in_(('subject_wall_post', 'location_wall_post'))),
                       or_(t_evt.c.object_id.in_(group_ids),) if group_ids else False))\
            .where(or_(t_evt.c.event_type != 'moderated_post_created',
                       t_evt.c.object_id.in_(user_is_admin_of_groups) if user_is_admin_of_groups else False))\
            .where(not_(t_evt.c.event_type.in_(self.ignored_events_list) if self.ignored_events_list else False))
        return query

    def not_covered(cls, count_as_covered=None,
                    count_as_not_covered_if_covered_before=None):
        """Filter a query to find only items without coverage records.
        :param count_as_covered: A list of constants that indicate
           types of coverage records that should count as 'coverage'
           for purposes of this query.
        :param count_as_not_covered_if_covered_before: If a coverage record
           exists, but is older than the given date, do not count it as
           covered.
        :return: A clause that can be passed in to Query.filter().
        """
        if not count_as_covered:
            count_as_covered = cls.DEFAULT_COUNT_AS_COVERED
        elif isinstance(count_as_covered, basestring):
            count_as_covered = [count_as_covered]

        # If there is no coverage record, then of course the item is
        # not covered.
        missing = cls.id==None

        # If we're looking for specific coverage statuses, then a
        # record does not count if it has some other status.
        missing = or_(
            missing, ~cls.status.in_(count_as_covered)
        )

        # If the record's timestamp is before the cutoff time, we
        # don't count it as covered, regardless of which status it
        # has.
        if count_as_not_covered_if_covered_before:
            missing = or_(
                missing, cls.timestamp < count_as_not_covered_if_covered_before
            )

        return missing

def user_show_groups_json(user_id, group_filter="all"):
    memberships = Membership.q.filter(Membership.user_id == user_id)
    if group_filter == "active":
        memberships = memberships.filter(
            # it is important to use == here, "is" does NOT work
            or_(Membership.begins_at == None,
                Membership.begins_at <= session.utcnow())
        ).filter(
            # it is important to use == here, "is" does NOT work
            or_(Membership.ends_at == None,
                Membership.ends_at > session.utcnow())
        )

    return jsonify(items=[{
            'group_name': membership.group.name,
            'begins_at': (datetime_filter(membership.begins_at)
                          if membership.begins_at is not None else ''),
            'ends_at': (datetime_filter(membership.ends_at)
                        if membership.ends_at is not None else ''),
            'actions': [{'href': url_for(".edit_membership",
                                        user_id=user_id,
                                        membership_id=membership.id),
                        'title': 'Bearbeiten',
                        'icon': 'glyphicon-edit'},
                        {'href': url_for(".end_membership",
                                         user_id=user_id,
                                         membership_id=membership.id),
                         'title': "Beenden",
                         'icon': 'glyphicon-off'} if membership.active() else {}],
        } for membership in memberships.all()])

    def index(self):

        # Releases
        theater = Db.query(MovieETA) \
            .join(MovieETA.Movie)  \
            .filter(MovieETA.theater <= time.time() + 1814400) \
            .filter(MovieETA.theater >= time.time()) \
            .filter(or_(Movie.status == 'want', Movie.status == 'waiting')) \
            .order_by(MovieETA.theater) \
            .all()

        dvd = Db.query(MovieETA) \
            .join(MovieETA.Movie)  \
            .filter(MovieETA.dvd <= time.time() + 3628800) \
            .filter(MovieETA.dvd >= time.time()) \
            .filter(or_(Movie.status == 'want', Movie.status == 'waiting')) \
            .order_by(MovieETA.dvd) \
            .all()

        dvdNow = Db.query(MovieETA) \
            .join(MovieETA.Movie)  \
            .filter(MovieETA.dvd <= time.time()) \
            .filter(MovieETA.dvd > 0) \
            .filter(or_(Movie.status == 'want', Movie.status == 'waiting')) \
            .order_by(MovieETA.dvd) \
            .all()

        return self.render({'dvd': dvd, 'theater':theater, 'dvdNow': dvdNow, 'running': self.cron.get('eta').isRunning()})

    def authenticate_user(self, name_or_email, password):
        """Authenticate user by user_name of email and password. If the user
        pass the authentication, return user_id, otherwise, raise error

        """
        from sqlalchemy.sql.expression import or_
        User = tables.User
        user = self.session.query(User) \
            .filter(or_(User.user_name == name_or_email,
                        User.email == name_or_email)) \
            .first()
        if user is None:
            # maybe it's case problem, although we enforce lower case to
            # user name and email now, but it seems there is still some
            # accounts have id in different cases, so that's why we do the
            # user query twice
            name_or_email = name_or_email.lower()
            user = self.session.query(User) \
                .filter(or_(User.user_name == name_or_email,
                            User.email == name_or_email)) \
                .first()
            if user is None:
                raise UserNotExist('User %s does not exist' % name_or_email)
        if not user.validate_password(password):
            raise BadPassword('Bad password')
        if not user.active:
            raise UserNotActived('User %s is not activated' % user.user_name)
        return user.user_id

    def availableChars(self, status = None, release_status = None):

        chars = ''

        db = get_session()

        # Make a list from string
        if not isinstance(status, (list, tuple)):
            status = [status]
        if release_status and not isinstance(release_status, (list, tuple)):
            release_status = [release_status]

        q = db.query(Movie) \
            .outerjoin(Movie.releases, Movie.library, Library.titles, Movie.status) \
            .options(joinedload_all('library.titles'))

        # Filter on movie status
        if status and len(status) > 0:
            q = q.filter(or_(*[Movie.status.has(identifier = s) for s in status]))

        # Filter on release status
        if release_status and len(release_status) > 0:
            q = q.filter(or_(*[Release.status.has(identifier = s) for s in release_status]))

        results = q.all()

        for movie in results:
            char = movie.library.titles[0].simple_title[0]
            char = char if char in ascii_lowercase else '#'
            if char not in chars:
                chars += str(char)

        db.expire_all()
        return ''.join(sorted(chars, key = str.lower))

    def list_bangumi(self, page, count, sort_field, sort_order, name, user_id, bangumi_type):
        try:

            session = SessionManager.Session()
            query_object = session.query(Bangumi).\
                options(joinedload(Bangumi.cover_image)).\
                filter(Bangumi.delete_mark == None)

            if bangumi_type != -1:
                query_object = query_object.filter(Bangumi.type == bangumi_type)

            if name is not None:
                name_pattern = '%{0}%'.format(name.encode('utf-8'),)
                logger.debug(name_pattern)
                query_object = query_object.\
                    filter(or_(Bangumi.name.ilike(name_pattern), Bangumi.name_cn.ilike(name_pattern)))
                # count total rows
                total = session.query(func.count(Bangumi.id)).\
                    filter(or_(Bangumi.name.ilike(name_pattern), Bangumi.name_cn.ilike(name_pattern))).\
                    scalar()
            else:
                total = session.query(func.count(Bangumi.id)).scalar()

            if sort_order == 'desc':
                query_object = query_object.\
                    order_by(desc(getattr(Bangumi, sort_field)))

            else:
                query_object = query_object.\
                    order_by(asc(getattr(Bangumi, sort_field)))

            if count == -1:
                bangumi_list = query_object.all()
            else:
                offset = (page - 1) * count
                bangumi_list = query_object.offset(offset).limit(count).all()

            bangumi_id_list = [bgm.id for bgm in bangumi_list]

            favorites = session.query(Favorites).\
                filter(Favorites.bangumi_id.in_(bangumi_id_list)).\
                filter(Favorites.user_id == user_id).\
                all()

            bangumi_dict_list = []
            for bgm in bangumi_list:
                bangumi = row2dict(bgm, Bangumi)
                bangumi['cover'] = utils.generate_cover_link(bgm)
                utils.process_bangumi_dict(bgm, bangumi)
                for fav in favorites:
                    if fav.bangumi_id == bgm.id:
                        bangumi['favorite_status'] = fav.status
                bangumi_dict_list.append(bangumi)

            return json_resp({'data': bangumi_dict_list, 'total': total})
        finally:
            SessionManager.Session.remove()

    def read_many_byuser(self, request):
        """
        """

        username = request.matchdict['username']

        page = int(request.params.get("page", 1))
        pagesize = int(request.params.get("pagesize", 10))

        if self.Session.query(User).filter(User.username == username).first() == None:
            raise HTTPNotFound("Requested user does not exist.")

        items = []

        activities_sub_query = self.Session.query(Activity.activity_identifier.label("identifier"), Activity.version, Changeset.timestamp, Changeset.fk_user).\
            join(Changeset).\
            filter(or_(Activity.fk_status == 2, Activity.fk_status == 3)).subquery(name="sub_act")

        activities_query = self.Session.query(activities_sub_query, User.username).\
            join(User).filter(User.username == username).subquery(name="act")

        # All active and inactive stakeholders
        stakeholder_active = self.Session.query(Stakeholder).\
            filter(or_(Stakeholder.fk_status == 2, Stakeholder.fk_status == 3)).\
            subquery("st_active")

        # Get the five latest stakeholder by changeset
        stakeholder_sub_query = self.Session.query(stakeholder_active.c.stakeholder_identifier.label("identifier"), \
                                                   stakeholder_active.c.version, Changeset.timestamp, Changeset.fk_user).\
            join(Changeset, Changeset.id == stakeholder_active.c.fk_changeset).\
            subquery(name="sub_st")

        # Join the resulting set to the user table
        stakeholder_query = self.Session.query(stakeholder_sub_query, User.username).\
            join(User).filter(User.username == username).subquery(name="st")

        query = self.Session.query(activities_query, literal_column("\'activity\'").label("type")).\
            union(self.Session.query(stakeholder_query, literal_column("\'stakeholder\'").label("type"))).\
            order_by(desc(activities_query.c.timestamp)).order_by(desc(activities_query.c.version))

        for i in query.offset((page-1)*pagesize).limit(pagesize).all():
            items.append({
            "type": i.type,
            "author": i.username,
            "timestamp": i.timestamp,
            "version": i.version,
            "identifier": str(i.identifier)
            })                            
        return {
            "items": items,
            "username": username,
            "totalitems": query.count(),
            "pagesize": pagesize,
            "currentpage": page
        }

        return {}

 def p_file_term(p):
     '''file_term : OP_FILE string'''
     if p[2].startswith('^'):
         p[0] = and_(or_(func.matches(p[2], gertty.db.file_table.c.path),
                         func.matches(p[2], gertty.db.file_table.c.old_path)),
                     gertty.db.file_table.c.status is not None)
     else:
         p[0] = and_(or_(gertty.db.file_table.c.path == p[2],
                         gertty.db.file_table.c.old_path == p[2]),
                     gertty.db.file_table.c.status is not None)

    def run(self, commit, welt2000_path):
        welt2000 = get_database(path=welt2000_path)

        self.current_date = datetime.utcnow()
        i = 0

        for airport_w2k in welt2000:
            if (airport_w2k.type != 'airport' and
                    airport_w2k.type != 'glider_site' and
                    airport_w2k.type != 'ulm'):
                continue

            i += 1
            if i % 100 == 0:
                db.session.flush()
                print str(i) + ": " + airport_w2k.country_code + " " + airport_w2k.name

            # try to find this airport in the database
            near_airport = Airport.query() \
                .filter(and_(Airport.short_name == airport_w2k.short_name,
                             Airport.country_code == airport_w2k.country_code)) \
                .filter(or_(Airport.valid_until == None, Airport.valid_until > self.current_date)) \
                .first()

            # fall back to location-search if airport is not found
            # and only reuse this airport if it's within 250 meters of the old one...
            if near_airport is None or near_airport.distance(airport_w2k) > 250:
                near_airport = Airport.by_location(airport_w2k, distance_threshold=0.0025)

            if near_airport is None:
                # this airport is not in our database yet. add it...
                self.add_airport(airport_w2k)

            else:
                # seems to be the same airport. update with current values
                self.show_differences(near_airport, airport_w2k)
                self.update_airport(near_airport, airport_w2k)

        db.session.flush()

        # now invalidate all remaining airports

        invalid_airports = Airport.query() \
            .filter(Airport.time_modified < self.current_date) \
            .filter(or_(Airport.valid_until == None, Airport.valid_until > self.current_date))

        for airport in invalid_airports:
            print "{}  {}  {}" \
                .format(airport.country_code, airport.name, airport.icao)
            print "  invalidated"

            airport.valid_until = self.current_date

        if commit:
            db.session.commit()

    def list(self, status = ['active'], limit_offset = None, starts_with = None, search = None):

        db = get_session()

        # Make a list from string
        if not isinstance(status, (list, tuple)):
            status = [status]


        q = db.query(Movie) \
            .join(Movie.library, Library.titles) \
            .options(joinedload_all('releases.status')) \
            .options(joinedload_all('releases.quality')) \
            .options(joinedload_all('releases.files')) \
            .options(joinedload_all('releases.info')) \
            .options(joinedload_all('library.titles')) \
            .options(joinedload_all('library.files')) \
            .options(joinedload_all('status')) \
            .options(joinedload_all('files')) \
            .filter(LibraryTitle.default == True) \
            .filter(or_(*[Movie.status.has(identifier = s) for s in status]))

        filter_or = []
        if starts_with:
            starts_with = toUnicode(starts_with.lower())
            if starts_with in ascii_lowercase:
                filter_or.append(LibraryTitle.simple_title.startswith(starts_with))
            else:
                ignore = []
                for letter in ascii_lowercase:
                    ignore.append(LibraryTitle.simple_title.startswith(toUnicode(letter)))
                filter_or.append(not_(or_(*ignore)))

        if search:
            filter_or.append(LibraryTitle.simple_title.like('%%' + search + '%%'))

        if filter_or:
            q = q.filter(or_(*filter_or))

        q = q.order_by(asc(LibraryTitle.simple_title))

        if limit_offset:
            splt = limit_offset.split(',')
            limit = splt[0]
            offset = 0 if len(splt) is 1 else splt[1]
            q = q.limit(limit).offset(offset)

        results = q.all()

        movies = []
        for movie in results:
            temp = movie.to_dict(self.default_dict)
            movies.append(temp)

        return movies

def get_locations(search_term, geo_level=None, year="2011"):
    if geo_level is not None and geo_level not in geo_levels:
        raise ValueError("Invalid geo_level: %s" % geo_level)

    session = get_session()
    try:
        if geo_level:
            levels = [geo_level]
        else:
            levels = ["country", "province", "municipality", "subplace"]

        objects = set()

        # search at each level
        for level in levels:
            # already checked that geo_level is valid
            model = get_geo_model(level)

            if level == "subplace":
                # check mainplace and subplace names
                objects.update(
                    session.query(Ward)
                    .join(model)
                    .filter(model.year == year)
                    .filter(
                        or_(
                            model.subplace_name.ilike(search_term + "%"),
                            model.subplace_name.ilike("City of %s" % search_term + "%"),
                            model.mainplace_name.ilike(search_term + "%"),
                            model.code == search_term,
                        )
                    )
                    .limit(10)
                )

            else:
                objects.update(
                    session.query(model)
                    .filter(model.year == year)
                    .filter(
                        or_(
                            model.name.ilike(search_term + "%"),
                            model.name.ilike("City of %s" % search_term + "%"),
                            model.code == search_term.upper(),
                        )
                    )
                    .limit(10)
                )

        order_map = {Country: 4, Ward: 3, Municipality: 2, Province: 1}
        objects = sorted(objects, key=lambda o: [order_map[o.__class__], getattr(o, "name", getattr(o, "code"))])

        return serialize_demarcations(objects[0:10])
    finally:
        session.close()

    def add(self, group):

        db = get_session()

        identifier = '%s.%s.%s' % (group['library']['identifier'], group['meta_data'].get('audio', 'unknown'), group['meta_data']['quality']['identifier'])


        done_status, snatched_status = fireEvent('status.get', ['done', 'snatched'], single = True)

        # Add movie
        movie = db.query(Media).filter_by(library_id = group['library'].get('id')).first()
        if not movie:
            movie = Media(
                library_id = group['library'].get('id'),
                profile_id = 0,
                status_id = done_status.get('id')
            )
            db.add(movie)
            db.commit()

        # Add Release
        rel = db.query(Relea).filter(
            or_(
                Relea.identifier == identifier,
                and_(Relea.identifier.startswith(group['library']['identifier']), Relea.status_id == snatched_status.get('id'))
            )
        ).first()
        if not rel:
            rel = Relea(
                identifier = identifier,
                movie = movie,
                quality_id = group['meta_data']['quality'].get('id'),
                status_id = done_status.get('id')
            )
            db.add(rel)
            db.commit()

        # Add each file type
        added_files = []
        for type in group['files']:
            for cur_file in group['files'][type]:
                added_file = self.saveFile(cur_file, type = type, include_media_info = type is 'movie')
                added_files.append(added_file.get('id'))

        # Add the release files in batch
        try:
            added_files = db.query(File).filter(or_(*[File.id == x for x in added_files])).all()
            rel.files.extend(added_files)
            db.commit()
        except:
            log.debug('Failed to attach "%s" to release: %s', (added_files, traceback.format_exc()))

        fireEvent('movie.restatus', movie.id)

        return True

def get_user_member(organization_id, state=None):
    """ Helper function to get member states """
    state_query = None
    if not state:
        state_query = or_(model.Member.state == 'active', model.Member.state == 'pending')
    else:
        state_query = or_(model.Member.state == state)

    query = model.Session.query(model.Member).filter(state_query) \
        .filter(model.Member.table_name == 'user').filter(model.Member.group_id == organization_id).filter(model.Member.table_id == c.userobj.id)
    return query.first()

def get_locations(search_term, geo_level=None, year='2011'):
    if geo_level is not None and geo_level not in geo_levels:
        raise ValueError('Invalid geo_level: %s' % geo_level)

    session = get_session()
    try:
        if geo_level:
            levels = [geo_level]
        else:
            levels = ['country', 'province', 'municipality', 'subplace']

        objects = set()

        # search at each level
        for level in levels:
            # already checked that geo_level is valid
            model = {
                'municipality': Municipality,
                'province': Province,
                'subplace': Subplace,
                'country': Country,
            }[level]

            if level == 'subplace':
                # check mainplace and subplace names
                objects.update(session
                    .query(Ward)
                    .join(model)
                    .filter(model.year == year)
                    .filter(or_(model.subplace_name.ilike(search_term + '%'),
                                model.subplace_name.ilike('City of %s' % search_term + '%'),
                                model.mainplace_name.ilike(search_term + '%'),
                                model.code == search_term))
                    .limit(10)
                )

            else:
                objects.update(session
                    .query(model)
                    .filter(model.year == year)
                    .filter(or_(model.name.ilike(search_term + '%'),
                                model.name.ilike('City of %s' % search_term + '%'),
                                model.code == search_term.upper()))
                    .limit(10)
                )


        order_map = {Country: 4, Ward: 3, Municipality: 2, Province: 1}
        objects = sorted(objects, key=lambda o: [order_map[o.__class__], getattr(o, 'name', getattr(o, 'code'))])

        return serialize_demarcations(objects[0:10])
    finally:
        session.close()

 def get_user( session, username=None, email=None):
   if username and email:
     filter_obj = or_( User.username==username, User.email==email )
   elif username:
     filter_obj = or_( User.username==username )
   elif email:
     filter_obj = or_( User.email==email )
   else:
     return  None
   user = session.query(User).filter( filter_obj  ).first()
   if user:
     return  user
   return None

    def list_bangumi(self, page, count, sort_field, sort_order, name, bangumi_type):
        try:
            session = SessionManager.Session()
            query_object = session.query(Bangumi).\
                options(joinedload(Bangumi.cover_image)).\
                options(joinedload(Bangumi.created_by)).\
                options(joinedload(Bangumi.maintained_by)).\
                filter(Bangumi.delete_mark == None)

            if bangumi_type != -1:
                query_object = query_object.filter(Bangumi.type == bangumi_type)

            if name is not None:
                name_pattern = '%{0}%'.format(name.encode('utf-8'),)
                logger.debug(name_pattern)
                query_object = query_object.\
                    filter(or_(Bangumi.name.ilike(name_pattern), Bangumi.name_cn.ilike(name_pattern)))
                # count total rows
                total = session.query(func.count(Bangumi.id)).\
                    filter(or_(Bangumi.name.ilike(name_pattern), Bangumi.name_cn.ilike(name_pattern))).\
                    scalar()
            else:
                total = session.query(func.count(Bangumi.id)).scalar()

            if sort_order == 'desc':
                query_object = query_object.\
                    order_by(desc(getattr(Bangumi, sort_field)))
            else:
                query_object = query_object.\
                    order_by(asc(getattr(Bangumi, sort_field)))

            # we now support query all method by passing count = -1
            if count == -1:
                bangumi_list = query_object.all()
            else:
                offset = (page - 1) * count
                bangumi_list = query_object.offset(offset).limit(count).all()

            bangumi_dict_list = []
            for bgm in bangumi_list:
                bangumi = row2dict(bgm, Bangumi)
                bangumi['cover'] = utils.generate_cover_link(bgm)
                utils.process_bangumi_dict(bgm, bangumi)
                self.__process_user_obj_in_bangumi(bgm, bangumi)
                bangumi_dict_list.append(bangumi)

            return json_resp({'data': bangumi_dict_list, 'total': total})
            # raise ClientError('something happened')
        finally:
            SessionManager.Session.remove()

    def agregar_usuario_fase(self, id, **named):
        identity = request.environ.get('repoze.who.identity')
        user = identity['user']
        usuarios = DBSession.query(User).join((Rol, User.groups)).filter(or_(Rol.group_name == 'Aprobador', Rol.group_name == 'Desarrollador')).all()
        roles = DBSession.query(Rol).filter(or_(Rol.group_name == 'Aprobador', Rol.group_name == 'Desarrollador')).all()
        fase = DBSession.query(Fase).filter(Fase.id == id).one()
#        usuarioFaseRol = DBSession.query(UsuarioFaseRol).filter(UsuarioFaseRol.fase_id == id).all()
        
        
        return dict(page='Asignar Usuario a fase ' + fase.descripcion,
                    usuarios=usuarios,
                    roles=roles,
                    proyecto_id=id,
                    user=user,
                    fase=fase) 

def tag_search(text, count=5):
    """Search in the tag_search_items table (for location tags)."""
    QTag = aliased(LocationTag)
    QParent = aliased(LocationTag)
    text = text.lower().strip()
    query = meta.Session.query(TagSearchItem)\
        .join(QTag)\
        .outerjoin((QParent, QParent.id==QTag.parent_id))\
        .filter(TagSearchItem.terms.op('@@')(func.plainto_tsquery(text)))\
        .order_by(or_(func.lower(func.btrim(QParent.title)) == text, func.lower(func.btrim(QParent.title_short)) == text).desc())\
        .order_by(or_(func.lower(func.btrim(QTag.title)) == text, func.lower(func.btrim(QTag.title_short)) == text).desc())\
        .order_by(func.ts_rank_cd(TagSearchItem.terms, func.plainto_tsquery(text)))
    if count is not None:
        query = query.limit(count)
    return query.all()