/**
* Create an array containing the service level history
* @author Ivan Lucas, Tom Gerrard
* @returns array
*/
function incident_sla_history($incidentid)
{
global $CONFIG, $dbIncidents, $dbServiceLevels, $dbUpdates;
$working_day_mins = ($CONFIG['end_working_day'] - $CONFIG['start_working_day']) / 60;
// Not the most efficient but..
$sql = "SELECT * FROM `{$dbIncidents}` WHERE id='{$incidentid}'";
$result = mysql_query($sql);
if (mysql_error()) {
trigger_error("MySQL Query Error " . mysql_error(), E_USER_WARNING);
}
$incident = mysql_fetch_object($result);
// Get service levels
$sql = "SELECT * FROM `{$dbServiceLevels}` WHERE tag='{$incident->servicelevel}' AND priority='{$incident->priority}' ";
$result = mysql_query($sql);
if (mysql_error()) {
trigger_error("MySQL Query Error " . mysql_error(), E_USER_WARNING);
}
$level = mysql_fetch_object($result);
// Loop through the updates in ascending order looking for service level events
$sql = "SELECT * FROM `{$dbUpdates}` WHERE type='slamet' AND incidentid='{$incidentid}' ORDER BY id ASC, timestamp ASC";
$result = mysql_query($sql);
if (mysql_error()) {
trigger_error("MySQL Query Error " . mysql_error(), E_USER_WARNING);
}
$prevtime = 0;
$idx = 0;
while ($history = mysql_fetch_object($result)) {
$slahistory[$idx]['targetsla'] = $history->sla;
switch ($history->sla) {
case 'initialresponse':
$slahistory[$idx]['targettime'] = $level->initial_response_mins;
break;
case 'probdef':
$slahistory[$idx]['targettime'] = $level->prob_determ_mins;
break;
case 'actionplan':
$slahistory[$idx]['targettime'] = $level->action_plan_mins;
break;
case 'solution':
$slahistory[$idx]['targettime'] = $level->resolution_days * $working_day_mins;
break;
default:
$slahistory[$idx]['targettime'] = 0;
}
if ($prevtime > 0) {
$slahistory[$idx]['actualtime'] = calculate_incident_working_time($incidentid, $prevtime, $history->timestamp);
} else {
$slahistory[$idx]['actualtime'] = 0;
}
$slahistory[$idx]['timestamp'] = $history->timestamp;
$slahistory[$idx]['userid'] = $history->userid;
if ($slahistory[$idx]['actualtime'] <= $slahistory[$idx]['targettime']) {
$slahistory[$idx]['targetmet'] = TRUE;
} else {
$slahistory[$idx]['targetmet'] = FALSE;
}
$prevtime = $history->timestamp;
$idx++;
}
// Get next target, but only if incident is still open
if ($incident->status != 2 and $incident->status != 7) {
$target = incident_get_next_target($incidentid);
$slahistory[$idx]['targetsla'] = $target->type;
switch ($target->type) {
case 'initialresponse':
$slahistory[$idx]['targettime'] = $level->initial_response_mins;
break;
case 'probdef':
$slahistory[$idx]['targettime'] = $level->prob_determ_mins;
break;
case 'actionplan':
$slahistory[$idx]['targettime'] = $level->action_plan_mins;
break;
case 'solution':
$slahistory[$idx]['targettime'] = $level->resolution_days * $working_day_mins;
break;
default:
$slahistory[$idx]['targettime'] = 0;
}
$slahistory[$idx]['actualtime'] = $target->since;
if ($slahistory[$idx]['actualtime'] <= $slahistory[$idx]['targettime']) {
$slahistory[$idx]['targetmet'] = TRUE;
} else {
$slahistory[$idx]['targetmet'] = FALSE;
}
$slahistory[$idx]['timestamp'] = 0;
}
return $slahistory;
}
/**
* Returns number of closed incidents that were opened within the period giving
* the average duration in minutes and the average worked time in minutes
* @author Ivan Lucas
*/
function average_incident_duration($start, $end, $states)
{
global $dbIncidents;
$sql = "SELECT opened, closed, (closed - opened) AS duration_closed, i.id AS incidentid ";
$sql .= "FROM `{$dbIncidents}` AS i ";
$sql .= "WHERE status='2' ";
if ($start > 0) {
$sql .= "AND opened >= {$start} ";
}
if ($end > 0) {
$sql .= "AND opened <= {$end} ";
}
$result = mysql_query($sql);
if (mysql_error()) {
trigger_error(mysql_error(), E_USER_WARNING);
}
$totalduration = 0;
$totalworkingduration = 0;
$countclosed = 0;
$total_unique_owners = 0;
while ($row = mysql_fetch_object($result)) {
$working_time = calculate_incident_working_time($row->incidentid, $row->opened, $row->closed, $states);
if ($row->duration_closed > 0) {
$totalduration = $totalduration + $row->duration_closed;
$totalworkingduration += $working_time;
$cio = count_incident_stats($row->incidentid);
$total_unique_owners += $cio[0];
$total_updates += $cio[1];
$countclosed++;
}
}
$total_number_updates = number_format($countclosed == 0 ? 0 : $total_updates / $countclosed, 1);
$average_owners = number_format($countclosed == 0 ? 0 : $total_unique_owners / $countclosed, 1);
$average_incident_duration = $countclosed == 0 ? 0 : $totalduration / $countclosed / 60;
$average_worked_minutes = $countclosed == 0 ? 0 : $totalworkingduration / $countclosed;
return array($countclosed, $average_incident_duration, $average_worked_minutes, $average_owners, $total_updates, $total_number_updates);
}
function incident_get_next_target($incidentid)
{
global $now;
// Find the most recent SLA target that was met
$sql = "SELECT sla,timestamp FROM `{$GLOBALS['dbUpdates']}` WHERE incidentid='{$incidentid}' AND type='slamet' ORDER BY id DESC LIMIT 1";
$result = mysql_query($sql);
if (mysql_error()) {
trigger_error(mysql_error(), E_USER_WARNING);
}
$target = '';
if (mysql_num_rows($result) > 0) {
$upd = mysql_fetch_object($result);
switch ($upd->sla) {
case 'opened':
$target->type = 'initialresponse';
break;
case 'initialresponse':
$target->type = 'probdef';
break;
case 'probdef':
$target->type = 'actionplan';
break;
case 'actionplan':
$target->type = 'solution';
break;
case 'solution':
$target->type = '';
break;
//case 'solution': $target->type = 'probdef'; break;
//case 'solution': $target->type = 'probdef'; break;
case 'closed':
$target->type = 'opened';
break;
}
$target->since = calculate_incident_working_time($incidentid, $upd->timestamp, $now);
} else {
$target->type = 'regularcontact';
$target->since = 0;
}
return $target;
}
/** Calculate SLA times
* @author Tom Gerrard
* @note Moved from htdocs/auto/timecalc.php by INL for 3.40 release
**/
function saction_TimeCalc()
{
global $now;
global $dbIncidents, $dbServiceLevels, $dbMaintenance, $dbUpdates;
global $GLOBALS, $CONFIG;
$success = TRUE;
// FIXME this should only run INSIDE the working day
// FIXME ? this will not update the database fully if two SLAs have been met since last run - does it matter ?
if ($CONFIG['debug']) {
//debug_log("Calculating SLA times");
$sql = "SELECT id, title, maintenanceid, priority, slaemail, slanotice, servicelevel, status, owner ";
}
$sql .= "FROM `{$dbIncidents}` WHERE status != " . STATUS_CLOSED . " AND status != " . STATUS_CLOSING;
$incident_result = mysql_query($sql);
if (mysql_error()) {
trigger_error(mysql_error(), E_USER_WARNING);
$success = FALSE;
}
while ($incident = mysql_fetch_array($incident_result)) {
// Get the service level timings for this class of incident, we may have one
// from the incident itself, otherwise look at contract type
if ($incident['servicelevel'] == '') {
$sql = "SELECT tag FROM `{$dbServiceLevels}` s, `{$dbMaintenance}` m ";
$sql .= "WHERE m.id = '{$incident['maintenanceid']}' AND s.id = m.servicelevelid";
$result = mysql_query($sql);
if (mysql_error()) {
trigger_error(mysql_error(), E_USER_WARNING);
}
$t = mysql_fetch_row($sql);
$tag = $t[0];
mysql_free_result($result);
} else {
$tag = $incident['servicelevel'];
}
if ($CONFIG['debug']) {
//debug_log($incident['id']." is a $tag incident");
$newReviewTime = -1;
}
$newSlaTime = -1;
$sql = "SELECT id, type, sla, timestamp, currentstatus FROM `{$dbUpdates}` WHERE incidentid='{$incident['id']}' ";
$sql .= " AND type = 'slamet' ORDER BY id DESC LIMIT 1";
$update_result = mysql_query($sql);
if (mysql_error()) {
trigger_error(mysql_error(), E_USER_WARNING);
$success = FALSE;
}
if (mysql_num_rows($update_result) != 1) {
//if ($CONFIG['debug']) //debug_log("Cannot find SLA information for incident ".$incident['id'].", skipping");
} else {
$slaInfo = mysql_fetch_array($update_result);
$newSlaTime = calculate_incident_working_time($incident['id'], $slaInfo['timestamp'], $now);
if ($CONFIG['debug']) {
//debug_log(" Last SLA record is ".$slaInfo['sla']." at ".date("jS F Y H:i",$slaInfo['timestamp'])." which is $newSlaTime working minutes ago");
}
}
mysql_free_result($update_result);
$sql = "SELECT id, type, sla, timestamp, currentstatus, currentowner FROM `{$dbUpdates}` WHERE incidentid='{$incident['id']}' ";
$sql .= "AND type='reviewmet' ORDER BY id DESC LIMIT 1";
$update_result = mysql_query($sql);
if (mysql_error()) {
trigger_error(mysql_error(), E_USER_WARNING);
$success = FALSE;
}
if (mysql_num_rows($update_result) != 1) {
//if ($CONFIG['debug']) //debug_log("Cannot find review information for incident ".$incident['id'].", skipping");
} else {
$reviewInfo = mysql_fetch_array($update_result);
$newReviewTime = floor($now - $reviewInfo['timestamp']) / 60;
if ($CONFIG['debug']) {
//if ($reviewInfo['currentowner'] != 0) //debug_log("There has been no review on incident {$incident['id']}, which was opened $newReviewTime minutes ago");
}
trigger('TRIGGER_INCIDENT_REVIEW_DUE', array('incidentid' => $incident['id'], 'time' => $newReviewTime));
}
mysql_free_result($update_result);
if ($newSlaTime != -1) {
// Get these time of NEXT SLA requirement in minutes
$coefficient = 1;
$NextslaName = $GLOBALS['strSLATarget'];
switch ($slaInfo['sla']) {
case 'opened':
$slaRequest = 'initial_response_mins';
$NextslaName = $GLOBALS['strInitialResponse'];
break;
case 'initialresponse':
$slaRequest = 'prob_determ_mins';
$NextslaName = $GLOBALS['strProblemDefinition'];
break;
case 'probdef':
$slaRequest = 'action_plan_mins';
$NextslaName = $GLOBALS['strActionPlan'];
break;
case 'actionplan':
$slaRequest = 'resolution_days';
$NextslaName = $GLOBALS['strResolutionReprioritisation'];
$coefficient = ($CONFIG['end_working_day'] - $CONFIG['start_working_day']) / 60;
break;
case 'solution':
$slaRequest = 'initial_response_mins';
$NextslaName = $GLOBALS['strInitialResponse'];
break;
}
// Query the database for the next SLA and review times...
$sql = "SELECT ({$slaRequest}*{$coefficient}) as 'next_sla_time', review_days ";
$sql .= "FROM `{$dbServiceLevels}` WHERE tag = '{$tag}' AND priority = '{$incident['priority']}'";
$result = mysql_query($sql);
if (mysql_error()) {
trigger_error(mysql_error(), E_USER_WARNING);
$success = FALSE;
}
$times = mysql_fetch_assoc($result);
mysql_free_result($result);
if ($CONFIG['debug']) {
//debug_log("The next SLA target should be met in ".$times['next_sla_time']." minutes");
//debug_log("Reviews need to be made every ".($times['review_days']*24*60)." minutes");
}
if ($incident['slanotice'] == 0) {
//reaching SLA
if ($times['next_sla_time'] > 0) {
$reach = $newSlaTime / $times['next_sla_time'];
} else {
$reach = 0;
}
if ($reach >= $CONFIG['urgent_threshold'] * 0.01) {
$timetil = $times['next_sla_time'] - $newSlaTime;
trigger('TRIGGER_INCIDENT_NEARING_SLA', array('incidentid' => $incident['id'], 'nextslatime' => $times['next_sla_time'], 'nextsla' => $NextslaName));
$sql = "UPDATE `{$dbIncidents}` SET slanotice='1' WHERE id='{$incident['id']}'";
mysql_query($sql);
if (mysql_error()) {
trigger_error(mysql_error(), E_USER_WARNING);
}
}
}
}
}
mysql_free_result($incident_result);
return $success;
}
$sql .= " AND u.currentstatus = is.id ";
$sql .= " ORDER BY timestamp ASC";
$result = mysql_query($sql);
if (mysql_error()) {
trigger_error(mysql_error(), E_USER_WARNING);
}
if (mysql_num_rows($result) > 0) {
$updatearray = array();
$last = -1;
$laststatus;
while ($row = mysql_fetch_object($result)) {
$updatearray[$row->currentstatus]['name'] = $row->name;
if ($last == -1) {
$updatearray[$row->currentstatus]['time'] = 0;
} else {
$updatearray[$laststatus]['time'] += 60 * calculate_incident_working_time($row->incidentid, $last, $row->timestamp, array(2, 7));
}
$laststatus = $row->currentstatus;
$last = $row->timestamp;
}
if ($incident->status == 7 or $incident->status == 2) {
$end = $incident->closed;
} else {
$end = $now;
}
$publicholidays = get_public_holidays($incident->opened, $end);
//calculate the last update
$updatearray[$laststatus]['time'] += 60 * calculate_working_time($last, time(), $publicholidays);
echo "<h3>{$strStatusSummary}</h3>";
if (extension_loaded('gd')) {
$data = array();