osTicket> Assigned To AND Age of Tickets

"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

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

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

$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)

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

And again for the Age column

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

<!--<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:

$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

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

to:

$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

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

To:

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