basic_stats.php

jsp程序开发系统

<?php
// +-------------------------------------------------------------+
// | DeskPRO v [2.0.1 Production]
// | Copyright (C) 2001 - 2004 Headstart Solutions Limited
// | Supplied by WTN-WDYL
// | Nullified by WTN-WDYL
// | Distribution via WebForum, ForumRU and associated file dumps
// +-------------------------------------------------------------+
// | DESKPRO IS NOT FREE SOFTWARE
// +-------------------------------------------------------------+
// | License ID : Full Enterprise License =) ...
// | License Owner : WTN-WDYL Team
// +-------------------------------------------------------------+
// | $RCSfile: basic_stats.php,v $
// | $Date: 2004/02/10 01:34:24 $
// | $Revision: 1.21 $
// +-------------------------------------------------------------+
// | File Details:
// | - Basic statistical summary of DeskPRO operations
// |   (administration interface)
// +-------------------------------------------------------------+

error_reporting(E_ALL & ~E_NOTICE);
require_once('./global.php');
//Nullify WTN-WDYL Team

define('NO_GRAPHIC_HANDLER', 'true');
include_once("./../includes/graph/jpgraph.php");
include_once ("./../includes/graph/jpgraph_bar.php");

error_reporting(0);

admin_header('Basic Statistics', 'Tickets and Techs');

if ((int)$_REQUEST['duration']) {
	$duration = (int)$_REQUEST['duration'];
	switch ($_REQUEST['units']) {
		case 'minutes':
		case 'hours':
		case 'days':
		case 'weeks':
		case 'months':
		case 'years':
			break;
		default:
			$_REQUEST['units'] = 'days';
			$duration = 1;
			break;
	}
} else {
	$_REQUEST['units'] = 'days';
	$duration = 1;
}

$text = "in the past $duration $_REQUEST[units]";
$when = strtotime(date('r') . " -$duration $_REQUEST[units]");

$start_text = "<P>Showing statistics from " . our_date($when, 'full') . " to " . our_date(mktime()) . " (events occuring $text):</P>\n";

$now = mktime();

////// Gathering stats //////

//// Tickets opened and closed

$open_close[0] = 0;
$open_close[1] = 0;

$db->query("SELECT COUNT(id) AS total, is_open FROM ticket WHERE date_opened >= $when OR date_closed >= $when GROUP BY is_open ORDER BY is_open");
while ($res = $db->row_array()) {
	$open_close[$res['is_open']] = $res['total'];
}

$open_close_total[0] = 0;
$open_close_total[1] = 0;

$db->query("SELECT COUNT(id) AS total, is_open FROM ticket GROUP BY is_open ORDER BY is_open");
while ($res = $db->row_array()) {
	$open_close_total[$res['is_open']] = $res['total'];
}

$oc_table_cols = array('<B>Opened</B>', '<B>Closed</B>');
$oc_table[] = array("$open_close[1] $text", "$open_close[0] $text");
$oc_table[] = array("$open_close_total[1] total", "$open_close_total[0] total");

//// Tickets awaiting reply from techs and users

$await_reply[0] = 0;
$await_reply[1] = 0;

$db->query("SELECT COUNT(id) AS total, awaiting_tech FROM ticket WHERE date_opened >= $when OR date_closed >= $when GROUP BY awaiting_tech ORDER BY awaiting_tech");
while ($res = $db->row_array()) {
	$await_reply[$res['awaiting_tech']] = $res['total'];
}

$await_reply_total[0] = 0;
$await_reply_total[1] = 0;

$db->query("SELECT COUNT(id) AS total, awaiting_tech FROM ticket GROUP BY awaiting_tech ORDER BY awaiting_tech");
while ($res = $db->row_array()) {
	$await_reply_total[$res['awaiting_tech']] = $res['total'];
}

$ar_table_cols = array('<B>Awaiting Tech Reply</B>', '<B>Awaiting User Reply</B>');
$ar_table[] = array("$await_reply[1] $text", "$await_reply[0] $text");
$ar_table[] = array("$await_reply_total[1] total", "$await_reply_total[0] total");

//// Tickets closed in duration, by technician

$db->query("SELECT username AS name FROM tech");

while ($res = $db->row_array()) {
	$techs[$res['name']] = 0;
}

