cea82a922f
What Changed Added scene-owned occupancy data:Scenes.FloorPlanID Scenes.InitialFloorPlanFloorID new SceneFloorPlanOccupancy table Added Occupancy section to the Scene Inspector with AJAX save/refresh. Added Floor Plan editor Structure / Occupancy mode. Occupancy mode shows linked scenes, previous/next scene controls, and character/asset tokens inside assigned locations. Character tokens use image thumbnail, then initials fallback. Asset tokens do the same, smaller. No Features, no new floor-plan architecture, no transition changes. Files Changed [CoreModels.cs](C:/Source/PlotLine/PlotLine/Models/CoreModels.cs) [CoreViewModels.cs](C:/Source/PlotLine/PlotLine/ViewModels/CoreViewModels.cs) [Repositories.cs](C:/Source/PlotLine/PlotLine/Data/Repositories.cs) [CoreServices.cs](C:/Source/PlotLine/PlotLine/Services/CoreServices.cs) [ScenesController.cs](C:/Source/PlotLine/PlotLine/Controllers/ScenesController.cs) [Program.cs](C:/Source/PlotLine/PlotLine/Program.cs) [Edit.cshtml](C:/Source/PlotLine/PlotLine/Views/FloorPlans/Edit.cshtml) [_SceneInspector.cshtml](C:/Source/PlotLine/PlotLine/Views/Scenes/_SceneInspector.cshtml) [_SceneOccupancy.cshtml](C:/Source/PlotLine/PlotLine/Views/Scenes/_SceneOccupancy.cshtml) [site.js](C:/Source/PlotLine/PlotLine/wwwroot/js/site.js) [site.css](C:/Source/PlotLine/PlotLine/wwwroot/css/site.css) minified CSS/JS updated. Database Added new migration only: [080_SceneFloorPlanOccupancy.sql](C:/Source/PlotLine/PlotLine/Sql/080_SceneFloorPlanOccupancy.sql) Applied locally. Added constraints/FKs and filtered unique indexes for one character/asset occupancy row per scene. Stored Procedures Added/Updated Scene_Save Scene_Get Scene_FloorPlanLink_Save Scene_ListByFloorPlan FloorPlanFloor_ListByPlan FloorPlanBlock_ListByPlan SceneFloorPlanOccupancy_ListByScene SceneFloorPlanOccupancy_ListByFloorPlan SceneFloorPlanOccupancy_Save Test Data Permanent Calendar House seed data added: Book: The Calendar House Chapters 1-3 14 scenes Characters: Ellie, Grant, Stephen, Housekeeper, Gardener Assets: Brass Key, Journal, Candle, Map, Letter 45 occupancy assignments Verification dotnet build PlotLine.slnx passes with 0 warnings/errors. Migration applied locally. Local app boots and returns HTTP 200. DB checks confirm 14 linked scenes and 45 occupancy rows. Manual browser click-through of the authenticated UI still needs a human pass for the AJAX controls and visual token placement.
279 lines
13 KiB
Transact-SQL
279 lines
13 KiB
Transact-SQL
SET QUOTED_IDENTIFIER ON;
|
|
GO
|
|
|
|
IF COL_LENGTH(N'dbo.Scenes', N'FloorPlanID') IS NULL
|
|
ALTER TABLE dbo.Scenes ADD FloorPlanID int NULL;
|
|
GO
|
|
|
|
IF COL_LENGTH(N'dbo.Scenes', N'InitialFloorPlanFloorID') IS NULL
|
|
ALTER TABLE dbo.Scenes ADD InitialFloorPlanFloorID int NULL;
|
|
GO
|
|
|
|
IF OBJECT_ID(N'dbo.FK_Scenes_FloorPlans', N'F') IS NULL
|
|
ALTER TABLE dbo.Scenes WITH CHECK ADD CONSTRAINT FK_Scenes_FloorPlans FOREIGN KEY (FloorPlanID) REFERENCES dbo.FloorPlans(FloorPlanID);
|
|
GO
|
|
|
|
IF OBJECT_ID(N'dbo.FK_Scenes_InitialFloorPlanFloors', N'F') IS NULL
|
|
ALTER TABLE dbo.Scenes WITH CHECK ADD CONSTRAINT FK_Scenes_InitialFloorPlanFloors FOREIGN KEY (InitialFloorPlanFloorID) REFERENCES dbo.FloorPlanFloors(FloorPlanFloorID);
|
|
GO
|
|
|
|
IF OBJECT_ID(N'dbo.SceneFloorPlanOccupancy', N'U') IS NULL
|
|
BEGIN
|
|
CREATE TABLE dbo.SceneFloorPlanOccupancy
|
|
(
|
|
SceneFloorPlanOccupancyID int IDENTITY(1,1) NOT NULL CONSTRAINT PK_SceneFloorPlanOccupancy PRIMARY KEY,
|
|
SceneID int NOT NULL,
|
|
FloorPlanID int NOT NULL,
|
|
FloorPlanFloorID int NULL,
|
|
CharacterID int NULL,
|
|
StoryAssetID int NULL,
|
|
LocationID int NOT NULL,
|
|
CreatedDate datetime2 NOT NULL CONSTRAINT DF_SceneFloorPlanOccupancy_CreatedDate DEFAULT SYSUTCDATETIME(),
|
|
ModifiedDate datetime2 NULL,
|
|
CONSTRAINT CK_SceneFloorPlanOccupancy_OneEntity CHECK
|
|
(
|
|
(CharacterID IS NOT NULL AND StoryAssetID IS NULL)
|
|
OR (CharacterID IS NULL AND StoryAssetID IS NOT NULL)
|
|
),
|
|
CONSTRAINT FK_SceneFloorPlanOccupancy_Scenes FOREIGN KEY (SceneID) REFERENCES dbo.Scenes(SceneID) ON DELETE CASCADE,
|
|
CONSTRAINT FK_SceneFloorPlanOccupancy_FloorPlans FOREIGN KEY (FloorPlanID) REFERENCES dbo.FloorPlans(FloorPlanID),
|
|
CONSTRAINT FK_SceneFloorPlanOccupancy_FloorPlanFloors FOREIGN KEY (FloorPlanFloorID) REFERENCES dbo.FloorPlanFloors(FloorPlanFloorID),
|
|
CONSTRAINT FK_SceneFloorPlanOccupancy_Characters FOREIGN KEY (CharacterID) REFERENCES dbo.Characters(CharacterID),
|
|
CONSTRAINT FK_SceneFloorPlanOccupancy_StoryAssets FOREIGN KEY (StoryAssetID) REFERENCES dbo.StoryAssets(StoryAssetID),
|
|
CONSTRAINT FK_SceneFloorPlanOccupancy_Locations FOREIGN KEY (LocationID) REFERENCES dbo.Locations(LocationID)
|
|
);
|
|
END;
|
|
GO
|
|
|
|
IF NOT EXISTS (SELECT 1 FROM sys.indexes WHERE name = N'UX_SceneFloorPlanOccupancy_Character' AND object_id = OBJECT_ID(N'dbo.SceneFloorPlanOccupancy'))
|
|
CREATE UNIQUE INDEX UX_SceneFloorPlanOccupancy_Character ON dbo.SceneFloorPlanOccupancy(SceneID, CharacterID) WHERE CharacterID IS NOT NULL;
|
|
GO
|
|
|
|
IF NOT EXISTS (SELECT 1 FROM sys.indexes WHERE name = N'UX_SceneFloorPlanOccupancy_Asset' AND object_id = OBJECT_ID(N'dbo.SceneFloorPlanOccupancy'))
|
|
CREATE UNIQUE INDEX UX_SceneFloorPlanOccupancy_Asset ON dbo.SceneFloorPlanOccupancy(SceneID, StoryAssetID) WHERE StoryAssetID IS NOT NULL;
|
|
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, @FloorPlanID int = NULL, @InitialFloorPlanFloorID 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, FloorPlanID, InitialFloorPlanFloorID)
|
|
VALUES (@ChapterID, @SceneNumber, @SceneTitle, @Summary, @NextSortOrder, @TimeModeID, @StartDateTime, @EndDateTime,
|
|
@DurationAmount, @DurationUnitID, @RelativeTimeText, @TimeConfidenceID, @ScenePurposeNotes, @SceneOutcomeNotes,
|
|
@RevisionStatusID, @PrimaryLocationID, @FloorPlanID, @InitialFloorPlanFloorID);
|
|
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,
|
|
FloorPlanID = @FloorPlanID, InitialFloorPlanFloorID = @InitialFloorPlanFloorID,
|
|
UpdatedDate = SYSUTCDATETIME()
|
|
WHERE SceneID = @SceneID;
|
|
END
|
|
SELECT @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,
|
|
s.FloorPlanID, s.InitialFloorPlanFloorID,
|
|
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.Scene_FloorPlanLink_Save
|
|
@SceneID int,
|
|
@FloorPlanID int = NULL,
|
|
@InitialFloorPlanFloorID int = NULL
|
|
AS
|
|
BEGIN
|
|
SET NOCOUNT ON;
|
|
IF @FloorPlanID IS NULL
|
|
SET @InitialFloorPlanFloorID = NULL;
|
|
|
|
UPDATE dbo.Scenes
|
|
SET FloorPlanID = @FloorPlanID,
|
|
InitialFloorPlanFloorID = @InitialFloorPlanFloorID,
|
|
UpdatedDate = SYSUTCDATETIME()
|
|
WHERE SceneID = @SceneID;
|
|
|
|
IF @FloorPlanID IS NULL
|
|
DELETE FROM dbo.SceneFloorPlanOccupancy WHERE SceneID = @SceneID;
|
|
END;
|
|
GO
|
|
|
|
CREATE OR ALTER PROCEDURE dbo.Scene_ListByFloorPlan
|
|
@FloorPlanID int
|
|
AS
|
|
BEGIN
|
|
SET NOCOUNT ON;
|
|
SELECT s.SceneID, s.ChapterID, s.SceneNumber, s.SceneTitle, s.Summary, s.POVCharacterID, s.PrimaryLocationID,
|
|
s.FloorPlanID, s.InitialFloorPlanFloorID, 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
|
|
WHERE s.FloorPlanID = @FloorPlanID AND s.IsArchived = 0
|
|
ORDER BY s.StartDateTime, s.SceneNumber, s.SortOrder, s.SceneTitle;
|
|
END;
|
|
GO
|
|
|
|
CREATE OR ALTER PROCEDURE dbo.FloorPlanFloor_ListByPlan
|
|
@FloorPlanID int
|
|
AS
|
|
BEGIN
|
|
SET NOCOUNT ON;
|
|
SELECT FloorPlanFloorID, FloorPlanID, LocationID, Name, SortOrder, GridWidth, GridHeight,
|
|
BackgroundImagePath, BackgroundImageOriginalFileName, BackgroundOpacity, BackgroundScale,
|
|
BackgroundOffsetX, BackgroundOffsetY, BackgroundLocked, CreatedDate, ModifiedDate
|
|
FROM dbo.FloorPlanFloors
|
|
WHERE FloorPlanID = @FloorPlanID
|
|
ORDER BY SortOrder, Name;
|
|
END;
|
|
GO
|
|
|
|
CREATE OR ALTER PROCEDURE dbo.FloorPlanBlock_ListByPlan
|
|
@FloorPlanID int
|
|
AS
|
|
BEGIN
|
|
SET NOCOUNT ON;
|
|
SELECT fpb.FloorPlanBlockID, fpb.FloorPlanFloorID, fpb.LocationID,
|
|
COALESCE(lp.LocationName, l.LocationName) AS LocationName,
|
|
COALESCE(lp.LocationPath, l.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.Locations l ON l.LocationID = fpb.LocationID
|
|
LEFT JOIN dbo.LocationPaths lp ON lp.LocationID = fpb.LocationID
|
|
WHERE fpf.FloorPlanID = @FloorPlanID
|
|
ORDER BY fpf.SortOrder, fpf.Name, fpb.Y, fpb.X;
|
|
END;
|
|
GO
|
|
|
|
CREATE OR ALTER PROCEDURE dbo.SceneFloorPlanOccupancy_ListByScene
|
|
@SceneID int
|
|
AS
|
|
BEGIN
|
|
SET NOCOUNT ON;
|
|
SELECT o.SceneFloorPlanOccupancyID, o.SceneID, o.FloorPlanID, o.FloorPlanFloorID,
|
|
o.CharacterID, c.CharacterName, c.ShortName AS CharacterShortName, c.ImagePath AS CharacterImagePath, c.ThumbnailPath AS CharacterThumbnailPath,
|
|
o.StoryAssetID, sa.AssetName, sa.ImagePath AS AssetImagePath, sa.ThumbnailPath AS AssetThumbnailPath,
|
|
o.LocationID, COALESCE(lp.LocationName, l.LocationName) AS LocationName, COALESCE(lp.LocationPath, l.LocationName) AS LocationPath,
|
|
fpf.Name AS FloorName, o.CreatedDate, o.ModifiedDate
|
|
FROM dbo.SceneFloorPlanOccupancy o
|
|
INNER JOIN dbo.Locations l ON l.LocationID = o.LocationID
|
|
LEFT JOIN dbo.LocationPaths lp ON lp.LocationID = o.LocationID
|
|
LEFT JOIN dbo.FloorPlanFloors fpf ON fpf.FloorPlanFloorID = o.FloorPlanFloorID
|
|
LEFT JOIN dbo.Characters c ON c.CharacterID = o.CharacterID
|
|
LEFT JOIN dbo.StoryAssets sa ON sa.StoryAssetID = o.StoryAssetID
|
|
WHERE o.SceneID = @SceneID
|
|
ORDER BY COALESCE(c.CharacterName, sa.AssetName);
|
|
END;
|
|
GO
|
|
|
|
CREATE OR ALTER PROCEDURE dbo.SceneFloorPlanOccupancy_ListByFloorPlan
|
|
@FloorPlanID int
|
|
AS
|
|
BEGIN
|
|
SET NOCOUNT ON;
|
|
SELECT o.SceneFloorPlanOccupancyID, o.SceneID, o.FloorPlanID, o.FloorPlanFloorID,
|
|
o.CharacterID, c.CharacterName, c.ShortName AS CharacterShortName, c.ImagePath AS CharacterImagePath, c.ThumbnailPath AS CharacterThumbnailPath,
|
|
o.StoryAssetID, sa.AssetName, sa.ImagePath AS AssetImagePath, sa.ThumbnailPath AS AssetThumbnailPath,
|
|
o.LocationID, COALESCE(lp.LocationName, l.LocationName) AS LocationName, COALESCE(lp.LocationPath, l.LocationName) AS LocationPath,
|
|
fpf.Name AS FloorName, o.CreatedDate, o.ModifiedDate
|
|
FROM dbo.SceneFloorPlanOccupancy o
|
|
INNER JOIN dbo.Locations l ON l.LocationID = o.LocationID
|
|
LEFT JOIN dbo.LocationPaths lp ON lp.LocationID = o.LocationID
|
|
LEFT JOIN dbo.FloorPlanFloors fpf ON fpf.FloorPlanFloorID = o.FloorPlanFloorID
|
|
LEFT JOIN dbo.Characters c ON c.CharacterID = o.CharacterID
|
|
LEFT JOIN dbo.StoryAssets sa ON sa.StoryAssetID = o.StoryAssetID
|
|
WHERE o.FloorPlanID = @FloorPlanID
|
|
ORDER BY o.SceneID, COALESCE(c.CharacterName, sa.AssetName);
|
|
END;
|
|
GO
|
|
|
|
CREATE OR ALTER PROCEDURE dbo.SceneFloorPlanOccupancy_Save
|
|
@SceneID int,
|
|
@FloorPlanID int = NULL,
|
|
@CharacterID int = NULL,
|
|
@StoryAssetID int = NULL,
|
|
@LocationID int = NULL
|
|
AS
|
|
BEGIN
|
|
SET NOCOUNT ON;
|
|
|
|
IF @FloorPlanID IS NULL OR @LocationID IS NULL
|
|
BEGIN
|
|
DELETE FROM dbo.SceneFloorPlanOccupancy
|
|
WHERE SceneID = @SceneID
|
|
AND ((@CharacterID IS NOT NULL AND CharacterID = @CharacterID)
|
|
OR (@StoryAssetID IS NOT NULL AND StoryAssetID = @StoryAssetID));
|
|
RETURN;
|
|
END;
|
|
|
|
DECLARE @FloorPlanFloorID int;
|
|
SELECT TOP (1) @FloorPlanFloorID = fpb.FloorPlanFloorID
|
|
FROM dbo.FloorPlanBlocks fpb
|
|
INNER JOIN dbo.FloorPlanFloors fpf ON fpf.FloorPlanFloorID = fpb.FloorPlanFloorID
|
|
WHERE fpf.FloorPlanID = @FloorPlanID
|
|
AND fpb.LocationID = @LocationID
|
|
ORDER BY fpf.SortOrder, fpf.Name, fpb.Y, fpb.X;
|
|
|
|
IF @FloorPlanFloorID IS NULL
|
|
THROW 51000, 'Location must be placed on the selected floor plan.', 1;
|
|
|
|
MERGE dbo.SceneFloorPlanOccupancy AS target
|
|
USING
|
|
(
|
|
SELECT @SceneID AS SceneID, @FloorPlanID AS FloorPlanID, @FloorPlanFloorID AS FloorPlanFloorID,
|
|
@CharacterID AS CharacterID, @StoryAssetID AS StoryAssetID, @LocationID AS LocationID
|
|
) AS source
|
|
ON target.SceneID = source.SceneID
|
|
AND ((source.CharacterID IS NOT NULL AND target.CharacterID = source.CharacterID)
|
|
OR (source.StoryAssetID IS NOT NULL AND target.StoryAssetID = source.StoryAssetID))
|
|
WHEN MATCHED THEN
|
|
UPDATE SET FloorPlanID = source.FloorPlanID, FloorPlanFloorID = source.FloorPlanFloorID,
|
|
LocationID = source.LocationID, ModifiedDate = SYSUTCDATETIME()
|
|
WHEN NOT MATCHED THEN
|
|
INSERT (SceneID, FloorPlanID, FloorPlanFloorID, CharacterID, StoryAssetID, LocationID)
|
|
VALUES (source.SceneID, source.FloorPlanID, source.FloorPlanFloorID, source.CharacterID, source.StoryAssetID, source.LocationID);
|
|
END;
|
|
GO
|