CREATE OR REPLACE PROCEDURE public.sp_getsetorphlist(
    xml xml DEFAULT NULL,
    isnew boolean DEFAULT TRUE,
    id uuid DEFAULT NULL,
    queuename varchar(100) DEFAULT,
    datetime timestamp DEFAULT NULL,
    "User" uuid DEFAULT NULL,
    registerid int 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_registerid      int          := registerid;
    v_result          text;
    v_contentencoding varchar(255) := 'utf-8';
    v_type            varchar(255) := 'ORPHAN_REGISTER';
    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 = TRUE) THEN
            
            v_operationtype = c_insert;
        
        ELSIF (p_isnew = FALSE AND EXISTS (SELECT 1 FROM rds.t_orphan_register n WHERE n.orphanregisterid = p_id)) THEN
            
            v_operationtype = c_update;
        ELSIF (p_isnew = FALSE AND
               NOT EXISTS (SELECT 1 FROM rds.t_orphan_register n WHERE n.orphanregisterid = p_id)) THEN
            
            v_operationtype = c_delete;
            
            SELECT XMLFOREST
                   (
                       XMLELEMENT
                       (
                           NAME "header",
                           XMLFOREST
                           (
                               XMLELEMENT(NAME "TYPE", v_operationtype),
                               XMLELEMENT(NAME "ID", p_id)
                           )
                       ),
                       XMLELEMENT
                       (
                           NAME "body",
                           XMLELEMENT(NAME "REGISTER_ID", p_registerid)
                       )
                   )::TEXT
            INTO v_result;
        END IF;
        
        IF (v_operationtype = c_ftload) THEN
            
            SELECT XMLELEMENT
                   (
                       NAME "ORPHAN_REGISTER",
                       XMLFOREST
                       (
                           XMLELEMENT
                           (
                               NAME "header",
                               XMLELEMENT(NAME "TYPE", v_operationtype)
                           ),
                           XMLELEMENT
                           (
                               NAME "body",
                               XMLELEMENT
                               (
                                   NAME "ORPHAN_REGISTERS",
                                   (SELECT XMLAGG
                                           (
                                               XMLELEMENT
                                               (
                                                   NAME "ORPHAN_REGISTER_ITEM",
                                                   XMLFOREST
                                                   (
                                                       XMLELEMENT(NAME "ID", orph.orphanregisterid),
                                                       XMLELEMENT(NAME "DATE_BEGIN", TO_CHAR(orph.datebegin, 'YYYY.MM.DD')),
                                                       XMLELEMENT(NAME "DATE_END", TO_CHAR(orph.dateend, 'YYYY.MM.DD')),
                                                       XMLELEMENT(NAME "FIRST_NAME", orph.firstname),
                                                       XMLELEMENT(NAME "LAST_NAME", orph.lastname),
                                                       XMLELEMENT(NAME "BIRTH_LAST_NAME", orph.birthlastname),
                                                       XMLELEMENT(NAME "FATHER_NAME", orph.fathername),
                                                       XMLELEMENT(NAME "BIRTHDAY", TO_CHAR(orph.birthday, 'YYYY.MM.DD')),
                                                       XMLELEMENT(NAME "SEX", orph.sex),
                                                       XMLELEMENT(NAME "SNILS", orph.snils),
                                                       XMLELEMENT(NAME "DIASBILITY", orph.disability),
                                                       XMLELEMENT(NAME "ADRESS", orph.adress),
                                                       XMLELEMENT(NAME "OKATO", orph.kodadr),
                                                       XMLELEMENT(NAME "FEDERAL", orph.federal),
                                                       XMLELEMENT(NAME "DOC_ISSUE_DATE",
                                                                  TO_CHAR(orph.docissuedate, 'YYYY.MM.DD')),
                                                       XMLELEMENT(NAME "DOC_ISSUE_ORG", orph.docissueorg),
                                                       XMLELEMENT(NAME "DOC_TYPE", orph.doctype),
                                                       XMLELEMENT(NAME "DOC_SERIES", orph.docseries),
                                                       XMLELEMENT(NAME "DOC_NUMBER", orph.docnumber),
                                                       XMLELEMENT(NAME "SMO", orph.f002smo),
                                                       XMLELEMENT(NAME "OMS_SERIAL", orph.omsserial),
                                                       XMLELEMENT(NAME "OMS_NUMBER", orph.omsnumber),
                                                       XMLELEMENT(NAME "MKB", orph.mkb),
                                                       XMLELEMENT(NAME "DIAGNOSIS", orph.diagnosis),
                                                       XMLELEMENT(NAME "MBK_DOP", orph.mkbdop),
                                                       XMLELEMENT(NAME "DIAGNOSIS_DOP", orph.diagnosisdop),
                                                       XMLELEMENT(NAME "LPU_MAIN_FIRST", orph.lpumainfirst),
                                                       XMLELEMENT(NAME "LPU_MAIN", orph.lpumain),
                                                       XMLELEMENT(NAME "POST_FIO", orph.postfio),
                                                       XMLELEMENT(NAME "REGISTER_ID", orph.registerid)
                                                   )
                                               )
                                           )
                                    FROM rds.t_orphan_register orph
                                   )
                               )
                           )
                       )
                   )::text
            INTO v_result;
        
        ELSIF (v_operationtype <> c_delete) THEN
            
            SELECT XMLELEMENT
                   (
                       NAME "ORPHAN_REGISTER",
                       XMLFOREST
                       (
                           XMLELEMENT
                           (
                               NAME "header",
                               XMLELEMENT(NAME "TYPE", v_operationtype)
                           ),
                           (SELECT XMLELEMENT
                                   (
                                       NAME "body",
                                       XMLFOREST
                                       (
                                           XMLELEMENT(NAME "ID", orph.orphanregisterid),
                                           XMLELEMENT(NAME "DATE_BEGIN", TO_CHAR(orph.datebegin, 'YYYY.MM.DD')),
                                           XMLELEMENT(NAME "DATE_END", TO_CHAR(orph.dateend, 'YYYY.MM.DD')),
                                           XMLELEMENT(NAME "FIRST_NAME", orph.firstname),
                                           XMLELEMENT(NAME "LAST_NAME", orph.lastname),
                                           XMLELEMENT(NAME "BIRTH_LAST_NAME", orph.birthlastname),
                                           XMLELEMENT(NAME "FATHER_NAME", orph.fathername),
                                           XMLELEMENT(NAME "BIRTHDAY", TO_CHAR(orph.birthday, 'YYYY.MM.DD')),
                                           XMLELEMENT(NAME "SEX", orph.sex),
                                           XMLELEMENT(NAME "SNILS", orph.snils),
                                           XMLELEMENT(NAME "DIASBILITY", orph.disability),
                                           XMLELEMENT(NAME "ADRESS", orph.adress),
                                           XMLELEMENT(NAME "OKATO", orph.kodadr),
                                           XMLELEMENT(NAME "FEDERAL", orph.federal),
                                           XMLELEMENT(NAME "DOC_ISSUE_DATE",
                                                      TO_CHAR(orph.docissuedate, 'YYYY.MM.DD')),
                                           XMLELEMENT(NAME "DOC_ISSUE_ORG", orph.docissueorg),
                                           XMLELEMENT(NAME "DOC_TYPE", orph.doctype),
                                           XMLELEMENT(NAME "DOC_SERIES", orph.docseries),
                                           XMLELEMENT(NAME "DOC_NUMBER", orph.docnumber),
                                           XMLELEMENT(NAME "SMO", orph.f002smo),
                                           XMLELEMENT(NAME "OMS_SERIAL", orph.omsserial),
                                           XMLELEMENT(NAME "OMS_NUMBER", orph.omsnumber),
                                           XMLELEMENT(NAME "MKB", orph.mkb),
                                           XMLELEMENT(NAME "DIAGNOSIS", orph.diagnosis),
                                           XMLELEMENT(NAME "MBK_DOP", orph.mkbdop),
                                           XMLELEMENT(NAME "DIAGNOSIS_DOP", orph.diagnosisdop),
                                           XMLELEMENT(NAME "LPU_MAIN_FIRST", orph.lpumainfirst),
                                           XMLELEMENT(NAME "LPU_MAIN", orph.lpumain),
                                           XMLELEMENT(NAME "POST_FIO", orph.postfio),
                                           XMLELEMENT(NAME "REGISTER_ID", orph.registerid)
                                       )
                                   )
                            FROM rds.t_orphan_register orph
                            WHERE orph.orphanregisterid = p_id
                           )
                       )
                   )::text
            INTO v_result;
        
        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;
$$
