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);
            
            -- Обработка операции
            IF (_operationtype = 'FTLoad')
            THEN
                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;
                
                ANALYZE tmp_rr_patients;
                
                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;
        END IF;
    END;
END;
$$;
