13c97db5a0
What I fixed: Removed the warning-calculation N+1 queries. Occupancy warnings now use one floor-plan participant query instead of per-scene character/asset lookups. Added SceneFloorPlanParticipant_ListByFloorPlan to the existing migration script with SET ANSI_NULLS ON and SET QUOTED_IDENTIFIER ON. Reapplied the migration locally. Confirmed FloorPlanBlock_Save, FloorPlan_GetEditor, and the new participant proc have quoted identifiers enabled. Reduced drag/resize freeze risk by throttling expensive geometry recalculation to animation frames instead of every pointer event. Added a final geometry refresh on pointer-up/cancel before autosave. Improved autosave failure visibility by showing the real error in the editor, not just leaving status stuck. Made Occupancy mode view/staging only:Blocks no longer start drag/resize in Occupancy mode. Resize handles are hidden. Structural toolbox is hidden. Plan canvas expands to use the freed width. Structure mode restores normal editing. Verification: dotnet build PlotLine.slnx passes with 0 warnings and 0 errors. Non-destructive SQL smoke test of FloorPlanBlock_Save against the Lounge block passed inside a transaction and rolled back. Confirmed the Lounge block stayed unchanged after the rollback. Reapplied 081_FloorPlanFloorDescriptionAndOccupancyWarnings.sql successfully. Files changed: [Repositories.cs](C:/Source/PlotLine/PlotLine/Data/Repositories.cs) [CoreModels.cs](C:/Source/PlotLine/PlotLine/Models/CoreModels.cs) [CoreServices.cs](C:/Source/PlotLine/PlotLine/Services/CoreServices.cs) [081 SQL migration](C:/Source/PlotLine/PlotLine/Sql/081_FloorPlanFloorDescriptionAndOccupancyWarnings.sql) [Edit.cshtml](C:/Source/PlotLine/PlotLine/Views/FloorPlans/Edit.cshtml) [site.css](C:/Source/PlotLine/PlotLine/wwwroot/css/site.css) [site.min.css](C:/Source/PlotLine/PlotLine/wwwroot/css/site.min.css) Manual browser QA still needed for the exact drag/add/reload flow, but the code path and SQL save path are now stabilized.
276 lines
8.6 KiB
Transact-SQL
276 lines
8.6 KiB
Transact-SQL
SET ANSI_NULLS ON;
|
|
GO
|
|
SET QUOTED_IDENTIFIER ON;
|
|
GO
|
|
|
|
IF COL_LENGTH(N'dbo.FloorPlanFloors', N'Description') IS NULL
|
|
ALTER TABLE dbo.FloorPlanFloors ADD Description nvarchar(max) NULL;
|
|
GO
|
|
|
|
CREATE OR ALTER PROCEDURE dbo.FloorPlan_GetEditor
|
|
@FloorPlanID int
|
|
AS
|
|
BEGIN
|
|
SET NOCOUNT ON;
|
|
|
|
DECLARE @ProjectID int;
|
|
SELECT @ProjectID = ProjectID
|
|
FROM dbo.FloorPlans
|
|
WHERE FloorPlanID = @FloorPlanID;
|
|
|
|
EXEC dbo.FloorPlan_Get @FloorPlanID;
|
|
|
|
CREATE TABLE #ProjectLocationPaths
|
|
(
|
|
LocationID int NOT NULL PRIMARY KEY,
|
|
LocationName nvarchar(200) NOT NULL,
|
|
LocationPath nvarchar(max) NOT NULL
|
|
);
|
|
|
|
;WITH ProjectLocationTree AS
|
|
(
|
|
SELECT
|
|
LocationID,
|
|
LocationName,
|
|
CAST(LocationName AS nvarchar(max)) AS LocationPath
|
|
FROM dbo.Locations
|
|
WHERE ProjectID = @ProjectID
|
|
AND ParentLocationID IS NULL
|
|
AND IsArchived = 0
|
|
|
|
UNION ALL
|
|
|
|
SELECT
|
|
child.LocationID,
|
|
child.LocationName,
|
|
CAST(parent.LocationPath + N' > ' + child.LocationName AS nvarchar(max)) AS LocationPath
|
|
FROM dbo.Locations child
|
|
INNER JOIN ProjectLocationTree parent ON parent.LocationID = child.ParentLocationID
|
|
WHERE child.ProjectID = @ProjectID
|
|
AND child.IsArchived = 0
|
|
)
|
|
INSERT #ProjectLocationPaths (LocationID, LocationName, LocationPath)
|
|
SELECT LocationID, LocationName, LocationPath
|
|
FROM ProjectLocationTree;
|
|
|
|
SELECT
|
|
fpf.FloorPlanFloorID,
|
|
fpf.FloorPlanID,
|
|
fpf.LocationID,
|
|
lp.LocationName,
|
|
lp.LocationPath,
|
|
fpf.Name,
|
|
fpf.Description,
|
|
fpf.SortOrder,
|
|
fpf.GridWidth,
|
|
fpf.GridHeight,
|
|
fpf.BackgroundImagePath,
|
|
fpf.BackgroundImageOriginalFileName,
|
|
COALESCE(fpf.BackgroundOpacity, CONVERT(decimal(5,2), 0.35)) AS BackgroundOpacity,
|
|
COALESCE(fpf.BackgroundScale, CONVERT(decimal(8,3), 1.000)) AS BackgroundScale,
|
|
COALESCE(fpf.BackgroundOffsetX, CONVERT(decimal(10,2), 0.00)) AS BackgroundOffsetX,
|
|
COALESCE(fpf.BackgroundOffsetY, CONVERT(decimal(10,2), 0.00)) AS BackgroundOffsetY,
|
|
fpf.BackgroundLocked,
|
|
fpf.CreatedDate,
|
|
fpf.ModifiedDate
|
|
FROM dbo.FloorPlanFloors fpf
|
|
LEFT JOIN #ProjectLocationPaths lp ON lp.LocationID = fpf.LocationID
|
|
WHERE fpf.FloorPlanID = @FloorPlanID
|
|
ORDER BY fpf.SortOrder, fpf.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 #ProjectLocationPaths lp ON lp.LocationID = fpb.LocationID
|
|
WHERE fpf.FloorPlanID = @FloorPlanID
|
|
ORDER BY fpf.SortOrder, fpb.Y, fpb.X, lp.LocationName;
|
|
|
|
SELECT
|
|
fpt.FloorPlanTransitionID,
|
|
fpt.FloorPlanFloorID,
|
|
fpt.FromLocationID,
|
|
fromPath.LocationName AS FromLocationName,
|
|
COALESCE(NULLIF(fromPath.LocationPath, N''), fromPath.LocationName) AS FromLocationPath,
|
|
fpt.ToLocationID,
|
|
toPath.LocationName AS ToLocationName,
|
|
COALESCE(NULLIF(toPath.LocationPath, N''), toPath.LocationName) AS ToLocationPath,
|
|
fpt.TransitionType,
|
|
fpt.Notes,
|
|
fpt.IsLocked,
|
|
fpt.DisplayLabel,
|
|
fpt.PositionWithinLocation,
|
|
fpt.CreatedDate,
|
|
fpt.ModifiedDate
|
|
FROM dbo.FloorPlanTransitions fpt
|
|
INNER JOIN dbo.FloorPlanFloors fpf ON fpf.FloorPlanFloorID = fpt.FloorPlanFloorID
|
|
INNER JOIN #ProjectLocationPaths fromPath ON fromPath.LocationID = fpt.FromLocationID
|
|
INNER JOIN #ProjectLocationPaths toPath ON toPath.LocationID = fpt.ToLocationID
|
|
WHERE fpf.FloorPlanID = @FloorPlanID
|
|
ORDER BY fpf.SortOrder, fromPath.LocationName, toPath.LocationName, fpt.TransitionType;
|
|
END;
|
|
GO
|
|
|
|
CREATE OR ALTER PROCEDURE dbo.SceneFloorPlanParticipant_ListByFloorPlan
|
|
@FloorPlanID int
|
|
AS
|
|
BEGIN
|
|
SET NOCOUNT ON;
|
|
|
|
SELECT DISTINCT
|
|
s.SceneID,
|
|
N'Character' AS EntityType,
|
|
sc.CharacterID AS EntityID,
|
|
c.CharacterName AS DisplayName
|
|
FROM dbo.Scenes s
|
|
INNER JOIN dbo.SceneCharacters sc ON sc.SceneID = s.SceneID
|
|
INNER JOIN dbo.Characters c ON c.CharacterID = sc.CharacterID
|
|
WHERE s.FloorPlanID = @FloorPlanID
|
|
AND s.IsArchived = 0
|
|
|
|
UNION
|
|
|
|
SELECT DISTINCT
|
|
s.SceneID,
|
|
N'Asset' AS EntityType,
|
|
ae.StoryAssetID AS EntityID,
|
|
sa.AssetName AS DisplayName
|
|
FROM dbo.Scenes s
|
|
INNER JOIN dbo.AssetEvents ae ON ae.SceneID = s.SceneID
|
|
INNER JOIN dbo.StoryAssets sa ON sa.StoryAssetID = ae.StoryAssetID
|
|
WHERE s.FloorPlanID = @FloorPlanID
|
|
AND s.IsArchived = 0;
|
|
END;
|
|
GO
|
|
|
|
CREATE OR ALTER PROCEDURE dbo.FloorPlanFloor_Get
|
|
@FloorPlanFloorID int
|
|
AS
|
|
BEGIN
|
|
SET NOCOUNT ON;
|
|
|
|
SELECT
|
|
fpf.FloorPlanFloorID,
|
|
fpf.FloorPlanID,
|
|
fpf.LocationID,
|
|
lp.LocationName,
|
|
lp.LocationPath,
|
|
fpf.Name,
|
|
fpf.Description,
|
|
fpf.SortOrder,
|
|
fpf.GridWidth,
|
|
fpf.GridHeight,
|
|
fpf.BackgroundImagePath,
|
|
fpf.BackgroundImageOriginalFileName,
|
|
COALESCE(fpf.BackgroundOpacity, CONVERT(decimal(5,2), 0.35)) AS BackgroundOpacity,
|
|
COALESCE(fpf.BackgroundScale, CONVERT(decimal(8,3), 1.000)) AS BackgroundScale,
|
|
COALESCE(fpf.BackgroundOffsetX, CONVERT(decimal(10,2), 0.00)) AS BackgroundOffsetX,
|
|
COALESCE(fpf.BackgroundOffsetY, CONVERT(decimal(10,2), 0.00)) AS BackgroundOffsetY,
|
|
fpf.BackgroundLocked,
|
|
fpf.CreatedDate,
|
|
fpf.ModifiedDate
|
|
FROM dbo.FloorPlanFloors fpf
|
|
LEFT JOIN dbo.LocationPaths lp ON lp.LocationID = fpf.LocationID
|
|
WHERE fpf.FloorPlanFloorID = @FloorPlanFloorID;
|
|
END;
|
|
GO
|
|
|
|
CREATE OR ALTER PROCEDURE dbo.FloorPlanFloor_ListByPlan
|
|
@FloorPlanID int
|
|
AS
|
|
BEGIN
|
|
SET NOCOUNT ON;
|
|
SELECT
|
|
fpf.FloorPlanFloorID,
|
|
fpf.FloorPlanID,
|
|
fpf.LocationID,
|
|
lp.LocationName,
|
|
lp.LocationPath,
|
|
fpf.Name,
|
|
fpf.Description,
|
|
fpf.SortOrder,
|
|
fpf.GridWidth,
|
|
fpf.GridHeight,
|
|
fpf.BackgroundImagePath,
|
|
fpf.BackgroundImageOriginalFileName,
|
|
fpf.BackgroundOpacity,
|
|
fpf.BackgroundScale,
|
|
fpf.BackgroundOffsetX,
|
|
fpf.BackgroundOffsetY,
|
|
fpf.BackgroundLocked,
|
|
fpf.CreatedDate,
|
|
fpf.ModifiedDate
|
|
FROM dbo.FloorPlanFloors fpf
|
|
LEFT JOIN dbo.LocationPaths lp ON lp.LocationID = fpf.LocationID
|
|
WHERE fpf.FloorPlanID = @FloorPlanID
|
|
ORDER BY fpf.SortOrder, fpf.Name;
|
|
END;
|
|
GO
|
|
|
|
CREATE OR ALTER PROCEDURE dbo.FloorPlanFloor_Save
|
|
@FloorPlanFloorID int,
|
|
@FloorPlanID int,
|
|
@LocationID int = NULL,
|
|
@Name nvarchar(100),
|
|
@Description nvarchar(max) = NULL,
|
|
@SortOrder int,
|
|
@GridWidth int,
|
|
@GridHeight int
|
|
AS
|
|
BEGIN
|
|
SET NOCOUNT ON;
|
|
|
|
DECLARE @ProjectID int;
|
|
SELECT @ProjectID = ProjectID FROM dbo.FloorPlans WHERE FloorPlanID = @FloorPlanID;
|
|
|
|
IF @ProjectID IS NULL
|
|
THROW 51003, 'Floor plan not found.', 1;
|
|
|
|
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 @LocationID IS NOT NULL
|
|
AND NOT EXISTS (SELECT 1 FROM dbo.Locations WHERE LocationID = @LocationID AND ProjectID = @ProjectID AND IsArchived = 0)
|
|
THROW 51008, 'Floor location must belong to this project.', 1;
|
|
|
|
IF @FloorPlanFloorID = 0
|
|
BEGIN
|
|
INSERT dbo.FloorPlanFloors (FloorPlanID, LocationID, Name, Description, SortOrder, GridWidth, GridHeight, BackgroundOpacity, BackgroundScale, BackgroundOffsetX, BackgroundOffsetY, BackgroundLocked)
|
|
VALUES (@FloorPlanID, @LocationID, @Name, @Description, @SortOrder, @GridWidth, @GridHeight, 0.35, 1.000, 0.00, 0.00, 1);
|
|
|
|
SELECT CONVERT(int, SCOPE_IDENTITY());
|
|
RETURN;
|
|
END;
|
|
|
|
UPDATE dbo.FloorPlanFloors
|
|
SET LocationID = @LocationID,
|
|
Name = @Name,
|
|
Description = @Description,
|
|
SortOrder = @SortOrder,
|
|
GridWidth = @GridWidth,
|
|
GridHeight = @GridHeight,
|
|
ModifiedDate = SYSUTCDATETIME()
|
|
WHERE FloorPlanFloorID = @FloorPlanFloorID
|
|
AND FloorPlanID = @FloorPlanID;
|
|
|
|
SELECT @FloorPlanFloorID;
|
|
END;
|
|
GO
|