mailinglist_archive.module

Go to the documentation of this file.

<?php

define('MAILINGLIST_ARCHIVE_VERSION', 1);

function mailinglist_archive_permission() {
  return array(
    'administer mailinglist archive' => array(
      'title' => t('Administer Mailinglist Archives'),
    ),
  );
}

function mailinglist_archive_cron() {

  $lists = mailinglist_list_load_all(false);
  foreach ($lists as $index => $list) {
    if (isset($list->operations['archive']) && $list->operations['archive']) {
      _mailinglist_archive_scan($list);
    }
  }
}

function mailinglist_archive_cron_queue_info() {
  $queue = array(
    'mailinglist_archive_thread' => array(
      'worker callback' => 'mailinglist_archive_thread_callback',
      'time' => '1',
    ),
  );
  return $queue;
}


function mailinglist_archive_ctools_plugin_directory($owner, $plugin_type) {
  return 'plugins/' . $owner . '/' . $plugin_type;
}

function mailinglist_archive_theme($existing, $type, $theme, $path) {
  if($type != 'module') {
    return array();
  }
  return array(
    'mailinglist_archive_form' => array(
      'render element' => 'element',
      'file' => 'mailinglist_archive_theme.inc',
    ),
    'mailinglist_archive_form_header' => array(
      'render element' => 'element',
      'file' => 'mailinglist_archive_theme.inc',
    ),
    'mailinglist_summary' => array(
      'render element' => 'element',
      'file' => 'mailinglist_archive_theme.inc',
    ),
    'mailinglist_thread' => array(
      'render element' => 'element',
      'file' => 'mailinglist_archive_theme.inc',
    ),
    'mailinglist_message' => array(
      'render element' => 'element',
      'file' => 'mailinglist_archive_theme.inc',
    ),
    'mailinglist_body' => array(
      'render element' => 'element',
      'file' => 'mailinglist_archive_theme.inc',
    ),
  );
}

function mailinglist_archive_node_info() {
  return array(
    'mailinglist_archive' => array(
      'name' => 'Mailing List Archive Message',
      'base' => 'mailinglist_message',
      'description' => 'Message archived for a mailing ist',
      'help' => '(opt) Help displayed when creating nodes',
      'has_title' => TRUE,  //(or FALSE) does type have a "title" FIELD.
      'title_lable' => 'Subject',
      'locked' => TRUE, //(or FALSE), can machine name be changed in admin interface
    ),
  );
}

/*******************************************************************************
 *
 */

function mailinglist_message_form($node, &$form_state) {
  dpm($node);
  return node_content_form($node, $form_state);
}

function mailinglist_message_insert($node){
  dpm($node);
}

function mailinglist_message_update($node) {
  dpm($node);
}

function mailinglist_message_delete($node) {
  dpm($node);
}

function mailinglist_message_load($nodes) {
  dpm($node);
/*
$nodes is an array, fill in the elements with the node data based on the keys.

Something like:
 $result = db_query('SELECT nid, foo FROM {mytable} WHERE nid IN (:nids)', array(':nids' => array_keys($nodes)));
  foreach ($result as $record) {
    $nodes[$record->nid]->foo = $record->foo;
  }
*/
}


