def _get_query_for_editors():
            """
            Returns a query that selects versions available to editors.
            """
            active_versions = Session.query(
                mappedClass.version,
                mappedClass.fk_status
            ).\
                filter(mappedClass.identifier == uid).\
                filter(or_(
                    mappedClass.fk_status == 2, mappedClass.fk_status == 3))

            own_filters = and_(
                mappedClass.identifier == uid,
                not_(mappedClass.fk_status == 2),
                not_(mappedClass.fk_status == 3),
                User.username == self.request.user.username)
            own_versions = Session.query(
                mappedClass.version,
                mappedClass.fk_status
            ).\
                join(Changeset).\
                join(User).\
                filter(*own_filters)
            return active_versions.union(own_versions)

    def deletePostsByTags(self, blacklist, whitelist):
        if not blacklist:
            return 0

        s = self.DBsession()

        subq = s.query(Post.id).distinct().join(Post.tags)
        subq = subq.filter(Tag.name.in_(whitelist))

        q = s.query(Post.id).distinct().join(Post.tags)
        q = q.filter(Tag.name.in_(blacklist)).except_(subq)

        d = s.query(Post).filter(Post.id.in_(q))
        post_count = d.delete(synchronize_session='fetch')

        # delete the image refs without posts from the database
        q = s.query(Post.image_id).distinct()
        d = s.query(Image.id).filter(not_(Image.id.in_(q)))
        img_count = d.delete(synchronize_session='fetch')

        q = s.query(Tag.id).join(Post.tags)
        d = s.query(Tag.id).filter(not_(Tag.id.in_(q)))
        tag_count = d.delete(synchronize_session='fetch')

        s.commit()
        return (post_count, img_count, tag_count)

 def constructQuery(self, context):
     session= Session()
     trusted=removeSecurityProxy(context)
     parliament_id = self._get_parliament_id(context)
     item_id = getattr(context, self.value_field, None)
     trusted = removeSecurityProxy(context)
     existing_item_ids = [assn.item_id for assn in trusted.values()]
     if item_id:
         query = session.query(domain.ParliamentaryItem).filter(
             domain.ParliamentaryItem.parliamentary_item_id ==
             item_id)
     else:
         query = session.query(domain.ParliamentaryItem).filter(
                 sql.and_(
                     sql.not_(domain.ParliamentaryItem.status.in_(
                             _assignable_state_ids
                         )
                     ),
                     sql.not_(
                         domain.ParliamentaryItem.parliamentary_item_id.in_(
                             existing_item_ids
                         )
                     ),
                     domain.ParliamentaryItem.parliament_id == parliament_id
                 )
             )
     return query

 def resume_work(self,context):
     """
     on resume setting value from task context
     """
     execution_context=context["execution_context"]
     WRK_LOGGER.debug("RESUMING WORKER for :"+self.worker )
     if execution_context:
         self.start_time=execution_context.get("start_time",datetime.utcnow())
         self.worker_ids=execution_context.get("worker_ids",[])
         self.sp_list=execution_context.get("sp_list",[])
         ##validate all the worker ids are taken care of
         ets = DBSession.query(EntityTasks).filter(EntityTasks.worker==self.worker).\
                                 filter(not_(EntityTasks.worker_id.in_(self.worker_ids))).all()
         if len(ets) > 0:
             xtra_work_ids = [et.worker_id for et in ets]
             WRK_LOGGER.error("GOT ENT Tasks different from execution_context :"+self.worker+\
             ": CONTEXT WORKERS : "+str(self.worker_ids) +": XTRA WORKERS :"+str(xtra_work_ids))
             r = DBSession.query(EntityTasks.entity_id).\
                     filter(EntityTasks.worker_id.in_(xtra_work_ids)).\
                     filter(EntityTasks.worker==self.worker).\
                     update(values=dict(worker_id=None,finished=True,end_time=datetime.utcnow()))
             transaction.commit()
             WRK_LOGGER.debug("Cleaned Up entity_tasks . worker:rows : "+self.worker+":"+str(r))
             
     WRK_LOGGER.debug("RESUMING WORKER for :"+self.worker+":"+str(self.start_time)+":"+str(self.worker_ids) )
     self.do_work()

 def _Not(self, node, fieldname):
     expressions = []
     self.in_not = True
     for subnode in node:
         expressions.append(self._eval(subnode, fieldname))
     self.in_not = False
     return not_(*expressions)

