You are here

support_charts.module in Support Ticketing System 7

Same filename and directory in other branches
  1. 6 support_charts/support_charts.module

Support charting. @author Jeremy Andrews <jeremy@tag1consulting.com> @package Support

File

support_charts/support_charts.module
View source
<?php

/**
 * @file
 * Support charting.
 * @author Jeremy Andrews <jeremy@tag1consulting.com>
 * @package Support
 */

/**
 * Implements hook_permission();
 */
function support_charts_permission() {
  return array(
    'access support charts' => array(
      'title' => t('Access support charts'),
    ),
  );
}

/**
 * Implements hook_menu().
 */
function support_charts_menu() {
  $items = array();
  $items['admin/support/charts'] = array(
    'title' => 'Charts',
    'description' => 'View support ticketing charts.',
    'page callback' => 'support_charts',
    'page arguments' => array(
      'global',
    ),
    'access arguments' => array(
      'access support charts',
    ),
  );
  $items['admin/support/charts/global'] = array(
    'title' => 'Global',
    'page callback' => 'support_charts',
    'page arguments' => array(
      'global',
    ),
    'access arguments' => array(
      'access support charts',
    ),
    'type' => MENU_DEFAULT_LOCAL_TASK,
  );
  $items['user/%user/support_charts'] = array(
    'title' => 'Support charts',
    'page callback' => 'support_charts',
    'page arguments' => array(
      'user_opened',
      1,
    ),
    'access arguments' => array(
      'access support charts',
    ),
    'type' => MENU_LOCAL_TASK,
  );
  $items['user/%user/support_charts/opened'] = array(
    'title' => 'Opened',
    'weight' => 1,
    'type' => MENU_DEFAULT_LOCAL_TASK,
  );
  $items['user/%user/support_charts/assigned'] = array(
    'title' => 'Assigned',
    'page callback' => 'support_charts',
    'page arguments' => array(
      'user_assigned',
      1,
    ),
    'access arguments' => array(
      'access support charts',
    ),
    'weight' => 2,
    'type' => MENU_LOCAL_TASK,
  );
  return $items;
}

/**
 * Page callback.
 */
function support_charts($page = 'global', $object = NULL) {
  return '<div id="support-charts">' . support_charts_display($page, $object) . '</div>';
}

/**
 * Display charts and content in context to the current page.
 *
 * @return string
 *   markup, chart images.
 */
function support_charts_display($page = 'global', $object) {
  $output = '';
  drupal_add_css(drupal_get_path('module', 'support_charts') . '/support_charts.css');
  switch ($page) {
    case 'global':
      $output .= support_charts_build_pie('tickets_open');
      $output .= support_charts_build_pie('tickets_per_state');
      $output .= support_charts_build_pie('tickets_per_priority');
      $output .= support_charts_build_pie('tickets_per_client');
      $output .= support_charts_build_pie('tickets_per_assigned');
      $output .= support_charts_build_graph('tickets_created');
      $output .= support_charts_build_graph('tickets_updated');
      $output .= support_charts_build_graph('tickets_closed');
      break;
    case 'user_opened':
      $output .= support_charts_build_pie('tickets_open', 'user', $object);
      $output .= support_charts_build_pie('tickets_per_state', 'user', $object);
      $output .= support_charts_build_pie('tickets_per_priority', 'user', $object);
      $output .= support_charts_build_pie('tickets_per_client', 'user', $object);
      $output .= support_charts_build_graph('tickets_created', 'user', $object);
      $output .= support_charts_build_graph('tickets_updated', 'user', $object);
      $output .= support_charts_build_graph('tickets_closed', 'user', $object);
      break;
    case 'user_assigned':
      $output .= support_charts_build_pie('tickets_per_assigned', 'user_assigned', $object);
      $output .= support_charts_build_pie('tickets_per_state', 'user_assigned', $object);
      $output .= support_charts_build_pie('tickets_per_priority', 'user_assigned', $object);
      $output .= support_charts_build_pie('tickets_per_client', 'user_assigned', $object);
  }
  return $output;
}

