110 lines
4.4 KiB
Transact-SQL
110 lines
4.4 KiB
Transact-SQL
SET ANSI_NULLS ON;
|
|
GO
|
|
SET QUOTED_IDENTIFIER ON;
|
|
GO
|
|
|
|
CREATE OR ALTER PROCEDURE dbo.WordCompanion_SceneLinks_Update
|
|
@SceneID int,
|
|
@UserID int,
|
|
@CharacterIds nvarchar(max) = NULL,
|
|
@AssetIds nvarchar(max) = NULL,
|
|
@LocationIds nvarchar(max) = NULL
|
|
AS
|
|
BEGIN
|
|
SET NOCOUNT ON;
|
|
|
|
DECLARE @ProjectID int;
|
|
SELECT @ProjectID = b.ProjectID
|
|
FROM dbo.Scenes s
|
|
INNER JOIN dbo.Chapters c ON c.ChapterID = s.ChapterID
|
|
INNER JOIN dbo.Books b ON b.BookID = c.BookID
|
|
INNER JOIN dbo.Projects p ON p.ProjectID = b.ProjectID
|
|
INNER JOIN dbo.ProjectUserAccess pua ON pua.ProjectID = p.ProjectID
|
|
WHERE s.SceneID = @SceneID
|
|
AND s.IsArchived = 0
|
|
AND c.IsArchived = 0
|
|
AND b.IsArchived = 0
|
|
AND p.IsArchived = 0
|
|
AND pua.UserID = @UserID
|
|
AND pua.IsActive = 1;
|
|
|
|
IF @ProjectID IS NULL
|
|
RETURN;
|
|
|
|
DECLARE @Characters TABLE (ID int PRIMARY KEY);
|
|
DECLARE @Assets TABLE (ID int PRIMARY KEY);
|
|
DECLARE @Locations TABLE (ID int PRIMARY KEY);
|
|
|
|
INSERT @Characters (ID)
|
|
SELECT DISTINCT TRY_CONVERT(int, LTRIM(RTRIM(value)))
|
|
FROM STRING_SPLIT(ISNULL(@CharacterIds, N''), N',')
|
|
WHERE NULLIF(LTRIM(RTRIM(value)), N'') IS NOT NULL
|
|
AND TRY_CONVERT(int, LTRIM(RTRIM(value))) IS NOT NULL;
|
|
|
|
INSERT @Assets (ID)
|
|
SELECT DISTINCT TRY_CONVERT(int, LTRIM(RTRIM(value)))
|
|
FROM STRING_SPLIT(ISNULL(@AssetIds, N''), N',')
|
|
WHERE NULLIF(LTRIM(RTRIM(value)), N'') IS NOT NULL
|
|
AND TRY_CONVERT(int, LTRIM(RTRIM(value))) IS NOT NULL;
|
|
|
|
INSERT @Locations (ID)
|
|
SELECT DISTINCT TRY_CONVERT(int, LTRIM(RTRIM(value)))
|
|
FROM STRING_SPLIT(ISNULL(@LocationIds, N''), N',')
|
|
WHERE NULLIF(LTRIM(RTRIM(value)), N'') IS NOT NULL
|
|
AND TRY_CONVERT(int, LTRIM(RTRIM(value))) IS NOT NULL;
|
|
|
|
IF EXISTS (SELECT 1 FROM @Characters requested WHERE requested.ID <= 0 OR NOT EXISTS (SELECT 1 FROM dbo.Characters c WHERE c.CharacterID = requested.ID AND c.ProjectID = @ProjectID AND c.IsArchived = 0))
|
|
RETURN;
|
|
IF EXISTS (SELECT 1 FROM @Assets requested WHERE requested.ID <= 0 OR NOT EXISTS (SELECT 1 FROM dbo.StoryAssets sa WHERE sa.StoryAssetID = requested.ID AND sa.ProjectID = @ProjectID AND sa.IsArchived = 0))
|
|
RETURN;
|
|
IF EXISTS (SELECT 1 FROM @Locations requested WHERE requested.ID <= 0 OR NOT EXISTS (SELECT 1 FROM dbo.Locations l WHERE l.LocationID = requested.ID AND l.ProjectID = @ProjectID AND l.IsArchived = 0))
|
|
RETURN;
|
|
|
|
DELETE sc
|
|
FROM dbo.SceneCharacters sc
|
|
WHERE sc.SceneID = @SceneID
|
|
AND NOT EXISTS (SELECT 1 FROM @Characters requested WHERE requested.ID = sc.CharacterID)
|
|
AND sc.RoleInSceneTypeID IS NULL
|
|
AND sc.PresenceTypeID IS NULL
|
|
AND sc.LocationID IS NULL
|
|
AND sc.EntryLocationID IS NULL
|
|
AND sc.ExitLocationID IS NULL
|
|
AND sc.AppearanceNotes IS NULL
|
|
AND sc.OutfitDescription IS NULL
|
|
AND sc.PhysicalCondition IS NULL
|
|
AND sc.EmotionalState IS NULL
|
|
AND sc.KnowledgeNotes IS NULL;
|
|
|
|
INSERT dbo.SceneCharacters (SceneID, CharacterID)
|
|
SELECT @SceneID, requested.ID
|
|
FROM @Characters requested
|
|
WHERE NOT EXISTS (SELECT 1 FROM dbo.SceneCharacters sc WHERE sc.SceneID = @SceneID AND sc.CharacterID = requested.ID);
|
|
|
|
DELETE wcsa
|
|
FROM dbo.WordCompanionSceneAssets wcsa
|
|
WHERE wcsa.SceneID = @SceneID
|
|
AND NOT EXISTS (SELECT 1 FROM @Assets requested WHERE requested.ID = wcsa.StoryAssetID);
|
|
|
|
INSERT dbo.WordCompanionSceneAssets (SceneID, StoryAssetID)
|
|
SELECT @SceneID, requested.ID
|
|
FROM @Assets requested
|
|
WHERE NOT EXISTS (SELECT 1 FROM dbo.WordCompanionSceneAssets wcsa WHERE wcsa.SceneID = @SceneID AND wcsa.StoryAssetID = requested.ID);
|
|
|
|
DELETE wcsl
|
|
FROM dbo.WordCompanionSceneLocations wcsl
|
|
WHERE wcsl.SceneID = @SceneID
|
|
AND NOT EXISTS (SELECT 1 FROM @Locations requested WHERE requested.ID = wcsl.LocationID);
|
|
|
|
INSERT dbo.WordCompanionSceneLocations (SceneID, LocationID)
|
|
SELECT @SceneID, requested.ID
|
|
FROM @Locations requested
|
|
WHERE NOT EXISTS (SELECT 1 FROM dbo.WordCompanionSceneLocations wcsl WHERE wcsl.SceneID = @SceneID AND wcsl.LocationID = requested.ID);
|
|
|
|
SELECT @SceneID AS SceneId,
|
|
(SELECT COUNT(*) FROM @Characters) AS CharactersUpdated,
|
|
(SELECT COUNT(*) FROM @Assets) AS AssetsUpdated,
|
|
(SELECT COUNT(*) FROM @Locations) AS LocationsUpdated,
|
|
N'Scene links updated.' AS Message;
|
|
END;
|
|
GO
|