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

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

--SET NOCOUNT ON;
--	IF (@Xml is null)
--	BEGIN

--	select @QueueName = 'RDS263'
--   ,@ContentEncoding = 'utf-8'
--   ,@Type = 'Lpu'
--   ,@OwnerID = @ID
--	-- Add the T-SQL statements to compute the return value here
--	SELECT @Result = ( 
--			select
--			@LpuCode											as 'header/LPU'
--			,@Type												as 'header/TYPE'
--			,@ID												as 'header/ID'
--			,(
--			--собираем BRANCH
--			select 
--					br.[BranchCode] as 'BR_CODE'
--					,br.[Name] as 'NAME'
--					,convert(varchar(10), br.[DateAdd], 20) as 'DATE_ADD'
--					,convert(varchar(10), br.[DateRemove], 20) as 'DATE_REMOVE'
--					,br.[AddressString] as 'ADDRESS'
--					,br.[NameFull] as 'NAMEFULL'
--					,br.[RegistryPhones] as 'PHONE'
--					,lp.[MCOD] as 'LPU'
--					,br.[LpuType] as 'LPU_TYPE'
--					,br.[Category] as 'CATEGORY'
--					,br.[BranchID] as 'ID'
--					,br.[IsActive] as 'ACTIVE'
--					--собираем DIVISION
--					, ( 
--					select 
--						d.[Code] as 'DV_CODE'
--						,d.[Name] as 'NAME'
--						,d.[FullName] as 'FULLNAME'
--						,d.[Level] as 'LEVEL'
--						,d.[CodeMz] as 'CODE_MZ'
--						,d.[Nofilet] as 'PHONE'
--						,d.[Adress] as 'ADDRESS'
--						,d.[Branch]			as 'BRANCH_ID'
--						,d.[DivisionType] as 'DIVISION_TYPE'
--						,d.[RevenueType] as 'REVENUE_TYPE'
--						,lp.[MCOD] as 'LPU'
--						,d.[DivisionID] as 'ID'
--						,d.[IDNumberCard]   as 'ID_NUMBER_CARD'
--					    ,d.[IsActive]								 as 'ACTIVE'
--                      --собираем DIVISION_LINK_STRUCTURE_BED
--    					, (
--    					select 
--    						sb.Code as 'STRUCTURE_BED'
--							,sbb.IDK_PR as 'V020_STRUCTURE_BED'
--    						,lp.[MCOD] as 'LPU' 
--							,dlsb.[DivisionLinkStructureBedID] as 'ID'
--							,dlsb.Division as 'DIVISION_ID'
--							--собираем AMOUNT_BED
--							, (
--							select 
--								sb.Code as 'STRUCTURE_BED'
--								,sbb.IDK_PR as 'V020_STRUCTURE_BED'
--								,lp.[MCOD] as 'LPU'
--								,ab.DivisionLinkStructureBed as 'DLSB'
--								,convert(varchar(10), ab.[DateBegin], 20) as 'DATE_BEGIN'
--							    ,convert(varchar(10), ab.[DateEnd], 20) as 'DATE_END'
--							    ,ab.[FemaleBed] as 'FEMALE_BED'
--							    ,ab.[MaleBed] as 'MALE_BED'
--							    ,ab.[BedsCount] as 'BEDS_COUNT'
--							    ,ab.[RepairBed] as 'REPAIR_BED'
--								,ab.[PlannedBedsCount]						as 'PLANNED_BEDS_COUNT'
--								,ab.[PlannedMaleBed]						as 'PLANNED_MALE_BED'
--								,ab.[PlannedFemaleBed]						as 'PLANNED_FEMALE_BED'
--								,ab.AmountBedID								as 'ID'
--							from [RDS].T_AMOUNT_BED ab 
--							where dlsb.DivisionLinkStructureBedID = ab.DivisionLinkStructureBed
--							FOR XML PATH('AMOUNT_BED'),
--							TYPE
--						)
--    					from [RDS].T_DIVISION_LINK_STRUCTURE_BED dlsb
--    					join [RDS].T_STRUCTURE_BED sb on sb.Code = dlsb.StructureBed 
--						left join [RDS].T_V020_STRUCTURE_BED sbb on sbb.IDK_PR = dlsb.V020StructureBed 
--    					where dlsb.Division = d.DivisionID
--    					FOR XML PATH('DIVISION_LINK_STRUCTURE_BED'),
--    					TYPE
--                      )
--					FROM [RDS].T_DIVISION d
--					where d.Branch = br.BranchID
--					FOR XML PATH('DIVISION'),
--					TYPE
--					)
--			FROM [RDS].T_BRANCH br
--			join [RDS].T_LPU_MAIN lp on lp.MCOD = br.LpuMain
--			where lp.MCOD = @LpuCode
--			FOR XML PATH('BRANCH'),
--			TYPE
--			)
--			FOR XML PATH(''),
--			ROOT('LPU')
--	) 

