IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_ISZL_HospitalisationXml]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[sp_ISZL_HospitalisationXml]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		Vitacore (с)
-- Create date: 2020-01-31
-- Description:	Выгрузка информации о госпитализации в ISZL
-- =============================================
CREATE PROCEDURE [dbo].[sp_ISZL_HospitalisationXml]
	@LpuCode varchar(max) = null,
	@Xml xml = null,
	@IsNew bit = 1,
	@ID uniqueidentifier = null,
	@QueueName varchar(255) = null,
	@MovementDate datetime = null,
	@MedCardNumber int = 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/MO'
				,@OperationType						as 'header/TYPE'
				,1											as 'body/FORM_MEDICAL_CARE'
				,@LpuCode									as 'body/MO'
				,'0'										as 'body/DIVISION'
				,convert(varchar(10), @MovementDate, 20)	as 'body/HOSPITALISATION_DATE'
				,'-'										as 'body/LAST_NAME'
				,'-'										as 'body/FIRST_NAME'
				,1											as 'body/SEX'
				,'2020-01-01' 								as 'body/BIRTHDAY'
				,1											as 'body/V020_STRUCTURE_BED'
				,@MedCardNumber								as 'body/MED_CARD_NUMBER'
				,'-'										as 'body/MKB'
				FOR XML PATH(''),
				ROOT('HOSPITALISATION')
			)
		END

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

		,CASE mdc.HospitalizationType
			WHEN 13001 THEN 1
			WHEN 13002 THEN 2
			WHEN 13003 THEN 3
			ELSE 1 END										as 'body/FORM_MEDICAL_CARE'
		,db.DirectionNumber									as 'body/REFERRAL_NUMBER'
		,convert(varchar(10), db.DirectionDate, 20)			as 'body/REFERRAL_DATE'	
		,lpDir.LpuCode										as 'body/REFERRAL_MO'
		,lm.LpuCode											as 'body/MO'
		,br.BranchCode										as 'body/BRANCH'
		,d.Code												as 'body/DIVISION'
		,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.LastName										as 'body/LAST_NAME'
		,pat.FirstName										as 'body/FIRST_NAME'
		,pat.FatherName										as 'body/FATHER_NAME'
		,(case when pat.Sex = 10301 then 1 else 2 end)		as 'body/SEX'
		,convert(varchar(10), pat.Birthday, 20)				as 'body/BIRTHDAY'
		--,''													as 'body/USL_OK' --isnull(v012.DL_USLOV,'')
		,sb.code											as 'body/V020_STRUCTURE_BED'
		,mov.MedCardNumber									as 'body/MED_CARD_NUMBER'
		,mov.Mkb											as 'body/MKB'
		--,''										as 'body/VMP_DATE'
		--,''										as 'body/VMP_NUMBER'
		--,''										as 'body/VID_VMP'
		--,''										as 'body/METOD_VMP'
		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
		join [AKUZ].T_LPU_MAIN lm on br.LpuMain = lm.LpuID
		--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 = pat.OmsPolicyMapped
		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
		--join AKUZ.T_V012_OUTCOME v012 on  mr.TreatmentOutcome = v012.IDIZ
		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