if OBJECT_ID('sp_RDS_ReferralXml', 'P') is not null
	drop procedure sp_RDS_ReferralXml
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_ReferralXml]
--	@LpuCode varchar(255) = null,
--	@Xml xml = null,
--	@IsNew bit = 1,
--	@ID uniqueidentifier = null,
--	@LpuTo varchar(255) = null,
--	@FilesXml varchar(max) = 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 not exists (select * from AKUZ.T_HOSPITALIZATION_REFERRAL hr where hr.MedicalRecordID = @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('HOSPITALISATION_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), ref.ReferralDate, 20) as 'body/REFERRAL_DATE'
--		,CASE hr.HospitalizationType
--			WHEN 13001 THEN 0
--			WHEN 13002 THEN 1
--			WHEN 13003 THEN 2
--            ELSE 0 END			as 'body/HOSPITALISATION_TYPE'
--        ,brT.BranchCode			as 'body/BRANCH_TO'
--        ,dT.Code				as 'body/DIVISION_TO'
--        ,v0.Id					as 'body/STRUCTURE_BED'
--        ,sb.Code                as 'body/V020_STRUCTURE_BED'
--        ,br.BranchCode			as 'body/BRANCH_FROM'
--        ,ref.MkbCode			as 'body/MKB'
--        ,ref.Diagnosis			as 'body/DIAGNOSIS'
--		,convert(varchar(10), hr.PreHospitalDate, 20) as 'body/PLANNED_DATE'
--		,convert(varchar(10), hr.HospitalisationDate, 20) as 'body/HOSPITALISATION_DATE'
--        ,p.Code					as 'body/DOC_CODE'
--        ,pol.PolicyForm			as 'body/POLICY_TYPE'
--        ,pol.Serial				as 'body/POLIS_SERIAL'
--        ,pol.Number				as 'body/POLIS_NUMBER'
--		,ISNULL(i1.SmoCod, i2.SmoCod) as 'body/SMO'
--        ,pat.FirstName			as 'body/FIRST_NAME'
--        ,pat.LastName			as 'body/LAST_NAME'
--        ,pat.FatherName			as 'body/FATHER_NAME'
--        ,pat.Sex				as 'body/SEX'
--		,convert(varchar(10), pat.Birthday, 20) as 'body/BIRTHDAY'
--        ,hr.Phone				as 'body/PHONE'
--        ,pat.PatientID			as 'body/PATIENT'
--        ,ISNULL(hr.AnotherRegion, 0) as 'body/ANOTHER_REGION'
--        ,Cast(@FilesXml as xml)            as 'body/FILES'
--				,	(
--						select
--							r.HospitalisationRefferalLinkResearchID				as 'HospitalisationRefferalLinkResearchID'
--						,	ms.Code												as 'MEDICAL_SERVICE_CODE'
--						,	ms.Name												as 'MEDICAL_SERVICE_NAME'
--						,	convert(varchar(10), r.PlannedDate, 20)				as 'PLANNED_DATE'
--						,	r.HospitalizationReferral							as 'HospitalizationReferralId'
--						from AKUZ.T_HOSPITALISATION_REFFERAL_LINK_RESEARCH r
--						join AKUZ.T_MEDICAL_SERVICE ms on ms.MedicalServiceID = r.MedicalService
--						where hr.MedicalRecordID = r.HospitalizationReferral
--						for xml path('HOSPITALISATION_REFFERAL_LINK_RESEARCHES')
--						, type
--					)
--		from [AKUZ].T_HOSPITALIZATION_REFERRAL hr 
--		join [AKUZ].T_STRUCTURE_BED sb on sb.StructureBedID = hr.StructureBed
--		join [AKUZ].T_V002_PROFILE v0 on v0.Id = sb.V002Profile
--		join [AKUZ].T_REFERRAL ref on ref.MedicalRecordID = hr.MedicalRecordID
--		--to
--		left join [AKUZ].T_BRANCH brT on brT.LpuID = hr.Branch
--		left join [AKUZ].T_DIVISION dT on dT.DivisionID = ref.Division
--		--from
--		join [AKUZ].T_MEDICAL_RECORD mr on mr.MedicalRecordID = ref.MedicalRecordID
--		join [AKUZ].T_POST p on p.ResourceID = mr.Post
--		join [AKUZ].T_RESOURCE r on r.ResourceID = p.ResourceID
--		join [AKUZ].T_DIVISION d on d.DivisionID = r.Division
--		left join [AKUZ].T_BRANCH br on br.LpuID = d.Branch
--		--patient
--		--join [AKUZ].T_AMBULANCE_RECORD ar on ar.MedicalRecordID = hr.MedicalRecordID
--		join [AKUZ].T_AMBULANCE_CARD_RECORD acr on acr.MedicalRecordID = hr.MedicalRecordID
--		join [AKUZ].T_EVENT_BASE eb on eb.EventID = acr.AmbulanceEvent
--		join [AKUZ].T_AMBULANCE_CARD ac on ac.AmbulanceCardID = eb.AmbulanceCard
--		join [AKUZ].T_PATIENT pat on pat.PatientID = ac.Patient
--		--polis
--		join [AKUZ].T_POLICY pol on pol.PolicyID = [dbo].[fn_GetOmsPolicy](pat.PatientID, ref.ReferralDate)
--		left join [AKUZ].T_INSURANCE_COMPANY ic on ic.OrganizationID = pol.InsuranceCompany
--		left join [AKUZ].T_INSURER i1 on i1.InsurerID = ic.Insurer
--		left join [AKUZ].T_INSURER i2 on i2.InsurerID = pol.Insurer
--		where hr.MedicalRecordID = @ID
--		FOR XML PATH(''),
--		ROOT('HOSPITALISATION_REFERRAL')

--		) 
--		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)
--		select @OperationType = (select T.c.value('./header[1]/TYPE[1]', 'varchar(max)') from @Xml.nodes('HOSPITALISATION_REFERRAL') T(c))
--		if(@OperationType = 'Reserve')
--		BEGIN
--		-- Generate table #HOSPITALIZATION_REFERRAL
--		select
--				T.c.value('./header[1]/LPU[1]', 'varchar(6)') as [LpuFrom]
--				,T.c.value('./header[1]/ID[1]', 'uniqueidentifier') as [ID]
--				,T.c.value('./header[1]/LPU_TO[1]', 'varchar(6)') as [LpuTo]
				