$db->query("SELECT COUNT(ticket.id) AS total, tech.username AS name 
	FROM ticket, tech 
	WHERE !is_open 
		AND ticket.tech = tech.id 
		AND date_closed >= $when
	GROUP BY name 
	ORDER BY name");

if ($db->num_rows()) {
	while ($res = $db->row_array()) {
		$techs[$res['name']] = $res['total'];
	}
}

$closed_techs_head[] = "<B>Technician</B>";
$closed_techs_data[] = "<B>Tickets</B>";

foreach($techs AS $key => $val) {
	$closed_techs_head[] = $key;
	$closed_techs_data[] = $val;
}

$closed_techs_table[] = $closed_techs_head;
$closed_techs_table[] = $closed_techs_data;

unset ($techs);

//// Tickets awaiting tech reply, by priority

$db->query("SELECT name FROM ticket_pri");

while ($res = $db->row_array()) {
	$pris[$res['name']] = 0;
}

$db->query("SELECT COUNT(ticket.id) AS total, ticket_pri.name AS name 
	FROM ticket, ticket_pri 
	WHERE is_open 
		AND awaiting_tech 
		AND ticket.priority = ticket_pri.id 
	GROUP BY name 
	ORDER BY name");

if ($db->num_rows()) {
	while ($res = $db->row_array()) {
		$pris[$res['name']] = $res['total'];
	}
}

$pris_head[] = "<B>Priorities</B>";
$pris_data[] = "<B>Tickets</B>";

if (is_array($pris)) {
	foreach($pris AS $key => $val) {
		$pris_head[] = $key;
		$pris_data[] = $val;
	}
}

$pris_table[] = $pris_head;
$pris_table[] = $pris_data;

//// Tickets awaiting tech reply, by category

$db->query("SELECT name FROM ticket_cat");

while ($res = $db->row_array()) {
	$cats[$res['name']] = 0;
}

$db->query("SELECT COUNT(ticket.id) AS total, ticket_cat.name AS name 
	FROM ticket, ticket_cat 
	WHERE is_open 
		AND awaiting_tech 
		AND ticket.category = ticket_cat.id 
	GROUP BY name 
	ORDER BY name");

if ($db->num_rows()) {
	while ($res = $db->row_array()) {
		$cats[$res['name']] = $res['total'];
	}
}

$cats_head[] = "<B>Category</B>";
$cats_data[] = "<B>Tickets</B>";

if (is_array($cats)) {
	foreach($cats AS $key => $val) {
		$cats_head[] = $key;
		$cats_data[] = $val;
	}
}

$cats_table[] = $cats_head;
$cats_table[] = $cats_data;

//// Tickets awaiting tech reply, by technician

$db->query("SELECT COUNT(ticket.id) AS total FROM ticket WHERE is_open AND awaiting_tech AND !tech GROUP BY tech");
$res = $db->row_array();
$techs['Unassigned'] = $res['total'];

$db->query("SELECT username AS name FROM tech");

while ($res = $db->row_array()) {
	$techs[$res['name']] = 0;
}

$db->query("SELECT COUNT(ticket.id) AS total, tech.username AS name 
	FROM ticket, tech 
	WHERE is_open 
		AND awaiting_tech 
		AND ticket.tech = tech.id 
	GROUP BY name 
	ORDER BY name");

if ($db->num_rows()) {
	while ($res = $db->row_array()) {
		$techs[$res['name']] = $res['total'];
	}
}

$techs_head[] = "<B>Technician</B>";
$techs_data[] = "<B>Tickets</B>";

foreach($techs AS $key => $val) {
	$techs_head[] = $key;
	$techs_data[] = $val;
}

$techs_table[] = $techs_head;
$techs_table[] = $techs_data;

//// Response times (answered within N minutes)

unset ($techs);
$db->query("SELECT username AS name FROM tech");
while ($res = $db->row_array()) {
	$techs[$res['name']] = array(0,0,0,0,0,0);
}

$db->query("SELECT (min(ticket_message.date) - ticket.date_opened) AS life, username AS name 
	FROM ticket, ticket_message 
	LEFT JOIN tech ON ticket_message.techid = tech.id 
	WHERE ticket_message.techid 
		AND ticket_message.ticketid = ticket.id 
	GROUP BY ticket_message.ticketid;");

while ($res = $db->row_array()) {
	if ($res['life'] <= (60 * 5)) { // Answered in under 5 minutes
		$techs[$res['name']][0]++;
	} elseif ($res['life'] <= (60 * 10)) { // Answered within 10 minutes
		$techs[$res['name']][1]++;
	} elseif ($res['life'] <= (60 * 15)) { // Answered within 15 minutes
		$techs[$res['name']][2]++;
	} elseif ($res['life'] <= (60 * 30)) { // Answered within 30 minutes
		$techs[$res['name']][3]++;
	} elseif ($res['life'] <= (60 * 60)) { // Answered within 60 minutes
		$techs[$res['name']][4]++;
	} else { // Answered after an hour
		$techs[$res['name']][5]++;
	}
}

$resp_table_cols = array(
	'Technician',
	'&lt; 5 mins',
	'5-10 mins',
	'10-15 mins',
	'15-30 mins',
	'30-60 mins',
	'&gt; 60 mins'
);
foreach($techs AS $key => $val) {
	if ($key) {
		$resp_cols = array("<B>$key</B>");
		foreach($val AS $key2 => $val2) {
			$resp_cols[] = $val2;
		}
		$resp_table[] = $resp_cols;
	}
}

//// Ticket and reply counts
$db->query("SELECT username, id FROM tech");
$techs = array();
while ($result = $db->row_array()) {
	$techs[$result['id']] = $result['username'];
}
if ($db->num_rows()) {
	$data = $db->query_return_array("SELECT techid, ticketid FROM ticket_message WHERE techid");
	if ($db->num_rows()) {
		foreach ($data AS $val) {
			$tech_count[$techs[$val['techid']]]['tickets'][$val['ticketid']] = $val['ticketid'];
			$tech_count[$techs[$val['techid']]]['count']++;
		}
	}
	if (is_array($tech_count)) {
		$per_tech_cols = array('Technician', 'Total Messages', 'Total Tickets');
		foreach ($tech_count AS $techname => $val) {
			$per_tech_totals[] = array($techname, $val['count'], count(array_keys($val['tickets'])));
			$per_tech_name[] = $techname;
			$per_tech_messages[] = $val['count'];
			$per_tech_tickets[] = count(array_keys($val['tickets']));
		}
	}
}

//// Print it all out

$form[] = $start_text;
$form[] = array('<FORM METHOD="post" ACTION="basic_stats.php"><B>Show Statistics from the Last ...</B>&nbsp;&nbsp;&nbsp;' .
	form_input('duration', $duration, 5) . '&nbsp;&nbsp;&nbsp;' .
	form_select('units', array('minutes', 'hours', 'days', 'weeks', 'months', 'years'), '', $_REQUEST['units'], 1) . '&nbsp;&nbsp;&nbsp' .form_submit('Update Statistics'));


table_header('History Options');
table_content('', $form);
table_footer();

table_header('Opened/Closed');
table_content($oc_table_cols, $oc_table);
table_footer();

$closed_techs_table[] = simple_bargraph($closed_techs_data, $closed_techs_head);
table_header("Tickets Closed $text");
table_content('', $closed_techs_table);
table_footer();

table_header('Awaiting Reply Status');
table_content($ar_table_cols, $ar_table);
table_footer();

$cats_table[] = simple_bargraph($cats_data, $cats_head);
table_header('Open Tickets (Awaiting Tech Reply, by Category)');
table_content('', $cats_table);
table_footer();

$pris_table[] = simple_bargraph($pris_data, $pris_head);
table_header('Open Tickets (Awaiting Tech Reply, by Priority)');
table_content('', $pris_table);
table_footer();

$techs_table[] = simple_bargraph($techs_data, $techs_head);
table_header('Open Tickets (Awaiting Tech Reply, by Technician)');
table_content('', $techs_table);
table_footer();

table_header('Response Times (time to first response, by Technician, per Ticket)');
table_content($resp_table_cols, $resp_table);
table_footer();

@array_unshift($per_tech_name, "empty"); 
@array_unshift($per_tech_tickets, "empty"); 
$per_tech_totals[] = simple_bargraph($per_tech_tickets, $per_tech_name, $per_tech_messages);
table_header('Total Responses Written and Total Tickets Responded To');
table_content($per_tech_cols, $per_tech_totals);
table_footer();

function simple_bargraph($datay, $datax, $datay2='') {

	@array_shift($datay);
	@array_shift($datax);

	// Create the graph. These two calls are always required
	$graph = new Graph(600,200);    
	$graph->SetScale("textlin");

	// Create a bar pot
	$bplot = new BarPlot($datay);

	// Adjust fill color
	$bplot->SetFillColor('orange');
	$bplot->value->Show();
	
	// No decimal places
	$bplot->value->SetFormat('%.0f T');

	if (is_array($datay2)) {

		// Create a bar pot
		$bplot2 = new BarPlot($datay2);

		// Adjust fill color
		$bplot2->SetFillColor('green');
		$bplot2->value->Show();
		
		// No decimal places
		$bplot2->value->SetFormat('%.0f M');

		$gbplot = new GroupBarPlot(array($bplot,$bplot2));

		// ...and add it to the graPH
		$graph->Add($gbplot);

	} else {
	
		$graph->Add($bplot);

	}

	$graph->img->SetMargin(40,30,20,40);

	// Setup X-axis
	$graph->xaxis->SetTickLabels($datax);
	$graph->xaxis->SetFont(FF_VERDANA,FS_NORMAL, 7);


	// Display the graph
	$x = rand(1, 1000000);
	$graph->Stroke("graphs/$x.png");
	return "<img src=\"graphs/$x.png\">\n";

}