PlotDirector/PlotLine/Sql/075_FloorPlanTransitions.sql

SET ANSI_NULLS ON;
GO
SET QUOTED_IDENTIFIER ON;
GO

IF OBJECT_ID(N'dbo.FloorPlanTransitions', N'U') IS NULL
BEGIN
    CREATE TABLE dbo.FloorPlanTransitions
    (
        FloorPlanTransitionID int IDENTITY(1,1) NOT NULL CONSTRAINT PK_FloorPlanTransitions PRIMARY KEY,
        FloorPlanFloorID int NOT NULL,
        FromLocationID int NOT NULL,
        ToLocationID int NOT NULL,
        TransitionType nvarchar(50) NOT NULL,
        Notes nvarchar(max) NULL,
        IsLocked bit NOT NULL CONSTRAINT DF_FloorPlanTransitions_IsLocked DEFAULT 0,
        DisplayLabel nvarchar(255) NULL,
        CreatedDate datetime2 NOT NULL CONSTRAINT DF_FloorPlanTransitions_CreatedDate DEFAULT SYSUTCDATETIME(),
        ModifiedDate datetime2 NULL
    );
END;
GO

IF NOT EXISTS (SELECT 1 FROM sys.foreign_keys WHERE name = N'FK_FloorPlanTransitions_FloorPlanFloors')
BEGIN
    ALTER TABLE dbo.FloorPlanTransitions
        ADD CONSTRAINT FK_FloorPlanTransitions_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_FloorPlanTransitions_FromLocations')
BEGIN
    ALTER TABLE dbo.FloorPlanTransitions
        ADD CONSTRAINT FK_FloorPlanTransitions_FromLocations FOREIGN KEY (FromLocationID) REFERENCES dbo.Locations(LocationID);
END;
GO

IF NOT EXISTS (SELECT 1 FROM sys.foreign_keys WHERE name = N'FK_FloorPlanTransitions_ToLocations')
BEGIN
    ALTER TABLE dbo.FloorPlanTransitions
        ADD CONSTRAINT FK_FloorPlanTransitions_ToLocations FOREIGN KEY (ToLocationID) REFERENCES dbo.Locations(LocationID);
END;
GO

IF NOT EXISTS (SELECT 1 FROM sys.indexes WHERE name = N'IX_FloorPlanTransitions_Floor' AND object_id = OBJECT_ID(N'dbo.FloorPlanTransitions'))
BEGIN
    CREATE INDEX IX_FloorPlanTransitions_Floor ON dbo.FloorPlanTransitions(FloorPlanFloorID);
END;
GO

IF NOT EXISTS (SELECT 1 FROM sys.indexes WHERE name = N'IX_FloorPlanTransitions_Locations' AND object_id = OBJECT_ID(N'dbo.FloorPlanTransitions'))
BEGIN
    CREATE INDEX IX_FloorPlanTransitions_Locations ON dbo.FloorPlanTransitions(FloorPlanFloorID, FromLocationID, ToLocationID, TransitionType);
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.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
AS
BEGIN
    SET NOCOUNT ON;

    SET @TransitionType = NULLIF(LTRIM(RTRIM(@TransitionType)), N'');
    SET @DisplayLabel = NULLIF(LTRIM(RTRIM(@DisplayLabel)), N'');

    IF @TransitionType IS NULL
        THROW 51020, 'Transition type is required.', 1;

    IF @FromLocationID = @ToLocationID
        THROW 51021, 'From and To locations must be different.', 1;

    IF @FloorPlanTransitionID = 0
    BEGIN
        INSERT dbo.FloorPlanTransitions (FloorPlanFloorID, FromLocationID, ToLocationID, TransitionType, Notes, IsLocked, DisplayLabel)
        VALUES (@FloorPlanFloorID, @FromLocationID, @ToLocationID, @TransitionType, @Notes, @IsLocked, @DisplayLabel);

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

    UPDATE dbo.FloorPlanTransitions
    SET FromLocationID = @FromLocationID,
        ToLocationID = @ToLocationID,
        TransitionType = @TransitionType,
        Notes = @Notes,
        IsLocked = @IsLocked,
        DisplayLabel = @DisplayLabel,
        ModifiedDate = SYSUTCDATETIME()
    WHERE FloorPlanTransitionID = @FloorPlanTransitionID
      AND FloorPlanFloorID = @FloorPlanFloorID;

    SELECT @FloorPlanTransitionID;
END;
GO

CREATE OR ALTER PROCEDURE dbo.FloorPlanTransition_Delete
    @FloorPlanTransitionID int
AS
BEGIN
    SET NOCOUNT ON;

    DELETE dbo.FloorPlanTransitions
    WHERE FloorPlanTransitionID = @FloorPlanTransitionID;
END;
GO