103 lines
3.7 KiB
Transact-SQL
103 lines
3.7 KiB
Transact-SQL
SET ANSI_NULLS ON;
|
|
GO
|
|
SET QUOTED_IDENTIFIER ON;
|
|
GO
|
|
|
|
CREATE OR ALTER PROCEDURE dbo.WordCompanion_Scene_Resolve
|
|
@BookID int,
|
|
@UserID int,
|
|
@ChapterTitle nvarchar(200),
|
|
@SceneTitle nvarchar(200)
|
|
AS
|
|
BEGIN
|
|
SET NOCOUNT ON;
|
|
|
|
DECLARE @ChapterTitleSearched nvarchar(200) =
|
|
LTRIM(RTRIM(REPLACE(REPLACE(REPLACE(REPLACE(COALESCE(@ChapterTitle, N''), NCHAR(160), N' '), CHAR(9), N' '), CHAR(13), N' '), CHAR(10), N' ')));
|
|
DECLARE @SceneTitleSearched nvarchar(200) =
|
|
LTRIM(RTRIM(REPLACE(REPLACE(REPLACE(REPLACE(COALESCE(@SceneTitle, N''), NCHAR(160), N' '), CHAR(9), N' '), CHAR(13), N' '), CHAR(10), N' ')));
|
|
|
|
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(0 AS bit) AS ChapterMatched,
|
|
CAST(NULL AS int) AS ChapterId,
|
|
CAST(NULL AS int) AS SceneId,
|
|
CAST(NULL AS nvarchar(50)) AS MatchType,
|
|
@BookID AS BookId,
|
|
@ChapterTitleSearched AS ChapterTitleSearched,
|
|
@SceneTitleSearched AS SceneTitleSearched,
|
|
CAST(0 AS int) AS AvailableChapterCount,
|
|
CAST(0 AS int) AS AvailableSceneCount,
|
|
CAST(0 AS bit) AS BookAccessible;
|
|
RETURN;
|
|
END;
|
|
|
|
DECLARE @AvailableChapterCount int;
|
|
DECLARE @AvailableSceneCount int;
|
|
|
|
SELECT @AvailableChapterCount = COUNT(*)
|
|
FROM dbo.Chapters c
|
|
WHERE c.BookID = @BookID
|
|
AND c.IsArchived = 0;
|
|
|
|
SELECT @AvailableSceneCount = COUNT(*)
|
|
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;
|
|
|
|
DECLARE @ChapterID int;
|
|
DECLARE @SceneID int;
|
|
|
|
SELECT TOP (1) @ChapterID = c.ChapterID
|
|
FROM dbo.Chapters c
|
|
CROSS APPLY
|
|
(
|
|
SELECT LTRIM(RTRIM(REPLACE(REPLACE(REPLACE(REPLACE(c.ChapterTitle, NCHAR(160), N' '), CHAR(9), N' '), CHAR(13), N' '), CHAR(10), N' '))) AS NormalizedChapterTitle
|
|
) chapterMatch
|
|
WHERE c.BookID = @BookID
|
|
AND c.IsArchived = 0
|
|
AND chapterMatch.NormalizedChapterTitle COLLATE SQL_Latin1_General_CP1_CI_AS = @ChapterTitleSearched COLLATE SQL_Latin1_General_CP1_CI_AS
|
|
ORDER BY c.SortOrder, c.ChapterID;
|
|
|
|
IF @ChapterID IS NOT NULL
|
|
BEGIN
|
|
SELECT TOP (1) @SceneID = s.SceneID
|
|
FROM dbo.Scenes s
|
|
CROSS APPLY
|
|
(
|
|
SELECT LTRIM(RTRIM(REPLACE(REPLACE(REPLACE(REPLACE(s.SceneTitle, NCHAR(160), N' '), CHAR(9), N' '), CHAR(13), N' '), CHAR(10), N' '))) AS NormalizedSceneTitle
|
|
) sceneMatch
|
|
WHERE s.ChapterID = @ChapterID
|
|
AND s.IsArchived = 0
|
|
AND sceneMatch.NormalizedSceneTitle COLLATE SQL_Latin1_General_CP1_CI_AS = @SceneTitleSearched COLLATE SQL_Latin1_General_CP1_CI_AS
|
|
ORDER BY s.SortOrder, s.SceneID;
|
|
END;
|
|
|
|
SELECT CAST(CASE WHEN @SceneID IS NULL THEN 0 ELSE 1 END AS bit) AS Matched,
|
|
CAST(CASE WHEN @ChapterID IS NULL THEN 0 ELSE 1 END AS bit) AS ChapterMatched,
|
|
@ChapterID AS ChapterId,
|
|
@SceneID AS SceneId,
|
|
CASE WHEN @SceneID IS NULL THEN CAST(NULL AS nvarchar(50)) ELSE N'ExactTitle' END AS MatchType,
|
|
@BookID AS BookId,
|
|
@ChapterTitleSearched AS ChapterTitleSearched,
|
|
@SceneTitleSearched AS SceneTitleSearched,
|
|
@AvailableChapterCount AS AvailableChapterCount,
|
|
@AvailableSceneCount AS AvailableSceneCount,
|
|
CAST(1 AS bit) AS BookAccessible;
|
|
END;
|
|
GO
|