Howdy, Stranger!

It looks like you're new here. If you want to get involved, click one of these buttons!

In this Discussion

osTicket v1.10 (stable) and Maintenance Release v1.9.15 are now available! Go get it now

[MOD] Sort by Last Internal Note or Action in Tickets List

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::striptags($title)).
',note='.db_input(Format::striptags($note)).
',staff_id='.db_input($thisuser?$thisuser->getId():0).
',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::striptags($title)).
',note='.db_input(Format::striptags($note)).
',staff_id='.db_input($thisuser?$thisuser->getId():0).
',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::db_date($row['created'])?></td>


Below this line Add:
<td align=\"center\"nowrap><?=Format::db_date($row['updated'])?></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.

image

Enjoy! Remember to back up your code before attempting!

Comments

  • [MOD] Sort by Last Internal Note or Action in Tickets List

    Hi Daniel,
    I am using 1.6ST. Can you write the MOD which is will add the "last Action" field only?
    Thank you,
    Cuong
  • cpham55;14771 said:
    Hi Daniel,
    I am using 1.6ST. Can you write the MOD which is will add the "last Action" field only?
    Thank you,
    Cuong
    I'm not quite sure what you mean, I'm sorry. Currently it does add the Last Action field only. The other changes are needed only to make it function with Internal Notes, System Messages, etc. This is for 1.6ST.

    Please clarify and I'll see how I can help.
  • Thank you. I changed the field "created" and "updated" up and down now it work?
    Some how my screen look diff.
    Like: Ticket---Last Action---Date---Department

    <?=Format::db_date($row['updated'])?>
    <?=Format::db_date($row['created'])?>
  • cpham55;14843 said:
    Thank you. I changed the field "created" and "updated" up and down now it work?
    Some how my screen look diff.
    Like: Ticket---Last Action---Date---Department

    <?=Format::db_date($row['updated'])?>
    <?=Format::db_date($row['created'])?>
    If you swap the order on those two it should come out to be the same as mine:

    <?=Format::db_date($row['created'])?>
    <?=Format::db_date($row['updated'])?>

    Is the column working as expected?
  • It is working for few days now it is showed the wrong date as" 11/29/1999 11:00 pm" .
    The only thing I changed is turn on the POP3.
  • cpham55;14895 said:
    It is working for few days now it is showed the wrong date as" 11/29/1999 11:00 pm" .
    The only thing I changed is turn on the POP3.
    This happen because when new ticket is created the "updated" field isn't updated with creation date.

    You need change \include\class.ticket.php

    search for:
    [HTML] $sql= 'INSERT INTO '.TICKET_TABLE.' SET created=NOW() '.
    ',ticketID='.db_input($extId).
    ',dept_id='.db_input($deptId).
    ',topic_id='.db_input($topicId).
    ',priority_id='.db_input($priorityId).
    ',email='.db_input($var['email']).
    ',name='.db_input(Format::striptags($var['name'])).
    ',subject='.db_input(Format::striptags($var['subject'])).
    ',helptopic='.db_input(Format::striptags($topicDesc)).
    ',phone="'.db_input($var['phone'],false).'"'.
    ',phone_ext='.db_input($var['phone_ext']?$var['phone_ext']:'').
    ',ip_address='.db_input($ipaddress).
    ',username='.db_input($var['username']).
    ',membership='.db_input($var['membership']).
    ',source='.db_input($source);[/HTML]

    replace with:

     $sql=   'INSERT INTO '.TICKET_TABLE.' SET created=NOW() '.
    ',updated=NOW() '.
    ',ticketID='.db_input($extId).
    ',dept_id='.db_input($deptId).
    ',topic_id='.db_input($topicId).
    ',priority_id='.db_input($priorityId).
    ',email='.db_input($var['email']).
    ',name='.db_input(Format::striptags($var['name'])).
    ',subject='.db_input(Format::striptags($var['subject'])).
    ',helptopic='.db_input(Format::striptags($topicDesc)).
    ',phone=\"'.db_input($var['phone'],false).'\"'.
    ',phone_ext='.db_input($var['phone_ext']?$var['phone_ext']:'').
    ',ip_address='.db_input($ipaddress).
    ',username='.db_input($var['username']).
    ',membership='.db_input($var['membership']).
    ',source='.db_input($source);
Sign In or Register to comment.