--DROP FUNCTION sp_getsetrequestcatalog(xml,uuid,boolean,text,timestamp without time zone,uuid)
CREATE OR REPLACE FUNCTION sp_getsetrequestcatalog
(
	"Xml" xml DEFAULT NULL::xml
,	"ID" uuid DEFAULT NULL::uuid
,	"IsNew" boolean DEFAULT '1'::boolean
,	"QueueName" text DEFAULT 'RDSSync'::text
,	"dateTime" timestamp DEFAULT NULL::timestamp
,	"User" uuid DEFAULT NULL::uuid
)
RETURNS void AS
$BODY$
Declare	_OperationType varchar(255);
	_FTLOAD varchar(6);
Begin
	_FTLOAD := 'FTLoad';

	IF "Xml" is not null
	then
		-- Получаем тип операции
		_OperationType := (xpath('/DRUG_APPLICATION_LIST/header[1]/TYPE[1]/text()',"Xml"))[1]::varchar(36);

		-- Generate table #DRUG_APPLICATION_LIST_ITEM
		Drop Table If Exists Temp_DRUG_APPLICATION_LIST_ITEM;
		Create Temp Table Temp_DRUG_APPLICATION_LIST_ITEM
		as 
		SELECT	
			(xpath('/DRUG_APPLICATION_LIST_ITEM/ID[1]/text()', x))[1]::text::UUID as ID
		,	to_timestamp((xpath('/DRUG_APPLICATION_LIST_ITEM/BeginDate[1]/text()', x))[1]::text, 'DD.MM.YYYY') as BeginDate
		,	to_timestamp((xpath('/DRUG_APPLICATION_LIST_ITEM/EndDate[1]/text()', x))[1]::text, 'DD.MM.YYYY') as EndDate
		,	(xpath('/DRUG_APPLICATION_LIST_ITEM/Price[1]/text()', x))[1]::text::decimal(31,10) as Price
		,	(xpath('/DRUG_APPLICATION_LIST_ITEM/RequestCode[1]/text()', x))[1]::text::int as RequestCode
		,	(xpath('/DRUG_APPLICATION_LIST_ITEM/CMnnID[1]/text()', x))[1]::text::int as CMnnID
		,	(xpath('/DRUG_APPLICATION_LIST_ITEM/CTrnID[1]/text()', x))[1]::text::int as CTrnID
		,	(xpath('/DRUG_APPLICATION_LIST_ITEM/GroupsOfDrugsClassification[1]/text()', x))[1]::text::int as GroupsOfDrugsClassification
		,	(xpath('/DRUG_APPLICATION_LIST_ITEM/BenefitType[1]/text()', x))[1]::text::int as BenefitType
		,	(xpath('/DRUG_APPLICATION_LIST_ITEM/DoseView[1]/text()', x))[1]::varchar(255) as DoseView
		,	(xpath('/DRUG_APPLICATION_LIST_ITEM/MedicationForms[1]/text()', x))[1]::text::int as MedicationForms
		,	(xpath('/DRUG_APPLICATION_LIST_ITEM/MeasureUnit[1]/text()', x))[1]::text::int as MeasureUnit
		,	(xpath('/DRUG_APPLICATION_LIST_ITEM/AdditionalMeasureUnit[1]/text()', x))[1]::text::int as AdditionalMeasureUnit
		,	(xpath('/DRUG_APPLICATION_LIST_ITEM/Concentration[1]/text()', x))[1]::text::decimal(31,10) as Concentration
		,	(xpath('/DRUG_APPLICATION_LIST_ITEM/PackQuantity[1]/text()', x))[1]::text::int as PackQuantity		
		,	(xpath('/DRUG_APPLICATION_LIST_ITEM/AdditionalConcentration[1]/text()', x))[1]::text::decimal(31,10) as AdditionalConcentration
		,	(xpath('/DRUG_APPLICATION_LIST_ITEM/IsActive[1]/text()', x))[1]::text::boolean as IsActive
		,	(xpath('/DRUG_APPLICATION_LIST_ITEM/SectionCode[1]/text()', x))[1]::varchar(255) as SectionCode
		,	(xpath('/DRUG_APPLICATION_LIST_ITEM/Comment[1]/text()', x))[1]::varchar(255) as Comment
		,	(xpath('/DRUG_APPLICATION_LIST_ITEM/DoseCount[1]/text()', x))[1]::text::decimal(5,2) as DoseCount
		,	(xpath('/DRUG_APPLICATION_LIST_ITEM/PersRequest[1]/text()', x))[1]::text::boolean as PersRequest
		,	(xpath('/DRUG_APPLICATION_LIST_ITEM/EhrRus9a9g09e8kl[1]/text()', x))[1]::varchar(52) as EhrRus9a9g09e8kl
		,	(xpath('/DRUG_APPLICATION_LIST_ITEM/EhrRus9a9g09e8klVer[1]/text()', x))[1]::varchar(8) as EhrRus9a9g09e8klVer
		,	(xpath('/DRUG_APPLICATION_LIST_ITEM/EhrRus6kiqts8t99[1]/text()', x))[1]::text::int as EhrRus6kiqts8t99
		,	(xpath('/DRUG_APPLICATION_LIST_ITEM/EhrRus6kiqts8t99Ver[1]/text()', x))[1]::varchar(8) as EhrRus6kiqts8t99Ver
		,	(xpath('/DRUG_APPLICATION_LIST_ITEM/EhrRusH17F8UTS3N[1]/text()', x))[1]::text::int as EhrRusH17F8UTS3N
		,	(xpath('/DRUG_APPLICATION_LIST_ITEM/EhrRusH17F8UTS3NVer[1]/text()', x))[1]::varchar(8) as EhrRusH17F8UTS3NVer
		from
		(
			select	unnest(xpath('/DRUG_APPLICATION_LIST/body/DRUG_APPLICATION_LIST_ITEMS/DRUG_APPLICATION_LIST_ITEM', x)) as x
			from 
			(
				select "Xml" as x
			) root
		) DRUG_APPLICATION_LIST_ITEM
		where _OperationType = _FTLOAD;

		-- Загружаем новые DoseView
		INSERT INTO akuz.S_DLO_DoseView(Value)
		SELECT trim(dli.DoseView)
		FROM Temp_DRUG_APPLICATION_LIST_ITEM dli
		where not exists
		(
			select* from akuz.S_DLO_DoseView dv where trim(dv.Value) = trim(dli.DoseView)
		)
		group by trim(dli.DoseView);
			
		-- Обновляем имеющиеся BASE_DRUG_REQUEST_ITEM
		update akuz.T_BASE_DRUG_REQUEST_ITEM bdri
		set	BeginDate = dli.BeginDate
		,	Price = dli.Price
		,	RequestCode = dli.RequestCode
		,	AppListBeginDate = dli.BeginDate
		,	AppListEndDate = dli.EndDate
		,	MedicationForms = mf.MedicationFormsID
		,	PersRequest = coalesce(dli.PersRequest, '0')
		from Temp_DRUG_APPLICATION_LIST_ITEM dli 
		left join lateral
		(
			select* from PHARM.T_MEDICATION_FORMS m where m.Code = dli.MedicationForms and m.Active = '1' limit 1
		) mf on true
		where  dli.ID = bdri.BaseDrugRequestItemID
		and bdri.type_ = 1233;
		
		-- Обновляем имеющиеся REQUEST_CATALOG 
		update akuz.T_REQUEST_CATALOG rc
		set	CreateDate = dli.BeginDate
		,	DoseView = dv.Id
		,	GroupsOfDrugsClassification = dli.GroupsOfDrugsClassification
		,	CMnn = mnn.CMnnID
		,	CTrn = trn.CTrnID
		,	BenefitType = eBt.EnumID
		,  	MeasureUnit = dvv.Code
		,	AdditionalMeasureUnit = dvva.Code
		,	Concentration = dli.Concentration
		,	PackQuantity = dli.PackQuantity
		,	AdditionalConcentration = dli.AdditionalConcentration
		,	IsActive = coalesce(dli.IsActive, '1')
		,	Comment = dli.Comment
		,	SectionCode = dli.SectionCode
		,	EhrRus9a9g09e8kl = ehr.EhrRusID
		,	EhrRus6kiqts8t99 = ehr1.EhrRusID
		,	EhrRusH17F8UTS3N = ehr2.EhrRusID
		from Temp_DRUG_APPLICATION_LIST_ITEM dli 
		left join 
			VCLib.T_ENUMS eBt 
			on eBt.Code = dli.BenefitType
			and eBt.EnumTypeID = 242
		left join lateral
		(
			select sd.Id from AKUZ.S_DLO_DoseView sd where TRIM(sd.Value) = TRIM(dli.DoseView) limit 1
		) dv on true
		left join lateral
		(
			select m.CMnnID from PHARM.T_C_MNN m where m.OtherID = dli.CMnnID and m.Active = '1' limit 1
		) mnn on true
		left join lateral
		(
			select t.CTrnID from PHARM.T_C_TRN t where t.OtherID = dli.CTrnID and t.Active = '1' limit 1
		) trn on true
		left join lateral
		(
			select d.Code from PHARM.T_DOSE_VIEW d where d.OtherID = dli.MeasureUnit limit 1
		) dvv on true
		left join lateral
		(
			select d.Code from PHARM.T_DOSE_VIEW d where d.OtherID = dli.AdditionalMeasureUnit limit 1
		) dvva on true
		left join lateral
		(
			select er.EhrRusID 
			from EHR.T_EHR_RUS_9A9G09E8KL prep
			left join 
				EHR.T_EHR_RUS er 
				on er.EhrRusID = prep.EhrRusID
			left join
				EHR.T_EHR_RUS_REF_BOOKS RRB 
				on RRB.EhrRusRefBooksID = er.EhrRusRefBooks
			where er.CodeEHR = dli.EhrRus9a9g09e8kl 
			and rrb.CurrVersion = dli.EhrRus9a9g09e8klVer
			limit 1
		) ehr on true
		left join lateral
		(
			select er.EhrRusID 
			from EHR.T_EHR_RUS_6KIQTS8T99 prep 
			left join 
				EHR.T_EHR_RUS er 
				on er.EhrRusID = prep.EhrRusID
			left join
				EHR.T_EHR_RUS_REF_BOOKS RRB 
				on RRB.EhrRusRefBooksID = er.EhrRusRefBooks
			where er.IdEHR = dli.EhrRus6kiqts8t99 
			and rrb.CurrVersion = dli.EhrRus6kiqts8t99Ver
			limit 1
		) ehr1 on true
		left join lateral
		(
			select er.EhrRusID 
			from EHR.T_EHR_RUS_H17F8UTS3N prep 
			left join 
				EHR.T_EHR_RUS er 
				on er.EhrRusID = prep.EhrRusID
			left join
				EHR.T_EHR_RUS_REF_BOOKS RRB 
				on RRB.EhrRusRefBooksID = er.EhrRusRefBooks
			where er.IdEHR = dli.EhrRusH17F8UTS3N 
			and rrb.CurrVersion = dli.EhrRusH17F8UTS3NVer
			limit 1
		) ehr2 on true
		where dli.ID = rc.BaseDrugRequestItemID
		and dv.Id is not null;

		-- Загружаем новые BASE_DRUG_REQUEST_ITEM
		INSERT INTO akuz.T_BASE_DRUG_REQUEST_ITEM(BaseDrugRequestItemID, BeginDate, Price, MedicationForms, RequestCode, AppListBeginDate, AppListEndDate, type_, PersRequest)
		SELECT dli.ID, dli.BeginDate, dli.Price, mf.MedicationFormsID, dli.RequestCode, dli.BeginDate, dli.EndDate, 1233, coalesce(dli.PersRequest, '0')
		from Temp_DRUG_APPLICATION_LIST_ITEM dli
		left join
			PHARM.T_MEDICATION_FORMS mf 
			on mf.Code = dli.MedicationForms 
			and mf.Active = '1'
		where not exists
		(
			select *
			from akuz.T_BASE_DRUG_REQUEST_ITEM bdri
			where bdri.BaseDrugRequestItemID = dli.ID
		);
		
		-- Загружаем новые REQUEST_CATALOG
		INSERT INTO akuz.T_REQUEST_CATALOG(BaseDrugRequestItemID, CreateDate, DoseView, GroupsOfDrugsClassification, CMnn, CTrn, BenefitType, MeasureUnit, AdditionalMeasureUnit, Concentration, PackQuantity, AdditionalConcentration, IsActive, SectionCode, Comment, DoseCount, EhrRus9a9g09e8kl, EhrRus6kiqts8t99, EhrRusH17F8UTS3N)
		SELECT dli.ID, dli.BeginDate, dv.Id, dli.GroupsOfDrugsClassification, dli.CMnnID, dli.CTrnID, dli.BenefitType, dli.MeasureUnit, dli.AdditionalMeasureUnit, dli.Concentration, dli.PackQuantity, dli.AdditionalConcentration, coalesce(dli.IsActive, '1'), dli.SectionCode, dli.Comment, dli.DoseCount, ehr.EhrRusID, ehr1.EhrRusID, ehr2.EhrRusID
		from Temp_DRUG_APPLICATION_LIST_ITEM dli
		left join 
			VCLib.T_ENUMS eBt 
			on eBt.Code = dli.BenefitType
			and eBt.EnumTypeID = 242
		left join lateral
		(
			select sd.Id from AKUZ.S_DLO_DoseView sd where TRIM(sd.Value) = TRIM(dli.DoseView) limit 1
		) dv on true
		left join lateral
		(
			select m.CMnnID from PHARM.T_C_MNN m where m.OtherID = dli.CMnnID and m.Active = '1' limit 1
		) mnn on true
		left join lateral
		(
			select t.CTrnID from PHARM.T_C_TRN t where t.OtherID = dli.CTrnID and t.Active = '1' limit 1
		) trn on true
		left join lateral
		(
			select d.Code from PHARM.T_DOSE_VIEW d where d.OtherID = dli.MeasureUnit limit 1
		) dvv on true
		left join lateral
		(
			select d.Code from PHARM.T_DOSE_VIEW d where d.OtherID = dli.AdditionalMeasureUnit limit 1
		) dvva on true
		left join lateral
		(
		 	select er.EhrRusID 
			from EHR.T_EHR_RUS_9A9G09E8KL prep
		 	left join 
				EHR.T_EHR_RUS er 
				on er.EhrRusID = prep.EhrRusID
			left join
				EHR.T_EHR_RUS_REF_BOOKS RRB 
				on RRB.EhrRusRefBooksID = er.EhrRusRefBooks
		 	where er.CodeEHR = dli.EhrRus9a9g09e8kl 
			and RRB.CURRVERSION = dli.EhrRus9a9g09e8klVer
			limit 1
		 ) ehr on true
		 left join lateral
		 (
		 	select er.EhrRusID 
		 	from EHR.T_EHR_RUS_6KIQTS8T99 prep 
			left join 
			 	EHR.T_EHR_RUS er 
			 	on er.EhrRusID = prep.EhrRusID
			left join
			 	EHR.T_EHR_RUS_REF_BOOKS RRB 
			 	on RRB.EhrRusRefBooksID = er.EhrRusRefBooks
			where er.IdEHR = dli.EhrRus6kiqts8t99
			and RRB.CURRVERSION = dli.EhrRus6kiqts8t99Ver
			limit 1
		) ehr1 on true
		left join lateral
		 (
		 	select er.EhrRusID 
		 	from EHR.EHR_RUS_H17F8UTS3N prep 
			left join 
			 	EHR.T_EHR_RUS er 
			 	on er.EhrRusID = prep.EhrRusID
			left join
			 	EHR.T_EHR_RUS_REF_BOOKS RRB 
			 	on RRB.EhrRusRefBooksID = er.EhrRusRefBooks
			where er.IdEHR = dli.EhrRusH17F8UTS3N
			and RRB.CURRVERSION = dli.EhrRusH17F8UTS3NVer
			limit 1
		) ehr1 on true
		where not exists
		(
			select * from akuz.T_REQUEST_CATALOG rc where rc.BaseDrugRequestItemID = dli.ID
		)
		and dv.Id is not null;
		
		if _OperationType = _FTLOAD
		then
			-- Закрываем REQUEST_CATALOG которых нет
			update AKUZ.T_REQUEST_CATALOG rc
			set IsActive = '0'
			FROM AKUZ.T_BASE_DRUG_REQUEST_ITEM bdri
			WHERE bdri.BaseDrugRequestItemID = rc.BaseDrugRequestItemID
			and not exists
			(
				select* from Temp_DRUG_APPLICATION_LIST_ITEM as dli where dli.ID = bdri.BaseDrugRequestItemID
			);

			-- Закрываем BASE_DRUG_REQUEST_ITEM которых нет
			update AKUZ.T_BASE_DRUG_REQUEST_ITEM bdri
			set AppListEndDate = now()::timestamp
			where not exists
			(
				select* from Temp_DRUG_APPLICATION_LIST_ITEM as dli where dli.ID = bdri.BaseDrugRequestItemID
			);
		end if;

		DROP TABLE if exists Temp_DRUG_APPLICATION_LIST_ITEM;
	END if;
End;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;
ALTER FUNCTION sp_getsetrequestcatalog(xml, uuid, boolean, text, timestamp without time zone, uuid)
  OWNER TO postgres;
