DP44/DataPRO/Modules/Database/DatabaseMigrationScripts/.svn/pristine/10/10585ac800133c0ab7792ec93407f16238ced740.svn-base

<EFBFBD><EFBFBD>
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