· 7 years ago · Nov 15, 2018, 01:16 PM
1/****** Object: UserDefinedFunction [dbo].[WorkTime] Script Date: 05/11/2018 15:51:55 ******/
2IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[WorkTime]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
3DROP FUNCTION [dbo].[WorkTime]
4GO
5
6/****** Object: UserDefinedFunction [dbo].[WorkTime] Script Date: 05/11/2018 15:51:55 ******/
7SET ANSI_NULLS ON
8GO
9
10SET QUOTED_IDENTIFIER ON
11GO
12
13IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[WorkTime]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
14BEGIN
15execute dbo.sp_executesql @statement = N'CREATE FUNCTION [dbo].[WorkTime]
16(
17 @StartDate DATETIME,
18 @FinishDate DATETIME
19)
20RETURNS BIGINT
21AS
22BEGIN
23 DECLARE @Temp BIGINT
24 SET @Temp=0
25
26 DECLARE @FirstDay DATE
27 SET @FirstDay = CONVERT(DATE, @StartDate, 112)
28
29 DECLARE @LastDay DATE
30 SET @LastDay = CONVERT(DATE, @FinishDate, 112)
31
32 DECLARE @StartTime TIME
33 SET @StartTime = CONVERT(TIME, @StartDate)
34
35 DECLARE @FinishTime TIME
36 SET @FinishTime = CONVERT(TIME, @FinishDate)
37
38 DECLARE @WorkStart TIME
39 SET @WorkStart = ''09:00''
40
41 DECLARE @WorkFinish TIME
42 SET @WorkFinish = ''17:00''
43
44 DECLARE @DailyWorkTime BIGINT
45 SET @DailyWorkTime = DATEDIFF(MINUTE, @WorkStart, @WorkFinish)
46
47 IF (@StartTime<@WorkStart)
48 BEGIN
49 SET @StartTime = @WorkStart
50 END
51 IF (@FinishTime>@WorkFinish)
52 BEGIN
53 SET @FinishTime=@WorkFinish
54 END
55 IF (@FinishTime<@WorkStart)
56 BEGIN
57 SET @FinishTime=@WorkStart
58 END
59 IF (@StartTime>@WorkFinish)
60 BEGIN
61 SET @StartTime = @WorkFinish
62 END
63
64 DECLARE @CurrentDate DATE
65 SET @CurrentDate = @FirstDay
66 DECLARE @LastDate DATE
67 SET @LastDate = @LastDay
68
69 WHILE(@CurrentDate<=@LastDate)
70 BEGIN
71 IF (DATEPART(dw, @CurrentDate)!=1 AND DATEPART(dw, @CurrentDate)!=7)
72 BEGIN
73 IF (@CurrentDate!=@FirstDay) AND (@CurrentDate!=@LastDay)
74 BEGIN
75 SET @Temp = @Temp + @DailyWorkTime
76 END
77 --IF it starts at startdate and it finishes not this date find diff between work finish and start as minutes
78 ELSE IF (@CurrentDate=@FirstDay) AND (@CurrentDate!=@LastDay)
79 BEGIN
80 SET @Temp = @Temp + DATEDIFF(MINUTE, @StartTime, @WorkFinish)
81 END
82
83 ELSE IF (@CurrentDate!=@FirstDay) AND (@CurrentDate=@LastDay)
84 BEGIN
85 SET @Temp = @Temp + DATEDIFF(MINUTE, @WorkStart, @FinishTime)
86 END
87 --IF it starts and finishes in the same date
88 ELSE IF (@CurrentDate=@FirstDay) AND (@CurrentDate=@LastDay)
89 BEGIN
90 SET @Temp = DATEDIFF(MINUTE, @StartTime, @FinishTime)
91 END
92 END
93 SET @CurrentDate = DATEADD(day, 1, @CurrentDate)
94 END
95
96 -- Return the result of the function
97 IF @Temp<0
98 BEGIN
99 SET @Temp=0
100 END
101 RETURN @Temp
102
103END
104'
105END
106GO
107
108/****** Object: StoredProcedure [dbo].[getwhere] Script Date: 06/11/2018 12:54:29 ******/
109IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[getwhere]') AND type in (N'P', N'PC'))
110DROP PROCEDURE [dbo].[getwhere]
111GO
112
113/****** Object: StoredProcedure [dbo].[getwhere] Script Date: 06/11/2018 12:54:29 ******/
114SET ANSI_NULLS ON
115GO
116
117SET QUOTED_IDENTIFIER ON
118GO
119
120IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[getwhere]') AND type in (N'P', N'PC'))
121BEGIN
122EXEC dbo.sp_executesql @statement = N'CREATE PROCEDURE [dbo].[getwhere] AS'
123END
124GO
125
126
127ALTER proc [dbo].[getwhere](@process_tablename varchar(100), @profile int, @email varchar(500), @where varchar(max) output) as
128begin
129 set nocount on
130 declare @ok nvarchar(500)
131 declare @s varchar(max)
132 declare @d varchar(20)
133 set @where='1=0'
134 -- figure out what AD groups the user is a member of
135 create table #usergroups(name nvarchar(1000))
136 if @email<>'' insert into #usergroups(name) exec getADgroups @email
137 select top 1
138 @ok = table_name,
139 @where = case when coalesce(where_column,'')='' then '1=1' else 'a.['+where_column+'] in('''+replace(replace(where_values,'''',''''''),',',''',''')+''')' end
140 from metadata_profile
141 where table_name = @process_tablename
142 and (id=@profile or @profile=0)
143 and ( --permissions checks
144 user_emails like '%'+@email +'%' -- email address is on the profile, grant access
145 or exists(select 0 from #usergroups where ', '+user_groups+',' like '%, '+name+',%') -- if the user is in one of the listed groups, grant access
146 -- or (coalesce(user_emails,'')='' and coalesce(user_groups,'')='') -- no permissions defined means everyone has access
147 or exists (select 0 from metadata_superuser where email=@email) --superusers have access
148 )
149 if @ok is null
150 begin
151 -- no profile was found. see if the user is a superuser, in which case they get to see everything
152 select @where='1=1' from metadata_superuser where email=@email
153 end
154
155end
156GO
157
158
159
160/****** Object: StoredProcedure [dbo].[process_get_weekly_stats] Script Date: 06/11/2018 11:45:42 ******/
161IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[process_get_weekly_stats]') AND type in (N'P', N'PC'))
162DROP PROCEDURE [dbo].[process_get_weekly_stats]
163GO
164
165/****** Object: StoredProcedure [dbo].[process_get_weekly_stats] Script Date: 06/11/2018 11:45:42 ******/
166SET ANSI_NULLS ON
167GO
168
169SET QUOTED_IDENTIFIER ON
170GO
171
172IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[process_get_weekly_stats]') AND type in (N'P', N'PC'))
173BEGIN
174EXEC dbo.sp_executesql @statement = N'CREATE PROCEDURE [dbo].[process_get_weekly_stats] AS'
175END
176GO
177
178go
179
180ALTER proc [dbo].[process_get_weekly_stats](@process_tablename varchar(100), @from date, @to date, @email varchar(500)='', @profile int=0) as
181begin
182 set nocount on
183 declare @ok nvarchar(500)
184 declare @s varchar(max)
185 declare @d varchar(20)
186 declare @where varchar(max)
187
188 exec getwhere @process_tablename=@process_tablename, @email=@email, @profile=@profile, @where=@where output
189 create table #stats(wk varchar(50), value int, stat varchar(50))
190 print @from
191 print @to
192 while @from < @to
193 begin
194 set @d= concat(datepart(year,@from),' wk ',datepart(wk,@from))
195 set @s= 'insert into #stats(wk, value, stat)
196 select '''+@d +''', count(*),''created''
197 from ['+@process_tablename+'] a
198 where ('+@where+') and concat(datepart(year,created_date),'' wk '',datepart(wk,created_date))='''+@d +''''
199 exec(@s)
200 set @s= 'insert into #stats(wk, value, stat)
201 select '''+@d +''', count(*),''completed''
202 from ['+@process_tablename+'] a
203 where ('+@where+') and concat(datepart(year,completed_date),'' wk '',datepart(wk,completed_date))='''+@d +''''
204 exec(@s)
205 set @s= 'insert into #stats(wk, value, stat)
206 select '''+@d +''', count(*),''open''
207 from ['+@process_tablename+'] a
208 where ('+@where+') and '''+convert(varchar,@from,121)+''' > created_date and (completed_date = ''1900-01-01'' or completed_date is null or completed_date>'''+convert(varchar,@from,113)+''')'
209 print @s
210 exec(@s)
211 set @from= dateadd(wk,1,@from)
212
213 end
214 select a.wk, a.value as created, b.value as completed , c.value as [open]
215 from #stats a
216 inner join #stats b on a.stat='created' and b.stat='completed' and a.wk=b.wk
217 left join #stats c on a.stat='created' and c.stat='open' and a.wk=c.wk
218 drop table #stats
219end
220GO
221
222/****** Object: StoredProcedure [dbo].[process_get_submission_data] Script Date: 06/11/2018 12:35:38 ******/
223IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[process_get_submission_data]') AND type in (N'P', N'PC'))
224DROP PROCEDURE [dbo].[process_get_submission_data]
225GO
226
227/****** Object: StoredProcedure [dbo].[process_get_submission_data] Script Date: 06/11/2018 12:35:38 ******/
228SET ANSI_NULLS ON
229GO
230
231SET QUOTED_IDENTIFIER ON
232GO
233
234IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[process_get_submission_data]') AND type in (N'P', N'PC'))
235BEGIN
236EXEC dbo.sp_executesql @statement = N'CREATE PROCEDURE [dbo].[process_get_submission_data] AS'
237END
238GO
239
240
241
242ALTER proc [dbo].[process_get_submission_data](@process_tablename varchar(100), @from date, @to date, @email varchar(500)='', @profile int=0) as
243begin
244
245 declare @s varchar(max)
246 declare @where varchar(max)
247 exec getwhere @process_tablename=@process_tablename, @email=@email, @profile=@profile, @where=@where output
248 set nocount on
249
250 set @s= 'select
251 convert(date,completed_date) as date,
252 count(id) as daily_submissions,
253 sum(case when product=''Dash'' then 1 else 0 end) as dash_submissions,
254 sum(case when product=''Self'' then 1 else 0 end) as self_submissions,
255 sum(case when product=''Service'' then 1 else 0 end) as service_submissions,
256 sum(case when product=''Forms'' then 1 else 0 end) as forms_submissions
257 from ['+@process_tablename+'] a
258 where ('+@where+') and completed_date between '''+convert(varchar,@from,113)+''' and '''+convert(varchar,@to,113)+'''
259 group by convert(date,completed_date)
260 order by convert(date,completed_date) '
261 exec(@s)
262end
263GO
264
265/****** Object: StoredProcedure [dbo].[stage_stats_for_box_chart] Script Date: 06/11/2018 13:02:58 ******/
266IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[stage_stats_for_box_chart]') AND type in (N'P', N'PC'))
267DROP PROCEDURE [dbo].[stage_stats_for_box_chart]
268GO
269
270/****** Object: StoredProcedure [dbo].[stage_stats_for_box_chart] Script Date: 06/11/2018 13:02:58 ******/
271SET ANSI_NULLS ON
272GO
273
274SET QUOTED_IDENTIFIER ON
275GO
276
277IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[stage_stats_for_box_chart]') AND type in (N'P', N'PC'))
278BEGIN
279EXEC dbo.sp_executesql @statement = N'CREATE PROCEDURE [dbo].[stage_stats_for_box_chart] AS'
280END
281GO
282
283
284ALTER proc [dbo].[stage_stats_for_box_chart](@id nvarchar(100), @mindate datetime, @maxdate datetime, @template varchar(max), @email varchar(500)='', @profile int=0) as
285begin
286 declare @t nvarchar(100)
287 declare @s nvarchar(1000)
288 declare @stagename varchar(200)
289 declare @reference varchar(100)
290 declare @mins decimal
291 declare @where varchar(max)
292 exec getwhere @process_tablename=@id, @email=@email, @profile=@profile, @where=@where output
293
294 create table #results(id int identity(1,1),stagename varchar(200), labels varchar(max), datas varchar(max))
295 create table #datas(id int identity(1,1),stagename varchar(200), reference varchar(100), mins decimal)
296 declare t cursor for select stage_tablename from v_processes where process_tablename=@id
297 open t
298 fetch next from t into @t
299 while @@FETCH_STATUS=0
300 begin
301 print @t
302 set @s = 'select distinct status,''[next]'',''[next]'' from ['+@t+'] a where '+@where
303 print @s
304 insert into #results(stagename, labels, datas) exec(@s)
305 --de duplicate, in case >1 table has the same stage name
306 delete from #results where id in (select max(id) from #results group by stagename having count(*)>1)
307 set @s='select status, reference, convert(decimal,sum(dbo.worktime(created_date, completed_date)))/60 as duration from ['+@t+'] a where ('+@where+') and completed_date is not null and completed_date between '''+convert(varchar,@mindate,113)+''' and '''+ convert(varchar,@maxdate,113)+''' group by reference, status'
308 print @s
309 insert into #datas(stagename, reference, mins) exec(@s)
310 fetch next from t into @t
311 end
312 close t
313 deallocate t
314
315 declare r cursor for select stagename, reference, mins from #datas
316 open r
317 fetch next from r into @stagename, @reference, @mins
318 while @@FETCH_STATUS=0
319 begin
320 update #results set labels=replace(labels,'next','"'+@reference+'", next'), datas=replace(datas,'next',convert(varchar(10),round(@mins,2))+', next') where stagename=@stagename
321 fetch next from r into @stagename, @reference, @mins
322 end
323 close r
324 deallocate r
325 delete from #results where labels='[next]'
326 select
327 stagename,
328 replace(labels,', next','') as labels,
329 replace(datas,', next','') as datas,
330 replace(replace(replace(@template,'[[datas]]',replace(datas,', next','')),'[[labels]]',replace(labels,', next','')),'[[stagename]]',stagename) as the_json
331 from #results
332
333 drop table #results
334 drop table #datas
335end
336GO
337
338
339
340/*
341
342select * from v_processes order by process_name where process_name like '%ict%'
343
344exec process_get_weekly_stats 'process_ERROL_Take_On_And_Formation_Only_AF-Process-6c4f8b90-6595-4fb4-acc6-cbb03e0386ce','01 jun 2018','01 nov 2018','Amanda.Borzym@ocorian.com',2
345exec process_get_weekly_stats 'process_ERROL_Take_On_And_Formation_Only_AF-Process-6c4f8b90-6595-4fb4-acc6-cbb03e0386ce','01 jun 2018','01 nov 2018','',null
346
347select * from metadata_profile
348
349
350*/