-- =============================================
-- Author:      Vitacore (с)
-- Create date: 2019-10-03
-- Description: Формирование пакета диспансерного наблюдения для идентификации в РДС
-- =============================================
CREATE or Replace Function sp_DispanserisationInitToISZL("GuidPackage" uuid, "QueueName" varchar(100) default null::varchar(100))
RETURNS record As 
$$
declare _filename varchar(50);
	_xml text;
Begin
	_filename := (select dp1.FILENAME from AKUZ.T_RDS_DISP_PACKAGE dp1 where dp1.RdsdispPackageID = "GuidPackage");

	--здесь формировать distinct #ZLSDIST
	create temp table TmpZLSDIST
	as
	select distinct dpt.ID_PAC, dpt.FAM, dpt.IM, dpt.OT, to_char(dpt.DR, 'YYYY-MM-DD') AS DR
	, case when dpt.VPOLIS = 3 then dpt.NPOLIS else dpt.ENP end as ENP
	, case when dpt.VPOLIS in (2, 3) then 3 when dpt.VPOLIS = 4 then 2 when dpt.VPOLIS = 1 then 1 end as VPOLIS 
	, case when dpt.SPOLIS = '' then null else dpt.SPOLIS end as SPOLIS	  
	, case when dpt.NPOLIS = '' then null else dpt.NPOLIS end as NPOLIS 	  
	, case when dpt.SMO = '' then null else dpt.SMO end as SMO 
	, case when dpt.SNILS = '' then null else dpt.SNILS end as SNILS
	from AKUZ.T_RDS_DISP_PATIENT dpt
	where dpt.RdsDispPackage = "GuidPackage";
	
	--здесь формировать distinct #DISPANSERISTATIONINITSDN
	create temp table TmpDISPANSERISTATIONINITSDN
	as
	select distinct	to_char(dpt.DS1_PR_DATE, 'YYYY-MM-DD') as DATE_BEGIN
	, to_char(dpt.DATE_OUT, 'YYYY-MM-DD') as DATE_OUT
	, dpt.DS1 as DS
	, dpt.Status as STATUS
	, dpt.CODE_MD as SNILS_MR
	, dpt.V021SpecializationCode as SPEC
	, dpt.ID_PAC as ID_PAC
	, dpt.DISPANSERISATION_INIT_ID
	, dpt.NPR_MO as MO
	from AKUZ.T_RDS_DISP_PATIENT dpt
	where dpt.RdsDispPackage = "GuidPackage";
	
	--здесь формировать distinct #ALLPRESENCES
	create temp table TmpALLPRESENCES
	as
	with cte as
	(
		select distinct dpt.AmbulanceCard, dpt.ID_PAC from AKUZ.T_RDS_DISP_PATIENT dpt where dpt.RdsDispPackage = "GuidPackage"
	)
	select
		pres.MedicalRecordID as PRESENCE_ID
	,	to_char(pres.AppointmentDate, 'YYYY-MM-DD') as DATE_PLAN
	,	case when pres.VisitLocation is null or pres.VisitLocation = 1 then 0 when pres.VisitLocation = 2 then 1 end as PLACE
	,	case when pres.VisitLocation is null or pres.VisitLocation = 1 then lpu_main.LpuCode end as MO_P
	,	dispinit.MedicalRecordID as DISPINIT_ID
	from cte c
	inner join 
		AKUZ.T_AMBULANCE_CARD ambcard 
		on ambcard.AmbulanceCardID = c.AmbulanceCard
	inner join 
		AKUZ.T_AMBULANCE_CARD_RECORD ambcardrecord 
		on ambcardrecord.AmbulanceCard = ambcard.AmbulanceCardID
	inner join 
		AKUZ.T_DISPANSERISATION_INIT dispinit 
		on dispinit.MedicalRecordID = ambcardrecord.MedicalRecordID
	inner join 
		AKUZ.T_DISPANSERISATION_PRESENCE pres 
		on pres.DispanserisationInit = dispinit.MedicalRecordID
	left join 
		AKUZ.T_LPU_MAIN lpu_main 
		on lpu_main.LpuID = pres.LpuMain;
	
	_xml := '<?xml version="1.0" encoding="windows-1251"?>' ||
		--ZGLV
		(
			SELECT
			xmlelement
			(
				name "DN_LIST"
			,	(
					select
					xmlelement
					(
						name "ZGLV"
					,	xmlforest
						(
							case when dp.VERSION is null or dp.VERSION = '' then '1.0' else dp.VERSION end AS "VERSION"
		 				,	to_char(now(), 'YYYY-MM-DD') AS "DATA"
		 				,	dp.FILENAME AS "FILENAME"
		 				,	dp.CODE_MO AS "MCOD"
		 				,	dp.InsuranceCompany as "SMO"
						)
					)
					FROM AKUZ.T_RDS_DISP_PACKAGE dp
					WHERE dp.RdsdispPackageID = "GuidPackage"
				)
			,	(
					SELECT
					xmlelement
					(
						name "ZL"
					,	xmlforest
						(
							zldist.ID_PAC as "ID_PAC"
						,	zldist.FAM as "FAM"
						,	zldist.IM as "IM"
						,	zldist.OT as "OT"
						,	zldist.DR as "DR"
						,	zldist.ENP as "ENP"
						,	zldist.VPOLIS as "VPOLIS"
						,	zldist.SPOLIS as "SPOLIS"
						,	zldist.NPOLIS as "NPOLIS"
						,	zldist.SNILS as "SNILS"
						)
					,	(
							select
							xmlagg
							(
								xmlelement
								(
									name "CONTACTS"
								,	xmlforest
									(
										pn.Number as "CONTACT"
									,	case  when pn.PhoneType = 3 then 3 when pn.PhoneType in (1,2,5) then 4 when pn.PhoneType = 4 then 2 else 3 end as "TYPE"
									)
								)
							)
							from AKUZ.T_PHONE_NUMBER pn 
							where pn.Patient = zldist.ID_PAC
						)
					,	(
							select
							xmlagg
							(
								xmlelement
								(
									name "DN"
								,	xmlforest
									(
										dn.DISPANSERISATION_INIT_ID as "DN_ID"
									,	dn.DATE_BEGIN
									,	dn.DATE_OUT
									,	case when t3.Name = 'Снят с выздоровлением' then 4 
									 		else
									 		case when t3.Name in ('Снят с переводом в группу Д2',
														  'Снят в связи с переводом в другое ЛПУ',
														  'Снят в связи со смертью',
														  'Снят в связи с переводом на другой участок',
														  'Снят в связи с изменением диагноза',
														  'Снят в связи с не подтверждением диагноза',
														  'Снят т.к. не налбюдался более 12 мес.',
														  'Снят/прочее') 
												then 6 
											end 
										end as "DN_PR"
									,	dn.DS
									,	dn.MO
									,	dn.SNILS_MR
									,	dn.SPEC
									)
								,	(
										select
										xmlagg
										(
											xmlelement
											(
												name "PRESENCES"
											,	xmlforest
												(
													pres.PRESENCE_ID
												,	pres.DATE_PLAN
												,	pres.PLACE
												,	pres.MO_P
												)
											)
										)
										from TmpALLPRESENCES pres
										where pres.DISPINIT_ID = dn.DISPANSERISATION_INIT_ID::uuid
									)
								)
							)
							FROM TmpDISPANSERISTATIONINITSDN dn
							inner join 
								AKUZ.T_DISPANSERISATION_INIT dinit 
								on dn.DISPANSERISATION_INIT_ID = dinit.MedicalRecordID
							left join 
								AKUZ.T_DISPANSERISATION_END t2 
								on t2.DispanserisationInit = dinit.MedicalRecordID
							left join 
								VCLib.T_ENUMS t3 
								on t2.CheckUpStatusEnd = t3.EnumID
							where dn.ID_PAC = zldist.ID_PAC
						)
					)
					FROM TmpZLSDIST zldist
				)
			)
		)::text;
			
		DROP TABLE if exists TmpZLSDIST;
		DROP TABLE if exists TmpDISPANSERISTATIONINITSDN;
		DROP TABLE if exists TmpALLPRESENCES;

	if "QueueName" is null
	then
		return _filename, _xml;
  	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(_xml, ''), "GuidPackage", null, 'utf-8', 'DN_LIST', null, 0, now()::timestamp, null;
	
	return null::varchar(50), null::text;
End;
$$ Language plpgsql;