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

--	declare @Result varchar(max)
--			,@ContentEncoding varchar(255)
--			,@Type varchar(255)
--			,@OperationType varchar(255)
--			,@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 [AKUZ].T_DIVISION d  where d.DivisionID = @ID))
--		BEGIN
--			select @OperationType = 'Update'
--		END
--		ELSE IF (@IsNew = 0 and not exists (select * from [AKUZ].T_DIVISION d where d.DivisionID = @ID))
--		BEGIN
--			select @OperationType = 'Delete'
--				select @Result = (select 
--				@LpuCode				as 'header/LPU'
--				,@OperationType			as 'header/TYPE'
--				,@ID					as 'header/ID'
--				FOR XML PATH(''),
--				ROOT('DIVISION')
--			) 
--		END

--select @ContentEncoding = 'utf-8'
--   ,@Type = 'DIVISION'
--   ,@OwnerID = @ID

--		IF(@OperationType <> 'Delete')
--		BEGIN

--			SELECT @Result = ( 
			
--					--собираем DIVISION
--					select 
--						@LpuCode			as 'header/LPU'
--						,@OperationType		as 'header/TYPE'
--						,@ID				as 'header/ID'

--						,d.[Code]			as 'body/DV_CODE'
--						,d.[Name]			as 'body/NAME'
--						,d.[FullName]		as 'body/FULLNAME'
--						,d.[Level]			as 'body/LEVEL'
--						,d.[CodeMz]			as 'body/CODE_MZ'
--						,d.[Nofilet]		as 'body/PHONE'
--						,d.[Adress]			as 'body/ADDRESS'
--						,d.[Branch]			as 'body/BRANCH_ID'
--						,CASE d.[DivisionType]
--						WHEN 12801 THEN 0
--						WHEN 12802 THEN 1
--						WHEN 12803 THEN 2
--						WHEN 12804 THEN 3
--						WHEN 12805 THEN 4
--						WHEN 12806 THEN 5
--						WHEN 12807 THEN 6
--						WHEN 12808 THEN 7
--						WHEN 12809 THEN 8
--						WHEN 12810 THEN 9
--						WHEN 12810 THEN 10
--						WHEN 12810 THEN 11
--						ELSE 0 END			as 'body/DIVISION_TYPE'
--						,d.[RevenueType]	as 'body/REVENUE_TYPE'
--						,d.[IDNumberCard]   as 'body/ID_NUMBER_CARD'
--					    ,d.[IsActive]								 as 'body/ACTIVE'
--					FROM [AKUZ].T_DIVISION d
--					where d.DivisionID = @ID
--					FOR XML PATH(''),
--					ROOT('DIVISION')
--			) 
--			END

