This has been doing my head in for months!!!!!!
Moderator: General Moderators
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.
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.
- scorphus
- Forum Regular
- Posts: 589
- Joined: Fri May 09, 2003 11:53 pm
- Location: Belo Horizonte, Brazil
- Contact:
No problem, you're welcome. I'm glad it seems to be working.Bech100 wrote:That code seems to be working really well, thank you very much.
I'll try to explain it for you. It is a case statement:Bech100 wrote:I don't fully undertand this Query, especially the "case", "when" and "end as" parts.
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";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;
}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:Also, i cant figure out why you need to doCode: Select all
array_pop($table);
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...Bech100 wrote:Just one more slight problem, any idea how i can make the code you gave me exclude saturdays and sundays!?
Hope I'm clear enough. Regards,
Scorphus.
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
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
- scorphus
- Forum Regular
- Posts: 589
- Joined: Fri May 09, 2003 11:53 pm
- Location: Belo Horizonte, Brazil
- Contact:
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.Bech100 wrote:(...) I just now need to modify that so it doesn't include saturdays or sundays (...)
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>Cheers,
Scorphus.
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
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
I can't get my head around this bit
If the day is saturday or sunday, then what is it doing?
Code: Select all
$checkDay = $dayArray[$day%7];
if ($checkDay == "Sun" || $checkDay == "Sat") {
$day++;
$dayMonths++;
for ($mDIndex = 1; $mDIndex < 13; $mDIndex++)
$monthDays[$mDIndex]++;
continue;
}- scorphus
- Forum Regular
- Posts: 589
- Joined: Fri May 09, 2003 11:53 pm
- Location: Belo Horizonte, Brazil
- Contact:
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.
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.
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
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
- scorphus
- Forum Regular
- Posts: 589
- Joined: Fri May 09, 2003 11:53 pm
- Location: Belo Horizonte, Brazil
- Contact:
Yes it does. I made some changes to the code, see:
I'm ready to help you figure out something you don't get.
Many cheers,
Scorphus.
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>Many cheers,
Scorphus.