DROP PROCEDURE IF EXISTS DBO.sp_rr_patients;
-- =============================================
-- Author:      Vitacore (с)
-- Create date: 2024-06-21
-- Description: Загрузка регистра региональных льготников
-- =============================================
DROP PROCEDURE IF EXISTS APS.sp_rr_patients;

CREATE OR REPLACE PROCEDURE APS.sp_rr_patients
(
	"Xml" text DEFAULT null
,	"ID" uuid DEFAULT NULL::uuid
,	"QueueName" character varying DEFAULT 'RDSSync'::character varying
,	"dateTime" timestamp without time zone DEFAULT NULL::timestamp without time zone
,	"User" uuid DEFAULT NULL::uuid
)
    language plpgsql
as
$$
Begin
    Declare 
		_OperationType varchar(255);
	begin
		"dateTime" := coalesce("dateTime", current_timestamp);

		IF ("Xml" is not null)
		then
		    _OperationType := (xpath('/RR_PATIENT/header[1]/TYPE[1]/text()', "Xml"::xml))[1]::varchar(6);

			Drop Table If Exists Tmp_RR_PATIENTS;
			create temp table Tmp_RR_PATIENTS
			as
		    select 
				(xpath('RR_PATIENT/RrPatientID[1]/text()', x))[1]::text::int                   						as RrPatientID
				,(xpath('RR_PATIENT/Snils[1]/text()', x))[1]::varchar(14)                             					as Snils
				,(xpath('RR_PATIENT/DocSeries[1]/text()', x))[1]::varchar(16)                             			as DocSeries
				,(xpath('RR_PATIENT/DocNumber[1]/text()', x))[1]::varchar(16)                             			as DocNumber
				,(xpath('RR_PATIENT/LastName[1]/text()', x))[1]::varchar(40)                             				as LastName
				,(xpath('RR_PATIENT/FirstName[1]/text()', x))[1]::varchar(40)                             			as FirstName
				,(xpath('RR_PATIENT/FatherName[1]/text()', x))[1]::varchar(40)                             			as FatherName		
				,to_timestamp((xpath('RR_PATIENT/Birthday[1]/text()', x))[1]::varchar(10), 'YYYY.MM.DD')     			as Birthday
				,to_timestamp((xpath('RR_PATIENT/DateBegin[1]/text()', x))[1]::varchar(10), 'YYYY.MM.DD')				as DateBegin		
				,to_timestamp((xpath('RR_PATIENT/DateEnd[1]/text()', x))[1]::varchar(10), 'YYYY.MM.DD')     			as DateEnd
				,to_timestamp((xpath('RR_PATIENT/LastEditDate[1]/text()', x))[1]::varchar(10), 'YYYY.MM.DD')			as LastEditDate
				,(xpath('RR_PATIENT/Sex[1]/text()', x))[1]::text::int                                  				as Sex
				,(xpath('RR_PATIENT/Lpu[1]/text()', x))[1]::varchar(6)                             					as Lpu
				,(xpath('RR_PATIENT/ExcludeReason[1]/text()', x))[1]::text::int										as ExcludeReason
				,(xpath('RR_PATIENT/DocType[1]/text()', x))[1]::text::int                                  			as TIPD
				,(xpath('RR_PATIENT/Kod[1]/text()', x))[1]::varchar(32)                             					as Kod
				,(xpath('RR_PATIENT/KodAdr[1]/text()', x))[1]::varchar(11)                             				as KodAdr
				,to_timestamp((xpath('RR_PATIENT/DAT_PROV[1]/text()', x))[1]::varchar(10), 'YYYY.MM.DD')     			as DAT_PROV
				,(xpath('RR_PATIENT/BirthSurname[1]/text()', x))[1]::varchar(40)                             			as BirthSurname
				,(xpath('RR_PATIENT/LpuFirst[1]/text()', x))[1]::varchar(6)                             				as LpuFirst
				,(xpath('RR_PATIENT/SOST_R[1]/text()', x))[1]::text::int                             					as SOST_R
				,to_timestamp((xpath('RR_PATIENT/DocIssueDate[1]/text()', x))[1]::varchar(10), 'YYYY.MM.DD')			as DocIssueDate
				,(xpath('RR_PATIENT/Enp[1]/text()', x))[1]::varchar(16)                             					as Enp
				,(xpath('RR_PATIENT/DocIssueOrg[1]/text()', x))[1]::varchar(256)                             			as DocIssueOrg
				,(xpath('RR_PATIENT/RealFiasAddress[1]/text()', x))[1]::varchar(36)                             		as RealFiasAddress
				,(xpath('RR_PATIENT/RealFiasHome[1]/text()', x))[1]::varchar(36)                             			as RealFiasHome
				,(xpath('RR_PATIENT/RealCity[1]/text()', x))[1]::varchar(100)                             			as RealCity
				,(xpath('RR_PATIENT/RealCityPrefix[1]/text()', x))[1]::varchar(10)                             		as RealCityPrefix
				,(xpath('RR_PATIENT/RealStreet[1]/text()', x))[1]::varchar(100)                             			as RealStreet
				,(xpath('RR_PATIENT/RealStreetPrefix[1]/text()', x))[1]::varchar(10)                             		as RealStreetPrefix
				,(xpath('RR_PATIENT/RealHouse[1]/text()', x))[1]::varchar(100)                             			as RealHouse
				,(xpath('RR_PATIENT/RealBuild[1]/text()', x))[1]::varchar(100)                             			as RealBuild
				,(xpath('RR_PATIENT/RealStruc[1]/text()', x))[1]::varchar(100)                             			as RealStruc
				,(xpath('RR_PATIENT/RealRoom[1]/text()', x))[1]::varchar(100)                             			as RealRoom
				,to_timestamp((xpath('RR_PATIENT/RealLiveStartDate[1]/text()', x))[1]::varchar(10), 'YYYY.MM.DD')		as RealLiveStartDate
				,to_timestamp((xpath('RR_PATIENT/RealLiveEndDate[1]/text()', x))[1]::varchar(10), 'YYYY.MM.DD')		as RealLiveEndDate
				,(xpath('RR_PATIENT/LegalFiasAddress[1]/text()', x))[1]::varchar(36)                             		as LegalFiasAddress
				,(xpath('RR_PATIENT/LegalFiasHome[1]/text()', x))[1]::varchar(36)                             		as LegalFiasHome
				,(xpath('RR_PATIENT/LegalCity[1]/text()', x))[1]::varchar(100)                             			as LegalCity
				,(xpath('RR_PATIENT/LegalCityPrefix[1]/text()', x))[1]::varchar(10)                             		as LegalCityPrefix
				,(xpath('RR_PATIENT/LegalStreet[1]/text()', x))[1]::varchar(100)                             			as LegalStreet
				,(xpath('RR_PATIENT/LegalStreetPrefix[1]/text()', x))[1]::varchar(10)                             	as LegalStreetPrefix
				,(xpath('RR_PATIENT/LegalHouse[1]/text()', x))[1]::varchar(100)                             			as LegalHouse
				,(xpath('RR_PATIENT/LegalBuild[1]/text()', x))[1]::varchar(100)                             			as LegalBuild
				,(xpath('RR_PATIENT/LegalStruc[1]/text()', x))[1]::varchar(100)                             			as LegalStruc
				,(xpath('RR_PATIENT/LegalRoom[1]/text()', x))[1]::varchar(100)                             			as LegalRoom
				,to_timestamp((xpath('RR_PATIENT/LegalLiveStartDate[1]/text()', x))[1]::varchar(10), 'YYYY.MM.DD')	as LegalLiveStartDate
				,to_timestamp((xpath('RR_PATIENT/LegalLiveEndDate[1]/text()', x))[1]::varchar(10), 'YYYY.MM.DD')		as LegalLiveEndDate
				,(xpath('RR_PATIENT/CitizenShip[1]/text()', x))[1]::text::int                             			as CitizenShip
				,(xpath('RR_PATIENT/EhrRus5o5e88vhc6[1]/text()', x))[1]::varchar(52)                             			as EhrRus5o5e88vhc6
				,(xpath('RR_PATIENT/EhrRus5o5e88vhc6Ver[1]/text()', x))[1]::varchar(52)                             			as EhrRus5o5e88vhc6Ver
		    from 
			(
				select unnest(xpath('RR_PATIENT/body[1]/RR_PATIENTS/RR_PATIENT', "Xml"::xml)) as x
			) RR_PATIENT
		    where _OperationType = 'FTLoad';

		    -- Обработка операции
		    if (_OperationType = 'FTLoad')
		    then
		        delete from APS.T_RR_PATIENT_BENEFIT;
				delete from APS.T_RR_PATIENT;

		        -- Загружаем новые
		        INSERT INTO APS.T_RR_PATIENT
		        (
					 RrPatientID
					,SNILS
					,DocSeries
					,DocNumber
					,FatherName
					,FirstName
					,LastName
					,Birthday
					,Sex
					,DateBegin
					,DateEnd
					,LastEditDate
					,LastEditLpu
					,ExcludeReason
					,Kod
					,KodAdr
					,DAT_PROV
					,BirthSurname
					,LpuMainFirst
					,SOST_R
					,DocType
					,DocIssueDate
					,DocIssueOrg
					,Enp
					,RealFiasAddress
					,RealFiasHome
					,RealCity
					,RealCityPrefix
					,RealStreet
					,RealStreetPrefix
					,RealHouse
					,RealBuild
					,RealStruc
					,RealRoom
					,RealLiveStartDate
					,RealLiveEndDate
					,LegalFiasAddress
					,LegalFiasHome
					,LegalCity
					,LegalCityPrefix
					,LegalStreet
					,LegalStreetPrefix
					,LegalHouse
					,LegalBuild
					,LegalStruc
					,LegalRoom
					,LegalLiveStartDate
					,LegalLiveEndDate
					,CitizenShip
					,EhrRus5o5e88vhc6
				)
		        SELECT
					 nrp.RrPatientID
					,nrp.Snils
					,nrp.DocSeries
					,nrp.DocNumber
					,nrp.FatherName
					,nrp.FirstName
					,nrp.LastName
					,nrp.Birthday
					,eSex.EnumID
					,nrp.DateBegin
					,nrp.DateEnd
					,nrp.LastEditDate
					,nrp.Lpu
					,nrp.ExcludeReason
					,nrp.Kod
					,nrp.KodAdr
					,nrp.DAT_PROV
					,nrp.BirthSurname
					,nrp.LpuFirst
					,nrp.SOST_R
					,e.EnumID
					,nrp.DocIssueDate
					,nrp.DocIssueOrg
					,nrp.Enp
					,RealFiasAddress,RealFiasHome,RealCity,RealCityPrefix,RealStreet,RealStreetPrefix,RealHouse,RealBuild,RealStruc,RealRoom,RealLiveStartDate,RealLiveEndDate
					,LegalFiasAddress,LegalFiasHome,LegalCity,LegalCityPrefix,LegalStreet,LegalStreetPrefix,LegalHouse,LegalBuild,LegalStruc,LegalRoom,LegalLiveStartDate,LegalLiveEndDate
					,cs.EhrRusID
					,doc.EhrRusID
		        from Tmp_RR_PATIENTS nrp
		            left join VCLib.T_ENUMS eSex on eSex.Code = nrp.Sex and eSex.EnumTypeID = 103
					left join vclib.T_ENUMS e on e.EnumTypeID = 105 and e.Code = nrp.TIPD
			        left join EHR.T_EHR_RUS_O00015 cs on cs.CODE = nrp.CitizenShip
			        left join lateral (
			        	select doc.EhrRusID 
			        	from EHR.T_EHR_RUS_5O5E88VHC6 doc
							LEFT JOIN EHR.T_EHR_RUS_REF_BOOKS RRB ON RRB.EhrRusRefBooksID = doc.EhrRusRefBooks
			        	where doc.CodeEHR = nrp.EhrRus5o5e88vhc6 and RRB.CURRVERSION = nrp.EhrRus5o5e88vhc6Ver limit 1
			        ) doc on true;
		    end if;
		    DROP TABLE if exists Tmp_RR_PATIENTS;
		end if;
	end;
End;
$$;
