Python expression.and_函数代码示例

def get_user_pending_contact_event(orm, user, contact_id):
    Event_v_all = aliased(Event_v)
    Event_v_new = aliased(Event_v)

    query = orm.query(Event_v_all) \
        .outerjoin((
            Event,
            Event.event_id == Event_v_all.event_id
            )) \
        .join((
            event_contact_v,
            and_(
                event_contact_v.c.event_id == Event_v_all.event_id,
                event_contact_v.c.contact_id == contact_id,
                event_contact_v.c.existence == 1,
                )
            )) \
        .filter(
            Event_v_all.moderation_user_id == user.user_id,
            ~exists().where(and_(
                Event_v_new.event_id == Event_v_all.event_id,
                Event_v_new.a_time > Event_v_all.a_time,
            )),
            or_(
                Event.a_time == None,
                Event_v_all.a_time > Event.a_time,
            )
        ) \
        .order_by(Event_v_all.a_time.desc()) \

    return query.all()

 def update(self):
     user_id = self.context.user_id
     parliament_id = self.context.group_id
     session = Session()
     # add cosigned items
     signed_pi_ids = [sgn.item_id for sgn in
         session.query(domain.Signatory).filter(
             sql.and_(domain.Signatory.user_id == user_id,
                 domain.Signatory.status.in_(
                         get_states("signatory", tagged=["public"])
                     ),
             )
         ).all()
     ]
     if len(signed_pi_ids) > 0:
         self.query = self.query.union(
             session.query(domain.ParliamentaryItem).filter(
                 sql.and_(
                     domain.ParliamentaryItem.parliament_id == parliament_id,
                     domain.ParliamentaryItem.status.in_(self.states),
                     domain.ParliamentaryItem.parliamentary_item_id.in_(
                         signed_pi_ids
                     )
                 )
             )
         )
     self.query = self.query.order_by(
         domain.ParliamentaryItem.parliamentary_item_id.desc()
     )

 def update(self):
     user_id = self.context.user_id
     parliament_id = self.context.group_id
     wf = capi.get_type_info("signatory").workflow
     session = Session()
     # add cosigned items
     signed_pi_ids = [sgn.head_id for sgn in
         session.query(domain.Signatory).filter(
             sql.and_(domain.Signatory.user_id == user_id,
                 domain.Signatory.status.in_(
                     wf.get_state_ids(tagged=["public"])
                 ),
             )
         ).all()
     ]
     if len(signed_pi_ids) > 0:
         self.query = self.query.union(
             session.query(domain.Doc).filter(
                 sql.and_(
                     domain.Doc.parliament_id == parliament_id,
                     domain.Doc.status.in_(self.states),
                     domain.Doc.doc_id.in_(
                         signed_pi_ids
                     )
                 )
             )
         )
     self.query = self.query.order_by(
         domain.Doc.doc_id.desc()
     )

 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 index(self, **kw):
        companies = DBSession.query(Company, Currency).filter(and_(Company.active == 0,
                                                                  Currency.active == 0,
                                                                  Company.currency_id == Currency.id,
                                                                  )).order_by(Company.name)

        subline = DBSession.query(Subline).filter(and_(Subline.active == 0)).order_by(Subline.label)
        saletype = DBSession.query(SaleType).filter(and_(SaleType.active == 0)).order_by(SaleType.label)

        result = {
                'companies' : companies,
                'subline'   : subline,
                'saletype'  : saletype,
                }

        if has_permission('FIN_VIEW_ALL'):  # if FIN team
            teams = DBSession.query(LogicTeam).filter(and_(LogicTeam.active == 0, LogicTeam.for_sale == 0)).order_by(LogicTeam.order).all()
            result['is_fin'] = True
        else:
            # get the user's belonging team
            result['is_fin'] = False
            teams = []
            try:
                mp = DBSession.query(Permission).filter(Permission.permission_name == 'MANAGER_VIEW').one()
                for g in request.identity["user"].groups:
                    if mp in g.permissions and g.logicteams:
                        teams.extend(g.logicteams)
            except:
                traceback.print_exc()
                pass
        result['teams'] = teams
        return result

