Page 2 of 3

Posted: Mon Jan 12, 2004 10:06 am
by McGruff
Not sure I've really got your problem but at first glance it seems a simple db structure will do the trick - ie a many-to-many relationship between holidays and employees.

employees
eid | name | etc

holidays
hid | name | date_start | date_end | start_time | end_time

j_employees_holidays
eid | hid

The calendar sounds like a candidate for a static page which is written over each time a holiday is added or deleted.

Posted: Mon Jan 12, 2004 11:55 am
by scorphus
Bech100 wrote:That code seems to be working really well, thank you very much.
No problem, you're welcome. I'm glad it seems to be working.
Bech100 wrote:I don't fully undertand this Query, especially the "case", "when" and "end as" parts.
I'll try to explain it for you. It is a case statement:

Code: Select all

$sql = "SELECT who, from_date, to_date, fd_hd,
(CASE (fd_hd)
	WHEN 'AM' THEN 'Half day / AM'
	WHEN 'PM' THEN 'Half day / PM'
	WHEN 'FD' THEN 'Full day'
END) AS fd_hd_ext
FROM holyday ORDER BY to_date";
'CASE' to open the statement, inside () the case_value followed by the various conditions ('WHEN when_value THEN something') and 'END' to close the statement. The result ('Half day / AM', 'Half day / PM' or 'Full day') will be assigned to a filed named fd_hd_ext.

Analogically, using PHP language, this same statement would be like this:

Code: Select all

switch ($fd_hd) {
	case 'AM':
		$fd_hd_ext = 'Half day / AM';
		break;
	case 'PM':
		$fd_hd_ext = 'Half day / PM';
		break;
	case 'FD':
		$fd_hd_ext = 'Full day';
		break;
}
It's just to make things clear, this is not applicable to your application.
Bech100 wrote:Also, i cant figure out why you need to do

Code: Select all

array_pop($table);
This is because the last attempt to retrive a row from the DB occurs, but, since there is no more available rows, $table[] = mysql_fetch_assoc($result) add a null value to the last position of the $table array, thus the need to remove the last position with [php_man]array_pop[/php_man]().
Bech100 wrote:Just one more slight problem, any idea how i can make the code you gave me exclude saturdays and sundays!?
I'm gonna try something here and let you know what I get. I'm also gonna try and work on McGruff's good idea...

Hope I'm clear enough. Regards,
Scorphus.

Posted: Mon Jan 12, 2004 3:08 pm
by JayBird
Thanks for the help buddy, i wait in anticipation for your solution ;)

Mark

Posted: Wed Jan 14, 2004 11:34 am
by JayBird
any ideas yet buddy?

Mark

Posted: Wed Jan 14, 2004 1:04 pm
by McGruff
If you'd like to work through an OOP solution with me, let me know.

Posted: Wed Jan 14, 2004 3:01 pm
by JayBird
Yeah mate, when completed, i might convert to OOP.

Just wanna get it working first.

Mark

Posted: Wed Jan 14, 2004 9:01 pm
by McGruff
Rgr. I thought a calendar app might be an interesting case study in building an OOP system. It would HAVE to be OOP: spaghetti scripts mixed up with html & other un-refactored horrors make my head hurt.

I think it's easier if you do use OOP from the start - much simpler to track down problems or re-organise chunks of code as required.

I'm still not sure exactly what you're getting stuck on. Off the top of my head:

(1) db query to get all holiday table rows in the view
(2) turn this into a $cell_vars array with "numerical" keys = unix timestamps and values = arrays of employee data (you've got one array element per date, and the date is stored in the key)
(3) when you come to print calendar cells, use isset() to check for a $cell_vars key = current cell date: if you find one stick the data in the cell

Posted: Thu Jan 15, 2004 2:59 am
by JayBird
Scorphus has now given me code that works to inout the data into the calendar. I just now need to modify that so it doesn't include saturdays or sundays.

Mark

Posted: Sat Jan 17, 2004 1:52 pm
by scorphus
Bech100 wrote:(...) I just now need to modify that so it doesn't include saturdays or sundays (...)
Hey Mark, I'm sorry, mate. I was in deep trouble applying a new customer's web application this week. I was working about 12-14 hours/day and getting too tired.

Well, today I got time to work a bit more on our (;)) code. This is what I have:

Code: Select all

