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;
}