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

CREATE PROCEDURE sp_RDS_BGDoseViews
(
	"Xml" text DEFAULT NULL
,	"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) := 'BILL_OF_GOODS_LINK_DOSE_VIEW';
		_OperationType varchar(6);
		_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_T_BILL_OF_GOODS_LINK_DOSE_VIEW;
			CREATE TEMP TABLE TEMP_T_BILL_OF_GOODS_LINK_DOSE_VIEW
			(
				BillOfGoodsLinkDoseViewID	uuid
			,	DoseView					INT
			,	BillOfGoods					uuid
			,	Val							DECIMAL
			,	OrderNum					INT
			,	AdditionalSymbol			VARCHAR(10)
			,	ValText						VARCHAR(1000)
			);
	
			_sql := 'insert into TEMP_T_BILL_OF_GOODS_LINK_DOSE_VIEW(BillOfGoodsLinkDoseViewID, DoseView, BillOfGoods, Val, OrderNum, AdditionalSymbol, ValText) ' 
				|| ' SELECT * FROM dblink(''dbname=' || "PATH" 
				|| ''','
				|| 'SELECT BillOfGoodsLinkDoseViewID, DoseView, BillOfGoods, Val, OrderNum, AdditionalSymbol, ValText FROM PHARM.T_BILL_OF_GOODS_LINK_DOSE_VIEW'') '
				|| ' AS tt(BillOfGoodsLinkDoseViewID uuid, DoseView INT, BillOfGoods uuid, Val DECIMAL, OrderNum INT, AdditionalSymbol VARCHAR, ValText VARCHAR)';
	
			EXECUTE _SQL;
	
			DELETE 
				FROM PHARM.T_BILL_OF_GOODS_LINK_DOSE_VIEW s
			WHERE NOT EXISTS
				(
					SELECT *
						FROM TEMP_T_BILL_OF_GOODS_LINK_DOSE_VIEW nmf
					WHERE nmf.BillOfGoodsLinkDoseViewID = s.BillOfGoodsLinkDoseViewID
				);
	
			-- Обновляем имеющиеся 
			UPDATE PHARM.T_BILL_OF_GOODS_LINK_DOSE_VIEW s
			SET DoseView = nmf.DoseView
			,	BillOfGoods = nmf.BillOfGoods
			,	Val = nmf.Val
			,	OrderNum = nmf.OrderNum
			,	AdditionalSymbol = nmf.AdditionalSymbol
			,	ValText = nmf.ValText
			FROM TEMP_T_BILL_OF_GOODS_LINK_DOSE_VIEW nmf 
			where nmf.BillOfGoodsLinkDoseViewID = s.BillOfGoodsLinkDoseViewID;
		
			-- Загружаем новые
			INSERT INTO PHARM.T_BILL_OF_GOODS_LINK_DOSE_VIEW
			(
				BillOfGoodsLinkDoseViewID,
				DoseView,
				BillOfGoods, 
				Val, 
				OrderNum, 
				AdditionalSymbol, 
				ValText
			)
			SELECT 
				nmf.BillOfGoodsLinkDoseViewID,
				nmf.DoseView, 
				nmf.BillOfGoods, 
				nmf.Val, 
				nmf.OrderNum, 
				nmf.AdditionalSymbol, 
				nmf.ValText
			FROM TEMP_T_BILL_OF_GOODS_LINK_DOSE_VIEW nmf
			WHERE NOT EXISTS
				(
					SELECT *
						FROM PHARM.T_BILL_OF_GOODS_LINK_DOSE_VIEW s 
					WHERE s.BillOfGoodsLinkDoseViewID = nmf.BillOfGoodsLinkDoseViewID
				);
		ELSE
			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 Pharm.T_BILL_OF_GOODS_LINK_DOSE_VIEW bg where bg.BillOfGoodsLinkDoseViewID = "ID")
				then
					_OperationType := _UPDATE;
				elsif "IsNew" = '0' and not exists (select * from Pharm.T_BILL_OF_GOODS_LINK_DOSE_VIEW bg where bg.BillOfGoodsLinkDoseViewID = "ID")
				then
					_OperationType := _DELETE;
					_Result := XMLELEMENT(name "BILL_OF_GOODS_LINK_DOSE_VIEW", XMLELEMENT(name header, XMLFOREST(_OperationType as "TYPE", "ID" as "ID")));
				end if;
	
				-- Обработка операции
				IF _OperationType = _FTLOAD
				then
					-- Первичная загрузка
					select 			    
					XMLELEMENT
					(
						name "BILL_OF_GOODS_LINK_DOSE_VIEW"
					,	XMLELEMENT
						(
							name header
						,	XMLFOREST(_FTLOAD as "TYPE")
						)
					,	XMLELEMENT
						(
							name body
						,	XMLELEMENT
							(
								name "BILL_OF_GOODS_LINK_DOSE_VIEWS"
							,	XMLELEMENT
								(
									name "BILL_OF_GOODS_LINK_DOSE_VIEW"
								,	XMLFOREST
									(
										dgdv.BillOfGoodsLinkDoseViewID		as "ID"
									,	dgdv.DoseView						as "DoseView"
									,	dgdv.BillOfGoods					as "BillOfGoods"
									,	dgdv.Val							as "Val"
									,	dgdv.ValText						as "ValText"
									,	dgdv.OrderNum						as "OrderNum"
									,	dgdv.AdditionalSymbol				as "AdditionalSymbol"
									)
								)
							)
						)
					)
					from pharm.T_BILL_OF_GOODS_LINK_DOSE_VIEW dgdv
					into _Result;		
				elsif _OperationType <> _DELETE
				then
					-- собираем BILL_OF_GOODS_LINK_DOSE_VIEW
					select 	
					XMLELEMENT
					(
						name "BILL_OF_GOODS_LINK_DOSE_VIEW"
						,	XMLELEMENT
							(
								name header
							,	XMLForest
								(
									_OperationType as "TYPE"
								,	"ID" as "ID"
								)
							)
						,	XMLELEMENT
							(
								name body
							,	XMLFOREST
								(
									dgdv.DoseView				as "DoseView"
								,	dgdv.BillOfGoods			as "BillOfGoods"
								,	dgdv.Val					as "Val"
								,	dgdv.ValText				as "ValText"
								,	dgdv.OrderNum				as "OrderNum"
								,	dgdv.AdditionalSymbol		as "AdditionalSymbol"
								)
							)					
						)
					from Pharm.T_BILL_OF_GOODS_LINK_DOSE_VIEW dgdv
					into _Result
					where dgdv.BillOfGoodsLinkDoseViewID = "ID";		    		  
				END if;
	
				-- Отправка
				INSERT INTO Akuz.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('/BILL_OF_GOODS/header[1]/TYPE[1]/text()', "Xml"::xml))[1]::varchar(6);
		
					-- Generate table #BILL_OF_GOODS_LINK_DOSE_VIEWS
					drop table if exists TmpBILL_OF_GOODS_LINK_DOSE_VIEWS;
					Create Temp Table TmpBILL_OF_GOODS_LINK_DOSE_VIEWS
					as
					select	(xpath('/BILL_OF_GOODS_LINK_DOSE_VIEW/ID[1]/text()', x))[1]::text::uuid as ID
					,	(xpath('/BILL_OF_GOODS_LINK_DOSE_VIEW/DoseView[1]/text()', x))[1]::text::int as DoseView
					,	(xpath('/BILL_OF_GOODS_LINK_DOSE_VIEW/BillOfGoods[1]/text()', x))[1]::text::uuid as BillOfGoods
					,	(xpath('/BILL_OF_GOODS_LINK_DOSE_VIEW/Val[1]/text()', x))[1]::text::decimal(31,10) as Val
					,	(xpath('/BILL_OF_GOODS_LINK_DOSE_VIEW/ValText[1]/text()', x))[1]::varchar(1000) as ValText
					,	(xpath('/BILL_OF_GOODS_LINK_DOSE_VIEW/OrderNum[1]/text()', x))[1]::text::int as OrderNum
					,	(xpath('/BILL_OF_GOODS_LINK_DOSE_VIEW/AdditionalSymbol[1]/text()', x))[1]::varchar(1) as AdditionalSymbol
					from unnest(xpath('/BILL_OF_GOODS_LINK_DOSE_VIEW/body/BILL_OF_GOODS_LINK_DOSE_VIEWS/BILL_OF_GOODS_LINK_DOSE_VIEW', "Xml"::xml)) x
					where _OperationType = _FTLOAD;
		
					-- Generate table #BILL_OF_GOODS_LINK_DOSE_VIEW
					drop table if exists TmpBILL_OF_GOODS_LINK_DOSE_VIEW;
					Create Temp Table TmpBILL_OF_GOODS_LINK_DOSE_VIEW
					as
					select	(xpath('/header[1]/ID[1]/text()', t.x1))[1]::text::uuid as ID
					,	(xpath('/body[1]/DoseView[1]/text()', t.x2))[1]::text::int as DoseView
					,	(xpath('/body[1]/BillOfGoods[1]/text()', t.x2))[1]::text::uuid as BillOfGoods
					,	(xpath('/body[1]/Val[1]/text()', t.x2))[1]::text::decimal(31,10) as Val
					,	(xpath('/body[1]/ValText[1]/text()', t.x2))[1]::varchar(1000) as ValText
					,	(xpath('/body[1]/OrderNum[1]/text()', t.x2))[1]::text::int as OrderNum
					,	(xpath('/body[1]/AdditionalSymbol[1]/text()', t.x2))[1]::varchar(1) as AdditionalSymbol
					from
					(
						select	unnest(xpath('/BILL_OF_GOODS_LINK_DOSE_VIEW/header', x)) as x1
						,	unnest(xpath('/BILL_OF_GOODS_LINK_DOSE_VIEW/body', x)) as x2
						from unnest(xpath('/BILL_OF_GOODS_LINK_DOSE_VIEW', "Xml"::xml)) x
						where _OperationType <> _FTLOAD
					) t;
					
					-- Обработка операции
					if _OperationType = _FTLOAD
					then
						delete from PHARM.T_BILL_OF_GOODS_LINK_DOSE_VIEW s
						where not exists
						(
							select* from TmpBILL_OF_GOODS_LINK_DOSE_VIEWS nmf where nmf.ID = s.BillOfGoodsLinkDoseViewID
						);
		
						-- Обновляем имеющиеся 
						update PHARM.T_BILL_OF_GOODS_LINK_DOSE_VIEW s
						set	DoseView = nmf.DoseView
						,	BillOfGoods = nmf.BillOfGoods
						,	Val = nmf.Val
						,	OrderNum = nmf.OrderNum
						,	AdditionalSymbol = nmf.AdditionalSymbol
						,	ValText = nmf.ValText
						from TmpBILL_OF_GOODS_LINK_DOSE_VIEWS nmf 
						where nmf.ID = s.BillOfGoodsLinkDoseViewID;
		
						-- Загружаем новые
						INSERT INTO PHARM.T_BILL_OF_GOODS_LINK_DOSE_VIEW(BillOfGoodsLinkDoseViewID, DoseView, BillOfGoods, Val, OrderNum, AdditionalSymbol, ValText)
						SELECT nmf.ID, nmf.DoseView, nmf.BillOfGoods, nmf.Val, nmf.OrderNum, nmf.AdditionalSymbol, nmf.ValText
						from TmpBILL_OF_GOODS_LINK_DOSE_VIEWS nmf
						where not exists(select* from PHARM.T_BILL_OF_GOODS_LINK_DOSE_VIEW s where s.BillOfGoodsLinkDoseViewID = nmf.ID);
					elsif _OperationType = _DELETE
					then			
						-- Удаление
						delete from PHARM.T_BILL_OF_GOODS_LINK_DOSE_VIEW s
						where exists(select* from TmpBILL_OF_GOODS_LINK_DOSE_VIEW nmf where nmf.ID = s.BillOfGoodsLinkDoseViewID);
					else
						update PHARM.T_BILL_OF_GOODS_LINK_DOSE_VIEW s
						set	DoseView = nmf.DoseView
						,	BillOfGoods = nmf.BillOfGoods
						,	Val = nmf.Val
						,	OrderNum = nmf.OrderNum
						,	AdditionalSymbol = nmf.AdditionalSymbol
						,	ValText = nmf.ValText
						from TmpBILL_OF_GOODS_LINK_DOSE_VIEW nmf
						where nmf.ID = s.BillOfGoodsLinkDoseViewID
						and _OperationType = _UPDATE;
		
						INSERT INTO PHARM.T_BILL_OF_GOODS_LINK_DOSE_VIEW(BillOfGoodsLinkDoseViewID, DoseView, BillOfGoods, Val, OrderNum, AdditionalSymbol, ValText)
						SELECT nmf.ID, nmf.DoseView, nmf.BillOfGoods, nmf.Val, nmf.OrderNum, nmf.AdditionalSymbol, nmf.ValText
						from TmpBILL_OF_GOODS_LINK_DOSE_VIEW nmf
						where not exists(select* from PHARM.T_BILL_OF_GOODS_LINK_DOSE_VIEW s where s.BillOfGoodsLinkDoseViewID = nmf.ID)
						and _OperationType = _INSERT;
					END if;
					
					DROP TABLE if exists TmpBILL_OF_GOODS_LINK_DOSE_VIEWS;
					DROP TABLE if exists TmpBILL_OF_GOODS_LINK_DOSE_VIEW;
				end if;
			END if;
		end if;
    End;
End;
$$;