From 0cec77105ac8ebb2c948fb9149dbdb98e20fde34 Mon Sep 17 00:00:00 2001 From: Steve Sutton Date: Fri, 21 Apr 2017 12:59:05 -0400 Subject: [PATCH] Adding import script for trail maps. This will import the custom fields and counties. --- models/admin/management/import.php | 5 + models/admin/management/import/members.php | 5 +- .../management/import/trailmapsImport.php | 218 ++++++++++++++++++ views/admin/management/import.html | 2 +- .../management/import/trailmapsImport.html | 15 ++ .../memberInfo/editProfileDescription.html | 4 +- 6 files changed, 244 insertions(+), 5 deletions(-) create mode 100644 models/admin/management/import/trailmapsImport.php create mode 100644 views/admin/management/import/trailmapsImport.html diff --git a/models/admin/management/import.php b/models/admin/management/import.php index 1a0682c4..a1f15b5e 100644 --- a/models/admin/management/import.php +++ b/models/admin/management/import.php @@ -165,6 +165,11 @@ class GlmMembersAdmin_management_import case 'importBrewbakers': require GLM_MEMBERS_PLUGIN_PATH.'/models/admin/management/import/brewbakersImport.php'; break; + + case 'importTrailMaps': + require GLM_MEMBERS_PLUGIN_PATH.'/models/admin/management/import/trailmapsImport.php'; + break; + case 'brewbakersBrands': require GLM_MEMBERS_PLUGIN_PATH.'/models/admin/management/import/brewbakersBrands.php'; break; diff --git a/models/admin/management/import/members.php b/models/admin/management/import/members.php index e6f9c865..ab7db7dc 100644 --- a/models/admin/management/import/members.php +++ b/models/admin/management/import/members.php @@ -224,6 +224,7 @@ if (!$failure) { // Read in member/credit card cross-reference table $numbCcardMembers = 0; +$membCcard = array(); if (!$failure) { $sql = " SELECT * @@ -810,7 +811,7 @@ if (!$failure) { $ccBits += pow(2, $b); } } - + $this->wpdb->show_errors(); // Insert Member Information Record $res = $this->wpdb->insert( GLM_MEMBERS_PLUGIN_DB_PREFIX.'member_info', @@ -823,7 +824,7 @@ if (!$failure) { 'short_descr' => $short_descr, 'addr1' => $val['street'], 'addr2' => '', - 'city' => $city[$val['city_id']]['new_id'], + 'city' => ( (isset($city[$val['city_id']]['new_id']) && $city[$val['city_id']]['new_id']) ? $city[$val['city_id']]['new_id']: 0 ), 'state' => $state[$val['state_id']]['state_abb'], 'country' => 'US', 'zip' => $val['zip'], diff --git a/models/admin/management/import/trailmapsImport.php b/models/admin/management/import/trailmapsImport.php new file mode 100644 index 00000000..82e3a1c6 --- /dev/null +++ b/models/admin/management/import/trailmapsImport.php @@ -0,0 +1,218 @@ +'.$err['message']; + $failure = true; +} +// Reset the custom field data table. +$this->wpdb->query("DELETE FROM " . GLM_MEMBERS_FIELDS_PLUGIN_DB_PREFIX . "custom_field_data"); +$this->wpdb->query("DELETE FROM " . GLM_MEMBERS_PLUGIN_DB_PREFIX . "counties"); + +function getMemberInfoIdFromOldMemberId( $wpdb, $oldMemberId ) +{ + return $wpdb->get_var( + $wpdb->prepare( + "SELECT id + FROM " . GLM_MEMBERS_PLUGIN_DB_PREFIX . "member_info + WHERE member IN ( + SELECT id + FROM " . GLM_MEMBERS_PLUGIN_DB_PREFIX . "members + WHERE old_member_id = %d)", + $oldMemberId + ) + ); +} +function getCustomFieldId( $wpdb, $name ) +{ + return $wpdb->get_var( + $wpdb->prepare( + "SELECT id + FROM " . GLM_MEMBERS_FIELDS_PLUGIN_DB_PREFIX . "custom_fields + WHERE field_name = %s", + $name + ) + ); +} +$customFieldIds = array( + 'reviewed' => getCustomFieldId( $this->wpdb, 'Reviewed' ), + 'distance' => getCustomFieldId( $this->wpdb, 'Distance' ), + 'trailtype' => getCustomFieldId( $this->wpdb, 'Type' ), + 'terrain' => getCustomFieldId( $this->wpdb, 'Terrain' ), + 'difficulty' => getCustomFieldId( $this->wpdb, 'Difficulty' ), + 'nearest_city' => getCustomFieldId( $this->wpdb, 'Nearest City or Town' ), + 'guide' => getCustomFieldId( $this->wpdb, 'Trail Guide' ), + 'facilities' => getCustomFieldId( $this->wpdb, 'Facilities' ), + 'hours' => getCustomFieldId( $this->wpdb, 'Hours & Fees' ), + 'directions' => getCustomFieldId( $this->wpdb, 'Directions' ), + 'information' => getCustomFieldId( $this->wpdb, 'Information' ), +); +//echo '
$customFieldIds: ' . print_r( $customFieldIds, true ) . '
'; +function addCustomFieldData( $wpdb, $customFieldIds, $ref_dest, $fieldName, $fieldValue ) +{ + // check the $fieldValue for t or f + if ( $fieldValue ) { + if ( $fieldValue == 't' ) { + $fieldValue = 'Yes'; + } elseif ( $fieldValue == 'f' ) { + $fieldValue = 'No'; + } + } + $wpdb->insert( + GLM_MEMBERS_FIELDS_PLUGIN_DB_PREFIX . 'custom_field_data', + array( + 'field_id' => $customFieldIds[$fieldName], + 'ref_dest' => $ref_dest, + 'field_data' => ($fieldValue) ? $fieldValue: '', + ), + array( '%d', '%d', '%s' ) + ); +} + +// Read in all counties +if (!$failure) { + $sql = " + SELECT * + FROM members.county + ;"; + $res = pg_query($db, $sql); + $rows = pg_num_rows($res); + if ($rows == 0) { + $templateData['genError'] = 'There does not appear to be any counties listed in this database!'; + $failure = true; + } else { + $tmp = pg_fetch_all($res); + if (count($tmp) != $rows) { + $notice = pg_last_notice($res); + $templateData['genError'] = 'While reading county data, we did not receive the expected number of counties! '; + if ($notice) { + $templateData['genError'] .= 'Perhaps the following message will help.
'.$notice; + } + $failure = true; + } else { + + // Since there was no problem, we'll post-process the cities into an array indexed by city_id + $county = array(); + + // Reprocess into array indexed by state_id + foreach ($tmp as $x) { + $county[$x['county_id']] = $x; + } + } + } +} + +if (!$failure) { + // Import Counties + while (list ($key, $val) = each ($county) ) { + + $res = $this->wpdb->insert( + GLM_MEMBERS_PLUGIN_DB_PREFIX.'counties', + array( + 'name' => $val['county_name'], + 'descr' => '', + 'short_descr' => '' + ), + array( + '%s', + '%s', + '%s' + ) + ); + $county[$key]['new_id'] = $this->wpdb->insert_id; + + } +} + +if ( !$failure ) { + $sql = " + SELECT member_id,county,reviewed,distance,trailtype,terrain,difficulty, + nearest_city,guide,facilities,hours,directions,information + 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 member files listed in this database!'; + $failure = true; + } else { + $trails = pg_fetch_all( $res ); + if ( count( $trails ) != $rows ) { + $notice = pg_last_notice( $res ); + $templateData['genError'] = 'While reading base trails data, we did not receive the expected number of trails! '; + if ( $notice ) { + $templateData['genError'] .= 'Perhaps the following message will help.
'.$notice; + } + $failure = true; + } + } +} +//echo '
$trails: ' . print_r( $trails, true ) . '
'; +//echo '
$customFieldIds: ' . print_r( $customFieldIds, true ) . '
'; +//exit; +$test = false;//true; +if (!$failure) { + // Import Members + while (list ($key, $val) = each ($trails) ) { + // Grab the new member info id from the old member_id field. + $infoID = getMemberInfoIdFromOldMemberId( $this->wpdb, $val['member_id'] ); + if ( !$infoID ) { + echo '
$infoID: ' . print_r( $infoID, true ) . '
'; + echo '
$val[member_id]: ' . print_r( $val['member_id'], true ) . '
'; + } + + if ( $infoID && !$test ) { + // Update the county field for the member info record + $res = $this->wpdb->update( + GLM_MEMBERS_PLUGIN_DB_PREFIX . 'member_info', + array( + 'county' => (isset($county[$val['county']]) ? $county[$val['county']]['new_id']: 0) + ), + array('id' => $infoID ), + array( '%d' ), + array( '%d' ) + ); + // Add Custom Fields + addCustomFieldData( $this->wpdb, $customFieldIds, $infoID, 'reviewed', $val['reviewed'] ); + addCustomFieldData( $this->wpdb, $customFieldIds, $infoID, 'distance', $val['distance'] ); + addCustomFieldData( $this->wpdb, $customFieldIds, $infoID, 'trailtype', $val['trailtype'] ); + addCustomFieldData( $this->wpdb, $customFieldIds, $infoID, 'terrain', $val['terrain'] ); + addCustomFieldData( $this->wpdb, $customFieldIds, $infoID, 'difficulty', $val['difficulty'] ); + addCustomFieldData( $this->wpdb, $customFieldIds, $infoID, 'nearest_city', $val['nearest_city'] ); + addCustomFieldData( $this->wpdb, $customFieldIds, $infoID, 'guide', $val['guide'] ); + addCustomFieldData( $this->wpdb, $customFieldIds, $infoID, 'facilities', $val['facilities'] ); + addCustomFieldData( $this->wpdb, $customFieldIds, $infoID, 'hours', $val['hours'] ); + addCustomFieldData( $this->wpdb, $customFieldIds, $infoID, 'directions', $val['directions'] ); + addCustomFieldData( $this->wpdb, $customFieldIds, $infoID, 'information', $val['information'] ); + } + + } + //echo '
$member: ' . print_r( $member, true ) . '
'; +} + +$haveCatImportIssues = false; +$catImportIssues = ''; +// If everything is OK, make data available to the template +if (!$failure) { + + $templateData['numbMembers'] = count($trails); +} + + +if ($failure) { + $requestedView = 'import.html'; +} else { + $requestedView = 'import/trailmapsImport.html'; +} diff --git a/views/admin/management/import.html b/views/admin/management/import.html index d139ff5c..af3b5131 100644 --- a/views/admin/management/import.html +++ b/views/admin/management/import.html @@ -20,7 +20,7 @@ Import member Clicks and Views
Import member Mailing Addresses
List old/new member IDs
- Import Brewbakers + Import Trail Maps diff --git a/views/admin/management/import/trailmapsImport.html b/views/admin/management/import/trailmapsImport.html new file mode 100644 index 00000000..869a3b96 --- /dev/null +++ b/views/admin/management/import/trailmapsImport.html @@ -0,0 +1,15 @@ +{include file='admin/management/header.html'} + +

Custom Field Import: Review Results.

+{if isset($genError)} +

+

Oops!

+

{$genError}

+

+{/if} + +
+Updated: {$numbMembers} records
+
+ +{include file='admin/footer.html'} diff --git a/views/admin/member/memberInfo/editProfileDescription.html b/views/admin/member/memberInfo/editProfileDescription.html index 8819b1ac..8c7378f9 100644 --- a/views/admin/member/memberInfo/editProfileDescription.html +++ b/views/admin/member/memberInfo/editProfileDescription.html @@ -3,7 +3,7 @@

Short Description:

- +
{if $memberInfo.fieldFail.short_descr}

{$memberInfo.fieldFail.short_descr}

{/if}
@@ -24,4 +24,4 @@ {if $memberInfo.fieldFail.descr}

{$memberInfo.fieldFail.descr}

{/if}
- \ No newline at end of file + -- 2.17.1