<?php
function getYearData () {
	mysql_connect('localhost', 'root');
	mysql_select_db('test');
	$sql = "select who, from_date, to_date, fd_hd,
		case fd_hd
			when 'AM' then 'Half day / AM'
			when 'PM' then 'Half day / PM'
			when 'FD' then 'Full day'
		end as fd_hd_ext
	from holyday order by to_date";
	$result = mysql_query($sql) or die(mysql_error());
	$table = array();
	while ($table[] = mysql_fetch_assoc($result));
	array_pop($table);
	$year = array_fill(0, 365, array());
	foreach ($table as $value) {
		$yearDayFrom = date('z', strtotime($value['from_date']));
		$yearDayTo = date('z', strtotime($value['to_date']));
		for ($day = $yearDayFrom; $day <= $yearDayTo; $day++)
			$year[$day][] = $value;
	}
	return $year;
}

$yearData = getYearData();

$BasePath = str_repeat("../", substr_count(dirname($_SERVER["SCRIPT_NAME"]), "/"));

//require ($BasePath."ocdaintranet/includes/db_connection.php");

// Get todays date
$todaysEpoch = time();
$todaysEpochArray = getdate($todaysEpoch);

if(!isset($_GET['year'])) { // If no year on query string, get this year
   $year = $todaysEpochArray['year'];
} else {
   $year = $_GET['year'];
}

?>

