DROP PROCEDURE IF EXISTS public.sp_getsetrdsnosology;

CREATE OR REPLACE PROCEDURE public.sp_getsetrdsnosology(
    "Xml" text DEFAULT NULL
, "ID" uuid DEFAULT NULL::uuid
, "IsNew" integer = 1
, "QueueName" character varying DEFAULT 'rds.registry'::character varying
, "dateTime" timestamp without time zone DEFAULT NULL::timestamp without time zone
, "User" uuid DEFAULT NULL::uuid
)
    LANGUAGE plpgsql
AS
$$
DECLARE
    p_xml             xml          := "Xml"::xml;
    p_id              uuid         := "ID";
    p_isnew           boolean      := "IsNew"::boolean;
    p_queuename       varchar(100) := "QueueName";
    p_datetime        timestamp    := COALESCE("dateTime", CURRENT_TIMESTAMP);
    p_user            uuid         := "User";
    v_result          text ;
    v_contentencoding varchar(255);
    v_type            varchar(255);
    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 NOT NULL)
    THEN
        
        SELECT (XPATH('/NOSOLOGY/header/TYPE/text()', x))[1]::text     AS type,
               (XPATH('/NOSOLOGY/header/ID/text()', x))[1]::text::uuid AS id,
               p_xml ::text
        INTO v_operationtype,
            v_ownerid,
            v_result
        FROM UNNEST(XPATH('/NOSOLOGY', p_xml)) AS x;
        
        CREATE TEMP TABLE temp_nosology
        (
            id                   uuid,
            adress               VARCHAR(256),
            cardstatus           VARCHAR(256),
            comment              VARCHAR(1024),
            dateofdeclusion      timestamp,
            dateofinclusion      timestamp,
            executiveauthority   VARCHAR(256),
            head                 VARCHAR(256),
            omsnumber            VARCHAR(30),
            omsserial            VARCHAR(30),
            uniquenumber         bigint,
            mkb                  VARCHAR(10),
            diagnosis            VARCHAR(4096),
            docseries            VARCHAR(30),
            docnumber            VARCHAR(30),
            birthday             timestamp,
            firstname            VARCHAR(60),
            lastname             VARCHAR(60),
            fathername           VARCHAR(60),
            docissuedate         timestamp,
            docissueorg          VARCHAR(256),
            declusionlpu         VARCHAR(6),
            documenttype         INT,
            exemptionby178       boolean,
            exemptionby890       boolean,
            inclusionlpu         VARCHAR(6),
            region               VARCHAR(13),
            sex                  INT,
            snils                VARCHAR(14),
            birthdaylastname     VARCHAR(60),
            subjectofrfaccounted boolean,
            deathdate            timestamp,
            okato                text,
            f002smo              VARCHAR(5),
            patient              uuid,
            dateofchange         timestamp
        );
        
        CREATE TEMP TABLE temp_nosology_benefits
        (
            nosologyid        uuid,
            mkb               VARCHAR(10),
            cardstatus        VARCHAR(256),
            head              VARCHAR(256),
            diagnosis         VARCHAR(4096),
            disability        boolean,
            therapyresistance boolean,
            benefitcode       VARCHAR(255),
            datebegin         timestamp,
            enddate           timestamp,
            uniquenumber      bigint
        );
        
        IF (v_operationtype = c_ftload) THEN
            
            INSERT INTO
                temp_nosology
            (id,
             adress,
             comment,
             dateofchange,
             dateofdeclusion,
             dateofinclusion,
             executiveauthority,
             omsnumber,
             omsserial,
             docseries,
             docnumber,
             birthday,
             firstname,
             lastname,
             fathername,
             docissuedate,
             docissueorg,
             declusionlpu,
             documenttype,
             exemptionby178,
             exemptionby890,
             subjectofrfaccounted,
             inclusionlpu,
             region,
             sex,
             snils,
             birthdaylastname,
             deathdate,
             okato,
             f002smo)
            SELECT (XPATH('/NOSOLOGY/ID/text()', x))[1]::text::uuid                  AS id,
                   (XPATH('/NOSOLOGY/ADRESS/text()', x))[1]::text                    AS adress,
                   (XPATH('/NOSOLOGY/COMMENT/text()', x))[1]::text                   AS comment,
                   (XPATH('/NOSOLOGY/CHANGE_DATE/text()', x))[1]::text::timestamp    AS changedate,
                   (XPATH('/NOSOLOGY/DECLUSION_DATE/text()', x))[1]::text::timestamp AS declusiondate,
                   (XPATH('/NOSOLOGY/INCLUSION_DATE/text()', x))[1]::text::timestamp AS inclusiondate,
                   (XPATH('/NOSOLOGY/EXECUTIVE_AUTHORITY/text()', x))[1]::text       AS executive_authority,
                   (XPATH('/NOSOLOGY/OMS_NUMBER/text()', x))[1]::text                AS oms_number,
                   (XPATH('/NOSOLOGY/OMS_SERIAL/text()', x))[1]::text                AS oms_serial,
                   (XPATH('/NOSOLOGY/DOC_SERIES/text()', x))[1]::text                AS doc_series,
                   (XPATH('/NOSOLOGY/DOC_NUMBER/text()', x))[1]::text                AS doc_number,
                   (XPATH('/NOSOLOGY/BIRTHDAY/text()', x))[1]::text::timestamp       AS birthday,
                   (XPATH('/NOSOLOGY/FIRST_NAME/text()', x))[1]::text                AS first_name,
                   (XPATH('/NOSOLOGY/LAST_NAME/text()', x))[1]::text                 AS last_name,
                   (XPATH('/NOSOLOGY/FATHER_NAME/text()', x))[1]::text               AS father_name,
                   (XPATH('/NOSOLOGY/DOC_ISSUE_DATE/text()', x))[1]::text::timestamp AS doc_issue_date,
                   (XPATH('/NOSOLOGY/DOC_ISSUE_ORG/text()', x))[1]::text             AS doc_issue_org,
                   (XPATH('/NOSOLOGY/DECLUSION_LPU/text()', x))[1]::text             AS declusion_lpu,
                   (XPATH('/NOSOLOGY/DOC_TYPE/text()', x))[1]::text::int             AS doc_type,
                   (XPATH('/NOSOLOGY/EX178/text()', x))[1]::text::boolean            AS ex178,
                   (XPATH('/NOSOLOGY/EX890/text()', x))[1]::text::boolean            AS ex890,
                   (XPATH('/NOSOLOGY/RF_ACCOUNT/text()', x))[1]::text::boolean       AS subjectofrfaccounted,
                   (XPATH('/NOSOLOGY/INCLUSION_LPU/text()', x))[1]::text             AS inclusion_lpu,
                   (XPATH('/NOSOLOGY/REGION/text()', x))[1]::text                    AS region,
                   (XPATH('/NOSOLOGY/SEX/text()', x))[1]::text::int                  AS sex,
                   (XPATH('/NOSOLOGY/SNILS/text()', x))[1]::text                     AS snils,
                   (XPATH('/NOSOLOGY/BIRTHDAY_LAST_NAME/text()', x))[1]::text        AS birthday_last_name,
                   (XPATH('/NOSOLOGY/DEATH_DATE/text()', x))[1]::text::timestamp     AS death_date,
                   (XPATH('/NOSOLOGY/OKATO/text()', x))[1]::text                     AS okato,
                   (XPATH('/NOSOLOGY/SMO/text()', x))[1]::text                       AS f002smo
            FROM UNNEST(XPATH('/NOSOLOGY/body/NOSOLOGYS/NOSOLOGY', p_xml)) AS x;
            
            ANALYZE temp_nosology;
            
            INSERT INTO
                temp_nosology_benefits
            (nosologyid,
             cardstatus,
             therapyresistance,
             head,
             mkb,
             diagnosis,
             uniquenumber,
             disability,
             benefitcode,
             datebegin,
             enddate)
            SELECT (XPATH('/NOSOLOGY_BENEFIT/NOSOLOGYID/text()', x))[1]::text::uuid            AS nosologyid,
                   (XPATH('/NOSOLOGY_BENEFIT/CARD_STATUS/text()', x))[1]::text                 AS card_status,
                   (XPATH('/NOSOLOGY_BENEFIT/THERAPY_RESISTANCE/text()', x))[1]::text::boolean AS therapy_resistance,
                   (XPATH('/NOSOLOGY_BENEFIT/HEAD/text()', x))[1]::text                        AS head,
                   (XPATH('/NOSOLOGY_BENEFIT/MKB/text()', x))[1]::text                         AS mkb,
                   (XPATH('/NOSOLOGY_BENEFIT/DIAGNOSIS/text()', x))[1]::text                   AS diagnosis,
                   (XPATH('/NOSOLOGY_BENEFIT/UNIQUE_NUMBER/text()', x))[1]::text::bigint       AS unique_number,
                   (XPATH('/NOSOLOGY_BENEFIT/DIASBILITY/text()', x))[1]::text::boolean         AS disability,
                   (XPATH('/NOSOLOGY_BENEFIT/BENEFIT_CODE/text()', x))[1]::text                AS benefit_code,
                   (XPATH('/NOSOLOGY_BENEFIT/DATE_BEGIN/text()', x))[1]::text::timestamp       AS date_begin,
                   (XPATH('/NOSOLOGY_BENEFIT/END_DATE/text()', x))[1]::text::timestamp         AS end_date
            FROM UNNEST(XPATH('/NOSOLOGY/body/NOSOLOGYS/NOSOLOGY/NOSOLOGY_BENEFITS/NOSOLOGY_BENEFIT', p_xml)) AS x;
            
            ANALYZE temp_nosology_benefits;
        
        ELSE
            
            INSERT INTO
                temp_nosology
            (id,
             adress,
             comment,
             dateofchange,
             dateofdeclusion,
             dateofinclusion,
             executiveauthority,
             omsnumber,
             omsserial,
             docseries,
             docnumber,
             birthday,
             firstname,
             lastname,
             fathername,
             docissuedate,
             docissueorg,
             declusionlpu,
             documenttype,
             exemptionby178,
             exemptionby890,
             subjectofrfaccounted,
             inclusionlpu,
             region,
             sex,
             snils,
             birthdaylastname,
             deathdate,
             okato,
             f002smo)
            SELECT (XPATH('/NOSOLOGY/body/ID/text()', x))[1]::text::uuid                  AS id,
                   (XPATH('/NOSOLOGY/body/ADRESS/text()', x))[1]::text                    AS adress,
                   (XPATH('/NOSOLOGY/body/COMMENT/text()', x))[1]::text                   AS comment,
                   (XPATH('/NOSOLOGY/body/CHANGE_DATE/text()', x))[1]::text::timestamp    AS changedate,
                   (XPATH('/NOSOLOGY/body/DECLUSION_DATE/text()', x))[1]::text::timestamp AS declusiondate,
                   (XPATH('/NOSOLOGY/body/INCLUSION_DATE/text()', x))[1]::text::timestamp AS inclusiondate,
                   (XPATH('/NOSOLOGY/body/EXECUTIVE_AUTHORITY/text()', x))[1]::text       AS executive_authority,
                   (XPATH('/NOSOLOGY/body/OMS_NUMBER/text()', x))[1]::text                AS oms_number,
                   (XPATH('/NOSOLOGY/body/OMS_SERIAL/text()', x))[1]::text                AS oms_serial,
                   (XPATH('/NOSOLOGY/body/DOC_SERIES/text()', x))[1]::text                AS doc_series,
                   (XPATH('/NOSOLOGY/body/DOC_NUMBER/text()', x))[1]::text                AS doc_number,
                   (XPATH('/NOSOLOGY/body/BIRTHDAY/text()', x))[1]::text::timestamp       AS birthday,
                   (XPATH('/NOSOLOGY/body/FIRST_NAME/text()', x))[1]::text                AS first_name,
                   (XPATH('/NOSOLOGY/body/LAST_NAME/text()', x))[1]::text                 AS last_name,
                   (XPATH('/NOSOLOGY/body/FATHER_NAME/text()', x))[1]::text               AS father_name,
                   (XPATH('/NOSOLOGY/body/DOC_ISSUE_DATE/text()', x))[1]::text::timestamp AS doc_issue_date,
                   (XPATH('/NOSOLOGY/body/DOC_ISSUE_ORG/text()', x))[1]::text             AS doc_issue_org,
                   (XPATH('/NOSOLOGY/body/DECLUSION_LPU/text()', x))[1]::text             AS declusion_lpu,
                   (XPATH('/NOSOLOGY/body/DOC_TYPE/text()', x))[1]::text::int             AS doc_type,
                   (XPATH('/NOSOLOGY/body/EX178/text()', x))[1]::text::boolean            AS ex178,
                   (XPATH('/NOSOLOGY/body/EX890/text()', x))[1]::text::boolean            AS ex890,
                   (XPATH('/NOSOLOGY/body/RF_ACCOUNT/text()', x))[1]::text::boolean       AS subjectofrfaccounted,
                   (XPATH('/NOSOLOGY/body/INCLUSION_LPU/text()', x))[1]::text             AS inclusion_lpu,
                   (XPATH('/NOSOLOGY/body/REGION/text()', x))[1]::text                    AS region,
                   (XPATH('/NOSOLOGY/body/SEX/text()', x))[1]::text::int                  AS sex,
                   (XPATH('/NOSOLOGY/body/SNILS/text()', x))[1]::text                     AS snils,
                   (XPATH('/NOSOLOGY/body/BIRTHDAY_LAST_NAME/text()', x))[1]::text        AS birthday_last_name,
                   (XPATH('/NOSOLOGY/body/DEATH_DATE/text()', x))[1]::text::timestamp     AS death_date,
                   (XPATH('/NOSOLOGY/body/OKATO/text()', x))[1]::text                     AS okato,
                   (XPATH('/NOSOLOGY/body/SMO/text()', x))[1]::text                       AS f002smo
            FROM UNNEST(XPATH('/NOSOLOGY', p_xml)) AS x;
            
            
            INSERT INTO
                temp_nosology_benefits
            (nosologyid,
             cardstatus,
             therapyresistance,
             head,
             mkb,
             diagnosis,
             uniquenumber,
             disability,
             benefitcode,
             datebegin,
             enddate)
            SELECT (XPATH('/NOSOLOGY_BENEFIT/NOSOLOGYID/text()', x))[1]::text::uuid            AS nosologyid,
                   (XPATH('/NOSOLOGY_BENEFIT/CARD_STATUS/text()', x))[1]::text                 AS card_status,
                   (XPATH('/NOSOLOGY_BENEFIT/THERAPY_RESISTANCE/text()', x))[1]::text::boolean AS therapy_resistance,
                   (XPATH('/NOSOLOGY_BENEFIT/HEAD/text()', x))[1]::text                        AS head,
                   (XPATH('/NOSOLOGY_BENEFIT/MKB/text()', x))[1]::text                         AS mkb,
                   (XPATH('/NOSOLOGY_BENEFIT/DIAGNOSIS/text()', x))[1]::text                   AS diagnosis,
                   (XPATH('/NOSOLOGY_BENEFIT/UNIQUE_NUMBER/text()', x))[1]::text::bigint       AS unique_number,
                   (XPATH('/NOSOLOGY_BENEFIT/DIASBILITY/text()', x))[1]::text::boolean         AS disability,
                   (XPATH('/NOSOLOGY_BENEFIT/BENEFIT_CODE/text()', x))[1]::text                AS benefit_code,
                   (XPATH('/NOSOLOGY_BENEFIT/DATE_BEGIN/text()', x))[1]::text::timestamp       AS date_begin,
                   (XPATH('/NOSOLOGY_BENEFIT/END_DATE/text()', x))[1]::text::timestamp         AS end_date
            FROM UNNEST(XPATH('/NOSOLOGY/body/NOSOLOGY_BENEFITS/NOSOLOGY_BENEFIT', p_xml)) AS x;
        
        END IF;
        
        UPDATE temp_nosology nos
        SET patient = p.patientid
        FROM akuz.t_patient p
        WHERE p.snils = nos.snils
          AND p.birthday = nos.birthday
          AND nos.patient IS NULL
          AND nos.snils IS NOT NULL
          AND nos.birthday IS NOT NULL;
        
        UPDATE temp_nosology nos
        SET patient = p.patientid
        FROM akuz.t_patient p
        WHERE LEFT(p.docseries, 8) = nos.docseries
          AND LEFT(p.docnumber, 8) = nos.docnumber
          AND p.birthday = nos.birthday
          AND nos.patient IS NULL
          AND nos.docseries IS NOT NULL
          AND nos.docnumber IS NOT NULL
          AND nos.birthday IS NOT NULL;
        
        UPDATE temp_nosology nos
        SET patient = p.patientid
        FROM akuz.t_patient p
        WHERE p.lastname = nos.lastname
          AND p.firstname = nos.firstname
          AND (p.fathername = nos.fathername OR p.fathername IS NULL AND nos.fathername IS NULL)
          AND p.birthday = nos.birthday
          AND nos.patient IS NULL;
        
        IF (v_operationtype = c_ftload) THEN
            
            DELETE
            FROM akuz.t_patient_benefits pd
            WHERE EXISTS (SELECT 1
                          FROM akuz.t_benefits b
                          WHERE b.benefitsid = pd.benefits
                            AND b.benefitviews = 19006
                         )
              AND pd.createtype = 2;
            
            
            INSERT INTO
                akuz.t_patient_benefits
            (patientbenefitsid,
             patient,
             benefits,
             benefitdocument,
             benefitdocumentnumber,
             begindate,
             enddate,
             mkb,
             active,
             deleted,
             createtype,
             pac_id,
             lpu_inf,
             sost_r,
             reasonregistration,
             reasonderegistration,
             benefitdocumentseries,
             benefitdoctype,
             regdate)
            SELECT gen_random_uuid(),
                   nos.patient,
                   (SELECT b.benefitsid
                    FROM akuz.t_benefits b
                    WHERE b.benefitcode = nb.benefitcode
                      AND b.benefitviews = 19006
                    LIMIT 1
                   ),
                   NULL,
                   NULL,
                   nb.datebegin,
                   nb.enddate,
                   nb.mkb,
                   TRUE,
                   FALSE,
                   2,
                   nos.uniquenumber,
                   nos.inclusionlpu,
                   NULL,
                   NULL,
                   NULL,
                   NULL,
                   NULL,
                   nos.dateofinclusion
            FROM temp_nosology                nos
            RIGHT JOIN temp_nosology_benefits nb ON nb.nosologyid = nos.id
            WHERE nos.patient IS NOT NULL;
        
        ELSE
            
            IF (v_operationtype = c_delete) THEN
                
                DELETE
                FROM akuz.t_patient_benefits pd
                    USING
                        akuz.t_benefits b,
                            temp_nosology nos,
                            temp_nosology_benefits nb
                WHERE b.benefitsid = pd.benefits
                  AND nos.patient = pd.patient
                  AND nb.nosologyid = nos.id
                  AND b.benefitviews = 19006
                  AND pd.createtype = 2;
            
            ELSE
                
                DELETE
                FROM akuz.t_patient_benefits pd
                    USING
                        akuz.t_benefits b,
                            temp_nosology nos,
                            temp_nosology_benefits nb
                WHERE b.benefitsid = pd.benefits
                  AND nos.patient = pd.patient
                  AND nb.nosologyid = nos.id
                  AND b.benefitviews = 19006
                  AND pd.createtype = 2;
                
                INSERT INTO
                    akuz.t_patient_benefits
                (patientbenefitsid,
                 patient,
                 benefits,
                 benefitdocument,
                 benefitdocumentnumber,
                 begindate,
                 enddate,
                 mkb,
                 active,
                 deleted,
                 createtype,
                 pac_id,
                 lpu_inf,
                 sost_r,
                 reasonregistration,
                 reasonderegistration,
                 benefitdocumentseries,
                 benefitdoctype,
                 regdate)
                SELECT gen_random_uuid(),
                       nos.patient,
                       (SELECT b.benefitsid
                        FROM akuz.t_benefits b
                        WHERE b.benefitcode = nb.benefitcode
                          AND b.benefitviews = 19006
                        LIMIT 1
                       ),
                       NULL,
                       NULL,
                       nb.datebegin,
                       nb.enddate,
                       nb.mkb,
                       TRUE,
                       FALSE,
                       2,
                       nos.uniquenumber,
                       nos.inclusionlpu,
                       NULL,
                       NULL,
                       NULL,
                       NULL,
                       NULL,
                       nos.dateofinclusion
                FROM temp_nosology                nos
                RIGHT JOIN temp_nosology_benefits nb ON nb.nosologyid = nos.id
                WHERE nos.patient IS NOT NULL;
            END IF;
        
        
        END IF;
    END IF;


END;
$$;