/**
 * Gather data and build a chart API structure.
 *
 * @return array
 *   chart API structure.
 */
function support_charts_build_pie($id, $type = 'global', $object = NULL) {
  $chart = array();
  $chart['#type'] = CHART_TYPE_PIE;
  $chart['#size'] = chart_size(600, 350);
  $args = array();
  switch ($id) {
    case 'tickets_open':
      $chart['#chart_id'] = 'tickets_open';
      if ($type == 'user') {
        $chart['#title'] = t('Tickets opened by !user', array(
          '!user' => check_plain($object->name),
        ));
        $args[':uid'] = $object->uid;
        $filter = "AND n.uid = :uid";
      }
      else {
        $chart['#title'] = t('Open tickets');
        $filter = '';
      }
      $result = db_query("SELECT COUNT(t.nid) AS count, s.state as label, s.isclosed FROM {support_ticket} t LEFT JOIN {support_states} s ON t.state = s.sid LEFT JOIN {node} n ON t.nid = n.nid WHERE n.status = 1 {$filter} GROUP BY s.isclosed ORDER BY s.isclosed", $args);
      break;
    case 'tickets_per_state':
      $chart['#chart_id'] = 'tickets_per_state';
      if ($type == 'user') {
        $chart['#title'] = t('Open tickets by !user per state', array(
          '!user' => check_plain($object->name),
        ));
        $args[':uid'] = $object->uid;
        $filter = "AND n.uid = :uid";
      }
      else {
        if ($type == 'user_assigned') {
          $chart['#title'] = t('Open tickets assigned to !user per state', array(
            '!user' => check_plain($object->name),
          ));
          $args[':uid'] = $object->uid;
          $filter = "AND t.assigned = :uid";
        }
        else {
          $chart['#title'] = t('Open tickets per state');
          $filter = '';
        }
      }
      $result = db_query("SELECT COUNT(t.nid) AS count, s.state as label FROM {support_ticket} t LEFT JOIN {support_states} s ON t.state = s.sid LEFT JOIN {node} n ON t.nid = n.nid WHERE n.status = 1 AND s.isclosed = 0 {$filter} GROUP BY s.state ORDER BY s.state", $args);
      break;
    case 'tickets_per_priority':
      $chart['#chart_id'] = 'tickets_per_priority';
      if ($type == 'user') {
        $chart['#title'] = t('Open tickets by !user per priority', array(
          '!user' => check_plain($object->name),
        ));
        $args[':uid'] = $object->uid;
        $filter = "AND n.uid = :uid";
      }
      else {
        if ($type == 'user_assigned') {
          $chart['#title'] = t('Open tickets assigned to !user per priority', array(
            '!user' => check_plain($object->name),
          ));
          $args[':uid'] = $object->uid;
          $filter = "AND t.assigned = :uid";
        }
        else {
          $chart['#title'] = t('Open tickets per priority');
          $filter = '';
        }
      }
      $result = db_query("SELECT COUNT(t.nid) AS count, p.priority as label FROM {support_ticket} t LEFT JOIN {support_priority} p ON t.priority = p.pid LEFT JOIN {support_states} s ON t.state = s.sid LEFT JOIN {node} n ON t.nid = n.nid WHERE n.status = 1 AND s.isclosed = 0 {$filter} GROUP BY t.priority ORDER BY t.priority", $args);
      break;
    case 'tickets_per_client':
      $chart['#chart_id'] = 'tickets_per_client';
      if ($type == 'user') {
        $chart['#title'] = t('Open tickets by !user per client', array(
          '!user' => check_plain($object->name),
        ));
        $args[':uid'] = $object->uid;
        $filter = "AND n.uid = :uid";
      }
      else {
        if ($type == 'user_assigned') {
          $chart['#title'] = t('Open tickets assigned to !user per client', array(
            '!user' => check_plain($object->name),
          ));
          $args[':uid'] = $object->uid;
          $filter = "AND t.assigned = :uid";
        }
        else {
          $chart['#title'] = t('Open tickets per client');
          $filter = '';
        }
      }
      $result = db_query("SELECT COUNT(t.nid) AS count, c.name as label FROM {support_ticket} t LEFT JOIN {support_client} c ON t.client = c.clid LEFT JOIN {support_states} s ON t.state = s.sid LEFT JOIN {node} n ON t.nid = n.nid WHERE n.status = 1 AND s.isclosed = 0 {$filter} GROUP BY t.client ORDER BY t.client", $args);
      break;
    case 'tickets_per_assigned':
      $chart['#chart_id'] = 'tickets_per_assigned';
      if ($type == 'user_assigned') {
        $chart['#title'] = t('Tickets assigned to !user', array(
          '!user' => check_plain($object->name),
        ));
        $result = db_query('SELECT COUNT(t.nid) AS count, s.state as label, s.isclosed FROM {support_ticket} t LEFT JOIN {users} u ON t.assigned = u.uid LEFT JOIN {support_states} s ON t.state = s.sid LEFT JOIN {node} n ON t.nid = n.nid WHERE n.status = 1 AND t.assigned = :assigned GROUP BY s.isclosed ORDER BY s.isclosed', array(
          ':assigned' => $object->uid,
        ));
      }
      else {
        $chart['#title'] = t('Assigned open tickets');
        $result = db_query('SELECT COUNT(t.nid) AS count, u.name as label FROM {support_ticket} t LEFT JOIN {users} u ON t.assigned = u.uid LEFT JOIN {support_states} s ON t.state = s.sid LEFT JOIN {node} n ON t.nid = n.nid WHERE n.status = 1 AND s.isclosed = 0 GROUP BY t.assigned ORDER BY t.assigned');
      }
      break;
  }
  foreach ($result as $data) {
    if ($id == 'tickets_open') {
      if ($data->isclosed) {
        $label = t('closed');
      }
      else {
        $label = t('open');
      }
    }
    else {
      if ($id == 'tickets_per_assigned') {
        if (empty($data->label)) {
          $label = t('Not assigned');
        }
        else {
          $label = $data->label;
        }
      }
      else {
        $label = isset($data->label) ? $data->label : t('unknown');
      }
    }
    $chart['#data'][] = $data->count;
    $chart['#labels'][] = "{$label}: {$data->count}";
    $chart['#data_colors'][] = chart_unique_color($label);
    $chart['#adjust_resolution'] = TRUE;
  }
  return theme('chart', array(
    'chart' => $chart,
  ));
}

