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

CREATE PROCEDURE sp_getpalliativeregister(
    "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('/PALLIATIVE_REGISTER/header[1]/TYPE[1]/text()', "Xml"::xml))[1]::varchar(36);
            
            -- Обработка операции
            IF (_operationtype = 'FTLoad')
            THEN
                -- Generate table Tmp_PALLIATIVE_REGISTER
                DROP TABLE IF EXISTS tmp_palliative_register;
                CREATE TEMP TABLE tmp_palliative_register
                AS
                    SELECT (XPATH('PATIENT/RegisterID[1]/text()', x))[1]::text::int                      AS registerid
                         , (XPATH('PATIENT/LastName[1]/text()', x))[1]::varchar(40)                      AS lastname
                         , (XPATH('PATIENT/FirstName[1]/text()', x))[1]::varchar(40)                     AS firstname
                         , (XPATH('PATIENT/FatherName[1]/text()', x))[1]::varchar(40)                    AS fathername
                         , TO_TIMESTAMP((XPATH('PATIENT/Birthday[1]/text()', x))[1]::text, 'YYYY.MM.DD') AS birthday
                         , (XPATH('PATIENT/Kod[1]/text()', x))[1]::varchar(32)                           AS kod
                         , (XPATH('PATIENT/DocSeries[1]/text()', x))[1]::varchar(8)                      AS docseries
                         , (XPATH('PATIENT/DocNumber[1]/text()', x))[1]::varchar(8)                      AS docnumber
                         , (XPATH('PATIENT/Sex[1]/text()', x))[1]::text::int                             AS sex
                         , (XPATH('PATIENT/Snils[1]/text()', x))[1]::varchar(14)                         AS snils
                         , TO_TIMESTAMP((XPATH('PATIENT/DateBegin[1]/text()', x))[1]::text,
                                        'YYYY.MM.DD')                                                    AS datebegin
                         , TO_TIMESTAMP((XPATH('PATIENT/DateEnd[1]/text()', x))[1]::text, 'YYYY.MM.DD')  AS dateend
                         , TO_TIMESTAMP((XPATH('PATIENT/LastEditDate[1]/text()', x))[1]::text,
                                        'YYYY.MM.DD')                                                    AS lasteditdate
                         , (XPATH('PATIENT/Lpu[1]/text()', x))[1]::varchar(6)                            AS lpu
                         , (XPATH('PATIENT/ExcludeReason[1]/text()', x))[1]::text::int                   AS excludereason
                         , (XPATH('PATIENT/DocType[1]/text()', x))[1]::text::int                         AS doctype
                         , (XPATH('PATIENT/PatientId[1]/text()', x))[1]::text::uuid                      AS patientid
                    FROM (SELECT UNNEST(XPATH('PALLIATIVE_REGISTER/body[1]/PATIENT', x)) AS x
                          FROM (SELECT "Xml"::xml AS x
                               ) root
                         ) patient;
                
                ANALYZE tmp_palliative_register;
                
                -- Generate table Tmp_PALLIATIVE_BENEFITS
                DROP TABLE IF EXISTS tmp_palliative_benefits;
                CREATE TEMP TABLE tmp_palliative_benefits
                AS
                    SELECT (XPATH('BENEFITS/RegisterID[1]/text()', x))[1]::text::int                       AS registerid
                         , TO_TIMESTAMP((XPATH('BENEFITS/DiagDate[1]/text()', x))[1]::text, 'YYYY.MM.DD')  AS diagdate
                         , (XPATH('BENEFITS/Document[1]/text()', x))[1]::varchar(16)                       AS document
                         , TO_TIMESTAMP((XPATH('./DocumentDate[1]/text()', x))[1]::text,
                                        'YYYY.MM.DD')                                                      AS documentdate
                         , (XPATH('BENEFITS/Mkb[1]/text()', x))[1]::varchar(8)                             AS mkb
                         , (XPATH('BENEFITS/BenefitCode[1]/text()', x))[1]::varchar(255)                   AS benefitcode
                         , (XPATH('BENEFITS/BenefitViews[1]/text()', x))[1]::text::int                     AS benefitviews
                         , TO_TIMESTAMP((XPATH('BENEFITS/BeginDate[1]/text()', x))[1]::text, 'YYYY.MM.DD') AS begindate
                         , TO_TIMESTAMP((XPATH('BENEFITS/EndDate[1]/text()', x))[1]::text, 'YYYY.MM.DD')   AS enddate
                         , (XPATH('BENEFITS/Lpu[1]/text()', x))[1]::varchar(6)                             AS lpu
                    FROM (SELECT UNNEST(XPATH('PALLIATIVE_REGISTER/body[1]/PATIENT/BENEFITS', x)) AS x
                          FROM (SELECT "Xml"::xml AS x
                               ) root
                         ) benefits;
                
                ANALYZE tmp_palliative_benefits;
                
                -- Связываем регистр с пациентами
                -- Поиск по СНИЛС + ДР
                UPDATE tmp_palliative_register pr
                SET patientid = p.patientid
                FROM akuz.t_patient p
                WHERE p.snils = pr.snils
                  AND p.birthday = pr.birthday
                  AND pr.snils IS NOT NULL
                  AND pr.birthday IS NOT NULL;
                
                -- Поиск по серии и номеру документа
                UPDATE tmp_palliative_register pr
                SET patientid = p.patientid
                FROM akuz.t_patient p
                WHERE LEFT(p.docseries, 8) = pr.docseries
                  AND LEFT(p.docnumber, 8) = pr.docnumber
                  AND pr.patientid IS NULL
                  AND pr.docseries IS NOT NULL
                  AND pr.docnumber IS NOT NULL;
                
                -- Поиск по ФИО + ДР
                UPDATE tmp_palliative_register pr
                SET patientid = p.patientid
                FROM akuz.t_patient p
                WHERE p.lastname = pr.lastname
                  AND p.firstname = pr.firstname
                  AND (p.fathername = pr.fathername OR p.fathername IS NULL AND pr.fathername IS NULL)
                  AND p.birthday = pr.birthday
                  AND pr.patientid IS NULL;
                
                DELETE
                FROM akuz.t_patient_benefits pb
                    USING akuz.t_benefits b
                WHERE b.benefitsid = pb.benefits
                  AND b.benefitviews = 19008;
                
                -- Загружаем новые
                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,>
                     , pb.document--<BenefitDocumentNumber, varchar(255),>
                     , pb.begindate--<BeginDate, datetime,>
                     , pb.enddate--<EndDate, datetime,>
                     , (SELECT m.mkbid FROM akuz.t_mkb m WHERE m.mcbcode = pb.mkb LIMIT 1)--<Mkb, uniqueidentifier,>
                     , '1'--<Active, bit,>
                     , '0'--<Deleted, bit,>
                     , 2--<CreateType, int,>
                     , pb.lpu--<LPU_INF, varchar(6),>
                     , "dateTime"--<RegDate, datetime,>)
                     , pb.registerid
                FROM tmp_palliative_benefits pb
                JOIN tmp_palliative_register pr ON pr.registerid = pb.registerid
                JOIN akuz.t_patient          p ON p.patientid = pr.patientid
                JOIN vclib.t_enums           ebv ON ebv.code = pb.benefitviews AND ebv.enumtypeid = 190
                JOIN akuz.t_benefits         b ON b.benefitcode = pb.benefitcode AND b.benefitviews = ebv.enumid;
            END IF;
        END IF;
    END;
END;
$$;