DROP PROCEDURE sp_GetSetSeries;
-- =============================================
-- Author:      Vitacore (с)
-- Create date: 2017-12-21
-- Description: Выгрузка/загрузка справочника серии в(из) RDS
-- =============================================
CREATE PROCEDURE sp_GetSetSeries
(
     "Xml" text DEFAULT NULL
    ,"IsNew" boolean DEFAULT '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) := 'SERIES';
        ,_OperationType varchar(6);
        ,_OwnerID uuid := "ID";
    Begin
        "dateTime" := Coalesce("dateTime", Current_TimeStamp::TIMESTAMP);

        if ("Xml" is null)
        then
            if ("ID" is null)
		    then 
		    	_OperationType := 'FTLoad';
		    end if;
		    elsif ("IsNew" = '1')
		    then
		    	_OperationType = 'Insert';
		    end if;
		    elsif ("IsNew" = '0' and exists (select * from PHARM.T_SERIES where SeriesID = "ID"))
		    then
		    	_OperationType = 'Update';
		    end if;
		    elsif ("IsNew" = '0' and not exists (select * from PHARM.T_SERIES where SeriesID = "ID"))
            then
                _OperationType := 'Delete';
		    	select 
		    	xmlelement
		    	(
		    		name "SERIES",
		    		xmlelement
		    		(
		    			name header,
		    			xmlforest
		    			(	
		    				_OperationType as "TYPE",
		    				"ID" as "ID"
		    			)
		    		)
		    	)
		    	into _Result;
            end if;

            if (_OperationType = 'FTLoad')
            then
                select
                xmlelement
                (
                    name "SERIES",
                    xmlelement
                    (
                        name header,
                        xmlforest('FTLoad' as "TYPE")
                    ),
                    xmlelement
                    (
                        name body,
                        xmlelement
                        (
                            name "SERIESS",
                            xmlelement
                            (
                                name "SERIES",
                                xmlforest
                                (
                                     s.SeriesID		                                    as "ID"
            		    		    ,s.SerialNumber			                            as "SerialNumber"
            		    		    ,to_char(s.RegistrationDate, 'YYYY-MM-DD')			as "RegistrationDate"
            		    		    ,to_char(s.ExpirationDate, 'YYYY-MM-DD')			as "ExpirationDate"
            		    		    ,to_char(s.DurationOfCertificate, 'YYYY-MM-DD')	    as "DurationOfCertificate"
            		    		    ,s.CertificateNumber	                            as "CertificateNumber"
            		    		    ,s.IdentificationNumber	                            as "IdentificationNumber"
                                    ,s.Active				                            as "Active"
            		    		    ,s.BillOfGoods			                            as "BillOfGoods"
                                )
                            )
                        )
                    )
                )
                from pharm.T_SERIES s
                into _Result;
            end if;
            elsif (_OperationType <> 'Delete')
            then
                select
                xmlelement
                (
                    name "SERIES",
                    xmlelement
                    (
                        name header,
                        xmlforest
                        (
                            _OperationType as "TYPE",
                            "ID" as "ID"
                        )
                    ),
                    xmlelement
                    (
                        name body,
                        xmlforest
                        (
                             s.SeriesID		                                    as "ID"
            		        ,s.SerialNumber			                            as "SerialNumber"
            		        ,to_char(s.RegistrationDate, 'YYYY-MM-DD')			as "RegistrationDate"
            		        ,to_char(s.ExpirationDate, 'YYYY-MM-DD')			as "ExpirationDate"
            		        ,to_char(s.DurationOfCertificate, 'YYYY-MM-DD')	    as "DurationOfCertificate"
            		        ,s.CertificateNumber	                            as "CertificateNumber"
            		        ,s.IdentificationNumber	                            as "IdentificationNumber"
                            ,s.Active				                            as "Active"
            		        ,s.BillOfGoods			                            as "BillOfGoods"  
                        )
                    )
                )
                into _Result
                from PHARM.T_SERIES s
                where s.SeriesID = "ID";
            end if;

            INSERT INTO RDS.T_RABBIT_SEND_OBJECT
            (RabbitSendObjectID, QueueName, Content, MessageID, Headers, ContentEncoding, Type, UserID, State, CreateDateTime, OwnerID)
        select uuid_generate_v4()--(<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, если не указана очередь
        end if; --xml is null
        elsif ("Xml" is not null)
        then
             _OperationType := (xpath('/MEDICATION_FORMS/header[1]/TYPE[1]/text()', "Xml"::xml))[1]::varchar(6);
            _OwnerID := (xpath('/MEDICATION_FORMS/header[1]/ID[1]/text()', "Xml"::xml))[1]::text::uuid;
            _Result = "Xml"; 

            Drop Table If Exists Tmp_SERIESS;
			Create Temp Table Tmp_SERIESS
			as
            select (xpath('SERIES/ID[1]/text()', x))[1]::text::UUID										        as ID
                  ,(xpath('SERIES/Active[1]/text()', x))[1]::text::boolean										as Active
                  ,(xpath('SERIES/SerialNumber[1]/text()', x))[1]::varchar(255)								    as SerialNumber
            	  ,to_timestamp((xpath('SERIES/RegistrationDate[1]/text()', x))[1]::text, 'YYYY-MM-DD')         as RegistrationDate
            	  ,to_timestamp((xpath('SERIES/ExpirationDate[1]/text()', x))[1]::text, 'YYYY-MM-DD')			as ExpirationDate
            	  ,to_timestamp((xpath('SERIES/DurationOfCertificate[1]/text()', x))[1]::text, 'YYYY-MM-DD')    as DurationOfCertificate
            	  ,(xpath('SERIES/CertificateNumber[1]/text()', x))[1]::text::int								as CertificateNumber
            	  ,(xpath('SERIES/IdentificationNumber[1]/text()', x))[1]::text::int							as IdentificationNumber
            	  ,(xpath('SERIES/BillOfGoods[1]/text()', x))[1]::text::uuid							        as BillOfGoods
            from unnest(xpath('/SERIES/body/SERIESS/SERIES', "Xml")) as x
            where _OperationType = 'FTLoad';
            
			Drop Table If Exists Tmp_SERIES;
			Create Temp Table Tmp_SERIES
			as
            select (xpath('./header[1]/ID[1]/text()', t.x1))[1]::text::int                                          as ID
                  ,(xpath('./body[1]/Active[1]/text()', t.x2))[1]::text::boolean									as Active
                  ,(xpath('./body[1]/SerialNumber[1]/text()', t.x2))[1]::varchar(255)							    as SerialNumber
            	  ,to_timestamp((xpath('./body[1]/RegistrationDate[1]/text()', t.x2))[1]::text, 'YYYY-MM-DD')       as RegistrationDate
            	  ,to_timestamp((xpath('./body[1]/ExpirationDate[1]/text()', t.x2))[1]::text, 'YYYY-MM-DD')		    as ExpirationDate
            	  ,to_timestamp((xpath('./body[1]/DurationOfCertificate[1]/text()', t.x2))[1]::text, 'YYYY-MM-DD')  as DurationOfCertificate
            	  ,(xpath('./body[1]/CertificateNumber[1]/text()', t.x2))[1]::text::int							    as CertificateNumber
            	  ,(xpath('./body[1]/IdentificationNumber[1]/text()', t.x2))[1]::text::int							as IdentificationNumber
            	  ,(xpath('./body[1]/BillOfGoods[1]/text()', t.x2))[1]::text::uuid							        as BillOfGoods
            from
			(
				select	unnest(xpath('/SERIES/header', x)) as x1
				,	unnest(xpath('/SERIES/body', x)) as x2
				from unnest(xpath('/SERIES', "Xml"::xml)) x
			) t
            where _OperationType <> 'FTLoad';

            if (_OperationType = 'FTLoad')
            then
                delete from PHARM.T_SERIES s
                where not exists (select * from Tmp_SERIESS nmfwhere nmf.ID = s.SeriesID);

                update PHARM.T_SERIES s
                set 
                     Active = nmf.Active
            	    ,SerialNumber = nmf.SerialNumber
            	    ,RegistrationDate = nmf.RegistrationDate
            	    ,ExpirationDate = nmf.ExpirationDate
            	    ,DurationOfCertificate = nmf.DurationOfCertificate
            	    ,CertificateNumber = nmf.CertificateNumber
            	    ,IdentificationNumber = nmf.IdentificationNumber
            	    ,BillOfGoods = nmf.BillOfGoods
                from Tmp_SERIESS nmf 
                where nmf.ID = s.SeriesID;

                INSERT INTO PHARM.T_SERIES
                    (SeriesID,SerialNumber,RegistrationDate,ExpirationDate,DurationOfCertificate,CertificateNumber,IdentificationNumber,Active,BillOfGoods)
                SELECT
                           nmf.ID,
                           nmf.SerialNumber,
            			   nmf.RegistrationDate,
            			   nmf.ExpirationDate,
            			   nmf.DurationOfCertificate,
            			   nmf.CertificateNumber,
            			   nmf.IdentificationNumber,
            			   nmf.Active,
            			   nmf.BillOfGoods
                from Tmp_SERIESS nmf
                where not exists (select * from PHARM.T_SERIES s where s.SeriesID = nmf.ID);
            end if;
            elsif (_OperationType = 'Delete')
            then
                delete from PHARM.T_SERIES s
                using Tmp_SERIES nmf 
                where nmf.ID = s.SeriesID;
            end if;
            else
            then
                update PHARM.T_SERIES s
                set 
                     Active = nmf.Active
            	    ,SerialNumber = nmf.SerialNumber
            	    ,RegistrationDate = nmf.RegistrationDate
            	    ,ExpirationDate = nmf.ExpirationDate
            	    ,DurationOfCertificate = nmf.DurationOfCertificate
            	    ,CertificateNumber = nmf.CertificateNumber
            	    ,IdentificationNumber = nmf.IdentificationNumber
            	    ,BillOfGoods = nmf.BillOfGoods
                from Tmp_SERIES nmf
                where nmf.ID = s.SeriesID and _OperationType = 'Update';

                INSERT INTO PHARM.T_SERIES
                    (SeriesID,SerialNumber,RegistrationDate,ExpirationDate,DurationOfCertificate,CertificateNumber,IdentificationNumber,Active,BillOfGoods)
                SELECT
                    nmf.ID,
                    nmf.SerialNumber,
            	    nmf.RegistrationDate,
            	    nmf.ExpirationDate,
            	    nmf.DurationOfCertificate,
            	    nmf.CertificateNumber,
            	    nmf.IdentificationNumber,
            	    nmf.Active,
            	    nmf.BillOfGoods
                from Tmp_SERIES nmf
                where not exists (select * from PHARM.T_SERIES s where s.SeriesID = 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 uuid_generate_v4()--(<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';

        -- Drop tables
        drop table if exists Tmp_SERIES;
        drop table if exists Tmp_SERIESS;
        end if;
    End;
End;
$$;