I wanted the manager to see the number of days the tickets are opened, by days in the ticket table view.

This way he can see if they tickets are being closed in a timely fashion. Our highest priority is same day and our lowest is within 30 days.

Below is a screen shot:

blank

Follow the instructions below.

Code below is as it appears in my code.

1. In include/staff/tickets.inc.php line ~146

Include 'daysopen'=>'daysopen' in the $sortOptions variable.

$sortOptions=array('date'=>'ticket.created','ID'=>'ticketID','pri'=>'priority_urgency','dept'=>'dept_name','staff_name'=>'staff_name','closed'=>'closed', 'ponumber'=>'ponumber','name'=>'name', 'daysopen'=>'daysopen');

2. Line ~170, modify $qselect to include:

CASE WHEN status = "open" THEN DATEDIFF(now(),ticket.created) ELSE DATEDIFF(ticket.closed,ticket.created) END AS daysopen

My code:

$qselect = 'SELECT ticket.ticket_id,lock_id,ticketID,ticket.dept_id,ticket.staff_id,subject,name,ticket.email,dept_name '.

',status,ponumber,ticket.closed,source,isoverdue,ticket.created,pri.*, CASE WHEN status = "open" THEN DATEDIFF(now(),ticket.created) ELSE DATEDIFF(ticket.closed,ticket.created) END AS daysopen, CASE WHEN staff.staff_id IS NULL THEN \'-- Unassigned --\' ELSE Concat(staff.lastname, \', \', staff.firstname) END staff_name ';

3. Line ~348 add:

<th width="92">

<a href="tickets.php?sort=daysopen&order=<?=$negorder?><?=$qstr?>" title="Sort By Days Open <?=$negorder?>">Days Open</a></th>

4. Line ~384 add: Any days over 30, the box is highlighted red.

<td class="nohover" align="center" style="background-color:<? $color="#FF745A"; $diff=$row; if ($diff > 30) print ($color) ?>">

<?php

$diff =$row;

$day ="day";

$days ="days";

if ($diff < 1)

print ($diff . " " . $day);

else

print ($diff . " " . $days);

?>

</td>