Double Booking

[iotech]

I use phpScheduleIt as an employee shift scheduler in my organization (a fire department). I've made a number of modifications to the sources in order to re-purpose the code, and today I added in a tweak to prevent double-booking (i.e. when an employee (person) signs up for a shift (reservation) in a position (resource), it checks to see if the person has already reserved any other shifts (reservations) for the same time period for any position (resource).

I've read in the forums a couple of people have asked for this, but Nick has stated that preventing double-booking isnt an intended feature. I thought I'd share with you how I did it in case anyone else wants to do this.

[Nick, if this topic is against your wishes, feel free to remove it!]

No warranty - This method has no warranty. It worked for me, but it could erase your hard drive or cause the collapse of the economies of several moderately large nations. If you use it, be aware that I take NO responsibility for anything negative it does for/to you!

Also, be aware that in my implementation, users are employees signing up for shifts. Shifts are reservations. Resources are positions on each work shift i.e. Firefighter #1, EMT, etc). In our usage, all shifts (reservations) are 12 hours in length, no more and no less. There are two shifts per day of course. You will have to tweak some of my math to make it work for you.

So, what did I change?

1. In Reservation.class.php, in function add_res($users_to_invite = array(), $resources_to_add = array()), change this:

Code:

$dates = array();
$tmp_valid = false;

if (!$this->is_blackout) {
$this->check_perms();

To This:

Code:

$dates = array();
$tmp_valid = false;

                if ($this->check_double_booking()){
                     echo "You already have a shift scheduled for this time period. <br>Double-booking is not allowed. <br>";
                     echo '<br><br><a href="javascript:window.close();">Close</a>';
                     die;
                }

if (!$this->is_blackout) {
$this->check_perms();

2. In Reservation.class.php, create the following function:

Code:

    function check_double_booking() {
        if ($this->adminMode) { return false; }
        if ($this->is_blackout) { return false; }
        $new_start = ($this->start_date)+($this->start);
        $new_end=($this->end_date)+($this->end);
        $new_user=$this->user->userid;
        $query = 'SELECT count(*) as count FROM '
                    . 'reservations' . ' as res INNER JOIN '
                    . 'reservation_users' . ' as resusers ON resusers.resid=res.resid'
                    . ' WHERE resusers.memberid="'.$new_user.'"'
                    . ' AND ((res.start_date + res.starttime ='.$new_start.') AND (res.end_date + res.endtime ='.$new_end.'))'
                    . ' AND res.is_blackout <> 1';
         $my_res=$this->db->do_freeform_query($query);
       return $my_res;
    }

3. In ResDB.class.php, create the following function:

Code:

function do_freeform_query($query) {
        $return=array();
        $result=$this->db->getRow($query);
        $this->check_for_error($result);
        $return=$this->cleanRow($result);
        $My_val=$return['count'];
        return $My_val;
    }

Ok, simple, no?
YES, the do_freeform_query() function is DANGEROUS. Use it wisely. Only for good, not evil.
YES, this is a hack. It isn't pretty to look at, but it works for me.

Have fun!

Gerry

[luciano.lingnau]

Not actually useful for me, but I really think it's very important for people to share their working (and even non-working) changes so the forums become a huge information source. Many of the changes I've made were based on older topics around here.

Thanks.