DROP PROCEDURE IF EXISTS sp_RR_Direction.pg;
-- =============================================
-- Author:      Vitacore (с)
-- Create date: 2024-06-21
-- Description: Загрузка информации по направлениям в региональный регистр RDS
-- =============================================
DROP PROCEDURE IF EXISTS APS.sp_RR_Direction;

CREATE OR REPLACE PROCEDURE APS.sp_RR_Direction
(
	"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);
		    Result text;
    begin
        "dateTime" := coalesce("dateTime", current_timestamp);    
    -- Получаем тип операции
    _OperationType := (xpath('/RR_DIRECTION/header[1]/TYPE[1]/text()', "Xml"::xml))[1]::varchar(36);

    drop table if exists Tmp_RR_DIRECTION;

    create temp table Tmp_RR_DIRECTION
    as
    select 
		  (xpath('RR_DIRECTION/RrPatientID[1]/text()', x))[1]::text::int                   						as RrPatientID
          ,(xpath('RR_DIRECTION/Snils[1]/text()', x))[1]::varchar(14)                             					as Snils
		  ,(xpath('RR_DIRECTION/DocSeries[1]/text()', x))[1]::varchar(16)                             			as DocSeries
		  ,(xpath('RR_DIRECTION/DocNumber[1]/text()', x))[1]::varchar(16)                             			as DocNumber
		  ,(xpath('RR_DIRECTION/LastName[1]/text()', x))[1]::varchar(40)                             				as LastName
		  ,(xpath('RR_DIRECTION/FirstName[1]/text()', x))[1]::varchar(40)                             			as FirstName
		  ,(xpath('RR_DIRECTION/FatherName[1]/text()', x))[1]::varchar(40)                             			as FatherName		
		  ,to_timestamp((xpath('RR_DIRECTION/Birthday[1]/text()', x))[1]::varchar(10), 'DD.MM.YYYY')     			as Birthday
		  ,to_timestamp((xpath('RR_DIRECTION/DateBegin[1]/text()', x))[1]::varchar(10), 'DD.MM.YYYY')				as DateBegin		
		  ,to_timestamp((xpath('RR_DIRECTION/DateEnd[1]/text()', x))[1]::varchar(10), 'DD.MM.YYYY')     			as DateEnd
		  ,to_timestamp((xpath('RR_DIRECTION/LastEditDate[1]/text()', x))[1]::varchar(10), 'DD.MM.YYYY')			as LastEditDate
		  ,(xpath('RR_DIRECTION/Sex[1]/text()', x))[1]::text::int                                  				as Sex
		  ,(xpath('RR_DIRECTION/Lpu[1]/text()', x))[1]::varchar(6)                             					as Lpu
		  ,(xpath('RR_DIRECTION/ExcludeReason[1]/text()', x))[1]::text::int										as ExcludeReason
		  ,(xpath('RR_DIRECTION/DocType[1]/text()', x))[1]::text::int                                  			as TIPD
		  ,(xpath('RR_DIRECTION/Kod[1]/text()', x))[1]::varchar(32)                             					as Kod
		  ,(xpath('RR_DIRECTION/KodAdr[1]/text()', x))[1]::varchar(11)                             				as KodAdr
		  ,to_timestamp((xpath('RR_DIRECTION/DAT_PROV[1]/text()', x))[1]::varchar(10), 'DD.MM.YYYY')     			as DAT_PROV
		  ,(xpath('RR_DIRECTION/BirthSurname[1]/text()', x))[1]::varchar(40)                             			as BirthSurname
		  ,(xpath('RR_DIRECTION/LpuFirst[1]/text()', x))[1]::varchar(6)                             				as LpuFirst
		  ,(xpath('RR_DIRECTION/SOST_R[1]/text()', x))[1]::text::int                             					as SOST_R
		  ,to_timestamp((xpath('RR_DIRECTION/DocIssueDate[1]/text()', x))[1]::varchar(10), 'DD.MM.YYYY')			as DocIssueDate
		  ,(xpath('RR_DIRECTION/Enp[1]/text()', x))[1]::varchar(16)                             					as Enp
		  ,(xpath('RR_DIRECTION/DocIssueOrg[1]/text()', x))[1]::varchar(256)                             			as DocIssueOrg
		  ,(xpath('RR_DIRECTION/RealFiasAddress[1]/text()', x))[1]::varchar(36)                             		as RealFiasAddress
		  ,(xpath('RR_DIRECTION/RealFiasHome[1]/text()', x))[1]::varchar(36)                             			as RealFiasHome
		  ,(xpath('RR_DIRECTION/RealCity[1]/text()', x))[1]::varchar(100)                             			as RealCity
		  ,(xpath('RR_DIRECTION/RealCityPrefix[1]/text()', x))[1]::varchar(10)                             		as RealCityPrefix
		  ,(xpath('RR_DIRECTION/RealStreet[1]/text()', x))[1]::varchar(100)                             			as RealStreet
		  ,(xpath('RR_DIRECTION/RealStreetPrefix[1]/text()', x))[1]::varchar(10)                             		as RealStreetPrefix
		  ,(xpath('RR_DIRECTION/RealHouse[1]/text()', x))[1]::varchar(100)                             			as RealHouse
		  ,(xpath('RR_DIRECTION/RealBuild[1]/text()', x))[1]::varchar(100)                             			as RealBuild
		  ,(xpath('RR_DIRECTION/RealStruc[1]/text()', x))[1]::varchar(100)                             			as RealStruc
		  ,(xpath('RR_DIRECTION/RealRoom[1]/text()', x))[1]::varchar(100)                             			as RealRoom
		  ,to_timestamp((xpath('RR_DIRECTION/RealLiveStartDate[1]/text()', x))[1]::varchar(10), 'DD.MM.YYYY')		as RealLiveStartDate
		  ,to_timestamp((xpath('RR_DIRECTION/RealLiveEndDate[1]/text()', x))[1]::varchar(10), 'DD.MM.YYYY')		as RealLiveEndDate
		  ,(xpath('RR_DIRECTION/LegalFiasAddress[1]/text()', x))[1]::varchar(36)                             		as LegalFiasAddress
		  ,(xpath('RR_DIRECTION/LegalFiasHome[1]/text()', x))[1]::varchar(36)                             		as LegalFiasHome
		  ,(xpath('RR_DIRECTION/LegalCity[1]/text()', x))[1]::varchar(100)                             			as LegalCity
		  ,(xpath('RR_DIRECTION/LegalCityPrefix[1]/text()', x))[1]::varchar(10)                             		as LegalCityPrefix
		  ,(xpath('RR_DIRECTION/LegalStreet[1]/text()', x))[1]::varchar(100)                             			as LegalStreet
		  ,(xpath('RR_DIRECTION/LegalStreetPrefix[1]/text()', x))[1]::varchar(10)                             	as LegalStreetPrefix
		  ,(xpath('RR_DIRECTION/LegalHouse[1]/text()', x))[1]::varchar(100)                             			as LegalHouse
		  ,(xpath('RR_DIRECTION/LegalBuild[1]/text()', x))[1]::varchar(100)                             			as LegalBuild
		  ,(xpath('RR_DIRECTION/LegalStruc[1]/text()', x))[1]::varchar(100)                             			as LegalStruc
		  ,(xpath('RR_DIRECTION/LegalRoom[1]/text()', x))[1]::varchar(100)                             			as LegalRoom
		  ,to_timestamp((xpath('RR_DIRECTION/LegalLiveStartDate[1]/text()', x))[1]::varchar(10), 'DD.MM.YYYY')	as LegalLiveStartDate
		  ,to_timestamp((xpath('RR_DIRECTION/LegalLiveEndDate[1]/text()', x))[1]::varchar(10), 'DD.MM.YYYY')		as LegalLiveEndDate
		  ,(xpath('RR_DIRECTION/CitizenShip[1]/text()', x))[1]::text::int                             			as CitizenShip
		  ,eh1.EhrRusID																							as EhrRus5o5e88vhc6
    from 
	(
		select unnest(xpath('RR_DIRECTION/body[1]/RR_DIRECTION', "Xml"::xml)) as x
	) RR_DIRECTION
	left join lateral 
	(
		select n1.EhrRusID
		from EHR.T_EHR_RUS_5O5E88VHC6 n1
			left join EHR.T_EHR_RUS_REF_BOOKS RRB ON RRB.EhrRusRefBooksID = n1.EhrRusRefBooks
		where n1.CodeEHR = (xpath('RR_DIRECTION/EhrRus5o5e88vhc6[1]/text()', x))[1]::varchar(255) 
			and 
			RRB.CURRVERSION = (xpath('RR_DIRECTION/EhrRus5o5e88vhc6Ver[1]/text()', x))[1]::varchar(255) limit 1
	) eh1 on true;

    -- Generate table #RR_DIRECTION_BENEFIT
	create temp table Tmp_RR_DIRECTION_BENEFIT
	as
    select	   
		(xpath('RR_DIRECTION_BENEFIT/RrPatient[1]/text()', x))[1]::text::int		as RrPatient
		,to_timestamp((xpath('RR_DIRECTION_BENEFIT/DiagDate[1]/text()', x))[1]::varchar(10), 'DD.MM.YYYY')		as DiagDate
		,(xpath('RR_DIRECTION_BENEFIT/Document[1]/text()', x))[1]::varchar(16)                             		as Document
		,to_timestamp((xpath('RR_DIRECTION_BENEFIT/DocumentDate[1]/text()', x))[1]::varchar(10), 'DD.MM.YYYY')	as DocumentDate
		,(xpath('RR_DIRECTION_BENEFIT/Dop[1]/text()', x))[1]::varchar(1)                             			as Dop
		,(xpath('RR_DIRECTION_BENEFIT/Mkb[1]/text()', x))[1]::varchar(8)                             			as Mkb
		,to_timestamp((xpath('RR_DIRECTION_BENEFIT/BeginDate[1]/text()', x))[1]::varchar(10), 'DD.MM.YYYY')		as BeginDate
		,to_timestamp((xpath('RR_DIRECTION_BENEFIT/EndDate[1]/text()', x))[1]::varchar(10), 'DD.MM.YYYY')		as EndDate
		,(xpath('RR_DIRECTION_BENEFIT/BenefitCode[1]/text()', x))[1]::varchar(255)                             	as BenefitCode
		,(xpath('RR_DIRECTION_BENEFIT/BenefitViews[1]/text()', x))[1]::text::int									as BenefitViews
		,(xpath('RR_DIRECTION_BENEFIT/Lpu[1]/text()', x))[1]::varchar(6)                             			as Lpu
		,to_timestamp((xpath('RR_DIRECTION_BENEFIT/LastEditDate[1]/text()', x))[1]::varchar(10), 'DD.MM.YYYY')	as LastEditDate
		,(xpath('RR_DIRECTION_BENEFIT/Post[1]/text()', x))[1]::varchar(10)                             			as Post
		,(xpath('RR_DIRECTION_BENEFIT/DivisionManager[1]/text()', x))[1]::varchar(10)                           as DivisionManager
    
	from 
	(
		select unnest(xpath('RR_DIRECTION/body[1]/RR_DIRECTION/RR_DIRECTION_BENEFIT', "Xml"::xml)) as x
	) RR_DIRECTION_BENEFIT;

	delete from APS.T_RR_PATIENT_BENEFIT pb
	using Tmp_RR_DIRECTION r 
	where r.RrPatientID = pb.RrPatient;

    delete from APS.T_RR_PATIENT p
	using Tmp_RR_DIRECTION r 
	where r.RrPatientID = p.RrPatientID;

    INSERT INTO APS.T_RR_PATIENT
    (
		 RrPatientID
		,FatherName
		,FirstName
		,LastName
		,Birthday
		,Sex
		,SNILS
		,DateBegin
		,DateEnd
		,LastEditDate
		,LastEditLpu
		,ExcludeReason
		,DocSeries
		,DocNumber
		,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
		 pd.RrPatientID
		,pd.FatherName
		,pd.FirstName
		,pd.LastName
		,pd.Birthday
		,eSex.EnumID
		,pd.SNILS
		,pd.LastEditDate
		,null
		,@dateTime
		,pd.LPU
		,null
		,pd.DocSeries
		,pd.DocNumber
		,pd.Kod
		,pd.KodAdr
		,pd.DAT_PROV
		,pd.BirthSurname
		,pd.LpuFirst
		,pd.SOST_R
		,eDoc.EnumID
		,pd.DocIssueDate
		,pd.DocIssueOrg
		,pd.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_DIRECTION pd
		left join VCLib.T_ENUMS eSex on eSex.EnumTypeID = 103 and eSex.Code = pd.Sex
		left join VCLib.T_ENUMS eDoc on eDoc.EnumTypeID = 105 and eDoc.Code = pd.TIPD
		left join EHR.T_EHR_RUS_O00015 cs on cs.CODE = pd.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 = pd.EhrRus5o5e88vhc6 and RRB.CURRVERSION = pd.EhrRus5o5e88vhc6Ver limit 1
		) doc on true;

		-- Загружаем новые
       INSERT INTO APS.T_RR_PATIENT_BENEFIT
                  (RrPatientBenefitID, DiagDate, Document, DocumentDate, Dop, Mkb, Benefits, RrPatient, BeginDate, EndDate, LastEditDate, LastEditLpu)
       SELECT
                  gen_random_uuid()
                  ,npb.DiagDate
                  ,npb.Document
                  ,npb.DocumentDate
                  ,npb.Dop
                  ,npb.Mkb
                  ,b.BenefitsID
                  ,npb.RrPatient
                  ,npb.BeginDate
                  ,npb.EndDate
                  ,npb.LastEditDate
                  ,npb.Lpu
       from RR_DIRECTION_BENEFIT npb
           join VCLib.T_ENUMS eBv on eBv.Code = npb.BenefitViews and eBv.EnumTypeID = 190
           join APS.T_BENEFITS b on b.BenefitCode = npb.BenefitCode and b.BenefitViews = eBv.EnumID;
    -- Drop tables
    DROP TABLE Tmp_RR_DIRECTION;
	end;
End;
$$;
