DROP PROCEDURE IF EXISTS dbo.sp_rr_direction;
-- =============================================
-- 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),
                                'YYYY.MM.DD')                                               AS birthday
                 , TO_TIMESTAMP((XPATH('RR_DIRECTION/DateBegin[1]/text()', x))[1]::varchar(10),
                                'YYYY.MM.DD')                                               AS datebegin
                 , TO_TIMESTAMP((XPATH('RR_DIRECTION/DateEnd[1]/text()', x))[1]::varchar(10),
                                'YYYY.MM.DD')                                               AS dateend
                 , TO_TIMESTAMP((XPATH('RR_DIRECTION/LastEditDate[1]/text()', x))[1]::varchar(10),
                                'YYYY.MM.DD')                                               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),
                                'YYYY.MM.DD')                                               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),
                                'YYYY.MM.DD')                                               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),
                                'YYYY.MM.DD')                                               AS reallivestartdate
                 , TO_TIMESTAMP((XPATH('RR_DIRECTION/RealLiveEndDate[1]/text()', x))[1]::varchar(10),
                                'YYYY.MM.DD')                                               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),
                                'YYYY.MM.DD')                                               AS legallivestartdate
                 , TO_TIMESTAMP((XPATH('RR_DIRECTION/LegalLiveEndDate[1]/text()', x))[1]::varchar(10),
                                'YYYY.MM.DD')                                               AS legalliveenddate
                 , (XPATH('RR_DIRECTION/CitizenShip[1]/text()', x))[1]::text::int           AS citizenship
                 , (XPATH('RR_DIRECTION/EhrRus5o5e88vhc6[1]/text()', x))[1]::varchar(52)    AS ehrrus5o5e88vhc6
                 , (XPATH('RR_DIRECTION/EhrRus5o5e88vhc6Ver[1]/text()', x))[1]::varchar(52) AS ehrrus5o5e88vhc6ver
            FROM (SELECT UNNEST(XPATH('RR_DIRECTION/body[1]/RR_DIRECTION', "Xml"::xml)) AS x
                 ) rr_direction;
        
        
        -- Generate table #RR_DIRECTION_BENEFIT
        DROP TABLE IF EXISTS tmp_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),
                                'YYYY.MM.DD')                                                   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),
                                'YYYY.MM.DD')                                                   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),
                                'YYYY.MM.DD')                                                   AS begindate
                 , TO_TIMESTAMP((XPATH('RR_DIRECTION_BENEFIT/EndDate[1]/text()', x))[1]::varchar(10),
                                'YYYY.MM.DD')                                                   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),
                                'YYYY.MM.DD')                                                   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.DateBegin
             , pd.DateEnd
             , pd.LastEditDate
             , pd.lpu
             , pd.ExcludeReason
             , 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 tmp_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;
    
    END;
END;
$$;
