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

DROP PROCEDURE IF EXISTS APS.sp_RDS_PackageType;

CREATE OR REPLACE PROCEDURE APS.sp_RDS_PackageType
(
	"Xml" text DEFAULT null
,	"IsNew" integer = 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) := 'PACKAGE_TYPE';
		_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 Temp_PACKAGE_TYPE;
			CREATE TEMP TABLE Temp_PACKAGE_TYPE(
				 PackageTypeID	uuid
				,Name			VARCHAR(500)
				,Code			integer
				,ShortName		VARCHAR(500)
				,FedCode		VARCHAR(50)
				,RegCode		VARCHAR(50));
				
			_sql := 'insert into Temp_PACKAGE_TYPE(PackageTypeID, Name, Code, ShortName, FedCode, RegCode); ' 
				|| ' SELECT pack.PackageTypeID, pack.Name, pack.Code, pack.ShortName, pack.FedCode, pack.RegCode '
				|| 'FROM dblink(''dbname=' || "PATH" 
				|| ''','
				|| ''' SELECT pt.PackageTypeID, pt.Name, pt.Code, pt.ShortName, pt.FedCode, pt.RegCode FROM PHARM.T_PACKAGE_TYPE pt '') ' 
					|| ' AS pack(PackageTypeID	uuid
								,Name			VARCHAR(500)
								,Code			integer
								,ShortName		VARCHAR(500)
								,FedCode		VARCHAR(50)
								,RegCode		VARCHAR(50)) ';

			EXECUTE _SQL;
				
			-- Обновляем имеющиеся
			update PHARM.T_PACKAGE_TYPE pt
			set Name = nmf.Name
			,	Code = nmf.Code
			,	ShortName = nmf.ShortName
			,	RegCode = nmf.RegCode
			,	FedCode = nmf.FedCode
			from Temp_PACKAGE_TYPE nmf 
			where nmf.PackageTypeID = pt.PackageTypeID;
		
			-- Загружаем новые
			INSERT INTO PHARM.T_PACKAGE_TYPE(PackageTypeID, Name, Code, ShortName, RegCode, FedCode)
			SELECT nmf.PackageTypeID, nmf.Name, nmf.Code, nmf.ShortName, nmf.RegCode, nmf.FedCode
			from Temp_PACKAGE_TYPE nmf
			where not exists(select* from PHARM.T_PACKAGE_TYPE pt where pt.PackageTypeID = nmf.PackageTypeID);
		else	
			IF "Xml" is null
			then		
				-- Инициализация
		    	_OwnerID := "ID";
				--выясняем тип операции
				IF "ID" is null
				then
					_OperationType := _FTLOAD;
				ELSIF "IsNew" = 1
				then
					_OperationType := _INSERT;
				ELSIF "IsNew" = 0 and exists(select * from PHARM.T_PACKAGE_TYPE pt where pt.PackageTypeID = "ID")
				then
					_OperationType := _UPDATE;
				ELSIF "IsNew" = 0 and not exists(select * from PHARM.T_PACKAGE_TYPE pt where pt.PackageTypeID = "ID")
				then
					_OperationType := _DELETE;
					_Result := XMLELEMENT(name "PACKAGE_TYPE", XMLFOREST(XMLFOREST(_OperationType as "TYPE", "ID" as "ID") as header));
				END if;

				-- Обработка операции
				IF _OperationType = _FTLOAD
				then
					-- Первичная загрузка
					_Result := XMLELEMENT
						(
							name "PACKAGE_TYPE"
						,	XMLFOREST
							(
								XMLFOREST
								(
									_FTLOAD as "TYPE"
								) as header
							)
						,	XMLELEMENT
							(
								name body
							,	xmlelement
								(
									name "PACKAGE_TYPES"
								,	(
										select
										XMLAGG
										(
											XMLELEMENT
											(
												name "PACKAGE_TYPE"
											,	XMLFOREST
												(
													pt.PackageTypeID	as "ID"
												,	pt.Code				as "Code"
												,	pt.Name				as "Name"
												,	pt.ShortName		as "ShortName"
												,	pt.RegCode			as "RegCode"
												,	pt.FedCode			as "FedCode"
												)
											)
										)
										from PHARM.T_PACKAGE_TYPE pt
									)
								)
							)
						);	
				ELSIF _OperationType <> _DELETE
				then
					-- собираем PACKAGE_TYPE
					select 
					XMLELEMENT
					(
						name "MEDICATION_FORMS"
					,	XMLFOREST
						(
							XMLFOREST
							(
								_OperationType as "TYPE"
							,	"ID" as "ID"
							) as header
						)
					,	XMLFOREST
						(
							XMLFOREST
							(
								pt.Code		  	as "Code"
							,	pt.Name       	as "Name"
							,	pt.ShortName	as "ShortName"
							,	pt.RegCode	  	as "RegCode"
							,	pt.FedCode	  	as "FedCode"
							) as body
						)
					)
					from pharm.T_PACKAGE_TYPE pt
					into _Result
					where pt.PackageTypeID = "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('/PACKAGE_TYPE/header[1]/TYPE[1]/text()', "Xml"::xml))[1]::varchar(6);

					-- Generate table Tmp_MEDICATION_FORMSS
					Drop Table If Exists Tmp_PACKAGE_TYPES;
					Create Temp Table Tmp_PACKAGE_TYPES
					as
					select	(xpath('/PACKAGE_TYPE/ID[1]/text()', x))[1]::text::uuid as ID
					,	(xpath('/PACKAGE_TYPE/Code[1]/text()', x))[1]::integer as Code
					,	(xpath('/PACKAGE_TYPE/Name[1]/text()', x))[1]::varchar(500) as Name
					,	(xpath('/PACKAGE_TYPE/ShortName[1]/text()', x))[1]::varchar(500) as ShortName
					,	(xpath('/PACKAGE_TYPE/RegCode[1]/text()', x))[1]::varchar(50) as RegCode
					,	(xpath('/PACKAGE_TYPE/FedCode[1]/text()', x))[1]::varchar(50) as FedCode				
					from unnest(xpath('/PACKAGE_TYPE/body/PACKAGE_TYPES/PACKAGE_TYPE', "Xml"::xml)) as x
					where _OperationType = _FTLOAD;

					-- Generate table #PACKAGE_TYPE
					Drop Table If Exists Tmp_PACKAGE_TYPE;
					Create Temp Table Tmp_PACKAGE_TYPE
					as
					SELECT	(xpath('/header[1]/ID[1]/text()', t.x1))[1]::text::uuid as ID
					,	(xpath('/body[1]/Code[1]/text()', x))[1]::integer as Code
					,	(xpath('/body[1]/Name[1]/text()', x))[1]::varchar(500) as Name
					,	(xpath('/body[1]/ShortName[1]/text()', x))[1]::varchar(500) as ShortName
					,	(xpath('/body[1]/RegCode[1]/text()', x))[1]::varchar(50) as RegCode
					,	(xpath('/body[1]/FedCode[1]/text()', x))[1]::varchar(50) as FedCode		
					from
					(
						select	unnest(xpath('/PACKAGE_TYPE/header', x)) as x1
						,	unnest(xpath('/PACKAGE_TYPE/body', x)) as x2
						from unnest(xpath('/PACKAGE_TYPE', "Xml"::xml)) x
					) t
					where _OperationType <> _FTLOAD;

					-- Обработка операции
		    		if _OperationType = _FTLOAD
		    		then
		    		    -- Первичная загрузка
			
		    		    -- Обновляем имеющиеся
		    		    update PHARM.T_PACKAGE_TYPE pt
		    		    set Name = nmf.Name
						,	Code = nmf.Code
						,	ShortName = nmf.ShortName
						,	RegCode = nmf.RegCode
						,	FedCode = nmf.FedCode
		    		    from Tmp_PACKAGE_TYPES nmf 
						where nmf.ID = pt.PackageTypeID;
			
		    		    -- Загружаем новые
		    		    INSERT INTO PHARM.T_PACKAGE_TYPE(PackageTypeID, Name, Code, ShortName, RegCode, FedCode)
		    		    SELECT nmf.ID, nmf.Name, nmf.Code, nmf.ShortName, nmf.RegCode, nmf.FedCode
		    		    from Tmp_PACKAGE_TYPES nmf
		    		    where not exists(select* from PHARM.T_PACKAGE_TYPE pt where pt.PackageTypeID = nmf.ID);
		    		elsif _OperationType = _DELETE
		    		then
		    		    -- Удаление
		    		    delete from PHARM.T_PACKAGE_TYPE pt
		    		    where exists(select* from Tmp_PACKAGE_TYPE nmf where nmf.ID = pt.PackageTypeID);
		    		else
		    		    update PHARM.T_PACKAGE_TYPE pt
		    		    set	Name = nmf.Name
						,	Code = nmf.Code
						,	ShortName = nmf.ShortName
						,	RegCode = nmf.RegCode
						,	FedCode = nmf.FedCode
		    		    from Tmp_PACKAGE_TYPE nmf 
		    		    where nmf.ID = pt.PackageTypeID
						and _OperationType = _UPDATE;
			
		    		    INSERT INTO PHARM.T_PACKAGE_TYPE(PackageTypeID, Name, Code, ShortName, RegCode, FedCode)
		    		    SELECT nmf.ID, nmf.Name, nmf.Code, nmf.ShortName, nmf.RegCode, nmf.FedCode
		    		    from Tmp_PACKAGE_TYPE nmf
		    		    where not exists(select* from PHARM.T_PACKAGE_TYPE pt where pt.PackageTypeID = nmf.ID)
						and _OperationType = _INSERT;
		    		END if;
					
					DROP TABLE if exists Tmp_PACKAGE_TYPE;
					DROP TABLE if exists Tmp_PACKAGE_TYPES;
					end if;
				END if;
			end if;
	End;
End;
$$;