DROP PROCEDURE IF EXISTS sp_rr_patients;

CREATE OR REPLACE PROCEDURE public.sp_rr_patients(
    "Xml" text DEFAULT NULL,
    "ID" uuid DEFAULT NULL,
    "QueueName" varchar(100) DEFAULT 'RDSSync',
    "datetime" timestamp DEFAULT NULL,
    "User" uuid DEFAULT NULL
)
    LANGUAGE plpgsql
AS
$$
DECLARE
    p_xml             xml          := "Xml" :: text;
    p_queuename       varchar(100) := "QueueName";
    p_datetime        timestamp    := COALESCE(datetime, CURRENT_TIMESTAMP);
    p_user            uuid         := "User";
    p_id              uuid         := "ID";
    v_result          text ;
    v_operationtype   varchar(255);
    v_contentencoding varchar(255);
    v_type            varchar(255) := 'RR_PATIENT_BENEFIT';
    v_ownerid         uuid ;
    c_ftload          varchar(6)   := 'FTLoad';
BEGIN
    
    IF (p_xml IS NULL) THEN
        v_operationtype := c_ftload;
    END IF;
    
    IF (v_operationtype = c_ftload) THEN
        
        SELECT XMLELEMENT
               (
                   NAME "RR_PATIENT",
                   XMLFOREST
                   (
                       XMLELEMENT
                       (
                           NAME "header",
                           XMLELEMENT(NAME "TYPE", c_ftload)
                       ),
                       XMLELEMENT
                       (
                           NAME "body",
                           XMLELEMENT
                           (
                               NAME "RR_PATIENTS",
                               (SELECT XMLELEMENT
                                       (
                                           NAME "RR_PATIENT",
                                           XMLFOREST
                                           (
                                               XMLELEMENT(NAME "RrPatientID", rp.rrpatientid),
                                               XMLELEMENT(NAME "Snils", rp.snils),
                                               XMLELEMENT(NAME "DocSeries", rp.docseries),
                                               XMLELEMENT(NAME "DocNumber", rp.docnumber),
                                               XMLELEMENT(NAME "LastName", rp.lastname),
                                               XMLELEMENT(NAME "FirstName", rp.firstname),
                                               XMLELEMENT(NAME "FatherName", rp.fathername),
                                               XMLELEMENT(NAME "Birthday", TO_CHAR(rp.birthday, 'YYYY.MM.DD')),
                                               XMLELEMENT(NAME "DateBegin", TO_CHAR(rp.datebegin, 'YYYY.MM.DD')),
                                               XMLELEMENT(NAME "DateEnd", TO_CHAR(rp.dateend, 'YYYY.MM.DD')),
                                               XMLELEMENT(NAME "LastEditDate", TO_CHAR(rp.lasteditdate, 'YYYY.MM.DD')),
                                               XMLELEMENT(NAME "Sex", esex.code),
                                               XMLELEMENT(NAME "Lpu", rp.lasteditlpu),
                                               XMLELEMENT(NAME "ExcludeReason", rp.excludereason),
                                               XMLELEMENT(NAME "DocType", rp.tipd),
                                               XMLELEMENT(NAME "Kod", rp.kod),
                                               XMLELEMENT(NAME "KodAdr", rp.kodadr),
                                               XMLELEMENT(NAME "DAT_PROV", TO_CHAR(rp.dat_prov, 'YYYY.MM.DD')),
                                               XMLELEMENT(NAME "BirthSurname", rp.birthsurname),
                                               XMLELEMENT(NAME "LpuFirst", rp.lpumainfirst),
                                               XMLELEMENT(NAME "SOST_R", rp.sost_r)
                                           )
                                       )
                                FROM rds.t_rr_patient rp
                                JOIN vclib.t_enums esex ON esex.enumid = rp.sex
                               )
                           )
                       )
                   )
               )::TEXT
        INTO v_result;
        
        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 (p_queuename IS NOT NULL AND s.servername = p_queuename)
           OR (p_queuename IS NULL AND s.servertype IN (0, 1));
    
    END IF;

END;
$$
