General scheduling application

Free Geek has many scheduling needs. These are currently being handled in several different ways.

• Staff work scheduling is handled by custom written software that is partially integrated with our FGdb database application
• Room use (and truck use) scheduling is handled by google calendars
• Most volunteer scheduling is handled on paper documents
• Most class scheduling is handled on paper documents
• Volunteers internship scheduling is maintained on our wiki and on google calendars.

These need to be handled in a coordinated manner.

Problems with current system

Example 1. Teachers and classes

The classes are scheduled on paper. The room the class will happen in is scheduled on a google calendar. The teacher is scheduled on both the paper class schedule and either the staff schedule or a volunteer intern schedule.

Example 2. Offsite pickups and offsite collection events.

The staff people are scheduled on the staff schedule. The truck is scheduled on a google calendar. The offsite collection events additionally would need to show up on a separate google calendar.

Example 3. Meetings

If staff are attending the meeting it is on the staff schedule. Meetings also need to be on the room use google calendar.

Clearly, organizing all these functions into one system is desirable in order to lessen the likelihood of accidentally double scheduling a volunteer, a staff person, a room, or the truck.

Most pressing is the fact that most of the volunteer scheduling happens on paper, meaning (for example) a build volunteer cannot sign up back in the build area, but has instead to go to the front desk where the schedule is. People can over schedule or write illegibly. Coordinators from the build area need to go up to the front desk to see who's scheduled, etc.

Path forward

This application would be developed in parallel with FGdb with the intention that it would be merged into that project. The first step would be to computerize the paper schedule for the build program. Once done, the functionality should exist to port the paper class schedules and all of the volunteer intern schedules into the same system. (See Milestone One below.)

Integrating the adoption program volunteer schedule would be the next step. The shifts in this schedule can be filled by several people over the day. For example, the receiving area is open from 11-7. There are a few volunteers there at any given time. But one might show up at 11, work until 2 and then be replaced by someone arriving at 2. Another might start at noon and work the rest of the day. Contrast this to the build, prebuild, and advanced build schedules, as well as the classes, where a shift is defined with a start and end time, and is filled with just one volunteer. (See Milestone Two below.)

After that, the next step would be to implement support for events that do not happen each week. So far all the events scheduled happen on a regular weekly schedule. Once we can schedule events that happen on a monthly and/or twice monthly basis, we will be able to accurately deal with meetings and more of the classes. Tracking vacations (time ranges when people are removed from schedule, leaving their shifts empty) and holidays would also be done at this point. (See Milestone Three below.)

Ultimately, we would also assign resources (such as the truck, a projector, or a room) to a shift, and at that point all our current systems' functionality would be in the application. To extend the functionality, we could also add features to track attendance problems (chiefly people who fill up the schedule, but don't show up for their shifts, and to display the information in a bunch of different ways.

