PHP SQL RDO Roster calculations

[bullantt]

I am building a Web based attendance using PHP 5.4 and SQL Server2008R2.

 

What i am having trouble with is trying to calculate the RDO (roster day off) i have 5 goups of employees a group for each day of the working week and i need to be able to show all the employees and when there RDOs are

 

Each group will add a day each week

 

example:

 

Yellow = Monday 3/09/12

Orange = Tuesday 4/09/12

Green = Wednesday 5/09/12

Blue = Thursday 6/09/12

Purple = Friday 7/09/12

Next Week would look like this

 

Yellow = Tuesday 11/09/12

Orange = Wednesday 12/09/12

Green = Thursday 13/09/12

Blue = Friday 14/09/12

Purple = Monday 10/09/12

And so on ...

 

i have setup a table in the database for the groups but for the life of me i cannot figure out how i can get this info without manual input of dates i would like to output this to a calendar i have made in PHP with all the employees on

 

i would like to add and remove employees from these groups and have it show up automatically with the dates of there RDO

 

i have thought about having someone go in each month and put this the date of the first RDO for each group every month and is what i will do if there is no other way around this

 

thanks guys this is my first post be gentle

[jcbones]

So, how is your table in the database set up?

 

IMHO, you should have relational tables.  How many would depend on just how far this application goes.

[bullantt]

Ok so i have 4 tables setup in the database i have a table that the users will input leave in a date range and a table for the leave categories also have a table setup for RDO empty right now as still not sure structure is correct table has these columns

id

group

sort

date

 

then a table with the employees in each group

 

id

PRN (pay roll number)

group

sort

 

this is the code i have on the page witch does work for the leave it shows a list of the employees and there leave witch is color coded eg: sick leave, annual leave, long service leave

 

but i cannot get it to show the RDO as a user is not inputting them to the database we would think them to be auto incrementing each week

 

i have attach a jpg of the current test site

 

<?php 
$search_result = employee::display_employees( $showceased='TRUE', $limit="300", $LabourHire ); 
$m= date("m");
$de= date("d")-3;
$y= date("Y");
$d_display = 60;
include_once(LIB_PATH.DS."attendance_colors.php" );
?>
<div id="list">
<table id="myemp_table" cellspacing="0">
<caption> </caption>
<tr>
<th scope="col" abbr="Employee Name" class="nobg">Employee Name</th>
<th >PRN</th>
</tr>
<?php foreach ( $search_result as $display_recent_result ) {
if($display_recent_result->Photo) { $photo = $display_recent_result->Photo; } else { $photo = NULL; }	 ?>
   <tr>
  <th scope="row" class="spec" ><div class="employee_fullname" id='<?php echo htmlentities($photo); ?>'><?php echo htmlentities($display_recent_result->SURNAME) . ", " . htmlentities($display_recent_result->NAME); ?></div> </th>
  <td class="table_prn_cell" ><?php echo htmlentities($display_recent_result->PRN); ?></td>
  </tr>
<?php } ?>
  </table>
</div>
<div id="attend">
<table id="mytable" cellpadding="0" cellspacing="0" summary="Current Employees">
<caption> </caption>
<tr>
<?php 
	for($i=0; $i<=$d_display; $i++){
	echo '<th class="attdate" scope="col" abbr="">'.date('d/m',mktime(0,0,0,$m,($de+$i),$y)).'</th>';
	}
	echo ' </tr>';
	foreach ( $search_result as $display_recent_result ) {
	$prn = $display_recent_result->PRN; 	

	echo '<tr>';

  		for($i=0; $i<=$d_display; $i++) {		
	$date = date('Y-m-d',mktime(0,0,0,$m,($de+$i),$y));	
	$state = attendance::find_by_PRN($prn, $date);

		if($state) {
			$id = $state->CategoryID;
			$cat = attendance_category::find_by_id($id);
			echo "<td height=\"32\" class=\"$cat->Code\">".$cat->Code."<span class=\"smalldate\">".date('d',mktime(0,0,0,$m,($de+$i),$y))."</span></td>";
		} else {
			echo "<td> 	 </td>";
		}		
	} 		
	echo ' </tr>';
	} 
	echo "</table>";
?>
</div>