From 6e251156de22a453734ee848a38738c0f3d844ed Mon Sep 17 00:00:00 2001
From: Steve Sutton <steve@gaslightmedia.com>
Date: Fri, 8 Mar 2019 14:53:54 -0500
Subject: [PATCH] Updates for member import/updates and verification

Adding way to verify lat lon data from old servers.
---
 models/admin/ajax/memberCountCheck.php | 166 +++++++++++++++++++++++++
 models/admin/ajax/verifyLatLon.php     |  85 ++++++-------
 models/admin/import/index.php          |  45 +++++++
 setup/validActions.php                 |   3 +-
 views/admin/import/header.html         |  24 ++--
 views/admin/import/verify.html         |  95 ++++++++++++++
 6 files changed, 366 insertions(+), 52 deletions(-)
 create mode 100644 models/admin/ajax/memberCountCheck.php
 create mode 100644 views/admin/import/verify.html

diff --git a/models/admin/ajax/memberCountCheck.php b/models/admin/ajax/memberCountCheck.php
new file mode 100644
index 00000000..3a5d5556
--- /dev/null
+++ b/models/admin/ajax/memberCountCheck.php
@@ -0,0 +1,166 @@
+<?php
+
+/**
+ * Gaslight Media Members Database
+ * Verify Member Numbers match
+ *
+ * PHP version 5.5
+ *
+ * @category glmWordPressPlugin
+ * @package  glmMembersDatabase
+ * @author   Chuck Scott <cscott@gaslightmedia.com>
+ * @license  http://www.gaslightmedia.com Gaslightmedia
+ * @version  0.1
+ */
+
+
+/**
+ * This class exports the currently selected members list
+ * to a printable HTML file, to a CSV file, or otherwise.
+ */
+class GlmMembersAdmin_ajax_memberCountCheck
+{
+
+    /**
+     * WordPress Database Object
+     *
+     * @var $wpdb
+     * @access public
+     */
+    public $wpdb;
+    /**
+     * Plugin Configuration Data
+     *
+     * @var $config
+     * @access public
+     */
+    public $config;
+
+    /**
+     * Constructor
+     *
+     * This constructor 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;
+
+    }
+
+    public function modelAction( $actionData = false ) {
+
+        $oldLat = array();
+        $oldLon = array();
+        $newLat = array();
+        $newLon = array();
+
+        // Counters
+        $totalMembers = 0;
+        $totalGood    = 0;
+        $totalBad     = 0;
+
+        // Get the database options.
+        $db_host = get_option( 'glm-member-import-db-host', '' );
+        $db_name = get_option( 'glm-member-import-db-name', '' );
+        $db_user = get_option( 'glm-member-import-db-user', '' );
+        $db_pass = get_option( 'glm-member-import-db-pass', '' );
+
+        $stats = array();
+
+        if ( !$db_host && !$db_name && !$db_user ) {
+            header( 'Content-Type: application/json' );
+            $stats = array( 'error' => true );
+            echo json_encode( $stats );
+            exit;
+        }
+
+        // Setup the PDO for the old postgresql database.
+        // Setup FETCH_ASSOC
+        $dbh = new PDO(
+            "pgsql: host=$db_host dbname=$db_name user=$db_user",
+            null,
+            null,
+            array(
+                PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC
+            )
+        );
+        // Setup the error mode for exceptions
+        $dbh->setAttribute(
+            PDO::ATTR_ERRMODE,
+            PDO::ERRMODE_EXCEPTION
+        );
+
+        $pSql = "
+        SELECT member_id as id,member_name as name
+          FROM members.member
+        ORDER BY name";
+
+        $oldMembers = $dbh->query( $pSql )->fetchAll();
+
+
+        foreach ( $oldMembers as $oldMember ) {
+            $old_members_list[ $oldMember['id'] ] = $oldMember['name'];
+        }
+        ksort( $old_members_list );
+
+        $members = $this->wpdb->get_results(
+            "SELECT old_member_id as id,name
+               FROM " . GLM_MEMBERS_PLUGIN_DB_PREFIX . "members
+              WHERE old_member_id IS NOT NULL
+                AND access != 90
+            ORDER BY name",
+            ARRAY_A
+        );
+
+        foreach ( $members as $member ) {
+            $members_list[ $member['id'] ] = $member['name'];
+        }
+        ksort( $members_list );
+        $new_keys = array_keys( $members_list );
+        $old_keys = array_keys( $old_members_list );
+
+        $diff = array_diff( $new_keys, $old_keys );
+        $total_deleted = count( $diff );
+
+        if ( isset( $_REQUEST['archiveDelete'] ) && $archive_delete = filter_var( $_REQUEST['archiveDelete'], FILTER_VALIDATE_BOOLEAN ) ) {
+            $archive_delete = filter_var( $_REQUEST['archiveDelete'], FILTER_VALIDATE_BOOLEAN );
+            if ( $archive_delete && isset( $diff ) && is_array( $diff ) && !empty( $diff ) ) {
+                // Archive members with deleted id's.
+                $this->wpdb->query(
+                    "UPDATE " . GLM_MEMBERS_PLUGIN_DB_PREFIX . "members
+                        SET access = 90
+                      WHERE old_member_id IN (" . implode( ',', $diff ) . ")"
+                );
+                $stats = array(
+                    'error'    => false,
+                    'archived' => true
+                );
+                echo json_encode( $stats );
+                exit;
+            }
+        }
+
+        $diff2 = array_diff( $old_keys, $new_keys );
+        $total_new = count( $diff2 );
+
+        header( 'Content-Type: application/json' );
+        $stats = array(
+            'error'   => false,
+            'new'     => $total_new,
+            'deleted' => $total_deleted,
+            'diff'    => array_values( $diff )
+        );
+        echo json_encode( $stats );
+
+    }
+
+}
diff --git a/models/admin/ajax/verifyLatLon.php b/models/admin/ajax/verifyLatLon.php
index 417c166e..5c84ee11 100644
--- a/models/admin/ajax/verifyLatLon.php
+++ b/models/admin/ajax/verifyLatLon.php
@@ -2,7 +2,7 @@
 
 /**
  * Gaslight Media Members Database
- * Members List JSON Export by AJAX
+ * Verify Member Lat Lon
  *
  * PHP version 5.5
  *
@@ -14,14 +14,11 @@
  */
 
 
-// Load Members data abstract
-// require_once GLM_MEMBERS_PLUGIN_CLASS_PATH . '/data/dataMemberInfo.php';
 /**
- *
  * This class exports the currently selected members list
  * to a printable HTML file, to a CSV file, or otherwise.
  */
-class GlmMembersAdmin_ajax_verifyLatLon // extends GlmDataMemberInfo
+class GlmMembersAdmin_ajax_verifyLatLon
 {
 
     /**
@@ -57,8 +54,6 @@ class GlmMembersAdmin_ajax_verifyLatLon // extends GlmDataMemberInfo
         // Save plugin configuration object
         $this->config = $config;
 
-        // parent::__construct( $wpdb, $config );
-
     }
 
     public function modelAction( $actionData = false ) {
@@ -67,13 +62,29 @@ class GlmMembersAdmin_ajax_verifyLatLon // extends GlmDataMemberInfo
         $oldLon = array();
         $newLat = array();
         $newLon = array();
+
         // Counters
         $totalMembers = 0;
         $totalGood    = 0;
         $totalBad     = 0;
 
+        // Get the database options.
+        $db_host = get_option( 'glm-member-import-db-host', '' );
+        $db_name = get_option( 'glm-member-import-db-name', '' );
+        $db_user = get_option( 'glm-member-import-db-user', '' );
+        $db_pass = get_option( 'glm-member-import-db-pass', '' );
+
+        $stats = array();
+
+        if ( !$db_host && !$db_name && !$db_user ) {
+            header( 'Content-Type: application/json' );
+            $stats = array( 'error' => true );
+            echo json_encode( $stats );
+            exit;
+        }
+
         $dbh = new PDO(
-            'pgsql: host=ds5 dbname=uptravel user=nobody',
+            "pgsql: host=$db_host dbname=$db_name user=$db_user",
             null,
             null,
             array(
@@ -90,62 +101,59 @@ class GlmMembersAdmin_ajax_verifyLatLon // extends GlmDataMemberInfo
           FROM members.member
         ORDER BY member_id";
 
-        $oldMembers = $dbh->query($pSql)->fetchAll();
+        $oldMembers = $dbh->query( $pSql )->fetchAll();
 
         if ( isset( $oldMembers ) && is_array( $oldMembers ) ) {
             foreach ( $oldMembers as $oldMember ) {
-                $oldLat[$oldMember['member_id']] = round( $oldMember['lat'], 4, PHP_ROUND_HALF_UP );
-                $oldLon[$oldMember['member_id']] = round( $oldMember['lon'], 4, PHP_ROUND_HALF_UP );
+                $oldLat[ $oldMember['member_id'] ] = round( $oldMember['lat'], 4, PHP_ROUND_HALF_UP );
+                $oldLon[ $oldMember['member_id'] ] = round( $oldMember['lon'], 4, PHP_ROUND_HALF_UP );
             }
         }
 
 
-        // echo '<pre>$oldMembers: ' . print_r( $oldMembers, true ) . '</pre>';
-        // exit;
-
         $sql = "
         SELECT MI.id,MI.member,MI.member_name,MI.lat,MI.lon,M.old_member_id
-          FROM ".GLM_MEMBERS_PLUGIN_DB_PREFIX. "member_info MI
+          FROM " . GLM_MEMBERS_PLUGIN_DB_PREFIX . "member_info MI
                LEFT OUTER JOIN " . GLM_MEMBERS_PLUGIN_DB_PREFIX . "members M
                ON ( MI.member = M.id )
-         WHERE status = ".$this->config['status_numb']['Active']."
+         WHERE status = " . $this->config['status_numb']['Active'] . "
            AND  (
                  SELECT access
-                 FROM ".GLM_MEMBERS_PLUGIN_DB_PREFIX."members
+                 FROM " . GLM_MEMBERS_PLUGIN_DB_PREFIX . "members
                  WHERE id = MI.member
            ) IN (
-                 ".$this->config['access_numb']['NoAccess'].",
-                 ".$this->config['access_numb']['Moderated'].",
-                 ".$this->config['access_numb']['Full']."
+                 " . $this->config['access_numb']['NoAccess'] . ",
+                 " . $this->config['access_numb']['Moderated'] . ",
+                 " . $this->config['access_numb']['Full'] . "
            )
           AND M.old_member_id IS NOT NULL
+          AND MI.lat IS NOT NULL
+          AND MI.lat <> 0
+          AND MI.lon IS NOT NULL
+          AND MI.lon <> 0
         ORDER BY M.old_member_id
         ";
 
         $members = $this->wpdb->get_results( $sql, ARRAY_A );
 
-        // echo '<pre>$members: ' . print_r( $members, true ) . '</pre>';
-        // echo '<p>Number of members: ' . count( $members ) . '</p>';
-
         if ( isset( $members ) && is_array( $members ) ) {
             foreach ( $members as $member ) {
                 $newLat[] = $member['lat'];
                 $newLon[] = $member['lon'];
 
-                if ( !$oldLon[$member['old_member_id']] ) {
+                if ( !$oldLon[ $member['old_member_id'] ] ) {
                     continue;
                 }
 
-                // echo '<p>Member: ' . $member['old_member_id'] . '</p>';
+                $totalMembers++;
 
                 // Compare lats
-                if ( $oldLat[$member['old_member_id']] == $member['lat'] ) {
-                    // echo '<p>Lat matches</p>';
+                if ( $oldLat[ $member['old_member_id'] ] == $member['lat'] ) {
                     $totalGood++;
                 } else {
                     // check if off by one
-                    $l = (float)$member['lat'];
-                    $ol = (float)$oldLat[$member['old_member_id']];
+                    $l  = (float)$member['lat'];
+                    $ol = (float)$oldLat[ $member['old_member_id'] ];
                     if ( (float)$ol > (float)$l ) {
                         $diff = ( (float)$ol - (float)$l );
                     } else {
@@ -155,26 +163,19 @@ class GlmMembersAdmin_ajax_verifyLatLon // extends GlmDataMemberInfo
                         $totalGood++;
                     } else {
                         $totalBad++;
-                        echo '<p>Member: ' . $member['old_member_id'] . '</p>';
-                        echo '<pre>$l: ' . print_r( $l, true ) . '</pre>';
-                        echo '<pre>$ol: ' . print_r( $ol, true ) . '</pre>';
-                        echo '<pre>$diff: ' . print_r( $diff, true ) . '</pre>';
                     }
                 }
             }
         }
 
-        echo '<pre>$totalMembers: ' . print_r( $totalMembers, true ) . '</pre>';
-        echo '<pre>$totalGood: ' . print_r( $totalGood, true ) . '</pre>';
-        echo '<pre>$totalBad: ' . print_r( $totalBad, true ) . '</pre>';
+        $stats = array(
+            'totalMembers' => $totalMembers,
+            'totalGood'    => $totalGood,
+            'totalBad'     => $totalBad,
+        );
 
-        // echo '<pre>$oldLat: ' . print_r( $oldLat, true ) . '</pre>';
-        // echo '<pre>$oldLon: ' . print_r( $oldLon, true ) . '</pre>';
-        // echo '<pre>$newLat: ' . print_r( $newLat, true ) . '</pre>';
-        // echo '<pre>$newLon: ' . print_r( $newLon, true ) . '</pre>';
+        echo json_encode( $stats );
 
-        // header( 'Content-Type: application/json' );
-        // echo json_encode( $members );
     }
 
 }
diff --git a/models/admin/import/index.php b/models/admin/import/index.php
index 37695dca..2005d32b 100644
--- a/models/admin/import/index.php
+++ b/models/admin/import/index.php
@@ -257,6 +257,10 @@ class GlmMembersAdmin_import_index
         $haveMembers = false;
         $fileExists  = false;
         $isValid     = false;
+
+        // If we should archive members (deleted ones from remote)
+        $archive_delete = false;
+
         // Check to see if they have members
         $haveMembers = $this->wpdb->get_var(
             "SELECT count(id)
@@ -372,6 +376,37 @@ class GlmMembersAdmin_import_index
 
         switch( $option ) {
 
+        case 'verify':
+
+            if ( $db_host = filter_var( $_REQUEST['db_host'] ) ) {
+                update_option( 'glm-member-import-db-host', $db_host );
+            }
+            if ( $db_name = filter_var( $_REQUEST['db_name'] ) ) {
+                update_option( 'glm-member-import-db-name', $db_name );
+            }
+            if ( $db_user = filter_var( $_REQUEST['db_user'] ) ) {
+                update_option( 'glm-member-import-db-user', $db_user );
+            }
+            if ( $db_pass = filter_var( $_REQUEST['db_pass'] ) ) {
+                update_option( 'glm-member-import-db-pass', $db_pass );
+            }
+
+            if ( isset( $_REQUEST['archiveDelete'] ) ) {
+               $archive_delete   = filter_var( $_REQUEST['archiveDelete'], FILTER_VALIDATE_BOOLEAN );
+               $archiveDeleteUrl = GLM_MEMBERS_PLUGIN_ADMIN_AJAX_URL . '?action=glm_members_admin_ajax&glm_action=memberCountCheck&archiveDelete=1';
+               $response         = wp_remote_get( $archiveDeleteUrl );
+               // echo '<pre>$response: ' . print_r( $response, true ) . '</pre>';
+               $responseBody     = wp_remote_retrieve_body( wp_remote_get( $archiveDeleteUrl ) );
+               // echo '<pre>$responseBody: ' . print_r( $responseBody, true ) . '</pre>';
+            }
+
+            $view = 'verify.html';
+            break;
+
+        case 'verifyValidate':
+            $view = 'verifyValidate.html';
+            break;
+
         case 'validate';
             $validFiles = 0;
             // Set the view file
@@ -912,6 +947,11 @@ class GlmMembersAdmin_import_index
             update_option( 'glm-import-stats', $glm_import_stats );
         }
 
+        $db_host = get_option( 'glm-member-import-db-host', '' );
+        $db_name = get_option( 'glm-member-import-db-name', '' );
+        $db_user = get_option( 'glm-member-import-db-user', '' );
+        $db_pass = get_option( 'glm-member-import-db-pass', '' );
+
         // Setup the template data array
         $templateData = array(
             //'addOnTabs'       => $addOnTabs,
@@ -937,6 +977,11 @@ class GlmMembersAdmin_import_index
             'haveMembers'     => $haveMembers,
             'isValid'         => $isValid,
             'sampleFileUrl'   => GLM_MEMBERS_PLUGIN_BASE_URL . '/sample-files/',
+            'db_host'         => $db_host,
+            'db_name'         => $db_name,
+            'db_user'         => $db_user,
+            'db_pass'         => $db_pass,
+            'archive_delete'  => $archive_delete,
         );
 
         // Return status, suggested view, and data to controller
diff --git a/setup/validActions.php b/setup/validActions.php
index 05a28f8c..eea0ce5d 100644
--- a/setup/validActions.php
+++ b/setup/validActions.php
@@ -50,7 +50,8 @@ $glmMembersValidActions = array(
             'countiesMapData'     => 'glm-member-db',
             'membersList'         => 'glm-member-db',
             'membersListJson'     => 'glm-member-db',
-            'verifyLatLon'     => 'glm-member-db',
+            'verifyLatLon'        => 'glm-member-db',
+            'memberCountCheck'    => 'glm-member-db',
         ),
         'dashboard' => array(
             'index'   => 'glm-member-db',
diff --git a/views/admin/import/header.html b/views/admin/import/header.html
index 621570d3..52aa901e 100644
--- a/views/admin/import/header.html
+++ b/views/admin/import/header.html
@@ -6,20 +6,26 @@
         <a
         href="{$thisUrl}?page={$thisPage}&glm_action=index" class="nav-tab{if $option==default} nav-tab-active{/if}">Import CSV</a>
         {if $haveMembers}
-        <a
-            href="{$thisUrl}?page={$thisPage}&glm_action=index&option=photos"
-            class="nav-tab{if $option==photos or $option==photosValidate} nav-tab-active{/if}">Import Photos</a>
-        <a
-            href="{$thisUrl}?page={$thisPage}&glm_action=index&option=files"
-            class="nav-tab{if $option==files or $option===filesValidate} nav-tab-active{/if}">Import Files</a>
-        <a
-            href="{$thisUrl}?page={$thisPage}&glm_action=index&option=videos"
-            class="nav-tab{if $option==videos or $option===videosValidate} nav-tab-active{/if}">Import Videos</a>
+            <a
+                href="{$thisUrl}?page={$thisPage}&glm_action=index&option=photos"
+                class="nav-tab{if $option==photos or $option==photosValidate} nav-tab-active{/if}">Import Photos</a>
+            <a
+                href="{$thisUrl}?page={$thisPage}&glm_action=index&option=files"
+                class="nav-tab{if $option==files or $option===filesValidate} nav-tab-active{/if}">Import Files</a>
+            <a
+                href="{$thisUrl}?page={$thisPage}&glm_action=index&option=videos"
+                class="nav-tab{if $option==videos or $option===videosValidate} nav-tab-active{/if}">Import Videos</a>
         {/if}
 
         {foreach $addOnTabs as $a}
             <a href="{$thisUrl}?page={$thisPage}&glm_action={$a.action}{if isset($a.option)}&option={$a.option}{/if}" class="nav-tab{if $thisAction==$a.action} nav-tab-active{/if}">{$a.text}</a>
         {/foreach}
+
+        {if $haveMembers}
+            <a
+                href="{$thisUrl}?page={$thisPage}&glm_action=index&option=verify"
+                class="nav-tab{if $option==verify or $option===verifyValidate} nav-tab-active{/if}">Verify Member Data</a>
+        {/if}
     </h2>
     <div id="glm-admin-content-container">
 
diff --git a/views/admin/import/verify.html b/views/admin/import/verify.html
new file mode 100644
index 00000000..d4bd5876
--- /dev/null
+++ b/views/admin/import/verify.html
@@ -0,0 +1,95 @@
+{include file='admin/import/header.html'}
+
+<h2>Data Verification</h2>
+<h3>Setup Database Options</h3>
+<form action="{$thisUrl}?page={$thisPage}" method="post" enctype="multipart/form-data">
+    <input type="hidden" name="glm_action" value="index" />
+    <input type="hidden" name="option" value="verify" />
+
+    <table class="glm-admin-table">
+        <tr>
+            <th> Host: </th>
+            <td> <input id="db_host" name="db_host" value="{$db_host}"> </td>
+        </tr>
+
+        <tr>
+            <th> Name: </th>
+            <td> <input id="db_name" name="db_name" value="{$db_name}"> </td>
+        </tr>
+        <tr>
+            <th> User: </th>
+            <td> <input id="db_user" name="db_user" value="{$db_user}"> </td>
+        </tr>
+        <tr>
+            <th> Password: </th>
+            <td> <input id="db_pass" name="db_pass" value="{$db_pass}"> </td>
+        </tr>
+    </table>
+
+    <input type="submit" value="Save" class="button button-primary submit-import">
+</form>
+
+<div id="import-stats">
+    <a href="{$thisUrl}?page={$thisPage}&glm_action=index&option=verify&archiveDelete=1" id="glmArchiveMembers" class="glm-hidden">Archive Deleted Member</a><br>
+
+</div>
+
+<script>
+jQuery(document).ready(function($) {
+
+    // If there's database connection then fetch count stats.
+    var db_host =  $('#db_host').val();
+    var db_name =  $('#db_name').val();
+    var db_user =  $('#db_user').val();
+    var db_pass =  $('#db_pass').val();
+
+    // Pass is not required
+    if ( db_host && db_name && db_user ) {
+
+        var memberCountUrl = '{$ajaxUrl}?action=glm_members_admin_ajax&glm_action=memberCountCheck';
+        var Counts = $.getJSON( memberCountUrl )
+        .fail(function(data) {
+            alert('Sorry, we had a communications failure. 2');
+        })
+        .done(function( data ) {
+            if ( data.error ) {
+                // There was an error
+                alert('Sorry, Something bad happened!');
+            } else {
+                $( '#import-stats' ).append( '<h3>Member Count Check</h3><br>' );
+                if ( data.new ) {
+                    // console.log( 'There are new members on old site', data.new );
+                    $('#import-stats').append( 'There are ' + data.new + ' new members on old site<br>' );
+                }
+                if ( data.deleted ) {
+                    // console.log( 'There are member deleted from old site', data.deleted );
+                    $( '#import-stats' ).append( 'There are ' + data.deleted + ' members deleted from old site<br>' );
+                    $('#glmArchiveMembers').removeClass( 'glm-hidden' );
+                }
+                if ( !data.deleted && !data.new ) {
+                    $( '#import-stats' ).append( 'All member counts match!!!<br>' );
+                }
+            }
+        });
+        var memberLatLonUrl = '{$ajaxUrl}?action=glm_members_admin_ajax&glm_action=verifyLatLon';
+        var LatLonChecks = $.getJSON( memberLatLonUrl )
+        .fail(function(data) {
+            alert('Sorry, we had a communications failure. 2');
+        })
+        .done(function( data ) {
+            $( '#import-stats' ).append( '<h3>Lat Lon Check</h3><br>' );
+            if ( data.error ) {
+                // There was an error
+                alert('Sorry, Something bad happened!');
+            } else {
+                $( '#import-stats' ).append( 'There are ' + data.totalMembers + ' members<br>' );
+                $( '#import-stats' ).append( 'There are ' + data.totalGood + ' Good lat lon<br>' );
+                $( '#import-stats' ).append( 'There are ' + data.totalBad + ' Bad lat lon<br>' );
+            }
+        });
+        var memberLatLonUrl
+    }
+});
+</script>
+
+{include file='admin/footer.html'}
-- 
2.17.1