-- =============================================
-- Author:      Vitacore (с)
-- Create date: 2016-02-08
-- Description: Выгрузка/загрузка справочника Лекартсвенных форм в(из) RDS
-- =============================================
CREATE OR REPLACE FUNCTION sp_rds_medicationforms
(
	"Xml" xml DEFAULT NULL::xml
,	"IsNew" boolean DEFAULT '1'::boolean
,	"ID" uuid DEFAULT NULL::uuid
,	"QueueName" varchar(100) DEFAULT 'RDSSync'::varchar(100)
,	"dateTime" timestamp DEFAULT NULL::timestamp
,	"User" uuid DEFAULT NULL::uuid
,	"PATH" text DEFAULT NULL::text
)
RETURNS void AS
$BODY$
Declare	_Result text;
	_ContentEncoding varchar(255);
	_Type varchar(255);
	_OperationType varchar(6);
	_OwnerID int;
	_sql text;
	_EntId int;
	-- Operation types
	_INSERT varchar(6);
	_UPDATE varchar(6);
	_DELETE varchar(6);
	_FTLOAD varchar(6);
Begin
	"dateTime" := coalesce("dateTime", Current_TimeStamp::TIMESTAMP);
	-- Operation types
	_INSERT := 'Insert';
	_UPDATE := 'Update';
	_DELETE := 'Delete';
	_FTLOAD := 'FTLoad';
	
	IF "PATH" IS NOT NULL
	then
		DROP TABLE IF EXISTS Temp_MEDICATION_FORMS;
		CREATE TEMP TABLE Temp_MEDICATION_FORMS(Name VARCHAR, Code INT, Active boolean, FormType INT, NameLat VARCHAR, FedCode VARCHAR, RegCode VARCHAR
												, MedicationFormsID uuid, EhrRusVzn431 INT, EhrRusVzn431Ver VARCHAR(8));
			
		_sql := 'insert into Temp_MEDICATION_FORMS(Name, Code, Active, FormType, NameLat, FedCode, RegCode, MedicationFormsID); ' 
			|| ' SELECT med.Name, med.Code, med.Active, med.FormType, med.NameLat, med.FedCode, med.RegCode, med.MedicationFormsID, EhrRusVzn431, EhrRusVzn431Ver '
			|| 'FROM dblink(''dbname=' || "PATH" 
			|| ''','
			|| ''' SELECT mp.Name, mp.Code, mp.Active, mp.FormType, mp.NameLat, mp.FedCode, mp.RegCode, mp.MedicationFormsID, ehr.IdEHR, ehr.CurrVersion
			FROM PHARM.T_MEDICATION_FORMS mp 
			left join lateral
				(
					SELECT er.IdEHR, rrb.CurrVersion 
					FROM EHR.T_EHR_RUS_VZN431 er
					LEFT JOIN EHR.T_EHR_RUS_REF_BOOKS RRB ON RRB.EhrRusRefBooksID = er.EhrRusRefBooks						
					WHERE er.EhrRusID = mp.EhrRusVzn431
					order by rrb.CurrVersion desc
					limit 1
				) ehr on true
			'') ' 
				|| ' AS med(Name VARCHAR, Code INT, Active boolean, FormType INT, NameLat VARCHAR, FedCode VARCHAR, RegCode VARCHAR, MedicationFormsID uuid, EhrRusVzn431 INT, EhrRusVzn431Ver VARCHAR(8)) ';

		EXECUTE _SQL;
			
		-- Лек. формы не из справочника RDS переводим в неактивные
    	UPDATE PHARM.T_MEDICATION_FORMS mf
    	SET Active = '0'
		WHERE NOT EXISTS(SELECT* FROM Temp_MEDICATION_FORMS nmf WHERE nmf.MedicationFormsID = mf.MedicationFormsID);

    	-- Обновляем имеющиеся лек. формы
    	update PHARM.T_MEDICATION_FORMS mf
    	set	Name = nmf.Name
		,	Active = nmf.Active
		,	FormType = coalesce(nmf.FormType,0)
		,	RegCode = nmf.RegCode
		,	FedCode = nmf.FedCode
		,	NameLat = nmf.NameLat
		,	Code = nmf.Code
		,	EhrRusVzn431 = ehr.EhrRusID
    	from Temp_MEDICATION_FORMS nmf 
		left join lateral
		(
			select er.EhrRusID 
			from EHR.T_EHR_RUS_VZN431 er
			LEFT JOIN 
				EHR.T_EHR_RUS_REF_BOOKS RRB 
				ON RRB.EhrRusRefBooksID = er.EhrRusRefBooks
			where er.IdEHR = nmf.EhrRusVzn431 and rrb.CurrVersion = nmf.EhrRusVzn431Ver
			limit 1
		) ehr on true
		where nmf.MedicationFormsID = mf.MedicationFormsID;

    	-- Загружаем новые
    	INSERT INTO PHARM.T_MEDICATION_FORMS(MedicationFormsID, Name, Active, FormType, RegCode, FedCode, NameLat, Code, EhrRusVzn431)
    	SELECT nmf.MedicationFormsID, nmf.Name, nmf.Active, coalesce(nmf.FormType,0), nmf.RegCode, nmf.FedCode, nmf.NameLat, nmf.Code, ehr.EhrRusID
    	FROM Temp_MEDICATION_FORMS nmf
		left join lateral
		(
			select er.EhrRusID 
			from EHR.T_EHR_RUS_VZN431 er
			LEFT JOIN 
				EHR.T_EHR_RUS_REF_BOOKS RRB 
				ON RRB.EhrRusRefBooksID = er.EhrRusRefBooks
			where er.IdEHR = nmf.EhrRusVzn431 and rrb.CurrVersion = nmf.EhrRusVzn431Ver
			limit 1
		) ehr on true
    	WHERE NOT EXISTS(SELECT* FROM PHARM.T_MEDICATION_FORMS mf WHERE mf.MedicationFormsID = nmf.MedicationFormsID);
	else	
		IF "Xml" is null
		then
			-- Инициализация
			_ContentEncoding := 'utf-8';
			_Type := 'MEDICATION_FORMS';
			_OwnerID := "ID";

			--выясняем тип операции
			IF "ID" is null
			then
				_OperationType := _FTLOAD;
			ELSIF "IsNew" = '1'
			then
				_OperationType := _INSERT;
			ELSIF "IsNew" = '0' and exists(select* from pharm.T_MEDICATION_FORMS mf where mf.MedicationFormsID = "ID")
			then
				_OperationType := _UPDATE;
			ELSIF "IsNew" = '0' and not exists(select* from pharm.T_MEDICATION_FORMS mf where mf.MedicationFormsID = "ID")
			then
				_OperationType := _DELETE;
				_Result := XMLELEMENT(name "MEDICATION_FORMS", XMLFOREST(XMLFOREST(_OperationType as "TYPE", "ID" as "ID") as header));
			END if;

			-- Обработка операции
			IF _OperationType = _FTLOAD
			then
				-- Первичная загрузка
				_Result := XMLELEMENT
					(
						name "MEDICATION_FORMS"
					,	XMLFOREST
						(
							XMLFOREST
							(
								_FTLOAD as "TYPE"
							) as header
						)
					,	XMLELEMENT
						(
							name body
						,	xmlelement
							(
								name "MEDICATION_FORMS"
							,	(
									select
									XMLAGG
									(
										XMLELEMENT
										(
											name "MEDICATION_FORM"
										,	XMLFOREST
											(
												mf.MedicationFormsID	as "ID"
											,	mf.Active				as "Active"
											,	mf.Name					as "Name"
											,	mf.FormType				as "FormType"
											,	mf.RegCode				as "RegCode"
											,	mf.FedCode				as "FedCode"
											,	mf.NameLat				as "NameLat"
											,	mf.Code					as "Code"
											,	er.IdEHR				as "EhrRusVzn431"
											,	RRB.CurrVersion			as "EhrRusVzn431Ver"
											)
										)
									)
									from pharm.T_MEDICATION_FORMS mf
									left join 
										EHR.T_EHR_RUS_VZN431 er
										on er.EhrRusID = mf.EhrRusVzn431
									LEFT JOIN 
										EHR.T_EHR_RUS_REF_BOOKS RRB 
										ON RRB.EhrRusRefBooksID = er.EhrRusRefBooks
								)
							)
						)
					);	
			ELSIF _OperationType <> _DELETE
			then
				-- собираем MEDICATION_FORMS
				select 
				XMLELEMENT
				(
					name "MEDICATION_FORMS"
				,	XMLFOREST
					(
						XMLFOREST
						(
							_OperationType as "TYPE"
						,	"ID" as "ID"
						) as header
					)
				,	XMLFOREST
					(
						XMLFOREST
						(
							mf.Active				as "Active"
						,	mf.Name					as "Name"
						,	mf.FormType				as "FormType"
						,	mf.RegCode				as "RegCode"
						,	mf.FedCode				as "FedCode"
						,	mf.NameLat				as "NameLat"
						,	mf.Code					as "Code"
						,	er.IdEHR				as "EhrRusVzn431"
						,	RRB.CurrVersion			as "EhrRusVzn431Ver"
						) as body
					)
				)
				from pharm.T_MEDICATION_FORMS mf
				left join 
					EHR.T_EHR_RUS_VZN431 er
					on er.EhrRusID = mf.EhrRusVzn431
				LEFT JOIN 
					EHR.T_EHR_RUS_REF_BOOKS RRB 
					ON RRB.EhrRusRefBooksID = er.EhrRusRefBooks
				into _Result
				where mf.MedicationFormsID = "ID";			
			END if;

			-- Отправка
			INSERT INTO akuz.T_RABBIT_SEND_OBJECT(RabbitSendObjectID, QueueName, Content, MessageID, Headers, ContentEncoding, Type, UserID, State, CreateDateTime, OwnerID)
			select uuid_generate_v4(), "QueueName", ConvertTextToBinaryZip(_Result, ''), null, null, _ContentEncoding, _Type, "User", 0, "dateTime", _OwnerID;
		ELSE 
			IF "Xml" is not null
			then

				-- Получаем тип операции
				_OperationType := (xpath('/MEDICATION_FORMS/header[1]/TYPE[1]/text()', "Xml"))[1]::varchar(6);

				-- Generate table Tmp_MEDICATION_FORMSS
				Drop Table If Exists Tmp_MEDICATION_FORMSS;
				Create Temp Table Tmp_MEDICATION_FORMSS
				as
				select	(xpath('/MEDICATION_FORM/ID[1]/text()', x))[1]::text::uuid as ID
				,	(xpath('/MEDICATION_FORM/Active[1]/text()', x))[1]::text::boolean	as Active	
				,	(xpath('/MEDICATION_FORM/Name[1]/text()', x))[1]::varchar(255) as Name
				,	(xpath('/MEDICATION_FORM/NameLat[1]/text()', x))[1]::varchar(255) as NameLat
				,	(xpath('/MEDICATION_FORM/FormType[1]/text()', x))[1]::text::int as FormType
				,	(xpath('/MEDICATION_FORM/RegCode[1]/text()', x))[1]::varchar(50) as RegCode
				,	(xpath('/MEDICATION_FORM/FedCode[1]/text()', x))[1]::varchar(50) as FedCode
				,	(xpath('/MEDICATION_FORM/Code[1]/text()', x))[1]::text::int as Code
				,	(xpath('/MEDICATION_FORM/EhrRusVzn431[1]/text()', t.x))[1]::text::int as EhrRusVzn431
				,	(xpath('/MEDICATION_FORM/EhrRusVzn431Ver[1]/text()', t.x))[1]::varchar(8) as EhrRusVzn431Ver
				from unnest(xpath('/MEDICATION_FORMS/body/MEDICATION_FORMS/MEDICATION_FORM', "Xml")) as x
				where _OperationType = _FTLOAD;

				-- Generate table #MEDICATION_FORMS
				Drop Table If Exists Tmp_MEDICATION_FORMS;
				Create Temp Table Tmp_MEDICATION_FORMS
				as
				SELECT	(xpath('/header[1]/ID[1]/text()', t.x1))[1]::text::uuid as ID
				,	(xpath('/body[1]/Active[1]/text()', t.x2))[1]::text::boolean as Active
				,	(xpath('/body[1]/Name[1]/text()', t.x2))[1]::varchar(255) as Name
				,	(xpath('/body[1]/NameLat[1]/text()', t.x2))[1]::varchar(255) as NameLat
				,	(xpath('/body[1]/FormType[1]/text()', t.x2))[1]::text::int as FormType
				,	(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	
				,	(xpath('/body[1]/EhrRusVzn431[1]/text()', t.x2))[1]::text::int as EhrRusVzn431
				,	(xpath('/body[1]/EhrRusVzn431Ver[1]/text()', t.x2))[1]::varchar(8) as EhrRusVzn431Ver		
				from
				(
					select	unnest(xpath('/MEDICATION_FORMS/header', x)) as x1
					,	unnest(xpath('/MEDICATION_FORMS/body', x)) as x2
					from unnest(xpath('/MEDICATION_FORMS', "Xml"::xml)) x
				) t
				where _OperationType <> _FTLOAD;

				-- Обработка операции
				if _OperationType = FTLOAD
				then
					-- Первичная загрузка
					-- Лек. формы не из справочника RDS переводим в неактивные
					update pharm.T_MEDICATION_FORMS mf
					set Active = '0'
					where not exists(select* from Tmp_MEDICATION_FORMSS nmf where nmf.ID = mf.MedicationFormsID);

					-- Обновляем имеющиеся лек. формы
					update pharm.T_MEDICATION_FORMS mf
					set	Name = nmf.Name
					,	Active = nmf.Active
					,	FormType = coalesce(nmf.FormType,0)
					,	RegCode = nmf.RegCode
					,	FedCode = nmf.FedCode
					,	NameLat = nmf.NameLat
					,	Code = nmf.Code
					,	EhrRusVzn431 = ehr.EhrRusID
					from Tmp_MEDICATION_FORMSS nmf 
					left join lateral
					(
						select er.EhrRusID 
						from EHR.T_EHR_RUS_VZN431 er
						LEFT JOIN 
							EHR.T_EHR_RUS_REF_BOOKS RRB 
							ON RRB.EhrRusRefBooksID = er.EhrRusRefBooks
						where er.IdEHR = nmf.EhrRusVzn431 and rrb.CurrVersion = nmf.EhrRusVzn431Ver
						limit 1
					) ehr on true
					where nmf.ID = mf.MedicationFormsID;

					-- Загружаем новые
					INSERT INTO PHARM.T_MEDICATION_FORMS(MedicationFormsID, Name, Active, FormType, RegCode, FedCode, NameLat, Code, EhrRusVzn431)
            		SELECT nmf.ID, nmf.Name, nmf.Active, coalesce(nmf.FormType,0), nmf.RegCode, nmf.FedCode, nmf.NameLat, nmf.Code, ehr.EhrRusID
            		from Tmp_MEDICATION_FORMSS nmf
					left join lateral
					(
						select er.EhrRusID 
						from EHR.T_EHR_RUS_VZN431 er
						LEFT JOIN 
							EHR.T_EHR_RUS_REF_BOOKS RRB 
							ON RRB.EhrRusRefBooksID = er.EhrRusRefBooks
						where er.IdEHR = nmf.EhrRusVzn431 and rrb.CurrVersion = nmf.EhrRusVzn431Ver
						limit 1
					) ehr on true
            		where not exists(select* from PHARM.T_MEDICATION_FORMS mf where mf.MedicationFormsID = nmf.ID);
				elsif _OperationType = _DELETE
				then
					-- Удаление
					update pharm.T_MEDICATION_FORMS mf
					set Active = '0'
					from Tmp_MEDICATION_FORMS nmf 
					where nmf.ID = mf.MedicationFormsID;
				else
					-- update pharm.T_MEDICATION_FORMS
					update pharm.T_MEDICATION_FORMS mf
					set Name = nmf.Name
					,	Active = nmf.Active
					,	FormType = coalesce(nmf.FormType, 0)
					,	RegCode = nmf.RegCode
					,	FedCode = nmf.FedCode
					,	NameLat = nmf.NameLat
					,	Code = nmf.Code
					,	EhrRusVzn431 = ehr.EhrRusID
					from Tmp_MEDICATION_FORMS nmf 
					left join lateral
					(
						select er.EhrRusID 
						from EHR.T_EHR_RUS_VZN431 er
						LEFT JOIN 
							EHR.T_EHR_RUS_REF_BOOKS RRB 
							ON RRB.EhrRusRefBooksID = er.EhrRusRefBooks
						where er.IdEHR = nmf.EhrRusVzn431 and rrb.CurrVersion = nmf.EhrRusVzn431Ver
						limit 1
					) ehr on true
					where nmf.ID = mf.MedicationFormsID 
					and _OperationType = _UPDATE;

					-- insert pharm.T_MEDICATION_FORMS
					INSERT INTO PHARM.T_MEDICATION_FORMS(MedicationFormsID, Name, Active, FormType, RegCode, FedCode, NameLat, Code, EhrRusVzn431)
            		SELECT nmf.ID, nmf.Name, nmf.Active, coalesce(nmf.FormType, 0), nmf.RegCode, nmf.FedCode, nmf.NameLat, nmf.Code, ehr.EhrRusID
            		from Tmp_MEDICATION_FORMS nmf

            		where not exists(select* from PHARM.T_MEDICATION_FORMS mf where mf.MedicationFormsID = nmf.ID)
					and _OperationType = _INSERT;
				END if;	
				
				_EntId := (select EntityID from VCLib.T_ENTITIES where EntName = 'MEDICATION_FORMS' and Schema = 'PHARM' limit 1);
				if exists 
				(
					select* from AKUZ.T_GEN_NUMBERS where EntityID = _EntId and NumberName = 'PHARM.MEDICATION_FORMS' Limit 1
				)
				then
					update AKUZ.T_GEN_NUMBERS
					set NextNumber = coalesce((select MAX(Code) from PHARM.T_MEDICATION_FORMS), 1) --+ 1
					where EntityID = _EntId
					and NumberName = 'PHARM.MEDICATION_FORMS';
				else
					INSERT INTO AKUZ.T_GEN_NUMBERS(GenNumbersID, EntityID, NumberName, NextNumber)
					VALUES(uuid_generate_v4(), _EntId, 'PHARM.MEDICATION_FORMS', coalesce((select MAX(OtherID) from PHARM.T_MEDICATION_FORMS), 1));
				end if;	
				
				DROP TABLE if exists Tmp_MEDICATION_FORMS;
				DROP TABLE if exists Tmp_MEDICATION_FORMSS;
			end if;
		END if;
	end if;
End;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;
ALTER FUNCTION sp_rds_medicationforms(xml, boolean, uuid, varchar(100), timestamp, uuid, text)
  OWNER TO postgres;
