zoom.py 6.52 KB
Newer Older
Martin Mareš's avatar
Martin Mareš committed
1
import json
2
from flask import Flask, render_template, request, g, request_tearing_down
Martin Mareš's avatar
Martin Mareš committed
3
4
5
import psycopg2
import psycopg2.extras
import time
Martin Mareš's avatar
Martin Mareš committed
6
from datetime import datetime, timedelta
Martin Mareš's avatar
Martin Mareš committed
7
import sys
8
9
import dateutil
import dateutil.tz
10
import locale
Martin Mareš's avatar
Martin Mareš committed
11
12
13
14
15

### Flask app object ###

app = Flask(__name__)
app.config.from_pyfile('config.py')
16
17
app.jinja_env.lstrip_blocks = True
app.jinja_env.trim_blocks = True
Martin Mareš's avatar
Martin Mareš committed
18

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

Martin Mareš's avatar
Martin Mareš committed
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
### 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)

50
51
52
53
54
55
56
57
58
59
60
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)

Martin Mareš's avatar
Martin Mareš committed
61
62
63
### Schedule ###

def get_date():
64
    """Return a datetime object corresponding to the start of the given date."""
Martin Mareš's avatar
Martin Mareš committed
65
66
    try:
        d = request.args.get('date', "")
67
        dt = datetime.strptime(d, "%Y-%m-%d")
Martin Mareš's avatar
Martin Mareš committed
68
    except ValueError:
69
70
71
        dt = datetime.today()
    tz = dateutil.tz.tzlocal()
    return dt.replace(hour=0, minute=0, second=0, microsecond=0, tzinfo=tz)
Martin Mareš's avatar
Martin Mareš committed
72

Martin Mareš's avatar
Martin Mareš committed
73
74
room_list = {
    'i': [
Martin Mareš's avatar
Martin Mareš committed
75
76
77
        ('Mejstříková', 'mejstrikova@zsdobrichovice.cz'),
        ('Barvová', 'barvova@zsdobrichovice.cz'),
        ('Dvořáková', 'dvorakova@zsdobrichovice.cz'),
Martin Mareš's avatar
Martin Mareš committed
78
79
80
81
82
83
84
85
86
87
        ('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'),
Martin Mareš's avatar
Martin Mareš committed
88
89
    ],
}
Martin Mareš's avatar
Martin Mareš committed
90
91
92
93

@app.route('/')
def main_page():
    dt = get_date()
94
95
    g.date = dt.strftime("%Y-%m-%d")
    g.dow = dt.strftime("%A")
Martin Mareš's avatar
Martin Mareš committed
96
97

    hours_arg = request.args.get("hours", "")
Martin Mareš's avatar
Martin Mareš committed
98
    if hours_arg in ["0", "1", "2"]:
Martin Mareš's avatar
Martin Mareš committed
99
100
101
102
103
        g.hours = int(hours_arg)
    else:
        g.hours = 0

    if g.hours == 0:
Martin Mareš's avatar
Martin Mareš committed
104
105
106
        slot_size = 3600
        first_slot = 8*3600
        num_slots = 16
Martin Mareš's avatar
Martin Mareš committed
107
    else:
Martin Mareš's avatar
Martin Mareš committed
108
109
110
        slot_size = 3600
        first_slot = 0
        num_slots = 24
Martin Mareš's avatar
Martin Mareš committed
111

Martin Mareš's avatar
Martin Mareš committed
112
113
114
115
116
    g.rooms = request.args.get("rooms", "")
    if g.rooms not in room_list:
        g.rooms = "i"
    rooms = room_list[g.rooms]

Martin Mareš's avatar
Martin Mareš committed
117
118
119
120
    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
Martin Mareš's avatar
Martin Mareš committed
121
122
123
    room_slot_height = room_hour_height * slot_size // 3600
    row_offset = 70

Martin Mareš's avatar
Martin Mareš committed
124
    g.total_width = room_box_width * num_rooms
Martin Mareš's avatar
Martin Mareš committed
125
    g.total_height = num_slots * room_slot_height
Martin Mareš's avatar
Martin Mareš committed
126
    g.room_boxes = [{
Martin Mareš's avatar
Martin Mareš committed
127
            "x": row_offset + i * room_box_width + 1,
Martin Mareš's avatar
Martin Mareš committed
128
129
130
131
132
            "w": room_box_width - 1,
            "h": g.total_height - 1,
            "name": rooms[i][0],
        } for i in range(num_rooms)]

Martin Mareš's avatar
Martin Mareš committed
133
134
135
136
137
138
139
140
141
142
143
144
145
146
    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)]
Martin Mareš's avatar
Martin Mareš committed
147

Martin Mareš's avatar
Martin Mareš committed
148
149
150
151
152
153
154
155
156
    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,
        }

Martin Mareš's avatar
Martin Mareš committed
157
158
    # XXX: No meeting is ever longer than 24 hours
    db_query("""
Martin Mareš's avatar
Martin Mareš committed
159
            SELECT m.meeting_id, m.topic, s.start_time, s.duration, u.email, u.full_name
Martin Mareš's avatar
Martin Mareš committed
160
            FROM zoom_meetings m
Martin Mareš's avatar
Martin Mareš committed
161
162
163
164
165
            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
Martin Mareš's avatar
Martin Mareš committed
166
        """,
167
        (dt, dt))
Martin Mareš's avatar
Martin Mareš committed
168
169

    g.meetings = []
170
171
    prev_room_i = -1
    prev_end_t = None
Martin Mareš's avatar
Martin Mareš committed
172
173
174
175
    for r in db.fetchall():
        i = email_to_room_index.get(r.email, -1)
        if i < 0:
            continue
176
177
178
        # 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())
Martin Mareš's avatar
Martin Mareš committed
179
        end_t = start_t + r.duration*60
Martin Mareš's avatar
Martin Mareš committed
180
        rel_start = start_t - dt.timestamp() - first_slot
Martin Mareš's avatar
Martin Mareš committed
181
182
        rel_end = rel_start + r.duration*60
        start = max(0, int(rel_start))
Martin Mareš's avatar
Martin Mareš committed
183
        end = min(num_slots * slot_size, int(rel_end))
184
        app.logger.debug("Meeting: %s start_t=%s start=%s end=%s room_i=%s", r, start_t, start, end, i)
Martin Mareš's avatar
Martin Mareš committed
185
        if start < end:
Martin Mareš's avatar
Martin Mareš committed
186
            coll = (i == prev_room_i and start_t < prev_end_t)
Martin Mareš's avatar
Martin Mareš committed
187
            g.meetings.append({
Martin Mareš's avatar
Martin Mareš committed
188
                "x": row_offset + i * room_box_width + 4 + 10*int(coll),
Martin Mareš's avatar
Martin Mareš committed
189
                "y": int(start / 3600. * room_hour_height),
Martin Mareš's avatar
Martin Mareš committed
190
                "w": room_box_width - 7 - 10*int(coll),
Martin Mareš's avatar
Martin Mareš committed
191
                "h": int((end - start) / 3600 * room_hour_height) - 1,
Martin Mareš's avatar
Martin Mareš committed
192
193
194
                "start": time.strftime("%H:%M", time.localtime(start_t)),
                "end": time.strftime("%H:%M", time.localtime(end_t)),
                "topic": r.topic,
Martin Mareš's avatar
Martin Mareš committed
195
                "coll": coll,
Martin Mareš's avatar
Martin Mareš committed
196
            })
197
198
            prev_room_i = i
            prev_end_t = end_t
Martin Mareš's avatar
Martin Mareš committed
199
200

    return render_template('main.html')