· 6 years ago · Jun 14, 2019, 05:42 AM
1
2
3
4ALTER PROCEDURE [dbo].[InsertScopeIndexValuesToTags]
5(
6 @portalId AS INT,
7 @userID AS BIGINT
8) AS
9/*
10BEGIN
11 SELECT [tmp_scopeTags].[DocumentIndexPropertyID] ,[tmp_scopeTags].[Value], [tmp_scopeTags].[UserID] , [tmp_scopeTags].[DocumentID]
12 INTO #tmp
13 FROM ( SELECT DISTINCT dt.[DocumentId] , t.[UserID]
14 FROM [dbo].[DocumentTags] AS DT
15 INNER JOIN [dbo].[Tags] AS T ON [DT].[TagID] = [T].[Id]
16 WHERE [DT].[portalId] = @portalId
17 ) tag_
18 INNER JOIN ( SELECT [IR].[DocumentIndexPropertyID] ,[IR].[Value] ,[UserID] ,[ADIT].[DocumentID]
19 FROM [dbo].[IndexReference] AS IR
20 INNER JOIN ( SELECT [UserID] ,[DocumentIndexPropertyID] ,[IndexReferenceID]
21 FROM [dbo].[UserIndexValueScope]
22 UNION
23 SELECT [UserID] ,[DocumentIndexPropertyID] ,[IndexReferenceID]
24 FROM [dbo].[UserGroup_User]
25 AS UGU
26 INNER JOIN [dbo].[UserGroupIndexValueScope]
27 AS UGIVS ON [UGU].[UserGroupID] = [UGIVS].[UserGroupID]
28 ) T ON [IR].[ID] = [T].[IndexReferenceID]
29 AND [IR].[DocumentIndexPropertyID] = [T].[DocumentIndexPropertyID]
30 INNER JOIN [dbo].[AllDocumentIndexTags] AS ADIT ON [ADIT].[IndexId] = [T].[DocumentIndexPropertyID]
31 AND [IR].[Value] = [ADIT].[Value] WHERE [T].[UserID] = @userID
32 ) tmp_scopeTags ON [tag_].[DocumentId] = [tmp_scopeTags].[DocumentID]
33 AND [tag_].[UserID] = [tmp_scopeTags].[UserID]
34
35
36 INSERT [dbo].[Tags]( [Tag] ,[UserID] ,[IndexId] ,[portalId])
37 SELECT DISTINCT [Value], [cte].[UserID],[DocumentIndexPropertyID],@portalId AS [portalId]
38 FROM (SELECT * FROM #tmp )[cte]
39 LEFT JOIN(SELECT * FROM [dbo].[Tags] WHERE [portalId] = @portalId) AS T ON [cte].[UserID] = [T].[UserID]
40 AND [cte].[DocumentIndexPropertyID] = [T].[IndexId]
41 AND [cte].[Value] = [T].[Tag]
42 WHERE id IS NULL
43
44 DELETE [dbo].[Tags] WHERE [Id] IN (
45 SELECT T2.[Id] FROM [#tmp] AS T RIGHT JOIN (SELECT * FROM [dbo].[Tags] WHERE [portalId] = @portalId) AS T2
46 ON [T].[UserID] = [T2].[UserID]
47 AND [T].[DocumentIndexPropertyID]= [T2].[IndexId]
48 AND [T].[Value] = [T2].[Tag]
49 WHERE [T2].[IndexId] IN (SELECT DISTINCT t3.[DocumentIndexPropertyID] FROM [#tmp] AS T3)
50 AND [T].[DocumentID] IS NULL)
51
52
53 INSERT [dbo].[DocumentTags]( [DocumentId], [TagID], [portalId] )
54 SELECT [temp_documentTags].[DocumentID] ,[temp_documentTags].[Id] , [temp_documentTags].[portalId]
55 FROM ( SELECT *
56 FROM [dbo].[DocumentTags]
57 WHERE [portalId] = @portalId
58 ) AS DT
59 RIGHT JOIN ( SELECT [DocumentID] , [Id] , [portalId] ,T2.[Id] AS TagID
60 FROM [#tmp] AS T
61 INNER JOIN ( SELECT *
62 FROM [dbo].[Tags]
63 WHERE [portalId] = @portalId
64 ) AS T2 ON [T].[UserID] = [T2].[UserID]
65 AND [T].[DocumentIndexPropertyID] = [T2].[IndexId]
66 AND [T].[Value] = [T2].[Tag]
67 ) temp_documentTags ON [DT].[TagID] = [temp_documentTags].[Id]
68 AND [DT].[DocumentId] = [temp_documentTags].[DocumentID]
69 AND [DT].[portalId] = [temp_documentTags].[portalId]
70 AND [DT].[TagID] = [temp_documentTags].[TagID]
71 WHERE [DT].[id] IS NULL;
72
73 DROP TABLE [#tmp]
74END
75*/
76
77GO
78
79ALTER PROCEDURE [dbo].[AddTagsForDocument] ( @DocumentID AS BIGINT )
80AS
81/*
82 BEGIN
83
84 DECLARE @library_ID BIGINT;
85 DECLARE @PortalId INT ;
86 DECLARE @Doc_SecurityLevel INT;
87
88 SELECT @Doc_SecurityLevel= [SecurityLevel],@library_ID=[Library_ID] FROM [dbo].[Document] AS D
89 WHERE [ID] = @DocumentID
90
91 SET @PortalId =@library_ID
92
93 CREATE TABLE #tmp
94 (
95 [DocumentID] BIGINT ,
96 [Value] NVARCHAR(MAX) ,
97 [Columns_] NVARCHAR(MAX) ,
98 [IndexId] INT ,
99 [IsUserScope] BIT ,
100 UserId INT
101 )
102
103 CREATE TABLE #tmp_permited_users ([UserId] INT)
104-----------------------------------------------------------------------------
105
106 INSERT [#tmp_permited_users] ( [UserId] )
107 SELECT DISTINCT [UserId]
108 FROM (SELECT [UGU].[UserID], [DocumentScopeID]
109 FROM [dbo].[DocumentScope_UserGroups] AS DSUG WITH(nolock)
110 INNER JOIN [dbo].[UserGroup_User] AS UGU WITH(nolock) ON [DSUG].[UserGroudID] = [UGU].[UserGroupID]
111
112 UNION
113
114 SELECT [UserID], [DocumentScopeID]
115 FROM [dbo].[DocumentScope_Users] AS DSU WITH(nolock)
116 ) user_scope
117 INNER JOIN (SELECT [DocumentScopeID]
118 FROM [dbo].[DocumentScopeDocuments] AS DSD WITH(nolock)
119 WHERE [DocumentID] = @DocumentID
120
121 UNION
122
123 SELECT 1 [DocumentScopeID]
124 )docscop
125 ON docscop.DocumentScopeID = [user_scope].[DocumentScopeID]
126 WHERE [user_scope].[DocumentScopeID] IN (SELECT [ID] FROM [dbo].[DocumentScope] WITH(nolock) WHERE [IsActive] = 1 )
127 AND user_scope.[UserID] IN (SELECT [ID]
128 FROM [dbo].[User] AS U WITH(nolock)
129 WHERE CASE WHEN [EnableUpperLevel] = 1 THEN [SecurityLevel] + 1 ELSE [SecurityLevel] END >= @Doc_SecurityLevel
130 AND [IsActive] = 1--AND [user_scope].[UserID] IN (SELECT [userId] FROM [dbo].[TagCreationStatus] AS TCS WHERE [portalId] = @library_ID)
131 )
132-----------------------------------------------------------------------------
133
134
135DECLARE @scopeIndexCount INT;
136
137 SELECT @scopeIndexCount = COUNT(*)
138 FROM [dbo].[AllDocumentIndexTags] AS ADIT
139 WHERE [DocumentID] = @DocumentID
140 AND [IsUserScope] = 1
141
142
143 IF @scopeIndexCount > 0
144 BEGIN
145 DELETE FROM #tmp_permited_users
146 WHERE UserID NOT IN (
147 SELECT [UserID]
148 FROM ( SELECT *
149 FROM [dbo].[AllDocumentIndexTags] AS ADIT WITH(nolock)
150 WHERE [DocumentID] = @DocumentID
151 ) _doc
152 INNER JOIN (SELECT [UserID],[IndexId],[IndexReferenceID], [Value]
153 FROM (SELECT [UserID], [DocumentIndexPropertyID] AS IndexId,[IndexReferenceID]
154 FROM [dbo].[UserIndexValueScope] AS UIVS WITH(nolock)
155
156 UNION
157
158 SELECT [UserID],[DocumentIndexPropertyID],[IndexReferenceID]
159 FROM [dbo].[UserGroupIndexValueScope] AS UGIVS WITH(nolock)
160 INNER JOIN [dbo].[UserGroup_User] AS UGU WITH(nolock)
161 ON [UGIVS].[UserGroupID] = [UGU].[UserGroupID]
162 ) T
163 INNER JOIN [dbo].[IndexReference] AS IR WITH(nolock)
164 ON [T].[IndexReferenceID] = [IR].[ID]
165 ) user_Access_scop_val
166 ON [_doc].[IndexId] = [user_Access_scop_val].[IndexId]
167 AND [_doc].[Value] = [user_Access_scop_val].[Value]
168 WHERE [user_Access_scop_val].[UserID] IN ( SELECT [ID]
169 FROM [dbo].[User] AS U WITH(nolock)
170 WHERE CASE WHEN [EnableUpperLevel] = 1 THEN [SecurityLevel] + 1 ELSE [SecurityLevel]END >= @Doc_SecurityLevel
171 AND [IsActive] = 1
172 )
173 AND [user_Access_scop_val].[UserID] IN (SELECT [userId] FROM [dbo].[TagCreationStatus] AS TCS WHERE [portalId] = @library_ID)
174 GROUP BY [user_Access_scop_val].[UserID]
175 HAVING COUNT(*) = ISNULL(@scopeIndexCount, 0)
176 )
177 END
178-----------------------------------------------------------------------------
179
180/*
181 USER ACCESSIBLE SCOPE VALUES
182*/
183INSERT [#tmp] ( [UserId], [IndexId], [Value], [IsUserScope], [DocumentID] )
184
185
186SELECT [UserID], [IndexId], [Value], [IsUserScope], [DocumentID]
187 FROM (SELECT [UserID], T.IndexId, [IR].[Value], [IsUserScope], [T2].[DocumentID]
188 FROM (SELECT [UserID], [DocumentIndexPropertyID] AS IndexId,[IndexReferenceID]
189 FROM [dbo].[UserIndexValueScope] AS UIVS WITH(nolock)
190
191 UNION
192
193 SELECT [UserID],[DocumentIndexPropertyID], [IndexReferenceID]
194 FROM [dbo].[UserGroupIndexValueScope] AS UGIVS WITH(nolock)
195 INNER JOIN [dbo].[UserGroup_User] AS UGU WITH(nolock)
196 ON [UGIVS].[UserGroupID] = [UGU].[UserGroupID]
197 ) T
198 INNER JOIN [dbo].[IndexReference] AS IR WITH(nolock) ON [T].[IndexReferenceID] = [IR].[ID]
199 INNER JOIN (SELECT *
200 FROM [dbo].[AllDocumentIndexTags]AS ADIT WITH(nolock)
201 WHERE [DocumentID] = @DocumentID
202 AND [IsUserScope] = 1
203 )T2
204 ON [T2].[IndexId] = [IR].[DocumentIndexPropertyID]
205 AND [IR].[Value] = [T2].[Value]
206
207--USER ACCESSIBLE INDEXS FOR DOCUMENTS
208
209 UNION
210
211 SELECT [UserID],[DocumentIndexPropertyID] AS IndexId, [Value], [T2].[IsUserScope], [DocumentID]
212 FROM (SELECT [UserID], [DocumentIndexPropertyID]
213 FROM [dbo].[UserIndexScope] WITH(nolock)
214
215 UNION
216
217 SELECT [UserID], [DocumentIndexPropertyID]
218 FROM [dbo].[UserGroup_User] AS UGU WITH(nolock)
219 INNER JOIN [dbo].[UserGroupIndexScope]AS UGIS WITH(nolock) ON [UGU].[UserGroupID] = [UGIS].[UserGroupID]
220 ) T
221 INNER JOIN [dbo].[DocumentIndexProperty] AS DIP WITH(nolock) ON [DIP].[ID] = [T].[DocumentIndexPropertyID]
222 INNER JOIN (SELECT [DocumentID], [Value], [Columns_], [IndexId],[IsUserScope]
223 FROM [dbo].[AllDocumentIndexTags] AS ADIT WITH(nolock)
224 WHERE [DocumentID] = @DocumentID
225 ) T2
226 ON [T2].[IndexId] = [T].[DocumentIndexPropertyID]
227 WHERE [DIP].[IsUserScope] = 0
228 ) T
229WHERE [T].[UserID] IN (SELECT TT.[UserID] FROM #tmp_permited_users TT)
230-----------------------------------------------------------------------------
231
232/* ADDING PRE DEFINE TAGS */
233 INSERT #tmp ([DocumentID],[VALUE],[IsUserScope],[UserID])
234 SELECT DISTINCT [DocumentID],[Content],0, UserId
235 FROM #tmp_permited_users
236 CROSS JOIN (SELECT [DocumentID],[Content]
237 FROM [dbo].[Tag] AS T WITH(nolock)
238 INNER JOIN [dbo].[PreDefineTag] AS PDT WITH(nolock) ON [PDT].[ID] = [T].[PreDefineTagID]
239 WHERE [DocumentID] = @DocumentID
240 AND [PDT].[Library_ID] = @library_ID
241 AND [IsActive] = 1
242 ) pre_def_tag
243 WHERE [Content] NOT IN (SELECT T.value FROM #tmp T);
244/* ADDING PRE DEFINE TAGS */
245-----------------------------------------------------------------------------
246
247
248INSERT [dbo].[Tags] ( [Tag], [UserID], [portalId] )
249
250SELECT DISTINCT [Value],[cte].[UserID],@portalId AS [portalId]
251 FROM #tmp [cte]
252LEFT JOIN (SELECT *
253 FROM [dbo].[Tags] WITH(nolock)
254 WHERE [portalId] = @portalId
255 ) AS T
256 ON [cte].[UserID] = [T].[UserID]
257 AND [cte].[Value] = [T].[Tag]
258 and T.indexid is null
259 WHERE id IS NULL
260
261-----------------------------------------------------------------------------
262
263
264INSERT [dbo].[DocumentTags] ( [DocumentId], [TagID], [portalId] )
265
266SELECT DISTINCT [temp_documentTags].[DocumentID],[temp_documentTags].[Id],[temp_documentTags].[portalId]
267 FROM (SELECT *
268 FROM [dbo].[DocumentTags] WITH(nolock)
269 WHERE [portalId] = @portalId
270 ) AS DT
271RIGHT JOIN (SELECT [DocumentID], [Id], [portalId],T2.[Id] AS TagID
272 FROM [#tmp] AS T
273 INNER JOIN (SELECT *
274 FROM [dbo].[Tags] WITH(nolock)
275 WHERE [portalId] = @portalId
276 )AS T2
277 ON [T].[UserID] = [T2].[UserID]
278 AND [T].[Value] = [T2].[Tag]
279 )temp_documentTags
280 ON [DT].[TagID] = [temp_documentTags].[Id]
281 AND [DT].[DocumentId] = [temp_documentTags].[DocumentID]
282 AND [DT].[portalId] = [temp_documentTags].[portalId]
283 AND [DT].[TagID] = [temp_documentTags].[TagID]
284 AND [DT].[DocumentId] <> 0
285 WHERE [DT].[id] IS NULL ;
286
287-----------------------------------------------------------------------------
288
289
290 DROP TABLE [#tmp]
291 DROP TABLE #tmp_permited_users
292
293END
294*/
295GO
296
297ALTER PROCEDURE [dbo].[AddTagsForDocumentUpdate] ( @DocumentID AS BIGINT )
298AS
299/*
300 BEGIN
301
302 DECLARE @library_ID BIGINT;
303 DECLARE @PortalId INT ;
304 DECLARE @Doc_SecurityLevel INT;
305
306 SELECT @Doc_SecurityLevel= [SecurityLevel],@library_ID=[Library_ID] FROM [dbo].[Document] AS D
307 WHERE [ID] = @DocumentID
308
309 SET @PortalId =@library_ID
310
311 CREATE TABLE #tmp
312 (
313 [DocumentID] BIGINT ,
314 [Value] NVARCHAR(MAX) ,
315 [Columns_] NVARCHAR(MAX) ,
316 [IndexId] INT ,
317 [IsUserScope] BIT ,
318 UserId INT
319 )
320
321 CREATE TABLE #tmp_permited_users ([UserId] INT)
322-----------------------------------------------------------------------------
323
324 INSERT [#tmp_permited_users] ( [UserId] )
325 SELECT DISTINCT [UserId]
326 FROM (SELECT [UGU].[UserID], [DocumentScopeID]
327 FROM [dbo].[DocumentScope_UserGroups] AS DSUG WITH(nolock)
328 INNER JOIN [dbo].[UserGroup_User] AS UGU WITH(nolock) ON [DSUG].[UserGroudID] = [UGU].[UserGroupID]
329
330 UNION
331
332 SELECT [UserID], [DocumentScopeID]
333 FROM [dbo].[DocumentScope_Users] AS DSU WITH(nolock)
334 ) user_scope
335 INNER JOIN (SELECT [DocumentScopeID]
336 FROM [dbo].[DocumentScopeDocuments] AS DSD WITH(nolock)
337 WHERE [DocumentID] = @DocumentID
338
339 UNION
340
341 SELECT 1 [DocumentScopeID]
342 )docscop
343 ON docscop.DocumentScopeID = [user_scope].[DocumentScopeID]
344 WHERE [user_scope].[DocumentScopeID] IN (SELECT [ID] FROM [dbo].[DocumentScope] WITH(nolock) WHERE [IsActive] = 1 )
345 AND user_scope.[UserID] IN (SELECT [ID]
346 FROM [dbo].[User] AS U WITH(nolock)
347 WHERE CASE WHEN [EnableUpperLevel] = 1 THEN [SecurityLevel] + 1 ELSE [SecurityLevel] END >= @Doc_SecurityLevel
348 AND [IsActive] = 1
349 )
350-----------------------------------------------------------------------------
351
352
353DECLARE @scopeIndexCount INT;
354
355 SELECT @scopeIndexCount = COUNT(*)
356 FROM [dbo].[AllDocumentIndexTags] AS ADIT
357 WHERE [DocumentID] = @DocumentID
358 AND [IsUserScope] = 1
359
360
361 IF @scopeIndexCount > 0
362 BEGIN
363 DELETE FROM #tmp_permited_users
364 WHERE UserID NOT IN (
365 SELECT [UserID]
366 FROM ( SELECT *
367 FROM [dbo].[AllDocumentIndexTags] AS ADIT WITH(nolock)
368 WHERE [DocumentID] = @DocumentID
369 ) _doc
370 INNER JOIN (SELECT [UserID],[IndexId],[IndexReferenceID], [Value]
371 FROM (SELECT [UserID], [DocumentIndexPropertyID] AS IndexId,[IndexReferenceID]
372 FROM [dbo].[UserIndexValueScope] AS UIVS WITH(nolock)
373
374 UNION
375
376 SELECT [UserID],[DocumentIndexPropertyID],[IndexReferenceID]
377 FROM [dbo].[UserGroupIndexValueScope] AS UGIVS WITH(nolock)
378 INNER JOIN [dbo].[UserGroup_User] AS UGU WITH(nolock)
379 ON [UGIVS].[UserGroupID] = [UGU].[UserGroupID]
380 ) T
381 INNER JOIN [dbo].[IndexReference] AS IR WITH(nolock)
382 ON [T].[IndexReferenceID] = [IR].[ID]
383 ) user_Access_scop_val
384 ON [_doc].[IndexId] = [user_Access_scop_val].[IndexId]
385 AND [_doc].[Value] = [user_Access_scop_val].[Value]
386 WHERE [user_Access_scop_val].[UserID] IN ( SELECT [ID]
387 FROM [dbo].[User] AS U WITH(nolock)
388 WHERE CASE WHEN [EnableUpperLevel] = 1 THEN [SecurityLevel] + 1 ELSE [SecurityLevel]END >= @Doc_SecurityLevel
389 AND [IsActive] = 1
390 )
391 AND [user_Access_scop_val].[UserID] IN (SELECT [userId] FROM [dbo].[TagCreationStatus] AS TCS WHERE [portalId] = @library_ID)
392 GROUP BY [user_Access_scop_val].[UserID]
393 HAVING COUNT(*) = ISNULL(@scopeIndexCount, 0)
394 )
395 END
396-----------------------------------------------------------------------------
397
398/*
399 USER ACCESSIBLE SCOPE VALUES
400*/
401INSERT [#tmp] ( [UserId], [IndexId], [Value], [IsUserScope], [DocumentID] )
402
403
404SELECT [UserID], [IndexId], [Value], [IsUserScope], [DocumentID]
405 FROM (SELECT [UserID], T.IndexId, [IR].[Value], [IsUserScope], [T2].[DocumentID]
406 FROM (SELECT [UserID], [DocumentIndexPropertyID] AS IndexId,[IndexReferenceID]
407 FROM [dbo].[UserIndexValueScope] AS UIVS WITH(nolock)
408
409 UNION
410
411 SELECT [UserID],[DocumentIndexPropertyID], [IndexReferenceID]
412 FROM [dbo].[UserGroupIndexValueScope] AS UGIVS WITH(nolock)
413 INNER JOIN [dbo].[UserGroup_User] AS UGU WITH(nolock)
414 ON [UGIVS].[UserGroupID] = [UGU].[UserGroupID]
415 ) T
416 INNER JOIN [dbo].[IndexReference] AS IR WITH(nolock) ON [T].[IndexReferenceID] = [IR].[ID]
417 INNER JOIN (SELECT *
418 FROM [dbo].[AllDocumentIndexTags]AS ADIT WITH(nolock)
419 WHERE [DocumentID] = @DocumentID
420 AND [IsUserScope] = 1
421 )T2
422 ON [T2].[IndexId] = [IR].[DocumentIndexPropertyID]
423 AND [IR].[Value] = [T2].[Value]
424
425--USER ACCESSIBLE INDEXS FOR DOCUMENTS
426
427 UNION
428
429 SELECT [UserID],[DocumentIndexPropertyID] AS IndexId, [Value], [T2].[IsUserScope], [DocumentID]
430 FROM (SELECT [UserID], [DocumentIndexPropertyID]
431 FROM [dbo].[UserIndexScope] WITH(nolock)
432
433 UNION
434
435 SELECT [UserID], [DocumentIndexPropertyID]
436 FROM [dbo].[UserGroup_User] AS UGU WITH(nolock)
437 INNER JOIN [dbo].[UserGroupIndexScope]AS UGIS WITH(nolock) ON [UGU].[UserGroupID] = [UGIS].[UserGroupID]
438 ) T
439 INNER JOIN [dbo].[DocumentIndexProperty] AS DIP WITH(nolock) ON [DIP].[ID] = [T].[DocumentIndexPropertyID]
440 INNER JOIN (SELECT [DocumentID], [Value], [Columns_], [IndexId],[IsUserScope]
441 FROM [dbo].[AllDocumentIndexTags] AS ADIT WITH(nolock)
442 WHERE [DocumentID] = @DocumentID
443 ) T2
444 ON [T2].[IndexId] = [T].[DocumentIndexPropertyID]
445 WHERE [DIP].[IsUserScope] = 0
446 ) T
447WHERE [T].[UserID] IN (SELECT TT.[UserID] FROM #tmp_permited_users TT)
448-----------------------------------------------------------------------------
449
450/* ADDING PRE DEFINE TAGS */
451 INSERT #tmp ([DocumentID],[VALUE],[IsUserScope],[UserID])
452 SELECT DISTINCT [DocumentID],[Content],0, UserId
453 FROM #tmp_permited_users
454 CROSS JOIN (SELECT [DocumentID],[Content]
455 FROM [dbo].[Tag] AS T WITH(nolock)
456 INNER JOIN [dbo].[PreDefineTag] AS PDT WITH(nolock) ON [PDT].[ID] = [T].[PreDefineTagID]
457 WHERE [DocumentID] = @DocumentID
458 AND [PDT].[Library_ID] = @library_ID
459 AND [IsActive] = 1
460 ) pre_def_tag
461 WHERE [Content] NOT IN (SELECT T.value FROM #tmp T);
462/* ADDING PRE DEFINE TAGS */
463-----------------------------------------------------------------------------
464
465
466DELETE [dbo].[Tags]
467 WHERE [portalId] = @portalId
468 AND [UserID] IN ( SELECT tmp.[UserID]
469 FROM [#tmp] tmp )
470 AND [Id] NOT IN ( SELECT [TagID]
471 FROM [dbo].[DocumentTags] AS DT With(nolock)
472 WHERE [DT].[portalId] = @portalId )
473
474-----------------------------------------------------------------------------
475INSERT [dbo].[Tags] ( [Tag], [UserID], [portalId] )
476
477SELECT DISTINCT [Value],[cte].[UserID],@portalId AS [portalId]
478 FROM #tmp [cte]
479LEFT JOIN (SELECT *
480 FROM [dbo].[Tags] WITH(nolock)
481 WHERE [portalId] = @portalId
482 ) AS T
483 ON [cte].[UserID] = [T].[UserID]
484 AND [cte].[Value] = [T].[Tag]
485 and T.indexid is null
486 WHERE id IS NULL
487
488-----------------------------------------------------------------------------
489
490DELETE [dbo].[DocumentTags]
491 WHERE [id] IN (SELECT [DocumentTagId]
492 FROM (SELECT dt.[id] AS DocumentTagId, [DocumentId],[TagID], [Tag], [UserID], [T].[portalId]
493 FROM [dbo].[DocumentTags] AS DT With(nolock)
494 INNER JOIN [dbo].[Tags] AS T With(nolock) ON [DT].[TagID] = [T].[Id]
495 WHERE [DT].[portalId] = @portalId
496 AND [DocumentId] IN ( SELECT DISTINCT tmp.[DocumentId] FROM [#tmp] tmp )
497 AND [UserID] IN ( SELECT DISTINCT tmp.userid FROM [#tmp] tmp )
498 ) all_tags
499 LEFT JOIN [#tmp] AS T2
500 ON [all_tags].[DocumentId] = [T2].[DocumentID]
501 AND [all_tags].[UserID] = [T2].[UserId]
502 AND [all_tags].[Tag] = [T2].[VALUE]
503 WHERE T2.DocumentId IS NULL
504 )
505
506
507-----------------------------------------------------------------------------
508
509
510INSERT [dbo].[DocumentTags] ( [DocumentId], [TagID], [portalId] )
511
512SELECT DISTINCT [temp_documentTags].[DocumentID],[temp_documentTags].[Id],[temp_documentTags].[portalId]
513 FROM (SELECT *
514 FROM [dbo].[DocumentTags] WITH(nolock)
515 WHERE [portalId] = @portalId
516 ) AS DT
517RIGHT JOIN (SELECT [DocumentID], [Id], [portalId],T2.[Id] AS TagID
518 FROM [#tmp] AS T
519 INNER JOIN (SELECT *
520 FROM [dbo].[Tags] WITH(nolock)
521 WHERE [portalId] = @portalId
522 )AS T2
523 ON [T].[UserID] = [T2].[UserID]
524 AND [T].[Value] = [T2].[Tag]
525 )temp_documentTags
526 ON [DT].[TagID] = [temp_documentTags].[Id]
527 AND [DT].[DocumentId] = [temp_documentTags].[DocumentID]
528 AND [DT].[portalId] = [temp_documentTags].[portalId]
529 AND [DT].[TagID] = [temp_documentTags].[TagID]
530 AND [DT].[DocumentId] <> 0
531 WHERE [DT].[id] IS NULL ;
532
533-----------------------------------------------------------------------------
534
535
536 DROP TABLE [#tmp]
537 DROP TABLE #tmp_permited_users
538
539END
540*/
541GO
542
543ALTER PROCEDURE [dbo].[GetIndexToTagsForUser] (@Library_ID BIGINT,@userID AS BIGINT) AS
544/*
545BEGIN
546SET NOCOUNT ON ;
547
548 DECLARE @portalId INT;
549
550 DECLARE @IndexDataTable UNIQUEIDENTIFIER;
551 SELECT @portalId = [ID], @IndexDataTable=[IndexDataTable] FROM [dbo].[Portal] AS P
552 WHERE [P].[ID] = @Library_ID
553
554 IF EXISTS (SELECT * FROM [dbo].[TagCreationStatus] WHERE portalId=@portalId AND userId=@userID)
555 RETURN;
556
557 DECLARE @all_records_table NVARCHAR(50) = NEWID()
558 DECLARE @str NVARCHAR(MAX)
559
560 DECLARE @global_temp_table_name NVARCHAR(200) = 'temp_tag_table_' + CAST(@userID AS VARCHAR(200))
561
562 DECLARE @tblColumnsInSelect TABLE -- this table contains the column names shold include with the result set
563 (
564 ID INT IDENTITY(1, 1) ,
565 [column] VARCHAR(200) ,
566 [IsUserScope] BIT
567 )
568
569
570 BEGIN TRANSACTION
571 BEGIN TRY
572 DECLARE @temp_sql NVARCHAR(1000);
573 SET @temp_sql = 'DROP TABLE [dbo].[' + @global_temp_table_name +']';
574
575 IF EXISTS (SELECT * FROM [sys].tables WHERE RTRIM([name]) = @global_temp_table_name)
576 BEGIN
577 EXECUTE sp_executesql @temp_sql ;
578 END
579
580 SET @temp_sql = N'CREATE TABLE [dbo].[' + @global_temp_table_name + N'] ( ID BIGINT PRIMARY KEY IDENTITY(1,1), DocumentId BIGINT, PageIds NVARCHAR(MAX) )'
581 EXECUTE SP_EXECUTESQL @temp_sql
582
583 COMMIT TRANSACTION
584 END TRY
585 BEGIN CATCH
586
587 ROLLBACK TRANSACTION
588 PRINT ERROR_MESSAGE()
589
590 END CATCH
591
592 BEGIN TRY
593 IF NOT EXISTS(SELECT * FROM @tblColumnsInSelect )
594 BEGIN
595 INSERT INTO @tblColumnsInSelect ( [column],[IsUserScope] )
596
597 SELECT doc_index_prop.IndexCaption, doc_index_prop.IsUserScope
598 FROM [dbo].[DocumentIndexProperty] AS doc_index_prop
599 WHERE [ID] IN (
600 SELECT [DocumentIndexPropertyID]
601 FROM [dbo].[UserIndexScope] AS user_index
602 WHERE [UserID] = @userID AND [VisibleResultIndex]=1
603 UNION
604 SELECT [DocumentIndexPropertyID]
605 FROM [dbo].[UserGroupIndexScope] AS UG_index
606 WHERE [UserGroupID] IN ( SELECT [UserGroupID]
607 FROM [dbo].[UserGroup_User] AS UG_user
608 WHERE [UserID] = @userID )
609 ) AND [doc_index_prop].[Library_ID] = @Library_ID
610
611
612 END
613
614
615 DECLARE @columns NVARCHAR(MAX);
616 DECLARE @tmp_table_creation_sql NVARCHAR(MAX);
617
618 SELECT @columns = ISNULL(@columns +',','') + [column] ,
619 @tmp_table_creation_sql = ISNULL(@tmp_table_creation_sql +',','') + [column] + CASE [ControlID] WHEN 3 THEN ' DATETIME ' ELSE ' nvarchar('+ CAST([IndexLength]AS NVARCHAR(10)) +')' END
620 FROM @tblColumnsInSelect INNER JOIN [dbo].[DocumentIndexProperty] DIP ON [column] = QUOTENAME([DIP].[IndexCaption])
621 WHERE [DIP].[Library_ID] =@Library_ID;
622
623 CREATE TABLE #all_records_table ( id BIGINT PRIMARY KEY IDENTITY , [DocumentID] BIGINT )
624
625 PRINT @columns
626
627 DECLARE @DocScope TABLE (ScopeID INT )
628 DECLARE @ScopeCount INT
629 SET @ScopeCount=0;
630
631
632 INSERT INTO @DocScope
633 SELECT ID
634 FROM dbo.DocumentScope
635 WHERE ID IN (
636 SELECT DocumentScopeID
637 FROM dbo.DocumentScope_UserGroups
638 WHERE UserGroudID IN ( SELECT UserGroupID
639 FROM dbo.UserGroup_User
640 WHERE UserID = @userID ) AND [Library_ID] = @Library_ID ) AND [IsActive] = 1
641 --UNION
642 --SELECT DocumentScopeID
643 --FROM DocumentScope_Users
644 --WHERE UserID = @userID
645
646 DECLARE @ScopeColumn VARCHAR(500);
647 SET @ScopeColumn='';
648
649 SELECT @ScopeColumn=@ScopeColumn + ',' + CAST( ScopeID AS VARCHAR(50)) FROM @DocScope
650 SET @ScopeColumn=SUBSTRING(@ScopeColumn,2,len(@ScopeColumn));
651 DECLARE @scopeEnabled BIT;
652
653 SELECT @scopeEnabled = [enabled] FROM dbo.Feature_Level1 WHERE ClassName = 'documentScope'
654
655 DECLARE @UserSecurityLevel INT ;
656 DECLARE @EnableUpperLevel BIT ;
657
658 SELECT @UserSecurityLevel=[SecurityLevel],
659 @EnableUpperLevel = [U].[EnableUpperLevel] FROM [dbo].[User] U
660 WHERE U.[ID] = @userID;
661
662 SET @ScopeCount= (SELECT COUNT( * ) FROM @DocScope)
663 IF(ISNULL( @scopeEnabled,0 ) = 1 )
664 BEGIN
665
666 IF EXISTS (SELECT * FROM @DocScope)
667 BEGIN
668 SET @str = ISNULL(@str ,'') +
669
670 CASE WHEN NULLIF(RTRIM(@ScopeColumn),'') IS NOT NULL
671 THEN ' DocumentID IN ( SELECT DocumentID FROM DocumentScopeDocuments WHERE DocumentScopeID IN (' + @ScopeColumn + ')) AND'
672 ELSE '' END
673 + ' [IsActive]=1 AND [Library_ID] = '+ CAST(@library_ID AS NVARCHAR(20))
674 print @str;
675 END
676 ELSE
677 BEGIN
678 SELECT 0;
679 PRINT 'NO DOCUMENT SCOPES ASSIGNED TO USER GROUP'
680 GOTO Early_Exit;
681 END
682
683 END
684 ---------------------------------Index Value Scope-----------------------
685
686
687 DECLARE @sql_in NVARCHAR(max);
688
689 WITH T1 AS ( SELECT [DocumentIndexPropertyID],
690 [Value],
691 [IndexCaption]
692 FROM [dbo].[IndexReference] AS index_ref
693 INNER JOIN ( SELECT DISTINCT
694 IndexReferenceID
695 FROM dbo.UserIndexValueScope
696 WHERE UserID = @userID
697 UNION
698 SELECT DISTINCT
699 IndexReferenceID
700 FROM dbo.UserGroupIndexValueScope
701 WHERE UserGroupID IN ( SELECT UserGroupID
702 FROM dbo.UserGroup_User
703 WHERE UserID = @userID )
704 ) T
705 ON [index_ref].[ID] = [T].[IndexReferenceID]
706 LEFT JOIN dbo.DocumentIndexProperty AS doc_index_prop
707 ON [index_ref].[DocumentIndexPropertyID] = [doc_index_prop].[ID]
708 WHERE --QUOTENAME([doc_index_prop].[IndexCaption]) IN ( SELECT Col_in_sel.[column]
709 --FROM @tblColumnsInSelect AS Col_in_sel )
710 --AND
711 [IsUserScope] = 1 AND [index_ref].[Library_ID] = @library_ID
712 )
713
714 SELECT @sql_in = COALESCE( @sql_in + ' and ','') + QUOTENAME(T2.[IndexCaption]) + ' in (' +
715 STUFF(ISNULL((SELECT ', ' + QUOTENAME(T3.[Value],'''') + ''
716 FROM T1 AS T3
717 WHERE T3.[IndexCaption] = T2.[IndexCaption]
718 GROUP BY T3.[Value]
719 FOR XML PATH(''), TYPE).value('.', 'VARCHAR(max)'), ''), 1, 2, '') + ') '
720 FROM T1 AS T2
721 GROUP BY T2.[IndexCaption]
722
723 SET @str = N'SELECT DocumentID FROM [dbo].'
724 + QUOTENAME(@IndexDataTable) + ' INNER JOIN [dbo].[Document] D ON [D].[ID] = DocumentID '
725 + CASE WHEN ISNULL(@sql_in, '') = ''
726 THEN CASE WHEN ISNULL(@str, '') = ''
727 THEN 'WHERE [IsActive]=1 AND [Library_ID] = ' + CAST(@library_ID AS NVARCHAR(20))
728 ELSE ' WHERE ' + ISNULL(@str, '')
729 END
730 ELSE ' WHERE ' + ISNULL(@str, '')
731 + CASE WHEN ISNULL(@str, '') = ''
732 THEN @sql_in
733 + ' AND [IsActive]=1 AND [Library_ID] = ' + CAST(@library_ID AS NVARCHAR(20))
734 ELSE ' AND ' + @sql_in
735 END
736 END;
737
738 --SET @str = @str + ' ' + ISNULL(@sql_in,'')
739
740 PRINT @sql_in
741 --PRINT ('insert into #all_records_table ' + @str)
742 SET @str = 'INSERT INTO #all_records_table ' + @str
743
744 IF EXISTS ( SELECT *
745 FROM @tblColumnsInSelect
746 WHERE IsUserScope = 1 )
747 BEGIN
748 IF ( ISNULL(@sql_in, '') = '' )
749 BEGIN
750 SELECT 0
751 PRINT 'NO INDEX VALUE SCOPES ASSIGNED TO USER OR USER GROUP'
752 END
753
754 ELSE
755 BEGIN
756 SET @str = @str + N';'
757 --PRINT @str
758 EXECUTE sp_executesql @str
759 END
760 END
761 ELSE
762 BEGIN
763 SET @str = @str + N'; '
764 --PRINT @str
765 EXECUTE sp_executesql @str
766 END
767
768 --PRINT @str ;
769
770 DECLARE @permited_Columns AS NVARCHAR(2000);
771 SELECT @permited_Columns = COALESCE(@permited_Columns +',','') + QUOTENAME([column],'''') FROM @tblColumnsInSelect
772 PRINT @permited_Columns;
773 CREATE TABLE #tmp ( [DocumentID] BIGINT , [Value] NVARCHAR(2000),
774 [Columns_] NVARCHAR(150) , [IndexId] INT,
775 [IsUserScope] BIT, UserId INT)
776
777 SET @str = 'INSERT INTO #tmp
778 SELECT [ADIT].[DocumentID] , [ADIT].[Value] ,
779 [ADIT].[Columns_] , [ADIT].[IndexId] ,
780 [ADIT].[IsUserScope], '+CAST (@userID AS NVARCHAR(5))+' AS UserId
781 FROM [dbo].#all_records_table AS DI
782 INNER JOIN [dbo].[AllDocumentIndexTags] AS ADIT ON DI.[DocumentID] = [ADIT].[DocumentID]
783 '+ CASE WHEN @permited_Columns IS NOT NULL THEN 'WHERE [ADIT].[Columns_] IN ( '+@permited_Columns+' )' ELSE '' END
784 PRINT @str
785 IF @permited_Columns IS NOT NULL
786 BEGIN
787 --PRINT @str
788 EXECUTE sp_executesql @str
789 END
790
791 INSERT [#tmp] ( [DocumentID], [Value], [IsUserScope], [UserId] )
792 SELECT [T].[DocumentID], [Content], 0, @userID
793 FROM [dbo].[Tag] AS T
794 INNER JOIN ( SELECT DISTINCT
795 [DocumentID]
796 FROM [#tmp]
797
798 ) AS T2 ON [T].[DocumentID] = [T2].[DocumentID]
799 INNER JOIN [dbo].[PreDefineTag] AS PDT ON [PDT].[ID] = [T].[PreDefineTagID]
800 WHERE --[IsActive] = 1 AND
801 [Content] NOT IN ( SELECT [Value]
802 FROM [#tmp] AS T3
803 WHERE [T3].[UserId] = @userID
804 AND [T3].[DocumentID] = [T].[DocumentID] )
805 INSERT [dbo].[Tags] ( [Tag], [UserID], [portalId] )
806 SELECT DISTINCT
807 [Value], [cte].[UserID], @portalId AS [portalId]
808 FROM ( SELECT *
809 FROM #tmp
810 -- raveen
811 where [IsUserScope]=0
812 or
813 value in (SELECT [Value] FROM IndexReference
814 WHERE IndexReference.ID IN(
815 SELECT [IndexReferenceID]
816 FROM [dbo].[UserIndexValueScope]
817 WHERE [UserID] = @userId AND Library_ID=@Library_ID
818 UNION
819 SELECT [IndexReferenceID]
820 FROM [dbo].[UserGroupIndexValueScope]
821 WHERE [UserGroupID] IN ( SELECT [UserGroupID]
822 FROM [dbo].[UserGroup_User]
823 WHERE [UserID] =@userId
824 )
825 ) and Library_ID=@Library_ID )
826
827
828
829 ) [cte]
830 LEFT JOIN ( SELECT *
831 FROM [dbo].[Tags]
832 WHERE [portalId] = @portalId ) AS T ON [cte].[UserID] = [T].[UserID] AND [cte].[Value] = [T].[Tag]
833 WHERE id IS NULL
834
835
836 DELETE [dbo].[DocumentTags]
837 WHERE [id] IN (
838 SELECT [DocumentTagId]
839 FROM ( SELECT dt.[id] AS DocumentTagId, [DocumentId],
840 [TagID], [Tag], [UserID], --[IndexId],
841 [T].[portalId]
842 FROM [dbo].[DocumentTags] AS DT
843 INNER JOIN [dbo].[Tags] AS T ON [DT].[TagID] = [T].[Id]
844 WHERE [DT].[portalId] = @portalId
845 AND [DocumentId] IN ( SELECT DISTINCT
846 tmp.[DocumentId]
847 FROM
848 [#tmp] tmp )
849 AND [UserID] IN ( SELECT DISTINCT
850 tmp.userid
851 FROM [#tmp] tmp ) ) all_tags
852 LEFT JOIN [#tmp] AS T2 ON [all_tags].[DocumentId] = [T2].[DocumentID]
853 -- AND [all_tags].[IndexId] = [T2].[IndexId]
854 AND [all_tags].[UserID] = [T2].[UserId]
855 AND [all_tags].[Tag] = [T2].[VALUE]
856 WHERE T2.DocumentId IS NULL )
857
858 INSERT [dbo].[DocumentTags] ( [DocumentId], [TagID], [portalId] )
859 SELECT DISTINCT [temp_documentTags].[DocumentID],
860 [temp_documentTags].[Id],
861 [temp_documentTags].[portalId]
862 FROM ( SELECT *
863 FROM [dbo].[DocumentTags]
864 WHERE [portalId] = @portalId) AS DT
865 RIGHT JOIN ( SELECT [DocumentID], [Id], [portalId],
866 T2.[Id] AS TagID
867 FROM [#tmp] AS T
868 INNER JOIN ( SELECT
869 *
870 FROM [dbo].[Tags]
871 WHERE
872 [portalId] = @portalId )
873 AS T2 ON [T].[UserID] = [T2].[UserID]
874 --AND [T].[IndexId] = [T2].[IndexId]
875 AND [T].[Value] = [T2].[Tag] ) temp_documentTags ON [DT].[TagID] = [temp_documentTags].[Id]
876 AND [DT].[DocumentId] = [temp_documentTags].[DocumentID]
877 AND [DT].[portalId] = [temp_documentTags].[portalId]
878 AND [DT].[TagID] = [temp_documentTags].[TagID]
879 WHERE [DT].[id] IS NULL ;
880
881
882 BEGIN TRANSACTION CREATION_STAT
883
884 IF NOT EXISTS ( SELECT * FROM [dbo].[TagCreationStatus] AS TCS WHERE [portalId] = @portalId AND [userId] = @userID)
885 BEGIN
886
887 INSERT [dbo].[TagCreationStatus]
888 ( [portalId] , [userId] , [LastUpdateDateTime] )
889 VALUES ( @portalId, @userID, GETDATE() )
890
891 END
892 ELSE
893 BEGIN
894
895 UPDATE [dbo].[TagCreationStatus] SET [LastUpdateDateTime] = GETDATE()
896 WHERE [portalId] = @portalId AND [userId] = @userID
897
898 END
899
900 COMMIT TRANSACTION CREATION_STAT
901
902 END TRY
903
904 BEGIN CATCH
905 IF @@TRANCOUNT > 0 ROLLBACK TRANSACTION CREATION_STAT
906
907 SELECT ERROR_NUMBER() AS ErrorNumber ,ERROR_MESSAGE() AS ErrorMessage;
908 SET @temp_sql = 'DROP TABLE [dbo].[' + @global_temp_table_name +']';
909
910 IF EXISTS (SELECT * FROM [sys].tables WHERE RTRIM([name]) = @global_temp_table_name)
911 BEGIN
912 EXECUTE sp_executesql @temp_sql ;
913 END
914 END CATCH
915
916 Early_Exit:
917 SET @temp_sql = 'DROP TABLE [dbo].[' + @global_temp_table_name +']';
918 IF EXISTS (SELECT * FROM [sys].tables WHERE RTRIM([name]) = @global_temp_table_name)
919 BEGIN
920 EXECUTE sp_executesql @temp_sql ;
921 END
922END
923
924
925
926-- GetIndexToTagsForUser 90, 217
927*/
928GO
929
930ALTER PROCEDURE [dbo].[RegeneratePreDefineTags]( @JSON NVARCHAR(MAX), @Library_ID BIGINT, @DocumentID BIGINT, @User_Id BIGINT)
931AS
932/*
933BEGIN
934
935 DECLARE @TABLE TABLE
936 (
937 [ID] BIGINT,
938 [CONTENT] NVARCHAR(MAX)
939 )
940 BEGIN TRY
941 INSERT @TABLE
942 SELECT P.[NAME], P.[StringValue]
943 FROM parseJSON(@JSON) P
944 WHERE P.[parent_ID] = 1
945
946 END TRY
947 BEGIN CATCH
948 DELETE FROM dbo.Tag
949 WHERE PreDefineTagID IN (SELECT ID FROM dbo.PreDefineTag WHERE IsUserTag = 1) AND DocumentID = @DocumentID
950 END CATCH
951
952 DECLARE @id INT
953 DECLARE @newID INT
954 DECLARE @Content NVARCHAR(MAX)
955 DECLARE @MyCursor CURSOR
956 DECLARE @Tid INT
957 DECLARE @CurID INT
958 DECLARE @ListofIDs TABLE(IDs INT)
959 DECLARE @Li TABLE(IDds INT)
960
961 SET @MyCursor = CURSOR FAST_FORWARD
962 FOR
963 SELECT [CONTENT] FROM @TABLE T LEFT JOIN Tag TG ON T.ID = TG.PreDefineTagID WHERE TG.ID IS NULL OR TG.DocumentID <> @DocumentID
964 OPEN @MyCursor
965 FETCH NEXT FROM @MyCursor
966 INTO @Content
967 WHILE @@FETCH_STATUS = 0
968 BEGIN
969 SELECT @CurID=ID FROM @TABLE WHERE CONTENT=@Content
970 IF @CurID = 0
971 BEGIN
972 IF NOT EXISTS (SELECT ID FROM dbo.PreDefineTag WHERE Content = @Content AND Library_ID = @Library_ID)
973 BEGIN
974 INSERT INTO [dbo].[PreDefineTag] (Library_ID, Content, IsActive, IsUserTag)
975 SELECT @Library_ID,T1.[CONTENT],0,1
976 FROM @TABLE T1
977 WHERE T1.CONTENT = @Content
978 SET @Tid = @@IDENTITY
979 INSERT INTO dbo.Tag([Library_ID],[DocumentID],[PreDefineTagID],[DateTime],[UserID],[PageID])
980 VALUES (@Library_ID, @DocumentID, @Tid, GETDATE(), @User_Id, 1)
981 INSERT INTO @ListofIDs VALUES(@Tid)
982 END
983 END
984 ELSE
985 BEGIN
986 IF NOT EXISTS (SELECT ID FROM [dbo].Tag WHERE PreDefineTagID = @CurID AND DocumentID = @DocumentID)
987 BEGIN
988 INSERT INTO dbo.Tag([Library_ID],[DocumentID],[PreDefineTagID],[DateTime],[UserID],[PageID])
989 VALUES (@Library_ID, @DocumentID, @CurID, GETDATE(), @User_Id, 1)
990 END
991 ELSE
992 BEGIN
993 IF NOT EXISTS (SELECT ID FROM [dbo].PreDefineTag WHERE Content = @Content AND Library_ID = @Library_ID)
994 BEGIN
995 UPDATE [dbo].Tags SET Tag = @Content WHERE Tag = (SELECT Content FROM [dbo].PreDefineTag WHERE ID = @CurID) AND portalId = @Library_ID
996 END
997 END
998 END
999 FETCH NEXT FROM @MyCursor
1000 INTO @Content
1001 END
1002 CLOSE @MyCursor
1003 DEALLOCATE @MyCursor
1004
1005 UPDATE [dbo].[PreDefineTag]
1006 SET [Content] = T2.[CONTENT]
1007 FROM @TABLE T2
1008 WHERE [dbo].[PreDefineTag].[ID] = T2.[ID]
1009 AND T2.ID <> 0
1010
1011 INSERT INTO @Li SELECT ID FROM @TABLE UNION SELECT IDs FROM @ListofIDs
1012
1013 DELETE FROM dbo.Tag WHERE ID IN (SELECT TG.ID FROM dbo.Tag TG INNER JOIN dbo.[PreDefineTag] PDT ON TG.PreDefineTagID = PDT.ID
1014 WHERE TG.PreDefineTagID NOT IN (SELECT IDds FROM @Li) AND TG.DocumentID = @DocumentID AND PDT.IsUserTag = 1)
1015
1016 DELETE FROM dbo.DocumentTags WHERE ID IN (
1017 SELECT DTG.ID FROM dbo.Tags TGS INNER JOIN dbo.DocumentTags DTG ON TGS.ID = DTG.TagID
1018 WHERE TGS.Tag IN (SELECT Content FROM dbo.PreDefineTag WHERE ID NOT IN (SELECT PreDefineTagID FROM dbo.Tag WHERE DocumentID = @DocumentID) AND IsUserTag = 1)
1019 AND DTG.DocumentId = @DocumentID)
1020
1021END
1022
1023 -- [RegeneratePreDefineTags] '{ "selected":}, "all": {"89439":"eryry","89440":"yertyr","89441":"ery","89442":"uyytu","89443":"iii","89444":"ooo"}, "libraryId": { "id": "2", } }' ,331,22246,1
1024
1025
1026
1027*/