733 lines
37 KiB
Transact-SQL
733 lines
37 KiB
Transact-SQL
SET ANSI_NULLS ON;
|
|
GO
|
|
SET QUOTED_IDENTIFIER ON;
|
|
GO
|
|
|
|
IF OBJECT_ID(N'dbo.LocationTypes', N'U') IS NULL
|
|
BEGIN
|
|
CREATE TABLE dbo.LocationTypes
|
|
(
|
|
LocationTypeID int IDENTITY(1,1) NOT NULL CONSTRAINT PK_LocationTypes PRIMARY KEY,
|
|
TypeName nvarchar(100) NOT NULL,
|
|
SortOrder int NOT NULL,
|
|
IsActive bit NOT NULL CONSTRAINT DF_LocationTypes_IsActive DEFAULT 1,
|
|
CONSTRAINT UQ_LocationTypes_TypeName UNIQUE (TypeName)
|
|
);
|
|
END;
|
|
GO
|
|
|
|
IF OBJECT_ID(N'dbo.Locations', N'U') IS NULL
|
|
BEGIN
|
|
CREATE TABLE dbo.Locations
|
|
(
|
|
LocationID int IDENTITY(1,1) NOT NULL CONSTRAINT PK_Locations PRIMARY KEY,
|
|
ProjectID int NOT NULL,
|
|
ParentLocationID int NULL,
|
|
LocationName nvarchar(200) NOT NULL,
|
|
LocationTypeID int NULL,
|
|
Description nvarchar(max) NULL,
|
|
CreatedDate datetime2 NOT NULL CONSTRAINT DF_Locations_CreatedDate DEFAULT SYSUTCDATETIME(),
|
|
UpdatedDate datetime2 NOT NULL CONSTRAINT DF_Locations_UpdatedDate DEFAULT SYSUTCDATETIME(),
|
|
IsArchived bit NOT NULL CONSTRAINT DF_Locations_IsArchived DEFAULT 0,
|
|
CONSTRAINT FK_Locations_Projects FOREIGN KEY (ProjectID) REFERENCES dbo.Projects(ProjectID),
|
|
CONSTRAINT FK_Locations_Parent FOREIGN KEY (ParentLocationID) REFERENCES dbo.Locations(LocationID),
|
|
CONSTRAINT FK_Locations_Types FOREIGN KEY (LocationTypeID) REFERENCES dbo.LocationTypes(LocationTypeID)
|
|
);
|
|
END;
|
|
GO
|
|
|
|
IF OBJECT_ID(N'dbo.LocationRelationshipTypes', N'U') IS NULL
|
|
BEGIN
|
|
CREATE TABLE dbo.LocationRelationshipTypes
|
|
(
|
|
LocationRelationshipTypeID int IDENTITY(1,1) NOT NULL CONSTRAINT PK_LocationRelationshipTypes PRIMARY KEY,
|
|
TypeName nvarchar(100) NOT NULL,
|
|
SortOrder int NOT NULL,
|
|
IsActive bit NOT NULL CONSTRAINT DF_LocationRelationshipTypes_IsActive DEFAULT 1,
|
|
CONSTRAINT UQ_LocationRelationshipTypes_TypeName UNIQUE (TypeName)
|
|
);
|
|
END;
|
|
GO
|
|
|
|
IF OBJECT_ID(N'dbo.LocationRelationships', N'U') IS NULL
|
|
BEGIN
|
|
CREATE TABLE dbo.LocationRelationships
|
|
(
|
|
LocationRelationshipID int IDENTITY(1,1) NOT NULL CONSTRAINT PK_LocationRelationships PRIMARY KEY,
|
|
FromLocationID int NOT NULL,
|
|
ToLocationID int NOT NULL,
|
|
LocationRelationshipTypeID int NOT NULL,
|
|
IsBidirectional bit NOT NULL CONSTRAINT DF_LocationRelationships_IsBidirectional DEFAULT 0,
|
|
Strength int NULL,
|
|
CanHearNormalSpeech bit NOT NULL CONSTRAINT DF_LocationRelationships_CanHearNormalSpeech DEFAULT 0,
|
|
CanHearLoudNoise bit NOT NULL CONSTRAINT DF_LocationRelationships_CanHearLoudNoise DEFAULT 0,
|
|
CanSeeMovement bit NOT NULL CONSTRAINT DF_LocationRelationships_CanSeeMovement DEFAULT 0,
|
|
CanSeeClearly bit NOT NULL CONSTRAINT DF_LocationRelationships_CanSeeClearly DEFAULT 0,
|
|
CanTravelDirectly bit NOT NULL CONSTRAINT DF_LocationRelationships_CanTravelDirectly DEFAULT 0,
|
|
Notes nvarchar(max) NULL,
|
|
CreatedDate datetime2 NOT NULL CONSTRAINT DF_LocationRelationships_CreatedDate DEFAULT SYSUTCDATETIME(),
|
|
UpdatedDate datetime2 NOT NULL CONSTRAINT DF_LocationRelationships_UpdatedDate DEFAULT SYSUTCDATETIME(),
|
|
IsArchived bit NOT NULL CONSTRAINT DF_LocationRelationships_IsArchived DEFAULT 0,
|
|
CONSTRAINT FK_LocationRelationships_From FOREIGN KEY (FromLocationID) REFERENCES dbo.Locations(LocationID),
|
|
CONSTRAINT FK_LocationRelationships_To FOREIGN KEY (ToLocationID) REFERENCES dbo.Locations(LocationID),
|
|
CONSTRAINT FK_LocationRelationships_Types FOREIGN KEY (LocationRelationshipTypeID) REFERENCES dbo.LocationRelationshipTypes(LocationRelationshipTypeID)
|
|
);
|
|
END;
|
|
GO
|
|
|
|
IF OBJECT_ID(N'dbo.SceneAssetLocations', N'U') IS NULL
|
|
BEGIN
|
|
CREATE TABLE dbo.SceneAssetLocations
|
|
(
|
|
SceneAssetLocationID int IDENTITY(1,1) NOT NULL CONSTRAINT PK_SceneAssetLocations PRIMARY KEY,
|
|
SceneID int NOT NULL,
|
|
StoryAssetID int NOT NULL,
|
|
LocationID int NOT NULL,
|
|
Description nvarchar(max) NULL,
|
|
CreatedDate datetime2 NOT NULL CONSTRAINT DF_SceneAssetLocations_CreatedDate DEFAULT SYSUTCDATETIME(),
|
|
UpdatedDate datetime2 NOT NULL CONSTRAINT DF_SceneAssetLocations_UpdatedDate DEFAULT SYSUTCDATETIME(),
|
|
CONSTRAINT FK_SceneAssetLocations_Scenes FOREIGN KEY (SceneID) REFERENCES dbo.Scenes(SceneID),
|
|
CONSTRAINT FK_SceneAssetLocations_StoryAssets FOREIGN KEY (StoryAssetID) REFERENCES dbo.StoryAssets(StoryAssetID),
|
|
CONSTRAINT FK_SceneAssetLocations_Locations FOREIGN KEY (LocationID) REFERENCES dbo.Locations(LocationID)
|
|
);
|
|
END;
|
|
GO
|
|
|
|
MERGE dbo.LocationTypes AS target
|
|
USING (VALUES
|
|
(N'Country', 10), (N'Region', 20), (N'Town', 30), (N'Village', 40), (N'Street', 50),
|
|
(N'Building', 60), (N'Floor', 70), (N'Room', 80), (N'Outdoor Area', 90), (N'Vehicle Interior', 100),
|
|
(N'Landmark', 110), (N'Other', 120)
|
|
) AS source (TypeName, SortOrder)
|
|
ON target.TypeName = source.TypeName
|
|
WHEN NOT MATCHED THEN INSERT (TypeName, SortOrder) VALUES (source.TypeName, source.SortOrder);
|
|
GO
|
|
|
|
MERGE dbo.LocationRelationshipTypes AS target
|
|
USING (VALUES
|
|
(N'Adjacent To', 10), (N'Above', 20), (N'Below', 30), (N'Connected To', 40), (N'Visible From', 50),
|
|
(N'Audible From', 60), (N'Near', 70), (N'Leads To', 80), (N'Overlooks', 90),
|
|
(N'Same Building As', 100), (N'Same Area As', 110), (N'Secret Passage', 120), (N'Road Connection', 130)
|
|
) AS source (TypeName, SortOrder)
|
|
ON target.TypeName = source.TypeName
|
|
WHEN NOT MATCHED THEN INSERT (TypeName, SortOrder) VALUES (source.TypeName, source.SortOrder);
|
|
GO
|
|
|
|
CREATE OR ALTER VIEW dbo.LocationPaths
|
|
AS
|
|
WITH LocationTree AS
|
|
(
|
|
SELECT LocationID, ProjectID, ParentLocationID, LocationName, LocationTypeID, Description,
|
|
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,
|
|
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, LocationPath, SortPath, Depth
|
|
FROM LocationTree;
|
|
GO
|
|
|
|
CREATE OR ALTER PROCEDURE dbo.LocationLookup_All
|
|
AS
|
|
BEGIN
|
|
SET NOCOUNT ON;
|
|
SELECT LocationTypeID, TypeName, SortOrder, IsActive FROM dbo.LocationTypes WHERE IsActive = 1 ORDER BY SortOrder;
|
|
SELECT LocationRelationshipTypeID, TypeName, SortOrder, IsActive FROM dbo.LocationRelationshipTypes WHERE IsActive = 1 ORDER BY SortOrder;
|
|
END;
|
|
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.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.LocationTree_GetByProject
|
|
@ProjectID int
|
|
AS
|
|
BEGIN
|
|
SET NOCOUNT ON;
|
|
EXEC dbo.Location_ListByProject @ProjectID;
|
|
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.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
|
|
AS
|
|
BEGIN
|
|
SET NOCOUNT ON;
|
|
IF @LocationID IS NULL OR @LocationID = 0
|
|
BEGIN
|
|
INSERT dbo.Locations (ProjectID, ParentLocationID, LocationName, LocationTypeID, Description)
|
|
VALUES (@ProjectID, @ParentLocationID, @LocationName, @LocationTypeID, @Description);
|
|
SET @LocationID = CAST(SCOPE_IDENTITY() AS int);
|
|
END
|
|
ELSE
|
|
BEGIN
|
|
UPDATE dbo.Locations
|
|
SET ParentLocationID = @ParentLocationID, LocationName = @LocationName, LocationTypeID = @LocationTypeID,
|
|
Description = @Description, UpdatedDate = SYSUTCDATETIME()
|
|
WHERE LocationID = @LocationID;
|
|
END
|
|
SELECT @LocationID;
|
|
END;
|
|
GO
|
|
|
|
CREATE OR ALTER PROCEDURE dbo.Location_Archive
|
|
@LocationID int
|
|
AS
|
|
BEGIN
|
|
SET NOCOUNT ON;
|
|
UPDATE dbo.Locations SET IsArchived = 1, UpdatedDate = SYSUTCDATETIME() WHERE LocationID = @LocationID;
|
|
END;
|
|
GO
|
|
|
|
CREATE OR ALTER PROCEDURE dbo.LocationRelationship_ListByLocation
|
|
@LocationID int
|
|
AS
|
|
BEGIN
|
|
SET NOCOUNT ON;
|
|
SELECT lr.LocationRelationshipID, lr.FromLocationID, fromLocation.LocationName AS FromLocationName,
|
|
lr.ToLocationID, toLocation.LocationName AS ToLocationName, lr.LocationRelationshipTypeID,
|
|
lrt.TypeName AS RelationshipTypeName, lr.IsBidirectional, lr.Strength,
|
|
lr.CanHearNormalSpeech, lr.CanHearLoudNoise, lr.CanSeeMovement, lr.CanSeeClearly, lr.CanTravelDirectly,
|
|
lr.Notes, lr.CreatedDate, lr.UpdatedDate, lr.IsArchived
|
|
FROM dbo.LocationRelationships lr
|
|
INNER JOIN dbo.Locations fromLocation ON fromLocation.LocationID = lr.FromLocationID
|
|
INNER JOIN dbo.Locations toLocation ON toLocation.LocationID = lr.ToLocationID
|
|
INNER JOIN dbo.LocationRelationshipTypes lrt ON lrt.LocationRelationshipTypeID = lr.LocationRelationshipTypeID
|
|
WHERE lr.IsArchived = 0 AND @LocationID IN (lr.FromLocationID, lr.ToLocationID)
|
|
ORDER BY lrt.SortOrder, fromLocation.LocationName, toLocation.LocationName;
|
|
END;
|
|
GO
|
|
|
|
CREATE OR ALTER PROCEDURE dbo.LocationRelationship_Save
|
|
@LocationRelationshipID int = NULL,
|
|
@FromLocationID int,
|
|
@ToLocationID int,
|
|
@LocationRelationshipTypeID int,
|
|
@IsBidirectional bit,
|
|
@Strength int = NULL,
|
|
@CanHearNormalSpeech bit,
|
|
@CanHearLoudNoise bit,
|
|
@CanSeeMovement bit,
|
|
@CanSeeClearly bit,
|
|
@CanTravelDirectly bit,
|
|
@Notes nvarchar(max) = NULL
|
|
AS
|
|
BEGIN
|
|
SET NOCOUNT ON;
|
|
IF @LocationRelationshipID IS NULL OR @LocationRelationshipID = 0
|
|
BEGIN
|
|
INSERT dbo.LocationRelationships (FromLocationID, ToLocationID, LocationRelationshipTypeID, IsBidirectional, Strength,
|
|
CanHearNormalSpeech, CanHearLoudNoise, CanSeeMovement, CanSeeClearly, CanTravelDirectly, Notes)
|
|
VALUES (@FromLocationID, @ToLocationID, @LocationRelationshipTypeID, @IsBidirectional, @Strength,
|
|
@CanHearNormalSpeech, @CanHearLoudNoise, @CanSeeMovement, @CanSeeClearly, @CanTravelDirectly, @Notes);
|
|
SET @LocationRelationshipID = CAST(SCOPE_IDENTITY() AS int);
|
|
END
|
|
ELSE
|
|
BEGIN
|
|
UPDATE dbo.LocationRelationships
|
|
SET FromLocationID = @FromLocationID, ToLocationID = @ToLocationID,
|
|
LocationRelationshipTypeID = @LocationRelationshipTypeID, IsBidirectional = @IsBidirectional,
|
|
Strength = @Strength, CanHearNormalSpeech = @CanHearNormalSpeech, CanHearLoudNoise = @CanHearLoudNoise,
|
|
CanSeeMovement = @CanSeeMovement, CanSeeClearly = @CanSeeClearly, CanTravelDirectly = @CanTravelDirectly,
|
|
Notes = @Notes, UpdatedDate = SYSUTCDATETIME()
|
|
WHERE LocationRelationshipID = @LocationRelationshipID;
|
|
END
|
|
SELECT @LocationRelationshipID;
|
|
END;
|
|
GO
|
|
|
|
CREATE OR ALTER PROCEDURE dbo.Scene_Save
|
|
@SceneID int = NULL, @ChapterID int, @SceneNumber decimal(10,2), @SceneTitle nvarchar(200), @Summary nvarchar(max) = NULL,
|
|
@TimeModeID int, @StartDateTime datetime2 = NULL, @EndDateTime datetime2 = NULL, @DurationAmount decimal(10,2) = NULL,
|
|
@DurationUnitID int = NULL, @RelativeTimeText nvarchar(200) = NULL, @TimeConfidenceID int,
|
|
@ScenePurposeNotes nvarchar(max) = NULL, @SceneOutcomeNotes nvarchar(max) = NULL, @RevisionStatusID int,
|
|
@PrimaryLocationID int = NULL
|
|
AS
|
|
BEGIN
|
|
SET NOCOUNT ON;
|
|
DECLARE @NextSortOrder int;
|
|
IF @SceneID IS NULL OR @SceneID = 0
|
|
BEGIN
|
|
SELECT @NextSortOrder = ISNULL(MAX(SortOrder), 0) + 10 FROM dbo.Scenes WHERE ChapterID = @ChapterID;
|
|
INSERT dbo.Scenes (ChapterID, SceneNumber, SceneTitle, Summary, SortOrder, TimeModeID, StartDateTime, EndDateTime,
|
|
DurationAmount, DurationUnitID, RelativeTimeText, TimeConfidenceID, ScenePurposeNotes, SceneOutcomeNotes, RevisionStatusID, PrimaryLocationID)
|
|
VALUES (@ChapterID, @SceneNumber, @SceneTitle, @Summary, @NextSortOrder, @TimeModeID, @StartDateTime, @EndDateTime,
|
|
@DurationAmount, @DurationUnitID, @RelativeTimeText, @TimeConfidenceID, @ScenePurposeNotes, @SceneOutcomeNotes, @RevisionStatusID, @PrimaryLocationID);
|
|
SET @SceneID = CAST(SCOPE_IDENTITY() AS int);
|
|
END
|
|
ELSE
|
|
BEGIN
|
|
UPDATE dbo.Scenes
|
|
SET SceneNumber = @SceneNumber, SceneTitle = @SceneTitle, Summary = @Summary, TimeModeID = @TimeModeID,
|
|
StartDateTime = @StartDateTime, EndDateTime = @EndDateTime, DurationAmount = @DurationAmount, DurationUnitID = @DurationUnitID,
|
|
RelativeTimeText = @RelativeTimeText, TimeConfidenceID = @TimeConfidenceID, ScenePurposeNotes = @ScenePurposeNotes,
|
|
SceneOutcomeNotes = @SceneOutcomeNotes, RevisionStatusID = @RevisionStatusID, PrimaryLocationID = @PrimaryLocationID,
|
|
UpdatedDate = SYSUTCDATETIME()
|
|
WHERE SceneID = @SceneID;
|
|
END
|
|
SELECT @SceneID;
|
|
END;
|
|
GO
|
|
|
|
CREATE OR ALTER PROCEDURE dbo.SceneLocation_SavePrimary
|
|
@SceneID int,
|
|
@PrimaryLocationID int = NULL
|
|
AS
|
|
BEGIN
|
|
SET NOCOUNT ON;
|
|
UPDATE dbo.Scenes
|
|
SET PrimaryLocationID = @PrimaryLocationID, UpdatedDate = SYSUTCDATETIME()
|
|
WHERE SceneID = @SceneID;
|
|
END;
|
|
GO
|
|
|
|
CREATE OR ALTER PROCEDURE dbo.Scene_Get
|
|
@SceneID int
|
|
AS
|
|
BEGIN
|
|
SET NOCOUNT ON;
|
|
EXEC dbo.SceneMetric_EnsureDefaults @SceneID;
|
|
SELECT s.SceneID, s.ChapterID, s.SceneNumber, s.SceneTitle, s.Summary, s.POVCharacterID, s.PrimaryLocationID,
|
|
location.LocationName AS PrimaryLocationName, location.LocationPath AS PrimaryLocationPath,
|
|
s.SortOrder, s.TimeModeID, tm.TimeModeName, s.StartDateTime, s.EndDateTime, s.DurationAmount, s.DurationUnitID,
|
|
du.DurationUnitName, s.RelativeTimeText, s.TimeConfidenceID, tc.TimeConfidenceName, s.ScenePurposeNotes,
|
|
s.SceneOutcomeNotes, s.RevisionStatusID, rs.StatusName AS RevisionStatusName, s.CreatedDate, s.UpdatedDate, s.IsArchived
|
|
FROM dbo.Scenes s
|
|
INNER JOIN dbo.TimeModes tm ON tm.TimeModeID = s.TimeModeID
|
|
INNER JOIN dbo.TimeConfidences tc ON tc.TimeConfidenceID = s.TimeConfidenceID
|
|
INNER JOIN dbo.RevisionStatuses rs ON rs.RevisionStatusID = s.RevisionStatusID
|
|
LEFT JOIN dbo.DurationUnits du ON du.DurationUnitID = s.DurationUnitID
|
|
LEFT JOIN dbo.LocationPaths location ON location.LocationID = s.PrimaryLocationID
|
|
WHERE s.SceneID = @SceneID AND s.IsArchived = 0;
|
|
SELECT ScenePurposeTypeID FROM dbo.ScenePurposes WHERE SceneID = @SceneID;
|
|
EXEC dbo.SceneMetric_ListByScene @SceneID;
|
|
END;
|
|
GO
|
|
|
|
CREATE OR ALTER PROCEDURE dbo.SceneCharacter_Save
|
|
@SceneCharacterID int = NULL, @SceneID int, @CharacterID int, @RoleInSceneTypeID int = NULL, @PresenceTypeID int = NULL,
|
|
@LocationID int = NULL, @EntryLocationID int = NULL, @ExitLocationID int = NULL,
|
|
@AppearanceNotes nvarchar(max) = NULL, @OutfitDescription nvarchar(max) = NULL, @PhysicalCondition nvarchar(max) = NULL,
|
|
@EmotionalState nvarchar(max) = NULL, @KnowledgeNotes nvarchar(max) = NULL
|
|
AS
|
|
BEGIN
|
|
SET NOCOUNT ON;
|
|
IF @SceneCharacterID IS NULL OR @SceneCharacterID = 0
|
|
SELECT @SceneCharacterID = SceneCharacterID FROM dbo.SceneCharacters WHERE SceneID = @SceneID AND CharacterID = @CharacterID;
|
|
IF @SceneCharacterID IS NULL OR @SceneCharacterID = 0
|
|
BEGIN
|
|
INSERT dbo.SceneCharacters (SceneID, CharacterID, RoleInSceneTypeID, PresenceTypeID, LocationID, EntryLocationID, ExitLocationID,
|
|
AppearanceNotes, OutfitDescription, PhysicalCondition, EmotionalState, KnowledgeNotes)
|
|
VALUES (@SceneID, @CharacterID, @RoleInSceneTypeID, @PresenceTypeID, @LocationID, @EntryLocationID, @ExitLocationID,
|
|
@AppearanceNotes, @OutfitDescription, @PhysicalCondition, @EmotionalState, @KnowledgeNotes);
|
|
SET @SceneCharacterID = CAST(SCOPE_IDENTITY() AS int);
|
|
END
|
|
ELSE
|
|
BEGIN
|
|
UPDATE dbo.SceneCharacters
|
|
SET CharacterID = @CharacterID, RoleInSceneTypeID = @RoleInSceneTypeID, PresenceTypeID = @PresenceTypeID,
|
|
LocationID = @LocationID, EntryLocationID = @EntryLocationID, ExitLocationID = @ExitLocationID,
|
|
AppearanceNotes = @AppearanceNotes, OutfitDescription = @OutfitDescription, PhysicalCondition = @PhysicalCondition,
|
|
EmotionalState = @EmotionalState, KnowledgeNotes = @KnowledgeNotes, UpdatedDate = SYSUTCDATETIME()
|
|
WHERE SceneCharacterID = @SceneCharacterID;
|
|
END
|
|
SELECT @SceneCharacterID;
|
|
END;
|
|
GO
|
|
|
|
CREATE OR ALTER PROCEDURE dbo.SceneCharacter_ListByScene
|
|
@SceneID int
|
|
AS
|
|
BEGIN
|
|
SET NOCOUNT ON;
|
|
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, sc.CreatedDate, sc.UpdatedDate
|
|
FROM dbo.SceneCharacters sc
|
|
INNER JOIN dbo.Characters 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 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.SceneID = @SceneID AND c.IsArchived = 0
|
|
ORDER BY role.SortOrder, c.CharacterName;
|
|
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
|
|
AS
|
|
BEGIN
|
|
SET NOCOUNT ON;
|
|
IF @StoryAssetID IS NULL OR @StoryAssetID = 0
|
|
BEGIN
|
|
INSERT dbo.StoryAssets (ProjectID, AssetName, AssetKindID, Description, Importance, CurrentStateID, CurrentLocationID, IsResolved)
|
|
VALUES (@ProjectID, @AssetName, @AssetKindID, @Description, @Importance, @CurrentStateID, @CurrentLocationID, @IsResolved);
|
|
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, UpdatedDate = SYSUTCDATETIME()
|
|
WHERE StoryAssetID = @StoryAssetID;
|
|
END
|
|
SELECT @StoryAssetID;
|
|
END;
|
|
GO
|
|
|
|
CREATE OR ALTER PROCEDURE dbo.SceneAssetLocation_Save
|
|
@SceneAssetLocationID int = NULL,
|
|
@SceneID int,
|
|
@StoryAssetID int,
|
|
@LocationID int,
|
|
@Description nvarchar(max) = NULL,
|
|
@UpdateCurrentLocation bit = 1
|
|
AS
|
|
BEGIN
|
|
SET NOCOUNT ON;
|
|
IF @SceneAssetLocationID IS NULL OR @SceneAssetLocationID = 0
|
|
SELECT @SceneAssetLocationID = SceneAssetLocationID FROM dbo.SceneAssetLocations WHERE SceneID = @SceneID AND StoryAssetID = @StoryAssetID;
|
|
IF @SceneAssetLocationID IS NULL OR @SceneAssetLocationID = 0
|
|
BEGIN
|
|
INSERT dbo.SceneAssetLocations (SceneID, StoryAssetID, LocationID, Description)
|
|
VALUES (@SceneID, @StoryAssetID, @LocationID, @Description);
|
|
SET @SceneAssetLocationID = CAST(SCOPE_IDENTITY() AS int);
|
|
END
|
|
ELSE
|
|
BEGIN
|
|
UPDATE dbo.SceneAssetLocations SET LocationID = @LocationID, Description = @Description, UpdatedDate = SYSUTCDATETIME()
|
|
WHERE SceneAssetLocationID = @SceneAssetLocationID;
|
|
END
|
|
IF @UpdateCurrentLocation = 1
|
|
UPDATE dbo.StoryAssets SET CurrentLocationID = @LocationID, UpdatedDate = SYSUTCDATETIME() WHERE StoryAssetID = @StoryAssetID;
|
|
SELECT @SceneAssetLocationID;
|
|
END;
|
|
GO
|
|
|
|
CREATE OR ALTER PROCEDURE dbo.SceneAssetLocation_ListByScene
|
|
@SceneID int
|
|
AS
|
|
BEGIN
|
|
SET NOCOUNT ON;
|
|
SELECT sal.SceneAssetLocationID, sal.SceneID, sal.StoryAssetID, sa.AssetName, sal.LocationID,
|
|
location.LocationName, location.LocationPath, sal.Description, sal.CreatedDate, sal.UpdatedDate
|
|
FROM dbo.SceneAssetLocations sal
|
|
INNER JOIN dbo.StoryAssets sa ON sa.StoryAssetID = sal.StoryAssetID
|
|
INNER JOIN dbo.LocationPaths location ON location.LocationID = sal.LocationID
|
|
WHERE sal.SceneID = @SceneID
|
|
ORDER BY location.LocationPath, sa.AssetName;
|
|
END;
|
|
GO
|
|
|
|
CREATE OR ALTER PROCEDURE dbo.Timeline_GetByProject
|
|
@ProjectID int,
|
|
@BookID int = NULL
|
|
AS
|
|
BEGIN
|
|
SET NOCOUNT ON;
|
|
SELECT ProjectID, ProjectName, Description, CreatedDate, UpdatedDate, IsArchived FROM dbo.Projects WHERE ProjectID = @ProjectID AND IsArchived = 0;
|
|
SELECT BookID, ProjectID, BookTitle, BookNumber, Description, SortOrder, CreatedDate, UpdatedDate, IsArchived
|
|
FROM dbo.Books WHERE ProjectID = @ProjectID AND IsArchived = 0 AND (@BookID IS NULL OR BookID = @BookID) ORDER BY SortOrder, BookNumber;
|
|
SELECT c.ChapterID, c.BookID, c.ChapterNumber, c.ChapterTitle, c.POVCharacterID, c.Summary, c.SortOrder,
|
|
c.RevisionStatusID, rs.StatusName AS RevisionStatusName, c.CreatedDate, c.UpdatedDate, c.IsArchived
|
|
FROM dbo.Chapters c INNER JOIN dbo.Books b ON b.BookID = c.BookID INNER JOIN dbo.RevisionStatuses rs ON rs.RevisionStatusID = c.RevisionStatusID
|
|
WHERE b.ProjectID = @ProjectID AND c.IsArchived = 0 AND b.IsArchived = 0 AND (@BookID IS NULL OR b.BookID = @BookID)
|
|
ORDER BY b.SortOrder, c.SortOrder;
|
|
SELECT s.SceneID, s.ChapterID, s.SceneNumber, s.SceneTitle, s.Summary, s.POVCharacterID, s.PrimaryLocationID,
|
|
location.LocationName AS PrimaryLocationName, location.LocationPath AS PrimaryLocationPath,
|
|
s.SortOrder, s.TimeModeID, tm.TimeModeName, s.StartDateTime, s.EndDateTime, s.DurationAmount, s.DurationUnitID,
|
|
du.DurationUnitName, s.RelativeTimeText, s.TimeConfidenceID, tc.TimeConfidenceName, s.ScenePurposeNotes,
|
|
s.SceneOutcomeNotes, s.RevisionStatusID, rs.StatusName AS RevisionStatusName, 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
|
|
INNER JOIN dbo.TimeModes tm ON tm.TimeModeID = s.TimeModeID
|
|
INNER JOIN dbo.TimeConfidences tc ON tc.TimeConfidenceID = s.TimeConfidenceID
|
|
INNER JOIN dbo.RevisionStatuses rs ON rs.RevisionStatusID = s.RevisionStatusID
|
|
LEFT JOIN dbo.DurationUnits du ON du.DurationUnitID = s.DurationUnitID
|
|
LEFT JOIN dbo.LocationPaths location ON location.LocationID = s.PrimaryLocationID
|
|
WHERE b.ProjectID = @ProjectID AND s.IsArchived = 0 AND c.IsArchived = 0 AND b.IsArchived = 0 AND (@BookID IS NULL OR b.BookID = @BookID)
|
|
ORDER BY b.SortOrder, c.SortOrder, s.SortOrder;
|
|
SELECT sp.SceneID, spt.ScenePurposeTypeID, spt.PurposeName, spt.SortOrder
|
|
FROM dbo.ScenePurposes sp INNER JOIN dbo.ScenePurposeTypes spt ON spt.ScenePurposeTypeID = sp.ScenePurposeTypeID
|
|
INNER JOIN dbo.Scenes s ON s.SceneID = sp.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 (@BookID IS NULL OR b.BookID = @BookID)
|
|
ORDER BY spt.SortOrder;
|
|
SELECT MetricTypeID, ProjectID, MetricName, Description, MinValue, MaxValue, DefaultValue, SortOrder, IsActive FROM dbo.SceneMetricTypes WHERE IsActive = 1 AND (ProjectID IS NULL OR ProjectID = @ProjectID) ORDER BY SortOrder;
|
|
SELECT smv.SceneID, smv.MetricTypeID, smt.MetricName, smv.Value, smv.Notes
|
|
FROM dbo.SceneMetricValues smv INNER JOIN dbo.SceneMetricTypes smt ON smt.MetricTypeID = smv.MetricTypeID
|
|
INNER JOIN dbo.Scenes s ON s.SceneID = smv.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 (@BookID IS NULL OR b.BookID = @BookID);
|
|
SELECT pl.PlotLineID, pl.ProjectID, pl.BookID, b.BookTitle, pl.PlotLineName, pl.PlotLineTypeID, plt.TypeName AS PlotLineTypeName,
|
|
pl.Description, pl.ParentPlotLineID, parent.PlotLineName AS ParentPlotLineName, pl.SortOrder, pl.Colour, pl.IsMainPlot, pl.IsVisibleOnTimeline,
|
|
pl.CreatedDate, pl.UpdatedDate, pl.IsArchived
|
|
FROM dbo.PlotLines pl INNER JOIN dbo.PlotLineTypes plt ON plt.PlotLineTypeID = pl.PlotLineTypeID LEFT JOIN dbo.Books b ON b.BookID = pl.BookID LEFT JOIN dbo.PlotLines parent ON parent.PlotLineID = pl.ParentPlotLineID
|
|
WHERE pl.ProjectID = @ProjectID AND pl.IsArchived = 0 AND pl.IsVisibleOnTimeline = 1 AND (@BookID IS NULL OR pl.BookID IS NULL OR pl.BookID = @BookID)
|
|
ORDER BY pl.SortOrder, pl.PlotLineName;
|
|
SELECT te.ThreadEventID, te.PlotThreadID, pt.ThreadTitle, pt.PlotLineID, pl.PlotLineName, pl.Colour, te.SceneID, te.EventTypeID,
|
|
tet.TypeName AS EventTypeName, tet.MarkerText, te.EventTitle, te.EventDescription, te.CreatedDate, te.UpdatedDate
|
|
FROM dbo.ThreadEvents te INNER JOIN dbo.PlotThreads pt ON pt.PlotThreadID = te.PlotThreadID INNER JOIN dbo.PlotLines pl ON pl.PlotLineID = pt.PlotLineID
|
|
INNER JOIN dbo.ThreadEventTypes tet ON tet.ThreadEventTypeID = te.EventTypeID INNER JOIN dbo.Scenes s ON s.SceneID = te.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 pl.IsArchived = 0 AND pt.IsArchived = 0 AND (@BookID IS NULL OR b.BookID = @BookID)
|
|
ORDER BY pl.SortOrder, pt.ThreadTitle, b.SortOrder, c.SortOrder, s.SortOrder;
|
|
END;
|
|
GO
|
|
|
|
CREATE OR ALTER PROCEDURE dbo.LocationRelationship_ListByProject
|
|
@ProjectID int
|
|
AS
|
|
BEGIN
|
|
SET NOCOUNT ON;
|
|
SELECT lr.LocationRelationshipID, lr.FromLocationID, fromLocation.LocationName AS FromLocationName,
|
|
lr.ToLocationID, toLocation.LocationName AS ToLocationName, lr.LocationRelationshipTypeID,
|
|
lrt.TypeName AS RelationshipTypeName, lr.IsBidirectional, lr.Strength,
|
|
lr.CanHearNormalSpeech, lr.CanHearLoudNoise, lr.CanSeeMovement, lr.CanSeeClearly, lr.CanTravelDirectly,
|
|
lr.Notes, lr.CreatedDate, lr.UpdatedDate, lr.IsArchived
|
|
FROM dbo.LocationRelationships lr
|
|
INNER JOIN dbo.Locations fromLocation ON fromLocation.LocationID = lr.FromLocationID
|
|
INNER JOIN dbo.Locations toLocation ON toLocation.LocationID = lr.ToLocationID
|
|
INNER JOIN dbo.LocationRelationshipTypes lrt ON lrt.LocationRelationshipTypeID = lr.LocationRelationshipTypeID
|
|
WHERE lr.IsArchived = 0 AND fromLocation.ProjectID = @ProjectID
|
|
ORDER BY lrt.SortOrder, fromLocation.LocationName, toLocation.LocationName;
|
|
END;
|
|
GO
|
|
|
|
CREATE OR ALTER PROCEDURE dbo.LocationRelationship_Archive
|
|
@LocationRelationshipID int
|
|
AS
|
|
BEGIN
|
|
SET NOCOUNT ON;
|
|
UPDATE dbo.LocationRelationships
|
|
SET IsArchived = 1, UpdatedDate = SYSUTCDATETIME()
|
|
WHERE LocationRelationshipID = @LocationRelationshipID;
|
|
END;
|
|
GO
|
|
|
|
CREATE OR ALTER PROCEDURE dbo.SceneAssetLocation_Delete
|
|
@SceneAssetLocationID int
|
|
AS
|
|
BEGIN
|
|
SET NOCOUNT ON;
|
|
DELETE dbo.SceneAssetLocations WHERE SceneAssetLocationID = @SceneAssetLocationID;
|
|
END;
|
|
GO
|
|
|
|
CREATE OR ALTER PROCEDURE dbo.SceneAssetLocation_ListByLocation
|
|
@LocationID int
|
|
AS
|
|
BEGIN
|
|
SET NOCOUNT ON;
|
|
SELECT sal.SceneAssetLocationID, sal.SceneID, sal.StoryAssetID, sa.AssetName, sal.LocationID,
|
|
location.LocationName, location.LocationPath, sal.Description, sal.CreatedDate, sal.UpdatedDate
|
|
FROM dbo.SceneAssetLocations sal
|
|
INNER JOIN dbo.StoryAssets sa ON sa.StoryAssetID = sal.StoryAssetID
|
|
INNER JOIN dbo.LocationPaths location ON location.LocationID = sal.LocationID
|
|
WHERE sal.LocationID = @LocationID
|
|
ORDER BY sa.AssetName;
|
|
END;
|
|
GO
|
|
|
|
CREATE OR ALTER PROCEDURE dbo.LocationScene_ListByLocation
|
|
@LocationID int
|
|
AS
|
|
BEGIN
|
|
SET NOCOUNT ON;
|
|
SELECT s.SceneID, s.ChapterID, s.SceneNumber, s.SceneTitle, s.Summary, s.POVCharacterID, s.PrimaryLocationID,
|
|
location.LocationName AS PrimaryLocationName, location.LocationPath AS PrimaryLocationPath,
|
|
s.SortOrder, s.TimeModeID, tm.TimeModeName, s.StartDateTime, s.EndDateTime, s.DurationAmount, s.DurationUnitID,
|
|
du.DurationUnitName, s.RelativeTimeText, s.TimeConfidenceID, tc.TimeConfidenceName, s.ScenePurposeNotes,
|
|
s.SceneOutcomeNotes, s.RevisionStatusID, rs.StatusName AS RevisionStatusName, s.CreatedDate, s.UpdatedDate, s.IsArchived
|
|
FROM dbo.Scenes s
|
|
INNER JOIN dbo.TimeModes tm ON tm.TimeModeID = s.TimeModeID
|
|
INNER JOIN dbo.TimeConfidences tc ON tc.TimeConfidenceID = s.TimeConfidenceID
|
|
INNER JOIN dbo.RevisionStatuses rs ON rs.RevisionStatusID = s.RevisionStatusID
|
|
LEFT JOIN dbo.DurationUnits du ON du.DurationUnitID = s.DurationUnitID
|
|
LEFT JOIN dbo.LocationPaths location ON location.LocationID = s.PrimaryLocationID
|
|
WHERE s.PrimaryLocationID = @LocationID AND s.IsArchived = 0
|
|
ORDER BY s.SortOrder;
|
|
END;
|
|
GO
|
|
|
|
CREATE OR ALTER PROCEDURE dbo.LocationCharacter_ListByLocation
|
|
@LocationID int
|
|
AS
|
|
BEGIN
|
|
SET NOCOUNT ON;
|
|
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 sc.LocationID = @LocationID AND c.IsArchived = 0
|
|
ORDER BY b.SortOrder, ch.SortOrder, s.SortOrder, c.CharacterName;
|
|
END;
|
|
GO
|
|
|
|
CREATE OR ALTER PROCEDURE dbo.LocationCharacter_ListByScene
|
|
@SceneID int,
|
|
@LocationID int = NULL
|
|
AS
|
|
BEGIN
|
|
SET NOCOUNT ON;
|
|
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, sc.CreatedDate, sc.UpdatedDate
|
|
FROM dbo.SceneCharacters sc
|
|
INNER JOIN dbo.Characters 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 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.SceneID = @SceneID AND (@LocationID IS NULL OR sc.LocationID = @LocationID) AND c.IsArchived = 0
|
|
ORDER BY role.SortOrder, c.CharacterName;
|
|
END;
|
|
GO
|
|
|
|
CREATE OR ALTER PROCEDURE dbo.SceneAssetLocation_ListBySceneLocation
|
|
@SceneID int,
|
|
@LocationID int = NULL
|
|
AS
|
|
BEGIN
|
|
SET NOCOUNT ON;
|
|
SELECT sal.SceneAssetLocationID, sal.SceneID, sal.StoryAssetID, sa.AssetName, sal.LocationID,
|
|
location.LocationName, location.LocationPath, sal.Description, sal.CreatedDate, sal.UpdatedDate
|
|
FROM dbo.SceneAssetLocations sal
|
|
INNER JOIN dbo.StoryAssets sa ON sa.StoryAssetID = sal.StoryAssetID
|
|
INNER JOIN dbo.LocationPaths location ON location.LocationID = sal.LocationID
|
|
WHERE sal.SceneID = @SceneID AND (@LocationID IS NULL OR sal.LocationID = @LocationID)
|
|
ORDER BY location.LocationPath, sa.AssetName;
|
|
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.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.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.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
|