-- =============================================
-- Author:		Vitacore (с)
-- Create date: 2020-01-31
-- Description:	Выгрузка/загрузка информации направлении на госпитализацию в(из) ISZL
-- =============================================
drop procedure if exists public.sp_ISZL_ReferralXml;
CREATE procedure public.sp_ISZL_ReferralXml
(
	"LpuCode" varchar(255) DEFAULT NULL::varchar(255)
,	"Xml" text DEFAULT NULL::text
,	"IsNew" integer DEFAULT '1'::integer
,	"ID" uuid DEFAULT NULL::uuid
,	"LpuTo" varchar(255) DEFAULT NULL::varchar(255)
,	"FilesXml" text DEFAULT NULL::text
,	"QueueName" varchar(255) DEFAULT NULL::varchar(255)
)
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 not exists(select* from AKUZ.T_HOSPITALIZATION_REFERRAL hr where hr.MedicalRecordID = "ID")
		then
			_OperationType := 'Delete';
			_Result := XMLELEMENT(name "HOSPITALISATION_REFERRAL", XMLFOREST(XMLFOREST(_OperationType as "TYPE", "LpuCode" as "MO", "LpuTo" as "MO_TO") as header));				
		END if;

		_ContentEncoding := 'utf-8';
		_Type := 'HOSPITALISATION_REFERRAL';
		_OwnerID := "ID";
		
		IF _OperationType = 'Insert'
		then
			select
			XMLELEMENT
			(
				name "HOSPITALISATION_REFERRAL"
			,	XMLFOREST
				(
					XMLFOREST
					(
						_OperationType as "TYPE"
					,	"LpuCode" as "MO"
					,	"LpuTo" as "MO_TO"
					) as header
				)
			,	XMLFOREST
				(
					XMLFOREST
					(
						hr.ReferralNumber as "REFERRAL_NUMBER"
					,	to_char(ref.ReferralDate, 'YYYY-MM-DD') as "REFERRAL_DATE"
					,	CASE hr.HospitalizationType
							WHEN 13001 THEN 3
							WHEN 13002 THEN 1
							WHEN 13003 THEN 2
							ELSE 0 
						END as "HOSPITALISATION_TYPE"
					,	br.BranchCode as "BRANCH_FROM"
					,	brT.BranchCode as "BRANCH_TO"
					,	dT.Code as "DIVISION_TO"
					,	sb.Code as "V020_STRUCTURE_BED"					
					,	ref.MkbCode as "MKB"
					,	to_char(hr.PreHospitalDate, 'YYYY-MM-DD') as "PLANNED_DATE"
					,	p.Code as "DOC_CODE"
					,	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"
					,	hr.Phone as "PHONE"
					,	coalesce(hr.AnotherRegion, '0')::int as "ANOTHER_REGION"
					) as body
				)
			)
			from akuz.T_HOSPITALIZATION_REFERRAL hr 
			inner join 
				akuz.T_STRUCTURE_BED sb 
				on sb.StructureBedID = hr.StructureBed
			inner join 
				akuz.T_V002_PROFILE v0 
				on v0.Id = sb.V002Profile
			inner join 
				akuz.T_REFERRAL ref 
				on ref.MedicalRecordID = hr.MedicalRecordID
			--to
			left join 
				akuz.T_BRANCH brT 
				on brT.LpuID = hr.Branch
			left join 
				akuz.T_DIVISION dT 
				on dT.DivisionID = ref.Division
			--from
			inner join 
				akuz.T_MEDICAL_RECORD mr 
				on mr.MedicalRecordID = ref.MedicalRecordID
			inner join 
				akuz.T_POST p 
				on p.ResourceID = mr.Post
			inner join 
				akuz.T_RESOURCE r 
				on r.ResourceID = p.ResourceID
			inner join 
				akuz.T_DIVISION d 
				on d.DivisionID = r.Division
			left  join 
				akuz.T_BRANCH br 
				on br.LpuID = d.Branch
			--patient
			inner join 
				akuz.T_AMBULANCE_CARD ac 
				on ac.AmbulanceCardID = mr.AmbulanceCard
			inner join 
				akuz.T_PATIENT pat 
				on pat.PatientID = ac.Patient
			--polis
			inner join 
				akuz.T_POLICY pol 
				on pol.PolicyID = dbo.fn_GetOmsPolicy(pat.PatientID, ref.ReferralDate)
			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
			into _Result
			where hr.MedicalRecordID = "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, ''), null, null, _ContentEncoding, _Type, null, 0, Current_TimeStamp::TIMESTAMP, _OwnerID;
	ELSE 
		IF "Xml" is not null
		then					
			_OperationType := (xpath('/HOSPITALISATION_REFERRAL/header[1]/TYPE[1]/text()', "Xml"::xml))[1]::text;

			-- Generate table #HOSPITALIZATION_REFERRAL
			Drop Table If Exists Tmp_HOSPITALIZATION_REFERRAL;
			Create Temp Table Tmp_HOSPITALIZATION_REFERRAL
			as
			SELECT	(xpath('/header[1]/TYPE[1]/text()', t.x1))[1]::text as OperationType
			,	(xpath('/header[1]/MO[1]/text()', t.x1))[1]::varchar(6) as LpuFrom
			,	(xpath('/header[1]/MO_TO[1]/text()', t.x1))[1]::varchar(6) as LpuTo

			,	(xpath('/body[1]/REFERRAL_NUMBER[1]/text()', t.x2))[1]::varchar(16) as ReferralNumber
			,	to_timestamp((xpath('/body[1]/REFERRAL_DATE[1]/text()', t.x2))[1]::text, 'YYYY-MM-DD') as ReferralDate
			,	(xpath('/body[1]/HOSPITALISATION_TYPE[1]/text()', t.x2))[1]::text::int as HospitalizationType
			,	(xpath('/body[1]/BRANCH_FROM[1]/text()', t.x2))[1]::varchar(16) as BranchFrom
			,	to_timestamp((xpath('/body[1]/VMP_DATE[1]/text()', t.x2))[1]::text, 'YYYY-MM-DD') as VmpDate
			,	(xpath('/body[1]/VMP_NUMBER[1]/text()', t.x2))[1]::text::int as VmpNumber
			,	(xpath('/body[1]/BRANCH_TO[1]/text()', t.x2))[1]::varchar(16) as BranchTo
			,	(xpath('/body[1]/DIVISION_TO[1]/text()', t.x2))[1]::varchar(16) as DivivsionTo				
			,	(xpath('/body[1]/V020_STRUCTURE_BED[1]/text()', t.x2))[1]::text::int as V020StructureBed
			,	(xpath('/body[1]/MKB[1]/text()', t.x2))[1]::varchar(5) as MkbCode
			,	to_timestamp((xpath('/body[1]/PLANNED_DATE[1]/text()', t.x2))[1]::text,'YYYY-MM-DD') as PlannedDate
			,	(xpath('/body[1]/USL_OK[1]/text()', t.x2))[1]::text::int as UslOk
			,	(xpath('/body[1]/DOC_CODE[1]/text()', t.x2))[1]::varchar(100) as DocCode
			,	(xpath('/body[1]/POLICY_TYPE[1]/text()', t.x2))[1]::text::int as PolicyType
			,	(xpath('/body[1]/POLIS_SERIAL[1]/text()', t.x2))[1]::varchar(16) as PolisSerial
			,	(xpath('/body[1]/POLIS_NUMBER[1]/text()', t.x2))[1]::varchar(16) as PolisNumber
			,	(xpath('/body[1]/SMO[1]/text()', t.x2))[1]::varchar(5) as Smo
			,	(xpath('/body[1]/LAST_NAME[1]/text()', t.x2))[1]::varchar(32) as LastName
			,	(xpath('/body[1]/FIRST_NAME[1]/text()', t.x2))[1]::varchar(32) as FirstName			
			,	(xpath('/body[1]/FATHER_NAME[1]/text()', t.x2))[1]::varchar(32) as FatherName
			,	(xpath('/body[1]/SEX[1]/text()', t.x2))[1]::text::int as Sex
			,	to_timestamp((xpath('/body[1]/BIRTHDAY[1]/text()', t.x2))[1]::text,'YYYY-MM-DD') as Birthday
			,	(xpath('/body[1]/PHONE[1]/text()', t.x2))[1]::varchar(128) as Phone
			,	(xpath('/body[1]/ANOTHER_REGION[1]/text()', t.x2))[1]::text::boolean as AnotherRegion
			from
			(
				select	unnest(xpath('/HOSPITALISATION_REFERRAL/header', x)) as x1
				,	unnest(xpath('/HOSPITALISATION_REFERRAL/body', x)) as x2
				from unnest(xpath('/HOSPITALISATION_REFERRAL', "Xml"::xml)) x
			) t;
		
			if _OperationType = 'Insert'
			then		
				--INSERT RDS_HOSPITALIZATION_REFERRAL
				INSERT INTO akuz.T_RDS_HOSPITALIZATION_REFERRAL(RdsHospitalizationReferralID, ReferralNumber, ReferralDate, HospitalizationType, Division, BranchFrom
																, PolicyForm, PolisSerial, PolisNumber, Insurer, FirstName, LastName, FatherName, Sex, Birthday, Phone
																, MkbCode, StructureBed, PlannedDate, DocCode, AnotherRegion, LpuMainFrom)
				SELECT gen_random_uuid(), hr.ReferralNumber, hr.ReferralDate
				, CASE hr.HospitalizationType WHEN 0 THEN 13001 WHEN 1 THEN 13002 WHEN 2 THEN 13003 ELSE 13001 END
				, dTo.DivisionID, bF.LpuID, hr.PolicyType, hr.PolisSerial, hr.PolisNumber, i.InsurerID, hr.FirstName, hr.LastName, hr.FatherName
				, case when hr.Sex = 1 then 10301 else 10302 end, hr.Birthday, hr.Phone, hr.MkbCode
				, case when hr.V020StructureBed is null 
					then (select sb.StructureBedID from AKUZ.T_STRUCTURE_BED sb
						inner join 
							AKUZ.T_V002_PROFILE v0 
							on v0.Id = sb.V002Profile
						where v0.Id = hr.V020StructureBed 
						limit 1
						)
					else (select sb.StructureBedID from AKUZ.T_STRUCTURE_BED sb
						inner join 
							AKUZ.T_V002_PROFILE v0 
							on v0.Id = sb.V002Profile
						where sb.Code = hr.V020StructureBed 
						and sb.DateBegin <= hr.ReferralDate
						and (sb.DateEnd is null or sb.DateEnd >= hr.ReferralDate) 
						limit 1
						) 
				  end 
				, hr.PlannedDate, hr.DocCode
				, hr.AnotherRegion
				, lpFrom.LpuId
				from Tmp_HOSPITALIZATION_REFERRAL hr
				left join 
					akuz.T_LPU_MAIN lpTo 
					on lpTo.LpuCode = hr.LpuTo 
				left join 
					akuz.T_BRANCH bTo 
					on bTo.LpuMain = lpTo.LpuID 
					and bTo.BranchCode = hr.BranchTo
				left join 
					akuz.T_DIVISION dTo 
					on dTo.Branch = bTo.LpuID 
					and dTo.Code = hr.DivivsionTo
				left join 
					akuz.T_LPU_MAIN lpFrom 
					on lpFrom.LpuCode = hr.LpuFrom
				left join 
					akuz.T_BRANCH bF 
					on bF.LpuMain = lpFrom.LpuID 
					and bF.BranchCode = hr.BranchFrom
				left join 
					akuz.T_INSURER i 
					on i.SmoCod = hr.Smo
				where not exists(select * 
									from akuz.T_RDS_HOSPITALIZATION_REFERRAL hr1 
									where hr1.ReferralNumber = hr.ReferralNumber 
										and hr1.ReferralDate = hr.ReferralDate);
			ELSE
				-- delete from akuz.T_RDS_HOSPITALIZATION_REFERRAL hr
				-- where exists(select* from Tmp_HOSPITALIZATION_REFERRAL r where hr.ID = r.ID);
			END if;
		-- Drop table
		DROP TABLE if exists Tmp_HOSPITALIZATION_REFERRAL;
		END if;
	end if;
End;
$BODY$ Language plpgsql;