This has been doing my head in for months!!!!!!

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

McGruff
DevNet Master
Posts: 2893
Joined: Thu Jan 30, 2003 8:26 pm
Location: Glasgow, Scotland

Post 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.
User avatar
scorphus
Forum Regular
Posts: 589
Joined: Fri May 09, 2003 11:53 pm
Location: Belo Horizonte, Brazil
Contact:

Post 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.
User avatar
JayBird
Admin
Posts: 4524
Joined: Wed Aug 13, 2003 7:02 am
Location: York, UK
Contact:

Post by JayBird »

Thanks for the help buddy, i wait in anticipation for your solution ;)

Mark
User avatar
JayBird
Admin
Posts: 4524
Joined: Wed Aug 13, 2003 7:02 am
Location: York, UK
Contact:

Post by JayBird »

any ideas yet buddy?

Mark
McGruff
DevNet Master
Posts: 2893
Joined: Thu Jan 30, 2003 8:26 pm
Location: Glasgow, Scotland

Post by McGruff »

If you'd like to work through an OOP solution with me, let me know.
User avatar
JayBird
Admin
Posts: 4524
Joined: Wed Aug 13, 2003 7:02 am
Location: York, UK
Contact:

Post by JayBird »

Yeah mate, when completed, i might convert to OOP.

Just wanna get it working first.

Mark
McGruff
DevNet Master
Posts: 2893
Joined: Thu Jan 30, 2003 8:26 pm
Location: Glasgow, Scotland

Post 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
User avatar
JayBird
Admin
Posts: 4524
Joined: Wed Aug 13, 2003 7:02 am
Location: York, UK
Contact:

Post 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
User avatar
scorphus
Forum Regular
Posts: 589
Joined: Fri May 09, 2003 11:53 pm
Location: Belo Horizonte, Brazil
Contact:

Post 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.
User avatar
JayBird
Admin
Posts: 4524
Joined: Wed Aug 13, 2003 7:02 am
Location: York, UK
Contact:

Post 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
User avatar
JayBird
Admin
Posts: 4524
Joined: Wed Aug 13, 2003 7:02 am
Location: York, UK
Contact:

Post 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?
User avatar
scorphus
Forum Regular
Posts: 589
Joined: Fri May 09, 2003 11:53 pm
Location: Belo Horizonte, Brazil
Contact:

Post 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.
User avatar
JayBird
Admin
Posts: 4524
Joined: Wed Aug 13, 2003 7:02 am
Location: York, UK
Contact:

Post 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
User avatar
scorphus
Forum Regular
Posts: 589
Joined: Fri May 09, 2003 11:53 pm
Location: Belo Horizonte, Brazil
Contact:

Post 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.
User avatar
JayBird
Admin
Posts: 4524
Joined: Wed Aug 13, 2003 7:02 am
Location: York, UK
Contact:

Post 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
Post Reply