-- =============================================
-- Author:      Vitacore (с)
-- Create date: 2022-03-16
-- Description: Загрузка молочного регистра.
-- =============================================
DROP PROCEDURE IF EXISTS sp_getmilkregister;

CREATE PROCEDURE sp_getmilkregister(
    "Xml" text DEFAULT NULL
, "IsNew" integer = 1
, "ID" uuid DEFAULT NULL::uuid
, "QueueName" character varying DEFAULT NULL::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
        IF ("Xml" IS NOT NULL)
        THEN
            -- Получаем тип операции
            SELECT INTO
                _operationtype (XPATH('/MILK_REGISTER/header[1]/TYPE[1]/text()', "Xml"::xml))[1]::varchar(36);
            
            
            -- Обработка операции
            IF (_operationtype = 'FTLoad')
            THEN
                -- Generate table TMP_MILK_REGISTER
                DROP TABLE IF EXISTS tmp_milk_register;
                CREATE TEMP TABLE tmp_milk_register
                AS
                    SELECT (XPATH('PATIENT/RegisterID[1]/text()', x))[1]::text::int                      registerid
                         , (XPATH('PATIENT/LastName[1]/text()', x))[1]::varchar(40)                      lastname
                         , (XPATH('PATIENT/FirstName[1]/text()', x))[1]::varchar(40)                     firstname
                         , (XPATH('PATIENT/FatherName[1]/text()', x))[1]::varchar(40)                    fathername
                         , TO_TIMESTAMP((XPATH('PATIENT/Birthday[1]/text()', x))[1]::text, 'YYYY.MM.DD') birthday
                         , (XPATH('PATIENT/DocSeries[1]/text()', x))[1]::varchar(16)                     docseries
                         , (XPATH('PATIENT/DocNumber[1]/text()', x))[1]::varchar(16)                     docnumber
                         , (XPATH('PATIENT/Sex[1]/text()', x))[1]::text::int                             sex
                         , (XPATH('PATIENT/Snils[1]/text()', x))[1]::varchar(14)                         snils
                         , (XPATH('PATIENT/DocType[1]/text()', x))[1]::text::int                         doctype
                         , (XPATH('PATIENT/Polis[1]/text()', x))[1]::varchar(255)                        polis
                         , (XPATH('PATIENT/OKATO[1]/text()', x))[1]::varchar(255)                        okato
                         , (XPATH('PATIENT/City[1]/text()', x))[1]::varchar(255)                         city
                         , (XPATH('PATIENT/LHouse[1]/text()', x))[1]::varchar(255)                       lhouse
                         , (XPATH('PATIENT/Block[1]/text()', x))[1]::varchar(255)                        block
                         , (XPATH('PATIENT/Flat[1]/text()', x))[1]::varchar(255)                         flat
                         , (XPATH('PATIENT/LFlat[1]/text()', x))[1]::varchar(255)                        lflat
                         , (XPATH('PATIENT/Room[1]/text()', x))[1]::varchar(255)                         room
                         , (XPATH('PATIENT/House[1]/text()', x))[1]::varchar(255)                        house
                         , (XPATH('PATIENT/Street[1]/text()', x))[1]::varchar(255)                       street
                         , (XPATH('PATIENT/ExcludeReason[1]/text()', x))[1]::text::int                   excludereason
                         , (XPATH('PATIENT/PatientId[1]/text()', x))[1]::text::uuid                      patientid
                    FROM (SELECT UNNEST(XPATH('MILK_REGISTER/body[1]/PATIENT', x)) AS x
                          FROM (SELECT "Xml"::xml AS x
                               ) root
                         ) patient;
                
                ANALYZE tmp_milk_register;  

                -- Generate table TMP_MILK_BENEFITS
                DROP TABLE IF EXISTS tmp_milk_benefits;
                CREATE TEMP TABLE tmp_milk_benefits
                AS
                    SELECT (XPATH('BENEFITS/RegisterID[1]/text()', x))[1]::text::int                       registerid
                         , TO_TIMESTAMP((XPATH('BENEFITS/DiagDate[1]/text()', x))[1]::text, 'YYYY.MM.DD')  diagdate
                         , (XPATH('BENEFITS/Mkb[1]/text()', x))[1]::varchar(8)                             mkb
                         , (XPATH('BENEFITS/BenefitCode[1]/text()', x))[1]::varchar(255)                   benefitcode
                         , (XPATH('BENEFITS/BenefitViews[1]/text()', x))[1]::text::int                     benefitviews
                         , TO_TIMESTAMP((XPATH('BENEFITS/BeginDate[1]/text()', x))[1]::text, 'YYYY.MM.DD') begindate
                         , TO_TIMESTAMP((XPATH('BENEFITS/EndDate[1]/text()', x))[1]::text, 'YYYY.MM.DD')   enddate
                    FROM (SELECT UNNEST(XPATH('MILK_REGISTER/body[1]/PATIENT/BENEFITS', x)) AS x
                          FROM (SELECT "Xml"::xml AS x
                               ) root
                         ) benefits;

                ANALYZE tmp_milk_benefits;  
                
                -- Связываем регистр с пациентами
                -- Поиск по СНИЛС + ДР
                UPDATE tmp_milk_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_milk_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 p.birthday = pr.birthday
                  AND pr.patientid IS NULL
                  AND pr.docseries IS NOT NULL
                  AND pr.docnumber IS NOT NULL;
                
                -- Поиск по ФИО + ДР
                UPDATE tmp_milk_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 = 19099;
                
                -- Загружаем новые
                INSERT INTO
                    akuz.t_patient_benefits
                ( patientbenefitsid
                , patient
                , benefits
                , begindate
                , enddate
                , mkb
                , active
                , deleted
                , createtype
                , regdate
                , pac_id)
                SELECT gen_random_uuid()--<PatientBenefitsID, uniqueidentifier,>
                     , p.patientid--<Patient, uniqueidentifier,>
                     , b.benefitsid--<Benefits, uniqueidentifier,>
                     , pb.begindate--<BeginDate, datetime,>
                     , pb.enddate--<EndDate, datetime,>
                     , pb.mkb--<Mkb, uniqueidentifier,>
                     , '1'--<Active, bit,>
                     , '0'--<Deleted, bit,>
                     , 2--<CreateType, int,>
                     , "dateTime"--<RegDate, datetime,>)
                     , pb.registerid
                FROM tmp_milk_benefits pb
                JOIN tmp_milk_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;
$$;