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

DROP PROCEDURE IF EXISTS APS.sp_fr_updatebeneficiaries;

CREATE OR REPLACE PROCEDURE APS.sp_fr_updatebeneficiaries
(
	"Xml" text DEFAULT null
,	"ver" int default null::int
,	"date" timestamp default null::timestamp
)
    language plpgsql
as
$$
Begin

	if "Xml" is not null
	then
		INSERT INTO APS.T_FEDERAL_REGISTER_LOAD(FederalRegisterLoadID, Users, Version, UpdateDate)
        select gen_random_uuid(), NULL, "ver", "date";

		-- Generate table #SNILS
		Drop Table If Exists TmpSNILS;
		Create Temp Table TmpSNILS
		as 
		SELECT	(xpath('/FR_RECORD[1]/Snils[1]/text()', t.x))[1]::char(14) as Snils
		from
		(
			select	unnest(xpath('/body/RECORDS/FR_RECORD', "Xml"::xml)) as x
		) t
		union 
		SELECT	(xpath('/FR_PATIENT[1]/Snils[1]/text()', t.x))[1]::char(14) as Snils
		from
		(
			select	unnest(xpath('/body/PATIENTS/FR_PATIENT', "Xml"::xml)) as x
		) t
		union
		SELECT	(xpath('/FR_DOCUMENT[1]/Snils[1]/text()', t.x))[1]::char(14) as Snils
		from
		(
			select	unnest(xpath('/body/DOCUMENTS/FR_DOCUMENT', "Xml"::xml)) as x
		) t;
		
		insert into APS.T_SNILS(Snils)
        select t.Snils 
		from TmpSNILS t
        where t.Snils not in (select s.SNILS from APS.T_SNILS s);

        -- Drop Table
        DROP TABLE if exists TmpSNILS;
            
        TRUNCATE TABLE APS.T_FR_PATIENT;

        -- Generate table #T_FR_PATIENT
		Drop Table If Exists Tmp_FR_PATIENT;
		Create Temp Table Tmp_FR_PATIENT
		as 
		SELECT	
			(xpath('/FR_PATIENT[1]/FirstName[1]/text()', t.x))[1]::varchar(255) as FirstName	
		,	(xpath('/FR_PATIENT[1]/LastName[1]/text()', t.x))[1]::varchar(255) as LastName
		,	(xpath('/FR_PATIENT[1]/FatherName[1]/text()', t.x))[1]::varchar(255) as FatherName
		,	(xpath('/FR_PATIENT[1]/Birthday[1]/text()', t.x))[1]::varchar(10) as Birthday
		,	(xpath('/FR_PATIENT[1]/Sex[1]/text()', t.x))[1]::text::int as Sex	
		,	(xpath('/FR_PATIENT[1]/Snils[1]/text()', t.x))[1]::char(14) as Snils
		from
		(
			select	unnest(xpath('/body/PATIENTS/FR_PATIENT', "Xml"::xml)) as x
		) t;
		
        INSERT INTO APS.T_FR_PATIENT(FrPatientID, Snils, FirstName, LastName, FatherName, Birthday, Sex)
        select gen_random_uuid(), fp.Snils, fp.FirstName, fp.LastName, fp.FatherName, to_timestamp(fp.Birthday, 'DD.MM.YYYY'), fp.Sex
        FROM Tmp_FR_PATIENT fp;
            
        -- Drop Table
        DROP TABLE if exists Tmp_FR_PATIENT;

        TRUNCATE TABLE APS.T_FR_RECORD;
		
		-- Generate table #T_FR_RECORD
		Drop Table If Exists Tmp_FR_RECORD;
		Create Temp Table Tmp_FR_RECORD
		as 
		SELECT	
			(xpath('/FR_RECORD[1]/Deleted[1]/text()', t.x))[1]::text::boolean as Deleted
		,	(xpath('/FR_RECORD[1]/DateBegin[1]/text()', t.x))[1]::varchar(10) as DateBegin
		,	(xpath('/FR_RECORD[1]/DateEnd[1]/text()', t.x))[1]::varchar(10) as DateEnd
		,	(xpath('/FR_RECORD[1]/NSU[1]/text()', t.x))[1]::text::boolean as NSU
		,	(xpath('/FR_RECORD[1]/DateNsuBegin[1]/text()', t.x))[1]::varchar(10) as DateNsuBegin
		,	(xpath('/FR_RECORD[1]/DateNsuEnd[1]/text()', t.x))[1]::varchar(10) as DateNsuEnd
		,	(xpath('/FR_RECORD[1]/Nsus[1]/text()', t.x))[1]::text::boolean as Nsus
		,	(xpath('/FR_RECORD[1]/Version[1]/text()', t.x))[1]::int as Version	
		,	(xpath('/FR_RECORD[1]/Snils[1]/text()', t.x))[1]::char(14) as Snils
		,	(xpath('/FR_RECORD[1]/UpdateDate[1]/text()', t.x))[1]::varchar(10) as UpdateDate
		from
		(
			select	unnest(xpath('/body/RECORDS/FR_RECORD', "Xml"::xml)) as x
		) t;
		
        INSERT INTO APS.T_FR_RECORD(FrRecordID, Snils, Deleted, DateBegin, DateEnd, NSU, DateNsuBegin, DateNsuEnd, Nsus, Version, UpdateDate)
        select gen_random_uuid(), fr.Snils, fr.Deleted, to_timestamp(fr.DateBegin, 'DD.MM.YYYY'), to_timestamp(fr.DateEnd, 'DD.MM.YYYY'), fr.NSU
		, to_timestamp(fr.DateNsuBegin, 'DD.MM.YYYY'), to_timestamp(fr.DateNsuEnd, 'DD.MM.YYYY'), fr.Nsus, fr.Version, to_timestamp(fr.UpdateDate, 'DD.MM.YYYY')
        FROM Tmp_FR_RECORD fr;

        -- Drop Table
        DROP TABLE if exists Tmp_FR_RECORD;
                            
        TRUNCATE TABLE APS.T_FR_DOCUMENT;

        -- Generate table #T_FR_DOCUMENT
		Drop Table If Exists Tmp_FR_DOCUMENT;
		Create Temp Table Tmp_FR_DOCUMENT
		as 
		SELECT	
			(xpath('/FR_DOCUMENT[1]/Name[1]/text()', t.x))[1]::varchar(255) as Name
		,	(xpath('/FR_DOCUMENT[1]/Number[1]/text()', t.x))[1]::varchar(255) as Number
		,	(xpath('/FR_DOCUMENT[1]/BenefitViews[1]/text()', t.x))[1]::text::int as BenefitViews
		,	(xpath('/FR_DOCUMENT[1]/BenefitCode[1]/text()', t.x))[1]::varchar(255) as BenefitCode		
		,	(xpath('/FR_DOCUMENT[1]/Snils[1]/text()', t.x))[1]::char(14) as Snils
		from
		(
			select	unnest(xpath('/body/DOCUMENTS/FR_DOCUMENT', "Xml"::xml)) as x
		) t;
		
        INSERT INTO APS.T_FR_DOCUMENT(FrDocumentID, Name, Number, Benefits, Snils)
        select gen_random_uuid(), dap.Name, dap.Number, b.BenefitsID, dap.Snils
        FROM Tmp_FR_DOCUMENT dap
		join VCLib.T_ENUMS eBv on eBv.EnumTypeID = 190 and ebv.Code = dap.BenefitViews
		join APS.T_BENEFITS b on b.BenefitViews = ebv.EnumID and b.BenefitCode = dap.BenefitCode;
        
        -- Drop Table
        DROP TABLE if exists Tmp_FR_DOCUMENT;
	end if;
End;
$$;	