CREATE OR REPLACE PROCEDURE public.sp_getbskregister(
    "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_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 '<BSK_REGISTER><header><TYPE>' || v_operationtype ||
                   '</TYPE></header><body><BSK_REGISTERS>' ||
                   COALESCE(STRING_AGG(
                                XMLSERIALIZE(CONTENT patient_xml AS TEXT),
                                ''
                            ), '') ||
                   '</BSK_REGISTERS></body></BSK_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, 'DD.MM.YYYY') 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, 'DD.MM.YYYY') AS "DateBegin",
                                 TO_CHAR(rp.dateend, 'DD.MM.YYYY') AS "DateEnd",
                                 TO_CHAR(rp.lasteditdate, 'DD.MM.YYYY') 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.bskregisterdataid AS "BenID",
                                                     TO_CHAR(rpb.diagdate, 'DD.MM.YYYY') AS "DiagDate",
                                                     rpb.document AS "Document",
                                                     TO_CHAR(rpb.documentdate, 'DD.MM.YYYY') AS
                                                     "DocumentDate",
                                                     rpb.mkb AS "Mkb",
                                                     b.benefitcode AS "BenefitCode",
                                                     e.code AS "BenefitViews",
                                                     TO_CHAR(rpb.begindate, 'DD.MM.YYYY') AS
                                                     "BeginDate",
                                                     TO_CHAR(rpb.enddate, 'DD.MM.YYYY') AS "EndDate",
                                                     rpb.servicecode AS "ServiceCode",
                                                     rpb.servicename AS "ServiceName",
                                                     rpb.exclusionreason AS "ExclusionReason",
                                                     TO_CHAR(rpb.estimatedddate, 'DD.MM.YYYY') AS
                                                     "EstimatedDDate",
                                                     TO_CHAR(rpb.dregistrationdate, 'DD.MM.YYYY') AS
                                                     "DRegistrationDate",
                                                     rpb.place AS "Place",
                                                     rpb.lpumainto AS "LpuTo",
                                                     rpb.lpumain AS "Lpu",
                                                     TO_CHAR(rpb.closedate, 'DD.MM.YYYY') AS "CloseDate",
                                                     (SELECT XMLAGG(
                                                                 XMLELEMENT(
                                                                     NAME "MEDICAMENTS",
                                                                     XMLFOREST(
                                                                         meds.bskregisterdata AS "BenID",
                                                                         mnn.otherid AS "Mnn",
                                                                         TO_CHAR(meds.datestart, 'DD.MM.YYYY') AS
                                                                         "DateStart",
                                                                         TO_CHAR(meds.enddate, 'DD.MM.YYYY') AS
                                                                         "EndDate",
                                                                         meds.medicationforms AS
                                                                         "MedicationForms",
                                                                         meds.regiment AS "Regiment",
                                                                         meds.dose AS "Dose",
                                                                         meds.prepid AS "Prepid",
                                                                         meds.daylicoeff AS "DayliCoeff",
                                                                         dv.name AS "MeasureUnit",
                                                                         dv.viewtype AS "MeasureUnitType",
                                                                         meds.singledose AS "SingleDose",
                                                                         meds.multiplicity AS
                                                                         "Multiplicity",
                                                                         dvdv.name AS "DoseViewUnit",
                                                                         dvdv.viewtype AS "DoseViewUnitType"
                                                                     )
                                                                 )
                                                             )
                                                      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
                                                     ) AS "MEDICAMENTS"
                                                 )
                                             )
                                         )
                                  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
                                 ) AS "BENEFITS"
                             )
                         ) AS patient_xml
                  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
                 ) AS "BSK_REGISTER";

            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;
$$;