-- =============================================
-- Author:		Vitacore (с)
-- Create date: 2020-02-03
-- Description:	Выгрузка/загрузка информации об анулировании направлении на госпитализацию в(из) ISZL
-- =============================================
drop procedure if exists public.sp_ISZL_CancelReferralXml;
CREATE procedure public.sp_ISZL_CancelReferralXml
(
	"LpuCode" varchar(255) DEFAULT NULL::varchar(255)
,	"Xml" xml DEFAULT NULL::xml
,	"IsNew" integer DEFAULT 1::integer
,	"ID" uuid DEFAULT NULL::uuid
,	"LpuTo" varchar(255) DEFAULT NULL::varchar(255)
,	"QueueName" varchar(255) DEFAULT NULL::varchar(255)
)
AS
$BODY$
Declare	_Result text;
	_ContentEncoding varchar(255);
	_Type varchar(255);
	_OperationType varchar(255);
	_OwnerID UUID;
	_lp varchar(6);
	_lpTo varchar(6);
Begin
	if "Xml" is null
	then
		--выясняем тип операции
		if "IsNew" = 1
		then
			_OperationType := 'Insert';
		elsif "IsNew" = 0 and exists(select* from akuz.T_CANCEL_HOSPITALIZATION_REFERRAL chr where chr.CancelHospitalizationReferralID = "ID")
		then
			_OperationType := 'Update';
		elsif "IsNew" = 0 and not exists(select* from akuz.T_CANCEL_HOSPITALIZATION_REFERRAL chr where chr.CancelHospitalizationReferralID = "ID")
		then
			_OperationType := 'Delete';
			_Result := 
			(
				select
				XMLELEMENT
				(
					name "CANCEL_HOSPITALISATION_REFERRAL"
				,	XMLELEMENT
					(
						name header
					,	XMLFOREST
						(
							"LpuCode" as "LPU"
						,	_OperationType as "TYPE"
						,	"LpuTo" as "LPU_TO"
						)
					)
				,	XMLELEMENT
					(
						name body
					,	XMLFOREST
						(
							coalesce(hr.ReferralNumber, rhr.ReferralNumber)	as "REFERRAL_NUMBER"
						,	coalesce(to_char(ref.ReferralDate, 'YYYY-MM-DD'), to_char(rhr.ReferralDate, 'YYYY-MM-DD')) as "DATE"
						,	coalesce(lpFrom1.LpuCode, lpFrom.LpuCode)		as "REFERRAL_MO"
						,	branch.BranchCode								as "BRANCH"
						)
					)
				)
				from AKUZ.T_CANCEL_HOSPITALIZATION_REFERRAL chr
				left join 
					AKUZ.T_RDS_HOSPITALIZATION_REFERRAL rhr 
					on rhr.RdsHospitalizationReferralID = chr.RdsHospitalizationReferral
				left join 
					AKUZ.T_BRANCH brFr 
					on brFr.LpuID = rhr.BranchFrom		
				left join 
					AKUZ.T_LPU_MAIN lpFrom1 
					on lpFrom1.LpuID = brFr.LpuMain
				left join 
					AKUZ.T_HOSPITALIZATION_REFERRAL hr 
					on  hr.MedicalRecordID = chr.HospitalizationReferral
				left join 
					AKUZ.T_REFERRAL ref 
					on ref.MedicalRecordID = hr.MedicalRecordID
				left join 
					AKUZ.T_MEDICAL_RECORD mr 
					on mr.MedicalRecordID = ref.MedicalRecordID
				left join 
					AKUZ.T_POST post 
					on post.ResourceID = mr.Post
				left join 
					AKUZ.T_RESOURCE r 
					on r.ResourceID = post.ResourceID
				left join 
					AKUZ.T_DIVISION d 
					on d.DivisionID = r.Division
				left join 
					AKUZ.T_BRANCH br 
					on br.LpuID = d.Branch
				left join 
					AKUZ.T_LPU_MAIN lpFrom 
					on lpFrom.LpuID = br.LpuMain
				inner join 
					AKUZ.T_USERS u 
					on u.UserID = chr.Users
				inner join 
					AKUZ.T_BRANCH branch 
					on branch.LpuID = u.Branch
				where chr.CancelHospitalizationReferralID = "ID"
			);
		end if;

		_ContentEncoding := 'utf-8';
		_Type := 'CANCEL_HOSPITALISATION_REFERRAL';
		_OwnerID := "ID";

		if _OperationType <> 'Delete'
		then
			select 
			XMLELEMENT
			(
				name "CANCEL_HOSPITALISATION_REFERRAL"
			,	XMLELEMENT
				(
					name header
				,	XMLFOREST
					(
						"LpuCode" as "MO"
					,	_OperationType as "TYPE"
					,	"LpuTo" as "MO_TO"
					)
				)
			,	XMLELEMENT
				(
					name body
				,	XMLFOREST
					(
						coalesce(hr.ReferralNumber, rhr.ReferralNumber)	as "REFERRAL_NUMBER"
					,	coalesce(to_char(ref.ReferralDate, 'YYYY-MM-DD'), to_char(rhr.ReferralDate, 'YYYY-MM-DD')) as "DATE"
					,	coalesce(lpFrom1.LpuCode, lpFrom.LpuCode)		as "REFERRAL_MO"
					,	branch.BranchCode								as "BRANCH"
					,	chr.ReasonCancel								as "REASON"
					,	chr.CancellationSource							as "CANCEL_SOURSE"
					,	"LpuCode"										as "CODE_ORG"
					,	to_char(chr.DateCancel, 'YYYY-MM-DD')			as "DATE_CANCEL"
					,	chr.Descr										as "CANCEL_DESCRIPTION"
					)
				)
			)			   		
			from AKUZ.T_CANCEL_HOSPITALIZATION_REFERRAL chr
			left join 
				AKUZ.T_RDS_HOSPITALIZATION_REFERRAL rhr 
				on rhr.RdsHospitalizationReferralID = chr.RdsHospitalizationReferral
			left join 
				AKUZ.T_BRANCH brFr 
				on brFr.LpuID = rhr.BranchFrom		
			left join 
				AKUZ.T_LPU_MAIN lpFrom1 
				on lpFrom1.LpuID = brFr.LpuMain
			left join 
				AKUZ.T_HOSPITALIZATION_REFERRAL hr 
				on  hr.MedicalRecordID = chr.HospitalizationReferral
			left join 
				AKUZ.T_REFERRAL ref 
				on ref.MedicalRecordID = hr.MedicalRecordID
			left join 
				AKUZ.T_AMBULANCE_CARD_RECORD acr 
				on acr.MedicalRecordID = hr.MedicalRecordID
			left join 
				AKUZ.T_AMBULANCE_CARD ac 
				on ac.AmbulanceCardID = acr.AmbulanceCard
			left join 
				AKUZ.T_PATIENT p 
				on p.PatientID = ac.Patient
			left join 
				AKUZ.T_MEDICAL_RECORD mr 
				on mr.MedicalRecordID = ref.MedicalRecordID
			left join 
				AKUZ.T_POST post 
				on post.ResourceID = mr.Post
			left join 
				AKUZ.T_RESOURCE r 
				on r.ResourceID = post.ResourceID
			left join 
				AKUZ.T_DIVISION d 
				on d.DivisionID = r.Division
			left join 
				AKUZ.T_BRANCH br 
				on br.LpuID = d.Branch
			left join 
				AKUZ.T_LPU_MAIN lpFrom 
				on lpFrom.LpuID = br.LpuMain
			inner join 
				AKUZ.T_USERS u 
				on u.UserID = chr.Users
			inner join 
				AKUZ.T_BRANCH branch 
				on branch.LpuID = u.Branch		
			into _Result
			where chr.CancelHospitalizationReferralID = "ID";
		end if;
		
		INSERT INTO akuz.T_RABBIT_SEND_OBJECT(RabbitSendObjectID, QueueName, Content, MessageID, Headers, ContentEncoding, Type, UserID, State, CreateDateTime, OwnerID)
		select uuid_generate_v4(), "QueueName", ConvertTextToBinaryZip(_Result, ''), null, null, _ContentEncoding, _Type, null, 0, Current_TimeStamp::TIMESTAMP, _OwnerID;
	else
		if "Xml" is not null
		then
			-- Generate table #CANCEL_HOSPITALIZATION_REFERRAL
			Drop Table If Exists Tmp_CANCEL_HOSPITALIZATION_REFERRAL;
			Create Temp Table Tmp_CANCEL_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 LpuMain
			,	(xpath('/header[1]/MO_TO[1]/text()', t.x1))[1]::varchar(6) as LpuMainTo			
			,	(xpath('/body[1]/REFERRAL_NUMBER[1]/text()', t.x2))[1]::varchar(16) as Referral
			,	to_timestamp((xpath('/body[1]/DATE[1]/text()', t.x2))[1]::text, 'YYYY-MM-DD') as RefDate
			,	(xpath('/body[1]/REFERRAL_MO[1]/text()', t.x2))[1]::varchar(6) as RefMo
			,	(xpath('/body[1]/BRANCH[1]/text()', t.x2))[1]::varchar(8) as Branch
			,	(xpath('/body[1]/REASON[1]/text()', t.x2))[1]::text::int as Reason
			,	(xpath('/body[1]/CANCEL_SOURSE[1]/text()', t.x2))[1]::text::int as CancellationSource
			,	(xpath('/body[1]/CODE_ORG[1]/text()', t.x2))[1]::text::int as CodeOrg
			,	to_timestamp((xpath('/body[1]/DATE_CANCEL[1]/text()', t.x2))[1]::text, 'YYYY-MM-DD') as DateCancel
			,	(xpath('/body[1]/CANCEL_DESCRIPTION[1]/text()', t.x2))[1]::text as Descr			
			from
			(
				select	
					unnest(xpath('/CANCEL_HOSPITALISATION_REFERRAL/header', x)) as x1
				,	unnest(xpath('/CANCEL_HOSPITALISATION_REFERRAL/body', x)) as x2
				from unnest(xpath('/CANCEL_HOSPITALISATION_REFERRAL', "Xml"::xml)) x
			) t;

			_OperationType := (select tmp.OperationType from Tmp_CANCEL_HOSPITALIZATION_REFERRAL tmp Limit 1);

			if _OperationType <> 'Delete'
			then
				--UPDATE CANCEL_HOSPITALIZATION_REFERRAL 
				update akuz.T_CANCEL_HOSPITALIZATION_REFERRAL chr
				set RdsHospitalizationReferral = 
				(
					select rhr.RdsHospitalizationReferralID 
					from akuz.T_RDS_HOSPITALIZATION_REFERRAL rhr
					inner join 
						akuz.T_BRANCH bF 
						on bF.LpuID = rhr.BranchFrom
					inner join 
						akuz.T_LPU_MAIN lpF 
						on lpF.LpuID = bF.LpuMain
					where rhr.ReferralNumber = chr1.Referral 
					and rhr.ReferralDate = chr1.RefDate 
					and lpF.LpuCode = chr1.RefMo
				)
				from akuz.T_HOSPITALIZATION_REFERRAL hr 					
				inner join 
					akuz.T_REFERRAL ref 
					on ref.MedicalRecordID = hr.MedicalRecordID
				inner join 
					akuz.T_MEDICAL_RECORD me 
					on me.MedicalRecordID = hr.MedicalRecordID
				inner join 
					akuz.T_RESOURCE r 
					on r.ResourceID = me.Post
				inner join 
					akuz.T_DIVISION d 
					on d.DivisionID = r.Division
				inner join 
					akuz.T_BRANCH b 
					on b.LpuID = d.Branch
				inner join 
					akuz.T_LPU_MAIN lp 
					on lp.LpuID = b.LpuMain
				inner join 
					Tmp_CANCEL_HOSPITALIZATION_REFERRAL chr1 
					on chr1.Referral = hr.ReferralNumber
				where hr.MedicalRecordID = chr.HospitalizationReferral
				and chr1.RefDate = ref.ReferralDate 
				and chr1.RefMo = lp.LpuCode;
				
				update akuz.T_CANCEL_HOSPITALIZATION_REFERRAL chr
				set HospitalizationReferral = 
				(
					select hr.MedicalRecordID 
					from akuz.T_HOSPITALIZATION_REFERRAL hr 
					inner join 
						akuz.T_REFERRAL ref 
						on ref.MedicalRecordID = hr.MedicalRecordID
					inner join 
						akuz.T_MEDICAL_RECORD me 
						on me.MedicalRecordID = hr.MedicalRecordID
					inner join 
						akuz.T_RESOURCE r 
						on r.ResourceID = me.Post
					inner join 
						akuz.T_DIVISION d 
						on d.DivisionID = r.Division
					inner join 
						akuz.T_BRANCH b 
						on b.LpuID = d.Branch
					inner join 
						akuz.T_LPU_MAIN lp 
						on lp.LpuID = b.LpuMain
					where chr1.Referral = hr.ReferralNumber 
					and chr1.RefDate = ref.ReferralDate 
					and chr1.RefMo = lp.LpuCode
					Limit 1
				)
				from akuz.T_RDS_HOSPITALIZATION_REFERRAL rhr 					
				inner join 
					akuz.T_BRANCH bF 
					on bF.LpuID = rhr.BranchFrom
				inner join 
					akuz.T_LPU_MAIN lpF 
					on lpF.LpuID = bF.LpuMain
				inner join 
					Tmp_CANCEL_HOSPITALIZATION_REFERRAL chr1 
					on chr1.Referral = rhr.ReferralNumber
				where rhr.RdsHospitalizationReferralID = chr.RdsHospitalizationReferral
				and chr1.RefDate = rhr.ReferralDate 
				and chr1.RefMo = lpF.LpuCode;

				select into _lp, _lpTo
					tmp.LpuMain, tmp.RefMo
				from Tmp_CANCEL_HOSPITALIZATION_REFERRAL tmp
				Limit 1;

				--INSERT CANCEL_HOSPITALIZATION_REFERRAL
				INSERT INTO akuz.T_CANCEL_HOSPITALIZATION_REFERRAL(CancelHospitalizationReferralID, ReasonCancel, HospitalizationReferral, CancellationSource, RdsHospitalizationReferral, DateCancel, Descr)
				SELECT	uuid_generate_v4() --chr.ID
				,	chr.Reason
				,	(
						select hr.MedicalRecordID 
						from akuz.T_HOSPITALIZATION_REFERRAL hr 
						inner join 
							akuz.T_REFERRAL ref 
							on ref.MedicalRecordID = hr.MedicalRecordID
						inner join 
							akuz.T_MEDICAL_RECORD me 
							on me.MedicalRecordID = hr.MedicalRecordID
						inner join 
							akuz.T_RESOURCE r 
							on r.ResourceID = me.Post
						inner join 
							akuz.T_DIVISION d 
							on d.DivisionID = r.Division
						inner join 
							akuz.T_BRANCH b 
							on b.LpuID = d.Branch
						inner join 
							akuz.T_LPU_MAIN lp 
							on lp.LpuID = b.LpuMain
						where chr.Referral = hr.ReferralNumber 
						and chr.RefDate = ref.ReferralDate 
						and chr.RefMo = lp.LpuCode
						Limit 1
					)
				,	chr.CancellationSource
				,	(
						select rhr.RdsHospitalizationReferralID 
						from akuz.T_RDS_HOSPITALIZATION_REFERRAL rhr 
						inner join 
							akuz.T_BRANCH b 
							on b.LpuID = rhr.BranchFrom
						inner join 
							akuz.T_LPU_MAIN lp 
							on lp.LpuID = b.LpuMain
						where chr.Referral = rhr.ReferralNumber 
						and chr.RefDate = rhr.ReferralDate 
						and chr.RefMo = lp.LpuCode
						Limit 1
					)
				,	chr.DateCancel
				,	chr.Descr
				from Tmp_CANCEL_HOSPITALIZATION_REFERRAL chr
				inner join 
					akuz.T_LPU_MAIN lp 
					on lp.LpuCode = chr.LpuMain 
				where not exists 
				(
					select* from akuz.T_CANCEL_HOSPITALIZATION_REFERRAL chr1
					inner join 
						akuz.T_RDS_HOSPITALIZATION_REFERRAL rhr1 
						on rhr1.RdsHospitalizationReferralID = chr1.RdsHospitalizationReferral
					inner join 
						akuz.T_BRANCH bF1 
						on bF1.LpuID = rhr1.BranchFrom
					inner join 
						akuz.T_LPU_MAIN lpF1 
						on lpF1.LpuID = bF1.LpuMain
					where chr.RefDate = rhr1.ReferralDate 
					and chr.RefMo = lpF1.LpuCode 
					and chr.Referral = rhr1.ReferralNumber
				)
				and not exists 
				(
					select* from akuz.T_CANCEL_HOSPITALIZATION_REFERRAL chr2
					inner join 
						akuz.T_HOSPITALIZATION_REFERRAL hr 
						on  hr.MedicalRecordID = chr2.HospitalizationReferral
					inner join 
						akuz.T_REFERRAL ref 
						on ref.MedicalRecordID = hr.MedicalRecordID
					inner join 
						akuz.T_MEDICAL_RECORD me 
						on me.MedicalRecordID = hr.MedicalRecordID
					inner join 
						akuz.T_RESOURCE r 
						on r.ResourceID = me.Post
					inner join 
						akuz.T_DIVISION d 
						on d.DivisionID = r.Division
					inner join 
						akuz.T_BRANCH b 
						on b.LpuID = d.Branch
					inner join 
						akuz.T_LPU_MAIN lp 
						on lp.LpuID = b.LpuMain
					where chr.Referral = hr.ReferralNumber 
					and chr.RefDate = ref.ReferralDate 
					and chr.RefMo = lp.LpuCode
				);
			--else
			--	delete from akuz.T_CANCEL_HOSPITALIZATION_REFERRAL ch
			--	where exists(select* from Tmp_CANCEL_HOSPITALIZATION_REFERRAL chr where ch.CancelHospitalizationReferralID = chr.ID);
			end if;
			DROP TABLE if exists Tmp_CANCEL_HOSPITALIZATION_REFERRAL;
		END if;
	End if;
End;
$BODY$ Language plpgsql;