General scheduling application

From FreekiWiki
Jump to navigation Jump to search

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

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 schedules
display schedule groupings
select from list of defined schedules or groupings of schedules
select start and end dates
find all shifts that match
display in a manner similar to current schedule, with links for filling shifts, editing shifts, etc.
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
  • delete shifts from schedule view
  • make assignments

Milestone Two

Be able to track adoption schedules

  • support for splitting shifts up

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)
Retrieved from "http://wiki.freegeek.org/index.php?title=General_scheduling_application&oldid=41098"