function _get_where_query($where)
{
if (!TravelHelper::checkTableDuplicate('st_hotel')) {
return $where;
}
global $wpdb;
$where .= "";
if (isset($_REQUEST['location_id']) && !empty($_REQUEST['location_id'])) {
$location_id = STInput::request('location_id', '');
$list = TravelHelper::getLocationByParent($location_id);
if (is_array($list) && count($list)) {
$where .= " AND (";
$where_tmp = "";
foreach ($list as $item) {
if (empty($where_tmp)) {
$where_tmp .= "tb.multi_location LIKE '%_{$item}_%'";
} else {
$where_tmp .= " OR tb.multi_location LIKE '%_{$item}_%'";
}
}
$list = implode(',', $list);
$where_tmp .= " OR tb.id_location IN ({$list})";
$where .= $where_tmp . ")";
} else {
$where .= " AND (tb.multi_location LIKE '%_{$location_id}_%' OR tb.id_location IN ('{$location_id}')) ";
}
} else {
if (!empty($_REQUEST['location_name'])) {
$location_name = STInput::request('location_name', '');
$ids_location = TravelerObject::_get_location_by_name($location_name);
if (is_array($ids_location) && count($ids_location)) {
$ids_location_tmp = array();
foreach ($ids_location as $item) {
$list = TravelHelper::getLocationByParent($item);
if (is_array($list) && count($list)) {
foreach ($list as $item) {
$ids_location_tmp[] = $item;
}
}
}
if (count($ids_location_tmp)) {
$ids_location = $ids_location_tmp;
}
}
if (is_array($ids_location) && count($ids_location)) {
$where .= " AND ((";
$where_tmp = "";
foreach ($ids_location as $id) {
if (empty($where_tmp)) {
$where_tmp .= " tb.multi_location LIKE '%_{$id}_%' ";
} else {
$where_tmp .= " OR tb.multi_location LIKE '%_{$id}_%' ";
}
}
$ids_location = implode(',', $ids_location);
$where_tmp .= " OR (tb.id_location IN ({$ids_location})";
$where .= $where_tmp . ")";
$where .= " OR (tb.address LIKE '%{$location_name}%'";
$where .= " OR {$wpdb->prefix}posts.post_title LIKE '%{$location_name}%')))";
} else {
if (!empty($_REQUEST['search_all'])) {
$where .= " AND (tb.address LIKE '%{$location_name}%'";
$where .= " OR {$wpdb->prefix}posts.post_title LIKE '%{$location_name}%')";
}
}
} elseif (isset($_REQUEST['address']) && !empty($_REQUEST['address'])) {
$address = STInput::request('address', '');
$value = STInput::request('address');
$value = explode(",", $value);
if (!empty($value[0]) and !empty($value[2])) {
$where .= " AND ( tb.address LIKE '%{$value[0]}%' OR tb.address LIKE '%{$value[2]}%')";
} else {
$where .= " AND ( tb.address LIKE '%{$address}%')";
}
}
}
if (isset($_GET['start']) && !empty($_GET['start']) && isset($_GET['end']) && !empty($_GET['end'])) {
$check_in = date('Y-m-d', strtotime(TravelHelper::convertDateFormat($_GET['start'])));
$check_out = date('Y-m-d', strtotime(TravelHelper::convertDateFormat($_GET['end'])));
$today = date('m/d/Y');
$period = TravelHelper::dateDiff($today, $check_in);
$adult_number = STInput::get('adult_number', 0);
if (intval($adult_number) < 0) {
$adult_number = 0;
}
$children_number = STInput::get('children_num', 0);
if (intval($children_number) < 0) {
$children_number = 0;
}
$number_room = STInput::get('room_num_search', 0);
if (intval($number_room) < 0) {
$number_room = 0;
}
$list_hotel = HotelHelper::_hotelValidate($check_in, $check_out, $adult_number, $children_number, $number_room);
if (!is_array($list_hotel) || count($list_hotel) <= 0) {
$list_hotel = "''";
} else {
$list_hotel = implode(',', $list_hotel);
}
$where .= " AND {$wpdb->prefix}posts.ID NOT IN ({$list_hotel}) AND CAST(tb.hotel_booking_period AS UNSIGNED) <= {$period}";
}
if (isset($_REQUEST['star_rate']) && !empty($_REQUEST['star_rate'])) {
$stars = STInput::get('star_rate', 1);
$stars = explode(',', $stars);
$all_star = array();
if (!empty($stars) && is_array($stars)) {
foreach ($stars as $val) {
for ($i = $val; $i < $val + 0.9; $i += 0.1) {
if ($i) {
$all_star[] = $i;
}
}
}
}
$list_star = implode(',', $all_star);
if ($list_star) {
$where .= " AND (tb.rate_review IN ({$list_star}))";
}
}
if (isset($_REQUEST['hotel_rate']) && !empty($_REQUEST['hotel_rate'])) {
$hotel_rate = STInput::get('hotel_rate', '');
$where .= " AND (tb.hotel_star IN ({$hotel_rate}))";
}
if (isset($_REQUEST['price_range']) && !empty($_REQUEST['price_range'])) {
$price = STInput::get('price_range', '0;0');
$priceobj = explode(';', $price);
// convert to default money
$priceobj[0] = TravelHelper::convert_money_to_default($priceobj[0]);
$priceobj[1] = TravelHelper::convert_money_to_default($priceobj[1]);
$where .= " AND (tb.price_avg >= {$priceobj[0]})";
if (isset($priceobj[1])) {
$priceobj[1] = TravelHelper::convert_money_to_default($priceobj[1]);
$where .= " AND (tb.price_avg <= {$priceobj[1]})";
}
}
if (isset($_REQUEST['range']) and isset($_REQUEST['location_id'])) {
$range = STInput::request('range', '5');
$location_id = STInput::request('location_id');
$post_type = get_query_var('post_type');
$map_lat = (double) get_post_meta($location_id, 'map_lat', true);
$map_lng = (double) get_post_meta($location_id, 'map_lng', true);
global $wpdb;
$where .= "\r\n AND {$wpdb->posts}.ID IN (\r\n SELECT ID FROM (\r\n SELECT {$wpdb->posts}.*,( 6371 * acos( cos( radians({$map_lat}) ) * cos( radians( mt1.meta_value ) ) *\r\n cos( radians( mt2.meta_value ) - radians({$map_lng}) ) + sin( radians({$map_lat}) ) *\r\n sin( radians( mt1.meta_value ) ) ) ) AS distance\r\n FROM {$wpdb->posts}, {$wpdb->postmeta} as mt1,{$wpdb->postmeta} as mt2\r\n WHERE {$wpdb->posts}.ID = mt1.post_id\r\n and {$wpdb->posts}.ID=mt2.post_id\r\n AND mt1.meta_key = 'map_lat'\r\n and mt2.meta_key = 'map_lng'\r\n AND {$wpdb->posts}.post_status = 'publish'\r\n AND {$wpdb->posts}.post_type = '{$post_type}'\r\n AND {$wpdb->posts}.post_date < NOW()\r\n GROUP BY {$wpdb->posts}.ID HAVING distance<{$range}\r\n ORDER BY distance ASC\r\n ) as st_data\r\n\t )";
}
if (!empty($_REQUEST['taxonomy_hotel_room'])) {
$tax = STInput::request('taxonomy_hotel_room');
if (!empty($tax) and is_array($tax)) {
$tax_query = array();
foreach ($tax as $key => $value) {
if ($value) {
$ids = "";
$ids_tmp = explode(',', $value);
if (!empty($ids_tmp)) {
foreach ($ids_tmp as $k => $v) {
if (!empty($v)) {
$ids[] = $v;
}
}
}
if (!empty($ids)) {
$tax_query[] = array('taxonomy' => $key, 'terms' => $ids);
}
}
}
$where_room = '';
if (!empty($tax_query)) {
$where_room = ' AND (';
foreach ($tax_query as $k => $v) {
$ids = implode(',', $v['terms']);
if ($k > 0) {
$where_room .= " AND ";
}
$where_room .= " (\r\n SELECT COUNT(1)\r\n FROM wp_term_relationships\r\n WHERE term_taxonomy_id IN ({$ids})\r\n AND object_id = wp_posts.ID\r\n ) = " . count($v['terms']) . " ";
}
$where_room .= " ) ";
}
$where .= " AND {$wpdb->posts}.ID IN\r\n (\r\n SELECT ID FROM\r\n (\r\n SELECT meta1.meta_value as ID\r\n FROM {$wpdb->prefix}posts\r\n\r\n INNER JOIN {$wpdb->prefix}postmeta as meta1 ON wp_posts.ID = meta1.post_id and meta1.meta_key='room_parent'\r\n WHERE 1=1\r\n {$where_room}\r\n AND {$wpdb->prefix}posts.post_type = 'hotel_room'\r\n GROUP BY meta1.meta_value\r\n ) as ids\r\n ) ";
}
}
if (isset($_REQUEST['item_id']) and !empty($_REQUEST['item_id'])) {
$item_id = STInput::request('item_id', '');
$where .= " AND ({$wpdb->prefix}posts.ID = '{$item_id}')";
}
return $where;
}
