DROP PROCEDURE if exists sp_GetSetProductForm;
-- =============================================
-- Author:      Vitacore (с)
-- Create date: 2017-12-21
-- Description: Выгрузка/загрузка справочника форм выпуска в(из) RDS
-- =============================================
CREATE PROCEDURE sp_GetSetProductForm
(
     "Xml" text DEFAULT NULL
    ,"IsNew" integer = 1
    ,"ID" uuid DEFAULT NULL::uuid
    ,"QueueName" character varying DEFAULT NULL::character varying
    ,"dateTime" timestamp without time zone DEFAULT NULL::timestamp without time zone
    ,"User" uuid DEFAULT NULL::uuid
)
    language plpgsql
as
$$
Begin
    declare 
        _Result text;
		_ContentEncoding varchar(255) := 'utf-8';
		_Type varchar(255) := 'PRODUCT_FORM';
		_OperationType varchar(255);
		_OwnerID UUID := "ID";
    Begin
        "dateTime" := Coalesce("dateTime", Current_TimeStamp::TIMESTAMP);
        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_PRODUCT_FORM where ProductFormID = "ID"))
		    then
		    	_OperationType = 'Update';
		    elsif ("IsNew" = 0 and not exists (select * from PHARM.T_PRODUCT_FORM where ProductFormID = "ID"))
            then
                _OperationType := 'Delete';
		    	select 
		    	xmlelement
		    	(
		    		name "PRODUCT_FORM",
		    		xmlelement
		    		(
		    			name header,
		    			xmlforest
		    			(	
		    				_OperationType as "TYPE",
		    				"ID" as "ID"
		    			)
		    		)
		    	)
		    	into _Result;
            end if;

            if (_OperationType = 'FTLoad')
            then
                _Result :=
    			XMLELEMENT
			    (
			        NAME "PRODUCT_FORM"
			        , XMLELEMENT
			          (
			            NAME header
			            , XMLFOREST
			              (
			                _ftload AS "TYPE"
			              )
			          )
			        , XMLELEMENT
			          (
			            NAME body
			            , XMLELEMENT
			              (
			                NAME "PRODUCT_FORMS"
			                , (SELECT XMLAGG
			                          (
			                              XMLELEMENT
			                              (
			                                  NAME "PRODUCT_FORM"
			                                  , XMLFOREST
			                                    (
			                                      pf.productformid AS "ID"
			                                      , pf.ordinalnumber AS "OrdinalNumber"
			                                      , pf.amount AS "Amount"
			                                      , pf.unitprice AS "UnitPrice"
			                                      , pf.writeoff AS "WriteOff"
			                                      , pf.packagetype AS "PackageType"
			                                      , pf.billofgoods AS "BillOfGoods"
			                                      , pf.descr AS "Descr"
			                                      , pf.notuseinpackage AS "NotUseInPackage"
			                                      , pf.appointment AS "Appointment"
			                                    )
			                              )
			                          )
			                   FROM pharm.t_product_form pf
			                  )
			              )
			          )
			    );
            elsif (_OperationType <> 'Delete')
            then
                select
                xmlelement
                (
                    name "PRODUCT_FORM",
                    xmlelement
                    (
                        name header,
                        xmlforest
                        (
                            _OperationType as "TYPE",
                            "ID" as "ID"
                        )
                    ),
                    xmlelement
                    (
                        name body,
                        xmlforest
                        (
                             pf.OrdinalNumber		as "OrdinalNumber"
                            ,pf.Amount			    as "Amount"
					        ,pf.UnitPrice			as "UnitPrice"
					        ,pf.WriteOff			as "WriteOff"
					        ,pf.PackageType		    as "PackageType"
					        ,pf.BillOfGoods		    as "BillOfGoods"
					        ,pf.Descr				as "Descr"
            		        ,pf.NotUseInPackage	    as "NotUseInPackage"
            		        ,pf.Appointment	        as "Appointment"
                        )
                    )
                )
                from PHARM.T_PRODUCT_FORM pf
                into _Result
                where pf.ProductFormID = "ID";
            end if; --_OperationType <> 'Delete'

            INSERT INTO RDS.T_RABBIT_SEND_OBJECT
            (RabbitSendObjectID ,QueueName ,Content ,MessageID ,Headers ,ContentEncoding ,Type ,UserID ,State ,CreateDateTime ,OwnerID)
                select gen_random_uuid()--(<RabbitSendObjectID, uniqueidentifier,>
                      ,s.ServerName--,<QueueName, varchar(255),>
                      ,converttexttobinaryzip(_Result, '')--,<Content, text,>
                      ,null--,<MessageID, varchar(255),>
                      ,null--,<Headers, varchar(255),>
                      ,_ContentEncoding--,<ContentEncoding, varchar(255),>
                      ,_Type--,<Type, varchar(255),>
                      ,"User"--,<UserID, varchar(255),>
                      ,0--,<State, int,>)
                      ,"dateTime"--,<CreateDateTime, datetime,>)
                      ,_OwnerID--,<OwnerID, varchar(255),>
                from RDS.T_SYNC_SERVERS s
                where ("QueueName" is not null and s.ServerName = "QueueName") or ("QueueName" is null and s.ServerType in (0, 1)); -- Рассылаем пакет всем подписанным серверам AKUZ-а и APS, если не указана очередь

        elsif ("Xml" is not null)
        then
            _OperationType := (xpath('/PRODUCT_FORM/header[1]/TYPE[1]/text()', "Xml"::xml))[1]::varchar(6);
            _OwnerID := (xpath('/PRODUCT_FORM/header[1]/ID[1]/text()', "Xml"::xml))[1]::text::uuid;
            _Result = "Xml"; 

            drop table if exists Tmp_PRODUCT_FORMS;
            create temp table Tmp_PRODUCT_FORMS
            as
            select
                 (xpath('/PRODUCT_FORM/ID[1]/text()', x))[1]::text::uuid                        as ID
			    ,(xpath('/PRODUCT_FORM/OrdinalNumber[1]/text()', x))[1]::text::int              as OrdinalNumber
			    ,(xpath('/PRODUCT_FORM/Amount[1]/text()', x))[1]::text::decimal(12,2)           as Amount
                ,(xpath('/PRODUCT_FORM/UnitPrice[1/text()', x))[1]::text::boolean		        as UnitPrice
                ,(xpath('/PRODUCT_FORM/WriteOff[1/text()', x))[1]::text::boolean		        as WriteOff
                ,(xpath('/PRODUCT_FORM/PackageType[1/text()', x))[1]::text::uuid		        as PackageType
                ,(xpath('/PRODUCT_FORM/BillOfGoods[1/text()', x))[1]::text::uuid		        as BillOfGoods
				,(xpath('/PRODUCT_FORM/Descr[1]/text()', x))[1]::varchar(500)			        as Descr
				,(xpath('/PRODUCT_FORM/NotUseInPackage[1]/text()', x))[1]::text::boolean	    as NotUseInPackage
				,(xpath('/PRODUCT_FORM/Appointment[1]/text()', x))[1]::text::boolean	        as Appointment
            from unnest(xpath('PRODUCT_FORM/body/PRODUCT_FORMS/PRODUCT_FORM', "Xml"::xml)) x
            where _OperationType = 'FTLoad';

            drop table if exists Tmp_PRODUCT_FORM;
            Create Temp Table TmpPRODUCT_FORM
			as
			select
				(xpath('/header[1]/ID[1]/text()', x1))[1]::text::uuid				            as ID
			    ,(xpath('/body[1]/OrdinalNumber[1]/text()', x2))[1]::text::int		            as OrdinalNumber
			    ,(xpath('/body[1]/Amount[1]/text()', x2))[1]::text::decimal(12,2)	            as Amount
			    ,(xpath('/body[1]/UnitPrice[1]/text()', x2))[1]::text::boolean		            as UnitPrice
			    ,(xpath('/body[1]/WriteOff[1]/text()', x2))[1]::text::boolean		            as WriteOff
			    ,(xpath('/body[1]/PackageType[1]/text()', x2))[1]::text::uuid    	            as PackageType
			    ,(xpath('/body[1]/BillOfGoods[1]/text()', x2))[1]::text::uuid    	            as BillOfGoods
			    ,(xpath('/body[1]/Descr[1]/text()', x2))[1]::varchar(500)			            as Descr
			    ,(xpath('/body[1]/NotUseInPackage[1]/text()', x))[1]::text::boolean     	    as NotUseInPackage
			    ,(xpath('/body[1]/Appointment[1]/text()', x))[1]::text::boolean     	        as Appointment
			from
			(
				select 
					(xpath('/PRODUCT_FORM/header', "Xml"::xml))[1] as x1
				,	(xpath('/PRODUCT_FORM/body', "Xml"::xml))[1] as x2
			) t1
			where _OperationType <> 'FTLoad';

            if (_OperationType = 'FTLoad')
            then
                delete from PHARM.T_PRODUCT_FORM
                where not exists (select * from Tmp_PRODUCT_FORMS nmf where nmf.ID = ProductFormID);

                update PHARM.T_PRODUCT_FORM pf
		        set OrdinalNumber = nmf.OrdinalNumber
				    ,Amount = nmf.Amount
				    ,UnitPrice = nmf.UnitPrice
				    ,WriteOff = nmf.WriteOff
				    ,PackageType = nmf.PackageType
				    ,BillOfGoods = nmf.BillOfGoods
				    ,Descr = nmf.Descr
				    ,NotUseInPackage = coalesce(nmf.NotUseInPackage, '0')
				    ,Appointment = coalesce(nmf.Appointment, '0')
		        from Tmp_PRODUCT_FORMS nmf 
				where nmf.ID = pf.ProductFormID;
				
				-- Загружаем новые
				INSERT INTO PHARM.T_PRODUCT_FORM
                    (ProductFormID, OrdinalNumber, Amount, UnitPrice, WriteOff, PackageType, BillOfGoods, Descr, NotUseInPackage, Appointment)
		        SELECT 
                    nmf.ID, 
                    nmf.OrdinalNumber, 
                    nmf.Amount, 
                    nmf.UnitPrice, 
                    nmf.WriteOff, 
                    nmf.PackageType, 
                    nmf.BillOfGoods, 
                    nmf.Descr, 
                    coalesce(nmf.NotUseInPackage, '0'),
                    coalesce(nmf.Appointment, '0')
		        from Tmp_PRODUCT_FORMS nmf
		        where not exists(select * from PHARM.T_PRODUCT_FORM pf where pf.ProductFormID = nmf.ID);
            elsif (_OperationType = 'Delete')
            then
                delete from PHARM.T_PRODUCT_FORM
                where exists (select * from Tmp_PRODUCT_FORM nmf where nmf.ID = ProductFormID);
            else
                update PHARM.T_PRODUCT_FORM pf
		        set	
                     OrdinalNumber = nmf.OrdinalNumber
				    ,Amount = nmf.Amount
				    ,UnitPrice = nmf.UnitPrice
				    ,WriteOff = nmf.WriteOff
				    ,PackageType = nmf.PackageType
				    ,BillOfGoods = nmf.BillOfGoods
				    ,Descr = nmf.Descr
				    ,NotUseInPackage = coalesce(nmf.NotUseInPackage, '0')
				    ,Appointment = coalesce(nmf.Appointment, '0')
		        from TmpPRODUCT_FORM nmf 
				where nmf.ID = pf.ProductFormID and _OperationType = 'Update';

                INSERT INTO PHARM.T_PRODUCT_FORM
                    (ProductFormID, OrdinalNumber, Amount, UnitPrice, WriteOff, PackageType, BillOfGoods, Descr, NotUseInPackage, Appointment)
		        SELECT 
                    nmf.ID, 
                    nmf.OrdinalNumber, 
                    nmf.Amount, 
                    nmf.UnitPrice, 
                    nmf.WriteOff, 
                    nmf.PackageType, 
                    nmf.BillOfGoods, 
                    nmf.Descr, 
                    coalesce(nmf.NotUseInPackage, '0'),
                    coalesce(nmf.Appointment, '0')
		        from Tmp_PRODUCT_FORM nmf
		        where not exists (select * from PHARM.T_PRODUCT_FORM pf where pf.ProductFormID = nmf.ID) and _OperationType = 'Insert';
            end if;

            insert into RDS.T_RABBIT_SEND_OBJECT
                (RabbitSendObjectID, QueueName, Content, MessageID, Headers, ContentEncoding, Type, UserID, State, CreateDateTime, OwnerID)    
            select gen_random_uuid()--(<RabbitSendObjectID, uniqueidentifier,>
              ,s.ServerName--,<QueueName, varchar(255),>
              ,ConvertTextToBinaryZip(_Result, '')--,<Content, text,>
              ,null--,<MessageID, varchar(255),>
              ,null--,<Headers, varchar(255),>
              ,_ContentEncoding--,<ContentEncoding, varchar(255),>
              ,_Type--,<Type, varchar(255),>
              ,"User"--,<UserID, varchar(255),>
              ,0--,<State, int,>)
              ,"dateTime"--,<CreateDateTime, datetime,>)
              ,_OwnerID--,<OwnerID, varchar(255),>
            from RDS.T_SYNC_SERVERS s
            where s.ServerName <> "QueueName" and _OperationType <> 'FTLoad';
        end if;
    End;
End;
$$;