[SOLVED] Find available time slots in booking table | populate dropdown with start time of booking

marcq

marcq

Member
Hi,

I have created a form which enable visitors to book a boat for 3 hours.

http://www.screencast.com/t/XnH81oavHZbS

Booking table : fab_booking

Each time a booking is created the date|time of the booking is created, but alos the date|time where the crew needs to attend and the crew has finished is duty is added into the Booking table in the following fields :

Example :
book_crewstartdate : 2016-08-31 06:00:00
book_bookingstartdate : 2016-08-31 07:30:00
book_bookingenddate : 2016-08-31 10:30:00
book_crewenddate : 2016-08-31 12:00:00

1:30 hour are need before and after the crew to prepare|clean the boat.

The visitor can choose the date with the date picker :

Date Picker Element : book_bookingdate
Dropdown Element : book_starttime

Enable if time slot available
I would like to enable the dates in the date picker if there's are 6 hours available slots (6 hours because the crew needs 1:30 hour before and after the booking to prepare|clean the boat)

Example 1 :
If a booking exists into the fab_booking table between 12:00 and 18:00 and a training session exists between 06:00 and 08:00, then we have 1 free time slot between 18:00 and 00:00 :

Code: 
1:30 hour for the crew to prepare the boat : 18:00 to 19:30
3 hours cruising : 19:30 to 22:30
1:30 hour for the crew to clean the boat : 22:30 to 00:00

Exemple 2 :
If a training exists into the fab_booing table between 14:00 et 16:00, then we have ten 6 hours free time slots between 06:00 and 14:00 and between 16:00 et 00:00 :

Code: 
1:30 hour for the crew to prepare the boat : 06:00 to 07:30
3 hours cruising : 07:30 to 10:30
1:30 hour for the crew to clean the boat : 10:30 to 12:00

1:30 hour for the crew to prepare the boat : 06:30 to 08:00
3 hours cruising : 08:00 to 11:00
1:30 hour for the crew to clean the boat : 11:00 to 12:30

1:30 hour for the crew to prepare the boat : 07:00 to 08:30
3 hours cruising : 08:30 to 11:30
1:30 hour for the crew to clean the boat : 11:30 to 13:00

1:30 hour for the crew to prepare the boat : 07:30 to 09:00
3 hours cruising : 09:00 to 12:00
1:30 hour for the crew to clean the boat : 12:00 to 13:30

1:30 hour for the crew to prepare the boat : 08:00 to 09:30
3 hours cruising : 09:30 to 12:30
1:30 hour for the crew to clean the boat : 12:30 to 14:00

1:30 hour for the crew to prepare the boat : 16:00 to 17:30
3 hours cruising : 17:30 to 20:30
1:30 hour for the crew to clean the boat : 20:30 to 22:00

1:30 hour for the crew to prepare the boat : 16:30 to 18:00
3 hours cruising : 18:00 to 21:00
1:30 hour for the crew to clean the boat : 21:00 to 22:30

1:30 hour for the crew to prepare the boat : 17:00 to 18:30
3 hours cruising : 18:30 to 21:30
1:30 hour for the crew to clean the boat : 21:30 to 23:00

1:30 hour for the crew to prepare the boat : 17:30 to 19:00
3 hours cruising : 19:00 to 22:00
1:30 hour for the crew to clean the boat : 22:00 to 23:30

1:30 hour for the crew to prepare the boat : 18:00 to 19:30
3 hours cruising : 19:30 to 22:30
1:30 hour for the crew to clean the boat : 22:30 to 00:00

Important : Training sessions and internal events are also saved into the same fab_booking table.

Bookings starts every 30 minutes
Internal Events starts every 30 minutes
Trainings every 15 minutes

Populate the dropdown with the available startime

http://www.screencast.com/t/oyJAJB3gH8

So according example 1 I would need to populate the dropdown Element book_starttime on the fly with the following values and labels :

Code: 
Valeur | Label
25       | 19:30

And according example 2 with the following values and labels :

Code: 
Valeur | Label
1       | 07:30
2       | 08:00
3       | 08:30
4       | 09:00
5       | 09:30
21       | 17:30
22       | 18:00
23       | 18:30
24       | 19:00
25       | 19:30

Would appreciate some help here, since I'm stuck.

Thanks in advance,

Cheers, marc
 
