PlotDirector/PlotLine/Sql/078_FloorPlanEditorPerformanceAndReciprocalStairs.sql

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.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.FloorPlanBlock_ListByFloor
    @FloorPlanFloorID int
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @ProjectID int;
    SELECT @ProjectID = fp.ProjectID
    FROM dbo.FloorPlanFloors fpf
    INNER JOIN dbo.FloorPlans fp ON fp.FloorPlanID = fpf.FloorPlanID
    WHERE fpf.FloorPlanFloorID = @FloorPlanFloorID;

    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
        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 #ProjectLocationPaths lp ON lp.LocationID = fpb.LocationID
    WHERE fpb.FloorPlanFloorID = @FloorPlanFloorID
    ORDER BY fpb.Y, fpb.X, lp.LocationName;
END;
GO

CREATE OR ALTER PROCEDURE dbo.FloorPlanBlock_Get
    @FloorPlanBlockID int
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @ProjectID int;
    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 = @FloorPlanBlockID;

    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
        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 #ProjectLocationPaths lp ON lp.LocationID = fpb.LocationID
    WHERE fpb.FloorPlanBlockID = @FloorPlanBlockID;
END;
GO