From df0db4656743ee2bc2ddcd2cfd13506869f98ac3 Mon Sep 17 00:00:00 2001 From: Steve Sutton Date: Fri, 23 Aug 2019 09:08:10 -0400 Subject: [PATCH] Working on Campaign Stats page Adding campaign stats Adding filter for message (subject) --- classes/data/dataEmailLogs.php | 184 +++++++++++++++++++++++ classes/data/dataEmailMessages.php | 2 +- models/admin/ajax/ajaxMessagePreview.php | 8 +- models/admin/ajax/trackMessagePixel.php | 8 +- models/admin/messages/index.php | 155 ++++++++++++++++++- setup/routes.php | 53 +++++++ views/admin/header.html | 2 +- views/admin/messages/list.html | 88 ++++++----- views/admin/messages/stats.html | 65 +++++++- 9 files changed, 504 insertions(+), 61 deletions(-) create mode 100644 classes/data/dataEmailLogs.php create mode 100644 setup/routes.php diff --git a/classes/data/dataEmailLogs.php b/classes/data/dataEmailLogs.php new file mode 100644 index 0000000..fb01174 --- /dev/null +++ b/classes/data/dataEmailLogs.php @@ -0,0 +1,184 @@ + + * @license http://www.gaslightmedia.com Gaslightmedia + * @release SVN: $Id: dataEvents.php,v 1.0 2011/01/25 19:31:47 cscott Exp $ + */ + +/** + * GlmDataEmailMessages class + * + * PHP version 5 + * + * @category Data + * @package GLM Member DB + * @author Chuck Scott + * @license http://www.gaslightmedia.com Gaslightmedia + * @release SVN: $Id: dataMembers.php,v 1.0 2011/01/25 19:31:47 cscott + * Exp $ + */ +class GlmDataEmailLogs extends GlmDataAbstract +{ + + /** + * WordPress Database Object + * + * @var $wpdb + * @access public + */ + public $wpdb; + /** + * Plugin Configuration Data + * + * @var $config + * @access public + */ + public $config; + /** + * Data Table Name + * + * @var $table + * @access public + */ + public $table; + /** + * Field definitions + * + * 'type' is type of field as defined by the application + * text Regular text field + * pointer Pointer to an entry in another table + * 'filters' is the filter name for a particular filter ID in PHP filter + * functions + * See PHP filter_id() + * + * 'use' is when to use the field + * l = List + * g = Get + * n = New + * i = Insert + * e = Edit + * u = Update + * d = Delete + * a = All + * + * @var $ini + * @access public + */ + public $fields = false; + + public $postOpens = false; + + /** + * Constructor + * + * @param object $d database connection + * @param array $config Configuration array + * @param bool $limitedEdit Flag to say indicate limited edit requested + * + * @return void + * @access public + */ + public function __construct($wpdb, $config, $limitedEdit = false) + { + + // If this class is not being extended along with existing $wpdb and $config + if (!$this->wpdb) { + + // Save WordPress Database object + $this->wpdb = $wpdb; + + // Save plugin configuration object + $this->config = $config; + + } + + /* + * Table Name + */ + $this->table = GLM_MEMBERS_MESSAGES_PLUGIN_DB_PREFIX . 'email_logs'; + + /* + * Table Data Fields + */ + + $this->fields = [ + + 'id' => [ + 'field' => 'id', + 'type' => 'integer', + 'view_only' => true, + 'use' => 'a' + ], + + // process id + 'process_id' => [ + 'field' => 'process_id', + 'type' => 'text', + 'required' => false, + 'use' => 'a' + ], + + // send date + 'send_date' => [ + 'field' => 'send_date', + 'type' => 'date', + 'required' => false, + 'use' => 'a' + ], + + // Message id + 'message_id' => [ + 'field' => 'message_id', + 'type' => 'pointer', + 'p_table' => GLM_MEMBERS_MESSAGES_PLUGIN_DB_PREFIX . 'email_messages', + 'p_field' => 'subject', + 'p_orderby' => 'subject', + 'force_list' => false, + 'required' => false, + 'use' => 'a' + ], + + ]; + + } + + /** + * Entry Post Processing Call-Back Method + * + * Perform post-processing for all result entries. + * + * In this case we're using it to append an array of category + * data to each member result and also sort by member name. + * + * @param array $r Array of field result data for a single entry + * @param string $a Action being performed (l, i, g, ...) + * + * @return object Class object + * + */ + public function entryPostProcessing($r, $a) + { + if ( $this->postOpens ) { + $opens = $this->wpdb->get_var( + $this->wpdb->prepare( + "SELECT count(*) + FROM " . GLM_MEMBERS_MESSAGES_PLUGIN_DB_PREFIX . "email_logs + WHERE process_id = %s + AND read_date IS NOT NULL", + $r['process_id'] + ) + ); + $r['opens'] = ( $opens ) ? $opens : '0'; + } + return $r; + } + + +} diff --git a/classes/data/dataEmailMessages.php b/classes/data/dataEmailMessages.php index b8e6913..2788f2b 100644 --- a/classes/data/dataEmailMessages.php +++ b/classes/data/dataEmailMessages.php @@ -125,7 +125,7 @@ class GlmDataEmailMessages extends GlmDataAbstract 'use' => 'a', ), - // Member Type + // Template Id 'template_id' => array ( 'field' => 'template_id', 'type' => 'pointer', diff --git a/models/admin/ajax/ajaxMessagePreview.php b/models/admin/ajax/ajaxMessagePreview.php index 3ea28cf..bf050a1 100644 --- a/models/admin/ajax/ajaxMessagePreview.php +++ b/models/admin/ajax/ajaxMessagePreview.php @@ -33,13 +33,7 @@ require_once GLM_MEMBERS_MESSAGES_PLUGIN_PATH . '/models/admin/messages/index.ph */ /** - * This class handles the work of creating new invoices based on. - * 1) Member Type of member matching a paid invoiceType - * 2) Member renewal date past - * 3) Member has Billing Account - * 4) Member has no active Invoice - * 5) Renewal date is within the next 30 Days - * + * Ajax previewer for Email messages */ class GlmMembersAdmin_ajax_ajaxMessagePreview extends GlmMembersAdmin_messages_index { diff --git a/models/admin/ajax/trackMessagePixel.php b/models/admin/ajax/trackMessagePixel.php index ac055b8..f444592 100644 --- a/models/admin/ajax/trackMessagePixel.php +++ b/models/admin/ajax/trackMessagePixel.php @@ -31,13 +31,7 @@ */ /** - * This class handles the work of creating new invoices based on. - * 1) Member Type of member matching a paid invoiceType - * 2) Member renewal date past - * 3) Member has Billing Account - * 4) Member has no active Invoice - * 5) Renewal date is within the next 30 Days - * + * Track the email opens */ class GlmMembersAdmin_ajax_trackMessagePixel { diff --git a/models/admin/messages/index.php b/models/admin/messages/index.php index b561874..2177f94 100644 --- a/models/admin/messages/index.php +++ b/models/admin/messages/index.php @@ -16,6 +16,7 @@ // Load Members data abstract require_once GLM_MEMBERS_MESSAGES_PLUGIN_CLASS_PATH.'/data/dataEmailMessages.php'; require_once GLM_MEMBERS_MESSAGES_PLUGIN_CLASS_PATH.'/data/dataEmailTemplates.php'; +require_once GLM_MEMBERS_MESSAGES_PLUGIN_CLASS_PATH.'/data/dataEmailLogs.php'; /* * This class performs the work for the default action of the "Members" menu @@ -104,6 +105,7 @@ class GlmMembersAdmin_messages_index extends GlmDataEmailMessages $option = ''; $option2 = false; $viewPath = 'admin/messages/'; + // Merge Tags $mergeTags = [ // 'Member Name', '{$member.name}', 'Contact First Name', '{$contact.fname}', @@ -117,6 +119,14 @@ class GlmMembersAdmin_messages_index extends GlmDataEmailMessages // 'Contact Home Phone', '{$contact.home_phone}', // 'Contact Mobile Phone', '{$contact.mobile_phone}', ]; + // For Paging + $paging = true; + $start = 1; + $limit = 20; + $numbDisplayed = false; + $lastDisplayed = false; + $prevStart = false; + $nextStart = false; // Setup Foundation 6 wp_enqueue_style( 'Foundation6', GLM_MEMBERS_PLUGIN_URL . 'css/foundation-6.min.css' ); @@ -150,8 +160,97 @@ class GlmMembersAdmin_messages_index extends GlmDataEmailMessages switch ( $option ) { case 'stats': $view = 'stats'; + $whereParts = [ + 'process_id IS NOT NULL' + ]; + $where = false; + if ( isset( $_REQUEST['filterMessage'] ) && $filterMessage = filter_var( $_REQUEST['filterMessage'], FILTER_SANITIZE_STRING) ) { + $whereParts[] = " message_id IN ( SELECT id FROM " . GLM_MEMBERS_MESSAGES_PLUGIN_DB_PREFIX . "email_messages WHERE subject like '%" . $filterMessage . "%')"; + } + // Check if we're doing paging (must be done before getList or getResults) + if ( isset( $_REQUEST['pageSelect'] ) ) { + + // If request is for Next + if ( $_REQUEST['pageSelect'][0] == 'N' ) { + $newStart = $_REQUEST['nextStart'] - 0; + + // Otherwise it must be Previous + } else { + $newStart = $_REQUEST['prevStart'] - 0; + } + + if ( $newStart > 0 ) { + $start = $newStart; + } + } + if ( !empty( $whereParts ) ) { + $where = implode( ' AND ', $whereParts ); + } + // Get logs + $Stats = new GlmDataEmailLogs( $this->wpdb, $this->config ); + $Stats->postOpens = true; + $statsData = $Stats->getResults( + [ + 'where' => $where, + 'groupby' => [ + 'aggregate' => 'count(*) as emails', + 'group' => 'process_id', + ], + 'start' => $start, + 'limit' => $limit, + 'stats' => true, + ] + ); + $statCount = $statsData['stats']; + // echo '
$statCount: ' . print_r( $statCount, true ) . '
'; + $Stats->postOpens = false; + // Get paging results (after getList or getResults) + $numbDisplayed = $statsData['returned']; + $lastDisplayed = $statsData['last']; + if ( $start == 1 ) { + $prevStart = false; + } else { + $prevStart = $start - $limit; + if ( $start < 1 ) { + $start = 1; + } + } + if ( $numbDisplayed == $limit ) { + $nextStart = $start + $limit; + } + if ( $statCount <= $limit ) { + $paging = false; + } else { + $paging = [ + 'unitName' => 'Stats', + 'start' => $start, + 'limit' => $limit, + 'numbDisplayed' => $numbDisplayed, + 'lastDisplayed' => $lastDisplayed, + 'prevStart' => $prevStart, + 'nextStart' => $nextStart, + ]; + } + $stats = $statsData['list']; + $messageData = $this->wpdb->get_results( + "SELECT id,subject + FROM " . GLM_MEMBERS_MESSAGES_PLUGIN_DB_PREFIX . "email_messages + ORDER BY subject" + ); + $messages = array(); + foreach ( $messageData as $row ) { + $messages[$row->id] = $row->subject; + } $tData = [ - 'stats' => 'test' + 'stats' => $stats, + 'messages' => $messages, + 'headers' => [ + 'Message', + 'Date Sent', + 'Total Sent', + 'Opens', + ], + 'paging' => $paging, ]; break; case 'search': @@ -466,6 +565,23 @@ class GlmMembersAdmin_messages_index extends GlmDataEmailMessages $view = 'list'; default: + // Check if we're doing paging (must be done before getList or getResults) + if ( isset( $_REQUEST['pageSelect'] ) ) { + + // If request is for Next + if ( $_REQUEST['pageSelect'][0] == 'N' ) { + $newStart = $_REQUEST['nextStart'] - 0; + + // Otherwise it must be Previous + } else { + $newStart = $_REQUEST['prevStart'] - 0; + } + + if ( $newStart > 0 ) { + $start = $newStart; + } + } + if ( isset( $_REQUEST['filterArchived'] ) && $filterArchived = filter_var( $_REQUEST['filterArchived'], FILTER_VALIDATE_BOOLEAN ) ) { $where = "T.archived = true"; } else { @@ -473,12 +589,43 @@ class GlmMembersAdmin_messages_index extends GlmDataEmailMessages } $this->postStats = true; $this->postSent = true; - $messages = $this->getList( $where ); + $order = 'last_updated DESC'; + $messageCount = $this->getStats( $where ); + $messageData = $this->getList( $where, $order, true, 'id', $start, $limit ); + // Get paging results (after getList or getResults) + $numbDisplayed = $messageData['returned']; + $lastDisplayed = $messageData['last']; + if ( $start == 1 ) { + $prevStart = false; + } else { + $prevStart = $start - $limit; + if ( $start < 1 ) { + $start = 1; + } + } + if ( $numbDisplayed == $limit ) { + $nextStart = $start + $limit; + } + if ( $messageCount <= $limit ) { + $paging = false; + } else { + $paging = [ + 'unitName' => 'Messages', + 'start' => $start, + 'limit' => $limit, + 'numbDisplayed' => $numbDisplayed, + 'lastDisplayed' => $lastDisplayed, + 'prevStart' => $prevStart, + 'nextStart' => $nextStart, + ]; + } + $messages = $messageData['list']; $this->postStats = false; $this->postSent = false; - $tData = array( + $tData = [ 'messages' => $messages, - ); + 'paging' => $paging, + ]; break; } diff --git a/setup/routes.php b/setup/routes.php new file mode 100644 index 0000000..b908e8f --- /dev/null +++ b/setup/routes.php @@ -0,0 +1,53 @@ +namespace = '/glm/v0'; + $this->resource_name = 'messages'; + } + + public function register_routes() + { + register_rest_route( $this->namespace, '/' . $this->resource_name, array( + // Here we register the readable endpoint for collections. + array( + 'methods' => 'GET', + 'callback' => array( $this, 'get_items' ), + ), + ) ); + register_rest_route( $this->namespace, '/' . $this->resource_name . '/(?P[\d]+)', array( + // Notice how we are registering multiple endpoints the 'schema' equates to an OPTIONS request. + array( + 'methods' => 'GET', + 'callback' => array( $this, 'get_item' ), + ), + ) ); + } + + /** + * Grabs the five most recent posts and outputs them as a rest response. + * + * @param WP_REST_Request $request Current request. + */ + public function get_item( $request ) { + $id = (int) $request['id']; + $post = get_post( $id ); + + if ( empty( $post ) ) { + return rest_ensure_response( array() ); + } + + $response = prepare_item_for_response( $post ); + + // Return all of our post response data. + return $response; + } + +} + +add_action( 'rest_api_init', function(){ + $controller = new Glm_REST_Messages_Controller(); + $controller->register_routes(); +}); diff --git a/views/admin/header.html b/views/admin/header.html index b6e3888..c997176 100644 --- a/views/admin/header.html +++ b/views/admin/header.html @@ -15,7 +15,7 @@ Email Messages
  • - Stats + Campaign Stats
    • {*
  • diff --git a/views/admin/messages/list.html b/views/admin/messages/list.html index 18490de..30b136b 100644 --- a/views/admin/messages/list.html +++ b/views/admin/messages/list.html @@ -1,50 +1,60 @@ +{* Emails Messages List view *} + +{* Header *} {include file='admin/header.html'}

    List Messages

    {* Form Start *} -{$ui = [ - 'id' => 'messageSearch', - 'action' => "{$thisUrl}?page={$thisPage}", - 'method' => 'post', - 'file' => false -]} -{include file='ui/f6/form-start.html'} - - - -
    -
    - Filter - - {* Show Archived *} - {$ui = [ - 'value' => $smarty.request.filterArchived|default:'', - 'field' => 'filterArchived', - 'label' => 'Show Archived', - 'required' => false, - 'errorText' => 'Show Archived is Required', - 'dataError' => '' - ]} - {include file='ui/f6/checkbox.html'} - - {* Submit *} - {$ui = [ - 'class' => 'primary', - 'label' => 'Submit', - 'submit' => true, - 'id' => '', - 'cancel' => "" - ]} - {include file='ui/f6/submit.html'} - -
    -
    +
    + + + + + + +
    +
    + Filter + + {* Show Archived *} + {$ui = [ + 'value' => $smarty.request.filterArchived|default:'', + 'field' => 'filterArchived', + 'label' => 'Show Archived', + 'required' => false, + 'errorText' => 'Show Archived is Required', + 'dataError' => '' + ]} + {include file='ui/f6/checkbox.html'} + + {* Submit *} + {$ui = [ + 'class' => 'primary', + 'label' => 'Submit', + 'submit' => true, + 'id' => '', + 'cancel' => "" + ]} + {include file='ui/f6/submit.html'} + +
    +
    -{* Form End *} -{include file='ui/f6/form-end.html'} Add Message +
    +{* Paging *} +{include file="ui/f6/paging.html"} + +{* List Messages *} {include file='admin/messages/listMessagesTable.html'} +{* Paging *} +{include file="ui/f6/paging.html"} + +{* Form End *} +{include file='ui/f6/form-end.html'} + +{* Footer *} {include file='../../admin/footer.html'} diff --git a/views/admin/messages/stats.html b/views/admin/messages/stats.html index 01844de..a189d1b 100644 --- a/views/admin/messages/stats.html +++ b/views/admin/messages/stats.html @@ -1,6 +1,67 @@ +{* Stats List View *} + +{* Header *} {include file='admin/header.html'} -

    Stats

    +

    Campaign Stats

    + +{* Form Start *} + + + + + + +
    +
    + Filter + + {* Message *} + {$ui = [ + 'value' => $smarty.request.filterMessage|default:'', + 'field' => 'filterMessage', + 'label' => 'Message', + 'required' => false, + 'errorText' => 'Message is Required', + 'dataError' => '' + ]} + {include file='ui/f6/text.html'} + + {* Submit *} + {$ui = [ + 'class' => 'primary', + 'label' => 'Submit', + 'submit' => true, + 'id' => '', + 'cancel' => "" + ]} + {include file='ui/f6/submit.html'} + +
    +
    + + {* Paging *} + {include file="ui/f6/paging.html"} + + + + + + + + + {foreach $stats as $campaign} + + + + + + + {/foreach} +
    Message Date Sent Total Sent Opens
    {$campaign.message_id} {$campaign.send_date.date} {$campaign.emails} {$campaign.opens}
    + + {* Paging *} + {include file="ui/f6/paging.html"} -{$stats} +
    {include file='../../admin/footer.html'} -- 2.17.1