163 lines
9.6 KiB
Plaintext
163 lines
9.6 KiB
Plaintext
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_DBImportTestSetupGroups]') AND type in (N'P', N'PC'))
|
|
DROP PROCEDURE [dbo].[sp_DBImportTestSetupGroups]
|
|
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_DBImportTestSetupGroups]') AND type in (N'P', N'PC'))
|
|
BEGIN
|
|
EXEC dbo.sp_executesql @statement = N'CREATE PROCEDURE [dbo].[sp_DBImportTestSetupGroups] AS'
|
|
END
|
|
GO
|
|
ALTER PROCEDURE [dbo].[sp_DBImportTestSetupGroups]
|
|
@TestSetupId int
|
|
,@TestSetup xml
|
|
,@errorNumber int output
|
|
,@errorMessage nvarchar(250) output
|
|
AS
|
|
BEGIN
|
|
set @errorNumber = 0
|
|
set @errorMessage = space(0)
|
|
|
|
declare @TestGroups xml
|
|
|
|
declare @tTestGroup table(
|
|
recId int identity(1,1)
|
|
,TestSetupId int
|
|
,TestObjectId int
|
|
,TestObjectName varchar(50)
|
|
,TestSetupName varchar(50)
|
|
,TargetSampleRate int
|
|
,ExcitationWarmupTimeMS float
|
|
,LocalOnly bit
|
|
,TestObjectType varchar(50)
|
|
,TestObjectPosition varchar(50)
|
|
,ChannelSetting xml
|
|
,Processed bit)
|
|
|
|
declare @t1 table (x xml)
|
|
|
|
insert into @t1
|
|
select convert(xml, replace(replace(convert(varchar(max), t.x.query('.')), '<TestObject>', '<AddedGroup>1</AddedGroup>'), '</TestObject>', space(0)))
|
|
from @TestSetup.nodes('/TestSetup/AddedGroups/TestObject') t(x)
|
|
|
|
insert into @t1
|
|
select convert(xml, replace(replace(convert(varchar(max), t.x.query('.')), '<TestObject>', '<AddedGroup>0</AddedGroup>'), '</TestObject>', space(0)))
|
|
from @TestSetup.nodes('/TestSetup/TestObjects/TestObject') t(x)
|
|
|
|
set @TestGroups = (select x TestObject from @t1 for xml path(''), root('TestObjects'))
|
|
|
|
insert into @tTestGroup
|
|
(TestSetupId
|
|
,TestObjectId
|
|
,TestObjectName
|
|
,TestSetupName
|
|
,TargetSampleRate
|
|
,ExcitationWarmupTimeMS
|
|
,LocalOnly
|
|
,TestObjectType
|
|
,TestObjectPosition
|
|
,ChannelSetting
|
|
,Processed)
|
|
select
|
|
@TestSetupId /* dbo.foo_IdGetTestSetup(t.x.value('(TestSetupName)[1]', 'nvarchar(50)')) */
|
|
,dbo.foo_IdGetTestSetupObject(t.x.value('(TestObjectSerialNumber)[1]', 'nvarchar(50)') )
|
|
,t.x.value('(TestObjectSerialNumber)[1]', 'nvarchar(50)')
|
|
,t.x.value('(TestSetupName)[1]', 'nvarchar(50)')
|
|
,t.x.value('(TargetSampleRate)[1]', 'int')
|
|
,t.x.value('(ExcitationWarmupTimeMS)[1]', 'float')
|
|
,t.x.value('(LocalOnly)[1]', 'bit')
|
|
,t.x.value('(TestObjectType)[1]', 'nvarchar(50)')
|
|
,t.x.value('(TestObjectPosition)[1]', 'nvarchar(50)')
|
|
,convert(xml, '<ChannelSettings>' + replace(replace(convert(varchar(max), t.x.query('ChannelSettings')), '<ChannelSettings>', '<ChannelSetting>'), '</ChannelSettings>', '</ChannelSetting>') + '</ChannelSettings>')
|
|
,0
|
|
from @TestGroups.nodes('/TestObjects/TestObject') t(x)
|
|
|
|
while (Select Count(*) From @tTestGroup Where Processed = 0) > 0
|
|
Begin
|
|
begin try
|
|
begin transaction tTestObject
|
|
declare @recId int
|
|
,@TestObjectId int
|
|
,@TestObjectName varchar(50)
|
|
,@TestSetupName varchar(50)
|
|
,@TargetSampleRate int
|
|
,@ExcitationWarmupTimeMS float
|
|
,@LocalOnly bit
|
|
,@TestObjectType int
|
|
,@TestObjectPosition varchar(1)
|
|
,@ChannelSetting xml
|
|
|
|
select top 1
|
|
@recId = recId
|
|
,@TestSetupId = TestSetupId
|
|
,@TestObjectId = TestObjectId
|
|
,@TestObjectName = TestObjectName
|
|
,@TestSetupName = TestSetupName
|
|
,@TargetSampleRate = TargetSampleRate
|
|
,@ExcitationWarmupTimeMS = ExcitationWarmupTimeMS
|
|
,@LocalOnly = LocalOnly
|
|
,@TestObjectType = TestObjectType
|
|
,@TestObjectPosition = TestObjectPosition
|
|
,@ChannelSetting = ChannelSetting
|
|
from @tTestGroup where Processed = 0
|
|
|
|
declare @TestSetupObjectId int
|
|
set @TestObjectId = dbo.foo_IdGetTestObject(@TestObjectName)
|
|
|
|
exec [dbo].[sp_TestSetupObjectsUpdateInsert] @TestSetupId
|
|
,@TestSetupName
|
|
,@TestObjectId
|
|
,@TestObjectName
|
|
,@TargetSampleRate
|
|
,@ExcitationWarmupTimeMS
|
|
,@LocalOnly
|
|
,@TestObjectType
|
|
,@TestObjectPosition
|
|
,@TestSetupObjectId output
|
|
,@errorNumber output
|
|
,@errorMessage output
|
|
|
|
exec dbo.sp_DBImportGroupChannelSetting @TestObjectId, @TestObjectName, @ChannelSetting, @errorNumber output, @errorMessage output
|
|
|
|
update @tTestGroup set [Processed] = 1 where recId = @recId
|
|
|
|
commit transaction tTestObject
|
|
end try
|
|
begin catch
|
|
set @errorNumber = error_number()
|
|
set @errorMessage = error_message()
|
|
rollback transaction tTestObject
|
|
end catch;
|
|
end
|
|
END
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
GO
|