Staff holidays booking system

[pooney]

Hi,

I've been asked to create a system where staff can enter a date when they want to stay off work. This could be one day, a week, 2 weeks, anything. Everything needs to be displayed in calendar format, here's what I've got so far..

<table width="100%" cellspacing="0" cellpadding="3" style="border:2px solid #ddeeff;">
<tbody style="color:#225C77;">
<?php

$month = 1;
$year = date("Y");
$bgcolor = "#999999";

echo '<tr><td bgcolor="#225C77" width="5%"><strong style="color:#FFFFFF;"><center>'.$year.'</center></strong></td>';

$rownumber = 1;
while ($rownumber <= 31) {
            echo '<td width="3%"><center>'.$rownumber.'</center></td>';
            $rownumber++;
        }
echo '</tr>';

while ($month <= 12) { if($bgcolor == "#EEF5FF"){$bgcolor = "#DDEEFF";} else {$bgcolor = "#EEF5FF";}
    $daysinmonth = date("t", mktime(0, 0, 0, $month, 1, $year));
        
        $monthname = date("M", mktime(0, 0, 0, $month, 1, $year));
        echo '<tr bgcolor="'.$bgcolor.'"><td width="5%">' . $monthname . '</td>';
        $rownumber = 1;
        while ($rownumber <= $daysinmonth) {
            echo '<td width="3%"> </td>';
            $rownumber++;
        }
        echo '</tr>';
    $month++;
}

?>
</tbody>
</table>

/\ This generates the calendar, you can see the output here: http://talkdesign.net/calendar.php

   act_ID  int(11)  UNSIGNED No    auto_increment  Change  Drop  Primary  Index  Unique  Fulltext  
   staff_id  int(5)   No  0    Change  Drop  Primary  Index  Unique  Fulltext  
   act_title  varchar(255)   No      Change  Drop  Primary  Index  Unique  Fulltext  
   act_desc  text   Yes  NULL    Change  Drop  Primary  Index  Unique  Fulltext  
   act_start  datetime   No  0000-00-00 00:00:00    Change  Drop  Primary  Index  Unique  Fulltext  
   act_end  datetime   No  0000-00-00 00:00:00    Change  Drop  Primary  Index  Unique  Fulltext  
   act_status  int(1)   No  0    Change  Drop  Primary  Index  Unique  Fulltext

/\ Thats my (mySQL) database so far.

I think it's mostly just the logic I'm stuck on, I'm having a bit of a mental block. How can I get each day where somebody is off work display with a red background? I have the date the holiday starts and the date it ends, I just don't know what to do with them!

[byron3@earthlink]

1. Slice out the numeric day of the start and end of the act.
2. When drawing the table cell for the act change the background color to "red" if the current cell is equal to or greater than the start date and less than or equal to the end date.

You do realize that acts starting in one month and ending in another will take a little more work.

[pooney]

You do realize that acts starting in one month and ending in another will take a little more work.

Yeah I do, there must be a date specific "greater/less than or equal to" operator I can use? That would make things easier.

[byron3@earthlink]

You can convert all dates into timestamps and compare....

[pooney]

Using timestamps and arrays now, made some good progress...

<table width="100%" cellspacing="0" cellpadding="3" style="border:2px solid #ddeeff;">
<tbody style="color:#225C77;font-size:13px;">
<?php
include '../db.php';

