CREATE OR REPLACE PROCEDURE RDS.sp_RDS_DloAccessPostDirection(
    Xml xml DEFAULT NULL,
    IsNew int DEFAULT 1,
    ID uuid default NULL,
    QueueName character varying DEFAULT NULL,
    dateTime timestamp without time zone DEFAULT NULL,
    "user" uuid DEFAULT null
)
AS $$
DECLARE
    v_Xml int;
    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;
    v_TRUE varchar(4) := 'true';
    v_FALSE varchar(5) := 'false';
    v_PostId uuid;
    v_PostCode varchar(255);
    v_Snils varchar(14);
    v_Speciality varchar(3);
    v_PostType varchar(4);
    v_LastName varchar(40);
    v_FirstName varchar(40);
    v_BeginWorkDate timestamp without time zone;
    v_NeedConfirmation varchar(6);
    v_OpenDirectionCount integer;

    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 v_Xml is not null THEN
        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/LPU_MAIN/text()', x))[1]::varchar(6) as LpuMain,
            (xpath('/DLO_ACCESSED_POSTS_DIRECTION/SNILS/text()', x))[1]::varchar(14) as Snils,
            (xpath('/DLO_ACCESSED_POSTS_DIRECTION/POST_CODE/text()', x))[1]::varchar(255) as PostCode,
            (xpath('/DLO_ACCESSED_POSTS_DIRECTION/DOC_CODE/text()', x))[1]::varchar(6) as DocCode,
            (xpath('/DLO_ACCESSED_POSTS_DIRECTION/LAST_NAME/text()', x))[1]::varchar(40) as LastName,
            (xpath('/DLO_ACCESSED_POSTS_DIRECTION/FIRST_NAME/text()', x))[1]::varchar(40) as FirstName,
            (xpath('/DLO_ACCESSED_POSTS_DIRECTION/FATHER_NAME/text()', x))[1]::varchar(40) as FatherName,
            (xpath('/DLO_ACCESSED_POSTS_DIRECTION/SPECIALITY/text()', x))[1]::varchar(3) as Speciality,
            (xpath('/DLO_ACCESSED_POSTS_DIRECTION/POST_TYPE/text()', x))[1]::varchar(4) as PostType,
            (xpath('/DLO_ACCESSED_POSTS_DIRECTION/CATEGORY/text()', x))[1]::varchar(4) as Category,
            to_timestamp((xpath('/DLO_ACCESSED_POSTS_DIRECTION/BEGIN_ACCESS_DATE/text()', x))[1]::text, 'DD.MM.YYYY') as BeginAccessDate,
            to_timestamp((xpath('/DLO_ACCESSED_POSTS_DIRECTION/END_ACCESS_DATE/text()', x))[1]::text, 'DD.MM.YYYY') as EndAccessDate,
            to_timestamp((xpath('/DLO_ACCESSED_POSTS_DIRECTION/BEGIN_ACCESS_DATE/text()', x))[1]::text, 'DD.MM.YYYY') as BeginWorkDate,
            to_timestamp((xpath('/DLO_ACCESSED_POSTS_DIRECTION/CERTIFICATE_DATE/text()', x))[1]::text, 'DD.MM.YYYY') as CertificateDate,
            (xpath('/DLO_ACCESSED_POSTS_DIRECTION/MEDICAL_SPECIALITY_CODE/text()', x))[1]::varchar(10) as MedicalSpecialityCode,
            to_timestamp((xpath('/DLO_ACCESSED_POSTS_DIRECTION/EXPULSION_DATE/text()', x))[1]::text, 'DD.MM.YYYY') as ExpulsionDate,
            (xpath('/DLO_ACCESSED_POSTS_DIRECTION/EhrRus7PJSAM6GJM/text()', x))[1]::varchar(10)::int as EhrRus7pjsam6gjmIdEhr,
            (xpath('/DLO_ACCESSED_POSTS_DIRECTION/EhrRusLS9R0Q4VR4/text()', x))[1]::varchar(10)::int as EhrRusLs9r0q4vr4IdEhr
        FROM (
                SELECT p_Xml as x
             ) t;

        SELECT
            p.ParameterValue
        INTO
            v_NeedConfirmation
        FROM VCLib.T_HOLDER_PARAMETER p
        JOIN VCLib.T_HOLDER h on p.Holder = h.HolderID
        WHERE h.Name = 'RegistryUpdateHolder' and p.ParameterName = 'NeedСonfirmation';

        IF v_NeedConfirmation NOT IN (v_TRUE, v_False) THEN
            RAISE EXCEPTION 'Значение параметра v_NeedConfirmation не установлено';
        END IF;

        SELECT
            d.Snils,
            d.Speciality,
            d.PostType,
            d.LastName,
            d.FirstName,
            d.BeginWorkDate,
            d.PostCode,
            d.DirectionType
        INTO
            v_Snils,
            v_Speciality,
            v_PostType,
            v_LastName,
            v_FirstName,
            v_BeginWorkDate,
            v_PostCode,
            v_OperationType
        FROM temp_DLO_ACCESSED_POSTS_DIRECTION d;

        IF v_Snils IS NULL THEN
            RAISE EXCEPTION 'Не установлен снилс SNILS';
        END IF;

        IF v_PostCode IS NULL THEN
            RAISE EXCEPTION 'Не установлен код врача POST_CODE';
        END IF;

        IF v_NeedConfirmation = v_True THEN
            SELECT
                COUNT(*)::integer
            INTO v_OpenDirectionCount
            FROM RDS.T_DLO_ACCESSED_POSTS_DIRECTION d
            JOIN temp_DLO_ACCESSED_POSTS_DIRECTION DLO on d.PostCode = d.PostCode and d.Snils = dlo.Snils and d.DirectionState = 0
            JOIN RDS.T_LPU_MAIN lpu on lpu.MCOD = DLO.LpuMain;

            IF v_OpenDirectionCount > 0 THEN
                RAISE EXCEPTION 'Для данного врача уже существует открытое направление';
            END IF;
        ELSE
            INSERT INTO RDS.T_DLO_ACCESSED_POSTS_DIRECTION (
                DloAccessedPostsDirectionID,
                DirectionType,
                DirectionState,
                LpuMain,
                Snils,
                PostCode,
                DocCode,
                LastName,
                FirstName,
                FatherName,
                Speciality,
                PostType,
                Category,
                BeginAccessDate,
                EndAccessDate,
                BeginWorkDate,
                MedicalSpecialityCode,
                ExpulsionDate,
                CreateDateTime,
                EhrRus7pjsam6gjmIdEhr,
                EhrRusLs9r0q4vr4IdEhr
            )
            SELECT
                dlo.Id,
                dlo.DirectionType,
                1::integer, -- принято
                dlo.LpuMain,
                dlo.Snils,
                dlo.PostCode,
                dlo.DocCode,
                dlo.LastName,
                dlo.FirstName,
                dlo.FatherName,
                dlo.Speciality,
                dlo.PostType,
                dlo.Category,
                dlo.BeginAccessDate,
                dlo.EndAccessDate,
                dlo.BeginWorkDate,
                dlo.MedicalSpecialityCode,
                dlo.ExpulsionDate,
                p_DateTime,
                dlo.EhrRus7pjsam6gjmIdEhr,
                dlo.EhrRusLs9r0q4vr4IdEhr
            FROM temp_DLO_ACCESSED_POSTS_DIRECTION dlo;
        END IF;

        IF v_OperationType = v_INSERT THEN
            v_PostId := gen_random_uuid();
            p_ID := v_PostId;

            INSERT INTO RDS.T_DLO_ACCESSED_POSTS (
                DloAccessedPostsID,
                BeginAccessDate,
                BeginWorkDate,
                Category,
                CertificateDate,
                DocCode,
                EndAccessDate,
                FatherName,
                FirstName,
                LastName,
                LpuMain,
                MedicalSpecialityCode,
                PostCode,
                PostType,
                Snils,
                Speciality,
                EhrRus7pjsam6gjm,
                EhrRusLs9r0q4vr4,
                EhrRus7pjsam6gjmIdEhr,
                EhrRusLs9r0q4vr4IdEhr
            )
            SELECT
                v_PostId,
                dir.BeginAccessDate,
                dir.BeginWorkDate,
                dir.Category,
                p_dateTime,
                dir.DocCode,
                dir.EndAccessDate,
                dir.FatherName,
                dir.FirstName,
                dir.LastName,
                dir.LpuMain,
                dir.MedicalSpecialityCode,
                dir.PostCode,
                dir.PostType,
                dir.Snils,
                dir.Speciality,
                (select e.EhrRusId from ehr.t_ehr_rus_7pjsam6gjm e where e.idehr = dir.EhrRus7pjsam6gjmIdEhr limit 1),
                (select e.EhrRusId from ehr.t_ehr_rus_ls9r0q4vr4 e where e.idehr = dir.EhrRusLs9r0q4vr4IdEhr limit 1),
                dir.EhrRus7pjsam6gjmIdEhr,
                dir.EhrRusLs9r0q4vr4IdEhr
            FROM RDS.T_DLO_ACCESSED_POSTS_DIRECTION dir
            JOIN temp_DLO_ACCESSED_POSTS_DIRECTION dlo on dlo.Id = dir.DloAccessedPostsDirectionID;

        ELSE

            SELECT
                p.DloAccessedPostsID,
                dir.Id
            INTO
                v_PostId,
                p_ID
            FROM RDS.T_DLO_ACCESSED_POSTS p
            JOIN temp_DLO_ACCESSED_POSTS_DIRECTION dir ON p.PostCode = dir.PostCode
            WHERE p.ExpulsionDate is null;

            UPDATE p
            SET ExpulsionDate = dir.ExpulsionDate
            FROM RDS.T_DLO_ACCESSED_POSTS p
            JOIN RDS.T_DLO_ACCESSED_POSTS_DIRECTION dir ON dir.DloAccessedPostsDirectionID = p_ID
                and p.DloAccessedPostsID = v_PostId;

        END IF;

        SELECT
            XMLELEMENT
            (
                NAME "DLO_ACCESSED_POSTS",
                XMLFOREST
                (
                    p.DloAccessedPostsID AS "ID",
                    p.PostCode AS "POST_CODE",
                    p.LastName AS "LAST_NAME",
                    p.FirstName AS "FIRST_NAME",
                    p.FatherName AS "FATHER_NAME",
                    p.Category AS "CATEGORY",
                    to_char(p.BeginAccessDate, 'DD.MM.YYYY') AS "BEGIN_ACCESS_DATE",
                    to_char(p.EndAccessDate, 'DD.MM.YYYY') AS "END_ACCESS_DATE",
                    to_char(p.BeginWorkDate, 'DD.MM.YYYY') AS "BEGIN_WORK_DATE",
                    to_char(p.CertificateDate, 'DD.MM.YYYY') AS "CERTIFICATE_DATE",
                    p.MedicalSpecialityCode AS "MEDICAL_SPECIALITY_CODE",
                    to_char(p.ExpulsionDate, 'DD.MM.YYYY') AS "EXPULSION_DATE",
                    p.Snils AS "SNILS",
                    p.PostType AS "POST_TYPE",
                    p.Speciality AS "SPECIALITY",
                    p.LpuMain AS "LPU_MAIN",
                    p.EhrRus7pjsam6gjmIdEhr as "EhrRus7PJSAM6GJM",
                    p.EhrRusLs9r0q4vr4IdEhr as "EhrRusLS9R0Q4VR4"
                )
            )::text
        INTO v_Result
        FROM RDS.T_DLO_ACCESSED_POSTS p
        WHERE p.DloAccessedPostsID = v_PostId;

        INSERT INTO RDS.T_RABBIT_SEND_OBJECT
        (
            RabbitSendObjectID,
            QueueName,
            Content,
            MessageID,
            Headers,
            ContentEncoding,
            Type,
            UserID,
            State,
            CreateDateTime,
            OwnerID
        )
        SELECT
            gen_random_uuid(),
            s.ServerName,
            ConvertTextToBinaryZip(v_Result::text, ''::text),
            null,
            null,
            v_ContentEncoding,
            'DLO_ACCESSED_POSTS',
            p_User,
            0::int,
            p_dateTime,
            p_ID
        FROM RDS.T_SYNC_SERVERS s
        WHERE s.ServerType = 1::integer;

        SELECT
            XMLELEMENT
            (
                    NAME "DLO_ACCESSED_POSTS",
                    XMLFOREST
                    (
                        dap.DloAccessedPostsDirectionID AS "DLO_ACCESSED_POSTS_DIRECTION_ID",
                        dap.DirectionType AS "DIRECTION_TYPE",
                        dap.DirectionState AS "DIRECTION_STATE",
                        v_PostID AS "DLO_ACCESSED_POSTS_ID",
                        dap.ExpulsionDate AS "EXPULSION_DATE",
                        dap.RefusingReason AS "REFUSING_REASON"

                    )
            )::text
        INTO v_Result
        FROM RDS.T_DLO_ACCESSED_POSTS_DIRECTION dap
        JOIN temp_DLO_ACCESSED_POSTS_DIRECTION dlo on dap.DloAccessedPostsDirectionID = dlo.Id;

        INSERT INTO RDS.T_RABBIT_SEND_OBJECT
        (
            RabbitSendObjectID,
            QueueName,
            Content,
            MessageID,
            Headers,
            ContentEncoding,
            Type,
            UserID,
            State,
            CreateDateTime,
            OwnerID
        )
        SELECT
            gen_random_uuid(),
            s.ServerName,
            ConvertTextToBinaryZip(v_Result::text, ''::text),
            null,
            null,
            v_ContentEncoding,
            v_Type,
            p_User,
            0::int,
            p_dateTime,
            p_ID
        FROM RDS.T_SYNC_SERVERS s
        WHERE s.ServerType = 0::integer;

    ELSE

        SELECT
            dir.DirectionType,
            dir.DirectionState
        INTO
            v_OperationType,
            v_DirectionState
        FROM RDS.T_DLO_ACCESSED_POSTS_DIRECTION dir
        WHERE dir.DloAccessedPostsDirectionID = p_ID;

        IF v_DirectionState = v_DS_Accept THEN
            IF v_OperationType = v_INSERT THEN
                v_PostId = gen_random_uuid();

                INSERT INTO RDS.T_DLO_ACCESSED_POSTS (
                    DloAccessedPostsID,
                    BeginAccessDate,
                    BeginWorkDate,
                    Category,
                    CertificateDate,
                    DocCode,
                    EndAccessDate,
                    FatherName,
                    FirstName,
                    LastName,
                    LpuMain,
                    MedicalSpecialityCode,
                    PostCode,
                    PostType,
                    Snils,
                    Speciality,
                    EhrRus7pjsam6gjm,
                    EhrRusLs9r0q4vr4,
                    EhrRus7pjsam6gjmIdEhr,
                    EhrRusLs9r0q4vr4IdEhr
                )
                SELECT
                    v_PostId,
                    dir.BeginAccessDate,
                    dir.BeginWorkDate,
                    dir.Category,
                    p_dateTime,
                    dir.DocCode,
                    dir.EndAccessDate,
                    dir.FatherName,
                    dir.FirstName,
                    dir.LastName,
                    dir.LpuMain,
                    dir.MedicalSpecialityCode,
                    dir.PostCode,
                    dir.PostType,
                    dir.Snils,
                    dir.Speciality,
                    (select e.EhrRusId from ehr.t_ehr_rus_7pjsam6gjm e where e.idehr = dir.EhrRus7pjsam6gjmIdEhr limit 1),
                    (select e.EhrRusId from ehr.t_ehr_rus_ls9r0q4vr4 e where e.idehr = dir.EhrRusLs9r0q4vr4IdEhr limit 1),
                    dir.EhrRus7pjsam6gjmIdEhr,
                    dir.EhrRusLs9r0q4vr4IdEhr

                FROM RDS.T_DLO_ACCESSED_POSTS_DIRECTION dir
                WHERE dir.DloAccessedPostsDirectionID = p_ID;
            ELSE

                SELECT
                    p.DloAccessedPostsID
                INTO
                    v_PostId
                FROM RDS.T_DLO_ACCESSED_POSTS p
				JOIN RDS.T_DLO_ACCESSED_POSTS_DIRECTION dir ON p.PostCode = dir.PostCode
                    and p.ExpulsionDate is null
                    and dir.DloAccessedPostsDirectionID = p_ID;

                SELECT
                    XMLELEMENT(
                        NAME "DLO_ACCESSED_POSTS",
                        XMLFOREST
                        (
                            p.DloAccessedPostsID AS "ID",
                            p.PostCode AS "POST_CODE",
                            p.LastName AS "LAST_NAME",
                            p.FirstName AS "FIRST_NAME",
                            p.FatherName AS "FATHER_NAME",
                            p.Category AS "CATEGORY",
                            to_char(p.BeginAccessDate, 'DD.MM.YYYY') AS "BEGIN_ACCESS_DATE",
                            to_char(p.EndAccessDate, 'DD.MM.YYYY') AS "END_ACCESS_DATE",
                            to_char(p.BeginWorkDate, 'DD.MM.YYYY') AS "BEGIN_WORK_DATE",
                            to_char(p.CertificateDate, 'DD.MM.YYYY') AS "CERTIFICATE_DATE",
                            p.MedicalSpecialityCode AS "MEDICAL_SPECIALITY_CODE",
                            to_char(p.ExpulsionDate, 'DD.MM.YYYY') AS "EXPULSION_DATE",
                            p.Snils AS "SNILS",
                            p.PostType AS "POST_TYPE",
                            p.Speciality AS "SPECIALITY",
                            p.LpuMain AS "LPU_MAIN",
                            p.EhrRus7pjsam6gjmIdEhr as "EhrRus7PJSAM6GJM",
                            p.EhrRusLs9r0q4vr4IdEhr as "EhrRusLS9R0Q4VR4"
                        )
                    )::text
                INTO v_Result
                FROM RDS.T_DLO_ACCESSED_POSTS p
                WHERE p.DloAccessedPostsID = v_PostId;

                INSERT INTO RDS.T_RABBIT_SEND_OBJECT
                (
                    RabbitSendObjectID,
                    QueueName,
                    Content,
                    MessageID,
                    Headers,
                    ContentEncoding,
                    Type,
                    UserID,
                    State,
                    CreateDateTime,
                    OwnerID
                )
                SELECT
                    gen_random_uuid(),
                    s.ServerName,
                    ConvertTextToBinaryZip(v_Result, ''),
                    NULL,
                    NULL,
                    v_ContentEncoding,
                    'DLO_ACCESSED_POSTS',
                    p_User,
                    0,
                    p_dateTime,
                    p_ID
                FROM RDS.T_SYNC_SERVERS s
                WHERE s.ServerType = 1;

                SELECT
                    XMLELEMENT(
                        NAME "DLO_ACCESSED_POSTS_DIRECTION",
                        XMLFOREST
                        (
                            dap.DloAccessedPostsDirectionID AS "DLO_ACCESSED_POSTS_DIRECTION_ID",
                            dap.DirectionType AS "DIRECTION_TYPE",
                            dap.DirectionState AS "DIRECTION_STATE",
                            v_PostId AS "DLO_ACCESSED_POSTS_ID",
                            to_char(dap.ExpulsionDate, 'DD.MM.YYYY') AS "EXPULSION_DATE",
                            dap.RefusingReason AS "REFUSING_REASON"
                        )
                    )::text
                INTO v_Result
                FROM RDS.T_DLO_ACCESSED_POSTS_DIRECTION dap
                WHERE dap.DloAccessedPostsDirectionID = p_ID;

                INSERT INTO RDS.T_RABBIT_SEND_OBJECT
                (
                    RabbitSendObjectID,
                    QueueName,
                    Content,
                    MessageID,
                    Headers,
                    ContentEncoding,
                    Type,
                    UserID,
                    State,
                    CreateDateTime,
                    OwnerID
                )
                SELECT
                    gen_random_uuid(),
                    s.ServerName,
                    ConvertTextToBinaryZip(v_Result, ''),
                    NULL,
                    NULL,
                    v_ContentEncoding,
                    v_Type,
                    p_User,
                    0,
                    p_dateTime,
                    p_ID
                FROM RDS.T_SYNC_SERVERS s
                WHERE s.ServerType = 0;

            END IF;
        END IF;
    END IF;
END ;
$$
LANGUAGE plpgsql;