Last edited:
Would appreciate some help here, since I'm stuck.
Click to expand...

Yeah, it's not trivial. And this isn't really something we can do as part of subscription support, this would have to be hourly billed custom coding.

I'll need a little more understanding of your requirements to give an accurate estimate, but off the top of my head that's probably at least a couple of hours work.

When you say "I would need to populate the dropdown Element book_starttime on the fly", would that be triggered by selecting the date?

And is the start time dropdown a dropdown element, or a join element?

-- hugh
 
Hi Hugh,

Thank you for your reply.

  1. Date Picker Element : book_bookingdate
    Only the dates for which a minimum of one 6 hours slot is available between 06:30am and midnight should be displayed. Dates with no availabilities should be disabled.
  2. Dropdown Element : book_starttime
    In case there's a slot (or more) available, the dropdown should only be populated with the start time values|lables like explained in #1.
    At the moment it is a dropdown element, but I can of course change it in a join element. I have already created the database table.
Thank you in advance for your quote.

Cheers, marc
 
You didn't really answer my question, which is does the book_starttime need to change in "real time" on the form, triggered by changing the book_bookingdate? I'm assuming it does, so when they pick a different date, they get a different selection of start times.

-- hugh
 
Hi Hugh,

Yes this is exactly how it should work.

Thanks and cheers, marc

By the way the dropdown should be populated with both value and label :

Code: 
Value | Label
1 | 07:30
2 | 08:00
3 | 08:30
4 | 09:00
5 | 09:30
6 | 10:00
7 | 10:30
8 | 11:00
9 | 11:30
10 | 12:00
11 | 12:30
12 | 13:00
13 | 13:30
14 | 14:00
15 | 14:30
16 | 15:00
17 | 15:30
18 | 16:00
19 | 16:30
20 | 17:00
21 | 17:30
22 | 18:00
23 | 18:30
24 | 19:00
25 | 19:30
[CODE]

If available start time are :

[CODE]
08:30, 09:00, 09:30, 10:00, 10:30, 18:30, 19:00, 19:30

Dropdown should be populated with the following values and labels
Code: 
Value | Label
3 | 08:30  
4 | 09:00  
5 | 09:30  
6 | 10:00  
7 | 10:30
23 | 18:30  
24 | 19:00  
25 | 19:30

Just to be clear a available slot need always to be of 6 hours :

01:30 for the crew to prepare the boat
03:00 for the tour
01:30 for the crew to clean the boat

It mean for example if we have a 6 hours slot from 06:30 to 12:30, the start time will be :

06:30 + 01:30 = 08:00

The dropdown will be populated with :

Code: 
Value | Label
2 | 08:00

I wanted to attached an excel file with some examples of already existing bookings and how much six hours slots are available per day and which value and labels should populate the dropdown, but no success. Could you please pm me an email address where I can send you this file ?
 
Last edited:
cheesegrits said:
I'm kind of swamped right now, it's going to be at least early next week before I can get to this.

-- hugh
Click to expand...
Hi Hugh,

Early next week would be perfect, since I really need to finish this project.

I have recreated a test environment with the booking form (only the needed fields) and some data. You will find all the information + your credentials under My Sites FABRIK. Cheers, marc
 
Hi Hugh,

I could find a way how to get the available slots :

1. Create a table with all possible 6 hours slots between 06:30 and midnight

Code: 
CREATE TABLE IF NOT EXISTS `fab_booking_slots` (
`id` int(10) unsigned NOT NULL,
  `time_start` time NOT NULL,
  `time_end` time NOT NULL
) ENGINE=InnoDB AUTO_INCREMENT=25 DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci ROW_FORMAT=COMPRESSED;

INSERT INTO `fab_booking_slots` (`id`, `time_start`, `time_end`) VALUES
(1, '06:30:00', '12:30:00'),
(2, '07:00:00', '13:00:00'),
(3, '07:30:00', '13:30:00'),
(4, '08:00:00', '14:00:00'),
(5, '08:30:00', '14:30:00'),
(6, '09:00:00', '15:00:00'),
(7, '09:30:00', '15:30:00'),
(8, '10:00:00', '16:00:00'),
(9, '10:30:00', '16:30:00'),
(10, '11:00:00', '17:00:00'),
(11, '11:30:00', '17:30:00'),
(12, '12:00:00', '18:00:00'),
(13, '12:30:00', '18:30:00'),
(14, '13:00:00', '19:00:00'),
(15, '13:30:00', '19:30:00'),
(16, '14:00:00', '20:00:00'),
(17, '14:30:00', '20:30:00'),
(18, '15:00:00', '21:00:00'),
(19, '15:30:00', '21:30:00'),
(20, '16:00:00', '22:00:00'),
(21, '16:30:00', '22:30:00'),
(22, '17:00:00', '23:00:00'),
(23, '17:30:00', '23:30:00'),
(24, '18:00:00', '24:00:00');

