-- =============================================
-- Author:      Vitacore (с)
-- Create date: 2015-07-29
-- Description: Выгрузка/загрузка классификации Международных непатентованных наименований (МНН) в(из) RDS
-- =============================================
DROP PROCEDURE IF EXISTS sp_RDS_CmnnClassification;

CREATE PROCEDURE sp_RDS_CmnnClassification
(
	"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) := 'C_MNN_CLASSIFICATION';
		_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_C_MNN_FOR_DRUG_CLASSIFICATION_LINK;
			CREATE TEMP TABLE Temp_C_MNN_FOR_DRUG_CLASSIFICATION_LINK
				(
					 CMnnForDrugClassificationLinkID		uuid
					,CMnn									uuid
					,GroupsOfDrugsClassification			int
				);
			_sql := 'INSERT INTO Temp_C_MNN_FOR_DRUG_CLASSIFICATION_LINK(
							 CMnnForDrugClassificationLinkID
							,CMnn
							,GroupsOfDrugsClassification) 
							SELECT CMnnForDrugClassificationLinkID
							,CMnn
							,GroupsOfDrugsClassification
							FROM dblink(''dbname=' || "PATH" 
							|| ''', 
							SELECT CMnnForDrugClassificationLinkID
							,CMnn
							,GroupsOfDrugsClassification 
						FROM PHARM.T_C_MNN_FOR_DRUG_CLASSIFICATION_LINK '') AS tt(
							CMnnForDrugClassificationLinkID		uuid
							,CMnn									uuid
							,GroupsOfDrugsClassification			int)';
			EXECUTE _sql;
			
			-- Первичная загрузка
			
				DELETE FROM PHARM.T_C_MNN_FOR_DRUG_CLASSIFICATION_LINK mdc
				WHERE NOT EXISTS
				    (
				        select *
							from Temp_C_MNN_FOR_DRUG_CLASSIFICATION_LINK mc
				        where mdc.Cmnn = mc.Cmnn
				                and
				            mdc.GroupsOfDrugsClassification = mc.GroupsOfDrugsClassification
				    );
				
				-- Загружаем новые
				INSERT INTO PHARM.T_C_MNN_FOR_DRUG_CLASSIFICATION_LINK
						(
							 CMnnForDrugClassificationLinkID
							,CMnn 
							,GroupsOfDrugsClassification
						)
				SELECT 
						 mc.CMnnForDrugClassificationLinkID 
						,mc.Cmnn 
						,mc.GroupsOfDrugsClassification
					from Temp_C_MNN_FOR_DRUG_CLASSIFICATION_LINK mc
				where not exists
				    (
				        select 1
				        from PHARM.T_C_MNN_FOR_DRUG_CLASSIFICATION_LINK mdc
				        where mdc.Cmnn = mc.Cmnn
				                and
				            mdc.GroupsOfDrugsClassification = mc.GroupsOfDrugsClassification
				    );
			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_C_MNN_FOR_DRUG_CLASSIFICATION_LINK mc where mc.CMnnForDrugClassificationLinkID = "ID")
					then
						_OperationType= _UPDATE;
					ELSIF "IsNew" = '0' and not exists (select * from pharm.T_C_MNN_FOR_DRUG_CLASSIFICATION_LINK mc where mc.CMnnForDrugClassificationLinkID = "ID")
					then
						_OperationType= _DELETE;
						_Result := XMLELEMENT
							  (
								name "C_MNN_CLASSIFICATION"
							  ,	XMLFOREST
								(
									XMLFOREST
									(
										_OperationType as "TYPE"
									,	"ID" as "ID"
									) as header
								)
							  );
					END if;
					-- Обработка операции
					IF _OperationType = _FTLOAD
					then
						-- Первичная загрузка
						_Result := XMLELEMENT
							  (
								name "C_MNN_CLASSIFICATION"
							  ,	XMLFOREST
								(
									XMLFOREST
									(
										_FTLOAD as "TYPE"
									) as header
								)
							  ,	xmlelement
								(
									name "body"
								,	xmlelement
									(
										name "C_MNN_CLASSIFICATIONS"
									,	(
											select
											xmlagg
											( 
												xmlforest
												(
													xmlforest
													(	
														mc.CMnnForDrugClassificationLinkID as "ID"
													,	mc.CMnn                            as "Cmnn"
													,	mc.GroupsOfDrugsClassification     as "Classification"
													) as "C_MNN_CLASSIFICATION"
												)
											) 
											from pharm.T_C_MNN_FOR_DRUG_CLASSIFICATION_LINK mc
											inner join 
												pharm.T_C_MNN m 
												on m.CMnnID = mc.CMnn
											where m.UsedIn = 1
										)
									)						
								)
							  );
					ELSIF _operationType<> _DELETE
					then
						-- собираем C_MNN_CLASSIFICATION
						select 
						XMLELEMENT
						(
							name "C_MNN_CLASSIFICATION"
						,	XMLELEMENT
							(
								name "header"
							,	XMLFOREST
								(
									_OperationType as "TYPE"
								,	"ID" as "ID"
								)
							)
						,	XMLELEMENT
							(
								name "body"
							,	XMLFOREST
								(
									mc.CMnn as "Cmnn"
								,	mc.GroupsOfDrugsClassification as "Classification"
								)
							)
						)
						from pharm.T_C_MNN_FOR_DRUG_CLASSIFICATION_LINK mc
						into _Result
						where mc.CMnnForDrugClassificationLinkID = "ID";
					END if;
					-- Отправка
					INSERT INTO pharm.T_RABBIT_SEND_OBJECT(RabbitSendObjectID, QueueName, Content, MessageID, Headers, ContentEncoding, Type, UserID, State, CreateDateTime, OwnerID)
					select gen_random_uuid(), "QueueName", _Result, null, null, ContentEncodingVar, TypeVar, "User", 0, "DateTime", OwnerIDVar;
				ELSE
					IF "Xml" is not null
					then        
						-- Получаем тип операции
						_OperationType= nullif(trim(xpath('/C_MNN_CLASSIFICATION//header[1]/TYPE[1]/text()',"Xml"::xml)::text,'{}'),'')::varchar(6);
						-- Generate table Tmp_C_MNN_CLASSIFICATIONS
						drop table if exists Tmp_C_MNN_CLASSIFICATIONS;
						create temp table Tmp_C_MNN_CLASSIFICATIONS 
						as 
						select	nullif(trim(xpath('/C_MNN_CLASSIFICATION/ID[1]/text()',x)::text,'{}'),'')::UUID as ID
						,	nullif(trim(xpath('/C_MNN_CLASSIFICATION/Cmnn[1]/text()',x)::text,'{}'),'')::UUID as Cmnn
						,	nullif(trim(xpath('/C_MNN_CLASSIFICATION/Classification[1]/text()',x)::text,'{}'),'')::int as Classification
						from
						(
							select	unnest(xpath('/C_MNN_CLASSIFICATION/body/C_MNN_CLASSIFICATIONS/C_MNN_CLASSIFICATION', x)) as x
							from 
							(
								select "Xml"::xml as x
							) root
						) C_MNN_CLASSIFICATION
						where _operationType= _FTLOAD;
						-- Generate table Tmp_C_MNN_CLASSIFICATION
						drop table if exists Tmp_C_MNN_CLASSIFICATION;
						create temp table Tmp_C_MNN_CLASSIFICATION
						as 
						select	nullif(trim(xpath('/C_MNN_CLASSIFICATION/header[1]/ID[1]/text()',"Xml"::xml)::text,'{}'),'')::UUID as ID
						,	nullif(trim(xpath('/C_MNN_CLASSIFICATION/body[1]/Cmnn[1]/text()',"Xml"::xml)::text,'{}'),'')::UUID as Cmnn
						,	nullif(trim(xpath('/C_MNN_CLASSIFICATION/body[1]/Classification[1]/text()',"Xml"::xml)::text,'{}'),'')::int as Classification
						where _operationType<> _FTLOAD;

						-- Обработка операции
						if _operationType= _FTLOAD
						then
							-- Первичная загрузка
							-- Удаляем несуществующие связки
							delete from pharm.T_C_MNN_FOR_DRUG_CLASSIFICATION_LINK mdc
							where not exists
							(
								select *
								from Tmp_C_MNN_CLASSIFICATIONS mc
								where mdc.Cmnn = mc.Cmnn
								and mdc.GroupsOfDrugsClassification = mc.Classification
							);
							-- Загружаем новые
							INSERT INTO pharm.T_C_MNN_FOR_DRUG_CLASSIFICATION_LINK(CMnnForDrugClassificationLinkID, CMnn, GroupsOfDrugsClassification)
							SELECT mc.ID ,mc.Cmnn ,mc.Classification
							from Tmp_C_MNN_CLASSIFICATIONS mc
							where not exists
							(
								select *
								from pharm.T_C_MNN_FOR_DRUG_CLASSIFICATION_LINK mdc
								where mdc.Cmnn = mc.Cmnn
								and mdc.GroupsOfDrugsClassification = mc.Classification
							);
						elsif _operationType= _DELETE
						then
							-- Удаление
							delete from pharm.T_C_MNN_FOR_DRUG_CLASSIFICATION_LINK mc
							where mc.CMnnForDrugClassificationLinkID in 
							(
								select nmc.ID from Tmp_C_MNN_CLASSIFICATION
							);
						else
							-- update T_C_MNN_CLASSIFICATION
							update pharm.T_C_MNN_FOR_DRUG_CLASSIFICATION_LINK mc
							set	CMnn = nmc.Cmnn
							,	GroupsOfDrugsClassification = nmc.Classification
							from Tmp_C_MNN_CLASSIFICATION nmc 
							where nmc.ID = mc.CMnnForDrugClassificationLinkID
							and _operationType= _UPDATE;

							-- insert T_C_MNN_CLASSIFICATION
							INSERT INTO pharm.T_C_MNN_FOR_DRUG_CLASSIFICATION_LINK( CMnnForDrugClassificationLinkID, CMnn, GroupsOfDrugsClassification)
							SELECT nmc.ID, nmc.Cmnn, nmc.Classification
							from Tmp_C_MNN_CLASSIFICATION nmc
							where not exists
							(
								select *
								from pharm.T_C_MNN_FOR_DRUG_CLASSIFICATION_LINK mc
								where mc.Cmnn = nmc.Cmnn
								and mc.GroupsOfDrugsClassification = nmc.Classification
							) and _operationType= _INSERT;
						END if;	
						-- Drop tables
						DROP TABLE if exists Tmp_C_MNN_CLASSIFICATION;
						DROP TABLE if exists Tmp_C_MNN_CLASSIFICATIONS;
					END if;
			end if;
		end if;
	End;
End;
$$;