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