Recount existing nodes and comments

Here's a (crap) function that resets the points to zero, then counts up all existing nodes and comments on the site to generate the points. I don't know where (or if) you want to include this, so there's no patch yet.

It's probably trivial to rewrite this into a single SQL query, but I just wanted a quick way to give people points for their old posts after enabling this module on an existing site. I think it creates an accurate count, but I haven't really tested it.

function _userpoints_recount_points() {

  // reset the points
  
  db_query("UPDATE {userpoints} SET points = 0");

  // count how many nodes of each type users have posted.
  foreach (node_list() as $type) {
    $points = variable_get('userpoints_post_' . $type, '1');
    $sql = "SELECT COUNT(*) contribs, n.uid
            FROM {node} n
            LEFT JOIN {users} u ON n.uid = u.uid
            WHERE n.uid >0
            AND u.name != ''
            AND n.type = '$type'
            AND n.status =1
            GROUP BY u.uid
            LIMIT 1000";
    $node_result = db_query($sql);
    while ($row = db_fetch_array($node_result)) {
      $current_points = db_result(db_query('SELECT points FROM {userpoints} WHERE uid = %d', $row[uid]));
      $new_points = $current_points + ($points * $row[contribs]);
      db_query("UPDATE {userpoints} SET points = '%d', last_update = '%d' WHERE uid = %d", $new_points, time(), $row[uid]);
    }
  }

  // count how many comments users have posted
  $comment_sql = "SELECT COUNT(*) contribs, u.uid
                  FROM {comments} c
                  LEFT JOIN {users} u ON c.uid = u.uid
                  WHERE u.name != ''
                  GROUP BY u.uid
                  LIMIT 1000";
  $comment_result = db_query($comment_sql);
  $points = variable_get('userpoints_post_comment', '1');
  while ($row = db_fetch_array($comment_result)) {
    $current_points = db_result(db_query('SELECT points FROM {userpoints} WHERE uid = %d', $row[uid]));
    $comment_points = $current_points + ($points * $row[contribs]);
    db_query("UPDATE {userpoints} SET points = '%d', last_update = '%d' WHERE uid = %d", $comment_points, time(), $row[uid]);
  }
   
  print theme('page', $output);
}