osTicket AutoClose

ost_autoclose.sql

INSERT INTO ost_ticket_event
  (ticket_id, staff_id, team_id, dept_id, topic_id, state, staff, annulled, timestamp)
  SELECT
    a.ticket_id, a.staff_id, a.team_id, a.dept_id, a.topic_id, 'closed', 'SYSTEM', '0', NOW()
  FROM
    ost_ticket a
  JOIN
    ost_autoclose_id b
  ON
    a.ticket_id=b.ticket_id;

  


DROP PROCEDURE IF EXISTS ost_autoclose;
DELIMITER //
CREATE PROCEDURE ost_autoclose()
BEGIN
SET @current_time = NOW();
CREATE TEMPORARY TABLE IF NOT EXISTS ost_autoclose_id (ticket_id INT(11) UNSIGNED);
TRUNCATE ost_autoclose_id;

INSERT INTO ost_autoclose_id (ticket_id) 
  SELECT ticket_id FROM ost_ticket
  WHERE status_id = '1'
  AND isanswered = '1'
  AND lastresponse <= DATE_SUB(NOW(), INTERVAL 7 day)
  AND lastmessage < lastresponse;

INSERT INTO ost_ticket_thread
  (pid, ticket_id, staff_id, user_id, thread_type, poster, title, body, format, created, updated)
  SELECT
   '0', ticket_id, '0', '0', 'N', 'SYSTEM', 'Ticket Automatically Resolved ',
   'Ticket automatically resolved due to no user response for 7 days.', 'html', @current_time, '0000-00-00 00:00:00'
  FROM ost_autoclose_id;

INSERT INTO ost_ticket_event
  (ticket_id, staff_id, team_id, dept_id, topic_id, state, staff, annulled, timestamp)
  SELECT
    a.ticket_id, a.staff_id, a.team_id, a.dept_id, a.topic_id, 'closed', 'SYSTEM', '0', @current_time
  FROM
    ost_ticket a
  JOIN
    ost_autoclose_id b
  ON
    a.ticket_id=b.ticket_id;
  
SET SQL_SAFE_UPDATES = 0;
UPDATE ost_ticket a
JOIN ost_autoclose_id b ON
  a.ticket_id=b.ticket_id
SET
  status_id='2',
  closed=@current_time
WHERE 
  status_id='1';
SET SQL_SAFE_UPDATES = 1;

DROP TEMPORARY TABLE IF EXISTS ost_autoclose_id;
END//