DROP PROCEDURE IF EXISTS ehr.sp_Process_SF8U7PKK0U;

CREATE PROCEDURE ehr.sp_Process_SF8U7PKK0U
(
    "Code" varchar(64),
    "Name" varchar(255),
    "CurrVersion" varchar(8),
	"OldVersion" varchar(8),
    "OID" varchar(54),
	"CheckMatching" boolean
)
    language plpgsql
as
$$
Begin

	declare 
		"entityID" integer := (select Entityid from VCLib.T_ENTITIES e where EntName = 'EHR_RUS_SF8U7PKK0U'); 
		"refBooks" integer := (select b.EhrRusRefBooksID from EHR.T_EHR_RUS_REF_BOOKS b where b.Entities = "entityID" and b.CurrVersion = "CurrVersion" limit 1);
		"ehrRusIDsCount" integer;
		maxEhrRusID integer;
	Begin
		if "OldVersion" is not null or "refBooks" is not null
		then
			if "refBooks" is null
			then
				"refBooks" := (select b.EhrRusRefBooksID from EHR.T_EHR_RUS_REF_BOOKS b where b.Entities = "entityID" and b.CurrVersion = "OldVersion" limit 1);
			end if;
		
			if "CheckMatching" = '1'
			then
				
				DROP TABLE IF EXISTS tempTbForEhrs;
				CREATE TEMP TABLE tempTbForEhrs(
				    Id1 integer
				  , Id2 integer
				);
		
				insert into tempTbForEhrs
				select er.IdEHR, tt."ID"
				from ehr.T_EHR_RUS_SF8U7PKK0U er 
					left join "SF8U7PKK0U" tt on er.IdEHR = tt."ID"
				where er.EhrRusRefBooks = "refBooks";
		
				if exists (select * from tempTbForEhrs where id2 is null)
				then
					RAISE EXCEPTION 'Невозможно обновить справочник, обнаружено несоответствие записей нового справочника с текущем. Скачайте новый справочник и обновите связи!';
				end if;

				DROP TABLE tempTbForEhrs;
			end if;
		
			if "refBooks" is null
			then
				CALL sp_GenerateIdForEntity('EHR.EHR_RUS_REF_BOOKS', 'PrimaryKey', 1,"refBooks");
		
				INSERT INTO EHR.T_EHR_RUS_REF_BOOKS
					(EhrRusRefBooksID,Code,Name,CurrVersion,UpdateDate,Entities,OID, Deleted)
				VALUES
					("refBooks", "Code", "Name", "CurrVersion", now(), "entityID", "OID", '0');
			else
				update EHR.T_EHR_RUS_REF_BOOKS
				set Code = "Code",
					Name= "Name",
					CurrVersion = "CurrVersion",
					UpdateDate = now(),
					Entities = "entityID",
					OID = "OID"
				where EhrRusRefBooksID = "refBooks";
			end if;
		
			UPDATE EHR.T_EHR_RUS_SF8U7PKK0U r2
			SET NameEHR = p."Label_RU"
			from "SF8U7PKK0U" p 
			where r2.IdEHR = p."ID" and r2.EhrRusRefBooks = "refBooks";
		
			UPDATE EHR.T_EHR_RUS_SF8U7PKK0U rb																					  
				SET 																						    
			       ID_RU					= p."ID_RU"
		           ,ID_Model				= p."ID_Model"
		           ,Status_RU				= p."Status_RU"
		           ,Status					= p."Status"
		           ,Label					= p."Label"
		           ,Dt_from					= p."Dt_from"
		           ,Dt_to					= p."Dt_to"
		           ,Registration_number		= p."Registration_number"
		           ,Registration_date		= p."Registration_date"
		           ,Registration_date_end	= p."Registration_date_end"
		           ,Producer				= p."Producer"
		           ,Producer_oksm_id		= p."Producer_oksm_id"
		           ,Producer_country		= p."Producer_country"
		           ,OKP						= p."OKP"
		           ,Class					= p."Class"
		           ,Kind					= p."Kind"
			 FROM "SF8U7PKK0U" p 
			WHERE rb.IdEHR = p."ID" and rb.EhrRusRefBooks = "refBooks";
		
			update p
			set EhrRusID = -1
			from "SF8U7PKK0U" p
			where not exists (select * from EHR.T_EHR_RUS_SF8U7PKK0U r2 where r2.IdEHR = p."ID" and r2.EhrRusRefBooks = "refBooks");
		
			"ehrRusIDsCount" := (SELECT COUNT(*) FROM "SF8U7PKK0U"  WHERE EhrRusID = -1);

			CALL sp_GenerateIdForEntity('EHR.EHR_RUS_SF8U7PKK0U', 'PrimaryKey', "ehrRusIDsCount",maxEhrRusID);
			maxEhrRusID := maxEhrRusID - 1;

			WITH CTE AS (
				SELECT
					EhrRusID,
					ROW_NUMBER() OVER (ORDER BY "ID") AS RowNum
				FROM "SF8U7PKK0U"
				WHERE EhrRusID = -1 
			)
			UPDATE "SF8U7PKK0U" d
			SET EhrRusID = c.RowNum + maxEhrRusID
			from CTE c 
			where c.EhrRusID = d.EhrRusID;
		
			INSERT INTO EHR.T_EHR_RUS_SF8U7PKK0U
			           (EhrRusRefBooks,
			            EhrRusID,
			            IdEHR,
			            NameEHR,
			            ID_RU,
		                ID_Model,
		                Status_RU,
		                Status,
		                Label,
		                Dt_from,
		                Dt_to,
		                Registration_number,
		                Registration_date,
		                Registration_date_end,
		                Producer,
		                Producer_oksm_id,
		                Producer_country,
		                OKP,
		                Class,
		                Kind)
			select 
						"refBooks"
						,p.EhrRusID
						,p."ID" --<IdEHR, int>
						,p."Label_RU" --<NameEHR, varchar(1024)>
				        ,p."ID_RU"
		                ,p."ID_Model"
		                ,p."Status_RU"
		                ,p."Status"
		                ,p."Label"
		                ,p."Dt_from"
		                ,p."Dt_to"
		                ,p."Registration_number"
		                ,p."Registration_date"
		                ,p."Registration_date_end"
		                ,p."Producer"
		                ,p."Producer_oksm_id"
		                ,p."Producer_country"
		                ,p."OKP"
		                ,p."Class"
		                ,p."Kind"
			from "SF8U7PKK0U" p
			where p.EhrRusID is not null;
		else
			CALL sp_GenerateIdForEntity('EHR.EHR_RUS_REF_BOOKS', 'PrimaryKey', 1,"refBooks");
		
			INSERT INTO EHR.T_EHR_RUS_REF_BOOKS
				(EhrRusRefBooksID,Code,Name,CurrVersion,UpdateDate,Entities,OID, Deleted)
			VALUES
				("refBooks", "Code", "Name", "CurrVersion", now(), "entityID", "OID", '0');
		
			"ehrRusIDsCount" := (SELECT COUNT(*) FROM "SF8U7PKK0U");
			
			CALL sp_GenerateIdForEntity('EHR.EHR_RUS_SF8U7PKK0U', 'PrimaryKey', "ehrRusIDsCount",maxEhrRusID);
			maxEhrRusID := maxEhrRusID - 1;
		
			WITH CTE AS (
				SELECT
					EhrRusID,
					ROW_NUMBER() OVER (ORDER BY "ID") AS RowNum
				FROM "SF8U7PKK0U"
			)
			UPDATE "SF8U7PKK0U" d
			SET EhrRusID = c.RowNum + maxEhrRusID
			from CTE c 
			where c.EhrRusID = d.EhrRusID;
		
			INSERT INTO EHR.T_EHR_RUS_SF8U7PKK0U
				       (EhrRusRefBooks,
					    EhrRusID,
			            IdEHR,
					    NameEHR,
			            ID_RU,
		                ID_Model,
		                Status_RU,
		                Status,
		                Label,
		                Dt_from,
		                Dt_to,
		                Registration_number,
		                Registration_date,
		                Registration_date_end,
		                Producer,
		                Producer_oksm_id,
		                Producer_country,
		                OKP,
		                Class,
		                Kind)  
			select 
						"refBooks"
						,p.EhrRusID
						,p."ID" --<IdEHR, int>
						,p."Label_RU" --<NameEHR, varchar(1024)>
						,p."ID_RU"
		                ,p."ID_Model"
		                ,p."Status_RU"
		                ,p."Status"
		                ,p."Label"
		                ,p."Dt_from"
		                ,p."Dt_to"
		                ,p."Registration_number"
		                ,p."Registration_date"
		                ,p."Registration_date_end"
		                ,p."Producer"
		                ,p."Producer_oksm_id"
		                ,p."Producer_country"
		                ,p."OKP"
		                ,p."Class"
		                ,p."Kind"
			from "SF8U7PKK0U" p
			where p.EhrRusID is not null;
		
			update ehr.T_EHR_REFERENCE
			set EhrRusRefBooks = "refBooks"
			where EhrEntity = "entityID" and EhrRusRefBooks is null;
		end if;

		drop table "SF8U7PKK0U";
	end;
End;
$$;