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

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[APS].[sp_FR_UpdateBeneficiaries]') AND type in (N'P', N'PC'))
DROP PROCEDURE [APS].[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 [APS].[sp_FR_UpdateBeneficiaries]
    @Xml xml = null,
    @ver int,
    @date datetime

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

        -- делаем insert в [APS].T_FEDERAL_REGISTER_LOAD        
        INSERT INTO [APS].[T_FEDERAL_REGISTER_LOAD]
        ([FederalRegisterLoadID],[Users],[Version],[UpdateDate])
        select
            NEWID()
            ,NULL
            ,@ver
            ,@date     
            
        DELETE FROM [APS].[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]', 'varchar(14)')         as Snils
        INTO #T_FR_PATIENT
        from @Xml.nodes('body/PATIENTS/FR_PATIENT') T(c)

        -- INSERT [APS].T_FR_PATIENT
        INSERT INTO [APS].[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

        DELETE FROM [APS].[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]', 'varchar(14)')            as Snils
        INTO #T_FR_RECORD
        from @Xml.nodes('body/RECORDS/FR_RECORD') T(c)

        -- INSERT [APS].T_FR_RECORD
        INSERT INTO [APS].[T_FR_RECORD]
           ([FrRecordID]
           ,[Snils]
           ,[Deleted]
           ,[DateBegin]
           ,[DateEnd]
           ,[NSU]
           ,[DateNsuBegin]
           ,[DateNsuEnd]
           ,[Nsus]
           ,[Version])
        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,>)        
        FROM #T_FR_RECORD fr

        -- Drop Table
        DROP TABLE #T_FR_RECORD        
        DELETE FROM [APS].[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    
            ,b.BenefitsID        
            ,T.c.value('./Snils[1]', 'varchar(14)')   as Snils
        INTO #T_FR_DOCUMENT
        from @Xml.nodes('body/DOCUMENTS/FR_DOCUMENT') T(c)
            join [VCLib].T_ENUMS eBv on eBv.EnumTypeID = 190 and ebv.Code = T.c.value('./BenefitViews[1]', 'int')
            join [APS].T_BENEFITS b on b.BenefitViews = ebv.EnumID and b.BenefitCode = T.c.value('./BenefitCode[1]', 'varchar(255)')

        -- INSERT [APS].T_FR_DOCUMENT
        INSERT INTO [APS].[T_FR_DOCUMENT]
           ([FrDocumentID]
           ,[Name]
           ,[Number]
           ,[Benefits]
           ,[Snils])
        select
           NEWID()--(<FrDocumentID, uniqueidentifier,>
           ,Name--,<Name, varchar(255),>
           ,Number--,<Number, varchar(255),>
           ,BenefitsID--,<Benefits, uniqueidentifier,>
           ,Snils--,<Snils, char(14),>)
        FROM #T_FR_DOCUMENT dap
        

        -- Drop Table
        DROP TABLE #T_FR_DOCUMENT                    
    END    
END
