-- =============================================
-- Author:      Vitacore (с)
-- Create date: 2016-02-08
-- Description: Выгрузка/загрузка справочника Лекартсвенных форм в(из) RDS
-- =============================================
DROP PROCEDURE IF EXISTS DBO.sp_rds_medicationforms;

DROP PROCEDURE IF EXISTS APS.sp_rds_medicationforms;

CREATE OR REPLACE PROCEDURE APS.sp_rds_medicationforms
(
	"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) := 'MEDICATION_FORMS';
		_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_MEDICATION_FORMS;
			CREATE TEMP TABLE Temp_MEDICATION_FORMS
				(
					 Name				VARCHAR(255)
					,Code				INT
					,Active				boolean
					,FormType			INT
					,NameLat			VARCHAR(4000)
					,FedCode			VARCHAR(50)
					,RegCode			VARCHAR(50)
					,MedicationFormsID	uuid
					,EhrRusVzn431       INT
					,EhrRusVzn431Ver    VARCHAR(8)
					,PackageType		uuid);
				
			_sql := 'insert into Temp_MEDICATION_FORMS
					(
						Name, 
						Code, 
						Active, 
						FormType, 
						NameLat, 
						FedCode, 
						RegCode, 
						MedicationFormsID,
						ehr.IdEHR,
						RRB.CurrVersion
						,PackageType
					);' 
				|| ' SELECT 
						med.Name, 
						med.Code, 
						med.Active, 
						med.FormType, 
						med.NameLat, 
						med.FedCode, 
						med.RegCode, 
						med.MedicationFormsID, 
						EhrRusVzn431, 
						EhrRusVzn431Ver
						,PackageType'
				|| '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
						,mp.PackageType
				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(255)
					,Code				INT
					,Active				boolean
					,FormType			INT
					,NameLat			VARCHAR(4000)
					,FedCode			VARCHAR(50)
					,RegCode			VARCHAR(50)
					,MedicationFormsID	uuid
					,EhrRusVzn431       INT
					,EhrRusVzn431Ver    VARCHAR(8))
					,PackageType		uuid ';

			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
				,PackageType = nmf.PackageType
			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,PackageType)
			SELECT 
				nmf.MedicationFormsID, 
				nmf.Name, 
				nmf.Active, 
				coalesce(nmf.FormType,0), 
				nmf.RegCode, 
				nmf.FedCode, 
				nmf.NameLat, 
				nmf.Code, 
				ehr.EhrRusID,
				nmf.PackageType
			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
				--выясняем тип операции
				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"
												,	mf.PackageType			as "PackageType"
												)
											)
										)
										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"
							,	mf.PackageType			as "PackageType"
							) 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 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

					-- Получаем тип операции
					_OperationType := (xpath('/MEDICATION_FORMS/header[1]/TYPE[1]/text()', "Xml"::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/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
					,	(xpath('/MEDICATION_FORM/PackageType[1]/text()', x))[1]::text::uuid as PackageType
					from unnest(xpath('/MEDICATION_FORMS/body/MEDICATION_FORMS/MEDICATION_FORM', "Xml"::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	
					,	(xpath('/body[1]/PackageType[1]/text()', t.x2))[1]::text::uuid as PackageType	
					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
						,	PackageType = nmf.PackageType
						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,PackageType)
		        		SELECT 
							nmf.ID, 
							nmf.Name, 
							nmf.Active, 
							coalesce(nmf.FormType,0), 
							nmf.RegCode, 
							nmf.FedCode, 
							nmf.NameLat, 
							nmf.Code, 
							ehr.EhrRusID,
							nmf.PackageType 
		        		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
						,	PackageType = nmf.PackageType
						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,PackageType)
		        		SELECT 
							nmf.ID, 
							nmf.Name, 
							nmf.Active, 
							coalesce(nmf.FormType, 0), 
							nmf.RegCode, 
							nmf.FedCode, 
							nmf.NameLat, 
							nmf.Code, 
							ehr.EhrRusID,
							nmf.PackageType
		        		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 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 VCLib.T_GEN_NUMBERS where EntityID = _EntId and NumberName = 'PHARM.MEDICATION_FORMS' Limit 1
					)
					then
						update VCLib.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 VCLib.T_GEN_NUMBERS
							(GenNumbersID, EntityID, NumberName, NextNumber)
						VALUES
							(gen_random_uuid(), _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;
End;
$$;