def find_sccp_speeddial_settings(session):
    query = (session.query(FuncKeyMapping.position.label('fknum'),
                           FuncKeyMapping.label.label('label'),
                           cast(FuncKeyMapping.blf, Integer).label('supervision'),
                           FuncKeyDestCustom.exten.label('exten'),
                           UserFeatures.id.label('user_id'),
                           SCCPDevice.device.label('device'))
             .join(UserFeatures,
                   FuncKeyMapping.template_id == UserFeatures.func_key_private_template_id)
             .join(FuncKeyDestCustom,
                   FuncKeyDestCustom.func_key_id == FuncKeyMapping.func_key_id)
             .join(UserLine,
                   and_(
                       UserLine.user_id == UserFeatures.id,
                       UserLine.main_user == True))
             .join(LineFeatures,
                   UserLine.line_id == LineFeatures.id)
             .join(SCCPLine,
                   and_(
                       LineFeatures.protocol == 'sccp',
                       LineFeatures.protocolid == SCCPLine.id))
             .join(SCCPDevice,
                   SCCPLine.name == SCCPDevice.line)
             .filter(LineFeatures.commented == 0))

    keys = [{'exten': row.exten,
             'fknum': row.fknum,
             'label': row.label,
             'supervision': row.supervision,
             'user_id': row.user_id,
             'device': row.device}
            for row in query]

    return keys

 def p_is_term(p):
     '''is_term : OP_IS string'''
     #TODO: implement starred, watched, owner, reviewer, draft
     username = p.parser.username
     if p[2] == 'reviewed':
         filters = []
         filters.append(gertty.db.approval_table.c.change_key == gertty.db.change_table.c.key)
         filters.append(gertty.db.approval_table.c.value != 0)
         s = select([gertty.db.change_table.c.key], correlate=False).where(and_(*filters))
         p[0] = gertty.db.change_table.c.key.in_(s)
     elif p[2] == 'open':
         p[0] = gertty.db.change_table.c.status.notin_(['MERGED', 'ABANDONED'])
     elif p[2] == 'closed':
         p[0] = gertty.db.change_table.c.status.in_(['MERGED', 'ABANDONED'])
     elif p[2] == 'submitted':
         p[0] = gertty.db.change_table.c.status == 'SUBMITTED'
     elif p[2] == 'merged':
         p[0] = gertty.db.change_table.c.status == 'MERGED'
     elif p[2] == 'abandoned':
         p[0] = gertty.db.change_table.c.status == 'ABANDONED'
     elif p[2] == 'owner':
         p[0] = and_(gertty.db.change_table.c.account_key == gertty.db.account_table.c.key,
                     gertty.db.account_table.c.username == username)
     elif p[2] == 'reviewer':
         filters = []
         filters.append(gertty.db.approval_table.c.change_key == gertty.db.change_table.c.key)
         filters.append(gertty.db.approval_table.c.account_key == gertty.db.account_table.c.key)
         filters.append(gertty.db.account_table.c.username == username)
         s = select([gertty.db.change_table.c.key], correlate=False).where(and_(*filters))
         p[0] = gertty.db.change_table.c.key.in_(s)
     else:
         raise gertty.search.SearchSyntaxError('Syntax error: has:%s is not supported' % p[2])

def users_with_permission(discussion_id, permission, id_only=True):
    from ..models import Discussion
    # assume all ids valid
    db = Discussion.default_db
    user_ids = db.query(User.id).join(
        LocalUserRole, Role, DiscussionPermission, Permission).filter(and_(
            Permission.name == permission,
            LocalUserRole.requested == False,  # noqa: E712
            LocalUserRole.discussion_id == discussion_id,
            DiscussionPermission.discussion_id == discussion_id)
        ).union(
            db.query(User.id).join(
                UserRole, Role, DiscussionPermission, Permission).filter(
                and_(
                    Permission.name == permission,
                    DiscussionPermission.discussion_id == discussion_id))
        ).union(
            db.query(User.id).join(
                UserRole, Role).filter(
                and_(
                    Role.name == R_SYSADMIN,
                    DiscussionPermission.discussion_id == discussion_id))
        ).distinct()
    if id_only:
        return [AgentProfile.uri_generic(id) for (id, ) in user_ids]
    else:
        return db.query(AgentProfile).filter(AgentProfile.id.in_(user_ids)).all()

    def auto_movie(self, q):
        orig_q = q
        q = q.lstrip().lower().split(' ')
        full_words, q = q[:-1], q[-1]
        if 'the' in full_words:
            full_words.remove('the')

        target = literal(' ').op('||')(Movie.title)

        filters = []
        for word in full_words:
            filters.append(target.ilike('%% %s %%' % word))

        filters.append(target.ilike('%% %s%%' % q))
        if len(filters) > 1:
            filters = and_(*filters)
        else:
            filters = filters[0]


        res = self.session.query(Movie.id, Movie.title, Rating.rating)\
                  .outerjoin((Rating, and_(Rating.movie_id == Movie.id,
                                           Rating.user == self.user)))\
                  .filter(filters)\
                  .order_by(func.similarity(func.lower(target), orig_q).desc())\
                  .limit(7).all()
        self.return_json(res)

