· 6 years ago · Feb 20, 2020, 09:06 AM
1 /**
2 * getFreeTimeSlots this function will return free timeslots based on clubId and date, coach_id and court id
3 * This api is getting called only from Mobile App
4 */
5 public function getFreeTimeSlots(){
6 if ($this->request->is('post')) {
7 $this->loadModel('ClubBookingSession');
8 $this->loadModel('Setting');
9 $this->loadModel('User');
10 $this->loadModel('Club');
11 $this->loadModel('Court');
12
13 $club_slots = array();
14 $club_id = $this->request->data['club_id'];
15 $booking_date = $this->request->data['booking_date'];
16 $user_id = $this->request->data['user_id'];
17
18
19
20
21
22 if(isset($this->request->data['coach_id'])){
23 $coach_id = $this->request->data['coach_id'];
24 }
25 else{
26 $coach_id = "";
27 }
28
29 //$clubSetting = $this->Setting->findByClubId($club_id);
30 $clubSetting = $this->Setting->find('first', array (
31 'conditions' => array (
32 'Setting.club_id' => $club_id
33 ),
34 'fields' => array (
35 'Setting.time_slot_gap',
36 'Club.default_start_time',
37 'Club.default_end_time',
38 'Club.timezone_id'
39 )
40 ));
41 // $clubDetails = $this->User->Club->findById($club_id);
42 // $clubDetails = $this->User->Club->find('first', array (
43 // 'conditions' => array (
44 // 'Club.id' => $club_id
45 // ),
46 // 'fields' => array (
47 // 'Timezone.id',
48 // 'Club.default_start_time',
49 // 'Club.default_end_time',
50 // )
51 // ));
52 $clubTimezoneOffset = $this->Common->getClubTimezoneOffset($clubSetting['Club']['timezone_id']);
53 $courtCounter = 0;
54 $day = strtoupper(date('D',strtotime($booking_date)));
55
56 if(empty($clubSetting)){
57 $response['status'] = false;
58 $response['message'] = 'Club not found.';
59 echo json_encode($response);
60 die;
61 }
62
63 if(empty($clubSetting)){
64 $response['status'] = false;
65 $response['message'] = 'Club setting not found.';
66 echo json_encode($response);
67 die;
68 }
69
70 //$userBuysSlots = $this->checkPlayerAvailabilityFromFreeslots($user_id ,$booking_date, $clubTimezoneOffset, true);
71
72 $duration = $clubSetting['Setting']['time_slot_gap']; // in minutes
73 $book_start_datetime = $booking_date.' '.$clubSetting['Club']['default_start_time'];
74 $book_end_datetime = $booking_date.' '.$clubSetting['Club']['default_end_time'];
75
76 if(isset($this->request->data['court_surface_id'])){
77 $court_surface_id = $this->request->data['court_surface_id'];
78 }
79 else{
80 $court_surface_id = "";
81 }
82
83 $courtFreeSlots = $this->getCourtIdFromCourtSurfaceId($club_id, $book_start_datetime, $book_end_datetime, $booking_date, $day, $user_id, $duration,$court_surface_id, $clubTimezoneOffset);
84 $userFreeSlots = $this->getUserFreeSlots($user_id,$book_start_datetime,$book_end_datetime,$duration, $clubTimezoneOffset);
85 $courtFreeSlots = array_intersect($userFreeSlots,$courtFreeSlots);
86 if(isset($coach_id) && $coach_id!=""){
87 $this->loadModel('CoachAvailableHour');
88 if($booking_date == date('Y-m-d')){
89 $bookingTimestamp = (strtotime(date("Y-m-d H:i:s")) - ($clubTimezoneOffset*60));
90 }else{
91 $bookingTimestamp = (strtotime($booking_date) - ($clubTimezoneOffset*60));
92 }
93 $bookingDay = strtoupper(date('D', $bookingTimestamp + ($clubTimezoneOffset*60))); // DAY of the day should be on CLUB's timezone not on UTC
94 $coachHours = $this->CoachAvailableHour->find('all',array('conditions'=>array('CoachAvailableHour.club_id'=>$club_id,'CoachAvailableHour.coach_id'=>$coach_id,'CoachAvailableHour.sport_type_id'=>1,'CoachAvailableHour.day'=>$bookingDay,'CoachAvailableHour.available_in_app'=>1)));
95 if(count($coachHours)>1){
96 // 2 timestamps get the one with
97 }
98 elseif (count($coachHours)==1) {
99 $coachHours = $coachHours[0];
100 $coachCourtStartTime = $booking_date.' '.$coachHours['CoachAvailableHour']['start_time'];
101 $coachCourtEndTime = $booking_date.' '.$coachHours['CoachAvailableHour']['end_time'];
102
103 $coachCourtStartTime = date('Y-m-d H:i:s',strtotime($booking_date." ".$coachHours['CoachAvailableHour']['start_time']) - ($clubTimezoneOffset*60));
104 $coachCourtEndTime = date('Y-m-d H:i:s',strtotime($booking_date." ".$coachHours['CoachAvailableHour']['end_time']) - ($clubTimezoneOffset*60));
105
106
107
108 $CoachSlots = $this->ClubBookingSession->query("CALL coachSlot('$coachCourtStartTime','$coachCourtEndTime','$duration','$club_id','$coach_id');");
109 $subCoachSlots = $this->ClubBookingSession->query("CALL subCoachSlot('$coachCourtStartTime','$coachCourtEndTime','$duration','$club_id','$coach_id');");
110
111 $coachFreeSlots=array();
112 $subCoachFreeSlots=array();
113 /* foreach($CoachSlots as $CoachSlotsKey=>$CoachSlot){
114 $coachFreeSlots[$CoachSlot['CS']['start_date_time'].'_'.$CoachSlot['CS']['end_date_time']]=$CoachSlot['CS']['start_date_time'].'_'.$CoachSlot['CS']['end_date_time'];
115 } */
116 foreach($CoachSlots as $freeslotKey=>$freeslot){
117 $temp_start_time = date('Y-m-d H:i:s',strtotime($freeslot['CS']['start_date_time'])+$clubTimezoneOffset*60);
118
119 $temp_end_time = date("Y-m-d H:i:s",strtotime($freeslot['CS']['end_date_time']) +($clubTimezoneOffset*60));
120 $coachFreeSlots[$temp_start_time.'_'.$temp_end_time]=$temp_start_time.'_'.$temp_end_time;
121 }
122 foreach($subCoachSlots as $freeslotKey=>$freeslot){
123 $temp_start_time = date('Y-m-d H:i:s',strtotime($freeslot['CS']['start_date_time'])+$clubTimezoneOffset*60);
124
125 $temp_end_time = date("Y-m-d H:i:s",strtotime($freeslot['CS']['end_date_time']) +($clubTimezoneOffset*60));
126 $subCoachFreeSlots[$temp_start_time.'_'.$temp_end_time]=$temp_start_time.'_'.$temp_end_time;
127 }
128 /* foreach($subCoachSlots as $CoachSlotsKey=>$CoachSlot){
129 $subCoachFreeSlots[$CoachSlot['CS']['start_date_time'].'_'.$CoachSlot['CS']['end_date_time']]=$CoachSlot['CS']['start_date_time'].'_'.$CoachSlot['CS']['end_date_time'];
130 } */
131 $total_free_slots=array_intersect($courtFreeSlots, $coachFreeSlots, $subCoachFreeSlots);
132
133 foreach ($total_free_slots as $key => $total_free_slot) {
134 $ct = date("Y-m-d H:i:s");
135
136 $slot_type = explode("_", $total_free_slot);
137
138 $start_time_array = explode(" ", $slot_type[0]);
139 $start_time_slot = $start_time_array[1];
140
141 $end_time_array = explode(" ", $slot_type[1]);
142 $end_time_slot = $end_time_array[1];
143
144
145 $temp = array();
146 if(($booking_date." ".$start_time_slot > date("Y-m-d H:i:s",strtotime($ct) + ($clubTimezoneOffset*60)))){
147 // $temp['startTime'] = date("H:i", strtotime($start_time_slot));
148 // $temp['endTime'] = date("H:i", strtotime($end_time_slot));
149 $temp['startTime'] = date("H:i",strtotime($start_time_slot));
150 $temp['endTime'] = date("H:i",strtotime($end_time_slot));
151 }
152 $club_slots[]=$temp;
153 }
154
155 }else {
156 $response['status'] = true;
157 $response['data']['club_start_time'] = $clubSetting['Club']['default_start_time'];
158 $response['data']['club_end_time'] = $clubSetting['Club']['default_end_time'];
159 $response['data']['time_slot_gap'] = $clubSetting['Setting']['time_slot_gap'];
160 $response['data']['available_hours'] = [];
161 echo json_encode($response);
162 die;
163 }
164 }
165 else{
166 foreach ($courtFreeSlots as $key => $courtFreeSlot) {
167 $ct = date("Y-m-d H:i:s");
168 $slot_type = explode("_", $courtFreeSlot);
169 $start_time_array = explode(" ", $slot_type[0]);
170 $start_time_slot = $start_time_array[1];
171
172 $end_time_array = explode(" ", $slot_type[1]);
173 $end_time_slot = $end_time_array[1];
174
175 $temp = array();
176 if(($booking_date." ".$start_time_slot >= date("Y-m-d H:i:s",strtotime($ct) + ($clubTimezoneOffset*60)))){
177 $temp['startTime'] = date("H:i", strtotime($start_time_slot));
178 $temp['endTime'] = date("H:i", strtotime($end_time_slot));
179 }
180
181 $club_slots[]=$temp;
182 }
183 }
184
185 $response['status'] = true;
186 $response['data']['club_start_time'] = $clubSetting['Club']['default_start_time'];
187 $response['data']['club_end_time'] = $clubSetting['Club']['default_end_time'];
188 $response['data']['time_slot_gap'] = $clubSetting['Setting']['time_slot_gap'];
189 $response['data']['available_hours'] = $club_slots;
190 } else {
191 $response['status'] = false;
192 $response['message'] = 'Invalid request';
193 }
194 echo json_encode($response);
195 }
196
197 /**
198 * gets user free slots
199 * start date time and end date times are in UTC
200 * calls the stored procudres and find the unique free slots in the same duration interval as specified in club settings
201 */
202 private function getUserFreeSlots($userId,$startDateTime,$endDateTime,$duration, $clubTimezoneOffset){
203 //get player details
204 $player = $this->User->findById($userId);
205 $playerId = $player['Player']['id'];
206 // convert the start date time and end date time to utc
207 $startDateTimeUTC = date('Y-m-d H:i:s',strtotime($startDateTime) - ($clubTimezoneOffset * 60));
208 $endDateTimeUTC = date('Y-m-d H:i:s',strtotime($endDateTime) - ($clubTimezoneOffset * 60));
209 // call the stored procedure
210 // the procedure accepts both userID and playerId in order to shortent the query and complexity of the StroedProd
211 $freeSlots = $this->ClubBookingSession->query("CALL getFreeSlotsForPlayer('$startDateTimeUTC','$endDateTimeUTC','$duration','$playerId','$userId');");
212 $freePlayerSlots = array();
213 foreach($freeSlots as $freeSlotsKey=>$freeSlot){
214 // convert the timeslots to local club timezone
215 $user_start_datetime = date('Y-m-d H:i:s',strtotime($freeSlot['S']['start_date_time']) + ($clubTimezoneOffset *60));
216 $user_end_datetime = date('Y-m-d H:i:s',strtotime($freeSlot['S']['end_date_time']) + ($clubTimezoneOffset * 60));
217
218
219 // $user_start_datetime = $freeSlot['S']['start_date_time'];
220 // $user_end_datetime = $freeSlot['S']['end_date_time'];
221 $freePlayerSlots[$user_start_datetime.'_'.$user_end_datetime]=$user_start_datetime.'_'.$user_end_datetime;
222 }
223 return $freePlayerSlots;
224 }
225
226
227 /**
228 * gets court free slots
229 * start date time and end date times are in UTC
230 * calls the stored procudres and find the unique free slots in the same duration interval as specified in club settings
231 */
232 private function getCourtIdFromCourtSurfaceId($club_id, $book_start_datetime, $book_end_datetime, $booking_date, $day, $user_id, $duration=30, $court_surface_id=0, $clubTimezoneOffset){
233 $return_array = array();
234 if($court_surface_id!=0){
235 //$allAvailableCourtsWithSpecifiedSurfaces = $this->Court->findAllByClubIdAndCourtSurfaceIdAndAvailableInAppAndStatus($club_id, $court_surface_id,1,1);
236 $allAvailableCourtsWithSpecifiedSurfaces = $this->Court->find('all', array(
237 'conditions' => array (
238 'Court.club_id' => $club_id,
239 'Court.court_surface_id' => $court_surface_id,
240 'Court.available_in_app' => 1,
241 'Court.status' => 1
242 ),
243 'fields' => array (
244 'Court.id'
245 )
246 ));
247 $courtFreeSlots=array();
248 if(!empty($allAvailableCourtsWithSpecifiedSurfaces)){
249 foreach( $allAvailableCourtsWithSpecifiedSurfaces as $freeKey => $allAvailableCourtsWithSpecifiedSurface){
250 $court_id = $allAvailableCourtsWithSpecifiedSurface['Court']['id'];
251
252 $this->loadModel('CourtAvailableHour');
253 $availableCourts = $this->CourtAvailableHour->find('first',
254 array(
255 'conditions' =>
256 array(
257 'CourtAvailableHour.court_id' => $court_id,
258 'CourtAvailableHour.club_id' => $club_id,
259 'CourtAvailableHour.day' => $day,
260 ),
261 'fields' =>
262 array(
263 'CourtAvailableHour.start_time',
264 'CourtAvailableHour.end_time'
265 )
266 )
267 );
268 if(!empty($availableCourts)){
269 $book_start_datetime = date('Y-m-d H:i:s',strtotime($booking_date." ".$availableCourts['CourtAvailableHour']['start_time']) - ($clubTimezoneOffset*60));
270 $book_end_datetime = date('Y-m-d H:i:s',strtotime($booking_date." ".$availableCourts['CourtAvailableHour']['end_time']) - ($clubTimezoneOffset*60));
271
272 $freeslots = $this->ClubBookingSession->query("CALL slots('$book_start_datetime','$book_end_datetime','$duration','$court_id');");
273 foreach($freeslots as $freeslotKey=>$freeslot){
274 $temp_start_time = date('Y-m-d H:i:s',strtotime($freeslot['S']['start_date_time'])+$clubTimezoneOffset*60);
275
276 $temp_end_time = date("Y-m-d H:i:s",strtotime($freeslot['S']['end_date_time']) +($clubTimezoneOffset*60));
277 $courtFreeSlots[$temp_start_time.'_'.$temp_end_time]=$temp_start_time.'_'.$temp_end_time;
278 }
279 }
280 }
281 }
282 $return_array = $courtFreeSlots;
283 }
284 else{
285 //$allAvailableCourtsWithSpecifiedSurfaces = $this->Court->findAllByClubIdAndAvailableInAppAndStatus($club_id, 1,1);
286 $allAvailableCourtsWithSpecifiedSurfaces = $this->Court->find('all', array(
287 'conditions' => array (
288 'Court.club_id' => $club_id,
289 'Court.available_in_app' => 1,
290 'Court.status' => 1
291 ),
292 'fields' => array (
293 'Court.id'
294 )
295 ));
296 $courtFreeSlots=array();
297 if(!empty($allAvailableCourtsWithSpecifiedSurfaces)){
298 foreach($allAvailableCourtsWithSpecifiedSurfaces as $freeKey => $allAvailableCourtsWithSpecifiedSurface){
299 $court_id = $allAvailableCourtsWithSpecifiedSurface['Court']['id'];
300
301 $this->loadModel('CourtAvailableHour');
302
303 // get free timeslot for each court
304 $availableCourts = $this->CourtAvailableHour->find('first',
305
306 array(
307 'conditions' =>
308 array(
309 'CourtAvailableHour.court_id' => $court_id,
310 'CourtAvailableHour.club_id' => $club_id,
311 'CourtAvailableHour.day' => $day,
312 ),
313 'fields' =>
314 array(
315 'CourtAvailableHour.start_time',
316 'CourtAvailableHour.end_time'
317 )
318
319 )
320 );
321 if(!empty($availableCourts)){
322 $book_start_datetime = $booking_date." ".$availableCourts['CourtAvailableHour']['start_time'];
323 $book_end_datetime = $booking_date." ".$availableCourts['CourtAvailableHour']['end_time'];
324
325 $book_start_datetime = date('Y-m-d H:i:s',strtotime($booking_date." ".$availableCourts['CourtAvailableHour']['start_time']) - ($clubTimezoneOffset*60));
326 $book_end_datetime = date('Y-m-d H:i:s',strtotime($booking_date." ".$availableCourts['CourtAvailableHour']['end_time']) - ($clubTimezoneOffset*60));
327
328 $freeslots = $this->ClubBookingSession->query("CALL slots('$book_start_datetime','$book_end_datetime','$duration','$court_id');");
329 foreach($freeslots as $freeslotKey=>$freeslot){
330 $temp_start_time = date('Y-m-d H:i:s',strtotime($freeslot['S']['start_date_time'])+$clubTimezoneOffset*60);
331 $temp_end_time = date("Y-m-d H:i:s",strtotime($freeslot['S']['end_date_time']) +($clubTimezoneOffset*60));
332 $courtFreeSlots[$temp_start_time.'_'.$temp_end_time]=$temp_start_time.'_'.$temp_end_time;
333 }
334 if($freeKey==0){
335 $return_array = $courtFreeSlots;
336 }
337 else{
338 $return_array=array_merge($return_array,$courtFreeSlots);
339 }
340
341 }
342 }
343 }
344
345 }
346 return $return_array;
347 }