CREATE OR REPLACE PROCEDURE AKUZ.sp_AKUZ_DloAccessPostDirection(
    Xml xml DEFAULT NULL::xml,
    IsNew integer DEFAULT 1,
    ID uuid DEFAULT NULL::UUID,
    QueueName character varying DEFAULT NULL::character varying,
    dateTime timestamp DEFAULT NULL,
    "user" uuid DEFAULT NULL::uuid
)
AS $$
DECLARE
    v_Result text;
    v_ContentEncoding varchar(255) := 'utf-8';
    v_Type varchar(255) := 'DLO_ACCESSED_POSTS_DIRECTION';
    v_OperationType integer;
    v_INSERT integer := 0;
    v_DirectionState integer;
    v_DS_Accept integer := 1;

    p_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));

    IF (p_Xml IS NULL) THEN
        SELECT
            XMLELEMENT(
                    NAME "DLO_ACCESSED_POSTS_DIRECTION",
                    XMLFOREST
                    (
                            dfidap.DloAccessedPostsDirectionID AS "DLO_ACCESSED_POSTS_DIRECTION_ID",
                            dfidap.DirectionType AS "DIRECTION_TYPE",
                            dfidap.DirectionState AS "DIRECTION_STATE",
                            lm.LpuCode AS "LPU_MAIN",
                            dfidap.Snils AS "SNILS",
                            dfidap.PostCode AS "POST_CODE",
                            p.Code AS "DOC_CODE",
                            dfidap.LastName AS "LAST_NAME",
                            dfidap.FirstName AS "FIRST_NAME",
                            dfidap.FatherName AS "FATHER_NAME",
                            s.Code AS "SPECIALITY",
                            pt.Code AS "POST_TYPE",
                            dfidap.Category AS "CATEGORY",
                            to_char(dfidap.BeginAccessDate, 'DD.MM.YYYY') AS "BEGIN_ACCESS_DATE",
                            to_char(dfidap.EndAccessDate, 'DD.MM.YYYY') AS "END_ACCESS_DATE",
                            to_char(dfidap.BeginWorkDate, 'DD.MM.YYYY') AS "BEGIN_WORK_DATE",
                            to_char(dfidap.CertificateDate, 'DD.MM.YYYY') AS "CERTIFICATE_DATE",
				            e1.IdEhr as	"EhrRus7PJSAM6GJM",
                            rrb1.CurrVersion as "EhrRus7PJSAM6GJMVer",
				            e2.IdEhr as	"EhrRusLS9R0Q4VR4",
                            rrb2.CurrVersion as "EhrRusLS9R0Q4VR4Ver"
                    )
            )::text INTO v_Result
        FROM AKUZ.T_DLO_ACCESSED_POSTS_DIRECTION dfidap
                 JOIN AKUZ.T_LPU_MAIN lm ON dfidap.LpuMain = lm.LpuId
                 JOIN AKUZ.T_SPECIALITY s ON dfidap.Speciality = s.SpecialityId
                 JOIN AKUZ.T_POST_TYPE pt ON dfidap.PostType = pt.PostTypeID
                 JOIN AKUZ.T_POST p ON p.ResourceID = dfidap.Post
                 LEFT JOIN EHR.T_EHR_RUS_7PJSAM6GJM e1 on dfidap.EhrRus7pjsam6gjm = e1.EhrRusId
                 LEFT JOIN EHR.T_EHR_RUS_REF_BOOKS RRB1 ON RRB1.EhrRusRefBooksID = e1.EhrRusRefBooks
                 LEFT JOIN EHR.T_EHR_RUS_LS9R0Q4VR4 e2 on dfidap.EhrRusLs9r0q4vr4 = e2.EhrRusId
                 LEFT JOIN EHR.T_EHR_RUS_REF_BOOKS RRB2 ON RRB2.EhrRusRefBooksID = e2.EhrRusRefBooks
        WHERE
            dfidap.DloAccessedPostsDirectionID = p_ID;

        INSERT INTO AKUZ.T_RABBIT_SEND_OBJECT (
            RabbitSendObjectID,
            QueueName,
            Content,
            MessageID,
            Headers,
            ContentEncoding,
            Type,
            UserID,
            State,
            CreateDateTime,
            OwnerID
        )
        VALUES
        (
            gen_random_uuid(),
            p_QueueName,
            ConvertTextToBinaryZip(v_Result, ''),
            NULL,
            NULL,
            v_ContentEncoding,
            v_Type,
            NULL,
            0,
            p_dateTime,
            p_ID
        );

    ELSE
        CREATE TEMPORARY TABLE temp_DLO_ACCESSED_POSTS_DIRECTION AS
        SELECT
            (xpath('/DLO_ACCESSED_POSTS_DIRECTION/DLO_ACCESSED_POSTS_DIRECTION_ID/text()', x))[1]::text::uuid as Id,
            (xpath('/DLO_ACCESSED_POSTS_DIRECTION/DIRECTION_TYPE/text()', x))[1]::text::integer as DirectionType,
            (xpath('/DLO_ACCESSED_POSTS_DIRECTION/DIRECTION_STATE/text()', x))[1]::text::integer as DirectionState,
            (xpath('/DLO_ACCESSED_POSTS_DIRECTION/POST_CODE/text()', x))[1]::text::varchar(32) as PostCode,
            to_timestamp((xpath('/DLO_ACCESSED_POSTS_DIRECTION/body/EXPULSION_DATE/text()', x))[1]::text, 'DD.MM.YYYY') as ExpulsionDate,
            (xpath('/DLO_ACCESSED_POSTS_DIRECTION/REFUSING_REASON/text()', x))[1]::text as RefusingReason
        FROM (
                 SELECT p_Xml as x
             ) t;

        UPDATE AKUZ.T_DLO_ACCESSED_POSTS_DIRECTION dap
        SET
            DirectionState = ndap.DirectionState,
            RefusingReason = ndap.RefusingReason
        FROM temp_DLO_ACCESSED_POSTS_DIRECTION ndap
        WHERE ndap.Id = dap.DloAccessedPostsDirectionID;

        SELECT
            DirectionState,
            DirectionType
        INTO
            v_DirectionState,
            v_OperationType
        FROM temp_DLO_ACCESSED_POSTS_DIRECTION
        LIMIT 1;

        IF (v_DirectionState = v_DS_Accept) THEN
            IF (v_OperationType = v_INSERT) THEN
                INSERT INTO AKUZ.T_DLO_ACCESSED_POSTS (
                    DloAccessedPostsID,
                    LpuMain,
                    PostCode,
                    LastName,
                    FirstName,
                    FatherName,
                    Category,
                    BeginAccessDate,
                    EndAccessDate,
                    BeginWorkDate,
                    CertificateDate,
                    MedicalSpecialityCode,
                    ExpulsionDate,
                    Snils,
                    PostType,
                    Speciality,
                    UpdateInfoDate,
                    EhrRus7pjsam6gjm,
                    EhrRusLs9r0q4vr4
                )
                SELECT
                    gen_random_uuid(),
                    dir.LpuMain,
                    dir.PostCode,
                    dir.LastName,
                    dir.FirstName,
                    dir.FatherName,
                    dir.Category,
                    dir.BeginAccessDate,
                    dir.EndAccessDate,
                    dir.BeginWorkDate,
                    dir.CertificateDate,
                    dir.MedicalSpecialityCode,
                    dir.ExpulsionDate,
                    dir.Snils,
                    dir.PostType,
                    dir.Speciality,
                    p_dateTime,
                    dir.EhrRus7pjsam6gjm,
                    dir.EhrRusLs9r0q4vr4
                FROM temp_DLO_ACCESSED_POSTS_DIRECTION dlo
                JOIN AKUZ.T_DLO_ACCESSED_POSTS_DIRECTION dir ON dlo.Id = dir.DloAccessedPostsDirectionID;

            ELSE
                UPDATE AKUZ.T_DLO_ACCESSED_POSTS post
                SET ExpulsionDate = dir.ExpulsionDate
                FROM temp_DLO_ACCESSED_POSTS_DIRECTION dir
                WHERE dir.PostCode = post.PostCode
                    AND post.ExpulsionDate IS NULL;
            END IF;
        END IF;

        DROP TABLE temp_DLO_ACCESSED_POSTS_DIRECTION;
    END IF;
END;
$$
LANGUAGE plpgsql;
