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

CREATE OR REPLACE PROCEDURE APS.sp_RDS_Series
(
	"Xml" xml DEFAULT NULL::xml
,	"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) := 'SERIES';
		_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 TMP_T_SERIES;
			    CREATE TEMP TABLE TMP_T_SERIES
                (
                	 SeriesID				uuid
                	,SerialNumber			VARCHAR(255)
                	,RegistrationDate		TIMESTAMP
                	,ExpirationDate			TIMESTAMP
                	,DurationOfCertificate	TIMESTAMP
                	,CertificateNumber		VARCHAR(255)
                	,IdentificationNumber	VARCHAR(255)
                	,BillOfGoods			uuid
                	,Active					boolean
                );
                
                _sql := 'INSERT INTO TMP_T_SERIES(
                                SeriesID
                				,SerialNumber
                				,RegistrationDate
                				,ExpirationDate
                				,DurationOfCertificate
                				,CertificateNumber
                				,IdentificationNumber
                				,BillOfGoods
                				,Active)
                			 SELECT * 
                             FROM dblink(''dbname=' || "PATH" 
				                || ''', SELECT
                                SeriesID
                				,SerialNumber
                				,RegistrationDate
                				,ExpirationDate
                				,DurationOfCertificate
                				,CertificateNumber
                				,IdentificationNumber
                				,BillOfGoods
                				,Active
                              from PHARM.T_SERIES sss
                              '') AS tt(
                                 SeriesID				uuid
                	            ,SerialNumber			VARCHAR(255)
                	            ,RegistrationDate		TIMESTAMP
                	            ,ExpirationDate			TIMESTAMP
                	            ,DurationOfCertificate	TIMESTAMP
                	            ,CertificateNumber		VARCHAR(255)
                	            ,IdentificationNumber	VARCHAR(255)
                	            ,BillOfGoods			uuid
                	            ,Active					boolean
                            )';
                EXECUTE _sql;
                
                --- Удаление
                DELETE FROM PHARM.T_SERIES S
                WHERE NOT EXISTS
                    (
                        SELECT *
                			FROM TMP_T_SERIES nmf
                        WHERE nmf.SeriesID = 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_T_SERIES nmf 
                where nmf.SeriesID = s.SeriesID;
                
                -- Загружаем новые
                INSERT INTO PHARM.T_SERIES
                		(	
                			 SeriesID
                			,SerialNumber
                			,RegistrationDate
                			,ExpirationDate
                			,DurationOfCertificate
                			,CertificateNumber
                			,IdentificationNumber
                			,Active
                			,BillOfGoods
                		)
                	SELECT
                			nmf.SeriesID,
                			nmf.SerialNumber,
                			nmf.RegistrationDate,
                			nmf.ExpirationDate,
                			nmf.DurationOfCertificate,
                			nmf.CertificateNumber,
                			nmf.IdentificationNumber,
                			nmf.Active,
                			nmf.BillOfGoods
                	from TMP_T_SERIES nmf
                	WHERE NOT EXISTS
                	    (
                	        SELECT *
                				FROM PHARM.T_SERIES s
                	        WHERE s.SeriesID = nmf.SeriesID
                	    );
            
             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_SERIES c where c.SeriesID = "ID")
				    then
				    	_OperationType := _UPDATE;
				    elsif "IsNew" = '0' and not exists(select* from Pharm.T_SERIES c where c.SeriesID = "ID")
				    then
				    	_OperationType := _DELETE;
				    	_Result := XMLELEMENT(name "SERIES", XMLELEMENT(name header, XMLFOREST(_OperationType as "TYPE", "ID" as "ID")));
				    end if;
            
                    -- Обработка операции
                    IF (_OperationType = _FTLOAD)
                    then
                        -- Первичная загрузка
					    _Result :=
					    XMLELEMENT
					    (
					    	name "SERIES"
					    ,	XMLELEMENT
					    	(
					    		name header
					    	,	XMLFOREST
					    		(
					    			_FTLOAD as "TYPE"
					    		)
					    	)
					    ,	XMLELEMENT
					    	(
					    		name body
					    	,	xmlelement
					    		(
					    			name "SERIESS"
					    		,	xmlelement
					    			(
					    				name "SERIES"
					    			,	(
					    					select	
					    					xmlagg
					    					(
					    						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
					    				)
					    			)
					    		)
					    	)
					    );

                    ELSIF(_OperationType <> _DELETE)
                    then
                        -- собираем SERIES
                         select 
					        XMLELEMENT
					        (
					        	name "SERIES"
					        ,	XMLFOREST
					        	(
					        		XMLFOREST
					        		(
					        			_OperationType as "TYPE"
					        		,	"ID" as "ID"
					        		) as header
					        	)
					        ,	XMLFOREST
					        	(
					        		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"
					        		) as body
					        	)
					        )
					        into _Result
					        from pharm.T_SERIES s
                         where s.SeriesID = "ID";

                    END if;
            
                    -- Отправка
                    INSERT INTO APS.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;
                ELSE 
                    IF ("Xml" is not null)
                    then
                        
                        -- Получаем тип операции
					    _OperationType := (xpath('/SERIES/header[1]/TYPE[1]/text()', "Xml"))[1]::varchar(6);
            
                        -- Generate table TMP_SERIESS
					    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;
            
                        -- Generate table TMP_SERIES
					    Drop Table If Exists TMP_SERIES;
					    Create Temp Table TMP_SERIES
					    as
                        select (xpath('./header[1]/ID[1]/text()', x1))[1]::text::int                                      as ID
                              ,(xpath('./body[1]/Active[1]/text()', x2))[1]::text::boolean									as Active
                              ,(xpath('./body[1]/SerialNumber[1]/text()', x2))[1]::varchar(255)							    as SerialNumber
            	    		  ,to_timestamp((xpath('./body[1]/RegistrationDate[1]/text()', x2))[1]::text, 'YYYY-MM-DD')      as RegistrationDate
            	    		  ,to_timestamp((xpath('./body[1]/ExpirationDate[1]/text()', x2))[1]::text, 'YYYY-MM-DD')		as ExpirationDate
            	    		  ,to_timestamp((xpath('./body[1]/DurationOfCertificate[1]/text()', x2))[1]::text, 'YYYY-MM-DD') as DurationOfCertificate
            	    		  ,(xpath('./body[1]/CertificateNumber[1]/text()', x2))[1]::text::int							as CertificateNumber
            	    		  ,(xpath('./body[1]/IdentificationNumber[1]/text()', x2))[1]::text::int							as IdentificationNumber
            	    		  ,(xpath('./body[1]/BillOfGoods[1]/text()', 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 nmf
                                    where 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
                                );

                        elsif(_OperationType = _DELETE)
                        then
                            -- Удаление
                            delete from PHARM.T_SERIES s
                                using TMP_SERIES nmf 
                            where nmf.ID = s.SeriesID;
                        else
                            -- update PHARM.T_SERIES
                            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 PHARM.T_SERIES
                            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;
            
                    -- Drop tables
                    DROP TABLE TMP_SERIES;
                    DROP TABLE TMP_SERIESS;
				end if;
			END if;
		end if;
	End;
End;
$$;