Show Assign To and Closed By in Ticket View

ticket_tester

You can see the username of the staff memeber who has been assigned the ticket.

You can see the username of the staff member who has closed the ticket.

You need to change a few things in

osticket_root/include/staff/tickets.inc.php

Remember to take a backup of your original tickets.inc.php file before you start. I've done very little testing. It's working well for me so far. I'll update this post if I find problems.

Let me know how you go.

Attached to this post are two zip files. They have a picture of the final result in each.

I had a lot of trouble trying to attach jpg of gif files to this post. It didn't matter how small I made the pictures they wouldn't upload.

Phase1: Extend the SQL

Step1:

Look for

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

',ticket.status,ticket.source,isoverdue,isanswered,ticket.created,pri.* ,count(attach.attach_id) as attachments ';

Replace with

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

',ticket.status,ticket.source,isoverdue,isanswered,ticket.created,pri.* ,count(attach.attach_id) as attachments ';

Step2:

Look For

//Ok..lets roll...create the actual query

//ADD attachment,priorities and lock crap

$qselect.=' ,count(attach.attach_id) as attachments, IF(ticket.reopened is NULL,ticket.created,ticket.reopened) as effective_date';

Replace with

//Ok..lets roll...create the actual query

//ADD attachment,priorities and lock crap

$qselect.=' ,count(attach.attach_id) as attachments, IF(ticket.reopened is NULL,ticket.created,ticket.reopened) as effective_date';

//Does Assigned To stuff

$qselect.=' ,IF(staff_table.username is NULL,"unassigned",staff_table.username) as username';

//Does Closed By stuff

$qselect.=' ,IF(response.staff_name is NULL,"Closed. No reply",response.staff_name) as closed_by';

Step3:

Look For

$qfrom.=' LEFT JOIN '.TICKET_PRIORITY_TABLE.' pri ON ticket.priority_id=pri.priority_id '.

' LEFT JOIN '.TICKET_LOCK_TABLE.' tlock ON ticket.ticket_id=tlock.ticket_id AND tlock.expire>NOW() '.

' LEFT JOIN '.TICKET_ATTACHMENT_TABLE.' attach ON ticket.ticket_id=attach.ticket_id ';

Replace With

$qfrom.=' LEFT JOIN '.TICKET_PRIORITY_TABLE.' pri ON ticket.priority_id=pri.priority_id '.

' LEFT JOIN '.TICKET_LOCK_TABLE.' tlock ON ticket.ticket_id=tlock.ticket_id AND tlock.expire>NOW() '.

' LEFT JOIN '.TICKET_ATTACHMENT_TABLE.' attach ON ticket.ticket_id=attach.ticket_id '.

' LEFT JOIN '.STAFF_TABLE.' staff_table ON ticket.staff_id=staff_table.staff_id'.

' LEFT JOIN '.TICKET_RESPONSE_TABLE.' response ON (ticket.ticket_id=response.ticket_id )';

Phase 2: Modify the html table data

Step1:

Look for

<th width="180" >From</th>

Replace with

<th width="180" >From</th>

<?php if($status!='closed'){?>

<th width="70">Assigned To</th>

<?php }else{ ?>

<th width="70">Closed By</th>

<?php } ?>

Step2:

Look For

<td nowrap><?=Format:($row,22,strpos($row,'@'))?> </td>

Replace with

<td nowrap><?=Format:($row,22,strpos($row,'@'))?> </td>

<?php if($status!='closed'){?>

<td nowrap><?=Format:($row,22)?> </td>

<?php }else{ ?>

<td nowrap><?=Format:($row,22)?> </td>

<?php } ?>

I had a lot of trouble trying to upload jpg or gif files. So I zipped the jpg instead. The final views are in the attached zip files.

I haven't done much testing. Working well for me so far.

Remember to take a backup of your original tickets.inc.php file before you start.