--	-- Отправка всем остальным подписанным серверам AKUZ-а и APS
--        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()--,<CreateDateTime, datetime,>)
--              ,@OwnerID--,<OwnerID, varchar(255),>
--        from [RDS].T_SYNC_SERVERS s
--        where s.ServerType = 0 -- Рассылаем пакет всем подписанным серверам AKUZ-а 

--		END
--	ELSE 
--	IF (@Xml is not null)
--		BEGIN
--		-- Generate table #BRANCH
--		select
--				T.c.value('./BR_CODE[1]', 'varchar(16)') as [BranchCode]
--				,T.c.value('./NAME[1]', 'varchar(max)') as [Name]
--				,T.c.value('./DATE_ADD[1]', 'datetime') as [DateAdd]
--				,T.c.value('./DATE_REMOVE[1]', 'datetime') as [DateRemove]
--				,T.c.value('./ADDRESS[1]', 'varchar(max)') as [AddressString]
--				,T.c.value('./NAMEFULL[1]', 'varchar(max)') as [NameFull]
--				,T.c.value('./PHONE[1]', 'varchar(max)') as [RegistryPhones]
--				,T.c.value('./LPU[1]', 'varchar(6)') as [LpuMain]
--				,T.c.value('./LPU_TYPE[1]', 'int') as [LpuType]
--				,T.c.value('./CATEGORY[1]', 'int') as [Category]
--				,T.c.value('./ID[1]', 'uniqueidentifier') as [ID]
--				,T.c.value('./ACTIVE[1]', 'bit') as [Active]
--			INTO #BRANCH
--		from @Xml.nodes('LPU/BRANCH') T(c)

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

--		update br
--		set br.IsActive = 0
--		from [RDS].T_BRANCH br
--		join #BRANCH bb on bb.[LpuMain] = br.LpuMain
--		where not exists (select * from #BRANCH b where 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], [IsActive], ID)
--			SELECT
--				newID()
--				,b.[BranchCode]
--				,b.[Name]
--				,b.[DateAdd]
--				,b.[DateRemove]
--				,b.[AddressString]
--				,b.[NameFull]
--				,b.[RegistryPhones]
--				,b.[LpuMain]
--				,b.[LpuType]
--				,b.[Category]
--				,b.[Active]
--				,b.[ID]
--			from #BRANCH b
--		        where not exists (
--				select * 
--				from [RDS].T_BRANCH b1 
--				where (b.[ID] = b1.ID or b1.[BranchID] = b.ID)
--			)

--		-- Generate table #DIVISION
--		select
--				T.c.value('./DV_CODE[1]', 'varchar(16)') as [Code]
--				,T.c.value('./NAME[1]', 'varchar(max)') as [Name]
--				,T.c.value('./FULLNAME[1]', 'varchar(max)') as [FullName]
--				,T.c.value('./LEVEL[1]', 'int') as [Level]
--				,T.c.value('./CODE_MZ[1]', 'varchar(16)') as [CodeMz]
--				,T.c.value('./PHONE[1]', 'varchar(max)') as [Nofilet]
--				,T.c.value('./ADDRESS[1]', 'varchar(max)') as [Adress]
--				,T.c.value('./BRANCH_ID[1]', 'uniqueidentifier') as [Branch]
--				,T.c.value('./DIVISION_TYPE[1]', 'int') as [DivisionType]
--				,T.c.value('./REVENUE_TYPE[1]', 'int') as [RevenueType]
--				,T.c.value('./LPU[1]', 'varchar(6)') as [Lpu]
--				,T.c.value('./ID[1]', 'uniqueidentifier') as [ID]
--				,T.c.value('./ID_NUMBER_CARD[1]', 'int') as [IDNumberCard]
--				,T.c.value('./ACTIVE[1]', 'bit') as [Active]
--			INTO #DIVISION
--		from @Xml.nodes('LPU/BRANCH/DIVISION') T(c)

--		-- update [RDS].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] = div.[DivisionType],
--		d.[IDNumberCard] = div.[IDNumberCard],
--		d.[RevenueType] = ( 
--		CASE d.[RevenueType]
--		WHEN 0 THEN 12900
--		WHEN 1 THEN 12901
--		WHEN 2 THEN 12902
--		ELSE div.[RevenueType] END),
--		d.[IsActive] = div.[Active],
--		d.ID = div.ID,
--		d.Branch = (select top 1 b.BranchId from [RDS].T_BRANCH b where b.BranchId = div.Branch or b.ID = div.Branch)
--		from [RDS].T_DIVISION d
--		join #DIVISION div on (div.ID = d.ID or div.ID = d.[DivisionID])

