-- =============================================
-- Author:      Vitacore (с)
-- Create date: 2014-03-01
-- 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
		-- Получаем тип операции
		"LpuTo" := nullif(trim(xpath('/ACCESSES/header[1]/LpuMain[1]/text()', "Xml")::varchar(6),'{}"'),'');
		
		if "LpuTo" is null
		then        
			DELETE FROM akuz.T_DLO_ACCESSED_POSTS;                     
		else
			delete from akuz.T_DLO_ACCESSED_POSTS dap
                using AKUZ.T_LPU_MAIN lm
            where dap.LpuMain = lm.LpuID and lm.LpuCode = "LpuTo";
		end if;

		-- Generate table Tmp_DLO_ACCESSED_POSTS
		Drop Table If Exists Tmp_DLO_ACCESSED_POSTS;
		Create Temp Table Tmp_DLO_ACCESSED_POSTS(ID UUID, PostCode varchar(255), LastName varchar(32), FirstName varchar(32), FatherName varchar(32), Category int
			, BeginAccessDate TimeStamp, EndAccessDate TimeStamp, ExpulsionDate TimeStamp, BeginWorkDate TimeStamp, CertificateDate TimeStamp, MedicalSpecialityCode varchar(16)
			, PostType UUID, Speciality varchar(3), LpuMain UUID, Snils varchar(14));
		insert into Tmp_DLO_ACCESSED_POSTS(ID, PostCode, LastName, FirstName, FatherName, Category, BeginAccessDate, EndAccessDate, ExpulsionDate, BeginWorkDate, CertificateDate, MedicalSpecialityCode
			, PostType, Speciality, LpuMain, Snils)
		SELECT	
			DAP.Id
		,	DAP.PostCode
		,	DAP.LastName
		,	DAP.FirstName
		,	DAP.FatherName
		,	DAP.Category
		,	DAP.BeginAccessDate
		,	DAP.EndAccessDate
		,	DAP.ExpulsionDate
		,	DAP.BeginWorkDate
		,	DAP.CertificateDate
		,	DAP.MedicalSpecialityCode
		,	pt.PostTypeID as PostType
		,	DAP.Speciality
--		,	nullif(trim(xpath('/ACCESSES/body/DLO_ACCESSED_POSTS/Speciality[1]/text()', XmlVar)::text,'{}"'),'')::varchar(3) as Speciality
      ,	lm.LpuID as LpuMain
      ,	sn.Snils   
      from akuz.T_LPU_MAIN lm
      join
		(
			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
			from
			(
				select	unnest(xpath('/ACCESSES/body/DLO_ACCESSED_POSTS', x)) as x
				from 
				(
					select "Xml" as x
				) root
			) DLO_ACCESSED_POSTS
      ) DAP on DAP.LpuMain = lm.LpuCode
		left join  akuz.T_POST_TYPE pt on pt.Code = DAP.PostType --nullif(trim(xpath('/ACCESSES/body/DLO_ACCESSED_POSTS/PostType[1]/text()', XmlVar)::text,'{}"'),'')::varchar(4)
		left join  akuz.T_SNILS sn on sn.Snils = DAP.Snils --nullif(trim(xpath('/ACCESSES/body/DLO_ACCESSED_POSTS/Snils[1]/text()', XmlVar)::text,'{}"'),'')::char(14)
		where DAP.PostCode like lm.Code || '%';
		--lm.LpuCode = nullif(trim(xpath('/ACCESSES/body/DLO_ACCESSED_POSTS/LpuMain[1]/text()', XmlVar)::text,'{}"'),'')::varchar(6)
		--and nullif(trim(xpath('/ACCESSES/body/DLO_ACCESSED_POSTS/PostCode[1]/text()', XmlVar)::text,'{}"'),'')::varchar(255) like lm.Code || '%';
			
		-- INSERT akuz.T_DLO_ACCESSED_POSTS
		INSERT INTO akuz.T_DLO_ACCESSED_POSTS(DloAccessedPostsID, LpuMain, PostCode, LastName, FirstName, FatherName, Speciality, PostType, Category, BeginAccessDate
		   , EndAccessDate, UpdateInfoDate, BeginWorkDate, CertificateDate, MedicalSpecialityCode, ExpulsionDate, Snils)
		SELECT dap.ID, dap.LpuMain, dap.PostCode, dap.LastName, dap.FirstName, dap.FatherName
		   ,(select s.SpecialityID from akuz.T_SPECIALITY s where s.Code = dap.Speciality Limit 1)
		   , dap.PostType, dap.Category, dap.BeginAccessDate, dap.EndAccessDate, "DateTime", dap.BeginWorkDate, dap.CertificateDate, dap.MedicalSpecialityCode
		   , dap.ExpulsionDate, dap.Snils
		FROM Tmp_DLO_ACCESSED_POSTS dap;

		-- Drop table
		DROP TABLE if exists Tmp_DLO_ACCESSED_POSTS;                        
	END if;  
End;
$$;