K1000 Ticket created emails not sent to staff when comments are present
We have an email notification setup where all category staff members are notified when a new ticket has been created in their queue. The rule works perfectly until a comment is added in the comment field – no email is sent when this happens. We are at a loss at what could cause this glitch.
Answers (1)
This is the query that finally worked.
select
HD_TICKET.ID,
HD_TICKET.ID ticknum,
HD_TICKET.TITLE,
SUBMITTER.FULL_NAME submitter_fname,
SUBMITTER.EMAIL submitter_email,
CAT.NAME category,
IMPACT.NAME impact,
PRIORITY.NAME priority,
OWNER.EMAIL EMAILCOLUMN
from
HD_TICKET
join HD_TICKET_CHANGE C on HD_TICKET.ID = C.HD_TICKET_ID
and C.ID = <CHANGE_ID>
join USER SUBMITTER on HD_TICKET.SUBMITTER_ID = SUBMITTER.ID
join USER OWNER on HD_TICKET.OWNER_ID = OWNER.ID
join HD_CATEGORY CAT on HD_TICKET.HD_CATEGORY_ID = CAT.ID
join HD_IMPACT IMPACT on HD_TICKET.HD_IMPACT_ID = IMPACT.ID
join HD_PRIORITY PRIORITY on HD_TICKET.HD_PRIORITY_ID = PRIORITY.ID where
C.DESCRIPTION like '%Ticket Created%'
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.COMMENT_LOC as CMT_L10N, -- $cmt_l10n
C.DESCRIPTION, -- $description
C.LOCALIZED_DESCRIPTION as DESC_L10N, -- $desc_110n
group_concat( concat('----- Change by ', UPDATER.EMAIL,' at ',H.TIMESTAMP,' -----\n',
H.DESCRIPTION,'\n',H.COMMENT,'\n\nPlease see your ticket at http://kbox/userui/ticket.php?ID=',H.HD_TICKET_ID,'\n')
order by H.ID desc separator '\n') as HISTORY, -- $history
group_concat( concat('----- Change by ', UPDATER.EMAIL,' at ',H.TIMESTAMP,' -----\n',
H.LOCALIZED_DESCRIPTION,'\n',H.COMMENT_LOC,'\n\nPlease see your ticket at http://kbox/userui/ticket.php?ID=',H.HD_TICKET_ID,'\n')
order by H.ID desc separator '\n') as HIST_l10n, -- $hist_l10n
-- 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
-- -- example of static distribution list
OWNER.EMAIL 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> and <TICKET_JOIN>
/* initial change **/ left join HD_TICKET_CHANGE_FIELD F ON
F.HD_TICKET_CHANGE_ID = C.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
F.ID is null /* initial entry for ticket */
/* this is necessary when using group by functions */
group by HD_TICKET.ID
The email body is:
$submitter_fname has submitted a scenario.
The submission was:
From: $submitter_fname ($submitter_email)
Loan Type: $category
Loan Purpose: $impact
Priority: $priority
$comment - cgwillis 10 years ago
join HD_TICKET_CHANGE C on C.HD_TICKET_ID = HD_TICKET.ID
and C.ID=<CHANGE_ID> - chucksteel 10 years ago
Log without comments:
10/22/2013 11:52:36> Starting: 10/22/2013 11:52:36
10/22/2013 11:52:36> Executing Select Query...
10/22/2013 11:52:36> selected 1 rows
10/22/2013 11:52:36> Sending ticket notifications...
10/22/2013 11:52:36> sent mail to 1 of 1
10/22/2013 11:52:36> Ending: 10/22/2013 11:52:36
Log with comments:
10/22/2013 11:56:22> Starting: 10/22/2013 11:56:22
10/22/2013 11:56:22> Executing Select Query...
10/22/2013 11:56:22> selected 0 rows - cgwillis 10 years ago