1110 lines
60 KiB
Transact-SQL
1110 lines
60 KiB
Transact-SQL
SET ANSI_NULLS ON;
|
|
GO
|
|
SET QUOTED_IDENTIFIER ON;
|
|
GO
|
|
|
|
CREATE OR ALTER VIEW dbo.NarrativeSceneOrder
|
|
AS
|
|
SELECT p.ProjectID, b.BookID, c.ChapterID, s.SceneID,
|
|
b.SortOrder AS BookSortOrder, c.SortOrder AS ChapterSortOrder, s.SortOrder AS SceneSortOrder,
|
|
ROW_NUMBER() OVER (PARTITION BY p.ProjectID ORDER BY b.SortOrder, b.BookNumber, c.SortOrder, c.ChapterNumber, s.SortOrder, s.SceneNumber, s.SceneID) AS GlobalSceneIndex,
|
|
ROW_NUMBER() OVER (PARTITION BY b.BookID ORDER BY c.SortOrder, c.ChapterNumber, s.SortOrder, s.SceneNumber, s.SceneID) AS BookSceneIndex,
|
|
b.BookTitle, b.Subtitle AS BookSubtitle, b.BookNumber, c.ChapterNumber, c.ChapterTitle, s.SceneNumber, s.SceneTitle,
|
|
s.PrimaryLocationID, s.StartDateTime, s.RelativeTimeText, s.ScenePurposeNotes, s.SceneOutcomeNotes
|
|
FROM dbo.Projects p
|
|
INNER JOIN dbo.Books b ON b.ProjectID = p.ProjectID AND b.IsArchived = 0
|
|
INNER JOIN dbo.Chapters c ON c.BookID = b.BookID AND c.IsArchived = 0
|
|
INNER JOIN dbo.Scenes s ON s.ChapterID = c.ChapterID AND s.IsArchived = 0
|
|
WHERE p.IsArchived = 0;
|
|
GO
|
|
|
|
CREATE OR ALTER PROCEDURE dbo.SceneOption_ListByProject
|
|
@ProjectID int
|
|
AS
|
|
BEGIN
|
|
SET NOCOUNT ON;
|
|
SELECT s.SceneID, b.BookTitle, b.Subtitle AS BookSubtitle, c.ChapterNumber, c.ChapterTitle, s.SceneNumber, s.SceneTitle
|
|
FROM dbo.Scenes s
|
|
INNER JOIN dbo.Chapters c ON c.ChapterID = s.ChapterID
|
|
INNER JOIN dbo.Books b ON b.BookID = c.BookID
|
|
WHERE b.ProjectID = @ProjectID AND b.IsArchived = 0 AND c.IsArchived = 0 AND s.IsArchived = 0
|
|
ORDER BY b.SortOrder, b.BookNumber, c.SortOrder, c.ChapterNumber, s.SortOrder, s.SceneNumber;
|
|
END;
|
|
GO
|
|
|
|
CREATE OR ALTER PROCEDURE dbo.ChapterOption_ListByProject
|
|
@ProjectID int
|
|
AS
|
|
BEGIN
|
|
SET NOCOUNT ON;
|
|
SELECT c.ChapterID, b.BookID, b.BookTitle, b.Subtitle AS BookSubtitle, b.BookNumber, c.ChapterNumber, c.ChapterTitle, c.SortOrder
|
|
FROM dbo.Chapters c
|
|
INNER JOIN dbo.Books b ON b.BookID = c.BookID
|
|
WHERE b.ProjectID = @ProjectID AND b.IsArchived = 0 AND c.IsArchived = 0
|
|
ORDER BY b.SortOrder, b.BookNumber, c.SortOrder, c.ChapterNumber;
|
|
END;
|
|
GO
|
|
|
|
CREATE OR ALTER PROCEDURE dbo.SceneDependency_ListByScene
|
|
@SceneID int
|
|
AS
|
|
BEGIN
|
|
SET NOCOUNT ON;
|
|
SELECT sd.SceneDependencyID, sd.ProjectID, sd.SourceSceneID, sd.TargetSceneID, sd.SceneDependencyTypeID,
|
|
sdt.TypeName AS SceneDependencyTypeName, sd.Description, sd.IsHardDependency, sd.CreatedDate, sd.UpdatedDate, sd.IsArchived,
|
|
sourceOrder.BookID AS SourceBookID, sourceOrder.BookTitle AS SourceBookTitle, sourceOrder.BookSubtitle AS SourceBookSubtitle, sourceOrder.ChapterID AS SourceChapterID,
|
|
sourceOrder.ChapterNumber AS SourceChapterNumber, sourceOrder.SceneNumber AS SourceSceneNumber, sourceOrder.SceneTitle AS SourceSceneTitle,
|
|
targetOrder.BookID AS TargetBookID, targetOrder.BookTitle AS TargetBookTitle, targetOrder.BookSubtitle AS TargetBookSubtitle, targetOrder.ChapterID AS TargetChapterID,
|
|
targetOrder.ChapterNumber AS TargetChapterNumber, targetOrder.SceneNumber AS TargetSceneNumber, targetOrder.SceneTitle AS TargetSceneTitle,
|
|
CASE WHEN sd.TargetSceneID = @SceneID THEN N'DependsOn' ELSE N'Dependent' END AS DependencyDirection
|
|
FROM dbo.SceneDependencies sd
|
|
INNER JOIN dbo.SceneDependencyTypes sdt ON sdt.SceneDependencyTypeID = sd.SceneDependencyTypeID
|
|
INNER JOIN dbo.NarrativeSceneOrder sourceOrder ON sourceOrder.SceneID = sd.SourceSceneID
|
|
INNER JOIN dbo.NarrativeSceneOrder targetOrder ON targetOrder.SceneID = sd.TargetSceneID
|
|
WHERE sd.IsArchived = 0 AND @SceneID IN (sd.SourceSceneID, sd.TargetSceneID)
|
|
ORDER BY DependencyDirection, sourceOrder.GlobalSceneIndex, targetOrder.GlobalSceneIndex;
|
|
END;
|
|
GO
|
|
|
|
CREATE OR ALTER PROCEDURE dbo.SceneDependency_ListByProject
|
|
@ProjectID int,
|
|
@BookID int = NULL
|
|
AS
|
|
BEGIN
|
|
SET NOCOUNT ON;
|
|
SELECT sd.SceneDependencyID, sd.ProjectID, sd.SourceSceneID, sd.TargetSceneID, sd.SceneDependencyTypeID,
|
|
sdt.TypeName AS SceneDependencyTypeName, sd.Description, sd.IsHardDependency, sd.CreatedDate, sd.UpdatedDate, sd.IsArchived,
|
|
sourceOrder.BookID AS SourceBookID, sourceOrder.BookTitle AS SourceBookTitle, sourceOrder.BookSubtitle AS SourceBookSubtitle, sourceOrder.ChapterID AS SourceChapterID,
|
|
sourceOrder.ChapterNumber AS SourceChapterNumber, sourceOrder.SceneNumber AS SourceSceneNumber, sourceOrder.SceneTitle AS SourceSceneTitle,
|
|
targetOrder.BookID AS TargetBookID, targetOrder.BookTitle AS TargetBookTitle, targetOrder.BookSubtitle AS TargetBookSubtitle, targetOrder.ChapterID AS TargetChapterID,
|
|
targetOrder.ChapterNumber AS TargetChapterNumber, targetOrder.SceneNumber AS TargetSceneNumber, targetOrder.SceneTitle AS TargetSceneTitle
|
|
FROM dbo.SceneDependencies sd
|
|
INNER JOIN dbo.SceneDependencyTypes sdt ON sdt.SceneDependencyTypeID = sd.SceneDependencyTypeID
|
|
INNER JOIN dbo.NarrativeSceneOrder sourceOrder ON sourceOrder.SceneID = sd.SourceSceneID
|
|
INNER JOIN dbo.NarrativeSceneOrder targetOrder ON targetOrder.SceneID = sd.TargetSceneID
|
|
WHERE sd.IsArchived = 0 AND sd.ProjectID = @ProjectID AND (@BookID IS NULL OR targetOrder.BookID = @BookID OR sourceOrder.BookID = @BookID)
|
|
ORDER BY sourceOrder.GlobalSceneIndex, targetOrder.GlobalSceneIndex;
|
|
END;
|
|
GO
|
|
|
|
CREATE OR ALTER PROCEDURE dbo.SceneMovePreview_Get
|
|
@SceneID int,
|
|
@TargetChapterID int,
|
|
@Position nvarchar(20) = N'End'
|
|
AS
|
|
BEGIN
|
|
SET NOCOUNT ON;
|
|
SELECT s.SceneID, s.SceneNumber, s.SceneTitle, s.ChapterID AS SourceChapterID, sourceChapter.ChapterNumber AS SourceChapterNumber,
|
|
sourceChapter.ChapterTitle AS SourceChapterTitle, sourceBook.BookID AS SourceBookID, sourceBook.BookTitle AS SourceBookTitle, sourceBook.Subtitle AS SourceBookSubtitle,
|
|
@TargetChapterID AS TargetChapterID, targetChapter.ChapterNumber AS TargetChapterNumber, targetChapter.ChapterTitle AS TargetChapterTitle,
|
|
targetBook.BookID AS TargetBookID, targetBook.BookTitle AS TargetBookTitle, targetBook.Subtitle AS TargetBookSubtitle, @Position AS Position,
|
|
(SELECT COUNT(*) FROM dbo.SceneDependencies WHERE IsArchived = 0 AND @SceneID IN (SourceSceneID, TargetSceneID)) AS DependencyCount,
|
|
(SELECT COUNT(*) FROM dbo.ContinuityWarnings WHERE IsDismissed = 0 AND IsIntentional = 0 AND SceneID = @SceneID) AS ActiveWarningCount
|
|
FROM dbo.Scenes s
|
|
INNER JOIN dbo.Chapters sourceChapter ON sourceChapter.ChapterID = s.ChapterID
|
|
INNER JOIN dbo.Books sourceBook ON sourceBook.BookID = sourceChapter.BookID
|
|
INNER JOIN dbo.Chapters targetChapter ON targetChapter.ChapterID = @TargetChapterID
|
|
INNER JOIN dbo.Books targetBook ON targetBook.BookID = targetChapter.BookID
|
|
WHERE s.SceneID = @SceneID;
|
|
END;
|
|
GO
|
|
|
|
CREATE OR ALTER PROCEDURE dbo.ContinuityWarning_List
|
|
@ProjectID int,
|
|
@BookID int = NULL,
|
|
@SceneID int = NULL,
|
|
@EntityType nvarchar(80) = NULL,
|
|
@EntityID int = NULL,
|
|
@WarningTypeID int = NULL,
|
|
@WarningSeverityID int = NULL,
|
|
@IncludeDismissed bit = 0,
|
|
@IncludeIntentional bit = 0
|
|
AS
|
|
BEGIN
|
|
SET NOCOUNT ON;
|
|
SELECT cw.ContinuityWarningID, cw.ValidationRunID, cw.ProjectID, cw.BookID, b.BookTitle, b.Subtitle AS BookSubtitle, cw.ChapterID, c.ChapterNumber, c.ChapterTitle,
|
|
cw.SceneID, s.SceneNumber, s.SceneTitle, cw.EntityType, cw.EntityID, cw.WarningTypeID, wt.TypeName AS WarningTypeName,
|
|
cw.WarningSeverityID, ws.SeverityName, cw.Message, cw.Details, cw.IsDismissed, cw.IsIntentional, cw.CreatedDate, cw.LastDetectedDate
|
|
FROM dbo.ContinuityWarnings cw
|
|
INNER JOIN dbo.WarningTypes wt ON wt.WarningTypeID = cw.WarningTypeID
|
|
INNER JOIN dbo.WarningSeverities ws ON ws.WarningSeverityID = cw.WarningSeverityID
|
|
LEFT JOIN dbo.Books b ON b.BookID = cw.BookID
|
|
LEFT JOIN dbo.Chapters c ON c.ChapterID = cw.ChapterID
|
|
LEFT JOIN dbo.Scenes s ON s.SceneID = cw.SceneID
|
|
WHERE cw.ProjectID = @ProjectID
|
|
AND (@BookID IS NULL OR cw.BookID = @BookID)
|
|
AND (@SceneID IS NULL OR cw.SceneID = @SceneID)
|
|
AND (@EntityType IS NULL OR cw.EntityType = @EntityType)
|
|
AND (@EntityID IS NULL OR cw.EntityID = @EntityID)
|
|
AND (@WarningTypeID IS NULL OR cw.WarningTypeID = @WarningTypeID)
|
|
AND (@WarningSeverityID IS NULL OR cw.WarningSeverityID = @WarningSeverityID)
|
|
AND (@IncludeDismissed = 1 OR cw.IsDismissed = 0)
|
|
AND (@IncludeIntentional = 1 OR cw.IsIntentional = 0)
|
|
ORDER BY ws.SortOrder DESC, cw.LastDetectedDate DESC, cw.ContinuityWarningID DESC;
|
|
END;
|
|
GO
|
|
|
|
CREATE OR ALTER PROCEDURE dbo.CharacterTimeline_GetByProject
|
|
@ProjectID int,
|
|
@BookID int = NULL
|
|
AS
|
|
BEGIN
|
|
SET NOCOUNT ON;
|
|
|
|
CREATE TABLE #TimelineScenes
|
|
(
|
|
SceneID int NOT NULL PRIMARY KEY,
|
|
SceneNumber decimal(9,2) NOT NULL,
|
|
SceneTitle nvarchar(200) NOT NULL,
|
|
ChapterNumber decimal(9,2) NOT NULL,
|
|
BookTitle nvarchar(200) NOT NULL,
|
|
BookSubtitle nvarchar(200) NULL,
|
|
BookSortOrder int NOT NULL,
|
|
ChapterSortOrder int NOT NULL,
|
|
SceneSortOrder int NOT NULL
|
|
);
|
|
|
|
INSERT #TimelineScenes (SceneID, SceneNumber, SceneTitle, ChapterNumber, BookTitle, BookSubtitle, BookSortOrder, ChapterSortOrder, SceneSortOrder)
|
|
SELECT s.SceneID, s.SceneNumber, s.SceneTitle, ch.ChapterNumber, b.BookTitle, b.Subtitle, b.SortOrder, ch.SortOrder, s.SortOrder
|
|
FROM dbo.Books b
|
|
INNER JOIN dbo.Chapters ch ON ch.BookID = b.BookID
|
|
INNER JOIN dbo.Scenes s ON s.ChapterID = ch.ChapterID
|
|
WHERE b.ProjectID = @ProjectID
|
|
AND b.IsArchived = 0
|
|
AND ch.IsArchived = 0
|
|
AND s.IsArchived = 0
|
|
AND (@BookID IS NULL OR b.BookID = @BookID);
|
|
|
|
CREATE TABLE #TimelineCharacters
|
|
(
|
|
CharacterID int NOT NULL PRIMARY KEY,
|
|
ProjectID int NOT NULL,
|
|
CharacterName nvarchar(200) NOT NULL,
|
|
ShortName nvarchar(100) NULL,
|
|
Sex nvarchar(50) NULL,
|
|
BirthDate date NULL,
|
|
AgeAtSeriesStart int NULL,
|
|
AgeReferenceSceneID int NULL,
|
|
Height nvarchar(50) NULL,
|
|
EyeColour nvarchar(50) NULL,
|
|
CharacterImportance int NULL,
|
|
ShowInQuickAddBar bit NOT NULL,
|
|
DefaultDescription nvarchar(max) NULL,
|
|
CreatedDate datetime2 NOT NULL,
|
|
UpdatedDate datetime2 NOT NULL,
|
|
IsArchived bit NOT NULL
|
|
);
|
|
|
|
INSERT #TimelineCharacters (CharacterID, ProjectID, CharacterName, ShortName, Sex, BirthDate, AgeAtSeriesStart, AgeReferenceSceneID, Height, EyeColour, CharacterImportance, ShowInQuickAddBar, DefaultDescription, CreatedDate, UpdatedDate, IsArchived)
|
|
SELECT CharacterID, ProjectID, CharacterName, ShortName, Sex, BirthDate, AgeAtSeriesStart, AgeReferenceSceneID,
|
|
Height, EyeColour, CharacterImportance, ShowInQuickAddBar, DefaultDescription, CreatedDate, UpdatedDate, IsArchived
|
|
FROM dbo.Characters
|
|
WHERE ProjectID = @ProjectID AND IsArchived = 0;
|
|
|
|
CREATE TABLE #ProjectLocationPaths
|
|
(
|
|
LocationID int NOT NULL PRIMARY KEY,
|
|
LocationName nvarchar(200) NOT NULL,
|
|
LocationPath nvarchar(max) NOT NULL
|
|
);
|
|
|
|
;WITH ProjectLocationTree AS
|
|
(
|
|
SELECT LocationID, LocationName, CAST(LocationName AS nvarchar(max)) AS LocationPath
|
|
FROM dbo.Locations
|
|
WHERE ProjectID = @ProjectID AND ParentLocationID IS NULL AND IsArchived = 0
|
|
|
|
UNION ALL
|
|
|
|
SELECT child.LocationID, child.LocationName, CAST(parent.LocationPath + N' > ' + child.LocationName AS nvarchar(max)) AS LocationPath
|
|
FROM dbo.Locations child
|
|
INNER JOIN ProjectLocationTree parent ON parent.LocationID = child.ParentLocationID
|
|
WHERE child.ProjectID = @ProjectID AND child.IsArchived = 0
|
|
)
|
|
INSERT #ProjectLocationPaths (LocationID, LocationName, LocationPath)
|
|
SELECT LocationID, LocationName, LocationPath
|
|
FROM ProjectLocationTree;
|
|
|
|
SELECT CharacterID, ProjectID, CharacterName, ShortName, Sex, BirthDate, AgeAtSeriesStart, AgeReferenceSceneID,
|
|
Height, EyeColour, CharacterImportance, ShowInQuickAddBar, DefaultDescription, CreatedDate, UpdatedDate, IsArchived
|
|
FROM #TimelineCharacters
|
|
ORDER BY ISNULL(CharacterImportance, 0) DESC, CharacterName;
|
|
|
|
SELECT sc.SceneCharacterID, sc.SceneID, sc.CharacterID, c.ProjectID, c.CharacterName, c.ShortName, c.BirthDate, c.AgeAtSeriesStart,
|
|
sc.RoleInSceneTypeID, role.TypeName AS RoleInSceneTypeName, sc.PresenceTypeID, presence.TypeName AS PresenceTypeName,
|
|
sc.LocationID, location.LocationName, location.LocationPath, sc.EntryLocationID, entry.LocationName AS EntryLocationName,
|
|
sc.ExitLocationID, exitLocation.LocationName AS ExitLocationName, sc.AppearanceNotes, sc.OutfitDescription,
|
|
sc.PhysicalCondition, sc.EmotionalState, sc.KnowledgeNotes, s.SceneNumber, s.SceneTitle, s.ChapterNumber, s.BookTitle, s.BookSubtitle,
|
|
sc.CreatedDate, sc.UpdatedDate
|
|
FROM #TimelineScenes s
|
|
INNER JOIN dbo.SceneCharacters sc ON sc.SceneID = s.SceneID
|
|
INNER JOIN #TimelineCharacters c ON c.CharacterID = sc.CharacterID
|
|
LEFT JOIN dbo.CharacterRoleInSceneTypes role ON role.CharacterRoleInSceneTypeID = sc.RoleInSceneTypeID
|
|
LEFT JOIN dbo.PresenceTypes presence ON presence.PresenceTypeID = sc.PresenceTypeID
|
|
LEFT JOIN #ProjectLocationPaths location ON location.LocationID = sc.LocationID
|
|
LEFT JOIN #ProjectLocationPaths entry ON entry.LocationID = sc.EntryLocationID
|
|
LEFT JOIN #ProjectLocationPaths exitLocation ON exitLocation.LocationID = sc.ExitLocationID
|
|
ORDER BY s.BookSortOrder, s.ChapterSortOrder, s.SceneSortOrder, c.CharacterName;
|
|
END;
|
|
GO
|
|
|
|
CREATE OR ALTER PROCEDURE dbo.CharacterAttributeEvent_ListByCharacter
|
|
@CharacterID int
|
|
AS
|
|
BEGIN
|
|
SET NOCOUNT ON;
|
|
SELECT cae.CharacterAttributeEventID, cae.CharacterID, ch.CharacterName, cae.CharacterAttributeTypeID,
|
|
cat.AttributeName, cae.SceneID, s.SceneNumber, s.SceneTitle, c.ChapterNumber, b.BookTitle, b.Subtitle AS BookSubtitle,
|
|
cae.AttributeValue, cae.Description, cae.CreatedDate, cae.UpdatedDate
|
|
FROM dbo.CharacterAttributeEvents cae
|
|
INNER JOIN dbo.Characters ch ON ch.CharacterID = cae.CharacterID
|
|
INNER JOIN dbo.CharacterAttributeTypes cat ON cat.CharacterAttributeTypeID = cae.CharacterAttributeTypeID
|
|
INNER JOIN dbo.Scenes s ON s.SceneID = cae.SceneID
|
|
INNER JOIN dbo.Chapters c ON c.ChapterID = s.ChapterID
|
|
INNER JOIN dbo.Books b ON b.BookID = c.BookID
|
|
WHERE cae.CharacterID = @CharacterID
|
|
ORDER BY b.SortOrder, c.SortOrder, s.SortOrder, cat.SortOrder;
|
|
END;
|
|
GO
|
|
|
|
CREATE OR ALTER PROCEDURE dbo.CharacterKnowledge_ListByCharacter
|
|
@CharacterID int
|
|
AS
|
|
BEGIN
|
|
SET NOCOUNT ON;
|
|
SELECT ck.CharacterKnowledgeID, ck.CharacterID, ch.CharacterName, ck.StoryAssetID, sa.AssetName,
|
|
ck.PlotThreadID, pt.ThreadTitle, ck.SceneID, s.SceneNumber, s.SceneTitle, c.ChapterNumber, b.BookTitle, b.Subtitle AS BookSubtitle,
|
|
ck.KnowledgeStateID, ks.StateName AS KnowledgeStateName, ck.SourceEventID, ck.Description,
|
|
ck.CreatedDate, ck.UpdatedDate
|
|
FROM dbo.CharacterKnowledge ck
|
|
INNER JOIN dbo.Characters ch ON ch.CharacterID = ck.CharacterID
|
|
INNER JOIN dbo.KnowledgeStates ks ON ks.KnowledgeStateID = ck.KnowledgeStateID
|
|
INNER JOIN dbo.Scenes s ON s.SceneID = ck.SceneID
|
|
INNER JOIN dbo.Chapters c ON c.ChapterID = s.ChapterID
|
|
INNER JOIN dbo.Books b ON b.BookID = c.BookID
|
|
LEFT JOIN dbo.StoryAssets sa ON sa.StoryAssetID = ck.StoryAssetID
|
|
LEFT JOIN dbo.PlotThreads pt ON pt.PlotThreadID = ck.PlotThreadID
|
|
WHERE ck.CharacterID = @CharacterID
|
|
ORDER BY b.SortOrder, c.SortOrder, s.SortOrder, ck.CharacterKnowledgeID;
|
|
END;
|
|
GO
|
|
|
|
CREATE OR ALTER PROCEDURE dbo.Relationship_ListInitialByCharacter
|
|
@CharacterID int
|
|
AS
|
|
BEGIN
|
|
SET NOCOUNT ON;
|
|
|
|
SELECT cr.CharacterRelationshipID, cr.ProjectID, cr.CharacterAID, ca.CharacterName AS CharacterAName,
|
|
cr.CharacterBID, cb.CharacterName AS CharacterBName, cr.RelationshipTypeID, rt.TypeName AS RelationshipTypeName,
|
|
rt.RelationshipCategoryID, rc.CategoryName AS RelationshipCategoryName, COALESCE(rc.SortOrder, 900) AS CategorySortOrder,
|
|
rt.SortOrder AS RelationshipTypeSortOrder,
|
|
cr.IsPermanent, cr.StartSceneID, cr.EndSceneID, cr.IsKnownToReader, cr.Notes,
|
|
cr.IsInitialRelationship, cr.InitialBookID, b.BookTitle AS InitialBookTitle, b.Subtitle AS InitialBookSubtitle, cr.ReaderInitiallyKnows,
|
|
cr.InitialRelationshipStateID, rs.StateName AS InitialRelationshipStateName, cr.InitialIntensity, cr.IsReciprocal,
|
|
cr.CreatedDate, cr.UpdatedDate, cr.IsArchived
|
|
FROM dbo.CharacterRelationships cr
|
|
INNER JOIN dbo.Characters ca ON ca.CharacterID = cr.CharacterAID
|
|
INNER JOIN dbo.Characters cb ON cb.CharacterID = cr.CharacterBID
|
|
INNER JOIN dbo.RelationshipTypes rt ON rt.RelationshipTypeID = cr.RelationshipTypeID
|
|
LEFT JOIN dbo.RelationshipCategories rc ON rc.RelationshipCategoryID = rt.RelationshipCategoryID
|
|
LEFT JOIN dbo.Books b ON b.BookID = cr.InitialBookID
|
|
LEFT JOIN dbo.RelationshipStates rs ON rs.RelationshipStateID = cr.InitialRelationshipStateID
|
|
WHERE cr.IsArchived = 0
|
|
AND cr.IsInitialRelationship = 1
|
|
AND @CharacterID IN (cr.CharacterAID, cr.CharacterBID)
|
|
ORDER BY COALESCE(rc.SortOrder, 900), COALESCE(b.SortOrder, 0), rt.SortOrder, ca.CharacterName, cb.CharacterName;
|
|
END;
|
|
GO
|
|
|
|
CREATE OR ALTER PROCEDURE dbo.Relationship_ListByCharacter
|
|
@CharacterID int
|
|
AS
|
|
BEGIN
|
|
SET NOCOUNT ON;
|
|
|
|
SELECT cr.CharacterRelationshipID, cr.ProjectID, cr.CharacterAID, ca.CharacterName AS CharacterAName,
|
|
cr.CharacterBID, cb.CharacterName AS CharacterBName, cr.RelationshipTypeID, rt.TypeName AS RelationshipTypeName,
|
|
rt.RelationshipCategoryID, rc.CategoryName AS RelationshipCategoryName, COALESCE(rc.SortOrder, 900) AS CategorySortOrder,
|
|
rt.SortOrder AS RelationshipTypeSortOrder,
|
|
cr.IsPermanent, cr.StartSceneID, cr.EndSceneID, cr.IsKnownToReader, cr.Notes,
|
|
cr.IsInitialRelationship, cr.InitialBookID, b.BookTitle AS InitialBookTitle, b.Subtitle AS InitialBookSubtitle, cr.ReaderInitiallyKnows,
|
|
cr.InitialRelationshipStateID, rs.StateName AS InitialRelationshipStateName, cr.InitialIntensity, cr.IsReciprocal,
|
|
cr.CreatedDate, cr.UpdatedDate, cr.IsArchived
|
|
FROM dbo.CharacterRelationships cr
|
|
INNER JOIN dbo.Characters ca ON ca.CharacterID = cr.CharacterAID
|
|
INNER JOIN dbo.Characters cb ON cb.CharacterID = cr.CharacterBID
|
|
INNER JOIN dbo.RelationshipTypes rt ON rt.RelationshipTypeID = cr.RelationshipTypeID
|
|
LEFT JOIN dbo.RelationshipCategories rc ON rc.RelationshipCategoryID = rt.RelationshipCategoryID
|
|
LEFT JOIN dbo.Books b ON b.BookID = cr.InitialBookID
|
|
LEFT JOIN dbo.RelationshipStates rs ON rs.RelationshipStateID = cr.InitialRelationshipStateID
|
|
WHERE cr.IsArchived = 0 AND @CharacterID IN (cr.CharacterAID, cr.CharacterBID)
|
|
ORDER BY COALESCE(rc.SortOrder, 900), ca.CharacterName, cb.CharacterName, rt.SortOrder;
|
|
END;
|
|
GO
|
|
|
|
CREATE OR ALTER PROCEDURE dbo.Relationship_ListByProject
|
|
@ProjectID int
|
|
AS
|
|
BEGIN
|
|
SET NOCOUNT ON;
|
|
|
|
SELECT cr.CharacterRelationshipID, cr.ProjectID, cr.CharacterAID, ca.CharacterName AS CharacterAName,
|
|
cr.CharacterBID, cb.CharacterName AS CharacterBName, cr.RelationshipTypeID, rt.TypeName AS RelationshipTypeName,
|
|
rt.RelationshipCategoryID, rc.CategoryName AS RelationshipCategoryName, COALESCE(rc.SortOrder, 900) AS CategorySortOrder,
|
|
rt.SortOrder AS RelationshipTypeSortOrder,
|
|
cr.IsPermanent, cr.StartSceneID, cr.EndSceneID, cr.IsKnownToReader, cr.Notes,
|
|
cr.IsInitialRelationship, cr.InitialBookID, b.BookTitle AS InitialBookTitle, b.Subtitle AS InitialBookSubtitle, cr.ReaderInitiallyKnows,
|
|
cr.InitialRelationshipStateID, rs.StateName AS InitialRelationshipStateName, cr.InitialIntensity, cr.IsReciprocal,
|
|
cr.CreatedDate, cr.UpdatedDate, cr.IsArchived
|
|
FROM dbo.CharacterRelationships cr
|
|
INNER JOIN dbo.Characters ca ON ca.CharacterID = cr.CharacterAID
|
|
INNER JOIN dbo.Characters cb ON cb.CharacterID = cr.CharacterBID
|
|
INNER JOIN dbo.RelationshipTypes rt ON rt.RelationshipTypeID = cr.RelationshipTypeID
|
|
LEFT JOIN dbo.RelationshipCategories rc ON rc.RelationshipCategoryID = rt.RelationshipCategoryID
|
|
LEFT JOIN dbo.Books b ON b.BookID = cr.InitialBookID
|
|
LEFT JOIN dbo.RelationshipStates rs ON rs.RelationshipStateID = cr.InitialRelationshipStateID
|
|
WHERE cr.ProjectID = @ProjectID AND cr.IsArchived = 0
|
|
ORDER BY COALESCE(rc.SortOrder, 900), ca.CharacterName, cb.CharacterName, rt.SortOrder;
|
|
END;
|
|
GO
|
|
|
|
CREATE OR ALTER PROCEDURE dbo.RelationshipEvent_ListByCharacter
|
|
@CharacterID int
|
|
AS
|
|
BEGIN
|
|
SET NOCOUNT ON;
|
|
|
|
SELECT re.RelationshipEventID, re.CharacterRelationshipID, cr.ProjectID, cr.CharacterAID, ca.CharacterName AS CharacterAName,
|
|
cr.CharacterBID, cb.CharacterName AS CharacterBName, rt.TypeName AS RelationshipTypeName,
|
|
rt.RelationshipCategoryID, rc.CategoryName AS RelationshipCategoryName, COALESCE(rc.SortOrder, 900) AS CategorySortOrder,
|
|
rt.SortOrder AS RelationshipTypeSortOrder,
|
|
cr.IsReciprocal, re.SceneID, s.SceneNumber, s.SceneTitle, ch.ChapterNumber, b.BookTitle, b.Subtitle AS BookSubtitle,
|
|
re.RelationshipStateID, rs.StateName AS RelationshipStateName, re.Intensity, re.IsKnownToOtherCharacter,
|
|
re.Description, re.CreatedDate, re.UpdatedDate
|
|
FROM dbo.RelationshipEvents re
|
|
INNER JOIN dbo.CharacterRelationships cr ON cr.CharacterRelationshipID = re.CharacterRelationshipID
|
|
INNER JOIN dbo.Characters ca ON ca.CharacterID = cr.CharacterAID
|
|
INNER JOIN dbo.Characters cb ON cb.CharacterID = cr.CharacterBID
|
|
INNER JOIN dbo.RelationshipTypes rt ON rt.RelationshipTypeID = cr.RelationshipTypeID
|
|
LEFT JOIN dbo.RelationshipCategories rc ON rc.RelationshipCategoryID = rt.RelationshipCategoryID
|
|
INNER JOIN dbo.RelationshipStates rs ON rs.RelationshipStateID = re.RelationshipStateID
|
|
INNER JOIN dbo.Scenes s ON s.SceneID = re.SceneID
|
|
INNER JOIN dbo.Chapters ch ON ch.ChapterID = s.ChapterID
|
|
INNER JOIN dbo.Books b ON b.BookID = ch.BookID
|
|
WHERE @CharacterID IN (cr.CharacterAID, cr.CharacterBID)
|
|
ORDER BY b.SortOrder, ch.SortOrder, s.SortOrder, re.CreatedDate, re.RelationshipEventID;
|
|
END;
|
|
GO
|
|
|
|
CREATE OR ALTER PROCEDURE dbo.AssetEvent_ListByAsset
|
|
@StoryAssetID int
|
|
AS
|
|
BEGIN
|
|
SET NOCOUNT ON;
|
|
SELECT ae.AssetEventID, ae.StoryAssetID, sa.ProjectID, sa.AssetName, sa.AssetKindID, ak.KindName,
|
|
ae.SceneID, ae.AssetEventTypeID, aet.TypeName AS AssetEventTypeName, aet.MarkerText,
|
|
ae.FromStateID, fs.StateName AS FromStateName, ae.ToStateID, ts.StateName AS ToStateName,
|
|
ae.EventTitle, ae.EventDescription, ae.CreatedDate, ae.UpdatedDate,
|
|
b.BookTitle, b.Subtitle AS BookSubtitle, c.ChapterNumber, s.SceneNumber, s.SceneTitle
|
|
FROM dbo.AssetEvents ae
|
|
INNER JOIN dbo.StoryAssets sa ON sa.StoryAssetID = ae.StoryAssetID
|
|
INNER JOIN dbo.AssetKinds ak ON ak.AssetKindID = sa.AssetKindID
|
|
INNER JOIN dbo.AssetEventTypes aet ON aet.AssetEventTypeID = ae.AssetEventTypeID
|
|
INNER JOIN dbo.Scenes s ON s.SceneID = ae.SceneID
|
|
INNER JOIN dbo.Chapters c ON c.ChapterID = s.ChapterID
|
|
INNER JOIN dbo.Books b ON b.BookID = c.BookID
|
|
LEFT JOIN dbo.AssetStates fs ON fs.AssetStateID = ae.FromStateID
|
|
LEFT JOIN dbo.AssetStates ts ON ts.AssetStateID = ae.ToStateID
|
|
WHERE ae.StoryAssetID = @StoryAssetID
|
|
ORDER BY b.SortOrder, c.SortOrder, s.SortOrder, ae.AssetEventID;
|
|
END;
|
|
GO
|
|
|
|
CREATE OR ALTER PROCEDURE dbo.AssetCustodyEvent_ListByAsset
|
|
@StoryAssetID int
|
|
AS
|
|
BEGIN
|
|
SET NOCOUNT ON;
|
|
SELECT ace.AssetCustodyEventID, ace.StoryAssetID, sa.AssetName, ace.SceneID,
|
|
ace.AssetCustodyEventTypeID, acet.TypeName AS CustodyEventTypeName, ace.Description,
|
|
STRING_AGG(CONCAT(aec.CharacterNameText, N' (', cr.RoleName, N')'), N', ') AS CustodianSummary,
|
|
ace.CreatedDate, ace.UpdatedDate, b.BookTitle, b.Subtitle AS BookSubtitle, c.ChapterNumber, s.SceneNumber, s.SceneTitle
|
|
FROM dbo.AssetCustodyEvents ace
|
|
INNER JOIN dbo.StoryAssets sa ON sa.StoryAssetID = ace.StoryAssetID
|
|
INNER JOIN dbo.AssetCustodyEventTypes acet ON acet.AssetCustodyEventTypeID = ace.AssetCustodyEventTypeID
|
|
INNER JOIN dbo.Scenes s ON s.SceneID = ace.SceneID
|
|
INNER JOIN dbo.Chapters c ON c.ChapterID = s.ChapterID
|
|
INNER JOIN dbo.Books b ON b.BookID = c.BookID
|
|
LEFT JOIN dbo.AssetCustodyEventCharacters aec ON aec.AssetCustodyEventID = ace.AssetCustodyEventID
|
|
LEFT JOIN dbo.CustodyRoles cr ON cr.CustodyRoleID = aec.CustodyRoleID
|
|
WHERE ace.StoryAssetID = @StoryAssetID
|
|
GROUP BY ace.AssetCustodyEventID, ace.StoryAssetID, sa.AssetName, ace.SceneID, ace.AssetCustodyEventTypeID, acet.TypeName, ace.Description, ace.CreatedDate, ace.UpdatedDate, b.BookTitle, b.Subtitle, c.ChapterNumber, s.SceneNumber, s.SceneTitle, b.SortOrder, c.SortOrder, s.SortOrder
|
|
ORDER BY b.SortOrder, c.SortOrder, s.SortOrder, ace.AssetCustodyEventID;
|
|
END;
|
|
GO
|
|
|
|
CREATE OR ALTER PROCEDURE dbo.WriterDashboard_Scenes
|
|
@ProjectID int = NULL,
|
|
@Queue nvarchar(50)
|
|
AS
|
|
BEGIN
|
|
SET NOCOUNT ON;
|
|
|
|
WITH SceneBase AS
|
|
(
|
|
SELECT p.ProjectID, p.ProjectName, b.BookID, b.BookTitle, b.Subtitle AS BookSubtitle, c.ChapterID, c.ChapterNumber, c.ChapterTitle,
|
|
s.SceneID, s.SceneNumber, s.SceneTitle, s.Summary,
|
|
s.RevisionStatusID,
|
|
rs.StatusName AS RevisionStatusName,
|
|
COALESCE(sw.DraftStatus, rs.StatusName, N'Planned') AS DraftStatus,
|
|
sw.Priority,
|
|
COALESCE(sw.IsBlocked, 0) AS IsBlocked,
|
|
sw.BlockedReason,
|
|
COALESCE(sw.ReadyForDraft, 0) AS ReadyForDraft,
|
|
COALESCE(sw.ReadyForRevision, 0) AS ReadyForRevision,
|
|
COALESCE(sw.ReadyForPolish, 0) AS ReadyForPolish,
|
|
b.SortOrder AS BookSortOrder,
|
|
c.SortOrder AS ChapterSortOrder,
|
|
s.SortOrder AS SceneSortOrder
|
|
FROM dbo.Projects p
|
|
INNER JOIN dbo.Books b ON b.ProjectID = p.ProjectID AND b.IsArchived = 0
|
|
INNER JOIN dbo.Chapters c ON c.BookID = b.BookID AND c.IsArchived = 0
|
|
INNER JOIN dbo.Scenes s ON s.ChapterID = c.ChapterID AND s.IsArchived = 0
|
|
INNER JOIN dbo.RevisionStatuses rs ON rs.RevisionStatusID = s.RevisionStatusID
|
|
LEFT JOIN dbo.SceneWorkflow sw ON sw.SceneID = s.SceneID
|
|
WHERE p.IsArchived = 0 AND (@ProjectID IS NULL OR p.ProjectID = @ProjectID)
|
|
)
|
|
SELECT TOP (40) sb.ProjectID, sb.ProjectName, sb.BookID, sb.BookTitle, sb.BookSubtitle, sb.ChapterID, sb.ChapterNumber, sb.ChapterTitle,
|
|
sb.SceneID, sb.SceneNumber, sb.SceneTitle, sb.Summary, sb.RevisionStatusID, sb.RevisionStatusName,
|
|
sb.DraftStatus, sb.Priority, sb.IsBlocked, sb.BlockedReason,
|
|
ISNULL(warnings.WarningCount, 0) AS WarningCount,
|
|
ISNULL(checklist.RemainingChecklistCount, 0) AS RemainingChecklistCount,
|
|
ISNULL(notes.UnresolvedNoteCount, 0) AS UnresolvedNoteCount,
|
|
threads.ImportantThreads,
|
|
pinned.PinnedNotes
|
|
FROM SceneBase sb
|
|
OUTER APPLY
|
|
(
|
|
SELECT COUNT(*) AS WarningCount
|
|
FROM dbo.ContinuityWarnings cw
|
|
WHERE cw.SceneID = sb.SceneID AND cw.IsDismissed = 0
|
|
) warnings
|
|
OUTER APPLY
|
|
(
|
|
SELECT COUNT(*) AS RemainingChecklistCount
|
|
FROM dbo.SceneChecklistItems sci
|
|
WHERE sci.SceneID = sb.SceneID AND sci.IsCompleted = 0
|
|
) checklist
|
|
OUTER APPLY
|
|
(
|
|
SELECT COUNT(*) AS UnresolvedNoteCount
|
|
FROM dbo.SceneNotes sn
|
|
WHERE sn.SceneID = sb.SceneID AND sn.IsResolved = 0
|
|
) notes
|
|
OUTER APPLY
|
|
(
|
|
SELECT STRING_AGG(CONVERT(nvarchar(max), pt.ThreadTitle), N', ') AS ImportantThreads
|
|
FROM dbo.ThreadEvents te
|
|
INNER JOIN dbo.PlotThreads pt ON pt.PlotThreadID = te.PlotThreadID
|
|
WHERE te.SceneID = sb.SceneID AND pt.Importance >= 8 AND pt.IsArchived = 0
|
|
) threads
|
|
OUTER APPLY
|
|
(
|
|
SELECT STRING_AGG(CONVERT(nvarchar(max), COALESCE(sn.NoteTitle, sn.NoteText)), N' | ') AS PinnedNotes
|
|
FROM dbo.SceneNotes sn
|
|
WHERE sn.SceneID = sb.SceneID AND sn.IsPinned = 1 AND sn.IsResolved = 0
|
|
) pinned
|
|
WHERE (@Queue = N'Blocked' AND sb.IsBlocked = 1)
|
|
OR (@Queue = N'ReadyForDraft' AND sb.ReadyForDraft = 1)
|
|
OR (@Queue = N'ReadyForRevision' AND sb.ReadyForRevision = 1)
|
|
OR (@Queue = N'ReadyForPolish' AND sb.ReadyForPolish = 1)
|
|
OR (@Queue = N'Warnings' AND ISNULL(warnings.WarningCount, 0) > 0)
|
|
ORDER BY CASE WHEN sb.IsBlocked = 1 THEN 0 ELSE 1 END,
|
|
ISNULL(sb.Priority, 0) DESC, sb.BookSortOrder, sb.ChapterSortOrder, sb.SceneSortOrder, sb.SceneID;
|
|
END;
|
|
GO
|
|
|
|
CREATE OR ALTER PROCEDURE dbo.Dynamics_CharacterArc
|
|
@CharacterID int
|
|
AS
|
|
BEGIN
|
|
SET NOCOUNT ON;
|
|
|
|
DECLARE @ProjectID int = (SELECT ProjectID FROM dbo.Characters WHERE CharacterID = @CharacterID);
|
|
|
|
SELECT CharacterID, ProjectID, CharacterName, ShortName, Sex, BirthDate, AgeAtSeriesStart, AgeReferenceSceneID,
|
|
Height, EyeColour, DefaultDescription, CreatedDate, UpdatedDate, IsArchived
|
|
FROM dbo.Characters
|
|
WHERE CharacterID = @CharacterID;
|
|
|
|
SELECT sc.SceneID, nso.BookTitle, nso.BookSubtitle, nso.ChapterNumber, nso.SceneNumber, nso.SceneTitle, nso.GlobalSceneIndex,
|
|
CAST(CASE WHEN COALESCE(s.POVCharacterID, c.POVCharacterID) = @CharacterID THEN 1 ELSE 0 END AS bit) AS IsPov,
|
|
MAX(CASE WHEN smt.MetricName = N'Emotional Weight' THEN smv.Value END) AS EmotionalWeight,
|
|
MAX(CASE WHEN smt.MetricName = N'Tension' THEN smv.Value END) AS Tension,
|
|
MAX(CASE WHEN smt.MetricName = N'Romance' THEN smv.Value END) AS Romance,
|
|
MAX(CASE WHEN smt.MetricName = N'Action' THEN smv.Value END) AS Action,
|
|
MAX(CASE WHEN smt.MetricName = N'Darkness' THEN smv.Value END) AS Darkness,
|
|
MAX(CASE WHEN smt.MetricName = N'Hope / Lightness' THEN smv.Value END) AS HopeLightness,
|
|
MAX(CASE WHEN smt.MetricName = N'Overall Intensity' THEN smv.Value END) AS OverallIntensity,
|
|
role.TypeName AS RoleInSceneTypeName,
|
|
presence.TypeName AS PresenceTypeName,
|
|
sc.EmotionalState,
|
|
sc.AppearanceNotes,
|
|
notes.KeyNotes
|
|
FROM dbo.SceneCharacters sc
|
|
INNER JOIN dbo.NarrativeSceneOrder nso ON nso.SceneID = sc.SceneID
|
|
INNER JOIN dbo.Scenes s ON s.SceneID = sc.SceneID
|
|
INNER JOIN dbo.Chapters c ON c.ChapterID = s.ChapterID
|
|
LEFT JOIN dbo.CharacterRoleInSceneTypes role ON role.CharacterRoleInSceneTypeID = sc.RoleInSceneTypeID
|
|
LEFT JOIN dbo.PresenceTypes presence ON presence.PresenceTypeID = sc.PresenceTypeID
|
|
LEFT JOIN dbo.SceneMetricValues smv ON smv.SceneID = sc.SceneID
|
|
LEFT JOIN dbo.SceneMetricTypes smt ON smt.MetricTypeID = smv.MetricTypeID
|
|
OUTER APPLY
|
|
(
|
|
SELECT STRING_AGG(CONVERT(nvarchar(max), COALESCE(sn.NoteTitle, sn.NoteText)), N' | ') AS KeyNotes
|
|
FROM dbo.SceneNotes sn
|
|
INNER JOIN dbo.SceneNoteTypes snt ON snt.SceneNoteTypeID = sn.SceneNoteTypeID
|
|
WHERE sn.SceneID = sc.SceneID AND sn.IsResolved = 0
|
|
AND snt.TypeName IN (N'Emotional Intent', N'Character Reminder', N'Revision Note', N'TODO')
|
|
) notes
|
|
WHERE sc.CharacterID = @CharacterID
|
|
GROUP BY sc.SceneID, nso.BookTitle, nso.BookSubtitle, nso.ChapterNumber, nso.SceneNumber, nso.SceneTitle, nso.GlobalSceneIndex,
|
|
s.POVCharacterID, c.POVCharacterID, role.TypeName, presence.TypeName, sc.EmotionalState, sc.AppearanceNotes, notes.KeyNotes
|
|
ORDER BY nso.GlobalSceneIndex;
|
|
|
|
EXEC dbo.RelationshipEvent_ListByCharacter @CharacterID;
|
|
EXEC dbo.CharacterKnowledge_ListByCharacter @CharacterID;
|
|
|
|
SELECT N'Character Presence' AS Category, ws.SeverityName, cw.Message AS Title, cw.Details AS Detail, cw.SceneID, cw.EntityID, cw.EntityType
|
|
FROM dbo.ContinuityWarnings cw
|
|
INNER JOIN dbo.WarningSeverities ws ON ws.WarningSeverityID = cw.WarningSeverityID
|
|
INNER JOIN dbo.WarningTypes wt ON wt.WarningTypeID = cw.WarningTypeID
|
|
WHERE cw.ProjectID = @ProjectID AND cw.IsDismissed = 0
|
|
AND (
|
|
(cw.EntityType = N'Character' AND cw.EntityID = @CharacterID)
|
|
OR cw.SceneID IN (SELECT SceneID FROM dbo.SceneCharacters WHERE CharacterID = @CharacterID)
|
|
)
|
|
AND wt.TypeName IN (N'Knowledge Continuity', N'Long Character Absence', N'Character Underused', N'Scene Character Overload');
|
|
END;
|
|
GO
|
|
|
|
CREATE OR ALTER PROCEDURE dbo.Dynamics_RelationshipEvolution
|
|
@CharacterRelationshipID int
|
|
AS
|
|
BEGIN
|
|
SET NOCOUNT ON;
|
|
|
|
SELECT cr.CharacterRelationshipID, cr.ProjectID, cr.CharacterAID, ca.CharacterName AS CharacterAName,
|
|
cr.CharacterBID, cb.CharacterName AS CharacterBName, cr.RelationshipTypeID, rt.TypeName AS RelationshipTypeName,
|
|
cr.IsPermanent, cr.StartSceneID, cr.EndSceneID, cr.IsKnownToReader, cr.Notes, cr.CreatedDate, cr.UpdatedDate, cr.IsArchived
|
|
FROM dbo.CharacterRelationships cr
|
|
INNER JOIN dbo.Characters ca ON ca.CharacterID = cr.CharacterAID
|
|
INNER JOIN dbo.Characters cb ON cb.CharacterID = cr.CharacterBID
|
|
INNER JOIN dbo.RelationshipTypes rt ON rt.RelationshipTypeID = cr.RelationshipTypeID
|
|
WHERE cr.CharacterRelationshipID = @CharacterRelationshipID;
|
|
|
|
SELECT re.RelationshipEventID, re.CharacterRelationshipID, re.SceneID, nso.BookTitle, nso.BookSubtitle, nso.ChapterNumber, nso.SceneNumber,
|
|
nso.SceneTitle, nso.GlobalSceneIndex, rs.StateName AS RelationshipStateName, re.Intensity, re.IsKnownToOtherCharacter, re.Description
|
|
FROM dbo.RelationshipEvents re
|
|
INNER JOIN dbo.RelationshipStates rs ON rs.RelationshipStateID = re.RelationshipStateID
|
|
INNER JOIN dbo.NarrativeSceneOrder nso ON nso.SceneID = re.SceneID
|
|
WHERE re.CharacterRelationshipID = @CharacterRelationshipID
|
|
ORDER BY nso.GlobalSceneIndex, re.RelationshipEventID;
|
|
|
|
SELECT N'Relationship Continuity' AS Category, ws.SeverityName, cw.Message AS Title, cw.Details AS Detail, cw.SceneID, cw.EntityID, cw.EntityType
|
|
FROM dbo.ContinuityWarnings cw
|
|
INNER JOIN dbo.WarningSeverities ws ON ws.WarningSeverityID = cw.WarningSeverityID
|
|
INNER JOIN dbo.WarningTypes wt ON wt.WarningTypeID = cw.WarningTypeID
|
|
WHERE cw.IsDismissed = 0 AND cw.EntityType = N'Relationship' AND cw.EntityID = @CharacterRelationshipID
|
|
AND wt.TypeName = N'Abrupt Relationship Change';
|
|
END;
|
|
GO
|
|
|
|
CREATE OR ALTER PROCEDURE dbo.StoryBible_TimelineSummary
|
|
@ProjectID int
|
|
AS
|
|
BEGIN
|
|
SET NOCOUNT ON;
|
|
|
|
SELECT b.BookID,
|
|
b.BookTitle,
|
|
b.Subtitle AS BookSubtitle,
|
|
c.ChapterID,
|
|
c.ChapterNumber,
|
|
c.ChapterTitle,
|
|
s.SceneID,
|
|
s.SceneNumber,
|
|
s.SceneTitle,
|
|
s.Summary,
|
|
pov.CharacterName AS PovCharacterName,
|
|
location.LocationPath,
|
|
rs.StatusName AS RevisionStatusName
|
|
FROM dbo.Books b
|
|
INNER JOIN dbo.Chapters c ON c.BookID = b.BookID AND c.IsArchived = 0
|
|
INNER JOIN dbo.Scenes s ON s.ChapterID = c.ChapterID AND s.IsArchived = 0
|
|
INNER JOIN dbo.RevisionStatuses rs ON rs.RevisionStatusID = s.RevisionStatusID
|
|
LEFT JOIN dbo.Characters pov ON pov.CharacterID = COALESCE(s.POVCharacterID, c.POVCharacterID)
|
|
LEFT JOIN dbo.LocationPaths location ON location.LocationID = s.PrimaryLocationID
|
|
WHERE b.ProjectID = @ProjectID AND b.IsArchived = 0
|
|
ORDER BY b.SortOrder, c.SortOrder, s.SortOrder, s.SceneID;
|
|
END;
|
|
GO
|
|
|
|
CREATE OR ALTER PROCEDURE dbo.Export_SceneBrief
|
|
@SceneID int
|
|
AS
|
|
BEGIN
|
|
SET NOCOUNT ON;
|
|
|
|
SELECT p.ProjectID, p.ProjectName, b.BookID, b.BookTitle, b.Subtitle AS BookSubtitle, c.ChapterID, c.ChapterNumber, c.ChapterTitle,
|
|
s.SceneID, s.SceneNumber, s.SceneTitle, s.Summary, s.ScenePurposeNotes, s.SceneOutcomeNotes,
|
|
rs.StatusName AS RevisionStatusName,
|
|
pov.CharacterName AS PovCharacterName,
|
|
location.LocationPath,
|
|
COALESCE(s.RelativeTimeText, CONVERT(nvarchar(30), s.StartDateTime, 113), N'Time not set') AS TimeLabel,
|
|
COALESCE(sw.DraftStatus, N'Planned') AS DraftStatus,
|
|
COALESCE(sw.IsBlocked, 0) AS IsBlocked,
|
|
sw.BlockedReason,
|
|
sw.Priority
|
|
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.RevisionStatuses rs ON rs.RevisionStatusID = s.RevisionStatusID
|
|
LEFT JOIN dbo.Characters pov ON pov.CharacterID = COALESCE(s.POVCharacterID, c.POVCharacterID)
|
|
LEFT JOIN dbo.LocationPaths location ON location.LocationID = s.PrimaryLocationID
|
|
LEFT JOIN dbo.SceneWorkflow sw ON sw.SceneID = s.SceneID
|
|
WHERE s.SceneID = @SceneID;
|
|
|
|
SELECT N'Purpose' AS ItemType, spt.PurposeName AS Title, CAST(NULL AS nvarchar(max)) AS Detail, CAST(NULL AS nvarchar(100)) AS Status, spt.SortOrder
|
|
FROM dbo.ScenePurposes sp
|
|
INNER JOIN dbo.ScenePurposeTypes spt ON spt.ScenePurposeTypeID = sp.ScenePurposeTypeID
|
|
WHERE sp.SceneID = @SceneID
|
|
UNION ALL
|
|
SELECT N'Character', ch.CharacterName, CONCAT(ISNULL(role.TypeName, N'No role'), N' / ', ISNULL(presence.TypeName, N'No presence'), N'. ', ISNULL(sc.AppearanceNotes, N'')), ISNULL(location.LocationPath, N'No location'), 100
|
|
FROM dbo.SceneCharacters sc
|
|
INNER JOIN dbo.Characters ch ON ch.CharacterID = sc.CharacterID
|
|
LEFT JOIN dbo.CharacterRoleInSceneTypes role ON role.CharacterRoleInSceneTypeID = sc.RoleInSceneTypeID
|
|
LEFT JOIN dbo.PresenceTypes presence ON presence.PresenceTypeID = sc.PresenceTypeID
|
|
LEFT JOIN dbo.LocationPaths location ON location.LocationID = sc.LocationID
|
|
WHERE sc.SceneID = @SceneID
|
|
UNION ALL
|
|
SELECT N'Plot Thread', pt.ThreadTitle, CONCAT(tet.TypeName, N': ', te.EventTitle, N'. ', ISNULL(te.EventDescription, N'')), CAST(pt.Importance AS nvarchar(20)), 200
|
|
FROM dbo.ThreadEvents te
|
|
INNER JOIN dbo.PlotThreads pt ON pt.PlotThreadID = te.PlotThreadID
|
|
INNER JOIN dbo.ThreadEventTypes tet ON tet.ThreadEventTypeID = te.EventTypeID
|
|
WHERE te.SceneID = @SceneID
|
|
UNION ALL
|
|
SELECT N'Asset', sa.AssetName, CONCAT(aet.TypeName, N': ', ae.EventTitle, N'. ', ISNULL(ae.EventDescription, N'')), ast.StateName, 300
|
|
FROM dbo.AssetEvents ae
|
|
INNER JOIN dbo.StoryAssets sa ON sa.StoryAssetID = ae.StoryAssetID
|
|
INNER JOIN dbo.AssetEventTypes aet ON aet.AssetEventTypeID = ae.AssetEventTypeID
|
|
LEFT JOIN dbo.AssetStates ast ON ast.AssetStateID = ae.ToStateID
|
|
WHERE ae.SceneID = @SceneID
|
|
UNION ALL
|
|
SELECT N'Relationship', CONCAT(ca.CharacterName, N' / ', cb.CharacterName), CONCAT(rs.StateName, N'. ', ISNULL(re.Description, N'')), CAST(re.Intensity AS nvarchar(20)), 400
|
|
FROM dbo.RelationshipEvents re
|
|
INNER JOIN dbo.CharacterRelationships cr ON cr.CharacterRelationshipID = re.CharacterRelationshipID
|
|
INNER JOIN dbo.Characters ca ON ca.CharacterID = cr.CharacterAID
|
|
INNER JOIN dbo.Characters cb ON cb.CharacterID = cr.CharacterBID
|
|
INNER JOIN dbo.RelationshipStates rs ON rs.RelationshipStateID = re.RelationshipStateID
|
|
WHERE re.SceneID = @SceneID
|
|
UNION ALL
|
|
SELECT N'Note', COALESCE(sn.NoteTitle, snt.TypeName), sn.NoteText, CASE WHEN sn.IsResolved = 1 THEN N'Resolved' ELSE N'Open' END, 500
|
|
FROM dbo.SceneNotes sn
|
|
INNER JOIN dbo.SceneNoteTypes snt ON snt.SceneNoteTypeID = sn.SceneNoteTypeID
|
|
WHERE sn.SceneID = @SceneID
|
|
UNION ALL
|
|
SELECT N'Attachment', sa.Title, CONCAT(ISNULL(sa.Notes, N''), N' ', ISNULL(sa.ExternalUrl, N''), N' ', ISNULL(sa.FilePath, N'')), sat.TypeName, 600
|
|
FROM dbo.SceneAttachments sa
|
|
INNER JOIN dbo.SceneAttachmentTypes sat ON sat.SceneAttachmentTypeID = sa.SceneAttachmentTypeID
|
|
WHERE sa.SceneID = @SceneID
|
|
UNION ALL
|
|
SELECT N'Checklist', sci.ItemText, CAST(NULL AS nvarchar(max)), CASE WHEN sci.IsCompleted = 1 THEN N'Done' ELSE N'Open' END, 700
|
|
FROM dbo.SceneChecklistItems sci
|
|
WHERE sci.SceneID = @SceneID
|
|
UNION ALL
|
|
SELECT N'Warning', wt.TypeName, cw.Message, ws.SeverityName, 800
|
|
FROM dbo.ContinuityWarnings cw
|
|
INNER JOIN dbo.WarningTypes wt ON wt.WarningTypeID = cw.WarningTypeID
|
|
INNER JOIN dbo.WarningSeverities ws ON ws.WarningSeverityID = cw.WarningSeverityID
|
|
WHERE cw.SceneID = @SceneID AND cw.IsDismissed = 0
|
|
ORDER BY SortOrder, ItemType, Title;
|
|
END;
|
|
GO
|
|
|
|
CREATE OR ALTER PROCEDURE dbo.Export_ChapterBrief
|
|
@ChapterID int
|
|
AS
|
|
BEGIN
|
|
SET NOCOUNT ON;
|
|
|
|
SELECT p.ProjectID, p.ProjectName, b.BookID, b.BookTitle, b.Subtitle AS BookSubtitle, c.ChapterID, c.ChapterNumber, c.ChapterTitle,
|
|
c.Summary, rs.StatusName AS RevisionStatusName, cg.GoalSummary, cg.EmotionalGoal, cg.ReaderTakeaway,
|
|
cg.MustInclude, cg.RisksOrConcerns
|
|
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.RevisionStatuses rs ON rs.RevisionStatusID = c.RevisionStatusID
|
|
LEFT JOIN dbo.ChapterGoals cg ON cg.ChapterID = c.ChapterID
|
|
WHERE c.ChapterID = @ChapterID;
|
|
|
|
SELECT b.BookID, b.BookTitle, b.Subtitle AS BookSubtitle, c.ChapterID, c.ChapterNumber, c.ChapterTitle, s.SceneID, s.SceneNumber,
|
|
s.SceneTitle, s.Summary, pov.CharacterName AS PovCharacterName, location.LocationPath, rs.StatusName AS RevisionStatusName
|
|
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.RevisionStatuses rs ON rs.RevisionStatusID = s.RevisionStatusID
|
|
LEFT JOIN dbo.Characters pov ON pov.CharacterID = COALESCE(s.POVCharacterID, c.POVCharacterID)
|
|
LEFT JOIN dbo.LocationPaths location ON location.LocationID = s.PrimaryLocationID
|
|
WHERE s.ChapterID = @ChapterID AND s.IsArchived = 0
|
|
ORDER BY s.SortOrder, s.SceneID;
|
|
|
|
SELECT N'Warning' AS ItemType, wt.TypeName AS Title, cw.Message AS Detail, ws.SeverityName AS Status, 10 AS SortOrder
|
|
FROM dbo.ContinuityWarnings cw
|
|
INNER JOIN dbo.WarningTypes wt ON wt.WarningTypeID = cw.WarningTypeID
|
|
INNER JOIN dbo.WarningSeverities ws ON ws.WarningSeverityID = cw.WarningSeverityID
|
|
WHERE cw.ChapterID = @ChapterID AND cw.IsDismissed = 0
|
|
UNION ALL
|
|
SELECT N'Open Thread', pt.ThreadTitle, pt.Summary, ts.StatusName, 20
|
|
FROM dbo.ThreadEvents te
|
|
INNER JOIN dbo.Scenes s ON s.SceneID = te.SceneID
|
|
INNER JOIN dbo.PlotThreads pt ON pt.PlotThreadID = te.PlotThreadID
|
|
INNER JOIN dbo.ThreadStatuses ts ON ts.ThreadStatusID = pt.ThreadStatusID
|
|
WHERE s.ChapterID = @ChapterID AND pt.IsArchived = 0 AND ts.IsOpenStatus = 1
|
|
UNION ALL
|
|
SELECT N'Open Asset', sa.AssetName, sa.Description, ast.StateName, 30
|
|
FROM dbo.AssetEvents ae
|
|
INNER JOIN dbo.Scenes s ON s.SceneID = ae.SceneID
|
|
INNER JOIN dbo.StoryAssets sa ON sa.StoryAssetID = ae.StoryAssetID
|
|
LEFT JOIN dbo.AssetStates ast ON ast.AssetStateID = sa.CurrentStateID
|
|
WHERE s.ChapterID = @ChapterID AND sa.IsArchived = 0 AND sa.IsResolved = 0
|
|
UNION ALL
|
|
SELECT N'Open Note', COALESCE(sn.NoteTitle, snt.TypeName), sn.NoteText, snt.TypeName, 40
|
|
FROM dbo.SceneNotes sn
|
|
INNER JOIN dbo.SceneNoteTypes snt ON snt.SceneNoteTypeID = sn.SceneNoteTypeID
|
|
INNER JOIN dbo.Scenes s ON s.SceneID = sn.SceneID
|
|
WHERE s.ChapterID = @ChapterID AND sn.IsResolved = 0
|
|
ORDER BY SortOrder, ItemType, Title;
|
|
END;
|
|
GO
|
|
|
|
CREATE OR ALTER PROCEDURE dbo.Export_CharacterReference
|
|
@CharacterID int
|
|
AS
|
|
BEGIN
|
|
SET NOCOUNT ON;
|
|
|
|
SELECT CharacterID, ProjectID, CharacterName, ShortName, Sex, BirthDate, AgeAtSeriesStart, AgeReferenceSceneID,
|
|
Height, EyeColour, DefaultDescription, CreatedDate, UpdatedDate, IsArchived
|
|
FROM dbo.Characters
|
|
WHERE CharacterID = @CharacterID;
|
|
|
|
EXEC dbo.Relationship_ListByCharacter @CharacterID;
|
|
|
|
SELECT sc.SceneCharacterID, sc.SceneID, sc.CharacterID, ch.ProjectID, ch.CharacterName, ch.ShortName, ch.BirthDate, ch.AgeAtSeriesStart,
|
|
sc.RoleInSceneTypeID, role.TypeName AS RoleInSceneTypeName, sc.PresenceTypeID, presence.TypeName AS PresenceTypeName,
|
|
sc.LocationID, location.LocationName, location.LocationPath, sc.EntryLocationID, entry.LocationName AS EntryLocationName,
|
|
sc.ExitLocationID, exitLocation.LocationName AS ExitLocationName, sc.AppearanceNotes, sc.OutfitDescription,
|
|
sc.PhysicalCondition, sc.EmotionalState, sc.KnowledgeNotes, s.SceneNumber, s.SceneTitle, c.ChapterNumber, b.BookTitle, b.Subtitle AS BookSubtitle,
|
|
sc.CreatedDate, sc.UpdatedDate
|
|
FROM dbo.SceneCharacters sc
|
|
INNER JOIN dbo.Characters ch ON ch.CharacterID = sc.CharacterID
|
|
INNER JOIN dbo.Scenes s ON s.SceneID = sc.SceneID
|
|
INNER JOIN dbo.Chapters c ON c.ChapterID = s.ChapterID
|
|
INNER JOIN dbo.Books b ON b.BookID = c.BookID
|
|
LEFT JOIN dbo.CharacterRoleInSceneTypes role ON role.CharacterRoleInSceneTypeID = sc.RoleInSceneTypeID
|
|
LEFT JOIN dbo.PresenceTypes presence ON presence.PresenceTypeID = sc.PresenceTypeID
|
|
LEFT JOIN dbo.LocationPaths location ON location.LocationID = sc.LocationID
|
|
LEFT JOIN dbo.LocationPaths entry ON entry.LocationID = sc.EntryLocationID
|
|
LEFT JOIN dbo.LocationPaths exitLocation ON exitLocation.LocationID = sc.ExitLocationID
|
|
WHERE sc.CharacterID = @CharacterID AND s.IsArchived = 0 AND c.IsArchived = 0 AND b.IsArchived = 0
|
|
ORDER BY b.SortOrder, c.SortOrder, s.SortOrder;
|
|
|
|
EXEC dbo.CharacterKnowledge_ListByCharacter @CharacterID;
|
|
|
|
SELECT ace.AssetCustodyEventID, ace.StoryAssetID, sa.AssetName, ace.SceneID,
|
|
ace.AssetCustodyEventTypeID, acet.TypeName AS CustodyEventTypeName, ace.Description,
|
|
STRING_AGG(CONCAT(COALESCE(custodian.CharacterName, aec.CharacterNameText), N' (', cr.RoleName, N')'), N', ') AS CustodianSummary,
|
|
ace.CreatedDate, ace.UpdatedDate, b.BookTitle, b.Subtitle AS BookSubtitle, ch.ChapterNumber, s.SceneNumber, s.SceneTitle
|
|
FROM dbo.AssetCustodyEvents ace
|
|
INNER JOIN dbo.StoryAssets sa ON sa.StoryAssetID = ace.StoryAssetID
|
|
INNER JOIN dbo.AssetCustodyEventTypes acet ON acet.AssetCustodyEventTypeID = ace.AssetCustodyEventTypeID
|
|
INNER JOIN dbo.Scenes s ON s.SceneID = ace.SceneID
|
|
INNER JOIN dbo.Chapters ch ON ch.ChapterID = s.ChapterID
|
|
INNER JOIN dbo.Books b ON b.BookID = ch.BookID
|
|
INNER JOIN dbo.AssetCustodyEventCharacters aec ON aec.AssetCustodyEventID = ace.AssetCustodyEventID
|
|
LEFT JOIN dbo.Characters custodian ON custodian.CharacterID = aec.CharacterID
|
|
LEFT JOIN dbo.CustodyRoles cr ON cr.CustodyRoleID = aec.CustodyRoleID
|
|
WHERE aec.CharacterID = @CharacterID
|
|
GROUP BY ace.AssetCustodyEventID, ace.StoryAssetID, sa.AssetName, ace.SceneID, ace.AssetCustodyEventTypeID, acet.TypeName, ace.Description, ace.CreatedDate, ace.UpdatedDate, b.BookTitle, b.Subtitle, ch.ChapterNumber, s.SceneNumber, s.SceneTitle, b.SortOrder, ch.SortOrder, s.SortOrder
|
|
ORDER BY b.SortOrder, ch.SortOrder, s.SortOrder;
|
|
|
|
SELECT N'Note' AS ItemType, COALESCE(sn.NoteTitle, snt.TypeName) AS Title, sn.NoteText AS Detail,
|
|
CASE WHEN sn.IsResolved = 1 THEN N'Resolved' ELSE N'Open' END AS Status, 10 AS SortOrder
|
|
FROM dbo.SceneNotes sn
|
|
INNER JOIN dbo.SceneNoteTypes snt ON snt.SceneNoteTypeID = sn.SceneNoteTypeID
|
|
INNER JOIN dbo.SceneCharacters sc ON sc.SceneID = sn.SceneID
|
|
WHERE sc.CharacterID = @CharacterID AND sn.IsResolved = 0
|
|
ORDER BY SortOrder, Title;
|
|
END;
|
|
GO
|
|
|
|
CREATE OR ALTER PROCEDURE dbo.Export_ResearchPack
|
|
@ProjectID int
|
|
AS
|
|
BEGIN
|
|
SET NOCOUNT ON;
|
|
|
|
SELECT b.BookTitle, b.Subtitle AS BookSubtitle, c.ChapterNumber, c.ChapterTitle, s.SceneID, s.SceneNumber, s.SceneTitle,
|
|
snt.TypeName AS Topic, COALESCE(sn.NoteTitle, snt.TypeName) AS Title, sn.NoteText AS Detail,
|
|
CAST(NULL AS nvarchar(1000)) AS Reference
|
|
FROM dbo.SceneNotes sn
|
|
INNER JOIN dbo.SceneNoteTypes snt ON snt.SceneNoteTypeID = sn.SceneNoteTypeID
|
|
INNER JOIN dbo.Scenes s ON s.SceneID = sn.SceneID
|
|
INNER JOIN dbo.Chapters c ON c.ChapterID = s.ChapterID
|
|
INNER JOIN dbo.Books b ON b.BookID = c.BookID
|
|
WHERE b.ProjectID = @ProjectID AND snt.TypeName IN (N'Research Note', N'Continuity Note', N'Timeline Note') AND s.IsArchived = 0 AND c.IsArchived = 0 AND b.IsArchived = 0
|
|
UNION ALL
|
|
SELECT b.BookTitle, b.Subtitle AS BookSubtitle, c.ChapterNumber, c.ChapterTitle, s.SceneID, s.SceneNumber, s.SceneTitle,
|
|
sat.TypeName, sa.Title, sa.Notes, COALESCE(sa.ExternalUrl, sa.FilePath)
|
|
FROM dbo.SceneAttachments sa
|
|
INNER JOIN dbo.SceneAttachmentTypes sat ON sat.SceneAttachmentTypeID = sa.SceneAttachmentTypeID
|
|
INNER JOIN dbo.Scenes s ON s.SceneID = sa.SceneID
|
|
INNER JOIN dbo.Chapters c ON c.ChapterID = s.ChapterID
|
|
INNER JOIN dbo.Books b ON b.BookID = c.BookID
|
|
WHERE b.ProjectID = @ProjectID AND s.IsArchived = 0 AND c.IsArchived = 0 AND b.IsArchived = 0
|
|
ORDER BY BookTitle, ChapterNumber, SceneNumber, Topic, Title;
|
|
END;
|
|
GO
|
|
|
|
CREATE OR ALTER PROCEDURE dbo.Analytics_StoryHealth
|
|
@ProjectID int,
|
|
@BookID int = NULL,
|
|
@StartChapterNumber decimal(10,2) = NULL,
|
|
@EndChapterNumber decimal(10,2) = NULL,
|
|
@StartSceneNumber decimal(10,2) = NULL,
|
|
@EndSceneNumber decimal(10,2) = NULL
|
|
AS
|
|
BEGIN
|
|
SET NOCOUNT ON;
|
|
|
|
DECLARE @HasMetricSettings bit =
|
|
CASE WHEN EXISTS (SELECT 1 FROM dbo.ProjectTimelineMetricSettings WHERE ProjectID = @ProjectID) THEN 1 ELSE 0 END;
|
|
|
|
;WITH ScopeScenes AS
|
|
(
|
|
SELECT nso.*
|
|
FROM dbo.NarrativeSceneOrder nso
|
|
WHERE nso.ProjectID = @ProjectID
|
|
AND (@BookID IS NULL OR nso.BookID = @BookID)
|
|
AND (@StartChapterNumber IS NULL OR nso.ChapterNumber >= @StartChapterNumber)
|
|
AND (@EndChapterNumber IS NULL OR nso.ChapterNumber <= @EndChapterNumber)
|
|
AND (@StartSceneNumber IS NULL OR nso.SceneNumber >= @StartSceneNumber)
|
|
AND (@EndSceneNumber IS NULL OR nso.SceneNumber <= @EndSceneNumber)
|
|
)
|
|
SELECT ss.SceneID, ss.BookID, ss.BookTitle, ss.BookSubtitle, ss.ChapterID, ss.ChapterNumber, ss.ChapterTitle,
|
|
ss.SceneNumber, ss.SceneTitle, ss.GlobalSceneIndex, ss.BookSceneIndex, smt.MetricTypeID,
|
|
smt.MetricName, smv.Value, smt.MinValue, smt.MaxValue,
|
|
CASE WHEN smt.MaxValue = smt.MinValue THEN 0 ELSE CAST(((smv.Value - smt.MinValue) * 100.0) / (smt.MaxValue - smt.MinValue) AS int) END AS [Percent]
|
|
FROM ScopeScenes ss
|
|
INNER JOIN dbo.SceneMetricValues smv ON smv.SceneID = ss.SceneID
|
|
INNER JOIN dbo.SceneMetricTypes smt ON smt.MetricTypeID = smv.MetricTypeID
|
|
WHERE smt.IsActive = 1
|
|
AND
|
|
(
|
|
(@HasMetricSettings = 1 AND EXISTS
|
|
(
|
|
SELECT 1
|
|
FROM dbo.ProjectTimelineMetricSettings ptms
|
|
WHERE ptms.ProjectID = @ProjectID AND ptms.MetricTypeID = smt.MetricTypeID
|
|
))
|
|
OR (@HasMetricSettings = 0 AND smt.MetricName IN
|
|
(
|
|
N'Overall Intensity', N'Tension', N'Emotional Weight', N'Action', N'Romance',
|
|
N'Violence', N'Sexual Charge', N'Mystery', N'Hope / Lightness', N'Comedy', N'Darkness'
|
|
))
|
|
)
|
|
ORDER BY smt.SortOrder, ss.GlobalSceneIndex;
|
|
|
|
;WITH ScopeScenes AS
|
|
(
|
|
SELECT nso.SceneID, nso.ChapterID
|
|
FROM dbo.NarrativeSceneOrder nso
|
|
WHERE nso.ProjectID = @ProjectID
|
|
AND (@BookID IS NULL OR nso.BookID = @BookID)
|
|
AND (@StartChapterNumber IS NULL OR nso.ChapterNumber >= @StartChapterNumber)
|
|
AND (@EndChapterNumber IS NULL OR nso.ChapterNumber <= @EndChapterNumber)
|
|
AND (@StartSceneNumber IS NULL OR nso.SceneNumber >= @StartSceneNumber)
|
|
AND (@EndSceneNumber IS NULL OR nso.SceneNumber <= @EndSceneNumber)
|
|
)
|
|
SELECT rs.RevisionStatusID, rs.StatusName, COUNT(s.SceneID) AS SceneCount,
|
|
SUM(CASE WHEN c.ChapterID IS NOT NULL THEN 1 ELSE 0 END) AS ChapterCount
|
|
FROM dbo.RevisionStatuses rs
|
|
LEFT JOIN dbo.Scenes s ON s.RevisionStatusID = rs.RevisionStatusID AND s.SceneID IN (SELECT SceneID FROM ScopeScenes)
|
|
LEFT JOIN dbo.Chapters c ON c.RevisionStatusID = rs.RevisionStatusID AND c.ChapterID IN (SELECT DISTINCT ChapterID FROM ScopeScenes)
|
|
GROUP BY rs.RevisionStatusID, rs.StatusName, rs.SortOrder
|
|
ORDER BY rs.SortOrder;
|
|
|
|
;WITH ScopeScenes AS
|
|
(
|
|
SELECT nso.SceneID, nso.BookID, nso.BookTitle, nso.BookSubtitle, nso.ChapterID, nso.ChapterNumber, nso.SceneNumber, nso.SceneTitle
|
|
FROM dbo.NarrativeSceneOrder nso
|
|
WHERE nso.ProjectID = @ProjectID
|
|
AND (@BookID IS NULL OR nso.BookID = @BookID)
|
|
AND (@StartChapterNumber IS NULL OR nso.ChapterNumber >= @StartChapterNumber)
|
|
AND (@EndChapterNumber IS NULL OR nso.ChapterNumber <= @EndChapterNumber)
|
|
AND (@StartSceneNumber IS NULL OR nso.SceneNumber >= @StartSceneNumber)
|
|
AND (@EndSceneNumber IS NULL OR nso.SceneNumber <= @EndSceneNumber)
|
|
)
|
|
SELECT ss.SceneID, ss.BookID, ss.BookTitle, ss.BookSubtitle, ss.ChapterID, ss.ChapterNumber, ss.SceneNumber, ss.SceneTitle,
|
|
rs.StatusName AS RevisionStatusName
|
|
FROM ScopeScenes ss
|
|
INNER JOIN dbo.Scenes s ON s.SceneID = ss.SceneID
|
|
INNER JOIN dbo.RevisionStatuses rs ON rs.RevisionStatusID = s.RevisionStatusID
|
|
WHERE rs.StatusName IN (N'Needs Work', N'Cut Candidate', N'Drafted')
|
|
ORDER BY rs.SortOrder, ss.BookID, ss.ChapterNumber, ss.SceneNumber;
|
|
|
|
;WITH ScopeScenes AS
|
|
(
|
|
SELECT nso.SceneID
|
|
FROM dbo.NarrativeSceneOrder nso
|
|
WHERE nso.ProjectID = @ProjectID
|
|
AND (@BookID IS NULL OR nso.BookID = @BookID)
|
|
AND (@StartChapterNumber IS NULL OR nso.ChapterNumber >= @StartChapterNumber)
|
|
AND (@EndChapterNumber IS NULL OR nso.ChapterNumber <= @EndChapterNumber)
|
|
AND (@StartSceneNumber IS NULL OR nso.SceneNumber >= @StartSceneNumber)
|
|
AND (@EndSceneNumber IS NULL OR nso.SceneNumber <= @EndSceneNumber)
|
|
),
|
|
Total AS (SELECT COUNT(*) AS TotalScenes FROM ScopeScenes)
|
|
SELECT spt.ScenePurposeTypeID, spt.PurposeName, COUNT(sp.SceneID) AS SceneCount,
|
|
CASE WHEN Total.TotalScenes = 0 THEN 0 ELSE CAST(COUNT(sp.SceneID) * 100.0 / Total.TotalScenes AS decimal(5,1)) END AS PercentOfScenes
|
|
FROM dbo.ScenePurposeTypes spt
|
|
CROSS JOIN Total
|
|
LEFT JOIN dbo.ScenePurposes sp ON sp.ScenePurposeTypeID = spt.ScenePurposeTypeID AND sp.SceneID IN (SELECT SceneID FROM ScopeScenes)
|
|
WHERE spt.IsArchived = 0
|
|
GROUP BY spt.ScenePurposeTypeID, spt.PurposeName, spt.SortOrder, Total.TotalScenes
|
|
ORDER BY spt.SortOrder;
|
|
|
|
;WITH ScopeScenes AS
|
|
(
|
|
SELECT nso.SceneID, nso.BookID, nso.BookTitle, nso.BookSubtitle, nso.ChapterID, nso.ChapterNumber, nso.SceneNumber, nso.SceneTitle
|
|
FROM dbo.NarrativeSceneOrder nso
|
|
INNER JOIN dbo.Scenes s ON s.SceneID = nso.SceneID
|
|
WHERE nso.ProjectID = @ProjectID
|
|
AND (@BookID IS NULL OR nso.BookID = @BookID)
|
|
AND (@StartChapterNumber IS NULL OR nso.ChapterNumber >= @StartChapterNumber)
|
|
AND (@EndChapterNumber IS NULL OR nso.ChapterNumber <= @EndChapterNumber)
|
|
AND (@StartSceneNumber IS NULL OR nso.SceneNumber >= @StartSceneNumber)
|
|
AND (@EndSceneNumber IS NULL OR nso.SceneNumber <= @EndSceneNumber)
|
|
AND (
|
|
NOT EXISTS (SELECT 1 FROM dbo.ScenePurposes sp WHERE sp.SceneID = nso.SceneID)
|
|
OR ISNULL(NULLIF(LTRIM(RTRIM(s.SceneOutcomeNotes)), N''), N'') = N''
|
|
)
|
|
)
|
|
SELECT ss.SceneID, ss.BookID, ss.BookTitle, ss.BookSubtitle, ss.ChapterID, ss.ChapterNumber, ss.SceneNumber, ss.SceneTitle,
|
|
CASE WHEN NOT EXISTS (SELECT 1 FROM dbo.ScenePurposes sp WHERE sp.SceneID = ss.SceneID) THEN 1 ELSE 0 END AS MissingPurpose,
|
|
CASE WHEN ISNULL(NULLIF(LTRIM(RTRIM(s.SceneOutcomeNotes)), N''), N'') = N'' THEN 1 ELSE 0 END AS MissingOutcome
|
|
FROM ScopeScenes ss
|
|
INNER JOIN dbo.Scenes s ON s.SceneID = ss.SceneID
|
|
ORDER BY ss.BookID, ss.ChapterNumber, ss.SceneNumber;
|
|
|
|
EXEC dbo.Analytics_StoryHealth_Remainder @ProjectID, @BookID, @StartChapterNumber, @EndChapterNumber, @StartSceneNumber, @EndSceneNumber;
|
|
END;
|
|
GO
|
|
|
|
CREATE OR ALTER PROCEDURE dbo.RelationshipMap_GetByProject
|
|
@ProjectID int
|
|
AS
|
|
BEGIN
|
|
SET NOCOUNT ON;
|
|
|
|
SELECT CharacterID, ProjectID, CharacterName, ShortName, Sex, BirthDate, AgeAtSeriesStart,
|
|
Height, EyeColour, DefaultDescription, CharacterImportance, ShowInQuickAddBar,
|
|
CreatedDate, UpdatedDate, IsArchived
|
|
FROM dbo.Characters
|
|
WHERE ProjectID = @ProjectID
|
|
AND IsArchived = 0
|
|
ORDER BY CharacterName;
|
|
|
|
SELECT b.BookID, b.ProjectID, b.BookTitle, b.Subtitle, b.BookNumber, b.Description, b.SortOrder,
|
|
b.CreatedDate, b.UpdatedDate, b.IsArchived
|
|
FROM dbo.Books b
|
|
WHERE b.ProjectID = @ProjectID
|
|
AND b.IsArchived = 0
|
|
ORDER BY b.SortOrder, b.BookNumber, b.BookTitle;
|
|
|
|
SELECT c.ChapterID, c.BookID, c.ChapterNumber, c.ChapterTitle, c.POVCharacterID, c.Summary,
|
|
c.SortOrder, c.RevisionStatusID, c.CreatedDate, c.UpdatedDate, c.IsArchived
|
|
FROM dbo.Chapters c
|
|
INNER JOIN dbo.Books b ON b.BookID = c.BookID
|
|
WHERE b.ProjectID = @ProjectID
|
|
AND b.IsArchived = 0
|
|
AND c.IsArchived = 0
|
|
ORDER BY b.SortOrder, c.SortOrder, c.ChapterNumber, c.ChapterTitle;
|
|
|
|
SELECT s.SceneID, s.ChapterID, s.SceneNumber, s.SceneTitle, s.Summary, s.POVCharacterID,
|
|
s.PrimaryLocationID, s.SortOrder, s.TimeModeID, s.StartDateTime, s.EndDateTime,
|
|
s.DurationAmount, s.DurationUnitID, s.RelativeTimeText, s.TimeConfidenceID,
|
|
s.ScenePurposeNotes, s.SceneOutcomeNotes, s.RevisionStatusID, s.CreatedDate,
|
|
s.UpdatedDate, s.IsArchived
|
|
FROM dbo.Scenes s
|
|
INNER JOIN dbo.Chapters c ON c.ChapterID = s.ChapterID
|
|
INNER JOIN dbo.Books b ON b.BookID = c.BookID
|
|
WHERE b.ProjectID = @ProjectID
|
|
AND b.IsArchived = 0
|
|
AND c.IsArchived = 0
|
|
AND s.IsArchived = 0
|
|
ORDER BY b.SortOrder, c.SortOrder, s.SortOrder, s.SceneNumber, s.SceneTitle;
|
|
|
|
SELECT cr.CharacterRelationshipID, cr.ProjectID, cr.CharacterAID, ca.CharacterName AS CharacterAName,
|
|
cr.CharacterBID, cb.CharacterName AS CharacterBName, cr.RelationshipTypeID, rt.TypeName AS RelationshipTypeName,
|
|
rt.RelationshipCategoryID, rc.CategoryName AS RelationshipCategoryName, COALESCE(rc.SortOrder, 900) AS CategorySortOrder,
|
|
rt.SortOrder AS RelationshipTypeSortOrder,
|
|
cr.IsPermanent, cr.StartSceneID, cr.EndSceneID, cr.IsKnownToReader, cr.Notes,
|
|
cr.IsInitialRelationship, cr.InitialBookID, b.BookTitle AS InitialBookTitle, b.Subtitle AS InitialBookSubtitle, cr.ReaderInitiallyKnows,
|
|
cr.InitialRelationshipStateID, rs.StateName AS InitialRelationshipStateName, cr.InitialIntensity, cr.IsReciprocal,
|
|
cr.CreatedDate, cr.UpdatedDate, cr.IsArchived
|
|
FROM dbo.CharacterRelationships cr
|
|
INNER JOIN dbo.Characters ca ON ca.CharacterID = cr.CharacterAID
|
|
INNER JOIN dbo.Characters cb ON cb.CharacterID = cr.CharacterBID
|
|
INNER JOIN dbo.RelationshipTypes rt ON rt.RelationshipTypeID = cr.RelationshipTypeID
|
|
LEFT JOIN dbo.RelationshipCategories rc ON rc.RelationshipCategoryID = rt.RelationshipCategoryID
|
|
LEFT JOIN dbo.Books b ON b.BookID = cr.InitialBookID
|
|
LEFT JOIN dbo.RelationshipStates rs ON rs.RelationshipStateID = cr.InitialRelationshipStateID
|
|
WHERE cr.ProjectID = @ProjectID
|
|
AND cr.IsArchived = 0
|
|
ORDER BY COALESCE(rc.SortOrder, 900), ca.CharacterName, cb.CharacterName, rt.SortOrder;
|
|
|
|
SELECT re.RelationshipEventID, re.CharacterRelationshipID, cr.ProjectID, cr.CharacterAID, ca.CharacterName AS CharacterAName,
|
|
cr.CharacterBID, cb.CharacterName AS CharacterBName, rt.TypeName AS RelationshipTypeName,
|
|
rt.RelationshipCategoryID, rc.CategoryName AS RelationshipCategoryName, COALESCE(rc.SortOrder, 900) AS CategorySortOrder,
|
|
rt.SortOrder AS RelationshipTypeSortOrder,
|
|
cr.IsReciprocal, re.SceneID, s.SceneNumber, s.SceneTitle, ch.ChapterNumber, b.BookTitle, b.Subtitle AS BookSubtitle,
|
|
re.RelationshipStateID, rs.StateName AS RelationshipStateName, re.Intensity, re.IsKnownToOtherCharacter,
|
|
re.Description, re.CreatedDate, re.UpdatedDate
|
|
FROM dbo.RelationshipEvents re
|
|
INNER JOIN dbo.CharacterRelationships cr ON cr.CharacterRelationshipID = re.CharacterRelationshipID
|
|
INNER JOIN dbo.Characters ca ON ca.CharacterID = cr.CharacterAID
|
|
INNER JOIN dbo.Characters cb ON cb.CharacterID = cr.CharacterBID
|
|
INNER JOIN dbo.RelationshipTypes rt ON rt.RelationshipTypeID = cr.RelationshipTypeID
|
|
LEFT JOIN dbo.RelationshipCategories rc ON rc.RelationshipCategoryID = rt.RelationshipCategoryID
|
|
INNER JOIN dbo.RelationshipStates rs ON rs.RelationshipStateID = re.RelationshipStateID
|
|
INNER JOIN dbo.Scenes s ON s.SceneID = re.SceneID
|
|
INNER JOIN dbo.Chapters ch ON ch.ChapterID = s.ChapterID
|
|
INNER JOIN dbo.Books b ON b.BookID = ch.BookID
|
|
WHERE cr.ProjectID = @ProjectID
|
|
AND cr.IsArchived = 0
|
|
AND b.IsArchived = 0
|
|
AND ch.IsArchived = 0
|
|
AND s.IsArchived = 0
|
|
ORDER BY b.SortOrder, ch.SortOrder, s.SortOrder, re.CreatedDate, re.RelationshipEventID;
|
|
|
|
SELECT RelationshipCategoryID, CategoryName, SortOrder, IsActive
|
|
FROM dbo.RelationshipCategories
|
|
WHERE IsActive = 1
|
|
ORDER BY SortOrder, CategoryName;
|
|
END;
|
|
GO
|