index.php

本代码是为客户联系管理而做的系统

<?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: index.php,v $
// | $Date: 2004/02/10 01:34:30 $
// | $Revision: 1.40 $
// +-------------------------------------------------------------+
// | File Details:
// | - Technician performance and statistics page.
// +-------------------------------------------------------------+

error_reporting(E_ALL ^ E_NOTICE);
include "./../global.php";
require_once(INCLUDE_PATH.'functions/calendar_functions.php');

if (function_exists('gd_info')) {
	$graphing = 1;
} else {
	$graphing = 0;
}

tech_nav('Reports & Statistics');

/************************************
*			QUICK STATS
************************************/

if (!$graphing) {
	echo "<center><b>Warning:</b> Graphing is not enabled, you will not see the graphs that should be displayed.</center><br /><br />";
}

$first_entry = $db->query_return("SELECT min(timestamp) AS early FROM ticket_log");
$first_entry = our_date($first_entry['early'], 'day');

// Grab ticket counts (closed and customers helped)

$total = $db->query_return("SELECT COUNT(distinct ticket_log.ticketid) AS total FROM ticket_log WHERE techid = '$user[id]' AND actionid = '14'");
$total = number_format($total['total']);

$reps = $db->query_return("SELECT COUNT(id) AS total FROM ticket_message WHERE techid = '$user[id]'");
$reps = number_format($reps['total']);

$db->query("SELECT DISTINCT ticketid FROM ticket_message WHERE techid = '$user[id]'", 'ticketid');
while ($res = $db->row_array()) {
        $custs[] = $res['ticketid'];
}
$custs = $db->query_return("SELECT COUNT(DISTINCT userid) AS total FROM ticket WHERE id in " . array2sql($custs));
$custs = number_format($custs['total']);

$simple_stats[] = "Sent $reps response(s)";
$simple_stats[] = "Closed a total of $total ticket(s)";
$simple_stats[] = "Helped $custs customer(s)";

/************************************
*		AVERAGE RESOLUTION TIME
************************************/

// CATEGORY

