<?php

function localBizDB_search_keyword_new($search,$dbd,$latmin,$latmax,$lonmin,$lonmax,$resultstart = 0,$resultcount = 10){
	$search_query = $search;
	$stopwords = array("a", "about", "above", "above", "across", "after", "afterwards", "again", "against", "all", "almost", "alone", "along", "already", "also","although","always","am","among", "amongst", "amoungst", "amount",  "an", "and", "another", "any","anyhow","anyone","anything","anyway", "anywhere", "are", "around", "as",  "at", "back","be","became", "because","become","becomes", "becoming", "been", "before", "beforehand", "behind", "being", "below", "beside", "besides", "between", "beyond", "bill", "both", "bottom","but", "by", "call", "can", "cannot", "cant", "co", "con", "could", "couldnt", "cry", "de", "describe", "detail", "do", "done", "down", "due", "during", "each", "eg", "eight", "either", "eleven","else", "elsewhere", "empty", "enough", "etc", "even", "ever", "every", "everyone", "everything", "everywhere", "except", "few", "fifteen", "fify", "fill", "find", "fire", "first", "five", "for", "former", "formerly", "forty", "found", "four", "from", "front", "full", "further", "get", "give", "go", "had", "has", "hasnt", "have", "he", "hence", "her", "here", "hereafter", "hereby", "herein", "hereupon", "hers", "herself", "him", "himself", "his", "how", "however", "hundred", "ie", "if", "in", "inc", "indeed", "interest", "into", "is", "it", "its", "itself", "keep", "last", "latter", "latterly", "least", "less", "ltd", "made", "many", "may", "me", "meanwhile", "might", "mill", "mine", "more", "moreover", "most", "mostly", "move", "much", "must", "my", "myself", "name", "namely", "neither", "never", "nevertheless", "next", "nine", "no", "nobody", "none", "noone", "nor", "not", "nothing", "now", "nowhere", "of", "off", "often", "on", "once", "one", "only", "onto", "or", "other", "others", "otherwise", "our", "ours", "ourselves", "out", "over", "own","part", "per", "perhaps", "please", "put", "rather", "re", "same", "see", "seem", "seemed", "seeming", "seems", "serious", "several", "she", "should", "show", "side", "since", "sincere", "six", "sixty", "so", "some", "somehow", "someone", "something", "sometime", "sometimes", "somewhere", "still", "such", "system", "take", "ten", "than", "that", "the", "their", "them", "themselves", "then", "thence", "there", "thereafter", "thereby", "therefore", "therein", "thereupon", "these", "they", "thickv", "thin", "third", "this", "those", "though", "three", "through", "throughout", "thru", "thus", "to", "together", "too", "top", "toward", "towards", "twelve", "twenty", "two", "un", "under", "until", "up", "upon", "us", "very", "via", "was", "we", "well", "were", "what", "whatever", "when", "whence", "whenever", "where", "whereafter", "whereas", "whereby", "wherein", "whereupon", "wherever", "whether", "which", "while", "whither", "who", "whoever", "whole", "whom", "whose", "why", "will", "with", "within", "without", "would", "yet", "you", "your", "yours", "yourself", "yourselves", "the");
	$terms = explode(' ', trim($search_query));
	$like = '';
	foreach($terms as $term) {
		if(strlen($term) >= 3 && !in_array($term, $stopwords)) {
			$like .= "bname like '%".$term."%' or ";
		}
	}
	$like = rtrim($like, ' or ');
	
	$searchresult = array();
	$query_start = microtime(true);
	//$query = "select * from local_pages_lat_lon_updated where bname like '%$search%' and lat between $latmin and $latmax and lon between $lonmin and $lonmax and duplicate=0 limit $resultstart,$resultcount";
	//$query = "select * from local_pages_lat_lon_updated where match(bname) against('$search' in boolean mode) and lat between $latmin and $latmax and lon between $lonmin and $lonmax and duplicate=0 limit $resultstart,$resultcount";
	//$query = "select * from local_pages_lat_lon_updated where match(bname, keywords) against('$search' in boolean mode) and lat between $latmin and $latmax and lon between $lonmin and $lonmax and duplicate=0 limit $resultstart,$resultcount";
	$query = "select SQL_NO_CACHE * from local_pages_lat_lon_updated WHERE MBRCONTAINS(LINESTRING(point($latmin,$lonmin), point ($latmax,$lonmax)),pnt) and duplicate=0 and ($like) limit $resultcount;";
	$result = @mysql_query($query,$dbd);
	$query_time = microtime(true) - $query_start;
	if( $_SERVER['HTTP_X_FORWARDED_FOR']=="199.27.174.35" || $_SERVER['HTTP_X_FORWARDED_FOR'] == '74.62.32.106' || strpos($_SERVER['HTTP_X_FORWARDED_FOR'], '192.168') == 0)
	{
	echo "<!-- <font color=white>
	Query1 <<< $query >>> time:: $query_time   </font> -->";
	}
	/*if(stristr($_SERVER['HTTP_X_FORWARDED_FOR'],"192.168.")){
		print "search_keyword_new : ";
		var_dump($query);
	}*/

	while ($row = @mysql_fetch_assoc($result))
	{
		//$searchresult[] = $row;
		$duplicate = false;
		for($i=0; $i<count($searchresult); $i++){
			if($searchresult[$i]['biz_id'] == $row['biz_id']){
				$duplicate = true;
				break;
			}
		}
		if(!$duplicate)
			$searchresult[] = $row;
	}
	
	$terms = explode(' ', trim($search_query));
	$like = '';
	foreach($terms as $term) {
		if(strlen($term) >= 3 && !in_array($term, $stopwords)) {
			$like .= 'keywords like \'%'.$term.'%\' or ';
		}
	}
	$like = rtrim($like, ' or ');
	
	$query_start = microtime(true);
	//$query = "select * from local_pages_lat_lon_updated where keywords like '%$search%' and lat between $latmin and $latmax and lon between $lonmin and $lonmax and duplicate=0 limit $resultstart,$resultcount";
	//$query = "select * from local_pages_lat_lon_updated where match(keywords) against('$search' in boolean mode) and lat between $latmin and $latmax and lon between $lonmin and $lonmax and duplicate=0 limit $resultstart,$resultcount";
	$query = "select SQL_NO_CACHE * from local_pages_lat_lon_updated WHERE MBRCONTAINS(LINESTRING(point($latmin,$lonmin), point ($latmax,$lonmax)),pnt) and duplicate=0 and ($like) limit $resultcount;";
	$result = @mysql_query($query,$dbd);
	$query_time = microtime(true) - $query_start;
	if( $_SERVER['HTTP_X_FORWARDED_FOR']=="199.27.174.35" || $_SERVER['HTTP_X_FORWARDED_FOR'] == '74.62.32.106' ||  strpos($_SERVER['HTTP_X_FORWARDED_FOR'], '192.168') == 0)
	{
	echo "<!-- <font color=white>
	Query2 <<< $query >>> time:: $query_time   </font> -->";
	}
	/*if(stristr($_SERVER['HTTP_X_FORWARDED_FOR'],"192.168.")){
		print "search_keyword_new : ";
		var_dump($query);
	}*/
	
	while ($row = @mysql_fetch_assoc($result)) {
		//$searchresult[] = $row;
		$duplicate = false;
		for($i=0; $i<count($searchresult); $i++){
			if($searchresult[$i]['biz_id'] == $row['biz_id']){
				$duplicate = true;
				break;
			}
		}
		if(!$duplicate)
			$searchresult[] = $row;
	}
	
#	$splitkeyword = array();
#	$splitkeyword = explode(" ", $search);
#
#	//remove blank searches or multiple spaces
#	$splitkeyword = array_values(array_filter(array_map('trim', $splitkeyword)));
#	if (count($searchresult) > $resultcount){
#		return array_slice($searchresult, 0, $resultcount);
#	} else {
#			$query_start = microtime(true);
#			//$query2 = "select * from local_pages_lat_lon_updated where lat between $latmin and $latmax and lon between $lonmin and $lonmax and keywords like '%$splitkeyword[0]%' and duplicate=0 limit 0,$resultcount";
#			$query2 = "select * from local_pages_lat_lon_updated where lat between $latmin and $latmax and lon between $lonmin and $lonmax and match(keywords) against('$splitkeyword[0]' in boolean mode) and duplicate=0 limit 0,$resultcount";
#			$result = @mysql_query($query2,$dbd);
#			$query_time = microtime(true) - $query_start;
#			if( $_SERVER['HTTP_X_FORWARDED_FOR']=="199.27.174.35" || $_SERVER['HTTP_X_FORWARDED_FOR'] == '74.62.32.106' ||  strpos($_SERVER['HTTP_X_FORWARDED_FOR'], '192.168') == 0)
#			{
#			echo "<!-- <font color=white>
#			Query4 <<< $query2 >>> time:: $query_time   </font> -->";
#			}
#			/*if(stristr($_SERVER['HTTP_X_FORWARDED_FOR'],"192.168.")){
#				print "search_keyword_new : ";
#				var_dump($query2);
#			}*/
#			while ($row = @mysql_fetch_assoc($result)) {
#				//$searchresult[] = $row;
#				$duplicate = false;
#				for($i=0; $i<count($searchresult); $i++){
#					if($searchresult[$i]['biz_id'] == $row['biz_id']){
#						$duplicate = true;
#						break;
#					}
#				}
#				if(!$duplicate)
#				$searchresult[] = $row;
#			}
#
#	}
#	if (count($searchresult) > $resultcount){
#		return array_slice($searchresult, 0, $resultcount);
#	} elseif ($splitkeyword[1]){
#			$query_start = microtime(true);
#			//$query3 = "select * from local_pages_lat_lon_updated where lat between $latmin and $latmax and lon between $lonmin and $lonmax and keywords like '%$splitkeyword[1]%' and duplicate=0 limit 0,$resultcount";
#			$query3 = "select * from local_pages_lat_lon_updated where lat between $latmin and $latmax and lon between $lonmin and $lonmax and match(keywords) against('$splitkeyword[1]' in boolean mode) and duplicate=0 limit 0,$resultcount";
#			$result = @mysql_query($query3,$dbd);
#			$query_time = microtime(true) - $query_start;
#			if( $_SERVER['HTTP_X_FORWARDED_FOR']=="199.27.174.35" || $_SERVER['HTTP_X_FORWARDED_FOR'] == '74.62.32.106' ||  strpos($_SERVER['HTTP_X_FORWARDED_FOR'], '192.168') == 0)
#			{
#			echo "<!-- <font color=white>
#			Query5 <<< $query3 >>> time:: $query_time   </font> -->";
#			}
#			/*if(stristr($_SERVER['HTTP_X_FORWARDED_FOR'],"192.168.")){
#				print "search_keyword_new : ";
#				var_dump($query3);
#			}*/
#			while ($row = @mysql_fetch_assoc($result)) {
#				//$searchresult[] = $row;
#				$duplicate = false;
#				for($i=0; $i<count($searchresult); $i++){
#					if($searchresult[$i]['biz_id'] == $row['biz_id']){
#						$duplicate = true;
#						break;
#					}
#				}
#				if(!$duplicate)
#				$searchresult[] = $row;
#			}
#
#	}
	if ($searchresult)
		return array_slice($searchresult, 0, $resultcount);
}

