• Home
  • Shell
    • Emacs
    • Perl
    • screen
    • sed
  • Ubuntu
    • VNC
  • Web Development
    • Javascript
    • Joomla
    • MySQL
    • osTicket
  • Windows
    • Gimp

osTicket> Age of Tickets

Oct26
2010
Written by Scott Rowley

This MOD will add the "Age" column to your tickets table
Ex:
Example
include/staff/tickets.inc.php
Replace the original $sortOptions with the following:

$sortOptions=array('date'=>'ticket.created','ID'=>'ticketID','pri'=>'priority_urgency','dept'=>'dept_name','ass'=>'firstname','timeopen'=>'created');

Replace the original $qselect line with the following:

$qselect = 'SELECT DISTINCT ticket.ticket_id,lock_id,ticketID,ticket.dept_id,ticket.staff_id,subject,name,ticket.email,dept_name,staff.firstname,staff.lastname '.
',ticket.status,ticket.source,isoverdue,ticket.created,pri.*,CASE WHEN status = "open" THEN FLOOR(TIME_TO_SEC(TIMEDIFF(now(),ticket.created))/60) ELSE FLOOR(TIME_TO_SEC(TIMEDIFF(ticket.closed,ticket.created))/60) END AS timeopen,count(attach.attach_id) as attachments ';
$qfrom=' FROM '.TICKET_TABLE.' ticket LEFT JOIN '.DEPT_TABLE.' dept ON ticket.dept_id=dept.dept_id '.
' LEFT JOIN '.STAFF_TABLE.' staff ON ticket.staff_id=staff.staff_id';

On my version I removed the "Created" column as I felt it was no longer needed with an "Age" column replacing it:

New Code! This fixed the issue with the >35 days (50339 MySQL time issue).

<!--<td align="center" nowrap><?=Format::db_date($row['created'])?></td>-->
<?
$ticket_id=$row['ticket_id'];
$sql = mysql_query("SELECT UNIX_TIMESTAMP(created) FROM ost_ticket WHERE ticket_id=$ticket_id");
$created_row = mysql_fetch_array($sql);
$created = $created_row['UNIX_TIMESTAMP(created)'];
// closed ticket correct age
if ($status=='closed')
{
// closed ticket
$sql = mysql_query("SELECT UNIX_TIMESTAMP(closed) FROM ost_ticket WHERE ticket_id=$ticket_id");
$closed_row = mysql_fetch_array($sql);
$closed = $closed_row['UNIX_TIMESTAMP(closed)'];
$diff = $closed - $created;
}
else
{
$now=date(U);
$diff = $now - $created;
}
?>
<td class="nohover" align="center" style="color:<?$color="#33FF66"; $old = round($diff / 86400); if ($old >= 4){$color="red"; print ($color);} ?> ">
<?
$diff = round($diff / 60);
$min = "min";
$mins = "mins";
$hours = "hours";
$hour = "hour";
$day = "day";
$days = "days";
if ( $diff <= 1 ){
print ($diff . " " . $min);
}elseif ( $diff > 1 && $diff <= 59 ){
print ($diff . " " . $mins);
}elseif ( $diff >= 60 && $diff <= 119 ){
$diff = round($diff / 60);
print (1 . " " . $hour);
}elseif ( $diff >= 120 && $diff <= 1439 ){
$diff = round($diff / 60);
print ($diff . " " . $hours);
}elseif ( $diff >= 1440 && $diff <= 2879 ){
print (1 . " " . $day);
}elseif ( $diff >= 2880 ){
$diff = round($diff / 1440);
print ($diff . " " . $days);
}else {};
?>
</td>

Around the lines in 385 or so you’ll have several th (table headers). You’ll need to add in the following where you want it to show up in the table. (and make sure to comment out your Date column as well or everything will be off by a column)

<th width='70px'>
<a href="tickets.php?sort=timeopen&order=<?=$negorder?><?=$qstr?>" title="Sort By Age <?=$negorder?>">Age</a></th>

Feel free to ask questions, thanks.

Posted in osTicket - Tagged mod, modification, osTicket, ticket
« osTicket> Assigned To column
» Gimp> Gradient text

