DROP PROCEDURE IF EXISTS sp_getsetrequestcatalog;

CREATE PROCEDURE sp_getsetrequestcatalog
(
	"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
		_OperationType varchar(255);
	Begin
		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;
	
			-- Загружаем новые 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 PHARM.T_MEDICATION_FORMS mf on m.Code = dli.MedicationForms and mf.Active = '1'
			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 er
					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 er
						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 er
						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 er
						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 er 
						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 er
						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 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'
				and not exists
				(
					select* from Temp_DRUG_APPLICATION_LIST_ITEM as dli where dli.ID = rc.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;
End;
$$;;
