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['hotel_id'])) {
$where .= " AND CT_Vacancies.hotel_id = '" . esc_sql(ct_hotel_org_id($_REQUEST['hotel_id'])) . "'";
}
if (!empty($_REQUEST['room_type_id'])) {
$where .= " AND CT_Vacancies.room_type_id = '" . esc_sql(ct_room_org_id($_REQUEST['room_type_id'])) . "'";
}
if (!empty($_REQUEST['date'])) {
$where .= " AND CT_Vacancies.date_from <= '" . esc_sql($_REQUEST['date']) . "' and CT_Vacancies.date_to > '" . $_REQUEST['date'] . "'";
}
if (!current_user_can('manage_options')) {
$where .= " AND hotel.post_author = '" . get_current_user_id() . "' ";
}
$sql = $wpdb->prepare('SELECT CT_Vacancies.* , hotel.ID as hotel_id, hotel.post_title as hotel_name, room_type.ID as room_type_id, room_type.post_title as room_type_name FROM %1$s as CT_Vacancies
INNER JOIN %2$s as hotel ON CT_Vacancies.hotel_id=hotel.ID
INNER JOIN %2$s as room_type ON CT_Vacancies.room_type_id=room_type.ID
WHERE ' . $where . ' ORDER BY %4$s %5$s
LIMIT %6$s, %7$s', CT_HOTEL_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 CT_Vacancies INNER JOIN %2$s as hotel ON CT_Vacancies.hotel_id=hotel.ID WHERE %3$s', CT_HOTEL_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)));
}
function ct_hotel_calc_room_price($hotel_id, $room_type_id, $from_date, $to_date, $rooms = 1, $adults = 1, $kids = 0)
{
global $wpdb;
$hotel_id = ct_hotel_org_id($hotel_id);
$room_type_id = ct_room_org_id($room_type_id);
//validation
$room_hotel_id = get_post_meta($room_type_id, '_room_hotel_id', true);
if ($room_hotel_id != $hotel_id) {
return esc_html__('Room Type Id is not matched.', 'citytours');
}
$max_adults = get_post_meta($room_type_id, '_room_max_adults', true);
if (empty($max_adults)) {
$max_adults = 0;
}
$max_kids = get_post_meta($room_type_id, '_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 esc_html__('Exceeds Max Guests.', 'citytours');
}
if (time() - 60 * 60 * 24 > ct_strtotime($from_date)) {
return esc_html__('Wrong Check In date. Please check again.', 'citytours');
}
$minimum_stay = get_post_meta($hotel_id, '_hotel_minimum_stay', true);
$minimum_stay = is_numeric($minimum_stay) ? $minimum_stay : 0;
if (!ct_strtotime($from_date) || !ct_strtotime($to_date) || ct_strtotime($from_date) >= ct_strtotime($to_date) || ct_strtotime($from_date . ' + ' . $minimum_stay . ' days') > ct_strtotime($to_date)) {
return esc_html__('Wrong Booking Date. Please check again.', 'citytours');
}
$from_date_obj = new DateTime('@' . ct_strtotime($from_date));
$to_date_obj = new DateTime('@' . ct_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;
$hotel_id = esc_sql($hotel_id);
$room_type_id = esc_sql($room_type_id);
$rooms = esc_sql($rooms);
$adults = esc_sql($adults);
$kids = esc_sql($kids);
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.price_per_child\r\n\t\t\t\t\tFROM (SELECT room_type_id, rooms, price_per_room, price_per_person, price_per_child\r\n\t\t\t\t\t\t\tFROM " . CT_HOTEL_VACANCIES_TABLE . " \r\n\t\t\t\t\t\t\tWHERE 1=1 AND hotel_id='" . $hotel_id . "' AND room_type_id = '" . $room_type_id . "' AND date_from <= '" . $check_date . "' AND date_to > '" . $check_date . "' ) AS vacancies\r\n\t\t\t\t\tLEFT JOIN (SELECT hotel_booking.room_type_id, SUM(hotel_booking.rooms) AS rooms \r\n\t\t\t\t\t\t\tFROM " . CT_HOTEL_BOOKINGS_TABLE . " AS hotel_booking \r\n\t\t\t\t\t\t\tINNER JOIN " . CT_ORDER_TABLE . " as hotel_order on hotel_order.id = hotel_booking.order_id\r\n\t\t\t\t\t\t\tWHERE 1=1 AND hotel_order.status!='cancelled' AND hotel_order.post_id='" . $hotel_id . "' AND hotel_booking.room_type_id = '" . $room_type_id . "' AND hotel_order.date_to > '" . $check_date . "' AND hotel_order.date_from <= '" . $check_date . "'" . (empty($except_booking_no) || empty($pin_code) ? "" : " AND NOT ( hotel_order.booking_no = '" . $except_booking_no . "' AND hotel_order.pin_code = '" . $pin_code . "' )") . "\r\n\t\t\t\t\t) AS bookings ON vacancies.room_type_id = bookings.room_type_id\r\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, price_per_child)
if (empty($result)) {
//if no available rooms on selected date
return esc_html__('Sorry, The room you are booking now is just taken by another customer. Please have another look.', 'citytours');
} else {
$price_per_room = (double) $result->price_per_room;
$price_per_person = (double) $result->price_per_person;
$price_per_child = (double) $result->price_per_child;
$day_price = $price_per_room * $rooms + $price_per_person * $adults + $price_per_child * $kids;
$price_data[$check_date] = array('ppr' => $price_per_room, 'ppp' => $price_per_person, 'ppc' => $price_per_child, 'total' => $day_price);
$total_price += $day_price;
}
}
$return_value = array('check_dates' => $check_dates, 'prices' => $price_data, 'total_price' => $total_price);
return $return_value;
}
