-- =============================================
-- Author:      Vitacore (с)
-- Create date: 2020-09-01
-- Description: Загрузка льготников по орфанным заболеваниям
-- =============================================
DROP PROCEDURE IF EXISTS sp_getsetrdsorphregister;

CREATE PROCEDURE sp_getsetrdsorphregister(
    "Xml" text DEFAULT NULL
, "ID" uuid DEFAULT NULL::uuid
, "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
$$
BEGIN
    DECLARE
        _operationtype varchar(36);
    BEGIN
        IF ("Xml" IS NOT NULL)
        THEN
            "dateTime" := COALESCE("dateTime", CURRENT_TIMESTAMP::TIMESTAMP);

            -- Получаем тип операции
            SELECT INTO
                _operationtype (XPATH('/ORPHAN_REGISTER/header[1]/TYPE[1]/text()', "Xml"::xml))[1]::varchar(36);


            IF (_operationtype = 'FTLoad')
            THEN
                -- Generate table TMP_ORPHAN_REGISTERS
                DROP TABLE IF EXISTS tmp_orphan_registers;
                CREATE TEMP TABLE tmp_orphan_registers
                AS
                    SELECT (XPATH('ORPHAN_REGISTER_ITEM/ID[1]/text()', x))[1]::text::uuid               AS id
                         , TO_TIMESTAMP((XPATH('ORPHAN_REGISTER_ITEM/DATE_BEGIN[1]/text()', x))[1]::text,
                                        'DD.MM.YYYY')                                                   AS datebegin
                         , TO_TIMESTAMP((XPATH('ORPHAN_REGISTER_ITEM/DATE_END[1]/text()', x))[1]::text,
                                        'DD.MM.YYYY')                                                   AS dateend
                         , (XPATH('ORPHAN_REGISTER_ITEM/FIRST_NAME[1]/text()', x))[1]::varchar(60)      AS firstname
                         , (XPATH('ORPHAN_REGISTER_ITEM/LAST_MANE[1]/text()', x))[1]::varchar(60)       AS lastname
                         , (XPATH('ORPHAN_REGISTER_ITEM/BIRTH_LAST_MANE[1]/text()', x))[1]::varchar(60) AS birthlastname
                         , (XPATH('ORPHAN_REGISTER_ITEM/FATHER_NAME[1]/text()', x))[1]::varchar(60)     AS fathername
                         , TO_TIMESTAMP((XPATH('ORPHAN_REGISTER_ITEM/BIRTHDAY[1]/text()', x))[1]::text,
                                        'DD.MM.YYYY')                                                   AS birthday
                         , (XPATH('ORPHAN_REGISTER_ITEM/SEX[1]/text()', x))[1]::text::int               AS sex
                         , (XPATH('ORPHAN_REGISTER_ITEM/SNILS[1]/text()', x))[1]::varchar(14)           AS snils
                         , (XPATH('ORPHAN_REGISTER_ITEM/DIASBILITY[1]/text()', x))[1]::text::int        AS disability
                         , (XPATH('ORPHAN_REGISTER_ITEM/ADRESS[1]/text()', x))[1]::varchar(1000)        AS adress
                         , (XPATH('ORPHAN_REGISTER_ITEM/OKATO[1]/text()', x))[1]::varchar(256)          AS okato
                         , (XPATH('ORPHAN_REGISTER_ITEM/FEDERAL[1]/text()', x))[1]::text::boolean       AS federal
                         , TO_TIMESTAMP((XPATH('ORPHAN_REGISTER_ITEM/DOC_ISSUE_DATE[1]/text()', x))[1]::text,
                                        'DD.MM.YYYY')                                                   AS docissuedate
                         , (XPATH('ORPHAN_REGISTER_ITEM/DOC_ISSUE_ORG[1]/text()', x))[1]::varchar(256)  AS docissueorg
                         , (XPATH('ORPHAN_REGISTER_ITEM/DOC_TYPE[1]/text()', x))[1]::text::int          AS doctype
                         , (XPATH('ORPHAN_REGISTER_ITEM/DOC_SERIES[1]/text()', x))[1]::varchar(30)      AS docseries
                         , (XPATH('ORPHAN_REGISTER_ITEM/DOC_NUMBER[1]/text()', x))[1]::varchar(30)      AS docnumber
                         , (XPATH('ORPHAN_REGISTER_ITEM/SMO[1]/text()', x))[1]::varchar(5)              AS f002smo
                         , (XPATH('ORPHAN_REGISTER_ITEM/OMS_SERIAL[1]/text()', x))[1]::varchar(12)      AS omsserial
                         , (XPATH('ORPHAN_REGISTER_ITEM/OMS_NUMBER[1]/text()', x))[1]::varchar(32)      AS omsnumber
                         , (XPATH('ORPHAN_REGISTER_ITEM/MKB[1]/text()', x))[1]::varchar(10)             AS mkb
                         , (XPATH('ORPHAN_REGISTER_ITEM/DIAGNOSIS[1]/text()', x))[1]::varchar(4096)     AS diagnosis
                         , (XPATH('ORPHAN_REGISTER_ITEM/MKB_DOP[1]/text()', x))[1]::varchar(10)         AS mkbdop
                         , (XPATH('ORPHAN_REGISTER_ITEM/DIAGNOSIS_DOP[1]/text()', x))[1]::varchar(4096) AS diagnosisdop
                         , (XPATH('ORPHAN_REGISTER_ITEM/LPU_MAIN_FIRST[1]/text()', x))[1]::varchar(6)   AS lpumainfirst
                         , (XPATH('ORPHAN_REGISTER_ITEM/LPU_MAIN[1]/text()', x))[1]::varchar(6)         AS lpumain
                         , (XPATH('ORPHAN_REGISTER_ITEM/POST_FIO[1]/text()', x))[1]::varchar(255)       AS postfio
                         , (XPATH('ORPHAN_REGISTER_ITEM/REGISTER_ID[1]/text()', x))[1]::text::int       AS registerid
                    FROM (SELECT UNNEST(XPATH('/ORPHAN_REGISTER/body/ORPHAN_REGISTERS/ORPHAN_REGISTER_ITEM',
                                              "Xml"::xml)) AS x
                         ) orphan_register_item;

                ANALYZE tmp_orphan_registers;

                ALTER TABLE tmp_orphan_registers
                    ADD patient uuid;

                -- Поиск по СНИЛС + ДР
                UPDATE tmp_orphan_registers orph
                SET patient = p.patientid
                FROM akuz.t_patient p
                WHERE p.snils = orph.snils
                  AND p.birthday = orph.birthday
                  AND orph.patient IS NULL
                  AND orph.snils IS NOT NULL
                  AND orph.birthday IS NOT NULL;

                -- Поиск по серии и номеру документа
                UPDATE tmp_orphan_registers orph
                SET patient = p.patientid
                FROM akuz.t_patient p
                WHERE LEFT(p.docseries, 8) = orph.docseries
                  AND LEFT(p.docnumber, 8) = orph.docnumber
                  AND p.birthday = orph.birthday
                  AND orph.patient IS NULL
                  AND orph.docseries IS NOT NULL
                  AND orph.docnumber IS NOT NULL
                  AND orph.birthday IS NOT NULL;

                -- Поиск по ФИО + ДР
                UPDATE tmp_orphan_registers orph
                SET patient = p.patientid
                FROM akuz.t_patient p
                WHERE p.lastname = orph.lastname
                  AND p.firstname = orph.firstname
                  AND (p.fathername = orph.fathername OR p.fathername IS NULL AND orph.fathername IS NULL)
                  AND p.birthday = orph.birthday
                  AND orph.patient IS NULL;

                DELETE
                FROM akuz.t_patient_benefits pd
                    USING akuz.t_benefits b
                WHERE b.benefitsid = pd.benefits
                  AND b.benefitviews = 19011
                  AND 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()
                     , orph.patient
                     , (SELECT b.benefitsid FROM akuz.t_benefits b WHERE b.benefitviews = 19011 LIMIT 1)
                     , NULL
                     , NULL
                     , orph.datebegin
                     , orph.dateend
                     , (SELECT m.mkbid FROM akuz.t_mkb m WHERE orph.mkb = m.mcbcode LIMIT 1)
                     , '1'
                     , '0'
                     , 2
                     , orph.registerid
                     , orph.lpumain
                     , NULL
                     , NULL
                     , NULL
                     , NULL
                     , NULL
                     , orph.datebegin
                FROM tmp_orphan_registers orph
                WHERE orph.patient IS NOT NULL;

            ELSE
                -- Generate table TMP_ORPHAN_REGISTER
                DROP TABLE IF EXISTS tmp_orphan_register;
                CREATE TEMP TABLE tmp_orphan_register
                AS
                    SELECT (XPATH('ORPHAN_REGISTER/body[1]/ID[1]/text()', x))[1]::text::uuid               AS id
                         , TO_TIMESTAMP((XPATH('ORPHAN_REGISTER/body[1]/DATE_BEGIN[1]/text()', x))[1]::text,
                                        'DD.MM.YYYY')                                                      AS datebegin
                         , TO_TIMESTAMP((XPATH('ORPHAN_REGISTER/body[1]/DATE_END[1]/text()', x))[1]::text,
                                        'DD.MM.YYYY')                                                      AS dateend
                         , (XPATH('ORPHAN_REGISTER/body[1]/FIRST_NAME[1]/text()', x))[1]::varchar(60)      AS firstname
                         , (XPATH('ORPHAN_REGISTER/body[1]/LAST_MANE[1]/text()', x))[1]::varchar(60)       AS lastname
                         , (XPATH('ORPHAN_REGISTER/body[1]/BIRTH_LAST_MANE[1]/text()', x))[1]::varchar(60) AS birthlastname
                         , (XPATH('ORPHAN_REGISTER/body[1]/FATHER_NAME[1]/text()', x))[1]::varchar(60)     AS fathername
                         , TO_TIMESTAMP((XPATH('ORPHAN_REGISTER/body[1]/BIRTHDAY[1]/text()', x))[1]::text,
                                        'DD.MM.YYYY')                                                      AS birthday
                         , (XPATH('ORPHAN_REGISTER/body[1]/SEX[1]/text()', x))[1]::text::int               AS sex
                         , (XPATH('ORPHAN_REGISTER/body[1]/SNILS[1]/text()', x))[1]::varchar(14)           AS snils
                         , (XPATH('ORPHAN_REGISTER/body[1]/DIASBILITY[1]/text()', x))[1]::text::int        AS disability
                         , (XPATH('ORPHAN_REGISTER/body[1]/ADRESS[1]/text()', x))[1]::varchar(1000)        AS adress
                         , (XPATH('ORPHAN_REGISTER/body[1]/OKATO[1]/text()', x))[1]::varchar(256)          AS okato
                         , (XPATH('ORPHAN_REGISTER/body[1]/FEDERAL[1]/text()', x))[1]::text::boolean       AS federal
                         , TO_TIMESTAMP((XPATH('ORPHAN_REGISTER/body[1]/DOC_ISSUE_DATE[1]/text()', x))[1]::text,
                                        'DD.MM.YYYY')                                                      AS docissuedate
                         , (XPATH('ORPHAN_REGISTER/body[1]/DOC_ISSUE_ORG[1]/text()', x))[1]::varchar(256)  AS docissueorg
                         , (XPATH('ORPHAN_REGISTER/body[1]/DOC_TYPE[1]/text()', x))[1]::text::int          AS doctype
                         , (XPATH('ORPHAN_REGISTER/body[1]/DOC_SERIES[1]/text()', x))[1]::varchar(30)      AS docseries
                         , (XPATH('ORPHAN_REGISTER/body[1]/DOC_NUMBER[1]/text()', x))[1]::varchar(30)      AS docnumber
                         , (XPATH('ORPHAN_REGISTER/body[1]/SMO[1]/text()', x))[1]::varchar(5)              AS f002smo
                         , (XPATH('ORPHAN_REGISTER/body[1]/OMS_SERIAL[1]/text()', x))[1]::varchar(12)      AS omsserial
                         , (XPATH('ORPHAN_REGISTER/body[1]/OMS_NUMBER[1]/text()', x))[1]::varchar(32)      AS omsnumber
                         , (XPATH('ORPHAN_REGISTER/body[1]/MKB[1]/text()', x))[1]::varchar(10)             AS mkb
                         , (XPATH('ORPHAN_REGISTER/body[1]/DIAGNOSIS[1]/text()', x))[1]::varchar(4096)     AS diagnosis
                         , (XPATH('ORPHAN_REGISTER/body[1]/MKB_DOP[1]/text()', x))[1]::varchar(10)         AS mkbdop
                         , (XPATH('ORPHAN_REGISTER/body[1]/DIAGNOSIS_DOP[1]/text()', x))[1]::varchar(4096) AS diagnosisdop
                         , (XPATH('ORPHAN_REGISTER/body[1]/LPU_MAIN_FIRST[1]/text()', x))[1]::varchar(6)   AS lpumainfirst
                         , (XPATH('ORPHAN_REGISTER/body[1]/LPU_MAIN[1]/text()', x))[1]::varchar(6)         AS lpumain
                         , (XPATH('ORPHAN_REGISTER/body[1]/POST_FIO[1]/text()', x))[1]::varchar(255)       AS postfio
                         , (XPATH('ORPHAN_REGISTER/body[1]/REGISTER_ID[1]/text()', x))[1]::text::int       AS registerid
                    FROM (SELECT UNNEST(XPATH('/ORPHAN_REGISTER', "Xml"::xml)) AS x
                         ) orphan_register;

                ANALYZE tmp_orphan_register;

                ALTER TABLE tmp_orphan_register
                    ADD patient uuid;

                -- Связываем регистр с пациентами
                -- Поиск по СНИЛС + ДР
                UPDATE tmp_orphan_register orph
                SET patient = p.patientid
                FROM akuz.t_patient p
                WHERE p.snils = orph.snils
                  AND p.birthday = orph.birthday
                  AND orph.patient IS NULL
                  AND orph.snils IS NOT NULL
                  AND orph.birthday IS NOT NULL;

                -- Поиск по серии и номеру документа
                UPDATE tmp_orphan_register orph
                SET patient = p.patientid
                FROM akuz.t_patient p
                WHERE LEFT(p.docseries, 8) = orph.docseries
                  AND LEFT(p.docnumber, 8) = orph.docnumber
                  AND p.birthday = orph.birthday
                  AND orph.patient IS NULL
                  AND orph.docseries IS NOT NULL
                  AND orph.docnumber IS NOT NULL
                  AND orph.birthday IS NOT NULL;

                -- Поиск по ФИО + ДР
                UPDATE tmp_orphan_register orph
                SET patient = p.patientid
                FROM akuz.t_patient p
                WHERE p.lastname = orph.lastname
                  AND p.firstname = orph.firstname
                  AND (p.fathername = orph.fathername OR p.fathername IS NULL AND orph.fathername IS NULL)
                  AND p.birthday = orph.birthday
                  AND orph.patient IS NULL;

                  
                 DELETE
                 FROM akuz.t_patient_benefits pd
                     USING akuz.t_benefits b,
                           tmp_orphan_register orph 
                 WHERE  orph.registerid = pd.pac_id
                     and b.benefitsid = pd.benefits
                   AND b.benefitviews = 19011
                   AND pd.createtype = 2;
                
                IF (_operationtype <> 'Delete')
                THEN
                    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()
                         , orph.patient
                         , (SELECT b.benefitsid FROM akuz.t_benefits b WHERE b.benefitviews = 19011 LIMIT 1)
                         , NULL
                         , NULL
                         , orph.datebegin
                         , orph.dateend
                         , orph.mkb
                         , '1'
                         , '0'
                         , 2
                         , orph.registerid
                         , orph.lpumain
                         , NULL
                         , NULL
                         , NULL
                         , NULL
                         , NULL
                         , orph.datebegin
                    FROM tmp_orphan_register orph
                    WHERE orph.patient IS NOT NULL;
                END IF;
            END IF;
        END IF;
    END;
END;
$$;