JfyNtk7R

1CREATE DEFINER=`root`@`localhost` PROCEDURE `slots`(IN `startdate` DATETIME, IN `enddate` DATETIME, IN `minutesValue` INT, IN `courtId` INT)
2    READS SQL DATA
3BEGIN
4   declare thisDate DateTime;
5   declare nextDate DateTime;
6   set thisDate = startdate;
7   if(minutesValue=0) THEN
8   set minutesValue =30;
9   end if;
10   set courtID = courtId;
11   drop temporary table if exists slots;
12   create temporary table if not exists slots
13      (
14      start_date_time DateTime,
15      end_date_time DateTime
16      );
17
18            REPEAT
19      set nextDate = DATE_ADD(thisDate, INTERVAL minutesValue MINUTE);
20      insert into slots (select thisDate, nextDate);
21      set thisDate = nextDate;
22   UNTIL nextDate >= str_to_date(endDate,'%Y-%m-%d %H:%i:%s')
23   END REPEAT;
24(select S.* from  slots S
25left join club_booking_sessions CBS ON ( ((CBS.book_start_time <= S.`start_date_time` AND CBS.`book_end_time` > S.`start_date_time`) OR  (CBS.book_start_time < S.`end_date_time` AND CBS.`book_end_time` >= S.`end_date_time`)) AND CBS.court_id=courtID)
26 
27left join club_bookings CB ON ( ((CB.book_start_time <= S.`start_date_time` AND CB.`book_end_time` > S.`start_date_time`) OR  (CB.book_start_time < S.`end_date_time` AND CB.`book_end_time` >= S.`end_date_time`)) AND CB.court_id=courtID)
28 WHERE CBS.id is NULL AND CB.id is NULL
29ORDER BY S.start_date_time ASC);
30 END