CREATE OR REPLACE PROCEDURE AKUZ.sp_AKUZ_FTLOAD_DloAccessPostDirection(
    p_Xml xml DEFAULT NULL::xml,
    p_IsNew boolean DEFAULT '1',
    p_ID uuid DEFAULT NULL::UUID,
    p_QueueName character varying DEFAULT NULL::character varying,
    p_dateTime timestamp without time zone DEFAULT NULL::timestamp without time zone,
    p_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;
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",
                            dfidap.MedicalSpecialityCode AS "MEDICAL_SPECIALITY_CODE",
                            to_char(dfidap.ExpulsionDate, 'DD.MM.YYYY') AS "EXPULSION_DATE"

                    )
            )::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
        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/DLO_ACCESSED_POSTS_ID/text()', x))[1]::text::uuid as PostId,
            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
                )
                SELECT
                    dlo.PostId,
                    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
                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.PostId = post.DloAccessedPostsID;
            END IF;
        END IF;

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