1/**
2 * @signature 2d590ffab4a6a928f08cc97aace1399e
3 * @version v1.10.0
4 * @title Make fields disable-able per help topic
5 *
6 * This patch adds the ability to associate more than one extra form with a
7 * help topic, allows specifying the sort order of each form, including the
8 * main ticket details forms, and also allows disabling any of the fields on
9 * any of the associated forms, including the issue details field.
10 *
11 * This patch migrates the columnar layout of the %filter table into a new
12 * %filter_action table. The cleanup portion of the script will drop the old
13 * columns from the %filter table.
14 */
15
16DROP TABLE IF EXISTS `%TABLE_PREFIX%filter_action`;
17CREATE TABLE `%TABLE_PREFIX%filter_action` (
18  `id` int(11) unsigned NOT NULL auto_increment,
19  `filter_id` int(10) unsigned NOT NULL,
20  `sort` int(10) unsigned NOT NULL default 0,
21  `type` varchar(24) NOT NULL,
22  `configuration` text,
23  `updated` datetime NOT NULL,
24  PRIMARY KEY (`id`),
25  KEY `filter_id` (`filter_id`)
26) DEFAULT CHARSET=utf8;
27
28INSERT INTO `%TABLE_PREFIX%filter_action`
29    (`filter_id`, `type`, `configuration`, `updated`)
30    SELECT `id`, 'reject', '', `updated`
31    FROM `%TABLE_PREFIX%filter`
32    WHERE `reject_ticket` != 0;
33
34INSERT INTO `%TABLE_PREFIX%filter_action`
35    (`filter_id`, `type`, `configuration`, `updated`)
36    SELECT `id`, 'replyto', '{"enable":true}', `updated`
37    FROM `%TABLE_PREFIX%filter`
38    WHERE `use_replyto_email` != 0;
39
40INSERT INTO `%TABLE_PREFIX%filter_action`
41    (`filter_id`, `type`, `configuration`, `updated`)
42    SELECT `id`, 'noresp', '{"enable":true}', `updated`
43    FROM `%TABLE_PREFIX%filter`
44    WHERE `disable_autoresponder` != 0;
45
46INSERT INTO `%TABLE_PREFIX%filter_action`
47    (`filter_id`, `type`, `configuration`, `updated`)
48    SELECT `id`, 'canned', CONCAT('{"canned_id":',`canned_response_id`,'}'), `updated`
49    FROM `%TABLE_PREFIX%filter`
50    WHERE `canned_response_id` != 0;
51
52INSERT INTO `%TABLE_PREFIX%filter_action`
53    (`filter_id`, `type`, `configuration`, `updated`)
54    SELECT `id`, 'dept', CONCAT('{"dept_id":',`dept_id`,'}'), `updated`
55    FROM `%TABLE_PREFIX%filter`
56    WHERE `dept_id` != 0;
57
58INSERT INTO `%TABLE_PREFIX%filter_action`
59    (`filter_id`, `type`, `configuration`, `updated`)
60    SELECT `id`, 'pri', CONCAT('{"priority_id":',`priority_id`,'}'), `updated`
61    FROM `%TABLE_PREFIX%filter`
62    WHERE `priority_id` != 0;
63
64INSERT INTO `%TABLE_PREFIX%filter_action`
65    (`filter_id`, `type`, `configuration`, `updated`)
66    SELECT `id`, 'sla', CONCAT('{"sla_id":',`sla_id`,'}'), `updated`
67    FROM `%TABLE_PREFIX%filter`
68    WHERE `sla_id` != 0;
69
70INSERT INTO `%TABLE_PREFIX%filter_action`
71    (`filter_id`, `type`, `configuration`, `updated`)
72    SELECT `id`, 'team', CONCAT('{"team_id":',`team_id`,'}'), `updated`
73    FROM `%TABLE_PREFIX%filter`
74    WHERE `team_id` != 0;
75
76INSERT INTO `%TABLE_PREFIX%filter_action`
77    (`filter_id`, `type`, `configuration`, `updated`)
78    SELECT `id`, 'agent', CONCAT('{"staff_id":',`staff_id`,'}'), `updated`
79    FROM `%TABLE_PREFIX%filter`
80    WHERE `staff_id` != 0;
81
82INSERT INTO `%TABLE_PREFIX%filter_action`
83    (`filter_id`, `type`, `configuration`, `updated`)
84    SELECT `id`, 'topic', CONCAT('{"topic_id":',`topic_id`,'}'), `updated`
85    FROM `%TABLE_PREFIX%filter`
86    WHERE `topic_id` != 0;
87
88INSERT INTO `%TABLE_PREFIX%filter_action`
89    (`filter_id`, `type`, `configuration`, `updated`)
90    SELECT `id`, 'status', CONCAT('{"status_id":',`status_id`,'}'), `updated`
91    FROM `%TABLE_PREFIX%filter`
92    WHERE `status_id` != 0;
93
94ALTER TABLE `%TABLE_PREFIX%form`
95    ADD `pid` int(10) unsigned DEFAULT NULL AFTER `id`,
96    ADD `name` varchar(64) NOT NULL DEFAULT '' AFTER `instructions`;
97
98ALTER TABLE `%TABLE_PREFIX%form_entry`
99    ADD `extra` text AFTER `sort`;
100
101CREATE TABLE `%TABLE_PREFIX%help_topic_form` (
102  `id` int(11) unsigned NOT NULL auto_increment,
103  `topic_id` int(11) unsigned NOT NULL default 0,
104  `form_id` int(10) unsigned NOT NULL default 0,
105  `sort` int(10) unsigned NOT NULL default 1,
106  `extra` text,
107  PRIMARY KEY (`id`),
108  KEY `topic-form` (`topic_id`, `form_id`)
109) DEFAULT CHARSET=utf8;
110
111-- Handle A4 / A3 / A2 / A1 help topics. For these, consider the forms
112-- associated with each, which should sort above the ticket details form, as
113-- the graphical interface rendered it suchly. Then, consider cascaded
114-- forms, where the parent form was specified on a child.
115insert into `%TABLE_PREFIX%help_topic_form`
116    (`topic_id`, `form_id`, `sort`)
117    select A1.topic_id, case
118        when A3.form_id = 4294967295 then A4.form_id
119        when A2.form_id = 4294967295 then A3.form_id
120        when A1.form_id = 4294967295 then A2.form_id
121        else COALESCE(A4.form_id, A3.form_id, A2.form_id, A1.form_id) end as form_id, 1 as `sort`
122    from `%TABLE_PREFIX%help_topic` A1
123    left join `%TABLE_PREFIX%help_topic` A2 on (A2.topic_id = A1.topic_pid)
124    left join `%TABLE_PREFIX%help_topic` A3 on (A3.topic_id = A2.topic_pid)
125    left join `%TABLE_PREFIX%help_topic` A4 on (A4.topic_id = A3.topic_pid)
126    having `form_id` > 0
127    union
128    select A2.topic_id, id as `form_id`, 2 as `sort`
129    from `%TABLE_PREFIX%form` A1
130    join `%TABLE_PREFIX%help_topic` A2
131    where A1.`type` = 'T';
132
133-- Finished with patch
134UPDATE `%TABLE_PREFIX%config`
135    SET `value` = '2d590ffab4a6a928f08cc97aace1399e'
136    WHERE `key` = 'schema_signature' AND `namespace` = 'core';
137