---- =============================================
---- 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;