IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_RDS_DictionaryXml]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[sp_RDS_DictionaryXml]
GO
--SET ANSI_NULLS ON
--GO
--SET QUOTED_IDENTIFIER ON
--GO
---- =============================================
---- Author:		Vitacore (с)
---- Create date: 2014-03-01
---- Description:	Выгрузка/загрузка справочников в(из) RDS 
---- =============================================
--CREATE PROCEDURE [dbo].[sp_RDS_DictionaryXml]
--	@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.[LpuCode]								as 'LPU'
--					,br.[LpuType]								as 'LPU_TYPE'
--					,br.[Category]								as 'CATEGORY'
--					,br.[LpuID]									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'
--						,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
--						ELSE 0 END			as 'DIVISION_TYPE'
--						,d.[RevenueType]	as 'REVENUE_TYPE'
--						,lp.[LpuCode]		as 'LPU'
--						,d.[DivisionID]		as 'ID'
--						,d.[IDNumberCard]   as 'ID_NUMBER_CARD'
--					    ,d.[IsActive]								 as 'ACTIVE'
--						--собираем DIVISION_LINK_STRUCTURE_BED
--						, (
--						select 
--							v0.Id								as 'STRUCTURE_BED'
--							,sb.Code								as 'V020_STRUCTURE_BED'
--							,lp.[LpuCode]						as 'LPU' 
--							,dlsb.[DivisionLinkStructureBedID]	as 'ID'
--							,dlsb.Division						as 'DIVISION_ID'
--							--собираем AMOUNT_BED
--							, (
--							select 
--								sb.Code									as 'V020_STRUCTURE_BED'
--								,v0.Id								as 'STRUCTURE_BED'
--								,lp.[LpuCode]								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 [AKUZ].T_AMOUNT_BED ab 
--							where dlsb.DivisionLinkStructureBedID = ab.DivisionLinkStructureBed 
--							FOR XML PATH('AMOUNT_BED'),
--							TYPE
--						)
--						from [AKUZ].T_DIVISION_LINK_STRUCTURE_BED dlsb
--						join [AKUZ].T_STRUCTURE_BED sb on sb.StructureBedID = dlsb.StructureBed 
--						join [AKUZ].T_V002_PROFILE v0 on v0.Id = sb.V002Profile
--						where dlsb.Division = d.DivisionID
--						FOR XML PATH('DIVISION_LINK_STRUCTURE_BED'),
--						TYPE
--					)
--					FROM [AKUZ].T_DIVISION d
--					where d.Branch = br.LpuID
--					FOR XML PATH('DIVISION'),
--					TYPE
--					)
--			FROM [AKUZ].T_BRANCH br
--			join [AKUZ].T_LPU_MAIN lp on lp.LpuID = br.LpuMain
--			join [AKUZ].T_LPU_SETTINGS ls on ls.LpuMain = lp.LpuID
--			where lp.LpuCode is not null
--			and lp.LpuID = @ID  
--			FOR XML PATH('BRANCH'),
--			TYPE
--			)
--			FOR XML PATH(''),
--			ROOT('LPU')
--	) 

--	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 #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 [AKUZ].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]
--		from [AKUZ].T_BRANCH br
--		join #BRANCH b on b.[ID] = br.OtherID

