From 92f2e11a300e15e67b5f8281ea2afb9bd8f938f4 Mon Sep 17 00:00:00 2001 From: Steve Sutton Date: Thu, 8 Nov 2018 10:49:42 -0500 Subject: [PATCH] Updates for interactive map Apply bounds if in shortcode. Import for county field from the old city table. --- js/leaflet-area-map.js | 32 +++ models/admin/management/import.php | 4 + models/admin/management/import/countyCity.php | 191 ++++++++++++++++++ models/front/members/interactivemap.php | 14 +- views/admin/management/import.html | 3 + views/admin/management/import/countyCity.html | 17 ++ 6 files changed, 259 insertions(+), 2 deletions(-) create mode 100644 models/admin/management/import/countyCity.php create mode 100644 views/admin/management/import/countyCity.html diff --git a/js/leaflet-area-map.js b/js/leaflet-area-map.js index 2b8a430e..a25ef187 100644 --- a/js/leaflet-area-map.js +++ b/js/leaflet-area-map.js @@ -270,6 +270,22 @@ var GlmMap = { var lat = markers[i].lat; var lng = markers[i].lon; + // Check if there's bounds + if ( glm_lat_bounds ) { + var latbounds = glm_lat_bounds.split(','); + if ( parseFloat(lat) < parseFloat(latbounds[0]) || parseFloat(lat) > parseFloat(latbounds[1]) ) { + // out of bounds + continue; + } + } + if ( glm_lon_bounds ) { + var lonbounds = glm_lon_bounds.split(','); + if ( parseFloat(lng) < parseFloat(lonbounds[0]) || parseFloat(lng) > parseFloat(lonbounds[1]) ) { + // out of bounds + continue; + } + } + var point = new google.maps.LatLng( parseFloat(lat), parseFloat(lng) @@ -303,6 +319,22 @@ var GlmMap = { for (i = 0; i < markers.length; i++) { var lat = markers[i].lat; var lng = markers[i].lon; + + // Check if there's bounds + if ( glm_lat_bounds ) { + var latbounds = glm_lat_bounds.split(','); + if ( parseFloat(lat) < parseFloat(latbounds[0]) || parseFloat(lat) > parseFloat(latbounds[1]) ) { + // out of bounds + continue; + } + } + if ( glm_lon_bounds ) { + var lonbounds = glm_lon_bounds.split(','); + if ( parseFloat(lng) < parseFloat(lonbounds[0]) || parseFloat(lng) > parseFloat(lonbounds[1]) ) { + // out of bounds + continue; + } + } var leafletMarker = L.marker([lat, lng]) .addTo(GlmMap.markerAreaGroup); } diff --git a/models/admin/management/import.php b/models/admin/management/import.php index f8c4b9d1..9998b556 100644 --- a/models/admin/management/import.php +++ b/models/admin/management/import.php @@ -144,6 +144,10 @@ class GlmMembersAdmin_management_import require GLM_MEMBERS_PLUGIN_PATH.'/models/admin/management/import/mailingAddresses.php'; break; + case 'importCountyCity': + require GLM_MEMBERS_PLUGIN_PATH.'/models/admin/management/import/countyCity.php'; + break; + case 'images': require GLM_MEMBERS_PLUGIN_PATH.'/models/admin/management/import/memberImages.php'; break; diff --git a/models/admin/management/import/countyCity.php b/models/admin/management/import/countyCity.php new file mode 100644 index 00000000..3cb9908c --- /dev/null +++ b/models/admin/management/import/countyCity.php @@ -0,0 +1,191 @@ + $dbServer, 'problem' => false); +if (!$dbServer || $dbServer == '' || $dbServer != trim($_REQUEST['dbServer'])) { + $templateData['dbServer']['problem'] = 'Server name or IP address was not provided or contained invalid characters.'; + $failure = true; +} + +// Check database port # +$dbPort = preg_replace("/[^0-9]+/", "", trim($_REQUEST['dbPort'])); +$templateData['dbPort'] = array('value' => $dbPort, 'problem' => false); +if (!$dbPort || $dbPort == '' || $dbPort != trim($_REQUEST['dbPort'])) { + $templateData['dbPort']['problem'] = 'Server port was not provided or is not a valid nummber.'; + $failure = true; +} + +// Check database name +$dbName = preg_replace("/[^a-zA-Z0-9_]+/", "", trim($_REQUEST['dbName'])); +$templateData['dbName'] = array('value' => $dbName, 'problem' => false); +if (!$dbName || $dbName == '' || $dbName != trim($_REQUEST['dbName'])) { + $templateData['dbName']['problem'] = 'Database name was not provided or is not valid for Postgres.'; + $failure = true; +} + +// Check database user +$dbUser = preg_replace("/[^a-zA-Z0-9_]+/", "", trim($_REQUEST['dbUser'])); +$templateData['dbUser'] = array('value' => $dbUser, 'problem' => false); +if (!$dbUser || $dbUser == '' || $dbUser != trim($_REQUEST['dbUser'])) { + $templateData['dbUser']['problem'] = 'Database user was not provided or is not valid for Postgres.'; + $failure = true; +} + +if ($failure) { + $templateData['genError'] = 'There was a problem with the database connection information you provided. See below for specific instructions.'; +} + +/* + * Determine if source database is sane + */ + +// Connect to database +if (!$failure) { + $connString = "host=$dbServer port=$dbPort dbname=$dbName user=$dbUser"; + $db = @pg_connect($connString); + if (!$db) { + + $err = error_get_last(); + $templateData['genError'] = 'There was a problem connecting to the database. The error message was...
'.$err['message']; + $failure = true; + + } +} + +// Determine if the members schema exists +if (!$failure) { + $sql = " + SELECT EXISTS + ( + SELECT 1 + FROM information_schema.schemata AS exists + WHERE schema_name = 'members' + ) AS isMembers + ;"; + $res = pg_query($db, $sql); + if (pg_fetch_result($res, 0, 'isMembers') == 'f') { + $templateData['genError'] = 'The "members" schema was not found! Is this the right database?'; + $failure = true; + } +} + +/* + * Load data from source database + */ + +// Attempt to get member base data +if (!$failure) { + $sql = " + SELECT C.city_id,R.region_name,C.city_name + FROM members.city C LEFT OUTER JOIN members.region R ON ( C.region_id = R.region_id ) + WHERE C.region_id is not null + ORDER BY C.city_name"; + $res = pg_query($db, $sql); + $rows = pg_num_rows($res); + if ( $rows == 0 ) { + $templateData['genError'] = 'There does not appear to be any cities listed in this database!'; + $failure = true; + } else { + $cities = pg_fetch_all($res); + if ( count( $cities ) != $rows ) { + $notice = pg_last_notice( $res ); + $templateData['genError'] = 'While reading base member data, we did not receive the expected number of cities! '; + if ( $notice ) { + $templateData['genError'] .= 'Perhaps the following message will help.
'.$notice; + } + $failure = true; + } + } +} + +/* + * Find cities for the Counties + */ +$notFoundList = array(); +$countyNames = array(); +$numbMembersFound = 0; +$numbCitiesNotFound = 0; + +if ( !$failure ) { + // Grab all of the county id's + $sql = " + SELECT * + FROM " . GLM_MEMBERS_PLUGIN_DB_PREFIX . "counties + ORDER BY name"; + $counties = $this->wpdb->get_results( $sql, ARRAY_A ); + foreach ( $counties as $county ) { + $countyNames[$county['name']] = $county['id']; + } + // echo '
$countyNames: ' . print_r( $countyNames, true ) . '
'; + foreach ( $cities as $city ) { + // grab the new city id searching the name field. + $newCityID = $this->wpdb->get_var( + $this->wpdb->prepare( + "SELECT id + FROM " . GLM_MEMBERS_PLUGIN_DB_PREFIX . "cities + WHERE name = %s", + $city['city_name'] + ) + ); + // echo '
$newCityID: ' . print_r( $newCityID, true ) . '
'; + if ( !$newCityID ) { + $notFoundList[] = $city['city_name']; + $numbCitiesNotFound++; + } else { + $countyId = $countyNames[$city['region_name']]; + // echo '
$countyId: ' . print_r( $countyId, true ) . '
'; + if ( !$countyId ) { + // need to add the cienty name + + // Clean up city name + $cName = trim(filter_var($city['region_name'])); + + // Try to add the city + // require_once GLM_MEMBERS_PLUGIN_CLASS_PATH.'/data/dataCities.php'; + // $Cities = new GlmDataCities($this->wpdb, $this->config); + // $cID = $Cities->addCity($cName); + // + // // If we got a city id back + // if (is_int($cID) && $cID > 0) { + // $countyId = $cID; + // } + } + if ( $countyId ) { + $this->wpdb->update( + GLM_MEMBERS_PLUGIN_DB_PREFIX."cities", + array( 'county' => $countyId ), + array( 'id' => $newCityID ), + array( '%d' ), + array( '%d' ) + + ); + $numbCitiesFound++; + } + } + } +} + +// If everything is OK, make data available to the template +if (!$failure) { + + $templateData['numbCities'] = count($cities); + $templateData['notFoundList'] = $notFoundList; + $templateData['numbCitiesFound'] = $numbCitiesFound; + $templateData['numbCitiesNotFound'] = $numbCitiesNotFound; + $requestedView = 'import/countyCity.html'; + +} else { + $requestedView = 'import.html'; +} + diff --git a/models/front/members/interactivemap.php b/models/front/members/interactivemap.php index e0d61a91..cd4b9ea3 100644 --- a/models/front/members/interactivemap.php +++ b/models/front/members/interactivemap.php @@ -208,7 +208,12 @@ class GlmMembersFront_members_interactivemap //extends GlmDataMemberInfo { return $this->wpdb->get_results( "SELECT * - FROM " . GLM_MEMBERS_PLUGIN_DB_PREFIX . "counties", + FROM " . GLM_MEMBERS_PLUGIN_DB_PREFIX . "counties + WHERE id IN ( + SELECT distinct( county ) + FROM " . GLM_MEMBERS_PLUGIN_DB_PREFIX . "member_info + WHERE status = 10 + AND (county IS NOT NULL OR county != 0 ) )", ARRAY_A ); } @@ -217,7 +222,12 @@ class GlmMembersFront_members_interactivemap //extends GlmDataMemberInfo { return $this->wpdb->get_results( "SELECT * - FROM " . GLM_MEMBERS_PLUGIN_DB_PREFIX . "cities", + FROM " . GLM_MEMBERS_PLUGIN_DB_PREFIX . "cities + WHERE id IN ( + SELECT distinct( city ) + FROM " . GLM_MEMBERS_PLUGIN_DB_PREFIX . "member_info + WHERE status = 10 + AND (county IS NOT NULL OR county != 0 ) )", ARRAY_A ); } diff --git a/views/admin/management/import.html b/views/admin/management/import.html index 5ce7b9f4..3c4db075 100644 --- a/views/admin/management/import.html +++ b/views/admin/management/import.html @@ -21,6 +21,7 @@ Import old member IDs only
Import member Clicks and Views
Import member Mailing Addresses
+ Import Counties to City Table
List old/new member IDs
@@ -144,6 +145,8 @@ // if doing member data import if (selected == 'members') { // do nothing + } else if (selected == 'importCountyCity') { + $('.for-member-import').addClass('glm-hidden'); } else if (selected == 'importOldMemberIds') { $('.for-member-import').addClass('glm-hidden'); } else if (selected == 'importClicksViews') { diff --git a/views/admin/management/import/countyCity.html b/views/admin/management/import/countyCity.html new file mode 100644 index 00000000..59344852 --- /dev/null +++ b/views/admin/management/import/countyCity.html @@ -0,0 +1,17 @@ +{include file='admin/management/header.html'} + +

Import Counties into City Table - Import Results

+ + + + +
Number of Cities{$numbCities}
Number of Cities Found{$numbCitiesFound}
Number of Cities Not Found{$numbCitiesNotFound}
+ + + {foreach $notFoundList as $n} + + {/foreach} +
Cities not found in new database
{$n}
+ + +{include file='admin/footer.html'} -- 2.17.1