ALTER TABLE `fab_booking_slots`
ADD PRIMARY KEY (`id`);

ALTER TABLE `fab_booking_slots`
MODIFY `id` int(10) unsigned NOT NULL AUTO_INCREMENT,AUTO_INCREMENT=25;

2. Create a table with the actual booking of the day

Code: 
CREATE TABLE IF NOT EXISTS `fab_booking_taken` (
`id` int(10) unsigned NOT NULL,
  `book_date` date NOT NULL,
  `book_start` time NOT NULL,
  `book_end` time NOT NULL
) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci ROW_FORMAT=COMPRESSED;

3. Create a query that insert all the bookings for a given day

Code: 
insert into fab_booking_taken (book_date,book_start,book_end) SELECT book_bookingdate as book_date, book_crewstartdate as book_start, book_crewenddate as book_end FROM fab_booking WHERE DATE_FORMAT(book_crewstartdate,'%Y-%m-%d') = '2016-08-15' AND ('2016-08-15 06:30:00' >= book_crewstartdate AND '2016-08-15 06:30:00' < book_crewenddate OR (book_crewstartdate >= '2016-08-15 06:30:00' AND book_crewstartdate < '2016-08-16 00:00:01')) ORDER BY book_crewstartdate

Example 2016-08-15, training session of 2 hours
Code: 
+------------+----+------------+----------+
| book_date  | id | time_start | time_end |
+------------+----+------------+----------+
| 2016-08-15 | 9  | 14:00:00   | 16:00:00 |
+------------+----+------------+----------+

4. Create a query that compare the 2 tables to retrieve the 6 hours slots that are still available

Code: 
select * from ( SELECT p1.book_date, t.*, count(p1.book_date) as nbre FROM fab_booking_taken as p1 CROSS JOIN fab_booking_slots as t WHERE NOT ((t.time_start < p1.book_end and t.time_end > p1.book_start)) AND p1.book_date = '2016-08-15' GROUP BY t.id ) as x where nbre = (select count(p2.book_date) from fab_booking_taken as p2 where p2.book_date = x.book_date)
[\CODE]

This is working fine, I'll get the 9 available slots.

[CODE]
+------------+----+------------+----------+------+
| book_date  | id | time_start | time_end | nbre |
+------------+----+------------+----------+------+
| 2016-08-15 | 1  | 06:30:00   | 12:30:00 |    1 |
| 2016-08-15 | 2  | 07:00:00   | 13:00:00 |    1 |
| 2016-08-15 | 3  | 07:30:00   | 13:30:00 |    1 |
| 2016-08-15 | 4  | 08:00:00   | 14:00:00 |    1 |
| 2016-08-15 | 20 | 16:00:00   | 22:00:00 |    1 |
| 2016-08-15 | 21 | 16:30:00   | 22:30:00 |    1 |
| 2016-08-15 | 22 | 17:00:00   | 23:00:00 |    1 |
| 2016-08-15 | 23 | 17:30:00   | 23:30:00 |    1 |
| 2016-08-15 | 24 | 18:00:00   | 24:00:00 |    1 |
+------------+----+------------+----------+------+

The issues I have now :

1. Where should I run those queries : in the date element or in the dropdown element ?

2. How can I populate the dropdown with the following values and labels returned by the query :

Code: 
Value | Label
1     | 06:30
2     | 07:00
3     | 07:30
4     | 08:00
20    | 16:00
21    | 16:30
22    | 17:00
23    | 17:30
24    | 18:00

Would appreciate some support here.

Thank you in advance,

Cheers, Marc

ps: I attached a dump of the booking table
 

Attachments

  • fab_booking.txt
    6 KB · Views: 224
