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

CREATE OR REPLACE PROCEDURE aps.sp_getpalliativeregister(
    "Xml" text DEFAULT NULL
, "ID" uuid DEFAULT NULL::uuid
, "QueueName" character varying DEFAULT 'rds.registry'::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(36);
    BEGIN
        IF ("Xml" IS NOT NULL)
        THEN
            "dateTime" := COALESCE("dateTime", CURRENT_TIMESTAMP::TIMESTAMP);

            -- Получаем тип операции
            SELECT INTO
                _operationtype (XPATH('/PALLIATIVE_REGISTER/header[1]/TYPE[1]/text()', "Xml"::xml))[1]::varchar(36);

            -- Generate table Tmp_PALLIATIVE_REGISTER
            DROP TABLE IF EXISTS tmp_palliative_register;
            CREATE TEMP TABLE tmp_palliative_register
            AS
                SELECT (XPATH('PATIENT/RegisterID[1]/text()', x))[1]::text::int            AS registerid
                    ,
                       (XPATH('PATIENT/LastName[1]/text()', x))[1]::varchar(40)            AS lastname
                    ,
                       (XPATH('PATIENT/FirstName[1]/text()', x))[1]::varchar(40)           AS firstname
                    ,
                       (XPATH('PATIENT/FatherName[1]/text()', x))[1]::varchar(40)          AS fathername
                    ,
                       TO_TIMESTAMP((XPATH('PATIENT/Birthday[1]/text()', x))[1]::text,
                                    'DD.MM.YYYY')                                          AS birthday
                    ,
                       (XPATH('PATIENT/Kod[1]/text()', x))[1]::varchar(32)                 AS kod
                    ,
                       (XPATH('PATIENT/DocSeries[1]/text()', x))[1]::varchar(8)            AS docseries
                    ,
                       (XPATH('PATIENT/DocNumber[1]/text()', x))[1]::varchar(8)            AS docnumber
                    ,
                       (XPATH('PATIENT/Sex[1]/text()', x))[1]::text::int                   AS sex
                    ,
                       (XPATH('PATIENT/Snils[1]/text()', x))[1]::varchar(14)               AS snils
                    ,
                       TO_TIMESTAMP((XPATH('PATIENT/DateBegin[1]/text()', x))[1]::text,
                                    'DD.MM.YYYY')                                          AS datebegin
                    ,
                       TO_TIMESTAMP((XPATH('PATIENT/DateEnd[1]/text()', x))[1]::text,
                                    'DD.MM.YYYY')                                          AS dateend
                    ,
                       TO_TIMESTAMP((XPATH('PATIENT/LastEditDate[1]/text()', x))[1]::text,
                                    'DD.MM.YYYY')                                          AS lasteditdate
                    ,
                       (XPATH('PATIENT/Lpu[1]/text()', x))[1]::varchar(6)                  AS lpu
                    ,
                       (XPATH('PATIENT/ExcludeReason[1]/text()', x))[1]::text::int         AS excludereason
                    ,
                       (XPATH('PATIENT/DocType[1]/text()', x))[1]::text::int               AS doctype
                    ,
                       (XPATH('PATIENT/PatientId[1]/text()', x))[1]::text::uuid            AS patientid,
                       (XPATH('PATIENT/BirthSurname[1]/text()', x))[1]::varchar(40)        AS birthsurname,
                       TO_TIMESTAMP((XPATH('PATIENT/DocIssueDate[1]/text()', x))[1]::text,
                                    'DD.MM.YYYY')                                          AS docissuedate,
                       (XPATH('PATIENT/Enp[1]/text()', x))[1]::varchar(16)                 AS enp,
                       (XPATH('PATIENT/DocIssueOrg[1]/text()', x))[1]::varchar(256)        AS docissueorg,
                       (XPATH('PATIENT/RealFiasAddress[1]/text()', x))[1]::varchar(36)     AS realfiasaddress,
                       (XPATH('PATIENT/RealFiasHome[1]/text()', x))[1]::varchar(36)        AS realfiashome,
                       (XPATH('PATIENT/RealCity[1]/text()', x))[1]::varchar(100)           AS realcity,
                       (XPATH('PATIENT/RealCityPrefix[1]/text()', x))[1]::varchar(10)      AS realcityprefix,
                       (XPATH('PATIENT/RealStreet[1]/text()', x))[1]::varchar(100)         AS realstreet,
                       (XPATH('PATIENT/RealStreetPrefix[1]/text()', x))[1]::varchar(10)    AS realstreetprefix,
                       (XPATH('PATIENT/RealHouse[1]/text()', x))[1]::varchar(100)          AS realhouse,
                       (XPATH('PATIENT/RealBuild[1]/text()', x))[1]::varchar(100)          AS realbuild,
                       (XPATH('PATIENT/RealStruc[1]/text()', x))[1]::varchar(100)          AS realstruc,
                       (XPATH('PATIENT/RealRoom[1]/text()', x))[1]::varchar(100)           AS realroom,
                       TO_TIMESTAMP((XPATH('PATIENT/RealLiveStartDate[1]/text()', x))[1]::text,
                                    'DD.MM.YYYY')                                          AS reallivestartdate,
                       TO_TIMESTAMP((XPATH('PATIENT/RealLiveEndDate[1]/text()', x))[1]::text,
                                    'DD.MM.YYYY')                                          AS realliveenddate,
                       (XPATH('PATIENT/LegalFiasAddress[1]/text()', x))[1]::varchar(36)    AS legalfiasaddress,
                       (XPATH('PATIENT/LegalFiasHome[1]/text()', x))[1]::varchar(36)       AS legalfiashome,
                       (XPATH('PATIENT/LegalCity[1]/text()', x))[1]::varchar(100)          AS legalcity,
                       (XPATH('PATIENT/LegalCityPrefix[1]/text()', x))[1]::varchar(10)     AS legalcityprefix,
                       (XPATH('PATIENT/LegalStreet[1]/text()', x))[1]::varchar(100)        AS legalstreet,
                       (XPATH('PATIENT/LegalStreetPrefix[1]/text()', x))[1]::varchar(10)   AS legalstreetprefix,
                       (XPATH('PATIENT/LegalHouse[1]/text()', x))[1]::varchar(100)         AS legalhouse,
                       (XPATH('PATIENT/LegalBuild[1]/text()', x))[1]::varchar(100)         AS legalbuild,
                       (XPATH('PATIENT/LegalStruc[1]/text()', x))[1]::varchar(100)         AS legalstruc,
                       (XPATH('PATIENT/LegalRoom[1]/text()', x))[1]::varchar(100)          AS legalroom,
                       TO_TIMESTAMP((XPATH('PATIENT/LegalLiveStartDate[1]/text()', x))[1]::text,
                                    'DD.MM.YYYY')                                          AS legallivestartdate,
                       TO_TIMESTAMP((XPATH('PATIENT/LegalLiveEndDate[1]/text()', x))[1]::text,
                                    'DD.MM.YYYY')                                          AS legalliveenddate,
                       (XPATH('PATIENT/CitizenShip[1]/text()', x))[1]::text::int           AS citizenship,
                       (XPATH('PATIENT/EhrRus5o5e88vhc6[1]/text()', x))[1]::varchar(52)    AS ehrrus5o5e88vhc6,
                       (XPATH('PATIENT/EhrRus5o5e88vhc6Ver[1]/text()', x))[1]::varchar(52) AS ehrrus5o5e88vhc6ver
                FROM (SELECT UNNEST(XPATH('PALLIATIVE_REGISTER/body[1]/PATIENT', x)) AS x
                      FROM (SELECT "Xml"::xml AS x
                           ) root
                     ) patient;


            -- Generate table Tmp_PALLIATIVE_BENEFITS
            DROP TABLE IF EXISTS tmp_palliative_benefits;
            CREATE TEMP TABLE tmp_palliative_benefits
            AS
                SELECT (XPATH('BENEFITS/RegisterID[1]/text()', x))[1]::text::int                       AS registerid
                     , TO_TIMESTAMP((XPATH('BENEFITS/DiagDate[1]/text()', x))[1]::text, 'DD.MM.YYYY')  AS diagdate
                     , (XPATH('BENEFITS/Document[1]/text()', x))[1]::varchar(16)                       AS document
                     , TO_TIMESTAMP((XPATH('./DocumentDate[1]/text()', x))[1]::text, 'DD.MM.YYYY')     AS documentdate
                     , (XPATH('BENEFITS/Mkb[1]/text()', x))[1]::varchar(8)                             AS mkb
                     , (XPATH('BENEFITS/BenefitCode[1]/text()', x))[1]::varchar(255)                   AS benefitcode
                     , (XPATH('BENEFITS/BenefitViews[1]/text()', x))[1]::text::int                     AS benefitviews
                     , TO_TIMESTAMP((XPATH('BENEFITS/BeginDate[1]/text()', x))[1]::text, 'DD.MM.YYYY') AS begindate
                     , TO_TIMESTAMP((XPATH('BENEFITS/EndDate[1]/text()', x))[1]::text, 'DD.MM.YYYY')   AS enddate
                     , (XPATH('BENEFITS/Lpu[1]/text()', x))[1]::varchar(6)                             AS lpu
                FROM (SELECT UNNEST(XPATH('PALLIATIVE_REGISTER/body[1]/PATIENT/BENEFITS', x)) AS x
                      FROM (SELECT "Xml"::xml AS x
                           ) root
                     ) benefits;

            -- Обработка операции
            IF (_operationtype = 'FTLoad')
            THEN

                DELETE FROM aps.t_palliative_register_data;
                DELETE FROM aps.t_palliative_register;

            ELSE

                DELETE
                FROM aps.t_palliative_register_data ben
                    USING aps.t_palliative_register rr, tmp_palliative_register r
                WHERE rr.palliativeregisterid = ben.palliativeregister
                  AND r.registerid = rr.pac_id;

                DELETE
                FROM tmp_palliative_register r
                    USING aps.t_palliative_register rr
                WHERE r.registerid = rr.pac_id;

            END IF;


            INSERT INTO
                aps.t_palliative_register
            (palliativeregisterid,
             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(),
                   r.excludereason,
                   r.lasteditdate,
                   r.lastname,
                   r.firstname,
                   r.fathername,
                   r.birthday,
                   r.kod,
                   r.docseries,
                   r.docnumber,
                   r.registerid,
                   e.enumid,
                   r.snils,
                   edoc.enumid,
                   r.lpu,
                   r.datebegin,
                   r.dateend,
                   r.docissuedate,
                   r.docissueorg,
                   r.enp,
                   r.realfiasaddress,
                   r.realfiashome,
                   r.realcity,
                   r.realcityprefix,
                   r.realstreet,
                   r.realstreetprefix,
                   r.realhouse,
                   r.realbuild,
                   r.realstruc,
                   r.realroom,
                   r.reallivestartdate,
                   r.realliveenddate,
                   r.legalfiasaddress,
                   r.legalfiashome,
                   r.legalcity,
                   r.legalcityprefix,
                   r.legalstreet,
                   r.legalstreetprefix,
                   r.legalhouse,
                   r.legalbuild,
                   r.legalstruc,
                   r.legalroom,
                   r.legallivestartdate,
                   r.legalliveenddate,
                   cs.ehrrusid,
                   doc.ehrrusid
            FROM tmp_palliative_register   r
            LEFT JOIN vclib.t_enums        edoc ON edoc.enumtypeid = 105 AND edoc.code = r.doctype
            LEFT JOIN vclib.t_enums        e ON e.enumtypeid = 103 AND e.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
                          ORDER BY doc.ehrrusid
                          LIMIT 1
                          )                doc ON TRUE;

            -- Загружаем новые
            INSERT INTO
                aps.t_palliative_register_data
            ( palliativeregisterdataid
            , palliativeregister
            , diagdate
            , document
            , documentdate
            , mkb
            , benefits
            , begindate
            , enddate
            , lasteditdate)
            SELECT gen_random_uuid()
                 , rr.palliativeregisterid
                 , r.diagdate
                 , r.document
                 , r.documentdate
                 , r.mkb
                 , b.benefitsid
                 , r.begindate
                 , r.enddate
                 , NOW()
            FROM tmp_palliative_benefits        r
            JOIN      aps.t_palliative_register rr ON rr.pac_id = r.registerid
            LEFT JOIN vclib.t_enums             e ON e.enumtypeid = 190 AND e.code = r.benefitviews
            LEFT JOIN aps.t_benefits            b ON b.benefitcode = r.benefitcode AND b.benefitviews = e.enumid;


        END IF;
    END;
END;
$$;