371 lines
28 KiB
Plaintext
371 lines
28 KiB
Plaintext
|
|
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
|
|
, @CalculatedChannels xml
|
|
, @LevelTriggers xml
|
|
, @DASSettings xml
|
|
|
|
select top 1 @recId = recId , @TestSetup = TestSetup from @tTestSetup Where Processed = 0
|
|
|
|
declare
|
|
@TestSetupId int
|
|
, @TestSetupName nvarchar(255)
|
|
, @SetupDescription nvarchar(255)
|
|
, @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
|
|
, @RegionsOfInterest nvarchar(max)
|
|
, @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)
|
|
, @UploadExportsOnly bit
|
|
, @Settings nvarchar(4000)
|
|
, @WarnOnBatteryFail bit
|
|
, @Dirty bit
|
|
, @Complete bit
|
|
, @Error nvarchar(255)
|
|
, @TestEngineerDetails nvarchar(50)
|
|
, @UseTestEngineerDetails bit
|
|
, @UserTags varbinary(max)
|
|
,@UserTagsText nvarchar(4000)
|
|
, @DoAutoArm bit
|
|
, @CheckoutMode bit
|
|
, @ISFFile nvarchar(4000)
|
|
, @QuitTestWithoutWarning bit
|
|
, @NotAllChannelsRealTime bit
|
|
, @NotAllChannelsViewer bit
|
|
, @SuppressMissingSensorsWarning bit
|
|
|
|
select
|
|
@TestSetupName = t.x.value('(./SetupName)[1]', 'nvarchar(255)')
|
|
,@SetupDescription = t.x.value('(./SetupDescription)[1]', 'nvarchar(255)')
|
|
,@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]', 'int')
|
|
,@RecordingMode = dbo.foo_RecordingModeConverterToInt(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')
|
|
,@RegionsOfInterest = t.x.value('(./RegionsOfInterest)[1]', 'nvarchar(max)')
|
|
,@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)')
|
|
,@UploadExportsOnly = isnull(t.x.value('(./UploadExportsOnly)[1]', 'bit'), 0)
|
|
,@Settings = t.x.value('(./Settings)[1]', 'nvarchar(4000)')
|
|
,@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)')*/
|
|
,@UserTagsText = t.x.value('(./UserTags)[1]', 'nvarchar(4000)')
|
|
,@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 ))
|
|
|
|
declare @tagTable TABLE (Element nvarchar(4000), Processed bit)
|
|
declare @tagIdTable TABLE (Id int, Processed bit)
|
|
insert into @tagTable (Element, Processed) select * from dbo.foo_SplitDelimitedString(@UserTagsText, ',')
|
|
|
|
/*call foo_IdGetTag to get the tag id */
|
|
while (Select Count(*) From @tagTable Where Processed = 0) > 0
|
|
Begin
|
|
begin try
|
|
declare @tagName nvarchar(4000)
|
|
declare @tagIdTemp int
|
|
select top 1 @tagName = Element from @tagTable where Processed = 0
|
|
set @tagIdTemp = dbo.foo_IdGetTag(@tagName)
|
|
insert into @tagIdTable (Id, Processed) VALUES (@tagIdTemp, 0)
|
|
end try
|
|
begin catch
|
|
set @errorNumber = error_number()
|
|
set @errorMessage = error_message()
|
|
rollback transaction tSensorsAnalog
|
|
end catch;
|
|
update @tagTable set Processed = 1 where Element = @tagName
|
|
End
|
|
|
|
/*store in a byte array*/
|
|
while (Select Count(*) From @tagIdTable Where Processed = 0) > 0
|
|
Begin
|
|
begin try
|
|
declare @tagId int
|
|
declare @tagIdByte varbinary(4)
|
|
select top 1 @tagId = Id from @tagIdTable where Processed = 0
|
|
--set @tagIdByte = dbo.foo_ConvertIntToBytes(@tagId)
|
|
set @tagIdByte = CONVERT(VARBINARY(MAX), REVERSE(CONVERT(VARBINARY(4), @tagId)))
|
|
set @UserTags = CONVERT(varbinary(max), CONCAT(@UserTags, @tagIdByte))
|
|
end try
|
|
begin catch
|
|
set @errorNumber = error_number()
|
|
set @errorMessage = error_message()
|
|
rollback transaction tSensorsAnalog
|
|
end catch;
|
|
update @tagIdTable set Processed = 1 where Id = @tagId
|
|
End
|
|
|
|
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
|
|
,@RegionsOfInterest
|
|
,@ROIStart
|
|
,@ROIEnd
|
|
,@ViewDownloadAll
|
|
,@Export
|
|
,@ExportFormat
|
|
,@LabDetails
|
|
,@UseLabDetails
|
|
,@CustomerDetails
|
|
,@UseCustomerDetails
|
|
,@AllowMissingSensors
|
|
,@AllowSensorIdToBlankChannel
|
|
,@LocalOnly
|
|
,@LastModified
|
|
,@LastModifiedBy
|
|
,@TurnOffExcitation
|
|
,@TriggerCheckRealtime
|
|
,@TriggerCheckStep
|
|
,@PostTestDiagnostics
|
|
,@ExportFolder
|
|
,@DownloadFolder
|
|
,@CommonStatusLine
|
|
,@SameAsDownloadFolder
|
|
,@UploadData
|
|
,@UploadDataFolder
|
|
,@UploadExportsOnly
|
|
,@Settings
|
|
,@WarnOnBatteryFail
|
|
,@Dirty
|
|
,@Complete
|
|
,@Error
|
|
,@TestEngineerDetails
|
|
,@UseTestEngineerDetails
|
|
,@UserTags
|
|
,@DoAutoArm
|
|
,@CheckoutMode
|
|
,@ISFFile
|
|
,@QuitTestWithoutWarning
|
|
,@NotAllChannelsRealTime
|
|
,@NotAllChannelsViewer
|
|
,@SuppressMissingSensorsWarning
|
|
,null
|
|
,@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, @errorNumber output, @errorMessage output
|
|
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, @errorNumber output, @errorMessage output
|
|
end
|
|
|
|
select @CalculatedChannels = t.x.query('.') from @TestSetup.nodes('/TestSetup/CalculatedChannels') t(x)
|
|
if(@CalculatedChannels is not null)
|
|
begin
|
|
exec dbo.sp_DBImportTestSetupCalculatedChannels @TestSetupId, @CalculatedChannels, @errorNumber output, @errorMessage output
|
|
end
|
|
|
|
select @LevelTriggers = t.x.query('.') from @TestSetup.nodes('/TestSetup/LevelTriggers') t(x)
|
|
if(@LevelTriggers is not null)
|
|
begin
|
|
exec dbo.sp_DBImportTestSetupLevelTriggers @TestSetupId, @LevelTriggers, @errorNumber output, @errorMessage output
|
|
end
|
|
|
|
select @DASSettings = t.x.query('.') from @TestSetup.nodes('/TestSetup/DASSettings') t(x)
|
|
if(@DASSettings is not null)
|
|
begin
|
|
exec dbo.sp_DBImportTestSetupDASSettings @DASSettings, @errorNumber output, @errorMessage output
|
|
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
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|