-- =============================================
-- Author:      Vitacore (с)
-- Create date: 2024-06-21
-- Description: Загрузка регистра БСК.
-- =============================================
DROP PROCEDURE IF EXISTS DBO.sp_getbskregister;

CREATE OR REPLACE PROCEDURE APS.sp_getbskregister
(
    "Xml" xml = null    
,	"ID" uuid DEFAULT NULL::uuid
,   "QueueName" varchar(100) = 'RDSSync'
,   "DateTime" TimeStamp = null
,	"User" uuid DEFAULT NULL::uuid
)
    language plpgsql
as
$$
 Begin
    declare 
    _OperationType varchar(36);
    Begin
        IF ("Xml" is not null)
        then
	    	"DateTime" := coalesce("DateTime", Current_TimeStamp::TIMESTAMP);
            
            -- Получаем тип операции		
			select into
				_OperationType
				(xpath('/BSK_REGISTER/header[1]/TYPE[1]/text()',"Xml"))[1]::varchar(36);

            -- Generate table Tmp_BSK_REGISTER
			Drop Table If Exists Tmp_BSK_REGISTER;
            Create Temp Table TmpDLO_ACCESSED_POSTS
            as
            select (xpath('(PATIENT/RegisterID[1]/text()', x))[1]::text::int                                    RegisterID
                  ,(xpath('(PATIENT/LastName[1]/text()', x))[1]::varchar(40)                                    LastName
                  ,(xpath('(PATIENT/FirstName[1]/text()', x))[1]::varchar(40)                                   FirstName
                  ,(xpath('(PATIENT/FatherName[1]/text()', x))[1]::varchar(40)                                  FatherName
                  ,to_timestamp((xpath('(PATIENT/Birthday[1]/text()', x))[1]::text, 'DD.MM.YYYY')               Birthday
                  ,(xpath('(PATIENT/Kod[1]/text()', x))[1]::varchar(32)		                                    Kod
                  ,(xpath('(PATIENT/DocSeries[1]/text()', x))[1]::varchar(8)                                    DocSeries
                  ,(xpath('(PATIENT/DocNumber[1]/text()', x))[1]::varchar(8)                                    DocNumber
                  ,(xpath('(PATIENT/Sex[1]/text()', x))[1]::text::int                                           Sex
                  ,(xpath('(PATIENT/Snils[1]/text()', x))[1]::varchar(14)                                       Snils
                  ,to_timestamp((xpath('(PATIENT/DateBegin[1]/text()', x))[1]::text, 'DD.MM.YYYY')              DateBegin
                  ,to_timestamp((xpath('(PATIENT/DateEnd[1]/text()', x))[1]::text, 'DD.MM.YYYY')                DateEnd
                  ,to_timestamp((xpath('(PATIENT/LastEditDate[1]/text()', x))[1]::text, 'DD.MM.YYYY')           LastEditDate
                  ,(xpath('(PATIENT/Lpu[1]/text()', x))[1]::varchar(6)                                          Lpu
                  ,(xpath('(PATIENT/ExcludeReason[1]/text()', x))[1]::text::int                                 ExcludeReason
                  ,(xpath('(PATIENT/DocType[1]/text()', x))[1]::text::int                                       DocType
                  ,(xpath('(PATIENT/PatientId[1]/text()', x))[1]::text::UUID                                    PatientId
                  ,(xpath('(PATIENT/BirthSurname[1]/text()', x))[1]::varchar(40)                                BirthSurname
                  ,to_timestamp((xpath('(PATIENT/DocIssueDate[1]/text()', x))[1]::text, 'DD.MM.YYYY')           DocIssueDate
                  ,(xpath('(PATIENT/Enp[1]/text()', x))[1]::varchar(16)                                         Enp
                  ,(xpath('(PATIENT/DocIssueOrg[1]/text()', x))[1]::varchar(256)                                DocIssueOrg
                  ,(xpath('(PATIENT/RealFiasAddress[1]/text()', x))[1]::varchar(36)                             RealFiasAddress
                  ,(xpath('(PATIENT/RealFiasHome[1]/text()', x))[1]::varchar(36)                                RealFiasHome
                  ,(xpath('(PATIENT/RealCity[1]/text()', x))[1]::varchar(100)                                   RealCity
                  ,(xpath('(PATIENT/RealCityPrefix[1]/text()', x))[1]::varchar(10)                              RealCityPrefix
                  ,(xpath('(PATIENT/RealStreet[1]/text()', x))[1]::varchar(100)                                 RealStreet
                  ,(xpath('(PATIENT/RealStreetPrefix[1]/text()', x))[1]::varchar(10)                            RealStreetPrefix
                  ,(xpath('(PATIENT/RealHouse[1]/text()', x))[1]::varchar(100)                                  RealHouse
                  ,(xpath('(PATIENT/RealBuild[1]/text()', x))[1]::varchar(100)                                  RealBuild
                  ,(xpath('(PATIENT/RealStruc[1]/text()', x))[1]::varchar(100)                                  RealStruc
                  ,(xpath('(PATIENT/RealRoom[1]/text()', x))[1]::varchar(100)                                   RealRoom
                  ,to_timestamp((xpath('(PATIENT/RealLiveStartDate[1]/text()', x))[1]::text, 'DD.MM.YYYY')      RealLiveStartDate
                  ,to_timestamp((xpath('(PATIENT/RealLiveEndDate[1]/text()', x))[1]::text, 'DD.MM.YYYY')        RealLiveEndDate
                  ,(xpath('(PATIENT/LegalFiasAddress[1]/text()', x))[1]::varchar(100)                           LegalFiasAddress
                  ,(xpath('(PATIENT/LegalFiasHome[1]/text()', x))[1]::varchar(100)                              LegalFiasHome
                  ,(xpath('(PATIENT/LegalCity[1]/text()', x))[1]::varchar(100)                                  LegalCity
                  ,(xpath('(PATIENT/LegalCityPrefix[1]/text()', x))[1]::varchar(100)                            LegalCityPrefix
                  ,(xpath('(PATIENT/LegalStreet[1]/text()', x))[1]::varchar(100)                                LegalStreet
                  ,(xpath('(PATIENT/LegalStreetPrefix[1]/text()', x))[1]::varchar(100)                          LegalStreetPrefix
                  ,(xpath('(PATIENT/LegalHouse[1]/text()', x))[1]::varchar(100)                                 LegalHouse
                  ,(xpath('(PATIENT/LegalBuild[1]/text()', x))[1]::varchar(100)                                 LegalBuild
                  ,(xpath('(PATIENT/LegalStruc[1]/text()', x))[1]::varchar(100)                                 LegalStruc
                  ,(xpath('(PATIENT/LegalRoom[1]/text()', x))[1]::varchar(100)                                  LegalRoom
                  ,to_timestamp((xpath('(PATIENT/LegalLiveStartDate[1]/text()', x))[1]::text, 'DD.MM.YYYY')     LegalLiveStartDate
                  ,to_timestamp((xpath('(PATIENT/LegalLiveEndDate[1]/text()', x))[1]::text, 'DD.MM.YYYY')       LegalLiveEndDate
                  ,(xpath('(PATIENT/CitizenShip[1]/text()', x))[1]::text::int                                   CitizenShip
                  ,eh1.EhrRusID                                                                                 EhrRus5o5e88vhc6
            from
    		(
    		    select	unnest(xpath('BSK_REGISTER/body[1]/PATIENT', "Xml")) as x
    		) PATIENT
            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('PATIENT/EhrRus5o5e88vhc6[1]/text()', x))[1]::varchar(255) and RRB.CURRVERSION = (xpath('PATIENT/EhrRus5o5e88vhc6Ver[1]/text()', x))[1]::varchar(255) 
                limit 1
		    ) eh1 on true;

	    	
            -- Generate table Tmp_BSK_BENEFITS
			Drop Table If Exists Tmp_BSK_BENEFITS;
            Create Temp Table Tmp_BSK_BENEFITS
            as
            select (xpath('(BENEFITS/RegisterID[1]/text()', x))[1]::text::int									RegisterID
                  ,(xpath('(BENEFITS/BenID[1]/text()', x))[1]::text::UUID									    BenID
                  ,to_timestamp((xpath('(BENEFITS/DiagDate[1]/text()', x))[1]::text, 'DD.MM.YYYY')			    DiagDate
                  ,(xpath('(BENEFITS/Document[1]/text()', x))[1]::varchar(16)									Document
                  ,to_timestamp((xpath('(BENEFITS/DocumentDate[1]/text()', x))[1]::text, 'DD.MM.YYYY')          DocumentDate
                  ,(xpath('(BENEFITS/Mkb[1]/text()', x))[1]::varchar(8)										    Mkb
                  ,(xpath('(BENEFITS/BenefitCode[1]/text()', x))[1]::varchar(255)								BenefitCode
                  ,(xpath('(BENEFITS/BenefitViews[1]/text()', x))[1]::text::int								    BenefitViews
                  ,to_timestamp((xpath('(BENEFITS/BeginDate[1]/text()', x))[1]::text, 'DD.MM.YYYY')		        BeginDate
                  ,to_timestamp((xpath('(BENEFITS/EndDate[1]/text()', x))[1]::text, 'DD.MM.YYYY')		        EndDate
                  ,(xpath('(BENEFITS/Lpu[1]/text()', x))[1]::varchar(6)										    Lpu
                  ,(xpath('(BENEFITS/ServiceCode[1]/text()', x))[1]::varchar(20)								ServiceCode
                  ,(xpath('(BENEFITS/ServiceName[1]/text()', x))[1]::varchar(255)								ServiceName
                  ,(xpath('(BENEFITS/ExclusionReason[1]/text()', x))[1]::text::int								ExclusionReason
                  ,to_timestamp((xpath('(BENEFITS/EstimatedDDate[1]/text()', x))[1]::text, 'DD.MM.YYYY')        EstimatedDDate
                  ,to_timestamp((xpath('(BENEFITS/DRegistrationDate[1]/text()', x))[1]::text, 'DD.MM.YYYY')     DRegistrationDate
                  ,(xpath('(BENEFITS/Place[1]/text()', x))[1]::text::int										Place
                  ,(xpath('(BENEFITS/LpuTo[1]/text()', x))[1]::varchar(6)										LpuTo
                  ,to_timestamp((xpath('(BENEFITS/CloseDate[1]/text()', x))[1]::text, 'DD.MM.YYYY')			    CloseDate
            from
    		(
    		    select	unnest(xpath('BSK_REGISTER/body[1]/PATIENT/BENEFITS', "Xml")) as x
    		) BENEFITS;	

	    	-- Generate table Tmp_BSK_MEDICAMENTS
			Drop Table If Exists Tmp_BSK_MEDICAMENTS;
            Create Temp Table Tmp_BSK_MEDICAMENTS
            as
            select (xpath('(MEDICAMENTS/BenID[1]/text()', x))[1]::text::UUID							        BenID
                  ,(xpath('(MEDICAMENTS/Mnn[1]/text()', x))[1]::text::int									    Mnn
                  ,to_timestamp((xpath('(MEDICAMENTS/DateStart[1]/text()', x))[1]::text, 'DD.MM.YYYY')          DateStart
                  ,to_timestamp((xpath('(MEDICAMENTS/EndDate[1]/text()', x))[1]::text, 'DD.MM.YYYY')            EndDate
                  ,(xpath('(MEDICAMENTS/MedicationForms[1]/text()', x))[1]::text::UUID				            MedicationForms
                  ,(xpath('(MEDICAMENTS/Regiment[1]/text()', x))[1]::varchar(255)                               Regiment
                  ,(xpath('(MEDICAMENTS/Dose[1]/text()', x))[1]::decimal(31,10)                                 Dose
                  ,(xpath('(MEDICAMENTS/Prepid[1]/text()', x))[1]::text::int								    Prepid
                  ,(xpath('(MEDICAMENTS/DayliCoeff[1]/text()', x))[1]::decimal(31,10)                           DayliCoeff
                  ,(xpath('(MEDICAMENTS/MeasureUnit[1]/text()', x))[1]::varchar(255)						    MeasureUnit
                  ,(xpath('(MEDICAMENTS/MeasureUnitType[1]/text()', x))[1]::text::int						    MeasureUnitType
                  ,(xpath('(MEDICAMENTS/SingleDose[1]/text()', x))[1]::decimal(31,10)                           SingleDose
                  ,(xpath('(MEDICAMENTS/Multiplicity[1]/text()', x))[1]::text::int							    Multiplicity
                  ,(xpath('(MEDICAMENTS/DoseView[1]/text()', x))[1]::varchar(255)							    DoseView
                  ,(xpath('(MEDICAMENTS/DoseView[1]/text()', x))[1]::text::int								    DoseViewType
            from
    			(
    				select	unnest(xpath('BSK_REGISTER/body[0]/PATIENT/BENEFITS/MEDICAMENTS', "Xml")) as x
    			) MEDICAMENTS;	
                
            -- Обработка операции
            if _OperationType = 'FTLoad' or (select count(*) from Tmp_BSK_REGISTER) > 1
            then
                delete from APS.T_BSK_REGISTER_MEDICAMENTS;
			    delete from APS.T_BSK_REGISTER_DATA;
			    delete from APS.T_BSK_REGISTER;
            else
			    delete from APS.T_BSK_REGISTER_MEDICAMENTS meds
			    	using APS.T_BSK_REGISTER_DATA ben, APS.T_BSK_REGISTER rr, Tmp_BSK_REGISTER r
                    where
                        ben.BskRegisterDataID = meds.BskRegisterData
                        and rr.BskRegisterID = ben.BskRegister
                        and r.RegisterID = rr.PAC_ID;

			    delete from APS.T_BSK_REGISTER_DATA ben
                    using APS.T_BSK_REGISTER rr, Tmp_BSK_REGISTER r 
                    where 
                        rr.BskRegisterID = ben.BskRegister and
                        r.RegisterID = rr.PAC_ID;

			    delete from APS.T_BSK_REGISTER rr
                    using Tmp_BSK_REGISTER r 
                    where 
                        r.RegisterID = rr.PAC_ID;
            end if;
            
            INSERT INTO APS.T_BSK_REGISTER
			(
                BskRegisterID
			    ,ExcludeReason
			    ,LastEditDate
			    ,LastName
			    ,FirstName
			    ,FatherName
			    ,Birthday
			    ,Kod
			    ,DocSeries
			    ,DocNumber
			    ,PAC_ID
			    ,Sex
			    ,SNILS
			    ,DocType
			    ,LpuMain
			    ,DateBegin
			    ,DateEnd
			    ,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	
                gen_random_uuid()
				,ExcludeReason
				,LastEditDate
				,LastName
				,FirstName
				,FatherName
				,Birthday
				,Kod
				,DocSeries
				,DocNumber
				,RegisterID
				,eSex.EnumID
				,SNILS
				,e.EnumID
				,Lpu
				,DateBegin
				,DateEnd
				,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
				,cs.EhrRusID
				,doc.EhrRusID
			from Tmp_BSK_REGISTER r
				left join vclib.T_ENUMS e on e.EnumTypeID = 105 and e.Code = r.DocType
				left join VCLib.T_ENUMS eSex on eSex.EnumTypeID = 103 and eSex.Code = r.Sex
				left join EHR.T_EHR_RUS_O00015 cs on cs.CODE = r.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 = r.EhrRus5o5e88vhc6 and RRB.CURRVERSION = r.EhrRus5o5e88vhc6Ver limit 1
				) doc on true;

			INSERT INTO APS.T_BSK_REGISTER_DATA
			(
				BskRegisterDataID
				,BskRegister
				,ServiceCode
				,ServiceName
				,DiagDate
				,Document
				,DocumentDate
				,Mkb
				,Benefits
				,BeginDate
				,EndDate
				,ExclusionReason
				,EstimatedDDate
				,DRegistrationDate
				,Place
				,LpuMainTo
				,CloseDate
				,LpuMain
				,LastEditDate
			)
			select		
				b.BenID
				,r.BskRegisterID
				,ServiceCode
				,ServiceName
				,DiagDate
				,Document
				,DocumentDate
				,Mkb
				,bb.BenefitsID
				,BeginDate
				,EndDate
			    ,b.ExclusionReason
			    ,b.EstimatedDDate
			    ,b.DRegistrationDate
			    ,b.Place
			    ,b.LpuTo
				,b.CloseDate
				,b.Lpu
				,current_date
			from Tmp_BSK_BENEFITS b
				join APS.T_BSK_REGISTER r on r.PAC_ID = b.RegisterID
				left join VCLib.T_ENUMS e on e.EnumTypeID = 190 and e.Code = b.BenefitViews
				left join APS.T_BENEFITS bb on bb.BenefitCode = b.BenefitCode and bb.BenefitViews = e.EnumID;

			
			INSERT INTO APS.T_BSK_REGISTER_MEDICAMENTS
		    (
				BskRegisterMedicamentsID
				,BskRegisterData
				,CMnn
				,DateStart
				,EndDate
				,MedicationForms
				,Regiment
				,Dose
				,MeasureUnit
				,Prepid
				,DayliCoeff
				,SingleDose
				,Multiplicity
				,DoseView
			)
		    select 
				gen_random_uuid()
				,meds.BenID
				,(select CMnnID 
					from PHARm.T_C_MNN 
					where OtherID = meds.Mnn limit 1)
				,meds.DateStart
				,meds.EndDate
				,meds.MedicationForms
				,meds.Regiment
				,meds.Dose
				,(select dw.Code 
					from PHARM.T_DOSE_VIEW dw 
					where dw.Name = meds.MeasureUnit and dw.ViewType = meds.MeasureUnitType limit 1)
				,meds.Prepid
				,meds.DayliCoeff
				,meds.SingleDose
				,meds.Multiplicity
				,(select dw.Code 
					from PHARM.T_DOSE_VIEW dw 
					where dw.Name = meds.DoseView and dw.ViewType = meds.DoseViewType limit 1)
		     from Tmp_BSK_MEDICAMENTS meds;

            -- Drop tables
            DROP TABLE Tmp_BSK_REGISTER;
            DROP TABLE Tmp_BSK_BENEFITS;
	    	DROP TABLE Tmp_BSK_MEDICAMENTS;
        end if;
    End;
End;
$$;