function trav_acc_get_room_price_data($acc_id, $room_type_id, $from_date, $to_date, $rooms = 1, $adults = 1, $kids = 0, $child_ages, $except_booking_no = 0, $pin_code = 0)
{
global $wpdb;
$acc_id = trav_acc_org_id($acc_id);
$room_type_id = trav_room_org_id($room_type_id);
//validation
if (!is_array($child_ages)) {
$child_ages = unserialize($child_ages);
}
$room_accommodation_id = get_post_meta($room_type_id, 'trav_room_accommodation', true);
if ($room_accommodation_id != $acc_id) {
return false;
}
$max_adults = get_post_meta($room_type_id, 'trav_room_max_adults', true);
if (empty($max_adults)) {
$max_adults = 0;
}
$max_kids = get_post_meta($room_type_id, 'trav_room_max_kids', true);
if (empty($max_adults)) {
$max_kids = 0;
}
$avg_adults = ceil($adults / $rooms);
$avg_kids = ceil($kids / $rooms);
if ($avg_adults > $max_adults || $avg_adults + $avg_kids > $max_adults + $max_kids) {
return false;
}
if (time() - 60 * 60 * 24 > trav_strtotime($from_date)) {
return false;
}
$minimum_stay = get_post_meta($acc_id, 'trav_accommodation_minimum_stay', true);
$minimum_stay = is_numeric($minimum_stay) ? $minimum_stay : 0;
if (!trav_strtotime($from_date) || !trav_strtotime($to_date) || trav_strtotime($from_date) >= trav_strtotime($to_date) || trav_strtotime($from_date . ' + ' . $minimum_stay . ' days') > trav_strtotime($to_date)) {
return false;
}
$from_date_obj = new DateTime('@' . trav_strtotime($from_date));
$to_date_obj = new DateTime('@' . trav_strtotime($to_date));
$date_interval = DateInterval::createFromDateString('1 day');
$period = new DatePeriod($from_date_obj, $date_interval, $to_date_obj);
$price_data = array();
$total_price = 0.0;
$acc_id = esc_sql($acc_id);
$room_type_id = esc_sql($room_type_id);
$rooms = esc_sql($rooms);
$adults = esc_sql($adults);
$kids = esc_sql($kids);
$child_ages = esc_sql($child_ages);
$except_booking_no = esc_sql($except_booking_no);
$pin_code = esc_sql($pin_code);
foreach ($period as $dt) {
$check_date = esc_sql($dt->format("Y-m-d"));
$check_dates[] = $check_date;
$sql = "SELECT vacancies.room_type_id, vacancies.price_per_room , vacancies.price_per_person, vacancies.child_price\n\t\t\t\t\tFROM (SELECT room_type_id, rooms, price_per_room, price_per_person, child_price\n\t\t\t\t\t\t\tFROM " . TRAV_ACCOMMODATION_VACANCIES_TABLE . " \n\t\t\t\t\t\t\tWHERE 1=1 AND accommodation_id='" . $acc_id . "' AND room_type_id = '" . $room_type_id . "' AND date_from <= '" . $check_date . "' AND date_to > '" . $check_date . "' ) AS vacancies\n\t\t\t\t\tLEFT JOIN (SELECT room_type_id, SUM(rooms) AS rooms \n\t\t\t\t\t\t\tFROM " . TRAV_ACCOMMODATION_BOOKINGS_TABLE . " \n\t\t\t\t\t\t\tWHERE 1=1 AND status!='0' AND accommodation_id='" . $acc_id . "' AND room_type_id = '" . $room_type_id . "' AND date_to > '" . $check_date . "' AND date_from <= '" . $check_date . "'" . (empty($except_booking_no) || empty($pin_code) ? "" : " AND NOT ( booking_no = '" . $except_booking_no . "' AND pin_code = '" . $pin_code . "' )") . "\n\t\t\t\t\t) AS bookings ON vacancies.room_type_id = bookings.room_type_id\n\t\t\t\t\tWHERE vacancies.rooms - IFNULL(bookings.rooms,0) >= " . $rooms . ";";
$result = $wpdb->get_row($sql);
// object (room_type_id, price_per_room, price_per_person, child_price)
if (empty($result)) {
//if no available rooms on selected date
return false;
} else {
$price_per_room = (double) $result->price_per_room;
$price_per_person = (double) $result->price_per_person;
$child_price_data = unserialize($result->child_price);
$child_price = array();
$total_child_price = 0;
if ($kids > 0 && !empty($child_price_data) && !empty($child_ages)) {
usort($child_price_data, function ($a, $b) {
return $a[0] - $b[0];
});
foreach ($child_ages as $child_age) {
$is_child = false;
foreach ($child_price_data as $age_price_pair) {
if (is_array($age_price_pair) && count($age_price_pair) >= 2 && (int) $child_age <= (int) $age_price_pair[0]) {
$is_child = true;
$child_price[] = (double) $age_price_pair[1];
$total_child_price += (double) $age_price_pair[1];
break;
}
}
//if child price for this age is not set, calculate as a adult
if (!$is_child) {
$child_price[] = $price_per_person;
$total_child_price += $price_per_person;
}
}
}
$day_price = $price_per_room * $rooms + $price_per_person * $adults + $total_child_price;
$price_data[$check_date] = array('ppr' => $price_per_room, 'ppp' => $price_per_person, 'cp' => $child_price, 'total' => $day_price);
$total_price += $day_price;
}
}
$return_value = array('check_dates' => $check_dates, 'prices' => $price_data, 'total_price' => $total_price);
return $return_value;
}
function trav_ajax_acc_change_room()
{
if (!isset($_POST['_wpnonce']) || !isset($_POST['booking_no']) || !wp_verify_nonce($_POST['_wpnonce'], 'booking-' . $_POST['booking_no'])) {
$result_json['success'] = 0;
$result_json['result'] = __('Sorry, your nonce did not verify.', 'trav');
wp_send_json($result_json);
}
global $wpdb;
$booking_no = sanitize_text_field($_POST['booking_no']);
$pin_code = sanitize_text_field($_POST['pin_code']);
if (!($booking_data = trav_acc_get_booking_data($booking_no, $pin_code))) {
$result_json['success'] = 0;
$result_json['result'] = __('Wrong booking number and pin code.', 'trav');
wp_send_json($result_json);
}
$booking_data['room_type_id'] = sanitize_text_field(trav_room_org_id($_POST['room_type_id']));
$room_price_data = trav_acc_get_room_price_data($booking_data['accommodation_id'], $booking_data['room_type_id'], $booking_data['date_from'], $booking_data['date_to'], $booking_data['rooms'], $booking_data['adults'], $booking_data['kids'], $booking_data['child_ages'], $booking_no, $pin_code);
if (!$room_price_data) {
$result_json['success'] = 0;
$result_json['result'] = __('The room is not available for the selected date, rooms and person. Please have another look at booking fields.', 'trav');
wp_send_json($result_json);
}
$tax_rate = get_post_meta($booking_data['accommodation_id'], 'trav_accommodation_tax_rate', true);
$tax = 0;
if (!empty($tax_rate)) {
$tax = $tax_rate * $room_price_data['total_price'] / 100;
}
$total_price_incl_tax = $room_price_data['total_price'] + $tax;
$booking_data['room_price'] = $room_price_data['total_price'];
$booking_data['tax'] = $tax;
$booking_data['total_price'] = $total_price_incl_tax;
/*if ( ! isset( $_SESSION['exchange_rate'] ) ) trav_init_currency();
$booking_data['currency_code'] = trav_get_user_currency();*/
$booking_data['updated'] = date('Y-m-d H:i:s');
$result = trav_acc_update_booking($booking_no, $pin_code, $booking_data, 'update');
if (false === $result) {
$result_json['success'] = 0;
$result_json['result'] = __('Sorry, some error occurred on update.', 'trav');
} else {
$result_json['success'] = 1;
$result_json['result'] = __('Your booking is updated successfully.', 'trav');
}
wp_send_json($result_json);
}
function prepare_items()
{
global $wpdb;
$per_page = 10;
$columns = $this->get_columns();
$hidden = array();
$sortable = $this->get_sortable_columns();
$this->_column_headers = array($columns, $hidden, $sortable);
$this->process_bulk_action();
$orderby = !empty($_REQUEST['orderby']) ? sanitize_sql_orderby($_REQUEST['orderby']) : 'id';
//If no sort, default to title
$order = !empty($_REQUEST['order']) ? sanitize_text_field($_REQUEST['order']) : 'desc';
//If no order, default to desc
$current_page = $this->get_pagenum();
$post_table_name = $wpdb->prefix . 'posts';
$where = "1=1";
if (!empty($_REQUEST['accommodation_id'])) {
$where .= " AND Trav_Vacancies.accommodation_id = '" . esc_sql(trav_acc_org_id($_REQUEST['accommodation_id'])) . "'";
}
if (!empty($_REQUEST['room_type_id'])) {
$where .= " AND Trav_Vacancies.room_type_id = '" . esc_sql(trav_room_org_id($_REQUEST['room_type_id'])) . "'";
}
if (!empty($_REQUEST['date'])) {
$where .= " AND Trav_Vacancies.date_from <= '" . esc_sql($_REQUEST['date']) . "' and Trav_Vacancies.date_to > '" . $_REQUEST['date'] . "'";
}
if (!current_user_can('manage_options')) {
$where .= " AND accommodation.post_author = '" . get_current_user_id() . "' ";
}
$sql = $wpdb->prepare('SELECT Trav_Vacancies.* , accommodation.ID as acc_id, accommodation.post_title as accommodation_name, room_type.ID as room_type_id, room_type.post_title as room_type_name FROM %1$s as Trav_Vacancies
INNER JOIN %2$s as accommodation ON Trav_Vacancies.accommodation_id=accommodation.ID
INNER JOIN %2$s as room_type ON Trav_Vacancies.room_type_id=room_type.ID
WHERE ' . $where . ' ORDER BY %4$s %5$s
LIMIT %6$s, %7$s', TRAV_ACCOMMODATION_VACANCIES_TABLE, $post_table_name, '', $orderby, $order, $per_page * ($current_page - 1), $per_page);
$data = $wpdb->get_results($sql, ARRAY_A);
$sql = sprintf('SELECT COUNT(*) FROM %1$s as Trav_Vacancies INNER JOIN %2$s as accommodation ON Trav_Vacancies.accommodation_id=accommodation.ID WHERE %3$s', TRAV_ACCOMMODATION_VACANCIES_TABLE, $post_table_name, $where);
$total_items = $wpdb->get_var($sql);
$this->items = $data;
$this->set_pagination_args(array('total_items' => $total_items, 'per_page' => $per_page, 'total_pages' => ceil($total_items / $per_page)));
}
