· 4 years ago · Jan 06, 2021, 05:42 PM
1IF OBJECT_ID('dbo.Documents_GetPagedDocumentIdsFromBatch') IS NULL
2 EXEC ('CREATE PROCEDURE dbo.Documents_GetPagedDocumentIdsFromBatch AS RETURN 0;');
3GO
4ALTER PROCEDURE [dbo].[Documents_GetPagedDocumentIdsFromBatch]
5 @BatchId int,
6 @PageSize int,
7 @PageNumber int,
8 @FilterReviewed int,
9 @Filter dbo.ColumnFilterType readonly,
10 @SortField dbo.OrderedGridItemType readonly,
11 @WithSeconds bit,
12 @WithMilliseconds bit,
13 @StartDate datetime = null,
14 @EndDate datetime = null,
15 @IncludeNoDate bit = null,
16 @DateFieldId int = null,
17 @MinDate datetime,
18 @MaxDate datetime,
19 @QuickSearchResultsId INT,
20 @UserName NVARCHAR(256)
21AS
22set nocount on
23
24DECLARE @sql as nvarchar(max) = ''
25DECLARE @PagingQueryWhereClause as nvarchar(max) = ''
26DECLARE @SqlSortRowNumber nvarchar(max) = ''
27DECLARE @SqlDateRangeWhere nvarchar(max) = ''
28DECLARE @SqlFilterWhere nvarchar(max) = ''
29DECLARE @SqlBatchJoin nvarchar(max) = ''
30DECLARE @TagPivotTempTable nvarchar(max) = ''
31DECLARE @QuickSearchJoin NVARCHAR(MAX) = ''
32DECLARE @SqlWhere nvarchar(max) = ''
33DECLARE @JoinOnAlias nvarchar(5) = 'df'
34
35select @PagingQueryWhereClause = dbo.PagingQueryWhereClause(@PageNumber, @PageSize)
36
37exec Documents_GetDocumentIds_GenerateDocumentDateClause @StartDate, @EndDate, @IncludeNoDate, @DateFieldId, @MinDate, @MaxDate, @SqlDateRangeWhere output
38exec Documents_GetDocumentIds_GenerateFilterClause @Filter, @WithSeconds, @WithMilliseconds, @SqlFilterWhere output
39
40select @SqlSortRowNumber = dbo.Documents_GenerateSortRowNumberStatementForBatches(@SortField, @BatchId, @FilterReviewed)
41
42if (@BatchId > 0)
43begin
44 set @SqlBatchJoin = 'INNER JOIN Review.BatchDocument bd ON ' + @JoinOnAlias + '.DocId = bd.DocId AND bd.BatchId = ' + Convert(varchar(10), @BatchId)
45 if (@FilterReviewed = 1)
46 set @SqlBatchJoin = @SqlBatchJoin + ' AND bd.ReviewStatusId = 1 '
47 else if (@FilterReviewed = 2)
48 set @SqlBatchJoin = @SqlBatchJoin + ' AND bd.ReviewStatusId <> 1 '
49END
50
51IF (@QuickSearchResultsId > 0)
52BEGIN
53 declare @UserKey INT, @ResultsTableName as nvarchar(256)
54 SELECT @UserKey = ActivityTracking.GetUserKeyForUsername(@UserName)
55 SET @ResultsTableName = UserTables.SearchResultsTableName(@UserKey)
56
57 SET @QuickSearchJoin = 'INNER JOIN ' + @ResultsTableName + ' qsr ON ' + @JoinOnAlias + '.DocId = qsr.DocId AND qsr.ResultsId = ' + Convert(varchar(10), @QuickSearchResultsId)
58END
59
60--Build where clause
61if(len(@SqlFilterWhere) > 0)
62begin
63 set @SqlWhere += 'WHERE ' + @SqlFilterWhere
64 if(len(@SqlDateRangeWhere) > 0)
65 set @SqlWhere += ' AND ' + @SqlDateRangeWhere
66end
67else if(len(@SqlDateRangeWhere) > 0)
68 set @SqlWhere += 'WHERE ' + @SqlDateRangeWhere
69
70IF(@BatchId > 0)
71 SET @TagPivotTempTable = dbo.Documents_GenerateTagPivotTempTable(@Filter, @SortField, 'Review.BatchDocument', DEFAULT, @BatchId)
72ELSE
73 SET @TagPivotTempTable = dbo.Documents_GenerateTagPivotTempTable(@Filter, @SortField, 'dbo.Documents', DEFAULT, DEFAULT)
74
75DECLARE @FieldsToInclude dbo.IdTableType
76INSERT INTO @FieldsToInclude (Id)
77SELECT ItemId
78FROM @Filter
79WHERE ItemType = 0 --GridItemType.Field
80UNION
81SELECT ItemId
82FROM @SortField
83WHERE ItemType = 0 --GridItemType.Field
84UNION
85SELECT @DateFieldId
86WHERE @DateFieldId is not NULL
87
88IF(@BatchId > 0)
89BEGIN
90 INSERT INTO @FieldsToInclude (Id)
91 SELECT sbf.FieldId
92 FROM Review.Batch b
93 INNER JOIN Review.SortByField sbf on sbf.ReviewPassId = b.ReviewPassId
94 WHERE b.BatchId = @BatchId
95END
96SELECT
97 TagGroupID
98 ,TagGroupName
99 ,TagId
100 ,TagName
101 ,IsLeafTag
102 ,TagPath
103INTO #vTagHierarchy
104FROM dbo.vTagHierarchy
105set @Sql = '
106<TagTempTable>
107
108Select
109 DocId
110FROM
111(
112 SELECT
113 RowNum = <SortRowNumber>
114 , df.DocId
115 FROM ' + [dbo].[Documents_GenerateAliasedDocumentFields](@FieldsToInclude, @JoinOnAlias) + '
116 <TagPivotJoin>
117 <QuickSearchJoin>
118 <BatchJoin>
119 <SqlWhere>
120)x
121WHERE RowNum <PagingQueryWhereClause>
122ORDER BY RowNum
123';
124
125set @sql = REPLACE(@sql,'<SortRowNumber>',@SqlSortRowNumber);
126set @sql = REPLACE(@sql,'<TagTempTable>',@TagPivotTempTable);
127if LEN(@TagPivotTempTable) > 1
128BEGIN
129 set @sql = REPLACE(@sql,'<TagPivotJoin>','INNER JOIN #TagDocs dt on dt.docid = df.docid');
130END
131ELSE
132BEGIN
133 set @sql = REPLACE(@sql,'<TagPivotJoin>','');
134END
135SET @sql = REPLACE(@sql,'<QuickSearchJoin>', @QuickSearchJoin);
136set @sql = REPLACE(@sql,'<BatchJoin>', @SqlBatchJoin);
137set @sql = REPLACE(@sql,'<SqlWhere>',@SqlWhere);
138set @sql = REPLACE(@sql,'<PagingQueryWhereClause>',@PagingQueryWhereClause);
139
140--print (@sql)
141execute sp_executesql @sql, N'@SortField dbo.OrderedGridItemType readonly, @Filter dbo.ColumnFilterType readonly', @SortField = @SortField, @Filter = @Filter
142GO
143IF OBJECT_ID('dbo.Documents_GetPagedDocumentIdsFromDocIds') IS NULL
144 EXEC ('CREATE PROCEDURE dbo.Documents_GetPagedDocumentIdsFromDocIds AS RETURN 0;');
145GO
146ALTER PROCEDURE [dbo].[Documents_GetPagedDocumentIdsFromDocIds]
147 @DocIds IdTableType readonly,
148 @PageSize int,
149 @PageNumber int,
150 @Filter dbo.ColumnFilterType readonly,
151 @SortField dbo.OrderedGridItemType readonly,
152 @WithSeconds bit,
153 @WithMilliseconds bit,
154 @StartDate datetime = null,
155 @EndDate datetime = null,
156 @IncludeNoDate bit = null,
157 @DateFieldId int = null,
158 @MinDate datetime,
159 @MaxDate datetime,
160 @QuickSearchResultsId INT,
161 @UserName NVARCHAR(256)
162AS
163 set nocount on
164
165DECLARE @sql as nvarchar(max) = ''
166DECLARE @PagingQueryWhereClause as nvarchar(max) = ''
167DECLARE @SqlSortRowNumber nvarchar(max) = ''
168DECLARE @SqlDateRangeWhere nvarchar(max) = ''
169DECLARE @SqlFilterWhere nvarchar(max) = ''
170DECLARE @TagPivotTempTable nvarchar(max) = ''
171DECLARE @SqlWhere nvarchar(max) = ''
172DECLARE @QuickSearchJoin NVARCHAR(MAX) = ''
173DECLARE @JoinOnAlias nvarchar(5) = 'df'
174
175IF (@QuickSearchResultsId > 0)
176BEGIN
177 declare @UserKey INT, @ResultsTableName as nvarchar(256)
178 SELECT @UserKey = ActivityTracking.GetUserKeyForUsername(@UserName)
179 SET @ResultsTableName = UserTables.SearchResultsTableName(@UserKey)
180
181 SET @QuickSearchJoin = ' INNER JOIN ' + @ResultsTableName + ' qsr ON ' + @JoinOnAlias + '.DocId = qsr.DocId AND qsr.ResultsId = ' + Convert(varchar(10), @QuickSearchResultsId)
182END
183
184select @PagingQueryWhereClause = dbo.PagingQueryWhereClause(@PageNumber, @PageSize)
185
186exec Documents_GetDocumentIds_GenerateDocumentDateClause @StartDate, @EndDate, @IncludeNoDate, @DateFieldId, @MinDate, @MaxDate, @SqlDateRangeWhere output
187exec Documents_GetDocumentIds_GenerateFilterClause @Filter, @WithSeconds, @WithMilliseconds, @SqlFilterWhere output
188
189if not exists(select * from @SortField)
190begin
191 set @SqlSortRowNumber = 'ROW_NUMBER() OVER(ORDER BY ' + @JoinOnAlias + '.DocId ASC)'-- +case when @SortAscending = 0 then 'DESC' else 'ASC' end +')'
192end
193else
194begin
195 select @SqlSortRowNumber = dbo.Documents_GenerateSortRowNumberStatement(@SortField)
196end
197
198select
199 d.Id as DocId
200into #DocIds
201from @DocIds d
202
203--Build where clause
204if(len(@SqlFilterWhere) > 0)
205begin
206 set @SqlWhere += 'WHERE ' + @SqlFilterWhere
207 if(len(@SqlDateRangeWhere) > 0)
208 set @SqlWhere += ' AND ' + @SqlDateRangeWhere
209end
210else if(len(@SqlDateRangeWhere) > 0)
211 set @SqlWhere += 'WHERE ' + @SqlDateRangeWhere
212
213SET @TagPivotTempTable = dbo.Documents_GenerateTagPivotTempTable(@Filter, @SortField, '#DocIds', DEFAULT, DEFAULT)
214
215DECLARE @FieldsToInclude dbo.IdTableType
216INSERT INTO @FieldsToInclude (Id)
217SELECT ItemId
218FROM @Filter
219WHERE ItemType = 0 --GridItemType.Field
220UNION
221SELECT ItemId
222FROM @SortField
223WHERE ItemType = 0 --GridItemType.Field
224UNION
225SELECT @DateFieldId
226WHERE @DateFieldId is not NULL
227
228SELECT
229 TagGroupID
230 ,TagGroupName
231 ,TagId
232 ,TagName
233 ,IsLeafTag
234 ,TagPath
235INTO #vTagHierarchy
236FROM dbo.vTagHierarchy
237
238
239set @Sql = '
240<TagTempTable>
241
242Select
243 DocId
244FROM
245(
246 SELECT
247 RowNum = <SortRowNumber>
248 , df.DocId
249 FROM ' + [dbo].[Documents_GenerateAliasedDocumentFields](@FieldsToInclude, @JoinOnAlias) + '
250 inner join #DocIds di on ' + @JoinOnAlias + '.DocId = di.DocId
251 <TagPivotJoin>
252 <QuickSearchJoin>
253 <SqlWhere>
254)x
255WHERE RowNum <PagingQueryWhereClause>
256ORDER BY RowNum
257';
258
259set @sql = REPLACE(@sql,'<SortRowNumber>',@SqlSortRowNumber);
260set @sql = REPLACE(@sql,'<TagTempTable>',@TagPivotTempTable);
261if LEN(@TagPivotTempTable) > 1
262BEGIN
263 set @sql = REPLACE(@sql,'<TagPivotJoin>','INNER JOIN #TagDocs dt on dt.docid = di.docid');
264END
265ELSE
266BEGIN
267 set @sql = REPLACE(@sql,'<TagPivotJoin>','');
268END
269SET @sql = REPLACE(@sql,'<QuickSearchJoin>',@QuickSearchJoin);
270set @sql = REPLACE(@sql,'<SqlWhere>',@SqlWhere);
271set @sql = REPLACE(@sql,'<PagingQueryWhereClause>',@PagingQueryWhereClause);
272
273--print (@sql)
274
275execute sp_executesql @statement = @sql,
276 @params = N'@Filter dbo.ColumnFilterType readonly, @SortField dbo.OrderedGridItemType readonly',
277 @Filter = @Filter,
278 @SortField = @SortField
279GO
280
281IF OBJECT_ID('dbo.Documents_GetPagedDocumentIdsFromRelationship') IS NULL
282 EXEC ('CREATE PROCEDURE dbo.Documents_GetPagedDocumentIdsFromRelationship AS RETURN 0;');
283GO
284ALTER PROCEDURE [dbo].[Documents_GetPagedDocumentIdsFromRelationship]
285 @ResultsId int,
286 @UserName nvarchar(256),
287 @PageSize int,
288 @PageNumber int,
289 @Filter dbo.ColumnFilterType readonly,
290 @SortField dbo.OrderedGridItemType readonly,
291 @WithSeconds bit,
292 @WithMilliseconds bit,
293 @StartDate datetime = null,
294 @EndDate datetime = null,
295 @IncludeNoDate bit = null,
296 @DateFieldId int = null,
297 @MinDate datetime,
298 @MaxDate datetime
299AS
300
301set nocount on
302
303declare @UserKey int
304select @UserKey = ActivityTracking.GetUserKeyForUsername(@UserName)
305
306DECLARE @ResultsTableName as nvarchar(256)
307 ,@sql as nvarchar(max) = ''
308 ,@PagingQueryWhereClause as nvarchar(max) = ''
309 ,@SqlSortRowNumber nvarchar(max) = ''
310 ,@SqlDateRangeWhere nvarchar(max) = ''
311 ,@SqlFilterWhere nvarchar(max) = ''
312 ,@TagPivotTempTable nvarchar(max) = ''
313 ,@SqlWhere nvarchar(max) = ''
314 ,@JoinOnAlias nvarchar(5) = 'df'
315
316EXEC @ResultsTableName = [UserTables].[RelationshipResultsTableName] @UserKey = @UserKey
317
318select @PagingQueryWhereClause = dbo.PagingQueryWhereClause(@PageNumber, @PageSize)
319
320exec Documents_GetDocumentIds_GenerateDocumentDateClause @StartDate, @EndDate, @IncludeNoDate, @DateFieldId, @MinDate, @MaxDate, @SqlDateRangeWhere output
321exec Documents_GetDocumentIds_GenerateFilterClause @Filter, @WithSeconds, @WithMilliseconds, @SqlFilterWhere output
322
323if not exists(select * from @SortField)
324begin
325 set @SqlSortRowNumber = 'ROW_NUMBER() OVER(ORDER BY ' + @JoinOnAlias + '.DocId ASC)'-- +case when @SortAscending = 0 then 'DESC' else 'ASC' end +')'
326end
327else
328begin
329 select @SqlSortRowNumber = dbo.Documents_GenerateSortRowNumberStatement(@SortField)
330end
331
332set @SqlWhere += 'WHERE r.ResultsId = <ResultsId>'
333
334--Build where clause
335if(len(@SqlFilterWhere) > 0)
336begin
337 set @SqlWhere += ' AND ' + @SqlFilterWhere
338 if(len(@SqlDateRangeWhere) > 0)
339 set @SqlWhere += ' AND ' + @SqlDateRangeWhere
340end
341else if(len(@SqlDateRangeWhere) > 0)
342 set @SqlWhere += ' AND ' + @SqlDateRangeWhere
343
344SET @TagPivotTempTable = dbo.Documents_GenerateTagPivotTempTable(@Filter, @SortField, @ResultsTableName, @ResultsId, DEFAULT)
345
346DECLARE @FieldsToInclude dbo.IdTableType
347INSERT INTO @FieldsToInclude (Id)
348SELECT ItemId
349FROM @Filter
350WHERE ItemType = 0 --GridItemType.Field
351UNION
352SELECT ItemId
353FROM @SortField
354WHERE ItemType = 0 --GridItemType.Field
355UNION
356SELECT @DateFieldId
357WHERE @DateFieldId is not NULL
358SELECT
359 TagGroupID
360 ,TagGroupName
361 ,TagId
362 ,TagName
363 ,IsLeafTag
364 ,TagPath
365INTO #vTagHierarchy
366FROM dbo.vTagHierarchy
367set @sql = '
368<TagTempTable>
369
370Select
371 DocId
372FROM
373(
374 SELECT
375 RowNum = <SortRowNumber>
376 , ' + @JoinOnAlias + '.DocId
377 FROM ' + [dbo].[Documents_GenerateAliasedDocumentFields](@FieldsToInclude, @JoinOnAlias) + '
378 INNER JOIN <ResultsTableName> r on ' + @JoinOnAlias + '.DocId = r.DocId
379 <TagPivotJoin>
380 <SqlWhere>
381 ) x
382WHERE RowNum <PagingQueryWhereClause>
383ORDER BY RowNum ';
384
385set @sql = REPLACE(@sql,'<SortRowNumber>',@SqlSortRowNumber);
386set @sql = REPLACE(@sql,'<ResultsTableName>',@ResultsTableName);
387set @sql = REPLACE(@sql,'<TagTempTable>',@TagPivotTempTable);
388if LEN(@TagPivotTempTable) > 1
389BEGIN
390 set @sql = REPLACE(@sql,'<TagPivotJoin>','INNER JOIN #TagDocs dt on dt.docid = r.docid');
391END
392ELSE
393BEGIN
394 set @sql = REPLACE(@sql,'<TagPivotJoin>','');
395END
396set @sql = REPLACE(@sql,'<SqlWhere>',@SqlWhere);
397set @sql = REPLACE(@sql,'<ResultsId>',Convert(varchar(10), @ResultsId));
398set @sql = REPLACE(@sql,'<PagingQueryWhereClause>',@PagingQueryWhereClause);
399
400--print (@sql)
401execute sp_executesql @statement = @sql,
402 @params = N'@Filter dbo.ColumnFilterType readonly, @SortField dbo.OrderedGridItemType readonly',
403 @Filter = @Filter,
404 @SortField = @SortField
405GO
406
407IF OBJECT_ID('dbo.Documents_GetPagedDocumentIdsFromSearch') IS NULL
408 EXEC ('CREATE PROCEDURE dbo.Documents_GetPagedDocumentIdsFromSearch AS RETURN 0;');
409GO
410ALTER PROCEDURE [dbo].[Documents_GetPagedDocumentIdsFromSearch]
411 @ResultsId int
412 ,@UserName nvarchar(256)
413 ,@PageSize int
414 ,@PageNumber int
415 ,@Filter dbo.ColumnFilterType readonly
416 ,@SortField dbo.OrderedGridItemType readonly
417 ,@WithSeconds bit
418 ,@WithMilliseconds bit
419 ,@StartDate datetime = null
420 ,@EndDate datetime = null
421 ,@IncludeNoDate bit = null
422 ,@DateFieldId int = null
423 ,@MinDate datetime
424 ,@MaxDate DATETIME
425 ,@QuickSearchResultsId INT
426AS
427
428set nocount on
429declare @UserKey int
430select @UserKey = ActivityTracking.GetUserKeyForUsername(@UserName)
431
432DECLARE @ResultsTableName as nvarchar(256)
433 ,@sql as nvarchar(max) = ''
434 ,@PagingQueryWhereClause as nvarchar(max) = ''
435 ,@SqlSortRowNumber nvarchar(max) = ''
436 ,@SqlScoreWhere nvarchar(max) = ''
437 ,@SqlFilterWhere nvarchar(max) = ''
438 ,@SqlDateRangeWhere nvarchar(max) = ''
439 ,@SqlScoreJoin nvarchar(max) = ''
440 ,@SqlWhere nvarchar(max) = ''
441 ,@TagPivotTempTable nvarchar(max) = ''
442 ,@TagPivotJoin nvarchar(max) = ''
443 ,@QuickSearchJoin NVARCHAR(MAX) = ''
444 ,@JoinOnAlias nvarchar(5) = 'df'
445
446SET @ResultsTableName = UserTables.SearchResultsTableName(@UserKey)
447select @PagingQueryWhereClause = dbo.PagingQueryWhereClause(@PageNumber, @PageSize)
448
449IF (@QuickSearchResultsId > 0)
450BEGIN
451 SET @QuickSearchJoin = 'INNER JOIN ' + @ResultsTableName + ' qsr ON ' + @JoinOnAlias + '.DocId = qsr.DocId AND qsr.ResultsId = ' + Convert(varchar(10), @QuickSearchResultsId)
452END
453
454exec Documents_GetDocumentIds_GenerateDocumentDateClause @StartDate, @EndDate, @IncludeNoDate, @DateFieldId, @MinDate, @MaxDate, @SqlDateRangeWhere output
455exec Documents_GetDocumentIds_GenerateFilterClause @Filter, @WithSeconds, @WithMilliseconds, @SqlFilterWhere output
456
457if not exists(select * from @SortField)
458begin
459 set @SqlSortRowNumber = 'ROW_NUMBER() OVER(ORDER BY ' + @JoinOnAlias + '.DocId ASC)'-- +case when @SortAscending = 0 then 'DESC' else 'ASC' end +')'
460end
461else
462begin
463 select @SqlSortRowNumber = dbo.Documents_GenerateSortRowNumberStatement(@SortField)
464end
465
466if exists (select * from @Filter where ItemType = 3) -- SimilarityScore (3)
467begin
468 declare @ScoreValue varchar(50);
469 select @ScoreValue =
470 case when left(Value,1) = '.' then '0' + Value
471 else Value
472 end
473 from @Filter
474 where ItemType = 3;
475
476 set @SqlScoreWhere += '( left(cast(Score.Score as varchar(50)), len(''' + @ScoreValue + ''')) = ''' + @ScoreValue + ''' ) ';
477end
478
479if exists(select * from @SortField sf where sf.ItemType = 3) --GridItemType.SimilarityScore
480or exists (select * from @Filter where ItemType = 3)
481 SET @SqlScoreJoin = 'inner join ' + UserTables.SearchResultsScoresTableName(@UserKey) + ' Score on score.docid = r.DocId and score.ResultsId = r.ResultsId';
482
483
484set @SqlWhere += 'WHERE r.ResultsId = <ResultsId>'
485
486--Build where clause
487if(len(@SqlFilterWhere) > 0)
488 set @SqlWhere += ' AND (' + @SqlFilterWhere + ')
489'
490
491if(len(@SqlDateRangeWhere) > 0)
492 set @SqlWhere += ' AND ' + @SqlDateRangeWhere + '
493'
494
495if(len(@SqlScoreWhere) > 0)
496 set @SqlWhere += ' AND ' + @SqlScoreWhere + '
497'
498
499SET @TagPivotTempTable = dbo.[Documents_GenerateTagPivotTempTable](@Filter, @SortField, @ResultsTableName, @ResultsId, DEFAULT)
500
501DECLARE @FieldsToInclude dbo.IdTableType
502INSERT INTO @FieldsToInclude (Id)
503SELECT ItemId
504FROM @Filter
505WHERE ItemType = 0 --GridItemType.Field
506UNION
507SELECT ItemId
508FROM @SortField
509WHERE ItemType = 0 --GridItemType.Field
510UNION
511SELECT @DateFieldId
512WHERE @DateFieldId is not NULL
513SELECT
514 TagGroupID
515 ,TagGroupName
516 ,TagId
517 ,TagName
518 ,IsLeafTag
519 ,TagPath
520INTO #vTagHierarchy
521FROM dbo.vTagHierarchy
522set @sql = '
523<TagTempTable>
524
525Select
526 DocId
527FROM
528(
529 SELECT
530 RowNum = <SortRowNumber>
531 , ' + @JoinOnAlias + '.DocId
532 FROM ' + [dbo].[Documents_GenerateAliasedDocumentFields](@FieldsToInclude, @JoinOnAlias) + '
533 INNER JOIN <ResultsTableName> r on ' + @JoinOnAlias + '.DocId = r.DocId
534 <TagPivotJoin>
535 <QuickSearchJoin>
536 <ScoreJoin>
537 <SqlWhere>
538 ) x
539WHERE RowNum <PagingQueryWhereClause>
540ORDER BY RowNum ';
541
542set @sql = REPLACE(@sql,'<SortRowNumber>',@SqlSortRowNumber);
543set @sql = REPLACE(@sql,'<ResultsTableName>',@ResultsTableName);
544
545set @sql = REPLACE(@sql,'<TagTempTable>',@TagPivotTempTable);
546if LEN(@TagPivotTempTable) > 1
547BEGIN
548 set @sql = REPLACE(@sql,'<TagPivotJoin>','INNER JOIN #TagDocs dt on dt.docid = r.docid');
549END
550ELSE
551BEGIN
552 set @sql = REPLACE(@sql,'<TagPivotJoin>','');
553END
554
555SET @sql = REPLACE(@sql,'<QuickSearchJoin>', @QuickSearchJoin);
556set @sql = REPLACE(@sql,'<ScoreJoin>', @SqlScoreJoin);
557set @sql = REPLACE(@sql,'<SqlWhere>',@SqlWhere);
558set @sql = REPLACE(@sql,'<ResultsId>',Convert(varchar(10), @ResultsId));
559set @sql = REPLACE(@sql,'<PagingQueryWhereClause>',@PagingQueryWhereClause);
560
561--print @sql
562execute sp_executesql @statement = @sql,
563 @params = N'@Filter dbo.ColumnFilterType readonly, @SortField dbo.OrderedGridItemType readonly',
564 @Filter = @Filter,
565 @SortField = @SortField
566GO
567
568IF OBJECT_ID('dbo.Documents_GetRelationshipCount') IS NULL
569 EXEC ('CREATE PROCEDURE dbo.Documents_GetRelationshipCount AS RETURN 0;');
570GO
571ALTER PROCEDURE [dbo].[Documents_GetRelationshipCount]
572 @ResultsId int
573, @UserName nvarchar(256)
574, @Filter dbo.ColumnFilterType readonly
575, @WithSeconds bit = 0
576, @WithMilliseconds bit = 0
577, @StartDate datetime = null
578, @EndDate datetime = null
579, @IncludeNoDate bit = null
580, @DateFieldId int = null
581, @MinDate datetime
582, @MaxDate datetime
583AS
584set nocount on
585
586declare @UserKey int
587select @UserKey = ActivityTracking.GetUserKeyForUsername(@UserName)
588
589DECLARE @ResultsTableName as nvarchar(256)
590select @ResultsTableName = [UserTables].[RelationshipResultsTableName](@UserKey)
591
592DECLARE @sql as nvarchar(max) = ''
593DECLARE @SqlFilterWhere nvarchar(max) = ''
594DECLARE @SqlDateRangeWhere nvarchar(max) = ''
595DECLARE @TagPivotTempTable nvarchar(max) = ''
596DECLARE @SqlWhere nvarchar(max) = ''
597
598exec Documents_GetDocumentIds_GenerateDocumentDateClause @StartDate, @EndDate, @IncludeNoDate, @DateFieldId, @MinDate, @MaxDate, @SqlDateRangeWhere output
599exec Documents_GetDocumentIds_GenerateFilterClause @Filter, @WithSeconds, @WithMilliseconds, @SqlFilterWhere output
600
601--Build where clause
602set @SqlWhere += 'WHERE r.ResultsId = <ResultsId>'
603if(len(@SqlFilterWhere) > 0)
604begin
605 set @SqlWhere += ' AND ' + @SqlFilterWhere
606 if(len(@SqlDateRangeWhere) > 0)
607 set @SqlWhere += ' AND ' + @SqlDateRangeWhere
608end
609else if(len(@SqlDateRangeWhere) > 0)
610 set @SqlWhere += ' AND' + @SqlDateRangeWhere
611
612DECLARE @SortField dbo.OrderedGridItemType
613SET @TagPivotTempTable = dbo.Documents_GenerateTagPivotTempTable(@Filter, @SortField, @ResultsTableName, @ResultsId, DEFAULT)
614
615DECLARE @FieldsToInclude dbo.IdTableType
616INSERT INTO @FieldsToInclude (Id)
617SELECT ItemId
618FROM @Filter
619WHERE ItemType = 0 --GridItemType.Field
620UNION
621SELECT @DateFieldId
622WHERE @DateFieldId is not NULL
623SELECT
624 TagGroupID
625 ,TagGroupName
626 ,TagId
627 ,TagName
628 ,IsLeafTag
629 ,TagPath
630INTO #vTagHierarchy
631FROM dbo.vTagHierarchy
632set @Sql = '
633<TagTempTable>
634
635SELECT
636 count(df.DocId)
637FROM ' + [dbo].[Documents_GenerateAliasedDocumentFields](@FieldsToInclude, 'df');
638
639set @Sql = @SQL + '
640INNER JOIN <ResultsTable> r on df.DocId = r.DocId
641<TagPivotJoin>
642<SqlWhere>
643';
644
645set @sql = REPLACE(@sql,'<ResultsTable>', @ResultsTableName);
646set @sql = REPLACE(@sql,'<TagTempTable>',@TagPivotTempTable);
647if LEN(@TagPivotTempTable) > 1
648BEGIN
649 set @sql = REPLACE(@sql,'<TagPivotJoin>','INNER JOIN #TagDocs dt on dt.docid = df.docid');
650END
651ELSE
652BEGIN
653 set @sql = REPLACE(@sql,'<TagPivotJoin>','');
654END
655set @sql = REPLACE(@sql,'<SqlWhere>',@SqlWhere);
656set @sql = REPLACE(@sql,'<ResultsId>',isnull(cast(@ResultsId as nvarchar(18)),'-1'))
657
658execute sp_executesql @sql, N'@Filter dbo.ColumnFilterType readonly', @Filter = @Filter
659GO
660
661IF OBJECT_ID('dbo.Documents_GetSearchCount') IS NULL
662 EXEC ('CREATE PROCEDURE dbo.Documents_GetSearchCount AS RETURN 0;');
663GO
664ALTER PROCEDURE [dbo].[Documents_GetSearchCount]
665 @ResultsId int
666, @UserName nvarchar(256)
667, @Filter dbo.ColumnFilterType readonly
668, @WithSeconds bit = 0
669, @WithMilliseconds bit = 0
670, @StartDate datetime = null
671, @EndDate datetime = null
672, @IncludeNoDate bit = null
673, @DateFieldId int = null
674, @MinDate datetime
675, @MaxDate DATETIME
676, @QuickSearchResultsId INT
677AS
678set nocount on
679
680declare @UserKey int
681select @UserKey = ActivityTracking.GetUserKeyForUsername(@UserName)
682
683DECLARE @ResultsTableName as nvarchar(256)
684select @ResultsTableName = UserTables.SearchResultsTableName(@UserKey)
685
686DECLARE @sql as nvarchar(max) = ''
687DECLARE @SqlFilterWhere nvarchar(max) = ''
688DECLARE @SqlScoreJoin nvarchar(max) = ''
689DECLARE @SqlDateRangeWhere nvarchar(max) = ''
690DECLARE @SqlScoreWhere nvarchar(max) = ''
691DECLARE @TagPivotTempTable nvarchar(max) = ''
692DECLARE @TagPivotJoin nvarchar(max) = ''
693DECLARE @QuickSearchJoin NVARCHAR(MAX) = ''
694DECLARE @SqlWhere nvarchar(max) = ''
695
696exec Documents_GetDocumentIds_GenerateDocumentDateClause @StartDate, @EndDate, @IncludeNoDate, @DateFieldId, @MinDate, @MaxDate, @SqlDateRangeWhere output
697exec Documents_GetDocumentIds_GenerateFilterClause @Filter, @WithSeconds, @WithMilliseconds, @SqlFilterWhere OUTPUT
698
699IF (@QuickSearchResultsId > 0)
700BEGIN
701 SET @QuickSearchJoin = 'INNER JOIN ' + @ResultsTableName + ' qsr ON df.DocId = qsr.DocId AND qsr.ResultsId = ' + Convert(varchar(10), @QuickSearchResultsId)
702END
703
704if exists (select * from @Filter where ItemType = 3) -- SimilarityScore (3)
705begin
706 declare @ScoreValue varchar(50);
707 select @ScoreValue =
708 case when left(Value,1) = '.' then '0' + Value
709 else Value
710 end
711 from @Filter
712 where ItemType = 3;
713
714 set @SqlScoreWhere = '( left(cast(Score.Score as varchar(50)), len(''' + @ScoreValue + ''')) = ''' + @ScoreValue + ''' )';
715 set @SqlScoreJoin = 'inner join ' + UserTables.SearchResultsScoresTableName(@UserKey) + ' Score on score.docid = r.DocId and score.ResultsId = r.ResultsId';
716end
717
718set @SqlWhere += 'WHERE r.ResultsId = <ResultsId>'
719
720--Build where clause
721if(len(@SqlFilterWhere) > 0)
722 set @SqlWhere += ' AND (' + @SqlFilterWhere + ')
723'
724
725if(len(@SqlDateRangeWhere) > 0)
726 set @SqlWhere += ' AND ' + @SqlDateRangeWhere + '
727'
728
729if(len(@SqlScoreWhere) > 0)
730 set @SqlWhere += ' AND ' + @SqlScoreWhere + '
731'
732
733DECLARE @SortField dbo.OrderedGridItemType
734SET @TagPivotTempTable = dbo.[Documents_GenerateTagPivotTempTable](@Filter, @SortField, @ResultsTableName, @ResultsId, DEFAULT)
735
736DECLARE @FieldsToInclude dbo.IdTableType
737INSERT INTO @FieldsToInclude (Id)
738SELECT ItemId
739FROM @Filter
740WHERE ItemType = 0 --GridItemType.Field
741UNION
742SELECT @DateFieldId
743WHERE @DateFieldId is not NULL
744SELECT
745 TagGroupID
746 ,TagGroupName
747 ,TagId
748 ,TagName
749 ,IsLeafTag
750 ,TagPath
751INTO #vTagHierarchy
752FROM dbo.vTagHierarchy
753set @Sql = '
754<TagTempTable>
755
756SELECT
757 count(df.DocId)
758FROM ' + [dbo].[Documents_GenerateAliasedDocumentFields](@FieldsToInclude, 'df');
759
760set @Sql = @SQL + '
761INNER JOIN <ResultsTable> r on df.DocId = r.DocId
762<TagPivotJoin>
763<QuickSearchJoin>
764<ScoreJoin>
765<SqlWhere>
766';
767
768set @sql = REPLACE(@sql,'<ResultsTable>', @ResultsTableName);
769set @sql = REPLACE(@sql,'<TagTempTable>',@TagPivotTempTable);
770if LEN(@TagPivotTempTable) > 1
771BEGIN
772 set @sql = REPLACE(@sql,'<TagPivotJoin>','INNER JOIN #TagDocs dt on dt.docid = r.docid');
773END
774ELSE
775BEGIN
776 set @sql = REPLACE(@sql,'<TagPivotJoin>','');
777END
778set @sql = REPLACE(@sql,'<QuickSearchJoin>', @QuickSearchJoin);
779set @sql = REPLACE(@sql,'<ScoreJoin>', @SqlScoreJoin);
780set @sql = REPLACE(@sql,'<SqlWhere>',@SqlWhere);
781set @sql = REPLACE(@sql,'<ResultsId>',isnull(cast(@ResultsId as nvarchar(18)),'-1'))
782
783--print @sql
784execute sp_executesql @sql, N'@Filter dbo.ColumnFilterType readonly', @Filter = @Filter
785GO
786
787IF OBJECT_ID('dbo.Documents_GenerateTagPivotTempTable') IS NULL
788 EXEC ('CREATE PROCEDURE dbo.Documents_GenerateTagPivotTempTable AS RETURN 0;');
789GO
790ALTER FUNCTION [dbo].[Documents_GenerateTagPivotTempTable]
791(
792 @Filter dbo.ColumnFilterType readonly,
793 @SortField dbo.OrderedGridItemType readonly,
794 @DocIdSourceTableName nvarchar(256),
795 @ResultsId int = NULL,
796 @BatchId int = NULL
797)
798RETURNS nvarchar(max)
799AS
800BEGIN
801 DECLARE @TagGroupCols nvarchar(max)
802
803 SET @TagGroupCols =
804 stuff((
805 select ',[TG - ' + TagGroupName + ']'
806 from(
807 select th.TagGroupName as TagGroupName
808 from dbo.vTagHierarchy th
809 inner join @SortField sf on th.TagGroupId = sf.ItemId and sf.ItemType = 1 --tag group
810 union
811 select th.TagGroupName as TagGroupName
812 from dbo.vTagHierarchy th
813 inner join @Filter f on th.TagGroupId = f.ItemId and f.ItemType = 1 --tag group
814 ) cols
815 group by TagGroupName
816 order by TagGroupName
817 for xml path(''), TYPE
818 ).value('.', 'nvarchar(max)')
819 ,1,1,'')
820
821 declare @HasTagSelections bit
822 set @HasTagSelections = case when @TagGroupCols is null then 0 else 1 end
823 RETURN case when @HasTagSelections = 1 then '
824 select DocId, ' + @TagGroupCols + N'
825 into #TagDocs
826 from
827 (
828 select
829 d1.DocId,
830 TagGroupName = ''TG - '' + th1.TagGroupName,
831 stuff(
832 (
833 select ''; '' + th2.TagName
834 from #vTagHierarchy th2
835 left join dbo.DocumentTags dt2 on th2.TagId = dt2.TagId
836 where dt2.DocId = d1.DocId and th1.TagGroupName = th2.TagGroupName and th2.IsLeafTag = 1
837 order by th2.TagPath
838 for xml path(''''), TYPE
839 ).value(''.'', ''varchar(max)''), 1, 2, ''''
840 ) as TagName
841 from ' + @DocIdSourceTableName + ' d1
842 left join dbo.DocumentTags dt1 on d1.DocId = dt1.DocId
843 left join #vTagHierarchy th1 on th1.TagId = dt1.TagId
844 ' + case when exists(select * from @SortField) then 'left join @SortField sf on th1.TagGroupId = sf.ItemId and sf.ItemType = 1 --tag group' else '' end + '
845 ' + case when exists(select * from @Filter) then 'left join @Filter f on th1.TagGroupId = f.ItemId and f.ItemType = 1 --tag group' else '' end + '
846 where (th1.IsLeafTag = 1 or th1.IsLeafTag is null)
847 ' + case when @ResultsId is not null then 'and d1.ResultsId = ' + CAST(@ResultsId as nvarchar(10)) else '' end + '
848 ' + case when @BatchId is not null then 'and d1.BatchId = ' + CAST(@BatchId as nvarchar(10)) else '' end + '
849 group by th1.TagGroupName, d1.DocId
850 ) x
851 pivot
852 (
853 max(TagName)
854 for TagGroupName in (' + @TagGroupCols + N')
855 ) p
856 ' else '' end
857END
858
859GO
860
861IF OBJECT_ID('dbo.Documents_GetBatchCount') IS NULL
862 EXEC ('CREATE PROCEDURE dbo.Documents_GetBatchCount AS RETURN 0;');
863GO
864ALTER PROCEDURE [dbo].[Documents_GetBatchCount]
865 @BatchId int,
866 @FilterReviewed int,
867 @Filter dbo.ColumnFilterType readonly,
868 @WithSeconds bit,
869 @WithMilliseconds bit,
870 @StartDate datetime = null,
871 @EndDate datetime = null,
872 @IncludeNoDate bit = null,
873 @DateFieldId int = null,
874 @MinDate datetime,
875 @MaxDate datetime,
876 @QuickSearchResultsId INT,
877 @UserName NVARCHAR(256)
878AS
879set nocount on
880
881DECLARE @sql as nvarchar(max) = ''
882DECLARE @SqlDateRangeWhere nvarchar(max) = ''
883DECLARE @SqlFilterWhere nvarchar(max) = ''
884DECLARE @SqlBatchJoin nvarchar(max) = ''
885DECLARE @TagPivotTempTable nvarchar(max) = ''
886DECLARE @QuickSearchJoin NVARCHAR(MAX) = ''
887DECLARE @SqlWhere nvarchar(max) = ''
888
889exec Documents_GetDocumentIds_GenerateDocumentDateClause @StartDate, @EndDate, @IncludeNoDate, @DateFieldId, @MinDate, @MaxDate, @SqlDateRangeWhere output
890exec Documents_GetDocumentIds_GenerateFilterClause @Filter, @WithSeconds, @WithMilliseconds, @SqlFilterWhere output
891
892if (@BatchId > 0)
893begin
894 set @SqlBatchJoin = 'INNER JOIN Review.BatchDocument bd ON df.DocId = bd.DocId AND bd.BatchId = ' + Convert(varchar(10), @BatchId)
895 if (@FilterReviewed = 1)
896 set @SqlBatchJoin = @SqlBatchJoin + ' AND bd.ReviewStatusId = 1 '
897 else if (@FilterReviewed = 2)
898 set @SqlBatchJoin = @SqlBatchJoin + ' AND bd.ReviewStatusId <> 1 '
899end
900
901IF (@QuickSearchResultsId > 0)
902BEGIN
903 declare @UserKey INT, @ResultsTableName as nvarchar(256)
904 SELECT @UserKey = ActivityTracking.GetUserKeyForUsername(@UserName)
905 SET @ResultsTableName = UserTables.SearchResultsTableName(@UserKey)
906
907 SET @QuickSearchJoin = 'INNER JOIN ' + @ResultsTableName + ' qsr ON bd.DocId = qsr.DocId AND qsr.ResultsId = ' + Convert(varchar(10), @QuickSearchResultsId)
908END
909
910--Build where clause
911if(len(@SqlFilterWhere) > 0)
912begin
913 set @SqlWhere += 'WHERE ' + @SqlFilterWhere
914 if(len(@SqlDateRangeWhere) > 0)
915 set @SqlWhere += ' AND ' + @SqlDateRangeWhere
916end
917else if(len(@SqlDateRangeWhere) > 0)
918 set @SqlWhere += 'WHERE ' + @SqlDateRangeWhere
919
920DECLARE @SortField dbo.OrderedGridItemType
921IF(@BatchId > 0)
922 SET @TagPivotTempTable = dbo.Documents_GenerateTagPivotTempTable(@Filter, @SortField, 'Review.BatchDocument', DEFAULT, @BatchId)
923ELSE
924 SET @TagPivotTempTable = dbo.Documents_GenerateTagPivotTempTable(@Filter, @SortField, 'dbo.Documents', DEFAULT, DEFAULT)
925
926DECLARE @FieldsToInclude dbo.IdTableType
927INSERT INTO @FieldsToInclude (Id)
928SELECT ItemId
929FROM @Filter
930WHERE ItemType = 0 --GridItemType.Field
931UNION
932SELECT @DateFieldId
933WHERE @DateFieldId is not NULL
934
935SELECT
936 TagGroupID
937 ,TagGroupName
938 ,TagId
939 ,TagName
940 ,IsLeafTag
941 ,TagPath
942INTO #vTagHierarchy
943FROM dbo.vTagHierarchy
944
945set @Sql = '
946<TagTempTable>
947
948SELECT
949 count(df.DocId)
950FROM ' + [dbo].[Documents_GenerateAliasedDocumentFields](@FieldsToInclude, 'df');
951
952set @SQL = @SQL + '
953<TagPivotJoin>
954<BatchJoin>
955<QuickSearchJoin>
956<SqlWhere>
957';
958set @sql = REPLACE(@sql,'<TagTempTable>',@TagPivotTempTable);
959if LEN(@TagPivotTempTable) > 1
960BEGIN
961 set @sql = REPLACE(@sql,'<TagPivotJoin>','INNER JOIN #TagDocs dt on dt.docid = df.docid');
962END
963ELSE
964BEGIN
965 set @sql = REPLACE(@sql,'<TagPivotJoin>','');
966END
967set @sql = REPLACE(@sql,'<BatchJoin>', @SqlBatchJoin);
968set @sql = REPLACE(@sql,'<QuickSearchJoin>', @QuickSearchJoin);
969set @sql = REPLACE(@sql,'<SqlWhere>', @SqlWhere);
970
971execute sp_executesql @sql, N'@Filter dbo.ColumnFilterType readonly', @Filter = @Filter
972GO
973
974IF OBJECT_ID('dbo.Documents_GetDocIdCount') IS NULL
975 EXEC ('CREATE PROCEDURE dbo.Documents_GetDocIdCount AS RETURN 0;');
976GO
977ALTER PROCEDURE [dbo].[Documents_GetDocIdCount]
978 @DocIds dbo.IdTableType readonly,
979 @Filter dbo.ColumnFilterType readonly,
980 @WithSeconds bit,
981 @WithMilliseconds bit,
982 @StartDate datetime = null,
983 @EndDate datetime = null,
984 @IncludeNoDate bit = null,
985 @DateFieldId int = null,
986 @MinDate datetime,
987 @MaxDate datetime,
988 @QuickSearchResultsId INT,
989 @UserName NVARCHAR(256)
990AS
991set nocount on
992
993DECLARE @sql as nvarchar(max) = ''
994DECLARE @SqlDateRangeWhere nvarchar(max) = ''
995DECLARE @SqlFilterWhere nvarchar(max) = ''
996DECLARE @TagPivotTempTable nvarchar(max) = ''
997DECLARE @QuickSearchJoin NVARCHAR(MAX) = ''
998DECLARE @SqlWhere nvarchar(max) = ''
999
1000exec Documents_GetDocumentIds_GenerateDocumentDateClause @StartDate, @EndDate, @IncludeNoDate, @DateFieldId, @MinDate, @MaxDate, @SqlDateRangeWhere output
1001exec Documents_GetDocumentIds_GenerateFilterClause @Filter, @WithSeconds, @WithMilliseconds, @SqlFilterWhere OUTPUT
1002
1003IF (@QuickSearchResultsId > 0)
1004BEGIN
1005 declare @UserKey INT, @ResultsTableName as nvarchar(256)
1006 SELECT @UserKey = ActivityTracking.GetUserKeyForUsername(@UserName)
1007 SET @ResultsTableName = UserTables.SearchResultsTableName(@UserKey)
1008
1009 SET @QuickSearchJoin = 'INNER JOIN ' + @ResultsTableName + ' qsr ON df.DocId = qsr.DocId AND qsr.ResultsId = ' + Convert(varchar(10), @QuickSearchResultsId)
1010END
1011
1012select
1013 DocId = Id
1014into #ForDocIds
1015from @DocIds
1016
1017--Build where clause
1018if(len(@SqlFilterWhere) > 0)
1019begin
1020 set @SqlWhere += 'WHERE ' + @SqlFilterWhere
1021 if(len(@SqlDateRangeWhere) > 0)
1022 set @SqlWhere += ' AND ' + @SqlDateRangeWhere
1023end
1024else if(len(@SqlDateRangeWhere) > 0)
1025 set @SqlWhere += 'WHERE ' + @SqlDateRangeWhere
1026
1027DECLARE @SortField dbo.OrderedGridItemType
1028SET @TagPivotTempTable = dbo.Documents_GenerateTagPivotTempTable(@Filter, @SortField, '#ForDocIds', DEFAULT, DEFAULT)
1029
1030DECLARE @FieldToInclude dbo.IdTableType
1031INSERT INTO @FieldToInclude (Id)
1032SELECT ItemId
1033FROM @Filter
1034WHERE ItemType = 0 --GridItemType.Field
1035UNION
1036SELECT @DateFieldId
1037WHERE @DateFieldId is not NULL
1038
1039SELECT
1040 TagGroupID
1041 ,TagGroupName
1042 ,TagId
1043 ,TagName
1044 ,IsLeafTag
1045 ,TagPath
1046INTO #vTagHierarchy
1047FROM dbo.vTagHierarchy
1048
1049set @Sql = '
1050<TagTempTable>
1051
1052SELECT
1053 count(df.DocId)
1054FROM ' + [dbo].[Documents_GenerateAliasedDocumentFields](@FieldToInclude, 'df');
1055
1056SET @SQL = @SQL + '
1057inner join #ForDocIds fd on df.DocId = fd.DocId
1058<TagPivotJoin>
1059<QuickSearchJoin>
1060<SqlWhere>
1061';
1062
1063set @sql = REPLACE(@sql,'<TagTempTable>',@TagPivotTempTable);
1064if LEN(@TagPivotTempTable) > 1
1065BEGIN
1066 set @sql = REPLACE(@sql,'<TagPivotJoin>','INNER JOIN #TagDocs dt on dt.docid = df.docid');
1067END
1068ELSE
1069BEGIN
1070 set @sql = REPLACE(@sql,'<TagPivotJoin>','');
1071END
1072set @sql = REPLACE(@sql,'<QuickSearchJoin>', @QuickSearchJoin);
1073set @sql = REPLACE(@sql,'<SqlWhere>',@SqlWhere);
1074
1075execute sp_executesql @sql, N'@Filter dbo.ColumnFilterType readonly', @Filter = @Filter
1076GO
1077
1078IF OBJECT_ID('dbo.Documents_GetDocumentDateCountsFromBatch') IS NULL
1079 EXEC ('CREATE PROCEDURE dbo.Documents_GetDocumentDateCountsFromBatch AS RETURN 0;');
1080GO
1081ALTER PROCEDURE [dbo].[Documents_GetDocumentDateCountsFromBatch]
1082 @BatchId int,
1083 @FilterReviewed int,
1084 @Filter dbo.ColumnFilterType readonly,
1085 @WithSeconds bit,
1086 @WithMilliseconds bit,
1087 @StartDate datetime = null,
1088 @EndDate datetime = null,
1089 @IncludeNoDate bit,
1090 @DateFieldId int,
1091 @MinDate datetime,
1092 @MaxDate datetime
1093AS
1094set nocount on
1095
1096DECLARE @sql as nvarchar(max) = ''
1097DECLARE @SqlDateRangeWhere nvarchar(max) = ''
1098DECLARE @SqlFilterWhere nvarchar(max) = ''
1099DECLARE @SqlBatchJoin nvarchar(max) = ''
1100DECLARE @TagPivotTempTable nvarchar(max) = ''
1101DECLARE @SqlWhere nvarchar(max) = ''
1102DECLARE @JoinOnAlias nvarchar(5) = 'df'
1103DECLARE @DocumentDateCol nvarchar(max) = ''
1104
1105select
1106 @DocumentDateCol = fd.ColumnName
1107from dbo.vFieldDefinition fd
1108where fd.FieldId = @DateFieldId
1109
1110exec Documents_GetDocumentIds_GenerateDocumentDateClause @StartDate, @EndDate, @IncludeNoDate, @DateFieldId, @MinDate, @MaxDate, @SqlDateRangeWhere output
1111exec Documents_GetDocumentIds_GenerateFilterClause @Filter, @WithSeconds, @WithMilliseconds, @SqlFilterWhere output
1112
1113if (@BatchId > 0)
1114begin
1115 set @SqlBatchJoin = 'INNER JOIN Review.BatchDocument bd ON df.DocId = bd.DocId AND bd.BatchId = ' + Convert(varchar(10), @BatchId)
1116 if (@FilterReviewed = 1)
1117 set @SqlBatchJoin = @SqlBatchJoin + ' AND bd.ReviewStatusId = 1 '
1118 else if (@FilterReviewed = 2)
1119 set @SqlBatchJoin = @SqlBatchJoin + ' AND bd.ReviewStatusId <> 1 '
1120end
1121
1122--Build where clause
1123if(len(@SqlFilterWhere) > 0)
1124begin
1125 set @SqlWhere += 'WHERE ' + @SqlFilterWhere
1126 if(len(@SqlDateRangeWhere) > 0)
1127 set @SqlWhere += ' AND ' + @SqlDateRangeWhere
1128end
1129else if(len(@SqlDateRangeWhere) > 0)
1130 set @SqlWhere += 'WHERE ' + @SqlDateRangeWhere
1131
1132DECLARE @SortField dbo.OrderedGridItemType
1133IF(@BatchId > 0)
1134 SET @TagPivotTempTable = dbo.Documents_GenerateTagPivotTempTable(@Filter, @SortField, 'Review.BatchDocument', DEFAULT, @BatchId)
1135ELSE
1136 SET @TagPivotTempTable = dbo.Documents_GenerateTagPivotTempTable(@Filter, @SortField, 'dbo.Documents', DEFAULT, DEFAULT)
1137
1138DECLARE @FieldsToInclude dbo.IdTableType
1139INSERT INTO @FieldsToInclude (Id)
1140SELECT ItemId
1141FROM @Filter
1142WHERE ItemType = 0 --GridItemType.Field
1143UNION
1144SELECT @DateFieldId
1145WHERE @DateFieldId is not NULL
1146
1147SELECT
1148 TagGroupID
1149 ,TagGroupName
1150 ,TagId
1151 ,TagName
1152 ,IsLeafTag
1153 ,TagPath
1154INTO #vTagHierarchy
1155FROM dbo.vTagHierarchy
1156
1157set @Sql = '
1158<TagTempTable>
1159
1160select
1161 DocumentDate = CAST(' + @JoinOnAlias + '.' + @DocumentDateCol + ' AS DATE),
1162 NumDocuments = COUNT(' + @JoinOnAlias + '.DocId)
1163FROM ' + [dbo].[Documents_GenerateAliasedDocumentFields](@FieldsToInclude, 'df');
1164
1165SET @SQL = @SQL + '
1166<TagPivotJoin>
1167<BatchJoin>
1168<SqlWhere>
1169GROUP BY CAST(' + @JoinOnAlias + '.' + @DocumentDateCol + ' AS DATE)
1170ORDER BY CAST(' + @JoinOnAlias + '.' + @DocumentDateCol + ' AS DATE)
1171'
1172
1173set @sql = REPLACE(@sql,'<TagTempTable>',@TagPivotTempTable);
1174if LEN(@TagPivotTempTable) > 1
1175BEGIN
1176 set @sql = REPLACE(@sql,'<TagPivotJoin>','INNER JOIN #TagDocs dt on dt.docid = df.docid');
1177END
1178ELSE
1179BEGIN
1180 set @sql = REPLACE(@sql,'<TagPivotJoin>','');
1181END
1182set @sql = REPLACE(@sql,'<BatchJoin>', @SqlBatchJoin);
1183set @sql = REPLACE(@sql,'<SqlWhere>',@SqlWhere);
1184
1185--print (@sql)
1186execute sp_executesql @sql, N'@Filter dbo.ColumnFilterType readonly', @Filter = @Filter
1187GO
1188
1189IF OBJECT_ID('dbo.Documents_GetDocumentDateCountsFromDocIds') IS NULL
1190 EXEC ('CREATE PROCEDURE dbo.Documents_GetDocumentDateCountsFromDocIds AS RETURN 0;');
1191GO
1192ALTER PROCEDURE [dbo].[Documents_GetDocumentDateCountsFromDocIds]
1193 @DocIds dbo.IdTableType readonly,
1194 @Filter dbo.ColumnFilterType readonly,
1195 @WithSeconds bit,
1196 @WithMilliseconds bit,
1197 @StartDate datetime = null,
1198 @EndDate datetime = null,
1199 @IncludeNoDate bit,
1200 @DateFieldId int,
1201 @MinDate datetime,
1202 @MaxDate datetime
1203AS
1204set nocount on
1205
1206DECLARE @sql as nvarchar(max) = ''
1207DECLARE @SqlDateRangeWhere nvarchar(max) = ''
1208DECLARE @SqlFilterWhere nvarchar(max) = ''
1209DECLARE @TagPivotTempTable nvarchar(max) = ''
1210DECLARE @SqlWhere nvarchar(max) = ''
1211DECLARE @JoinOnAlias nvarchar(5) = 'df'
1212DECLARE @DocumentDateCol nvarchar(max) = ''
1213
1214select
1215 @DocumentDateCol = fd.ColumnName
1216from dbo.vFieldDefinition fd
1217where fd.FieldId = @DateFieldId
1218
1219select
1220 DocId = Id
1221into #ForDocIds
1222from @DocIds
1223
1224exec Documents_GetDocumentIds_GenerateDocumentDateClause @StartDate, @EndDate, @IncludeNoDate, @DateFieldId, @MinDate, @MaxDate, @SqlDateRangeWhere output
1225exec Documents_GetDocumentIds_GenerateFilterClause @Filter, @WithSeconds, @WithMilliseconds, @SqlFilterWhere output
1226
1227--Build where clause
1228if(len(@SqlFilterWhere) > 0)
1229begin
1230 set @SqlWhere += 'WHERE ' + @SqlFilterWhere
1231 if(len(@SqlDateRangeWhere) > 0)
1232 set @SqlWhere += ' AND ' + @SqlDateRangeWhere
1233end
1234else if(len(@SqlDateRangeWhere) > 0)
1235 set @SqlWhere += 'WHERE ' + @SqlDateRangeWhere
1236
1237DECLARE @SortField dbo.OrderedGridItemType
1238SET @TagPivotTempTable = dbo.Documents_GenerateTagPivotTempTable(@Filter, @SortField, '#ForDocIds', DEFAULT, DEFAULT)
1239
1240DECLARE @FieldsToInclude dbo.IdTableType
1241INSERT INTO @FieldsToInclude (Id)
1242SELECT ItemId
1243FROM @Filter
1244WHERE ItemType = 0 --GridItemType.Field
1245UNION
1246SELECT @DateFieldId
1247WHERE @DateFieldId is not NULL
1248
1249SELECT
1250 TagGroupID
1251 ,TagGroupName
1252 ,TagId
1253 ,TagName
1254 ,IsLeafTag
1255 ,TagPath
1256INTO #vTagHierarchy
1257FROM dbo.vTagHierarchy
1258
1259set @Sql = '
1260<TagTempTable>
1261
1262select
1263 DocumentDate = CAST(' + @DocumentDateCol + ' AS DATE),
1264 NumDocuments = COUNT(' + 'df.DocId)
1265FROM ' + [dbo].[Documents_GenerateAliasedDocumentFields](@FieldsToInclude, 'df');
1266
1267SET @SQL = @SQL + '
1268inner join #ForDocIds fd on df.DocId = fd.DocId
1269<TagPivotJoin>
1270<SqlWhere>
1271GROUP BY CAST(' + @DocumentDateCol + ' AS DATE)
1272ORDER BY CAST(' + @DocumentDateCol + ' AS DATE)
1273';
1274
1275set @sql = REPLACE(@sql,'<TagTempTable>',@TagPivotTempTable);
1276if LEN(@TagPivotTempTable) > 1
1277BEGIN
1278 set @sql = REPLACE(@sql,'<TagPivotJoin>','INNER JOIN #TagDocs dt on dt.docid = df.docid');
1279END
1280ELSE
1281BEGIN
1282 set @sql = REPLACE(@sql,'<TagPivotJoin>','');
1283END
1284set @sql = REPLACE(@sql,'<SqlWhere>',@SqlWhere);
1285set @sql = REPLACE(@sql,'<CurrentDatabase>', DB_NAME())
1286
1287--print (@sql)
1288execute sp_executesql @sql, N'@Filter dbo.ColumnFilterType readonly', @Filter = @Filter
1289GO
1290
1291IF OBJECT_ID('dbo.Documents_GetDocumentDateCountsFromRelationship') IS NULL
1292 EXEC ('CREATE PROCEDURE dbo.Documents_GetDocumentDateCountsFromRelationship AS RETURN 0;');
1293GO
1294ALTER PROCEDURE [dbo].[Documents_GetDocumentDateCountsFromRelationship]
1295 @ResultsId int,
1296 @UserName nvarchar(256),
1297 @Filter dbo.ColumnFilterType readonly,
1298 @WithSeconds bit,
1299 @WithMilliseconds bit,
1300 @StartDate datetime = null,
1301 @EndDate datetime = null,
1302 @IncludeNoDate bit,
1303 @DateFieldId int,
1304 @MinDate datetime,
1305 @MaxDate datetime
1306AS
1307
1308set nocount on
1309
1310DECLARE @sql as nvarchar(max) = ''
1311DECLARE @SqlDateRangeWhere nvarchar(max) = ''
1312DECLARE @SqlFilterWhere nvarchar(max) = ''
1313DECLARE @TagPivotTempTable nvarchar(max) = ''
1314DECLARE @SqlWhere nvarchar(max) = ''
1315DECLARE @UserKey int
1316DECLARE @ResultsTableName as nvarchar(256)
1317DECLARE @DocumentDateCol nvarchar(max) = ''
1318
1319select
1320 @DocumentDateCol = fd.ColumnName
1321from dbo.vFieldDefinition fd
1322where fd.FieldId = @DateFieldId
1323
1324select @UserKey = ActivityTracking.GetUserKeyForUsername(@UserName)
1325exec @ResultsTableName = [UserTables].[RelationshipResultsTableName] @UserKey = @UserKey
1326
1327exec Documents_GetDocumentIds_GenerateDocumentDateClause @StartDate, @EndDate, @IncludeNoDate, @DateFieldId, @MinDate, @MaxDate, @SqlDateRangeWhere output
1328exec Documents_GetDocumentIds_GenerateFilterClause @Filter, @WithSeconds, @WithMilliseconds, @SqlFilterWhere output
1329
1330--Build where clause
1331set @SqlWhere += 'WHERE r.ResultsId = <ResultsId>'
1332if(len(@SqlFilterWhere) > 0)
1333begin
1334 set @SqlWhere += ' AND ' + @SqlFilterWhere
1335 if(len(@SqlDateRangeWhere) > 0)
1336 set @SqlWhere += ' AND ' + @SqlDateRangeWhere
1337end
1338else if(len(@SqlDateRangeWhere) > 0)
1339 set @SqlWhere += ' AND ' + @SqlDateRangeWhere
1340
1341DECLARE @SortField dbo.OrderedGridItemType
1342SET @TagPivotTempTable = dbo.Documents_GenerateTagPivotTempTable(@Filter, @SortField, @ResultsTableName, @ResultsId, DEFAULT)
1343
1344DECLARE @FieldsToInclude dbo.IdTableType
1345INSERT INTO @FieldsToInclude (Id)
1346SELECT ItemId
1347FROM @Filter
1348WHERE ItemType = 0 --GridItemType.Field
1349UNION
1350SELECT @DateFieldId
1351WHERE @DateFieldId is not NULL
1352
1353SELECT
1354 TagGroupID
1355 ,TagGroupName
1356 ,TagId
1357 ,TagName
1358 ,IsLeafTag
1359 ,TagPath
1360INTO #vTagHierarchy
1361FROM dbo.vTagHierarchy
1362
1363set @Sql = '
1364<TagTempTable>
1365
1366select
1367 DocumentDate = CAST(' + @DocumentDateCol + ' AS DATE),
1368 NumDocuments = COUNT(df.DocId)
1369FROM ' + [dbo].[Documents_GenerateAliasedDocumentFields](@FieldsToInclude, 'df')
1370
1371SET @SQL = @SQL + '
1372INNER JOIN <ResultsTableName> r on df.DocId = r.DocId
1373<TagPivotJoin>
1374<SqlWhere>
1375GROUP BY CAST(' + @DocumentDateCol + ' AS DATE)
1376ORDER BY CAST(' + @DocumentDateCol + ' AS DATE)
1377';
1378
1379set @sql = REPLACE(@sql,'<ResultsTableName>',@ResultsTableName);
1380set @sql = REPLACE(@sql,'<TagTempTable>',@TagPivotTempTable);
1381if LEN(@TagPivotTempTable) > 1
1382BEGIN
1383 set @sql = REPLACE(@sql,'<TagPivotJoin>','INNER JOIN #TagDocs dt on dt.docid = df.docid');
1384END
1385ELSE
1386BEGIN
1387 set @sql = REPLACE(@sql,'<TagPivotJoin>','');
1388END
1389set @sql = REPLACE(@sql,'<SqlWhere>',@SqlWhere);
1390set @sql = REPLACE(@sql,'<ResultsId>',Convert(varchar(10), @ResultsId));
1391
1392execute sp_executesql @sql, N'@Filter dbo.ColumnFilterType readonly', @Filter = @Filter
1393GO
1394IF OBJECT_ID('dbo.Documents_GetFilteredDocumentIdsFromDocIds') IS NULL
1395 EXEC ('CREATE PROCEDURE dbo.Documents_GetFilteredDocumentIdsFromDocIds AS RETURN 0;');
1396GO
1397ALTER PROCEDURE [dbo].[Documents_GetFilteredDocumentIdsFromDocIds]
1398 @DocIds IdTableType readonly,
1399 @Filter dbo.ColumnFilterType readonly,
1400 @WithSeconds bit,
1401 @WithMilliseconds bit,
1402 @StartDate datetime = null,
1403 @EndDate datetime = null,
1404 @IncludeNoDate bit = null,
1405 @DateFieldId int = null,
1406 @MinDate datetime,
1407 @MaxDate datetime
1408AS
1409 set nocount on
1410
1411DECLARE @sql as nvarchar(max) = ''
1412DECLARE @SqlDateRangeWhere nvarchar(max) = ''
1413DECLARE @SqlFilterWhere nvarchar(max) = ''
1414DECLARE @TagPivotTempTable nvarchar(max) = ''
1415DECLARE @SqlWhere nvarchar(max) = ''
1416DECLARE @JoinOnAlias nvarchar(5) = 'df'
1417
1418
1419exec Documents_GetDocumentIds_GenerateDocumentDateClause @StartDate, @EndDate, @IncludeNoDate, @DateFieldId, @MinDate, @MaxDate, @SqlDateRangeWhere output
1420exec Documents_GetDocumentIds_GenerateFilterClause @Filter, @WithSeconds, @WithMilliseconds, @SqlFilterWhere output
1421
1422select
1423 d.Id
1424into #DocIds
1425from @DocIds d
1426
1427--Build where clause
1428if(len(@SqlFilterWhere) > 0)
1429begin
1430 set @SqlWhere += 'WHERE ' + @SqlFilterWhere
1431 if(len(@SqlDateRangeWhere) > 0)
1432 set @SqlWhere += ' AND ' + @SqlDateRangeWhere
1433end
1434else if(len(@SqlDateRangeWhere) > 0)
1435 set @SqlWhere += 'WHERE ' + @SqlDateRangeWhere
1436
1437DECLARE @SortField dbo.OrderedGridItemType
1438SET @TagPivotTempTable = dbo.Documents_GenerateTagPivotTempTable(@Filter, @SortField, '#DocIds', DEFAULT, DEFAULT)
1439
1440SELECT
1441 TagGroupID
1442 ,TagGroupName
1443 ,TagId
1444 ,TagName
1445 ,IsLeafTag
1446 ,TagPath
1447INTO #vTagHierarchy
1448FROM dbo.vTagHierarchy
1449
1450set @Sql = '
1451<TagTempTable>
1452
1453SELECT
1454 df.DocId
1455 FROM dbo.vDocumentFields ' + @JoinOnAlias + '
1456 inner join #DocIds di on ' + @JoinOnAlias + '.DocId = di.Id
1457 <TagPivotJoin>
1458 <SqlWhere>
1459';
1460
1461set @sql = REPLACE(@sql,'<TagTempTable>',@TagPivotTempTable);
1462if LEN(@TagPivotTempTable) > 1
1463BEGIN
1464 set @sql = REPLACE(@sql,'<TagPivotJoin>','INNER JOIN #TagDocs dt on dt.docid = di.docid');
1465END
1466ELSE
1467BEGIN
1468 set @sql = REPLACE(@sql,'<TagPivotJoin>','');
1469END
1470set @sql = REPLACE(@sql,'<SqlWhere>',@SqlWhere);
1471
1472--print (@sql)
1473execute sp_executesql @sql, N'@Filter dbo.ColumnFilterType readonly', @Filter = @Filter
1474GO
1475
1476IF OBJECT_ID('dbo.Documents_GetDocumentViewByDocIds') IS NULL
1477 EXEC ('CREATE PROCEDURE dbo.Documents_GetDocumentViewByDocIds AS RETURN 0;');
1478GO
1479ALTER PROCEDURE [dbo].[Documents_GetDocumentViewByDocIds]
1480 @UserName nvarchar(256)
1481, @Doc_SortByDocId IdSortedTableType readonly
1482, @SortField dbo.OrderedGridItemType readonly
1483, @GridSelections TripleIntegerTableType readonly
1484AS
1485BEGIN
1486
1487set nocount on
1488declare @starttime datetime = getdate()
1489declare @UserKey int
1490select @UserKey = ActivityTracking.GetUserKeyForUsername(@UserName)
1491
1492--Copy temp tables for dynamic SQL below
1493select
1494 DocId = Docs.Id,
1495 Docs.SortNumber
1496into #DocIds
1497from @Doc_SortByDocId Docs
1498order by docs.SortNumber
1499
1500
1501
1502select
1503 ItemId = Integer1,
1504 ItemType = Integer2,
1505 ReviewPassId = Integer3
1506into #GridSelections
1507from @GridSelections
1508
1509
1510
1511/************ FIELDS ************/
1512create table #Fields (FieldId int, SystemFieldType int, TableName nvarchar(500), ColumnName nvarchar(500))
1513insert into #Fields (FieldId, SystemFieldType, TableName, ColumnName)
1514select
1515 f.FieldId,
1516 f.SystemFieldType,
1517 f.TableName,
1518 f.ColumnName
1519from vFieldDefinition f
1520inner join #GridSelections g on g.ItemId = f.FieldId and g.ItemType = 0 --field
1521
1522-- Add NativeFileType field in case it is not present in GridSelections
1523-- This is used to join up and get the file icon type for the document
1524declare @NativeTypeTable varchar(500),
1525 @NativeTypeColumn varchar(500)
1526if not exists(select * from #Fields where SystemFieldType = 23) --NativeFileType
1527 insert into #Fields (FieldId, SystemFieldType, TableName, ColumnName)
1528 select
1529 f.FieldId,
1530 f.SystemFieldType,
1531 f.TableName,
1532 f.ColumnName
1533 from vFieldDefinition f
1534 where f.SystemFieldType = 23
1535
1536select
1537 @NativeTypeTable = f.TableName,
1538 @NativeTypeColumn = f.ColumnName
1539 from vFieldDefinition f
1540 where f.SystemFieldType = 23
1541
1542--Compute the DocumentFields select SQL
1543declare
1544 @DocumentFieldsJoinSql varchar(max) = '',
1545 @DocumentFieldsSelectSql varchar(max) = '',
1546 @TableName varchar(500) = '',
1547 @ColumnName varchar(500) = ''
1548
1549declare DocFieldsSelectCursor cursor for
1550select
1551 TableName,
1552 ColumnName
1553from #Fields
1554
1555open DocFieldsSelectCursor
1556
1557fetch next from DocFieldsSelectCursor
1558into
1559 @TableName,
1560 @ColumnName
1561
1562while @@FETCH_STATUS = 0
1563begin
1564 set @DocumentFieldsSelectSql = @DocumentFieldsSelectSql + ' ,' + @TableName + '.' + @ColumnName + '
1565'
1566
1567fetch next from DocFieldsSelectCursor
1568into
1569 @TableName,
1570 @ColumnName
1571end
1572
1573close DocFieldsSelectCursor
1574deallocate DocFieldsSelectCursor
1575
1576--Compute the DocumentFields join SQL
1577declare DocFieldsJoinCursor cursor for
1578select distinct
1579 TableName
1580from #Fields
1581
1582open DocFieldsJoinCursor
1583
1584fetch next from DocFieldsJoinCursor
1585into
1586 @TableName
1587
1588while @@FETCH_STATUS = 0
1589begin
1590 set @DocumentFieldsJoinSql = @DocumentFieldsJoinSql + '
1591inner join ' + @TableName + ' on ' + @TableName + '.DocId = docs.DocId'
1592
1593fetch next from DocFieldsJoinCursor
1594into
1595 @TableName
1596end
1597
1598close DocFieldsJoinCursor
1599deallocate DocFieldsJoinCursor
1600/************ END OF FIELDS ************/
1601
1602/************ TAGS ************/
1603SELECT
1604 TagGroupID
1605 ,TagGroupName
1606 ,TagId
1607 ,TagName
1608 ,IsLeafTag
1609 ,TagPath
1610INTO #vTagHierarchy
1611FROM dbo.vTagHierarchy
1612
1613
1614declare @TagGroupCols varchar(MAX) = '',
1615 @query varchar(MAX) = '',
1616 @TagGroupPrefixCols varchar(max) = ''
1617
1618set @TagGroupCols = stuff((select ',[TG - ' + th.TagGroupName + ']'
1619 from #vTagHierarchy th
1620 inner join #GridSelections gs on th.TagGroupId = gs.ItemId and gs.ItemType = 1 --tag group
1621 group by th.TagGroupName
1622 order by th.TagGroupName
1623 for xml path(''), TYPE
1624 ).value('.', 'nvarchar(max)')
1625 ,1,1,'')
1626
1627set @TagGroupPrefixCols = STUFF((SELECT ',TagPivot.' + t.String + '
1628'
1629 from dbo.DelimitedStringToTable(@TagGroupCols, ',') t
1630 for xml path(''), TYPE
1631 ).value('.', 'nvarchar(max)')
1632,1,1,'')
1633/************ END OF TAGS ************/
1634
1635declare @HasTagSelections bit
1636set @HasTagSelections = case when @TagGroupCols is null or @TagGroupPrefixCols is null then 0 else 1 end
1637declare @Sql nvarchar(max) = ''
1638
1639select @Sql = '
1640
1641select
1642 docid,
1643 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)
1644into #HasRedactions
1645FROM dbo.DocumentAnnotations
1646where RedactionCatId IS NOT NULL
1647group by docid
1648
1649
1650'+ case when @HasTagSelections = 1 then '
1651select DocId, ' + @TagGroupCols + N'
1652into #tagpivot
1653 from
1654 (
1655 select
1656 d1.DocId,
1657 TagGroupName = ''TG - '' + th1.TagGroupName,
1658 stuff(
1659 (
1660 select ''; '' + th2.TagName
1661 from #vTagHierarchy th2
1662 left join dbo.DocumentTags dt2 on th2.TagId = dt2.TagId
1663 where dt2.DocId = d1.DocId and th1.TagGroupID = th2.TagGroupID and th2.IsLeafTag = 1
1664 order by th2.TagPath
1665 for xml path(''''), TYPE
1666 ).value(''.'', ''varchar(max)''), 1, 2, ''''
1667 ) as TagName
1668 from #DocIds d1
1669 left join dbo.DocumentTags dt1 on d1.DocId = dt1.DocId
1670 left join #vTagHierarchy th1 on th1.TagId = dt1.TagId
1671 inner join #GridSelections gs on th1.TagGroupId = gs.ItemId and gs.ItemType = 1 --tag group
1672 where th1.IsLeafTag = 1 or th1.IsLeafTag is null
1673 group by th1.TagGroupID,th1.TagGroupName, d1.DocId
1674 ) x
1675 pivot
1676 (
1677 max(TagName)
1678 for TagGroupName in (' + @TagGroupCols + N')
1679 ) p
1680
1681' else '' end + '
1682
1683SELECT
1684 docs.DocId
1685 ,BegDoc = d.DocumentKey
1686 ,HasRedactions = cast(case when HasRedactions.docid is null then 0 else 1 end as bit)
1687 ,HasOtherUserRedactions = cast(isnull(HasRedactions.OtherUsersRedactionsExist,0) as bit)
1688 ,fc.FileCategoryId
1689 ,fi.FileIconId
1690' + @DocumentFieldsSelectSql
1691+ case when @HasTagSelections = 1 then ',' + @TagGroupPrefixCols else '' end
1692+ 'FROM #DocIds docs
1693inner join dbo.Documents d on docs.DocId = d.DocId' + @DocumentFieldsJoinSql
1694+ case when @HasTagSelections = 1 then '
1695left join #TagPivot TagPivot on docs.DocId = TagPivot.DocId ' else '' end + '
1696LEFT JOIN dbo.FileTypes ft on ft.FileTypeId = ' + @NativeTypeTable + '.' + @NativeTypeColumn + '
1697LEFT JOIN dbo.FileCategories fc on fc.FileCategoryId = ft.FileCategoryId
1698LEFT JOIN FileTypes.FileIcon fi on fi.FileIconId = fc.FileIconId
1699LEFT JOIN #HasRedactions HasRedactions on HasRedactions.DocId = docs.DocId
1700order by docs.SortNumber
1701 '
1702
1703 execute sp_executesql @Sql
1704
1705END
1706GO
1707IF OBJECT_ID('dbo.Search_GetTallyForTagGroup') IS NULL
1708 EXEC ('CREATE PROCEDURE dbo.Search_GetTallyForTagGroup AS RETURN 0;');
1709GO
1710ALTER PROCEDURE [dbo].[Search_GetTallyForTagGroup]
1711 @TagGroupId INT,
1712 @DocIdTable dbo.IdSortedTableType READONLY
1713
1714AS
1715BEGIN
1716
1717 SELECT Id into #DocIdTable
1718 FROM @DocIdTable
1719
1720 SELECT
1721 TagGroupID
1722 ,TagGroupName
1723 ,TagId
1724 ,TagName
1725 ,IsLeafTag
1726 ,TagPath
1727 INTO #vTagHierarchy
1728 FROM dbo.vTagHierarchy
1729
1730 select Id, TagGroup
1731 into #TagDocs
1732 from
1733 (
1734 select
1735 d1.Id,
1736 TagGroupName = 'TagGroup',
1737 stuff(
1738 (
1739 select '; ' + th2.TagName
1740 from #vTagHierarchy th2
1741 left join dbo.DocumentTags dt2 on th2.TagId = dt2.TagId
1742 where dt2.DocId = d1.Id and th1.TagGroupName = th2.TagGroupName and th2.IsLeafTag = 1
1743 order by th2.TagPath
1744 for xml path(''), TYPE
1745 ).value('.', 'varchar(max)'), 1, 2, ''
1746 ) as TagName
1747 from #DocIdTable d1
1748 left join dbo.DocumentTags dt1 on d1.Id = dt1.DocId
1749 left join #vTagHierarchy th1 on th1.TagId = dt1.TagId and th1.TagGroupId = @TagGroupId
1750 where (th1.IsLeafTag = 1 or th1.IsLeafTag is null)
1751 group by th1.TagGroupName, d1.Id
1752 ) x
1753 pivot
1754 (
1755 max(TagName)
1756 for TagGroupName in (TagGroup)
1757 ) p
1758
1759 SELECT [Value] = ISNULL(t.TagGroup, ''), [Count] = COUNT(*)
1760 FROM #TagDocs t
1761 GROUP BY t.TagGroup
1762 ORDER BY 2 DESC, [Value] ASC
1763
1764END
1765GO
1766