-- =============================================
-- Author:      Vitacore (с)
-- Create date: 2017-12-21
-- Description: Выгрузка/загрузка справочника  в(из) RDS
-- =============================================
DROP PROCEDURE IF EXISTS sp_RDS_BillOfGoodsLinkPT;

CREATE PROCEDURE sp_RDS_BillOfGoodsLinkPT
(
	"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
,	"PATH"  character varying DEFAULT null::character varying
)
    language plpgsql
as
$$
Begin

	Declare	_Result text;
		_ContentEncoding varchar(255) := 'utf-8';
		_Type varchar(255) := 'BILL_OF_GOODS_LINK_PAYMENT_TYPE';
		_OperationType varchar(255);
		_OwnerID UUID := "ID";
		-- Operation types
		_INSERT varchar(6) := 'Insert';
		_UPDATE varchar(6) := 'Update';
		_DELETE varchar(6) := 'Delete';
		_FTLOAD varchar(6) := 'FTLoad';
		_sql text;
	Begin
		"dateTime" := Coalesce("dateTime", Current_TimeStamp::TIMESTAMP);

        IF "PATH" IS NOT NULL
        Then
        
	        DROP TABLE IF EXISTS TEMP_BILL_OF_GOODS_LINK_PAYMENT_TYPE;
	        CREATE TEMP TABLE TEMP_BILL_OF_GOODS_LINK_PAYMENT_TYPE(
            	 BillOfGoodsLinkPaymentTypeID	uuid
            	,BillOfGoods					uuid
            	,PaymentType					INT
            	,DateBegin						timestamp
            	,DateEnd						timestamp
	        );

            _sql := 'INSERT INTO TEMP_BILL_OF_GOODS_LINK_PAYMENT_TYPE(
                             BillOfGoodsLinkPaymentTypeID
            				,BillOfGoods
            				,PaymentType
            				,DateBegin
            				,DateEnd )
            			SELECT 				 
            				 BillOfGoodsLinkPaymentTypeID
            				,BillOfGoods
            				,PaymentType
            				,DateBegin
            				,DateEnd 
            			FROM dblink(''dbname=' || "PATH" 
		    		        || ''','
		    		        || ''' 
                        SELECT 				 
            				 BillOfGoodsLinkPaymentTypeID
            				,BillOfGoods
            				,PaymentType
            				,DateBegin
            				,DateEnd
                        FROM PHARM.T_BILL_OF_GOODS_LINK_PAYMENT_TYPE'') 
					AS lpt( BillOfGoodsLinkPaymentTypeID	uuid
            	            ,BillOfGoods					uuid
            	            ,PaymentType					INT
            	            ,DateBegin						timestamp
            	            ,DateEnd						timestamp) ';

            EXECUTE _sql;
    
    	    DELETE from PHARM.T_BILL_OF_GOODS_LINK_PAYMENT_TYPE;
    	    
    	    -- Загружаем новые
    	    INSERT INTO PHARM.T_BILL_OF_GOODS_LINK_PAYMENT_TYPE
    	    		(
    	    			 BillOfGoodsLinkPaymentTypeID
    	    			,BillOfGoods
    	    			,PaymentType
    	    			,DateBegin
    	    			,DateEnd
    	    		)
    	    SELECT
    	               nmf.BillOfGoodsLinkPaymentTypeID,
    	               nmf.BillOfGoods,
    	    		   nmf.PaymentType,
    	    		   nmf.DateBegin,
    	    		   nmf.DateEnd
    	    FROM TEMP_BILL_OF_GOODS_LINK_PAYMENT_TYPE nmf;
    
    	ELSE
        
            IF ("Xml" is null)
            then
				--выясняем тип операции
				if "ID" is null
				then
					_OperationType := _FTLOAD;
				elsif "IsNew" = '1'
				then
					_OperationType := _INSERT;
				elsif "IsNew" = '0' and exists(select* from pharm.T_BILL_OF_GOODS_LINK_PAYMENT_TYPE bg where bg.BillOfGoodsLinkPaymentTypeID = "ID")
				then
					_OperationType := _UPDATE;
				elsif "IsNew" = '0' and not exists(select* from pharm.T_BILL_OF_GOODS_LINK_PAYMENT_TYPE bg where bg.BillOfGoodsLinkPaymentTypeID = "ID")
				then
					_OperationType := _DELETE;
					_Result := XMLELEMENT(name BILL_OF_GOODS_LINK_PAYMENT_TYPE, XMLELEMENT(name header, XMLFOREST(_OperationType as "TYPE", "ID" as "ID")));
				end if;
    
                -- Обработка операции
                IF (_OperationType = _FTLOAD)
                then
                    -- Первичная загрузка
                    select 			    
					XMLELEMENT
					(
						name "BILL_OF_GOODS_LINK_PAYMENT_TYPE"
					,	XMLELEMENT
						(
							name header
						,	XMLFOREST(_FTLOAD as "TYPE")
						)
					,	XMLELEMENT
						(
							name body
						,	XMLELEMENT
							(
								name BILL_OF_GOODS_LINK_PAYMENT_TYPES
							,	XMLELEMENT
								(
									name BILL_OF_GOODS_LINK_PAYMENT_TYPE
								,	XMLFOREST
									(
									     s.BillOfGoodsLinkPaymentTypeID				as "ID"
    	    						    ,s.BillOfGoods								as "BillOfGoods"
    	    				            ,to_char(s.DateBegin, 'YYYY-MM-DD')	        AS "DateBegin"
    	    				            ,to_char(s.DateEnd, 'YYYY-MM-DD')		    AS "DateEnd"
    	    						    ,s.PaymentType								as "PaymentType"
									)
								)
							)
						)
					)
					from PHARM.T_BILL_OF_GOODS_LINK_PAYMENT_TYPE s
					into _Result;	
                ELSIF(_OperationType <> _DELETE)
                then
                    -- собираем BILL_OF_GOODS_LINK_PAYMENT_TYPE
                    select
					XMLELEMENT
					(
						name "BILL_OF_GOODS_LINK_PAYMENT_TYPE"
					    ,	XMLELEMENT
					    	(
					    		name header
					    	,	XMLForest
					    		(
					    			_OperationType as "TYPE"
					    		,	"ID" as "ID"
					    		)
					    	)
					    ,	XMLELEMENT
					    	(
					    		name body
					    	,	XMLFOREST
					    		(
                                   s.BillOfGoods						AS "BillOfGoods"
    	    		    		  ,to_char(s.DateBegin, 'YYYY-MM-DD')	AS "DateBegin"
    	    		    		  ,to_char(s.DateEnd, 'YYYY-MM-DD')		AS "DateEnd"
    	    		    		  ,s.PaymentType						as "PaymentType"
					    	)					
					    )
                    )
					from PHARM.T_BILL_OF_GOODS_LINK_PAYMENT_TYPE s
					into _Result
					where s.BillOfGoodsLinkPaymentTypeID = "ID";
                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(_Result, ''), null, null, _ContentEncoding, _Type, "User", 0, "dateTime", _OwnerID;
            ELSIF ("Xml" is not null)
            Then
                -- Получаем тип операции
					_OperationType := (xpath('/BILL_OF_GOODS_LINK_PAYMENT_TYPE/header[1]/TYPE[1]/text()', "Xml"::xml))[1]::text::varchar(36);
    
                -- Generate table Tmp_BILL_OF_GOODS_LINK_PAYMENT_TYPES
				Drop Table If Exists Tmp_BILL_OF_GOODS_LINK_PAYMENT_TYPES;
				Create Temp Table Tmp_BILL_OF_GOODS_LINK_PAYMENT_TYPES
				as 
                select (xpath('BILL_OF_GOODS_LINK_PAYMENT_TYPE/ID[1]/text()', t.x))[1]::text::UUID								as ID
                      ,(xpath('BILL_OF_GOODS_LINK_PAYMENT_TYPE/BillOfGoods[1]/text()', t.x))[1]::text::UUID					    as BillOfGoods
			          ,	to_timestamp((xpath('/BILL_OF_GOODS_LINK_PAYMENT_TYPE/DateBegin[1]/text()', t.x))[1]::text, 'YYYY-MM-DD') as DateBegin
			          ,	to_timestamp((xpath('/BILL_OF_GOODS_LINK_PAYMENT_TYPE/DateEnd[1]/text()', t.x))[1]::text, 'YYYY-MM-DD')   as BeginPeriod
    	    		  ,(xpath('BILL_OF_GOODS_LINK_PAYMENT_TYPE/PaymentType[1]/text()', t.x))[1]::text::int						as PaymentType
                
				from
				(
					select	unnest(xpath('/BILL_OF_GOODS_LINK_PAYMENT_TYPE/body/BILL_OF_GOODS_LINK_PAYMENT_TYPES/BILL_OF_GOODS_LINK_PAYMENT_TYPE', "Xml"::xml)) as x
				) t
				where _OperationType = _FTLOAD;
    
                -- Generate table Tmp_BILL_OF_GOODS_LINK_PAYMENT_TYPE
				Drop Table If Exists Tmp_BILL_OF_GOODS_LINK_PAYMENT_TYPE;
				Create Temp Table Tmp_BILL_OF_GOODS_LINK_PAYMENT_TYPE
				as 
                select (xpath('/header[1]/ID[1]/text()', t.x1))[1]::text::UUID							as ID
                      ,(xpath('/body[1]/BillOfGoods[1]/text()', t.x2))[1]::text::UUID					as BillOfGoods
			          ,	to_timestamp((xpath('/body[1]/DateBegin[1]/text()', t.x2))[1]::text, 'YYYY-MM-DD') as DateBegin
			          ,	to_timestamp((xpath('/body[1]/DateEnd[1]/text()', t.x2))[1]::text, 'YYYY-MM-DD')   as DateEnd
    	    		  ,(xpath('./body[1]/CertificateNumber[1]/text()', t.x2))[1]::text::int				as PaymentType
                from
				(
					select	unnest(xpath('/BILL_OF_GOODS_LINK_PAYMENT_TYPE/header', x)) as x1
					,	unnest(xpath('/BILL_OF_GOODS_LINK_PAYMENT_TYPE/body', x)) as x2
					from unnest(xpath('/BILL_OF_GOODS_LINK_PAYMENT_TYPE', "Xml"::xml)) x
					where _OperationType <> _FTLOAD
				) t;
    
                -- Обработка операции
                if (_OperationType = _FTLOAD)
                Then
                    delete from PHARM.T_BILL_OF_GOODS_LINK_PAYMENT_TYPE;
    	    		
                    -- Загружаем новые
                    INSERT INTO PHARM.T_BILL_OF_GOODS_LINK_PAYMENT_TYPE
                               (BillOfGoodsLinkPaymentTypeID,BillOfGoods,PaymentType,DateBegin,DateEnd)
                    SELECT
                               nmf.ID,
                               nmf.BillOfGoods,
    	    				   nmf.PaymentType,
    	    				   nmf.DateBegin,
    	    				   nmf.DateEnd
                    from Tmp_BILL_OF_GOODS_LINK_PAYMENT_TYPES nmf;
                elsif(_OperationType = _DELETE)
                then
                    -- Удаление
                    delete from PHARM.T_BILL_OF_GOODS_LINK_PAYMENT_TYPE s
                        using Tmp_BILL_OF_GOODS_LINK_PAYMENT_TYPE nmf 
                    where nmf.ID = s.BillOfGoodsLinkPaymentTypeID;
                else
                    -- update PHARM.T_BILL_OF_GOODS_LINK_PAYMENT_TYPE
                    update PHARM.T_BILL_OF_GOODS_LINK_PAYMENT_TYPE s
                    set BillOfGoods = nmf.BillOfGoods
    	    		   ,PaymentType = nmf.PaymentType
    	    		   ,DateBegin = nmf.DateBegin
    	    		   ,DateEnd = nmf.DateEnd
                    from Tmp_BILL_OF_GOODS_LINK_PAYMENT_TYPE nmf
                    where nmf.ID = s.BillOfGoodsLinkPaymentTypeID and _OperationType = _UPDATE;
    
                    -- insert PHARM.T_BILL_OF_GOODS_LINK_PAYMENT_TYPE
                    INSERT INTO PHARM.T_BILL_OF_GOODS_LINK_PAYMENT_TYPE
                        (BillOfGoodsLinkPaymentTypeID,BillOfGoods,PaymentType,DateBegin,DateEnd)
                    SELECT
                               nmf.ID,
                               nmf.BillOfGoods,
    	    				   nmf.PaymentType,
    	    				   nmf.DateBegin,
    	    				   nmf.DateEnd
                    from Tmp_BILL_OF_GOODS_LINK_PAYMENT_TYPE nmf
                    where _OperationType = _INSERT;
                END if;
    
            -- Drop tables
            DROP TABLE Tmp_BILL_OF_GOODS_LINK_PAYMENT_TYPE;
            DROP TABLE Tmp_BILL_OF_GOODS_LINK_PAYMENT_TYPES;
			END if;
		end if;
	End;
End;
$$;