38 Comments

  1. Tom's Gravatar Tom
    October 29, 2010 at 12:18 am

    Great!

    Where to add the large block of code was not apparent.

    Cannot get the Age column to sort.

    added to your last line…

    <a href="tickets.php?sort=timeopen&order=” title=”Sort By Age “>Age

    … it seeems to sort on something but not on age.

    Is there something I am missing?

  2. scottro's Gravatar scottro
    October 29, 2010 at 11:10 am

    Did you get the $sortOptions code in and the original commented out ok? If so feel free to just email me your file and I’ll take a look at it. scott (at) sudobash (dot) net.

  3. scottro's Gravatar scottro
    November 4, 2010 at 12:56 pm

    Tom,

    The problem was in my code, the sortOptions was incorrect. at the end of that line it should be ,’timeopen’=>’created’);

    NOT

    ,’timeopen’=>’timeopen’);

    This worked for me, let me know if it works ok for you. Code has been updated in the article.

  4. Wavetable's Gravatar Wavetable
    November 11, 2010 at 9:44 am

    Hello,

    thank you for your Mod and support.
    But where i must build in the code, which begins with the line “<!—->”
    All other mods are done, only this code is missing.

    Thanks for helping me.

    Ps.: Sorry for my english, I’m german. 😉

    Greetings,
    Wavetable

  5. scottro's Gravatar scottro
    November 11, 2010 at 9:50 am

    Thats actually code thats commented out. Just find the uncommented code:

    <td align=”center” nowrap><?=Format::db_date($row[‘created’])?></td>

    Then comment that out and add all that code right below it.

  6. Frank's Gravatar Frank
    May 11, 2011 at 1:30 am

    Great stuff 🙂 works perfectly. Its a must-have function that should have been implemented as standard.

    thanks!

    • Scott Rowley's Gravatar Scott Rowley
      May 11, 2011 at 7:02 am

      Thanks! Glad it worked for you. Also, I believe the function is included in 1.7 (whenever its finally released).

  7. Pedro Uva's Gravatar Pedro Uva
    May 19, 2011 at 3:44 pm

    Hello, I did this but now I have a problem caused by this modification. I need to return the column ticket on the tickets to make an white collor when we don’t interact with it and bold, when interact.

    Tks

    Pedro

    • Scott Rowley's Gravatar Scott Rowley
      May 22, 2011 at 1:37 pm

      Double check your work, this MOD has no effect on that. Perhaps you overwrote too much of the code?

      • Pedro Uva's Gravatar Pedro Uva
        August 31, 2011 at 4:33 pm

        Please, I dont know how to correct this modification. I made exactly all modifications in the tutorial, and i need your help to solve this problem. When I remove this mod, I have no problem and when I put this, dont work. What is the problem? Tell me about the file or part of code whit I correct this. Tks

        • Scott Rowley's Gravatar Scott Rowley
          September 1, 2011 at 7:35 am

          Pedro,

          If you want to email me your files I’ll take a look and get it working. It may be several days before I can get them back to you though as tomorrow my wife is delivering our 3rd baby. 🙂

  8. Eric's Gravatar Eric
    June 24, 2011 at 5:57 am

    Hello,

    Where should I put the long code, what file and what line?

    thank you again

    • Scott Rowley's Gravatar Scott Rowley
      June 24, 2011 at 10:38 am

      Thats still within include/staff/tickets.inc.php, find the following and replace it with the above “long code”

      <td align=”center” nowrap><?=Format::db_date($row[‘created’])?></td>

  9. alogan's Gravatar alogan
    October 4, 2011 at 1:41 pm

    I am trying to add this function to my client end ticket view.
    So far I have the following, it looks like its working but the
    $diff = round($diff / 125400); is a number I came up with through trail and error.

    Any idea how to make this truly work.

    The following is my complete code.
    Thanks in advance for your help.

    Ticket Age:

    <?
    $diff = round($diff / 125400);
    $min = "min";
    $mins = "mins";
    $hours = "hours";
    $hour = "hour";
    $day = "day";
    $days = "days";
    if ( $diff 1 && $diff = 60 && $diff = 120 && $diff = 1440 && $diff = 2880 ){
    $diff = round($diff / 1440);
    print ($diff . " " . $days);
    }else {};
    ?>

    • alogan's Gravatar alogan
      October 4, 2011 at 1:45 pm

      my full code can be found at http://jsfiddle.net/CvhkM/1457/

    • alogan's Gravatar alogan
      October 4, 2011 at 2:02 pm

      Some of my code got scrubbed out of my reply so please check the full code link.

      Thanks

    • alogan's Gravatar alogan
      October 6, 2011 at 9:48 am

      I was able to answer my own question by changing top line to $ticket_id=$ticket->getExtId()

      Full code is at http://jsfiddle.net/CvhkM/1475/

  10. Pedro Pinheiro's Gravatar Pedro Pinheiro
    November 17, 2011 at 4:59 pm

    The following code will calculate age of closed tickets.

    Replace

    $now=date(U);
    $diff = $now - $created;

    With

    // closed ticket correct age
    if ($status=='closed')
    {
    // closed ticket
    $sql = mysql_query("SELECT UNIX_TIMESTAMP(closed) FROM ost_ticket WHERE ticket_id=$ticket_id");
    $closed_row = mysql_fetch_array($sql);
    $closed = $closed_row['UNIX_TIMESTAMP(closed)'];
    $diff = $closed - $created;
    }
    else
    {
    $now=date(U);
    $diff = $now - $created;
    }

    • Scott Rowley's Gravatar Scott Rowley
      November 17, 2011 at 5:11 pm

      Awesome, Pedro. Thanks very much. I have updated the code in the tutorial.

      • Keith Salisbury's Gravatar Keith Salisbury
        March 8, 2012 at 3:52 pm

        I’m having a bit of trouble with this one.
        All seems well on the open ticket display. Age is displayed appropriately.
        The trouble for me comes on the closed tickets. The Age of the ticket when it was closed is displayed, but the sort seems to be by date created, not by the tickets age like the column would indicate. It’s a bit confusing since the column isn’t there to judge how it’s sorting.
        How can I have age of the ticket show on the open tab and have created on, closed on, or simply omit the column on the closed ticket page.
        Hopefully my ramblings make some sense.

        Keith

        • Scott Rowley's Gravatar Scott Rowley
          March 8, 2012 at 4:29 pm

          Keith,

          Understandable. If you don’t want the column to show up you can wrap the td and th code up in the following:

          if($_GET[‘status’]!=’closed’){ The_TDorTH_code_here }

  11. fackeid's Gravatar fackeid
    November 21, 2011 at 4:09 pm

    Hey,
    Suppose i want both
    Age + Created date

    Then how to alter this MOD?

  12. kaj's Gravatar kaj
    November 27, 2011 at 2:01 pm

    HI there,
    Thanks for your great work..

    Having troublw with this one.. The tickets aren’t aging or at least not showing it in column , all i get is a “0” for age..
    cannot find a mistake, do notice that ticket list view has dropped down from the REFRESH button by quite a way.. only on pc, mobile device is ok ????

    HELP
    kaj

    • Scott Rowley's Gravatar Scott Rowley
      November 27, 2011 at 2:28 pm

      Hey Kaj, thanks for the note. I’ll try and give you a hand when I have time but it might be awhile as I have a full time job plus a part time contracting job right now (not to mention 3 kids and a family to give some time to.) I’ll take a look at it when (if) I get some time.

  13. Barry Murphy's Gravatar Barry Murphy
    April 19, 2012 at 4:13 pm

    Hi,

    I’ve been using this mod for sometime and its great, thanks for building it!
    I’m wondering if it’s possible to have the age include the last reply from notes. Quite often we have tickets that are internal in nature and staff are adding information to the internal notes but not replying to customers so the ticket doesnt show as being updated. Is there a way we can include internal notes to show the last time since any reply to the ticket, be it internal or a message.

    Many thanks in advance
    Barry

    • Scott Rowley's Gravatar Scott Rowley
      April 19, 2012 at 9:42 pm

      Barry, thanks I’m glad it’s working out for you!

      Anything is possible so long as you have the data! Unfortunately, I don’t have the time right now to code something up. You may want to ask for assistance on the forum and see if maybe someone else has some time to work on it.

      Good luck!

  14. marw's Gravatar marw
    April 27, 2012 at 4:36 am

    Hi Scott,

    I got “500 — Internal server error.
    There is a problem with the resource you are looking for, and it cannot be displayed.” every time I have tried to add your age MOD or age + assigned to MOD. Can you help me with both?

    Best Regards
    Marw

    • Scott Rowley's Gravatar Scott Rowley
      April 27, 2012 at 7:30 am

      Do you have access to your Apache error log?

  15. Todd St Pierre's Gravatar Todd St Pierre
    June 26, 2012 at 9:42 am

    Great code, but where do I change the code so the tickets are sorted with the most recent ticket displayed at the top of the list?

    • Scott Rowley's Gravatar Scott Rowley
      June 26, 2012 at 10:33 am

      I believe it’s sorted by default by ticket ID number which means the newest should already be at the top. What’s currently occurring for you?

      • Todd St Pierre's Gravatar Todd St Pierre
        June 27, 2012 at 7:07 am

        doesn’t seem to be sorted at all.

  16. Steve Aubrey's Gravatar Steve Aubrey
    September 14, 2012 at 3:23 pm

    A satisfied customer (again). Thanks, Scott, for collecting and maintaining this code.

    I made some changes to the bottom part of the big block — not functional changes, but to make the code cleaner. I replaced the less-than-or-equal with a “less than one higher”, and I cleaned up the elseif (each evaluation would get hit only if the prior one failed, so there is no need for the lower boundary check). Again, your code works just fine — this is the picky proofreader in me coming out.


    if ( $diff < 2 ){
    print ($diff . " " . $min);
    }elseif ( $diff < 60 ){
    print ($diff . " " . $mins);
    }elseif ( $diff < 120 ){
    $diff = round($diff / 60);
    print (1 . " " . $hour);
    }elseif ( $diff < 1440 ){
    $diff = round($diff / 60);
    print ($diff . " " . $hours);
    }elseif ( $diff < 2880 ){
    print (1 . " " . $day);
    }else {
    $diff = round($diff / 1440);
    print ($diff . " " . $days);
    };

  17. BrianV's Gravatar BrianV
    October 31, 2012 at 5:00 pm

    Hi,

    When i create a new ticket it shows me 15645 days instead of 0 minutes.
    My date settings are correct.

  18. Gurpreet's Gravatar Gurpreet
    November 20, 2012 at 7:00 am

    The code looks quite useful. Thanks for that. Please help that where should I add the following code (which has to be added as new code) and starts with:
    <!—->
    <?
    $ticket_id=$row['ticket_id'];
    .
    .
    .

    Also, the age header is displayed but still date created is getting shown in that column. Also one count with value 15644 is showing up on the top of the table displaying the tickets. Please help.

  19. phpshell's Gravatar phpshell
    September 4, 2013 at 12:58 pm

    where we can put this code ?
    New Code! This fixed the issue with the >35 days (50339 MySQL time issue).

    • phpshell's Gravatar phpshell
      September 9, 2013 at 3:42 pm

      everything now normal with me only
      i have problem with assign to field shown as Not assigned if i log in to TT already assign

    • Scott Rowley's Gravatar Scott Rowley
      September 11, 2013 at 10:26 am

      If you are just updating your code to make this work then just overwrite what’s in the following box with what you currently have.

  20. Todd St Pierre's Gravatar Todd St Pierre
    October 29, 2013 at 7:02 pm

    Does this ticket age mod work in v1.7.2?

Corrections? Questions? Comments?

Find an error?
Everything work out great for you?
Have some feedback?
Like to see something added to the article?

PLEASE leave us a comment after the article and let us know how we are doing, or if something needs corrected, improved or clarified.

Thank you!
- The Management

Advertisement

Sudo Bash
By Geeks - For Geeks

Back to Top