Custom Ticket Rule for Changing status
I am new to SQL. So, this is probably not a difficult ticket rule, but I don't know how to do it. I want a ticket rule for changing status to "waiting to respond" when ticket owner adds a comment.
Answers (1)
You could try this modification of the canned CustomerResponded ticket rule - I left some of the extraneous columns in, just to make comparison to the original easier. If you want to just test the Select statement, change the <CHANGE_ID> to an actual ticket number (i.e. left join HD_TICKET_CHANGE on HD_TICKET_CHANGE.ID = 4321).
John
___________________________
select distinct HD_TICKET.ID,
HD_TICKET.OWNER_ID as OWNER_ID,
HD_TICKET.ID as TICKNUM,
HD_TICKET.TITLE,
HD_STATUS.NAME AS STATUS_NAME,
HD_STATUS.STATE as STATE,
OWNER.USER_NAME as OWNER_NAME,
OWNER.FULL_NAME as OWNER_FULLNAME,
OWNER.EMAIL as OWNER_EMAIL,
UPDATER.USER_NAME as UPDATERNAME,
UPDATER.EMAIL as UPDATEREMAIL
from (HD_TICKET, HD_STATUS)
left join HD_TICKET_CHANGE on HD_TICKET_CHANGE.ID = <CHANGE_ID>
left join HD_TICKET_CHANGE_FIELD on HD_TICKET_CHANGE_FIELD.HD_TICKET_CHANGE_ID = HD_TICKET_CHANGE.ID
left join USER OWNER on OWNER.ID = HD_TICKET.OWNER_ID
left join USER UPDATER on UPDATER.ID = HD_TICKET_CHANGE.USER_ID
where HD_STATUS.ID = HD_TICKET.HD_STATUS_ID
and HD_TICKET_CHANGE.HD_TICKET_ID= HD_TICKET.ID
and HD_TICKET_CHANGE_FIELD.FIELD_CHANGED !='SATISFACTION_RATING'
and HD_TICKET_CHANGE_FIELD.FIELD_CHANGED !='SATISFACTION_COMMENT'
and UPDATER.ID = OWNER.ID
update HD_TICKET as T, HD_STATUS as STATUS
set T.HD_STATUS_ID = STATUS.ID,
T.RESOLUTION = CONCAT(T.RESOLUTION,'waiting to respond'),
T.TIME_OPENED = IF(STATUS.STATE = 'opened', NOW(), T.TIME_OPENED),
T.TIME_CLOSED = IF(STATUS.STATE = 'closed', NOW(), T.TIME_CLOSED),
T.TIME_STALLED = IF(STATUS.STATE = 'stalled', NOW(), T.TIME_STALLED),
T.SATISFACTION_RATING = IF(STATUS.STATE = 'closed', NULL, T.SATISFACTION_RATING),
T.SATISFACTION_COMMENT = IF(STATUS.STATE = 'closed', NULL, T.SATISFACTION_COMMENT)
where STATUS.NAME = 'waiting to respond' and
T.HD_QUEUE_ID = STATUS.HD_QUEUE_ID and (T.ID in (<TICKET_IDS>))
Comments:
-
Hi, I have tried this, however it seems to work fine, but if you need to change the status manually for any reason you cannot until the submitter responds.
Any Help? - iaciofanog 11 years ago-
This is expected behavior, as the ticket rule forces the status change which will remain in place until the submitter replies (and keep changing it back if it gets changed).
If you need to change the status on a ticket, I'd suggest (temporarily) disabling the ticket rule to make the status change. Just keep in mind that until the submitter responds, the status will keep getting flipped back to "waiting to respond" if this ticket rule is enabled - that is what this rule does, so if that is undesired behavior then I wouldn't use this ticket rule in your environment.
John - jverbosk 11 years ago-
My work around for this is sloppy but works for techs:
and (UPDATER.ID <> OWNER.ID or OWNER.ID is NULL)
and UPDATER.ID > 0
and UPDATER.ID <> '0000' */ Tech ID goes here */
Basically all of the techs ID's are spanned down in the AND UPDATER.ID <> ' ' section that way anytime a tech wants to change the status, the ticket won't recognize them and the ticket rule won't fire, thus they can change the ticket.
With this in place (and I have " and UPDATER.ID" multipled 10 times, unfortunately) I don't have to turn the rule on/off and techs can work tickets normally, as we want the ticket to auto change status when normal USERS change status, not us. - Wildwolfay 11 years ago -
Wildwolfay,
For some reason I couldn't reply directly to your comment, so I'll do it here - nice workaround! ^_^
John - jverbosk 11 years ago
