function gdlr_get_booking_room_query($data, $room_number) { global $wpdb, $hotel_option, $sitepress; $num_people = intval($data['gdlr-adult-number'][$room_number]) + intval($data['gdlr-children-number'][$room_number]); $hotel_option['preserve-booking-room'] = empty($hotel_option['preserve-booking-room']) ? 'paid' : $hotel_option['preserve-booking-room']; // collect the previously selected room $rooms = array(); $room_temp = array(); if (!empty($data['gdlr-room-id'])) { foreach ($data['gdlr-room-id'] as $selected_room_id) { if (!empty($sitepress)) { $trid = $sitepress->get_element_trid($selected_room_id, 'post_room'); $translations = $sitepress->get_element_translations($trid, 'post_room'); if (empty($translations)) { $room_temp[$selected_room_id] = empty($room_temp[$selected_room_id]) ? 1 : $room_temp[$selected_room_id] + 1; } else { foreach ($translations as $translation) { $room_temp[$translation->element_id] = empty($room_temp[$translation->element_id]) ? 1 : $room_temp[$translation->element_id] + 1; } } } else { $room_temp[$selected_room_id] = empty($room_temp[$selected_room_id]) ? 1 : $room_temp[$selected_room_id] + 1; } } } // select all room id where max people > selected people $sql = "SELECT DISTINCT wpostmeta.post_id FROM {$wpdb->postmeta} wpostmeta "; if (!empty($data['gdlr-hotel-branches'])) { $sql .= "LEFT JOIN {$wpdb->term_relationships} ON (wpostmeta.post_id = {$wpdb->term_relationships}.object_id) "; $sql .= "LEFT JOIN {$wpdb->term_taxonomy} ON ({$wpdb->term_relationships}.term_taxonomy_id = {$wpdb->term_taxonomy}.term_taxonomy_id) "; } $sql .= "WHERE wpostmeta.meta_key = 'gdlr_max_people' AND wpostmeta.meta_value >= {$num_people} "; if (!empty($data['gdlr-hotel-branches'])) { $sql .= "AND {$wpdb->term_taxonomy}.taxonomy = 'room_category' "; $sql .= "AND {$wpdb->term_taxonomy}.term_id = {$data['gdlr-hotel-branches']} "; } $sql .= "ORDER BY post_id DESC"; $room_query = $wpdb->get_results($sql, OBJECT); // get data with false value filled $all_date = gdlr_split_date($data['gdlr-check-in'], $data['gdlr-check-out']); // check if the date is blocked $blocked_date = ''; foreach ($all_date as $key => $val) { if (gdlr_is_ss($key, array('date' => $hotel_option['block-date']))) { $blocked_date .= empty($blocked_date) ? '' : ', '; $blocked_date .= $key; } } if (!empty($blocked_date)) { $ret = '<div class="gdlr-hotel-missing-room">'; $ret .= '<i class="fa fa-frown-o icon-frown"></i>'; $ret .= __('Sorry, our hotel is closed on these following dates :', 'gdlr-hotel'); $ret .= '<br><strong>' . $blocked_date . '</strong>'; $ret .= '</div>'; return $ret; } // check if each room is available foreach ($room_query as $room) { $avail_num = intval(get_post_meta($room->post_id, 'gdlr_room_amount', true)); if (!empty($room_temp[$room->post_id])) { $avail_num = $avail_num - $room_temp[$room->post_id]; } $sql = "SELECT COUNT(*) "; $sql .= "FROM {$wpdb->prefix}gdlr_hotel_booking, {$wpdb->prefix}gdlr_hotel_payment WHERE "; // for wpml where room_id = $room if (!empty($sitepress)) { $count = 0; $trid = $sitepress->get_element_trid($room->post_id, 'post_room'); $translations = $sitepress->get_element_translations($trid, 'post_room'); if (!empty($translations)) { $sql .= "("; foreach ($translations as $translation) { $count++; $sql .= $count > 1 ? 'OR ' : ''; $sql .= "{$wpdb->prefix}gdlr_hotel_booking.room_id = {$translation->element_id} "; } $sql .= ") AND "; } else { $sql .= "{$wpdb->prefix}gdlr_hotel_booking.room_id = {$room->post_id} AND "; } } else { $sql .= "{$wpdb->prefix}gdlr_hotel_booking.room_id = {$room->post_id} AND "; } // where payment_status = selected_status $sql .= "{$wpdb->prefix}gdlr_hotel_payment.id = {$wpdb->prefix}gdlr_hotel_booking.payment_id AND "; if ($hotel_option['preserve-booking-room'] == 'paid') { $sql .= "{$wpdb->prefix}gdlr_hotel_payment.payment_status = 'paid' AND "; } else { $sql .= "{$wpdb->prefix}gdlr_hotel_payment.payment_status != 'pending' AND "; } // where date within $room_free = true; foreach ($all_date as $key => $val) { $temp_sql = $sql . "(start_date <= '{$key}' AND end_date > '{$key}')"; if ($avail_num <= $wpdb->get_var($temp_sql)) { $room_free = false; } else { $all_date[$key] = true; } } if ($room_free) { $rooms[] = $room->post_id; } } // query available room to print out if (!empty($rooms)) { $paged = empty($_POST['paged']) ? 1 : $_POST['paged']; $args = array('post_type' => 'room', 'post__in' => $rooms, 'posts_per_page' => $hotel_option['booking-num-fetch'], 'paged' => $paged); if (!empty($data['gdlr-hotel-branches'])) { $args['tax_query'] = array(array('taxonomy' => 'room_category', 'field' => 'id', 'terms' => intval($data['gdlr-hotel-branches']))); } $query = new WP_Query($args); return gdlr_get_booking_room($query, array('check-in' => $data['gdlr-check-in'], 'check-out' => $data['gdlr-check-out'], 'adult' => $data['gdlr-adult-number'][$room_number], 'children' => $data['gdlr-children-number'][$room_number])) . gdlr_get_ajax_pagination($query->max_num_pages, $paged); // room not available } else { $no_room_text = ''; foreach ($all_date as $key => $val) { $no_room_text .= empty($no_room_text) ? '' : ', '; $no_room_text .= !$val ? $key : ''; } $ret = '<div class="gdlr-hotel-missing-room">'; $ret .= '<i class="fa fa-frown-o icon-frown"></i>'; if (!empty($no_room_text)) { $ret .= __('Sorry, there\'re no room available in these following dates :', 'gdlr-hotel'); $ret .= '<br><strong>' . $no_room_text . '</strong>'; } else { $ret .= __('Sorry, there\'re no room available within selected dates.', 'gdlr-hotel'); } $ret .= '</div>'; return $ret; } }
function gdlr_get_booking_room_query($data, $room_number) { global $wpdb, $hotel_option; $num_people = intval($data['gdlr-adult-number'][$room_number]) + intval($data['gdlr-children-number'][$room_number]); $hotel_option['preserve-booking-room'] = empty($hotel_option['preserve-booking-room']) ? 'paid' : $hotel_option['preserve-booking-room']; $sql = "SELECT DISTINCT post_id FROM {$wpdb->postmeta} "; $sql .= "WHERE meta_key = 'gdlr_max_people' AND meta_value >= {$num_people} "; $sql .= "ORDER BY post_id DESC"; $rooms = array(); $room_query = $wpdb->get_results($sql, OBJECT); foreach ($room_query as $room) { $avail_num = intval(get_post_meta($room->post_id, 'gdlr_room_amount', true)); $sql = "SELECT COUNT(*) "; $sql .= "FROM {$wpdb->prefix}gdlr_hotel_booking, {$wpdb->prefix}gdlr_hotel_payment WHERE "; $sql .= "{$wpdb->prefix}gdlr_hotel_booking.room_id = {$room->post_id} AND "; $sql .= "{$wpdb->prefix}gdlr_hotel_payment.id = {$wpdb->prefix}gdlr_hotel_booking.payment_id AND "; if ($hotel_option['preserve-booking-room'] == 'paid') { $sql .= "{$wpdb->prefix}gdlr_hotel_payment.payment_status = 'paid' AND "; } else { $sql .= "{$wpdb->prefix}gdlr_hotel_payment.payment_status != 'pending' AND "; } // $sql .= "((start_date >= '{$data['gdlr-check-in']}' AND start_date < '{$data['gdlr-check-out']}') OR "; // $sql .= "(end_date > '{$data['gdlr-check-in']}' AND end_date <= '{$data['gdlr-check-out']}'))"; $sql .= "(start_date < '{$data['gdlr-check-out']}' AND '{$data['gdlr-check-in']}' < end_date)"; if ($avail_num > $wpdb->get_var($sql)) { $rooms[] = $room->post_id; } } if (!empty($rooms)) { $paged = empty($_POST['paged']) ? 1 : $_POST['paged']; $args = array('post_type' => 'room', 'post__in' => $rooms, 'posts_per_page' => $hotel_option['booking-num-fetch'], 'paged' => $paged); if (!empty($data['gdlr-hotel-branches'])) { $args['tax_query'] = array(array('taxonomy' => 'room_category', 'field' => 'id', 'terms' => $data['gdlr-hotel-branches'])); } $query = new WP_Query($args); return gdlr_get_booking_room($query, array('check-in' => $data['gdlr-check-in'], 'check-out' => $data['gdlr-check-out'], 'adult' => $data['gdlr-adult-number'][$room_number], 'children' => $data['gdlr-children-number'][$room_number])) . gdlr_get_ajax_pagination($query->max_num_pages, $paged); } else { return '<div class="gdlr-hotel-missing-room">' . __('No room available', 'gdlr-hotel') . '</div>'; } }