DROP PROCEDURE IF EXISTS ehr.SP_EHR_REPLACEMENT_FILL;

CREATE PROCEDURE ehr.SP_EHR_REPLACEMENT_FILL
(
	"oldVersion" varchar(8)
,	"newVersion" varchar(8)
,	"code" varchar(64)
,	"parent" uuid
,	"Entity" varchar(100)
,	"Attr" varchar(100)
,	"OnlyUpdate" boolean
)
    language plpgsql
as
$$
#variable_conflict use_variable
Begin

	DECLARE 
		"OldEhrRusRefBooks" integer := (SELECT rb.EhrRusRefBooksID FROM EHR.T_EHR_RUS_REF_BOOKS rb WHERE rb.Code = "code" AND rb.CurrVersion = "oldVersion" limit 1);
		"NewEhrRusRefBooks" integer := (SELECT rb1.EhrRusRefBooksID FROM EHR.T_EHR_RUS_REF_BOOKS rb1 WHERE rb1.Code = "code" AND rb1.CurrVersion = "newVersion" limit 1);
		"EntityID" integer := (SELECT erfb.Entities FROM EHR.T_EHR_RUS_REF_BOOKS erfb WHERE erfb.EhrRusRefBooksID = "OldEhrRusRefBooks");
		"NameEHR" VARCHAR(132) := (SELECT e.Schema + '.T_' + e.EntName FROM VCLib.T_ENTITIES e WHERE e.EntityID = "EntityID");
		"conditionCode" VARCHAR(100);
		_tsql text;
BEGIN
	IF EXISTS (SELECT MappingName FROM VCLib.T_ENTITIES_ATTRIBUTES ea WHERE EntityID = "EntityID" And MappingName = 'IdEHR' limit 1) then
		"conditionCode" := 'e1.IdEHR = e2.IdEHR';
	ELSIF EXISTS (SELECT MappingName FROM VCLib.T_ENTITIES_ATTRIBUTES ea WHERE EntityID = "EntityID" And MappingName = 'CodeEHR' limit 1) then
		"conditionCode" := 'e1.CodeEHR = e2.CodeEHR';
	end if;
	
	
	if("OnlyUpdate" = 1)
	then
		_tsql := '
		select distinct(dd.' || "Attr" || ')
		into TEMPORARY OldData
		from ' || "Entity" || ' dd 
		join ' || "NameEHR" || ' er on er.EhrRusID = dd.' || "Attr" || '
		where er.EhrRusRefBooks = ''' || "OldEhrRusRefBooks"::varchar(50) || ''';
		
		INSERT INTO  EHR.T_ELEMENT_EHR_REPLACEMENT (ElementEhrReplacementID, EhrReplacement, OldEhr, NewEhr, ExistsData, AutomaticallyFilled)
		SELECT gen_random_uuid(),  ''' || "parent" ::varchar(36) || ''', e1.EhrRusID, e2.EhrRusID , case when old.' || "Attr" || ' is not null then true else false end, case when e2.EhrRusID is not null then true else false end
		FROM ' || "NameEHR" || ' e1
		LEFT JOIN ' || "NameEHR" || ' e2 ON ' || "conditionCode" || ' AND e2.EhrRusRefBooks = ''' || "NewEhrRusRefBooks"::varchar(50) || '''
		left join OldData old on old.' || "Attr" || ' = e1.EhrRusID
		WHERE e1.EhrRusRefBooks = ''' || "OldEhrRusRefBooks"::varchar(50) || ''' ;
		
		drop table OldData;';
	ELSE  
		_tsql := '
			select distinct(dd.' || "Attr" || ')
			into OldData
			from ' || "Entity" || ' dd 
			join ' || "NameEHR" || ' er on er.EhrRusID = dd.' || "Attr" || '
			where er.EhrRusRefBooks = ''' || "OldEhrRusRefBooks"::varchar(50) || ''';
			
			update EHR.T_ELEMENT_EHR_REPLACEMENT er
				set ExistsData = true
			from OldData old  
			where old.' || "Attr" || ' = er.OldEhr and er.ExistsData = ''' || 0 || ''' and er.EhrReplacement = ''' || "parent" ::varchar(36) || ''';
	
			drop table OldData ;';
	End if;

	--RAISE NOTICE '%', _tsql;
	EXECUTE _tsql;	
End;
End;
$$;