Last edited:
The way I was planning to do it was with pretty much that exact query (looks like one of the ones from Stack Overflow I usually wind up referring to when dealing with these "available dates" issues), as part of a where clause on a join element.

So change the dropdown element of slots to a join element to the slots table, and add a WHERE filter clause that does ...

Code: 
WHERE {thistable}.id IN (SELECT id FROM ...)

... using a query, similar to the one you came up with, but which just returns the id's of the slot table which are available for that date.

You can then use a CONCAT label on that join to display the time range for the slot.

If you use a placeholder for the chosen date in your query, and enable "AJAX Update", then it should automatically update the slot dropdown when you change the date. There may be an issue with that, I've never tried it with dates. The issue being that date elements are a bit weird, as the actual DOM element for the date is hidden, and gets updated programmatically by our code when the date picker is used. I can't remember off the top of my head if that code fires the right event for the join element to see the change.

-- hugh
 
Hi Hugh,

Thank you for your always great support and accurate advices. I appreciate and enable me to learn a lot.

Where clause of the database join dropdown element
Code: 
WHERE {thistable}.id IN (SELECT id +3 FROM (SELECT p1.book_date, t.*, count(p1.book_date) AS nbre FROM fab_booking_taken AS p1 CROSS JOIN fab_booking_slots AS t WHERE NOT ((t.heuredepart_resa < p1.book_end AND t.heurearrivee_resa > p1.book_start)) AND p1.book_date = '{fab_booking___book_bookingdate}' GROUP BY t.id ) AS x WHERE nbre = (SELECT count(p2.book_date) FROM fab_booking_taken AS p2 WHERE p2.book_date = x.book_date))

Everything is working as expected and the dropdown is populated with the appropriate labels, but only when there's already a value to compare into the ""fab_booking_taken". But when there's no value, then dropdown is empty.

Is there a way to have conditional where clauses here, for example :

If value exist then run this query :
Code: 
WHERE {thistable}.id IN (SELECT id +3 FROM (SELECT p1.book_date, t.*, count(p1.book_date) AS nbre FROM fab_booking_taken AS p1 CROSS JOIN fab_booking_slots AS t WHERE NOT ((t.heuredepart_resa < p1.book_end AND t.heurearrivee_resa > p1.book_start)) AND p1.book_date = '{fab_booking___book_bookingdate}' GROUP BY t.id ) AS x WHERE nbre = (SELECT count(p2.book_date) FROM fab_booking_taken AS p2 WHERE p2.book_date = x.book_date))
Else run this query :
Code: 
WHERE {thistable}.id IN (SELECT id +3 FROM fab_booking_slots)

Thank you in advance for your reply.

Cheers, Marc
 
UPDATE :

I have created a calc element which is counting the number of records from the "fab_booking_taken" table where the book_date is equal to the booking date chosen by the user with the datepicker :

Code: 
$db = JFactory::getDbo();
$query = $db->getQuery(true);
$query = "SELECT COUNT(*) FROM fab_booking_taken2 WHERE book_date = '{fab_booking___book_bookingdate}'";
$db->setQuery($query);
$count = $db->loadResult();
return $count;

Now would be great if I could use this in the where clause of the database join dropdown element.

If $count > 0 use one where clause
Else use another one

But it seems not to be possible, so what do I have for an alternative ?
 
Last edited:
The only way I can think of to work round that is to use NOT IN rather than IN, and have your subquery return the taken slots, not the free slots.

What you are doing now is ...