def get_linked_xreports(document, lang):
    condition_as_child = and_(
        Association.child_document_id == Xreport.document_id,
        Association.parent_document_id == document.document_id
    )
    condition_as_parent = and_(
        Association.child_document_id == document.document_id,
        Association.parent_document_id == Xreport.document_id
    )

    if document.type in [WAYPOINT_TYPE, USERPROFILE_TYPE,
                         ARTICLE_TYPE, IMAGE_TYPE]:
        condition = condition_as_parent
    elif document.type in [ROUTE_TYPE, OUTING_TYPE]:
        condition = condition_as_child

    xreport_ids = get_first_column(
        DBSession.query(Xreport.document_id).
        filter(Xreport.redirects_to.is_(None)).
        join(
            Association, condition).
        group_by(Xreport.document_id).
        all())

    return get_documents_for_ids(
        xreport_ids, lang, xreport_documents_config).get('documents')

    def index( self, **kw ):
        values = {
                  'division_id' : kw.get( 'division_id', None ) or None,
                  'category_id' : kw.get( 'category_id', None ) or None
                  }
        cds = [
               OnclFit.active == 0 , OnclDivision.active == 0 , OnclCategory.active == 0,
               OnclFit.category_id == OnclCategory.id, OnclCategory.division_id == OnclDivision.id,
               ]

        if values['division_id']:
            cds.append( OnclDivision.id == values['division_id'] )
            cats = DBSession.query( OnclCategory ).filter( and_( OnclCategory.active == 0,
                                                      OnclCategory.division_id == values['division_id'] ) ).order_by( OnclCategory.name )
        else:
            cats = []

        if values['category_id']:
            cds.append( OnclCategory.id == values['category_id'] )

        result = DBSession.query( OnclDivision, OnclCategory, OnclFit ).filter( and_( *cds ) ).order_by( OnclDivision.name,
                                                                                                          OnclCategory.name,
                                                                                                          OnclFit.name
                                                                                                          )
        divs = DBSession.query( OnclDivision ).filter( and_( OnclDivision.active == 0 ) ).order_by( OnclDivision.name )
        return {'result' : result, 'divs' : divs , 'cats' : cats, 'values' : values }

def get_linked_articles(document, lang):
    condition_as_child = and_(
        Association.child_document_id == Article.document_id,
        Association.parent_document_id == document.document_id
    )
    condition_as_parent = and_(
        Association.child_document_id == document.document_id,
        Association.parent_document_id == Article.document_id
    )

    if document.type == IMAGE_TYPE:
        condition = condition_as_parent
    elif document.type in [WAYPOINT_TYPE,
                           OUTING_TYPE,
                           ROUTE_TYPE,
                           BOOK_TYPE,
                           XREPORT_TYPE,
                           USERPROFILE_TYPE]:
        condition = condition_as_child

    elif document.type == ARTICLE_TYPE:
        condition = or_(condition_as_child, condition_as_parent)

    article_ids = get_first_column(
        DBSession.query(Article.document_id).
        filter(Article.redirects_to.is_(None)).
        join(
            Association, condition).
        group_by(Article.document_id).
        all())

    return get_documents_for_ids(
        article_ids, lang, article_documents_config).get('documents')

def get_linked_routes(document, lang):
    condition_as_child = and_(
        Association.child_document_id == Route.document_id,
        Association.parent_document_id == document.document_id)
    condition_as_parent = and_(
        Association.child_document_id == document.document_id,
        Association.parent_document_id == Route.document_id)

    if document.type == WAYPOINT_TYPE:
        condition = condition_as_child
    elif document.type in [OUTING_TYPE, IMAGE_TYPE, ARTICLE_TYPE,
                           XREPORT_TYPE]:
        condition = condition_as_parent
    else:
        condition = or_(condition_as_child, condition_as_parent)

    route_ids = get_first_column(
        DBSession.query(Route.document_id).
        filter(Route.redirects_to.is_(None)).
        join(Association, condition).
        group_by(Route.document_id).
        all())

    return get_documents_for_ids(
        route_ids, lang, route_documents_config).get('documents')

def get_user_pending_org_address(orm, user, org_id):
    Address_v_all = aliased(Address_v)
    Address_v_new = aliased(Address_v)

    query = orm.query(Address_v_all) \
        .outerjoin((
            Address,
            Address.address_id == Address_v_all.address_id
        )) \
        .join((
            org_address_v,
            and_(
                org_address_v.c.address_id == Address_v_all.address_id,
                org_address_v.c.org_id == org_id,
                org_address_v.c.existence == 1,
            )
        )) \
        .filter(
            Address_v_all.moderation_user_id == user.user_id,
            ~exists().where(and_(
                Address_v_new.address_id == Address_v_all.address_id,
                Address_v_new.a_time > Address_v_all.a_time,
            )),
            or_(
                Address.a_time == None,
                Address_v_all.a_time > Address.a_time,
            )
        ) \
        .order_by(Address_v_all.a_time.desc())

    return query.all()

def check_venue_bookings( start, end, venue, sitting=None ):
    """
    return all sittings (but sitting if given) a venue is booked for
    in the period
    """
    assert( type(start) == datetime.datetime )
    assert( type(end) == datetime.datetime ) 
    session = Session()
    b_filter = sql.and_(sql.or_( 
                    sql.between(schema.sittings.c.start_date, start, end), 
                    sql.between(schema.sittings.c.end_date, start, end),
                    sql.between(start, schema.sittings.c.start_date, 
                                schema.sittings.c.end_date),
                    sql.between(end, schema.sittings.c.start_date, 
                                schema.sittings.c.end_date)
                        ),
                    schema.sittings.c.venue_id == venue.venue_id)
    if sitting:
        if sitting.sitting_id:
            b_filter = sql.and_(b_filter,
                        schema.sittings.c.sitting_id != sitting.sitting_id)
    query = session.query(BookedVenue).filter(b_filter)
    venues = query.all()
    #session.close()
    return venues