Response Time

[Mythotical]

I am trying to get this to properly give the average response time for tickets. Similar to what is on vb.com but for the life of me it won't work. Gives me -34613 hours and -14 mins, very annoying.

Here is the function:
function getResponseTime()
{
global $db, $vbulletin, $hour, $minute;
$result = $db->query_read("SELECT * FROM `" . TABLE_PREFIX . "pl9_support_tickets` ORDER BY tid DESC LIMIT 30");
$ticket_num = $db->num_rows($result);
if($ticket_num > 0)
{
$i = 0;
$seconds = 0;
while ($res = $db->fetch_array($result)){
$query = $db->query_first("SELECT radded FROM `" . TABLE_PREFIX . "pl9_support_replies` WHERE ticket='". $res['tid'] ."' ORDER BY radded ASC LIMIT 1");
$query_num = $db->num_rows($query);
$rep = strtotime($query['radded']);
$tick = strtotime($res['tadded']);
$seconds += ($rep - $tick);
$i++;
}
if($i>0)
{
$avg = $seconds / $i;
}
} else {
$avg = 0;
}
$hour = round($avg/3600);
$resttime = $avg - ($hour*3600);
$minute = round($resttime/60);
return;
}

The way I use it in my script is adding this line:
$response_time = getResponseTime();

Then in my template I have:
$hour:$minute

Am I trying to do this wrong? Could someone please provide a proper function that will do this right?

Thanks
Steve

[kh99]

What are the types in the database of pl9_support_tickets.tadded and pl9_support_replies.radded? Are they strings of the correct format? I'm thinking maybe they could already be numbers. You could easily look at this (if you haven't already) by building up a string then throwing it in the template somewhere (as an html comment maybe).

Also I don't think you want "round" on the hour calculation, I think you want to use floor() or else make sure you are dividing two integer values.

[Carnage]

kh99 is right; round is the wrong function to use:

http://uk3.php.net/manual/en/function.round.php round() will round 3.5 UP to 4.

I think the logic of your function is off slightly as well, but I'm unable to figure out where.

[Mythotical]

tadded and radded is unix time inserted into the database when a ticket is added or a reply is added. So round() is possibly causing the problem? So far any thing I found that deals with giving an average uses round.

[kh99]

tadded and radded is unix time inserted into the database when a ticket is added or a reply is added.
In that case I don't think you need to call strtotime() on them, you just want to subtract them as the come.

So round() is possibly causing the problem? So far any thing I found that deals with giving an average uses round.

1 hr 45 min = 3600 + (45 * 60) = 6300 seconds.

hrs = 6300 / 3600 = 1.75, rounded up = 2 hrs, but floor(1.75) = 1

[Mythotical]

Ok so using my code above, any suggestion of a way to rewrite. I've stated this before but I learn best from example codes then discussion of various means. I understand what your saying but implementing is my problem. LOL

[kh99]

Well assuming I'm right about those time fields (and I'm not sure), I would say change these two lines to:


$rep = $query['radded'];
$tick = $res['tadded'];

