CREATE OR REPLACE PROCEDURE public.sp_bskdirection(
    "Xml" text,
    "QueueName" varchar(100) DEFAULT 'RDSSync',
    "dateTime" timestamp DEFAULT NULL,
    "User" uuid DEFAULT NULL
)
    LANGUAGE plpgsql
AS
$$
DECLARE
    p_xml             xml          := "Xml"::xml;
    p_queuename       varchar(100) := "QueueName";
    p_datetime        timestamp    := COALESCE("dateTime", CURRENT_TIMESTAMP);
    p_user            uuid         := "User";
    v_result          text;
    v_operationtype   varchar(6);
    v_contentencoding varchar(255);
    v_type            varchar(255);
    v_ownerid         uuid;
    v_registerid      int;
    v_changetype      int;
    c_insert          varchar(6)   := 'Insert';
    c_update          varchar(6)   := 'Update';
    c_delete          varchar(6)   := 'Delete';
BEGIN
    
    SELECT (XPATH('/BSK_DIRECTION/RegisterID/text()', x))[1]::varchar(10)::int AS registerid,
           (XPATH('/BSK_DIRECTION/ChangeType/text()', x))[1]::varchar(10)::int AS changetype,
           'utf-8',
           'BSK_DIRECTION'
    INTO v_registerid,
        v_changetype,
        v_contentencoding,
        v_type
    FROM UNNEST(XPATH('/BSK_DIRECTION', p_xml)) x;
    
    CREATE TEMP TABLE temp_bsk_direction AS
        SELECT (XPATH('/BSK_DIRECTION/ID/text()', x))[1]::text::uuid                      AS id,
               (XPATH('/BSK_DIRECTION/LPU/text()', x))[1]::varchar(6)                     AS lpu,
               (XPATH('/BSK_DIRECTION/EnterDate/text()', x))[1]::text::timestamp          AS enterdate,
               (XPATH('/BSK_DIRECTION/LastName/text()', x))[1]::varchar(40)               AS lastname,
               (XPATH('/BSK_DIRECTION/FirstName/text()', x))[1]::varchar(40)              AS firstname,
               (XPATH('/BSK_DIRECTION/FatherName/text()', x))[1]::varchar(40)             AS fathername,
               (XPATH('/BSK_DIRECTION/Birthday/text()', x))[1]::text::timestamp           AS birthday,
               (XPATH('/BSK_DIRECTION/Date/text()', x))[1]::text::timestamp               AS date,
               (XPATH('/BSK_DIRECTION/Sex/text()', x))[1]::varchar(10)::int               AS sex,
               (XPATH('/BSK_DIRECTION/Kod/text()', x))[1]::varchar(32)                    AS kod,
               (XPATH('/BSK_DIRECTION/DocSeries/text()', x))[1]::varchar(16)              AS docseries,
               (XPATH('/BSK_DIRECTION/DocNumber/text()', x))[1]::varchar(16)              AS docnumber,
               (XPATH('/BSK_DIRECTION/DocType/text()', x))[1]::varchar(10)::int           AS doctype,
               (XPATH('/BSK_DIRECTION/RegisterID/text()', x))[1]::varchar(10)::int        AS registerid,
               (XPATH('/BSK_DIRECTION/Snils/text()', x))[1]::varchar(14)                  AS snils,
               (XPATH('/BSK_DIRECTION/ChangeType/text()', x))[1]::varchar(10)::int        AS changetype,
               (XPATH('/BSK_DIRECTION/ExcludeReason/text()', x))[1]::varchar(10)::int     AS excludereason,
               (XPATH('/BSK_DIRECTION/CreateDateTime/text()', x))[1]::text::timestamp     AS createdatetime,
               (XPATH('/BSK_DIRECTION/BirthSurname/text()', x))[1]::varchar(40)           AS birthsurname,
               (XPATH('/BSK_DIRECTION/DocIssueDate/text()', x))[1]::text::timestamp       AS docissuedate,
               (XPATH('/BSK_DIRECTION/Enp/text()', x))[1]::varchar(16)                    AS enp,
               (XPATH('/BSK_DIRECTION/DocIssueOrg/text()', x))[1]::varchar(256)           AS docissueorg,
               (XPATH('/BSK_DIRECTION/RealFiasAddress/text()', x))[1]::varchar(36)        AS realfiasaddress,
               (XPATH('/BSK_DIRECTION/RealFiasHome/text()', x))[1]::varchar(36)           AS realfiashome,
               (XPATH('/BSK_DIRECTION/RealCity/text()', x))[1]::varchar(100)              AS realcity,
               (XPATH('/BSK_DIRECTION/RealCityPrefix/text()', x))[1]::varchar(10)         AS realcityprefix,
               (XPATH('/BSK_DIRECTION/RealStreet/text()', x))[1]::varchar(100)            AS realstreet,
               (XPATH('/BSK_DIRECTION/RealStreetPrefix/text()', x))[1]::varchar(10)       AS realstreetprefix,
               (XPATH('/BSK_DIRECTION/RealHouse/text()', x))[1]::varchar(100)             AS realhouse,
               (XPATH('/BSK_DIRECTION/RealBuild/text()', x))[1]::varchar(100)             AS realbuild,
               (XPATH('/BSK_DIRECTION/RealStruc/text()', x))[1]::varchar(100)             AS realstruc,
               (XPATH('/BSK_DIRECTION/RealRoom/text()', x))[1]::varchar(100)              AS realroom,
               (XPATH('/BSK_DIRECTION/RealLiveStartDate/text()', x))[1]::text::timestamp  AS reallivestartdate,
               (XPATH('/BSK_DIRECTION/RealLiveEndDate/text()', x))[1]::text::timestamp    AS realliveenddate,
               (XPATH('/BSK_DIRECTION/LegalFiasAddress/text()', x))[1]::varchar(36)       AS legalfiasaddress,
               (XPATH('/BSK_DIRECTION/LegalFiasHome/text()', x))[1]::varchar(36)          AS legalfiashome,
               (XPATH('/BSK_DIRECTION/LegalCity/text()', x))[1]::varchar(100)             AS legalcity,
               (XPATH('/BSK_DIRECTION/LegalCityPrefix/text()', x))[1]::varchar(10)        AS legalcityprefix,
               (XPATH('/BSK_DIRECTION/LegalStreet/text()', x))[1]::varchar(100)           AS legalstreet,
               (XPATH('/BSK_DIRECTION/LegalStreetPrefix/text()', x))[1]::varchar(10)      AS legalstreetprefix,
               (XPATH('/BSK_DIRECTION/LegalHouse/text()', x))[1]::varchar(100)            AS legalhouse,
               (XPATH('/BSK_DIRECTION/LegalBuild/text()', x))[1]::varchar(100)            AS legalbuild,
               (XPATH('/BSK_DIRECTION/LegalStruc/text()', x))[1]::varchar(100)            AS legalstruc,
               (XPATH('/BSK_DIRECTION/LegalRoom/text()', x))[1]::varchar(100)             AS legalroom,
               (XPATH('/BSK_DIRECTION/LegalLiveStartDate/text()', x))[1]::text::timestamp AS legallivestartdate,
               (XPATH('/BSK_DIRECTION/LegalLiveEndDate/text()', x))[1]::text::timestamp   AS legalliveenddate,
               (XPATH('/BSK_DIRECTION/CitizenShip/text()', x))[1]::varchar(3)             AS citizenship
        FROM UNNEST(XPATH('/BSK_DIRECTION', p_xml)) x;
    
    CREATE TEMP TABLE temp_bsk_benefits AS
        SELECT (XPATH('/BSK_DIRECTION_BENEFIT/DirectionID/text()', x))[1]::varchar(36)::uuid     AS directionid,
               (XPATH('/BSK_DIRECTION_BENEFIT/DirectionBenID/text()', x))[1]::varchar(36)::uuid  AS directionbenid,
               (XPATH('/BSK_DIRECTION_BENEFIT/DiagDate/text()', x))[1]::text::timestamp          AS diagdate,
               (XPATH('/BSK_DIRECTION_BENEFIT/Document/text()', x))[1]::varchar(16)              AS document,
               (XPATH('/BSK_DIRECTION_BENEFIT/DocumentDate/text()', x))[1]::text::timestamp      AS documentdate,
               (XPATH('/BSK_DIRECTION_BENEFIT/Mkb/text()', x))[1]::varchar(8)                    AS mkb,
               (XPATH('/BSK_DIRECTION_BENEFIT/BenefitCode/text()', x))[1]::varchar(255)          AS benefitcode,
               (XPATH('/BSK_DIRECTION_BENEFIT/BenefitViews/text()', x))[1]::varchar(10)::int     AS benefitviews,
               (XPATH('/BSK_DIRECTION_BENEFIT/PostCode/text()', x))[1]::varchar(255)             AS postcode,
               (XPATH('/BSK_DIRECTION_BENEFIT/ChangeType/text()', x))[1]::varchar(10)::int       AS changetype,
               (XPATH('/BSK_DIRECTION_BENEFIT/ServiceCode/text()', x))[1]::varchar(20)           AS servicecode,
               (XPATH('/BSK_DIRECTION_BENEFIT/ServiceName/text()', x))[1]::varchar(255)          AS servicename,
               (XPATH('/BSK_DIRECTION_BENEFIT/DateBegin/text()', x))[1]::text::timestamp         AS datebegin,
               (XPATH('/BSK_DIRECTION_BENEFIT/DateEnd/text()', x))[1]::text::timestamp           AS dateend,
               (XPATH('/BSK_DIRECTION_BENEFIT/ExclusionReason/text()', x))[1]::varchar(10)::int  AS exclusionreason,
               (XPATH('/BSK_DIRECTION_BENEFIT/EstimatedDDate/text()', x))[1]::text::timestamp    AS estimatedddate,
               (XPATH('/BSK_DIRECTION_BENEFIT/DRegistrationDate/text()', x))[1]::text::timestamp AS dregistrationdate,
               (XPATH('/BSK_DIRECTION_BENEFIT/Place/text()', x))[1]::varchar(10)::int            AS place,
               (XPATH('/BSK_DIRECTION_BENEFIT/LpuTo/text()', x))[1]::varchar(6)                  AS lputo,
               (XPATH('/BSK_DIRECTION_BENEFIT/CloseDate/text()', x))[1]::text::timestamp         AS closedate,
               (XPATH('/BSK_DIRECTION_BENEFIT/ConcomitantMkb/text()', x))[1]::varchar(255)       AS concomitantmkb,
               (XPATH('/BSK_DIRECTION_BENEFIT/ContomitantDisease/text()', x))[1]::varchar(255)   AS contomitantdisease
        FROM UNNEST(XPATH('/BSK_DIRECTION/BSK_DIRECTION_BENEFITS/BSK_DIRECTION_BENEFIT', p_xml)) x;
    
    CREATE TEMP TABLE temp_medicaments AS
        SELECT (XPATH('/BSK_BENEFITS_DIRECTION_MEDICAMENT/DirectionBenId/text()', x))[1]::varchar(36)::uuid  AS directionbenid,
               (XPATH('/BSK_BENEFITS_DIRECTION_MEDICAMENT/Mnn/text()', x))[1]::varchar(10)::int              AS mnn,
               (XPATH('/BSK_BENEFITS_DIRECTION_MEDICAMENT/DateStart/text()', x))[1]::text::timestamp         AS datestart,
               (XPATH('/BSK_BENEFITS_DIRECTION_MEDICAMENT/EndDate/text()', x))[1]::text::timestamp           AS enddate,
               (XPATH('/BSK_BENEFITS_DIRECTION_MEDICAMENT/MedicationForms/text()', x))[1]::varchar(36)::uuid AS medicationforms,
               (XPATH('/BSK_BENEFITS_DIRECTION_MEDICAMENT/Regiment/text()', x))[1]::varchar(255)             AS regiment,
               (XPATH('/BSK_BENEFITS_DIRECTION_MEDICAMENT/Dose/text()', x))[1]::text::decimal(31, 10)        AS dose,
               (XPATH('/BSK_BENEFITS_DIRECTION_MEDICAMENT/Prepid/text()', x))[1]::varchar(10)::int           AS prepid,
               (XPATH('/BSK_BENEFITS_DIRECTION_MEDICAMENT/DayliCoeff/text()', x))[1]::text::decimal(31, 10)  AS daylicoeff,
               (XPATH('/BSK_BENEFITS_DIRECTION_MEDICAMENT/MeasureUnit/text()', x))[1]::varchar(255)          AS measureunit,
               (XPATH('/BSK_BENEFITS_DIRECTION_MEDICAMENT/MeasureUnitType/text()', x))[1]::varchar(10)::int  AS measureunittype,
               (XPATH('/BSK_BENEFITS_DIRECTION_MEDICAMENT/SingleDose/text()', x))[1]::text::decimal(31, 10)  AS singledose,
               (XPATH('/BSK_BENEFITS_DIRECTION_MEDICAMENT/Multiplicity/text()', x))[1]::varchar(10)::int     AS multiplicity,
               (XPATH('/BSK_BENEFITS_DIRECTION_MEDICAMENT/DoseView/text()', x))[1]::varchar(255)             AS doseview,
               (XPATH('/BSK_BENEFITS_DIRECTION_MEDICAMENT/DoseViewType/text()', x))[1]::varchar(10)::int     AS doseviewtype
        FROM UNNEST(XPATH(
            '/BSK_DIRECTION/BSK_DIRECTION_BENEFITS/BSK_DIRECTION_BENEFIT/BSK_BENEFITS_DIRECTION_MEDICAMENTS/BSK_BENEFITS_DIRECTION_MEDICAMENT',
            p_xml)) x;
    
    CREATE TEMP TABLE temp_complications AS
        SELECT (XPATH('/BSK_REGISTER_COMPLICATION_LINK/DirectionBenId/text()', x))[1]::varchar(36)::uuid  AS directionbenid,
               (XPATH('/BSK_REGISTER_COMPLICATION_LINK/ComplicationID/text()', x))[1]::varchar(36)::uuid  AS complicationid,
               (XPATH('/BSK_REGISTER_COMPLICATION_LINK/ComplicationType/text()', x))[1]::varchar(10)::int AS complicationtype,
               (XPATH('/BSK_REGISTER_COMPLICATION_LINK/RegistrationDate/text()', x))[1]::text::timestamp  AS registrationdate,
               (XPATH('/BSK_REGISTER_COMPLICATION_LINK/MKBCode/text()', x))[1]::varchar(255)              AS mkbcode,
               (XPATH('/BSK_REGISTER_COMPLICATION_LINK/Diagnosis/text()', x))[1]::varchar(255)            AS diagnosis,
               (XPATH('/BSK_REGISTER_COMPLICATION_LINK/EhrRusR6JR8PINUS/text()', x))[1]::varchar(10)::int AS ehrrusr6jr8pinus,
               (XPATH('/BSK_REGISTER_COMPLICATION_LINK/EhrRusR6JR8PINUSVer/text()', x))[1]::varchar(8)    AS ehrrusr6jr8pinusver,
               (XPATH('/BSK_REGISTER_COMPLICATION_LINK/EhrRusIN6SHID9F6/text()', x))[1]::varchar(10)::int AS ehrrusin6shid9f6,
               (XPATH('/BSK_REGISTER_COMPLICATION_LINK/EhrRusIN6SHID9F6Ver/text()', x))[1]::varchar(8)    AS ehrrusin6shid9f6ver,
               (XPATH('/BSK_REGISTER_COMPLICATION_LINK/EhrRusJEDS208EN8/text()', x))[1]::varchar(10)::int AS ehrrusjeds208en8,
               (XPATH('/BSK_REGISTER_COMPLICATION_LINK/EhrRusJEDS208EN8Ver/text()', x))[1]::varchar(8)    AS ehrrusjeds208en8ver,
               (XPATH('/BSK_REGISTER_COMPLICATION_LINK/LeftVentricularEjectionFraction/text()',
                      x))[1]::varchar(10)::int                                                            AS leftventricularejectionfraction,
               (XPATH('/BSK_REGISTER_COMPLICATION_LINK/FractionRegistrationDate/text()',
                      x))[1]::text::timestamp                                                             AS fractionregistrationdate
        FROM UNNEST(XPATH(
            '/BSK_DIRECTION/BSK_DIRECTION_BENEFITS/BSK_DIRECTION_BENEFIT/BSK_REGISTER_COMPLICATION_LINKS/BSK_REGISTER_COMPLICATION_LINK',
            p_xml)) x;
    
    IF (v_registerid IS NULL AND v_changetype <> 1) THEN
        
        UPDATE bskd
        SET registerid = br.pac_id
        FROM temp_bsk_direction bskd
        JOIN rds.t_bsk_register br ON bskd.snils = br.snils
        WHERE bskd.registerid IS NULL
          AND bskd.snils IS NOT NULL
          AND bskd.snils <> ''
          AND bskd.snils <> '              '
          AND ((br.dateend IS NULL OR br.dateend > p_datetime) AND br.excludereason IS NULL)
        RETURNING br.pac_id
            INTO v_registerid;
        
        -- Поиск по серии и номеру документа
        UPDATE bskd
        SET registerid = br.pac_id
        FROM temp_bsk_direction bskd
        JOIN rds.t_bsk_register br ON bskd.docseries = br.docseries AND bskd.docnumber = br.docnumber
        WHERE bskd.registerid IS NULL
          AND bskd.docseries IS NOT NULL
          AND bskd.docnumber IS NOT NULL
          AND bskd.docseries <> ''
          AND bskd.docnumber <> ''
          AND ((br.dateend IS NULL OR br.dateend > p_datetime) AND br.excludereason IS NULL)
        RETURNING br.pac_id
            INTO v_registerid;
        
        -- Поиск по ФИО + ДР
        UPDATE bskd
        SET registerid = br.pac_id
        FROM temp_bsk_direction bskd
        JOIN rds.t_bsk_register br ON bskd.lastname = br.lastname AND bskd.firstname = br.firstname AND
                                      (bskd.fathername = br.fathername OR
                                       (bskd.fathername IS NULL AND br.fathername IS NULL)) AND
                                      bskd.birthday = br.birthday
        WHERE bskd.registerid IS NULL
          AND ((br.dateend IS NULL OR br.dateend > p_datetime) AND br.excludereason IS NULL)
        RETURNING br.pac_id
            INTO v_registerid;
    
    ELSIF (v_registerid IS NULL AND v_changetype = 1) THEN
        
        CALL public.sp_generateidforentity('BSK_REGISTER', 'PrimaryKey', 1, v_registerid);
        
        UPDATE temp_bsk_direction SET registerid = v_registerid;
        
        IF (v_registerid = 0) THEN
            RAISE EXCEPTION 'Не верно сгенерирован идентификатор льготника в регистре!';
        END IF;
    END IF;
    
    UPDATE rp
    SET lastname           = p.lastname,
        firstname          = p.firstname,
        fathername         = p.fathername,
        birthday           = p.birthday,
        kod                = p.kod,
        docseries          = p.docseries,
        docnumber          = p.docnumber,
        sex                = esex.enumid,
        snils              = p.snils,
        doctype            = edoc.enumid,
        lpumain            = p.lpu,
        lasteditdate       = p_datetime,
        docissuedate       = p.docissuedate,
        docissueorg        = p.docissueorg,
        enp                = p.enp,
        realfiasaddress    = p.realfiasaddress,
        realfiashome       = p.realfiashome,
        realcity           = p.realcity,
        realcityprefix     = p.realcityprefix,
        realstreet         = p.realstreet,
        realstreetprefix   = p.realstreetprefix,
        realhouse          = p.realhouse,
        realbuild          = p.realbuild,
        realstruc          = p.realstruc,
        realroom           = p.realroom,
        reallivestartdate  = p.reallivestartdate,
        realliveenddate    = p.realliveenddate,
        legalfiasaddress   = p.legalfiasaddress,
        legalfiashome      = p.legalfiashome,
        legalcity          = p.legalcity,
        legalcityprefix    = p.legalcityprefix,
        legalstreet        = p.legalstreet,
        legalstreetprefix  = p.legalstreetprefix,
        legalhouse         = p.legalhouse,
        legalbuild         = p.legalbuild,
        legalstruc         = p.legalstruc,
        legalroom          = p.legalroom,
        legallivestartdate = p.legallivestartdate,
        legalliveenddate   = p.legalliveenddate,
        citizenship        = cs.ehrrusid,
        ehrrus5o5e88vhc6   = dul.ehrrus5o5e88vhc6
    FROM rds.t_bsk_register rp
    JOIN temp_bsk_direction p ON p.registerid = rp.pac_id
    LEFT JOIN vclib.t_enums esex ON esex.enumtypeid = 103 AND esex.code = p.sex
    LEFT JOIN vclib.t_enums edoc ON edoc.enumtypeid = 105 AND edoc.code = p.doctype
    LEFT JOIN ehr.t_ehr_rus_o00015 cs ON cs.code = p.citizenship
    LEFT JOIN ehr.t_ehr_type_matching_dul dul ON dul.doctype = edoc.enumid
    WHERE p.changetype = 2;
    
    UPDATE rp
    SET lasteditdate  = p_datetime,
        dateend       = p.enterdate,
        excludereason = p.excludereason
    FROM rds.t_bsk_register rp
    JOIN temp_bsk_direction p
        ON p.registerid = rp.pac_id
    WHERE p.changetype = 3;
    
    UPDATE brd
    SET closedate       = p_datetime,
        lasteditdate    = p_datetime,
        exclusionreason = CASE
                              WHEN p.excludereason = 0 THEN 2
                              WHEN p.excludereason = 1 THEN 0
                              WHEN p.excludereason = 2 THEN 3
                          END
    FROM rds.t_bsk_register_data brd
    JOIN rds.t_bsk_register rp ON rp.bskregisterid = brd.bskregister
    JOIN temp_bsk_direction p ON p.registerid = rp.pac_id
    WHERE p.changetype = 3
      AND brd.closedate IS NULL;
    
    INSERT INTO
        rds.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,
           p_datetime,
           lastname,
           firstname,
           fathername,
           birthday,
           kod,
           docseries,
           docnumber,
           registerid,
           esex.enumid,
           snils,
           edoc.enumid,
           lpu,
           p_datetime,
           NULL,
           d.docissuedate,
           d.docissueorg,
           d.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,
           dul.ehrrus5o5e88vhc6
    FROM temp_bsk_direction d
    LEFT JOIN vclib.t_enums esex ON esex.enumtypeid = 103 AND esex.code = d.sex
    LEFT JOIN vclib.t_enums edoc ON edoc.enumtypeid = 105 AND edoc.code = d.doctype
    LEFT JOIN ehr.t_ehr_rus_o00015 cs ON cs.code = d.citizenship
    LEFT JOIN ehr.t_ehr_type_matching_dul dul ON dul.doctype = edoc.enumid
    WHERE d.changetype = 1;
    
    INSERT INTO
        rds.t_bsk_register_data
    (bskregisterdataid,
     bskregister,
     servicecode,
     servicename,
     diagdate,
     document,
     documentdate,
     mkb,
     benefits,
     begindate,
     enddate,
     exclusionreason,
     estimatedddate,
     dregistrationdate,
     place,
     lpumainto,
     closedate,
     lpumain,
     createdatetime,
     lasteditdate,
     concomitantmkb,
     contomitantdisease)
    SELECT bd.directionbenid,
           r.bskregisterid,
           servicecode,
           servicename,
           diagdate,
           document,
           documentdate,
           mkb,
           b.benefitsid,
           COALESCE(bd.datebegin, p_datetime),
           COALESCE(
               bd.dateend,
               CASE
                   WHEN b.duration IS NOT NULL AND b.periodofact IS NOT NULL AND b.closetype IS NOT NULL THEN
                       CASE
                           WHEN b.closetype = 0 AND b.periodofact = 0 THEN brr.birthday + (b.duration || ' days')::INTERVAL
                           WHEN b.closetype = 0 AND b.periodofact = 1 THEN brr.birthday + (b.duration || ' months')::INTERVAL
                           WHEN b.closetype = 0 AND b.periodofact = 2 THEN brr.birthday + (b.duration || ' years')::INTERVAL
                           WHEN b.closetype = 1 AND b.periodofact = 0 THEN COALESCE(bd.datebegin, p_datetime) +
                                                                           (b.duration || ' days')::INTERVAL
                           WHEN b.closetype = 1 AND b.periodofact = 1 THEN COALESCE(bd.datebegin, p_datetime) +
                                                                           (b.duration || ' months')::INTERVAL
                           WHEN b.closetype = 1 AND b.periodofact = 2 THEN COALESCE(bd.datebegin, p_datetime) +
                                                                           (b.duration || ' years')::INTERVAL
                           ELSE NULL
                       END
                   ELSE NULL
               END
           )
    FROM temp_bsk_benefits bd
    JOIN temp_bsk_direction brr ON brr.id = bd.directionid
    JOIN rds.t_bsk_register r ON r.pac_id = v_registerid
    LEFT JOIN vclib.t_enums e ON e.enumtypeid = 190 AND e.code = bd.benefitviews
    LEFT JOIN rds.t_benefits b ON b.benefitcode = bd.benefitcode AND b.benefitviews = e.enumid
    WHERE bd.changetype = 1;
    
    UPDATE rpb
    SET dregistrationdate = rdb.dregistrationdate,
        lasteditdate      = p_datetime
    FROM temp_bsk_benefits rdb
    JOIN rds.t_bsk_register r ON r.pac_id = v_registerid
    LEFT JOIN vclib.t_enums e ON e.enumtypeid = 190 AND e.code = rdb.benefitviews
    LEFT JOIN rds.t_benefits b ON b.benefitcode = rdb.benefitcode AND b.benefitviews = e.enumid
    JOIN rds.t_bsk_register_data rpb ON rpb.bskregister = r.bskregisterid AND rpb.benefits = b.benefitsid
    WHERE rdb.changetype = 2
      AND (rpb.enddate IS NULL OR rpb.enddate > p_datetime)
      AND rpb.closedate IS NULL;
    
    INSERT INTO
        rds.t_bsk_register_medicaments
    (bskregistermedicamentsid,
     bskregisterdata,
     cmnn,
     datestart,
     enddate,
     medicationforms,
     regiment,
     dose,
     measureunit,
     prepid,
     daylicoeff,
     singledose,
     multiplicity,
     doseview)
    SELECT gen_random_uuid(),
           ben.directionbenid,
           (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 temp_medicaments meds
    JOIN temp_bsk_benefits ben ON ben.directionbenid = meds.directionbenid
    WHERE ben.changetype = 1;
    
    
    INSERT INTO
        rds.t_bsk_register_complication_link (bskregistercomplicationlinkid,
                                              bskregisterdata,
                                              complicationtype,
                                              registrationdate,
                                              mkbcode,
                                              diagnosis,
                                              type_)
    SELECT compl.complicationid,
           ben.directionbenid,
           compl.complicationtype,
           compl.registrationdate,
           compl.mkbcode,
           compl.diagnosis,
           (SELECT e.entityid FROM vclib.t_entities e WHERE e.entname = 'BSK_REGISTER_CHRONIC_HEART_FAILURE' LIMIT 1)
    FROM temp_complications compl
    JOIN temp_bsk_benefits ben ON ben.directionbenid = compl.directionbenid
    WHERE ben.changetype = 1;
    
    INSERT INTO
        rds.t_bsk_register_complication_link
    (bskregistercomplicationlinkid, bskregisterdata, complicationtype, registrationdate, mkbcode, diagnosis, type_)
    SELECT compl.complicationid,
           ben.directionbenid,
           compl.complicationtype,
           compl.registrationdate,
           compl.mkbcode,
           compl.diagnosis,
           (SELECT e.entityid FROM vclib.t_entities e WHERE e.entname = 'BSK_REGISTER_CHRONIC_HEART_FAILURE' LIMIT 1)
    FROM temp_complications compl
    JOIN temp_bsk_benefits ben ON ben.directionbenid = compl.directionbenid
    LEFT JOIN LATERAL ( SELECT er.ehrrusid
                        FROM ehr.t_ehr_rus_r6jr8pinus er
                        LEFT JOIN ehr.t_ehr_rus_ref_books rrb ON rrb.ehrrusrefbooksid = er.ehrrusrefbooks
                        WHERE er.idehr = compl.ehrrusr6jr8pinus
                          AND rrb.currversion = compl.ehrrusr6jr8pinusver
                        LIMIT 1
        ) ehr1 ON TRUE
    LEFT JOIN LATERAL ( SELECT er.ehrrusid
                        FROM ehr.t_ehr_rus_in6shid9f6 er
                        LEFT JOIN ehr.t_ehr_rus_ref_books rrb ON rrb.ehrrusrefbooksid = er.ehrrusrefbooks
                        WHERE er.idehr = compl.ehrrusin6shid9f6
                          AND rrb.currversion = compl.ehrrusin6shid9f6ver
                        LIMIT 1
        ) ehr2 ON TRUE
    LEFT JOIN LATERAL ( SELECT er.ehrrusid
                        FROM ehr.t_ehr_rus_jeds208en8 er
                        LEFT JOIN ehr.t_ehr_rus_ref_books rrb ON rrb.ehrrusrefbooksid = er.ehrrusrefbooks
                        WHERE er.idehr = compl.ehrrusjeds208en8
                          AND rrb.currversion = compl.ehrrusjeds208en8ver
                        LIMIT 1
        ) ehr3 ON TRUE
    WHERE ben.changetype = 1;
    
    SELECT XMLELEMENT
           (
               NAME "BSK_REGISTER",
               XMLFOREST
               (
                   XMLELEMENT(NAME "ID", rd.id),
                   XMLELEMENT(NAME "RegisterID", rd.registerid)
               )
           )::text
    INTO v_result
    FROM temp_bsk_direction rd;
    
    SELECT XMLELEMENT(
               NAME "BSK_REGISTER",
               XMLFOREST
               (
                   XMLELEMENT(NAME "header", XMLELEMENT(NAME "TYPE", v_operationtype)),
                   XMLELEMENT(
                       NAME "body",
                       XMLELEMENT(
                           NAME "PATIENT",
                           XMLFOREST
                           (rp.pac_id AS "RegisterID",
                            rp.lastname AS "LastName",
                            rp.firstname AS "FirstName",
                            rp.fathername AS "FatherName",
                            TO_CHAR(rp.birthday, 'DD.MM.YYYY') AS "Birthday",
                            rp.kod AS "Kod",
                            rp.docseries AS "DocSeries",
                            rp.docnumber AS "DocNumber",
                            esex.code AS "Sex",
                            rp.snils AS "Snils",
                            TO_CHAR(rp.datebegin, 'DD.MM.YYYY') AS "DateBegin",
                            TO_CHAR(rp.dateend, 'DD.MM.YYYY') AS "DateEnd",
                            TO_CHAR(rp.lasteditdate, 'DD.MM.YYYY') AS "LastEditDate",
                            rp.lpumain AS "Lpu",
                            rp.excludereason AS "ExcludeReason",
                            edoc.code AS "DocType",
                            TO_CHAR(rp.docissuedate, 'DD.MM.YYYY') AS "DocIssueDate",
                            rp.docissueorg AS "DocIssueOrg",
                            rp.enp AS "Enp",
                            rp.realfiasaddress AS "RealFiasAddress",
                            rp.realfiashome AS "RealFiasHome",
                            rp.realcity AS "RealCity",
                            rp.realcityprefix AS "RealCityPrefix",
                            rp.realstreet AS "RealStreet",
                            rp.realstreetprefix AS "RealStreetPrefix",
                            rp.realhouse AS "RealHouse",
                            rp.realbuild AS "RealBuild",
                            rp.realstruc AS "RealStruc",
                            rp.realroom AS "RealRoom",
                            TO_CHAR(rp.reallivestartdate, 'DD.MM.YYYY') AS "RealLiveStartDate",
                            TO_CHAR(rp.realliveenddate, 'DD.MM.YYYY') AS "RealLiveEndDate",
                            rp.legalfiasaddress AS "LegalFiasAddress",
                            rp.legalfiashome AS "LegalFiasHome",
                            rp.legalcity AS "LegalCity",
                            rp.legalcityprefix AS "LegalCityPrefix",
                            rp.legalstreet AS "LegalStreetPrefix",
                            rp.legalhouse AS "LegalHouse",
                            rp.legalbuild AS "LegalBuild",
                            rp.legalstruc AS "LegalStruc",
                            rp.legalroom AS "LegalRoom",
                            TO_CHAR(rp.legallivestartdate, 'DD.MM.YYYY') AS "LegalLiveStartDate",
                            TO_CHAR(rp.legalliveenddate, 'DD.MM.YYYY') AS "LegalLiveEndDate",
                            rp.citizenship AS "CitizenShip",
                            doc.codeehr AS "EhrRus5o5e88vhc6",
                            rrb.currversion AS "EhrRus5o5e88vhc6Ver",
                            (SELECT XMLAGG(
                                        XMLELEMENT(
                                            NAME "BENEFITS",
                                            XMLFOREST
                                            (
                                                rp.pac_id AS "RegisterID",
                                                rpb.bskregisterdataid AS "RegisterID",
                                                TO_CHAR(rpb.diagdate, 'DD.MM.YYYY') AS "DiagDate",
                                                rpb.document AS "Document",
                                                TO_CHAR(rpb.documentdate, 'DD.MM.YYYY') AS "DocumentDate",
                                                rpb.mkb AS "Mkb",
                                                b.benefitcode AS "BenefitCode",
                                                e.code AS "BenefitViews",
                                                TO_CHAR(rpb.begindate, 'DD.MM.YYYY') AS "BeginDate",
                                                TO_CHAR(rpb.enddate, 'DD.MM.YYYY') AS "EndDate",
                                                rpb.servicecode AS "ServiceCode",
                                                rpb.servicename AS "ServiceName",
                                                rpb.exclusionreason AS "ExclusionReason",
                                                TO_CHAR(rpb.estimatedddate, 'DD.MM.YYYY') AS "EstimatedDDate",
                                                TO_CHAR(rpb.dregistrationdate, 'DD.MM.YYYY') AS "DRegistrationDate",
                                                rpb.place AS "Place",
                                                rpb.lpumainto AS "LpuTo",
                                                rpb.lpumain AS "Lpu",
                                                TO_CHAR(rpb.closedate, 'DD.MM.YYYY') AS "CloseDate",
                                                (SELECT XMLAGG(
                                                            XMLELEMENT
                                                            (
                                                                NAME "MEDICAMENTS",
                                                                XMLFOREST
                                                                (
                                                                    meds.bskregisterdata AS "BenID",
                                                                    mnn.otherid AS "Mnn",
                                                                    TO_CHAR(meds.datestart, 'DD.MM.YYYY') AS
                                                                    "DateStart",
                                                                    TO_CHAR(meds.enddate, 'DD.MM.YYYY') AS "EndDate",
                                                                    meds.medicationforms AS "MedicationForms",
                                                                    meds.regiment AS "Regiment",
                                                                    meds.dose AS "Dose",
                                                                    meds.prepid AS "Prepid",
                                                                    meds.daylicoeff AS "DayliCoeff",
                                                                    dv.name AS "MeasureUnit",
                                                                    dv.viewtype AS "MeasureUnitType",
                                                                    meds.singledose AS "SingleDose",
                                                                    meds.multiplicity AS "Multiplicity",
                                                                    dvdv.name AS "DoseViewUnit",
                                                                    dvdv.viewtype AS "DoseViewUnitType"
                                                                )
                                                            )
                                                        )
                                                 FROM rds.t_bsk_register_medicaments meds
                                                 JOIN pharm.t_c_mnn mnn ON mnn.cmnnid = meds.cmnn
                                                 LEFT JOIN pharm.t_dose_view dv ON dv.code = meds.measureunit
                                                 LEFT JOIN pharm.t_dose_view dvdv ON dvdv.code = meds.doseview
                                                 WHERE meds.bskregisterdata = rpb.bskregisterdataid
                                                )
                                            )
                                        )
                                    )
                             FROM rds.t_bsk_register_data rpb
                             JOIN rds.t_benefits b ON b.benefitsid = rpb.benefits
                             JOIN vclib.t_enums e ON e.enumtypeid = b.benefitviews
                             WHERE rpb.bskregister = rp.bskregisterid
                            
                            )
                           )
                       )
                   )
               )
           )
    INTO p_xml
    FROM rds.t_bsk_register rp
    JOIN vclib.t_enums esex ON esex.enumid = rp.sex
    JOIN vclib.t_enums edoc ON edoc.enumid = rp.doctype
    LEFT JOIN ehr.t_ehr_rus_o00015 cs ON cs.ehrrusid = rp.citizenship
    LEFT JOIN ehr.t_ehr_rus_5o5e88vhc6 doc ON doc.ehrrusid = rp.ehrrus5o5e88vhc6
    LEFT JOIN ehr.t_ehr_rus_ref_books rrb ON rrb.ehrrusrefbooksid = doc.ehrrusrefbooks
    WHERE rp.pac_id = v_registerid;
    
    
    INSERT INTO
        rds.t_rabbit_send_object
    (rabbitsendobjectid,
     queuename,
     content,
     messageid,
     headers,
     contentencoding,
     type,
     userid,
     state,
     createdatetime,
     ownerid)
    SELECT gen_random_uuid(),
           s.servername,
           public.converttexttobinaryzip(v_result, ''),
           NULL,
           NULL,
           v_contentencoding,
           v_type,
           p_user,
           0,
           p_datetime,
           v_ownerid
    FROM rds.t_sync_servers s
    WHERE s.servername = p_queuename
      AND s.servertype = 0
      AND v_result IS NOT NULL;
    
    
    INSERT INTO
        rds.t_rabbit_send_object
    (rabbitsendobjectid,
     queuename,
     content,
     messageid,
     headers,
     contentencoding,
     type,
     userid,
     state,
     createdatetime,
     ownerid)
    SELECT gen_random_uuid(),
           s.servername,
           public.converttexttobinaryzip(p_xml::text, ''),
           NULL,
           NULL,
           v_contentencoding,
           v_type,
           p_user,
           0,
           p_datetime,
           v_ownerid
    FROM rds.t_sync_servers s
    WHERE s.servertype = 1;
    
    UPDATE e
    SET id = (SELECT MAX(b.pac_id) + 1 FROM rds.t_bsk_register b)
    FROM public.t_entity_ids e
    WHERE e.entname IN ('BSK_REGISTER', 'RDS.BSK_REGISTER');
END;
$$;