/**
 * Gather data and build a chart API structure.
 *
 * @return array
 *   chart API structure.
 */
function support_charts_build_graph($id, $type = 'global', $object = NULL) {
  $chart = array();
  $chart['#type'] = CHART_TYPE_LINE;
  $chart['#size'] = chart_size(620, 250);
  $chart['#grid_lines'] = chart_grid_lines(25, 9.5, 1, 3);
  $chart['#adjust_resolution'] = TRUE;
  $now = isset($_GET['year']) && isset($_GET['month']) ? mktime(0, 0, 0, $_GET['month'], 30, $_GET['year']) : time();
  $args = array();
  switch ($id) {
    case 'tickets_created':
      if ($type == 'user') {
        $chart['#title'] = chart_title(t('Tickets created by !user in !date', array(
          '!date' => date('F Y', $now),
          '!user' => check_plain($object->name),
        )));
        $args[':uid'] = $object->uid;
        $filter = "AND n.uid = :uid";
      }
      else {
        $chart['#title'] = chart_title(t('Tickets created in !date', array(
          '!date' => date('F Y', $now),
        )));
        $filter = '';
      }
      $chart['#chart_id'] = $id;
      $args[':end'] = $now;
      $args[':begin'] = mktime(0, 0, 0, date('m', $now), 1, date('Y', $now));
      $result = db_query("SELECT n.created AS timestamp FROM {node} n WHERE n.type = 'support_ticket' AND n.created < :end AND n.created > :begin {$filter} ORDER BY n.created", $args);
      break;
    case 'tickets_updated':
      if ($type == 'user') {
        $chart['#title'] = chart_title(t('Tickets updated by !user in !date', array(
          '!date' => date('F Y', $now),
          '!user' => check_plain($object->name),
        )));
        $args[':uid'] = $object->uid;
        $filter = "AND n.uid = :uid";
      }
      else {
        $chart['#title'] = chart_title(t('Tickets updated in !date', array(
          '!date' => date('F Y', $now),
        )));
        $filter = '';
      }
      $chart['#chart_id'] = $id;
      $args[':end'] = $now;
      $args[':begin'] = mktime(0, 0, 0, date('m', $now), 1, date('Y', $now));
      $result = db_query("SELECT GREATEST(n.changed, l.last_comment_timestamp) AS timestamp FROM {node} n LEFT JOIN {node_comment_statistics} l ON n.nid = l.nid WHERE n.type = 'support_ticket' AND (n.changed < :end OR l.last_comment_timestamp < :end) AND (n.changed > :begin OR l.last_comment_timestamp > :begin) {$filter} ORDER BY timestamp", $args);
      break;
    case 'tickets_closed':
      if ($type == 'user') {
        $chart['#title'] = chart_title(t('Tickets closed by !user in !date', array(
          '!date' => date('F Y', $now),
          '!user' => check_plain($object->name),
        )));
        $args[':uid'] = $object->uid;
        $filter = "AND n.uid = :uid";
      }
      else {
        $chart['#title'] = chart_title(t('Tickets closed in !date', array(
          '!date' => date('F Y', $now),
        )));
        $filter = '';
      }
      $chart['#chart_id'] = $id;
      $args[':end'] = $now;
      $args[':begin'] = mktime(0, 0, 0, date('m', $now), 1, date('Y', $now));
      $result = db_query("SELECT GREATEST(n.changed, l.last_comment_timestamp) AS timestamp FROM {node} n LEFT JOIN {node_comment_statistics} l ON n.nid = l.nid LEFT JOIN {support_ticket} t ON n.nid = t.nid LEFT JOIN {support_states} s ON t.state = s.sid WHERE s.isclosed = 1 AND n.type = 'support_ticket' AND (n.changed < :end OR l.last_comment_timestamp < :end) AND (n.changed > :begin OR l.last_comment_timestamp > :begin) {$filter} ORDER BY timestamp", $args);
      break;
  }
  $max = array();
  $counts = array();
  $types = array();
  foreach ($result as $data) {
    $day = ltrim(date('d', $data->timestamp), '0');
    $types[$type] = $type;
    if (!isset($counts[$day][$type])) {
      $counts[$day][$type] = 0;
    }
    $counts[$day][$type]++;
    if (!isset($max[$type])) {
      $max[$type] = 0;
    }
    $max[$type]++;
  }

  // Generate data and labels
  if (count($counts) && count($types)) {
    for ($i = 0; $i <= date('d', $now); $i++) {
      $chart['#labels'][] = $i;
      foreach ($types as $type) {
        if (!empty($counts[$i][$type])) {
          $chart['#data'][$type][] = $counts[$i][$type];
        }
        else {
          $chart['#data'][$type][] = '0';
        }
      }
    }
  }

  // Data colors, legends, line styles, and labels
  if (count($types)) {
    foreach ($types as $type) {
      $chart['#data_colors'][] = chart_unique_color($type);
      $chart['#legends'][] = $type;
      $chart['#line_styles'][] = chart_line_style(2);
    }
  }
  $max = count($max) ? max($max) : 0;
  $chart['#mixed_axis_labels'][CHART_AXIS_Y_LEFT][0][] = chart_mixed_axis_range_label(0, $max);
  return theme('chart', array(
    'chart' => $chart,
  ));
}

Functions

Namesort descending Description
support_charts Page callback.
support_charts_build_graph Gather data and build a chart API structure.
support_charts_build_pie Gather data and build a chart API structure.
support_charts_display Display charts and content in context to the current page.
support_charts_menu Implements hook_menu().
support_charts_permission Implements hook_permission();