-- =============================================
-- Author:      Vitacore (с)
-- Create date: 2015-12-30
-- Description: Автоматическая синхронизация регистра врачей DLO в(из) RDS
-- =============================================
DROP PROCEDURE IF EXISTS sp_BA_DloAccessPost.pg;

CREATE PROCEDURE sp_BA_DloAccessPost.pg
(
	"Xml" xml DEFAULT NULL::xml
,	"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) := 'DLO_ACCESSED_POSTS';
		_OperationType varchar(255);
		_OwnerID UUID := "ID";
		-- Operation types
		_INSERT varchar(6) := 'Insert';
		_UPDATE varchar(6) := 'Update';
		_DELETE varchar(6) := 'Delete';
		_FTLOAD varchar(6) := 'FTLoad';
	Begin
		"dateTime" := coalesce("dateTime", Current_TimeStamp::TIMESTAMP);
		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 akuz.T_DLO_ACCESSED_POSTS dap where dap.DloAccessedPostsID = "ID")
			then	_OperationType := _UPDATE;
			ELSIF "IsNew" = '0' and not exists (select * from akuz.T_DLO_ACCESSED_POSTS dap where dap.DloAccessedPostsID = "ID")
			then	_OperationType := _DELETE;
				_Result := XMLELEMENT 
					(
						NAME "DLO_ACCESSED_POSTS",
						XMLELEMENT
						(
							Name header,
							XMLELEMENT(NAME "TYPE", _OperationType),
							XMLELEMENT(NAME "ID", "ID")
						)
					);
			END if;

			-- Обработка операции
			IF _OperationType <> _DELETE
			then
				-- собираем DLO_ACCESSED_POSTS
				select 
					XMLELEMENT 
					(
						NAME "DLO_ACCESSED_POSTS",
						XMLELEMENT
						(
							Name header,
							XMLELEMENT(NAME "TYPE", _OperationType),
							XMLELEMENT(NAME "ID", "ID")
						),
						XMLELEMENT
						(
							Name body,
							xmlforest
							(
								dap.PostCode as "PostCode"
							,	dap.LastName as "LastName"
							,	dap.FirstName as "FirstName"
							,	dap.FatherName as "FatherName"
							,	dap.Category as "Category"
							,	to_char(dap.BeginAccessDate,'DD.MM.YYYY') as "BeginAccessDate"
							,	to_char(dap.EndAccessDate,'DD.MM.YYYY') as "EndAccessDate"
							,	to_char(dap.BeginWorkDate,'DD.MM.YYYY') as "BeginWorkDate"			
							,	to_char(dap.CertificateDate,'DD.MM.YYYY') as "CertificateDate"
							,	dap.MedicalSpecialityCode as "MedicalSpecialityCode"
							,	to_char(dap.ExpulsionDate,'DD.MM.YYYY') as "ExpulsionDate"
							,	dap.Snils as "Snils"
							,	dap.PostType as "PostType"
							,	dap.Speciality as "Speciality"
							,	dap.LpuMain as "LpuMain"
							)
						)
					)
				into _Result
				from akuz.T_DLO_ACCESSED_POSTS dap
				where dap.DloAccessedPostsID = "ID";
			END if;
			-- Отправка
			INSERT INTO akuz.T_RABBIT_SEND_OBJECT(RabbitSendObjectID, QueueName, Content, MessageID, Headers, ContentEncoding, Type, UserID, State, CreateDateTime, OwnerID)
			select uuid_generate_v4(), "QueueName", dbo.ConvertTextToBinaryZip(_Result, ''), null, null, _ContentEncoding, _Type, "User"::text, 0, "dateTime", _OwnerID;
	-------------------------------------------------------------------------------------------------------------
	-------------------------------------------------------------------------------------------------------------
		ELSE 
			IF "Xml" is not null
			then		
				-- Получаем тип операции			
				select into
					_OperationType, _OwnerId, _Result
					(xpath('/DLO_ACCESSED_POSTS/header[1]/TYPE[1]/text()',"Xml"))[1]::varchar(36),
					(xpath('/DLO_ACCESSED_POSTS/header[1]/ID[1]/text()',"Xml"))[1]::text::uuid,
					"Xml"::text;

				Drop Table If Exists TmpDLO_ACCESSED_POSTS;
				-- Generate table #DLO_ACCESSED_POSTS
				Create Temp Table TmpDLO_ACCESSED_POSTS
				As
				select	(xpath('/DLO_ACCESSED_POSTS/header[1]/ID[1]/text()', x))[1]::text::UUID			as ID
				,	(xpath('/DLO_ACCESSED_POSTS/body[1]/PostCode[1]/text()', x))[1]::varchar(255)			as PostCode
				,	(xpath('/DLO_ACCESSED_POSTS/body[1]/LastName[1]/text()', x))[1]::varchar(40)			as LastName
				,	(xpath('/DLO_ACCESSED_POSTS/body[1]/FirstName[1]/text()', x))[1]::varchar(40)			as FirstName
				,	(xpath('/DLO_ACCESSED_POSTS/body[1]/FatherName[1]/text()', x))[1]::varchar(40)			as FatherName
				,	(xpath('/DLO_ACCESSED_POSTS/body[1]/Category[1]/text()', x))[1]::text::int		as Category
				,	to_timestamp((xpath('/DLO_ACCESSED_POSTS/body[1]/BeginAccessDate[1]/text()', x))[1]::text, 'DD.MM.YYYY')as BeginAccessDate
				,	to_timestamp((xpath('/DLO_ACCESSED_POSTS/body[1]/EndAccessDate[1]/text()', x))[1]::text, 'DD.MM.YYYY')	as EndAccessDate
				,	to_timestamp((xpath('/DLO_ACCESSED_POSTS/body[1]/BeginWorkDate[1]/text()', x))[1]::text, 'DD.MM.YYYY')	as BeginWorkDate
				,	to_timestamp((xpath('/DLO_ACCESSED_POSTS/body[1]/CertificateDate[1]/text()', x))[1]::text, 'DD.MM.YYYY') as CertificateDate
				,	(xpath('/DLO_ACCESSED_POSTS/body[1]/MedicalSpecialityCode[1]/text()', x))[1]::varchar(16) 	as MedicalSpecialityCode
				,	to_timestamp((xpath('/DLO_ACCESSED_POSTS/body[1]/ExpulsionDate[1]/text()', x))[1]::text, 'DD.MM.YYYY')	as ExpulsionDate
				,	(xpath('/DLO_ACCESSED_POSTS/body[1]/Snils[1]/text()', x))[1]::varchar(14)			as Snils
				,	(xpath('/DLO_ACCESSED_POSTS/body[1]/PostType[1]/text()', x))[1]::varchar(4)			as PostType
				,	(xpath('/DLO_ACCESSED_POSTS/body[1]/Speciality[1]/text()', x))[1]::varchar(3)			as Speciality
				,	(xpath('/DLO_ACCESSED_POSTS/body[1]/LpuMain[1]/text()', x))[1]::varchar(6)			as LpuMain
				from
				(
					select "Xml" as x
				) t;
				
				-- Обработка операции
				IF _OperationType <> _DELETE
				then				
					-- update akuz.T_DLO_ACCESSED_POSTS
					update akuz.T_DLO_ACCESSED_POSTS dap
					set	LpuMain = lm.LpuID
					,	PostCode = ndap.PostCode
					,	LastName = ndap.LastName
					,	FirstName = ndap.FirstName
					,	FatherName = ndap.FatherName
					,	Category = ndap.Category
					,	BeginAccessDate = ndap.BeginAccessDate
					,	EndAccessDate = ndap.EndAccessDate
					,	BeginWorkDate = ndap.BeginWorkDate
					,	CertificateDate = ndap.CertificateDate
					,	MedicalSpecialityCode = ndap.MedicalSpecialityCode
					,	ExpulsionDate = ndap.ExpulsionDate
					,	Snils = ndap.Snils
					,	PostType = pt.PostTypeID
					,	Speciality = s.SpecialityID
					from TmpDLO_ACCESSED_POSTS ndap 					
					join akuz.T_LPU_MAIN lm on lm.LpuCode = ndap.LpuMain
					left join akuz.T_SPECIALITY s on s.Code = ndap.Speciality
					left join akuz.T_POST_TYPE pt on pt.Code = ndap.PostType
					where ndap.ID = dap.DloAccessedPostsID
					and _OperationType = _UPDATE;
					-- insert akuz.T_DLO_ACCESSED_POSTS
					INSERT INTO akuz.T_DLO_ACCESSED_POSTS(DloAccessedPostsID, LpuMain, PostCode, LastName, FirstName, FatherName, Category, BeginAccessDate, EndAccessDate
					,	BeginWorkDate, CertificateDate, MedicalSpecialityCode, ExpulsionDate, Snils, PostType, Speciality)
					SELECT
						ndap.ID--<DloAccessedPostsID, uniqueidentifier,>
					,	lm.LpuID--<LpuMain, varchar(6),>
					,	ndap.PostCode--<PostCode, varchar(255),>
					,	ndap.LastName--<LastName, varchar(40),>
					,	ndap.FirstName--<FirstName, varchar(40),>
					,	ndap.FatherName--<FatherName, varchar(40),>
					,	ndap.Category--<Category, int,>
					,	ndap.BeginAccessDate--<BeginAccessDate, datetime,>
					,	ndap.EndAccessDate--<EndAccessDate, datetime,>
					,	ndap.BeginWorkDate--<BeginWorkDate, datetime,>
					,	ndap.CertificateDate--<CertificateDate, datetime,>
					,	ndap.MedicalSpecialityCode--<MedicalSpecialityCode, varchar(16),>
					,	ndap.ExpulsionDate--<ExpulsionDate, datetime,>
					,	ndap.Snils--<Snils, char(14),>
					,	pt.PostTypeID--<PostType, varchar(4),>
					,	(select s.SpecialityID from akuz.T_SPECIALITY s where s.Code = ndap.Speciality Limit 1)--<Speciality, varchar(3),>
					from TmpDLO_ACCESSED_POSTS ndap
					join akuz.T_LPU_MAIN lm on lm.LpuCode = ndap.LpuMain and ndap.PostCode like lm.Code || '%'
					left join akuz.T_POST_TYPE pt on pt.Code = ndap.PostType
					where not exists
					(
						select * from akuz.T_DLO_ACCESSED_POSTS dap where dap.DloAccessedPostsID = ndap.ID
					)
					and _OperationType = _INSERT;
				END if;
				-- Drop tables
				DROP TABLE If Exists TmpDLO_ACCESSED_POSTS;
			END if;
		end if;
	End;
End;
$$;