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>';
}
}