def user_view(user_id):
  user = User.query.get(user_id)
  tab = request.args.get("tab", "activity")

  e = Env(label=user.name, user=user)
  e.bread_crumbs = make_bread_crumbs()
  e.active_tab_id = tab
  e.tabs = make_tabs(user)

  if tab == "conversations":
    pass

  elif tab == "activity":
    # XXX quick & dirty
    e.activity_entries =\
      ActivityEntry.query.filter(ActivityEntry.actor_id == user.id).limit(30).all()

  elif tab == "audit":
    # XXX quick & dirty
    e.audit_entries =\
      AuditEntry.query.filter(AuditEntry.user_id == user.id).limit(30).all()

  elif tab in ("documents", "images"):
    files = File.query.filter(File.owner_id == user_id)
    if tab == "documents":
      files = files.filter(not_(File.mime_type.like("image/%")))
      e.documents = files.all()
    elif tab == "images":
      files = files.filter(File.mime_type.like("image/%"))
      e.images = files.all()

  return render_template("social/user.html", **e)

    def index(self):
        self.template = 'index.phtml'

        # top rated
        top_rated = Rating.top_rated(self.session, limit=6)
        self.view['top_rated'] = top_rated

        # last rated
        last_rated = self.view['last_rated']
        shuffle(last_rated)
        last_rated[5:] = []
        ids = map(itemgetter1, self.view['last_rated'])
        movies = self.session.query(Movie).filter(Movie.id.in_(ids)).all()
        movies = dict((m.id, m)
                      for m in movies)
        self.view['movies'] = movies

        # recent users
        self.view['recent_users'] = User.recent(self.session, limit=8)

        # recent ratings
        already_shown = set(ids)
        already_shown.update((m.id for m, _ in top_rated))
        recent = self.session.query(Rating)\
                     .options(joinedload(Rating.movie))\
                     .options(joinedload(Rating.user))\
                     .filter(not_(Rating.movie_id.in_(already_shown)))\
                     .order_by(Rating.rated.desc()).limit(15).all()
        shuffle(recent)
        recent[10:] = []
        self.view['recent_ratings'] = recent

 def roles(self, new_roles):
     db().query(Role).filter(not_(Role.name.in_(new_roles))).delete(synchronize_session="fetch")
     added_roles = self.roles
     for role in new_roles:
         if role not in added_roles:
             self.role_list.append(Role(name=role, release=self))
             added_roles.append(role)

 def constructQuery(self, context):
     session= Session()
     trusted=removeSecurityProxy(context)
     parliament_id = self._get_parliament_id(context)
     trusted = removeSecurityProxy(context)
     assigned_committee_ids = []
     if IGroupGroupItemAssignment.providedBy(trusted):
         committee_id = getattr(trusted, "group_id", None)
         if committee_id:
             query = session.query(domain.Committee).filter(
                 sql.and_(
                     domain.Committee.parent_group_id == parliament_id,
                     domain.Committee.group_id == committee_id
                 )
             )
             return query
         else:
             assigned_committee_ids = \
                 [ comm.group_id for comm in trusted.__parent__.values() ]
     else:
         assigned_committee_ids = \
             [ comm.group_id for comm in trusted.values() ]
     query = session.query(domain.Committee).filter(
         sql.and_(
             domain.Committee.status == 'active',
             domain.Committee.parent_group_id == parliament_id,
             sql.not_(
                 domain.Committee.group_id.in_(assigned_committee_ids)
             )
         )
     )
     return query

