
DROP PROCEDURE  IF EXISTS AKUZ.sp_AKUZ_FTLOAD_DloAccessPost;

CREATE OR REPLACE PROCEDURE AKUZ.sp_AKUZ_FTLOAD_DloAccessPost(
    "Xml" text DEFAULT NULL,
    "IsNew" integer DEFAULT 1,
    "ID" uuid DEFAULT NULL,
    "QueueName" character varying DEFAULT NULL,
    "dateTime" timestamp DEFAULT NULL,
    "User" uuid DEFAULT NULL
)
AS $$
DECLARE
    v_ContentEncoding varchar(255);
    v_Type varchar(255);

    p_Xml xml := "Xml"::xml;
    p_IsNew integer := "IsNew";
    p_ID uuid := "ID";
    p_QueueName varchar(100) := "QueueName";
    p_dateTime timestamp := "dateTime";
    p_User uuid := "User";
BEGIN
    p_dateTime := COALESCE(p_dateTime, CURRENT_TIMESTAMP(0));
    v_ContentEncoding := 'utf-8';
    v_Type := 'DLO_ACCESSED_POSTS_FTLOAD';

    IF (p_Xml IS NOT NULL) THEN
        CREATE TEMPORARY TABLE temp_DLO_ACCESSED_POSTS AS
        SELECT
            (xpath('/DLO_ACCESSED_POST/ID/text()', x))[1]::text::uuid AS ID,
            (xpath('/DLO_ACCESSED_POST/POST_CODE/text()', x))[1]::text AS PostCode,
            (xpath('/DLO_ACCESSED_POST/LAST_NAME/text()', x))[1]::text AS LastName,
            (xpath('/DLO_ACCESSED_POST/FIRST_NAME/text()', x))[1]::text AS FirstName,
            (xpath('/DLO_ACCESSED_POST/FATHER_NAME/text()', x))[1]::text AS FatherName,
            (xpath('/DLO_ACCESSED_POST/CATEGORY/text()', x))[1]::text::int AS Category,
            to_timestamp((xpath('/DLO_ACCESSED_POST/BEGIN_ACCESS_DATE/text()', x))[1]::text, 'YYYY-MM-DD') AS BeginAccessDate,
            to_timestamp((xpath('/DLO_ACCESSED_POST/END_ACCESS_DATE/text()', x))[1]::text, 'YYYY-MM-DD') AS EndAccessDate,
            to_timestamp((xpath('/DLO_ACCESSED_POST/BEGIN_WORK_DATE/text()', x))[1]::text, 'YYYY-MM-DD') AS BeginWorkDate,
            to_timestamp((xpath('/DLO_ACCESSED_POST/CERTIFICATE_DATE/text()', x))[1]::text, 'YYYY-MM-DD') AS CertificateDate,
            (xpath('/DLO_ACCESSED_POST/MEDICAL_SPECIALITY_CODE/text()', x))[1]::text AS MedicalSpecialityCode,
            to_timestamp((xpath('/DLO_ACCESSED_POST/EXPULSION_DATE/text()', x))[1]::text, 'YYYY-MM-DD') AS ExpulsionDate,
            (xpath('/DLO_ACCESSED_POST/SNILS/text()', x))[1]::text AS Snils,
            (xpath('/DLO_ACCESSED_POST/POST_TYPE/text()', x))[1]::text AS PostType,
            (xpath('/DLO_ACCESSED_POST/SPECIALITY/text()', x))[1]::text AS Speciality,
            (xpath('/DLO_ACCESSED_POST/LPU_MAIN/text()', x))[1]::text AS LpuMain,
            (xpath('/DLO_ACCESSED_POST/EhrRus7PJSAM6GJM/text()', x))[1]::text::int AS EhrRus7pjsam6gjm,
            (xpath('/DLO_ACCESSED_POST/EhrRus7PJSAM6GJMVer/text()', x))[1]::varchar(8) AS EhrRus7pjsam6gjmVer,
            (xpath('/DLO_ACCESSED_POST/EhrRusLS9R0Q4VR4/text()', x))[1]::text::int AS EhrRusLs9r0q4vr4,
            (xpath('/DLO_ACCESSED_POST/EhrRusLS9R0Q4VR4Ver/text()', x))[1]::varchar(4) AS EhrRusLs9r0q4vr4Ver
        FROM
            unnest(xpath('DLO_ACCESSED_POSTS/body/DLO_ACCESSED_POSTS/DLO_ACCESSED_POST', p_Xml)) as x;

        DELETE FROM AKUZ.T_DLO_ACCESSED_POSTS;

        INSERT INTO AKUZ.T_DLO_ACCESSED_POSTS (
            DloAccessedPostsID,
            PostCode,
            LastName,
            FirstName,
            FatherName,
            Category,
            BeginAccessDate,
            EndAccessDate,
            BeginWorkDate,
            CertificateDate,
            MedicalSpecialityCode,
            ExpulsionDate,
            Snils,
            PostType,
            Speciality,
            LpuMain,
            EhrRus7pjsam6gjm,
            EhrRusLs9r0q4vr4
        )
        SELECT
            p.ID,
            PostCode,
            LastName,
            FirstName,
            FatherName,
            Category,
            BeginAccessDate,
            EndAccessDate,
            BeginWorkDate,
            CertificateDate,
            MedicalSpecialityCode,
            ExpulsionDate,
            Snils,
            (SELECT pt.PostTypeID FROM AKUZ.T_POST_TYPE pt WHERE pt.Code = p.PostType LIMIT 1),
            (SELECT s.SpecialityID FROM AKUZ.T_SPECIALITY s WHERE s.Code = p.Speciality LIMIT 1),
            (SELECT lm.LpuId FROM AKUZ.T_LPU_MAIN lm WHERE lm.LpuCode = p.LpuMain LIMIT 1),
            er1.EhrRusId,
            er2.EhrRusId
        FROM temp_DLO_ACCESSED_POSTS p
        LEFT JOIN LATERAL (
            SELECT er.EhrRusID
            FROM EHR.T_EHR_RUS_7PJSAM6GJM er
            JOIN EHR.T_EHR_RUS_REF_BOOKS RRB ON RRB.EhrRusRefBooksID = er.EhrRusRefBooks
            WHERE er.IdEHR = p.EhrRus7pjsam6gjm AND RRB.CURRVERSION = p.EhrRus7pjsam6gjmVer
            LIMIT 1
        ) er1 ON TRUE
        LEFT JOIN LATERAL (
            SELECT er.EhrRusID
            FROM EHR.T_EHR_RUS_LS9R0Q4VR4 er
            JOIN EHR.T_EHR_RUS_REF_BOOKS RRB ON RRB.EhrRusRefBooksID = er.EhrRusRefBooks
            WHERE er.IdEHR = p.EhrRusLs9r0q4vr4 AND RRB.CURRVERSION = p.EhrRusLs9r0q4vr4Ver
            LIMIT 1
        ) er2 ON TRUE;

    END IF;
END;
$$
LANGUAGE plpgsql;
