Approver Rule K1000
I am trying to set up a rule that will send an email to the user that needs to approve the request. I have part of it complete but I would like to include the actual request in the approval email.
This is what I have so far.
select HD_TICKET.*,
HD_STATUS.NAME AS STATUS_NAME,
HD_STATUS.ORDINAL as STATUS_ORDINAL,
HD_IMPACT.ORDINAL as IMPACT_ORDINAL,
HD_CATEGORY.ORDINAL as CATEGORY_ORDINAL,
HD_PRIORITY.ORDINAL as PRIORITY_NUMBER,
HD_TICKET.TITLE as ticket_title,
HD_TICKET_CHANGE.COMMENT as COMMENT, /*COMMENTS*/
U2.FULL_NAME as submitter,
STATE,
if((datediff(DUE_DATE, now()) = 0), 2, if((datediff(DUE_DATE, now())<0), 1, 3)) as SORT_OVERDUE_STATUS,
if(unix_timestamp(TIME_OPENED) > 0, TIME_OPENED, 1<<62) as SORT_TIME_OPENED,
if(unix_timestamp(TIME_STALLED) > 0, TIME_STALLED, 1<<62) as SORT_TIME_STALLED,
if(unix_timestamp(TIME_CLOSED) > 0, TIME_CLOSED, 1<<62) as SORT_TIME_CLOSED,
if(unix_timestamp(ESCALATED) > 0, ESCALATED, 1<<62) as SORT_ESCALATED,
if(unix_timestamp(HD_TICKET.CREATED) > 0, HD_TICKET.CREATED, 1<<62) as SORT_TIME_CREATED,
if(unix_timestamp(HD_TICKET.MODIFIED) > 0, HD_TICKET.MODIFIED, 1<<62) as SORT_MODIFIED,
if(unix_timestamp(HD_TICKET.DUE_DATE) > 0, HD_TICKET.DUE_DATE, 1<<62) as SORT_DUE_DATE,
case upper(STATE)
when 'CLOSED' then unix_timestamp(HD_TICKET.TIME_CLOSED) - unix_timestamp(HD_TICKET.TIME_OPENED)
when 'OPENED' then unix_timestamp(NOW()) - unix_timestamp(HD_TICKET.TIME_OPENED)
else unix_timestamp(NOW()) - unix_timestamp(HD_TICKET.CREATED) end as AGE,
if ((LENGTH(U1.FULL_NAME) = 0), U1.USER_NAME, U1.FULL_NAME) as OWNER_NAME,
U1.FULL_NAME as OWNER_FULLNAME,
U1.EMAIL as OWNER_EMAIL,
if (U1.ID is null, 'z', concat('a', if ((LENGTH(U1.FULL_NAME) = 0), U1.USER_NAME, U1.FULL_NAME))) as SORT_OWNER_NAME,
if ((LENGTH(U2.FULL_NAME) = 0), U2.USER_NAME, U2.FULL_NAME) as SUBMITTER_NAME,
U2.FULL_NAME as SUBMITTER_FULLNAME,
U2.EMAIL as SUBMITTER_EMAIL,
if (U2.ID is null, 'z', concat('a', if ((LENGTH(U2.FULL_NAME) = 0), U2.USER_NAME, U2.FULL_NAME))) as SORT_SUBMITTER_NAME,
U3.FULL_NAME as APPROVER_FULLNAME,
U3.EMAIL as APPROVER_EMAIL,
if (U3.ID is null, 'z', concat('a', if ((LENGTH(U3.FULL_NAME) = 0), U3.USER_NAME, U3.FULL_NAME))) as SORT_APPROVER_NAME,
if(APPROVAL='rejected', 'Rejected', if(APPROVAL='info', 'More Info Needed', if(APPROVAL='approved', 'Approved', if(APPROVER_ID>0, 'Pending', '')))) as APPROVAL_STATUS,
Q.NAME as QUEUE_NAME
from (HD_TICKET, HD_PRIORITY, HD_STATUS, HD_IMPACT, HD_CATEGORY, HD_TICKET_CHANGE /*COMMENTS*/)
LEFT JOIN USER U1 on U1.ID = HD_TICKET.OWNER_ID
LEFT JOIN USER U2 on U2.ID = HD_TICKET.SUBMITTER_ID
LEFT JOIN USER U3 on U3.ID = HD_TICKET.APPROVER_ID
LEFT JOIN HD_QUEUE Q on Q.ID = HD_TICKET.HD_QUEUE_ID
LEFT JOIN MACHINE M1 on M1.ID = HD_TICKET.MACHINE_ID
where HD_PRIORITY.ID = HD_PRIORITY_ID
and HD_STATUS.ID = HD_STATUS_ID
and HD_IMPACT.ID = HD_IMPACT_ID
and HD_CATEGORY.ID = HD_CATEGORY_ID
and HD_TICKET.ID = HD_TICKET_ID /*COMMENTS*/
and (( HD_STATUS.NAME = 'Waiting for Approval') and HD_TICKET.HD_QUEUE_ID = 5)
GROUP BY HD_TICKET.ID
Do you Approve of the following request submitted by $submitter.
Ticket Title: $ticket_title
Ticket Comments: $comment
To APPROVE this request, please click here and then click Send an auto-generated email:
<mailto:ithelpdesk@plainscommerce.com?subject=[TICK:$id]$title&body=@approval=Approved%0a@status=Opened%20-%20Work%20in%20Progress%0a@approval_note=[Enter%20Notes%20Here]>
To NOT APPROVE this request, please click here and then click Send on auto-generated email:
<mailto:ithelpdesk@plainscommerce.com?subject=[TICK:$id]&body=@approval=Rejected%0a@status=Closed%20By%20Approver%0a@approval_note=[Enter%20Notes%20Here]>
Thank you,
IT
Thanks!
1 Comment
[ + ] Show comment
-
So, what are you trying to add that isn't working? I'm not sure that the mailto tag will work that way and it will also depend on the email client that the user is using to read their email if it is rendered properly. - chucksteel 7 years ago
-
I would like the summary of the ticket to go to the Approver in the email when we change the approver. The email portion works as I have tested that part already. I just can't figure out how to get the summary in the email yet. - abratton 7 years ago
-
The summary should be available as $summary. - chucksteel 7 years ago
Answers (1)
Please log in to answer
Posted by:
JasonEgg
7 years ago
You'll need to add this somewhere in your SELECT clause:
HD_TICKET.SUMMARY AS SUMMARY,
Then the summary field is available via the variable $summary
Comments:
-
FYI, the wizard always includes HD_TICKET.* in the select clause, so there is no need to select the SUMMARY column on its own. The $summary variable should already be available. - chucksteel 7 years ago
-
Haha I didn't notice the first line! Also I was unaware that the wizard always includes it. Thanks Chuck! - JasonEgg 7 years ago