def get_available_venues( start, end, sitting=None ):
    """get all venues that are not booked for a sitting
    (but sitting if given)
    in the given time period 
    SQL:
    SELECT * 
    FROM venues 
    WHERE venues.venue_id NOT IN (SELECT sitting.venue_id 
        FROM sitting 
        WHERE (sitting.start_date BETWEEN '2000-01-01' AND '2000-01-02' 
            OR sitting.end_date BETWEEN '2000-01-01'  AND '2000-01-02'
            OR '2000-01-01'  BETWEEN sitting.start_date AND sitting.end_date 
            OR '2000-01-02'  BETWEEN sitting.start_date AND sitting.end_date) 
        AND sitting.venue_id IS NOT NULL)
    """
    session = Session()
    query = session.query(domain.Venue)
    b_filter = sql.and_(
        sql.or_( 
            sql.between(schema.sitting.c.start_date, start, end), 
            sql.between(schema.sitting.c.end_date, start, end),
            sql.between(start, schema.sitting.c.start_date, 
                schema.sitting.c.end_date),
            sql.between(end, schema.sitting.c.start_date, 
                schema.sitting.c.end_date)
        ),
        schema.sitting.c.venue_id != None)
    if sitting:
        if sitting.sitting_id:
            b_filter = sql.and_(b_filter,
                schema.sitting.c.sitting_id != sitting.sitting_id)
    query = query.filter(sql.not_(schema.venue.c.venue_id.in_(
                sql.select( [schema.sitting.c.venue_id] ).where(b_filter) )))
    venues = query.all()
    return venues

    def find(self, reservation_id=None, resource_id=None, slice_id=None, user_id=None, start_time=None, end_time=None):
        """
        Returns a list of reservation value objects (see class description).
        If all parameters a None, all reservations for this schedule_subject will be returned.
        If given parameters are not-None the result will be filtered by the respective field.
        If multiple params are given the result will be reduced (conditions will be AND-ed).
        If the times are given, all records which touch the given period will be returned.
        If {start_time} is given, but {end_time} is omitted, all records which span start_time will be returned.
        
        Limitations:
        - This method can not be used to filter records with NULL fields. E.g. it is not possible to filter all records to the ones which have set user_id to NULL.
        """
        q = db_session.query(ReservationRecord)
        q = q.filter_by(schedule_subject=self.schedule_subject)
        if not reservation_id is None:
            q = q.filter_by(reservation_id=reservation_id)
        if not resource_id is None:
            q = q.filter_by(resource_id=resource_id)
        if not slice_id is None:
            q = q.filter_by(slice_id=slice_id)
        if not user_id is None:
            q = q.filter_by(user_id=user_id)

        if (not start_time is None) and (end_time is None):
            end_time = start_time
        if (not start_time is None) and (not end_time is None):
            q = q.filter(not_(or_(ReservationRecord.end_time < start_time, ReservationRecord.start_time > end_time)))

        records = q.all()
        result = [self._convert_record_to_value_object(r) for r in records]
        db_session.expunge_all()
        return result

    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 managegroups(request):
    params = {'action': 'manage_groups', 'form': None}
    form, session = None, DBSession
    is_a_post = request.method == 'POST'
    adding = 'groupname' in request.POST
    deleting = request.params.get('group_action', '') == 'delete'
    settings = request.registry.settings
    default_groups_names = [a[0] 
                            for a in get_default_groups(settings)] 
    if is_a_post and deleting:
        items = [a[1]
                 for a in request.POST.items()
                 if a[0] == 'delete']

        todelete = session.query( AuthGroup).filter(
            se.and_(
                AuthGroup.id.in_(items),
                se.not_(AuthGroup.name.in_(default_groups_names))
            )).all()
        noecho = [session.delete(i) for i in todelete]
        request.session.flash(_('Groups %s have been deleted') % (
            ', '.join([a.name for a in todelete])), 'info')
    add_form = GroupForm(request.POST)
    if is_a_post and adding:
        if add_form.validate():
            try:
                group = add_form.save()
                add_form = GroupForm()
                flash(_('Added group : %s' % group.name, 'info'))
            except Exception, e:
                flash(_('Problem adding group : %s' % e, 'error'))

    def _fetch_db(result):
        LOGGER.debug('Open aggregate for item #%d: %r', item_id, result)
        # Si l'info se trouvait dans le cache,
        # on utilise cette valeur là.
        if result is not None:
            # La valeur 0 est utilisée à la place de None
            # dans le cache. On fait la conversion inverse ici.
            if not result:
                return None
            return result

        # Sinon on récupère l'information
        # depuis la base de données...
        state_ok = StateName.statename_to_value('OK')
        state_up = StateName.statename_to_value('UP')
        aggregate = database.run(
            DBSession.query(
                CorrEvent.idcorrevent
            ).join(
                (Event, CorrEvent.idcause == Event.idevent)
            ).filter(
                # Ici, on ne prend pas en compte l'état d'acquittement :
                # on n'agrège jamais une alerte dans un agrégat OK/UP
                # (voir le ticket #1027 pour plus d'information).
                not_(Event.current_state.in_([state_ok, state_up]))
            ).filter(Event.idsupitem == item_id
            ).scalar)

        # ...et on met à jour le cache avant de retourner l'ID.
        # NB: la valeur 0 est utilisée à la place de None pour que
        # le cache puisse réellement servir à l'appel suivant.
        ctx.setShared('open_aggr:%d' % item_id, aggregate or 0)
        return aggregate

 def get_unsolved_challenges(self):
     """
     Return a query that produces a list of all unsolved challenges for a
     given team.
     """
     team_solved_subquery = get_team_solved_subquery(self.id)
     online = get_online_challenges()
     return online.filter(not_(team_solved_subquery))

    def search_moc(self, facility_id, public, moc_id=None, state=None,
                   with_description=False, order_by_date=False):
        """
        Search for MOC records for a facility.
        """

        select_cols = [
            moc.c.id,
            moc.c.facility_id,
            moc.c.name,
            moc.c.public,
            moc.c.uploaded,
            moc.c.num_cells,
            moc.c.area,
            moc.c.state,
        ]

        if with_description:
            select_cols.append(moc.c.description)
            select_cols.append(moc.c.description_format)
            default = {}
        else:
            default = {'description': None, 'description_format': None}

        stmt = select(select_cols)

        if facility_id is not None:
            stmt = stmt.where(moc.c.facility_id == facility_id)

        if public is not None:
            if public:
                stmt = stmt.where(moc.c.public)
            else:
                stmt = stmt.where(not_(moc.c.public))

        if moc_id is not None:
            stmt = stmt.where(moc.c.id == moc_id)

        if state is not None:
            if is_list_like(state):
                stmt = stmt.where(moc.c.state.in_(state))
            else:
                stmt = stmt.where(moc.c.state == state)

        if order_by_date:
            stmt = stmt.order_by(moc.c.uploaded.desc())
        else:
            stmt = stmt.order_by(moc.c.id.asc())

        ans = ResultCollection()

        with self._transaction() as conn:
            for row in conn.execute(stmt):
                values = default.copy()
                values.update(**row)
                ans[row['id']] = MOCInfo(**values)

        return ans

