-- ===================================================
-- Author:      Vitacore (с)
-- Create date: 2019-04-23
-- Description: Синхронизация дополнительных факторов заявочного каталога
-- ===================================================
DROP PROCEDURE IF EXISTS sp_GetSetAdditionalFactorsRequestCatalog;

CREATE PROCEDURE sp_GetSetAdditionalFactorsRequestCatalog
(
	"Xml" text DEFAULT NULL
,	"IsNew" boolean DEFAULT '1'
,	"ID" uuid DEFAULT NULL::uuid
,	"QueueName" character varying DEFAULT 'RDSSync'::character varying
,	"dateTime" timestamp without time zone DEFAULT NULL::timestamp without time zone
,	"User" uuid DEFAULT NULL::uuid
)
    language plpgsql
as
$$
Begin
    declare _OperationType varchar(255);
    Begin
        IF ("Xml" is not null)
        Then
			-- Получаем тип операции
	    	select into
	    		_OperationType
	    		(xpath('/ADDITIONAL_FACTORS_REQUEST_CATALOG/header[1]/TYPE[1]/text()',"Xml"::xml))[1]::varchar(36);
				
            -- Generate table TMP_ADDITIONAL_FACTORS_REQUEST_CATALOG
	    	Drop Table If Exists TMP_ADDITIONAL_FACTORS_REQUEST_CATALOG;
            Create Temp Table TMP_ADDITIONAL_FACTORS_REQUEST_CATALOG
            as
            select 		
                   (xpath('ADDITIONAL_FACTORS_REQUEST_CATALOG/ID[1]/text()', x))[1]::text::UUID                             ID
                  ,(xpath('ADDITIONAL_FACTORS_REQUEST_CATALOG/DrugApplicationListItem[1]/text()', x))[1]::text::UUID        RequestCatalog
				  ,(xpath('ADDITIONAL_FACTORS_REQUEST_CATALOG/AdditionalFinanc[1]/text()', x))[1]::text::boolean            AdditionalFinanc
				  ,(xpath('ADDITIONAL_FACTORS_REQUEST_CATALOG/Palliative[1]/text()', x))[1]::text::boolean                  Palliative
				  ,(xpath('ADDITIONAL_FACTORS_REQUEST_CATALOG/BenefitViews[1]/text()', x))[1]::text::int                    BenefitViews
                  ,(xpath('ADDITIONAL_FACTORS_REQUEST_CATALOG/BenefitCode[1]/text()', x))[1]::varchar(255)                  BenefitCode
            from
        	(
        		select	unnest(xpath('ADDITIONAL_FACTORS_REQUEST_CATALOG/body/ADDITIONAL_FACTORS_REQUEST_CATALOGS/ADDITIONAL_FACTORS_REQUEST_CATALOG', x)) as x
        		from 
        		(
        			select "Xml"::xml as x
        		) root
        	) ADDITIONAL_FACTORS_REQUEST_CATALOG	
			WHERE  _OperationType = 'FTLoad';

			-- Удаляем отсутствующие
			DELETE FROM AKUZ.T_ADDITIONAL_FACTORS_REQUEST_CATALOG;
			
			-- Добавляем новые
			INSERT INTO AKUZ.T_ADDITIONAL_FACTORS_REQUEST_CATALOG
			(AdditionalFactorsRequestCatalogID,
			 RequestCatalog,
			 AdditionalFinanc,
			 Palliative,
			 Benefits
			)
			SELECT adrc.id,
					adrc.RequestCatalog,
					adrc.AdditionalFinanc,
					adrc.Palliative,
					b.BenefitsID
			FROM  TMP_ADDITIONAL_FACTORS_REQUEST_CATALOG adrc
			left join AKUZ.T_BENEFITS b on b.BenefitCode = adrc.BenefitCode and b.BenefitViews = adrc.BenefitViews;

			-- Удаляем временную таблицу
		DROP TABLE TMP_ADDITIONAL_FACTORS_REQUEST_CATALOG;	
		END if;
    End;
End;
$$;