PlotDirector/PlotLine/Sql/077_FloorPlanStairTransitionPlacement.sql

IF COL_LENGTH(N'dbo.FloorPlanTransitions', N'PositionWithinLocation') IS NULL
BEGIN
    ALTER TABLE dbo.FloorPlanTransitions
        ADD PositionWithinLocation nvarchar(20) NOT NULL
            CONSTRAINT DF_FloorPlanTransitions_PositionWithinLocation DEFAULT N'Centre';
END;
GO

CREATE OR ALTER PROCEDURE dbo.FloorPlan_GetEditor
    @FloorPlanID int
AS
BEGIN
    SET NOCOUNT ON;

    EXEC dbo.FloorPlan_Get @FloorPlanID;

    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 dbo.LocationPaths 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 dbo.LocationPaths 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 dbo.LocationPaths fromPath ON fromPath.LocationID = fpt.FromLocationID
    INNER JOIN dbo.LocationPaths 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.FloorPlanTransition_Save
    @FloorPlanTransitionID int,
    @FloorPlanFloorID int,
    @FromLocationID int,
    @ToLocationID int,
    @TransitionType nvarchar(50),
    @Notes nvarchar(max) = NULL,
    @IsLocked bit = 0,
    @DisplayLabel nvarchar(255) = NULL,
    @PositionWithinLocation nvarchar(20) = N'Centre'
AS
BEGIN
    SET NOCOUNT ON;

    IF @FloorPlanTransitionID = 0
    BEGIN
        INSERT dbo.FloorPlanTransitions
        (
            FloorPlanFloorID,
            FromLocationID,
            ToLocationID,
            TransitionType,
            Notes,
            IsLocked,
            DisplayLabel,
            PositionWithinLocation
        )
        VALUES
        (
            @FloorPlanFloorID,
            @FromLocationID,
            @ToLocationID,
            @TransitionType,
            @Notes,
            @IsLocked,
            @DisplayLabel,
            COALESCE(NULLIF(@PositionWithinLocation, N''), N'Centre')
        );

        SELECT CONVERT(int, SCOPE_IDENTITY()) AS FloorPlanTransitionID;
        RETURN;
    END;

    UPDATE dbo.FloorPlanTransitions
    SET
        FromLocationID = @FromLocationID,
        ToLocationID = @ToLocationID,
        TransitionType = @TransitionType,
        Notes = @Notes,
        IsLocked = @IsLocked,
        DisplayLabel = @DisplayLabel,
        PositionWithinLocation = COALESCE(NULLIF(@PositionWithinLocation, N''), N'Centre'),
        ModifiedDate = SYSUTCDATETIME()
    WHERE FloorPlanTransitionID = @FloorPlanTransitionID
        AND FloorPlanFloorID = @FloorPlanFloorID;

    SELECT @FloorPlanTransitionID AS FloorPlanTransitionID;
END;
GO