--				,T.c.value('./body[1]/RESULT[1]', 'int') as [Result]
--				,T.c.value('./body[1]/DESCRIPTION[1]', 'varchar(max)') as [Descr]
--			INTO #RESERVE
--		from @Xml.nodes('HOSPITALISATION_REFERRAL') T(c)

--		update hr
--		set hr.[Status] = (CASE r.[Result] 
--							WHEN 1 THEN  3
--							WHEN 2 THEN  2
--							ELSE 2 END), hr.ErrorReserv = r.[Descr]
--		from [AKUZ].t_HOSPITALIZATION_REFERRAL hr
--		join #RESERVE r on r.ID = hr.MedicalRecordId

--		END
--		ELSE
--		BEGIN
--		-- Generate table #HOSPITALIZATION_REFERRAL 
--		select
--				T.c.value('./header[1]/TYPE[1]', 'varchar(max)') as [OperationType]
--				,T.c.value('./header[1]/LPU[1]', 'varchar(6)') as [LpuFrom]
--				,T.c.value('./header[1]/ID[1]', 'uniqueidentifier') as [ID]
--				,T.c.value('./header[1]/LPU_TO[1]', 'varchar(6)') as [LpuTo]

--				,T.c.value('./body[1]/REFERRAL_NUMBER[1]', 'varchar(16)') as [ReferralNumber]
--				,T.c.value('./body[1]/REFERRAL_DATE[1]', 'datetime') as [ReferralDate]
--				,T.c.value('./body[1]/HOSPITALISATION_DATE[1]', 'datetime') as [HospDate]
--				,T.c.value('./body[1]/HOSPITALISATION_TYPE[1]', 'int') as [HospitalizationType]
--				,T.c.value('./body[1]/BRANCH_TO[1]', 'varchar(16)') as [BranchTo] 
--				,T.c.value('./body[1]/DIVISION_TO[1]', 'varchar(16)') as [DivivsionTo]
--				,T.c.value('./body[1]/BRANCH_FROM[1]', 'varchar(16)') as [BranchFrom]
--				,T.c.value('./body[1]/MKB[1]', 'varchar(5)') as [MkbCode]
--				,T.c.value('./body[1]/PLANNED_DATE[1]', 'datetime') as [PlannedDate]
--				,T.c.value('./body[1]/DOC_CODE[1]', 'varchar(100)') as [DocCode]
--				,T.c.value('./body[1]/POLICY_TYPE[1]', 'int') as [PolicyType]
--				,T.c.value('./body[1]/POLIS_SERIAL[1]', 'varchar(16)') as [PolisSerial]
--				,T.c.value('./body[1]/POLIS_NUMBER[1]', 'varchar(16)') as [PolisNumber]
--				,T.c.value('./body[1]/SMO[1]', 'varchar(5)') as [Smo]
--				,T.c.value('./body[1]/FIRST_NAME[1]', 'varchar(32)') as [FirstName]
--				,T.c.value('./body[1]/LAST_NAME[1]', 'varchar(32)') as [LastName]
--				,T.c.value('./body[1]/FATHER_NAME[1]', 'varchar(32)') as [FatherName]
--				,T.c.value('./body[1]/SEX[1]', 'int') as [Sex]
--				,T.c.value('./body[1]/BIRTHDAY[1]', 'datetime') as [Birthday]
--				,T.c.value('./body[1]/PHONE[1]', 'varchar(128)') as [Phone]
--				,T.c.value('./body[1]/DIAGNOSIS[1]', 'varchar(max)') as [Diagnosis]
--				,T.c.value('./body[1]/PATIENT[1]', 'uniqueidentifier') as [Patient]
--				,T.c.value('./body[1]/ANOTHER_REGION[1]', 'bit') as [AnotherRegion]
--				,T.c.value('./body[1]/STRUCTURE_BED[1]', 'int') as [StructureBed]
--				,T.c.value('./body[1]/V020_STRUCTURE_BED[1]', 'int') as [V020StructureBed]
--			INTO #HOSPITALIZATION_REFERRAL
--		from @Xml.nodes('HOSPITALISATION_REFERRAL') T(c)

