timeslots

 /**
     * getFreeTimeSlots this function will return free timeslots based on clubId and date, coach_id and court id
     * This api is getting called only from Mobile App
     */
     public function getFreeTimeSlots(){
        if ($this->request->is('post')) {
            $this->loadModel('ClubBookingSession');
            $this->loadModel('Setting');
            $this->loadModel('User');
            $this->loadModel('Club');
            $this->loadModel('Court');

            $club_slots = array();
            $club_id            = $this->request->data['club_id'];
            $booking_date       = $this->request->data['booking_date'];
            $user_id            = $this->request->data['user_id'];





            if(isset($this->request->data['coach_id'])){
                $coach_id           = $this->request->data['coach_id'];
            }
            else{
                $coach_id           = "";
            }

            //$clubSetting        = $this->Setting->findByClubId($club_id);
            $clubSetting        = $this->Setting->find('first', array (
                'conditions' => array (
                    'Setting.club_id' => $club_id
                ),
                'fields' => array (
                    'Setting.time_slot_gap',
                    'Club.default_start_time',
                    'Club.default_end_time',
                    'Club.timezone_id'
                )
            ));
           // $clubDetails        = $this->User->Club->findById($club_id);
            // $clubDetails        = $this->User->Club->find('first', array (
            //     'conditions' => array (
            //         'Club.id' => $club_id
            //     ),
            //     'fields' => array (
            //         'Timezone.id',
            //         'Club.default_start_time',
            //         'Club.default_end_time',
            //     )
            // ));
            $clubTimezoneOffset = $this->Common->getClubTimezoneOffset($clubSetting['Club']['timezone_id']);
            $courtCounter       = 0;
            $day                = strtoupper(date('D',strtotime($booking_date)));

            if(empty($clubSetting)){
                $response['status'] = false;
                $response['message'] = 'Club not found.';
                echo json_encode($response);
                die;
            }

            if(empty($clubSetting)){
                $response['status'] = false;
                $response['message'] = 'Club setting not found.';
                echo json_encode($response);
                die;
            }

            //$userBuysSlots = $this->checkPlayerAvailabilityFromFreeslots($user_id ,$booking_date, $clubTimezoneOffset, true);

            $duration               = $clubSetting['Setting']['time_slot_gap']; // in minutes
            $book_start_datetime    = $booking_date.' '.$clubSetting['Club']['default_start_time'];
            $book_end_datetime      = $booking_date.' '.$clubSetting['Club']['default_end_time'];

            if(isset($this->request->data['court_surface_id'])){
                $court_surface_id = $this->request->data['court_surface_id'];
            }
            else{
                $court_surface_id = "";
            }

            $courtFreeSlots = $this->getCourtIdFromCourtSurfaceId($club_id, $book_start_datetime, $book_end_datetime, $booking_date, $day,  $user_id, $duration,$court_surface_id, $clubTimezoneOffset);
            $userFreeSlots = $this->getUserFreeSlots($user_id,$book_start_datetime,$book_end_datetime,$duration, $clubTimezoneOffset);
            $courtFreeSlots = array_intersect($userFreeSlots,$courtFreeSlots);
            if(isset($coach_id) && $coach_id!=""){
                $this->loadModel('CoachAvailableHour');
                if($booking_date == date('Y-m-d')){
                    $bookingTimestamp = (strtotime(date("Y-m-d H:i:s")) - ($clubTimezoneOffset*60));
                }else{
                    $bookingTimestamp = (strtotime($booking_date) - ($clubTimezoneOffset*60));
                }
                $bookingDay = strtoupper(date('D', $bookingTimestamp + ($clubTimezoneOffset*60))); // DAY of the day should be on CLUB's timezone not on UTC
                $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)));
                if(count($coachHours)>1){
                    // 2 timestamps get the one with
                }
                elseif (count($coachHours)==1) {
                    $coachHours = $coachHours[0];
                    $coachCourtStartTime = $booking_date.' '.$coachHours['CoachAvailableHour']['start_time'];
                    $coachCourtEndTime = $booking_date.' '.$coachHours['CoachAvailableHour']['end_time'];

                    $coachCourtStartTime = date('Y-m-d H:i:s',strtotime($booking_date." ".$coachHours['CoachAvailableHour']['start_time']) - ($clubTimezoneOffset*60));
                    $coachCourtEndTime = date('Y-m-d H:i:s',strtotime($booking_date." ".$coachHours['CoachAvailableHour']['end_time']) - ($clubTimezoneOffset*60));



                    $CoachSlots = $this->ClubBookingSession->query("CALL coachSlot('$coachCourtStartTime','$coachCourtEndTime','$duration','$club_id','$coach_id');");
                    $subCoachSlots = $this->ClubBookingSession->query("CALL subCoachSlot('$coachCourtStartTime','$coachCourtEndTime','$duration','$club_id','$coach_id');");

                    $coachFreeSlots=array();
                    $subCoachFreeSlots=array();
                    /* foreach($CoachSlots as $CoachSlotsKey=>$CoachSlot){
                        $coachFreeSlots[$CoachSlot['CS']['start_date_time'].'_'.$CoachSlot['CS']['end_date_time']]=$CoachSlot['CS']['start_date_time'].'_'.$CoachSlot['CS']['end_date_time'];
                    } */
                    foreach($CoachSlots as $freeslotKey=>$freeslot){
                        $temp_start_time = date('Y-m-d H:i:s',strtotime($freeslot['CS']['start_date_time'])+$clubTimezoneOffset*60);

                        $temp_end_time = date("Y-m-d H:i:s",strtotime($freeslot['CS']['end_date_time']) +($clubTimezoneOffset*60));
                            $coachFreeSlots[$temp_start_time.'_'.$temp_end_time]=$temp_start_time.'_'.$temp_end_time;
                    }
                    foreach($subCoachSlots as $freeslotKey=>$freeslot){
                        $temp_start_time = date('Y-m-d H:i:s',strtotime($freeslot['CS']['start_date_time'])+$clubTimezoneOffset*60);

                        $temp_end_time = date("Y-m-d H:i:s",strtotime($freeslot['CS']['end_date_time']) +($clubTimezoneOffset*60));
                            $subCoachFreeSlots[$temp_start_time.'_'.$temp_end_time]=$temp_start_time.'_'.$temp_end_time;
                    }
                    /* foreach($subCoachSlots as $CoachSlotsKey=>$CoachSlot){
                        $subCoachFreeSlots[$CoachSlot['CS']['start_date_time'].'_'.$CoachSlot['CS']['end_date_time']]=$CoachSlot['CS']['start_date_time'].'_'.$CoachSlot['CS']['end_date_time'];
                    } */
                    $total_free_slots=array_intersect($courtFreeSlots, $coachFreeSlots, $subCoachFreeSlots);

                    foreach ($total_free_slots as $key => $total_free_slot) {
                            $ct = date("Y-m-d H:i:s");

                            $slot_type = explode("_", $total_free_slot);

                            $start_time_array = explode(" ", $slot_type[0]);
                            $start_time_slot = $start_time_array[1];

                            $end_time_array = explode(" ", $slot_type[1]);
                            $end_time_slot = $end_time_array[1];


                            $temp = array();
                            if(($booking_date." ".$start_time_slot > date("Y-m-d H:i:s",strtotime($ct) + ($clubTimezoneOffset*60)))){
                                // $temp['startTime'] = date("H:i", strtotime($start_time_slot));
                                // $temp['endTime'] = date("H:i", strtotime($end_time_slot));
                                $temp['startTime'] = date("H:i",strtotime($start_time_slot));
                                $temp['endTime'] = date("H:i",strtotime($end_time_slot));
                            }
                            $club_slots[]=$temp;
                    }

                }else {
                    $response['status'] = true;
                    $response['data']['club_start_time'] = $clubSetting['Club']['default_start_time'];
                    $response['data']['club_end_time'] = $clubSetting['Club']['default_end_time'];
                    $response['data']['time_slot_gap'] = $clubSetting['Setting']['time_slot_gap'];
                    $response['data']['available_hours'] = [];
                    echo json_encode($response);
                    die;
                }
            }
            else{
                foreach ($courtFreeSlots as $key => $courtFreeSlot) {
                    $ct = date("Y-m-d H:i:s");
                    $slot_type = explode("_", $courtFreeSlot);
                    $start_time_array = explode(" ", $slot_type[0]);
                    $start_time_slot = $start_time_array[1];

                    $end_time_array = explode(" ", $slot_type[1]);
                    $end_time_slot = $end_time_array[1];

                    $temp = array();
                    if(($booking_date." ".$start_time_slot >= date("Y-m-d H:i:s",strtotime($ct) + ($clubTimezoneOffset*60)))){
                        $temp['startTime'] = date("H:i", strtotime($start_time_slot));
                        $temp['endTime'] = date("H:i", strtotime($end_time_slot));
                    }

                    $club_slots[]=$temp;
                }
            }

            $response['status'] = true;
            $response['data']['club_start_time'] = $clubSetting['Club']['default_start_time'];
            $response['data']['club_end_time'] = $clubSetting['Club']['default_end_time'];
            $response['data']['time_slot_gap'] = $clubSetting['Setting']['time_slot_gap'];
            $response['data']['available_hours'] = $club_slots;
        } else {
            $response['status'] = false;
            $response['message'] = 'Invalid request';
        }
        echo json_encode($response);
    }

    /**
     * gets user free slots
     * start date time and end date times are in UTC
     * calls the stored procudres and find the unique free slots in the same duration interval as specified in club settings
     */
    private function getUserFreeSlots($userId,$startDateTime,$endDateTime,$duration, $clubTimezoneOffset){
        //get player details
        $player = $this->User->findById($userId);
        $playerId = $player['Player']['id'];
        // convert the start date time and end date time to utc
        $startDateTimeUTC = date('Y-m-d H:i:s',strtotime($startDateTime) - ($clubTimezoneOffset * 60));
        $endDateTimeUTC = date('Y-m-d H:i:s',strtotime($endDateTime) - ($clubTimezoneOffset * 60));
        // call the stored procedure
        // the procedure accepts both userID and playerId in order to shortent the query and complexity of the StroedProd
        $freeSlots = $this->ClubBookingSession->query("CALL getFreeSlotsForPlayer('$startDateTimeUTC','$endDateTimeUTC','$duration','$playerId','$userId');");
        $freePlayerSlots = array();
        foreach($freeSlots as $freeSlotsKey=>$freeSlot){
            // convert the timeslots to local club timezone
            $user_start_datetime = date('Y-m-d H:i:s',strtotime($freeSlot['S']['start_date_time']) + ($clubTimezoneOffset *60));
            $user_end_datetime = date('Y-m-d H:i:s',strtotime($freeSlot['S']['end_date_time']) + ($clubTimezoneOffset * 60));


            // $user_start_datetime = $freeSlot['S']['start_date_time'];
            // $user_end_datetime = $freeSlot['S']['end_date_time'];
            $freePlayerSlots[$user_start_datetime.'_'.$user_end_datetime]=$user_start_datetime.'_'.$user_end_datetime;
        }
        return $freePlayerSlots;
    }


    /**
     * gets court free slots
     * start date time and end date times are in UTC
     * calls the stored procudres and find the unique free slots in the same duration interval as specified in club settings
     */
    private function getCourtIdFromCourtSurfaceId($club_id, $book_start_datetime, $book_end_datetime, $booking_date, $day,  $user_id, $duration=30, $court_surface_id=0, $clubTimezoneOffset){
        $return_array = array();
        if($court_surface_id!=0){
            //$allAvailableCourtsWithSpecifiedSurfaces = $this->Court->findAllByClubIdAndCourtSurfaceIdAndAvailableInAppAndStatus($club_id, $court_surface_id,1,1);
            $allAvailableCourtsWithSpecifiedSurfaces = $this->Court->find('all', array(
                'conditions' => array (
                    'Court.club_id' => $club_id,
                    'Court.court_surface_id' => $court_surface_id,
                    'Court.available_in_app' => 1,
                    'Court.status' => 1
                ),
                'fields' => array (
                    'Court.id'
                )
            ));
            $courtFreeSlots=array();
            if(!empty($allAvailableCourtsWithSpecifiedSurfaces)){
                foreach( $allAvailableCourtsWithSpecifiedSurfaces as $freeKey => $allAvailableCourtsWithSpecifiedSurface){
                    $court_id           = $allAvailableCourtsWithSpecifiedSurface['Court']['id'];

                    $this->loadModel('CourtAvailableHour');
                    $availableCourts = $this->CourtAvailableHour->find('first',
                        array(
                            'conditions' =>
                            array(
                                'CourtAvailableHour.court_id' => $court_id,
                                'CourtAvailableHour.club_id' => $club_id,
                                'CourtAvailableHour.day' => $day,
                            ),
                            'fields' =>
                            array(
                                'CourtAvailableHour.start_time',
                                'CourtAvailableHour.end_time'
                            )
                        )
                    );
                    if(!empty($availableCourts)){
                        $book_start_datetime = date('Y-m-d H:i:s',strtotime($booking_date." ".$availableCourts['CourtAvailableHour']['start_time']) - ($clubTimezoneOffset*60));
                        $book_end_datetime = date('Y-m-d H:i:s',strtotime($booking_date." ".$availableCourts['CourtAvailableHour']['end_time']) - ($clubTimezoneOffset*60));

                        $freeslots = $this->ClubBookingSession->query("CALL slots('$book_start_datetime','$book_end_datetime','$duration','$court_id');");
                        foreach($freeslots as $freeslotKey=>$freeslot){
                            $temp_start_time = date('Y-m-d H:i:s',strtotime($freeslot['S']['start_date_time'])+$clubTimezoneOffset*60);

                            $temp_end_time = date("Y-m-d H:i:s",strtotime($freeslot['S']['end_date_time']) +($clubTimezoneOffset*60));
                                $courtFreeSlots[$temp_start_time.'_'.$temp_end_time]=$temp_start_time.'_'.$temp_end_time;
                        }
                    }
                }
            }
            $return_array = $courtFreeSlots;
        }
        else{
            //$allAvailableCourtsWithSpecifiedSurfaces = $this->Court->findAllByClubIdAndAvailableInAppAndStatus($club_id, 1,1);
            $allAvailableCourtsWithSpecifiedSurfaces = $this->Court->find('all', array(
                'conditions' => array (
                    'Court.club_id' => $club_id,
                    'Court.available_in_app' => 1,
                    'Court.status' => 1
                ),
                'fields' => array (
                    'Court.id'
                )
            ));
            $courtFreeSlots=array();
            if(!empty($allAvailableCourtsWithSpecifiedSurfaces)){
                foreach($allAvailableCourtsWithSpecifiedSurfaces as $freeKey => $allAvailableCourtsWithSpecifiedSurface){
                    $court_id = $allAvailableCourtsWithSpecifiedSurface['Court']['id'];

                    $this->loadModel('CourtAvailableHour');

                    // get free timeslot for each court
                    $availableCourts = $this->CourtAvailableHour->find('first',

                        array(
                            'conditions' =>
                            array(
                                'CourtAvailableHour.court_id' => $court_id,
                                'CourtAvailableHour.club_id' => $club_id,
                                'CourtAvailableHour.day' => $day,
                            ),
                            'fields' =>
                            array(
                                'CourtAvailableHour.start_time',
                                'CourtAvailableHour.end_time'
                            )

                        )
                    );
                    if(!empty($availableCourts)){
                        $book_start_datetime = $booking_date." ".$availableCourts['CourtAvailableHour']['start_time'];
                        $book_end_datetime = $booking_date." ".$availableCourts['CourtAvailableHour']['end_time'];

                        $book_start_datetime = date('Y-m-d H:i:s',strtotime($booking_date." ".$availableCourts['CourtAvailableHour']['start_time']) - ($clubTimezoneOffset*60));
                        $book_end_datetime = date('Y-m-d H:i:s',strtotime($booking_date." ".$availableCourts['CourtAvailableHour']['end_time']) - ($clubTimezoneOffset*60));

                        $freeslots = $this->ClubBookingSession->query("CALL slots('$book_start_datetime','$book_end_datetime','$duration','$court_id');");
                        foreach($freeslots as $freeslotKey=>$freeslot){
                            $temp_start_time = date('Y-m-d H:i:s',strtotime($freeslot['S']['start_date_time'])+$clubTimezoneOffset*60);
                            $temp_end_time = date("Y-m-d H:i:s",strtotime($freeslot['S']['end_date_time']) +($clubTimezoneOffset*60));
                            $courtFreeSlots[$temp_start_time.'_'.$temp_end_time]=$temp_start_time.'_'.$temp_end_time;
                        }
                        if($freeKey==0){
                            $return_array = $courtFreeSlots;
                        }
                        else{
                            $return_array=array_merge($return_array,$courtFreeSlots);
                        }

                    }
                }
            }

        }
        return $return_array;
    }