CREATE OR REPLACE PROCEDURE public.sp_getsetspecialbenefitslist(
    xml xml DEFAULT NULL,
    isnew boolean DEFAULT TRUE,
    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;
    p_isnew           boolean      := isnew;
    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(@SNILS, '0', ''), '-', '')) = '' THEN NULL ELSE p_snils END;
            
            SELECT XMLELEMENT
                   (
                       NAME "SPECIAL_BENEFITS_REGISTR",
                       XMLFOREST
                       (
                           XMLELEMENT
                           (
                               NAME "header",
                               XMLFOREST
                               (
                                   XMLELEMENT(NAME "TYPE", v_operationtype),
                                   XMLELEMENT(NAME "ID", p_id)
                               )
                           ),
                           XMLELEMENT
                           (
                               NAME "body",
                               XMLFOREST
                               (
                                   XMLELEMENT(NAME "SNILS", p_snils),
                                   XMLELEMENT(NAME "BIRTHDAY", TO_CHAR(p_birthday, 'YYYY.MM.DD')),
                                   XMLELEMENT(NAME "DOC_SERIES", p_docseries),
                                   XMLELEMENT(NAME "DOC_NUMBER", p_docnumber),
                                   XMLELEMENT(NAME "FIRST_NAME", p_firstname),
                                   XMLELEMENT(NAME "LAST_NAME", p_lastname),
                                   XMLELEMENT(NAME "FATHER_NAME", p_fathername),
                                   XMLELEMENT(NAME "BENEFITS", p_benefits)
                               )
                           )
                       )
                   )::text
            INTO v_result;
        
        
        END IF;
        
        IF (v_operationtype = c_ftload) THEN
            
            SELECT XMLELEMENT
                   (
                       NAME "SPECIAL_BENEFITS_REGISTR",
                       XMLFOREST
                       (
                           XMLFOREST
                           (
                               XMLELEMENT
                               (
                                   NAME "header",
                                   XMLELEMENT(NAME "TYPE", v_operationtype)
                               ),
                               XMLELEMENT(
                                   NAME "body",
                                   XMLELEMENT
                                   (
                                       NAME "SPECIAL_BENEFITS_REGISTR_ITEMS",
                                       (SELECT XMLAGG
                                               (
                                                   XMLELEMENT
                                                   (
                                                       NAME "SPECIAL_BENEFITS_REGISTR_ITEM",
                                                       XMLFOREST
                                                       (
                                                           XMLELEMENT(NAME "ID", sbr.specialbenefitsregistrid),
                                                           XMLELEMENT(NAME "ADRESS", sbr.adress),
                                                           XMLELEMENT(NAME "COMMENT", sbr.comment),
                                                           XMLELEMENT(NAME "CHANGE_DATE",
                                                                      TO_CHAR(sbr.dateofchange, 'YYYY.MM.DD')),
                                                           XMLELEMENT(NAME "DECLUSION_DATE",
                                                                      TO_CHAR(sbr.dateofdeclusion, 'YYYY.MM.DD')),
                                                           XMLELEMENT(NAME "INCLUSION_DATE",
                                                                      TO_CHAR(sbr.dateofinclusion, 'YYYY.MM.DD')),
                                                           XMLELEMENT(NAME "OMS_NUMBER", sbr.omsnumber),
                                                           XMLELEMENT(NAME "OMS_SERIAL", sbr.omsserial),
                                                           XMLELEMENT(NAME "UNIQUE_NUMBER", sbr.uniquenumber),
                                                           XMLELEMENT(NAME "MKB", sbr.mkb),
                                                           XMLELEMENT(NAME "DIAGNOSIS", sbr.diagnosis),
                                                           XMLELEMENT(NAME "DOC_SERIES", sbr.docseries),
                                                           XMLELEMENT(NAME "DOC_NUMBER", sbr.docnumber),
                                                           XMLELEMENT(NAME "BIRTHDAY", TO_CHAR(sbr.birthday, 'YYYY.MM.DD')),
                                                           XMLELEMENT(NAME "FIRST_NAME", sbr.firstname),
                                                           XMLELEMENT(NAME "LAST_NAME", sbr.lastname),
                                                           XMLELEMENT(NAME "FATHER_NAME", sbr.fathername),
                                                           XMLELEMENT(NAME "DOC_ISSUE_DATE",
                                                                      TO_CHAR(sbr.docissuedate, 'YYYY.MM.DD')),
                                                           XMLELEMENT(NAME "DOC_ISSUE_ORG", sbr.docissueorg),
                                                           XMLELEMENT(NAME "DOC_TYPE", edoc.code),
                                                           XMLELEMENT(NAME "INCLUSION_LPU", sbr.inclusionlpu),
                                                           XMLELEMENT(NAME "SEX", esex.code),
                                                           XMLELEMENT(NAME "BIRTHDAY_LAST_NAME", sbr.birthdaylastname),
                                                           XMLELEMENT(NAME "SMO", sbr.f002smo),
                                                           XMLELEMENT(NAME "SNILS", CASE
                                                                                        WHEN TRIM(REPLACE(REPLACE(sbr.snils, '0', ''), '-', '')) = ''
                                                                                            THEN NULL
                                                                                        ELSE sbr.snils
                                                                                    END),
                                                           XMLELEMENT(NAME "BENEFITS", b.benefitcode),
                                                           XMLELEMENT(NAME "BENEFITS_VIEWS", b.benefitviews)
                                                       )
                                                   )
                                               )
                                        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
                                       )
                                   )
                               )
                           )
                       )
                   )::text
            INTO v_result;
        
        ELSIF (v_operationtype <> c_delete) THEN
            
            SELECT XMLELEMENT
                   (
                       NAME "SPECIAL_BENEFITS_REGISTR",
                       XMLFOREST
                       (
                           XMLELEMENT
                           (
                               NAME "header",
                               XMLFOREST
                               (
                                   XMLELEMENT(NAME "TYPE", v_operationtype),
                                   XMLELEMENT(NAME "TYPE", v_operationtype),
                               )
                           ),
                           XMLELEMENT
                           (
                               NAME "body",
                               XMLFOREST
                               (
                                   XMLELEMENT(NAME "ID", sbr.specialbenefitsregistrid),
                                   XMLELEMENT(NAME "ADRESS", sbr.adress),
                                   XMLELEMENT(NAME "COMMENT", sbr.comment),
                                   XMLELEMENT(NAME "CHANGE_DATE",
                                              TO_CHAR(sbr.dateofchange, 'YYYY.MM.DD')),
                                   XMLELEMENT(NAME "DECLUSION_DATE",
                                              TO_CHAR(sbr.dateofdeclusion, 'YYYY.MM.DD')),
                                   XMLELEMENT(NAME "INCLUSION_DATE",
                                              TO_CHAR(sbr.dateofinclusion, 'YYYY.MM.DD')),
                                   XMLELEMENT(NAME "OMS_NUMBER", sbr.omsnumber),
                                   XMLELEMENT(NAME "OMS_SERIAL", sbr.omsserial),
                                   XMLELEMENT(NAME "UNIQUE_NUMBER", sbr.uniquenumber),
                                   XMLELEMENT(NAME "MKB", sbr.mkb),
                                   XMLELEMENT(NAME "DIAGNOSIS", sbr.diagnosis),
                                   XMLELEMENT(NAME "DOC_SERIES", sbr.docseries),
                                   XMLELEMENT(NAME "DOC_NUMBER", sbr.docnumber),
                                   XMLELEMENT(NAME "BIRTHDAY", TO_CHAR(sbr.birthday, 'YYYY.MM.DD')),
                                   XMLELEMENT(NAME "FIRST_NAME", sbr.firstname),
                                   XMLELEMENT(NAME "LAST_NAME", sbr.lastname),
                                   XMLELEMENT(NAME "FATHER_NAME", sbr.fathername),
                                   XMLELEMENT(NAME "DOC_ISSUE_DATE",
                                              TO_CHAR(sbr.docissuedate, 'YYYY.MM.DD')),
                                   XMLELEMENT(NAME "DOC_ISSUE_ORG", sbr.docissueorg),
                                   XMLELEMENT(NAME "DOC_TYPE", edoc.code),
                                   XMLELEMENT(NAME "INCLUSION_LPU", sbr.inclusionlpu),
                                   XMLELEMENT(NAME "SEX", esex.code),
                                   XMLELEMENT(NAME "BIRTHDAY_LAST_NAME", sbr.birthdaylastname),
                                   XMLELEMENT(NAME "SMO", sbr.f002smo),
                                   XMLELEMENT(NAME "SNILS", CASE
                                                                WHEN TRIM(REPLACE(REPLACE(sbr.snils, '0', ''), '-', '')) = ''
                                                                    THEN NULL
                                                                ELSE sbr.snils
                                                            END),
                                   XMLELEMENT(NAME "BENEFITS", b.benefitcode),
                                   XMLELEMENT(NAME "BENEFITS_VIEWS", b.benefitviews)
                               )
                           )
                       )
                   )::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;
$$