The basic approach to the problem is that there would be multiple discrete schedules that are organized by program. These discrete schedules could be grouped together in various ways for display and editing. Each schedule would be a collection of shifts that may or may not have a person assigned to them yet. Shifts would be described in a default shifts system which contains the data necessary to generate the actual shifts for an actual schedule. For example, a default shift would know it happens on a Thursday afternoon from 3:00 to 7:00, and that it has seven slots. When the schedule is generated for the end of July 2009, seven actual shifts would be assigned to Thursday, July 30 from 3:00 to 7:00. If there were default people assigned to the default shift, their names would be assigned to the actual shift. (Eventually the software would leave the person off if s/he's on vacation or skip generating the shift if Free Geek is closed that day. This is not a necessary feature out of the gate, though.)

We are writing this in Ruby on Rails so it can be later easily integrated into our current database application. It will be licensed by Free Geek under the GPL. We want to use code sprints to get as far as we can (especially at the beginning) and once we see something that is functional, we'll begin integrating the code into our main project.

Schema

Diagram

Schema notes

groupings

• a group of schedules that can be displayed together

name

• self-explanatory

groupings_schedules

grouping_id

schedule_id

schedules

• for grouping related shifts

name

• self-explanatory

eligibility_type

• Who can fill shifts on this schedule? Either a LIMITED list of people, or ANY person

shift_type

• Can the shift be split up into chunks? Either RIGID (one person per shift), or SPLITTABLE (multiple people with non-overlapping, consecutive times can fill this shift)

schedules_people

• if the schedule eligibility type indicates that a limited list of people can takes shifts, then a record linking the person to the schedule needs to be here in order for the person to be appear in the selection widget

schedule_id

person_id

people ???

• person specific information could go here -- i.e. first person date, status, reliability index, etc.
• if there's no specific person info to track, we could just use the contacts table from the main database.

contact_id

name

• until hooked up to main app when name will come from contacts table

jobs

• job to be performed during the shift

name

• self-explanatory

schedule_id

default_shifts

• representing a shift or shifts on an "ideal" schedule with no actual date yet attached

effective_date

• first date this shift can appear on an actual schedule

ineffective_date

• last date this shift can appear on an actual schedule

day_of_week

• day of week, 0-6 = sun-sat

start_time

end_time

slot_count

• 1-N for use in producing multiple shifts, default = 1

job_id

schedule_id

ALSO, fields needed for frequency tracking

frequency_type

daily, weekly, monthly, yearly

repeat_every

applies only to weekly, monthly, yearly

weekly_sunday

boolean, applies only to weekly

weekly_monday

boolean, applies only to weekly

weekly_tuesday

boolean, applies only to weekly

weekly_wednesday

boolean, applies only to weekly

weekly_thursday

boolean, applies only to weekly

weekly_friday

boolean, applies only to weekly

weekly_saturday

boolean, applies only to weekly

monthly_subtype

day of month OR week of month

day_of_month

integer 1-31. if >28 and no such day in a given month, then make it last day of month

applies only to monthly -> day of month

monthly_weekday

applies only to monthly -> week of month

monthly_first

applies only to monthly -> week of month

monthly_second

applies only to monthly -> week of month

monthly_third

applies only to monthly -> week of month

monthly_fourth

applies only to monthly -> week of month

monthly_fifth

applies only to monthly -> week of month

monthly_last

applies only to monthly -> week of month

default_assignments

• list of people who will be assigned to shifts when they are generated

starts_at

• start date and time of shift on shift date

ends_at

• end date and time of shift on shift date

default_shift_id

person_id

shifts

• representing a single shift on an actual schedule with a defined date

default_shift_id

job_id

assignments

• representing the filled part of an shift -- that is, someone has signed up for that shift

starts_at

• start date and time of shift on shift date

ends_at

• end date and time of shift on shift date

shift_id

person_id

attendance

• for tracking who actually shows up for shifts and who does not, etc.

assignment_id

person_id

status

• NO CALL NO SHOW, CANCELED, TARDY, and ARRIVED would be useful statuses

resources

• usually the room where a class or meeting takes place, but also things needed for exclusive use of the shift (like the truck or a projector)

name

default_shifts_resources

• if the the default shift needs a resource (i.e. the classroom or the truck), then a record linking the two should be here

resource_id

default_shift_id

shifts_resources

• if the the shift needs a resource (i.e. the classroom or the truck), then a record linking the two should be here

resource_id

shift_id

Sample data values

groupings

name = "Adoption View"

name = "Prebuild View"

name = "Volunteer Interns"

name = "Adoption Classes"

schedules

type = "limited", name = "Adoption Teachers"

type = "all", name = "Adoption Class Students"

type = "limited", name = "Prebuild Interns"

type = "limited", name = "Front Desk Interns"

type = "all", name = "Prebuild"

groupings_schedules

grouping = "Adoption View", schedule = "Adoption Teachers"

grouping = "Adoption View", schedule = "Adoption Class Students"

grouping = "Prebuild View", schedule = "Prebuild Interns"

grouping = "Prebuild View", schedule = "Prebuild"

grouping = "Volunteer Interns", schedule = "Adoption Teachers"

grouping = "Volunteer Interns", schedule = "Prebuild Interns"

grouping = "Volunteer Interns", schedule = "Front Desk Interns"

schedules_people

schedule = "Adoption Teachers" person = "Joe"

schedule = "Adoption Teachers" person = "Mary"

schedule = "Prebuild Interns" person = "Fred"

schedule = "Prebuild Interns" person = "Poindexter"

schedule = "Front Desk Interns" person = "Flip"

schedule = "Front Desk Interns" person = "Bozo"

people

name = "Joe"

name = "Mary"

name = "Fred"

name = "Poindexter"

name = "Flip"

name = "Bozo"

default_shifts

SAMPLE RECORD A

effective_date = 2009-08-01

ineffective_date = NULL (never)

day_of_week = 2 (Tuesday)

start_time = 11:30

end_time = 15:30

slot_count = 7

job => "System Evaluation"

person_id = NULL

schedule => "Prebuild"

default_assignments

starts_at

ends_at

default_shift => SAMPLE RECORD A

person => "Pointdexter"

shifts

SAMPLE RECORD B

default_shift => SAMPLE RECORD A

job => System Evaluation

SAMPLE RECORD C

default_shift => SAMPLE RECORD A

job => System Evaluation

person => "Pointdexter"

assignments

SAMPLE RECORD D

shift => SAMPLE RECORD B

starts_at = 11:30

ends_at = 15:00 (person needs to leave early)

person => Doogie (Doogie doesn't have a schedules_people record, but does exist in the people table)

SAMPLE RECORD E

shift => SAMPLE RECORD C

starts_at = 12:00 (person arrives late)

ends_at = 15:30

person => Pointdexter (this was assigned when the shift was generated, because Pointdexter has a matching record in default_assignments table)

attendance

assignment => SAMPLE RECORD D

person => Doogie

status = TARDY

assignment => SAMPLE RECORD D

person => Poindexter

status = ARRIVED

resources

name = Truck

name = Classroom

default_shifts_resources

resource => Truck

default_shift => a regularly occurring offsite donations shift

resource => Classroom

default_shift => a regularly occurring adoption class

shifts_resources

resource => Truck

shift => a specific adoption class

Functionality

Basic create, retrieve, update, destroy for each table (CRUD)

for most tables an edit page, a display page, and a list page with widgets for destroy on the display and list pages.

Widgets for setting relationships between records in different tables (one to many)

for example, from a shift page, set the person from a select drop down

for example, from a shift page, set the person by typing in their id number

Widgets for setting relationships between records in different tables (many to many)

for example, from a schedule page, add the schedule to an existing or new grouping

for example, from a grouping page, select an existing schedule

Generate shifts

select a schedule or grouping

select begin and end date

warn and then blow away any shifts that already exist that are in the way

loop through dates, checking for day of week

find default shifts that fit

for X = 1 to slot_count

generate a shift

get next default person for this shift. if found, connect them to shift

display default schedules

display schedules

display schedule groupings

select from list of defined schedules or groupings of schedules (or ALL schedules)

show default or show actual schedule?

unless show default, select start and end dates

there should be reasonable defaults, like today and for the next two weeks

find all shifts that match

display in a manner similar to current schedule, with links for filling shifts, editing shifts, etc.

as a table with job and slot number down the left hand side, time across the top, and people's names in the cells

BONUS -- a view with the same, but people's names down the left hand side and put the jobs (and slot number?) in the cells

each table represents a day (like the prebuild and build schedules)

BONUS -- option where each table only represents concurrent shifts of the same type (like the class schedules)

each displayed schedule should have a read only mode (suitable for general viewing, and an edit mode that provides links for editing, deleting, copying, and splitting shifts)

assign shifts to people

copy shifts

edit shifts

split up a shift (BONUS POINTS)

ask for a time point after it starts before it ends and make two shifts out of one

take attendance

when person checks in at front desk, staff person can set a status by selecting an option for that assignment

this inserts a record into the attendance table and sets the status

BONUS -- have a way to create an attendance score and view attendance history from a person's contact record

Milestones

We're going to try to get as far as we can towards milestone one in the first sprint. At the end of the first sprint we should have an idea of if another sprint or two is needed.

Milestone One

Be able to track the build schedules (build and prebuild). This requires:

• all the basic CRUD
• generate shifts
  • support for Weekly recurring shifts
  • support for multiple slots
  • support for default people in shifts (this could slip to next milestone if needed)
• grouping multiple schedules into one
• display schedules
• display schedule groupings
• copy shifts from schedule view
• edit shifts from schedule view
• split shifts from schedule view (this could slip to next milestone if needed)
• delete shifts from schedule view
• make assignments

Milestone Two

Be able to track adoption schedules

• support for splitting shifts up into consecutive chunks (instead of one person per shift)

Milestone Three

Be able to track staff schedules.

• support for Monthly and Yearly recurring shifts
• Vacations
• Holidays (could be done in an earlier milestone if easy enough)

Milestone Four

Other stuff.

• take attendance
• assign resources to shifts

Future

• Able to track staff hours worked (for payroll reporting), using scheduled shifts as a starting point.
• Alternate schedule display formats (monthly, weekly, just one day)
• Schedule view for just one person (across all schedules)