function _mailinglist_archives_list_schema($list) {
  $list_name = $list->admin_name;
  $list_schema = array();
  $list_schema['mailinglist_archives__' . $list_name] = array(
    'description' => 'Table for holding the text of the messages',
    'fields' => array(
      'mid' => array(
        'type' => 'serial',
        'unsigned' => TRUE,
        'not null' => TRUE,
        'description' => 'Primary Key for table',
      ),
      'msg_id' => array(
        'type' => 'varchar',
        'length' => '1024',
        'description' => 'Message ID from message',
        'not null' => TRUE,
      ),
      'reply_to' => array(
        'type' => 'int',
        'unsigned' => TRUE,
        'description' => 'mid of message we are a reply to',
      ),
      'thread_id' => array(
        'type' => 'int',
        'unsigned' => TRUE,
        'not null' => TRUE,
        'description' => 'mid of first message of thread',
        'default' => 0, // Actually = mid, but we can't tell sql that
      ),
      'time' => _mailinglist_datetime_schema('Time message was posted'),
      'subject' => array(
        'type' => 'varchar',
        'length' => '2048',
        'description' => 'Subject of Message',
        'not null' => TRUE,
      ),
      'year' => array(
        'type' => 'int',
        'size' => 'small',
        'not null' => TRUE,
      ),
      'month' => array(
        'type' => 'int',
        'size' => 'tiny',
        'not null' => TRUE,
      ),
      'day' => array(
        'type' => 'int',
        'size' => 'tiny',
        'not null' => TRUE,
      ),
      'hour' => array(
        'type' => 'int',
        'size' => 'tiny',
        'not null' => TRUE,
      ),
      'author' => array(
        'type' => 'int',
        'unsigned' => TRUE,
        'not null' => TRUE,
        'description' => 'Index to Author\'s ID in mailinglist_names'
      ),
      'author_email' => array(
        'type' => 'varchar',
        'length' => 255,
        'not null' => TRUE,
      ),
      'updated' => _mailinglist_datetime_schema(
        array(
          'description' => 'Time message was last updated (Debug)',
          'not null' => TRUE,
          'default' => '0000',
        )
      ),
      'body' => array(
        'type' => 'text',
      ),
      'headers' => array(
        'type' => 'text',
        'serialize' => TRUE,
      ),
      'raw_body' => array(
        'type' => 'text',
      ),
      'status' => array(
        'type' => 'int',
        'description' => 'published status, 0 = published, 1 = moderation queue, 2=waiting reply, 3=rejected',
        'not null' => TRUE,
        'default' => 0,
      ),
      'version' => array(
        'type' => 'int',
        'description' => 'Version of the record (for development)',
        'not null' => TRUE,
        'default' => 0,
      ),
      // ?? digest flag, topic flags
    ),
    'primary key' => array('mid'),
    'unique keys' => array(
      'msg_id' => array(array('msg_id', 100)),
    ),
    'indexes' => array(
      'time' => array('time'),
      'thread' => array('thread_id', 'time'),
      'subject' => array(array('subject', 255), 'time'),
      'author' => array('author', 'time'),
      'author_email' => array('author_email'),
      'hour' => array('hour'),
      'reply_to' => array('reply_to', 'time'),
      'time_parsed' => array('year', 'month', 'day'),
      'updated' => array('updated', 'time', 'version'),
      ),
    'foreign keys' => array(
      'reply_to' => array(
        'table' => 'mailinglist_archives__' . $list_name,
        'columns' => array('reply_to' => 'mid'),
      ),
      'thread_id' => array(
        'table' => 'mailinglist_archives__' . $list_name,
        'columns' => array('thread_id' => 'mid'),
      ),
      'author' => array(
        'table' => 'mailinglist_names',
        'columns' => array('author' => 'key'),
      ),
    ),
  );
  $list_schema['mailinglist_archives_references__' . $list_name]  = array(
    'description' => 'Table to track the references of a message',
    'fields' => array(
      'mid' => array(
        'type' => 'serial',
        'unsigned' => TRUE,
        'not null' => TRUE,
        'description' => 'mid of message these are references to',
      ),
      'idx' => array(
        'type' => 'int',
        'unsigned' => TRUE,
        'not null' => TRUE,
        'description' => 'Sequence number for which ref this is for the meesage'
      ),
      'ref_mid' => array(
        'type' => 'int',
        'unsigned' => TRUE,
        'description' => 'mid of message we are a reply to',
      ),
      'ref_msg_id' => array(
        'type' => 'varchar',
        'length' => '2048',
        'not null' => TRUE,
        'description' => 'Message ID of message we are a reply to',
      ),
    ),
    'primary key' => array('mid', 'idx'),
    'indexes' => array(
      'ref_mid' => array('ref_mid'),
      'ref_msg_id' => array(array('ref_msg_id', 40)),
    ),
    'foreign keys' => array(
      'mid' => array(
        'table' => 'mailinglist_archives__' . $list_name,
        'columns' => array('mid' => 'mid'),
      ),
      'ref_mid' => array(
        'table' => 'mailinglist_archives__' . $list_name,
        'columns' => array('ref_mid' => 'mid'),
      ),
      'ref_msg_id' => array(
        'table' => 'mailinglist_archives__' . $list_name,
        'columns' => array('ref_msg_id' => 'msg_id'),
      ),
    ),
  );
  $list_schema['mailinglist_archives_thread__' . $list_name] = array(
    'description' => 'Table for holding information about threads',
    'fields' => array(
      'thread_id' => array(
        'type' => 'int',
        'unsigned' => TRUE,
        'not null' => TRUE,
        'description' => 'mid of first message of thread',
      ),
      'start_time' => _mailinglist_datetime_schema(array(
        'description' => 'Time thread was started',
        'not null' => TRUE,
      )),
      'last_time' => _mailinglist_datetime_schema(array(
        'description' => 'Time thread was last posted to',
        'not null' => TRUE,
      )),
    ),
    'primary key' => array('thread_id'),
    'indexes' => array(
      'times' => array('start_time', 'last_time'),
    ),
    'foreign keys' => array(
      'mid' => array(
        'table' => 'mailinglist_archives__' . $list_name,
        'columns' => array('thread_id' => 'thread_id'),
      ),
    ),
  );
  // $list_schema['mailinglist_archives_topics__ . $list_name]

  $list->schema($list_schema, 'archive');
  return $list_schema;
}

