Apologies, your first post was sent while I was away dealing with a death in my family.
Yes, it is possible to do this. Several folk have built booking forms which have validation code that checks for availability. I don't think anyone has done it with a repeat group, but the principle should be the same.
The general approach is always the same for availability testing, and there's a number of Stack Overflow QAs that explain it:
http://stackoverflow.com/questions/25549765/find-booking-overlaps-to-check-dates-availability
As the first question explains, the way to test an overlap between [a,b] and [c,d] is "a <= d and b >= c". If true, then there's an overlap.
Here's part of a query I use for finding overlapping bookings, in this case for portable electrical generators rather than rooms. In this query, [a,b] are existing booking in the table, and [c,d] are the requested start/end date from the form:
Code:
SELECT generator FROM generatoren_reservations
WHERE
(
CONVERT_TZ(generatoren_reservations.startdate, '+00:00', '+02:00') <= '{generatoren_reservations___enddate}'
AND
CONVERT_TZ(generatoren_reservations.enddate, '+00:00', '+02:00') >= '{generatoren_reservations___startdate}'
)
AND
generatoren_reservations.generator = '{generatoren_reservations___generator}'
It yields a selection of overlapping rows. I use that approach in two places. In validation, I use it to fail the booking - I run that query, if it yields any rows, there's an overlap, so validation fails. In the booking form itself, I use it in the WHERE clause for the generator join dropdown, like this ...
Code:
WHERE {thistable}.id NOT IN (... that query ...)
... which excludes any overlapped bookings from the dropdown. Combined with "AJAX update", this provides a way of dynamically restricting the generators they can select to ones which don't overlap. In the join WHERE clause you can use this style, of just the query with the placeholders.
In the validation, you would have to write the code to run that as a query, using the usual $myDb->query() stuff, getting your date values from $formModel->formData[]. One tip I can give you is that in the validation PHP, you have to do some sanity checking on the format of the dates, as for historical reasons, the format can be different (like when reloading a page after a validation failure).
So here's an example of a validation which makes sure start date < end date:
Code:
$startDate = $formModel->formData['generatoren_data___startdate'];
if (is_array($startDate)) {
$sDate = explode(' ', $startDate['date']);
$sDate = $sDate[0] . ' ' . ltrim($startDate['time']);
}
else {
$sDate = $startDate;
}
//var_dump($sDate);
$sDate = strtotime($sDate);
$endDate = $formModel->formData['generatoren_data___enddate'];
if (is_array($endDate)) {
$eDate = explode(' ', $endDate['date']);
$eDate = $eDate[0] . ' ' . ltrim($endDate['time']);
}
else {
$eDate = $endDate;
}
//var_dump($eDate);
$eDate = strtotime($eDate);
//var_dump($eDate, $eDate);exit;
return $eDate > $sDate;
To test the overlap, you would replace the last few lines with the database query, and test to make sure the result is empty.
On this site, we use the Full Calendar visualization for doing the booking.
This should give you something to go on. If you need further help, we would probably need to look at doing this for you as billable work, as it goes beyond the scope of subscription support.
-- hugh
