285 lines
17 KiB
Plaintext
285 lines
17 KiB
Plaintext
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_DBImportDASList]') AND type in (N'P', N'PC'))
|
|
DROP PROCEDURE [dbo].[sp_DBImportDASList]
|
|
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_DBImportDASList]') AND type in (N'P', N'PC'))
|
|
BEGIN
|
|
EXEC dbo.sp_executesql @statement = N'CREATE PROCEDURE [dbo].[sp_DBImportDASList] AS'
|
|
END
|
|
GO
|
|
ALTER PROCEDURE [dbo].[sp_DBImportDASList]
|
|
@DASList xml
|
|
,@errorNumber int output
|
|
,@errorMessage nvarchar(250) output
|
|
AS
|
|
BEGIN
|
|
|
|
set @errorNumber = 0
|
|
set @errorMessage = space(0)
|
|
declare @tDAS table (
|
|
[DASId] [int] NULL,
|
|
[SerialNumber] [nvarchar](50) NULL,
|
|
[Type] [int] NULL,
|
|
[MaxModules] [int] NULL,
|
|
[MaxMemory] [bigint] NULL,
|
|
[MaxSampleRate] [decimal](18, 0) NULL,
|
|
[MinSampleRate] [decimal](18, 0) NULL,
|
|
[FirmwareVersion] [nvarchar](50) NULL,
|
|
[CalDate] [datetime] NULL,
|
|
[ProtocolVersion] [int] NULL,
|
|
[LastModified] [datetime] NULL,
|
|
[LastModifiedBy] [nvarchar](50) NULL,
|
|
[Version] [int] NULL,
|
|
[LocalOnly] [bit] NULL,
|
|
[LastUsed] [datetime] NULL,
|
|
[LastUsedBy] [nvarchar](50) NULL,
|
|
[Connection] [nvarchar](50) NULL,
|
|
[Channels] [int] NULL,
|
|
[Position] [nvarchar](50) NULL,
|
|
[ChannelTypes] [nvarchar](255) NULL,
|
|
[Reprogramable] [bit] NULL,
|
|
[Reconfigurable] [bit] NULL,
|
|
[IsModule] [bit] NULL,
|
|
[PositionOnDistributor] [smallint] NULL,
|
|
[PositionOnChain] [smallint] NULL,
|
|
[Port] [smallint] NULL,
|
|
[ParentDAS] [nvarchar](50) NULL,
|
|
[DASChannels] [xml] NULL,
|
|
[Processed] [bit] NULL)
|
|
|
|
declare @DASId int
|
|
,@SerialNumber nvarchar(50)
|
|
,@Type int
|
|
,@MaxModules int
|
|
,@MaxMemory bigint
|
|
,@MaxSampleRate decimal(18,0)
|
|
,@MinSampleRate decimal(18,0)
|
|
,@FirmwareVersion nvarchar(50)
|
|
,@CalDate datetime
|
|
,@ProtocolVersion int
|
|
,@LastModified datetime
|
|
,@LastModifiedBy nvarchar(50)
|
|
,@Version int
|
|
,@LocalOnly bit
|
|
,@LastUsed datetime
|
|
,@LastUsedBy nvarchar(50)
|
|
,@Connection nvarchar(50)
|
|
,@Channels int
|
|
,@Position nvarchar(50)
|
|
,@ChannelTypes nvarchar(255)
|
|
,@Reprogramable bit
|
|
,@Reconfigurable bit
|
|
,@IsModule bit
|
|
,@PositionOnDistributor smallint
|
|
,@PositionOnChain smallint
|
|
,@Port smallint
|
|
,@ParentDAS nvarchar(50)
|
|
,@new_id int
|
|
|
|
insert into @tDAS
|
|
([DASId]
|
|
,[SerialNumber]
|
|
,[Type]
|
|
,[MaxModules]
|
|
,[MaxMemory]
|
|
,[MaxSampleRate]
|
|
,[MinSampleRate]
|
|
,[FirmwareVersion]
|
|
,[CalDate]
|
|
,[ProtocolVersion]
|
|
,[LastModified]
|
|
,[LastModifiedBy]
|
|
,[Version]
|
|
,[LocalOnly]
|
|
,[LastUsed]
|
|
,[LastUsedBy]
|
|
,[Connection]
|
|
,[Channels]
|
|
,[Position]
|
|
,[ChannelTypes]
|
|
,[Reprogramable]
|
|
,[Reconfigurable]
|
|
,[IsModule]
|
|
,[PositionOnDistributor]
|
|
,[PositionOnChain]
|
|
,[Port]
|
|
,[ParentDAS]
|
|
,[DASChannels]
|
|
,[Processed])
|
|
select
|
|
isnull(t.x.value('@DASId', 'int'), 0)
|
|
,t.x.value('@SerialNumber', 'nvarchar(50)')
|
|
,t.x.value('@Type', 'int')
|
|
,t.x.value('@MaxModules', 'int')
|
|
,t.x.value('@MaxMemory', 'bigint')
|
|
,t.x.value('@MaxSampleRate', 'decimal(18, 0)')
|
|
,t.x.value('@MinSampleRate', 'decimal(18, 0)')
|
|
,t.x.value('@FirmwareVersion', 'nvarchar(50)')
|
|
,t.x.value('@CalDate', 'datetime')
|
|
,t.x.value('@ProtocolVersion', 'int')
|
|
,t.x.value('@LastModified', 'datetime')
|
|
,t.x.value('@LastModifiedBy', 'nvarchar(50)')
|
|
,t.x.value('@Version', 'int')
|
|
,t.x.value('@LocalOnly', 'bit')
|
|
,t.x.value('@LastUsed', 'datetime')
|
|
,t.x.value('@LastUsedBy', 'nvarchar(50)')
|
|
,t.x.value('@Connection', 'nvarchar(50)')
|
|
,t.x.value('@Channels', 'int')
|
|
,t.x.value('@Position', 'nvarchar(50)')
|
|
,t.x.value('@ChannelTypes', 'nvarchar(255)')
|
|
,t.x.value('@Reprogramable', 'bit')
|
|
,t.x.value('@Reconfigurable', 'bit')
|
|
,t.x.value('@IsModule', 'bit')
|
|
,t.x.value('@PositionOnDistributor', 'smallint')
|
|
,t.x.value('@PositionOnChain', 'smallint')
|
|
,t.x.value('@Port', 'smallint')
|
|
,t.x.value('@ParentDAS', 'nvarchar(50)')
|
|
,t.x.query('./DASChannels')
|
|
,0
|
|
from @DASList.nodes('/DASList/DASHardware') t(x)
|
|
where ltrim(rtrim(isnull(t.x.value('@SerialNumber', 'nvarchar(50)'), space(0)))) != space(0)
|
|
OPTION (OPTIMIZE FOR ( @DASList = NULL ))
|
|
|
|
while (Select Count(*) From @tDAS Where Processed = 0) > 0
|
|
Begin
|
|
begin try
|
|
begin transaction tDAS
|
|
declare @DASSerialNumber nvarchar(50)
|
|
declare @DASChannels xml
|
|
set @DASSerialNumber = (select top 1 SerialNumber from @tDAS t where t.[Processed] = 0)
|
|
|
|
select top 1
|
|
@DASId = [DASId]
|
|
,@SerialNumber = [SerialNumber]
|
|
,@Type = [Type]
|
|
,@MaxModules = [MaxModules]
|
|
,@MaxMemory = [MaxMemory]
|
|
,@MaxSampleRate = [MaxSampleRate]
|
|
,@MinSampleRate = [MinSampleRate]
|
|
,@FirmwareVersion = [FirmwareVersion]
|
|
,@CalDate = [CalDate]
|
|
,@ProtocolVersion = [ProtocolVersion]
|
|
,@LastModified = getdate() /* [LastModified]*/
|
|
,@LastModifiedBy = 'DBImport' /* [LastModifiedBy] */
|
|
,@Version = [Version]
|
|
,@LocalOnly = [LocalOnly]
|
|
,@LastUsed = [LastUsed]
|
|
,@LastUsedBy = [LastUsedBy]
|
|
,@Connection = [Connection]
|
|
,@Channels = [Channels]
|
|
,@Position = [Position]
|
|
,@ChannelTypes = [ChannelTypes]
|
|
,@Reprogramable = [Reprogramable]
|
|
,@Reconfigurable = [Reconfigurable]
|
|
,@IsModule = [IsModule]
|
|
,@PositionOnDistributor = [PositionOnDistributor]
|
|
,@PositionOnChain = [PositionOnChain]
|
|
,@Port = [Port]
|
|
,@ParentDAS = [ParentDAS]
|
|
,@DASChannels = [DASChannels]
|
|
from @tDAS t where t.[SerialNumber] = @DASSerialNumber
|
|
|
|
exec sp_DASUpdateInsert @DASId
|
|
,@SerialNumber
|
|
,@Type
|
|
,@MaxModules
|
|
,@MaxMemory
|
|
,@MaxSampleRate
|
|
,@MinSampleRate
|
|
,@FirmwareVersion
|
|
,@CalDate
|
|
,@ProtocolVersion
|
|
,@LastModified
|
|
,@LastModifiedBy
|
|
,@Version
|
|
,@LocalOnly
|
|
,@LastUsed
|
|
,@LastUsedBy
|
|
,@Connection
|
|
,@Channels
|
|
,@Position
|
|
,@ChannelTypes
|
|
,@Reprogramable
|
|
,@Reconfigurable
|
|
,@IsModule
|
|
,@PositionOnDistributor
|
|
,@PositionOnChain
|
|
,@Port
|
|
,@ParentDAS
|
|
,@DASId output
|
|
,@errorNumber output
|
|
,@errorMessage output
|
|
|
|
delete from dbo.DASChannels where DASId = @DASId
|
|
|
|
insert into dbo.DASChannels
|
|
([DASId]
|
|
,[ChannelIdx]
|
|
,[SupportedBridges]
|
|
,[SupportedExcitations]
|
|
,[DASDisplayOrder]
|
|
,[LocalOnly]
|
|
,[SupportedDigitalInputModes]
|
|
,[SupportedSquibFireModes]
|
|
,[SupportedDigitalOutputModes]
|
|
,[ModuleSerialNumber]
|
|
,[SettingId]
|
|
,[ModuleArrayIndex])
|
|
select isnull(t.x.value('@DASId', 'int'), 0)
|
|
,t.x.value('@ChannelIdx','int')
|
|
,t.x.value('@SupportedBridges','int')
|
|
,t.x.value('@SupportedExcitations','int')
|
|
,t.x.value('@DASDisplayOrder','int')
|
|
,t.x.value('@LocalOnly','bit')
|
|
,t.x.value('@SupportedDigitalInputModes' ,'int')
|
|
,t.x.value('@SupportedSquibFireModes','int')
|
|
,t.x.value('@SupportedDigitalOutputModes','int')
|
|
,t.x.value('@ModuleSerialNumber','nvarchar(16)')
|
|
,0
|
|
,t.x.value('@ModuleArrayIndex','int')
|
|
from @DASChannels.nodes('/DASChannels/DASChannel') t(x)
|
|
OPTION (OPTIMIZE FOR ( @DASChannels = NULL ))
|
|
|
|
update @tDAS set [Processed] = 1 where SerialNumber = @DASSerialNumber
|
|
|
|
commit transaction tDAS
|
|
end try
|
|
begin catch
|
|
set @errorMessage = error_message()
|
|
set @errorNumber = error_number()
|
|
|
|
rollback transaction tDAS
|
|
end catch;
|
|
end
|
|
END
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
GO
|