From f9b074644c445b71b893d38a924db17964769d0e Mon Sep 17 00:00:00 2001
From: Steve Sutton <steve@gaslightmedia.com>
Date: Mon, 4 Feb 2019 16:46:48 -0500
Subject: [PATCH] Update query for cities and create correct one for regions
 for event search.

The city and region search should only show the events cities or event
regions (including member locations).
---
 models/front/events/list.php | 38 +++++++++++++++++++++++++++++-------
 1 file changed, 31 insertions(+), 7 deletions(-)

diff --git a/models/front/events/list.php b/models/front/events/list.php
index 58a6384..9e48028 100644
--- a/models/front/events/list.php
+++ b/models/front/events/list.php
@@ -76,17 +76,41 @@ class GlmMembersFront_events_list extends GlmMembersFront_events_baseAction
         $this->notInCat  = array();
         $pdfCategoryList = '';
 
-        $defaultViewFile = ( $this->config['settings']['default_agenda_view'] ) ? $this->config['settings']['default_agenda_view'] . '.html' : 'agenda.html';
+        $defaultViewFile = ( $this->config['settings']['default_agenda_view'] )
+            ? $this->config['settings']['default_agenda_view'] . '.html'
+            : 'agenda.html';
 
         // Get list cities to search by
-        require_once GLM_MEMBERS_PLUGIN_CLASS_PATH . '/data/dataCities.php';
-        $cities = new GlmDataCities( $this->wpdb, $this->config );
-        $cityData = $cities->getList( null, 'name' );
+        $citySql = "
+        SELECT C1.id,C1.name
+          FROM " . GLM_MEMBERS_PLUGIN_DB_PREFIX . "cities C1
+               LEFT OUTER JOIN " . GLM_MEMBERS_EVENTS_PLUGIN_DB_PREFIX . "locations EL ON ( EL.city = C1.id )
+               LEFT OUTER JOIN " . GLM_MEMBERS_EVENTS_PLUGIN_DB_PREFIX . "events E ON ( E.id = EL.event )
+         WHERE E.use_member_location <> true
+        UNION
+        SELECT C2.id,C2.name
+          FROM " . GLM_MEMBERS_PLUGIN_DB_PREFIX . "cities C2
+               LEFT OUTER JOIN " . GLM_MEMBERS_PLUGIN_DB_PREFIX . "member_info MI ON ( MI.city = C2.id )
+               LEFT OUTER JOIN " . GLM_MEMBERS_EVENTS_PLUGIN_DB_PREFIX . "events E ON ( E.ref_dest = MI.member )
+         WHERE E.use_member_location = true
+        ORDER BY name";
+        $cityData = $this->wpdb->get_results( $citySql, ARRAY_A );
 
         // Get list of regions to search by
-        require_once GLM_MEMBERS_PLUGIN_CLASS_PATH . '/data/dataRegions.php';
-        $regions = new GlmDataRegions( $this->wpdb, $this->config );
-        $regionData = $regions->getList( null, 'name' );
+        $regionSql = "
+        SELECT C1.id,C1.name
+          FROM " . GLM_MEMBERS_PLUGIN_DB_PREFIX . "regions C1
+               LEFT OUTER JOIN " . GLM_MEMBERS_EVENTS_PLUGIN_DB_PREFIX . "locations EL ON ( EL.region = C1.id )
+               LEFT OUTER JOIN " . GLM_MEMBERS_EVENTS_PLUGIN_DB_PREFIX . "events E ON ( E.id = EL.event )
+         WHERE E.use_member_location <> true
+        UNION
+        SELECT C2.id,C2.name
+          FROM " . GLM_MEMBERS_PLUGIN_DB_PREFIX . "regions C2
+               LEFT OUTER JOIN " . GLM_MEMBERS_PLUGIN_DB_PREFIX . "member_info MI ON ( MI.region = C2.id )
+               LEFT OUTER JOIN " . GLM_MEMBERS_EVENTS_PLUGIN_DB_PREFIX . "events E ON ( E.ref_dest = MI.member )
+         WHERE E.use_member_location = true
+        ORDER BY name";
+        $regionData = $this->wpdb->get_results( $regionSql, ARRAY_A );
 
         // If there's a pageslug as attribute then update the pageSlug
         if ( isset ( $actionData['request']['pageslug'] ) ) {
-- 
2.17.1