1DROP TABLE IF EXISTS `%TABLE_PREFIX%ticket_event`; 2CREATE TABLE `%TABLE_PREFIX%ticket_event` ( 3 `ticket_id` int(11) unsigned NOT NULL default '0', 4 `staff_id` int(11) unsigned NOT NULL, 5 `team_id` int(11) unsigned NOT NULL, 6 `dept_id` int(11) unsigned NOT NULL, 7 `topic_id` int(11) unsigned NOT NULL, 8 `state` enum('created','closed','reopened','assigned','transferred','overdue') NOT NULL, 9 `staff` varchar(255) NOT NULL default 'SYSTEM', 10 `timestamp` datetime NOT NULL, 11 KEY `ticket_state` (`ticket_id`, `state`, `timestamp`), 12 KEY `ticket_stats` (`timestamp`, `state`) 13) DEFAULT CHARSET=utf8; 14 15DROP TABLE IF EXISTS `%TABLE_PREFIX%ticket_history`; 16DROP TABLE IF EXISTS `%TABLE_PREFIX%history`; 17 18-- Transfer ticket statistics from the %ticket table (inaccurate) 19-- REOPENED 20INSERT INTO `%TABLE_PREFIX%ticket_event` 21 (`ticket_id`, `staff_id`, `team_id`, `dept_id`, `topic_id`, 22 `state`, `staff`, `timestamp`) 23 SELECT `ticket_id`, T1.`staff_id`, `team_id`, T1.`dept_id`, `topic_id`, 24 'reopened', T2.`username`, `reopened` 25 FROM `%TABLE_PREFIX%ticket` T1 26 INNER JOIN `%TABLE_PREFIX%staff` T2 27 ON (T1.`staff_id` = T2.`staff_id`) 28 WHERE `status` = 'open' and `reopened` is not null; 29 30-- CLOSED 31INSERT INTO `%TABLE_PREFIX%ticket_event` 32 (`ticket_id`, `staff_id`, `team_id`, `dept_id`, `topic_id`, 33 `state`, `staff`, `timestamp`) 34 SELECT `ticket_id`, T1.`staff_id`, `team_id`, T1.`dept_id`, `topic_id`, 35 'closed', COALESCE(T2.`username`,'unknown'), `closed` 36 FROM `%TABLE_PREFIX%ticket` T1 37 LEFT JOIN `%TABLE_PREFIX%staff` T2 38 ON (T1.`staff_id` = T2.`staff_id`) 39 WHERE `status` = 'closed' and `closed` is not null; 40 41-- OVERDUE 42INSERT INTO `%TABLE_PREFIX%ticket_event` 43 (`ticket_id`, `staff_id`, `team_id`, `dept_id`, `topic_id`, 44 `state`, `staff`, `timestamp`) 45 SELECT `ticket_id`, T1.`staff_id`, `team_id`, T1.`dept_id`, `topic_id`, 46 'overdue', 'SYSTEM', `duedate` 47 FROM `%TABLE_PREFIX%ticket` T1 48 INNER JOIN `%TABLE_PREFIX%staff` T2 49 ON (T1.`staff_id` = T2.`staff_id`) 50 WHERE `status` = 'open' and `isoverdue`; 51 52-- OPENED 53INSERT INTO `%TABLE_PREFIX%ticket_event` 54 (`ticket_id`, `staff_id`, `team_id`, `dept_id`, `topic_id`, 55 `state`, `staff`, `timestamp`) 56 SELECT `ticket_id`, 0, 0, 0, `topic_id`, 57 'created', 'SYSTEM', T1.`created` 58 FROM `%TABLE_PREFIX%ticket` T1; 59 60UPDATE `%TABLE_PREFIX%config` 61 SET `schema_signature`='f8856d56e51c5cc3416389de78b54515'; 62