454 lines
17 KiB
Transact-SQL
454 lines
17 KiB
Transact-SQL
IF OBJECT_ID(N'dbo.FloorPlanBlocks', N'U') IS NULL
|
|
BEGIN
|
|
CREATE TABLE dbo.FloorPlanBlocks
|
|
(
|
|
FloorPlanBlockID int IDENTITY(1,1) NOT NULL CONSTRAINT PK_FloorPlanBlocks PRIMARY KEY,
|
|
FloorPlanFloorID int NOT NULL,
|
|
LocationID int NOT NULL,
|
|
X int NOT NULL,
|
|
Y int NOT NULL,
|
|
WidthCells int NOT NULL,
|
|
HeightCells int NOT NULL,
|
|
BlockType nvarchar(40) NOT NULL CONSTRAINT DF_FloorPlanBlocks_BlockType DEFAULT N'Room',
|
|
LabelOverride nvarchar(200) NULL,
|
|
Notes nvarchar(max) NULL,
|
|
CreatedDate datetime2 NOT NULL CONSTRAINT DF_FloorPlanBlocks_CreatedDate DEFAULT SYSUTCDATETIME(),
|
|
ModifiedDate datetime2 NULL
|
|
);
|
|
END;
|
|
GO
|
|
|
|
IF OBJECT_ID(N'dbo.FloorPlanFloors', N'U') IS NULL
|
|
BEGIN
|
|
CREATE TABLE dbo.FloorPlanFloors
|
|
(
|
|
FloorPlanFloorID int IDENTITY(1,1) NOT NULL CONSTRAINT PK_FloorPlanFloors PRIMARY KEY,
|
|
FloorPlanID int NOT NULL,
|
|
Name nvarchar(100) NOT NULL,
|
|
SortOrder int NOT NULL CONSTRAINT DF_FloorPlanFloors_SortOrder DEFAULT 0,
|
|
GridWidth int NOT NULL CONSTRAINT DF_FloorPlanFloors_GridWidth DEFAULT 24,
|
|
GridHeight int NOT NULL CONSTRAINT DF_FloorPlanFloors_GridHeight DEFAULT 16,
|
|
CreatedDate datetime2 NOT NULL CONSTRAINT DF_FloorPlanFloors_CreatedDate DEFAULT SYSUTCDATETIME(),
|
|
ModifiedDate datetime2 NULL
|
|
);
|
|
END;
|
|
GO
|
|
|
|
IF OBJECT_ID(N'dbo.FloorPlans', N'U') IS NULL
|
|
BEGIN
|
|
CREATE TABLE dbo.FloorPlans
|
|
(
|
|
FloorPlanID int IDENTITY(1,1) NOT NULL CONSTRAINT PK_FloorPlans PRIMARY KEY,
|
|
ProjectID int NOT NULL,
|
|
Name nvarchar(200) NOT NULL,
|
|
Description nvarchar(max) NULL,
|
|
CreatedDate datetime2 NOT NULL CONSTRAINT DF_FloorPlans_CreatedDate DEFAULT SYSUTCDATETIME(),
|
|
ModifiedDate datetime2 NULL
|
|
);
|
|
END;
|
|
GO
|
|
|
|
IF NOT EXISTS (SELECT 1 FROM sys.foreign_keys WHERE name = N'FK_FloorPlans_Projects')
|
|
BEGIN
|
|
ALTER TABLE dbo.FloorPlans
|
|
ADD CONSTRAINT FK_FloorPlans_Projects FOREIGN KEY (ProjectID) REFERENCES dbo.Projects(ProjectID);
|
|
END;
|
|
GO
|
|
|
|
IF NOT EXISTS (SELECT 1 FROM sys.foreign_keys WHERE name = N'FK_FloorPlanFloors_FloorPlans')
|
|
BEGIN
|
|
ALTER TABLE dbo.FloorPlanFloors
|
|
ADD CONSTRAINT FK_FloorPlanFloors_FloorPlans FOREIGN KEY (FloorPlanID) REFERENCES dbo.FloorPlans(FloorPlanID) ON DELETE CASCADE;
|
|
END;
|
|
GO
|
|
|
|
IF NOT EXISTS (SELECT 1 FROM sys.foreign_keys WHERE name = N'FK_FloorPlanBlocks_FloorPlanFloors')
|
|
BEGIN
|
|
ALTER TABLE dbo.FloorPlanBlocks
|
|
ADD CONSTRAINT FK_FloorPlanBlocks_FloorPlanFloors FOREIGN KEY (FloorPlanFloorID) REFERENCES dbo.FloorPlanFloors(FloorPlanFloorID) ON DELETE CASCADE;
|
|
END;
|
|
GO
|
|
|
|
IF NOT EXISTS (SELECT 1 FROM sys.foreign_keys WHERE name = N'FK_FloorPlanBlocks_Locations')
|
|
BEGIN
|
|
ALTER TABLE dbo.FloorPlanBlocks
|
|
ADD CONSTRAINT FK_FloorPlanBlocks_Locations FOREIGN KEY (LocationID) REFERENCES dbo.Locations(LocationID);
|
|
END;
|
|
GO
|
|
|
|
IF NOT EXISTS (SELECT 1 FROM sys.check_constraints WHERE name = N'CK_FloorPlanFloors_GridSize')
|
|
BEGIN
|
|
ALTER TABLE dbo.FloorPlanFloors
|
|
ADD CONSTRAINT CK_FloorPlanFloors_GridSize CHECK (GridWidth BETWEEN 8 AND 80 AND GridHeight BETWEEN 8 AND 80);
|
|
END;
|
|
GO
|
|
|
|
IF NOT EXISTS (SELECT 1 FROM sys.check_constraints WHERE name = N'CK_FloorPlanBlocks_PositionSize')
|
|
BEGIN
|
|
ALTER TABLE dbo.FloorPlanBlocks
|
|
ADD CONSTRAINT CK_FloorPlanBlocks_PositionSize CHECK (X >= 0 AND Y >= 0 AND WidthCells >= 1 AND HeightCells >= 1);
|
|
END;
|
|
GO
|
|
|
|
IF NOT EXISTS (SELECT 1 FROM sys.check_constraints WHERE name = N'CK_FloorPlanBlocks_BlockType')
|
|
BEGIN
|
|
ALTER TABLE dbo.FloorPlanBlocks
|
|
ADD CONSTRAINT CK_FloorPlanBlocks_BlockType CHECK (BlockType IN (N'Room', N'Corridor', N'OpenSpace', N'Exterior', N'Other'));
|
|
END;
|
|
GO
|
|
|
|
IF NOT EXISTS (SELECT 1 FROM sys.indexes WHERE name = N'IX_FloorPlans_ProjectID' AND object_id = OBJECT_ID(N'dbo.FloorPlans'))
|
|
BEGIN
|
|
CREATE INDEX IX_FloorPlans_ProjectID ON dbo.FloorPlans(ProjectID);
|
|
END;
|
|
GO
|
|
|
|
IF NOT EXISTS (SELECT 1 FROM sys.indexes WHERE name = N'IX_FloorPlanFloors_FloorPlanID' AND object_id = OBJECT_ID(N'dbo.FloorPlanFloors'))
|
|
BEGIN
|
|
CREATE INDEX IX_FloorPlanFloors_FloorPlanID ON dbo.FloorPlanFloors(FloorPlanID);
|
|
END;
|
|
GO
|
|
|
|
IF NOT EXISTS (SELECT 1 FROM sys.indexes WHERE name = N'IX_FloorPlanBlocks_FloorPlanFloorID' AND object_id = OBJECT_ID(N'dbo.FloorPlanBlocks'))
|
|
BEGIN
|
|
CREATE INDEX IX_FloorPlanBlocks_FloorPlanFloorID ON dbo.FloorPlanBlocks(FloorPlanFloorID);
|
|
END;
|
|
GO
|
|
|
|
IF NOT EXISTS (SELECT 1 FROM sys.indexes WHERE name = N'IX_FloorPlanBlocks_LocationID' AND object_id = OBJECT_ID(N'dbo.FloorPlanBlocks'))
|
|
BEGIN
|
|
CREATE INDEX IX_FloorPlanBlocks_LocationID ON dbo.FloorPlanBlocks(LocationID);
|
|
END;
|
|
GO
|
|
|
|
CREATE OR ALTER PROCEDURE dbo.FloorPlan_ListByProject
|
|
@ProjectID int
|
|
AS
|
|
BEGIN
|
|
SET NOCOUNT ON;
|
|
|
|
SELECT
|
|
fp.FloorPlanID,
|
|
fp.ProjectID,
|
|
fp.Name,
|
|
fp.Description,
|
|
fp.CreatedDate,
|
|
fp.ModifiedDate,
|
|
COUNT(DISTINCT fpf.FloorPlanFloorID) AS FloorCount,
|
|
COUNT(fpb.FloorPlanBlockID) AS BlockCount
|
|
FROM dbo.FloorPlans fp
|
|
LEFT JOIN dbo.FloorPlanFloors fpf ON fpf.FloorPlanID = fp.FloorPlanID
|
|
LEFT JOIN dbo.FloorPlanBlocks fpb ON fpb.FloorPlanFloorID = fpf.FloorPlanFloorID
|
|
WHERE fp.ProjectID = @ProjectID
|
|
GROUP BY fp.FloorPlanID, fp.ProjectID, fp.Name, fp.Description, fp.CreatedDate, fp.ModifiedDate
|
|
ORDER BY fp.Name;
|
|
END;
|
|
GO
|
|
|
|
CREATE OR ALTER PROCEDURE dbo.FloorPlan_Get
|
|
@FloorPlanID int
|
|
AS
|
|
BEGIN
|
|
SET NOCOUNT ON;
|
|
|
|
SELECT FloorPlanID, ProjectID, Name, Description, CreatedDate, ModifiedDate
|
|
FROM dbo.FloorPlans
|
|
WHERE FloorPlanID = @FloorPlanID;
|
|
END;
|
|
GO
|
|
|
|
CREATE OR ALTER PROCEDURE dbo.FloorPlan_GetEditor
|
|
@FloorPlanID int
|
|
AS
|
|
BEGIN
|
|
SET NOCOUNT ON;
|
|
|
|
EXEC dbo.FloorPlan_Get @FloorPlanID;
|
|
|
|
SELECT FloorPlanFloorID, FloorPlanID, Name, SortOrder, GridWidth, GridHeight, CreatedDate, ModifiedDate
|
|
FROM dbo.FloorPlanFloors
|
|
WHERE FloorPlanID = @FloorPlanID
|
|
ORDER BY SortOrder, Name;
|
|
|
|
SELECT
|
|
fpb.FloorPlanBlockID,
|
|
fpb.FloorPlanFloorID,
|
|
fpb.LocationID,
|
|
lp.LocationName,
|
|
COALESCE(NULLIF(lp.LocationPath, N''), lp.LocationName) AS LocationPath,
|
|
fpb.X,
|
|
fpb.Y,
|
|
fpb.WidthCells,
|
|
fpb.HeightCells,
|
|
fpb.BlockType,
|
|
fpb.LabelOverride,
|
|
fpb.Notes,
|
|
fpb.CreatedDate,
|
|
fpb.ModifiedDate
|
|
FROM dbo.FloorPlanBlocks fpb
|
|
INNER JOIN dbo.FloorPlanFloors fpf ON fpf.FloorPlanFloorID = fpb.FloorPlanFloorID
|
|
INNER JOIN dbo.LocationPaths lp ON lp.LocationID = fpb.LocationID
|
|
WHERE fpf.FloorPlanID = @FloorPlanID
|
|
ORDER BY fpf.SortOrder, fpb.Y, fpb.X, lp.LocationName;
|
|
END;
|
|
GO
|
|
|
|
CREATE OR ALTER PROCEDURE dbo.FloorPlan_Save
|
|
@FloorPlanID int,
|
|
@ProjectID int,
|
|
@Name nvarchar(200),
|
|
@Description nvarchar(max) = NULL
|
|
AS
|
|
BEGIN
|
|
SET NOCOUNT ON;
|
|
|
|
SET @Name = NULLIF(LTRIM(RTRIM(@Name)), N'');
|
|
IF @Name IS NULL
|
|
THROW 51000, 'Floor plan name is required.', 1;
|
|
|
|
IF @FloorPlanID = 0
|
|
BEGIN
|
|
INSERT dbo.FloorPlans (ProjectID, Name, Description)
|
|
VALUES (@ProjectID, @Name, @Description);
|
|
|
|
SELECT CONVERT(int, SCOPE_IDENTITY());
|
|
RETURN;
|
|
END;
|
|
|
|
UPDATE dbo.FloorPlans
|
|
SET Name = @Name,
|
|
Description = @Description,
|
|
ModifiedDate = SYSUTCDATETIME()
|
|
WHERE FloorPlanID = @FloorPlanID
|
|
AND ProjectID = @ProjectID;
|
|
|
|
SELECT @FloorPlanID;
|
|
END;
|
|
GO
|
|
|
|
CREATE OR ALTER PROCEDURE dbo.FloorPlan_Delete
|
|
@FloorPlanID int
|
|
AS
|
|
BEGIN
|
|
SET NOCOUNT ON;
|
|
|
|
DELETE dbo.FloorPlans
|
|
WHERE FloorPlanID = @FloorPlanID;
|
|
END;
|
|
GO
|
|
|
|
CREATE OR ALTER PROCEDURE dbo.FloorPlanFloor_Get
|
|
@FloorPlanFloorID int
|
|
AS
|
|
BEGIN
|
|
SET NOCOUNT ON;
|
|
|
|
SELECT FloorPlanFloorID, FloorPlanID, Name, SortOrder, GridWidth, GridHeight, CreatedDate, ModifiedDate
|
|
FROM dbo.FloorPlanFloors
|
|
WHERE FloorPlanFloorID = @FloorPlanFloorID;
|
|
END;
|
|
GO
|
|
|
|
CREATE OR ALTER PROCEDURE dbo.FloorPlanFloor_Save
|
|
@FloorPlanFloorID int,
|
|
@FloorPlanID int,
|
|
@Name nvarchar(100),
|
|
@SortOrder int,
|
|
@GridWidth int,
|
|
@GridHeight int
|
|
AS
|
|
BEGIN
|
|
SET NOCOUNT ON;
|
|
|
|
SET @Name = NULLIF(LTRIM(RTRIM(@Name)), N'');
|
|
IF @Name IS NULL
|
|
THROW 51001, 'Floor name is required.', 1;
|
|
|
|
IF @GridWidth NOT BETWEEN 8 AND 80 OR @GridHeight NOT BETWEEN 8 AND 80
|
|
THROW 51002, 'Grid width and height must be between 8 and 80.', 1;
|
|
|
|
IF @FloorPlanFloorID = 0
|
|
BEGIN
|
|
INSERT dbo.FloorPlanFloors (FloorPlanID, Name, SortOrder, GridWidth, GridHeight)
|
|
VALUES (@FloorPlanID, @Name, @SortOrder, @GridWidth, @GridHeight);
|
|
|
|
SELECT CONVERT(int, SCOPE_IDENTITY());
|
|
RETURN;
|
|
END;
|
|
|
|
UPDATE dbo.FloorPlanFloors
|
|
SET Name = @Name,
|
|
SortOrder = @SortOrder,
|
|
GridWidth = @GridWidth,
|
|
GridHeight = @GridHeight,
|
|
ModifiedDate = SYSUTCDATETIME()
|
|
WHERE FloorPlanFloorID = @FloorPlanFloorID
|
|
AND FloorPlanID = @FloorPlanID;
|
|
|
|
SELECT @FloorPlanFloorID;
|
|
END;
|
|
GO
|
|
|
|
CREATE OR ALTER PROCEDURE dbo.FloorPlanFloor_Delete
|
|
@FloorPlanFloorID int
|
|
AS
|
|
BEGIN
|
|
SET NOCOUNT ON;
|
|
|
|
DELETE dbo.FloorPlanFloors
|
|
WHERE FloorPlanFloorID = @FloorPlanFloorID;
|
|
END;
|
|
GO
|
|
|
|
CREATE OR ALTER PROCEDURE dbo.FloorPlanBlock_Save
|
|
@FloorPlanBlockID int,
|
|
@FloorPlanFloorID int,
|
|
@LocationID int,
|
|
@X int,
|
|
@Y int,
|
|
@WidthCells int,
|
|
@HeightCells int,
|
|
@BlockType nvarchar(40),
|
|
@LabelOverride nvarchar(200) = NULL,
|
|
@Notes nvarchar(max) = NULL
|
|
AS
|
|
BEGIN
|
|
SET NOCOUNT ON;
|
|
|
|
DECLARE @GridWidth int, @GridHeight int, @ProjectID int;
|
|
|
|
SELECT
|
|
@GridWidth = fpf.GridWidth,
|
|
@GridHeight = fpf.GridHeight,
|
|
@ProjectID = fp.ProjectID
|
|
FROM dbo.FloorPlanFloors fpf
|
|
INNER JOIN dbo.FloorPlans fp ON fp.FloorPlanID = fpf.FloorPlanID
|
|
WHERE fpf.FloorPlanFloorID = @FloorPlanFloorID;
|
|
|
|
IF @ProjectID IS NULL
|
|
THROW 51003, 'Floor not found.', 1;
|
|
|
|
IF NOT EXISTS (SELECT 1 FROM dbo.Locations WHERE LocationID = @LocationID AND ProjectID = @ProjectID AND IsArchived = 0)
|
|
THROW 51004, 'Location must belong to this project.', 1;
|
|
|
|
IF @BlockType NOT IN (N'Room', N'Corridor', N'OpenSpace', N'Exterior', N'Other')
|
|
THROW 51005, 'Choose a valid block type.', 1;
|
|
|
|
IF @X < 0 OR @Y < 0 OR @WidthCells < 1 OR @HeightCells < 1 OR @X + @WidthCells > @GridWidth OR @Y + @HeightCells > @GridHeight
|
|
THROW 51006, 'Block is outside the floor grid.', 1;
|
|
|
|
IF @FloorPlanBlockID = 0
|
|
BEGIN
|
|
INSERT dbo.FloorPlanBlocks (FloorPlanFloorID, LocationID, X, Y, WidthCells, HeightCells, BlockType, LabelOverride, Notes)
|
|
VALUES (@FloorPlanFloorID, @LocationID, @X, @Y, @WidthCells, @HeightCells, @BlockType, NULLIF(LTRIM(RTRIM(@LabelOverride)), N''), @Notes);
|
|
|
|
SELECT CONVERT(int, SCOPE_IDENTITY());
|
|
RETURN;
|
|
END;
|
|
|
|
UPDATE dbo.FloorPlanBlocks
|
|
SET FloorPlanFloorID = @FloorPlanFloorID,
|
|
LocationID = @LocationID,
|
|
X = @X,
|
|
Y = @Y,
|
|
WidthCells = @WidthCells,
|
|
HeightCells = @HeightCells,
|
|
BlockType = @BlockType,
|
|
LabelOverride = NULLIF(LTRIM(RTRIM(@LabelOverride)), N''),
|
|
Notes = @Notes,
|
|
ModifiedDate = SYSUTCDATETIME()
|
|
WHERE FloorPlanBlockID = @FloorPlanBlockID;
|
|
|
|
SELECT @FloorPlanBlockID;
|
|
END;
|
|
GO
|
|
|
|
CREATE OR ALTER PROCEDURE dbo.FloorPlanBlock_Delete
|
|
@FloorPlanBlockID int
|
|
AS
|
|
BEGIN
|
|
SET NOCOUNT ON;
|
|
|
|
DELETE dbo.FloorPlanBlocks
|
|
WHERE FloorPlanBlockID = @FloorPlanBlockID;
|
|
END;
|
|
GO
|
|
|
|
CREATE OR ALTER PROCEDURE dbo.ProjectAccess_UserCanAccessEntity
|
|
@EntityType nvarchar(100),
|
|
@EntityID int,
|
|
@UserID int
|
|
AS
|
|
BEGIN
|
|
SET NOCOUNT ON;
|
|
|
|
DECLARE @ProjectID int;
|
|
|
|
IF @EntityType = N'Project'
|
|
SET @ProjectID = @EntityID;
|
|
ELSE IF @EntityType = N'Book'
|
|
SELECT @ProjectID = ProjectID FROM dbo.Books WHERE BookID = @EntityID;
|
|
ELSE IF @EntityType = N'Chapter'
|
|
SELECT @ProjectID = b.ProjectID FROM dbo.Chapters c INNER JOIN dbo.Books b ON b.BookID = c.BookID WHERE c.ChapterID = @EntityID;
|
|
ELSE IF @EntityType = N'Scene'
|
|
SELECT @ProjectID = b.ProjectID 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 = @EntityID;
|
|
ELSE IF @EntityType = N'Character'
|
|
SELECT @ProjectID = ProjectID FROM dbo.Characters WHERE CharacterID = @EntityID;
|
|
ELSE IF @EntityType = N'StoryAsset'
|
|
SELECT @ProjectID = ProjectID FROM dbo.StoryAssets WHERE StoryAssetID = @EntityID;
|
|
ELSE IF @EntityType = N'Location'
|
|
SELECT @ProjectID = ProjectID FROM dbo.Locations WHERE LocationID = @EntityID;
|
|
ELSE IF @EntityType = N'FloorPlan'
|
|
SELECT @ProjectID = ProjectID FROM dbo.FloorPlans WHERE FloorPlanID = @EntityID;
|
|
ELSE IF @EntityType = N'FloorPlanFloor'
|
|
SELECT @ProjectID = fp.ProjectID FROM dbo.FloorPlanFloors fpf INNER JOIN dbo.FloorPlans fp ON fp.FloorPlanID = fpf.FloorPlanID WHERE fpf.FloorPlanFloorID = @EntityID;
|
|
ELSE IF @EntityType = N'FloorPlanBlock'
|
|
SELECT @ProjectID = fp.ProjectID FROM dbo.FloorPlanBlocks fpb INNER JOIN dbo.FloorPlanFloors fpf ON fpf.FloorPlanFloorID = fpb.FloorPlanFloorID INNER JOIN dbo.FloorPlans fp ON fp.FloorPlanID = fpf.FloorPlanID WHERE fpb.FloorPlanBlockID = @EntityID;
|
|
ELSE IF @EntityType = N'PlotLine'
|
|
SELECT @ProjectID = ProjectID FROM dbo.PlotLines WHERE PlotLineID = @EntityID;
|
|
ELSE IF @EntityType = N'PlotThread'
|
|
SELECT @ProjectID = pl.ProjectID FROM dbo.PlotThreads pt INNER JOIN dbo.PlotLines pl ON pl.PlotLineID = pt.PlotLineID WHERE pt.PlotThreadID = @EntityID;
|
|
ELSE IF @EntityType = N'CharacterRelationship'
|
|
SELECT @ProjectID = ProjectID FROM dbo.CharacterRelationships WHERE CharacterRelationshipID = @EntityID;
|
|
ELSE IF @EntityType = N'LocationRelationship'
|
|
SELECT @ProjectID = l.ProjectID FROM dbo.LocationRelationships lr INNER JOIN dbo.Locations l ON l.LocationID = lr.FromLocationID WHERE lr.LocationRelationshipID = @EntityID;
|
|
ELSE IF @EntityType = N'TimelineViewPreset'
|
|
SELECT @ProjectID = ProjectID FROM dbo.TimelineViewPresets WHERE TimelineViewPresetID = @EntityID;
|
|
ELSE IF @EntityType = N'ProjectRestorePoint'
|
|
SELECT @ProjectID = ProjectID FROM dbo.ProjectRestorePoints WHERE ProjectRestorePointID = @EntityID;
|
|
ELSE IF @EntityType = N'Scenario'
|
|
SELECT @ProjectID = ProjectID FROM dbo.Scenarios WHERE ScenarioID = @EntityID;
|
|
ELSE IF @EntityType = N'ThreadEvent'
|
|
SELECT @ProjectID = pl.ProjectID FROM dbo.ThreadEvents te INNER JOIN dbo.PlotThreads pt ON pt.PlotThreadID = te.PlotThreadID INNER JOIN dbo.PlotLines pl ON pl.PlotLineID = pt.PlotLineID WHERE te.ThreadEventID = @EntityID;
|
|
ELSE IF @EntityType = N'AssetEvent'
|
|
SELECT @ProjectID = sa.ProjectID FROM dbo.AssetEvents ae INNER JOIN dbo.StoryAssets sa ON sa.StoryAssetID = ae.StoryAssetID WHERE ae.AssetEventID = @EntityID;
|
|
ELSE IF @EntityType = N'SceneAssetLocation'
|
|
SELECT @ProjectID = sa.ProjectID FROM dbo.SceneAssetLocations sal INNER JOIN dbo.StoryAssets sa ON sa.StoryAssetID = sal.StoryAssetID WHERE sal.SceneAssetLocationID = @EntityID;
|
|
ELSE IF @EntityType = N'AssetCustodyEvent'
|
|
SELECT @ProjectID = sa.ProjectID FROM dbo.AssetCustodyEvents ace INNER JOIN dbo.StoryAssets sa ON sa.StoryAssetID = ace.StoryAssetID WHERE ace.AssetCustodyEventID = @EntityID;
|
|
ELSE IF @EntityType = N'SceneCharacter'
|
|
SELECT @ProjectID = ch.ProjectID FROM dbo.SceneCharacters sc INNER JOIN dbo.Characters ch ON ch.CharacterID = sc.CharacterID WHERE sc.SceneCharacterID = @EntityID;
|
|
ELSE IF @EntityType = N'SceneDependency'
|
|
SELECT @ProjectID = ProjectID FROM dbo.SceneDependencies WHERE SceneDependencyID = @EntityID;
|
|
ELSE IF @EntityType = N'CharacterKnowledge'
|
|
SELECT @ProjectID = c.ProjectID FROM dbo.CharacterKnowledge ck INNER JOIN dbo.Characters c ON c.CharacterID = ck.CharacterID WHERE ck.CharacterKnowledgeID = @EntityID;
|
|
ELSE IF @EntityType = N'RelationshipEvent'
|
|
SELECT @ProjectID = cr.ProjectID FROM dbo.RelationshipEvents re INNER JOIN dbo.CharacterRelationships cr ON cr.CharacterRelationshipID = re.CharacterRelationshipID WHERE re.RelationshipEventID = @EntityID;
|
|
ELSE IF @EntityType = N'SceneNote'
|
|
SELECT @ProjectID = b.ProjectID FROM dbo.SceneNotes sn INNER JOIN dbo.Scenes s ON s.SceneID = sn.SceneID INNER JOIN dbo.Chapters c ON c.ChapterID = s.ChapterID INNER JOIN dbo.Books b ON b.BookID = c.BookID WHERE sn.SceneNoteID = @EntityID;
|
|
ELSE IF @EntityType = N'SceneChecklistItem'
|
|
SELECT @ProjectID = b.ProjectID FROM dbo.SceneChecklistItems sci INNER JOIN dbo.Scenes s ON s.SceneID = sci.SceneID INNER JOIN dbo.Chapters c ON c.ChapterID = s.ChapterID INNER JOIN dbo.Books b ON b.BookID = c.BookID WHERE sci.SceneChecklistItemID = @EntityID;
|
|
ELSE IF @EntityType = N'SceneAttachment'
|
|
SELECT @ProjectID = b.ProjectID FROM dbo.SceneAttachments sa INNER JOIN dbo.Scenes s ON s.SceneID = sa.SceneID INNER JOIN dbo.Chapters c ON c.ChapterID = s.ChapterID INNER JOIN dbo.Books b ON b.BookID = c.BookID WHERE sa.SceneAttachmentID = @EntityID;
|
|
|
|
SELECT CONVERT(bit, CASE WHEN EXISTS
|
|
(
|
|
SELECT 1
|
|
FROM dbo.ProjectUserAccess
|
|
WHERE ProjectID = @ProjectID
|
|
AND UserID = @UserID
|
|
AND IsActive = 1
|
|
) THEN 1 ELSE 0 END);
|
|
END;
|
|
GO
|