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

osTicket> Assigned To AND Age of Tickets

Oct26
2010
Written by Scott Rowley

"Assigned To" is a variation on webpragmatists original "Assigned To" MOD as seen on the osTicket forum.
"Age of Tickets" is sudobash.net original coding
http://www.sudobash.net/images/age_assigned.png

include/staff/tickets.inc.php
Remember to comment out the original sortOptions

?
1
2
3
//I admit this crap sucks…but who cares??
$sortOptions=array('date'=>'ticket.created','ID'=> 'ticketID','pri'=>'priority_urgency','dept'=>'dept_name','ass'=>'firstname','timeopen'=>'timeopen');
//$sortOptions=array('date'=>'ticket.created','ID'=>'ticketID','pri'=>'priority_urgency','dept'=>'dept_name');

Remember to comment out the first entry for $qselect and $qfrom

?
1
2
3
$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';

Place this with the other table headers (wherever you want it to show up in the table)

?
1
<th width="150" ><a href="tickets.php?sort=ass&order=" title="Sort By Assignee ">Assigned To</a></th>

And again for the Age column

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

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

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
<!--<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>
?
<td nowrap><?=($row['firstname']) ? $row['firstname'] : '&nbsp;';?> <?=($row['lastname']) ? $row['lastname'] : '';?></td>

Also make sure to comment out your "Date" — you won’t need it now that you know the Age.

You should now be able to refresh your Ticket page and have a nice "Assigned To" column along with the "Age of Tickets" column.

Now you may notice that after you close a ticket that is assigned to someone that assignment gets removed. I have found this undesirable so we’ll edit the following

include/class.ticket.php
remove staff_id=0 from the following line:

?
1
$sql= 'UPDATE '.TICKET_TABLE.' SET status='.db_input('closed').',isoverdue=0,duedate="NULL",updated=NOW(),closed=NOW() '.

This will still make the ticket show up under "My Tickets" in order to get it to stop doing that (if you desire), then change the following entry from:
include/staff/tickets.inc.php

?
1
$qwhere.=' AND ticket.staff_id='.db_input($staffId);

to:

?
1
$qwhere.=' AND ticket.status="open" AND ticket.staff_id='.db_input($staffId);

You’ll also need to change the following (otherwise it will count that you have tickets assigned but won’t list them thus confusing everyone).
scp/tickets.php

?
1
'LEFT JOIN '.TICKET_TABLE.' assigned ON assigned.ticket_id=ticket.ticket_id AND assigned.staff_id='.db_input($thisuser->getId());

To:

?
1
'LEFT JOIN '.TICKET_TABLE.' assigned ON assigned.ticket_id=ticket.ticket_id AND open.status="Open" AND assigned.staff_id='.db_input($thisuser->getId());
Posted in osTicket - Tagged age, assign, assigned, mod, modification, osTicket, ticket
« osTicket> Change default action
» osTicket> Assigned To column

