The Arcive of Official vBulletin Modifications Site.

Mythotical · #1 02-01-2010, 02:04 AM

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:

PHP Code:


			
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:

PHP Code:


			
$response_time = getResponseTime();

Then in my template I have:

HTML Code:

$hour:$minute

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

Thanks
Steve

kh99 · #2 02-01-2010, 08:44 AM

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 · #3 02-01-2010, 02:14 PM

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 · #4 02-01-2010, 04:25 PM

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 · #5 02-01-2010, 04:52 PM

Quote:

Originally Posted by Steve M

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.

Quote:

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 · #6 02-01-2010, 05:03 PM

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 · #7 02-01-2010, 06:00 PM

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

Code:

     $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

Code:

  $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 · #8 02-01-2010, 07:22 PM

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

PHP Code:


			
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 [DATE]1265060826[/DATE] at [TIME]1265060826[/TIME] ---------------

Ok I tested the code, result was the following:

Code:

-175695:10

Both my fields are INT type UNSIGNED.

kh99 · #9 02-01-2010, 08:44 PM

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....

PHP Code:


			
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 · #10 02-02-2010, 01:02 AM

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:

Code:

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 [DATE]1265079985[/DATE] at [TIME]1265079985[/TIME] ---------------

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

--------------- Added [DATE]1265080418[/DATE] at [TIME]1265080418[/TIME] ---------------

Reversed $seconds += ($rep - $tick) to ($tick - $rep) which gives me the following:

Code:

tid=2 tadded=1265002705 radded= seconds=1265002705 i=1
tid=1 tadded=1264641238 radded=1264641238 seconds=1265002705 i=2

And time read out of:

Code:

175694:49

--------------- Added [DATE]1265081378[/DATE] at [TIME]1265081378[/TIME] ---------------

Added a reply to the tid=2 and this is the new output:

Code:

tid=2 tadded=1265002705 radded=1265084927 seconds=-82222 i=1
tid=1 tadded=1264641238 radded=1264641238 seconds=-82222 i=2
-12:34

X vBulletin 3.8.12 by vBS Debug Information
Page Generation 0.10282 seconds Memory Usage 2,309KB Queries Executed 11 (?)
More Information
Template Usage: (1)SHOWTHREAD (1)ad_footer_end (1)ad_footer_start (1)ad_header_end (1)ad_header_logo (1)ad_navbar_below (1)ad_showthread_beforeqr (1)ad_showthread_firstpost (1)ad_showthread_firstpost_sig (1)ad_showthread_firstpost_start (7)bbcode_code (1)bbcode_html (4)bbcode_php (2)bbcode_quote (1)footer (1)forumjump (1)forumrules (1)gobutton (1)header (1)headinclude (1)navbar (3)navbar_link (120)option (1)pagenav (1)pagenav_curpage (1)pagenav_pagelink (10)post_thanks_box (10)post_thanks_button (1)post_thanks_javascript (1)post_thanks_navbar_search (10)post_thanks_postbit_info (10)postbit (10)postbit_onlinestatus (10)postbit_wrapper (1)spacer_close (1)spacer_open (1)tagbit_wrapper Phrase Groups Available: global inlinemod postbit posting reputationlevel showthread	Included Files: ./showthread.php ./global.php ./includes/init.php ./includes/class_core.php ./includes/config.php ./includes/functions.php ./includes/class_hook.php ./includes/modsystem_functions.php ./includes/functions_bigthree.php ./includes/class_postbit.php ./includes/class_bbcode.php ./includes/functions_reputation.php ./includes/functions_post_thanks.php Hooks Called: init_startup init_startup_session_setup_start init_startup_session_setup_complete cache_permissions fetch_threadinfo_query fetch_threadinfo fetch_foruminfo style_fetch cache_templates global_start parse_templates global_setup_complete showthread_start showthread_getinfo forumjump showthread_post_start showthread_query_postids showthread_query bbcode_fetch_tags bbcode_create showthread_postbit_create postbit_factory postbit_display_start post_thanks_function_post_thanks_off_start post_thanks_function_post_thanks_off_end post_thanks_function_fetch_thanks_start post_thanks_function_fetch_thanks_end post_thanks_function_thanked_already_start post_thanks_function_thanked_already_end fetch_musername postbit_imicons bbcode_parse_start bbcode_parse_complete_precache bbcode_parse_complete postbit_display_complete post_thanks_function_can_thank_this_post_start pagenav_page pagenav_complete tag_fetchbit_complete forumrules navbits navbits_complete showthread_complete
Messages:

The Arcive of Official vBulletin Modifications Site.

It is not a VB3 engine, just a parsed copy!