function mailinglist_archive_message_add($msg, $list) {
  $listname = $list -> admin_name;
  $table = 'mailinglist_archives__' . $listname;
  $table_ref = 'mailinglist_archives_references__' . $listname;
  $msg_id = $msg->header('Message-ID');
  $from = $msg->headerEmail('From');
  $subject = $msg->header('Subject');
  $time = $msg->headerNoComments('Date');
  $refs = $msg->header('References');
  $refrply = $msg->headerNoComments('In-Reply-To');
  $status = $msg->header('XX-Status');

  if (!is_array($refs)) {
    if (empty($refs)) {
      if (empty($refrply)) {
        $refs = array();
      } else {
        $refs = array($refrply);
      }
    } else {
      $refs = explode(' ',  $refs);
    }
  }

  // $transaction = db_transaction();
  // See if message exists.
  $sql = 'SELECT * FROM {' . $table .
          '} WHERE msg_id = :msgid';
  $result = db_query($sql, array(':msgid' => $msg_id)) -> fetchAssoc();
  dpm($result, 'Old Version');
  $incr = empty($result);

  $nameid = mailinglist_email_name($from['email'], $from['name'], $incr);
  $headers = serialize($msg->headers());
  $body = $msg->body();
  $raw_body = $msg->raw();
  $time = str_replace(array("US/Mountain"), array("America/Denver"), $time);
  $ts = strtotime($time);
  if($ts < strtotime('1980-01-01')) {
    $list->log('Bad Date: ' . $time, $list::LOG_ERROR);
  }
  $info = array (
    'msg_id' => $msg_id,
    'reply_to' => 0,  // will be filled in later if needed
    'thread_id' => 0, // will be filled in later
    'time' => _mailinglist_datetime_format($ts),
    'year' => (int)date('Y', $ts),
    'month' => (int)date('m', $ts),
    'day' => (int)date('d', $ts),
    'hour' => (int)date('h', $ts),
    'subject' => substr($subject, 0, 255),
    'author' => $nameid,
    'author_email' => $from['email'],
    'body' => $body,
    'headers' => $headers,
    'raw_body' => $raw_body,
    'status' => (int)$status,
    'updated' => _mailinglist_datetime_format(time()),
    'version' => MAILINGLIST_ARCHIVE_VERSION,
  );
  try {
    if (empty($result)) {
      dpm($info, 'New Record');
      // New Record
      $mid = db_insert($table) ->fields($info) -> execute();
      $info['thread_id'] = $mid;
    } else {
      // Updating record
      $info['status'] = min($info['status'], (int)$result['status']);
      $mid = $result['mid'];
      $info['mid'] = $mid;
      if(($info['thread_id'] == 0) or ($info['reply_to'] == 0)) {
        $info['thread_id'] = $mid;
      }
    }
    dpm($info, 'Update Record');
    db_update($table) -> fields($info) -> condition('mid', $mid) -> execute();



    // Build up Reference Table
    foreach ($refs as $idx => $ref) {
      // See if $ref is a known msg_id
      $sql = 'SELECT mid FROM {' . $table . '} WHERE msg_id = :msgid';
      $result = db_query($sql, array(':msgid' => $ref)) -> fetchAssoc();
      if ($result) {
        $ref_mid = $result['mid'];
      } else {
        $ref_mid = NULL;
      }

      $info_ref = array(
        'mid' => $mid,
        'idx' => $idx,
        'ref_mid' => $ref_mid,
        'ref_msg_id' => $ref,
      );
      dpm($info_ref, 'Reference');
      $sql = 'SELECT * FROM {' . $table_ref . '} WHERE mid = :mid AND idx = :idx';
      $result = db_query($sql, array(':mid' => $mid, ':idx' => $idx)) ->fetchAssoc();
      if ($result) {
        db_update($table_ref) -> fields($info_ref) -> condition('mid', $mid) ->
          condition('idx', $idx) -> execute();
      } else {
        db_insert($table_ref) -> fields($info_ref) -> execute();
      }
    }

    mailinglist_archive_message_ref_id($list, $mid, $msg_id);
    mailinglist_archive_message_link($list, $mid);
  } catch (Exception $e) {
    watchdog('mailinglist', 'Exception in mailinglist_archive_message_add: @e ',
      array('@e' => print_r($e, true)), WATCHDOG_ERROR);
    return;
  }
}

