General scheduling application
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.
- 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, staff person, or resource (the room or a truck).
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)
- Generate shifts
- select begin and end date
- 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
- 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
- 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
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)