-- =============================================
-- Author:      Vitacore (с)
-- Create date: 2015-11-18
-- Description: Загрузка регистра региональных льготников.
-- =============================================
DROP PROCEDURE IF EXISTS sp_rr_patients;

CREATE PROCEDURE sp_rr_patients(
    "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(255);
        _versionid     uuid := gen_random_uuid();
    BEGIN
        "dateTime" := COALESCE("dateTime", CURRENT_TIMESTAMP);

        IF "Xml" IS NOT NULL
        THEN
            -- Получаем тип операции
            _operationtype := (XPATH('/RR_PATIENT/header[1]/TYPE[1]/text()', "Xml"::xml))[1]::varchar(6);

            -- Обработка операции
            IF _operationtype = 'FTLoad'
            THEN
                -- Generate table #RR_PATIENTS
                DROP TABLE IF EXISTS tmprr_patients;
                CREATE TEMP TABLE tmprr_patients
                AS
                    SELECT (XPATH('RR_PATIENT/RrPatientID[1]/text()', x))[1]::text::int    AS rrpatientid
                         , (XPATH('RR_PATIENT/Snils[1]/text()', x))[1]::varchar(14)        AS snils
                         , (XPATH('RR_PATIENT/DocSeries[1]/text()', x))[1]::varchar(16)    AS docseries
                         , (XPATH('RR_PATIENT/DocNumber[1]/text()', x))[1]::varchar(16)    AS docnumber
                         , (XPATH('RR_PATIENT/LastName[1]/text()', x))[1]::varchar(40)     AS lastname
                         , (XPATH('RR_PATIENT/FirstName[1]/text()', x))[1]::varchar(40)    AS firstname
                         , (XPATH('RR_PATIENT/FatherName[1]/text()', x))[1]::varchar(40)   AS fathername
                         , TO_TIMESTAMP((XPATH('RR_PATIENT/Birthday[1]/text()', x))[1]::varchar(10),
                                        'DD.MM.YYYY')                                      AS birthday
                         , TO_TIMESTAMP((XPATH('RR_PATIENT/DateBegin[1]/text()', x))[1]::varchar(10),
                                        'DD.MM.YYYY')                                      AS datebegin
                         , TO_TIMESTAMP((XPATH('RR_PATIENT/DateEnd[1]/text()', x))[1]::varchar(10),
                                        'DD.MM.YYYY')                                      AS dateend
                         , TO_TIMESTAMP((XPATH('RR_PATIENT/LastEditDate[1]/text()', x))[1]::varchar(10),
                                        'DD.MM.YYYY')                                      AS lasteditdate
                         , (XPATH('RR_PATIENT/Sex[1]/text()', x))[1]::text::int            AS sex
                         , (XPATH('RR_PATIENT/Lpu[1]/text()', x))[1]::varchar(6)           AS lpu
                         , (XPATH('RR_PATIENT/ExcludeReason[1]/text()', x))[1]::text::int  AS excludereason
                         , (XPATH('RR_PATIENT/DocType[1]/text()', x))[1]::text::int        AS tipd
                         , (XPATH('RR_PATIENT/Kod[1]/text()', x))[1]::varchar(32)          AS kod
                         , (XPATH('RR_PATIENT/KodAdr[1]/text()', x))[1]::varchar(11)       AS kodadr
                         , TO_TIMESTAMP((XPATH('RR_PATIENT/DAT_PROV[1]/text()', x))[1]::varchar(10),
                                        'DD.MM.YYYY')                                      AS dat_prov
                         , (XPATH('RR_PATIENT/BirthSurname[1]/text()', x))[1]::varchar(40) AS birthsurname
                         , (XPATH('RR_PATIENT/LpuFirst[1]/text()', x))[1]::varchar(6)      AS lpufirst
                         , (XPATH('RR_PATIENT/SOST_R[1]/text()', x))[1]::text::int         AS sost_r
                    FROM (SELECT UNNEST(XPATH('/RR_PATIENT/body/RR_PATIENTS/RR_PATIENT', "Xml"::xml)) AS x
                         ) rr_patient
                    WHERE _operationtype = 'FTLoad';

                ANALYZE tmprr_patients;

                -- Версия регистра
                INSERT INTO
                    akuz.t_rr_load(version, updatedate, rrloadid, filename, loadtype)
                SELECT (SELECT COUNT(*) FROM akuz.t_rr_load), "dateTime", _versionid, 'RR_PATIENT', 1;
                -- Полная загрузка

                --удаляем все
                DELETE FROM akuz.t_rr_patient;

                -- Загружаем новые
                INSERT INTO
                    akuz.t_rr_patient
                ( pac_id
                , fam
                , im
                , ot
                , w
                , drod
                , ss
                , sdok
                , ndok
                , date_rb
                , date_re
                , dat_reg
                , lpu_inf
                , rrload
                , prichina
                , rrpatientid
                , kod
                , kodadr
                , dat_prov
                , birthsurname
                , lpumainfirst
                , sost_r
                , tipd)
                SELECT nrp.rrpatientid--<PAC_ID, int,>
                     , nrp.lastname--<FAM, varchar(40),>
                     , nrp.firstname--<IM, varchar(40),>
                     , nrp.fathername--<OT, varchar(40),>
                     , nrp.sex--<W, varchar(1),>
                     , nrp.birthday--<DROD, datetime,>
                     , nrp.snils--<SS, varchar(14),>
                     , nrp.docseries--<SDOK, varchar(8),>
                     , nrp.docnumber--<NDOK, varchar(8),>
                     , nrp.datebegin--<DATE_RB, datetime,>
                     , nrp.dateend--<DATE_RE, datetime,>
                     , nrp.lasteditdate--<DAT_REG, datetime,>
                     , nrp.lpu--<LPU_INF, varchar(4),>
                     , _versionid--<RrLoad, uniqueidentifier,>
                     , nrp.excludereason--<PRICHINA, varchar(1),>
                     , gen_random_uuid()--<RrPatientID, uniqueidentifier,>)
                     , nrp.kod
                     , nrp.kodadr
                     , nrp.dat_prov
                     , nrp.birthsurname
                     , nrp.lpufirst
                     , nrp.sost_r
                     , nrp.tipd
                FROM tmprr_patients nrp;

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

                -- Поиск по серии и номеру документа
                UPDATE akuz.t_rr_patient rp
                SET patient = p.patientid
                FROM akuz.t_patient p
                WHERE LEFT(p.docseries, 8) = rp.sdok
                  AND LEFT(p.docnumber, 8) = rp.ndok
                  AND rp.patient IS NULL
                  AND rp.sdok IS NOT NULL
                  AND rp.sdok <> ''
                  AND rp.ndok IS NOT NULL
                  AND rp.ndok <> '';

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

            END IF;
        END IF;
    END;
END;
$$;