(or I guess you don't really need the intermediate variables, but whatever). When you say those fields are "unix time", what's the actual type of the column (or is that a type I don't know about?). For instance, vBulletin saves unix times, but it uses a database type of INT (unsigned, that is).

Also, change this line to

$hour = floor($avg/3600);

That one I'm pretty sure about, but I don't see how that alone would explain what you're seeing.

[Mythotical]

Yeah they both are INT type, I believe they are unsigned. So this is what I should have:

function getResponseTime()
{
global $db, $vbulletin, $hour, $minute;
$result = $db->query_read("SELECT * FROM `" . TABLE_PREFIX . "pl9_support_tickets` ORDER BY tid DESC LIMIT 30");
$ticket_num = $db->num_rows($result);
if($ticket_num > 0)
{
$i = 0;
$seconds = 0;
while ($res = $db->fetch_array($result)){
$query = $db->query_first("SELECT radded FROM `" . TABLE_PREFIX . "pl9_support_replies` WHERE ticket='". $res['tid'] ."' ORDER BY radded ASC LIMIT 1");
$query_num = $db->num_rows($query);
$rep = $query['radded'];
$tick = $res['tadded'];
$seconds += ($rep - $tick);
$i++;
}
if($i>0)
{
$avg = $seconds / $i;
}
} else {
$avg = 0;
}
$hour = floor($avg/3600);
$resttime = $avg - ($hour*3600);
$minute = floor($resttime/60);
return;
}

So is that what it should look like? If so I will test it right quick.

--------------- Added 1265060826 at 1265060826 ---------------

Ok I tested the code, result was the following:
-175695:10

Both my fields are INT type UNSIGNED.

[kh99]

ok, well if anyone else has an idea, feel free to jump in....

meanwhile, if it were me i'd find a way to display the values and see where they don't look right, like maybe....

function getResponseTime()
{
global $db, $vbulletin, $hour, $minute;
$result = $db->query_read("SELECT * FROM `" . TABLE_PREFIX . "pl9_support_tickets` ORDER BY tid DESC LIMIT 30");
$ticket_num = $db->num_rows($result);
if($ticket_num > 0)
{
$i = 0;
$seconds = 0;
while ($res = $db->fetch_array($result)){
$query = $db->query_first("SELECT radded FROM `" . TABLE_PREFIX . "pl9_support_replies` WHERE ticket='". $res['tid'] ."' ORDER BY radded ASC LIMIT 1");
$query_num = $db->num_rows($query);
$rep = $query['radded'];
$tick = $res['tadded'];
$seconds += ($rep - $tick);
$i++;
$debug_tickets .= "tid=".$res['tid']." tadded=".$res['tadded']." radded=".$query['radded']." seconds=".$seconds." i=".$i."<BR>";
}
if($i>0)
{
$avg = $seconds / $i;
}
} else {
$avg = 0;
}
$hour = floor($avg/3600);
$resttime = $avg - ($hour*3600);
$minute = floor($resttime/60);
return;
}


Then put $debug_tickets in the template somewhere.

[Mythotical]

Ok I had to take it out of a function and make it a straight query just to get an output. Here is the output of the $debug_tickets:
tid=2 tadded=1265002705 radded= seconds=-1265002705 i=1
tid=1 tadded=1264641238 radded=1264641238 seconds=-1265002705 i=2

LOL no idea what I'm even looking at honestly. I recognize the numbers and know a few things but nothing concrete.

--------------- Added 1265079985 at 1265079985 ---------------

BTW, the first line of tid=2, radded= is blank because no reply has been added to that ticket.

--------------- Added 1265080418 at 1265080418 ---------------

Reversed $seconds += ($rep - $tick) to ($tick - $rep) which gives me the following:
tid=2 tadded=1265002705 radded= seconds=1265002705 i=1
tid=1 tadded=1264641238 radded=1264641238 seconds=1265002705 i=2

And time read out of:
175694:49

--------------- Added 1265081378 at 1265081378 ---------------

Added a reply to the tid=2 and this is the new output:
tid=2 tadded=1265002705 radded=1265084927 seconds=-82222 i=1
tid=1 tadded=1264641238 radded=1264641238 seconds=-82222 i=2
-12:34

[kh99]

Yeah, I think that's your problem - if radded is blank you shouldn't be including it in the average, right? You're subtracting the start time from 0 and ending up with a huge negative number. I think you need something like:

if ($rep > 0)
{
$seconds += ($rep - $tick);
$i++;
}

[Mythotical]

Another test, I changed $rep - $tick back to normal and got this:
tid=3 tadded=1265085047 radded=1265085108 seconds=61 i=1
tid=2 tadded=1265002705 radded=1265084927 seconds=82283 i=2
tid=1 tadded=1264641238 radded=1264641238 seconds=82283 i=3
7:37

Good point, I will add that and test.

--------------- Added 1265081713 at 1265081713 ---------------

Do I need this at all?

if($i>0)
{
$avg = $seconds / $i;
}
} else {
$avg = 0;
}

[kh99]

Do I need this at all?


Well that first part keeps you from dividing by 0 if there are no replies. The last part I guess just sets it to 0 if there are no tickets (or there's a database error or something).

[Mythotical]

Ah ok, overall then it is working, showing a high reply time but I assume that is due to the amount of tickets and the replies that are there since it is a testing enviroment.

[kh99]

yeah, you can see the debug output and you can actually do the math yourself. you have 3 tickets with reply times 61 seconds, about 22 hours, 0 seconds, so the average should be around 7 hours.

[Mythotical]

Yeah, pain pills kinda slows down my math skills. LOL

Its all good now, I have it giving an average for last 30 tickets and an average for a single ticket.

Thanks for all the help.