IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_RDS_HospitalisationXml]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[sp_RDS_HospitalisationXml]
GO
--SET ANSI_NULLS ON
--GO
--SET QUOTED_IDENTIFIER ON
--GO
---- =============================================
---- Author:		Vitacore (с)
---- Create date: 2014-03-11
---- Description:	Выгрузка информации о госпитализации в RDS 
---- =============================================
--CREATE PROCEDURE [dbo].[sp_RDS_HospitalisationXml]
--	@LpuCode varchar(max) = null,
--	@Xml xml = null,
--	@IsNew bit = 1,
--	@ID uniqueidentifier = 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_MEDICALCARD_REGISTRATION mcr  where mcr.MovementID = @ID))
--		BEGIN
--			select @OperationType = 'Update'
--		END
--		ELSE IF (@IsNew = 0 and not exists (select * from [AKUZ].T_MEDICALCARD_REGISTRATION mcr where mcr.MovementID = @ID))
--		BEGIN
--			select @OperationType = 'Delete'
--				select @Result = (select 
--				@LpuCode				as 'header/LPU'
--				,@OperationType			as 'header/TYPE'
--				,@ID					as 'header/ID'
--				FOR XML PATH(''),
--				ROOT('HOSPITALISATION')
--			)
--		END

--select @ContentEncoding = 'utf-8'
--   ,@Type = 'HOSPITALISATION'
--   ,@OwnerID = @ID
--		IF(@OperationType <> 'Delete')
--		BEGIN
--	select @Result = (
--		select 
--		@LpuCode											as 'header/LPU'
--		,@OperationType										as 'header/TYPE'
--		,@ID												as 'header/ID'

--		,db.DirectionNumber									as 'body/REFERRAL_NUMBER'
--		,convert(varchar(10), db.DirectionDate, 20)			as 'body/REFERRAL_DATE'	
--		,lpDir.LpuCode										as 'body/REFERRAL_MO'
--		,br.BranchCode										as 'body/BRANCH'
--		,convert(varchar(10), mov.MovementDate, 20)			as 'body/HOSPITALISATION_DATE'
--		,convert(varchar(20), mov.MovementTime, 20)			as 'body/HOSPITALISATION_TIME'
--		,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'
--		,v0.Id												as 'body/STRUCTURE_BED'
--		,sb.code												as 'body/V020_STRUCTURE_BED'
--		,d.Code												as 'body/DIVISION'
--		,mov.MedCardNumber									as 'body/MED_CARD_NUMBER'
--		,mov.Mkb											as 'body/MKB'
--		,mov.Diagnosis										as 'body/DIAGNOSIS'
--		,pat.PatientID										as 'body/PATIENT'
--		,mov.MovementID										as 'body/ID'
--		,CASE mdc.HospitalizationType
--			WHEN 13001 THEN 0
--			WHEN 13002 THEN 1
--			WHEN 13003 THEN 2
--			ELSE 0 END										as 'body/FORM_MEDICAL_CARE'
--		from [AKUZ].T_MOVEMENT mov 
--		join [AKUZ].T_STRUCTURE_BED sb on sb.StructureBedID = mov.StructureBed
--		join [AKUZ].T_V002_PROFILE v0 on v0.Id = sb.V002Profile
--		--LPU
--		join [AKUZ].T_DIVISION d on d.DivisionID = mov.Division
--		join [AKUZ].T_BRANCH br on br.LpuID = d.Branch
--		--patient
--		join [AKUZ].T_EVENT_BASE eb on eb.EventID = mov.EmergencyEvent
--		join [AKUZ].T_AMBULANCE_CARD ac on ac.AmbulanceCardID = eb.AmbulanceCard
--		join [AKUZ].T_PATIENT pat on pat.PatientID = ac.Patient
--		--polis
--		left join [AKUZ].T_POLICY pol on pol.PolicyID = [dbo].[fn_GetOmsPolicy](pat.PatientID,mov.MovementDate)
--		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
--		--referral
--		left join [AKUZ].T_DIRECTION dir on dir.DirectionID = eb.Direction
--		left join [AKUZ].T_DIRECTION_BASE db on db.DirectionID = dir.DirectionID
--		left join [AKUZ].T_LPU_MAIN lpDir on lpDir.LpuID = db.DirectorLpu
--		join [AKUZ].T_MEDICALCARD_REGISTRATION mdc on mdc.MovementID = mov.MovementID
--		where mov.MovementID = @ID

--		FOR XML PATH(''),
--		ROOT('HOSPITALISATION')
--	) 	
--			 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
--END
--GO