DROP PROCEDURE IF EXISTS public.sp_getsetnosologylist;

CREATE OR REPLACE PROCEDURE public.sp_getsetnosologylist(
    "Xml" xml DEFAULT NULL,
    "IsNew" integer = 1,
    "ID" uuid DEFAULT NULL,
    "QueueName" varchar(100) DEFAULT NULL,
    "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,
    "PacId" int DEFAULT NULL
)
    LANGUAGE plpgsql
AS
$$
DECLARE
    p_xml             xml          := "Xml";
    p_isnew           boolean      := "IsNew"::boolean;
    p_id              uuid         := "ID";
    p_queuename       varchar(100) := "QueueName";
    p_datetime        timestamp    := "dateTime";
    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_pacid           int          := "PacId";
    v_result          text;
    v_operationtype   varchar(255);
    v_ownerid         uuid         := "ID";
    c_insert          varchar(6)   := 'Insert';
    c_update          varchar(6)   := 'Update';
    c_delete          varchar(6)   := 'Delete';
    c_ftload          varchar(6)   := 'FTLoad';
    v_contentencoding varchar(8)   := 'utf-8';
    v_type            varchar(8)   := 'NOSOLOGY';
BEGIN
    
    IF (p_xml IS NULL) THEN
        
        IF (p_id IS NULL) THEN
            
            v_operationtype := c_ftload;
        
        ELSE
            
            IF (p_isnew = TRUE) THEN
                v_operationtype := c_insert;
            ELSE
                
                IF (p_isnew = FALSE AND EXISTS (SELECT 1 FROM rds.t_nosology n WHERE n.nosologyid = 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 "NOSOLOGY",
                               XMLELEMENT
                               (
                                   NAME "header",
                                   XMLELEMENT(NAME "TYPE", v_operationtype),
                                   XMLELEMENT(NAME "ID", p_id)
                               ),
                               XMLELEMENT
                               (
                                   NAME "body",
                                   XMLELEMENT(NAME "SNILS", p_snils),
                                   XMLELEMENT(NAME "BIRTHDAY", TO_CHAR(p_birthday, 'DD.MM.YYYY')),
                                   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 "PAC_ID", p_pacid)
                               )
                           )
                    INTO v_result;
                
                END IF;
            
            
            END IF;
        
        END IF;
        
        IF (v_operationtype = c_ftload) THEN
            
            SELECT XMLELEMENT
                   (
                       NAME "NOSOLOGY",
                       XMLELEMENT
                       (
                           NAME "head",
                           XMLELEMENT(NAME "TYPE", v_operationtype)
                       ),
                       XMLELEMENT
                       (
                           NAME "body",
                           XMLELEMENT
                           (
                               NAME "NOSOLOGYS",
                               (SELECT XMLELEMENT
                                       (
                                           NAME "NOSOLOGY",
                                           XMLFOREST
                                           (
                                               nos.nosologyid AS "ID",
                                               nos.adress AS "ADRESS",
                                               TO_CHAR(nos.dateofchange, 'DD.MM.YYYY') AS "CHANGE_DATE",
                                               nos.pac_id AS "PAC_ID",
                                               nos.firstname AS "FIRST_NAME",
                                               nos.lastname AS "LAST_NAME",
                                               nos.fathername AS "FATHER_NAME",
                                               TO_CHAR(nos.birthday, 'DD.MM.YYYY') AS "BIRTHDAY",
                                               nos.birthdaylastname AS "BIRTHDAY_LAST_NAME",
                                               esex.code AS "SEX",
                                               CASE
                                                   WHEN TRIM(REPLACE(REPLACE(nos.snils, '0', ''), '-', '')) = ''
                                                       THEN NULL
                                                   ELSE nos.snils
                                               END AS "SNILS",
                                               edoc.code AS "DOC_TYPE",
                                               nos.docseries AS "DOC_SERIES",
                                               nos.docnumber AS "DOC_NUMBER",
                                               TO_CHAR(nos.docissuedate, 'DD.MM.YYYY') AS "DOC_ISSUE_DATE",
                                               nos.docissueorg AS "DOC_ISSUE_ORG",
                                               nos.omsserial AS "OMS_SERIAL",
                                               nos.omsnumber AS "OMS_NUMBER",
                                               nos.f002smo AS "SMO",
                                               nos.okato AS "OKATO",
                                               TO_CHAR(nos.dateofinclusion, 'DD.MM.YYYY') AS "INCLUSION_DATE",
                                               nos.inclusionlpu AS "INCLUSION_LPU",
                                               nos.executiveauthority AS "EXECUTIVE_AUTHORITY",
                                               TO_CHAR(nos.dateofdeclusion, 'DD.MM.YYYY') AS "DECLUSION_DATE",
                                               nos.declusionlpu AS "DECLUSION_LPU",
                                               nos.exemptionby178 AS "EX178",
                                               nos.exemptionby890 AS "EX890",
                                               nos.subjectofrfaccounted AS "RF_ACCOUNT",
                                               TO_CHAR(nos.deathdate, 'DD.MM.YYYY'),
                                               (SELECT XMLAGG
                                                       (
                                                           XMLELEMENT
                                                           (
                                                               NAME "NOSOLOGY_BENEFIT",
                                                               XMLFOREST
                                                               (
                                                                   nb.uniquenumber AS "UNIQUE_NUMBER",
                                                                   nos.nosologyid AS "NOSOLOGYID",
                                                                   nb.mkb AS "MKB",
                                                                   nb.cardstatus AS "CARD_STATUS",
                                                                   nb.head AS "HEAD",
                                                                   nb.diagnosis AS "DIAGNOSIS",
                                                                   nb.disability AS "DIASBILITY",
                                                                   nb.therapyresistance AS "THERAPY_RESISTANCE",
                                                                   b.benefitcode AS "BENEFIT_CODE",
                                                                   TO_CHAR(nb.datebegin, 'DD.MM.YYYY'),
                                                                   TO_CHAR(nb.enddate, 'DD.MM.YYYY')
                                                               )
                                                           )
                                                       )
                                                FROM rds.t_nosology_benefits nb
                                                JOIN rds.t_benefits b ON b.benefitsid = nb.benefits
                                                WHERE nb.nosology = nos.nosologyid
                                               ) AS "NOSOLOGY_BENEFITS"
                                           )
                                       )
                                FROM rds.t_nosology nos
                                LEFT JOIN rds.t_region reg ON reg.regionid = nos.region
                                LEFT JOIN vclib.t_enums esex ON esex.enumtypeid = 103 AND esex.enumid = nos.sex
                                LEFT JOIN vclib.t_enums edoc ON edoc.enumtypeid = 105 AND edoc.enumid = nos.documenttype
                               )
                           )
                       )
                   )
            INTO v_result;
        
        ELSIF (v_operationtype <> c_delete) THEN
            
            SELECT XMLELEMENT
                   (
                       NAME "NOSOLOGY",
                       XMLELEMENT
                       (
                           NAME "header",
                           XMLELEMENT(NAME "TYPE", v_operationtype),
                           XMLELEMENT(NAME "ID", p_id)
                       ),
                       (SELECT XMLELEMENT
                               (
                                   NAME "body",
                                   XMLFOREST
                                   (
                                       nos.nosologyid AS "ID",
                                       nos.adress AS "ADRESS",
                                       TO_CHAR(nos.dateofchange, 'DD.MM.YYYY') AS "CHANGE_DATE",
                                       nos.pac_id AS "PAC_ID",
                                       nos.firstname AS "FIRST_NAME",
                                       nos.lastname AS "LAST_NAME",
                                       nos.fathername AS "FATHER_NAME",
                                       TO_CHAR(nos.birthday, 'DD.MM.YYYY') AS "BIRTHDAY",
                                       nos.birthdaylastname AS "BIRTHDAY_LAST_NAME",
                                       esex.code AS "SEX",
                                       CASE
                                           WHEN TRIM(REPLACE(REPLACE(nos.snils, '0', ''), '-', '')) = ''
                                               THEN NULL
                                           ELSE nos.snils
                                       END AS "SNILS",
                                       edoc.code AS "DOC_TYPE",
                                       nos.docseries AS "DOC_SERIES",
                                       nos.docnumber AS "DOC_NUMBER",
                                       TO_CHAR(nos.docissuedate, 'DD.MM.YYYY') AS "DOC_ISSUE_DATE",
                                       nos.docissueorg AS "DOC_ISSUE_ORG",
                                       nos.omsserial AS "OMS_SERIAL",
                                       nos.omsnumber AS "OMS_NUMBER",
                                       nos.f002smo AS "SMO",
                                       nos.okato AS "OKATO",
                                       TO_CHAR(nos.dateofinclusion, 'DD.MM.YYYY') AS "INCLUSION_DATE",
                                       nos.inclusionlpu AS "INCLUSION_LPU",
                                       nos.executiveauthority AS "EXECUTIVE_AUTHORITY",
                                       TO_CHAR(nos.dateofdeclusion, 'DD.MM.YYYY') AS "DECLUSION_DATE",
                                       nos.declusionlpu AS "DECLUSION_LPU",
                                       nos.exemptionby178 AS "EX178",
                                       nos.exemptionby890 AS "EX890",
                                       nos.subjectofrfaccounted AS "RF_ACCOUNT",
                                       TO_CHAR(nos.deathdate, 'DD.MM.YYYY'),
                                       (SELECT XMLAGG
                                               (
                                                   XMLELEMENT
                                                   (
                                                       NAME "NOSOLOGY_BENEFIT",
                                                       XMLFOREST
                                                       (
                                                           nb.uniquenumber AS "UNIQUE_NUMBER",
                                                           nos.nosologyid AS "NOSOLOGYID",
                                                           nb.mkb AS "MKB",
                                                           nb.cardstatus AS "CARD_STATUS",
                                                           nb.head AS "HEAD",
                                                           nb.diagnosis AS "DIAGNOSIS",
                                                           nb.disability AS "DIASBILITY",
                                                           nb.therapyresistance AS "THERAPY_RESISTANCE",
                                                           b.benefitcode AS "BENEFIT_CODE",
                                                           TO_CHAR(nb.datebegin, 'DD.MM.YYYY'),
                                                           TO_CHAR(nb.enddate, 'DD.MM.YYYY')
                                                       )
                                                   )
                                               )
                                        FROM rds.t_nosology_benefits nb
                                        JOIN rds.t_benefits b ON b.benefitsid = nb.benefits
                                        WHERE nb.nosology = nos.nosologyid
                                       ) AS "NOSOLOGY_BENEFITS"
                                   )
                               )
                        FROM rds.t_nosology nos
                        LEFT JOIN rds.t_region reg ON reg.regionid = nos.region
                        LEFT JOIN vclib.t_enums esex ON esex.enumtypeid = 103 AND esex.enumid = nos.sex
                        LEFT JOIN vclib.t_enums edoc ON edoc.enumtypeid = 105 AND edoc.enumid = nos.documenttype
                       )
                   )
            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;
$$