291 lines
7.9 KiB
Transact-SQL
291 lines
7.9 KiB
Transact-SQL
SET ANSI_NULLS ON;
|
|
GO
|
|
|
|
SET QUOTED_IDENTIFIER ON;
|
|
GO
|
|
|
|
IF COL_LENGTH(N'dbo.FloorPlans', N'LocationID') IS NULL
|
|
BEGIN
|
|
ALTER TABLE dbo.FloorPlans ADD LocationID int NULL;
|
|
END;
|
|
GO
|
|
|
|
IF COL_LENGTH(N'dbo.FloorPlanFloors', N'LocationID') IS NULL
|
|
BEGIN
|
|
ALTER TABLE dbo.FloorPlanFloors ADD LocationID int NULL;
|
|
END;
|
|
GO
|
|
|
|
IF NOT EXISTS (SELECT 1 FROM sys.foreign_keys WHERE name = N'FK_FloorPlans_Locations')
|
|
BEGIN
|
|
ALTER TABLE dbo.FloorPlans
|
|
ADD CONSTRAINT FK_FloorPlans_Locations FOREIGN KEY (LocationID) REFERENCES dbo.Locations(LocationID);
|
|
END;
|
|
GO
|
|
|
|
IF NOT EXISTS (SELECT 1 FROM sys.foreign_keys WHERE name = N'FK_FloorPlanFloors_Locations')
|
|
BEGIN
|
|
ALTER TABLE dbo.FloorPlanFloors
|
|
ADD CONSTRAINT FK_FloorPlanFloors_Locations FOREIGN KEY (LocationID) REFERENCES dbo.Locations(LocationID);
|
|
END;
|
|
GO
|
|
|
|
IF NOT EXISTS (SELECT 1 FROM sys.indexes WHERE name = N'IX_FloorPlans_LocationID' AND object_id = OBJECT_ID(N'dbo.FloorPlans'))
|
|
BEGIN
|
|
CREATE INDEX IX_FloorPlans_LocationID ON dbo.FloorPlans(LocationID) WHERE LocationID IS NOT NULL;
|
|
END;
|
|
GO
|
|
|
|
IF NOT EXISTS (SELECT 1 FROM sys.indexes WHERE name = N'IX_FloorPlanFloors_LocationID' AND object_id = OBJECT_ID(N'dbo.FloorPlanFloors'))
|
|
BEGIN
|
|
CREATE INDEX IX_FloorPlanFloors_LocationID ON dbo.FloorPlanFloors(LocationID) WHERE LocationID IS NOT NULL;
|
|
END;
|
|
GO
|
|
|
|
UPDATE fp
|
|
SET LocationID = matched.LocationID
|
|
FROM dbo.FloorPlans fp
|
|
CROSS APPLY
|
|
(
|
|
SELECT TOP (1) l.LocationID
|
|
FROM dbo.Locations l
|
|
WHERE l.ProjectID = fp.ProjectID
|
|
AND l.ParentLocationID IS NULL
|
|
AND l.IsArchived = 0
|
|
AND LTRIM(RTRIM(l.LocationName)) = LTRIM(RTRIM(fp.Name))
|
|
ORDER BY l.LocationID
|
|
) matched
|
|
WHERE fp.LocationID IS NULL;
|
|
GO
|
|
|
|
UPDATE fpf
|
|
SET LocationID = matched.LocationID
|
|
FROM dbo.FloorPlanFloors fpf
|
|
INNER JOIN dbo.FloorPlans fp ON fp.FloorPlanID = fpf.FloorPlanID
|
|
CROSS APPLY
|
|
(
|
|
SELECT TOP (1) l.LocationID
|
|
FROM dbo.Locations l
|
|
WHERE l.ProjectID = fp.ProjectID
|
|
AND l.ParentLocationID = fp.LocationID
|
|
AND l.IsArchived = 0
|
|
AND LTRIM(RTRIM(l.LocationName)) = LTRIM(RTRIM(fpf.Name))
|
|
ORDER BY l.LocationID
|
|
) matched
|
|
WHERE fpf.LocationID IS NULL
|
|
AND fp.LocationID IS NOT NULL;
|
|
GO
|
|
|
|
CREATE OR ALTER PROCEDURE dbo.FloorPlan_ListByProject
|
|
@ProjectID int
|
|
AS
|
|
BEGIN
|
|
SET NOCOUNT ON;
|
|
|
|
SELECT
|
|
fp.FloorPlanID,
|
|
fp.ProjectID,
|
|
fp.LocationID,
|
|
lp.LocationName,
|
|
lp.LocationPath,
|
|
fp.Name,
|
|
fp.Description,
|
|
fp.CreatedDate,
|
|
fp.ModifiedDate,
|
|
COUNT(DISTINCT fpf.FloorPlanFloorID) AS FloorCount,
|
|
COUNT(fpb.FloorPlanBlockID) AS BlockCount
|
|
FROM dbo.FloorPlans fp
|
|
LEFT JOIN dbo.LocationPaths lp ON lp.LocationID = fp.LocationID
|
|
LEFT JOIN dbo.FloorPlanFloors fpf ON fpf.FloorPlanID = fp.FloorPlanID
|
|
LEFT JOIN dbo.FloorPlanBlocks fpb ON fpb.FloorPlanFloorID = fpf.FloorPlanFloorID
|
|
WHERE fp.ProjectID = @ProjectID
|
|
GROUP BY fp.FloorPlanID, fp.ProjectID, fp.LocationID, lp.LocationName, lp.LocationPath, fp.Name, fp.Description, fp.CreatedDate, fp.ModifiedDate
|
|
ORDER BY fp.Name;
|
|
END;
|
|
GO
|
|
|
|
CREATE OR ALTER PROCEDURE dbo.FloorPlan_Get
|
|
@FloorPlanID int
|
|
AS
|
|
BEGIN
|
|
SET NOCOUNT ON;
|
|
|
|
SELECT
|
|
fp.FloorPlanID,
|
|
fp.ProjectID,
|
|
fp.LocationID,
|
|
lp.LocationName,
|
|
lp.LocationPath,
|
|
fp.Name,
|
|
fp.Description,
|
|
fp.CreatedDate,
|
|
fp.ModifiedDate
|
|
FROM dbo.FloorPlans fp
|
|
LEFT JOIN dbo.LocationPaths lp ON lp.LocationID = fp.LocationID
|
|
WHERE fp.FloorPlanID = @FloorPlanID;
|
|
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.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;
|
|
END;
|
|
GO
|
|
|
|
CREATE OR ALTER PROCEDURE dbo.FloorPlan_Save
|
|
@FloorPlanID int,
|
|
@ProjectID int,
|
|
@LocationID int = NULL,
|
|
@Name nvarchar(200),
|
|
@Description nvarchar(max) = NULL
|
|
AS
|
|
BEGIN
|
|
SET NOCOUNT ON;
|
|
|
|
SET @Name = NULLIF(LTRIM(RTRIM(@Name)), N'');
|
|
IF @Name IS NULL
|
|
THROW 51000, 'Floor plan name is required.', 1;
|
|
|
|
IF @LocationID IS NOT NULL
|
|
AND NOT EXISTS (SELECT 1 FROM dbo.Locations WHERE LocationID = @LocationID AND ProjectID = @ProjectID AND IsArchived = 0)
|
|
THROW 51007, 'Floor plan location must belong to this project.', 1;
|
|
|
|
IF @FloorPlanID = 0
|
|
BEGIN
|
|
INSERT dbo.FloorPlans (ProjectID, LocationID, Name, Description)
|
|
VALUES (@ProjectID, @LocationID, @Name, @Description);
|
|
|
|
SELECT CONVERT(int, SCOPE_IDENTITY());
|
|
RETURN;
|
|
END;
|
|
|
|
UPDATE dbo.FloorPlans
|
|
SET LocationID = @LocationID,
|
|
Name = @Name,
|
|
Description = @Description,
|
|
ModifiedDate = SYSUTCDATETIME()
|
|
WHERE FloorPlanID = @FloorPlanID
|
|
AND ProjectID = @ProjectID;
|
|
|
|
SELECT @FloorPlanID;
|
|
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.SortOrder,
|
|
fpf.GridWidth,
|
|
fpf.GridHeight,
|
|
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_Save
|
|
@FloorPlanFloorID int,
|
|
@FloorPlanID int,
|
|
@LocationID int = NULL,
|
|
@Name nvarchar(100),
|
|
@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, SortOrder, GridWidth, GridHeight)
|
|
VALUES (@FloorPlanID, @LocationID, @Name, @SortOrder, @GridWidth, @GridHeight);
|
|
|
|
SELECT CONVERT(int, SCOPE_IDENTITY());
|
|
RETURN;
|
|
END;
|
|
|
|
UPDATE dbo.FloorPlanFloors
|
|
SET LocationID = @LocationID,
|
|
Name = @Name,
|
|
SortOrder = @SortOrder,
|
|
GridWidth = @GridWidth,
|
|
GridHeight = @GridHeight,
|
|
ModifiedDate = SYSUTCDATETIME()
|
|
WHERE FloorPlanFloorID = @FloorPlanFloorID
|
|
AND FloorPlanID = @FloorPlanID;
|
|
|
|
SELECT @FloorPlanFloorID;
|
|
END;
|
|
GO
|