122 lines
3.4 KiB
Plaintext
122 lines
3.4 KiB
Plaintext
|
CREATE PROCEDURE [dbo].[sp_LockGet]
|
||
|
|
@UserId INT = NULL
|
||
|
|
,@UserName NVARCHAR(50) = NULL
|
||
|
|
,@ItemId INT = NULL
|
||
|
|
,@ItemKey NVARCHAR(50) = NULL
|
||
|
|
,@ItemCategory NVARCHAR(50)
|
||
|
|
,@MachineName NVARCHAR(50)
|
||
|
|
,@errorNumber INT OUTPUT
|
||
|
|
,@errorMessage NVARCHAR(250) OUTPUT
|
||
|
|
,@LockingUser NVARCHAR(50) OUTPUT
|
||
|
|
,@LockingMachineName NVARCHAR(50) OUTPUT
|
||
|
|
,@LastUsedTime DATETIME OUTPUT
|
||
|
|
,@LockCreateTime DATETIME OUTPUT
|
||
|
|
,@LockedItemCategory INT OUTPUT
|
||
|
|
,@LockedItemId INT OUTPUT
|
||
|
|
AS
|
||
|
|
BEGIN
|
||
|
|
SET @errorNumber = 0
|
||
|
|
SET @errorMessage = SPACE(0)
|
||
|
|
SET @LockingUser = SPACE(0)
|
||
|
|
SET @LockingMachineName = SPACE(0)
|
||
|
|
SET @LastUsedTime = NULL
|
||
|
|
SET @LockCreateTime = NULL
|
||
|
|
DECLARE @CategoryId INT
|
||
|
|
|
||
|
|
IF NOT EXISTS( SELECT CategoryId FROM LockedItemCategories WHERE CategoryText=@ItemCategory)
|
||
|
|
BEGIN
|
||
|
|
INSERT INTO LockedItemCategories (CategoryText) VALUES (@ItemCategory)
|
||
|
|
SET @CategoryId = SCOPE_IDENTITY()
|
||
|
|
SET @LockedItemCategory = @CategoryId
|
||
|
|
END
|
||
|
|
ELSE
|
||
|
|
BEGIN
|
||
|
|
SELECT @CategoryId = CategoryId from LockedItemCategories WHERE CategoryText=@ItemCategory
|
||
|
|
SET @LockedItemCategory = @CategoryId
|
||
|
|
END
|
||
|
|
|
||
|
|
IF @ItemId IS NULL
|
||
|
|
BEGIN
|
||
|
|
IF @ItemCategory = 'TestSetup'
|
||
|
|
BEGIN
|
||
|
|
IF NOT EXISTS( SELECT TestSetupId from TestSetups WHERE TestSetupName=@ItemKey)
|
||
|
|
BEGIN
|
||
|
|
SET @errorNumber = 1
|
||
|
|
SET @errorMessage = 'TestSetup not found'
|
||
|
|
SET @LockedItemId = -1
|
||
|
|
return -1
|
||
|
|
END
|
||
|
|
ELSE
|
||
|
|
BEGIN
|
||
|
|
SELECT @ItemId = TestSetupId from TestSetups where TestSetupName=@ItemKey
|
||
|
|
SET @LockedItemId = @ItemId
|
||
|
|
END
|
||
|
|
END
|
||
|
|
ELSE IF @ItemCategory = 'Group'
|
||
|
|
BEGIN
|
||
|
|
IF NOT EXISTS( SELECT Id from Groups WHERE SerialNumber=@ItemKey)
|
||
|
|
BEGIN
|
||
|
|
SET @errorNumber = 1
|
||
|
|
SET @errorMessage = 'Group not found'
|
||
|
|
SET @LockedItemId = -1
|
||
|
|
return -1
|
||
|
|
END
|
||
|
|
ELSE
|
||
|
|
BEGIN
|
||
|
|
SELECT @ItemId = Id from Groups where SerialNumber=@ItemKey
|
||
|
|
SET @LockedItemId = @ItemId
|
||
|
|
END
|
||
|
|
END
|
||
|
|
ELSE IF @ItemCategory = 'Sensor'
|
||
|
|
BEGIN
|
||
|
|
IF NOT EXISTS( SELECT Id from Sensors WHERE SerialNumber=@ItemKey)
|
||
|
|
BEGIN
|
||
|
|
SET @errorNumber = 1
|
||
|
|
SET @errorMessage = 'Sensor not found'
|
||
|
|
SET @LockedItemId = -1
|
||
|
|
return -1
|
||
|
|
END
|
||
|
|
ELSE
|
||
|
|
BEGIN
|
||
|
|
SELECT @ItemId = Id from Sensors where SerialNumber=@ItemKey
|
||
|
|
SET @LockedItemId = @ItemId
|
||
|
|
END
|
||
|
|
END
|
||
|
|
ELSE
|
||
|
|
BEGIN
|
||
|
|
SET @errorNumber = 4
|
||
|
|
SET @errorMessage = 'Unknown category'
|
||
|
|
SET @LockedItemId = -1
|
||
|
|
return -1
|
||
|
|
END
|
||
|
|
END
|
||
|
|
ELSE
|
||
|
|
BEGIN
|
||
|
|
SET @LockedItemId=@ItemId
|
||
|
|
END
|
||
|
|
|
||
|
|
IF(@UserId) IS NULL
|
||
|
|
BEGIN
|
||
|
|
IF NOT EXISTS( SELECT ID FROM Users WHERE UserName=@UserName)
|
||
|
|
BEGIN
|
||
|
|
SET @errorNumber = 2
|
||
|
|
SET @errorMessage = 'User not found'
|
||
|
|
return -1
|
||
|
|
END
|
||
|
|
END
|
||
|
|
|
||
|
|
IF NOT EXISTS( SELECT LockId FROM LockedItems WHERE CategoryId=@CategoryId AND ItemId=@ItemId)
|
||
|
|
BEGIN
|
||
|
|
DECLARE @InsertTime DateTime
|
||
|
|
SET @InsertTime = GETDATE()
|
||
|
|
INSERT INTO LockedItems (CategoryId, ItemId, MachineName, DataPROUserID, StartTime, LastTouch) VALUES (@CategoryId, @ItemId, @MachineName, @UserId, @InsertTime, @InsertTime)
|
||
|
|
END
|
||
|
|
ELSE
|
||
|
|
BEGIN
|
||
|
|
SELECT @LockingUser = B.UserName, @LockingMachineName=A.MachineName, @LastUsedTime=A.LastTouch, @LockCreateTime=A.StartTime FROM LockedItems as A INNER JOIN Users as B on A.DataPROUserID=B.ID WHERE A.CategoryId=@CategoryId AND A.ItemId=@ItemId
|
||
|
|
SET @errorNumber = 3
|
||
|
|
SET @errorMessage = 'Already locked'
|
||
|
|
return -1
|
||
|
|
END
|
||
|
|
END
|