123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253 |
- <?php
- /**
- * @file
- * Mandrill simplenews user activity report.
- * Provides user (email address) centric reports. Includes total number of
- * newsletters sent, last interaction date, list of newsletters sent with
- * their subject, open and click count based on report pulled from
- * Mandrill web services.
- */
- /**
- * Implements hook_menu().
- */
- function mandrill_simplenews_report_menu() {
- $items = array();
- $items['admin/reports/mandrill/simplenews'] = array(
- 'title' => 'Simplenews',
- 'page callback' => array('mandrill_simplenews_report_newsletter'),
- 'access arguments' => array('view mandrill reports'),
- 'type' => MENU_LOCAL_TASK,
- );
- $items['admin/reports/mandrill/simplenews/%taxonomy_term'] = array(
- 'title' => 'Newsletter subscription',
- 'page callback' => array('mandrill_simplenews_report_newsletter_subscription'),
- 'page arguments' => array(4),
- 'access arguments' => array('view mandrill reports'),
- 'type' => MENU_CALLBACK,
- );
- $items['admin/reports/mandrill/simplenews/%taxonomy_term/%/%'] = array(
- 'title' => 'Mandrill User Activity Report',
- 'page callback' => array('mandrill_simplenews_report_user_activity'),
- 'page arguments' => array(4, 5, 6),
- 'access arguments' => array('view mandrill reports'),
- 'type' => MENU_CALLBACK,
- );
- return $items;
- }
- /**
- * Page callback to show the simplenews user subscription details in mandrill reports.
- */
- function mandrill_simplenews_report_newsletter() {
- drupal_set_title(t('Newsletter Categories'));
- $items = array();
- foreach (simplenews_categories_load_multiple() as $list) {
- $item = l($list->name, 'admin/reports/mandrill/simplenews/' . $list->tid);
- $item .= !empty($list->description) ? '<div class="description">' . $list->description . '</div>' : '';
- $items[] = $item;
- }
- $variables = array(
- 'title' => t('Click newsletter categories below to see their reports'),
- 'items' => $items,
- 'attributes' => array(
- 'class' => array('admin-list'),
- ),
- );
- return theme('item_list', $variables);
- }
- /**
- * Page callback to display subscribed user list for newsletter category.
- */
- function mandrill_simplenews_report_newsletter_subscription($term) {
- $tid = $term->tid;
- $title = t('Newsletter !title', array('!title' => $term->name));
- drupal_set_title($title);
- $breadcrumb = array(
- l(t('Home'), '<front>'),
- l(t('Administration'), 'admin'),
- l(t('Reports'), 'admin/reports'),
- l(t('Mandrill'), 'admin/reports/mandrill'),
- l(t('Newsletter'), 'admin/reports/mandrill/simplenews'),
- );
- drupal_set_breadcrumb($breadcrumb);
- $rows = array();
- $header = array(
- 'mail' => array('data' => t('Email'), 'field' => 'sn.mail', 'sort' => 'asc'),
- 'username' => array('data' => t('Username'), 'field' => 'u.name'),
- 'status' => array('data' => t('Status'), 'field' => 'sn.activated'),
- 'emails' => array('data' => t('Total no. of emails sent')),
- 'last_interaction' => array('data' => t('Last interaction'), 'field' => 'ms.last_interaction'),
- 'report' => array('data' => t('Report')),
- );
- $query = db_select('simplenews_subscriber', 'sn')->extend('PagerDefault')->extend('TableSort');
- $query->leftJoin('users', 'u', 'sn.uid = u.uid');
- $query->innerJoin('simplenews_subscription', 'su', 'sn.snid = su.snid');
- $query->leftJoin('mandrill_simplenews_report_subscription_extra', 'ms', 'su.snid = ms.snid');
- $query->condition('su.status', SIMPLENEWS_SUBSCRIPTION_STATUS_SUBSCRIBED);
- $query->condition('su.tid', $tid);
- $query->condition('ms.tid', $tid);
- $query->addField('u', 'name', 'name');
- $query->addField('ms', 'last_interaction', 'last_interaction');
- $subscription = $query
- ->fields('sn', array('snid', 'activated', 'mail', 'uid', 'language', 'created'))
- ->limit(30)
- ->orderByHeader($header)
- ->execute();
- foreach ($subscription as $subscriber) {
- $sent_mail_count = db_query("SELECT COUNT(id) FROM {mandrill_simplenews_report_newsletter_sent}
- WHERE tid = :tid AND sent_to_mail = :sent_to_mail",
- array(':tid' => $tid, ':sent_to_mail' => $subscriber->mail))->fetchField();
- $last_interaction = $subscriber->last_interaction;
- $rows[] = array(
- 'mail' => $subscriber->mail,
- 'username' => $subscriber->name,
- 'status' => theme('simplenews_status', array('source' => 'activated', 'status' => $subscriber->activated)),
- 'emails' => $sent_mail_count,
- 'last_interaction' => ((string) (int) $last_interaction === $last_interaction) ? format_date($last_interaction) : format_date($subscriber->timestamp),
- 'report' => ($sent_mail_count > 0) ? l(t('See User Activity Report'), 'admin/reports/mandrill/simplenews/' . $tid . '/' . $subscriber->mail . '/' . $subscriber->snid ) : '',
- );
- }
- // @todo: add pager
- $empty = t('No subscribers available');
- return theme('table', array('header' => $header, 'rows' => $rows, 'empty' => $empty));
- }
- /**
- * Page callback to display mandrill user activity reports.
- */
- function mandrill_simplenews_report_user_activity($term, $mail, $snid) {
- $tid = $term->tid;
- $output = '';
- $newsletter_name = $term->name;
- $breadcrumb = array(
- l(t('Home'), '<front>'),
- l(t('Administration'), 'admin'),
- l(t('Reports'), 'admin/reports'),
- l(t('Mandrill'), 'admin/reports/mandrill'),
- l(t('Newsletter'), 'admin/reports/mandrill/simplenews'),
- l($term->name, 'admin/reports/mandrill/simplenews/' . $tid),
- );
- drupal_set_breadcrumb($breadcrumb);
- drupal_set_title(t('User !mail in !newsletter', array('!mail' => $mail, '!newsletter' => $newsletter_name)));
- // get the number of emails sent to the subscriber.
- $sent_mail_count = db_query("SELECT COUNT(id) FROM {mandrill_simplenews_report_newsletter_sent}
- WHERE tid = :tid AND sent_to_mail = :sent_to_mail",
- array(':tid' => $tid, ':sent_to_mail' => $mail))->fetchField();
- // get the user's subscription date and time for the newsletter term.
- $created = db_query("SELECT timestamp FROM {simplenews_subscription} WHERE tid = :tid AND snid = :snid", array(':tid' => $tid, ':snid' => $snid))->fetchField();
- // append the member since and mail sent details along with more details.
- $caption = '<p>' . t('Member Since : !created', array('!created' => format_date($created))) . '</p>';
- $caption .= '<p>' . t('Total number of emails sent : !mail_count', array('!mail_count' => $sent_mail_count)) . '</p>';
- $header = array(
- array('data' => t('Newsletter Subject'), 'field' => 'sn.subject'),
- array('data' => t('Sent time'), 'field' => 'sn.sent_timestamp', 'sort' => 'asc'),
- array('data' => t('Opens'), 'field' => 'sn.open_count'),
- array('data' => t('Clicks'), 'field' => 'sn.click_count'),
- );
- // get the user activity details from the table.
- $query = db_select('mandrill_simplenews_report_newsletter_sent', 'sn')
- ->extend('PagerDefault')
- ->extend('TableSort');
- $query->fields('sn', array('subject', 'sent_timestamp', 'open_count', 'click_count'));
- $query->condition('sent_to_mail', $mail);
- $query->condition('tid', $tid);
- $result = $query
- ->limit(50)
- ->orderByHeader($header)
- ->execute();
- $rows = array();
- foreach ($result as $row) {
- $rows[]['data'] = array(
- 'newsletter_title' => check_plain($row->subject),
- 'newsletter_sent_time' => format_date($row->sent_timestamp, 'medium'),
- 'open_count' => $row->open_count,
- 'click_count' => $row->click_count,
- );
- }
- return array(
- 'pager_table' => array(
- '#theme' => 'table',
- '#header' => $header,
- '#rows' => $rows,
- '#caption' => $caption,
- '#empty' => t('No results founds.'),
- ),
- 'pager_pager' => array('#theme' => 'pager')
- );
- }
- /**
- * Implements hook_cronapi()
- */
- function mandrill_simplenews_report_cronapi($op, $job = NULL) {
- $items['mandrill_simplenews_email_report'] = array(
- 'description' => t('To make mandrill api calls to get sent out emails report.'),
- 'rule' => '0 */6 * * *',
- 'weight' => 11,
- 'callback' => 'mandrill_simplenews_mandrill_email_report_cron',
- );
- $items['mandrill_simplenews_last_interaction'] = array(
- 'description' => t('To update the user last interaction time on newsletters'),
- 'rule' => '0 */24 * * *',
- 'weight' => 13,
- 'callback' => 'mandrill_simplenews_update_last_interaction_cron',
- );
- return $items;
- }
- /**
- * Cron Function callback to get the email report.
- */
- function mandrill_simplenews_mandrill_email_report_cron() {
- $api_key = variable_get('mandrill_api_key', '');
- $api_timeout = variable_get('mandrill_api_timeout', 120);
- $maildrop_mandrill = new MandrillSimplenewsReport($api_key, $api_timeout);
- $maildrop_mandrill->activity_report_import();
- }
- /**
- * Cron Function callback to get the last interaction of user on newsletters.
- */
- function mandrill_simplenews_update_last_interaction_cron() {
- // query the mail and tid from the simplenews_subscription table.
- $results = db_query("SELECT sn.snid, sn.mail, su.tid, su.timestamp
- FROM simplenews_subscription su
- INNER JOIN simplenews_subscriber sn ON su.snid = sn.snid
- WHERE status = :status", array(':status' => 1));
- foreach ($results as $result) {
- // get the last time when the user opened the newsletter mail.
- $last_interaction = db_query_range("SELECT sent_timestamp
- FROM {mandrill_simplenews_report_newsletter_sent}
- WHERE tid = :tid AND open_count > :open_count AND sent_to_mail = :sent_to_mail
- ORDER BY sent_timestamp DESC", 0, 1,
- array(':tid' => $result->tid, ':open_count' => 0, ':sent_to_mail' => $result->mail))->fetchField();
- $time = ((string) (int) $last_interaction === $last_interaction) ? $last_interaction : $result->timestamp;
- // if time is not empty, update in md_simplenews_subscription_extra table.
- if ($time) {
- $update_subscription = db_merge('mandrill_simplenews_report_subscription_extra')
- ->key(array(
- 'tid' => $result->tid,
- 'snid' => $result->snid,
- ))
- ->fields(array(
- 'snid' => $result->snid,
- 'tid' => $result->tid,
- 'last_interaction' => $time,
- ))
- ->execute();
- }
- }
- watchdog('mandrill_simplenews_last_interaction', 'last interaction time updated successfully');
- }
|