--		update dd
--		set dd.IsActive = 0
--		from [RDS].T_DIVISION dd
--		join [RDS].T_BRANCH b on b.BranchID = dd.Branch
--		join #DIVISION ddd on ddd.[Lpu] = b.[LpuMain]
--		where not exists (select * from #DIVISION div where div.[ID] = dd.ID or dd.[DivisionID] = div.ID)

--		-- INSERT [RDS].T_DIVISION
--		INSERT INTO [RDS].[T_DIVISION] 
--		([DivisionID], [Code], [Name], [FullName], [Level], [CodeMz], [Nofilet], [Adress], [Branch], [DivisionType], [RevenueType], [IsActive], ID,[IDNumberCard])
--		SELECT
--					newID()
--					,d.[Code]
--					,d.[Name]
--					,d.[FullName]
--					,d.[Level]
--					,d.[CodeMz]
--					,d.[Nofilet]
--					,d.[Adress]
--					,b.BranchID
--					,d.[DivisionType]
--					,(CASE d.[RevenueType]
--					   WHEN 0 THEN 12900
--					   WHEN 1 THEN 12901
--					   WHEN 2 THEN 12902
--					   ELSE d.[RevenueType] END)
--					,d.[Active]
--					, d.id
--					,d.[IDNumberCard]
--		from #DIVISION d
--		join [RDS].T_BRANCH b on (b.ID = d.Branch or b.BranchID = d.Branch) 
--		where b.[IsActive] = 1 and not exists (
--			select * 
--			from [RDS].T_DIVISION d1 
--			where (d1.ID = d.ID or d.ID = d1.[DivisionID])
--		)


--		-- Generate table #DIVISION_LINK_STRUCTURE_BED
--		select
--				T.c.value('./STRUCTURE_BED[1]', 'int') as [CodeSB]
--				,T.c.value('./V020_STRUCTURE_BED[1]', 'int') as [V020StructureBed]
--				,T.c.value('./LPU[1]', 'varchar(6)') as [Lpu]
--				,T.c.value('./ID[1]', 'uniqueidentifier') as [ID]
--				,T.c.value('./DIVISION_ID[1]', 'uniqueidentifier') as [Div]
--			INTO #DIVISION_LINK_STRUCTURE_BED
--		from @Xml.nodes('LPU/BRANCH/DIVISION/DIVISION_LINK_STRUCTURE_BED') T(c)		


--		-- Generate table #AMOUNT_BED
--		select
--				T.c.value('./STRUCTURE_BED[1]', 'int') as [CodeSB]
--				,T.c.value('./V020_STRUCTURE_BED[1]', 'int') as [V020StructureBed]
--				,T.c.value('./LPU[1]', 'varchar(6)') as [Lpu]
--				,T.c.value('./DATE_BEGIN[1]', 'datetime') as [DateBegin]
--				,T.c.value('./DATE_END[1]', 'datetime') as [DateEnd] 
--				,T.c.value('./FEMALE_BED[1]', 'int') as [FemaleBed]
--				,T.c.value('./MALE_BED[1]', 'int') as [MaleBed] 
--				,T.c.value('./BEDS_COUNT[1]', 'int') as [BedsCount]
--				,T.c.value('./REPAIR_BED[1]', 'int') as [RepairBed]
--				,T.c.value('./ID[1]', 'uniqueidentifier') as [ID]
--				,T.c.value('./DLSB[1]', 'uniqueidentifier') as [Dlsb]
--				,T.c.value('./PLANNED_BEDS_COUNT[1]', 'int') as [PlannedBedsCount] 
--				,T.c.value('./PLANNED_MALE_BED[1]', 'int') as [PlannedMaleBed]
--				,T.c.value('./PLANNED_FEMALE_BED[1]', 'int') as [PlannedFemaleBed]
--			INTO #AMOUNT_BED
--		from @Xml.nodes('LPU/BRANCH/DIVISION/DIVISION_LINK_STRUCTURE_BED/AMOUNT_BED') T(c)

--		-- INSERT [RDS].T_DIVISION_LINK_STRUCTURE_BED
--		INSERT INTO [RDS].[T_DIVISION_LINK_STRUCTURE_BED]
--		(DivisionLinkStructureBedID ,[Division],[StructureBed], [ID],V020StructureBed)
--		SELECT
--					newID()
--					,d.DivisionID
--					,dsl.CodeSB
--					,dsl.ID
--					,dsl.V020StructureBed
--		from #DIVISION_LINK_STRUCTURE_BED dsl
--		join [RDS].T_DIVISION d on (d.ID = dsl.Div or d.DivisionID = dsl.Div)
--		where d.IsActive = 1 and not exists (select * from [RDS].T_DIVISION_LINK_STRUCTURE_BED dd where dd.ID = dsl.ID or dd.ID = dd.DivisionLinkStructureBedID) 
  
--		-- update [RDS].T_AMOUNT_BED
--		update ab
--		set
--			ab.[DateEnd] = am.DateEnd
--			,ab.[FemaleBed] = ISNULL(am.FemaleBed, 0)
--			,ab.[MaleBed] = ISNULL(am.MaleBed, 0)
--			,ab.[BedsCount] = ISNULL(am.BedsCount, 0)
--			,ab.[RepairBed] = ISNULL(am.RepairBed, 0)
--			,ab.[PlannedFemaleBed] = ISNULL(am.[PlannedFemaleBed], 0)
--			,ab.[PlannedMaleBed] =  ISNULL(am.[PlannedMaleBed], 0)
--			,ab.[PlannedBedsCount] = ISNULL(am.[PlannedBedsCount], 0)
--			,ab.DivisionLinkStructureBed = (select top 1 dl.DivisionLinkStructureBedID from [RDS].T_DIVISION_LINK_STRUCTURE_BED dl where dl.DivisionLinkStructureBedID = am.Dlsb or dl.ID = am.Dlsb)
--		from [RDS].T_AMOUNT_BED ab
--		join #AMOUNT_BED am on (am.ID = ab.ID  or am.ID = ab.[AmountBedID])

--		update ab
--		set ab.DateEnd = DATEADD(DAY,-1, Getdate())
--		from [RDS].T_AMOUNT_BED ab
--		Join [RDS].[T_DIVISION_LINK_STRUCTURE_BED] dlsb on dlsb.DivisionLinkStructureBedID = ab.DivisionLinkStructureBed
--		join [RDS].T_DIVISION d on dlsb.Division =  d.DivisionID
--		join [RDS].T_BRANCH b on b.BranchID = d.Branch
--		join #AMOUNT_BED abb on abb.[Lpu] = b.[LpuMain]
--		where not exists (select * from #AMOUNT_BED abi where abi.[ID] = ab.ID or abi.[ID] = ab.AmountBedID)

--		-- INSERT [RDS].T_AMOUNT_BED
--		INSERT INTO [RDS].[T_AMOUNT_BED]
--		([AmountBedID], [DateBegin], [DateEnd], [DivisionLinkStructureBed], [FemaleBed], [MaleBed], [BedsCount], [RepairBed], [ID], [PlannedFemaleBed], [PlannedMaleBed], [PlannedBedsCount])
--		SELECT
--				    newID()
--				    ,ab.[DateBegin]
--				    ,ab.[DateEnd]
--				    ,dlsb.DivisionLinkStructureBedID
--				    ,ISNULL(ab.[FemaleBed], 0)
--				    ,ISNULL(ab.[MaleBed], 0)
--				    ,ISNULL(ab.[BedsCount], 0)
--				    ,ISNULL(ab.[RepairBed], 0)
--					,ab.ID
--					,ISNULL(ab.[PlannedFemaleBed], 0)
--					,ISNULL(ab.[PlannedMaleBed], 0)
--					,ISNULL(ab.[PlannedBedsCount], 0)
--		from #AMOUNT_BED ab
--		join [RDS].T_DIVISION_LINK_STRUCTURE_BED dlsb on (dlsb.ID = ab.Dlsb or dlsb.DivisionLinkStructureBedID = ab.Dlsb)
--		join [RDS].T_DIVISION d on (d.ID = dlsb.Division or d.DivisionID = dlsb.Division)
--		where d.IsActive = 1 and not exists (
--			select * 
--			from [RDS].[T_AMOUNT_BED] ab1 
--			where (ab1.ID = ab.ID  or ab.ID = ab1.[AmountBedID])
--		)

--		-- Drop table
		
--		DROP TABLE #BRANCH
--		DROP TABLE #DIVISION
--		DROP TABLE #DIVISION_LINK_STRUCTURE_BED
--		DROP TABLE #AMOUNT_BED

--		END
--END
--GO