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'


	IF (@Xml is null)
	BEGIN
		SELECT @Result = (
			SELECT @FTLOAD as 'header/TYPE'
			FOR XML PATH(''),
			ROOT('DLO_ACCESSED_POSTS_FTLOAD')
		)

		INSERT INTO [APS].[T_RABBIT_SEND_OBJECT]
        (
			[RabbitSendObjectID],
			[QueueName],
			[Content],
			[MessageID],
			[Headers],
			[ContentEncoding],
			[Type],
			[UserID],
			[State],
			[CreateDateTime],
			[OwnerID]
		)
        SELECT NewID(),
			@QueueName,
            dbo.ConvertTextToBinaryZip(@Result, ''),
            null,
            null,
            @ContentEncoding,
            @Type,
            @User,
            0,
            @dateTime,
            @OwnerID

	END
	ELSE
	BEGIN
		
        SELECT 
			T.c.value('./ID[1]', 'uniqueidentifier') as [ID],
            T.c.value('./POST_CODE[1]', 'varchar(255)') as [PostCode],
            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,
			PostCode,
			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
END
