Staff hours tracking application

As a result in changes to our workers comp insurance, paid staff will likely need to track their hours by job task on a daily basis. Each job will fall into one of (probably) four categories (still to be determined).

Tracking categories

Assuming we really do need to do this, an opportunity to track other things associated with job tasks. Here are the obvious things that would be useful to track:

Workers Comp Categories

As defined by our workers compensation provider. There might be four categories, including:

1. Truck driving, or riding along. (This is self explanatory.)
2. Office Work. (Administrative, non-production work done in a designated office area, meeting room, reception, or the classroom.)
3. Recycling. (Work performed in an area that primarily involves handling anything not yet triaged into KEEP or RECYCLE, or already determined to be destined for recycling. This includes Receiving, Prebuild, Testing, and the Warehouse areas.)
4. The default category. (Most production and sales, and anything not defined above.)

Program Categories

We use these to report program costs to the IRS each year, but they could also be used in grant writing in the future.

See Programs and Activities for current scheme of programs.

Production and Income Stream Categories

This would be categories that help us define what costs are associated with the production of something that's going for sale related income or for mission related disbursement.

The actual categories here remain to be defined and will likely require some experimentation and tweaking once a preliminary design has been laid out.

Worker types are succinctly described on the Staff Ratios page. A better description of each can be found on the Staff Categories page.

Diagram

The workers_worker_types records and the three categories associatiated with job tasks all can change over time. So these tables will need fields for effective dates and ineffective dates.

Possible need: Depending on what we determine to be a manageable amount of job_tasks to deal with, we might also find ourselves needing to have a many-to-many relation between the category table (for example, programs) and the job tasks table. That is, a job task might need to have multiple programs that it is associated with. If we need to do this (and I don't want to if we don't have to) we would need to determine what percent of hours spent at a given job is assigned to each program (or other category). The percents associated with a given job task would need to total up to 100% or a similar scheme would need to be implemented.

User interface

The simplest, most convenient possible user interface needs to be designed. Workers will need to be able to very easily enter there hours as accurately as possible, seeing only the date (default of today), and multiple lines with the jobs and the hours. Useful feedback to give the user in the UI includes:

What they need to know

• if they're gonna go over their weekly ceiling.
• if they're gonna go under their pay periodly floor.

Right?

holiday credit = ceiling for week / 5

