DROP PROCEDURE IF EXISTS rds.sp_rds_dloaccesspostdirection;

CREATE OR REPLACE PROCEDURE rds.sp_rds_dloaccesspostdirection(
    "Xml" text DEFAULT NULL,
    "IsNew" boolean DEFAULT TRUE,
    "ID" uuid DEFAULT NULL,
    "QueueName" character varying DEFAULT NULL,
    "dateTime" timestamp without time zone DEFAULT NULL,
    "User" uuid DEFAULT NULL
)
    LANGUAGE plpgsql
AS
$$
DECLARE
    v_result             text;
    v_contentencoding    varchar(255) := 'utf-8';
    v_type               varchar(255) := 'DLO_ACCESSED_POSTS_DIRECTION';
    v_operationtype      integer;
    v_insert             integer      := 0;
    v_directionstate     integer;
    v_ds_accept          integer      = 1;
    v_true               varchar(4)   := 'true';
    v_false              varchar(5)   := 'false';
    v_postid             uuid;
    v_postcode           varchar(255);
    v_snils              varchar(14);
    v_speciality         varchar(3);
    v_posttype           varchar(4);
    v_lastname           varchar(40);
    v_firstname          varchar(40);
    v_beginworkdate      timestamp without time zone;
    v_needconfirmation   varchar(6);
    v_opendirectioncount integer;
    p_xml                xml          := "Xml"::xml;
    p_isnew              boolean      := "IsNew"::boolean;
    p_id                 uuid         := "ID";
    p_queuename          varchar(100) := "QueueName";
    p_datetime           timestamp    := "dateTime";
    p_user               uuid         := "User";

