// // 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; }