282 lines
9.2 KiB
Transact-SQL
282 lines
9.2 KiB
Transact-SQL
SET ANSI_NULLS ON;
|
|
GO
|
|
SET QUOTED_IDENTIFIER ON;
|
|
GO
|
|
|
|
CREATE OR ALTER PROCEDURE dbo.SceneCharacterSuggestions_ListByScene
|
|
@SceneID int,
|
|
@UserID int
|
|
AS
|
|
BEGIN
|
|
SET NOCOUNT ON;
|
|
|
|
SELECT suggestion.SceneCharacterSuggestionID,
|
|
suggestion.SceneID,
|
|
suggestion.CharacterID,
|
|
ch.CharacterName,
|
|
suggestion.Status,
|
|
suggestion.Confidence,
|
|
suggestion.Reason,
|
|
suggestion.DetectedUtc
|
|
FROM dbo.SceneCharacterSuggestions suggestion
|
|
INNER JOIN dbo.Scenes s ON s.SceneID = suggestion.SceneID
|
|
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
|
|
INNER JOIN dbo.Characters ch ON ch.CharacterID = suggestion.CharacterID
|
|
WHERE suggestion.SceneID = @SceneID
|
|
AND suggestion.Status = N'Pending'
|
|
AND s.IsArchived = 0
|
|
AND c.IsArchived = 0
|
|
AND b.IsArchived = 0
|
|
AND p.IsArchived = 0
|
|
AND ch.IsArchived = 0
|
|
AND pua.UserID = @UserID
|
|
AND pua.IsActive = 1
|
|
ORDER BY
|
|
CASE suggestion.Confidence WHEN N'High' THEN 0 WHEN N'Medium' THEN 1 ELSE 2 END,
|
|
ch.CharacterName;
|
|
END;
|
|
GO
|
|
|
|
CREATE OR ALTER PROCEDURE dbo.SceneCharacterSuggestion_Accept
|
|
@SceneCharacterSuggestionID int,
|
|
@UserID int
|
|
AS
|
|
BEGIN
|
|
SET NOCOUNT ON;
|
|
|
|
DECLARE @SceneID int;
|
|
DECLARE @CharacterID int;
|
|
DECLARE @SceneCharacterCreated bit = 0;
|
|
|
|
SELECT @SceneID = suggestion.SceneID,
|
|
@CharacterID = suggestion.CharacterID
|
|
FROM dbo.SceneCharacterSuggestions suggestion
|
|
INNER JOIN dbo.Scenes s ON s.SceneID = suggestion.SceneID
|
|
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
|
|
INNER JOIN dbo.Characters ch ON ch.CharacterID = suggestion.CharacterID AND ch.ProjectID = p.ProjectID
|
|
WHERE suggestion.SceneCharacterSuggestionID = @SceneCharacterSuggestionID
|
|
AND suggestion.Status = N'Pending'
|
|
AND s.IsArchived = 0
|
|
AND c.IsArchived = 0
|
|
AND b.IsArchived = 0
|
|
AND p.IsArchived = 0
|
|
AND ch.IsArchived = 0
|
|
AND pua.UserID = @UserID
|
|
AND pua.IsActive = 1;
|
|
|
|
IF @SceneID IS NULL OR @CharacterID IS NULL
|
|
RETURN;
|
|
|
|
IF NOT EXISTS (SELECT 1 FROM dbo.SceneCharacters WHERE SceneID = @SceneID AND CharacterID = @CharacterID)
|
|
BEGIN
|
|
INSERT dbo.SceneCharacters (SceneID, CharacterID)
|
|
VALUES (@SceneID, @CharacterID);
|
|
|
|
SET @SceneCharacterCreated = 1;
|
|
END;
|
|
|
|
UPDATE dbo.SceneCharacterSuggestions
|
|
SET Status = N'Accepted',
|
|
ReviewedByUserID = @UserID,
|
|
ReviewedUtc = SYSUTCDATETIME()
|
|
WHERE SceneCharacterSuggestionID = @SceneCharacterSuggestionID
|
|
AND Status = N'Pending';
|
|
|
|
SELECT @SceneCharacterSuggestionID AS SceneCharacterSuggestionID,
|
|
@SceneID AS SceneID,
|
|
@CharacterID AS CharacterID,
|
|
N'Accepted' AS Status,
|
|
@SceneCharacterCreated AS SceneCharacterCreated;
|
|
END;
|
|
GO
|
|
|
|
CREATE OR ALTER PROCEDURE dbo.SceneCharacterSuggestion_Reject
|
|
@SceneCharacterSuggestionID int,
|
|
@UserID int
|
|
AS
|
|
BEGIN
|
|
SET NOCOUNT ON;
|
|
|
|
DECLARE @SceneID int;
|
|
DECLARE @CharacterID int;
|
|
|
|
SELECT @SceneID = suggestion.SceneID,
|
|
@CharacterID = suggestion.CharacterID
|
|
FROM dbo.SceneCharacterSuggestions suggestion
|
|
INNER JOIN dbo.Scenes s ON s.SceneID = suggestion.SceneID
|
|
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
|
|
INNER JOIN dbo.Characters ch ON ch.CharacterID = suggestion.CharacterID AND ch.ProjectID = p.ProjectID
|
|
WHERE suggestion.SceneCharacterSuggestionID = @SceneCharacterSuggestionID
|
|
AND suggestion.Status = N'Pending'
|
|
AND s.IsArchived = 0
|
|
AND c.IsArchived = 0
|
|
AND b.IsArchived = 0
|
|
AND p.IsArchived = 0
|
|
AND ch.IsArchived = 0
|
|
AND pua.UserID = @UserID
|
|
AND pua.IsActive = 1;
|
|
|
|
IF @SceneID IS NULL OR @CharacterID IS NULL
|
|
RETURN;
|
|
|
|
UPDATE dbo.SceneCharacterSuggestions
|
|
SET Status = N'Rejected',
|
|
ReviewedByUserID = @UserID,
|
|
ReviewedUtc = SYSUTCDATETIME()
|
|
WHERE SceneCharacterSuggestionID = @SceneCharacterSuggestionID
|
|
AND Status = N'Pending';
|
|
|
|
SELECT @SceneCharacterSuggestionID AS SceneCharacterSuggestionID,
|
|
@SceneID AS SceneID,
|
|
@CharacterID AS CharacterID,
|
|
N'Rejected' AS Status,
|
|
CAST(0 AS bit) AS SceneCharacterCreated;
|
|
END;
|
|
GO
|
|
|
|
CREATE OR ALTER PROCEDURE dbo.SceneCharacterSuggestions_CountByChapter
|
|
@ChapterID int,
|
|
@UserID int
|
|
AS
|
|
BEGIN
|
|
SET NOCOUNT ON;
|
|
|
|
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 c.ChapterID = @ChapterID
|
|
AND c.IsArchived = 0
|
|
AND b.IsArchived = 0
|
|
AND p.IsArchived = 0
|
|
AND pua.UserID = @UserID
|
|
AND pua.IsActive = 1
|
|
)
|
|
RETURN;
|
|
|
|
SELECT s.SceneID,
|
|
COUNT(suggestion.SceneCharacterSuggestionID) AS PendingSuggestionCount
|
|
FROM dbo.Scenes s
|
|
INNER JOIN dbo.SceneCharacterSuggestions suggestion ON suggestion.SceneID = s.SceneID
|
|
INNER JOIN dbo.Characters ch ON ch.CharacterID = suggestion.CharacterID
|
|
WHERE s.ChapterID = @ChapterID
|
|
AND s.IsArchived = 0
|
|
AND ch.IsArchived = 0
|
|
AND suggestion.Status = N'Pending'
|
|
GROUP BY s.SceneID;
|
|
END;
|
|
GO
|
|
|
|
CREATE OR ALTER PROCEDURE dbo.WordCompanion_Runtime_CharacterSuggestions_Create
|
|
@UserID int,
|
|
@DocumentGuid uniqueidentifier,
|
|
@SceneID int,
|
|
@CharacterIds nvarchar(max)
|
|
AS
|
|
BEGIN
|
|
SET NOCOUNT ON;
|
|
|
|
IF @DocumentGuid IS NULL
|
|
OR @DocumentGuid = '00000000-0000-0000-0000-000000000000'
|
|
OR @SceneID <= 0
|
|
RETURN;
|
|
|
|
DECLARE @ProjectID int;
|
|
DECLARE @ManuscriptDocumentID int;
|
|
|
|
SELECT @ProjectID = b.ProjectID,
|
|
@ManuscriptDocumentID = md.ManuscriptDocumentID
|
|
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.ManuscriptDocuments md ON md.BookID = b.BookID
|
|
INNER JOIN dbo.ProjectUserAccess pua ON pua.ProjectID = b.ProjectID
|
|
WHERE s.SceneID = @SceneID
|
|
AND s.IsArchived = 0
|
|
AND c.IsArchived = 0
|
|
AND b.IsArchived = 0
|
|
AND p.IsArchived = 0
|
|
AND md.DocumentGuid = @DocumentGuid
|
|
AND md.IsActive = 1
|
|
AND s.IsLinkedToManuscript = 1
|
|
AND s.ManuscriptDocumentID = md.ManuscriptDocumentID
|
|
AND pua.UserID = @UserID
|
|
AND pua.IsActive = 1;
|
|
|
|
IF @ProjectID IS NULL
|
|
RETURN;
|
|
|
|
DECLARE @Requested table (CharacterID int NOT NULL PRIMARY KEY);
|
|
|
|
INSERT @Requested (CharacterID)
|
|
SELECT DISTINCT TRY_CONVERT(int, LTRIM(RTRIM(value)))
|
|
FROM STRING_SPLIT(COALESCE(@CharacterIds, N''), N',')
|
|
WHERE TRY_CONVERT(int, LTRIM(RTRIM(value))) IS NOT NULL
|
|
AND TRY_CONVERT(int, LTRIM(RTRIM(value))) > 0;
|
|
|
|
IF NOT EXISTS (SELECT 1 FROM @Requested)
|
|
BEGIN
|
|
SELECT @SceneID AS SceneId,
|
|
0 AS CreatedCount,
|
|
0 AS SkippedCount,
|
|
N'No character suggestions detected.' AS Message;
|
|
RETURN;
|
|
END;
|
|
|
|
IF EXISTS
|
|
(
|
|
SELECT 1
|
|
FROM @Requested requested
|
|
WHERE NOT EXISTS
|
|
(
|
|
SELECT 1
|
|
FROM dbo.Characters ch
|
|
WHERE ch.CharacterID = requested.CharacterID
|
|
AND ch.ProjectID = @ProjectID
|
|
AND ch.IsArchived = 0
|
|
)
|
|
)
|
|
RETURN;
|
|
|
|
DECLARE @Inserted table (CharacterID int NOT NULL PRIMARY KEY);
|
|
|
|
INSERT dbo.SceneCharacterSuggestions
|
|
(SceneID, CharacterID, SuggestionSource, Status, Confidence, Reason, CreatedByUserID)
|
|
OUTPUT inserted.CharacterID INTO @Inserted
|
|
SELECT @SceneID,
|
|
requested.CharacterID,
|
|
N'Word Companion',
|
|
N'Pending',
|
|
N'High',
|
|
N'Detected in manuscript text',
|
|
@UserID
|
|
FROM @Requested requested
|
|
WHERE NOT EXISTS
|
|
(SELECT 1 FROM dbo.SceneCharacters sc WHERE sc.SceneID = @SceneID AND sc.CharacterID = requested.CharacterID)
|
|
AND NOT EXISTS
|
|
(SELECT 1 FROM dbo.SceneCharacterSuggestions suggestion WHERE suggestion.SceneID = @SceneID AND suggestion.CharacterID = requested.CharacterID);
|
|
|
|
SELECT @SceneID AS SceneId,
|
|
(SELECT COUNT(*) FROM @Inserted) AS CreatedCount,
|
|
(SELECT COUNT(*) FROM @Requested) - (SELECT COUNT(*) FROM @Inserted) AS SkippedCount,
|
|
CASE
|
|
WHEN (SELECT COUNT(*) FROM @Inserted) = 1 THEN N'1 character suggestion detected.'
|
|
WHEN (SELECT COUNT(*) FROM @Inserted) > 1 THEN CONCAT((SELECT COUNT(*) FROM @Inserted), N' character suggestions detected.')
|
|
ELSE N'No new character suggestions.'
|
|
END AS Message;
|
|
END;
|
|
GO
|