556 lines
25 KiB
Transact-SQL
556 lines
25 KiB
Transact-SQL
SET ANSI_NULLS ON;
|
|
GO
|
|
SET QUOTED_IDENTIFIER ON;
|
|
GO
|
|
|
|
CREATE OR ALTER PROCEDURE dbo.StoryBible_CharacterSummaries
|
|
@ProjectID int
|
|
AS
|
|
BEGIN
|
|
SET NOCOUNT ON;
|
|
|
|
SELECT ch.CharacterID,
|
|
ch.CharacterName,
|
|
ch.ShortName,
|
|
ch.DefaultDescription AS Description,
|
|
ch.EyeColour,
|
|
ch.Height,
|
|
ch.AgeAtSeriesStart,
|
|
ISNULL(sceneCounts.SceneCount, 0) AS SceneCount,
|
|
ISNULL(povCounts.PovSceneCount, 0) AS PovSceneCount,
|
|
ISNULL(knowledge.KnowledgeCount, 0) AS KnowledgeCount,
|
|
ISNULL(relationships.RelationshipCount, 0) AS RelationshipCount,
|
|
ISNULL(sceneCounts.MissingLocationCount, 0) AS MissingLocationCount,
|
|
firstScene.SceneID AS FirstSceneID,
|
|
firstScene.SceneLabel AS FirstSceneLabel,
|
|
lastScene.SceneID AS LastSceneID,
|
|
lastScene.SceneLabel AS LastSceneLabel,
|
|
locations.LocationHistory
|
|
FROM dbo.Characters ch
|
|
OUTER APPLY
|
|
(
|
|
SELECT COUNT(DISTINCT sc.SceneID) AS SceneCount,
|
|
COUNT(DISTINCT CASE WHEN sc.LocationID IS NULL THEN sc.SceneCharacterID END) AS MissingLocationCount
|
|
FROM dbo.SceneCharacters sc
|
|
INNER JOIN dbo.Scenes s ON s.SceneID = sc.SceneID AND s.IsArchived = 0
|
|
INNER JOIN dbo.Chapters c ON c.ChapterID = s.ChapterID AND c.IsArchived = 0
|
|
INNER JOIN dbo.Books b ON b.BookID = c.BookID AND b.IsArchived = 0
|
|
WHERE b.ProjectID = @ProjectID AND sc.CharacterID = ch.CharacterID
|
|
) sceneCounts
|
|
OUTER APPLY
|
|
(
|
|
SELECT COUNT(DISTINCT s.SceneID) AS PovSceneCount
|
|
FROM dbo.Scenes s
|
|
INNER JOIN dbo.Chapters c ON c.ChapterID = s.ChapterID AND c.IsArchived = 0
|
|
INNER JOIN dbo.Books b ON b.BookID = c.BookID AND b.IsArchived = 0
|
|
WHERE b.ProjectID = @ProjectID AND s.IsArchived = 0 AND COALESCE(s.POVCharacterID, c.POVCharacterID) = ch.CharacterID
|
|
) povCounts
|
|
OUTER APPLY
|
|
(
|
|
SELECT COUNT(*) AS KnowledgeCount
|
|
FROM dbo.CharacterKnowledge ck
|
|
INNER JOIN dbo.Scenes s ON s.SceneID = ck.SceneID AND s.IsArchived = 0
|
|
INNER JOIN dbo.Chapters c ON c.ChapterID = s.ChapterID AND c.IsArchived = 0
|
|
INNER JOIN dbo.Books b ON b.BookID = c.BookID AND b.IsArchived = 0
|
|
WHERE b.ProjectID = @ProjectID AND ck.CharacterID = ch.CharacterID
|
|
) knowledge
|
|
OUTER APPLY
|
|
(
|
|
SELECT COUNT(*) AS RelationshipCount
|
|
FROM dbo.CharacterRelationships cr
|
|
WHERE cr.ProjectID = @ProjectID AND cr.IsArchived = 0
|
|
AND (cr.CharacterAID = ch.CharacterID OR cr.CharacterBID = ch.CharacterID)
|
|
) relationships
|
|
OUTER APPLY
|
|
(
|
|
SELECT TOP (1) s.SceneID,
|
|
CONCAT(b.BookTitle, N' / Ch ', c.ChapterNumber, N' / Scene ', s.SceneNumber, N': ', s.SceneTitle) AS SceneLabel
|
|
FROM dbo.SceneCharacters sc
|
|
INNER JOIN dbo.Scenes s ON s.SceneID = sc.SceneID AND s.IsArchived = 0
|
|
INNER JOIN dbo.Chapters c ON c.ChapterID = s.ChapterID AND c.IsArchived = 0
|
|
INNER JOIN dbo.Books b ON b.BookID = c.BookID AND b.IsArchived = 0
|
|
WHERE b.ProjectID = @ProjectID AND sc.CharacterID = ch.CharacterID
|
|
ORDER BY b.SortOrder, c.SortOrder, s.SortOrder, s.SceneID
|
|
) firstScene
|
|
OUTER APPLY
|
|
(
|
|
SELECT TOP (1) s.SceneID,
|
|
CONCAT(b.BookTitle, N' / Ch ', c.ChapterNumber, N' / Scene ', s.SceneNumber, N': ', s.SceneTitle) AS SceneLabel
|
|
FROM dbo.SceneCharacters sc
|
|
INNER JOIN dbo.Scenes s ON s.SceneID = sc.SceneID AND s.IsArchived = 0
|
|
INNER JOIN dbo.Chapters c ON c.ChapterID = s.ChapterID AND c.IsArchived = 0
|
|
INNER JOIN dbo.Books b ON b.BookID = c.BookID AND b.IsArchived = 0
|
|
WHERE b.ProjectID = @ProjectID AND sc.CharacterID = ch.CharacterID
|
|
ORDER BY b.SortOrder DESC, c.SortOrder DESC, s.SortOrder DESC, s.SceneID DESC
|
|
) lastScene
|
|
OUTER APPLY
|
|
(
|
|
SELECT STRING_AGG(CONVERT(nvarchar(max), location.LocationPath), N', ') AS LocationHistory
|
|
FROM
|
|
(
|
|
SELECT DISTINCT lp.LocationPath
|
|
FROM dbo.SceneCharacters sc
|
|
INNER JOIN dbo.LocationPaths lp ON lp.LocationID = sc.LocationID
|
|
WHERE sc.CharacterID = ch.CharacterID AND lp.ProjectID = @ProjectID
|
|
) location
|
|
) locations
|
|
WHERE ch.ProjectID = @ProjectID AND ch.IsArchived = 0
|
|
ORDER BY ch.CharacterName;
|
|
END;
|
|
GO
|
|
|
|
CREATE OR ALTER PROCEDURE dbo.StoryBible_ThreadSummaries
|
|
@ProjectID int
|
|
AS
|
|
BEGIN
|
|
SET NOCOUNT ON;
|
|
|
|
SELECT pt.PlotThreadID,
|
|
pt.ThreadTitle,
|
|
pl.PlotLineName,
|
|
tt.TypeName AS ThreadTypeName,
|
|
ts.StatusName AS ThreadStatusName,
|
|
pt.Importance,
|
|
ISNULL(events.EventCount, 0) AS EventCount,
|
|
introduced.SceneID AS IntroducedSceneID,
|
|
introduced.SceneLabel AS IntroducedSceneLabel,
|
|
lastTouched.SceneID AS LastTouchedSceneID,
|
|
lastTouched.SceneLabel AS LastTouchedSceneLabel,
|
|
planned.SceneID AS PlannedResolutionSceneID,
|
|
planned.SceneLabel AS PlannedResolutionSceneLabel,
|
|
actual.SceneID AS ActualResolutionSceneID,
|
|
actual.SceneLabel AS ActualResolutionSceneLabel,
|
|
events.EventHistory,
|
|
ISNULL(warnings.WarningCount, 0) AS WarningCount
|
|
FROM dbo.PlotThreads pt
|
|
INNER JOIN dbo.PlotLines pl ON pl.PlotLineID = pt.PlotLineID AND pl.IsArchived = 0
|
|
INNER JOIN dbo.ThreadTypes tt ON tt.ThreadTypeID = pt.ThreadTypeID
|
|
INNER JOIN dbo.ThreadStatuses ts ON ts.ThreadStatusID = pt.ThreadStatusID
|
|
OUTER APPLY
|
|
(
|
|
SELECT s.SceneID,
|
|
CONCAT(b.BookTitle, N' / Ch ', c.ChapterNumber, N' / Scene ', s.SceneNumber, N': ', s.SceneTitle) AS SceneLabel
|
|
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 s.SceneID = pt.IntroducedSceneID
|
|
) introduced
|
|
OUTER APPLY
|
|
(
|
|
SELECT s.SceneID,
|
|
CONCAT(b.BookTitle, N' / Ch ', c.ChapterNumber, N' / Scene ', s.SceneNumber, N': ', s.SceneTitle) AS SceneLabel
|
|
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 s.SceneID = pt.PlannedResolutionSceneID
|
|
) planned
|
|
OUTER APPLY
|
|
(
|
|
SELECT s.SceneID,
|
|
CONCAT(b.BookTitle, N' / Ch ', c.ChapterNumber, N' / Scene ', s.SceneNumber, N': ', s.SceneTitle) AS SceneLabel
|
|
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 s.SceneID = pt.ActualResolutionSceneID
|
|
) actual
|
|
OUTER APPLY
|
|
(
|
|
SELECT COUNT(*) AS EventCount,
|
|
STRING_AGG(CONVERT(nvarchar(max), CONCAT(tet.TypeName, N': ', te.EventTitle)), N' -> ') WITHIN GROUP (ORDER BY b.SortOrder, c.SortOrder, s.SortOrder, te.ThreadEventID) AS EventHistory
|
|
FROM dbo.ThreadEvents te
|
|
INNER JOIN dbo.ThreadEventTypes tet ON tet.ThreadEventTypeID = te.EventTypeID
|
|
INNER JOIN dbo.Scenes s ON s.SceneID = te.SceneID AND s.IsArchived = 0
|
|
INNER JOIN dbo.Chapters c ON c.ChapterID = s.ChapterID AND c.IsArchived = 0
|
|
INNER JOIN dbo.Books b ON b.BookID = c.BookID AND b.IsArchived = 0
|
|
WHERE te.PlotThreadID = pt.PlotThreadID
|
|
) events
|
|
OUTER APPLY
|
|
(
|
|
SELECT TOP (1) s.SceneID,
|
|
CONCAT(b.BookTitle, N' / Ch ', c.ChapterNumber, N' / Scene ', s.SceneNumber, N': ', s.SceneTitle) AS SceneLabel
|
|
FROM dbo.ThreadEvents te
|
|
INNER JOIN dbo.Scenes s ON s.SceneID = te.SceneID AND s.IsArchived = 0
|
|
INNER JOIN dbo.Chapters c ON c.ChapterID = s.ChapterID AND c.IsArchived = 0
|
|
INNER JOIN dbo.Books b ON b.BookID = c.BookID AND b.IsArchived = 0
|
|
WHERE te.PlotThreadID = pt.PlotThreadID
|
|
ORDER BY b.SortOrder DESC, c.SortOrder DESC, s.SortOrder DESC, te.ThreadEventID DESC
|
|
) lastTouched
|
|
OUTER APPLY
|
|
(
|
|
SELECT COUNT(*) AS WarningCount
|
|
FROM dbo.ContinuityWarnings cw
|
|
WHERE cw.ProjectID = @ProjectID AND cw.IsDismissed = 0
|
|
AND cw.EntityID = pt.PlotThreadID AND cw.EntityType IN (N'PlotThread', N'Thread')
|
|
) warnings
|
|
WHERE pl.ProjectID = @ProjectID AND pt.IsArchived = 0
|
|
ORDER BY pl.SortOrder, pt.Importance DESC, pt.ThreadTitle;
|
|
END;
|
|
GO
|
|
|
|
CREATE OR ALTER PROCEDURE dbo.StoryBible_AssetSummaries
|
|
@ProjectID int
|
|
AS
|
|
BEGIN
|
|
SET NOCOUNT ON;
|
|
|
|
SELECT sa.StoryAssetID,
|
|
sa.AssetName,
|
|
ak.KindName,
|
|
sa.Importance,
|
|
ast.StateName AS CurrentStateName,
|
|
currentLocation.LocationPath AS CurrentLocationPath,
|
|
sa.IsResolved,
|
|
ISNULL(events.EventCount, 0) AS EventCount,
|
|
ISNULL(dependencies.DependencyCount, 0) AS DependencyCount,
|
|
ISNULL(custody.CustodyCount, 0) AS CustodyCount,
|
|
ISNULL(sceneLocations.SceneCount, 0) AS SceneCount,
|
|
events.EventHistory,
|
|
dependencies.DependencySummary,
|
|
custody.CustodySummary,
|
|
ISNULL(warnings.WarningCount, 0) AS WarningCount
|
|
FROM dbo.StoryAssets sa
|
|
INNER JOIN dbo.AssetKinds ak ON ak.AssetKindID = sa.AssetKindID
|
|
LEFT JOIN dbo.AssetStates ast ON ast.AssetStateID = sa.CurrentStateID
|
|
LEFT JOIN dbo.LocationPaths currentLocation ON currentLocation.LocationID = sa.CurrentLocationID
|
|
OUTER APPLY
|
|
(
|
|
SELECT COUNT(*) AS EventCount,
|
|
STRING_AGG(CONVERT(nvarchar(max), CONCAT(aet.TypeName, N': ', ae.EventTitle)), N' -> ') WITHIN GROUP (ORDER BY b.SortOrder, c.SortOrder, s.SortOrder, ae.AssetEventID) AS EventHistory
|
|
FROM dbo.AssetEvents ae
|
|
INNER JOIN dbo.AssetEventTypes aet ON aet.AssetEventTypeID = ae.AssetEventTypeID
|
|
INNER JOIN dbo.Scenes s ON s.SceneID = ae.SceneID AND s.IsArchived = 0
|
|
INNER JOIN dbo.Chapters c ON c.ChapterID = s.ChapterID AND c.IsArchived = 0
|
|
INNER JOIN dbo.Books b ON b.BookID = c.BookID AND b.IsArchived = 0
|
|
WHERE ae.StoryAssetID = sa.StoryAssetID
|
|
) events
|
|
OUTER APPLY
|
|
(
|
|
SELECT COUNT(*) AS DependencyCount,
|
|
STRING_AGG(CONVERT(nvarchar(max), CONCAT(source.AssetName, N' ', adt.TypeName, N' ', target.AssetName)), N'; ') AS DependencySummary
|
|
FROM dbo.AssetDependencies ad
|
|
INNER JOIN dbo.AssetDependencyTypes adt ON adt.AssetDependencyTypeID = ad.AssetDependencyTypeID
|
|
INNER JOIN dbo.StoryAssets source ON source.StoryAssetID = ad.SourceAssetID
|
|
INNER JOIN dbo.StoryAssets target ON target.StoryAssetID = ad.TargetAssetID
|
|
WHERE ad.IsArchived = 0 AND (ad.SourceAssetID = sa.StoryAssetID OR ad.TargetAssetID = sa.StoryAssetID)
|
|
) dependencies
|
|
OUTER APPLY
|
|
(
|
|
SELECT COUNT(*) AS CustodyCount,
|
|
STRING_AGG(CONVERT(nvarchar(max), CONCAT(acet.TypeName, N': ', ISNULL(chars.CharacterNames, N'No named holder'))), N'; ') AS CustodySummary
|
|
FROM dbo.AssetCustodyEvents ace
|
|
INNER JOIN dbo.AssetCustodyEventTypes acet ON acet.AssetCustodyEventTypeID = ace.AssetCustodyEventTypeID
|
|
OUTER APPLY
|
|
(
|
|
SELECT STRING_AGG(CONVERT(nvarchar(max), COALESCE(ch.CharacterName, acec.CharacterNameText)), N', ') AS CharacterNames
|
|
FROM dbo.AssetCustodyEventCharacters acec
|
|
LEFT JOIN dbo.Characters ch ON ch.CharacterID = acec.CharacterID
|
|
WHERE acec.AssetCustodyEventID = ace.AssetCustodyEventID
|
|
) chars
|
|
WHERE ace.StoryAssetID = sa.StoryAssetID
|
|
) custody
|
|
OUTER APPLY
|
|
(
|
|
SELECT COUNT(DISTINCT SceneID) AS SceneCount
|
|
FROM
|
|
(
|
|
SELECT ae.SceneID FROM dbo.AssetEvents ae WHERE ae.StoryAssetID = sa.StoryAssetID
|
|
UNION
|
|
SELECT sal.SceneID FROM dbo.SceneAssetLocations sal WHERE sal.StoryAssetID = sa.StoryAssetID
|
|
) x
|
|
) sceneLocations
|
|
OUTER APPLY
|
|
(
|
|
SELECT COUNT(*) AS WarningCount
|
|
FROM dbo.ContinuityWarnings cw
|
|
WHERE cw.ProjectID = @ProjectID AND cw.IsDismissed = 0
|
|
AND cw.EntityID = sa.StoryAssetID AND cw.EntityType IN (N'StoryAsset', N'Asset')
|
|
) warnings
|
|
WHERE sa.ProjectID = @ProjectID AND sa.IsArchived = 0
|
|
ORDER BY sa.Importance DESC, sa.AssetName;
|
|
END;
|
|
GO
|
|
|
|
CREATE OR ALTER PROCEDURE dbo.StoryBible_RelationshipSummaries
|
|
@ProjectID int
|
|
AS
|
|
BEGIN
|
|
SET NOCOUNT ON;
|
|
|
|
SELECT cr.CharacterRelationshipID,
|
|
cr.CharacterAID,
|
|
ca.CharacterName AS CharacterAName,
|
|
cr.CharacterBID,
|
|
cb.CharacterName AS CharacterBName,
|
|
rt.TypeName AS RelationshipTypeName,
|
|
currentState.StateName AS CurrentStateName,
|
|
ISNULL(events.EventCount, 0) AS EventCount,
|
|
firstEvent.SceneLabel AS FirstEventLabel,
|
|
events.EventHistory
|
|
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
|
|
OUTER APPLY
|
|
(
|
|
SELECT TOP (1) rs.StateName
|
|
FROM dbo.RelationshipEvents re
|
|
INNER JOIN dbo.RelationshipStates rs ON rs.RelationshipStateID = re.RelationshipStateID
|
|
INNER JOIN dbo.Scenes s ON s.SceneID = re.SceneID AND s.IsArchived = 0
|
|
INNER JOIN dbo.Chapters c ON c.ChapterID = s.ChapterID AND c.IsArchived = 0
|
|
INNER JOIN dbo.Books b ON b.BookID = c.BookID AND b.IsArchived = 0
|
|
WHERE re.CharacterRelationshipID = cr.CharacterRelationshipID
|
|
ORDER BY b.SortOrder DESC, c.SortOrder DESC, s.SortOrder DESC, re.RelationshipEventID DESC
|
|
) currentState
|
|
OUTER APPLY
|
|
(
|
|
SELECT COUNT(*) AS EventCount,
|
|
STRING_AGG(CONVERT(nvarchar(max), CONCAT(rs.StateName, ISNULL(N': ' + re.Description, N''))), N' -> ') WITHIN GROUP (ORDER BY b.SortOrder, c.SortOrder, s.SortOrder, re.RelationshipEventID) AS EventHistory
|
|
FROM dbo.RelationshipEvents re
|
|
INNER JOIN dbo.RelationshipStates rs ON rs.RelationshipStateID = re.RelationshipStateID
|
|
INNER JOIN dbo.Scenes s ON s.SceneID = re.SceneID AND s.IsArchived = 0
|
|
INNER JOIN dbo.Chapters c ON c.ChapterID = s.ChapterID AND c.IsArchived = 0
|
|
INNER JOIN dbo.Books b ON b.BookID = c.BookID AND b.IsArchived = 0
|
|
WHERE re.CharacterRelationshipID = cr.CharacterRelationshipID
|
|
) events
|
|
OUTER APPLY
|
|
(
|
|
SELECT TOP (1) CONCAT(b.BookTitle, N' / Ch ', c.ChapterNumber, N' / Scene ', s.SceneNumber, N': ', s.SceneTitle) AS SceneLabel
|
|
FROM dbo.RelationshipEvents re
|
|
INNER JOIN dbo.Scenes s ON s.SceneID = re.SceneID AND s.IsArchived = 0
|
|
INNER JOIN dbo.Chapters c ON c.ChapterID = s.ChapterID AND c.IsArchived = 0
|
|
INNER JOIN dbo.Books b ON b.BookID = c.BookID AND b.IsArchived = 0
|
|
WHERE re.CharacterRelationshipID = cr.CharacterRelationshipID
|
|
ORDER BY b.SortOrder, c.SortOrder, s.SortOrder, re.RelationshipEventID
|
|
) firstEvent
|
|
WHERE cr.ProjectID = @ProjectID AND cr.IsArchived = 0
|
|
ORDER BY ca.CharacterName, cb.CharacterName;
|
|
END;
|
|
GO
|
|
|
|
CREATE OR ALTER PROCEDURE dbo.StoryBible_LocationSummaries
|
|
@ProjectID int
|
|
AS
|
|
BEGIN
|
|
SET NOCOUNT ON;
|
|
|
|
SELECT lp.LocationID,
|
|
lp.LocationName,
|
|
lp.LocationPath,
|
|
lt.TypeName AS LocationTypeName,
|
|
ISNULL(sceneCounts.SceneCount, 0) AS SceneCount,
|
|
ISNULL(characterCounts.CharacterCount, 0) AS CharacterCount,
|
|
ISNULL(assetCounts.AssetCount, 0) AS AssetCount,
|
|
ISNULL(relationshipCounts.RelationshipCount, 0) AS RelationshipCount,
|
|
related.RelatedLocations
|
|
FROM dbo.LocationPaths lp
|
|
LEFT JOIN dbo.LocationTypes lt ON lt.LocationTypeID = lp.LocationTypeID
|
|
OUTER APPLY
|
|
(
|
|
SELECT COUNT(DISTINCT s.SceneID) AS SceneCount
|
|
FROM dbo.Scenes s
|
|
INNER JOIN dbo.Chapters c ON c.ChapterID = s.ChapterID AND c.IsArchived = 0
|
|
INNER JOIN dbo.Books b ON b.BookID = c.BookID AND b.IsArchived = 0
|
|
WHERE b.ProjectID = @ProjectID AND s.IsArchived = 0 AND s.PrimaryLocationID = lp.LocationID
|
|
) sceneCounts
|
|
OUTER APPLY
|
|
(
|
|
SELECT COUNT(DISTINCT sc.CharacterID) AS CharacterCount
|
|
FROM dbo.SceneCharacters sc
|
|
WHERE sc.LocationID = lp.LocationID
|
|
) characterCounts
|
|
OUTER APPLY
|
|
(
|
|
SELECT COUNT(DISTINCT sa.StoryAssetID) AS AssetCount
|
|
FROM dbo.StoryAssets sa
|
|
WHERE sa.ProjectID = @ProjectID AND sa.IsArchived = 0 AND sa.CurrentLocationID = lp.LocationID
|
|
) assetCounts
|
|
OUTER APPLY
|
|
(
|
|
SELECT COUNT(*) AS RelationshipCount
|
|
FROM dbo.LocationRelationships lr
|
|
WHERE lr.IsArchived = 0 AND (lr.FromLocationID = lp.LocationID OR lr.ToLocationID = lp.LocationID)
|
|
) relationshipCounts
|
|
OUTER APPLY
|
|
(
|
|
SELECT STRING_AGG(CONVERT(nvarchar(max), relatedLocation.LocationPath), N', ') AS RelatedLocations
|
|
FROM
|
|
(
|
|
SELECT DISTINCT other.LocationPath
|
|
FROM dbo.LocationRelationships lr
|
|
INNER JOIN dbo.LocationPaths other ON other.LocationID = CASE WHEN lr.FromLocationID = lp.LocationID THEN lr.ToLocationID ELSE lr.FromLocationID END
|
|
WHERE lr.IsArchived = 0 AND (lr.FromLocationID = lp.LocationID OR lr.ToLocationID = lp.LocationID)
|
|
) relatedLocation
|
|
) related
|
|
WHERE lp.ProjectID = @ProjectID
|
|
ORDER BY lp.LocationPath;
|
|
END;
|
|
GO
|
|
|
|
CREATE OR ALTER PROCEDURE dbo.StoryBible_TimelineSummary
|
|
@ProjectID int
|
|
AS
|
|
BEGIN
|
|
SET NOCOUNT ON;
|
|
|
|
SELECT b.BookID,
|
|
b.BookTitle,
|
|
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.StoryBible_OpenQuestions
|
|
@ProjectID int
|
|
AS
|
|
BEGIN
|
|
SET NOCOUNT ON;
|
|
|
|
SELECT N'Plot Thread' AS Category, N'Info' AS SeverityName, pt.ThreadTitle AS Title,
|
|
CONCAT(N'Open thread in ', pl.PlotLineName, N' with status ', ts.StatusName) AS Detail,
|
|
CAST(NULL AS int) AS SceneID, pt.PlotThreadID AS EntityID, N'PlotThread' AS EntityType
|
|
FROM dbo.PlotThreads pt
|
|
INNER JOIN dbo.ThreadStatuses ts ON ts.ThreadStatusID = pt.ThreadStatusID
|
|
INNER JOIN dbo.PlotLines pl ON pl.PlotLineID = pt.PlotLineID AND pl.ProjectID = @ProjectID AND pl.IsArchived = 0
|
|
WHERE pt.IsArchived = 0 AND ts.IsOpenStatus = 1
|
|
UNION ALL
|
|
SELECT N'Story Asset', N'Info', sa.AssetName,
|
|
CONCAT(N'Important unresolved asset. Importance ', sa.Importance, N'.'),
|
|
CAST(NULL AS int), sa.StoryAssetID, N'StoryAsset'
|
|
FROM dbo.StoryAssets sa
|
|
WHERE sa.ProjectID = @ProjectID AND sa.IsArchived = 0 AND sa.IsResolved = 0 AND sa.Importance >= 6
|
|
UNION ALL
|
|
SELECT N'Scene', N'Warning', s.SceneTitle,
|
|
CONCAT(N'Scene ', s.SceneNumber, N' is marked Needs Work.'),
|
|
s.SceneID, s.SceneID, N'Scene'
|
|
FROM dbo.Scenes s
|
|
INNER JOIN dbo.Chapters c ON c.ChapterID = s.ChapterID AND c.IsArchived = 0
|
|
INNER JOIN dbo.Books b ON b.BookID = c.BookID AND b.ProjectID = @ProjectID AND b.IsArchived = 0
|
|
INNER JOIN dbo.RevisionStatuses rs ON rs.RevisionStatusID = s.RevisionStatusID
|
|
WHERE s.IsArchived = 0 AND rs.StatusName = N'Needs Work'
|
|
UNION ALL
|
|
SELECT N'Scene Purpose', N'Info', s.SceneTitle,
|
|
N'No primary scene purpose has been assigned yet.',
|
|
s.SceneID, s.SceneID, N'Scene'
|
|
FROM dbo.Scenes s
|
|
INNER JOIN dbo.Chapters c ON c.ChapterID = s.ChapterID AND c.IsArchived = 0
|
|
INNER JOIN dbo.Books b ON b.BookID = c.BookID AND b.ProjectID = @ProjectID AND b.IsArchived = 0
|
|
WHERE s.IsArchived = 0 AND NOT EXISTS (SELECT 1 FROM dbo.ScenePurposes sp WHERE sp.SceneID = s.SceneID)
|
|
UNION ALL
|
|
SELECT N'Scene Outcome', N'Info', s.SceneTitle,
|
|
N'Outcome notes are empty.',
|
|
s.SceneID, s.SceneID, N'Scene'
|
|
FROM dbo.Scenes s
|
|
INNER JOIN dbo.Chapters c ON c.ChapterID = s.ChapterID AND c.IsArchived = 0
|
|
INNER JOIN dbo.Books b ON b.BookID = c.BookID AND b.ProjectID = @ProjectID AND b.IsArchived = 0
|
|
WHERE s.IsArchived = 0 AND NULLIF(LTRIM(RTRIM(s.SceneOutcomeNotes)), N'') IS NULL
|
|
UNION ALL
|
|
SELECT N'Warning', ws.SeverityName, cw.Message, cw.Details,
|
|
cw.SceneID, cw.EntityID, cw.EntityType
|
|
FROM dbo.ContinuityWarnings cw
|
|
INNER JOIN dbo.WarningSeverities ws ON ws.WarningSeverityID = cw.WarningSeverityID
|
|
WHERE cw.ProjectID = @ProjectID AND cw.IsDismissed = 0;
|
|
END;
|
|
GO
|
|
|
|
CREATE OR ALTER PROCEDURE dbo.StoryBible_RevisionProgress
|
|
@ProjectID int
|
|
AS
|
|
BEGIN
|
|
SET NOCOUNT ON;
|
|
|
|
SELECT N'Scene revision' AS GroupName,
|
|
COUNT(*) AS ItemCount,
|
|
rs.StatusName AS Detail,
|
|
CAST(NULL AS int) AS SceneID,
|
|
CAST(NULL AS int) AS ChapterID
|
|
FROM dbo.Scenes s
|
|
INNER JOIN dbo.Chapters c ON c.ChapterID = s.ChapterID AND c.IsArchived = 0
|
|
INNER JOIN dbo.Books b ON b.BookID = c.BookID AND b.ProjectID = @ProjectID AND b.IsArchived = 0
|
|
INNER JOIN dbo.RevisionStatuses rs ON rs.RevisionStatusID = s.RevisionStatusID
|
|
WHERE s.IsArchived = 0
|
|
GROUP BY rs.StatusName, rs.SortOrder
|
|
UNION ALL
|
|
SELECT N'Unfinished chapters',
|
|
COUNT(DISTINCT c.ChapterID),
|
|
N'Chapters with at least one scene not revised, polished, locked, or cut',
|
|
CAST(NULL AS int),
|
|
CAST(NULL AS int)
|
|
FROM dbo.Chapters c
|
|
INNER JOIN dbo.Books b ON b.BookID = c.BookID AND b.ProjectID = @ProjectID AND b.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
|
|
WHERE c.IsArchived = 0 AND rs.StatusName NOT IN (N'Revised', N'Polished', N'Locked', N'Cut Candidate');
|
|
END;
|
|
GO
|
|
|
|
CREATE OR ALTER PROCEDURE dbo.StoryBible_Search
|
|
@ProjectID int,
|
|
@Query nvarchar(200) = NULL
|
|
AS
|
|
BEGIN
|
|
SET NOCOUNT ON;
|
|
|
|
IF NULLIF(LTRIM(RTRIM(@Query)), N'') IS NULL
|
|
RETURN;
|
|
|
|
DECLARE @Term nvarchar(204) = N'%' + LTRIM(RTRIM(@Query)) + N'%';
|
|
|
|
SELECT N'Character' AS ResultType, ch.CharacterID AS EntityID, ch.CharacterName AS Title,
|
|
ch.DefaultDescription AS Detail, CAST(NULL AS int) AS SceneID
|
|
FROM dbo.Characters ch
|
|
WHERE ch.ProjectID = @ProjectID AND ch.IsArchived = 0
|
|
AND (ch.CharacterName LIKE @Term OR ch.ShortName LIKE @Term OR ch.DefaultDescription LIKE @Term)
|
|
UNION ALL
|
|
SELECT N'Scene', s.SceneID, CONCAT(N'Scene ', s.SceneNumber, N': ', s.SceneTitle),
|
|
s.Summary, s.SceneID
|
|
FROM dbo.Scenes s
|
|
INNER JOIN dbo.Chapters c ON c.ChapterID = s.ChapterID AND c.IsArchived = 0
|
|
INNER JOIN dbo.Books b ON b.BookID = c.BookID AND b.ProjectID = @ProjectID AND b.IsArchived = 0
|
|
WHERE s.IsArchived = 0 AND (s.SceneTitle LIKE @Term OR s.Summary LIKE @Term OR s.ScenePurposeNotes LIKE @Term OR s.SceneOutcomeNotes LIKE @Term)
|
|
UNION ALL
|
|
SELECT N'Plot Thread', pt.PlotThreadID, pt.ThreadTitle, pt.Summary, CAST(NULL AS int)
|
|
FROM dbo.PlotThreads pt
|
|
INNER JOIN dbo.PlotLines pl ON pl.PlotLineID = pt.PlotLineID AND pl.ProjectID = @ProjectID AND pl.IsArchived = 0
|
|
WHERE pt.IsArchived = 0 AND (pt.ThreadTitle LIKE @Term OR pt.Summary LIKE @Term)
|
|
UNION ALL
|
|
SELECT N'Story Asset', sa.StoryAssetID, sa.AssetName, sa.Description, CAST(NULL AS int)
|
|
FROM dbo.StoryAssets sa
|
|
WHERE sa.ProjectID = @ProjectID AND sa.IsArchived = 0 AND (sa.AssetName LIKE @Term OR sa.Description LIKE @Term)
|
|
UNION ALL
|
|
SELECT N'Location', lp.LocationID, lp.LocationPath, lp.Description, CAST(NULL AS int)
|
|
FROM dbo.LocationPaths lp
|
|
WHERE lp.ProjectID = @ProjectID AND (lp.LocationName LIKE @Term OR lp.LocationPath LIKE @Term OR lp.Description LIKE @Term)
|
|
UNION ALL
|
|
SELECT N'Relationship', cr.CharacterRelationshipID, CONCAT(ca.CharacterName, N' - ', cb.CharacterName),
|
|
CONCAT(rt.TypeName, N'. ', ISNULL(cr.Notes, N'')), CAST(NULL AS int)
|
|
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.ProjectID = @ProjectID AND cr.IsArchived = 0
|
|
AND (ca.CharacterName LIKE @Term OR cb.CharacterName LIKE @Term OR rt.TypeName LIKE @Term OR cr.Notes LIKE @Term)
|
|
UNION ALL
|
|
SELECT N'Warning', cw.ContinuityWarningID, cw.Message, cw.Details, cw.SceneID
|
|
FROM dbo.ContinuityWarnings cw
|
|
WHERE cw.ProjectID = @ProjectID AND cw.IsDismissed = 0
|
|
AND (cw.Message LIKE @Term OR cw.Details LIKE @Term OR cw.EntityType LIKE @Term)
|
|
ORDER BY ResultType, Title;
|
|
END;
|
|
GO
|