Stop tickets closed more than X days ago from being re-opened
Hi, here's one for all you SQL experts out there.
I would like to test the idea of stopping tickets that were closed more than say 3 days ago, from being able to be re-opened. The idea being that a user couldn't just find an old helpdesk email and reply to it as a way of asking a new questions etc.
I got the theory of what I want OK, but I'm just not good enough at this SQL business to implement it myself:
On ticket save:
IF time closed exists but IS NOT within last 3 days AND status != "Closed - Resolved", change status to "Closed - Resolved" and email TICKET_OWNER and TICKET_SUBMITTER:
"Hello, an attempt was made to re-open ticket $ticknum however the original resolution date is more than 3 days ago. Tickets that were closed more than 3 days ago cannot be re-opened. If you attempted to re-open this ticket or replied to an email regarding this ticket, please log a new ticket with the helpdesk." (wording to be confirmed of course).
-Will this cause the ticket closed email to be re-sent? In my experience if a rule changes a ticket status, it doesn't trigger other rules based on status change.
-What select queries do I need like HD_TICKET.ID AS TICKNUM etc including being able to use OWNER_EMAIL and SUBMITTER_EMAIL in the email address field.
-Need to make sure this doesn't cause any email loops