366 lines
23 KiB
Plaintext
366 lines
23 KiB
Plaintext
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_DBImportSensorsAnalog]') AND type in (N'P', N'PC'))
|
|
DROP PROCEDURE [dbo].[sp_DBImportSensorsAnalog]
|
|
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_DBImportSensorsAnalog]') AND type in (N'P', N'PC'))
|
|
BEGIN
|
|
EXEC dbo.sp_executesql @statement = N'CREATE PROCEDURE [dbo].[sp_DBImportSensorsAnalog] AS'
|
|
END
|
|
GO
|
|
ALTER PROCEDURE [dbo].[sp_DBImportSensorsAnalog]
|
|
@AnalogSensors xml
|
|
,@errorNumber int output
|
|
,@errorMessage nvarchar(250) output
|
|
AS
|
|
BEGIN
|
|
set @errorNumber = 0
|
|
set @errorMessage = ''
|
|
|
|
declare @tSensorsAnalog table
|
|
( [SerialNumber] [nvarchar](50) NOT NULL,
|
|
[UserSerialNumber] [nvarchar](50) NOT NULL,
|
|
[Model] [nvarchar](50) NULL,
|
|
[SensorModelId] [int] NOT NULL,
|
|
[Manufacturer] [nvarchar](50) NULL,
|
|
[Status] [nvarchar](50) NOT NULL,
|
|
[MeasurementUnit] [nvarchar](50) NOT NULL,
|
|
[OffsetToleranceLow] [float] NOT NULL,
|
|
[OffsetToleranceHigh] [float] NOT NULL,
|
|
[eId] [nvarchar](50) NOT NULL,
|
|
[Capacity] [float] NOT NULL,
|
|
[Comment] [nvarchar](50) NOT NULL,
|
|
[BridgeType] [smallint] NOT NULL,
|
|
[BridgeLegMode] [smallint] NOT NULL,
|
|
[Shunt] [smallint] NOT NULL,
|
|
[Invert] [bit] NOT NULL,
|
|
[UserValue1] [nvarchar](50) NULL,
|
|
[UserValue2] [nvarchar](50) NULL,
|
|
[UserValue3] [nvarchar](50) NULL,
|
|
[FilterClass] [nvarchar](50) NOT NULL,
|
|
[BridgeResistance] [float] NOT NULL,
|
|
[IsoCode] [nvarchar](50) NOT NULL,
|
|
[CheckOffset] [bit] NOT NULL,
|
|
[SupportedExcitation] [nvarchar](50) NOT NULL,
|
|
[InitialEU] [float] NULL,
|
|
[CalInterval] [int] NOT NULL,
|
|
[CalibrationSignal] [bit] NOT NULL,
|
|
[InternalShuntResistance] [float] NOT NULL,
|
|
[ExternalShuntResistance] [float] NOT NULL,
|
|
[UniPolar] [bit] NOT NULL,
|
|
[RangeLow] [float] NOT NULL,
|
|
[RangeAve] [float] NOT NULL,
|
|
[RangeHigh] [float] NOT NULL,
|
|
[Created] [datetime] NOT NULL,
|
|
[TimesUsed] [bigint] NOT NULL,
|
|
[SensorCategory] [int] NOT NULL,
|
|
[BypassFilter] [bit] NOT NULL,
|
|
[CouplingMode] [smallint] NOT NULL,
|
|
[Version] [int] NOT NULL,
|
|
[LastModified] [datetime] NOT NULL,
|
|
[ModifiedBy] [nvarchar](50) NOT NULL,
|
|
[LocalOnly] [bit] NULL ,
|
|
[AxisNumber] [smallint] NOT NULL,
|
|
[NumberOfAxes] [smallint] NOT NULL,
|
|
[UserTags] [varbinary](max) NULL,
|
|
[DoNotUse] [bit] NOT NULL ,
|
|
[Broken] [bit] NOT NULL,
|
|
[Processed] [bit] )
|
|
|
|
insert into @tSensorsAnalog
|
|
(SerialNumber
|
|
, UserSerialNumber
|
|
, Model
|
|
, SensorModelId
|
|
, Manufacturer
|
|
, [Status]
|
|
, MeasurementUnit
|
|
, OffsetToleranceLow
|
|
, OffsetToleranceHigh
|
|
, eId
|
|
, Capacity
|
|
, Comment
|
|
, BridgeType
|
|
, BridgeLegMode
|
|
, Shunt
|
|
, Invert
|
|
, UserValue1
|
|
, UserValue2
|
|
, UserValue3
|
|
, FilterClass
|
|
, BridgeResistance
|
|
, IsoCode
|
|
, CheckOffset
|
|
, SupportedExcitation
|
|
, InitialEU
|
|
, CalInterval
|
|
, CalibrationSignal
|
|
, InternalShuntResistance
|
|
, ExternalShuntResistance
|
|
, UniPolar
|
|
, RangeLow
|
|
, RangeAve
|
|
, RangeHigh
|
|
, Created
|
|
, TimesUsed
|
|
, SensorCategory
|
|
, BypassFilter
|
|
, CouplingMode
|
|
, [Version]
|
|
, LastModified
|
|
, ModifiedBy
|
|
, LocalOnly
|
|
, AxisNumber
|
|
, NumberOfAxes
|
|
, Broken
|
|
, DoNotUse
|
|
,Processed)
|
|
select
|
|
t.x.value('@SerialNumber', 'varchar(50)') as SerialNumber
|
|
, t.x.value('@UserSerialNumber ', 'varchar(20)') as UserSerialNumber
|
|
, t.x.value('@Model ', 'varchar(50)') as Model
|
|
,0 as SensorModelId
|
|
, t.x.value('@Manufacturer', 'varchar(50)') as Manufacturer
|
|
, t.x.value('@Status', 'varchar(50)') as [Status]
|
|
, t.x.value('@MeasurementUnit', 'varchar(50)') as MeasurementUnit
|
|
, t.x.value('@OffsetToleranceLow', 'float') as OffsetToleranceLow
|
|
, t.x.value('@OffsetToleranceHigh', 'float') as OffsetToleranceHigh
|
|
, t.x.value('@eId', 'varchar(50)') as eId
|
|
, t.x.value('@Capacity', 'float') as Capacity
|
|
, t.x.value('@Comment', 'varchar(50)') as Comment
|
|
, dbo.foo_BridgeTypeConverterToInt(t.x.value('@BridgeType', 'varchar(50)')) as BridgeType
|
|
, t.x.value('@BridgeLegMode', 'smallint') as BridgeLegMode
|
|
, t.x.value('@Shunt', 'smallint') as Shunt
|
|
, t.x.value('@Invert', 'bit') as Invert
|
|
, t.x.value('@UserValue1', 'varchar(50)') as UserValue1
|
|
, t.x.value('@UserValue2', 'varchar(50)') as UserValue2
|
|
, t.x.value('@UserValue3', 'varchar(50)') as UserValue3
|
|
, t.x.value('@FilterClass', 'varchar(50)') as FilterClass
|
|
, t.x.value('@BridgeResistance', 'float') as BridgeResistance
|
|
, t.x.value('@IsoCode', 'varchar(50)') as IsoCode
|
|
, t.x.value('@CheckOffset', 'bit') as CheckOffset
|
|
, t.x.value('@SupportedExcitation', 'varchar(max)') as SupportedExcitation
|
|
, t.x.value('@InitialEU', 'float') as InitialEU
|
|
, t.x.value('@CalInterval', 'int') as CalInterval
|
|
, t.x.value('@CalibrationSignal', 'bit') as CalibrationSignal
|
|
, t.x.value('@InternalShuntResistance', 'float') as InternalShuntResistance
|
|
, t.x.value('@ExternalShuntResistance', 'float') as ExternalShuntResistance
|
|
, t.x.value('@UniPolar', 'bit') as UniPolar
|
|
, t.x.value('@RangeLow', 'float') as RangeLow
|
|
, t.x.value('@RangeAve', 'float') as RangeAve
|
|
, t.x.value('@RangeHigh', 'float') as RangeHigh
|
|
, t.x.value('@Created', 'datetime') as Created
|
|
, t.x.value('@TimesUsed', 'bigint') as TimesUsed
|
|
, t.x.value('@SensorCategory', 'int') as SensorCategory
|
|
, t.x.value('@BypassFilter', 'bit') as BypassFilter
|
|
, t.x.value('@CouplingMode', 'smallint') as CouplingMode
|
|
, t.x.value('@Version', 'int') as [Version]
|
|
, getdate() as LastModified /* t.x.value('@LastModified', 'datetime') */
|
|
, 'DBImport' as ModifiedBy /* t.x.value('@ModifiedBy', 'varchar(50)') */
|
|
, t.x.value('@LocalOnly', 'bit') as LocalOnly
|
|
, t.x.value('@AxisNumber', 'smallint') as AxisNumber
|
|
, t.x.value('@NumberOfAxes', 'smallint') as NumberOfAxes
|
|
, t.x.value('@Broken', 'bit') as Broken
|
|
, t.x.value('@DoNotUse', 'bit') as DoNotUse
|
|
,0
|
|
from @AnalogSensors.nodes('/Sensors/SensorData') t(x) OPTION (OPTIMIZE FOR ( @AnalogSensors = NULL ))
|
|
|
|
while (Select Count(*) From @tSensorsAnalog Where Processed = 0) > 0
|
|
Begin
|
|
begin try
|
|
begin transaction tSensorsAnalog
|
|
|
|
declare
|
|
@SerialNumber nvarchar(50) ,
|
|
@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) ,
|
|
@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
|
|
|
|
select top 1
|
|
@SerialNumber = SerialNumber
|
|
,@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
|
|
,@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
|
|
from @tSensorsAnalog where Processed = 0
|
|
|
|
declare @new_id int
|
|
|
|
exec dbo.sp_SensorsAnalogUpdateInsert
|
|
@SerialNumber
|
|
,@UserSerialNumber
|
|
,@Model
|
|
,@Manufacturer
|
|
,@Status
|
|
,@MeasurementUnit
|
|
,@OffsetToleranceLow
|
|
,@OffsetToleranceHigh
|
|
,@eId
|
|
,@Capacity
|
|
,@Comment
|
|
,@BridgeType
|
|
,@BridgeLegMode
|
|
,@Shunt
|
|
,@Invert
|
|
,@UserValue1
|
|
,@UserValue2
|
|
,@UserValue3
|
|
,@FilterClass
|
|
,@BridgeResistance
|
|
,@IsoCode
|
|
,@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
|
|
,@new_id output
|
|
,@errorNumber output
|
|
,@errorMessage output
|
|
|
|
update @tSensorsAnalog set Processed = 1 where SerialNumber = @SerialNumber
|
|
commit transaction tSensorsAnalog
|
|
|
|
end try
|
|
begin catch
|
|
set @errorNumber = error_number()
|
|
set @errorMessage = error_message()
|
|
rollback transaction tSensorsAnalog
|
|
end catch;
|
|
end
|
|
END
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
GO
|