From 12d9dd06112918243148d5d4b6132dfc6379ff37 Mon Sep 17 00:00:00 2001 From: Laury GvR Date: Wed, 7 Aug 2019 18:40:09 -0400 Subject: [PATCH] POI route test filtering events by time and within latlon boundary --- setup/routes/pointsOfInterest.php | 168 +++++++++++++++++++++++++++++- 1 file changed, 165 insertions(+), 3 deletions(-) diff --git a/setup/routes/pointsOfInterest.php b/setup/routes/pointsOfInterest.php index 0448f54..f492382 100644 --- a/setup/routes/pointsOfInterest.php +++ b/setup/routes/pointsOfInterest.php @@ -60,6 +60,11 @@ class GLMA_POI_Rest_Controller 'methods' => WP_REST_Server::READABLE, 'callback' => function( $request ) { + // Defaults + $limit = 1; + + + // Parameter collection & assignment $this->params = $request->get_params(); $latMin = $this->params['latMin']; $lastLatMin = $this->params['lastLatMin']; @@ -69,6 +74,15 @@ class GLMA_POI_Rest_Controller $lastLonMin = $this->params['lastLonMin']; $lonMax = $this->params['lonMax']; $lastLonMax = $this->params['lastLonMax']; + + $poiRequest = $this->params['poiRequest']; + + + $searchText = "test"; + + if (isset($this->params['limit'])) { + $limit = $this->params['limit']; + } // Callback function can be a lot shorter. @@ -120,8 +134,8 @@ class GLMA_POI_Rest_Controller $poiData = apply_filters( 'glm-hook-list-map-items-by-latlon', array( - 'request' => $request, - // 'filter' => $searchText, + 'request' => $poiRequest, + 'filter' => $searchText, 'area' => $poiAreas, 'sources' => array(), 'mapItems' => array() @@ -130,10 +144,157 @@ class GLMA_POI_Rest_Controller $this->params = $request->get_params(); - $test = json_encode($request['latMax']); + + //$mapItems = $MapItems->modelAction($data['request'], $data['area'], $data['filter']); + + //$limit = 5; + $dateFrom = "2016-11-22 00:00:00"; + $dateTo = "2026-11-30 24:59:59"; + $textWhere = ''; + $latLonWhereForEventLoc = ''; + $latLonWhereForMembLoc = ''; + if ($poiRequest == 'onMap') { + + $latLonWhereForEventLoc = ' AND ( '; + $latLonWhereForMembLoc = ' AND ( '; + $or = ''; + foreach ($poiAreas as $area) { + $latLonWhereForEventLoc .= " $or ( (L.lat BETWEEN ".$area['latMin']." AND ".$area['latMax'].") AND (L.lon BETWEEN ".$area['lonMin']." AND ".$area['lonMax'].") ) "; + $latLonWhereForMembLoc .= " $or ( (I.lat BETWEEN ".$area['latMin']." AND ".$area['latMax'].") AND (I.lon BETWEEN ".$area['lonMin']." AND ".$area['lonMax'].") ) "; + $or = 'OR'; + } + $latLonWhereForEventLoc .= ')'; + $latLonWhereForMembLoc .= ')'; + + } + + $sql = " + SELECT E.id, + L.id AS loc_id, + L.lat, + L.lon, + E.name, + L.name AS loc_name, + L.address AS addr1, + '' AS addr2, + C.name AS city, + L.state, + L.zip, + E.contact_phone AS phone, + E.contact_email AS email, + E.url, + R.name AS region, + E.descr, + E.intro AS short_descr + FROM ".GLM_MEMBERS_EVENTS_PLUGIN_DB_PREFIX."events E, + ".GLM_MEMBERS_EVENTS_PLUGIN_DB_PREFIX."locations L, + ".GLM_MEMBERS_PLUGIN_DB_PREFIX."cities C, + ".GLM_MEMBERS_PLUGIN_DB_PREFIX."regions R + WHERE E.status = 10 + AND L.event = E.id + AND C.id = L.city + AND R.id = L.region + AND ( + SELECT MIN(start_time) + FROM ".GLM_MEMBERS_EVENTS_PLUGIN_DB_PREFIX."times + WHERE event = E.id + AND active + ) < '$dateFrom' + AND ( + SELECT MAX(end_time) + FROM ".GLM_MEMBERS_EVENTS_PLUGIN_DB_PREFIX."times + WHERE event = E.id + AND active + ) > '$dateTo' + $latLonWhereForEventLoc + $textWhere + + ;"; + $eventsEventLocations = $this->wpdb->get_results($sql, ARRAY_A); + + $sql = " + SELECT E.id, + L.id AS loc_id, + L.lat, + L.lon, + E.name, + L.name AS loc_name, + L.address AS addr1, + '' AS addr2, + C.name AS city, + L.state, + L.zip, + E.contact_phone AS phone, + E.contact_email AS email, + E.url, + R.name AS region, + E.descr, + E.intro AS short_descr + FROM ".GLM_MEMBERS_EVENTS_PLUGIN_DB_PREFIX."events E, + ".GLM_MEMBERS_EVENTS_PLUGIN_DB_PREFIX."locations L, + ".GLM_MEMBERS_PLUGIN_DB_PREFIX."cities C, + ".GLM_MEMBERS_PLUGIN_DB_PREFIX."regions R + WHERE E.status = 10 + AND L.event = E.id + AND C.id = L.city + AND R.id = L.region + AND L.lat < $latMax + AND L.lat > $latMin + AND L.lon < $lonMax + AND L.lon > $lonMin + LIMIT $limit + "; + + $latlontest = $this->wpdb->get_results($sql, ARRAY_A); + + + $sql = " + SELECT M.id, + M.name, + M.member_slug, + M.featured, + MI.member as member_info_id, + MI.addr1, + MI.addr2, + MI.city, + MI.state, + MI.country, + MI.zip, + MI.lat, + MI.lon, + MI.region, + MI.county, + MI.phone, + MI.url, + MI.email, + MI.logo + FROM ".GLM_MEMBERS_PLUGIN_DB_PREFIX."members M, + ".GLM_MEMBERS_PLUGIN_DB_PREFIX."member_info MI + WHERE M.id = MI.member + AND MI.status = 10 + AND MI.lat < $latMax + AND MI.lat > $latMin + AND MI.lon < $lonMax + AND MI.lon > $lonMin + LIMIT $limit + "; + $membertest = $this->wpdb->get_results($sql, ARRAY_A); + + // $sql = " + // SELECT L.id, L.event + // FROM ".GLM_MEMBERS_EVENTS_PLUGIN_DB_PREFIX."locations L + // LIMIT 3 + // "; + // $locationtest = $this->wpdb->get_results($sql, ARRAY_A); + + //$test = json_encode($request['latMax']); + // $response['locationtest'] = $locationtest; + $response['membertest'] = $membertest; + $response['latlontest'] = $latlontest; $response['params'] = $this->params; $response['poiData'] = $poiData; $response['poiAreas'] = $poiAreas; + $response['eventsEventLocations'] = $eventsEventLocations; return rest_ensure_response( $response ); //return rest_ensure_response( get_object_vars ( (object) $response ) ); @@ -144,6 +305,7 @@ class GLMA_POI_Rest_Controller } + public function get_collection_params(){ return array( 'page' => array( -- 2.17.1