99 lines
5.1 KiB
Transact-SQL
99 lines
5.1 KiB
Transact-SQL
SET ANSI_NULLS ON;
|
|
GO
|
|
SET QUOTED_IDENTIFIER ON;
|
|
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.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, 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,
|
|
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
|