drop procedure if exists sp_RDS_DoseView;

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_RDS_DoseView]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[sp_RDS_DoseView]
GO
-- =============================================
-- Author:      Vitacore (с)
-- Create date: 2015-07-29
-- Description: Выгрузка/загрузка справочника доз в(из) RDS
-- =============================================
create procedure sp_RDS_DoseView
(
     "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
)
    language plpgsql
as
$$
Begin
    declare 
         _Result text;
        ,_ContentEncoding varchar(255) := 'utf-8';
        ,_Type varchar(255) := 'DOSE_VIEW';
        ,_OperationType varchar(255);
        ,_OwnerID uuid := uuid_generate_v4();
        _EntId int;
    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_DOSE_VIEW where Code = "ID"))
		    then
		    	_OperationType = 'Update';
		    end if;
		    elsif ("IsNew" = '0' and not exists (select * from PHARM.T_DOSE_VIEW where Code = "ID"))
            then
                _OperationType := 'Delete';
		    	select 
		    	xmlelement
		    	(
		    		name "DOSE_VIEW",
		    		xmlelement
		    		(
		    			name header,
		    			xmlforest
		    			(	
		    				_OperationType as "TYPE",
		    				"ID" as "ID"
		    			)
		    		)
		    	)
		    	into _Result;
            end if;
            
            if (_OperationType = 'FTLoad')
            then    
                select 
                xmlelement
                (
                    name "DOSE_VIEW",
                    xmlelement
                    (
                        name header,
                        xmlforest('FTLoad' as "TYPE")
                    )
                    xmlelement
                    (
                        name body,
                        xmlelement
                        (
                            name "",
                            xmlforest
                            (
                                 dv.Code				as "ID"
                                ,dv.Name				as "Name"
                                ,dv.FullName			as "FullName"
                                ,dv.OtherID			    as "OtherID"
                                ,dv.ViewType			as "ViewType"
							    ,RegCode				as "RegCode"
							    ,FedCode				as "FedCode"
							    ,er.IdEHR				as "EhrRusS0JLHGTLBJ"
							    ,RRB.CurrVersion		as "EhrRusS0JLHGTLBJVer"
                            )
                        )
                    )
                )
                into _Result
                from PHARM.T_DOSE_VIEW dv
                    left join EHR.T_EHR_RUS_S0JLHGTLBJ er on er.EhrRusID = dv.EhrRusS0JLHGTLBJ
					left join EHR.T_EHR_RUS_REF_BOOKS RRB ON RRB.EhrRusRefBooksID = er.EhrRusRefBooks;
            end if;
            elsif (_OperationType <> 'Delete')
            then
                select
                xmlelement
                (
                    name "DOSE_VIEW",
                    xmlelement
                    (
                        name header,
                        xmlforest
                        (
                            _OperationType as "TYPE",
                            "ID" as "ID"
                        )
                    )
                    xmlelement
                    (
                        name body,
                        xmlforest
                        (
                             dv.Name					as "Name"
                            ,dv.FullName				as "FullName"
                            ,dv.OtherID				    as "OtherID"
                            ,dv.ViewType				as "ViewType"
					        ,RegCode					as "RegCode"
					        ,FedCode					as "FedCode"
					        ,er.IdEHR					as "EhrRusS0JLHGTLBJ"
					        ,RRB.CurrVersion			as "EhrRusS0JLHGTLBJVer"
                        )
                    )
                )
                into _Result
                from PHARM.T_DOSE_VIEW dv
			        left join EHR.T_EHR_RUS_S0JLHGTLBJ er on er.EhrRusID = dv.EhrRusS0JLHGTLBJ
					left join EHR.T_EHR_RUS_REF_BOOKS RRB ON RRB.EhrRusRefBooksID = er.EhrRusRefBooks
                where dv.Code = "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,>
              ,"QueueName"--,<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),>
        end if; --xml is null
        elsif ("Xml" is not null)
        then
             _OperationType := (xpath('/DOSE_VIEW/header[1]/TYPE[1]/text()', "Xml"))[1]::varchar(6);
            _OwnerID := (xpath('/DOSE_VIEW/header[1]/ID[1]/text()', "Xml"))[1]::text::uuid;
            _Result = cast("Xml" as text); 

			-- Generate table Tmp_DOSE_VIEW_LIST
			Drop Table If Exists Tmp_DOSE_VIEW_LIST;
			Create Temp Table Tmp_DOSE_VIEW_LIST
			as
			SELECT
				(xpath('/DOSE_VIEW/ID[1]/text()', x))[1]::text::int                     as ID
			,	(xpath('/DOSE_VIEW/Name[1]/text()', x))[1]::varchar(256)                as Name
			,	(xpath('/DOSE_VIEW/FullName[1]/text()', x))[1]::varchar(4000)           as FullName
			,	(xpath('/DOSE_VIEW/OtherID[1]/text()', x))[1]::text::int                as OtherID
			,	(xpath('/DOSE_VIEW/ViewType[1]/text()', x))[1]::text::int               as ViewType
			,	(xpath('/DOSE_VIEW/RegCode[1]/text()', x))[1]::varchar(50)              as RegCode
			,	(xpath('/DOSE_VIEW/FedCode[1]/text()', x))[1]::varchar(50)              as FedCode
			,	(xpath('/DOSE_VIEW/EhrRusS0JLHGTLBJ[1]/text()', x))[1]::text::int       as EhrRusS0JLHGTLBJ
			,	(xpath('/DOSE_VIEW/EhrRusS0JLHGTLBJVer[1]/text()', x))[1]::varchar(8)   as EhrRusS0JLHGTLBJVer
			from unnest(xpath('/DOSE_VIEW/body/DOSE_VIEW_LIST/DOSE_VIEW', "Xml")) as x
			where _OperationType = 'FTLoad';	

            -- Generate table Tmp_DOSE_VIEW
			Drop Table If Exists Tmp_DOSE_VIEW;
			Create Temp Table Tmp_DOSE_VIEW
			as
			SELECT	
                 (xpath('/header[1]/ID[1]/text()', t.x1))[1]::text::int                 as ID
			    ,(xpath('/body[1]/Name[1]/text()', t.x2))[1]::varchar(256)              as Name
			    ,(xpath('/body[1]/FullName[1]/text()', t.x2))[1]::varchar(4000)         as FullName			
			    ,(xpath('/body[1]/OtherID[1]/text()', t.x2))[1]::text::int              as OtherID
			    ,(xpath('/body[1]/ViewType[1]/text()', t.x2))[1]::text::int             as ViewType
			    ,(xpath('/body[1]/RegCode[1]/text()', t.x2))[1]::varchar(50)            as RegCode
			    ,(xpath('/body[1]/FedCode[1]/text()', t.x2))[1]::varchar(50)            as FedCode
			    ,(xpath('/body[1]/EhrRusS0JLHGTLBJ[1]/text()', t.x2))[1]::text::int     as EhrRusS0JLHGTLBJ
			    ,(xpath('/body[1]/EhrRusS0JLHGTLBJVer[1]/text()', t.x2))[1]::varchar(8) as EhrRusS0JLHGTLBJVer
			from
			(
				select   unnest(xpath('/DOSE_VIEW/header', x)) as x1
				        ,unnest(xpath('/DOSE_VIEW/body', x)) as x2
				from unnest(xpath('/DOSE_VIEW', "Xml"::xml)) x
			) t
			where _OperationType <> 'FTLoad';

            if (_OperationType = 'FTLoad')
            then
                update PHARM.T_DOSE_VIEW dv
                set 
                     dv.Name = dvl.Name
				    ,dv.FullName = dvl.FullName
				    ,dv.OtherID = dvl.OtherID
				    ,dv.ViewType = dvl.ViewType
			        ,dv.RegCode = dvl.RegCode
			        ,dv.FedCode = dvl.FedCode
                from Tmp_DOSE_VIEW_LIST dvl 
                where dvl.ID = dv.Code
				left join lateral 
                (
				    select er.EhrRusID 
					from EHR.T_EHR_RUS_S0JLHGTLBJ er
					left join EHR.T_EHR_RUS_REF_BOOKS RRB ON RRB.EhrRusRefBooksID = er.EhrRusRefBooks
					where er.IdEHR = dvl.EhrRusS0JLHGTLBJ and RRB.CURRVERSION = dvl.EhrRusS0JLHGTLBJVer limit 1
				) ehr on true;

                INSERT INTO PHARM.T_DOSE_VIEW
                       (Code, Name, FullName, OtherID, ViewType, RegCode, FedCode, EhrRusS0JLHGTLBJ)
                SELECT
                    dvl.ID
				    ,dvl.Name
				    ,dvl.FullName
				    ,dvl.OtherID
				    ,dvl.ViewType
				    ,RegCode
				    ,FedCode
				    ,ehr.EhrRusID
                from Tmp_DOSE_VIEW_LIST dvl
			    left join lateral 
                (
			    	select er.EhrRusID 
			    	from EHR.T_EHR_RUS_S0JLHGTLBJ er
			    	left join EHR.T_EHR_RUS_REF_BOOKS RRB ON RRB.EhrRusRefBooksID = er.EhrRusRefBooks
			    	where er.IdEHR = dvl.EhrRusS0JLHGTLBJ and RRB.CURRVERSION = dvl.EhrRusS0JLHGTLBJVer limit 1
			    ) ehr on true
                where not exists (select * from PHARM.T_DOSE_VIEW dv where dv.Code = dvl.ID);
            end if;
            else
            then
                update PHARM.T_DOSE_VIEW d
						set	Name = dv.Name
							,FullName = dv.FullName
							,OtherID = dv.OtherID
							,ViewType = dv.ViewType
							,RegCode = dv.RegCode
				    		,FedCode = dv.FedCode
				    		,EhrRusS0JLHGTLBJ = ehr.EhrRusID
						from Tmp_DOSE_VIEW dv 
                join #DOSE_VIEW dv on 
				left join lateral
                (
					select er.EhrRusID 
					from EHR.T_EHR_RUS_S0JLHGTLBJ er
						LEFT JOIN EHR.T_EHR_RUS_REF_BOOKS RRB ON RRB.EhrRusRefBooksID = er.EhrRusRefBooks
					where er.IdEHR = dv.EhrRusS0JLHGTLBJ and RRB.CURRVERSION = dv.EhrRusS0JLHGTLBJVer limit 1
				) ehr
                where dv.ID = d.Code and _OperationType = 'Update';

            -- Загружаем новый T_DOSE_VIEW
                INSERT INTO PHARM.T_DOSE_VIEW
                           (Code, Name, FullName, OtherID, ViewType, RegCode, FedCode, EhrRusS0JLHGTLBJ)
                SELECT
                     dv.ID
			        ,dv.Name
			        ,dv.FullName
			        ,dv.OtherID
			        ,dv.ViewType
			        ,dv.RegCode
			        ,dv.FedCode
			        ,ehr.EhrRusID
                from Tmp_DOSE_VIEW dv
			    left join lateral
                (
			    	select er.EhrRusID 
			    	from EHR.T_EHR_RUS_S0JLHGTLBJ er
			    		LEFT JOIN EHR.T_EHR_RUS_REF_BOOKS RRB ON RRB.EhrRusRefBooksID = er.EhrRusRefBooks
			    	where er.IdEHR = dv.EhrRusS0JLHGTLBJ and RRB.CURRVERSION = dv.EhrRusS0JLHGTLBJVer limit 1
			    ) ehr on true
                where _OperationType = 'Insert' and not exists (select * from PHARM.T_DOSE_VIEW dvv where dvv.Code = dv.ID);
            end if;

            _EntId := (select EntityID from VCLib.T_ENTITIES where EntName = 'DOSE_VIEW' and Schema = 'PHARM' limit 1);

            if exists (select * from VCLIB.T_GEN_NUMBERS where EntityID = _EntId and NumberName = 'PHARM.DOSE_VIEW/0' Limit 1)
			then
				---- генерируем след. номер
				--perform public.sp_GenerateNumber(@EntId, 'PHARM.C_TRN');
				update VCLIB.T_GEN_NUMBERS
					set NextNumber = coalesce((select MAX(Code) from PHARM.T_DOSE_VIEW where ViewType = 0), 1) --+ 1
				where EntityID = _EntId and NumberName = 'PHARM.DOSE_VIEW/0';
			end if;
			else
			then
				INSERT INTO VCLIB.T_GEN_NUMBERS (GenNumbersID, EntityID, NumberName, NextNumber)
				VALUES (uuid_generate_v4(), _EntId, 'PHARM.DOSE_VIEW/0', coalesce((select MAX(Code) from PHARM.T_DOSE_VIEW where iewType = 0), 1));
			end if;

			if exists (select * from VCLIB.T_GEN_NUMBERS where EntityID = _EntId and NumberName = 'PHARM.DOSE_VIEW/1' Limit 1)
			then
				update VCLIB.T_GEN_NUMBERS
					set NextNumber = coalesce((select MAX(Code) from PHARM.T_DOSE_VIEW where ViewType = 1), 1) --+ 1
				where EntityID = _EntId and NumberName = 'PHARM.DOSE_VIEW/1';
			end if;
			else
			then
				INSERT INTO VCLIB.T_GEN_NUMBERS (GenNumbersID, EntityID, NumberName, NextNumber)
				VALUES(uuid_generate_v4(), _EntId, 'PHARM.DOSE_VIEW/1', coalesce((select MAX(Code) from PHARM.T_DOSE_VIEW where iewType = 1), 1));
			end if;
			
			if exists (select * from VCLIB.T_GEN_NUMBERS where EntityID = _EntId and NumberName = 'PHARM.DOSE_VIEW/2' Limit 1)
			then
				update VCLIB.T_GEN_NUMBERS
				set NextNumber = coalesce((select MAX(Code) from PHARM.T_DOSE_VIEW where ViewType = 2), 1) --+ 1
				where EntityID = _EntId
				and NumberName = 'PHARM.DOSE_VIEW/2';
			end if;
			else
			then
				INSERT INTO VCLIB.T_GEN_NUMBERS (GenNumbersID, EntityID, NumberName, NextNumber)
				VALUES(uuid_generate_v4(), _EntId, 'PHARM.DOSE_VIEW/2', coalesce((select MAX(Code) from PHARM.T_DOSE_VIEW where iewType = 2), 1));
			end if;

			if exists (select * from VCLIB.T_GEN_NUMBERS where EntityID = _EntId and NumberName = 'PHARM.DOSE_VIEW/3' Limit 1)
			then
				update VCLIB.T_GEN_NUMBERS
				set NextNumber = coalesce((select MAX(Code) from PHARM.T_DOSE_VIEW where ViewType = 3), 1) --+ 1
				where EntityID = _EntId
				and NumberName = 'PHARM.DOSE_VIEW/3';
			else
				INSERT INTO VCLIB.T_GEN_NUMBERS(GenNumbersID, EntityID, NumberName, NextNumber)
				VALUES(uuid_generate_v4(), _EntId, 'PHARM.DOSE_VIEW/3', coalesce((select MAX(Code) from PHARM.T_DOSE_VIEW where iewType = 3), 1));
			end if;

			if exists (select * from VCLIB.T_GEN_NUMBERS where EntityID = _EntId and NumberName = 'PHARM.DOSE_VIEW/4' Limit 1)
			then
				update VCLIB.T_GEN_NUMBERS
				set NextNumber = coalesce((select MAX(Code) from PHARM.T_DOSE_VIEW where ViewType = 4), 1) --+ 1
				where EntityID = _EntId
				and NumberName = 'PHARM.DOSE_VIEW/4';
			end if;
			else
			then
				INSERT INTO VCLIB.T_GEN_NUMBERS (GenNumbersID, EntityID, NumberName, NextNumber)
				VALUES(uuid_generate_v4(), _EntId, 'PHARM.DOSE_VIEW/4', coalesce((select MAX(Code) from PHARM.T_DOSE_VIEW where iewType = 4), 1));
			end if;
			
			
			if exists (select * from VCLIB.T_GEN_NUMBERS where EntityID = _EntId and NumberName = 'PHARM.DOSE_VIEW/5' Limit 1)
			then
				update VCLIB.T_GEN_NUMBERS
				set NextNumber = coalesce((select MAX(Code) from PHARM.T_DOSE_VIEW where ViewType = 5), 1) --+ 1
				where EntityID = _EntId
				and NumberName = 'PHARM.DOSE_VIEW/5';
			end if;
			else
			then
				INSERT INTO VCLIB.T_GEN_NUMBERS(GenNumbersID, EntityID, NumberName, NextNumber)
				VALUES(uuid_generate_v4(), _EntId, 'PHARM.DOSE_VIEW/5', coalesce((select MAX(OtherID) from PHARM.T_DOSE_VIEW where iewType = 5), 1));
			end if;

			-- Drop tables
			drop table if exists Tmp_DOSE_VIEW;
			drop table if exists Tmp_DOSE_VIEW_LIST;
        end if;
    End;
End;
$$;