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