DROP PROCEDURE IF EXISTS sp_getsetorphlist;

CREATE OR REPLACE PROCEDURE public.sp_getsetorphlist(
    "Xml" TEXT DEFAULT NULL,
    "IsNew" integer DEFAULT 1,
    "ID" uuid DEFAULT NULL,
    "QueueName" varchar(100) DEFAULT NULL,
    "dateTime" timestamp DEFAULT NULL,
    "User" uuid DEFAULT NULL,
    "RegisterId" int 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_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 XMLELEMENT(
                       NAME "ORPHAN_REGISTER",
                       XMLFOREST(
                           XMLELEMENT(
                               NAME "header",
                               XMLFOREST(
                                   v_operationtype AS "TYPE",
                                   p_id AS "ID"
                               )
                           ),
                           XMLELEMENT(
                               NAME "body",
                               XMLFOREST(
                                   p_registerid AS "REGISTER_ID"
                               )
                           )
                       )
                   )::TEXT
            INTO v_result;
        END IF;
        
        IF (v_operationtype = c_ftload) THEN
            SELECT '<ORPHAN_REGISTER><header><TYPE>' || v_operationtype || '</TYPE></header><body><ORPHAN_REGISTERS>' ||
                   COALESCE(STRING_AGG(
                                XMLSERIALIZE(CONTENT patient_xml AS TEXT),
                                ''
                            ), '') ||
                   '</ORPHAN_REGISTERS></body></ORPHAN_REGISTER>'
            INTO v_result
            FROM (SELECT XMLELEMENT(
                             NAME "ORPHAN_REGISTER_ITEM",
                             XMLFOREST(
                                 orph.orphanregisterid AS "ID",
                                 TO_CHAR(orph.datebegin, 'YYYY.MM.DD') AS "DATE_BEGIN",
                                 TO_CHAR(orph.dateend, 'YYYY.MM.DD') AS "DATE_END",
                                 orph.firstname AS "FIRST_NAME",
                                 orph.lastname AS "LAST_NAME",
                                 orph.birthlastname AS "BIRTH_LAST_NAME",
                                 orph.fathername AS "FATHER_NAME",
                                 TO_CHAR(orph.birthday, 'YYYY.MM.DD') AS "BIRTHDAY",
                                 orph.sex AS "SEX",
                                 orph.snils AS "SNILS",
                                 orph.disability AS "DIASBILITY",
                                 orph.adress AS "ADRESS",
                                 orph.kodadr AS "OKATO",
                                 orph.federal AS "FEDERAL",
                                 TO_CHAR(orph.docissuedate, 'YYYY.MM.DD') AS "DOC_ISSUE_DATE",
                                 orph.docissueorg AS "DOC_ISSUE_ORG",
                                 orph.doctype AS "DOC_TYPE",
                                 orph.docseries AS "DOC_SERIES",
                                 orph.docnumber AS "DOC_NUMBER",
                                 orph.f002smo AS "SMO",
                                 orph.omsserial AS "OMS_SERIAL",
                                 orph.omsnumber AS "OMS_NUMBER",
                                 orph.mkb AS "MKB",
                                 orph.diagnosis AS "DIAGNOSIS",
                                 orph.mkbdop AS "MBK_DOP",
                                 orph.diagnosisdop AS "DIAGNOSIS_DOP",
                                 orph.lpumainfirst AS "LPU_MAIN_FIRST",
                                 orph.lpumain AS "LPU_MAIN",
                                 orph.postfio AS "POST_FIO",
                                 orph.registerid AS "REGISTER_ID"
                             )
                         ) AS patient_xml
                  FROM rds.t_orphan_register orph
                 ) AS patients;
        
        ELSIF (v_operationtype <> c_delete) THEN
            SELECT XMLELEMENT(
                       NAME "ORPHAN_REGISTER",
                       XMLFOREST(
                           XMLELEMENT(
                               NAME "header",
                               XMLFOREST(v_operationtype AS "TYPE")
                           ),
                           (SELECT XMLELEMENT(
                                       NAME "body",
                                       XMLFOREST(
                                           orph.orphanregisterid AS "ID",
                                           TO_CHAR(orph.datebegin, 'YYYY.MM.DD') AS "DATE_BEGIN",
                                           TO_CHAR(orph.dateend, 'YYYY.MM.DD') AS "DATE_END",
                                           orph.firstname AS "FIRST_NAME",
                                           orph.lastname AS "LAST_NAME",
                                           orph.birthlastname AS "BIRTH_LAST_NAME",
                                           orph.fathername AS "FATHER_NAME",
                                           TO_CHAR(orph.birthday, 'YYYY.MM.DD') AS "BIRTHDAY",
                                           orph.sex AS "SEX",
                                           orph.snils AS "SNILS",
                                           orph.disability AS "DIASBILITY",
                                           orph.adress AS "ADRESS",
                                           orph.kodadr AS "OKATO",
                                           orph.federal AS "FEDERAL",
                                           TO_CHAR(orph.docissuedate, 'YYYY.MM.DD') AS "DOC_ISSUE_DATE",
                                           orph.docissueorg AS "DOC_ISSUE_ORG",
                                           orph.doctype AS "DOC_TYPE",
                                           orph.docseries AS "DOC_SERIES",
                                           orph.docnumber AS "DOC_NUMBER",
                                           orph.f002smo AS "SMO",
                                           orph.omsserial AS "OMS_SERIAL",
                                           orph.omsnumber AS "OMS_NUMBER",
                                           orph.mkb AS "MKB",
                                           orph.diagnosis AS "DIAGNOSIS",
                                           orph.mkbdop AS "MBK_DOP",
                                           orph.diagnosisdop AS "DIAGNOSIS_DOP",
                                           orph.lpumainfirst AS "LPU_MAIN_FIRST",
                                           orph.lpumain AS "LPU_MAIN",
                                           orph.postfio AS "POST_FIO",
                                           orph.registerid AS "REGISTER_ID"
                                       )
                                   )
                            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;
$$;