DROP PROCEDURE IF EXISTS public.sp_getpalliativeregister;

CREATE OR REPLACE PROCEDURE public.sp_getpalliativeregister(
    "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"::xml;
    p_id              uuid         := "ID";
    p_queuename       varchar(100) := "QueueName";
    p_datetime        timestamp    := COALESCE("dateTime", CURRENT_TIMESTAMP);
    p_user            uuid         := "User";
    v_result          text;
    v_contentencoding varchar(255) := 'utf-8';
    v_type            varchar(255) := 'PALLIATIVE_REGISTER';
    v_operationtype   varchar(255);
    v_ownerid         uuid;
    c_insert          varchar(6)   := 'Insert';
    c_update          varchar(6)   := 'Update';
    c_delete          varchar(6)   := 'Delete';
    c_ftload          varchar(6)   := 'FTLoad';
BEGIN
    
    IF (v_contentencoding IS NULL) THEN
        v_operationtype = c_ftload;
    END IF;
    
    IF (v_operationtype = c_ftload) THEN
        
        SELECT '<PALLIATIVE_REGISTER><header><TYPE>' || v_operationtype ||
               '</TYPE></header><body><PALLIATIVE_REGISTERS>' ||
               COALESCE(STRING_AGG(
                            XMLSERIALIZE(CONTENT patient_xml AS TEXT),
                            ''
                        ), '') ||
               '</PALLIATIVE_REGISTERS></body></PALLIATIVE_REGISTER>'
        INTO v_result
        FROM (SELECT 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, 'YYYY.MM.DD') 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, 'YYYY.MM.DD') AS "DateBegin",
                             TO_CHAR(rp.dateend, 'YYYY.MM.DD') AS "DateEnd",
                             TO_CHAR(rp.lasteditdate, 'YYYY.MM.DD') AS "LastEditDate",
                             rp.lpumain AS "Lpu",
                             rp.excludereason AS "ExcludeReason",
                             edoc.code AS "DocType",
                             (SELECT XMLAGG(
                                         XMLELEMENT(
                                             NAME "BENEFITS",
                                             XMLFOREST(
                                                 rp.pac_id AS "RegisterID",
                                                 rpb.document AS "Document",
                                                 TO_CHAR(rpb.documentdate, 'YYYY.MM.DD') AS
                                                 "DocumentDate",
                                                 rpb.mkb AS "Mkb",
                                                 b.benefitcode AS "BenefitCode",
                                                 e.code AS "BenefitViews",
                                                 TO_CHAR(rpb.begindate, 'YYYY.MM.DD') AS "BeginDate",
                                                 TO_CHAR(rpb.enddate, 'YYYY.MM.DD') AS "EndDate"
                                             )
                                         )
                                     )
                              FROM rds.t_palliative_register_data rpb
                              JOIN rds.t_benefits                 b ON b.benefitsid = rpb.benefits
                              JOIN vclib.t_enums                  e ON e.enumid = b.benefitviews
                              WHERE rpb.palliativeregister = rp.palliativeregisterid
                             ) AS "BENEFITS"
                         )
                     ) AS patient_xml
              FROM rds.t_palliative_register rp
              JOIN vclib.t_enums             esex ON esex.enumid = rp.sex
              JOIN vclib.t_enums             edoc ON edoc.enumid = rp.doctype
             ) AS palliative_patients;
        
        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;
$$