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

DROP PROCEDURE IF EXISTS APS.sp_RDS_ProductForm;

CREATE OR REPLACE PROCEDURE APS.sp_RDS_ProductForm
(
	"Xml" text DEFAULT null
,	"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
,	"PATH"  character varying DEFAULT null::character varying
)
    language plpgsql
as
$$
Begin

	Declare	_Result text;
		_ContentEncoding varchar(255) := 'utf-8';
		_Type varchar(255) := 'PRODUCT_FORM';
		_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_PRODUCT_FORM;
			CREATE TEMP TABLE Temp_PRODUCT_FORM(
			ProductFormID		uuid
			,OrdinalNumber		INT
			,PackageType		uuid
			,Amount				DECIMAL(12,2)
			,BillOfGoods		uuid
			,FormType			INT
			,UnitPrice			boolean
			,WriteOff			boolean
			,Descr				VARCHAR(50)
			,NotUseInPackage	boolean
			,Appointment	boolean);
				
			_sql := 'insert into Temp_PRODUCT_FORM(ProductFormID, OrdinalNumber, PackageType, Amount, BillOfGoods, FormType, UnitPrice, WriteOff, Descr, NotUseInPackage,Appointment); ' 
				|| ' SELECT pf.ProductFormID, pf.OrdinalNumber, pf.PackageType, pf.Amount, pf.BillOfGoods, pf.FormType, pf.UnitPrice, pf.WriteOff, pf.Descr, pf.NotUseInPackage,pf.Appointment '
				|| 'FROM dblink(''dbname=' || "PATH" 
				|| ''','
				|| ''' SELECT p.ProductFormID, p.OrdinalNumber, p.PackageType, p.Amount, p.BillOfGoods, p.FormType, p.UnitPrice, p.WriteOff, p.Descr, p.NotUseInPackage p.Appointment
					FROM PHARM.T_PRODUCT_FORM p '') ' 
					|| ' AS pf(ProductFormID		uuid
								,OrdinalNumber		INT
								,PackageType		uuid
								,Amount				DECIMAL(12,2)
								,BillOfGoods		uuid
								,FormType			INT
								,UnitPrice			boolean
								,WriteOff			boolean
								,Descr				VARCHAR(50)
								,NotUseInPackage	boolean
								,Appointment	boolean) ';

			EXECUTE _SQL;
				
			DELETE FROM PHARM.T_PRODUCT_FORM pf
			WHERE NOT EXISTS(SELECT* FROM Temp_PRODUCT_FORM nmf WHERE nmf.ProductFormID = pf.ProductFormID);
		
			-- Обновляем имеющиеся 
			update PHARM.T_PRODUCT_FORM pf
			set OrdinalNumber = nmf.OrdinalNumber
			,	Amount = nmf.Amount
			,	UnitPrice = nmf.UnitPrice
			,	WriteOff = nmf.WriteOff
			,	PackageType = nmf.PackageType
			,	BillOfGoods = nmf.BillOfGoods
			,	Descr = nmf.Descr
			,	NotUseInPackage = coalesce(nmf.NotUseInPackage, '0')
			,	Appointment = coalesce(nmf.Appointment, '0')
			from Temp_PRODUCT_FORM nmf 
			where nmf.ProductFormID = pf.ProductFormID;
		
			-- Загружаем новые
			INSERT INTO PHARM.T_PRODUCT_FORM(ProductFormID, OrdinalNumber, Amount, UnitPrice, WriteOff, PackageType, BillOfGoods, Descr, NotUseInPackage,Appointment)
			SELECT nmf.ProductFormID, nmf.OrdinalNumber, nmf.Amount, nmf.UnitPrice, nmf.WriteOff, nmf.PackageType, nmf.BillOfGoods, nmf.Descr, coalesce(nmf.NotUseInPackage, '0'), coalesce(nmf.Appointment, '0')
			FROM Temp_PRODUCT_FORM nmf
			WHERE NOT EXISTS(select* from PHARM.T_PRODUCT_FORM pf where pf.ProductFormID = nmf.ProductFormID);
		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_PRODUCT_FORM pf where pf.ProductFormID = "ID")
				then
					_OperationType := _UPDATE;
				elsif "IsNew" = '0' and not exists(select* from PHARM.T_PRODUCT_FORM pf where pf.ProductFormID = "ID")
				then
					_OperationType := _DELETE;
					_Result := xmlelement
								(
									name "PRODUCT_FORM"
								,	xmlelement
									(
										name header
									,	xmlforest
										(
											_OperationType	as "TYPE"
										,	"ID"			as "ID"
										)
									)
								);
				end if;

				-- Обработка операции
				if _OperationType = _FTLOAD
				then
					-- Первичная загрузка
					_Result :=
					xmlelement
					(
						name "PRODUCT_FORM"
					,	xmlelement
						(
							name header
						,	xmlforest
							(
								_FTLOAD as "TYPE"
							)
						)
					,	xmlelement
						(
							name body
						,	xmlelement
							(
								name "PRODUCT_FORMS"
							,	(
									SELECT 
									xmlagg
									(
										xmlelement
										(
											name "PRODUCT_FORM"
										,	xmlforest
											(
												pf.ProductFormID	as "ID"
											,	pf.OrdinalNumber 	as "OrdinalNumber"
											,	pf.Amount			as "Amount"
											,	pf.UnitPrice		as "UnitPrice"
											,	pf.WriteOff			as "WriteOff"
											,	pf.PackageType   	as "PackageType"
											,	pf.BillOfGoods   	as "BillOfGoods"
											,	pf.Descr			as "Descr"
											,	pf.NotUseInPackage	as "NotUseInPackage"
											,	pf.Appointment		as "Appointment"
											)
										)
									)
									from PHARM.T_PRODUCT_FORM pf
								)
							)			 
						)
					);
				elsif _OperationType <> _DELETE
				then
					-- собираем PRODUCT_FORM
					select 
					xmlelement
					(
						name "PRODUCT_FORM"
					,	xmlelement
						(
							name header
						,	xmlforest
							(
								_OperationType	as "TYPE"
							,	"ID" 			as "ID"
							)
						)
					,	xmlelement
						(
							name body
						,	xmlforest
							(
								pf.OrdinalNumber	as "OrdinalNumber"
							,	pf.Amount			as "Amount"
							,	pf.UnitPrice		as "UnitPrice"
							,	pf.WriteOff			as "WriteOff"
							,	pf.PackageType   	as "PackageType"
							,	pf.BillOfGoods   	as "BillOfGoods"
							,	pf.Descr			as "Descr"
							,	pf.NotUseInPackage	as "NotUseInPackage"
							,	pf.Appointment		as "Appointment"
							)
						)
					)
					from PHARM.T_PRODUCT_FORM pf            
					into _Result
					where pf.ProductFormID = "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;
			elsif "Xml" is not null
			then
				-- Получаем тип операции
				_OperationType := (xpath('/PRODUCT_FORM/header[1]/TYPE[1]/text()', "Xml"::xml))[1]::varchar(6);

				-- Generate table #PRODUCT_FORM
				Drop Table If Exists TmpPRODUCT_FORMS;
				Create Temp Table TmpPRODUCT_FORMS
				As
				select
					(xpath('//ID[1]/text()', x))[1]::text::uuid					as ID
				,	(xpath('//OrdinalNumber[1]/text()', x))[1]::text::int		as OrdinalNumber
				,	(xpath('//Amount[1]/text()', x))[1]::text::decimal(12,2)	as Amount
				,	(xpath('//UnitPrice[1]/text()', x))[1]::text::boolean		as UnitPrice
				,	(xpath('//WriteOff[1]/text()', x))[1]::text::boolean		as WriteOff
				,	(xpath('//PackageType[1]/text()', x))[1]::text::uuid    	as PackageType
				,	(xpath('//BillOfGoods[1]/text()', x))[1]::text::uuid    	as BillOfGoods
				,	(xpath('//Descr[1]/text()', x))[1]::varchar(500)			as Descr
				,	(xpath('//NotUseInPackage[1]/text()', x))[1]::text::boolean	as NotUseInPackage
				,	(xpath('//Appointment[1]/text()', x))[1]::text::boolean		as Appointment
				from
				(
					select unnest(xpath('/PRODUCT_FORM/body/PRODUCT_FORMS/PRODUCT_FORM', "Xml"::xml)) as x
				) t1
				where _OperationType = _FTLOAD;

				-- Generate table #PRODUCT_FORM
				Drop Table If Exists TmpPRODUCT_FORM;
				Create Temp Table TmpPRODUCT_FORM
				As
				select
					(xpath('//ID[1]/text()', x1))[1]::text::uuid					as ID
				,	(xpath('//OrdinalNumber[1]/text()', x2))[1]::text::int		as OrdinalNumber
				,	(xpath('//Amount[1]/text()', x2))[1]::text::decimal(12,2)	as Amount
				,	(xpath('//UnitPrice[1]/text()', x2))[1]::text::boolean		as UnitPrice
				,	(xpath('//WriteOff[1]/text()', x2))[1]::text::boolean		as WriteOff
				,	(xpath('//PackageType[1]/text()', x2))[1]::text::uuid    	as PackageType
				,	(xpath('//BillOfGoods[1]/text()', x2))[1]::text::uuid    	as BillOfGoods
				,	(xpath('//Descr[1]/text()', x2))[1]::varchar(500)			as Descr
				,	(xpath('//NotUseInPackage[1]/text()', x))[1]::text::boolean	as NotUseInPackage
				,	(xpath('//Appointment[1]/text()', x))[1]::text::boolean		as Appointment
				from
				(
					select 
						(xpath('/PRODUCT_FORM/header', "Xml"::xml))[1] as x1
					,	(xpath('/PRODUCT_FORM/body', "Xml"::xml))[1] as x2
				) t1
				where _OperationType <> _FTLOAD;

				-- Обработка операции
				if _OperationType = _FTLOAD
				then
					delete from PHARM.T_PRODUCT_FORM f
					where not exists(select* from TmpPRODUCT_FORMS nmf where nmf.ID = pf.ProductFormID);
					
					-- Обновляем имеющиеся 
		        	update PHARM.T_PRODUCT_FORM pf
		        	set OrdinalNumber = nmf.OrdinalNumber
					,	Amount = nmf.Amount
					,	UnitPrice = nmf.UnitPrice
					,	WriteOff = nmf.WriteOff
					,	PackageType = nmf.PackageType
					,	BillOfGoods = nmf.BillOfGoods
					,	Descr = nmf.Descr
					,	NotUseInPackage = coalesce(nmf.NotUseInPackage, '0')
					,	Appointment = coalesce(nmf.Appointment, '0')
		        	from TmpPRODUCT_FORMS nmf 
					where nmf.ID = pf.ProductFormID;
					
					-- Загружаем новые
					INSERT INTO PHARM.T_PRODUCT_FORM(ProductFormID, OrdinalNumber, Amount, UnitPrice, WriteOff, PackageType, BillOfGoods, Descr, NotUseInPackage,Appointment)
		        	SELECT nmf.ID, nmf.OrdinalNumber, nmf.Amount, nmf.UnitPrice, nmf.WriteOff, nmf.PackageType, nmf.BillOfGoods, nmf.Descr, coalesce(nmf.NotUseInPackage, '0'), coalesce(nmf.Appointment, '0')
		        	from TmpPRODUCT_FORMS nmf
		        	where not exists(select* from PHARM.T_PRODUCT_FORM pf where pf.ProductFormID = nmf.ID);
				elsif _OperationType = _DELETE
				then
					-- Удаление
					delete from PHARM.T_PRODUCT_FORM pf
					where exists(select* from TmpPRODUCT_FORM nmf where nmf.ID = pf.ProductFormID);
				else
					update PHARM.T_PRODUCT_FORM pf
		        	set	OrdinalNumber = nmf.OrdinalNumber
					,	Amount = nmf.Amount
					,	UnitPrice = nmf.UnitPrice
					,	WriteOff = nmf.WriteOff
					,	PackageType = nmf.PackageType
					,	BillOfGoods = nmf.BillOfGoods
					,	Descr = nmf.Descr
					,	NotUseInPackage = coalesce(nmf.NotUseInPackage, '0')
					,	Appointment = coalesce(nmf.Appointment, '0')
		        	from TmpPRODUCT_FORM nmf 
					where nmf.ID = pf.ProductFormID and _OperationType = _UPDATE;

		        	INSERT INTO PHARM.T_PRODUCT_FORM(ProductFormID, OrdinalNumber, Amount, UnitPrice, WriteOff, PackageType, BillOfGoods, Descr, NotUseInPackage,Appointment)
		        	SELECT nmf.ID, nmf.OrdinalNumber, nmf.Amount, nmf.UnitPrice, nmf.WriteOff, nmf.PackageType, nmf.BillOfGoods, nmf.Descr, coalesce(nmf.NotUseInPackage, '0'), coalesce(nmf.Appointment, '0')
		        	from TmpPRODUCT_FORM nmf
		        	where not exists(select* from PHARM.T_PRODUCT_FORM pf where pf.ProductFormID = nmf.ID)
		            and _OperationType = _INSERT;
				end if;

				-- Drop tables
				drop table if exists TmpPRODUCT_FORM;
				drop table if exists TmpPRODUCT_FORMS;
				end if;
			END if;
	End;
End;
$$;