@@ -0,0 +1,2306 @@
+from datetime import timedelta%0A%0Afrom sqlalchemy import func, nulls_last%0Afrom sqlalchemy.engine import Row%0Afrom sqlalchemy.future import select%0Afrom sqlalchemy.sql.elements import and_, not_%0Afrom sqlalchemy.sql.functions import current_timestamp%0A%0Afrom pycroft import config, Config%0Afrom pycroft.model.property import CurrentProperty%0Afrom pycroft.model.session import session%0Afrom pycroft.model.user import User, Membership%0A%0A%0Adef get_archivable_members() -%3E list%5BRow%5D:%0A    %22%22%22Return all the users that qualify for being archived right now.%0A%0A    Selected are those users%0A    - whose last membership in the member_group ended two weeks in the past,%0A    - excluding users who currently have the %60noarchive%60 property.%0A    %22%22%22%0A    # see FunctionElement.over%0A    window_args = %7B'partition_by': User.id, 'order_by': nulls_last(Membership.ends_at),%0A                   'rows': (None, None)%7D%0A    last_mem = (%0A        select(%0A            User.id.label('user_id'),%0A            func.last_value(Membership.id).over(**window_args).label('mem_id'),%0A            func.last_value(Membership.ends_at).over(**window_args).label('mem_end'),%0A        )%0A        .select_from(User)%0A        .distinct()%0A        .join(Membership)%0A        .join(Config, Config.member_group_id == Membership.group_id)%0A    ).cte('last_mem')%0A    stmt = (%0A        select(%0A            User,%0A            last_mem.c.mem_id,%0A            last_mem.c.mem_end,%0A            CurrentProperty.property_name.is_not(None).label('noarchive')%0A        )%0A        .select_from(last_mem)%0A        .join(CurrentProperty,%0A              and_(last_mem.c.user_id == CurrentProperty.user_id,%0A                   CurrentProperty.property_name == 'noarchive',%0A                   not_(CurrentProperty.denied)),%0A              isouter=True)%0A        .join(User, User.id == last_mem.c.user_id)%0A        .filter(last_mem.c.mem_end %3C current_timestamp() - timedelta(days=14))%0A        .order_by(last_mem.c.mem_end)%0A    )%0A%0A    return session.execute(stmt).all()%0A%0A%0Adef get_invalidated_archive_memberships() -%3E list%5BMembership%5D:%0A    %22%22%22Get all memberships in %60to_be_archived%60 of users who have an active %60do-not-archive%60 property.%0A%0A    This can happen if archivability is detected, and later the user becomes a member again,%0A    or if for some reason the user shall not be archived.%0A    %22%22%22%0A    pass%0A

Add first working `get_archivable_members` function