Zhkt4pUh

1CREATE DEFINER=`root`@`localhost` PROCEDURE `coachSlot`(IN `startdate` DATETIME, IN `enddate` DATETIME, IN `minutesValue` INT, IN `clubId` INT, IN `coachId` 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 clubID = clubId;
11   set coachID = coachId;
12   drop temporary table if exists coachslots;
13   create temporary table if not exists coachslots
14      (
15      start_date_time DateTime,
16      end_date_time DateTime
17      );
18
19            REPEAT
20      set nextDate = DATE_ADD(thisDate, INTERVAL minutesValue MINUTE);
21      insert into coachslots (select thisDate, nextDate);
22      set thisDate = nextDate;
23   UNTIL nextDate >= str_to_date(endDate,'%Y-%m-%d %H:%i:%s')
24   END REPEAT;
25(select CS.* from  coachslots CS
26left join club_booking_sessions CBS ON ( ((CBS.book_start_time <= CS.`start_date_time` AND CBS.`book_end_time` > CS.`start_date_time`) OR  (CBS.book_start_time < CS.`end_date_time` AND CBS.`book_end_time` >= CS.`end_date_time`)) AND CBS.club_id=clubID AND CBS.coach_id=coachID)
27 
28left join club_bookings CB ON ( ((CB.book_start_time <= CS.`start_date_time` AND CB.`book_end_time` > CS.`start_date_time`) OR  (CB.book_start_time < CS.`end_date_time` AND CB.`book_end_time` >= CS.`end_date_time`)) AND CB.club_id=clubID AND CB.coach_id=coachID)
29 WHERE CBS.id is NULL AND CB.id is NULL
30ORDER BY CS.start_date_time ASC);
31 END