You can not select more than 25 topics
Topics must start with a letter or number, can include dashes ('-') and can be up to 35 characters long.
70 lines
1.9 KiB
70 lines
1.9 KiB
-- Creates default user collections for organization users |
|
-- Filters out existing default collections at database level |
|
CREATE OR ALTER PROCEDURE [dbo].[Collection_CreateDefaultCollections] |
|
@OrganizationId UNIQUEIDENTIFIER, |
|
@DefaultCollectionName VARCHAR(MAX), |
|
@OrganizationUserCollectionIds AS [dbo].[TwoGuidIdArray] READONLY -- OrganizationUserId, CollectionId |
|
AS |
|
BEGIN |
|
SET NOCOUNT ON |
|
|
|
DECLARE @Now DATETIME2(7) = GETUTCDATE() |
|
|
|
-- Filter to only users who don't have default collections |
|
SELECT ids.Id1, ids.Id2 |
|
INTO #FilteredIds |
|
FROM @OrganizationUserCollectionIds ids |
|
WHERE NOT EXISTS ( |
|
SELECT 1 |
|
FROM [dbo].[CollectionUser] cu |
|
INNER JOIN [dbo].[Collection] c ON c.Id = cu.CollectionId |
|
WHERE c.OrganizationId = @OrganizationId |
|
AND c.[Type] = 1 -- CollectionType.DefaultUserCollection |
|
AND cu.OrganizationUserId = ids.Id1 |
|
); |
|
|
|
-- Insert collections only for users who don't have default collections yet |
|
INSERT INTO [dbo].[Collection] |
|
( |
|
[Id], |
|
[OrganizationId], |
|
[Name], |
|
[CreationDate], |
|
[RevisionDate], |
|
[Type], |
|
[ExternalId], |
|
[DefaultUserCollectionEmail] |
|
) |
|
SELECT |
|
ids.Id2, -- CollectionId |
|
@OrganizationId, |
|
@DefaultCollectionName, |
|
@Now, |
|
@Now, |
|
1, -- CollectionType.DefaultUserCollection |
|
NULL, |
|
NULL |
|
FROM |
|
#FilteredIds ids; |
|
|
|
-- Insert collection user mappings |
|
INSERT INTO [dbo].[CollectionUser] |
|
( |
|
[CollectionId], |
|
[OrganizationUserId], |
|
[ReadOnly], |
|
[HidePasswords], |
|
[Manage] |
|
) |
|
SELECT |
|
ids.Id2, -- CollectionId |
|
ids.Id1, -- OrganizationUserId |
|
0, -- ReadOnly = false |
|
0, -- HidePasswords = false |
|
1 -- Manage = true |
|
FROM |
|
#FilteredIds ids; |
|
|
|
DROP TABLE #FilteredIds; |
|
END |
|
GO
|
|
|