-- =============================================
-- Author:      Vitacore (с)
-- Create date: 2020-04-01
-- Description: Загрузка регистра БСК.
-- =============================================
DROP PROCEDURE IF EXISTS sp_getbskregister;

CREATE PROCEDURE sp_getbskregister(
    "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('/BSK_REGISTER/header[1]/TYPE[1]/text()', "Xml"::xml))[1]::varchar(36);
            
            
            -- Обработка операции
            IF (_operationtype = 'FTLoad')
            THEN
                -- Generate table Tmp_BSK_REGISTER
                DROP TABLE IF EXISTS tmp_bsk_register;
                CREATE TEMP TABLE tmp_bsk_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/Kod[1]/text()', x))[1]::varchar(32)                            kod
                         , (XPATH('PATIENT/DocSeries[1]/text()', x))[1]::varchar(8)                       docseries
                         , (XPATH('PATIENT/DocNumber[1]/text()', x))[1]::varchar(8)                       docnumber
                         , (XPATH('PATIENT/Sex[1]/text()', x))[1]::text::int                              sex
                         , (XPATH('PATIENT/Snils[1]/text()', x))[1]::varchar(14)                          snils
                         , TO_TIMESTAMP((XPATH('PATIENT/DateBegin[1]/text()', x))[1]::text, 'YYYY.MM.DD') datebegin
                         , TO_TIMESTAMP((XPATH('PATIENT/DateEnd[1]/text()', x))[1]::text, 'YYYY.MM.DD')   dateend
                         , TO_TIMESTAMP((XPATH('PATIENT/LastEditDate[1]/text()', x))[1]::text,
                                        'YYYY.MM.DD')                                                     lasteditdate
                         , (XPATH('PATIENT/Lpu[1]/text()', x))[1]::varchar(6)                             lpu
                         , (XPATH('PATIENT/ExcludeReason[1]/text()', x))[1]::text::int                    excludereason
                         , (XPATH('PATIENT/DocType[1]/text()', x))[1]::text::int                          doctype
                         , (XPATH('PATIENT/PatientId[1]/text()', x))[1]::text::uuid                       patientid
                    FROM (SELECT UNNEST(XPATH('/BSK_REGISTER/body[1]/PATIENT', "Xml"::xml)) AS x
                         ) patient;
                
                ANALYZE tmp_bsk_register;
                
                -- Generate table Tmp_BSK_BENEFITS
                DROP TABLE IF EXISTS tmp_bsk_benefits;
                CREATE TEMP TABLE tmp_bsk_benefits
                AS
                    SELECT (XPATH('BENEFITS/RegisterID[1]/text()', x))[1]::text::int                       registerid
                         , (XPATH('BENEFITS/BenID[1]/text()', x))[1]::text::uuid                           benid
                         , TO_TIMESTAMP((XPATH('BENEFITS/DiagDate[1]/text()', x))[1]::text, 'YYYY.MM.DD')  diagdate
                         , (XPATH('BENEFITS/Document[1]/text()', x))[1]::varchar(16)                       document
                         , TO_TIMESTAMP((XPATH('BENEFITS/DocumentDate[1]/text()', x))[1]::text,
                                        'YYYY.MM.DD')                                                      documentdate
                         , (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
                         , (XPATH('BENEFITS/Lpu[1]/text()', x))[1]::varchar(6)                             lpu
                         , (XPATH('BENEFITS/ServiceCode[1]/text()', x))[1]::varchar(20)                    servicecode
                         , (XPATH('BENEFITS/ServiceName[1]/text()', x))[1]::varchar(255)                   servicename
                         , (XPATH('BENEFITS/ExclusionReason[1]/text()', x))[1]::text::int                  exclusionreason
                         , TO_TIMESTAMP((XPATH('BENEFITS/EstimatedDDate[1]/text()', x))[1]::text,
                                        'YYYY.MM.DD')                                                      estimatedddate
                         , TO_TIMESTAMP((XPATH('BENEFITS/DRegistrationDate[1]/text()', x))[1]::text,
                                        'YYYY.MM.DD')                                                      dregistrationdate
                         , (XPATH('BENEFITS/Place[1]/text()', x))[1]::text::int                            place
                         , (XPATH('BENEFITS/LpuTo[1]/text()', x))[1]::varchar(6)                           lputo
                         , TO_TIMESTAMP((XPATH('BENEFITS/CloseDate[1]/text()', x))[1]::text, 'YYYY.MM.DD') closedate
                    FROM (SELECT UNNEST(XPATH('/BSK_REGISTER/body[1]/PATIENT/BENEFITS', "Xml"::xml)) AS x
                         ) benefits;
                
                ANALYZE tmp_bsk_benefits;
                
                --  	-- Generate table Tmp_BSK_MEDICAMENTS
                --Drop Table If Exists Tmp_BSK_MEDICAMENTS;
                --         Create Temp Table Tmp_BSK_MEDICAMENTS
                --         as
                --         select (xpath('MEDICAMENTS/BenID[1]/text()', x))[1]::text::UUID							    BenID
                --               ,(xpath('MEDICAMENTS/Mnn[1]/text()', x))[1]::text::int									Mnn
                --               ,to_timestamp((xpath('MEDICAMENTS/DateStart[1]/text()', x))[1]::text, 'YYYY.MM.DD') DateStart
                --               ,to_timestamp((xpath('MEDICAMENTS/EndDate[1]/text()', x))[1]::text, 'YYYY.MM.DD')   EndDate
                --               ,(xpath('MEDICAMENTS/MedicationForms[1]/text()', x))[1]::text::UUID				        MedicationForms
                --               ,(xpath('MEDICAMENTS/Regiment[1]/text()', x))[1]::varchar(255)                           Regiment
                --               ,(xpath('MEDICAMENTS/Dose[1]/text()', x))[1]::text::decimal(31,10)                             Dose
                --               ,(xpath('MEDICAMENTS/Prepid[1]/text()', x))[1]::text::int								Prepid
                --               ,(xpath('MEDICAMENTS/DayliCoeff[1]/text()', x))[1]::text::decimal(31,10)                       DayliCoeff
                --               ,(xpath('MEDICAMENTS/MeasureUnit[1]/text()', x))[1]::varchar(255)						MeasureUnit
                --               ,(xpath('MEDICAMENTS/MeasureUnitType[1]/text()', x))[1]::text::int						MeasureUnitType
                --               ,(xpath('MEDICAMENTS/SingleDose[1]/text()', x))[1]::text::decimal(31,10)                       SingleDose
                --               ,(xpath('MEDICAMENTS/Multiplicity[1]/text()', x))[1]::text::int							Multiplicity
                --               ,(xpath('MEDICAMENTS/DoseView[1]/text()', x))[1]::varchar(255)							DoseView
                --               ,(xpath('MEDICAMENTS/DoseView[1]/text()', x))[1]::text::int								DoseViewType
                --         from
                -- 			(
                --	    select unnest(xpath('/BSK_REGISTER/body[1]/PATIENT/BENEFITS/MEDICAMENTS', "Xml"::xml)) as x
                -- 			) MEDICAMENTS;
                
                -- Связываем регистр с пациентами
                -- Поиск по СНИЛС + ДР
                UPDATE tmp_bsk_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_bsk_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_bsk_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 = 19007;
                
                -- Загружаем новые
                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,>
                     , COALESCE(pb.closedate, 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_bsk_benefits pb
                JOIN tmp_bsk_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;
$$;