From 645c08b3ca669da2ac088d2e841a573b5f0ff065 Mon Sep 17 00:00:00 2001 From: Steve Sutton Date: Thu, 14 Feb 2019 06:05:53 -0500 Subject: [PATCH] Add County to even locations. Add new db fields for county. Add new db fields for search options. WIP for event form. --- classes/data/dataLocations.php | 12 + classes/data/dataManagement.php | 15 + index.php | 2 +- models/admin/events/list.php | 2 +- models/front/events/list.php | 56 +++ ...0.1.13.sql => create_database_V0.1.14.sql} | 9 +- setup/databaseScripts/dbVersions.php | 1 + .../update_database_V0.1.14.sql | 33 ++ views/admin/events/editLocation.html | 362 ++++++++++-------- views/admin/management/events.html | 2 + views/front/events/searchForm.html | 32 +- 11 files changed, 343 insertions(+), 183 deletions(-) rename setup/databaseScripts/{create_database_V0.1.13.sql => create_database_V0.1.14.sql} (97%) create mode 100644 setup/databaseScripts/update_database_V0.1.14.sql diff --git a/classes/data/dataLocations.php b/classes/data/dataLocations.php index 4f3066e..34908ed 100644 --- a/classes/data/dataLocations.php +++ b/classes/data/dataLocations.php @@ -174,6 +174,18 @@ class GlmDataEventsLocations extends GlmDataAbstract 'use' => 'a' ), + // County - References main plugin county table + 'county' => array ( + 'field' => 'county', + 'type' => 'pointer', + 'p_table' => GLM_MEMBERS_PLUGIN_DB_PREFIX . 'counties', + 'p_field' => 'name', + 'p_orderby' => 'name', + 'p_blank' => false, + 'force_list' => true, + 'use' => 'a' + ), + // Country 'country' => array ( 'field' => 'country', diff --git a/classes/data/dataManagement.php b/classes/data/dataManagement.php index 6fd7e2f..9ea1580 100644 --- a/classes/data/dataManagement.php +++ b/classes/data/dataManagement.php @@ -297,6 +297,14 @@ class GlmDataEventsManagement extends GlmDataAbstract 'use' => 'a' ), + // Show category in search form + 'show_search_category' => array( + 'field' => 'show_search_category', + 'type' => 'checkbox', + 'default' => false, + 'use' => 'a' + ), + // Show region in search form 'show_search_region' => array( 'field' => 'show_search_region', @@ -305,6 +313,13 @@ class GlmDataEventsManagement extends GlmDataAbstract 'use' => 'a' ), + // Show county in search form + 'show_search_county' => array( + 'field' => 'show_search_county', + 'type' => 'checkbox', + 'default' => false, + 'use' => 'a' + ), // Turn on required fields for Michigan.org 'michigan_org_requirements' => array( 'field' => 'michigan_org_requirements', diff --git a/index.php b/index.php index 5a028cb..4d13ea4 100755 --- a/index.php +++ b/index.php @@ -44,7 +44,7 @@ if (!defined('ABSPATH')) { * version from this plugin. */ define('GLM_MEMBERS_EVENTS_PLUGIN_VERSION', '1.7.19'); -define('GLM_MEMBERS_EVENTS_PLUGIN_DB_VERSION', '0.1.13'); +define('GLM_MEMBERS_EVENTS_PLUGIN_DB_VERSION', '0.1.14'); // This is the minimum version of the GLM Members DB plugin require for this plugin. define('GLM_MEMBERS_EVENTS_PLUGIN_MIN_MEMBERS_REQUIRED_VERSION', '2.9.15'); diff --git a/models/admin/events/list.php b/models/admin/events/list.php index 8521691..f3510ee 100644 --- a/models/admin/events/list.php +++ b/models/admin/events/list.php @@ -867,7 +867,7 @@ class GlmMembersAdmin_events_list extends GlmDataEvents // since we're doing paging, we have to break out just the events data $events = $eventsResult['list']; - if (count($events)>0) { + if ( is_array( $events ) && !empty( $events ) && count($events) > 0 ) { $haveEvents = true; } unset($eventsResult); diff --git a/models/front/events/list.php b/models/front/events/list.php index adaa457..f9b2d5e 100644 --- a/models/front/events/list.php +++ b/models/front/events/list.php @@ -59,6 +59,7 @@ class GlmMembersFront_events_list extends GlmMembersFront_events_baseAction $eventsByDate = null; $categoryId = null; $categories = array(); + $counties = array(); $amenityId = null; $amenities = array(); $memberId = null; @@ -87,12 +88,24 @@ class GlmMembersFront_events_list extends GlmMembersFront_events_baseAction 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 + AND E.id IN ( + SELECT event + FROM " . GLM_MEMBERS_EVENTS_PLUGIN_DB_PREFIX . "times ET + WHERE E.status = " . $this->config['status_numb']['Active'] . " + AND " . $this->dateRange . " + ) 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 + AND E.id IN ( + SELECT event + FROM " . GLM_MEMBERS_EVENTS_PLUGIN_DB_PREFIX . "times ET + WHERE E.status = " . $this->config['status_numb']['Active'] . " + AND " . $this->dateRange . " + ) ORDER BY name"; $cityData = $this->wpdb->get_results( $citySql, ARRAY_A ); @@ -103,12 +116,24 @@ class GlmMembersFront_events_list extends GlmMembersFront_events_baseAction 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 + AND E.id IN ( + SELECT event + FROM " . GLM_MEMBERS_EVENTS_PLUGIN_DB_PREFIX . "times ET + WHERE E.status = " . $this->config['status_numb']['Active'] . " + AND " . $this->dateRange . " + ) 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 + AND E.id IN ( + SELECT event + FROM " . GLM_MEMBERS_EVENTS_PLUGIN_DB_PREFIX . "times ET + WHERE E.status = " . $this->config['status_numb']['Active'] . " + AND " . $this->dateRange . " + ) ORDER BY name"; $regionData = $this->wpdb->get_results( $regionSql, ARRAY_A ); @@ -401,6 +426,36 @@ class GlmMembersFront_events_list extends GlmMembersFront_events_baseAction } else { $eventNameSearch = false; } + // Setup counties for search + if ( $this->config['settings']['enable_counties'] ) { + // Get list of counties to search by + $countySql = " + SELECT C1.id,C1.name + FROM " . GLM_MEMBERS_PLUGIN_DB_PREFIX . "counties C1 + LEFT OUTER JOIN " . GLM_MEMBERS_EVENTS_PLUGIN_DB_PREFIX . "locations EL ON ( EL.county = C1.id ) + LEFT OUTER JOIN " . GLM_MEMBERS_EVENTS_PLUGIN_DB_PREFIX . "events E ON ( E.id = EL.event ) + WHERE E.use_member_location <> true + AND E.id IN ( + SELECT event + FROM " . GLM_MEMBERS_EVENTS_PLUGIN_DB_PREFIX . "times ET + WHERE E.status = " . $this->config['status_numb']['Active'] . " + AND " . $this->dateRange . " + ) + UNION + SELECT C2.id,C2.name + FROM " . GLM_MEMBERS_PLUGIN_DB_PREFIX . "counties C2 + LEFT OUTER JOIN " . GLM_MEMBERS_PLUGIN_DB_PREFIX . "member_info MI ON ( MI.county = 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 + AND E.id IN ( + SELECT event + FROM " . GLM_MEMBERS_EVENTS_PLUGIN_DB_PREFIX . "times ET + WHERE E.status = " . $this->config['status_numb']['Active'] . " + AND " . $this->dateRange . " + ) + ORDER BY name"; + $countyData = $this->wpdb->get_results( $countySql, ARRAY_A ); + } $categories = $this->getCategories(); if ( isset($categories) && is_array($categories)) { @@ -750,6 +805,7 @@ class GlmMembersFront_events_list extends GlmMembersFront_events_baseAction 'amenities' => $amenities, 'memberId' => $memberId, 'cities' => $cityData, + 'counties' => $countyData, 'regions' => $regionData, 'cityId' => $cityId, 'regionId' => $regionId, diff --git a/setup/databaseScripts/create_database_V0.1.13.sql b/setup/databaseScripts/create_database_V0.1.14.sql similarity index 97% rename from setup/databaseScripts/create_database_V0.1.13.sql rename to setup/databaseScripts/create_database_V0.1.14.sql index 46f5e3a..c941c61 100644 --- a/setup/databaseScripts/create_database_V0.1.13.sql +++ b/setup/databaseScripts/create_database_V0.1.14.sql @@ -138,6 +138,7 @@ CREATE TABLE {prefix}locations ( state TINYTEXT NULL, -- Two character state abbreviation zip TINYTEXT NULL, -- ZIP/Postal code country TINYTEXT NULL, -- Country Code + county INT NULL, -- Reference to county table lat FLOAT NULL, -- Latitude of location lon FLOAT NULL, -- Longitude of location region INT NULL, -- Pointer to Region - references main plugin region table @@ -149,7 +150,11 @@ CREATE TABLE {prefix}locations ( contact_lname TINYTEXT NULL, -- Contact last name for this location (optional) contact_phone TINYTEXT NULL, -- Contact phone for this location (optional) contact_email TINYTEXT NULL, -- Contact E-Mail address (optional) - PRIMARY KEY (id) + PRIMARY KEY (id), + INDEX(event), + INDEX(city), + INDEX(county), + INDEX(region) ); ---- @@ -248,6 +253,8 @@ CREATE TABLE {prefix}management ( sort_by_featured_in_agenda BOOLEAN DEFAULT '0', -- Sort by the featured events in agenda view show_search_city BOOLEAN DEFAULT '0', -- Show city in search form show_search_region BOOLEAN DEFAULT '0', -- Show region in search form + show_search_county BOOLEAN DEFAULT '0', -- Show county in search form + show_search_category BOOLEAN DEFAULT '1', -- Show category in search form PRIMARY KEY (id) ); diff --git a/setup/databaseScripts/dbVersions.php b/setup/databaseScripts/dbVersions.php index 104236c..430fdd7 100644 --- a/setup/databaseScripts/dbVersions.php +++ b/setup/databaseScripts/dbVersions.php @@ -49,5 +49,6 @@ $glmMembersEventsDbVersions = array( '0.1.11' => array('version' => '0.1.11', 'tables' => 13, 'date' => '01/07/2019'), '0.1.12' => array('version' => '0.1.12', 'tables' => 13, 'date' => '01/30/2019'), '0.1.13' => array('version' => '0.1.13', 'tables' => 13, 'date' => '02/04/2019'), + '0.1.14' => array('version' => '0.1.14', 'tables' => 13, 'date' => '02/13/2019'), ); diff --git a/setup/databaseScripts/update_database_V0.1.14.sql b/setup/databaseScripts/update_database_V0.1.14.sql new file mode 100644 index 0000000..0e0554a --- /dev/null +++ b/setup/databaseScripts/update_database_V0.1.14.sql @@ -0,0 +1,33 @@ +-- Gaslight Media Members Database - Events Add-On +-- File Created: 02/13/19 +-- Database Version: 0.1.14 +-- Database Update From Previous Version Script +-- +-- To permit each query below to be executed separately, +-- all queries must be separated by a line with four dashes + +ALTER TABLE {prefix}locations ADD COLUMN county INT NULL; -- Reference to county table + +---- + +ALTER TABLE {prefix}management ADD COLUMN show_search_county BOOLEAN DEFAULT '0'; -- Show county in search form + +---- + +ALTER TABLE {prefix}management ADD COLUMN show_search_category BOOLEAN DEFAULT '1'; -- Show category in search form + +---- + +CREATE INDEX event ON {prefix}locations (event); + +---- + +CREATE INDEX city ON {prefix}locations (city); + +---- + +CREATE INDEX county ON {prefix}locations (county); + +---- + +CREATE INDEX region ON {prefix}locations (region); diff --git a/views/admin/events/editLocation.html b/views/admin/events/editLocation.html index f1150e4..f2dede9 100755 --- a/views/admin/events/editLocation.html +++ b/views/admin/events/editLocation.html @@ -1,5 +1,4 @@ - - +{* Location *} {if $settings.selected_map_interface == 1} @@ -10,7 +9,7 @@ {/if} - +{* Add New City Dialog Box *}
@@ -25,7 +24,7 @@ Cancel - +{* New city hidden input fields go here *} {if !$settings.use_venue_locations} @@ -35,9 +34,9 @@ @@ -78,160 +77,172 @@
- + *}
- -{if $locations} - {foreach $locations as $loc} - - - - + + + {/foreach} + {/if}
- - - - - - - - - - Address: - - - - City - - - - State: - - - - ZIP / Postal Code: - - - - - - - - - - - - - - - - - - - - - - - -
Location Name: - -
- -
- {if !$lockedToMember} - -
Add a new City
- {/if} - - -
- -
- -
Country: - -
Region: - -
Phone: - -
Web Address: - -
Email Address: - -
- 		- - - - + + - +
Location: - - - -
Map Location Using Above Address
-

