86 lines
2.6 KiB
Transact-SQL
86 lines
2.6 KiB
Transact-SQL
SET ANSI_NULLS ON;
|
|
GO
|
|
SET QUOTED_IDENTIFIER ON;
|
|
GO
|
|
|
|
CREATE OR ALTER PROCEDURE dbo.WordCompanion_Scene_Create
|
|
@BookID int,
|
|
@ChapterID int,
|
|
@UserID int,
|
|
@SceneTitle nvarchar(200)
|
|
AS
|
|
BEGIN
|
|
SET NOCOUNT ON;
|
|
|
|
DECLARE @TrimmedSceneTitle nvarchar(200) = LTRIM(RTRIM(COALESCE(@SceneTitle, N'')));
|
|
IF NULLIF(@TrimmedSceneTitle, N'') IS NULL
|
|
RETURN;
|
|
|
|
IF NOT EXISTS
|
|
(
|
|
SELECT 1
|
|
FROM dbo.Chapters c
|
|
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 b.BookID = @BookID
|
|
AND c.ChapterID = @ChapterID
|
|
AND c.IsArchived = 0
|
|
AND b.IsArchived = 0
|
|
AND p.IsArchived = 0
|
|
AND pua.UserID = @UserID
|
|
AND pua.IsActive = 1
|
|
)
|
|
RETURN;
|
|
|
|
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);
|
|
|
|
IF @PlannedRevisionStatusID IS NULL OR @UnknownTimeModeID IS NULL OR @UnknownTimeConfidenceID IS NULL
|
|
RETURN;
|
|
|
|
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;
|
|
DECLARE @SceneID int;
|
|
|
|
INSERT dbo.Scenes
|
|
(
|
|
ChapterID,
|
|
SceneNumber,
|
|
SceneTitle,
|
|
Summary,
|
|
SortOrder,
|
|
TimeModeID,
|
|
TimeConfidenceID,
|
|
ScenePurposeNotes,
|
|
SceneOutcomeNotes,
|
|
RevisionStatusID,
|
|
PrimaryLocationID
|
|
)
|
|
VALUES
|
|
(
|
|
@ChapterID,
|
|
@NextSceneNumber,
|
|
@TrimmedSceneTitle,
|
|
NULL,
|
|
@NextSceneOrder,
|
|
@UnknownTimeModeID,
|
|
@UnknownTimeConfidenceID,
|
|
NULL,
|
|
NULL,
|
|
@PlannedRevisionStatusID,
|
|
NULL
|
|
);
|
|
|
|
SET @SceneID = CAST(SCOPE_IDENTITY() AS int);
|
|
|
|
INSERT dbo.SceneWorkflow (SceneID, DraftStatus, EstimatedWordCount, ActualWordCount, IsBlocked)
|
|
VALUES (@SceneID, N'Planned', 0, 0, 0);
|
|
|
|
SELECT @ChapterID AS ChapterId,
|
|
@SceneID AS SceneId,
|
|
N'Scene created.' AS Message;
|
|
END;
|
|
GO
|