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'[dbo].[sp_BA_DloAccessPostDirection]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[sp_BA_DloAccessPostDirection]
GO
-- =============================================
-- Author:      Vitacore (с)
-- Create date: 2015-12-30
-- Description: Автоматическая синхронизация регистра врачей DLO в(из) RDS
-- =============================================
CREATE PROCEDURE [dbo].[sp_BA_DloAccessPostDirection]
    @Xml xml = null,
    @dateTime datetime = null,
    @QueueName varchar(100) = null,  
    @User uniqueidentifier = null
AS
BEGIN

    SET NOCOUNT ON;
    IF (@Xml is not null)
    BEGIN
        
        -- Generate table #DLO_ACCESSED_POSTS_DIRECTION
        select T.c.value('./body[1]/PostCode[1]', 'varchar(255)')                               as [PostCode]					
              ,T.c.value('./body[1]/LastName[1]', 'varchar(40)')                                as [LastName]					
              ,T.c.value('./body[1]/FirstName[1]', 'varchar(40)')                               as [FirstName]					
              ,T.c.value('./body[1]/FatherName[1]', 'varchar(40)')                              as [FatherName]					
			  ,convert(datetime, T.c.value('./body[1]/BeginAccessDate[1]', 'varchar(10)'), 104) as [BeginAccessDate]			
              ,convert(datetime, T.c.value('./body[1]/EndAccessDate[1]', 'varchar(10)'), 104)   as [EndAccessDate]			  	
              ,convert(datetime, T.c.value('./body[1]/BeginWorkDate[1]', 'varchar(10)'), 104)   as [BeginWorkDate]			  	
              ,T.c.value('./body[1]/MedicalSpecialityCode[1]', 'varchar(16)')                   as [MedicalSpecialityCode]	  	
              ,convert(datetime, T.c.value('./body[1]/ExpulsionDate[1]', 'varchar(10)'), 104)   as [ExpulsionDate]			  	
              ,T.c.value('./body[1]/Snils[1]', 'varchar(14)')                                   as [Snils]					  	
              ,T.c.value('./body[1]/PostType[1]', 'varchar(4)')                                 as [PostType]				  	
              ,T.c.value('./body[1]/Speciality[1]', 'varchar(3)')                               as [Speciality]				  	
              ,T.c.value('./body[1]/DocCode[1]', 'varchar(6)')                                  as [DocCode]				  	
              ,T.c.value('./body[1]/LpuMain[1]', 'varchar(6)')                                  as [LpuMain]					
			  ,T.c.value('./body[1]/DirectionState[1]', 'varchar(36)')                          as [DirectionState]	     		
		      ,T.c.value('./body[1]/RefusingReason[1]', 'varchar(255)')                         as [RefusingReason]				
		      ,T.c.value('./body[1]/PreviousPostCode[1]', 'varchar(255)')                       as [PreviousPostCode]				
		      ,T.c.value('./body[1]/PreviousFatherName[1]', 'varchar(40)')                      as [PreviousFatherName]				
		      ,T.c.value('./body[1]/PreviousFirstName[1]', 'varchar(40)')                       as [PreviousFirstName]				
		      ,T.c.value('./body[1]/PreviousLastName[1]', 'varchar(40)')                        as [PreviousLastName]				
            INTO #DLO_ACCESSED_POSTS_DIRECTION																						
        from @Xml.nodes('DLO_ACCESSED_POSTS_DIRECTION') T(c)																	

        -- Обработка операции
        
        -- update [AKUZ].T_DLO_ACCESSED_POSTS_DIRECTION
        update dap
        set dap.DirectionState = ndap.DirectionState
           ,dap.RefusingReason = ndap.RefusingReason-- select *
        from [AKUZ].T_DLO_ACCESSED_POSTS_DIRECTION dap
            join #DLO_ACCESSED_POSTS_DIRECTION ndap on (dap.PreviousPostCode = ndap.PreviousPostCode or dap.PostCode = ndap.PostCode) and ndap.Snils = dap.Snils
            join [AKUZ].T_LPU_MAIN lm on lm.LpuCode = ndap.LpuMain
            left join [AKUZ].T_SPECIALITY s on s.Code = ndap.Speciality
            left join [AKUZ].T_POST_TYPE pt on pt.Code = ndap.PostType
		where dap.DirectionState = 0

        -- Drop tables
        DROP TABLE #DLO_ACCESSED_POSTS_DIRECTION

    END
END
GO