Average Response Time

[Corey]

Hey,

Something I would like implemented is average response time. It would pretty much be like how vBulletin (http://www.vbulletin.com) does it. They have a block on the left side on their site, that says "Average response time" and then under that says (last 30 support issues)
X hr : XX min

Please note this is NOT a guarantee that every ticket will be responded to in this time frame. This is an average, more complicated problems may require more time to resolve.

I think it's a good feature to add.

[NetLink]

Hi! I've created a small script to calculate the average response time. It's a class that can be used on any part of your website by simply adding the following lines on the page where you'd like the output to appear:

<?php
include('include/class.response_time.php');
$response_time = new response_time();
echo "<p>Average response time: $response_time->output.</p>";
?>

This would display (as an example):

"Average response time: 120 minutes."

... or ...

"Average response time: 1.4 hours."

... depending on the response time in seconds.

I've attached the class below. I haven't tested it much yet so let me know what you think of it...

<?php

class response_time {

var $dbhost = 'localhost';
var $dbname = 'DATABASE_NAME';
var $dbuser = 'USERNAME';
var $dbpass = 'PASSWORD';

var $output;

function response_time() {

$connection = $this->connect();

$query = "SELECT * FROM ost_ticket ORDER BY ticket_id DESC LIMIT 10";
$result = mysql_query($query);

$o = array();

$i=0;

$seconds = 0;

while( $r=mysql_fetch_assoc($result) ) {

$q = mysql_query("SELECT created FROM ost_ticket_response WHERE ticket_id='".$r['ticket_id']."' ORDER BY response_id LIMIT 1") or die(mysql_error());

if ( mysql_num_rows($q) > 0 ) {
$created = strtotime($r['created']);
$updated = strtotime(mysql_result($q,0));
$seconds += $updated - $created;
$i++;
}

}

$avg = $seconds / $i;

$minutes = round( $avg / 60 );
if ( $minutes > 120 ) {
$output = round( ($minutes / 60), 1 ) . " hours";
} else {
$output = "$minutes minutes";
}

$this->output = $output;

}

function connect() {
$connection = mysql_connect($this->dbhost,$this->dbuser,$this->dbpass);
mysql_select_db($dbname,$connection);
return $connection;
}

}

?>

[jazzy639]

I tried this however it just says:

"Average response time: 0 minutes."

I've in putted the correct DB username/password/etc. (this should really take this from the config file)

[NetLink]

Hi jazzy639,

I made that script over a year ago and haven't really checked it since. It was working at the time, but will need to check if there have been any updates to osTicket that might render my script useless (well, not useless, but some modifications might need to be made).

This might be a dumb question, but have you added any tickets/responses since implementing the code to test it?

I'll have another look at it as soon as I can and will keep you updated here.

[jazzy639]

Thanks for the quick reply. Managed to sort it. My fault.... My OST installation has table prefixes not by ost, something else.

I'll see if I can mod it to use the default configuration file, I'm not so good at PHP though, more of an ASP person :) Thanks for the great script.

Just checking: does this get the average response time from when the customer opened the ticket to when the first reply was? What if there is no replies to a ticket yet?

edit: ok I see the SQL, checks to see if there is a reply.

[NetLink]

No problem. Glad you find the script useful.

Let me know how you get on with the config file. If you can't get it working I can have a look at it.

Yes, it's the time between opening the ticket to first response from staff.

[NetLink]

What if there is no replies to a ticket yet?

Good question. I'll need to get myself familiar with the script again :) but I think if there are no responses, it will not be counted. I'll need to double check that though.