if OBJECT_ID('sp_RDS_CancelReferralXml', 'P') is not null
	drop procedure sp_RDS_CancelReferralXml
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_CancelReferralXml]
--	@LpuCode varchar(255) = null,
--	@Xml xml = null,
--	@IsNew bit = 1,
--	@ID uniqueidentifier = null,
--	@LpuTo varchar(255) = 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_CANCEL_HOSPITALIZATION_REFERRAL chr  where chr.CancelHospitalizationReferralID = @ID))
--		BEGIN
--			select @OperationType = 'Update'
--		END
--		ELSE IF (@IsNew = 0 and not exists (select * from [AKUZ].T_CANCEL_HOSPITALIZATION_REFERRAL chr where chr.CancelHospitalizationReferralID = @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('CANCEL_HOSPITALISATION_REFERRAL')
--			) 
--		END

--select @ContentEncoding = 'utf-8'
--   ,@Type = 'CANCEL_HOSPITALISATION_REFERRAL'
--   ,@OwnerID = @ID
--		IF(@OperationType <> 'Delete')
--		BEGIN

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

--           ,chr.[ReasonCancel]								as 'body/REASON'
--           ,chr.[CancellationSource]						as 'body/CANCEL_SOURSE'
--           ,ISNULL(rhr.PatientID, p.PatientID)				as 'body/PATIENT'
--           ,ISNULL(hr.ReferralNumber, rhr.ReferralNumber)	as 'body/REFERRAL_NUMBER'
--		   ,ISNULL(convert(varchar(10), ref.ReferralDate, 20), convert(varchar(10), rhr.ReferralDate, 20)) as 'body/DATE'
--           ,branch.BranchCode								as 'body/BRANCH'
--		   ,ISNULL(lpFrom1.LpuCode, lpFrom.LpuCode)			as 'body/REFERRAL_LPU'
--		   ,convert(varchar(10), chr.DateCancel, 20)		as 'body/DATE_CANCEL'
--		   ,0												as 'body/CANCEL_TYPE'
--		   ,chr.Descr										as 'body/CANCEL_DESCRIPTION'
--		from [AKUZ].T_CANCEL_HOSPITALIZATION_REFERRAL chr
--		left join [AKUZ].T_RDS_HOSPITALIZATION_REFERRAL rhr on rhr.RdsHospitalizationReferralID = chr.RdsHospitalizationReferral
--		left join [AKUZ].T_BRANCH brFr on brFr.LpuID = rhr.BranchFrom		
--		left join [AKUZ].T_LPU_MAIN lpFrom1 on lpFrom1.LpuID = brFr.LpuMain
--		left join [AKUZ].T_HOSPITALIZATION_REFERRAL hr on  hr.MedicalRecordID = chr.HospitalizationReferral
--		left join [AKUZ].T_REFERRAL ref on ref.MedicalRecordID = hr.MedicalRecordID
--		--left join [AKUZ].T_AMBULANCE_RECORD ar on ar.MedicalRecordID = hr.MedicalRecordID
--		left join [AKUZ].T_AMBULANCE_CARD_RECORD acr on acr.MedicalRecordID = hr.MedicalRecordID
--		left join [AKUZ].T_EVENT_BASE eb on eb.EventID = acr.AmbulanceEvent
--		left join [AKUZ].T_AMBULANCE_CARD ac on ac.AmbulanceCardID = eb.AmbulanceCard
--		left join [AKUZ].T_PATIENT p on p.PatientID = ac.Patient
--		left join [AKUZ].T_MEDICAL_RECORD mr on mr.MedicalRecordID = ref.MedicalRecordID
--		left join [AKUZ].T_POST post on post.ResourceID = mr.Post
--		left join [AKUZ].T_RESOURCE r on r.ResourceID = post.ResourceID
--		left join [AKUZ].T_DIVISION d on d.DivisionID = r.Division
--		left join [AKUZ].T_BRANCH br on br.LpuID = d.Branch
--		left join [AKUZ].T_LPU_MAIN lpFrom on lpFrom.LpuID = br.LpuMain
--		join [AKUZ].T_USERS u on u.UserID = chr.Users
--		join [AKUZ].T_BRANCH branch on branch.LpuID = u.Branch
--		where chr.CancelHospitalizationReferralID = @ID
--		FOR XML PATH(''),
--		ROOT('CANCEL_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)
--		BEGIN
--		-- Generate table #CANCEL_HOSPITALIZATION_REFERRAL
--		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]/REASON[1]', 'int') as [Reason]
--				,T.c.value('./body[1]/CANCEL_SOURSE[1]', 'int') as [CancellationSource]
--				,T.c.value('./body[1]/REFERRAL_NUMBER[1]', 'varchar(16)') as [Referral]
--				,T.c.value('./body[1]/BRANCH[1]', 'varchar(8)') as [Branch]
--				,T.c.value('./body[1]/PATIENT[1]', 'uniqueidentifier') as [Patient]
--				,T.c.value('./body[1]/DATE_CANCEL[1]', 'datetime') as [DateCancel]
--				,T.c.value('./body[1]/DATE[1]', 'datetime') as [RefDate]
--				,T.c.value('./body[1]/REFERRAL_LPU[1]', 'varchar(6)') as [RefMo]
--				,T.c.value('./body[1]/CANCEL_DESCRIPTION[1]', 'varchar(max)') as [Descr]
--			INTO #CANCEL_HOSPITALIZATION_REFERRAL
--		from @Xml.nodes('CANCEL_HOSPITALISATION_REFERRAL') T(c)				
--		select @OperationType = (select OperationType from #CANCEL_HOSPITALIZATION_REFERRAL)
		
--		if(@OperationType <> 'Delete')
--		BEGIN
--		--UPDATE CANCEL_HOSPITALIZATION_REFERRAL 
--		update chr
--		set chr.RdsHospitalizationReferral = 
--		(select rhr.RdsHospitalizationReferralID from [AKUZ].T_RDS_HOSPITALIZATION_REFERRAL rhr
--		join [AKUZ].T_BRANCH bF on bF.LpuID = rhr.BranchFrom
--		join [AKUZ].T_LPU_MAIN lpF on lpF.LpuID = bF.LpuMain
--		where rhr.ReferralNumber = chr1.Referral and rhr.ReferralDate = chr1.RefDate and lpF.LpuCode = chr1.RefMo)
--		from [AKUZ].[T_CANCEL_HOSPITALIZATION_REFERRAL] chr
--		join [AKUZ].T_HOSPITALIZATION_REFERRAL hr on  hr.MedicalRecordID = chr.HospitalizationReferral
--		join [AKUZ].T_REFERRAL ref on ref.MedicalRecordID = hr.MedicalRecordID
--		join [AKUZ].T_MEDICAL_RECORD me on me.MedicalRecordID = hr.MedicalRecordID
--		join [AKUZ].T_RESOURCE r on r.ResourceID = me.Post
--		join [AKUZ].T_DIVISION d on d.DivisionID = r.Division
--		join [AKUZ].T_BRANCH b on b.LpuID = d.Branch
--		join [AKUZ].T_LPU_MAIN lp on lp.LpuID = b.LpuMain
--		join #CANCEL_HOSPITALIZATION_REFERRAL chr1 on chr1.Referral = hr.ReferralNumber
--		where chr1.RefDate = ref.ReferralDate and chr1.RefMo = lp.LpuCode

--		update chr
--		set chr.HospitalizationReferral = (select hr.MedicalRecordID from [AKUZ].T_HOSPITALIZATION_REFERRAL hr 
--		join [AKUZ].T_REFERRAL ref on ref.MedicalRecordID = hr.MedicalRecordID
--		join [AKUZ].T_MEDICAL_RECORD me on me.MedicalRecordID = hr.MedicalRecordID
--		join [AKUZ].T_RESOURCE r on r.ResourceID = me.Post
--		join [AKUZ].T_DIVISION d on d.DivisionID = r.Division
--		join [AKUZ].T_BRANCH b on b.LpuID = d.Branch
--		join [AKUZ].T_LPU_MAIN lp on lp.LpuID = b.LpuMain
--		where chr1.Referral = hr.ReferralNumber and chr1.RefDate = ref.ReferralDate and chr1.RefMo = lp.LpuCode)
--		from [AKUZ].[T_CANCEL_HOSPITALIZATION_REFERRAL] chr
--		join [AKUZ].T_RDS_HOSPITALIZATION_REFERRAL rhr on rhr.RdsHospitalizationReferralID = chr.RdsHospitalizationReferral
--		join [AKUZ].T_BRANCH bF on bF.LpuID = rhr.BranchFrom
--		join [AKUZ].T_LPU_MAIN lpF on lpF.LpuID = bF.LpuMain
--		join #CANCEL_HOSPITALIZATION_REFERRAL chr1 on chr1.Referral = rhr.ReferralNumber
--		where chr1.RefDate = rhr.ReferralDate and chr1.RefMo = lpF.LpuCode

--		declare @lp varchar(6), @lpTo varchar(6)
--		select @lp =(select [LpuMain] from #CANCEL_HOSPITALIZATION_REFERRAL)
--		select @lpTo =(select [RefMo] from #CANCEL_HOSPITALIZATION_REFERRAL)
--			 --INSERT CANCEL_HOSPITALIZATION_REFERRAL
--			INSERT INTO [AKUZ].[T_CANCEL_HOSPITALIZATION_REFERRAL]
--			([CancelHospitalizationReferralID]
--			   ,[ReasonCancel]
--			   ,[HospitalizationReferral]
--			   ,[CancellationSource]
--			   ,[RdsHospitalizationReferral]
--			   ,[DateCancel]
--			   ,[Descr])
--			SELECT
--				chr.[ID]
--			   ,chr.[Reason]
--			   ,(select TOP 1 hr.MedicalRecordID from [AKUZ].T_HOSPITALIZATION_REFERRAL hr 
--					join [AKUZ].T_REFERRAL ref on ref.MedicalRecordID = hr.MedicalRecordID
--					join [AKUZ].T_MEDICAL_RECORD me on me.MedicalRecordID = hr.MedicalRecordID
--					join [AKUZ].T_RESOURCE r on r.ResourceID = me.Post
--					join [AKUZ].T_DIVISION d on d.DivisionID = r.Division
--					join [AKUZ].T_BRANCH b on b.LpuID = d.Branch
--					join [AKUZ].T_LPU_MAIN lp on lp.LpuID = b.LpuMain
--					where chr.Referral = hr.ReferralNumber and chr.RefDate = ref.ReferralDate and chr.RefMo = lp.LpuCode)
--			   ,chr.[CancellationSource]
--			   ,(select TOP 1 rhr.RdsHospitalizationReferralID from [AKUZ].T_RDS_HOSPITALIZATION_REFERRAL rhr 
--					join [AKUZ].T_BRANCH b on b.LpuID = rhr.BranchFrom
--					join [AKUZ].T_LPU_MAIN lp on lp.LpuID = b.LpuMain
--					where chr.Referral = rhr.ReferralNumber and chr.RefDate = rhr.ReferralDate and chr.RefMo = lp.LpuCode)
--			   ,chr.DateCancel
--			   ,chr.Descr
--			from #CANCEL_HOSPITALIZATION_REFERRAL chr
--			join [AKUZ].T_LPU_MAIN lp on lp.LpuCode = chr.[LpuMain] 
--			where not exists 
--			(
--				select * from [AKUZ].[T_CANCEL_HOSPITALIZATION_REFERRAL] chr1
--				join [AKUZ].T_RDS_HOSPITALIZATION_REFERRAL rhr1 on rhr1.RdsHospitalizationReferralID = chr1.RdsHospitalizationReferral
--				join [AKUZ].T_BRANCH bF1 on bF1.LpuID = rhr1.BranchFrom
--				join [AKUZ].T_LPU_MAIN lpF1 on lpF1.LpuID = bF1.LpuMain
--				where chr.RefDate = rhr1.ReferralDate and chr.RefMo = lpF1.LpuCode and chr.Referral = rhr1.ReferralNumber
--			)
--			and not exists 
--			(
--				select * from [AKUZ].[T_CANCEL_HOSPITALIZATION_REFERRAL] chr2
--				join [AKUZ].T_HOSPITALIZATION_REFERRAL hr on  hr.MedicalRecordID = chr2.HospitalizationReferral
--				join [AKUZ].T_REFERRAL ref on ref.MedicalRecordID = hr.MedicalRecordID
--				join [AKUZ].T_MEDICAL_RECORD me on me.MedicalRecordID = hr.MedicalRecordID
--				join [AKUZ].T_RESOURCE r on r.ResourceID = me.Post
--				join [AKUZ].T_DIVISION d on d.DivisionID = r.Division
--				join [AKUZ].T_BRANCH b on b.LpuID = d.Branch
--				join [AKUZ].T_LPU_MAIN lp on lp.LpuID = b.LpuMain
--				where chr.Referral = hr.ReferralNumber and chr.RefDate = ref.ReferralDate and chr.RefMo = lp.LpuCode
--			)
--		END
--		ELSE
--		BEGIN
--			delete ch
--			from [AKUZ].[T_CANCEL_HOSPITALIZATION_REFERRAL] ch
--			join #CANCEL_HOSPITALIZATION_REFERRAL chr on chr.[ID] = ch.CancelHospitalizationReferralID
--		END

--		-- Drop table
--		DROP TABLE #CANCEL_HOSPITALIZATION_REFERRAL

--		END
--END