From 0be32f7c69f51a232b87b7307560602e2a7f4358 Mon Sep 17 00:00:00 2001 From: Steve Sutton Date: Thu, 29 Dec 2016 15:46:01 -0500 Subject: [PATCH] Add to member import option for getting mailing addresses. Add option from the mailing addresses from their old database. --- models/admin/management/import.php | 4 + .../management/import/mailingAddresses.php | 199 ++++++++++++++++++ views/admin/management/import.html | 43 ++-- .../management/import/mailingAddresses.html | 17 ++ 4 files changed, 243 insertions(+), 20 deletions(-) create mode 100644 models/admin/management/import/mailingAddresses.php create mode 100644 views/admin/management/import/mailingAddresses.html diff --git a/models/admin/management/import.php b/models/admin/management/import.php index dc5daf40..777d5a83 100644 --- a/models/admin/management/import.php +++ b/models/admin/management/import.php @@ -138,6 +138,10 @@ class GlmMembersAdmin_management_import require GLM_MEMBERS_PLUGIN_PATH.'/models/admin/management/import/memberClicksViews.php'; break; + case 'importMailingAddresses': + require GLM_MEMBERS_PLUGIN_PATH.'/models/admin/management/import/mailingAddresses.php'; + break; + case 'images': require GLM_MEMBERS_PLUGIN_PATH.'/models/admin/management/import/memberImages.php'; break; diff --git a/models/admin/management/import/mailingAddresses.php b/models/admin/management/import/mailingAddresses.php new file mode 100644 index 00000000..ace5a939 --- /dev/null +++ b/models/admin/management/import/mailingAddresses.php @@ -0,0 +1,199 @@ + $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 m.member_id,m.member_name,m.mailing_address,m.mailing_city,s.state_abb as mailing_state,m.mailing_zip + FROM members.member m, members.state s + WHERE m.state_id = s.state_id + AND (m.mailing_address IS NOT NULL OR m.mailing_address != '') + AND (m.mailing_city IS NOT NULL OR m.mailing_city != '') + ORDER BY member_id + ;"; + $res = pg_query($db, $sql); + $rows = pg_num_rows($res); + if ( $rows == 0 ) { + $templateData['genError'] = 'There does not appear to be any members listed in this database!'; + $failure = true; + } else { + $members = pg_fetch_all($res); + if ( count( $members ) != $rows ) { + $notice = pg_last_notice( $res ); + $templateData['genError'] = 'While reading base member data, we did not receive the expected number of members! '; + if ( $notice ) { + $templateData['genError'] .= 'Perhaps the following message will help.
'.$notice; + } + $failure = true; + } + } +} + +/* + * Find members for the mailing addresses. + */ +$notFoundList = array(); +$cityNames = array(); +$numbMembersFound = 0; +$numbMembersNotFound = 0; + +if ( !$failure ) { + // Grab all of the city id's + $sql = " + SELECT * + FROM " . GLM_MEMBERS_PLUGIN_DB_PREFIX . "cities + ORDER BY name"; + $cities = $this->wpdb->get_results( $sql, ARRAY_A ); + foreach ( $cities as $city ) { + $cityNames[$city['name']] = $city['id']; + } + foreach ( $members as $member ) { + // grab the new member id searching the old_member_id field. + $newMemberID = $this->wpdb->get_var( + $this->wpdb->prepare( + "SELECT id + FROM " . GLM_MEMBERS_PLUGIN_DB_PREFIX . "members + WHERE old_member_id = %d", + $member['member_id'] + ) + ); + if ( !$newMemberID ) { + $notFoundList[] = $member['member_name']; + $numbMembersNotFound++; + } else { + $cityId = $cityNames[$member['mailing_city']]; + if ( !$cityId ) { + // need to add the city name + + // Clean up city name + $cName = trim(filter_var($member['mailing_city'])); + + // 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) { + $cityId = $cID; + } + } + $this->wpdb->update( + GLM_MEMBERS_PLUGIN_DB_PREFIX."member_info", + array( + 'mailing_addr1' => $member['mailing_address'], + 'mailing_city' => (isset( $cityId ) ? $cityId : ''), + 'mailing_state' => $member['mailing_state'], + 'mailing_zip' => $member['mailing_zip'], + ), + array( 'id' => $newMemberID ), + array( + '%s', + '%d', + '%s', + '%s', + ), + array( '%d' ) + + ); + $numbMembersFound++; + } + } +} + +// If everything is OK, make data available to the template +if (!$failure) { + + $templateData['numbOldMembers'] = count($members); + $templateData['notFoundList'] = $notFoundList; + $templateData['numbMembersFound'] = $numbMembersFound; + $templateData['numbMembersNotFound'] = $numbMembersNotFound; + $requestedView = 'import/mailingAddresses.html'; + +} else { + $requestedView = 'import.html'; +} + diff --git a/views/admin/management/import.html b/views/admin/management/import.html index 8d28b74f..70bc714a 100644 --- a/views/admin/management/import.html +++ b/views/admin/management/import.html @@ -1,23 +1,24 @@ {include file='admin/management/header.html'} - +

Data Import Step 1: Supply database access information.

-{if isset($genError)} +{if isset($genError)}

Oops!

{$genError}

-{/if} +{/if}
- + - + @@ -34,7 +35,7 @@
  • Import any images.
  • Review results
    • - +
    Select Action: Import member data
    Import old member IDs only
    Import member Clicks and Views
    + Import member Mailing Addresses
    List old/new member IDs
     
    @@ -94,11 +95,11 @@ - + - +
    - + {if $haveImageArray}
    @@ -107,18 +108,18 @@
    {/if} - - + +

    Please Wait!

    - + - - - + + + {include file='admin/footer.html'} diff --git a/views/admin/management/import/mailingAddresses.html b/views/admin/management/import/mailingAddresses.html new file mode 100644 index 00000000..bc637fd6 --- /dev/null +++ b/views/admin/management/import/mailingAddresses.html @@ -0,0 +1,17 @@ +{include file='admin/management/header.html'} + +

    Import Mailing Addresses - Data Import Results

    + + + + +
    Number of Old Members{$numbOldMembers}
    Number of Members Found{$numbMembersFound}
    Number of Members Not Found{$numbMembersNotFound}
    + + + {foreach $notFoundList as $n} + + {/foreach} +
    Old members not found in new database
    {$n}
    + + +{include file='admin/footer.html'} -- 2.17.1