616 lines
23 KiB
Transact-SQL
616 lines
23 KiB
Transact-SQL
SET ANSI_NULLS ON;
|
|
GO
|
|
SET QUOTED_IDENTIFIER ON;
|
|
GO
|
|
|
|
IF OBJECT_ID(N'dbo.WordCompanionSceneAssets', N'U') IS NULL
|
|
BEGIN
|
|
CREATE TABLE dbo.WordCompanionSceneAssets
|
|
(
|
|
WordCompanionSceneAssetID int IDENTITY(1,1) NOT NULL CONSTRAINT PK_WordCompanionSceneAssets PRIMARY KEY,
|
|
SceneID int NOT NULL,
|
|
StoryAssetID int NOT NULL,
|
|
CreatedDate datetime2 NOT NULL CONSTRAINT DF_WordCompanionSceneAssets_CreatedDate DEFAULT SYSUTCDATETIME(),
|
|
UpdatedDate datetime2 NOT NULL CONSTRAINT DF_WordCompanionSceneAssets_UpdatedDate DEFAULT SYSUTCDATETIME(),
|
|
CONSTRAINT FK_WordCompanionSceneAssets_Scenes FOREIGN KEY (SceneID) REFERENCES dbo.Scenes(SceneID),
|
|
CONSTRAINT FK_WordCompanionSceneAssets_StoryAssets FOREIGN KEY (StoryAssetID) REFERENCES dbo.StoryAssets(StoryAssetID),
|
|
CONSTRAINT UQ_WordCompanionSceneAssets UNIQUE (SceneID, StoryAssetID)
|
|
);
|
|
END;
|
|
GO
|
|
|
|
IF OBJECT_ID(N'dbo.WordCompanionSceneLocations', N'U') IS NULL
|
|
BEGIN
|
|
CREATE TABLE dbo.WordCompanionSceneLocations
|
|
(
|
|
WordCompanionSceneLocationID int IDENTITY(1,1) NOT NULL CONSTRAINT PK_WordCompanionSceneLocations PRIMARY KEY,
|
|
SceneID int NOT NULL,
|
|
LocationID int NOT NULL,
|
|
CreatedDate datetime2 NOT NULL CONSTRAINT DF_WordCompanionSceneLocations_CreatedDate DEFAULT SYSUTCDATETIME(),
|
|
UpdatedDate datetime2 NOT NULL CONSTRAINT DF_WordCompanionSceneLocations_UpdatedDate DEFAULT SYSUTCDATETIME(),
|
|
CONSTRAINT FK_WordCompanionSceneLocations_Scenes FOREIGN KEY (SceneID) REFERENCES dbo.Scenes(SceneID),
|
|
CONSTRAINT FK_WordCompanionSceneLocations_Locations FOREIGN KEY (LocationID) REFERENCES dbo.Locations(LocationID),
|
|
CONSTRAINT UQ_WordCompanionSceneLocations UNIQUE (SceneID, LocationID)
|
|
);
|
|
END;
|
|
GO
|
|
|
|
CREATE OR ALTER PROCEDURE dbo.WordCompanion_Project_List
|
|
@UserID int
|
|
AS
|
|
BEGIN
|
|
SET NOCOUNT ON;
|
|
|
|
SELECT p.ProjectID AS ProjectId, p.ProjectName AS Title
|
|
FROM dbo.Projects p
|
|
INNER JOIN dbo.ProjectUserAccess pua ON pua.ProjectID = p.ProjectID
|
|
WHERE pua.UserID = @UserID
|
|
AND pua.IsActive = 1
|
|
AND p.IsArchived = 0
|
|
ORDER BY p.ProjectName, p.ProjectID;
|
|
END;
|
|
GO
|
|
|
|
CREATE OR ALTER PROCEDURE dbo.WordCompanion_Book_ListByProject
|
|
@ProjectID int,
|
|
@UserID int
|
|
AS
|
|
BEGIN
|
|
SET NOCOUNT ON;
|
|
|
|
IF NOT EXISTS
|
|
(
|
|
SELECT 1
|
|
FROM dbo.ProjectUserAccess pua
|
|
INNER JOIN dbo.Projects p ON p.ProjectID = pua.ProjectID
|
|
WHERE pua.ProjectID = @ProjectID
|
|
AND pua.UserID = @UserID
|
|
AND pua.IsActive = 1
|
|
AND p.IsArchived = 0
|
|
)
|
|
RETURN;
|
|
|
|
SELECT b.BookID AS BookId, b.BookTitle AS Title, b.SortOrder
|
|
FROM dbo.Books b
|
|
WHERE b.ProjectID = @ProjectID
|
|
AND b.IsArchived = 0
|
|
ORDER BY b.SortOrder, b.BookNumber, b.BookTitle, b.BookID;
|
|
END;
|
|
GO
|
|
|
|
CREATE OR ALTER PROCEDURE dbo.WordCompanion_Book_Structure
|
|
@BookID int,
|
|
@UserID int
|
|
AS
|
|
BEGIN
|
|
SET NOCOUNT ON;
|
|
|
|
IF NOT EXISTS
|
|
(
|
|
SELECT 1
|
|
FROM dbo.Books b
|
|
INNER JOIN dbo.Projects p ON p.ProjectID = b.ProjectID
|
|
INNER JOIN dbo.ProjectUserAccess pua ON pua.ProjectID = p.ProjectID
|
|
WHERE b.BookID = @BookID
|
|
AND b.IsArchived = 0
|
|
AND p.IsArchived = 0
|
|
AND pua.UserID = @UserID
|
|
AND pua.IsActive = 1
|
|
)
|
|
RETURN;
|
|
|
|
SELECT @BookID AS BookId;
|
|
|
|
SELECT c.ChapterID AS ChapterId, c.ChapterTitle AS Title, c.SortOrder
|
|
FROM dbo.Chapters c
|
|
WHERE c.BookID = @BookID
|
|
AND c.IsArchived = 0
|
|
ORDER BY c.SortOrder, c.ChapterNumber, c.ChapterID;
|
|
|
|
SELECT s.ChapterID AS ChapterId, s.SceneID AS SceneId, s.SceneTitle AS Title, s.SortOrder,
|
|
rs.StatusName AS RevisionStatus,
|
|
sw.EstimatedWordCount AS EstimatedWords,
|
|
sw.ActualWordCount AS ActualWords
|
|
FROM dbo.Scenes s
|
|
INNER JOIN dbo.Chapters c ON c.ChapterID = s.ChapterID
|
|
INNER JOIN dbo.RevisionStatuses rs ON rs.RevisionStatusID = s.RevisionStatusID
|
|
LEFT JOIN dbo.SceneWorkflow sw ON sw.SceneID = s.SceneID
|
|
WHERE c.BookID = @BookID
|
|
AND c.IsArchived = 0
|
|
AND s.IsArchived = 0
|
|
ORDER BY c.SortOrder, s.SortOrder, s.SceneNumber, s.SceneID;
|
|
END;
|
|
GO
|
|
|
|
CREATE OR ALTER PROCEDURE dbo.WordCompanion_Scene_Resolve
|
|
@BookID int,
|
|
@UserID int,
|
|
@ChapterTitle nvarchar(200),
|
|
@SceneTitle nvarchar(200)
|
|
AS
|
|
BEGIN
|
|
SET NOCOUNT ON;
|
|
|
|
IF NOT EXISTS
|
|
(
|
|
SELECT 1
|
|
FROM dbo.Books b
|
|
INNER JOIN dbo.Projects p ON p.ProjectID = b.ProjectID
|
|
INNER JOIN dbo.ProjectUserAccess pua ON pua.ProjectID = p.ProjectID
|
|
WHERE b.BookID = @BookID
|
|
AND b.IsArchived = 0
|
|
AND p.IsArchived = 0
|
|
AND pua.UserID = @UserID
|
|
AND pua.IsActive = 1
|
|
)
|
|
BEGIN
|
|
SELECT CAST(0 AS bit) AS Matched, CAST(NULL AS int) AS ChapterId, CAST(NULL AS int) AS SceneId, CAST(NULL AS nvarchar(50)) AS MatchType;
|
|
RETURN;
|
|
END;
|
|
|
|
DECLARE @ChapterID int;
|
|
DECLARE @SceneID int;
|
|
|
|
SELECT TOP (1)
|
|
@ChapterID = c.ChapterID,
|
|
@SceneID = s.SceneID
|
|
FROM dbo.Chapters c
|
|
INNER JOIN dbo.Scenes s ON s.ChapterID = c.ChapterID
|
|
WHERE c.BookID = @BookID
|
|
AND c.IsArchived = 0
|
|
AND s.IsArchived = 0
|
|
AND UPPER(LTRIM(RTRIM(c.ChapterTitle))) = UPPER(LTRIM(RTRIM(@ChapterTitle)))
|
|
AND UPPER(LTRIM(RTRIM(s.SceneTitle))) = UPPER(LTRIM(RTRIM(@SceneTitle)))
|
|
ORDER BY c.SortOrder, s.SortOrder, s.SceneID;
|
|
|
|
SELECT CAST(CASE WHEN @SceneID IS NULL THEN 0 ELSE 1 END AS bit) AS Matched,
|
|
@ChapterID AS ChapterId,
|
|
@SceneID AS SceneId,
|
|
CASE WHEN @SceneID IS NULL THEN CAST(NULL AS nvarchar(50)) ELSE N'ExactTitle' END AS MatchType;
|
|
END;
|
|
GO
|
|
|
|
CREATE OR ALTER PROCEDURE dbo.WordCompanion_Scene_Companion
|
|
@SceneID int,
|
|
@UserID int
|
|
AS
|
|
BEGIN
|
|
SET NOCOUNT ON;
|
|
|
|
DECLARE @ProjectID int;
|
|
DECLARE @BookID int;
|
|
|
|
SELECT @ProjectID = b.ProjectID, @BookID = b.BookID
|
|
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;
|
|
|
|
SELECT s.SceneID, c.ChapterID, c.ChapterTitle, s.SceneTitle,
|
|
sw.ActualWordCount AS ActualWords,
|
|
sw.EstimatedWordCount AS EstimatedWords,
|
|
s.ScenePurposeNotes AS WritingBrief,
|
|
COALESCE(sw.IsBlocked, 0) AS Blocked,
|
|
sw.BlockedReason
|
|
FROM dbo.Scenes s
|
|
INNER JOIN dbo.Chapters c ON c.ChapterID = s.ChapterID
|
|
LEFT JOIN dbo.SceneWorkflow sw ON sw.SceneID = s.SceneID
|
|
WHERE s.SceneID = @SceneID;
|
|
|
|
SELECT ch.CharacterID, ch.CharacterName AS Name,
|
|
CAST(CASE WHEN EXISTS (SELECT 1 FROM dbo.SceneCharacters sc WHERE sc.SceneID = @SceneID AND sc.CharacterID = ch.CharacterID) THEN 1 ELSE 0 END AS bit) AS Linked
|
|
FROM dbo.Characters ch
|
|
WHERE ch.ProjectID = @ProjectID
|
|
AND ch.IsArchived = 0
|
|
ORDER BY ch.CharacterName, ch.CharacterID;
|
|
|
|
SELECT sa.StoryAssetID AS AssetId, sa.AssetName AS Name,
|
|
CAST(CASE WHEN EXISTS (SELECT 1 FROM dbo.WordCompanionSceneAssets wcsa WHERE wcsa.SceneID = @SceneID AND wcsa.StoryAssetID = sa.StoryAssetID)
|
|
OR EXISTS (SELECT 1 FROM dbo.AssetEvents ae WHERE ae.SceneID = @SceneID AND ae.StoryAssetID = sa.StoryAssetID)
|
|
OR EXISTS (SELECT 1 FROM dbo.SceneAssetLocations sal WHERE sal.SceneID = @SceneID AND sal.StoryAssetID = sa.StoryAssetID)
|
|
THEN 1 ELSE 0 END AS bit) AS Linked
|
|
FROM dbo.StoryAssets sa
|
|
WHERE sa.ProjectID = @ProjectID
|
|
AND sa.IsArchived = 0
|
|
ORDER BY sa.AssetName, sa.StoryAssetID;
|
|
|
|
SELECT l.LocationID, l.LocationName AS Name,
|
|
CAST(CASE WHEN EXISTS (SELECT 1 FROM dbo.WordCompanionSceneLocations wcsl WHERE wcsl.SceneID = @SceneID AND wcsl.LocationID = l.LocationID)
|
|
OR EXISTS (SELECT 1 FROM dbo.Scenes s WHERE s.SceneID = @SceneID AND s.PrimaryLocationID = l.LocationID)
|
|
OR EXISTS (SELECT 1 FROM dbo.SceneCharacters sc WHERE sc.SceneID = @SceneID AND (sc.LocationID = l.LocationID OR sc.EntryLocationID = l.LocationID OR sc.ExitLocationID = l.LocationID))
|
|
OR EXISTS (SELECT 1 FROM dbo.SceneAssetLocations sal WHERE sal.SceneID = @SceneID AND sal.LocationID = l.LocationID)
|
|
THEN 1 ELSE 0 END AS bit) AS Linked
|
|
FROM dbo.Locations l
|
|
WHERE l.ProjectID = @ProjectID
|
|
AND l.IsArchived = 0
|
|
ORDER BY l.LocationName, l.LocationID;
|
|
END;
|
|
GO
|
|
|
|
CREATE OR ALTER PROCEDURE dbo.WordCompanion_Scene_UpdateWordCount
|
|
@SceneID int,
|
|
@UserID int,
|
|
@ActualWords int
|
|
AS
|
|
BEGIN
|
|
SET NOCOUNT ON;
|
|
|
|
IF @ActualWords < 0
|
|
RETURN;
|
|
|
|
IF NOT EXISTS
|
|
(
|
|
SELECT 1
|
|
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
|
|
)
|
|
RETURN;
|
|
|
|
IF EXISTS (SELECT 1 FROM dbo.SceneWorkflow WHERE SceneID = @SceneID)
|
|
BEGIN
|
|
UPDATE dbo.SceneWorkflow
|
|
SET ActualWordCount = @ActualWords,
|
|
UpdatedDate = SYSUTCDATETIME()
|
|
WHERE SceneID = @SceneID;
|
|
END
|
|
ELSE
|
|
BEGIN
|
|
INSERT dbo.SceneWorkflow (SceneID, ActualWordCount)
|
|
VALUES (@SceneID, @ActualWords);
|
|
END;
|
|
|
|
SELECT @SceneID AS SceneId, @ActualWords AS ActualWords, N'Word count updated.' AS Message;
|
|
END;
|
|
GO
|
|
|
|
CREATE OR ALTER PROCEDURE dbo.WordCompanion_Scene_Sync
|
|
@BookID int,
|
|
@UserID int,
|
|
@ChapterTitle nvarchar(200),
|
|
@ChapterSortOrder int = NULL,
|
|
@SceneTitle nvarchar(200),
|
|
@SceneSortOrder int = NULL,
|
|
@ActualWords int,
|
|
@DocumentId nvarchar(200) = NULL,
|
|
@ContentHash nvarchar(200) = NULL
|
|
AS
|
|
BEGIN
|
|
SET NOCOUNT ON;
|
|
|
|
IF @ActualWords < 0 OR NULLIF(LTRIM(RTRIM(@ChapterTitle)), N'') IS NULL OR NULLIF(LTRIM(RTRIM(@SceneTitle)), N'') IS NULL
|
|
RETURN;
|
|
|
|
DECLARE @ProjectID int;
|
|
SELECT @ProjectID = b.ProjectID
|
|
FROM dbo.Books b
|
|
INNER JOIN dbo.Projects p ON p.ProjectID = b.ProjectID
|
|
INNER JOIN dbo.ProjectUserAccess pua ON pua.ProjectID = p.ProjectID
|
|
WHERE b.BookID = @BookID
|
|
AND b.IsArchived = 0
|
|
AND p.IsArchived = 0
|
|
AND pua.UserID = @UserID
|
|
AND pua.IsActive = 1;
|
|
|
|
IF @ProjectID IS NULL
|
|
RETURN;
|
|
|
|
DECLARE @ChapterID int;
|
|
DECLARE @SceneID int;
|
|
DECLARE @ChapterCreated bit = 0;
|
|
DECLARE @SceneCreated bit = 0;
|
|
DECLARE @PlannedRevisionStatusID int = (SELECT TOP (1) RevisionStatusID FROM dbo.RevisionStatuses WHERE StatusName = N'Planned' ORDER BY SortOrder);
|
|
DECLARE @UnknownTimeModeID int = (SELECT TOP (1) TimeModeID FROM dbo.TimeModes WHERE TimeModeName = N'Unknown' ORDER BY SortOrder);
|
|
DECLARE @UnknownTimeConfidenceID int = (SELECT TOP (1) TimeConfidenceID FROM dbo.TimeConfidences WHERE TimeConfidenceName = N'Unknown' ORDER BY SortOrder);
|
|
|
|
SELECT TOP (1) @ChapterID = ChapterID
|
|
FROM dbo.Chapters
|
|
WHERE BookID = @BookID
|
|
AND IsArchived = 0
|
|
AND UPPER(LTRIM(RTRIM(ChapterTitle))) = UPPER(LTRIM(RTRIM(@ChapterTitle)))
|
|
ORDER BY SortOrder, ChapterID;
|
|
|
|
IF @ChapterID IS NULL
|
|
BEGIN
|
|
DECLARE @NextChapterNumber decimal(9,2) = ISNULL((SELECT MAX(ChapterNumber) FROM dbo.Chapters WHERE BookID = @BookID), 0) + 1;
|
|
DECLARE @NextChapterOrder int = ISNULL((SELECT MAX(SortOrder) FROM dbo.Chapters WHERE BookID = @BookID), 0) + 10;
|
|
INSERT dbo.Chapters (BookID, ChapterNumber, ChapterTitle, SortOrder, RevisionStatusID)
|
|
VALUES (@BookID, @NextChapterNumber, LTRIM(RTRIM(@ChapterTitle)), COALESCE(NULLIF(@ChapterSortOrder, 0), @NextChapterOrder), @PlannedRevisionStatusID);
|
|
SET @ChapterID = CAST(SCOPE_IDENTITY() AS int);
|
|
SET @ChapterCreated = 1;
|
|
END;
|
|
|
|
SELECT TOP (1) @SceneID = SceneID
|
|
FROM dbo.Scenes
|
|
WHERE ChapterID = @ChapterID
|
|
AND IsArchived = 0
|
|
AND UPPER(LTRIM(RTRIM(SceneTitle))) = UPPER(LTRIM(RTRIM(@SceneTitle)))
|
|
ORDER BY SortOrder, SceneID;
|
|
|
|
IF @SceneID IS NULL
|
|
BEGIN
|
|
DECLARE @NextSceneNumber decimal(9,2) = ISNULL((SELECT MAX(SceneNumber) FROM dbo.Scenes WHERE ChapterID = @ChapterID), 0) + 1;
|
|
DECLARE @NextSceneOrder int = ISNULL((SELECT MAX(SortOrder) FROM dbo.Scenes WHERE ChapterID = @ChapterID), 0) + 10;
|
|
INSERT dbo.Scenes (ChapterID, SceneNumber, SceneTitle, SortOrder, TimeModeID, TimeConfidenceID, RevisionStatusID)
|
|
VALUES (@ChapterID, @NextSceneNumber, LTRIM(RTRIM(@SceneTitle)), COALESCE(NULLIF(@SceneSortOrder, 0), @NextSceneOrder), @UnknownTimeModeID, @UnknownTimeConfidenceID, @PlannedRevisionStatusID);
|
|
SET @SceneID = CAST(SCOPE_IDENTITY() AS int);
|
|
SET @SceneCreated = 1;
|
|
END;
|
|
|
|
IF EXISTS (SELECT 1 FROM dbo.SceneWorkflow WHERE SceneID = @SceneID)
|
|
BEGIN
|
|
UPDATE dbo.SceneWorkflow
|
|
SET ActualWordCount = @ActualWords,
|
|
UpdatedDate = SYSUTCDATETIME()
|
|
WHERE SceneID = @SceneID;
|
|
END
|
|
ELSE
|
|
BEGIN
|
|
INSERT dbo.SceneWorkflow (SceneID, ActualWordCount)
|
|
VALUES (@SceneID, @ActualWords);
|
|
END;
|
|
|
|
SELECT @ChapterID AS ChapterId, @SceneID AS SceneId, @ChapterCreated AS ChapterCreated, @SceneCreated AS SceneCreated,
|
|
CAST(1 AS bit) AS ActualWordsUpdated, N'Scene synced successfully.' AS Message;
|
|
END;
|
|
GO
|
|
|
|
CREATE OR ALTER PROCEDURE dbo.WordCompanion_Manuscript_Sync
|
|
@BookID int,
|
|
@UserID int,
|
|
@DocumentId nvarchar(200) = NULL,
|
|
@StructureJson nvarchar(max)
|
|
AS
|
|
BEGIN
|
|
SET NOCOUNT ON;
|
|
|
|
IF ISJSON(@StructureJson) <> 1
|
|
RETURN;
|
|
|
|
IF NOT EXISTS
|
|
(
|
|
SELECT 1
|
|
FROM dbo.Books b
|
|
INNER JOIN dbo.Projects p ON p.ProjectID = b.ProjectID
|
|
INNER JOIN dbo.ProjectUserAccess pua ON pua.ProjectID = p.ProjectID
|
|
WHERE b.BookID = @BookID
|
|
AND b.IsArchived = 0
|
|
AND p.IsArchived = 0
|
|
AND pua.UserID = @UserID
|
|
AND pua.IsActive = 1
|
|
)
|
|
RETURN;
|
|
|
|
DECLARE @ChaptersCreated int = 0;
|
|
DECLARE @ChaptersUpdated int = 0;
|
|
DECLARE @ScenesCreated int = 0;
|
|
DECLARE @ScenesUpdated int = 0;
|
|
|
|
DECLARE @PlannedRevisionStatusID int = (SELECT TOP (1) RevisionStatusID FROM dbo.RevisionStatuses WHERE StatusName = N'Planned' ORDER BY SortOrder);
|
|
|
|
DECLARE chapter_cursor CURSOR LOCAL FAST_FORWARD FOR
|
|
SELECT Title, SortOrder, WordCount, ScenesJson
|
|
FROM OPENJSON(@StructureJson)
|
|
WITH
|
|
(
|
|
Title nvarchar(200) '$.Title',
|
|
SortOrder int '$.SortOrder',
|
|
WordCount int '$.WordCount',
|
|
ScenesJson nvarchar(max) '$.Scenes' AS JSON
|
|
)
|
|
WHERE NULLIF(LTRIM(RTRIM(Title)), N'') IS NOT NULL
|
|
AND ISNULL(WordCount, 0) >= 0;
|
|
|
|
DECLARE @ChapterTitle nvarchar(200);
|
|
DECLARE @ChapterSortOrder int;
|
|
DECLARE @ChapterWordCount int;
|
|
DECLARE @ScenesJson nvarchar(max);
|
|
|
|
OPEN chapter_cursor;
|
|
FETCH NEXT FROM chapter_cursor INTO @ChapterTitle, @ChapterSortOrder, @ChapterWordCount, @ScenesJson;
|
|
WHILE @@FETCH_STATUS = 0
|
|
BEGIN
|
|
DECLARE @ChapterID int = NULL;
|
|
DECLARE @BeforeChapterCount int;
|
|
|
|
SELECT @BeforeChapterCount = COUNT(*) FROM dbo.Chapters WHERE BookID = @BookID AND IsArchived = 0;
|
|
|
|
SELECT TOP (1) @ChapterID = ChapterID
|
|
FROM dbo.Chapters
|
|
WHERE BookID = @BookID
|
|
AND IsArchived = 0
|
|
AND UPPER(LTRIM(RTRIM(ChapterTitle))) = UPPER(LTRIM(RTRIM(@ChapterTitle)))
|
|
ORDER BY SortOrder, ChapterID;
|
|
|
|
IF @ChapterID IS NULL
|
|
BEGIN
|
|
DECLARE @NextChapterNumber decimal(9,2) = ISNULL((SELECT MAX(ChapterNumber) FROM dbo.Chapters WHERE BookID = @BookID), 0) + 1;
|
|
DECLARE @NextChapterOrder int = ISNULL((SELECT MAX(SortOrder) FROM dbo.Chapters WHERE BookID = @BookID), 0) + 10;
|
|
INSERT dbo.Chapters (BookID, ChapterNumber, ChapterTitle, SortOrder, RevisionStatusID)
|
|
VALUES (@BookID, @NextChapterNumber, LTRIM(RTRIM(@ChapterTitle)), COALESCE(NULLIF(@ChapterSortOrder, 0), @NextChapterOrder), @PlannedRevisionStatusID);
|
|
SET @ChapterID = CAST(SCOPE_IDENTITY() AS int);
|
|
SET @ChaptersCreated += 1;
|
|
END
|
|
ELSE
|
|
BEGIN
|
|
SET @ChaptersUpdated += 1;
|
|
END;
|
|
|
|
DECLARE scene_cursor CURSOR LOCAL FAST_FORWARD FOR
|
|
SELECT Title, SortOrder, WordCount, ContentHash
|
|
FROM OPENJSON(ISNULL(@ScenesJson, N'[]'))
|
|
WITH
|
|
(
|
|
Title nvarchar(200) '$.Title',
|
|
SortOrder int '$.SortOrder',
|
|
WordCount int '$.WordCount',
|
|
ContentHash nvarchar(200) '$.ContentHash'
|
|
)
|
|
WHERE NULLIF(LTRIM(RTRIM(Title)), N'') IS NOT NULL
|
|
AND ISNULL(WordCount, 0) >= 0;
|
|
|
|
DECLARE @SceneTitle nvarchar(200);
|
|
DECLARE @SceneSortOrder int;
|
|
DECLARE @SceneWordCount int;
|
|
DECLARE @ContentHash nvarchar(200);
|
|
|
|
OPEN scene_cursor;
|
|
FETCH NEXT FROM scene_cursor INTO @SceneTitle, @SceneSortOrder, @SceneWordCount, @ContentHash;
|
|
WHILE @@FETCH_STATUS = 0
|
|
BEGIN
|
|
DECLARE @SceneResponse TABLE (ChapterId int, SceneId int, ChapterCreated bit, SceneCreated bit, ActualWordsUpdated bit, Message nvarchar(200));
|
|
INSERT @SceneResponse
|
|
EXEC dbo.WordCompanion_Scene_Sync
|
|
@BookID = @BookID,
|
|
@UserID = @UserID,
|
|
@ChapterTitle = @ChapterTitle,
|
|
@ChapterSortOrder = @ChapterSortOrder,
|
|
@SceneTitle = @SceneTitle,
|
|
@SceneSortOrder = @SceneSortOrder,
|
|
@ActualWords = @SceneWordCount,
|
|
@DocumentId = @DocumentId,
|
|
@ContentHash = @ContentHash;
|
|
|
|
IF EXISTS (SELECT 1 FROM @SceneResponse WHERE SceneCreated = 1)
|
|
SET @ScenesCreated += 1;
|
|
ELSE
|
|
SET @ScenesUpdated += 1;
|
|
|
|
FETCH NEXT FROM scene_cursor INTO @SceneTitle, @SceneSortOrder, @SceneWordCount, @ContentHash;
|
|
END;
|
|
CLOSE scene_cursor;
|
|
DEALLOCATE scene_cursor;
|
|
|
|
FETCH NEXT FROM chapter_cursor INTO @ChapterTitle, @ChapterSortOrder, @ChapterWordCount, @ScenesJson;
|
|
END;
|
|
CLOSE chapter_cursor;
|
|
DEALLOCATE chapter_cursor;
|
|
|
|
SELECT @ChaptersCreated AS ChaptersCreated, @ChaptersUpdated AS ChaptersUpdated,
|
|
@ScenesCreated AS ScenesCreated, @ScenesUpdated AS ScenesUpdated,
|
|
N'Manuscript sync completed.' AS Message;
|
|
END;
|
|
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, value)
|
|
FROM STRING_SPLIT(ISNULL(@CharacterIds, N''), N',')
|
|
WHERE TRY_CONVERT(int, value) IS NOT NULL;
|
|
|
|
INSERT @Assets (ID)
|
|
SELECT DISTINCT TRY_CONVERT(int, value)
|
|
FROM STRING_SPLIT(ISNULL(@AssetIds, N''), N',')
|
|
WHERE TRY_CONVERT(int, value) IS NOT NULL;
|
|
|
|
INSERT @Locations (ID)
|
|
SELECT DISTINCT TRY_CONVERT(int, value)
|
|
FROM STRING_SPLIT(ISNULL(@LocationIds, N''), N',')
|
|
WHERE TRY_CONVERT(int, value) IS NOT NULL;
|
|
|
|
IF EXISTS (SELECT 1 FROM @Characters requested WHERE 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 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 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
|