-- =============================================
-- Author:      Vitacore (с)
-- Create date: 2024-06-21
-- Description: Загрузка разрешений на выдачу рецептов из RDS 
-- =============================================

DROP PROCEDURE IF EXISTS sp_rds_directionaccessxml;

--CREATE PROCEDURE sp_rds_directionaccessxml
--(
--    "Xml" xml = null,    
--    "QueueName" varchar(100) = 'RDSSync',
--    "DateTime" TimeStamp = null,
--    "LpuTo" varchar(6) = null
--)
--    language plpgsql
--as
--$$
--Begin

--	"DateTime" := coalesce("DateTime", Current_TimeStamp::TIMESTAMP);

--	IF "Xml" is not null
--	then
--		DELETE FROM APS.T_DLO_ACCESSED_POSTS;                     

--		-- Generate table Tmp_DLO_ACCESSED_POSTS
--		Drop Table If Exists Tmp_DLO_ACCESSED_POSTS;

--			SELECT
--				nullif(trim(xpath('/DLO_ACCESSED_POSTS/ID[1]/text()', x)::text,'{}"'),'')::UUID as ID
--				,nullif(trim(xpath('/DLO_ACCESSED_POSTS/PostCode[1]/text()', x)::text,'{}"'),'')::varchar(255) as PostCode
--				,nullif(trim(xpath('/DLO_ACCESSED_POSTS/LastName[1]/text()', x)::text,'{}"'),'')::varchar(32) as LastName
--				,nullif(trim(xpath('/DLO_ACCESSED_POSTS/FirstName[1]/text()', x)::text,'{}"'),'')::varchar(32) as FirstName
--				,nullif(trim(xpath('/DLO_ACCESSED_POSTS/FatherName[1]/text()', x)::text,'{}"'),'')::varchar(32) as FatherName
--				,nullif(trim(xpath('/DLO_ACCESSED_POSTS/Category[1]/text()', x)::text,'{}"'),'')::int as Category
--				,to_timestamp(nullif(trim(xpath('/DLO_ACCESSED_POSTS/BeginAccessDate[1]/text()', x)::text,'{}"'),''),'YYYY-MM-DD HH:MI:SS.MS') as BeginAccessDate
--				,to_timestamp(nullif(trim(xpath('/DLO_ACCESSED_POSTS/EndAccessDate[1]/text()', x)::text,'{}"'),''),'YYYY-MM-DD HH:MI:SS.MS') as EndAccessDate
--				,to_timestamp(nullif(trim(xpath('/DLO_ACCESSED_POSTS/ExpulsionDate[1]/text()', x)::text,'{}"'),''),'YYYY-MM-DD HH:MI:SS.MS') as ExpulsionDate
--				,to_timestamp(nullif(trim(xpath('/DLO_ACCESSED_POSTS/BeginWorkDate[1]/text()', x)::text,'{}"'),''),'YYYY-MM-DD HH:MI:SS.MS') as BeginWorkDate
--				,to_timestamp(nullif(trim(xpath('/DLO_ACCESSED_POSTS/CertificateDate[1]/text()', x)::text,'{}"'),''),'YYYY-MM-DD HH:MI:SS.MS') as CertificateDate
--				,nullif(trim(xpath('/DLO_ACCESSED_POSTS/MedicalSpecialityCode[1]/text()', x)::text,'{}"'),'')::varchar(16) as MedicalSpecialityCode
--				,nullif(trim(xpath('/DLO_ACCESSED_POSTS/Speciality[1]/text()', x)::text,'{}"'),'')::varchar(3) as Speciality
--				,nullif(trim(xpath('/DLO_ACCESSED_POSTS/PostType[1]/text()', x)::text,'{}"'),'')::varchar(4) as PostType
--				,nullif(trim(xpath('/DLO_ACCESSED_POSTS/Snils[1]/text()', x)::text,'{}"'),'')::char(14) as Snils
--				,nullif(trim(xpath('/DLO_ACCESSED_POSTS/LpuMain[1]/text()', x)::text,'{}"'),'')::varchar(6) as LpuMain
--			into temp table Tmp_DLO_ACCESSED_POSTS
--			from
--			(
--				select	unnest(xpath('/ACCESSES/body/DLO_ACCESSED_POSTS', x)) as x
--				from 
--				(
--					select "Xml" as x
--				) root
--			) DLO_ACCESSED_POSTS;

--        INSERT INTO APS.T_DLO_ACCESSED_POSTS
--           (
--				DloAccessedPostsID
--				,LpuMain
--				,PostCode
--				,LastName
--				,FirstName
--				,FatherName
--				,Speciality
--				,PostType
--				,Category
--				,BeginAccessDate
--				,EndAccessDate
--				,BeginWorkDate
--				,CertificateDate
--				,MedicalSpecialityCode
--				,ExpulsionDate
--				,Snils
--		   )
--         SELECT
--				dap.ID
--				,dap.LpuMain
--				,dap.PostCode
--				,dap.LastName
--				,dap.FirstName
--				,dap.FatherName
--				,dap.Speciality
--				,dap.PostType
--				,dap.Category
--				,dap.BeginAccessDate
--				,dap.EndAccessDate
--				,dap.BeginWorkDate
--				,dap.CertificateDate
--				,dap.MedicalSpecialityCode
--				,dap.ExpulsionDate
--				,dap.Snils
--        FROM Tmp_DLO_ACCESSED_POSTS dap;

--		DROP TABLE if exists Tmp_DLO_ACCESSED_POSTS;                        
--	end if;  
--End;
--$$;