279 lines
14 KiB
Transact-SQL
279 lines
14 KiB
Transact-SQL
SET ANSI_NULLS ON;
|
|
GO
|
|
SET QUOTED_IDENTIFIER ON;
|
|
GO
|
|
|
|
CREATE OR ALTER PROCEDURE dbo.Export_StoryBible
|
|
@ProjectID int
|
|
AS
|
|
BEGIN
|
|
SET NOCOUNT ON;
|
|
EXEC dbo.StoryBible_CharacterSummaries @ProjectID;
|
|
EXEC dbo.StoryBible_ThreadSummaries @ProjectID;
|
|
EXEC dbo.StoryBible_AssetSummaries @ProjectID;
|
|
EXEC dbo.StoryBible_RelationshipSummaries @ProjectID;
|
|
EXEC dbo.StoryBible_LocationSummaries @ProjectID;
|
|
EXEC dbo.StoryBible_TimelineSummary @ProjectID;
|
|
EXEC dbo.StoryBible_OpenQuestions @ProjectID;
|
|
EXEC dbo.StoryBible_RevisionProgress @ProjectID;
|
|
END;
|
|
GO
|
|
|
|
CREATE OR ALTER PROCEDURE dbo.Export_NarrativeTimeline
|
|
@ProjectID int
|
|
AS
|
|
BEGIN
|
|
SET NOCOUNT ON;
|
|
EXEC dbo.StoryBible_TimelineSummary @ProjectID;
|
|
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, 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, 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, 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_WriterSessionBrief
|
|
@SceneID int
|
|
AS
|
|
BEGIN
|
|
SET NOCOUNT ON;
|
|
EXEC dbo.Export_SceneBrief @SceneID;
|
|
END;
|
|
GO
|
|
|
|
CREATE OR ALTER PROCEDURE dbo.Export_CharacterReference
|
|
@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;
|
|
|
|
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,
|
|
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, 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, 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, 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, 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.Export_SearchResults
|
|
@ProjectID int,
|
|
@Query nvarchar(200)
|
|
AS
|
|
BEGIN
|
|
SET NOCOUNT ON;
|
|
EXEC dbo.StoryBible_Search @ProjectID, @Query;
|
|
END;
|
|
GO
|