-- =============================================
-- Author:      Vitacore (с)
-- Create date: 2015-07-29
-- Description: Выгрузка/загрузка справочника доз в(из) RDS
-- =============================================
DROP PROCEDURE IF EXISTS DBO.sp_RDS_DoseView;

DROP PROCEDURE IF EXISTS APS.sp_RDS_DoseView;

CREATE OR REPLACE PROCEDURE APS.sp_RDS_DoseView
(
	"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) := 'DOSE_VIEW';
		_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_DOSE_VIEW;
			CREATE TEMP TABLE Temp_DOSE_VIEW
				(
					Name					text, 
					Code					INT, 
					ViewType				INT, 
					OtherID					INT, 
					FullName				VARCHAR(4000), 
					FedCode					VARCHAR(50), 
					RegCode					VARCHAR(50),
					EhrRusS0JLHGTLBJ		INT, 
					EhrRusS0JLHGTLBJVer		VARCHAR(8)
				);
				
			_sql := 'insert into Temp_DOSE_VIEW
					(
						Name, 
						Code, 
						ViewType, 
						OtherID, 
						FullName, 
						FedCode, 
						RegCode, 
						EhrRusS0JLHGTLBJ, 
						EhrRusS0JLHGTLBJVer
					);' 
				|| ' SELECT dv.Name, dv.Code, dv.ViewType, dv.OtherID, dv.FullName, dv.FedCode, dv.RegCode, dv.EhrRusS0JLHGTLBJ, dv.EhrRusS0JLHGTLBJVer '
				|| 'FROM dblink(''dbname=' || "PATH" 
				|| ''','
				|| ''' SELECT tdv.Name, tdv.Code, tdv.ViewType, tdv.OtherID, tdv.FullName, tdv.FedCode, tdv.RegCode, EHR.IdEHR AS EhrRusS0JLHGTLBJ, EHR.CurrVersion AS EhrRusS0JLHGTLBJVer
				FROM PHARM.T_DOSE_VIEW TDV
				left join lateral
				(
					SELECT EH.IDEHR, rrb.CurrVersion 
					FROM EHR.T_EHR_RUS_S0JLHGTLBJ EH
						LEFT JOIN EHR.T_EHR_RUS_REF_BOOKS RRB ON RRB.EhrRusRefBooksID = eh.EhrRusRefBooks
					WHERE TDV.EhrRusS0JLHGTLBJ = EH.EHRRUSID
					limit 1
				) EHR on true '') ' 
					|| ' AS dv(Name					text, 
					Code					INT, 
					ViewType				INT, 
					OtherID					INT, 
					FullName				VARCHAR(4000), 
					FedCode					VARCHAR(50), 
					RegCode					VARCHAR(50),
					EhrRusS0JLHGTLBJ		INT, 
					EhrRusS0JLHGTLBJVer		VARCHAR(8)) ';

			EXECUTE _SQL;
				
			-- Обновляем имеющиеся
			update PHARM.T_DOSE_VIEW dv
				set Name = dvl.Name
				,FullName = dvl.FullName
				,OtherID  = dvl.OtherID
				,ViewType = dvl.ViewType
				,RegCode  = dvl.RegCode
				,FedCode  = dvl.FedCode
				,EhrRusS0JLHGTLBJ = ehr.EhrRusID
			from Temp_DOSE_VIEW dvl 
			left join lateral
			(
				select er.EhrRusID 
				from EHR.T_EHR_RUS_S0JLHGTLBJ er
					LEFT JOIN EHR.T_EHR_RUS_REF_BOOKS RRB ON RRB.EhrRusRefBooksID = er.EhrRusRefBooks
				where er.IdEHR = dvl.EhrRusS0JLHGTLBJ AND RRB.CURRVERSION = DVL.EhrRusS0JLHGTLBJVer
				limit 1
			) ehr on true
			where dvl.Code = dv.Code;
		
			-- Загружаем новые
			INSERT INTO PHARM.T_DOSE_VIEW
				(
					Code, 
					Name, 
					FullName, 
					OtherID, 
					ViewType, 
					RegCode, 
					FedCode, 
					EhrRusS0JLHGTLBJ
				)
			SELECT 
				dvl.Code, 
				dvl.Name, 
				dvl.FullName, 
				dvl.OtherID, 
				dvl.ViewType, 
				RegCode, 
				FedCode, 
				ehr.EhrRusID
			from Temp_DOSE_VIEW dvl
			left join lateral
			(
				select er.EhrRusID 
				from EHR.T_EHR_RUS_S0JLHGTLBJ er
					LEFT JOIN EHR.T_EHR_RUS_REF_BOOKS RRB ON RRB.EhrRusRefBooksID = er.EhrRusRefBooks
				where er.IdEHR = dvl.EhrRusS0JLHGTLBJ AND RRB.CURRVERSION = DVL.EhrRusS0JLHGTLBJVer
				limit 1
			) ehr on true
			WHERE NOT EXISTS
				(
					SELECT * 
						FROM PHARM.T_DOSE_VIEW dv 
					WHERE dv.Code = dvl.Code);
		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_DOSE_VIEW dv where dv.Code = "ID")
				then
					_OperationType := _UPDATE;
				elsif "IsNew" = 0 and not exists(select* from Pharm.T_DOSE_VIEW dv where dv.Code = "ID")
				then
					_OperationType := _DELETE;
					_Result := XMLELEMENT(name "DOSE_VIEW", XMLELEMENT(name header, XMLFOREST(_OperationType as "TYPE", "ID" as "ID")));
				end if;

				-- Обработка операции
				if _OperationType = _FTLOAD
				then	-- Первичная загрузка
					_Result :=
					XMLELEMENT
					(
						name "DOSE_VIEW"
					,	XMLELEMENT
						(
							name header
						,	XMLFOREST
							(
								_FTLOAD as "TYPE"
							)
						)
					,	XMLELEMENT
						(
							name body
						,	xmlelement
							(
								name "DOSE_VIEW_LIST"
							,	xmlelement
								(
									name "DOSE_VIEW"
								,	(
										select	
										xmlagg
										(
											xmlforest
											(
												dv.Code				as "ID"
											,	dv.Name				as "Name"
											,	dv.FullName			as "FullName"
											,	dv.OtherID			as "OtherID"
											,	dv.ViewType			as "ViewType"
											,	dv.RegCode			as "RegCode"
									  		,	dv.FedCode			as "FedCode"
									  		,	er.IdEHR			as "EhrRusS0JLHGTLBJ"
									  		,	RRB.CurrVersion		as "EhrRusS0JLHGTLBJVer"
											)
										)
										from PHARM.T_DOSE_VIEW dv
											left join EHR.T_EHR_RUS_S0JLHGTLBJ er on er.EhrRusID = dv.EhrRusS0JLHGTLBJ
											left join EHR.T_EHR_RUS_REF_BOOKS RRB ON RRB.EhrRusRefBooksID = er.EhrRusRefBooks 
									)
								)
							)
						)
					);
				elsif _OperationType <> _DELETE
				then
					-- собираем DOSE_VIEW
					select 
					XMLELEMENT
					(
						name "DOSE_VIEW"
					,	XMLELEMENT
						(
							name header
						,	XMLFOREST
							(
								_OperationType as "TYPE"
							,	"ID" as "ID"
							)
						)
					,	XMLELEMENT
						(
							name body
						,	XMLFOREST
							(
								dv.Name				as "Name"
							,	dv.FullName			as "FullName"
							,	dv.OtherID			as "OtherID"
							,	dv.ViewType			as "ViewType"
							,	dv.RegCode			as "RegCode"
							,	dv.FedCode			as "FedCode"
							,	er.IdEHR			as "EhrRusS0JLHGTLBJ"
							,	RRB.CurrVersion		as "EhrRusS0JLHGTLBJVer"
							)
						)
					)				
					from PHARM.T_DOSE_VIEW dv
						left join EHR.T_EHR_RUS_S0JLHGTLBJ er on er.EhrRusID = dv.EhrRusS0JLHGTLBJ
						left join EHR.T_EHR_RUS_REF_BOOKS RRB ON RRB.EhrRusRefBooksID = er.EhrRusRefBooks
					into _Result
					where dv.Code = "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('/DOSE_VIEW/header[1]/TYPE[1]/text()', "Xml"::xml))[1]::varchar(36);

					-- Generate table Tmp_DOSE_VIEW_LIST
					Drop Table If Exists Tmp_DOSE_VIEW_LIST;
					Create Temp Table Tmp_DOSE_VIEW_LIST
					as
					SELECT
						(xpath('/DOSE_VIEW/ID[1]/text()', x))[1]::text::int as ID
					,	(xpath('/DOSE_VIEW/Name[1]/text()', x))[1]::varchar(256) as Name
					,	(xpath('/DOSE_VIEW/FullName[1]/text()', x))[1]::varchar(4000) as FullName
					,	(xpath('/DOSE_VIEW/OtherID[1]/text()', x))[1]::text::int as OtherID
					,	(xpath('/DOSE_VIEW/ViewType[1]/text()', x))[1]::text::int as ViewType
					,	(xpath('/DOSE_VIEW/RegCode[1]/text()', x))[1]::varchar(50) as RegCode
					,	(xpath('/DOSE_VIEW/FedCode[1]/text()', x))[1]::varchar(50) as FedCode
					,	(xpath('/DOSE_VIEW/EhrRusS0JLHGTLBJ[1]/text()', x))[1]::text::int as EhrRusS0JLHGTLBJ
					,	(xpath('/DOSE_VIEW/EhrRusS0JLHGTLBJVer[1]/text()', x))[1]::varchar(8) as EhrRusS0JLHGTLBJVer
					from unnest(xpath('/DOSE_VIEW/body/DOSE_VIEW_LIST/DOSE_VIEW', "Xml"::xml)) as x
					where _OperationType = _FTLOAD;			

					-- Generate table Tmp_DOSE_VIEW
					Drop Table If Exists Tmp_DOSE_VIEW;
					Create Temp Table Tmp_DOSE_VIEW
					as
					SELECT	(xpath('/header[1]/ID[1]/text()', t.x1))[1]::text::int as ID
					,	(xpath('/body[1]/Name[1]/text()', t.x2))[1]::varchar(256) as Name
					,	(xpath('/body[1]/FullName[1]/text()', t.x2))[1]::varchar(4000) as FullName			
					,	(xpath('/body[1]/OtherID[1]/text()', t.x2))[1]::text::int as OtherID
					,	(xpath('/body[1]/ViewType[1]/text()', t.x2))[1]::text::int as ViewType
					,	(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]/EhrRusS0JLHGTLBJ[1]/text()', t.x2))[1]::text::int as EhrRusS0JLHGTLBJ
					,	(xpath('/body[1]/EhrRusS0JLHGTLBJVer[1]/text()', t.x2))[1]::varchar(8) as EhrRusS0JLHGTLBJVer
					from
					(
						select	unnest(xpath('/DOSE_VIEW/header', x)) as x1
						,	unnest(xpath('/DOSE_VIEW/body', x)) as x2
						from unnest(xpath('/DOSE_VIEW', "Xml"::xml)) x
					) t
					where _OperationType <> _FTLOAD;

					-- Обработка операции
					if _OperationType = _FTLOAD
					then
						-- Обновляем имеющиеся
						update PHARM.T_DOSE_VIEW dv
						set	Name = dvl.Name
							,FullName = dvl.FullName
							,OtherID = dvl.OtherID
							,ViewType = dvl.ViewType
							,RegCode = dvl.RegCode
				    		,FedCode = dvl.FedCode
				    		,EhrRusS0JLHGTLBJ = ehr.EhrRusID
						from Tmp_DOSE_VIEW_LIST dvl 
						left join lateral
						(
							select er.EhrRusID 
							from EHR.T_EHR_RUS_S0JLHGTLBJ er
								LEFT JOIN EHR.T_EHR_RUS_REF_BOOKS RRB ON RRB.EhrRusRefBooksID = er.EhrRusRefBooks
							where er.IdEHR = dvl.EhrRusS0JLHGTLBJ AND RRB.CURRVERSION = DVL.EhrRusS0JLHGTLBJVer
							limit 1
						) ehr on true
						where dvl.ID = dv.Code;

						-- Загружаем новые
						INSERT INTO PHARM.T_DOSE_VIEW
							(Code, Name, FullName, OtherID, ViewType, RegCode, FedCode, EhrRusS0JLHGTLBJ)
						SELECT 
							dvl.ID, 
							dvl.Name, 
							dvl.FullName, 
							dvl.OtherID, 
							dvl.ViewType, 
							dvl.RegCode, 
							dvl.FedCode, 
							ehr.EhrRusID
						from Tmp_DOSE_VIEW_LIST dvl
						left join lateral
						(
							select er.EhrRusID 
							from EHR.T_EHR_RUS_S0JLHGTLBJ er
								LEFT JOIN EHR.T_EHR_RUS_REF_BOOKS RRB ON RRB.EhrRusRefBooksID = er.EhrRusRefBooks
							where er.IdEHR = dvl.EhrRusS0JLHGTLBJ AND RRB.CURRVERSION = DVL.EhrRusS0JLHGTLBJVer
							limit 1
						) ehr on true
						where not exists(select* from PHARM.T_DOSE_VIEW dv where dv.Code = dvl.ID);			
					else
						update PHARM.T_DOSE_VIEW d
						set	Name = dv.Name
							,FullName = dv.FullName
							,OtherID = dv.OtherID
							,ViewType = dv.ViewType
							,RegCode = dv.RegCode
				    		,FedCode = dv.FedCode
				    		,EhrRusS0JLHGTLBJ = ehr.EhrRusID
						from Tmp_DOSE_VIEW dv 
						left join lateral
						(
							select er.EhrRusID 
							from EHR.T_EHR_RUS_S0JLHGTLBJ er
								LEFT JOIN EHR.T_EHR_RUS_REF_BOOKS RRB ON RRB.EhrRusRefBooksID = er.EhrRusRefBooks
							where er.IdEHR = dvl.EhrRusS0JLHGTLBJ AND RRB.CURRVERSION = DVL.EhrRusS0JLHGTLBJVer
							limit 1
						) ehr on true
						where dv.ID = d.Code
						and _OperationType = _UPDATE;

						-- Загружаем новый
						INSERT INTO PHARM.T_DOSE_VIEW
							(Code, Name, FullName, OtherID, ViewType, RegCode, FedCode, EhrRusS0JLHGTLBJ)
						SELECT 
							dv.ID,
							dv.Name, 
							dv.FullName, 
							dv.OtherID, 
							dv.ViewType, 
							dv.RegCode, 
							dv.FedCode, 
							ehr.EhrRusID
						from Tmp_DOSE_VIEW dv
						left join lateral
						(
							select er.EhrRusID 
							from EHR.T_EHR_RUS_S0JLHGTLBJ er
								LEFT JOIN EHR.T_EHR_RUS_REF_BOOKS RRB ON RRB.EhrRusRefBooksID = er.EhrRusRefBooks
							where er.IdEHR = dv.EhrRusS0JLHGTLBJ AND RRB.CURRVERSION = dv.EhrRusS0JLHGTLBJVer
							limit 1
						) ehr on true
						where not exists 
							(
								select * 
									from PHARM.T_DOSE_VIEW dvv 
								where dvv.Code = dv.ID
							)
						and _OperationType = _INSERT;
					END if;

					_EntId := (select EntityID from VCLib.T_ENTITIES where EntName = 'DOSE_VIEW' and Schema = 'PHARM' limit 1);

					if exists (select * from VCLib.T_GEN_NUMBERS where EntityID = _EntId and NumberName = 'PHARM.DOSE_VIEW/0' Limit 1)
					then
						---- генерируем след. номер
						--perform public.sp_GenerateNumber(@EntId, 'PHARM.C_TRN');
						update VCLib.T_GEN_NUMBERS
							set NextNumber = coalesce((select MAX(Code) from PHARM.T_DOSE_VIEW where ViewType = 0), 1) --+ 1
						where EntityID = _EntId and NumberName = 'PHARM.DOSE_VIEW/0';
					else
						INSERT INTO VCLib.T_GEN_NUMBERS
							(GenNumbersID, EntityID, NumberName, NextNumber)
						VALUES
							(gen_random_uuid(), _EntId, 'PHARM.DOSE_VIEW/0', coalesce((select MAX(OtherID) from PHARM.T_DOSE_VIEW where ViewType = 0), 1));
					end if;
					
					if exists (select * from VCLib.T_GEN_NUMBERS where EntityID = _EntId and NumberName = 'PHARM.DOSE_VIEW/1' Limit 1)
					then
						update VCLib.T_GEN_NUMBERS
							set NextNumber = coalesce((select MAX(Code) from PHARM.T_DOSE_VIEW where ViewType = 1), 1) --+ 1
						where EntityID = _EntId and NumberName = 'PHARM.DOSE_VIEW/1';
					else
						INSERT INTO VCLib.T_GEN_NUMBERS(GenNumbersID, EntityID, NumberName, NextNumber)
						VALUES(gen_random_uuid(), _EntId, 'PHARM.DOSE_VIEW/1', coalesce((select MAX(OtherID) from PHARM.T_DOSE_VIEW where ViewType = 1), 1));
					end if;
					
					if exists (select * from VCLib.T_GEN_NUMBERS where EntityID = _EntId and NumberName = 'PHARM.DOSE_VIEW/2' Limit 1)
					then
						update VCLib.T_GEN_NUMBERS
						set NextNumber = coalesce((select MAX(Code) from PHARM.T_DOSE_VIEW where ViewType = 2), 1) --+ 1
						where EntityID = _EntId
						and NumberName = 'PHARM.DOSE_VIEW/2';
					else
						INSERT INTO VCLib.T_GEN_NUMBERS(GenNumbersID, EntityID, NumberName, NextNumber)
						VALUES(gen_random_uuid(), _EntId, 'PHARM.DOSE_VIEW/2', coalesce((select MAX(OtherID) from PHARM.T_DOSE_VIEW where ViewType = 2), 1));
					end if;
					
					if exists (select * from VCLib.T_GEN_NUMBERS where EntityID = _EntId and NumberName = 'PHARM.DOSE_VIEW/3' Limit 1)
					then
						update VCLib.T_GEN_NUMBERS
						set NextNumber = coalesce((select MAX(Code) from PHARM.T_DOSE_VIEW where ViewType = 3), 1) --+ 1
						where EntityID = _EntId
						and NumberName = 'PHARM.DOSE_VIEW/3';
					else
						INSERT INTO VCLib.T_GEN_NUMBERS(GenNumbersID, EntityID, NumberName, NextNumber)
						VALUES(gen_random_uuid(), _EntId, 'PHARM.DOSE_VIEW/3', coalesce((select MAX(OtherID) from PHARM.T_DOSE_VIEW where ViewType = 3), 1));
					end if;
					
					if exists (select * from VCLib.T_GEN_NUMBERS where EntityID = _EntId and NumberName = 'PHARM.DOSE_VIEW/4' Limit 1)
					then
						update VCLib.T_GEN_NUMBERS
						set NextNumber = coalesce((select MAX(Code) from PHARM.T_DOSE_VIEW where ViewType = 4), 1) --+ 1
						where EntityID = _EntId
						and NumberName = 'PHARM.DOSE_VIEW/4';
					else
						INSERT INTO VCLib.T_GEN_NUMBERS(GenNumbersID, EntityID, NumberName, NextNumber)
						VALUES(gen_random_uuid(), _EntId, 'PHARM.DOSE_VIEW/4', coalesce((select MAX(OtherID) from PHARM.T_DOSE_VIEW where ViewType = 4), 1));
					end if;
					
					if exists (select * from VCLib.T_GEN_NUMBERS where EntityID = _EntId and NumberName = 'PHARM.DOSE_VIEW/5' Limit 1)
					then
						update VCLib.T_GEN_NUMBERS
						set NextNumber = coalesce((select MAX(Code) from PHARM.T_DOSE_VIEW where ViewType = 5), 1) --+ 1
						where EntityID = _EntId
						and NumberName = 'PHARM.DOSE_VIEW/5';
					else
						INSERT INTO VCLib.T_GEN_NUMBERS(GenNumbersID, EntityID, NumberName, NextNumber)
						VALUES(gen_random_uuid(), _EntId, 'PHARM.DOSE_VIEW/5', coalesce((select MAX(OtherID) from PHARM.T_DOSE_VIEW where ViewType = 5), 1));
					end if;

					-- Drop tables
					Drop Table If Exists Tmp_DOSE_VIEW;
					Drop Table If Exists Tmp_DOSE_VIEW_LIST;
				end if;
			END if;
		end if;
	End;
End;
$$;