-- =============================================
-- Author:		Marat Zigangarayev
-- Create date: 2019/02/19
-- Description:	Загрузка справочника ЕИП с кодом "BR9AP1S2QB"(ФРМО. Справочник структурных подразделений)
--              Версия справочника 2.59, 20.02.2019 00:53
-- =============================================

DROP PROCEDURE IF EXISTS ehr.sp_Process_BR9AP1S2QB;

CREATE PROCEDURE ehr.sp_Process_BR9AP1S2QB
(
    "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_BR9AP1S2QB'  limit 1);
		"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
        ANALYZE "BR9AP1S2QB";

        update "BR9AP1S2QB" set "depart_kind_id" = null where "depart_kind_id" = '';
        update "BR9AP1S2QB" set "building_id" = null where "building_id" = '';
        update "BR9AP1S2QB" set "building_build_year" = null where "building_build_year" = '';
        update "BR9AP1S2QB" set "building_floor_count" = null where "building_floor_count" = '';
        update "BR9AP1S2QB" set "building_address_post_index" = null where "building_address_post_index" = '';
        update "BR9AP1S2QB" set "ambulatory_visit_per_shift" = null where "ambulatory_visit_per_shift" = '';
        update "BR9AP1S2QB" set "ambulatory_patient_attached" = null where "ambulatory_patient_attached" = '';
        update "BR9AP1S2QB" set "ambulatory_child_attached" = null where "ambulatory_child_attached" = '';
        update "BR9AP1S2QB" set "building_address_region_id" = null where "building_address_region_id" = '';
        update "BR9AP1S2QB" set "hospital_home_building_id" = null where "hospital_home_building_id" = '';
        update "BR9AP1S2QB" set "hospital_home_building_build_year" = null where "hospital_home_building_build_year" = '';
        update "BR9AP1S2QB" set "hospital_home_building_floor_count" = null where "hospital_home_building_floor_count" = '';
        update "BR9AP1S2QB" set "hospital_home_building_address_post_index" = null where "hospital_home_building_address_post_index" = '';
        update "BR9AP1S2QB" set "hospital_home_building_address_region_id" = null where "hospital_home_building_address_region_id" = '';

		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.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)
				then
					RAISE EXCEPTION 'Невозможно обновить справочник, обнаружено несоответствие записей нового справочника с текущем. Скачайте новый справочник и обновите связи!';
                    RETURN;
				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');
					--("refBooks",'"BR9AP1S2QB"','ФРМО. Справочник структурных подразделений','2.58',now(),"entityID",'1.2.643.5.1.13.13.99.2.114')
			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_BR9AP1S2QB r2
			SET NameEHR = p."depart_name"
			from "BR9AP1S2QB" p
			where r2.CodeEHR = p."depart_oid" and r2.EhrRusRefBooks = "refBooks";
		
			UPDATE EHR.T_EHR_RUS_BR9AP1S2QB rb																					  
				SET 																						    
				  depart_create_date								= p."depart_create_date"
				  ,depart_modify_date								= p."depart_modify_date"
				  ,depart_liquidation_date							= p."depart_liquidation_date"
				  ,depart_type_id									= p."depart_type_id"::integer
				  ,depart_type_name									= p."depart_type_name"					
				  ,depart_kind_id									= p."depart_kind_id"::integer
				  ,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								= p."building_create_date"
				  ,building_modify_date								= p."building_modify_date"
				  ,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				= p."hospital_home_building_create_date"
				  ,hospital_home_building_modify_date				= p."hospital_home_building_modify_date"
				  ,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 "BR9AP1S2QB" p 
				WHERE rb.CodeEHR = p."depart_oid" and rb.EhrRusRefBooks = "refBooks";
		
			update "BR9AP1S2QB"
			set EhrRusID = -1
			where not exists (select * from EHR.T_EHR_RUS_BR9AP1S2QB r2 where r2.CodeEHR = "depart_oid" and r2.EhrRusRefBooks = "refBooks");
		
			"ehrRusIDsCount" := (SELECT COUNT(*) FROM "BR9AP1S2QB" p WHERE p.EhrRusID = -1);
		
			CALL sp_GenerateIdForEntity('EHR.EHR_RUS_BR9AP1S2QB', 'PrimaryKey', "ehrRusIDsCount",maxEhrRusID);
			maxEhrRusID := maxEhrRusID - 1;
		
			WITH CTE AS (
				SELECT
					CodeEHR,
					ROW_NUMBER() OVER (ORDER BY "depart_oid") AS RowNum
				FROM "BR9AP1S2QB"
				WHERE EhrRusID = -1 
			)
			UPDATE "BR9AP1S2QB" d
			SET EhrRusID = c.RowNum + maxEhrRusID
			from CTE c 
			where c.CodeEHR = d.CodeEHR;
		
			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),>
				,p."depart_create_date"
				,p."depart_modify_date"
				,p."depart_liquidation_date"
				,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"
				,p."building_create_date"
				,p."building_modify_date"
				,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"
				,p."hospital_home_building_create_date"
				,p."hospital_home_building_modify_date"
				,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;
		else
		    alter table "BR9AP1S2QB" add column idnew int GENERATED ALWAYS AS IDENTITY;
		
			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 "BR9AP1S2QB");
		
			CALL sp_GenerateIdForEntity('EHR.EHR_RUS_BR9AP1S2QB', 'PrimaryKey', "ehrRusIDsCount",maxEhrRusID);
			maxEhrRusID := maxEhrRusID - 1;
		
			UPDATE "BR9AP1S2QB" SET EhrRusID = idnew + 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),>
				,p."depart_create_date"
				,p."depart_modify_date"
				,p."depart_liquidation_date"
				,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"
				,p."building_create_date"
				,p."building_modify_date"
				,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"
				,p."hospital_home_building_create_date"
				,p."hospital_home_building_modify_date"
				,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 if;

		drop table "BR9AP1S2QB";
	End;
End;
$$;