40 lines
2.1 KiB
Transact-SQL
40 lines
2.1 KiB
Transact-SQL
SET ANSI_NULLS ON;
|
|
GO
|
|
SET QUOTED_IDENTIFIER ON;
|
|
GO
|
|
|
|
CREATE OR ALTER PROCEDURE dbo.CharacterTimeline_GetByProject
|
|
@ProjectID int,
|
|
@BookID int = NULL
|
|
AS
|
|
BEGIN
|
|
SET NOCOUNT ON;
|
|
|
|
SELECT CharacterID, ProjectID, CharacterName, ShortName, Sex, BirthDate, AgeAtSeriesStart, AgeReferenceSceneID,
|
|
Height, EyeColour, CharacterImportance, DefaultDescription, CreatedDate, UpdatedDate, IsArchived
|
|
FROM dbo.Characters
|
|
WHERE ProjectID = @ProjectID AND IsArchived = 0
|
|
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, ch.ChapterNumber, b.BookTitle,
|
|
sc.CreatedDate, sc.UpdatedDate
|
|
FROM dbo.SceneCharacters sc
|
|
INNER JOIN dbo.Characters c ON c.CharacterID = sc.CharacterID
|
|
INNER JOIN dbo.Scenes s ON s.SceneID = sc.SceneID
|
|
INNER JOIN dbo.Chapters ch ON ch.ChapterID = s.ChapterID
|
|
INNER JOIN dbo.Books b ON b.BookID = ch.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 c.ProjectID = @ProjectID AND c.IsArchived = 0 AND s.IsArchived = 0 AND ch.IsArchived = 0 AND b.IsArchived = 0
|
|
AND (@BookID IS NULL OR b.BookID = @BookID)
|
|
ORDER BY b.SortOrder, ch.SortOrder, s.SortOrder, c.CharacterName;
|
|
END;
|
|
GO
|