function ct_hotel_get_search_result($args)
{
global $ct_options, $wpdb;
$s = '';
$date_from = '';
$date_to = '';
$rooms = 1;
$adults = 1;
$kids = 0;
$district = array();
$price_filter = array();
$star_filter = array();
$rating_filter = array();
$facility_filter = array();
$order_by = '';
$order = '';
$last_no = 0;
$per_page = isset($ct_options['hotel_posts']) && is_numeric($ct_options['hotel_posts']) ? $ct_options['hotel_posts'] : 6;
extract($args);
$order_array = array('ASC', 'DESC');
$order_by_array = array('' => '', 'price' => 'convert(meta_price.meta_value, decimal)', 'rating' => 'meta_rating.meta_value');
if (!array_key_exists($order_by, $order_by_array)) {
$order_by = '';
}
if (!in_array($order, $order_array)) {
$order = 'ASC';
}
$tbl_posts = esc_sql($wpdb->posts);
$tbl_postmeta = esc_sql($wpdb->postmeta);
$tbl_terms = esc_sql($wpdb->prefix . 'terms');
$tbl_term_taxonomy = esc_sql($wpdb->prefix . 'term_taxonomy');
$tbl_term_relationships = esc_sql($wpdb->prefix . 'term_relationships');
$tbl_icl_translations = esc_sql($wpdb->prefix . 'icl_translations');
$temp_tbl_name = esc_sql('Search_' . session_id());
$from_date_obj = date_create_from_format(ct_get_date_format('php'), $date_from);
$to_date_obj = date_create_from_format(ct_get_date_format('php'), $date_to);
$s_query = '';
// sql for search keyword
$c_query = '';
// sql for conditions ( review, avg_price, user_rating )
$v_query = '';
// sql for vacancy check
$s_query = "SELECT post_s1.ID AS hotel_id FROM {$tbl_posts} AS post_s1 \r\n\t\t\t\t\tWHERE (post_s1.post_status = 'publish') AND (post_s1.post_type = 'hotel')";
if (!empty($s)) {
//mysql escape sting and like escape
if (floatval(get_bloginfo('version')) >= 4.0) {
$s = esc_sql($wpdb->esc_like($s));
} else {
$s = esc_sql(like_escape($s));
}
$s_query .= " AND ((post_s1.post_title LIKE '%{$s}%') OR (post_s1.post_content LIKE '%{$s}%') )";
}
// if wpml is enabled do search by default language post
if (defined('ICL_LANGUAGE_CODE') && ct_get_lang_count() > 1 && ct_get_default_language() != ICL_LANGUAGE_CODE) {
$s_query = "SELECT DISTINCT it2.element_id AS hotel_id FROM ({$s_query}) AS t0\r\n\t\t\t\t\t\tINNER JOIN {$tbl_icl_translations} it1 ON (it1.element_type = 'post_hotel') AND it1.element_id = t0.hotel_id\r\n\t\t\t\t\t\tINNER JOIN {$tbl_icl_translations} it2 ON (it2.element_type = 'post_hotel') AND it2.language_code='" . ct_get_default_language() . "' AND it2.trid = it1.trid ";
}
$c_query = "SELECT t1.*, meta_c1.post_id AS room_id, meta_c2.meta_value AS max_adults, meta_c3.meta_value AS max_kids, meta_c4.meta_value AS minimum_stay\r\n\t\t\t\t\tFROM ( {$s_query} ) AS t1\r\n\t\t\t\t\tINNER JOIN {$tbl_postmeta} AS meta_c1 ON (meta_c1.meta_key = '_room_hotel_id') AND (t1.hotel_id = meta_c1.meta_value)\r\n\t\t\t\t\tINNER JOIN {$tbl_postmeta} AS meta_c2 ON (meta_c1.post_id = meta_c2.post_id) AND (meta_c2.meta_key='_room_max_adults')\r\n\t\t\t\t\tLEFT JOIN {$tbl_postmeta} AS meta_c3 ON (meta_c1.post_id = meta_c3.post_id) AND (meta_c3.meta_key='_room_max_kids')\r\n\t\t\t\t\tLEFT JOIN {$tbl_postmeta} AS meta_c4 ON (t1.hotel_id = meta_c4.post_id) AND (meta_c4.meta_key='_hotel_minimum_stay')";
// if this searh has specified date then check vacancy and booking data, but if it doesn't have specified date then only check other search factors
if ($from_date_obj && $to_date_obj) {
// has specified date
$date_interval = DateInterval::createFromDateString('1 day');
$period = new DatePeriod($from_date_obj, $date_interval, $to_date_obj);
$sql_check_date_parts = array();
$days = 0;
foreach ($period as $dt) {
$check_date = $dt->format("Y-m-d");
$sql_check_date_parts[] = "SELECT '{$check_date}' AS check_date";
$days++;
}
$sql_check_date = implode(' UNION ', $sql_check_date_parts);
$v_query = "SELECT t3.hotel_id, t3.room_id, t3.max_adults, t3.max_kids, t3.minimum_stay, MIN(rooms) AS min_rooms FROM (\r\n\t\t\t\t\t\t\tSELECT t2.*, (IFNULL(vacancies.rooms,0) - IFNULL(SUM(bookings.rooms),0)) AS rooms, check_dates.check_date \r\n\t\t\t\t\t\t\tFROM ({$c_query}) AS t2\r\n\t\t\t\t\t\t\tJOIN ( {$sql_check_date} ) AS check_dates\r\n\t\t\t\t\t\t\tLEFT JOIN " . CT_HOTEL_VACANCIES_TABLE . " AS vacancies ON (vacancies.room_type_id = t2.room_id) AND (vacancies.date_from <= check_dates.check_date AND vacancies.date_to > check_dates.check_date)\r\n\t\t\t\t\t\t\tLEFT JOIN ( SELECT hotel_booking.*, hotel_order.date_from, hotel_order.date_to FROM " . CT_HOTEL_BOOKINGS_TABLE . " AS hotel_booking INNER JOIN " . CT_ORDER_TABLE . " as hotel_order ON hotel_order.id = hotel_booking.order_id AND hotel_order.status!='cancelled' ) AS bookings ON (bookings.room_type_id = t2.room_id) AND (bookings.date_from <= check_dates.check_date AND bookings.date_to > check_dates.check_date)\r\n\t\t\t\t\t\t\tGROUP BY t2.room_id, check_dates.check_date\r\n\t\t\t\t\t\t ) AS t3\r\n\t\t\t\t\t\t GROUP BY t3.room_id";
// if rooms == 1 do specific search and if rooms > 1 do overal search for vacancies
if ($rooms == 1) {
$sql = "SELECT t4.hotel_id, SUM(t4.min_rooms) AS rooms FROM ({$v_query}) AS t4\r\n\t\t\t\t\tWHERE ((t4.minimum_stay IS NULL) OR (t4.minimum_stay <= {$days}))\r\n\t\t\t\t\t AND (t4.max_adults >= {$adults})\r\n\t\t\t\t\t AND (t4.max_adults + IFNULL(t4.max_kids,0) >= {$adults} + {$kids})\r\n\t\t\t\t\tGROUP BY t4.hotel_id\r\n\t\t\t\t\tHAVING rooms >= {$rooms}";
} else {
$sql = "SELECT t4.hotel_id, SUM(t4.min_rooms) AS rooms, SUM(IFNULL(t4.max_adults,0) * t4.min_rooms) as hotel_max_adults, SUM(IFNULL(t4.max_kids,0) * t4.min_rooms) as hotel_max_kids FROM ({$v_query}) AS t4\r\n\t\t\t\t\tWHERE ((t4.minimum_stay IS NULL) OR (t4.minimum_stay <= {$days}))\r\n\t\t\t\t\tGROUP BY t4.hotel_id\r\n\t\t\t\t\tHAVING rooms >= {$rooms} AND hotel_max_adults >= {$adults} AND hotel_max_kids >= {$kids}";
}
} else {
// without specified date
$avg_adults = ceil($adults / $rooms);
$avg_kids = ceil($kids / $rooms);
$sql = "{$c_query} WHERE (meta_c2.meta_value >= {$avg_adults}) AND (meta_c2.meta_value + IFNULL(meta_c3.meta_value,0) >= {$avg_adults} + {$avg_kids}) GROUP BY hotel_id";
}
// if wpml is enabled return current language posts
if (defined('ICL_LANGUAGE_CODE') && ct_get_lang_count() > 1 && ct_get_default_language() != ICL_LANGUAGE_CODE) {
$sql = "SELECT it4.element_id AS hotel_id FROM ({$sql}) AS t5\r\n\t\t\t\t\tINNER JOIN {$tbl_icl_translations} it3 ON (it3.element_type = 'post_hotel') AND it3.element_id = t5.hotel_id\r\n\t\t\t\t\tINNER JOIN {$tbl_icl_translations} it4 ON (it4.element_type = 'post_hotel') AND it4.language_code='" . ICL_LANGUAGE_CODE . "' AND it4.trid = it3.trid";
}
// var_dump($sql);
$sql = "CREATE TEMPORARY TABLE IF NOT EXISTS {$temp_tbl_name} AS " . $sql;
$wpdb->query($sql);
$sql = " FROM {$temp_tbl_name} as t1\r\n\t\t\t\tINNER JOIN {$tbl_posts} post_s1 ON (t1.hotel_id = post_s1.ID) AND (post_s1.post_status = 'publish') AND (post_s1.post_type = 'hotel')";
$where = ' 1=1';
// district filter
if (!empty($district) && trim(implode('', $district)) != "") {
$sql .= " INNER JOIN {$tbl_term_relationships} AS tr ON tr.object_id = post_s1.ID \r\n\t\t\t\t\tINNER JOIN {$tbl_term_taxonomy} AS tt ON tt.term_taxonomy_id = tr.term_taxonomy_id";
$where .= " AND tt.taxonomy = 'district' AND tt.term_id IN (" . esc_sql(implode(',', $district)) . ")";
}
// price filter
$sql .= " LEFT JOIN {$tbl_postmeta} AS meta_price ON post_s1.ID = meta_price.post_id AND meta_price.meta_key = '_hotel_price'";
if (!empty($price_filter) && trim(implode('', $price_filter)) != "") {
$price_where = array();
$price_steps = empty($ct_options['hotel_price_filter_steps']) ? '50,80,100' : $ct_options['hotel_price_filter_steps'];
$step_arr = explode(',', $price_steps);
array_unshift($step_arr, 0);
foreach ($price_filter as $index) {
if ($index < count($step_arr) - 1) {
// 80 ~ 100 case
$price_where[] = "( cast(meta_price.meta_value as unsigned) BETWEEN " . esc_sql($step_arr[$index]) . " AND " . esc_sql($step_arr[$index + 1]) . " )";
} else {
// 200+ case
$price_where[] = "( cast(meta_price.meta_value as unsigned) >= " . esc_sql($step_arr[$index]) . " )";
}
}
$where .= " AND ( " . implode(' OR ', $price_where) . " )";
}
// hotel star filter
if (!empty($star_filter) && trim(implode('', $star_filter)) != "") {
$sql .= " LEFT JOIN {$tbl_postmeta} AS meta_star ON post_s1.ID = meta_star.post_id AND meta_star.meta_key = '_hotel_star'";
$where .= " AND IFNULL( meta_star.meta_value, 0 ) IN ( " . esc_sql(implode(',', $star_filter)) . " )";
}
// review filter
$sql .= " LEFT JOIN {$tbl_postmeta} AS meta_rating ON post_s1.ID = meta_rating.post_id AND meta_rating.meta_key = '_review'";
if (!empty($rating_filter) && trim(implode('', $rating_filter)) != "") {
$where .= " AND round( cast( IFNULL( meta_rating.meta_value, 0 ) AS decimal(2,1) ) ) IN ( " . esc_sql(implode(',', $rating_filter)) . " )";
}
// facility filter
if (!empty($facility_filter) && trim(implode('', $facility_filter)) != "") {
$where .= " AND (( SELECT COUNT(1) FROM {$tbl_term_relationships} AS tr1 \r\n\t\t\t\t\tINNER JOIN {$tbl_term_taxonomy} AS tt1 ON ( tr1.term_taxonomy_id= tt1.term_taxonomy_id )\r\n\t\t\t\t\tWHERE tt1.taxonomy = 'hotel_facility' AND tt1.term_id IN (" . esc_sql(implode(',', $facility_filter)) . ") AND tr1.object_id = post_s1.ID ) = " . count($facility_filter) . ")";
}
$sql .= " WHERE {$where}";
$count_sql = "SELECT COUNT(DISTINCT t1.hotel_id)" . $sql;
$count = $wpdb->get_var($count_sql);
if (!empty($order_by)) {
$sql .= " ORDER BY " . $order_by_array[$order_by] . " " . $order;
}
$sql .= " LIMIT {$last_no}, {$per_page};";
$main_sql = "SELECT DISTINCT t1.hotel_id AS hotel_id" . $sql;
$ids = $wpdb->get_results($main_sql, ARRAY_A);
return array('count' => $count, 'ids' => $ids);
}
function ct_tour_get_search_result($args)
{
global $ct_options, $wpdb;
$s = '';
$date = '';
$adults = 1;
$kids = 0;
$tour_type = array();
$price_filter = array();
$rating_filter = array();
$facility_filter = array();
$order_by = '';
$order = '';
$last_no = 0;
$per_page = isset($ct_options['tour_posts']) && is_numeric($ct_options['tour_posts']) ? $ct_options['tour_posts'] : 6;
extract($args);
$order_array = array('ASC', 'DESC');
$order_by_array = array('' => '', 'price' => 'convert(meta_price.meta_value, decimal)', 'rating' => 'meta_rating.meta_value');
if (!array_key_exists($order_by, $order_by_array)) {
$order_by = '';
}
if (!in_array($order, $order_array)) {
$order = 'ASC';
}
$tbl_posts = esc_sql($wpdb->posts);
$tbl_postmeta = esc_sql($wpdb->postmeta);
$tbl_terms = esc_sql($wpdb->prefix . 'terms');
$tbl_term_taxonomy = esc_sql($wpdb->prefix . 'term_taxonomy');
$tbl_term_relationships = esc_sql($wpdb->prefix . 'term_relationships');
$tbl_icl_translations = esc_sql($wpdb->prefix . 'icl_translations');
$temp_tbl_name = esc_sql('Search_' . session_id());
$s_query = "SELECT DISTINCT post_s1.ID AS tour_id FROM {$tbl_posts} AS post_s1 WHERE (post_s1.post_status = 'publish') AND (post_s1.post_type = 'tour')";
// search filter
if (!empty($s)) {
$s_query .= " AND ((post_s1.post_title LIKE '%{$s}%') OR (post_s1.post_content LIKE '%{$s}%') )";
}
// if wpml is enabled do search by default language post
if (defined('ICL_LANGUAGE_CODE') && ct_get_lang_count() > 1) {
$s_query = "SELECT DISTINCT it2.element_id AS tour_id FROM ({$s_query}) AS t0\r\n\t\t\t\t\t\tINNER JOIN {$tbl_icl_translations} it1 ON (it1.element_type = 'post_tour') AND it1.element_id = t0.tour_id\r\n\t\t\t\t\t\tINNER JOIN {$tbl_icl_translations} it2 ON (it2.element_type = 'post_tour') AND it2.language_code='" . ct_get_default_language() . "' AND it2.trid = it1.trid ";
}
$sql = "SELECT t1.* FROM ( {$s_query} ) AS t1 ";
if (!empty($date)) {
$date = esc_sql(date('Y-m-d', ct_strtotime($date)));
$sql .= " LEFT JOIN {$tbl_postmeta} AS meta_c1 ON (meta_c1.meta_key = '_tour_max_people') AND (t1.tour_id = meta_c1.post_id)";
$sql .= " LEFT JOIN {$tbl_postmeta} AS meta_c2 ON (meta_c2.meta_key = '_tour_repeated') AND (t1.tour_id = meta_c2.post_id)";
$sql .= " LEFT JOIN {$tbl_postmeta} AS meta_c3 ON (meta_c3.meta_key = '_tour_date') AND (t1.tour_id = meta_c3.post_id)";
$sql .= " LEFT JOIN ( SELECT tour_booking.tour_id, SUM( tour_booking.adults ) as adults, SUM( tour_booking.kids ) as kids FROM " . CT_TOUR_BOOKINGS_TABLE . " AS tour_booking\r\n\t\t\t\t\t\tINNER JOIN " . CT_ORDER_TABLE . " as tour_order \r\n\t\t\t\t\t\tON tour_order.id = tour_booking.order_id AND tour_order.status!='cancelled'\r\n\t\t\t\t\t\tWHERE tour_order.date_from = '{$date}'\r\n\t\t\t\t\t\tGROUP BY tour_booking.tour_id ) AS booking_info ON booking_info.tour_id = t1.tour_id";
$sql .= " WHERE (( meta_c2.meta_value=1 ) OR ( meta_c2.meta_value=0 AND meta_c3.meta_value='{$date}' )) \r\n\t\t\t\t\t\tAND ((meta_c1.meta_value IS NULL) OR (meta_c1.meta_value='') OR (meta_c1.meta_value-IFNULL(booking_info.adults, 0) > {$adults}) )";
}
// if wpml is enabled return current language posts
if (defined('ICL_LANGUAGE_CODE') && ct_get_lang_count() > 1 && ct_get_default_language() != ICL_LANGUAGE_CODE) {
$sql = "SELECT it4.element_id AS tour_id FROM ({$sql}) AS t5\r\n\t\t\t\t\tINNER JOIN {$tbl_icl_translations} it3 ON (it3.element_type = 'post_hotel') AND it3.element_id = t5.tour_id\r\n\t\t\t\t\tINNER JOIN {$tbl_icl_translations} it4 ON (it4.element_type = 'post_hotel') AND it4.language_code='" . ICL_LANGUAGE_CODE . "' AND it4.trid = it3.trid";
}
$sql = "CREATE TEMPORARY TABLE IF NOT EXISTS {$temp_tbl_name} AS " . $sql;
$wpdb->query($sql);
$sql = " FROM {$temp_tbl_name} as t1\r\n\t\t\t\tINNER JOIN {$tbl_posts} post_s1 ON (t1.tour_id = post_s1.ID) AND (post_s1.post_status = 'publish') AND (post_s1.post_type = 'tour')";
$where = ' WHERE 1=1';
// tour_type filter
if (!empty($tour_type) && trim(implode('', $tour_type)) != "") {
$sql .= " INNER JOIN {$tbl_term_relationships} AS tr ON tr.object_id = post_s1.ID \r\n\t\t\t\t\tINNER JOIN {$tbl_term_taxonomy} AS tt ON tt.term_taxonomy_id = tr.term_taxonomy_id";
$where .= " AND tt.taxonomy = 'tour_type' AND tt.term_id IN (" . esc_sql(implode(',', $tour_type)) . ")";
}
// price filter
$sql .= " LEFT JOIN {$tbl_postmeta} AS meta_price ON post_s1.ID = meta_price.post_id AND meta_price.meta_key = '_tour_price'";
if (!empty($price_filter) && trim(implode('', $price_filter)) != "") {
$price_where = array();
$price_steps = empty($ct_options['tour_price_filter_steps']) ? '50,80,100' : $ct_options['tour_price_filter_steps'];
$step_arr = explode(',', $price_steps);
array_unshift($step_arr, 0);
foreach ($price_filter as $index) {
if ($index < count($step_arr) - 1) {
// 80 ~ 100 case
$price_where[] = "( cast(meta_price.meta_value as unsigned) BETWEEN " . esc_sql($step_arr[$index]) . " AND " . esc_sql($step_arr[$index + 1]) . " )";
} else {
// 200+ case
$price_where[] = "( cast(meta_price.meta_value as unsigned) >= " . esc_sql($step_arr[$index]) . " )";
}
}
$where .= " AND ( " . implode(' OR ', $price_where) . " )";
}
// review filter
$sql .= " LEFT JOIN {$tbl_postmeta} AS meta_rating ON post_s1.ID = meta_rating.post_id AND meta_rating.meta_key = '_review'";
if (!empty($rating_filter) && trim(implode('', $rating_filter)) != "") {
$where .= " AND round( cast( IFNULL( meta_rating.meta_value, 0 ) AS decimal(2,1) ) ) IN ( " . esc_sql(implode(',', $rating_filter)) . " )";
}
// facility filter
if (!empty($facility_filter) && trim(implode('', $facility_filter)) != "") {
$where .= " AND (( SELECT COUNT(1) FROM {$tbl_term_relationships} AS tr1 \r\n\t\t\t\t\tINNER JOIN {$tbl_term_taxonomy} AS tt1 ON ( tr1.term_taxonomy_id= tt1.term_taxonomy_id )\r\n\t\t\t\t\tWHERE tt1.taxonomy = 'tour_facility' AND tt1.term_id IN (" . esc_sql(implode(',', $facility_filter)) . ") AND tr1.object_id = post_s1.ID ) = " . count($facility_filter) . ")";
}
$sql .= $where;
$count_sql = "SELECT COUNT(DISTINCT t1.tour_id)" . $sql;
$count = $wpdb->get_var($count_sql);
if (!empty($order_by)) {
$sql .= " ORDER BY " . $order_by_array[$order_by] . " " . $order;
}
$sql .= " LIMIT {$last_no}, {$per_page};";
$main_sql = "SELECT DISTINCT t1.tour_id AS tour_id" . $sql;
$ids = $wpdb->get_results($main_sql, ARRAY_A);
return array('count' => $count, 'ids' => $ids);
}
