-- =============================================
-- Author:      Vitacore (с)
-- Create date: 2015-12-23
-- Description: Выгрузка/загрузка справочника Производитель торгового наименования в(из) RDS
-- =============================================
DROP PROCEDURE IF EXISTS public.sp_RDS_MedicamentProducer;
DROP PROCEDURE IF EXISTS aps.sp_RDS_MedicamentProducer;

CREATE PROCEDURE aps.sp_RDS_MedicamentProducer
(
	"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) := 'MEDICAMENT_PRODUCER';
		_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;
		_EntId int;
	Begin
		"dateTime" := Coalesce("dateTime", Current_TimeStamp::TIMESTAMP);

		IF "PATH" IS NOT NULL
		then
			DROP TABLE IF EXISTS Temp_MEDICAMENT_PRODUCER;
			CREATE TEMP TABLE Temp_MEDICAMENT_PRODUCER(
				 Inn					varchar(255)
				,Name					varchar(4000)
				,Address				varchar(500)
				,Active					boolean
				,Descr					varchar(255)
				,Contacts				varchar(255)
				,Country				uuid
				,FedCode				varchar(50)
				,RegCode				varchar(50)
				,MedicamentProducerID	varchar
				,Code					int);
				
			_sql := 'insert into Temp_MEDICAMENT_PRODUCER(Inn, Name, Address, Active, Descr, Contacts, Country, FedCode, RegCode, MedicamentProducerID, Code); ' 
				|| ' SELECT med.Inn, med.Name, med.Address, med.Active, med.Descr, med.Contacts, med.Country, med.FedCode, med.RegCode, med.MedicamentProducerID, med.Code '
				|| 'FROM dblink(''dbname=' || "PATH" 
				|| ''','
				|| ''' SELECT mp.Inn, mp.Name, mp.Address, mp.Active, mp.Descr, mp.Contacts, mp.Country, mp.FedCode, mp.RegCode, mp.MedicamentProducerID, mp.Code
				FROM PHARM.T_MEDICAMENT_PRODUCER mp '') ' 
					|| ' AS med(Inn					varchar(255)
				,Name					varchar(4000)
				,Address				varchar(500)
				,Active					boolean
				,Descr					varchar(255)
				,Contacts				varchar(255)
				,Country				uuid
				,FedCode				varchar(50)
				,RegCode				varchar(50)
				,MedicamentProducerID	varchar
				,Code					int) ';

			EXECUTE _SQL;
				
			-- Производителей не из справочника RDS переводим в неактивные
			UPDATE PHARM.T_MEDICAMENT_PRODUCER mp
			SET Active = '0'
			WHERE NOT EXISTS(SELECT* FROM Temp_MEDICAMENT_PRODUCER nmp WHERE nmp.MedicamentProducerID = mp.MedicamentProducerID);
		
			-- Обновляем имеющихся производителей
			UPDATE PHARM.T_MEDICAMENT_PRODUCER mp
			SET Inn = nmp.Inn
			,	Name = nmp.Name
			,	Address = nmp.Address
			,	Country = (select c.CountryId from PHARM.T_COUNTRY c where c.Code = nmp.Country limit 1)
			,	Active = nmp.Active
			,	RegCode = nmp.RegCode
			,	FedCode = nmp.FedCode
			,	Code = nmp.Code
			FROM Temp_MEDICAMENT_PRODUCER nmp 
			where nmp.MedicamentProducerID = mp.MedicamentProducerID;
		
			-- Загружаем новые
			INSERT INTO PHARM.T_MEDICAMENT_PRODUCER(MedicamentProducerID, Name, Inn, Address, Country, Active, RegCode, FedCode, Code)
			SELECT nmp.MedicamentProducerID, nmp.Name, nmp.Inn, nmp.Address
			, (select c.CountryId from PHARM.T_COUNTRY c where c.Code = nmp.Country limit 1)
			, nmp.Active, nmp.RegCode, nmp.FedCode, nmp.Code
			FROM Temp_MEDICAMENT_PRODUCER nmp
			WHERE NOT EXISTS(SELECT* FROM PHARM.T_MEDICAMENT_PRODUCER mp WHERE mp.MedicamentProducerID = nmp.MedicamentProducerID);
		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_MEDICAMENT_PRODUCER mp where mp.MedicamentProducerID = "ID")
				then
					_OperationType := _UPDATE;
				elsif "IsNew" = 0 and not exists(select* from Pharm.T_MEDICAMENT_PRODUCER mp where mp.MedicamentProducerID = "ID")
				then
					_OperationType := _DELETE;
					_Result := XMLELEMENT(name "MEDICAMENT_PRODUCER", XMLELEMENT(name header, XMLFOREST(_OperationType as "TYPE", "ID" as "ID")));
				end if;

				-- Обработка операции
				if _OperationType = _FTLOAD
				then	-- Первичная загрузка
					_Result :=
					XMLELEMENT
					(
						name "MEDICAMENT_PRODUCER"
					,	XMLELEMENT
						(
							name header
						,	XMLFOREST
							(
								_FTLOAD as "TYPE"
							)
						)
					,	XMLELEMENT
						(
							name body
						,	xmlelement
							(
								name "MEDICAMENT_PRODUCERS"						
								,	(
										select	
										xmlagg
										(
											xmlelement
											(
												name "MEDICAMENT_PRODUCER"
											,	xmlforest
												(
													mp.MedicamentProducerID as "ID"
												,	mp.Inn                  as "Inn"
												,	mp.Name                 as "Name"
												,	mp.Address            	as "Address"
												,	mp.Active               as "Active"
												,	c.Code                  as "Country"
												,	mp.RegCode				as "RegCode"
												,	mp.FedCode				as "FedCode"
												,	mp.Code				    as "Code"
												)
											)
										)
										from PHARM.T_MEDICAMENT_PRODUCER mp
										left join
											PHARM.T_COUNTRY c 
											on c.CountryID = mp.Country
									)
							)
						)
					);
				elsif _OperationType <> _DELETE
				then
					-- собираем MEDICAMENT_PRODUCER
					select 
					XMLELEMENT
					(
						name "MEDICAMENT_PRODUCER"
					,	XMLELEMENT
						(
							name header
						,	XMLFOREST
							(
								_OperationType as "TYPE"
							,	"ID" as "ID"
							)
						)
					,	XMLELEMENT
						(
							name body
						,	XMLFOREST
							(
								mp.Inn      as "Inn"
							,	mp.Name     as "Name"
							,	mp.Address  as "Address"
							,	mp.Active   as "Active"
							,	c.Code	    as "Country"
							,	mp.RegCode	as "RegCode"
							,	mp.FedCode	as "FedCode"
							,	mp.Code		as "Code"
							)
						)
					)				
					from PHARM.T_MEDICAMENT_PRODUCER mp
					left join 
						PHARM.T_COUNTRY c 
						on c.CountryID = mp.Country                
					into _Result
					where mp.MedicamentProducerID = "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
					-- Получаем тип операции
					SELECT 
						(xpath('/header[1]/TYPE[1]/text()', x))[1]::varchar(36)
					,	(xpath('/header[1]/ID[1]/text()', x))[1]::text::uuid
					,	"Xml"
					into _OperationType, _OwnerID, _Result
					from
					(
						select unnest(xpath('/MEDICAMENT_PRODUCER/header', "Xml"::xml)) x
					) t;
					
					
					-- Generate table Tmp_MEDICAMENT_PRODUCERS
					Drop Table If Exists Tmp_MEDICAMENT_PRODUCERS;
					Create Temp Table Tmp_MEDICAMENT_PRODUCERS
					as
					SELECT
						(xpath('/MEDICAMENT_PRODUCER[1]/ID[1]/text()', x))[1]::text::uuid as ID
					,	(xpath('/MEDICAMENT_PRODUCER[1]/Inn[1]/text()', x))[1]::varchar(255) as Inn
					,	(xpath('/MEDICAMENT_PRODUCER[1]/Name[1]/text()', x))[1]::varchar(255) as Name
					,	(xpath('/MEDICAMENT_PRODUCER[1]/Address[1]/text()', x))[1]::varchar(500) as Address
					,	(xpath('/MEDICAMENT_PRODUCER[1]/Active[1]/text()', x))[1]::text::boolean as Active
					,	(xpath('/MEDICAMENT_PRODUCER[1]/Country[1]/text()', x))[1]::varchar(13) as Country
					,	(xpath('/MEDICAMENT_PRODUCER[1]/RegCode[1]/text()', x))[1]::varchar(50) as RegCode
					,	(xpath('/MEDICAMENT_PRODUCER[1]/FedCode[1]/text()', x))[1]::varchar(50) as FedCode
					,	(xpath('/MEDICAMENT_PRODUCER[1]/Code[1]/text()', x))[1]::text::int as Code
					from unnest(xpath('/MEDICAMENT_PRODUCER/body/MEDICAMENT_PRODUCERS/MEDICAMENT_PRODUCER', "Xml"::xml)) as x
					where _OperationType = _FTLOAD;			

					-- Generate table Tmp_MEDICAMENT_PRODUCER
					Drop Table If Exists Tmp_MEDICAMENT_PRODUCER;
					Create Temp Table Tmp_MEDICAMENT_PRODUCER
					as
					SELECT	(xpath('/header[1]/ID[1]/text()', t.x1))[1]::text::uuid as ID
					,	(xpath('/body[1]/Inn[1]/text()', t.x2))[1]::varchar(255) as Inn
					,	(xpath('/body[1]/Name[1]/text()', t.x2))[1]::varchar(255) as Name
					,	(xpath('/body[1]/Address[1]/text()', t.x2))[1]::varchar(500) as Address			
					,	(xpath('/body[1]/Active[1]/text()', t.x2))[1]::text::boolean as Active
					,	(xpath('/body[1]/Country[1]/text()', t.x2))[1]::varchar(13) as Country
					,	(xpath('/body[1]/RegCode[1]/text()', t.x2))[1]::varchar(50) as RegCode
					,	(xpath('/body[1]/FedCode[1]/text()', t.x2))[1]::varchar(50) as FedCode
					,	(xpath('/body[1]/Code[1]/text()', t.x2))[1]::text::int as Code
					from
					(
						select	unnest(xpath('/MEDICAMENT_PRODUCER/header', x)) as x1
						,	unnest(xpath('/MEDICAMENT_PRODUCER/body', x)) as x2
						from unnest(xpath('/MEDICAMENT_PRODUCER', "Xml"::xml)) x
					) t
					where _OperationType <> _FTLOAD;

					-- Обработка операции
					if _OperationType = _FTLOAD
					then
						-- Первичная загрузка
						-- Производителей не из справочника RDS переводим в неактивные
						update PHARM.T_MEDICAMENT_PRODUCER mp
						set Active = '0'
						where not exists(select* from Tmp_MEDICAMENT_PRODUCERS nmp where nmp.ID = mp.MedicamentProducerID);

						-- Обновляем имеющихся производителей
						update PHARM.T_MEDICAMENT_PRODUCER mp
						set Inn = nmp.Inn
						,	Name = nmp.Name
						,	Address = nmp.Address
						,	Country = (select c.CountryId from PHARM.T_COUNTRY c where c.Code = nmp.Country limit 1)
						,	Active = nmp.Active
						,	RegCode = nmp.RegCode
						,	FedCode = nmp.FedCode
						,	Code = nmp.Code
						from Tmp_MEDICAMENT_PRODUCERS nmp 
						where nmp.ID = mp.MedicamentProducerID;

						-- Загружаем новые
						INSERT INTO PHARM.T_MEDICAMENT_PRODUCER(MedicamentProducerID, Name, Inn, Address, Country, Active, RegCode, FedCode, Code)
						SELECT nmp.ID, nmp.Name, nmp.Inn, nmp.Address
						, (select c.CountryId from PHARM.T_COUNTRY c where c.Code = nmp.Country limit 1)
						, nmp.Active, nmp.RegCode, nmp.FedCode, nmp.Code
						from Tmp_MEDICAMENT_PRODUCERS nmp
						where not exists(select* from PHARM.T_MEDICAMENT_PRODUCER mp where mp.MedicamentProducerID = nmp.ID);
					elsif _OperationType = _DELETE
		    		then
		        		-- Удаление
						update PHARM.T_MEDICAMENT_PRODUCER mp
						set Active = '0'
						from Tmp_MEDICAMENT_PRODUCER nmp 
						where nmp.ID = mp.MedicamentProducerID;
					else
						update PHARM.T_MEDICAMENT_PRODUCER mp
						set Inn = nmp.Inn
						,	Name = nmp.Name
						,	Address = nmp.Address
						,	Country = (select c.CountryId from PHARM.T_COUNTRY c where c.Code = nmp.Country limit 1)
						,	Active = nmp.Active
						,	RegCode = nmp.RegCode
						,	FedCode = nmp.FedCode
						,	Code = nmp.Code
						from Tmp_MEDICAMENT_PRODUCER nmp
						where nmp.ID = mp.MedicamentProducerID
						and _OperationType = _UPDATE;

						INSERT INTO PHARM.T_MEDICAMENT_PRODUCER(MedicamentProducerID, Name, Inn, Address, Country, Active, RegCode, FedCode, Code)
						SELECT nmp.ID, nmp.Name, nmp.Inn, nmp.Address
						, (select c.CountryId from PHARM.T_COUNTRY c where c.Code = nmp.Country limit 1)
						, nmp.Active, nmp.RegCode, nmp.FedCode, nmp.Code
						from Tmp_MEDICAMENT_PRODUCER nmp
						where not exists(select* from PHARM.T_MEDICAMENT_PRODUCER mp where mp.MedicamentProducerID = nmp.ID)
						and _OperationType = _INSERT;
					END if;

					_EntId := (select EntityID from VCLib.T_ENTITIES where EntName = 'MEDICAMENT_PRODUCER' and Schema = 'PHARM' limit 1);
					if exists 
					(
						select* from VCLib.T_GEN_NUMBERS where EntityID = _EntId and NumberName = 'PHARM.MEDICAMENT_PRODUCER' Limit 1
					)
					then
						update VCLib.T_GEN_NUMBERS
						set NextNumber = coalesce((select MAX(Code) from PHARM.T_MEDICAMENT_PRODUCER), 1) --+ 1
						where EntityID = _EntId
						and NumberName = 'PHARM.MEDICAMENT_PRODUCER';
					else
						INSERT INTO VCLib.T_GEN_NUMBERS(GenNumbersID, EntityID, NumberName, NextNumber)
						VALUES(gen_random_uuid(), _EntId, 'PHARM.MEDICAMENT_PRODUCER', coalesce((select MAX(OtherID) from PHARM.T_MEDICAMENT_PRODUCER), 1));
					end if;							

					-- Drop tables
					Drop Table If Exists Tmp_MEDICAMENT_PRODUCER;
					Drop Table If Exists Tmp_MEDICAMENT_PRODUCERS;
				end if;
			END if;
		end if;
	End;
End;
$$;