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

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

--	SET NOCOUNT ON;
--	IF (@Xml is null)
--	BEGIN
--		--выясняем тип операции
--		IF(@IsNew = 1)
--		BEGIN
--			select @OperationType = 'Insert'
--		END
--		ELSE IF (@IsNew = 0 and not exists (select * from [RDS].T_HOSPITALIZATION_REFERRAL hr where hr.HospitalizationReferralID = @ID))
--		BEGIN
--			select @OperationType = 'Delete'
--				select @Result = (select 
--				@LpuCode				as 'header/LPU'
--				,@OperationType			as 'header/TYPE'
--				,@ID					as 'header/ID'
--				,@LpuTo					as 'header/LPU_TO'
--				FOR XML PATH(''),
--				ROOT('HOSPITASISATION_REFERRAL')
--			) 
--		END

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

--	IF(@OperationType = 'Insert')
--		BEGIN

--		SELECT @Result = ( 
--		select 
--		@LpuCode			as 'header/LPU'
--		,@OperationType		as 'header/TYPE'
--		,@ID				as 'header/ID'
--		,@LpuTo				as 'header/LPU_TO'

--		,hr.ReferralNumber	as 'body/REFERRAL_NUMBER'
--		,convert(varchar(10), hr.ReferralDate, 20) as 'body/REFERRAL_DATE'
--		,convert(varchar(10), hr.HospitalisationDate, 20) as 'body/HOSPITALISATION_DATE'
--		,hr.HospitalizationType as 'body/HOSPITALISATION_TYPE'
--		,brT.BranchCode		as 'body/BRANCH_TO'
--		,dT.Code			as 'body/DIVISION_TO'
--		,sb.Code			as 'body/STRUCTURE_BED'
--		,sbb.IDK_PR			as 'body/V020_STRUCTURE_BED'
--		,br.BranchCode		as 'body/BRANCH_FROM'
--		,hr.MkbCode			as 'body/MKB'
--		,hr.Diagnosis		as 'body/DIAGNOSIS'
--		,convert(varchar(10), hr.PlannedDate, 20) as 'body/PLANNED_DATE'
--		,hr.DocCode			as 'body/DOC_CODE'
--		,hr.PolicyForm		as 'body/POLICY_TYPE'
--		,hr.PolisSerial		as 'body/POLIS_SERIAL'
--		,hr.PolisNumber		as 'body/POLIS_NUMBER'
--		,hr.F002Smo			as 'body/SMO'
--		,hr.FirstName		as 'body/FIRST_NAME'
--		,hr.LastName		as 'body/LAST_NAME'
--		,hr.FatherName		as 'body/FATHER_NAME'
--		,hr.Sex				as 'body/SEX'
--		,hr.AnotherRegion	as 'body/ANOTHER_REGION'
--		,convert(varchar(10), hr.Birthday, 20) as 'body/BIRTHDAY'
--		,hr.Phone			as 'body/PHONE'
--		,hr.PatientID		as 'body/PATIENT'
--				,(
--					select
--						r.HospRefResearchID						as 'HospRefResearchID'
--					,	r.Code									as 'MEDICAL_SERVICE_CODE'
--					,	r.Name									as 'MEDICAL_SERVICE_NAME'
--					,	convert(varchar(10), r.PlanetDate, 20) as 'PLANNED_DATE'
--					from RDS.T_HOSP_REF_RESEARCH r
--					where hr.HospitalizationReferralID = r.HospitalizationReferral
--					for xml path('HOSPITALISATION_REFFERAL_LINK_RESEARCH'), type
--				)
				
--		from [RDS].T_HOSPITALIZATION_REFERRAL hr 
--		join [RDS].T_STRUCTURE_BED sb on sb.Code = hr.StructureBed
--		left join [RDS].T_V020_STRUCTURE_BED sbb on sbb.IDK_PR = hr.V020StructureBed
--		--to
--		left join [RDS].T_DIVISION dT on dT.DivisionID = hr.DivisionTo
--		left join [RDS].T_BRANCH brT on brT.BranchID = dT.Branch
--		--from
--		join [RDS].T_BRANCH br on br.BranchID = hr.BranchFrom
--		where hr.HospitalizationReferralID = @ID
--		FOR XML PATH(''),
--		ROOT('HOSPITALISATION_REFERRAL')
--		) 
--		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 
--			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 = @LpuTo and s1.SyncServersID = s.SyncServersID)
--END
--END
--GO