I've done some searching on the forums and haven't had much luck finding an answer. From the Staff Control Panel, one can see there open tickets. If you go to Closed, you see ALL closed tickets in the system. Does anyone know a SQL query that will allow me to see ALL closed tickets for a particular user?
something like that?
SELECT * FROM ost_ticket where email = 'emailaddress@ofthat.user' AND status = 'closed'
Best
T.
Thank you thofei for the reply.
While that query did show all the closed tickets associated with a particular email id it seems there is a flaw. It appears that the staff_id field is used to show which staff member is actually assigned to the ticket. Just because some user creates a ticket doesn't mean he/she is the one who is working on it. Furthermore, once a ticket is closed the staff_id field goes to '0' and we effectively loose the important information of who actually worked the ticket.
I suppose there's no way to pull this information out of the database without modifying the php code. Let me know if you think of something.
Thanks,
Aaron
Sorry, but i'm not sure what you want now. Do you want all closed tickets for a user or all closed tickets where a staff member worked on or all closed tickets that were closed by a specific staff member?
Best
T.
I would like to have ALL closed tickets for the user who worked on that ticket ie. not necessarily the person who created the ticket.
I assume we use different wordings, so the misunderstanding. For me a "user" is someone who just submits a ticket and waits for an answer or a solution to a problem. The guy (or girl) who is actually solving the problem is from "staff". Seems you use the term "user" where i use "staff".
Regardless, what you probably want is a modification, that the information about the "closing staff person" is not lost when the ticket is actually closed.
To do that, open /include/class.ticket.php and search for the "function close" (should be around line 420). There you see an sql-statement that contains
... SET status='.db_input('closed').',staff_id=0,isoverdue=0,duedate=NULL,updated=NOW(), ...
Change that to
... SET status='.db_input('closed').',isoverdue=0,duedate=NULL,updated=NOW(), ...
so the staff_id of the last person, that worked on and closed it, doesn't get deleted.
Best
T.
You're the best! Sorry for the confusion on staff vs user. We're just using this among ourselves in a small technology group and therefore haven't rolled out the user portion to end users yet.
Sounds like this modification will do exactly what we need. I'll try to a little later and confirm that it is in fact working. I assume that after this is working that we'll be able to run that same query for "closed" and any particular staff_id to find the closed tickets. Thanks again for your help!
I'm hoping we did not open can of worms on this. It seems that we're now experiencing a new issue. Because we updated the staff_id so that it did not reset upon completed a ticket, the open tickets for a given user does not change. For example, if a staff member closes out a ticket it still shows in the "My Tickets" as an open ticket. It seems that this is happening because we now set an actual number instead of the original "0" value that was being used before. Any ideas on a potential fix?
I think the solution to this is actually already there, just commented out. In include/staff/tickets.inc.php around line 41:
case 'assigned':
//$status='Open'; //
$staffId=$thisuser->getId();
break;
Uncomment $status='Open' and that should do the trick.... been a while since I've dealt with anything in there other than front-end code, though, so I may be miss-remembering how it all works.
ehm, yes, what Kelli said :) ... i did that as well, just forgot that this is needed for the correct display.
Best
T.
Great, that appears to fix the tickets listed in My Tickets. However, the total number if parenthesis does not appear to update. Is there a way to change this too?
I know this is minor but if anyone knows of a way to update the My Tickets count that would be great.
Looks like /scp/tickets.php needs to be modified I'm just not sure what exactly to do here
This solution does not change the ticket count for the user. Is there something we are missing possibly?
