/build/static/layout/Breadcrumb_cap_w.png

Scripting Question


Do not send email if Tech assigns ticket to self...?

01/09/2019 603 views

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

0 Comments   [ + ] Show comments

Comments


All Answers

0

In the where clause add:

AND UPDATER.ID != OWNER.ID


Answered 01/10/2019 by: chucksteel
Red Belt

 
This website uses cookies. By continuing to use this site and/or clicking the "Accept" button you are providing consent Quest Software and its affiliates do NOT sell the Personal Data you provide to us either when you register on our websites or when you do business with us. For more information about our Privacy Policy and our data protection efforts, please visit GDPR-HQ