<html>
<head>
<title>Holiday Chart</title>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
<link href="/ocdaintranet/styles/stylesheet.css" rel="stylesheet" type="text/css">
<link href="/ocdaintranet/styles/calendar.css" rel="stylesheet" type="text/css">
<style type="text/css">
<!--
.button { cursor:hand; background-color: #DBEDFF }
.buttonover { cursor:hand; background-color: #CEE7FF }
.buttonweekend { background-color: #FFD5D5 }
.buttonoverweekend { cursor:hand; background-color: #FFC5C5 }
.nodate { background-color: #DDDEDF }
-->
</style>
<script language="JavaScript">
<!-- Change the row colour on mouseOver/Out

function BGNew(obj, new_style) {
obj.className = new_style;
}

//-->
</script>
</head>
<body onLoad="MM_preloadImages('/ocdaintranet/images/winxp/btn_add_over.gif')">

<?php //require ($BasePath."ocdaintranet/menu/menu.php"); ?>

<h1 class="heading">Holiday Chart - <?php echo $year; ?></h1>
<table width="604" border="0" cellpadding="0" cellspacing="0" class="icons">
  <tr>
    <td class="commandBtn"><table width="52" border="0" cellspacing="0" cellpadding="1">
      <tr align="center">
        <td width="50"><a href="add_date.php" onMouseOver="MM_swapImage('calendList011','','/ocdaintranet/images/winxp/btn_add_over.gif',1)" onMouseOut="MM_swapImgRestore()"><img src="/ocdaintranet/images/winxp/btn_add_norm.gif" alt='Add' name="calendList011" width="23" height="23" border="0" id="calendList01"></a></td>
        </tr>
      <tr align="center">
        <td width="50">Add</td>
        </tr>
    </table>     
    <div id="calendListtt" class="rel"></div>
    </td>
    <td class="commandDesc" align="right" width="99%"><?php
   // Output the number of days left for each person
   if (is_array($count)) {
      echo "<strong>Days left</strong> ::";
      foreach ($count as $key=>$val) {
         echo $key."//<strong>".(21-$val)."</strong> :: ";
      }
   }
   ?></td>
  </tr>
</table>

<?php

echo "<table width="100%">";
echo "\t<tr>\n";

$dayArray = array("Mon", "Tue", "Wed", "Thu", "Fri", "Sat", "Sun");

$dayMonths = 1;
$day = 0;

// Output column headings (Months)
echo "\t\t<td width="4%"> </td>\n";
$monthDays = array();
for ($i = 1; $i < 13; $i++) {
	$monthDays[$i] = 1;
	echo "\t\t<td width="8%" align="center" class="odd">" . date("M", mktime(0,0,0,$i,1,2000)) . "</td>\n";
}
echo "\t</tr>\n";
$mDIndex = 1;

$td_colour_weekend = "even";
$tr_out = "even";

while ($dayMonths <= 37) { // The maximum number of cells any month will need is 37

   $checkDay = $dayArray[$day%7];
   
   // If saturday or Sunday change cell color
   if ($checkDay == "Sun" || $checkDay == "Sat") {
      $day++;
      $dayMonths++;
			for ($mDIndex = 1; $mDIndex < 13; $mDIndex++)
	      $monthDays[$mDIndex]++;
      continue;
   }
   echo "\t<tr>\n";
   echo "\t\t<td width="4%" class="odd" align="right">$checkDay</td>\n";

   for ($mDIndex = 1; $mDIndex < 13; $mDIndex++) {

     $checkDay = $dayArray[$day%7];
	   
	   // Month check
	   // If the current day number is equal to the name of the day and we are still in the same month, output the date
	   if (date("D", mktime(0,0,0,$mDIndex,$monthDays[$mDIndex],$year)) == $checkDay && date("m", mktime(0,0,0,$mDIndex,$monthDays[$mDIndex],$year)) == $mDIndex) {
	   	if (is_array($days_off)) {
	         $currentDay = date("z", mktime(0,0,0,$mDIndex,$january,$year));
	         if (in_array($currentDay,$days_off)){
	            $key = array_search($currentDay,$days_off);
	            $name = $who_sorted[$key];
	            $td_colour_weekend_old = $td_colour_weekend;
	            $td_colour_weekend = "buttonover";
	            $tr_out = "buttonover";
	         }
	      }
	      echo "\t\t<td width="8%" align="top" class="".$td_colour_weekend."" onMouseOver="BGNew(this,'buttonover')" onMouseOut="BGNew(this,'".$tr_out."')" onclick="location='view_contact.php?id=".$line['id']."'">";
	      echo "<div align="right">".date("d", mktime(0,0,0,$mDIndex,$monthDays[$mDIndex],$year))."</div>";
	      $yearDay = date('z', mktime(0,0,0,$mDIndex,$monthDays[$mDIndex],$year));
	      echo "<div align="left">\n";
	      foreach ($yearData[$yearDay] as $value)
	      	echo "\t\t<span class="full_half_" . $value['fd_hd'] . "">" . $value['who'] . "</span><br />\n";
	      echo "\t\t</div>\n</td>\n";
	      if (!empty($name))
	         $td_colour_weekend = $td_colour_weekend_old;
	      $name = "";
	      $monthDays[$mDIndex]++;
	   }
	   else // Otherwise, print a blank cell
	      echo "\t\t<td width="8%" class="nodate"></td>\n";
   }
   echo "\t</tr>\n";
   $day++;
   $dayMonths++;
}

echo "</table>";

?>
<table cellspacing="0" width="100%" border="0" cellpadding="0">
  <tr>
    <td nowrap align="right" class="footerCell"><a href="holiday_chart.php?year=<?php echo ($year-1); ?>"><< Previous year</a> | <a href="holiday_chart.php?year=<?php echo $todaysEpochArray['year']; ?>">This Year</a> | <a href="holiday_chart.php?year=<?php echo ($year+1); ?>">Next year >></a></td>
  </tr>
  <tr>
    <td height="5" colspan="2"><img width="1" height="5" border="0" src="../themes/winxp/spacer.gif" alt=""></td>
  </tr>
</table>

</body>
</html>
Just some 'burnishing' here and there and I think it's ready. Sorry for not adding comments...

Cheers,
Scorphus.

Posted: Thu Jan 22, 2004 4:39 am
by JayBird
Hi buddy,

Only just got round to testing the new code you gave me, been busy with other things. Looking good, a lot shorter than the original.

One thing, when i said i didn't want to include Saturdays and Sundays, i meant, i didn't want anyones name to be inputted in them days. I still want to display Saturday and Sunday on the calendar.

I'm gonna have a go at sorting it myself, but without comments on your code, i don't fully understand what is happening.

If you know how i can get saturdays and sundays back easily, please let me know.

Thanks

Mark

Posted: Thu Jan 22, 2004 5:19 am
by JayBird
I can't get my head around this bit

Code: Select all

$checkDay = $dayArray[$day%7]; 

   if ($checkDay == "Sun" || $checkDay == "Sat") { 
      $day++; 
      $dayMonths++; 
         for ($mDIndex = 1; $mDIndex < 13; $mDIndex++) 
         $monthDays[$mDIndex]++; 
      continue; 
   }
If the day is saturday or sunday, then what is it doing?

Posted: Tue Mar 02, 2004 10:46 am
by scorphus
Hello Mark, how're you doing fella!

Just to know how this project is going. Sorry for going away, I was out of time applying a very large project in São Paulo last months. I also had a training course on network security there.

So, how is it going the Calendar Project? Is there anything I still can help you with? Please let me know.

Cheers,
Scorphus.

Posted: Tue Mar 02, 2004 11:24 am
by JayBird
Hi mate, yeah, still working on this, been on hold for a while though.

Basically, the last code you gave me was almost perfect, but when i said i didn't want to include saturdays or sundays, what i actually wanted was these days to be displayed on the calendar, but i didn't want anyones name to be inputed into them.

Make sense??

Mark

Posted: Wed Mar 03, 2004 1:13 pm
by scorphus
Yes it does. I made some changes to the code, see:

Code: Select all

<?php
function getYearData () {
  mysql_connect('localhost', 'root');
  mysql_select_db('test');
  $sql = "select who, from_date, to_date, fd_hd,
    case fd_hd
      when 'AM' then 'Half day / AM'
      when 'PM' then 'Half day / PM'
      when 'FD' then 'Full day'
    end as fd_hd_ext
  from holyday order by to_date";
  $result = mysql_query($sql) or die(mysql_error());
  $table = array();
  while ($table[] = mysql_fetch_assoc($result));
  array_pop($table);
  $year = array_fill(0, 365, array());
  foreach ($table as $value) {
    $yearDayFrom = date('z', strtotime($value['from_date']));
    $yearDayTo = date('z', strtotime($value['to_date']));
    for ($day = $yearDayFrom; $day <= $yearDayTo; $day++)
      $year[$day][] = $value;
  }
  return $year;
}

$yearData = getYearData();

$BasePath = str_repeat("../", substr_count(dirname($_SERVER["SCRIPT_NAME"]), "/"));

//require ($BasePath."ocdaintranet/includes/db_connection.php");

// Get todays date
$todaysEpoch = time();
$todaysEpochArray = getdate($todaysEpoch);

if(!isset($_GET['year'])) { // If no year on query string, get this year
   $year = $todaysEpochArray['year'];
} else {
   $year = $_GET['year'];
}

?>

<html>
<head>
<title>Holiday Chart</title>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
<link href="/ocdaintranet/styles/stylesheet.css" rel="stylesheet" type="text/css">
<link href="/ocdaintranet/styles/calendar.css" rel="stylesheet" type="text/css">
<style type="text/css">
<!--
.button { cursor:hand; background-color: #DBEDFF }
.buttonover { cursor:hand; background-color: #CEE7FF }
.buttonweekend { background-color: #FFD5D5 }
.buttonoverweekend { cursor:hand; background-color: #FFC5C5 }
.nodate { background-color: #DDDEDF }
-->
</style>
<script language="JavaScript">
<!-- Change the row colour on mouseOver/Out

function BGNew(obj, new_style) {
obj.className = new_style;
}

//-->
</script>
</head>
<body onLoad="MM_preloadImages('/ocdaintranet/images/winxp/btn_add_over.gif')">

<?php //require ($BasePath."ocdaintranet/menu/menu.php"); ?>

<h1 class="heading">Holiday Chart - <?php echo $year; ?></h1>
<table width="604" border="0" cellpadding="0" cellspacing="0" class="icons">
  <tr>
    <td class="commandBtn"><table width="52" border="0" cellspacing="0" cellpadding="1">
      <tr align="center">
        <td width="50"><a href="add_date.php" onMouseOver="MM_swapImage('calendList011','','/ocdaintranet/images/winxp/btn_add_over.gif',1)" onMouseOut="MM_swapImgRestore()"><img src="/ocdaintranet/images/winxp/btn_add_norm.gif" alt='Add' name="calendList011" width="23" height="23" border="0" id="calendList01"></a></td>
        </tr>
      <tr align="center">
        <td width="50">Add</td>
        </tr>
    </table>     
    <div id="calendListtt" class="rel"></div>
    </td>
    <td class="commandDesc" align="right" width="99%"><?php
   // Output the number of days left for each person
   if (is_array($count)) {
      echo "<strong>Days left</strong> ::";
      foreach ($count as $key=>$val) {
         echo $key."//<strong>".(21-$val)."</strong> :: ";
      }
   }
   ?></td>
  </tr>
</table>

<?php

echo "<table width="100%">";
echo "\t<tr>\n";

$dayArray = array("Mon", "Tue", "Wed", "Thu", "Fri", "Sat", "Sun");

$dayMonths = 1;
$day = 0;

// Output column headings (Months)
echo "\t\t<td width="4%"> </td>\n";
$monthDays = array();
for ($i = 1; $i < 13; $i++) {
  $monthDays[$i] = 1;
  echo "\t\t<td width="8%" align="center" class="odd">" . date("M", mktime(0,0,0,$i,1,2000)) . "</td>\n";
}
echo "\t</tr>\n";
$mDIndex = 1;

$td_colour_weekend = "even";
$tr_out = "even";

while ($dayMonths <= 37) { // The maximum number of cells any month will need is 37

   $checkDay = $dayArray[$day%7];
      
   // If saturday or Sunday change cell color
   if ($checkDay == "Sun" || $checkDay == "Sat") {
      $td_colour_weekend = "buttonweekend";
      $tr_out = "buttonweekend";
      for ($mDIndex = 1; $mDIndex < 13; $mDIndex++) {
        $yearDay = date('z', mktime(0,0,0,$mDIndex,$monthDays[$mDIndex],$year));
        unset($yearData[$yearDay]);
        $yearData[$yearDay] = array();
      }
   } else {
      $td_colour_weekend = "even";
      $tr_out = "even";
   }

   echo "\t<tr>\n";
   echo "\t\t<td width="4%" class="odd" align="right">$checkDay</td>\n";

   for ($mDIndex = 1; $mDIndex < 13; $mDIndex++) {

     $checkDay = $dayArray[$day%7];

     // Month check
     // If the current day number is equal to the name of the day and we are still in the same month, output the date
     if (date("D", mktime(0,0,0,$mDIndex,$monthDays[$mDIndex],$year)) == $checkDay && date("m", mktime(0,0,0,$mDIndex,$monthDays[$mDIndex],$year)) == $mDIndex) {
      $yearDay = date('z', mktime(0,0,0,$mDIndex,$monthDays[$mDIndex],$year));
       $days_off = $yearData[$yearDay];
       if (is_array($days_off)) {
           $currentDay = date("z", mktime(0,0,0,$mDIndex,$january,$year));
           if (in_array($currentDay,$days_off)){
              $key = array_search($currentDay,$days_off);
              $name = $who_sorted[$key];
              $td_colour_weekend_old = $td_colour_weekend;
              $td_colour_weekend = "buttonover";
              $tr_out = "buttonover";
           }
        }
        echo "\t\t<td width="8%" align="top" class="".$td_colour_weekend."" onMouseOver="BGNew(this,'buttonover')" onMouseOut="BGNew(this,'".$tr_out."')" onclick="location='view_contact.php?id=".$line['id']."'">";
        echo "<div align="right">".date("d", mktime(0,0,0,$mDIndex,$monthDays[$mDIndex],$year))."</div>";
        echo "<div align="left">\n";
        foreach ($yearData[$yearDay] as $value)
          echo "\t\t<span class="full_half_" . $value['fd_hd'] . "">" . $value['who'] . "</span><br />\n";
        echo "\t\t</div>\n</td>\n";
        if (!empty($name))
           $td_colour_weekend = $td_colour_weekend_old;
        $name = "";
        $monthDays[$mDIndex]++;
     }
     else // Otherwise, print a blank cell
        echo "\t\t<td width="8%" class="nodate">a</td>\n";
   }
   echo "\t</tr>\n";
   $day++;
   $dayMonths++;
}

echo "</table>";
?>
<table cellspacing="0" width="100%" border="0" cellpadding="0">
  <tr>
    <td nowrap align="right" class="footerCell"><a href="holiday_chart.php?year=<?php echo ($year-1); ?>"><< Previous year</a> | <a href="holiday_chart.php?year=<?php echo $todaysEpochArray['year']; ?>">This Year</a> | <a href="holiday_chart.php?year=<?php echo ($year+1); ?>">Next year >></a></td>
  </tr>
  <tr>
    <td height="5" colspan="2"><img width="1" height="5" border="0" src="../themes/winxp/spacer.gif" alt=""></td>
  </tr>
</table>

</body>
</html>
I'm ready to help you figure out something you don't get.

Many cheers,
Scorphus.

Posted: Wed Mar 03, 2004 1:34 pm
by JayBird
cheerz buddy, ill give it a go when i am at work 2mrw and get back to you and let you know how our code is working ;)

Mark