265 lines
14 KiB
Transact-SQL
265 lines
14 KiB
Transact-SQL
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_SensorsAnalogUpdateInsert]') AND type in (N'P', N'PC'))
|
||
DROP PROCEDURE [dbo].[sp_SensorsAnalogUpdateInsert]
|
||
GO
|
||
SET ANSI_NULLS ON
|
||
GO
|
||
SET QUOTED_IDENTIFIER ON
|
||
GO
|
||
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_SensorsAnalogUpdateInsert]') AND type in (N'P', N'PC'))
|
||
BEGIN
|
||
EXEC dbo.sp_executesql @statement = N'CREATE PROCEDURE [dbo].[sp_SensorsAnalogUpdateInsert] AS'
|
||
END
|
||
GO
|
||
ALTER PROCEDURE [dbo].[sp_SensorsAnalogUpdateInsert]
|
||
@UserSerialNumber NVARCHAR (50),
|
||
@Model NVARCHAR (50),
|
||
@SensorModelId INT,
|
||
@Manufacturer NVARCHAR (50),
|
||
@Status NVARCHAR (50),
|
||
@MeasurementUnit NVARCHAR (50),
|
||
@OffsetToleranceLow FLOAT,
|
||
@OffsetToleranceHigh FLOAT,
|
||
@eId NVARCHAR (50),
|
||
@Capacity FLOAT,
|
||
@Comment NVARCHAR (50),
|
||
@BridgeType SMALLINT,
|
||
@BridgeLegMode SMALLINT,
|
||
@Shunt SMALLINT,
|
||
@Invert BIT,
|
||
@UserValue1 NVARCHAR (50),
|
||
@UserValue2 NVARCHAR (50),
|
||
@UserValue3 NVARCHAR (50),
|
||
@FilterClass NVARCHAR (50),
|
||
@BridgeResistance FLOAT,
|
||
@IsoCode NVARCHAR (50),
|
||
@IsoChannelName NVARCHAR (255),
|
||
@UserCode NVARCHAR (50),
|
||
@UserChannelName NVARCHAR (255),
|
||
@CheckOffset BIT,
|
||
@SupportedExcitation NVARCHAR (50),
|
||
@InitialEU FLOAT,
|
||
@CalInterval INT,
|
||
@CalibrationSignal BIT,
|
||
@InternalShuntResistance FLOAT,
|
||
@ExternalShuntResistance FLOAT,
|
||
@UniPolar BIT,
|
||
@RangeLow FLOAT,
|
||
@RangeAve FLOAT,
|
||
@RangeHigh FLOAT,
|
||
@Created DATETIME,
|
||
@TimesUsed BIGINT,
|
||
@SensorCategory INT,
|
||
@BypassFilter BIT,
|
||
@CouplingMode SMALLINT,
|
||
@Version INT,
|
||
@LastModified DATETIME,
|
||
@ModifiedBy NVARCHAR (50),
|
||
@LocalOnly BIT,
|
||
@AxisNumber SMALLINT,
|
||
@NumberOfAxes SMALLINT,
|
||
@UserTags VARBINARY (MAX),
|
||
@DoNotUse BIT,
|
||
@Broken BIT,
|
||
@DiagnosticsMode BIT,
|
||
@SerialNumber NVARCHAR (255),
|
||
@new_id INT OUTPUT,
|
||
@errorNumber INT OUTPUT,
|
||
@errorMessage NVARCHAR (255) OUTPUT
|
||
AS
|
||
BEGIN
|
||
BEGIN TRY
|
||
SET NOCOUNT ON
|
||
SET @errorNumber = 0
|
||
SET @errorMessage = ''
|
||
|
||
DECLARE @SensorType AS TINYINT
|
||
DECLARE @SensorId AS INT
|
||
DECLARE @MaxCalIntervalDays AS INT
|
||
-- FB14622 Maximum allowed interval days. When updating this value please update the _maxCalIntervalDays variable in SensitivityControl.xaml.cs
|
||
SET @MaxCalIntervalDays = 365 * 10
|
||
|
||
SELECT @SensorType = [TypeId] FROM [dbo].[SensorsType] WHERE [SensorType]='Analog'
|
||
|
||
SELECT @SensorId = A.[Id] from [dbo].[Sensors] AS A INNER JOIN [dbo].SensorsType AS B ON A.SensorType=B.TypeId WHERE B.SensorType='Analog' AND A.SerialNumber=@SerialNumber
|
||
|
||
-- FB14622 Validate the range of calibration intervals
|
||
IF(@CalInterval > @MaxCalIntervalDays OR @CalInterval <= 0 )
|
||
BEGIN
|
||
RAISERROR ('Invalid calibration inteval',16,1)
|
||
END
|
||
|
||
IF( @SensorId IS NULL)
|
||
BEGIN
|
||
INSERT INTO [dbo].[Sensors] (SensorType, SerialNumber) VALUES (@SensorType, @SerialNumber)
|
||
SELECT @SensorId = SCOPE_IDENTITY()
|
||
SET @new_id = @SensorId
|
||
INSERT INTO [dbo].[SensorsAnalog] (
|
||
[SensorId],
|
||
[UserSerialNumber],
|
||
[Model],
|
||
[SensorModelId],
|
||
[Manufacturer],
|
||
[Status],
|
||
[MeasurementUnit],
|
||
[OffsetToleranceLow],
|
||
[OffsetToleranceHigh],
|
||
[eId],
|
||
[Capacity],
|
||
[Comment],
|
||
[BridgeType],
|
||
[BridgeLegMode],
|
||
[Shunt],
|
||
[Invert],
|
||
[UserValue1],
|
||
[UserValue2],
|
||
[UserValue3],
|
||
[FilterClass],
|
||
[BridgeResistance],
|
||
[IsoCode],
|
||
[IsoChannelName],
|
||
[UserCode],
|
||
[UserChannelName],
|
||
[CheckOffset],
|
||
[SupportedExcitation],
|
||
[InitialEU],
|
||
[CalInterval],
|
||
[CalibrationSignal],
|
||
[InternalShuntResistance],
|
||
[ExternalShuntResistance],
|
||
[UniPolar],
|
||
[RangeLow],
|
||
[RangeAve],
|
||
[RangeHigh],
|
||
[Created],
|
||
[TimesUsed],
|
||
[SensorCategory],
|
||
[BypassFilter],
|
||
[CouplingMode],
|
||
[Version],
|
||
[LastModified],
|
||
[ModifiedBy],
|
||
[LocalOnly],
|
||
[AxisNumber],
|
||
[NumberOfAxes],
|
||
[UserTags],
|
||
[DoNotUse],
|
||
[Broken],
|
||
[DiagnosticsMode])
|
||
VALUES (
|
||
@SensorId,
|
||
@UserSerialNumber,
|
||
@Model,
|
||
@SensorModelId,
|
||
@Manufacturer,
|
||
@Status,
|
||
@MeasurementUnit,
|
||
@OffsetToleranceLow,
|
||
@OffsetToleranceHigh,
|
||
@eId,
|
||
@Capacity,
|
||
@Comment,
|
||
@BridgeType,
|
||
@BridgeLegMode,
|
||
@Shunt,
|
||
@Invert,
|
||
@UserValue1,
|
||
@UserValue2,
|
||
@UserValue3,
|
||
@FilterClass,
|
||
@BridgeResistance,
|
||
@IsoCode,
|
||
@IsoChannelName,
|
||
@UserCode,
|
||
@UserChannelName,
|
||
@CheckOffset,
|
||
@SupportedExcitation,
|
||
@InitialEU,
|
||
@CalInterval,
|
||
@CalibrationSignal,
|
||
@InternalShuntResistance,
|
||
@ExternalShuntResistance,
|
||
@UniPolar,
|
||
@RangeLow,
|
||
@RangeAve,
|
||
@RangeHigh,
|
||
@Created,
|
||
@TimesUsed,
|
||
@SensorCategory,
|
||
@BypassFilter,
|
||
@CouplingMode,
|
||
@Version,
|
||
@LastModified,
|
||
@ModifiedBy,
|
||
@LocalOnly,
|
||
@AxisNumber,
|
||
@NumberOfAxes,
|
||
@UserTags,
|
||
@DoNotUse,
|
||
@Broken,
|
||
@DiagnosticsMode)
|
||
END
|
||
ELSE
|
||
BEGIN
|
||
SET @new_id = @SensorId
|
||
UPDATE [dbo].[SensorsAnalog] SET
|
||
[SensorId] = @SensorId,
|
||
[UserSerialNumber] = @UserSerialNumber,
|
||
[Model] = @Model,
|
||
[SensorModelId] = @SensorModelId,
|
||
[Manufacturer] = @Manufacturer,
|
||
[Status] = @Status,
|
||
[MeasurementUnit] = @MeasurementUnit,
|
||
[OffsetToleranceLow] = @OffsetToleranceLow,
|
||
[OffsetToleranceHigh] = @OffsetToleranceHigh,
|
||
[eId] = @eId,
|
||
[Capacity] = @Capacity,
|
||
[Comment] = @Comment,
|
||
[BridgeType] = @BridgeType,
|
||
[BridgeLegMode] = @BridgeLegMode,
|
||
[Shunt] = @Shunt,
|
||
[Invert] = @Invert,
|
||
[UserValue1] = @UserValue1,
|
||
[UserValue2] = @UserValue2,
|
||
[UserValue3] = @UserValue3,
|
||
[FilterClass] = @FilterClass,
|
||
[BridgeResistance] = @BridgeResistance,
|
||
[IsoCode] = @IsoCode,
|
||
[IsoChannelName] = @IsoChannelName,
|
||
[UserCode] = @UserCode,
|
||
[UserChannelName] = @UserChannelName,
|
||
[CheckOffset] = @CheckOffset,
|
||
[SupportedExcitation] = @SupportedExcitation,
|
||
[InitialEU] = @InitialEU,
|
||
[CalInterval] = @CalInterval,
|
||
[CalibrationSignal] = @CalibrationSignal,
|
||
[InternalShuntResistance] = @InternalShuntResistance,
|
||
[ExternalShuntResistance] = @ExternalShuntResistance,
|
||
[UniPolar] = @UniPolar,
|
||
[RangeLow] = @RangeLow,
|
||
[RangeAve] = @RangeAve,
|
||
[RangeHigh] = @RangeHigh,
|
||
[Created] = @Created,
|
||
[TimesUsed] = @TimesUsed,
|
||
[SensorCategory] = @SensorCategory,
|
||
[BypassFilter] = @BypassFilter,
|
||
[CouplingMode] = @CouplingMode,
|
||
[Version] = @Version,
|
||
[LastModified] = @LastModified,
|
||
[ModifiedBy] = @ModifiedBy,
|
||
[LocalOnly] = @LocalOnly,
|
||
[AxisNumber] = @AxisNumber,
|
||
[NumberOfAxes] = @NumberOfAxes,
|
||
[UserTags] = @UserTags,
|
||
[DoNotUse] = @DoNotUse,
|
||
[Broken] = @Broken,
|
||
[DiagnosticsMode] = @DiagnosticsMode WHERE [SensorId]=@SensorId
|
||
END
|
||
END TRY
|
||
BEGIN CATCH
|
||
SET @errorNumber = error_number()
|
||
SET @errorMessage = error_message()
|
||
END CATCH
|
||
END
|
||
GO
|
||
|