/userpoints page needs an ability to display SUMed categories

This patch fixes part of the problem by removing the 'GROUP' portion of the db query. Since we're looking for a comprehensive list of points all users have in all categories, using GROUP returns a result set that's smaller than what we expect.

There's another issue though. The only way to get the complete list (after the patch is applied) is to click the /userpoints menu -- choosing "Display all" in the filter doesn't do anything.

Here's the chunk of code I modified (look for comments near the bottom).

/*
* Purpose: List the users and their point totals by all or by category
* Returns: HTML from the theme function
*/
function userpoints_list_users() {
  $tid = arg(1);

  $sql = "SELECT p.uid, u.name, p.points, p.tid, t.name as cat 
          FROM {userpoints} p INNER JOIN {users} u USING (uid) 
          LEFT JOIN {term_data} t ON p.tid = t.tid
          ";

  //Check for filtering
  if (is_numeric($tid) && $tid == 0) {
    $sql .= "WHERE p.tid IS NULL OR p.tid = ''";
    $cat = t('!Uncategorized', userpoints_translation()); 
  }
  elseif (is_numeric($tid)) {
    $sql .= "WHERE p.tid = %d";
    $cat = db_result(db_query("SELECT name from {term_data} WHERE tid = %d", $tid));
  }
  else {
    $cat = t('All');
  }

  $sql_cnt = "SELECT COUNT(DISTINCT(uid)) 
              FROM {userpoints} 
              WHERE tid = %d
              ";

  if (variable_get(USERPOINTS_REPORT_DISPLAYZERO, 1) == 0 ) {
    //The user would NOT like to see users with zero points
    $sql .= " AND p.points <> 0";
    $sql_cnt .= " AND points <> 0";
  }
  // Grouping causes problems when "Display all" filter is applied.
  // It doesn't seem to be necessary otherwise.
  // $sql .= " GROUP BY p.uid, u.name, p.points";
  $header = theme('userpoints_list_users_header');
  $sql .= tablesort_sql($header);
  $pager_limit = variable_get(USERPOINTS_REPORT_USERCOUNT, 30);
  $result = pager_query($sql, $pager_limit, 0, $sql_cnt, $tid);
  while ($data = db_fetch_object($result)) {
    $rows[] = theme('userpoints_list_users_row', $data);
  }

  drupal_set_title(t($cat) ." ". t("!points", userpoints_translation()));
  return theme('userpoints_list_users', $rows, $tid, $pager_limit);
}

Khalid,

After I submitted the patch, I realized that "users by points" should indeed be a list of all users and a summary of the points (across all categories) that they've earned.

So, I guess two approaches/queries are necessary (similar to the 'all' solution we discussed the other day).

1) if no tid is passed, the query should include a SUM() to aggregate the category totals.

2) if a tid is passed, the existing query works to list an individual category and the points each users have earned in that category.

Sounds good Jacob.

Thanks for taking a look at this, committing the fix, and adding this to the requested feature list.

Kevin