find-collisions.py

#!/usr/bin/env python3

import configparser
import psycopg2
import psycopg2.extras
import datetime
import dateutil.tz
import time

config = configparser.ConfigParser()
config.read('zoom.ini')

db_conn = psycopg2.connect(dbname=config['db']['name'], user=config['db']['user'], password=config['db']['passwd'], host="127.0.0.1")
db = db_conn.cursor(cursor_factory=psycopg2.extras.NamedTupleCursor)

db.execute("""
    SELECT
        m.meeting_id, m.topic, u.uid, u.full_name, s.occurrence_id, s.start_time, s.duration
    FROM
        zoom_meetings m
        JOIN zoom_users u ON u.uid = m.host_uid
        JOIN zoom_schedule s ON s.mid = m.mid
    WHERE
        s.start_time >= NOW()
    ORDER BY u.uid, s.start_time, m.mid
    """)

class Meeting:

    def __init__(self, row):
        for f in row._fields:
            setattr(self, f, getattr(row, f))
        self.start_time = self.start_time.replace(tzinfo=dateutil.tz.tz.tzutc()).astimezone(dateutil.tz.gettz())
        self.end_time = self.start_time + datetime.timedelta(minutes=self.duration)
        self.time_range = self.start_time.strftime("%Y-%m-%d %H:%M") + "-" + self.end_time.strftime("%H:%M")

    def __str__(self):
        return "Meeting(" + ", ".join([ k + ":" + str(getattr(self, k)) for k in dir(self) if k[0] != '_' ]) + ")"

last_uid = None
running = []

for row in db:
    m = Meeting(row)
    # print(m)

    if m.uid != last_uid:
        last_uid = m.uid
        running = []

    while running and running[0].end_time <= m.start_time:
        running.pop(0)

    colls = [ r for r in running if r.end_time > m.start_time + datetime.timedelta(minutes=5) ]
    if colls:
        print(f"Collison for {m.time_range} {m.topic} [{m.meeting_id}:{m.occurrence_id} {m.full_name}]")
        for c in colls:
            print(f"\t{c.time_range} {c.topic} [{c.meeting_id}:{c.occurrence_id}]")
        print()

    pos = sum(1 for r in running if r.end_time <= m.end_time)
    running.insert(pos, m)