297 lines
22 KiB
Transact-SQL
297 lines
22 KiB
Transact-SQL
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_DBImportTestSetups]') AND type in (N'P', N'PC'))
|
||
DROP PROCEDURE [dbo].[sp_DBImportTestSetups]
|
||
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_DBImportTestSetups]') AND type in (N'P', N'PC'))
|
||
BEGIN
|
||
EXEC dbo.sp_executesql @statement = N'CREATE PROCEDURE [dbo].[sp_DBImportTestSetups] AS'
|
||
END
|
||
GO
|
||
ALTER PROCEDURE [dbo].[sp_DBImportTestSetups]
|
||
@TestSetups xml
|
||
,@errorNumber int output
|
||
,@errorMessage nvarchar(250) output
|
||
AS
|
||
BEGIN
|
||
set @errorNumber = 0
|
||
set @errorMessage = space(0)
|
||
|
||
declare @tTestSetup table (recId int identity(1,1), TestSetup xml, Processed bit)
|
||
insert into @tTestSetup
|
||
select t.x.query('.'), 0 from @TestSetups.nodes('/TestSetups/TestSetup') t(x)
|
||
|
||
while (Select Count(*) From @tTestSetup Where Processed = 0) > 0
|
||
Begin
|
||
begin try
|
||
begin transaction tTestSetup
|
||
declare
|
||
@recId int
|
||
, @TestSetup xml
|
||
, @Hardware xml
|
||
, @MetaData xml
|
||
, @Graphs xml
|
||
|
||
select top 1 @recId = recId , @TestSetup = TestSetup from @tTestSetup Where Processed = 0
|
||
|
||
declare
|
||
@TestSetupId int
|
||
, @TestSetupName nvarchar(50)
|
||
, @SetupDescription nvarchar(50)
|
||
, @AutomaticTestProgression bit
|
||
, @AutomaticProgressionDelayMS int
|
||
, @InvertTrigger bit
|
||
, @InvertStart bit
|
||
, @ViewDiagnostics bit
|
||
, @VerifyChannels bit
|
||
, @AutoVerifyChannels bit
|
||
, @VerifyChannelsDelayMS int
|
||
, @RecordingMode nvarchar(50) /* smallint*/
|
||
, @SamplesPerSecond float
|
||
, @PreTriggerSeconds float
|
||
, @PostTriggerSeconds float
|
||
, @StrictDiagnostics bit
|
||
, @RequireConfirmationOnErrors bit
|
||
, @ROIDownload bit
|
||
, @ViewROIDownload bit
|
||
, @DownloadAll bit
|
||
, @ViewRealtime bit
|
||
, @RealtimePlotCount smallint
|
||
, @ROIStart float
|
||
, @ROIEnd float
|
||
, @ViewDownloadAll bit
|
||
, @Export bit
|
||
, @ExportFormat bigint
|
||
, @LabDetails nvarchar(50)
|
||
, @UseLabDetails bit
|
||
, @CustomerId int
|
||
, @LabratoryId int
|
||
, @TestEngineerId int
|
||
, @CustomerDetails nvarchar(50)
|
||
, @UseCustomerDetails bit
|
||
, @AllowMissingSensors bit
|
||
, @AllowSensorIdToBlankChannel bit
|
||
, @LocalOnly bit
|
||
, @LastModified datetime
|
||
, @LastModifiedBy nvarchar(50)
|
||
, @TurnOffExcitation bit
|
||
, @TriggerCheckRealtime bit
|
||
, @TriggerCheckStep bit
|
||
, @PostTestDiagnostics int
|
||
, @ExportFolder nvarchar(150)
|
||
, @DownloadFolder nvarchar(150)
|
||
, @CommonStatusLine bit
|
||
, @SameAsDownloadFolder bit
|
||
, @UploadData bit
|
||
, @UploadDataFolder nvarchar(150)
|
||
, @Settings nvarchar(255)
|
||
, @WarnOnBatteryFail bit
|
||
, @Dirty bit
|
||
, @Complete bit
|
||
, @Error nvarchar(255)
|
||
, @TestEngineerDetails nvarchar(50)
|
||
, @UseTestEngineerDetails bit
|
||
, @UserTags varbinary(max)
|
||
, @DoAutoArm bit
|
||
, @CheckoutMode bit
|
||
, @ISFFile nvarchar(4000)
|
||
, @QuitTestWithoutWarning bit
|
||
, @NotAllChannelsRealTime bit
|
||
, @NotAllChannelsViewer bit
|
||
, @SuppressMissingSensorsWarning bit
|
||
|
||
select
|
||
@TestSetupName = t.x.value('(./SetupName)[1]', 'nvarchar(50)')
|
||
,@SetupDescription = t.x.value('(./SetupDescription)[1]', 'nvarchar(50)')
|
||
,@AutomaticTestProgression = t.x.value('(./AutomaticTestProgression)[1]', 'bit')
|
||
,@AutomaticProgressionDelayMS = t.x.value('(./AutomaticProgressionDelayMS)[1]', 'int')
|
||
,@InvertTrigger = t.x.value('(./InvertTrigger)[1]', 'bit')
|
||
,@InvertStart = t.x.value('(./InvertStart)[1]', 'bit')
|
||
,@ViewDiagnostics = t.x.value('(./ViewDiagnostics)[1]', 'bit')
|
||
,@VerifyChannels = t.x.value('(./VerifyChannels)[1]', 'bit')
|
||
,@AutoVerifyChannels = t.x.value('(./AutoVerifyChannels)[1]', 'bit')
|
||
,@VerifyChannelsDelayMS = t.x.value('(./VerifyChannelsDelayMS)[1]', 'bit')
|
||
,@RecordingMode = t.x.value('(./RecordingMode)[1]', 'nvarchar(50)')
|
||
,@SamplesPerSecond = t.x.value('(./SamplesPerSecond)[1]', 'float')
|
||
,@PreTriggerSeconds = t.x.value('(./PreTriggerSeconds)[1]', 'float')
|
||
,@PostTriggerSeconds = t.x.value('(./PostTriggerSeconds)[1]', 'float')
|
||
,@StrictDiagnostics = t.x.value('(./StrictDiagnostics)[1]', 'bit')
|
||
,@RequireConfirmationOnErrors = t.x.value('(./RequireConfirmationOnErrors)[1]', 'bit')
|
||
,@ROIDownload = t.x.value('(./ROIDownload)[1]', 'bit')
|
||
,@ViewROIDownload = t.x.value('(./ViewROIDownload)[1]', 'bit')
|
||
,@DownloadAll = t.x.value('(./DownloadAll)[1]', 'bit')
|
||
,@ViewRealtime = t.x.value('(./ViewRealtime)[1]', 'bit')
|
||
,@RealtimePlotCount = t.x.value('(./RealtimePlotCount)[1]', 'smallint')
|
||
,@ROIStart = t.x.value('(./ROIStart)[1]', 'float')
|
||
,@ROIEnd = t.x.value('(./ROIEnd)[1]', 'float')
|
||
,@ViewDownloadAll = t.x.value('(./ViewDownloadAll)[1]', 'bit')
|
||
,@Export = t.x.value('(./Export)[1]', 'bit')
|
||
,@ExportFormat = t.x.value('(./ExportFormat)[1]', 'bigint')
|
||
,@LabDetails = t.x.value('(./LabDetails)[1]', 'nvarchar(50)')
|
||
,@UseLabDetails = t.x.value('(./UseLabDetails)[1]', 'bit')
|
||
,@CustomerDetails = t.x.value('(./CustomerDetails)[1]', 'nvarchar(50)')
|
||
,@UseCustomerDetails = t.x.value('(./UseCustomerDetails)[1]', 'bit')
|
||
,@AllowMissingSensors = t.x.value('(./AllowMissingSensors)[1]', 'bit')
|
||
,@AllowSensorIdToBlankChannel = t.x.value('(./AllowSensorIdToBlankChannel)[1]', 'bit')
|
||
,@LocalOnly = t.x.value('(./LocalOnly)[1]', 'bit')
|
||
,@LastModified = t.x.value('(./LastModified)[1]', 'datetime')
|
||
,@LastModifiedBy = t.x.value('(./LastModifiedBy)[1]', 'nvarchar(50)')
|
||
,@TurnOffExcitation = t.x.value('(./TurnOffExcitation)[1]', 'bit')
|
||
,@TriggerCheckRealtime = t.x.value('(./TriggerCheckRealtime)[1]', 'bit')
|
||
,@TriggerCheckStep = t.x.value('(./TriggerCheckStep)[1]', 'bit')
|
||
,@PostTestDiagnostics = t.x.value('(./PostTestDiagnostics)[1]', 'bit')
|
||
,@ExportFolder = t.x.value('(./ExportFolder)[1]', 'nvarchar(150)')
|
||
,@DownloadFolder = t.x.value('(./DownloadFolder)[1]', 'nvarchar(150)')
|
||
,@CommonStatusLine = t.x.value('(./CommonStatusLine)[1]', 'bit')
|
||
,@SameAsDownloadFolder = t.x.value('(./SameAsDownloadFolder)[1]', 'bit')
|
||
,@UploadData = t.x.value('(./UploadData)[1]', 'bit')
|
||
,@UploadDataFolder = t.x.value('(./UploadDataFolder)[1]', 'nvarchar(150)')
|
||
,@Settings = t.x.value('(./Settings)[1]', 'nvarchar(50)')
|
||
,@WarnOnBatteryFail = t.x.value('(./WarnOnBatteryFail)[1]', 'nvarchar(255)')
|
||
,@Dirty = t.x.value('(./Dirty)[1]', 'bit')
|
||
,@Complete = t.x.value('(./Complete)[1]', 'bit')
|
||
,@Error = t.x.value('(./ErrorMessage)[1]', 'nvarchar(255)')
|
||
,@TestEngineerDetails = t.x.value('(./TestEngineerDetails)[1]', 'nvarchar(50)')
|
||
,@UseTestEngineerDetails = t.x.value('(./UseTestEngineerDetails)[1]', 'bit')
|
||
,@UserTags = t.x.value('(./UserTags)[1]', 'varbinary(max)')
|
||
,@DoAutoArm = t.x.value('(./DoAutoArm)[1]', 'bit')
|
||
,@CheckoutMode = t.x.value('(./CheckoutMode)[1]', 'bit')
|
||
,@QuitTestWithoutWarning = t.x.value('(./QuitTestWithoutWarning)[1]', 'bit')
|
||
,@SuppressMissingSensorsWarning = t.x.value('(./SuppressMissingSensorsWarning)[1]', 'bit')
|
||
,@ISFFile = t.x.value('(./ISFFile)[1]', 'nvarchar(4000)')
|
||
,@NotAllChannelsRealTime = t.x.value('(./NotAllChannelsRealTime)[1]', 'bit')
|
||
,@NotAllChannelsViewer = t.x.value('(./NotAllChannelsViewer)[1]', 'bit')
|
||
from @TestSetup.nodes('/TestSetup/Fields') t(x)
|
||
OPTION (OPTIMIZE FOR ( @TestSetup = NULL ))
|
||
|
||
exec [dbo].[sp_TestSetupsInsert]
|
||
@TestSetupName
|
||
,@SetupDescription
|
||
,@AutomaticTestProgression
|
||
,@AutomaticProgressionDelayMS
|
||
,@InvertTrigger
|
||
,@InvertStart
|
||
,@ViewDiagnostics
|
||
,@VerifyChannels
|
||
,@AutoVerifyChannels
|
||
,@VerifyChannelsDelayMS
|
||
,@RecordingMode
|
||
,@SamplesPerSecond
|
||
,@PreTriggerSeconds
|
||
,@PostTriggerSeconds
|
||
,@StrictDiagnostics
|
||
,@RequireConfirmationOnErrors
|
||
,@ROIDownload
|
||
,@ViewROIDownload
|
||
,@DownloadAll
|
||
,@ViewRealtime
|
||
,@RealtimePlotCount
|
||
,@ROIStart
|
||
,@ROIEnd
|
||
,@ViewDownloadAll
|
||
,@Export
|
||
,@ExportFormat
|
||
,@LabDetails
|
||
,@UseLabDetails
|
||
,@CustomerDetails
|
||
,@UseCustomerDetails
|
||
,@AllowMissingSensors
|
||
,@AllowSensorIdToBlankChannel
|
||
,@LocalOnly
|
||
,@LastModified
|
||
,@LastModifiedBy
|
||
,@TurnOffExcitation
|
||
,@TriggerCheckRealtime
|
||
,@TriggerCheckStep
|
||
,@PostTestDiagnostics
|
||
,@ExportFolder
|
||
,@DownloadFolder
|
||
,@CommonStatusLine
|
||
,@SameAsDownloadFolder
|
||
,@UploadData
|
||
,@UploadDataFolder
|
||
,@Settings
|
||
,@WarnOnBatteryFail
|
||
,@Dirty
|
||
,@Complete
|
||
,@Error
|
||
,@TestEngineerDetails
|
||
,@UseTestEngineerDetails
|
||
,@UserTags
|
||
,@DoAutoArm
|
||
,@CheckoutMode
|
||
,@ISFFile
|
||
,@QuitTestWithoutWarning
|
||
,@NotAllChannelsRealTime
|
||
,@NotAllChannelsViewer
|
||
,@SuppressMissingSensorsWarning
|
||
,@TestSetupId output
|
||
,@errorNumber output
|
||
,@errorMessage output
|
||
|
||
if(@TestSetupId !=0)
|
||
begin
|
||
|
||
exec dbo.sp_DBImportTestSetupGroups @TestSetupId, @TestSetup, @errorNumber output, @errorMessage output
|
||
|
||
|
||
select @Hardware = t.x.query('.') from @TestSetup.nodes('/TestSetup/HardwareOverrides') t(x)
|
||
if(@Hardware is not null)
|
||
begin
|
||
exec dbo.sp_DBImportTestSetupHardware @TestSetupId , @TestSetupName, @Hardware,@errorNumber output, @errorMessage output
|
||
end
|
||
|
||
select @MetaData = t.x.query('.') from @TestSetup.nodes('/TestSetup/MetaDatas') t(x)
|
||
if(@MetaData is not null)
|
||
begin
|
||
exec dbo.sp_DBImportTestSetupObjectMetaData @TestSetupId, @MetaData
|
||
end
|
||
|
||
select @Graphs = t.x.query('.') from @TestSetup.nodes('/TestSetup/Graphs') t(x)
|
||
if(@Graphs is not null)
|
||
begin
|
||
exec dbo.sp_DBImportTestSetupGraphs @TestSetupId, @Graphs
|
||
end
|
||
end
|
||
|
||
update @tTestSetup set [Processed] = 1 where recId = @recId
|
||
commit transaction tTestSetup
|
||
end try
|
||
begin catch
|
||
set @errorNumber = error_number()
|
||
set @errorMessage = error_message()
|
||
rollback transaction tTestSetup
|
||
end catch;
|
||
end
|
||
END
|
||
|
||
|
||
|
||
|
||
|
||
|
||
|
||
|
||
|
||
|
||
|
||
|
||
|
||
|
||
|
||
|
||
|
||
|
||
|
||
|
||
|
||
|
||
|
||
|
||
|
||
|
||
|
||
GO
|