howforge.com

My organization is trying to track incoming requests from outsiders and also insiders. The reason and only one reason is that me and my colleagues work so hard for nothing. Our main works are in the state of no progress for so long time. Actually, all works are late for two months already and they are not finished yet. The problem is my boss has seen we worked so hard but he have never known what we did and why all works are not finished on time.

That is the thing drove me to deploy a kind of issue ticket management system to track what we are doing all days all nights. There are so many softwares that may solve our problems. However, I chose Drupal and Case Tracker. It works almost perfect and we have used it for a week without problem. Actually, it does not meet our requirements yet. We also want to track performance of support teams. There are so many performance metrics. The first one is how long it take to solve each case. I decided to write a report using PHP input format in Page content type.

First of all, I tried to use Views module. It was almost perfect but it also lacked of interval since the case created until solved. As a result, I copied code from casetracker_cases_overview() with only limited filtering.

  $case_statuses = array(7,8);
 
  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('Interval'), 'field' => 'interval'),
    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')
  );
 
  global $user;
  $case_filter_args = variable_get('casetracker_case_node_types', array('casetracker_basic_case'));
  $case_filter_sql  = array('n.type IN ('.str_pad('', count(array_filter(variable_get('casetracker_case_node_types', array('casetracker_basic_case')))) * 5 - 1, "'%s',").')');
 
  $case_filter_sql[] = 'cc.case_status_id IN ('.implode($case_statuses,',').')';
 
  $case_filter_sql = count($case_filter_sql) ? 'AND '.implode(' AND ', $case_filter_sql) : NULL;
  $sql = db_rewrite_sql('SELECT n.nid, n.title, n.created, ncs.last_comment_timestamp-n.created AS interval, 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 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) WHERE n.status = 1 '.$case_filter_sql);
 
  $results = pager_query($sql . tablesort_sql($headers), 15, 0, NULL, $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);
    $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' => format_interval($result->interval), 'class' => 'interval'),
                      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' => theme('username', user_load(array('uid' => $result->assign_to))), 'class' => 'assign-to'),
                    ), 'class' => $state_classes);
  }
  if (count($rows) == 0) { $rows[] = array(array('data' => t('No cases found.'), 'colspan' => 7)); }
 
  $output .= theme('table', $headers, $rows, array('id' => 'casetracker-cases-overview'));
  $output .= theme('pager', NULL, 15, 0);
 
  echo $output;
?>

Tags: drupal, casetracker