function simplenews_statistics_update_6302 in Simplenews Statistics 6.3

Upgrade to 3.x continued.

File

./simplenews_statistics.install, line 225

Simplenews statistics (un)install and updates file.

Code

function simplenews_statistics_update_6302() {
  $ret = array();

  // Change the name from clicks tot total_clicks.
  $spec = array(
    'description' => 'Total clicks for this newsletter',
    'type' => 'int',
    'unsigned' => TRUE,
    'not null' => TRUE,
    'default' => 0,
  );
  db_change_field($ret, 'simplenews_statistics', 'clicks', 'total_clicks', $spec);

  // Change the name from opens tot total_opens.
  $spec = array(
    'description' => 'Total opens for this newsletter',
    'type' => 'int',
    'unsigned' => TRUE,
    'not null' => TRUE,
    'default' => 0,
  );
  db_change_field($ret, 'simplenews_statistics', 'opens', 'total_opens', $spec);

  // Add a field for the unique opens based on emailaddress.
  $spec = array(
    'description' => 'Emailaddress-unique opens for this newsletter',
    'type' => 'int',
    'unsigned' => TRUE,
    'not null' => TRUE,
    'default' => 0,
  );
  db_add_field($ret, 'simplenews_statistics', 'unique_opens', $spec);

  // Fill the field with data.
  $query = 'SELECT COUNT(email) AS unique_opens, nid FROM {simplenews_statistics_opens} GROUP BY nid';
  $result = db_query($query);
  while ($row = db_fetch_array($result)) {
    $query = 'UPDATE {simplenews_statistics} SET unique_opens=%d WHERE nid=%d';
    db_query($query, $row['unique_opens'], $row['nid']);
  }

  // Add a field for the unique click-throughs based on email and newsletter.
  $spec = array(
    'description' => 'Number of users who have clicked at least one link in the newsletter',
    'type' => 'int',
    'unsigned' => TRUE,
    'not null' => TRUE,
    'default' => 0,
  );
  db_add_field($ret, 'simplenews_statistics', 'user_unique_click_through', $spec);

  // Fill the field with data.
  $query = 'SELECT COUNT(email) AS unique_clicks, nid FROM {simplenews_statistics_clicks} GROUP BY nid';
  $result = db_query($query);
  while ($row = db_fetch_array($result)) {
    $query = 'UPDATE {simplenews_statistics} SET user_unique_click_through=%d WHERE nid=%d';
    db_query($query, $row['unique_clicks'], $row['nid']);
  }

  // Remove opens field and insert the number of opens as individual records.
  $result = db_query('SELECT email, nid, opens, timestamp FROM {simplenews_statistics_opens} WHERE opens > 1');
  while ($row = db_fetch_array($result)) {
    for ($i = 0; $i < $row['opens']; $i++) {
      $query = 'INSERT INTO {simplenews_statistics_opens} (email, nid, timestamp) VALUES ("%s", %d, %d)';
      db_query($query, $row['email'], $row['nid'], $row['timestamp']);
    }
  }
  db_drop_field($ret, 'simplenews_statistics_opens', 'opens');
  return $ret;
}