1/*
2 * @version=1.7RC2+
3 *
4 * change variable names
5 */
6
7-- Canned Responses (with variables)
8UPDATE `%TABLE_PREFIX%canned_response` SET `response` = REPLACE(`response`, '%id', '%{ticket.id}');
9UPDATE `%TABLE_PREFIX%canned_response` SET `response` = REPLACE(`response`, '%ticket', '%{ticket.number}');
10UPDATE `%TABLE_PREFIX%canned_response` SET `response` = REPLACE(`response`, '%name', '%{ticket.name}');
11UPDATE `%TABLE_PREFIX%canned_response` SET `response` = REPLACE(`response`, '%email', '%{ticket.email}');
12UPDATE `%TABLE_PREFIX%canned_response` SET `response` = REPLACE(`response`, '%subject', '%{ticket.subject}');
13
14UPDATE `%TABLE_PREFIX%canned_response` SET `response` = REPLACE(`response`, '%status', '%{ticket.status}');
15UPDATE `%TABLE_PREFIX%canned_response` SET `response` = REPLACE(`response`, '%priority', '%{ticket.priority}');
16
17UPDATE `%TABLE_PREFIX%canned_response` SET `response` = REPLACE(`response`, '%auth', '%{ticket.auth_token}');
18
19UPDATE `%TABLE_PREFIX%canned_response` SET `response` = REPLACE(`response`, '%phone', '%{ticket.phone_number}');
20UPDATE `%TABLE_PREFIX%canned_response` SET `response` = REPLACE(`response`, '%createdate', '%{ticket.create_date}');
21UPDATE `%TABLE_PREFIX%canned_response` SET `response` = REPLACE(`response`, '%duedate', '%{ticket.due_date}');
22UPDATE `%TABLE_PREFIX%canned_response` SET `response` = REPLACE(`response`, '%closedate', '%{ticket.close_date}');
23
24UPDATE `%TABLE_PREFIX%canned_response` SET `response` = REPLACE(`response`, '%topic', '%{ticket.topic.name}');
25UPDATE `%TABLE_PREFIX%canned_response` SET `response` = REPLACE(`response`, '%dept', '%{ticket.dept.name}');
26UPDATE `%TABLE_PREFIX%canned_response` SET `response` = REPLACE(`response`, '%team', '%{ticket.team.name}');
27
28-- %id
29UPDATE `%TABLE_PREFIX%email_template`
30    SET `ticket_autoresp_body` = REPLACE(`ticket_autoresp_body`, '%id', '%{ticket.id}'),
31        `message_autoresp_body` = REPLACE(`message_autoresp_body`, '%id', '%{ticket.id}'),
32        `ticket_notice_body` = REPLACE(`ticket_notice_body`, '%id', '%{ticket.id}'),
33        `ticket_overlimit_body` = REPLACE(`ticket_overlimit_body`, '%id', '%{ticket.id}'),
34        `ticket_reply_body` = REPLACE(`ticket_reply_body`, '%id', '%{ticket.id}'),
35        `ticket_alert_body` = REPLACE(`ticket_alert_body`, '%id', '%{ticket.id}'),
36        `message_alert_body` = REPLACE(`message_alert_body`, '%id', '%{ticket.id}'),
37        `note_alert_body` = REPLACE(`note_alert_body`, '%id', '%{ticket.id}'),
38        `assigned_alert_body` = REPLACE(`assigned_alert_body`, '%id', '%{ticket.id}'),
39        `transfer_alert_body` = REPLACE(`transfer_alert_body`, '%id', '%{ticket.id}'),
40        `ticket_overdue_body` = REPLACE(`ticket_overdue_body`, '%id', '%{ticket.id}');
41
42-- %ticket
43UPDATE `%TABLE_PREFIX%email_template`
44    SET `ticket_autoresp_subj` = REPLACE(`ticket_autoresp_subj`, '%ticket', '%{ticket.number}'),
45        `ticket_autoresp_body` = REPLACE(`ticket_autoresp_body`, '%ticket', '%{ticket.number}'),
46        `message_autoresp_subj` = REPLACE(`message_autoresp_subj`, '%ticket', '%{ticket.number}'),
47        `message_autoresp_body` = REPLACE(`message_autoresp_body`, '%ticket', '%{ticket.number}'),
48        `ticket_notice_subj` = REPLACE(`ticket_notice_subj`, '%ticket', '%{ticket.number}'),
49        `ticket_notice_body` = REPLACE(`ticket_notice_body`, '%ticket', '%{ticket.number}'),
50        `ticket_overlimit_subj` = REPLACE(`ticket_overlimit_subj`, '%ticket', '%{ticket.number}'),
51        `ticket_overlimit_body` = REPLACE(`ticket_overlimit_body`, '%ticket', '%{ticket.number}'),
52        `ticket_reply_subj` = REPLACE(`ticket_reply_subj`, '%ticket', '%{ticket.number}'),
53        `ticket_reply_body` = REPLACE(`ticket_reply_body`, '%ticket', '%{ticket.number}'),
54        `ticket_alert_subj` = REPLACE(`ticket_alert_subj`, '%ticket', '%{ticket.number}'),
55        `ticket_alert_body` = REPLACE(`ticket_alert_body`, '%ticket', '%{ticket.number}'),
56        `message_alert_subj` = REPLACE(`message_alert_subj`, '%ticket', '%{ticket.number}'),
57        `message_alert_body` = REPLACE(`message_alert_body`, '%ticket', '%{ticket.number}'),
58        `note_alert_subj` = REPLACE(`note_alert_subj`, '%ticket', '%{ticket.number}'),
59        `note_alert_body` = REPLACE(`note_alert_body`, '%ticket', '%{ticket.number}'),
60        `assigned_alert_subj` = REPLACE(`assigned_alert_subj`, '%ticket', '%{ticket.number}'),
61        `assigned_alert_body` = REPLACE(`assigned_alert_body`, '%ticket', '%{ticket.number}'),
62        `transfer_alert_subj` = REPLACE(`transfer_alert_subj`, '%ticket', '%{ticket.number}'),
63        `transfer_alert_body` = REPLACE(`transfer_alert_body`, '%ticket', '%{ticket.number}'),
64        `ticket_overdue_subj` = REPLACE(`ticket_overdue_subj`, '%ticket', '%{ticket.number}'),
65        `ticket_overdue_body` = REPLACE(`ticket_overdue_body`, '%ticket', '%{ticket.number}');
66
67-- %subject
68UPDATE `%TABLE_PREFIX%email_template`
69    SET `ticket_autoresp_subj` = REPLACE(`ticket_autoresp_subj`, '%subject', '%{ticket.subject}'),
70        `ticket_autoresp_body` = REPLACE(`ticket_autoresp_body`, '%subject', '%{ticket.subject}'),
71        `message_autoresp_subj` = REPLACE(`message_autoresp_subj`, '%subject', '%{ticket.subject}'),
72        `message_autoresp_body` = REPLACE(`message_autoresp_body`, '%subject', '%{ticket.subject}'),
73        `ticket_notice_subj` = REPLACE(`ticket_notice_subj`, '%subject', '%{ticket.subject}'),
74        `ticket_notice_body` = REPLACE(`ticket_notice_body`, '%subject', '%{ticket.subject}'),
75        `ticket_overlimit_subj` = REPLACE(`ticket_overlimit_subj`, '%subject', '%{ticket.subject}'),
76        `ticket_overlimit_body` = REPLACE(`ticket_overlimit_body`, '%subject', '%{ticket.subject}'),
77        `ticket_reply_subj` = REPLACE(`ticket_reply_subj`, '%subject', '%{ticket.subject}'),
78        `ticket_reply_body` = REPLACE(`ticket_reply_body`, '%subject', '%{ticket.subject}'),
79        `ticket_alert_subj` = REPLACE(`ticket_alert_subj`, '%subject', '%{ticket.subject}'),
80        `ticket_alert_body` = REPLACE(`ticket_alert_body`, '%subject', '%{ticket.subject}'),
81        `message_alert_subj` = REPLACE(`message_alert_subj`, '%subject', '%{ticket.subject}'),
82        `message_alert_body` = REPLACE(`message_alert_body`, '%subject', '%{ticket.subject}'),
83        `note_alert_subj` = REPLACE(`note_alert_subj`, '%subject', '%{ticket.subject}'),
84        `note_alert_body` = REPLACE(`note_alert_body`, '%subject', '%{ticket.subject}'),
85        `assigned_alert_subj` = REPLACE(`assigned_alert_subj`, '%subject', '%{ticket.subject}'),
86        `assigned_alert_body` = REPLACE(`assigned_alert_body`, '%subject', '%{ticket.subject}'),
87        `transfer_alert_subj` = REPLACE(`transfer_alert_subj`, '%subject', '%{ticket.subject}'),
88        `transfer_alert_body` = REPLACE(`transfer_alert_body`, '%subject', '%{ticket.subject}'),
89        `ticket_overdue_subj` = REPLACE(`ticket_overdue_subj`, '%subject', '%{ticket.subject}'),
90        `ticket_overdue_body` = REPLACE(`ticket_overdue_body`, '%subject', '%{ticket.subject}');
91
92-- %name
93UPDATE `%TABLE_PREFIX%email_template`
94    SET `ticket_autoresp_subj` = REPLACE(`ticket_autoresp_subj`, '%name', '%{ticket.name}'),
95        `ticket_autoresp_body` = REPLACE(`ticket_autoresp_body`, '%name', '%{ticket.name}'),
96        `message_autoresp_subj` = REPLACE(`message_autoresp_subj`, '%name', '%{ticket.name}'),
97        `message_autoresp_body` = REPLACE(`message_autoresp_body`, '%name', '%{ticket.name}'),
98        `ticket_notice_subj` = REPLACE(`ticket_notice_subj`, '%name', '%{ticket.name}'),
99        `ticket_notice_body` = REPLACE(`ticket_notice_body`, '%name', '%{ticket.name}'),
100        `ticket_overlimit_subj` = REPLACE(`ticket_overlimit_subj`, '%name', '%{ticket.name}'),
101        `ticket_overlimit_body` = REPLACE(`ticket_overlimit_body`, '%name', '%{ticket.name}'),
102        `ticket_reply_subj` = REPLACE(`ticket_reply_subj`, '%name', '%{ticket.name}'),
103        `ticket_reply_body` = REPLACE(`ticket_reply_body`, '%name', '%{ticket.name}'),
104        `ticket_alert_subj` = REPLACE(`ticket_alert_subj`, '%name', '%{ticket.name}'),
105        `ticket_alert_body` = REPLACE(`ticket_alert_body`, '%name', '%{ticket.name}'),
106        `message_alert_subj` = REPLACE(`message_alert_subj`, '%name', '%{ticket.name}'),
107        `message_alert_body` = REPLACE(`message_alert_body`, '%name', '%{ticket.name}'),
108        `note_alert_subj` = REPLACE(`note_alert_subj`, '%name', '%{ticket.name}'),
109        `note_alert_body` = REPLACE(`note_alert_body`, '%name', '%{ticket.name}'),
110        `assigned_alert_subj` = REPLACE(`assigned_alert_subj`, '%name', '%{ticket.name}'),
111        `assigned_alert_body` = REPLACE(`assigned_alert_body`, '%name', '%{ticket.name}'),
112        `transfer_alert_subj` = REPLACE(`transfer_alert_subj`, '%name', '%{ticket.name}'),
113        `transfer_alert_body` = REPLACE(`transfer_alert_body`, '%name', '%{ticket.name}'),
114        `ticket_overdue_subj` = REPLACE(`ticket_overdue_subj`, '%name', '%{ticket.name}'),
115        `ticket_overdue_body` = REPLACE(`ticket_overdue_body`, '%name', '%{ticket.name}');
116
117-- %email
118UPDATE `%TABLE_PREFIX%email_template`
119    SET `ticket_autoresp_subj` = REPLACE(`ticket_autoresp_subj`, '%email', '%{ticket.email}'),
120        `ticket_autoresp_body` = REPLACE(`ticket_autoresp_body`, '%email', '%{ticket.email}'),
121        `message_autoresp_subj` = REPLACE(`message_autoresp_subj`, '%email', '%{ticket.email}'),
122        `message_autoresp_body` = REPLACE(`message_autoresp_body`, '%email', '%{ticket.email}'),
123        `ticket_notice_subj` = REPLACE(`ticket_notice_subj`, '%email', '%{ticket.email}'),
124        `ticket_notice_body` = REPLACE(`ticket_notice_body`, '%email', '%{ticket.email}'),
125        `ticket_overlimit_subj` = REPLACE(`ticket_overlimit_subj`, '%email', '%{ticket.email}'),
126        `ticket_overlimit_body` = REPLACE(`ticket_overlimit_body`, '%email', '%{ticket.email}'),
127        `ticket_reply_subj` = REPLACE(`ticket_reply_subj`, '%email', '%{ticket.email}'),
128        `ticket_reply_body` = REPLACE(`ticket_reply_body`, '%email', '%{ticket.email}'),
129        `ticket_alert_subj` = REPLACE(`ticket_alert_subj`, '%email', '%{ticket.email}'),
130        `ticket_alert_body` = REPLACE(`ticket_alert_body`, '%email', '%{ticket.email}'),
131        `message_alert_subj` = REPLACE(`message_alert_subj`, '%email', '%{ticket.email}'),
132        `message_alert_body` = REPLACE(`message_alert_body`, '%email', '%{ticket.email}'),
133        `note_alert_subj` = REPLACE(`note_alert_subj`, '%email', '%{ticket.email}'),
134        `note_alert_body` = REPLACE(`note_alert_body`, '%email', '%{ticket.email}'),
135        `assigned_alert_subj` = REPLACE(`assigned_alert_subj`, '%email', '%{ticket.email}'),
136        `assigned_alert_body` = REPLACE(`assigned_alert_body`, '%email', '%{ticket.email}'),
137        `transfer_alert_subj` = REPLACE(`transfer_alert_subj`, '%email', '%{ticket.email}'),
138        `transfer_alert_body` = REPLACE(`transfer_alert_body`, '%email', '%{ticket.email}'),
139        `ticket_overdue_subj` = REPLACE(`ticket_overdue_subj`, '%email', '%{ticket.email}'),
140        `ticket_overdue_body` = REPLACE(`ticket_overdue_body`, '%email', '%{ticket.email}');
141
142-- %status
143UPDATE `%TABLE_PREFIX%email_template`
144    SET `ticket_autoresp_subj` = REPLACE(`ticket_autoresp_subj`, '%status', '%{ticket.status}'),
145        `ticket_autoresp_body` = REPLACE(`ticket_autoresp_body`, '%status', '%{ticket.status}'),
146        `message_autoresp_subj` = REPLACE(`message_autoresp_subj`, '%status', '%{ticket.status}'),
147        `message_autoresp_body` = REPLACE(`message_autoresp_body`, '%status', '%{ticket.status}'),
148        `ticket_notice_subj` = REPLACE(`ticket_notice_subj`, '%status', '%{ticket.status}'),
149        `ticket_notice_body` = REPLACE(`ticket_notice_body`, '%status', '%{ticket.status}'),
150        `ticket_overlimit_subj` = REPLACE(`ticket_overlimit_subj`, '%status', '%{ticket.status}'),
151        `ticket_overlimit_body` = REPLACE(`ticket_overlimit_body`, '%status', '%{ticket.status}'),
152        `ticket_reply_subj` = REPLACE(`ticket_reply_subj`, '%status', '%{ticket.status}'),
153        `ticket_reply_body` = REPLACE(`ticket_reply_body`, '%status', '%{ticket.status}'),
154        `ticket_alert_subj` = REPLACE(`ticket_alert_subj`, '%status', '%{ticket.status}'),
155        `ticket_alert_body` = REPLACE(`ticket_alert_body`, '%status', '%{ticket.status}'),
156        `message_alert_subj` = REPLACE(`message_alert_subj`, '%status', '%{ticket.status}'),
157        `message_alert_body` = REPLACE(`message_alert_body`, '%status', '%{ticket.status}'),
158        `note_alert_subj` = REPLACE(`note_alert_subj`, '%status', '%{ticket.status}'),
159        `note_alert_body` = REPLACE(`note_alert_body`, '%status', '%{ticket.status}'),
160        `assigned_alert_subj` = REPLACE(`assigned_alert_subj`, '%status', '%{ticket.status}'),
161        `assigned_alert_body` = REPLACE(`assigned_alert_body`, '%status', '%{ticket.status}'),
162        `transfer_alert_subj` = REPLACE(`transfer_alert_subj`, '%status', '%{ticket.status}'),
163        `transfer_alert_body` = REPLACE(`transfer_alert_body`, '%status', '%{ticket.status}'),
164        `ticket_overdue_subj` = REPLACE(`ticket_overdue_subj`, '%status', '%{ticket.status}'),
165        `ticket_overdue_body` = REPLACE(`ticket_overdue_body`, '%status', '%{ticket.status}');
166
167-- %priority
168UPDATE `%TABLE_PREFIX%email_template`
169    SET `ticket_autoresp_subj` = REPLACE(`ticket_autoresp_subj`, '%priority', '%{ticket.priority}'),
170        `ticket_autoresp_body` = REPLACE(`ticket_autoresp_body`, '%priority', '%{ticket.priority}'),
171        `message_autoresp_subj` = REPLACE(`message_autoresp_subj`, '%priority', '%{ticket.priority}'),
172        `message_autoresp_body` = REPLACE(`message_autoresp_body`, '%priority', '%{ticket.priority}'),
173        `ticket_notice_subj` = REPLACE(`ticket_notice_subj`, '%priority', '%{ticket.priority}'),
174        `ticket_notice_body` = REPLACE(`ticket_notice_body`, '%priority', '%{ticket.priority}'),
175        `ticket_overlimit_subj` = REPLACE(`ticket_overlimit_subj`, '%priority', '%{ticket.priority}'),
176        `ticket_overlimit_body` = REPLACE(`ticket_overlimit_body`, '%priority', '%{ticket.priority}'),
177        `ticket_reply_subj` = REPLACE(`ticket_reply_subj`, '%priority', '%{ticket.priority}'),
178        `ticket_reply_body` = REPLACE(`ticket_reply_body`, '%priority', '%{ticket.priority}'),
179        `ticket_alert_subj` = REPLACE(`ticket_alert_subj`, '%priority', '%{ticket.priority}'),
180        `ticket_alert_body` = REPLACE(`ticket_alert_body`, '%priority', '%{ticket.priority}'),
181        `message_alert_subj` = REPLACE(`message_alert_subj`, '%priority', '%{ticket.priority}'),
182        `message_alert_body` = REPLACE(`message_alert_body`, '%priority', '%{ticket.priority}'),
183        `note_alert_subj` = REPLACE(`note_alert_subj`, '%priority', '%{ticket.priority}'),
184        `note_alert_body` = REPLACE(`note_alert_body`, '%priority', '%{ticket.priority}'),
185        `assigned_alert_subj` = REPLACE(`assigned_alert_subj`, '%priority', '%{ticket.priority}'),
186        `assigned_alert_body` = REPLACE(`assigned_alert_body`, '%priority', '%{ticket.priority}'),
187        `transfer_alert_subj` = REPLACE(`transfer_alert_subj`, '%priority', '%{ticket.priority}'),
188        `transfer_alert_body` = REPLACE(`transfer_alert_body`, '%priority', '%{ticket.priority}'),
189        `ticket_overdue_subj` = REPLACE(`ticket_overdue_subj`, '%priority', '%{ticket.priority}'),
190        `ticket_overdue_body` = REPLACE(`ticket_overdue_body`, '%priority', '%{ticket.priority}');
191
192-- %auth
193UPDATE `%TABLE_PREFIX%email_template`
194    SET `ticket_autoresp_body` = REPLACE(`ticket_autoresp_body`, '%auth', '%{ticket.auth_code}'),
195        `message_autoresp_body` = REPLACE(`message_autoresp_body`, '%auth', '%{ticket.auth_code}'),
196        `ticket_notice_body` = REPLACE(`ticket_notice_body`, '%auth', '%{ticket.auth_code}'),
197        `ticket_overlimit_body` = REPLACE(`ticket_overlimit_body`, '%auth', '%{ticket.auth_code}'),
198        `ticket_reply_body` = REPLACE(`ticket_reply_body`, '%auth', '%{ticket.auth_code}'),
199        `ticket_alert_body` = REPLACE(`ticket_alert_body`, '%auth', '%{ticket.auth_code}'),
200        `message_alert_body` = REPLACE(`message_alert_body`, '%auth', '%{ticket.auth_code}'),
201        `note_alert_body` = REPLACE(`note_alert_body`, '%auth', '%{ticket.auth_code}'),
202        `assigned_alert_body` = REPLACE(`assigned_alert_body`, '%auth', '%{ticket.auth_code}'),
203        `transfer_alert_body` = REPLACE(`transfer_alert_body`, '%auth', '%{ticket.auth_code}'),
204        `ticket_overdue_body` = REPLACE(`ticket_overdue_body`, '%auth', '%{ticket.auth_code}');
205
206-- %phone
207UPDATE `%TABLE_PREFIX%email_template`
208    SET `ticket_autoresp_body` = REPLACE(`ticket_autoresp_body`, '%phone', '%{ticket.phone_number}'),
209        `message_autoresp_body` = REPLACE(`message_autoresp_body`, '%phone', '%{ticket.phone_number}'),
210        `ticket_notice_body` = REPLACE(`ticket_notice_body`, '%phone', '%{ticket.phone_number}'),
211        `ticket_overlimit_body` = REPLACE(`ticket_overlimit_body`, '%phone', '%{ticket.phone_number}'),
212        `ticket_reply_body` = REPLACE(`ticket_reply_body`, '%phone', '%{ticket.phone_number}'),
213        `ticket_alert_body` = REPLACE(`ticket_alert_body`, '%phone', '%{ticket.phone_number}'),
214        `message_alert_body` = REPLACE(`message_alert_body`, '%phone', '%{ticket.phone_number}'),
215        `note_alert_body` = REPLACE(`note_alert_body`, '%phone', '%{ticket.phone_number}'),
216        `assigned_alert_body` = REPLACE(`assigned_alert_body`, '%phone', '%{ticket.phone_number}'),
217        `transfer_alert_body` = REPLACE(`transfer_alert_body`, '%phone', '%{ticket.phone_number}'),
218        `ticket_overdue_body` = REPLACE(`ticket_overdue_body`, '%phone', '%{ticket.phone_number}');
219
220-- %createdate
221UPDATE `%TABLE_PREFIX%email_template`
222    SET `ticket_autoresp_body` = REPLACE(`ticket_autoresp_body`, '%createdate', '%{ticket.create_date}'),
223        `message_autoresp_body` = REPLACE(`message_autoresp_body`, '%createdate', '%{ticket.create_date}'),
224        `ticket_notice_body` = REPLACE(`ticket_notice_body`, '%createdate', '%{ticket.create_date}'),
225        `ticket_overlimit_body` = REPLACE(`ticket_overlimit_body`, '%createdate', '%{ticket.create_date}'),
226        `ticket_reply_body` = REPLACE(`ticket_reply_body`, '%createdate', '%{ticket.create_date}'),
227        `ticket_alert_body` = REPLACE(`ticket_alert_body`, '%createdate', '%{ticket.create_date}'),
228        `message_alert_body` = REPLACE(`message_alert_body`, '%createdate', '%{ticket.create_date}'),
229        `note_alert_body` = REPLACE(`note_alert_body`, '%createdate', '%{ticket.create_date}'),
230        `assigned_alert_body` = REPLACE(`assigned_alert_body`, '%createdate', '%{ticket.create_date}'),
231        `transfer_alert_body` = REPLACE(`transfer_alert_body`, '%createdate', '%{ticket.create_date}'),
232        `ticket_overdue_body` = REPLACE(`ticket_overdue_body`, '%createdate', '%{ticket.create_date}');
233
234-- %duedate
235UPDATE `%TABLE_PREFIX%email_template`
236    SET `ticket_autoresp_body` = REPLACE(`ticket_autoresp_body`, '%duedate', '%{ticket.due_date}'),
237        `message_autoresp_body` = REPLACE(`message_autoresp_body`, '%duedate', '%{ticket.due_date}'),
238        `ticket_notice_body` = REPLACE(`ticket_notice_body`, '%duedate', '%{ticket.due_date}'),
239        `ticket_overlimit_body` = REPLACE(`ticket_overlimit_body`, '%duedate', '%{ticket.due_date}'),
240        `ticket_reply_body` = REPLACE(`ticket_reply_body`, '%duedate', '%{ticket.due_date}'),
241        `ticket_alert_body` = REPLACE(`ticket_alert_body`, '%duedate', '%{ticket.due_date}'),
242        `message_alert_body` = REPLACE(`message_alert_body`, '%duedate', '%{ticket.due_date}'),
243        `note_alert_body` = REPLACE(`note_alert_body`, '%duedate', '%{ticket.due_date}'),
244        `assigned_alert_body` = REPLACE(`assigned_alert_body`, '%duedate', '%{ticket.due_date}'),
245        `transfer_alert_body` = REPLACE(`transfer_alert_body`, '%duedate', '%{ticket.due_date}'),
246        `ticket_overdue_body` = REPLACE(`ticket_overdue_body`, '%duedate', '%{ticket.due_date}');
247
248-- %closedate
249UPDATE `%TABLE_PREFIX%email_template`
250    SET `ticket_autoresp_body` = REPLACE(`ticket_autoresp_body`, '%closedate', '%{ticket.close_date}'),
251        `message_autoresp_body` = REPLACE(`message_autoresp_body`, '%closedate', '%{ticket.close_date}'),
252        `ticket_notice_body` = REPLACE(`ticket_notice_body`, '%closedate', '%{ticket.close_date}'),
253        `ticket_overlimit_body` = REPLACE(`ticket_overlimit_body`, '%closedate', '%{ticket.close_date}'),
254        `ticket_reply_body` = REPLACE(`ticket_reply_body`, '%closedate', '%{ticket.close_date}'),
255        `ticket_alert_body` = REPLACE(`ticket_alert_body`, '%closedate', '%{ticket.close_date}'),
256        `message_alert_body` = REPLACE(`message_alert_body`, '%closedate', '%{ticket.close_date}'),
257        `note_alert_body` = REPLACE(`note_alert_body`, '%closedate', '%{ticket.close_date}'),
258        `assigned_alert_body` = REPLACE(`assigned_alert_body`, '%closedate', '%{ticket.close_date}'),
259        `transfer_alert_body` = REPLACE(`transfer_alert_body`, '%closedate', '%{ticket.close_date}'),
260        `ticket_overdue_body` = REPLACE(`ticket_overdue_body`, '%closedate', '%{ticket.close_date}');
261
262-- %topic
263UPDATE `%TABLE_PREFIX%email_template`
264    SET `ticket_autoresp_body` = REPLACE(`ticket_autoresp_body`, '%topic', '%{ticket.topic.name}'),
265        `message_autoresp_body` = REPLACE(`message_autoresp_body`, '%topic', '%{ticket.topic.name}'),
266        `ticket_notice_body` = REPLACE(`ticket_notice_body`, '%topic', '%{ticket.topic.name}'),
267        `ticket_overlimit_body` = REPLACE(`ticket_overlimit_body`, '%topic', '%{ticket.topic.name}'),
268        `ticket_reply_body` = REPLACE(`ticket_reply_body`, '%topic', '%{ticket.topic.name}'),
269        `ticket_alert_body` = REPLACE(`ticket_alert_body`, '%topic', '%{ticket.topic.name}'),
270        `message_alert_body` = REPLACE(`message_alert_body`, '%topic', '%{ticket.topic.name}'),
271        `note_alert_body` = REPLACE(`note_alert_body`, '%topic', '%{ticket.topic.name}'),
272        `assigned_alert_body` = REPLACE(`assigned_alert_body`, '%topic', '%{ticket.topic.name}'),
273        `transfer_alert_body` = REPLACE(`transfer_alert_body`, '%topic', '%{ticket.topic.name}'),
274        `ticket_overdue_body` = REPLACE(`ticket_overdue_body`, '%topic', '%{ticket.topic.name}');
275
276-- %dept
277UPDATE `%TABLE_PREFIX%email_template`
278    SET `ticket_autoresp_body` = REPLACE(`ticket_autoresp_body`, '%dept', '%{ticket.dept.name}'),
279        `message_autoresp_body` = REPLACE(`message_autoresp_body`, '%dept', '%{ticket.dept.name}'),
280        `ticket_notice_body` = REPLACE(`ticket_notice_body`, '%dept', '%{ticket.dept.name}'),
281        `ticket_overlimit_body` = REPLACE(`ticket_overlimit_body`, '%dept', '%{ticket.dept.name}'),
282        `ticket_reply_body` = REPLACE(`ticket_reply_body`, '%dept', '%{ticket.dept.name}'),
283        `ticket_alert_body` = REPLACE(`ticket_alert_body`, '%dept', '%{ticket.dept.name}'),
284        `message_alert_body` = REPLACE(`message_alert_body`, '%dept', '%{ticket.dept.name}'),
285        `note_alert_body` = REPLACE(`note_alert_body`, '%dept', '%{ticket.dept.name}'),
286        `assigned_alert_body` = REPLACE(`assigned_alert_body`, '%dept', '%{ticket.dept.name}'),
287        `transfer_alert_subj` = REPLACE(`transfer_alert_subj`, '%dept', '%{ticket.dept.name}'),
288        `transfer_alert_body` = REPLACE(`transfer_alert_body`, '%dept', '%{ticket.dept.name}'),
289        `ticket_overdue_body` = REPLACE(`ticket_overdue_body`, '%dept', '%{ticket.dept.name}');
290
291-- %team
292UPDATE `%TABLE_PREFIX%email_template`
293    SET `ticket_autoresp_body` = REPLACE(`ticket_autoresp_body`, '%team', '%{ticket.team.name}'),
294        `message_autoresp_body` = REPLACE(`message_autoresp_body`, '%team', '%{ticket.team.name}'),
295        `ticket_notice_body` = REPLACE(`ticket_notice_body`, '%team', '%{ticket.team.name}'),
296        `ticket_overlimit_body` = REPLACE(`ticket_overlimit_body`, '%team', '%{ticket.team.name}'),
297        `ticket_reply_body` = REPLACE(`ticket_reply_body`, '%team', '%{ticket.team.name}'),
298        `ticket_alert_body` = REPLACE(`ticket_alert_body`, '%team', '%{ticket.team.name}'),
299        `message_alert_body` = REPLACE(`message_alert_body`, '%team', '%{ticket.team.name}'),
300        `note_alert_body` = REPLACE(`note_alert_body`, '%team', '%{ticket.team.name}'),
301        `assigned_alert_body` = REPLACE(`assigned_alert_body`, '%team', '%{ticket.team.name}'),
302        `transfer_alert_body` = REPLACE(`transfer_alert_body`, '%team', '%{ticket.team.name}'),
303        `ticket_overdue_body` = REPLACE(`ticket_overdue_body`, '%team', '%{ticket.team.name}');
304
305-- %clientlink
306UPDATE `%TABLE_PREFIX%email_template`
307    SET `ticket_autoresp_body` = REPLACE(`ticket_autoresp_body`, '%clientlink', '%{ticket.client_link}'),
308        `message_autoresp_body` = REPLACE(`message_autoresp_body`, '%clientlink', '%{ticket.client_link}'),
309        `ticket_notice_body` = REPLACE(`ticket_notice_body`, '%clientlink', '%{ticket.client_link}'),
310        `ticket_overlimit_body` = REPLACE(`ticket_overlimit_body`, '%clientlink', '%{ticket.client_link}'),
311        `ticket_reply_body` = REPLACE(`ticket_reply_body`, '%clientlink', '%{ticket.client_link}'),
312        `ticket_alert_body` = REPLACE(`ticket_alert_body`, '%clientlink', '%{ticket.client_link}'),
313        `message_alert_body` = REPLACE(`message_alert_body`, '%clientlink', '%{ticket.client_link}'),
314        `note_alert_body` = REPLACE(`note_alert_body`, '%clientlink', '%{ticket.client_link}'),
315        `assigned_alert_body` = REPLACE(`assigned_alert_body`, '%clientlink', '%{ticket.client_link}'),
316        `transfer_alert_body` = REPLACE(`transfer_alert_body`, '%clientlink', '%{ticket.client_link}'),
317        `ticket_overdue_body` = REPLACE(`ticket_overdue_body`, '%clientlink', '%{ticket.client_link}');
318
319-- %staff (recipient of the alert)
320UPDATE `%TABLE_PREFIX%email_template`
321    SET `ticket_alert_body` = REPLACE(`ticket_alert_body`, '%staff', '%{recipient}'),
322        `message_alert_body` = REPLACE(`message_alert_body`, '%staff', '%{recipient}'),
323        `note_alert_body` = REPLACE(`note_alert_body`, '%staff', '%{recipient}'),
324        `assigned_alert_body` = REPLACE(`assigned_alert_body`, '%staff', '%{recipient}'),
325        `transfer_alert_body` = REPLACE(`transfer_alert_body`, '%staff', '%{recipient}'),
326        `ticket_overlimit_body` = REPLACE(`ticket_overlimit_body`, '%staff', '%{recipient}'),
327        `ticket_overdue_body` = REPLACE(`ticket_overdue_body`, '%staff', '%{recipient}');
328
329-- %message
330UPDATE `%TABLE_PREFIX%email_template`
331    SET `ticket_autoresp_body` = REPLACE(`ticket_autoresp_body`, '%message', '%{message}'),
332        `message_autoresp_body` = REPLACE(`message_autoresp_body`, '%message', '%{message}'),
333        `ticket_notice_body` = REPLACE(`ticket_notice_body`, '%message', '%{message}'),
334        `ticket_overlimit_body` = REPLACE(`ticket_overlimit_body`, '%message', '%{message}'),
335        `ticket_reply_body` = REPLACE(`ticket_reply_body`, '%message', '%{message}'),
336        `ticket_alert_body` = REPLACE(`ticket_alert_body`, '%message', '%{message}'),
337        `message_alert_body` = REPLACE(`message_alert_body`, '%message', '%{message}'),
338        `note_alert_body` = REPLACE(`note_alert_body`, '%message', '%{message}'),
339        `assigned_alert_body` = REPLACE(`assigned_alert_body`, '%message', '%{message}'),
340        `transfer_alert_body` = REPLACE(`transfer_alert_body`, '%message', '%{message}'),
341        `ticket_overdue_body` = REPLACE(`ticket_overdue_body`, '%message', '%{message}');
342
343-- %response
344UPDATE `%TABLE_PREFIX%email_template`
345    SET `ticket_autoresp_body` = REPLACE(`ticket_autoresp_body`, '%response', '%{response}'),
346        `message_autoresp_body` = REPLACE(`message_autoresp_body`, '%response', '%{response}'),
347        `ticket_notice_body` = REPLACE(`ticket_notice_body`, '%response', '%{response}'),
348        `ticket_overlimit_body` = REPLACE(`ticket_overlimit_body`, '%response', '%{response}'),
349        `ticket_reply_body` = REPLACE(`ticket_reply_body`, '%response', '%{response}'),
350        `ticket_alert_body` = REPLACE(`ticket_alert_body`, '%response', '%{response}'),
351        `message_alert_body` = REPLACE(`message_alert_body`, '%response', '%{response}'),
352        `note_alert_body` = REPLACE(`note_alert_body`, '%response', '%{response}'),
353        `assigned_alert_body` = REPLACE(`assigned_alert_body`, '%response', '%{response}'),
354        `transfer_alert_body` = REPLACE(`transfer_alert_body`, '%response', '%{response}'),
355        `ticket_overdue_body` = REPLACE(`ticket_overdue_body`, '%response', '%{response}');
356
357-- %note
358UPDATE `%TABLE_PREFIX%email_template`
359    SET `note_alert_body` = REPLACE(`note_alert_body`, '%note', '* %{note.title} *\r\n\r\n%{note.message}'),
360        `assigned_alert_body` = REPLACE(`assigned_alert_body`, '%note', '%{comments}'),
361        `transfer_alert_body` = REPLACE(`transfer_alert_body`, '%note', '%{comments}');
362
363-- %{note} (dev branch installations)
364UPDATE `%TABLE_PREFIX%email_template`
365    SET `note_alert_body` = REPLACE(`note_alert_body`, '%{note}', '%{note.message}'),
366        `assigned_alert_body` = REPLACE(`assigned_alert_body`, '%{note}', '%{comments}'),
367        `transfer_alert_body` = REPLACE(`transfer_alert_body`, '%{note}', '%{comments}');
368
369-- %{title} (dev branch installations)
370UPDATE `%TABLE_PREFIX%email_template`
371    SET `note_alert_body` = REPLACE(`note_alert_body`, '%{title}', '* %{note.title} *\r\n');
372
373-- %url
374UPDATE `%TABLE_PREFIX%email_template`
375    SET `ticket_autoresp_body` = REPLACE(`ticket_autoresp_body`, '%url', '%{url}'),
376        `message_autoresp_body` = REPLACE(`message_autoresp_body`, '%url', '%{url}'),
377        `ticket_notice_body` = REPLACE(`ticket_notice_body`, '%url', '%{url}'),
378        `ticket_overlimit_body` = REPLACE(`ticket_overlimit_body`, '%url', '%{url}'),
379        `ticket_reply_body` = REPLACE(`ticket_reply_body`, '%url', '%{url}'),
380        `ticket_alert_body` = REPLACE(`ticket_alert_body`, '%url', '%{url}'),
381        `message_alert_body` = REPLACE(`message_alert_body`, '%url', '%{url}'),
382        `note_alert_body` = REPLACE(`note_alert_body`, '%url', '%{url}'),
383        `assigned_alert_body` = REPLACE(`assigned_alert_body`, '%url', '%{url}'),
384        `transfer_alert_body` = REPLACE(`transfer_alert_body`, '%url', '%{url}'),
385        `ticket_overdue_body` = REPLACE(`ticket_overdue_body`, '%url', '%{url}');
386
387-- %signature
388UPDATE `%TABLE_PREFIX%email_template`
389    SET `ticket_autoresp_body` = REPLACE(`ticket_autoresp_body`, '%signature', '%{signature}'),
390        `message_autoresp_body` = REPLACE(`message_autoresp_body`, '%signature', '%{signature}'),
391        `ticket_notice_body` = REPLACE(`ticket_notice_body`, '%signature', '%{signature}'),
392        `ticket_overlimit_body` = REPLACE(`ticket_overlimit_body`, '%signature', '%{signature}'),
393        `ticket_reply_body` = REPLACE(`ticket_reply_body`, '%signature', '%{signature}'),
394        `ticket_alert_body` = REPLACE(`ticket_alert_body`, '%signature', '%{signature}'),
395        `message_alert_body` = REPLACE(`message_alert_body`, '%signature', '%{signature}'),
396        `note_alert_body` = REPLACE(`note_alert_body`, '%signature', '%{signature}'),
397        `assigned_alert_body` = REPLACE(`assigned_alert_body`, '%signature', '%{signature}'),
398        `transfer_alert_body` = REPLACE(`transfer_alert_body`, '%signature', '%{signature}'),
399        `ticket_overdue_body` = REPLACE(`ticket_overdue_body`, '%signature', '%{signature}');
400
401-- %assignee
402UPDATE `%TABLE_PREFIX%email_template`
403    SET `assigned_alert_subj` = REPLACE(`assigned_alert_subj`, '%assignee', '%{assignee}'),
404        `assigned_alert_body` = REPLACE(`assigned_alert_body`, '%assignee', '%{assignee}');
405
406-- %assigner
407UPDATE `%TABLE_PREFIX%email_template`
408    SET `assigned_alert_subj` = REPLACE(`assigned_alert_subj`, '%assigner', '%{assigner}'),
409        `assigned_alert_body` = REPLACE(`assigned_alert_body`, '%assigner', '%{assigner}');
410
411/* Change links */
412
413-- Client URL -> %{url}/view.php?e=%{ticket.email}&t=%{ticket.number}
414UPDATE `%TABLE_PREFIX%email_template`
415    SET `ticket_autoresp_body` = REPLACE(`ticket_autoresp_body`, '%{url}/view.php?e=%{ticket.email}&t=%{ticket.number}', '%{ticket.client_link}'),
416        `message_autoresp_body` = REPLACE(`message_autoresp_body`, '%{url}/view.php?e=%{ticket.email}&t=%{ticket.number}', '%{ticket.client_link}'),
417        `ticket_notice_body` = REPLACE(`ticket_notice_body`, '%{url}/view.php?e=%{ticket.email}&t=%{ticket.number}', '%{ticket.client_link}'),
418        `ticket_overlimit_body` = REPLACE(`ticket_overlimit_body`, '%{url}/view.php?e=%{ticket.email}&t=%{ticket.number}', '%{ticket.client_link}'),
419        `ticket_reply_body` = REPLACE(`ticket_reply_body`, '%{url}/view.php?e=%{ticket.email}&t=%{ticket.number}', '%{ticket.client_link}');
420
421-- Client URL -> %{url}/view.php?e=%{ticket.email} (overlimit template)
422UPDATE `%TABLE_PREFIX%email_template`
423    SET `ticket_overlimit_body` = REPLACE(`ticket_overlimit_body`, '%{url}/view.php?e=%{ticket.email}', '%{url}/tickets.php?e=%{ticket.email}');
424
425-- Staff URL -> %{url}/scp/ticket.php?id=%{ticket.id} (should be tickets.php)
426UPDATE `%TABLE_PREFIX%email_template`
427    SET `ticket_alert_body` = REPLACE(`ticket_alert_body`, '%{url}/scp/ticket.php?id=%{ticket.id}', '%{ticket.staff_link}'),
428        `message_alert_body` = REPLACE(`message_alert_body`, '%{url}/scp/ticket.php?id=%{ticket.id}', '%{ticket.staff_link}'),
429        `note_alert_body` = REPLACE(`note_alert_body`, '%{url}/scp/ticket.php?id=%{ticket.id}', '%{ticket.staff_link}'),
430        `assigned_alert_body` = REPLACE(`assigned_alert_body`, '%{url}/scp/ticket.php?id=%{ticket.id}', '%{ticket.staff_link}'),
431        `transfer_alert_body` = REPLACE(`transfer_alert_body`, '%{url}/scp/ticket.php?id=%{ticket.id}', '%{ticket.staff_link}'),
432        `ticket_overdue_body` = REPLACE(`ticket_overdue_body`, '%{url}/scp/ticket.php?id=%{ticket.id}', '%{ticket.staff_link}');
433
434-- Staff URL 2 -> %{url}/scp/tickets.php?id=%{ticket.id}
435UPDATE `%TABLE_PREFIX%email_template`
436    SET `ticket_alert_body` = REPLACE(`ticket_alert_body`, '%{url}/scp/tickets.php?id=%{ticket.id}', '%{ticket.staff_link}'),
437        `message_alert_body` = REPLACE(`message_alert_body`, '%{url}/scp/tickets.php?id=%{ticket.id}', '%{ticket.staff_link}'),
438        `note_alert_body` = REPLACE(`note_alert_body`, '%{url}/scp/tickets.php?id=%{ticket.id}', '%{ticket.staff_link}'),
439        `assigned_alert_body` = REPLACE(`assigned_alert_body`, '%{url}/scp/tickets.php?id=%{ticket.id}', '%{ticket.staff_link}'),
440        `transfer_alert_body` = REPLACE(`transfer_alert_body`, '%{url}/scp/tickets.php?id=%{ticket.id}', '%{ticket.staff_link}'),
441        `ticket_overdue_body` = REPLACE(`ticket_overdue_body`, '%{url}/scp/tickets.php?id=%{ticket.id}', '%{ticket.staff_link}');
442
443 -- update schema signature
444UPDATE `%TABLE_PREFIX%config`
445    SET `schema_signature`='f4da0c9befa257b5a20a923d4e9c0e91';
446