· 8 years ago · May 29, 2018, 07:12 AM
1/*
2Run this script on:
3
4 PCJP\SQLEXPRESS.AdFortuna_Mobile_Celcio_Luc - This database will be modified
5
6to synchronize it with:
7
8 PCJP\SQLEXPRESS.AdFortuna_Mobile
9
10You are recommended to back up your database before running this script
11
12Script created by SQL Compare version 9.0.0 from Red Gate Software Ltd at 19/01/2012 13:25:53
13
14*/
15SET NUMERIC_ROUNDABORT OFF
16GO
17SET ANSI_PADDING, ANSI_WARNINGS, CONCAT_NULL_YIELDS_NULL, ARITHABORT, QUOTED_IDENTIFIER, ANSI_NULLS ON
18GO
19IF EXISTS (SELECT * FROM tempdb..sysobjects WHERE id=OBJECT_ID('tempdb..#tmpErrors')) DROP TABLE #tmpErrors
20GO
21CREATE TABLE #tmpErrors (Error int)
22GO
23SET XACT_ABORT ON
24GO
25SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
26GO
27BEGIN TRANSACTION
28GO
29PRINT N'Dropping constraints from [dbo].[Mobile$MegaTrade Setup]'
30GO
31ALTER TABLE [dbo].[Mobile$MegaTrade Setup] DROP CONSTRAINT [Mobile$MegaTradeSetup_JobToDoTicketCategory_DEFAULT]
32GO
33IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
34GO
35IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
36GO
37PRINT N'Dropping constraints from [dbo].[Mobile$Ticket]'
38GO
39ALTER TABLE [dbo].[Mobile$Ticket] DROP CONSTRAINT [Mobile$Ticket_SearchDescription_DEFAULT]
40GO
41IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
42GO
43IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
44GO
45PRINT N'Dropping constraints from [dbo].[Mobile$Ticket]'
46GO
47ALTER TABLE [dbo].[Mobile$Ticket] DROP CONSTRAINT [Mobile$Ticket_AssignedResourceNo__DEFAULT]
48GO
49IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
50GO
51IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
52GO
53PRINT N'Altering [dbo].[Mobile$Ticket]'
54GO
55ALTER TABLE [dbo].[Mobile$Ticket] ALTER COLUMN [Search Description] [varchar] (250) COLLATE Latin1_General_CI_AI NOT NULL
56ALTER TABLE [dbo].[Mobile$Ticket] ALTER COLUMN [Assigned Resource No_] [varchar] (20) COLLATE Latin1_General_CI_AI NOT NULL
57GO
58IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
59GO
60IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
61GO
62PRINT N'Altering [dbo].[Mobile$MegaTrade Setup]'
63GO
64ALTER TABLE [dbo].[Mobile$MegaTrade Setup] ALTER COLUMN [Job To Do Ticket Category] [varchar] (20) COLLATE Latin1_General_CI_AI NOT NULL
65GO
66IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
67GO
68IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
69GO
70PRINT N'Altering [dbo].[fn_Tickets]'
71GO
72
73ALTER FUNCTION [dbo].[fn_Tickets]
74(
75 @ShipToContactNo nvarchar(20),
76 @ServiceItemNo nvarchar(20),
77 @ServiceHeaderNo_ nvarchar(20),
78 @Type int
79)
80
81RETURNS TABLE
82AS
83RETURN
84(
85 ---- Toegewezen aan bon
86 ---- Type = 1
87 SELECT
88 *
89 FROM [dbo].[Mobile$Ticket]
90 WHERE
91 (
92 [dbo].[Mobile$Ticket].[Entry No_]
93 IN (SELECT [Ticket No_]
94 FROM [dbo].[Mobile$Ticket Link]
95 WHERE [Service Order No_] = @ServiceHeaderNo_)
96 AND @Type = 1
97 )
98
99 OR
100
101 (
102 @Type = 2
103 AND [Finished] = 0
104
105 AND [Category Code] = (SELECT [Service Ticket Category] FROM [dbo].[Mobile$MegaTrade Setup])
106
107 AND [dbo].[Mobile$Ticket].[Ship-to Contact No_] = @ShipToContactNo
108 AND ([dbo].[Mobile$Ticket].[Service Item No_] = ''
109 OR
110 [dbo].[Mobile$Ticket].[Service Item No_] = @ServiceItemNo)
111 AND [dbo].[Mobile$Ticket].[Entry No_]
112 NOT IN (SELECT [Ticket No_]
113 FROM [dbo].[Mobile$Ticket Link]
114 WHERE [Service Order No_] = @ServiceHeaderNo_)
115 )
116
117 OR
118
119 (
120 @Type = 3
121 AND [dbo].[Mobile$Ticket].[Finished] = 0
122 AND [Category Code] = (SELECT [Job To Do Ticket Category] FROM [dbo].[Mobile$MegaTrade Setup])
123
124 AND [dbo].[Mobile$Ticket].[Ship-to Contact No_] = @ShipToContactNo
125 AND [dbo].[Mobile$Ticket].[Entry No_]
126 NOT IN (SELECT [Ticket No_]
127 FROM [dbo].[Mobile$Ticket Link]
128 WHERE [Service Order No_] = @ServiceHeaderNo_)
129 )
130
131)
132GO
133IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
134GO
135IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
136GO
137PRINT N'Altering [dbo].[fn_TicketsOverview]'
138GO
139-- =============================================
140-- Author: <Author,,Name>
141-- Create date: <Create Date,,>
142-- Description: <Description,,>
143-- =============================================
144ALTER FUNCTION [dbo].[fn_TicketsOverview]
145(
146 -- Add the parameters for the function here
147 @Param_ServiceItemNo_ nvarchar(20),
148 @Param_ShiptoContactNo_ nvarchar(20),
149 @Type int
150)
151RETURNS @JTD TABLE
152(
153 -- Lines
154 JTDL_Description nvarchar(150),
155 JTDL_InternalRemark nvarchar(250),
156 JTDL_ExternalRemark nvarchar(250),
157 JTDL_Ok bit
158
159 -- Framework
160 --ID int PRIMARY KEY
161 --CreationDate
162)
163AS
164BEGIN
165
166DECLARE @JTD_Head nvarchar(150)
167 -- Service Item
168DECLARE @SI_No nvarchar(20)
169DECLARE @SI_Description nvarchar(150)
170 -- JTDL
171DECLARE @JTDL_Description nvarchar(150)
172DECLARE @JTDL_InternalRemark nvarchar(250)
173DECLARE @JTDL_ExternalRemark nvarchar(250)
174DECLARE @JTDL_Finished bit
175
176
177 -- JTDL ----------------------------
178 DECLARE rsJTDL CURSOR LOCAL FAST_FORWARD FOR
179 SELECT
180 [Description]
181 ,[Internal Remark]
182 ,[External Remark]
183 ,[Finished]
184 FROM [dbo].[Mobile$Ticket] JTDL
185 WHERE (
186 @Type = 1
187 AND [Category Code] = (SELECT [Service Ticket Category] FROM [dbo].[Mobile$MegaTrade Setup])
188 AND JTDL.[Service Item No_] = @Param_ServiceItemNo_
189 AND JTDL.[Ship-to Contact No_] = @Param_ShiptoContactNo_
190 )
191 OR (
192 @Type = 2
193 AND [Category Code] = (SELECT [Job To Do Ticket Category] FROM [dbo].[Mobile$MegaTrade Setup])
194 AND [Ship-to Contact No_] = @Param_ShiptoContactNo_
195 )
196
197 OPEN rsJTDL
198
199 FETCH NEXT FROM rsJTDL INTO @JTDL_Description, @JTDL_InternalRemark, @JTDL_ExternalRemark, @JTDL_Finished
200 WHILE @@FETCH_STATUS = 0 BEGIN
201
202 INSERT INTO @JTD(JTDL_Description, JTDL_InternalRemark, JTDL_ExternalRemark, JTDL_Ok)
203 VALUES(@JTDL_Description, @JTDL_InternalRemark, @JTDL_ExternalRemark, @JTDL_Finished)
204
205 FETCH NEXT FROM rsJTDL INTO @JTDL_Description, @JTDL_InternalRemark, @JTDL_ExternalRemark, @JTDL_Finished END
206 CLOSE rsJTDL
207 DEALLOCATE rsJTDL
208
209 -------------------------------
210
211--DECLARE rsJTDW CURSOR LOCAL FAST_FORWARD FOR
212
213-- SELECT
214-- [Description]
215-- ,[Internal Remark]
216-- ,[External Remark]
217-- ,[Finished]
218-- FROM [dbo].[Mobile$Job To Do Line] JTDL
219-- WHERE JTDL.[Ship-to Contact No_] = @Param_ShipToContactNo
220-- AND JTDL.[Service Item No_] = ''
221
222-- OPEN rsJTDW
223
224-- FETCH NEXT FROM rsJTDW INTO @JTDL_Description, @JTDL_InternalRemark, @JTDL_ExternalRemark, @JTDL_Finished
225-- WHILE @@FETCH_STATUS = 0 BEGIN
226
227-- SET @JTD_Head = 'WERF'
228
229-- INSERT INTO @JTD(JTD_Head, JTDL_Description, JTDL_InternalRemark, JTDL_ExternalRemark, JTDL_Ok)
230-- VALUES(@JTD_Head, @JTDL_Description, @JTDL_InternalRemark, @JTDL_ExternalRemark, @JTDL_Finished)
231
232-- FETCH NEXT FROM rsJTDW INTO @JTDL_Description, @JTDL_InternalRemark, @JTDL_ExternalRemark, @JTDL_Finished END
233-- CLOSE rsJTDW
234-- DEALLOCATE rsJTDW
235
236RETURN
237END
238GO
239IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
240GO
241IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
242GO
243PRINT N'Altering [dbo].[fn_TicketsProblemsOverView]'
244GO
245-- =============================================
246-- Author: <Author,,Name>
247-- Create date: <Create Date,,>
248-- Description: <Description,,>
249-- =============================================
250ALTER FUNCTION [dbo].[fn_TicketsProblemsOverView]
251(
252 @Param_ServiceItemNo_ nvarchar(20),
253 @Param_ShiptoContactNo_ nvarchar(20),
254 @Type int
255)
256RETURNS @TicketsProblems TABLE
257(
258 -- Service Header
259 TP_Head nvarchar(250),
260 TP_Type nvarchar(150),
261 TP_Description nvarchar(250),
262 TP_Ok tinyint
263 -- Framework
264 --ID int PRIMARY KEY
265)
266AS
267BEGIN
268
269 -- Melding
270 DECLARE @TP_Date datetime
271 DECLARE @TP_Head nvarchar(50)
272 DECLARE @TP_Melding nvarchar(250)
273 DECLARE @TP_EntryNo bigint
274 DECLARE @TP_Type nvarchar(50)
275 DECLARE @TP_Ok tinyint
276 DECLARE @TP_Internal nvarchar(250)
277 DECLARE @TP_External nvarchar(250)
278
279 DECLARE @TCL_Description nvarchar(250)
280
281 DECLARE @ProblemCode nvarchar(250)
282 DECLARE @CauseCode nvarchar(250)
283 DECLARE @SolutionCode nvarchar(250)
284
285 DECLARE @ProblemDescription nvarchar(250)
286 DECLARE @CauseDescription nvarchar(250)
287 DECLARE @SolutionDescription nvarchar(250)
288
289 DECLARE @ID int
290
291 SET @ID = 1
292
293 -- Melding
294 DECLARE rsMelding CURSOR LOCAL FAST_FORWARD FOR
295
296 SELECT
297 [Creation Date],
298 [Description],
299 [Entry No_],
300 [Internal Remark],
301 [External Remark],
302 [Problem Code],
303 [Problem Cause Code],
304 [Problem Solution Code],
305 [Finished]
306 FROM [dbo].[Mobile$Ticket] T
307 WHERE (
308 @Type = 1
309 AND [Category Code] = (SELECT [Service Ticket Category] FROM [dbo].[Mobile$MegaTrade Setup])
310 AND [Service Item No_] = @Param_ServiceItemNo_
311 AND [Ship-to Contact No_] = @Param_ShiptoContactNo_
312 )
313 OR (
314 @Type = 2
315 AND [Category Code] = (SELECT [Job To Do Ticket Category] FROM [dbo].[Mobile$MegaTrade Setup])
316 AND [Ship-to Contact No_] = @Param_ShiptoContactNo_
317 )
318
319 OPEN rsMelding
320
321 FETCH NEXT FROM rsMelding INTO @TP_Date, @TP_Melding, @TP_EntryNo, @TP_Internal, @TP_External, @ProblemCode, @CauseCode, @SolutionCode, @TP_Ok
322 WHILE @@FETCH_STATUS = 0 BEGIN
323
324 SET @TP_Head = CAST(CAST(@TP_Date AS DATE) AS NVARCHAR) + ' | ' + CAST(@TP_EntryNo AS NVARCHAR) + ' | ' + ISNULL(@TP_Melding, '')
325
326 -- Melding
327 --
328 INSERT INTO @TicketsProblems(TP_Head, TP_Type, TP_Description, TP_Ok)
329 VALUES(@TP_Head, 'A) Melding: ' , '=> ' + ISNULL(@TP_Melding, ''), @TP_Ok)
330 --
331 DECLARE rsTCL CURSOR LOCAL FAST_FORWARD FOR
332 SELECT [Description]
333 FROM [dbo].[Mobile$Ticket Comment Line] TCL
334 WHERE TCL.[Ticket No_] = @TP_EntryNo
335 AND TCL.[Type] = 0
336
337 OPEN rsTCL
338
339
340 FETCH NEXT FROM rsTCL INTO @TCL_Description
341 WHILE @@FETCH_STATUS = 0 BEGIN
342
343 INSERT INTO @TicketsProblems(TP_Head, TP_Type, TP_Description, TP_Ok)
344 --VALUES(@TP_Head, 'A) Melding: ' + ISNULL(@TP_Melding, ''), '- ' + @TCL_Description, @TP_Ok)
345 VALUES(@TP_Head, 'A) Melding: ' , '- ' + @TCL_Description, @TP_Ok)
346
347 FETCH NEXT FROM rsTCL INTO @TCL_Description
348 END
349 CLOSE rsTCL
350 DEALLOCATE rsTCL
351
352 -- Intern
353 --
354 INSERT INTO @TicketsProblems(TP_Head, TP_Type, TP_Description, TP_Ok)
355 VALUES(@TP_Head, 'B) Interne opmerking: ' , '=> '+ ISNULL(@TP_Internal, ''), @TP_Ok)
356 --
357 DECLARE rsTCL CURSOR LOCAL FAST_FORWARD FOR
358 SELECT [Description]
359 FROM [dbo].[Mobile$Ticket Comment Line] TCL
360 WHERE TCL.[Ticket No_] = @TP_EntryNo
361 AND TCL.[Type] = 1
362
363 OPEN rsTCL
364
365 FETCH NEXT FROM rsTCL INTO @TCL_Description
366 WHILE @@FETCH_STATUS = 0 BEGIN
367
368 INSERT INTO @TicketsProblems(TP_Head, TP_Type, TP_Description, TP_Ok)
369 --VALUES(@TP_Head, 'B) Interne opmerking: ' + ISNULL(@TP_Internal, '') , '- ' + ISNULL(@TCL_Description, ''), @TP_Ok)
370 VALUES(@TP_Head, 'B) Interne opmerking: ' , '- ' + ISNULL(@TCL_Description, ''), @TP_Ok)
371
372 FETCH NEXT FROM rsTCL INTO @TCL_Description
373 END
374 CLOSE rsTCL
375 DEALLOCATE rsTCL
376
377 -- Extern
378 --
379 INSERT INTO @TicketsProblems(TP_Head, TP_Type, TP_Description, TP_Ok)
380 VALUES(@TP_Head, 'D) Bevindingen: ' , '=> '+ ISNULL(@TP_External, ''), @TP_Ok)
381 --
382 DECLARE rsTCL CURSOR LOCAL FAST_FORWARD FOR
383 SELECT [Description]
384 FROM [dbo].[Mobile$Ticket Comment Line] TCL
385 WHERE TCL.[Ticket No_] = @TP_EntryNo
386 AND TCL.[Type] = 2
387
388 OPEN rsTCL
389
390 FETCH NEXT FROM rsTCL INTO @TCL_Description
391 WHILE @@FETCH_STATUS = 0 BEGIN
392
393 INSERT INTO @TicketsProblems(TP_Head, TP_Type, TP_Description, TP_Ok)
394 --VALUES(@TP_Head, 'D) Bevindingen: ' + ISNULL(@TP_External, ''), '- ' + @TCL_Description, @TP_Ok)
395 VALUES(@TP_Head, 'D) Bevindingen: ' , '- ' + @TCL_Description, @TP_Ok)
396
397 FETCH NEXT FROM rsTCL INTO @TCL_Description
398 END
399 CLOSE rsTCL
400 DEALLOCATE rsTCL
401
402 SELECT @ProblemDescription = [Description]
403 FROM [dbo].[Mobile$Service Problem]
404 WHERE [Code] = @ProblemCode
405
406 INSERT INTO @TicketsProblems(TP_Head, TP_Type, TP_Description, TP_Ok)
407 VALUES(@TP_Head, 'C) Oplossing: ' , 'Probleem: ' + ISNULL(@ProblemDescription, ''), @TP_Ok)
408
409 SELECT @CauseDescription = [Description]
410 FROM [dbo].[Mobile$Service Problem]
411 WHERE [Code] = @CauseCode
412
413 INSERT INTO @TicketsProblems(TP_Head, TP_Type, TP_Description, TP_Ok)
414 VALUES(@TP_Head, 'C) Oplossing: ' , 'Oorzaak: ' + ISNULL(@CauseDescription, ''), @TP_Ok)
415
416 SELECT @SolutionDescription = [Description]
417 FROM [dbo].[Mobile$Service Problem]
418 WHERE [Code] = @SolutionCode
419
420 INSERT INTO @TicketsProblems(TP_Head, TP_Type, TP_Description, TP_Ok)
421 VALUES(@TP_Head, 'C) Oplossing: ' , 'Oplossing: ' + ISNULL(@SolutionDescription, ''), @TP_Ok)
422
423
424
425 -- Probleem
426 --SELECT @TP_External = [External Remark]
427 --FROM [dbo].[Mobile$Ticket] T
428 --WHERE [Entry No_] = @TP_EntryNo
429
430 --INSERT INTO @TicketsProblems(TP_Head, TP_Type, TP_Description)
431 --VALUES(@TP_Melding, 'Probleem', @TP_External)
432
433
434
435 FETCH NEXT FROM rsMelding INTO @TP_Date, @TP_Melding, @TP_EntryNo, @TP_Internal, @TP_External, @ProblemCode, @CauseCode, @SolutionCode, @TP_Ok
436 END
437 CLOSE rsMelding
438 DEALLOCATE rsMelding
439
440 RETURN
441END
442GO
443IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
444GO
445IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
446GO
447PRINT N'Adding constraints to [dbo].[Mobile$MegaTrade Setup]'
448GO
449ALTER TABLE [dbo].[Mobile$MegaTrade Setup] ADD CONSTRAINT [JobToDoTicketCategory_DEFAULT] DEFAULT ('') FOR [Job To Do Ticket Category]
450GO
451IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
452GO
453IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
454GO
455PRINT N'Adding constraints to [dbo].[Mobile$Ticket]'
456GO
457ALTER TABLE [dbo].[Mobile$Ticket] ADD CONSTRAINT [SearchDescription_DEFAULT] DEFAULT ('') FOR [Search Description]
458GO
459IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
460GO
461IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
462GO
463ALTER TABLE [dbo].[Mobile$Ticket] ADD CONSTRAINT [AssignedResourceNo__DEFAULT] DEFAULT ('') FOR [Assigned Resource No_]
464GO
465IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
466GO
467IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
468GO
469IF EXISTS (SELECT * FROM #tmpErrors) ROLLBACK TRANSACTION
470GO
471IF @@TRANCOUNT>0 BEGIN
472PRINT 'The database update succeeded'
473COMMIT TRANSACTION
474END
475ELSE PRINT 'The database update failed'
476GO
477DROP TABLE #tmpErrors
478GO