Select SQL:
select distinct(HD_TICKET.ID), U.EMAIL as EMAIL from HD_TICKET
left join HD_TICKET_CHANGE TC on TC.HD_TICKET_ID = HD_TICKET.ID
left join HD_TICKET_CHANGE_FIELD CF on CF.HD_TICKET_CHANGE_ID = TC.ID
left join USER U on U.ID = HD_TICKET.SUBMITTER_ID
where CF.AFTER_VALUE = 'Opened'
and CF.FIELD_CHANGED = 'STATUS_NAME'
and TC.TIMESTAMP > subdate(NOW(), INTERVAL 10 SECOND)
and HD_TICKET.HD_QUEUE_ID = 1
Email each recipient in query results:
Subject: (TICK: $id) was moved
Column containing email addresses: EMAIL
Subject:
The Ticket with ID: $id was moved to the Trouble Ticket team. Please review and address.
http://KBOXAddress/adminui/ticket.php?ID=$id
Comments