IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_RDS_DirectionAccessXml]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[sp_RDS_DirectionAccessXml]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:        Vitacore (с)
-- Create date: 2014-03-01
-- Description:    Загрузка разрешений на выдачу рецептов из RDS 
-- =============================================
CREATE PROCEDURE [dbo].[sp_RDS_DirectionAccessXml]
    @Xml xml = null,    
    @QueueName varchar(100) = 'RDSSync',
    @dateTime datetime = null,
    @LpuTo varchar(6) = null
AS
BEGIN
SET NOCOUNT ON;

    select @dateTime = ISNULL(@dateTime, GETDATE())

    IF (@Xml is not null)
    BEGIN

        -- Получаем тип операции
        select @LpuTo = t.c.value('./header[1]/LpuMain[1]', 'varchar(6)')
        from @Xml.nodes('ACCESSES') T(c)

        if (@LpuTo is null)        
            DELETE FROM [AKUZ].[T_DLO_ACCESSED_POSTS]                     
        else
            DELETE dap FROM [AKUZ].[T_DLO_ACCESSED_POSTS] dap
                join [AKUZ].T_LPU_MAIN lm on dap.LpuMain = lm.LpuID
            where lm.LpuCode = @LpuTo

        -- Generate table #DLO_ACCESSED_POSTS
        select
                T.c.value('./ID[1]', 'uniqueidentifier') as ID
                ,T.c.value('./PostCode[1]', 'varchar(255)') as PostCode
                ,T.c.value('./LastName[1]', 'varchar(32)') as LastName
                ,T.c.value('./FirstName[1]', 'varchar(32)') as FirstName
                ,T.c.value('./FatherName[1]', 'varchar(32)') as FatherName
                ,T.c.value('./Category[1]', 'int') as Category
                ,convert(datetime, t.c.value('./BeginAccessDate[1]', 'varchar(10)'), 104) as BeginAccessDate
                ,convert(datetime, t.c.value('./EndAccessDate[1]', 'varchar(10)'), 104) as EndAccessDate
                ,convert(datetime, t.c.value('./ExpulsionDate[1]', 'varchar(10)'), 104) as ExpulsionDate
                ,convert(datetime, t.c.value('./BeginWorkDate[1]', 'varchar(10)'), 104) as BeginWorkDate
                ,convert(datetime, t.c.value('./CertificateDate[1]', 'varchar(10)'), 104) as CertificateDate
                ,T.c.value('./MedicalSpecialityCode[1]', 'varchar(16)') as MedicalSpecialityCode
                ,pt.PostTypeID as PostType
                ,T.c.value('./Speciality[1]', 'varchar(3)') as Speciality
                ,lm.LpuID as LpuMain
                ,sn.Snils
            INTO #DLO_ACCESSED_POSTS
        from @Xml.nodes('ACCESSES/body/DLO_ACCESSED_POSTS') T(c)
            join [AKUZ].T_LPU_MAIN lm on lm.LpuCode = T.c.value('./LpuMain[1]', 'varchar(6)') and T.c.value('./PostCode[1]', 'varchar(255)') like lm.Code + '%'
            left join [AKUZ].T_POST_TYPE pt on pt.Code = T.c.value('./PostType[1]', 'varchar(4)') 
            left join [AKUZ].T_SNILS sn on sn.Snils = T.c.value('./Snils[1]', 'char(14)')

        -- INSERT [AKUZ].T_DLO_ACCESSED_POSTS
        INSERT INTO [AKUZ].[T_DLO_ACCESSED_POSTS]
           ([DloAccessedPostsID]
           ,[LpuMain]
           ,[PostCode]
           ,[LastName]
           ,[FirstName]
           ,[FatherName]
           ,[Speciality]
           ,[PostType]
           ,[Category]
           ,[BeginAccessDate]
           ,[EndAccessDate]
           ,[UpdateInfoDate]
           ,[BeginWorkDate]
           ,[CertificateDate]
           ,[MedicalSpecialityCode]
           ,ExpulsionDate
           ,Snils)
         SELECT
           dap.ID--(<DloAccessedPostsID, uniqueidentifier,>
           ,dap.LpuMain--,<LpuMain, uniqueidentifier,>
           ,dap.PostCode--,<PostCode, varchar(255),>
           ,dap.LastName--,<LastName, varchar(32),>
           ,dap.FirstName--,<FirstName, varchar(32),>
           ,dap.FatherName--,<FatherName, varchar(32),>
           ,(select top 1 s.SpecialityID from [AKUZ].T_SPECIALITY s where s.Code = dap.Speciality)--,<Speciality, uniqueidentifier,>
           ,dap.PostType--,<PostType, uniqueidentifier,>
           ,dap.Category--,<Category, int,>
           ,dap.BeginAccessDate--,<BeginAccessDate, datetime,>
           ,dap.EndAccessDate--,<EndAccessDate, datetime,>
           ,@datetime--,<UpdateInfoDate, datetime,>
           ,dap.BeginWorkDate--,<BeginWorkDate, datetime,>
           ,dap.CertificateDate--,<CertificateDate, datetime,>
           ,dap.MedicalSpecialityCode--,<MedicalSpecialityCode, varchar(16),>)
           ,dap.ExpulsionDate
           ,dap.Snils
        FROM #DLO_ACCESSED_POSTS dap                      
    END                                     
END                                                    
