· 6 years ago · Aug 01, 2019, 09:32 AM
1USE [Lime_test]
2GO
3
4/****** Object: StoredProcedure [dbo].[csp_CL_addHistory] Script Date: 2019-08-01 11:28:20 ******/
5SET ANSI_NULLS ON
6GO
7
8SET QUOTED_IDENTIFIER ON
9GO
10
11
12
13
14
15
16
17-- =============================================
18-- Author: Sofie Helgesson
19-- Create date: 2016-10-06
20-- Description:
21-- =============================================
22CREATE PROCEDURE [dbo].[csp_CL_addHistory]
23 @idhelpdesk INT,
24 --@idchecklistactivity INT,
25 @note NVARCHAR(MAX),
26 @iduser INT
27AS
28BEGIN
29
30 -- FLAG_EXTERNALACCESS --
31
32 DECLARE @type AS INT
33 DECLARE @idcoworker AS INT
34
35 SELECT @type = idstring FROM string WHERE [key] = 'checklistaction'
36 SELECT @idcoworker = idcoworker FROM coworker WHERE username = @iduser
37
38
39 INSERT INTO history ([status], createduser, updateduser, [type], coworker, note, helpdesk)
40 VALUES(0, @iduser, @iduser, @type, @idcoworker, @note, @idhelpdesk)
41
42END
43
44
45
46
47
48
49GO
50
51/****** Object: StoredProcedure [dbo].[csp_CL_cleanupchecklistactivities] Script Date: 2019-08-01 11:28:20 ******/
52SET ANSI_NULLS ON
53GO
54
55SET QUOTED_IDENTIFIER ON
56GO
57
58
59
60
61
62
63CREATE PROCEDURE [dbo].[csp_CL_cleanupchecklistactivities]
64 @@idhelpdesktype INTEGER
65AS
66BEGIN
67
68 -- FLAG_EXTERNALACCESS --
69
70 EXEC csp_CL_listorder @@idhelpdesktype
71
72 DECLARE @table TABLE(
73 [row] INT IDENTITY(1,1),
74 [idnextstep] INT
75 )
76 INSERT INTO @table (idnextstep)
77 SELECT idnextstep
78 FROM checklistactivity l
79 LEFT JOIN nextstep n
80 ON l.idchecklistactivity = n.nextcheckliststep
81 WHERE l.[status] = 0
82 AND l.helpdesktype = @@idhelpdesktype
83 AND n.idnextstep IS NOT NULL
84
85
86 DECLARE @i AS INTEGER
87 DECLARE @idnextstep AS INTEGER
88 DECLARE @counter AS INTEGER
89 SET @i = 1
90
91 WHILE @i <= (SELECT COUNT(*) FROM @table)
92 BEGIN
93
94 SELECT @idnextstep = idnextstep FROM @table
95 WHERE [ROW] = @i
96
97 SET @counter = 0
98
99 SELECT @counter = COUNT(*) FROM nextstep WHERE checklistactivity = (SELECT checklistactivity FROM nextstep WHERE idnextstep = @idnextstep and [status] = 0)
100 print cast(@counter AS NVARCHAR(32)) + ' -- ' + cast(@idnextstep as nvarchar(32))
101 IF (@counter > 1 )
102 BEGIN
103 UPDATE checklistactivity SET hasoptions = 1 WHERE idchecklistactivity = (SELECT checklistactivity FROM nextstep WHERE idnextstep = @idnextstep)
104 END
105 ELSE
106 BEGIN
107 UPDATE checklistactivity SET hasoptions = 0 WHERE idchecklistactivity = (SELECT checklistactivity FROM nextstep WHERE idnextstep = @idnextstep)
108 END
109
110
111 SET @i = @i + 1
112 END
113
114END
115
116
117
118
119
120GO
121
122/****** Object: StoredProcedure [dbo].[csp_CL_clearChecklist] Script Date: 2019-08-01 11:28:20 ******/
123SET ANSI_NULLS ON
124GO
125
126SET QUOTED_IDENTIFIER ON
127GO
128
129
130
131
132
133-- =============================================
134-- Author: Andreas Åström
135-- Create date: 2014-07-10
136-- Description: Används för att "backa" checklistan.
137-- =============================================
138CREATE PROCEDURE [dbo].[csp_CL_clearChecklist]
139 @@idchecklist int
140 ,@@xml as nvarchar(4000) OUTPUT
141AS
142BEGIN
143
144 -- FLAG_EXTERNALACCESS --
145
146 declare @count int
147 declare @i int
148 declare @xml as xml
149
150 set @i = 1
151
152
153 declare @temp table
154 (row int identity(1,1),
155 id int
156 )
157
158 insert into @temp (id)
159 select n.nextcheckliststep
160 from checklistactivity c
161 left join nextstep n
162 on c.idchecklistactivity = n.checklistactivity
163 where c.idchecklistactivity = @@idchecklist
164
165 set @count = (select count(*) id from @temp)
166
167 if @count > 0
168 begin
169 while (1 = 1 )
170 begin
171 insert into @temp (id)
172 select n.nextcheckliststep
173 from checklistactivity c
174 left join nextstep n
175 on c.idchecklistactivity = n.checklistactivity
176 where c.idchecklistactivity = (select id from @temp where [row] = @i)
177
178 if @i = 1000
179 begin
180 break
181 end
182 set @i = @i + 1
183 end
184 end
185
186 delete @temp
187 where id is null
188
189 set @xml = (
190 select distinct id from @temp
191 for xml path('activity'), ROOT('activities'))
192
193 set @@xml = (case when(cast(@xml as nvarchar(max))) is null then '' else (cast(@xml as nvarchar(max))) end)
194
195END
196
197
198
199
200
201GO
202
203/****** Object: StoredProcedure [dbo].[csp_CL_createVisibleCLactivities] Script Date: 2019-08-01 11:28:20 ******/
204SET ANSI_NULLS ON
205GO
206
207SET QUOTED_IDENTIFIER ON
208GO
209
210
211
212
213
214
215-- =============================================
216-- Author: Sofie Helgesson
217-- Create date: 2016-04-26
218-- Description: Uses the Checklist XMl to create visible activites in the table soschecklistactivities
219-- =============================================
220CREATE PROCEDURE [dbo].[csp_CL_createVisibleCLactivities]
221 @@idhelpdesk AS INT
222AS
223BEGIN
224
225 DECLARE @xml AS XML
226 SELECT @xml = REPLACE([xml], '&', '&') FROM helpdesk WHERE idhelpdesk = @@idhelpdesk
227
228 -- FLAG_EXTERNALACCESS --
229
230
231 -- Create a table and fill it with the checklistactivities from XML
232 DECLARE @temptable TABLE
233 (
234 [idchecklistactivity] INT,
235 [description] NVARCHAR(128),
236 [visible] INT,
237 [isvisible] INT,
238 [option] INT,
239 [done] INT
240 )
241
242 INSERT INTO @temptable ([idchecklistactivity], [description], [visible], [isvisible], [option], done)
243 SELECT [table].[column].value('idchecklistactivity[1]', 'int')
244 ,[table].[column].value('description[1]', 'nvarchar(128)')
245 ,[table].[column].value('visible[1]', 'int')
246 ,[table].[column].value('isVisible[1]', 'int')
247 ,[table].[column].value('option[1]', 'int')
248 ,[table].[column].value('done[1]', 'int')
249 FROM @xml.nodes('/checklistactivitys/checklistactivity') AS [table]([column])
250
251
252 ;WITH cte_create ([idchecklistactivity], [description], [position], [office], [done])
253 AS
254 (
255 SELECT t.[idchecklistactivity], t.[description], c.position, c.office, t.[done]
256 FROM @temptable t
257 INNER JOIN checklistactivity c ON c.idchecklistactivity = t.idchecklistactivity
258 WHERE (t.visible = 1 or t.isvisible = 1) AND [option] = 0
259 AND t.idchecklistactivity NOT IN (SELECT checklistactivity FROM soschecklistactivity WHERE helpdesk = @@idhelpdesk AND status = 0)
260 )
261
262 INSERT INTO soschecklistactivity ([status], createdtime, createduser, helpdesk, checklistactivity, [description], position, office, done)
263 SELECT 0, getdate(), 1, @@idhelpdesk, idchecklistactivity, [description], position, office, done FROM cte_create
264
265 DELETE soschecklistactivity
266 WHERE helpdesk = @@idhelpdesk
267 AND checklistactivity IN (SELECT idchecklistactivity FROM @temptable WHERE (visible = 0))-- and isvisible = 0))
268
269
270END
271
272
273
274
275
276
277GO
278
279/****** Object: StoredProcedure [dbo].[csp_CL_deletechecklist] Script Date: 2019-08-01 11:28:20 ******/
280SET ANSI_NULLS ON
281GO
282
283SET QUOTED_IDENTIFIER ON
284GO
285
286
287
288
289
290-- =============================================
291-- Author: <Author,,Name>
292-- Create date: <Create Date,,>
293-- Description: <Description,,>
294-- =============================================
295CREATE PROCEDURE [dbo].[csp_CL_deletechecklist]
296 @@idhelpdesk INT
297AS
298BEGIN
299
300 -- FLAG_EXTERNALACCESS --
301
302 UPDATE helpdesk
303 SET xml = ''
304 WHERE idhelpdesk = @@idhelpdesk
305
306 --Nollställ soschecklistactivities
307 DELETE soschecklistactivity
308 WHERE helpdesk = @@idhelpdesk
309
310END
311
312
313
314
315
316GO
317
318/****** Object: StoredProcedure [dbo].[csp_CL_getchildnode] Script Date: 2019-08-01 11:28:20 ******/
319SET ANSI_NULLS ON
320GO
321
322SET QUOTED_IDENTIFIER ON
323GO
324
325
326
327
328
329-- =============================================
330-- Author: <Author,,Name>
331-- Create date: <Create Date,,>
332-- Description: <Description,,>
333-- =============================================
334CREATE PROCEDURE [dbo].[csp_CL_getchildnode]
335 -- Add the parameters for the stored procedure here
336 @@helpdesktype int,
337 @@idchecklistactivity int,
338 @@xml nvarchar(max) output
339
340AS
341BEGIN
342
343 -- FLAG_EXTERNALACCESS --
344
345 DECLARE @descriptipn AS NVARCHAR(64)
346 DECLARE @xml AS XML
347
348 SELECT @descriptipn = [description]
349 FROM checklistactivity
350 WHERE idchecklistactivity = @@idchecklistactivity
351
352 SET @xml = (
353 SELECT cl.description, cl.idchecklistactivity, '' as child FROM checklistactivity c
354 LEFT JOIN nextstep n
355 ON c.idchecklistactivity = n.checklistactivity
356 LEFT JOIN checklistactivity cl
357 ON n.nextcheckliststep = cl.idchecklistactivity
358 WHERE n.checklistactivity is not null
359 AND c.helpdesktype = @@helpdesktype
360 AND c.idchecklistactivity = @@idchecklistactivity
361 FOR XML PATH('option'))
362
363 SET @@xml = CASE WHEN @xml IS NULL THEN '' ELSE CAST(@xml AS NVARCHAR(MAX)) END
364
365END
366
367
368
369
370
371GO
372
373/****** Object: StoredProcedure [dbo].[csp_CL_getxmlstructureDynamic] Script Date: 2019-08-01 11:28:20 ******/
374SET ANSI_NULLS ON
375GO
376
377SET QUOTED_IDENTIFIER ON
378GO
379
380
381
382
383
384
385
386-- =============================================
387-- Author: <Author,,Name>
388-- Create date: <Create Date,,>
389-- Description: <Description,,>
390-- =============================================
391CREATE PROCEDURE [dbo].[csp_CL_getxmlstructureDynamic]
392 @@xml as nvarchar(max) output
393 ,@@idhelpdesk nvarchar(32)
394 ,@@activitytable nvarchar(32)
395 ,@@relationtable nvarchar(32)
396 ,@@description nvarchar(32)
397 ,@@nextstepRelation nvarchar(32)
398 ,@@checklistActivity nvarchar(32)
399 ,@@office nvarchar(32)
400AS
401BEGIN
402
403 -- FLAG_EXTERNALACCESS --
404
405 DECLARE @sql as NVARCHAR(MAX)
406 DECLARE @showall AS nvarchar(32)
407 DECLARE @positionoffirstoption as nvarchar(32)
408 DECLARE @result AS XML
409 DECLARE @xml AS XML
410
411 --DECLARE @@activitytable AS NVARCHAR(32)
412 --DECLARE @@relationtable AS NVARCHAR(32)
413 --DECLARE @@description AS NVARCHAR(32)
414 --DECLARE @@office AS NVARCHAR(32)
415 --DECLARE @@nextstepRelation AS NVARCHAR(32)
416 --DECLARE @@checklistActivity AS NVARCHAR(32)
417
418 --***********************************************************************
419 --************************ INSTÄLLNINGAR ********************************
420 --***********************************************************************
421 -- Här kan du ställa in vilka tabeller som ska användas för att samla
422 -- in data till checklistan.
423
424 --SET @@activitytable = N'checklistactivity' -- Aktivitetstabellen
425 --SET @@relationtable = N'nextstep' -- Tabellen som används för att länka samman aktiviteterna
426 --SET @@description = N'description' -- Är titeln på aktiviteten
427 --SET @@office = N'office' -- Tabellen som används för att länka samman avdelningar med aktiviteter
428 --SET @@nextstepRelation = N'nextcheckliststep' -- 'Används som steget man står på
429 --SET @@checklistActivity = N'checklistactivity' -- 'Används som nästa steg'
430
431 -------------------------------------------------------------------------
432 --***********************************************************************
433 --***********************************************************************
434
435 SELECT @showall = showall
436 FROM dbo.helpdesktype
437 WHERE idhelpdesktype = @@idhelpdesk
438 and [status] = 0
439
440 --AOC för att kunna markera som visible
441 SELECT top 1 @positionoffirstoption = position from checklistactivity
442 WHERE helpdesktype = @@idhelpdesk and hasoptions = 1 order by position asc
443 SET @positionoffirstoption = ISNULL(@positionoffirstoption,0)
444
445
446 SELECT @sql = (N'select @result = (
447 SELECT ce.id'+@@activitytable+',
448 ce.[hasoptions],
449 REPLACE(ce.['+@@description+'],'','',''.'') AS description,
450 0 AS done ,'''' as histories,
451 0 as [option],
452 ce.[document] + ce.[document_sevab] as document,
453 (Case WHEN ce.position = 1 then 1 when ce.position != 1 and '+ @showall + '= 1 and (' +@positionoffirstoption + ' = 0 or ce.position <= '+@positionoffirstoption+') then 1 else 0 end) as visible,
454 ce.runvba,'+
455 --ce.activateadvance,
456 +'ce.email + ce.email_sevab as email,
457 ce.externallink + ce.externallink_sevab as externallink ,
458 ce.comment,
459 ce.' + @@office + ' as office,
460 case when ce.' + @@office + ' is not null then (select ''['' + o.name + '']'' FROM ' + @@office + ' o where id' + @@office + ' = ce.' + @@office + ') else ''0'' end as descriptionoffice,
461 ce.reset_coworker as resetcoworker,
462 ce.cancelerrand, '+
463 --0 as mark,
464 +'0 as [ignore],
465 0 as [ongoing],
466 REPLACE(REPLACE(tooltip, CHAR(10), ''<br/>''), CHAR(13), ''<br/>'') as [tooltip],'
467 + @showall + ' as showall,'+
468 --, ce.deadlinedays
469 --OPTIONS
470 '(select
471 REPLACE(cl.['+@@description+'],'','',''.'') AS description,
472 cl.id'+@@activitytable+',0 as selected,
473 cl.[' + @@office + '] as office,'
474 --, isnull(cl.coworker,0) as coworker, cl.setowner
475 +'(case when
476 (select count(*) id'+@@activitytable+'
477 from '+@@relationtable+' ni
478 left join '+@@activitytable+' ci
479 on ni.'+@@nextstepRelation+' = ci.id'+@@activitytable+'
480 where ni.'+@@checklistActivity+' = cl.id'+@@activitytable+') > 1
481 then 0
482 else (
483 case when
484 (select ci.id'+@@activitytable+'
485 from '+@@relationtable+' ni
486 left join '+@@activitytable+' ci
487 on ni.'+@@nextstepRelation+' = ci.id'+@@activitytable+'
488 where ni.'+@@checklistActivity+' = cl.id'+@@activitytable+')
489 is null then
490 0 else (select ci.id'+@@activitytable+'
491 from '+@@relationtable+' ni
492 left join '+@@activitytable+' ci
493 on ni.'+@@nextstepRelation+' = ci.id'+@@activitytable+'
494 where ni.'+@@checklistActivity+' = cl.id'+@@activitytable+')
495 end)
496 end) as '+@@relationtable+',
497 REPLACE(ce.['+@@description+'],'','',''.'') AS parent,
498 case when cl.tooltip = '''' then cl.description else cl.tooltip end as tooltip
499 from ' + @@activitytable + ' c
500 left join '+@@relationtable+' n
501 on c.id'+@@activitytable+' = n.'+@@checklistActivity+'
502 left join ' + @@activitytable + ' cl
503 on n.'+@@nextstepRelation+' = cl.id'+@@activitytable+'
504 where n.'+@@checklistActivity+' is not null
505 and c.helpdesktype = ' + @@idhelpdesk + '
506 and c.id'+@@activitytable+' = ce.id'+@@activitytable+'
507 for xml path(''option''), elements, type) as options
508 from '+@@activitytable+' ce
509 INNER JOIN helpdesktype h on ce.helpdesktype = h.idhelpdesktype
510 where helpdesktype = ' + @@idhelpdesk + '
511 and ce.[status] = 0
512 order by ce.position
513 for xml path('''+@@activitytable+'''), ROOT('''+@@activitytable+'s''))')
514 print @sql
515 EXEC sp_executesql @sql, N'@result as xml OUTPUT', @result OUTPUT
516
517 SET @@xml = CASE WHEN @result IS NULL THEN '' ELSE CAST(@result AS NVARCHAR(MAX)) END
518
519END
520
521
522
523
524
525GO
526
527/****** Object: StoredProcedure [dbo].[csp_CL_getxmlTopstructure] Script Date: 2019-08-01 11:28:20 ******/
528SET ANSI_NULLS ON
529GO
530
531SET QUOTED_IDENTIFIER ON
532GO
533
534
535
536
537
538-- =============================================
539-- Author: <Author,,Name>
540-- Create date: <Create Date,,>
541-- Description: <Description,,>
542-- =============================================
543CREATE PROCEDURE [dbo].[csp_CL_getxmlTopstructure]
544 @@xml as nvarchar(max) output,
545 @@idchecklistactivity int,
546 @@idhelpdesktype int
547AS
548BEGIN
549
550 -- FLAG_EXTERNALACCESS --
551
552 declare @xml as xml
553 select @xml = (
554 select ce.idchecklistactivity, ce.[hasoptions],ce.[description], 0 as done ,'' as histories, ce.[document],
555 case when ce.position = 1 THEN 1 ELSE 0 END as visible, --ce.runvba,ce.activateadvance,
556 ce.email, ce.externallink,ce.comment,
557 (
558 select cl.[description],cl.idchecklistactivity,0 as selected,
559 (case when
560 (select count(*) idchecklistactivity
561 from nextstep ni
562 left join checklistactivity ci
563 on ni.nextcheckliststep = ci.idchecklistactivity
564 where ni.checklistactivity = cl.idchecklistactivity) > 1
565 then 0
566 else (
567 case when
568 (select ci.idchecklistactivity
569 from nextstep ni
570 left join checklistactivity ci
571 on ni.nextcheckliststep = ci.idchecklistactivity
572 where ni.checklistactivity = cl.idchecklistactivity)
573 is null then
574 0 else (select ci.idchecklistactivity
575 from nextstep ni
576 left join checklistactivity ci
577 on ni.nextcheckliststep = ci.idchecklistactivity
578 where ni.checklistactivity = cl.idchecklistactivity)
579 end)
580 end) as nextstep
581 from checklistactivity c
582 left join nextstep n
583 on c.idchecklistactivity = n.checklistactivity
584 left join checklistactivity cl
585 on n.nextcheckliststep = cl.idchecklistactivity
586 --where n.checklistactivity is not null
587 where c.helpdesktype = @@idhelpdesktype
588 and c.idchecklistactivity = ce.idchecklistactivity
589 for xml path('option'), elements, type) as options
590 from checklistactivity ce
591 where helpdesktype = @@idhelpdesktype
592 and idchecklistactivity = @@idchecklistactivity
593 order by ce.position
594 for xml path('checklistactivity'), ROOT('checklistactivitys'))
595
596 set @@xml = case when @xml is null then '' else cast(@xml as nvarchar(max)) end
597END
598
599
600
601
602
603
604GO
605
606/****** Object: StoredProcedure [dbo].[csp_CL_listorder] Script Date: 2019-08-01 11:28:20 ******/
607SET ANSI_NULLS ON
608GO
609
610SET QUOTED_IDENTIFIER ON
611GO
612
613
614
615
616
617-- =============================================
618-- Author: AAS
619-- Create date: 2014-07-14
620-- Description: <Description,,>
621-- =============================================
622CREATE PROCEDURE [dbo].[csp_CL_listorder]
623 @@idhelpdesktype INT
624AS
625BEGIN
626
627 -- FLAG_EXTERNALACCESS --
628 DECLARE @temp TABLE
629 (
630 [row] int identity(1,1)
631 ,id int
632 )
633
634 INSERT INTO @temp (id)
635 SELECT idchecklistactivity
636 FROM checklistactivity
637 WHERE helpdesktype = @@idhelpdesktype
638 AND [status] = 0
639
640 DECLARE @count INT
641 DECLARE @i INT
642 DECLARE @id INT
643
644 SET @i = 1
645 SET @count = (SELECT COUNT(*) FROM @temp)
646
647 while(@count >= @i)
648 BEGIN
649 SELECT @id = id FROM @temp WHERE [row] = @i
650 EXEC csp_CL_setlistorder @@idchecklistactivity = @id
651 SET @i = @i + 1
652 END
653
654
655
656END
657
658
659
660
661
662GO
663
664/****** Object: StoredProcedure [dbo].[csp_CL_setlistorder] Script Date: 2019-08-01 11:28:20 ******/
665SET ANSI_NULLS ON
666GO
667
668SET QUOTED_IDENTIFIER ON
669GO
670
671
672
673
674
675-- =============================================
676-- Author: <Author,,Name>
677-- Create date: <Create Date,,>
678-- Description: <Description,,>
679-- =============================================
680CREATE PROCEDURE [dbo].[csp_CL_setlistorder]
681 @@idchecklistactivity int
682AS
683BEGIN
684
685 -- FLAG_EXTERNALACCESS --
686
687 DECLARE @count INT
688 DECLARE @i INT
689 DECLARE @position Int
690 SET @i = 1
691
692 DECLARE @values TABLE(
693 ROW INT IDENTITY(1,1),
694 id INT,
695 [option] INT,
696 [position] INT
697 )
698
699 DECLARE @temp TABLE(
700 ROW INT IDENTITY(1,1),
701 id INT,
702 [position] INT
703 )
704
705
706 INSERT INTO @temp ([id],[position])
707 SELECT c.idchecklistactivity, c.position
708 FROM nextstep n
709 JOIN checklistactivity c
710 ON n.checklistactivity = c.idchecklistactivity
711 WHERE n.nextcheckliststep = @@idchecklistactivity
712 AND c.[status] = 0
713 AND n.[status] = 0
714
715 SET @count = (SELECT COUNT(*) id FROM @temp)
716
717 while (@count >= @i)
718 BEGIN
719 INSERT INTO @values ([id],[position])
720 SELECT [id],[position] from @temp where [row] = @i
721
722 SET @i = @i + 1
723 END
724
725 SET @position = (SELECT TOP 1 (position) + 1 FROM @values
726 ORDER BY position DESC )
727
728 UPDATE checklistactivity SET position = ( CASE WHEN @position is null THEN 1 ELSE @position END)
729 WHERE idchecklistactivity = @@idchecklistactivity
730
731
732END
733
734
735
736
737
738GO
739
740/****** Object: StoredProcedure [dbo].[csp_CL_updateChecklist] Script Date: 2019-08-01 11:28:20 ******/
741SET ANSI_NULLS ON
742GO
743
744SET QUOTED_IDENTIFIER ON
745GO
746
747
748
749
750
751-- =============================================
752-- Author: Sofie Helgesson
753-- Create date: 2016-04-29
754-- Description: Updates the Checklist XML when a soschecklistactivity has been updated
755-- =============================================
756--DECLARE--
757CREATE PROCEDURE [dbo].[csp_CL_updateChecklist]
758 @idhelpdesk INT,
759 @idchecklistactivity INT,
760 @done INT,
761 @officedescription NVARCHAR(32),
762 @ignore INT,
763 @ongoing INT,
764 @timestamp NVARCHAR(16),
765 @iduser INT,
766 @optionid INT,
767 @option NVARCHAR(128)
768AS
769BEGIN
770
771--SET @idhelpdesk = 4914101
772--SET @idchecklistactivity = 291301
773--SET @done = 0
774
775
776 DECLARE @user NVARCHAR(255)
777 DECLARE @idcoworker INT
778 DECLARE @idoffice_user INT
779 DECLARE @xml XML
780 DECLARE @xmlhistory XML
781 DECLARE @description NVARCHAR(255)
782 DECLARE @note NVARCHAR(MAX)
783 DECLARE @cancelerrand INT
784 DECLARE @cancelerrandoption INT
785 DECLARE @nextactivity INT
786 DECLARE @nextdone INT
787 DECLARE @hasoptions INT
788 DECLARE @stop INT
789 DECLARE @showall INT
790 DECLARE @olddone INT
791 DECLARE @oldofficedescription NVARCHAR(32)
792 DECLARE @oldignore INT
793 DECLARE @oldongoing INT
794 DECLARE @idcurrentcheckliststep INT
795 DECLARE @currentcheckliststep NVARCHAR(128)
796 DECLARE @office INT
797 DECLARE @officeoption INT
798 DECLARE @resetcoworker INT --#Anpassning
799
800 --SELECT CAST(REPLACE([xml], '&', '&') AS XML) FROM helpdesk WHERE idhelpdesk = 1002
801 SELECT @user = name, @idcoworker = idcoworker, @idoffice_user = office FROM coworker WHERE username = @iduser
802 SELECT @xml = REPLACE([xml], '&', '&') FROM helpdesk WHERE idhelpdesk = @idhelpdesk
803 SELECT @description = [table].[column].value('description[1]', 'nvarchar(255)'),
804 @olddone = [table].[column].value('done[1]', 'int'),
805 @oldofficedescription = [table].[column].value('descriptionoffice[1]', 'nvarchar(32)'),
806 @oldignore = [table].[column].value('ignore[1]', 'int'),
807 @oldongoing = [table].[column].value('ongoing[1]', 'int'),
808 @showall = [table].[column].value('showall[1]', 'int'),
809 @cancelerrand = [table].[column].value('cancelerrand[1]', 'int')
810 FROM @xml.nodes('/checklistactivitys/checklistactivity') AS [table]([column])
811 WHERE ([table].[column].value('idchecklistactivity[1]', 'int') = @idchecklistactivity)
812
813
814 -- FLAG_EXTERNALACCESS --
815
816 SET @note = ''
817
818 --DONE
819 IF @olddone <> @done
820 BEGIN
821 --Pricka av aktiviteten i Checklistan
822 SET @xml.modify('replace value of (/checklistactivitys/checklistactivity[idchecklistactivity = sql:variable("@idchecklistactivity")]/done/text())[1] with sql:variable("@done")')
823
824 --Histories
825 IF @done = 1
826 BEGIN
827 SET @note = 'Prickade av: ' + ISNULL(@description, '')
828 SET @xml.modify('replace value of (/checklistactivitys/checklistactivity[idchecklistactivity = sql:variable("@idchecklistactivity")]/ongoing/text())[1] with 0')
829 END
830 ELSE
831 BEGIN
832 SET @note = 'Backade: ' + ISNULL(@description, '')
833 END
834
835 --Skapa histories
836 SET @xmlhistory =
837 '<timestamp>' + @timestamp + '</timestamp>
838 <done>' + CAST(@done AS NVARCHAR(1)) + '</done>
839 <user>' + @user + '</user>
840 <option>' + ISNULL(@option, '') + '</option>'
841
842 -- Delete history before adding latest
843 SET @xml.modify('delete (/checklistactivitys/checklistactivity[idchecklistactivity = sql:variable("@idchecklistactivity")]/histories/timestamp)[1]')
844 SET @xml.modify('delete (/checklistactivitys/checklistactivity[idchecklistactivity = sql:variable("@idchecklistactivity")]/histories/done)[1]')
845 SET @xml.modify('delete (/checklistactivitys/checklistactivity[idchecklistactivity = sql:variable("@idchecklistactivity")]/histories/user)[1]')
846 SET @xml.modify('delete (/checklistactivitys/checklistactivity[idchecklistactivity = sql:variable("@idchecklistactivity")]/histories/option)[1]')
847
848 -- Insert history
849 SET @xml.modify('insert sql:variable("@xmlhistory") as first into (/checklistactivitys/checklistactivity[idchecklistactivity = sql:variable("@idchecklistactivity")]/histories)[1]')
850
851
852 IF @optionid IS NULL --OR @option = ''
853 BEGIN
854 --select 'option null'
855 SELECT
856 @nextactivity = [table].[column].value('options[1]/option[1]/idchecklistactivity[1]', 'int'),
857 @cancelerrandoption = [table].[column].value('cancelerrand[1]', 'int')
858 FROM @xml.nodes('/checklistactivitys/checklistactivity') AS [table]([column])
859 WHERE ([table].[column].value('idchecklistactivity[1]', 'int') = @idchecklistactivity)
860
861 -- --#Anpassning - ångra alla punkter framåt
862 -- --IF @done = 0 AND ISNULL(@nextactivity, 0) <> 0
863 -- -- SELECT @nextdone = [table].[column].value('done[1]', 'int')
864 -- -- FROM @xml.nodes('/checklistactivitys/checklistactivity') AS [table]([column])
865 -- -- WHERE ([table].[column].value('idchecklistactivity[1]', 'int') = @nextactivity)
866 END
867
868 WHILE((ISNULL(@nextactivity, 0) <> 0 OR ISNULL(@optionid, 0) <> 0) AND (ISNULL(@stop, 0) <> 1))
869 BEGIN
870 --select 'while'
871 --select @nextactivity
872 --select @optionid
873 --select @stop
874 --select @done
875 IF ISNULL(@optionid, 0) <> 0
876 BEGIN
877 --Markera option som valt
878 SET @xml.modify('replace value of (/checklistactivitys/checklistactivity[idchecklistactivity = sql:variable("@idchecklistactivity")]/options/option[idchecklistactivity = sql:variable("@optionid")]/selected/text())[1] with sql:variable("@done")')
879 --Pricka av aktiviteten i Checklistan
880 SET @xml.modify('replace value of (/checklistactivitys/checklistactivity[idchecklistactivity = sql:variable("@optionid")]/done/text())[1] with sql:variable("@done")')
881 SET @xml.modify('replace value of (/checklistactivitys/checklistactivity[idchecklistactivity = sql:variable("@optionid")]/option/text())[1] with sql:variable("@done")')
882 SET @xml.modify('replace value of (/checklistactivitys/checklistactivity[idchecklistactivity = sql:variable("@optionid")]/visible/text())[1] with sql:variable("@done")')
883 --SET @xml.modify('replace value of (/checklistactivitys/checklistactivity[idchecklistactivity = sql:variable("@optionid")]/mark/text())[1] with sql:variable("@done")')
884
885
886 --Hämta ut nästa punkt
887 SELECT
888 @nextactivity = [table].[column].value('options[1]/option[1]/idchecklistactivity[1]', 'int'),
889 --@stop = [table].[column].value('hasoptions[1]', 'int'),
890 @officeoption = [table].[column].value('office[1]', 'int')
891 FROM @xml.nodes('/checklistactivitys/checklistactivity') AS [table]([column])
892 WHERE ([table].[column].value('idchecklistactivity[1]', 'int') = @optionid)
893
894
895 IF @done = 0 AND ISNULL(@nextactivity, 0) <> 0
896 SELECT @nextdone = [table].[column].value('done[1]', 'int')
897 FROM @xml.nodes('/checklistactivitys/checklistactivity') AS [table]([column])
898 WHERE ([table].[column].value('idchecklistactivity[1]', 'int') = @nextactivity)
899
900 SET @optionid = NULL
901 END
902 --ELSE
903 --BEGIN
904 -- SELECT
905 -- @nextactivity = [table].[column].value('options[1]/option[1]/idchecklistactivity[1]', 'int'),
906 -- @stop = [table].[column].value('hasoptions[1]', 'int')
907 -- FROM @xml.nodes('/checklistactivitys/checklistactivity') AS [table]([column])
908 -- WHERE ([table].[column].value('idchecklistactivity[1]', 'int') = @nextactivity)
909
910 -- --#Anpassning - ångra alla punkter framåt
911 -- --IF @done = 0 AND ISNULL(@nextactivity, 0) <> 0
912 -- -- SELECT @nextdone = [table].[column].value('done[1]', 'int')
913 -- -- FROM @xml.nodes('/checklistactivitys/checklistactivity') AS [table]([column])
914 -- -- WHERE ([table].[column].value('idchecklistactivity[1]', 'int') = @nextactivity)
915 --END
916
917 --#Anpassning - ångra alla punkter framåt
918 IF 1=1 --@done = 1 OR @nextdone = 0 --Om nästa punkt är avprickad ska vi inte dölja
919 BEGIN
920 --Sätt nästa punkt som synlig/osynlig
921 SET @xml.modify('replace value of (/checklistactivitys/checklistactivity[idchecklistactivity = sql:variable("@nextactivity")]/visible/text())[1] with sql:variable("@done")')
922
923 --#Anpassing - ångra alla punkter framåt
924 IF @done = 0
925 SET @xml.modify('replace value of (/checklistactivitys/checklistactivity[idchecklistactivity = sql:variable("@nextactivity")]/done/text())[1] with sql:variable("@done")')
926
927 SELECT @hasoptions = [table].[column].value('hasoptions[1]', 'int')
928 FROM @xml.nodes('/checklistactivitys/checklistactivity') AS [table]([column])
929 WHERE ([table].[column].value('idchecklistactivity[1]', 'int') = @nextactivity)
930
931 IF @hasoptions = 0
932 BEGIN
933 SET @optionid = NULL
934 SELECT @nextactivity = [table].[column].value('options[1]/option[1]/idchecklistactivity[1]', 'int')
935 FROM @xml.nodes('/checklistactivitys/checklistactivity') AS [table]([column])
936 WHERE ([table].[column].value('idchecklistactivity[1]', 'int') = @nextactivity)
937
938 IF @done = 0 AND ISNULL(@nextactivity, 0) <> 0
939 SELECT @nextdone = [table].[column].value('done[1]', 'int')
940 FROM @xml.nodes('/checklistactivitys/checklistactivity') AS [table]([column])
941 WHERE ([table].[column].value('idchecklistactivity[1]', 'int') = @nextactivity)
942 END
943 ELSE -- @hasoptions = 1
944 BEGIN
945 SELECT @optionid = [table].[column].value('idchecklistactivity[1]', 'int')
946 FROM @xml.nodes('/checklistactivitys/checklistactivity[idchecklistactivity = sql:variable("@nextactivity")]/options/option') AS [table]([column])
947 WHERE ([table].[column].value('selected[1]', 'int') = 1)
948
949 SET @idchecklistactivity = @nextactivity
950
951 --2018-02-15 hamnade i oändlig loop
952 IF @done = 0 AND ISNULL(@nextactivity, 0) <> 0
953 SELECT @nextdone = [table].[column].value('done[1]', 'int')
954 FROM @xml.nodes('/checklistactivitys/checklistactivity') AS [table]([column])
955 WHERE ([table].[column].value('idchecklistactivity[1]', 'int') = @nextactivity)
956 END
957
958
959
960 IF (@done = 1 AND @showall = 0) OR (@done = 1 AND @hasoptions = 1) OR (@done=0 AND @hasoptions = 1 and @nextdone = 0 AND ISNULL(@optionid, 0) = 0)
961 SET @stop = 1
962 END
963 ELSE
964 BEGIN
965 SET @stop = 1
966 END
967 END
968
969 END
970
971 --IGNORE
972 IF @oldignore <> @ignore
973 BEGIN
974 --Uppdatera XMLen
975 SET @xml.modify('replace value of (/checklistactivitys/checklistactivity[idchecklistactivity = sql:variable("@idchecklistactivity")]/ignore/text())[1] with sql:variable("@ignore")')
976
977 IF @ignore = 1
978 SET @note = 'Ignorerade: ' + ISNULL(@description, '')
979 ELSE
980 SET @note = 'Ångrade ignorering: ' + ISNULL(@description, '')
981 END
982
983 --ONGOING
984 IF @oldongoing <> @ongoing
985 BEGIN
986 --Uppdatera XMLen
987 SET @xml.modify('replace value of (/checklistactivitys/checklistactivity[idchecklistactivity = sql:variable("@idchecklistactivity")]/ongoing/text())[1] with sql:variable("@ongoing")')
988
989 IF @ongoing = 1
990 SET @note = 'Påbörjade: ' + ISNULL(@description, '')
991 ELSE
992 IF @done = 0
993 SET @note = 'Avbröt: ' + ISNULL(@description, '')
994
995 --Skapa histories
996 SET @xmlhistory =
997 '<timestamp>' + @timestamp + '</timestamp>
998 <done>' + CAST(@ongoing AS NVARCHAR(1)) + '</done>
999 <user>' + @user + '</user>
1000 <option>' + ISNULL(@option, '') + '</option>'
1001
1002 -- Delete xml hist before adding latest
1003 SET @xml.modify('delete (/checklistactivitys/checklistactivity[idchecklistactivity = sql:variable("@idchecklistactivity")]/histories/timestamp)[1]')
1004 SET @xml.modify('delete (/checklistactivitys/checklistactivity[idchecklistactivity = sql:variable("@idchecklistactivity")]/histories/done)[1]')
1005 SET @xml.modify('delete (/checklistactivitys/checklistactivity[idchecklistactivity = sql:variable("@idchecklistactivity")]/histories/user)[1]')
1006 SET @xml.modify('delete (/checklistactivitys/checklistactivity[idchecklistactivity = sql:variable("@idchecklistactivity")]/histories/option)[1]')
1007
1008 -- Insert history
1009 SET @xml.modify('insert sql:variable("@xmlhistory") as first into (/checklistactivitys/checklistactivity[idchecklistactivity = sql:variable("@idchecklistactivity")]/histories)[1]')
1010
1011 END
1012
1013 --Logga historik
1014 IF ISNULL(@note, N'') <> N''
1015 BEGIN
1016 EXECUTE [dbo].[csp_CL_addHistory] @idhelpdesk, @note, @iduser
1017 END
1018
1019 --Get first unchecked checklistactivity
1020 SELECT TOP 1
1021 @idcurrentcheckliststep = [table].[column].value('idchecklistactivity[1]', 'int'),
1022 @currentcheckliststep = [table].[column].value('description[1]', 'nvarchar(128)'),
1023 @office = [table].[column].value('office[1]', 'int'),
1024 @resetcoworker = [table].[column].value('resetcoworker[1]', 'int')
1025 FROM @xml.nodes('/checklistactivitys/checklistactivity') AS [table]([column])
1026 WHERE ([table].[column].value('done[1]', 'int') = '0')
1027 AND ([table].[column].value('ignore[1]', 'int') = '0')
1028 AND ([table].[column].value('visible[1]', 'int') = '1')
1029
1030
1031 IF @done = 1 OR @done IS NULL
1032 BEGIN
1033 UPDATE helpdesk
1034 SET [xml] = REPLACE(CAST(@xml AS NVARCHAR(MAX)), '&', '&'),
1035 --[currentchecklistactivity] = @idcurrentcheckliststep,
1036 [activeactivity] = ISNULL(@currentcheckliststep,''), -- #Anpassning - since textfield
1037 [office] = CASE
1038 WHEN @office IS NOT NULL THEN @office
1039 WHEN @officeoption IS NOT NULL THEN @officeoption
1040 ELSE office
1041 END,
1042 [coworker] = CASE
1043 WHEN @office IS NULL AND @officeoption IS NULL THEN coworker
1044 WHEN office <> COALESCE(@office, @officeoption) THEN NULL
1045 WHEN @resetcoworker = 1 THEN NULL
1046 ELSE coworker
1047 END,
1048 [changeddate] = CASE
1049 WHEN @office IS NOT NULL AND @office <> ISNULL(office, 0) THEN GETDATE()
1050 WHEN @officeoption IS NOT NULL AND @officeoption <> ISNULL(office, 0) THEN GETDATE()
1051 ELSE changeddate
1052 END
1053 WHERE idhelpdesk = @idhelpdesk
1054 END
1055 ELSE -- #Anpassning - Om man backar till en aktivitet som saknar ansvarig avd ska den aktiva användaren och dess avdelning sättas
1056 -- @resetcoworker gäller inte vid backning
1057 BEGIN
1058 UPDATE helpdesk
1059 SET [xml] = REPLACE(CAST(@xml AS NVARCHAR(MAX)), '&', '&'),
1060 --[currentchecklistactivity] = @idcurrentcheckliststep,
1061 [activeactivity] = ISNULL(@currentcheckliststep,''), -- #Anpassning - since textfield
1062 [office] = CASE
1063 WHEN @office IS NOT NULL THEN @office
1064 ELSE @idoffice_user
1065 END,
1066 [coworker] = CASE
1067 WHEN @office IS NULL THEN @idcoworker
1068 WHEN office <> @office THEN NULL
1069 --WHEN @resetcoworker = 1 THEN NULL
1070 ELSE coworker
1071 END,
1072 [changeddate] = CASE
1073 WHEN @office IS NOT NULL AND @office <> ISNULL(office, 0) THEN GETDATE()
1074 WHEN @idoffice_user <> ISNULL(office, 0) THEN GETDATE()
1075 ELSE changeddate
1076 END
1077 WHERE idhelpdesk = @idhelpdesk
1078 END
1079
1080 --Avsluta ärende
1081 IF @done = 1 AND (@cancelerrand = 1 OR @cancelerrandoption = 1)
1082 BEGIN
1083 UPDATE helpdesk
1084 SET
1085 activeactivity = '',
1086 [startdate] = CASE WHEN [startdate] IS NULL THEN GETDATE() ELSE startdate END,
1087 [enddate] = GETDATE(),
1088 [office] = CASE WHEN [coworker] IS NULL THEN @idoffice_user ELSE [office] END,
1089 [coworker] = CASE WHEN [coworker] IS NULL THEN @idcoworker ELSE coworker END
1090 WHERE idhelpdesk = @idhelpdesk
1091
1092 UPDATE helpdesk
1093 SET
1094 [statuscolor] = dbo.cfn_gethelpdeskcolor(GETDATE(), @idhelpdesk)
1095 WHERE idhelpdesk = @idhelpdesk
1096
1097 --Logga historik
1098 --create history
1099 SELECT @note = [sv] FROM localize WHERE code = 't_errand_done'
1100 EXECUTE [dbo].[csp_CL_addHistory] @idhelpdesk, @note, @iduser
1101
1102 END
1103
1104END
1105
1106
1107
1108
1109
1110
1111
1112
1113GO
1114
1115/****** Object: StoredProcedure [dbo].[csp_CL_updateSOSchecklistactivities] Script Date: 2019-08-01 11:28:20 ******/
1116SET ANSI_NULLS ON
1117GO
1118
1119SET QUOTED_IDENTIFIER ON
1120GO
1121
1122
1123
1124
1125
1126
1127
1128-- =============================================
1129-- Author: Sofie Helgesson
1130-- Create date: 2016-04-29
1131-- Description: Updates the soschecklistactivities when the checklist has been updated
1132-- =============================================
1133CREATE PROCEDURE [dbo].[csp_CL_updateSOSchecklistactivities]
1134 @idhelpdesk INT,
1135 @idchecklistactivity INT,
1136 @done INT = NULL,
1137 @option INT = NULL,
1138 @ignore INT = NULL,
1139 @ongoing INT = NULL,
1140 @user NVARCHAR(128)
1141AS
1142BEGIN
1143
1144 -- FLAG_EXTERNALACCESS --
1145
1146 DECLARE @@tid as uniqueidentifier
1147 DECLARE @@idsoschecklist as INT
1148
1149 SELECT @@idsoschecklist = idsoschecklistactivity
1150 FROM soschecklistactivity
1151 WHERE checklistactivity = @idchecklistactivity
1152 AND helpdesk = @idhelpdesk
1153
1154 BEGIN TRANSACTION t1
1155
1156 EXEC lsp_beginupdate @@tid OUTPUT
1157 EXEC lsp_addupdatelog @@table = N'soschecklistactivity', @@idrecord = @@idsoschecklist, @@transactionid = @@tid, @@username = @user, @@new = 0, @@updated = 1
1158
1159 UPDATE soschecklistactivity
1160 SET
1161 done = ISNULL(@done, done),
1162 checklistactivityoption = ISNULL(@option, checklistactivityoption),
1163 ignore = ISNULL(@ignore, ignore),
1164 ongoing = CASE WHEN @done = 1 THEN 0 ELSE ISNULL(@ongoing, ongoing) END
1165 WHERE idsoschecklistactivity = @@idsoschecklist
1166
1167 EXEC lsp_enddataupdate @@transactionid = @@tid, @@username = @user
1168 EXEC lsp_endupdate @@transactionid = @@tid, @@username = @user
1169
1170 COMMIT TRANSACTION t1
1171 EXEC lsp_updatecompleted @@transactionid = @@tid
1172
1173END
1174
1175
1176
1177
1178
1179
1180
1181GO
1182
1183/****** Object: StoredProcedure [dbo].[csp_clearChecklist] Script Date: 2019-08-01 11:28:20 ******/
1184SET ANSI_NULLS ON
1185GO
1186
1187SET QUOTED_IDENTIFIER ON
1188GO
1189
1190-- =============================================
1191-- Author: Andreas Åström
1192-- Create date: 2014-07-10
1193-- Description: Används för att "backa" checklistan.
1194-- =============================================
1195CREATE PROCEDURE [dbo].[csp_clearChecklist]
1196 @@idchecklist int
1197 ,@@xml as nvarchar(4000) OUTPUT
1198AS
1199BEGIN
1200
1201 -- FLAG_EXTERNALACCESS --
1202
1203 declare @count int
1204 declare @i int
1205 declare @xml as xml
1206
1207 set @i = 1
1208
1209
1210 declare @temp table
1211 (row int identity(1,1),
1212 id int
1213 )
1214
1215 insert into @temp (id)
1216 select n.nextcheckliststep
1217 from checklistactivity c
1218 left join nextstep n
1219 on c.idchecklistactivity = n.checklistactivity
1220 where c.idchecklistactivity = @@idchecklist
1221
1222 set @count = (select count(*) id from @temp)
1223
1224 if @count > 0
1225 begin
1226 while (1 = 1 )
1227 begin
1228 insert into @temp (id)
1229 select n.nextcheckliststep
1230 from checklistactivity c
1231 left join nextstep n
1232 on c.idchecklistactivity = n.checklistactivity
1233 where c.idchecklistactivity = (select id from @temp where [row] = @i)
1234
1235 if @i = 1000
1236 begin
1237 break
1238 end
1239 set @i = @i + 1
1240 end
1241 end
1242
1243 delete @temp
1244 where id is null
1245
1246 set @xml = (
1247 select distinct id from @temp
1248 for xml path('activity'), ROOT('activities'))
1249
1250 set @@xml = (case when(cast(@xml as nvarchar(max))) is null then '' else (cast(@xml as nvarchar(max))) end)
1251
1252END
1253GO
1254
1255
1256USE [Lime_test]
1257GO
1258
1259/****** Object: UserDefinedFunction [dbo].[cfn_diffworkingminutes] Script Date: 2019-08-01 11:29:02 ******/
1260SET ANSI_NULLS ON
1261GO
1262
1263SET QUOTED_IDENTIFIER ON
1264GO
1265
1266-- =============================================
1267-- Author: Calle Wallisson (snodd av MOL)
1268-- Create date: 2014-09-24
1269-- Description: Returns working minutes between
1270-- @@starttime and @@endtime
1271-- IF @@daysoff = 0, days off will not be handeled,
1272-- IF @@daysoff = 1, days off will be excluded from working time
1273-- =============================================
1274CREATE FUNCTION [dbo].[cfn_diffworkingminutes]
1275(
1276 @@starttime DATETIME,
1277 @@endtime DATETIME
1278)
1279RETURNS INT
1280AS
1281 BEGIN
1282
1283
1284 DECLARE @daysoffend DATETIME
1285 DECLARE @daysoff INT
1286 DECLARE @current DATETIME
1287 DECLARE @minutes INT
1288
1289
1290 DECLARE @daystart TIME
1291 DECLARE @lunchstart TIME
1292 DECLARE @lunchend TIME
1293 DECLARE @dayend TIME
1294
1295 -- Below variables could be fetched from table in Lime
1296
1297 SET @daystart = '08:00'
1298 SET @dayend = '17:00'
1299 SET @lunchstart = '12:00'
1300 SET @lunchend = '13:00'
1301 SET @daysoff = 0
1302
1303 --Startvalues
1304 SET @minutes = 0
1305 SET @current = @@starttime
1306
1307 --GOTO START OF DAY IF TIMEPART BEFORE DAYSTART
1308 IF CONVERT(TIME, @current) < @daystart
1309 BEGIN
1310 SET @current = CONVERT(DATETIME, CONVERT(DATE, @current))
1311 + CONVERT(DATETIME, @daystart)
1312 END
1313
1314 --GOTO MONDAY IF START ON WEEKEND
1315 IF ( ( ( DATEPART(dw, @current) - 1 ) + ( @@DATEFIRST - 1 ) ) % 7 + 1 ) > 5
1316 BEGIN
1317 SET @current = CAST(CAST(DATEADD(DAY,
1318 8 - ( ( ( DATEPART(dw,
1319 @current) - 1 )
1320 + ( @@DATEFIRST - 1 ) )
1321 % 7 + 1 ), @current) AS DATE) AS DATETIME)
1322 + CAST(@daystart AS DATETIME)
1323 END
1324
1325 --LOOP UNTIL WE HAVE ENOUGH WORKING DAYS
1326 WHILE @current < @@endtime
1327 BEGIN
1328
1329 IF (@daysoff = 1)
1330 BEGIN
1331 -- GET END OF DAYS OFF (NULL IF CURRENT NOT IN DAYS OFF)
1332 SELECT @daysoffend = MAX([enddate])
1333 FROM [dbo].[daysoff]
1334 WHERE [status] = 0
1335 AND @current >= [startdate]
1336 AND @current < [enddate]
1337 END
1338 --GOTO MONDAY IF START ON WEEKEND
1339 IF ( ( ( DATEPART(dw, @current) - 1 ) + ( @@DATEFIRST - 1 ) )
1340 % 7 + 1 ) > 5
1341 BEGIN
1342 SET @current = CAST(CAST(DATEADD(DAY,
1343 8 - ( ( ( DATEPART(dw,
1344 @current) - 1 )
1345 + ( @@DATEFIRST
1346 - 1 ) ) % 7 + 1 ),
1347 @current) AS DATE) AS DATETIME)
1348 + CAST(@daystart AS DATETIME)
1349 END
1350 -- GOTO END OF LUNCH TIME IF LUNCH
1351 ELSE IF (CONVERT(TIME, @current) >= @lunchstart
1352 AND CONVERT(TIME, @current) < @lunchend
1353 AND @lunchstart IS NOT NULL
1354 AND @lunchend IS NOT NULL)
1355 BEGIN
1356 SET @current = DATEADD(MINUTE,
1357 DATEDIFF(MINUTE,
1358 CONVERT(TIME, @current),
1359 @lunchend),
1360 @current)
1361
1362 END
1363 -- GOTO NEXT DAY IF END OF DAY
1364 ELSE IF ( CONVERT(TIME, @current) >= @dayend )
1365 BEGIN
1366 SET @current = DATEADD(MINUTE,
1367 1440 - DATEDIFF(MINUTE,
1368 @daystart,
1369 @dayend)
1370 - CASE WHEN CONVERT(TIME, @current) > @dayend
1371 THEN DATEDIFF(MINUTE,
1372 @dayend,
1373 CONVERT(TIME, @current))
1374 ELSE 0
1375 END, @current)
1376 END
1377 -- GOTO END OF DAYS OFF IF DAYS OFF
1378 ELSE IF (NOT @daysoffend IS NULL AND @daysoff = 1)
1379 BEGIN
1380 SET @current = @daysoffend
1381 END
1382 -- WORKING HOUR --> ADD 1 MINUTE
1383 ELSE
1384 BEGIN
1385 SET @current = DATEADD(MINUTE, 1, @current)
1386 SET @minutes = @minutes + 1
1387 END
1388
1389 END
1390 RETURN ISNULL(@minutes, 0)
1391 END
1392
1393GO
1394
1395/****** Object: UserDefinedFunction [dbo].[cfn_diffworkingminutes2] Script Date: 2019-08-01 11:29:02 ******/
1396SET ANSI_NULLS ON
1397GO
1398
1399SET QUOTED_IDENTIFIER ON
1400GO
1401
1402
1403-- =============================================
1404-- Author: Calle Wallisson (snodd av MOL)
1405-- Create date: 2014-09-24
1406-- Description: Returns working minutes between
1407-- @@starttime and @@endtime
1408-- IF @@daysoff = 0, days off will not be handeled,
1409-- IF @@daysoff = 1, days off will be excluded from working time
1410-- =============================================
1411CREATE FUNCTION [dbo].[cfn_diffworkingminutes2]
1412(
1413 @@inparam1 DATETIME,
1414 @@inparam2 DATETIME
1415)
1416RETURNS INT
1417AS
1418 BEGIN
1419
1420
1421 DECLARE @daysoffend DATETIME
1422 DECLARE @daysoff INT
1423 DECLARE @current DATETIME
1424 DECLARE @minutes INT
1425
1426
1427 DECLARE @daystart TIME
1428 DECLARE @lunchstart TIME
1429 DECLARE @lunchend TIME
1430 DECLARE @dayend TIME
1431
1432 DECLARE @@starttime DATETIME
1433 DECLARE @@endtime DATETIME
1434
1435 SET @@starttime = @@inparam1
1436 SET @@endtime = @@inparam2
1437
1438 -- Below variables could be fetched from table in Lime
1439
1440 SET @daystart = '08:00'
1441 SET @dayend = '17:00'
1442 SET @lunchstart = '12:00'
1443 SET @lunchend = '13:00'
1444 SET @daysoff = 0
1445
1446 --Startvalues
1447 SET @minutes = 0
1448 SET @current = @@starttime
1449
1450 --GOTO START OF DAY IF TIMEPART BEFORE DAYSTART
1451 IF CONVERT(TIME, @current) < @daystart
1452 BEGIN
1453 SET @current = CONVERT(DATETIME, CONVERT(DATE, @current))
1454 + CONVERT(DATETIME, @daystart)
1455 END
1456
1457 --GOTO MONDAY IF START ON WEEKEND
1458 IF ( ( ( DATEPART(dw, @current) - 1 ) + ( @@DATEFIRST - 1 ) ) % 7 + 1 ) > 5
1459 BEGIN
1460 SET @current = CAST(CAST(DATEADD(DAY,
1461 8 - ( ( ( DATEPART(dw,
1462 @current) - 1 )
1463 + ( @@DATEFIRST - 1 ) )
1464 % 7 + 1 ), @current) AS DATE) AS DATETIME)
1465 + CAST(@daystart AS DATETIME)
1466 END
1467
1468 --LOOP UNTIL WE HAVE ENOUGH WORKING DAYS
1469 WHILE @current < @@endtime
1470 BEGIN
1471
1472 IF (@daysoff = 1)
1473 BEGIN
1474 -- GET END OF DAYS OFF (NULL IF CURRENT NOT IN DAYS OFF)
1475 SELECT @daysoffend = MAX([enddate])
1476 FROM [dbo].[daysoff]
1477 WHERE [status] = 0
1478 AND @current >= [startdate]
1479 AND @current < [enddate]
1480 END
1481 --GOTO MONDAY IF START ON WEEKEND
1482 IF ( ( ( DATEPART(dw, @current) - 1 ) + ( @@DATEFIRST - 1 ) )
1483 % 7 + 1 ) > 5
1484 BEGIN
1485 SET @current = CAST(CAST(DATEADD(DAY,
1486 8 - ( ( ( DATEPART(dw,
1487 @current) - 1 )
1488 + ( @@DATEFIRST
1489 - 1 ) ) % 7 + 1 ),
1490 @current) AS DATE) AS DATETIME)
1491 + CAST(@daystart AS DATETIME)
1492 END
1493 -- GOTO END OF LUNCH TIME IF LUNCH
1494 ELSE IF (CONVERT(TIME, @current) >= @lunchstart
1495 AND CONVERT(TIME, @current) < @lunchend
1496 AND @lunchstart IS NOT NULL
1497 AND @lunchend IS NOT NULL)
1498 BEGIN
1499 SET @current = DATEADD(MINUTE,
1500 DATEDIFF(MINUTE,
1501 CONVERT(TIME, @current),
1502 @lunchend),
1503 @current)
1504
1505 END
1506 -- GOTO NEXT DAY IF END OF DAY
1507 ELSE IF ( CONVERT(TIME, @current) >= @dayend )
1508 BEGIN
1509 SET @current = DATEADD(MINUTE,
1510 1440 - DATEDIFF(MINUTE,
1511 @daystart,
1512 @dayend)
1513 - CASE WHEN CONVERT(TIME, @current) > @dayend
1514 THEN DATEDIFF(MINUTE,
1515 @dayend,
1516 CONVERT(TIME, @current))
1517 ELSE 0
1518 END, @current)
1519 END
1520 -- GOTO END OF DAYS OFF IF DAYS OFF
1521 ELSE IF (NOT @daysoffend IS NULL AND @daysoff = 1)
1522 BEGIN
1523 SET @current = @daysoffend
1524 END
1525 -- WORKING HOUR --> ADD 1 MINUTE
1526 ELSE
1527 BEGIN
1528 SET @current = DATEADD(MINUTE, 1, @current)
1529 SET @minutes = @minutes + 1
1530 END
1531
1532 END
1533 RETURN ISNULL(@minutes, 0)
1534 END
1535
1536
1537GO
1538
1539/****** Object: UserDefinedFunction [dbo].[cfn_getAddressForHelpdesk] Script Date: 2019-08-01 11:29:02 ******/
1540SET ANSI_NULLS ON
1541GO
1542
1543SET QUOTED_IDENTIFIER ON
1544GO
1545
1546
1547CREATE FUNCTION [dbo].[cfn_getAddressForHelpdesk] (
1548 @@idhelpdesk INT
1549)
1550 RETURNS nvarchar(256)
1551AS
1552BEGIN
1553 -- Declarations
1554 DECLARE @address nvarchar(256)
1555 DECLARE @idservice int
1556 DECLARE @idserviceplace int
1557 DECLARE @idcompany int
1558
1559 SET @address = ''
1560
1561 SELECT @idservice = [service] from helpdesk where idhelpdesk = @@idhelpdesk AND [status] = 0
1562 IF (@idservice IS NULL)
1563 BEGIN
1564 SELECT @idcompany = company from helpdesk where idhelpdesk = @@idhelpdesk
1565 SELECT @address = [visitingaddress1] + ', ' + [visitingzipcode] + ' ' + [visitingcity] from company where idcompany = @idcompany
1566 IF (LEN(@address) < 6)
1567 BEGIN
1568 SELECT @address = [postaladdress1] + ', ' + [postalzipcode] + ' ' + [postalcity] from company where idcompany = @idcompany
1569 END
1570
1571 END
1572 ELSE IF @idservice IS NOT NULL
1573 BEGIN
1574 SELECT @idserviceplace = [serviceplace] FROM [service] WHERE idservice = @idservice AND [status] = 0
1575 IF (@idserviceplace is not null)
1576 BEGIN
1577 SELECT @address = [address] + ', ' + [zipcode] + ' ' + [postofficename] FROM serviceplace WHERE [idserviceplace] = @idserviceplace AND [status] = 0
1578 END
1579 ELSE
1580 BEGIN
1581 SELECT @idcompany = company from helpdesk where idhelpdesk = @@idhelpdesk
1582 SELECT @address = [visitingaddress1] + ', ' + [visitingzipcode] + ' ' + [visitingcity] from company where idcompany = @idcompany
1583 IF (LEN(@address) < 6)
1584 BEGIN
1585 SELECT @address = [postaladdress1] + ', ' + [postalzipcode] + ' ' + [postalcity] from company where idcompany = @idcompany
1586 END
1587 END
1588 END
1589 ELSE
1590 BEGIN
1591 SELECT @address = ''
1592 END
1593 IF (LEN(@address) < 5)
1594 BEGIN
1595 SELECT @address = ''
1596 END
1597
1598 RETURN @address
1599END
1600
1601
1602
1603
1604
1605
1606
1607GO
1608
1609/****** Object: UserDefinedFunction [dbo].[cfn_getAddressForParticipant] Script Date: 2019-08-01 11:29:02 ******/
1610SET ANSI_NULLS ON
1611GO
1612
1613SET QUOTED_IDENTIFIER ON
1614GO
1615
1616
1617CREATE FUNCTION [dbo].[cfn_getAddressForParticipant] (
1618 @@idparticipant INT, @@type NVARCHAR(16)
1619)
1620 RETURNS nvarchar(256)
1621AS
1622BEGIN
1623 -- Declarations
1624 DECLARE @address nvarchar(256)
1625
1626 DECLARE @idcompany INT
1627 DECLARE @idperson INT
1628
1629 SET @address = ''
1630
1631
1632 SELECT @idcompany = company from participant where idparticipant = @@idparticipant
1633 SELECT @idperson = person from participant where idparticipant = @@idparticipant
1634
1635
1636
1637 IF @idcompany IS NOT NULL AND @idperson IS NULL
1638 BEGIN
1639
1640 IF @@type = 'full'
1641 BEGIN
1642 SELECT @address = [visitingaddress1] + ', ' + [visitingzipcode] + ' ' + [visitingcity] from company where idcompany = @idcompany
1643
1644 IF (LEN(@address) < 6)
1645 BEGIN
1646 SELECT @address = [postaladdress1] + ', ' + [postalzipcode] + ' ' + [postalcity] from company where idcompany = @idcompany
1647 END
1648 END
1649 ELSE IF @@type = 'postaladdress'
1650 BEGIN
1651 SELECT @address = [postaladdress1] from company where idcompany = @idcompany
1652 END
1653 ELSE IF @@type = 'postalzipcode'
1654 BEGIN
1655 SELECT @address = postalzipcode from company where idcompany = @idcompany
1656 END
1657 ELSE IF @@type = 'postalcity'
1658 BEGIN
1659 SELECT @address = postalcity from company where idcompany = @idcompany
1660 END
1661
1662 END
1663 ELSE IF @idperson IS NOT NULL
1664 BEGIN
1665 SELECT @address = [postaladress] + ', ' + [postalzipcode] + ' ' + [postalcity] from person where idperson = @idperson
1666
1667 IF (LEN(@address) < 6)
1668 BEGIN
1669 IF @@type = 'full'
1670 BEGIN
1671 SELECT @address = [postaladdress1] + ', ' + [postalzipcode] + ' ' + [postalcity] from company where idcompany = @idcompany
1672 END
1673
1674 IF @@type = 'postaladdress'
1675 BEGIN
1676 SELECT @address = [postaladdress1] from company where idcompany = @idcompany
1677 END
1678 IF @@type = 'postalzipcode'
1679 BEGIN
1680 SELECT @address = postalzipcode from company where idcompany = @idcompany
1681 END
1682 IF @@type = 'postalcity'
1683 BEGIN
1684 SELECT @address = postalcity from company where idcompany = @idcompany
1685 END
1686 END
1687 ELSE
1688 BEGIN
1689 IF @@type = 'postaladdress'
1690 BEGIN
1691 SELECT @address = [postaladress] FROM person WHERE idperson = @idperson
1692 END
1693 IF @@type = 'postalzipcode'
1694 BEGIN
1695 SELECT @address = postalzipcode FROM person WHERE idperson = @idperson
1696 END
1697 IF @@type = 'postalcity'
1698 BEGIN
1699 SELECT @address = postalcity FROM person WHERE idperson = @idperson
1700 END
1701
1702 END
1703 END
1704 ELSE
1705 BEGIN
1706 SELECT @address = ''
1707 END
1708
1709 RETURN @address
1710END
1711
1712
1713
1714
1715
1716
1717
1718GO
1719
1720/****** Object: UserDefinedFunction [dbo].[cfn_getchildnod] Script Date: 2019-08-01 11:29:02 ******/
1721SET ANSI_NULLS ON
1722GO
1723
1724SET QUOTED_IDENTIFIER ON
1725GO
1726
1727-- =============================================
1728-- Author: <Author,,Name>
1729-- Create date: <Create Date, ,>
1730-- Description: <Description, ,>
1731-- =============================================
1732CREATE FUNCTION [dbo].[cfn_getchildnod]
1733(
1734 -- Add the parameters for the function here
1735 @idrecord int
1736)
1737RETURNS int
1738AS
1739BEGIN
1740 return 2
1741END
1742GO
1743
1744/****** Object: UserDefinedFunction [dbo].[cfn_gethelpdeskcolor] Script Date: 2019-08-01 11:29:02 ******/
1745SET ANSI_NULLS ON
1746GO
1747
1748SET QUOTED_IDENTIFIER ON
1749GO
1750
1751
1752CREATE FUNCTION [dbo].[cfn_gethelpdeskcolor] (
1753 @@now datetime,
1754 @@idhelpdesk int
1755)
1756 RETURNS int
1757AS
1758BEGIN
1759 -- Declare priority constants
1760 DECLARE @status_new int
1761 DECLARE @status_assigned_ok int
1762 DECLARE @status_assigned_panic int
1763 DECLARE @status_done int
1764 DECLARE @status_open_ok int
1765 DECLARE @status_open_panic int
1766 DECLARE @status_open_warning int
1767 DECLARE @status_parked int
1768 DECLARE @status_needs_developer int
1769
1770 -- Set priority constants
1771 SELECT @status_new = 1
1772 SELECT @status_open_panic = 2
1773 SELECT @status_open_warning = 3
1774 SELECT @status_open_ok = 4
1775 SELECT @status_assigned_panic = 5
1776 SELECT @status_assigned_ok = 6
1777 SELECT @status_parked = 7
1778 SELECT @status_done = 8
1779
1780 -- Declarations
1781 DECLARE @color_status int
1782 DECLARE @priority int
1783 -- Get priority
1784 SELECT @priority = dbo.cfn_gethelpdeskpriority(@@now, @@idhelpdesk)
1785
1786 -- Assigned and OK (Orange Hex: F79646)
1787 IF @priority = @status_assigned_ok or @priority = @status_open_ok
1788 SELECT @color_status = 187301
1789
1790 -- Assigned and panic (Pink Hex: FF4D4A)
1791 IF @priority = @status_assigned_panic
1792 SELECT @color_status = 187401
1793
1794 -- Done (Green Hex: 0F8B05)
1795 IF @priority = @status_done
1796 SELECT @color_status = 187501
1797
1798 -- Open and panic (Red Hex: FF0000)
1799 IF @priority = @status_open_panic
1800 SELECT @color_status = 187601
1801
1802 -- Open and warning (Yellow HEX: FFFF7B)
1803 IF @priority = @status_open_warning
1804 SELECT @color_status = 187701
1805
1806 -- Parked (Silver Hex: C6C3C6)
1807 IF @priority = @status_parked
1808 SELECT @color_status = 187801
1809
1810 -- Not started not assigned (Blue Hex: 6495ED)
1811 IF @priority = @status_new
1812 SELECT @color_status = 187901
1813
1814 RETURN @color_status
1815END
1816GO
1817
1818/****** Object: UserDefinedFunction [dbo].[cfn_gethelpdeskpriority] Script Date: 2019-08-01 11:29:02 ******/
1819SET ANSI_NULLS ON
1820GO
1821
1822SET QUOTED_IDENTIFIER ON
1823GO
1824
1825
1826CREATE FUNCTION [dbo].[cfn_gethelpdeskpriority] (
1827 @@now datetime,
1828 @@idhelpdesk int
1829)
1830 RETURNS int
1831AS
1832BEGIN
1833 -- Declare priority constants
1834 DECLARE @status_assigned_ok int
1835 DECLARE @status_assigned_panic int
1836 DECLARE @status_new int
1837 DECLARE @status_done int
1838 DECLARE @status_open_ok int
1839 DECLARE @status_open_panic int
1840 DECLARE @status_open_warning int
1841 DECLARE @status_parked int
1842 DECLARE @status_needs_developer int
1843
1844 -- Set priority constants
1845 SELECT @status_new = 1
1846 SELECT @status_open_panic = 2
1847 SELECT @status_open_warning = 3
1848 SELECT @status_open_ok = 4
1849 SELECT @status_assigned_panic = 5
1850 SELECT @status_assigned_ok = 6
1851 SELECT @status_parked = 7
1852 SELECT @status_done = 8
1853
1854 -- Declare time intervals
1855 DECLARE @default_parked_to_time datetime
1856 DECLARE @default_yellow_interval datetime
1857
1858 -- Set time intervals
1859 SELECT @default_parked_to_time = '08:00'
1860 SELECT @default_yellow_interval = '01:00'
1861
1862 -- Declarations
1863 DECLARE @priority int
1864 DECLARE @enddate datetime
1865 DECLARE @startdate datetime
1866 DECLARE @deadlinedate datetime
1867 DECLARE @parkeddate datetime
1868 DECLARE @assigned_to int
1869
1870 -- Get necessary information from record
1871 SELECT @enddate = [enddate],
1872 @startdate = [startdate],
1873 @deadlinedate = [deadlinedate],
1874 @parkeddate = [parkeddate],
1875 @assigned_to = [coworker]
1876 FROM dbo.[helpdesk]
1877 WHERE [idhelpdesk] = @@idhelpdesk
1878 AND [status] = 0
1879
1880 -- Set color_status depending on passed conditions
1881 SELECT @priority =
1882 CASE
1883 -- Task is done
1884 WHEN @enddate IS NOT NULL
1885 THEN @status_done
1886
1887 -- Task is parked
1888 WHEN @@now < ISNULL(@parkeddate, 0)
1889 THEN @status_parked
1890
1891
1892 -- Task is open and deadline is set and passed
1893 WHEN @@now > ISNULL(@deadlinedate, @@now + 60) AND @assigned_to IS NULL
1894 THEN @status_open_panic
1895
1896 -- Task is open and deadline is getting too close
1897 WHEN @@now > ISNULL(@deadlinedate, @@now + 60) - @default_yellow_interval
1898 AND @assigned_to IS NULL
1899 THEN @status_open_warning
1900
1901 -- Task is assigned and deadline is set and passed
1902 WHEN @@now > ISNULL(@deadlinedate, @@now + 60) AND @assigned_to IS NOT NULL
1903 THEN @status_assigned_panic
1904
1905 -- Task is new, not started nor assigned
1906 WHEN @startdate IS NULL AND @assigned_to IS NULL
1907 THEN @status_new
1908
1909 -- Task is assigned and OK
1910 WHEN @@now > ISNULL(@startdate, @@now + 60) AND @assigned_to IS NOT NULL
1911 THEN @status_assigned_ok
1912
1913 -- Task is open
1914 ELSE @status_open_ok
1915 END
1916
1917 RETURN @priority
1918END
1919GO
1920
1921/****** Object: UserDefinedFunction [dbo].[cfn_gethelpdesksort] Script Date: 2019-08-01 11:29:02 ******/
1922SET ANSI_NULLS ON
1923GO
1924
1925SET QUOTED_IDENTIFIER ON
1926GO
1927
1928
1929CREATE FUNCTION [dbo].[cfn_gethelpdesksort] (
1930 @@now datetime,
1931 @@idhelpdesk int
1932)
1933 RETURNS nvarchar(32)
1934AS
1935BEGIN
1936 -- Declarations
1937 DECLARE @sortexp nvarchar(32)
1938 DECLARE @priority int
1939 DECLARE @deadlinedate datetime
1940 DECLARE @parkeddate datetime
1941 DECLARE @enddate datetime
1942
1943 -- Get priority
1944 SELECT @priority = dbo.cfn_gethelpdeskpriority(@@now, @@idhelpdesk)
1945
1946 -- Get dates
1947 SELECT @deadlinedate = [deadlinedate],
1948 @parkeddate = [parkeddate],
1949 @enddate = [enddate]
1950 FROM dbo.[helpdesk]
1951 WHERE [idhelpdesk] = @@idhelpdesk
1952 AND [status] = 0
1953
1954 -- Set prioritystring
1955 SELECT @sortexp = CAST(@priority AS nvarchar(1))
1956
1957 -- If task is open
1958 IF @priority BETWEEN 1 AND 6
1959 SELECT @sortexp = @sortexp + dbo.lfn_formatdatetimeutc(@deadlinedate)
1960
1961 -- If task is parked
1962 IF @priority = 7
1963 SELECT @sortexp = @sortexp + dbo.lfn_formatdatetimeutc(@parkeddate)
1964
1965 -- If task is closed
1966 IF @priority = 8
1967 SELECT @sortexp = @sortexp + dbo.lfn_formatdatetimeutc(@enddate)
1968
1969 RETURN @sortexp
1970END
1971GO
1972
1973/****** Object: UserDefinedFunction [dbo].[cfn_getidstringbykey] Script Date: 2019-08-01 11:29:02 ******/
1974SET ANSI_NULLS ON
1975GO
1976
1977SET QUOTED_IDENTIFIER ON
1978GO
1979
1980-- Written by: Fredrik Eriksson
1981-- Created: 2015-03-17
1982
1983-- Returns the idstring corresponding to the key for the specified field.
1984CREATE FUNCTION [dbo].[cfn_getidstringbykey]
1985(
1986 @@tablename NVARCHAR(64)
1987 , @@fieldname NVARCHAR(64)
1988 , @@key NVARCHAR(256)
1989)
1990RETURNS INT
1991AS
1992BEGIN
1993
1994 RETURN
1995 (
1996 SELECT s.idstring
1997 FROM string s
1998 INNER JOIN attributedata ad
1999 ON ad.[owner] = N'field'
2000 AND ad.name = N'idcategory'
2001 AND ad.value = s.idcategory
2002 INNER JOIN field f
2003 ON f.idfield = ad.idrecord
2004 INNER JOIN [table] t
2005 ON t.idtable = f.idtable
2006 WHERE t.name = @@tablename
2007 AND f.name = @@fieldname
2008 AND s.[key] = @@key
2009 )
2010END
2011GO
2012
2013/****** Object: UserDefinedFunction [dbo].[cfn_getpasswordhash] Script Date: 2019-08-01 11:29:02 ******/
2014SET ANSI_NULLS ON
2015GO
2016
2017SET QUOTED_IDENTIFIER ON
2018GO
2019
2020
2021
2022CREATE function [dbo].[cfn_getpasswordhash]
2023(
2024 @@password nvarchar(128),
2025 @@salt nvarchar(128)
2026)
2027RETURNS nvarchar(40)
2028BEGIN
2029 DECLARE @hash varbinary(20)
2030 DECLARE @hashstr nvarchar(42)
2031
2032 SET @hash = HashBytes('SHA1', @@password + @@salt)
2033 SET @hashstr = master.dbo.fn_varbintohexstr(@hash)
2034
2035 RETURN RIGHT(@hashstr, len(@hashstr) - 2)
2036END
2037GO
2038
2039/****** Object: UserDefinedFunction [dbo].[cfn_getrandomstring] Script Date: 2019-08-01 11:29:02 ******/
2040SET ANSI_NULLS ON
2041GO
2042
2043SET QUOTED_IDENTIFIER ON
2044GO
2045
2046
2047
2048CREATE function [dbo].[cfn_getrandomstring]
2049(
2050 @length AS smallint
2051)
2052RETURNS nvarchar(100)
2053AS
2054BEGIN
2055 DECLARE @password nvarchar(100)
2056 DECLARE @characters nvarchar(100)
2057 DECLARE @count int
2058
2059 SET @characters = ''
2060 SET @count = 48
2061
2062 WHILE @count <=57
2063 BEGIN
2064 SET @characters = @characters + CAST(nCHAR(@count) AS nchar(1))
2065 SET @count = @count + 1
2066 END
2067
2068 SET @count = 65
2069
2070 WHILE @count <=90
2071 BEGIN
2072 SET @characters = @characters + CAST(nCHAR(@count) AS nchar(1))
2073 SET @count = @count + 1
2074 END
2075
2076 SET @count = 97
2077
2078 WHILE @count <=122
2079 BEGIN
2080 SET @characters = @characters + CAST(nCHAR(@count) AS nchar(1))
2081 SET @count = @count + 1
2082 END
2083
2084 SET @count = 0
2085 SET @password = ''
2086
2087 WHILE @count < @length
2088 BEGIN
2089 SET @password = @password + SUBSTRING(@characters,CAST((SELECT r FROM RandomHelper)*LEN(@characters) AS int)+1,1)
2090 SET @count = @count + 1
2091 END
2092
2093 RETURN @password
2094END
2095GO
2096
2097/****** Object: UserDefinedFunction [dbo].[cfn_getuserloggedin] Script Date: 2019-08-01 11:29:02 ******/
2098SET ANSI_NULLS ON
2099GO
2100
2101SET QUOTED_IDENTIFIER ON
2102GO
2103
2104
2105
2106CREATE FUNCTION [dbo].[cfn_getuserloggedin] (
2107 @@iduser int
2108)
2109 RETURNS nvarchar(64)
2110AS
2111BEGIN
2112 -- Declarations
2113 DECLARE @active int
2114 DECLARE @activestring nvarchar(64)
2115
2116 -- Set initial values
2117 SELECT @active = 0
2118
2119 -- See if the user is logged in
2120 SELECT @active = COUNT(*)
2121 FROM dbo.[session] s
2122 INNER JOIN dbo.[coworker] c
2123 ON s.[iduser] = c.[username]
2124 WHERE s.[active] = 1
2125 AND c.[username] = @@iduser
2126
2127 -- Set return value as string
2128 IF @active = 0
2129 BEGIN
2130 SELECT TOP 1 @activestring = N'Nej (' + dbo.lfn_formatdatetimeutc(s.[logouttime]) + N')'
2131 FROM dbo.[session] s
2132 WHERE s.[iduser] = @@iduser
2133 ORDER BY s.[logintime] DESC
2134 END
2135 ELSE
2136 BEGIN
2137 SELECT TOP 1 @activestring = N'Ja (' + dbo.lfn_formatdatetimeutc(s.[logintime]) + N')'
2138 FROM dbo.[session] s
2139 WHERE s.[iduser] = @@iduser
2140 ORDER BY s.[logintime] DESC
2141 END
2142
2143 -- If NULL, user has never logged in
2144 IF @activestring IS NULL
2145 SELECT @activestring = N'Nej'
2146
2147 RETURN @activestring
2148END
2149
2150
2151
2152
2153GO
2154
2155/****** Object: UserDefinedFunction [dbo].[cfn_iscustomer] Script Date: 2019-08-01 11:29:02 ******/
2156SET ANSI_NULLS ON
2157GO
2158
2159SET QUOTED_IDENTIFIER ON
2160GO
2161
2162-- =============================================
2163-- Author: Calle Wallisson
2164-- Create date: 2014-10-14
2165-- Description: Updates the customer after active contracts
2166-- =============================================
2167CREATE FUNCTION [dbo].[cfn_iscustomer]
2168(
2169 -- Add the parameters for the function here
2170 @@idcustomer INT
2171)
2172RETURNS INT
2173AS
2174BEGIN
2175
2176DECLARE @result INT
2177
2178
2179 ------BREDBAND
2180 --IF EXISTS (SELECT ct.idcontract FROM [contract] ct
2181 -- WHERE ct.[status] = 0
2182 -- AND ct.utilityname = 'Bredband'
2183 -- AND ct.contractstatustype <> 201001
2184 -- AND ct.contractstatustype <> 201201
2185 -- AND (ct.effectiveenddate IS NULL OR ct.effectiveenddate > GETDATE())
2186 -- AND ct.[company] = @@idcustomer)
2187 --BEGIN
2188 -- SET @result = 108601
2189 --END
2190 ----ELHANDEL
2191 --ELSE IF EXISTS (SELECT ct.idcontract FROM [contract] ct
2192 -- WHERE ct.[status] = 0
2193 -- --AND annualconsumptioncalculated != ''
2194 -- AND ct.utilityname = 'EL'
2195 -- AND ct.contracttype = 198501
2196 -- AND ct.contractstatustype != 201001
2197 -- AND ct.contractstatustype != 201201
2198 -- AND ct.company = @@idcustomer
2199 -- AND (ct.effectiveenddate > GETDATE() or ct.effectiveenddate IS NULL))
2200 --BEGIN
2201 -- SET @result = 108601
2202 --END
2203 ----ELNÄT
2204 --ELSE IF EXISTS (SELECT ct.idcontract FROM [contract] ct
2205 -- WHERE ct.[status] = 0
2206 -- --AND annualconsumptioncalculated != ''
2207 -- AND ct.utilityname = 'EL'
2208 -- AND ct.contracttype = 198701
2209 -- AND ct.contractstatustype <> 201001
2210 -- AND ct.contractstatustype <> 201201
2211 -- AND ct.company = @@idcustomer
2212 -- AND (ct.effectiveenddate > GETDATE() or ct.effectiveenddate IS NULL))
2213 --BEGIN
2214 -- SET @result = 108601
2215 --END
2216 ----FJÄRRVÄRME
2217 --ELSE IF EXISTS (SELECT ct.idcontract FROM [contract] ct
2218 -- WHERE ct.[status] = 0
2219 -- --AND annualconsumptioncalculated != ''
2220 -- AND ct.utilityname = 'Fjärrvärme'
2221 -- AND ct.contracttype = 198701
2222 -- AND ct.contractstatustype <> 201001
2223 -- AND ct.contractstatustype <> 201201
2224 -- AND ct.company = @@idcustomer
2225 -- AND (ct.effectiveenddate > GETDATE() or ct.effectiveenddate IS NULL))
2226 --BEGIN
2227 -- SET @result = 108601
2228 --END
2229 ----FJÄRRKYLA
2230 --ELSE IF EXISTS (SELECT ct.idcontract FROM [contract] ct
2231 -- WHERE ct.[status] = 0
2232 -- --AND annualconsumptioncalculated != ''
2233 -- AND ct.utilityname = 'Fjärrkyla'
2234 -- AND ct.contracttype = 198701
2235 -- AND ct.contractstatustype <> 201001
2236 -- AND ct.contractstatustype <> 201201
2237 -- AND ct.company = @@idcustomer
2238 -- AND (ct.effectiveenddate > GETDATE() or ct.effectiveenddate IS NULL))
2239 --BEGIN
2240 -- SET @result = 108601
2241 --END
2242 ----RENHÅLLNING
2243 --ELSE IF EXISTS (SELECT ct.idcontract FROM [contract] ct
2244 -- WHERE ct.[status] = 0
2245 -- AND ct.utilityname = 'Renhållning'
2246 -- AND ct.contracttype = 198701
2247 -- AND ct.contractstatustype <> 201001
2248 -- AND ct.contractstatustype <> 201201
2249 -- AND ct.company = @@idcustomer
2250 -- AND (ct.effectiveenddate > GETDATE() or ct.effectiveenddate IS NULL))
2251 --BEGIN
2252 -- SET @result = 108601
2253 --END
2254 ----VATTEN
2255 --ELSE IF EXISTS (SELECT ct.idcontract FROM [contract] ct
2256 -- WHERE ct.[status] = 0
2257 -- --AND annualconsumptioncalculated != ''
2258 -- AND ct.utilityname = 'Vatten'
2259 -- AND ct.contracttype = 198701
2260 -- AND ct.contractstatustype <> 201001
2261 -- AND ct.contractstatustype <> 201201
2262 -- AND ct.company = @@idcustomer
2263 -- AND (ct.effectiveenddate > GETDATE() or ct.effectiveenddate IS NULL))
2264 --BEGIN
2265 -- SET @result = 108601
2266 --END
2267 --INAKTIV KUND
2268
2269 IF EXISTS (SELECT ct.idcontract FROM [contract] ct
2270 WHERE ct.[status] = 0
2271 --AND annualconsumptioncalculated != ''
2272 AND ct.contractstatustype <> 201001
2273 AND ct.contractstatustype <> 201201
2274 AND ct.company = @@idcustomer
2275 AND (ct.effectiveenddate > GETDATE() or ct.effectiveenddate IS NULL))
2276 BEGIN
2277 SET @result = 108601
2278 END
2279 ELSE IF EXISTS (SELECT ct.idcontract FROM [contract] ct
2280 WHERE ct.[status] = 0
2281 AND (ct.effectiveenddate < GETDATE()
2282 OR ct.contractstatustype = 201001
2283 OR ct.contractstatustype = 201201))
2284 AND NOT EXISTS (SELECT ct.idcontract FROM [contract] ct
2285 WHERE ct.[status] = 0
2286 AND (ct.effectiveenddate > GETDATE() or ct.effectiveenddate IS NULL)
2287 AND ct.contractstatustype <> 201001
2288 AND ct.contractstatustype <> 201201)
2289 BEGIN
2290 SET @result = 108701
2291 END
2292 ELSE
2293 --PROSPEKT
2294 BEGIN
2295 SET @result = 108501
2296 END
2297
2298 RETURN ISNULL(@result, 108501)
2299
2300END
2301GO
2302
2303/****** Object: UserDefinedFunction [dbo].[cfn_sos_diffworkingminutes] Script Date: 2019-08-01 11:29:02 ******/
2304SET ANSI_NULLS ON
2305GO
2306
2307SET QUOTED_IDENTIFIER ON
2308GO
2309
2310-- =============================================
2311-- Author: Mostafa Ali
2312-- Create date: 2016-12-28
2313-- Description: Calculates the workingminutes between two datetimevalues
2314-- =============================================
2315CREATE FUNCTION [dbo].[cfn_sos_diffworkingminutes]
2316(
2317 @starttime DATETIME,
2318 @endtime DATETIME
2319)
2320RETURNS INT
2321AS
2322BEGIN
2323
2324 DECLARE @current DATETIME
2325 DECLARE @minutes INT
2326 DECLARE @daystart TIME
2327 DECLARE @dayend TIME
2328 DECLARE @daymonth INT
2329 DECLARE @redDays table(id int)
2330
2331 SET @daystart = '08:00'
2332 SET @dayend = '17:00'
2333
2334 -- Röda dagar dag+månad
2335 insert @redDays(id) values (11),(61),(15),(66),(2412),(2512),( 2612),(3112)
2336
2337 -- Fixa starttiden, sätter till starttid på nästa arbetsdagen
2338 IF DATEPART(hh, @starttime) >= datepart(hh,@dayend)
2339 set @starttime = dateadd(dd, 1, DATEDIFF(dd, 0, @starttime)) + cast (@daystart as datetime)
2340 IF DATEPART(hh, @starttime) < datepart(hh,@daystart)
2341 set @starttime = dateadd(dd, 0, DATEDIFF(dd, 0, @starttime)) + cast (@daystart as datetime)
2342
2343 WHILE (DATEPART(DW, @starttime) = 1 OR DATEPART(DW, @starttime) = 7 OR cast (cast(datepart(d,@starttime) as varchar) + cast(datepart(m,@starttime) as varchar) as int) in (select id from @redDays))
2344 set @starttime = dateadd(dd, 1, DATEDIFF(dd, 0, @starttime)) + cast (@daystart as datetime)
2345
2346
2347 -- Räkna tiden (helger och röda dagarna följer inte med)
2348 SET @minutes = 0
2349 SET @current = DATEADD(d, 1, @starttime)
2350
2351 while cast (@current as date) <= cast (@endtime as date)
2352 BEGIN
2353 set @daymonth = cast (cast(datepart(d,@current) as varchar) + cast(datepart(m,@current) as varchar) as int)
2354 if DATEPART(DW, @current) > 1 AND DATEPART(DW, @current) < 7 and not @daymonth in (select id from @redDays)
2355 set @minutes += 540
2356 set @current = dateadd (d, 1, @current)
2357 END
2358 if @starttime < @endtime
2359 set @minutes = @minutes + datediff(mi, cast(@starttime as time), cast(@endtime as time))
2360 else
2361 set @minutes = 0
2362
2363 RETURN @minutes
2364END
2365
2366GO
2367
2368/****** Object: UserDefinedFunction [dbo].[cfn_workorder_descriptive] Script Date: 2019-08-01 11:29:02 ******/
2369SET ANSI_NULLS ON
2370GO
2371
2372SET QUOTED_IDENTIFIER ON
2373GO
2374
2375
2376CREATE FUNCTION [dbo].[cfn_workorder_descriptive] (
2377 @@idworkorder INT
2378)
2379 RETURNS nvarchar(200)
2380AS
2381BEGIN
2382
2383RETURN (SELECT CAST(idworkorder AS NVARCHAR(32)) + ' - ' + s.address + ' (' + CAST(s.useplacecode AS NVARCHAR(32)) + ') , ' + s.zipcode + ', ' + s.postofficename
2384FROM workorder w join serviceplace s on s.idserviceplace = w.serviceplace
2385WHERE idworkorder = @@idworkorder)
2386
2387
2388
2389END
2390GO