30 Comments

  1. Dennis Hall's Gravatar Dennis Hall
    March 15, 2011 at 8:14 pm

    this is a great mod packaged together nicely. i have one problem, after completing the mod it doesn’t show a border on the left of the age column….not a big issue just kind of an eyesore. i went with the no color option but even tried the color option with the same results. Thanks for putting this together

    • scottro's Gravatar scottro
      March 16, 2011 at 4:14 pm

      Hmm, haven’t run into that problem. Have you modified your CSS at all prior to this? I suppose if nothing else you could alter the style of that particular section. Glad its working for you otherwise though!

      • Dennis Hall's Gravatar Dennis Hall
        August 19, 2011 at 3:33 pm

        I figured out it was just chrome not ‘showing’ the border to the left. No problems now. Now i am trying to get the total time on a project to be a column at the end with use of the time mod

  2. M450N's Gravatar M450N
    May 30, 2011 at 8:11 am

    Are you able to post the edited files?
    I’m having problems implementing these both at the same time on 1.6ST

    I get as far as the headers being shown and the age and colour showing but the assigned staff member fields are not there and everything is offset from the header.
    Thanks

    • Scott Rowley's Gravatar Scott Rowley
      May 30, 2011 at 9:27 am

      I don’t have the files prepared with these MODs alone as I have several other MODs in place as well, however, if you’d like you can email me the files and I can take a look and fix them for you. Scott (at) sudobash (dot) net.

  3. Jason Sica's Gravatar Jason Sica
    October 4, 2011 at 10:31 am

    Man your scripts are amazing, it’s helped so much.

    • Scott Rowley's Gravatar Scott Rowley
      October 4, 2011 at 10:53 am

      Awesome, thanks very much 🙂

  4. Dennis Hall's Gravatar Dennis Hall
    December 21, 2011 at 6:55 pm

    I’ve got a crazy request…can the age be represented differently on the closed tickets, say the time in days it took to resolve the issue instead of just the original age of the ticket? If you did this, it would be greatly appreciated

    • Scott Rowley's Gravatar Scott Rowley
      December 21, 2011 at 7:14 pm

      If I get some time I’ll see about adding this. 🙂

      • Dennis Hall's Gravatar Dennis Hall
        December 21, 2011 at 9:08 pm

        I played around with it a bit today….if we were to put an IF statement regarding ?status=closed before it writes the AGE column and have a different formula/SQL statement for the age column it could be done with closed MINUS created fields right? Just not sure how to do it SQL wise 🙂

        • Dennis Hall's Gravatar Dennis Hall
          December 21, 2011 at 10:08 pm

          made my own little workaround.
          in the headers for the main tickets view I put this in place of AGE

          $action = $_GET[‘status’];
          if ($action == ”){
          ?>Age

          Resolution

          And I changed it to be the difference of closed and created to get a figure and used similar IF statement above to plug it in if on the Closed ticket view.

          May be clunky, but it works for me 🙂

          • Scott Rowley's Gravatar Scott Rowley
            December 21, 2011 at 11:11 pm

            Something to take into account is if a ticket has been reopened. If its been reopened it still has a “Closed” date stamp. Not sure if you solution needs to worry about that or not but I remember bumping into that at one point.

  5. Dennis Hall's Gravatar Dennis Hall
    December 22, 2011 at 8:43 pm

    Not really an issue for me….it changes the view so if it is open it doesnt show resolution, it shows age. unless I am missing something here 🙂

  6. Derek's Gravatar Derek
    March 7, 2012 at 10:21 pm

    I may be missing something. I have everything working perfectly except I dont see the code to add to make the Assigned To column actually work. I see the code to add the header for Assigned to. And I have added that fine. I have also added the Header for Age. And added the code for the Color for Age and that works fine.

    But I dont see any code to add to the lower part of the table to pull the Assigned to info. Where do I find this at?

    Thanks

    • Derek's Gravatar Derek
      March 7, 2012 at 10:49 pm

      Never mind. I found the code on the other thread.

      But it is missing from this thread. Here is the missing code:

      • Scott Rowley's Gravatar Scott Rowley
        March 7, 2012 at 11:01 pm

        Sorry about that, I’ve added it in from the other post.

        • Derek's Gravatar Derek
          March 7, 2012 at 11:42 pm

          Thanks Scott,

          I have added several of your mods tonight and I am very impressed with your code. The reports are awsome. Keep up the great work.

          Derek

  7. JDC's Gravatar JDC
    March 21, 2012 at 11:58 am

    Hello,
    Thanks for these great mods!

    In order to get the ‘My Tickets’ count to show up properly, I had to use this line in \scp\tickets.php:
    ‘LEFT JOIN ‘.TICKET_TABLE.’ assigned ON assigned.ticket_id=ticket.ticket_id AND assigned.status=\’open\’ AND assigned.staff_id=’.db_input($thisuser->getId());

  8. Mathesonian's Gravatar Mathesonian
    June 18, 2012 at 11:59 pm

    Hey, Thanks for the great mods!

    I have been having a problem with this one though.
    Every time I try to add the last part of this mod in scp/tickets.php

    ‘LEFT JOIN ‘.TICKET_TABLE.’ assigned ON assigned.ticket_id=ticket.ticket_id AND open.status=’Open’ AND assigned.staff_id=’.db_input($thisuser->getId());

    I get the following error
    “Parse error: syntax error, unexpected T_STRING in /home/matheson/public_html/osticket/scp/tickets.php on line 442”

    • Scott Rowley's Gravatar Scott Rowley
      August 13, 2012 at 10:18 am

      Yep, just ran into that myself. Sorry for the issue. I have updated the code to use “Open” instead of ‘Open’ as it was breaking outside of the variable.

  9. Mathesonian's Gravatar Mathesonian
    June 19, 2012 at 12:13 am

    Nevermind,

    I think its working now when I changed the line to

    ‘LEFT JOIN ‘.TICKET_TABLE.’ assigned ON assigned.ticket_id=ticket.ticket_id AND assigned.status=\’open\’ AND assigned.staff_id=’.db_input($thisuser->getId());

    Thanks again for a great mod!

  10. john's Gravatar john
    October 19, 2012 at 10:00 am

    Thanks for this MOD.

    just one question…in the age column, the colors aren’t working, can u help me?

    • Scott Rowley's Gravatar Scott Rowley
      November 5, 2012 at 1:02 pm

      What’s happening instead? Is code outputting or are the colors simply not showing up?

      • john's Gravatar john
        January 3, 2013 at 12:00 pm

        i have problems with this line

        <td class="nohover" align="center" style="color:= 4){$color=”red”; print ($color);} ?> “>

        • john's Gravatar john
          January 3, 2013 at 12:00 pm

          the colors aren’t showing

  11. Alon's Gravatar Alon
    November 3, 2012 at 9:53 am

    Hi,

    Thanks for the mod.

    I have an issue: in the Age column every ticket shows 15648 days.
    I’m using w2k8..

    I also implemented Reports and it’s great 🙂

    Thanks,

    Alon

    • john's Gravatar john
      January 3, 2013 at 5:14 pm

      Hi, in your sql sentences check the prefix for the tables

      $sql = mysql_query("SELECT UNIX_TIMESTAMP(closed) FROM ost_ticket .....

      if you rename the tables with other prefix you need to change here with that prefix.

      I solved with that.

      Regards.

  12. Imran's Gravatar Imran
    February 8, 2013 at 10:00 am

    Scott –

    Great mod. I actually ended up ditching Ticket Age, but I used what I learned here to include a couple of other columns. The first was “Last Response” which was easy enough. It was primarily to make sure that our staff was keeping constant communication with customers regardless of ticket age (we’re not measured on time-to-closure).

    The second, which was a big hit with the group was “Message Age.” First we check to see if the Last Message came in after the Last Response .. if so, then show Last Message age highlighted. Provides an immediately actionable dashboard. Let me know if you’d be interested in possibly cleaning up the code and publishing.

    • Jon's Gravatar Jon
      March 22, 2013 at 2:31 pm

      Can you send me this mod I want to use last response and message age as well.

      Thank you

      Jon

  13. Jon's Gravatar Jon
    March 22, 2013 at 1:48 pm

    I am using 1.6 RC5 — When I implament this mod the priority header goes missing but the column is still there. Can you just send me the full new moded tickets.inc.php file?

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