IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_FR_UpdateBeneficiaries]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[sp_FR_UpdateBeneficiaries]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:        Vitacore (с)
-- Create date: 2014-02-05
-- Description: Загрузка федеральных льготников в RDS 
-- =============================================
CREATE PROCEDURE [dbo].[sp_FR_UpdateBeneficiaries]
    @Xml xml = null,
    @ver int,
    @date datetime

AS
BEGIN
    SET NOCOUNT ON;
    IF (@Xml is not null)
        BEGIN

        -- делаем insert в [AKUZ].T_FEDERAL_REGISTER_LOAD        
        INSERT INTO [AKUZ].[T_FEDERAL_REGISTER_LOAD]
        ([FederalRegisterLoadID],[Users],[Version],[UpdateDate])
        select
            NEWID()
            ,NULL
            ,@ver
            ,@date

        select                            
             T.c.value('./Snils[1]', 'char(14)') as Snils
        INTO #T_SNILS        
        from @Xml.nodes('body/RECORDS/FR_RECORD') T(c)
        union select                            
             T.c.value('./Snils[1]', 'char(14)') as Snils
        from @Xml.nodes('body/PATIENTS/FR_PATIENT') T(c)        
        union select                            
             T.c.value('./Snils[1]', 'char(14)') as Snils                             
        from @Xml.nodes('body/DOCUMENTS/FR_DOCUMENT') T(c)

        insert into [AKUZ].T_SNILS
        select Snils from #T_SNILS
        where Snils not in (select SNILS from [AKUZ].T_SNILS)

        -- Drop Table
        DROP TABLE #T_SNILS
            
        TRUNCATE TABLE [AKUZ].[T_FR_PATIENT]

        -- Generate table #T_FR_PATIENT
        select                            
            T.c.value('./FirstName[1]', 'varchar(255)')		as FirstName
            ,T.c.value('./LastName[1]', 'varchar(255)')		as LastName
            ,T.c.value('./FatherName[1]', 'varchar(255)')	as FatherName
            ,T.c.value('./Birthday[1]', 'varchar(10)')		as Birthday
            ,T.c.value('./Sex[1]', 'int')					as Sex            
            ,T.c.value('./Snils[1]', 'char(14)')			as Snils
        INTO #T_FR_PATIENT
        from @Xml.nodes('body/PATIENTS/FR_PATIENT') T(c)

        -- INSERT [AKUZ].T_FR_PATIENT
        INSERT INTO [AKUZ].[T_FR_PATIENT]
           ([FrPatientID]
           ,[Snils]
           ,[FirstName]
           ,[LastName]
           ,[FatherName]
           ,[Birthday]
           ,[Sex])
        select
           NEWID()--(<FrPatientID, uniqueidentifier,>
           ,fp.Snils--,<Snils, char(14),>
           ,fp.FirstName--,<FirstName, varchar(255),>
           ,fp.LastName--,<LastName, varchar(255),>
           ,fp.FatherName--,<FatherName, varchar(255),>
           ,CONVERT(datetime, fp.Birthday, 104)--,<Birthday, datetime,>
           ,fp.Sex--,<Sex, int,>)    
        FROM #T_FR_PATIENT fp        
            
        -- Drop Table
        DROP TABLE #T_FR_PATIENT

        TRUNCATE TABLE [AKUZ].[T_FR_RECORD]

        -- Generate table #T_FR_RECORD
        select                
            T.c.value('./Deleted[1]', 'bit')				as Deleted
            ,T.c.value('./DateBegin[1]', 'varchar(10)')		as DateBegin
            ,T.c.value('./DateEnd[1]', 'varchar(10)')		as DateEnd
            ,T.c.value('./NSU[1]', 'bit')					as NSU
            ,T.c.value('./DateNsuBegin[1]', 'varchar(10)')	as DateNsuBegin
            ,T.c.value('./DateNsuEnd[1]', 'varchar(10)')	as DateNsuEnd
            ,T.c.value('./Nsus[1]', 'bit')					as Nsus
            ,T.c.value('./Version[1]', 'int')		        as [Version]
            ,T.c.value('./Snils[1]', 'char(14)')			as Snils
            ,T.c.value('./UpdateDate[1]', 'varchar(10)')	as UpdateDate
        INTO #T_FR_RECORD
        from @Xml.nodes('body/RECORDS/FR_RECORD') T(c)

        -- INSERT [AKUZ].T_FR_RECORD
        INSERT INTO [AKUZ].[T_FR_RECORD]
           ([FrRecordID]
           ,[Snils]
           ,[Deleted]
           ,[DateBegin]
           ,[DateEnd]
           ,[NSU]
           ,[DateNsuBegin]
           ,[DateNsuEnd]
           ,[Nsus]
           ,[Version]
           ,[UpdateDate])
        select
           NEWID()--(<FrRecordID, uniqueidentifier,>
           ,fr.Snils--,<Snils, char(14),>
           ,fr.Deleted--,<Deleted, bit,>
           ,CONVERT(datetime, fr.DateBegin, 104)--,<DateBegin, datetime,>
           ,CONVERT(datetime, fr.DateEnd, 104)--,<DateEnd, datetime,>
           ,fr.NSU--,<NSU, bit,>
           ,CONVERT(datetime, fr.DateNsuBegin, 104)--,<DateNsuBegin, datetime,>
           ,CONVERT(datetime, fr.DateNsuEnd, 104)--,<DateNsuEnd, datetime,>
           ,fr.Nsus--,<Nsus, bit,>
           ,fr.[Version]--,<Version, int,>)     
           ,CONVERT(datetime, fr.[UpdateDate], 104) -- UpdateDate
        FROM #T_FR_RECORD fr

        -- Drop Table
        DROP TABLE #T_FR_RECORD        
                            
        TRUNCATE TABLE [AKUZ].[T_FR_DOCUMENT]

        -- Generate table #T_FR_DOCUMENT
        select                
            T.c.value('./Name[1]', 'varchar(255)')			as Name
            ,T.c.value('./Number[1]', 'varchar(255)')		as Number    
            ,T.c.value('./BenefitViews[1]', 'int')			as BenefitViews
            ,T.c.value('./BenefitCode[1]', 'varchar(255)')	as BenefitCode
            ,T.c.value('./Snils[1]', 'char(14)')			as Snils
        INTO #T_FR_DOCUMENT
        from @Xml.nodes('body/DOCUMENTS/FR_DOCUMENT') T(c)

        -- INSERT [AKUZ].T_FR_DOCUMENT
        INSERT INTO [AKUZ].[T_FR_DOCUMENT]
           ([FrDocumentID]
           ,[Name]
           ,[Number]
           ,[Benefits]
           ,[Snils])
        select
           NEWID()--(<FrDocumentID, uniqueidentifier,>
           ,dap.Name--,<Name, varchar(255),>
           ,Number--,<Number, varchar(255),>
           ,b.BenefitsID--,<Benefits, uniqueidentifier,>
           ,Snils--,<Snils, char(14),>)
        FROM #T_FR_DOCUMENT dap
            join [VCLib].T_ENUMS eBv on eBv.EnumTypeID = 190 and ebv.Code = dap.BenefitViews
            join [AKUZ].T_BENEFITS b on b.BenefitViews = ebv.EnumID and b.BenefitCode = dap.BenefitCode
        
        -- Drop Table
        DROP TABLE #T_FR_DOCUMENT                    
    END    
END