- {if $settings.selected_map_interface == 1} - -

- {/if} + {* Location forms go here. *} + {if $locations} + {foreach $locations as $loc} +
+ + + + + + + + + + Address: + + + + City + + + + State: + + + + ZIP / Postal Code: + + + + + + + + + + + + + + + + + + + + + + + + + + - {if $settings.selected_map_interface == 2} - -
(map loads here)
- {/if} -

- Position: -  Lat -  Lon -   Update pointer
- You may drag the map marker to the exact location desired. Also consider selecting "Satellite" view to better locate the marker. -

-

- - -
Location Name: + +
+ +
+ {if !$lockedToMember} + {* Add new city dialog *} +
Add a new City
+ {/if} + {* City Selection *} + +
+ +
+ +
County: + +
Country: + +
Region: + +
Phone: + +
Web Address: + +
Email Address: + +
-
+ 		+ + + + + +
Location: + + {* Only doing one location per event right now + Delete Location222 + *} + +
Map Location Using Above Address
+

+ {if $settings.selected_map_interface == 1} + +

+ {/if} - {/foreach} -{/if} + {if $settings.selected_map_interface == 2} + +
(map loads here)
+ {/if} +

+ Position: +  Lat +  Lon +   Update pointer
+ You may drag the map marker to the exact location desired. Also consider selecting "Satellite" view to better locate the marker. +

