CREATE OR REPLACE PROCEDURE public.sp_getbskregister(
    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;
    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) := 'BSK_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 (p_xml IS NULL) THEN
        
        IF (p_id IS NULL) THEN
            v_operationtype := c_ftload;
        END IF;
        
        IF (v_operationtype = c_ftload) THEN
            
            SELECT XMLELEMENT
                   (
                       NAME "BSK_REGISTER",
                       XMLFOREST
                       (
                           XMLELEMENT
                           (
                               NAME "header",
                               XMLELEMENT(NAME "TYPE", c_ftload)
                           ),
                           XMLELEMENT(
                               NAME "body",
                               (SELECT XMLAGG
                                       (
                                           XMLELEMENT(
                                               NAME "PATIENT",
                                               XMLFOREST
                                               (
                                                   XMLELEMENT(NAME "RegisterID", rp.pac_id),
                                                   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 "Kod", rp.kod),
                                                   XMLELEMENT(NAME "DocSeries", rp.docseries),
                                                   XMLELEMENT(NAME "DocNumber", rp.docnumber),
                                                   XMLELEMENT(NAME "Sex", esex.code),
                                                   XMLELEMENT(NAME "Snils", rp.snils),
                                                   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 "Lpu", rp.lpumain),
                                                   XMLELEMENT(NAME "ExcludeReason", rp.excludereason),
                                                   XMLELEMENT(NAME "DocType", edoc.code),
                                                   (SELECT XMLAGG
                                                           (
                                                               XMLELEMENT
                                                               (
                                                                   NAME "BENEFITS",
                                                                   XMLFOREST
                                                                   (
                                                                       XMLELEMENT(NAME "RegisterID", rp.pac_id),
                                                                       XMLELEMENT(NAME "BenID", rpb.bskregisterdataid),
                                                                       XMLELEMENT(NAME "DiagDate", TO_CHAR(rpb.diagdate, 'YYYY.MM.DD')),
                                                                       XMLELEMENT(NAME "Document", rpb.document),
                                                                       XMLELEMENT(NAME "DocumentDate",
                                                                                  TO_CHAR(rpb.documentdate, 'YYYY.MM.DD')),
                                                                       XMLELEMENT(NAME "Mkb", rpb.mkb),
                                                                       XMLELEMENT(NAME "BenefitCode", b.benefitcode),
                                                                       XMLELEMENT(NAME "BenefitViews", e.code),
                                                                       XMLELEMENT(NAME "BeginDate", TO_CHAR(rpb.begindate, 'YYYY.MM.DD')),
                                                                       XMLELEMENT(NAME "EndDate", TO_CHAR(rpb.enddate, 'YYYY.MM.DD')),
                                                                       XMLELEMENT(NAME "ServiceCode", rpb.servicecode),
                                                                       XMLELEMENT(NAME "ServiceName", rpb.servicename),
                                                                       XMLELEMENT(NAME "ExclusionReason", rpb.exclusionreason),
                                                                       XMLELEMENT(NAME "EstimatedDDate",
                                                                                  TO_CHAR(rpb.estimatedddate, 'YYYY.MM.DD')),
                                                                       XMLELEMENT(NAME "DRegistrationDate",
                                                                                  TO_CHAR(rpb.dregistrationdate, 'YYYY.MM.DD')),
                                                                       XMLELEMENT(NAME "Place", rpb.place),
                                                                       XMLELEMENT(NAME "LpuTo", rpb.lpumainto),
                                                                       XMLELEMENT(NAME "Lpu", rpb.lpumain),
                                                                       XMLELEMENT(NAME "CloseDate", TO_CHAR(rpb.closedate, 'YYYY.MM.DD')),
                                                                       (SELECT XMLAGG(
                                                                                   XMLELEMENT(
                                                                                       NAME "MEDICAMENTS",
                                                                                       XMLFOREST
                                                                                       (
                                                                                           XMLELEMENT(NAME "BenID", meds.bskregisterdata),
                                                                                           XMLELEMENT(NAME "Mnn", mnn.otherid),
                                                                                           XMLELEMENT(NAME "DateStart", TO_CHAR(meds.datestart, 'YYYY.MM.DD')),
                                                                                           XMLELEMENT(NAME "EndDat e", TO_CHAR(meds.enddate, 'YYYY.MM.DD')),
                                                                                           XMLELEMENT(NAME "MedicationForms", meds.medicationforms),
                                                                                           XMLELEMENT(NAME "Regiment", meds.regiment),
                                                                                           XMLELEMENT(NAME "Dose", meds.dose),
                                                                                           XMLELEMENT(NAME "Prepid", meds.prepid),
                                                                                           XMLELEMENT(NAME "DayliCoeff", meds.daylicoeff),
                                                                                           XMLELEMENT(NAME "MeasureUnit", dv.name),
                                                                                           XMLELEMENT(NAME "MeasureUnitType", dv.viewtype),
                                                                                           XMLELEMENT(NAME "SingleDose", meds.singledose),
                                                                                           XMLELEMENT(NAME "Multiplicity", meds.multiplicity),
                                                                                           XMLELEMENT(NAME "DoseViewUnit", dvdv.name),
                                                                                           XMLELEMENT(NAME "DoseViewUnitType", dvdv.viewtype)
                                                                                       )
                                                                                   )
                                                                               )
                                                                        FROM rds.t_bsk_register_medicaments meds
                                                                        JOIN pharm.t_c_mnn mnn ON mnn.cmnnid = meds.cmnn
                                                                        LEFT JOIN pharm.t_dose_view dv ON dv.code = meds.measureunit
                                                                        LEFT JOIN pharm.t_dose_view dvdv ON dvdv.code = meds.doseview
                                                                        WHERE meds.bskregisterdata = rpb.bskregisterdataid
                                                                       )
                                                                   )
                                                               )
                                                           )
                                                    FROM rds.t_bsk_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.bskregister = rp.bskregisterid
                                                   )
                                               )
                                           )
                                       )
                                FROM rds.t_bsk_register rp
                                JOIN vclib.t_enums esex ON esex.enumid = rp.sex
                                LEFT JOIN vclib.t_enums edoc ON edoc.enumid = rp.doctype
                               )
                           )
                       )
                   )::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 (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;
$$