DROP PROCEDURE IF EXISTS public.sp_getsetnosologylist;

CREATE OR REPLACE PROCEDURE public.sp_getsetnosologylist(
    "Xml" TEXT 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"::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",
                        XMLFOREST(
                            XMLELEMENT(
                                NAME "header",
                                XMLFOREST(
                                    v_operationtype AS "TYPE",
                                    p_id AS "ID"
                                )
                            ),
                            XMLELEMENT(
                                NAME "body",
                                XMLFOREST(
                                    p_snils AS "SNILS",
                                    TO_CHAR(p_birthday, 'DD.MM.YYYY') AS "BIRTHDAY",
                                    p_docseries AS "DOC_SERIES",
                                    p_docnumber AS "DOC_NUMBER",
                                    p_firstname AS "FIRST_NAME",
                                    p_lastname AS "LAST_NAME",
                                    p_fathername AS "FATHER_NAME",
                                    p_pacid AS "PAC_ID"
                                )
                            )
                        )
                    ) INTO v_result;
                END IF;
            END IF;
        END IF;
        
        IF (v_operationtype = c_ftload) THEN
            SELECT 
			    '<NOSOLOGY><header><TYPE>' || v_operationtype || '</TYPE></header><body><NOSOLOGYS>' ||
			    string_agg(
			        XMLSERIALIZE(CONTENT patient_xml AS TEXT),
			        ''
			    ) ||
			    '</NOSOLOGYS></body></NOSOLOGY>'
			INTO v_result
            FROM (
                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')                        AS "DEATH_DATE",
                   (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') AS "DATE_BEGIN",
                               TO_CHAR(nb.enddate, 'DD.MM.YYYY')   AS "END_DATE"
                           )
                       )
                   ) FROM rds.t_nosology_benefits nb
                   JOIN rds.t_benefits b ON b.benefitsid = nb.benefits
                   WHERE nb.nosology = nos.nosologyid) AS "NOSOLOGY_BENEFITS"
			)
               ) AS patient_xml
            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
        ) AS patients;
        
        ELSIF (v_operationtype <> c_delete) THEN
            SELECT XMLELEMENT(
                NAME "NOSOLOGY",
                XMLFOREST(
                    XMLELEMENT(
                        NAME "header",
                        XMLFOREST(
                            v_operationtype AS "TYPE",
                            p_id AS "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') AS "DEATH_DATE",
                            (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') AS "DATE_BEGIN",
                                        TO_CHAR(nb.enddate, 'DD.MM.YYYY') AS "END_DATE"
                                    )
                                )
                            ) 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,
            now(),
            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;
$$;