From 9688529e3637df9fc4f028be331daae75ed950b2 Mon Sep 17 00:00:00 2001 From: Chuck Scott Date: Tue, 14 Jun 2016 12:56:23 -0400 Subject: [PATCH] Added old member id import and old/new member id list --- classes/data/dataMemberInfo.php | 2 +- models/admin/ajax/newOldMemberIdsCsv.php | 94 +++++++++ models/admin/management/import.php | 15 +- .../import/displayNewOldMemberIds.php | 10 + models/admin/management/import/members.php | 13 +- .../admin/management/import/oldMemberIds.php | 193 ++++++++++++++++++ setup/validActions.php | 3 +- views/admin/management/header.html | 2 +- views/admin/management/import.html | 83 +++++--- .../import/displayNewOldMemberIds.html | 14 ++ .../admin/management/import/oldMemberIds.html | 17 ++ 11 files changed, 409 insertions(+), 37 deletions(-) create mode 100644 models/admin/ajax/newOldMemberIdsCsv.php create mode 100644 models/admin/management/import/displayNewOldMemberIds.php create mode 100644 models/admin/management/import/oldMemberIds.php create mode 100644 views/admin/management/import/displayNewOldMemberIds.html create mode 100644 views/admin/management/import/oldMemberIds.html diff --git a/classes/data/dataMemberInfo.php b/classes/data/dataMemberInfo.php index 6ccb65d4..1c782ca1 100644 --- a/classes/data/dataMemberInfo.php +++ b/classes/data/dataMemberInfo.php @@ -534,7 +534,7 @@ class GlmDataMemberInfo extends GlmDataAbstract /* - * Get Apha list of first characters in member name + * Get Alpha list of first characters in member name * for those members that have active info. * * @param string $where Where clause diff --git a/models/admin/ajax/newOldMemberIdsCsv.php b/models/admin/ajax/newOldMemberIdsCsv.php new file mode 100644 index 00000000..c2982e68 --- /dev/null +++ b/models/admin/ajax/newOldMemberIdsCsv.php @@ -0,0 +1,94 @@ + + * @license http://www.gaslightmedia.com Gaslightmedia + * @version 0.1 + */ + +/* + * This class performs the work of handling images passed to it via + * an AJAX call that goes through the WorPress AJAX Handler. + * + */ +class GlmMembersAdmin_ajax_newOldMemberIdsCsv +{ + + /** + * WordPress Database Object + * + * @var $wpdb + * @access public + */ + public $wpdb; + /** + * Plugin Configuration Data + * + * @var $config + * @access public + */ + public $config; + + /* + * Constructor + * + * This contructor sets up this model. At this time that only includes + * storing away the WordPress data object. + * + * @return object Class object + * + */ + public function __construct ($wpdb, $config) + { + + // Save WordPress Database object + $this->wpdb = $wpdb; + + // Save plugin configuration object + $this->config = $config; + + } + + /* + * Perform Model Action + * + * This modelAction takes an AJAX image upload and stores the image in the + * media/images directory of the plugin. + * + * This model action does not return, it simply does it's work then calls die(); + * + * @param $actionData + * + * Echos JSON string as response and does not return + */ + public function modelAction ($actionData = false) + { + + $members = $this->wpdb->get_results( "SELECT * FROM ".GLM_MEMBERS_PLUGIN_DB_PREFIX."members ORDER BY name", ARRAY_A ); + + header("Content-Type: text/csv"); + header("Content-Disposition: attachment; filename=file.csv"); + // Disable caching + header("Cache-Control: no-cache, no-store, must-revalidate"); // HTTP 1.1 + header("Pragma: no-cache"); // HTTP 1.0 + header("Expires: 0"); // Proxies + + // Output CSV + echo '"Member Name","Old Member ID","New Member ID"'."\r\n"; + + foreach($members as $m) { + echo '"'.$m['name'].'","'.$m['old_member_id'].'","'.$m['id'].'"'."\r\n"; + } + + die(); + + } + +} diff --git a/models/admin/management/import.php b/models/admin/management/import.php index a66442d2..06d22d8b 100644 --- a/models/admin/management/import.php +++ b/models/admin/management/import.php @@ -142,13 +142,26 @@ class GlmMembersAdmin_management_import require GLM_MEMBERS_PLUGIN_PATH.'/models/admin/management/import/memberImages.php'; + break; + + case 'importOldMemberIds': + + require GLM_MEMBERS_PLUGIN_PATH.'/models/admin/management/import/oldMemberIds.php'; + + break; + + case 'displayOldNewMemberIds': + + require GLM_MEMBERS_PLUGIN_PATH.'/models/admin/management/import/displayNewOldMemberIds.php'; + + break; + default: break; } - // Return status, suggested view, and data to controller return array( 'status' => true, diff --git a/models/admin/management/import/displayNewOldMemberIds.php b/models/admin/management/import/displayNewOldMemberIds.php new file mode 100644 index 00000000..7428e4bb --- /dev/null +++ b/models/admin/management/import/displayNewOldMemberIds.php @@ -0,0 +1,10 @@ +wpdb->get_results( "SELECT * FROM ".GLM_MEMBERS_PLUGIN_DB_PREFIX."members ORDER BY name", ARRAY_A ); + +$requestedView = 'import/displayNewOldMemberIds.html'; + + diff --git a/models/admin/management/import/members.php b/models/admin/management/import/members.php index bc67b885..ae24ea3a 100644 --- a/models/admin/management/import/members.php +++ b/models/admin/management/import/members.php @@ -975,15 +975,8 @@ if (!$failure) { if ($failure) { - return array( - 'status' => true, - 'menuItemRedirect' => 'management', - 'modelRedirect' => 'import', - 'view' => 'admin/management/import.html', - 'data' => $templateData - ); - + $requestedView = 'import.html'; +} else { + $requestedView = 'import/members.html'; } -$requestedView = 'import/members.html'; - diff --git a/models/admin/management/import/oldMemberIds.php b/models/admin/management/import/oldMemberIds.php new file mode 100644 index 00000000..ffc8fe73 --- /dev/null +++ b/models/admin/management/import/oldMemberIds.php @@ -0,0 +1,193 @@ + $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 * + FROM members.member + 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 { + $member = pg_fetch_all($res); + if (count($member) != $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; + } + } +} + +/* + * Clear old member IDs + */ +$this->wpdb->query("UPDATE ".GLM_MEMBERS_PLUGIN_DB_PREFIX."members SET old_member_id = NULL"); + +/* + * Find new member IDs for old members + */ +$notFoundList = array(); +$numbMembersFound = 0; +$numbMembersNotFound = 0; +if (!$failure) { + + // For each member in the old database, try to match to new member + foreach ( $member as $m ) { + + $oldID = $m['member_id']; + $newID = false; + + // Try matching with this name + $sql = $this->wpdb->prepare( + "SELECT id FROM ".GLM_MEMBERS_PLUGIN_DB_PREFIX."members WHERE name = %s", + $m['member_name'] + ); + $res = $this->wpdb->get_results( $sql, ARRAY_A ); + + // If there's only one match, then this should be the new ID + if ($this->wpdb->num_rows == 1) { + $newID = $res[0]['id']; + } else { + + // Try matching street address (line 1) + $sql = $this->wpdb->prepare( + "SELECT member FROM ".GLM_MEMBERS_PLUGIN_DB_PREFIX."member_info WHERE addr1 = %s AND status = %d", + $m['street'], + $this->config['status_numb']['Active'] + ); + $res = $this->wpdb->get_results( $sql, ARRAY_A ); + + // If there's only one match, this should be the active member info record for the matching member + if ($this->wpdb->num_rows == 1) { + $newID = $res[0]['member']; + } + + } + + // If we matched a member, update the member with the old id + if ($newID) { + + $numbMembersFound++; + + $this->wpdb->update( + GLM_MEMBERS_PLUGIN_DB_PREFIX."members", + array( 'old_member_id' => $m['member_id']), + array( 'id' => $newID ), + array( '%d' ), + array( '%d' ) + ); + + // Otherwise list as not found + } else { + $numbMembersNotFound++; + $notFoundList[] = $m['member_name']; + } + + } // each old member + +} + +// If everything is OK, make data available to the template +if (!$failure) { + + $templateData['numbOldMembers'] = count($member); + $templateData['notFoundList'] = $notFoundList; + $templateData['numbMembersFound'] = $numbMembersFound; + $templateData['numbMembersNotFound'] = $numbMembersNotFound; + $requestedView = 'import/oldMemberIds.html'; + +} else { + $requestedView = 'import.html'; +} + diff --git a/setup/validActions.php b/setup/validActions.php index 651e9545..53d2d5ad 100644 --- a/setup/validActions.php +++ b/setup/validActions.php @@ -33,7 +33,8 @@ $glmMembersValidActions = array( 'adminActions' => array( 'ajax' => array( - 'imageUpload' => 'glm-member-db' + 'imageUpload' => 'glm-member-db', + 'newOldMemberIdsCsv' => 'glm-member-db' ), 'dashboardWidget' => array( 'index' => 'glm-member-db' diff --git a/views/admin/management/header.html b/views/admin/management/header.html index da746808..2bda60f0 100644 --- a/views/admin/management/header.html +++ b/views/admin/management/header.html @@ -7,7 +7,7 @@ Terms and Phrases Theme Settings Development - Import Members + Members Add-Ons Hooks {foreach $addOnTabs as $a} diff --git a/views/admin/management/import.html b/views/admin/management/import.html index 27c31024..70353854 100644 --- a/views/admin/management/import.html +++ b/views/admin/management/import.html @@ -1,20 +1,6 @@ {include file='admin/management/header.html'} -

Data Import

-

- This process exports data from a Postgres legacy Gaslight Media Member DB database and imports that data into this plugin. -

-

- The process will include the following steps. -

-
    -
  1. Provide legacy database information and import member data.
    2. -
  2. Import any images.
    3. -
  3. Review results
    4. -
- - -

Data Import Step 1: Supply database access information.

+

Data Import Step 1: Supply database access information.

{if isset($genError)}

Oops!

@@ -23,13 +9,36 @@ {/if}
- - - + + + + + + + +
Select Action: + Import member data
+ Import old member IDs only
+ List old/new member IDs +
+

+ This process exports data from a Postgres legacy Gaslight Media Member DB database and imports that data into this plugin. +

+

+ The process will include the following steps. +

+
    +
  1. Provide legacy database information and import member data.
    2. +
  2. Import any images.
    3. +
  3. Review results
    4. +
+
 
+ + - + - + - + - + - +
Database Server: @@ -38,7 +47,7 @@ {/if}
Database Port #: @@ -47,7 +56,7 @@ {/if}
Database Name: @@ -56,7 +65,7 @@ {/if}
Database User: @@ -65,7 +74,7 @@ {/if}
Base Original Image URL: @@ -75,7 +84,7 @@ {/if}
Reset Database Before Import: @@ -98,4 +107,32 @@ {/if} + + + + {include file='admin/footer.html'} diff --git a/views/admin/management/import/displayNewOldMemberIds.html b/views/admin/management/import/displayNewOldMemberIds.html new file mode 100644 index 00000000..c0f2215c --- /dev/null +++ b/views/admin/management/import/displayNewOldMemberIds.html @@ -0,0 +1,14 @@ +{include file='admin/management/header.html'} + +

New & Old Member IDs

+ +

Export as CSV File

+ + + + {foreach $members as $m} + + {/foreach} +
MemberOld IDNew ID
{$m.name}{$m.old_member_id}{$m.id}
+ +{include file='admin/footer.html'} diff --git a/views/admin/management/import/oldMemberIds.html b/views/admin/management/import/oldMemberIds.html new file mode 100644 index 00000000..74f17227 --- /dev/null +++ b/views/admin/management/import/oldMemberIds.html @@ -0,0 +1,17 @@ +{include file='admin/management/header.html'} + +

Import Old Member IDs - 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