SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[APS].[sp_APS_DloAccessPost]') AND type in (N'P', N'PC'))
DROP PROCEDURE [APS].[sp_APS_DloAccessPost]
GO
-- =============================================
-- Author:      Vitacore (с)
-- Create date: 2015-12-30
-- Description: Автоматическая синхронизация регистра врачей DLO в(из) RDS
-- =============================================
CREATE PROCEDURE [APS].[sp_APS_DloAccessPost]
    @Xml xml = null,
    @IsNew bit = 1,
    @ID uniqueidentifier = null,
    @QueueName varchar(100) = null,
    @dateTime datetime = null,
    @User uniqueidentifier = null
AS
BEGIN

    SET NOCOUNT ON;

    declare @Result varchar(max)
           ,@ContentEncoding varchar(255)
           ,@Type varchar(255)
           ,@OperationType varchar(255)
           ,@OwnerID uniqueidentifier
            -- Operation types
           ,@INSERT varchar(6)
           ,@UPDATE varchar(6)
           ,@DELETE varchar(6)
           ,@FTLOAD varchar(6)

    select 
		@Type = 'DLO_ACCESSED_POSTS',
		@dateTime = ISNULL(@dateTime, GETDATE()),
        @INSERT = 'Insert',
        @UPDATE = 'Update',
        @DELETE = 'Delete',
        @FTLOAD = 'FTLoad'


    IF (@Xml is not null)
    BEGIN
        
        -- Получаем тип операции
        SELECT @OperationType = convert(int, t.c.value('./OPERATION_TYPE[1]', 'varchar(1)'))
              ,@OwnerID = t.c.value('./ID[1]', 'uniqueidentifier')
              ,@Result = cast(@Xml as varchar(max))
        FROM @Xml.nodes('DLO_ACCESSED_POSTS') T(c)

        -- Generate table #DLO_ACCESSED_POSTS
        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') 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

        UPDATE dap
        SET 
            dap.LpuMain = ndap.LpuMain,
            dap.PostCode = ndap.PostCode,
            dap.LastName = ndap.LastName,
            dap.FirstName = ndap.FirstName,
            dap.FatherName = ndap.FatherName,
            dap.Category = ndap.Category,
            dap.BeginAccessDate = ndap.BeginAccessDate,
            dap.EndAccessDate = ndap.EndAccessDate,
            dap.BeginWorkDate = ndap.BeginWorkDate,
            dap.CertificateDate = ndap.CertificateDate,
            dap.MedicalSpecialityCode = ndap.MedicalSpecialityCode,
            dap.ExpulsionDate = ndap.ExpulsionDate,
            dap.Snils = ndap.Snils,
            dap.PostType = ndap.PostType,
            dap.Speciality = ndap.Speciality,
            dap.EhrRus7pjsam6gjm = er1.EhrRusID,
            dap.EhrRusLs9r0q4vr4 = er2.EhrRusID
        FROM [APS].T_DLO_ACCESSED_POSTS dap
        JOIN #DLO_ACCESSED_POSTS ndap on ndap.ID = dap.DloAccessedPostsId
        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 = ndap.EhrRus7pjsam6gjm AND RRB.CURRVERSION = ndap.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 = ndap.EhrRusLs9r0q4vr4 AND RRB.CURRVERSION = ndap.EhrRusLs9r0q4vr4Ver) er2

        INSERT INTO [APS].[T_DLO_ACCESSED_POSTS]
        (
        	[DloAccessedPostsID],
        	[LpuMain],
        	[PostCode],
        	[LastName],
        	[FirstName],
        	[FatherName],
        	[Category],
        	[BeginAccessDate],
        	[EndAccessDate],
        	[BeginWorkDate],
        	[CertificateDate],
        	[MedicalSpecialityCode],
        	[ExpulsionDate],
        	[Snils],
        	[PostType],
        	[Speciality],
            [EhrRus7pjsam6gjm],
            [EhrRusLs9r0q4vr4]
        )
        SELECT
        	ndap.ID,
        	ndap.LpuMain,
        	ndap.PostCode,
        	ndap.LastName,
        	ndap.FirstName,
        	ndap.FatherName,
        	ndap.Category,
        	ndap.BeginAccessDate,
        	ndap.EndAccessDate,
        	ndap.BeginWorkDate,
        	ndap.CertificateDate,
        	ndap.MedicalSpecialityCode,
        	ndap.ExpulsionDate,
        	ndap.Snils,
        	ndap.PostType,
        	ndap.Speciality,
            er1.EhrRusId,
            er2.EhrRusId
        FROM #DLO_ACCESSED_POSTS ndap
        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 = ndap.EhrRus7pjsam6gjm AND RRB.CURRVERSION = ndap.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 = ndap.EhrRusLs9r0q4vr4 AND RRB.CURRVERSION = ndap.EhrRusLs9r0q4vr4Ver) er2
        WHERE 
        	NOT EXISTS
        	(
        		select *
        		from [APS].T_DLO_ACCESSED_POSTS dap
        		where dap.DloAccessedPostsID = ndap.ID
        	)
       

        DROP TABLE #DLO_ACCESSED_POSTS
    END
END
GO