IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[AKUZ].[sp_AKUZ_FTLOAD_DloAccessPost]') AND type in (N'P', N'PC'))
DROP PROCEDURE [AKUZ].[sp_AKUZ_FTLOAD_DloAccessPost]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [AKUZ].[sp_AKUZ_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'


    IF (@xml IS NOT NULL)
	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)

		DELETE FROM AKUZ.T_DLO_ACCESSED_POSTS

		INSERT INTO AKUZ.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,
			(SELECT TOP 1 pt.PostTypeID FROM AKUZ.T_POST_TYPE pt where pt.Code = p.PostType) as PostType,
			(SELECT TOP 1 s.SpecialityID FROM AKUZ.T_SPECIALITY s where s.Code = p.Speciality) as Speciality,
			(SELECT TOP 1 lm.LpuId FROM AKUZ.T_LPU_MAIN lm where lm.LpuCode = p.LpuMain) as 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
