318 lines
20 KiB
Transact-SQL
318 lines
20 KiB
Transact-SQL
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
|