zoom.py

import json
from flask import Flask, render_template, request, g, request_tearing_down
import psycopg2
import psycopg2.extras
import time
from datetime import datetime, timedelta
import sys
import dateutil
import dateutil.tz
import locale

### Flask app object ###

app = Flask(__name__)
app.config.from_pyfile('config.py')
app.jinja_env.lstrip_blocks = True
app.jinja_env.trim_blocks = True

locale.setlocale(locale.LC_TIME, 'cs_CZ')

### Database connection ###

db_connection = None
db = None

# XXX: This is safe only because we never write to the database. Otherwise, we would
# have to handle transactions and roll them back if an exception occurs during
# processing of the request.

def db_connect():
    global db_connection, db
    db_connection = psycopg2.connect(
                host = 'localhost',
                user = app.config['DB_USER'],
                password = app.config['DB_PASSWD'],
                dbname = app.config['DB_NAME'],
            )
    db = db_connection.cursor(cursor_factory=psycopg2.extras.NamedTupleCursor)

def db_query(query, args=()):
    if db is None:
        db_connect()
    try:
        db.execute(query, args)
    except psycopg2.DatabaseError:
        # Reconnect if the connection died (timeout, server restart etc.)
        db_connect()
        db.execute(query, args)

def db_reset_signal(sender, **extra):
    # At the end of every request, we have to close the implicitly opened
    # transaction. Otherwise we end up with serving stale data.
    if db_connection is not None:
        try:
            db_connection.rollback()
        except:
            pass

request_tearing_down.connect(db_reset_signal, app)

### Schedule ###

def get_date():
    """Return a datetime object corresponding to the start of the given date."""
    try:
        d = request.args.get('date', "")
        dt = datetime.strptime(d, "%Y-%m-%d")
    except ValueError:
        dt = datetime.today()
    tz = dateutil.tz.tzlocal()
    return dt.replace(hour=0, minute=0, second=0, microsecond=0, tzinfo=tz)

room_list = {
    'i': [
        ('Mejstříková', 'mejstrikova@zsdobrichovice.cz'),
        ('Barvová', 'barvova@zsdobrichovice.cz'),
        ('Dvořáková', 'dvorakova@zsdobrichovice.cz'),
        ('Filípková', 'filipkova@zsdobrichovice.cz'),
        ('Hopfingerová', 'hopfingerova@zsdobrichovice.cz'),
        ('Macelová', 'macelova@zsdobrichovice.cz'),
        ('Moravcová', 'moravcova@zsdobrichovice.cz'),
        ('Paletová', 'paletova@zsdobrichovice.cz'),
        ('Pekárková', 'pekarkova@zsdobrichovice.cz'),
        ('Švédová', 'svedova@zsdobrichovice.cz'),
        ('Kustošová', 'kustosova@zsdobrichovice.cz'),
        ('Synáčková', 'synackova@zsdobrichovice.cz'),
        ('Vejvodová', 'vejvodova.j@email.cz'),
    ],
}

@app.route('/')
def main_page():
    dt = get_date()
    g.date = dt.strftime("%Y-%m-%d")
    g.dow = dt.strftime("%A")

    hours_arg = request.args.get("hours", "")
    if hours_arg in ["0", "1", "2"]:
        g.hours = int(hours_arg)
    else:
        g.hours = 0

    if g.hours == 0:
        slot_size = 3600
        first_slot = 8*3600
        num_slots = 16
    else:
        slot_size = 3600
        first_slot = 0
        num_slots = 24

    g.rooms = request.args.get("rooms", "")
    if g.rooms not in room_list:
        g.rooms = "i"
    rooms = room_list[g.rooms]

    num_rooms = len(rooms)
    email_to_room_index = { rooms[i][1]: i for i in range(num_rooms) }
    room_box_width = 100
    room_hour_height = 50
    room_slot_height = room_hour_height * slot_size // 3600
    row_offset = 70

    g.total_width = room_box_width * num_rooms
    g.total_height = num_slots * room_slot_height
    g.room_boxes = [{
            "x": row_offset + i * room_box_width + 1,
            "w": room_box_width - 1,
            "h": g.total_height - 1,
            "name": rooms[i][0],
        } for i in range(num_rooms)]

    g.slot_boxes = [{
            "x": row_offset + 1,
            "y": i * room_slot_height + 1,
            "w": num_rooms * room_box_width,
            "h": room_slot_height - 1,
        } for i in range(num_slots)]

    g.slot_labels = [{
            "x": 0,
            "y": i * room_slot_height,
            "w": row_offset - 2,
            "h": room_slot_height - 1,
            "label": (dt + timedelta(seconds = first_slot + i*slot_size)).strftime("%H:%M"),
        } for i in range(num_slots)]

    dt_now = datetime.now(tz=dateutil.tz.tzlocal())
    rel_now = dt_now.timestamp() - dt.timestamp() - first_slot
    if rel_now > 0 and rel_now < num_slots * slot_size:
        g.now = {
            "x": 0,
            "y": int(rel_now / 3600. * room_hour_height),
            "w": row_offset + num_rooms * room_box_width + 20,
        }

    # XXX: No meeting is ever longer than 24 hours
    db_query("""
            SELECT m.meeting_id, m.topic, s.start_time, s.duration, u.email, u.full_name
            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 >= DATE %s - INTERVAL '1 day'
              AND s.start_time <  DATE %s + INTERVAL '1 day'
            ORDER BY u.email, s.start_time
        """,
        (dt, dt))

    g.meetings = []
    prev_room_i = -1
    prev_end_t = None
    for r in db.fetchall():
        i = email_to_room_index.get(r.email, -1)
        if i < 0:
            continue
        # Datetime in the DB is in UTC, but psycopg2 interprets it as local time
        start_dt = r.start_time.replace(tzinfo=dateutil.tz.tz.tzutc())
        start_t = int(start_dt.timestamp())
        end_t = start_t + r.duration*60
        rel_start = start_t - dt.timestamp() - first_slot
        rel_end = rel_start + r.duration*60
        start = max(0, int(rel_start))
        end = min(num_slots * slot_size, int(rel_end))
        app.logger.debug("Meeting: %s start_t=%s start=%s end=%s room_i=%s", r, start_t, start, end, i)
        if start < end:
            coll = (i == prev_room_i and start_t < prev_end_t)
            g.meetings.append({
                "x": row_offset + i * room_box_width + 4 + 10*int(coll),
                "y": int(start / 3600. * room_hour_height),
                "w": room_box_width - 7 - 10*int(coll),
                "h": int((end - start) / 3600 * room_hour_height) - 1,
                "start": time.strftime("%H:%M", time.localtime(start_t)),
                "end": time.strftime("%H:%M", time.localtime(end_t)),
                "topic": r.topic,
                "coll": coll,
            })
            prev_room_i = i
            prev_end_t = end_t

    return render_template('main.html')