--		update br
--		set br.IsActive = 0
--		from [AKUZ].T_BRANCH br
--		join [AKUZ].T_LPU_MAIN lp on lp.LpuID = br.LpuMain
--		join #BRANCH bb on bb.[LpuMain] = lp.LpuCode
--		where not exists (select * from #BRANCH b where b.[ID] = br.OtherID)

--		-- INSERT [AKUZ].T_BRANCH
--		INSERT INTO [AKUZ].[T_BRANCH] 
--		([LpuID],[BranchCode], [Name], [DateAdd], [DateRemove], [AddressString], [NameFull], [RegistryPhones], [LpuMain], [LpuType], [Category], [OtherID], [IsActive], [BranchSettings])
--		SELECT
--					newID()
--					,b.[BranchCode]
--					,b.[Name]
--					,b.[DateAdd]
--					,b.[DateRemove]
--					,b.[AddressString]
--					,b.[NameFull]
--					,b.[RegistryPhones]
--					, (select lpu.[LpuID] from [AKUZ].T_LPU_MAIN lpu where lpu.[LpuCode] = b.[LpuMain])
--					,b.[LpuType]
--					,b.[Category]
--					,b.ID
--					,b.[Active]
--					,0
--		from #BRANCH b
--		where not exists (
--			select * 
--			from [AKUZ].T_BRANCH b1 
--			where b.ID = b1.OtherID
--			)

--		-- Drop table
--		DROP TABLE #BRANCH

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

--		update dd
--		set dd.IsActive = 0
--		from [AKUZ].T_DIVISION dd
--		join [AKUZ].T_BRANCH b on b.LpuID = dd.Branch
--		join [AKUZ].T_LPU_MAIN lp on lp.LpuID = b.LpuMain
--		join #DIVISION ddd on ddd.[Lpu] = lp.[LpuCode]
--		where not exists (select * from #DIVISION div where dd.[OtherID] = div.ID)

--		-- INSERT [AKUZ].T_DIVISION
--		INSERT INTO [AKUZ].[T_DIVISION] 
--		([DivisionID], [Code], [Name], [FullName], [Level], [CodeMz], [Nofilet], [Adress], [Branch], [DivisionType], [RevenueType], [IsRequestonDivision], [IsRequestInHospital], [OtherID], IDNumberCard, [WorkDays], [IsActive], [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.ID
--			,d.[IDNumberCard]
--			,31
--			,d.[Active]
--			,'20000101 00:00:00'
--		from #DIVISION d
--		where not exists (
--			select * 
--			from [AKUZ].T_DIVISION d1 
--			where d.ID = d1.OtherID
--		)

--		-- Drop table
--		DROP TABLE #DIVISION

--		-- 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('./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]
--				,T.c.value('./DLSB[1]', 'uniqueidentifier') as [Dlsb]
--			INTO #AMOUNT_BED
--		from @Xml.nodes('LPU/BRANCH/DIVISION/DIVISION_LINK_STRUCTURE_BED/AMOUNT_BED') T(c)

		
--		-- INSERT [AKUZ].T_DIVISION_LINK_STRUCTURE_BED
--		INSERT INTO [AKUZ].[T_DIVISION_LINK_STRUCTURE_BED]
--		([DivisionLinkStructureBedID] ,[Division],[StructureBed], [OtherID])
--		SELECT
--					newID()
--					,d.DivisionID
--					,case when V020StructureBed = null then (select TOP 1 sb.StructureBedID from [AKUZ].T_STRUCTURE_BED sb
--																join [AKUZ].T_V002_PROFILE v0 on v0.Id = sb.V002Profile
--																	where v0.Id = dsl.[CodeSB])
--						 else (select TOP 1 sb.StructureBedID from [AKUZ].T_STRUCTURE_BED sb
--																join [AKUZ].T_V002_PROFILE v0 on v0.Id = sb.V002Profile
--																	where v0.Id = dsl.[CodeSB]
--																	and sb.Code = dsl.[V020StructureBed] 
--																	and sb.DateBegin <= GETDATE()
--																	and (sb.DateEnd is null or
--																			sb.DateEnd >= GETDATE())) end 
--					,dsl.ID
--		from #DIVISION_LINK_STRUCTURE_BED dsl
--		join [AKUZ].T_DIVISION d on d.OtherID = dsl.Div
--		where d.IsActive = 1 and not exists (select * from [AKUZ].T_DIVISION_LINK_STRUCTURE_BED dl where dl.OtherID = dsl.ID)
		
--		-- update [AKUZ].T_AMOUNT_BED
--		update ab
--		set
--			ab.[DateEnd] = am.DateEnd
--			,ab.[FemaleBed] = am.FemaleBed
--			,ab.[MaleBed] = am.MaleBed
--			,ab.[BedsCount] = am.BedsCount
--			,ab.[RepairBed] = am.RepairBed			
--			,ab.[PlannedFemaleBed] = ISNULL(am.[PlannedFemaleBed], 0)
--			,ab.[PlannedMaleBed] =  ISNULL(am.[PlannedMaleBed], 0)
--			,ab.[PlannedBedsCount] = ISNULL(am.[PlannedBedsCount], 0)
--		from [AKUZ].T_AMOUNT_BED ab
--		join #AMOUNT_BED am on am.[ID] = ab.OtherID

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

--		-- INSERT [AKUZ].T_AMOUNT_BED
--		INSERT INTO [AKUZ].[T_AMOUNT_BED]
--		([AmountBedID], [DateBegin], [DateEnd], [DivisionLinkStructureBed], [FemaleBed], [MaleBed], [BedsCount], [RepairBed], [OtherID],[PlannedFemaleBed], [PlannedMaleBed], [PlannedBedsCount])
--		SELECT
--				    newID()
--				    ,ab.[DateBegin]
--				    ,ab.[DateEnd]
--				    ,(select dlsb.DivisionLinkStructureBedID from [AKUZ].T_DIVISION_LINK_STRUCTURE_BED dlsb where dlsb.OtherId = ab.[Dlsb])
--				    ,ab.[FemaleBed]
--				    ,ab.[MaleBed]
--				    ,ab.[BedsCount]
--				    ,ab.[RepairBed]
--					,ab.[ID]
--					,ISNULL(ab.[PlannedFemaleBed], 0)
--					,ISNULL(ab.[PlannedMaleBed], 0)
--					,ISNULL(ab.[PlannedBedsCount], 0)
--		from #AMOUNT_BED ab
--		where not exists (
--			select * 
--			from [AKUZ].[T_AMOUNT_BED] ab1 
--			where ab.ID = ab1.OtherID
--		)

--		-- Drop table
--		DROP TABLE #DIVISION_LINK_STRUCTURE_BED
--		DROP TABLE #AMOUNT_BED
--		END
--END
--GO