I have a calendar script that displays up to 6 appointments per morning and per afternoon on any given day. It counts how many are remaining and if all 6 are taken then displays FULL. What I would also like to do is to be able to block out a morning, afternoon, whole day or even whole week (for a holiday for example) and mark them as FULL without having to add 6/12/84 individual rows to the db.
I have tried adding a column called 'block' and setting it's value to 1 for a given day, 0 as default, but I can't get both queries to run at the same time. It then doesn't display the days that still have 6 appointments (i.e. don't have any rows in the db therefore don't have a block value of 0 OR 1). I've tried every which way of nesting 2 while loops and I've tried IF EXISTS in the sql, exists/isset in the PHP but just can't get it to work. Can anyone please help?
date_default_timezone_set('Europe/London');
// Get prev & next month
if (isset($_GET['ym'])) {
$ym = $_GET['ym'];
} else {
// This month
$ym = date('Y-m');
}
// Check format
$timestamp = strtotime($ym . '-01'); // the first day of the month
if ($timestamp === false) {
$ym = date('Y-m');
$timestamp = strtotime($ym . '-01');
}
// Today (Format:2018-08-8)
$today = date('Y-m-d');
$todaynum = date('N');
$hour = date('a');
// Title (Format:August, 2018)
$title = date('F, Y', $timestamp);
// Display Month (Format: August), Display Year (Format: 2018)
$dismth = date(' F ', $timestamp);
$disyr = date('Y', $timestamp);
// Current Year (Format:2018-), Current Month (Format:08-),
$curryr = date('Y-', $timestamp);
$currmth = date('m-', $timestamp);
// Create prev & next month link
$prev = date('Y-m', strtotime('-1 month', $timestamp));
$next = date('Y-m', strtotime('+1 month', $timestamp));
// Number of days in the month
$day_count = date('t', $timestamp);
// 1:Mon 2:Tue 3: Wed ... 7:Sun
$str = date('N', $timestamp);
// Array for calendar
$weeks = [];
$week = '';
// Add empty cell(s)
$week .= str_repeat('<td></td>', $str - 1);
for ($day = 1; $day <= $day_count; $day++, $str++) {
/* create a variable to concantenate the dates into db format */
/*$chosen = $curryr.$currmth.$day;*/
if ($day < 10) {
$date = $ym . '-0' . $day;
}
else {
$date = $ym . '-' . $day;
}
/* find if it's a weekend */
$weekdays = strtotime($date);
$weekday = date('l', $weekdays);
/* create a variable to concantenate the day and dates into display format */
$display = $weekday.', '.$day.$dismth.$disyr;
/* query - count number of appointments for current day and time */
$sqlam = "SELECT count(*) AS amapp FROM wasps_appointments WHERE date = '$date' AND time = 'Morning'";
$resultam = $connection->query($sqlam);
$sqlpm = "SELECT count(*) AS pmapp FROM wasps_appointments WHERE date = '$date' AND time = 'Afternoon'";
$resultpm = $connection->query($sqlpm);
/* start cell - check if today and if yes add class */
if ($today == $date) {
$week .= '<td class="today">';
} else {
$week .= '<td>';
}
/* Write day number into cell */
$week .= '<span class="date">';
$week .= $day;
$week .= '</span>';
/* if weekend show nothing */
if ( $weekday == 'Saturday' || $weekday == 'Sunday' ){ }
/* else (if not weekend) show links */
else {
/* ------------------------ MORNING -----------------------*/
/* display morning appointment availability from query above */
while($row = mysqli_fetch_array($resultam)){
/* if in the future show links */
if ($date >= $today){
/* count how many remaining */
$amremain = 6 - $row['amapp'];
/* change colour according to availability*/
$trafficlight = "green";
if ($amremain == 3 || $amremain == 2) { $trafficlight = "amber";}
elseif ($amremain == 1) { $trafficlight = "red";}
/* check if fully booked */
if ($amremain == 0) {
$week .= 'am <span class="am full">FULL</span><br />';
}
else {
/* write dates into form fields from link */
$week .= 'am <span class="am '.$trafficlight.'"><a href="#form" onClick="document.getElementById(\'displaydate\').value=\'' . $display . '\';document.getElementById(\'chosendate\').value=\'' . $date . '\';document.getElementById(\'displaytime\').value=\'Morning\'">' . $amremain . ' left</a></span><br />';
}
}
/* else if in the past show nothing */
else { }
}
/* ------------------------ END MORNING -----------------------*/
/* ------------------------ AFTERNOON -----------------------*/
/* display afternoon appointment availability from query above */
while($row = mysqli_fetch_array($resultpm)){
/* if in the future show links */
if ($date >= $today){
$pmremain = 6 - $row['pmapp'];
/* change colour according to availability*/
$trafficlight = "green";
if ($pmremain == 3 || $pmremain == 2) { $trafficlight = "amber";}
elseif ($pmremain == 1) { $trafficlight = "red";}
if ($pmremain == 0) {
$week .= 'pm <span class="pm full">FULL</span>';
}
else {
/* write dates into form fields from link */
$week .= 'pm <span class="pm '.$trafficlight.'"><a href="#form" onClick="document.getElementById(\'displaydate\').value=\'' . $display . '\';document.getElementById(\'chosendate\').value=\'' . $date . '\';document.getElementById(\'displaytime\').value=\'Afternoon\'">' . $pmremain . ' left</a></span>';
}
}
/* else if in the past show nothing */
else { }
}
/* ------------------------ END AFTERNOON -----------------------*/
}
/* end cell */
$week .= '</td>';
// Sunday OR last day of the month
if ($str % 7 == 0 || $day == $day_count) {
// last day of the month
if ($day == $day_count && $str % 7 != 0) {
// Add empty cell(s)
$week .= str_repeat('<td></td>', 7 - $str % 7);
}
$weeks[] = '<tr>' . $week . '</tr>';
$week = '';
}
}
?>
<div id="calendar">
<div class="datenavigation"><a href="?ym=<?= $prev; ?>#calendar">< prev</a> <span class="title"><?= $title; ?></span> <a href="?ym=<?= $next; ?>#calendar">next ></a><!-- <a href="#calendar">today</a>--></div>
<div class="key"><span class="am">Morning 8am to 12pm</span><br /><span class="pm">Afternoon 1pm to 6pm</span></div>
<table>
<thead>
<tr>
<th>M</th>
<th>T</th>
<th>W</th>
<th>T</th>
<th>F</th>
<th>S</th>
<th>S</th>
</tr>
</thead>
<tbody>
<?php
foreach ($weeks as $week) {
echo $week;
}
?>
</tbody>
</table>
</div>