BEGIN
    
    p_datetime := COALESCE(p_datetime, CURRENT_TIMESTAMP(0));
    
    IF p_xml IS NOT NULL THEN
        CREATE TEMPORARY TABLE temp_dlo_accessed_posts_direction AS
            SELECT (XPATH('/DLO_ACCESSED_POSTS_DIRECTION/DLO_ACCESSED_POSTS_DIRECTION_ID/text()',
                          x))[1]::text::uuid                                                                AS id,
                   (XPATH('/DLO_ACCESSED_POSTS_DIRECTION/DIRECTION_TYPE/text()', x))[1]::text::integer      AS directiontype,
                   (XPATH('/DLO_ACCESSED_POSTS_DIRECTION/DIRECTION_STATE/text()', x))[1]::text::integer     AS directionstate,
                   (XPATH('/DLO_ACCESSED_POSTS_DIRECTION/LPU_MAIN/text()', x))[1]::varchar(6)               AS lpumain,
                   (XPATH('/DLO_ACCESSED_POSTS_DIRECTION/SNILS/text()', x))[1]::varchar(14)                 AS snils,
                   (XPATH('/DLO_ACCESSED_POSTS_DIRECTION/POST_CODE/text()', x))[1]::varchar(255)            AS postcode,
                   (XPATH('/DLO_ACCESSED_POSTS_DIRECTION/DOC_CODE/text()', x))[1]::varchar(11)              AS doccode,
                   (XPATH('/DLO_ACCESSED_POSTS_DIRECTION/LAST_NAME/text()', x))[1]::varchar(40)             AS lastname,
                   (XPATH('/DLO_ACCESSED_POSTS_DIRECTION/FIRST_NAME/text()', x))[1]::varchar(40)            AS firstname,
                   (XPATH('/DLO_ACCESSED_POSTS_DIRECTION/FATHER_NAME/text()', x))[1]::varchar(40)           AS fathername,
                   (XPATH('/DLO_ACCESSED_POSTS_DIRECTION/SPECIALITY/text()', x))[1]::varchar(3)             AS speciality,
                   (XPATH('/DLO_ACCESSED_POSTS_DIRECTION/POST_TYPE/text()', x))[1]::varchar(4)              AS posttype,
                   (XPATH('/DLO_ACCESSED_POSTS_DIRECTION/CATEGORY/text()', x))[1]::varchar(4)::integer      AS category,
                   TO_TIMESTAMP((XPATH('/DLO_ACCESSED_POSTS_DIRECTION/BEGIN_ACCESS_DATE/text()', x))[1]::text,
                                'YYYY-MM-DD')                                                               AS beginaccessdate,
                   TO_TIMESTAMP((XPATH('/DLO_ACCESSED_POSTS_DIRECTION/END_ACCESS_DATE/text()', x))[1]::text,
                                'YYYY-MM-DD')                                                               AS endaccessdate,
                   TO_TIMESTAMP((XPATH('/DLO_ACCESSED_POSTS_DIRECTION/BEGIN_ACCESS_DATE/text()', x))[1]::text,
                                'YYYY-MM-DD')                                                               AS beginworkdate,
                   TO_TIMESTAMP((XPATH('/DLO_ACCESSED_POSTS_DIRECTION/CERTIFICATE_DATE/text()', x))[1]::text,
                                'YYYY-MM-DD')                                                               AS certificatedate,
                   (XPATH('/DLO_ACCESSED_POSTS_DIRECTION/MEDICAL_SPECIALITY_CODE/text()',
                          x))[1]::varchar(10)                                                               AS medicalspecialitycode,
                   TO_TIMESTAMP((XPATH('/DLO_ACCESSED_POSTS_DIRECTION/EXPULSION_DATE/text()', x))[1]::text,
                                'YYYY-MM-DD')                                                               AS expulsiondate,
                   (XPATH('/DLO_ACCESSED_POSTS_DIRECTION/EhrRus7PJSAM6GJM/text()', x))[1]::varchar(10)::int AS ehrrus7pjsam6gjm,
                   (XPATH('/DLO_ACCESSED_POSTS_DIRECTION/EhrRus7PJSAM6GJMVer/text()', x))[1]::varchar(8)    AS ehrrus7pjsam6gjmver,
                   (XPATH('/DLO_ACCESSED_POSTS_DIRECTION/EhrRusLS9R0Q4VR4/text()', x))[1]::varchar(10)::int AS ehrrusls9r0q4vr4,
                   (XPATH('/DLO_ACCESSED_POSTS_DIRECTION/EhrRusLS9R0Q4VR4Ver/text()', x))[1]::varchar(8)    AS ehrrusls9r0q4vr4ver
            FROM (SELECT p_xml AS x
                 ) t;
        
        IF (EXISTS (SELECT 1
                    FROM temp_dlo_accessed_posts_direction dir
                    LEFT JOIN LATERAL (
                        SELECT ehr.idehr, rrb.currversion
                        FROM ehr.t_ehr_rus_7pjsam6gjm ehr
                        JOIN ehr.t_ehr_rus_ref_books rrb ON rrb.ehrrusrefbooksid = ehr.ehrrusrefbooks
                        WHERE ehr.idehr = dir.ehrrus7pjsam6gjm
                          AND rrb.currversion = dir.ehrrus7pjsam6gjmver
                        LIMIT 1
                        ) e ON TRUE
                    WHERE dir.ehrrus7pjsam6gjm IS NOT NULL
                      AND dir.ehrrus7pjsam6gjmver IS NOT NULL
                      AND e.idehr IS NULL
                      AND e.currversion IS NULL
                   ))
        THEN
            RAISE EXCEPTION 'Установлены некорректные значения EhrRus7pjsam6gjm и EhrRus7pjsam6gjmVer ';
        END IF;
        
        IF (EXISTS (SELECT 1
                    FROM temp_dlo_accessed_posts_direction dir
                    LEFT JOIN LATERAL (
                        SELECT ehr.idehr, rrb.currversion
                        FROM ehr.t_ehr_rus_ls9r0q4vr4 ehr
                        JOIN ehr.t_ehr_rus_ref_books rrb ON rrb.ehrrusrefbooksid = ehr.ehrrusrefbooks
                        WHERE ehr.idehr = dir.ehrrusls9r0q4vr4
                          AND rrb.currversion = dir.ehrrusls9r0q4vr4ver
                        LIMIT 1
                        ) e ON TRUE
                    WHERE dir.ehrrusls9r0q4vr4 IS NOT NULL
                      AND dir.ehrrusls9r0q4vr4ver IS NOT NULL
                      AND e.idehr IS NULL
                      AND e.currversion IS NULL
                   ))
        THEN
            RAISE EXCEPTION 'Установлены некорректные значения EhrRusLS9R0Q4VR4 и EhrRusLS9R0Q4VR4Ver ';
        END IF;
        
        SELECT p.parametervalue
        INTO
            v_needconfirmation
        FROM vclib.t_holder_parameter p
        JOIN vclib.t_holder h ON p.holder = h.holderid
        WHERE h.name = 'RegistryUpdateHolder'
          AND p.parametername = 'NeedСonfirmation';
        
        IF v_needconfirmation NOT IN (v_true, v_false) THEN
            RAISE EXCEPTION 'Значение параметра v_NeedConfirmation не установлено';
        END IF;
        
        SELECT d.snils,
               d.speciality,
               d.posttype,
               d.lastname,
               d.firstname,
               d.beginworkdate,
               d.postcode,
               d.directiontype
        INTO
            v_snils,
            v_speciality,
            v_posttype,
            v_lastname,
            v_firstname,
            v_beginworkdate,
            v_postcode,
            v_operationtype
        FROM temp_dlo_accessed_posts_direction d;
        
        IF v_snils IS NULL THEN
            RAISE EXCEPTION 'Не установлен снилс SNILS';
        END IF;
        
        IF v_postcode IS NULL THEN
            RAISE EXCEPTION 'Не установлен код врача POST_CODE';
        END IF;
        
        IF v_needconfirmation = v_true THEN
            SELECT COUNT(*)::integer
            INTO v_opendirectioncount
            FROM rds.t_dlo_accessed_posts_direction d
            JOIN temp_dlo_accessed_posts_direction dlo
                ON d.postcode = d.postcode AND d.snils = dlo.snils AND d.directionstate = 0
            JOIN rds.t_lpu_main lpu ON lpu.mcod = dlo.lpumain;
            
            IF v_opendirectioncount > 0 THEN
                RAISE EXCEPTION 'Для данного врача уже существует открытое направление';
            END IF;
        END IF;
        IF (v_needconfirmation = v_false) THEN
            
            INSERT INTO
                rds.t_dlo_accessed_posts_direction (dloaccessedpostsdirectionid,
                                                    directiontype,
                                                    directionstate,
                                                    lpumain,
                                                    snils,
                                                    postcode,
                                                    doccode,
                                                    lastname,
                                                    firstname,
                                                    fathername,
                                                    speciality,
                                                    posttype,
                                                    category,
                                                    beginaccessdate,
                                                    endaccessdate,
                                                    beginworkdate,
                                                    medicalspecialitycode,
                                                    expulsiondate,
                                                    createdatetime,
                                                    ehrrus7pjsam6gjm,
                                                    ehrrusls9r0q4vr4)
            SELECT dlo.id,
                   dlo.directiontype,
                   1::integer, -- принято
                   dlo.lpumain,
                   dlo.snils,
                   dlo.postcode,
                   dlo.doccode,
                   dlo.lastname,
                   dlo.firstname,
                   dlo.fathername,
                   dlo.speciality,
                   dlo.posttype,
                   dlo.category,
                   dlo.beginaccessdate,
                   dlo.endaccessdate,
                   dlo.beginworkdate,
                   dlo.medicalspecialitycode,
                   dlo.expulsiondate,
                   p_datetime,
                   er1.ehrrusid,
                   er2.ehrrusid
            FROM temp_dlo_accessed_posts_direction dlo
            LEFT JOIN LATERAL (
                SELECT er.ehrrusid
                FROM ehr.t_ehr_rus_7pjsam6gjm er
                JOIN ehr.t_ehr_rus_ref_books rrb ON rrb.ehrrusrefbooksid = er.ehrrusrefbooks
                WHERE er.idehr = dlo.ehrrus7pjsam6gjm
                  AND rrb.currversion = dlo.ehrrus7pjsam6gjmver
                LIMIT 1) er1 ON TRUE
            LEFT JOIN LATERAL (
                SELECT er.ehrrusid
                FROM ehr.t_ehr_rus_ls9r0q4vr4 er
                JOIN ehr.t_ehr_rus_ref_books rrb ON rrb.ehrrusrefbooksid = er.ehrrusrefbooks
                WHERE er.idehr = dlo.ehrrusls9r0q4vr4
                  AND rrb.currversion = dlo.ehrrusls9r0q4vr4ver
                LIMIT 1) er2 ON TRUE;
            
            IF v_operationtype = v_insert THEN
                v_postid := gen_random_uuid();
                p_id := v_postid;
                
                INSERT INTO
                    rds.t_dlo_accessed_posts (dloaccessedpostsid,
                                              beginaccessdate,
                                              beginworkdate,
                                              category,
                                              certificatedate,
                                              doccode,
                                              endaccessdate,
                                              fathername,
                                              firstname,
                                              lastname,
                                              lpumain,
                                              medicalspecialitycode,
                                              postcode,
                                              posttype,
                                              snils,
                                              speciality,
                                              ehrrus7pjsam6gjm,
                                              ehrrusls9r0q4vr4)
                SELECT v_postid,
                       dir.beginaccessdate,
                       dir.beginworkdate,
                       dir.category,
                       p_datetime,
                       dir.doccode,
                       dir.endaccessdate,
                       dir.fathername,
                       dir.firstname,
                       dir.lastname,
                       dir.lpumain,
                       dir.medicalspecialitycode,
                       dir.postcode,
                       dir.posttype,
                       dir.snils,
                       dir.speciality,
                       dir.ehrrus7pjsam6gjm,
                       dir.ehrrusls9r0q4vr4
                FROM rds.t_dlo_accessed_posts_direction dir
                JOIN temp_dlo_accessed_posts_direction dlo ON dlo.id = dir.dloaccessedpostsdirectionid;
            
            ELSE
                
                SELECT p.dloaccessedpostsid,
                       dir.id
                INTO
                    v_postid,
                    p_id
                FROM rds.t_dlo_accessed_posts p
                JOIN temp_dlo_accessed_posts_direction dir ON p.postcode = dir.postcode
                WHERE p.expulsiondate IS NULL;
                
                UPDATE p
                SET expulsiondate = dir.expulsiondate
                FROM rds.t_dlo_accessed_posts p
                JOIN rds.t_dlo_accessed_posts_direction dir ON dir.dloaccessedpostsdirectionid = p_id
                    AND p.dloaccessedpostsid = v_postid;
            
            END IF;
            
            SELECT XMLELEMENT
                   (
                       NAME "DLO_ACCESSED_POSTS",
                       XMLFOREST
                       (
                           p.dloaccessedpostsid AS "ID",
                           p.postcode AS "POST_CODE",
                           p.doccode AS "DOC_CODE",
                           p.lastname AS "LAST_NAME",
                           p.firstname AS "FIRST_NAME",
                           p.fathername AS "FATHER_NAME",
                           p.category AS "CATEGORY",
                           TO_CHAR(p.beginaccessdate, 'YYYY-MM-DD') AS "BEGIN_ACCESS_DATE",
                           TO_CHAR(p.endaccessdate, 'YYYY-MM-DD') AS "END_ACCESS_DATE",
                           TO_CHAR(p.beginworkdate, 'YYYY-MM-DD') AS "BEGIN_WORK_DATE",
                           TO_CHAR(p.certificatedate, 'YYYY-MM-DD') AS "CERTIFICATE_DATE",
                           p.medicalspecialitycode AS "MEDICAL_SPECIALITY_CODE",
                           TO_CHAR(p.expulsiondate, 'YYYY-MM-DD') AS "EXPULSION_DATE",
                           p.snils AS "SNILS",
                           p.posttype AS "POST_TYPE",
                           p.speciality AS "SPECIALITY",
                           p.lpumain AS "LPU_MAIN",
                           e1.idehr AS "EhrRus7PJSAM6GJM",
                           rrb1.currversion AS "EhrRus7PJSAM6GJMVer",
                           e2.idehr AS "EhrRusLS9R0Q4VR4",
                           rrb2.currversion AS "EhrRusLS9R0Q4VR4Ver"
                       )
                   )::text
            INTO v_result
            FROM rds.t_dlo_accessed_posts p
            LEFT JOIN ehr.t_ehr_rus_7pjsam6gjm e1 ON p.ehrrus7pjsam6gjm = e1.ehrrusid
            LEFT JOIN ehr.t_ehr_rus_ref_books rrb1 ON rrb1.ehrrusrefbooksid = e1.ehrrusrefbooks
            LEFT JOIN ehr.t_ehr_rus_ls9r0q4vr4 e2 ON p.ehrrusls9r0q4vr4 = e2.ehrrusid
            LEFT JOIN ehr.t_ehr_rus_ref_books rrb2 ON rrb2.ehrrusrefbooksid = e2.ehrrusrefbooks
            WHERE p.dloaccessedpostsid = v_postid;
            
            INSERT INTO
                rds.t_rabbit_send_object
            (rabbitsendobjectid,
             queuename,
             content,
             messageid,
             headers,
             contentencoding,
             type,
             userid,
             state,
             createdatetime,
             ownerid)
            SELECT gen_random_uuid(),
                   s.servername,
                   converttexttobinaryzip(v_result::text, ''::text),
                   NULL,
                   NULL,
                   v_contentencoding,
                   'DLO_ACCESSED_POSTS',
                   p_user,
                   0::int,
                   p_datetime,
                   p_id
            FROM rds.t_sync_servers s
            WHERE s.servertype = 1::integer;
            
            SELECT XMLELEMENT
                   (
                       NAME "DLO_ACCESSED_POSTS",
                       XMLFOREST
                       (
                           dap.dloaccessedpostsdirectionid AS "DLO_ACCESSED_POSTS_DIRECTION_ID",
                           dap.directiontype AS "DIRECTION_TYPE",
                           dap.directionstate AS "DIRECTION_STATE",
                           dap.postcode AS "POST_CODE",
                           dap.expulsiondate AS "EXPULSION_DATE",
                           dap.refusingreason AS "REFUSING_REASON"
                       )
                   )::text
            INTO v_result
            FROM rds.t_dlo_accessed_posts_direction dap
            JOIN temp_dlo_accessed_posts_direction dlo ON dap.dloaccessedpostsdirectionid = dlo.id;
            
            INSERT INTO
                rds.t_rabbit_send_object
            (rabbitsendobjectid,
             queuename,
             content,
             messageid,
             headers,
             contentencoding,
             type,
             userid,
             state,
             createdatetime,
             ownerid)
            SELECT gen_random_uuid(),
                   s.servername,
                   converttexttobinaryzip(v_result::text, ''::text),
                   NULL,
                   NULL,
                   v_contentencoding,
                   v_type,
                   p_user,
                   0::int,
                   p_datetime,
                   p_id
            FROM rds.t_sync_servers s
            WHERE s.servertype = 0::integer;
        
        ELSE
            
            INSERT INTO
                rds.t_dlo_accessed_posts_direction (dloaccessedpostsdirectionid,
                                                    directiontype,
                                                    directionstate,
                                                    lpumain,
                                                    snils,
                                                    postcode,
                                                    doccode,
                                                    lastname,
                                                    firstname,
                                                    fathername,
                                                    speciality,
                                                    posttype,
                                                    category,
                                                    beginaccessdate,
                                                    endaccessdate,
                                                    beginworkdate,
                                                    medicalspecialitycode,
                                                    expulsiondate,
                                                    createdatetime,
                                                    ehrrus7pjsam6gjm,
                                                    ehrrusls9r0q4vr4)
            SELECT dlo.id,
                   dlo.directiontype,
                   0::integer, -- принято
                   dlo.lpumain,
                   dlo.snils,
                   dlo.postcode,
                   dlo.doccode,
                   dlo.lastname,
                   dlo.firstname,
                   dlo.fathername,
                   dlo.speciality,
                   dlo.posttype,
                   dlo.category,
                   dlo.beginaccessdate,
                   dlo.endaccessdate,
                   dlo.beginworkdate,
                   dlo.medicalspecialitycode,
                   dlo.expulsiondate,
                   p_datetime,
                   er1.ehrrusid,
                   er2.ehrrusid
            FROM temp_dlo_accessed_posts_direction dlo
            LEFT JOIN LATERAL (
                SELECT er.ehrrusid
                FROM ehr.t_ehr_rus_7pjsam6gjm er
                JOIN ehr.t_ehr_rus_ref_books rrb ON rrb.ehrrusrefbooksid = er.ehrrusrefbooks
                WHERE er.idehr = dlo.ehrrus7pjsam6gjm
                  AND rrb.currversion = dlo.ehrrus7pjsam6gjmver
                LIMIT 1) er1 ON TRUE
            LEFT JOIN LATERAL (
                SELECT er.ehrrusid
                FROM ehr.t_ehr_rus_ls9r0q4vr4 er
                JOIN ehr.t_ehr_rus_ref_books rrb ON rrb.ehrrusrefbooksid = er.ehrrusrefbooks
                WHERE er.idehr = dlo.ehrrusls9r0q4vr4
                  AND rrb.currversion = dlo.ehrrusls9r0q4vr4ver
                LIMIT 1) er2 ON TRUE;
        
        END IF;
    
    
    ELSE
        
        SELECT dir.directiontype,
               dir.directionstate
        INTO
            v_operationtype,
            v_directionstate
        FROM rds.t_dlo_accessed_posts_direction dir
        WHERE dir.dloaccessedpostsdirectionid = p_id;
        
        IF v_directionstate = v_ds_accept THEN
            IF v_operationtype = v_insert THEN
                v_postid = gen_random_uuid();
                
                INSERT INTO
                    rds.t_dlo_accessed_posts (dloaccessedpostsid,
                                              beginaccessdate,
                                              beginworkdate,
                                              category,
                                              certificatedate,
                                              doccode,
                                              endaccessdate,
                                              fathername,
                                              firstname,
                                              lastname,
                                              lpumain,
                                              medicalspecialitycode,
                                              postcode,
                                              posttype,
                                              snils,
                                              speciality,
                                              ehrrus7pjsam6gjm,
                                              ehrrusls9r0q4vr4)
                SELECT v_postid,
                       dir.beginaccessdate,
                       dir.beginworkdate,
                       dir.category,
                       p_datetime,
                       dir.doccode,
                       dir.endaccessdate,
                       dir.fathername,
                       dir.firstname,
                       dir.lastname,
                       dir.lpumain,
                       dir.medicalspecialitycode,
                       dir.postcode,
                       dir.posttype,
                       dir.snils,
                       dir.speciality,
                       dir.ehrrus7pjsam6gjm,
                       dir.ehrrusls9r0q4vr4
                
                FROM rds.t_dlo_accessed_posts_direction dir
                WHERE dir.dloaccessedpostsdirectionid = p_id;
            ELSE
                
                SELECT p.dloaccessedpostsid
                INTO
                    v_postid
                FROM rds.t_dlo_accessed_posts p
                JOIN rds.t_dlo_accessed_posts_direction dir ON p.postcode = dir.postcode
                    AND p.expulsiondate IS NULL
                    AND dir.dloaccessedpostsdirectionid = p_id;
                
                UPDATE rds.t_dlo_accessed_posts p
                SET expulsiondate = dir.expulsiondate
                FROM rds.t_dlo_accessed_posts_direction dir
                WHERE p.postcode = dir.postcode
                  AND p.expulsiondate IS NULL
                  AND dir.dloaccessedpostsdirectionid = p_id;
            
            
            END IF;
            
            
            SELECT XMLELEMENT(
                       NAME "DLO_ACCESSED_POSTS",
                       XMLFOREST
                       (
                           p.dloaccessedpostsid AS "ID",
                           p.postcode AS "POST_CODE",
                           p.doccode AS "DOC_CODE",
                           p.lastname AS "LAST_NAME",
                           p.firstname AS "FIRST_NAME",
                           p.fathername AS "FATHER_NAME",
                           p.category AS "CATEGORY",
                           TO_CHAR(p.beginaccessdate, 'YYYY-MM-DD') AS "BEGIN_ACCESS_DATE",
                           TO_CHAR(p.endaccessdate, 'YYYY-MM-DD') AS "END_ACCESS_DATE",
                           TO_CHAR(p.beginworkdate, 'YYYY-MM-DD') AS "BEGIN_WORK_DATE",
                           TO_CHAR(p.certificatedate, 'YYYY-MM-DD') AS "CERTIFICATE_DATE",
                           p.medicalspecialitycode AS "MEDICAL_SPECIALITY_CODE",
                           TO_CHAR(p.expulsiondate, 'YYYY-MM-DD') AS "EXPULSION_DATE",
                           p.snils AS "SNILS",
                           p.posttype AS "POST_TYPE",
                           p.speciality AS "SPECIALITY",
                           p.lpumain AS "LPU_MAIN",
                           e1.idehr AS "EhrRus7PJSAM6GJM",
                           rrb1.currversion AS "EhrRus7PJSAM6GJMVer",
                           e2.idehr AS "EhrRusLS9R0Q4VR4",
                           rrb2.currversion AS "EhrRusLS9R0Q4VR4Ver"
                       )
                   )::text
            INTO v_result
            FROM rds.t_dlo_accessed_posts p
            LEFT JOIN ehr.t_ehr_rus_7pjsam6gjm e1 ON p.ehrrus7pjsam6gjm = e1.ehrrusid
            LEFT JOIN ehr.t_ehr_rus_ref_books rrb1 ON rrb1.ehrrusrefbooksid = e1.ehrrusrefbooks
            LEFT JOIN ehr.t_ehr_rus_ls9r0q4vr4 e2 ON p.ehrrusls9r0q4vr4 = e2.ehrrusid
            LEFT JOIN ehr.t_ehr_rus_ref_books rrb2 ON rrb2.ehrrusrefbooksid = e2.ehrrusrefbooks
            WHERE p.dloaccessedpostsid = v_postid;
            
            INSERT INTO
                rds.t_rabbit_send_object
            (rabbitsendobjectid,
             queuename,
             content,
             messageid,
             headers,
             contentencoding,
             type,
             userid,
             state,
             createdatetime,
             ownerid)
            SELECT gen_random_uuid(),
                   s.servername,
                   converttexttobinaryzip(v_result, ''),
                   NULL,
                   NULL,
                   v_contentencoding,
                   'DLO_ACCESSED_POSTS',
                   p_user,
                   0,
                   p_datetime,
                   p_id
            FROM rds.t_sync_servers s
            WHERE s.servertype = 1;
            
            SELECT XMLELEMENT(
                       NAME "DLO_ACCESSED_POSTS_DIRECTION",
                       XMLFOREST
                       (
                           dap.dloaccessedpostsdirectionid AS "DLO_ACCESSED_POSTS_DIRECTION_ID",
                           dap.directiontype AS "DIRECTION_TYPE",
                           dap.directionstate AS "DIRECTION_STATE",
                           dap.postcode AS "POST_CODE",
                           TO_CHAR(dap.expulsiondate, 'YYYY-MM-DD') AS "EXPULSION_DATE",
                           dap.refusingreason AS "REFUSING_REASON"
                       )
                   )::text
            INTO v_result
            FROM rds.t_dlo_accessed_posts_direction dap
            WHERE dap.dloaccessedpostsdirectionid = p_id;
            
            INSERT INTO
                rds.t_rabbit_send_object
            (rabbitsendobjectid,
             queuename,
             content,
             messageid,
             headers,
             contentencoding,
             type,
             userid,
             state,
             createdatetime,
             ownerid)
            SELECT gen_random_uuid(),
                   s.servername,
                   converttexttobinaryzip(v_result, ''),
                   NULL,
                   NULL,
                   v_contentencoding,
                   v_type,
                   p_user,
                   0,
                   p_datetime,
                   p_id
            FROM rds.t_sync_servers s
            WHERE s.servertype = 0;
        
        END IF;
    END IF;
END
$$
