drop procedure if exists sp_DirectDispancerRDSXML12;

-- =============================================
-- Author:      Vitacore (с)
-- Create date: 2017-11-29
-- Description: Список пациентов, направленных на идентификацию формате XML (DIRECT_IDENT)
-- =============================================

CREATE PROCEDURE sp_DirectDispancerRDSXML12
	(
		GuidPackage UUID,
	    QueueName varchar(100) = null,
	    Step BOOLEAN = false,
	    INOUT cur REFCURSOR = null
	)
AS $$
	declare i int;
	declare filename varchar(50);
	declare xml TEXT;
BEGIN
	
	select 
	case  when count(*)  > 0  then 1
			else 0 end 
	into i  
	from  AKUZ.T_RDS_DISP_PATIENT
	where  AKUZ.T_RDS_DISP_PATIENT.RdsDispPackage = GuidPackage;


  ---------------------  Удаляем результаты загрузки предыдущего акта -----------
 update AKUZ.T_RDS_DISP_PATIENT 
         set  SMO = null,  RdsDispPackage = GuidPackage
	from AKUZ.T_RDS_DISP_PATIENT pt
			where pt.RdsDispPatientID in 
			(
			   select pt.RdsDispPatientID from  AKUZ.T_RDS_DISP_PATIENT pt
			   left join AKUZ.T_RDS_DISP_PACKAGE pc on  pt.RdsDispPackage = pc.RdsdispPackageID
			   where pc.RdsDispPackage = GuidPackage
			);
   delete from AKUZ.T_RDS_DISP_PACKAGE where RdsDispPackage = GuidPackage;


 ------------ XML список пациентов -------------------
  
 filename := (select dp1.FILENAME from AKUZ.T_RDS_DISP_PACKAGE dp1  where dp1.RdsdispPackageID= GuidPackage);

 

 xml := 
  '<?xml version="1.0" encoding="windows-1251"?>
  ' || 
	(SELECT XMLELEMENT
		(	
		 name "PERS_LIST",
		 (	
			 select XMLAGG
			 (
				XMLELEMENT
				(
					name "ZGLV",
					XMLFOREST
					(
						'2.1' AS VERSION
						 ,TO_CHAR(NOW(),'yyyy-MM-dd') AS DATA   
						 ,dp.YearDisp  AS YEAR
						 ,dp.MONTH AS MONTH
						 ,dp.CODE_MO AS CODE_MO
						 ,dp.FILENAME AS FILENAME
					)
				)
			 )
			 FROM AKUZ.T_RDS_DISP_PACKAGE dp
			 WHERE dp.RdsdispPackageID =  GuidPackage
		 ),
		 (
			select xmlagg
	 		(
	 			XMLELEMENT
	 			(
	 				name "PERS",
	 				XMLFOREST
	 				(
	 					dpt.ID_PAC as ID_PAC
						, i as TYPE
						, dpt.YEAR as YEAR_Q
						, DATE_PART('QUARTER',dpt.DR) as QUARTER
						, MonthDISP  as MONTH_Q
						, dpt.PODR as PODR
						, dpt.UCH as UCH
						, dpt.V016DispType as DISP
					    , '0' as STAGE
						, 0 as P_OTK
						, dpt.SMO as SMO
						, dpt.ENP as ENP
					    , dpt.FAM   as FAM 
					    , dpt.IM   as IM     
					    , dpt.OT  as OT 
					    , dpt.W   as W
						, dpt.LOCATION as LOCATION
						, dpt.DATE_NEXT as DATE_NEXT
						, dpt.DS1 as DS1
						, dpt.NPR_MO as NPR_MO
					    , TO_CHAR(dpt.DR,'yyyy-MM-dd') AS DR 
	 				), 
					(
						select XMLAGG
						(
							XMLELEMENT
							(
								name "CONTACTS",
								XMLFOREST
								(
									Number       as CONTACT, 
									case  when PhoneType = 3  then 3
									   when PhoneType in (1,2,5) then 4
									   when PhoneType = 4  then 2
									else 3 end    as TYPE
								)
							)
						)
						FROM  AKUZ.T_PHONE_NUMBER pn where pn.Patient =   dpt.ID_PAC::UUID
					),
	 				XMLFOREST
	 				(
	 					dpt.MR  as MR 
						 , dpt.DOCTYPE as DOCTYPE 
						 , case  when REPLACE(dpt.DOCSER, ' ', '' ) <> ''  then REPLACE(dpt.DOCSER, ' ', '' )
							else null end    as DOCSER
					
						 , case  when dpt.DOCNUM <> ''  then dpt.DOCNUM
							else null end    as DOCNUM
					
						 , case when dpt.SNILS = '' then null else dpt.SNILS end as SNILS
						 , dpt.VPOLIS  as VPOLIS
					
						 , case  when REPLACE(dpt.SPOLIS, ' ', '' )  <> ''  then REPLACE(dpt.SPOLIS, ' ', '' ) 
							else null end    as SPOLIS
						
						 , case  when dpt.NPOLIS  <> ''  then dpt.NPOLIS
							else null end    as NPOLIS
					)
				)
			)
	 		FROM AKUZ.T_RDS_DISP_PATIENT dpt
    		WHERE dpt.RdsDispPackage = GuidPackage 
		 )
		)
	 );
  if (QueueName is null) then
	  begin
			
			open cur for SELECT filename,xml;
	  end;
  else
	  begin
	          INSERT INTO AKUZ.T_RABBIT_SEND_OBJECT
	            (RabbitSendObjectID ,QueueName ,Content ,MessageID ,Headers ,ContentEncoding ,Type ,UserID ,State ,CreateDateTime ,OwnerID)
					select uuid_generate_v4()
	              ,QueueName
	              ,dbo.ConvertTextToBinaryZip(xml, '')
	              ,GuidPackage
	              ,null
	              ,'utf-8'
	              ,case when Step = 0 then 'DISP_DIRECT_IDENT' else 'DISP_DIRECT' end
	              ,null
	              ,0
	              ,NOW()
	              ,null;
	  end;
  end if;
END;
$$ Language plpgsql;