<?php
require_once("lib/php/include/dbPDO.php");
require_once('memcache.php');
require_once("lib.php");

class Localeze_model extends lib_Memcache
{
	use Lib;
	function __construct()
	{
		$this->db = CommonPDOConnector::connect('MasterLP');
	}

	public function business_results($search, $latlon, $start = 0, $count = 10)
	{
		$results = null;
		$search = trim($search);
		$cache_app = "__-__";
		if ($search != '') {
			$latmin = $this->latmin($latlon, 10);
			$latmax = $this->latmax($latlon, 10);
			$lonmin = $this->lonmin($latlon, 10);
			$lonmax = $this->lonmax($latlon, 10);

			$key = md5('business_results' . $cache_app . $search . $_SESSION['location'] . $start . $count);
			$ttl = 60 * 60 * 24 * 30; // 30 days

			$skipcache = false;
			$yextrow = lib_Memcache::mGet()->get('yext' . $cache_app . $latmin . $latmax . $lonmin . $lonmax);
			$alreadychecked = lib_Memcache::mGet()->get('yext-cache' . $cache_app . $latmin . $latmax . $lonmin . $lonmax);
			if ($alreadychecked === false) {
				if ($yextrow === false) {
					$query = $this->db->query("select lat, lon from yext_inserts where lat between $latmin and $latmax and lon between $lonmin and $lonmax limit 0,200;");
					$query_result = $query->fetchAll(PDO::FETCH_ASSOC);
					$yextrow = count($query_result);
					lib_Memcache::mGet()->set('yext' . $cache_app . $latmin . $latmax . $lonmin . $lonmax, $yextrow, 0, 60 * 60 * 24);
				}
				if ($yextrow > 0) {
					$skipcache = true;
				}
			}

			if ($skipcache === false) {
				$results = lib_Memcache::mGet()->get($key);
				if (is_array($results) && count($results) > 0) {
					foreach ($results as $result) {
						$bizidkey = $result['biz_id'];
						$bizcacheid = lib_Memcache::mGet()->get($bizidkey);

						if ($bizcacheid && $bizcacheid == 1) {
							$results = null;
							lib_Memcache::mGet()->delete($result['biz_id']);
							break;
						}
					}
				} else if (is_int($results)) {
					if ($results == 5) {
						lib_Memcache::mGet()->set($key, 6, 0, 60 * 60 * 24 * 5);
					} else if ($results < 5) {
						lib_Memcache::mGet()->set($key, $results++, 0, 60 * 60 * 24);
					}
				}
			}
			$results = false;
			if ($results === false || $results === null) {
				$keywordData = $this->local_search_keyword($search, $latmin, $latmax, $lonmin, $lonmax, $start, $count);
				if(empty($keywordData)){
					return array();
				}
				$total = $this->search_keyword_total($search, $latmin, $latmax, $lonmin, $lonmax);
				if($total<=0){
					return array();
				}
				lib_Memcache::mGet()->set('total_search', $total, 0, 60 * 60 * 24);
				$results = $this->select_biz($keywordData);
				if (is_array($results) && count($results) > 0) {
					lib_Memcache::mGet()->set($key, $results, 0, $ttl);
				} else {
					lib_Memcache::mGet()->set($key, 1, 0, 60 * 60 * 24);
				}
				lib_Memcache::mGet()->set('yext-cache' . $cache_app . $latmin . $latmax . $lonmin . $lonmax, true, 0, 60 * 60 * 24);
			} else {
				$total = lib_Memcache::mGet()->get('total_search');
			}
			return ["data" => array_values(array_filter($results)), "total" => $total];
		} else {
			return array();
		}
	}

