DROP PROCEDURE IF EXISTS sp_getmilkregister;

CREATE OR REPLACE PROCEDURE public.sp_getmilkregister(
    "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) := 'MILK_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 '<MILK_REGISTER><header><TYPE>' || v_operationtype ||
                   '</TYPE></header><body><MILK_REGISTERS>' ||
                   COALESCE(STRING_AGG(
                                XMLSERIALIZE(CONTENT patient_xml AS TEXT),
                                ''
                            ), '') ||
                   '</MILK_REGISTERS></body></MILK_REGISTER>'
            INTO v_result
            FROM (SELECT XMLELEMENT(
                             NAME "PATIENT",
                             XMLFOREST(
                                 rp.childcode AS "RegisterID",
                                 rp.lastname AS "LastName",
                                 rp.firstname AS "FirstName",
                                 rp.fathername AS "FatherName",
                                 TO_CHAR(rp.birthday, 'DD.MM.YYYY') AS "Birhday",
                                 rp.docseries AS "DocSeries",
                                 rp.docnumber AS "DocNumber",
                                 edoc.code AS "DocType",
                                 esex.code AS "Sex",
                                 rp.polis AS "Polis",
                                 rp.okato AS "OKATO",
                                 rp.city AS "City",
                                 rp.lhouse AS "LHouse",
                                 rp.block AS "Block",
                                 rp.flat AS "Flat",
                                 rp.lfat AS "LFat",
                                 rp.room AS "Room",
                                 rp.snils AS "Snils",
                                 rp.house AS "House",
                                 rp.street AS "Street",
                                 rp.excludereason AS "ExcludeReason",
                                 (SELECT XMLAGG(
                                             XMLELEMENT(
                                                 NAME "BENEFITS",
                                                 XMLFOREST(
                                                     rp.childcode AS "RegisterID",
                                                     TO_CHAR(rpb.diagdate, 'DD.MM.YYYY') AS
                                                     "DiagDate",
                                                     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"
                                                 )
                                             )
                                         )
                                  FROM rds.t_milk_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.milkregister = rp.milkregisterid
                                    AND rpb.benefits <> '188B840E-35CF-4A09-92CC-A21831646E88'
                                 ) AS "BENEFITS"
                             )
                         ) AS patient_xml
                  FROM rds.t_milk_register rp
                  JOIN vclib.t_enums       esex ON esex.enumid = rp.sex
                  JOIN vclib.t_enums       edoc ON edoc.enumid = rp.doctype
                 ) AS milk_patients;
        END IF;

        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 OR s.servertype IN (0, 1));


    END IF;

END;

$$