">
<input type="hidden" name="post_type" value="tour">
<input type="hidden" name="view" value="<?php
echo esc_attr($current_view);
?>
">
<input type="hidden" name="order_by" value="<?php
echo esc_attr($order_by);
?>
">
<input type="hidden" name="order" value="<?php
echo esc_attr($order);
?>
">
<?php
if (defined('ICL_LANGUAGE_CODE') && $language_count > 1 && trav_get_default_language() != ICL_LANGUAGE_CODE) {
?>
<input type="hidden" name="lang" value="<?php
echo esc_attr(ICL_LANGUAGE_CODE);
?>
">
<?php
}
?>
<div class="form-group">
<label><?php
_e('Your Destination', 'trav');
?>
</label>
<input type="text" name="s" class="input-text full-width" placeholder="<?php
_e('Enter a destination or tour name', 'trav');
function trav_tour_get_search_result($search_data = array())
{
//$search_data = array('s'=>$s, 'date_from'=>$date_from, 'date_to'=>$date_to, 'order_by'=>$order_by_array[$order_by], 'order'=>$order, 'last_no'=>( $page - 1 ) * $per_page, 'per_page'=>$per_page, 'min_price'=>$min_price, 'max_price'=>$max_price, 'tour_type'=>$tour_type )
// if wrong date return false
if (!empty($search_data['date_from']) && !empty($search_data['date_to']) && trav_strtotime($search_data['date_from']) > trav_strtotime($search_data['date_to'])) {
return false;
}
global $wpdb, $language_count;
$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());
$order_by = esc_sql(empty($search_data['order_by']) ? 'tour_title' : $search_data['order_by']);
$order = esc_sql(empty($search_data['order']) ? 'ASC' : $search_data['order']);
$last_no = esc_sql(empty($search_data['last_no']) ? 0 : $search_data['last_no']);
$per_page = esc_sql(empty($search_data['per_page']) ? 10 : $search_data['per_page']);
$max_price = esc_sql(empty($search_data['max_price']) ? 'no_max' : $search_data['max_price']);
$min_price = esc_sql(empty($search_data['min_price']) ? 0 : $search_data['min_price']);
$tour_type = empty($search_data['tour_type']) || !is_array($search_data['tour_type']) ? array() : $search_data['tour_type'];
foreach ($tour_type as $key => $value) {
if (!is_numeric($value)) {
unset($tour_type[$key]);
}
}
$s = '';
if (floatval(get_bloginfo('version')) >= 4.0) {
$s = esc_sql($wpdb->esc_like($search_data['s']));
} else {
$s = esc_sql(like_escape($search_data['s']));
}
$date_from = '';
$date_to = '';
if (!empty($search_data['date_from']) && trav_strtotime($search_data['date_from'])) {
$date_from = trav_strtotime($search_data['date_from']);
} else {
$date_from = time();
}
if (!empty($search_data['date_to']) && trav_strtotime($search_data['date_to'])) {
$date_to = trav_strtotime($search_data['date_to']) + 86400;
} else {
$date_to = strtotime('+30 days');
}
$from_date_obj = new DateTime();
$from_date_obj->setTimestamp($date_from);
$to_date_obj = new DateTime();
$to_date_obj->setTimestamp($date_to);
$sql = '';
$s_query = '';
// sql for search keyword
if (!empty($s)) {
$s_query = "SELECT DISTINCT post_s1.ID AS tour_id FROM {$tbl_posts} AS post_s1 \n\t\t\t\t\t\tLEFT JOIN {$tbl_postmeta} AS meta_s1 ON post_s1.ID = meta_s1.post_id\n\t\t\t\t\t\tLEFT JOIN {$tbl_terms} AS terms_s1 ON (meta_s1.meta_key IN('trav_tour_country','trav_tour_city')) AND (terms_s1.term_id = meta_s1.meta_value)\n\t\t\t\t\t\tWHERE (post_s1.post_status = 'publish') AND (post_s1.post_type = 'tour')\n\t\t\t\t\t\t AND ((post_s1.post_title LIKE '%{$s}%') \n\t\t\t\t\t\t\tOR (post_s1.post_content LIKE '%{$s}%')\n\t\t\t\t\t\t\tOR (meta_s1.meta_value LIKE '%{$s}%')\n\t\t\t\t\t\t\tOR (terms_s1.name LIKE '%{$s}%'))";
} else {
$s_query = "SELECT post_s1.ID AS tour_id FROM {$tbl_posts} AS post_s1 \n\t\t\t\t\t\tWHERE (post_s1.post_status = 'publish') AND (post_s1.post_type = 'tour')";
}
// if wpml is enabled do search by default language post
if (defined('ICL_LANGUAGE_CODE') && $language_count > 1) {
$s_query = "SELECT DISTINCT it2.element_id AS tour_id FROM ({$s_query}) AS t0\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\n\t\t\t\t\t\tINNER JOIN {$tbl_icl_translations} it2 ON (it2.element_type = 'post_tour') AND it2.language_code='" . trav_get_default_language() . "' AND it2.trid = it1.trid ";
}
// if this searh has specified date then check schedule 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);
$sql = "SELECT available_schedules.tour_id, MIN(available_schedules.check_date) as min_date, MAX(available_schedules.check_date) as max_date\n\t\t\t\t\tFROM ( SELECT schedules.*, check_dates.check_date, schedules.max_people - SUM( IFNULL(bookings.adults,0) ) - SUM( IFNULL(bookings.kids,0) ) AS available_seat\n\t\t\t\t\t\tFROM ({$s_query}) AS tours\n\t\t\t\t\t\tINNER JOIN " . TRAV_TOUR_SCHEDULES_TABLE . " AS schedules\n\t\t\t\t\t\tON tours.tour_id = schedules.tour_id\n\t\t\t\t\t\tINNER JOIN ({$sql_check_date}) AS check_dates\n\t\t\t\t\t\tON ( schedules.is_daily = 0 AND check_dates.check_date = schedules.tour_date ) OR ( schedules.is_daily = 1 AND check_dates.check_date >= schedules.tour_date AND check_dates.check_date <= schedules.date_to )\n\t\t\t\t\t\tLEFT JOIN " . TRAV_TOUR_BOOKINGS_TABLE . " AS bookings\n\t\t\t\t\t\tON bookings.tour_id = schedules.tour_id AND bookings.st_id = schedules.st_id AND bookings.tour_date = check_dates.check_date AND bookings.status<>0\n\t\t\t\t\t\tGROUP BY schedules.tour_id, schedules.st_id, check_dates.check_date\n\t\t\t\t\t\tHAVING available_seat > 0\n\t\t\t\t\t\tORDER BY st_id ASC, tour_date ASC ) AS available_schedules\n\t\t\t\t\tGROUP BY available_schedules.tour_id";
} else {
return false;
}
// if wpml is enabled return current language posts
if (defined('ICL_LANGUAGE_CODE') && $language_count > 1 && trav_get_default_language() != ICL_LANGUAGE_CODE) {
$sql = "SELECT it4.element_id AS tour_id, t5.min_date, t5.max_date FROM ({$sql}) AS t5\n\t\t\t\t\tINNER JOIN {$tbl_icl_translations} it3 ON (it3.element_type = 'post_tour') AND it3.element_id = t5.tour_id\n\t\t\t\t\tINNER JOIN {$tbl_icl_translations} it4 ON (it4.element_type = 'post_tour') 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 = "SELECT DISTINCT t1.*, post_l1.post_title as tour_title, meta_price.meta_value as min_price FROM {$temp_tbl_name} as t1\n\t\t\t\tINNER JOIN {$tbl_posts} post_l1 ON (t1.tour_id = post_l1.ID) AND (post_l1.post_status = 'publish') AND (post_l1.post_type = 'tour')\n\t\t\t\tLEFT JOIN {$tbl_postmeta} AS meta_price ON (t1.tour_id = meta_price.post_id) AND (meta_price.meta_key = 'trav_tour_min_price')";
$where = ' 1=1';
if ($min_price != 0) {
$where .= " AND cast(meta_price.meta_value as unsigned) >= {$min_price}";
}
if ($max_price != 'no_max') {
$where .= " AND cast(meta_price.meta_value as unsigned) <= {$max_price} ";
}
if (!empty($tour_type)) {
$sql .= " INNER JOIN {$tbl_term_relationships} AS tr ON tr.object_id = t1.tour_id \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)) . ")";
}
$sql .= " WHERE {$where} ORDER BY {$order_by} {$order} LIMIT {$last_no}, {$per_page};";
$results = $wpdb->get_results($sql);
return $results;
}
function trav_acc_get_search_result($s = '', $date_from = '', $date_to = '', $rooms = 1, $adults = 1, $kids = 0, $order_by = 'acc_title', $order = 'ASC', $last_no = 0, $per_page = 12, $min_price = 0, $max_price = 'no_max', $rating = 0, $acc_type, $amenities)
{
// if wrong date return false
if (!empty($date_from) && !empty($date_to) && trav_strtotime($date_from) >= trav_strtotime($date_to)) {
return false;
}
global $wpdb, $language_count;
$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());
$sql = '';
$rooms = esc_sql($rooms);
$adults = esc_sql($adults);
$kids = esc_sql($kids);
$order_by = esc_sql($order_by);
$order = esc_sql($order);
$last_no = esc_sql($last_no);
$per_page = esc_sql($per_page);
$min_price = esc_sql($min_price);
$max_price = esc_sql($max_price);
$rating = esc_sql($rating);
if (empty($acc_type) || !is_array($acc_type)) {
$acc_type = array();
}
if (empty($amenities) || !is_array($amenities)) {
$amenities = array();
}
foreach ($acc_type as $key => $value) {
if (!is_numeric($value)) {
unset($acc_type[$key]);
}
}
foreach ($amenities as $key => $value) {
if (!is_numeric($value)) {
unset($amenities[$key]);
}
}
//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));
}
$from_date_obj = date_create_from_format(trav_get_date_format('php'), $date_from);
$to_date_obj = date_create_from_format(trav_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
if (!empty($s)) {
$s_query = "SELECT DISTINCT post_s1.ID AS acc_id FROM {$tbl_posts} AS post_s1 \n\t\t\t\t\t\tLEFT JOIN {$tbl_postmeta} AS meta_s1 ON post_s1.ID = meta_s1.post_id\n\t\t\t\t\t\tLEFT JOIN {$tbl_terms} AS terms_s1 ON (meta_s1.meta_key IN('trav_accommodation_country','trav_accommodation_city')) AND (terms_s1.term_id = meta_s1.meta_value)\n\t\t\t\t\t\tWHERE (post_s1.post_status = 'publish') AND (post_s1.post_type = 'accommodation')\n\t\t\t\t\t\t AND ((post_s1.post_title LIKE '%{$s}%') \n\t\t\t\t\t\t\tOR (post_s1.post_content LIKE '%{$s}%')\n\t\t\t\t\t\t\tOR (meta_s1.meta_value LIKE '%{$s}%')\n\t\t\t\t\t\t\tOR (terms_s1.name LIKE '%{$s}%'))";
} else {
$s_query = "SELECT post_s1.ID AS acc_id FROM {$tbl_posts} AS post_s1 \n\t\t\t\t\t\tWHERE (post_s1.post_status = 'publish') AND (post_s1.post_type = 'accommodation')";
}
// if wpml is enabled do search by default language post
if (defined('ICL_LANGUAGE_CODE') && $language_count > 1) {
$s_query = "SELECT DISTINCT it2.element_id AS acc_id FROM ({$s_query}) AS t0\n\t\t\t\t\t\tINNER JOIN {$tbl_icl_translations} it1 ON (it1.element_type = 'post_accommodation') AND it1.element_id = t0.acc_id\n\t\t\t\t\t\tINNER JOIN {$tbl_icl_translations} it2 ON (it2.element_type = 'post_accommodation') AND it2.language_code='" . trav_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\n\t\t\t\t\tFROM ( {$s_query} ) AS t1\n\t\t\t\t\tINNER JOIN {$tbl_postmeta} AS meta_c1 ON (meta_c1.meta_key = 'trav_room_accommodation') AND (t1.acc_id = meta_c1.meta_value)\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='trav_room_max_adults')\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='trav_room_max_kids')\n\t\t\t\t\tLEFT JOIN {$tbl_postmeta} AS meta_c4 ON (t1.acc_id = meta_c4.post_id) AND (meta_c4.meta_key='trav_accommodation_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.acc_id, t3.room_id, t3.max_adults, t3.max_kids, t3.minimum_stay, MIN(rooms) AS min_rooms FROM (\n\t\t\t\t\t\t\tSELECT t2.*, (IFNULL(vacancies.rooms,0) - IFNULL(SUM(bookings.rooms),0)) AS rooms, check_dates.check_date \n\t\t\t\t\t\t\tFROM ({$c_query}) AS t2\n\t\t\t\t\t\t\tJOIN ( {$sql_check_date} ) AS check_dates\n\t\t\t\t\t\t\tLEFT JOIN " . TRAV_ACCOMMODATION_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)\n\t\t\t\t\t\t\tLEFT JOIN " . TRAV_ACCOMMODATION_BOOKINGS_TABLE . " AS bookings ON bookings.status!='0' AND (bookings.room_type_id = t2.room_id) AND (bookings.date_from <= check_dates.check_date AND bookings.date_to > check_dates.check_date)\n\t\t\t\t\t\t\tGROUP BY t2.room_id, check_dates.check_date\n\t\t\t\t\t\t ) AS t3\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.acc_id, SUM(t4.min_rooms) AS rooms FROM ({$v_query}) AS t4\n\t\t\t\t\tWHERE ((t4.minimum_stay IS NULL) OR (t4.minimum_stay <= {$days}))\n\t\t\t\t\t AND (t4.max_adults >= {$adults})\n\t\t\t\t\t AND (t4.max_adults + IFNULL(t4.max_kids,0) >= {$adults} + {$kids})\n\t\t\t\t\tGROUP BY t4.acc_id\n\t\t\t\t\tHAVING rooms >= {$rooms}";
} else {
$sql = "SELECT t4.acc_id, SUM(t4.min_rooms) AS rooms, SUM(IFNULL(t4.max_adults,0) * t4.min_rooms) as acc_max_adults, SUM(IFNULL(t4.max_kids,0) * t4.min_rooms) as acc_max_kids FROM ({$v_query}) AS t4\n\t\t\t\t\tWHERE ((t4.minimum_stay IS NULL) OR (t4.minimum_stay <= {$days}))\n\t\t\t\t\tGROUP BY t4.acc_id\n\t\t\t\t\tHAVING rooms >= {$rooms} AND acc_max_adults >= {$adults} AND acc_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 acc_id";
}
// if wpml is enabled return current language posts
if (defined('ICL_LANGUAGE_CODE') && $language_count > 1 && trav_get_default_language() != ICL_LANGUAGE_CODE) {
$sql = "SELECT it4.element_id AS acc_id FROM ({$sql}) AS t5\n\t\t\t\t\tINNER JOIN {$tbl_icl_translations} it3 ON (it3.element_type = 'post_accommodation') AND it3.element_id = t5.acc_id\n\t\t\t\t\tINNER JOIN {$tbl_icl_translations} it4 ON (it4.element_type = 'post_accommodation') 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 = "SELECT t1.*, post_l1.post_title as acc_title, meta_rating.meta_value as review, meta_price.meta_value as avg_price FROM {$temp_tbl_name} as t1\n\t\t\t\tINNER JOIN {$tbl_posts} post_l1 ON (t1.acc_id = post_l1.ID) AND (post_l1.post_status = 'publish') AND (post_l1.post_type = 'accommodation')\n\t\t\t\tLEFT JOIN {$tbl_postmeta} AS meta_rating ON (t1.acc_id = meta_rating.post_id) AND (meta_rating.meta_key = 'review')\n\t\t\t\tLEFT JOIN {$tbl_postmeta} AS meta_price ON (t1.acc_id = meta_price.post_id) AND (meta_price.meta_key = 'trav_accommodation_avg_price')";
$where = ' 1=1';
if ($min_price != 0) {
$where .= " AND cast(meta_price.meta_value as unsigned) >= {$min_price}";
}
if ($max_price != 'no_max') {
$where .= " AND cast(meta_price.meta_value as unsigned) <= {$max_price} ";
}
// rating filter
if ($rating != 0) {
$where .= " AND cast(meta_rating.meta_value as DECIMAL(2,1)) >= {$rating} ";
}
if (!empty($acc_type)) {
$sql .= " INNER JOIN {$tbl_term_relationships} AS tr ON tr.object_id = t1.acc_id \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 = 'accommodation_type' AND tt.term_id IN (" . esc_sql(implode(',', $acc_type)) . ")";
}
if (!empty($amenities)) {
$where .= " AND (( SELECT COUNT(1) FROM {$tbl_term_relationships} AS tr1 \n\t\t\t\t\tINNER JOIN {$tbl_term_taxonomy} AS tt1 ON ( tr1.term_taxonomy_id= tt1.term_taxonomy_id )\n\t\t\t\t\tWHERE tt1.taxonomy = 'amenity' AND tt1.term_id IN (" . esc_sql(implode(',', $amenities)) . ") AND tr1.object_id = t1.acc_id ) = " . count($amenities) . ")";
}
$sql .= " WHERE {$where} GROUP BY acc_id ORDER BY {$order_by} {$order} LIMIT {$last_no}, {$per_page};";
$results = $wpdb->get_results($sql);
return $results;
}