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


DROP PROCEDURE IF EXISTS ehr.sp_Process_EP3FVD4DUL;

CREATE PROCEDURE ehr.sp_Process_EP3FVD4DUL
(
    "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_EP3FVD4DUL'); 
		"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 "EP3FVD4DUL";

		update "EP3FVD4DUL" set "depart_type_id" = null where "depart_type_id" = '';
        update "EP3FVD4DUL" set "depart_kind_id" = null where "depart_kind_id" = '';
        update "EP3FVD4DUL" set "hospital_subdivision_id" = null where "hospital_subdivision_id" = '';
		update "EP3FVD4DUL" set "ambulance_subdivision_id" = null where "ambulance_subdivision_id" = '';
        update "EP3FVD4DUL" set "ambulance_room_count" = null where "ambulance_room_count" = '';
        update "EP3FVD4DUL" set "lab_subdivision_id" = null where "lab_subdivision_id" = '';
        update "EP3FVD4DUL" set "lab_room_count" = null where "lab_room_count" = '';
        update "EP3FVD4DUL" set "lab_exam_per_shift" = null where "lab_exam_per_shift" = '';
        update "EP3FVD4DUL" set "building_id" = null where "building_id" = '';
        update "EP3FVD4DUL" set "building_build_year" = null where "building_build_year" = '';
        update "EP3FVD4DUL" set "building_floor_count" = null where "building_floor_count" = '';
        update "EP3FVD4DUL" set "building_address_post_index" = null where "building_address_post_index" = '';
        update "EP3FVD4DUL" set "building_address_region_id" = null where "building_address_region_id" = '';
		
        update "EP3FVD4DUL" set "depart_liquidation_date" = null where "depart_liquidation_date" = '';
		update "EP3FVD4DUL" set "hospital_liquidation_date" = null where "hospital_liquidation_date" = '';

		
		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 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)
				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_EP3FVD4DUL r2 
			SET NameEHR = p."depart_name"
			from "EP3FVD4DUL" p 
			where r2.CodeEHR = p."oid" and r2.EhrRusRefBooks = "refBooks";
		
			UPDATE EHR.T_EHR_RUS_EP3FVD4DUL rb																					  
				SET 																						    
			       mo_oid								= p."mo_oid"
			      ,depart_oid							= p."depart_oid"
			      ,depart_create_date					= p."depart_create_date"
			      ,depart_modify_date					= p."depart_modify_date"
			      ,depart_liquidation_date				= NULLIF(p."depart_liquidation_date", '')::timestamp
			      ,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			= NULLIF(p."hospital_liquidation_date", '')::timestamp
			      ,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					= p."building_create_date"
			      ,building_modify_date					= p."building_modify_date"
			      ,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 "EP3FVD4DUL" p 
			WHERE rb.CodeEHR = p."oid" and rb.EhrRusRefBooks = "refBooks";
		
		
			update "EP3FVD4DUL" p
			set EhrRusID = -1
			where not exists (select * from EHR.T_EHR_RUS_EP3FVD4DUL r2 where r2.CodeEHR = p."oid" and r2.EhrRusRefBooks = "refBooks");
		
			"ehrRusIDsCount" := (SELECT COUNT(*) FROM "EP3FVD4DUL" p WHERE p.EhrRusID = -1);
		
			CALL sp_GenerateIdForEntity('EHR.EHR_RUS_EP3FVD4DUL', 'PrimaryKey', "ehrRusIDsCount",maxEhrRusID);
			maxEhrRusID := maxEhrRusID - 1;
		
			WITH CTE AS (
				SELECT
					CodeEHR,
					ROW_NUMBER() OVER (ORDER BY "oid") AS RowNum
				FROM "EP3FVD4DUL"
				WHERE EhrRusID = -1 
			)
			UPDATE "EP3FVD4DUL" d
			SET EhrRusID = c.RowNum + maxEhrRusID
			from CTE c 
			where c.CodeEHR = d.CodeEHR;
		
			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"
				,p."depart_create_date"
				,p."depart_modify_date"
				,NULLIF(p."depart_liquidation_date", '')::timestamp without time zone
				,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"
				,NULLIF(p."hospital_liquidation_date", '')::timestamp without time zone
				,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"
				,p."building_create_date"
				,p."building_modify_date"
				,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;
		else
			alter table "EP3FVD4DUL" 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 "EP3FVD4DUL");
			
			CALL sp_GenerateIdForEntity('EHR.EHR_RUS_EP3FVD4DUL', 'PrimaryKey', "ehrRusIDsCount",maxEhrRusID);
			maxEhrRusID := maxEhrRusID - 1;
		
			UPDATE "EP3FVD4DUL" SET EhrRusID = idnew + 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"
				,p."depart_create_date"
				,p."depart_modify_date"
				,NULLIF(p."depart_liquidation_date", '')::timestamp without time zone
				,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"
				,NULLIF(p."hospital_liquidation_date", '')::timestamp without time zone
				,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"
				,p."building_create_date"
				,p."building_modify_date"
				,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 if;

		drop table "EP3FVD4DUL";
	end;
End;
$$;