so far = everything from the beginning of the period of interest to max(today, the day they're entering for).

• Hours for this day: Z (total of whats entered on screen)
• Hours for this week: Y (Z, the week so far, plus holiday credit)
• Maximum hours for this week: X (ceiling_hours)
• Overtime: W (max(Y - Z, 0))
• Range this far in pay period: (for days that have passed, get the ceiling and floor, add them up)
• Hours this far in pay period plus holiday in total for month:
• PTO: max(min(range) - total, 0)

WRONG

• How many total hours are currently being displayed for the date they are entering (adding up whatever they type in)
• Whether the total hours are more or less than what is scheduled for that day.
• Hours recorded for that week (Monday AM to Sunday PM) so far.
• Hours recorded for that calendar month so far (and how close they are to the monthly limit).

Reports needed

Individual Pay Period Report

This should show (in calendar format) the total number of hours a selected worker worked on each day. This is similar to the old database's report (which always assumed a calendar month pay period). This will be reviewed at the end of each pay period by payroll staff to see if it seems reasonable that all hours have been reported.

AND/OR

A table version of the above with each worker on one line, and each of the seven weekdays on each column. Totals in an eighth column and at the bottom row of the report.

We need either one of the two at rollout, and both of them sooner or later.

Payroll report

(Needed to report hours to payroll when getting checks issued. This would typically be run When reporting hours to our payroll service, we need to be able to list the workers for a pay period alphabetically by surname and report the following numbers for each worker:

1. Hours worked (actually logged as work hours)
2. PTO hours taken
3. Overtime hours worked
4. Holiday hours credited.

A summary section should contain:

• Grand total for each of the above numbers
• A line for each type of worker (collective, intern, etc.) showing subtotals for each of the above numbers.

A general hours report that works something like the income report would be useful. (This would not contain the worker line by listed line above.) The user would select a date range, and a total number of hours would be displayed. One section of the report would show a breakdown by worker type (one type per column) and workers compensation category (one category per row). The next section would show the same, but by worker type (column) and program category (row). The next by worker type (column) and production and income stream category (row). Each section would have subtotals along the bottom, and subtotals as a final column. A grand total sum line would be at the bottom. The exact layout of this report is not necessarily this, but the information explained here should be displayed. Besides just asking for data on a date range, the report could be limited to the type of worker, the type of income stream, workers comp category, or program category as well.

Trend reports tracking hours over time by worker type, program, and production/income stream would prove very useful in planning staffing needs and estimating averages.

Floor and Ceiling numbers, Other calculations

We've been paying people monthly. At some point we might want to change to a fourteen day pay schedule where the pay period is two weeks long instead of from the first to the end of the month. We should code assuming that we'll use a date range, but start with the default of the first of the month to the end of the month as the date range.

Weeks are the seven day period from Monday AM (just after midnight) to Sunday PM (just before midnight).

For collective members, people are salaried. The amount of time scheduled is actually two numbers, a "floor" number and a "ceiling" number. Per week, the floor number is 37 hours/week for full time collective members and the ceiling number is 40. Part time collective members have different floors and ceiling, where the floor is approximately this: floor = ceiling * 37/40. The ceiling number is stored in the database for each of the seven weekdays for each of the workers. An aggregate floor and ceiling number is also stored in the database for a typical week. The total ceiling number for a typical week should match the seven weekday values added together. The floor number for any given weekday could be calculated by multiplying that weekday's ceiling value by the aggregate floor divided by the aggregate ceiling.

If someone works more than their ceiling hours in a week then they're working overtime. This needs to be tracked.

For non-collective members, people are paid straight hourly. Overtime kicks in at 40 hours per week. This can be implemented by giving them a floor number equal to whatever typical scheduled amount works and a ceiling number of 40.

If someone works less than their floor hours in a monthly pay period then they need to used PTO time. This needs to be tracked. We are only calculating how much the worker will "eat into" their already accumulated PTO. The actual accumulation of PTO is calculated and stored by our payroll services company.

However not all workers get PTO. Some non-collective members get PTO time. All collective members do. A flag in the worker's record should indicate if PTO needs to be calculated.

Assuming whole day holidays, each holiday is calculated as 1/5 of a typical week ceiling for the given worker. (This may be more or fewer hours than they would normally be scheduled for on the holiday's weekday.) For full time workers 1/5 of a full week is 8 hours per holiday. Currently only collective members get holiday credit. This may change in the future, so a flag should exist in the worker record to indicate if the worker get holiday benefits or not.

When displaying useful information to the user, numbers good to display will be:

1. Number of hours they've logged so far this week, next to the floor and ceiling numbers for the typical week.
2. Number of hours in excess of their scheduled or ceiling hours per week (overtime).
   • (Workers comp rules set the ceiling number to 40 hours.)
3. Number of hours short of their scheduled or floor hours per month (PTO time).

To calculate PTO

This is done monthly.

1. Determine the floor hours for the month for the worker (A).
   • (This is done by adding up all the scheduled hours that occur in a month regardless of holiday status. Some months will have more Tuesdays or fewer Saturdays, for example, and people may be scheduled to work more or less on any standard weekday.)
2. Determine the ceiling hours for a typical work week for the worker (B).
3. Determine any holiday credit (1/5 of B per holiday occurring in that month) (C).
4. Determine how many hours logged in the whole month (D).
5. Add (D) + (C) for a total hours credited (E).
6. If (E) is less than (A), the difference is the amount of PTO used.

To calculate Overtime

This is calculated per week, but must be reported monthly. Some weeks straddle two months, however. Assuming a given week straddles two calender months, it is reported in the month that the week ends.

1. Determine the ceiling hours for a typical work week for the worker (A).
2. Determine any holiday credit (1/5 of A for any holiday occurring that week) (B).
3. Determine how many hours worked (C).
4. Add (C) + (B) for a total hours credited (D).
5. If (D) is more than (A), the difference is the amount of overtime used.

Relationship with scheduling application

This could eventually link in to the General scheduling application. The scheduled job tasks could be used to pre-fill in values for a given day, so the user would only need to correct for unscheduled work time (admin) and changes that came up during the day. This feature is not necessary immediately but should be part of a long term vision.

At the outset, we will need to know a little about scheduling even if that application is not ready, however. We'll need to know the floor and ceiling numbers for each person so that we can calculate how many overtime hours are being accumulated and display that.

Relationship with volunteer tasks application

Volunteers already have a list of volunteer tasks that they record as they finish their volunteer work. This project may be similar enough to share code between the two applications. Some notes though:

• Currently when volunteers work in more than one area, their hours are often recorded in only one area, rather than multiple areas. The current UI only allows one volunteer task type to be recorded per person per shift, so entering two task types requires entering the person again. This is efficient for most cases, since most people only volunteer in one area, but most staff people work in several over the course of the day.
• Volunteers also often report the start time and end time, rather than the number of hours worked. This makes the front desk worker calculate the hours using time math in their head.
• Once we are able to analyze paid worker shifts according to program type and production/income stream, we may want to look at volunteer hours in the same way. That is, we may want to port these functions to the volunteer app from the staff app.