Implement Basic Rota

[GHLasse]

We want to implement the rota which is currently run using Excel on the website.

to get things started, we need the database structure for it.

I suggest the following tables:

• sessions contains all the rota sessions (usually 3 per day) id | start_date | end_date | dem_required | public where
  • id is the session id
  • start_date is a date-time stamp, when the rota-session starts (e.g. 21/02/2018 09:00:00)
  • end_date is a date-time stamp, when the rota-session ends (e.g. 21/02/2018 12:00:00)
  • dem_required is the number of demonstrators required for that session (e.g. 2)
  • public is a boolean if this is a public session as opposed to a training session (e.g. true)
• sessions2staff maps the demonstrators to the sessions id | session_id | staff_id where
  • id is the table id
  • session_id is the id of the session from the session table
  • staff_id is the id of the demonstrator from the staff table
• availability records the availability of demonstrators for sessions id | session_id | staff_id | availability where
  • id is the table id
  • session_id is the id of the session from the session table
  • staff_id is the id of the demonstrator from the staff table
  • availability is a string defining if the person is 'available', 'tentative', 'busy', or 'away'
• events records events other than workshop sessions, such as university closure periods id | start_date | end_date | name | type where
  • id is the table id
  • start_date is a date-time stamp, when the event starts (e.g. 02/04/2018 00:00:00)
  • end_date is a date-time stamp, when the event ends (e.g. 08/04/2018 00:00:00)
  • name is the user friendly name for the event (e.g. 'Easter closure period')
  • type is the type of event as a pre-defined string: 'academic','holidays','closure','internal'
  • availability is a string defining if the person is 'available', 'tentative', 'busy', or 'away'

[GHLasse]

Update database like this:

CREATE TABLE sessions (id INT NOT NULL AUTO_INCREMENT, start_date DATETIME NOT NULL, end_date DATETIME NOT NULL, dem_required TINYINT NOT NULL, public TINYINT NOT NULL, created_at DATETIME NULL, updated_at DATETIME NULL, PRIMARY KEY ( id ));

CREATE TABLE sessions_staff (id INT NOT NULL AUTO_INCREMENT, sessions_id INT NOT NULL, staff_id INT NOT NULL, PRIMARY KEY ( id ), FOREIGN KEY (sessions_id) REFERENCES sessions(id), FOREIGN KEY (staff_id) REFERENCES staff(id));

CREATE TABLE availabilities (id INT NOT NULL AUTO_INCREMENT, sessions_id INT NOT NULL, staff_id INT NOT NULL, availability ENUM('available','tentative','busy','away'), created_at DATETIME NULL, updated_at DATETIME NULL, PRIMARY KEY ( id ), FOREIGN KEY (sessions_id) REFERENCES sessions(id), FOREIGN KEY (staff_id) REFERENCES staff(id));

CREATE TABLE events (id INT NOT NULL AUTO_INCREMENT, start_date DATETIME NOT NULL, end_date DATETIME NOT NULL, name TINYTEXT NOT NULL, type ENUM('academic','holidays','closure','internal') NOT NULL, created_at DATETIME NULL, updated_at DATETIME NULL, PRIMARY KEY ( id ));

[GHLasse]

I created an SQL file with historic rota data. Most of the workflow is completed now. Last features missing before it can go live:

• [x] sort out permissions,
• [x] add option to send rota via email

features that can be added later:

• [ ] improved design
• [x] automatic selection of most suitable demonstrator assignment for rota (see TODO in code)

[GHLasse]

Added automatic demonstrator selection - this exceeds the issue, so I will mark it as closed. Please open new issues to address particular problems with the Rota