69 lines
2.0 KiB
Transact-SQL
69 lines
2.0 KiB
Transact-SQL
SET ANSI_NULLS ON;
|
|
GO
|
|
SET QUOTED_IDENTIFIER ON;
|
|
GO
|
|
|
|
CREATE OR ALTER PROCEDURE dbo.ManuscriptDocument_UnlinkBook
|
|
@BookID int,
|
|
@DocumentGuid uniqueidentifier = NULL,
|
|
@UserID int
|
|
AS
|
|
BEGIN
|
|
SET NOCOUNT ON;
|
|
|
|
DECLARE @ManuscriptDocumentID int;
|
|
DECLARE @ChaptersUnlinked int = 0;
|
|
DECLARE @ScenesUnlinked int = 0;
|
|
|
|
SELECT @ManuscriptDocumentID = md.ManuscriptDocumentID
|
|
FROM dbo.ManuscriptDocuments md
|
|
INNER JOIN dbo.Books b ON b.BookID = md.BookID
|
|
INNER JOIN dbo.Projects p ON p.ProjectID = b.ProjectID
|
|
INNER JOIN dbo.ProjectUserAccess pua ON pua.ProjectID = p.ProjectID
|
|
WHERE md.BookID = @BookID
|
|
AND md.IsActive = 1
|
|
AND b.IsArchived = 0
|
|
AND p.IsArchived = 0
|
|
AND pua.UserID = @UserID
|
|
AND pua.IsActive = 1
|
|
AND (@DocumentGuid IS NULL OR @DocumentGuid = '00000000-0000-0000-0000-000000000000' OR md.DocumentGuid = @DocumentGuid);
|
|
|
|
IF @ManuscriptDocumentID IS NULL
|
|
RETURN;
|
|
|
|
UPDATE dbo.Chapters
|
|
SET IsLinkedToManuscript = 0,
|
|
ManuscriptDocumentID = NULL,
|
|
ExternalReference = NULL,
|
|
LinkedUtc = NULL,
|
|
UpdatedDate = SYSUTCDATETIME()
|
|
WHERE BookID = @BookID
|
|
AND ManuscriptDocumentID = @ManuscriptDocumentID;
|
|
|
|
SET @ChaptersUnlinked = @@ROWCOUNT;
|
|
|
|
UPDATE s
|
|
SET IsLinkedToManuscript = 0,
|
|
ManuscriptDocumentID = NULL,
|
|
ExternalReference = NULL,
|
|
LinkedUtc = NULL,
|
|
UpdatedDate = SYSUTCDATETIME()
|
|
FROM dbo.Scenes s
|
|
INNER JOIN dbo.Chapters c ON c.ChapterID = s.ChapterID
|
|
WHERE c.BookID = @BookID
|
|
AND s.ManuscriptDocumentID = @ManuscriptDocumentID;
|
|
|
|
SET @ScenesUnlinked = @@ROWCOUNT;
|
|
|
|
UPDATE dbo.ManuscriptDocuments
|
|
SET IsActive = 0
|
|
WHERE ManuscriptDocumentID = @ManuscriptDocumentID;
|
|
|
|
SELECT @BookID AS BookID,
|
|
@ManuscriptDocumentID AS ManuscriptDocumentID,
|
|
@ChaptersUnlinked AS ChaptersUnlinked,
|
|
@ScenesUnlinked AS ScenesUnlinked,
|
|
N'Manuscript unlinked. Chapters, scenes, and story data were kept.' AS Message;
|
|
END;
|
|
GO
|