285 lines
17 KiB
Transact-SQL
285 lines
17 KiB
Transact-SQL
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
|