710 lines
32 KiB
Transact-SQL
710 lines
32 KiB
Transact-SQL
SET ANSI_NULLS ON;
|
|
GO
|
|
SET QUOTED_IDENTIFIER ON;
|
|
GO
|
|
|
|
IF OBJECT_ID(N'dbo.AssetKinds', N'U') IS NULL
|
|
BEGIN
|
|
CREATE TABLE dbo.AssetKinds
|
|
(
|
|
AssetKindID int IDENTITY(1,1) NOT NULL CONSTRAINT PK_AssetKinds PRIMARY KEY,
|
|
KindName nvarchar(100) NOT NULL,
|
|
SortOrder int NOT NULL,
|
|
IsActive bit NOT NULL CONSTRAINT DF_AssetKinds_IsActive DEFAULT 1,
|
|
CONSTRAINT UQ_AssetKinds_KindName UNIQUE (KindName)
|
|
);
|
|
END;
|
|
GO
|
|
|
|
IF OBJECT_ID(N'dbo.AssetStates', N'U') IS NULL
|
|
BEGIN
|
|
CREATE TABLE dbo.AssetStates
|
|
(
|
|
AssetStateID int IDENTITY(1,1) NOT NULL CONSTRAINT PK_AssetStates PRIMARY KEY,
|
|
ProjectID int NULL,
|
|
AssetKindID int NULL,
|
|
StateName nvarchar(100) NOT NULL,
|
|
SortOrder int NOT NULL,
|
|
Description nvarchar(max) NULL,
|
|
IsResolvedState bit NOT NULL CONSTRAINT DF_AssetStates_IsResolvedState DEFAULT 0,
|
|
IsActive bit NOT NULL CONSTRAINT DF_AssetStates_IsActive DEFAULT 1,
|
|
CONSTRAINT FK_AssetStates_Projects FOREIGN KEY (ProjectID) REFERENCES dbo.Projects(ProjectID),
|
|
CONSTRAINT FK_AssetStates_AssetKinds FOREIGN KEY (AssetKindID) REFERENCES dbo.AssetKinds(AssetKindID)
|
|
);
|
|
END;
|
|
GO
|
|
|
|
IF OBJECT_ID(N'dbo.StoryAssets', N'U') IS NULL
|
|
BEGIN
|
|
CREATE TABLE dbo.StoryAssets
|
|
(
|
|
StoryAssetID int IDENTITY(1,1) NOT NULL CONSTRAINT PK_StoryAssets PRIMARY KEY,
|
|
ProjectID int NOT NULL,
|
|
AssetName nvarchar(200) NOT NULL,
|
|
AssetKindID int NOT NULL,
|
|
Description nvarchar(max) NULL,
|
|
Importance int NOT NULL CONSTRAINT DF_StoryAssets_Importance DEFAULT 5,
|
|
CurrentStateID int NULL,
|
|
CurrentLocationID int NULL,
|
|
IsResolved bit NOT NULL CONSTRAINT DF_StoryAssets_IsResolved DEFAULT 0,
|
|
CreatedDate datetime2 NOT NULL CONSTRAINT DF_StoryAssets_CreatedDate DEFAULT SYSUTCDATETIME(),
|
|
UpdatedDate datetime2 NOT NULL CONSTRAINT DF_StoryAssets_UpdatedDate DEFAULT SYSUTCDATETIME(),
|
|
IsArchived bit NOT NULL CONSTRAINT DF_StoryAssets_IsArchived DEFAULT 0,
|
|
CONSTRAINT FK_StoryAssets_Projects FOREIGN KEY (ProjectID) REFERENCES dbo.Projects(ProjectID),
|
|
CONSTRAINT FK_StoryAssets_AssetKinds FOREIGN KEY (AssetKindID) REFERENCES dbo.AssetKinds(AssetKindID),
|
|
CONSTRAINT FK_StoryAssets_CurrentState FOREIGN KEY (CurrentStateID) REFERENCES dbo.AssetStates(AssetStateID),
|
|
CONSTRAINT CK_StoryAssets_Importance CHECK (Importance BETWEEN 1 AND 10)
|
|
);
|
|
END;
|
|
GO
|
|
|
|
IF OBJECT_ID(N'dbo.AssetEventTypes', N'U') IS NULL
|
|
BEGIN
|
|
CREATE TABLE dbo.AssetEventTypes
|
|
(
|
|
AssetEventTypeID int IDENTITY(1,1) NOT NULL CONSTRAINT PK_AssetEventTypes PRIMARY KEY,
|
|
TypeName nvarchar(100) NOT NULL,
|
|
MarkerText nvarchar(10) NOT NULL,
|
|
SortOrder int NOT NULL,
|
|
IsActive bit NOT NULL CONSTRAINT DF_AssetEventTypes_IsActive DEFAULT 1,
|
|
CONSTRAINT UQ_AssetEventTypes_TypeName UNIQUE (TypeName)
|
|
);
|
|
END;
|
|
GO
|
|
|
|
IF OBJECT_ID(N'dbo.AssetEvents', N'U') IS NULL
|
|
BEGIN
|
|
CREATE TABLE dbo.AssetEvents
|
|
(
|
|
AssetEventID int IDENTITY(1,1) NOT NULL CONSTRAINT PK_AssetEvents PRIMARY KEY,
|
|
StoryAssetID int NOT NULL,
|
|
SceneID int NOT NULL,
|
|
AssetEventTypeID int NOT NULL,
|
|
FromStateID int NULL,
|
|
ToStateID int NULL,
|
|
EventTitle nvarchar(200) NOT NULL,
|
|
EventDescription nvarchar(max) NULL,
|
|
CreatedDate datetime2 NOT NULL CONSTRAINT DF_AssetEvents_CreatedDate DEFAULT SYSUTCDATETIME(),
|
|
UpdatedDate datetime2 NOT NULL CONSTRAINT DF_AssetEvents_UpdatedDate DEFAULT SYSUTCDATETIME(),
|
|
CONSTRAINT FK_AssetEvents_StoryAssets FOREIGN KEY (StoryAssetID) REFERENCES dbo.StoryAssets(StoryAssetID),
|
|
CONSTRAINT FK_AssetEvents_Scenes FOREIGN KEY (SceneID) REFERENCES dbo.Scenes(SceneID),
|
|
CONSTRAINT FK_AssetEvents_AssetEventTypes FOREIGN KEY (AssetEventTypeID) REFERENCES dbo.AssetEventTypes(AssetEventTypeID),
|
|
CONSTRAINT FK_AssetEvents_FromState FOREIGN KEY (FromStateID) REFERENCES dbo.AssetStates(AssetStateID),
|
|
CONSTRAINT FK_AssetEvents_ToState FOREIGN KEY (ToStateID) REFERENCES dbo.AssetStates(AssetStateID)
|
|
);
|
|
END;
|
|
GO
|
|
|
|
IF OBJECT_ID(N'dbo.AssetDependencyTypes', N'U') IS NULL
|
|
BEGIN
|
|
CREATE TABLE dbo.AssetDependencyTypes
|
|
(
|
|
AssetDependencyTypeID int IDENTITY(1,1) NOT NULL CONSTRAINT PK_AssetDependencyTypes PRIMARY KEY,
|
|
TypeName nvarchar(100) NOT NULL,
|
|
SortOrder int NOT NULL,
|
|
IsValidatingDependency bit NOT NULL CONSTRAINT DF_AssetDependencyTypes_IsValidatingDependency DEFAULT 0,
|
|
IsActive bit NOT NULL CONSTRAINT DF_AssetDependencyTypes_IsActive DEFAULT 1,
|
|
CONSTRAINT UQ_AssetDependencyTypes_TypeName UNIQUE (TypeName)
|
|
);
|
|
END;
|
|
GO
|
|
|
|
IF OBJECT_ID(N'dbo.AssetDependencies', N'U') IS NULL
|
|
BEGIN
|
|
CREATE TABLE dbo.AssetDependencies
|
|
(
|
|
AssetDependencyID int IDENTITY(1,1) NOT NULL CONSTRAINT PK_AssetDependencies PRIMARY KEY,
|
|
SourceAssetID int NOT NULL,
|
|
TargetAssetID int NOT NULL,
|
|
AssetDependencyTypeID int NOT NULL,
|
|
SourceRequiredStateID int NULL,
|
|
TargetBlockedStateID int NULL,
|
|
Description nvarchar(max) NULL,
|
|
CreatedDate datetime2 NOT NULL CONSTRAINT DF_AssetDependencies_CreatedDate DEFAULT SYSUTCDATETIME(),
|
|
UpdatedDate datetime2 NOT NULL CONSTRAINT DF_AssetDependencies_UpdatedDate DEFAULT SYSUTCDATETIME(),
|
|
IsArchived bit NOT NULL CONSTRAINT DF_AssetDependencies_IsArchived DEFAULT 0,
|
|
CONSTRAINT FK_AssetDependencies_SourceAsset FOREIGN KEY (SourceAssetID) REFERENCES dbo.StoryAssets(StoryAssetID),
|
|
CONSTRAINT FK_AssetDependencies_TargetAsset FOREIGN KEY (TargetAssetID) REFERENCES dbo.StoryAssets(StoryAssetID),
|
|
CONSTRAINT FK_AssetDependencies_Type FOREIGN KEY (AssetDependencyTypeID) REFERENCES dbo.AssetDependencyTypes(AssetDependencyTypeID),
|
|
CONSTRAINT FK_AssetDependencies_SourceState FOREIGN KEY (SourceRequiredStateID) REFERENCES dbo.AssetStates(AssetStateID),
|
|
CONSTRAINT FK_AssetDependencies_TargetState FOREIGN KEY (TargetBlockedStateID) REFERENCES dbo.AssetStates(AssetStateID)
|
|
);
|
|
END;
|
|
GO
|
|
|
|
IF OBJECT_ID(N'dbo.AssetCustodyEventTypes', N'U') IS NULL
|
|
BEGIN
|
|
CREATE TABLE dbo.AssetCustodyEventTypes
|
|
(
|
|
AssetCustodyEventTypeID int IDENTITY(1,1) NOT NULL CONSTRAINT PK_AssetCustodyEventTypes PRIMARY KEY,
|
|
TypeName nvarchar(100) NOT NULL,
|
|
SortOrder int NOT NULL,
|
|
IsActive bit NOT NULL CONSTRAINT DF_AssetCustodyEventTypes_IsActive DEFAULT 1,
|
|
CONSTRAINT UQ_AssetCustodyEventTypes_TypeName UNIQUE (TypeName)
|
|
);
|
|
END;
|
|
GO
|
|
|
|
IF OBJECT_ID(N'dbo.CustodyRoles', N'U') IS NULL
|
|
BEGIN
|
|
CREATE TABLE dbo.CustodyRoles
|
|
(
|
|
CustodyRoleID int IDENTITY(1,1) NOT NULL CONSTRAINT PK_CustodyRoles PRIMARY KEY,
|
|
RoleName nvarchar(100) NOT NULL,
|
|
SortOrder int NOT NULL,
|
|
IsActive bit NOT NULL CONSTRAINT DF_CustodyRoles_IsActive DEFAULT 1,
|
|
CONSTRAINT UQ_CustodyRoles_RoleName UNIQUE (RoleName)
|
|
);
|
|
END;
|
|
GO
|
|
|
|
IF OBJECT_ID(N'dbo.AssetCustodyEvents', N'U') IS NULL
|
|
BEGIN
|
|
CREATE TABLE dbo.AssetCustodyEvents
|
|
(
|
|
AssetCustodyEventID int IDENTITY(1,1) NOT NULL CONSTRAINT PK_AssetCustodyEvents PRIMARY KEY,
|
|
StoryAssetID int NOT NULL,
|
|
SceneID int NOT NULL,
|
|
AssetCustodyEventTypeID int NOT NULL,
|
|
Description nvarchar(max) NULL,
|
|
CreatedDate datetime2 NOT NULL CONSTRAINT DF_AssetCustodyEvents_CreatedDate DEFAULT SYSUTCDATETIME(),
|
|
UpdatedDate datetime2 NOT NULL CONSTRAINT DF_AssetCustodyEvents_UpdatedDate DEFAULT SYSUTCDATETIME(),
|
|
CONSTRAINT FK_AssetCustodyEvents_StoryAssets FOREIGN KEY (StoryAssetID) REFERENCES dbo.StoryAssets(StoryAssetID),
|
|
CONSTRAINT FK_AssetCustodyEvents_Scenes FOREIGN KEY (SceneID) REFERENCES dbo.Scenes(SceneID),
|
|
CONSTRAINT FK_AssetCustodyEvents_Type FOREIGN KEY (AssetCustodyEventTypeID) REFERENCES dbo.AssetCustodyEventTypes(AssetCustodyEventTypeID)
|
|
);
|
|
END;
|
|
GO
|
|
|
|
IF OBJECT_ID(N'dbo.AssetCustodyEventCharacters', N'U') IS NULL
|
|
BEGIN
|
|
CREATE TABLE dbo.AssetCustodyEventCharacters
|
|
(
|
|
AssetCustodyEventCharacterID int IDENTITY(1,1) NOT NULL CONSTRAINT PK_AssetCustodyEventCharacters PRIMARY KEY,
|
|
AssetCustodyEventID int NOT NULL,
|
|
CharacterID int NULL,
|
|
CharacterNameText nvarchar(200) NULL,
|
|
CustodyRoleID int NOT NULL,
|
|
CONSTRAINT FK_AssetCustodyEventCharacters_Events FOREIGN KEY (AssetCustodyEventID) REFERENCES dbo.AssetCustodyEvents(AssetCustodyEventID) ON DELETE CASCADE,
|
|
CONSTRAINT FK_AssetCustodyEventCharacters_Roles FOREIGN KEY (CustodyRoleID) REFERENCES dbo.CustodyRoles(CustodyRoleID)
|
|
);
|
|
END;
|
|
GO
|
|
|
|
IF NOT EXISTS (SELECT 1 FROM sys.indexes WHERE name = N'IX_StoryAssets_ProjectID' AND object_id = OBJECT_ID(N'dbo.StoryAssets'))
|
|
CREATE INDEX IX_StoryAssets_ProjectID ON dbo.StoryAssets(ProjectID, IsArchived, Importance);
|
|
GO
|
|
IF NOT EXISTS (SELECT 1 FROM sys.indexes WHERE name = N'IX_AssetEvents_SceneID' AND object_id = OBJECT_ID(N'dbo.AssetEvents'))
|
|
CREATE INDEX IX_AssetEvents_SceneID ON dbo.AssetEvents(SceneID, StoryAssetID, AssetEventTypeID);
|
|
GO
|
|
IF NOT EXISTS (SELECT 1 FROM sys.indexes WHERE name = N'IX_AssetDependencies_TargetAssetID' AND object_id = OBJECT_ID(N'dbo.AssetDependencies'))
|
|
CREATE INDEX IX_AssetDependencies_TargetAssetID ON dbo.AssetDependencies(TargetAssetID, IsArchived, AssetDependencyTypeID);
|
|
GO
|
|
IF NOT EXISTS (SELECT 1 FROM sys.indexes WHERE name = N'IX_AssetCustodyEvents_SceneID' AND object_id = OBJECT_ID(N'dbo.AssetCustodyEvents'))
|
|
CREATE INDEX IX_AssetCustodyEvents_SceneID ON dbo.AssetCustodyEvents(SceneID, StoryAssetID);
|
|
GO
|
|
|
|
MERGE dbo.AssetKinds AS target
|
|
USING (VALUES
|
|
(N'Physical Object', 10), (N'Clothing', 20), (N'Document', 30), (N'Evidence', 40),
|
|
(N'Clue', 50), (N'Secret', 60), (N'Fact', 70), (N'Emotional Fact', 80),
|
|
(N'Relationship Truth', 90), (N'Plan', 100), (N'Threat', 110), (N'Promise', 120),
|
|
(N'Location Item', 130), (N'Vehicle', 140), (N'Money Trail', 150), (N'Other', 160)
|
|
) AS source (KindName, SortOrder)
|
|
ON target.KindName = source.KindName
|
|
WHEN NOT MATCHED THEN INSERT (KindName, SortOrder) VALUES (source.KindName, source.SortOrder);
|
|
GO
|
|
|
|
MERGE dbo.AssetStates AS target
|
|
USING (VALUES
|
|
(N'Unknown', 10, 0), (N'Mentioned', 20, 0), (N'Known To Exist', 30, 0), (N'Found', 40, 0),
|
|
(N'Possessed', 50, 0), (N'Investigated', 60, 0), (N'Identified', 70, 0), (N'Used', 80, 0),
|
|
(N'Lost', 90, 0), (N'Hidden', 100, 0), (N'Revealed', 110, 0), (N'Opened', 120, 0),
|
|
(N'Destroyed', 130, 1), (N'Resolved', 140, 1)
|
|
) AS source (StateName, SortOrder, IsResolvedState)
|
|
ON target.ProjectID IS NULL AND target.AssetKindID IS NULL AND target.StateName = source.StateName
|
|
WHEN NOT MATCHED THEN INSERT (StateName, SortOrder, IsResolvedState) VALUES (source.StateName, source.SortOrder, source.IsResolvedState);
|
|
GO
|
|
|
|
MERGE dbo.AssetEventTypes AS target
|
|
USING (VALUES
|
|
(N'Mentioned', N'M', 10), (N'First Mentioned', N'I', 20), (N'Found', N'F', 30),
|
|
(N'Lost', N'L', 40), (N'Given', N'G', 50), (N'Taken', N'T', 60), (N'Stolen', N'S', 70),
|
|
(N'Hidden', N'H', 80), (N'Moved', N'Mv', 90), (N'Used', N'U', 100), (N'Opened', N'O', 110),
|
|
(N'Destroyed', N'X', 120), (N'Revealed', N'R', 130), (N'Investigated', N'?', 140),
|
|
(N'State Changed', N'C', 150), (N'Custody Changed', N'Cc', 160), (N'Location Changed', N'Loc', 170),
|
|
(N'Note', N'N', 180)
|
|
) AS source (TypeName, MarkerText, SortOrder)
|
|
ON target.TypeName = source.TypeName
|
|
WHEN NOT MATCHED THEN INSERT (TypeName, MarkerText, SortOrder) VALUES (source.TypeName, source.MarkerText, source.SortOrder);
|
|
GO
|
|
|
|
MERGE dbo.AssetDependencyTypes AS target
|
|
USING (VALUES
|
|
(N'Requires', 10, 1), (N'Unlocks', 20, 1), (N'Reveals', 30, 0), (N'Leads To', 40, 0),
|
|
(N'Contains', 50, 0), (N'Contradicts', 60, 0), (N'Supports', 70, 0), (N'Related To', 80, 0)
|
|
) AS source (TypeName, SortOrder, IsValidatingDependency)
|
|
ON target.TypeName = source.TypeName
|
|
WHEN NOT MATCHED THEN INSERT (TypeName, SortOrder, IsValidatingDependency) VALUES (source.TypeName, source.SortOrder, source.IsValidatingDependency);
|
|
GO
|
|
|
|
MERGE dbo.AssetCustodyEventTypes AS target
|
|
USING (VALUES
|
|
(N'Has', 10), (N'Shared Custody', 20), (N'Given To', 30), (N'Taken From', 40),
|
|
(N'Stolen By', 50), (N'Hidden By', 60), (N'Found By', 70), (N'Used By', 80),
|
|
(N'Observed By', 90), (N'Left With', 100), (N'Lost By', 110), (N'Returned To', 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.CustodyRoles AS target
|
|
USING (VALUES
|
|
(N'Has Custody', 10), (N'Shared Custody', 20), (N'Handles', 30), (N'Uses', 40),
|
|
(N'Observes', 50), (N'Transfers From', 60), (N'Transfers To', 70), (N'Steals From', 80),
|
|
(N'Hides', 90), (N'Finds', 100)
|
|
) AS source (RoleName, SortOrder)
|
|
ON target.RoleName = source.RoleName
|
|
WHEN NOT MATCHED THEN INSERT (RoleName, SortOrder) VALUES (source.RoleName, source.SortOrder);
|
|
GO
|
|
|
|
CREATE OR ALTER PROCEDURE dbo.AssetLookup_All
|
|
AS
|
|
BEGIN
|
|
SET NOCOUNT ON;
|
|
SELECT AssetKindID, KindName, SortOrder, IsActive FROM dbo.AssetKinds WHERE IsActive = 1 ORDER BY SortOrder;
|
|
SELECT AssetStateID, ProjectID, AssetKindID, StateName, SortOrder, Description, IsResolvedState, IsActive FROM dbo.AssetStates WHERE IsActive = 1 ORDER BY SortOrder;
|
|
SELECT AssetEventTypeID, TypeName, MarkerText, SortOrder, IsActive FROM dbo.AssetEventTypes WHERE IsActive = 1 ORDER BY SortOrder;
|
|
SELECT AssetDependencyTypeID, TypeName, SortOrder, IsValidatingDependency, IsActive FROM dbo.AssetDependencyTypes WHERE IsActive = 1 ORDER BY SortOrder;
|
|
SELECT AssetCustodyEventTypeID, TypeName, SortOrder, IsActive FROM dbo.AssetCustodyEventTypes WHERE IsActive = 1 ORDER BY SortOrder;
|
|
SELECT CustodyRoleID, RoleName, SortOrder, IsActive FROM dbo.CustodyRoles WHERE IsActive = 1 ORDER BY SortOrder;
|
|
END;
|
|
GO
|
|
|
|
CREATE OR ALTER PROCEDURE dbo.AssetKind_List AS BEGIN SET NOCOUNT ON; SELECT AssetKindID, KindName, SortOrder, IsActive FROM dbo.AssetKinds WHERE IsActive = 1 ORDER BY SortOrder; END;
|
|
GO
|
|
CREATE OR ALTER PROCEDURE dbo.AssetState_List AS BEGIN SET NOCOUNT ON; SELECT AssetStateID, ProjectID, AssetKindID, StateName, SortOrder, Description, IsResolvedState, IsActive FROM dbo.AssetStates WHERE IsActive = 1 ORDER BY SortOrder; END;
|
|
GO
|
|
CREATE OR ALTER PROCEDURE dbo.AssetEventType_List AS BEGIN SET NOCOUNT ON; SELECT AssetEventTypeID, TypeName, MarkerText, SortOrder, IsActive FROM dbo.AssetEventTypes WHERE IsActive = 1 ORDER BY SortOrder; END;
|
|
GO
|
|
CREATE OR ALTER PROCEDURE dbo.AssetDependencyType_List AS BEGIN SET NOCOUNT ON; SELECT AssetDependencyTypeID, TypeName, SortOrder, IsValidatingDependency, IsActive FROM dbo.AssetDependencyTypes WHERE IsActive = 1 ORDER BY SortOrder; END;
|
|
GO
|
|
CREATE OR ALTER PROCEDURE dbo.AssetCustodyEventType_List AS BEGIN SET NOCOUNT ON; SELECT AssetCustodyEventTypeID, TypeName, SortOrder, IsActive FROM dbo.AssetCustodyEventTypes WHERE IsActive = 1 ORDER BY SortOrder; END;
|
|
GO
|
|
CREATE OR ALTER PROCEDURE dbo.CustodyRole_List AS BEGIN SET NOCOUNT ON; SELECT CustodyRoleID, RoleName, SortOrder, IsActive FROM dbo.CustodyRoles WHERE IsActive = 1 ORDER BY SortOrder; 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, 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
|
|
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, 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
|
|
WHERE sa.StoryAssetID = @StoryAssetID AND sa.IsArchived = 0;
|
|
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,
|
|
@IsResolved bit
|
|
AS
|
|
BEGIN
|
|
SET NOCOUNT ON;
|
|
|
|
IF @StoryAssetID IS NULL OR @StoryAssetID = 0
|
|
BEGIN
|
|
INSERT dbo.StoryAssets (ProjectID, AssetName, AssetKindID, Description, Importance, CurrentStateID, IsResolved)
|
|
VALUES (@ProjectID, @AssetName, @AssetKindID, @Description, @Importance, @CurrentStateID, @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,
|
|
IsResolved = @IsResolved,
|
|
UpdatedDate = SYSUTCDATETIME()
|
|
WHERE StoryAssetID = @StoryAssetID;
|
|
END
|
|
|
|
SELECT @StoryAssetID;
|
|
END;
|
|
GO
|
|
|
|
CREATE OR ALTER PROCEDURE dbo.StoryAsset_Archive
|
|
@StoryAssetID int
|
|
AS
|
|
BEGIN
|
|
SET NOCOUNT ON;
|
|
UPDATE dbo.StoryAssets SET IsArchived = 1, UpdatedDate = SYSUTCDATETIME() WHERE StoryAssetID = @StoryAssetID;
|
|
END;
|
|
GO
|
|
|
|
CREATE OR ALTER PROCEDURE dbo.AssetEvent_ListByScene
|
|
@SceneID int
|
|
AS
|
|
BEGIN
|
|
SET NOCOUNT ON;
|
|
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
|
|
LEFT JOIN dbo.AssetStates fs ON fs.AssetStateID = ae.FromStateID
|
|
LEFT JOIN dbo.AssetStates ts ON ts.AssetStateID = ae.ToStateID
|
|
WHERE ae.SceneID = @SceneID
|
|
ORDER BY sa.AssetName, ae.AssetEventID;
|
|
END;
|
|
GO
|
|
|
|
CREATE OR ALTER PROCEDURE dbo.AssetEvent_ListByAsset
|
|
@StoryAssetID int
|
|
AS
|
|
BEGIN
|
|
SET NOCOUNT ON;
|
|
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,
|
|
b.BookTitle, c.ChapterNumber, s.SceneNumber, s.SceneTitle
|
|
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 ae.StoryAssetID = @StoryAssetID
|
|
ORDER BY b.SortOrder, c.SortOrder, s.SortOrder, ae.AssetEventID;
|
|
END;
|
|
GO
|
|
|
|
CREATE OR ALTER PROCEDURE dbo.AssetEvent_Save
|
|
@AssetEventID int = NULL,
|
|
@StoryAssetID int,
|
|
@SceneID int,
|
|
@AssetEventTypeID int,
|
|
@FromStateID int = NULL,
|
|
@ToStateID int = NULL,
|
|
@EventTitle nvarchar(200),
|
|
@EventDescription nvarchar(max) = NULL
|
|
AS
|
|
BEGIN
|
|
SET NOCOUNT ON;
|
|
|
|
IF @AssetEventID IS NULL OR @AssetEventID = 0
|
|
BEGIN
|
|
INSERT dbo.AssetEvents (StoryAssetID, SceneID, AssetEventTypeID, FromStateID, ToStateID, EventTitle, EventDescription)
|
|
VALUES (@StoryAssetID, @SceneID, @AssetEventTypeID, @FromStateID, @ToStateID, @EventTitle, @EventDescription);
|
|
SET @AssetEventID = CAST(SCOPE_IDENTITY() AS int);
|
|
END
|
|
ELSE
|
|
BEGIN
|
|
UPDATE dbo.AssetEvents
|
|
SET StoryAssetID = @StoryAssetID,
|
|
SceneID = @SceneID,
|
|
AssetEventTypeID = @AssetEventTypeID,
|
|
FromStateID = @FromStateID,
|
|
ToStateID = @ToStateID,
|
|
EventTitle = @EventTitle,
|
|
EventDescription = @EventDescription,
|
|
UpdatedDate = SYSUTCDATETIME()
|
|
WHERE AssetEventID = @AssetEventID;
|
|
END
|
|
|
|
IF @ToStateID IS NOT NULL
|
|
BEGIN
|
|
UPDATE dbo.StoryAssets
|
|
SET CurrentStateID = @ToStateID,
|
|
IsResolved = CASE WHEN EXISTS (SELECT 1 FROM dbo.AssetStates WHERE AssetStateID = @ToStateID AND IsResolvedState = 1) THEN 1 ELSE IsResolved END,
|
|
UpdatedDate = SYSUTCDATETIME()
|
|
WHERE StoryAssetID = @StoryAssetID;
|
|
END
|
|
|
|
SELECT @AssetEventID;
|
|
END;
|
|
GO
|
|
|
|
CREATE OR ALTER PROCEDURE dbo.AssetEvent_Delete
|
|
@AssetEventID int
|
|
AS
|
|
BEGIN
|
|
SET NOCOUNT ON;
|
|
DELETE FROM dbo.AssetEvents WHERE AssetEventID = @AssetEventID;
|
|
END;
|
|
GO
|
|
|
|
CREATE OR ALTER PROCEDURE dbo.AssetDependency_ListByAsset
|
|
@StoryAssetID int
|
|
AS
|
|
BEGIN
|
|
SET NOCOUNT ON;
|
|
SELECT ad.AssetDependencyID, ad.SourceAssetID, source.AssetName AS SourceAssetName,
|
|
ad.TargetAssetID, target.AssetName AS TargetAssetName,
|
|
ad.AssetDependencyTypeID, adt.TypeName AS DependencyTypeName, adt.IsValidatingDependency,
|
|
ad.SourceRequiredStateID, srs.StateName AS SourceRequiredStateName,
|
|
ad.TargetBlockedStateID, tbs.StateName AS TargetBlockedStateName,
|
|
ad.Description, ad.CreatedDate, ad.UpdatedDate, ad.IsArchived
|
|
FROM dbo.AssetDependencies ad
|
|
INNER JOIN dbo.StoryAssets source ON source.StoryAssetID = ad.SourceAssetID
|
|
INNER JOIN dbo.StoryAssets target ON target.StoryAssetID = ad.TargetAssetID
|
|
INNER JOIN dbo.AssetDependencyTypes adt ON adt.AssetDependencyTypeID = ad.AssetDependencyTypeID
|
|
LEFT JOIN dbo.AssetStates srs ON srs.AssetStateID = ad.SourceRequiredStateID
|
|
LEFT JOIN dbo.AssetStates tbs ON tbs.AssetStateID = ad.TargetBlockedStateID
|
|
WHERE ad.IsArchived = 0 AND (@StoryAssetID IN (ad.SourceAssetID, ad.TargetAssetID))
|
|
ORDER BY ad.AssetDependencyID DESC;
|
|
END;
|
|
GO
|
|
|
|
CREATE OR ALTER PROCEDURE dbo.AssetDependency_Save
|
|
@AssetDependencyID int = NULL,
|
|
@SourceAssetID int,
|
|
@TargetAssetID int,
|
|
@AssetDependencyTypeID int,
|
|
@SourceRequiredStateID int = NULL,
|
|
@TargetBlockedStateID int = NULL,
|
|
@Description nvarchar(max) = NULL
|
|
AS
|
|
BEGIN
|
|
SET NOCOUNT ON;
|
|
IF @AssetDependencyID IS NULL OR @AssetDependencyID = 0
|
|
BEGIN
|
|
INSERT dbo.AssetDependencies (SourceAssetID, TargetAssetID, AssetDependencyTypeID, SourceRequiredStateID, TargetBlockedStateID, Description)
|
|
VALUES (@SourceAssetID, @TargetAssetID, @AssetDependencyTypeID, @SourceRequiredStateID, @TargetBlockedStateID, @Description);
|
|
SET @AssetDependencyID = CAST(SCOPE_IDENTITY() AS int);
|
|
END
|
|
ELSE
|
|
BEGIN
|
|
UPDATE dbo.AssetDependencies
|
|
SET SourceAssetID = @SourceAssetID,
|
|
TargetAssetID = @TargetAssetID,
|
|
AssetDependencyTypeID = @AssetDependencyTypeID,
|
|
SourceRequiredStateID = @SourceRequiredStateID,
|
|
TargetBlockedStateID = @TargetBlockedStateID,
|
|
Description = @Description,
|
|
UpdatedDate = SYSUTCDATETIME()
|
|
WHERE AssetDependencyID = @AssetDependencyID;
|
|
END
|
|
SELECT @AssetDependencyID;
|
|
END;
|
|
GO
|
|
|
|
CREATE OR ALTER PROCEDURE dbo.AssetDependency_Delete
|
|
@AssetDependencyID int
|
|
AS
|
|
BEGIN
|
|
SET NOCOUNT ON;
|
|
UPDATE dbo.AssetDependencies SET IsArchived = 1, UpdatedDate = SYSUTCDATETIME() WHERE AssetDependencyID = @AssetDependencyID;
|
|
END;
|
|
GO
|
|
|
|
CREATE OR ALTER PROCEDURE dbo.AssetCustodyEvent_ListByScene
|
|
@SceneID int
|
|
AS
|
|
BEGIN
|
|
SET NOCOUNT ON;
|
|
SELECT ace.AssetCustodyEventID, ace.StoryAssetID, sa.AssetName, ace.SceneID,
|
|
ace.AssetCustodyEventTypeID, acet.TypeName AS CustodyEventTypeName, ace.Description,
|
|
STRING_AGG(CONCAT(aec.CharacterNameText, N' (', cr.RoleName, N')'), N', ') AS CustodianSummary,
|
|
ace.CreatedDate, ace.UpdatedDate
|
|
FROM dbo.AssetCustodyEvents ace
|
|
INNER JOIN dbo.StoryAssets sa ON sa.StoryAssetID = ace.StoryAssetID
|
|
INNER JOIN dbo.AssetCustodyEventTypes acet ON acet.AssetCustodyEventTypeID = ace.AssetCustodyEventTypeID
|
|
LEFT JOIN dbo.AssetCustodyEventCharacters aec ON aec.AssetCustodyEventID = ace.AssetCustodyEventID
|
|
LEFT JOIN dbo.CustodyRoles cr ON cr.CustodyRoleID = aec.CustodyRoleID
|
|
WHERE ace.SceneID = @SceneID
|
|
GROUP BY ace.AssetCustodyEventID, ace.StoryAssetID, sa.AssetName, ace.SceneID, ace.AssetCustodyEventTypeID, acet.TypeName, ace.Description, ace.CreatedDate, ace.UpdatedDate
|
|
ORDER BY sa.AssetName, ace.AssetCustodyEventID;
|
|
END;
|
|
GO
|
|
|
|
CREATE OR ALTER PROCEDURE dbo.AssetCustodyEvent_ListByAsset
|
|
@StoryAssetID int
|
|
AS
|
|
BEGIN
|
|
SET NOCOUNT ON;
|
|
SELECT ace.AssetCustodyEventID, ace.StoryAssetID, sa.AssetName, ace.SceneID,
|
|
ace.AssetCustodyEventTypeID, acet.TypeName AS CustodyEventTypeName, ace.Description,
|
|
STRING_AGG(CONCAT(aec.CharacterNameText, N' (', cr.RoleName, N')'), N', ') AS CustodianSummary,
|
|
ace.CreatedDate, ace.UpdatedDate, b.BookTitle, c.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 c ON c.ChapterID = s.ChapterID
|
|
INNER JOIN dbo.Books b ON b.BookID = c.BookID
|
|
LEFT JOIN dbo.AssetCustodyEventCharacters aec ON aec.AssetCustodyEventID = ace.AssetCustodyEventID
|
|
LEFT JOIN dbo.CustodyRoles cr ON cr.CustodyRoleID = aec.CustodyRoleID
|
|
WHERE ace.StoryAssetID = @StoryAssetID
|
|
GROUP BY ace.AssetCustodyEventID, ace.StoryAssetID, sa.AssetName, ace.SceneID, ace.AssetCustodyEventTypeID, acet.TypeName, ace.Description, ace.CreatedDate, ace.UpdatedDate, b.BookTitle, c.ChapterNumber, s.SceneNumber, s.SceneTitle, b.SortOrder, c.SortOrder, s.SortOrder
|
|
ORDER BY b.SortOrder, c.SortOrder, s.SortOrder, ace.AssetCustodyEventID;
|
|
END;
|
|
GO
|
|
|
|
CREATE OR ALTER PROCEDURE dbo.AssetCustodyEvent_Save
|
|
@AssetCustodyEventID int = NULL,
|
|
@StoryAssetID int,
|
|
@SceneID int,
|
|
@AssetCustodyEventTypeID int,
|
|
@Description nvarchar(max) = NULL,
|
|
@CustodianNames nvarchar(max) = NULL,
|
|
@CustodyRoleID int
|
|
AS
|
|
BEGIN
|
|
SET NOCOUNT ON;
|
|
|
|
IF @AssetCustodyEventID IS NULL OR @AssetCustodyEventID = 0
|
|
BEGIN
|
|
INSERT dbo.AssetCustodyEvents (StoryAssetID, SceneID, AssetCustodyEventTypeID, Description)
|
|
VALUES (@StoryAssetID, @SceneID, @AssetCustodyEventTypeID, @Description);
|
|
SET @AssetCustodyEventID = CAST(SCOPE_IDENTITY() AS int);
|
|
END
|
|
ELSE
|
|
BEGIN
|
|
UPDATE dbo.AssetCustodyEvents
|
|
SET StoryAssetID = @StoryAssetID,
|
|
SceneID = @SceneID,
|
|
AssetCustodyEventTypeID = @AssetCustodyEventTypeID,
|
|
Description = @Description,
|
|
UpdatedDate = SYSUTCDATETIME()
|
|
WHERE AssetCustodyEventID = @AssetCustodyEventID;
|
|
END
|
|
|
|
DELETE FROM dbo.AssetCustodyEventCharacters WHERE AssetCustodyEventID = @AssetCustodyEventID;
|
|
|
|
INSERT dbo.AssetCustodyEventCharacters (AssetCustodyEventID, CharacterNameText, CustodyRoleID)
|
|
SELECT @AssetCustodyEventID, LTRIM(RTRIM(value)), @CustodyRoleID
|
|
FROM STRING_SPLIT(REPLACE(ISNULL(@CustodianNames, N''), CHAR(10), N','), N',')
|
|
WHERE LTRIM(RTRIM(value)) <> N'';
|
|
|
|
SELECT @AssetCustodyEventID;
|
|
END;
|
|
GO
|
|
|
|
CREATE OR ALTER PROCEDURE dbo.AssetCustodyEvent_Delete
|
|
@AssetCustodyEventID int
|
|
AS
|
|
BEGIN
|
|
SET NOCOUNT ON;
|
|
DELETE FROM dbo.AssetCustodyEvents WHERE AssetCustodyEventID = @AssetCustodyEventID;
|
|
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, 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
|
|
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
|
|
|
|
CREATE OR ALTER PROCEDURE dbo.AssetDependency_CheckForEvent
|
|
@StoryAssetID int,
|
|
@SceneID int,
|
|
@ToStateID int = NULL
|
|
AS
|
|
BEGIN
|
|
SET NOCOUNT ON;
|
|
|
|
;WITH CurrentScene AS
|
|
(
|
|
SELECT b.SortOrder AS BookSortOrder, c.SortOrder AS ChapterSortOrder, s.SortOrder AS SceneSortOrder
|
|
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 s.SceneID = @SceneID
|
|
)
|
|
SELECT ad.AssetDependencyID, source.AssetName AS SourceAssetName, target.AssetName AS TargetAssetName,
|
|
adt.TypeName AS DependencyTypeName, srs.StateName AS SourceRequiredStateName,
|
|
tbs.StateName AS TargetBlockedStateName, ad.Description
|
|
FROM dbo.AssetDependencies ad
|
|
INNER JOIN dbo.AssetDependencyTypes adt ON adt.AssetDependencyTypeID = ad.AssetDependencyTypeID
|
|
INNER JOIN dbo.StoryAssets source ON source.StoryAssetID = ad.SourceAssetID
|
|
INNER JOIN dbo.StoryAssets target ON target.StoryAssetID = ad.TargetAssetID
|
|
LEFT JOIN dbo.AssetStates srs ON srs.AssetStateID = ad.SourceRequiredStateID
|
|
LEFT JOIN dbo.AssetStates tbs ON tbs.AssetStateID = ad.TargetBlockedStateID
|
|
CROSS JOIN CurrentScene cs
|
|
WHERE ad.IsArchived = 0
|
|
AND adt.IsValidatingDependency = 1
|
|
AND ad.TargetAssetID = @StoryAssetID
|
|
AND (@ToStateID IS NULL OR ad.TargetBlockedStateID IS NULL OR ad.TargetBlockedStateID = @ToStateID)
|
|
AND ad.SourceRequiredStateID IS NOT NULL
|
|
AND NOT EXISTS
|
|
(
|
|
SELECT 1
|
|
FROM dbo.AssetEvents ae
|
|
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
|
|
WHERE ae.StoryAssetID = ad.SourceAssetID
|
|
AND ae.ToStateID = ad.SourceRequiredStateID
|
|
AND (
|
|
b.SortOrder < cs.BookSortOrder
|
|
OR (b.SortOrder = cs.BookSortOrder AND c.SortOrder < cs.ChapterSortOrder)
|
|
OR (b.SortOrder = cs.BookSortOrder AND c.SortOrder = cs.ChapterSortOrder AND s.SortOrder < cs.SceneSortOrder)
|
|
)
|
|
);
|
|
END;
|
|
GO
|