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

CREATE PROCEDURE [RDS].[sp_RDS_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),
	    @OwnerID uniqueidentifier,
	    @FTLOAD varchar(6),
		@OperationType varchar(10)


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

    SELECT @Result =
    (
        SELECT
            p.DloAccessedPostsID as 'body/DLO_ACCESSED_POST/ID',
            p.PostCode as 'body/DLO_ACCESSED_POST/POST_CODE',
            p.LastName as 'body/DLO_ACCESSED_POST/LAST_NAME',
            p.FirstName AS 'body/DLO_ACCESSED_POST/FIRST_NAME',
            p.FatherName AS 'body/DLO_ACCESSED_POST/FATHER_NAME',
            p.Category AS 'body/DLO_ACCESSED_POST/CATEGORY',
            convert(varchar(10), p.BeginAccessDate, 120) as 'body/DLO_ACCESSED_POST/BEGIN_ACCESS_DATE',
            convert(varchar(10), p.EndAccessDate, 120) as 'body/DLO_ACCESSED_POST/END_ACCESS_DATE',
            convert(varchar(10), p.BeginWorkDate, 120) as 'body/DLO_ACCESSED_POST/BEGIN_WORK_DATE',
            convert(varchar(10), p.CertificateDate, 120) as 'body/DLO_ACCESSED_POST/CERTIFICATE_DATE',
            p.MedicalSpecialityCode AS 'body/DLO_ACCESSED_POST/MEDICAL_SPECIALITY_CODE',
            convert(varchar(10), p.ExpulsionDate, 120) as 'body/DLO_ACCESSED_POST/EXPULSION_DATE',
            p.Snils as 'body/DLO_ACCESSED_POST/SNILS',
            p.PostType as 'body/DLO_ACCESSED_POST/POST_TYPE',
            p.Speciality as 'body/DLO_ACCESSED_POST/SPECIALITY',
            p.LpuMain as 'body/DLO_ACCESSED_POST/LPU_MAIN',
            e1.IdEhr as 'body/DLO_ACCESSED_POST/EhrRus7PJSAM6GJM',
            rrb1.CurrVersion as 'body/DLO_ACCESSED_POST/EhrRus7PJSAM6GJMVer',
            e2.IdEhr as 'body/DLO_ACCESSED_POST/EhrRusLS9R0Q4VR4',
            rrb2.CurrVersion as 'body/DLO_ACCESSED_POST/EhrRusLS9R0Q4VR4Ver'
        FROM RDS.T_DLO_ACCESSED_POSTS p
        LEFT JOIN EHR.T_EHR_RUS_7PJSAM6GJM e1 on p.EhrRus7pjsam6gjm = e1.EhrRusId
        LEFT JOIN EHR.T_EHR_RUS_REF_BOOKS RRB1 ON RRB1.EhrRusRefBooksID = e1.EhrRusRefBooks
        LEFT JOIN EHR.T_EHR_RUS_LS9R0Q4VR4 e2 on p.EhrRusLs9r0q4vr4 = e2.EhrRusId
        LEFT JOIN EHR.T_EHR_RUS_REF_BOOKS RRB2 ON RRB2.EhrRusRefBooksID = e2.EhrRusRefBooks
        FOR XML PATH(''),
        ROOT ('DLO_ACCESSED_POSTS') 
    )

    select @Result = 
    (
        select @FTLOAD							as 'header/TYPE',
        (
            SELECT
                p.DloAccessedPostsID as 'DLO_ACCESSED_POST/ID',
                p.PostCode as 'DLO_ACCESSED_POST/POST_CODE',
                p.LastName as 'DLO_ACCESSED_POST/LAST_NAME',
                p.FirstName AS 'DLO_ACCESSED_POST/FIRST_NAME',
                p.FatherName AS 'DLO_ACCESSED_POST/FATHER_NAME',
                p.Category AS 'DLO_ACCESSED_POST/CATEGORY',
                convert(varchar(10), p.BeginAccessDate, 120) as 'DLO_ACCESSED_POST/BEGIN_ACCESS_DATE',
                convert(varchar(10), p.EndAccessDate, 120) as 'DLO_ACCESSED_POST/END_ACCESS_DATE',
                convert(varchar(10), p.BeginWorkDate, 120) as 'DLO_ACCESSED_POST/BEGIN_WORK_DATE',
                convert(varchar(10), p.CertificateDate, 120) as 'DLO_ACCESSED_POST/CERTIFICATE_DATE',
                p.MedicalSpecialityCode AS 'DLO_ACCESSED_POST/MEDICAL_SPECIALITY_CODE',
                convert(varchar(10), p.ExpulsionDate, 120) as 'DLO_ACCESSED_POST/EXPULSION_DATE',
                p.Snils as 'DLO_ACCESSED_POST/SNILS',
                p.PostType as 'DLO_ACCESSED_POST/POST_TYPE',
                p.Speciality as 'DLO_ACCESSED_POST/SPECIALITY',
                p.LpuMain as 'DLO_ACCESSED_POST/LPU_MAIN',
                e1.IdEhr as 'DLO_ACCESSED_POST/EhrRus7PJSAM6GJM',
                rrb1.CurrVersion as 'DLO_ACCESSED_POST/EhrRus7PJSAM6GJMVer',
                e2.IdEhr as 'DLO_ACCESSED_POST/EhrRusLS9R0Q4VR4',
                rrb2.CurrVersion as 'DLO_ACCESSED_POST/EhrRusLS9R0Q4VR4Ver'
            FROM RDS.T_DLO_ACCESSED_POSTS p
            LEFT JOIN EHR.T_EHR_RUS_7PJSAM6GJM e1 on p.EhrRus7pjsam6gjm = e1.EhrRusId
            LEFT JOIN EHR.T_EHR_RUS_REF_BOOKS RRB1 ON RRB1.EhrRusRefBooksID = e1.EhrRusRefBooks
            LEFT JOIN EHR.T_EHR_RUS_LS9R0Q4VR4 e2 on p.EhrRusLs9r0q4vr4 = e2.EhrRusId
            LEFT JOIN EHR.T_EHR_RUS_REF_BOOKS RRB2 ON RRB2.EhrRusRefBooksID = e2.EhrRusRefBooks
            FOR XML PATH(''),
            type 
        ) as 'body/DLO_ACCESSED_POSTS'
        FOR XML PATH(''),
        root ('DLO_ACCESSED_POSTS')
    )

    
    INSERT INTO [RDS].[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,
        null
END