-- =============================================
-- Author:      Vitacore (с)
-- Create date: 2017-11-29
-- Description: Список пациентов, направленных на диспансеризацию
-- =============================================
CREATE OR REPLACE PROCEDURE public.sp_directdispanceriszlxmldir(
	IN "GuidPackage" uuid,
	IN "QueueName" character varying DEFAULT NULL::character varying(100))
LANGUAGE 'plpgsql'
AS $BODY$
declare _filename varchar(50);
	_xml text;
Begin
	 select dp1.FILENAME into _filename from AKUZ.T_RDS_DISP_PACKAGE dp1 where dp1.RdsdispPackageID = "GuidPackage" limit 1;

	_xml := '<?xml version="1.0" encoding="windows-1251"?>' ||
		--ZGLV

		(
			SELECT
			xmlelement
			(
				name "PROF_LIST"
			,	(
					-- Заголовок файла   ZGLV
					select
					xmlelement
					(
						name "ZGLV"
					,	xmlforest
						(
							'1.0' AS "VERSION"
		 				,	to_char(now(), 'YYYY-MM-DD') AS "DATA"
		 				,	_filename AS "FILENAME"						 
					    ,	dp.CODE_MO AS "MCOD"
						,	dp.InsuranceCompany AS "SMO"
	 
						)
					)
					FROM AKUZ.T_RDS_DISP_PACKAGE dp
					WHERE dp.RdsdispPackageID = "GuidPackage"
				)
			,	(
					SELECT
					xmlagg
					(
						xmlelement(
						name "ZL"
					,	xmlforest
						(
							-- Данные   PERS
							dpt.ID_PAC as "ID_PAC"
						, 	dpt.FAM   as "FAM"	
						, 	dpt.IM   as "IM"				
						, 	dpt.OT  as "OT"							
						,	to_char(dpt.DR, 'YYYY-MM-DD') AS "DR"
						,	dpt.ENP 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 REPLACE(dpt.SPOLIS, ' ', '') <> '' then REPLACE(dpt.SPOLIS, ' ', '') end as "SPOLIS"
						,	case when dpt.NPOLIS <> '' then dpt.NPOLIS end as "NPOLIS"
						,	dpt.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 = dpt.ID_PAC
						)
					,	
						(
	                    XMLELEMENT(NAME "PROF",
                        XMLFOREST(
						    dpt.YEAR as "YEAR"
						,	DATE_PART('Month', dpt.DR) as "MONTH"
						,	dpt.TYPE as "TYPE"
		 				,	dpt.V016DispType as  "IDDT"		
						)))
					))
					FROM AKUZ.T_RDS_DISP_PATIENT dpt
    				WHERE dpt.RdsDispPackage = "GuidPackage"
					and dpt.MonthDISP <> 0   --  Если месяц установлен в 0 - диспансеризация не проводится.
				)
			)
		)::text;
		
	if "QueueName" is null
	then
		select _filename, _xml;
  	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(_xml, ''), "GuidPackage", null, 'utf-8', 'PROF_LIST', null, 0, now()::timestamp, null;
	
End;
$BODY$;