/build/static/layout/Breadcrumb_cap_w.png
02/26/2019 86 views

Hello,

I am trying to automatically update a ticket status to "Pending Response" when the ticket owner adds a comment to the ticket and saves it. I have created the following rule select and update SQL but I would like to check/get a list of the STATUS.ID's in the system since I have added custom ones. I cannot seem to find good documentation on the SQL Tables or how to directly query / access the database with SQL Manager, if that is possible.

SELECT STATEMENT:

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 HD_STATUS.ID in (10,32)


                   and (UPDATER.ID = OWNER.ID)


                   and UPDATER.ID > 0

UPDATE STATEMENT:

 update HD_TICKET as T, HD_STATUS as STATUS


set T.HD_STATUS_ID = STATUS.ID,


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 = 'Awaiting Response' and


T.HD_QUEUE_ID = STATUS.HD_QUEUE_ID and  (T.ID in (<TICKET_IDS>))

0 Comments   [ + ] Show comments

Comments


There are no answers at this time