DROP PROCEDURE IF EXISTS dbo.sp_rr_patientbenefits;
-- =============================================
-- Author:      Vitacore (с)
-- Create date: 2024-06-21
-- Description: Загрузка регистра региональных льготников
-- =============================================
DROP PROCEDURE IF EXISTS aps.sp_rr_patientbenefits;

CREATE OR REPLACE PROCEDURE aps.sp_rr_patientbenefits(
    "Xml" text DEFAULT NULL
, "ID" uuid DEFAULT NULL::uuid
, "QueueName" character varying DEFAULT 'RDSSync'::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);
    BEGIN
        "dateTime" := COALESCE("dateTime", CURRENT_TIMESTAMP::TIMESTAMP);

        IF "Xml" IS NOT NULL
        THEN
            _operationtype :=
                NULLIF(TRIM(XPATH('/RR_PATIENT_BENEFIT/header[1]/TYPE[1]/text()', "Xml"::xml)::text, '{}'), '');

            -- Обработка операции
            IF _operationtype = 'FTLoad'
            THEN
                DROP TABLE IF EXISTS tmp_rr_patient_benefits;
                CREATE TEMP TABLE tmp_rr_patient_benefits
                AS
                    SELECT NULLIF(TRIM(XPATH('/RR_PATIENT_BENEFIT/RrPatient[1]/text()', x)::text, '{}'),
                                  '')::int            AS rrpatient
                         , TO_TIMESTAMP(
                        NULLIF(TRIM(XPATH('/RR_PATIENT_BENEFIT/DiagDate[1]/text()', x)::text, '{}'), ''),
                        'DD.MM.YYYY')                 AS diagdate
                         , NULLIF(TRIM(XPATH('/RR_PATIENT_BENEFIT/Document[1]/text()', x)::text, '{}'),
                                  '')::varchar(16)    AS document
                         , TO_TIMESTAMP(
                        NULLIF(TRIM(XPATH('/RR_PATIENT_BENEFIT/DocumentDate[1]/text()', x)::text, '{}'), ''),
                        'DD.MM.YYYY')                 AS documentdate
                         , NULLIF(TRIM(XPATH('/RR_PATIENT_BENEFIT/Dop[1]/text()', x)::text, '{}'),
                                  '')::varchar(1)     AS dop
                         , NULLIF(TRIM(XPATH('/RR_PATIENT_BENEFIT/Mkb[1]/text()', x)::text, '{}'),
                                  '')::varchar(8)     AS mkb
                         , TO_TIMESTAMP(
                        NULLIF(TRIM(XPATH('/RR_PATIENT_BENEFIT/BeginDate[1]/text()', x)::text, '{}'), ''),
                        'DD.MM.YYYY')                 AS begindate
                         , TO_TIMESTAMP(NULLIF(TRIM(XPATH('/RR_PATIENT_BENEFIT/EndDate[1]/text()', x)::text, '{}'), ''),
                                        'DD.MM.YYYY') AS enddate
                         , NULLIF(TRIM(XPATH('/RR_PATIENT_BENEFIT/BenefitCode[1]/text()', x)::text, '{}'),
                                  '')::varchar(255)   AS benefitcode
                         , NULLIF(TRIM(XPATH('/RR_PATIENT_BENEFIT/BenefitViews[1]/text()', x)::text, '{}'),
                                  '')::int            AS benefitviews
                         , NULLIF(TRIM(XPATH('/RR_PATIENT_BENEFIT/Lpu[1]/text()', x)::text, '{}'),
                                  '')::varchar(6)     AS lpu
                         , TO_TIMESTAMP(
                        NULLIF(TRIM(XPATH('/RR_PATIENT_BENEFIT/LastEditDate[1]/text()', x)::text, '{}'), ''),
                        'DD.MM.YYYY')                 AS lasteditdate
                         , NULLIF(TRIM(XPATH('/RR_PATIENT_BENEFIT/Post[1]/text()', x)::text, '{}'),
                                  '')::varchar(10)    AS post
                         , NULLIF(TRIM(XPATH('/RR_PATIENT_BENEFIT/DivisionManager[1]/text()', x)::text, '{}'),
                                  '')::varchar(10)    AS divisionmanager
                    FROM (SELECT UNNEST(XPATH('/RR_PATIENT_BENEFIT/body/RR_PATIENT_BENEFITS/RR_PATIENT_BENEFIT',
                                              "Xml"::xml)) AS x
                         ) rr_patient_benefit;

                ANALYZE tmp_rr_patient_benefits;

                DELETE FROM aps.t_rr_patient_benefit pb;

                INSERT INTO
                    aps.t_rr_patient_benefit (rrpatientbenefitid,
                                              diagdate,
                                              document,
                                              documentdate,
                                              dop,
                                              mkb,
                                              benefits,
                                              rrpatient,
                                              begindate,
                                              enddate,
                                              lasteditdate,
                                              lasteditlpu,
                                              post,
                                              divisionmanager)
                SELECT gen_random_uuid()
                     , npb.diagdate
                     , npb.document
                     , npb.documentdate
                     , npb.dop
                     , npb.mkb
                     , b.benefitsid
                     , npb.rrpatient
                     , npb.begindate
                     , npb.enddate
                     , npb.lasteditdate
                     , npb.lpu
                     , npb.post
                     , npb.divisionmanager
                FROM tmp_rr_patient_benefits npb
                LEFT JOIN vclib.t_enums      ebv ON ebv.code = npb.benefitviews AND ebv.enumtypeid = 190
                LEFT JOIN aps.t_benefits     b ON b.benefitcode = npb.benefitcode AND b.benefitviews = ebv.enumid;
            END IF;
        END IF;
    END;
END;
$$;