--		-- Generate table #HOSPITALISATION_REFFERAL_LINK_RESEARCHES
--        select t.c.value('./HospitalizationReferralId[1]', 'uniqueidentifier')		as [HospitalizationReferralId]
--			  ,t.c.value('./MEDICAL_SERVICE_CODE[1]', 'varchar(64)')				as [Code]
--			  ,t.c.value('./MEDICAL_SERVICE_NAME[1]', 'varchar(256)')				as [Name]
--			  ,t.c.value('./PLANNED_DATE[1]', 'datetime')							as [PlannetDate]
--		INTO #HOSPITALISATION_REFFERAL_LINK_RESEARCHES
--        from @Xml.nodes('HOSPITALISATION_REFERRAL/HOSPITALISATION_REFFERAL_LINK_RESEARCHES') T(c)
		
--		if(@OperationType <> 'Delete')
--		BEGIN	
		
--		declare @refid uniqueidentifier
--		select @refid = newid()

--		 --INSERT RDS_HOSPITALIZATION_REFERRAL
--		INSERT INTO [AKUZ].[T_RDS_HOSPITALIZATION_REFERRAL]
--		(	[RdsHospitalizationReferralID]
--		   ,[ReferralNumber] 
--           ,[ReferralDate] 
--           ,[HospitalizationType] 
--           ,[Division] 
--           ,[BranchFrom]
--           ,[PolicyForm]
--           ,[PolisSerial]
--           ,[PolisNumber]
--           ,[Insurer]
--           ,[FirstName] 
--           ,[LastName] 
--           ,[FatherName] 
--           ,[Sex] 
--           ,[Birthday] 
--           ,[Phone] 
--           ,[MkbCode]
--           ,[StructureBed]
--           ,[PlannedDate] 
--           ,[DocCode]
--           ,[PatientID]
--		   ,[Diagnosis]
--		   ,[ID]
--		   ,[HospitalisationDate]
--		   ,[AnotherRegion]
--		   ,LpuMainFrom)
--		SELECT
--			@refid
--		   ,hr.[ReferralNumber] 
--           ,hr.[ReferralDate] 
--		   ,(CASE hr.[HospitalizationType]
--			WHEN 0 THEN 13001
--			WHEN 1 THEN 13002
--			WHEN 2 THEN 13003
--			ELSE 13001 END) 
--           ,dTo.DivisionID 
--           ,bF.LpuID
--           ,hr.[PolicyType]
--           ,hr.[PolisSerial]
--           ,hr.[PolisNumber]
--           ,i.InsurerID
--           ,hr.[FirstName] 
--           ,hr.[LastName] 
--           ,hr.[FatherName] 
--           ,hr.[Sex] 
--           ,hr.[Birthday] 
--           ,hr.[Phone] 
--           ,hr.[MkbCode]
--			,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 = hr.[StructureBed])
--				 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 = hr.[StructureBed]
--															and sb.Code = hr.[V020StructureBed] 
--															and sb.DateBegin <= hr.[ReferralDate]
--															and (sb.DateEnd is null or
--																	sb.DateEnd >= hr.[ReferralDate])) end 
--           ,hr.[PlannedDate] 
--           ,hr.[DocCode]
--           ,hr.[Patient]
--		   ,hr.Diagnosis
--		   ,hr.ID
--		   ,hr.[HospDate]
--		   ,case when hr.[AnotherRegion] = '1' then 1
--				when hr.[AnotherRegion] = 'true' then 1
--				else 0
--			end
--			,lpFrom.LpuId
--		from #HOSPITALIZATION_REFERRAL hr
--		left join [AKUZ].T_LPU_MAIN lpTo on lpTo.LpuCode = hr.LpuTo 
--		left join [AKUZ].T_BRANCH bTo on bTo.LpuMain = lpTo.LpuID and bTo.BranchCode = hr.BranchTo
--		left join [AKUZ].T_DIVISION dTo on dTo.Branch = bTo.LpuID and dTo.Code = hr.DivivsionTo

