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;
        ELSIF (p_isnew = TRUE) THEN
            v_operationtype := c_insert;
        ELSIF (p_isnew = FALSE AND EXISTS (SELECT 1 FROM rds.t_nosology n WHERE n.nosologyid = p_id)) THEN
            v_operationtype := c_update;
        ELSIF (p_isnew = FALSE AND not 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",
                        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;

        IF (v_operationtype = c_ftload) THEN
            SELECT '<NOSOLOGY><header><TYPE>' || v_operationtype || '</TYPE></header><body><NOSOLOGYS>' ||
                   COALESCE(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",
                    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
                        WHERE nos.nosologyid = p_id
                    )
                )
            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;
$$;