function user_prune_build_sql in User Prune 7

Builds the sql based on the form value settings provided.

Parameters

$values: Form field values that will determine if anything is going to be left out.

Return value

string The SQL string that will be used to query the database and find the users that need to be trimed.

2 calls to user_prune_build_sql()

user_prune_delete in ./user_prune.module

Deleting the users who matching criteria.

user_prune_preview in ./user_prune.module

Collecting information for preview table.

File

./user_prune.module, line 522

Module API The main module file. Implementing forms and functions.

Code

function user_prune_build_sql($values) {

  // Filter on user created time.
  $logged_in_limit = time() - $values['user_prune_time_year'] - $values['user_prune_time_month'] - $values['user_prune_time_day'] - $values['user_prune_time_hour'] - $values['user_prune_time_minute'];
  $sql = 'SELECT u.uid AS uid, name AS Name, created AS Created, access AS Lastaccess, login AS Lastlogin, status AS Status FROM {users} u WHERE created < ' . $logged_in_limit;

  // Option to filter only never logged in users.
  if ($values['user_prune_never_logged_in']) {
    $sql .= ' AND access = 0';
    $sql .= ' AND login  = 0';
  }
  else {
    $sql .= ' AND access < ' . $logged_in_limit;
    $sql .= ' AND login < ' . $logged_in_limit;
  }

  // Limit based on users active or blocked.
  switch ($values['user_prune_user_status']) {
    case 'blocked':
      $sql .= ' AND status = 0';
      break;
    case 'active':
      $sql .= ' AND status = 1';
      break;
  }

  // Make sure not to include power users.
  $sql .= ' AND uid <> 0 AND uid <> 1';

  // Filter based on user roles.
  $selected_roles = array_filter($values['user_prune_user_roles_checkbox']);
  foreach ($selected_roles as $rid) {
    $sql .= ' AND u.uid NOT IN (SELECT r.uid FROM {users_roles} r WHERE rid = ' . $rid . ')';
  }

  // Filter on out users who have added a comment.
  if ($values['user_prune_no_comment'] == 1) {
    $sql .= ' AND u.uid NOT IN (SELECT c.uid FROM {comment} c)';
  }

  // Filter out users who have created a node.
  if ($values['user_prune_no_node'] == 1) {
    $sql .= ' AND u.uid NOT IN (SELECT d.uid FROM {node} d)';
  }

  // Add subscription query if module exists.
  if (module_exists('subscriptions')) {

    // Filter out users who have a subscription.
    if ($values['user_prune_no_subscriptions'] == 1) {
      $sql .= ' AND u.uid NOT IN (SELECT s.recipient_uid FROM {subscriptions} s)';
    }
  }

  // Add groups query if module exists.
  if (module_exists('og')) {

    // Filter out users who have a og.
    if ($values['user_prune_no_og'] == 1) {
      $sql .= ' AND u.uid NOT IN (SELECT ogm.etid FROM {og_membership} ogm)';
    }
  }

  // Add entityform query if module exists.
  if (module_exists('entityform')) {

    // Filter out users who have a entityform.
    if ($values['user_prune_no_entityform'] == 1) {
      $sql .= ' AND u.uid NOT IN (SELECT ef.uid FROM {entityform} ef)';
    }
  }

  // Add tmgmt query if module exists.
  if (module_exists('tmgmt')) {

    // Filter out users who have a tmgmt.
    if ($values['user_prune_no_tmgmt'] == 1) {
      $sql .= ' AND u.uid NOT IN (SELECT tj.uid FROM {tmgmt_job} tj)';
    }
  }

  // Limit batches.
  $sql .= ' ORDER BY uid limit ' . $values['user_prune_prune_per_run'];
  return $sql;
}