function mailinglist_archive_message_link($list, $mid) {
  $listname = $list -> admin_name;
  $table = 'mailinglist_archives__' . $listname;
  $table_ref = 'mailinglist_archives_references__' . $listname;

  $reply_to = 0;
  $thread_id = $mid;

  // Find lowest index item that we have a message to it.
  $sql = 'SELECT ref_mid
    FROM {' . $table_ref . '} as r, {' . $table . '} as m
    WHERE r.mid = :mid AND r.ref_mid = m.mid and m.status = 0
    ORDER BY r.idx ASC LIMIT 1';
  $result = db_query($sql, array(':mid' => $mid)) -> fetchAssoc();
  dpm($result, 'Link Info');
  if ($result) {
    $reply_to = $result['ref_mid'];
    $sql = 'SELECT * FROM {' . $table . '} WHERE mid = :mid';
    $result = db_query($sql, array(':mid' => $reply_to)) -> fetchAssoc();
    $thread_id = $result['thread_id'];
  } else {
    $reply_to = 0;
    $thread_id = $mid;
  }
  $info = array('reply_to' => $reply_to, 'thread_id' => $thread_id);
  db_update($table) -> fields($info) -> condition('mid', $mid) -> execute();
  dpm($info, 'Link');

  $sql = 'SELECT MIN( `time` ) AS start_time, MAX( `time` ) AS last_time
    FROM {mailinglist_archives__' . $listname . '}
    WHERE thread_id =:tid
    GROUP BY thread_id';
  $result = db_query($sql, array(':tid' => $thread_id)) -> fetchAssoc();
  dpm($result, 'Threading');

  db_merge('mailinglist_archives_thread__' . $listname)
    -> key(array('thread_id' => $thread_id))
    -> fields($result)
    -> execute();

  return;
}

function mailinglist_archive_message_ref_id($list, $mid, $msgid) {
  $listname = $list -> admin_name;
  $table_ref = 'mailinglist_archives_references__' . $listname;

  // Find all archive_references that reference this msgid
  $sql = 'SELECT * FROM {' . $table_ref . '} WHERE ref_msg_id = :msgid';
  $result = db_query($sql, array(':msgid' => $msgid));
  while($row = $result -> fetchAssoc()) {
    // Does it currently point to the right mid?
    if ($row['ref_mid'] != $mid) {
      // No. Update reference and message it is a reference for.
      $ref_mid = $row['mid'];
      $ref_idx = $row['idx'];
      $info_ref = array(
        'ref_mid' => $mid,
      );
      db_update($table_ref) -> fields($info_ref) -> condition('mid', $ref_mid) ->
        condition('idx', $ref_idx) -> execute();
      mailinglist_archive_message_link($list, $ref_mid);
    }
  }
}

