Software

Hello everyone, I used ticket rule wizard and managed to create a rule to change ticket status to "Reopened" from "Resolved", and email ticket owner, when users update a resolved ticket.

I customized the sql code to include some customized information, but after I added the high-lighted code below to include submitter info, it came up with errors.

I'm not sure how to fix it. Can anyone please help? Thanks very much. Please see below for sql code.

Select SQL:

select
                       HD_TICKET.ID,
                       HD_TICKET.OWNER_ID as OWNER_ID,
                       HD_TICKET.ID as TICKNUM,
                       HD_TICKET.TITLE as TITLE,
                       HD_STATUS.NAME AS STATUS_NAME,
                       HD_STATUS.STATE as STATE,

                       OWNER.USER_NAME as OWNER_NAME,
                       OWNER.FULL_NAME as OWNER_FULLNAME,
                       Substring_Index(OWNER.FULL_NAME, ',',1) as OWNER_LASTNAME,
                       Substring_Index(OWNER.FULL_NAME, ',',-1) as OWNER_FIRSTNAME,
                       OWNER.EMAIL as OWNER_EMAIL,

                       UPDATER.USER_NAME as UPDATER_UNAME,
                       UPDATER.FULL_NAME AS UPDATER_FNAME,
                   Substring_Index(UPDATER.FULL_NAME, ',',1) as UPDATER_LASTNAME,
                       Substring_Index(UPDATER.FULL_NAME, ',',-1) as UPDATER_FIRSTNAME,
                       UPDATER.EMAIL as UPDATEREMAIL,

                      SUBMITTER.USER_NAME AS SUBMITTER_UNAME,
                      SUBMITTER.FULL_NAME AS SUBMITTER_FNAME,
                      Substring_Index(SUBMITTER.FULL_NAME, ',',1) as SUBMITTER_LASTNAME,
                      Substring_Index(SUBMITTER.FULL_NAME, ',',-1) as SUBMITTER_FIRSTNAME,
                      SUBMITTER.EMAIL AS SUBMITTER_EMAIL,
                      
                  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
             LEFT JOIN USER SUBMITTER ON SUBMITTER.ID = HD_TICKET.SUBMITTER_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 (33)
                   and (UPDATER.ID <> OWNER.ID or OWNER.ID is NULL)
                   and UPDATER.ID > 0

Update SQL:

update HD_TICKET as T, HD_STATUS as STATUS
set T.HD_STATUS_ID = STATUS.ID,
T.RESOLUTION = CONCAT(T.RESOLUTION,'
Reopened'),
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 = 'Reopened' and
T.HD_QUEUE_ID = STATUS.HD_QUEUE_ID and  (T.ID in (<TICKET_IDS>))

Error:

11/30/2018 13:09:23> Starting: 11/30/2018 13:09:23 11/30/2018 13:09:23> Executing Select Query... 11/30/2018 13:09:23> mysqli error: [1064: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'from (HD_TICKET, HD_STATUS) left join HD_TICKET_CHANGE on HD_TICKET' at line 27] in EXECUTE("select HD_TICKET.ID, HD_TICKET.OWNER_ID as OWNER_ID, HD_TICKET.ID as TICKNUM, HD_TICKET.TITLE as TITLE, HD_STATUS.NAME AS STATUS_NAME, HD_STATUS.STATE as STATE, OWNER.USER_NAME as OWNER_NAME, OWNER.FULL_NAME as OWNER_FULLNAME, Substring_Index(OWNER.FULL_NAME, ',',1) as OWNER_LASTNAME, Substring_Index(OWNER.FULL_NAME, ',',-1) as OWNER_FIRSTNAME, OWNER.EMAIL as OWNER_EMAIL, UPDATER.USER_NAME as UPDATER_UNAME, UPDATER.FULL_NAME AS UPDATER_FNAME, Substring_Index(UPDATER.FULL_NAME, ',',1) as UPDATER_LASTNAME, Substring_Index(UPDATER.FULL_NAME, ',',-1) as UPDATER_FIRSTNAME, UPDATER.EMAIL as UPDATEREMAIL, SUBMITTER.USER_NAME AS SUBMITTER_UNAME, SUBMITTER.FULL_NAME AS SUBMITTER_FNAME, Substring_Index(SUBMITTER.FULL_NAME, ',',1) as SUBMITTER_LASTNAME, Substring_Index(SUBMITTER.FULL_NAME, ',',-1) as SUBMITTER_FIRSTNAME, SUBMITTER.EMAIL AS SUBMITTER_EMAIL, from (HD_TICKET, HD_STATUS) left join HD_TICKET_CHANGE on HD_TICKET_CHANGE.ID = 0 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 LEFT JOIN USER SUBMITTER ON SUBMITTER.ID = HD_TICKET.SUBMITTER_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 (33) and (UPDATER.ID <> OWNER.ID or OWNER.ID is NULL) and UPDATER.ID > 0 and (HD_TICKET.ID = 88) ")

Frequency: On Ticket Save