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

CREATE OR REPLACE PROCEDURE aps.sp_getbskregister(
    "Xml" text DEFAULT NULL::text,
    "IsNew" integer DEFAULT 1,
    "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(36);
    BEGIN
        "dateTime" := COALESCE("dateTime", CURRENT_TIMESTAMP::TIMESTAMP);
        IF ("Xml" IS NOT NULL)
        THEN
            
            -- Получаем тип операции
            _operationtype := (XPATH('/BSK_REGISTER/header[1]/TYPE[1]/text()', "Xml"::xml))[1]::varchar(36);
            
            -- 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()', t.x))[1]::text::int           AS registerid
                     , (XPATH('/PATIENT/LastName[1]/text()', t.x))[1]::varchar(40)           AS lastname
                     , (XPATH('/PATIENT/FirstName[1]/text()', t.x))[1]::varchar(40)          AS firstname
                     , (XPATH('/PATIENT/FatherName[1]/text()', t.x))[1]::varchar(40)         AS fathername
                     , TO_TIMESTAMP((XPATH('/PATIENT/Birthday[1]/text()', t.x))[1]::text,
                                    'YYYY.MM.DD')                                            AS birthday
                     , (XPATH('/PATIENT/Kod[1]/text()', t.x))[1]::varchar(32)                AS kod
                     , (XPATH('/PATIENT/DocSeries[1]/text()', t.x))[1]::varchar(8)           AS docseries
                     , (XPATH('/PATIENT/DocNumber[1]/text()', t.x))[1]::varchar(8)           AS docnumber
                     , (XPATH('/PATIENT/Sex[1]/text()', t.x))[1]::text::int                  AS sex
                     , (XPATH('/PATIENT/Snils[1]/text()', t.x))[1]::varchar(14)              AS snils
                     , TO_TIMESTAMP((XPATH('/PATIENT/DateBegin[1]/text()', t.x))[1]::text,
                                    'YYYY.MM.DD')                                            AS datebegin
                     , TO_TIMESTAMP((XPATH('/PATIENT/DateEnd[1]/text()', t.x))[1]::text,
                                    'YYYY.MM.DD')                                            AS dateend
                     , TO_TIMESTAMP((XPATH('/PATIENT/LastEditDate[1]/text()', t.x))[1]::text,
                                    'YYYY.MM.DD')                                            AS lasteditdate
                     , (XPATH('/PATIENT/Lpu[1]/text()', t.x))[1]::varchar(6)                 AS lpu
                     , (XPATH('/PATIENT/ExcludeReason[1]/text()', t.x))[1]::text::int        AS excludereason
                     , (XPATH('/PATIENT/DocType[1]/text()', t.x))[1]::text::int              AS doctype
                     , (XPATH('/PATIENT/PatientId[1]/text()', t.x))[1]::text::uuid           AS patientid
                     , (XPATH('/PATIENT/BirthSurname[1]/text()', t.x))[1]::varchar(40)       AS birthsurname
                     , TO_TIMESTAMP((XPATH('/PATIENT/DocIssueDate[1]/text()', t.x))[1]::text,
                                    'YYYY.MM.DD')                                            AS docissuedate
                     , (XPATH('/PATIENT/Enp[1]/text()', t.x))[1]::varchar(16)                AS enp
                     , (XPATH('/PATIENT/DocIssueOrg[1]/text()', t.x))[1]::varchar(256)       AS docissueorg
                     , (XPATH('/PATIENT/RealFiasAddress[1]/text()', t.x))[1]::varchar(36)    AS realfiasaddress
                     , (XPATH('/PATIENT/RealFiasHome[1]/text()', t.x))[1]::varchar(36)       AS realfiashome
                     , (XPATH('/PATIENT/RealCity[1]/text()', t.x))[1]::varchar(100)          AS realcity
                     , (XPATH('/PATIENT/RealCityPrefix[1]/text()', t.x))[1]::varchar(10)     AS realcityprefix
                     , (XPATH('/PATIENT/RealStreet[1]/text()', t.x))[1]::varchar(100)        AS realstreet
                     , (XPATH('/PATIENT/RealStreetPrefix[1]/text()', t.x))[1]::varchar(10)   AS realstreetprefix
                     , (XPATH('/PATIENT/RealHouse[1]/text()', t.x))[1]::varchar(100)         AS realhouse
                     , (XPATH('/PATIENT/RealBuild[1]/text()', t.x))[1]::varchar(100)         AS realbuild
                     , (XPATH('/PATIENT/RealStruc[1]/text()', t.x))[1]::varchar(100)         AS realstruc
                     , (XPATH('/PATIENT/RealRoom[1]/text()', t.x))[1]::varchar(100)          AS realroom
                     , TO_TIMESTAMP((XPATH('/PATIENT/RealLiveStartDate[1]/text()', t.x))[1]::text,
                                    'YYYY.MM.DD')                                            AS reallivestartdate
                     , TO_TIMESTAMP((XPATH('/PATIENT/RealLiveEndDate[1]/text()', t.x))[1]::text,
                                    'YYYY.MM.DD')                                            AS realliveenddate
                     , (XPATH('/PATIENT/LegalFiasAddress[1]/text()', t.x))[1]::varchar(100)  AS legalfiasaddress
                     , (XPATH('/PATIENT/LegalFiasHome[1]/text()', t.x))[1]::varchar(100)     AS legalfiashome
                     , (XPATH('/PATIENT/LegalCity[1]/text()', t.x))[1]::varchar(100)         AS legalcity
                     , (XPATH('/PATIENT/LegalCityPrefix[1]/text()', t.x))[1]::varchar(100)   AS legalcityprefix
                     , (XPATH('/PATIENT/LegalStreet[1]/text()', t.x))[1]::varchar(100)       AS legalstreet
                     , (XPATH('/PATIENT/LegalStreetPrefix[1]/text()', t.x))[1]::varchar(100) AS legalstreetprefix
                     , (XPATH('/PATIENT/LegalHouse[1]/text()', t.x))[1]::varchar(100)        AS legalhouse
                     , (XPATH('/PATIENT/LegalBuild[1]/text()', t.x))[1]::varchar(100)        AS legalbuild
                     , (XPATH('/PATIENT/LegalStruc[1]/text()', t.x))[1]::varchar(100)        AS legalstruc
                     , (XPATH('/PATIENT/LegalRoom[1]/text()', t.x))[1]::varchar(100)         AS legalroom
                     , TO_TIMESTAMP((XPATH('/PATIENT/LegalLiveStartDate[1]/text()', t.x))[1]::text,
                                    'YYYY.MM.DD')                                            AS legallivestartdate
                     , TO_TIMESTAMP((XPATH('/PATIENT/LegalLiveEndDate[1]/text()', t.x))[1]::text,
                                    'YYYY.MM.DD')                                            AS legalliveenddate
                     , (XPATH('/PATIENT/CitizenShip[1]/text()', t.x))[1]::text::varchar(52)  AS citizenship
                     , eh1.ehrrusid::text::varchar(52)                                       AS ehrrus5o5e88vhc6
                     , (XPATH('PATIENT/EhrRus5o5e88vhc6Ver[1]/text()', t.x))[1]::varchar(52) AS ehrrus5o5e88vhc6ver
                FROM (SELECT UNNEST(XPATH('BSK_REGISTER/body[1]/PATIENT', "Xml"::xml)) AS x
                     )          t
                LEFT JOIN LATERAL (
                              SELECT n1.ehrrusid
                              FROM ehr.t_ehr_rus_5o5e88vhc6     n1
                              LEFT JOIN ehr.t_ehr_rus_ref_books rrb ON rrb.ehrrusrefbooksid = n1.ehrrusrefbooks
                              WHERE n1.codeehr = (XPATH('PATIENT/EhrRus5o5e88vhc6[1]/text()', x))[1]::varchar(255)
                                AND
                                  rrb.currversion = (XPATH('PATIENT/EhrRus5o5e88vhc6Ver[1]/text()', x))[1]::varchar(255)
                              LIMIT 1
                              ) eh1 ON TRUE;
            
            
            -- 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()', t.x))[1]::text::int      AS registerid
                     , (XPATH('/BENEFITS/BenID[1]/text()', t.x))[1]::text::uuid          AS benid
                     , TO_TIMESTAMP((XPATH('/BENEFITS/DiagDate[1]/text()', t.x))[1]::text,
                                    'YYYY.MM.DD')                                        AS diagdate
                     , (XPATH('/BENEFITS/Document[1]/text()', t.x))[1]::varchar(16)      AS document
                     , TO_TIMESTAMP((XPATH('/BENEFITS/DocumentDate[1]/text()', t.x))[1]::text,
                                    'YYYY.MM.DD')                                        AS documentdate
                     , (XPATH('/BENEFITS/Mkb[1]/text()', t.x))[1]::varchar(8)            AS mkb
                     , (XPATH('/BENEFITS/BenefitCode[1]/text()', t.x))[1]::varchar(255)  AS benefitcode
                     , (XPATH('/BENEFITS/BenefitViews[1]/text()', t.x))[1]::text::int    AS benefitviews
                     , TO_TIMESTAMP((XPATH('/BENEFITS/BeginDate[1]/text()', t.x))[1]::text,
                                    'YYYY.MM.DD')                                        AS begindate
                     , TO_TIMESTAMP((XPATH('/BENEFITS/EndDate[1]/text()', t.x))[1]::text,
                                    'YYYY.MM.DD')                                        AS enddate
                     , (XPATH('/BENEFITS/Lpu[1]/text()', t.x))[1]::varchar(6)            AS lpu
                     , (XPATH('/BENEFITS/ServiceCode[1]/text()', t.x))[1]::varchar(20)   AS servicecode
                     , (XPATH('/BENEFITS/ServiceName[1]/text()', t.x))[1]::varchar(255)  AS servicename
                     , (XPATH('/BENEFITS/ExclusionReason[1]/text()', t.x))[1]::text::int AS exclusionreason
                     , TO_TIMESTAMP((XPATH('/BENEFITS/EstimatedDDate[1]/text()', t.x))[1]::text,
                                    'YYYY.MM.DD')                                        AS estimatedddate
                     , TO_TIMESTAMP((XPATH('/BENEFITS/DRegistrationDate[1]/text()', t.x))[1]::text,
                                    'YYYY.MM.DD')                                        AS dregistrationdate
                     , (XPATH('/BENEFITS/Place[1]/text()', t.x))[1]::text::int           AS place
                     , (XPATH('/BENEFITS/LpuTo[1]/text()', t.x))[1]::varchar(6)          AS lputo
                     , TO_TIMESTAMP((XPATH('/BENEFITS/CloseDate[1]/text()', t.x))[1]::text,
                                    'YYYY.MM.DD')                                        AS closedate
                FROM (SELECT UNNEST(XPATH('BSK_REGISTER/body[1]/PATIENT/BENEFITS', "Xml"::xml)) AS x
                     ) t;
            
            -- 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()', t.x))[1]::text::uuid                         AS benid
                     , (XPATH('/MEDICAMENTS/Mnn[1]/text()', t.x))[1]::text::int                            AS mnn
                     , TO_TIMESTAMP((XPATH('/MEDICAMENTS/DateStart[1]/text()', t.x))[1]::text,
                                    'YYYY.MM.DD')                                                          AS datestart
                     , TO_TIMESTAMP((XPATH('/MEDICAMENTS/EndDate[1]/text()', t.x))[1]::text, 'YYYY.MM.DD') AS enddate
                     , (XPATH('/MEDICAMENTS/MedicationForms[1]/text()', t.x))[1]::text::uuid               AS medicationforms
                     , (XPATH('/MEDICAMENTS/Regiment[1]/text()', t.x))[1]::varchar(255)                    AS regiment
                     , (XPATH('/MEDICAMENTS/Dose[1]/text()', t.x))[1]::text::decimal(31, 10)               AS dose
                     , (XPATH('/MEDICAMENTS/Prepid[1]/text()', t.x))[1]::text::int                         AS prepid
                     , (XPATH('/MEDICAMENTS/DayliCoeff[1]/text()', t.x))[1]::text::decimal(31, 10)         AS daylicoeff
                     , (XPATH('/MEDICAMENTS/MeasureUnit[1]/text()', t.x))[1]::varchar(255)                 AS measureunit
                     , (XPATH('/MEDICAMENTS/MeasureUnitType[1]/text()', t.x))[1]::text::int                AS measureunittype
                     , (XPATH('/MEDICAMENTS/SingleDose[1]/text()', t.x))[1]::text::decimal(31, 10)         AS singledose
                     , (XPATH('/MEDICAMENTS/Multiplicity[1]/text()', t.x))[1]::text::int                   AS multiplicity
                     , (XPATH('/MEDICAMENTS/DoseView[1]/text()', t.x))[1]::varchar(255)                    AS doseview
                     , (XPATH('/MEDICAMENTS/DoseView[1]/text()', t.x))[1]::text::int                       AS doseviewtype
                FROM (SELECT UNNEST(XPATH('BSK_REGISTER/body[0]/PATIENT/BENEFITS/MEDICAMENTS', "Xml"::xml)) AS x
                     ) t;
            
            -- Обработка операции
            IF _operationtype = 'FTLoad' OR (SELECT COUNT(*) FROM tmp_bsk_register) > 1
            THEN
                DELETE FROM aps.t_bsk_register_medicaments;
                DELETE FROM aps.t_bsk_register_data;
                DELETE FROM aps.t_bsk_register;
            ELSE
                DELETE
                FROM aps.t_bsk_register_medicaments meds
                    USING aps.t_bsk_register_data ben, aps.t_bsk_register rr, tmp_bsk_register r
                WHERE ben.bskregisterdataid = meds.bskregisterdata
                  AND rr.bskregisterid = ben.bskregister
                  AND r.registerid = rr.pac_id;
                
                DELETE
                FROM aps.t_bsk_register_data ben
                    USING aps.t_bsk_register rr, tmp_bsk_register r
                WHERE rr.bskregisterid = ben.bskregister
                  AND r.registerid = rr.pac_id;
                
                DELETE
                FROM aps.t_bsk_register rr
                    USING tmp_bsk_register r
                WHERE r.registerid = rr.pac_id;
            END IF;
            
            INSERT INTO
                aps.t_bsk_register
            ( bskregisterid
            , excludereason
            , lasteditdate
            , lastname
            , firstname
            , fathername
            , birthday
            , kod
            , docseries
            , docnumber
            , pac_id
            , sex
            , snils
            , doctype
            , lpumain
            , datebegin
            , dateend
            , docissuedate
            , docissueorg
            , enp
            , realfiasaddress
            , realfiashome
            , realcity
            , realcityprefix
            , realstreet
            , realstreetprefix
            , realhouse
            , realbuild
            , realstruc
            , realroom
            , reallivestartdate
            , realliveenddate
            , legalfiasaddress
            , legalfiashome
            , legalcity
            , legalcityprefix
            , legalstreet
            , legalstreetprefix
            , legalhouse
            , legalbuild
            , legalstruc
            , legalroom
            , legallivestartdate
            , legalliveenddate
            , citizenship
            , ehrrus5o5e88vhc6)
            SELECT gen_random_uuid()
                 , excludereason
                 , lasteditdate
                 , lastname
                 , firstname
                 , fathername
                 , birthday
                 , kod
                 , docseries
                 , docnumber
                 , registerid
                 , esex.enumid
                 , snils
                 , e.enumid
                 , lpu
                 , datebegin
                 , dateend
                 , docissuedate
                 , docissueorg
                 , enp
                 , realfiasaddress
                 , realfiashome
                 , realcity
                 , realcityprefix
                 , realstreet
                 , realstreetprefix
                 , realhouse
                 , realbuild
                 , realstruc
                 , realroom
                 , reallivestartdate
                 , realliveenddate
                 , legalfiasaddress
                 , legalfiashome
                 , legalcity
                 , legalcityprefix
                 , legalstreet
                 , legalstreetprefix
                 , legalhouse
                 , legalbuild
                 , legalstruc
                 , legalroom
                 , legallivestartdate
                 , legalliveenddate
                 , cs.ehrrusid
                 , doc.ehrrusid
            FROM tmp_bsk_register          r
            LEFT JOIN vclib.t_enums        e ON e.enumtypeid = 105 AND e.code = r.doctype
            LEFT JOIN vclib.t_enums        esex ON esex.enumtypeid = 103 AND esex.code = r.sex
            LEFT JOIN ehr.t_ehr_rus_o00015 cs ON cs.code = r.citizenship
            LEFT JOIN LATERAL (
                          SELECT doc.ehrrusid
                          FROM ehr.t_ehr_rus_5o5e88vhc6     doc
                          LEFT JOIN ehr.t_ehr_rus_ref_books rrb ON rrb.ehrrusrefbooksid = doc.ehrrusrefbooks
                          WHERE doc.codeehr = r.ehrrus5o5e88vhc6
                            AND rrb.currversion = r.ehrrus5o5e88vhc6ver
                          LIMIT 1
                          )                doc ON TRUE;
            
            INSERT INTO
                aps.t_bsk_register_data
            ( bskregisterdataid
            , bskregister
            , servicecode
            , servicename
            , diagdate
            , document
            , documentdate
            , mkb
            , benefits
            , begindate
            , enddate
            , exclusionreason
            , estimatedddate
            , dregistrationdate
            , place
            , lpumainto
            , closedate
            , lpumain
            , lasteditdate)
            SELECT b.benid
                 , r.bskregisterid
                 , servicecode
                 , servicename
                 , diagdate
                 , document
                 , documentdate
                 , mkb
                 , bb.benefitsid
                 , begindate
                 , enddate
                 , b.exclusionreason
                 , b.estimatedddate
                 , b.dregistrationdate
                 , b.place
                 , b.lputo
                 , b.closedate
                 , b.lpu
                 , CURRENT_DATE
            FROM tmp_bsk_benefits        b
            JOIN      aps.t_bsk_register r ON r.pac_id = b.registerid
            LEFT JOIN vclib.t_enums      e ON e.enumtypeid = 190 AND e.code = b.benefitviews
            LEFT JOIN aps.t_benefits     bb ON bb.benefitcode = b.benefitcode AND bb.benefitviews = e.enumid;
            
            
            INSERT INTO
                aps.t_bsk_register_medicaments
            ( bskregistermedicamentsid
            , bskregisterdata
            , cmnn
            , datestart
            , enddate
            , medicationforms
            , regiment
            , dose
            , measureunit
            , prepid
            , daylicoeff
            , singledose
            , multiplicity
            , doseview)
            SELECT gen_random_uuid()
                 , meds.benid
                 , (SELECT cmnnid
                    FROM pharm.t_c_mnn
                    WHERE otherid = meds.mnn
                    LIMIT 1
                   )
                 , meds.datestart
                 , meds.enddate
                 , meds.medicationforms
                 , meds.regiment
                 , meds.dose
                 , (SELECT dw.code
                    FROM pharm.t_dose_view dw
                    WHERE dw.name = meds.measureunit
                      AND dw.viewtype = meds.measureunittype
                    LIMIT 1
                   )
                 , meds.prepid
                 , meds.daylicoeff
                 , meds.singledose
                 , meds.multiplicity
                 , (SELECT dw.code
                    FROM pharm.t_dose_view dw
                    WHERE dw.name = meds.doseview
                      AND dw.viewtype = meds.doseviewtype
                    LIMIT 1
                   )
            FROM tmp_bsk_medicaments meds;
        END IF;
    END;
END;
$$;