· 4 years ago · Jan 06, 2021, 05:52 PM
1/*****************************************************************************************************************************
2TagPivot.AllSprocs_AllDatabases
3Update line 20 if ADD is on separate instance of SQL then Eclipse databases
4*****************************************************************************************************************************/
5
6IF OBJECT_ID('tempdb..#tbl_databases') IS NOT NULL
7BEGIN
8 DROP TABLE #tbl_databases;
9END
10CREATE TABLE #tbl_databases (DatabaseName NVARCHAR(50));
11
12IF OBJECT_ID('tempdb..#tbl_importtables') IS NOT NULL
13BEGIN
14 DROP TABLE #tbl_importtables;
15END
16CREATE TABLE #tbl_importtables (DatabaseName NVARCHAR(50)); --, ImportTableSizeMB DECIMAL(18,2));
17
18INSERT INTO #tbl_databases (DatabaseName)
19SELECT [DatabaseName]
20FROM [_Support_2019_7_0_ADDConfig].Enterprise.CaseProductEnvironment CPE
21INNER JOIN sys.databases D
22 ON D.[name] = CPE.DatabaseName
23WHERE ProductID = 3 and IsDeleted = 0 and CaseProductEnvironmentId = 4096
24/*-----------------------------------------------------*/
25
26
27-- define some variables to use in the loop
28DECLARE @execute_sql NVARCHAR(MAX), @SQL NVARCHAR(MAX), @ContextString nvarchar(max);
29DECLARE @database_name NVARCHAR(500);
30
31-- iterate through each database
32WHILE EXISTS (SELECT * FROM #tbl_databases)
33BEGIN
34
35 -- get this iteration's database
36 SELECT TOP 1
37 @database_name = [DatabaseName]
38 FROM #tbl_databases
39
40 SET @ContextString = 'USE ' + QUOTENAME(@database_name) + 'exec sp_executesql @SQL'
41
42 set @SQL = '
43 ALTER PROCEDURE [ActivityTracking].[ActivityTrackingSmartFolderDocuments]
44(
45 @SmartFolderGroupId uniqueidentifier
46 ,@SmartFolders ActivityTracking.SmartFolderType readonly
47)
48AS
49begin
50
51declare @SmartFolderValueKey int, @DocIds varchar(max), @SmartFolders_GroupId uniqueidentifier
52
53if exists(select top 1 * from @SmartFolders)
54 select @SmartFolders_GroupId = sf.SmartFolderGroupId
55 from @SmartFolders sf
56else
57 select @SmartFolders_GroupId = @SmartFolderGroupId
58
59if @SmartFolderGroupId != @SmartFolders_GroupId
60begin
61 raiserror (''SmartFolderGroupIds do not match'',16,1);
62 return -1;
63end
64
65create table #SmartfolderValueWithHash_New (SmartFolderGroupId uniqueidentifier, SmartFolderGroupName nvarchar(max), EntityId int, Value nvarchar(max), DocIds nvarchar(max), SmartFolderHash binary(20))
66
67--Add any new values that may not exist
68insert into #SmartFolderValueWithHash_New(SmartFolderGroupId, SmartFolderGroupName, EntityId, Value, DocIds, SmartFolderHash)
69select
70sf.SmartFolderGroupId
71,sf.SmartFolderGroupName
72,sf.EntityId
73,sf.Value
74,sf.DocIds
75,SmartFolderHash = HASHBYTES(''SHA1'', CAST(sf.SmartFolderGroupId as nvarchar(36)) + sf.SmartFolderGroupName + sf.EntityId + sf.Value)
76from @SmartFolders sf
77
78insert into ActivityTracking.DIM_SmartFolderValue
79(
80 SmartFolderGroupId
81 ,SmartFolderGroupName
82 ,EntityId
83 ,Value
84 ,SmartFolderHash
85)
86select
87 sf.SmartFolderGroupId
88 ,sf.SmartFolderGroupName
89 ,sf.EntityId
90 ,sf.Value
91 ,sf.SmartFolderHash
92from #SmartFolderValueWithHash_New sf
93left join ActivityTracking.DIM_SmartFolderValue sf_d on sf.SmartFolderHash = sf_d.SmartFolderHash
94where sf_d.SmartFolderValueKey is null
95-------------------------------------------------------------
96
97create table #ValueMap (SmartFolderValueKey int, EntityId int, value nvarchar(max), SmartFolderHash binary(20))
98
99insert into #ValueMap(SmartFolderValueKey,EntityId, Value, SmartFolderHash)
100select
101 sfv.SmartFolderValueKey
102 ,sfv.EntityId
103 ,sfv.value
104 ,sfv.SmartFolderHash
105from ActivityTracking.DIM_SmartFolderValue sfv
106where sfv.SmartFolderGroupId = @SmartFolderGroupId
107
108-------------------------------------------------------------
109create table #ValueMapToDocs_Prior (SmartFolderValueKey int, DocId int, DocumentEventActionTypeId int)
110create table #ValueMapToDocs_New (SmartFolderValueKey int, DocId int)
111create table #LastDocumentEvent (DocumentEventKey int)
112
113insert into #LastDocumentEvent(DocumentEventKey)
114select
115 DocumentEventKey = max(f.DocumentEventKey)
116from ActivityTracking.F_DocumentEventActions f
117inner join #ValueMap vm on f.SmartFolderValueKey = vm.SmartFolderValueKey
118
119insert into #ValueMapToDocs_Prior(SmartFolderValueKey, DocId, DocumentEventActionTypeId)
120select
121 vm.SmartFolderValueKey
122 ,d.DocId
123 ,f.DocumentEventActionTypeId
124from #ValueMap vm
125inner join ActivityTracking.F_DocumentEventActions f on vm.SmartFolderValueKey = f.SmartFolderValueKey
126inner join ActivityTracking.DIM_Document d on f.DocumentDWKey = d.DocumentDWKey
127inner join #LastDocumentEvent l on f.DocumentEventKey = l.DocumentEventKey
128
129insert into #ValueMapToDocs_New(SmartFolderValueKey, DocId)
130select
131 vm.SmartFolderValueKey
132 ,cast(parseDocId.[value] as int)
133from #SmartFolderValueWithHash_New sf
134inner join #ValueMap vm on sf.SmartFolderHash = vm.SmartFolderHash
135cross apply string_split(replace(replace( sf.docids,''['',''''),'']'','''') , '','') parseDocId
136
137-------------------------------------------------------------
138
139select
140 Change = case when p.docid is not null then ''R'' else ''A'' end
141 ,DocId = Coalesce(p.DocId, n.DocId)
142 ,SmartFolderValueKey = coalesce(p.SmartFolderValueKey, n.SmartFolderValueKey)
143from #ValueMapToDocs_Prior p
144full outer join #ValueMapToDocs_New n on p.DocId = n.DocId and p.SmartFolderValueKey = n.SmartFolderValueKey
145where
146 p.DocId is null
147 or n.DocId is null
148 and (
149 p.DocumentEventActionTypeId is null -- no previous events
150 or (p.DocId is not null and p.DocumentEventActionTypeId != 33) -- do not duplicate remove event
151 )
152end
153 '
154 EXEC sp_executesql @contextstring, N'@SQL nvarchar(max)', @SQL=@SQL
155
156 Set @SQL = 'ALTER PROCEDURE [ActivityTracking].[BatchUserSessionActivity_GetLastByBatchIds]
157 @BatchIds dbo.IdTableType READONLY
158AS
159
160SELECT
161 f.[BatchUserSessionActivityId]
162 ,f.[BatchUserSessionActivityTypeKey]
163 ,u.[UserKey]
164 ,u.[UserName]
165 ,b.[BatchId]
166 ,f.[DurationMinutes]
167 ,f.[ActivityCount]
168 ,f.[EventDateTime]
169FROM [ActivityTracking].[F_BatchUserSessionActivity] f
170INNER JOIN [ActivityTracking].[DIM_User] u
171 ON u.UserKey = f.UserKey
172INNER JOIN [ActivityTracking].[DIM_Batch] b
173 ON b.BatchDWKey = f.BatchDWKey
174WHERE f.BatchUserSessionActivityId =
175(
176 SELECT
177 MAX(BatchUserSessionActivityId)
178 FROM [ActivityTracking].[F_BatchUserSessionActivity]
179 WHERE BatchDWKey = f.BatchDWKey
180)
181AND b.BatchId IN
182(
183 SELECT id
184 FROM @BatchIds
185)'
186
187 EXEC sp_executesql @contextstring, N'@SQL nvarchar(max)', @SQL=@SQL
188
189 Set @SQL = 'ALTER PROCEDURE [ActivityTracking].[UserInsertOrUpdate]
190 @UserName nvarchar(256)
191 ,@FirstName nvarchar(50)
192 ,@LastName nvarchar(50)
193AS
194BEGIN
195 SET NOCOUNT ON;
196
197 DECLARE @UserInfo TABLE (UserName NVARCHAR(256), FirstName NVARCHAR(50), LastName NVARCHAR(50))
198 INSERT INTO @UserInfo (UserName, FirstName, LastName)
199 SELECT
200 UserName,
201 FirstName,
202 LastName
203 FROM ActivityTracking.DIM_User WITH (NOLOCK)
204 WHERE UserName = @UserName
205
206 IF EXISTS ( SELECT 1 FROM @UserInfo WHERE FirstName = @FirstName AND LastName = @LastName )
207 BEGIN
208 RETURN
209 END
210 ELSE IF EXISTS ( SELECT 1 FROM @UserInfo WHERE FirstName != @FirstName OR LastName != @LastName )
211 BEGIN
212 UPDATE ActivityTracking.DIM_User
213 SET FirstName = @FirstName
214 ,LastName = @LastName
215 WHERE UserName = @UserName
216 END
217 ELSE
218 BEGIN
219 INSERT INTO ActivityTracking.DIM_User ( UserName, FirstName, LastName )
220 VALUES ( @UserName, @FirstName, @LastName )
221 END
222END'
223
224 EXEC sp_executesql @contextstring, N'@SQL nvarchar(max)', @SQL=@SQL
225
226 Set @SQL = 'ALTER FUNCTION [dbo].[Documents_GenerateSortRowNumberStatement]
227(
228 @SortField dbo.OrderedGridItemType READONLY
229)
230RETURNS NVARCHAR(MAX)
231AS
232BEGIN
233 DECLARE @SqlSortStatement NVARCHAR(MAX)
234 SET @SqlSortStatement = ''ROW_NUMBER() OVER(ORDER BY ''
235 SET @SqlSortStatement +=
236 ISNULL(STUFF
237 ((
238 SELECT
239 CASE fd.ItemType WHEN 0 THEN ''df.'' ELSE '''' END + ColumnName +'' ''+ItemSortOrder + '', ''
240 FROM
241 (
242 SELECT
243 ItemId = FieldId
244 ,ColumnName
245 ,ItemType = 0 --GridItemType.Field
246 FROM vFieldDefinition
247 UNION
248 SELECT
249 ItemId = TagGroupId
250 ,ColumnName = ''[TG - '' + TagGroupName + '']''
251 ,ItemType = 1 --GridItemType.TagGroup
252 FROM TagGroups
253 UNION
254 SELECT
255 ItemID = 0
256 ,ColumnName = ''Score.Score''
257 ,ItemType = 3 --GridItemType.SimilarityScore
258 ) fd
259 INNER JOIN @SortField sf
260 ON sf.ItemId = fd.ItemId
261 AND sf.ItemType = fd.ItemType
262 ORDER BY sf.ItemOrder ASC FOR XML PATH('''')
263 ),1,0,''''), '''')
264 SET @SqlSortStatement += ''df.DocId) ''
265
266 RETURN @SqlSortStatement
267END'
268
269 EXEC sp_executesql @contextstring, N'@SQL nvarchar(max)', @SQL=@SQL
270
271 Set @SQL = '
272 ALTER FUNCTION [dbo].[Documents_GenerateSortRowNumberStatementForBatches]
273(
274 @SortField dbo.OrderedGridItemType READONLY,
275 @BatchId INT,
276 @FilterReviewed INT
277)
278RETURNS NVARCHAR(MAX)
279AS
280BEGIN
281 DECLARE @SqlSortStatement NVARCHAR(MAX)
282 ,@MutableSortFields dbo.OrderedGridItemType
283
284 INSERT INTO @MutableSortFields
285 SELECT *
286 FROM @SortField
287
288 IF NOT EXISTS(SELECT * FROM @MutableSortFields)
289 BEGIN
290 IF @BatchId = 0
291 BEGIN
292 RETURN ''ROW_NUMBER() OVER(ORDER BY df.DocId ASC)''
293 END
294 ELSE
295 BEGIN
296 INSERT INTO @MutableSortFields (ItemOrder, ItemId, ItemType, ItemSortOrder)
297 SELECT
298 FieldOrder = ROW_NUMBER() OVER (ORDER BY sbf.FieldSortOrder)
299 ,fd.FieldId
300 ,0
301 ,FieldValue = --GridItemType.Field
302 CASE
303 WHEN sbf.IsSortOrderAsc = 1
304 THEN ''ASC''
305 WHEN sbf.IsSortOrderAsc = 0
306 THEN ''DESC''
307 END
308 FROM Review.Batch b
309 INNER JOIN Review.SortByField sbf
310 ON sbf.ReviewPassId = b.ReviewPassId
311 INNER JOIN dbo.FieldDefinition fd
312 ON fd.FieldId = sbf.FieldId
313 WHERE b.BatchId = @BatchId
314 END
315 END
316
317 SET @SqlSortStatement = ''ROW_NUMBER() OVER(ORDER BY ''
318 SET @SqlSortStatement +=
319 ISNULL(STUFF
320 ((
321 SELECT
322 CASE fd.ItemType WHEN 0 THEN ''df.'' ELSE '''' END + ColumnName +'' ''+ItemSortOrder + '', ''
323 FROM
324 (
325 SELECT
326 ItemId = FieldId
327 ,ColumnName
328 ,ItemType = 0 --GridItemType.Field
329 FROM vFieldDefinition
330 UNION
331 SELECT
332 ItemId = TagGroupId
333 ,ColumnName = ''[TG - '' + TagGroupName + '']''
334 ,ItemType = 1 --GridItemType.TagGroup
335 FROM TagGroups
336 UNION
337 SELECT
338 ItemID = 0
339 ,ColumnName = ''Score.Score''
340 ,ItemType = 3 --GridItemType.SimilarityScore
341 ) fd
342 INNER JOIN @MutableSortFields sf
343 ON sf.ItemId = fd.ItemId
344 AND sf.ItemType = fd.ItemType
345 ORDER BY sf.ItemOrder ASC FOR XML PATH('''')
346 ),1,0,''''), '''')
347 SET @SqlSortStatement += ''df.DocId) ''
348
349 RETURN @SqlSortStatement
350END'
351
352 EXEC sp_executesql @contextstring, N'@SQL nvarchar(max)', @SQL=@SQL
353
354 Set @SQL = '
355 ALTER FUNCTION [dbo].[Documents_GenerateTagPivotStatement]
356(
357 @Filter dbo.ColumnFilterType readonly,
358 @SortField dbo.OrderedGridItemType readonly,
359 @DocIdSourceTableName nvarchar(256),
360 @ResultsId int = NULL,
361 @BatchId int = NULL
362)
363RETURNS nvarchar(max)
364AS
365BEGIN
366 /*
367 Calling sproc must contain the following table creation for join to work properly
368
369 IF OBJECT_ID(N''tempdb..#vTagHierarchy'') IS NOT NULL
370 DROP TABLE #vTagHierarchy
371
372 CREATE TABLE #vTagHierarchy
373 (
374 vID INT
375 ,TagGroupId int
376 ,TagGroupname nvarchar(100)
377 ,TagId int
378 ,tagname nvarchar(256)
379 ,isleaftag bit
380 )
381
382 INSERT INTO #vTagHierarchy (vID,TagGroupId, TagGroupname, tagid, tagname, isleaftag)
383 SELECT
384 ROW_NUMBER() OVER (ORDER BY TagPath) vID
385 ,TagGroupID
386 ,TagGroupName
387 ,TagId
388 ,TagName
389 ,IsLeafTag
390 FROM dbo.vTagHierarchy
391 */
392 DECLARE @TagGroupCols nvarchar(max)
393 DECLARE @TagGroupIds varchar(max)
394
395 SET @TagGroupCols =
396 stuff((
397 select '',[TG - '' + TagGroupName + '']''
398 from(
399 select th.TagGroupName as TagGroupName
400 from dbo.vTagHierarchy th
401 inner join @SortField sf on th.TagGroupId = sf.ItemId and sf.ItemType = 1 --tag group
402 union
403 select th.TagGroupName as TagGroupName
404 from dbo.vTagHierarchy th
405 inner join @Filter f on th.TagGroupId = f.ItemId and f.ItemType = 1 --tag group
406 ) cols
407 group by TagGroupName
408 order by TagGroupName
409 for xml path(''''), TYPE
410 ).value(''.'', ''nvarchar(max)'')
411 ,1,1,'''')
412
413 SET @TagGroupIds =
414 STUFF(( SELECT '', ''+ CONVERT(VARCHAR ,ItemId )
415 FROM
416 (
417 SELECT
418 ItemID
419 FROM @SortField
420 WHERE ItemType = 1
421 UNION
422 SELECT
423 ItemID
424 FROM @Filter
425 WHERE ItemType = 1
426 ) TagGroupIds
427 FOR XML PATH('''')), 1, 2, '''')
428
429 declare @HasTagSelections bit
430 set @HasTagSelections = case when @TagGroupCols is null then 0 else 1 end
431 RETURN case when @HasTagSelections = 1 then ''
432 inner join
433 (
434 select DocId, '' + @TagGroupCols + N''
435 from
436 (
437 select
438 d1.DocId,
439 TagGroupName = ''''TG - '''' + th1.TagGroupName,
440 stuff(
441 (
442 select ''''; '''' + th2.TagName
443 from #vTagHierarchy th2
444 left join dbo.DocumentTags dt2 on th2.TagId = dt2.TagId
445 where dt2.DocId = d1.DocId and th1.TagGroupId = th2.TagGroupId and th2.IsLeafTag = 1
446 order by th2.vID
447 for xml path('''''''')), 1, 2, ''''''''
448 ) as TagName
449 from '' + @DocIdSourceTableName + '' d1
450 left join dbo.DocumentTags dt1 on d1.DocId = dt1.DocId
451 '' + case when @ResultsId is not null then ''and d1.ResultsId = '' + CAST(@ResultsId as nvarchar(10)) else '''' end + ''
452 '' + case when @BatchId is not null then ''and d1.BatchId = '' + CAST(@BatchId as nvarchar(10)) else '''' end + ''
453 left join #vTagHierarchy th1 on th1.TagId = dt1.TagId
454 '' + case when exists(select * from @SortField where ItemType = 1) OR exists(select * from @Filter where ItemType = 1 ) then ''AND th1.TagGroupID IN (''+ @TagGroupIds + N'')''
455 else '''' end + ''
456 where (th1.IsLeafTag = 1 or th1.IsLeafTag is null)
457
458 group by th1.TagGroupName, d1.DocId, th1.TagGroupID
459 ) x
460 pivot
461 (
462 max(TagName)
463 for TagGroupName in ('' + @TagGroupCols + N'')
464 ) p
465 '' else '''' end
466END'
467
468 EXEC sp_executesql @contextstring, N'@SQL nvarchar(max)', @SQL=@SQL
469
470 Set @SQL = 'ALTER PROCEDURE [dbo].[Documents_GetBatchCount]
471 @BatchId int,
472 @FilterReviewed int,
473 @Filter dbo.ColumnFilterType readonly,
474 @WithSeconds bit,
475 @WithMilliseconds bit,
476 @StartDate datetime = null,
477 @EndDate datetime = null,
478 @IncludeNoDate bit = null,
479 @DateFieldId int = null,
480 @MinDate datetime,
481 @MaxDate datetime,
482 @QuickSearchResultsId INT,
483 @UserName NVARCHAR(256)
484AS
485set nocount on
486
487DECLARE @sql as nvarchar(max) = ''''
488DECLARE @SqlDateRangeWhere nvarchar(max) = ''''
489DECLARE @SqlFilterWhere nvarchar(max) = ''''
490DECLARE @SqlBatchJoin nvarchar(max) = ''''
491DECLARE @TagPivotTempTable nvarchar(max) = ''''
492DECLARE @QuickSearchJoin NVARCHAR(MAX) = ''''
493DECLARE @SqlWhere nvarchar(max) = ''''
494
495exec Documents_GetDocumentIds_GenerateDocumentDateClause @StartDate, @EndDate, @IncludeNoDate, @DateFieldId, @MinDate, @MaxDate, @SqlDateRangeWhere output
496exec Documents_GetDocumentIds_GenerateFilterClause @Filter, @WithSeconds, @WithMilliseconds, @SqlFilterWhere output
497
498if (@BatchId > 0)
499begin
500 set @SqlBatchJoin = ''INNER JOIN Review.BatchDocument bd ON df.DocId = bd.DocId AND bd.BatchId = '' + Convert(varchar(10), @BatchId)
501 if (@FilterReviewed = 1)
502 set @SqlBatchJoin = @SqlBatchJoin + '' AND bd.ReviewStatusId = 1 ''
503 else if (@FilterReviewed = 2)
504 set @SqlBatchJoin = @SqlBatchJoin + '' AND bd.ReviewStatusId <> 1 ''
505end
506
507IF (@QuickSearchResultsId > 0)
508BEGIN
509 declare @UserKey INT, @ResultsTableName as nvarchar(256)
510 SELECT @UserKey = ActivityTracking.GetUserKeyForUsername(@UserName)
511 SET @ResultsTableName = UserTables.SearchResultsTableName(@UserKey)
512
513 SET @QuickSearchJoin = ''INNER JOIN '' + @ResultsTableName + '' qsr ON bd.DocId = qsr.DocId AND qsr.ResultsId = '' + Convert(varchar(10), @QuickSearchResultsId)
514END
515
516--Build where clause
517if(len(@SqlFilterWhere) > 0)
518begin
519 set @SqlWhere += ''WHERE '' + @SqlFilterWhere
520 if(len(@SqlDateRangeWhere) > 0)
521 set @SqlWhere += '' AND '' + @SqlDateRangeWhere
522end
523else if(len(@SqlDateRangeWhere) > 0)
524 set @SqlWhere += ''WHERE '' + @SqlDateRangeWhere
525
526DECLARE @SortField dbo.OrderedGridItemType
527IF(@BatchId > 0)
528 SET @TagPivotTempTable = dbo.Documents_GenerateTagPivotTempTable(@Filter, @SortField, ''Review.BatchDocument'', DEFAULT, @BatchId)
529ELSE
530 SET @TagPivotTempTable = dbo.Documents_GenerateTagPivotTempTable(@Filter, @SortField, ''dbo.Documents'', DEFAULT, DEFAULT)
531
532DECLARE @FieldsToInclude dbo.IdTableType
533INSERT INTO @FieldsToInclude (Id)
534SELECT ItemId
535FROM @Filter
536WHERE ItemType = 0 --GridItemType.Field
537UNION
538SELECT @DateFieldId
539WHERE @DateFieldId is not NULL
540
541IF OBJECT_ID(N''tempdb..#vTagHierarchy'') IS NOT NULL
542 DROP TABLE #vTagHierarchy
543
544CREATE TABLE #vTagHierarchy
545(
546 vID INT
547 ,TagGroupId int
548 ,TagGroupname nvarchar(100)
549 ,TagId int
550 ,tagname nvarchar(256)
551 ,isleaftag bit
552)
553
554INSERT INTO #vTagHierarchy (vID,TagGroupId, TagGroupname, tagid, tagname, isleaftag)
555SELECT
556 ROW_NUMBER() OVER (ORDER BY TagPath) vID
557 ,TagGroupID
558 ,TagGroupName
559 ,TagId
560 ,TagName
561 ,IsLeafTag
562FROM dbo.vTagHierarchy
563
564set @Sql = ''
565<TagTempTable>
566
567SELECT
568 count(df.DocId)
569FROM '' + [dbo].[Documents_GenerateAliasedDocumentFields](@FieldsToInclude, ''df'');
570
571set @SQL = @SQL + ''
572<TagPivotJoin>
573<BatchJoin>
574<QuickSearchJoin>
575<SqlWhere>
576'';
577set @sql = REPLACE(@sql,''<TagTempTable>'',@TagPivotTempTable);
578if LEN(@TagPivotTempTable) > 1
579BEGIN
580 set @sql = REPLACE(@sql,''<TagPivotJoin>'',''INNER JOIN #TagDocs dt on dt.docid = df.docid'');
581END
582ELSE
583BEGIN
584 set @sql = REPLACE(@sql,''<TagPivotJoin>'','''');
585END
586set @sql = REPLACE(@sql,''<BatchJoin>'', @SqlBatchJoin);
587set @sql = REPLACE(@sql,''<QuickSearchJoin>'', @QuickSearchJoin);
588set @sql = REPLACE(@sql,''<SqlWhere>'', @SqlWhere);
589
590execute sp_executesql @sql, N''@Filter dbo.ColumnFilterType readonly'', @Filter = @Filter'
591
592 EXEC sp_executesql @contextstring, N'@SQL nvarchar(max)', @SQL=@SQL
593
594 Set @SQL = '
595 ALTER PROCEDURE [dbo].[Documents_GetDocumentIds_GenerateFilterClause]
596 @Filter dbo.ColumnFilterType readonly
597 ,@WithSeconds bit
598 ,@WithMilliseconds bit
599 ,@WhereClause nvarchar(max) output
600AS
601begin
602set nocount on
603
604 set @WhereClause = ''''
605
606 if not exists(select * from @Filter) return 0
607
608 if ( (select dbo.Documents_DocumentViewStrategy_CheckFilterRules (@Filter)) = 0)
609 begin
610 set @WhereClause = ''1 = 2''
611 return 0
612 end
613
614 declare
615 @ColumnName nvarchar(128)
616 ,@FieldValue nvarchar(max)
617 ,@FieldDataType int
618 ,@FieldValueDateTime1 datetime
619 ,@FieldValueDateTime2 datetime
620 ,@FieldValueDate1 date
621 ,@FieldValueDate2 date
622 ,@FieldPrefix varchar(3)
623 ,@ItemType int
624
625 declare cur_filterFields cursor for
626 select
627 df.ColumnName
628 ,Value =
629 replace(
630 replace(
631 replace(f.Value,'''''''','''''''''''')
632 ,''%'',''[%]'')
633 ,''*'',''%'')
634 ,df.FieldDataType
635 ,f.ItemType
636 from @Filter f
637 left join dbo.vFieldDefinition df on df.FieldId = f.ItemId and f.ItemType = 0
638 where f.ItemType = 0 -- field=0, tag group=1
639 union
640 select
641 ''[TG - '' + th.TagGroupName +'']''
642 ,Value =
643 replace(
644 replace(
645 replace(f.Value,'''''''','''''''''''')
646 ,''%'',''[%]'')
647 ,''*'',''%'')
648 ,1
649 ,f.ItemType
650 from @Filter f
651 join dbo.TagGroups th on th.TagGroupId = f.ItemId and f.ItemType = 1
652 where f.ItemType = 1
653
654 open cur_filterFields
655
656 fetch next from cur_filterFields
657 into @ColumnName
658 ,@FieldValue
659 ,@FieldDataType
660 ,@ItemType
661
662 declare @i int = 0;
663
664 while @@FETCH_STATUS =0
665 begin
666 if @ItemType = 0
667 set @FieldPrefix = ''df.''
668 else
669 set @FieldPrefix = ''''
670
671 if (@i > 0)
672 set @WhereClause += '' and ''
673
674 if @FieldDataType = 6 and @FieldValue in (''y'',''yes'')
675 set @FieldValue = ''1'';
676 if @FieldDataType = 6 and @FieldValue in (''n'',''no'')
677 set @FieldValue = ''0'';
678
679 if @FieldValue = '''' OR @FieldValue IS NULL
680 set @WhereClause += ''('' + @FieldPrefix + @ColumnName +'' = '''''''' OR '' + @FieldPrefix + @ColumnName + '' IS NULL)'';
681 else if @FieldDataType = 10
682 begin
683 if isnull(@WithMilliseconds, 1) = 1
684 set @WhereClause += @FieldPrefix + @ColumnName + '' = '''''' + @FieldValue + ''''''''
685 else
686 begin
687 begin try
688 set @FieldValue = REPLACE(@FieldValue,''%'','''');
689
690 if LEN(@FieldValue) = 4
691 begin
692 set @FieldValueDateTime1 = cast(@FieldValue as datetime)
693 set @FieldValueDateTime2 = DATEADD(ms, -3, DATEADD(yy, DATEDIFF(yy, 0, @FieldValueDateTime1) + 1, 0))
694 print @FieldValueDateTime2
695 end
696 else if LEN(@FieldValue) = 7
697 begin
698 set @FieldValueDateTime1 = cast((@FieldValue + ''-01'') as datetime)
699 set @FieldValueDateTime2 = DATEADD(ms, -3, DATEADD(m, DATEDIFF(m, 0, @FieldValueDateTime1) + 1, 0))
700 end
701 else if LEN(@FieldValue) = 10
702 begin
703 set @FieldValueDateTime1 = cast(@FieldValue as datetime)
704 set @FieldValueDateTime2 = DATEADD(ms, -3, DATEADD(dd, DATEDIFF(dd, 0, @FieldValueDateTime1), 0)) + 1
705 end
706 else
707 begin
708 begin try
709 set @FieldValueDateTime1 = CONVERT(datetime, @FieldValue,103);
710 end try
711 begin catch
712 set @FieldValueDateTime1 = CONVERT(datetime, @FieldValue,121);
713 end catch
714 if isnull(@WithSeconds, 1) = 0
715 begin
716 set @FieldValueDateTime1 = dateadd(MILLISECOND, -1 * datepart(MILLISECOND, @FieldValueDateTime1), @FieldValueDateTime1)
717 set @FieldValueDateTime1 = dateadd(SECOND, -1 * datepart(SECOND, @FieldValueDateTime1), @FieldValueDateTime1)
718 set @FieldValueDateTime2 = dateadd(MINUTE, 1, @FieldValueDateTime1)
719 end
720 else
721 begin
722 set @FieldValueDateTime1 = dateadd(MILLISECOND, -1 * datepart(MILLISECOND, @FieldValueDateTime1), @FieldValueDateTime1)
723 set @FieldValueDateTime2 = dateadd(SECOND, 1, @FieldValueDateTime1)
724 end
725 end
726 set @WhereClause += @FieldPrefix + @ColumnName + '' >= '''''' + convert(varchar(40), @FieldValueDateTime1, 121) + ''''''''
727 set @WhereClause += '' and '' + @FieldPrefix + @ColumnName + '' < '''''' + convert(varchar(40), @FieldValueDateTime2, 121) + ''''''''
728 end try
729 begin catch
730 set @WhereClause += ''1 = 2''
731 end catch
732 end
733 end
734 else if @FieldDataType = 5
735 begin
736 if isnull(@WithMilliseconds, 1) = 1
737 set @WhereClause += @FieldPrefix + @ColumnName + '' = '''''' + @FieldValue + ''''''''
738 else
739 begin
740 begin try
741 set @FieldValue = REPLACE(@FieldValue,''%'','''');
742
743 if LEN(@FieldValue) = 4 OR LEN(@FieldValue) = 7
744 begin
745 if LEN(@FieldValue) = 4
746 begin
747 set @FieldValueDate1 = cast(@FieldValue as date)
748 set @FieldValueDate2 = DATEADD(ms, -3, DATEADD(yy, DATEDIFF(yy, 0, @FieldValueDate1) + 1, 0))
749 end
750 else if LEN(@FieldValue) = 7
751 begin
752 set @FieldValueDate1 = cast((@FieldValue + ''-01'') as date)
753 set @FieldValueDate2 = DATEADD(ms, -3, DATEADD(m, DATEDIFF(m, 0, @FieldValueDate1) + 1, 0))
754 end
755
756 set @WhereClause += @FieldPrefix + @ColumnName + '' >= '''''' + convert(varchar(40), @FieldValueDate1, 121) + ''''''''
757 set @WhereClause += '' and '' + @FieldPrefix + @ColumnName + '' <= '''''' + convert(varchar(40), @FieldValueDate2, 121) + ''''''''
758 end
759 else if LEN(@FieldValue) = 10
760 begin
761 set @FieldValueDate1 = cast(@FieldValue as date)
762 set @WhereClause += @FieldPrefix + @ColumnName + '' = '''''' + convert(varchar(40), @FieldValueDate1, 121) + ''''''''
763 end
764 else
765 begin
766 set @WhereClause += ''1 = 2''
767 end
768 end try
769 begin catch
770 set @WhereClause += ''1 = 2''
771 end catch
772 end
773 end
774 else
775 begin
776 set @WhereClause += @FieldPrefix + @ColumnName
777
778 if isnull(CHARINDEX(''%'',@FieldValue),0) = 0
779 set @WhereClause += '' = '''''' + @FieldValue + '''''''';
780 else
781 set @WhereClause += '' LIKE '''''' + @FieldValue + '''''''';
782 end
783
784 set @i += 1;
785
786 fetch next from cur_filterFields
787 into @ColumnName
788 ,@FieldValue
789 ,@FieldDataType
790 ,@ItemType
791
792 set @WhereClause += ''''
793 end
794
795 close cur_filterFields
796 deallocate cur_filterFields
797end'
798
799 EXEC sp_executesql @contextstring, N'@SQL nvarchar(max)', @SQL=@SQL
800
801 Set @SQL = '
802 ALTER PROCEDURE [dbo].[GetDocumentFieldsDynamicAggregateDataWithFilter] ( @ResultsId INT,
803 @IncludeFieldId INT = NULL,
804 @FieldId1 INT,
805 @FieldId2 INT = NULL,
806 @UserKey INT,
807 @Filter dbo.ColumnFilterType readonly,
808 @SortField dbo.OrderedGridItemType readonly,
809 @WithSeconds bit,
810 @WithMilliseconds bit,
811 @StartDate datetime = null,
812 @EndDate datetime = null,
813 @IncludeNoDate bit,
814 @DateFieldId int,
815 @MinDate datetime,
816 @MaxDate datetime,
817 @QuickSearchResultsId INT
818 ) AS
819
820SET XACT_ABORT, NOCOUNT ON
821BEGIN TRY
822BEGIN TRANSACTION
823
824DECLARE @SQLStatement NVARCHAR(MAX) = ''''
825DECLARE @PreSQLStatement NVARCHAR(MAX) = ''''
826DECLARE @CompleteSQLStatement NVARCHAR(MAX) = ''''
827DECLARE @ErrorMessage NVARCHAR(MAX)
828DECLARE @SearchTableName VARCHAR(128)
829DECLARE @CheckTableName VARCHAR(128)
830DECLARE @SearchResultsIncludeTableName VARCHAR(128)
831DECLARE @CheckUserKey INT
832DECLARE @CheckResultsId INT
833DECLARE @Table1 VARCHAR(128)
834DECLARE @Column1 VARCHAR(128)
835DECLARE @Table2 VARCHAR(128)
836DECLARE @Column2 VARCHAR(128)
837DECLARE @UserName NVARCHAR(100)
838DECLARE @IncludeTable VARCHAR(128)
839DECLARE @IncludeColumn VARCHAR(128)
840DECLARE @IncludeFieldCrossReference1 VARCHAR(128)
841DECLARE @IncludeFieldCrossReference2 VARCHAR(128)
842DECLARE @GUID VARCHAR(36)
843DECLARE @SourceTableValues Search.SourceTableValuesTableType
844DECLARE @Parameters NVARCHAR(MAX)
845DECLARE @IncludeColumnDataType NVARCHAR(MAX)
846DEClARE @IncludeColumnDataLength NVARCHAR(MAX)
847DECLARE @IncludeColumnTypeString NVARCHAR(MAX)
848DECLARE @RowCount INT = 0
849
850-- To make refactoring possible, we are clearly delineating things and going to use existing
851-- logic in the GetDocumentFieldsDynamicAggregateData proc with one minor modification - we are going to point the query generated by that proc
852-- to a temp table instead of to UserTables.SearchResults
853
854 select @UserName = UserName from ActivityTracking.DIM_User where UserKey = @UserKey
855
856DECLARE @IsFiltered BIT = 1
857IF
858(
859 @StartDate IS NULL
860 AND @EndDate IS NULL
861 AND @IncludeNoDate = 1
862) SET @IsFiltered = 0
863
864IF (@IsFiltered = 1)
865BEGIN
866 SET @SQLStatement = ''select @RowCount = Count(*) from UserTables.SearchResults'' + Convert(varchar, @UserKey) + '' Where ResultsId = '' + Convert(varchar, @ResultsId)
867 SET @Parameters = N''@RowCount INT OUTPUT''
868 EXEC sp_executesql @SQLStatement, @Parameters,
869 @RowCount = @RowCount OUTPUT
870
871
872 create table #FilteredResults (ResultsId int default 999, DocId int)
873
874
875 insert into #FilteredResults (DocId)
876 exec dbo.Documents_GetPagedDocumentIdsFromSearch @ResultsId = @ResultsId
877 ,@UserName = @UserName
878 ,@PageSize = @RowCount
879 ,@PageNumber = 1
880 ,@Filter = @Filter
881 ,@SortField = @SortField
882 ,@WithSeconds = @WithSeconds
883 ,@WithMilliseconds = @WithMilliseconds
884 ,@StartDate = @StartDate
885 ,@EndDate = @EndDate
886 ,@IncludeNoDate = @IncludeNoDate
887 ,@DateFieldId = @DateFieldId
888 ,@MinDate = @MinDate
889 ,@MaxDate = @MaxDate
890 ,@QuickSearchResultsId = @QuickSearchResultsId
891
892
893 SET @SQLStatement = '' ''
894END
895
896
897----------------------------- Line in the sand ------------------------------------------
898
899/*****************************************************************************
900*
901* UserKey and ResultsId validation starts here. If a UserKey is provided,
902* then a ResultsId also needs to be provided (and vice versa) UserKey and
903* ResultsId are used to limit the rows returned by Visual Search. Effectively
904* this allows the user to drill down into data
905*
906*****************************************************************************/
907
908
909
910 IF @UserKey IS NOT NULL
911 BEGIN
912 SELECT @CheckUserKey = UserKey,
913 @UserName = UserName
914 FROM ActivityTracking.Dim_User
915 WHERE UserKey = @UserKey
916
917 IF @CheckUserKey IS NULL
918 BEGIN
919 SET @ErrorMessage = ''Error 91001: GetDocumentFieldsDynamicAggregateData procedure. There is no matching record in ActivityTracking.Dim_User for user '' + CONVERT(VARCHAR,@UserKey)
920 RAISERROR(@ErrorMessage,16,1)
921 END
922 END
923
924 IF @ResultsId IS NOT NULL
925 BEGIN
926 IF @UserKey IS NULL
927 BEGIN
928 SET @ErrorMessage = ''Error 91011: GetDocumentFieldsDynamicAggregateData procedure. A UserKey must be supplied if a ResultsId is passed to the proc to get a filtered dataset '' + CONVERT(VARCHAR,@UserKey)
929 RAISERROR(@ErrorMessage,16,1)
930 END
931 END
932
933/*****************************************************************************
934*
935* Field1 input validation starts here
936*
937*****************************************************************************/
938
939--- Find the table and column name(s) that we are interested in
940
941--- Find table and column for FieldId1
942
943 BEGIN
944 SELECT @Table1 = TableName,
945 @Column1 = ColumnName
946 FROM vFieldDefinition
947 WHERE FieldId = @FieldId1
948
949--- See if the field of interest has actually been defined
950
951 IF @Table1 IS NULL
952 BEGIN
953 SET @ErrorMessage = ''Error 91002: GetDocumentFieldsDynamicAggregateData procedure. The FieldId you specified - '' + CONVERT(VARCHAR,@FieldId1) + '' can not be found in the FieldDefinition table.''
954 RAISERROR(@ErrorMessage,16,1)
955 END
956
957--- See if we have an Aggregate table for our field of interest
958
959 SET @SearchTableName = @Table1 + ''_'' + @Column1 + ''_Aggregate''
960 SET @CheckTableName = NULL
961
962 SELECT @CheckTableName = table_name
963 FROM information_schema.columns
964 WHERE table_name = @SearchTableName
965
966 IF @CheckTableName IS NULL
967 BEGIN
968 SET @ErrorMessage = ''Error 91003: GetDocumentFieldsDynamicAggregateData procedure. There is no Aggregate table found for '' + CONVERT(VARCHAR,@FieldId1) + '' : '' + @Table1 + ''.'' + @Column1
969 RAISERROR(@ErrorMessage,16,1)
970 END
971
972--- See if we have an Association table for our field of interest
973
974 SET @SearchTableName = @Table1 + ''_'' + @Column1 + ''_Association''
975 SET @CheckTableName = NULL
976
977 SELECT @CheckTableName = table_name
978 FROM information_schema.columns
979 WHERE table_name = @SearchTableName
980
981 IF @CheckTableName IS NULL
982 BEGIN
983 SET @ErrorMessage = ''Error 91003: GetDocumentFieldsDynamicAggregateData procedure. There is no Association table found for '' + CONVERT(VARCHAR,@FieldId1) + '' : '' + @Table1 + ''.'' + @Column1
984 RAISERROR(@ErrorMessage,16,1)
985 END
986
987 END --- End of our logic block for FieldId1
988
989/*****************************************************************************
990*
991* Field2 input validation starts here
992*
993*****************************************************************************/
994
995--- Find table and column for FieldId2
996
997 IF @FieldId2 IS NOT NULL
998 BEGIN
999 SELECT @Table2 = TableName,
1000 @Column2 = ColumnName
1001 FROM vFieldDefinition
1002 WHERE FieldId = @FieldId2
1003
1004--- See if the field of interest has actually been defined
1005
1006 IF @Table2 IS NULL
1007 BEGIN
1008 SET @ErrorMessage = ''Error 91004: GetDocumentFieldsDynamicAggregateData procedure. The FieldId you specified - '' + CONVERT(VARCHAR,@FieldId2) + '' can not be found in the FieldDefinition table.''
1009 RAISERROR(@ErrorMessage,16,1)
1010 END
1011
1012--- See if we have an Aggregate table for our field of interest
1013
1014 SET @SearchTableName = @Table2 + ''_'' + @Column2 + ''_Aggregate''
1015 SET @CheckTableName = NULL
1016
1017 SELECT @CheckTableName = table_name
1018 FROM information_schema.columns
1019 WHERE table_name = @SearchTableName
1020
1021 IF @CheckTableName IS NULL
1022 BEGIN
1023 SET @ErrorMessage = ''Error 91005: GetDocumentFieldsDynamicAggregateData procedure. There is no Aggregate table found for '' + CONVERT(VARCHAR,@FieldId2) + '' : '' + @Table2 + ''.'' + @Column2
1024 RAISERROR(@ErrorMessage,16,1)
1025 END
1026
1027--- See if we have an Association table for our field of interest
1028
1029 SET @SearchTableName = @Table2 + ''_'' + @Column2 + ''_Association''
1030 SET @CheckTableName = NULL
1031
1032 SELECT @CheckTableName = table_name
1033 FROM information_schema.columns
1034 WHERE table_name = @SearchTableName
1035
1036 IF @CheckTableName IS NULL
1037 BEGIN
1038 SET @ErrorMessage = ''Error 91006: GetDocumentFieldsDynamicAggregateData procedure. There is no Association table found for '' + CONVERT(VARCHAR,@FieldId2) + '' : '' + @Table2 + ''.'' + @Column2
1039 RAISERROR(@ErrorMessage,16,1)
1040 END
1041
1042--- Check to see if the same FieldId was used for both inputs
1043
1044 IF @FieldId1 = @FieldId2
1045 BEGIN
1046 SET @ErrorMessage = ''Error 91006: GetDocumentFieldsDynamicAggregateData procedure. The first and second FieldId parameters have the same value. This is illogical''
1047 RAISERROR(@ErrorMessage,16,1)
1048 END
1049
1050 END --- End of our logic block for FieldId2
1051
1052
1053/*****************************************************************************
1054*
1055* IncludeField input validation starts here
1056*
1057*****************************************************************************/
1058
1059--- Find table and column for IncludeFieldId
1060
1061 IF @IncludeFieldId IS NOT NULL
1062 BEGIN
1063 SELECT @IncludeTable = TableName,
1064 @IncludeColumn = ColumnName
1065 FROM vFieldDefinition
1066 WHERE FieldId = @IncludeFieldId
1067
1068--- See if the Include Field has actually been defined
1069
1070 IF @IncludeTable IS NULL
1071 BEGIN
1072 SET @ErrorMessage = ''Error 91008: GetDocumentFieldsDynamicAggregateData procedure. The Include Field Id you specified - '' + CONVERT(VARCHAR,@IncludeFieldId) + '' can not be found in the FieldDefinition table.''
1073 RAISERROR(@ErrorMessage,16,1)
1074 END
1075
1076--- See if we have an Aggregate table for our field of interest
1077
1078 SET @SearchTableName = @IncludeTable + ''_'' + @IncludeColumn + ''_Aggregate''
1079 SET @CheckTableName = NULL
1080
1081 SELECT @CheckTableName = table_name
1082 FROM information_schema.columns
1083 WHERE table_name = @SearchTableName
1084 IF @CheckTableName IS NULL
1085 BEGIN
1086 SET @IncludeTable = ''#'' + @IncludeTable
1087 SELECT @IncludeColumnDataType = data_type,
1088 @IncludeColumnDataLength = CONVERT(VARCHAR, character_maximum_length )
1089 FROM information_schema.columns
1090 WHERE table_name = ( SELECT TableName from vFieldDefinition WHERE FieldId = @IncludeFieldId )
1091 AND column_name = ( SELECT ColumnName FROM vFieldDefinition WHERE FieldId = @IncludeFieldId )
1092
1093 IF @IncludeColumnDataType IN ( ''VARCHAR'', ''NVARCHAR'', ''CHAR'' )
1094 BEGIN
1095 IF @IncludeColumnDataLength = ''-1'' --- needs to be in quotes because it is a varchar representation of the column
1096 BEGIN
1097 SET @IncludeColumnTypeString = @IncludeColumnDataType + ''(MAX)''
1098 END
1099 IF @IncludeColumnDataLength != ''-1''
1100 BEGIN
1101 SET @IncludeColumnTypeString = @IncludeColumnDataType + ''('' + CONVERT(VARCHAr,@IncludeColumnDataLength) + '')''
1102 END
1103 END
1104
1105-- Need to add logic for date and date time data types. The front end does not support it, but the buildAggregateTahles proc does. Eventually we may
1106-- just re-enable that portion of the system.
1107
1108
1109 SET @PreSQLStatement = ''CREATE TABLE '' + @IncludeTable + ''_'' + @IncludeColumn + ''_Aggregate ( '' + SUBSTRING(@IncludeTable,2,LEN(@IncludeTable) ) + ''_'' + @IncludeColumn + ''Id INT IDENTITY(1,1), '' + @IncludeColumn + '' '' + @IncludeColumnTypeString + '', NumberOfOccurences INT );''
1110-- exec sp_executesql @SQLStatement
1111
1112
1113 SET @PreSQLStatement = @PreSQLStatement + ''
1114 INSERT INTO '' + @IncludeTable + ''_'' + @IncludeColumn + ''_Aggregate ( '' + @IncludeColumn + '', NumberOfOccurences )
1115 SELECT '' + @IncludeColumn + '', COUNT(*) AS NumberOfOccurences
1116 FROM '' + SUBSTRING(@IncludeTable,2,LEN(@IncludeTable)) + ''
1117 GROUP BY '' + @IncludeColumn
1118-- exec sp_executesql @SQLStatement
1119 END
1120
1121--- See if we have an Association table for our field of interest
1122
1123 IF @IncludeTable NOT LIKE ''#%''
1124 BEGIN
1125 SET @SearchTableName = @IncludeTable + ''_'' + @IncludeColumn + ''_Association''
1126 END
1127 IF @IncludeTable LIKE ''#%''
1128 BEGIN
1129 SET @SearchTableName = SUBSTRING(@IncludeTable,2,LEN(@IncludeTable) ) + ''_'' + @IncludeColumn + ''_Association''
1130 END
1131
1132 SET @CheckTableName = NULL
1133
1134 SELECT @CheckTableName = table_name
1135 FROM information_schema.columns
1136 WHERE table_name = @SearchTableName
1137
1138 IF @CheckTableName IS NULL
1139 BEGIN
1140 IF @IncludeTable NOT LIKE ''#%''
1141 BEGIN
1142 SET @IncludeTable = ''#'' + @IncludeTable
1143 END
1144 SET @PreSQLStatement = @PreSQLStatement + ''
1145 CREATE TABLE '' + @IncludeTable + ''_'' + @IncludeColumn + ''_Association ( '' + @IncludeTable + ''_'' + @IncludeColumn + ''_AssociationId INT IDENTITY(1,1), DocId INT, '' + SUBSTRING(@IncludeTable,2,LEN(@IncludeTable) ) + ''_'' + @IncludeColumn + ''Id INT );''
1146
1147 SET @PreSQLStatement = @PreSQLStatement + ''
1148 INSERT INTO '' + @IncludeTable + ''_'' + @IncludeColumn + ''_Association '' +
1149 ''( DocId, '' + SUBSTRING(@IncludeTable,2,LEN(@IncludeTable) ) + ''_'' + @IncludeColumn + ''Id )
1150 SELECT df.DocId, dfa.'' + SUBSTRING(@IncludeTable,2,LEN(@IncludeTable) ) + ''_'' + @IncludeColumn + ''Id
1151 FROM '' + SUBSTRING(@IncludeTable,2,LEN(@IncludeTable) ) + '' df
1152 INNER JOIN '' + @IncludeTable + ''_'' + @IncludeColumn + ''_Aggregate dfa ON df.'' + @IncludeColumn + '' = dfa.'' + @IncludeColumn
1153 END
1154
1155
1156
1157--- When "IncludeFields" are part of the search, the IncludeField serves as a virtual grouping field to create document families on the fly. When an IncludeField
1158--- is part of the search, statistics will be calculated for Documents in the same virtual family, and unique documents in the same virtual family. We build a
1159--- cross reference table to simply this process. (Columns from our field of interest and the IncludeField have to be joined together. Since our Aggregate tables
1160--- are essentially key/value pairs, we need to build cross reference tables.
1161
1162--- Build the Cross Reference table for Field1 and IncludeId
1163
1164
1165 IF @FieldId1 IS NOT NULL
1166 BEGIN
1167 CREATE TABLE #IncludeCR1 ( SourceValue NVARCHAR(MAX), TargetValue NVARCHAR(MAX) )
1168 SET @PreSQLStatement = @PreSQLStatement + ''
1169 DECLARE @SourceTableValues SEARCH.SourceTableValuesTableType
1170 INSERT INTO @SourceTableValues
1171 SELECT '' + @Column1 + '' FROM '' + @Table1 + ''_'' + @Column1 + ''_Aggregate
1172 INSERT INTO #IncludeCR1 ''
1173 IF (@IsFiltered = 1)
1174 BEGIN
1175 SET @PreSQLStatement = @PreSQLStatement + ''
1176 exec GetAggregateFamilyKeysFilter @SourceTable = '''''' + @Table1 + ''_'' + @Column1 + ''_Aggregate'' + '''''',
1177 @SourceTableColumn = '''''' + @Column1 +'''''',
1178 @SourceTableValues = @SourceTableValues,
1179 @TargetTable = '''''' + @IncludeTable + ''_'' + @IncludeColumn + ''_Aggregate'' + '''''',
1180 @TargetTableColumn = '''''' + @IncludeColumn + ''''''''
1181 END
1182 IF (@IsFiltered = 0)
1183 BEGIN
1184 SET @PreSQLStatement = @PreSQLStatement + ''
1185 exec GetAggregateFamilyKeys @SourceTable = '''''' + @Table1 + ''_'' + @Column1 + ''_Aggregate'' + '''''',
1186 @SourceTableColumn = '''''' + @Column1 +'''''',
1187 @SourceTableValues = @SourceTableValues,
1188 @TargetTable = '''''' + @IncludeTable + ''_'' + @IncludeColumn + ''_Aggregate'' + '''''',
1189 @TargetTableColumn = '''''' + @IncludeColumn + ''''''''
1190 END
1191
1192 IF @ResultsId IS NOT NULL
1193 AND @UserKey IS NOT NULL
1194 BEGIN
1195 SET @PreSQLStatement = @PreSQLStatement + '',
1196 @UserKey = '' + CONVERT(VARCHAR,@UserKey) + '',
1197 @ResultsId = '' + CONVERT(VARCHAR,@ResultsId)
1198 END
1199
1200 SET @Parameters = N''@SourceTableValues Search.SourceTableValuesTableType ''
1201-- EXEC sp_executesql @SQLStatement
1202
1203 END
1204
1205 IF @FieldId2 IS NOT NULL
1206 BEGIN
1207 CREATE TABLE #IncludeCR2 ( SourceValue NVARCHAR(MAX), TargetValue NVARCHAR(MAX) )
1208 SET @PreSQLStatement = @PreSQLStatement + ''
1209 INSERT INTO @SourceTableValues
1210 SELECT '' + @Column2 + '' FROM '' + @Table2 + ''_'' + @Column2 + ''_Aggregate
1211
1212 INSERT INTO #IncludeCR2 ''
1213 IF (@IsFiltered = 1)
1214 BEGIN
1215 SET @PreSQLStatement = @PreSQLStatement + ''
1216 exec GetAggregateFamilyKeysFilter @SourceTable = '''''' + @Table2 + ''_'' + @Column2 + ''_Aggregate'' + '''''',
1217 @SourceTableColumn = '''''' + @Column2 +'''''',
1218 @SourceTableValues = @SourceTableValues,
1219 @TargetTable = '''''' + @IncludeTable + ''_'' + @IncludeColumn + ''_Aggregate'' + '''''',
1220 @TargetTableColumn = '''''' + @IncludeColumn + ''''''''
1221 END
1222 IF (@IsFiltered = 0)
1223 BEGIN
1224 SET @PreSQLStatement = @PreSQLStatement + ''
1225 exec GetAggregateFamilyKeys @SourceTable = '''''' + @Table2 + ''_'' + @Column2 + ''_Aggregate'' + '''''',
1226 @SourceTableColumn = '''''' + @Column2 +'''''',
1227 @SourceTableValues = @SourceTableValues,
1228 @TargetTable = '''''' + @IncludeTable + ''_'' + @IncludeColumn + ''_Aggregate'' + '''''',
1229 @TargetTableColumn = '''''' + @IncludeColumn + ''''''''
1230 END
1231 IF @ResultsId IS NOT NULL
1232 AND @UserKey IS NOT NULL
1233 BEGIN
1234 SET @PreSQLStatement = @PreSQLStatement + '',
1235 @UserKey = '' + CONVERT(VARCHAR,@UserKey) + '',
1236 @ResultsId = '' + CONVERT(VARCHAR,@ResultsId)
1237 END
1238
1239 SET @Parameters = N''@SourceTableValues Search.SourceTableValuesTableType ''
1240-- EXEC sp_executesql @SQLStatement
1241
1242 END
1243
1244
1245 END -- end of logic for when an IncludeFieldId is provided
1246
1247
1248
1249--- Check for our Search Results table name. By convention, the searh results are always placed in a table called
1250--- SearchResults in the UserTables schema. The SearchResults table has a UserKey (from ActivityTracking.Dim_User
1251--- that is used as a suffix
1252
1253
1254 IF @UserKey IS NOT NULL
1255 BEGIN
1256 SET @SearchTableName = ''SearchResults'' + CONVERT(VARCHAR,@UserKey)
1257
1258--- Now that we have a table name to use for our input, make sure that it really exists
1259
1260 SET @CheckTableName = NULL
1261
1262 SELECT @CheckTableName = table_name
1263 FROM information_schema.columns
1264 WHERE table_schema = ''UserTables''
1265 AND table_name = @SearchTableName
1266
1267 IF @CheckTableName IS NULL
1268 BEGIN
1269 SET @ErrorMessage = ''Error 91007: GetDocumentFieldsDynamicAggregateData procedure. There is no SearchResults table for user - '' + CONVERT(VARCHAR,@UserKey) + '' '' + @UserName
1270 RAISERROR(@ErrorMessage,16,1)
1271 END
1272 END --- End of IF @UserKey IS NOT NULL conditional
1273
1274
1275--- Build our SearchResultsInclude table name. By convention, the searh results are always placed in a table called
1276--- SearchResults in the UserTables schema. The SearchResultsInclude table has a UserKey (from ActivityTracking.Dim_User
1277--- that is used as a suffix
1278
1279 IF @UserKey IS NOT NULL
1280 BEGIN
1281 SET @SearchResultsIncludeTableName = ''SearchResultsInclude'' + CONVERT(VARCHAR,@UserKey)
1282
1283--- Now that we have a table name to use for our input, make sure that it really exists
1284
1285 SET @CheckTableName = NULL
1286
1287 SELECT @CheckTableName = table_name
1288 FROM information_schema.columns
1289 WHERE table_schema = ''UserTables''
1290 AND table_name = @SearchResultsIncludeTableName
1291
1292 IF @CheckTableName IS NULL
1293 BEGIN
1294 SET @ErrorMessage = ''Error 91008: GetDocumentFieldsDynamicAggregateData procedure. There is no SearchResultsInclude table for user - '' + CONVERT(VARCHAR,@UserKey) + '' '' + @UserName
1295 RAISERROR(@ErrorMessage,16,1)
1296 END
1297 END --- End of IF @UserKey IS NOT NULL conditional
1298
1299
1300--- Build our query
1301
1302-- IF @IncludeFieldId IS NULL
1303-- BEGIN
1304 SET @SQLStatement = @SQLStatement + ''
1305 SELECT COUNT(*) Documents, '' + @Column1
1306
1307
1308 IF @Column2 IS NOT NULL
1309 BEGIN
1310 SET @SQLStatement = @SQLStatement + '','' + @Column2 + ''
1311''
1312 END
1313
1314 SET @SQLStatement = @SQLStatement + ''
1315 FROM '' + @Table1 + ''_'' + @Column1 + ''_Aggregate dfa1,
1316 '' + @Table1 + ''_'' + @Column1 + ''_Association assoc1''
1317 IF @Column2 IS NOT NULL
1318 BEGIN
1319 SET @SQLStatement = @SQLStatement + '','' + ''
1320 '' + @Table2 + ''_'' + @Column2 + ''_Aggregate dfa2,
1321 '' + @Table2 + ''_'' + @Column2 + ''_Association assoc2 ''
1322
1323 END
1324
1325 SET @SQLStatement = @SQLStatement + ''
1326WHERE dfa1.'' + @Table1 + ''_'' + @Column1 + ''Id = assoc1.'' + @Table1 + ''_'' + @Column1 + ''Id
1327''
1328 IF @Column2 IS NOT NULL
1329 BEGIN
1330 SET @SQLStatement = @SQLStatement + ''
1331AND dfa2.'' + @Table2 + ''_'' + @Column2 + ''Id = assoc2.'' + @Table2 + ''_'' + @Column2 + ''Id
1332''
1333
1334 END
1335
1336 IF @UserKey IS NOT NULL
1337 AND @ResultsId IS NOT NULL
1338 BEGIN
1339 IF (@IsFiltered = 1)
1340 BEGIN
1341 SET @SQLStatement = @SQLStatement + ''
1342 AND assoc1.DocId IN ( SELECT DocId
1343 FROM #FilteredResults'' + ''
1344 WHERE ResultsId = 999 ) ''
1345 END
1346 IF (@IsFiltered = 0)
1347 BEGIN
1348 SET @SQLStatement = @SQLStatement + ''
1349 AND assoc1.DocId IN ( SELECT DocId
1350 FROM UserTables.'' + @SearchTableName + ''
1351 WHERE ResultsId = '' + CONVERT(VARCHAR,@ResultsId) + '') ''
1352 END
1353 IF @IncludeFieldId IS NOT NULL
1354 BEGIN
1355 SET @SQLStatement = @SQLStatement + ''
1356 AND assoc1.DocId NOT IN ( SELECT DocId
1357 FROM UserTables.'' + @SearchResultsIncludeTableName +
1358 '' WHERE ResultsId = '' + CONVERT(VARCHAR,@ResultsId) + '') ''
1359 END
1360 END
1361
1362 IF @Column2 IS NOT NULL
1363 BEGIN
1364 IF @UserKey IS NOT NULL
1365 AND @ResultsId IS NOT NULL
1366 BEGIN
1367 IF (@IsFiltered = 1)
1368 BEGIN
1369 SET @SQLStatement = @SQLStatement + ''
1370 AND assoc2.DocId IN ( SELECT DocId
1371 FROM #FilteredResults '' + ''
1372 WHERE ResultsId = 999 ) ''
1373 END
1374 IF (@IsFiltered = 0)
1375 BEGIN
1376 SET @SQLStatement = @SQLStatement + ''
1377 AND assoc2.DocId IN ( SELECT DocId
1378 FROM UserTables.'' + @SearchTableName + ''
1379 WHERE ResultsId = '' + CONVERT(VARCHAR,@ResultsId) + '') ''
1380 END
1381 IF @IncludeFieldId IS NOT NULL
1382 BEGIN
1383 SET @SQLStatement = @SQLStatement + ''
1384 AND assoc1.DocId NOT IN ( SELECT DocId FROM UserTables.'' + @SearchResultsIncludeTableName + '' WHERE ResultsId = '' + CONVERT(VARCHAR,@ResultsId) + '')''
1385 END
1386 END
1387
1388 SET @SQLStatement = @SQLStatement + ''AND assoc1.DocId = assoc2.DocId
1389''
1390 END --- End of @Column2 IS NOT NULL conditional
1391--END ----- new stuff
1392
1393/********************************************************************************************************************/
1394-- Column 2 is null
1395/********************************************************************************************************************/
1396
1397 IF @Column2 IS NULL
1398 AND @IncludeFieldId IS NOT NULL
1399 BEGIN
1400 SET @SQLStatement = ''
1401 SELECT Raw.'' + @Column1 + '', Documents, DocsWithFamily, UniqueFamilyDocsWithFamily FROM ( '' + @SQLStatement + ''GROUP BY '' + @Column1 + '' ) Raw
1402LEFT OUTER JOIN
1403( SELECT NumberOfOccurences AS DocsWithFamily, SourceValue
1404 FROM ''
1405 IF @IncludeFieldId IS NOT NULL
1406 BEGIN
1407 SET @SQLStatement = @SQLStatement + ''
1408(
1409 SELECT SUM(NumberOfOccurences) as NumberOfOccurences, SourceValue
1410 FROM '' + @IncludeTable + ''_'' + @IncludeColumn + ''_Aggregate a, '' + ''
1411 #IncludeCR1 b
1412 WHERE a.'' + @IncludeColumn + '' = b.TargetValue
1413 GROUP BY SourceValue
1414
1415) SummaryData ) FamilyOccurences
1416 ON Raw.'' + @Column1 + '' = FamilyOccurences.SourceValue
1417''
1418--- Begining of logic for Unique Family Occurences when only one FieldId is supplied
1419 SET @SQLStatement = @SQLStatement + ''
1420LEFT OUTER JOIN
1421(
1422 SELECT COUNT(*) AS UniqueFamilyDocsWithFamily, F1'' + @Column1 + '' AS '' + @Column1 + '' FROM (
1423 SELECT FieldOfInterest, ia.'' + @IncludeColumn + '' AS Inc'' + @IncludeColumn + '', ic.'' + @Column1 + '' AS F1'' + @Column1 + '' FROM
1424 ( SELECT Inc'' + @IncludeColumn + '' AS FieldOfInterest FROM (
1425 SELECT COUNT(*) AS DetailCount, Inc'' + @IncludeColumn + '', F1'' + @Column1 + '' FROM (
1426 SELECT b.DocId, a.'' + @IncludeColumn + '' as Inc'' + @IncludeColumn +'', c.'' + @Column1 + '' as F1'' + @Column1 + ''
1427 FROM '' + @IncludeTable + ''_'' + @IncludeColumn + ''_Aggregate a,
1428 '' + @IncludeTable + ''_'' + @IncludeColumn + ''_Association b,
1429 '' + @Table1 + ''_'' + @Column1 + ''_Aggregate c,
1430 '' + @Table1 + ''_'' + @Column1 + ''_Association d
1431 WHERE a.'' + REPLACE(@IncludeTable,''#'','''') + ''_'' + @IncludeColumn + ''Id = b.'' + REPLACE(@IncludeTable,''#'','''') + ''_'' + @IncludeColumn + ''Id
1432 AND c.'' + @Table1 + ''_'' + @Column1 + ''Id = d.'' + @Table1 + ''_'' + @Column1 + ''Id
1433 AND b.DocId = d.DocId ) DetailData,
1434 #IncludeCR1 b
1435 WHERE DetailData.Inc'' + @IncludeColumn + '' = TargetValue
1436 GROUP BY DetailData.Inc'' + @IncludeColumn + '', DetailData.F1'' + @Column1 + '') SummaryData
1437 GROUP BY Inc'' + @IncludeColumn + ''
1438 HAVING COUNT(*) = 1 ) IncludeField,
1439 '' + @IncludeTable + ''_'' + @IncludeColumn + ''_Aggregate ia,
1440 '' + @IncludeTable + ''_'' + @IncludeColumn + ''_Association ib,
1441 '' + @Table1 + ''_'' + @Column1 + ''_Aggregate ic,
1442 '' + @Table1 + ''_'' + @Column1 + ''_Association id
1443 WHERE ia.'' + REPLACE(@IncludeTable,''#'','''') + ''_'' + @IncludeColumn + ''Id = ib.'' + REPLACE(@IncludeTable,''#'','''') + ''_'' + @IncludeColumn + ''Id
1444 AND ic.'' + @Table1 + ''_'' + @Column1 + ''Id = id.'' + @Table1 + ''_'' + @Column1 + ''Id
1445 AND ib.DocId = id.DocId
1446 AND FieldOfInterest = ia.'' + @IncludeColumn + '' ) RecordsOfInterest
1447 GROUP BY F1'' + @Column1 + ''
1448
1449) UniqueFamilyResults
1450 ON Raw.'' + @Column1 + '' = UniqueFamilyResults.'' + @Column1 + ''
1451''
1452 END ---- End of logic for @IncludeFieldId IS NOT NULL within logic for when @Column2 is null
1453
1454 END --- End of logic for when @Column2 IS NULL
1455
1456/*******************************************************************************************/
1457---- Column 2 is not null
1458/********************************************************************************************/
1459
1460 IF @Column2 IS NOT NULL
1461 AND @IncludeFieldId IS NOT NULL
1462 BEGIN
1463 SET @SQLStatement = ''
1464SELECT Raw.'' + @Column1 + '', Raw.'' + @Column2 + '', Documents, DocsWithFamily, UniqueFamilyDocsWithFamily FROM ( '' + @SQLStatement + ''GROUP BY '' + @Column1 + '', '' + @Column2 + '' ) Raw
1465LEFT OUTER JOIN
1466( SELECT NumberOfOccurences AS DocsWithFamily, Field1SourceValue, Field2SourceValue
1467 FROM ''
1468 IF @IncludeFieldId IS NOT NULL
1469 BEGIN
1470 SET @SQLStatement = @SQLStatement + ''
1471(
1472 SELECT SUM(NumberOfOccurences) as NumberOfOccurences, b.SourceValue as Field1SourceValue, c.SourceValue as Field2SourceValue
1473 FROM '' + @IncludeTable + ''_'' + @IncludeColumn + ''_Aggregate a, '' + ''
1474 #IncludeCR1 b,
1475 #IncludeCR2 c
1476 WHERE a.'' + @IncludeColumn + '' = b.TargetValue
1477 AND a.'' + @IncludeColumn + '' = c.TargetValue
1478 GROUP BY b.SourceValue, c.SourceValue
1479
1480) SummaryData ) FamilyOccurences
1481 ON Raw.'' + @Column1 + '' = FamilyOccurences.Field1SourceValue AND Raw.'' + @Column2 + '' = FamilyOccurences.Field2SourceValue
1482''
1483 END
1484--- Begining of logic for Unique Family Occurences
1485 IF @Column2 IS NOT NULL
1486 BEGIN
1487 SET @SQLStatement = @SQLStatement + ''
1488LEFT OUTER JOIN
1489(
1490 SELECT COUNT(*) AS UniqueFamilyDocsWithFamily, F1'' + @Column1 + '', F2'' + @Column2 + '' FROM (
1491 SELECT FieldOfInterest, ia.'' + @IncludeColumn + '' AS Inc'' + @IncludeColumn + '', ic.'' + @Column1 + '' AS F1'' + @Column1 + '', ie.'' + @Column2 + '' as F2'' + @Column2 + '' FROM
1492 ( SELECT Inc'' + @IncludeColumn + '' AS FieldOfInterest FROM (
1493 SELECT COUNT(*) AS DetailCount, Inc'' + @IncludeColumn + '', F1'' + @Column1 + '', F2'' + @Column2 + '' FROM (
1494 SELECT b.DocId, a.'' + @IncludeColumn + '' as Inc'' + @IncludeColumn + '', c.'' + @Column1 + '' AS F1'' + @Column1 + '', e.'' + @Column2 + '' AS F2'' + @Column2 + ''
1495 FROM '' + @IncludeTable + ''_'' + @IncludeColumn + ''_Aggregate a,
1496 '' + @IncludeTable + ''_'' + @IncludeColumn + ''_Association b,
1497 '' + @Table1 + ''_'' + @Column1 + ''_Aggregate c,
1498 '' + @Table1 + ''_'' + @Column1 + ''_Association d,
1499 '' + @Table2 + ''_'' + @Column2 + ''_Aggregate e,
1500 '' + @Table2 + ''_'' + @Column2 + ''_Association f
1501 WHERE a.'' + REPLACE(@IncludeTable,''#'','''') + ''_'' + @IncludeColumn + ''Id = b.'' + REPLACE(@IncludeTable,''#'','''') + ''_'' + @IncludeColumn + ''Id
1502 AND c.'' + @Table1 + ''_'' + @Column1 + ''Id = d.'' + @Table1 + ''_'' + @Column1 + ''Id
1503 AND e.'' + @Table2 + ''_'' + @Column2 + ''Id = f.'' + @Table2 + ''_'' + @Column2 + ''Id
1504 AND b.DocId = d.DocId
1505 AND b.DocId = f.DocId ) DetailData,
1506 #IncludeCR1 b,
1507 #IncludeCR2 c
1508 WHERE DetailData.Inc'' + @IncludeColumn + '' = b.TargetValue
1509 AND DetailData.Inc'' + @IncludeColumn + '' = c.TargetValue
1510 GROUP BY DetailData.Inc'' + @IncludeColumn + '', DetailData.F1'' + @Column1 + '', F2'' + @Column2 + '') SummaryData
1511 GROUP BY Inc'' + @IncludeColumn + ''
1512 HAVING COUNT(*) = 1 ) IncludeField,
1513 '' + @IncludeTable + ''_'' + @IncludeColumn + ''_Aggregate ia,
1514 '' + @IncludeTable + ''_'' + @IncludeColumn + ''_Association ib,
1515 '' + @Table1 + ''_'' + @Column1 + ''_Aggregate ic,
1516 '' + @Table1 + ''_'' + @Column1 + ''_Association id,
1517 '' + @Table2 + ''_'' + @Column2 + ''_Aggregate ie,
1518 '' + @Table2 + ''_'' + @Column2 + ''_Association ig
1519 WHERE ia.'' + REPLACE(@IncludeTable,''#'','''') + ''_'' + @IncludeColumn + ''Id = ib.'' + REPLACE(@IncludeTable,''#'','''') + ''_'' + @IncludeColumn + ''Id
1520 AND ic.'' + @Table1 + ''_'' + @Column1 + ''Id = id.'' + @Table1 + ''_'' + @Column1 + ''Id
1521 AND ie.'' + @Table2 + ''_'' + @Column2 + ''Id = ig.'' + @Table2 + ''_'' + @Column2 + ''Id
1522 AND ib.DocId = id.DocId
1523 AND ib.DocId = ig.DocId
1524 AND FieldOfInterest = ia.'' + @IncludeColumn + '' ) RecordsOfInterest
1525 GROUP BY F1'' + @Column1 + '', F2'' + @Column2 + ''
1526) UniqueFamilyResults
1527 ON Raw.'' + @Column1 + '' = UniqueFamilyResults.F1'' + @Column1 + '' AND Raw.'' + @Column2 + '' = UniqueFamilyResults.F2'' + @Column2 + ''
1528 ORDER BY 1, 2''
1529 END
1530 END --- End of logic for when Column2 is not null
1531
1532 IF @IncludeFieldId IS NULL
1533 BEGIN
1534 IF @Column2 IS NULL
1535 BEGIN
1536 SET @SQLStatement = ''SELECT '' + @Column1 + '', Documents, 0 as DocsWithFamily, 0 as UniqueFamilyDocsWithFamily
1537 FROM ( '' + @SQLStatement + ''
1538 GROUP BY '' + @Column1 + ''
1539 ) CombinedStatement
1540 ORDER BY '' + @Column1
1541 END
1542 ELSE
1543 BEGIN
1544 SET @SQLStatement = ''SELECT '' + @Column1 + '', '' + @Column2 + '', Documents, 0 as DocsWithFamily, 0 as UniqueFamilyDocsWithFamily
1545 FROM ( '' + @SQLStatement + ''
1546 GROUP BY '' + @Column1 + '' , '' + @Column2 + ''
1547 ) CombinedStatement
1548 ORDER BY '' + @Column1 + '', '' + @Column2
1549 END
1550
1551 END
1552 SET @CompleteSQLStatement = @PreSQLStatement + @SQLStatement
1553--insert into sqlTrapper SELECT @CompleteSQLStatement
1554 EXEC sp_executesql @CompleteSQLStatement
1555
1556COMMIT
1557END TRY
1558BEGIN CATCH
1559
1560 IF @@trancount > 0
1561 BEGIN
1562 ROLLBACK TRANSACTION
1563 END
1564 IF @ErrorMessage IS NULL
1565 BEGIN
1566 SET @ErrorMessage = Error_Message() + '': Error 91999: GetDocumentFieldsDynamicAggregateData procedure. Unknown error - contact technical support''
1567 END
1568 RAISERROR(@ErrorMessage,16,1)
1569 RETURN 52002
1570END CATCH'
1571
1572 EXEC sp_executesql @contextstring, N'@SQL nvarchar(max)', @SQL=@SQL
1573
1574 Set @SQL = '
1575 ALTER PROCEDURE [Reporting].[PrivilegedReport]
1576(
1577 @Tags dbo.IdTableType READONLY
1578 )
1579AS
1580begin
1581 SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
1582
1583 set nocount on
1584 DECLARE @DisplayId int;
1585 DECLARE @ReportId int;
1586
1587 /* get the displayId for the privileged report */
1588 SELECT @DisplayId = DisplayId
1589 FROM dbo.FieldDisplays
1590 WHERE ReportId = 13;
1591
1592 create TABLE #DocIds (DocId int NOT NULL PRIMARY KEY);
1593
1594 /* Get privileged documents */
1595 INSERT INTO #DocIds (DocId)
1596 SELECT
1597 DISTINCT dt.DocId
1598 FROM dbo.DocumentTags dt
1599 INNER JOIN
1600 (
1601 SELECT
1602 t.TagId
1603 FROM @Tags AS tt
1604 JOIN dbo.Tags t ON t.TagId = tt.Id
1605 ) t
1606 ON t.TagId = dt.TagId ;
1607
1608 SELECT [UserName]
1609 ,[PrivateTag]
1610 ,[TagId]
1611 ,[ParentId]
1612 ,[Level]
1613 ,[IsLeafTag]
1614 ,[TagGroupId]
1615 ,[TagGroupName]
1616 ,[TagName]
1617 ,[TagIdPath]
1618 ,[TagPath]
1619 into #TagHierarchy
1620 from dbo.vTagHierarchy
1621
1622 SELECT
1623 ReportColumnName =
1624 CASE WHEN i.FieldDisplayItemTypeId = 1 THEN f.FieldName
1625 WHEN i.FieldDisplayItemTypeId = 2 THEN g.TagGroupName
1626 WHEN i.FieldDisplayItemTypeId = 3 THEN t.TagGroupName + t.TagPath
1627 ELSE '''' END
1628 ,i.itemId
1629 ,i.DisplayOrder
1630 ,i.FieldDisplayItemTypeId
1631 into #OutputFields
1632 FROM dbo.FieldDisplayItems i
1633 LEFT OUTER JOIN dbo.FieldDefinition f ON f.FieldId = i.ItemId
1634 AND i.FieldDisplayItemTypeId = 1 --fields
1635 LEFT OUTER JOIN dbo.TagGroups g ON g.TagGroupId = i.ItemId
1636 AND i.FieldDisplayItemTypeId = 2 --tag groups
1637 LEFT OUTER JOIN #TagHierarchy t ON t.TagId = i.ItemId
1638 AND i.FieldDisplayItemTypeId = 3 --tags
1639 WHERE i.DisplayId = @DisplayId;
1640
1641 -- Add FieldDisplay tags from tag groups to temporary table
1642 SELECT t.TagGroupId, t.TagGroupName, t.TagId, t.TagName
1643 into #DisplayTagGroup
1644 FROM #TagHierarchy t
1645 INNER JOIN dbo.FieldDisplayItems i ON i.ItemId = t.TagGroupId
1646 AND i.FieldDisplayItemTypeId = 2
1647 WHERE i.DisplayId = @DisplayId;
1648
1649 --query up the tag information for the documents
1650
1651 SELECT t.TagId, t.TagName
1652 into #DisplayTag
1653 FROM #TagHierarchy t
1654 INNER JOIN dbo.FieldDisplayItems i ON i.ItemId = t.TagId
1655 AND i.FieldDisplayItemTypeId = 3
1656 WHERE i.DisplayId = @DisplayId;
1657
1658 select
1659 d.DocId
1660 ,disptag.TagId
1661 ,dispTag.TagName
1662 into #DocumentTags
1663 from #DocIds d
1664 inner join dbo.DocumentTags dt ON dt.DocId = d.DocId
1665 inner join #DisplayTag dispTag on dispTag.TagId = dt.TagId
1666
1667
1668 --pivot multiple tags in a tag group to a single row.
1669
1670 SELECT
1671 d.DocId
1672 , t.TagGroupId
1673 , t.TagGroupName
1674 , t.TagName
1675 into #DocumentTagGroup
1676 FROM #DocIds d
1677 INNER JOIN dbo.DocumentTags dt ON dt.DocId = d.DocId
1678 INNER JOIN #DisplayTagGroup t ON t.TagId = dt.TagId;
1679
1680 declare @delimiter varchar(10)
1681 set @delimiter = ''; ''
1682
1683 SELECT
1684 t1.DocId
1685 ,t1.TagGroupId
1686 ,t1.TagGroupName
1687 ,Tags = STUFF((Select @delimiter + CAST(t2.TagName AS VARCHAR(1000))
1688 FROM #DocumentTagGroup t2
1689 WHERE t1.DocId = t2.DocId
1690 and t1.TagGroupName = t2.TagGroupName
1691 FOR XML Path ('''')
1692 ),1,len(@delimiter),'''')
1693 into #DocumentTagGroup_TagPivot
1694 FROM #DocumentTagGroup t1
1695 GROUP BY
1696 t1.DocId
1697 ,t1.TagGroupId
1698 ,t1.TagGroupName
1699
1700 -----------------------------------------------------------------
1701 -----------------------------------------------------------------
1702 DECLARE @SQLStatement NVARCHAR(MAX) = ''''
1703 DECLARE @dfColumns NVARCHAR(MAX) = ''''
1704 DECLARE @dfTables NVARCHAR(MAX) = ''''
1705
1706 ;WITH dfStatement
1707 AS
1708 (
1709 SELECT
1710 ''df''+SUBSTRING(TableName,CHARINDEX(''_'',TableName)+1,4) AS Alias
1711 ,QUOTENAME(o.ReportColumnName) + ''= '' + (''df''+SUBSTRING(TableName,CHARINDEX(''_'',TableName)+1,4)+''.''+QUOTENAME(ColumnName)) AS dfColumns
1712 ,''LEFT JOIN '' + QUOTENAME(TableName) + '' df'' + (SUBSTRING(TableName,CHARINDEX(''_'',TableName)+1,4)) + '' ON df'' + (SUBSTRING(TableName,CHARINDEX(''_'',TableName)+1,4)) + ''.DocID = d.DocId '' AS dfTables
1713 ,o.DisplayOrder
1714 FROM #OutputFields o
1715 INNER JOIN vFieldDefinition fd
1716 ON o.FieldDisplayItemTypeId = 1
1717 AND o.ItemId = fd.FieldId
1718
1719 )
1720 SELECT
1721 @dfColumns = '',''+STUFF((
1722 SELECT '', '' + dfColumns
1723 FROM dfStatement
1724 ORDER BY DisplayOrder
1725 FOR XML Path ('''')
1726 ),1,2,''''),
1727 @dfTables = STUFF((
1728 SELECT DISTINCT '' ''+ dfTables
1729 FROM dfStatement
1730 FOR XML Path ('''')
1731 ),1,1,'''')
1732 -----------------------------------------------------------------
1733 -----------------------------------------------------------------
1734 declare
1735 @SqlColumns nvarchar(max) = ''''
1736 ,@SqlTables nvarchar(max) = ''''
1737 ,@SqlString nvarchar(MAX) = ''''
1738 ,@OutputColCursor cursor
1739 ,@ReportColumnName nvarchar(max)
1740 ,@ItemId int
1741 ,@FieldDisplayItemTypeId int
1742 ;
1743 set @OutputColCursor = cursor
1744 for
1745 select
1746 outfields.ReportColumnName
1747 ,outfields.ItemId
1748 ,outfields.FieldDisplayItemTypeId
1749 from #OutputFields outfields
1750 where FieldDisplayItemTypeId = 2
1751 order by DisplayOrder
1752
1753 open @OutputColCursor
1754
1755 fetch next from @OutputColCursor
1756 into @ReportColumnName
1757 ,@ItemId
1758 ,@FieldDisplayItemTypeId
1759
1760 --loop through the reporting columns and build the final output query.
1761 while @@FETCH_STATUS = 0
1762 begin
1763 declare @ItemIdString varchar(256)
1764 set @ItemIdString = cast(@ItemId as varchar(256))
1765
1766
1767
1768 if @FieldDisplayItemTypeId = 2 --tag group
1769 begin
1770 --,[Confidentiality] = isnull(tgp_6.Tags,'''')
1771 --left outer join #DocumentTagGroup_TagPivot tgp_6 on tgp_6.DocId = docs.DocId and tgp_6.TagGroupId = 6
1772 set @SqlColumns += ''
1773 ,['' + @ReportColumnName + ''] = isnull(tgp_'' + @ItemIdString + ''.Tags,'''''''')''
1774 set @SqlTables += ''
1775 left outer join #DocumentTagGroup_TagPivot tgp_'' + @ItemIdString + '' on tgp_'' + @ItemIdString + ''.DocId = d.DocId and tgp_'' + @ItemIdString + ''.TagGroupId = '' + @ItemIdString
1776
1777 end
1778
1779
1780 fetch next from @OutputColCursor
1781 into @ReportColumnName
1782 ,@ItemId
1783 ,@FieldDisplayItemTypeId
1784 end
1785 close @OutputColCursor
1786 -----------------------------------------------------------------
1787 -----------------------------------------------------------------
1788 DECLARE @TagColumns NVARCHAR(MAX) = ''''
1789 DECLARE @tColumns BIT = 0
1790 IF EXISTS (SELECT * FROM #OutputFields WHERE FieldDisplayItemTypeID = 3)
1791 SET @tColumns = 1
1792
1793 IF (@tColumns = 1)
1794 BEGIN
1795 SET @TagColumns = STUFF((SELECT DISTINCT '',['' + ReportColumnName + '']''
1796 FROM #OutputFields
1797 WHERE FieldDisplayitemTypeID = 3
1798 FOR XML PATH('''')),1,1,'''')
1799 DECLARE @TagColumnTable NVARCHAR(MAX)
1800 SET @TagColumnTable = STUFF((SELECT DISTINCT '',['' + ReportColumnName + ''] NVARCHAR(358)''
1801 FROM #OutputFields
1802 WHERE FieldDisplayitemTypeID = 3
1803 FOR XML PATH('''')),1,1,'''')
1804
1805 IF OBJECT_ID(N''tempdb..#DocumentTags_Pivot'') IS NOT NULL
1806 DROP TABLE #DocumentTags_Pivot
1807 CREATE TABLE #DocumentTags_Pivot (DocID INT NOT NULL)
1808 EXECUTE
1809 (N''
1810 ALTER TABLE #DocumentTags_Pivot ADD
1811 ''+ @TagColumnTable +''
1812 '')
1813 INSERT INTO #DocumentTags_Pivot
1814 EXECUTE
1815 (N''
1816 SELECT
1817 DocId
1818 , '' + @TagColumns + N''
1819 FROM
1820 (
1821 SELECT DISTINCT
1822 dt.DocID
1823 ,ReportColumnName
1824 ,dt.TagName
1825 FROM #DocumentTags dt
1826 LEFT JOIN #OutputFields o
1827 ON dt.TagID = o.ItemID
1828 AND o.FieldDisplayItemTypeId = 3
1829 ) PivotSource
1830 PIVOT
1831 (
1832 MAX(TagName)
1833 FOR ReportColumnName IN ('' + @TagColumns + N'')
1834 ) PivotTable
1835 '')
1836 CREATE CLUSTERED INDEX tix_tDocID ON #DocumentTags_Pivot (DocID);
1837 END
1838 -----------------------------------------------------------------
1839 -----------------------------------------------------------------
1840 --assemble the query
1841 SET @SqlString = ''SELECT
1842 d.DocId
1843 ''+ CASE WHEN LEN(@dfColumns) > 1 THEN @dfColumns ELSE '''''''' END +''
1844 ''+ @SqlColumns + ''
1845 ''+ CASE WHEN @tColumns = 1 THEN '',tp.'' + REPLACE(@TagColumns,'','','',tp.'') ELSE '''''''' END +''
1846 FROM #DocIds d
1847 ''+ CASE WHEN LEN(@dfColumns) > 1 THEN @dfTables ELSE '''''''' END +''
1848 ''+ @SqlTables +''
1849 ''+ CASE WHEN @tColumns = 1 THEN ''LEFT JOIN #DocumentTags_Pivot tp ON d.DocId = tp.DocId '' ELSE '''''''' END +''
1850 order by d.DocId''
1851
1852 --print @sql
1853 exec (@SQLString)
1854 ---------------------------
1855END'
1856
1857 EXEC sp_executesql @contextstring, N'@SQL nvarchar(max)', @SQL=@SQL
1858
1859 Set @SQL = 'ALTER PROCEDURE [Review].[GetBatch]
1860 @BatchId int
1861AS
1862WITH CountsTable
1863AS
1864(
1865 select
1866 bd.batchId,
1867 DocumentCount = count(bd.BatchId),
1868 UnreviewedCount = Sum(case when bd.ReviewStatusId = 0 then 1 else 0 end),
1869 ReviewedCount = Sum(case when bd.ReviewStatusId = 1 then 1 else 0 end),
1870 OnHoldCount = Sum(case when bd.ReviewStatusId = 2 then 1 else 0 end)
1871 from review.BatchDocument bd
1872 where bd.batchId = @BatchId
1873 group by bd.BatchId
1874)
1875 SELECT
1876 r.[ReviewPassId]
1877 , r.[ReviewPassName]
1878 , b.BatchId
1879 , BatchNumber = CASE WHEN r.BatchPrefix <> '''' THEN r.BatchPrefix + REPLICATE( ''0'', 6 - LEN(b.BatchNumber)) + CONVERT(varchar(20), b.BatchNumber)
1880 ELSE CONVERT(varchar(10), b.BatchNumber) END
1881 , DocumentCount = ISNULL(DocumentCount,0)
1882 , UnreviewedCount = ISNULL(UnreviewedCount,0)
1883 , ReviewedCount = ISNULL(ReviewedCount,0)
1884 , OnHoldCount = ISNULL(OnHoldCount,0)
1885 , b.[BatchStatusId]
1886 , DateOpened = ISNULL(DateOpened, '''')
1887 , DateAssigned = ISNULL(DateAssigned, '''')
1888 , LastCheckIn = ISNULL(LastCheckIn, '''')
1889 , DateClosed = ISNULL(DateClosed, '''')
1890 , LastActivity = ISNULL(LastActivity, '''')
1891 , SpecialInstructions = ISNULL(b.SpecialInstructions, '''')
1892 , b.Priority
1893 , GroupByValue = ISNULL(b.GroupByValue, '''')
1894 , AssignedUserName = ISNULL(u.UserName,'''')
1895 , b.DateCreated
1896 FROM [Review].Batch b
1897 LEFT JOIN ActivityTracking.DIM_User u on u.UserKey =b.AssignedUserKey
1898 INNER JOIN [Review].[ReviewPass] r ON r.[ReviewPassId] = b.[ReviewPassId]
1899 LEFT JOIN CountsTable ON CountsTable.BatchId = b.BatchId
1900 WHERE b.BatchId = @BatchId'
1901
1902 EXEC sp_executesql @contextstring, N'@SQL nvarchar(max)', @SQL=@SQL
1903
1904 Set @SQL = '
1905 ALTER PROCEDURE [Review].[GetBatches]
1906 @UserName nvarchar(256)
1907, @IsAdmin bit
1908, @HasExtendedPrivilege bit
1909, @GroupIdsTVP StringTableType readonly
1910
1911AS
1912
1913create table #GroupIdTable
1914(
1915 GroupId varchar(50) not null
1916)
1917
1918insert into #GroupIdTable (GroupId)
1919select StringValue from @GroupIdsTVP
1920
1921declare @SQLString nvarchar(max)
1922set @SQLString = ''
1923WITH CountsTable
1924AS
1925(
1926 select
1927 bd.batchId,
1928 DocumentCount = count(bd.BatchId),
1929 UnreviewedCount = Sum(case when bd.ReviewStatusId = 0 then 1 else 0 end),
1930 ReviewedCount = Sum(case when bd.ReviewStatusId = 1 then 1 else 0 end),
1931 OnHoldCount = Sum(case when bd.ReviewStatusId = 2 then 1 else 0 end)
1932 from review.BatchDocument bd
1933 group by bd.BatchId
1934)
1935''
1936IF @IsAdmin = 1
1937BEGIN
1938SET @SQLString = @SQLString + ''
1939
1940 SELECT
1941 r.[ReviewPassId]
1942 , r.[ReviewPassName]
1943 , b.BatchId
1944 , BatchNumber = CASE WHEN r.BatchPrefix <> '''''''' THEN r.BatchPrefix + REPLICATE( ''''0'''', 6 - LEN(b.BatchNumber)) + CONVERT(varchar(20), b.BatchNumber)
1945 ELSE CONVERT(varchar(10), b.BatchNumber) END
1946 , DocumentCount = ISNULL(DocumentCount,0)
1947 , UnreviewedCount = ISNULL(UnreviewedCount,0)
1948 , ReviewedCount = ISNULL(ReviewedCount,0)
1949 , OnHoldCount = ISNULL(OnHoldCount,0)
1950 , b.[BatchStatusId]
1951 , DateOpened = ISNULL(DateOpened, '''''''')
1952 , DateAssigned = ISNULL(DateAssigned, '''''''')
1953 , LastCheckIn = ISNULL(LastCheckIn, '''''''')
1954 , DateClosed = ISNULL(DateClosed, '''''''')
1955 , LastActivity = ISNULL(LastActivity, '''''''')
1956 , SpecialInstructions = ISNULL(b.SpecialInstructions, '''''''')
1957 , b.Priority
1958 , GroupByValue = ISNULL(b.GroupByValue, '''''''')
1959 , AssignedUserName = ISNULL(u.UserName,'''''''')
1960 , b.DateCreated
1961 FROM [Review].Batch b
1962 LEFT JOIN ActivityTracking.DIM_User u on u.UserKey =b.AssignedUserKey
1963 INNER JOIN [Review].[ReviewPass] r ON r.[ReviewPassId] = b.[ReviewPassId]
1964 LEFT JOIN CountsTable ON CountsTable.BatchId = b.BatchId
1965 ORDER BY b.[ReviewPassId], b.BatchId''
1966END
1967ELSE IF @HasExtendedPrivilege = 1
1968BEGIN
1969SET @SQLString = @SQLString + ''
1970 SELECT
1971 r.[ReviewPassId]
1972 , r.[ReviewPassName]
1973 , b.BatchId
1974 , BatchNumber = CASE WHEN r.BatchPrefix <> '''''''' THEN r.BatchPrefix + REPLICATE( ''''0'''', 6 - LEN(b.BatchNumber)) + CONVERT(varchar(20), b.BatchNumber)
1975 ELSE CONVERT(varchar(10), b.BatchNumber) END
1976 , DocumentCount = ISNULL(DocumentCount,0)
1977 , UnreviewedCount = ISNULL(UnreviewedCount,0)
1978 , ReviewedCount = ISNULL(ReviewedCount,0)
1979 , OnHoldCount = ISNULL(OnHoldCount,0)
1980 , b.[BatchStatusId]
1981 , DateOpened = ISNULL(DateOpened, '''''''')
1982 , DateAssigned = ISNULL(DateAssigned, '''''''')
1983 , LastCheckIn = ISNULL(LastCheckIn, '''''''')
1984 , DateClosed = ISNULL(DateClosed, '''''''')
1985 , LastActivity = ISNULL(LastActivity, '''''''')
1986 , SpecialInstructions = ISNULL(b.SpecialInstructions, '''''''')
1987 , b.Priority
1988 , GroupByValue = ISNULL(b.GroupByValue, '''''''')
1989 , AssignedUserName = ISNULL(u.UserName,'''''''')
1990 , b.DateCreated
1991 FROM [Review].Batch b
1992 LEFT JOIN ActivityTracking.DIM_User u on u.UserKey =b.AssignedUserKey
1993 INNER JOIN [Review].[ReviewPass] r ON r.[ReviewPassId] = b.[ReviewPassId]
1994 LEFT JOIN CountsTable ON CountsTable.BatchId = b.BatchId
1995 LEFT JOIN
1996 (
1997 select distinct ReviewPassId
1998 from Review.ReviewPassGroup rpg
1999 inner join [Ipro].[Group] g on rpg.GroupId = g.Id
2000 INNER JOIN #GroupIdTable gId on gId.GroupId = g.GroupId
2001 )rpg on rpg.ReviewPassId = r.ReviewPassId
2002 ORDER BY b.[ReviewPassId], b.BatchId''
2003
2004END
2005ELSE
2006BEGIN
2007
2008
2009declare @UserKey int
2010select @UserKey = ActivityTracking.GetUserKeyForUsername(@UserName)
2011
2012 create table #StatusChoices ([BatchStatusId] int not null)
2013 insert into #StatusChoices
2014 select 1
2015 union select 2
2016 union select 4
2017
2018 set @SQLString = ''
2019 WITH CountsTable
2020 AS
2021 (
2022 select
2023 bd.batchId,
2024 DocumentCount = count(bd.BatchId),
2025 UnreviewedCount = Sum(case when bd.ReviewStatusId = 0 then 1 else 0 end),
2026 ReviewedCount = Sum(case when bd.ReviewStatusId = 1 then 1 else 0 end),
2027 OnHoldCount = Sum(case when bd.ReviewStatusId = 2 then 1 else 0 end)
2028 from review.BatchDocument bd
2029 left join review.Batch b on bd.batchId = b.batchId
2030 WHERE b.AssignedUserKey = ''''''+CAST(@UserKey AS nvarchar)+''''''
2031 group by bd.BatchId
2032 )
2033
2034 SELECT
2035 r.[ReviewPassId]
2036 , r.[ReviewPassName]
2037 , b.BatchId
2038 , BatchNumber = CASE WHEN r.BatchPrefix <> '''''''' THEN r.BatchPrefix + REPLICATE( ''''0'''', 6 - LEN(b.BatchNumber)) + CONVERT(varchar(20), b.BatchNumber)
2039 ELSE CONVERT(varchar(10), b.BatchNumber) END
2040 , DocumentCount = ISNULL(DocumentCount,0)
2041 , UnreviewedCount = ISNULL(UnreviewedCount,0)
2042 , ReviewedCount = ISNULL(ReviewedCount,0)
2043 , OnHoldCount = ISNULL(OnHoldCount,0)
2044 , b.[BatchStatusId]
2045 , DateOpened = ISNULL(DateOpened, '''''''')
2046 , DateAssigned = ISNULL(DateAssigned, '''''''')
2047 , LastCheckIn = ISNULL(LastCheckIn, '''''''')
2048 , DateClosed = ISNULL(DateClosed, '''''''')
2049 , LastActivity = ISNULL(LastActivity, '''''''')
2050 , SpecialInstructions = ISNULL(b.SpecialInstructions, '''''''')
2051 , b.Priority
2052 , GroupByValue = ISNULL(b.GroupByValue, '''''''')
2053 , AssignedUserName = ISNULL(u.UserName,'''''''')
2054 , b.DateCreated
2055 FROM [Review].Batch b
2056 LEFT JOIN ActivityTracking.DIM_User u on u.UserKey = b.AssignedUserKey
2057 INNER JOIN #StatusChoices sc on sc.BatchStatusId = b.BatchStatusId
2058 INNER JOIN [Review].[ReviewPass] r ON r.[ReviewPassId] = b.[ReviewPassId]
2059 LEFT JOIN CountsTable ON CountsTable.BatchId = b.BatchId
2060
2061 WHERE b.AssignedUserKey = ''''''+CAST(@UserKey AS nvarchar)+''''''
2062 ORDER BY b.[ReviewPassId], b.BatchId''
2063
2064END
2065EXECUTE (@SQLString)'
2066
2067 EXEC sp_executesql @contextstring, N'@SQL nvarchar(max)', @SQL=@SQL
2068
2069 Set @SQL = '
2070 ALTER PROCEDURE [Review].[GetBatchesByIds]
2071 @BatchIds dbo.IdTableType readonly
2072AS
2073select id
2074into #batchids
2075from @BatchIds;
2076WITH CountsTable
2077AS
2078(
2079 select
2080 bd.batchId,
2081 DocumentCount = count(bd.BatchId),
2082 UnreviewedCount = Sum(case when bd.ReviewStatusId = 0 then 1 else 0 end),
2083 ReviewedCount = Sum(case when bd.ReviewStatusId = 1 then 1 else 0 end),
2084 OnHoldCount = Sum(case when bd.ReviewStatusId = 2 then 1 else 0 end)
2085 from review.BatchDocument bd
2086 inner join #batchids bid on bd.batchId = bid.id
2087 group by bd.BatchId
2088)
2089
2090
2091 SELECT
2092 r.[ReviewPassId]
2093 , r.[ReviewPassName]
2094 , b.BatchId
2095 , BatchNumber = CASE WHEN r.BatchPrefix <> '''' THEN r.BatchPrefix + REPLICATE( ''0'', 6 - LEN(b.BatchNumber)) + CONVERT(varchar(20), b.BatchNumber)
2096 ELSE CONVERT(varchar(10), b.BatchNumber) END
2097 , DocumentCount = ISNULL(DocumentCount,0)
2098 , UnreviewedCount = ISNULL(UnreviewedCount,0)
2099 , ReviewedCount = ISNULL(ReviewedCount,0)
2100 , OnHoldCount = ISNULL(OnHoldCount,0)
2101 , b.[BatchStatusId]
2102 , DateOpened = ISNULL(DateOpened, '''')
2103 , DateAssigned = ISNULL(DateAssigned, '''')
2104 , LastCheckIn = ISNULL(LastCheckIn, '''')
2105 , DateClosed = ISNULL(DateClosed, '''')
2106 , LastActivity = ISNULL(LastActivity, '''')
2107 , SpecialInstructions = ISNULL(b.SpecialInstructions, '''')
2108 , b.Priority
2109 , GroupByValue = ISNULL(b.GroupByValue, '''')
2110 , AssignedUserName = ISNULL(u.UserName,'''')
2111 , b.DateCreated
2112 FROM [Review].Batch b
2113 LEFT JOIN ActivityTracking.DIM_User u on u.UserKey =b.AssignedUserKey
2114 INNER JOIN [Review].[ReviewPass] r ON r.[ReviewPassId] = b.[ReviewPassId]
2115 LEFT JOIN CountsTable ON CountsTable.BatchId = b.BatchId
2116 INNER JOIN #batchids bId on bId.Id = b.BatchId'
2117
2118 EXEC sp_executesql @contextstring, N'@SQL nvarchar(max)', @SQL=@SQL
2119
2120
2121Set @SQL = '
2122ALTER PROCEDURE [Review].[GetBatchesByReviewPass]
2123 @UserName nvarchar(256)
2124, @IsAdmin bit
2125, @HasExtendedPrivilege bit
2126, @GroupIdsTVP StringTableType readonly
2127, @ReviewPassId int
2128
2129AS
2130
2131create table #GroupIdTable
2132(
2133 GroupId varchar(50) not null
2134)
2135
2136insert into #GroupIdTable (GroupId)
2137select StringValue from @GroupIdsTVP
2138DECLARE @SQLStatement NVARCHAR(MAX)
2139
2140SET @SQLStatement = ''
2141;WITH CountsTable
2142AS
2143(
2144 select
2145 bd.batchId,
2146 DocumentCount = count(bd.BatchId),
2147 UnreviewedCount = Sum(case when bd.ReviewStatusId = 0 then 1 else 0 end),
2148 ReviewedCount = Sum(case when bd.ReviewStatusId = 1 then 1 else 0 end),
2149 OnHoldCount = Sum(case when bd.ReviewStatusId = 2 then 1 else 0 end)
2150 from review.BatchDocument bd
2151 inner join review.Batch b
2152 on bd.batchid = b.batchid
2153 and b.reviewpassid = ''''''+CAST(@ReviewPassId AS NVARCHAR)+''''''
2154 group by bd.BatchId
2155)
2156''
2157
2158IF @IsAdmin = 1
2159BEGIN
2160 SET @SQLStatement = @SQLStatement + ''
2161 SELECT
2162 r.[ReviewPassId]
2163 , r.[ReviewPassName]
2164 , b.BatchId
2165 , BatchNumber = CASE WHEN r.BatchPrefix <> '''''''' THEN r.BatchPrefix + REPLICATE( ''''0'''', 6 - LEN(b.BatchNumber)) + CONVERT(varchar(20), b.BatchNumber)
2166 ELSE CONVERT(varchar(10), b.BatchNumber) END
2167 , DocumentCount = ISNULL(DocumentCount,0)
2168 , UnreviewedCount = ISNULL(UnreviewedCount,0)
2169 , ReviewedCount = ISNULL(ReviewedCount,0)
2170 , OnHoldCount = ISNULL(OnHoldCount,0)
2171 , b.[BatchStatusId]
2172 , DateOpened = ISNULL(DateOpened, '''''''')
2173 , DateAssigned = ISNULL(DateAssigned, '''''''')
2174 , LastCheckIn = ISNULL(LastCheckIn, '''''''')
2175 , DateClosed = ISNULL(DateClosed, '''''''')
2176 , LastActivity = ISNULL(LastActivity, '''''''')
2177 , SpecialInstructions = ISNULL(b.SpecialInstructions, '''''''')
2178 , b.Priority
2179 , GroupByValue = ISNULL(b.GroupByValue, '''''''')
2180 , AssignedUserName = ISNULL(u.UserName,'''''''')
2181 , b.DateCreated
2182 FROM [Review].Batch b
2183 LEFT JOIN ActivityTracking.DIM_User u on u.UserKey =b.AssignedUserKey
2184 INNER JOIN [Review].[ReviewPass] r ON r.[ReviewPassId] = b.[ReviewPassId]
2185 LEFT JOIN CountsTable ON CountsTable.BatchId = b.BatchId
2186 WHERE r.ReviewPassId = ''''''+CAST(@ReviewPassId AS NVARCHAR)+''''''
2187 ORDER BY b.[ReviewPassId], b.BatchId
2188''
2189END
2190ELSE IF @HasExtendedPrivilege = 1
2191BEGIN
2192SET @SQLStatement = @SQLStatement + ''
2193 SELECT
2194 r.[ReviewPassId]
2195 , r.[ReviewPassName]
2196 , b.BatchId
2197 , BatchNumber = CASE WHEN r.BatchPrefix <> '''''''' THEN r.BatchPrefix + REPLICATE( ''''0'''', 6 - LEN(b.BatchNumber)) + CONVERT(varchar(20), b.BatchNumber)
2198 ELSE CONVERT(varchar(10), b.BatchNumber) END
2199 , DocumentCount = ISNULL(DocumentCount,0)
2200 , UnreviewedCount = ISNULL(UnreviewedCount,0)
2201 , ReviewedCount = ISNULL(ReviewedCount,0)
2202 , OnHoldCount = ISNULL(OnHoldCount,0)
2203 , b.[BatchStatusId]
2204 , DateOpened = ISNULL(DateOpened, '''''''')
2205 , DateAssigned = ISNULL(DateAssigned, '''''''')
2206 , LastCheckIn = ISNULL(LastCheckIn, '''''''')
2207 , DateClosed = ISNULL(DateClosed, '''''''')
2208 , LastActivity = ISNULL(LastActivity, '''''''')
2209 , SpecialInstructions = ISNULL(b.SpecialInstructions, '''''''')
2210 , b.Priority
2211 , GroupByValue = ISNULL(b.GroupByValue, '''''''')
2212 , AssignedUserName = ISNULL(u.UserName,'''''''')
2213 , b.DateCreated
2214 FROM [Review].Batch b
2215 LEFT JOIN ActivityTracking.DIM_User u on u.UserKey =b.AssignedUserKey
2216 INNER JOIN [Review].[ReviewPass] r ON r.[ReviewPassId] = b.[ReviewPassId]
2217 LEFT JOIN CountsTable ON CountsTable.BatchId = b.BatchId
2218 LEFT JOIN
2219 (
2220 select distinct ReviewPassId
2221 from Review.ReviewPassGroup rpg
2222 inner join [Ipro].[Group] g on rpg.GroupId = g.Id
2223 INNER JOIN #GroupIdTable gId on gId.GroupId = g.GroupId
2224 WHERE rpg.ReviewPassId = ''''''+CAST(@ReviewPassId AS NVARCHAR)+''''''
2225 )rpg on rpg.ReviewPassId = r.ReviewPassId
2226 WHERE r.ReviewPassId = ''''''+CAST(@ReviewPassId AS NVARCHAR)+''''''
2227 ORDER BY b.[ReviewPassId], b.BatchId
2228''
2229END
2230ELSE
2231BEGIN
2232
2233declare @UserKey int
2234select @UserKey = ActivityTracking.GetUserKeyForUsername(@UserName)
2235
2236 create table #StatusChoices ([BatchStatusId] int not null)
2237 insert into #StatusChoices
2238 select 1
2239 union select 2
2240 union select 4
2241 SET @SQLStatement = @SQLStatement + ''
2242 SELECT
2243 r.[ReviewPassId]
2244 , r.[ReviewPassName]
2245 , b.BatchId
2246 , BatchNumber = CASE WHEN r.BatchPrefix <> '''''''' THEN r.BatchPrefix + REPLICATE( ''''0'''', 6 - LEN(b.BatchNumber)) + CONVERT(varchar(20), b.BatchNumber)
2247 ELSE CONVERT(varchar(10), b.BatchNumber) END
2248 , DocumentCount = ISNULL(DocumentCount,0)
2249 , UnreviewedCount = ISNULL(UnreviewedCount,0)
2250 , ReviewedCount = ISNULL(ReviewedCount,0)
2251 , OnHoldCount = ISNULL(OnHoldCount,0)
2252 , b.[BatchStatusId]
2253 , DateOpened = ISNULL(DateOpened, '''''''')
2254 , DateAssigned = ISNULL(DateAssigned, '''''''')
2255 , LastCheckIn = ISNULL(LastCheckIn, '''''''')
2256 , DateClosed = ISNULL(DateClosed, '''''''')
2257 , LastActivity = ISNULL(LastActivity, '''''''')
2258 , SpecialInstructions = ISNULL(b.SpecialInstructions, '''''''')
2259 , b.Priority
2260 , GroupByValue = ISNULL(b.GroupByValue, '''''''')
2261 , AssignedUserName = ISNULL(u.UserName,'''''''')
2262 , b.DateCreated
2263 FROM [Review].Batch b
2264 LEFT JOIN ActivityTracking.DIM_User u on u.UserKey = b.AssignedUserKey
2265 INNER JOIN #StatusChoices sc on sc.BatchStatusId = b.BatchStatusId
2266 INNER JOIN [Review].[ReviewPass] r ON r.[ReviewPassId] = b.[ReviewPassId]
2267 LEFT JOIN CountsTable ON CountsTable.BatchId = b.BatchId
2268
2269 WHERE b.AssignedUserKey = ''''''+CAST(@UserKey AS NVARCHAR)+'''''' and r.ReviewPassId = ''''''+CAST(@ReviewPassId AS NVARCHAR)+''''''
2270 ORDER BY b.[ReviewPassId], b.BatchId
2271 ''
2272
2273END
2274EXECUTE (@SQLStatement)'
2275
2276 EXEC sp_executesql @contextstring, N'@SQL nvarchar(max)', @SQL=@SQL
2277
2278 Set @SQL = 'ALTER PROCEDURE [Review].[GetBatchesByUsername]
2279 @UserName nvarchar(256)
2280AS
2281
2282declare @UserKey int
2283select @UserKey = ActivityTracking.GetUserKeyForUsername(@UserName)
2284
2285;WITH CountsTable AS
2286( select
2287 bd.batchId,
2288 DocumentCount = count(bd.BatchId),
2289 UnreviewedCount = Sum(case when bd.ReviewStatusId = 0 then 1 else 0 end),
2290 ReviewedCount = Sum(case when bd.ReviewStatusId = 1 then 1 else 0 end),
2291 OnHoldCount = Sum(case when bd.ReviewStatusId = 2 then 1 else 0 end)
2292 from review.BatchDocument bd
2293 inner join review.Batch b
2294 on bd.batchid = b.batchid
2295 and b.AssignedUserKey = @UserKey
2296 group by bd.BatchId
2297)
2298 SELECT
2299 r.[ReviewPassId]
2300 , r.[ReviewPassName]
2301 , b.BatchId
2302 , BatchNumber = CASE WHEN r.BatchPrefix <> '''' THEN r.BatchPrefix + REPLICATE( ''0'', 6 - LEN(b.BatchNumber)) + CONVERT(varchar(20), b.BatchNumber)
2303 ELSE CONVERT(varchar(10), b.BatchNumber) END
2304 , DocumentCount = ISNULL(DocumentCount,0)
2305 , UnreviewedCount = ISNULL(UnreviewedCount,0)
2306 , ReviewedCount = ISNULL(ReviewedCount,0)
2307 , OnHoldCount = ISNULL(OnHoldCount,0)
2308 , b.[BatchStatusId]
2309 , DateOpened = ISNULL(DateOpened, '''')
2310 , DateAssigned = ISNULL(DateAssigned, '''')
2311 , LastCheckIn = ISNULL(LastCheckIn, '''')
2312 , DateClosed = ISNULL(DateClosed, '''')
2313 , LastActivity = ISNULL(LastActivity, '''')
2314 , SpecialInstructions = ISNULL(b.SpecialInstructions, '''')
2315 , b.Priority
2316 , GroupByValue = ISNULL(b.GroupByValue, '''')
2317 , AssignedUserName = ISNULL(u.UserName,'''')
2318 , b.DateCreated
2319 FROM [Review].Batch b
2320 LEFT JOIN ActivityTracking.DIM_User u on u.UserKey =b.AssignedUserKey
2321 INNER JOIN [Review].[ReviewPass] r ON r.[ReviewPassId] = b.[ReviewPassId]
2322 LEFT JOIN CountsTable ON CountsTable.BatchId = b.BatchId
2323 where b.AssignedUserKey = @UserKey'
2324
2325 EXEC sp_executesql @contextstring, N'@SQL nvarchar(max)', @SQL=@SQL
2326
2327 Set @SQL = '
2328 ALTER PROCEDURE [Review].[GetNativeSizeStatusCounts]
2329AS
2330
2331SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
2332
2333DECLARE @TableName VARCHAR(128), @ColumnName VARCHAR(128)
2334SELECT
2335 @TableName = TableName
2336 ,@ColumnName = ColumnName
2337FROM vFieldDefinition
2338WHERE FieldDataType = 8
2339AND SystemFieldType = 24
2340
2341EXECUTE
2342(N''
2343 SELECT
2344 LoadedSize
2345 ,LoadedDocCount
2346 ,InReviewSize
2347 ,InReviewDocCount
2348 ,ProducedSize
2349 ,ProducedDocCount
2350 FROM
2351 (
2352 SELECT
2353 1 as JoinKey
2354 ,ISNULL(SUM(['' + @ColumnName + '']), 0) AS LoadedSize
2355 ,COUNT(df.DocId) AS LoadedDocCount
2356 FROM ['' + @TableName + ''] df
2357 ) as l
2358 INNER JOIN
2359 (
2360 SELECT
2361 1 as JoinKey
2362 ,ISNULL(SUM(['' + @ColumnName + '']), 0) AS InReviewSize
2363 ,COUNT(df.DocId) AS InReviewDocCount
2364 FROM ['' + @TableName + ''] df
2365 INNER JOIN
2366 (
2367 SELECT DISTINCT DocId
2368 FROM [Review].[BatchDocument]
2369 ) AS bd ON df.DocId = bd.DocId
2370 ) AS r ON l.JoinKey = r.JoinKey
2371 INNER JOIN
2372 (
2373 SELECT
2374 1 as JoinKey
2375 ,ISNULL(SUM(['' + @ColumnName + '']), 0) AS ProducedSize
2376 ,COUNT(df.DocId) AS ProducedDocCount
2377 FROM ['' + @TableName + ''] df
2378 INNER JOIN
2379 (
2380 SELECT DISTINCT
2381 DocumentId
2382 FROM Production.Production prod
2383 inner join Production.ProductionHistory ph
2384 on prod.ProductionId = ph.ProductionId
2385 inner join Production.ProductionHistoryDocuments phd
2386 on ph.ProductionHistoryId = phd.ProductionHistoryId
2387 where prod.IsProduction = 1
2388 and ph.JobStatusId = 5
2389 ) AS pd ON df.DocId = pd.DocumentId
2390 ) AS p ON l.JoinKey = p.JoinKey
2391OPTION (MAXDOP 0)
2392'')'
2393
2394 EXEC sp_executesql @contextstring, N'@SQL nvarchar(max)', @SQL=@SQL
2395
2396 Set @SQL = '
2397 ALTER PROCEDURE [Review].[GetReviewableReviewPassDocumentReviewStatus]
2398 @ReviewPassId int = 0
2399AS
2400SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
2401SELECT
2402[ReviewPassId]
2403, [ReviewPassName]
2404, CodingFormId
2405, LayoutStyle
2406, DocumentStatus_NotReviewed = [0]
2407, DocumentStatus_Reviewed = [1]
2408, DocumentStatus_OnHold = [2]
2409FROM
2410(
2411 SELECT
2412 r.[ReviewPassId]
2413 , r.[ReviewPassName]
2414 , r.CodingFormId
2415 , r.LayoutStyle
2416 , bd.DocId
2417 , bd.ReviewStatusId
2418FROM [Review].[ReviewPass] r
2419INNER JOIN [Review].Batch b ON b.ReviewPassId = r.ReviewPassId
2420INNER JOIN [Review].[BatchDocument] bd ON bd.BatchId = b.BatchId
2421WHERE @ReviewPassId = 0 or @ReviewPassId = r.ReviewPassId
2422)AS SourceTable
2423PIVOT
2424(COUNT(DocId)
2425FOR ReviewStatusId IN ([0], [1], [2])
2426)AS PivotTable
2427OPTION (MAXDOP 0)'
2428
2429 EXEC sp_executesql @contextstring, N'@SQL nvarchar(max)', @SQL=@SQL
2430
2431 Set @SQL = '
2432 ALTER PROCEDURE [Search].[GetSearchItemResultsAggregateDataWithFilter]
2433(
2434 @ResultsId INT,
2435 @FieldId1 INT,
2436 @FieldId2 INT = NULL,
2437 @UserKey INT,
2438 @IncludeFieldId INT = NULL,
2439 @Filter dbo.ColumnFilterType readonly,
2440 @SortField dbo.OrderedGridItemType readonly,
2441 @WithSeconds bit,
2442 @WithMilliseconds bit,
2443 @StartDate datetime = null,
2444 @EndDate datetime = null,
2445 @IncludeNoDate bit,
2446 @DateFieldId int,
2447 @MinDate datetime,
2448 @MaxDate datetime,
2449 @QuickSearchResultsId int
2450)
2451AS
2452/*********************************************************************************************/
2453-- Author: Steve Larrison
2454-- Date Written: - September, 2016
2455-- Date Modified
2456-- Revision History:
2457-- September, 2016 - Initial version
2458/*********************************************************************************************/
2459SET XACT_ABORT, NOCOUNT ON
2460BEGIN TRY
2461BEGIN TRANSACTION
2462
2463DECLARE @SQLStatement NVARCHAR(MAX)
2464DECLARE @ErrorMessage NVARCHAR(MAX)
2465----
2466DECLARE @SearchTableName VARCHAR(128)
2467DECLARE @CheckTableName VARCHAR(128)
2468DECLARE @CheckUserKey INT
2469DECLARE @CheckResultsId INT
2470DECLARE @Table1 VARCHAR(128)
2471DECLARE @Column1 VARCHAR(128)
2472DECLARE @Table2 VARCHAR(128)
2473DECLARE @Column2 VARCHAR(128)
2474DECLARE @UserName NVARCHAR(100)
2475DECLARE @RowCount INT = 0
2476DECLARE @Parameters NVARCHAR(MAX)
2477
2478
2479
2480-- anything above the "line in the sand comment is copied from the Documents_GetDocumentDateCountsFromSearch proc and that bolted on to
2481-- the [Search].[GetSearchItemResultsAggregateData] proc. To make refactoring possible, we are clearly delineating things and going to use existing
2482-- logic in the [Search].[GetSearchItemResultsAggregateData] proc with one minor modification - we are going to point the query generated by that proc
2483-- to a temp table instead of to UserTables.SearchResults
2484
2485
2486/* Added Logic Here to determine if filtered grid or not. This will allow us to short circuit load times when filtering is not needed. */
2487DECLARE @IsFiltered BIT = 1
2488IF
2489(
2490 @StartDate IS NULL
2491 AND @EndDate IS NULL
2492 AND @IncludeNoDate = 1
2493) SET @IsFiltered = 0
2494
2495
2496 select @UserName = UserName from ActivityTracking.DIM_User where UserKey = @UserKey
2497
2498IF (@IsFiltered = 1)
2499BEGIN
2500
2501 SET @SQLStatement = ''select @RowCount = Count(*) from UserTables.SearchResults'' + Convert(varchar, @UserKey) + '' Where ResultsId = '' + Convert(varchar, @ResultsId)
2502 SET @Parameters = N''@RowCount INT OUTPUT''
2503 EXEC sp_executesql @SQLStatement, @Parameters,
2504 @RowCount = @RowCount OUTPUT
2505
2506
2507 create table #FilteredResults (ResultsId int default 999, DocId int)
2508
2509
2510 insert into #FilteredResults (DocId)
2511 exec dbo.Documents_GetPagedDocumentIdsFromSearch @ResultsId = @ResultsId,
2512 @UserName = @UserName,
2513 @PageSize = @RowCount,
2514 @PageNumber = 1,
2515 @Filter = @Filter,
2516 @SortField = @SortField,
2517 @WithSeconds = @WithSeconds,
2518 @WithMilliseconds = @WithMilliseconds,
2519 @StartDate = @StartDate,
2520 @EndDate = @EndDate,
2521 @IncludeNoDate = @IncludeNoDate,
2522 @DateFieldId = @DateFieldId,
2523 @MinDate = @MinDate,
2524 @MaxDate = @MaxDate,
2525 @QuickSearchResultsId = @QuickSearchResultsId
2526
2527
2528
2529END
2530
2531 SET @SQLStatement = '' ''
2532
2533
2534----------------------------- Line in the sand ------------------------------------------
2535
2536
2537
2538 SELECT @CheckUserKey = UserKey,
2539 @UserName = UserName
2540 FROM ActivityTracking.Dim_User
2541 WHERE UserKey = @UserKey
2542
2543 IF @CheckUserKey IS NULL
2544 BEGIN
2545 SET @ErrorMessage = ''Error 91001: GetSearchItemResultsAggregateData procedure. There is no matching record in ActivityTracking.Dim_User for user '' + CONVERT(VARCHAR,@UserKey)
2546 RAISERROR(@ErrorMessage,16,1)
2547 END
2548
2549--- Find the table and column name(s) that we are interested in
2550
2551--- Find table and column for FieldId1
2552 BEGIN
2553 SELECT @Table1 = TableName,
2554 @Column1 = ColumnName
2555 FROM vFieldDefinition
2556 WHERE FieldId = @FieldId1
2557
2558--- See if the field of interest has actually been defined
2559
2560 IF @Table1 IS NULL
2561 BEGIN
2562 SET @ErrorMessage = ''Error 91002: GetSearchItemResultsAggregateData procedure. The FieldId you specified - '' + CONVERT(VARCHAR,@FieldId1) + '' can not be found in the FieldDefinition table.''
2563 RAISERROR(@ErrorMessage,16,1)
2564 END
2565
2566--- See if we have an Aggregate table for our field of interest
2567
2568 SET @SearchTableName = @Table1 + ''_'' + @Column1 + ''_Aggregate''
2569 SET @CheckTableName = NULL
2570
2571 SELECT @CheckTableName = table_name
2572 FROM information_schema.columns
2573 WHERE table_name = @SearchTableName
2574
2575 IF @CheckTableName IS NULL
2576 BEGIN
2577 SET @ErrorMessage = ''Error 91003: GetSearchItemResultsAggregateData procedure. There is no Aggregate table found for '' + CONVERT(VARCHAR,@FieldId1) + '' : '' + @Table1 + ''.'' + @Column1
2578 RAISERROR(@ErrorMessage,16,1)
2579 END
2580
2581 END --- End of our logic block for FieldId1
2582
2583--- Find table and column for FieldId2
2584 IF @FieldId2 IS NOT NULL
2585 BEGIN
2586 SELECT @Table2 = TableName,
2587 @Column2 = ColumnName
2588 FROM vFieldDefinition
2589 WHERE FieldId = @FieldId2
2590
2591--- See if the field of interest has actually been defined
2592
2593 IF @Table2 IS NULL
2594 BEGIN
2595 SET @ErrorMessage = ''Error 91004: GetSearchItemResultsAggregateData procedure. The FieldId you specified - '' + CONVERT(VARCHAR,@FieldId2) + '' can not be found in the FieldDefinition table.''
2596 RAISERROR(@ErrorMessage,16,1)
2597 END
2598
2599--- See if we have an Aggregate table for our field of interest
2600
2601 SET @SearchTableName = @Table2 + ''_'' + @Column2 + ''_Aggregate''
2602 SET @CheckTableName = NULL
2603
2604 SELECT @CheckTableName = table_name
2605 FROM information_schema.columns
2606 WHERE table_name = @SearchTableName
2607
2608 IF @CheckTableName IS NULL
2609 BEGIN
2610 SET @ErrorMessage = ''Error 91005: GetSearchItemResultsAggregateData procedure. There is no Aggregate table found for '' + CONVERT(VARCHAR,@FieldId2) + '' : '' + @Table2 + ''.'' + @Column2
2611 RAISERROR(@ErrorMessage,16,1)
2612 END
2613
2614--- See if we have an Aggregate table for our field of interest
2615
2616 SET @SearchTableName = @Table2 + ''_'' + @Column2 + ''_Association''
2617 SET @CheckTableName = NULL
2618
2619 SELECT @CheckTableName = table_name
2620 FROM information_schema.columns
2621 WHERE table_name = @SearchTableName
2622
2623 IF @CheckTableName IS NULL
2624 BEGIN
2625 SET @ErrorMessage = ''Error 91006: GetSearchItemResultsAggregateData procedure. There is no Association table found for '' + CONVERT(VARCHAR,@FieldId2) + '' : '' + @Table2 + ''.'' + @Column2
2626 RAISERROR(@ErrorMessage,16,1)
2627 END
2628
2629
2630 END --- End of our logic block for FieldId2
2631
2632--- Check to see if the same FieldId was used for both inputs
2633
2634 IF @FieldId1 = @FieldId2
2635 BEGIN
2636 SET @ErrorMessage = ''Error 91006: GetSearchItemResultsAggregateData procedure. The first and second FieldId parameters have the same value. This is illogical''
2637 RAISERROR(@ErrorMessage,16,1)
2638 END
2639
2640
2641--- Build our Search Item Results table name. By convention, the searh results are always placed in a table called
2642--- SearchResults in the UserTables schema. The SearchResults table has a UserKey (from ActivityTracking.Dim_User
2643--- that is used as a suffix
2644
2645 SET @SearchTableName = ''SearchResults'' + CONVERT(VARCHAR,@UserKey)
2646
2647--- Now that we have a table name to use for our input, make sure that it really exists
2648
2649 SET @CheckTableName = NULL
2650
2651 SELECT @CheckTableName = table_name
2652 FROM information_schema.columns
2653 WHERE table_schema = ''UserTables''
2654 AND table_name = @SearchTableName
2655
2656 IF @CheckTableName IS NULL
2657 BEGIN
2658 SET @ErrorMessage = ''Error 91002: GetSearchItemResultsAggregateData procedure. There is no SearchResults table for user - '' + CONVERT(VARCHAR,@UserKey) + '' '' + @UserName
2659 RAISERROR(@ErrorMessage,16,1)
2660 END
2661
2662--- Build our query
2663 IF (@IsFiltered = 0)
2664 BEGIN
2665 SET @SQLStatement ='';WITH SearchResults AS ( SELECT DocID FROM UserTables.''+@SearchTableName+'' WHERE ResultsID =''+ CONVERT(VARCHAR,@ResultsId)+'' )
2666 ''
2667 END
2668
2669 SET @SQLStatement = @SQLStatement + '' SELECT COUNT(*) Documents, '' + @Column1
2670 IF @Column2 IS NOT NULL
2671 BEGIN
2672 SET @SQLStatement = @SQLStatement + '','' + @Column2 + ''
2673''
2674 END
2675 SET @SQLStatement = @SQLStatement + '' FROM '' + @Table1 + ''_'' + @Column1 + ''_Aggregate dfa1,
2676 '' + @Table1 + ''_'' + @Column1 + ''_Association assoc1 ''
2677 IF @Column2 IS NOT NULL
2678 BEGIN
2679 SET @SQLStatement = @SQLStatement + '','' +
2680 @Table2 + ''_'' + @Column2 + ''_Aggregate dfa2,
2681 '' + @Table2 + ''_'' + @Column2 + ''_Association assoc2 ''
2682
2683 END
2684 IF(@IsFiltered = 1)
2685 BEGIN
2686 SET @SQLStatement = @SQLStatement + ''WHERE dfa1.'' + @Table1 + ''_'' + @Column1 + ''Id = assoc1.'' + @Table1 + ''_'' + @Column1 + ''Id
2687 AND assoc1.DocId IN ( SELECT DocId
2688 FROM #FilteredResults '' + ''
2689 WHERE ResultsId = 999 ) ''
2690 END
2691 IF(@IsFiltered = 0)
2692 BEGIN
2693 SET @SQLStatement = @SQLStatement + ''WHERE dfa1.'' + @Table1 + ''_'' + @Column1 + ''Id = assoc1.'' + @Table1 + ''_'' + @Column1 + ''Id
2694 AND assoc1.DocId IN ( SELECT DocId
2695 FROM SearchResults ) ''
2696 END
2697 IF @IncludeFieldId IS NOT NULL
2698 BEGIN
2699 SET @SQLStatement = @SQLStatement + ''
2700 AND assoc1.DocId NOT IN ( SELECT DocId
2701 FROM UserTables.SearchResultsInclude'' + CONVERT(VARCHAR,@UserKey) + ''
2702 WHERE ResultsId = '' + CONVERT(VARCHAR,@ResultsId) + '' ) ''
2703 END
2704 IF @Column2 IS NOT NULL
2705 BEGIN
2706 IF(@IsFiltered = 1)
2707 BEGIN
2708 SET @SQLStatement = @SQLStatement + ''AND dfa2.'' + @Table2 + ''_'' + @Column2 + ''Id = assoc2.'' + @Table2 + ''_'' + @Column2 + ''Id
2709 AND assoc2.DocId IN ( SELECT DocId
2710 FROM #FilteredResults '' + ''
2711 WHERE ResultsId = 999 '' + '')
2712 AND assoc1.DocId = assoc2.DocId ''
2713 END
2714 IF(@IsFiltered = 0)
2715 BEGIN
2716 SET @SQLStatement = @SQLStatement + ''AND dfa2.'' + @Table2 + ''_'' + @Column2 + ''Id = assoc2.'' + @Table2 + ''_'' + @Column2 + ''Id
2717 AND assoc2.DocId IN ( SELECT DocId
2718 FROM SearchResults )
2719 AND assoc1.DocId = assoc2.DocId ''
2720 END
2721 END
2722 SET @SQLStatement = @SQLStatement + ''GROUP BY '' + @Column1
2723
2724 IF @Column2 IS NOT NULL
2725 BEGIN
2726 SET @SQLStatement = @SQLStatement + '','' + @Column2 + '' ORDER BY '' + @Column1
2727 END
2728 EXEC sp_executesql @SQLStatement
2729
2730COMMIT
2731END TRY
2732BEGIN CATCH
2733
2734 IF @@trancount > 0
2735 BEGIN
2736 ROLLBACK TRANSACTION
2737 END
2738 IF @ErrorMessage IS NULL
2739 BEGIN
2740 SET @ErrorMessage = Error_Message() + '': Error 91999: GetSearchItemResultsAggregateData procedure. Unknown error - contact technical support''
2741 END
2742 RAISERROR(@ErrorMessage,16,1)
2743 RETURN 52002
2744END CATCH;'
2745
2746 EXEC sp_executesql @contextstring, N'@SQL nvarchar(max)', @SQL=@SQL
2747
2748
2749 set @SQL = '
2750--IF OBJECT_ID(''dbo.Documents_GetPagedDocumentIdsFromBatch'') IS NULL
2751-- EXEC (''CREATE PROCEDURE dbo.Documents_GetPagedDocumentIdsFromBatch AS RETURN 0;'');
2752
2753ALTER PROCEDURE [dbo].[Documents_GetPagedDocumentIdsFromBatch]
2754 @BatchId int,
2755 @PageSize int,
2756 @PageNumber int,
2757 @FilterReviewed int,
2758 @Filter dbo.ColumnFilterType readonly,
2759 @SortField dbo.OrderedGridItemType readonly,
2760 @WithSeconds bit,
2761 @WithMilliseconds bit,
2762 @StartDate datetime = null,
2763 @EndDate datetime = null,
2764 @IncludeNoDate bit = null,
2765 @DateFieldId int = null,
2766 @MinDate datetime,
2767 @MaxDate datetime,
2768 @QuickSearchResultsId INT,
2769 @UserName NVARCHAR(256)
2770AS
2771set nocount on
2772
2773DECLARE @sql as nvarchar(max) = ''''
2774DECLARE @PagingQueryWhereClause as nvarchar(max) = ''''
2775DECLARE @SqlSortRowNumber nvarchar(max) = ''''
2776DECLARE @SqlDateRangeWhere nvarchar(max) = ''''
2777DECLARE @SqlFilterWhere nvarchar(max) = ''''
2778DECLARE @SqlBatchJoin nvarchar(max) = ''''
2779DECLARE @TagPivotTempTable nvarchar(max) = ''''
2780DECLARE @QuickSearchJoin NVARCHAR(MAX) = ''''
2781DECLARE @SqlWhere nvarchar(max) = ''''
2782DECLARE @JoinOnAlias nvarchar(5) = ''df''
2783
2784select @PagingQueryWhereClause = dbo.PagingQueryWhereClause(@PageNumber, @PageSize)
2785
2786exec Documents_GetDocumentIds_GenerateDocumentDateClause @StartDate, @EndDate, @IncludeNoDate, @DateFieldId, @MinDate, @MaxDate, @SqlDateRangeWhere output
2787exec Documents_GetDocumentIds_GenerateFilterClause @Filter, @WithSeconds, @WithMilliseconds, @SqlFilterWhere output
2788
2789select @SqlSortRowNumber = dbo.Documents_GenerateSortRowNumberStatementForBatches(@SortField, @BatchId, @FilterReviewed)
2790
2791if (@BatchId > 0)
2792begin
2793 set @SqlBatchJoin = ''INNER JOIN Review.BatchDocument bd ON '' + @JoinOnAlias + ''.DocId = bd.DocId AND bd.BatchId = '' + Convert(varchar(10), @BatchId)
2794 if (@FilterReviewed = 1)
2795 set @SqlBatchJoin = @SqlBatchJoin + '' AND bd.ReviewStatusId = 1 ''
2796 else if (@FilterReviewed = 2)
2797 set @SqlBatchJoin = @SqlBatchJoin + '' AND bd.ReviewStatusId <> 1 ''
2798END
2799
2800IF (@QuickSearchResultsId > 0)
2801BEGIN
2802 declare @UserKey INT, @ResultsTableName as nvarchar(256)
2803 SELECT @UserKey = ActivityTracking.GetUserKeyForUsername(@UserName)
2804 SET @ResultsTableName = UserTables.SearchResultsTableName(@UserKey)
2805
2806 SET @QuickSearchJoin = ''INNER JOIN '' + @ResultsTableName + '' qsr ON '' + @JoinOnAlias + ''.DocId = qsr.DocId AND qsr.ResultsId = '' + Convert(varchar(10), @QuickSearchResultsId)
2807END
2808
2809--Build where clause
2810if(len(@SqlFilterWhere) > 0)
2811begin
2812 set @SqlWhere += ''WHERE '' + @SqlFilterWhere
2813 if(len(@SqlDateRangeWhere) > 0)
2814 set @SqlWhere += '' AND '' + @SqlDateRangeWhere
2815end
2816else if(len(@SqlDateRangeWhere) > 0)
2817 set @SqlWhere += ''WHERE '' + @SqlDateRangeWhere
2818
2819IF(@BatchId > 0)
2820 SET @TagPivotTempTable = dbo.Documents_GenerateTagPivotTempTable(@Filter, @SortField, ''Review.BatchDocument'', DEFAULT, @BatchId)
2821ELSE
2822 SET @TagPivotTempTable = dbo.Documents_GenerateTagPivotTempTable(@Filter, @SortField, ''dbo.Documents'', DEFAULT, DEFAULT)
2823
2824DECLARE @FieldsToInclude dbo.IdTableType
2825INSERT INTO @FieldsToInclude (Id)
2826SELECT ItemId
2827FROM @Filter
2828WHERE ItemType = 0 --GridItemType.Field
2829UNION
2830SELECT ItemId
2831FROM @SortField
2832WHERE ItemType = 0 --GridItemType.Field
2833UNION
2834SELECT @DateFieldId
2835WHERE @DateFieldId is not NULL
2836
2837IF(@BatchId > 0)
2838BEGIN
2839 INSERT INTO @FieldsToInclude (Id)
2840 SELECT sbf.FieldId
2841 FROM Review.Batch b
2842 INNER JOIN Review.SortByField sbf on sbf.ReviewPassId = b.ReviewPassId
2843 WHERE b.BatchId = @BatchId
2844END
2845SELECT
2846 TagGroupID
2847 ,TagGroupName
2848 ,TagId
2849 ,TagName
2850 ,IsLeafTag
2851 ,TagPath
2852INTO #vTagHierarchy
2853FROM dbo.vTagHierarchy
2854set @Sql = ''
2855<TagTempTable>
2856
2857Select
2858 DocId
2859FROM
2860(
2861 SELECT
2862 RowNum = <SortRowNumber>
2863 , df.DocId
2864 FROM '' + [dbo].[Documents_GenerateAliasedDocumentFields](@FieldsToInclude, @JoinOnAlias) + ''
2865 <TagPivotJoin>
2866 <QuickSearchJoin>
2867 <BatchJoin>
2868 <SqlWhere>
2869)x
2870WHERE RowNum <PagingQueryWhereClause>
2871ORDER BY RowNum
2872'';
2873
2874set @sql = REPLACE(@sql,''<SortRowNumber>'',@SqlSortRowNumber);
2875set @sql = REPLACE(@sql,''<TagTempTable>'',@TagPivotTempTable);
2876if LEN(@TagPivotTempTable) > 1
2877BEGIN
2878 set @sql = REPLACE(@sql,''<TagPivotJoin>'',''INNER JOIN #TagDocs dt on dt.docid = df.docid'');
2879END
2880ELSE
2881BEGIN
2882 set @sql = REPLACE(@sql,''<TagPivotJoin>'','''');
2883END
2884SET @sql = REPLACE(@sql,''<QuickSearchJoin>'', @QuickSearchJoin);
2885set @sql = REPLACE(@sql,''<BatchJoin>'', @SqlBatchJoin);
2886set @sql = REPLACE(@sql,''<SqlWhere>'',@SqlWhere);
2887set @sql = REPLACE(@sql,''<PagingQueryWhereClause>'',@PagingQueryWhereClause);
2888
2889--print (@sql)
2890execute sp_executesql @sql, N''@SortField dbo.OrderedGridItemType readonly, @Filter dbo.ColumnFilterType readonly'', @SortField = @SortField, @Filter = @Filter'
2891
2892EXEC sp_executesql @contextstring, N'@SQL nvarchar(max)', @SQL=@SQL
2893
2894set @SQL = '
2895--IF OBJECT_ID(''dbo.Documents_GetPagedDocumentIdsFromDocIds'') IS NULL
2896-- EXEC (''CREATE PROCEDURE dbo.Documents_GetPagedDocumentIdsFromDocIds AS RETURN 0;'');
2897--GO
2898ALTER PROCEDURE [dbo].[Documents_GetPagedDocumentIdsFromDocIds]
2899 @DocIds IdTableType readonly,
2900 @PageSize int,
2901 @PageNumber int,
2902 @Filter dbo.ColumnFilterType readonly,
2903 @SortField dbo.OrderedGridItemType readonly,
2904 @WithSeconds bit,
2905 @WithMilliseconds bit,
2906 @StartDate datetime = null,
2907 @EndDate datetime = null,
2908 @IncludeNoDate bit = null,
2909 @DateFieldId int = null,
2910 @MinDate datetime,
2911 @MaxDate datetime,
2912 @QuickSearchResultsId INT,
2913 @UserName NVARCHAR(256)
2914AS
2915 set nocount on
2916
2917DECLARE @sql as nvarchar(max) = ''''
2918DECLARE @PagingQueryWhereClause as nvarchar(max) = ''''
2919DECLARE @SqlSortRowNumber nvarchar(max) = ''''
2920DECLARE @SqlDateRangeWhere nvarchar(max) = ''''
2921DECLARE @SqlFilterWhere nvarchar(max) = ''''
2922DECLARE @TagPivotTempTable nvarchar(max) = ''''
2923DECLARE @SqlWhere nvarchar(max) = ''''
2924DECLARE @QuickSearchJoin NVARCHAR(MAX) = ''''
2925DECLARE @JoinOnAlias nvarchar(5) = ''df''
2926
2927IF (@QuickSearchResultsId > 0)
2928BEGIN
2929 declare @UserKey INT, @ResultsTableName as nvarchar(256)
2930 SELECT @UserKey = ActivityTracking.GetUserKeyForUsername(@UserName)
2931 SET @ResultsTableName = UserTables.SearchResultsTableName(@UserKey)
2932
2933 SET @QuickSearchJoin = '' INNER JOIN '' + @ResultsTableName + '' qsr ON '' + @JoinOnAlias + ''.DocId = qsr.DocId AND qsr.ResultsId = '' + Convert(varchar(10), @QuickSearchResultsId)
2934END
2935
2936select @PagingQueryWhereClause = dbo.PagingQueryWhereClause(@PageNumber, @PageSize)
2937
2938exec Documents_GetDocumentIds_GenerateDocumentDateClause @StartDate, @EndDate, @IncludeNoDate, @DateFieldId, @MinDate, @MaxDate, @SqlDateRangeWhere output
2939exec Documents_GetDocumentIds_GenerateFilterClause @Filter, @WithSeconds, @WithMilliseconds, @SqlFilterWhere output
2940
2941if not exists(select * from @SortField)
2942begin
2943 set @SqlSortRowNumber = ''ROW_NUMBER() OVER(ORDER BY '' + @JoinOnAlias + ''.DocId ASC)''-- +case when @SortAscending = 0 then ''DESC'' else ''ASC'' end +'')''
2944end
2945else
2946begin
2947 select @SqlSortRowNumber = dbo.Documents_GenerateSortRowNumberStatement(@SortField)
2948end
2949
2950select
2951 d.Id as DocId
2952into #DocIds
2953from @DocIds d
2954
2955--Build where clause
2956if(len(@SqlFilterWhere) > 0)
2957begin
2958 set @SqlWhere += ''WHERE '' + @SqlFilterWhere
2959 if(len(@SqlDateRangeWhere) > 0)
2960 set @SqlWhere += '' AND '' + @SqlDateRangeWhere
2961end
2962else if(len(@SqlDateRangeWhere) > 0)
2963 set @SqlWhere += ''WHERE '' + @SqlDateRangeWhere
2964
2965SET @TagPivotTempTable = dbo.Documents_GenerateTagPivotTempTable(@Filter, @SortField, ''#DocIds'', DEFAULT, DEFAULT)
2966
2967DECLARE @FieldsToInclude dbo.IdTableType
2968INSERT INTO @FieldsToInclude (Id)
2969SELECT ItemId
2970FROM @Filter
2971WHERE ItemType = 0 --GridItemType.Field
2972UNION
2973SELECT ItemId
2974FROM @SortField
2975WHERE ItemType = 0 --GridItemType.Field
2976UNION
2977SELECT @DateFieldId
2978WHERE @DateFieldId is not NULL
2979
2980SELECT
2981 TagGroupID
2982 ,TagGroupName
2983 ,TagId
2984 ,TagName
2985 ,IsLeafTag
2986 ,TagPath
2987INTO #vTagHierarchy
2988FROM dbo.vTagHierarchy
2989
2990
2991set @Sql = ''
2992<TagTempTable>
2993
2994Select
2995 DocId
2996FROM
2997(
2998 SELECT
2999 RowNum = <SortRowNumber>
3000 , df.DocId
3001 FROM '' + [dbo].[Documents_GenerateAliasedDocumentFields](@FieldsToInclude, @JoinOnAlias) + ''
3002 inner join #DocIds di on '' + @JoinOnAlias + ''.DocId = di.DocId
3003 <TagPivotJoin>
3004 <QuickSearchJoin>
3005 <SqlWhere>
3006)x
3007WHERE RowNum <PagingQueryWhereClause>
3008ORDER BY RowNum
3009'';
3010
3011set @sql = REPLACE(@sql,''<SortRowNumber>'',@SqlSortRowNumber);
3012set @sql = REPLACE(@sql,''<TagTempTable>'',@TagPivotTempTable);
3013if LEN(@TagPivotTempTable) > 1
3014BEGIN
3015 set @sql = REPLACE(@sql,''<TagPivotJoin>'',''INNER JOIN #TagDocs dt on dt.docid = di.docid'');
3016END
3017ELSE
3018BEGIN
3019 set @sql = REPLACE(@sql,''<TagPivotJoin>'','''');
3020END
3021SET @sql = REPLACE(@sql,''<QuickSearchJoin>'',@QuickSearchJoin);
3022set @sql = REPLACE(@sql,''<SqlWhere>'',@SqlWhere);
3023set @sql = REPLACE(@sql,''<PagingQueryWhereClause>'',@PagingQueryWhereClause);
3024
3025--print (@sql)
3026
3027execute sp_executesql @statement = @sql,
3028 @params = N''@Filter dbo.ColumnFilterType readonly, @SortField dbo.OrderedGridItemType readonly'',
3029 @Filter = @Filter,
3030 @SortField = @SortField'
3031--GO
3032EXEC sp_executesql @contextstring, N'@SQL nvarchar(max)', @SQL=@SQL
3033
3034Set @SQL = '
3035--IF OBJECT_ID(''dbo.Documents_GetPagedDocumentIdsFromRelationship'') IS NULL
3036-- EXEC (''CREATE PROCEDURE dbo.Documents_GetPagedDocumentIdsFromRelationship AS RETURN 0;'');
3037--GO
3038ALTER PROCEDURE [dbo].[Documents_GetPagedDocumentIdsFromRelationship]
3039 @ResultsId int,
3040 @UserName nvarchar(256),
3041 @PageSize int,
3042 @PageNumber int,
3043 @Filter dbo.ColumnFilterType readonly,
3044 @SortField dbo.OrderedGridItemType readonly,
3045 @WithSeconds bit,
3046 @WithMilliseconds bit,
3047 @StartDate datetime = null,
3048 @EndDate datetime = null,
3049 @IncludeNoDate bit = null,
3050 @DateFieldId int = null,
3051 @MinDate datetime,
3052 @MaxDate datetime
3053AS
3054
3055set nocount on
3056
3057declare @UserKey int
3058select @UserKey = ActivityTracking.GetUserKeyForUsername(@UserName)
3059
3060DECLARE @ResultsTableName as nvarchar(256)
3061 ,@sql as nvarchar(max) = ''''
3062 ,@PagingQueryWhereClause as nvarchar(max) = ''''
3063 ,@SqlSortRowNumber nvarchar(max) = ''''
3064 ,@SqlDateRangeWhere nvarchar(max) = ''''
3065 ,@SqlFilterWhere nvarchar(max) = ''''
3066 ,@TagPivotTempTable nvarchar(max) = ''''
3067 ,@SqlWhere nvarchar(max) = ''''
3068 ,@JoinOnAlias nvarchar(5) = ''df''
3069
3070EXEC @ResultsTableName = [UserTables].[RelationshipResultsTableName] @UserKey = @UserKey
3071
3072select @PagingQueryWhereClause = dbo.PagingQueryWhereClause(@PageNumber, @PageSize)
3073
3074exec Documents_GetDocumentIds_GenerateDocumentDateClause @StartDate, @EndDate, @IncludeNoDate, @DateFieldId, @MinDate, @MaxDate, @SqlDateRangeWhere output
3075exec Documents_GetDocumentIds_GenerateFilterClause @Filter, @WithSeconds, @WithMilliseconds, @SqlFilterWhere output
3076
3077if not exists(select * from @SortField)
3078begin
3079 set @SqlSortRowNumber = ''ROW_NUMBER() OVER(ORDER BY '' + @JoinOnAlias + ''.DocId ASC)''-- +case when @SortAscending = 0 then ''DESC'' else ''ASC'' end +'')''
3080end
3081else
3082begin
3083 select @SqlSortRowNumber = dbo.Documents_GenerateSortRowNumberStatement(@SortField)
3084end
3085
3086set @SqlWhere += ''WHERE r.ResultsId = <ResultsId>''
3087
3088--Build where clause
3089if(len(@SqlFilterWhere) > 0)
3090begin
3091 set @SqlWhere += '' AND '' + @SqlFilterWhere
3092 if(len(@SqlDateRangeWhere) > 0)
3093 set @SqlWhere += '' AND '' + @SqlDateRangeWhere
3094end
3095else if(len(@SqlDateRangeWhere) > 0)
3096 set @SqlWhere += '' AND '' + @SqlDateRangeWhere
3097
3098SET @TagPivotTempTable = dbo.Documents_GenerateTagPivotTempTable(@Filter, @SortField, @ResultsTableName, @ResultsId, DEFAULT)
3099
3100DECLARE @FieldsToInclude dbo.IdTableType
3101INSERT INTO @FieldsToInclude (Id)
3102SELECT ItemId
3103FROM @Filter
3104WHERE ItemType = 0 --GridItemType.Field
3105UNION
3106SELECT ItemId
3107FROM @SortField
3108WHERE ItemType = 0 --GridItemType.Field
3109UNION
3110SELECT @DateFieldId
3111WHERE @DateFieldId is not NULL
3112SELECT
3113 TagGroupID
3114 ,TagGroupName
3115 ,TagId
3116 ,TagName
3117 ,IsLeafTag
3118 ,TagPath
3119INTO #vTagHierarchy
3120FROM dbo.vTagHierarchy
3121set @sql = ''
3122<TagTempTable>
3123
3124Select
3125 DocId
3126FROM
3127(
3128 SELECT
3129 RowNum = <SortRowNumber>
3130 , '' + @JoinOnAlias + ''.DocId
3131 FROM '' + [dbo].[Documents_GenerateAliasedDocumentFields](@FieldsToInclude, @JoinOnAlias) + ''
3132 INNER JOIN <ResultsTableName> r on '' + @JoinOnAlias + ''.DocId = r.DocId
3133 <TagPivotJoin>
3134 <SqlWhere>
3135 ) x
3136WHERE RowNum <PagingQueryWhereClause>
3137ORDER BY RowNum '';
3138
3139set @sql = REPLACE(@sql,''<SortRowNumber>'',@SqlSortRowNumber);
3140set @sql = REPLACE(@sql,''<ResultsTableName>'',@ResultsTableName);
3141set @sql = REPLACE(@sql,''<TagTempTable>'',@TagPivotTempTable);
3142if LEN(@TagPivotTempTable) > 1
3143BEGIN
3144 set @sql = REPLACE(@sql,''<TagPivotJoin>'',''INNER JOIN #TagDocs dt on dt.docid = r.docid'');
3145END
3146ELSE
3147BEGIN
3148 set @sql = REPLACE(@sql,''<TagPivotJoin>'','''');
3149END
3150set @sql = REPLACE(@sql,''<SqlWhere>'',@SqlWhere);
3151set @sql = REPLACE(@sql,''<ResultsId>'',Convert(varchar(10), @ResultsId));
3152set @sql = REPLACE(@sql,''<PagingQueryWhereClause>'',@PagingQueryWhereClause);
3153
3154--print (@sql)
3155execute sp_executesql @statement = @sql,
3156 @params = N''@Filter dbo.ColumnFilterType readonly, @SortField dbo.OrderedGridItemType readonly'',
3157 @Filter = @Filter,
3158 @SortField = @SortField'
3159--GO
3160EXEC sp_executesql @contextstring, N'@SQL nvarchar(max)', @SQL=@SQL
3161
3162set @SQL = '
3163--IF OBJECT_ID(''dbo.Documents_GetPagedDocumentIdsFromSearch'') IS NULL
3164-- EXEC (''CREATE PROCEDURE dbo.Documents_GetPagedDocumentIdsFromSearch AS RETURN 0;'');
3165--GO
3166ALTER PROCEDURE [dbo].[Documents_GetPagedDocumentIdsFromSearch]
3167 @ResultsId int
3168 ,@UserName nvarchar(256)
3169 ,@PageSize int
3170 ,@PageNumber int
3171 ,@Filter dbo.ColumnFilterType readonly
3172 ,@SortField dbo.OrderedGridItemType readonly
3173 ,@WithSeconds bit
3174 ,@WithMilliseconds bit
3175 ,@StartDate datetime = null
3176 ,@EndDate datetime = null
3177 ,@IncludeNoDate bit = null
3178 ,@DateFieldId int = null
3179 ,@MinDate datetime
3180 ,@MaxDate DATETIME
3181 ,@QuickSearchResultsId INT
3182AS
3183
3184set nocount on
3185declare @UserKey int
3186select @UserKey = ActivityTracking.GetUserKeyForUsername(@UserName)
3187
3188DECLARE @ResultsTableName as nvarchar(256)
3189 ,@sql as nvarchar(max) = ''''
3190 ,@PagingQueryWhereClause as nvarchar(max) = ''''
3191 ,@SqlSortRowNumber nvarchar(max) = ''''
3192 ,@SqlScoreWhere nvarchar(max) = ''''
3193 ,@SqlFilterWhere nvarchar(max) = ''''
3194 ,@SqlDateRangeWhere nvarchar(max) = ''''
3195 ,@SqlScoreJoin nvarchar(max) = ''''
3196 ,@SqlWhere nvarchar(max) = ''''
3197 ,@TagPivotTempTable nvarchar(max) = ''''
3198 ,@TagPivotJoin nvarchar(max) = ''''
3199 ,@QuickSearchJoin NVARCHAR(MAX) = ''''
3200 ,@JoinOnAlias nvarchar(5) = ''df''
3201
3202SET @ResultsTableName = UserTables.SearchResultsTableName(@UserKey)
3203select @PagingQueryWhereClause = dbo.PagingQueryWhereClause(@PageNumber, @PageSize)
3204
3205IF (@QuickSearchResultsId > 0)
3206BEGIN
3207 SET @QuickSearchJoin = ''INNER JOIN '' + @ResultsTableName + '' qsr ON '' + @JoinOnAlias + ''.DocId = qsr.DocId AND qsr.ResultsId = '' + Convert(varchar(10), @QuickSearchResultsId)
3208END
3209
3210exec Documents_GetDocumentIds_GenerateDocumentDateClause @StartDate, @EndDate, @IncludeNoDate, @DateFieldId, @MinDate, @MaxDate, @SqlDateRangeWhere output
3211exec Documents_GetDocumentIds_GenerateFilterClause @Filter, @WithSeconds, @WithMilliseconds, @SqlFilterWhere output
3212
3213if not exists(select * from @SortField)
3214begin
3215 set @SqlSortRowNumber = ''ROW_NUMBER() OVER(ORDER BY '' + @JoinOnAlias + ''.DocId ASC)''-- +case when @SortAscending = 0 then ''DESC'' else ''ASC'' end +'')''
3216end
3217else
3218begin
3219 select @SqlSortRowNumber = dbo.Documents_GenerateSortRowNumberStatement(@SortField)
3220end
3221
3222if exists (select * from @Filter where ItemType = 3) -- SimilarityScore (3)
3223begin
3224 declare @ScoreValue varchar(50);
3225 select @ScoreValue =
3226 case when left(Value,1) = ''.'' then ''0'' + Value
3227 else Value
3228 end
3229 from @Filter
3230 where ItemType = 3;
3231
3232 set @SqlScoreWhere += ''( left(cast(Score.Score as varchar(50)), len('''''' + @ScoreValue + '''''')) = '''''' + @ScoreValue + '''''' ) '';
3233end
3234
3235if exists(select * from @SortField sf where sf.ItemType = 3) --GridItemType.SimilarityScore
3236or exists (select * from @Filter where ItemType = 3)
3237 SET @SqlScoreJoin = ''inner join '' + UserTables.SearchResultsScoresTableName(@UserKey) + '' Score on score.docid = r.DocId and score.ResultsId = r.ResultsId'';
3238
3239
3240set @SqlWhere += ''WHERE r.ResultsId = <ResultsId>''
3241
3242--Build where clause
3243if(len(@SqlFilterWhere) > 0)
3244 set @SqlWhere += '' AND ('' + @SqlFilterWhere + '')
3245''
3246
3247if(len(@SqlDateRangeWhere) > 0)
3248 set @SqlWhere += '' AND '' + @SqlDateRangeWhere + ''
3249''
3250
3251if(len(@SqlScoreWhere) > 0)
3252 set @SqlWhere += '' AND '' + @SqlScoreWhere + ''
3253''
3254
3255SET @TagPivotTempTable = dbo.[Documents_GenerateTagPivotTempTable](@Filter, @SortField, @ResultsTableName, @ResultsId, DEFAULT)
3256
3257DECLARE @FieldsToInclude dbo.IdTableType
3258INSERT INTO @FieldsToInclude (Id)
3259SELECT ItemId
3260FROM @Filter
3261WHERE ItemType = 0 --GridItemType.Field
3262UNION
3263SELECT ItemId
3264FROM @SortField
3265WHERE ItemType = 0 --GridItemType.Field
3266UNION
3267SELECT @DateFieldId
3268WHERE @DateFieldId is not NULL
3269SELECT
3270 TagGroupID
3271 ,TagGroupName
3272 ,TagId
3273 ,TagName
3274 ,IsLeafTag
3275 ,TagPath
3276INTO #vTagHierarchy
3277FROM dbo.vTagHierarchy
3278set @sql = ''
3279<TagTempTable>
3280
3281Select
3282 DocId
3283FROM
3284(
3285 SELECT
3286 RowNum = <SortRowNumber>
3287 , '' + @JoinOnAlias + ''.DocId
3288 FROM '' + [dbo].[Documents_GenerateAliasedDocumentFields](@FieldsToInclude, @JoinOnAlias) + ''
3289 INNER JOIN <ResultsTableName> r on '' + @JoinOnAlias + ''.DocId = r.DocId
3290 <TagPivotJoin>
3291 <QuickSearchJoin>
3292 <ScoreJoin>
3293 <SqlWhere>
3294 ) x
3295WHERE RowNum <PagingQueryWhereClause>
3296ORDER BY RowNum '';
3297
3298set @sql = REPLACE(@sql,''<SortRowNumber>'',@SqlSortRowNumber);
3299set @sql = REPLACE(@sql,''<ResultsTableName>'',@ResultsTableName);
3300
3301set @sql = REPLACE(@sql,''<TagTempTable>'',@TagPivotTempTable);
3302if LEN(@TagPivotTempTable) > 1
3303BEGIN
3304 set @sql = REPLACE(@sql,''<TagPivotJoin>'',''INNER JOIN #TagDocs dt on dt.docid = r.docid'');
3305END
3306ELSE
3307BEGIN
3308 set @sql = REPLACE(@sql,''<TagPivotJoin>'','''');
3309END
3310
3311SET @sql = REPLACE(@sql,''<QuickSearchJoin>'', @QuickSearchJoin);
3312set @sql = REPLACE(@sql,''<ScoreJoin>'', @SqlScoreJoin);
3313set @sql = REPLACE(@sql,''<SqlWhere>'',@SqlWhere);
3314set @sql = REPLACE(@sql,''<ResultsId>'',Convert(varchar(10), @ResultsId));
3315set @sql = REPLACE(@sql,''<PagingQueryWhereClause>'',@PagingQueryWhereClause);
3316
3317--print @sql
3318execute sp_executesql @statement = @sql,
3319 @params = N''@Filter dbo.ColumnFilterType readonly, @SortField dbo.OrderedGridItemType readonly'',
3320 @Filter = @Filter,
3321 @SortField = @SortField'
3322--GO
3323EXEC sp_executesql @contextstring, N'@SQL nvarchar(max)', @SQL=@SQL
3324
3325Set @SQL = '
3326--IF OBJECT_ID(''dbo.Documents_GetRelationshipCount'') IS NULL
3327-- EXEC (''CREATE PROCEDURE dbo.Documents_GetRelationshipCount AS RETURN 0;'');
3328--GO
3329ALTER PROCEDURE [dbo].[Documents_GetRelationshipCount]
3330 @ResultsId int
3331, @UserName nvarchar(256)
3332, @Filter dbo.ColumnFilterType readonly
3333, @WithSeconds bit = 0
3334, @WithMilliseconds bit = 0
3335, @StartDate datetime = null
3336, @EndDate datetime = null
3337, @IncludeNoDate bit = null
3338, @DateFieldId int = null
3339, @MinDate datetime
3340, @MaxDate datetime
3341AS
3342set nocount on
3343
3344declare @UserKey int
3345select @UserKey = ActivityTracking.GetUserKeyForUsername(@UserName)
3346
3347DECLARE @ResultsTableName as nvarchar(256)
3348select @ResultsTableName = [UserTables].[RelationshipResultsTableName](@UserKey)
3349
3350DECLARE @sql as nvarchar(max) = ''''
3351DECLARE @SqlFilterWhere nvarchar(max) = ''''
3352DECLARE @SqlDateRangeWhere nvarchar(max) = ''''
3353DECLARE @TagPivotTempTable nvarchar(max) = ''''
3354DECLARE @SqlWhere nvarchar(max) = ''''
3355
3356exec Documents_GetDocumentIds_GenerateDocumentDateClause @StartDate, @EndDate, @IncludeNoDate, @DateFieldId, @MinDate, @MaxDate, @SqlDateRangeWhere output
3357exec Documents_GetDocumentIds_GenerateFilterClause @Filter, @WithSeconds, @WithMilliseconds, @SqlFilterWhere output
3358
3359--Build where clause
3360set @SqlWhere += ''WHERE r.ResultsId = <ResultsId>''
3361if(len(@SqlFilterWhere) > 0)
3362begin
3363 set @SqlWhere += '' AND '' + @SqlFilterWhere
3364 if(len(@SqlDateRangeWhere) > 0)
3365 set @SqlWhere += '' AND '' + @SqlDateRangeWhere
3366end
3367else if(len(@SqlDateRangeWhere) > 0)
3368 set @SqlWhere += '' AND'' + @SqlDateRangeWhere
3369
3370DECLARE @SortField dbo.OrderedGridItemType
3371SET @TagPivotTempTable = dbo.Documents_GenerateTagPivotTempTable(@Filter, @SortField, @ResultsTableName, @ResultsId, DEFAULT)
3372
3373DECLARE @FieldsToInclude dbo.IdTableType
3374INSERT INTO @FieldsToInclude (Id)
3375SELECT ItemId
3376FROM @Filter
3377WHERE ItemType = 0 --GridItemType.Field
3378UNION
3379SELECT @DateFieldId
3380WHERE @DateFieldId is not NULL
3381SELECT
3382 TagGroupID
3383 ,TagGroupName
3384 ,TagId
3385 ,TagName
3386 ,IsLeafTag
3387 ,TagPath
3388INTO #vTagHierarchy
3389FROM dbo.vTagHierarchy
3390set @Sql = ''
3391<TagTempTable>
3392
3393SELECT
3394 count(df.DocId)
3395FROM '' + [dbo].[Documents_GenerateAliasedDocumentFields](@FieldsToInclude, ''df'');
3396
3397set @Sql = @SQL + ''
3398INNER JOIN <ResultsTable> r on df.DocId = r.DocId
3399<TagPivotJoin>
3400<SqlWhere>
3401'';
3402
3403set @sql = REPLACE(@sql,''<ResultsTable>'', @ResultsTableName);
3404set @sql = REPLACE(@sql,''<TagTempTable>'',@TagPivotTempTable);
3405if LEN(@TagPivotTempTable) > 1
3406BEGIN
3407 set @sql = REPLACE(@sql,''<TagPivotJoin>'',''INNER JOIN #TagDocs dt on dt.docid = df.docid'');
3408END
3409ELSE
3410BEGIN
3411 set @sql = REPLACE(@sql,''<TagPivotJoin>'','''');
3412END
3413set @sql = REPLACE(@sql,''<SqlWhere>'',@SqlWhere);
3414set @sql = REPLACE(@sql,''<ResultsId>'',isnull(cast(@ResultsId as nvarchar(18)),''-1''))
3415
3416execute sp_executesql @sql, N''@Filter dbo.ColumnFilterType readonly'', @Filter = @Filter'
3417--GO
3418EXEC sp_executesql @contextstring, N'@SQL nvarchar(max)', @SQL=@SQL
3419
3420Set @SQL = '
3421--IF OBJECT_ID(''dbo.Documents_GetSearchCount'') IS NULL
3422-- EXEC (''CREATE PROCEDURE dbo.Documents_GetSearchCount AS RETURN 0;'');
3423--GO
3424ALTER PROCEDURE [dbo].[Documents_GetSearchCount]
3425 @ResultsId int
3426, @UserName nvarchar(256)
3427, @Filter dbo.ColumnFilterType readonly
3428, @WithSeconds bit = 0
3429, @WithMilliseconds bit = 0
3430, @StartDate datetime = null
3431, @EndDate datetime = null
3432, @IncludeNoDate bit = null
3433, @DateFieldId int = null
3434, @MinDate datetime
3435, @MaxDate DATETIME
3436, @QuickSearchResultsId INT
3437AS
3438set nocount on
3439
3440declare @UserKey int
3441select @UserKey = ActivityTracking.GetUserKeyForUsername(@UserName)
3442
3443DECLARE @ResultsTableName as nvarchar(256)
3444select @ResultsTableName = UserTables.SearchResultsTableName(@UserKey)
3445
3446DECLARE @sql as nvarchar(max) = ''''
3447DECLARE @SqlFilterWhere nvarchar(max) = ''''
3448DECLARE @SqlScoreJoin nvarchar(max) = ''''
3449DECLARE @SqlDateRangeWhere nvarchar(max) = ''''
3450DECLARE @SqlScoreWhere nvarchar(max) = ''''
3451DECLARE @TagPivotTempTable nvarchar(max) = ''''
3452DECLARE @TagPivotJoin nvarchar(max) = ''''
3453DECLARE @QuickSearchJoin NVARCHAR(MAX) = ''''
3454DECLARE @SqlWhere nvarchar(max) = ''''
3455
3456exec Documents_GetDocumentIds_GenerateDocumentDateClause @StartDate, @EndDate, @IncludeNoDate, @DateFieldId, @MinDate, @MaxDate, @SqlDateRangeWhere output
3457exec Documents_GetDocumentIds_GenerateFilterClause @Filter, @WithSeconds, @WithMilliseconds, @SqlFilterWhere OUTPUT
3458
3459IF (@QuickSearchResultsId > 0)
3460BEGIN
3461 SET @QuickSearchJoin = ''INNER JOIN '' + @ResultsTableName + '' qsr ON df.DocId = qsr.DocId AND qsr.ResultsId = '' + Convert(varchar(10), @QuickSearchResultsId)
3462END
3463
3464if exists (select * from @Filter where ItemType = 3) -- SimilarityScore (3)
3465begin
3466 declare @ScoreValue varchar(50);
3467 select @ScoreValue =
3468 case when left(Value,1) = ''.'' then ''0'' + Value
3469 else Value
3470 end
3471 from @Filter
3472 where ItemType = 3;
3473
3474 set @SqlScoreWhere = ''( left(cast(Score.Score as varchar(50)), len('''''' + @ScoreValue + '''''')) = '''''' + @ScoreValue + '''''' )'';
3475 set @SqlScoreJoin = ''inner join '' + UserTables.SearchResultsScoresTableName(@UserKey) + '' Score on score.docid = r.DocId and score.ResultsId = r.ResultsId'';
3476end
3477
3478set @SqlWhere += ''WHERE r.ResultsId = <ResultsId>''
3479
3480--Build where clause
3481if(len(@SqlFilterWhere) > 0)
3482 set @SqlWhere += '' AND ('' + @SqlFilterWhere + '')
3483''
3484
3485if(len(@SqlDateRangeWhere) > 0)
3486 set @SqlWhere += '' AND '' + @SqlDateRangeWhere + ''
3487''
3488
3489if(len(@SqlScoreWhere) > 0)
3490 set @SqlWhere += '' AND '' + @SqlScoreWhere + ''
3491''
3492
3493DECLARE @SortField dbo.OrderedGridItemType
3494SET @TagPivotTempTable = dbo.[Documents_GenerateTagPivotTempTable](@Filter, @SortField, @ResultsTableName, @ResultsId, DEFAULT)
3495
3496DECLARE @FieldsToInclude dbo.IdTableType
3497INSERT INTO @FieldsToInclude (Id)
3498SELECT ItemId
3499FROM @Filter
3500WHERE ItemType = 0 --GridItemType.Field
3501UNION
3502SELECT @DateFieldId
3503WHERE @DateFieldId is not NULL
3504SELECT
3505 TagGroupID
3506 ,TagGroupName
3507 ,TagId
3508 ,TagName
3509 ,IsLeafTag
3510 ,TagPath
3511INTO #vTagHierarchy
3512FROM dbo.vTagHierarchy
3513set @Sql = ''
3514<TagTempTable>
3515
3516SELECT
3517 count(df.DocId)
3518FROM '' + [dbo].[Documents_GenerateAliasedDocumentFields](@FieldsToInclude, ''df'');
3519
3520set @Sql = @SQL + ''
3521INNER JOIN <ResultsTable> r on df.DocId = r.DocId
3522<TagPivotJoin>
3523<QuickSearchJoin>
3524<ScoreJoin>
3525<SqlWhere>
3526'';
3527
3528set @sql = REPLACE(@sql,''<ResultsTable>'', @ResultsTableName);
3529set @sql = REPLACE(@sql,''<TagTempTable>'',@TagPivotTempTable);
3530if LEN(@TagPivotTempTable) > 1
3531BEGIN
3532 set @sql = REPLACE(@sql,''<TagPivotJoin>'',''INNER JOIN #TagDocs dt on dt.docid = r.docid'');
3533END
3534ELSE
3535BEGIN
3536 set @sql = REPLACE(@sql,''<TagPivotJoin>'','''');
3537END
3538set @sql = REPLACE(@sql,''<QuickSearchJoin>'', @QuickSearchJoin);
3539set @sql = REPLACE(@sql,''<ScoreJoin>'', @SqlScoreJoin);
3540set @sql = REPLACE(@sql,''<SqlWhere>'',@SqlWhere);
3541set @sql = REPLACE(@sql,''<ResultsId>'',isnull(cast(@ResultsId as nvarchar(18)),''-1''))
3542
3543--print @sql
3544execute sp_executesql @sql, N''@Filter dbo.ColumnFilterType readonly'', @Filter = @Filter'
3545--GO
3546EXEC sp_executesql @contextstring, N'@SQL nvarchar(max)', @SQL=@SQL
3547
3548Set @SQL = '
3549--IF OBJECT_ID(''dbo.Documents_GenerateTagPivotTempTable'') IS NULL
3550-- EXEC (''CREATE PROCEDURE dbo.Documents_GenerateTagPivotTempTable AS RETURN 0;'');
3551--GO
3552ALTER FUNCTION [dbo].[Documents_GenerateTagPivotTempTable]
3553(
3554 @Filter dbo.ColumnFilterType readonly,
3555 @SortField dbo.OrderedGridItemType readonly,
3556 @DocIdSourceTableName nvarchar(256),
3557 @ResultsId int = NULL,
3558 @BatchId int = NULL
3559)
3560RETURNS nvarchar(max)
3561AS
3562BEGIN
3563 DECLARE @TagGroupCols nvarchar(max)
3564
3565 SET @TagGroupCols =
3566 stuff((
3567 select '',[TG - '' + TagGroupName + '']''
3568 from(
3569 select th.TagGroupName as TagGroupName
3570 from dbo.vTagHierarchy th
3571 inner join @SortField sf on th.TagGroupId = sf.ItemId and sf.ItemType = 1 --tag group
3572 union
3573 select th.TagGroupName as TagGroupName
3574 from dbo.vTagHierarchy th
3575 inner join @Filter f on th.TagGroupId = f.ItemId and f.ItemType = 1 --tag group
3576 ) cols
3577 group by TagGroupName
3578 order by TagGroupName
3579 for xml path(''''), TYPE
3580 ).value(''.'', ''nvarchar(max)'')
3581 ,1,1,'''')
3582
3583 declare @HasTagSelections bit
3584 set @HasTagSelections = case when @TagGroupCols is null then 0 else 1 end
3585 RETURN case when @HasTagSelections = 1 then ''
3586 select DocId, '' + @TagGroupCols + N''
3587 into #TagDocs
3588 from
3589 (
3590 select
3591 d1.DocId,
3592 TagGroupName = ''''TG - '''' + th1.TagGroupName,
3593 stuff(
3594 (
3595 select ''''; '''' + th2.TagName
3596 from #vTagHierarchy th2
3597 left join dbo.DocumentTags dt2 on th2.TagId = dt2.TagId
3598 where dt2.DocId = d1.DocId and th1.TagGroupName = th2.TagGroupName and th2.IsLeafTag = 1
3599 order by th2.TagPath
3600 for xml path(''''''''), TYPE
3601 ).value(''''.'''', ''''varchar(max)''''), 1, 2, ''''''''
3602 ) as TagName
3603 from '' + @DocIdSourceTableName + '' d1
3604 left join dbo.DocumentTags dt1 on d1.DocId = dt1.DocId
3605 left join #vTagHierarchy th1 on th1.TagId = dt1.TagId
3606 '' + case when exists(select * from @SortField) then ''left join @SortField sf on th1.TagGroupId = sf.ItemId and sf.ItemType = 1 --tag group'' else '''' end + ''
3607 '' + case when exists(select * from @Filter) then ''left join @Filter f on th1.TagGroupId = f.ItemId and f.ItemType = 1 --tag group'' else '''' end + ''
3608 where (th1.IsLeafTag = 1 or th1.IsLeafTag is null)
3609 '' + case when @ResultsId is not null then ''and d1.ResultsId = '' + CAST(@ResultsId as nvarchar(10)) else '''' end + ''
3610 '' + case when @BatchId is not null then ''and d1.BatchId = '' + CAST(@BatchId as nvarchar(10)) else '''' end + ''
3611 group by th1.TagGroupName, d1.DocId
3612 ) x
3613 pivot
3614 (
3615 max(TagName)
3616 for TagGroupName in ('' + @TagGroupCols + N'')
3617 ) p
3618 '' else '''' end
3619END'
3620EXEC sp_executesql @contextstring, N'@SQL nvarchar(max)', @SQL=@SQL
3621
3622--GO
3623
3624Set @SQL = '
3625--IF OBJECT_ID(''dbo.Documents_GetBatchCount'') IS NULL
3626-- EXEC (''CREATE PROCEDURE dbo.Documents_GetBatchCount AS RETURN 0;'');
3627--GO
3628ALTER PROCEDURE [dbo].[Documents_GetBatchCount]
3629 @BatchId int,
3630 @FilterReviewed int,
3631 @Filter dbo.ColumnFilterType readonly,
3632 @WithSeconds bit,
3633 @WithMilliseconds bit,
3634 @StartDate datetime = null,
3635 @EndDate datetime = null,
3636 @IncludeNoDate bit = null,
3637 @DateFieldId int = null,
3638 @MinDate datetime,
3639 @MaxDate datetime,
3640 @QuickSearchResultsId INT,
3641 @UserName NVARCHAR(256)
3642AS
3643set nocount on
3644
3645DECLARE @sql as nvarchar(max) = ''''
3646DECLARE @SqlDateRangeWhere nvarchar(max) = ''''
3647DECLARE @SqlFilterWhere nvarchar(max) = ''''
3648DECLARE @SqlBatchJoin nvarchar(max) = ''''
3649DECLARE @TagPivotTempTable nvarchar(max) = ''''
3650DECLARE @QuickSearchJoin NVARCHAR(MAX) = ''''
3651DECLARE @SqlWhere nvarchar(max) = ''''
3652
3653exec Documents_GetDocumentIds_GenerateDocumentDateClause @StartDate, @EndDate, @IncludeNoDate, @DateFieldId, @MinDate, @MaxDate, @SqlDateRangeWhere output
3654exec Documents_GetDocumentIds_GenerateFilterClause @Filter, @WithSeconds, @WithMilliseconds, @SqlFilterWhere output
3655
3656if (@BatchId > 0)
3657begin
3658 set @SqlBatchJoin = ''INNER JOIN Review.BatchDocument bd ON df.DocId = bd.DocId AND bd.BatchId = '' + Convert(varchar(10), @BatchId)
3659 if (@FilterReviewed = 1)
3660 set @SqlBatchJoin = @SqlBatchJoin + '' AND bd.ReviewStatusId = 1 ''
3661 else if (@FilterReviewed = 2)
3662 set @SqlBatchJoin = @SqlBatchJoin + '' AND bd.ReviewStatusId <> 1 ''
3663end
3664
3665IF (@QuickSearchResultsId > 0)
3666BEGIN
3667 declare @UserKey INT, @ResultsTableName as nvarchar(256)
3668 SELECT @UserKey = ActivityTracking.GetUserKeyForUsername(@UserName)
3669 SET @ResultsTableName = UserTables.SearchResultsTableName(@UserKey)
3670
3671 SET @QuickSearchJoin = ''INNER JOIN '' + @ResultsTableName + '' qsr ON bd.DocId = qsr.DocId AND qsr.ResultsId = '' + Convert(varchar(10), @QuickSearchResultsId)
3672END
3673
3674--Build where clause
3675if(len(@SqlFilterWhere) > 0)
3676begin
3677 set @SqlWhere += ''WHERE '' + @SqlFilterWhere
3678 if(len(@SqlDateRangeWhere) > 0)
3679 set @SqlWhere += '' AND '' + @SqlDateRangeWhere
3680end
3681else if(len(@SqlDateRangeWhere) > 0)
3682 set @SqlWhere += ''WHERE '' + @SqlDateRangeWhere
3683
3684DECLARE @SortField dbo.OrderedGridItemType
3685IF(@BatchId > 0)
3686 SET @TagPivotTempTable = dbo.Documents_GenerateTagPivotTempTable(@Filter, @SortField, ''Review.BatchDocument'', DEFAULT, @BatchId)
3687ELSE
3688 SET @TagPivotTempTable = dbo.Documents_GenerateTagPivotTempTable(@Filter, @SortField, ''dbo.Documents'', DEFAULT, DEFAULT)
3689
3690DECLARE @FieldsToInclude dbo.IdTableType
3691INSERT INTO @FieldsToInclude (Id)
3692SELECT ItemId
3693FROM @Filter
3694WHERE ItemType = 0 --GridItemType.Field
3695UNION
3696SELECT @DateFieldId
3697WHERE @DateFieldId is not NULL
3698
3699SELECT
3700 TagGroupID
3701 ,TagGroupName
3702 ,TagId
3703 ,TagName
3704 ,IsLeafTag
3705 ,TagPath
3706INTO #vTagHierarchy
3707FROM dbo.vTagHierarchy
3708
3709set @Sql = ''
3710<TagTempTable>
3711
3712SELECT
3713 count(df.DocId)
3714FROM '' + [dbo].[Documents_GenerateAliasedDocumentFields](@FieldsToInclude, ''df'');
3715
3716set @SQL = @SQL + ''
3717<TagPivotJoin>
3718<BatchJoin>
3719<QuickSearchJoin>
3720<SqlWhere>
3721'';
3722set @sql = REPLACE(@sql,''<TagTempTable>'',@TagPivotTempTable);
3723if LEN(@TagPivotTempTable) > 1
3724BEGIN
3725 set @sql = REPLACE(@sql,''<TagPivotJoin>'',''INNER JOIN #TagDocs dt on dt.docid = df.docid'');
3726END
3727ELSE
3728BEGIN
3729 set @sql = REPLACE(@sql,''<TagPivotJoin>'','''');
3730END
3731set @sql = REPLACE(@sql,''<BatchJoin>'', @SqlBatchJoin);
3732set @sql = REPLACE(@sql,''<QuickSearchJoin>'', @QuickSearchJoin);
3733set @sql = REPLACE(@sql,''<SqlWhere>'', @SqlWhere);
3734
3735execute sp_executesql @sql, N''@Filter dbo.ColumnFilterType readonly'', @Filter = @Filter'
3736--GO
3737EXEC sp_executesql @contextstring, N'@SQL nvarchar(max)', @SQL=@SQL
3738
3739Set @SQL = '
3740--IF OBJECT_ID(''dbo.Documents_GetDocIdCount'') IS NULL
3741-- EXEC (''CREATE PROCEDURE dbo.Documents_GetDocIdCount AS RETURN 0;'');
3742--GO
3743ALTER PROCEDURE [dbo].[Documents_GetDocIdCount]
3744 @DocIds dbo.IdTableType readonly,
3745 @Filter dbo.ColumnFilterType readonly,
3746 @WithSeconds bit,
3747 @WithMilliseconds bit,
3748 @StartDate datetime = null,
3749 @EndDate datetime = null,
3750 @IncludeNoDate bit = null,
3751 @DateFieldId int = null,
3752 @MinDate datetime,
3753 @MaxDate datetime,
3754 @QuickSearchResultsId INT,
3755 @UserName NVARCHAR(256)
3756AS
3757set nocount on
3758
3759DECLARE @sql as nvarchar(max) = ''''
3760DECLARE @SqlDateRangeWhere nvarchar(max) = ''''
3761DECLARE @SqlFilterWhere nvarchar(max) = ''''
3762DECLARE @TagPivotTempTable nvarchar(max) = ''''
3763DECLARE @QuickSearchJoin NVARCHAR(MAX) = ''''
3764DECLARE @SqlWhere nvarchar(max) = ''''
3765
3766exec Documents_GetDocumentIds_GenerateDocumentDateClause @StartDate, @EndDate, @IncludeNoDate, @DateFieldId, @MinDate, @MaxDate, @SqlDateRangeWhere output
3767exec Documents_GetDocumentIds_GenerateFilterClause @Filter, @WithSeconds, @WithMilliseconds, @SqlFilterWhere OUTPUT
3768
3769IF (@QuickSearchResultsId > 0)
3770BEGIN
3771 declare @UserKey INT, @ResultsTableName as nvarchar(256)
3772 SELECT @UserKey = ActivityTracking.GetUserKeyForUsername(@UserName)
3773 SET @ResultsTableName = UserTables.SearchResultsTableName(@UserKey)
3774
3775 SET @QuickSearchJoin = ''INNER JOIN '' + @ResultsTableName + '' qsr ON df.DocId = qsr.DocId AND qsr.ResultsId = '' + Convert(varchar(10), @QuickSearchResultsId)
3776END
3777
3778select
3779 DocId = Id
3780into #ForDocIds
3781from @DocIds
3782
3783--Build where clause
3784if(len(@SqlFilterWhere) > 0)
3785begin
3786 set @SqlWhere += ''WHERE '' + @SqlFilterWhere
3787 if(len(@SqlDateRangeWhere) > 0)
3788 set @SqlWhere += '' AND '' + @SqlDateRangeWhere
3789end
3790else if(len(@SqlDateRangeWhere) > 0)
3791 set @SqlWhere += ''WHERE '' + @SqlDateRangeWhere
3792
3793DECLARE @SortField dbo.OrderedGridItemType
3794SET @TagPivotTempTable = dbo.Documents_GenerateTagPivotTempTable(@Filter, @SortField, ''#ForDocIds'', DEFAULT, DEFAULT)
3795
3796DECLARE @FieldToInclude dbo.IdTableType
3797INSERT INTO @FieldToInclude (Id)
3798SELECT ItemId
3799FROM @Filter
3800WHERE ItemType = 0 --GridItemType.Field
3801UNION
3802SELECT @DateFieldId
3803WHERE @DateFieldId is not NULL
3804
3805SELECT
3806 TagGroupID
3807 ,TagGroupName
3808 ,TagId
3809 ,TagName
3810 ,IsLeafTag
3811 ,TagPath
3812INTO #vTagHierarchy
3813FROM dbo.vTagHierarchy
3814
3815set @Sql = ''
3816<TagTempTable>
3817
3818SELECT
3819 count(df.DocId)
3820FROM '' + [dbo].[Documents_GenerateAliasedDocumentFields](@FieldToInclude, ''df'');
3821
3822SET @SQL = @SQL + ''
3823inner join #ForDocIds fd on df.DocId = fd.DocId
3824<TagPivotJoin>
3825<QuickSearchJoin>
3826<SqlWhere>
3827'';
3828
3829set @sql = REPLACE(@sql,''<TagTempTable>'',@TagPivotTempTable);
3830if LEN(@TagPivotTempTable) > 1
3831BEGIN
3832 set @sql = REPLACE(@sql,''<TagPivotJoin>'',''INNER JOIN #TagDocs dt on dt.docid = df.docid'');
3833END
3834ELSE
3835BEGIN
3836 set @sql = REPLACE(@sql,''<TagPivotJoin>'','''');
3837END
3838set @sql = REPLACE(@sql,''<QuickSearchJoin>'', @QuickSearchJoin);
3839set @sql = REPLACE(@sql,''<SqlWhere>'',@SqlWhere);
3840
3841execute sp_executesql @sql, N''@Filter dbo.ColumnFilterType readonly'', @Filter = @Filter'
3842--GO
3843EXEC sp_executesql @contextstring, N'@SQL nvarchar(max)', @SQL=@SQL
3844
3845Set @SQL = '
3846--IF OBJECT_ID(''dbo.Documents_GetDocumentDateCountsFromBatch'') IS NULL
3847-- EXEC (''CREATE PROCEDURE dbo.Documents_GetDocumentDateCountsFromBatch AS RETURN 0;'');
3848--GO
3849ALTER PROCEDURE [dbo].[Documents_GetDocumentDateCountsFromBatch]
3850 @BatchId int,
3851 @FilterReviewed int,
3852 @Filter dbo.ColumnFilterType readonly,
3853 @WithSeconds bit,
3854 @WithMilliseconds bit,
3855 @StartDate datetime = null,
3856 @EndDate datetime = null,
3857 @IncludeNoDate bit,
3858 @DateFieldId int,
3859 @MinDate datetime,
3860 @MaxDate datetime
3861AS
3862set nocount on
3863
3864DECLARE @sql as nvarchar(max) = ''''
3865DECLARE @SqlDateRangeWhere nvarchar(max) = ''''
3866DECLARE @SqlFilterWhere nvarchar(max) = ''''
3867DECLARE @SqlBatchJoin nvarchar(max) = ''''
3868DECLARE @TagPivotTempTable nvarchar(max) = ''''
3869DECLARE @SqlWhere nvarchar(max) = ''''
3870DECLARE @JoinOnAlias nvarchar(5) = ''df''
3871DECLARE @DocumentDateCol nvarchar(max) = ''''
3872
3873select
3874 @DocumentDateCol = fd.ColumnName
3875from dbo.vFieldDefinition fd
3876where fd.FieldId = @DateFieldId
3877
3878exec Documents_GetDocumentIds_GenerateDocumentDateClause @StartDate, @EndDate, @IncludeNoDate, @DateFieldId, @MinDate, @MaxDate, @SqlDateRangeWhere output
3879exec Documents_GetDocumentIds_GenerateFilterClause @Filter, @WithSeconds, @WithMilliseconds, @SqlFilterWhere output
3880
3881if (@BatchId > 0)
3882begin
3883 set @SqlBatchJoin = ''INNER JOIN Review.BatchDocument bd ON df.DocId = bd.DocId AND bd.BatchId = '' + Convert(varchar(10), @BatchId)
3884 if (@FilterReviewed = 1)
3885 set @SqlBatchJoin = @SqlBatchJoin + '' AND bd.ReviewStatusId = 1 ''
3886 else if (@FilterReviewed = 2)
3887 set @SqlBatchJoin = @SqlBatchJoin + '' AND bd.ReviewStatusId <> 1 ''
3888end
3889
3890--Build where clause
3891if(len(@SqlFilterWhere) > 0)
3892begin
3893 set @SqlWhere += ''WHERE '' + @SqlFilterWhere
3894 if(len(@SqlDateRangeWhere) > 0)
3895 set @SqlWhere += '' AND '' + @SqlDateRangeWhere
3896end
3897else if(len(@SqlDateRangeWhere) > 0)
3898 set @SqlWhere += ''WHERE '' + @SqlDateRangeWhere
3899
3900DECLARE @SortField dbo.OrderedGridItemType
3901IF(@BatchId > 0)
3902 SET @TagPivotTempTable = dbo.Documents_GenerateTagPivotTempTable(@Filter, @SortField, ''Review.BatchDocument'', DEFAULT, @BatchId)
3903ELSE
3904 SET @TagPivotTempTable = dbo.Documents_GenerateTagPivotTempTable(@Filter, @SortField, ''dbo.Documents'', DEFAULT, DEFAULT)
3905
3906DECLARE @FieldsToInclude dbo.IdTableType
3907INSERT INTO @FieldsToInclude (Id)
3908SELECT ItemId
3909FROM @Filter
3910WHERE ItemType = 0 --GridItemType.Field
3911UNION
3912SELECT @DateFieldId
3913WHERE @DateFieldId is not NULL
3914
3915SELECT
3916 TagGroupID
3917 ,TagGroupName
3918 ,TagId
3919 ,TagName
3920 ,IsLeafTag
3921 ,TagPath
3922INTO #vTagHierarchy
3923FROM dbo.vTagHierarchy
3924
3925set @Sql = ''
3926<TagTempTable>
3927
3928select
3929 DocumentDate = CAST('' + @JoinOnAlias + ''.'' + @DocumentDateCol + '' AS DATE),
3930 NumDocuments = COUNT('' + @JoinOnAlias + ''.DocId)
3931FROM '' + [dbo].[Documents_GenerateAliasedDocumentFields](@FieldsToInclude, ''df'');
3932
3933SET @SQL = @SQL + ''
3934<TagPivotJoin>
3935<BatchJoin>
3936<SqlWhere>
3937GROUP BY CAST('' + @JoinOnAlias + ''.'' + @DocumentDateCol + '' AS DATE)
3938ORDER BY CAST('' + @JoinOnAlias + ''.'' + @DocumentDateCol + '' AS DATE)
3939''
3940
3941set @sql = REPLACE(@sql,''<TagTempTable>'',@TagPivotTempTable);
3942if LEN(@TagPivotTempTable) > 1
3943BEGIN
3944 set @sql = REPLACE(@sql,''<TagPivotJoin>'',''INNER JOIN #TagDocs dt on dt.docid = df.docid'');
3945END
3946ELSE
3947BEGIN
3948 set @sql = REPLACE(@sql,''<TagPivotJoin>'','''');
3949END
3950set @sql = REPLACE(@sql,''<BatchJoin>'', @SqlBatchJoin);
3951set @sql = REPLACE(@sql,''<SqlWhere>'',@SqlWhere);
3952
3953--print (@sql)
3954execute sp_executesql @sql, N''@Filter dbo.ColumnFilterType readonly'', @Filter = @Filter'
3955--GO
3956EXEC sp_executesql @contextstring, N'@SQL nvarchar(max)', @SQL=@SQL
3957
3958Set @SQL = '
3959--IF OBJECT_ID(''dbo.Documents_GetDocumentDateCountsFromDocIds'') IS NULL
3960-- EXEC (''CREATE PROCEDURE dbo.Documents_GetDocumentDateCountsFromDocIds AS RETURN 0;'');
3961--GO
3962ALTER PROCEDURE [dbo].[Documents_GetDocumentDateCountsFromDocIds]
3963 @DocIds dbo.IdTableType readonly,
3964 @Filter dbo.ColumnFilterType readonly,
3965 @WithSeconds bit,
3966 @WithMilliseconds bit,
3967 @StartDate datetime = null,
3968 @EndDate datetime = null,
3969 @IncludeNoDate bit,
3970 @DateFieldId int,
3971 @MinDate datetime,
3972 @MaxDate datetime
3973AS
3974set nocount on
3975
3976DECLARE @sql as nvarchar(max) = ''''
3977DECLARE @SqlDateRangeWhere nvarchar(max) = ''''
3978DECLARE @SqlFilterWhere nvarchar(max) = ''''
3979DECLARE @TagPivotTempTable nvarchar(max) = ''''
3980DECLARE @SqlWhere nvarchar(max) = ''''
3981DECLARE @JoinOnAlias nvarchar(5) = ''df''
3982DECLARE @DocumentDateCol nvarchar(max) = ''''
3983
3984select
3985 @DocumentDateCol = fd.ColumnName
3986from dbo.vFieldDefinition fd
3987where fd.FieldId = @DateFieldId
3988
3989select
3990 DocId = Id
3991into #ForDocIds
3992from @DocIds
3993
3994exec Documents_GetDocumentIds_GenerateDocumentDateClause @StartDate, @EndDate, @IncludeNoDate, @DateFieldId, @MinDate, @MaxDate, @SqlDateRangeWhere output
3995exec Documents_GetDocumentIds_GenerateFilterClause @Filter, @WithSeconds, @WithMilliseconds, @SqlFilterWhere output
3996
3997--Build where clause
3998if(len(@SqlFilterWhere) > 0)
3999begin
4000 set @SqlWhere += ''WHERE '' + @SqlFilterWhere
4001 if(len(@SqlDateRangeWhere) > 0)
4002 set @SqlWhere += '' AND '' + @SqlDateRangeWhere
4003end
4004else if(len(@SqlDateRangeWhere) > 0)
4005 set @SqlWhere += ''WHERE '' + @SqlDateRangeWhere
4006
4007DECLARE @SortField dbo.OrderedGridItemType
4008SET @TagPivotTempTable = dbo.Documents_GenerateTagPivotTempTable(@Filter, @SortField, ''#ForDocIds'', DEFAULT, DEFAULT)
4009
4010DECLARE @FieldsToInclude dbo.IdTableType
4011INSERT INTO @FieldsToInclude (Id)
4012SELECT ItemId
4013FROM @Filter
4014WHERE ItemType = 0 --GridItemType.Field
4015UNION
4016SELECT @DateFieldId
4017WHERE @DateFieldId is not NULL
4018
4019SELECT
4020 TagGroupID
4021 ,TagGroupName
4022 ,TagId
4023 ,TagName
4024 ,IsLeafTag
4025 ,TagPath
4026INTO #vTagHierarchy
4027FROM dbo.vTagHierarchy
4028
4029set @Sql = ''
4030<TagTempTable>
4031
4032select
4033 DocumentDate = CAST('' + @DocumentDateCol + '' AS DATE),
4034 NumDocuments = COUNT('' + ''df.DocId)
4035FROM '' + [dbo].[Documents_GenerateAliasedDocumentFields](@FieldsToInclude, ''df'');
4036
4037SET @SQL = @SQL + ''
4038inner join #ForDocIds fd on df.DocId = fd.DocId
4039<TagPivotJoin>
4040<SqlWhere>
4041GROUP BY CAST('' + @DocumentDateCol + '' AS DATE)
4042ORDER BY CAST('' + @DocumentDateCol + '' AS DATE)
4043'';
4044
4045set @sql = REPLACE(@sql,''<TagTempTable>'',@TagPivotTempTable);
4046if LEN(@TagPivotTempTable) > 1
4047BEGIN
4048 set @sql = REPLACE(@sql,''<TagPivotJoin>'',''INNER JOIN #TagDocs dt on dt.docid = df.docid'');
4049END
4050ELSE
4051BEGIN
4052 set @sql = REPLACE(@sql,''<TagPivotJoin>'','''');
4053END
4054set @sql = REPLACE(@sql,''<SqlWhere>'',@SqlWhere);
4055set @sql = REPLACE(@sql,''<CurrentDatabase>'', DB_NAME())
4056
4057--print (@sql)
4058execute sp_executesql @sql, N''@Filter dbo.ColumnFilterType readonly'', @Filter = @Filter'
4059--GO
4060EXEC sp_executesql @contextstring, N'@SQL nvarchar(max)', @SQL=@SQL
4061
4062Set @SQL = '
4063--IF OBJECT_ID(''dbo.Documents_GetDocumentDateCountsFromRelationship'') IS NULL
4064-- EXEC (''CREATE PROCEDURE dbo.Documents_GetDocumentDateCountsFromRelationship AS RETURN 0;'');
4065--GO
4066ALTER PROCEDURE [dbo].[Documents_GetDocumentDateCountsFromRelationship]
4067 @ResultsId int,
4068 @UserName nvarchar(256),
4069 @Filter dbo.ColumnFilterType readonly,
4070 @WithSeconds bit,
4071 @WithMilliseconds bit,
4072 @StartDate datetime = null,
4073 @EndDate datetime = null,
4074 @IncludeNoDate bit,
4075 @DateFieldId int,
4076 @MinDate datetime,
4077 @MaxDate datetime
4078AS
4079
4080set nocount on
4081
4082DECLARE @sql as nvarchar(max) = ''''
4083DECLARE @SqlDateRangeWhere nvarchar(max) = ''''
4084DECLARE @SqlFilterWhere nvarchar(max) = ''''
4085DECLARE @TagPivotTempTable nvarchar(max) = ''''
4086DECLARE @SqlWhere nvarchar(max) = ''''
4087DECLARE @UserKey int
4088DECLARE @ResultsTableName as nvarchar(256)
4089DECLARE @DocumentDateCol nvarchar(max) = ''''
4090
4091select
4092 @DocumentDateCol = fd.ColumnName
4093from dbo.vFieldDefinition fd
4094where fd.FieldId = @DateFieldId
4095
4096select @UserKey = ActivityTracking.GetUserKeyForUsername(@UserName)
4097exec @ResultsTableName = [UserTables].[RelationshipResultsTableName] @UserKey = @UserKey
4098
4099exec Documents_GetDocumentIds_GenerateDocumentDateClause @StartDate, @EndDate, @IncludeNoDate, @DateFieldId, @MinDate, @MaxDate, @SqlDateRangeWhere output
4100exec Documents_GetDocumentIds_GenerateFilterClause @Filter, @WithSeconds, @WithMilliseconds, @SqlFilterWhere output
4101
4102--Build where clause
4103set @SqlWhere += ''WHERE r.ResultsId = <ResultsId>''
4104if(len(@SqlFilterWhere) > 0)
4105begin
4106 set @SqlWhere += '' AND '' + @SqlFilterWhere
4107 if(len(@SqlDateRangeWhere) > 0)
4108 set @SqlWhere += '' AND '' + @SqlDateRangeWhere
4109end
4110else if(len(@SqlDateRangeWhere) > 0)
4111 set @SqlWhere += '' AND '' + @SqlDateRangeWhere
4112
4113DECLARE @SortField dbo.OrderedGridItemType
4114SET @TagPivotTempTable = dbo.Documents_GenerateTagPivotTempTable(@Filter, @SortField, @ResultsTableName, @ResultsId, DEFAULT)
4115
4116DECLARE @FieldsToInclude dbo.IdTableType
4117INSERT INTO @FieldsToInclude (Id)
4118SELECT ItemId
4119FROM @Filter
4120WHERE ItemType = 0 --GridItemType.Field
4121UNION
4122SELECT @DateFieldId
4123WHERE @DateFieldId is not NULL
4124
4125SELECT
4126 TagGroupID
4127 ,TagGroupName
4128 ,TagId
4129 ,TagName
4130 ,IsLeafTag
4131 ,TagPath
4132INTO #vTagHierarchy
4133FROM dbo.vTagHierarchy
4134
4135set @Sql = ''
4136<TagTempTable>
4137
4138select
4139 DocumentDate = CAST('' + @DocumentDateCol + '' AS DATE),
4140 NumDocuments = COUNT(df.DocId)
4141FROM '' + [dbo].[Documents_GenerateAliasedDocumentFields](@FieldsToInclude, ''df'')
4142
4143SET @SQL = @SQL + ''
4144INNER JOIN <ResultsTableName> r on df.DocId = r.DocId
4145<TagPivotJoin>
4146<SqlWhere>
4147GROUP BY CAST('' + @DocumentDateCol + '' AS DATE)
4148ORDER BY CAST('' + @DocumentDateCol + '' AS DATE)
4149'';
4150
4151set @sql = REPLACE(@sql,''<ResultsTableName>'',@ResultsTableName);
4152set @sql = REPLACE(@sql,''<TagTempTable>'',@TagPivotTempTable);
4153if LEN(@TagPivotTempTable) > 1
4154BEGIN
4155 set @sql = REPLACE(@sql,''<TagPivotJoin>'',''INNER JOIN #TagDocs dt on dt.docid = df.docid'');
4156END
4157ELSE
4158BEGIN
4159 set @sql = REPLACE(@sql,''<TagPivotJoin>'','''');
4160END
4161set @sql = REPLACE(@sql,''<SqlWhere>'',@SqlWhere);
4162set @sql = REPLACE(@sql,''<ResultsId>'',Convert(varchar(10), @ResultsId));
4163
4164execute sp_executesql @sql, N''@Filter dbo.ColumnFilterType readonly'', @Filter = @Filter'
4165EXEC sp_executesql @contextstring, N'@SQL nvarchar(max)', @SQL=@SQL
4166
4167--GO
4168Set @SQL = '
4169--IF OBJECT_ID(''dbo.Documents_GetFilteredDocumentIdsFromDocIds'') IS NULL
4170-- EXEC (''CREATE PROCEDURE dbo.Documents_GetFilteredDocumentIdsFromDocIds AS RETURN 0;'');
4171--GO
4172ALTER PROCEDURE [dbo].[Documents_GetFilteredDocumentIdsFromDocIds]
4173 @DocIds IdTableType readonly,
4174 @Filter dbo.ColumnFilterType readonly,
4175 @WithSeconds bit,
4176 @WithMilliseconds bit,
4177 @StartDate datetime = null,
4178 @EndDate datetime = null,
4179 @IncludeNoDate bit = null,
4180 @DateFieldId int = null,
4181 @MinDate datetime,
4182 @MaxDate datetime
4183AS
4184 set nocount on
4185
4186DECLARE @sql as nvarchar(max) = ''''
4187DECLARE @SqlDateRangeWhere nvarchar(max) = ''''
4188DECLARE @SqlFilterWhere nvarchar(max) = ''''
4189DECLARE @TagPivotTempTable nvarchar(max) = ''''
4190DECLARE @SqlWhere nvarchar(max) = ''''
4191DECLARE @JoinOnAlias nvarchar(5) = ''df''
4192
4193
4194exec Documents_GetDocumentIds_GenerateDocumentDateClause @StartDate, @EndDate, @IncludeNoDate, @DateFieldId, @MinDate, @MaxDate, @SqlDateRangeWhere output
4195exec Documents_GetDocumentIds_GenerateFilterClause @Filter, @WithSeconds, @WithMilliseconds, @SqlFilterWhere output
4196
4197select
4198 d.Id
4199into #DocIds
4200from @DocIds d
4201
4202--Build where clause
4203if(len(@SqlFilterWhere) > 0)
4204begin
4205 set @SqlWhere += ''WHERE '' + @SqlFilterWhere
4206 if(len(@SqlDateRangeWhere) > 0)
4207 set @SqlWhere += '' AND '' + @SqlDateRangeWhere
4208end
4209else if(len(@SqlDateRangeWhere) > 0)
4210 set @SqlWhere += ''WHERE '' + @SqlDateRangeWhere
4211
4212DECLARE @SortField dbo.OrderedGridItemType
4213SET @TagPivotTempTable = dbo.Documents_GenerateTagPivotTempTable(@Filter, @SortField, ''#DocIds'', DEFAULT, DEFAULT)
4214
4215SELECT
4216 TagGroupID
4217 ,TagGroupName
4218 ,TagId
4219 ,TagName
4220 ,IsLeafTag
4221 ,TagPath
4222INTO #vTagHierarchy
4223FROM dbo.vTagHierarchy
4224
4225set @Sql = ''
4226<TagTempTable>
4227
4228SELECT
4229 df.DocId
4230 FROM dbo.vDocumentFields '' + @JoinOnAlias + ''
4231 inner join #DocIds di on '' + @JoinOnAlias + ''.DocId = di.Id
4232 <TagPivotJoin>
4233 <SqlWhere>
4234'';
4235
4236set @sql = REPLACE(@sql,''<TagTempTable>'',@TagPivotTempTable);
4237if LEN(@TagPivotTempTable) > 1
4238BEGIN
4239 set @sql = REPLACE(@sql,''<TagPivotJoin>'',''INNER JOIN #TagDocs dt on dt.docid = di.docid'');
4240END
4241ELSE
4242BEGIN
4243 set @sql = REPLACE(@sql,''<TagPivotJoin>'','''');
4244END
4245set @sql = REPLACE(@sql,''<SqlWhere>'',@SqlWhere);
4246
4247--print (@sql)
4248execute sp_executesql @sql, N''@Filter dbo.ColumnFilterType readonly'', @Filter = @Filter'
4249--GO
4250EXEC sp_executesql @contextstring, N'@SQL nvarchar(max)', @SQL=@SQL
4251
4252Set @SQL = '
4253--IF OBJECT_ID(''dbo.Documents_GetDocumentViewByDocIds'') IS NULL
4254-- EXEC (''CREATE PROCEDURE dbo.Documents_GetDocumentViewByDocIds AS RETURN 0;'');
4255--GO
4256ALTER PROCEDURE [dbo].[Documents_GetDocumentViewByDocIds]
4257 @UserName nvarchar(256)
4258, @Doc_SortByDocId IdSortedTableType readonly
4259, @SortField dbo.OrderedGridItemType readonly
4260, @GridSelections TripleIntegerTableType readonly
4261AS
4262BEGIN
4263
4264set nocount on
4265declare @starttime datetime = getdate()
4266declare @UserKey int
4267select @UserKey = ActivityTracking.GetUserKeyForUsername(@UserName)
4268
4269--Copy temp tables for dynamic SQL below
4270select
4271 DocId = Docs.Id,
4272 Docs.SortNumber
4273into #DocIds
4274from @Doc_SortByDocId Docs
4275order by docs.SortNumber
4276
4277
4278
4279select
4280 ItemId = Integer1,
4281 ItemType = Integer2,
4282 ReviewPassId = Integer3
4283into #GridSelections
4284from @GridSelections
4285
4286
4287
4288/************ FIELDS ************/
4289create table #Fields (FieldId int, SystemFieldType int, TableName nvarchar(500), ColumnName nvarchar(500))
4290insert into #Fields (FieldId, SystemFieldType, TableName, ColumnName)
4291select
4292 f.FieldId,
4293 f.SystemFieldType,
4294 f.TableName,
4295 f.ColumnName
4296from vFieldDefinition f
4297inner join #GridSelections g on g.ItemId = f.FieldId and g.ItemType = 0 --field
4298
4299-- Add NativeFileType field in case it is not present in GridSelections
4300-- This is used to join up and get the file icon type for the document
4301declare @NativeTypeTable varchar(500),
4302 @NativeTypeColumn varchar(500)
4303if not exists(select * from #Fields where SystemFieldType = 23) --NativeFileType
4304 insert into #Fields (FieldId, SystemFieldType, TableName, ColumnName)
4305 select
4306 f.FieldId,
4307 f.SystemFieldType,
4308 f.TableName,
4309 f.ColumnName
4310 from vFieldDefinition f
4311 where f.SystemFieldType = 23
4312
4313select
4314 @NativeTypeTable = f.TableName,
4315 @NativeTypeColumn = f.ColumnName
4316 from vFieldDefinition f
4317 where f.SystemFieldType = 23
4318
4319--Compute the DocumentFields select SQL
4320declare
4321 @DocumentFieldsJoinSql varchar(max) = '''',
4322 @DocumentFieldsSelectSql varchar(max) = '''',
4323 @TableName varchar(500) = '''',
4324 @ColumnName varchar(500) = ''''
4325
4326declare DocFieldsSelectCursor cursor for
4327select
4328 TableName,
4329 ColumnName
4330from #Fields
4331
4332open DocFieldsSelectCursor
4333
4334fetch next from DocFieldsSelectCursor into
4335 @TableName,
4336 @ColumnName
4337
4338while @@FETCH_STATUS = 0
4339begin
4340 set @DocumentFieldsSelectSql = @DocumentFieldsSelectSql + '' ,'' + @TableName + ''.'' + @ColumnName + ''
4341''
4342
4343fetch next from DocFieldsSelectCursor into
4344 @TableName,
4345 @ColumnName
4346end
4347
4348close DocFieldsSelectCursor
4349deallocate DocFieldsSelectCursor
4350
4351--Compute the DocumentFields join SQL
4352declare DocFieldsJoinCursor cursor for
4353select distinct
4354 TableName
4355from #Fields
4356
4357open DocFieldsJoinCursor
4358
4359fetch next from DocFieldsJoinCursor into
4360 @TableName
4361
4362while @@FETCH_STATUS = 0
4363begin
4364 set @DocumentFieldsJoinSql = @DocumentFieldsJoinSql + ''
4365inner join '' + @TableName + '' on '' + @TableName + ''.DocId = docs.DocId''
4366
4367fetch next from DocFieldsJoinCursor into
4368 @TableName
4369end
4370
4371close DocFieldsJoinCursor
4372deallocate DocFieldsJoinCursor
4373/************ END OF FIELDS ************/
4374
4375/************ TAGS ************/
4376SELECT
4377 TagGroupID
4378 ,TagGroupName
4379 ,TagId
4380 ,TagName
4381 ,IsLeafTag
4382 ,TagPath
4383INTO #vTagHierarchy
4384FROM dbo.vTagHierarchy
4385
4386
4387declare @TagGroupCols varchar(MAX) = '''',
4388 @query varchar(MAX) = '''',
4389 @TagGroupPrefixCols varchar(max) = ''''
4390
4391set @TagGroupCols = stuff((select '',[TG - '' + th.TagGroupName + '']''
4392 from #vTagHierarchy th
4393 inner join #GridSelections gs on th.TagGroupId = gs.ItemId and gs.ItemType = 1 --tag group
4394 group by th.TagGroupName
4395 order by th.TagGroupName
4396 for xml path(''''), TYPE
4397 ).value(''.'', ''nvarchar(max)'')
4398 ,1,1,'''')
4399
4400set @TagGroupPrefixCols = STUFF((SELECT '',TagPivot.'' + t.String + ''
4401''
4402 from dbo.DelimitedStringToTable(@TagGroupCols, '','') t
4403 for xml path(''''), TYPE
4404 ).value(''.'', ''nvarchar(max)'')
4405,1,1,'''')
4406/************ END OF TAGS ************/
4407
4408declare @HasTagSelections bit
4409set @HasTagSelections = case when @TagGroupCols is null or @TagGroupPrefixCols is null then 0 else 1 end
4410declare @Sql nvarchar(max) = ''''
4411
4412select @Sql = ''
4413
4414select
4415 docid,
4416 OtherUsersRedactionsExist = cast(max(case when CreatedByKey <> '' + cast(@UserKey as varchar(20)) + '' OR ModifiedByKey <> '' + cast(@UserKey as varchar(20)) + '' then 1 else 0 end) as bit)
4417into #HasRedactions
4418FROM dbo.DocumentAnnotations
4419where RedactionCatId IS NOT NULL
4420group by docid
4421
4422
4423''+ case when @HasTagSelections = 1 then ''
4424select DocId, '' + @TagGroupCols + N''
4425into #tagpivot
4426 from
4427 (
4428 select
4429 d1.DocId,
4430 TagGroupName = ''''TG - '''' + th1.TagGroupName,
4431 stuff(
4432 (
4433 select ''''; '''' + th2.TagName
4434 from #vTagHierarchy th2
4435 left join dbo.DocumentTags dt2 on th2.TagId = dt2.TagId
4436 where dt2.DocId = d1.DocId and th1.TagGroupID = th2.TagGroupID and th2.IsLeafTag = 1
4437 order by th2.TagPath
4438 for xml path(''''''''), TYPE
4439 ).value(''''.'''', ''''varchar(max)''''), 1, 2, ''''''''
4440 ) as TagName
4441 from #DocIds d1
4442 left join dbo.DocumentTags dt1 on d1.DocId = dt1.DocId
4443 left join #vTagHierarchy th1 on th1.TagId = dt1.TagId
4444 inner join #GridSelections gs on th1.TagGroupId = gs.ItemId and gs.ItemType = 1 --tag group
4445 where th1.IsLeafTag = 1 or th1.IsLeafTag is null
4446 group by th1.TagGroupID,th1.TagGroupName, d1.DocId
4447 ) x
4448 pivot
4449 (
4450 max(TagName)
4451 for TagGroupName in ('' + @TagGroupCols + N'')
4452 ) p
4453
4454'' else '''' end + ''
4455
4456SELECT
4457 docs.DocId
4458 ,BegDoc = d.DocumentKey
4459 ,HasRedactions = cast(case when HasRedactions.docid is null then 0 else 1 end as bit)
4460 ,HasOtherUserRedactions = cast(isnull(HasRedactions.OtherUsersRedactionsExist,0) as bit)
4461 ,fc.FileCategoryId
4462 ,fi.FileIconId
4463'' + @DocumentFieldsSelectSql
4464+ case when @HasTagSelections = 1 then '','' + @TagGroupPrefixCols else '''' end
4465+ ''FROM #DocIds docs
4466inner join dbo.Documents d on docs.DocId = d.DocId'' + @DocumentFieldsJoinSql
4467+ case when @HasTagSelections = 1 then ''
4468left join #TagPivot TagPivot on docs.DocId = TagPivot.DocId '' else '''' end + ''
4469LEFT JOIN dbo.FileTypes ft on ft.FileTypeId = '' + @NativeTypeTable + ''.'' + @NativeTypeColumn + ''
4470LEFT JOIN dbo.FileCategories fc on fc.FileCategoryId = ft.FileCategoryId
4471LEFT JOIN FileTypes.FileIcon fi on fi.FileIconId = fc.FileIconId
4472LEFT JOIN #HasRedactions HasRedactions on HasRedactions.DocId = docs.DocId
4473order by docs.SortNumber
4474 ''
4475
4476 execute sp_executesql @Sql
4477
4478END'
4479EXEC sp_executesql @contextstring, N'@SQL nvarchar(max)', @SQL=@SQL
4480--GO
4481
4482Set @SQL = '
4483--IF OBJECT_ID(''dbo.Search_GetTallyForTagGroup'') IS NULL
4484-- EXEC (''CREATE PROCEDURE dbo.Search_GetTallyForTagGroup AS RETURN 0;'');
4485--GO
4486ALTER PROCEDURE [dbo].[Search_GetTallyForTagGroup]
4487 @TagGroupId INT,
4488 @DocIdTable dbo.IdSortedTableType READONLY
4489
4490AS
4491BEGIN
4492
4493 SELECT Id into #DocIdTable
4494 FROM @DocIdTable
4495
4496 SELECT
4497 TagGroupID
4498 ,TagGroupName
4499 ,TagId
4500 ,TagName
4501 ,IsLeafTag
4502 ,TagPath
4503 INTO #vTagHierarchy
4504 FROM dbo.vTagHierarchy
4505
4506 select Id, TagGroup
4507 into #TagDocs
4508 from
4509 (
4510 select
4511 d1.Id,
4512 TagGroupName = ''TagGroup'',
4513 stuff(
4514 (
4515 select ''; '' + th2.TagName
4516 from #vTagHierarchy th2
4517 left join dbo.DocumentTags dt2 on th2.TagId = dt2.TagId
4518 where dt2.DocId = d1.Id and th1.TagGroupName = th2.TagGroupName and th2.IsLeafTag = 1
4519 order by th2.TagPath
4520 for xml path(''''), TYPE
4521 ).value(''.'', ''varchar(max)''), 1, 2, ''''
4522 ) as TagName
4523 from #DocIdTable d1
4524 left join dbo.DocumentTags dt1 on d1.Id = dt1.DocId
4525 left join #vTagHierarchy th1 on th1.TagId = dt1.TagId and th1.TagGroupId = @TagGroupId
4526 where (th1.IsLeafTag = 1 or th1.IsLeafTag is null)
4527 group by th1.TagGroupName, d1.Id
4528 ) x
4529 pivot
4530 (
4531 max(TagName)
4532 for TagGroupName in (TagGroup)
4533 ) p
4534
4535 SELECT [Value] = ISNULL(t.TagGroup, ''''), [Count] = COUNT(*)
4536 FROM #TagDocs t
4537 GROUP BY t.TagGroup
4538 ORDER BY 2 DESC, [Value] ASC
4539
4540END'
4541--GO
4542--'
4543
4544 --EXEC (@SQL)
4545 EXEC sp_executesql @contextstring, N'@SQL nvarchar(max)', @SQL=@SQL
4546
4547 -- delete this database so the loop will process the next one
4548 DELETE FROM #tbl_databases
4549 WHERE [DatabaseName] = @database_name
4550
4551END
4552
4553-- clean up
4554DROP TABLE #tbl_databases
4555
4556