$sql = mysql_query("
SELECT *,
 date_format(act_start, '%d') as start_day,
 date_format(act_start, '%m') as start_month,
 date_format(act_start, '%Y') as start_year,
 date_format(act_end, '%d') as end_day,
 date_format(act_end, '%m') as end_month,
 date_format(act_end, '%Y') as end_year 
FROM holidays");
$i = 0;
while ($row = mysql_fetch_array($sql)) {
    $act_id = $row['act_ID'];
    $staff_id = $row['staff_id'];
    
        $sql2 = mysql_query('SELECT firstname FROM engineers WHERE id="'.$staff_id.'" LIMIT 1;');
        
        while ($row2 = mysql_fetch_array($sql2)) {
            $firstname[] = $row2['firstname'];
        }
    $act_title[] = $row['act_title'];
    $act_desc[] = $row['act_desc'];
    $start_day[] = $row['start_day'];
    $start_month[] = $row['start_month'];
    $start_year[] = $row['start_year'];
    $end_day[] = $row['end_day'];
    $end_month[] = $row['end_month'];
    $end_year[] = $row['end_year'];
    $act_status[] = $row['act_status'];
    
    $holidays_start[] = mktime(0, 0, 0, $start_month[$i], $start_day[$i], $start_year[$i]);
    $holidays_end[] = mktime(0, 0, 0, $end_month[$i], $end_day[$i], $end_year[$i]);
    $i++;
}

            
$num_elements=count($act_title);
for ($i=0; $i < $num_elements; $i++) { 
echo $firstname[$i] . $holidays_start[$i] . ' - ' . $holidays_end[$i] .'<br>';
}

$month = 1;
$year = date("Y");
$bgcolor = "#999999";

echo '<tr><td bgcolor="#225C77" width="5%"><strong style="color:#FFFFFF;"><center>'.$year.'</center></strong></td>';

$rownumber = 1;
while ($rownumber <= 31) {
            echo '<td width="3%"><center>'.$rownumber.'</center></td>';
            $rownumber++;
        }
echo '</tr>';

while ($month <= 12) { if($bgcolor == "#EEF5FF"){$bgcolor = "#DDEEFF";} else {$bgcolor = "#EEF5FF";}
    $daysinmonth = date("t", mktime(0, 0, 0, $month, 1, $year));
        
        $monthname = date("M", mktime(0, 0, 0, $month, 1, $year));
        echo '<tr bgcolor="'.$bgcolor.'"><td width="5%">' . $monthname . '</td>';
        $rownumber = 1;
        while ($rownumber <= $daysinmonth) {
            $todaysdate = mktime(0, 0, 0, $month, $rownumber, $year);
            
$num_elements=count($act_title);
for ($i=0; $i < $num_elements; $i++) { 
            if($todaysdate >= $holidays_start[$i] AND $todaysdate <= $holidays_end[$i]) {
            echo '<td width="3%" style="font-size:9px;color:#FFFFFF;" bgcolor="#DD5555"><center>'.$firstname[$i].'</center></td>';
            } else {
            echo '<td width="3%"> </td>'; 
            }
            $rownumber++;
} // end for
        
 }
        echo '</tr>';
        $month++;
}

?>
</tbody>
</table>

and the database..

act_ID     staff_id     act_title     act_desc     act_start     act_end   	  
1          1            Holiday     Holiday in Jamaica  2007-06-06 00:00:00 	2007-06-20 00:00:00
3 	  5 	     Holiday 	      Going to Miami 	2007-08-01 00:00:00 	2007-08-15 00:00:00

The result

As you can see from the result, there is a problem with my for() loop which causes that weird gap between each red <td>. I need a way to stop the "else" part from happening but it's hard because each holiday is at different times.. Hope you get what I'm saying!

[pooney]

Does anybody know how to avoid those extra table cells? I'm pretty stumped.

[byron3@earthlink]

Are you displaying a table for each individual or one common calendar for everyone to view?

Either way as you display each table cell you need to decide if there is a scheduled event on that day, if yes make the cell background red if not make the cell background your base color.

Just a suggestion, have you looked at Webcalendar from Source Forge?
Why reinvent the wheel when you can get it for free and bolt it on?
http://www.k5n.us/webcalendar.php

[pooney]

Are you displaying a table for each individual or one common calendar for everyone to view?

Either way as you display each table cell you need to decide if there is a scheduled event on that day, if yes make the cell background red if not make the cell background your base color.

Just a suggestion, have you looked at Webcalendar from Source Forge?
Why reinvent the wheel when you can get it for free and bolt it on?
http://www.k5n.us/webcalendar.php

I am displaying one common calendar for all staff's holidays for everybody to view.

I already am using an if statement to decide the cell's background color...

if($todaysdate >= $holidays_start[$i] AND $todaysdate <= $holidays_end[$i]) {
            echo '<td width="3%" style="font-size:9px;color:#FFFFFF;" bgcolor="#DD5568"><center>'.$firstname[$i].'</center></td>';
            } else {
            echo '<td width="3%"> </td>'; 
            } 

The problem is that the process of creating all of those table cells is repeated for each holiday. At the moment there are 3 holidays in the database so there are 2 extra cells being created. I need to stop that happening.

As for that webcalendar I'd prefer to do this from scratch, one for the learning experience and two becuase I find my own code easier to modify and add to.

[pooney]

$num_elements=count($act_title);
for ($i=0; $i < $num_elements; $i++) { // this counts how many values are in the array $act_title and then carries out the below code that amount of times

if($todaysdate >= $holidays_start[$i] AND $todaysdate <= $holidays_end[$i]) {
            echo '<td width="3%" style="font-size:9px;color:#FFFFFF;" bgcolor="#DD5568"><center>'.$firstname[$i].'</center></td>';
            } else {
            echo '<td width="3%"> </td>'; 
            }
} 

The problem is with using a loop, the table cells for the same date are created mroe than once. Is there anybody who can help me out here? I'm well and truly stumped.