SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sp_Process_2M69I03GTD]') and xtype = N'P')
drop procedure [dbo].[sp_Process_2M69I03GTD]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[ehr].[sp_Process_2M69I03GTD]') and xtype = N'P')
drop procedure ehr.[sp_Process_2M69I03GTD]
GO

-- =============================================
-- Author:		Arseniy Gadelshin
-- Create date: 2021/07/15
-- Description:	Загрузка справочника ЕИП с кодом 2M69I03GTD(Лекарственные препараты. Товарные позиции. ЕСКЛП с кодами КТРУ)
--              Версия справочника 8.8, 14.07.2021 19:16
-- =============================================
CREATE PROCEDURE ehr.[sp_Process_2M69I03GTD] 
    @Code varchar(64),
    @Name varchar(255),
    @CurrVersion varchar(8),
	@OldVersion varchar(8),
    @OID varchar(54),
	@CheckMatching BIT
As
Begin
	SET NOCOUNT ON;

declare @entityID int, @refBooks int, @ehrRusIDsCount int, @maxEhrRusID int;
select @entityID = Entityid from VCLib.T_ENTITIES e where EntName = 'EHR_RUS_2M69I03GTD';
select top 1 @refBooks = b.EhrRusRefBooksID from EHR.T_EHR_RUS_REF_BOOKS b where b.Entities = @entityID and b.CurrVersion = @CurrVersion;
if @OldVersion is not null or @refBooks is not null
begin
	if @refBooks is null
	begin
		select top 1 @refBooks = b.EhrRusRefBooksID from EHR.T_EHR_RUS_REF_BOOKS b where b.Entities = @entityID and b.CurrVersion = @OldVersion;
	end

	if @CheckMatching = 1
	begin
		IF object_id('tempdb..#tempTbForEhrs') IS NOT NULL
		DROP TABLE #tempTbForEhrs
		CREATE TABLE #tempTbForEhrs
		(
		Id1 int,
		Id2 int
		)

		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)
		begin
			raiserror('Невозможно обновить справочник, обнаружено несоответствие записей нового справочника с текущем. Скачайте новый справочник и обновите связи', 16, 10)
		end
		DROP TABLE #tempTbForEhrs
	end

	if @refBooks is null
	begin
		EXEC @refBooks = sp_GenerateIdForEntity 'EHR.EHR_RUS_REF_BOOKS';
	
		INSERT INTO [EHR].[T_EHR_RUS_REF_BOOKS]
			([EhrRusRefBooksID],[Code],[Name],[CurrVersion],[UpdateDate],[Entities],[OID], Deleted)
		VALUES
			(@refBooks, @Code, @Name, @CurrVersion, getdate(), @entityID, @OID, 0)
	end
	else
	begin
		update [EHR].[T_EHR_RUS_REF_BOOKS]
		set [Code] = @Code,
			[Name]= @Name,
			[CurrVersion] = @CurrVersion,
			[UpdateDate] = getdate(),
			[Entities] = @entityID,
			[OID] = @OID
		where [EhrRusRefBooksID] = @refBooks;
	end

	UPDATE r2
	SET [NameEHR] = p.NAME_TRADE
	from [EHR].[T_EHR_RUS_2M69I03GTD] r2 
		join #2M69I03GTD p on r2.IdEHR = p.ID
	where r2.EhrRusRefBooks = @refBooks;

	UPDATE 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]		          = Convert(datetime, p.[DATE_REGISTRATION]	,104)
	      ,[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  [EHR].[T_EHR_RUS_2M69I03GTD] rb					
		join #2M69I03GTD p on rb.IdEHR = p.ID
	WHERE rb.EhrRusRefBooks = @refBooks;


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

	SELECT @ehrRusIDsCount = COUNT(*) FROM #2M69I03GTD p WHERE p.[EhrRusID] = -1;

	EXEC @maxEhrRusID = sp_GenerateIdForEntity 'EHR.EHR_RUS_2M69I03GTD' , 'PrimaryKey', @ehrRusIDsCount;
	SET @maxEhrRusID = @maxEhrRusID - 1;

	;WITH CTE AS (
		SELECT
			[EhrRusID],
			ROW_NUMBER() OVER (ORDER BY ID) AS RowNum
		FROM #2M69I03GTD 
		WHERE [EhrRusID] = -1 
	)
	UPDATE CTE 
	SET [EhrRusID] = RowNum + @maxEhrRusId;

	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]		  
		       ,Convert(datetime, p.[DATE_REGISTRATION]	,104)			  
		       ,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;
end
else
begin
	EXEC @refBooks = sp_GenerateIdForEntity 'EHR.EHR_RUS_REF_BOOKS';
	
	INSERT INTO [EHR].[T_EHR_RUS_REF_BOOKS]
		([EhrRusRefBooksID],[Code],[Name],[CurrVersion],[UpdateDate],[Entities],[OID], Deleted)
	VALUES
		(@refBooks, @Code, @Name, @CurrVersion, getdate(), @entityID, @OID, 0)

	SELECT @ehrRusIDsCount = COUNT(*) FROM #2M69I03GTD;
	
	EXEC @maxEhrRusID = sp_GenerateIdForEntity 'EHR.EHR_RUS_2M69I03GTD', 'PrimaryKey', @ehrRusIDsCount;
	SET @maxEhrRusID = @maxEhrRusID - 1;

	;WITH CTE AS (
		SELECT
			[EhrRusID],
			ROW_NUMBER() OVER (ORDER BY ID) AS RowNum
		FROM #2M69I03GTD 
	)
	UPDATE CTE 
	SET [EhrRusID] = RowNum + @maxEhrRusId;

	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]		  
		       ,Convert(datetime, p.[DATE_REGISTRATION]	,104)		  
		       ,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
End
go
