IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_ISZL_MotionInHospitalXml]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[sp_ISZL_MotionInHospitalXml]
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_MotionInHospitalXml]
	@LpuCode varchar(max) = null,
	@Xml xml = null,
	@IsNew bit = 1,
	@ID uniqueidentifier = null,
	@QueueName varchar(255) = null,
	@ObjectType bit = 0 -- 0 - Движение 1- выписка
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(@ObjectType = 0)
		BEGIN
			--выясняем тип операции
			IF(@IsNew = 1)
			BEGIN
				select @OperationType = 'Insert'
			END
			ELSE IF (@IsNew = 0 and exists (select * from [AKUZ].T_MOVEMENTS_DIVISION_CARD mdc  where mdc.MedicalEpisodeID = @ID))
			BEGIN
				select @OperationType = 'Update'
			END
			ELSE IF (@IsNew = 0 and not exists (select * from [AKUZ].T_MOVEMENTS_DIVISION_CARD mdc where mdc.MedicalEpisodeID = @ID))
			BEGIN
			select @OperationType = 'Delete'
			select @Result = (select 
					@LpuCode				as 'header/MO'
					,@OperationType			as 'header/TYPE'
					FOR XML PATH(''),
					ROOT('MOTION_IN_HOSPITAL')
					) 
			END
			
			select @ContentEncoding = 'utf-8'
					,@Type = 'MOTION_IN_HOSPITAL'
					,@OwnerID = @ID
			
			IF(@OperationType <> 'Delete')
				BEGIN
					select @Result = (
					select 
					@LpuCode									as 'header/MO'
					,@OperationType								as 'header/TYPE'
					
					,convert(varchar(10), mov.MovementDate, 20) as 'body/HOSPITALISATION_DATE'
					,mov.MedCardNumber							as 'body/MED_CARD_NUMBER'
					,br.BranchCode								as 'body/BRANCH'
					,d.Code										as 'body/DIVISION'
					,sb.Code									as 'body/V020_STRUCTURE_BED' 
					,convert(varchar(10), me.DateIn, 20)		as 'body/DATE_IN'
					,convert(varchar(10), me.DateOut, 20)		as 'body/DATE_OUT'
					,1											as 'body/TYPE_MOT'
					,''											as 'body/USL_OK'
					,''											as 'body/OUTCOME'

					from [AKUZ].T_MOVEMENTS_DIVISION_CARD mdc
					join [AKUZ].T_MEDICAL_EPISODE me on mdc.MedicalEpisodeID = me.MedicalEpisodeID
					join [AKUZ].T_STRUCTURE_BED sb on sb.StructureBedID = mdc.StructureBed
					join [AKUZ].T_V002_PROFILE v0 on v0.V002ProfileID = sb.V002Profile
					--LPU
					join [AKUZ].T_DIVISION d on d.DivisionID = mdc.Division
					join [AKUZ].T_BRANCH br on br.LpuID = d.Branch
					join [AKUZ].T_LPU_MAIN lp on lp.LpuID = br.LpuMain
					----patient
					join [AKUZ].T_MOVEMENT mov on mov.MovementID = mdc.Movement
					where mdc.MedicalEpisodeID = @ID
					FOR XML PATH(''),
					ROOT('MOTION_IN_HOSPITAL')
				) 
				END
		END
		ELSE
			BEGIN
			--выясняем тип операции
			IF(@IsNew = 1)
			BEGIN
				select @OperationType = 'Insert'
			END
			ELSE IF (@IsNew = 0 and exists (select * from [AKUZ].T_MOVEMENT_RESULT mr  where mr.MedicalRecordId = @ID))
			BEGIN
				select @OperationType = 'Update'
			END
			ELSE IF (@IsNew = 0 and not exists (select * from [AKUZ].T_MOVEMENT_RESULT mr where mr.MedicalRecordId = @ID))
			BEGIN
				select @OperationType = 'Delete'
				select @Result = (select 
						@LpuCode				as 'header/MO'
						,@OperationType			as 'header/TYPE'
					FOR XML PATH(''),
					ROOT('MOTION_IN_HOSPITAL')
					) 
			END
			select @ContentEncoding = 'utf-8'
					,@Type = 'MOTION_IN_HOSPITAL'
					,@OwnerID = @ID

			IF(@OperationType <> 'Delete')
				BEGIN
				
					select @Result = (
						select 
						@LpuCode									as 'header/MO'
						,@OperationType								as 'header/TYPE'
						
						,convert(varchar(10), mov.MovementDate, 20) as 'body/HOSPITALISATION_DATE'
						,mov.MedCardNumber							as 'body/MED_CARD_NUMBER'
						,br.BranchCode								as 'body/BRANCH'
						,d.Code										as 'body/DIVISION'
						,sb.Code									as 'body/V020_STRUCTURE_BED' 
						,convert(varchar(10), mr.DatIn, 20)			as 'body/DATE_IN'
						,convert(varchar(10), mr.DatOut, 20)		as 'body/DATE_OUT'
						,2											as 'body/TYPE_MOT'
						,isnull(v012.DL_USLOV,'')					as 'body/USL_OK'
						,case when mr.TreatmentOutcome = 102 then 1
								when mr.TreatmentOutcome = 103 then 2
								when mr.TreatmentOutcome = 104 then 3 end as 'body/OUTCOME'

						from [AKUZ].T_MOVEMENT_RESULT mr
						join [AKUZ].T_STRUCTURE_BED sb on sb.StructureBedID = mr.StructureBed
						join [AKUZ].T_V002_PROFILE v0 on v0.V002ProfileID = sb.V002Profile
						--LPU
						join [AKUZ].T_DIVISION d on d.DivisionID = mr.Division
						join [AKUZ].T_BRANCH br on br.LpuID = d.Branch
						join [AKUZ].T_LPU_MAIN lp on lp.LpuID = br.LpuMain
						--patient
						join [AKUZ].T_MOVEMENT mov on mov.MovementID = mr.MedicalcardRegistration
						join AKUZ.T_V012_OUTCOME v012 on  mr.TreatmentOutcome = v012.IDIZ
						where mr.MedicalRecordId = @ID --and d.IsRequestonDivision = 0 and d.IsRequestInHospital = 1
						FOR XML PATH(''),
						ROOT('MOTION_IN_HOSPITAL')
					) 
				END
		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