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

 def get_friendship_from_database( self, userA, userB, crawl_id=None ):
     """
     Input
         userA, userB: user objects.
         crawl_id: (optional) if included, only looks for the friendship 
             in the particular run. otherwise, all copies of the friendship
             are returned.
     
     Retrieves the specified friendship from the database. There may be
     multiple matches because the same friendship maybe have been mined
     and stored on multiple occasions.
     
     Output  List of friendship objects. If none found, list length is 0.
     """
     userA_id = userA.id
     userB_id = userB.id
     if crawl_id is not None:
         results = self.session.query( Friendship ).filter( and_(
             Friendship.userA_id==userA_id,
             Friendship.userB_id==userB_id,
             Friendship.crawl_id==crawl_id ) ).all()
     else:
         results = self.session.query( Friendship ).filter( and_(
             Friendship.userA_id==userA_id,
             Friendship.userB_id==userB_id ) ).all()
     return results 

    def all_watched_subjects(self):
        from ututi.model import Subject
        subjects_table = meta.metadata.tables['subjects']
        umst = meta.metadata.tables['user_monitored_subjects']
        directly_watched_subjects = meta.Session.query(Subject)\
            .join((umst,
                   and_(umst.c.subject_id==subjects_table.c.id,
                        umst.c.subject_id==subjects_table.c.id)))\
            .filter(and_(umst.c.user_id == self.id,
                         umst.c.ignored == False))

        user_ignored_subjects = meta.Session.query(Subject)\
            .join((umst,
                   and_(umst.c.subject_id==subjects_table.c.id,
                        umst.c.subject_id==subjects_table.c.id)))\
            .filter(and_(umst.c.user_id == self.id,
                         umst.c.ignored == True))

        gwst = meta.metadata.tables['group_watched_subjects']
        gmt = meta.metadata.tables['group_members']
        gt = meta.metadata.tables['groups']
        group_watched_subjects = meta.Session.query(Subject)\
            .join((gwst,
                   and_(gwst.c.subject_id==subjects_table.c.id,
                        gwst.c.subject_id==subjects_table.c.id)))\
            .join((gmt, gmt.c.group_id == gwst.c.group_id))\
            .join((gt, gmt.c.group_id == gt.c.id))\
            .filter(gmt.c.user_id == self.id)
        return directly_watched_subjects.union(
            group_watched_subjects.except_(user_ignored_subjects))\
            .order_by(Subject.title.asc())\
            .all()

 def to_remove(self):
     # first, remove albums deleted from disk
     while True:
         album = (
             self._session.query(Album)
             .filter(
                 and_(
                     Album.disk_generation != self._disk_gen,
                     Album.ipod_generation != self._ipod_gen + 1,
                     Album.loaded == True,
                 )
             )
             .order_by(Album.disk_generation.asc())
             .first()
         )
         if not album:
             break
         album.loaded = False
         self._session.commit()
         yield album
     # next, remove any albums not loaded in this load
     while True:
         album = (
             self._session.query(Album)
             .filter(and_(Album.ipod_generation != self._ipod_gen + 1, Album.loaded == True))
             .order_by(Album.disk_generation.asc())
             .first()
         )
         if not album:
             break
         album.loaded = False
         self._session.commit()
         yield album

    def del_relation(category, tag_id_list):
        for tag_id in tag_id_list:
            if category.current_level == 1:
                # 已经存在关系?
                relation = TagCategoryRelation.query.filter(
                    and_(TagCategoryRelation.category1_id == category.id, TagCategoryRelation.category2_id == 0,
                         TagCategoryRelation.category3_id == 0, TagCategoryRelation.tag_id == tag_id)).first()
                if relation:
                    relation.num_entries = TagCategoryRelation.__table__.c.num_entries - 1

            if category.current_level == 2:
                relation = TagCategoryRelation.query.filter(
                    and_(TagCategoryRelation.category1_id == category.path[0].id,
                         TagCategoryRelation.category2_id == category.id, TagCategoryRelation.category3_id == 0,
                         TagCategoryRelation.tag_id == tag_id)).first()
                if relation:
                    relation.num_entries = TagCategoryRelation.__table__.c.num_entries - 1

            if category.current_level == 3:
                relation = TagCategoryRelation.query.filter(
                    and_(TagCategoryRelation.category1_id == category.path[0].id,
                         TagCategoryRelation.category2_id == category.path[1].id,
                         TagCategoryRelation.category3_id == category.id, TagCategoryRelation.tag_id == tag_id)).first()
                if relation:
                    relation.num_entries = TagCategoryRelation.__table__.c.num_entries - 1

        db.session.commit()

 def create_expression(self):
     if self.last_result is None:
         try:
             expr = select(self.table.columns).order_by(*[safe_collate(self.table.columns[nm], None) for nm in self.sort_key])
             if self.starter is not None:
                 expr = expr.where(and_(self.starter, self.filter))
             else:
                 expr = expr.where(self.filter)
         except:
             raise
     else:
         try:
             where_clause = vector_greater_than([self.table.columns[nm] for nm in self.sort_key], \
                                               [self.last_result[n] for n in self.sort_index])
             expr = (select(self.table.columns).order_by(*[safe_collate(self.table.columns[nm], None) for nm in self.sort_key]) \
                    .where(and_(where_clause, self.filter)))
         except:
             raise
     
     if self.limit_per is not None:
         expr = expr.limit(self.limit_per)
         
     if self.stream:
         expr = expr.execution_options(stream_results=True)
     
     return expr