/build/static/layout/Breadcrumb_cap_w.png

How Do I send an email when a new ticket is created with a specific category

I am trying to modify this code so that it will send me an email whenever a new ticket is received with the category called "User Maintenance", but am not having any luck trying to add that to the where clause. Any help would be greatly appreciated.

SELECT


      -- ticket fields

      HD_TICKET.ID, -- $id

      HD_TICKET.ID AS TICKNUM, -- $ticknum

      HD_TICKET.TITLE, -- $title

      DATE_FORMAT(HD_TICKET.CREATED,'%b %d %Y %I:%i:%s %p') AS CREATED, -- $created

      DATE_FORMAT(HD_TICKET.MODIFIED,'%b %d %Y %I:%i:%s %p') AS MODIFIED, -- $modified


      -- change fields


      C.COMMENT, -- $comment

      C.DESCRIPTION, -- $description

      GROUP_CONCAT(CONCAT('----- Change by ', UPDATER.EMAIL,' at ',H.TIMESTAMP,' -----\n',

       H.DESCRIPTION,'\n',H.COMMENT,'\n\nPlease see your ticket at https://MYKACE.com/userui/ticket.php?ID=',H.HD_TICKET_ID,'\n')

       ORDER BY H.ID DESC SEPARATOR '\n') HISTORY, -- $history


      -- about the updater


      UPDATER.USER_NAME AS UPDATER_UNAME, -- $updater_uname

      UPDATER.FULL_NAME AS UPDATER_FNAME, -- $updater_fname

      UPDATER.EMAIL AS UPDATER_EMAIL,     -- $updater_email

      IF(UPDATER.FULL_NAME='',UPDATER.USER_NAME,UPDATER.FULL_NAME) AS UPDATER_CONDITIONAL, -- $updater_conditional


      -- about the owner


      OWNER.USER_NAME AS OWNER_UNAME, -- $owner_uname

      OWNER.FULL_NAME AS OWNER_FNAME, -- $owner_fname

      OWNER.EMAIL AS OWNER_EMAIL,     -- $owner_email

      IFNULL(OWNER.USER_NAME,'Unassigned') OWNER_USER, -- $owner_user


      -- about the submitter


      SUBMITTER.USER_NAME AS SUBMITTER_UNAME, -- $submitter_uname

      SUBMITTER.FULL_NAME AS SUBMITTER_FNAME, -- $submitter_fname

      SUBMITTER.EMAIL AS SUBMITTER_EMAIL,     -- $submitter_email


      -- about priority


      P.NAME AS PRIORITY, -- $priority


      -- about status


      S.NAME AS STATUS,   -- $status


      -- about impact


      I.NAME AS IMPACT,   -- $impact


      -- about category


      CAT.NAME AS CATEGORY, -- $category


      -- other fields


                              CASE WHEN HOUR(NOW()) IN (8,9,10,11,12,13,14,15,16)


                                             AND DAYOFWEEK(NOW()) IN (2,3,4,5)


                              THEN '' -- make this blank  for normal hours


                                             ELSE 'me@me.com'


                              END AS NEWTICKETEMAIL -- $newticketemail


    FROM HD_TICKET


     /* latest change ***/ JOIN HD_TICKET_CHANGE C ON C.HD_TICKET_ID = HD_TICKET.ID


                            AND C.ID=<CHANGE_ID>


     /* complete history*/ JOIN HD_TICKET_CHANGE H ON H.HD_TICKET_ID = HD_TICKET.ID


     /* priority ********/ JOIN HD_PRIORITY P ON P.ID=HD_PRIORITY_ID


     /* status **********/ JOIN HD_STATUS S ON S.ID=HD_STATUS_ID


     /* impact-severity */ JOIN HD_IMPACT I ON I.ID=HD_IMPACT_ID


     /* category ********/ JOIN HD_CATEGORY CAT ON CAT.ID=HD_CATEGORY_ID


     /* owner ***********/ LEFT JOIN USER OWNER ON OWNER.ID = HD_TICKET.OWNER_ID


     /* submitter *******/ LEFT JOIN USER SUBMITTER ON SUBMITTER.ID = HD_TICKET.SUBMITTER_ID


     /* updater *********/ LEFT JOIN USER UPDATER ON UPDATER.ID = C.USER_ID


    WHERE


     C.DESCRIPTION LIKE 'TICKET CREATED%'


      /* this is necessary when using group by functions */


    GROUP BY HD_TICKET.ID


    HAVING 1=1



0 Comments   [ + ] Show comments

Answers (2)

Posted by: Hobbsy 2 years ago
Red Belt
0

Glenn, I’d simplify this, you could build this with the wizard, or alternatively use a a switch field to make sure only one email is sent and use the ID of the category in the WHERE Statement along with the ID for status when it is new

Posted by: Hobbsy 2 years ago
Red Belt
0

Or actually if you want to keep all the code maybe just add to the WHERE statement HD_TICKET.HD_CATEGORY_ID = ( whatever the specific category is you need)



Don't be a Stranger!

Sign up today to participate, stay informed, earn points and establish a reputation for yourself!

Sign up! or login

Share

 
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