133 lines
3.3 KiB
Transact-SQL
133 lines
3.3 KiB
Transact-SQL
SET ANSI_NULLS ON;
|
|
GO
|
|
SET QUOTED_IDENTIFIER ON;
|
|
GO
|
|
|
|
CREATE OR ALTER PROCEDURE dbo.WordCompanion_Project_HasAccess
|
|
@ProjectID int,
|
|
@UserID int
|
|
AS
|
|
BEGIN
|
|
SET NOCOUNT ON;
|
|
|
|
SELECT CAST(CASE WHEN EXISTS
|
|
(
|
|
SELECT 1
|
|
FROM dbo.Projects p
|
|
INNER JOIN dbo.ProjectUserAccess pua ON pua.ProjectID = p.ProjectID
|
|
WHERE p.ProjectID = @ProjectID
|
|
AND p.IsArchived = 0
|
|
AND pua.UserID = @UserID
|
|
AND pua.IsActive = 1
|
|
)
|
|
THEN 1 ELSE 0 END AS bit) AS HasAccess;
|
|
END;
|
|
GO
|
|
|
|
CREATE OR ALTER PROCEDURE dbo.WordCompanion_Character_CreateDiscovered
|
|
@UserID int,
|
|
@ProjectID int,
|
|
@CandidatesJson nvarchar(max)
|
|
AS
|
|
BEGIN
|
|
SET NOCOUNT ON;
|
|
SET XACT_ABORT ON;
|
|
|
|
IF NOT EXISTS
|
|
(
|
|
SELECT 1
|
|
FROM dbo.Projects p
|
|
INNER JOIN dbo.ProjectUserAccess pua ON pua.ProjectID = p.ProjectID
|
|
WHERE p.ProjectID = @ProjectID
|
|
AND p.IsArchived = 0
|
|
AND pua.UserID = @UserID
|
|
AND pua.IsActive = 1
|
|
)
|
|
RETURN;
|
|
|
|
DECLARE @Candidates table
|
|
(
|
|
Name nvarchar(200) NOT NULL PRIMARY KEY
|
|
);
|
|
|
|
INSERT @Candidates (Name)
|
|
SELECT DISTINCT LEFT(LTRIM(RTRIM(JSON_VALUE(value, '$.Name'))), 200)
|
|
FROM OPENJSON(@CandidatesJson)
|
|
WHERE NULLIF(LTRIM(RTRIM(JSON_VALUE(value, '$.Name'))), N'') IS NOT NULL;
|
|
|
|
IF NOT EXISTS (SELECT 1 FROM @Candidates)
|
|
RETURN;
|
|
|
|
DECLARE @Results table
|
|
(
|
|
CharacterId int NOT NULL,
|
|
Name nvarchar(200) NOT NULL,
|
|
Created bit NOT NULL
|
|
);
|
|
|
|
INSERT @Results (CharacterId, Name, Created)
|
|
SELECT ch.CharacterID,
|
|
c.Name,
|
|
CAST(0 AS bit)
|
|
FROM @Candidates c
|
|
INNER JOIN dbo.Characters ch ON ch.ProjectID = @ProjectID
|
|
AND ch.IsArchived = 0
|
|
AND UPPER(LTRIM(RTRIM(ch.CharacterName))) = UPPER(c.Name);
|
|
|
|
DECLARE @Missing table
|
|
(
|
|
Name nvarchar(200) NOT NULL PRIMARY KEY
|
|
);
|
|
|
|
INSERT @Missing (Name)
|
|
SELECT c.Name
|
|
FROM @Candidates c
|
|
WHERE NOT EXISTS
|
|
(
|
|
SELECT 1
|
|
FROM dbo.Characters ch
|
|
WHERE ch.ProjectID = @ProjectID
|
|
AND ch.IsArchived = 0
|
|
AND UPPER(LTRIM(RTRIM(ch.CharacterName))) = UPPER(c.Name)
|
|
);
|
|
|
|
DECLARE @Inserted table
|
|
(
|
|
CharacterID int NOT NULL,
|
|
CharacterName nvarchar(200) NOT NULL
|
|
);
|
|
|
|
INSERT dbo.Characters (ProjectID, CharacterName, ShortName, CharacterImportance, ShowInQuickAddBar, DefaultDescription)
|
|
OUTPUT inserted.CharacterID, inserted.CharacterName INTO @Inserted (CharacterID, CharacterName)
|
|
SELECT @ProjectID,
|
|
m.Name,
|
|
NULL,
|
|
8,
|
|
1,
|
|
N'Created from Word Companion manuscript import.'
|
|
FROM @Missing m
|
|
ORDER BY m.Name;
|
|
|
|
INSERT @Results (CharacterId, Name, Created)
|
|
SELECT CharacterID,
|
|
CharacterName,
|
|
CAST(1 AS bit)
|
|
FROM @Inserted;
|
|
|
|
SELECT @ProjectID AS ProjectId,
|
|
SUM(CASE WHEN Created = 1 THEN 1 ELSE 0 END) AS CreatedCount,
|
|
SUM(CASE WHEN Created = 0 THEN 1 ELSE 0 END) AS SkippedCount,
|
|
CASE
|
|
WHEN SUM(CASE WHEN Created = 1 THEN 1 ELSE 0 END) = 0 THEN N'No new characters were created.'
|
|
ELSE N'Characters created.'
|
|
END AS Message
|
|
FROM @Results;
|
|
|
|
SELECT CharacterId,
|
|
Name,
|
|
Created
|
|
FROM @Results
|
|
ORDER BY Created DESC, Name;
|
|
END;
|
|
GO
|