IF EXISTS (SELECT *
           FROM sys.objects
           WHERE
               object_id = OBJECT_ID(N'[APS].[sp_APS_FTLOAD_DloAccessPost]') AND type IN (N'P', N'PC'))
    DROP PROCEDURE APS.sp_APS_FTLOAD_DloAccessPost
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE APS.sp_APS_FTLOAD_DloAccessPost @Xml xml = NULL,
                                                 @IsNew BIT = 1,
                                                 @ID UNIQUEIDENTIFIER = NULL,
                                                 @QueueName VARCHAR(100) = NULL,
                                                 @dateTime DATETIME = NULL,
                                                 @User UNIQUEIDENTIFIER = NULL
AS
BEGIN
    DECLARE @Result VARCHAR(MAX),
        @ContentEncoding VARCHAR(255),
        @Type VARCHAR(255),
        @OperationType VARCHAR(6),
        @OwnerID UNIQUEIDENTIFIER,
        @FTLOAD VARCHAR(6)


    SELECT
        @dateTime = ISNULL(@dateTime, GETDATE()),
        @ContentEncoding = 'utf-8',
        @Type = 'DLO_ACCESSED_POSTS_FTLOAD',
        @FTLOAD = 'FTLoad'


    SELECT
        T.c.value('./ID[1]', 'uniqueidentifier')                                   AS ID,
        T.c.value('./POST_CODE[1]', 'varchar(255)')                                AS PostCode,
        T.c.value('./DOC_CODE[1]', 'varchar(255)')                                 AS DocCode,
        T.c.value('./LAST_NAME[1]', 'varchar(40)')                                 AS LastName,
        T.c.value('./FIRST_NAME[1]', 'varchar(40)')                                AS FirstName,
        T.c.value('./FATHER_NAME[1]', 'varchar(40)')                               AS FatherName,
        T.c.value('./CATEGORY[1]', 'int')                                          AS Category,
        CONVERT(DATETIME, T.c.value('./BEGIN_ACCESS_DATE[1]', 'varchar(10)'), 120) AS BeginAccessDate,
        CONVERT(DATETIME, T.c.value('./END_ACCESS_DATE[1]', 'varchar(10)'), 120)   AS EndAccessDate,
        CONVERT(DATETIME, T.c.value('./BEGIN_WORK_DATE[1]', 'varchar(10)'), 120)   AS BeginWorkDate,
        CONVERT(DATETIME, T.c.value('./CERTIFICATE_DATE[1]', 'varchar(10)'), 120)  AS CertificateDate,
        T.c.value('./MEDICAL_SPECIALITY_CODE[1]', 'varchar(16)')                   AS MedicalSpecialityCode,
        CONVERT(DATETIME, T.c.value('./EXPULSION_DATE[1]', 'varchar(10)'), 120)    AS ExpulsionDate,
        T.c.value('./SNILS[1]', 'varchar(14)')                                     AS Snils,
        T.c.value('./POST_TYPE[1]', 'varchar(4)')                                  AS PostType,
        T.c.value('./SPECIALITY[1]', 'varchar(3)')                                 AS Speciality,
        T.c.value('./LPU_MAIN[1]', 'varchar(6)')                                   AS LpuMain,
        T.c.value('./EhrRus7PJSAM6GJM[1]', 'int')                                  AS EhrRus7pjsam6gjm,
        T.c.value('./EhrRus7PJSAM6GJMVer[1]', 'varchar(8)')                        AS EhrRus7pjsam6gjmVer,
        T.c.value('./EhrRusLS9R0Q4VR4[1]', 'int')                                  AS EhrRusLs9r0q4vr4,
        T.c.value('./EhrRusLS9R0Q4VR4Ver[1]', 'varchar(8)')                        AS EhrRusLs9r0q4vr4Ver
    INTO
        #DLO_ACCESSED_POSTS
    FROM @Xml.nodes('DLO_ACCESSED_POSTS/body/DLO_ACCESSED_POSTS/DLO_ACCESSED_POST') T(c)

    IF (EXISTS (SELECT
                    1
                FROM #DLO_ACCESSED_POSTS dir
                OUTER APPLY (SELECT TOP 1
                                 ehr.IdEhr,
                                 rrb.CurrVersion
                             FROM EHR.T_EHR_RUS_7PJSAM6GJM ehr
                             JOIN EHR.T_EHR_RUS_REF_BOOKS rrb ON rrb.EhrRusRefBooksID = ehr.EhrRusRefBooks
                             WHERE ehr.IdEhr = dir.EhrRus7pjsam6gjm AND rrb.CurrVersion = dir.EhrRus7pjsam6gjmVer) e
                WHERE
                    dir.EhrRus7pjsam6gjm IS NOT NULL AND dir.EhrRus7pjsam6gjmVer IS NOT NULL
                                                     AND e.IdEhr IS NULL AND e.CurrVersion IS NULL))
        BEGIN
            RAISERROR ('Установлены некорректные значения EhrRus7pjsam6gjm и EhrRus7pjsam6gjmVer ', 16, 10)
        END

    IF (EXISTS (SELECT
                    1
                FROM #DLO_ACCESSED_POSTS dir
                OUTER APPLY (SELECT TOP 1
                                 ehr.IdEhr,
                                 rrb.CurrVersion
                             FROM EHR.T_EHR_RUS_LS9R0Q4VR4 ehr
                             JOIN EHR.T_EHR_RUS_REF_BOOKS rrb ON rrb.EhrRusRefBooksID = ehr.EhrRusRefBooks
                             WHERE ehr.IdEhr = dir.EhrRusLs9r0q4vr4 AND rrb.CurrVersion = dir.EhrRusLs9r0q4vr4Ver) e
                WHERE
                    dir.EhrRusLs9r0q4vr4 IS NOT NULL AND dir.EhrRusLs9r0q4vr4Ver IS NOT NULL
                                                     AND e.IdEhr IS NULL AND e.CurrVersion IS NULL))
        BEGIN
            RAISERROR ('Установлены некорректные значения EhrRusLS9R0Q4VR4 и EhrRusLS9R0Q4VR4Ver ', 16, 10)
        END

    DELETE FROM APS.T_DLO_ACCESSED_POSTS

    INSERT INTO
        APS.T_DLO_ACCESSED_POSTS
    (DloAccessedPostsID,
     PostCode,
     LastName,
     FirstName,
     FatherName,
     Category,
     BeginAccessDate,
     EndAccessDate,
     BeginWorkDate,
     CertificateDate,
     MedicalSpecialityCode,
     ExpulsionDate,
     Snils,
     PostType,
     Speciality,
     LpuMain,
     EhrRus7pjsam6gjm,
     EhrRusLs9r0q4vr4)
    SELECT
        ID,
        DocCode,
        LastName,
        FirstName,
        FatherName,
        Category,
        BeginAccessDate,
        EndAccessDate,
        BeginWorkDate,
        CertificateDate,
        MedicalSpecialityCode,
        ExpulsionDate,
        Snils,
        PostType,
        Speciality,
        LpuMain,
        er1.EhrRusID,
        er2.EhrRusID
    FROM #DLO_ACCESSED_POSTS p
    OUTER APPLY (SELECT TOP 1
                     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) er1
    OUTER APPLY (SELECT TOP 1
                     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) er2
END
