USE [LT] GO /****** Object: StoredProcedure [dbo].[sp_asset_data_transfer] Script Date: 06/16/2016 17:53:23 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO -- ============================================= -- Author: <> -- Create date: <2016-5-13> -- Description: <资产数据转移> -- ============================================= CREATE PROCEDURE [dbo].[sp_asset_data_transfer] AS BEGIN declare @gsmc varchar(50) declare @prjkey varchar(50) declare @prjsum varchar(50) declare @khbh varchar(50) declare @gsdz varchar(50) declare @sum varchar(50) declare @gsdz_key varchar(50) declare @gsdz_sum varchar(50) declare @floor_key varchar(50) declare @floor_name varchar(50) declare @floor_sum varchar(50) declare @floor_code varchar(50) declare @room_key varchar(50) declare @room_name varchar(50) declare @room_sum varchar(50) declare @room_code varchar(50) declare @cabinet_key varchar(50) declare @cabinet_name varchar(50) declare @cabinet_sum varchar(50) declare @cabinet_code varchar(50) declare @key varchar(50) declare @datatype char(1) declare @weizhixinxi_key varchar(50) declare @root_key varchar(50) DECLARE @ID INT DECLARE @i INT DECLARE @count INT DECLARE @i2 INT DECLARE @count2 INT DECLARE @i3 INT DECLARE @count3 INT DECLARE @i4 INT DECLARE @count4 INT DECLARE @i5 INT DECLARE @count5 INT DECLARE @i6 INT DECLARE @count6 INT DECLARE @i7 INT DECLARE @count7 INT --同步项目 create table #a(Sid int identity(1,1),"key" varchar(50),"text" varchar(50)) create index idx_#a_sid on #a(sid) insert into #a("key","text") select prjkey,prjname from VisualNet_Project where id = 12 select @count = COUNT(1) from #a set @i = 1; while(@i<=@count) begin select @prjkey = "key",@gsmc = "text" from #a where Sid = @i select @root_key="key" from VisualNet_tvwPrj where prjkey = @prjkey and "relative" = 'root' create table #b(Sid int identity(1,1),"key" varchar(50),"text" varchar(50)) create index idx_#b_sid on #b(sid) insert into #b("key","text") select "key","text" from VisualNet_tvwPrj where prjkey = @prjkey and "relative"= @root_key select @count2 = COUNT(1) from #b set @i2 = 1; while(@i2<=@count2) begin select @gsdz_key = "key",@gsdz = "text" from #b where Sid = @i2 select @sum = COUNT(1) from VisualNetKey where PrjectKey = @prjkey and CustomerAddress = @gsdz and DeleteFlg = 0 select @khbh = CustomerCode from VisualNetKey where PrjectKey = @prjkey and CustomerAddress = @gsdz and DeleteFlg = 0 if(@sum = 0) begin select @prjsum=COUNT(1) from Customers where gsmc = @gsmc and gsdz = @gsdz if(@prjsum = 0) begin select @khbh = MAX(khbh+1) from Customers if(@khbh is null) begin set @khbh = '0001' end else begin set @khbh = RIGHT(('0000'+@khbh),4) end insert into Customers(khbh,gsmc,gsdz,gsdh) values(@khbh,@gsmc,@gsdz,'') insert into VisualNetKey(PrjectKey,ItemKey,CustomerCode,CustomerAddress,DataType,SyncFlg,DeleteFlg) values(@prjkey,'',@khbh,@gsdz,0,1,0) end else begin select @khbh=khbh from Customers where gsmc = @gsmc and gsdz = @gsdz insert into VisualNetKey(PrjectKey,ItemKey,CustomerCode,CustomerAddress,DataType,SyncFlg,DeleteFlg) values(@prjkey,'',@khbh,@gsdz,0,1,0) end end else begin update VisualNetKey set SyncFlg = 1 where PrjectKey = @prjkey and CustomerCode = @khbh and CustomerAddress = @gsdz and DataType = 0 end set @i2=@i2+1 end --位置信息 select @weizhixinxi_key = "key" from VisualNet_tvwPrj where prjkey = @prjkey and "relative" = 'root' --地址 create table #c(Sid int identity(1,1),"key" varchar(50),"text" varchar(50)) create index idx_#c_sid on #c(sid) insert into #c("key","text") select "key","text" from VisualNet_tvwPrj where prjkey = @prjkey and "relative" = @weizhixinxi_key select @count3 = COUNT(1) from #c set @i3 = 1; while(@i3<=@count3) begin select @gsdz_key = "key",@gsdz = "text" from #b where Sid = @i3 select @gsdz_sum = COUNT(1) from VisualNetKey where ItemKey = @gsdz_key and DeleteFlg = 0 select @gsdz = CustomerAddress from VisualNetKey where ItemKey = @gsdz_key and DeleteFlg = 0 if(@gsdz_sum = 0) begin insert into VisualNetKey(PrjectKey,ItemKey,CustomerCode,CustomerAddress,DataType,SyncFlg,DeleteFlg) values(@prjkey,@gsdz_key,@khbh,@gsdz,1,1,0) end else begin update VisualNetKey set SyncFlg = 1 where PrjectKey = @prjkey and CustomerCode = @khbh and CustomerAddress = @gsdz and DataType = 1 end --楼层 create table #d(Sid int identity(1,1),"key" varchar(50),"text" varchar(50)) create index idx_#d_sid on #d(sid) insert into #d("key","text") select "key","text" from VisualNet_tvwPrj where prjkey = @prjkey and "relative" = @gsdz_key select @count4 = COUNT(1) from #d set @i4 = 1; while(@i4<=@count4) begin select @floor_key = "key",@floor_name = "text" from #d where Sid = @i4 select @floor_sum = COUNT(1) from VisualNetKey where ItemKey = @floor_key and PrjectKey = @prjkey and CustomerAddress = @gsdz and DeleteFlg = 0 select @floor_code = FloorCode from VisualNetKey where ItemKey = @floor_key and PrjectKey = @prjkey and CustomerAddress = @gsdz and DeleteFlg = 0 if(@floor_sum = 0) begin select @floor_code = MAX(floorcode+1) from CustomersFloor if(@floor_code is null) begin set @floor_code = '000001' end else begin set @floor_code = RIGHT(('000000'+@floor_code),6) end select @khbh = khbh from Customers where gsmc = @gsmc and gsdz = @gsdz insert into CustomersFloor(customercode,floorcode,floorname,floordevice) values(@khbh,@floor_code,@floor_name,0) insert into VisualNetKey(PrjectKey,ItemKey,CustomerCode,CustomerAddress,FloorCode,DataType,SyncFlg,DeleteFlg) values(@prjkey,@floor_key,@khbh,@gsdz,@floor_code,2,1,0) end else begin update CustomersFloor set floorname = @floor_name where floorcode = @floor_code update VisualNetKey set SyncFlg = 1 where FloorCode = @floor_code and DataType = 2 end --房间 create table #e(Sid int identity(1,1),"key" varchar(50),"text" varchar(50)) create index idx_#e_sid on #e(sid) insert into #e("key","text") select "key","text" from VisualNet_tvwPrj where prjkey = @prjkey and "relative" = @floor_key select @count5 = COUNT(1) from #e set @i5 = 1; while(@i5<=@count5) begin select @room_key = "key",@room_name = "text" from #e where Sid = @i5 select @room_sum = COUNT(1) from VisualNetKey where ItemKey = @room_key and PrjectKey = @prjkey and CustomerAddress = @gsdz and DeleteFlg = 0 select @room_code = RoomCode from VisualNetKey where ItemKey = @room_key and PrjectKey = @prjkey and CustomerAddress = @gsdz and DeleteFlg = 0 if(@room_sum = 0) begin select @room_code = MAX(roomcode+1) from CustomersRoom if(@room_code is null) begin set @room_code = '000001' end else begin set @room_code = RIGHT(('000000'+@room_code),6) end select @khbh = khbh from Customers where gsmc = @gsmc and gsdz = @gsdz insert into CustomersRoom(customercode,floorcode,roomcode,roomname,roomdevice) values(@khbh,@floor_code,@room_code,@room_name,0) insert into VisualNetKey(PrjectKey,ItemKey,CustomerCode,CustomerAddress,FloorCode,RoomCode,DataType,SyncFlg,DeleteFlg) values(@prjkey,@room_key,@khbh,@gsdz,@floor_code,@room_code,3,1,0) end else begin update CustomersRoom set roomname = @room_name where roomcode = @room_code update VisualNetKey set SyncFlg = 1 where RoomCode = @room_code and DataType =3 end --机柜 create table #f(Sid int identity(1,1),"key" varchar(50),"text" varchar(50)) create index idx_#f_sid on #f(sid) insert into #f("key","text") select "key","text" from VisualNet_tvwPrj where prjkey = @prjkey and "relative" = @room_key select @count6 = COUNT(1) from #f set @i6 = 1; while(@i6<=@count6) begin select @cabinet_key = "key",@cabinet_name = "text" from #f where Sid = @i6 select @cabinet_sum = COUNT(1) from VisualNetKey where ItemKey = @cabinet_key and PrjectKey = @prjkey and CustomerAddress = @gsdz and DeleteFlg = 0 select @cabinet_code = CabinetCode from VisualNetKey where ItemKey = @cabinet_key and PrjectKey = @prjkey and CustomerAddress = @gsdz and DeleteFlg = 0 if(@cabinet_sum = 0) begin select @cabinet_code = MAX(cabinetcode+1) from CustomersCabinet if(@cabinet_code is null) begin set @cabinet_code = '000001' end else begin set @cabinet_code = RIGHT(('000000'+@cabinet_code),6) end select @khbh = khbh from Customers where gsmc = @gsmc and gsdz = @gsdz insert into CustomersCabinet(customercode,floorcode,roomcode,cabinetcode,cabinetname,cabinetdevice) values(@khbh,@floor_code,@room_code,@cabinet_code,@cabinet_name,0) insert into VisualNetKey(PrjectKey,ItemKey,CustomerCode,CustomerAddress,FloorCode,RoomCode,CabinetCode,DataType,SyncFlg,DeleteFlg) values(@prjkey,@cabinet_key,@khbh,@gsdz,@floor_code,@room_code,@cabinet_code,4,1,0) end else begin update CustomersCabinet set cabinetname = @cabinet_name where cabinetcode = @cabinet_code update VisualNetKey set SyncFlg = 1 where CabinetCode = @cabinet_code and DataType =4 end set @i6=@i6+1 end drop table #f set @i5=@i5+1 end drop table #e set @i4=@i4+1 end drop table #d set @i3=@i3+1 end drop table #c drop table #b set @i=@i+1 end drop table #a update VisualNetKey set DeleteFlg = 1 where SyncFlg = 0; declare cursor_del cursor for select PrjectKey,ItemKey,CustomerCode,CustomerAddress,FloorCode,RoomCode,CabinetCode,datatype from VisualNetKey where DeleteFlg = 1 open cursor_del fetch next from cursor_del into @prjkey,@key,@khbh,@gsdz,@floor_code,@room_code,@cabinet_code,@datatype while @@FETCH_STATUS=0 begin if(@datatype = 2) begin delete from CustomersFloor where floorcode = @floor_code end if(@datatype = 3) begin delete from CustomersRoom where roomcode = @room_code end if(@datatype = 4) begin delete from CustomersCabinet where cabinetcode = @cabinet_code delete from AssetSb where lxcode = @cabinet_code end fetch next from cursor_del into @prjkey,@key,@khbh,@gsdz,@floor_code,@room_code,@cabinet_code,@datatype end close cursor_del deallocate cursor_del update VisualNetKey set SyncFlg = 0; END GO