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

CREATE PROCEDURE sp_rr_patientbenefits(
    "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);
    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
                -- Generate table #RR_PATIENT_BENEFITS
                DROP TABLE IF EXISTS tmp_rr_patient_benefits;
                CREATE TEMP TABLE tmp_rr_patient_benefits
                    --(RrPatient int, DiagDate TimeStamp , BenefitCode varchar(255), BenefitViews int, Document varchar(16), BeginDate TimeStamp, EndDate TimeStamp, Mkb varchar(8), Lpu varchar(6));
                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, '{}'), ''),
                        'YYYY.MM.DD')                 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, '{}'), ''),
                        'YYYY.MM.DD')                 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, '{}'), ''),
                        'YYYY.MM.DD')                 AS begindate
                         , TO_TIMESTAMP(NULLIF(TRIM(XPATH('/RR_PATIENT_BENEFIT/EndDate[1]/text()', x)::text, '{}'), ''),
                                        'YYYY.MM.DD') 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, '{}'), ''),
                        'YYYY.MM.DD')                 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',
                                              x)) AS x
                          FROM (SELECT "Xml"::xml AS x
                               ) root
                         ) rr_patient_benefit;
                
                ANALYZE tmp_rr_patient_benefits;  

                DELETE
                FROM akuz.t_patient_benefits pb
                    USING akuz.t_benefits b
                WHERE b.benefitsid = pb.benefits
                  AND b.benefitviews IN (19002, 19003, 19005);
                
                INSERT INTO
                    akuz.t_patient_benefits
                ( patientbenefitsid
                , patient
                , benefits
                , benefitdocumentnumber
                , begindate
                , enddate
                , mkb
                , active
                , deleted
                , createtype
                , lpu_inf
                , regdate
                , pac_id)
                SELECT gen_random_uuid()--<PatientBenefitsID, uniqueidentifier,>
                     , p.patientid--<Patient, uniqueidentifier,>
                     , b.benefitsid--<Benefits, uniqueidentifier,>
                     , npb.document--<BenefitDocumentNumber, varchar(255),>
                     , npb.begindate--<BeginDate, datetime,>
                     , npb.enddate--<EndDate, datetime,>
                     , npb.mkb--<Mkb, uniqueidentifier,>
                     , '1'--<Active, bit,>
                     , '0'--<Deleted, bit,>
                     , 2--<CreateType, int,>
                     , npb.lpu--<LPU_INF, varchar(6),>
                     , "dateTime"--<RegDate, datetime,>)
                     , npb.rrpatient
                FROM tmp_rr_patient_benefits npb
                JOIN akuz.t_rr_patient       rp ON rp.pac_id = npb.rrpatient
                JOIN akuz.t_patient          p ON p.patientid = rp.patient
                JOIN vclib.t_enums           ebv ON ebv.code = npb.benefitviews AND ebv.enumtypeid = 190
                JOIN akuz.t_benefits         b ON b.benefitcode = npb.benefitcode AND b.benefitviews = ebv.enumid;
            
            END IF;
        END IF;
    END;
END;
$$;