IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_GetSetBranchXml]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[sp_GetSetBranchXml]
--GO
--SET ANSI_NULLS ON
--GO
--SET QUOTED_IDENTIFIER ON
--GO
---- =============================================
---- Author:		Vitacore (с)
---- Create date: 2014-02-05
---- Description:	Выгрузка/загрузка справочника Филиалов 
---- =============================================
--CREATE PROCEDURE [dbo].[sp_GetSetBranchXml]
--	@LpuCode varchar(max) = null,
--	@Xml xml = null,
--	@IsNew bit = 1,
--	@ID uniqueidentifier = null
--AS
--BEGIN

--	declare @Result varchar(max)
--			,@ContentEncoding varchar(255)
--			,@Type varchar(255)
--			,@OperationType varchar(max) = null
--			,@OwnerID uniqueidentifier

--	SET NOCOUNT ON;
--	IF (@Xml is null)
--	BEGIN
--		--выясняем тип операции
--		IF(@IsNew = 1)
--		BEGIN
--			select @OperationType = 'Insert'
--		END
--		ELSE IF (@IsNew = 0 and exists (select * from [RDS].T_BRANCH br  where br.BranchID = @ID))
--		BEGIN
--			select @OperationType = 'Update'
--		END
--		ELSE IF (@IsNew = 0 and not exists (select * from [RDS].T_BRANCH br where br.BranchID = @ID))
--		BEGIN
--			select @OperationType = 'Delete'
--				select @Result = (select 
--				@LpuCode				as 'header/LPU'
--				,@OperationType			as 'header/TYPE'
--				,@ID					as 'header/ID'
--				FOR XML PATH(''),
--				ROOT('BRANCH')
--			) 
--		END
--select @ContentEncoding = 'utf-8'
--   ,@Type = 'BRANCH'
--   ,@OwnerID = @ID
--		IF(@OperationType <> 'Delete')
--		BEGIN
--			select @Result = ( 
--				--собираем BRANCH
--				select 			
--					@LpuCode			as 'header/LPU'
--					,@OperationType		as 'header/TYPE'
--					,@ID				as 'header/ID'

--					,br.[BranchCode]	as 'body/BR_CODE'
--					,br.[Name]			as 'body/NAME'
--					,convert(varchar(10), br.[DateAdd], 20) as 'body/DATE_ADD'
--					,convert(varchar(10), br.[DateRemove], 20) as 'body/DATE_REMOVE'
--					,br.[AddressString] as 'body/ADDRESS'
--					,br.[NameFull]		as 'body/NAMEFULL'
--					,br.[RegistryPhones] as 'body/PHONE'
--					,br.[LpuType]		as 'body/LPU_TYPE'
--					,br.[Category]		as 'body/CATEGORY'
--					,br.[IsActive]								 as 'body/ACTIVE'
--				FROM [RDS].T_BRANCH br
--				where br.BranchID = @ID
--				FOR XML PATH(''),
--				ROOT('BRANCH')
--			) 
--		END

