Hey everybody. I've modified the OS Ticketing system quite a bit to work as an internal only system for an inbound customer service center. When tickets come in, they get assigned to a variety of departments during their life cycle and as such we needed a new way to sort tickets based on when the last action had occurred on the ticket, as opposed to when it was created.

I originally looked at the "lastresponse" and "lastmessage" fields, but found that we could not use these as the "POST REPLY" function has been stripped out of our system and all communication is done via Internal Notes. Unfortunately, the internal notes did not update either "lastresponse", "lastmessage" or the "updated" fields in the ost_ticket sql table.

Below shows you how you can update that field when an internal note is posted, and then list and sort by the "updated" field on your tickets pane.

STEP 1

Open \include\class.ticket.php

Find:

function onNote(){

Replace with:

function onNote(){

db_query('UPDATE '.TICKET_TABLE.' SET updated=NOW() WHERE ticket_id='.db_input($this->getId()));

Next, find:

function postNote($title,$note,$alert=true,$poster='') {

global $thisuser,$cfg;

$sql= 'INSERT INTO '.TICKET_NOTE_TABLE.' SET created=NOW() '.

',ticket_id='.db_input($this->getId()).

',title='.db_input(Format:($title)).

',note='.db_input(Format:($note)).

',staff_id='.db_input($thisuser?$thisuser->getId()).

',source='.db_input(($poster || !$thisuser)?$poster:$thisuser->getName());

//echo $sql;

Replace with:

function postNote($title,$note,$alert=true,$poster='') {

global $thisuser,$cfg;

$sql= 'INSERT INTO '.TICKET_NOTE_TABLE.' SET created=NOW() '.

',ticket_id='.db_input($this->getId()).

',title='.db_input(Format:($title)).

',note='.db_input(Format:($note)).

',staff_id='.db_input($thisuser?$thisuser->getId()).

',source='.db_input(($poster || !$thisuser)?$poster:$thisuser->getName());

$this->onNote();

//echo $sql;

STEP 2

Open \include\staff\tickets.inc.php

Find:

//I admit this crap sucks...but who cares??

$sortOptions=array('date'=>'ticket.created','ID'=>'ticketID','pri'=>'priority_urgency','dept'=>'dept_name','subject'=>'subject','from'=>'name');

Replace with:

//I admit this crap sucks...but who cares??

$sortOptions=array('date'=>'ticket.created','ID'=>'ticketID','pri'=>'priority_urgency','dept'=>'dept_name','subject'=>'subject','from'=>'name','updated'=>'updated');

Find:

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

',status,source,isoverdue,ticket.created,pri.* ';

Replace with:

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

',status,source,isoverdue,ticket.created,pri.* ';

Find:

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

Below this line Add:

<th width="70"><a href="tickets.php?sort=updated&order=<?=$negorder?><?=$qstr?>" title="Sort By Updated <?=$negorder?>">Last Action</a></th>

Find:

<td align="center" nowrap><?=Format:($row)?></td>

Below this line Add:

<td align="center"nowrap><?=Format:($row)?></td>

Once this is complete it should be working. Keep in mind, this will only effect notes posted from the time you create this change, it can not go back and update all your previous tickets to reflect the last date an internal note was posted.

The updated field is also changed when a ticket is reopened, closed, reassigned, priority changes and becomes overdue. This change was needed only because internal notes did not cause an update to the "updated" field.

blank

Enjoy! Remember to back up your code before attempting!