--		left join [AKUZ].T_LPU_MAIN lpFrom on lpFrom.LpuCode = hr.LpuFrom
--		left join [AKUZ].T_BRANCH bF on bF.LpuMain = lpFrom.LpuID and bF.BranchCode = hr.BranchFrom

--		left join [AKUZ].T_INSURER i on i.SmoCod = hr.Smo
--		where 
--		not exists (
--			select * 
--			from [AKUZ].[T_RDS_HOSPITALIZATION_REFERRAL] hr1
--			where hr1.[ID] = hr.ID
--		)

--		INSERT INTO [AKUZ].[T_RDS_HOSP_REF_LINK_RESEARCH]
--           ([RdsHospRefLinkResearchID]
--           ,[RdsHospitalizationReferral]
--           ,[MedicalService]
--           ,[PlannedDate])
--			select newid(),
--				  @refid,
--				  (select top 1 ms.MedicalServiceID from AKUZ.T_MEDICAL_SERVICE ms where ms.Code = r.Code),
--				  r.[PlannetDate]
--			from #HOSPITALISATION_REFFERAL_LINK_RESEARCHES r

--		END
--		ELSE
--		BEGIN
--			delete hr
--			from [AKUZ].[T_RDS_HOSPITALIZATION_REFERRAL] hr
--			join #HOSPITALIZATION_REFERRAL r on r.[ID] = hr.ID
--		END

--		-- Drop table
--		DROP TABLE #HOSPITALIZATION_REFERRAL

--		END
--END