CREATE OR REPLACE PROCEDURE public.sp_rr_patientbenefits(
    "Xml" text DEFAULT NULL::text,
    "ID" uuid DEFAULT NULL::uuid,
    "QueueName" character varying DEFAULT 'RDSSync'::character varying,
    "dateTime" timestamp without time zone DEFAULT NULL::timestamp without time zone,
    "User" uuid DEFAULT NULL::uuid
)
    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";
    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
        
        IF (p_id IS NULL) THEN
            v_operationtype := c_ftload;
        END IF;
        
        IF (v_operationtype = c_ftload) THEN
            
            SELECT '<RR_PATIENT_BENEFITS><header><TYPE>' || c_ftload || '</TYPE></header><body><RR_PATIENT_BENEFITS>' ||
                   COALESCE(STRING_AGG(
                                XMLSERIALIZE(CONTENT benefit_xml AS TEXT),
                                ''
                            ), '') ||
                   '</RR_PATIENT_BENEFITS></body></RR_PATIENT_BENEFITS>'
            INTO v_result
            FROM (SELECT XMLELEMENT(
                             NAME "RR_PATIENT_BENEFIT",
                             XMLFOREST(
                                 rrp.rrpatientid AS "RrPatient",
                                 TO_CHAR(rpb.diagdate, 'YYYY.MM.DD') AS "DiagDate",
                                 rpb.document AS "Document",
                                 TO_CHAR(rpb.documentdate, 'YYYY.MM.DD') AS "DocumentDate",
                                 rpb.dop AS "Dop",
                                 rpb.mkb AS "Mkb",
                                 TO_CHAR(rpb.begindate, 'YYYY.MM.DD') AS "BeginDate",
                                 TO_CHAR(rpb.enddate, 'YYYY.MM.DD') AS "EndDate",
                                 b.benefitcode AS "BenefitCode",
                                 ebv.code AS "BenefitViews",
                                 rpb.lasteditlpu AS "Lpu",
                                 TO_CHAR(rpb.lasteditdate, 'YYYY.MM.DD') AS "LastEditDate",
                                 rpb.post AS "Post",
                                 rpb.divisionmanager AS "DivisionManager"
                             )
                         ) AS benefit_xml
                  FROM rds.t_rr_patient_benefit rpb
                  JOIN rds.t_rr_patient         rrp ON rrp.id = rpb.rrpatient
                  JOIN rds.t_benefits           b ON b.benefitsid = rpb.benefits
                  JOIN vclib.t_enums            ebv ON ebv.enumid = b.benefitviews
                 ) AS benefits;
            
            
            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 IF;

END;
$$
