SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

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

-- =============================================
-- Author:		Marat Zigangarayev
-- Create date: 2019/02/19
-- Description:	Загрузка справочника ЕИП с кодом BR9AP1S2QB(ФРМО. Справочник структурных подразделений)
--              Версия справочника 2.59, 20.02.2019 00:53
-- =============================================
CREATE PROCEDURE ehr.[sp_Process_BR9AP1S2QB] 
    @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_BR9AP1S2QB';
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.CodeEHR, tt.depart_oid from ehr.T_EHR_RUS_BR9AP1S2QB er 
		left join #BR9AP1S2QB tt
		on er.CodeEHR = tt.depart_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)
			--(@refBooks,'BR9AP1S2QB','ФРМО. Справочник структурных подразделений','2.58',getdate(),@entityID,'1.2.643.5.1.13.13.99.2.114')
	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_BR9AP1S2QB] r2 
		join #BR9AP1S2QB p on r2.CodeEHR = p.depart_oid
	where r2.EhrRusRefBooks = @refBooks;

	UPDATE rb																					  
		SET 																						    
		  [depart_create_date] = Convert(datetime, p.depart_create_date		,104)										    
		  ,[depart_modify_date] = Convert(datetime, p.depart_modify_date		,104)										  
		  ,[depart_liquidation_date] = p.depart_liquidation_date								  
		  ,[depart_type_id] = cast(p.depart_type_id as int)								
		  ,[depart_type_name] = p.depart_type_name										
		  ,[depart_kind_id] = cast(p.depart_kind_id as int)								
		  ,[depart_kind_name] = p.depart_kind_name										
		  ,[building_address_region_id] = p.building_address_region_id					
		  ,[building_address_region_name] = p.building_address_region_name				
		  ,[building_name] = p.building_name											
		  ,[building_address_area_name] = p.building_address_area_name					
		  ,[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						
		  ,[mo_oid] = p.mo_oid	
		  ,[separate_depart_boolean]= p.[separate_depart_boolean]
		  ,[separate_depart_text] = p.[separate_depart_text]
		  ,[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_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_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_prefix_street] = p.[building_address_prefix_street]
		  ,[building_address_fias_version] = p.[building_address_fias_version]
		  ,[ambulatory_visit_per_shift] = p.[ambulatory_visit_per_shift]
		  ,[ambulatory_patient_attached] = p.[ambulatory_patient_attached]
		  ,[ambulatory_child_attached] = p.[ambulatory_child_attached]
		  ,[ambulatory_visit_home] = p.[ambulatory_visit_home]
		  ,[hospital_mode_id] = p.[hospital_mode_id]
		  ,[hospital_mode_name] = p.[hospital_mode_name]
		  ,[hospital_ambulance_boolean] = p.[hospital_ambulance_boolean]
		  ,[hospital_ambulance_text] = p.[hospital_ambulance_text]
		  ,[hospital_home_bed_count] = p.[hospital_home_bed_count]
		  ,[hospital_home_building_id] = p.[hospital_home_building_id]
		  ,[hospital_home_building_create_date] = Convert(datetime, p.[hospital_home_building_create_date]	,104)
		  ,[hospital_home_building_modify_date] = Convert(datetime, p.[hospital_home_building_modify_date]	,104)
		  ,[hospital_home_building_name] = p.[hospital_home_building_name]
		  ,[hospital_home_building_build_year] = p.[hospital_home_building_build_year]
		  ,[hospital_home_building_floor_count] = p.[hospital_home_building_floor_count]
		  ,[hospital_home_building_has_trouble] = p.[hospital_home_building_has_trouble]
		  ,[hospital_home_building_address_post_index] = p.[hospital_home_building_address_post_index]
		  ,[hospital_home_building_address_cadastral_number] = p.[hospital_home_building_address_cadastral_number]
		  ,[hospital_home_building_address_latitude] = p.[hospital_home_building_address_latitude]
		  ,[hospital_home_building_address_longtitude] = p.[hospital_home_building_address_longtitude]
		  ,[hospital_home_building_address_region_id] = p.[hospital_home_building_address_region_id]
		  ,[hospital_home_building_address_region_name] = p.[hospital_home_building_address_region_name]
		  ,[hospital_home_building_address_aoid_area] = p.[hospital_home_building_address_aoid_area]
		  ,[hospital_home_building_address_aoid_street] = p.[hospital_home_building_address_aoid_street]
		  ,[hospital_home_building_address_houseid] = p.[hospital_home_building_address_houseid]
		  ,[hospital_home_building_address_prefix_area] = p.[hospital_home_building_address_prefix_area]
		  ,[hospital_home_building_address_area_name] = p.[hospital_home_building_address_area_name]
		  ,[hospital_home_building_address_prefix_street] = p.[hospital_home_building_address_prefix_street]
		  ,[hospital_home_building_address_street_name] = p.[hospital_home_building_address_street_name]
		  ,[hospital_home_building_address_house] = p.[hospital_home_building_address_house]
		  ,[hospital_home_building_address_building] = p.[hospital_home_building_address_building]
		  ,[hospital_home_building_address_struct] = p.[hospital_home_building_address_struct]
		  ,[hospital_home_building_address_fias_version] = p.[hospital_home_building_address_fias_version]
	  														
		FROM  [EHR].[T_EHR_RUS_BR9AP1S2QB] rb					
		join #BR9AP1S2QB p on rb.CodeEHR = p.depart_oid
		WHERE rb.EhrRusRefBooks = @refBooks;


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

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

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

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

	INSERT INTO [EHR].[T_EHR_RUS_BR9AP1S2QB]
	           ([EhrRusRefBooks]
	           ,[EhrRusID]
	           ,[CodeEHR]
	           ,[NameEHR]
	           ,[depart_create_date]
	           ,[depart_modify_date]
	           ,[depart_liquidation_date]
	           ,[depart_type_id]
	           ,[depart_type_name]
	           ,[depart_kind_id]
	           ,[depart_kind_name]
	           ,[building_address_region_id]
	           ,[building_address_region_name]
	           ,[building_name]
	           ,[building_address_area_name]
	           ,[building_address_street_name]
	           ,[building_address_house]
	           ,[building_address_building]
	           ,[building_address_struct]
	           ,[mo_oid]
	           ,[separate_depart_boolean]
	           ,[separate_depart_text]
	           ,[building_id]
	           ,[building_create_date]
	           ,[building_modify_date]
	           ,[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_aoid_area]
	           ,[building_address_aoid_street]
	           ,[building_address_houseid]
	           ,[building_address_prefix_area]
	           ,[building_address_prefix_street]
	           ,[building_address_fias_version]
	           ,[ambulatory_visit_per_shift]
	           ,[ambulatory_patient_attached]
	           ,[ambulatory_child_attached]
	           ,[ambulatory_visit_home]
	           ,[hospital_mode_id]
	           ,[hospital_mode_name]
	           ,[hospital_ambulance_boolean]
	           ,[hospital_ambulance_text]
	           ,[hospital_home_bed_count]
	           ,[hospital_home_building_id]
	           ,[hospital_home_building_create_date]
	           ,[hospital_home_building_modify_date]
	           ,[hospital_home_building_name]
	           ,[hospital_home_building_build_year]
	           ,[hospital_home_building_floor_count]
	           ,[hospital_home_building_has_trouble]
	           ,[hospital_home_building_address_post_index]
	           ,[hospital_home_building_address_cadastral_number]
	           ,[hospital_home_building_address_latitude]
	           ,[hospital_home_building_address_longtitude]
	           ,[hospital_home_building_address_region_id]
	           ,[hospital_home_building_address_region_name]
	           ,[hospital_home_building_address_aoid_area]
	           ,[hospital_home_building_address_aoid_street]
	           ,[hospital_home_building_address_houseid]
	           ,[hospital_home_building_address_prefix_area]
	           ,[hospital_home_building_address_area_name]
	           ,[hospital_home_building_address_prefix_street]
	           ,[hospital_home_building_address_street_name]
	           ,[hospital_home_building_address_house]
	           ,[hospital_home_building_address_building]
	           ,[hospital_home_building_address_struct]
	           ,[hospital_home_building_address_fias_version])
	select 
	    @refBooks
	    ,p.[EhrRusID]
	    ,p.depart_oid --<CodeEHR, varchar(52),>
	    ,p.depart_name --<NameEHR, varchar(1024),>
		,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.[building_address_region_id]
		,p.[building_address_region_name]
		,p.[building_name]
		,p.[building_address_area_name]
		,p.[building_address_street_name]
		,p.[building_address_house]
		,p.[building_address_building]
		,p.[building_address_struct]
		,p.[mo_oid]
		,p.[separate_depart_boolean]
		,p.[separate_depart_text]
		,p.[building_id]
		,Convert(datetime, p.[building_create_date]	,104)
		,Convert(datetime, p.[building_modify_date]	,104)
		,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_aoid_area]
		,p.[building_address_aoid_street]
		,p.[building_address_houseid]
		,p.[building_address_prefix_area]
		,p.[building_address_prefix_street]
		,p.[building_address_fias_version]
		,p.[ambulatory_visit_per_shift]
		,p.[ambulatory_patient_attached]
		,p.[ambulatory_child_attached]
		,p.[ambulatory_visit_home]
		,p.[hospital_mode_id]
		,p.[hospital_mode_name]
		,p.[hospital_ambulance_boolean]
		,p.[hospital_ambulance_text]
		,p.[hospital_home_bed_count]
		,p.[hospital_home_building_id]
		,Convert(datetime, p.[hospital_home_building_create_date]	,104)
		,Convert(datetime, p.[hospital_home_building_modify_date]	,104)
		,p.[hospital_home_building_name]
		,p.[hospital_home_building_build_year]
		,p.[hospital_home_building_floor_count]
		,p.[hospital_home_building_has_trouble]
		,p.[hospital_home_building_address_post_index]
		,p.[hospital_home_building_address_cadastral_number]
		,p.[hospital_home_building_address_latitude]
		,p.[hospital_home_building_address_longtitude]
		,p.[hospital_home_building_address_region_id]
		,p.[hospital_home_building_address_region_name]
		,p.[hospital_home_building_address_aoid_area]
		,p.[hospital_home_building_address_aoid_street]
		,p.[hospital_home_building_address_houseid]
		,p.[hospital_home_building_address_prefix_area]
		,p.[hospital_home_building_address_area_name]
		,p.[hospital_home_building_address_prefix_street]
		,p.[hospital_home_building_address_street_name]
		,p.[hospital_home_building_address_house]
		,p.[hospital_home_building_address_building]
		,p.[hospital_home_building_address_struct]
		,p.[hospital_home_building_address_fias_version]

	from #BR9AP1S2QB 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)
			--(@refBooks,'BR9AP1S2QB','ФРМО. Справочник структурных подразделений','2.58',getdate(),@entityID,'1.2.643.5.1.13.13.99.2.114')
	
	SELECT @ehrRusIDsCount = COUNT(*) FROM #BR9AP1S2QB;

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

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

	INSERT INTO [EHR].[T_EHR_RUS_BR9AP1S2QB]
	           ([EhrRusRefBooks]
	           ,[EhrRusID]
	           ,[CodeEHR]
	           ,[NameEHR]
	           ,[depart_create_date]
	           ,[depart_modify_date]
	           ,[depart_liquidation_date]
	           ,[depart_type_id]
	           ,[depart_type_name]
	           ,[depart_kind_id]
	           ,[depart_kind_name]
	           ,[building_address_region_id]
	           ,[building_address_region_name]
	           ,[building_name]
	           ,[building_address_area_name]
	           ,[building_address_street_name]
	           ,[building_address_house]
	           ,[building_address_building]
	           ,[building_address_struct]
	           ,[mo_oid]
	           ,[separate_depart_boolean]
	           ,[separate_depart_text]
	           ,[building_id]
	           ,[building_create_date]
	           ,[building_modify_date]
	           ,[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_aoid_area]
	           ,[building_address_aoid_street]
	           ,[building_address_houseid]
	           ,[building_address_prefix_area]
	           ,[building_address_prefix_street]
	           ,[building_address_fias_version]
	           ,[ambulatory_visit_per_shift]
	           ,[ambulatory_patient_attached]
	           ,[ambulatory_child_attached]
	           ,[ambulatory_visit_home]
	           ,[hospital_mode_id]
	           ,[hospital_mode_name]
	           ,[hospital_ambulance_boolean]
	           ,[hospital_ambulance_text]
	           ,[hospital_home_bed_count]
	           ,[hospital_home_building_id]
	           ,[hospital_home_building_create_date]
	           ,[hospital_home_building_modify_date]
	           ,[hospital_home_building_name]
	           ,[hospital_home_building_build_year]
	           ,[hospital_home_building_floor_count]
	           ,[hospital_home_building_has_trouble]
	           ,[hospital_home_building_address_post_index]
	           ,[hospital_home_building_address_cadastral_number]
	           ,[hospital_home_building_address_latitude]
	           ,[hospital_home_building_address_longtitude]
	           ,[hospital_home_building_address_region_id]
	           ,[hospital_home_building_address_region_name]
	           ,[hospital_home_building_address_aoid_area]
	           ,[hospital_home_building_address_aoid_street]
	           ,[hospital_home_building_address_houseid]
	           ,[hospital_home_building_address_prefix_area]
	           ,[hospital_home_building_address_area_name]
	           ,[hospital_home_building_address_prefix_street]
	           ,[hospital_home_building_address_street_name]
	           ,[hospital_home_building_address_house]
	           ,[hospital_home_building_address_building]
	           ,[hospital_home_building_address_struct]
	           ,[hospital_home_building_address_fias_version])
	select 
	    @refBooks
	    ,p.[EhrRusID]
	    ,p.depart_oid --<CodeEHR, varchar(52),>
	    ,p.depart_name --<NameEHR, varchar(1024),>
		,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.[building_address_region_id]
		,p.[building_address_region_name]
		,p.[building_name]
		,p.[building_address_area_name]
		,p.[building_address_street_name]
		,p.[building_address_house]
		,p.[building_address_building]
		,p.[building_address_struct]
		,p.[mo_oid]
		,p.[separate_depart_boolean]
		,p.[separate_depart_text]
		,p.[building_id]
		,Convert(datetime, p.[building_create_date]	,104)
		,Convert(datetime, p.[building_modify_date]	,104)
		,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_aoid_area]
		,p.[building_address_aoid_street]
		,p.[building_address_houseid]
		,p.[building_address_prefix_area]
		,p.[building_address_prefix_street]
		,p.[building_address_fias_version]
		,p.[ambulatory_visit_per_shift]
		,p.[ambulatory_patient_attached]
		,p.[ambulatory_child_attached]
		,p.[ambulatory_visit_home]
		,p.[hospital_mode_id]
		,p.[hospital_mode_name]
		,p.[hospital_ambulance_boolean]
		,p.[hospital_ambulance_text]
		,p.[hospital_home_bed_count]
		,p.[hospital_home_building_id]
		,Convert(datetime, p.[hospital_home_building_create_date]	,104)
		,Convert(datetime, p.[hospital_home_building_modify_date]	,104)
		,p.[hospital_home_building_name]
		,p.[hospital_home_building_build_year]
		,p.[hospital_home_building_floor_count]
		,p.[hospital_home_building_has_trouble]
		,p.[hospital_home_building_address_post_index]
		,p.[hospital_home_building_address_cadastral_number]
		,p.[hospital_home_building_address_latitude]
		,p.[hospital_home_building_address_longtitude]
		,p.[hospital_home_building_address_region_id]
		,p.[hospital_home_building_address_region_name]
		,p.[hospital_home_building_address_aoid_area]
		,p.[hospital_home_building_address_aoid_street]
		,p.[hospital_home_building_address_houseid]
		,p.[hospital_home_building_address_prefix_area]
		,p.[hospital_home_building_address_area_name]
		,p.[hospital_home_building_address_prefix_street]
		,p.[hospital_home_building_address_street_name]
		,p.[hospital_home_building_address_house]
		,p.[hospital_home_building_address_building]
		,p.[hospital_home_building_address_struct]
		,p.[hospital_home_building_address_fias_version]

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