Entering shifts twice seems to be allowed by the system. Validation algorithm called for?

[tareko]

RoRo sent me a message regarding the appearance of a double shift in his June schedule. The shift 23 June 2012 from 11-19 Vic (shift type 13) appears in his index list (upcoming shifts), but not on the main schedule.

Upon analysis of the database and code, it appears that Kitab was sent some of the June schedule twice (shift ID 4679 and 4555).

Likely chain of events: The most likely chain of events is that some part of the June schedule was sent twice while doing the mass entry. Once entered twice, the calendar displays only the first of the two copies (4555). This copy would have been traded at some point (0853h, 11 June 2012), correctly reflecting in the schedule. The error would show in the index and on any electronic calendar used by Roy.

Other problems revealed: It is almost certain that some part of June will have this error. Anybody with a shift trade during the period that was entered twice will see their shift twice. Anybody with a trade as well will see both shifts - original and traded.

This is unlikely to result in any missed shifts, but may result in confusion and/or people showing up to shifts they were not scheduled for.

Actions I have to take: 1) Immediate: I will review all shifts in June for possible duplicates of this variety. I have deleted RoRo's 'extra' shift.

2) Short term: I have entered this bug

[tareko]

I think this will be relatively easy: Just add a validation rule that ensures no duplicates.

This will be tricky for split shifts, but I think split shifts then will just consist of a ".1" for the first, ".2" for the second, etc., so that they are unique.

[tareko]

Also reported by KD and HH

[tareko]

Finding duplicates:

select s.id, t.* from shifts s join ( select date, shifts_typeid, count() as qty from shifts group by date, shifts_typeid having count() > 1 ) t on s.date = t.date and s.shifts_type_id = t.shifts_type_id ORDER BY t.date ASC, t.shifts_type_id ASC, s.id ASC

This will show all duplicates. Simply delete the LARGER ID

[tareko]

Will need a custom validation rule for this:

http://book.cakephp.org/2.0/en/models/data-validation.html#custom-validation-rules