// the update to fix the old (never worked right) data.

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

  switch ($GLOBALS['db_type']) {
    case 'mysql':
    case 'mysqli':
      $ret[] = update_sql("DELETE FROM {sequences} WHERE name = 'case_id'");
      $ret[] = update_sql("DELETE FROM {sequences} WHERE name = 'project_no'");

      db_query('LOCK TABLES {casetracker_project} WRITE');
      $project_number = db_result(db_query_range("SELECT project_no FROM {casetracker_project} ORDER BY project_no DESC", 0, 1));
      $project_number = $project_number ? $project_number : 100; // if there are no projects, we always start at 100.
      variable_set('casetracker_current_project_number', $project_number);

      db_query('LOCK TABLES {casetracker_case} WRITE');
      // case numbers were supposed to be namespaced to the project, but that
      // never actually happened, so we'll loop through all the projects and
      // just start proper namespacing at whatever the last case number was.
      $results = db_query("SELECT p_id, MAX(case_id) FROM casetracker_case GROUP BY p_id    ");
      $project_case_numbers = array(); while ($result = db_fetch_object($results)) {
        $project_case_numbers[$result->p_id] = $result->case_id;
      } // i know, i know, serializing an array, but it's my cross to bare.
      variable_set('casetracker_current_case_numbers', $project_case_numbers);

      db_query('UNLOCK TABLES');
      break;
  }

  return $ret;
}

// and the "going forward" implementation in the module itself.
/**
 * Returns the next case number for use in a project. Case numbers are
 * unique to a project, so there will be multiple case number 100s, etc.
 * See also _casetracker_next_project_number(); no LOCK, so race conditions.
 *
 * @param $project_id
 *   The node ID of the project this case is assigned to.
 */
function _casetracker_next_case_number($project_id) {
  $project_case_numbers = variable_get('casetracker_current_case_numbers', $project_case_numbers);
  $case_number = $project_case_numbers[$project_id] + 1; // cases increment by one per project.
  variable_set('casetracker_current_case_numbers', $project_case_numbers);
  return $case_number;
}

/**
 * Returns the next project number for use. We don't use Drupal sequences here
 * because our projects increment by 100, not 1. We keep the latest value
 * stored as a variable so we don't have to worry about deletions/reuse.
 * There's no way we can LOCK here, so there is a race condition. However,
 * we didn't want to clutter up the sequences table with per-project case 
 * counters, which would be required if we forced that namespacing.
 */
function _casetracker_next_project_number() {
  $project_number = variable_get('casetracker_current_project_number', 0) + 100;
  variable_set('casetracker_current_project_number', $project_number);
  return $project_number;
}




// pastebin repaste
#
// the complete data set.
#
mysql> select nid, p_id, case_id from casetracker_case;
#
+-----+------+---------+
#
| nid | p_id | case_id |
#
+-----+------+---------+
#
|  22 |   13 |      13 |
#
|  23 |   14 |      14 |
#
|  21 |   13 |      12 |
#
+-----+------+---------+
#
3 rows in set (0.00 sec)
#
 
#
// the one i started with
#
mysql> select nid, p_id, case_id from casetracker_case group by p_id;
#
+-----+------+---------+
#
| nid | p_id | case_id |
#
+-----+------+---------+
#
|  22 |   13 |      13 |
#
|  23 |   14 |      14 |
#
+-----+------+---------+
#
2 rows in set (0.00 sec)
#
 
#
// your recommendations.
#
mysql> SELECT p_id, max(case_id) FROM casetracker_case GROUP BY p_id;
#
+------+--------------+
#
| p_id | max(case_id) |
#
+------+--------------+
#
|   13 |           13 |
#
|   14 |           14 |
#
+------+--------------+