· 7 years ago · Nov 15, 2018, 01:12 PM
1-- 15/08/18 RC - Added retention policy feature
2
3if not exists(select 0 from INFORMATION_SCHEMA.COLUMNS where table_name='metadata_profile' and COLUMN_name='retention_days')
4 alter table metadata_profile add retention_days int;
5 update metadata_profile set retention_days=0
6go
7if not exists(select 0 from INFORMATION_SCHEMA.COLUMNS where table_name='metadata_profile' and COLUMN_name='b_view')
8 alter table metadata_profile add b_view bit;
9 update metadata_profile set b_view=1
10go
11if not exists(select 0 from INFORMATION_SCHEMA.TABLES where table_name='metadata_retention_log')
12 create table metadata_retention_log(id int identity(1,1),profile_id int not null, profile_name varchar(100) not null, table_name [sysname] NOT NULL, action_date datetime not null, rows int not null, minref varchar(50) not null, maxref varchar(50) not null)
13go
14
15IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[profile_retention]') AND type in (N'P'))
16 DROP PROCEDURE [dbo].[profile_retention]
17go
18create proc profile_retention as
19begin
20 set nocount on
21 declare p cursor for select table_name, profile_name, where_column, where_values, retention_days, id from metadata_profile where coalesce(retention_days,0)>0
22 declare @pt nvarchar(1000)
23 declare @pn nvarchar(100)
24 declare @pw nvarchar(1000)
25 declare @pv nvarchar(1000)
26 declare @pr int
27 declare @pid int
28 declare @cmd nvarchar(max)
29 open p
30 fetch next from p into @pt, @pn, @pw, @pv, @pr, @pid
31 while @@fetch_status=0
32 begin
33 set @cmd = 'insert into metadata_retention_log(table_name, profile_id, profile_name, action_date, minref, maxref, rows) select '''+@pt+''','+convert(varchar,@pid)+','''+@pn+''',getdate(), min(reference), max(reference), count(*) from ['+@pt+'] where completed_date is not null and completed_date < dateadd(dd, -'+convert(varchar(10),@pr)+', getdate())'
34 if @pw is not null and @pw<>''
35 set @cmd=@cmd+' and ['+@pw+'] in ('''+replace(replace(@pv,'''',''''''),',',''',''')+''')'
36 print @cmd
37 exec (@cmd)
38 if @@ROWCOUNT>0
39 begin
40 set @cmd = 'delete from ['+@pt+'] where completed_date is not null and completed_date < dateadd(dd, -'+convert(varchar(10),@pr)+', getdate())'
41 if @pw is not null and @pw<>''
42 set @cmd=@cmd+' and ['+@pw+'] in ('''+replace(replace(@pv,'''',''''''),',',''',''')+''')'
43 print @cmd
44 -- exec (@cmd)
45 end
46
47 fetch next from p into @pt, @pn, @pw, @pv, @pr, @pid
48 end
49 close p
50 deallocate p
51
52end
53go
54IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[buildviews]') AND type in (N'P'))
55 DROP PROCEDURE [dbo].[buildviews]
56go
57CREATE proc [dbo].[buildviews] as
58begin
59 set nocount on
60 declare p cursor for select process_tablename, stage_tablename, process_name, processid from v_processes
61 declare @pt nvarchar(1000)
62 declare @ps nvarchar(1000)
63 declare @pn nvarchar(1000)
64 declare @pid nvarchar(1000)
65 declare @cmd nvarchar(max)
66
67 open p
68 fetch next from p into @pt, @ps, @pn, @pid
69 while @@FETCH_STATUS=0
70 begin
71 print 'processing '+@pn
72 exec buildview @pt
73 if @ps is not null exec buildview @ps
74 delete from metadata_process where process_tablename = @pt
75
76 set @cmd = 'insert into metadata_process(process_tablename, stage_tablename, process_name, processid,
77 min_completed, max_completed, max_exported, completed_row_count, active_row_count, min_incomplete_start)
78 select '''+@pt+''', '''+@ps+''', '''+@pn+''', '''+@pid+''', min(case when completed_date is null or completed_date=''1900-01-01 00:00'' then null else completed_date end) as min_completed,
79 max(completed_date) as max_completed,
80 max(date_exported) as max_exported,
81 sum(case when completed_date is null or completed_date=''1900-01-01 00:00'' then 0 else 1 end) as completed_row_count,
82 sum(case when completed_date is null or completed_date=''1900-01-01 00:00'' then 1 else 0 end) as active_row_count,
83 min(case when completed_date is null or completed_date=''1900-01-01 00:00'' then null else created_date end) as min_incomplete_start
84 from ['+@pt+']'
85 -- print @cmd
86 exec(@cmd)
87 fetch next from p into @pt, @ps, @pn, @pid
88 end
89 close p
90 deallocate p
91 exec profile_retention
92end
93go
94
95
96IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[reportdata]') AND type in (N'P'))
97 DROP PROCEDURE [dbo].[reportdata]
98go
99CREATE procedure [dbo].[reportdata](@process_tablename nvarchar(1000), @start datetime, @end datetime, @email varchar(500), @profile int=0) as
100begin
101 declare @cmd nvarchar(max)
102 declare @cols nvarchar(max)
103 declare @ok nvarchar(500)
104 declare @b_view bit
105 declare @where nvarchar(max)
106 set nocount on
107 -- figure out what AD groups the user is a member of
108 create table #usergroups(name nvarchar(1000))
109 insert into #usergroups(name) exec getADgroups @email
110
111 -- try to load a profile first, that matches the table name, and either contains the email address or has blank email (matches everyone)
112 select top 1
113 @b_view = b_view,
114 @ok = table_name,
115 @cols=
116 coalesce(stuff(dbo.[process_hidden_column_list]([filter_cols],'_rrr'),1,1,'')+',','')+
117 case
118 when [display_cols] is null then 'a.*'
119 else stuff(replace(','+replace([display_cols],' ',''),',',',a.'),1,1,'')
120 + coalesce(dbo.process_hidden_column_list([hide_cols],'_hhh'),'')
121 end
122 + case
123 when map='single' then ', [' + location_field + '] as ___maplocation_hhh'
124 when map='separate' then ', concat([' + lat_field+'], '', '', ['+lng_field + ']) as ___maplocation_hhh'
125 else ''
126 end
127 + case
128 when coalesce(map_icon_field,'')<>'' and coalesce(map_icon_template,'')<>''
129 then ', replace('''+map_icon_template+''',''ICONNAME'', ['+map_icon_field+']) as ___mapicon_hhh'
130 else ''
131 end,
132 @where = case when coalesce(where_column,'')='' then '1=1' else 'a.['+where_column+'] in('''+replace(replace(where_values,'''',''''''),', ',''',''')+''')' end
133 from metadata_profile
134 where table_name = @process_tablename
135 and (id=@profile or @profile=0)
136 and ( --permissions checks
137 user_emails like '%'+@email +'%' -- email address is on the profile, grant access
138 or exists(select 0 from #usergroups where ', '+user_groups+',' like '%, '+name+',%') -- if the user is in one of the listed groups, grant access
139 -- or (coalesce(user_emails,'')='' and coalesce(user_groups,'')='') -- no permissions defined means everyone has access
140 or exists (select 0 from metadata_superuser where email=@email) --superusers have access
141 )
142 if @ok is null
143 begin
144 -- no profile was found. see if the user is a superuser, in which case they get to see everything
145 select @ok = 'yeah', @cols='a.*', @where='1=1' from metadata_superuser where email=@email
146 end
147
148 if @ok is null
149 begin
150 select 'No data could be obtained. Please check your permissions' as result
151 return
152 end
153
154
155 declare @stage_tablename nvarchar(1000)
156 select @stage_tablename = 'stage_'+substring(@process_tablename,9,1000)
157 set @cmd=' ORDER BY a.reference'
158 -- if @cols is null set @cols='a.*'
159 -- if the dates are null, we query all active records
160 if @start is null or @end is null
161 set @cmd= 'a.completed_date = ''1900-01-01'' or a.completed_date is null '+@cmd
162 else
163 -- otherwise we get records that were completed within the specified dates
164 set @cmd= 'a.completed_date between '''+convert(varchar,@start)+''' and '''+ convert(varchar, @end)+''' '+@cmd
165 if @ok is null or @b_view = 1
166 begin
167 set @cols=@cols+', ''/AchieveForms?service=R4&mode=view&task_id=''+a.reference+''&db_id=''+laststage.task_db_id as view_url'
168 end
169 select @cmd = 'select '+@cols+' from ['+@process_tablename+'] a '
170 +'left join (select ROW_NUMBER() over(partition by reference order by completed_date desc) as r,reference, task_db_id '
171 +' from ['+@stage_tablename+']) as laststage on r=1 and laststage.reference=a.reference'
172 +' where ('+@where+') and '+@cmd
173 -- select @cmd as sql, @where as whereclause, 'hello' as test, @start as start, @end as theend, @cols as cols
174 -- print @cmd
175 exec (@cmd)
176end
177go
178
179 exec profile_retention
180go