· 8 years ago · Dec 21, 2017, 04:46 PM
1IF EXISTS (SELECT * FROM tempdb..sysobjects WHERE id=OBJECT_ID('tempdb..#tmpErrors')) DROP TABLE #tmpErrors
2GO
3CREATE TABLE #tmpErrors ([Message] nvarchar(255))
4GO
5
6
7BEGIN TRANSACTION
8
9DECLARE @SourceURL AS nvarchar(max)
10DECLARE @TargetURL AS nvarchar(max)
11DECLARE @ChangeToRelative as bit
12DECLARE @NewURL AS nvarchar(max)
13DECLARE @Error AS int
14
15/*******VARIABLES A MODIFICAR POR LAS URLS DE LA INSTALACION*********/
16
17SET @SourceURL = N'http://old.auraportal.com/'
18SET @TargetURL = N'http://new.auraportal.com/'
19SET @ChangeToRelative = 0 /*NO = 0, Yes = 1*/
20
21/*******************************************************************/
22
23IF(@ChangeToRelative = 1)
24 SET @NewURL = N'./'
25ELSE
26 SET @NewURL = @TargetURL
27
28IF(@SourceURL = '' OR @TargetURL = '')
29 INSERT INTO #tmpErrors ([Message]) VALUES ('Error: SourceURL & TargetURL value')
30
31--Forms
32 IF(@ChangeToRelative = 1)
33 UPDATE AP__PaginaFormulario WITH(ROWLOCK)
34 SET HtmlPagina = CAST(REPLACE(REPLACE(REPLACE(CAST(HtmlPagina AS nvarchar(max)), @SourceURL + N'Home/', @NewURL), @SourceURL + N'AP/', @NewURL),@SourceURL, @TargetURL) AS ntext)
35 ELSE
36 UPDATE AP__PaginaFormulario WITH(ROWLOCK)
37 SET HtmlPagina = CAST(REPLACE(CAST(HtmlPagina AS nvarchar(max)), @SourceURL, @NewURL) AS ntext)
38
39
40 IF(@@Error > 0)
41 INSERT INTO #tmpErrors ([Message]) VALUES ('Error: Update AP__PaginaFormulario')
42
43--Divisions
44 IF(@ChangeToRelative = 1)
45 UPDATE AP__DivisionPaginaFormulario WITH(ROWLOCK)
46 SET htmlDivision = CAST(REPLACE(REPLACE(REPLACE(CAST(htmlDivision AS nvarchar(max)), @SourceURL + N'Home/', @NewURL), @SourceURL + N'AP/', @NewURL), @SourceURL, @TargetURL) AS ntext),
47 TextoCuerpo = CAST(REPLACE(REPLACE(REPLACE(CAST(TextoCuerpo AS nvarchar(max)), @SourceURL + N'Home/', @NewURL), @SourceURL + N'AP/', @NewURL), @SourceURL, @TargetURL) AS ntext)
48 ELSE
49 UPDATE AP__DivisionPaginaFormulario WITH(ROWLOCK)
50 SET htmlDivision = CAST(REPLACE(CAST(htmlDivision AS nvarchar(max)), @SourceURL, @NewURL) AS ntext),
51 TextoCuerpo = CAST(REPLACE(CAST(TextoCuerpo AS nvarchar(max)), @SourceURL, @NewURL) AS ntext)
52
53 IF(@@Error > 0)
54 INSERT INTO #tmpErrors ([Message]) VALUES ('Error: Update AP__DivisionPaginaFormulario')
55
56--Fields
57 IF(@ChangeToRelative = 1)
58 UPDATE AP__BPM_CampoFormulario WITH(ROWLOCK)
59 SET HtmlCampo = CAST(REPLACE(REPLACE(REPLACE(CAST(HtmlCampo AS nvarchar(max)), @SourceURL + N'Home/', @NewURL), @SourceURL + N'AP/', @NewURL), @SourceURL, @TargetURL) AS ntext),
60 Url = CAST(REPLACE(REPLACE(REPLACE(CAST(Url AS nvarchar(max)), @SourceURL + N'Home/', @NewURL), @SourceURL + N'AP/', @NewURL), @SourceURL, @TargetURL) AS ntext),
61 TextoCuerpo = CAST(REPLACE(REPLACE(REPLACE(CAST(TextoCuerpo AS nvarchar(max)), @SourceURL + N'Home/', @NewURL), @SourceURL + N'AP/', @NewURL), @SourceURL, @TargetURL) AS ntext),
62 TextoCabecera = CAST(REPLACE(REPLACE(REPLACE(CAST(TextoCabecera AS nvarchar(max)), @SourceURL + N'Home/', @NewURL), @SourceURL + N'AP/', @NewURL), @SourceURL, @TargetURL) AS ntext)
63 ELSE
64 UPDATE AP__BPM_CampoFormulario WITH(ROWLOCK)
65 SET HtmlCampo = CAST(REPLACE(CAST(HtmlCampo AS nvarchar(max)), @SourceURL, @NewURL) AS ntext),
66 Url = CAST(REPLACE(CAST(Url AS nvarchar(max)), @SourceURL, @TargetURL) AS ntext),
67 TextoCuerpo = CAST(REPLACE(CAST(TextoCuerpo AS nvarchar(max)), @SourceURL, @NewURL) AS ntext),
68 TextoCabecera = CAST(REPLACE(CAST(TextoCabecera AS nvarchar(max)), @SourceURL, @NewURL) AS ntext)
69
70 IF(@@Error > 0)
71 INSERT INTO #tmpErrors ([Message]) VALUES ('Error: Update AP__BPM_CampoFormulario')
72
73--Instructions
74 IF(@ChangeToRelative = 1)
75 UPDATE AP__BPM_TareasClases_Personal WITH(ROWLOCK)
76 SET Instrucciones = CAST(REPLACE(REPLACE(REPLACE(CAST(Instrucciones AS nvarchar(max)), @SourceURL + N'Home/', @NewURL), @SourceURL + N'AP/', @NewURL), @SourceURL, @TargetURL) AS ntext)
77 ELSE
78 UPDATE AP__BPM_TareasClases_Personal WITH(ROWLOCK)
79 SET Instrucciones = CAST(REPLACE(CAST(Instrucciones AS nvarchar(max)), @SourceURL, @NewURL) AS ntext)
80
81 IF(@@Error > 0)
82 INSERT INTO #tmpErrors ([Message]) VALUES ('Error: Update AP__BPM_TareasClases_Personal')
83
84--Notify
85
86 IF(@ChangeToRelative = 1)
87 UPDATE AP__BPM_TareaNotificacion WITH(ROWLOCK)
88 SET ContenidoNotificacion = CAST(REPLACE(REPLACE(REPLACE(CAST(ContenidoNotificacion AS nvarchar(max)), @SourceURL + N'Home/', @NewURL), @SourceURL + N'AP/', @NewURL), @SourceURL, @TargetURL) AS ntext)
89 ELSE
90 UPDATE AP__BPM_TareaNotificacion WITH(ROWLOCK)
91 SET ContenidoNotificacion = CAST(REPLACE(CAST(ContenidoNotificacion AS nvarchar(max)), @SourceURL, @NewURL) AS ntext)
92
93 IF(@@Error > 0)
94 INSERT INTO #tmpErrors ([Message]) VALUES ('Error: Update AP__BPM_TareaNotificacion')
95
96 IF(@ChangeToRelative = 1)
97 UPDATE AP__BPM_TareasSistClases_Notificacion WITH(ROWLOCK)
98 SET ContenidoTexto = CAST(REPLACE(REPLACE(REPLACE(CAST(ContenidoTexto AS nvarchar(max)), @SourceURL + N'Home/', @NewURL), @SourceURL + N'AP/', @NewURL), @SourceURL, @TargetURL) AS ntext)
99 where IdTareaSistClaseAsc in (select ID from AP__BPM_TareasSistClases with(nolock) where IdFuncion in (1,3))--Notificaciones empleados y externos
100 ELSE
101 UPDATE AP__BPM_TareasSistClases_Notificacion WITH(ROWLOCK)
102 SET ContenidoTexto = CAST(REPLACE(CAST(ContenidoTexto AS nvarchar(max)), @SourceURL, @NewURL) AS ntext)
103 where IdTareaSistClaseAsc in (select ID from AP__BPM_TareasSistClases with(nolock) where IdFuncion in (1,3))--Notificaciones empleados y externos
104
105 IF(@@Error > 0)
106 INSERT INTO #tmpErrors ([Message]) VALUES ('Error: Update AP__BPM_TareasSistClases_Notificacion')
107
108--Notify via email
109 IF(((@ChangeToRelative = 1) and (@TargetURL <> '')) or (@ChangeToRelative = 0))
110 begin
111 UPDATE AP__BPM_TareasSistClases_EMFormulario WITH(ROWLOCK)
112 SET ContenidoTexto = CAST(REPLACE(CAST(ContenidoTexto AS nvarchar(max)), @SourceURL, @TargetURL) AS ntext)
113
114 IF(@@Error > 0)
115 INSERT INTO #tmpErrors ([Message]) VALUES ('Error: Update AP__BPM_TareasSistClases_EMFormulario')
116
117 UPDATE AP__BPM_TareasSistClases_Notificacion WITH(ROWLOCK)
118 SET ContenidoTexto = CAST(REPLACE(CAST(ContenidoTexto AS nvarchar(max)), @SourceURL, @TargetURL) AS ntext)
119 where IdTareaSistClaseAsc in (select ID from AP__BPM_TareasSistClases with(nolock) where IdFuncion in (5,31,24))--Notificaciones via email, notifica via sms, confirmar email
120
121 IF(@@Error > 0)
122 INSERT INTO #tmpErrors ([Message]) VALUES ('Error: Update AP__BPM_TareasSistClases_Notificacion')
123
124 UPDATE AP__BPM_TareaEnvioFormulario WITH(ROWLOCK)
125 SET ContenidoMail = CAST(REPLACE(CAST(ContenidoMail AS nvarchar(max)), @SourceURL, @TargetURL) AS ntext)
126
127 IF(@@Error > 0)
128 INSERT INTO #tmpErrors ([Message]) VALUES ('Error: Update AP__BPM_TareaEnvioFormulario')
129 end
130
131--Base Doc
132 IF(@ChangeToRelative = 1)
133 UPDATE AP__DocumentosBase WITH(ROWLOCK)
134 SET DocBaseTexto = CAST(REPLACE(REPLACE(REPLACE(CAST(DocBaseTexto AS nvarchar(max)), @SourceURL + N'Home/', @NewURL), @SourceURL + N'AP/', @NewURL), @SourceURL, @TargetURL) AS ntext)
135 ELSE
136 UPDATE AP__DocumentosBase WITH(ROWLOCK)
137 SET DocBaseTexto = CAST(REPLACE(CAST(DocBaseTexto AS nvarchar(max)), @SourceURL, @NewURL) AS ntext)
138
139 IF(@@Error > 0)
140 INSERT INTO #tmpErrors ([Message]) VALUES ('Error: Update AP__DocumentosBase')
141
142 IF(@ChangeToRelative = 1)
143 UPDATE AP__DocumentosBaseProcesos WITH(ROWLOCK)
144 SET DocBaseTexto = CAST(REPLACE(REPLACE(REPLACE(CAST(DocBaseTexto AS nvarchar(max)), @SourceURL + N'Home/', @NewURL), @SourceURL + N'AP/', @NewURL), @SourceURL, @TargetURL) AS ntext)
145 ELSE
146 UPDATE AP__DocumentosBaseProcesos WITH(ROWLOCK)
147 SET DocBaseTexto = CAST(REPLACE(CAST(DocBaseTexto AS nvarchar(max)), @SourceURL, @NewURL) AS ntext)
148
149 IF(@@Error > 0)
150 INSERT INTO #tmpErrors ([Message]) VALUES ('Error: Update AP__DocumentosBaseProcesos')
151
152 IF(@ChangeToRelative = 1)
153 UPDATE AP__BPM_ParrafoRepetible WITH(ROWLOCK)
154 SET Contenido = CAST(REPLACE(REPLACE(REPLACE(CAST(Contenido AS nvarchar(max)), @SourceURL + N'Home/', @NewURL), @SourceURL + N'AP/', @NewURL), @SourceURL, @TargetURL) AS ntext)
155 ELSE
156 UPDATE AP__BPM_ParrafoRepetible WITH(ROWLOCK)
157 SET Contenido = CAST(REPLACE(CAST(Contenido AS nvarchar(max)), @SourceURL, @NewURL) AS ntext)
158
159 IF(@@Error > 0)
160 INSERT INTO #tmpErrors ([Message]) VALUES ('Error: Update AP__BPM_ParrafoRepetible')
161
162--Navigation Buttons
163 IF(@ChangeToRelative = 1)
164 UPDATE AP__NavigationMenuItems WITH(ROWLOCK)
165 SET Navigation = CAST(REPLACE(REPLACE(REPLACE(CAST(Navigation AS nvarchar(max)), @SourceURL + N'Home/', @NewURL), @SourceURL + N'AP/', @NewURL), @SourceURL, @TargetURL) AS ntext)
166 WHERE NavigationType = 0 AND UrlType = 0
167 ELSE
168 UPDATE AP__NavigationMenuItems WITH(ROWLOCK)
169 SET Navigation = CAST(REPLACE(CAST(Navigation AS nvarchar(max)), @SourceURL, @NewURL) AS ntext)
170 WHERE NavigationType = 0 AND UrlType = 0
171
172 IF(@@Error > 0)
173 INSERT INTO #tmpErrors ([Message]) VALUES ('Error: Update AP__NavigationMenuItems')
174
175--Content HTML
176 IF(@ChangeToRelative = 1)
177 UPDATE AP__ContentManual WITH(ROWLOCK)
178 SET Content = CAST(REPLACE(REPLACE(REPLACE(CAST(Content AS nvarchar(max)), @SourceURL + N'Home/', @NewURL), @SourceURL + N'AP/', @NewURL), @SourceURL, @TargetURL) AS ntext)
179 ELSE
180 UPDATE AP__ContentManual WITH(ROWLOCK)
181 SET Content = CAST(REPLACE(CAST(Content AS nvarchar(max)), @SourceURL, @NewURL) AS ntext)
182
183 IF(@@Error > 0)
184 INSERT INTO #tmpErrors ([Message]) VALUES ('Error: Update AP__ContentManual')
185
186-- Content Plugin
187 IF(@ChangeToRelative = 1)
188 UPDATE AP__ContentPlugin WITH(ROWLOCK)
189 SET Content = CAST(REPLACE(REPLACE(REPLACE(CAST(Content AS nvarchar(max)), @SourceURL + N'Home/', @NewURL), @SourceURL + N'AP/', @NewURL), @SourceURL, @TargetURL) AS ntext)
190 ELSE
191 UPDATE AP__ContentPlugin WITH(ROWLOCK)
192 SET Content = CAST(REPLACE(CAST(Content AS nvarchar(max)), @SourceURL, @NewURL) AS ntext)
193
194 IF(@@Error > 0)
195 INSERT INTO #tmpErrors ([Message]) VALUES ('Error: Update AP__ContentPlugin')
196
197--External Page
198 IF(@ChangeToRelative = 1)
199 UPDATE AP__ContentWebPage WITH(ROWLOCK)
200 SET WebPage = CAST(REPLACE(REPLACE(REPLACE(CAST(WebPage AS nvarchar(max)), @SourceURL + N'Home/', @NewURL), @SourceURL + N'AP/', @NewURL), @SourceURL, @TargetURL) AS ntext)
201 WHERE UrlType = 0
202 ELSE
203 UPDATE AP__ContentWebPage WITH(ROWLOCK)
204 SET WebPage = CAST(REPLACE(CAST(WebPage AS nvarchar(max)), @SourceURL, @NewURL) AS ntext)
205 WHERE UrlType = 0
206
207 IF(@@Error > 0)
208 INSERT INTO #tmpErrors ([Message]) VALUES ('Error: Update AP__ContentWebPage')
209
210--Links
211 IF(@ChangeToRelative = 1)
212 UPDATE AP__Links WITH(ROWLOCK)
213 SET LinkPath = REPLACE(REPLACE(REPLACE(LinkPath, @SourceURL + N'Home/', @NewURL), @SourceURL + N'AP/', @NewURL), @SourceURL, @TargetURL)
214 WHERE LinkType = 0
215 ELSE
216 UPDATE AP__Links WITH(ROWLOCK)
217 SET LinkPath = REPLACE(LinkPath, @SourceURL, @NewURL)
218 WHERE LinkType = 0
219
220 IF(@@Error > 0)
221 INSERT INTO #tmpErrors ([Message]) VALUES ('Error: Update AP__Links')
222
223--MultiLanguage
224 IF(((@ChangeToRelative = 1) and (@TargetURL <> '')) or (@ChangeToRelative = 0))--Notificaciones que hacen envio de email
225 begin
226
227 UPDATE AP__Multiidioma WITH(ROWLOCK)
228 SET Descripcion = CAST(REPLACE(CAST(Descripcion AS nvarchar(max)), @SourceURL, @TargetURL) AS ntext)
229 WHERE ((TipoTermino in (37,36)) and (IdTerminoCampo in (select ID from AP__BPM_TareasSistClases with(nolock) where IdFuncion in (8,5,31,24))))
230
231
232 IF(@@Error > 0)
233 INSERT INTO #tmpErrors ([Message]) VALUES ('Error: Update AP__Multiidioma (Notify via Email)')
234
235 end
236
237 IF(@ChangeToRelative = 1)
238 begin
239 UPDATE AP__Multiidioma WITH(ROWLOCK)
240 SET Descripcion = CAST(REPLACE(REPLACE(REPLACE(CAST(Descripcion AS nvarchar(max)), @SourceURL + N'Home/', @NewURL), @SourceURL + N'AP/', @NewURL), @SourceURL, @TargetURL) AS ntext)
241 WHERE TipoTermino NOT IN (36,37)
242
243 UPDATE AP__Multiidioma WITH(ROWLOCK)
244 SET Descripcion = CAST(REPLACE(REPLACE(REPLACE(CAST(Descripcion AS nvarchar(max)), @SourceURL + N'Home/', @NewURL), @SourceURL + N'AP/', @NewURL), @SourceURL, @TargetURL) AS ntext)
245 WHERE (TipoTermino in (37,36)) and (IdTerminoCampo in (select ID from AP__BPM_TareasSistClases with(nolock) where IdFuncion in (1,3)))--Notificación empleado y externo
246
247 end
248 ELSE
249 begin
250 UPDATE AP__Multiidioma WITH(ROWLOCK)
251 SET Descripcion = CAST(REPLACE(CAST(Descripcion AS nvarchar(max)), @SourceURL, @NewURL) AS ntext)
252 WHERE TipoTermino NOT IN (36,37)
253
254 UPDATE AP__Multiidioma WITH(ROWLOCK)
255 SET Descripcion = CAST(REPLACE(CAST(Descripcion AS nvarchar(max)), @SourceURL, @NewURL) AS ntext)
256 WHERE (TipoTermino in (37,36)) and (IdTerminoCampo in (select ID from AP__BPM_TareasSistClases with(nolock) where IdFuncion in (1,3)))--Notificación empleado y externo
257 end
258
259 IF(@@Error > 0)
260 INSERT INTO #tmpErrors ([Message]) VALUES ('Error: Update AP__Multiidioma')
261
262--External Query
263 IF(@ChangeToRelative = 1)
264 UPDATE AP__LineaConsulta WITH(ROWLOCK)
265 SET Valor = CAST(REPLACE(REPLACE(REPLACE(CAST(Valor AS nvarchar(max)), @SourceURL + N'Home/', @NewURL), @SourceURL + 'AP/', @NewURL), @SourceURL, @TargetURL) AS ntext)
266 FROM AP__CabeceraConsulta Con WITH(NOLOCK)
267 WHERE Con.ID = AP__LineaConsulta.IdCabecera
268 AND Con.PaginaWeb = 10
269 ELSE
270 UPDATE AP__LineaConsulta WITH(ROWLOCK)
271 SET Valor = CAST(REPLACE(CAST(Valor AS nvarchar(max)), @SourceURL, @TargetURL) AS ntext)
272 FROM AP__CabeceraConsulta Con WITH(NOLOCK)
273 WHERE Con.ID = AP__LineaConsulta.IdCabecera
274 AND Con.PaginaWeb = 10
275
276 IF(@@Error > 0)
277 INSERT INTO #tmpErrors ([Message]) VALUES ('Error: Update AP__LineaConsulta')
278
279
280--Update Value multiLine Rich
281 DECLARE @FieldName AS NVARCHAR(255)
282 DECLARE @TableName AS NVARCHAR(255)
283 DECLARE @RealTableName AS NVARCHAR(255)
284 DECLARE @OverFlow AS INT
285 DECLARE @SQL AS NVARCHAR(MAX)
286
287
288 DECLARE cursor_field CURSOR FOR
289
290 SELECT cmp.NombreInterno, cmp.NombreTabla, cmp.IdTablaOverflow
291 FROM AP__CamposTabla cmp WITH(NOLOCK)
292 WHERE cmp.TipoCampo = 19
293
294 OPEN cursor_field
295
296 FETCH NEXT FROM cursor_field
297 INTO @FieldName, @TableName, @OverFlow
298
299 WHILE @@FETCH_STATUS = 0
300 BEGIN
301
302 IF(@OverFlow > 0)
303 SET @RealTableName = @TableName + N'_o' + CAST(@OverFlow AS NVARCHAR(5))
304 ELSE
305 SET @RealTableName = @TableName
306
307 IF EXISTS(SELECT * FROM dbo.syscolumns WHERE Name = @FieldName and ID = Object_ID(@RealTableName))
308 BEGIN
309 IF(@ChangeToRelative = 1)
310 SET @SQL = N'UPDATE [' + @RealTableName + N'] WITH(ROWLOCK) SET [' + @FieldName + N'] = CAST(REPLACE(REPLACE(REPLACE(CAST([' + @FieldName
311 + N'] AS nvarchar(max)), ''' + @SourceURL + N'Home/'', ''' + @NewURL + N'''), ''' + @SourceURL + N'AP/'', ''' + @NewURL + N'''), ''' + @SourceURL + N''', '''
312 + @TargetURL + ''') AS ntext)'
313 ELSE
314 SET @SQL = N'UPDATE [' + @RealTableName + N'] WITH(ROWLOCK) SET [' + @FieldName + N'] = CAST(REPLACE(CAST([' + @FieldName + N'] AS nvarchar(max)), ''' + @SourceURL
315 + N''', ''' + @NewURL + N''') AS ntext)'
316
317 BEGIN TRY
318 EXECUTE sp_executesql @SQL
319 END TRY
320 BEGIN CATCH
321 INSERT INTO #tmpErrors ([Message]) VALUES ('Error: Update '+@RealTableName)
322 END CATCH
323 END
324
325 FETCH NEXT FROM cursor_field
326 INTO @FieldName, @TableName, @OverFlow
327
328 END
329 CLOSE cursor_field
330 DEALLOCATE cursor_field
331
332IF EXISTS (SELECT * FROM tempdb..sysobjects WHERE id=OBJECT_ID('tempdb..#tmpErrors'))
333BEGIN
334 IF EXISTS (SELECT * FROM #tmpErrors)
335 BEGIN
336 SELECT * FROM #tmpErrors
337 ROLLBACK TRANSACTION
338 PRINT 'DataBase update failed'
339 END
340 ELSE
341 BEGIN
342 INSERT INTO #tmpErrors ([Message]) VALUES ('DataBase update succeeded')
343 SELECT * FROM #tmpErrors
344 COMMIT TRANSACTION
345 PRINT 'DataBase update succeeded'
346 END
347 DROP TABLE #tmpErrors
348END