59 lines
1.7 KiB
Transact-SQL
59 lines
1.7 KiB
Transact-SQL
CREATE OR ALTER PROCEDURE dbo.Scene_MoveRelative
|
|
@SceneID int,
|
|
@AnchorSceneID int,
|
|
@Position nvarchar(10)
|
|
AS
|
|
BEGIN
|
|
SET NOCOUNT ON;
|
|
|
|
IF @SceneID = @AnchorSceneID
|
|
RETURN;
|
|
|
|
DECLARE @SourceChapterID int = (SELECT ChapterID FROM dbo.Scenes WHERE SceneID = @SceneID);
|
|
DECLARE @TargetChapterID int;
|
|
DECLARE @AnchorSortOrder int;
|
|
DECLARE @NewSortOrder int;
|
|
|
|
SELECT @TargetChapterID = ChapterID, @AnchorSortOrder = SortOrder
|
|
FROM dbo.Scenes
|
|
WHERE SceneID = @AnchorSceneID AND IsArchived = 0;
|
|
|
|
IF @TargetChapterID IS NULL
|
|
RETURN;
|
|
|
|
IF @Position = N'Before'
|
|
BEGIN
|
|
SELECT @NewSortOrder = (ISNULL(MAX(SortOrder), @AnchorSortOrder - 20) + @AnchorSortOrder) / 2
|
|
FROM dbo.Scenes
|
|
WHERE ChapterID = @TargetChapterID
|
|
AND IsArchived = 0
|
|
AND SceneID NOT IN (@SceneID, @AnchorSceneID)
|
|
AND SortOrder < @AnchorSortOrder;
|
|
|
|
IF @NewSortOrder >= @AnchorSortOrder
|
|
SET @NewSortOrder = @AnchorSortOrder - 5;
|
|
END
|
|
ELSE
|
|
BEGIN
|
|
SELECT @NewSortOrder = (@AnchorSortOrder + ISNULL(MIN(SortOrder), @AnchorSortOrder + 20)) / 2
|
|
FROM dbo.Scenes
|
|
WHERE ChapterID = @TargetChapterID
|
|
AND IsArchived = 0
|
|
AND SceneID NOT IN (@SceneID, @AnchorSceneID)
|
|
AND SortOrder > @AnchorSortOrder;
|
|
|
|
IF @NewSortOrder <= @AnchorSortOrder
|
|
SET @NewSortOrder = @AnchorSortOrder + 5;
|
|
END;
|
|
|
|
UPDATE dbo.Scenes
|
|
SET ChapterID = @TargetChapterID,
|
|
SortOrder = @NewSortOrder,
|
|
UpdatedDate = SYSUTCDATETIME()
|
|
WHERE SceneID = @SceneID;
|
|
|
|
EXEC dbo.Scene_NormalizeSortOrder @SourceChapterID;
|
|
EXEC dbo.Scene_NormalizeSortOrder @TargetChapterID;
|
|
END;
|
|
GO
|