vc_assistance_migrate.py

from indico.core.db import db
from terminaltables import AsciiTable

db.session.execute('''
    INSERT INTO events.requests (event_id, type, state, data, created_by_id, created_dt)
    SELECT
        rvl.linked_event_id,
        'vc-assistance',
        1,
        json_build_object('comment', ''),
        (SELECT id FROM users.users WHERE is_system),
        rv.created_dt
    FROM roombooking.reservations rv
    JOIN roombooking.reservation_links rvl ON rv.link_id = rvl.id AND rvl.link_type = 2
    WHERE rv.needs_vc_assistance
''')

invalid_reservations = db.session.execute('''
    SELECT
        rv.id,
        rv.room_id,
        format('%s/%s-%s', r.building, r.floor, r.number),
        rv.start_dt,
        rv.end_dt,
        rv.booking_reason,
        rv.booked_for_name,
        ue.email
    FROM roombooking.reservations rv
    JOIN roombooking.rooms r ON r.id = rv.room_id
    JOIN users.users u ON u.id = rv.booked_for_id
    JOIN users.emails ue ON u.id = ue.user_id
    LEFT JOIN roombooking.reservation_links rvl ON rv.link_id = rvl.id
    WHERE rv.needs_vc_assistance
    AND (rv.link_id IS NULL OR rvl.link_type != 2)
    AND rv.start_dt > NOW()
''')

table_data = [['ID', 'Room ID', 'Room name', 'Start date', 'End date', 'Booking reason', 'Booked for', 'Email']]
for resv in invalid_reservations:
    table_data.append(list(resv))

if len(table_data) > 1:
    print(AsciiTable(table_data,
                     'Bookings without a link to an event that need vc assistance in the future').table)


db.session.commit()