def check_field_association(session, tid, field_dict):
    if field_dict.get('fieldgroup_id', '') and session.query(models.Field).filter(models.Field.id == field_dict['fieldgroup_id'],
                                                                                  models.Field.tid != tid).count():
        raise errors.InputValidationError()

    if field_dict.get('template_id', '') and session.query(models.Field).filter(models.Field.id == field_dict['template_id'],
                                                                                not_(models.Field.tid.in_(set([1, tid])))).count():
        raise errors.InputValidationError()

    if field_dict.get('step_id', '') and session.query(models.Field).filter(models.Step.id == field_dict['step_id'],
                                                                            models.Questionnaire.id == models.Step.questionnaire_id,
                                                                            not_(models.Questionnaire.tid.in_(set([1, tid])))).count():
        raise errors.InputValidationError()

    if field_dict.get('fieldgroup_id', ''):
        ancestors = set(fieldtree_ancestors(session, field_dict['fieldgroup_id']))
        if field_dict['id'] == field_dict['fieldgroup_id'] or field_dict['id'] in ancestors:
            raise errors.InputValidationError("Provided field association would cause recursion loop")

 def _op1(self, values, op, elem):
     elem = self._walk(values, elem)
     if op == "+":
         return + elem
     elif op == "-":
         return - elem
     elif op == "not":
         return expr.not_(elem)
     assert False, "should not happen"

def db_update_fieldoptions(session, field_id, options, language):
    options_ids = [db_update_fieldoption(session, field_id, option['id'], option, language, idx) for idx, option in enumerate(options)]

    if not options_ids:
        return

    to_remove = session.query(models.FieldOption.id).filter(models.FieldOption.field_id == field_id,
                                                            not_(models.FieldOption.id.in_(options_ids)))

    session.query(models.FieldOption).filter(models.FieldOption.id.in_(to_remove.subquery())).delete(synchronize_session='fetch')

 def check_vm_name(self, vm_name, vm_id):
     from convirt.model import DBSession
     from convirt.model.VM import VM
     query = DBSession.query(VM)
     if vm_id:
         query = query.filter(not_(VM.id==vm_id))
     vm = query.filter(VM.name==vm_name).first()
     if vm :
         return dict(success=False,msg='VM <b>'+vm_name+'</b> already exists.')
     return dict(success=True,msg='')