[open_ticket_assignedto.zip](https://forum.osticket.com/assets/files/migrated/7/13170fb240193f7d7d03bca6b3fd73e.zip)

[closed_ticket_closedby.zip](https://forum.osticket.com/assets/files/migrated/4/e8fdfc3a4bd881de3d3b8c5c478b962.zip)

thewronggrape

Works great except...

Hello,

Thank you for taking the time to share this and posting very clear instructions! It works great but my only issue is that it seems to break the Search function. Every time I do a search, I get 0 results even though I know there are results and I get a DB Error in my log emailed to me.

Have you encountered this and do you have any solutions? Thanks in advance - I truly appreciate it.

Here's the error if it helps:

Not unique table/alias: 'response'

vsi

Same error here - but love the MOD

I found the MOD very useful, but admin email recieves the excact same error as 'thewronggrape' when people query a search.

Did you find a solution to make the search function work again?

Thanx in advance

Hello,
Thank you for taking the time to share this and posting very clear instructions! It works great but my only issue is that it seems to break the Search function. Every time I do a search, I get 0 results even though I know there are results and I get a DB Error in my log emailed to me.
Have you encountered this and do you have any solutions? Thanks in advance - I truly appreciate it.
Here's the error if it helps:
Not unique table/alias: 'response'

CobbCarl

This is a great MOD...I have been looking for something to help me in editing the tickets.inc.php page to display the "Help Topic" instead of the "Subject" of the tickets. I have removed the ability to input a "Subject" on my INPUT form for users, therefore that field in the DB is always blank. My "Help Topics" are very definitive and I only want users to submit things by 'Help Topic" and what the "Message Body" has in it. I was able to remove 'Subject' from all applicable areas of the site, only problem is I can't figure out how to add "Help Topic" to the table in tickets.inc.php in the include\staff area. Do you have any suggestions, help, end all be all's for this?

Much appreciated,

Carl

opsidao

I think I solved the DB Error #1066

Hi all,

I just modified the provided MOD to avoid the DB error. I'm not even sure if the change makes sense, but the search works again and no error appears anywhere.

The change is in the $qfrom statement, and it involves changing the provided MODedd code from:

$qfrom.=' LEFT JOIN '.TICKET_PRIORITY_TABLE.' pri ON ticket.priority_id=pri.priority_id '.

' LEFT JOIN '.TICKET_LOCK_TABLE.' tlock ON ticket.ticket_id=tlock.ticket_id AND tlock.expire>NOW() '.

' LEFT JOIN '.TICKET_ATTACHMENT_TABLE.' attach ON ticket.ticket_id=attach.ticket_id '.

' LEFT JOIN '.STAFF_TABLE.' staff_table ON ticket.staff_id=staff_table.staff_id'.

' LEFT JOIN '.TICKET_RESPONSE_TABLE.' response ON (ticket.ticket_id=response.ticket_id )';

to something like:

$qfrom.=' LEFT JOIN '.TICKET_PRIORITY_TABLE.' pri ON ticket.priority_id=pri.priority_id '.

' LEFT JOIN '.TICKET_LOCK_TABLE.' tlock ON ticket.ticket_id=tlock.ticket_id AND tlock.expire>NOW() '.

' LEFT JOIN '.TICKET_ATTACHMENT_TABLE.' attach ON ticket.ticket_id=attach.ticket_id '.

' LEFT JOIN '.STAFF_TABLE.' staff_table ON ticket.staff_id=staff_table.staff_id';

if(!$search) {

$qfrom.=' LEFT JOIN '.TICKET_RESPONSE_TABLE.' response ON (ticket.ticket_id=response.ticket_id )';

}

Could any core developer comment on this change and it's implications?

Thank you for reading :).

UPDATE: Just found that another change is needed because with the previous changes the search by mail was failing too with a #1054 error. The new change is in the "Does Closed By stuff" part.

In the place that says:

//Does Closed By stuff

$qselect.=' ,IF(response.staff_name is NULL,"Cerrado. Sin respuesta",response.staff_name) as

}

I changed it to:

//Does Closed By stuff

if(!$search) {

$qselect.=' ,IF(response.staff_name is NULL,"Cerrado. Sin respuesta",response.staff_name) as closed_by';

}

Just my 0.02€

Vortex

I have the same names in "From" and "Closed by"" columns.

What did I wrong?

intellicom

DB Error #1054

I get the follow error when working adding code

Unknown column 'staff_table.username' in 'field list'

anavmon

Hello!

I have a little problem with this line in SELECT

$qselect.=' ,IF(response.staff_name is NULL,"Cerrado. Sin respuesta",response.staff_name) as closed_by';

It returns the name of the staff who answered first in ticket thread, but not the name of whose closed it.

Any idea to fix it?

Thanks

Muthu Kumar

What is the Solution of this thread?

I saw all the thread related to this topic... But what is the solution of this? any body know please reply..

anishpv

It returns the name of the staff who answered first in ticket thread, but not the nam

It returns the name of the staff who answered first in ticket thread, but not the name of whose closed it.

anishpv

I have the same names in "From" and "Closed by"" columns.
What did I wrong?

replace the Cerrado. Sin respuesta to closed. without responce

anavmon

Hello!
I have a little problem with this line in SELECT
$qselect.=' ,IF(response.staff_name is NULL,"Cerrado. Sin respuesta",response.staff_name) as closed_by';
It returns the name of the staff who answered first in ticket thread, but not the name of whose closed it.
Any idea to fix it?
Thanks

Any idea?