General scheduling application

From FreekiWiki
Jump to navigation Jump to search


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

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.

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 10-6. 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.

Basic snapshot of application

The basic approach to the problem is that there would be multiple discrete chunks of a schedules (called "rosters" in the schema) that are organized by program. These rosters could be grouped together as real world schedules in various ways for display and editing. Most users would interact with the schedule, maybe not even realizing that it is made up of rosters. Each schedule would then be a collection of shifts all related to a specific grouping of rosters.

The shifts may or may not have a person assigned to them. Shifts would be described in a default shifts table 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.)

Software being used:

  • ruby version 1.8
  • postgresql 8.3
  • rails 2.1

Schema

Programs and Activities

Diagram

This is a graph with borders and nodes. Maybe there is an Imagemap used so the nodes may be linking to some Pages.

Schema notes

schedules
  • a group of rosters that can be displayed together
name
  • self-explanatory
schedules_rosters
schedule_id
roster_id
rosters
  • for schedule related shifts
name
  • self-explanatory
eligibility_type
  • Who can fill shifts on this roster? 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)
rosters_people
  • if the roster eligibility type indicates that a limited list of people can takes shifts, then a record linking the person to the roster needs to be here in order for the person to be appear in the selection widget
roster_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
roster_id
default_shifts
  • representing a shift or shifts on an "ideal" roster with no actual date yet attached
effective_at
  • first date this shift can appear on an actual roster
ineffective_at
  • last date this shift can appear on an actual roster
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
roster_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 roster 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, WRONG TIME, 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
holidays
  • used to suppress generation of all shifts in a time frame
name
effective_at
  • date and time stamp to support half day holidays
ineffective_at
  • date and time stamp to support half day holidays
vacations
  • used to suppress generation of all shifts normally assigned to a particular person in a time frame
person_id
effective_at
  • date and time stamp to support half day vacations
ineffective_at
  • date and time stamp to support half day vacations


Sample data values

schedules
name = "Adoption View"
name = "Prebuild View"
name = "Volunteer Interns"
name = "Adoption Classes"
rosters
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"
schedules_rosters
schedule = "Adoption View", roster = "Adoption Teachers"
schedule = "Adoption View", roster = "Adoption Class Students"
schedule = "Prebuild View", roster = "Prebuild Interns"
schedule = "Prebuild View", roster = "Prebuild"
schedule = "Volunteer Interns", roster = "Adoption Teachers"
schedule = "Volunteer Interns", roster = "Prebuild Interns"
schedule = "Volunteer Interns", roster = "Front Desk Interns"
rosters_people
roster = "Adoption Teachers" person = "Joe"
roster = "Adoption Teachers" person = "Mary"
roster = "Prebuild Interns" person = "Fred"
roster = "Prebuild Interns" person = "Poindexter"
roster = "Front Desk Interns" person = "Flip"
roster = "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
roster => "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 rosters_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
holidays
  • closed from 5 pm on Christmas eve through midnight
name = "Christmas Break"
effective_at = '2009-12-24 17:00'
ineffective_at '2009-12-27 00:00'
vacations
  • Doogie will be off Aug 27, 2009
person => Doogie
effective_at = '2009-08-27 00:00'
ineffective_at = '2009-08-27 23:59'

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 roster
for example, from a roster page, select an existing schedule
generate shifts by roster
user selects roster, begin and end dates
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
generate shifts by schedule
user selects a schedule (or ALL schedules), begin and end dates
create a collection of rosters in this schedule
warn and then blow away any shifts that already exist that are in the way
create shifts for each roster (as above, but no need for the warning)
display default rosters
display default schedules
select from list of defined default rosters, default schedules, or ALL
show default schedule
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 roster should have a read only mode (suitable for general viewing, and an edit mode that provides links for editing, deleting, copying, and splitting shifts)
display rosters
display schedules
select from list of defined rosters, schedules, or ALL
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
widget available on a popup or similar linked to from the editable version of the default roster/schedule
similar widget available on a popup or similar linked to from the editable version of the actual roster/schedule
if the roster has a defined list of people associated with it, the widget should be a drop down that allows the user to select people from the predefined list (this should include the current person even if they are not on the predefined list)
edit shifts
widget available on the editable version of the default roster/schedule
similar widget available on the editable version of the actual roster/schedule
brings user to shift edit form
copy shifts
widget available on the editable version of the default roster/schedule
similar widget available on the editable version of the actual roster/schedule
creates a new shift with same values, and brings user to shift edit form for the copy of the shift
split up a shift (BONUS POINTS)
widget available on the editable version of the default roster/schedule
similar widget available on the editable version of the actual roster/schedule
ask for a time point after it starts before it ends and make two shifts out of one by copying the shift and changing the end time of the original and the start time of the copy
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 recurring shifts (Weekly only)
    • support for multiple slots
    • support for default people in shifts (this could slip to next milestone if needed)
  • grouping multiple rosters into one schedule
  • display rosters
  • display schedules
  • 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 fillings shifts with multiple but consecutive people (instead of one person per shift)

