schedule.py

import re
import requests
import sqlite3
from ics import Calendar

database = sqlite3.connect("assets/database.sqlite")

url: str = "https://adecons.unistra.fr/jsp/custom/modules/plannings/anonymous_cal.jsp?resources=5241,5232,5242,5245,5236,5474,5436,5449,5446,5439,5440,5441,5442,5443,5445,5447,5401,5366,5391,5435,5448&projectId=8&calType=ical&nbWeeks=4"

rooms: list = [
    ('T01', 'tp', 0),
    ('T02', 'tp', 0),
    ('T03', 'tp', 4),
    ('T11', 'tp', 1),
    ('T20', 'tp', 2),
    ('T21', 'tp', 2),
    ('T22', 'tp', 2),
    ('T23', 'tp', 2),
    ('T24', 'tp', 2),
    ('C1', 'td', 0),
    ('C4', 'td', 0),
    ('C5', 'td', 0),
    ('C6', 'td', 0),
    ('C7', 'td', 0),
    ('C8', 'td', 0),
    ('C9', 'td', 0),
    ('C10', 'td', 0),
    ('C11', 'td', 1),
    ('C13', 'td', 1),
    ('C14', 'td', 1),
    ('C15', 'td', 1),
]


def main():
    setup_tables()
    import_schedule()


def import_schedule(reset: bool = True):
    cursor = database.cursor()
    
    if reset:
        cursor.execute("DELETE FROM schedule")

    cal = Calendar(requests.get(url, allow_redirects=True).text)
    events = [(re.match('([A-Z]\d+)', e.location).group(1),
               e.begin.format("YYYY-MM-DD"),
               e.begin.shift(hours=+2).format("HH:mm"),
               e.end.shift(hours=+2).format("HH:mm"))

              for e in cal.events]

    cursor.executemany(
        "INSERT OR IGNORE INTO schedule VALUES (?,?,?,?)", events)
    database.commit()


def setup_tables():
    cursor = database.cursor()

    # Create 'rooms' table
    cursor.execute("""CREATE TABLE IF NOT EXISTS rooms (
        id TEXT PRIMARY KEY,
        type TEXT NOT NULL,
        location INTEGER NOT NULL
    )""")

    # Populate 'rooms' table
    cursor.executemany("INSERT OR IGNORE INTO rooms VALUES (?,?,?)", rooms)

    # Create 'schedule' table
    cursor.execute("""CREATE TABLE IF NOT EXISTS schedule (
        id TEXT NOT NULL,
        date TEXT NOT NULL,
        start_time TEXT NOT NULL,
        end_time TEXT NOT NULL,
        FOREIGN KEY(id) REFERENCES rooms(id)
    )""")

    database.commit()


if __name__ == "__main__":
    main()