	public function select_biz($data)
	{
		$results = [];
		$resultscount = !empty($data) ? count($data) : 0;
		if ($resultscount > 0 && $data !== false) {
			// $ids = '';
			// foreach ($results as $result) {
			// 	$ids .= $result['biz_id'] . ',';
			// }
			// $ids = rtrim($ids, ',');
			// $result = array();
			// $query = $this->db->query("select id,image,record_id, business_name, FULL_ADDRESS, city, STATE, zip, PHONE, PHONE_CODE, LAT, LON, WEB_ADDRESS, description, specialOffer, specialOffer_url,web_address from local_pages_biz_updated where id in(" . $ids . ") AND (BUSINESS_FLAG IS NULL OR BUSINESS_FLAG != 'S') and duplicate=0 and (CHANGE_CODE IS NULL OR CHANGE_CODE != 'C')");
			// $data = $query->fetchAll(PDO::FETCH_ASSOC);
			// if (count($data) > 0) {
			foreach ($data as $row) {
				$i = array();
				$i['business_name'] = $row['business_name'];
				$i['description'] = $row['description'];
				$i['address1'] = $row['FULL_ADDRESS'];
				$i['biz_id'] = $row['id'];
				$i['city'] = $row['city'];
				$i['state'] = $row['STATE'];
				$i['zip'] = $row['zip'];
				$i['area_code'] = $row['PHONE_CODE'];
				$i['phone'] = $row['PHONE'];
				$i['record_id'] = $row['record_id'];
				$i['specialOffer'] = $row['specialOffer'];
				$i['specialOffer_url'] = $row['specialOffer_url'];
				$i['lat'] = $row['LAT'];
				$i['lon'] = $row['LON'];
				$i['url'] = $row['WEB_ADDRESS'];
				$i['business_id'] = 'lze' . $row['id'];
				$i['phonefrmt']="";
				if ($row['PHONE']) {
					$i['phonefrmt'] = "(" . substr($row['PHONE'], 0, 3) . ") " . substr($row['PHONE'], 3, 3) . '-' . substr($row['PHONE'], 6, 4);
				}
				$i['image'] = (!empty($row['image'])) ? explode('::', $row['image']) : "";

				array_push($results, $i);
				$i = null;
			}
			// }
		}
		if (!empty($results)) {
			return $results;
		} else {
			return false;
		}
	}