--		INSERT INTO [RDS].[T_RABBIT_SEND_OBJECT]
--		   ([RabbitSendObjectID]
--		   ,[QueueName]
--		   ,[Content]
--		   ,[MessageID]
--		   ,[Headers]
--		   ,[ContentEncoding]
--		   ,[Type]
--		   ,[UserID]
--		   ,[State]
--		   ,[CreateDateTime]
--		   ,[OwnerID])
--		select
--		   NewID()--(<RabbitSendObjectID, uniqueidentifier,>
--		   ,s.ServerName--,<QueueName, varchar(255),>
--		   ,dbo.ConvertTextToBinaryZip(@Result, '')--,<Content, text,>
--		   ,null--,<MessageID, varchar(255),>
--		   ,null--,<Headers, varchar(255),>
--		   ,@ContentEncoding--,<ContentEncoding, varchar(255),>
--		   ,@Type--,<Type, varchar(255),>
--		   ,null--,<UserID, varchar(255),>
--		   ,0--,<State, int,>)
--		   ,GETDATE()
--		   ,@OwnerID
--		   from [RDS].T_SYNC_SERVERS s
--			where s.ServerType <> 1 and not 
--			exists (select * from [RDS].T_SYNC_SERVERS s1
--			join [RDS].T_SERVERS_LINK sl on sl.SyncServers = s1.SyncServersID
--			join [RDS].T_SERVERS_LINK_LPU ll on ll.ServersLinkID = sl.ServersLinkID
--			join [RDS].T_LPU_MAIN lp on lp.MCOD = ll.LpuMain
--			where lp.MCOD = @LpuCode and s1.SyncServersID = s.SyncServersID)
--	END
--	ELSE IF (@Xml is not null)
--	BEGIN		
--		-- Generate table #BRANCH
--		select
--				T.c.value('./header[1]/TYPE[1]', 'varchar(max)') as [OperationType]
--				,T.c.value('./header[1]/LPU[1]', 'varchar(6)') as [LpuMain]
--				,T.c.value('./header[1]/ID[1]', 'uniqueidentifier') as [ID]
--				,T.c.value('./body[1]/BR_CODE[1]', 'varchar(16)') as [BranchCode]
--				,T.c.value('./body[1]/NAME[1]', 'varchar(max)') as [Name]
--				,T.c.value('./body[1]/DATE_ADD[1]', 'datetime') as [DateAdd]
--				,T.c.value('./body[1]/DATE_REMOVE[1]', 'datetime') as [DateRemove]
--				,T.c.value('./body[1]/ADDRESS[1]', 'varchar(max)') as [AddressString]
--				,T.c.value('./body[1]/NAMEFULL[1]', 'varchar(max)') as [NameFull]
--				,T.c.value('./body[1]/PHONE[1]', 'varchar(max)') as [RegistryPhones]
--				,T.c.value('./body[1]/LPU_TYPE[1]', 'int') as [LpuType]
--				,T.c.value('./body[1]/CATEGORY[1]', 'int') as [Category]
--				,T.c.value('./body[1]/ACTIVE[1]', 'bit') as [Active]
--			INTO #BRANCH
--		from @Xml.nodes('BRANCH') T(c)		
--		select @OperationType = (select OperationType from #BRANCH)

--		if(@OperationType <> 'Delete')
--		BEGIN
--		-- update [RDS].T_BRANCH
--			update br
--			set 
--				br.[BranchCode] = b.[BranchCode], 
--				br.[Name] = b.[Name],
--				br.[DateAdd] = b.[DateAdd],
--				br.[DateRemove] = b.[DateRemove],
--				br.[AddressString] = b.[AddressString],
--				br.[NameFull] = b.[NameFull],
--				br.[RegistryPhones] = b.[RegistryPhones],
--				br.[LpuType] = b.[LpuType],
--				br.[Category] = b.[Category],
--				br.[IsActive] = b.[Active],
--				br.ID = b.ID,
--				br.LpuMain = b.LpuMain
--			from [RDS].T_BRANCH br
--			join #BRANCH b on (b.[ID] = br.ID or br.BranchID = b.[ID])

--			-- INSERT [RDS].T_BRANCH
--			INSERT INTO [RDS].[T_BRANCH] 
--			([BranchID],[BranchCode], [Name], [DateAdd], [DateRemove], [AddressString], [NameFull], [RegistryPhones], [LpuMain], [LpuType], [Category], [ID], [IsActive])
--			SELECT
--				newID()
--				,b.[BranchCode]
--				,b.[Name]
--				,b.[DateAdd]
--				,b.[DateRemove]
--				,b.[AddressString]
--				,b.[NameFull]
--				,b.[RegistryPhones]
--				, (select lpu.[MCOD] from [RDS].T_LPU_MAIN lpu where lpu.[MCOD] = b.[LpuMain])
--				,b.[LpuType]
--				,b.[Category]
--				,b.ID
--				,b.[Active]
--			from #BRANCH b
--			where not exists (
--				select * 
--				from [RDS].T_BRANCH b1 
--				where (b.[ID] = b1.ID or b1.BranchID = b.[ID])
--			)
--		END
--		ELSE
--		BEGIN
--			delete br
--			from [RDS].[T_BRANCH] br
--			join #BRANCH b on (b.[ID] = br.ID or br.BranchID = b.[ID])
--		END
		
--		-- Drop table
--		DROP TABLE #BRANCH
--	END
--END
--GO