Newer
Older
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
/**
* @version v1.7.1
* @signature 740428f9986da6ad85f88ec841b57bfe
*
* - Migrates the email template table to two tables, groups and templates.
* Templates organized in a separate table by group will allow for a more
* extensible model for email templates.
*
* - Add site page table and default templates required by the system to function.
*
*/
DROP TABLE IF EXISTS `%TABLE_PREFIX%email_template_group`;
CREATE TABLE `%TABLE_PREFIX%email_template_group` (
`tpl_id` int(11) NOT NULL auto_increment,
`isactive` tinyint(1) unsigned NOT NULL default '0',
`name` varchar(32) NOT NULL default '',
`notes` text,
`created` datetime NOT NULL,
`updated` timestamp NOT NULL,
PRIMARY KEY (`tpl_id`)
) DEFAULT CHARSET=utf8;
INSERT INTO `%TABLE_PREFIX%email_template_group`
SELECT `tpl_id`, `isactive`, `name`, `notes`, `created`, `updated`
FROM `%TABLE_PREFIX%email_template`;
DROP TABLE IF EXISTS `%TABLE_PREFIX%_email_template`;
CREATE TABLE `%TABLE_PREFIX%_email_template` (
`id` int(11) unsigned NOT NULL auto_increment,
`tpl_id` int(11) unsigned NOT NULL,
`code_name` varchar(32) NOT NULL,
`subject` varchar(255) NOT NULL default '',
`body` text NOT NULL,
`created` datetime NOT NULL,
`updated` datetime NOT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `template_lookup` (`tpl_id`, `code_name`)
) DEFAULT CHARSET=utf8;
INSERT INTO `%TABLE_PREFIX%_email_template`
(`tpl_id`, `code_name`, `subject`, `body`, `created`, `updated`)
SELECT `tpl_id`, 'ticket.autoresp', ticket_autoresp_subj, ticket_autoresp_body, `created`, `updated`
FROM `%TABLE_PREFIX%email_template`;
INSERT INTO `%TABLE_PREFIX%_email_template`
(`tpl_id`, `code_name`, `subject`, `body`, `created`, `updated`)
SELECT `tpl_id`, 'ticket.autoreply', ticket_autoreply_subj, ticket_autoreply_body, `created`, `updated`
FROM `%TABLE_PREFIX%email_template`;
INSERT INTO `%TABLE_PREFIX%_email_template`
(`tpl_id`, `code_name`, `subject`, `body`, `created`, `updated`)
SELECT `tpl_id`, 'message.autoresp', message_autoresp_subj, message_autoresp_body, `created`, `updated`
FROM `%TABLE_PREFIX%email_template`;
INSERT INTO `%TABLE_PREFIX%_email_template`
(`tpl_id`, `code_name`, `subject`, `body`, `created`, `updated`)
SELECT `tpl_id`, 'ticket.notice', ticket_notice_subj, ticket_notice_body, `created`, `updated`
FROM `%TABLE_PREFIX%email_template`;
INSERT INTO `%TABLE_PREFIX%_email_template`
(`tpl_id`, `code_name`, `subject`, `body`, `created`, `updated`)
SELECT `tpl_id`, 'ticket.overlimit', ticket_overlimit_subj, ticket_overlimit_body, `created`, `updated`
FROM `%TABLE_PREFIX%email_template`;
INSERT INTO `%TABLE_PREFIX%_email_template`
(`tpl_id`, `code_name`, `subject`, `body`, `created`, `updated`)
SELECT `tpl_id`, 'ticket.reply', ticket_reply_subj, ticket_reply_body, `created`, `updated`
FROM `%TABLE_PREFIX%email_template`;
INSERT INTO `%TABLE_PREFIX%_email_template`
(`tpl_id`, `code_name`, `subject`, `body`, `created`, `updated`)
SELECT `tpl_id`, 'ticket.alert', ticket_alert_subj, ticket_alert_body, `created`, `updated`
FROM `%TABLE_PREFIX%email_template`;
INSERT INTO `%TABLE_PREFIX%_email_template`
(`tpl_id`, `code_name`, `subject`, `body`, `created`, `updated`)
SELECT `tpl_id`, 'message.alert', message_alert_subj, message_alert_body, `created`, `updated`
FROM `%TABLE_PREFIX%email_template`;
INSERT INTO `%TABLE_PREFIX%_email_template`
(`tpl_id`, `code_name`, `subject`, `body`, `created`, `updated`)
SELECT `tpl_id`, 'note.alert', note_alert_subj, note_alert_body, `created`, `updated`
FROM `%TABLE_PREFIX%email_template`;
INSERT INTO `%TABLE_PREFIX%_email_template`
(`tpl_id`, `code_name`, `subject`, `body`, `created`, `updated`)
SELECT `tpl_id`, 'assigned.alert', assigned_alert_subj, assigned_alert_body, `created`, `updated`
FROM `%TABLE_PREFIX%email_template`;
INSERT INTO `%TABLE_PREFIX%_email_template`
(`tpl_id`, `code_name`, `subject`, `body`, `created`, `updated`)
SELECT `tpl_id`, 'transfer.alert', transfer_alert_subj, transfer_alert_body, `created`, `updated`
FROM `%TABLE_PREFIX%email_template`;
INSERT INTO `%TABLE_PREFIX%_email_template`
(`tpl_id`, `code_name`, `subject`, `body`, `created`, `updated`)
SELECT `tpl_id`, 'ticket.overdue', ticket_overdue_subj, ticket_overdue_body, `created`, `updated`
FROM `%TABLE_PREFIX%email_template`;
DROP TABLE `%TABLE_PREFIX%email_template`;
ALTER TABLE `%TABLE_PREFIX%_email_template`
RENAME TO `%TABLE_PREFIX%email_template`;
-- pages
CREATE TABLE IF NOT EXISTS `%TABLE_PREFIX%page` (
`id` int(10) unsigned NOT NULL auto_increment,
`isactive` tinyint(1) unsigned NOT NULL default '0',
`type` enum('landing','offline','thank-you','other') NOT NULL default 'other',
`name` varchar(255) NOT NULL,
`body` text NOT NULL,
`notes` text,
`created` datetime NOT NULL,
`updated` datetime NOT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `name` (`name`)
) DEFAULT CHARSET=utf8;
INSERT INTO `%TABLE_PREFIX%page` (`isactive`, `type`, `name`, `body`, `notes`, `created`, `updated`) VALUES
(1, 'offline', 'Offline', '<div>\r\n<h1><span style="font-size: medium">Support Ticket System Offline</span></h1>\r\n<p>Thank you for your interest in contacting us.</p>\r\n<p>Our helpdesk is offline at the moment, please check back at a later time.</p>\r\n</div>', 'Default offline page', NOW(), NOW()),
(1, 'thank-you', 'Thank you', '<div>%{ticket.name},<br />\r\n \r\n<p>\r\nThank you for contacting us.</p><p> A support ticket request #%{ticket.number} has been created and a representative will be getting back to you shortly if necessary.</p>\r\n \r\n<p>Support Team </p>\r\n</div>', 'Default "thank you" page displayed after the end-user creates a web ticket.', NOW(), NOW()),
(1, 'landing', 'Landing', '<h1>Welcome to the Support Center</h1>\r\n<p>In order to streamline support requests and better serve you, we utilize a support ticket system. Every support request is assigned a unique ticket number which you can use to track the progress and responses online. For your reference we provide complete archives and history of all your support requests. A valid email address is required to submit a ticket.\r\n</p>\r\n', 'Introduction text on the landing page.', NOW(), NOW());
INSERT INTO `%TABLE_PREFIX%config` (`key`, `value`, `namespace`) VALUES
('landing_page_id', (SELECT `id` FROM `%TABLE_PREFIX%page` WHERE `type` = 'landing'), 'core')
, ('offline_page_id', (SELECT `id` FROM `%TABLE_PREFIX%page` WHERE `type` = 'offline'), 'core')
, ('thank-you_page_id', (SELECT `id` FROM `%TABLE_PREFIX%page` WHERE `type` = 'thank-you'), 'core');
ALTER TABLE `%TABLE_PREFIX%help_topic`
ADD `page_id` INT UNSIGNED NOT NULL DEFAULT '0' AFTER `sla_id` ,
ADD INDEX ( `page_id` );
ALTER TABLE `%TABLE_PREFIX%file`
ADD `ft` CHAR( 1 ) NOT NULL DEFAULT 'T' AFTER `id`,
ADD INDEX ( `ft` );
-- Finished with patch
UPDATE `%TABLE_PREFIX%config`
SET `value` = '740428f9986da6ad85f88ec841b57bfe'
WHERE `key` = 'schema_signature' AND `namespace` = 'core';