监控节点 xml写入数据库 存储过程
USE [Appserver] GO /****** Object: StoredProcedure [AppServer].[ImportNodeRelationData] Script Date: 08/05/2013 19:15:56 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO
ALTER procedure [AppServer].[ImportNodeRelationData] @ClientId uniqueidentifier, @RelationDataXML ntext, @TimeCreated datetime, @WriteToLog bit, @ResultValue int output --执行结果0：执行成功，-1：操作客户端不存在，-2：插入存在错误 AS declare @m_Code varchar(50) declare @m_Name varchar(50) declare @m_CoreId uniqueidentifier declare @m_BoxCode varchar(50) declare @m_IPAddress varchar(50) declare @m_ProductModel varchar(50) declare @m_OprationType varchar(50) declare @m_OldNodeCode varchar(50) declare @m_IDNumber int declare @m_ZoneCode varchar(50) declare @m_AnchorPointCode varchar(50) declare @m_CANIP varchar(50) declare @m_AVCodecPort int declare @m_MainCameraCode varchar(50) declare @m_BesideCamera1Code varchar(50) declare @m_BesideCamera2Code varchar(50) declare @m_BesideCamera3Code varchar(50) declare @m_BesideCamera4Code varchar(50) declare @m_AudioCodecIP varchar(50) declare @m_IOControllerIP varchar(50) declare @m_IOControllerPort int declare @m_CoreIdNull uniqueidentifier declare @m_BoxId uniqueidentifier declare @m_CANId uniqueidentifier declare @m_ZoneId uniqueidentifier declare @m_AnchorPointId uniqueidentifier declare @m_GlobalId uniqueidentifier declare @m_AVCodecId uniqueidentifier declare @m_IOControllerId uniqueidentifier declare @m_SwitchId uniqueidentifier declare @m_MainCameraID uniqueidentifier declare @m_BesideCamera1ID uniqueidentifier declare @m_BesideCamera2ID uniqueidentifier declare @m_BesideCamera3ID uniqueidentifier declare @m_BesideCamera4ID uniqueidentifier declare @m_BelongTo varchar(50) declare @m_RowNum int declare @m_PtzCameraId varchar(50) declare @m_PtzPresetId varchar(50) declare @m_AimetisCameraCode int declare @m_AimetisPolicyId int declare @m_LeftCameraId varchar(50) declare @m_RightCameraId varchar(50) declare @m_LeftCameragid uniqueidentifier declare @m_RightCameragid uniqueidentifier declare @m_MainCameragid uniqueidentifier declare @m_PtzCameragid uniqueidentifier begin IF NOT EXISTS(SELECT 1 FROM AppServer.ClientInfo WHERE ClientId=@ClientId ) BEGIN SET @ResultValue=-1 return; END set @ResultValue=0; set @m_CoreIdNull='00000000-0000-0000-0000-000000000000'; --创建临时表 IF EXISTS (SELECT 1 FROM tempdb.dbo.sysobjects WHERE id = object_id('tempdb..#ImportNodeErrorDataTempTable') AND TYPE = 'U') DROP TABLE #ImportNodeErrorDataTempTable CREATE TABLE #ImportNodeErrorDataTempTable ( ErrorId uniqueidentifier, [Key] varchar(100), Row int, ErrorType varchar(100), BelongTo varchar(100)); DECLARE @hdoc int EXEC sp_xml_preparedocument @hdoc OUTPUT, @RelationDataXML
--IOControllers begin Set @m_BelongTo='IOControllers'; Set @m_RowNum=0; declare cur_resulttmp cursor for select Code,Name,CoreId,BoxCode,IPAddress,ProductModel,OprationType,OldNodeCode FROM OPENXML (@hdoc, '/NodeRelation/IOControllers/IOController',1) WITH (Code varchar(50),Name varchar(50),CoreId uniqueidentifier,BoxCode varchar(50),IPAddress varchar(50), ProductModel varchar(50),OprationType varchar(50),OldNodeCode varchar(50)); open cur_resulttmp fetch next from cur_resulttmp into @m_Code,@m_Name,@m_CoreId,@m_BoxCode,@m_IPAddress,@m_ProductModel,@m_OprationType,@m_OldNodeCode; while @@fetch_status=0 begin begin transaction IF @m_CoreId IS NULL BEGIN set @m_CoreId=@m_CoreIdNull; END IF @m_BoxCode IS NULL BEGIN set @m_BoxId=NULL; END ELSE BEGIN SELECT @m_BoxId=BoxId FROM AppServer.DeviceControlBox where Code=@m_BoxCode; IF @m_BoxId IS NULL BEGIN set @m_BoxId=newid(); Insert into AppServer.DeviceControlBox(BoxId,Code,Text) values(@m_BoxId,@m_BoxCode,'控制箱'+@m_BoxCode); END END IF @m_OprationType='ADD' BEGIN IF NOT EXISTS(SELECT * FROM AppServer.NodeIndex where Code=@m_Code and NodeType='IOController') BEGIN Insert into AppServer.NodeIndex(GlobalId,BoxId,CoreNodeId,Code,Name,NodeType,IPAddress,ProductModel) values(NEWID(),@m_BoxId,@m_CoreId,@m_Code,@m_Name,'IOController',@m_IPAddress,@m_ProductModel); END ELSE BEGIN Set @ResultValue=-2; Insert into #ImportNodeErrorDataTempTable(ErrorId,[Key],Row,ErrorType,BelongTo) values(newid(),@m_Code,@m_RowNum,'KeyExist',@m_BelongTo); END END --ELSE IF @m_OprationType='Modify' --BEGIN -- IF EXISTS(SELECT * FROM AppServer.NodeIndex where Code=@m_OldNodeCode and nodetype='IOController') -- BEGIN -- IF NOT EXISTS(SELECT * FROM AppServer.NodeIndex where Code=@m_Code and @m_OldNodeCode<>@m_Code and nodetype='IOController') -- BEGIN -- UpDate AppServer.NodeIndex Set BoxId=@m_BoxId,CoreNodeId=@m_CoreId,Code=@m_Code, -- Name=@m_Name,IPAddress=@m_IPAddress,ProductModel=@m_ProductModel where Code=@m_OldNodeCode and nodetype='IOController'; -- END -- ELSE -- BEGIN -- Set @ResultValue=-2; -- Insert into #ImportNodeErrorDataTempTable(ErrorId,[Key],Row,ErrorType,BelongTo) -- values(newid(),@m_Code,@m_RowNum,'KeyExist',@m_BelongTo); -- END -- END -- ELSE -- BEGIN -- Set @ResultValue=-2; -- Insert into #ImportNodeErrorDataTempTable(ErrorId,[Key],Row,ErrorType,BelongTo) -- values(newid(),@m_OldNodeCode,@m_RowNum,'RecordNotExist',@m_BelongTo); -- END --END commit transaction; SET @m_RowNum=@m_RowNum+1; --SET @m_Code=NULL; --SET @m_Name=NULL; --SET @m_CoreId=NULL; --SET @m_BoxCode=NULL; --SET @m_IPAddress=NULL; --SET @m_ProductModel=NULL; --SET @m_OprationType=NULL; --SET @m_OldNodeCode=NULL; SET @m_BoxId=NULL; fetch next from cur_resulttmp into @m_Code,@m_Name,@m_CoreId,@m_BoxCode,@m_IPAddress,@m_ProductModel,@m_OprationType,@m_OldNodeCode; end close cur_resulttmp deallocate cur_resulttmp; END --CANs begin Set @m_BelongTo='CANs'; Set @m_RowNum=0; declare cur_resulttmp cursor for select Code,Name,CoreId,BoxCode,IPAddress,IOControllerIP,IOControllerPort,OprationType,OldNodeCode FROM OPENXML (@hdoc, '/NodeRelation/CANs/CAN',1) WITH (Code varchar(50),Name varchar(50),CoreId uniqueidentifier,BoxCode varchar(50),IPAddress varchar(50),IOControllerIP varchar(50),IOControllerPort int,OprationType varchar(50),OldNodeCode varchar(50)); open cur_resulttmp fetch next from cur_resulttmp into @m_Code,@m_Name,@m_CoreId,@m_BoxCode,@m_IPAddress,@m_IOControllerIP,@m_IOControllerPort,@m_OprationType,@m_OldNodeCode; while @@fetch_status=0 begin begin transaction IF @m_CoreId IS NULL BEGIN set @m_CoreId=@m_CoreIdNull; END IF @m_BoxCode IS NULL BEGIN set @m_BoxId=NULL; END ELSE BEGIN SELECT @m_BoxId=BoxId FROM AppServer.DeviceControlBox where Code=@m_BoxCode; IF @m_BoxId IS NULL BEGIN set @m_BoxId=newid(); Insert into AppServer.DeviceControlBox(BoxId,Code,Text) values(@m_BoxId,@m_BoxCode,'控制箱'+@m_BoxCode); END END IF @m_IOControllerIP IS NULL BEGIN set @m_IOControllerId=NULL; END ELSE BEGIN SELECT @m_IOControllerId=GlobalId FROM AppServer.NodeIndex where IPAddress=@m_IOControllerIP and NodeType='IOController'; IF @m_IOControllerId IS NULL BEGIN set @m_IOControllerId=newid(); Insert into AppServer.NodeIndex(GlobalId,CoreNodeId,Code,Name,NodeType,IPAddress) values(@m_IOControllerId,@m_CoreIdNull,@m_Code+'-IOController',@m_Code+'-IOController','IOController',@m_IOControllerIP); END END IF (@m_IOControllerPort IS NULL) or (@m_IOControllerId IS NULL) BEGIN set @m_SwitchId=NULL; END ELSE BEGIN Select @m_SwitchId=GlobalId from AppServer.NodeIndex a,AppServer.NodeDataLinkRelation b where a.globalid=b.childnodeid and b.parentnodeid=@m_IOControllerId and a.idnumber=@m_IOControllerPort and nodetype='Switch'; IF (@m_SwitchId IS NULL) BEGIN set @m_SwitchId=NEWID(); Insert into AppServer.NodeIndex(GlobalId,CoreNodeId,Code,Name,NodeType,IDNumber) values(@m_SwitchId,@m_CoreIdNull,@m_IOControllerIP+'-'+Convert(varchar(50),@m_IOControllerPort),@m_IOControllerIP+'-'+Convert(varchar(50),@m_IOControllerPort),'Switch',@m_IOControllerPort); Insert into AppServer.NodeConnectRelation(ConnectionId,ParentNodeId,ChildNodeId) values(NEWID(),@m_IOControllerId,@m_SwitchId); Insert into AppServer.NodeDataLinkRelation(DataLinkId,ParentNodeId,ChildNodeId) values(NEWID(),@m_IOControllerId,@m_SwitchId); END END IF (@m_OprationType='ADD') BEGIN IF NOT EXISTS(SELECT * FROM AppServer.NodeIndex where Code=@m_Code and NodeType='CAN') BEGIN set @m_GlobalId=NEWID(); Insert into AppServer.NodeIndex(GlobalId,BoxId,CoreNodeId,Code,Name,NodeType,IPAddress,IDNumber) values(@m_GlobalId,@m_BoxId,@m_CoreId,@m_Code,@m_Name,'CAN',@m_IPAddress,@m_IOControllerPort); IF (@m_IOControllerId IS NOT NULL) BEGIN --添加数据连接关系 IF NOT EXISTS(SELECT * FROM AppServer.NodeConnectRelation where ParentNodeId=@m_IOControllerId and ChildNodeId=@m_GlobalId) BEGIN Insert into AppServer.NodeConnectRelation(ConnectionId,ParentNodeId,ChildNodeId) values(NEWID(),@m_IOControllerId,@m_GlobalId); END --添加物理连接关系 IF NOT EXISTS(SELECT * FROM AppServer.NodeDataLinkRelation where ParentNodeId=@m_IOControllerId and ChildNodeId=@m_GlobalId) BEGIN Insert into AppServer.NodeDataLinkRelation(DataLinkId,ParentNodeId,ChildNodeId) values(NEWID(),@m_IOControllerId,@m_GlobalId); END END --添加电源关系 IF (@m_SwitchId IS NOT NULL) BEGIN IF NOT EXISTS(SELECT * FROM AppServer.NodePowerSupplyRelation where PowerNodeId=@m_SwitchId and ElectricityNodeId=@m_GlobalId) BEGIN Insert into AppServer.NodePowerSupplyRelation(PowerSupplyId,PowerNodeId,ElectricityNodeId) values(NEWID(),@m_SwitchId,@m_GlobalId); END END END ELSE BEGIN Set @ResultValue=-2; Insert into #ImportNodeErrorDataTempTable(ErrorId,[Key],Row,ErrorType,BelongTo) values(newid(),@m_Code,@m_RowNum,'KeyExist',@m_BelongTo); END END --ELSE IF (@m_OprationType='Modify') --BEGIN --END commit transaction; SET @m_RowNum=@m_RowNum+1; --SET @m_Code=NULL; --SET @m_Name=NULL; --SET @m_CoreId=NULL; --SET @m_BoxCode=NULL; --SET @m_IPAddress=NULL; --SET @m_IOControllerIP=NULL; --SET @m_IOControllerPort=NULL; --SET @m_OprationType=NULL; --SET @m_OldNodeCode=NULL; SET @m_BoxId=NULL; SET @m_IOControllerId=NULL; SET @m_SwitchId=NULL; set @m_GlobalId=NULL; fetch next from cur_resulttmp into @m_Code,@m_Name,@m_CoreId,@m_BoxCode,@m_IPAddress,@m_IOControllerIP,@m_IOControllerPort,@m_OprationType,@m_OldNodeCode; end close cur_resulttmp deallocate cur_resulttmp; END --AudioVideoCodecs begin Set @m_BelongTo='AudioVideoCodecs'; Set @m_RowNum=0; declare cur_resulttmp cursor for select Code,Name,CoreId,BoxCode,IPAddress,ProductModel,OprationType,OldNodeCode FROM OPENXML (@hdoc, '/NodeRelation/AudioVideoCodecs/AudioVideoCodec',1) WITH (Code varchar(50),Name varchar(50),CoreId uniqueidentifier,BoxCode varchar(50),IPAddress varchar(50),ProductModel varchar(50),OprationType varchar(50),OldNodeCode varchar(50)); open cur_resulttmp fetch next from cur_resulttmp into @m_Code,@m_Name,@m_CoreId,@m_BoxCode,@m_IPAddress,@m_ProductModel,@m_OprationType,@m_OldNodeCode; while @@fetch_status=0 begin begin transaction IF @m_CoreId IS NULL BEGIN set @m_CoreId=@m_CoreIdNull; END IF @m_BoxCode IS NULL BEGIN set @m_BoxId=NULL; END ELSE BEGIN SELECT @m_BoxId=BoxId FROM AppServer.DeviceControlBox where Code=@m_BoxCode; IF @m_BoxId IS NULL BEGIN set @m_BoxId=newid(); Insert into AppServer.DeviceControlBox(BoxId,Code,Text) values(@m_BoxId,@m_BoxCode,'控制箱'+@m_BoxCode); END END IF (@m_OprationType='ADD') BEGIN IF NOT EXISTS(SELECT * FROM AppServer.NodeIndex where Code=@m_Code and NodeType='AudioVideoCodec') BEGIN set @m_GlobalId=NEWID(); IF @m_CoreId IS NULL BEGIN set @m_CoreId=@m_CoreIdNull; END Insert into AppServer.NodeIndex(GlobalId,BoxId,CoreNodeId,Code,Name,NodeType,IPAddress,ProductModel) values(@m_GlobalId,@m_BoxId,@m_CoreId,@m_Code,@m_Name,'AudioVideoCodec',@m_IPAddress,@m_ProductModel); END ELSE BEGIN Set @ResultValue=-2; Insert into #ImportNodeErrorDataTempTable(ErrorId,[Key],Row,ErrorType,BelongTo) values(newid(),@m_Code,@m_RowNum,'KeyExist',@m_BelongTo); END END --ELSE IF (@m_OprationType='Modify') --BEGIN --END commit transaction; SET @m_RowNum=@m_RowNum+1; SET @m_BoxId=NULL; set @m_GlobalId=NULL; fetch next from cur_resulttmp into @m_Code,@m_Name,@m_CoreId,@m_BoxCode,@m_IPAddress,@m_ProductModel,@m_OprationType,@m_OldNodeCode; end close cur_resulttmp deallocate cur_resulttmp; END --SensorControllers begin Set @m_BelongTo='SensorControllers'; Set @m_RowNum=0; declare cur_resulttmp cursor for select Code,Name,CoreId,BoxCode,IDNumber,ZoneCode,AnchorPointCode,CANIP,ProductModel,PtzCameraId,PtzPresetId,OprationType,OldNodeCode FROM OPENXML (@hdoc, '/NodeRelation/SensorControllers/SensorController',1) WITH (Code varchar(50),Name varchar(50),CoreId uniqueidentifier,BoxCode varchar(50),IDNumber int,ZoneCode varchar(50),AnchorPointCode varchar(50),CANIP varchar(50),ProductModel varchar(50),PtzCameraId varchar(50),PtzPresetId varchar(50),OprationType varchar(50),OldNodeCode varchar(50)); open cur_resulttmp fetch next from cur_resulttmp into @m_Code,@m_Name,@m_CoreId,@m_BoxCode,@m_IDNumber,@m_ZoneCode,@m_AnchorPointCode,@m_CANIP,@m_ProductModel,@m_PtzCameraId,@m_PtzPresetId,@m_OprationType,@m_OldNodeCode; while @@fetch_status=0 begin begin transaction IF @m_CoreId IS NULL BEGIN set @m_CoreId=@m_CoreIdNull; END IF @m_BoxCode IS NULL BEGIN set @m_BoxId=NULL; END ELSE BEGIN SELECT @m_BoxId=BoxId FROM AppServer.DeviceControlBox where Code=@m_BoxCode; IF @m_BoxId IS NULL BEGIN set @m_BoxId=newid(); Insert into AppServer.DeviceControlBox(BoxId,Code,Text) values(@m_BoxId,@m_BoxCode,'控制箱'+@m_BoxCode); END END IF @m_CANIP IS NULL BEGIN set @m_CANId=NULL; END ELSE BEGIN SELECT @m_CANId=GlobalId FROM AppServer.NodeIndex where IPAddress=@m_CANIP and NodeType='CAN'; IF @m_CANId IS NULL BEGIN Set @ResultValue=-2; Insert into #ImportNodeErrorDataTempTable(ErrorId,[Key],Row,ErrorType,BelongTo) values(newid(),@m_CANIP,@m_RowNum,'ForeignkeyNotExist',@m_BelongTo); END END IF @m_ZoneCode IS NULL BEGIN set @m_ZoneId=NULL; END ELSE BEGIN SELECT @m_ZoneId=ZoneId FROM AppServer.DeviceDefenceZone where Code=@m_ZoneCode; IF @m_ZoneId IS NULL BEGIN Set @ResultValue=-2; Insert into #ImportNodeErrorDataTempTable(ErrorId,[Key],Row,ErrorType,BelongTo) values(newid(),@m_ZoneCode,@m_RowNum,'ForeignkeyNotExist',@m_BelongTo); END END IF @m_AnchorPointCode IS NULL BEGIN set @m_AnchorPointId=NULL; END ELSE BEGIN SELECT @m_AnchorPointId=AnchorPointId FROM AppServer.AnchorPoint where Code=@m_AnchorPointCode; IF @m_AnchorPointId IS NULL BEGIN set @m_AnchorPointId=newid(); Insert into AppServer.AnchorPoint(AnchorPointId,Code,Text) values(@m_AnchorPointId,@m_AnchorPointCode,@m_AnchorPointCode); END END IF (@m_CANId IS NOT NULL) AND (@m_ZoneId IS NOT NULL) BEGIN IF (@m_OprationType='ADD') BEGIN set @m_PtzCameragid=NULL; SELECT @m_PtzCameragid=GlobalId FROM AppServer.NodeIndex where Code=@m_PtzCameraId and NodeType='Camera' IF NOT EXISTS(SELECT * FROM AppServer.NodeIndex where Code=@m_Code and NodeType='SensorController') BEGIN IF(@m_PtzCameraId IS NULL OR @m_PtzCameragid IS NOT NULL) BEGIN IF((@m_PtzCameraId IS NULL and @m_PtzPresetId IS NULL)OR(@m_PtzCameraId IS NOT NULL and @m_PtzPresetId IS NOT NULL)) BEGIN set @m_GlobalId=NEWID(); IF @m_CoreId IS NULL BEGIN set @m_CoreId=@m_CoreIdNull; END Insert into AppServer.NodeIndex(GlobalId,BoxId,CoreNodeId,Code,Name,NodeType,IDNumber,ProductModel) values(@m_GlobalId,@m_BoxId,@m_CoreId,@m_Code,@m_Name,'SensorController',@m_IDNumber,@m_ProductModel); --添加防区关系 IF NOT EXISTS(SELECT * FROM AppServer.NodeInDDZ where ZoneId=@m_ZoneId and NodeId=@m_GlobalId) BEGIN Insert into AppServer.NodeInDDZ(NDLId,ZoneId,NodeId,IsMain) values(NEWID(),@m_ZoneId,@m_GlobalId,0); END --添加数据连接关系 IF NOT EXISTS(SELECT * FROM AppServer.NodeConnectRelation where ParentNodeId=@m_CANId and ChildNodeId=@m_GlobalId) BEGIN Insert into AppServer.NodeConnectRelation(ConnectionId,ParentNodeId,ChildNodeId) values(NEWID(),@m_CANId,@m_GlobalId); END --添加物理连接关系 IF NOT EXISTS(SELECT * FROM AppServer.NodeDataLinkRelation where ParentNodeId=@m_CANId and ChildNodeId=@m_GlobalId) BEGIN Insert into AppServer.NodeDataLinkRelation(DataLinkId,ParentNodeId,ChildNodeId) values(NEWID(),@m_CANId,@m_GlobalId); END --添加柱子关系 IF (@m_AnchorPointId IS NOT NULL) BEGIN IF NOT EXISTS(SELECT * FROM AppServer.NodeLocationRelation where AnchorPointId=@m_AnchorPointId and NodeGlobalId=@m_GlobalId) BEGIN Insert into AppServer.NodeLocationRelation(LocationRefId,AnchorPointId,NodeGlobalId) values(NEWID(),@m_AnchorPointId,@m_GlobalId); END END --添加信息扩展 set @m_MainCameragid=NULL; SELECT @m_MainCameragid=GlobalId FROM AppServer.NodeIndex where Code=@m_Code and NodeType='SensorController' IF (@m_MainCameragid IS NOT NULL AND(@m_PtzCameraId IS NOT NULL and @m_PtzPresetId IS NOT NULL)) BEGIN Insert into AppServer.ExtNodeInfo(NodeId,ExtDeviceId,ExtVarChar3) values(@m_MainCameragid,@m_PtzPresetId,@m_PtzCameragid) END END ELSE BEGIN IF(@m_PtzCameraId IS NULL) BEGIN Set @ResultValue=-2; Insert into #ImportNodeErrorDataTempTable(ErrorId,[Key],Row,ErrorType,BelongTo) values(newid(),@m_PtzCameraId,@m_RowNum,'CanNotyBeEmept',@m_BelongTo); END ELSE BEGIN Set @ResultValue=-2; Insert into #ImportNodeErrorDataTempTable(ErrorId,[Key],Row,ErrorType,BelongTo) values(newid(),@m_PtzPresetId,@m_RowNum,'CanNotyBeEmept',@m_BelongTo); END END END ELSE BEGIN Set @ResultValue=-2; Insert into #ImportNodeErrorDataTempTable(ErrorId,[Key],Row,ErrorType,BelongTo) values(newid(),@m_PtzCameraId,@m_RowNum,'ForeignkeyNotExist',@m_BelongTo); END END ELSE BEGIN Set @ResultValue=-2; Insert into #ImportNodeErrorDataTempTable(ErrorId,[Key],Row,ErrorType,BelongTo) values(newid(),@m_Code,@m_RowNum,'KeyExist',@m_BelongTo); END END --ELSE IF (@m_OprationType='Modify') --BEGIN --END END commit transaction; SET @m_RowNum=@m_RowNum+1; SET @m_BoxId=NULL; SET @m_CANId=NULL; SET @m_ZoneId=NULL; SET @m_AnchorPointId=NULL; set @m_GlobalId=NULL; fetch next from cur_resulttmp into @m_Code,@m_Name,@m_CoreId,@m_BoxCode,@m_IDNumber,@m_ZoneCode,@m_AnchorPointCode,@m_CANIP,@m_ProductModel,@m_PtzCameraId,@m_PtzPresetId,@m_OprationType,@m_OldNodeCode; end close cur_resulttmp deallocate cur_resulttmp; END --Cameras begin Set @m_BelongTo='Cameras'; Set @m_RowNum=0; declare cur_resulttmp cursor for select Code,Name,CoreId,BoxCode,IPAddress,AVCodecPort,AnchorPointCode,PtzCameraId,PtzPresetId,AimetisCameraCode,AimetisPolicyId,LeftCameraId,RightCameraId,OprationType,OldNodeCode FROM OPENXML (@hdoc, '/NodeRelation/Cameras/Camera',1) WITH (Code varchar(50),Name varchar(50),CoreId uniqueidentifier,BoxCode varchar(50),IPAddress varchar(50),AVCodecPort int,AnchorPointCode varchar(50),PtzCameraId varchar(50),PtzPresetId varchar(50),AimetisCameraCode int,AimetisPolicyId int,LeftCameraId varchar(50),RightCameraId varchar(50),OprationType varchar(50),OldNodeCode varchar(50)); open cur_resulttmp fetch next from cur_resulttmp into @m_Code,@m_Name,@m_CoreId,@m_BoxCode,@m_IPAddress,@m_AVCodecPort,@m_AnchorPointCode,@m_PtzCameraId,@m_PtzPresetId,@m_AimetisCameraCode,@m_AimetisPolicyId,@m_LeftCameraId,@m_RightCameraId,@m_OprationType,@m_OldNodeCode; while @@fetch_status=0 begin begin transaction IF @m_CoreId IS NULL BEGIN set @m_CoreId=@m_CoreIdNull; END IF @m_AnchorPointCode IS NULL BEGIN set @m_AnchorPointId=NULL; END ELSE BEGIN SELECT @m_AnchorPointId=AnchorPointId FROM AppServer.AnchorPoint where Code=@m_AnchorPointCode; IF @m_AnchorPointId IS NULL BEGIN set @m_AnchorPointId=newid(); Insert into AppServer.AnchorPoint(AnchorPointId,Code,Text) values(@m_AnchorPointId,@m_AnchorPointCode,@m_AnchorPointCode); END END IF @m_IPAddress IS NULL BEGIN set @m_AVCodecId=NULL; END ELSE BEGIN SELECT @m_AVCodecId=GlobalId FROM AppServer.NodeIndex where IPAddress=@m_IPAddress and NodeType='AudioVideoCodec'; IF @m_AVCodecId IS NULL BEGIN set @m_AVCodecId=newid(); Insert into AppServer.NodeIndex(GlobalId,CoreNodeId,Code,Name,NodeType,IPAddress) values(@m_AVCodecId,@m_CoreIdNull,@m_Code+'-AVCodec',@m_Code+'-AVCodec','AudioVideoCodec',@m_IPAddress); END END IF (@m_OprationType='ADD') BEGIN set @m_MainCameragid=NULL; set @m_PtzCameragid=NULL; set @m_RightCameragid=NULL; set @m_LeftCameragid=NULL; SELECT @m_MainCameragid=GlobalId FROM AppServer.NodeIndex where Code=@m_Code and NodeType='Camera' SELECT @m_LeftCameragid=GlobalId FROM AppServer.NodeIndex where Code=@m_LeftCameraId and NodeType='Camera' SELECT @m_RightCameragid=GlobalId FROM AppServer.NodeIndex where Code=@m_RightCameraId and NodeType='Camera' SELECT @m_PtzCameragid=GlobalId FROM AppServer.NodeIndex where Code=@m_PtzCameraId and NodeType='Camera' IF (@m_MainCameragid IS NULL) BEGIN IF (@m_LeftCameraId IS NULL OR @m_LeftCameragid IS NOT NULL) BEGIN IF(@m_RightCameraId IS NULL OR @m_RightCameragid IS NOT NULL) BEGIN IF(@m_PtzCameraId IS NULL OR @m_PtzCameragid IS NOT NULL) BEGIN IF((@m_PtzCameraId IS NULL and @m_PtzPresetId IS NULL)OR(@m_PtzCameraId IS NOT NULL and @m_PtzPresetId IS NOT NULL)) BEGIN IF((@m_AimetisCameraCode IS NULL and @m_AimetisPolicyId IS NULL)OR(@m_AimetisCameraCode IS NOT NULL and @m_AimetisPolicyId IS NOT NULL)) BEGIN IF @m_AVCodecPort IS NULL BEGIN set @m_AVCodecPort=0; END set @m_GlobalId=newid(); Insert into AppServer.NodeIndex(GlobalId,CoreNodeId,Code,Name,NodeType,IDNumber) values(@m_GlobalId,@m_CoreId,@m_Code,@m_Name,'Camera',@m_AVCodecPort); IF (@m_AVCodecId IS NOT NULL) BEGIN --添加数据连接关系 IF NOT EXISTS(SELECT * FROM AppServer.NodeConnectRelation where ParentNodeId=@m_AVCodecId and ChildNodeId=@m_GlobalId) BEGIN Insert into AppServer.NodeConnectRelation(ConnectionId,ParentNodeId,ChildNodeId) values(NEWID(),@m_AVCodecId,@m_GlobalId); END --添加物理连接关系 IF NOT EXISTS(SELECT * FROM AppServer.NodeDataLinkRelation where ParentNodeId=@m_AVCodecId and ChildNodeId=@m_GlobalId) BEGIN Insert into AppServer.NodeDataLinkRelation(DataLinkId,ParentNodeId,ChildNodeId) values(NEWID(),@m_AVCodecId,@m_GlobalId); END --添加柱子关系 IF (@m_AnchorPointId IS NOT NULL) BEGIN IF NOT EXISTS(SELECT * FROM AppServer.NodeLocationRelation where AnchorPointId=@m_AnchorPointId and NodeGlobalId=@m_GlobalId) BEGIN Insert into AppServer.NodeLocationRelation(LocationRefId,AnchorPointId,NodeGlobalId) values(NEWID(),@m_AnchorPointId,@m_GlobalId); END END --添加信息扩展 IF (@m_MainCameragid IS NOT NULL AND(@m_LeftCameraId IS NOT NULL OR @m_RightCameraId IS NULL OR(@m_PtzCameraId IS NOT NULL and @m_PtzPresetId IS NOT NULL)OR(@m_AimetisCameraCode IS NOT NULL and @m_AimetisPolicyId IS NOT NULL))) BEGIN Insert into AppServer.ExtNodeInfo(NodeId,ExtVarChar1,ExtVarChar2,ExtDeviceId,ExtVarChar3,ExtInt1,ExtInt2) values(@m_MainCameragid,@m_LeftCameragid,@m_RightCameragid,@m_PtzPresetId,@m_PtzCameragid,@m_AimetisPolicyId,@m_AimetisCameraCode) END END END ELSE BEGIN IF(@m_AimetisCameraCode IS NULL) BEGIN Set @ResultValue=-2; Insert into #ImportNodeErrorDataTempTable(ErrorId,[Key],Row,ErrorType,BelongTo) values(newid(),@m_AimetisCameraCode,@m_RowNum,'CanNotyBeEmept',@m_BelongTo); END ELSE BEGIN Set @ResultValue=-2; Insert into #ImportNodeErrorDataTempTable(ErrorId,[Key],Row,ErrorType,BelongTo) values(newid(),@m_AimetisPolicyId,@m_RowNum,'CanNotyBeEmept',@m_BelongTo); END END END ELSE BEGIN IF(@m_PtzCameraId IS NULL) BEGIN Set @ResultValue=-2; Insert into #ImportNodeErrorDataTempTable(ErrorId,[Key],Row,ErrorType,BelongTo) values(newid(),@m_PtzCameraId,@m_RowNum,'CanNotyBeEmept',@m_BelongTo); END ELSE BEGIN Set @ResultValue=-2; Insert into #ImportNodeErrorDataTempTable(ErrorId,[Key],Row,ErrorType,BelongTo) values(newid(),@m_PtzPresetId,@m_RowNum,'CanNotyBeEmept',@m_BelongTo); END END END ELSE BEGIN Set @ResultValue=-2; Insert into #ImportNodeErrorDataTempTable(ErrorId,[Key],Row,ErrorType,BelongTo) values(newid(),@m_PtzCameraId,@m_RowNum,'ForeignkeyNotExist',@m_BelongTo); END END ELSE BEGIN Set @ResultValue=-2; Insert into #ImportNodeErrorDataTempTable(ErrorId,[Key],Row,ErrorType,BelongTo) values(newid(),@m_RightCameraId,@m_RowNum,'ForeignkeyNotExist',@m_BelongTo); END END ELSE BEGIN Set @ResultValue=-2; Insert into #ImportNodeErrorDataTempTable(ErrorId,[Key],Row,ErrorType,BelongTo) values(newid(),@m_LeftCameraId,@m_RowNum,'ForeignkeyNotExist',@m_BelongTo); END END ELSE BEGIN Set @ResultValue=-2; Insert into #ImportNodeErrorDataTempTable(ErrorId,[Key],Row,ErrorType,BelongTo) values(newid(),@m_Code,@m_RowNum,'KeyExist',@m_BelongTo); END END --ELSE IF (@m_OprationType='Modify') --BEGIN --END commit transaction; SET @m_RowNum=@m_RowNum+1; SET @m_AnchorPointId=NULL; SET @m_AVCodecId=NULL; set @m_GlobalId=NULL; fetch next from cur_resulttmp into @m_Code,@m_Name,@m_CoreId,@m_BoxCode,@m_IPAddress,@m_AVCodecPort,@m_AnchorPointCode,@m_PtzCameraId,@m_PtzPresetId,@m_AimetisCameraCode,@m_AimetisPolicyId,@m_LeftCameraId,@m_RightCameraId,@m_OprationType,@m_OldNodeCode; end close cur_resulttmp deallocate cur_resulttmp; END --DefenceCameras begin Set @m_BelongTo='DefenceCameras'; Set @m_RowNum=0; declare cur_resulttmp cursor for select ZoneCode,MainCameraCode,BesideCamera1Code,BesideCamera2Code,BesideCamera3Code,BesideCamera4Code FROM OPENXML (@hdoc, '/NodeRelation/DefenceCameras/DefenceCamera',1) WITH (ZoneCode varchar(50),MainCameraCode varchar(50),BesideCamera1Code varchar(50),BesideCamera2Code varchar(50),BesideCamera3Code varchar(50),BesideCamera4Code varchar(50)); open cur_resulttmp fetch next from cur_resulttmp into @m_ZoneCode, @m_MainCameraCode, @m_BesideCamera1Code, @m_BesideCamera2Code, @m_BesideCamera3Code, @m_BesideCamera4Code; while @@fetch_status=0 begin begin transaction IF @m_ZoneCode IS NULL BEGIN set @m_ZoneId=NULL; END ELSE BEGIN SELECT @m_ZoneId=ZoneId FROM AppServer.DeviceDefenceZone where Code=@m_ZoneCode; END IF @m_MainCameraCode IS NULL BEGIN set @m_MainCameraID=NULL; END ELSE BEGIN SELECT @m_MainCameraID=GlobalId FROM AppServer.NodeIndex where Code=@m_MainCameraCode and NodeType='Camera'; IF @m_MainCameraID IS NULL BEGIN Set @ResultValue=-2; Insert into #ImportNodeErrorDataTempTable(ErrorId,[Key],Row,ErrorType,BelongTo) values(newid(),@m_MainCameraCode,@m_RowNum,'ForeignkeyNotExist',@m_BelongTo); END END IF @m_BesideCamera1Code IS NULL BEGIN set @m_BesideCamera1ID=NULL; END ELSE BEGIN SELECT @m_BesideCamera1ID=GlobalId FROM AppServer.NodeIndex where Code=@m_BesideCamera1Code and NodeType='Camera'; IF @m_BesideCamera1ID IS NULL BEGIN Set @ResultValue=-2; Insert into #ImportNodeErrorDataTempTable(ErrorId,[Key],Row,ErrorType,BelongTo) values(newid(),@m_BesideCamera1Code,@m_RowNum,'ForeignkeyNotExist',@m_BelongTo); END END IF @m_BesideCamera2Code IS NULL BEGIN set @m_BesideCamera2ID=NULL; END ELSE BEGIN SELECT @m_BesideCamera2ID=GlobalId FROM AppServer.NodeIndex where Code=@m_BesideCamera2Code and NodeType='Camera'; IF @m_BesideCamera2ID IS NULL BEGIN Set @ResultValue=-2; Insert into #ImportNodeErrorDataTempTable(ErrorId,[Key],Row,ErrorType,BelongTo) values(newid(),@m_BesideCamera2Code,@m_RowNum,'ForeignkeyNotExist',@m_BelongTo); END END IF @m_BesideCamera3Code IS NULL BEGIN set @m_BesideCamera3ID=NULL; END ELSE BEGIN SELECT @m_BesideCamera3ID=GlobalId FROM AppServer.NodeIndex where Code=@m_BesideCamera3Code and NodeType='Camera'; IF @m_BesideCamera3ID IS NULL BEGIN Set @ResultValue=-2; Insert into #ImportNodeErrorDataTempTable(ErrorId,[Key],Row,ErrorType,BelongTo) values(newid(),@m_BesideCamera3Code,@m_RowNum,'ForeignkeyNotExist',@m_BelongTo); END END IF @m_BesideCamera4Code IS NULL BEGIN set @m_BesideCamera4ID=NULL; END ELSE BEGIN SELECT @m_BesideCamera4ID=GlobalId FROM AppServer.NodeIndex where Code=@m_BesideCamera4Code and NodeType='Camera'; IF @m_BesideCamera4ID IS NULL BEGIN Set @ResultValue=-2; Insert into #ImportNodeErrorDataTempTable(ErrorId,[Key],Row,ErrorType,BelongTo) values(newid(),@m_BesideCamera4Code,@m_RowNum,'ForeignkeyNotExist',@m_BelongTo); END END IF (@m_ZoneId IS NOT NULL) BEGIN Delete From AppServer.NodeInDDZ where NDLId in(select NDLId from AppServer.NodeInDDZ a,AppServer.NodeIndex b where a.nodeid=b.globalid and b.nodetype='Camera') and ZoneId=@m_ZoneId; IF (@m_MainCameraID IS NOT NULL) BEGIN Insert into AppServer.NodeInDDZ(NDLId,ZoneId,NodeId,IsMain) values(NEWID(),@m_ZoneId,@m_MainCameraID,1); END IF (@m_BesideCamera1ID IS NOT NULL) BEGIN Insert into AppServer.NodeInDDZ(NDLId,ZoneId,NodeId,IsMain) values(NEWID(),@m_ZoneId,@m_BesideCamera1ID,0); END IF (@m_BesideCamera2ID IS NOT NULL) BEGIN Insert into AppServer.NodeInDDZ(NDLId,ZoneId,NodeId,IsMain) values(NEWID(),@m_ZoneId,@m_BesideCamera2ID,0); END IF (@m_BesideCamera3ID IS NOT NULL) BEGIN Insert into AppServer.NodeInDDZ(NDLId,ZoneId,NodeId,IsMain) values(NEWID(),@m_ZoneId,@m_BesideCamera3ID,0); END IF (@m_BesideCamera4ID IS NOT NULL) BEGIN Insert into AppServer.NodeInDDZ(NDLId,ZoneId,NodeId,IsMain) values(NEWID(),@m_ZoneId,@m_BesideCamera4ID,0); END END ELSE BEGIN Set @ResultValue=-2; Insert into #ImportNodeErrorDataTempTable(ErrorId,[Key],Row,ErrorType,BelongTo) values(newid(),@m_ZoneCode,@m_RowNum,'ForeignkeyNotExist',@m_BelongTo); END commit transaction; SET @m_RowNum=@m_RowNum+1; SET @m_ZoneId=NULL; SET @m_MainCameraID=NULL; SET @m_BesideCamera1ID=NULL; SET @m_BesideCamera2ID=NULL; SET @m_BesideCamera3ID=NULL; SET @m_BesideCamera4ID=NULL; fetch next from cur_resulttmp into @m_ZoneCode, @m_MainCameraCode, @m_BesideCamera1Code, @m_BesideCamera2Code, @m_BesideCamera3Code, @m_BesideCamera4Code; end close cur_resulttmp deallocate cur_resulttmp; END --IntelligentVideos begin Set @m_BelongTo='IntelligentVideos'; Set @m_RowNum=0; declare cur_resulttmp cursor for select Code,Name,CoreId,BoxCode,IPAddress,AVCodecPort,ZoneCode,OprationType,OldNodeCode FROM OPENXML (@hdoc, '/NodeRelation/IntelligentVideos/IntelligentVideo',1) WITH (Code varchar(50),Name varchar(50),CoreId uniqueidentifier,BoxCode varchar(50),IPAddress varchar(50),AVCodecPort int,ZoneCode varchar(50),OprationType varchar(50),OldNodeCode varchar(50)); open cur_resulttmp fetch next from cur_resulttmp into @m_Code,@m_Name,@m_CoreId,@m_BoxCode,@m_IPAddress,@m_AVCodecPort,@m_ZoneCode,@m_OprationType,@m_OldNodeCode; while @@fetch_status=0 begin begin transaction IF @m_CoreId IS NULL BEGIN set @m_CoreId=@m_CoreIdNull; END IF @m_ZoneCode IS NULL BEGIN set @m_ZoneId=NULL; END ELSE BEGIN SELECT @m_ZoneId=ZoneId FROM AppServer.DeviceDefenceZone where Code=@m_ZoneCode; IF @m_ZoneId IS NULL BEGIN Set @ResultValue=-2; Insert into #ImportNodeErrorDataTempTable(ErrorId,[Key],Row,ErrorType,BelongTo) values(newid(),@m_ZoneCode,@m_RowNum,'ForeignkeyNotExist',@m_BelongTo); END END IF @m_IPAddress IS NULL BEGIN set @m_AVCodecId=NULL; END ELSE BEGIN SELECT @m_AVCodecId=GlobalId FROM AppServer.NodeIndex where IPAddress=@m_IPAddress and NodeType='AudioVideoCodec'; IF @m_AVCodecId IS NULL BEGIN set @m_AVCodecId=newid(); Insert into AppServer.NodeIndex(GlobalId,CoreNodeId,Code,Name,NodeType,IPAddress) values(@m_AVCodecId,@m_CoreIdNull,@m_Code+'-AudioVideoCodec',@m_Code+'-AudioVideoCodec','AudioVideoCodec',@m_IPAddress); END END
IF (@m_OprationType='ADD') BEGIN IF NOT EXISTS(SELECT * FROM AppServer.NodeIndex where Code=@m_Code and NodeType='IntelligentVideo') BEGIN IF @m_AVCodecPort IS NULL BEGIN set @m_AVCodecPort=0; END set @m_GlobalId=NEWID(); Insert into AppServer.NodeIndex(GlobalId,CoreNodeId,Code,Name,NodeType,IDNumber) values(@m_GlobalId,@m_CoreId,@m_Code,@m_Name,'IntelligentVideo',@m_AVCodecPort); IF (@m_ZoneId IS NOT NULL) BEGIN --添加防区关系 IF NOT EXISTS(SELECT * FROM AppServer.NodeInDDZ where ZoneId=@m_ZoneId and NodeId=@m_GlobalId) BEGIN Insert into AppServer.NodeInDDZ(NDLId,ZoneId,NodeId,IsMain) values(NEWID(),@m_ZoneId,@m_GlobalId,0); END END IF (@m_AVCodecId IS NOT NULL) BEGIN --添加数据连接关系 IF NOT EXISTS(SELECT * FROM AppServer.NodeConnectRelation where ParentNodeId=@m_AVCodecId and ChildNodeId=@m_GlobalId) BEGIN Insert into AppServer.NodeConnectRelation(ConnectionId,ParentNodeId,ChildNodeId) values(NEWID(),@m_AVCodecId,@m_GlobalId); END --添加物理连接关系 IF NOT EXISTS(SELECT * FROM AppServer.NodeDataLinkRelation where ParentNodeId=@m_AVCodecId and ChildNodeId=@m_GlobalId) BEGIN Insert into AppServer.NodeDataLinkRelation(DataLinkId,ParentNodeId,ChildNodeId) values(NEWID(),@m_AVCodecId,@m_GlobalId); END END END ELSE BEGIN Set @ResultValue=-2; Insert into #ImportNodeErrorDataTempTable(ErrorId,[Key],Row,ErrorType,BelongTo) values(newid(),@m_Code,@m_RowNum,'KeyExist',@m_BelongTo); END END --ELSE IF (@m_OprationType='Modify') --BEGIN --END
commit transaction; SET @m_RowNum=@m_RowNum+1; SET @m_ZoneId=NULL; SET @m_AVCodecId=NULL; SET @m_GlobalId=NULL; fetch next from cur_resulttmp into @m_Code,@m_Name,@m_CoreId,@m_BoxCode,@m_IPAddress,@m_AVCodecPort,@m_ZoneCode,@m_OprationType,@m_OldNodeCode; end close cur_resulttmp deallocate cur_resulttmp; END --Speakers begin Set @m_BelongTo='Speakers'; Set @m_RowNum=0; declare cur_resulttmp cursor for select Code,Name,CoreId,ZoneCode,AnchorPointCode,AudioCodecIP,IOControllerIP,IOControllerPort,OprationType,OldNodeCode FROM OPENXML (@hdoc, '/NodeRelation/Speakers/Speaker',1) WITH (Code varchar(50),Name varchar(50),CoreId uniqueidentifier,ZoneCode varchar(50),AnchorPointCode varchar(50),AudioCodecIP varchar(50),IOControllerIP varchar(50),IOControllerPort int,OprationType varchar(50),OldNodeCode varchar(50)); open cur_resulttmp fetch next from cur_resulttmp into @m_Code,@m_Name,@m_CoreId,@m_ZoneCode,@m_AnchorPointCode,@m_AudioCodecIP,@m_IOControllerIP,@m_IOControllerPort,@m_OprationType,@m_OldNodeCode; while @@fetch_status=0 begin begin transaction IF @m_CoreId IS NULL BEGIN set @m_CoreId=@m_CoreIdNull; END IF @m_ZoneCode IS NULL BEGIN set @m_ZoneId=NULL; END ELSE BEGIN SELECT @m_ZoneId=ZoneId FROM AppServer.DeviceDefenceZone where Code=@m_ZoneCode; IF @m_ZoneId IS NULL BEGIN Set @ResultValue=-2; Insert into #ImportNodeErrorDataTempTable(ErrorId,[Key],Row,ErrorType,BelongTo) values(newid(),@m_ZoneCode,@m_RowNum,'ForeignkeyNotExist',@m_BelongTo); END END IF @m_AnchorPointCode IS NULL BEGIN set @m_AnchorPointId=NULL; END ELSE BEGIN SELECT @m_AnchorPointId=AnchorPointId FROM AppServer.AnchorPoint where Code=@m_AnchorPointCode; IF @m_AnchorPointId IS NULL BEGIN set @m_AnchorPointId=newid(); Insert into AppServer.AnchorPoint(AnchorPointId,Code,Text) values(@m_AnchorPointId,@m_AnchorPointCode,@m_AnchorPointCode); END END IF @m_AudioCodecIP IS NULL BEGIN set @m_AVCodecId=NULL; END ELSE BEGIN SELECT @m_AVCodecId=GlobalId FROM AppServer.NodeIndex where IPAddress=@m_AudioCodecIP and NodeType='AudioVideoCodec'; IF @m_AVCodecId IS NULL BEGIN set @m_AVCodecId=newid(); Insert into AppServer.NodeIndex(GlobalId,CoreNodeId,Code,Name,NodeType,IPAddress) values(@m_AVCodecId,@m_CoreIdNull,@m_Code+'-AVCodec',@m_Code+'-AVCodec','AudioVideoCodec',@m_AudioCodecIP); END END IF @m_IOControllerIP IS NULL BEGIN set @m_IOControllerId=NULL; END ELSE BEGIN SELECT @m_IOControllerId=GlobalId FROM AppServer.NodeIndex where IPAddress=@m_IOControllerIP and NodeType='IOController'; IF @m_IOControllerId IS NULL BEGIN set @m_IOControllerId=newid(); Insert into AppServer.NodeIndex(GlobalId,CoreNodeId,Code,Name,NodeType,IPAddress) values(@m_IOControllerId,@m_CoreIdNull,@m_Code+'-IOController',@m_Code+'-IOController','IOController',@m_IOControllerIP); END END IF (@m_IOControllerPort IS NULL) or (@m_IOControllerId IS NULL) BEGIN set @m_SwitchId=NULL; END ELSE BEGIN Select @m_SwitchId=GlobalId from AppServer.NodeIndex a,AppServer.NodeDataLinkRelation b where a.globalid=b.childnodeid and b.parentnodeid=@m_IOControllerId and a.idnumber=@m_IOControllerPort and nodetype='Switch'; IF (@m_SwitchId IS NULL) BEGIN set @m_SwitchId=NEWID(); Insert into AppServer.NodeIndex(GlobalId,CoreNodeId,Code,Name,NodeType,IDNumber) values(@m_SwitchId,@m_CoreIdNull,@m_IOControllerIP+'-'+Convert(varchar(50),@m_IOControllerPort),@m_IOControllerIP+'-'+Convert(varchar(50),@m_IOControllerPort),'Switch',@m_IOControllerPort); Insert into AppServer.NodeConnectRelation(ConnectionId,ParentNodeId,ChildNodeId) values(NEWID(),@m_IOControllerId,@m_SwitchId); Insert into AppServer.NodeDataLinkRelation(DataLinkId,ParentNodeId,ChildNodeId) values(NEWID(),@m_IOControllerId,@m_SwitchId); END END IF (@m_OprationType='ADD') BEGIN Select @m_GlobalId=GlobalId from AppServer.NodeIndex where Code=@m_Code and NodeType='Speaker'; IF @m_GlobalId IS NULL BEGIN set @m_GlobalId=newid(); Insert into AppServer.NodeIndex(GlobalId,CoreNodeId,Code,Name,NodeType,IDNumber) values(@m_GlobalId,@m_CoreId,@m_Code,@m_Name,'Speaker',0); END IF @m_IOControllerPort IS NULL BEGIN set @m_IOControllerPort=0; END IF (@m_AVCodecId IS NOT NULL) BEGIN --添加数据连接关系 IF NOT EXISTS(SELECT * FROM AppServer.NodeConnectRelation where ParentNodeId=@m_AVCodecId and ChildNodeId=@m_GlobalId) BEGIN Insert into AppServer.NodeConnectRelation(ConnectionId,ParentNodeId,ChildNodeId) values(NEWID(),@m_AVCodecId,@m_GlobalId); END --添加物理连接关系 IF NOT EXISTS(SELECT * FROM AppServer.NodeDataLinkRelation where ParentNodeId=@m_AVCodecId and ChildNodeId=@m_GlobalId) BEGIN Insert into AppServer.NodeDataLinkRelation(DataLinkId,ParentNodeId,ChildNodeId) values(NEWID(),@m_AVCodecId,@m_GlobalId); END END --添加防区关系 IF (@m_ZoneId IS NOT NULL) BEGIN IF NOT EXISTS(SELECT * FROM AppServer.NodeInDDZ where ZoneId=@m_ZoneId and NodeId=@m_GlobalId) BEGIN Insert into AppServer.NodeInDDZ(NDLId,ZoneId,NodeId,IsMain) values(newid(),@m_ZoneId,@m_GlobalId,0); END END --添加柱子关系 IF (@m_AnchorPointId IS NOT NULL) BEGIN IF NOT EXISTS(SELECT * FROM AppServer.NodeLocationRelation where AnchorPointId=@m_AnchorPointId and NodeGlobalId=@m_GlobalId) BEGIN Insert into AppServer.NodeLocationRelation(LocationRefId,AnchorPointId,NodeGlobalId) values(NEWID(),@m_AnchorPointId,@m_GlobalId); END END --添加电源关系 IF (@m_SwitchId IS NOT NULL) BEGIN IF NOT EXISTS(SELECT * FROM AppServer.NodePowerSupplyRelation where PowerNodeId=@m_SwitchId and ElectricityNodeId=@m_GlobalId) BEGIN Insert into AppServer.NodePowerSupplyRelation(PowerSupplyId,PowerNodeId,ElectricityNodeId) values(NEWID(),@m_SwitchId,@m_GlobalId); END END END --ELSE IF (@m_OprationType='Modify') --BEGIN --END commit transaction; SET @m_RowNum=@m_RowNum+1; SET @m_ZoneId=NULL; SET @m_AnchorPointId=NULL; SET @m_AVCodecId=NULL; SET @m_IOControllerId=NULL; SET @m_SwitchId=NULL; SET @m_GlobalId=NULL; fetch next from cur_resulttmp into @m_Code,@m_Name,@m_CoreId,@m_ZoneCode,@m_AnchorPointCode,@m_AudioCodecIP,@m_IOControllerIP,@m_IOControllerPort,@m_OprationType,@m_OldNodeCode; end close cur_resulttmp deallocate cur_resulttmp; END --Lights begin Set @m_BelongTo='Lights'; Set @m_RowNum=0; declare cur_resulttmp cursor for select Code,Name,CoreId,ZoneCode,AnchorPointCode,IOControllerIP,IOControllerPort,OprationType,OldNodeCode FROM OPENXML (@hdoc, '/NodeRelation/Lights/Light',1) WITH (Code varchar(50),Name varchar(50),CoreId uniqueidentifier,ZoneCode varchar(50),AnchorPointCode varchar(50),IOControllerIP varchar(50),IOControllerPort int,OprationType varchar(50),OldNodeCode varchar(50)); open cur_resulttmp fetch next from cur_resulttmp into @m_Code,@m_Name,@m_CoreId,@m_ZoneCode,@m_AnchorPointCode,@m_IOControllerIP,@m_IOControllerPort,@m_OprationType,@m_OldNodeCode; while @@fetch_status=0 begin begin transaction IF @m_CoreId IS NULL BEGIN set @m_CoreId=@m_CoreIdNull; END IF @m_ZoneCode IS NULL BEGIN set @m_ZoneId=NULL; END ELSE BEGIN SELECT @m_ZoneId=ZoneId FROM AppServer.DeviceDefenceZone where Code=@m_ZoneCode; IF @m_ZoneId IS NULL BEGIN Set @ResultValue=-2; Insert into #ImportNodeErrorDataTempTable(ErrorId,[Key],Row,ErrorType,BelongTo) values(newid(),@m_ZoneCode,@m_RowNum,'ForeignkeyNotExist',@m_BelongTo); END END IF @m_AnchorPointCode IS NULL BEGIN set @m_AnchorPointId=NULL; END ELSE BEGIN SELECT @m_AnchorPointId=AnchorPointId FROM AppServer.AnchorPoint where Code=@m_AnchorPointCode; IF @m_AnchorPointId IS NULL BEGIN set @m_AnchorPointId=newid(); Insert into AppServer.AnchorPoint(AnchorPointId,Code,Text) values(@m_AnchorPointId,@m_AnchorPointCode,@m_AnchorPointCode); END END IF @m_IOControllerIP IS NULL BEGIN set @m_IOControllerId=NULL; END ELSE BEGIN SELECT @m_IOControllerId=GlobalId FROM AppServer.NodeIndex where IPAddress=@m_IOControllerIP and NodeType='IOController'; IF @m_IOControllerId IS NULL BEGIN set @m_IOControllerId=newid(); Insert into AppServer.NodeIndex(GlobalId,CoreNodeId,Code,Name,NodeType,IPAddress) values(@m_IOControllerId,@m_CoreIdNull,@m_Code+'-IOController',@m_Code+'-IOController','IOController',@m_IOControllerIP); END END IF (@m_IOControllerPort IS NULL) or (@m_IOControllerId IS NULL) BEGIN set @m_SwitchId=NULL; END ELSE BEGIN Select @m_SwitchId=GlobalId from AppServer.NodeIndex a,AppServer.NodeDataLinkRelation b where a.globalid=b.childnodeid and b.parentnodeid=@m_IOControllerId and a.idnumber=@m_IOControllerPort and nodetype='Switch'; IF (@m_SwitchId IS NULL) BEGIN set @m_SwitchId=NEWID(); Insert into AppServer.NodeIndex(GlobalId,CoreNodeId,Code,Name,NodeType,IDNumber) values(@m_SwitchId,@m_CoreIdNull,@m_IOControllerIP+'-'+Convert(varchar(50),@m_IOControllerPort),@m_IOControllerIP+'-'+Convert(varchar(50),@m_IOControllerPort),'Switch',@m_IOControllerPort); Insert into AppServer.NodeConnectRelation(ConnectionId,ParentNodeId,ChildNodeId) values(NEWID(),@m_IOControllerId,@m_SwitchId); Insert into AppServer.NodeDataLinkRelation(DataLinkId,ParentNodeId,ChildNodeId) values(NEWID(),@m_IOControllerId,@m_SwitchId); END END IF (@m_OprationType='ADD') BEGIN Select @m_GlobalId=GlobalId from AppServer.NodeIndex where Code=@m_Code and NodeType='Light'; IF @m_GlobalId IS NULL BEGIN set @m_GlobalId=newid(); Insert into AppServer.NodeIndex(GlobalId,CoreNodeId,Code,Name,NodeType,IDNumber) values(@m_GlobalId,@m_CoreId,@m_Code,@m_Name,'Light',0); END --添加防区关系 IF (@m_ZoneId IS NOT NULL) BEGIN IF NOT EXISTS(SELECT * FROM AppServer.NodeInDDZ where ZoneId=@m_ZoneId and NodeId=@m_GlobalId) BEGIN Insert into AppServer.NodeInDDZ(NDLId,ZoneId,NodeId,IsMain) values(newid(),@m_ZoneId,@m_GlobalId,0); END END --添加柱子关系 IF (@m_AnchorPointId IS NOT NULL) BEGIN IF NOT EXISTS(SELECT * FROM AppServer.NodeLocationRelation where AnchorPointId=@m_AnchorPointId and NodeGlobalId=@m_GlobalId) BEGIN Insert into AppServer.NodeLocationRelation(LocationRefId,AnchorPointId,NodeGlobalId) values(NEWID(),@m_AnchorPointId,@m_GlobalId); END END --添加电源关系 IF (@m_SwitchId IS NOT NULL) BEGIN IF NOT EXISTS(SELECT * FROM AppServer.NodePowerSupplyRelation where PowerNodeId=@m_SwitchId and ElectricityNodeId=@m_GlobalId) BEGIN Insert into AppServer.NodePowerSupplyRelation(PowerSupplyId,PowerNodeId,ElectricityNodeId) values(NEWID(),@m_SwitchId,@m_GlobalId); END END END --ELSE IF (@m_OprationType='Modify') --BEGIN --END commit transaction; SET @m_RowNum=@m_RowNum+1; SET @m_ZoneId=NULL; SET @m_AnchorPointId=NULL; SET @m_IOControllerId=NULL; SET @m_SwitchId=NULL; SET @m_GlobalId=NULL; SET @m_IOControllerPort=NULL; fetch next from cur_resulttmp into @m_Code,@m_Name,@m_CoreId,@m_ZoneCode,@m_AnchorPointCode,@m_IOControllerIP,@m_IOControllerPort,@m_OprationType,@m_OldNodeCode; end close cur_resulttmp deallocate cur_resulttmp; END EXEC sp_xml_removedocument @hdoc Select * From #ImportNodeErrorDataTempTable; DROP TABLE #ImportNodeErrorDataTempTable; IF (@WriteToLog=1) BEGIN EXEC AppServer.SaveSystemEventLog2 @ClientId=@ClientId,@EventType='CLIENT_ImportNodeZoneRelationData',@TimeCreated=@TimeCreated,@EventData=@RelationDataXML END end
