-- =============================================
-- Author:		Vitacore (с)
-- Create date: 2020-01-31
-- Description:	Выгрузка информации о госпитализации в ISZL
-- =============================================
drop procedure if exists public.sp_ISZL_HospitalisationXml;
CREATE procedure public.sp_ISZL_HospitalisationXml
(
	"IsNew" integer DEFAULT 1::integer
,	"ID" uuid DEFAULT NULL::uuid
,	"LpuCode" text DEFAULT NULL::text
,	"QueueName" varchar(255) DEFAULT NULL::varchar(255)
,	"MovementDate" timestamp default null::timestamp
,	"MedCardNumber" integer default null::int
,	"Xml" text DEFAULT NULL
)
AS
$BODY$
Declare	_Result text;
	_ContentEncoding varchar(255);
	_Type varchar(255);
	_OperationType varchar(255);
	_OwnerID UUID;
Begin
	IF "Xml" is null
	then
		--выясняем тип операции
		if "IsNew" = 1
		then
			_OperationType := 'Insert';
		elsif "IsNew" = 0 and exists(select* from akuz.T_MEDICALCARD_REGISTRATION mcr where mcr.MovementID = "ID")
		then
			_OperationType := 'Update';
		elsif "IsNew" = 0 and not exists(select* from akuz.T_MEDICALCARD_REGISTRATION mcr where mcr.MovementID = "ID")
		then		
			_OperationType := 'Delete';
			_Result := XMLELEMENT
						(
							name "HOSPITALISATION"
				  		, 	xmlforest
							(
								xmlforest
								(
									"LpuCode" as "MO"
								,	_OperationType as "TYPE"
								) as header
							)
						, 	xmlforest
							(
								xmlforest
								(
									1 as "FORM_MEDICAL_CARE"
								,	"LpuCode" as "MO"
								,	'0'										as "DIVISION"
								,	to_char("MovementDate", 'YYYY-MM-DD')	as "HOSPITALISATION_DATE"
								,	'-'										as "LAST_NAME"
								,	'-'										as "FIRST_NAME"
								,	1										as "SEX"
								,	'20200101' 								as "BIRTHDAY"
								,	1										as "V020_STRUCTURE_BED"
								,	"MedCardNumber"							as "MED_CARD_NUMBER"
								,	'-'										as "MKB"
								) as body
							)
				  		);
		end if;

		_ContentEncoding := 'utf-8';
		_Type := 'HOSPITALISATION';
		_OwnerID := "ID";
		
		IF	_OperationType <> 'Delete'
		then
			select
			XMLELEMENT
			(
				name "HOSPITALISATION"
			,	XMLFOREST
				(
					XMLFOREST
					(
						"LpuCode" as "MO"
					,	_OperationType as "TYPE"
					) as header
				)
			,	XMLFOREST
				(
					XMLFOREST
					(
						CASE mdc.HospitalizationType
							WHEN 13001 THEN 1
							WHEN 13002 THEN 2
							WHEN 13003 THEN 3
							ELSE 1 
						END as "FORM_MEDICAL_CARE"
					,	db.DirectionNumber as "REFERRAL_NUMBER"
					,	to_char(db.DirectionDate, 'YYYY-MM-DD') as "REFERRAL_DATE"
					,	lpDir.LpuCode as "REFERRAL_MO"
					,	lm.LpuCode as "MO"
					,	br.BranchCode as "BRANCH"
					,	d.Code as "DIVISION"
					,	to_char(mov.MovementDate, 'YYYY-MM-DD') as "HOSPITALISATION_DATE"
					,	to_char(mov.MovementTime, 'YYYY-MM-DD HH24:MI:SS') as "HOSPITALISATION_TIME"
					,	pol.PolicyForm as "POLICY_TYPE"
					,	pol.Serial as "POLIS_SERIAL"
					,	pol.Number as "POLIS_NUMBER"
					,	Coalesce(i1.SmoCod, i2.SmoCod) as "SMO"
					,	pat.LastName as "LAST_NAME"
					,	pat.FirstName as "FIRST_NAME"
					,	pat.FatherName as "FATHER_NAME"
					,	case when pat.Sex = 10301 then 1 else 2 end as "SEX"
					,	to_char(pat.Birthday, 'YYYY-MM-DD') as "BIRTHDAY"
					,	sb.code	 as "V020_STRUCTURE_BED"					
					,	mov.MedCardNumber as "MED_CARD_NUMBER"
					,	mov.Mkb as "MKB"
					) as body
				)
			)
			from akuz.T_MOVEMENT mov 
			inner join 
				akuz.T_STRUCTURE_BED sb 
				on sb.StructureBedID = mov.StructureBed
			inner join 
				akuz.T_V002_PROFILE v0 
				on v0.Id = sb.V002Profile
			--LPU
			inner join 
				akuz.T_DIVISION d 
				on d.DivisionID = mov.Division
			inner join 
				akuz.T_BRANCH br 
				on br.LpuID = d.Branch
			inner join 
				AKUZ.T_LPU_MAIN lm 
				on br.LpuMain = lm.LpuID
			--patient
			inner join 
				akuz.T_EVENT_BASE eb 
				on eb.EventID = mov.EmergencyEvent
			inner join 
				akuz.T_AMBULANCE_CARD ac 
				on ac.AmbulanceCardID = eb.AmbulanceCard
			inner 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
			inner join 
				akuz.T_MEDICALCARD_REGISTRATION mdc 
				on mdc.MovementID = mov.MovementID
			into _Result
			where mov.MovementID = "ID";			
		END if;

		INSERT INTO akuz.T_RABBIT_SEND_OBJECT(RabbitSendObjectID, QueueName, Content, MessageID, Headers, ContentEncoding, Type, UserID, State, CreateDateTime, OwnerID)
		select gen_random_uuid(), "QueueName", ConvertTextToBinaryZip(_Result::text, ''), null, null, _ContentEncoding, _Type, null, 0, Current_TimeStamp::TIMESTAMP, _OwnerID;
	END if;
End;
$BODY$ Language plpgsql;
