SitePoint Sponsor

User Tag List

Results 1 to 9 of 9
  1. #1
    SitePoint Zealot pooney's Avatar
    Join Date
    Jan 2006
    Posts
    123
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Staff holidays booking system

    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..

    PHP Code:
    <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(000$month1$year));
            
            
    $monthname date("M"mktime(000$month1$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

    Code:
       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!
    Web Host Advice - Under Development

  2. #2
    SitePoint Evangelist
    Join Date
    Aug 2005
    Posts
    453
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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.
    Computers and Fire ...
    In the hands of the inexperienced or uneducated,
    the results can be disastrous.
    While the professional can tame, master even conquer.

  3. #3
    SitePoint Zealot pooney's Avatar
    Join Date
    Jan 2006
    Posts
    123
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by byron3@earthlink View Post
    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.
    Web Host Advice - Under Development

  4. #4
    SitePoint Evangelist
    Join Date
    Aug 2005
    Posts
    453
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    You can convert all dates into timestamps and compare....
    Computers and Fire ...
    In the hands of the inexperienced or uneducated,
    the results can be disastrous.
    While the professional can tame, master even conquer.

  5. #5
    SitePoint Zealot pooney's Avatar
    Join Date
    Jan 2006
    Posts
    123
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Using timestamps and arrays now, made some good progress...

    PHP Code:
    <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(000$start_month[$i], $start_day[$i], $start_year[$i]);
        
    $holidays_end[] = mktime(000$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(000$month1$year));
            
            
    $monthname date("M"mktime(000$month1$year));
            echo 
    '<tr bgcolor="'.$bgcolor.'"><td width="5%">' $monthname '</td>';
            
    $rownumber 1;
            while (
    $rownumber <= $daysinmonth) {
                
    $todaysdate mktime(000$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..
    Code:
    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!
    Web Host Advice - Under Development

  6. #6
    SitePoint Zealot pooney's Avatar
    Join Date
    Jan 2006
    Posts
    123
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Does anybody know how to avoid those extra table cells? I'm pretty stumped.
    Web Host Advice - Under Development

  7. #7
    SitePoint Evangelist
    Join Date
    Aug 2005
    Posts
    453
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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
    Computers and Fire ...
    In the hands of the inexperienced or uneducated,
    the results can be disastrous.
    While the professional can tame, master even conquer.

  8. #8
    SitePoint Zealot pooney's Avatar
    Join Date
    Jan 2006
    Posts
    123
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by byron3@earthlink View Post
    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...
    PHP Code:
    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.
    Web Host Advice - Under Development

  9. #9
    SitePoint Zealot pooney's Avatar
    Join Date
    Jan 2006
    Posts
    123
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    PHP Code:
    $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.
    Web Host Advice - Under Development


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •