318 lines
20 KiB
Plaintext
318 lines
20 KiB
Plaintext
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_SensorsAnalogUpdateAll]') AND type in (N'P', N'PC'))
|
|
DROP PROCEDURE [dbo].[sp_SensorsAnalogUpdateAll]
|
|
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_SensorsAnalogUpdateAll]') AND type in (N'P', N'PC'))
|
|
BEGIN
|
|
EXEC dbo.sp_executesql @statement = N'CREATE PROCEDURE [dbo].[sp_SensorsAnalogUpdateAll] AS'
|
|
END
|
|
GO
|
|
ALTER PROCEDURE [dbo].[sp_SensorsAnalogUpdateAll]
|
|
@sensors xml = null
|
|
,@errorNumber int output
|
|
,@errorMessage nvarchar(250) output
|
|
AS
|
|
BEGIN
|
|
set @errorNumber = 0
|
|
set @errorMessage = space(0)
|
|
|
|
if(@sensors is null)
|
|
begin
|
|
RAISERROR(15600,-1,-1, 'sp_SensorsAnalogUpdateAll') /* Error 1560 - An invalid parameter or option was specified for procedure*/
|
|
end
|
|
else
|
|
begin
|
|
SET NOCOUNT ON;
|
|
declare @SensorsAnalog Table(
|
|
[SerialNumber] [nvarchar](50) NOT NULL,
|
|
[UserSerialNumber] [nvarchar](50) NOT NULL,
|
|
[Model] [nvarchar](50) 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] NOT NULL,
|
|
[AxisNumber] [smallint] NOT NULL,
|
|
[NumberOfAxes] [smallint] NOT NULL,
|
|
[UserTags] [varbinary](max) NULL,
|
|
[DoNotUse] [bit] DEFAULT ((0)),
|
|
[Broken] [bit] DEFAULT ((0)),
|
|
[Exist] [bit] DEFAULT ((0))
|
|
)
|
|
declare @exist bit
|
|
set @exist =(select @sensors.exist('/DigitalIn/Sensor'))
|
|
if(@exist = 1)
|
|
begin
|
|
insert into @SensorsAnalog select
|
|
t.x.value('@SerialNumber','varchar(50)')
|
|
, t.x.value('@UserSerialNumber','varchar(50)')
|
|
, t.x.value('@Model','varchar(50)')
|
|
, t.x.value('@Manufacturer','varchar(50)')
|
|
, t.x.value('@Status','varchar(50)')
|
|
, t.x.value('@MeasurementUnit','varchar(50)')
|
|
, t.x.value('@OffsetToleranceLow','float')
|
|
, t.x.value('@OffsetToleranceHigh','float')
|
|
, t.x.value('@eId','varchar(50)')
|
|
, t.x.value('@Capacity','float')
|
|
, t.x.value('@Comment','varchar(50)')
|
|
, t.x.value('@BridgeType','smallint')
|
|
, t.x.value('@BridgeLegMode','smallint')
|
|
, t.x.value('@Shunt','smallint')
|
|
, t.x.value('@Invert','bit')
|
|
, t.x.value('@UserValue1','varchar(50)')
|
|
, t.x.value('@UserValue2','varchar(50)')
|
|
, t.x.value('@UserValue3','varchar(50)')
|
|
, t.x.value('@FilterClass','varchar(50)')
|
|
, t.x.value('@BridgeResistance','float')
|
|
, t.x.value('@IsoCode','varchar(50)')
|
|
, t.x.value('@CheckOffset','bit')
|
|
, t.x.value('@SupportedExcitation','varchar(50)')
|
|
, t.x.value('@InitialEU','float')
|
|
, t.x.value('@CalInterval','int')
|
|
, t.x.value('@CalibrationSignal','bit')
|
|
, t.x.value('@InternalShuntResistance','float')
|
|
, t.x.value('@ExternalShuntResistance','float')
|
|
, t.x.value('@UniPolar','bit')
|
|
, t.x.value('@RangeLow','float')
|
|
, t.x.value('@RangeAve','float')
|
|
, t.x.value('@RangeHigh','float')
|
|
, t.x.value('@Created','datetime')
|
|
, t.x.value('@TimesUsed','bigint')
|
|
, t.x.value('@SensorCategory','int')
|
|
, t.x.value('@BypassFilter','bit')
|
|
, t.x.value('@CouplingMode','smallint')
|
|
, t.x.value('@Version','int')
|
|
, t.x.value('@LastModified','datetime')
|
|
, t.x.value('@ModifiedBy','varchar(50)')
|
|
, t.x.value('@LocalOnly','bit')
|
|
, t.x.value('@AxisNumber','smallint')
|
|
, t.x.value('@NumberOfAxes','smallint')
|
|
, t.x.value('@UserTags','varbinary(max)')
|
|
, t.x.value('@DoNotUse','bit')
|
|
, t.x.value('@Broken','bit')
|
|
, 0
|
|
from @sensors.nodes('/Analog/Sensor') t(x)
|
|
OPTION (OPTIMIZE FOR ( @sensors = NULL ))
|
|
|
|
declare @count int
|
|
set @count = (select count(*) from @SensorsAnalog)
|
|
if(@count > 0)
|
|
begin
|
|
update @SensorsAnalog set Exist = case when EXISTS(select SerialNumber from v_SensorSerialNumber where SensorType =0 ) then 1 else 0 end
|
|
|
|
update [dbo].[SensorsAnalog]
|
|
SET [SerialNumber] =s.SerialNumber
|
|
,[UserSerialNumber] =s.UserSerialNumber
|
|
,[Model] =s.Model
|
|
,[Manufacturer] =s.Manufacturer
|
|
,[Status] =s.[Status]
|
|
,[MeasurementUnit] =s.MeasurementUnit
|
|
,[OffsetToleranceLow] =s.OffsetToleranceLow
|
|
,[OffsetToleranceHigh] =s.OffsetToleranceHigh
|
|
,[eId] =s.eId
|
|
,[Capacity] =s.Capacity
|
|
,[Comment] =s.Comment
|
|
,[BridgeType] =s.BridgeType
|
|
,[BridgeLegMode] =s.BridgeLegMode
|
|
,[Shunt] =s.Shunt
|
|
,[Invert] =s.Invert
|
|
,[UserValue1] =s.UserValue1
|
|
,[UserValue2] =s.UserValue2
|
|
,[UserValue3] =s.UserValue3
|
|
,[FilterClass] =s.FilterClass
|
|
,[BridgeResistance] =s.BridgeResistance
|
|
,[IsoCode] =s.IsoCode
|
|
,[CheckOffset] =s.CheckOffset
|
|
,[SupportedExcitation] =s.SupportedExcitation
|
|
,[InitialEU] =s.InitialEU
|
|
,[CalInterval] =s.CalInterval
|
|
,[CalibrationSignal] =s.CalibrationSignal
|
|
,[InternalShuntResistance] =s.InternalShuntResistance
|
|
,[ExternalShuntResistance] =s.ExternalShuntResistance
|
|
,[UniPolar] =s.UniPolar
|
|
,[RangeLow] =s.RangeLow
|
|
,[RangeAve] =s.RangeAve
|
|
,[RangeHigh] =s.RangeHigh
|
|
,[Created] =s.Created
|
|
,[TimesUsed] =s.TimesUsed
|
|
,[SensorCategory] =s.SensorCategory
|
|
,[BypassFilter] =s.BypassFilter
|
|
,[CouplingMode] =s.CouplingMode
|
|
,[Version] =s.[Version]
|
|
,[LastModified] =s.LastModified
|
|
,[ModifiedBy] =s.ModifiedBy
|
|
,[LocalOnly] =s.LocalOnly
|
|
,[AxisNumber] =s.AxisNumber
|
|
,[NumberOfAxes] =s.NumberOfAxes
|
|
,[UserTags] =s.UserTags
|
|
,[DoNotUse] =s.DoNotUse
|
|
,[Broken] =s.Broken
|
|
from @SensorsAnalog s
|
|
where [dbo].[SensorsAnalog].[SerialNumber] =s.SerialNumber
|
|
|
|
insert into [dbo].[SensorsAnalog]
|
|
([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])
|
|
select
|
|
s.SerialNumber
|
|
,s.UserSerialNumber
|
|
,s.Model
|
|
,s.Manufacturer
|
|
,s.[Status]
|
|
,s.MeasurementUnit
|
|
,s.OffsetToleranceLow
|
|
,s.OffsetToleranceHigh
|
|
,s.eId
|
|
,s.Capacity
|
|
,s.Comment
|
|
,s.BridgeType
|
|
,s.BridgeLegMode
|
|
,s.Shunt
|
|
,s.Invert
|
|
,s.UserValue1
|
|
,s.UserValue2
|
|
,s.UserValue3
|
|
,s.FilterClass
|
|
,s.BridgeResistance
|
|
,s.IsoCode
|
|
,s.CheckOffset
|
|
,s.SupportedExcitation
|
|
,s.InitialEU
|
|
,s.CalInterval
|
|
,s.CalibrationSignal
|
|
,s.InternalShuntResistance
|
|
,s.ExternalShuntResistance
|
|
,s.UniPolar
|
|
,s.RangeLow
|
|
,s.RangeAve
|
|
,s.RangeHigh
|
|
,s.Created
|
|
,s.TimesUsed
|
|
,s.SensorCategory
|
|
,s.BypassFilter
|
|
,s.CouplingMode
|
|
,s.[Version]
|
|
,s.LastModified
|
|
,s.ModifiedBy
|
|
,s.LocalOnly
|
|
,s.AxisNumber
|
|
,s.NumberOfAxes
|
|
,s.UserTags
|
|
,s.DoNotUse
|
|
,s.Broken
|
|
from @SensorsAnalog s where s.Exist = 0
|
|
/* insert/update SensorCalibrations record */
|
|
end
|
|
end
|
|
end
|
|
END
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
GO
|