	public function search_keyword($search, $latmin, $latmax, $lonmin, $lonmax, $start = 1, $count = 10)
	{
		$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');
		$search_query = $search;
		$search_query = str_replace("'", "", $search_query);
		$search_query = str_replace('"', '', $search_query);
		$result = array();
		$whereQuery = "  WHERE MBRCONTAINS(LINESTRING(point($latmin,$lonmin), point($latmax,$lonmax)), k.pnt) and k.duplicate=0 and  MATCH (k.bname,k.keywords) AGAINST ('" . $search_query . "' IN NATURAL LANGUAGE MODE) and (b.BUSINESS_FLAG IS NULL OR b.BUSINESS_FLAG != 'S') and (b.CHANGE_CODE IS NULL OR b.CHANGE_CODE != 'C')";
		$sql = "select SQL_NO_CACHE * from local_pages_lat_lon_updated AS k join local_pages_biz_updated as b on (b.id=k.biz_id) $whereQuery limit $start, $count;";
		$query = $this->db->query($sql);
		$query_result = $query->fetchAll(PDO::FETCH_ASSOC);
		if (count($query_result) > 0) {
			$resultcount = count($result);
			foreach ($query_result as $row) {
				$duplicate = false;
				for ($i = 0; $i < $resultcount; $i++) {
					if ($result[$i]['biz_id'] == $row['biz_id']) {
						$duplicate = true;
						break;
					}
				}
				if (!$duplicate) {
					$result[] = $row;
				}
			}
		}

		if ($result) {
//			$totalQuery = $this->db->query("select count(*) as total from local_pages_lat_lon_updated AS k join local_pages_biz_updated as b on (b.id=k.biz_id) $whereQuery limit 1");
//			$totalQuery->execute();
//			$totaQueryRes = $totalQuery->fetch();
			$resultcount = mt_rand(100,500);// $totaQueryRes['total'];
			return array_slice($result, 0, $count);
		} else {
			return false;
		}
	}
	public function biz_detail($id)
	{
		$ttl = 60 * 60 * 2; // 30 days
		$key = md5(base_url() . 'bizdetail_new_s2132_' . $id);
		
		$result = lib_Memcache::mGet()->get($key);
		$query = $this->db->query("select id,record_id, business_name, FULL_ADDRESS, city, business_hour,paymentOptions,phone_numbers,
		STATE, zip, PHONE, PHONE_CODE, LAT, LON, WEB_ADDRESS, description, specialOffer, specialOffer_url,category,email,web_address,
		BUSINESS_FLAG,attribution,CHANGE_CODE,suppression_id, record_id , image, video 
		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') 
		limit 1");
		$query_result = $query->fetchAll(PDO::FETCH_ASSOC);
		
		if ($result === false) {
			
			if (count($query_result) > 0) {
				$business_hour = '';
				foreach ($query_result as $row) {
					if ($row['business_hour']) {
						$Hours = unserialize(($row['business_hour']));
						$Days = array("MONDAY", "TUESDAY", "WEDNESDAY", "THURSDAY", "FRIDAY", "SATURDAY", "SUNDAY");


						foreach ($Days as $Day) {
							if ($Hours[$Day]) {
								$SplitHour = explode(',', $Hours[$Day]);
								if ($SplitHour[0] == '00:00:00-00:00:00')
									$business_hour .= ucfirst(strtolower($Day)) . ' : Open All Day <br>';
								else if ($SplitHour[0] == '-')
									$business_hour .= ucfirst(strtolower($Day)) . ' : Closed <br>';
								else if (isset($SplitHour[1])) {
									$Hour = explode('-', $SplitHour[0]);
									$business_hour .= ucfirst(strtolower($Day)) . ' : ' . date("g:i A", strtotime($Hour[0])) . ' - ' . date("g:i A", strtotime($Hour[1]));
									$Hour = explode('-', $SplitHour[1]);
									$business_hour .= ' , ' . date("g:i A", strtotime($Hour[0])) . ' - ' . date("g:i A", strtotime($Hour[1])) . '<br>';
								} else {
									$Hour = explode('-', $SplitHour[0]);
									$business_hour .= ucfirst(strtolower($Day)) . ' : ' . date("g:i A", strtotime($Hour[0])) . ' - ' . date("g:i A", strtotime($Hour[1])) . '<br>';
								}
							}
						}
					}
					$result['business_name'] = $row['business_name'];
					$result['description'] = $row['description'];
					$result['address1'] = $row['FULL_ADDRESS'];
					$result['biz_id'] = $row['id'];
					$result['city'] = $row['city'];
					$result['state'] = $row['STATE'];
					$result['zip'] = $row['zip'];
					$result['area_code'] = $row['PHONE_CODE'];
					$result['phone'] = $row['PHONE'];
					$result['phones'] = !empty($row['phone_numbers']) ? explode("::", $row['phone_numbers']) : [];
					$result['record_id'] = $row['record_id'];
					$result['specialOffer'] = $row['specialOffer'];
					$result['specialOffer_url'] = $row['specialOffer_url'];
					$result['lat'] = $row['LAT'];;
					$result['lon'] = $row['LON'];;
					$result['url'] = $row['WEB_ADDRESS'];
					$result['business_hour'] = $business_hour;
					$result['email'] = $row['email'];
					$result['category'] = !empty($row['category']) ? explode("::", $row['category']) : [];
					$result['payment_options'] = $row['paymentOptions'];
					$result['business_id'] = 'lze' . $row['id'];
					$result['images'] = array_filter(explode('::', $row['image']));
					$result['videos'] = array_filter(explode('::', $row['video']));
					if ($row['PHONE']) {
						$result['phonefrmt'] = "(" . substr($row['PHONE'], 0, 3) . ") " . substr($row['PHONE'], 3, 3) . '-' . substr($row['PHONE'], 6, 4);
					}

					$result['business_flag'] = $row['BUSINESS_FLAG'];
					$result['attribution'] = json_decode($row['attribution'], true);
					$result['change_code'] = $row['CHANGE_CODE'];
					$result['suppression_id'] = $row['suppression_id'];
					$result['yext_id'] = (strpos(strtolower($row['record_id']), 'yext-') !== false) ? ltrim($result['record_id'], 'yext-') : '';
				}
			}
			if (isset($result)) {
				lib_Memcache::mGet()->set($key, $result, 0, $ttl);
			} else {
				return false;
			}
		}
		return $result;
	}

	public function search_keyword_total($search, $latmin, $latmax, $lonmin, $lonmax)
	{
		$search_query = $search;
		$search_query = str_replace("'", "", $search_query);
		$search_query = str_replace('"', '', $search_query);
		// $whereQuery = "  WHERE MBRCONTAINS(LINESTRING(point($latmin,$lonmin), point($latmax,$lonmax)), k.pnt) and k.duplicate=0 and  MATCH (k.bname,k.keywords) AGAINST ('" . $search_query . "' IN NATURAL LANGUAGE MODE) and (b.BUSINESS_FLAG IS NULL OR b.BUSINESS_FLAG != 'S') and (b.CHANGE_CODE IS NULL OR b.CHANGE_CODE != 'C')";
		// $totalQuery = $this->db->query("select count(*) as total from local_pages_lat_lon_updated AS k join local_pages_biz_updated as b on (b.id=k.biz_id) $whereQuery limit 1");
		$whereQuery = "  WHERE MBRCONTAINS(LINESTRING(point($latmin,$lonmin), point($latmax,$lonmax)), l.pnt) and l.duplicate=0 and  MATCH (l.bname,l.keywords) AGAINST ('".$search_query."' IN NATURAL LANGUAGE MODE) "; 
		$totalQuery = $this->db->query("select count(*) as total from local_pages_lat_lon_updated  as l
               JOIN local_pages_biz_updated as b ON l.biz_id = b.id
               $whereQuery AND (b.BUSINESS_FLAG IS NULL OR b.BUSINESS_FLAG != 'S') AND (b.CHANGE_CODE IS NULL OR b.CHANGE_CODE != 'C') limit 1
				");
		$totalQuery->execute();
		$totaQueryRes = $totalQuery->fetch();
		//return mt_rand(100,500);
		return $totaQueryRes['total'];
	}


	public function local_search_keyword($search, $latmin, $latmax, $lonmin, $lonmax, $start = 0, $count = 10)
	{
		$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');
		$search_query = $search;
		$search_query = str_replace("'", "", $search_query);
		$search_query = str_replace('"', '', $search_query);
		$result = array();
		$whereQuery = "  WHERE MBRCONTAINS(LINESTRING(point($latmin,$lonmin), point($latmax,$lonmax)), l.pnt) and l.duplicate=0 and  MATCH (l.bname,l.keywords) AGAINST ('".$search_query."' IN NATURAL LANGUAGE MODE) ";
		$query = $this->db->query("select SQL_NO_CACHE * from local_pages_lat_lon_updated  as l
                JOIN local_pages_biz_updated as b ON l.biz_id = b.id
                $whereQuery AND (b.BUSINESS_FLAG IS NULL OR b.BUSINESS_FLAG != 'S') AND (b.CHANGE_CODE IS NULL OR b.CHANGE_CODE != 'C') limit $start, $count;");

		// $whereQuery = "  WHERE MBRCONTAINS(LINESTRING(point($latmin,$lonmin), point($latmax,$lonmax)), pnt) and duplicate=0 and  MATCH (bname,keywords) AGAINST ('" . $search_query . "' IN NATURAL LANGUAGE MODE) ";
		// $query = $this->db->query("select SQL_NO_CACHE * from local_pages_lat_lon_updated $whereQuery limit $start, $count;");
		$query->execute();
		$output = $query->fetchAll();
		if (count($output)>0) {
			foreach ($output as $row) {
				$result[$row['biz_id']] = $row;
			}
		}
		if ($result) {
			return array_slice($result, 0, $count);
		} else {
			return false;
		}
	}
}