-- =============================================
-- Author:		Arseniy Gadelshin
-- Create date: 2021/07/15
-- Description:	Загрузка справочника ЕИП с кодом "2M69I03GTD"(Лекарственные препараты. Товарные позиции. ЕСКЛП с кодами КТРУ)
--              Версия справочника 8.8, 14.07.2021 19:16
-- =============================================

DROP PROCEDURE IF EXISTS ehr.sp_Process_2M69I03GTD;

CREATE PROCEDURE ehr.sp_Process_2M69I03GTD
(
    "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_2M69I03GTD'); 
		"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_2M69I03GTD er 
						left join "2M69I03GTD" 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_2M69I03GTD r2 
				SET NameEHR = p."NAME_TRADE"
				from "2M69I03GTD" p 
				where r2.IdEHR = p."ID" and r2.EhrRusRefBooks = "refBooks";

				UPDATE EHR.T_EHR_RUS_2M69I03GTD rb																					  
				   SET 												
				       KLP_CODE						 = p."KLP_CODE"
				      ,SMNN_CODE				     = p."SMNN_CODE"
				      ,KTRU_CODE		             = p."KTRU_CODE"
				      ,STANDARD_INN					 = p."STANDARD_INN"
				      ,STANDARD_FORM				 = p."STANDARD_FORM"
				      ,STANDARD_DOZE				 = p."STANDARD_DOZE"				  
				      ,NAME_PRODUCER				 = p."NAME_PRODUCER"
				      ,OKSM_CODE					 = p."OKSM_CODE"
				      ,COUNTRY			             = p."COUNTRY"			  
				      ,NUMBER_REGISTRATION			 = p."NUMBER_REGISTRATION"
				      ,DATE_REGISTRATION		     = p."DATE_REGISTRATION"
				      ,NAME_UNIT					 = p."NAME_UNIT"
				      ,OKEI_CODE		             = p."OKEI_CODE"
				      ,NORMALIZED_DOSAGE			 = p."NORMALIZED_DOSAGE"
				      ,NORMALIZED_FORM				 = p."NORMALIZED_FORM"
				      ,NAME_1_PACKING				 = p."NAME_1_PACKING"
				      ,NUMBER_UNITS_1				 = p."NUMBER_UNITS_1"
				      ,NAME_2_PACKAGE				 = p."NAME_2_PACKAGE"
				      ,NUMBER_PACKAGES			     = p."NUMBER_PACKAGES"
				      ,NUMBER_UNITS_2				 = p."NUMBER_UNITS_2"
				      ,OKPD_2_CODE					 = p."OKPD_2_CODE"
				      ,ESSENTIAL_MEDICINES			 = p."ESSENTIAL_MEDICINES"
				      ,NARCOTIC_PSYCHOTROPIC		 = p."NARCOTIC_PSYCHOTROPIC"
				      ,CODE_ATC					     = p."CODE_ATC"
				      ,NAME_ATC						 = p."NAME_ATC"
				      ,TN							 = p."TN"
				      ,COMPLETENESS					 = p."COMPLETENESS"
				FROM "2M69I03GTD" p
				WHERE rb.IdEHR = p."ID" and rb.EhrRusRefBooks = "refBooks";

				update "2M69I03GTD"
				set EhrRusID = -1
				where not exists (select * from EHR.T_EHR_RUS_2M69I03GTD r2 where r2.IdEHR = "ID" and r2.EhrRusRefBooks = "refBooks");

				"ehrRusIDsCount" := (SELECT COUNT(*) FROM "2M69I03GTD"  WHERE EhrRusID = -1);

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

				WITH CTE AS (
					SELECT
						EhrRusID,
						ROW_NUMBER() OVER (ORDER BY "ID") AS RowNum
					FROM "2M69I03GTD" 
					WHERE EhrRusID = -1 
				)
				UPDATE "2M69I03GTD" d
				SET EhrRusID = c.RowNum + maxEhrRusID
				from CTE c 
				where c.EhrRusID = d.EhrRusID;

				INSERT INTO EHR.T_EHR_RUS_2M69I03GTD
				           (EhrRusRefBooks
				           ,EhrRusID
				           ,IdEHR
				           ,NameEHR
				           ,KLP_CODE							  
				           ,SMNN_CODE						  
				           ,KTRU_CODE				  
				           ,STANDARD_INN				  
				           ,STANDARD_FORM		  
				           ,STANDARD_DOZE					  
				           ,NAME_PRODUCER				  
				           ,OKSM_CODE					  
				           ,COUNTRY				  
				           ,NUMBER_REGISTRATION		  
				           ,DATE_REGISTRATION			  
				           ,NAME_UNIT					  
				           ,OKEI_CODE		  
				           ,NORMALIZED_DOSAGE		  
				           ,NAME_1_PACKING		  
				           ,NUMBER_UNITS_1		  
				           ,NAME_2_PACKAGE		  
				           ,NUMBER_PACKAGES			  
				           ,NUMBER_UNITS_2				  
				           ,OKPD_2_CODE			  
				           ,ESSENTIAL_MEDICINES					  
				           ,NARCOTIC_PSYCHOTROPIC				  
				           ,CODE_ATC					  
				           ,NAME_ATC			  
				           ,TN			  
				           ,COMPLETENESS)		  
				select 
							"refBooks"
						   ,p.EhrRusID
						   ,p."ID"--<IdEHR, int>
						   ,p."NAME_TRADE" --<NameEHR, varchar(1024),>
				           ,p."KLP_CODE"							  
				           ,p."SMNN_CODE"						  
				           ,p."KTRU_CODE"				  
				           ,p."STANDARD_INN"				  
				           ,p."STANDARD_FORM"		  
					       ,p."STANDARD_DOZE"					  
					       ,p."NAME_PRODUCER"				  
					       ,p."OKSM_CODE"					  
					       ,p."COUNTRY"				  
					       ,p."NUMBER_REGISTRATION"		  
					       ,p."DATE_REGISTRATION"			  
					       ,p."NAME_UNIT"					  
					       ,p."OKEI_CODE"		  
					       ,p."NORMALIZED_DOSAGE"		  
					       ,p."NAME_1_PACKING"		  
					       ,p."NUMBER_UNITS_1"		  
					       ,p."NAME_2_PACKAGE"		  
					       ,p."NUMBER_PACKAGES"			  
					       ,p."NUMBER_UNITS_2"				  
					       ,p."OKPD_2_CODE"			  
					       ,p."ESSENTIAL_MEDICINES"					  
					       ,p."NARCOTIC_PSYCHOTROPIC"				  
					       ,p."CODE_ATC"					  
					       ,p."NAME_ATC"			  
					       ,p."TN"			  
					       ,p."COMPLETENESS"	
				from "2M69I03GTD" 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 "2M69I03GTD");
				
				CALL sp_GenerateIdForEntity('EHR.EHR_RUS_2M69I03GTD', 'PrimaryKey', "ehrRusIDsCount",maxEhrRusID);
				maxEhrRusID := maxEhrRusID - 1;

				WITH CTE AS (
					SELECT
						EhrRusID,
						ROW_NUMBER() OVER (ORDER BY "ID") AS RowNum
					FROM "2M69I03GTD" 
				)
				UPDATE "2M69I03GTD" d
				SET EhrRusID = c.RowNum + maxEhrRusID
				from CTE c 
				where c.EhrRusID = d.EhrRusID;

				INSERT INTO EHR.T_EHR_RUS_2M69I03GTD
				           (EhrRusRefBooks
				           ,EhrRusID
				           ,IdEHR
				           ,NameEHR
				           ,KLP_CODE							  
				           ,SMNN_CODE						  
				           ,KTRU_CODE				  
				           ,STANDARD_INN				  
				           ,STANDARD_FORM		  
				           ,STANDARD_DOZE					  
				           ,NAME_PRODUCER				  
				           ,OKSM_CODE					  
				           ,COUNTRY				  
				           ,NUMBER_REGISTRATION		  
				           ,DATE_REGISTRATION			  
				           ,NAME_UNIT					  
				           ,OKEI_CODE		  
				           ,NORMALIZED_DOSAGE		  
				           ,NAME_1_PACKING		  
				           ,NUMBER_UNITS_1		  
				           ,NAME_2_PACKAGE		  
				           ,NUMBER_PACKAGES			  
				           ,NUMBER_UNITS_2				  
				           ,OKPD_2_CODE			  
				           ,ESSENTIAL_MEDICINES					  
				           ,NARCOTIC_PSYCHOTROPIC				  
				           ,CODE_ATC					  
				           ,NAME_ATC			  
				           ,TN			  
				           ,COMPLETENESS)		  
				select 
						   "refBooks"
						   ,p.EhrRusID
						   ,p."ID"--<IdEHR, int>
						   ,p."NAME_TRADE" --<NameEHR, varchar(1024),>
				           ,p."KLP_CODE"							  
				           ,p."SMNN_CODE"						  
				           ,p."KTRU_CODE"				  
				           ,p."STANDARD_INN"				  
				           ,p."STANDARD_FORM"		  
					       ,p."STANDARD_DOZE"					  
					       ,p."NAME_PRODUCER"				  
					       ,p."OKSM_CODE"					  
					       ,p."COUNTRY"				  
					       ,p."NUMBER_REGISTRATION"		  
					       ,p."DATE_REGISTRATION"			  
					       ,p."NAME_UNIT"					  
					       ,p."OKEI_CODE"		  
					       ,p."NORMALIZED_DOSAGE"		  
					       ,p."NAME_1_PACKING"		  
					       ,p."NUMBER_UNITS_1"		  
					       ,p."NAME_2_PACKAGE"		  
					       ,p."NUMBER_PACKAGES"			  
					       ,p."NUMBER_UNITS_2"				  
					       ,p."OKPD_2_CODE"			  
					       ,p."ESSENTIAL_MEDICINES"					  
					       ,p."NARCOTIC_PSYCHOTROPIC"				  
					       ,p."CODE_ATC"					  
					       ,p."NAME_ATC"			  
					       ,p."TN"			  
					       ,p."COMPLETENESS"	
				from "2M69I03GTD" p
				where p.EhrRusID is not null;

				update EHR.T_EHR_REFERENCE
				set EhrRusRefBooks = "refBooks"
				where EhrRusRefBooks is null and EhrEntity = "entityID";
		end if;

		drop table "2M69I03GTD";
	end;
End;
$$;
