Abigail

Or even, a date-oriented structure, since the shifts repeat themselves daily as well. So you'd have a table with shifts and shift IDs, a table with employees and employee IDs, and a table combining the two, mapping date, shift ID and employee ID together. This third table can then continue indefinitely (tho of course you'll probably want to periodically remove old data). It has a double-index of date and shift ID.

Code-wise, you then calculate 'today and next seven days', look up those dates in the table, translating shift IDs to times, and employee IDs to names as you go, and making some sort of hash:

  %current = (
               '2003-12-05' => { {
                                 'time' => '9:00 AM',
                                 'id'   => 1,
                                 'employee' => {'id'  => 1,
                                                'name => 'Fred Flintst
+one',
                                               }
                                 },
                                 {
                                 'time' => '5:00 PM',
                                 'id'   => 2,
                                 'employee' => { 'id' => 4,
                                                 'name' => 'Wilma',
                                 }
                                 {
                                 'time' => '1:00 AM',
                                 'id'   => 3,
                                 'employee' => { 'id' => 3,
                                                 'name' => 'Barney',
                                 }
                               },
               '2003-12-06' => { ... 
             }
[download]

C.

There are a couple of other things to think about:

• One of the issues you haven't addressed is how the changes to a shift/employee propagate -- in other words, if I change the shift assignment would I normally be expecting to change it permanently or just for the next week?
• This is also a situation where the shift_id solution (despite being more 'database-y') might actually be less efficient than a more simple (but less commonly-used) strategy. In other words, have you considered a bitmap of some sort using bitwise operations to determine shift availability and prevent overlapping shifts or other shenanigans?

HTH

I would sit down and ask myself what information would be needed to decide who goes when: for example has Frank worked more than 30 hours in the last week? When was Jane's last shift? Does Bob do mornings?

It will probably also be helpful to create a big overview screen that shows all the shifts currently assigned, and where staff are needed.

Interesting problem. I think once you have the problem nailed the storage method will suggest itself...

(Edit: Oops, misread the previous post, thought it said 'automatically' in there somewhere, on second reading, it doesnt, anyway, argument against that still stands).

That's not what it looked like to me. If it is, then Im also interested in the result, since I spent an hour or three recently looking for a similar app, to fit some specific requirements, I found a few Windows ones that came close (best: http://www.schedulerlite.com), but nothing that was exact enough, or could be changed to do it. There were a couple of OS apps on freshmeat etc, but nothing remotely useful (for what I needed anyway, no offense meant..)

While pondering doing it myself, I decided that automatically assigning, while possible, isn't really very practical. You'd need very exact details of who can, when, and for how long. These are bound to change last-minute, someone gets sick, etc. Which would require either a complete recalc, meaning everyones schedules get changed at the last minute (they're going to love that), or that someone sit down and manually shuffle to minimise the knock-on effect. (I'm not going to suppose that you have people sitting around idle that you can swap-in, no business sense in that).

In short the best way to do it seems to me, to have someone manually assign the shifts, and have the interface just be *really* helpful, ie add up hours/week on the fly, check constraints on the fly, be able to track vacations/stand-ins, when people like to work, drag+drop names to shifts.. and and and.. Manual assignment also has the advantage of the assigner actually knowing vaguely who is going to be when/where.

SchedulerLite seems nearly perfect, its just missing that 'show total hours/week on the fly' thing, and a couple of other bits. After fiddling around with it a while, I might attempt coding one myself (in perl of course ,) (BTW it also has all the SQL/tables right there, so the OP might grab some ideas from it..)

C.

You've all come up with fascinating questions and answers. Thanks a lot.

I should clarify that the system was never required to do leave, resolve conflicts, remember staff preferences etc, just work as a kind of content-management system for one page, the "who's working when?" page. As Abigail said, just a dumb front end to a database.

Questions like this are becoming more interesting to me now, I note, whereas before I would have just started writing something right away, I'm now much more concerned with the structure before I start on the code.

---




($_='kkvvttuubbooppuuiiffssqqffssmmiibbddllffss')
 =~y~b-v~a-z~s;                             print
[download]

SORRY, I have to catch my plane (already late), so this goes without cleanup or further comment for now. I will come back to this node tomorrow...

our $oopser = 0; # protect from endless loops
$#empsh = 20; # prefill worktable

for my $sh (1 .. 21) { # 21 shifts are 7 days with 3 shifts
    for my $shid (1 .. 3) { # every shift has 3 mandtory workers
        # find a free worker
        my $emp = 0;
        $oopser = 0;
        while ($emp == 0) {
            $oopser++; die if $oopser > 100;
            $emp = int( 1 + rand(20) ); # take a random worker
            $emp = 0 if $empsh->[$emp][$sh]; # already assigned
        }
        $empsh[$emp][$sh] = "work ($shid)"; # work this shift (8 hours
+)
        $empsh[$emp][$sh+1] = 'free (+)';   # and have 2 shifts rest (
+16 hours)
        $empsh[$emp][$sh+2] = 'free (++)';  # (German law says 11 hour
+s between shifts)
        $shcount[$sh]++; # no of worker per shift
    }
    foreach my $emp (@empsh) { # all other workers are unassigned on t
+his shift
        $emp->[$sh] ||= ' - ';
    }
}

my %emplist = map { $_ => $_ } (1 .. 20); # workerlist, delete()able
$oopser = 0;
while (keys %emplist) { # check for remaining every worker (maybe add 
+"sort rand()"
    $oopser++; die if $oopser > 100;
    my @shnum = (); # no of shifts per worker
    for my $emp (keys %emplist) {
        foreach my $tag (@{ $empsh[$emp] }) {
            $shnum[$emp]++ if $tag =~ /^work/; # count his working shi
+fts
        }
        if ($shnum[$emp] == 5) { # worked 5 shifts?
            delete $emplist{$emp};
        } # (each worker has to work exactly 5 shifts a week)
    }
    EMP: foreach my $emp (keys %emplist) { # who has to work more?
        my $sh = 1;
        while ($empsh[$emp][$sh] ne ' - ') { # find a unassigned shift
+ for this worker
            if ($sh > 21) { # oops, none found?
                delete $emplist{$emp};
                print STDERR "$emp has underwork\n";
                next EMP; # re-run this program then
            }
            $sh++;
        }
        if ($shcount[$sh] >= 5) {
            # shift full
            $empsh[$emp][$sh] = 'free (shift full)';
        } elsif ($empsh[$emp][$sh+1] =~ /^work/) {
            # no 2 shifts rest possible
            $empsh[$emp][$sh] = 'free (-)';
        } elsif ($empsh[$emp][$sh+2] =~ /^work/) {
            # no 2 shifts rest possible
            $empsh[$emp][$sh] = 'free (--)';
        } else {
            # hehe, work here!
            $empsh[$emp][$sh] = 'work (backup)';
            $empsh[$emp][$sh+1] = 'free (+)';
            $empsh[$emp][$sh+2] = 'free (++)';
        }
    }
}

# now print
print "<table border><tr><td>***";
for my $sh (1 .. 21) {
    print "<th>-$sh-";
}
print "<td>";
for my $emp (1 .. 20) {
    print "<tr><th>", $emp;
    for my $sh (1 .. 21) {
        print "<td>", $empsh[$emp][$sh];
    }
}
print "</table>";
[download]