Introduction
In other words, you need to do a comparison of all reservation intervals (UNIX timestamps) for a particular resource to determine if a new reservation is valid (within the domain for new reservations).
Step 1
First, a SQL query similar to this might help. While key words like ANY, ALL, NOT, EXISTS and others may seem tempting, it is up to you to decide how much information you need in the event of a scheduling conflict (based on your UI). This query provides the opportunity to extract the maximum amount of information (in PHP, etc ...) about a potential reservation with look ahead forecasting.
// A query like this might help. It's not perfect, but you get the idea.
// This query looks for ALL potential conflicts, starting and ending.
$sql = "SELECT DISTINCT `t1`.`startTime`, `t1`.`endTime`
FROM `reservations` AS `t1`
INNER JOIN `resources` AS `t2`
ON `t1`.`resourceId` = `t2`.`resourceId`
WHERE `t2`.`resourceId` = :resourceId
AND (`t1`.`startTime` BETWEEN :minTime1 AND :maxTime1)
OR (`t1`.`endTime` BETWEEN :minTime2 AND :maxTime2)
ORDER BY `t1`.`startTime` ASC";
Potentially. this will leave you with a multi-dimentional array. The following logic allows you to get a report detailing why the reservation cannot be made. It is up to you to interpret the report in another module.
Step 2
Generalize the solution as a methods of a Reservation class. Depending on your RDBMS, you may be able to do something like this in SQL. Although, it will probably be far less specific and you may want that granularity later. You could send the report in JSON format to a JavaScript front end (just something to think about).
private function inOpenDomain(array $exclusion, $testStart, $testEnd)
{
$result = null;
$code = null;
$start = $exclusion[0];
$end = $exclusion[1];
if (($testStart > $end) || ($testEnd < $start)) {
$result = true;
$code = 0; //Good! No conflict.
} elseif ($testStart === $start) {
$result = false;
$code = 1;
} elseif ($testStart === $end) {
$result = false;
$code = 2;
} elseif ($testEnd === $start) {
$result = false;
$code = 3;
} elseif ($testEnd === $end) {
$result = false;
$code = 4;
} elseif (($testStart > $start) && ($testEnd < $end)) { //Middle
$result = false;
$code = 5;
} elseif (($testStart < $start) && ($testEnd > $start)) { //Left limit
$result = false;
$code = 6;
} elseif (($testStart < $end) && ($testEnd > $end)) { //Right limit
$result = false;
$code = 7;
} elseif (($testStart < $start) && ($testEnd > $end)) { //Both limits
$result = false;
$code = 8;
} else {
$result = false;
$code = 9;
}
return ['start' => $start, 'end' => $end, 'result' => $result => 'code' => $code];
}
Step 3
Make a method that manages the checking of prior reservation times (assuming PDO::FETCH_ASSOC).
private function checkPeriods(array $periods, $newStartTime, $newEndTime)
{
$report = [];
if (!isset($periods[0])) { //If NOT multi-dimensional
$report = inOpenDomain($periods, $newStartTime, $newEndTime)
} else {
for ($i = 0, $length = $count($periods); $i < $length; ++$i) {
$report[$i] = inOpenDomain($periods[$i], $newStartTime, $newEndTime);
}
}
return $report;
}
Step 4
Fashion a method for doing a SELECT on the reservations table using a PDO prepared statement. Generally, ...
private function getReservationTimes($resourceId, $minTime, $maxTime)
{
$sql = "SELECT DISTINCT `t1`.`startTime`, `t1`.`endTime`
FROM `reservations` AS `t1`
INNER JOIN `resources` AS `t2`
ON `t1`.`resourceId` = `t2`.`resourceId`
WHERE `t2`.`resourceId` = :resourceId
AND (`t1`.`startTime` BETWEEN :minTime1 AND :maxTime1)
OR (`t1`.`endTime` BETWEEN :minTime2 AND :maxTime2)
ORDER BY `t1`.`startTime` ASC";
$stmt = $this->db->prepare($sql);
$stmt->bindParam(:resourceId , $resourceId);
$stmt->bindParam(:minTime1 , $minTime);
$stmt->bindParam(:maxTime1 , $maxTime);
$stmt->bindParam(:minTime2 , $minTime);
$stmt->bindParam(:maxTime2 , $maxTime);
$stmt->execute();
return $stmt->fetchAll();
}
Step 5
Make a public method (interface) for the entire process.
public function isOpen($minTime, $maxTime)
{
$periods = $this->getReservationTimes($this->resource->getResourceId(), $minTime, $maxTime);
if (empty($periods)) {
return true; //You may reserve the resource during the time period.
}
return $this->checkPeriods($periods, $this->start, $this->end));
}
Step 6
Separate the concerns.
Create a class hierarchy for the actual items being reserved.
abstact class Product
{
}
class Resource extends Product implements Reservable //Or, something ...
{
private $resourceId;
//etc ....
}
Create a class hierarchy for reservations.
abstract class Interval
{
private $start;
private $end;
public function __construct($start, $end)
{
$this->start = $start;
$this->end = $end;
}
}
class Reservation extends Interval
{
private $db;
private $resource;
public function __construct(PDO $pdo, Reservable $resource, $reqStartTime, $reqEndTime)
{
parent::__construct($reqStartTime, $reqEndTime);
$this->db = $pdo;
$this->resource = $resource;
}
}
Step 7
Run within try/catch
When you instantiate the Reservation object, supply at least a Reservable object, the requested start time, and requested end time (as UNIX timestamps, in this case).
try
{
$day = 84600; // Seconds per day.
$future = $day * 90; // Application specific.
//User requested times.
$reqStartTime = 1488394687 + $day; // Tomorrow.
$reqEndTime = 1488394687 + ($day * 2); // Two day duration.
//Search constraints.
$minTime = time(); // Today. Right now.
$maxTime = 1488394687 + $future; // 90 day look ahead.
$reservation = new Reservation($pdo, $resourceObj, $reqStartTime, $reqEndTime);
$availability = $reservation->isOpen($minTime, $maxTime);
if($availability === true){
$reservation->confirm();
} else {
//Have some other object deal with the report
$reporter = new Reporter($availability);
$reporter->analyzeReport();
//Have some other object update the view, etc ...
}
}
catch(Exception $e)
{
//Handle it.
}
