How can I email the submitter when the ticket status is changed?
I have an HR Queue where I want to email the submitter when the status of the ticket is changed from "Open to "Password Changed....." Below is my SQL and instead of the submitter getting an email only when this event happens, they get emailed every single time any other change is made after the status is changed to "Password Changed..." Example: I change the status to Password Changed, they get an email. If I go back in change a date field for example, the get the same resulting email. I only want this to happen when the description of the last change is like "Open" to "Password Changed/Email Fwd". When I run this in MySQL Workbench, I get the exact results of four open tickets that match this Where statement. Is there something else that I am missing?
SELECT
-- ticket fields
HD_TICKET.CC_LIST, -- $CC_LIST
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
-- 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 http://k1000/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
HD_TICKET.CUSTOM_FIELD_VALUE0 AS TIME_ZONE, -- $time_zone
HD_TICKET.CUSTOM_FIELD_VALUE1 AS COMPANY, -- $company
HD_TICKET.CUSTOM_FIELD_VALUE2 AS COMPANY2, -- $company2
HD_TICKET.CUSTOM_FIELD_VALUE3 AS MOBILE, -- $mobile
DATE_FORMAT(HD_TICKET.CUSTOM_FIELD_VALUE7,'%b %d %Y') AS EMAIL_END, -- $email_end
HD_TICKET.CUSTOM_FIELD_VALUE13 AS ERP, -- $erp
DATE_FORMAT(HD_TICKET.CUSTOM_FIELD_VALUE10,'%b %d %Y') AS TERM_DATE, -- $term_date
HD_TICKET.CUSTOM_FIELD_VALUE11 AS TERM_TIME, -- $term_time
HD_TICKET.CUSTOM_FIELD_VALUE12 AS COMPANY12, -- $company12
HD_TICKET.CUSTOM_FIELD_VALUE14 AS COMPANY14, -- $company14
-- employee fields
EMPLOYEE.FULL_NAME as EMPLOYEE_FNAME, -- $employee_fname
EMPLOYEE.EMAIL AS EMPLOYEE_EMAIL, -- $employee_email
-- manager fields
MANAGER.FULL_NAME as MANAGER_FNAME, -- $manager_fname
MANAGER.EMAIL AS MANAGER_EMAIL, -- $manager_email
-- forward_email fields
FORWARD_EMAIL.FULL_NAME as FORWARD_EMAIL_FNAME, -- $forward_email_fname
FORWARD_EMAIL.EMAIL AS FORWARD_EMAIL_EMAIL, -- $forward_email_email
-- h_drive fields
H_DRIVE.FULL_NAME as H_DRIVE_FNAME, -- $h_drive_fname
H_DRIVE.EMAIL AS H_DRIVE_EMAIL -- $h_drive_email
-- -- example of static distribution list
FROM HD_TICKET
/* latest change ***/ JOIN HD_TICKET_CHANGE C ON C.HD_TICKET_ID = HD_TICKET.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
/* employee ********/ LEFT JOIN USER EMPLOYEE ON EMPLOYEE.ID = HD_TICKET.CUSTOM_FIELD_VALUE4
/* forward_email ***/ LEFT JOIN USER FORWARD_EMAIL ON FORWARD_EMAIL.ID = HD_TICKET.CUSTOM_FIELD_VALUE5
/* h_drive *********/ LEFT JOIN USER H_DRIVE ON H_DRIVE.ID = HD_TICKET.CUSTOM_FIELD_VALUE6
/* manager *********/ LEFT JOIN USER MANAGER ON MANAGER.ID = HD_TICKET.CUSTOM_FIELD_VALUE9
WHERE
C.DESCRIPTION LIKE '%"Open" to "Password Changed/Email Fwd"%'
/* this is necessary when using group by functions */
GROUP BY HD_TICKET.ID
HAVING 1=1
0 Comments
[ + ] Show comments
Answers (1)
Answer Summary:
Please log in to answer
Posted by:
chucksteel
8 years ago
Top Answer
For this join:
/* latest change ***/ JOIN HD_TICKET_CHANGE C ON C.HD_TICKET_ID = HD_TICKET.ID
You should use the change ID of the current change:
/* latest change ***/ JOIN HD_TICKET_CHANGE C ON C.HD_TICKET_ID = HD_TICKET.ID
and C.ID=<CHANGE_ID>
KACE will replace <CHANGE_ID> with the ID of the change at runtime.
Comments:
-
That fixed it. Thanks for your help! - totero21 8 years ago
