CREATE OR REPLACE PROCEDURE public.sp_getsetnosologylist(
    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(p_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) := 'NOSOLOGY';
    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
        v_operationtype := c_ftload;
    ELSIF (p_isnew) THEN
        v_operationtype := c_insert;
    ELSIF (p_isnew = false AND EXISTS (SELECT 1 FROM rds.t_nosology_benefits n WHERE n.nosologybenefitsid = p_id)) THEN
        v_operationtype = c_update;
    ELSIF (p_isnew = false AND NOT EXISTS (SELECT 1 FROM rds.t_nosology_benefits n WHERE n.nosologybenefitsid = p_id)) THEN
        
        v_operationtype := c_delete;
        snils := CASE WHEN TRIM(REPLACE(REPLACE(p_snils, '0', ''), '-', '')) = '' THEN NULL ELSE p_snils END;
        
        SELECT XMLFOREST
               (
                   XMLELEMENT
                   (
                       NAME "header",
                       XMLFOREST
                       (
                           XMLELEMENT(NAME "TYPE", v_operationtype),
                           XMLELEMENT(NAME "ID", 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 "NOSOLOGY",
                   XMLFOREST
                   (
                       XMLELEMENT
                       (
                           NAME "header",
                           XMLELEMENT(NAME "TYPE", c_ftload)
                       ),
                       XMLELEMENT(
                           NAME "body",
                           XMLELEMENT
                           (
                               NAME "NOSOLOGY_ITEMS",
                               (SELECT XMLAGG(
                                           XMLELEMENT
                                           (
                                               NAME "NOSOLOGY_ITEM",
                                               XMLFOREST
                                               (
                                                   XMLELEMENT(NAME "ID", nos.nosologyid),
                                                   XMLELEMENT(NAME "ADRESS", nos.adress),
                                                   XMLELEMENT(NAME "CARD_STATUS", nb.cardstatus),
                                                   XMLELEMENT(NAME "CHANGE_DATE",
                                                              TO_CHAR(nos.dateofchange, 'YYYY.MM.DD')),
                                                   XMLELEMENT(NAME "DECLUSION_DATE",
                                                              TO_CHAR(nos.dateofdeclusion, 'YYYY.MM.DD')),
                                                   XMLELEMENT(NAME "INCLUSION_DATE",
                                                              TO_CHAR(nos.dateofinclusion, 'YYYY.MM.DD')),
                                                   XMLELEMENT(NAME "EXECUTIVE_AUTHORITY", nos.executiveauthority),
                                                   XMLELEMENT(NAME "HEAD", nb.head),
                                                   XMLELEMENT(NAME "OMS_NUMBER", nos.omsnumber),
                                                   XMLELEMENT(NAME "OMS_SERIAL", nos.omsserial),
                                                   XMLELEMENT(NAME "OPERATION_CODE", nos.operationcode),
                                                   XMLELEMENT(NAME "UNIQUE_NUMBER", nos.pac_id),
                                                   XMLELEMENT(NAME "MKB", nb.mkb),
                                                   XMLELEMENT(NAME "DIAGNOSIS", nb.diagnosis),
                                                   XMLELEMENT(NAME "DOC_SERIES", nos.docseries),
                                                   XMLELEMENT(NAME "DOC_NUMBER", nos.docnumber),
                                                   XMLELEMENT(NAME "BIRTHDAY", TO_CHAR(nos.birthday, 'YYYY.DD.MM')),
                                                   XMLELEMENT(NAME "FIRST_NAME", nos.firstname),
                                                   XMLELEMENT(NAME "LAST_NAME", nos.lastname),
                                                   XMLELEMENT(NAME "FATHER_NAME", nos.fathername),
                                                   XMLELEMENT(NAME "DOC_ISSUE_DATE",
                                                              TO_CHAR(nos.docissuedate, 'YYYY.DD.MM')),
                                                   XMLELEMENT(NAME "DOC_ISSUE_ORG", nos.docissueorg),
                                                   XMLELEMENT(NAME "DECLUSION_LPU", nos.declusionlpu),
                                                   XMLELEMENT(NAME "DOC_TYPE", nos.documenttype),
                                                   XMLELEMENT(NAME "EX178", nos.exemptionby178),
                                                   XMLELEMENT(NAME "EX890", nos.exemptionby890),
                                                   XMLELEMENT(NAME "INCLUSION_LPU", nos.inclusionlpu),
                                                   XMLELEMENT(NAME "REGION", reg.code),
                                                   XMLELEMENT(NAME "SEX", nos.sex),
                                                   XMLELEMENT(NAME "RF_ACCOUNT", nos.subjectofrfaccounted),
                                                   XMLELEMENT(NAME "BIRTHDAY_LAST_NAME", nos.birthdaylastname),
                                                   XMLELEMENT(NAME "DIASBILITY", nb.disability),
                                                   XMLELEMENT(NAME "THERAPY_RESISTANCE", nb.therapyresistance),
                                                   XMLELEMENT(NAME "DEATH_DATE", TO_CHAR(nos.deathdate, 'YYYY.DD.MM')),
                                                   XMLELEMENT(NAME "OKATO", nos.okato),
                                                   XMLELEMENT(NAME "SMO", nos.f002smo),
                                                   XMLELEMENT(NAME "SNILS",
                                                              CASE
                                                                  WHEN TRIM(REPLACE(REPLACE(nos.snils, '0', ''), '-', '')) = ''
                                                                      THEN NULL
                                                                  ELSE nos.snils
                                                              END),
                                                   XMLELEMENT(NAME "BENEFITS", b.benefitcode)
                                               )
                                           )
                                       )
                                FROM rds.t_nosology_benefits nb
                                JOIN rds.t_nosology nos ON nos.nosologyid = nb.nosology
                                LEFT JOIN rds.t_region reg ON reg.regionid = nos.region
                                JOIN rds.t_benefits b ON b.benefitsid = nb.benefits
                               )
                           )
                       )
                   ))::TEXT
        INTO v_result;
    
    ELSIF (v_operationtype <> c_delete) THEN
        
        SELECT XMLELEMENT
               (
                   NAME "NOSOLOGY",
                   XMLFOREST
                   (
                       XMLELEMENT
                       (
                           NAME "header",
                           XMLELEMENT(NAME "TYPE", c_ftload)
                       ),
                       XMLELEMENT(
                           NAME "body",
                           (SELECT XMLFOREST
                                   (
                                       XMLELEMENT(NAME "ID", nos.nosologyid),
                                       XMLELEMENT(NAME "ADRESS", nos.adress),
                                       XMLELEMENT(NAME "CARD_STATUS", nb.cardstatus),
                                       XMLELEMENT(NAME "CHANGE_DATE",
                                                  TO_CHAR(nos.dateofchange, 'YYYY.MM.DD')),
                                       XMLELEMENT(NAME "DECLUSION_DATE",
                                                  TO_CHAR(nos.dateofdeclusion, 'YYYY.MM.DD')),
                                       XMLELEMENT(NAME "INCLUSION_DATE",
                                                  TO_CHAR(nos.dateofinclusion, 'YYYY.MM.DD')),
                                       XMLELEMENT(NAME "EXECUTIVE_AUTHORITY", nos.executiveauthority),
                                       XMLELEMENT(NAME "HEAD", nb.head),
                                       XMLELEMENT(NAME "OMS_NUMBER", nos.omsnumber),
                                       XMLELEMENT(NAME "OMS_SERIAL", nos.omsserial),
                                       XMLELEMENT(NAME "OPERATION_CODE", nos.operationcode),
                                       XMLELEMENT(NAME "UNIQUE_NUMBER", nos.pac_id),
                                       XMLELEMENT(NAME "MKB", nb.mkb),
                                       XMLELEMENT(NAME "DIAGNOSIS", nb.diagnosis),
                                       XMLELEMENT(NAME "DOC_SERIES", nos.docseries),
                                       XMLELEMENT(NAME "DOC_NUMBER", nos.docnumber),
                                       XMLELEMENT(NAME "BIRTHDAY", TO_CHAR(nos.birthday, 'YYYY.DD.MM')),
                                       XMLELEMENT(NAME "FIRST_NAME", nos.firstname),
                                       XMLELEMENT(NAME "LAST_NAME", nos.lastname),
                                       XMLELEMENT(NAME "FATHER_NAME", nos.fathername),
                                       XMLELEMENT(NAME "DOC_ISSUE_DATE",
                                                  TO_CHAR(nos.docissuedate, 'YYYY.DD.MM')),
                                       XMLELEMENT(NAME "DOC_ISSUE_ORG", nos.docissueorg),
                                       XMLELEMENT(NAME "DECLUSION_LPU", nos.declusionlpu),
                                       XMLELEMENT(NAME "DOC_TYPE", nos.documenttype),
                                       XMLELEMENT(NAME "EX178", nos.exemptionby178),
                                       XMLELEMENT(NAME "EX890", nos.exemptionby890),
                                       XMLELEMENT(NAME "INCLUSION_LPU", nos.inclusionlpu),
                                       XMLELEMENT(NAME "REGION", reg.code),
                                       XMLELEMENT(NAME "SEX", nos.sex),
                                       XMLELEMENT(NAME "RF_ACCOUNT", nos.subjectofrfaccounted),
                                       XMLELEMENT(NAME "BIRTHDAY_LAST_NAME", nos.birthdaylastname),
                                       XMLELEMENT(NAME "DIASBILITY", nb.disability),
                                       XMLELEMENT(NAME "THERAPY_RESISTANCE", nb.therapyresistance),
                                       XMLELEMENT(NAME "DEATH_DATE", TO_CHAR(nos.deathdate, 'YYYY.DD.MM')),
                                       XMLELEMENT(NAME "OKATO", nos.okato),
                                       XMLELEMENT(NAME "SMO", nos.f002smo),
                                       XMLELEMENT(NAME "SNILS",
                                                  CASE
                                                      WHEN TRIM(REPLACE(REPLACE(nos.snils, '0', ''), '-', '')) = ''
                                                          THEN NULL
                                                      ELSE nos.snils
                                                  END),
                                       XMLELEMENT(NAME "BENEFITS", b.benefitcode)
                                   )
                            
                            FROM rds.t_nosology_benefits nb
                            JOIN rds.t_nosology nos ON nos.nosologyid = nb.nosology
                            LEFT JOIN rds.t_region reg ON reg.regionid = nos.region
                            JOIN rds.t_benefits b ON b.benefitsid = nb.benefits
                            WHERE nb.nosologybenefitsid = 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;
$$