function localBizDB_select_biz_new2($results,$dbd){
	for ($i=0;$i< count($results);$i++){
		//echo $results[$i]['biz_id']." - ";
		 $query = "select id, record_id, business_name,FULL_ADDRESS,city,STATE,zip,PHONE,PHONE_CODE,web_address,description,specialOffer, specialOffer_url, LAT, LON  from local_pages_biz_updated where id = ". $results[$i]['biz_id']." AND (BUSINESS_FLAG IS NULL OR BUSINESS_FLAG != 'S') and duplicate=0 and (CHANGE_CODE IS NULL OR CHANGE_CODE != 'C') GROUP BY business_name limit 1";

		$result = @mysql_query($query,$dbd);

		/*if(stristr($_SERVER['HTTP_X_FORWARDED_FOR'],"192.168.")){
			print "select_biz_new2 : ";
			var_dump($query);
		}*/
		while ($row = @mysql_fetch_assoc($result))
			{

			$BizResult['InArea'][$i]['business_name'] = $row['business_name'];
			$BizResult['InArea'][$i]['description'] = $row['description'];
			$BizResult['InArea'][$i]['address1'] = $row['FULL_ADDRESS'];
			//$BizResult['InArea'][$i]['biz_id'] = $row['id'];
			$BizResult['InArea'][$i]['city'] = $row['city'];
			$BizResult['InArea'][$i]['state'] = $row['STATE'];
			$BizResult['InArea'][$i]['zip'] = $row['zip'];
			//$BizResult['InArea'][$i]['area_code'] = $row['PHONE_CODE'];
			$BizResult['InArea'][$i]['phone'] = $row['PHONE'];
			//$BizResult['InArea'][$i]['record_id'] = $row['record_id'];
			//$BizResult['InArea'][$i]['specialOffer'] = $row['specialOffer'];
			//$BizResult['InArea'][$i]['specialOffer_url'] = $row['specialOffer_url'];
			$BizResult['InArea'][$i]['lat'] = $row['LAT'];
			$BizResult['InArea'][$i]['lon'] = $row['LON'];
			$BizResult['InArea'][$i]['url'] = $row['web_address'];
			$BizResult['InArea'][$i]['business_id'] = 'lze'.$row['id'];

			if ($row['PHONE']){
				$BizResult['InArea'][$i]['phone'] = "(".substr($row['PHONE'],0,3).") ".substr($row['PHONE'],3,3).'-'.substr($row['PHONE'],6,4);
			}
			
		}
		
		//$QueryPremium = "select id from premium where id = ". $BizResult['InArea'][$i]['biz_id']." limit 1";
		//$ResultPremium = @mysql_query($QueryPremium,$dbd);
		/*if(stristr($_SERVER['HTTP_X_FORWARDED_FOR'],"192.168.")){
			print "select_biz_new2 : ";
			var_dump($QueryPremium);
		}*/
		
		
		/*if(@mysql_num_rows($ResultPremium))
			$BizResult['InArea'][$i]['premium'] = 1;
		else
			$BizResult['InArea'][$i]['premium'] = 0;
		*/

	}

	if ($_SERVER['HTTP_X_FORWARDED_FOR']=="74.62.32.106" || stristr($_SERVER['HTTP_X_FORWARDED_FOR'],"192.168.")){
		//print "<!--";
		//print "business query:  $cache_key ";
		//print_r($searchresult);

		//print "-->";
	}
	if ($BizResult['InArea'])
		return $BizResult;
}

