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
-- Port data from the ticket table
-- 1. Create form entries for each ticket
INSERT INTO `%TABLE_PREFIX%form_entry` (
`form_id`, `object_id`, `object_type`, `sort`, `created`, `updated`)
SELECT (SELECT id FROM ost_form WHERE `type`='T'),
`ticket_id`, 'T', 10, `created`, `updated`
FROM `%TABLE_PREFIX%ticket`;
-- 2. Copy subject lines from the ticket table into form entry
INSERT INTO `%TABLE_PREFIX%form_entry_values` (
`field_id`, `entry_id`, `value`)
SELECT A3.`id`, A2.`id`, A1.`subject`
FROM `%TABLE_PREFIX%ticket` A1
INNER JOIN `%TABLE_PREFIX%form` A4 ON (`type`='T')
INNER JOIN `%TABLE_PREFIX%form_entry` A2 ON (A2.`object_id`
= A1.`ticket_id` AND A2.`object_type` = 'T')
INNER JOIN `%TABLE_PREFIX%form_field` A3 ON (A2.`form_id`
= A4.`id`)
WHERE A3.`name` = 'subject';
-- 2b. Copy priority from ticket to custom form entry
INSERT INTO `%TABLE_PREFIX%form_entry_values` (
`field_id`, `entry_id`, `value`, `value_id`)
SELECT A3.`id`, A2.`id`, A5.`priority_desc`, A1.`priority_id`
FROM `%TABLE_PREFIX%ticket` A1
INNER JOIN `%TABLE_PREFIX%form` A4 ON (`type`='T')
INNER JOIN `%TABLE_PREFIX%form_entry` A2 ON (A2.`object_id`
= A1.`ticket_id` AND A2.`object_type` = 'T')
INNER JOIN `%TABLE_PREFIX%form_field` A3 ON (A2.`form_id`
= A4.`id`)
INNER JOIN `%TABLE_PREFIX%ticket_priority` A5 ON (A5.`priority_id`
= A1.`priority_id`)
WHERE A3.`name` = 'priority';
-- 3. Create <user> accounts for everybody
-- - Start with creating email addresses for the accounts
INSERT INTO `%TABLE_PREFIX%user_email` (`address`)
SELECT DISTINCT `email` FROM `%TABLE_PREFIX%ticket`;
-- - Then create the accounts and link the `default_email`s
INSERT INTO `%TABLE_PREFIX%user` (`name`, `default_email_id`, `created`, `updated`)
SELECT MAX(`name`), A2.`id`, A1.`created`, A1.`updated`
FROM `%TABLE_PREFIX%ticket` A1
INNER JOIN `%TABLE_PREFIX%user_email` A2 ON (A1.`email` = A2.`address`)
GROUP BY A2.`id`;
-- - Now link the user and user_email tables
ALTER TABLE `%TABLE_PREFIX%user` ADD KEY `def_eml_id` (`default_email_id`, `id`);
UPDATE `%TABLE_PREFIX%user_email` A1
SET user_id = (
SELECT A2.`id` FROM `%TABLE_PREFIX%user` A2
WHERE `default_email_id` = A1.`id`);
ALTER TABLE `%TABLE_PREFIX%user` DROP INDEX `def_eml_id`;
-- - Update the ticket table
ALTER TABLE `%TABLE_PREFIX%ticket` ADD KEY `email_lookup` (`email`);
UPDATE `%TABLE_PREFIX%ticket` A1
JOIN `%TABLE_PREFIX%user_email` A2 ON A2.`address` = A1.`email`
SET A1.`user_id` = A2.`user_id`,
A1.`user_email_id` = A2.`id`;
ALTER TABLE `%TABLE_PREFIX%ticket` DROP INDEX `email_lookup`;
-- TODO: Move this to a client info dynamic entry
-- 4. Create form entries for each ticket
INSERT INTO `%TABLE_PREFIX%form_entry` (
`form_id`, `object_id`, `object_type`, `sort`, `created`, `updated`)
SELECT DISTINCT A2.`id`, `user_id`, 'U', 10, MIN(A1.`created`),
MAX(A1.`updated`)
FROM `%TABLE_PREFIX%ticket` A1
JOIN `%TABLE_PREFIX%form` A2 ON (A2.`type` = 'U')
GROUP BY `user_id`, A2.`id`;
-- 5. Copy Phone from the ticket table to section #1
INSERT INTO `%TABLE_PREFIX%form_entry_values` (
`field_id`, `entry_id`, `value`)
SELECT A3.`id`, A2.`id`, MAX(CONCAT(
REPLACE( REPLACE( REPLACE( REPLACE( REPLACE( REPLACE(
A1.`phone`,
' ', ''),
')', ''),
'(', ''),
'+', ''),
'-', ''),
'.', ''), 'X', A1.`phone_ext`))
FROM `%TABLE_PREFIX%ticket` A1
INNER JOIN `%TABLE_PREFIX%form` A4 ON (`type`='U')
INNER JOIN `%TABLE_PREFIX%form_entry` A2 ON (A2.`object_id`
= A1.`ticket_id` AND A2.`object_type` = 'U')
INNER JOIN `%TABLE_PREFIX%form_field` A3 ON (A2.`form_id`
= A4.`id`)
WHERE A3.`name` = 'phone' AND LENGTH(A1.`phone`)
GROUP BY A3.`id`, A2.`id`;
-- 6. Remove columns from ticket table
ALTER TABLE `%TABLE_PREFIX%ticket`
DROP COLUMN `name`,
DROP COLUMN `email`,
DROP COLUMN `phone`,
DROP COLUMN `phone_ext`,
DROP COLUMN `subject`,
DROP COLUMN `priority_id`;
-- 5. Cleanup ticket table with dropped varchar columns
OPTIMIZE TABLE `%TABLE_PREFIX%ticket`;