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 XMLELEMENT(
                NAME "NOSOLOGY",
                XMLFOREST(
                    XMLELEMENT(
                        NAME "head",
                        XMLFOREST(v_operationtype AS "TYPE")
                    ),
                    XMLELEMENT(
                        NAME "body",
                        XMLELEMENT(
                            NAME "NOSOLOGYS",
                            (SELECT XMLAGG(
                                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"
                                    )
                                )
                            ) 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",
                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,
            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;
$$;