+

+
+
- +{* Add/Edit Location Template *}
- {if $haveEvent} - - {/if} + {if $haveEvent} + {* required to have event id with prefix *} + {/if} @@ -257,15 +268,15 @@ City @@ -273,11 +284,11 @@ State: @@ -287,15 +298,26 @@ + + + + @@ -304,9 +326,9 @@ @@ -335,18 +357,18 @@ diff --git a/views/front/events/searchForm.html b/views/front/events/searchForm.html index 8d46e3a..a49b12f 100644 --- a/views/front/events/searchForm.html +++ b/views/front/events/searchForm.html @@ -70,16 +70,28 @@ {/if} -
- - -
- {if $settings.show_search_city || $settings.show_search_region} + {if $settings.enable_counties && $settings.show_search_county} +
+ +
+ {/if} + {if $settings.show_search_category} +
+ + +
+ {/if} + {if $settings.show_search_city || $settings.show_search_region || $settings.show_search_county}
{if !$lockedToMember} - + {* Add new city dialog *}
Add a new City
{/if} - + {* City Selection *}
County: + +
Country:
Location: - + *}
Map Location Using Address

{if $settings.selected_map_interface == 1} - + {* Leaflet Map *}

{/if} {if $settings.selected_map_interface == 2} - + {* Google Map *}
(map loads here)
{/if} diff --git a/views/admin/management/events.html b/views/admin/management/events.html index bf8b127..c7cdbc6 100644 --- a/views/admin/management/events.html +++ b/views/admin/management/events.html @@ -113,8 +113,10 @@
Front-End Event Search Form +





+