--			INSERT INTO [AKUZ].[T_RABBIT_SEND_OBJECT]
--			   ([RabbitSendObjectID]
--			   ,[QueueName]
--			   ,[Content]
--			   ,[MessageID]
--			   ,[Headers]
--			   ,[ContentEncoding]
--			   ,[Type]
--			   ,[UserID]
--			   ,[State]
--			   ,[CreateDateTime]
--			   ,[OwnerID])
--			select
--			   NewID()--(<RabbitSendObjectID, uniqueidentifier,>
--			   ,@QueueName--,<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
--		END
--	ELSE 
--	IF (@Xml is not null)
--		BEGIN
		
--		-- Generate table #DIVISION
--		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]/DV_CODE[1]', 'varchar(16)') as [Code]
--				,T.c.value('./body[1]/NAME[1]', 'varchar(max)') as [Name]
--				,T.c.value('./body[1]/FULLNAME[1]', 'varchar(max)') as [FullName]
--				,T.c.value('./body[1]/LEVEL[1]', 'int') as [Level]
--				,T.c.value('./body[1]/CODE_MZ[1]', 'varchar(16)') as [CodeMz]
--				,T.c.value('./body[1]/PHONE[1]', 'varchar(max)') as [Nofilet]
--				,T.c.value('./body[1]/ADDRESS[1]', 'varchar(max)') as [Adress]
--				,T.c.value('./body[1]/BRANCH_ID[1]', 'uniqueidentifier') as [Branch]
--				,T.c.value('./body[1]/DIVISION_TYPE[1]', 'int') as [DivisionType]
--				,T.c.value('./body[1]/REVENUE_TYPE[1]', 'int') as [RevenueType]
--				,T.c.value('./body[1]/ID_NUMBER_CARD[1]', 'int') as [IDNumberCard]
--				,T.c.value('./body[1]/ACTIVE[1]', 'bit') as [Active]
--			INTO #DIVISION
--		from @Xml.nodes('DIVISION') T(c)
--		select @OperationType = (select OperationType from #DIVISION)

--		if(@OperationType <> 'Delete')
--		BEGIN
--		-- update [AKUZ].T_DIVISION
--		update d
--		set 
--		d.[Code] = div.[Code], 
--		d.[Name] = div.[Name],
--		d.[FullName] = div.[FullName],
--		d.[Level] = div.[Level],
--		d.[CodeMz] = div.[CodeMz],
--		d.[Nofilet] = div.[Nofilet],
--		d.[Adress] = div.[Adress],
--		d.[DivisionType] = (CASE div.[DivisionType]
--			WHEN 0 THEN 12801
--			WHEN 1 THEN 12802
--			WHEN 2 THEN 12803
--			WHEN 3 THEN 12804
--			WHEN 4 THEN 12805
--			WHEN 5 THEN 12806
--			WHEN 6 THEN 12807
--			WHEN 7 THEN 12808
--			WHEN 8 THEN 12809
--			WHEN 9 THEN 12810
--			ELSE 12801 END),
--		d.[RevenueType] = div.[RevenueType],
--		d.[IDNumberCard] = div.[IDNumberCard],
--		d.[IsActive] = div.[Active]
--		from [AKUZ].T_DIVISION d
--		join #DIVISION div on div.[ID] = d.OtherID

--		-- INSERT [AKUZ].T_DIVISION
--		INSERT INTO [AKUZ].[T_DIVISION] 
--		([DivisionID], [Code], [Name], [FullName], [Level], [CodeMz], [Nofilet], [Adress], [Branch], [DivisionType], [RevenueType],[IsRequestonDivision], [IsRequestInHospital], [IsActive], [OtherID],[IDNumberCard],[WorkDays], [HospitalizationRecTime])
--		SELECT
--			newID()
--			,d.[Code]
--			,d.[Name]
--			,d.[FullName]
--			,d.[Level]
--			,d.[CodeMz]
--			,d.[Nofilet]
--			,d.[Adress]
--			,(select br.LpuID from [AKUZ].T_BRANCH br where br.OtherId = d.[Branch])
--			,(CASE d.[DivisionType]
--			WHEN 0 THEN 12801
--			WHEN 1 THEN 12802
--			WHEN 2 THEN 12803
--			WHEN 3 THEN 12804
--			WHEN 4 THEN 12805
--			WHEN 5 THEN 12806
--			WHEN 6 THEN 12807
--			WHEN 7 THEN 12808
--			WHEN 8 THEN 12809
--			WHEN 9 THEN 12810
--			ELSE 12801 END)
--			,d.[RevenueType]
--			,0
--			,0
--			,d.[Active]
--			,d.ID
--			,d.[IDNumberCard]
--			,31
--			,'20000101 00:00:00'
--		from #DIVISION d
--		where not exists (
--			select * 
--			from [AKUZ].T_DIVISION d1 
--			join #DIVISION dd on dd.[ID] = d1.OtherID
--		)
		
--		END
--		ELSE
--		BEGIN
--			delete d
--			from [AKUZ].[T_DIVISION] d
--			join #DIVISION dd on dd.[ID] = d.OtherID
--		END
--		-- Drop table
--		DROP TABLE #DIVISION

--		END
--END
--GO