53 lines
1.5 KiB
Transact-SQL
53 lines
1.5 KiB
Transact-SQL
CREATE OR ALTER PROCEDURE dbo.WordCompanion_Scene_UpdateWordCount
|
|
@SceneID int,
|
|
@UserID int,
|
|
@ActualWords int,
|
|
@LastWorkedOn datetime2 = NULL
|
|
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;
|
|
|
|
DECLARE @LastWorkedOnDate date = COALESCE(CONVERT(date, @LastWorkedOn), CONVERT(date, SYSUTCDATETIME()));
|
|
|
|
IF EXISTS (SELECT 1 FROM dbo.SceneWorkflow WHERE SceneID = @SceneID)
|
|
BEGIN
|
|
UPDATE dbo.SceneWorkflow
|
|
SET ActualWordCount = @ActualWords,
|
|
LastWorkedOn = @LastWorkedOnDate,
|
|
UpdatedDate = SYSUTCDATETIME()
|
|
WHERE SceneID = @SceneID;
|
|
END
|
|
ELSE
|
|
BEGIN
|
|
INSERT dbo.SceneWorkflow (SceneID, ActualWordCount, LastWorkedOn)
|
|
VALUES (@SceneID, @ActualWords, @LastWorkedOnDate);
|
|
END;
|
|
|
|
SELECT @SceneID AS SceneId,
|
|
@ActualWords AS ActualWords,
|
|
@LastWorkedOnDate AS LastWorkedOn,
|
|
N'Word count updated.' AS Message;
|
|
END;
|
|
GO
|