$db->query("SELECT (min(ticket_message.date) - ticket.date_opened) AS life, ticket_cat.name AS category 
	FROM ticket, ticket_message 
	LEFT JOIN ticket_cat ON ticket_cat.id = ticket.category 
	WHERE ticket_message.ticketid = ticket.id
		AND techid = '$user[id]'
	GROUP by ticketid
	ORDER BY ticket_cat.name
");

$speed = array();
$speed_tot = array();
$cat_data = array();

while ($result = $db->row_array()) {
	$speed[$result['category']]['count']++;
	$speed[$result['category']]['time'] += $result['life'];
	$speed_tot['count']++;
	$speed_tot['time'] += $result['life'];
}

foreach ($speed AS $category => $data) {
	$cat_data[] = array('category' => $category, 'life' => ($data['time'] / $data['count']));
}
if ($speed_tot['count']) {
	$cat_data[] = array('category' => '<B><I>Average</I></B>', 'life' => ($speed_tot['time'] / $speed_tot['count']));
} else {
	$cat_data[] = array('category' => '<B><I>Average</I></B>', 'life' => 'None');
}

foreach ($cat_data AS $key => $val) {
	$catspeeds = 1;
	if (!$val[category]) {
		$val[category] = "<I>Uncategorized</I>";
	}
	$val['life'] = clean_time($val['life'], 1);
	$avg_response_category[] = array($val[category], $val[life]);
}

if (!$catspeeds) {
	$avg_response_category[] = 'None';
}

// PRIORITY

$db->query("SELECT (min(ticket_message.date) - ticket.date_opened) AS life, ticket_pri.name AS priority 
	FROM ticket, ticket_message 
	LEFT JOIN ticket_pri ON ticket_pri.id = ticket.priority 
	WHERE ticket_message.ticketid = ticket.id
		AND techid = '$user[id]' 
	GROUP by ticketid
	ORDER BY ticket_pri.name
");

$speed = array();
$speed_tot = array();
$pri_data = array();

while ($result = $db->row_array()) {
	$speed[$result['priority']]['count']++;
	$speed[$result['priority']]['time'] += $result['life'];
	$speed_tot['count']++;
	$speed_tot['time'] += $result['life'];
}

foreach ($speed AS $priority => $data) {
	$pri_data[] = array('priority' => $priority, 'life' => ($data['time'] / $data['count']));
}
if ($speed_tot['count']) {
	$pri_data[] = array('priority' => '<B><I>Average</I></B>', 'life' => ($speed_tot['time'] / $speed_tot['count']));
} else {
	$pri_data[] = array('priority' => '<B><I>Average</I></B>', 'life' => 'None');
}

foreach ($pri_data AS $key => $val) {
	$prispeeds = 1;
	if (!$val[priority]) {
		$val[priority] = "<I>Unprioritized</I>";
	}
	$val['life'] = clean_time($val['life'], 1);
	$avg_response_pri[] = array($val[priority], $val[life]);
}

if (!$prispeeds) {
	$avg_response_pri[] = 'None';
}

// OTHER TECHS (TODAY)

$time = strtotime(date('Y-m-d'));
$techs = $db->query_return_array("SELECT (min(ticket_message.date) - ticket.date_opened) AS life, username
        FROM ticket, ticket_message
        LEFT JOIN tech ON ticket.tech = tech.id
        WHERE ticket_message.ticketid = ticket.id 
                AND ticket.tech != '$user[id]'
                AND ticket_message.techid 
				AND ticket_message.date >= '$time'
        GROUP by ticketid
");

if (is_array($techs)) {
        foreach ($techs AS $tech) {
                $avg_resp_other[$tech['username']]['sum'] += $tech['life'];
                $avg_resp_other[$tech['username']]['count']++;
        }
        foreach($avg_resp_other AS $techname => $val) {
                if ($techname) {
                        $avg_resp_tech_today[] = array($techname, clean_time($val['sum'] / $val['count']));
                }
        }
} else {
        $avg_resp_tech_today[] = array("No data available.");
}

// OTHER TECHS (THIS WEEK)

$time = strtotime(date('Y-m-d') . " - 1 week");
$techs = $db->query_return_array("SELECT (min(ticket_message.date) - ticket.date_opened) AS life, username
        FROM ticket, ticket_message
        LEFT JOIN tech ON ticket.tech = tech.id
        WHERE ticket_message.ticketid = ticket.id 
                AND ticket.tech != '$user[id]'
                AND ticket_message.techid 
				AND ticket_message.date >= '$time'
        GROUP by ticketid
");

if (is_array($techs)) {
        foreach ($techs AS $tech) {
                $avg_resp_other[$tech['username']]['sum'] += $tech['life'];
                $avg_resp_other[$tech['username']]['count']++;
        }
        foreach($avg_resp_other AS $techname => $val) {
                if ($techname) {
                        $avg_resp_tech_week[] = array($techname, clean_time($val['sum'] / $val['count']));
                }
        }
} else {
        $avg_resp_tech_week[] = array("No data available.");
}


/************************************
*		YOUR OPEN TICKETS
************************************/

// CATEGORY 

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

unset($cats);
while ($result = $db->row_array()) {
	$cats++;
	$awaiting_response_category[] = array($result[category], $result[total]);
}
$awaiting_response_category[] = array('<b>Total</b>', '<b>' . $cats . '</b>');

// add graph
if (!$cats) {
	$awaiting_response_category[] = 'None';
} elseif ($graphing) {
	$awaiting_response_category[] = "<img src=\"graph.php?type=category\">";
}

// PRIORITY

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

unset($pris);
while ($result = $db->row_array()) {
	$pris++;
	$awaiting_response_priority[] = array($result[priority], $result[total]);
}
$awaiting_response_priority[] = array('<b>Total</b>', '<b>' . $pris . '</b>');