-- =============================================
-- Author:      Vitacore (с)
-- Create date: 2015-07-29
-- Description: Выгрузка/загрузка справочника доз в(из) RDS
-- =============================================
CREATE OR REPLACE FUNCTION sp_RDS_DoseView
(
	"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;
	_OwnerID varchar(255);
	_ContentEncoding varchar(255);
	_OperationType varchar(255);
	_Type varchar(255);
	_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_DOSE_VIEW;
		CREATE TEMP TABLE Temp_DOSE_VIEW(Name text, Code INT, ViewType INT, OtherID INT, FullName VARCHAR, FedCode VARCHAR, RegCode VARCHAR
										 , EhrRusS0JLHGTLBJ INT, EhrRusS0JLHGTLBJVer VARCHAR);
			
		_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, FedCode VARCHAR, RegCode VARCHAR, EhrRusS0JLHGTLBJ INT, EhrRusS0JLHGTLBJVer VARCHAR) ';

		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
			-- Инициализация
			_ContentEncoding := 'utf-8';
			_Type := 'DOSE_VIEW';
			_OwnerID := uuid_generate_v4();
			--выясняем тип операции
			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 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('/DOSE_VIEW/header[1]/TYPE[1]/text()', "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(50) as EhrRusS0JLHGTLBJVer
				from unnest(xpath('/DOSE_VIEW/body/DOSE_VIEW_LIST/DOSE_VIEW', "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()', x))[1]::varchar(50) as RegCode
				,	(xpath('/body[1]/FedCode[1]/text()', x))[1]::varchar(50) as FedCode
				,	(xpath('/body[1]/EhrRusS0JLHGTLBJ[1]/text()', x))[1]::text::int as EhrRusS0JLHGTLBJ
				,	(xpath('/body[1]/EhrRusS0JLHGTLBJVer[1]/text()', x))[1]::varchar(50) 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 = dvl.EhrRusS0JLHGTLBJ AND RRB.CURRVERSION = DVL.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 AKUZ.T_GEN_NUMBERS where EntityID = _EntId and NumberName = 'PHARM.DOSE_VIEW/0' Limit 1
				)
				then
					---- генерируем след. номер
					--perform public.sp_GenerateNumber(@EntId, 'PHARM.C_TRN');
						
					update AKUZ.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 AKUZ.T_GEN_NUMBERS(GenNumbersID, EntityID, NumberName, NextNumber)
					VALUES(uuid_generate_v4(), _EntId, 'PHARM.DOSE_VIEW/0', coalesce((select MAX(OtherID) from PHARM.T_DOSE_VIEW where ViewType = 0), 1));
				end if;
				
				if exists 
				(
					select* from AKUZ.T_GEN_NUMBERS where EntityID = _EntId and NumberName = 'PHARM.DOSE_VIEW/1' Limit 1
				)
				then
					update AKUZ.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 AKUZ.T_GEN_NUMBERS(GenNumbersID, EntityID, NumberName, NextNumber)
					VALUES(uuid_generate_v4(), _EntId, 'PHARM.DOSE_VIEW/1', coalesce((select MAX(OtherID) from PHARM.T_DOSE_VIEW where ViewType = 1), 1));
				end if;
				
				if exists 
				(
					select* from AKUZ.T_GEN_NUMBERS where EntityID = _EntId and NumberName = 'PHARM.DOSE_VIEW/2' Limit 1
				)
				then
					update AKUZ.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 AKUZ.T_GEN_NUMBERS(GenNumbersID, EntityID, NumberName, NextNumber)
					VALUES(uuid_generate_v4(), _EntId, 'PHARM.DOSE_VIEW/2', coalesce((select MAX(OtherID) from PHARM.T_DOSE_VIEW where ViewType = 2), 1));
				end if;
				
				if exists 
				(
					select* from AKUZ.T_GEN_NUMBERS where EntityID = _EntId and NumberName = 'PHARM.DOSE_VIEW/3' Limit 1
				)
				then
					update AKUZ.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 AKUZ.T_GEN_NUMBERS(GenNumbersID, EntityID, NumberName, NextNumber)
					VALUES(uuid_generate_v4(), _EntId, 'PHARM.DOSE_VIEW/3', coalesce((select MAX(OtherID) from PHARM.T_DOSE_VIEW where ViewType = 3), 1));
				end if;
				
				if exists 
				(
					select* from AKUZ.T_GEN_NUMBERS where EntityID = _EntId and NumberName = 'PHARM.DOSE_VIEW/4' Limit 1
				)
				then
					update AKUZ.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 AKUZ.T_GEN_NUMBERS(GenNumbersID, EntityID, NumberName, NextNumber)
					VALUES(uuid_generate_v4(), _EntId, 'PHARM.DOSE_VIEW/4', coalesce((select MAX(OtherID) from PHARM.T_DOSE_VIEW where ViewType = 4), 1));
				end if;
				
				if exists 
				(
					select* from AKUZ.T_GEN_NUMBERS where EntityID = _EntId and NumberName = 'PHARM.DOSE_VIEW/5' Limit 1
				)
				then
					update AKUZ.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 AKUZ.T_GEN_NUMBERS(GenNumbersID, EntityID, NumberName, NextNumber)
					VALUES(uuid_generate_v4(), _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;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;
ALTER FUNCTION sp_RDS_DoseView(xml, boolean, uuid, varchar(100), timestamp, uuid, text)
  OWNER TO postgres;