WHERE {thistable}.id IN (SELECT all free slots, which returns empty set if there isn't a trip booked for that date)

... change that to ...

WHERE {thistable}.id NOT IN (SELECT all reserved slots, which returns empty if there isn't a trip booked for that date)

I think you can do that fairly easily with your existing query by just tweaking the WHERE part

-- hugh
 
Hi Hugh,

Thank you for your always outstanding and helpfull support!

I find another solution, which is easier for me to implement and which work.

a. I have already a database join dropdown element (called first dropdown) which is returning values as explained in the previous posts

1. I have created a second database join dropdown element which return all the available time slots
2. I have created a calc element which is returning a value.
3a. If the value is = 0 then the second database join dropdown is displayed and the first dropdown is hidden.
3b. If the value is > 0 then the first dropdown is shown.

Each dropdown as it own Data - Where clause :

1. First dropdown is returning only the available slots
Code: 
WHERE {thistable}.id IN (SELECT id +3 FROM (SELECT p1.book_date, t.*, count(p1.book_date) AS nbre FROM fab_booking_taken AS p1 CROSS JOIN fab_booking_slots AS t WHERE NOT ((t.heuredepart_resa < p1.book_end AND t.heurearrivee_resa > p1.book_start)) AND p1.book_date = '{fab_booking___book_bookingdate}' GROUP BY t.id ) AS x WHERE nbre = (SELECT count(p2.book_date) FROM fab_booking_taken AS p2 WHERE p2.book_date = x.book_date))

2. Second drop down is returning all available slots
Code: 
WHERE {thistable}.id IN (SELECT id +3 FROM fab_booking_slots2)

It is perhaps not the state of the art, but it is working fine.

Now I just need to disable or enable dates into the date Element.

I have already a Date function into the Advanced tab :

Code: 
$myDb = JFactory::getDbo();

$myQuery = $myDb->getQuery(true);
$myQuery
    ->select ('DATE(book_bookingdate)')
    ->from('fab_booking');
  
  
$myDb->setQuery($myQuery);
$bookedDates = $myDb->loadColumn();

$begin = new DateTime();
$end = new DateTime('2026-12-31');

$interval = DateInterval::createFromDateString('1 day');
$period = new DatePeriod($begin, $interval, $end);

$myAllowedDates = array();

foreach( $period as $dt ) {
    $thisDate = $dt->format("Y-m-d");
    if (!in_array($thisDate, $bookedDates)) {
    $myAllowedDates[] = $thisDate;
    }
}
return $myAllowedDates;

You helped me setting up this function which is enabling the date which are not already booked.


Now I need to disable all dates which have already 2 bookings (since maximum 2 bookings per day are possible), so I changed the query with the following :

Code: 
$myDb = JFactory::getDbo();

$myQuery = $myDb->getQuery(true);
$myQuery = "SELECT fab_booking.book_bookingdate, COUNT(fab_booking_taken2.book_date) AS Total FROM fab_booking LEFT JOIN fab_booking_taken2 ON fab_booking.book_bookingdate = fab_booking_taken2.book_date GROUP BY fab_booking.book_bookingdate HAVING (Total / 2) > 1";
$myDb->setQuery($myQuery);
$bookedDates = $myDb->loadColumn();
// var_dump($bookedDates);exit;
$begin = new DateTime();
$end = new DateTime('2026-12-31');

$interval = DateInterval::createFromDateString('1 day');
$period = new DatePeriod($begin, $interval, $end);

$myAllowedDates = array();

foreach( $period as $dt ) {
    $thisDate = $dt->format("Y-m-d");
    if (!in_array($thisDate, $bookedDates)) {
    $myAllowedDates[] = $thisDate;
    }
}
return $myAllowedDates;

HAVING (Total / 2) > 1
Click to expand...
since the count is returning "4" because it counts the 2 dates from the "fab_booking" table and the 2 dates from the "fab_booking_taken2" table.

Indeed everytime a booking is created the "fab_booking" table is populated with the booking informations and the "fab_booking_taken2" is populated with the booking date, the start time and the end time of the booking data I need to populate the starttime dropdown.

I var_dumped the $bookedDates of the query and the returned value is correct "2016-09-09", because I've got already 2 bookings on "2016-09-09" so this date should be excluded | disabled from the datepicker, which is not the case, I can still choose this date with the datepicker.

Any clue why ?

Thank you in advance for your support, cheers, marc
 
No idea.

You might want to install JDump, which makes dumping variables easier, as they then show up in a popup in the browser, and you can let the code run without putting an exit in.

http://extensions.joomla.org/extension/j-dump

So with that installed and enabled, before the foreach ...

dump($bookedDates, 'bookedDates');

... and in the foreach, after you format $thisDate ...

dump($thisDate, 'thisDate');

-- hugh
 
You must log in or register to reply here.

Useful links

  • Create a Fabrik Account (Downloads)
  • Create a Forum Account
  • Fabrik Wiki
  • Fabrik 4 Changelog /Install /Upgrade
    • We are in need of some funding.
    More details.

    Thank you.

    Members online

    Total: 10 (members: 2, guests: 8)
    Back
    Top