//
// SiT (Support Incident Tracker) - Support call tracking system
// Copyright (C) 2000-2009 Salford Software Ltd. and Contributors
//
// This software may be used and distributed according to the terms
// of the GNU General Public License, incorporated herein by reference.
//
// Author: Paul Heaney <paulheaney[at]users.sourceforge.net>
// Included by ../statistics.php
// Prevent script from being run directly (ie. it must always be included
if (realpath(__FILE__) == realpath($_SERVER['SCRIPT_FILENAME'])) {
exit;
}
switch ($offset) {
case 0:
$dayString = $strToday;
break;
case 1:
$dayString = $strYesterday;
break;
default:
$dayString = date("l", mktime(0, 0, 0, date('m'), date('d') - $offset, date('Y')));
break;
}
echo "<h2>" . sprintf($strStatsFromDayStatistics, $dateString);
echo "<table align='center'>";
echo "<tr><th>{$strPeriod}</th><th>{$strOpened}</th><th>{$strUpdated}</th><th>{$strClosed}</th><th>{$strHandled}</th>";
echo "<th>{$strUpdates}</th><th>{$strPerIncident}</th><th>{$strSkills}</th><th>{$strOwners}</th><th>{$strUsers}</th>";
echo "<th>{$strUpdatePerUserAbbrev}</th><th>{$strIncidentPerOwnerAbbrev}</th><th>{$strEmailReceivedAbbrev}</th><th>{$strEmailTransmittedAbbrev}</th><th>{$strHigherPriority}</th><th>{$strActivity}</th></tr>\n";
echo stats_period_row($dayString, mktime(0, 0, 0, date('m'), date('d') - $offset, date('Y')), mktime(23, 59, 59, date('m'), date('d') - $offset, date('Y')));
echo "</table>";
/**
* @author Paul Heaney
*/
function give_overview()
{
global $todayrecent, $mode, $CONFIG;
echo "<table align='center'>";
echo "<tr><th>{$GLOBALS['strPeriod']}</th>";
echo "<th>{$GLOBALS['strOpened']}</th><th>{$GLOBALS['strUpdated']}</th>";
echo "<th>{$GLOBALS['strClosed']}</th><th>{$GLOBALS['strHandled']}</th>";
echo "<th>{$GLOBALS['strUpdates']}</th><th>{$GLOBALS['strPerIncident']}</th><th>{$GLOBALS['strSkills']}</th>";
echo "<th>{$GLOBALS['strOwners']}</th><th>{$GLOBALS['strUsers']}</th>";
echo "<th>{$GLOBALS['strPerUser']}</th><th>{$GLOBALS['strIncidentPerOwnerAbbrev']}</th><th>{$GLOBALS['strEmailReceivedAbbrev']}</th>";
echo "<th>{$GLOBALS['strEmailTransmittedAbbrev']}</th><th>{$GLOBALS['strHigherPriority']}</th>";
echo "<th>{$GLOBALS['strActivity']}</th></tr>\n";
echo stats_period_row("<a href='{$_SERVER['PHP_SELF']}?mode=daybreakdown&offset=0'>{$GLOBALS['strToday']}</a>", mktime(0, 0, 0, date('m'), date('d'), date('Y')), mktime(23, 59, 59, date('m'), date('d'), date('Y')));
echo stats_period_row("<a href='{$_SERVER['PHP_SELF']}?mode=daybreakdown&offset=1'>{$GLOBALS['strYesterday']}</a>", mktime(0, 0, 0, date('m'), date('d') - 1, date('Y')), mktime(23, 59, 59, date('m'), date('d') - 1, date('Y')));
echo stats_period_row("<a href='{$_SERVER['PHP_SELF']}?mode=daybreakdown&offset=2'>" . ldate('l', mktime(0, 0, 0, date('m'), date('d') - 2, date('Y'))) . "</a>", mktime(0, 0, 0, date('m'), date('d') - 2, date('Y')), mktime(23, 59, 59, date('m'), date('d') - 2, date('Y')));
echo stats_period_row("<a href='{$_SERVER['PHP_SELF']}?mode=daybreakdown&offset=3'>" . ldate('l', mktime(0, 0, 0, date('m'), date('d') - 3, date('Y'))) . "</a>", mktime(0, 0, 0, date('m'), date('d') - 3, date('Y')), mktime(23, 59, 59, date('m'), date('d') - 3, date('Y')));
echo stats_period_row("<a href='{$_SERVER['PHP_SELF']}?mode=daybreakdown&offset=4'>" . ldate('l', mktime(0, 0, 0, date('m'), date('d') - 4, date('Y'))) . "</a>", mktime(0, 0, 0, date('m'), date('d') - 4, date('Y')), mktime(23, 59, 59, date('m'), date('d') - 4, date('Y')));
echo stats_period_row("<a href='{$_SERVER['PHP_SELF']}?mode=daybreakdown&offset=5'>" . ldate('l', mktime(0, 0, 0, date('m'), date('d') - 5, date('Y'))) . "</a>", mktime(0, 0, 0, date('m'), date('d') - 5, date('Y')), mktime(23, 59, 59, date('m'), date('d') - 5, date('Y')));
echo stats_period_row("<a href='{$_SERVER['PHP_SELF']}?mode=daybreakdown&offset=6'>" . ldate('l', mktime(0, 0, 0, date('m'), date('d') - 6, date('Y'))) . "</a>", mktime(0, 0, 0, date('m'), date('d') - 6, date('Y')), mktime(23, 59, 59, date('m'), date('d') - 6, date('Y')));
echo "<tr><td colspan='*'></td></tr>";
echo stats_period_row($GLOBALS['strThisWeek'], mktime(0, 0, 0, date('m'), date('d') - 6, date('Y')), mktime(23, 59, 59, date('m'), date('d'), date('Y')));
echo stats_period_row($GLOBALS['strLastWeek'], mktime(0, 0, 0, date('m'), date('d') - 13, date('Y')), mktime(23, 59, 59, date('m'), date('d') - 7, date('Y')));
echo "<tr><td colspan='*'></td></tr>";
if ($mode == 'detail') {
echo stats_period_row($GLOBALS['strThisMonth'], mktime(0, 0, 0, date('m'), 1, date('Y')), mktime(23, 59, 59, date('m'), date('d'), date('Y')));
echo stats_period_row($GLOBALS['strLastMonth'], mktime(0, 0, 0, date('m') - 1, date('d'), date('Y')), mktime(23, 59, 59, date('m'), 0, date('Y')));
echo stats_period_row(date('F y', mktime(0, 0, 0, date('m') - 2, 1, date('Y'))), mktime(0, 0, 0, date('m') - 2, date('d'), date('Y')), mktime(23, 59, 59, date('m') - 1, 0, date('Y')));
echo stats_period_row(date('F y', mktime(0, 0, 0, date('m') - 3, 1, date('Y'))), mktime(0, 0, 0, date('m') - 3, date('d'), date('Y')), mktime(23, 59, 59, date('m') - 2, 0, date('Y')));
echo stats_period_row(date('F y', mktime(0, 0, 0, date('m') - 4, 1, date('Y'))), mktime(0, 0, 0, date('m') - 4, date('d'), date('Y')), mktime(23, 59, 59, date('m') - 3, 0, date('Y')));
echo stats_period_row(date('F y', mktime(0, 0, 0, date('m') - 5, 1, date('Y'))), mktime(0, 0, 0, date('m') - 5, date('d'), date('Y')), mktime(23, 59, 59, date('m') - 4, 0, date('Y')));
echo stats_period_row(date('F y', mktime(0, 0, 0, date('m') - 6, 1, date('Y'))), mktime(0, 0, 0, date('m') - 6, date('d'), date('Y')), mktime(23, 59, 59, date('m') - 5, 0, date('Y')));
echo "<tr><td colspan='*'></td></tr>";
echo stats_period_row($GLOBALS['strThisYear'], mktime(0, 0, 0, 1, 1, date('Y')), mktime(23, 59, 59, date('m'), date('d'), date('Y')));
echo stats_period_row($GLOBALS['strLastYear'], mktime(0, 0, 0, 1, 1, date('Y') - 1), mktime(23, 59, 59, 12, 31, date('Y') - 1));
echo stats_period_row(date('Y', mktime(0, 0, 0, 1, 1, date('Y') - 2)), mktime(0, 0, 0, 1, 1, date('Y') - 2), mktime(23, 59, 59, 12, 31, date('Y') - 2));
echo stats_period_row(date('Y', mktime(0, 0, 0, 1, 1, date('Y') - 3)), mktime(0, 0, 0, 1, 1, date('Y') - 3), mktime(23, 59, 59, 12, 31, date('Y') - 3));
echo stats_period_row(date('Y', mktime(0, 0, 0, 1, 1, date('Y') - 4)), mktime(0, 0, 0, 1, 1, date('Y') - 4), mktime(23, 59, 59, 12, 31, date('Y') - 4));
echo stats_period_row(date('Y', mktime(0, 0, 0, 1, 1, date('Y') - 5)), mktime(0, 0, 0, 1, 1, date('Y') - 5), mktime(23, 59, 59, 12, 31, date('Y') - 5));
}
echo "</table>\n";
echo "<br />\n";
$sql = "SELECT DISTINCT g.id AS groupid, g.name FROM `{$GLOBALS['dbGroups']}` AS g ";
//$sql .= "WHERE (incidents.status != 2 AND incidents.status != 7) AND incidents.owner = users.id AND users.groupid = groups.id ORDER BY groups.id";
$result = mysql_query($sql);
if (mysql_error()) {
trigger_error("MySQL Query Error " . mysql_error(), E_USER_WARNING);
}
if (mysql_num_rows($result) > 1) {
echo "<h2>{$GLOBALS['strByGroup']}</h2><table class='vertical' align='center'>";
while ($groups = mysql_fetch_object($result)) {
$sqlGroups = "SELECT COUNT(i.id) AS count, istatus.name ";
$sqlGroups .= "FROM `{$GLOBALS['dbIncidents']}` AS i, `{$GLOBALS['dbIncidentStatus']}` AS istatus, `{$GLOBALS['dbUsers']}` AS u, `{$GLOBALS['dbGroups']}` AS g ";
$sqlGroups .= "WHERE i.status = istatus.id AND closed = 0 AND i.owner = u.id ";
$sqlGroups .= "AND u.groupid = {$groups->groupid} ";
$sqlGroups .= "GROUP BY i.status";
$resultGroups = mysql_query($sqlGroups);
if (mysql_error()) {
trigger_error("MySQL Query Error " . mysql_error(), E_USER_WARNING);
}
if (mysql_num_rows($resultGroups) > 0) {
$openCallsGroup = 0;
echo "<td style='vertical-align:top' align='center'><strong>{$groups->name}</strong>";
echo "<table class='vertical' align='center'>";
while ($rowGroup = mysql_fetch_object($resultGroups)) {
echo "<tr><th>{$GLOBALS[$rowGroup->name]}</th><td class='shade2' align='left'>";
//FIXME - HACK, no idea why this is needed
$amount = round($rowGroup->count / 8);
echo "{$amount}</td></tr>";
//if (strpos(strtolower($rowGroup['name']), "clos") === false)
//{
$openCallsGroup += $amount;
//}
}
echo "<tr><th>{$GLOBALS['strTotalOpen']}</th>";
echo "<td class='shade2' align='left'><strong>{$openCallsGroup}</strong></td></tr></table></td>";
}
}
echo "</table>";
}
plugin_do('statistics_table_overview');
echo "</table>";
mysql_free_result($result);
//count incidents by Vendor
/*
$sql = "SELECT DISTINCT products.vendorid, vendors.name FROM incidents, products, vendors ";
$sql .= "WHERE (status != 2 AND status != 7) AND incidents.product = products.id AND vendors.id = products.vendorid ORDER BY vendorid";
*/
$sql = "SELECT DISTINCT s.vendorid, v.name FROM `{$GLOBALS['dbIncidents']}` AS i, `{$GLOBALS['dbSoftware']}` AS s, `{$GLOBALS['dbVendors']}` AS v ";
$sql .= "WHERE (status != 2 AND status != 7) AND i.softwareid = s.id AND v.id = s.vendorid ORDER BY vendorid";
$result = mysql_query($sql);
if (mysql_error()) {
trigger_error("MySQL Query Error " . mysql_error(), E_USER_WARNING);
}
if (mysql_num_rows($result) > 1) {
echo "<h2>{$GLOBALS['strByVendor']}</h2><table class='vertical' align='center'>";
while ($vendors = mysql_fetch_array($result)) {
// This should use the software and relate to the product and then to the vendor
/*
$sqlVendor = "SELECT COUNT(incidents.id), incidentstatus.name FROM incidents, incidentstatus, products ";
$sqlVendor .= "WHERE incidents.status = incidentstatus.id AND closed = 0 AND incidents.product = products.id ";
$sqlVendor .= "AND products.vendorid = ".$vendors['vendorid']." ";
$sqlVendor .= "GROUP BY incidents.status";
*/
$sqlVendor = "SELECT COUNT(i.id) AS count, istatus.name FROM `{$GLOBALS['dbIncidents']}` AS i, `{$GLOBALS['dbIncidentStatus']}` AS istatus, `{$GLOBALS['dbSoftware']}` AS s ";
$sqlVendor .= "WHERE i.status = istatus.id AND closed = 0 AND i.softwareid = s.id ";
$sqlVendor .= "AND s.vendorid = {$vendors['vendorid']} ";
$sqlVendor .= "GROUP BY i.status";
$resultVendor = mysql_query($sqlVendor);
if (mysql_error()) {
trigger_error("MySQL Query Error " . mysql_error(), E_USER_WARNING);
}
if (mysql_num_rows($resultVendor) > 0) {
$openCallsVendor = 0;
echo "<td style='vertical-align:top' align='center'><strong>{$vendors['name']}</strong>";
echo "<table class='vertical' align='center'>";
while ($rowVendor = mysql_fetch_object($resultVendor)) {
echo "<tr><th>{$GLOBALS[$rowVendor->name]}</th><td class='shade2' align='left'>";
echo "{$rowVendor->count}</td></tr>";
if (strpos(strtolower($rowVendor->name), "clos") === false) {
$openCallsVendor += $rowVendor->count;
}
}
echo "<tr><th>{$GLOBALS['strTotalOpen']}</th>";
echo "<td class='shade2' align='left'><strong>{$openCallsVendor}</strong></td></tr></table></td>";
}
}
echo "</table>";
}
// Count incidents logged today
$sql = "SELECT id FROM `{$GLOBALS['dbIncidents']}` WHERE opened > '{$todayrecent}'";
$result = mysql_query($sql);
if (mysql_error()) {
trigger_error("MySQL Query Error " . mysql_error(), E_USER_WARNING);
}
$todaysincidents = mysql_num_rows($result);
mysql_free_result($result);
$string = "<h4>" . sprintf($GLOBALS['strIncidentsLoggedToday'], $todaysincidents) . "</h4>";
if ($todaysincidents > 0) {
$string .= "<table align='center' width='50%'><tr><td colspan='2'>{$GLOBALS['strAssignedAsFollows']}</td></tr>";
$sql = "SELECT COUNT(i.id) AS count, realname, u.id AS owner FROM `{$GLOBALS['dbIncidents']}` AS i, `{$GLOBALS['dbUsers']}` AS u WHERE opened > '{$todayrecent}' AND i.owner = u.id GROUP BY owner DESC";
$result = mysql_query($sql);
if (mysql_error()) {
trigger_error("MySQL Query Error " . mysql_error(), E_USER_WARNING);
}
while ($row = mysql_fetch_object($result)) {
$sql = "SELECT id, title FROM `{$GLOBALS['dbIncidents']}` WHERE opened > '{$todayrecent}' AND owner = '{$row->owner}'";
$string .= "<tr><th>{$row->count}</th>";
$string .= "<td class='shade2' align='left'>";
$string .= "<a href='incidents.php?user={$row->owner}&queue=1&type=support'>{$row->realname}</a> ";
$iresult = mysql_query($sql);
if (mysql_error()) {
trigger_error("MySQL Query Error " . mysql_error(), E_USER_WARNING);
}
while ($irow = mysql_fetch_object($iresult)) {
$string .= "<small><a href=\"javascript:incident_details_window('{$irow->id}', 'incident{$irow->id}')\" title='{$irow->title}'>[{$irow->id}]</a></small> ";
}
$string .= "</td></tr>";
}
$string .= "</table>";
}
// Count incidents closed today
$sql = "SELECT COUNT(id) FROM `{$GLOBALS['dbIncidents']}` WHERE closed > '{$todayrecent}'";
$result = mysql_query($sql);
if (mysql_error()) {
trigger_error("MySQL Query Error " . mysql_error(), E_USER_ERROR);
}
list($todaysclosed) = mysql_fetch_row($result);
$string .= "<h4>" . sprintf($GLOBALS['strIncidentsClosedToday'], $todaysclosed) . "</h4>";
if ($todaysclosed > 0) {
$sql = "SELECT COUNT(i.id) AS count, realname, u.id AS owner FROM `{$GLOBALS['dbIncidents']}` AS i ";
$sql .= "LEFT JOIN `{$GLOBALS['dbUsers']}` AS u ON i.owner = u.id WHERE closed > '{$todayrecent}' ";
$sql .= "GROUP BY owner";
$result = mysql_query($sql);
if (mysql_error()) {
trigger_error("MySQL Query Error " . mysql_error(), E_USER_WARNING);
}
$string .= "<table align='center' width='50%'>";
$string .= "<tr><th>{$GLOBALS['strID']}</th><th>{$GLOBALS['strTitle']}</th>";
$string .= "<th>{$GLOBALS['strOwner']}</th><th>{$GLOBALS['strClosingStatus']}</th></tr>\n";
while ($row = mysql_fetch_object($result)) {
$string .= "<tr><th colspan='4' align='left'>{$row->count} {$GLOBALS['strClosedBy']} {$row->realname}</th></tr>\n";
$sql = "SELECT i.id, i.title, cs.name ";
$sql .= "FROM `{$GLOBALS['dbIncidents']}` AS i, `{$GLOBALS['dbClosingStatus']}` AS cs ";
$sql .= "WHERE i.closingstatus = cs.id AND closed > '{$todayrecent}' ";
$sql .= "AND i.owner = '{$row->owner}' ";
$sql .= "ORDER BY closed";
$iresult = mysql_query($sql);
if (mysql_error()) {
trigger_error("MySQL Query Error " . mysql_error(), E_USER_WARNING);
}
while ($irow = mysql_fetch_object($iresult)) {
$string .= "<tr><th><a href=\"javascript:incident_details_window('{$irow->id}', 'incident{$irow->id}')\" title='[{$irow->id}] - {$irow->title}'>{$irow->id}</a></th>";
$string .= "<td class='shade2' align='left'>{$irow->title}</td>";
$string .= "<td class='shade2' align='left'>{$row->realname}</td>";
$string .= "<td class='shade2'>{$GLOBALS[$irow->name]}</td></tr>\n";
}
}
$string .= "</table>\n\n";
}
mysql_free_result($result);
$totalresult = 0;
$numquestions = 0;
$qsql = "SELECT * FROM `{$GLOBALS['dbFeedbackQuestions']}` WHERE formid='1' AND type='rating' ORDER BY taborder";
$qresult = mysql_query($qsql);
if (mysql_error()) {
trigger_error(mysql_error(), E_USER_WARNING);
}
if (mysql_num_rows($qresult) >= 1) {
$string .= "<h2>{$GLOBALS['strCustomerFeedback']}</h2>";
$string .= "<table align='center' class='vertical'>";
while ($qrow = mysql_fetch_object($qresult)) {
$numquestions++;
$string .= "<tr><th>Q{$qrow->taborder}: {$qrow->question}</th>";
$sql = "SELECT * FROM `{$GLOBALS['dbFeedbackRespondents']}` AS fr, `{$GLOBALS['dbIncidents']}` AS i, `{$GLOBALS['dbUsers']}` AS u, `{$GLOBALS['dbFeedbackResults']}` AS fres ";
$sql .= "WHERE fr.incidentid=i.id ";
$sql .= "AND i.owner=u.id ";
$sql .= "AND fr.id=fres.respondentid ";
$sql .= "AND fres.questionid='{$qrow->id}' ";
$sql .= "AND fr.completed = 'yes' \n";
$sql .= "ORDER BY i.owner, i.id";
$result = mysql_query($sql);
if (mysql_error()) {
trigger_error(mysql_error(), E_USER_WARNING);
}
$numsurveys = mysql_num_rows($result);
$numresults = 0;
$cumul = 0;
$percent = 0;
$average = 0;
while ($row = mysql_fetch_object($result)) {
if (!empty($row->result)) {
$cumul += $row->result;
$numresults++;
}
}
if ($numresults > 0) {
$average = number_format($cumul / $numresults, 2);
}
$percent = number_format(($average - 1) * (100 / ($CONFIG['feedback_max_score'] - 1)), 0);
$totalresult += $average;
$string .= "<td>{$average}</td></tr>";
// <strong>({$percent}%)</strong><br />";
}
$string .= "</table>\n";
$total_average = number_format($totalresult / $numquestions, 2);
$total_percent = number_format(($total_average - 1) * (100 / ($CONFIG['feedback_max_score'] - 1)), 0);
if ($total_percent < 0) {
$total_percent = 0;
}
$string .= "<p align='center'>{$GLOBALS['strPositivity']}: {$total_average} <strong>({$total_percent}%)</strong> ";
$string .= "From {$numsurveys} results</p>";
$surveys += $numresults;
}
return $string;
}