function mailinglist_archive_summary_list($list, $vars, $cond = '', $order = "", $limit = "") {
  $listname = $list->admin_name;
  $tablename = 'mailinglist_archives__' . $listname;
  $base = variable_get('mailinglist_base', 'mailinglist');

  if(!is_array($vars)) {
    if(is_numeric($vars) && $cond == '') {
      $cond = $vars;
    }
    $vars = array();
  }

  if(is_numeric($cond)){
    $vars[':mid'] = $cond;
    $cond = 'a.mid = :mid';
  }

  if(empty($order)) {
    $order = 'ORDER BY a.time ASC';
  }

  if(empty($limit)) {
    $limit = 100;
  }
  if(is_numeric($limit)) {
    $limit = "LIMIT $limit";
  }

  $sql = 'Select * FROM
    {' . $tablename . '} AS a
    LEFT JOIN {mailinglist_names} AS n ON a.author = n.name_key';
  if(!empty($cond)) {
    $sql .= ' WHERE ' . $cond;
  }
  $sql .= ' ' . $order . ' ' . $limit;
//  dpm($vars, $sql);
  $data = array(
    '#theme' => 'mailinglist_thread',
    '#type' => 'item',
    '#level' => 0,
    'items' => array(),
  );

  $result = db_query($sql, $vars);

  while($row = $result -> fetchAssoc()) {
    $data['items'][] = array(
      '#theme' => 'mailinglist_summary',
      '#type' => 'item',
      '#date' => _mailinglist_datetime_ts($row['time']),
      'subject' => array('#markup' => $row['subject']),
      'email' => array('#markup' => $row['email']),
      'name' => array('#markup' => $row['name']),
      'url' => array('#item' => "$base/$listname/archive/msg/" . $row['mid']),
//      'mid' => array('#markup' => $row['mid'], '#printed' => TRUE),
//      'reply_to' => array('#markup' => $row['reply_to'], '#printed' => TRUE),
      '#mid' => $row['mid'],
      '#reply_to' => $row['reply_to'],

    );
  }
  return $data;
}

function mailinglist_archive_reply_tree($list, $msgid, $n = 0) {
  $replies =  mailinglist_archive_summary_list($list, array(':mid' => $msgid),
          'a.reply_to = :mid', 'ORDER BY a.time ASC');
  $replies['#level'] = $n;
if($n < 5) {
  foreach($replies['items'] as $key => &$item) {
    $item['reply_to'] = mailinglist_archive_reply_tree($list, $item['#mid'], $n+1);
  }
}
//dpm($replies, 'Reply Tree');
  return $replies;
}

function mailinglist_archive_message_get($list, $msgid) {
  $tablename = 'mailinglist_archives__' . $list->admin_name;
  $sql = 'SELECT * FROM {' . $tablename. '} AS a,
    {mailinglist_names} as n
    WHERE a.mid = :mid AND a.author = n.name_key';
  $result = db_query($sql, array(':mid' => $msgid)) -> fetchAssoc();
  return $result;
}

function mailinglist_archive_mailinglist_message($msg, $list) {
  if ($msg->header('XX-Archive')) {
    mailinglist_archive_message_add($msg, $list);
  }
}

function _mailinglist_archive_scan($list) {
  $queue = DrupalQueue::get('mailinglist_message');
  if($queue->numberOfItems() > 0) {
    // Don't repair if importing, or behind
    return;
  }

  $listname = $list->admin_name;

  $sql = 'SELECT * FROM {mailinglist_archives__' . $listname . '}
    ORDER BY updated ASC, time ASC LIMIT 10';
  $result = db_query($sql, array());
  while($row = $result -> fetchAssoc()) {
    dpm($row, 'Refresh Scan');
    $msg = $row['raw_body'];

    $msg = new MailinglistMessage($msg);

    $from = $msg->header('From');
    if (is_array($from)) {
      $from = $from[0];
    }
    // Fix up obfuscated from line
    if (strpos($from, '@') == FALSE) {
      // obfuscated from line from archives
      $from = str_replace(' at ', '@', $from);
      $msg->setHeader('From', $from);
    }

    // resumbit message
    $msg->setHeader('XX-List', $list->admin_name);
    $msg->setHeader('XX-Archive', '1');
    $msg->setHeader('XX-Status', $row['status']);
    $list->processMessage($msg);
  }
}

function mailinglist_archive_thread_callback($thread) {
}