schedule.py

"""Create a schedule from a spreadsheet."""

import datetime
import os

import sqlalchemy
from sqlalchemy import (
    Column, Date, Enum, ForeignKey, Integer, String, Table, Text, Time,
)
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import backref, relationship, sessionmaker
from sqlalchemy.orm.exc import NoResultFound
import xlrd

MEALS = 'Registration', 'Lunch'
BREAKS = 'BREAK',
KEYNOTES = 'Keynote',
LIGHTNING = 'Lightning Talks',
SPECIAL_TALKS = MEALS + BREAKS + KEYNOTES + LIGHTNING

DATABASE_URI = os.environ['DATABASE_URI']

Base = declarative_base()

engine = sqlalchemy.create_engine(DATABASE_URI)
Session = sessionmaker(bind=engine, autocommit=False)
session = Session()


rooms_slots = Table(
    'rooms_slots',
    Base.metadata,
    Column('slot_id', Integer, ForeignKey('slots.id')),
    Column('room_id', Integer, ForeignKey('rooms.id')),
)


class Day(Base):
    """Days of the event."""

    __tablename__ = 'days'

    id = Column(Integer, primary_key=True)
    date = Column(Date)

    event_id = Column(Integer, ForeignKey('events.id'))
    event = relationship('Event', backref=backref('days', lazy='dynamic'))


class Duration(Base):
    """Talk durations."""

    __tablename__ = 'durations'

    id = Column(Integer, primary_key=True)
    name = Column(String(255), nullable=False)
    duration = Column(Integer, nullable=False)

    def __str__(self):
        return '{} minutes'.format(self.duration)


class Event(Base):
    """Events."""

    __tablename__ = 'events'

    id = Column(Integer, primary_key=True)


class Presentation(Base):
    """Talk presentations."""

    __tablename__ = 'presentations'

    id = Column(Integer, primary_key=True)

    slot_id = Column(Integer, ForeignKey('slots.id'), nullable=False)
    slot = relationship('Slot', backref=backref('presentation', uselist=False))

    talk_id = Column(Integer, ForeignKey('talks.id'), nullable=False)
    talk = relationship('Talk', backref=backref('presentation', uselist=False))


class Room(Base):
    """Rooms of talks."""

    __tablename__ = 'rooms'

    id = Column(Integer, primary_key=True)
    name = Column(String(255), nullable=False)
    order = Column(Integer, nullable=False)

    def __str__(self):
        return self.name


class Slot(Base):
    """Talk slots."""

    __tablename__ = 'slots'

    id = Column(Integer, primary_key=True)
    kind = Column(
        Enum('break', 'meal', 'keynote', 'talk', 'tutorial', 'lightning'),
        nullable=False,
    )
    content_override = Column(Text)
    start = Column(Time, nullable=False)
    end = Column(Time, nullable=False)

    day_id = Column(Integer, ForeignKey('days.id'), nullable=False)
    day = relationship('Day', backref=backref('slots', lazy='dynamic'))

    rooms = relationship(
        'Room',
        secondary=rooms_slots,
        backref=backref('slots', lazy='dynamic'),
    )


class Talk(Base):
    """Talk submissions."""

    __tablename__ = 'talks'

    id = Column(Integer, primary_key=True)
    name = Column(String(255), nullable=False)
    status = Column(Enum('draft', 'accepted'), nullable=False)

    duration_id = Column(Integer, ForeignKey('durations.id'), nullable=False)
    duration = relationship('Duration')

    event_id = Column(Integer, ForeignKey('events.id'), nullable=False)
    event = relationship('Event', backref=backref('talks', lazy='dynamic'))

    def __str__(self):
        return self.name


def date_and_time(sheet, row, date_column, time_column):
    return datetime.datetime(
        *get_date(sheet, row, date_column)[:3],
        *get_date(sheet, row, time_column)[3:],
    )


def get_date(sheet, row, column):
    """Return the date inside a cell."""
    return xlrd.xldate_as_tuple(row[column].value, sheet.book.datemode)


def get_or_create(cls, **filters):
    """Get or create a model instance."""
    try:
        result = session.query(cls).filter_by(**filters).one()
    except NoResultFound:
        result = cls(**filters)
        session.add(result)

    return result


EVENT = session.query(Event).filter(Event.id == 3).one()

book = xlrd.open_workbook('schedule.xlsx')
sheet = book.sheet_by_name('Schedule')
headers, *rows = sheet.get_rows()
rooms = [
    get_or_create(Room, name=column.value, order=i)
    for i, column
    in enumerate(headers[2:]) if column.value
]
for I, row in enumerate(rows):
    value = row[0].value
    if value in ('', 'NIGHT'):
        continue

    if any(column.value for column in row[2:]):
        dt = date_and_time(sheet, row, 0, 1)
        day = get_or_create(Day, date=dt.date(), event=EVENT)
        print('On', day.date, 'at', dt.time(), '\b:')

        for i, column in enumerate(row[2:]):
            talk_title = column.value
            if not talk_title:
                continue

            if talk_title.startswith(SPECIAL_TALKS):
                override, extra = talk_title.split(':')
                if override.startswith(KEYNOTES):
                    talk = session.query(Talk).filter(Talk.name == extra).one()

                    duration = talk.duration.duration

                    slot = get_or_create(
                        Slot,
                        kind='keynote',
                        day=day,
                        start=dt.time(),
                        end=(dt + datetime.timedelta(minutes=duration)).time(),
                    )
                    get_or_create(Presentation, slot=slot, talk=talk)

                    print('\t', 'Keynote:', talk.name, 'for', talk.duration)

                else:
                    duration = int(extra)
                    if override.startswith(MEALS):
                        kind = 'meal'
                    elif override.startswith(BREAKS):
                        kind = 'break'
                    elif override.startswith(LIGHTNING):
                        kind = 'lightning'
                        continue

                    slot = get_or_create(
                        Slot,
                        kind=kind,
                        content_override=override,
                        day=day,
                        start=dt.time(),
                        end=(dt + datetime.timedelta(minutes=duration)).time(),
                    )

                    print(
                        '\t',
                        override,
                        '(' + kind + ') for',
                        duration,
                        'minutes',
                    )

                slot.rooms = rooms
            else:
                talk = session.query(Talk).filter(
                    Talk.name == talk_title).one()

                print('\t', talk, 'in', rooms[i], 'for', talk.duration)

                end = dt + datetime.timedelta(
                    minutes=talk.duration.duration)

                try:
                    slot = session.query(Slot).filter(
                        Slot.kind == 'talk',
                        Slot.day == day,
                        Slot.start == dt.time(),
                        Slot.end == end.time(),
                        Slot.rooms.contains(rooms[i]),
                    ).one()
                except NoResultFound:
                    slot = Slot(
                        kind='talk',
                        day=day,
                        start=dt.time(),
                        end=end.time(),
                        rooms=[rooms[i]],
                    )
                    session.add(slot)

                get_or_create(Presentation, slot=slot, talk_id=talk.id)

session.commit()