Hi,
I’m developing a calendar that is designed to show all the jobs scheduled for my company.
I’ve been working on creating a text field that lets a user enter the address, job type and/or date into this field and using jquery’s autocomplete it will show a list which when an item is clicked on will take the user to the next occurrence of that job.
My problem is that while constructing the date for the next occurrence with PHP for some reason a large portion of events repeated on a 4 weekly basis that are not on Monday seem to generate the date of the day before they are actually on, but when clicked on it will open the correct event on the correct day.
Below is the PHP loadSearchItems.php File (bug in the date creation used for $html, the filtering of duplicates works as expected)
<?php
//Used to filter duplicates out
//Gets the next future occurrence
//params:
// - $start : The start date
// - $rec : The recurring type
// $type_$interval_$day_$interval2_$days#extra
function getNextDate($start, $rec) {
$serviceDate = mysql_escape_string($start);
$rec_type = mysql_escape_string($rec);
$s = explode('-', $serviceDate);
$DAY = $s[2];
$MONTH = $s[1];
$YEAR = $s[0];
$rec_type2 = explode("_", $rec_type);
$type = $rec_type2[0];
$interval = $rec_type2[1];
$day = $rec_type2[2];
$interval2 = $rec_type2[3];
$days = $rec_type2[4];
$days = explode("#", $days);
$days = explode(",", $days[0]);
$nextService = "1970-01-01 00:00:00";
//Check what the recurrence type is
if ($type == "day") {
$nextService = date("Y-m-d H:i:s", (strtotime($serviceDate) + (86400 * $interval)));
} else if ($type == "week") {
$d = date("w", strtotime($serviceDate));
if ($d == 0)
$d = "sunday";
else if ($d == 1)
$d = "monday";
else if ($d == 2)
$d = "tuesday";
else if ($d == 3)
$d = "wednesday";
else if ($d == 4)
$d = "thursday";
else if ($d == 5)
$d = "friday";
else if ($d == 6)
$d = "saturday";
$dt = strtotime("next $d", mktime(0, 0, 0, $MONTH, ($DAY + ($interval * 7) - 7), $YEAR));
$nextService = date("Y-m-d H:i:s", $dt);
} else if ($type == "month") {
if ($day == "" && $interval2 == "") {
$y = $YEAR;
$m = $MONTH;
$m = ($m + $interval);
$yAdjust = floor(($m / 12));
$y = ($y + $yAdjust);
$mAdjust = $m % 12;
if ($mAdjust == 0)
$m = "12";
else if ($mAdjust < 10)
$m = "0".$m;
$d = $DAY;
$dt = $y."-".$m."-".$d;
$nextService = date("Y-m-d H:i:s", strtotime($dt));
} else {
$y = $YEAR;
$m = $MONTH;
$m = ($m + $interval);
if ($m > 12) {
$yAdjust = floor(($m / 12));
$y = ($y + $yAdjust);
$m = ceil($m / 12);
if ($m == 0)
$m = "12";
else if ($m < 10)
$m = "0".$m;
}
$d = $day;
if ($d == 0)
$d = "sunday";
else if ($d == 1)
$d = "monday";
else if ($d == 2)
$d = "tuesday";
else if ($d == 3)
$d = "wednesday";
else if ($d == 4)
$d = "thursday";
else if ($d == 5)
$d = "friday";
else if ($d == 6)
$d = "saturday";
$mW = $interval2;
if ($mW == "1")
$mW = 'first';
else if ($mW == "2")
$mW = 'second';
else if ($mW == "3")
$mW = 'third';
else if ($mW == "4")
$mW = 'fourth';
else if ($mW == "5")
$mW = 'fifth';
$dt = strtotime("$mW $d", mktime(0, 0, 0, $m, 1, $y));
$nextService = date("Y-m-d H:i:s", $dt);
}
} else if ($type == "year") {
if ($day == "" && $interval2 == "") {
$y = ($YEAR + $interval);
$m = $MONTH;
if ($m < 10)
$m = "0".$m;
$d = $DAY;
if ($d < 10)
$d = "0".$d;
$dt = strtotime($y."-".$m."-".$d);
$nextService = date("Y-m-d H:i:s", $dt);
} else {
$y = ($YEAR + $interval);
$d = $day;
if ($d == 0)
$d = "sunday";
else if ($d == 1)
$d = "monday";
else if ($d == 2)
$d = "tuesday";
else if ($d == 3)
$d = "wednesday";
else if ($d == 4)
$d = "thursday";
else if ($d == 5)
$d = "friday";
else if ($d == 6)
$d = "saturday";
$mW = $interval2;
if ($mW == "1")
$mW = 'first';
else if ($mW == "2")
$mW = 'second';
else if ($mW == "3")
$mW = 'third';
else if ($mW == "4")
$mW = 'fourth';
else if ($mW == "5")
$mW = 'fifth';
$dt = strtotime("$mW $d", mktime(0, 0, 0, $m, 1, $y));
$nextService = date("Y-m-d H:i:s", $dt);
}
}
//While the next occurrence date is in the past find the next date
while (strtotime($nextService) < strtotime(date("Y-m-d H:i:s"))) {
$sD = explode(" ", $nextService);
$sD = explode("-", $sD[0]);
$YEAR = $sD[0];
$MONTH = $sD[1];
$DAY = $sD[2];
if ($type == "day") {
$nextService = date("Y-m-d H:i:s", (strtotime($nextService) + (86400 * $interval)));
} else if ($type == "week") {
$d = date("w", strtotime($nextService));
if ($d == 0)
$d = "sunday";
else if ($d == 1)
$d = "monday";
else if ($d == 2)
$d = "tuesday";
else if ($d == 3)
$d = "wednesday";
else if ($d == 4)
$d = "thursday";
else if ($d == 5)
$d = "friday";
else if ($d == 6)
$d = "saturday";
$dt = strtotime("next $d", mktime(0, 0, 0, $MONTH, ($DAY + ($interval * 7) - 7), $YEAR));
$nextService = date("Y-m-d H:i:s", $dt);
} else if ($type == "month") {
if ($day == "" && $interval2 == "") {
$y = $YEAR;
$m = $MONTH;
$m = ($m + $interval);
$yAdjust = floor(($m / 12));
$y = ($y + $yAdjust);
$mAdjust = $m % 12;
if ($mAdjust == 0)
$m = "12";
else if ($mAdjust < 10)
$m = "0".$m;
$d = $DAY;
$dt = $y."-".$m."-".$d;
$nextService = date("Y-m-d H:i:s", strtotime($dt));
} else {
$y = $YEAR;
$m = $MONTH;
$m = ($m + $interval);
if ($m > 12) {
$yAdjust = floor(($m / 12));
$y = ($y + $yAdjust);
$m = ceil($m / 12);
if ($m == 0)
$m = "12";
else if ($m < 10)
$m = "0".$m;
}
$d = $day;
if ($d == 0)
$d = "sunday";
else if ($d == 1)
$d = "monday";
else if ($d == 2)
$d = "tuesday";
else if ($d == 3)
$d = "wednesday";
else if ($d == 4)
$d = "thursday";
else if ($d == 5)
$d = "friday";
else if ($d == 6)
$d = "saturday";
$mW = $interval2;
if ($mW == "1")
$mW = 'first';
else if ($mW == "2")
$mW = 'second';
else if ($mW == "3")
$mW = 'third';
else if ($mW == "4")
$mW = 'fourth';
else if ($mW == "5")
$mW = 'fifth';
$dt = strtotime("$mW $d", mktime(0, 0, 0, $m, 1, $y));
$nextService = date("Y-m-d H:i:s", $dt);
}
} else if ($type == "year") {
if ($day == "" && $interval2 == "") {
$y = ($YEAR + $interval);
$m = $MONTH;
if ($m < 10)
$m = "0".$m;
$d = $DAY;
if ($d < 10)
$d = "0".$d;
$dt = strtotime($y."-".$m."-".$d);
$nextService = date("Y-m-d H:i:s", $dt);
} else {
$y = ($YEAR + $interval);
$d = $day;
if ($d == 0)
$d = "sunday";
else if ($d == 1)
$d = "monday";
else if ($d == 2)
$d = "tuesday";
else if ($d == 3)
$d = "wednesday";
else if ($d == 4)
$d = "thursday";
else if ($d == 5)
$d = "friday";
else if ($d == 6)
$d = "saturday";
$mW = $interval2;
if ($mW == "1")
$mW = 'first';
else if ($mW == "2")
$mW = 'second';
else if ($mW == "3")
$mW = 'third';
else if ($mW == "4")
$mW = 'fourth';
else if ($mW == "5")
$mW = 'fifth';
$dt = strtotime("$mW $d", mktime(0, 0, 0, $m, 1, $y));
$nextService = date("Y-m-d H:i:s", $dt);
}
}
}
return $nextService;
}
function checkDatesInArray($arrs = "", $item) {
$items = array();
for($i = 0; $i < count($arrs); $i++) {
if (strtotime($arrs[$i][2]) > strtotime($item[2]) && $arrs[$i][4] == $item[4] && $arrs[$i][14] == $item[14]) {
return $i;
}
}
return count($arrs);
}
$html = "";
$html .= "var addressList = [";
$con = calendar_dbOpen();
//Get the next occurrences for the series' that are in the future
$query = "SELECT event_pid, tasks_id FROM tasks WHERE abn = '$abn' AND event_pid != '0' AND end_date >= curdate()";
$result = mysql_query($query, $con);
$q = "";
if (mysql_num_rows($result) > 0) {
while ($item = mysql_fetch_array($result))
$q .= " OR tasks_id = '" . $item[0] . "' OR tasks_id = '" . $item[1] . "'";
}
$q = substr($q, 4);
//SELECT everything related to the task where the id's are of the previous search.
//tasks returned will be those of future occurrences and the series relating to those occurrences
$query = "SELECT * FROM tasks WHERE $q ORDER BY address ASC";
$result = mysql_query($query, $con);
$itemsExcluded = array();
if (mysql_num_rows($result) > 0) {
$items = array();
//Loop though all returned results
while ($item = mysql_fetch_array($result)) {
//if event_pid == 0, then check when it's next occurrence is and set that as the start date
if ($item[6] == "0") {
$item[2] = getNextDate($item[2], $item[5]);
}
$tasksIdList[] = $item[0];
//Don't allow a series to be shown if an occurrence occurs on that day
$newId = checkDatesInArray($items, $item);
$itemsExcluded[] = $items[$newId][0];
$items[$newId] = $item;
}
}
$ii = 0;
while($ii < count($items)) {
$newId = "N/A";
for($i = 0; $i < count($items); $i++) {
if ($items[$i][4] == $items[$ii][4] && $items[$i][14] == $items[$ii][14] && $i != $ii) {
if (strtotime($items[$i][2]) > strtotime($items[$ii][2]))
$newId = $i;
else
$newId = $ii;
$i = count($items);
$itemsExcluded[] = $items[$newId][0];
}
}
$ii++;
}
//Get all the tasks details for the company and have not been excluded due to being duplicate events
$query = "SELECT address, service, event_length, start_date, end_date, rec_type, event_pid FROM tasks WHERE abn = '$abn'";
for($i = 0; $i < count($itemsExcluded); $i++)
$query .= " AND tasks_id != '".$itemsExcluded[$i]."'";
$query .= " ORDER BY address";
$result = mysql_query($query, $con);
$items = array();
while ($item = mysql_fetch_array($result)) {
$items[] = $item;
}
//loop though all the results to find the next occurrence date
//Than store this date in $html along with the other data required for the autocomplete options
for($i = 0; $i < (mysql_num_rows($result) - 1); $i++) {
$sDate = $items[$i][3];
$eDate = $items[$i][4];
//If occurrence
if ($items[$i][6] != "0" && strtotime(date("Y-m-d H:i:s",strtotime($sDate))) > strtotime(date("Y-m-d H:i:s"))) {
$sDate = explode(" ", $sDate);
$sDate[0] = explode("-", $sDate[0]);
$sDate[0] = $sDate[0][2] . "-" . $sDate[0][1] . "-" . $sDate[0][0];
$sDate = implode(" ", $sDate);
$sDate = strtotime($sDate);
$sDate = date("d-m-Y", $sDate);
$html .= "{label:'" . $items[$i][0] . " (" . $items[$i][1] . ") [" . $sDate . "]', value:'" . $items[$i][0] . " (" . $items[$i][1] . ")'},\n";
//If series
} else if (strtotime($eDate) >= strtotime(date("Y-m-d H:i:s"))) {
$recType = $items[$i][5];
$recType = explode("#", $recType);
$recType = explode("_", $recType[0]);
$step = 0;
$sDate = explode(" ", $sDate);
$sDate[0] = explode("-", $sDate[0]);
$sDate[0] = $sDate[0][2] . "-" . $sDate[0][1] . "-" . $sDate[0][0];
$sDate = implode(" ", $sDate);
$multiplier = 0;
//Adjust the multiplier field for if the recurrence start date is a monday
//Other days of the week seem to work fine without this
if (date("D", strtotime($sDate)) == "Mon") {
$multiplier = 86400;
}
$next = strtotime($sDate);
$next = date("Y-m-d H:i:s", $next);
$next = strtotime($next);
if ($recType[0] == "day") {
$step = 86400 * $recType[1];
while($next <= strtotime(date("Y-m-d H:i:s")))
$next += $step;
} else if ($recType[0] == "week") {
$days = explode(",", $recType[4]);
$step = 604800 * $recType[1];
$next2 = $next;
$week = (86400 * 7 * $recType[1]) - 86400;
$flag = false;
while($next2 <= strtotime((date("Y-m-d H:i:s",(strtotime(date("Y-m-d H:i:s"))+$week)))) && !$flag) {
if (count($days) > 1) {
for($ii = 0; $ii < count($days); $ii++) {
$day = $days[$ii];
$next = $next2;
$next += 86400 * ($days[$ii] - 1);
if ($next > strtotime(date("Y-m-d H:i:s"))) {
$ii = count($days);
$flag = true;
}
}
} else {
$next = $next2;
$next += ($multiplier * ($days[0] - 1));
if ($next > strtotime(date("Y-m-d H:i:s"))) {
$flag = true;
}
}
$next2 += $step;
}
if ($flag == false) {
for($ii = 0; $ii < count($days); $ii++) {
$next = $next2;
if ($next > strtotime(date("Y-m-d H:i:s"))) {
$ii = count($days);
}
}
}
}
if ($recType[0] == "month" || $recType[0] == "year") {
$html .= "{label:'" . $items[$i][0] . " (" . $items[$i][1] . ")', value:'" . $items[$i][0] . " (" . $items[$i][1] . ")'},\n";
} else {
$html .= "{label:'" . $items[$i][0] . " (" . $items[$i][1] . ") [" . date("d-m-Y", $next) . "]', value:'" . $items[$i][0] . " (" . $items[$i][1] . ")'},\n";
}
}
}
$i = (mysql_num_rows($result) - 1);
if ($items[$i][6] != "0") {
$sDate = explode(" ", $sDate);
$sDate[0] = explode("-", $sDate[0]);
$sDate = $sDate[0][2] . "-" . $sDate[0][1] . "-" . $sDate[0][0];
//$sDate = implode(" ", $sDate);
$sDate = strtotime($sDate);
$sDate = date("d-m-Y", $sDate);
$html .= "{label:'" . $items[$i][0] . " (" . $items[$i][1] . ") [" . $sDate . "]', value:'" . $items[$i][0] . " (" . $items[$i][1] . ")'}\n";
} else if (strtotime($eDate) >= strtotime(date("Y-m-d H:i:s"))) {
$recType = $items[$i][5];
$recType = explode("#", $recType);
$recType = explode("_", $recType[0]);
$step = 0;
$sDate = explode(" ", $sDate);
$sDate[0] = explode("-", $sDate[0]);
$sDate[0] = $sDate[0][2] . "-" . $sDate[0][1] . "-" . $sDate[0][0];
$sDate = implode(" ", $sDate);
$multiplier = 0;
//Adjust the multiplier field for if the recurrence start date is a monday
//Other days of the week seem to work fine without this
if (date("D", strtotime($sDate)) == "Mon") {
$multiplier = 86400;
}
$next = strtotime($sDate);
$next = date("Y-m-d", $next);
$next = strtotime($next);
if ($recType[0] == "day") {
$step = 86400 * $recType[1];
while($next <= strtotime(date("Y-m-d H:i:s"))) {
$st = strtotime(date("Y-m-d H:i:s"));
$next += $step;
}
} else if ($recType[0] == "week") {
$days = explode(",", $recType[4]);
$step = 604800 * $recType[1];
$next2 = $next;
$week = (86400 * 7 * $recType[1]) - 86400;
$flag = false;
while($next2 <= strtotime((date("Y-m-d H:i:s",(strtotime(date("Y-m-d H:i:s"))+$week)))) && !$flag) {
if (count($days) > 1) {
for($ii = 0; $ii < count($days); $ii++) {
$day = $days[$ii];
$next = $next2;
$next += 86400 * ($days[$ii] - 1);
if ($next > strtotime(date("Y-m-d H:i:s"))) {
$ii = count($days);
$flag = true;
}
}
} else {
$next = $next2;
$next += ($multiplier * ($days[0] - 1));
if ($next > strtotime(date("Y-m-d H:i:s"))) {
$flag = true;
}
}
$next2 += $step;
}
if ($flag == false) {
for($ii = 0; $ii < count($days); $ii++) {
$next = $next2;
if ($next > strtotime(date("Y-m-d H:i:s"))) {
$ii = count($days);
}
}
}
}
if ($recType[0] == "month" || $recType[0] == "year") {
$html .= "{label:'" . $items[$i][0] . " (" . $items[$i][1] . ")', value:'" . $items[$i][0] . " (" . $items[$i][1] . ")'}\n";
} else {
$html .= "{label:'" . $items[$i][0] . " (" . $items[$i][1] . ") [" . date("d-m-Y", $next) . "]', value:'" . $items[$i][0] . " (" . $items[$i][1] . ")'}\n";
}
}
my_dbClose($con);
$html .= "];";
//Prints the variable addressList to the page to be used in jquery's autocomplete field
echo $html;
?>and the Jquery autocomplete function (which works)
$(function() {
<?php include "loadSearchItems.php"; ?>
$("#address").autocomplete({
source: addressList,
close: function(event, ui) {
//Get the value entered in the search box
var d = {"address":$("#address").val()};
if ($("#address").val() != "") {
$.ajax({
url: "loadAddressId.php",
type: "POST",
async: false,
dataType: "text",
data: d,
success: function(data) {
//data = the id of the series within the database
var id = data;
if (id > 0) {
var rcd = scheduler.getRecDates(id);
var d = new Date();
//loop through all recDates
for(var i = 0; i < rcd.length; i++) {
//if the recDate is in the future
if (rcd[i].start_date.getTime() >= d) {
var m = scheduler.getState().mode;
scheduler.init('scheduler_here',rcd[i].start_date,m);
//Grab the events details
var evs = scheduler.getEvents(rcd[i].start_date,rcd[i].end_date);
var flag = false;
//Handle multiple events at the same time
for (var i=0; i<evs.length; i++) {
var str = evs[i].id;
str = str.split("#");
//Check to see if the id matches either the occurrence's id or the parent's id
if (str[0] == id || id == evs[i].event_pid) {
id = evs[i].id;
flag = true;
i = evs.length;
}
}
i = rcd.length;
}
}
<?php
if ($searchOption == "skip") {
echo "scheduler.showEvent(id, scheduler.getState().mode);\n";
} else if ($searchOption == "skipOpen") {
echo "scheduler.showEvent(id, scheduler.getState().mode);\n";
echo "scheduler.showLightbox(id);\n";
} else {
echo "scheduler.showLightbox(id);\n";
}
?>
}
}
});
}
}
})
});and just to be complete the loadAddressId.php
<?php
session_start();
include "../includes/defs.php";
$text = mysql_escape_string($_POST['address']);
$address = substr($text, 0, strrpos($text, " ("));
$service = substr($text, strrpos($text, "(")+1, strlen($text)-strrpos($text, "("));
$service = substr($service, 0, strrpos($service, ")"));
$con = calendar_dbOpen();
$query = "SELECT tasks_id FROM tasks WHERE address = '$address' AND service = '$service'";
$result = mysql_query($query, $con);
$items = array();
while ($item = mysql_fetch_array($result)) {
$items[] = $item[0];
}
if (mysql_num_rows($result) > 0) {
$html = $items[0];
}
my_dbClose($con);
echo $html;
?>Any advice about how to fix the date bug in the php for the autocomplete menu would be much appreciated, it’s starting to drive me mad, I’ve spent so much time on it