CREATE OR REPLACE PROCEDURE public.sp_getsetspecialbenefitslist(
    "Xml" text DEFAULT NULL,
    "IsNew" int DEFAULT 1,
    "ID" uuid DEFAULT NULL,
    "QueueName" varchar(100) DEFAULT 'RDSSync',
    "datetime" timestamp DEFAULT NULL,
    "User" uuid DEFAULT NULL,
    "SNILS" varchar(14) DEFAULT NULL,
    "Birthday" timestamp DEFAULT NULL,
    "DocSeries" varchar(30) DEFAULT NULL,
    "DocNumber" varchar(30) DEFAULT NULL,
    "FirstName" varchar(60) DEFAULT NULL,
    "LastName" varchar(60) DEFAULT NULL,
    "FatherName" varchar(60) DEFAULT NULL,
    "Benefits" text DEFAULT NULL
)
    LANGUAGE plpgsql
AS
$$
DECLARE
    p_xml             xml          := "Xml"::xml;
    p_isnew           boolean      := "IsNew"::boolean;
    p_id              uuid         := "ID";
    p_queuename       varchar(100) := "QueueName";
    p_datetime        timestamp    := COALESCE("datetime", CURRENT_TIMESTAMP);
    p_user            uuid         := "User";
    p_snils           varchar(14)  := "SNILS";
    p_birthday        timestamp    := "Birthday";
    p_docseries       varchar(30)  := "DocSeries";
    p_docnumber       varchar(30)  := "DocNumber";
    p_firstname       varchar(60)  := "FirstName";
    p_lastname        varchar(60)  := "LastName";
    p_fathername      varchar(60)  := "FatherName";
    p_benefits        text         := "Benefits";
    v_result          text;
    v_contentencoding varchar(255) := 'utf-8';
    v_type            varchar(255) := 'SPECIAL_BENEFITS_REGISTR';
    v_operationtype   varchar(255);
    v_ownerid         uuid         := p_id;
    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;
        ELSIF (p_isnew = 1) THEN
            v_operationtype = c_insert;
        ELSIF (p_isnew = 0 AND
               EXISTS (SELECT 1 FROM rds.t_special_benefits_registr n WHERE n.specialbenefitsregistrid = p_id)) THEN
            v_operationtype := c_update;
        ELSIF (p_isnew = 0 AND
               EXISTS (SELECT 1 FROM rds.t_special_benefits_registr n WHERE n.specialbenefitsregistrid = p_id)) THEN
            v_operationtype := c_delete;
            p_snils := CASE WHEN TRIM(REPLACE(REPLACE(p_snils, '0', ''), '-', '')) = '' THEN NULL ELSE p_snils END;

            SELECT XMLELEMENT(
                       NAME "SPECIAL_BENEFITS_REGISTR",
                       XMLFOREST(
                           XMLELEMENT(
                               NAME "header",
                               XMLFOREST(
                                   v_operationtype AS "TYPE",
                                   p_id AS "ID"
                               )
                           ),
                           XMLELEMENT(
                               NAME "body",
                               XMLFOREST(
                                   p_snils AS "SNILS",
                                   TO_CHAR(p_birthday, 'DD.MM.YYYY') AS "BIRTHDAY",
                                   p_docseries AS "DOC_SERIES",
                                   p_docnumber AS "DOC_NUMBER",
                                   p_firstname AS "FIRST_NAME",
                                   p_lastname AS "LAST_NAME",
                                   p_fathername AS "FATHER_NAME",
                                   p_benefits AS "BENEFITS"
                               )
                           )
                       )
                   )::text
            INTO v_result;
        END IF;

        IF (v_operationtype = c_ftload) THEN
        SELECT '<SPECIAL_BENEFITS_REGISTR><header><TYPE>' || v_operationtype ||
                   '</TYPE></header><body><SPECIAL_BENEFITS_REGISTR_ITEMS>' ||
                   COALESCE(STRING_AGG(
                                XMLSERIALIZE(CONTENT patient_xml AS TEXT),
                                ''
                            ), '') ||
                   '</SPECIAL_BENEFITS_REGISTR_ITEMS></body></SPECIAL_BENEFITS_REGISTR>'
            INTO v_result
            FROM (SELECT XMLELEMENT(
                             NAME "RR_PATIENT",
                             XMLFOREST(
                                 sbr.specialbenefitsregistrid AS "ID",
                                 sbr.adress AS "ADRESS",
                                 sbr.comment AS "COMMENT",
                                 TO_CHAR(sbr.dateofchange, 'DD.MM.YYYY') AS "CHANGE_DATE",
                                 TO_CHAR(sbr.dateofdeclusion, 'DD.MM.YYYY') AS "DECLUSION_DATE",
                                 TO_CHAR(sbr.dateofinclusion, 'DD.MM.YYYY') AS "INCLUSION_DATE",
                                 sbr.omsnumber AS "OMS_NUMBER",
                                 sbr.omsserial AS "OMS_SERIAL",
                                 sbr.uniquenumber AS "UNIQUE_NUMBER",
                                 sbr.mkb AS "MKB",
                                 sbr.diagnosis AS "DIAGNOSIS",
                                 sbr.docseries AS "DOC_SERIES",
                                 sbr.docnumber AS "DOC_NUMBER",
                                 TO_CHAR(sbr.birthday, 'DD.MM.YYYY') AS "BIRTHDAY",
                                 sbr.firstname AS "FIRST_NAME",
                                 sbr.lastname AS "LAST_NAME",
                                 sbr.fathername AS "FATHER_NAME",
                                 TO_CHAR(sbr.docissuedate, 'DD.MM.YYYY') AS "DOC_ISSUE_DATE",
                                 sbr.docissueorg AS "DOC_ISSUE_ORG",
                                 edoc.code AS "DOC_TYPE",
                                 sbr.inclusionlpu AS "INCLUSION_LPU",
                                 esex.code AS "SEX",
                                 sbr.birthdaylastname AS "BIRTHDAY_LAST_NAME",
                                 sbr.f002smo AS "SMO",
                                 CASE
                                     WHEN TRIM(REPLACE(REPLACE(sbr.snils, '0', ''), '-', '')) = ''
                                         THEN NULL
                                     ELSE sbr.snils
                                 END AS "SNILS",
                                 b.benefitcode AS "BENEFITS",
                                 b.benefitviews AS "BENEFITS_VIEWS"
                             )
                         ) AS patient_xml
                  FROM rds.t_special_benefits_registr sbr
                  JOIN rds.t_benefits                 b ON b.benefitsid = sbr.benefits
                  JOIN vclib.t_enums                  esex ON esex.enumid = sbr.sex
                  JOIN vclib.t_enums                  edoc ON edoc.enumid = sbr.documenttype
                 ) AS patients;

        ELSIF (v_operationtype <> c_delete) THEN
            SELECT XMLELEMENT(
                       NAME "SPECIAL_BENEFITS_REGISTR",
                       XMLFOREST(
                           XMLELEMENT(
                               NAME "header",
                               XMLFOREST(
                                   v_operationtype AS "TYPE"
                               )
                           ),
                           XMLELEMENT(
                               NAME "body",
                               XMLFOREST(
                                   sbr.specialbenefitsregistrid AS "ID",
                                   sbr.adress AS "ADRESS",
                                   sbr.comment AS "COMMENT",
                                   TO_CHAR(sbr.dateofchange, 'DD.MM.YYYY') AS "CHANGE_DATE",
                                   TO_CHAR(sbr.dateofdeclusion, 'DD.MM.YYYY') AS "DECLUSION_DATE",
                                   TO_CHAR(sbr.dateofinclusion, 'DD.MM.YYYY') AS "INCLUSION_DATE",
                                   sbr.omsnumber AS "OMS_NUMBER",
                                   sbr.omsserial AS "OMS_SERIAL",
                                   sbr.uniquenumber AS "UNIQUE_NUMBER",
                                   sbr.mkb AS "MKB",
                                   sbr.diagnosis AS "DIAGNOSIS",
                                   sbr.docseries AS "DOC_SERIES",
                                   sbr.docnumber AS "DOC_NUMBER",
                                   TO_CHAR(sbr.birthday, 'DD.MM.YYYY') AS "BIRTHDAY",
                                   sbr.firstname AS "FIRST_NAME",
                                   sbr.lastname AS "LAST_NAME",
                                   sbr.fathername AS "FATHER_NAME",
                                   TO_CHAR(sbr.docissuedate, 'DD.MM.YYYY') AS "DOC_ISSUE_DATE",
                                   sbr.docissueorg AS "DOC_ISSUE_ORG",
                                   edoc.code AS "DOC_TYPE",
                                   sbr.inclusionlpu AS "INCLUSION_LPU",
                                   esex.code AS "SEX",
                                   sbr.birthdaylastname AS "BIRTHDAY_LAST_NAME",
                                   sbr.f002smo AS "SMO",
                                   CASE
                                       WHEN TRIM(REPLACE(REPLACE(sbr.snils, '0', ''), '-', '')) = ''
                                           THEN NULL
                                       ELSE sbr.snils
                                   END AS "SNILS",
                                   b.benefitcode AS "BENEFITS",
                                   b.benefitviews AS "BENEFITS_VIEWS"
                               )
                           )
                       )
                   )::TEXT
            INTO v_result
            FROM rds.t_special_benefits_registr sbr
            JOIN rds.t_benefits b ON b.benefitsid = sbr.benefits
            JOIN vclib.t_enums esex ON esex.enumid = sbr.sex
            JOIN vclib.t_enums edoc ON edoc.enumid = sbr.documenttype
            WHERE sbr.specialbenefitsregistrid = p_id;
        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 AND s.servertype IN (0, 1));
    END IF;
END;
$$