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

CREATE OR REPLACE PROCEDURE APS.sp_RDS_Pharmacy
(
	"Xml" xml DEFAULT NULL::xml
,	"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
)
    language plpgsql
as
$$
Begin

	Declare	_Result text;
		_ContentEncoding varchar(255) := 'utf-8';
		_Type varchar(255) := 'PHARMACY';
		_OperationType varchar(255);
		_OwnerID UUID := "ID";
		-- Operation types
		_INSERT varchar(6) := 'Insert';
		_UPDATE varchar(6) := 'Update';
		_DELETE varchar(6) := 'Delete';
		_FTLOAD varchar(6) := 'FTLoad';
	Begin
		"dateTime" := Coalesce("dateTime", Current_TimeStamp::TIMESTAMP);

		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 APS.T_PHARMACY p where p.Code = "ID")
			then
				_OperationType := _UPDATE;
			ELSIF "IsNew" = '0' and not exists (select * from APS.T_PHARMACY p where p.Code = "ID")
			then
				_OperationType := _DELETE;
				_Result := xmlelement
					  (
						name "PHARMACY"
					  ,	xmlforest
						(
							xmlforest
							(
								_OperationType as "TYPE"
							,	"ID"         as "ID"
							) as "header"
						)
					  );
			END if;
			-- Обработка операции
			IF _OperationType = _FTLOAD
			then
				-- Первичная загрузка
				_Result := 
				xmlelement
				(
					name "PHARMACY"
				,	xmlforest
					(
						xmlforest
						(
							_FTLOAD as "TYPE"
						) as "header"
					)
				,	xmlelement
					(
						name "body"
					,	xmlelement
						(
							name "PHARMACYS"
						,	(
								select
								xmlagg
								(
									xmlforest
									(
										xmlforest
										(
											p.Code                                 	as "ID"
										,	p.Name                                 	as "Name"
										,	p.NameFull                             	as "NameFull"
										,	p.OGRN                                 	as "OGRN"
										,	p.Terr                                 	as "Terr"
										,	p.OKATO                                	as "OKATO"
										,	to_char(p.DateBegin, 'DD.MM.YYYY')		as "DateBegin"
										,	to_char(p.DateEnd, 'DD.MM.YYYY')   		as "DateEnd"
										,	to_char("DateTime", 'DD.MM.YYYY')		as "RegisterDate"
										,	p.RecipeServedTypes						as "RecipeServedTypes"
										,	p.Address								as "Address"
										,	r.Code									as "Rajon"
										,	p.Number								as "Number"
										,	p.Phone									as "Phone"
										,	p.Dlo									as "Dlo"
										,	p.LicenseNumber							as "LicenseNumber"
										,	p.LicenseIssue							as "LicenseIssue"
										,	p.LicenseDate							as "LicenseDate"
										,	p.FiasAddress							as "FiasAddress"
										,	p.PostalCode							as "PostalCode"
										,	p.FiasHome								as "FiasHome"
										,	e.IdEHR									as "EhrRusMdr308"
										,	RRB.CurrVersion							as "EhrRusMdr308Ver"
										) as "PHARMACY"
									)
								)
								from APS.T_PHARMACY p
									left join APS.T_RAJON r on r.RajonID = p.Rajon
									left join EHR.T_EHR_RUS_MDR308 e on e.EhrRusID = p.EhrRusMdr308
									join EHR.T_EHR_RUS_REF_BOOKS RRB ON RRB.EhrRusRefBooksID = e.EhrRusRefBooks
							) 
						)
					)
				);
			ELSIF _OperationType <> _DELETE
			then		
				-- собираем PHARMACY
				select 
				xmlelement
				(
					name "PHARMACY"
				,	xmlforest
					(
						xmlforest
						(
							_OperationType as "TYPE"
						,	"ID" as "ID"
						) as "header"
					)
				,	xmlforest
					(
						xmlforest
						(
							p.Name                                 	as "Name"
						,	p.NameFull                             	as "NameFull"
						,	p.OGRN                                 	as "OGRN"
						,	p.Terr                                 	as "Terr"
						,	p.OKATO                                	as "OKATO"
						,	to_char(p.DateBegin, 'DD.MM.YYYY') 		as "DateBegin"
						,	to_char(p.DateEnd, 'DD.MM.YYYY')   		as "DateEnd"
						,	to_char("DateTime", 'DD.MM.YYYY')   	as "RegisterDate"
						,	p.RecipeServedTypes                    	as "RecipeServedTypes"
						,	p.Address								as "Address"
						,	r.Code									as "Rajon"
						,	p.Number								as "Number"
						,	p.Phone									as "Phone"
						,	p.Dlo									as "Dlo"
						,	p.LicenseNumber							as "LicenseNumber"
						,	p.LicenseIssue							as "LicenseIssue"
						,	p.LicenseDate							as "LicenseDate"
						,	p.FiasAddress							as "FiasAddress"
						,	p.PostalCode							as "PostalCode"
						,	p.FiasHome								as "FiasHome"
						,	e.IdEHR									as "EhrRusMdr308"
						,	RRB.CurrVersion							as "EhrRusMdr308Ver"
						) as "body"
					)
				)
				from APS.T_PHARMACY p
					left join APS.T_RAJON r on r.RajonID = p.Rajon
					left join EHR.T_EHR_RUS_MDR308 e on e.EhrRusID = p.EhrRusMdr308
					join EHR.T_EHR_RUS_REF_BOOKS RRB ON RRB.EhrRusRefBooksID = e.EhrRusRefBooks
				into _Result
				where p.Code = "ID";					
			END if;
			-- Отправка
			INSERT INTO APS.T_RABBIT_SEND_OBJECT(RabbitSendObjectID, QueueName, Content, MessageID, Headers, ContentEncoding, Type, UserID, State, CreateDateTime, OwnerID)
			select uuid_generate_v1(), "QueueName", ConvertTextToBinaryZip(_Result, ''), null, null, _ContentEncoding, _Type, "User", 0, "DateTime", _OwnerID;
		ELSE 
			IF "Xml" is not null
			then 
				-- Получаем тип операции
				_OperationType := nullif(trim(xpath('/PHARMACY/header[1]/TYPE[1]/text()',"Xml")::text,'{}'),'')::varchar(36);
				-- Generate table Tmp_PHARMACYS
				drop table if exists Tmp_PHARMACYS;
				create temp table Tmp_PHARMACYS 
				as
				select	
					nullif(trim(xpath('/PHARMACY/ID[1]/text()',x)::text,'{}'),'')::int as ID
				,	nullif(trim(xpath('/PHARMACY/Name[1]/text()',x)::text,'{}'),'')::varchar(255) as Name
				,	nullif(trim(xpath('/PHARMACY/NameFull[1]/text()',x)::text,'{}'),'')::varchar(255) as NameFull
				,	nullif(trim(xpath('/PHARMACY/OGRN[1]/text()',x)::text,'{}'),'')::varchar(255) as OGRN
				,	nullif(trim(xpath('/PHARMACY/Terr[1]/text()',x)::text,'{}'),'')::varchar(255) as Terr
				,	nullif(trim(xpath('/PHARMACY/OKATO[1]/text()',x)::text,'{}'),'')::varchar(255) as OKATO
				,	to_timestamp(nullif(trim(xpath('/PHARMACY/DateBegin[1]/text()',x)::text,'{}'),''), 'DD.MM.YYYY') as DateBegin
				,	to_timestamp(nullif(trim(xpath('/PHARMACY/DateEnd[1]/text()',x)::text,'{}'),''), 'DD.MM.YYYY') as DateEnd
				,	to_timestamp(nullif(trim(xpath('/PHARMACY/RegisterDate[1]/text()',x)::text,'{}'),''), 'DD.MM.YYYY') as RegisterDate
				,	nullif(trim(xpath('/PHARMACY/RecipeServedTypes[1]/text()',x)::text,'{}'),'')::int as RecipeServedTypes
				,	nullif(trim(xpath('/PHARMACY/Address[1]/text()',x)::text,'{}'),'')::varchar(255) as Address
				,	nullif(trim(xpath('/PHARMACY/Rajon[1]/text()',x)::text,'{}'),'')::varchar(13) as Rajon
				,	nullif(trim(xpath('/PHARMACY/Number[1]/text()',x)::text,'{}'),'')::varchar(50) as Number
				,	nullif(trim(xpath('/PHARMACY/Phone[1]/text()',x)::text,'{}'),'')::varchar(40) as Phone
				,	nullif(trim(xpath('/PHARMACY/Dlo[1]/text()',x)::text,'{}'),'')::int as Dlo
				,	nullif(trim(xpath('/PHARMACY/LicenseNumber[1]/text()',x)::text,'{}'),'')::varchar(255) as LicenseNumber
				,	nullif(trim(xpath('/PHARMACY/LicenseIssue[1]/text()',x)::text,'{}'),'')::varchar(255) as LicenseIssue
				,	to_timestamp(nullif(trim(xpath('/PHARMACY/LicenseDate[1]/text()',x)::text,'{}'),''), 'DD.MM.YYYY') as LicenseDate
				,	nullif(trim(xpath('/PHARMACY/FiasAddress[1]/text()',x)::text,'{}'),'')::varchar(255) as FiasAddress
				,	nullif(trim(xpath('/PHARMACY/PostalCode[1]/text()',x)::text,'{}'),'')::varchar(255) as PostalCode
				,	nullif(trim(xpath('/PHARMACY/FiasHome[1]/text()',x)::text,'{}'),'')::varchar(255) as FiasHome
				,	nullif(trim(xpath('/PHARMACY/EhrRusMdr308Ver[1]/text()',x)::text,'{}'),'')::varchar(8) as EhrRusMdr308Ver
				,	nullif(trim(xpath('/PHARMACY/EhrRusMdr308[1]/text()',x)::text,'{}'),'')::int as EhrRusMdr308
				from
				(
					select	unnest(xpath('/PHARMACY/body/PHARMACYS/PHARMACY', x)) as x
					from 
					(
						select "Xml" as x
					) root
				) PHARMACY
				where _OperationType = _FTLOAD;
				-- Generate table Tmp_PHARMACY
				drop table if exists Tmp_PHARMACY;
				create temp table Tmp_PHARMACY 
				as 
				select	nullif(trim(xpath('/PHARMACY/header[1]/ID[1]/text()',"Xml")::text,'{}'),'')::int as ID
				,	nullif(trim(xpath('/PHARMACY/body[1]/Name[1]/text()',"Xml")::text,'{}'),'')::varchar(255) as Name
				,	nullif(trim(xpath('/PHARMACY/body[1]/NameFull[1]/text()',"Xml")::text,'{}'),'')::varchar(255) as NameFull
				,	nullif(trim(xpath('/PHARMACY/body[1]/OGRN[1]/text()',"Xml")::text,'{}'),'')::varchar(255) as OGRN
				,	nullif(trim(xpath('/PHARMACY/body[1]/Terr[1]/text()',"Xml")::text,'{}'),'')::varchar(255) as Terr
				,	nullif(trim(xpath('/PHARMACY/body[1]/OKATO[1]/text()',"Xml")::text,'{}'),'')::varchar(255) as OKATO
				,	to_timestamp(nullif(trim(xpath('/PHARMACY/body[1]/DateBegin[1]/text()',"Xml")::text,'{}'),''), 'DD.MM.YYYY') as DateBegin
				,	to_timestamp(nullif(trim(xpath('/PHARMACY/body[1]/DateEnd[1]/text()',"Xml")::text,'{}'),''), 'DD.MM.YYYY') as DateEnd
				,	to_timestamp(nullif(trim(xpath('/PHARMACY/body[1]/RegisterDate[1]/text()',"Xml")::text,'{}'),''), 'DD.MM.YYYY') as RegisterDate
				,	nullif(trim(xpath('/PHARMACY/body[1]/RecipeServedTypes[1]/text()',"Xml")::text,'{}'),'')::int as RecipeServedTypes
				,	nullif(trim(xpath('/PHARMACY/body[1]/Address[1]/text()',"Xml")::text,'{}'),'')::varchar(255) as Address
				,	nullif(trim(xpath('/PHARMACY/body[1]/Rajon[1]/text()',"Xml")::text,'{}'),'')::varchar(13) as Rajon
				,	nullif(trim(xpath('/PHARMACY/body[1]/Number[1]/text()',"Xml")::text,'{}'),'')::varchar(50) as Number
				,	nullif(trim(xpath('/PHARMACY/Phone[1]/text()',"Xml")::text,'{}'),'')::varchar(40) as Phone
				,	nullif(trim(xpath('/PHARMACY/header[1]/Dlo[1]/text()',"Xml")::text,'{}'),'')::int as Dlo
				,	nullif(trim(xpath('/PHARMACY/body[1]/LicenseNumber[1]/text()',x)::text,'{}'),'')::varchar(255) as LicenseNumber
				,	nullif(trim(xpath('/PHARMACY/body[1]/LicenseIssue[1]/text()',x)::text,'{}'),'')::varchar(255) as LicenseIssue
				,	to_timestamp(nullif(trim(xpath('/PHARMACY/body[1]/LicenseDate[1]/text()',x)::text,'{}'),''), 'DD.MM.YYYY') as LicenseDate
				,	nullif(trim(xpath('/PHARMACY/body[1]/FiasAddress[1]/text()',x)::text,'{}'),'')::varchar(255) as FiasAddress
				,	nullif(trim(xpath('/PHARMACY/body[1]/PostalCode[1]/text()',x)::text,'{}'),'')::varchar(255) as PostalCode
				,	nullif(trim(xpath('/PHARMACY/body[1]/FiasHome[1]/text()',x)::text,'{}'),'')::varchar(255) as FiasHome
				,	nullif(trim(xpath('/PHARMACY/body[1]/EhrRusMdr308Ver[1]/text()',x)::text,'{}'),'')::varchar(8) as EhrRusMdr308Ver
				,	nullif(trim(xpath('/PHARMACY/body[1]/EhrRusMdr308[1]/text()',x)::text,'{}'),'')::int as EhrRusMdr308
				where _OperationType <> _FTLOAD;
				-- Обработка операции
				if _OperationType = _FTLOAD
				then		
					-- Первичная загрузка
					-- Аптеки не из справочника RDS переводим в неактивные
					update APS.T_PHARMACY p
					set	DateEnd = "DateTime"
					,	RegisterDate = "DateTime"
					where not exists
					(
						select *
						from Tmp_PHARMACYS np
						where np.ID = p.Code
					);
					-- Обновляем имеющиеся Аптеки
					update APS.T_PHARMACY p
					set	Name = np.Name
					,	NameFull = np.NameFull
					,	OGRN = np.OGRN
					,	Terr = np.Terr
					,	OKATO = np.OKATO
					,	DateBegin = np.DateBegin
					,	DateEnd = np.DateEnd
					,	RegisterDate = np.RegisterDate
					,	RecipeServedTypes = np.RecipeServedTypes
					,	Address = np.Address
				   ,	Rajon = (select r.RajonID from APS.T_RAJON r where r.Code = np.Rajon limit 1 )
				   ,	Number = np.Number
				   ,	Phone = np.Phone
				   ,	Dlo = Coalesce(np.Dlo,0)
					,	LicenseNumber = np.LicenseNumber
					,	LicenseIssue = np.LicenseIssue
					,	LicenseDate = np.LicenseDate
					,	FiasAddress = np.FiasAddress
					,	PostalCode = np.PostalCode
					,	FiasHome = np.FiasHome
					,	EhrRusMdr308 = e.EhrRusID
					from Tmp_PHARMACYS np 
					left join lateral (
						select e.EhrRusID 
						from EHR.T_EHR_RUS_MDR308 e
							LEFT JOIN EHR.T_EHR_RUS_REF_BOOKS RRB ON RRB.EhrRusRefBooksID = e.EhrRusRefBooks
						where e.IdEHR = np.EhrRusMdr308 and RRB.CURRVERSION = np.EhrRusMdr308Ver
					) e on true
					where np.ID = p.Code;

					-- Загружаем новые
					INSERT INTO APS.T_PHARMACY(Code, Name, NameFull, OGRN, Terr, OKATO, DateBegin, DateEnd, RegisterDate, RecipeServedTypes, Address,Rajon,Number,Phone,Dlo, LicenseNumber, LicenseIssue, LicenseDate, FiasAddress, PostalCode, FiasHome, EhrRusMdr308)
					SELECT np.ID, np.Name, np.NameFull, np.OGRN, np.Terr, np.OKATO, np.DateBegin, np.DateEnd, np.RegisterDate, np.RecipeServedTypes, np.Address
						   ,(select r.RajonID from APS.T_RAJON r where r.Code = np.Rajon limit 1 )--<Rajon, varchar(255),>
						   ,np.Number--<Number, varchar(50),>
						   ,Phone
						   ,Coalesce(np.Dlo,0)
						   ,np.LicenseNumber
						   ,np.LicenseIssue
						   ,np.LicenseDate
						   ,np.FiasAddress
						   ,np.PostalCode
						   ,np.FiasHome
						   ,e.EhrRusID
					from Tmp_PHARMACYS np
					left join lateral (
						select e.EhrRusID 
						from EHR.T_EHR_RUS_MDR308 e
							LEFT JOIN EHR.T_EHR_RUS_REF_BOOKS RRB ON RRB.EhrRusRefBooksID = e.EhrRusRefBooks
						where e.IdEHR = np.EhrRusMdr308 and RRB.CURRVERSION = np.EhrRusMdr308Ver
					) e on true
					where not exists
					(
						select *
						from APS.T_PHARMACY p
						where p.Code = np.ID
					);
				elsif _OperationType = _DELETE
				then
					-- Удаление
					update APS.T_PHARMACY p
					set	DateEnd = "DateTime"
					,	RegisterDate = "DateTime"
					from Tmp_PHARMACY np 
					where np.ID = p.Code;
				else
					-- update APS.T_PHARMACY
					update APS.T_PHARMACY p
					set	Name = np.Name
					,	NameFull = np.NameFull
					,	OGRN = np.OGRN
					,	Terr = np.Terr
					,	OKATO = np.OKATO
					,	DateBegin = np.DateBegin
					,	DateEnd = np.DateEnd
					,	RegisterDate = np.RegisterDate
					,	RecipeServedTypes = np.RecipeServedTypes
					,	Address = np.Address
				   ,	Rajon = (select r.RajonID from APS.T_RAJON r where r.Code = np.Rajon limit 1 )
				   ,	Number = np.Number
				   ,	Phone = np.Phone
				   ,    Dlo = Coalesce(np.Dlo,0)
					,	LicenseNumber = np.LicenseNumber
					,	LicenseIssue = np.LicenseIssue
					,	LicenseDate = np.LicenseDate
					,	FiasAddress = np.FiasAddress
					,	PostalCode = np.PostalCode
					,	FiasHome = np.FiasHome
					,	EhrRusMdr308 = e.EhrRusID
					from Tmp_PHARMACY np 
					left join lateral (
						select e.EhrRusID 
						from EHR.T_EHR_RUS_MDR308 e
							LEFT JOIN EHR.T_EHR_RUS_REF_BOOKS RRB ON RRB.EhrRusRefBooksID = e.EhrRusRefBooks
						where e.IdEHR = np.EhrRusMdr308 and RRB.CURRVERSION = np.EhrRusMdr308Ver
					) e on true
					where np.ID = p.Code
					and _OperationType = _UPDATE;

					-- insert APS.T_PHARMACY
					INSERT INTO APS.T_PHARMACY(Code, Name, NameFull, OGRN, Terr, OKATO, DateBegin, DateEnd, RegisterDate, RecipeServedTypes, Address,Rajon,Number,Phone, Dlo, LicenseNumber, LicenseIssue, LicenseDate, FiasAddress, PostalCode, FiasHome, EhrRusMdr308)
					SELECT np.ID, np.Name, np.NameFull, np.OGRN, np.Terr, np.OKATO, np.DateBegin, np.DateEnd, np.RegisterDate, np.RecipeServedTypes, np.Address
						   ,(select r.RajonID from APS.T_RAJON r where r.Code = np.Rajon limit 1 )--<Rajon, varchar(255),>
						   ,np.Number--<Number, varchar(50),>
						   ,Phone
						   ,Coalesce(np.Dlo,0)
						   ,np.LicenseNumber
						   ,np.LicenseIssue
						   ,np.LicenseDate
						   ,np.FiasAddress
						   ,np.PostalCode
						   ,np.FiasHome
						   ,e.EhrRusID
					from Tmp_PHARMACY np
					left join lateral (
						select e.EhrRusID 
						from EHR.T_EHR_RUS_MDR308 e
							LEFT JOIN EHR.T_EHR_RUS_REF_BOOKS RRB ON RRB.EhrRusRefBooksID = e.EhrRusRefBooks
						where e.IdEHR = np.EhrRusMdr308 and RRB.CURRVERSION = np.EhrRusMdr308Ver
					) e on true
					where not exists
					(
						select *
						from APS.T_PHARMACY p
						where p.Code = np.ID
					) and _OperationType = _INSERT;
				END if;
				

				-- генерируем след. номер
				--call dbo.sp_GenerateIdForEntity 'PHARMACY'
				update  T_ENTITY_IDS ei
				set id = (select MAX(Code) from APS.T_PHARMACY) + 1
				where ei.EntName = 'PHARMACY' and ei.FieldName = 'PrimaryKey';

				-- Drop tables
				DROP TABLE if exists Tmp_PHARMACY;
				DROP TABLE if exists Tmp_PHARMACYS;
				end if;
			END if;
	End;
End;
$$;