function support_overview_summary in Support Ticketing System 7

Same name and namespace in other branches

1. 6 support_overview/support_overview.module \support_overview_summary()

Display support ticket overview, highlighting ticket queues with tickets older than a configurable age limit.

1 string reference to 'support_overview_summary'

support_overview_menu in support_overview/support_overview.module

Implements hook_menu().

File

support_overview/support_overview.module, line 82

support_overview.module

Code

function support_overview_summary() {
  drupal_add_css(drupal_get_path('module', 'support_overview') . '/support-overview.css');

  // Determine which clients to display
  $enabled_clients = variable_get('support_overview_clients', array());
  if (!is_array($enabled_clients) || empty($enabled_clients)) {
    $enabled_clients = _support_available_clients();
  }
  $all_clients = _support_available_clients();
  $clients = array();
  foreach ($enabled_clients as $clid => $name) {

    // Don't display clients user doesn't have permission to see
    if (isset($all_clients[$clid])) {
      $clients[$clid] = support_client_load($clid);
    }
  }
  $enabled_states = variable_get('support_overview_states', array());
  if (!is_array($enabled_states) || empty($enabled_states)) {
    $states = _support_states();
  }
  else {
    $states = array();
    foreach ($enabled_states as $sid) {
      if ($sid) {
        $states[$sid] = _support_state($sid);
      }
      else {
        $states[$sid] = t('all');
      }
    }
  }
  $output = '<div class="support-overview">';
  $header = array(
    'tickets',
    'all',
  );
  $enabled_priorities = variable_get('support_overview_priorities', array());
  foreach ($enabled_priorities as $pid) {
    $header[] = _support_priorities($pid);
  }
  foreach ($clients as $client) {
    $rows = array();
    $current_row = 0;

    // Break down overview by ticket state
    foreach ($states as $sid => $state) {
      $total_args = $latest_args = $oldest_args = array();
      $rows[$current_row][] = l($state, support_queue_url($client) . "/{$state}");

      // TODO Fully convert these queries to D7.
      $total_sql = 'SELECT COUNT(t.nid) FROM {support_ticket} t WHERE t.client = :client';
      $total_args[':client'] = $client->clid;
      $latest_sql = $oldest_sql = 'SELECT GREATEST(n.changed, l.last_comment_timestamp) AS last_update, n.nid FROM {node} n LEFT JOIN {support_ticket} t ON n.nid = t.nid INNER JOIN {node_comment_statistics} l ON n.nid = l.nid WHERE t.client = :client';
      $latest_args[':client'] = $client->clid;
      $oldest_args[':client'] = $client->clid;
      if ($sid) {
        $total_sql .= ' AND t.state = :state';
        $total_args[':state'] = $sid;
        $latest_sql .= ' AND t.state = :state';
        $latest_args[':state'] = $sid;
        $oldest_sql .= ' AND t.state = :state';
        $oldest_args[':state'] = $sid;
      }
      $latest_sql .= ' ORDER BY last_update DESC';
      $oldest_sql .= ' ORDER BY last_update ASC';
      $total = db_query($total_sql, $total_args)
        ->fetchField();
      $latest = db_query_range($latest_sql, 0, 1, $latest_args)
        ->fetch();
      $oldest = db_query_range($oldest_sql, 0, 1, $oldest_args)
        ->fetch();
      if (!empty($latest->nid)) {
        $latest_alert = _support_overview_alert($latest->last_update);
        $oldest_alert = _support_overview_alert($oldest->last_update);
        $alerts = "{$latest_alert} {$oldest_alert}";
        $node = node_load($latest->nid);
        $latest_update = $latest->last_update ? t('most recent: !time, "!title"', array(
          '!time' => format_interval(time() - $latest->last_update),
          '!title' => l($node->title, "node/{$node->nid}"),
        )) : '';
        if ($latest->last_update != $oldest->last_update) {
          $node = node_load($oldest->nid);
          $oldest_update = $oldest->last_update ? t('oldest: !time, "!title"', array(
            '!time' => format_interval(time() - $oldest->last_update),
            '!title' => l($node->title, "node/{$node->nid}"),
          )) : '';
        }
        else {
          $oldest_update = '';
        }
        $rows[$current_row][] = t('!total !latest !oldest', array(
          '!total' => "<span class='support-overview-total {$state}'>{$total}</span>",
          '!latest' => "<div class='support-overview-latest {$state}{$latest_alert}'>{$latest_update}</div>",
          '!oldest' => "<div class='support-overview-oldest {$state}{$oldest_alert}'>{$oldest_update}</div>",
        ));
      }
      else {
        $rows[$current_row][] = '0';
      }
      foreach ($enabled_priorities as $pid) {
        if ($pid && $sid) {
          $total = db_query('SELECT COUNT(nid) FROM {support_ticket} WHERE client = :client AND state = :state AND priority = :priority', array(
            ':client' => $client->clid,
            ':state' => $sid,
            ':priority' => $pid,
          ))
            ->fetchField();
        }
        else {
          if ($pid) {
            $total = db_query('SELECT COUNT(nid) FROM {support_ticket} WHERE client = :client AND priority = :priority', array(
              ':client' => $client->clid,
              ':priority' => $pid,
            ))
              ->fetchField();
          }
          else {
            $total = db_query('SELECT COUNT(nid) FROM {support_ticket} WHERE client = :client AND state = :state', array(
              ':client' => $client->clid,
              ':state' => $sid,
            ))
              ->fetchField();
          }
        }
        if ($total) {
          $priority = _support_priorities($pid);
        }
        else {
          $priority = 'not-' . _support_priorities($pid);
        }
        $rows[$current_row][] = "<div class='" . check_plain($priority) . "'>{$total}</div>";
      }
      $current_row++;
    }

    // Break down overview by who the ticket is assigned to
    $query = db_select('support_ticket')
      ->distinct()
      ->fields('support_ticket', array(
      'assigned',
    ))
      ->condition('client', $client->clid)
      ->orderBy('assigned', 'ASC');

    // [0] == 'All'
    if (!empty($enabled_states) && !isset($enabled_states[0])) {
      $query
        ->condition('state', $enabled_states);
    }
    $result = $query
      ->execute();
    foreach ($result as $assigned) {
      $account = user_load($assigned->assigned);
      $total_sql = db_select('support_ticket')
        ->condition('client', $client->clid)
        ->condition('assigned', $assigned->assigned);
      $total_sql
        ->addExpression('COUNT(nid)');
      $latest_sql = db_select('node', 'n');
      $latest_sql
        ->leftJoin('support_ticket', 't', 'n.nid = t.nid');
      $latest_sql
        ->innerJoin('node_comment_statistics', 'l', 'n.nid = l.nid');
      $latest_sql
        ->addExpression('GREATEST(n.changed, l.last_comment_timestamp)', 'last_update');
      $latest_sql
        ->addField('n', 'nid');
      $latest_sql
        ->condition('t.client', $client->clid);
      $latest_sql
        ->condition('assigned', $assigned->assigned);
      $latest_sql
        ->range(0, 1);

      // [0] == 'All'
      if (!empty($enabled_states) && !isset($enabled_states[0])) {
        $latest_sql
          ->condition('state', $enabled_states);
      }
      $oldest_sql = clone $latest_sql;
      $latest_sql
        ->orderBy('last_update', 'DESC');
      $oldest_sql
        ->orderBy('last_update', 'ASC');
      $total = $total_sql
        ->execute()
        ->fetchField();
      $latest = $latest_sql
        ->execute()
        ->fetch();
      $oldest = $oldest_sql
        ->execute()
        ->fetch();
      $latest_alert = _support_overview_alert($latest->last_update);
      $oldest_alert = _support_overview_alert($oldest->last_update);
      $alerts = "{$latest_alert} {$oldest_alert}";
      $node = node_load($latest->nid);
      $latest_update = $latest->last_update ? t('most recent: !time, "!title"', array(
        '!time' => format_interval(time() - $latest->last_update),
        '!title' => l($node->title, "node/{$node->nid}"),
      )) : '';
      if ($latest->last_update != $oldest->last_update) {
        $node = node_load($oldest->nid);
        $oldest_update = $oldest->last_update ? t('oldest: !time, "!title"', array(
          '!time' => format_interval(time() - $oldest->last_update),
          '!title' => l($node->title, "node/{$node->nid}"),
        )) : '';
      }
      else {
        $oldest_update = '';
      }
      if (empty($account->name)) {
        $rows[$current_row][] = "<div class='support-overview-name {$alerts}'>" . t('not assigned') . '</div>';
      }
      else {
        $rows[$current_row][] = "<div class='support-overview-name {$alerts}'>" . l($account->name, "support/{$account->uid}/assigned") . '</div>';
      }
      $rows[$current_row][] = t('!total !latest !oldest', array(
        '!total' => "<span class='support-overview-total {$state}'>{$total}</span>",
        '!latest' => "<div class='support-overview-latest {$state}{$latest_alert}'>{$latest_update}</div>",
        '!oldest' => "<div class='support-overview-oldest {$state}{$oldest_alert}'>{$oldest_update}</div>",
      ));
      foreach ($enabled_priorities as $pid) {
        $query = db_select('support_ticket')
          ->condition('client', $client->clid)
          ->condition('assigned', $assigned->assigned);
        $query
          ->addExpression('COUNT(nid)');
        if ($pid) {
          $query
            ->condition('priority', $pid);
        }

        // [0] == 'All'
        if (!empty($enabled_states) && !isset($enabled_states[0])) {
          $query
            ->condition('state', $enabled_states);
        }
        $total = $query
          ->execute()
          ->fetchField();
        if ($total) {
          $priority = _support_priorities($pid);
        }
        else {
          $priority = 'not-' . _support_priorities($pid);
        }
        $rows[$current_row][] = "<div class='" . check_plain($priority) . "'>{$total}</div>";
      }
      $current_row++;
    }
    $output .= "<a href='" . url(support_queue_url($client)) . "'><h3 class='support-overview-client-name {$alerts}'>" . check_plain($client->name) . '</h3></a>';
    $output .= theme('table', array(
      'header' => $header,
      'rows' => $rows,
    ));
  }
  $output .= '</div>';
  return $output;
}