Do not send email if Tech assigns ticket to self...?
So I use the below Select SQL to send an email whenever a ticket is assigned to a technician. However, in many instances, they are assigning tickets to themselves. Is there a way to stop the email if a tech is assigning the ticket to themselves?
SELECT
HD_TICKET.ID,
HD_TICKET.ID AS TICKNUM,
HD_TICKET.TITLE,
DATE_FORMAT(HD_TICKET.CREATED,
'%b %d %Y %I:%i:%s %p') AS CREATED,
DATE_FORMAT(HD_TICKET.MODIFIED,
'%b %d %Y %I:%i:%s %p') AS MODIFIED,
C.COMMENT,
C.DESCRIPTION,
GROUP_CONCAT(CONCAT('----- Change by ',
UPDATER.EMAIL,
' at ',
H.TIMESTAMP,
' -----
',
H.DESCRIPTION,
'
',
H.COMMENT,
'
Please see your ticket at http://kbox/userui/ticket.php?ID=',
H.HD_TICKET_ID,
'
')
ORDER BY H.ID DESC
SEPARATOR '
') HISTORY,
UPDATER.USER_NAME AS UPDATER_UNAME,
UPDATER.FULL_NAME AS UPDATER_FNAME,
UPDATER.EMAIL AS UPDATER_EMAIL,
IF(UPDATER.FULL_NAME = '',
UPDATER.USER_NAME,
UPDATER.FULL_NAME) AS UPDATER_CONDITIONAL,
OWNER.USER_NAME AS OWNER_UNAME,
OWNER.FULL_NAME AS OWNER_FNAME,
OWNER.EMAIL AS OWNER_EMAIL,
IFNULL(OWNER.USER_NAME, 'Unassigned') OWNER_USER,
SUBMITTER.USER_NAME AS SUBMITTER_UNAME,
SUBMITTER.FULL_NAME AS SUBMITTER_FNAME,
SUBMITTER.EMAIL AS SUBMITTER_EMAIL,
P.NAME AS PRIORITY,
S.NAME AS STATUS,
I.NAME AS IMPACT,
CAT.NAME AS CATEGORY,
HD_QUEUE.NAME AS QUEUENAME
FROM
HD_TICKET
JOIN
HD_TICKET_CHANGE C ON C.HD_TICKET_ID = HD_TICKET.ID
AND C.ID = <CHANGE_ID>
JOIN
HD_TICKET_CHANGE H ON H.HD_TICKET_ID = HD_TICKET.ID
JOIN
HD_PRIORITY P ON P.ID = HD_PRIORITY_ID
JOIN
HD_STATUS S ON S.ID = HD_STATUS_ID
JOIN
HD_IMPACT I ON I.ID = HD_IMPACT_ID
JOIN
HD_CATEGORY CAT ON CAT.ID = HD_CATEGORY_ID
LEFT JOIN
USER OWNER ON OWNER.ID = HD_TICKET.OWNER_ID
LEFT JOIN
USER SUBMITTER ON SUBMITTER.ID = HD_TICKET.SUBMITTER_ID
LEFT JOIN
USER UPDATER ON UPDATER.ID = C.USER_ID
JOIN
HD_QUEUE ON HD_TICKET.HD_QUEUE_ID = HD_QUEUE.ID
WHERE
(C.DESCRIPTION LIKE '%Changed ticket Owner%')
and OWNER.USER_NAME is not null
GROUP BY OLIST.EMAIL
HAVING 1 = 1
