425 lines
18 KiB
Transact-SQL
425 lines
18 KiB
Transact-SQL
SET ANSI_NULLS ON;
|
|
GO
|
|
SET QUOTED_IDENTIFIER ON;
|
|
GO
|
|
|
|
IF COL_LENGTH(N'dbo.Characters', N'ShowInQuickAddBar') IS NULL
|
|
BEGIN
|
|
ALTER TABLE dbo.Characters
|
|
ADD ShowInQuickAddBar bit NOT NULL
|
|
CONSTRAINT DF_Characters_ShowInQuickAddBar DEFAULT 0 WITH VALUES;
|
|
END;
|
|
GO
|
|
|
|
IF COL_LENGTH(N'dbo.StoryAssets', N'ShowInQuickAddBar') IS NULL
|
|
BEGIN
|
|
ALTER TABLE dbo.StoryAssets
|
|
ADD ShowInQuickAddBar bit NOT NULL
|
|
CONSTRAINT DF_StoryAssets_ShowInQuickAddBar DEFAULT 0 WITH VALUES;
|
|
END;
|
|
GO
|
|
|
|
IF COL_LENGTH(N'dbo.Locations', N'ShowInQuickAddBar') IS NULL
|
|
BEGIN
|
|
ALTER TABLE dbo.Locations
|
|
ADD ShowInQuickAddBar bit NOT NULL
|
|
CONSTRAINT DF_Locations_ShowInQuickAddBar DEFAULT 0 WITH VALUES;
|
|
END;
|
|
GO
|
|
|
|
UPDATE dbo.Characters
|
|
SET ShowInQuickAddBar = 1
|
|
WHERE ShowInQuickAddBar = 0 AND ISNULL(CharacterImportance, 0) >= 6;
|
|
GO
|
|
|
|
UPDATE dbo.StoryAssets
|
|
SET ShowInQuickAddBar = 1
|
|
WHERE ShowInQuickAddBar = 0 AND (Importance >= 5 OR IsResolved = 0);
|
|
GO
|
|
|
|
WITH RankedCharacters AS
|
|
(
|
|
SELECT CharacterID,
|
|
ROW_NUMBER() OVER (PARTITION BY ProjectID ORDER BY ISNULL(CharacterImportance, 0) DESC, CharacterName) AS RowNumber,
|
|
SUM(CASE WHEN ShowInQuickAddBar = 1 THEN 1 ELSE 0 END) OVER (PARTITION BY ProjectID) AS FlaggedCount
|
|
FROM dbo.Characters
|
|
WHERE IsArchived = 0
|
|
)
|
|
UPDATE c
|
|
SET ShowInQuickAddBar = 1
|
|
FROM dbo.Characters c
|
|
INNER JOIN RankedCharacters rc ON rc.CharacterID = c.CharacterID
|
|
WHERE rc.FlaggedCount = 0 AND rc.RowNumber <= 10;
|
|
GO
|
|
|
|
WITH RankedAssets AS
|
|
(
|
|
SELECT StoryAssetID,
|
|
ROW_NUMBER() OVER (PARTITION BY ProjectID ORDER BY Importance DESC, AssetName) AS RowNumber,
|
|
SUM(CASE WHEN ShowInQuickAddBar = 1 THEN 1 ELSE 0 END) OVER (PARTITION BY ProjectID) AS FlaggedCount
|
|
FROM dbo.StoryAssets
|
|
WHERE IsArchived = 0
|
|
)
|
|
UPDATE sa
|
|
SET ShowInQuickAddBar = 1
|
|
FROM dbo.StoryAssets sa
|
|
INNER JOIN RankedAssets ra ON ra.StoryAssetID = sa.StoryAssetID
|
|
WHERE ra.FlaggedCount = 0 AND ra.RowNumber <= 10;
|
|
GO
|
|
|
|
WITH RankedLocations AS
|
|
(
|
|
SELECT lp.LocationID,
|
|
ROW_NUMBER() OVER (PARTITION BY lp.ProjectID ORDER BY lp.SortPath) AS RowNumber,
|
|
SUM(CASE WHEN l.ShowInQuickAddBar = 1 THEN 1 ELSE 0 END) OVER (PARTITION BY lp.ProjectID) AS FlaggedCount
|
|
FROM dbo.LocationPaths lp
|
|
INNER JOIN dbo.Locations l ON l.LocationID = lp.LocationID
|
|
)
|
|
UPDATE l
|
|
SET ShowInQuickAddBar = 1
|
|
FROM dbo.Locations l
|
|
INNER JOIN RankedLocations rl ON rl.LocationID = l.LocationID
|
|
WHERE rl.FlaggedCount = 0 AND rl.RowNumber <= 10;
|
|
GO
|
|
|
|
CREATE OR ALTER PROCEDURE dbo.Character_ListByProject
|
|
@ProjectID int
|
|
AS
|
|
BEGIN
|
|
SET NOCOUNT ON;
|
|
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
|
|
ORDER BY CharacterName;
|
|
END;
|
|
GO
|
|
|
|
CREATE OR ALTER PROCEDURE dbo.Character_Get
|
|
@CharacterID int
|
|
AS
|
|
BEGIN
|
|
SET NOCOUNT ON;
|
|
SELECT CharacterID, ProjectID, CharacterName, ShortName, Sex, BirthDate, AgeAtSeriesStart, AgeReferenceSceneID,
|
|
Height, EyeColour, CharacterImportance, ShowInQuickAddBar, DefaultDescription, CreatedDate, UpdatedDate, IsArchived
|
|
FROM dbo.Characters
|
|
WHERE CharacterID = @CharacterID AND IsArchived = 0;
|
|
END;
|
|
GO
|
|
|
|
CREATE OR ALTER PROCEDURE dbo.Character_Save
|
|
@CharacterID int = NULL,
|
|
@ProjectID int,
|
|
@CharacterName nvarchar(200),
|
|
@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 = 0,
|
|
@DefaultDescription nvarchar(max) = NULL
|
|
AS
|
|
BEGIN
|
|
SET NOCOUNT ON;
|
|
IF @CharacterID IS NULL OR @CharacterID = 0
|
|
BEGIN
|
|
INSERT dbo.Characters (ProjectID, CharacterName, ShortName, Sex, BirthDate, AgeAtSeriesStart, AgeReferenceSceneID, Height, EyeColour, CharacterImportance, ShowInQuickAddBar, DefaultDescription)
|
|
VALUES (@ProjectID, @CharacterName, @ShortName, @Sex, @BirthDate, @AgeAtSeriesStart, @AgeReferenceSceneID, @Height, @EyeColour, @CharacterImportance, @ShowInQuickAddBar, @DefaultDescription);
|
|
SET @CharacterID = CAST(SCOPE_IDENTITY() AS int);
|
|
END
|
|
ELSE
|
|
BEGIN
|
|
UPDATE dbo.Characters
|
|
SET CharacterName = @CharacterName, ShortName = @ShortName, Sex = @Sex, BirthDate = @BirthDate,
|
|
AgeAtSeriesStart = @AgeAtSeriesStart, AgeReferenceSceneID = @AgeReferenceSceneID,
|
|
Height = @Height, EyeColour = @EyeColour, CharacterImportance = @CharacterImportance,
|
|
ShowInQuickAddBar = @ShowInQuickAddBar, DefaultDescription = @DefaultDescription,
|
|
UpdatedDate = SYSUTCDATETIME()
|
|
WHERE CharacterID = @CharacterID;
|
|
END
|
|
SELECT @CharacterID;
|
|
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,
|
|
BookSortOrder int NOT NULL,
|
|
ChapterSortOrder int NOT NULL,
|
|
SceneSortOrder int NOT NULL
|
|
);
|
|
|
|
INSERT #TimelineScenes (SceneID, SceneNumber, SceneTitle, ChapterNumber, BookTitle, BookSortOrder, ChapterSortOrder, SceneSortOrder)
|
|
SELECT s.SceneID, s.SceneNumber, s.SceneTitle, ch.ChapterNumber, b.BookTitle, 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,
|
|
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 VIEW dbo.LocationPaths
|
|
AS
|
|
WITH LocationTree AS
|
|
(
|
|
SELECT LocationID, ProjectID, ParentLocationID, LocationName, LocationTypeID, Description, ShowInQuickAddBar,
|
|
CAST(LocationName AS nvarchar(max)) AS LocationPath,
|
|
CAST(RIGHT('000000' + CAST(LocationID AS varchar(6)), 6) AS nvarchar(max)) AS SortPath,
|
|
0 AS Depth
|
|
FROM dbo.Locations
|
|
WHERE ParentLocationID IS NULL AND IsArchived = 0
|
|
UNION ALL
|
|
SELECT child.LocationID, child.ProjectID, child.ParentLocationID, child.LocationName, child.LocationTypeID, child.Description, child.ShowInQuickAddBar,
|
|
CAST(parent.LocationPath + N' > ' + child.LocationName AS nvarchar(max)),
|
|
CAST(parent.SortPath + N'.' + RIGHT('000000' + CAST(child.LocationID AS varchar(6)), 6) AS nvarchar(max)),
|
|
parent.Depth + 1
|
|
FROM dbo.Locations child
|
|
INNER JOIN LocationTree parent ON parent.LocationID = child.ParentLocationID
|
|
WHERE child.IsArchived = 0
|
|
)
|
|
SELECT LocationID, ProjectID, ParentLocationID, LocationName, LocationTypeID, Description, ShowInQuickAddBar, LocationPath, SortPath, Depth
|
|
FROM LocationTree;
|
|
GO
|
|
|
|
CREATE OR ALTER PROCEDURE dbo.Location_ListByProject
|
|
@ProjectID int
|
|
AS
|
|
BEGIN
|
|
SET NOCOUNT ON;
|
|
SELECT lp.LocationID, lp.ProjectID, lp.ParentLocationID, lp.LocationName, lp.LocationTypeID, lt.TypeName AS LocationTypeName,
|
|
lp.Description, lp.ShowInQuickAddBar, lp.LocationPath, lp.Depth, l.CreatedDate, l.UpdatedDate, l.IsArchived
|
|
FROM dbo.LocationPaths lp
|
|
INNER JOIN dbo.Locations l ON l.LocationID = lp.LocationID
|
|
LEFT JOIN dbo.LocationTypes lt ON lt.LocationTypeID = lp.LocationTypeID
|
|
WHERE lp.ProjectID = @ProjectID
|
|
ORDER BY lp.SortPath;
|
|
END;
|
|
GO
|
|
|
|
CREATE OR ALTER PROCEDURE dbo.Location_Get
|
|
@LocationID int
|
|
AS
|
|
BEGIN
|
|
SET NOCOUNT ON;
|
|
SELECT lp.LocationID, lp.ProjectID, lp.ParentLocationID, parent.LocationName AS ParentLocationName,
|
|
lp.LocationName, lp.LocationTypeID, lt.TypeName AS LocationTypeName, lp.Description, lp.ShowInQuickAddBar, lp.LocationPath, lp.Depth,
|
|
l.CreatedDate, l.UpdatedDate, l.IsArchived
|
|
FROM dbo.LocationPaths lp
|
|
INNER JOIN dbo.Locations l ON l.LocationID = lp.LocationID
|
|
LEFT JOIN dbo.Locations parent ON parent.LocationID = lp.ParentLocationID
|
|
LEFT JOIN dbo.LocationTypes lt ON lt.LocationTypeID = lp.LocationTypeID
|
|
WHERE lp.LocationID = @LocationID;
|
|
END;
|
|
GO
|
|
|
|
CREATE OR ALTER PROCEDURE dbo.Location_Save
|
|
@LocationID int = NULL,
|
|
@ProjectID int,
|
|
@ParentLocationID int = NULL,
|
|
@LocationName nvarchar(200),
|
|
@LocationTypeID int = NULL,
|
|
@Description nvarchar(max) = NULL,
|
|
@ShowInQuickAddBar bit = 0
|
|
AS
|
|
BEGIN
|
|
SET NOCOUNT ON;
|
|
IF @LocationID IS NULL OR @LocationID = 0
|
|
BEGIN
|
|
INSERT dbo.Locations (ProjectID, ParentLocationID, LocationName, LocationTypeID, Description, ShowInQuickAddBar)
|
|
VALUES (@ProjectID, @ParentLocationID, @LocationName, @LocationTypeID, @Description, @ShowInQuickAddBar);
|
|
SET @LocationID = CAST(SCOPE_IDENTITY() AS int);
|
|
END
|
|
ELSE
|
|
BEGIN
|
|
UPDATE dbo.Locations
|
|
SET ParentLocationID = @ParentLocationID, LocationName = @LocationName, LocationTypeID = @LocationTypeID,
|
|
Description = @Description, ShowInQuickAddBar = @ShowInQuickAddBar, UpdatedDate = SYSUTCDATETIME()
|
|
WHERE LocationID = @LocationID;
|
|
END
|
|
SELECT @LocationID;
|
|
END;
|
|
GO
|
|
|
|
CREATE OR ALTER PROCEDURE dbo.StoryAsset_Save
|
|
@StoryAssetID int = NULL, @ProjectID int, @AssetName nvarchar(200), @AssetKindID int, @Description nvarchar(max) = NULL,
|
|
@Importance int, @CurrentStateID int = NULL, @CurrentLocationID int = NULL, @IsResolved bit, @ShowInQuickAddBar bit = 0
|
|
AS
|
|
BEGIN
|
|
SET NOCOUNT ON;
|
|
IF @StoryAssetID IS NULL OR @StoryAssetID = 0
|
|
BEGIN
|
|
INSERT dbo.StoryAssets (ProjectID, AssetName, AssetKindID, Description, Importance, CurrentStateID, CurrentLocationID, IsResolved, ShowInQuickAddBar)
|
|
VALUES (@ProjectID, @AssetName, @AssetKindID, @Description, @Importance, @CurrentStateID, @CurrentLocationID, @IsResolved, @ShowInQuickAddBar);
|
|
SET @StoryAssetID = CAST(SCOPE_IDENTITY() AS int);
|
|
END
|
|
ELSE
|
|
BEGIN
|
|
UPDATE dbo.StoryAssets
|
|
SET AssetName = @AssetName, AssetKindID = @AssetKindID, Description = @Description, Importance = @Importance,
|
|
CurrentStateID = @CurrentStateID, CurrentLocationID = @CurrentLocationID, IsResolved = @IsResolved,
|
|
ShowInQuickAddBar = @ShowInQuickAddBar, UpdatedDate = SYSUTCDATETIME()
|
|
WHERE StoryAssetID = @StoryAssetID;
|
|
END
|
|
SELECT @StoryAssetID;
|
|
END;
|
|
GO
|
|
|
|
CREATE OR ALTER PROCEDURE dbo.StoryAsset_ListByProject
|
|
@ProjectID int
|
|
AS
|
|
BEGIN
|
|
SET NOCOUNT ON;
|
|
SELECT sa.StoryAssetID, sa.ProjectID, sa.AssetName, sa.AssetKindID, ak.KindName, sa.Description, sa.Importance,
|
|
sa.CurrentStateID, ast.StateName AS CurrentStateName, sa.CurrentLocationID,
|
|
location.LocationName AS CurrentLocationName, location.LocationPath AS CurrentLocationPath,
|
|
sa.IsResolved, sa.ShowInQuickAddBar, sa.CreatedDate, sa.UpdatedDate, sa.IsArchived
|
|
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 location ON location.LocationID = sa.CurrentLocationID
|
|
WHERE sa.ProjectID = @ProjectID AND sa.IsArchived = 0
|
|
ORDER BY sa.Importance DESC, sa.AssetName;
|
|
END;
|
|
GO
|
|
|
|
CREATE OR ALTER PROCEDURE dbo.StoryAsset_Get
|
|
@StoryAssetID int
|
|
AS
|
|
BEGIN
|
|
SET NOCOUNT ON;
|
|
SELECT sa.StoryAssetID, sa.ProjectID, sa.AssetName, sa.AssetKindID, ak.KindName, sa.Description, sa.Importance,
|
|
sa.CurrentStateID, ast.StateName AS CurrentStateName, sa.CurrentLocationID,
|
|
location.LocationName AS CurrentLocationName, location.LocationPath AS CurrentLocationPath,
|
|
sa.IsResolved, sa.ShowInQuickAddBar, sa.CreatedDate, sa.UpdatedDate, sa.IsArchived
|
|
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 location ON location.LocationID = sa.CurrentLocationID
|
|
WHERE sa.StoryAssetID = @StoryAssetID AND sa.IsArchived = 0;
|
|
END;
|
|
GO
|
|
|
|
CREATE OR ALTER PROCEDURE dbo.AssetTimeline_GetByProject
|
|
@ProjectID int,
|
|
@BookID int = NULL
|
|
AS
|
|
BEGIN
|
|
SET NOCOUNT ON;
|
|
SELECT sa.StoryAssetID, sa.ProjectID, sa.AssetName, sa.AssetKindID, ak.KindName, sa.Description, sa.Importance,
|
|
sa.CurrentStateID, ast.StateName AS CurrentStateName, sa.CurrentLocationID,
|
|
location.LocationName AS CurrentLocationName, location.LocationPath AS CurrentLocationPath,
|
|
sa.IsResolved, sa.ShowInQuickAddBar, sa.CreatedDate, sa.UpdatedDate, sa.IsArchived
|
|
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 location ON location.LocationID = sa.CurrentLocationID
|
|
WHERE sa.ProjectID = @ProjectID AND sa.IsArchived = 0 AND (sa.Importance >= 5 OR sa.IsResolved = 0)
|
|
ORDER BY sa.Importance DESC, sa.AssetName;
|
|
|
|
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
|
|
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 sa.ProjectID = @ProjectID AND sa.IsArchived = 0 AND (@BookID IS NULL OR b.BookID = @BookID)
|
|
ORDER BY b.SortOrder, c.SortOrder, s.SortOrder, sa.AssetName;
|
|
END;
|
|
GO
|