SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

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

-- =============================================
-- Author:		Marat Zigangarayev
-- Create date: 2019/02/19
-- Description:	Загрузка справочника ЕИП с кодом EP3FVD4DUL(ФРМО. Справочник отделений и кабинетов)
--              Версия справочника 2.59, 20.02.2019 00:53
-- =============================================
CREATE PROCEDURE ehr.[sp_Process_EP3FVD4DUL] 
    @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_EP3FVD4DUL';
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 varchar(60),
		Id2 varchar(60)
		)

		insert into #tempTbForEhrs
		select er.CodeEHR, tt.oid from ehr.T_EHR_RUS_EP3FVD4DUL er 
		left join #EP3FVD4DUL tt
		on er.CodeEHR = tt.oid
		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.depart_name
	from [EHR].[T_EHR_RUS_EP3FVD4DUL] r2 
		join #EP3FVD4DUL p on r2.CodeEHR = p.oid
	where r2.EhrRusRefBooks = @refBooks;

	UPDATE rb																					  
		SET 																						    
	       [mo_oid]							  = p.[mo_oid]
	      ,[depart_oid]						  = p.[depart_oid]
	      ,[depart_create_date]				  = Convert(datetime,p.[depart_create_date]	,104)
	      ,[depart_modify_date]				  = Convert(datetime,p.[depart_modify_date]	,104)
	      ,[depart_liquidation_date]		  = Convert(datetime,p.[depart_liquidation_date]	,104)
	      ,[depart_type_id]					  = p.[depart_type_id]
	      ,[depart_type_name]				  = p.[depart_type_name]
	      ,[depart_kind_id]					  = p.[depart_kind_id]
	      ,[depart_kind_name]				  = p.[depart_kind_name]
	      ,[separate_depart_boolean]		  = p.[separate_depart_boolean]
	      ,[separate_depart_text]			  = p.[separate_depart_text]
	      ,[hospital_name]					  = p.[hospital_name]
	      ,[hospital_subdivision_id]		  = p.[hospital_subdivision_id]
	      ,[hospital_subdivision_name]		  = p.[hospital_subdivision_name]
	      ,[hospital_liquidation_date]		  = Convert(datetime,p.[hospital_liquidation_date]	,104)
	      ,[ambulance_subdivision_id]		  = p.[ambulance_subdivision_id]
	      ,[ambulance_subdivision_name]		  = p.[ambulance_subdivision_name]
	      ,[ambulance_room_count]			  = p.[ambulance_room_count]
	      ,[lab_subdivision_id]				  = p.[lab_subdivision_id]
	      ,[lab_subdivision_name]			  = p.[lab_subdivision_name]
	      ,[lab_room_count]					  = p.[lab_room_count]
	      ,[lab_exam_per_shift]				  = p.[lab_exam_per_shift]
	      ,[building_id]					  = p.[building_id]
	      ,[building_create_date]			  = Convert(datetime,p.[building_create_date]	,104)
	      ,[building_modify_date]			  = Convert(datetime,p.[building_modify_date]	,104)
	      ,[building_name]					  = p.[building_name]
	      ,[building_build_year]			  = p.[building_build_year]
	      ,[building_floor_count]			  = p.[building_floor_count]
	      ,[building_has_trouble]			  = p.[building_has_trouble]
	      ,[building_address_post_index]	  = p.[building_address_post_index]
	      ,[building_address_cadastral_number]= p.[building_address_cadastral_number]
	      ,[building_address_latitude]		  = p.[building_address_latitude]
	      ,[building_address_longtitude]	  = p.[building_address_longtitude]
	      ,[building_address_region_id]		  = p.[building_address_region_id]
	      ,[building_address_region_name]	  = p.[building_address_region_name]
	      ,[building_address_aoid_area]		  = p.[building_address_aoid_area]
	      ,[building_address_aoid_street]	  = p.[building_address_aoid_street]
	      ,[building_address_houseid]		  = p.[building_address_houseid]
	      ,[building_address_prefix_area]	  = p.[building_address_prefix_area]
	      ,[building_address_area_name]		  = p.[building_address_area_name]
	      ,[building_address_prefix_street]	  = p.[building_address_prefix_street]
	      ,[building_address_street_name]	  = p.[building_address_street_name]
	      ,[building_address_house]			  = p.[building_address_house]
	      ,[building_address_building]		  = p.[building_address_building]
	      ,[building_address_struct]		  = p.[building_address_struct]
	      ,[building_address_fias_version]	  = p.[building_address_fias_version]
	  														
	 FROM  [EHR].[T_EHR_RUS_EP3FVD4DUL] rb		
		join #EP3FVD4DUL p on rb.CodeEHR = p.oid
	WHERE rb.EhrRusRefBooks = @refBooks;


	update p
	set [EhrRusID] = -1
	from #EP3FVD4DUL p
	where not exists (select * from [EHR].[T_EHR_RUS_EP3FVD4DUL] r2 where r2.CodeEHR = p.oid and r2.EhrRusRefBooks = @refBooks);

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

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

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

	INSERT INTO [EHR].[T_EHR_RUS_EP3FVD4DUL]
	           ([EhrRusRefBooks]
	           ,[EhrRusID]
	           ,[CodeEHR]
	           ,[NameEHR]
	           ,[mo_oid]							  
	           ,[depart_oid]						  
	           ,[depart_create_date]				  
	           ,[depart_modify_date]				  
	           ,[depart_liquidation_date]		  
	           ,[depart_type_id]					  
	           ,[depart_type_name]				  
	           ,[depart_kind_id]					  
	           ,[depart_kind_name]				  
	           ,[separate_depart_boolean]		  
	           ,[separate_depart_text]			  
	           ,[hospital_name]					  
	           ,[hospital_subdivision_id]		  
	           ,[hospital_subdivision_name]		  
	           ,[hospital_liquidation_date]		  
	           ,[ambulance_subdivision_id]		  
	           ,[ambulance_subdivision_name]		  
	           ,[ambulance_room_count]			  
	           ,[lab_subdivision_id]				  
	           ,[lab_subdivision_name]			  
	           ,[lab_room_count]					  
	           ,[lab_exam_per_shift]				  
	           ,[building_id]					  
	           ,[building_create_date]			  
	           ,[building_modify_date]			  
	           ,[building_name]					  
	           ,[building_build_year]			  
	           ,[building_floor_count]			  
	           ,[building_has_trouble]			  
	           ,[building_address_post_index]	  
	           ,[building_address_cadastral_number]
	           ,[building_address_latitude]		  
	           ,[building_address_longtitude]	  
	           ,[building_address_region_id]		  
	           ,[building_address_region_name]	  
	           ,[building_address_aoid_area]		  
	           ,[building_address_aoid_street]	  
	           ,[building_address_houseid]		  
	           ,[building_address_prefix_area]	  
	           ,[building_address_area_name]		  
	           ,[building_address_prefix_street]	  
	           ,[building_address_street_name]	  
	           ,[building_address_house]			  
	           ,[building_address_building]		  
	           ,[building_address_struct]		  
	           ,[building_address_fias_version])  

	select 
		@refBooks
		,p.[EhrRusID]
		,p.oid --<CodeEHR, varchar(52),>
		,p.depart_name --<NameEHR, varchar(1024),>
		,p.[mo_oid]
		,p.[depart_oid]
		,Convert(datetime,p.[depart_create_date]	,104)
		,Convert(datetime,p.[depart_modify_date]	,104)
		,Convert(datetime,p.[depart_liquidation_date]	,104)
		,p.[depart_type_id]
		,p.[depart_type_name]
		,p.[depart_kind_id]
		,p.[depart_kind_name]
		,p.[separate_depart_boolean]
		,p.[separate_depart_text]
		,p.[hospital_name]
		,p.[hospital_subdivision_id]
		,p.[hospital_subdivision_name]
		,Convert(datetime,p.[hospital_liquidation_date]	,104)
		,p.[ambulance_subdivision_id]
		,p.[ambulance_subdivision_name]
		,p.[ambulance_room_count]
		,p.[lab_subdivision_id]
		,p.[lab_subdivision_name]
		,p.[lab_room_count]
		,p.[lab_exam_per_shift]
		,p.[building_id]
		,Convert(datetime,p.[building_create_date]	,104)
		,Convert(datetime,p.[building_modify_date]	,104)
		,p.[building_name]
		,p.[building_build_year]
		,p.[building_floor_count]
		,p.[building_has_trouble]
		,p.[building_address_post_index]
		,p.[building_address_cadastral_number]
		,p.[building_address_latitude]
		,p.[building_address_longtitude]
		,p.[building_address_region_id]
		,p.[building_address_region_name]
		,p.[building_address_aoid_area]
		,p.[building_address_aoid_street]
		,p.[building_address_houseid]
		,p.[building_address_prefix_area]
		,p.[building_address_area_name]
		,p.[building_address_prefix_street]
		,p.[building_address_street_name]
		,p.[building_address_house]
		,p.[building_address_building]
		,p.[building_address_struct]
		,p.[building_address_fias_version]

	from #EP3FVD4DUL 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 #EP3FVD4DUL

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

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

	INSERT INTO [EHR].[T_EHR_RUS_EP3FVD4DUL]
		       ([EhrRusRefBooks]
			   ,[EhrRusID]
		       ,[CodeEHR]
			   ,[NameEHR]
	           ,[mo_oid]							  
	           ,[depart_oid]						  
	           ,[depart_create_date]				  
	           ,[depart_modify_date]				  
	           ,[depart_liquidation_date]		  
	           ,[depart_type_id]					  
	           ,[depart_type_name]				  
	           ,[depart_kind_id]					  
	           ,[depart_kind_name]				  
	           ,[separate_depart_boolean]		  
	           ,[separate_depart_text]			  
	           ,[hospital_name]					  
	           ,[hospital_subdivision_id]		  
	           ,[hospital_subdivision_name]		  
	           ,[hospital_liquidation_date]		  
	           ,[ambulance_subdivision_id]		  
	           ,[ambulance_subdivision_name]		  
	           ,[ambulance_room_count]			  
	           ,[lab_subdivision_id]				  
	           ,[lab_subdivision_name]			  
	           ,[lab_room_count]					  
	           ,[lab_exam_per_shift]				  
	           ,[building_id]					  
	           ,[building_create_date]			  
	           ,[building_modify_date]			  
	           ,[building_name]					  
	           ,[building_build_year]			  
	           ,[building_floor_count]			  
	           ,[building_has_trouble]			  
	           ,[building_address_post_index]	  
	           ,[building_address_cadastral_number]
	           ,[building_address_latitude]		  
	           ,[building_address_longtitude]	  
	           ,[building_address_region_id]		  
	           ,[building_address_region_name]	  
	           ,[building_address_aoid_area]		  
	           ,[building_address_aoid_street]	  
	           ,[building_address_houseid]		  
	           ,[building_address_prefix_area]	  
	           ,[building_address_area_name]		  
	           ,[building_address_prefix_street]	  
	           ,[building_address_street_name]	  
	           ,[building_address_house]			  
	           ,[building_address_building]		  
	           ,[building_address_struct]		  
	           ,[building_address_fias_version])  

	select 
		@refBooks
		,p.[EhrRusID]
		,p.oid --<CodeEHR, varchar(52),>
		,p.depart_name --<NameEHR, varchar(1024),>
		,p.[mo_oid]
		,p.[depart_oid]
		,Convert(datetime,p.[depart_create_date]	,104)
		,Convert(datetime,p.[depart_modify_date]	,104)
		,Convert(datetime,p.[depart_liquidation_date]	,104)
		,p.[depart_type_id]
		,p.[depart_type_name]
		,p.[depart_kind_id]
		,p.[depart_kind_name]
		,p.[separate_depart_boolean]
		,p.[separate_depart_text]
		,p.[hospital_name]
		,p.[hospital_subdivision_id]
		,p.[hospital_subdivision_name]
		,Convert(datetime,p.[hospital_liquidation_date]	,104)
		,p.[ambulance_subdivision_id]
		,p.[ambulance_subdivision_name]
		,p.[ambulance_room_count]
		,p.[lab_subdivision_id]
		,p.[lab_subdivision_name]
		,p.[lab_room_count]
		,p.[lab_exam_per_shift]
		,p.[building_id]
		,Convert(datetime,p.[building_create_date]	,104)
		,Convert(datetime,p.[building_modify_date]	,104)
		,p.[building_name]
		,p.[building_build_year]
		,p.[building_floor_count]
		,p.[building_has_trouble]
		,p.[building_address_post_index]
		,p.[building_address_cadastral_number]
		,p.[building_address_latitude]
		,p.[building_address_longtitude]
		,p.[building_address_region_id]
		,p.[building_address_region_name]
		,p.[building_address_aoid_area]
		,p.[building_address_aoid_street]
		,p.[building_address_houseid]
		,p.[building_address_prefix_area]
		,p.[building_address_area_name]
		,p.[building_address_prefix_street]
		,p.[building_address_street_name]
		,p.[building_address_house]
		,p.[building_address_building]
		,p.[building_address_struct]
		,p.[building_address_fias_version]

	from #EP3FVD4DUL p
	where p.[EhrRusID] is not null;

	update ehr.T_EHR_REFERENCE
	set EhrRusRefBooks = @refBooks
	where EhrEntity = @entityID and EhrRusRefBooks is null
end
End
go