function localBizDB_place2latlon_new($place,$dbd){
	
	if (   strlen($place) == 5 && is_numeric($place)){
			
		 	//return (zip2latlon($place, $dbd));
			$query = "select zipcode, lat,lon,state_abbr,city_name from zip_codes where zipcode = '{$place}' limit 1";
			$result = mysql_query($query,$dbd);
			$row=mysql_fetch_assoc($result);

			return $row;
	
	} elseif ((strlen($place)==3) && is_number($place)){
	
			return(area2latlon($place, $dbd));
	}

	$locationarray = array();
	$locationarray = explode(",",$place);
	$count = count($locationarray);
	
	if ($count >= 2){
		$city = trim($locationarray[$count-2]);
		$state = trim($locationarray[$count-1]);
	} else {
		$city = trim($locationarray[$count-1]);
	}

	if ($state){
		 $query = "select zipcode, lat,lon,state_abbr,city_name from zip_codes where city_name like '$city' and (state_abbr like '$state' or state_name like '$state') limit 1";
	} else {
		 $query = "select zipcode, lat,lon,state_abbr,city_name from zip_codes where city_name like '$city' limit 1";
	}

	$result = mysql_query($query,$dbd);
	$row=mysql_fetch_assoc($result);

	return $row;
}

function localBizDB_biz_profile($id,$dbd){
	$id = str_replace('lze', '', $id);
	$query = "select id, record_id, business_name,FULL_ADDRESS,city,STATE,zip,PHONE,PHONE_CODE,web_address,description,specialOffer, specialOffer_url  from local_pages_biz_updated where id = ".$id." AND (BUSINESS_FLAG IS NULL OR BUSINESS_FLAG != 'S') and duplicate=0 and (CHANGE_CODE IS NULL OR CHANGE_CODE != 'C') GROUP BY business_name limit 1";

	$result = @mysql_query($query,$dbd);

	while ($row = @mysql_fetch_assoc($result))
	{

		$BizResult['Listing']['business_name'] = $row['business_name'];
		$BizResult['Listing']['description'] = $row['description'];
		$BizResult['Listing']['address1'] = $row['FULL_ADDRESS'];
		//$BizResult['Listing']['biz_id'] = $row['id'];
		$BizResult['Listing']['city'] = $row['city'];
		$BizResult['Listing']['state'] = $row['STATE'];
		$BizResult['Listing']['zip'] = $row['zip'];
		//$BizResult['Listing']['area_code'] = $row['PHONE_CODE'];
		$BizResult['Listing']['phone'] = $row['PHONE'];
		//$BizResult['Listing']['record_id'] = $row['record_id'];
		//$BizResult['Listing']['specialOffer'] = $row['specialOffer'];
		//$BizResult['Listing']['specialOffer_url'] = $row['specialOffer_url'];
		$BizResult['Listing']['lat'] = '';
		$BizResult['Listing']['lon'] = '';
		$BizResult['Listing']['url'] = $row['web_address'];
		$BizResult['Listing']['business_id'] = 'lze'.$row['id'];

		if ($row['PHONE']){
			$BizResult['Listing']['phone'] = "(".substr($row['PHONE'],0,3).") ".substr($row['PHONE'],3,3).'-'.substr($row['PHONE'],6,4);
		}
	}
		
	//$QueryPremium = "select id from premium where id = ". $BizResult['InArea'][$i]['biz_id']." limit 1";
	//$ResultPremium = @mysql_query($QueryPremium,$dbd);
	/*if(stristr($_SERVER['HTTP_X_FORWARDED_FOR'],"192.168.")){
		print "select_biz_new2 : ";
		var_dump($QueryPremium);
	}*/
		
		
	/*if(@mysql_num_rows($ResultPremium))
		$BizResult['InArea'][$i]['premium'] = 1;
	else
		$BizResult['InArea'][$i]['premium'] = 0;
	*/
	
	return $BizResult;
}