Milestone Three

Be able to track staff schedules.

  • additional support for recurring shifts (both types of Monthly recurrences and Yearly recurrences)
  • Vacations
  • Holidays

Milestone Four

Other stuff.

  • take attendance
  • assign resources to shifts
  • Able to track staff hours worked (for payroll reporting), using scheduled shifts as a starting point.

Alternate reports

Could be implemented in any milestone

  • Alternate schedule display formats (monthly, weekly, just one day)
  • Schedule view for just one person (across all schedules)

Additional Notes

Real world issues that have presented themselves since this spec was developed:

  • A volunteer has some kind of special need -- for example, they need a one-on-one helper to read instructions to them. This information is actually recorded in their volunteer record, but is not mentioned on the paper schedule, so the staff person on duty has no idea of the special need until the volunteer shows up, and we find ourselves short staffed. Solution: Find a way to see the volunteer notes from the schedule. This could present a security issue, since these notes should not be shown to everyone who might want to see the schedule. So they should only be displayed for certain users with appropriate rights.

Simplified Schema

This is a graph with borders and nodes. Maybe there is an Imagemap used so the nodes may be linking to some Pages.

RostersSkeds

  • roster_id
  • sked_id

Skeds

  • name

Rosters

  • name

Volunteer Default Shifts

  • effective_at
  • ineffective_at
  • day_of_week
  • start_time
  • end_time
  • slot_count
  • volunteer_task_type_id
  • roster_id

Volunteer Shifts

  • volunteer_default_shift_id
  • date
  • start_time
  • end_time
  • volunteer_task_type_id
  • roster_id
  • slot_number

Assignments

  • volunteer_shift_id
  • contact_id
  • start_time
  • end_time

Remaining Todos

  • show cancelled checkbox label floats away
  • volunteer_events creating/editing interface sucks
    • needs some way to do multiple slots in a single line, real quick like.
    • is otherwise buggy, I think
  • interns can be scheduled for things that there are volunteer slots for, and then it gets a high up slot id or displaces the normal volunteer slot ids. intern and staff shouldnt even have slot numbers.
  • default_assignments skedjul view
  • copy function have option to include {default_,}assignments too
  • make the search page better
  • convert skedjulnators vacations to be on the contact record instead of the worker, and make the volunteer scheduling check volunteers for vacations too.
    • might only want to show this on people who have worker or regular volunteer shift records
  • rework the hours logging to be like the staff hours, and to preseed based on assigned shifts
    • this will probably close #12482
  • restrict time range widget on assignments
    • limit to the range that is within the shifts range unless extendable? (ex: printers can be extended past two with intructor permission)
    • disable any time range changing unless splitable?
  • have a select_visibility for type that allows selection of volunteer tasks id or selection of class_type_id (new table, class_types)
    • will eventually be used for job_id, etc for staff
  • fancy: ajax filter_criteria, ajax form opening (rather than popup), reload schedule display (or relevant parts) on save

Suggested features

These features are suggestions from the users.

  • A waiting list feature. Maybe just a note or something so when someone cancels we can know who would like to be scheduled in their place.
  • Can we get a way to tell who scheduled someone? (This would likely require people punching in the PIN "cashier" code, right?) This may or may not be a good idea.

From Recycling:

  • "No Work" entry = volunteer #84691, but - we need a way to stop folks from turning this into "available" just because "No Work" did not change to "arrived" within 30 minutes. Training might work....
  • "!" = Add some # of extra lines for when we want to be overbooked with volunteers, but prevent volunteer being signed up in those slots without recycling's approval (similar to the "No Walkins" flag). This may mean only letting Recycling enter the volunteer in?

Also:

  • Some comments on contacts are not appropriate for presenting on the schedule. I printed out 100 recent comments and am asking for types of notes people actually take, so we can set up different types of comments, some of which should show up on the vol schedule, and some of which should not.