materio-base-d7/sites/all/modules/contrib/mail/mandrill_simplenews_report/mandrill_simplenews_report.module

<?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');
}