You are here

function casetracker_cases_overview in Case Tracker 5

Menu callback; displays a list of all cases in a table. See the README.txt for the various URLs we support.

The "design" behind $project_filters and $case_filters has been inspired by the search.module, and we hope to eventually use this function as a frontend to that feature, once we actually recode it over again.

Parameters

$project_filters: Whether 'all' or only 'my' (current user) project cases are shown. Any numbers passed are considered project node IDs. Multiple filters can be passed through by space-separating them.

$case_filters: 'all', 'my', or 'assigned' cases from the project filter and/or various keyed filters that are explained in the README.txt.

1 string reference to 'casetracker_cases_overview'
casetracker_menu in ./casetracker.module
Implementation of hook_menu().

File

./casetracker.module, line 711
Enables the handling of projects and their cases.

Code

function casetracker_cases_overview($project_filters = 'all', $case_filters = 'all') {
  drupal_set_breadcrumb(array(
    l(t('Home'), NULL),
    l(t('Case Tracker'), 'casetracker'),
    l(t('All cases'), 'casetracker/cases'),
  ));
  drupal_add_css(drupal_get_path('module', 'casetracker') . '/casetracker.css');
  $output = NULL;
  $headers = array(
    array(
      'data' => t('#'),
      'field' => 'cc.case_number',
    ),
    array(
      'data' => t('Title'),
      'field' => 'n.title',
    ),
    array(
      'data' => t('Last updated'),
      'field' => 'ncs.last_comment_timestamp',
      'sort' => 'desc',
    ),
    array(
      'data' => t('Priority'),
      'field' => 'cc.case_priority_id',
    ),
    array(
      'data' => t('Status'),
      'field' => 'cc.case_status_id',
    ),
    array(
      'data' => t('Type'),
      'field' => 'cc.case_type_id',
    ),
    array(
      'data' => t('Assigned to'),
      'field' => 'cc.assign_to',
    ),
  );

  // ah, the joys of filtering data based upon URL arguments. we try to base
  // everything around one "master" SQL query, and add in filterable WHERE
  // clauses ($case_filter_sql) and arguments ($case_filter_args) when needed.
  global $user;

  // I DON'T LIKE HOW YOU MAKE ME FEEL! PLEASE STOP IT! AAHHhHHHHHHh!
  $caseTypes = _casetracker_getCaseTypes();
  $case_filter_args = strpos($case_filters, 'type') !== FALSE ? array() : $caseTypes;
  $case_filter_sql = strpos($case_filters, 'type') !== FALSE ? NULL : array(
    'n.type IN (' . str_pad('', count($caseTypes) * 5 - 1, "'%s',") . ')',
  );
  $case_filter_explanation = array();

  // human readable explanation of filters.
  // first up is the project_filter. see README.txt about URLs.
  // rather simple here - just "all", "my", and/or project nid(s).
  if (strpos('all', $project_filters) === FALSE) {

    // no filtering on 'all'
    $project_filter_nids = array();

    // merged into case_filter_args and _sql.
    $project_filter_parts = preg_split('/\\s+/', $project_filters);
    foreach ($project_filter_parts as $project_filter) {
      if ($project_filter == 'my') {
        $project_filter_args = array_filter(variable_get('casetracker_project_node_types', array(
          'casetracker_basic_project',
        )));
        $project_filter_args[] = $user->uid;
        $results = db_query('SELECT n.nid FROM {node} n LEFT JOIN {casetracker_project} cp ON (n.vid = cp.vid) WHERE n.type IN (' . str_pad('', count(array_filter(variable_get('casetracker_project_node_types', array(
          'casetracker_basic_project',
        )))) * 5 - 1, "'%s',") . ') AND n.uid = %d AND n.status = 1', $project_filter_args);
        while ($result = db_fetch_object($results)) {
          $project_filter_nids[] = $result->nid;
        }
        $case_filter_explanation[] = t('my projects');
      }
      else {

        // probably project node ID(s).
        $project_filter_values = explode(',', $project_filter);
        foreach ($project_filter_values as $project_filter_value) {
          if (!is_numeric($project_filter_value)) {
            continue;
          }
          $project_filter_nids[] = $project_filter_value;
          $case_filter_explanation[] = t('project %title', array(
            '%title' => db_result(db_query('SELECT title FROM {node} n WHERE n.nid = %d', $project_filter_value)),
          ));
        }
      }
    }

    // project filtering is finished, so merge into mastah...
    if (count($project_filter_nids) >= 1) {

      // ... but only if values.
      $case_filter_args = array_merge($case_filter_args, $project_filter_nids);
      $case_filter_sql[] = 'cc.pid IN (' . str_pad('', count($project_filter_nids) * 5 - 1, "'%s',") . ')';
    }
  }
  else {
    $case_filter_explanation[] = t('all projects');
  }

  // determine the projects part of the page title based on our criteria.
  $title_project_filters = t('filtered projects');

  // just a generic default if we can't think of anything better.
  if (is_numeric($project_filters)) {
    $title_project_filters = db_result(db_query('SELECT title FROM {node} n WHERE n.nid = %d', $project_filters));
  }
  elseif ($project_filters == 'all' || $project_filters == 'my') {
    $title_project_filters = t('!project_filters projects', array(
      '!project_filters' => t($project_filters),
    ));
  }

  // case filters are up next - we support keyed and unkeyed filters
  // here, so have to loop a few more times to get it right and uber.
  //   EXAMPLE: "type:casetracker_basic_case my author:4"
  //   UNKEYED FILTERS: all, my, assigned
  //     KEYED FILTERS: assigned author state type
  if (strpos($case_filters, 'all') === FALSE) {

    // no filtering on 'all'.
    $case_filter_parts = preg_split('/\\s+/', $case_filters);
    asort($case_filter_parts);
    foreach ($case_filter_parts as $case_filter_part) {
      $case_filter = explode(':', $case_filter_part);

      // if value exists, this is a keyed filter
      // like state:15,16 or similar. README.txt.
      if (isset($case_filter[1])) {
        $case_filter_values = explode(',', $case_filter[1]);
        $case_filter_values = array_unique($case_filter_values);
        if ($case_filter[0] == 'assigned') {
          $assigned_uids = array();

          // numbers from input only.
          foreach ($case_filter_values as $case_filter_value) {
            if (!is_numeric($case_filter_value)) {
              continue;
            }
            $assigned_uids[] = $case_filter_value;
            $case_filter_args[] = $case_filter_value;
            $case_filter_explanation[] = t('assigned to %user', array(
              '%user' => db_result(db_query('SELECT name FROM {users} u WHERE u.uid = %d', $case_filter_value)),
            ));
          }

          // we do this out here with assigned_uids to make sure they're all numeric.
          $case_filter_sql[] = 'cc.assign_to IN (' . str_pad('', count($assigned_uids) * 5 - 1, "'%s',") . ')';
        }
        if ($case_filter[0] == 'author') {
          $author_uids = array();

          // numbers from input only.
          foreach ($case_filter_values as $case_filter_value) {
            if (!is_numeric($case_filter_value)) {
              continue;
            }
            $author_uids[] = $case_filter_value;
            $case_filter_args[] = $case_filter_value;
            $case_filter_explanation[] = t('created by %user', array(
              '%user' => db_result(db_query('SELECT name FROM {users} u WHERE u.uid = %d', $case_filter_value)),
            ));
          }

          // we do this out here with author_uids to make sure they're all numeric.
          $case_filter_sql[] = 'n.uid IN (' . str_pad('', count($author_uids) * 5 - 1, "'%s',") . ')';
        }

        // what follows is an edge case where the more sensible thing is to OR the queries,
        // not AND. it's more useful to find (all items by a certain uid OR assigned to a
        // certain uid) as opposed to (all items by a certain uid AND assigned to a certain
        // uid). we'll sniff for these sorts of requests and mutilate our SQL array to OR
        // instead of AND. A similar edge case is required for "my" and "assigned" in the
        // unkeyed filters below. NOTE: we check against "author" first so that we know
        // we've processed all relevant (and alphabetically stored) case filters.
        if ($case_filter[0] == 'author' && strpos($case_filters, 'author:') !== FALSE && strpos($case_filters, 'assigned:') !== FALSE) {
          $sql_2 = array_pop($case_filter_sql);
          $sql_1 = array_pop($case_filter_sql);
          $case_filter_sql[] = '(' . $sql_1 . ' OR ' . $sql_2 . ')';

          // and make a new one.
        }
        if ($case_filter[0] == 'state') {
          $state_ids_by_realm = array();
          $state_sql = array();
          $state_args = array();
          foreach ($case_filter_values as $case_filter_value) {
            if (!is_numeric($case_filter_value)) {
              continue;
            }
            $state = casetracker_case_state_load(NULL, $case_filter_value);
            $state_ids_by_realm[$state['realm']][] = $case_filter_value;
            $case_filter_explanation[] = t('case %realm %name', array(
              '%realm' => $state['realm'],
              '%name' => $state['name'],
            ));
          }

          // turn our IDs into a happy OR query. laborious.
          foreach ($state_ids_by_realm as $realm => $state_ids) {
            $state_sql[] = 'cc.case_' . $realm . '_id IN (' . str_pad('', count($state_ids) * 5 - 1, "'%s',") . ')';
            $state_args = array_merge($state_args, $state_ids);
          }

          // and finally add them to our master query, so...
          if ($state_sql) {

            // make sure there's something there.
            $case_filter_sql[] = '(' . implode(' AND ', $state_sql) . ')';
            $case_filter_args = array_merge($case_filter_args, $state_args);
          }
        }
        if ($case_filter[0] == 'type') {
          $valid_node_types = array();
          $all_node_types = node_get_types('names');

          // for human readable names.
          foreach ($case_filter_values as $case_filter_value) {
            if (isset($caseTypes[$case_filter_value])) {
              $valid_node_types[] = $case_filter_value;
              $case_filter_args[] = $case_filter_value;
              $case_filter_explanation[] = t('node type %type', array(
                '%type' => $all_node_types[$case_filter_value],
              ));
            }

            // we only want to search through node types that are valid casetracker case value-adds.
          }
          $case_filter_sql[] = 'n.type IN (' . str_pad('', count($valid_node_types) * 5 - 1, "'%s',") . ')';
        }
      }
      else {

        // unkeyed, currently only my or assigned.
        $case_filter_values = explode(',', $case_filter[0]);
        foreach ($case_filter_values as $case_filter_value) {
          if ($case_filter_value == 'assigned') {
            $case_filter_args[] = $user->uid;
            $case_filter_sql[] = 'cc.assign_to = %d';
            $case_filter_explanation[] = t('my assigned cases');
          }
          if ($case_filter_value == 'my') {
            $case_filter_args[] = $user->uid;
            $case_filter_sql[] = 'n.uid = %d';
            $case_filter_explanation[] = t('my opened cases');
          }

          // see the discussion about edge cases above under key filters. we can use the
          // in_array here instead of strpos since unkeyed filters are their own index.
          if ($case_filter_value == 'my' && in_array('assigned', $case_filter_parts) && in_array('my', $case_filter_parts)) {
            $sql_2 = array_pop($case_filter_sql);
            $sql_1 = array_pop($case_filter_sql);
            $case_filter_sql[] = '(' . $sql_1 . ' OR ' . $sql_2 . ')';

            // and make a new one.
          }
        }
      }
    }
  }
  else {
    $case_filter_explanation[] = t('all cases');
  }

  // determine the cases part of the page title.
  $title_case_filters = t('filtered cases');

  // a generic default.
  if ($case_filters == 'all') {
    $title_case_filters = t('all cases');
  }
  elseif ($case_filters == 'my') {
    $title_case_filters = t('my opened cases');
  }
  elseif ($case_filters == 'assigned') {
    $title_case_filters = t('my assigned cases');
  }

  // and set the page title now that all filteres are handled.
  drupal_set_title(t('%case_filter in %project_filter', array(
    '%case_filter' => $title_case_filters,
    '%project_filter' => $title_project_filters,
  )));

  // now, with our filter arguments out of the way, actually run the query and go nutty.
  $case_filter_sql = count($case_filter_sql) ? 'AND ' . implode(' AND ', $case_filter_sql) : NULL;

  // make a final string of WHERE clauses.
  // create the querys
  $sql_select = 'SELECT DISTINCT(n.nid), n.title, ncs.last_comment_timestamp, cc.case_number, cc.case_priority_id, cc.case_status_id, cc.case_type_id, cc.assign_to, cp.project_number ';
  $sql_count = 'SELECT COUNT(DISTINCT(n.nid)) ';
  $sql_from = ' FROM {node} n LEFT JOIN {casetracker_case} cc ON (n.vid = cc.vid) LEFT JOIN {casetracker_project} cp ON (cp.nid = cc.pid) LEFT JOIN {node_comment_statistics} ncs ON (n.nid = ncs.nid) ';
  $sql_where = ' WHERE n.status = 1 ' . $case_filter_sql;
  $sql_data = db_rewrite_sql($sql_select . $sql_from . $sql_where) . tablesort_sql($headers);
  $sql_pager = db_rewrite_sql($sql_count . $sql_from . $sql_where);
  $results = pager_query($sql_data, 15, 0, $sql_pager, $case_filter_args);
  $rows = array();
  while ($result = db_fetch_object($results)) {
    $state_classes = '';
    $state_links = array();
    foreach (array(
      'priority',
      'status',
      'type',
    ) as $state) {
      $state_classes .= $state . '-' . preg_replace('/[^\\w\\-]/', '-', drupal_strtolower(casetracker_case_state_load($state, $result->{'case_' . $state . '_id'}))) . ' ';
      $state_links[$state] = strpos($case_filters, 'state') !== FALSE ? str_replace('state:', 'state:' . $result->{'case_' . $state . '_id'} . ',', $case_filters) : $case_filters . ' state:' . $result->{'case_' . $state . '_id'};
      $state_links[$state] = "casetracker/cases/{$project_filters}/" . str_replace('all ', '', $state_links[$state]);
    }
    $state_classes = rtrim($state_classes);

    // pedant: remove final space from the classes.
    $assign_to_display = $result->assign_to != 0 ? l(casetracker_get_name($result->assign_to), 'user/' . $result->assign_to, array(
      'title' => t('View user profile.'),
    )) : casetracker_get_name($result->assign_to);
    $rows[] = array(
      'data' => array(
        array(
          'data' => $result->project_number . '-' . $result->case_number,
          'class' => 'case-number',
        ),
        array(
          'data' => l($result->title, 'node/' . $result->nid),
          'class' => 'title',
        ),
        array(
          'data' => format_date($result->last_comment_timestamp, 'small'),
          'class' => 'last-updated',
        ),
        array(
          'data' => l(casetracker_case_state_load('priority', $result->case_priority_id), $state_links['priority']),
          'class' => 'priority',
        ),
        array(
          'data' => l(casetracker_case_state_load('status', $result->case_status_id), $state_links['status']),
          'class' => 'status',
        ),
        array(
          'data' => l(casetracker_case_state_load('type', $result->case_type_id), $state_links['type']),
          'class' => 'type',
        ),
        array(
          'data' => $assign_to_display,
          'class' => 'assign-to',
        ),
      ),
      'class' => $state_classes,
    );
  }
  if (count($rows) == 0) {
    $rows[] = array(
      array(
        'data' => t('No cases found.'),
        'colspan' => 7,
      ),
    );
  }

  // turn the filter explanations into a comma-spliced list for human readout. we use a filter
  // criteria AND a page title, because sometimes the criteria is too big to fit nicely into both.
  $output .= '<div id="case-filter-criteria"><span class="case-filter-title">' . t('Case filter criteria:') . '</span> ';
  if (count($case_filter_explanation) < 1) {
    $output .= t("Do you think you're being naughty?");
  }
  else {
    $output .= implode(', ', $case_filter_explanation) . '.';
  }
  $output .= '</div>';
  $output .= theme('table', $headers, $rows, array(
    'id' => 'casetracker-cases-overview',
  ));
  $output .= theme('pager', NULL, 15, 0);
  return $output;
}