DROP PROCEDURE IF EXISTS rds.sp_rds_ftload_dloaccesspost;

CREATE OR REPLACE PROCEDURE rds.sp_rds_ftload_dloaccesspost(
    "Xml" text DEFAULT NULL::text,
    "IsNew" integer DEFAULT 1,
    "ID" uuid DEFAULT NULL::uuid,
    "QueueName" character varying DEFAULT NULL::character varying,
    "dateTime" timestamp without time zone DEFAULT NULL::timestamp without time zone,
    "User" uuid DEFAULT NULL::uuid
) AS
$$
DECLARE
    v_result          text;
    v_contentencoding varchar(255) := 'utf-8';
    v_type            varchar(255) := 'DLO_ACCESSED_POSTS_FTLOAD';
    p_xml             xml          := "Xml"::xml;
    p_isnew           integer      := "IsNew";
    p_id              uuid         := "ID";
    p_queuename       varchar(100) := "QueueName";
    p_datetime        timestamp    := "dateTime";
    p_user            uuid         := "User";
    c_ftload          varchar(10)  := 'FTLoad';

BEGIN

    p_datetime = COALESCE(p_datetime, CURRENT_TIMESTAMP(0));

    SELECT '<DLO_ACCESSED_POST><header><TYPE>' || c_ftload || '</TYPE></header><body><DLO_ACCESSED_POSTS>' ||
           COALESCE(STRING_AGG(
                        XMLSERIALIZE(CONTENT tt.xml_elem AS TEXT),
                        ''
                    ), '') ||
           '</DLO_ACCESSED_POSTS></body></DLO_ACCESSED_POST>'
    INTO v_result
    FROM (SELECT XMLELEMENT(
                     NAME "DLO_ACCESSED_POST",
                     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"
                     )
                 ) AS xml_elem
          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
         ) tt;

    INSERT INTO
        rds.t_rabbit_send_object
    (rabbitsendobjectid,
     queuename,
     content,
     messageid,
     headers,
     contentencoding,
     type,
     userid,
     state,
     createdatetime,
     ownerid)
    VALUES (gen_random_uuid(),
            p_queuename,
            converttexttobinaryzip(v_result, ''),
            NULL,
            NULL,
            v_contentencoding,
            v_type,
            p_user,
            0,
            p_datetime,
            NULL);
END;
$$
    LANGUAGE plpgsql;